alice.pds.demo.boltless.dev
This repository has no description
1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log/slog"
8 "strings"
9
10 _ "github.com/mattn/go-sqlite3"
11 "tangled.org/core/log"
12 "tangled.org/core/orm"
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 "_busy_timeout=5000",
39 }
40
41 logger := log.FromContext(ctx)
42 logger = log.SubLogger(logger, "db")
43
44 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
45 if err != nil {
46 return nil, err
47 }
48
49 conn, err := db.Conn(ctx)
50 if err != nil {
51 return nil, err
52 }
53 defer conn.Close()
54
55 _, err = conn.ExecContext(ctx, `
56 create table if not exists registrations (
57 id integer primary key autoincrement,
58 domain text not null unique,
59 did text not null,
60 secret text not null,
61 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
62 registered text
63 );
64 create table if not exists public_keys (
65 id integer primary key autoincrement,
66 did text not null,
67 name text not null,
68 key text not null,
69 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
70 unique(did, name, key)
71 );
72 create table if not exists repos (
73 id integer primary key autoincrement,
74 did text not null,
75 name text not null,
76 knot text not null,
77 rkey text not null,
78 at_uri text not null unique,
79 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
80 unique(did, name, knot, rkey)
81 );
82 create table if not exists collaborators (
83 id integer primary key autoincrement,
84 did text not null,
85 repo integer not null,
86 foreign key (repo) references repos(id) on delete cascade
87 );
88 create table if not exists follows (
89 user_did text not null,
90 subject_did text not null,
91 rkey text not null,
92 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
93 primary key (user_did, subject_did),
94 check (user_did <> subject_did)
95 );
96 create table if not exists vouches (
97 did text not null,
98 subject_did text not null,
99 cid text not null,
100 kind text not null default 'vouch',
101 reason text,
102 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
103 primary key (did, subject_did),
104 check (did <> subject_did),
105 check (kind in ('vouch', 'denounce'))
106 );
107 create table if not exists issues (
108 id integer primary key autoincrement,
109 owner_did text not null,
110 repo_at text not null,
111 issue_id integer not null,
112 title text not null,
113 body text not null,
114 open integer not null default 1,
115 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
116 issue_at text,
117 unique(repo_at, issue_id),
118 foreign key (repo_at) references repos(at_uri) 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 reference_links (
565 id integer primary key autoincrement,
566 from_at text not null,
567 to_at text not null,
568 unique (from_at, to_at)
569 );
570
571 create table if not exists webhooks (
572 id integer primary key autoincrement,
573 repo_at text not null,
574 url text not null,
575 secret text,
576 active integer not null default 1,
577 events text not null, -- comma-separated list of events
578 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
579 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
580
581 foreign key (repo_at) references repos(at_uri) on delete cascade
582 );
583
584 create table if not exists webhook_deliveries (
585 id integer primary key autoincrement,
586 webhook_id integer not null,
587 event text not null,
588 delivery_id text not null,
589 url text not null,
590 request_body text not null,
591 response_code integer,
592 response_body text,
593 success integer not null default 0,
594 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
595
596 foreign key (webhook_id) references webhooks(id) on delete cascade
597 );
598
599 create table if not exists bluesky_posts (
600 rkey text primary key,
601 text text not null,
602 created_at text not null,
603 langs text,
604 facets text,
605 embed text,
606 like_count integer not null default 0,
607 reply_count integer not null default 0,
608 repost_count integer not null default 0,
609 quote_count integer not null default 0
610 );
611
612 create table if not exists domain_claims (
613 id integer primary key autoincrement,
614 did text not null unique,
615 domain text not null unique,
616 deleted text -- timestamp when the domain was released/unclaimed; null means actively claimed
617 );
618
619 create table if not exists repo_sites (
620 id integer primary key autoincrement,
621 repo_at text not null unique,
622 branch text not null,
623 dir text not null default '/',
624 is_index integer not null default 0,
625 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
626 updated text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
627 foreign key (repo_at) references repos(at_uri) on delete cascade
628 );
629
630 create table if not exists site_deploys (
631 id integer primary key autoincrement,
632 repo_at text not null,
633 branch text not null,
634 dir text not null default '/',
635 commit_sha text not null default '',
636 status text not null check (status in ('success', 'failure')),
637 trigger text not null check (trigger in ('config_change', 'push')),
638 error text not null default '',
639 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
640 foreign key (repo_at) references repos(at_uri) on delete cascade
641 );
642
643 create table if not exists punchcard_preferences (
644 id integer primary key autoincrement,
645 user_did text not null unique,
646 hide_mine integer default 0,
647 hide_others integer default 0
648 );
649
650 create table if not exists newsletter_preferences (
651 id integer primary key autoincrement,
652 user_did text not null unique,
653 status text not null check (status in ('subscribed', 'dismissed')),
654 email text,
655 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
656 );
657
658 create table if not exists vouch_evidences (
659 id integer primary key autoincrement,
660 vouch_id integer not null,
661 at_uri text not null,
662 unique(vouch_id, at_uri),
663 foreign key (vouch_id) references vouches(id) on delete cascade
664 );
665
666 create table if not exists vouch_skips (
667 did text not null,
668 subject_did text not null,
669 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
670 primary key (did, subject_did),
671 check (did <> subject_did)
672 );
673
674
675 create table if not exists migrations (
676 id integer primary key autoincrement,
677 name text unique
678 );
679
680 -- indexes for better performance
681 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
682 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
683 create index if not exists idx_references_from_at on reference_links(from_at);
684 create index if not exists idx_references_to_at on reference_links(to_at);
685 create index if not exists idx_webhook_deliveries_webhook_id on webhook_deliveries(webhook_id);
686 create index if not exists idx_newsletter_prefs_user_did on newsletter_preferences(user_did);
687 `)
688 if err != nil {
689 return nil, err
690 }
691
692 // run migrations
693 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
694 tx.Exec(`
695 alter table repos add column description text check (length(description) <= 200);
696 `)
697 return nil
698 })
699
700 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
701 // add unconstrained column
702 _, err := tx.Exec(`
703 alter table public_keys
704 add column rkey text;
705 `)
706 if err != nil {
707 return err
708 }
709
710 // backfill
711 _, err = tx.Exec(`
712 update public_keys
713 set rkey = ''
714 where rkey is null;
715 `)
716 if err != nil {
717 return err
718 }
719
720 return nil
721 })
722
723 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
724 _, err := tx.Exec(`
725 alter table comments drop column comment_at;
726 alter table comments add column rkey text;
727 `)
728 return err
729 })
730
731 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
732 _, err := tx.Exec(`
733 alter table comments add column deleted text; -- timestamp
734 alter table comments add column edited text; -- timestamp
735 `)
736 return err
737 })
738
739 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
740 _, err := tx.Exec(`
741 alter table pulls add column source_branch text;
742 alter table pulls add column source_repo_at text;
743 alter table pull_submissions add column source_rev text;
744 `)
745 return err
746 })
747
748 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
749 _, err := tx.Exec(`
750 alter table repos add column source text;
751 `)
752 return err
753 })
754
755 // disable foreign-keys for the next migration
756 // NOTE: this cannot be done in a transaction, so it is run outside [0]
757 //
758 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
759 conn.ExecContext(ctx, "pragma foreign_keys = off;")
760 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
761 _, err := tx.Exec(`
762 create table pulls_new (
763 -- identifiers
764 id integer primary key autoincrement,
765 pull_id integer not null,
766
767 -- at identifiers
768 repo_at text not null,
769 owner_did text not null,
770 rkey text not null,
771
772 -- content
773 title text not null,
774 body text not null,
775 target_branch text not null,
776 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
777
778 -- source info
779 source_branch text,
780 source_repo_at text,
781
782 -- stacking
783 stack_id text,
784 change_id text,
785 parent_change_id text,
786
787 -- meta
788 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
789
790 -- constraints
791 unique(repo_at, pull_id),
792 foreign key (repo_at) references repos(at_uri) on delete cascade
793 );
794
795 insert into pulls_new (
796 id, pull_id,
797 repo_at, owner_did, rkey,
798 title, body, target_branch, state,
799 source_branch, source_repo_at,
800 created
801 )
802 select
803 id, pull_id,
804 repo_at, owner_did, rkey,
805 title, body, target_branch, state,
806 source_branch, source_repo_at,
807 created
808 FROM pulls;
809
810 drop table pulls;
811 alter table pulls_new rename to pulls;
812 `)
813 return err
814 })
815 conn.ExecContext(ctx, "pragma foreign_keys = on;")
816
817 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
818 tx.Exec(`
819 alter table repos add column spindle text;
820 `)
821 return nil
822 })
823
824 // drop all knot secrets, add unique constraint to knots
825 //
826 // knots will henceforth use service auth for signed requests
827 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
828 _, err := tx.Exec(`
829 create table registrations_new (
830 id integer primary key autoincrement,
831 domain text not null,
832 did text not null,
833 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
834 registered text,
835 read_only integer not null default 0,
836 unique(domain, did)
837 );
838
839 insert into registrations_new (id, domain, did, created, registered, read_only)
840 select id, domain, did, created, registered, 1 from registrations
841 where registered is not null;
842
843 drop table registrations;
844 alter table registrations_new rename to registrations;
845 `)
846 return err
847 })
848
849 // recreate and add rkey + created columns with default constraint
850 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
851 // create new table
852 // - repo_at instead of repo integer
853 // - rkey field
854 // - created field
855 _, err := tx.Exec(`
856 create table collaborators_new (
857 -- identifiers for the record
858 id integer primary key autoincrement,
859 did text not null,
860 rkey text,
861
862 -- content
863 subject_did text not null,
864 repo_at text not null,
865
866 -- meta
867 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
868
869 -- constraints
870 foreign key (repo_at) references repos(at_uri) on delete cascade
871 )
872 `)
873 if err != nil {
874 return err
875 }
876
877 // copy data
878 _, err = tx.Exec(`
879 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
880 select
881 c.id,
882 r.did,
883 '',
884 c.did,
885 r.at_uri
886 from collaborators c
887 join repos r on c.repo = r.id
888 `)
889 if err != nil {
890 return err
891 }
892
893 // drop old table
894 _, err = tx.Exec(`drop table collaborators`)
895 if err != nil {
896 return err
897 }
898
899 // rename new table
900 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
901 return err
902 })
903
904 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
905 _, err := tx.Exec(`
906 alter table issues add column rkey text not null default '';
907
908 -- get last url section from issue_at and save to rkey column
909 update issues
910 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
911 `)
912 return err
913 })
914
915 // repurpose the read-only column to "needs-upgrade"
916 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
917 _, err := tx.Exec(`
918 alter table registrations rename column read_only to needs_upgrade;
919 `)
920 return err
921 })
922
923 // require all knots to upgrade after the release of total xrpc
924 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
925 _, err := tx.Exec(`
926 update registrations set needs_upgrade = 1;
927 `)
928 return err
929 })
930
931 // require all knots to upgrade after the release of total xrpc
932 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
933 _, err := tx.Exec(`
934 alter table spindles add column needs_upgrade integer not null default 0;
935 `)
936 return err
937 })
938
939 // remove issue_at from issues and replace with generated column
940 //
941 // this requires a full table recreation because stored columns
942 // cannot be added via alter
943 //
944 // couple other changes:
945 // - columns renamed to be more consistent
946 // - adds edited and deleted fields
947 //
948 // disable foreign-keys for the next migration
949 conn.ExecContext(ctx, "pragma foreign_keys = off;")
950 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
951 _, err := tx.Exec(`
952 create table if not exists issues_new (
953 -- identifiers
954 id integer primary key autoincrement,
955 did text not null,
956 rkey text not null,
957 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
958
959 -- at identifiers
960 repo_at text not null,
961
962 -- content
963 issue_id integer not null,
964 title text not null,
965 body text not null,
966 open integer not null default 1,
967 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
968 edited text, -- timestamp
969 deleted text, -- timestamp
970
971 unique(did, rkey),
972 unique(repo_at, issue_id),
973 unique(at_uri),
974 foreign key (repo_at) references repos(at_uri) on delete cascade
975 );
976 `)
977 if err != nil {
978 return err
979 }
980
981 // transfer data
982 _, err = tx.Exec(`
983 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
984 select
985 i.id,
986 i.owner_did,
987 i.rkey,
988 i.repo_at,
989 i.issue_id,
990 i.title,
991 i.body,
992 i.open,
993 i.created
994 from issues i;
995 `)
996 if err != nil {
997 return err
998 }
999
1000 // drop old table
1001 _, err = tx.Exec(`drop table issues`)
1002 if err != nil {
1003 return err
1004 }
1005
1006 // rename new table
1007 _, err = tx.Exec(`alter table issues_new rename to issues`)
1008 return err
1009 })
1010 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1011
1012 // - renames the comments table to 'issue_comments'
1013 // - rework issue comments to update constraints:
1014 // * unique(did, rkey)
1015 // * remove comment-id and just use the global ID
1016 // * foreign key (repo_at, issue_id)
1017 // - new columns
1018 // * column "reply_to" which can be any other comment
1019 // * column "at-uri" which is a generated column
1020 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
1021 _, err := tx.Exec(`
1022 create table if not exists issue_comments (
1023 -- identifiers
1024 id integer primary key autoincrement,
1025 did text not null,
1026 rkey text,
1027 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
1028
1029 -- at identifiers
1030 issue_at text not null,
1031 reply_to text, -- at_uri of parent comment
1032
1033 -- content
1034 body text not null,
1035 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1036 edited text,
1037 deleted text,
1038
1039 -- constraints
1040 unique(did, rkey),
1041 unique(at_uri),
1042 foreign key (issue_at) references issues(at_uri) on delete cascade
1043 );
1044 `)
1045 if err != nil {
1046 return err
1047 }
1048
1049 // transfer data
1050 _, err = tx.Exec(`
1051 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
1052 select
1053 c.id,
1054 c.owner_did,
1055 c.rkey,
1056 i.at_uri, -- get at_uri from issues table
1057 c.body,
1058 c.created,
1059 c.edited,
1060 c.deleted
1061 from comments c
1062 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
1063 `)
1064 if err != nil {
1065 return err
1066 }
1067
1068 // drop old table
1069 _, err = tx.Exec(`drop table comments`)
1070 return err
1071 })
1072
1073 // add generated at_uri column to pulls table
1074 //
1075 // this requires a full table recreation because stored columns
1076 // cannot be added via alter
1077 //
1078 // disable foreign-keys for the next migration
1079 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1080 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
1081 _, err := tx.Exec(`
1082 create table if not exists pulls_new (
1083 -- identifiers
1084 id integer primary key autoincrement,
1085 pull_id integer not null,
1086 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
1087
1088 -- at identifiers
1089 repo_at text not null,
1090 owner_did text not null,
1091 rkey text not null,
1092
1093 -- content
1094 title text not null,
1095 body text not null,
1096 target_branch text not null,
1097 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
1098
1099 -- source info
1100 source_branch text,
1101 source_repo_at text,
1102
1103 -- stacking
1104 stack_id text,
1105 change_id text,
1106 parent_change_id text,
1107
1108 -- meta
1109 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1110
1111 -- constraints
1112 unique(repo_at, pull_id),
1113 unique(at_uri),
1114 foreign key (repo_at) references repos(at_uri) on delete cascade
1115 );
1116 `)
1117 if err != nil {
1118 return err
1119 }
1120
1121 // transfer data
1122 _, err = tx.Exec(`
1123 insert into pulls_new (
1124 id, pull_id, repo_at, owner_did, rkey,
1125 title, body, target_branch, state,
1126 source_branch, source_repo_at,
1127 stack_id, change_id, parent_change_id,
1128 created
1129 )
1130 select
1131 id, pull_id, repo_at, owner_did, rkey,
1132 title, body, target_branch, state,
1133 source_branch, source_repo_at,
1134 stack_id, change_id, parent_change_id,
1135 created
1136 from pulls;
1137 `)
1138 if err != nil {
1139 return err
1140 }
1141
1142 // drop old table
1143 _, err = tx.Exec(`drop table pulls`)
1144 if err != nil {
1145 return err
1146 }
1147
1148 // rename new table
1149 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1150 return err
1151 })
1152 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1153
1154 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1155 //
1156 // this requires a full table recreation because stored columns
1157 // cannot be added via alter
1158 //
1159 // disable foreign-keys for the next migration
1160 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1161 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1162 _, err := tx.Exec(`
1163 create table if not exists pull_submissions_new (
1164 -- identifiers
1165 id integer primary key autoincrement,
1166 pull_at text not null,
1167
1168 -- content, these are immutable, and require a resubmission to update
1169 round_number integer not null default 0,
1170 patch text,
1171 source_rev text,
1172
1173 -- meta
1174 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1175
1176 -- constraints
1177 unique(pull_at, round_number),
1178 foreign key (pull_at) references pulls(at_uri) on delete cascade
1179 );
1180 `)
1181 if err != nil {
1182 return err
1183 }
1184
1185 // transfer data, constructing pull_at from pulls table
1186 _, err = tx.Exec(`
1187 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1188 select
1189 ps.id,
1190 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1191 ps.round_number,
1192 ps.patch,
1193 ps.created
1194 from pull_submissions ps
1195 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1196 `)
1197 if err != nil {
1198 return err
1199 }
1200
1201 // drop old table
1202 _, err = tx.Exec(`drop table pull_submissions`)
1203 if err != nil {
1204 return err
1205 }
1206
1207 // rename new table
1208 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1209 return err
1210 })
1211 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1212
1213 // knots may report the combined patch for a comparison, we can store that on the appview side
1214 // (but not on the pds record), because calculating the combined patch requires a git index
1215 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1216 _, err := tx.Exec(`
1217 alter table pull_submissions add column combined text;
1218 `)
1219 return err
1220 })
1221
1222 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1223 _, err := tx.Exec(`
1224 alter table profile add column pronouns text;
1225 `)
1226 return err
1227 })
1228
1229 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1230 _, err := tx.Exec(`
1231 alter table repos add column website text;
1232 alter table repos add column topics text;
1233 `)
1234 return err
1235 })
1236
1237 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1238 _, err := tx.Exec(`
1239 alter table notification_preferences add column user_mentioned integer not null default 1;
1240 `)
1241 return err
1242 })
1243
1244 // remove the foreign key constraints from stars.
1245 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1246 _, err := tx.Exec(`
1247 create table stars_new (
1248 id integer primary key autoincrement,
1249 did text not null,
1250 rkey text not null,
1251
1252 subject_at text not null,
1253
1254 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1255 unique(did, rkey),
1256 unique(did, subject_at)
1257 );
1258
1259 insert into stars_new (
1260 id,
1261 did,
1262 rkey,
1263 subject_at,
1264 created
1265 )
1266 select
1267 id,
1268 starred_by_did,
1269 rkey,
1270 repo_at,
1271 created
1272 from stars;
1273
1274 drop table stars;
1275 alter table stars_new rename to stars;
1276
1277 create index if not exists idx_stars_created on stars(created);
1278 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1279 `)
1280 return err
1281 })
1282
1283 orm.RunMigration(conn, logger, "add-avatar-to-profile", func(tx *sql.Tx) error {
1284 _, err := tx.Exec(`
1285 alter table profile add column avatar text;
1286 `)
1287 return err
1288 })
1289
1290 orm.RunMigration(conn, logger, "remove-profile-stats-column-constraint", func(tx *sql.Tx) error {
1291 _, err := tx.Exec(`
1292 -- create new table without the check constraint
1293 create table profile_stats_new (
1294 id integer primary key autoincrement,
1295 did text not null,
1296 kind text not null, -- no constraint this time
1297 foreign key (did) references profile(did) on delete cascade
1298 );
1299
1300 -- copy data from old table
1301 insert into profile_stats_new (id, did, kind)
1302 select id, did, kind
1303 from profile_stats;
1304
1305 -- drop old table
1306 drop table profile_stats;
1307
1308 -- rename new table
1309 alter table profile_stats_new rename to profile_stats;
1310 `)
1311 return err
1312 })
1313
1314 orm.RunMigration(conn, logger, "add-preferred-handle-profile", func(tx *sql.Tx) error {
1315 _, err := tx.Exec(`
1316 alter table profile add column preferred_handle text;
1317 `)
1318 return err
1319 })
1320
1321 orm.RunMigration(conn, logger, "add-repo-did-column", func(tx *sql.Tx) error {
1322 _, err := tx.Exec(`
1323 alter table repos add column repo_did text;
1324 create unique index if not exists idx_repos_repo_did on repos(repo_did);
1325 `)
1326 return err
1327 })
1328
1329 orm.RunMigration(conn, logger, "add-pds-rewrite-status", func(tx *sql.Tx) error {
1330 _, err := tx.Exec(`
1331 create table if not exists pds_rewrite_status (
1332 id integer primary key autoincrement,
1333 user_did text not null,
1334 repo_did text not null,
1335 record_nsid text not null,
1336 record_rkey text not null,
1337 old_repo_at text not null,
1338 status text not null default 'pending',
1339 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1340 unique(user_did, record_nsid, record_rkey)
1341 );
1342 create index if not exists idx_pds_rewrite_user on pds_rewrite_status(user_did, status);
1343 `)
1344 return err
1345 })
1346
1347 orm.RunMigration(conn, logger, "add-pipelines-repo-did", func(tx *sql.Tx) error {
1348 _, err := tx.Exec(`
1349 alter table pipelines add column repo_did text;
1350 create index if not exists idx_pipelines_repo_did on pipelines(repo_did);
1351 `)
1352 return err
1353 })
1354
1355 orm.RunMigration(conn, logger, "migrate-knots-to-repo-dids", func(tx *sql.Tx) error {
1356 _, err := tx.Exec(`update registrations set needs_upgrade = 1`)
1357 return err
1358 })
1359
1360 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1361 orm.RunMigration(conn, logger, "drop-pinned-repos-at-uri-fk", func(tx *sql.Tx) error {
1362 _, err := tx.Exec(`
1363 create table if not exists profile_pinned_repositories_new (
1364 id integer primary key autoincrement,
1365 did text not null,
1366 pin text not null,
1367
1368 unique(did, pin),
1369 foreign key (did) references profile(did) on delete cascade
1370 );
1371
1372 insert into profile_pinned_repositories_new (id, did, pin)
1373 select id, did, at_uri from profile_pinned_repositories;
1374
1375 drop table profile_pinned_repositories;
1376
1377 alter table profile_pinned_repositories_new rename to profile_pinned_repositories;
1378 `)
1379 return err
1380 })
1381 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1382
1383 orm.RunMigration(conn, logger, "reset-profile-pin-rewrites", func(tx *sql.Tx) error {
1384 _, err := tx.Exec(`
1385 update pds_rewrite_status
1386 set status = 'pending',
1387 updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1388 where record_nsid = 'sh.tangled.actor.profile'
1389 and status = 'done'
1390 `)
1391 return err
1392 })
1393
1394 orm.RunMigration(conn, logger, "add-blob-data-to-pull-submissions", func(tx *sql.Tx) error {
1395 _, err := tx.Exec(`
1396 alter table pull_submissions add column patch_blob_ref text;
1397 alter table pull_submissions add column patch_blob_mime text;
1398 alter table pull_submissions add column patch_blob_size integer;
1399 `)
1400 return err
1401 })
1402
1403 orm.RunMigration(conn, logger, "replace-parent-change-id-with-aturi", func(tx *sql.Tx) error {
1404 // add new column
1405 _, err := tx.Exec(`
1406 alter table pulls add column dependent_on text;
1407 `)
1408 if err != nil {
1409 return err
1410 }
1411
1412 // populate dependent_on with at_uri of the parent
1413 _, err = tx.Exec(`
1414 update pulls
1415 set dependent_on = (
1416 select at_uri
1417 from pulls as parent
1418 where parent.stack_id = pulls.stack_id
1419 and parent.change_id = pulls.parent_change_id
1420 )
1421 where parent_change_id is not null;
1422 `)
1423 if err != nil {
1424 return err
1425 }
1426
1427 // drop old columns
1428 _, err = tx.Exec(`
1429 alter table pulls drop column parent_change_id;
1430 alter table pulls drop column stack_id;
1431 `)
1432
1433 return err
1434 })
1435
1436 orm.RunMigration(conn, logger, "add-pds-migration", func(tx *sql.Tx) error {
1437 _, err := tx.Exec(`
1438 create table if not exists pds_migration (
1439 name text not null,
1440
1441 -- record at_uri
1442 did text not null,
1443 collection text not null,
1444 rkey text not null,
1445
1446 status text not null default 'pending',
1447 error_msg text,
1448 retry_count integer not null default 0,
1449 retry_after integer not null default 0,
1450 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1451
1452 unique(name, did, collection, rkey)
1453 );
1454 `)
1455 return err
1456 })
1457
1458 orm.RunMigration(conn, logger, "unify-pds-record-migration-table", func(tx *sql.Tx) error {
1459 _, err := tx.Exec(`
1460 insert into pds_migration (
1461 name,
1462 did,
1463 collection,
1464 rkey,
1465 status,
1466 updated_at
1467 )
1468 select
1469 'add-repo-did',
1470 user_did,
1471 record_nsid,
1472 record_rkey,
1473 status,
1474 updated_at
1475 from pds_rewrite_status;
1476
1477 drop table pds_rewrite_status;
1478 `)
1479 return err
1480 })
1481
1482 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1483 orm.RunMigration(conn, logger, "add-id-to-vouches", func(tx *sql.Tx) error {
1484 _, err := tx.Exec(`
1485 create table vouches_new (
1486 id integer primary key autoincrement,
1487 did text not null,
1488 subject_did text not null,
1489 cid text not null,
1490 kind text not null default 'vouch',
1491 reason text,
1492 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1493 unique(did, subject_did),
1494 check (did <> subject_did),
1495 check (kind in ('vouch', 'denounce'))
1496 );
1497
1498 insert into vouches_new (did, subject_did, cid, kind, reason, created_at)
1499 select did, subject_did, cid, kind, reason, created_at
1500 from vouches;
1501
1502 drop table vouches;
1503 alter table vouches_new rename to vouches;
1504 `)
1505 return err
1506 })
1507 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1508
1509 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1510 orm.RunMigration(conn, logger, "drop-pipeline-statuses-pipeline-fk", func(tx *sql.Tx) error {
1511 _, err := tx.Exec(`
1512 create table if not exists pipeline_statuses_new (
1513 id integer primary key autoincrement,
1514 spindle text not null,
1515 rkey text not null,
1516
1517 pipeline_knot text not null,
1518 pipeline_rkey text not null,
1519
1520 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1521 workflow text not null,
1522 status text not null,
1523 error text,
1524 exit_code integer not null default 0,
1525
1526 unique (spindle, rkey)
1527 );
1528
1529 insert into pipeline_statuses_new
1530 select * from pipeline_statuses;
1531
1532 drop table pipeline_statuses;
1533 alter table pipeline_statuses_new rename to pipeline_statuses;
1534 `)
1535 return err
1536 })
1537 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1538
1539 orm.RunMigration(conn, logger, "add-repo-renames", func(tx *sql.Tx) error {
1540 res, err := tx.Exec(`
1541 update repos
1542 set name = name || '-renamed-' || id || '-' || lower(hex(randomblob(4)))
1543 where id in (
1544 select id from (
1545 select id, row_number() over (
1546 partition by did, knot, name
1547 order by created desc, id desc
1548 ) as rn
1549 from repos
1550 ) where rn > 1
1551 );
1552 `)
1553 if err != nil {
1554 return err
1555 }
1556 if n, _ := res.RowsAffected(); n > 0 {
1557 logger.Warn("suffixed legacy duplicate repo names before adding unique index", "rows", n)
1558 }
1559
1560 var remaining int
1561 if err := tx.QueryRow(`
1562 select count(*) from (
1563 select 1 from repos group by did, knot, name having count(*) > 1
1564 )
1565 `).Scan(&remaining); err != nil {
1566 return fmt.Errorf("checking for residual duplicate (did, knot, name) groups: %w", err)
1567 }
1568 if remaining > 0 {
1569 return fmt.Errorf("add-repo-renames: %d duplicate (did, knot, name) groups remain after suffix pass; manual cleanup required before unique index can be created", remaining)
1570 }
1571
1572 _, err = tx.Exec(`
1573 create table if not exists repo_renames (
1574 owner_did text not null,
1575 old_rkey text not null,
1576 repo_did text not null,
1577 renamed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1578 primary key (owner_did, old_rkey)
1579 );
1580 create unique index if not exists idx_repos_owner_knot_name
1581 on repos(did, knot, name);
1582 `)
1583 return err
1584 })
1585
1586 orm.RunMigration(conn, logger, "repos-canonical-rkey-uniqueness", func(tx *sql.Tx) error {
1587 _, err := tx.Exec(`
1588 drop index if exists idx_repos_owner_knot_name;
1589 create unique index if not exists idx_repos_did_rkey
1590 on repos(did, rkey);
1591 `)
1592 return err
1593 })
1594
1595 orm.RunMigration(conn, logger, "repo-did-references", func(tx *sql.Tx) error {
1596 tables := []struct{ table, oldCol, newCol string }{
1597 {"issues", "repo_at", "repo_did"},
1598 {"pulls", "repo_at", "repo_did"},
1599 {"pull_comments", "repo_at", "repo_did"},
1600 {"stars", "subject_at", "subject_did"},
1601 {"artifacts", "repo_at", "repo_did"},
1602 {"webhooks", "repo_at", "repo_did"},
1603 {"repo_sites", "repo_at", "repo_did"},
1604 {"site_deploys", "repo_at", "repo_did"},
1605 {"collaborators", "repo_at", "repo_did"},
1606 {"repo_issue_seqs", "repo_at", "repo_did"},
1607 {"repo_pull_seqs", "repo_at", "repo_did"},
1608 {"repo_languages", "repo_at", "repo_did"},
1609 {"repo_labels", "repo_at", "repo_did"},
1610 }
1611
1612 stmts := ""
1613 for _, t := range tables {
1614 stmts += fmt.Sprintf(
1615 `ALTER TABLE %s ADD COLUMN %s TEXT;
1616 UPDATE %s SET %s = (SELECT repos.repo_did FROM repos WHERE repos.at_uri = %s.%s);
1617 CREATE INDEX IF NOT EXISTS idx_%s_%s ON %s(%s);
1618 `, t.table, t.newCol, t.table, t.newCol, t.table, t.oldCol, t.table, t.newCol, t.table, t.newCol)
1619 }
1620
1621 stmts += `ALTER TABLE pulls ADD COLUMN source_repo_did TEXT;
1622 UPDATE pulls SET source_repo_did = (SELECT repos.repo_did FROM repos WHERE repos.at_uri = pulls.source_repo_at);
1623
1624 UPDATE profile_pinned_repositories SET pin = (
1625 SELECT repos.repo_did FROM repos WHERE repos.at_uri = profile_pinned_repositories.pin
1626 ) WHERE pin LIKE 'at://%'
1627 AND EXISTS (SELECT 1 FROM repos WHERE repos.at_uri = profile_pinned_repositories.pin AND repos.repo_did IS NOT NULL AND repos.repo_did != '');
1628 `
1629
1630 _, err := tx.Exec(stmts)
1631 return err
1632 })
1633
1634 orm.RunMigration(conn, logger, "backfill-pds-rewrites-star-issue-pull-collab", func(tx *sql.Tx) error {
1635 type source struct {
1636 userDidCol string
1637 table string
1638 nsid string
1639 fkCol string
1640 }
1641 sources := []source{
1642 {"did", "stars", "sh.tangled.feed.star", "subject_at"},
1643 {"did", "issues", "sh.tangled.repo.issue", "repo_at"},
1644 {"owner_did", "pulls", "sh.tangled.repo.pull", "repo_at"},
1645 {"did", "collaborators", "sh.tangled.repo.collaborator", "repo_at"},
1646 }
1647
1648 for _, src := range sources {
1649 _, err := tx.Exec(fmt.Sprintf(`
1650 INSERT INTO pds_migration (name, did, collection, rkey, status)
1651 SELECT 'add-repo-did', t.%s, '%s', t.rkey, 'pending'
1652 FROM %s t
1653 JOIN repos r ON r.at_uri = t.%s
1654 WHERE r.repo_did IS NOT NULL AND r.repo_did != ''
1655 ON CONFLICT(name, did, collection, rkey) DO NOTHING
1656 `, src.userDidCol, src.nsid, src.table, src.fkCol))
1657 if err != nil {
1658 return fmt.Errorf("backfill pds rewrites for %s: %w", src.table, err)
1659 }
1660 }
1661
1662 return nil
1663 })
1664
1665 orm.RunMigration(conn, logger, "backfill-pds-rewrites-profiles", func(tx *sql.Tx) error {
1666 _, err := tx.Exec(`
1667 INSERT INTO pds_migration (name, did, collection, rkey, status)
1668 SELECT DISTINCT 'add-repo-did', pp.did, 'sh.tangled.actor.profile', 'self', 'pending'
1669 FROM profile_pinned_repositories pp
1670 JOIN repos r ON r.at_uri = pp.pin
1671 WHERE pp.pin LIKE 'at://%'
1672 AND r.repo_did IS NOT NULL AND r.repo_did != ''
1673 ON CONFLICT(name, did, collection, rkey) DO NOTHING
1674 `)
1675 if err != nil {
1676 return fmt.Errorf("backfill pds rewrites for profiles: %w", err)
1677 }
1678 return nil
1679 })
1680
1681 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1682 orm.RunMigration(conn, logger, "drop-old-at-uri-columns", func(tx *sql.Tx) error {
1683 _, err := tx.Exec(`
1684 CREATE TABLE repos_new (
1685 id INTEGER PRIMARY KEY AUTOINCREMENT,
1686 did TEXT NOT NULL,
1687 name TEXT NOT NULL,
1688 knot TEXT NOT NULL,
1689 rkey TEXT NOT NULL,
1690 at_uri TEXT NOT NULL UNIQUE,
1691 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1692 description TEXT CHECK (length(description) <= 200),
1693 source TEXT,
1694 spindle TEXT,
1695 website TEXT,
1696 topics TEXT,
1697 repo_did TEXT,
1698 UNIQUE(did, rkey)
1699 );
1700 INSERT INTO repos_new (id, did, name, knot, rkey, at_uri, created, description, source, spindle, website, topics, repo_did)
1701 SELECT id, did, name, knot, rkey, at_uri, created, description, source, spindle, website, topics, repo_did
1702 FROM repos;
1703 DROP TABLE repos;
1704 ALTER TABLE repos_new RENAME TO repos;
1705 CREATE UNIQUE INDEX idx_repos_repo_did ON repos(repo_did);
1706 CREATE UNIQUE INDEX idx_repos_did_rkey ON repos(did, rkey);
1707
1708 CREATE TABLE issues_new (
1709 id INTEGER PRIMARY KEY AUTOINCREMENT,
1710 did TEXT NOT NULL,
1711 rkey TEXT NOT NULL,
1712 at_uri TEXT GENERATED ALWAYS AS ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) STORED,
1713 repo_did TEXT NOT NULL,
1714 issue_id INTEGER NOT NULL,
1715 title TEXT NOT NULL,
1716 body TEXT NOT NULL,
1717 open INTEGER NOT NULL DEFAULT 1,
1718 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1719 edited TEXT,
1720 deleted TEXT,
1721 UNIQUE(did, rkey),
1722 UNIQUE(repo_did, issue_id),
1723 UNIQUE(at_uri),
1724 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1725 );
1726 INSERT INTO issues_new (id, did, rkey, repo_did, issue_id, title, body, open, created, edited, deleted)
1727 SELECT id, did, rkey, repo_did, issue_id, title, body, open, created, edited, deleted
1728 FROM issues WHERE repo_did IS NOT NULL AND repo_did != '';
1729 DROP TABLE issues;
1730 ALTER TABLE issues_new RENAME TO issues;
1731 CREATE INDEX idx_issues_repo_did ON issues(repo_did);
1732
1733 CREATE TABLE pulls_new (
1734 id INTEGER PRIMARY KEY AUTOINCREMENT,
1735 pull_id INTEGER NOT NULL,
1736 at_uri TEXT GENERATED ALWAYS AS ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) STORED,
1737 repo_did TEXT NOT NULL,
1738 owner_did TEXT NOT NULL,
1739 rkey TEXT NOT NULL,
1740 title TEXT NOT NULL,
1741 body TEXT NOT NULL,
1742 target_branch TEXT NOT NULL,
1743 state INTEGER NOT NULL DEFAULT 0 CHECK (state IN (0, 1, 2, 3)),
1744 source_branch TEXT,
1745 source_repo_did TEXT,
1746 change_id TEXT,
1747 dependent_on TEXT,
1748 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1749 UNIQUE(repo_did, pull_id),
1750 UNIQUE(at_uri),
1751 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1752 );
1753 INSERT INTO pulls_new (id, pull_id, repo_did, owner_did, rkey, title, body, target_branch, state, source_branch, source_repo_did, change_id, dependent_on, created)
1754 SELECT id, pull_id, repo_did, owner_did, rkey, title, body, target_branch, state, source_branch, source_repo_did, change_id, dependent_on, created
1755 FROM pulls WHERE repo_did IS NOT NULL AND repo_did != '';
1756 DROP TABLE pulls;
1757 ALTER TABLE pulls_new RENAME TO pulls;
1758 CREATE INDEX idx_pulls_repo_did ON pulls(repo_did);
1759 CREATE INDEX idx_pulls_source_repo_did ON pulls(source_repo_did);
1760
1761 CREATE TABLE pull_comments_new (
1762 id INTEGER PRIMARY KEY AUTOINCREMENT,
1763 pull_id INTEGER NOT NULL,
1764 submission_id INTEGER NOT NULL,
1765 repo_did TEXT NOT NULL,
1766 owner_did TEXT NOT NULL,
1767 comment_at TEXT NOT NULL,
1768 body TEXT NOT NULL,
1769 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1770 FOREIGN KEY (repo_did, pull_id) REFERENCES pulls(repo_did, pull_id) ON DELETE CASCADE,
1771 FOREIGN KEY (submission_id) REFERENCES pull_submissions(id) ON DELETE CASCADE
1772 );
1773 INSERT INTO pull_comments_new (id, pull_id, submission_id, repo_did, owner_did, comment_at, body, created)
1774 SELECT id, pull_id, submission_id, repo_did, owner_did, comment_at, body, created
1775 FROM pull_comments WHERE repo_did IS NOT NULL AND repo_did != '';
1776 DROP TABLE pull_comments;
1777 ALTER TABLE pull_comments_new RENAME TO pull_comments;
1778 CREATE INDEX idx_pull_comments_repo_did ON pull_comments(repo_did);
1779
1780 CREATE TABLE stars_new (
1781 id INTEGER PRIMARY KEY AUTOINCREMENT,
1782 did TEXT NOT NULL,
1783 rkey TEXT NOT NULL,
1784 subject_type TEXT NOT NULL CHECK (subject_type IN ('repo', 'string')),
1785 subject TEXT NOT NULL,
1786 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1787 UNIQUE(did, rkey),
1788 UNIQUE(did, subject)
1789 );
1790 INSERT INTO stars_new (id, did, rkey, subject_type, subject, created)
1791 SELECT id, did, rkey, 'repo', subject_did, created
1792 FROM stars
1793 WHERE subject_did IS NOT NULL AND subject_did != '';
1794 INSERT OR IGNORE INTO stars_new (id, did, rkey, subject_type, subject, created)
1795 SELECT id, did, rkey, 'string', subject_at, created
1796 FROM stars
1797 WHERE (subject_did IS NULL OR subject_did = '')
1798 AND subject_at LIKE 'at://%/sh.tangled.string/%';
1799 DROP TABLE stars;
1800 ALTER TABLE stars_new RENAME TO stars;
1801 CREATE INDEX idx_stars_subject ON stars(subject);
1802 CREATE INDEX idx_stars_subject_type ON stars(subject_type);
1803 CREATE INDEX idx_stars_created ON stars(created);
1804
1805 CREATE TABLE collaborators_new (
1806 id INTEGER PRIMARY KEY AUTOINCREMENT,
1807 did TEXT NOT NULL,
1808 rkey TEXT,
1809 subject_did TEXT NOT NULL,
1810 repo_did TEXT NOT NULL,
1811 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1812 UNIQUE(did, rkey),
1813 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1814 );
1815 INSERT INTO collaborators_new (id, did, rkey, subject_did, repo_did, created)
1816 SELECT id, did, NULLIF(rkey, ''), subject_did, repo_did, created
1817 FROM collaborators WHERE repo_did IS NOT NULL AND repo_did != '';
1818 DROP TABLE collaborators;
1819 ALTER TABLE collaborators_new RENAME TO collaborators;
1820 CREATE INDEX idx_collaborators_repo_did ON collaborators(repo_did);
1821
1822 CREATE TABLE artifacts_new (
1823 id INTEGER PRIMARY KEY AUTOINCREMENT,
1824 did TEXT NOT NULL,
1825 rkey TEXT NOT NULL,
1826 repo_did TEXT NOT NULL,
1827 tag BINARY(20) NOT NULL,
1828 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1829 blob_cid TEXT NOT NULL,
1830 name TEXT NOT NULL,
1831 size INTEGER NOT NULL DEFAULT 0,
1832 mimetype TEXT NOT NULL DEFAULT '*/*',
1833 UNIQUE(did, rkey),
1834 UNIQUE(repo_did, tag, name),
1835 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1836 );
1837 INSERT INTO artifacts_new (id, did, rkey, repo_did, tag, created, blob_cid, name, size, mimetype)
1838 SELECT id, did, rkey, repo_did, tag, created, blob_cid, name, size, mimetype
1839 FROM artifacts WHERE repo_did IS NOT NULL AND repo_did != '';
1840 DROP TABLE artifacts;
1841 ALTER TABLE artifacts_new RENAME TO artifacts;
1842 CREATE INDEX idx_artifacts_repo_did ON artifacts(repo_did);
1843
1844 CREATE TABLE webhooks_new (
1845 id INTEGER PRIMARY KEY AUTOINCREMENT,
1846 repo_did TEXT NOT NULL,
1847 url TEXT NOT NULL,
1848 secret TEXT,
1849 active INTEGER NOT NULL DEFAULT 1,
1850 events TEXT NOT NULL,
1851 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1852 updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1853 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1854 );
1855 INSERT INTO webhooks_new (id, repo_did, url, secret, active, events, created_at, updated_at)
1856 SELECT id, repo_did, url, secret, active, events, created_at, updated_at
1857 FROM webhooks WHERE repo_did IS NOT NULL AND repo_did != '';
1858 DROP TABLE webhooks;
1859 ALTER TABLE webhooks_new RENAME TO webhooks;
1860 CREATE INDEX idx_webhooks_repo_did ON webhooks(repo_did);
1861
1862 CREATE TABLE repo_sites_new (
1863 id INTEGER PRIMARY KEY AUTOINCREMENT,
1864 repo_did TEXT NOT NULL UNIQUE,
1865 branch TEXT NOT NULL,
1866 dir TEXT NOT NULL DEFAULT '/',
1867 is_index INTEGER NOT NULL DEFAULT 0,
1868 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1869 updated TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1870 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1871 );
1872 INSERT INTO repo_sites_new (id, repo_did, branch, dir, is_index, created, updated)
1873 SELECT id, repo_did, branch, dir, is_index, created, updated
1874 FROM repo_sites WHERE repo_did IS NOT NULL AND repo_did != '';
1875 DROP TABLE repo_sites;
1876 ALTER TABLE repo_sites_new RENAME TO repo_sites;
1877
1878 CREATE TABLE site_deploys_new (
1879 id INTEGER PRIMARY KEY AUTOINCREMENT,
1880 repo_did TEXT NOT NULL,
1881 branch TEXT NOT NULL,
1882 dir TEXT NOT NULL DEFAULT '/',
1883 commit_sha TEXT NOT NULL DEFAULT '',
1884 status TEXT NOT NULL CHECK (status IN ('success', 'failure')),
1885 trigger TEXT NOT NULL CHECK (trigger IN ('config_change', 'push')),
1886 error TEXT NOT NULL DEFAULT '',
1887 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1888 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1889 );
1890 INSERT INTO site_deploys_new (id, repo_did, branch, dir, commit_sha, status, trigger, error, created_at)
1891 SELECT id, repo_did, branch, dir, commit_sha, status, trigger, error, created_at
1892 FROM site_deploys WHERE repo_did IS NOT NULL AND repo_did != '';
1893 DROP TABLE site_deploys;
1894 ALTER TABLE site_deploys_new RENAME TO site_deploys;
1895 CREATE INDEX idx_site_deploys_repo_did ON site_deploys(repo_did);
1896
1897 CREATE TABLE repo_issue_seqs_new (
1898 repo_did TEXT PRIMARY KEY,
1899 next_issue_id INTEGER NOT NULL DEFAULT 1,
1900 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1901 );
1902 INSERT INTO repo_issue_seqs_new (repo_did, next_issue_id)
1903 SELECT repo_did, next_issue_id
1904 FROM repo_issue_seqs WHERE repo_did IS NOT NULL AND repo_did != '';
1905 DROP TABLE repo_issue_seqs;
1906 ALTER TABLE repo_issue_seqs_new RENAME TO repo_issue_seqs;
1907
1908 CREATE TABLE repo_pull_seqs_new (
1909 repo_did TEXT PRIMARY KEY,
1910 next_pull_id INTEGER NOT NULL DEFAULT 1,
1911 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1912 );
1913 INSERT INTO repo_pull_seqs_new (repo_did, next_pull_id)
1914 SELECT repo_did, next_pull_id
1915 FROM repo_pull_seqs WHERE repo_did IS NOT NULL AND repo_did != '';
1916 DROP TABLE repo_pull_seqs;
1917 ALTER TABLE repo_pull_seqs_new RENAME TO repo_pull_seqs;
1918
1919 CREATE TABLE repo_languages_new (
1920 id INTEGER PRIMARY KEY AUTOINCREMENT,
1921 repo_did TEXT NOT NULL,
1922 ref TEXT NOT NULL,
1923 is_default_ref INTEGER NOT NULL DEFAULT 0,
1924 language TEXT NOT NULL,
1925 bytes INTEGER NOT NULL CHECK (bytes >= 0),
1926 UNIQUE(repo_did, ref, language),
1927 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1928 );
1929 INSERT INTO repo_languages_new (id, repo_did, ref, is_default_ref, language, bytes)
1930 SELECT id, repo_did, ref, is_default_ref, language, bytes
1931 FROM repo_languages WHERE repo_did IS NOT NULL AND repo_did != '';
1932 DROP TABLE repo_languages;
1933 ALTER TABLE repo_languages_new RENAME TO repo_languages;
1934
1935 CREATE TABLE repo_labels_new (
1936 id INTEGER PRIMARY KEY AUTOINCREMENT,
1937 repo_did TEXT NOT NULL,
1938 label_at TEXT NOT NULL,
1939 UNIQUE(repo_did, label_at),
1940 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1941 );
1942 INSERT INTO repo_labels_new (id, repo_did, label_at)
1943 SELECT id, repo_did, label_at
1944 FROM repo_labels WHERE repo_did IS NOT NULL AND repo_did != '';
1945 DROP TABLE repo_labels;
1946 ALTER TABLE repo_labels_new RENAME TO repo_labels;
1947 `)
1948 return err
1949 })
1950 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1951
1952 orm.RunMigration(conn, logger, "migrate-knots-to-repo-did-rename", func(tx *sql.Tx) error {
1953 _, err := tx.Exec(`
1954 update registrations set needs_upgrade = 1;
1955 `)
1956 return err
1957 })
1958
1959 orm.RunMigration(conn, logger, "drop-ghost-comments-table", func(tx *sql.Tx) error {
1960 _, err := tx.Exec(`DROP TABLE IF EXISTS comments`)
1961 return err
1962 })
1963
1964 return &DB{
1965 db,
1966 logger,
1967 }, nil
1968}
1969
1970func (d *DB) Close() error {
1971 return d.DB.Close()
1972}