this repo has no description
tangled.org
1package db
2
3import (
4 "database/sql"
5
6 _ "github.com/mattn/go-sqlite3"
7)
8
9type DB struct {
10 *sql.DB
11}
12
13func Make(dbPath string) (*DB, error) {
14 db, err := sql.Open("sqlite3", dbPath+"?_foreign_keys=1")
15 if err != nil {
16 return nil, err
17 }
18
19 _, err = db.Exec(`
20 pragma journal_mode = WAL;
21 pragma synchronous = normal;
22 pragma temp_store = memory;
23 pragma mmap_size = 30000000000;
24 pragma page_size = 32768;
25 pragma auto_vacuum = incremental;
26 pragma busy_timeout = 5000;
27
28 create table if not exists _jetstream (
29 id integer primary key autoincrement,
30 last_time_us integer not null
31 );
32
33 create table if not exists known_dids (
34 did text primary key
35 );
36
37 create table if not exists repos (
38 id integer primary key autoincrement,
39 knot text not null,
40 owner text not null,
41 name text not null,
42 addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
43
44 unique(owner, name)
45 );
46
47 create table if not exists spindle_members (
48 -- identifiers for the record
49 id integer primary key autoincrement,
50 did text not null,
51 rkey text not null,
52
53 -- data
54 instance text not null,
55 subject text not null,
56 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
57
58 -- constraints
59 unique (did, instance, subject)
60 );
61
62 -- status event for a single workflow
63 create table if not exists events (
64 rkey text not null,
65 nsid text not null,
66 event text not null, -- json
67 created integer not null -- unix nanos
68 );
69 `)
70 if err != nil {
71 return nil, err
72 }
73
74 return &DB{db}, nil
75}
76
77func (d *DB) SaveLastTimeUs(lastTimeUs int64) error {
78 _, err := d.Exec(`
79 insert into _jetstream (id, last_time_us)
80 values (1, ?)
81 on conflict(id) do update set last_time_us = excluded.last_time_us
82 `, lastTimeUs)
83 return err
84}
85
86func (d *DB) GetLastTimeUs() (int64, error) {
87 var lastTimeUs int64
88 row := d.QueryRow(`select last_time_us from _jetstream where id = 1;`)
89 err := row.Scan(&lastTimeUs)
90 return lastTimeUs, err
91}