summaryrefslogtreecommitdiffhomepage
path: root/dlang/src/ocda/sqlite.d
blob: fc6b2bee7359c95dd622ff7f5a2ceb28b7dc2a3e (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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
/+
- Name: SisuDoc Spine, Doc Reform [a part of]
  - Description: documents, structuring, processing, publishing, search
    - static content generator

  - Author: Ralph Amissah
    [ralph.amissah@gmail.com]

  - Copyright: (C) 2015 (continuously updated, current 2026) Ralph Amissah, All Rights Reserved.

  - License: AGPL 3 or later:

    Spine (SiSU), a framework for document structuring, publishing and
    search

    Copyright (C) Ralph Amissah

    This program is free software: you can redistribute it and/or modify it
    under the terms of the GNU AFERO General Public License as published by the
    Free Software Foundation, either version 3 of the License, or (at your
    option) any later version.

    This program is distributed in the hope that it will be useful, but WITHOUT
    ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
    FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
    more details.

    You should have received a copy of the GNU General Public License along with
    this program. If not, see [https://www.gnu.org/licenses/].

    If you have Internet connection, the latest version of the AGPL should be
    available at these locations:
    [https://www.fsf.org/licensing/licenses/agpl.html]
    [https://www.gnu.org/licenses/agpl.html]

  - Spine (by Doc Reform, related to SiSU) uses standard:
    - docReform markup syntax
      - standard SiSU markup syntax with modified headers and minor modifications
    - docReform object numbering
      - standard SiSU object citation numbering & system

  - Homepages:
    [https://www.sisudoc.org]
    [https://www.doc-reform.org]

  - Git
    [https://git.sisudoc.org/]

+/
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";
  }
}