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