this repo has no description
tangled.org
1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log"
8 "reflect"
9 "strings"
10
11 _ "github.com/mattn/go-sqlite3"
12)
13
14type DB struct {
15 *sql.DB
16}
17
18type Execer interface {
19 Query(query string, args ...any) (*sql.Rows, error)
20 QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
21 QueryRow(query string, args ...any) *sql.Row
22 QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
23 Exec(query string, args ...any) (sql.Result, error)
24 ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
25 Prepare(query string) (*sql.Stmt, error)
26 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
27}
28
29func Make(dbPath string) (*DB, error) {
30 db, err := sql.Open("sqlite3", dbPath+"?_foreign_keys=1")
31 if err != nil {
32 return nil, err
33 }
34 _, err = db.Exec(`
35 pragma journal_mode = WAL;
36 pragma synchronous = normal;
37 pragma temp_store = memory;
38 pragma mmap_size = 30000000000;
39 pragma page_size = 32768;
40 pragma auto_vacuum = incremental;
41 pragma busy_timeout = 5000;
42
43 create table if not exists registrations (
44 id integer primary key autoincrement,
45 domain text not null unique,
46 did text not null,
47 secret text not null,
48 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
49 registered text
50 );
51 create table if not exists public_keys (
52 id integer primary key autoincrement,
53 did text not null,
54 name text not null,
55 key text not null,
56 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
57 unique(did, name, key)
58 );
59 create table if not exists repos (
60 id integer primary key autoincrement,
61 did text not null,
62 name text not null,
63 knot text not null,
64 rkey text not null,
65 at_uri text not null unique,
66 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
67 unique(did, name, knot, rkey)
68 );
69 create table if not exists collaborators (
70 id integer primary key autoincrement,
71 did text not null,
72 repo integer not null,
73 foreign key (repo) references repos(id) on delete cascade
74 );
75 create table if not exists follows (
76 user_did text not null,
77 subject_did text not null,
78 rkey text not null,
79 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
80 primary key (user_did, subject_did),
81 check (user_did <> subject_did)
82 );
83 create table if not exists issues (
84 id integer primary key autoincrement,
85 owner_did text not null,
86 repo_at text not null,
87 issue_id integer not null,
88 title text not null,
89 body text not null,
90 open integer not null default 1,
91 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
92 issue_at text,
93 unique(repo_at, issue_id),
94 foreign key (repo_at) references repos(at_uri) on delete cascade
95 );
96 create table if not exists comments (
97 id integer primary key autoincrement,
98 owner_did text not null,
99 issue_id integer not null,
100 repo_at text not null,
101 comment_id integer not null,
102 comment_at text not null,
103 body text not null,
104 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
105 unique(issue_id, comment_id),
106 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
107 );
108 create table if not exists pulls (
109 -- identifiers
110 id integer primary key autoincrement,
111 pull_id integer not null,
112
113 -- at identifiers
114 repo_at text not null,
115 owner_did text not null,
116 rkey text not null,
117 pull_at text,
118
119 -- content
120 title text not null,
121 body text not null,
122 target_branch text not null,
123 state integer not null default 0 check (state in (0, 1, 2)), -- open, merged, closed
124
125 -- meta
126 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
127
128 -- constraints
129 unique(repo_at, pull_id),
130 foreign key (repo_at) references repos(at_uri) on delete cascade
131 );
132
133 -- every pull must have atleast 1 submission: the initial submission
134 create table if not exists pull_submissions (
135 -- identifiers
136 id integer primary key autoincrement,
137 pull_id integer not null,
138
139 -- at identifiers
140 repo_at text not null,
141
142 -- content, these are immutable, and require a resubmission to update
143 round_number integer not null default 0,
144 patch text,
145
146 -- meta
147 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
148
149 -- constraints
150 unique(repo_at, pull_id, round_number),
151 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade
152 );
153
154 create table if not exists pull_comments (
155 -- identifiers
156 id integer primary key autoincrement,
157 pull_id integer not null,
158 submission_id integer not null,
159
160 -- at identifiers
161 repo_at text not null,
162 owner_did text not null,
163 comment_at text not null,
164
165 -- content
166 body text not null,
167
168 -- meta
169 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
170
171 -- constraints
172 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade,
173 foreign key (submission_id) references pull_submissions(id) on delete cascade
174 );
175
176 create table if not exists _jetstream (
177 id integer primary key autoincrement,
178 last_time_us integer not null
179 );
180
181 create table if not exists repo_issue_seqs (
182 repo_at text primary key,
183 next_issue_id integer not null default 1
184 );
185
186 create table if not exists repo_pull_seqs (
187 repo_at text primary key,
188 next_pull_id integer not null default 1
189 );
190
191 create table if not exists stars (
192 id integer primary key autoincrement,
193 starred_by_did text not null,
194 repo_at text not null,
195 rkey text not null,
196 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
197 foreign key (repo_at) references repos(at_uri) on delete cascade,
198 unique(starred_by_did, repo_at)
199 );
200
201 create table if not exists reactions (
202 id integer primary key autoincrement,
203 reacted_by_did text not null,
204 thread_at text not null,
205 kind text not null,
206 rkey text not null,
207 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
208 unique(reacted_by_did, thread_at, kind)
209 );
210
211 create table if not exists emails (
212 id integer primary key autoincrement,
213 did text not null,
214 email text not null,
215 verified integer not null default 0,
216 verification_code text not null,
217 last_sent text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
218 is_primary integer not null default 0,
219 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
220 unique(did, email)
221 );
222
223 create table if not exists artifacts (
224 -- id
225 id integer primary key autoincrement,
226 did text not null,
227 rkey text not null,
228
229 -- meta
230 repo_at text not null,
231 tag binary(20) not null,
232 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
233
234 -- data
235 blob_cid text not null,
236 name text not null,
237 size integer not null default 0,
238 mimetype string not null default "*/*",
239
240 -- constraints
241 unique(did, rkey), -- record must be unique
242 unique(repo_at, tag, name), -- for a given tag object, each file must be unique
243 foreign key (repo_at) references repos(at_uri) on delete cascade
244 );
245
246 create table if not exists profile (
247 -- id
248 id integer primary key autoincrement,
249 did text not null,
250
251 -- data
252 description text not null,
253 include_bluesky integer not null default 0,
254 location text,
255
256 -- constraints
257 unique(did)
258 );
259 create table if not exists profile_links (
260 -- id
261 id integer primary key autoincrement,
262 did text not null,
263
264 -- data
265 link text not null,
266
267 -- constraints
268 foreign key (did) references profile(did) on delete cascade
269 );
270 create table if not exists profile_stats (
271 -- id
272 id integer primary key autoincrement,
273 did text not null,
274
275 -- data
276 kind text not null check (kind in (
277 "merged-pull-request-count",
278 "closed-pull-request-count",
279 "open-pull-request-count",
280 "open-issue-count",
281 "closed-issue-count",
282 "repository-count"
283 )),
284
285 -- constraints
286 foreign key (did) references profile(did) on delete cascade
287 );
288 create table if not exists profile_pinned_repositories (
289 -- id
290 id integer primary key autoincrement,
291 did text not null,
292
293 -- data
294 at_uri text not null,
295
296 -- constraints
297 unique(did, at_uri),
298 foreign key (did) references profile(did) on delete cascade,
299 foreign key (at_uri) references repos(at_uri) on delete cascade
300 );
301
302 create table if not exists oauth_requests (
303 id integer primary key autoincrement,
304 auth_server_iss text not null,
305 state text not null,
306 did text not null,
307 handle text not null,
308 pds_url text not null,
309 pkce_verifier text not null,
310 dpop_auth_server_nonce text not null,
311 dpop_private_jwk text not null
312 );
313
314 create table if not exists oauth_sessions (
315 id integer primary key autoincrement,
316 did text not null,
317 handle text not null,
318 pds_url text not null,
319 auth_server_iss text not null,
320 access_jwt text not null,
321 refresh_jwt text not null,
322 dpop_pds_nonce text,
323 dpop_auth_server_nonce text not null,
324 dpop_private_jwk text not null,
325 expiry text not null
326 );
327
328 create table if not exists punchcard (
329 did text not null,
330 date text not null, -- yyyy-mm-dd
331 count integer,
332 primary key (did, date)
333 );
334
335 create table if not exists spindles (
336 id integer primary key autoincrement,
337 owner text not null,
338 instance text not null,
339 verified text, -- time of verification
340 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
341
342 unique(owner, instance)
343 );
344
345 create table if not exists spindle_members (
346 -- identifiers for the record
347 id integer primary key autoincrement,
348 did text not null,
349 rkey text not null,
350
351 -- data
352 instance text not null,
353 subject text not null,
354 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
355
356 -- constraints
357 unique (did, instance, subject)
358 );
359
360 create table if not exists pipelines (
361 -- identifiers
362 id integer primary key autoincrement,
363 knot text not null,
364 rkey text not null,
365
366 repo_owner text not null,
367 repo_name text not null,
368
369 -- every pipeline must be associated with exactly one commit
370 sha text not null check (length(sha) = 40),
371 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
372
373 -- trigger data
374 trigger_id integer not null,
375
376 unique(knot, rkey),
377 foreign key (trigger_id) references triggers(id) on delete cascade
378 );
379
380 create table if not exists triggers (
381 -- primary key
382 id integer primary key autoincrement,
383
384 -- top-level fields
385 kind text not null,
386
387 -- pushTriggerData fields
388 push_ref text,
389 push_new_sha text check (length(push_new_sha) = 40),
390 push_old_sha text check (length(push_old_sha) = 40),
391
392 -- pullRequestTriggerData fields
393 pr_source_branch text,
394 pr_target_branch text,
395 pr_source_sha text check (length(pr_source_sha) = 40),
396 pr_action text
397 );
398
399 create table if not exists pipeline_statuses (
400 -- identifiers
401 id integer primary key autoincrement,
402 spindle text not null,
403 rkey text not null,
404
405 -- referenced pipeline. these form the (did, rkey) pair
406 pipeline_knot text not null,
407 pipeline_rkey text not null,
408
409 -- content
410 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
411 workflow text not null,
412 status text not null,
413 error text,
414 exit_code integer not null default 0,
415
416 unique (spindle, rkey),
417 foreign key (pipeline_knot, pipeline_rkey)
418 references pipelines (knot, rkey)
419 on delete cascade
420 );
421
422 create table if not exists repo_languages (
423 -- identifiers
424 id integer primary key autoincrement,
425
426 -- repo identifiers
427 repo_at text not null,
428 ref text not null,
429 is_default_ref integer not null default 0,
430
431 -- language breakdown
432 language text not null,
433 bytes integer not null check (bytes >= 0),
434
435 unique(repo_at, ref, language)
436 );
437
438 create table if not exists signups_inflight (
439 id integer primary key autoincrement,
440 email text not null unique,
441 invite_code text not null,
442 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
443 );
444
445 create table if not exists strings (
446 -- identifiers
447 did text not null,
448 rkey text not null,
449
450 -- content
451 filename text not null,
452 description text,
453 content text not null,
454 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
455 edited text,
456
457 primary key (did, rkey)
458 );
459
460 create table if not exists migrations (
461 id integer primary key autoincrement,
462 name text unique
463 );
464 `)
465 if err != nil {
466 return nil, err
467 }
468
469 // run migrations
470 runMigration(db, "add-description-to-repos", func(tx *sql.Tx) error {
471 tx.Exec(`
472 alter table repos add column description text check (length(description) <= 200);
473 `)
474 return nil
475 })
476
477 runMigration(db, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
478 // add unconstrained column
479 _, err := tx.Exec(`
480 alter table public_keys
481 add column rkey text;
482 `)
483 if err != nil {
484 return err
485 }
486
487 // backfill
488 _, err = tx.Exec(`
489 update public_keys
490 set rkey = ''
491 where rkey is null;
492 `)
493 if err != nil {
494 return err
495 }
496
497 return nil
498 })
499
500 runMigration(db, "add-rkey-to-comments", func(tx *sql.Tx) error {
501 _, err := tx.Exec(`
502 alter table comments drop column comment_at;
503 alter table comments add column rkey text;
504 `)
505 return err
506 })
507
508 runMigration(db, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
509 _, err := tx.Exec(`
510 alter table comments add column deleted text; -- timestamp
511 alter table comments add column edited text; -- timestamp
512 `)
513 return err
514 })
515
516 runMigration(db, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
517 _, err := tx.Exec(`
518 alter table pulls add column source_branch text;
519 alter table pulls add column source_repo_at text;
520 alter table pull_submissions add column source_rev text;
521 `)
522 return err
523 })
524
525 runMigration(db, "add-source-to-repos", func(tx *sql.Tx) error {
526 _, err := tx.Exec(`
527 alter table repos add column source text;
528 `)
529 return err
530 })
531
532 // disable foreign-keys for the next migration
533 // NOTE: this cannot be done in a transaction, so it is run outside [0]
534 //
535 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
536 db.Exec("pragma foreign_keys = off;")
537 runMigration(db, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
538 _, err := tx.Exec(`
539 create table pulls_new (
540 -- identifiers
541 id integer primary key autoincrement,
542 pull_id integer not null,
543
544 -- at identifiers
545 repo_at text not null,
546 owner_did text not null,
547 rkey text not null,
548
549 -- content
550 title text not null,
551 body text not null,
552 target_branch text not null,
553 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
554
555 -- source info
556 source_branch text,
557 source_repo_at text,
558
559 -- stacking
560 stack_id text,
561 change_id text,
562 parent_change_id text,
563
564 -- meta
565 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
566
567 -- constraints
568 unique(repo_at, pull_id),
569 foreign key (repo_at) references repos(at_uri) on delete cascade
570 );
571
572 insert into pulls_new (
573 id, pull_id,
574 repo_at, owner_did, rkey,
575 title, body, target_branch, state,
576 source_branch, source_repo_at,
577 created
578 )
579 select
580 id, pull_id,
581 repo_at, owner_did, rkey,
582 title, body, target_branch, state,
583 source_branch, source_repo_at,
584 created
585 FROM pulls;
586
587 drop table pulls;
588 alter table pulls_new rename to pulls;
589 `)
590 return err
591 })
592 db.Exec("pragma foreign_keys = on;")
593
594 // run migrations
595 runMigration(db, "add-spindle-to-repos", func(tx *sql.Tx) error {
596 tx.Exec(`
597 alter table repos add column spindle text;
598 `)
599 return nil
600 })
601
602 // recreate and add rkey + created columns with default constraint
603 runMigration(db, "rework-collaborators-table", func(tx *sql.Tx) error {
604 // create new table
605 // - repo_at instead of repo integer
606 // - rkey field
607 // - created field
608 _, err := tx.Exec(`
609 create table collaborators_new (
610 -- identifiers for the record
611 id integer primary key autoincrement,
612 did text not null,
613 rkey text,
614
615 -- content
616 subject_did text not null,
617 repo_at text not null,
618
619 -- meta
620 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
621
622 -- constraints
623 foreign key (repo_at) references repos(at_uri) on delete cascade
624 )
625 `)
626 if err != nil {
627 return err
628 }
629
630 // copy data
631 _, err = tx.Exec(`
632 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
633 select
634 c.id,
635 r.did,
636 '',
637 c.did,
638 r.at_uri
639 from collaborators c
640 join repos r on c.repo = r.id
641 `)
642 if err != nil {
643 return err
644 }
645
646 // drop old table
647 _, err = tx.Exec(`drop table collaborators`)
648 if err != nil {
649 return err
650 }
651
652 // rename new table
653 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
654 return err
655 })
656
657 return &DB{db}, nil
658}
659
660type migrationFn = func(*sql.Tx) error
661
662func runMigration(d *sql.DB, name string, migrationFn migrationFn) error {
663 tx, err := d.Begin()
664 if err != nil {
665 return err
666 }
667 defer tx.Rollback()
668
669 var exists bool
670 err = tx.QueryRow("select exists (select 1 from migrations where name = ?)", name).Scan(&exists)
671 if err != nil {
672 return err
673 }
674
675 if !exists {
676 // run migration
677 err = migrationFn(tx)
678 if err != nil {
679 log.Printf("Failed to run migration %s: %v", name, err)
680 return err
681 }
682
683 // mark migration as complete
684 _, err = tx.Exec("insert into migrations (name) values (?)", name)
685 if err != nil {
686 log.Printf("Failed to mark migration %s as complete: %v", name, err)
687 return err
688 }
689
690 // commit the transaction
691 if err := tx.Commit(); err != nil {
692 return err
693 }
694
695 log.Printf("migration %s applied successfully", name)
696 } else {
697 log.Printf("skipped migration %s, already applied", name)
698 }
699
700 return nil
701}
702
703type filter struct {
704 key string
705 arg any
706 cmp string
707}
708
709func newFilter(key, cmp string, arg any) filter {
710 return filter{
711 key: key,
712 arg: arg,
713 cmp: cmp,
714 }
715}
716
717func FilterEq(key string, arg any) filter { return newFilter(key, "=", arg) }
718func FilterNotEq(key string, arg any) filter { return newFilter(key, "<>", arg) }
719func FilterGte(key string, arg any) filter { return newFilter(key, ">=", arg) }
720func FilterLte(key string, arg any) filter { return newFilter(key, "<=", arg) }
721func FilterIs(key string, arg any) filter { return newFilter(key, "is", arg) }
722func FilterIsNot(key string, arg any) filter { return newFilter(key, "is not", arg) }
723func FilterIn(key string, arg any) filter { return newFilter(key, "in", arg) }
724
725func (f filter) Condition() string {
726 rv := reflect.ValueOf(f.arg)
727 kind := rv.Kind()
728
729 // if we have `FilterIn(k, [1, 2, 3])`, compile it down to `k in (?, ?, ?)`
730 if (kind == reflect.Slice && rv.Type().Elem().Kind() != reflect.Uint8) || kind == reflect.Array {
731 if rv.Len() == 0 {
732 // always false
733 return "1 = 0"
734 }
735
736 placeholders := make([]string, rv.Len())
737 for i := range placeholders {
738 placeholders[i] = "?"
739 }
740
741 return fmt.Sprintf("%s %s (%s)", f.key, f.cmp, strings.Join(placeholders, ", "))
742 }
743
744 return fmt.Sprintf("%s %s ?", f.key, f.cmp)
745}
746
747func (f filter) Arg() []any {
748 rv := reflect.ValueOf(f.arg)
749 kind := rv.Kind()
750 if (kind == reflect.Slice && rv.Type().Elem().Kind() != reflect.Uint8) || kind == reflect.Array {
751 if rv.Len() == 0 {
752 return nil
753 }
754
755 out := make([]any, rv.Len())
756 for i := range rv.Len() {
757 out[i] = rv.Index(i).Interface()
758 }
759 return out
760 }
761
762 return []any{f.arg}
763}