sqlite — SQLite database

Requires: nothing — SQLite is compiled directly into the plugin (amalgamation)

#include "sqlite"

Query results are returned as a JSON string — parse with the Json module.

Method	Args	Out	Description
`SQLite:Open`	`path`	Number	Open or create database; returns handle. Throws `SQLiteException` on failure
`SQLite:Close`	`handle`	void	Close the database
`SQLite:Exec`	`handle, sql`	Boolean	Run DDL / DML; `true` = success
`SQLite:Query`	`handle, sql`	String	SELECT → JSON array of row objects
`SQLite:QueryOne`	`handle, sql`	String	SELECT → first row as JSON object, or `""`
`SQLite:RowsAffected`	`handle`	Number	Rows changed by last `Exec`
`SQLite:LastInsertId`	`handle`	Number	Row ID of last INSERT
`SQLite:Error`	`handle`	String	Last error message, `""` if none

Throws SQLiteException on open failure. See Object — Exception.

Var:Number db;
Var:Boolean ok;
Var:String rows;
Var:Number n;

try {
    SQLite:Open("data.db", db);
} catch (SQLiteException e) {
    Konsol:Print("open failed: ${e.message}");
}

SQLite:Exec(db, "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)", ok);
SQLite:Exec(db, "INSERT INTO users (name) VALUES ('Alice')", ok);
SQLite:LastInsertId(db, n);
Konsol:Print("inserted id: ${n}");

SQLite:Query(db, "SELECT * FROM users", rows);
Konsol:Print(rows);   // [{"id":1,"name":"Alice"}]

SQLite:Close(db);

Building from source

No system SQLite package needed — the plugin compiles the SQLite amalgamation directly. Before building, download the amalgamation zip and place sqlite3.c and sqlite3.h in sqlite_plugin/.

make -C sqlite_plugin

Examples

// test_sqlite.ks — SQLite plugin tests
// Run: minks --plugin ./kse_sqlite.dll test_sqlite.ks
// Creates a temporary "test_sqlite.db" in the working directory.

Konsol:Print("=== SQLite plugin ===");

Var:Number db;
Var:Boolean ok;
Var:String  rows;
Var:String  row;
Var:String  err;
Var:Number  n;

// ── Open ───────────────────────────────────────────────────────────────────────

Konsol:Print("--- Open");
SQLite:Open("test_sqlite.db", db);
Konsol:Print("handle : ${db}");

// ── CREATE TABLE ───────────────────────────────────────────────────────────────

Konsol:Print("--- CREATE TABLE");
SQLite:Exec(db, "DROP TABLE IF EXISTS users", ok);
SQLite:Exec(db, "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER)", ok);
Konsol:Print("ok     : ${ok}");
SQLite:Error(db, err);
Konsol:Print("error  : '${err}'");

// ── INSERT ─────────────────────────────────────────────────────────────────────

Konsol:Print("--- INSERT");
SQLite:Exec(db, "INSERT INTO users (name, age) VALUES ('Alice', 30)", ok);
SQLite:LastInsertId(db, n);
Konsol:Print("ok, last_id : ${ok} ${n}");

SQLite:Exec(db, "INSERT INTO users (name, age) VALUES ('Bob', 25)", ok);
SQLite:LastInsertId(db, n);
Konsol:Print("ok, last_id : ${ok} ${n}");

SQLite:Exec(db, "INSERT INTO users (name, age) VALUES ('Carol', 35)", ok);
SQLite:LastInsertId(db, n);
Konsol:Print("ok, last_id : ${ok} ${n}");

// ── SELECT all ─────────────────────────────────────────────────────────────────

Konsol:Print("--- SELECT all");
SQLite:Query(db, "SELECT * FROM users ORDER BY id", rows);
Konsol:Print("rows   : ${rows}");

// ── SELECT filtered ────────────────────────────────────────────────────────────

Konsol:Print("--- SELECT WHERE age > 28");
SQLite:Query(db, "SELECT * FROM users WHERE age > 28 ORDER BY age", rows);
Konsol:Print("rows   : ${rows}");

// ── QueryOne ───────────────────────────────────────────────────────────────────

Konsol:Print("--- QueryOne");
SQLite:QueryOne(db, "SELECT * FROM users WHERE name = 'Bob'", row);
Konsol:Print("row    : ${row}");

SQLite:QueryOne(db, "SELECT * FROM users WHERE name = 'Nobody'", row);
Konsol:Print("no match (expect empty) : '${row}'");

// ── UPDATE ─────────────────────────────────────────────────────────────────────

Konsol:Print("--- UPDATE");
SQLite:Exec(db, "UPDATE users SET age = 31 WHERE name = 'Alice'", ok);
SQLite:RowsAffected(db, n);
Konsol:Print("ok, rows_affected : ${ok} ${n}");

SQLite:QueryOne(db, "SELECT age FROM users WHERE name = 'Alice'", row);
Konsol:Print("alice age now : ${row}");

// ── DELETE ─────────────────────────────────────────────────────────────────────

Konsol:Print("--- DELETE");
SQLite:Exec(db, "DELETE FROM users WHERE name = 'Carol'", ok);
SQLite:RowsAffected(db, n);
Konsol:Print("ok, rows_affected : ${ok} ${n}");

SQLite:Query(db, "SELECT * FROM users ORDER BY id", rows);
Konsol:Print("remaining : ${rows}");

// ── Error handling ─────────────────────────────────────────────────────────────

Konsol:Print("--- bad SQL");
SQLite:Exec(db, "THIS IS NOT SQL", ok);
Konsol:Print("ok (expect false) : ${ok}");
SQLite:Error(db, err);
Konsol:Print("error  : '${err}'");

// ── Close ──────────────────────────────────────────────────────────────────────

Konsol:Print("--- Close");
SQLite:Close(db);
Konsol:Print("closed");

Konsol:Print("=== done ===");