An Introduction To The SQLite C/C++ Interface

為了能夠用SQL將資料由資料庫撈出，我們必須認識兩個主要的Object：

1. The database connection object：sqlite3
執行sqlite3_open()之後，就會得到該object，感覺類似open之後的file descriptor。

2. The prepared statement object：sqlite3_stmt
把SQL轉成bytecode，但是還沒真正被執行。請參考Compiling An SQL Statement/To execute an SQL query, it must first be compiled into a byte-code program using one of these routines.
An instance of this object represents a single SQL statement. This object is variously known as a "prepared statement" or a "compiled SQL statement" or simply as a "statement".

一個常見的SQLite pattern為：

1. sqlite3_open()：取得database connection object。
2. sqlite3_preapre() ：取得prepared statement object。
3. sqlite3_step()：執行prepared statement object。
4. sqlite3_column_<type>()：將sqlite回傳回來的資料，轉成對應的type取出。
5. sqlite3_finalize()：釋放prepared statement object。
6. sqlite3_close()：釋放database connection object。

#include <iostream>
#include <sqlite3.h>
#include <cstdlib>
#include <assert.h>

using namespace std;

int main(int argc, char** argv)
{
  sqlite3 *conn;
  sqlite3_stmt *statement; // SQL Statement Object
  int ret = 0;
  int cols;

  // This routine opens a connection to an SQLite database file
  //  and returns a database connection object.
  ret = sqlite3_open_v2("hello.db", &conn, SQLITE_OPEN_READONLY, NULL);
  if (ret) {
    cout << "can not open database\n";
    exit(0);
  }

  ret = sqlite3_prepare_v2(conn, "select * from hello", -1, &statement, NULL);
  if (ret != SQLITE_OK) {
    cout << "We did not get any data\n";
    exit(0);
  }

  cols = sqlite3_column_count(statement);

  for (int col = 0; col < cols; col++) {
    cout << " " << sqlite3_column_name(statement, col);
  };
  cout << endl;

  while (true) {
    ret = sqlite3_step(statement);
    if (ret == SQLITE_ROW) {
      for (int col = 0; col < cols; col++) {
        switch (sqlite3_column_type(statement, col)) {
          case SQLITE_INTEGER:
            cout << " " << sqlite3_column_int(statement, col) << " ";
            break;
          case SQLITE_FLOAT:
            cout << " " << sqlite3_column_double(statement, col) << " ";
            break;
          case SQLITE_TEXT:
            cout << " " << sqlite3_column_text(statement, col) << " ";
            break;
          case SQLITE_NULL:
            cout << " " << "NULL" << " ";
            break;
        }
      };
      cout << endl;
    } else if (ret == SQLITE_DONE) {
      cout << "done" << endl;
      break;
    } else {
      cout << "ret:" << ret << endl;
      break;
    }
  }

  sqlite3_finalize(statement);
  sqlite3_close(conn);

  return 0;
}

sqlite3_exec()是將sqlite3_prepare_v2(), sqlite3_step()和 sqlite3_finalize()包裝起來的API，其原型為

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

• 第二個參數要執行的SQL statement。
• 第三個參數是callback function。每一個row都會呼叫該callback一次。
• 第四個參數是要傳給callback function的data。
• 第五個參數就是發生錯誤時，儲存錯誤資訊的指標。

因此上面的程式可以改寫為

#include <iostream>
#include <sqlite3.h>

using namespace std;

int callback(void *data, int column, char **value, char **name)
{
  int i;
  cout << (char *)data << endl;
  for (i = 0; i < column; i++) {
    cout << name[i] << ":" << (value[i] ?: "NULL") << endl << flush;
  }
  return 0;
}


int main(int argc, char** argv)
{
  sqlite3 *conn;
  int ret = 0;
  char *errmsg = NULL;
  char data[] = "brook";

  // This routine opens a connection to an SQLite database file
  //  and returns a database connection object.
  ret = sqlite3_open_v2("hello.db", &conn, SQLITE_OPEN_READONLY, NULL);
  if (ret != SQLITE_OK) {
     cout << sqlite3_errmsg(conn) << ".(" << ret << ")" << endl;
     exit(0);
  }

  ret = sqlite3_exec(conn, "select * from hello", callback, data, &errmsg);
  if (ret != SQLITE_OK) {
     cout << "We did not get any data. " << errmsg << endl;
     sqlite3_free(errmsg);
  }

  sqlite3_close(conn);

  return 0;
}

參考資料：
• An Introduction To The SQLite C/C++ Interface
• One-Step Query Execution Interface

Introduction SQLite

大家比較耳熟能詳的database大多是client/server的架構居多，如：MySQL、PostgreSQL、MS-SQL和Oracle等等，而SQLite感覺比較像MS的Access， 程式本身就負責開啟資料庫並且直接操作，更多的資訊可以參考官網http://www.sqlite.org/。

以下就個人在SQLite網站看到的介紹，寫下一些筆記。
About SQLite
SQLite不像許多SQL資料庫有分開的Server process。SQLite直接對資料庫檔案做存取，這些資料庫檔案室可以跨平台的， 意味著您可以直接在32/64和big-endian/little-endian複製這些資料庫檔案，都能正確地被SQLite所存取。

Appropriate Uses For SQLite/Situations Where Another RDBMS May Work Better SQLite因為直接對database做存取，所以多個client同時對某database做存取可能會有問題。 如果是高流量(High-volume Websites)或是高資料量(Very large datasets)都不適合SQLite，畢竟它是拿來給embedded用的。

總體而言，對於小資料庫的應用SQLite已經是非常好的選擇了。


基本操作

brook:~$ ls hello.db
ls: cannot access hello.db: No such file or directory
brook:~$ sqlite hello.db
Loading resources from /home/brook/.sqliterc
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> .help
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in a text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line(s)", "column(s)",
                       "insert", "list", or "html"
.mode insert TABLE     Generate SQL insert statements for TABLE
.nullvalue STRING      Print STRING instead of nothing for NULL data
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode

sqlite> .tables
sqlite> create table hello (x integer PRIMARY KEY ASC, y);
sqlite> .tables
hello
sqlite> insert into hello (y) values('a');
sqlite> insert into hello (y) values(10);
sqlite> insert into hello (y) values(datetime('now'));
sqlite> select * from hello;
x           y
----------  ----------
1           a
2           10
3           2012-07-30
sqlite> .quit

我們也可以直接在command line上面執行sqlite

brook:~$ sqlite hello.db .dump
Loading resources from /home/brook/.sqliterc
BEGIN TRANSACTION;
create table hello (x integer PRIMARY KEY ASC, y);
INSERT INTO hello VALUES(1,'a');
INSERT INTO hello VALUES(2,10);
INSERT INTO hello VALUES(3,'2012-07-30 04:20:12');
COMMIT;
brook:~$ sqlite hello.db "select * from hello"
Loading resources from /home/brook/.sqliterc
x           y
----------  ----------
1           a
2           10
3           2012-07-30
brook:~$ cat ~/.sqliterc
.mode column
.header on
.nullvalue NULL

更多SQL語法請參考http://www.sqlite.org/lang.html

參考資料：
• http://www.sqlite.org/