1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
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";
}
}
|