SQLite 是一個很小的 C 語言函式庫。這個函式庫本身就完全包含資料庫引擎的功能，而且可以嵌入至其他程式中。

SQLite 支援 In-Memory 與 Disk-based 等形式的使用方式，下面是一個使用 C API 來開啟一個 In-Memory Database 的例子（使用 ":memory:" 檔名）：

rc = sqlite3_open(":memory:", &db);

列舉資料庫中的表格

From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python bindings) you can get access to table and index names by doing a SELECT on a special table named "SQLITE_MASTER". Every SQLite database has an SQLITE_MASTER table that defines the schema for the database. The SQLITE_MASTER table looks like this:

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

所以我們可以使用下列的方式來列舉資料庫中的表格：

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

Auto-increment column

下面二種寫法都會建立 autoincrementing column; the AUTOINCREMENT keyword only prevents reusing deleted values.

CREATE TABLE t1 (col1 INTEGER PRIMARY KEY);
CREATE TABLE t1 (col1 INTEGER PRIMARY KEY AUTOINCREMENT);

UPSERT

對於 SQLite 而言，有幾種方式提供 UPSERT 的語法。SQLite 提供了 REPLACE INTO 的語法，同時提供了與 PostgreSQL 類似的 INSERT INTO ... ON CONFLICT clause 的方式來處理。

C API

使用 C 語言開發 Sqlite 的程式非常容易，下面是一個簡單的例子：

#include <sqlite3.h>
#include <stdio.h>

static char *createsql = "CREATE TABLE Contact("
                         "ID INTEGER PRIMARY KEY,"
                         "Name VARCHAR(10),"
                         "PhoneNumber VARCHAR(10));";

static char *insertsql =
    "INSERT INTO Contact VALUES(1, 'Richard', '09990123456');";

static char *querysql = "SELECT * FROM Contact;";

void main(void) {
    int rows, cols;
    sqlite3 *db;
    char *errMsg = NULL;
    char **result;
    int i, j;

    if (sqlite3_open_v2("example.db3", &db,
                        SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)) {
        return;
    }

    sqlite3_exec(db, createsql, 0, 0, &errMsg);
    sqlite3_exec(db, insertsql, 0, 0, &errMsg);

    printf("%d\n", sqlite3_last_insert_rowid(db));

    sqlite3_get_table(db, querysql, &result, &rows, &cols, &errMsg);
    for (i = 0; i <= rows; i++) {
        for (j = 0; j < cols; j++) {
            printf("%s\t", result[i * cols + j]);
        }
        printf("\n");
    }

    sqlite3_free_table(result);
    sqlite3_close(db);
}

編譯時只要連結 sqlite 函式庫：

gcc  -lsqlite3  -o sqlite_ex sqlite_ex.c

Free Pascal SQLDb

下面是使用 Free Pascal 的 SQLDB 所提供的 sqlite3 介面取得目前的時間。

{$ifdef FPC} {$mode delphi} {$endif}
{$ifdef MSWINDOWS} {$apptype CONSOLE} {$endif}

program MyProgram;

uses 
SysUtils, SQLdb, SQLite3Conn;


var 
  Conn: TSQLite3Connection;
  Transaction1: TSQLTransaction;
  Query1: TSQLQuery;

begin
  Conn := TSQLite3Connection.Create(nil);
  try
    try
      Conn.DatabaseName := ':memory:';

      // Open the connection
      Conn.Connected := True;

      Transaction1 := TSQLTransaction.Create(nil);
      Transaction1.DataBase := Conn;

      Query1 := TSQLQuery.Create(nil);
      Query1.DataBase := Conn;
      Query1.Transaction := Transaction1;

      // Example query execution
      Transaction1.StartTransaction;
      Query1.SQL.Text := 'SELECT DATETIME("now", "localtime") as datetime';
      Query1.Open;

      while not Query1.EOF do
        begin
          WriteLn(Query1.FieldByName('datetime').AsString);
          Query1.Next;
        end;

      Query1.Close;
      Transaction1.Commit;
      Conn.Connected := False;

    except
      on E: Exception do
            writeln('An error occurred: ', E.Message);
    end;

  finally
    // Clean up resources
    if Assigned(Query1) then Query1.Free;
    if Assigned(Transaction1) then Transaction1.Free;
    if Assigned(Conn) then Conn.Free;
  end;
end.

Tcl TDBC

下面是使用 Tcl TDBC 取得 SQLite 版本資訊的例子。

package require tdbc::sqlite3
tdbc::sqlite3::connection create db ":memory:"

set statement [db prepare {
    SELECT sqlite_version() as version
}]

$statement foreach row {
    puts [dict get $row version]
}

$statement close
db close

參考連結

SQLite

Hun Speaking

2024/03/10

SQLite