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