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 37namespace 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. 42static 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 49SQLiteStatement::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 59SQLiteStatement::~SQLiteStatement() 60{ 61 finalize(); 62} 63 64int 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 96int 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 116int 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 129int 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 138bool 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 151bool 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 165int 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 179int 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 193int 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 211int 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 220int 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 229int 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 238int 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 247int 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 262unsigned 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 270int SQLiteStatement::columnCount() 271{ 272 ASSERT(m_isPrepared); 273 if (!m_statement) 274 return 0; 275 return sqlite3_data_count(m_statement); 276} 277 278bool 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 290String 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 301SQLValue 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 329String 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 340double 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 351int 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 362int64_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 373String 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 395void 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 421const 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 449bool 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 471bool 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 491bool 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 511bool 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 531bool SQLiteStatement::isExpired() 532{ 533 return !m_statement || sqlite3_expired(m_statement); 534} 535 536} // namespace WebCore 537 538#endif // ENABLE(DATABASE) 539