Databases: use db

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

use db

conn = connect['sqlite://:memory:']
exec[conn, 'CREATE TABLE t (k TEXT, v INTEGER)']
exec[conn, 'INSERT INTO t VALUES (?, ?)', ['a', 1]]
exec[conn, 'INSERT INTO t VALUES (?, ?)', ['b', 2]]
rows = query[conn, 'SELECT k, v FROM t ORDER BY k']
log[rows.k]
log[rows.v]

fn add_c[tx]
    exec[tx, 'INSERT INTO t VALUES (?, ?)', ['c', 3]]

transact[conn, add_c]
log[len[query[conn, 'SELECT k FROM t']]]
log[describe[conn, 'SELECT k, v FROM t']]

--- a function that fails rolls its transaction back, and transact
--- answers its error
fn add_d_then_fail[tx]
    exec[tx, 'INSERT INTO t VALUES (?, ?)', ['d', 4]]
    return error['not today', 'db']

r = transact[conn, add_d_then_fail]
log[r.ok]
log[r.reason]
log[len[query[conn, 'SELECT k FROM t']]]

--- bulk-load a frame, and apply .sql migrations from a directory
use frame
write['ex_rows.csv', 'k,v\nd,4\ne,5\n']
more = frame.load['ex_rows.csv']
log[insert[conn, 't', more]]
log[len[query[conn, 'SELECT k FROM t']]]
mkdir['ex_migrations']
write['ex_migrations/001_audit.sql', 'CREATE TABLE audit (note TEXT)']
log[migrate[conn, 'ex_migrations']]
log[migrate[conn, 'ex_migrations']]
rm['ex_rows.csv']
rm['ex_migrations']