path: root/chrome/common/sqlite_utils.h

// Copyright 2008, Google Inc.
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without
// modification, are permitted provided that the following conditions are
// met:
//
//    * Redistributions of source code must retain the above copyright
// notice, this list of conditions and the following disclaimer.
//    * Redistributions in binary form must reproduce the above
// copyright notice, this list of conditions and the following disclaimer
// in the documentation and/or other materials provided with the
// distribution.
//    * Neither the name of Google Inc. nor the names of its
// contributors may be used to endorse or promote products derived from
// this software without specific prior written permission.
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
// "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
// LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
// A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
// OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
// SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
// LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
// DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
// THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
// (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
// OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

#ifndef CHROME_COMMON_SQLITEUTILS_H__
#define CHROME_COMMON_SQLITEUTILS_H__

#include <vector>

#include "base/logging.h"
#include "chrome/third_party/sqlite/sqlite3.h"

// forward declarations of classes defined here
class SQLTransaction;
class SQLNestedTransaction;
class SQLNestedTransactionSite;
class scoped_sqlite3_stmt_ptr;
class SQLStatement;

//------------------------------------------------------------------------------
// A wrapper for sqlite transactions that rollsback when the wrapper
// goes out of scope if the caller has not already called Commit or Rollback.
// Note: the constructor does NOT Begin a transaction.
//------------------------------------------------------------------------------
class SQLTransaction {
 public:
  SQLTransaction(sqlite3 *db) {
    db_ = db;
    began_ = false;
  }

  virtual ~SQLTransaction() {
    if (began_) {
      Rollback();
    }
  }

  int Begin() {
    // By default, we BEGIN IMMEDIATE to establish file locks at the
    // onset of a transaction. This avoids SQLITE_BUSY errors, without
    // waiting for the busy timeout period, which can occur when BEGIN
    // DEFERRED is used.
    return BeginImmediate();
  }

  int BeginExclusive() {
    return BeginCommand("BEGIN EXCLUSIVE");
  }

  int BeginImmediate() {
    return BeginCommand("BEGIN IMMEDIATE");
  }

  int BeginDeferred() {
    return BeginCommand("BEGIN DEFERRED");
  }

  int Commit() {
    return EndCommand("COMMIT");
  }

  int Rollback() {
    return EndCommand("ROLLBACK");
  }

  bool HasBegun() {
    return began_;
  }

 protected:
  virtual int BeginCommand(const char *command) {
    int rv = SQLITE_ERROR;
    if (!began_ && db_) {
      rv = sqlite3_exec(db_, command, NULL, NULL, NULL);
      began_ = (rv == SQLITE_OK);
    }
    return rv;
  }

  virtual int EndCommand(const char *command) {
    int rv = SQLITE_ERROR;
    if (began_ && db_) {
      rv = sqlite3_exec(db_, command, NULL, NULL, NULL);
      began_ = (rv != SQLITE_OK);
    }
    return rv;
  }

  bool began_;
  sqlite3 *db_;
  DISALLOW_EVIL_CONSTRUCTORS(SQLTransaction);
};


//------------------------------------------------------------------------------
// A class for use with SQLNestedTransaction.
//------------------------------------------------------------------------------
class SQLNestedTransactionSite {
 protected:
  SQLNestedTransactionSite() : db_(NULL), top_transaction_(NULL) {}
  virtual ~SQLNestedTransactionSite() { DCHECK(!top_transaction_); }

  // The following virtual methods provide notification of true transaction
  // boundaries as they are crossed by a top nested transaction.
  // Intended to be overriden (See WebCacheDB)
  // SQLNestedTransaction calls these after the underlying database
  // operation has been performed.

  virtual void OnBegin() {}
  virtual void OnCommit() {}
  virtual void OnRollback() {}

  // Returns the sqlite3 database connection associated with this site
  // Used by SQLNestedTransaction
  sqlite3* GetSqlite3DB() { return db_; }

