nel.pet
1package db
2
3import (
4 "database/sql"
5 "strings"
6
7 _ "github.com/mattn/go-sqlite3"
8)
9
10type DB struct {
11 *sql.DB
12}
13
14func Make(dbPath string) (*DB, error) {
15 // https://github.com/mattn/go-sqlite3#connection-string
16 opts := []string{
17 "_foreign_keys=1",
18 "_journal_mode=WAL",
19 "_synchronous=NORMAL",
20 "_auto_vacuum=incremental",
21 }
22
23 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
24 if err != nil {
25 return nil, err
26 }
27
28 // NOTE: If any other migration is added here, you MUST
29 // copy the pattern in appview: use a single sql.Conn
30 // for every migration.
31
32 _, err = db.Exec(`
33 create table if not exists _jetstream (
34 id integer primary key autoincrement,
35 last_time_us integer not null
36 );
37
38 create table if not exists known_dids (
39 did text primary key
40 );
41
42 create table if not exists repos (
43 id integer primary key autoincrement,
44 knot text not null,
45 owner text not null,
46 name text not null,
47 addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
48
49 unique(owner, name)
50 );
51
52 create table if not exists spindle_members (
53 -- identifiers for the record
54 id integer primary key autoincrement,
55 did text not null,
56 rkey text not null,
57
58 -- data
59 instance text not null,
60 subject text not null,
61 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
62
63 -- constraints
64 unique (did, instance, subject)
65 );
66
67 -- status event for a single workflow
68 create table if not exists events (
69 rkey text not null,
70 nsid text not null,
71 event text not null, -- json
72 created integer not null -- unix nanos
73 );
74 `)
75 if err != nil {
76 return nil, err
77 }
78
79 return &DB{db}, nil
80}
81
82func (d *DB) SaveLastTimeUs(lastTimeUs int64) error {
83 _, err := d.Exec(`
84 insert into _jetstream (id, last_time_us)
85 values (1, ?)
86 on conflict(id) do update set last_time_us = excluded.last_time_us
87 `, lastTimeUs)
88 return err
89}
90
91func (d *DB) GetLastTimeUs() (int64, error) {
92 var lastTimeUs int64
93 row := d.QueryRow(`select last_time_us from _jetstream where id = 1;`)
94 err := row.Scan(&lastTimeUs)
95 return lastTimeUs, err
96}