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