veryroundbird.house
1package db
2
3import (
4 "context"
5 "database/sql"
6 "log"
7
8 _ "github.com/mattn/go-sqlite3"
9)
10
11type DB struct {
12 *sql.DB
13}
14
15type Execer interface {
16 Query(query string, args ...any) (*sql.Rows, error)
17 QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
18 QueryRow(query string, args ...any) *sql.Row
19 QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
20 Exec(query string, args ...any) (sql.Result, error)
21 ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
22 Prepare(query string) (*sql.Stmt, error)
23 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
24}
25
26func Make(dbPath string) (*DB, error) {
27 db, err := sql.Open("sqlite3", dbPath)
28 if err != nil {
29 return nil, err
30 }
31 _, err = db.Exec(`
32 pragma journal_mode = WAL;
33 pragma synchronous = normal;
34 pragma foreign_keys = on;
35 pragma temp_store = memory;
36 pragma mmap_size = 30000000000;
37 pragma page_size = 32768;
38 pragma auto_vacuum = incremental;
39 pragma busy_timeout = 5000;
40
41 create table if not exists registrations (
42 id integer primary key autoincrement,
43 domain text not null unique,
44 did text not null,
45 secret text not null,
46 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
47 registered text
48 );
49 create table if not exists public_keys (
50 id integer primary key autoincrement,
51 did text not null,
52 name text not null,
53 key text not null,
54 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
55 unique(did, name, key)
56 );
57 create table if not exists repos (
58 id integer primary key autoincrement,
59 did text not null,
60 name text not null,
61 knot text not null,
62 rkey text not null,
63 at_uri text not null unique,
64 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
65 unique(did, name, knot, rkey)
66 );
67 create table if not exists collaborators (
68 id integer primary key autoincrement,
69 did text not null,
70 repo integer not null,
71 foreign key (repo) references repos(id) on delete cascade
72 );
73 create table if not exists follows (
74 user_did text not null,
75 subject_did text not null,
76 rkey text not null,
77 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
78 primary key (user_did, subject_did),
79 check (user_did <> subject_did)
80 );
81 create table if not exists issues (
82 id integer primary key autoincrement,
83 owner_did text not null,
84 repo_at text not null,
85 issue_id integer not null,
86 title text not null,
87 body text not null,
88 open integer not null default 1,
89 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
90 issue_at text,
91 unique(repo_at, issue_id),
92 foreign key (repo_at) references repos(at_uri) on delete cascade
93 );
94 create table if not exists comments (
95 id integer primary key autoincrement,
96 owner_did text not null,
97 issue_id integer not null,
98 repo_at text not null,
99 comment_id integer not null,
100 comment_at text not null,
101 body text not null,
102 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
103 unique(issue_id, comment_id),
104 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
105 );
106 create table if not exists pulls (
107 -- identifiers
108 id integer primary key autoincrement,
109 pull_id integer not null,
110
111 -- at identifiers
112 repo_at text not null,
113 owner_did text not null,
114 rkey text not null,
115 pull_at text,
116
117 -- content
118 title text not null,
119 body text not null,
120 target_branch text not null,
121 state integer not null default 0 check (state in (0, 1, 2)), -- open, merged, closed
122
123 -- meta
124 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
125
126 -- constraints
127 unique(repo_at, pull_id),
128 foreign key (repo_at) references repos(at_uri) on delete cascade
129 );
130
131 -- every pull must have atleast 1 submission: the initial submission
132 create table if not exists pull_submissions (
133 -- identifiers
134 id integer primary key autoincrement,
135 pull_id integer not null,
136
137 -- at identifiers
138 repo_at text not null,
139
140 -- content, these are immutable, and require a resubmission to update
141 round_number integer not null default 0,
142 patch text,
143
144 -- meta
145 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
146
147 -- constraints
148 unique(repo_at, pull_id, round_number),
149 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade
150 );
151
152 create table if not exists pull_comments (
153 -- identifiers
154 id integer primary key autoincrement,
155 pull_id integer not null,
156 submission_id integer not null,
157
158 -- at identifiers
159 repo_at text not null,
160 owner_did text not null,
161 comment_at text not null,
162
163 -- content
164 body text not null,
165
166 -- meta
167 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
168
169 -- constraints
170 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade,
171 foreign key (submission_id) references pull_submissions(id) on delete cascade
172 );
173
174 create table if not exists _jetstream (
175 id integer primary key autoincrement,
176 last_time_us integer not null
177 );
178
179 create table if not exists repo_issue_seqs (
180 repo_at text primary key,
181 next_issue_id integer not null default 1
182 );
183
184 create table if not exists repo_pull_seqs (
185 repo_at text primary key,
186 next_pull_id integer not null default 1
187 );
188
189 create table if not exists stars (
190 id integer primary key autoincrement,
191 starred_by_did text not null,
192 repo_at text not null,
193 rkey text not null,
194 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
195 foreign key (repo_at) references repos(at_uri) on delete cascade,
196 unique(starred_by_did, repo_at)
197 );
198
199 create table if not exists emails (
200 id integer primary key autoincrement,
201 did text not null,
202 email text not null,
203 verified integer not null default 0,
204 verification_code text not null,
205 last_sent text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
206 is_primary integer not null default 0,
207 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
208 unique(did, email)
209 );
210
211 create table if not exists artifacts (
212 -- id
213 id integer primary key autoincrement,
214 did text not null,
215 rkey text not null,
216
217 -- meta
218 repo_at text not null,
219 tag binary(20) not null,
220 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
221
222 -- data
223 blob_cid text not null,
224 name text not null,
225 size integer not null default 0,
226 mimetype string not null default "*/*",
227
228 -- constraints
229 unique(did, rkey), -- record must be unique
230 unique(repo_at, tag, name), -- for a given tag object, each file must be unique
231 foreign key (repo_at) references repos(at_uri) on delete cascade
232 );
233
234 create table if not exists migrations (
235 id integer primary key autoincrement,
236 name text unique
237 )
238 `)
239 if err != nil {
240 return nil, err
241 }
242
243 // run migrations
244 runMigration(db, "add-description-to-repos", func(tx *sql.Tx) error {
245 tx.Exec(`
246 alter table repos add column description text check (length(description) <= 200);
247 `)
248 return nil
249 })
250
251 runMigration(db, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
252 // add unconstrained column
253 _, err := tx.Exec(`
254 alter table public_keys
255 add column rkey text;
256 `)
257 if err != nil {
258 return err
259 }
260
261 // backfill
262 _, err = tx.Exec(`
263 update public_keys
264 set rkey = ''
265 where rkey is null;
266 `)
267 if err != nil {
268 return err
269 }
270
271 return nil
272 })
273
274 runMigration(db, "add-rkey-to-comments", func(tx *sql.Tx) error {
275 _, err := tx.Exec(`
276 alter table comments drop column comment_at;
277 alter table comments add column rkey text;
278 `)
279 return err
280 })
281
282 runMigration(db, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
283 _, err := tx.Exec(`
284 alter table comments add column deleted text; -- timestamp
285 alter table comments add column edited text; -- timestamp
286 `)
287 return err
288 })
289
290 runMigration(db, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
291 _, err := tx.Exec(`
292 alter table pulls add column source_branch text;
293 alter table pulls add column source_repo_at text;
294 alter table pull_submissions add column source_rev text;
295 `)
296 return err
297 })
298
299 runMigration(db, "add-source-to-repos", func(tx *sql.Tx) error {
300 _, err := tx.Exec(`
301 alter table repos add column source text;
302 `)
303 return err
304 })
305
306 return &DB{db}, nil
307}
308
309type migrationFn = func(*sql.Tx) error
310
311func runMigration(d *sql.DB, name string, migrationFn migrationFn) error {
312 tx, err := d.Begin()
313 if err != nil {
314 return err
315 }
316 defer tx.Rollback()
317
318 var exists bool
319 err = tx.QueryRow("select exists (select 1 from migrations where name = ?)", name).Scan(&exists)
320 if err != nil {
321 return err
322 }
323
324 if !exists {
325 // run migration
326 err = migrationFn(tx)
327 if err != nil {
328 log.Printf("Failed to run migration %s: %v", name, err)
329 return err
330 }
331
332 // mark migration as complete
333 _, err = tx.Exec("insert into migrations (name) values (?)", name)
334 if err != nil {
335 log.Printf("Failed to mark migration %s as complete: %v", name, err)
336 return err
337 }
338
339 // commit the transaction
340 if err := tx.Commit(); err != nil {
341 return err
342 }
343
344 log.Printf("migration %s applied successfully", name)
345 } else {
346 log.Printf("skipped migration %s, already applied", name)
347 }
348
349 return nil
350}