  // Returns the current top nested transaction associated with this site
  // Used by SQLNestedTransaction
  SQLNestedTransaction *GetTopTransaction() {
    return top_transaction_;
  }

  // Sets or clears the top nested transaction associated with this site
  // Used by SQLNestedTransaction
  void SetTopTransaction(SQLNestedTransaction *top) {
    DCHECK(!top || !top_transaction_);
    top_transaction_ = top;
  }

  sqlite3* db_;
  SQLNestedTransaction *top_transaction_;
  friend class SQLNestedTransaction;
};

//------------------------------------------------------------------------------
// SQLite does not support nested transactions. This class provides a gross
// approximation of nested transactions.
//
// Really there is only one transaction, the top transaction.
//
// A nested transaction commits with respect to the top transaction.
// That is, even though the nested transaction commits, the permanence of its
// effects depends on the top transaction committing. If the top
// transaction rollsback, the results of the nested transaction are backed out.
// If any nested transaction aborts, the top transaction ultimately rollsback
// as well.
//
// Note: If a nested transaction is open for a particular db connection, an
// attempt to open a non-nested transaction (class SQLTransaction) will fail.
// And vice versa.
//
// TODO(michaeln): demonstrate usage here
// TODO(michaeln): safegaurds to prevent mis-use
//------------------------------------------------------------------------------
class SQLNestedTransaction : public SQLTransaction {
 public:
  SQLNestedTransaction(SQLNestedTransactionSite *site)
    : SQLTransaction(site->GetSqlite3DB()),
      needs_rollback_(false),
      site_(site) {
    DCHECK(site);
    if (site->GetTopTransaction() == NULL) {
      site->SetTopTransaction(this);
    }
  }

  virtual ~SQLNestedTransaction() {
    if (began_) {
      Rollback();
    }
    if (site_->GetTopTransaction() == this) {
      site_->SetTopTransaction(NULL);
    }
  }

 protected:
  virtual int BeginCommand(const char *command) {
    DCHECK(db_);
    DCHECK(site_ && site_->GetTopTransaction());
    if (!db_ || began_) {
      return SQLITE_ERROR;
    }
    if (site_->GetTopTransaction() == this) {
      int rv = sqlite3_exec(db_, command, NULL, NULL, NULL);
      began_ = (rv == SQLITE_OK);
      if (began_) {
        site_->OnBegin();
      }
      return rv;
    } else {
      if (site_->GetTopTransaction()->needs_rollback_) {
        return SQLITE_ERROR;
      }
      began_ = true;
      return SQLITE_OK;
    }
  }

  virtual int EndCommand(const char *command) {
    DCHECK(db_);
    DCHECK(site_ && site_->GetTopTransaction());
    if (!db_ || !began_) {
      return SQLITE_ERROR;
    }
    if (site_->GetTopTransaction() == this) {
      if (needs_rollback_) {
        sqlite3_exec(db_, "ROLLBACK", NULL, NULL, NULL);
        began_ = false; // reset so we don't try to rollback or call
                        // OnRollback() again
        site_->OnRollback();
        return SQLITE_ERROR;
      } else {
        int rv = sqlite3_exec(db_, command, NULL, NULL, NULL);
        began_ = (rv != SQLITE_OK);
        if (strcmp(command, "ROLLBACK") == 0) {
          began_ = false; // reset so we don't try to rollbck or call
                          // OnRollback() again
          site_->OnRollback();
        } else {
          DCHECK(strcmp(command, "COMMIT") == 0);
          if (rv == SQLITE_OK) {
            site_->OnCommit();
          }
        }
        return rv;
      }
    } else {
      if (strcmp(command, "ROLLBACK") == 0) {
        site_->GetTopTransaction()->needs_rollback_ = true;
      }
      began_ = false;
      return SQLITE_OK;
    }
  }

