sql/connection.cc

   1 // Copyright (c) 2012 The Chromium Authors. All rights reserved.
   2 // Use of this source code is governed by a BSD-style license that can be
   3 // found in the LICENSE file.
   4
   5 #include "sql/connection.h"
   6
   7 #include <string.h>
   8
   9 #include "base/file_path.h"
  10 #include "base/logging.h"
  11 #include "base/string_util.h"
  12 #include "base/stringprintf.h"
  13 #include "base/utf_string_conversions.h"
  14 #include "sql/statement.h"
  15 #include "third_party/sqlite/sqlite3.h"
  16
  17 namespace {
  18
  19 // Spin for up to a second waiting for the lock to clear when setting
  20 // up the database.
  21 // TODO(shess): Better story on this.  http://crbug.com/56559
  22 const int kBusyTimeoutSeconds = 1;
  23
  24 class ScopedBusyTimeout {
  25  public:
  26   explicit ScopedBusyTimeout(sqlite3* db)
  27       : db_(db) {
  28   }
  29   ~ScopedBusyTimeout() {
  30     sqlite3_busy_timeout(db_, 0);
  31   }
  32
  33   int SetTimeout(base::TimeDelta timeout) {
  34     DCHECK_LT(timeout.InMilliseconds(), INT_MAX);
  35     return sqlite3_busy_timeout(db_,
  36                                 static_cast<int>(timeout.InMilliseconds()));
  37   }
  38
  39  private:
  40   sqlite3* db_;
  41 };
  42
  43 }  // namespace
  44
  45 namespace sql {
  46
  47 bool StatementID::operator<(const StatementID& other) const {
  48   if (number_ != other.number_)
  49     return number_ < other.number_;
  50   return strcmp(str_, other.str_) < 0;
  51 }
  52
  53 ErrorDelegate::ErrorDelegate() {
  54 }
  55
  56 ErrorDelegate::~ErrorDelegate() {
  57 }
  58
  59 Connection::StatementRef::StatementRef()
  60     : connection_(NULL),
  61       stmt_(NULL) {
  62 }
  63
  64 Connection::StatementRef::StatementRef(sqlite3_stmt* stmt)
  65     : connection_(NULL),
  66       stmt_(stmt) {
  67 }
  68
  69 Connection::StatementRef::StatementRef(Connection* connection,
  70                                        sqlite3_stmt* stmt)
  71     : connection_(connection),
  72       stmt_(stmt) {
  73   connection_->StatementRefCreated(this);
  74 }
  75
  76 Connection::StatementRef::~StatementRef() {
  77   if (connection_)
  78     connection_->StatementRefDeleted(this);
  79   Close();
  80 }
  81
  82 void Connection::StatementRef::Close() {
  83   if (stmt_) {
  84     // Call to AssertIOAllowed() cannot go at the beginning of the function
  85     // because Close() is called unconditionally from destructor to clean
  86     // connection_. And if this is inactive statement this won't cause any
  87     // disk access and destructor most probably will be called on thread
  88     // not allowing disk access.
  89     // TODO(paivanof@gmail.com): This should move to the beginning
  90     // of the function. http://crbug.com/136655.
  91     AssertIOAllowed();
  92     sqlite3_finalize(stmt_);
  93     stmt_ = NULL;
  94   }
  95   connection_ = NULL;  // The connection may be getting deleted.
  96 }
  97
  98 Connection::Connection()
  99     : db_(NULL),
 100       page_size_(0),
 101       cache_size_(0),
 102       exclusive_locking_(false),
 103       transaction_nesting_(0),
 104       needs_rollback_(false),
 105       in_memory_(false) {
 106 }
 107
 108 Connection::~Connection() {
 109   Close();
 110 }
 111
 112 bool Connection::Open(const FilePath& path) {
 113 #if defined(OS_WIN)
 114   return OpenInternal(WideToUTF8(path.value()));
 115 #elif defined(OS_POSIX)
 116   return OpenInternal(path.value());
 117 #endif
 118 }
 119
 120 bool Connection::OpenInMemory() {
 121   in_memory_ = true;
 122   return OpenInternal(":memory:");
 123 }
 124
 125 void Connection::Close() {
 126   // TODO(shess): Calling "PRAGMA journal_mode = DELETE" at this point
 127   // will delete the -journal file.  For ChromiumOS or other more
 128   // embedded systems, this is probably not appropriate, whereas on
 129   // desktop it might make some sense.
 130
 131   // sqlite3_close() needs all prepared statements to be finalized.
 132   // Release all cached statements, then assert that the client has
 133   // released all statements.
 134   statement_cache_.clear();
 135   DCHECK(open_statements_.empty());
 136
 137   // Additionally clear the prepared statements, because they contain
 138   // weak references to this connection.  This case has come up when
 139   // error-handling code is hit in production.
 140   ClearCache();
 141
 142   if (db_) {
 143     // Call to AssertIOAllowed() cannot go at the beginning of the function
 144     // because Close() must be called from destructor to clean
 145     // statement_cache_, it won't cause any disk access and it most probably
 146     // will happen on thread not allowing disk access.
 147     // TODO(paivanof@gmail.com): This should move to the beginning
 148     // of the function. http://crbug.com/136655.
 149     AssertIOAllowed();
 150     // TODO(shess): Histogram for failure.
 151     sqlite3_close(db_);
 152     db_ = NULL;
 153   }
 154 }
 155
 156 void Connection::Preload() {
 157   AssertIOAllowed();
 158
 159   if (!db_) {
 160     DLOG(FATAL) << "Cannot preload null db";
 161     return;
 162   }
 163
 164   // A statement must be open for the preload command to work. If the meta
 165   // table doesn't exist, it probably means this is a new database and there
 166   // is nothing to preload (so it's OK we do nothing).
 167   if (!DoesTableExist("meta"))
 168     return;
 169   Statement dummy(GetUniqueStatement("SELECT * FROM meta"));
 170   if (!dummy.Step())
 171     return;
 172
 173 #if !defined(USE_SYSTEM_SQLITE)
 174   // This function is only defined in Chromium's version of sqlite.
 175   // Do not call it when using system sqlite.
 176   sqlite3_preload(db_);
 177 #endif
 178 }
 179
 180 // Create an in-memory database with the existing database's page
 181 // size, then backup that database over the existing database.
 182 bool Connection::Raze() {
 183   AssertIOAllowed();
 184
 185   if (!db_) {
 186     DLOG(FATAL) << "Cannot raze null db";
 187     return false;
 188   }
 189
 190   if (transaction_nesting_ > 0) {
 191     DLOG(FATAL) << "Cannot raze within a transaction";
 192     return false;
 193   }
 194
 195   sql::Connection null_db;
 196   if (!null_db.OpenInMemory()) {
 197     DLOG(FATAL) << "Unable to open in-memory database.";
 198     return false;
 199   }
 200
 201   // Get the page size from the current connection, then propagate it
 202   // to the null database.
 203   {
 204     Statement s(GetUniqueStatement("PRAGMA page_size"));
 205     if (!s.Step())
 206       return false;
 207     const std::string sql = StringPrintf("PRAGMA page_size=%d",
 208                                          s.ColumnInt(0));
 209     if (!null_db.Execute(sql.c_str()))
 210       return false;
 211   }
 212
 213   // Get the value of auto_vacuum from the current connection, then propagate it
 214   // to the null database.
 215   {
 216     Statement s(GetUniqueStatement("PRAGMA auto_vacuum"));
 217     if (!s.Step())
 218       return false;
 219     const std::string sql = StringPrintf("PRAGMA auto_vacuum=%d",
 220                                          s.ColumnInt(0));
 221     if (!null_db.Execute(sql.c_str()))
 222       return false;
 223   }
 224
 225   // The page size doesn't take effect until a database has pages, and
 226   // at this point the null database has none.  Changing the schema
 227   // version will create the first page.  This will not affect the
 228   // schema version in the resulting database, as SQLite's backup
 229   // implementation propagates the schema version from the original
 230   // connection to the new version of the database, incremented by one
 231   // so that other readers see the schema change and act accordingly.
 232   if (!null_db.Execute("PRAGMA schema_version = 1"))
 233     return false;
 234
 235   sqlite3_backup* backup = sqlite3_backup_init(db_, "main",
 236                                                null_db.db_, "main");
 237   if (!backup) {
 238     DLOG(FATAL) << "Unable to start sqlite3_backup().";
 239     return false;
 240   }
 241
 242   // -1 backs up the entire database.
 243   int rc = sqlite3_backup_step(backup, -1);
 244   int pages = sqlite3_backup_pagecount(backup);
 245   sqlite3_backup_finish(backup);
 246
 247   // The destination database was locked.
 248   if (rc == SQLITE_BUSY) {
 249     return false;
 250   }
 251
 252   // The entire database should have been backed up.
 253   if (rc != SQLITE_DONE) {
 254     DLOG(FATAL) << "Unable to copy entire null database.";
 255     return false;
 256   }
 257
 258   // Exactly one page should have been backed up.  If this breaks,
 259   // check this function to make sure assumptions aren't being broken.
 260   DCHECK_EQ(pages, 1);
 261
 262   return true;
 263 }
 264
 265 bool Connection::RazeWithTimout(base::TimeDelta timeout) {
 266   if (!db_) {
 267     DLOG(FATAL) << "Cannot raze null db";
 268     return false;
 269   }
 270
 271   ScopedBusyTimeout busy_timeout(db_);
 272   busy_timeout.SetTimeout(timeout);
 273   return Raze();
 274 }
 275
 276 bool Connection::BeginTransaction() {
 277   if (needs_rollback_) {
 278     DCHECK_GT(transaction_nesting_, 0);
 279
 280     // When we're going to rollback, fail on this begin and don't actually
 281     // mark us as entering the nested transaction.
 282     return false;
 283   }
 284
 285   bool success = true;
 286   if (!transaction_nesting_) {
 287     needs_rollback_ = false;
 288
 289     Statement begin(GetCachedStatement(SQL_FROM_HERE, "BEGIN TRANSACTION"));
 290     if (!begin.Run())
 291       return false;
 292   }
 293   transaction_nesting_++;
 294   return success;
 295 }
 296
 297 void Connection::RollbackTransaction() {
 298   if (!transaction_nesting_) {
 299     DLOG(FATAL) << "Rolling back a nonexistent transaction";
 300     return;
 301   }
 302
 303   transaction_nesting_--;
 304
 305   if (transaction_nesting_ > 0) {
 306     // Mark the outermost transaction as needing rollback.
 307     needs_rollback_ = true;
 308     return;
 309   }
 310
 311   DoRollback();
 312 }
 313
 314 bool Connection::CommitTransaction() {
 315   if (!transaction_nesting_) {
 316     DLOG(FATAL) << "Rolling back a nonexistent transaction";
 317     return false;
 318   }
 319   transaction_nesting_--;
 320
 321   if (transaction_nesting_ > 0) {
 322     // Mark any nested transactions as failing after we've already got one.
 323     return !needs_rollback_;
 324   }
 325
 326   if (needs_rollback_) {
 327     DoRollback();
 328     return false;
 329   }
 330
 331   Statement commit(GetCachedStatement(SQL_FROM_HERE, "COMMIT"));
 332   return commit.Run();
 333 }
 334
 335 int Connection::ExecuteAndReturnErrorCode(const char* sql) {
 336   AssertIOAllowed();
 337   if (!db_)
 338     return false;
 339   return sqlite3_exec(db_, sql, NULL, NULL, NULL);
 340 }
 341
 342 bool Connection::Execute(const char* sql) {
 343   int error = ExecuteAndReturnErrorCode(sql);
 344   // This needs to be a FATAL log because the error case of arriving here is
 345   // that there's a malformed SQL statement. This can arise in development if
 346   // a change alters the schema but not all queries adjust.
 347   if (error == SQLITE_ERROR)
 348     DLOG(FATAL) << "SQL Error in " << sql << ", " << GetErrorMessage();
 349   return error == SQLITE_OK;
 350 }
 351
 352 bool Connection::ExecuteWithTimeout(const char* sql, base::TimeDelta timeout) {
 353   if (!db_)
 354     return false;
 355
 356   ScopedBusyTimeout busy_timeout(db_);
 357   busy_timeout.SetTimeout(timeout);
 358   return Execute(sql);
 359 }
 360
 361 bool Connection::HasCachedStatement(const StatementID& id) const {
 362   return statement_cache_.find(id) != statement_cache_.end();
 363 }
 364
 365 scoped_refptr<Connection::StatementRef> Connection::GetCachedStatement(
 366     const StatementID& id,
 367     const char* sql) {
 368   CachedStatementMap::iterator i = statement_cache_.find(id);
 369   if (i != statement_cache_.end()) {
 370     // Statement is in the cache. It should still be active (we're the only
 371     // one invalidating cached statements, and we'll remove it from the cache
 372     // if we do that. Make sure we reset it before giving out the cached one in
 373     // case it still has some stuff bound.
 374     DCHECK(i->second->is_valid());
 375     sqlite3_reset(i->second->stmt());
 376     return i->second;
 377   }
 378
 379   scoped_refptr<StatementRef> statement = GetUniqueStatement(sql);
 380   if (statement->is_valid())
 381     statement_cache_[id] = statement;  // Only cache valid statements.
 382   return statement;
 383 }
 384
 385 scoped_refptr<Connection::StatementRef> Connection::GetUniqueStatement(
 386     const char* sql) {
 387   AssertIOAllowed();
 388
 389   if (!db_)
 390     return new StatementRef();  // Return inactive statement.
 391
 392   sqlite3_stmt* stmt = NULL;
 393   if (sqlite3_prepare_v2(db_, sql, -1, &stmt, NULL) != SQLITE_OK) {
 394     // This is evidence of a syntax error in the incoming SQL.
 395     DLOG(FATAL) << "SQL compile error " << GetErrorMessage();
 396     return new StatementRef();
 397   }
 398   return new StatementRef(this, stmt);
 399 }
 400
 401 scoped_refptr<Connection::StatementRef> Connection::GetUntrackedStatement(
 402     const char* sql) const {
 403   if (!db_)
 404     return new StatementRef();  // Return inactive statement.
 405
 406   sqlite3_stmt* stmt = NULL;
 407   int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, NULL);
 408   if (rc != SQLITE_OK) {
 409     // This is evidence of a syntax error in the incoming SQL.
 410     DLOG(FATAL) << "SQL compile error " << GetErrorMessage();
 411     return new StatementRef();
 412   }
 413   return new StatementRef(stmt);
 414 }
 415
 416 bool Connection::IsSQLValid(const char* sql) {
 417   AssertIOAllowed();
 418   sqlite3_stmt* stmt = NULL;
 419   if (sqlite3_prepare_v2(db_, sql, -1, &stmt, NULL) != SQLITE_OK)
 420     return false;
 421
 422   sqlite3_finalize(stmt);
 423   return true;
 424 }
 425
 426 bool Connection::DoesTableExist(const char* table_name) const {
 427   return DoesTableOrIndexExist(table_name, "table");
 428 }
 429
 430 bool Connection::DoesIndexExist(const char* index_name) const {
 431   return DoesTableOrIndexExist(index_name, "index");
 432 }
 433
 434 bool Connection::DoesTableOrIndexExist(
 435     const char* name, const char* type) const {
 436   const char* kSql = "SELECT name FROM sqlite_master WHERE type=? AND name=?";
 437   Statement statement(GetUntrackedStatement(kSql));
 438   statement.BindString(0, type);
 439   statement.BindString(1, name);
 440
 441   return statement.Step();  // Table exists if any row was returned.
 442 }
 443
 444 bool Connection::DoesColumnExist(const char* table_name,
 445                                  const char* column_name) const {
 446   std::string sql("PRAGMA TABLE_INFO(");
 447   sql.append(table_name);
 448   sql.append(")");
 449
 450   Statement statement(GetUntrackedStatement(sql.c_str()));
 451   while (statement.Step()) {
 452     if (!statement.ColumnString(1).compare(column_name))
 453       return true;
 454   }
 455   return false;
 456 }
 457
 458 int64 Connection::GetLastInsertRowId() const {
 459   if (!db_) {
 460     DLOG(FATAL) << "Illegal use of connection without a db";
 461     return 0;
 462   }
 463   return sqlite3_last_insert_rowid(db_);
 464 }
 465
 466 int Connection::GetLastChangeCount() const {
 467   if (!db_) {
 468     DLOG(FATAL) << "Illegal use of connection without a db";
 469     return 0;
 470   }
 471   return sqlite3_changes(db_);
 472 }
 473
 474 int Connection::GetErrorCode() const {
 475   if (!db_)
 476     return SQLITE_ERROR;
 477   return sqlite3_errcode(db_);
 478 }
 479
 480 int Connection::GetLastErrno() const {
 481   if (!db_)
 482     return -1;
 483
 484   int err = 0;
 485   if (SQLITE_OK != sqlite3_file_control(db_, NULL, SQLITE_LAST_ERRNO, &err))
 486     return -2;
 487
 488   return err;
 489 }
 490
 491 const char* Connection::GetErrorMessage() const {
 492   if (!db_)
 493     return "sql::Connection has no connection.";
 494   return sqlite3_errmsg(db_);
 495 }
 496
 497 bool Connection::OpenInternal(const std::string& file_name) {
 498   AssertIOAllowed();
 499
 500   if (db_) {
 501     DLOG(FATAL) << "sql::Connection is already open.";
 502     return false;
 503   }
 504
 505   int err = sqlite3_open(file_name.c_str(), &db_);
 506   if (err != SQLITE_OK) {
 507     OnSqliteError(err, NULL);
 508     Close();
 509     db_ = NULL;
 510     return false;
 511   }
 512
 513   // Enable extended result codes to provide more color on I/O errors.
 514   // Not having extended result codes is not a fatal problem, as
 515   // Chromium code does not attempt to handle I/O errors anyhow.  The
 516   // current implementation always returns SQLITE_OK, the DCHECK is to
 517   // quickly notify someone if SQLite changes.
 518   err = sqlite3_extended_result_codes(db_, 1);
 519   DCHECK_EQ(err, SQLITE_OK) << "Could not enable extended result codes";
 520
 521   // If indicated, lock up the database before doing anything else, so
 522   // that the following code doesn't have to deal with locking.
 523   // TODO(shess): This code is brittle.  Find the cases where code
 524   // doesn't request |exclusive_locking_| and audit that it does the
 525   // right thing with SQLITE_BUSY, and that it doesn't make
 526   // assumptions about who might change things in the database.
 527   // http://crbug.com/56559
 528   if (exclusive_locking_) {
 529     // TODO(shess): This should probably be a full CHECK().  Code
 530     // which requests exclusive locking but doesn't get it is almost
 531     // certain to be ill-tested.
 532     if (!Execute("PRAGMA locking_mode=EXCLUSIVE"))
 533       DLOG(FATAL) << "Could not set locking mode: " << GetErrorMessage();
 534   }
 535
 536   // http://www.sqlite.org/pragma.html#pragma_journal_mode
 537   // DELETE (default) - delete -journal file to commit.
 538   // TRUNCATE - truncate -journal file to commit.
 539   // PERSIST - zero out header of -journal file to commit.
 540   // journal_size_limit provides size to trim to in PERSIST.
 541   // TODO(shess): Figure out if PERSIST and journal_size_limit really
 542   // matter.  In theory, it keeps pages pre-allocated, so if
 543   // transactions usually fit, it should be faster.
 544   ignore_result(Execute("PRAGMA journal_mode = PERSIST"));
 545   ignore_result(Execute("PRAGMA journal_size_limit = 16384"));
 546
 547   const base::TimeDelta kBusyTimeout =
 548     base::TimeDelta::FromSeconds(kBusyTimeoutSeconds);
 549
 550   if (page_size_ != 0) {
 551     // Enforce SQLite restrictions on |page_size_|.
 552     DCHECK(!(page_size_ & (page_size_ - 1)))
 553         << " page_size_ " << page_size_ << " is not a power of two.";
 554     static const int kSqliteMaxPageSize = 32768;  // from sqliteLimit.h
 555     DCHECK_LE(page_size_, kSqliteMaxPageSize);
 556     const std::string sql = StringPrintf("PRAGMA page_size=%d", page_size_);
 557     if (!ExecuteWithTimeout(sql.c_str(), kBusyTimeout))
 558       DLOG(FATAL) << "Could not set page size: " << GetErrorMessage();
 559   }
 560
 561   if (cache_size_ != 0) {
 562     const std::string sql = StringPrintf("PRAGMA cache_size=%d", cache_size_);
 563     if (!ExecuteWithTimeout(sql.c_str(), kBusyTimeout))
 564       DLOG(FATAL) << "Could not set cache size: " << GetErrorMessage();
 565   }
 566
 567   if (!ExecuteWithTimeout("PRAGMA secure_delete=ON", kBusyTimeout)) {
 568     DLOG(FATAL) << "Could not enable secure_delete: " << GetErrorMessage();
 569     Close();
 570     return false;
 571   }
 572
 573   return true;
 574 }
 575
 576 void Connection::DoRollback() {
 577   Statement rollback(GetCachedStatement(SQL_FROM_HERE, "ROLLBACK"));
 578   rollback.Run();
 579   needs_rollback_ = false;
 580 }
 581
 582 void Connection::StatementRefCreated(StatementRef* ref) {
 583   DCHECK(open_statements_.find(ref) == open_statements_.end());
 584   open_statements_.insert(ref);
 585 }
 586
 587 void Connection::StatementRefDeleted(StatementRef* ref) {
 588   StatementRefSet::iterator i = open_statements_.find(ref);
 589   if (i == open_statements_.end())
 590     DLOG(FATAL) << "Could not find statement";
 591   else
 592     open_statements_.erase(i);
 593 }
 594
 595 void Connection::ClearCache() {
 596   statement_cache_.clear();
 597
 598   // The cache clear will get most statements. There may be still be references
 599   // to some statements that are held by others (including one-shot statements).
 600   // This will deactivate them so they can't be used again.
 601   for (StatementRefSet::iterator i = open_statements_.begin();
 602        i != open_statements_.end(); ++i)
 603     (*i)->Close();
 604 }
 605
 606 int Connection::OnSqliteError(int err, sql::Statement *stmt) {
 607   if (error_delegate_.get())
 608     return error_delegate_->OnError(err, this, stmt);
 609   // The default handling is to assert on debug and to ignore on release.
 610   DLOG(FATAL) << GetErrorMessage();
 611   return err;
 612 }
 613
 614 }  // namespace sql