matmanna.dev
forked from
tangled.org/core
Monorepo for Tangled
boltless.me
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 "_busy_timeout=5000",
22 }
23
24 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
25 if err != nil {
26 return nil, err
27 }
28
29 // NOTE: If any other migration is added here, you MUST
30 // copy the pattern in appview: use a single sql.Conn
31 // for every migration.
32
33 _, err = db.Exec(`
34 create table if not exists _jetstream (
35 id integer primary key autoincrement,
36 last_time_us integer not null
37 );
38
39 create table if not exists known_dids (
40 did text primary key
41 );
42
43 create table if not exists repos (
44 id integer primary key autoincrement,
45 knot text not null,
46 owner text not null,
47 name text not null,
48 addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
49
50 unique(owner, name)
51 );
52
53 create table if not exists spindle_members (
54 -- identifiers for the record
55 id integer primary key autoincrement,
56 did text not null,
57 rkey text not null,
58
59 -- data
60 instance text not null,
61 subject text not null,
62 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
63
64 -- constraints
65 unique (did, instance, subject)
66 );
67
68 -- status event for a single workflow
69 create table if not exists events (
70 rkey text not null,
71 nsid text not null,
72 event text not null, -- json
73 created integer not null -- unix nanos
74 );
75 `)
76 if err != nil {
77 return nil, err
78 }
79
80 return &DB{db}, nil
81}
82
83func (d *DB) SaveLastTimeUs(lastTimeUs int64) error {
84 _, err := d.Exec(`
85 insert into _jetstream (id, last_time_us)
86 values (1, ?)
87 on conflict(id) do update set last_time_us = excluded.last_time_us
88 `, lastTimeUs)
89 return err
90}
91
92func (d *DB) GetLastTimeUs() (int64, error) {
93 var lastTimeUs int64
94 row := d.QueryRow(`select last_time_us from _jetstream where id = 1;`)
95 err := row.Scan(&lastTimeUs)
96 return lastTimeUs, err
97}