 private:
  bool needs_rollback_;
  SQLNestedTransactionSite *site_;
  DISALLOW_EVIL_CONSTRUCTORS(SQLNestedTransaction);
};

//------------------------------------------------------------------------------
// A scoped sqlite statement that finalizes when it goes out of scope.
//------------------------------------------------------------------------------
class scoped_sqlite3_stmt_ptr {
 public:
  ~scoped_sqlite3_stmt_ptr() {
    finalize();
  }

  scoped_sqlite3_stmt_ptr() : stmt_(NULL) {
  }

  explicit scoped_sqlite3_stmt_ptr(sqlite3_stmt *stmt)
    : stmt_(stmt) {
  }

  sqlite3_stmt *get() const {
    return stmt_;
  }

  void set(sqlite3_stmt *stmt) {
    finalize();
    stmt_ = stmt;
  }

  sqlite3_stmt *release() {
    sqlite3_stmt *tmp = stmt_;
    stmt_ = NULL;
    return tmp;
  }

  // It is not safe to call sqlite3_finalize twice on the same stmt.
  // Sqlite3's sqlite3_finalize() function should not be called directly
  // without calling the release method.  If sqlite3_finalize() must be
  // called directly, the following usage is advised:
  //  scoped_sqlite3_stmt_ptr stmt;
  //  ... do something with stmt ...
  //  sqlite3_finalize(stmt.release());
  int finalize() {
    int err = sqlite3_finalize(stmt_);
    stmt_ = NULL;
    return err;
  }

 protected:
  sqlite3_stmt *stmt_;

 private:
  DISALLOW_EVIL_CONSTRUCTORS(scoped_sqlite3_stmt_ptr);
};


//------------------------------------------------------------------------------
// A scoped sqlite statement with convenient C++ wrappers for sqlite3 APIs.
//------------------------------------------------------------------------------
class SQLStatement : public scoped_sqlite3_stmt_ptr {
public:
  SQLStatement() {
  }

  int prepare(sqlite3 *db, const char *sql) {
    return prepare(db, sql, -1);
  }

  int prepare(sqlite3 *db, const char *sql, int sql_len) {
    DCHECK(!stmt_);
    int rv = sqlite3_prepare_v2(db, sql, sql_len, &stmt_, NULL);
    if (rv != SQLITE_OK) {
      DLOG(ERROR) << "SQLStatement.prepare_v2 failed: " << sqlite3_errmsg(db);
    }
    return rv;
  }

  int prepare16(sqlite3 *db, const wchar_t *sql) {
    return prepare16(db, sql, -1);
  }

  // sql_len is number of characters or may be negative
  // a for null-terminated sql string
  int prepare16(sqlite3 *db, const wchar_t *sql, int sql_len) {
    DCHECK(!stmt_);
    sql_len *= sizeof(wchar_t);
    int rv = sqlite3_prepare16_v2(db, sql, sql_len, &stmt_, NULL);
    if (rv != SQLITE_OK) {
      DLOG(ERROR) << "SQLStatement.prepare16_v2 failed: " << sqlite3_errmsg(db);
    }
    return rv;
  }

  int step() {
    DCHECK(stmt_);
    return sqlite3_step(stmt_);
  }

  int reset() {
    DCHECK(stmt_);
    return sqlite3_reset(stmt_);
  }

  sqlite_int64 last_insert_rowid() {
    DCHECK(stmt_);
    return sqlite3_last_insert_rowid(db_handle());
  }

  sqlite3 *db_handle() {
    DCHECK(stmt_);
    return sqlite3_db_handle(stmt_);
  }

  //
  // Parameter binding helpers (NOTE: index is 0-based)
  //

  int bind_parameter_count() {
    DCHECK(stmt_);
    return sqlite3_bind_parameter_count(stmt_);
  }

  typedef void (*Function)(void*);

