summaryrefslogtreecommitdiffhomepage
path: root/dlang/src/ocda/sqlite.d
blob: 6f3753c7ffbf2b1cfe309a25abe23291910eb641 (plain)
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";
  }
}