| 1 | ## SQLite storage: one file, WAL mode, every query scoped to one list. |
| 2 | ## |
| 3 | ## The binding is `db_connector/sqlite3`; the SQLite amalgamation is compiled |
| 4 | ## into the binary here, and the build passes `--dynlibOverride:sqlite3` so |
| 5 | ## the binding links against it instead of loading `libsqlite3.so`. |
| 6 | |
| 7 | import std/[sysrand, strutils, times] |
| 8 | import db_connector/sqlite3 |
| 9 | |
| 10 | const |
| 11 | sqliteSource {.strdefine.} = "../.cache/sqlite/sqlite3.c" |
| 12 | sqliteFlags = "-O2 -DSQLITE_THREADSAFE=0 -DSQLITE_DQS=0 " & |
| 13 | "-DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 " & |
| 14 | "-DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_DEPRECATED " & |
| 15 | "-DSQLITE_OMIT_SHARED_CACHE -DSQLITE_LIKE_DOESNT_MATCH_BLOBS" |
| 16 | |
| 17 | {.compile(sqliteSource, sqliteFlags).} |
| 18 | |
| 19 | type |
| 20 | StoreError* = object of CatchableError |
| 21 | |
| 22 | Todo* = object |
| 23 | id*: int64 |
| 24 | title*: string |
| 25 | completed*: bool |
| 26 | |
| 27 | Store* = ref object |
| 28 | db: PSqlite3 |
| 29 | stmts: seq[PStmt] |
| 30 | |
| 31 | Stmt = enum |
| 32 | sListExists, sCreateList, sTodos, sAdd, sToggle, sRename, sDestroy, |
| 33 | sCountAll, sCountCompleted, sSetAll, sClearCompleted |
| 34 | |
| 35 | const |
| 36 | # Migrations only ever add. Append; never edit or remove a shipped entry. |
| 37 | migrations = [ |
| 38 | """ |
| 39 | CREATE TABLE lists ( |
| 40 | id TEXT PRIMARY KEY, |
| 41 | created_at INTEGER NOT NULL |
| 42 | ) WITHOUT ROWID; |
| 43 | CREATE TABLE todos ( |
| 44 | id INTEGER PRIMARY KEY, |
| 45 | list_id TEXT NOT NULL REFERENCES lists(id), |
| 46 | title TEXT NOT NULL, |
| 47 | completed INTEGER NOT NULL DEFAULT 0, |
| 48 | created_at INTEGER NOT NULL |
| 49 | ); |
| 50 | CREATE INDEX todos_by_list ON todos(list_id, id); |
| 51 | """, |
| 52 | ] |
| 53 | |
| 54 | sql: array[Stmt, string] = [ |
| 55 | sListExists: "SELECT 1 FROM lists WHERE id = ?1", |
| 56 | sCreateList: "INSERT INTO lists (id, created_at) VALUES (?1, ?2)", |
| 57 | sTodos: "SELECT id, title, completed FROM todos WHERE list_id = ?1 " & |
| 58 | "AND (?2 = 0 OR completed = ?3) ORDER BY id", |
| 59 | sAdd: "INSERT INTO todos (list_id, title, created_at) VALUES (?1, ?2, ?3)", |
| 60 | sToggle: "UPDATE todos SET completed = 1 - completed " & |
| 61 | "WHERE list_id = ?1 AND id = ?2", |
| 62 | sRename: "UPDATE todos SET title = ?3 WHERE list_id = ?1 AND id = ?2", |
| 63 | sDestroy: "DELETE FROM todos WHERE list_id = ?1 AND id = ?2", |
| 64 | sCountAll: "SELECT count(*) FROM todos WHERE list_id = ?1", |
| 65 | sCountCompleted: "SELECT count(*) FROM todos WHERE list_id = ?1 AND completed = 1", |
| 66 | sSetAll: "UPDATE todos SET completed = ?2 WHERE list_id = ?1", |
| 67 | sClearCompleted: "DELETE FROM todos WHERE list_id = ?1 AND completed = 1", |
| 68 | ] |
| 69 | |
| 70 | proc check(s: Store, rc: int32): int32 {.discardable.} = |
| 71 | if rc notin [SQLITE_OK, SQLITE_ROW, SQLITE_DONE]: |
| 72 | raise newException(StoreError, $errmsg(s.db)) |
| 73 | rc |
| 74 | |
| 75 | proc execScript(s: Store, script: string) = |
| 76 | var err: cstring |
| 77 | if exec(s.db, script.cstring, nil, nil, err) != SQLITE_OK: |
| 78 | let msg = $err |
| 79 | free(err) |
| 80 | raise newException(StoreError, msg) |
| 81 | |
| 82 | proc scalar(s: Store, query: string): int64 = |
| 83 | var st: PStmt |
| 84 | s.check prepare_v2(s.db, query.cstring, -1, st, nil) |
| 85 | try: |
| 86 | if step(st) == SQLITE_ROW: result = column_int64(st, 0) |
| 87 | finally: |
| 88 | discard finalize(st) |
| 89 | |
| 90 | proc schemaVersion*(s: Store): int = s.scalar("PRAGMA user_version").int |
| 91 | |
| 92 | proc migrate(s: Store) = |
| 93 | for i in s.schemaVersion ..< migrations.len: |
| 94 | s.execScript("BEGIN;\n" & migrations[i] & "\nPRAGMA user_version = " & |
| 95 | $(i + 1) & ";\nCOMMIT;") |
| 96 | |
| 97 | proc open*(path: string): Store = |
| 98 | result = Store() |
| 99 | if sqlite3.open(path.cstring, result.db) != SQLITE_OK: |
| 100 | raise newException(StoreError, "cannot open " & path) |
| 101 | discard busy_timeout(result.db, 5000) |
| 102 | result.execScript("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; " & |
| 103 | "PRAGMA foreign_keys = ON;") |
| 104 | result.migrate() |
| 105 | result.stmts = newSeq[PStmt](Stmt.high.ord + 1) |
| 106 | for k in Stmt: |
| 107 | result.check prepare_v2(result.db, sql[k].cstring, -1, result.stmts[k.ord], nil) |
| 108 | |
| 109 | proc close*(s: Store) = |
| 110 | for st in s.stmts: discard finalize(st) |
| 111 | s.stmts.setLen(0) |
| 112 | discard sqlite3.close(s.db) |
| 113 | |
| 114 | # Statements are prepared once and reused: bind, step, then reset. |
| 115 | |
| 116 | type Arg = object |
| 117 | case isText: bool |
| 118 | of true: text: string |
| 119 | of false: num: int64 |
| 120 | |
| 121 | proc toArg(v: string): Arg = Arg(isText: true, text: v) |
| 122 | proc toArg(v: int64): Arg = Arg(isText: false, num: v) |
| 123 | proc toArg(v: int): Arg = Arg(isText: false, num: v.int64) |
| 124 | proc toArg(v: bool): Arg = Arg(isText: false, num: ord(v).int64) |
| 125 | |
| 126 | proc bound(s: Store, k: Stmt, args: openArray[Arg]): PStmt = |
| 127 | result = s.stmts[k.ord] |
| 128 | discard reset(result) |
| 129 | discard clear_bindings(result) |
| 130 | for i, a in args: |
| 131 | let n = int32(i + 1) |
| 132 | if a.isText: |
| 133 | s.check bind_text(result, n, a.text.cstring, a.text.len.int32, SQLITE_TRANSIENT) |
| 134 | else: |
| 135 | s.check bind_int64(result, n, a.num) |
| 136 | |
| 137 | proc exec(s: Store, k: Stmt, args: varargs[Arg, toArg]): int = |
| 138 | ## Runs a statement that returns no rows; answers the rows it changed. |
| 139 | let st = s.bound(k, args) |
| 140 | try: |
| 141 | s.check step(st) |
| 142 | result = changes(s.db).int |
| 143 | finally: |
| 144 | discard reset(st) |
| 145 | |
| 146 | proc count(s: Store, k: Stmt, args: varargs[Arg, toArg]): int = |
| 147 | let st = s.bound(k, args) |
| 148 | try: |
| 149 | if s.check(step(st)) == SQLITE_ROW: result = column_int64(st, 0).int |
| 150 | finally: |
| 151 | discard reset(st) |
| 152 | |
| 153 | proc now(): int64 = getTime().toUnix() |
| 154 | |
| 155 | # Lists |
| 156 | |
| 157 | proc newListId*(): string = |
| 158 | ## 128 random bits from the OS, as 32 hex characters. |
| 159 | for b in urandom(16): result.add b.toHex(2).toLowerAscii |
| 160 | |
| 161 | proc isWellFormedListId*(id: string): bool = |
| 162 | id.len == 32 and id.allCharsInSet({'0'..'9', 'a'..'f'}) |
| 163 | |
| 164 | proc listExists*(s: Store, id: string): bool = |
| 165 | isWellFormedListId(id) and s.count(sListExists, id) == 1 |
| 166 | |
| 167 | proc createList*(s: Store): string = |
| 168 | result = newListId() |
| 169 | discard s.exec(sCreateList, result, now()) |
| 170 | |
| 171 | # Todos |
| 172 | |
| 173 | type Filter* = enum |
| 174 | fAll = "all", fActive = "active", fCompleted = "completed" |
| 175 | |
| 176 | proc todos*(s: Store, list: string, filter = fAll): seq[Todo] = |
| 177 | let st = s.bound(sTodos, [toArg(list), toArg(filter != fAll), |
| 178 | toArg(filter == fCompleted)]) |
| 179 | try: |
| 180 | while s.check(step(st)) == SQLITE_ROW: |
| 181 | result.add Todo(id: column_int64(st, 0), title: $column_text(st, 1), |
| 182 | completed: column_int64(st, 2) != 0) |
| 183 | finally: |
| 184 | discard reset(st) |
| 185 | |
| 186 | proc counts*(s: Store, list: string): tuple[total, completed: int] = |
| 187 | (s.count(sCountAll, list), s.count(sCountCompleted, list)) |
| 188 | |
| 189 | proc add*(s: Store, list, title: string) = |
| 190 | discard s.exec(sAdd, list, title, now()) |
| 191 | |
| 192 | proc toggle*(s: Store, list: string, id: int64): bool = |
| 193 | ## False when `id` isn't in `list`. |
| 194 | s.exec(sToggle, list, id) == 1 |
| 195 | |
| 196 | proc rename*(s: Store, list: string, id: int64, title: string): bool = |
| 197 | s.exec(sRename, list, id, title) == 1 |
| 198 | |
| 199 | proc destroy*(s: Store, list: string, id: int64): bool = |
| 200 | s.exec(sDestroy, list, id) == 1 |
| 201 | |
| 202 | proc toggleAll*(s: Store, list: string) = |
| 203 | ## Completes every item, or reopens them all if all were already done. |
| 204 | let (total, completed) = s.counts(list) |
| 205 | discard s.exec(sSetAll, list, total == 0 or completed < total) |
| 206 | |
| 207 | proc clearCompleted*(s: Store, list: string) = |
| 208 | discard s.exec(sClearCompleted, list) |
| 209 | |
amadan