  int bind_blob(int index, std::vector<unsigned char> *blob) {
    if (blob) {
      const void *value = &(*blob)[0];
      int len = static_cast<int>(blob->size());
      return bind_blob(index, value, len);
    } else {
      return bind_null(index);
    }
  }

  int bind_blob(int index, const void *value, int value_len) {
     return bind_blob(index, value, value_len, SQLITE_TRANSIENT);
  }

  int bind_blob(int index, const void *value, int value_len, Function dtor) {
    DCHECK(stmt_);
    return sqlite3_bind_blob(stmt_, index + 1, value, value_len, dtor);
  }

  int bind_double(int index, double value) {
    DCHECK(stmt_);
    return sqlite3_bind_double(stmt_, index + 1, value);
  }

  int bind_bool(int index, bool value) {
    DCHECK(stmt_);
    return sqlite3_bind_int(stmt_, index + 1, value);
  }

  int bind_int(int index, int value) {
    DCHECK(stmt_);
    return sqlite3_bind_int(stmt_, index + 1, value);
  }

  int bind_int64(int index, sqlite_int64 value) {
    DCHECK(stmt_);
    return sqlite3_bind_int64(stmt_, index + 1, value);
  }

  int bind_null(int index) {
    DCHECK(stmt_);
    return sqlite3_bind_null(stmt_, index + 1);
  }

  int bind_string(int index, const std::string& value) {
    // don't use c_str so it doesn't have to fix up the null terminator
    // (sqlite just uses the length)
    return bind_text(index, value.data(),
                     static_cast<int>(value.length()), SQLITE_TRANSIENT);
  }

  int bind_wstring(int index, const std::wstring& value) {
    // don't use c_str so it doesn't have to fix up the null terminator
    // (sqlite just uses the length)
    return bind_text16(index, value.data(),
                       static_cast<int>(value.length()), SQLITE_TRANSIENT);
  }

  int bind_text(int index, const char *value) {
    return bind_text(index, value, -1, SQLITE_TRANSIENT);
  }

  // value_len is number of characters or may be negative
  // a for null-terminated value string
  int bind_text(int index, const char *value, int value_len) {
    return bind_text(index, value, value_len, SQLITE_TRANSIENT);
  }

  // value_len is number of characters or may be negative
  // a for null-terminated value string
  int bind_text(int index, const char *value, int value_len,
                Function dtor) {
    DCHECK(stmt_);
    return sqlite3_bind_text(stmt_, index + 1, value, value_len, dtor);
  }

  int bind_text16(int index, const wchar_t *value) {
    return bind_text16(index, value, -1, SQLITE_TRANSIENT);
  }

  // value_len is number of characters or may be negative
  // a for null-terminated value string
  int bind_text16(int index, const wchar_t *value, int value_len) {
    return bind_text16(index, value, value_len, SQLITE_TRANSIENT);
  }

  // value_len is number of characters or may be negative
  // a for null-terminated value string
  int bind_text16(int index, const wchar_t *value, int value_len,
                  Function dtor) {
    DCHECK(stmt_);
    value_len *= sizeof(wchar_t);
    return sqlite3_bind_text16(stmt_, index + 1, value, value_len, dtor);
  }

  int bind_value(int index, const sqlite3_value *value) {
    DCHECK(stmt_);
    return sqlite3_bind_value(stmt_, index + 1, value);
  }

  //
  // Column helpers (NOTE: index is 0-based)
  //

  int column_count() {
    DCHECK(stmt_);
    return sqlite3_column_count(stmt_);
  }

  int column_type(int index) {
    DCHECK(stmt_);
    return sqlite3_column_type(stmt_, index);
  }

  const wchar_t *column_name16(int index) {
    DCHECK(stmt_);
    return static_cast<const wchar_t*>( sqlite3_column_name16(stmt_, index) );
  }

  const void *column_blob(int index) {
    DCHECK(stmt_);
    return sqlite3_column_blob(stmt_, index);
  }

