1 /*
2 * Copyright (C) 2006, 2007, 2008 Apple Inc. All rights reserved.
3 *
4 * Redistribution and use in source and binary forms, with or without
5 * modification, are permitted provided that the following conditions
6 * are met:
7 * 1. Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * 2. Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 *
13 * THIS SOFTWARE IS PROVIDED BY APPLE COMPUTER, INC. ``AS IS'' AND ANY
14 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
15 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
16 * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL APPLE COMPUTER, INC. OR
17 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
18 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
19 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
20 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
21 * OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
22 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
23 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
24 */
25
26 #include "config.h"
27 #include "SQLiteStatement.h"
28
29 #if ENABLE(DATABASE)
30
31 #include "Logging.h"
32 #include "SQLValue.h"
33 #include <sqlite3.h>
34 #include <wtf/Assertions.h>
35 #include <wtf/text/CString.h>
36
37 namespace WebCore {
38
39 #if SQLITE_VERSION_NUMBER < 3003009
40
41 // FIXME: This overload helps us compile with older versions of SQLite 3, but things like quotas will not work.
sqlite3_prepare16_v2(sqlite3 * db,const void * zSql,int nBytes,sqlite3_stmt ** ppStmt,const void ** pzTail)42 static inline int sqlite3_prepare16_v2(sqlite3* db, const void* zSql, int nBytes, sqlite3_stmt** ppStmt, const void** pzTail)
43 {
44 return sqlite3_prepare16(db, zSql, nBytes, ppStmt, pzTail);
45 }
46
47 #endif
48
SQLiteStatement(SQLiteDatabase & db,const String & sql)49 SQLiteStatement::SQLiteStatement(SQLiteDatabase& db, const String& sql)
50 : m_database(db)
51 , m_query(sql)
52 , m_statement(0)
53 #ifndef NDEBUG
54 , m_isPrepared(false)
55 #endif
56 {
57 }
58
~SQLiteStatement()59 SQLiteStatement::~SQLiteStatement()
60 {
61 finalize();
62 }
63
prepare()64 int SQLiteStatement::prepare()
65 {
66 ASSERT(!m_isPrepared);
67
68 MutexLocker databaseLock(m_database.databaseMutex());
69 if (m_database.isInterrupted())
70 return SQLITE_INTERRUPT;
71
72 const void* tail = 0;
73 LOG(SQLDatabase, "SQL - prepare - %s", m_query.ascii().data());
74 String strippedQuery = m_query.stripWhiteSpace();
75 int error = sqlite3_prepare16_v2(m_database.sqlite3Handle(), strippedQuery.charactersWithNullTermination(), -1, &m_statement, &tail);
76
77 // Starting with version 3.6.16, sqlite has a patch (http://www.sqlite.org/src/ci/256ec3c6af)
78 // that should make sure sqlite3_prepare16_v2 doesn't return a SQLITE_SCHEMA error.
79 // If we're using an older sqlite version, try to emulate the patch.
80 if (error == SQLITE_SCHEMA) {
81 sqlite3_finalize(m_statement);
82 error = sqlite3_prepare16_v2(m_database.sqlite3Handle(), m_query.charactersWithNullTermination(), -1, &m_statement, &tail);
83 }
84
85 if (error != SQLITE_OK)
86 LOG(SQLDatabase, "sqlite3_prepare16 failed (%i)\n%s\n%s", error, m_query.ascii().data(), sqlite3_errmsg(m_database.sqlite3Handle()));
87 const UChar* ch = static_cast<const UChar*>(tail);
88 if (ch && *ch)
89 error = SQLITE_ERROR;
90 #ifndef NDEBUG
91 m_isPrepared = error == SQLITE_OK;
92 #endif
93 return error;
94 }
95
step()96 int SQLiteStatement::step()
97 {
98 ASSERT(m_isPrepared);
99
100 MutexLocker databaseLock(m_database.databaseMutex());
101 if (m_database.isInterrupted())
102 return SQLITE_INTERRUPT;
103
104 if (!m_statement)
105 return SQLITE_OK;
106 LOG(SQLDatabase, "SQL - step - %s", m_query.ascii().data());
107 int error = sqlite3_step(m_statement);
108 if (error != SQLITE_DONE && error != SQLITE_ROW) {
109 LOG(SQLDatabase, "sqlite3_step failed (%i)\nQuery - %s\nError - %s",
110 error, m_query.ascii().data(), sqlite3_errmsg(m_database.sqlite3Handle()));
111 }
112
113 return error;
114 }
115
finalize()116 int SQLiteStatement::finalize()
117 {
118 #ifndef NDEBUG
119 m_isPrepared = false;
120 #endif
121 if (!m_statement)
122 return SQLITE_OK;
123 LOG(SQLDatabase, "SQL - finalize - %s", m_query.ascii().data());
124 int result = sqlite3_finalize(m_statement);
125 m_statement = 0;
126 return result;
127 }
128
reset()129 int SQLiteStatement::reset()
130 {
131 ASSERT(m_isPrepared);
132 if (!m_statement)
133 return SQLITE_OK;
134 LOG(SQLDatabase, "SQL - reset - %s", m_query.ascii().data());
135 return sqlite3_reset(m_statement);
136 }
137
executeCommand()138 bool SQLiteStatement::executeCommand()
139 {
140 if (!m_statement && prepare() != SQLITE_OK)
141 return false;
142 ASSERT(m_isPrepared);
143 if (step() != SQLITE_DONE) {
144 finalize();
145 return false;
146 }
147 finalize();
148 return true;
149 }
150
returnsAtLeastOneResult()151 bool SQLiteStatement::returnsAtLeastOneResult()
152 {
153 if (!m_statement && prepare() != SQLITE_OK)
154 return false;
155 ASSERT(m_isPrepared);
156 if (step() != SQLITE_ROW) {
157 finalize();
158 return false;
159 }
160 finalize();
161 return true;
162
163 }
164
bindBlob(int index,const void * blob,int size)165 int SQLiteStatement::bindBlob(int index, const void* blob, int size)
166 {
167 ASSERT(m_isPrepared);
168 ASSERT(index > 0);
169 ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
170 ASSERT(blob);
171 ASSERT(size >= 0);
172
173 if (!m_statement)
174 return SQLITE_ERROR;
175
176 return sqlite3_bind_blob(m_statement, index, blob, size, SQLITE_TRANSIENT);
177 }
178
bindBlob(int index,const String & text)179 int SQLiteStatement::bindBlob(int index, const String& text)
180 {
181 // String::characters() returns 0 for the empty string, which SQLite
182 // treats as a null, so we supply a non-null pointer for that case.
183 UChar anyCharacter = 0;
184 const UChar* characters;
185 if (text.isEmpty() && !text.isNull())
186 characters = &anyCharacter;
187 else
188 characters = text.characters();
189
190 return bindBlob(index, characters, text.length() * sizeof(UChar));
191 }
192
bindText(int index,const String & text)193 int SQLiteStatement::bindText(int index, const String& text)
194 {
195 ASSERT(m_isPrepared);
196 ASSERT(index > 0);
197 ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
198
199 // String::characters() returns 0 for the empty string, which SQLite
200 // treats as a null, so we supply a non-null pointer for that case.
201 UChar anyCharacter = 0;
202 const UChar* characters;
203 if (text.isEmpty() && !text.isNull())
204 characters = &anyCharacter;
205 else
206 characters = text.characters();
207
208 return sqlite3_bind_text16(m_statement, index, characters, sizeof(UChar) * text.length(), SQLITE_TRANSIENT);
209 }
210
bindInt(int index,int integer)211 int SQLiteStatement::bindInt(int index, int integer)
212 {
213 ASSERT(m_isPrepared);
214 ASSERT(index > 0);
215 ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
216
217 return sqlite3_bind_int(m_statement, index, integer);
218 }
219
bindInt64(int index,int64_t integer)220 int SQLiteStatement::bindInt64(int index, int64_t integer)
221 {
222 ASSERT(m_isPrepared);
223 ASSERT(index > 0);
224 ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
225
226 return sqlite3_bind_int64(m_statement, index, integer);
227 }
228
bindDouble(int index,double number)229 int SQLiteStatement::bindDouble(int index, double number)
230 {
231 ASSERT(m_isPrepared);
232 ASSERT(index > 0);
233 ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
234
235 return sqlite3_bind_double(m_statement, index, number);
236 }
237
bindNull(int index)238 int SQLiteStatement::bindNull(int index)
239 {
240 ASSERT(m_isPrepared);
241 ASSERT(index > 0);
242 ASSERT(static_cast<unsigned>(index) <= bindParameterCount());
243
244 return sqlite3_bind_null(m_statement, index);
245 }
246
bindValue(int index,const SQLValue & value)247 int SQLiteStatement::bindValue(int index, const SQLValue& value)
248 {
249 switch (value.type()) {
250 case SQLValue::StringValue:
251 return bindText(index, value.string());
252 case SQLValue::NumberValue:
253 return bindDouble(index, value.number());
254 case SQLValue::NullValue:
255 return bindNull(index);
256 }
257
258 ASSERT_NOT_REACHED();
259 return SQLITE_ERROR;
260 }
261
bindParameterCount() const262 unsigned SQLiteStatement::bindParameterCount() const
263 {
264 ASSERT(m_isPrepared);
265 if (!m_statement)
266 return 0;
267 return sqlite3_bind_parameter_count(m_statement);
268 }
269
columnCount()270 int SQLiteStatement::columnCount()
271 {
272 ASSERT(m_isPrepared);
273 if (!m_statement)
274 return 0;
275 return sqlite3_data_count(m_statement);
276 }
277
isColumnNull(int col)278 bool SQLiteStatement::isColumnNull(int col)
279 {
280 ASSERT(col >= 0);
281 if (!m_statement)
282 if (prepareAndStep() != SQLITE_ROW)
283 return false;
284 if (columnCount() <= col)
285 return false;
286
287 return sqlite3_column_type(m_statement, col) == SQLITE_NULL;
288 }
289
getColumnName(int col)290 String SQLiteStatement::getColumnName(int col)
291 {
292 ASSERT(col >= 0);
293 if (!m_statement)
294 if (prepareAndStep() != SQLITE_ROW)
295 return String();
296 if (columnCount() <= col)
297 return String();
298 return String(reinterpret_cast<const UChar*>(sqlite3_column_name16(m_statement, col)));
299 }
300
getColumnValue(int col)301 SQLValue SQLiteStatement::getColumnValue(int col)
302 {
303 ASSERT(col >= 0);
304 if (!m_statement)
305 if (prepareAndStep() != SQLITE_ROW)
306 return SQLValue();
307 if (columnCount() <= col)
308 return SQLValue();
309
310 // SQLite is typed per value. optional column types are
311 // "(mostly) ignored"
312 sqlite3_value* value = sqlite3_column_value(m_statement, col);
313 switch (sqlite3_value_type(value)) {
314 case SQLITE_INTEGER: // SQLValue and JS don't represent integers, so use FLOAT -case
315 case SQLITE_FLOAT:
316 return SQLValue(sqlite3_value_double(value));
317 case SQLITE_BLOB: // SQLValue and JS don't represent blobs, so use TEXT -case
318 case SQLITE_TEXT:
319 return SQLValue(String(reinterpret_cast<const UChar*>(sqlite3_value_text16(value))));
320 case SQLITE_NULL:
321 return SQLValue();
322 default:
323 break;
324 }
325 ASSERT_NOT_REACHED();
326 return SQLValue();
327 }
328
getColumnText(int col)329 String SQLiteStatement::getColumnText(int col)
330 {
331 ASSERT(col >= 0);
332 if (!m_statement)
333 if (prepareAndStep() != SQLITE_ROW)
334 return String();
335 if (columnCount() <= col)
336 return String();
337 return String(reinterpret_cast<const UChar*>(sqlite3_column_text16(m_statement, col)));
338 }
339
getColumnDouble(int col)340 double SQLiteStatement::getColumnDouble(int col)
341 {
342 ASSERT(col >= 0);
343 if (!m_statement)
344 if (prepareAndStep() != SQLITE_ROW)
345 return 0.0;
346 if (columnCount() <= col)
347 return 0.0;
348 return sqlite3_column_double(m_statement, col);
349 }
350
getColumnInt(int col)351 int SQLiteStatement::getColumnInt(int col)
352 {
353 ASSERT(col >= 0);
354 if (!m_statement)
355 if (prepareAndStep() != SQLITE_ROW)
356 return 0;
357 if (columnCount() <= col)
358 return 0;
359 return sqlite3_column_int(m_statement, col);
360 }
361
getColumnInt64(int col)362 int64_t SQLiteStatement::getColumnInt64(int col)
363 {
364 ASSERT(col >= 0);
365 if (!m_statement)
366 if (prepareAndStep() != SQLITE_ROW)
367 return 0;
368 if (columnCount() <= col)
369 return 0;
370 return sqlite3_column_int64(m_statement, col);
371 }
372
getColumnBlobAsString(int col)373 String SQLiteStatement::getColumnBlobAsString(int col)
374 {
375 ASSERT(col >= 0);
376
377 if (!m_statement && prepareAndStep() != SQLITE_ROW)
378 return String();
379
380 if (columnCount() <= col)
381 return String();
382
383 const void* blob = sqlite3_column_blob(m_statement, col);
384 if (!blob)
385 return String();
386
387 int size = sqlite3_column_bytes(m_statement, col);
388 if (size < 0)
389 return String();
390
391 ASSERT(!(size % sizeof(UChar)));
392 return String(static_cast<const UChar*>(blob), size / sizeof(UChar));
393 }
394
getColumnBlobAsVector(int col,Vector<char> & result)395 void SQLiteStatement::getColumnBlobAsVector(int col, Vector<char>& result)
396 {
397 ASSERT(col >= 0);
398
399 if (!m_statement && prepareAndStep() != SQLITE_ROW) {
400 result.clear();
401 return;
402 }
403
404 if (columnCount() <= col) {
405 result.clear();
406 return;
407 }
408
409 const void* blob = sqlite3_column_blob(m_statement, col);
410 if (!blob) {
411 result.clear();
412 return;
413 }
414
415 int size = sqlite3_column_bytes(m_statement, col);
416 result.resize((size_t)size);
417 for (int i = 0; i < size; ++i)
418 result[i] = (static_cast<const unsigned char*>(blob))[i];
419 }
420
getColumnBlob(int col,int & size)421 const void* SQLiteStatement::getColumnBlob(int col, int& size)
422 {
423 ASSERT(col >= 0);
424
425 size = 0;
426
427 if (finalize() != SQLITE_OK)
428 LOG(SQLDatabase, "Finalize failed");
429 if (prepare() != SQLITE_OK) {
430 LOG(SQLDatabase, "Prepare failed");
431 return 0;
432 }
433 if (step() != SQLITE_ROW) {
434 LOG(SQLDatabase, "Step wasn't a row");
435 return 0;
436 }
437
438 if (columnCount() <= col)
439 return 0;
440
441 const void* blob = sqlite3_column_blob(m_statement, col);
442 if (!blob)
443 return 0;
444
445 size = sqlite3_column_bytes(m_statement, col);
446 return blob;
447 }
448
returnTextResults(int col,Vector<String> & v)449 bool SQLiteStatement::returnTextResults(int col, Vector<String>& v)
450 {
451 ASSERT(col >= 0);
452
453 v.clear();
454
455 if (m_statement)
456 finalize();
457 if (prepare() != SQLITE_OK)
458 return false;
459
460 while (step() == SQLITE_ROW)
461 v.append(getColumnText(col));
462 bool result = true;
463 if (m_database.lastError() != SQLITE_DONE) {
464 result = false;
465 LOG(SQLDatabase, "Error reading results from database query %s", m_query.ascii().data());
466 }
467 finalize();
468 return result;
469 }
470
returnIntResults(int col,Vector<int> & v)471 bool SQLiteStatement::returnIntResults(int col, Vector<int>& v)
472 {
473 v.clear();
474
475 if (m_statement)
476 finalize();
477 if (prepare() != SQLITE_OK)
478 return false;
479
480 while (step() == SQLITE_ROW)
481 v.append(getColumnInt(col));
482 bool result = true;
483 if (m_database.lastError() != SQLITE_DONE) {
484 result = false;
485 LOG(SQLDatabase, "Error reading results from database query %s", m_query.ascii().data());
486 }
487 finalize();
488 return result;
489 }
490
returnInt64Results(int col,Vector<int64_t> & v)491 bool SQLiteStatement::returnInt64Results(int col, Vector<int64_t>& v)
492 {
493 v.clear();
494
495 if (m_statement)
496 finalize();
497 if (prepare() != SQLITE_OK)
498 return false;
499
500 while (step() == SQLITE_ROW)
501 v.append(getColumnInt64(col));
502 bool result = true;
503 if (m_database.lastError() != SQLITE_DONE) {
504 result = false;
505 LOG(SQLDatabase, "Error reading results from database query %s", m_query.ascii().data());
506 }
507 finalize();
508 return result;
509 }
510
returnDoubleResults(int col,Vector<double> & v)511 bool SQLiteStatement::returnDoubleResults(int col, Vector<double>& v)
512 {
513 v.clear();
514
515 if (m_statement)
516 finalize();
517 if (prepare() != SQLITE_OK)
518 return false;
519
520 while (step() == SQLITE_ROW)
521 v.append(getColumnDouble(col));
522 bool result = true;
523 if (m_database.lastError() != SQLITE_DONE) {
524 result = false;
525 LOG(SQLDatabase, "Error reading results from database query %s", m_query.ascii().data());
526 }
527 finalize();
528 return result;
529 }
530
isExpired()531 bool SQLiteStatement::isExpired()
532 {
533 return !m_statement || sqlite3_expired(m_statement);
534 }
535
536 } // namespace WebCore
537
538 #endif // ENABLE(DATABASE)
539