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