1ddb351dbec246cf1fab5ec20d2d5520909041de1Kristian Monsen// Copyright (c) 2011 The Chromium Authors. All rights reserved. 206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Use of this source code is governed by a BSD-style license that can be 306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// found in the LICENSE file. 406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch#ifndef CHROME_COMMON_SQLITE_UTILS_H_ 606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch#define CHROME_COMMON_SQLITE_UTILS_H_ 73345a6884c488ff3a535c2c9acdd33d74b37e311Iain Merrick#pragma once 806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch#include <string> 1006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch#include <vector> 1106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 1206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch#include "base/basictypes.h" 13ddb351dbec246cf1fab5ec20d2d5520909041de1Kristian Monsen#include "base/memory/scoped_ptr.h" 1406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch#include "base/string16.h" 1506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch#include "base/utf_string_conversions.h" 163345a6884c488ff3a535c2c9acdd33d74b37e311Iain Merrick#include "third_party/sqlite/sqlite3.h" 1706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 1806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// forward declarations of classes defined here 1906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass FilePath; 2006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLTransaction; 2106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLNestedTransaction; 2206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLNestedTransactionSite; 2306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass scoped_sqlite3_stmt_ptr; 2406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLStatement; 2506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 2606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 2706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Interface to be implemented by objects that can handle exceptional sqlite 2806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// conditions. This way client code can focus on handling normal condtions. 2906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 3006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLErrorHandler { 3106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch public: 3206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual ~SQLErrorHandler() {} 3306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Handle a sqlite error. |error| is the return code an of sqlite operation 3406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // which is considered an error. This handler is free to try repair, notify 3506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // someone or even break into the debugger depending on the situation. 3606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual int HandleError(int error, sqlite3* db) = 0; 3706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Returns the last value of |error| passed to HandleError. 3806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual int GetLastError() const = 0; 3906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch}; 4006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 4106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 4206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// The factory interface is used to create the different error handling 4306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// strategies for debug, release and for diagnostic mode. 4406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 4506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLErrorHandlerFactory { 4606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch public: 4706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual ~SQLErrorHandlerFactory() {} 4806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual SQLErrorHandler* Make() = 0; 4906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch}; 5006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 5106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 5206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// A wrapper for sqlite transactions that rollsback when the wrapper 5306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// goes out of scope if the caller has not already called Commit or Rollback. 5406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Note: the constructor does NOT Begin a transaction. 5506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 5606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLTransaction { 5706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch public: 5806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch explicit SQLTransaction(sqlite3* db); 5906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual ~SQLTransaction(); 6006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 6106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int Begin() { 6206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // By default, we BEGIN IMMEDIATE to establish file locks at the 6306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // onset of a transaction. This avoids SQLITE_BUSY errors, without 6406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // waiting for the busy timeout period, which can occur when BEGIN 6506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // DEFERRED is used. 6606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return BeginImmediate(); 6706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 6806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 6906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int BeginExclusive() { 7006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return BeginCommand("BEGIN EXCLUSIVE"); 7106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 7206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 7306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int BeginImmediate() { 7406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return BeginCommand("BEGIN IMMEDIATE"); 7506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 7606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 7706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int BeginDeferred() { 7806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return BeginCommand("BEGIN DEFERRED"); 7906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 8006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 8106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int Commit() { 8206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return EndCommand("COMMIT"); 8306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 8406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 8506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int Rollback() { 8606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return EndCommand("ROLLBACK"); 8706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 8806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 8906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool HasBegun() { 9006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return began_; 9106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 9206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 9306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch protected: 9406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual int BeginCommand(const char* command); 9506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual int EndCommand(const char* command); 9606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 9706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3* db_; 9806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool began_; 9906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch DISALLOW_COPY_AND_ASSIGN(SQLTransaction); 10006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch}; 10106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 10206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 10306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 10406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// A class for use with SQLNestedTransaction. 10506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 10606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLNestedTransactionSite { 10706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch protected: 10806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch SQLNestedTransactionSite() : db_(NULL), top_transaction_(NULL) {} 10906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual ~SQLNestedTransactionSite(); 11006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 11106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // The following virtual methods provide notification of true transaction 11206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // boundaries as they are crossed by a top nested transaction. 11306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Intended to be overriden (See WebCacheDB) 11406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // SQLNestedTransaction calls these after the underlying database 11506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // operation has been performed. 11606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 11706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual void OnBegin() {} 11806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual void OnCommit() {} 11906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual void OnRollback() {} 12006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 12106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Returns the sqlite3 database connection associated with this site 12206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Used by SQLNestedTransaction 12306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3* GetSqlite3DB() { return db_; } 12406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 12506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Returns the current top nested transaction associated with this site 12606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Used by SQLNestedTransaction 12706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch SQLNestedTransaction* GetTopTransaction() { 12806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return top_transaction_; 12906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 13006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 13106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Sets or clears the top nested transaction associated with this site 13206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Used by SQLNestedTransaction 13306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch void SetTopTransaction(SQLNestedTransaction* top); 13406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 13506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3* db_; 13606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch SQLNestedTransaction* top_transaction_; 13706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch friend class SQLNestedTransaction; 13806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch}; 13906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 14006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 14106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// SQLite does not support nested transactions. This class provides a gross 14206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// approximation of nested transactions. 14306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// 14406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Really there is only one transaction, the top transaction. 14506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// 14606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// A nested transaction commits with respect to the top transaction. 14706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// That is, even though the nested transaction commits, the permanence of its 14806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// effects depends on the top transaction committing. If the top 14906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// transaction rollsback, the results of the nested transaction are backed out. 15006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// If any nested transaction aborts, the top transaction ultimately rollsback 15106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// as well. 15206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// 15306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Note: If a nested transaction is open for a particular db connection, an 15406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// attempt to open a non-nested transaction (class SQLTransaction) will fail. 15506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// And vice versa. 15606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// 15706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// TODO(michaeln): demonstrate usage here 15806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// TODO(michaeln): safegaurds to prevent mis-use 15906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 16006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLNestedTransaction : public SQLTransaction { 16106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch public: 16206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch explicit SQLNestedTransaction(SQLNestedTransactionSite* site); 16306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual ~SQLNestedTransaction(); 16406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 16506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch protected: 16606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual int BeginCommand(const char* command); 16706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch virtual int EndCommand(const char* command); 16806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 16906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch private: 17006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool needs_rollback_; 17106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch SQLNestedTransactionSite* site_; 17206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch DISALLOW_COPY_AND_ASSIGN(SQLNestedTransaction); 17306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch}; 17406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 17506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 17606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// A scoped sqlite statement that finalizes when it goes out of scope. 17706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 17806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass scoped_sqlite3_stmt_ptr { 17906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch public: 18006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch ~scoped_sqlite3_stmt_ptr() { 18106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch finalize(); 18206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 18306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 18406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch scoped_sqlite3_stmt_ptr() : stmt_(NULL) { 18506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 18606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 18706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch explicit scoped_sqlite3_stmt_ptr(sqlite3_stmt* stmt) 18806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch : stmt_(stmt) { 18906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 19006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 19106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3_stmt* get() const { 19206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return stmt_; 19306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 19406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 19506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch void set(sqlite3_stmt* stmt) { 19606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch finalize(); 19706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch stmt_ = stmt; 19806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 19906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 20006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3_stmt* release() { 20106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3_stmt* tmp = stmt_; 20206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch stmt_ = NULL; 20306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return tmp; 20406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 20506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 20606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // It is not safe to call sqlite3_finalize twice on the same stmt. 20706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Sqlite3's sqlite3_finalize() function should not be called directly 20806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // without calling the release method. If sqlite3_finalize() must be 20906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // called directly, the following usage is advised: 21006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // scoped_sqlite3_stmt_ptr stmt; 21106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // ... do something with stmt ... 21206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // sqlite3_finalize(stmt.release()); 21306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int finalize() { 21406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int err = sqlite3_finalize(stmt_); 21506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch stmt_ = NULL; 21606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return err; 21706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 21806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 21906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch protected: 22006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3_stmt* stmt_; 22106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 22206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch private: 22306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch DISALLOW_COPY_AND_ASSIGN(scoped_sqlite3_stmt_ptr); 22406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch}; 22506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 22606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 22706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// A scoped sqlite statement with convenient C++ wrappers for sqlite3 APIs. 22806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 22906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass SQLStatement : public scoped_sqlite3_stmt_ptr { 23006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch public: 23106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch SQLStatement() {} 23206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 23306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int prepare(sqlite3* db, const char* sql) { 23406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return prepare(db, sql, -1); 23506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 23606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 23706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int prepare(sqlite3* db, const char* sql, int sql_len); 23806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 23906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int step(); 24006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int reset(); 24106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite_int64 last_insert_rowid(); 24206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int changes(); 24306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3* db_handle(); 24406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 24506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // 24606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Parameter binding helpers (NOTE: index is 0-based) 24706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // 24806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 24906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_parameter_count(); 25006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 25106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch typedef void (*Function)(void*); 25206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 25306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_blob(int index, std::vector<unsigned char>* blob); 25406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_blob(int index, const void* value, int value_len); 25506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_blob(int index, const void* value, int value_len, Function dtor); 25606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_double(int index, double value); 25706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_bool(int index, bool value); 25806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_int(int index, int value); 25906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_int64(int index, sqlite_int64 value); 26006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_null(int index); 26106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 26206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_string(int index, const std::string& value) { 26306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // don't use c_str so it doesn't have to fix up the null terminator 26406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // (sqlite just uses the length) 26506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return bind_text(index, value.data(), 26606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch static_cast<int>(value.length()), SQLITE_TRANSIENT); 26706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 26806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 26906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_string16(int index, const string16& value) { 27006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // don't use c_str so it doesn't have to fix up the null terminator 27106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // (sqlite just uses the length) 27206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch std::string value_utf8(UTF16ToUTF8(value)); 27306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return bind_text(index, value_utf8.data(), 27406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch static_cast<int>(value_utf8.length()), SQLITE_TRANSIENT); 27506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 27606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 27706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_wstring(int index, const std::wstring& value) { 27806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // don't use c_str so it doesn't have to fix up the null terminator 27906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // (sqlite just uses the length) 28006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch std::string value_utf8(WideToUTF8(value)); 28106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return bind_text(index, value_utf8.data(), 28206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch static_cast<int>(value_utf8.length()), SQLITE_TRANSIENT); 28306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 28406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 28506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_text(int index, const char* value) { 28606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return bind_text(index, value, -1, SQLITE_TRANSIENT); 28706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 28806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 28906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // value_len is number of characters or may be negative 29006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // a for null-terminated value string 29106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_text(int index, const char* value, int value_len) { 29206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return bind_text(index, value, value_len, SQLITE_TRANSIENT); 29306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 29406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 29506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // value_len is number of characters or may be negative 29606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // a for null-terminated value string 29706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_text(int index, const char* value, int value_len, 29806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch Function dtor); 29906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 30006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_text16(int index, const char16* value) { 30106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return bind_text16(index, value, -1, SQLITE_TRANSIENT); 30206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 30306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 30406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // value_len is number of characters or may be negative 30506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // a for null-terminated value string 30606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_text16(int index, const char16* value, int value_len) { 30706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return bind_text16(index, value, value_len, SQLITE_TRANSIENT); 30806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 30906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 31006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // value_len is number of characters or may be negative 31106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // a for null-terminated value string 31206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_text16(int index, const char16* value, int value_len, 31306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch Function dtor); 31406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 31506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int bind_value(int index, const sqlite3_value* value); 31606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 31706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // 31806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // Column helpers (NOTE: index is 0-based) 31906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch // 32006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 32106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int column_count(); 32206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int column_type(int index); 32306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const void* column_blob(int index); 32406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool column_blob_as_vector(int index, std::vector<unsigned char>* blob); 32506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool column_blob_as_string(int index, std::string* blob); 32606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int column_bytes(int index); 32706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int column_bytes16(int index); 32806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch double column_double(int index); 32906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool column_bool(int index); 33006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch int column_int(int index); 33106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite_int64 column_int64(int index); 33206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* column_text(int index); 33306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool column_string(int index, std::string* str); 33406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch std::string column_string(int index); 33506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char16* column_text16(int index); 33606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool column_string16(int index, string16* str); 33706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch string16 column_string16(int index); 33806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch bool column_wstring(int index, std::wstring* str); 33906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch std::wstring column_wstring(int index); 34006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 34106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch private: 34206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch DISALLOW_COPY_AND_ASSIGN(SQLStatement); 34306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch}; 34406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 34506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochnamespace sqlite_utils { 34606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 34706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 34806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// A scoped sqlite database that closes when it goes out of scope. 34906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch//------------------------------------------------------------------------------ 35006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochclass DBClose { 35106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch public: 35206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch inline void operator()(sqlite3* x) const { 35306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch sqlite3_close(x); 35406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch } 35506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch}; 35606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 35706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochtypedef scoped_ptr_malloc<sqlite3, DBClose> scoped_sqlite_db_ptr; 35806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 35906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Opens the DB in the file pointed to by |filepath|. This method forces the 36006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// database to be in UTF-8 mode on all platforms. See 36106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// http://www.sqlite.org/capi3ref.html#sqlite3_open for an explanation of the 36206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// return value. 36306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochint OpenSqliteDb(const FilePath& filepath, sqlite3** database); 36406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 36506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Returns true if there is a table with the given name in the database. 36606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// For the version where a database name is specified, it may be NULL or the 36706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// empty string if no database name is necessary. 36806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochbool DoesSqliteTableExist(sqlite3* db, 36906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* db_name, 37006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* table_name); 37106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochinline bool DoesSqliteTableExist(sqlite3* db, const char* table_name) { 37206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return DoesSqliteTableExist(db, NULL, table_name); 37306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch} 37406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 37506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Test whether a table has a column matching the provided name and type. 37606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Returns true if the column exist and false otherwise. There are two 37706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// versions, one that takes a database name, the other that doesn't. The 37806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// database name can be NULL or empty if no name is desired. 37906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// 38006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Column type is optional, it can be NULL or empty. If specified, we the 38106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// function will check that the column is of the correct type (case-sensetive). 38206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochbool DoesSqliteColumnExist(sqlite3* db, 38306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* datbase_name, 38406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* table_name, 38506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* column_name, 38606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* column_type); 38706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochinline bool DoesSqliteColumnExist(sqlite3* db, 38806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* table_name, 38906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* column_name, 39006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch const char* column_type) { 39106741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch return DoesSqliteColumnExist(db, NULL, table_name, column_name, column_type); 39206741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch} 39306741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 39406741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// Test whether a table has one or more rows. Returns true if the table 39506741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch// has one or more rows and false if the table is empty or doesn't exist. 39606741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdochbool DoesSqliteTableHaveRow(sqlite3* db, const char* table_name); 39706741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 39806741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch} // namespace sqlite_utils 39906741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch 40006741cbc25cd4227a9fba40dfd0273bfcc1a587aBen Murdoch#endif // CHROME_COMMON_SQLITE_UTILS_H_ 401