nel.pet
1package db
2
3import (
4 "database/sql"
5 "strings"
6
7 _ "github.com/mattn/go-sqlite3"
8)
9
10type DB struct {
11 db *sql.DB
12}
13
14func Setup(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 known_dids (
34 did text primary key
35 );
36
37 create table if not exists public_keys (
38 id integer primary key autoincrement,
39 did text not null,
40 key text not null,
41 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
42 unique(did, key),
43 foreign key (did) references known_dids(did) on delete cascade
44 );
45
46 create table if not exists _jetstream (
47 id integer primary key autoincrement,
48 last_time_us integer not null
49 );
50
51 create table if not exists events (
52 rkey text not null,
53 nsid text not null,
54 event text not null, -- json
55 created integer not null default (strftime('%s', 'now')),
56 primary key (rkey, nsid)
57 );
58 `)
59 if err != nil {
60 return nil, err
61 }
62
63 return &DB{db: db}, nil
64}