forked from
tangled.org/core
Monorepo for Tangled
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 migrations (
655 id integer primary key autoincrement,
656 name text unique
657 );
658
659 create table if not exists punchcard_preferences (
660 id integer primary key autoincrement,
661 user_did text not null unique,
662 hide_mine integer default 0,
663 hide_others integer default 0
664 );
665
666 create table if not exists newsletter_preferences (
667 id integer primary key autoincrement,
668 user_did text not null unique,
669 status text not null check (status in ('subscribed', 'dismissed')),
670 email text,
671 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
672 );
673
674 -- indexes for better performance
675 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
676 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
677 create index if not exists idx_references_from_at on reference_links(from_at);
678 create index if not exists idx_references_to_at on reference_links(to_at);
679 create index if not exists idx_webhooks_repo_at on webhooks(repo_at);
680 create index if not exists idx_webhook_deliveries_webhook_id on webhook_deliveries(webhook_id);
681 create index if not exists idx_site_deploys_repo_at on site_deploys(repo_at);
682 create index if not exists idx_newsletter_prefs_user_did on newsletter_preferences(user_did);
683 `)
684 if err != nil {
685 return nil, err
686 }
687
688 // run migrations
689 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
690 tx.Exec(`
691 alter table repos add column description text check (length(description) <= 200);
692 `)
693 return nil
694 })
695
696 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
697 // add unconstrained column
698 _, err := tx.Exec(`
699 alter table public_keys
700 add column rkey text;
701 `)
702 if err != nil {
703 return err
704 }
705
706 // backfill
707 _, err = tx.Exec(`
708 update public_keys
709 set rkey = ''
710 where rkey is null;
711 `)
712 if err != nil {
713 return err
714 }
715
716 return nil
717 })
718
719 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
720 _, err := tx.Exec(`
721 alter table comments drop column comment_at;
722 alter table comments add column rkey text;
723 `)
724 return err
725 })
726
727 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
728 _, err := tx.Exec(`
729 alter table comments add column deleted text; -- timestamp
730 alter table comments add column edited text; -- timestamp
731 `)
732 return err
733 })
734
735 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
736 _, err := tx.Exec(`
737 alter table pulls add column source_branch text;
738 alter table pulls add column source_repo_at text;
739 alter table pull_submissions add column source_rev text;
740 `)
741 return err
742 })
743
744 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
745 _, err := tx.Exec(`
746 alter table repos add column source text;
747 `)
748 return err
749 })
750
751 // disable foreign-keys for the next migration
752 // NOTE: this cannot be done in a transaction, so it is run outside [0]
753 //
754 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
755 conn.ExecContext(ctx, "pragma foreign_keys = off;")
756 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
757 _, err := tx.Exec(`
758 create table pulls_new (
759 -- identifiers
760 id integer primary key autoincrement,
761 pull_id integer not null,
762
763 -- at identifiers
764 repo_at text not null,
765 owner_did text not null,
766 rkey text not null,
767
768 -- content
769 title text not null,
770 body text not null,
771 target_branch text not null,
772 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
773
774 -- source info
775 source_branch text,
776 source_repo_at text,
777
778 -- stacking
779 stack_id text,
780 change_id text,
781 parent_change_id text,
782
783 -- meta
784 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
785
786 -- constraints
787 unique(repo_at, pull_id),
788 foreign key (repo_at) references repos(at_uri) on delete cascade
789 );
790
791 insert into pulls_new (
792 id, pull_id,
793 repo_at, owner_did, rkey,
794 title, body, target_branch, state,
795 source_branch, source_repo_at,
796 created
797 )
798 select
799 id, pull_id,
800 repo_at, owner_did, rkey,
801 title, body, target_branch, state,
802 source_branch, source_repo_at,
803 created
804 FROM pulls;
805
806 drop table pulls;
807 alter table pulls_new rename to pulls;
808 `)
809 return err
810 })
811 conn.ExecContext(ctx, "pragma foreign_keys = on;")
812
813 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
814 tx.Exec(`
815 alter table repos add column spindle text;
816 `)
817 return nil
818 })
819
820 // drop all knot secrets, add unique constraint to knots
821 //
822 // knots will henceforth use service auth for signed requests
823 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
824 _, err := tx.Exec(`
825 create table registrations_new (
826 id integer primary key autoincrement,
827 domain text not null,
828 did text not null,
829 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
830 registered text,
831 read_only integer not null default 0,
832 unique(domain, did)
833 );
834
835 insert into registrations_new (id, domain, did, created, registered, read_only)
836 select id, domain, did, created, registered, 1 from registrations
837 where registered is not null;
838
839 drop table registrations;
840 alter table registrations_new rename to registrations;
841 `)
842 return err
843 })
844
845 // recreate and add rkey + created columns with default constraint
846 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
847 // create new table
848 // - repo_at instead of repo integer
849 // - rkey field
850 // - created field
851 _, err := tx.Exec(`
852 create table collaborators_new (
853 -- identifiers for the record
854 id integer primary key autoincrement,
855 did text not null,
856 rkey text,
857
858 -- content
859 subject_did text not null,
860 repo_at text not null,
861
862 -- meta
863 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
864
865 -- constraints
866 foreign key (repo_at) references repos(at_uri) on delete cascade
867 )
868 `)
869 if err != nil {
870 return err
871 }
872
873 // copy data
874 _, err = tx.Exec(`
875 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
876 select
877 c.id,
878 r.did,
879 '',
880 c.did,
881 r.at_uri
882 from collaborators c
883 join repos r on c.repo = r.id
884 `)
885 if err != nil {
886 return err
887 }
888
889 // drop old table
890 _, err = tx.Exec(`drop table collaborators`)
891 if err != nil {
892 return err
893 }
894
895 // rename new table
896 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
897 return err
898 })
899
900 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
901 _, err := tx.Exec(`
902 alter table issues add column rkey text not null default '';
903
904 -- get last url section from issue_at and save to rkey column
905 update issues
906 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
907 `)
908 return err
909 })
910
911 // repurpose the read-only column to "needs-upgrade"
912 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
913 _, err := tx.Exec(`
914 alter table registrations rename column read_only to needs_upgrade;
915 `)
916 return err
917 })
918
919 // require all knots to upgrade after the release of total xrpc
920 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
921 _, err := tx.Exec(`
922 update registrations set needs_upgrade = 1;
923 `)
924 return err
925 })
926
927 // require all knots to upgrade after the release of total xrpc
928 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
929 _, err := tx.Exec(`
930 alter table spindles add column needs_upgrade integer not null default 0;
931 `)
932 return err
933 })
934
935 // remove issue_at from issues and replace with generated column
936 //
937 // this requires a full table recreation because stored columns
938 // cannot be added via alter
939 //
940 // couple other changes:
941 // - columns renamed to be more consistent
942 // - adds edited and deleted fields
943 //
944 // disable foreign-keys for the next migration
945 conn.ExecContext(ctx, "pragma foreign_keys = off;")
946 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
947 _, err := tx.Exec(`
948 create table if not exists issues_new (
949 -- identifiers
950 id integer primary key autoincrement,
951 did text not null,
952 rkey text not null,
953 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
954
955 -- at identifiers
956 repo_at text not null,
957
958 -- content
959 issue_id integer not null,
960 title text not null,
961 body text not null,
962 open integer not null default 1,
963 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
964 edited text, -- timestamp
965 deleted text, -- timestamp
966
967 unique(did, rkey),
968 unique(repo_at, issue_id),
969 unique(at_uri),
970 foreign key (repo_at) references repos(at_uri) on delete cascade
971 );
972 `)
973 if err != nil {
974 return err
975 }
976
977 // transfer data
978 _, err = tx.Exec(`
979 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
980 select
981 i.id,
982 i.owner_did,
983 i.rkey,
984 i.repo_at,
985 i.issue_id,
986 i.title,
987 i.body,
988 i.open,
989 i.created
990 from issues i;
991 `)
992 if err != nil {
993 return err
994 }
995
996 // drop old table
997 _, err = tx.Exec(`drop table issues`)
998 if err != nil {
999 return err
1000 }
1001
1002 // rename new table
1003 _, err = tx.Exec(`alter table issues_new rename to issues`)
1004 return err
1005 })
1006 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1007
1008 // - renames the comments table to 'issue_comments'
1009 // - rework issue comments to update constraints:
1010 // * unique(did, rkey)
1011 // * remove comment-id and just use the global ID
1012 // * foreign key (repo_at, issue_id)
1013 // - new columns
1014 // * column "reply_to" which can be any other comment
1015 // * column "at-uri" which is a generated column
1016 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
1017 _, err := tx.Exec(`
1018 create table if not exists issue_comments (
1019 -- identifiers
1020 id integer primary key autoincrement,
1021 did text not null,
1022 rkey text,
1023 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
1024
1025 -- at identifiers
1026 issue_at text not null,
1027 reply_to text, -- at_uri of parent comment
1028
1029 -- content
1030 body text not null,
1031 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1032 edited text,
1033 deleted text,
1034
1035 -- constraints
1036 unique(did, rkey),
1037 unique(at_uri),
1038 foreign key (issue_at) references issues(at_uri) on delete cascade
1039 );
1040 `)
1041 if err != nil {
1042 return err
1043 }
1044
1045 // transfer data
1046 _, err = tx.Exec(`
1047 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
1048 select
1049 c.id,
1050 c.owner_did,
1051 c.rkey,
1052 i.at_uri, -- get at_uri from issues table
1053 c.body,
1054 c.created,
1055 c.edited,
1056 c.deleted
1057 from comments c
1058 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
1059 `)
1060 if err != nil {
1061 return err
1062 }
1063
1064 // drop old table
1065 _, err = tx.Exec(`drop table comments`)
1066 return err
1067 })
1068
1069 // add generated at_uri column to pulls table
1070 //
1071 // this requires a full table recreation because stored columns
1072 // cannot be added via alter
1073 //
1074 // disable foreign-keys for the next migration
1075 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1076 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
1077 _, err := tx.Exec(`
1078 create table if not exists pulls_new (
1079 -- identifiers
1080 id integer primary key autoincrement,
1081 pull_id integer not null,
1082 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
1083
1084 -- at identifiers
1085 repo_at text not null,
1086 owner_did text not null,
1087 rkey text not null,
1088
1089 -- content
1090 title text not null,
1091 body text not null,
1092 target_branch text not null,
1093 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
1094
1095 -- source info
1096 source_branch text,
1097 source_repo_at text,
1098
1099 -- stacking
1100 stack_id text,
1101 change_id text,
1102 parent_change_id text,
1103
1104 -- meta
1105 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1106
1107 -- constraints
1108 unique(repo_at, pull_id),
1109 unique(at_uri),
1110 foreign key (repo_at) references repos(at_uri) on delete cascade
1111 );
1112 `)
1113 if err != nil {
1114 return err
1115 }
1116
1117 // transfer data
1118 _, err = tx.Exec(`
1119 insert into pulls_new (
1120 id, pull_id, repo_at, owner_did, rkey,
1121 title, body, target_branch, state,
1122 source_branch, source_repo_at,
1123 stack_id, change_id, parent_change_id,
1124 created
1125 )
1126 select
1127 id, pull_id, repo_at, owner_did, rkey,
1128 title, body, target_branch, state,
1129 source_branch, source_repo_at,
1130 stack_id, change_id, parent_change_id,
1131 created
1132 from pulls;
1133 `)
1134 if err != nil {
1135 return err
1136 }
1137
1138 // drop old table
1139 _, err = tx.Exec(`drop table pulls`)
1140 if err != nil {
1141 return err
1142 }
1143
1144 // rename new table
1145 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1146 return err
1147 })
1148 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1149
1150 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1151 //
1152 // this requires a full table recreation because stored columns
1153 // cannot be added via alter
1154 //
1155 // disable foreign-keys for the next migration
1156 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1157 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1158 _, err := tx.Exec(`
1159 create table if not exists pull_submissions_new (
1160 -- identifiers
1161 id integer primary key autoincrement,
1162 pull_at text not null,
1163
1164 -- content, these are immutable, and require a resubmission to update
1165 round_number integer not null default 0,
1166 patch text,
1167 source_rev text,
1168
1169 -- meta
1170 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1171
1172 -- constraints
1173 unique(pull_at, round_number),
1174 foreign key (pull_at) references pulls(at_uri) on delete cascade
1175 );
1176 `)
1177 if err != nil {
1178 return err
1179 }
1180
1181 // transfer data, constructing pull_at from pulls table
1182 _, err = tx.Exec(`
1183 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1184 select
1185 ps.id,
1186 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1187 ps.round_number,
1188 ps.patch,
1189 ps.created
1190 from pull_submissions ps
1191 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1192 `)
1193 if err != nil {
1194 return err
1195 }
1196
1197 // drop old table
1198 _, err = tx.Exec(`drop table pull_submissions`)
1199 if err != nil {
1200 return err
1201 }
1202
1203 // rename new table
1204 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1205 return err
1206 })
1207 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1208
1209 // knots may report the combined patch for a comparison, we can store that on the appview side
1210 // (but not on the pds record), because calculating the combined patch requires a git index
1211 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1212 _, err := tx.Exec(`
1213 alter table pull_submissions add column combined text;
1214 `)
1215 return err
1216 })
1217
1218 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1219 _, err := tx.Exec(`
1220 alter table profile add column pronouns text;
1221 `)
1222 return err
1223 })
1224
1225 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1226 _, err := tx.Exec(`
1227 alter table repos add column website text;
1228 alter table repos add column topics text;
1229 `)
1230 return err
1231 })
1232
1233 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1234 _, err := tx.Exec(`
1235 alter table notification_preferences add column user_mentioned integer not null default 1;
1236 `)
1237 return err
1238 })
1239
1240 // remove the foreign key constraints from stars.
1241 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1242 _, err := tx.Exec(`
1243 create table stars_new (
1244 id integer primary key autoincrement,
1245 did text not null,
1246 rkey text not null,
1247
1248 subject_at text not null,
1249
1250 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1251 unique(did, rkey),
1252 unique(did, subject_at)
1253 );
1254
1255 insert into stars_new (
1256 id,
1257 did,
1258 rkey,
1259 subject_at,
1260 created
1261 )
1262 select
1263 id,
1264 starred_by_did,
1265 rkey,
1266 repo_at,
1267 created
1268 from stars;
1269
1270 drop table stars;
1271 alter table stars_new rename to stars;
1272
1273 create index if not exists idx_stars_created on stars(created);
1274 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1275 `)
1276 return err
1277 })
1278
1279 orm.RunMigration(conn, logger, "add-avatar-to-profile", func(tx *sql.Tx) error {
1280 _, err := tx.Exec(`
1281 alter table profile add column avatar text;
1282 `)
1283 return err
1284 })
1285
1286 orm.RunMigration(conn, logger, "remove-profile-stats-column-constraint", func(tx *sql.Tx) error {
1287 _, err := tx.Exec(`
1288 -- create new table without the check constraint
1289 create table profile_stats_new (
1290 id integer primary key autoincrement,
1291 did text not null,
1292 kind text not null, -- no constraint this time
1293 foreign key (did) references profile(did) on delete cascade
1294 );
1295
1296 -- copy data from old table
1297 insert into profile_stats_new (id, did, kind)
1298 select id, did, kind
1299 from profile_stats;
1300
1301 -- drop old table
1302 drop table profile_stats;
1303
1304 -- rename new table
1305 alter table profile_stats_new rename to profile_stats;
1306 `)
1307 return err
1308 })
1309
1310 orm.RunMigration(conn, logger, "add-preferred-handle-profile", func(tx *sql.Tx) error {
1311 _, err := tx.Exec(`
1312 alter table profile add column preferred_handle text;
1313 `)
1314 return err
1315 })
1316
1317 orm.RunMigration(conn, logger, "add-repo-did-column", func(tx *sql.Tx) error {
1318 _, err := tx.Exec(`
1319 alter table repos add column repo_did text;
1320 create unique index if not exists idx_repos_repo_did on repos(repo_did);
1321 `)
1322 return err
1323 })
1324
1325 orm.RunMigration(conn, logger, "add-pds-rewrite-status", func(tx *sql.Tx) error {
1326 _, err := tx.Exec(`
1327 create table if not exists pds_rewrite_status (
1328 id integer primary key autoincrement,
1329 user_did text not null,
1330 repo_did text not null,
1331 record_nsid text not null,
1332 record_rkey text not null,
1333 old_repo_at text not null,
1334 status text not null default 'pending',
1335 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1336 unique(user_did, record_nsid, record_rkey)
1337 );
1338 create index if not exists idx_pds_rewrite_user on pds_rewrite_status(user_did, status);
1339 `)
1340 return err
1341 })
1342
1343 orm.RunMigration(conn, logger, "add-pipelines-repo-did", func(tx *sql.Tx) error {
1344 _, err := tx.Exec(`
1345 alter table pipelines add column repo_did text;
1346 create index if not exists idx_pipelines_repo_did on pipelines(repo_did);
1347 `)
1348 return err
1349 })
1350
1351 orm.RunMigration(conn, logger, "migrate-knots-to-repo-dids", func(tx *sql.Tx) error {
1352 _, err := tx.Exec(`update registrations set needs_upgrade = 1`)
1353 return err
1354 })
1355
1356 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1357 orm.RunMigration(conn, logger, "drop-pinned-repos-at-uri-fk", func(tx *sql.Tx) error {
1358 _, err := tx.Exec(`
1359 create table if not exists profile_pinned_repositories_new (
1360 id integer primary key autoincrement,
1361 did text not null,
1362 pin text not null,
1363
1364 unique(did, pin),
1365 foreign key (did) references profile(did) on delete cascade
1366 );
1367
1368 insert into profile_pinned_repositories_new (id, did, pin)
1369 select id, did, at_uri from profile_pinned_repositories;
1370
1371 drop table profile_pinned_repositories;
1372
1373 alter table profile_pinned_repositories_new rename to profile_pinned_repositories;
1374 `)
1375 return err
1376 })
1377 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1378
1379 orm.RunMigration(conn, logger, "reset-profile-pin-rewrites", func(tx *sql.Tx) error {
1380 _, err := tx.Exec(`
1381 update pds_rewrite_status
1382 set status = 'pending',
1383 updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1384 where record_nsid = 'sh.tangled.actor.profile'
1385 and status = 'done'
1386 `)
1387 return err
1388 })
1389
1390 orm.RunMigration(conn, logger, "add-blob-data-to-pull-submissions", func(tx *sql.Tx) error {
1391 _, err := tx.Exec(`
1392 alter table pull_submissions add column patch_blob_ref text;
1393 alter table pull_submissions add column patch_blob_mime text;
1394 alter table pull_submissions add column patch_blob_size integer;
1395 `)
1396 return err
1397 })
1398
1399 orm.RunMigration(conn, logger, "replace-parent-change-id-with-aturi", func(tx *sql.Tx) error {
1400 // add new column
1401 _, err := tx.Exec(`
1402 alter table pulls add column dependent_on text;
1403 `)
1404 if err != nil {
1405 return err
1406 }
1407
1408 // populate dependent_on with at_uri of the parent
1409 _, err = tx.Exec(`
1410 update pulls
1411 set dependent_on = (
1412 select at_uri
1413 from pulls as parent
1414 where parent.stack_id = pulls.stack_id
1415 and parent.change_id = pulls.parent_change_id
1416 )
1417 where parent_change_id is not null;
1418 `)
1419 if err != nil {
1420 return err
1421 }
1422
1423 // drop old columns
1424 _, err = tx.Exec(`
1425 alter table pulls drop column parent_change_id;
1426 alter table pulls drop column stack_id;
1427 `)
1428
1429 return err
1430 })
1431
1432 return &DB{
1433 db,
1434 logger,
1435 }, nil
1436}
1437
1438func (d *DB) Close() error {
1439 return d.DB.Close()
1440}