module ocda.sqlite; /+ ↓ the sqlite3 C API, declared rather than depended upon. . A binding from the dub registry would be a dependency to fetch, and the point of this reader is that it builds in a sandbox with no network. The dozen functions a reader needs are declared here and the library is linked directly, which costs a page of extern(C) and saves a dependency. +/ import std.conv : to; import std.string : fromStringz, toStringz; extern (C) { struct sqlite3; struct sqlite3_stmt; int sqlite3_open_v2(const(char)* filename, sqlite3** db, int flags, const(char)* vfs); int sqlite3_close(sqlite3* db); int sqlite3_prepare_v2(sqlite3* db, const(char)* sql, int nbyte, sqlite3_stmt** stmt, const(char)** tail); int sqlite3_step(sqlite3_stmt* stmt); int sqlite3_finalize(sqlite3_stmt* stmt); int sqlite3_column_count(sqlite3_stmt* stmt); const(char)* sqlite3_column_name(sqlite3_stmt* stmt, int col); const(ubyte)* sqlite3_column_text(sqlite3_stmt* stmt, int col); int sqlite3_column_int(sqlite3_stmt* stmt, int col); int sqlite3_column_type(sqlite3_stmt* stmt, int col); const(char)* sqlite3_errmsg(sqlite3* db); const(void)* sqlite3_column_blob(sqlite3_stmt* stmt, int col); int sqlite3_column_bytes(sqlite3_stmt* stmt, int col); } enum SQLITE_OK = 0; enum SQLITE_ROW = 100; enum SQLITE_DONE = 101; enum SQLITE_NULL = 5; enum SQLITE_OPEN_READONLY = 0x00000001; class SqliteError : Exception { this(string msg) { super(msg); } } /+ ↓ a database, opened read only. Read only is deliberate: a reader has no business writing, and a request that opens a database read-write can create an empty one where a path is mistyped. +/ struct Db { private sqlite3* _handle; static Db open(string path) { sqlite3* handle; auto rc = sqlite3_open_v2(path.toStringz, &handle, SQLITE_OPEN_READONLY, null); if (rc != SQLITE_OK) { string msg = (handle is null) ? "cannot open " ~ path : "cannot open " ~ path ~ ": " ~ sqlite3_errmsg(handle).fromStringz.idup; if (handle !is null) { sqlite3_close(handle); } throw new SqliteError(msg); } Db db; db._handle = handle; return db; } void close() { if (_handle !is null) { sqlite3_close(_handle); _handle = null; } } /+ ↓ every row of a query, each row as its columns in order +/ string[][] rows(string sql) { sqlite3_stmt* stmt; auto rc = sqlite3_prepare_v2(_handle, sql.toStringz, -1, &stmt, null); if (rc != SQLITE_OK) { throw new SqliteError(sqlite3_errmsg(_handle).fromStringz.idup ~ " in: " ~ sql); } scope(exit) { sqlite3_finalize(stmt); } string[][] out_rows; int cols = sqlite3_column_count(stmt); while (sqlite3_step(stmt) == SQLITE_ROW) { string[] row; foreach (c; 0 .. cols) { row ~= (sqlite3_column_type(stmt, c) == SQLITE_NULL) ? "" : (cast(const(char)*) sqlite3_column_text(stmt, c)).fromStringz.idup; } out_rows ~= row; } return out_rows; } /+ ↓ the first column of the first row, "" when there is none +/ string one(string sql) { auto r = rows(sql); return (r.length > 0 && r[0].length > 0) ? r[0][0] : ""; } /+ ↓ the first column of the first row as bytes, for a blob. Separate from one(), because a blob is not text: an image read as text stops at the first zero byte, and a png's ninth byte is zero. +/ ubyte[] blob(string sql) { sqlite3_stmt* stmt; if (sqlite3_prepare_v2(_handle, sql.toStringz, -1, &stmt, null) != SQLITE_OK) { throw new SqliteError("prepare: " ~ sqlite3_errmsg(_handle).fromStringz.idup ~ " [" ~ sql ~ "]"); } scope(exit) { sqlite3_finalize(stmt); } if (sqlite3_step(stmt) != SQLITE_ROW) { return []; } int n = sqlite3_column_bytes(stmt, 0); if (n <= 0) { return []; } auto p = cast(const(ubyte)*) sqlite3_column_blob(stmt, 0); if (p is null) { return []; } return p[0 .. n].dup; } /+ ↓ does a table or view of this name exist +/ bool has(string name) { return one("SELECT count(*) FROM sqlite_master WHERE name = '" ~ name ~ "'") != "0"; } }