1
// Copyright 2007, Google Inc.
3
// Redistribution and use in source and binary forms, with or without
4
// modification, are permitted provided that the following conditions are met:
6
// 1. Redistributions of source code must retain the above copyright notice,
7
// this list of conditions and the following disclaimer.
8
// 2. Redistributions in binary form must reproduce the above copyright notice,
9
// this list of conditions and the following disclaimer in the documentation
10
// and/or other materials provided with the distribution.
11
// 3. Neither the name of Google Inc. nor the names of its contributors may be
12
// used to endorse or promote products derived from this software without
13
// specific prior written permission.
15
// THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED
16
// WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
17
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO
18
// EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
19
// SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
20
// PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
21
// OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
22
// WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
23
// OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
24
// ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
27
#include "gears/base/common/file.h"
28
#include "gears/base/common/mutex.h"
29
#include "gears/base/common/paths.h"
30
#include "gears/base/common/sqlite_wrapper.h"
31
#include "gears/base/common/stopwatch.h"
32
#include "gears/base/common/string_utils.h"
33
#include "gears/base/common/thread_locals.h"
36
const char *SQLDatabase::kUnspecifiedTransactionLabel = "Unspecified";
38
// Returns a static string describing any SQLite status code.
39
// Based on SQLite's internal function sqlite3ErrStr().
40
const char16* SqliteRetvalAsString(int status) {
42
switch (status & 0xFF) {
45
case SQLITE_OK: z = STRING16(L"not an error"); break;
46
case SQLITE_ERROR: z = STRING16(L"SQL logic error or missing database"); break;
47
case SQLITE_PERM: z = STRING16(L"access permission denied"); break;
48
case SQLITE_ABORT: z = STRING16(L"callback requested query abort"); break;
49
case SQLITE_BUSY: z = STRING16(L"database is locked"); break;
50
case SQLITE_LOCKED: z = STRING16(L"database table is locked"); break;
51
case SQLITE_NOMEM: z = STRING16(L"out of memory"); break;
52
case SQLITE_READONLY: z = STRING16(L"attempt to write a readonly database"); break;
53
case SQLITE_INTERRUPT: z = STRING16(L"interrupted"); break;
54
case SQLITE_IOERR: z = STRING16(L"disk I/O error"); break;
55
case SQLITE_CORRUPT: z = STRING16(L"database disk image is malformed"); break;
56
case SQLITE_FULL: z = STRING16(L"database or disk is full"); break;
57
case SQLITE_CANTOPEN: z = STRING16(L"unable to open database file"); break;
58
case SQLITE_PROTOCOL: z = STRING16(L"database locking protocol failure"); break;
59
case SQLITE_EMPTY: z = STRING16(L"table contains no data"); break;
60
case SQLITE_SCHEMA: z = STRING16(L"database schema has changed"); break;
61
case SQLITE_CONSTRAINT: z = STRING16(L"constraint failed"); break;
62
case SQLITE_MISMATCH: z = STRING16(L"datatype mismatch"); break;
63
case SQLITE_MISUSE: z = STRING16(L"library routine called out of sequence"); break;
64
case SQLITE_NOLFS: z = STRING16(L"kernel lacks large file support"); break;
65
case SQLITE_AUTH: z = STRING16(L"authorization denied"); break;
66
case SQLITE_FORMAT: z = STRING16(L"auxiliary database format error"); break;
67
case SQLITE_RANGE: z = STRING16(L"bind or column index out of range"); break;
68
case SQLITE_NOTADB: z = STRING16(L"file is encrypted or is not a database"); break;
69
default: z = STRING16(L"unknown error"); break;
75
void BuildSqliteErrorString(const char16 *summary, int sql_status, sqlite3 *db,
79
out->append(STRING16(L" ERROR: "));
80
out->append(SqliteRetvalAsString(sql_status));
81
out->append(STRING16(L" DETAILS: "));
82
out->append(STRING16(sqlite3_errmsg16(db)));
86
static void LogIfConspicuouslyLongTime(const char *format_str,
89
const int64 kConspicuoslyLongTimeMsec = 5 * 1000;
90
int64 duration = GetCurrentTimeMillis() - start_time;
91
if (duration > kConspicuoslyLongTimeMsec) {
92
LOG((format_str, static_cast<int>(duration), label));
96
//------------------------------------------------------------------------------
97
// Constructor and Destructor
98
//------------------------------------------------------------------------------
100
SQLDatabase::SQLDatabase() :
101
db_(NULL), transaction_count_(0), needs_rollback_(false),
102
transaction_start_time_(0), opt_transaction_mutex_(NULL),
103
transaction_listener_(NULL) {
107
SQLDatabase::~SQLDatabase() {
111
//------------------------------------------------------------------------------
113
//------------------------------------------------------------------------------
114
bool SQLDatabase::Open(const char16 *name) {
115
ASSERT_SINGLE_THREAD();
117
// When parameter binding multiple parameters, we frequently use a scheme
118
// of OR'ing return values together for testing for an error once after
119
// all rv |= bind_foo() assignments have been made. This relies on
120
// SQLITE_OK being 0.
121
assert(SQLITE_OK == 0);
123
// For testing purposes, we've seperated opening and configuring the
124
// sqlite3 connection.
125
if (!OpenConnection(name)) {
129
if (!ConfigureConnection()) {
138
bool SQLDatabase::OpenConnection(const char16 *name) {
139
// Nobody should be calling Open() twice.
142
LOG(("SQLDatabase: already open\n"));
146
transaction_count_ = 0;
147
needs_rollback_ = false;
150
if (!GetFullDatabaseFilePath(name, &path)) {
154
if (SQLITE_OK != sqlite3_open16(path.c_str(), &db_)) {
155
// sqlite3_close() should be called after sqlite3_open() failures.
156
// The DB handle may be valid or NULL, sqlite3_close() handles
166
bool SQLDatabase::ConfigureConnection() {
168
// Set the busy timeout value before executing any SQL statements.
169
// With the timeout value set, SQLite will wait and retry if another
170
// thread has the database locked rather than immediately fail with an
171
// SQLITE_BUSY error.
172
if (SQLITE_OK != sqlite3_busy_timeout(db_, kBusyTimeout)) {
173
LOG(("SQLDatabase: Could not set busy timeout: %d\n",
174
sqlite3_errcode(db_)));
178
// Turn off flushing writes thru to disk, significantly faster (2x)
180
sqlite3_exec(db_, "PRAGMA synchronous = OFF" , NULL, NULL, NULL)) {
181
LOG(("SQLDatabase: Could not set PRAGMA synchronous: %d\n",
182
sqlite3_errcode(db_)));
186
// Use UTF8, significantly smaller
188
sqlite3_exec(db_, "PRAGMA encoding = \"UTF-8\"", NULL, NULL, NULL)) {
189
LOG(("SQLDatabase: Could not set PRAGMA encoding: %d\n",
190
sqlite3_errcode(db_)));
198
//------------------------------------------------------------------------------
200
//------------------------------------------------------------------------------
201
void SQLDatabase::Close() {
205
if (IsInTransaction() && opt_transaction_mutex_) {
206
opt_transaction_mutex_->Unlock();
212
//------------------------------------------------------------------------------
214
//------------------------------------------------------------------------------
215
bool SQLDatabase::IsOpen() {
216
ASSERT_SINGLE_THREAD();
221
//------------------------------------------------------------------------------
223
//------------------------------------------------------------------------------
224
bool SQLDatabase::BeginTransaction(const char *log_label) {
225
ASSERT_SINGLE_THREAD();
232
log_label = kUnspecifiedTransactionLabel;
234
// EndTransaction should have been watching out for us going negative.
235
assert(transaction_count_ >= 0);
237
if (transaction_count_ > 0) {
238
if (needs_rollback_) {
239
LOG(("SQLDatabase: Cannot begin transaction for %s"
240
" - already rolled back\n",
244
++transaction_count_;
249
LOG(("SQLDatabase: BeginTransaction for %s\n", log_label));
250
transaction_start_time_ = GetCurrentTimeMillis();
252
if (opt_transaction_mutex_) {
253
opt_transaction_mutex_->Lock();
256
// We always use BEGIN IMMEDIATE for now but this could be parameterized in
257
// the future if necessary.
258
if (SQLITE_OK != sqlite3_exec(db_, "BEGIN IMMEDIATE", NULL, NULL, NULL)) {
259
if (opt_transaction_mutex_) {
260
opt_transaction_mutex_->Unlock();
262
LOG(("SQLDatabase: Cannot exceute BEGIN IMMEDIATE: %d for %s\n",
263
sqlite3_errcode(db_),
268
LogIfConspicuouslyLongTime(
269
"SQLDatabase: Warning, BEGIN IMMEDIATE took %d ms for %s\n",
270
transaction_start_time_, log_label);
272
needs_rollback_ = false;
273
++transaction_count_;
275
if (transaction_listener_) {
276
transaction_listener_->OnBegin();
283
//------------------------------------------------------------------------------
284
// RollbackTransaction
285
//------------------------------------------------------------------------------
286
void SQLDatabase::RollbackTransaction(const char *log_label) {
287
ASSERT_SINGLE_THREAD();
288
needs_rollback_ = true;
289
EndTransaction(log_label);
293
//------------------------------------------------------------------------------
295
//------------------------------------------------------------------------------
296
bool SQLDatabase::CommitTransaction(const char *log_label) {
297
ASSERT_SINGLE_THREAD();
299
if (!EndTransaction(log_label)) {
303
return !needs_rollback_;
307
//------------------------------------------------------------------------------
309
//------------------------------------------------------------------------------
310
bool SQLDatabase::EndTransaction(const char *log_label) {
311
ASSERT_SINGLE_THREAD();
314
log_label = kUnspecifiedTransactionLabel;
316
if (0 == transaction_count_) {
317
LOG(("SQLDatabase: unbalanced transaction - %s\n", log_label));
322
// Always decrement. If Commit() fails we will Rollback(), which cannot fail.
323
--transaction_count_;
330
if (transaction_count_ > 0) {
331
// This is not the top tx, nothing to do
335
LOG(("SQLDatabase: EndTransaction for %s\n", log_label));
337
// OK, we are closing the last transaction, commit provided rollback has
339
if (!needs_rollback_) {
340
if (SQLITE_OK == sqlite3_exec(db_, "COMMIT", NULL, NULL, NULL)) {
341
if (opt_transaction_mutex_) {
342
opt_transaction_mutex_->Unlock();
344
LogIfConspicuouslyLongTime(
345
"SQLDatabase: Committed transaction was open for %d ms for %s\n",
346
transaction_start_time_, log_label);
348
if (transaction_listener_) {
349
transaction_listener_->OnCommit();
354
LOG(("SQLDatabase: Could not execute COMMIT: %d\n",
355
sqlite3_errcode(db_)));
357
// Since commit did not succeed, we should rollback. For most types of
358
// errors, sqlite has already rolled back at this point. But for
359
// SQLITE_BUSY, it won't have, and we want to treat that as an error.
362
// Rollback is necessary.
363
// TODO(aa): What, if any, are the cases that rollback can fail. Should we do
364
// anything about them?
365
sqlite3_exec(db_, "ROLLBACK", NULL, NULL, NULL);
366
if (opt_transaction_mutex_) {
367
opt_transaction_mutex_->Unlock();
369
LogIfConspicuouslyLongTime(
370
"SQLDatabase: Rolled back transaction was open for %d ms for %s\n",
371
transaction_start_time_, log_label);
373
if (transaction_listener_) {
374
transaction_listener_->OnRollback();
377
LOG(("SQLDatabase: Rolled back transaction for %s\n", log_label));
382
//------------------------------------------------------------------------------
384
//------------------------------------------------------------------------------
385
bool SQLDatabase::DropAllObjects() {
386
// It appears that when you drop a table, it's associated indicies and
387
// triggers also get dropped. However, shess cautions that there have been
388
// bugs where virtual tables could not be dropped after the tables they
389
// depedended upon had been dropped. This means that if we ever start using
390
// virtual tables with this class, we may need to revisit this
391
// implementation and find a way to distinguish virtual tables from regular
392
// ones and drop those first.
393
SQLTransaction tx(this, "SQLDatabase::DropAllObjects");
398
// Find all the tables and gather them into a list
400
const char16 *select_sql =
401
STRING16(L"SELECT name FROM sqlite_master WHERE type = 'table'");
402
if (SQLITE_OK != stmt.prepare16(this, select_sql)) {
403
LOG(("SQLDatabase::DropAllObjects - error preparing select: %d\n",
404
sqlite3_errcode(db_)));
408
std::vector<std::string16> table_names;
409
int rv = stmt.step();
410
while (SQLITE_ROW == rv) {
411
table_names.push_back(std::string16(stmt.column_text16_safe(0)));
415
if (SQLITE_DONE != rv) {
416
LOG(("SQLDatabase::DropAllObjects - error iterating objects: %d\n",
417
sqlite3_errcode(db_)));
421
// Now iterate the list and drop all the tables we found
422
for (std::vector<std::string16>::iterator iter = table_names.begin();
423
iter != table_names.end(); ++iter) {
424
SQLStatement drop_stmt;
425
std::string16 drop_sql(STRING16(L"DROP TABLE "));
427
if (SQLITE_OK != drop_stmt.prepare16(this, drop_sql.c_str())) {
428
// Some tables internal to sqlite may not be dropped, for example
429
// sqlite_sequence. We ignore this error.
430
if (StartsWith(*iter, std::string16(STRING16(L"sqlite_sequence"))))
435
if (SQLITE_DONE != drop_stmt.step()) {
436
LOG(("SQLDatabase::DropAllObjects - error dropping table: %d\n",
437
sqlite3_errcode(db_)));
450
//------------------------------------------------------------------------------
452
//------------------------------------------------------------------------------
453
sqlite3 *SQLDatabase::GetDBHandle() {
454
ASSERT_SINGLE_THREAD();
460
//------------------------------------------------------------------------------
461
// SetTransactionListener
462
//------------------------------------------------------------------------------
463
void SQLDatabase::SetTransactionListener(SQLTransactionListener *listener) {
464
ASSERT_SINGLE_THREAD();
466
transaction_listener_ = listener;
470
//------------------------------------------------------------------------------
471
// GetFullDatabaseFilePath
472
//------------------------------------------------------------------------------
474
bool SQLDatabase::GetFullDatabaseFilePath(const char16 *filename,
475
std::string16 *path) {
476
if (!GetBaseDataDirectory(path)) {
479
if (!File::RecursivelyCreateDir(path->c_str())) {
482
(*path) += kPathSeparator;