Working with SQLite

Opening A New Database Connection

int sqlite3_open(
  const char *filename,
  sqlite3 **ppDb
);

One-Step Query Execution Interface

int sqlite3_exec(
  sqlite3*,
  const char *sql,
  int (*callback)(void*,int,char**,char**),
  void *,
  char **errmsg
);

sqlite3_exec() is appropriate for SQL statements that do not return data. For SQL statements that return data, prepared statements are usually used.

Prepared Statements
The use of prepared statements involves three phases: preparation, execution, and
finalization.

Preparation - Present a statement to the SQLite engine for compilation. The engine compiles this statement into byte code and reserves the resources needed for actual execution.

int sqlite3_prepare_v2(
  sqlite3 *db,
  const char *zSql,
  int nByte,
  sqlite3_stmt **ppStmt,
  const char **pzTail
);

Execution - Execute the byte code and obtain rows from the result of the statement. We repeat this phase for every row in the result set.

int sqlite3_step(sqlite3_stmt*);

SQLITE_DONE means that the statement has finished executing successfully. If the SQL statement being executed returns any data, then SQLITE_ROW is returned each time a new row of data is ready for processing by the caller.

Finalization - After obtaining all rows in the result set, we finalize the prepared statement so that resources reserved for it can be freed.

int sqlite3_finalize(sqlite3_stmt *pStmt);

Closing A Database Connection

int sqlite3_close(sqlite3 *);

---

Example
In sqlite_repository.h,

#include "repository.h"

struct sqlite3;

namespace repository {

class SqliteRepository : public Repository {
 public:
  SqliteRepository(const std::string& name = std::string());
  ~SqliteRepository();

  void setValue(const std::string& key, 
                const std::string& value);

  std::string value(const std::string& key,
                    const std::string& default_value) const;

  void sync() {}

 private:
  void executeNonQuery(const std::string& query);

  sqlite3* database_;
};

}  // namespace repository

In sqlite_repository.cc,

#include "sqlite_repository.h"
#include "third_party/sqlite/sqlite3.h"

namespace repository {

SqliteRepository::SqliteRepository(...) {
  sqlite3_open(name.c_str(), &database_);
  executeNonQuery("CREATE TABLE IF NOT EXISTS "
                  "pair(key VARCHAR(16) PRIMARY KEY, value VARCHAR(128))");
}

SqliteRepository::~SqliteRepository() {
  sqlite3_close(database_);
}

void SqliteRepository::setValue(...) {
  executeNonQuery("DELETE FROM pair WHERE key='" + key + "'");
  executeNonQuery("INSERT INTO pair VALUES ('" + key + "', '" + value + "')");
}

std::string SqliteRepository::value(...) const {
  // Prepares.
  std::string stmt = "SELECT value FROM pair WHERE key='" + key + "'";
  sqlite3_stmt* sql_result;
  sqlite3_prepare_v2(database_, stmt.c_str(), stmt.length(), &sql_result, NULL);

  // Executes.
  if (sqlite3_step(sql_result) != SQLITE_ROW) {
    return default_value;
  }
  std::string result(reinterpret_cast<const char*>(
      sqlite3_column_text(sql_result, 0)));

  // Finalizes.
  sqlite3_finalize(sql_result);

  return result;
}

void SqliteRepository::executeNonQuery(...) {
  sqlite3_exec(database_, query.c_str(), NULL, NULL, NULL);
}

}  // namespace repository