veryroundbird.house
1package db
2
3import (
4 "context"
5 "database/sql"
6
7 _ "github.com/mattn/go-sqlite3"
8)
9
10type DB struct {
11 *sql.DB
12}
13
14type Execer interface {
15 Query(query string, args ...any) (*sql.Rows, error)
16 QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
17 QueryRow(query string, args ...any) *sql.Row
18 QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
19 Exec(query string, args ...any) (sql.Result, error)
20 ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
21 Prepare(query string) (*sql.Stmt, error)
22 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
23}
24
25func Make(dbPath string) (*DB, error) {
26 db, err := sql.Open("sqlite3", dbPath)
27 if err != nil {
28 return nil, err
29 }
30 _, err = db.Exec(`
31 pragma journal_mode = WAL;
32 pragma synchronous = normal;
33 pragma foreign_keys = on;
34 pragma temp_store = memory;
35 pragma mmap_size = 30000000000;
36 pragma page_size = 32768;
37 pragma auto_vacuum = incremental;
38 pragma busy_timeout = 5000;
39
40 create table if not exists registrations (
41 id integer primary key autoincrement,
42 domain text not null unique,
43 did text not null,
44 secret text not null,
45 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
46 registered text
47 );
48 create table if not exists public_keys (
49 id integer primary key autoincrement,
50 did text not null,
51 name text not null,
52 key text not null,
53 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
54 unique(did, name, key)
55 );
56 create table if not exists repos (
57 id integer primary key autoincrement,
58 did text not null,
59 name text not null,
60 knot text not null,
61 rkey text not null,
62 at_uri text not null unique,
63 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
64 unique(did, name, knot, rkey)
65 );
66 create table if not exists collaborators (
67 id integer primary key autoincrement,
68 did text not null,
69 repo integer not null,
70 foreign key (repo) references repos(id) on delete cascade
71 );
72 create table if not exists follows (
73 user_did text not null,
74 subject_did text not null,
75 rkey text not null,
76 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
77 primary key (user_did, subject_did),
78 check (user_did <> subject_did)
79 );
80 create table if not exists issues (
81 id integer primary key autoincrement,
82 owner_did text not null,
83 repo_at text not null,
84 issue_id integer not null,
85 title text not null,
86 body text not null,
87 open integer not null default 1,
88 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
89 issue_at text,
90 unique(repo_at, issue_id),
91 foreign key (repo_at) references repos(at_uri) on delete cascade
92 );
93 create table if not exists comments (
94 id integer primary key autoincrement,
95 owner_did text not null,
96 issue_id integer not null,
97 repo_at text not null,
98 comment_id integer not null,
99 comment_at text not null,
100 body text not null,
101 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
102 unique(issue_id, comment_id),
103 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
104 );
105 create table if not exists _jetstream (
106 id integer primary key autoincrement,
107 last_time_us integer not null
108 );
109
110 create table if not exists repo_issue_seqs (
111 repo_at text primary key,
112 next_issue_id integer not null default 1
113 );
114
115 `)
116 if err != nil {
117 return nil, err
118 }
119 return &DB{db}, nil
120}