nel.pet
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 // https://github.com/mattn/go-sqlite3#connection-string
31 opts := []string{
32 "_foreign_keys=1",
33 "_journal_mode=WAL",
34 "_synchronous=NORMAL",
35 "_auto_vacuum=incremental",
36 }
37
38 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
39 if err != nil {
40 return nil, err
41 }
42
43 ctx := context.Background()
44
45 conn, err := db.Conn(ctx)
46 if err != nil {
47 return nil, err
48 }
49 defer conn.Close()
50
51 _, err = conn.ExecContext(ctx, `
52 create table if not exists registrations (
53 id integer primary key autoincrement,
54 domain text not null unique,
55 did text not null,
56 secret text not null,
57 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
58 registered text
59 );
60 create table if not exists public_keys (
61 id integer primary key autoincrement,
62 did text not null,
63 name text not null,
64 key text not null,
65 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
66 unique(did, name, key)
67 );
68 create table if not exists repos (
69 id integer primary key autoincrement,
70 did text not null,
71 name text not null,
72 knot text not null,
73 rkey text not null,
74 at_uri text not null unique,
75 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
76 unique(did, name, knot, rkey)
77 );
78 create table if not exists collaborators (
79 id integer primary key autoincrement,
80 did text not null,
81 repo integer not null,
82 foreign key (repo) references repos(id) on delete cascade
83 );
84 create table if not exists follows (
85 user_did text not null,
86 subject_did text not null,
87 rkey text not null,
88 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
89 primary key (user_did, subject_did),
90 check (user_did <> subject_did)
91 );
92 create table if not exists issues (
93 id integer primary key autoincrement,
94 owner_did text not null,
95 repo_at text not null,
96 issue_id integer not null,
97 title text not null,
98 body text not null,
99 open integer not null default 1,
100 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
101 issue_at text,
102 unique(repo_at, issue_id),
103 foreign key (repo_at) references repos(at_uri) on delete cascade
104 );
105 create table if not exists comments (
106 id integer primary key autoincrement,
107 owner_did text not null,
108 issue_id integer not null,
109 repo_at text not null,
110 comment_id integer not null,
111 comment_at text not null,
112 body text not null,
113 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
114 unique(issue_id, comment_id),
115 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
116 );
117 create table if not exists pulls (
118 -- identifiers
119 id integer primary key autoincrement,
120 pull_id integer not null,
121
122 -- at identifiers
123 repo_at text not null,
124 owner_did text not null,
125 rkey text not null,
126 pull_at text,
127
128 -- content
129 title text not null,
130 body text not null,
131 target_branch text not null,
132 state integer not null default 0 check (state in (0, 1, 2)), -- open, merged, closed
133
134 -- meta
135 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
136
137 -- constraints
138 unique(repo_at, pull_id),
139 foreign key (repo_at) references repos(at_uri) on delete cascade
140 );
141
142 -- every pull must have atleast 1 submission: the initial submission
143 create table if not exists pull_submissions (
144 -- identifiers
145 id integer primary key autoincrement,
146 pull_id integer not null,
147
148 -- at identifiers
149 repo_at text not null,
150
151 -- content, these are immutable, and require a resubmission to update
152 round_number integer not null default 0,
153 patch text,
154
155 -- meta
156 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
157
158 -- constraints
159 unique(repo_at, pull_id, round_number),
160 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade
161 );
162
163 create table if not exists pull_comments (
164 -- identifiers
165 id integer primary key autoincrement,
166 pull_id integer not null,
167 submission_id integer not null,
168
169 -- at identifiers
170 repo_at text not null,
171 owner_did text not null,
172 comment_at text not null,
173
174 -- content
175 body text not null,
176
177 -- meta
178 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
179
180 -- constraints
181 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade,
182 foreign key (submission_id) references pull_submissions(id) on delete cascade
183 );
184
185 create table if not exists _jetstream (
186 id integer primary key autoincrement,
187 last_time_us integer not null
188 );
189
190 create table if not exists repo_issue_seqs (
191 repo_at text primary key,
192 next_issue_id integer not null default 1
193 );
194
195 create table if not exists repo_pull_seqs (
196 repo_at text primary key,
197 next_pull_id integer not null default 1
198 );
199
200 create table if not exists stars (
201 id integer primary key autoincrement,
202 starred_by_did text not null,
203 repo_at text not null,
204 rkey text not null,
205 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
206 foreign key (repo_at) references repos(at_uri) on delete cascade,
207 unique(starred_by_did, repo_at)
208 );
209
210 create table if not exists reactions (
211 id integer primary key autoincrement,
212 reacted_by_did text not null,
213 thread_at text not null,
214 kind text not null,
215 rkey text not null,
216 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
217 unique(reacted_by_did, thread_at, kind)
218 );
219
220 create table if not exists emails (
221 id integer primary key autoincrement,
222 did text not null,
223 email text not null,
224 verified integer not null default 0,
225 verification_code text not null,
226 last_sent text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
227 is_primary integer not null default 0,
228 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
229 unique(did, email)
230 );
231
232 create table if not exists artifacts (
233 -- id
234 id integer primary key autoincrement,
235 did text not null,
236 rkey text not null,
237
238 -- meta
239 repo_at text not null,
240 tag binary(20) not null,
241 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
242
243 -- data
244 blob_cid text not null,
245 name text not null,
246 size integer not null default 0,
247 mimetype string not null default "*/*",
248
249 -- constraints
250 unique(did, rkey), -- record must be unique
251 unique(repo_at, tag, name), -- for a given tag object, each file must be unique
252 foreign key (repo_at) references repos(at_uri) on delete cascade
253 );
254
255 create table if not exists profile (
256 -- id
257 id integer primary key autoincrement,
258 did text not null,
259
260 -- data
261 description text not null,
262 include_bluesky integer not null default 0,
263 location text,
264
265 -- constraints
266 unique(did)
267 );
268 create table if not exists profile_links (
269 -- id
270 id integer primary key autoincrement,
271 did text not null,
272
273 -- data
274 link text not null,
275
276 -- constraints
277 foreign key (did) references profile(did) on delete cascade
278 );
279 create table if not exists profile_stats (
280 -- id
281 id integer primary key autoincrement,
282 did text not null,
283
284 -- data
285 kind text not null check (kind in (
286 "merged-pull-request-count",
287 "closed-pull-request-count",
288 "open-pull-request-count",
289 "open-issue-count",
290 "closed-issue-count",
291 "repository-count"
292 )),
293
294 -- constraints
295 foreign key (did) references profile(did) on delete cascade
296 );
297 create table if not exists profile_pinned_repositories (
298 -- id
299 id integer primary key autoincrement,
300 did text not null,
301
302 -- data
303 at_uri text not null,
304
305 -- constraints
306 unique(did, at_uri),
307 foreign key (did) references profile(did) on delete cascade,
308 foreign key (at_uri) references repos(at_uri) on delete cascade
309 );
310
311 create table if not exists oauth_requests (
312 id integer primary key autoincrement,
313 auth_server_iss text not null,
314 state text not null,
315 did text not null,
316 handle text not null,
317 pds_url text not null,
318 pkce_verifier text not null,
319 dpop_auth_server_nonce text not null,
320 dpop_private_jwk text not null
321 );
322
323 create table if not exists oauth_sessions (
324 id integer primary key autoincrement,
325 did text not null,
326 handle text not null,
327 pds_url text not null,
328 auth_server_iss text not null,
329 access_jwt text not null,
330 refresh_jwt text not null,
331 dpop_pds_nonce text,
332 dpop_auth_server_nonce text not null,
333 dpop_private_jwk text not null,
334 expiry text not null
335 );
336
337 create table if not exists punchcard (
338 did text not null,
339 date text not null, -- yyyy-mm-dd
340 count integer,
341 primary key (did, date)
342 );
343
344 create table if not exists spindles (
345 id integer primary key autoincrement,
346 owner text not null,
347 instance text not null,
348 verified text, -- time of verification
349 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
350
351 unique(owner, instance)
352 );
353
354 create table if not exists spindle_members (
355 -- identifiers for the record
356 id integer primary key autoincrement,
357 did text not null,
358 rkey text not null,
359
360 -- data
361 instance text not null,
362 subject text not null,
363 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
364
365 -- constraints
366 unique (did, instance, subject)
367 );
368
369 create table if not exists pipelines (
370 -- identifiers
371 id integer primary key autoincrement,
372 knot text not null,
373 rkey text not null,
374
375 repo_owner text not null,
376 repo_name text not null,
377
378 -- every pipeline must be associated with exactly one commit
379 sha text not null check (length(sha) = 40),
380 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
381
382 -- trigger data
383 trigger_id integer not null,
384
385 unique(knot, rkey),
386 foreign key (trigger_id) references triggers(id) on delete cascade
387 );
388
389 create table if not exists triggers (
390 -- primary key
391 id integer primary key autoincrement,
392
393 -- top-level fields
394 kind text not null,
395
396 -- pushTriggerData fields
397 push_ref text,
398 push_new_sha text check (length(push_new_sha) = 40),
399 push_old_sha text check (length(push_old_sha) = 40),
400
401 -- pullRequestTriggerData fields
402 pr_source_branch text,
403 pr_target_branch text,
404 pr_source_sha text check (length(pr_source_sha) = 40),
405 pr_action text
406 );
407
408 create table if not exists pipeline_statuses (
409 -- identifiers
410 id integer primary key autoincrement,
411 spindle text not null,
412 rkey text not null,
413
414 -- referenced pipeline. these form the (did, rkey) pair
415 pipeline_knot text not null,
416 pipeline_rkey text not null,
417
418 -- content
419 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
420 workflow text not null,
421 status text not null,
422 error text,
423 exit_code integer not null default 0,
424
425 unique (spindle, rkey),
426 foreign key (pipeline_knot, pipeline_rkey)
427 references pipelines (knot, rkey)
428 on delete cascade
429 );
430
431 create table if not exists repo_languages (
432 -- identifiers
433 id integer primary key autoincrement,
434
435 -- repo identifiers
436 repo_at text not null,
437 ref text not null,
438 is_default_ref integer not null default 0,
439
440 -- language breakdown
441 language text not null,
442 bytes integer not null check (bytes >= 0),
443
444 unique(repo_at, ref, language)
445 );
446
447 create table if not exists signups_inflight (
448 id integer primary key autoincrement,
449 email text not null unique,
450 invite_code text not null,
451 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
452 );
453
454 create table if not exists strings (
455 -- identifiers
456 did text not null,
457 rkey text not null,
458
459 -- content
460 filename text not null,
461 description text,
462 content text not null,
463 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
464 edited text,
465
466 primary key (did, rkey)
467 );
468
469 create table if not exists label_definitions (
470 -- identifiers
471 id integer primary key autoincrement,
472 did text not null,
473 rkey text not null,
474 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.definition' || '/' || rkey) stored,
475
476 -- content
477 name text not null,
478 value_type text not null check (value_type in (
479 "null",
480 "boolean",
481 "integer",
482 "string"
483 )),
484 value_format text not null default "any",
485 value_enum text, -- comma separated list
486 scope text not null, -- comma separated list of nsid
487 color text,
488 multiple integer not null default 0,
489 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
490
491 -- constraints
492 unique (did, rkey)
493 unique (at_uri)
494 );
495
496 -- ops are flattened, a record may contain several additions and deletions, but the table will include one row per add/del
497 create table if not exists label_ops (
498 -- identifiers
499 id integer primary key autoincrement,
500 did text not null,
501 rkey text not null,
502 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.op' || '/' || rkey) stored,
503
504 -- content
505 subject text not null,
506 operation text not null check (operation in ("add", "del")),
507 operand_key text not null,
508 operand_value text not null,
509 -- we need two time values: performed is declared by the user, indexed is calculated by the av
510 performed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
511 indexed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
512
513 -- constraints
514 -- traditionally (did, rkey) pair should be unique, but not in this case
515 -- operand_key should reference a label definition
516 foreign key (operand_key) references label_definitions (at_uri) on delete cascade,
517 unique (did, rkey, subject, operand_key, operand_value)
518 );
519
520 create table if not exists repo_labels (
521 -- identifiers
522 id integer primary key autoincrement,
523
524 -- repo identifiers
525 repo_at text not null,
526
527 -- label to subscribe to
528 label_at text not null,
529
530 unique (repo_at, label_at)
531 );
532
533 create table if not exists notifications (
534 id integer primary key autoincrement,
535 recipient_did text not null,
536 actor_did text not null,
537 type text not null,
538 entity_type text not null,
539 entity_id text not null,
540 read integer not null default 0,
541 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
542 repo_id integer references repos(id),
543 issue_id integer references issues(id),
544 pull_id integer references pulls(id)
545 );
546
547 create table if not exists notification_preferences (
548 id integer primary key autoincrement,
549 user_did text not null unique,
550 repo_starred integer not null default 1,
551 issue_created integer not null default 1,
552 issue_commented integer not null default 1,
553 pull_created integer not null default 1,
554 pull_commented integer not null default 1,
555 followed integer not null default 1,
556 pull_merged integer not null default 1,
557 issue_closed integer not null default 1,
558 email_notifications integer not null default 0
559 );
560
561 create table if not exists migrations (
562 id integer primary key autoincrement,
563 name text unique
564 );
565
566 -- indexes for better performance
567 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
568 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
569 create index if not exists idx_stars_created on stars(created);
570 create index if not exists idx_stars_repo_at_created on stars(repo_at, created);
571 `)
572 if err != nil {
573 return nil, err
574 }
575
576 // run migrations
577 runMigration(conn, "add-description-to-repos", func(tx *sql.Tx) error {
578 tx.Exec(`
579 alter table repos add column description text check (length(description) <= 200);
580 `)
581 return nil
582 })
583
584 runMigration(conn, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
585 // add unconstrained column
586 _, err := tx.Exec(`
587 alter table public_keys
588 add column rkey text;
589 `)
590 if err != nil {
591 return err
592 }
593
594 // backfill
595 _, err = tx.Exec(`
596 update public_keys
597 set rkey = ''
598 where rkey is null;
599 `)
600 if err != nil {
601 return err
602 }
603
604 return nil
605 })
606
607 runMigration(conn, "add-rkey-to-comments", func(tx *sql.Tx) error {
608 _, err := tx.Exec(`
609 alter table comments drop column comment_at;
610 alter table comments add column rkey text;
611 `)
612 return err
613 })
614
615 runMigration(conn, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
616 _, err := tx.Exec(`
617 alter table comments add column deleted text; -- timestamp
618 alter table comments add column edited text; -- timestamp
619 `)
620 return err
621 })
622
623 runMigration(conn, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
624 _, err := tx.Exec(`
625 alter table pulls add column source_branch text;
626 alter table pulls add column source_repo_at text;
627 alter table pull_submissions add column source_rev text;
628 `)
629 return err
630 })
631
632 runMigration(conn, "add-source-to-repos", func(tx *sql.Tx) error {
633 _, err := tx.Exec(`
634 alter table repos add column source text;
635 `)
636 return err
637 })
638
639 // disable foreign-keys for the next migration
640 // NOTE: this cannot be done in a transaction, so it is run outside [0]
641 //
642 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
643 conn.ExecContext(ctx, "pragma foreign_keys = off;")
644 runMigration(conn, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
645 _, err := tx.Exec(`
646 create table pulls_new (
647 -- identifiers
648 id integer primary key autoincrement,
649 pull_id integer not null,
650
651 -- at identifiers
652 repo_at text not null,
653 owner_did text not null,
654 rkey text not null,
655
656 -- content
657 title text not null,
658 body text not null,
659 target_branch text not null,
660 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
661
662 -- source info
663 source_branch text,
664 source_repo_at text,
665
666 -- stacking
667 stack_id text,
668 change_id text,
669 parent_change_id text,
670
671 -- meta
672 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
673
674 -- constraints
675 unique(repo_at, pull_id),
676 foreign key (repo_at) references repos(at_uri) on delete cascade
677 );
678
679 insert into pulls_new (
680 id, pull_id,
681 repo_at, owner_did, rkey,
682 title, body, target_branch, state,
683 source_branch, source_repo_at,
684 created
685 )
686 select
687 id, pull_id,
688 repo_at, owner_did, rkey,
689 title, body, target_branch, state,
690 source_branch, source_repo_at,
691 created
692 FROM pulls;
693
694 drop table pulls;
695 alter table pulls_new rename to pulls;
696 `)
697 return err
698 })
699 conn.ExecContext(ctx, "pragma foreign_keys = on;")
700
701 runMigration(conn, "add-spindle-to-repos", func(tx *sql.Tx) error {
702 tx.Exec(`
703 alter table repos add column spindle text;
704 `)
705 return nil
706 })
707
708 // drop all knot secrets, add unique constraint to knots
709 //
710 // knots will henceforth use service auth for signed requests
711 runMigration(conn, "no-more-secrets", func(tx *sql.Tx) error {
712 _, err := tx.Exec(`
713 create table registrations_new (
714 id integer primary key autoincrement,
715 domain text not null,
716 did text not null,
717 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
718 registered text,
719 read_only integer not null default 0,
720 unique(domain, did)
721 );
722
723 insert into registrations_new (id, domain, did, created, registered, read_only)
724 select id, domain, did, created, registered, 1 from registrations
725 where registered is not null;
726
727 drop table registrations;
728 alter table registrations_new rename to registrations;
729 `)
730 return err
731 })
732
733 // recreate and add rkey + created columns with default constraint
734 runMigration(conn, "rework-collaborators-table", func(tx *sql.Tx) error {
735 // create new table
736 // - repo_at instead of repo integer
737 // - rkey field
738 // - created field
739 _, err := tx.Exec(`
740 create table collaborators_new (
741 -- identifiers for the record
742 id integer primary key autoincrement,
743 did text not null,
744 rkey text,
745
746 -- content
747 subject_did text not null,
748 repo_at text not null,
749
750 -- meta
751 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
752
753 -- constraints
754 foreign key (repo_at) references repos(at_uri) on delete cascade
755 )
756 `)
757 if err != nil {
758 return err
759 }
760
761 // copy data
762 _, err = tx.Exec(`
763 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
764 select
765 c.id,
766 r.did,
767 '',
768 c.did,
769 r.at_uri
770 from collaborators c
771 join repos r on c.repo = r.id
772 `)
773 if err != nil {
774 return err
775 }
776
777 // drop old table
778 _, err = tx.Exec(`drop table collaborators`)
779 if err != nil {
780 return err
781 }
782
783 // rename new table
784 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
785 return err
786 })
787
788 runMigration(conn, "add-rkey-to-issues", func(tx *sql.Tx) error {
789 _, err := tx.Exec(`
790 alter table issues add column rkey text not null default '';
791
792 -- get last url section from issue_at and save to rkey column
793 update issues
794 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
795 `)
796 return err
797 })
798
799 // repurpose the read-only column to "needs-upgrade"
800 runMigration(conn, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
801 _, err := tx.Exec(`
802 alter table registrations rename column read_only to needs_upgrade;
803 `)
804 return err
805 })
806
807 // require all knots to upgrade after the release of total xrpc
808 runMigration(conn, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
809 _, err := tx.Exec(`
810 update registrations set needs_upgrade = 1;
811 `)
812 return err
813 })
814
815 // require all knots to upgrade after the release of total xrpc
816 runMigration(conn, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
817 _, err := tx.Exec(`
818 alter table spindles add column needs_upgrade integer not null default 0;
819 `)
820 return err
821 })
822
823 // remove issue_at from issues and replace with generated column
824 //
825 // this requires a full table recreation because stored columns
826 // cannot be added via alter
827 //
828 // couple other changes:
829 // - columns renamed to be more consistent
830 // - adds edited and deleted fields
831 //
832 // disable foreign-keys for the next migration
833 conn.ExecContext(ctx, "pragma foreign_keys = off;")
834 runMigration(conn, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
835 _, err := tx.Exec(`
836 create table if not exists issues_new (
837 -- identifiers
838 id integer primary key autoincrement,
839 did text not null,
840 rkey text not null,
841 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
842
843 -- at identifiers
844 repo_at text not null,
845
846 -- content
847 issue_id integer not null,
848 title text not null,
849 body text not null,
850 open integer not null default 1,
851 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
852 edited text, -- timestamp
853 deleted text, -- timestamp
854
855 unique(did, rkey),
856 unique(repo_at, issue_id),
857 unique(at_uri),
858 foreign key (repo_at) references repos(at_uri) on delete cascade
859 );
860 `)
861 if err != nil {
862 return err
863 }
864
865 // transfer data
866 _, err = tx.Exec(`
867 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
868 select
869 i.id,
870 i.owner_did,
871 i.rkey,
872 i.repo_at,
873 i.issue_id,
874 i.title,
875 i.body,
876 i.open,
877 i.created
878 from issues i;
879 `)
880 if err != nil {
881 return err
882 }
883
884 // drop old table
885 _, err = tx.Exec(`drop table issues`)
886 if err != nil {
887 return err
888 }
889
890 // rename new table
891 _, err = tx.Exec(`alter table issues_new rename to issues`)
892 return err
893 })
894 conn.ExecContext(ctx, "pragma foreign_keys = on;")
895
896 // - renames the comments table to 'issue_comments'
897 // - rework issue comments to update constraints:
898 // * unique(did, rkey)
899 // * remove comment-id and just use the global ID
900 // * foreign key (repo_at, issue_id)
901 // - new columns
902 // * column "reply_to" which can be any other comment
903 // * column "at-uri" which is a generated column
904 runMigration(conn, "rework-issue-comments", func(tx *sql.Tx) error {
905 _, err := tx.Exec(`
906 create table if not exists issue_comments (
907 -- identifiers
908 id integer primary key autoincrement,
909 did text not null,
910 rkey text,
911 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
912
913 -- at identifiers
914 issue_at text not null,
915 reply_to text, -- at_uri of parent comment
916
917 -- content
918 body text not null,
919 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
920 edited text,
921 deleted text,
922
923 -- constraints
924 unique(did, rkey),
925 unique(at_uri),
926 foreign key (issue_at) references issues(at_uri) on delete cascade
927 );
928 `)
929 if err != nil {
930 return err
931 }
932
933 // transfer data
934 _, err = tx.Exec(`
935 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
936 select
937 c.id,
938 c.owner_did,
939 c.rkey,
940 i.at_uri, -- get at_uri from issues table
941 c.body,
942 c.created,
943 c.edited,
944 c.deleted
945 from comments c
946 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
947 `)
948 if err != nil {
949 return err
950 }
951
952 // drop old table
953 _, err = tx.Exec(`drop table comments`)
954 return err
955 })
956
957 // add generated at_uri column to pulls table
958 //
959 // this requires a full table recreation because stored columns
960 // cannot be added via alter
961 //
962 // disable foreign-keys for the next migration
963 conn.ExecContext(ctx, "pragma foreign_keys = off;")
964 runMigration(conn, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
965 _, err := tx.Exec(`
966 create table if not exists pulls_new (
967 -- identifiers
968 id integer primary key autoincrement,
969 pull_id integer not null,
970 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
971
972 -- at identifiers
973 repo_at text not null,
974 owner_did text not null,
975 rkey text not null,
976
977 -- content
978 title text not null,
979 body text not null,
980 target_branch text not null,
981 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
982
983 -- source info
984 source_branch text,
985 source_repo_at text,
986
987 -- stacking
988 stack_id text,
989 change_id text,
990 parent_change_id text,
991
992 -- meta
993 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
994
995 -- constraints
996 unique(repo_at, pull_id),
997 unique(at_uri),
998 foreign key (repo_at) references repos(at_uri) on delete cascade
999 );
1000 `)
1001 if err != nil {
1002 return err
1003 }
1004
1005 // transfer data
1006 _, err = tx.Exec(`
1007 insert into pulls_new (
1008 id, pull_id, repo_at, owner_did, rkey,
1009 title, body, target_branch, state,
1010 source_branch, source_repo_at,
1011 stack_id, change_id, parent_change_id,
1012 created
1013 )
1014 select
1015 id, pull_id, repo_at, owner_did, rkey,
1016 title, body, target_branch, state,
1017 source_branch, source_repo_at,
1018 stack_id, change_id, parent_change_id,
1019 created
1020 from pulls;
1021 `)
1022 if err != nil {
1023 return err
1024 }
1025
1026 // drop old table
1027 _, err = tx.Exec(`drop table pulls`)
1028 if err != nil {
1029 return err
1030 }
1031
1032 // rename new table
1033 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1034 return err
1035 })
1036 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1037
1038 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1039 //
1040 // this requires a full table recreation because stored columns
1041 // cannot be added via alter
1042 //
1043 // disable foreign-keys for the next migration
1044 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1045 runMigration(conn, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1046 _, err := tx.Exec(`
1047 create table if not exists pull_submissions_new (
1048 -- identifiers
1049 id integer primary key autoincrement,
1050 pull_at text not null,
1051
1052 -- content, these are immutable, and require a resubmission to update
1053 round_number integer not null default 0,
1054 patch text,
1055 source_rev text,
1056
1057 -- meta
1058 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1059
1060 -- constraints
1061 unique(pull_at, round_number),
1062 foreign key (pull_at) references pulls(at_uri) on delete cascade
1063 );
1064 `)
1065 if err != nil {
1066 return err
1067 }
1068
1069 // transfer data, constructing pull_at from pulls table
1070 _, err = tx.Exec(`
1071 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1072 select
1073 ps.id,
1074 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1075 ps.round_number,
1076 ps.patch,
1077 ps.created
1078 from pull_submissions ps
1079 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1080 `)
1081 if err != nil {
1082 return err
1083 }
1084
1085 // drop old table
1086 _, err = tx.Exec(`drop table pull_submissions`)
1087 if err != nil {
1088 return err
1089 }
1090
1091 // rename new table
1092 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1093 return err
1094 })
1095 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1096
1097 return &DB{db}, nil
1098}
1099
1100type migrationFn = func(*sql.Tx) error
1101
1102func runMigration(c *sql.Conn, name string, migrationFn migrationFn) error {
1103 tx, err := c.BeginTx(context.Background(), nil)
1104 if err != nil {
1105 return err
1106 }
1107 defer tx.Rollback()
1108
1109 var exists bool
1110 err = tx.QueryRow("select exists (select 1 from migrations where name = ?)", name).Scan(&exists)
1111 if err != nil {
1112 return err
1113 }
1114
1115 if !exists {
1116 // run migration
1117 err = migrationFn(tx)
1118 if err != nil {
1119 log.Printf("Failed to run migration %s: %v", name, err)
1120 return err
1121 }
1122
1123 // mark migration as complete
1124 _, err = tx.Exec("insert into migrations (name) values (?)", name)
1125 if err != nil {
1126 log.Printf("Failed to mark migration %s as complete: %v", name, err)
1127 return err
1128 }
1129
1130 // commit the transaction
1131 if err := tx.Commit(); err != nil {
1132 return err
1133 }
1134
1135 log.Printf("migration %s applied successfully", name)
1136 } else {
1137 log.Printf("skipped migration %s, already applied", name)
1138 }
1139
1140 return nil
1141}
1142
1143func (d *DB) Close() error {
1144 return d.DB.Close()
1145}
1146
1147type filter struct {
1148 key string
1149 arg any
1150 cmp string
1151}
1152
1153func newFilter(key, cmp string, arg any) filter {
1154 return filter{
1155 key: key,
1156 arg: arg,
1157 cmp: cmp,
1158 }
1159}
1160
1161func FilterEq(key string, arg any) filter { return newFilter(key, "=", arg) }
1162func FilterNotEq(key string, arg any) filter { return newFilter(key, "<>", arg) }
1163func FilterGte(key string, arg any) filter { return newFilter(key, ">=", arg) }
1164func FilterLte(key string, arg any) filter { return newFilter(key, "<=", arg) }
1165func FilterIs(key string, arg any) filter { return newFilter(key, "is", arg) }
1166func FilterIsNot(key string, arg any) filter { return newFilter(key, "is not", arg) }
1167func FilterIn(key string, arg any) filter { return newFilter(key, "in", arg) }
1168func FilterLike(key string, arg any) filter { return newFilter(key, "like", arg) }
1169func FilterNotLike(key string, arg any) filter { return newFilter(key, "not like", arg) }
1170func FilterContains(key string, arg any) filter {
1171 return newFilter(key, "like", fmt.Sprintf("%%%v%%", arg))
1172}
1173
1174func (f filter) Condition() string {
1175 rv := reflect.ValueOf(f.arg)
1176 kind := rv.Kind()
1177
1178 // if we have `FilterIn(k, [1, 2, 3])`, compile it down to `k in (?, ?, ?)`
1179 if (kind == reflect.Slice && rv.Type().Elem().Kind() != reflect.Uint8) || kind == reflect.Array {
1180 if rv.Len() == 0 {
1181 // always false
1182 return "1 = 0"
1183 }
1184
1185 placeholders := make([]string, rv.Len())
1186 for i := range placeholders {
1187 placeholders[i] = "?"
1188 }
1189
1190 return fmt.Sprintf("%s %s (%s)", f.key, f.cmp, strings.Join(placeholders, ", "))
1191 }
1192
1193 return fmt.Sprintf("%s %s ?", f.key, f.cmp)
1194}
1195
1196func (f filter) Arg() []any {
1197 rv := reflect.ValueOf(f.arg)
1198 kind := rv.Kind()
1199 if (kind == reflect.Slice && rv.Type().Elem().Kind() != reflect.Uint8) || kind == reflect.Array {
1200 if rv.Len() == 0 {
1201 return nil
1202 }
1203
1204 out := make([]any, rv.Len())
1205 for i := range rv.Len() {
1206 out[i] = rv.Index(i).Interface()
1207 }
1208 return out
1209 }
1210
1211 return []any{f.arg}
1212}