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.
5 #include "sql/connection.h"
9 #include "base/files/file_path.h"
10 #include "base/file_util.h"
11 #include "base/logging.h"
12 #include "base/metrics/histogram.h"
13 #include "base/metrics/sparse_histogram.h"
14 #include "base/string_util.h"
15 #include "base/stringprintf.h"
16 #include "base/strings/string_split.h"
17 #include "base/utf_string_conversions.h"
18 #include "sql/statement.h"
19 #include "third_party/sqlite/sqlite3.h"
23 // Spin for up to a second waiting for the lock to clear when setting
25 // TODO(shess): Better story on this. http://crbug.com/56559
26 const int kBusyTimeoutSeconds
= 1;
28 class ScopedBusyTimeout
{
30 explicit ScopedBusyTimeout(sqlite3
* db
)
33 ~ScopedBusyTimeout() {
34 sqlite3_busy_timeout(db_
, 0);
37 int SetTimeout(base::TimeDelta timeout
) {
38 DCHECK_LT(timeout
.InMilliseconds(), INT_MAX
);
39 return sqlite3_busy_timeout(db_
,
40 static_cast<int>(timeout
.InMilliseconds()));
47 // Helper to "safely" enable writable_schema. No error checking
48 // because it is reasonable to just forge ahead in case of an error.
49 // If turning it on fails, then most likely nothing will work, whereas
50 // if turning it off fails, it only matters if some code attempts to
51 // continue working with the database and tries to modify the
52 // sqlite_master table (none of our code does this).
53 class ScopedWritableSchema
{
55 explicit ScopedWritableSchema(sqlite3
* db
)
57 sqlite3_exec(db_
, "PRAGMA writable_schema=1", NULL
, NULL
, NULL
);
59 ~ScopedWritableSchema() {
60 sqlite3_exec(db_
, "PRAGMA writable_schema=0", NULL
, NULL
, NULL
);
71 bool StatementID::operator<(const StatementID
& other
) const {
72 if (number_
!= other
.number_
)
73 return number_
< other
.number_
;
74 return strcmp(str_
, other
.str_
) < 0;
77 ErrorDelegate::~ErrorDelegate() {
80 Connection::StatementRef::StatementRef(Connection
* connection
,
83 : connection_(connection
),
85 was_valid_(was_valid
) {
87 connection_
->StatementRefCreated(this);
90 Connection::StatementRef::~StatementRef() {
92 connection_
->StatementRefDeleted(this);
96 void Connection::StatementRef::Close(bool forced
) {
98 // Call to AssertIOAllowed() cannot go at the beginning of the function
99 // because Close() is called unconditionally from destructor to clean
100 // connection_. And if this is inactive statement this won't cause any
101 // disk access and destructor most probably will be called on thread
102 // not allowing disk access.
103 // TODO(paivanof@gmail.com): This should move to the beginning
104 // of the function. http://crbug.com/136655.
106 sqlite3_finalize(stmt_
);
109 connection_
= NULL
; // The connection may be getting deleted.
111 // Forced close is expected to happen from a statement error
112 // handler. In that case maintain the sense of |was_valid_| which
113 // previously held for this ref.
114 was_valid_
= was_valid_
&& forced
;
117 Connection::Connection()
121 exclusive_locking_(false),
122 transaction_nesting_(0),
123 needs_rollback_(false),
126 error_delegate_(NULL
) {
129 Connection::~Connection() {
133 bool Connection::Open(const base::FilePath
& path
) {
134 if (!histogram_tag_
.empty()) {
136 if (file_util::GetFileSize(path
, &size_64
)) {
137 size_t sample
= static_cast<size_t>(size_64
/ 1024);
138 std::string full_histogram_name
= "Sqlite.SizeKB." + histogram_tag_
;
139 base::HistogramBase
* histogram
=
140 base::Histogram::FactoryGet(
141 full_histogram_name
, 1, 1000000, 50,
142 base::HistogramBase::kUmaTargetedHistogramFlag
);
144 histogram
->Add(sample
);
149 return OpenInternal(WideToUTF8(path
.value()));
150 #elif defined(OS_POSIX)
151 return OpenInternal(path
.value());
155 bool Connection::OpenInMemory() {
157 return OpenInternal(":memory:");
160 void Connection::CloseInternal(bool forced
) {
161 // TODO(shess): Calling "PRAGMA journal_mode = DELETE" at this point
162 // will delete the -journal file. For ChromiumOS or other more
163 // embedded systems, this is probably not appropriate, whereas on
164 // desktop it might make some sense.
166 // sqlite3_close() needs all prepared statements to be finalized.
168 // Release cached statements.
169 statement_cache_
.clear();
171 // With cached statements released, in-use statements will remain.
172 // Closing the database while statements are in use is an API
173 // violation, except for forced close (which happens from within a
174 // statement's error handler).
175 DCHECK(forced
|| open_statements_
.empty());
177 // Deactivate any outstanding statements so sqlite3_close() works.
178 for (StatementRefSet::iterator i
= open_statements_
.begin();
179 i
!= open_statements_
.end(); ++i
)
181 open_statements_
.clear();
184 // Call to AssertIOAllowed() cannot go at the beginning of the function
185 // because Close() must be called from destructor to clean
186 // statement_cache_, it won't cause any disk access and it most probably
187 // will happen on thread not allowing disk access.
188 // TODO(paivanof@gmail.com): This should move to the beginning
189 // of the function. http://crbug.com/136655.
191 // TODO(shess): Histogram for failure.
197 void Connection::Close() {
198 // If the database was already closed by RazeAndClose(), then no
199 // need to close again. Clear the |poisoned_| bit so that incorrect
200 // API calls are caught.
206 CloseInternal(false);
209 void Connection::Preload() {
213 DLOG_IF(FATAL
, !poisoned_
) << "Cannot preload null db";
217 // A statement must be open for the preload command to work. If the meta
218 // table doesn't exist, it probably means this is a new database and there
219 // is nothing to preload (so it's OK we do nothing).
220 if (!DoesTableExist("meta"))
222 Statement
dummy(GetUniqueStatement("SELECT * FROM meta"));
226 #if !defined(USE_SYSTEM_SQLITE)
227 // This function is only defined in Chromium's version of sqlite.
228 // Do not call it when using system sqlite.
229 sqlite3_preload(db_
);
233 // Create an in-memory database with the existing database's page
234 // size, then backup that database over the existing database.
235 bool Connection::Raze() {
239 DLOG_IF(FATAL
, !poisoned_
) << "Cannot raze null db";
243 if (transaction_nesting_
> 0) {
244 DLOG(FATAL
) << "Cannot raze within a transaction";
248 sql::Connection null_db
;
249 if (!null_db
.OpenInMemory()) {
250 DLOG(FATAL
) << "Unable to open in-memory database.";
255 // Enforce SQLite restrictions on |page_size_|.
256 DCHECK(!(page_size_
& (page_size_
- 1)))
257 << " page_size_ " << page_size_
<< " is not a power of two.";
258 const int kSqliteMaxPageSize
= 32768; // from sqliteLimit.h
259 DCHECK_LE(page_size_
, kSqliteMaxPageSize
);
260 const std::string sql
=
261 base::StringPrintf("PRAGMA page_size=%d", page_size_
);
262 if (!null_db
.Execute(sql
.c_str()))
266 #if defined(OS_ANDROID)
267 // Android compiles with SQLITE_DEFAULT_AUTOVACUUM. Unfortunately,
268 // in-memory databases do not respect this define.
269 // TODO(shess): Figure out a way to set this without using platform
270 // specific code. AFAICT from sqlite3.c, the only way to do it
271 // would be to create an actual filesystem database, which is
273 if (!null_db
.Execute("PRAGMA auto_vacuum = 1"))
277 // The page size doesn't take effect until a database has pages, and
278 // at this point the null database has none. Changing the schema
279 // version will create the first page. This will not affect the
280 // schema version in the resulting database, as SQLite's backup
281 // implementation propagates the schema version from the original
282 // connection to the new version of the database, incremented by one
283 // so that other readers see the schema change and act accordingly.
284 if (!null_db
.Execute("PRAGMA schema_version = 1"))
287 // SQLite tracks the expected number of database pages in the first
288 // page, and if it does not match the total retrieved from a
289 // filesystem call, treats the database as corrupt. This situation
290 // breaks almost all SQLite calls. "PRAGMA writable_schema" can be
291 // used to hint to SQLite to soldier on in that case, specifically
292 // for purposes of recovery. [See SQLITE_CORRUPT_BKPT case in
293 // sqlite3.c lockBtree().]
294 // TODO(shess): With this, "PRAGMA auto_vacuum" and "PRAGMA
295 // page_size" can be used to query such a database.
296 ScopedWritableSchema
writable_schema(db_
);
298 sqlite3_backup
* backup
= sqlite3_backup_init(db_
, "main",
299 null_db
.db_
, "main");
301 DLOG(FATAL
) << "Unable to start sqlite3_backup().";
305 // -1 backs up the entire database.
306 int rc
= sqlite3_backup_step(backup
, -1);
307 int pages
= sqlite3_backup_pagecount(backup
);
308 sqlite3_backup_finish(backup
);
310 // The destination database was locked.
311 if (rc
== SQLITE_BUSY
) {
315 // The entire database should have been backed up.
316 if (rc
!= SQLITE_DONE
) {
317 DLOG(FATAL
) << "Unable to copy entire null database.";
321 // Exactly one page should have been backed up. If this breaks,
322 // check this function to make sure assumptions aren't being broken.
328 bool Connection::RazeWithTimout(base::TimeDelta timeout
) {
330 DLOG_IF(FATAL
, !poisoned_
) << "Cannot raze null db";
334 ScopedBusyTimeout
busy_timeout(db_
);
335 busy_timeout
.SetTimeout(timeout
);
339 bool Connection::RazeAndClose() {
341 DLOG_IF(FATAL
, !poisoned_
) << "Cannot raze null db";
345 // Raze() cannot run in a transaction.
346 while (transaction_nesting_
) {
347 RollbackTransaction();
350 bool result
= Raze();
354 // Mark the database so that future API calls fail appropriately,
355 // but don't DCHECK (because after calling this function they are
356 // expected to fail).
362 bool Connection::BeginTransaction() {
363 if (needs_rollback_
) {
364 DCHECK_GT(transaction_nesting_
, 0);
366 // When we're going to rollback, fail on this begin and don't actually
367 // mark us as entering the nested transaction.
372 if (!transaction_nesting_
) {
373 needs_rollback_
= false;
375 Statement
begin(GetCachedStatement(SQL_FROM_HERE
, "BEGIN TRANSACTION"));
379 transaction_nesting_
++;
383 void Connection::RollbackTransaction() {
384 if (!transaction_nesting_
) {
385 DLOG_IF(FATAL
, !poisoned_
) << "Rolling back a nonexistent transaction";
389 transaction_nesting_
--;
391 if (transaction_nesting_
> 0) {
392 // Mark the outermost transaction as needing rollback.
393 needs_rollback_
= true;
400 bool Connection::CommitTransaction() {
401 if (!transaction_nesting_
) {
402 DLOG_IF(FATAL
, !poisoned_
) << "Rolling back a nonexistent transaction";
405 transaction_nesting_
--;
407 if (transaction_nesting_
> 0) {
408 // Mark any nested transactions as failing after we've already got one.
409 return !needs_rollback_
;
412 if (needs_rollback_
) {
417 Statement
commit(GetCachedStatement(SQL_FROM_HERE
, "COMMIT"));
421 int Connection::ExecuteAndReturnErrorCode(const char* sql
) {
424 DLOG_IF(FATAL
, !poisoned_
) << "Illegal use of connection without a db";
427 return sqlite3_exec(db_
, sql
, NULL
, NULL
, NULL
);
430 bool Connection::Execute(const char* sql
) {
432 DLOG_IF(FATAL
, !poisoned_
) << "Illegal use of connection without a db";
436 int error
= ExecuteAndReturnErrorCode(sql
);
437 if (error
!= SQLITE_OK
)
438 error
= OnSqliteError(error
, NULL
);
440 // This needs to be a FATAL log because the error case of arriving here is
441 // that there's a malformed SQL statement. This can arise in development if
442 // a change alters the schema but not all queries adjust.
443 if (error
== SQLITE_ERROR
)
444 DLOG(FATAL
) << "SQL Error in " << sql
<< ", " << GetErrorMessage();
445 return error
== SQLITE_OK
;
448 bool Connection::ExecuteWithTimeout(const char* sql
, base::TimeDelta timeout
) {
450 DLOG_IF(FATAL
, !poisoned_
) << "Illegal use of connection without a db";
454 ScopedBusyTimeout
busy_timeout(db_
);
455 busy_timeout
.SetTimeout(timeout
);
459 bool Connection::HasCachedStatement(const StatementID
& id
) const {
460 return statement_cache_
.find(id
) != statement_cache_
.end();
463 scoped_refptr
<Connection::StatementRef
> Connection::GetCachedStatement(
464 const StatementID
& id
,
466 CachedStatementMap::iterator i
= statement_cache_
.find(id
);
467 if (i
!= statement_cache_
.end()) {
468 // Statement is in the cache. It should still be active (we're the only
469 // one invalidating cached statements, and we'll remove it from the cache
470 // if we do that. Make sure we reset it before giving out the cached one in
471 // case it still has some stuff bound.
472 DCHECK(i
->second
->is_valid());
473 sqlite3_reset(i
->second
->stmt());
477 scoped_refptr
<StatementRef
> statement
= GetUniqueStatement(sql
);
478 if (statement
->is_valid())
479 statement_cache_
[id
] = statement
; // Only cache valid statements.
483 scoped_refptr
<Connection::StatementRef
> Connection::GetUniqueStatement(
487 // Return inactive statement.
489 return new StatementRef(NULL
, NULL
, poisoned_
);
491 sqlite3_stmt
* stmt
= NULL
;
492 int rc
= sqlite3_prepare_v2(db_
, sql
, -1, &stmt
, NULL
);
493 if (rc
!= SQLITE_OK
) {
494 // This is evidence of a syntax error in the incoming SQL.
495 DLOG(FATAL
) << "SQL compile error " << GetErrorMessage();
497 // It could also be database corruption.
498 OnSqliteError(rc
, NULL
);
499 return new StatementRef(NULL
, NULL
, false);
501 return new StatementRef(this, stmt
, true);
504 scoped_refptr
<Connection::StatementRef
> Connection::GetUntrackedStatement(
505 const char* sql
) const {
506 // Return inactive statement.
508 return new StatementRef(NULL
, NULL
, poisoned_
);
510 sqlite3_stmt
* stmt
= NULL
;
511 int rc
= sqlite3_prepare_v2(db_
, sql
, -1, &stmt
, NULL
);
512 if (rc
!= SQLITE_OK
) {
513 // This is evidence of a syntax error in the incoming SQL.
514 DLOG(FATAL
) << "SQL compile error " << GetErrorMessage();
515 return new StatementRef(NULL
, NULL
, false);
517 return new StatementRef(NULL
, stmt
, true);
520 bool Connection::IsSQLValid(const char* sql
) {
523 DLOG_IF(FATAL
, !poisoned_
) << "Illegal use of connection without a db";
527 sqlite3_stmt
* stmt
= NULL
;
528 if (sqlite3_prepare_v2(db_
, sql
, -1, &stmt
, NULL
) != SQLITE_OK
)
531 sqlite3_finalize(stmt
);
535 bool Connection::DoesTableExist(const char* table_name
) const {
536 return DoesTableOrIndexExist(table_name
, "table");
539 bool Connection::DoesIndexExist(const char* index_name
) const {
540 return DoesTableOrIndexExist(index_name
, "index");
543 bool Connection::DoesTableOrIndexExist(
544 const char* name
, const char* type
) const {
545 const char* kSql
= "SELECT name FROM sqlite_master WHERE type=? AND name=?";
546 Statement
statement(GetUntrackedStatement(kSql
));
547 statement
.BindString(0, type
);
548 statement
.BindString(1, name
);
550 return statement
.Step(); // Table exists if any row was returned.
553 bool Connection::DoesColumnExist(const char* table_name
,
554 const char* column_name
) const {
555 std::string
sql("PRAGMA TABLE_INFO(");
556 sql
.append(table_name
);
559 Statement
statement(GetUntrackedStatement(sql
.c_str()));
560 while (statement
.Step()) {
561 if (!statement
.ColumnString(1).compare(column_name
))
567 int64
Connection::GetLastInsertRowId() const {
569 DLOG_IF(FATAL
, !poisoned_
) << "Illegal use of connection without a db";
572 return sqlite3_last_insert_rowid(db_
);
575 int Connection::GetLastChangeCount() const {
577 DLOG_IF(FATAL
, !poisoned_
) << "Illegal use of connection without a db";
580 return sqlite3_changes(db_
);
583 int Connection::GetErrorCode() const {
586 return sqlite3_errcode(db_
);
589 int Connection::GetLastErrno() const {
594 if (SQLITE_OK
!= sqlite3_file_control(db_
, NULL
, SQLITE_LAST_ERRNO
, &err
))
600 const char* Connection::GetErrorMessage() const {
602 return "sql::Connection has no connection.";
603 return sqlite3_errmsg(db_
);
606 bool Connection::OpenInternal(const std::string
& file_name
) {
610 DLOG(FATAL
) << "sql::Connection is already open.";
614 // If |poisoned_| is set, it means an error handler called
615 // RazeAndClose(). Until regular Close() is called, the caller
616 // should be treating the database as open, but is_open() currently
617 // only considers the sqlite3 handle's state.
618 // TODO(shess): Revise is_open() to consider poisoned_, and review
619 // to see if any non-testing code even depends on it.
620 DLOG_IF(FATAL
, poisoned_
) << "sql::Connection is already open.";
622 int err
= sqlite3_open(file_name
.c_str(), &db_
);
623 if (err
!= SQLITE_OK
) {
624 // Histogram failures specific to initial open for debugging
626 UMA_HISTOGRAM_ENUMERATION("Sqlite.OpenFailure", err
& 0xff, 50);
628 OnSqliteError(err
, NULL
);
634 // sqlite3_open() does not actually read the database file (unless a
635 // hot journal is found). Successfully executing this pragma on an
636 // existing database requires a valid header on page 1.
637 // TODO(shess): For now, just probing to see what the lay of the
638 // land is. If it's mostly SQLITE_NOTADB, then the database should
640 err
= ExecuteAndReturnErrorCode("PRAGMA auto_vacuum");
641 if (err
!= SQLITE_OK
)
642 UMA_HISTOGRAM_ENUMERATION("Sqlite.OpenProbeFailure", err
& 0xff, 50);
644 // Enable extended result codes to provide more color on I/O errors.
645 // Not having extended result codes is not a fatal problem, as
646 // Chromium code does not attempt to handle I/O errors anyhow. The
647 // current implementation always returns SQLITE_OK, the DCHECK is to
648 // quickly notify someone if SQLite changes.
649 err
= sqlite3_extended_result_codes(db_
, 1);
650 DCHECK_EQ(err
, SQLITE_OK
) << "Could not enable extended result codes";
652 // If indicated, lock up the database before doing anything else, so
653 // that the following code doesn't have to deal with locking.
654 // TODO(shess): This code is brittle. Find the cases where code
655 // doesn't request |exclusive_locking_| and audit that it does the
656 // right thing with SQLITE_BUSY, and that it doesn't make
657 // assumptions about who might change things in the database.
658 // http://crbug.com/56559
659 if (exclusive_locking_
) {
660 // TODO(shess): This should probably be a full CHECK(). Code
661 // which requests exclusive locking but doesn't get it is almost
662 // certain to be ill-tested.
663 if (!Execute("PRAGMA locking_mode=EXCLUSIVE"))
664 DLOG(FATAL
) << "Could not set locking mode: " << GetErrorMessage();
667 // http://www.sqlite.org/pragma.html#pragma_journal_mode
668 // DELETE (default) - delete -journal file to commit.
669 // TRUNCATE - truncate -journal file to commit.
670 // PERSIST - zero out header of -journal file to commit.
671 // journal_size_limit provides size to trim to in PERSIST.
672 // TODO(shess): Figure out if PERSIST and journal_size_limit really
673 // matter. In theory, it keeps pages pre-allocated, so if
674 // transactions usually fit, it should be faster.
675 ignore_result(Execute("PRAGMA journal_mode = PERSIST"));
676 ignore_result(Execute("PRAGMA journal_size_limit = 16384"));
678 const base::TimeDelta kBusyTimeout
=
679 base::TimeDelta::FromSeconds(kBusyTimeoutSeconds
);
681 if (page_size_
!= 0) {
682 // Enforce SQLite restrictions on |page_size_|.
683 DCHECK(!(page_size_
& (page_size_
- 1)))
684 << " page_size_ " << page_size_
<< " is not a power of two.";
685 const int kSqliteMaxPageSize
= 32768; // from sqliteLimit.h
686 DCHECK_LE(page_size_
, kSqliteMaxPageSize
);
687 const std::string sql
=
688 base::StringPrintf("PRAGMA page_size=%d", page_size_
);
689 if (!ExecuteWithTimeout(sql
.c_str(), kBusyTimeout
))
690 DLOG(FATAL
) << "Could not set page size: " << GetErrorMessage();
693 if (cache_size_
!= 0) {
694 const std::string sql
=
695 base::StringPrintf("PRAGMA cache_size=%d", cache_size_
);
696 if (!ExecuteWithTimeout(sql
.c_str(), kBusyTimeout
))
697 DLOG(FATAL
) << "Could not set cache size: " << GetErrorMessage();
700 if (!ExecuteWithTimeout("PRAGMA secure_delete=ON", kBusyTimeout
)) {
701 DLOG(FATAL
) << "Could not enable secure_delete: " << GetErrorMessage();
709 void Connection::DoRollback() {
710 Statement
rollback(GetCachedStatement(SQL_FROM_HERE
, "ROLLBACK"));
712 needs_rollback_
= false;
715 void Connection::StatementRefCreated(StatementRef
* ref
) {
716 DCHECK(open_statements_
.find(ref
) == open_statements_
.end());
717 open_statements_
.insert(ref
);
720 void Connection::StatementRefDeleted(StatementRef
* ref
) {
721 StatementRefSet::iterator i
= open_statements_
.find(ref
);
722 if (i
== open_statements_
.end())
723 DLOG(FATAL
) << "Could not find statement";
725 open_statements_
.erase(i
);
728 void Connection::AddTaggedHistogram(const std::string
& name
,
729 size_t sample
) const {
730 if (histogram_tag_
.empty())
733 // TODO(shess): The histogram macros create a bit of static storage
734 // for caching the histogram object. This code shouldn't execute
735 // often enough for such caching to be crucial. If it becomes an
736 // issue, the object could be cached alongside histogram_prefix_.
737 std::string full_histogram_name
= name
+ "." + histogram_tag_
;
738 base::HistogramBase
* histogram
=
739 base::SparseHistogram::FactoryGet(
741 base::HistogramBase::kUmaTargetedHistogramFlag
);
743 histogram
->Add(sample
);
746 int Connection::OnSqliteError(int err
, sql::Statement
*stmt
) {
747 UMA_HISTOGRAM_SPARSE_SLOWLY("Sqlite.Error", err
);
748 AddTaggedHistogram("Sqlite.Error", err
);
750 // Always log the error.
751 LOG(ERROR
) << "sqlite error " << err
752 << ", errno " << GetLastErrno()
753 << ": " << GetErrorMessage();
755 if (!error_callback_
.is_null()) {
756 error_callback_
.Run(err
, stmt
);
760 // TODO(shess): Remove |error_delegate_| once everything is
761 // converted to |error_callback_|.
762 if (error_delegate_
.get())
763 return error_delegate_
->OnError(err
, this, stmt
);
765 // The default handling is to assert on debug and to ignore on release.
766 DLOG(FATAL
) << GetErrorMessage();
770 // TODO(shess): Allow specifying integrity_check versus quick_check.
771 // TODO(shess): Allow specifying maximum results (default 100 lines).
772 bool Connection::IntegrityCheck(std::vector
<std::string
>* messages
) {
773 const char kSql
[] = "PRAGMA integrity_check";
774 sql::Statement
stmt(GetUniqueStatement(kSql
));
778 // The pragma appears to return all results (up to 100 by default)
779 // as a single string. This doesn't appear to be an API contract,
780 // it could return separate lines, so loop _and_ split.
781 while (stmt
.Step()) {
782 std::string
result(stmt
.ColumnString(0));
783 base::SplitString(result
, '\n', messages
);
785 return stmt
.Succeeded();