veryroundbird.house
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)
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 foreign_keys = on;
23 pragma temp_store = memory;
24 pragma mmap_size = 30000000000;
25 pragma page_size = 32768;
26 pragma auto_vacuum = incremental;
27 pragma busy_timeout = 5000;
28
29 create table if not exists _jetstream (
30 id integer primary key autoincrement,
31 last_time_us integer not null
32 );
33
34 create table if not exists known_dids (
35 did text primary key
36 );
37
38 create table if not exists repos (
39 id integer primary key autoincrement,
40 knot text not null,
41 owner text not null,
42 name text not null,
43 addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
44
45 unique(owner, name)
46 );
47
48 create table if not exists spindle_members (
49 -- identifiers for the record
50 id integer primary key autoincrement,
51 did text not null,
52 rkey text not null,
53
54 -- data
55 instance text not null,
56 subject text not null,
57 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
58
59 -- constraints
60 unique (did, instance, subject)
61 );
62
63 -- status event for a single workflow
64 create table if not exists events (
65 rkey text not null,
66 nsid text not null,
67 event text not null, -- json
68 created integer not null -- unix nanos
69 );
70 `)
71 if err != nil {
72 return nil, err
73 }
74
75 return &DB{db}, nil
76}
77
78func (d *DB) SaveLastTimeUs(lastTimeUs int64) error {
79 _, err := d.Exec(`
80 insert into _jetstream (id, last_time_us)
81 values (1, ?)
82 on conflict(id) do update set last_time_us = excluded.last_time_us
83 `, lastTimeUs)
84 return err
85}
86
87func (d *DB) GetLastTimeUs() (int64, error) {
88 var lastTimeUs int64
89 row := d.QueryRow(`select last_time_us from _jetstream where id = 1;`)
90 err := row.Scan(&lastTimeUs)
91 return lastTimeUs, err
92}