1 // Copyright (c) 2011 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 #ifndef CHROME_COMMON_SQLITE_UTILS_H_
6 #define CHROME_COMMON_SQLITE_UTILS_H_
7 #pragma once
8
9 #include <string>
10 #include <vector>
11
12 #include "base/basictypes.h"
13 #include "base/memory/scoped_ptr.h"
14 #include "base/string16.h"
15 #include "base/utf_string_conversions.h"
16 #include "third_party/sqlite/sqlite3.h"
17
18 // forward declarations of classes defined here
19 class FilePath;
20 class SQLTransaction;
21 class SQLNestedTransaction;
22 class SQLNestedTransactionSite;
23 class scoped_sqlite3_stmt_ptr;
24 class SQLStatement;
25
26 //------------------------------------------------------------------------------
27 // Interface to be implemented by objects that can handle exceptional sqlite
28 // conditions. This way client code can focus on handling normal condtions.
29 //------------------------------------------------------------------------------
30 class SQLErrorHandler {
31 public:
~SQLErrorHandler()32 virtual ~SQLErrorHandler() {}
33 // Handle a sqlite error. |error| is the return code an of sqlite operation
34 // which is considered an error. This handler is free to try repair, notify
35 // someone or even break into the debugger depending on the situation.
36 virtual int HandleError(int error, sqlite3* db) = 0;
37 // Returns the last value of |error| passed to HandleError.
38 virtual int GetLastError() const = 0;
39 };
40
41 //------------------------------------------------------------------------------
42 // The factory interface is used to create the different error handling
43 // strategies for debug, release and for diagnostic mode.
44 //------------------------------------------------------------------------------
45 class SQLErrorHandlerFactory {
46 public:
~SQLErrorHandlerFactory()47 virtual ~SQLErrorHandlerFactory() {}
48 virtual SQLErrorHandler* Make() = 0;
49 };
50
51 //------------------------------------------------------------------------------
52 // A wrapper for sqlite transactions that rollsback when the wrapper
53 // goes out of scope if the caller has not already called Commit or Rollback.
54 // Note: the constructor does NOT Begin a transaction.
55 //------------------------------------------------------------------------------
56 class SQLTransaction {
57 public:
58 explicit SQLTransaction(sqlite3* db);
59 virtual ~SQLTransaction();
60
Begin()61 int Begin() {
62 // By default, we BEGIN IMMEDIATE to establish file locks at the
63 // onset of a transaction. This avoids SQLITE_BUSY errors, without
64 // waiting for the busy timeout period, which can occur when BEGIN
65 // DEFERRED is used.
66 return BeginImmediate();
67 }
68
BeginExclusive()69 int BeginExclusive() {
70 return BeginCommand("BEGIN EXCLUSIVE");
71 }
72
BeginImmediate()73 int BeginImmediate() {
74 return BeginCommand("BEGIN IMMEDIATE");
75 }
76
BeginDeferred()77 int BeginDeferred() {
78 return BeginCommand("BEGIN DEFERRED");
79 }
80
Commit()81 int Commit() {
82 return EndCommand("COMMIT");
83 }
84
Rollback()85 int Rollback() {
86 return EndCommand("ROLLBACK");
87 }
88
HasBegun()89 bool HasBegun() {
90 return began_;
91 }
92
93 protected:
94 virtual int BeginCommand(const char* command);
95 virtual int EndCommand(const char* command);
96
97 sqlite3* db_;
98 bool began_;
99 DISALLOW_COPY_AND_ASSIGN(SQLTransaction);
100 };
101
102
103 //------------------------------------------------------------------------------
104 // A class for use with SQLNestedTransaction.
105 //------------------------------------------------------------------------------
106 class SQLNestedTransactionSite {
107 protected:
SQLNestedTransactionSite()108 SQLNestedTransactionSite() : db_(NULL), top_transaction_(NULL) {}
109 virtual ~SQLNestedTransactionSite();
110
111 // The following virtual methods provide notification of true transaction
112 // boundaries as they are crossed by a top nested transaction.
113 // Intended to be overriden (See WebCacheDB)
114 // SQLNestedTransaction calls these after the underlying database
115 // operation has been performed.
116
OnBegin()117 virtual void OnBegin() {}
OnCommit()118 virtual void OnCommit() {}
OnRollback()119 virtual void OnRollback() {}
120
121 // Returns the sqlite3 database connection associated with this site
122 // Used by SQLNestedTransaction
GetSqlite3DB()123 sqlite3* GetSqlite3DB() { return db_; }
124
125 // Returns the current top nested transaction associated with this site
126 // Used by SQLNestedTransaction
GetTopTransaction()127 SQLNestedTransaction* GetTopTransaction() {
128 return top_transaction_;
129 }
130
131 // Sets or clears the top nested transaction associated with this site
132 // Used by SQLNestedTransaction
133 void SetTopTransaction(SQLNestedTransaction* top);
134
135 sqlite3* db_;
136 SQLNestedTransaction* top_transaction_;
137 friend class SQLNestedTransaction;
138 };
139
140 //------------------------------------------------------------------------------
141 // SQLite does not support nested transactions. This class provides a gross
142 // approximation of nested transactions.
143 //
144 // Really there is only one transaction, the top transaction.
145 //
146 // A nested transaction commits with respect to the top transaction.
147 // That is, even though the nested transaction commits, the permanence of its
148 // effects depends on the top transaction committing. If the top
149 // transaction rollsback, the results of the nested transaction are backed out.
150 // If any nested transaction aborts, the top transaction ultimately rollsback
151 // as well.
152 //
153 // Note: If a nested transaction is open for a particular db connection, an
154 // attempt to open a non-nested transaction (class SQLTransaction) will fail.
155 // And vice versa.
156 //
157 // TODO(michaeln): demonstrate usage here
158 // TODO(michaeln): safegaurds to prevent mis-use
159 //------------------------------------------------------------------------------
160 class SQLNestedTransaction : public SQLTransaction {
161 public:
162 explicit SQLNestedTransaction(SQLNestedTransactionSite* site);
163 virtual ~SQLNestedTransaction();
164
165 protected:
166 virtual int BeginCommand(const char* command);
167 virtual int EndCommand(const char* command);
168
169 private:
170 bool needs_rollback_;
171 SQLNestedTransactionSite* site_;
172 DISALLOW_COPY_AND_ASSIGN(SQLNestedTransaction);
173 };
174
175 //------------------------------------------------------------------------------
176 // A scoped sqlite statement that finalizes when it goes out of scope.
177 //------------------------------------------------------------------------------
178 class scoped_sqlite3_stmt_ptr {
179 public:
~scoped_sqlite3_stmt_ptr()180 ~scoped_sqlite3_stmt_ptr() {
181 finalize();
182 }
183
scoped_sqlite3_stmt_ptr()184 scoped_sqlite3_stmt_ptr() : stmt_(NULL) {
185 }
186
scoped_sqlite3_stmt_ptr(sqlite3_stmt * stmt)187 explicit scoped_sqlite3_stmt_ptr(sqlite3_stmt* stmt)
188 : stmt_(stmt) {
189 }
190
get()191 sqlite3_stmt* get() const {
192 return stmt_;
193 }
194
set(sqlite3_stmt * stmt)195 void set(sqlite3_stmt* stmt) {
196 finalize();
197 stmt_ = stmt;
198 }
199
release()200 sqlite3_stmt* release() {
201 sqlite3_stmt* tmp = stmt_;
202 stmt_ = NULL;
203 return tmp;
204 }
205
206 // It is not safe to call sqlite3_finalize twice on the same stmt.
207 // Sqlite3's sqlite3_finalize() function should not be called directly
208 // without calling the release method. If sqlite3_finalize() must be
209 // called directly, the following usage is advised:
210 // scoped_sqlite3_stmt_ptr stmt;
211 // ... do something with stmt ...
212 // sqlite3_finalize(stmt.release());
finalize()213 int finalize() {
214 int err = sqlite3_finalize(stmt_);
215 stmt_ = NULL;
216 return err;
217 }
218
219 protected:
220 sqlite3_stmt* stmt_;
221
222 private:
223 DISALLOW_COPY_AND_ASSIGN(scoped_sqlite3_stmt_ptr);
224 };
225
226 //------------------------------------------------------------------------------
227 // A scoped sqlite statement with convenient C++ wrappers for sqlite3 APIs.
228 //------------------------------------------------------------------------------
229 class SQLStatement : public scoped_sqlite3_stmt_ptr {
230 public:
SQLStatement()231 SQLStatement() {}
232
prepare(sqlite3 * db,const char * sql)233 int prepare(sqlite3* db, const char* sql) {
234 return prepare(db, sql, -1);
235 }
236
237 int prepare(sqlite3* db, const char* sql, int sql_len);
238
239 int step();
240 int reset();
241 sqlite_int64 last_insert_rowid();
242 int changes();
243 sqlite3* db_handle();
244
245 //
246 // Parameter binding helpers (NOTE: index is 0-based)
247 //
248
249 int bind_parameter_count();
250
251 typedef void (*Function)(void*);
252
253 int bind_blob(int index, std::vector<unsigned char>* blob);
254 int bind_blob(int index, const void* value, int value_len);
255 int bind_blob(int index, const void* value, int value_len, Function dtor);
256 int bind_double(int index, double value);
257 int bind_bool(int index, bool value);
258 int bind_int(int index, int value);
259 int bind_int64(int index, sqlite_int64 value);
260 int bind_null(int index);
261
bind_string(int index,const std::string & value)262 int bind_string(int index, const std::string& value) {
263 // don't use c_str so it doesn't have to fix up the null terminator
264 // (sqlite just uses the length)
265 return bind_text(index, value.data(),
266 static_cast<int>(value.length()), SQLITE_TRANSIENT);
267 }
268
bind_string16(int index,const string16 & value)269 int bind_string16(int index, const string16& value) {
270 // don't use c_str so it doesn't have to fix up the null terminator
271 // (sqlite just uses the length)
272 std::string value_utf8(UTF16ToUTF8(value));
273 return bind_text(index, value_utf8.data(),
274 static_cast<int>(value_utf8.length()), SQLITE_TRANSIENT);
275 }
276
bind_wstring(int index,const std::wstring & value)277 int bind_wstring(int index, const std::wstring& value) {
278 // don't use c_str so it doesn't have to fix up the null terminator
279 // (sqlite just uses the length)
280 std::string value_utf8(WideToUTF8(value));
281 return bind_text(index, value_utf8.data(),
282 static_cast<int>(value_utf8.length()), SQLITE_TRANSIENT);
283 }
284
bind_text(int index,const char * value)285 int bind_text(int index, const char* value) {
286 return bind_text(index, value, -1, SQLITE_TRANSIENT);
287 }
288
289 // value_len is number of characters or may be negative
290 // a for null-terminated value string
bind_text(int index,const char * value,int value_len)291 int bind_text(int index, const char* value, int value_len) {
292 return bind_text(index, value, value_len, SQLITE_TRANSIENT);
293 }
294
295 // value_len is number of characters or may be negative
296 // a for null-terminated value string
297 int bind_text(int index, const char* value, int value_len,
298 Function dtor);
299
bind_text16(int index,const char16 * value)300 int bind_text16(int index, const char16* value) {
301 return bind_text16(index, value, -1, SQLITE_TRANSIENT);
302 }
303
304 // value_len is number of characters or may be negative
305 // a for null-terminated value string
bind_text16(int index,const char16 * value,int value_len)306 int bind_text16(int index, const char16* value, int value_len) {
307 return bind_text16(index, value, value_len, SQLITE_TRANSIENT);
308 }
309
310 // value_len is number of characters or may be negative
311 // a for null-terminated value string
312 int bind_text16(int index, const char16* value, int value_len,
313 Function dtor);
314
315 int bind_value(int index, const sqlite3_value* value);
316
317 //
318 // Column helpers (NOTE: index is 0-based)
319 //
320
321 int column_count();
322 int column_type(int index);
323 const void* column_blob(int index);
324 bool column_blob_as_vector(int index, std::vector<unsigned char>* blob);
325 bool column_blob_as_string(int index, std::string* blob);
326 int column_bytes(int index);
327 int column_bytes16(int index);
328 double column_double(int index);
329 bool column_bool(int index);
330 int column_int(int index);
331 sqlite_int64 column_int64(int index);
332 const char* column_text(int index);
333 bool column_string(int index, std::string* str);
334 std::string column_string(int index);
335 const char16* column_text16(int index);
336 bool column_string16(int index, string16* str);
337 string16 column_string16(int index);
338 bool column_wstring(int index, std::wstring* str);
339 std::wstring column_wstring(int index);
340
341 private:
342 DISALLOW_COPY_AND_ASSIGN(SQLStatement);
343 };
344
345 namespace sqlite_utils {
346
347 //------------------------------------------------------------------------------
348 // A scoped sqlite database that closes when it goes out of scope.
349 //------------------------------------------------------------------------------
350 class DBClose {
351 public:
operator()352 inline void operator()(sqlite3* x) const {
353 sqlite3_close(x);
354 }
355 };
356
357 typedef scoped_ptr_malloc<sqlite3, DBClose> scoped_sqlite_db_ptr;
358
359 // Opens the DB in the file pointed to by |filepath|. This method forces the
360 // database to be in UTF-8 mode on all platforms. See
361 // http://www.sqlite.org/capi3ref.html#sqlite3_open for an explanation of the
362 // return value.
363 int OpenSqliteDb(const FilePath& filepath, sqlite3** database);
364
365 // Returns true if there is a table with the given name in the database.
366 // For the version where a database name is specified, it may be NULL or the
367 // empty string if no database name is necessary.
368 bool DoesSqliteTableExist(sqlite3* db,
369 const char* db_name,
370 const char* table_name);
DoesSqliteTableExist(sqlite3 * db,const char * table_name)371 inline bool DoesSqliteTableExist(sqlite3* db, const char* table_name) {
372 return DoesSqliteTableExist(db, NULL, table_name);
373 }
374
375 // Test whether a table has a column matching the provided name and type.
376 // Returns true if the column exist and false otherwise. There are two
377 // versions, one that takes a database name, the other that doesn't. The
378 // database name can be NULL or empty if no name is desired.
379 //
380 // Column type is optional, it can be NULL or empty. If specified, we the
381 // function will check that the column is of the correct type (case-sensetive).
382 bool DoesSqliteColumnExist(sqlite3* db,
383 const char* datbase_name,
384 const char* table_name,
385 const char* column_name,
386 const char* column_type);
DoesSqliteColumnExist(sqlite3 * db,const char * table_name,const char * column_name,const char * column_type)387 inline bool DoesSqliteColumnExist(sqlite3* db,
388 const char* table_name,
389 const char* column_name,
390 const char* column_type) {
391 return DoesSqliteColumnExist(db, NULL, table_name, column_name, column_type);
392 }
393
394 // Test whether a table has one or more rows. Returns true if the table
395 // has one or more rows and false if the table is empty or doesn't exist.
396 bool DoesSqliteTableHaveRow(sqlite3* db, const char* table_name);
397
398 } // namespace sqlite_utils
399
400 #endif // CHROME_COMMON_SQLITE_UTILS_H_
401