Monorepo for Tangled
tangled.org
1package db
2
3import (
4 "context"
5 "database/sql"
6 "log/slog"
7 "strings"
8
9 _ "github.com/mattn/go-sqlite3"
10 "tangled.org/core/log"
11 "tangled.org/core/orm"
12)
13
14type DB struct {
15 *sql.DB
16 logger *slog.Logger
17}
18
19type Execer interface {
20 Query(query string, args ...any) (*sql.Rows, error)
21 QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
22 QueryRow(query string, args ...any) *sql.Row
23 QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
24 Exec(query string, args ...any) (sql.Result, error)
25 ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
26 Prepare(query string) (*sql.Stmt, error)
27 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
28}
29
30func Make(ctx context.Context, dbPath string) (*DB, error) {
31 // https://github.com/mattn/go-sqlite3#connection-string
32 opts := []string{
33 "_foreign_keys=1",
34 "_journal_mode=WAL",
35 "_synchronous=NORMAL",
36 "_auto_vacuum=incremental",
37 "_busy_timeout=5000",
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 vouches (
96 did text not null,
97 subject_did text not null,
98 cid text not null,
99 kind text not null default 'vouch',
100 reason text,
101 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
102 primary key (did, subject_did),
103 check (did <> subject_did),
104 check (kind in ('vouch', 'denounce'))
105 );
106 create table if not exists issues (
107 id integer primary key autoincrement,
108 owner_did text not null,
109 repo_at text not null,
110 issue_id integer not null,
111 title text not null,
112 body text not null,
113 open integer not null default 1,
114 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
115 issue_at text,
116 unique(repo_at, issue_id),
117 foreign key (repo_at) references repos(at_uri) on delete cascade
118 );
119 create table if not exists comments (
120 id integer primary key autoincrement,
121 owner_did text not null,
122 issue_id integer not null,
123 repo_at text not null,
124 comment_id integer not null,
125 comment_at text not null,
126 body text not null,
127 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
128 unique(issue_id, comment_id),
129 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
130 );
131 create table if not exists pulls (
132 -- identifiers
133 id integer primary key autoincrement,
134 pull_id integer not null,
135
136 -- at identifiers
137 repo_at text not null,
138 owner_did text not null,
139 rkey text not null,
140 pull_at text,
141
142 -- content
143 title text not null,
144 body text not null,
145 target_branch text not null,
146 state integer not null default 0 check (state in (0, 1, 2)), -- open, merged, closed
147
148 -- meta
149 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
150
151 -- constraints
152 unique(repo_at, pull_id),
153 foreign key (repo_at) references repos(at_uri) on delete cascade
154 );
155
156 -- every pull must have atleast 1 submission: the initial submission
157 create table if not exists pull_submissions (
158 -- identifiers
159 id integer primary key autoincrement,
160 pull_id integer not null,
161
162 -- at identifiers
163 repo_at text not null,
164
165 -- content, these are immutable, and require a resubmission to update
166 round_number integer not null default 0,
167 patch text,
168
169 -- meta
170 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
171
172 -- constraints
173 unique(repo_at, pull_id, round_number),
174 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade
175 );
176
177 create table if not exists pull_comments (
178 -- identifiers
179 id integer primary key autoincrement,
180 pull_id integer not null,
181 submission_id integer not null,
182
183 -- at identifiers
184 repo_at text not null,
185 owner_did text not null,
186 comment_at text not null,
187
188 -- content
189 body text not null,
190
191 -- meta
192 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
193
194 -- constraints
195 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade,
196 foreign key (submission_id) references pull_submissions(id) on delete cascade
197 );
198
199 create table if not exists _jetstream (
200 id integer primary key autoincrement,
201 last_time_us integer not null
202 );
203
204 create table if not exists repo_issue_seqs (
205 repo_at text primary key,
206 next_issue_id integer not null default 1
207 );
208
209 create table if not exists repo_pull_seqs (
210 repo_at text primary key,
211 next_pull_id integer not null default 1
212 );
213
214 create table if not exists stars (
215 id integer primary key autoincrement,
216 starred_by_did text not null,
217 repo_at text not null,
218 rkey text not null,
219 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
220 foreign key (repo_at) references repos(at_uri) on delete cascade,
221 unique(starred_by_did, repo_at)
222 );
223
224 create table if not exists reactions (
225 id integer primary key autoincrement,
226 reacted_by_did text not null,
227 thread_at text not null,
228 kind text not null,
229 rkey text not null,
230 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
231 unique(reacted_by_did, thread_at, kind)
232 );
233
234 create table if not exists emails (
235 id integer primary key autoincrement,
236 did text not null,
237 email text not null,
238 verified integer not null default 0,
239 verification_code text not null,
240 last_sent text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
241 is_primary integer not null default 0,
242 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
243 unique(did, email)
244 );
245
246 create table if not exists artifacts (
247 -- id
248 id integer primary key autoincrement,
249 did text not null,
250 rkey text not null,
251
252 -- meta
253 repo_at text not null,
254 tag binary(20) not null,
255 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
256
257 -- data
258 blob_cid text not null,
259 name text not null,
260 size integer not null default 0,
261 mimetype string not null default "*/*",
262
263 -- constraints
264 unique(did, rkey), -- record must be unique
265 unique(repo_at, tag, name), -- for a given tag object, each file must be unique
266 foreign key (repo_at) references repos(at_uri) on delete cascade
267 );
268
269 create table if not exists profile (
270 -- id
271 id integer primary key autoincrement,
272 did text not null,
273
274 -- data
275 description text not null,
276 include_bluesky integer not null default 0,
277 location text,
278
279 -- constraints
280 unique(did)
281 );
282 create table if not exists profile_links (
283 -- id
284 id integer primary key autoincrement,
285 did text not null,
286
287 -- data
288 link text not null,
289
290 -- constraints
291 foreign key (did) references profile(did) on delete cascade
292 );
293 create table if not exists profile_stats (
294 -- id
295 id integer primary key autoincrement,
296 did text not null,
297
298 -- data
299 kind text not null check (kind in (
300 "merged-pull-request-count",
301 "closed-pull-request-count",
302 "open-pull-request-count",
303 "open-issue-count",
304 "closed-issue-count",
305 "repository-count"
306 )),
307
308 -- constraints
309 foreign key (did) references profile(did) on delete cascade
310 );
311 create table if not exists profile_pinned_repositories (
312 -- id
313 id integer primary key autoincrement,
314 did text not null,
315
316 -- data
317 at_uri text not null,
318
319 -- constraints
320 unique(did, at_uri),
321 foreign key (did) references profile(did) on delete cascade,
322 foreign key (at_uri) references repos(at_uri) on delete cascade
323 );
324
325 create table if not exists oauth_requests (
326 id integer primary key autoincrement,
327 auth_server_iss text not null,
328 state text not null,
329 did text not null,
330 handle text not null,
331 pds_url text not null,
332 pkce_verifier text not null,
333 dpop_auth_server_nonce text not null,
334 dpop_private_jwk text not null
335 );
336
337 create table if not exists oauth_sessions (
338 id integer primary key autoincrement,
339 did text not null,
340 handle text not null,
341 pds_url text not null,
342 auth_server_iss text not null,
343 access_jwt text not null,
344 refresh_jwt text not null,
345 dpop_pds_nonce text,
346 dpop_auth_server_nonce text not null,
347 dpop_private_jwk text not null,
348 expiry text not null
349 );
350
351 create table if not exists punchcard (
352 did text not null,
353 date text not null, -- yyyy-mm-dd
354 count integer,
355 primary key (did, date)
356 );
357
358 create table if not exists spindles (
359 id integer primary key autoincrement,
360 owner text not null,
361 instance text not null,
362 verified text, -- time of verification
363 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
364
365 unique(owner, instance)
366 );
367
368 create table if not exists spindle_members (
369 -- identifiers for the record
370 id integer primary key autoincrement,
371 did text not null,
372 rkey text not null,
373
374 -- data
375 instance text not null,
376 subject text not null,
377 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
378
379 -- constraints
380 unique (did, instance, subject)
381 );
382
383 create table if not exists pipelines (
384 -- identifiers
385 id integer primary key autoincrement,
386 knot text not null,
387 rkey text not null,
388
389 repo_owner text not null,
390 repo_name text not null,
391
392 -- every pipeline must be associated with exactly one commit
393 sha text not null check (length(sha) = 40),
394 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
395
396 -- trigger data
397 trigger_id integer not null,
398
399 unique(knot, rkey),
400 foreign key (trigger_id) references triggers(id) on delete cascade
401 );
402
403 create table if not exists triggers (
404 -- primary key
405 id integer primary key autoincrement,
406
407 -- top-level fields
408 kind text not null,
409
410 -- pushTriggerData fields
411 push_ref text,
412 push_new_sha text check (length(push_new_sha) = 40),
413 push_old_sha text check (length(push_old_sha) = 40),
414
415 -- pullRequestTriggerData fields
416 pr_source_branch text,
417 pr_target_branch text,
418 pr_source_sha text check (length(pr_source_sha) = 40),
419 pr_action text
420 );
421
422 create table if not exists pipeline_statuses (
423 -- identifiers
424 id integer primary key autoincrement,
425 spindle text not null,
426 rkey text not null,
427
428 -- referenced pipeline. these form the (did, rkey) pair
429 pipeline_knot text not null,
430 pipeline_rkey text not null,
431
432 -- content
433 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
434 workflow text not null,
435 status text not null,
436 error text,
437 exit_code integer not null default 0,
438
439 unique (spindle, rkey),
440 foreign key (pipeline_knot, pipeline_rkey)
441 references pipelines (knot, rkey)
442 on delete cascade
443 );
444
445 create table if not exists repo_languages (
446 -- identifiers
447 id integer primary key autoincrement,
448
449 -- repo identifiers
450 repo_at text not null,
451 ref text not null,
452 is_default_ref integer not null default 0,
453
454 -- language breakdown
455 language text not null,
456 bytes integer not null check (bytes >= 0),
457
458 unique(repo_at, ref, language)
459 );
460
461 create table if not exists signups_inflight (
462 id integer primary key autoincrement,
463 email text not null unique,
464 invite_code text not null,
465 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
466 );
467
468 create table if not exists strings (
469 -- identifiers
470 did text not null,
471 rkey text not null,
472
473 -- content
474 filename text not null,
475 description text,
476 content text not null,
477 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
478 edited text,
479
480 primary key (did, rkey)
481 );
482
483 create table if not exists label_definitions (
484 -- identifiers
485 id integer primary key autoincrement,
486 did text not null,
487 rkey text not null,
488 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.definition' || '/' || rkey) stored,
489
490 -- content
491 name text not null,
492 value_type text not null check (value_type in (
493 "null",
494 "boolean",
495 "integer",
496 "string"
497 )),
498 value_format text not null default "any",
499 value_enum text, -- comma separated list
500 scope text not null, -- comma separated list of nsid
501 color text,
502 multiple integer not null default 0,
503 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
504
505 -- constraints
506 unique (did, rkey)
507 unique (at_uri)
508 );
509
510 -- ops are flattened, a record may contain several additions and deletions, but the table will include one row per add/del
511 create table if not exists label_ops (
512 -- identifiers
513 id integer primary key autoincrement,
514 did text not null,
515 rkey text not null,
516 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.op' || '/' || rkey) stored,
517
518 -- content
519 subject text not null,
520 operation text not null check (operation in ("add", "del")),
521 operand_key text not null,
522 operand_value text not null,
523 -- we need two time values: performed is declared by the user, indexed is calculated by the av
524 performed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
525 indexed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
526
527 -- constraints
528 -- traditionally (did, rkey) pair should be unique, but not in this case
529 -- operand_key should reference a label definition
530 foreign key (operand_key) references label_definitions (at_uri) on delete cascade,
531 unique (did, rkey, subject, operand_key, operand_value)
532 );
533
534 create table if not exists repo_labels (
535 -- identifiers
536 id integer primary key autoincrement,
537
538 -- repo identifiers
539 repo_at text not null,
540
541 -- label to subscribe to
542 label_at text not null,
543
544 unique (repo_at, label_at)
545 );
546
547 create table if not exists notifications (
548 id integer primary key autoincrement,
549 recipient_did text not null,
550 actor_did text not null,
551 type text not null,
552 entity_type text not null,
553 entity_id text not null,
554 read integer not null default 0,
555 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
556 repo_id integer references repos(id),
557 issue_id integer references issues(id),
558 pull_id integer references pulls(id)
559 );
560
561 create table if not exists notification_preferences (
562 id integer primary key autoincrement,
563 user_did text not null unique,
564 repo_starred integer not null default 1,
565 issue_created integer not null default 1,
566 issue_commented integer not null default 1,
567 pull_created integer not null default 1,
568 pull_commented integer not null default 1,
569 followed integer not null default 1,
570 pull_merged integer not null default 1,
571 issue_closed integer not null default 1,
572 email_notifications integer not null default 0
573 );
574
575 create table if not exists reference_links (
576 id integer primary key autoincrement,
577 from_at text not null,
578 to_at text not null,
579 unique (from_at, to_at)
580 );
581
582 create table if not exists webhooks (
583 id integer primary key autoincrement,
584 repo_at text not null,
585 url text not null,
586 secret text,
587 active integer not null default 1,
588 events text not null, -- comma-separated list of events
589 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
590 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
591
592 foreign key (repo_at) references repos(at_uri) on delete cascade
593 );
594
595 create table if not exists webhook_deliveries (
596 id integer primary key autoincrement,
597 webhook_id integer not null,
598 event text not null,
599 delivery_id text not null,
600 url text not null,
601 request_body text not null,
602 response_code integer,
603 response_body text,
604 success integer not null default 0,
605 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
606
607 foreign key (webhook_id) references webhooks(id) on delete cascade
608 );
609
610 create table if not exists bluesky_posts (
611 rkey text primary key,
612 text text not null,
613 created_at text not null,
614 langs text,
615 facets text,
616 embed text,
617 like_count integer not null default 0,
618 reply_count integer not null default 0,
619 repost_count integer not null default 0,
620 quote_count integer not null default 0
621 );
622
623 create table if not exists domain_claims (
624 id integer primary key autoincrement,
625 did text not null unique,
626 domain text not null unique,
627 deleted text -- timestamp when the domain was released/unclaimed; null means actively claimed
628 );
629
630 create table if not exists repo_sites (
631 id integer primary key autoincrement,
632 repo_at text not null unique,
633 branch text not null,
634 dir text not null default '/',
635 is_index integer not null default 0,
636 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
637 updated text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
638 foreign key (repo_at) references repos(at_uri) on delete cascade
639 );
640
641 create table if not exists site_deploys (
642 id integer primary key autoincrement,
643 repo_at text not null,
644 branch text not null,
645 dir text not null default '/',
646 commit_sha text not null default '',
647 status text not null check (status in ('success', 'failure')),
648 trigger text not null check (trigger in ('config_change', 'push')),
649 error text not null default '',
650 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
651 foreign key (repo_at) references repos(at_uri) on delete cascade
652 );
653
654 create table if not exists punchcard_preferences (
655 id integer primary key autoincrement,
656 user_did text not null unique,
657 hide_mine integer default 0,
658 hide_others integer default 0
659 );
660
661 create table if not exists newsletter_preferences (
662 id integer primary key autoincrement,
663 user_did text not null unique,
664 status text not null check (status in ('subscribed', 'dismissed')),
665 email text,
666 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
667 );
668
669 create table if not exists vouch_evidences (
670 id integer primary key autoincrement,
671 vouch_id integer not null,
672 at_uri text not null,
673 unique(vouch_id, at_uri),
674 foreign key (vouch_id) references vouches(id) on delete cascade
675 );
676
677 create table if not exists vouch_skips (
678 did text not null,
679 subject_did text not null,
680 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
681 primary key (did, subject_did),
682 check (did <> subject_did)
683 );
684
685
686 create table if not exists migrations (
687 id integer primary key autoincrement,
688 name text unique
689 );
690
691 -- indexes for better performance
692 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
693 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
694 create index if not exists idx_references_from_at on reference_links(from_at);
695 create index if not exists idx_references_to_at on reference_links(to_at);
696 create index if not exists idx_webhooks_repo_at on webhooks(repo_at);
697 create index if not exists idx_webhook_deliveries_webhook_id on webhook_deliveries(webhook_id);
698 create index if not exists idx_site_deploys_repo_at on site_deploys(repo_at);
699 create index if not exists idx_newsletter_prefs_user_did on newsletter_preferences(user_did);
700 `)
701 if err != nil {
702 return nil, err
703 }
704
705 // run migrations
706 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
707 tx.Exec(`
708 alter table repos add column description text check (length(description) <= 200);
709 `)
710 return nil
711 })
712
713 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
714 // add unconstrained column
715 _, err := tx.Exec(`
716 alter table public_keys
717 add column rkey text;
718 `)
719 if err != nil {
720 return err
721 }
722
723 // backfill
724 _, err = tx.Exec(`
725 update public_keys
726 set rkey = ''
727 where rkey is null;
728 `)
729 if err != nil {
730 return err
731 }
732
733 return nil
734 })
735
736 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
737 _, err := tx.Exec(`
738 alter table comments drop column comment_at;
739 alter table comments add column rkey text;
740 `)
741 return err
742 })
743
744 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
745 _, err := tx.Exec(`
746 alter table comments add column deleted text; -- timestamp
747 alter table comments add column edited text; -- timestamp
748 `)
749 return err
750 })
751
752 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
753 _, err := tx.Exec(`
754 alter table pulls add column source_branch text;
755 alter table pulls add column source_repo_at text;
756 alter table pull_submissions add column source_rev text;
757 `)
758 return err
759 })
760
761 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
762 _, err := tx.Exec(`
763 alter table repos add column source text;
764 `)
765 return err
766 })
767
768 // disable foreign-keys for the next migration
769 // NOTE: this cannot be done in a transaction, so it is run outside [0]
770 //
771 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
772 conn.ExecContext(ctx, "pragma foreign_keys = off;")
773 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
774 _, err := tx.Exec(`
775 create table pulls_new (
776 -- identifiers
777 id integer primary key autoincrement,
778 pull_id integer not null,
779
780 -- at identifiers
781 repo_at text not null,
782 owner_did text not null,
783 rkey text not null,
784
785 -- content
786 title text not null,
787 body text not null,
788 target_branch text not null,
789 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
790
791 -- source info
792 source_branch text,
793 source_repo_at text,
794
795 -- stacking
796 stack_id text,
797 change_id text,
798 parent_change_id text,
799
800 -- meta
801 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
802
803 -- constraints
804 unique(repo_at, pull_id),
805 foreign key (repo_at) references repos(at_uri) on delete cascade
806 );
807
808 insert into pulls_new (
809 id, pull_id,
810 repo_at, owner_did, rkey,
811 title, body, target_branch, state,
812 source_branch, source_repo_at,
813 created
814 )
815 select
816 id, pull_id,
817 repo_at, owner_did, rkey,
818 title, body, target_branch, state,
819 source_branch, source_repo_at,
820 created
821 FROM pulls;
822
823 drop table pulls;
824 alter table pulls_new rename to pulls;
825 `)
826 return err
827 })
828 conn.ExecContext(ctx, "pragma foreign_keys = on;")
829
830 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
831 tx.Exec(`
832 alter table repos add column spindle text;
833 `)
834 return nil
835 })
836
837 // drop all knot secrets, add unique constraint to knots
838 //
839 // knots will henceforth use service auth for signed requests
840 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
841 _, err := tx.Exec(`
842 create table registrations_new (
843 id integer primary key autoincrement,
844 domain text not null,
845 did text not null,
846 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
847 registered text,
848 read_only integer not null default 0,
849 unique(domain, did)
850 );
851
852 insert into registrations_new (id, domain, did, created, registered, read_only)
853 select id, domain, did, created, registered, 1 from registrations
854 where registered is not null;
855
856 drop table registrations;
857 alter table registrations_new rename to registrations;
858 `)
859 return err
860 })
861
862 // recreate and add rkey + created columns with default constraint
863 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
864 // create new table
865 // - repo_at instead of repo integer
866 // - rkey field
867 // - created field
868 _, err := tx.Exec(`
869 create table collaborators_new (
870 -- identifiers for the record
871 id integer primary key autoincrement,
872 did text not null,
873 rkey text,
874
875 -- content
876 subject_did text not null,
877 repo_at text not null,
878
879 -- meta
880 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
881
882 -- constraints
883 foreign key (repo_at) references repos(at_uri) on delete cascade
884 )
885 `)
886 if err != nil {
887 return err
888 }
889
890 // copy data
891 _, err = tx.Exec(`
892 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
893 select
894 c.id,
895 r.did,
896 '',
897 c.did,
898 r.at_uri
899 from collaborators c
900 join repos r on c.repo = r.id
901 `)
902 if err != nil {
903 return err
904 }
905
906 // drop old table
907 _, err = tx.Exec(`drop table collaborators`)
908 if err != nil {
909 return err
910 }
911
912 // rename new table
913 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
914 return err
915 })
916
917 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
918 _, err := tx.Exec(`
919 alter table issues add column rkey text not null default '';
920
921 -- get last url section from issue_at and save to rkey column
922 update issues
923 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
924 `)
925 return err
926 })
927
928 // repurpose the read-only column to "needs-upgrade"
929 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
930 _, err := tx.Exec(`
931 alter table registrations rename column read_only to needs_upgrade;
932 `)
933 return err
934 })
935
936 // require all knots to upgrade after the release of total xrpc
937 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
938 _, err := tx.Exec(`
939 update registrations set needs_upgrade = 1;
940 `)
941 return err
942 })
943
944 // require all knots to upgrade after the release of total xrpc
945 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
946 _, err := tx.Exec(`
947 alter table spindles add column needs_upgrade integer not null default 0;
948 `)
949 return err
950 })
951
952 // remove issue_at from issues and replace with generated column
953 //
954 // this requires a full table recreation because stored columns
955 // cannot be added via alter
956 //
957 // couple other changes:
958 // - columns renamed to be more consistent
959 // - adds edited and deleted fields
960 //
961 // disable foreign-keys for the next migration
962 conn.ExecContext(ctx, "pragma foreign_keys = off;")
963 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
964 _, err := tx.Exec(`
965 create table if not exists issues_new (
966 -- identifiers
967 id integer primary key autoincrement,
968 did text not null,
969 rkey text not null,
970 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
971
972 -- at identifiers
973 repo_at text not null,
974
975 -- content
976 issue_id integer not null,
977 title text not null,
978 body text not null,
979 open integer not null default 1,
980 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
981 edited text, -- timestamp
982 deleted text, -- timestamp
983
984 unique(did, rkey),
985 unique(repo_at, issue_id),
986 unique(at_uri),
987 foreign key (repo_at) references repos(at_uri) on delete cascade
988 );
989 `)
990 if err != nil {
991 return err
992 }
993
994 // transfer data
995 _, err = tx.Exec(`
996 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
997 select
998 i.id,
999 i.owner_did,
1000 i.rkey,
1001 i.repo_at,
1002 i.issue_id,
1003 i.title,
1004 i.body,
1005 i.open,
1006 i.created
1007 from issues i;
1008 `)
1009 if err != nil {
1010 return err
1011 }
1012
1013 // drop old table
1014 _, err = tx.Exec(`drop table issues`)
1015 if err != nil {
1016 return err
1017 }
1018
1019 // rename new table
1020 _, err = tx.Exec(`alter table issues_new rename to issues`)
1021 return err
1022 })
1023 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1024
1025 // - renames the comments table to 'issue_comments'
1026 // - rework issue comments to update constraints:
1027 // * unique(did, rkey)
1028 // * remove comment-id and just use the global ID
1029 // * foreign key (repo_at, issue_id)
1030 // - new columns
1031 // * column "reply_to" which can be any other comment
1032 // * column "at-uri" which is a generated column
1033 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
1034 _, err := tx.Exec(`
1035 create table if not exists issue_comments (
1036 -- identifiers
1037 id integer primary key autoincrement,
1038 did text not null,
1039 rkey text,
1040 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
1041
1042 -- at identifiers
1043 issue_at text not null,
1044 reply_to text, -- at_uri of parent comment
1045
1046 -- content
1047 body text not null,
1048 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1049 edited text,
1050 deleted text,
1051
1052 -- constraints
1053 unique(did, rkey),
1054 unique(at_uri),
1055 foreign key (issue_at) references issues(at_uri) on delete cascade
1056 );
1057 `)
1058 if err != nil {
1059 return err
1060 }
1061
1062 // transfer data
1063 _, err = tx.Exec(`
1064 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
1065 select
1066 c.id,
1067 c.owner_did,
1068 c.rkey,
1069 i.at_uri, -- get at_uri from issues table
1070 c.body,
1071 c.created,
1072 c.edited,
1073 c.deleted
1074 from comments c
1075 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
1076 `)
1077 if err != nil {
1078 return err
1079 }
1080
1081 // drop old table
1082 _, err = tx.Exec(`drop table comments`)
1083 return err
1084 })
1085
1086 // add generated at_uri column to pulls table
1087 //
1088 // this requires a full table recreation because stored columns
1089 // cannot be added via alter
1090 //
1091 // disable foreign-keys for the next migration
1092 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1093 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
1094 _, err := tx.Exec(`
1095 create table if not exists pulls_new (
1096 -- identifiers
1097 id integer primary key autoincrement,
1098 pull_id integer not null,
1099 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
1100
1101 -- at identifiers
1102 repo_at text not null,
1103 owner_did text not null,
1104 rkey text not null,
1105
1106 -- content
1107 title text not null,
1108 body text not null,
1109 target_branch text not null,
1110 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
1111
1112 -- source info
1113 source_branch text,
1114 source_repo_at text,
1115
1116 -- stacking
1117 stack_id text,
1118 change_id text,
1119 parent_change_id text,
1120
1121 -- meta
1122 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1123
1124 -- constraints
1125 unique(repo_at, pull_id),
1126 unique(at_uri),
1127 foreign key (repo_at) references repos(at_uri) on delete cascade
1128 );
1129 `)
1130 if err != nil {
1131 return err
1132 }
1133
1134 // transfer data
1135 _, err = tx.Exec(`
1136 insert into pulls_new (
1137 id, pull_id, repo_at, owner_did, rkey,
1138 title, body, target_branch, state,
1139 source_branch, source_repo_at,
1140 stack_id, change_id, parent_change_id,
1141 created
1142 )
1143 select
1144 id, pull_id, repo_at, owner_did, rkey,
1145 title, body, target_branch, state,
1146 source_branch, source_repo_at,
1147 stack_id, change_id, parent_change_id,
1148 created
1149 from pulls;
1150 `)
1151 if err != nil {
1152 return err
1153 }
1154
1155 // drop old table
1156 _, err = tx.Exec(`drop table pulls`)
1157 if err != nil {
1158 return err
1159 }
1160
1161 // rename new table
1162 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1163 return err
1164 })
1165 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1166
1167 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1168 //
1169 // this requires a full table recreation because stored columns
1170 // cannot be added via alter
1171 //
1172 // disable foreign-keys for the next migration
1173 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1174 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1175 _, err := tx.Exec(`
1176 create table if not exists pull_submissions_new (
1177 -- identifiers
1178 id integer primary key autoincrement,
1179 pull_at text not null,
1180
1181 -- content, these are immutable, and require a resubmission to update
1182 round_number integer not null default 0,
1183 patch text,
1184 source_rev text,
1185
1186 -- meta
1187 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1188
1189 -- constraints
1190 unique(pull_at, round_number),
1191 foreign key (pull_at) references pulls(at_uri) on delete cascade
1192 );
1193 `)
1194 if err != nil {
1195 return err
1196 }
1197
1198 // transfer data, constructing pull_at from pulls table
1199 _, err = tx.Exec(`
1200 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1201 select
1202 ps.id,
1203 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1204 ps.round_number,
1205 ps.patch,
1206 ps.created
1207 from pull_submissions ps
1208 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1209 `)
1210 if err != nil {
1211 return err
1212 }
1213
1214 // drop old table
1215 _, err = tx.Exec(`drop table pull_submissions`)
1216 if err != nil {
1217 return err
1218 }
1219
1220 // rename new table
1221 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1222 return err
1223 })
1224 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1225
1226 // knots may report the combined patch for a comparison, we can store that on the appview side
1227 // (but not on the pds record), because calculating the combined patch requires a git index
1228 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1229 _, err := tx.Exec(`
1230 alter table pull_submissions add column combined text;
1231 `)
1232 return err
1233 })
1234
1235 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1236 _, err := tx.Exec(`
1237 alter table profile add column pronouns text;
1238 `)
1239 return err
1240 })
1241
1242 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1243 _, err := tx.Exec(`
1244 alter table repos add column website text;
1245 alter table repos add column topics text;
1246 `)
1247 return err
1248 })
1249
1250 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1251 _, err := tx.Exec(`
1252 alter table notification_preferences add column user_mentioned integer not null default 1;
1253 `)
1254 return err
1255 })
1256
1257 // remove the foreign key constraints from stars.
1258 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1259 _, err := tx.Exec(`
1260 create table stars_new (
1261 id integer primary key autoincrement,
1262 did text not null,
1263 rkey text not null,
1264
1265 subject_at text not null,
1266
1267 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1268 unique(did, rkey),
1269 unique(did, subject_at)
1270 );
1271
1272 insert into stars_new (
1273 id,
1274 did,
1275 rkey,
1276 subject_at,
1277 created
1278 )
1279 select
1280 id,
1281 starred_by_did,
1282 rkey,
1283 repo_at,
1284 created
1285 from stars;
1286
1287 drop table stars;
1288 alter table stars_new rename to stars;
1289
1290 create index if not exists idx_stars_created on stars(created);
1291 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1292 `)
1293 return err
1294 })
1295
1296 orm.RunMigration(conn, logger, "add-avatar-to-profile", func(tx *sql.Tx) error {
1297 _, err := tx.Exec(`
1298 alter table profile add column avatar text;
1299 `)
1300 return err
1301 })
1302
1303 orm.RunMigration(conn, logger, "remove-profile-stats-column-constraint", func(tx *sql.Tx) error {
1304 _, err := tx.Exec(`
1305 -- create new table without the check constraint
1306 create table profile_stats_new (
1307 id integer primary key autoincrement,
1308 did text not null,
1309 kind text not null, -- no constraint this time
1310 foreign key (did) references profile(did) on delete cascade
1311 );
1312
1313 -- copy data from old table
1314 insert into profile_stats_new (id, did, kind)
1315 select id, did, kind
1316 from profile_stats;
1317
1318 -- drop old table
1319 drop table profile_stats;
1320
1321 -- rename new table
1322 alter table profile_stats_new rename to profile_stats;
1323 `)
1324 return err
1325 })
1326
1327 orm.RunMigration(conn, logger, "add-preferred-handle-profile", func(tx *sql.Tx) error {
1328 _, err := tx.Exec(`
1329 alter table profile add column preferred_handle text;
1330 `)
1331 return err
1332 })
1333
1334 orm.RunMigration(conn, logger, "add-repo-did-column", func(tx *sql.Tx) error {
1335 _, err := tx.Exec(`
1336 alter table repos add column repo_did text;
1337 create unique index if not exists idx_repos_repo_did on repos(repo_did);
1338 `)
1339 return err
1340 })
1341
1342 orm.RunMigration(conn, logger, "add-pds-rewrite-status", func(tx *sql.Tx) error {
1343 _, err := tx.Exec(`
1344 create table if not exists pds_rewrite_status (
1345 id integer primary key autoincrement,
1346 user_did text not null,
1347 repo_did text not null,
1348 record_nsid text not null,
1349 record_rkey text not null,
1350 old_repo_at text not null,
1351 status text not null default 'pending',
1352 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1353 unique(user_did, record_nsid, record_rkey)
1354 );
1355 create index if not exists idx_pds_rewrite_user on pds_rewrite_status(user_did, status);
1356 `)
1357 return err
1358 })
1359
1360 orm.RunMigration(conn, logger, "add-pipelines-repo-did", func(tx *sql.Tx) error {
1361 _, err := tx.Exec(`
1362 alter table pipelines add column repo_did text;
1363 create index if not exists idx_pipelines_repo_did on pipelines(repo_did);
1364 `)
1365 return err
1366 })
1367
1368 orm.RunMigration(conn, logger, "migrate-knots-to-repo-dids", func(tx *sql.Tx) error {
1369 _, err := tx.Exec(`update registrations set needs_upgrade = 1`)
1370 return err
1371 })
1372
1373 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1374 orm.RunMigration(conn, logger, "drop-pinned-repos-at-uri-fk", func(tx *sql.Tx) error {
1375 _, err := tx.Exec(`
1376 create table if not exists profile_pinned_repositories_new (
1377 id integer primary key autoincrement,
1378 did text not null,
1379 pin text not null,
1380
1381 unique(did, pin),
1382 foreign key (did) references profile(did) on delete cascade
1383 );
1384
1385 insert into profile_pinned_repositories_new (id, did, pin)
1386 select id, did, at_uri from profile_pinned_repositories;
1387
1388 drop table profile_pinned_repositories;
1389
1390 alter table profile_pinned_repositories_new rename to profile_pinned_repositories;
1391 `)
1392 return err
1393 })
1394 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1395
1396 orm.RunMigration(conn, logger, "reset-profile-pin-rewrites", func(tx *sql.Tx) error {
1397 _, err := tx.Exec(`
1398 update pds_rewrite_status
1399 set status = 'pending',
1400 updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1401 where record_nsid = 'sh.tangled.actor.profile'
1402 and status = 'done'
1403 `)
1404 return err
1405 })
1406
1407 orm.RunMigration(conn, logger, "add-blob-data-to-pull-submissions", func(tx *sql.Tx) error {
1408 _, err := tx.Exec(`
1409 alter table pull_submissions add column patch_blob_ref text;
1410 alter table pull_submissions add column patch_blob_mime text;
1411 alter table pull_submissions add column patch_blob_size integer;
1412 `)
1413 return err
1414 })
1415
1416 orm.RunMigration(conn, logger, "replace-parent-change-id-with-aturi", func(tx *sql.Tx) error {
1417 // add new column
1418 _, err := tx.Exec(`
1419 alter table pulls add column dependent_on text;
1420 `)
1421 if err != nil {
1422 return err
1423 }
1424
1425 // populate dependent_on with at_uri of the parent
1426 _, err = tx.Exec(`
1427 update pulls
1428 set dependent_on = (
1429 select at_uri
1430 from pulls as parent
1431 where parent.stack_id = pulls.stack_id
1432 and parent.change_id = pulls.parent_change_id
1433 )
1434 where parent_change_id is not null;
1435 `)
1436 if err != nil {
1437 return err
1438 }
1439
1440 // drop old columns
1441 _, err = tx.Exec(`
1442 alter table pulls drop column parent_change_id;
1443 alter table pulls drop column stack_id;
1444 `)
1445
1446 return err
1447 })
1448
1449 orm.RunMigration(conn, logger, "add-pds-migration", func(tx *sql.Tx) error {
1450 _, err := tx.Exec(`
1451 create table if not exists pds_migration (
1452 name text not null,
1453
1454 -- record at_uri
1455 did text not null,
1456 collection text not null,
1457 rkey text not null,
1458
1459 status text not null default 'pending',
1460 error_msg text,
1461 retry_count integer not null default 0,
1462 retry_after integer not null default 0,
1463 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1464
1465 unique(name, did, collection, rkey)
1466 );
1467 `)
1468 return err
1469 })
1470
1471 orm.RunMigration(conn, logger, "unify-pds-record-migration-table", func(tx *sql.Tx) error {
1472 _, err := tx.Exec(`
1473 insert into pds_migration (
1474 name,
1475 did,
1476 collection,
1477 rkey,
1478 status,
1479 updated_at
1480 )
1481 select
1482 'add-repo-did',
1483 user_did,
1484 record_nsid,
1485 record_rkey,
1486 status,
1487 updated_at
1488 from pds_rewrite_status;
1489
1490 drop table pds_rewrite_status;
1491 `)
1492 return err
1493 })
1494
1495 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1496 orm.RunMigration(conn, logger, "add-id-to-vouches", func(tx *sql.Tx) error {
1497 _, err := tx.Exec(`
1498 create table vouches_new (
1499 id integer primary key autoincrement,
1500 did text not null,
1501 subject_did text not null,
1502 cid text not null,
1503 kind text not null default 'vouch',
1504 reason text,
1505 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1506 unique(did, subject_did),
1507 check (did <> subject_did),
1508 check (kind in ('vouch', 'denounce'))
1509 );
1510
1511 insert into vouches_new (did, subject_did, cid, kind, reason, created_at)
1512 select did, subject_did, cid, kind, reason, created_at
1513 from vouches;
1514
1515 drop table vouches;
1516 alter table vouches_new rename to vouches;
1517 `)
1518 return err
1519 })
1520 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1521
1522 return &DB{
1523 db,
1524 logger,
1525 }, nil
1526}
1527
1528func (d *DB) Close() error {
1529 return d.DB.Close()
1530}