  bool column_blob_as_vector(int index, std::vector<unsigned char> *blob) {
    DCHECK(stmt_);
    const void *p = column_blob(index);
    size_t len = column_bytes(index);
    blob->resize(len);
    if (blob->size() != len) {
      return false;
    }
    if (len > 0)
      memcpy(&(blob->front()), p, len);
    return true;
  }

  bool column_blob_as_string(int index, std::string* blob) {
    DCHECK(stmt_);
    const void *p = column_blob(index);
    size_t len = column_bytes(index);
    blob->resize(len);
    if (blob->size() != len) {
      return false;
    }
    blob->assign(reinterpret_cast<const char*>(p), len);
    return true;
  }

  int column_bytes(int index) {
    DCHECK(stmt_);
    return sqlite3_column_bytes(stmt_, index);
  }

  int column_bytes16(int index) {
    DCHECK(stmt_);
    return sqlite3_column_bytes16(stmt_, index);
  }

  double column_double(int index) {
    DCHECK(stmt_);
    return sqlite3_column_double(stmt_, index);
  }

  bool column_bool(int index) {
    DCHECK(stmt_);
    return sqlite3_column_int(stmt_, index) ? true : false;
  }

  int column_int(int index) {
    DCHECK(stmt_);
    return sqlite3_column_int(stmt_, index);
  }

  sqlite_int64 column_int64(int index) {
    DCHECK(stmt_);
    return sqlite3_column_int64(stmt_, index);
  }

  const char* column_text(int index) {
    DCHECK(stmt_);
    return reinterpret_cast<const char*>(sqlite3_column_text(stmt_, index));
  }

  bool column_string(int index, std::string *str) {
    DCHECK(stmt_);
    DCHECK(str);
    const char* s = column_text(index);
	str->assign(s ? s : std::string(""));
    return s != NULL;
  }

  std::string column_string(int index) {
    std::string str;
    column_string(index, &str);
    return str;
  }

  const wchar_t *column_text16(int index) {
    DCHECK(stmt_);
    return static_cast<const wchar_t*>( sqlite3_column_text16(stmt_, index) );
  }

  bool column_string16(int index, std::wstring *str) {
    DCHECK(stmt_);
    DCHECK(str);
    const wchar_t *s = column_text16(index);
    str->assign(s ? s : std::wstring(L""));
    return (s != NULL);
  }

  std::wstring column_string16(int index) {
    std::wstring wstr;
    column_string16(index, &wstr);
    return wstr;
  }
 private:
  DISALLOW_EVIL_CONSTRUCTORS(SQLStatement);
};

// Returns true if there is a table with the given name in the database.
// For the version where a database name is specified, it may be NULL or the
// empty string if no database name is necessary.
bool DoesSqliteTableExist(sqlite3* db,
                          const char* db_name,
                          const char* table_name);
inline bool DoesSqliteTableExist(sqlite3* db, const char* table_name) {
  return DoesSqliteTableExist(db, NULL, table_name);
}


// Test whether a table has a column matching the provided name and type.
// Returns true if the column exist and false otherwise. There are two
// versions, one that takes a database name, the other that doesn't. The
// database name can be NULL or empty if no name is desired.
//
// Column type is optional, it can be NULL or empty. If specified, we the
// function will check that the column is of the correct type (case-sensetive).
bool DoesSqliteColumnExist(sqlite3* db,
                           const char* datbase_name,
                           const char* table_name,
                           const char* column_name,
                           const char* column_type);
inline bool DoesSqliteColumnExist(sqlite3* db,
                           const char* table_name,
                           const char* column_name,
                           const char* column_type) {
  return DoesSqliteColumnExist(db, NULL, table_name, column_name, column_type);
}

// Test whether a table has one or more rows. Returns true if the table
// has one or more rows and false if the table is empty or doesn't exist.
bool DoesSqliteTableHaveRow(sqlite3* db, const char* table_name);

#endif  // CHROME_COMMON_SQLITEUTILS_H__