Monorepo for Tangled
tangled.org
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 orm.RunMigration(conn, logger, "add-knot-members-table", func(tx *sql.Tx) error {
1965 _, err := tx.Exec(`
1966 create table if not exists knot_members (
1967 id integer primary key autoincrement,
1968 did text not null,
1969 rkey text not null,
1970 domain text not null,
1971 subject text not null,
1972 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1973 unique (did, domain, subject)
1974 );
1975 create index if not exists idx_knot_members_did_rkey on knot_members(did, rkey);
1976 `)
1977 return err
1978 })
1979
1980 orm.RunMigration(conn, logger, "add-comments-table", func(tx *sql.Tx) error {
1981 _, err := tx.Exec(`
1982 drop table if exists comments;
1983
1984 create table comments (
1985 -- identifiers
1986 id integer primary key autoincrement,
1987
1988 did text not null,
1989 collection text not null default 'sh.tangled.feed.comment',
1990 rkey text not null,
1991 at_uri text generated always as ('at://' || did || '/' || collection || '/' || rkey) stored,
1992 cid text,
1993
1994 -- content
1995 subject_uri text not null, -- at_uri of subject (issue, pr, string)
1996 subject_cid text not null, -- cid of subject
1997
1998 body_text text not null,
1999 body_original text,
2000 body_blobs text, -- json
2001
2002 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2003
2004 reply_to_uri text, -- at_uri of parent comment
2005 reply_to_cid text, -- cid of parent comment
2006
2007 pull_round_idx integer, -- pull round index. required when subject is sh.tangled.repo.pull
2008
2009 -- appview-local information
2010 edited text,
2011 deleted text,
2012
2013 unique(did, collection, rkey)
2014 );
2015
2016 insert into comments (
2017 did,
2018 collection,
2019 rkey,
2020 subject_uri,
2021 subject_cid, -- we need to know cid
2022 body_text,
2023 created,
2024 reply_to_uri,
2025 reply_to_cid, -- we need to know cid
2026 edited,
2027 deleted
2028 )
2029 select
2030 did,
2031 'sh.tangled.repo.issue.comment',
2032 rkey,
2033 issue_at,
2034 '',
2035 body,
2036 created,
2037 reply_to,
2038 '',
2039 edited,
2040 deleted
2041 from issue_comments
2042 where rkey is not null;
2043
2044 insert into comments (
2045 did,
2046 collection,
2047 rkey,
2048 subject_uri,
2049 subject_cid, -- we need to know cid
2050 body_text,
2051 created,
2052 pull_round_idx
2053 )
2054 select
2055 c.owner_did,
2056 'sh.tangled.repo.pull.comment',
2057 substr(
2058 substr(c.comment_at, 6 + instr(substr(c.comment_at, 6), '/')), -- nsid/rkey
2059 instr(
2060 substr(c.comment_at, 6 + instr(substr(c.comment_at, 6), '/')), -- nsid/rkey
2061 '/'
2062 ) + 1
2063 ), -- rkey
2064 p.at_uri,
2065 '',
2066 c.body,
2067 c.created,
2068 s.round_number
2069 from pull_comments c
2070 join pulls p on c.repo_did = p.repo_did and c.pull_id = p.pull_id
2071 join pull_submissions s on s.id = c.submission_id;
2072 `)
2073 return err
2074 })
2075
2076 orm.RunMigration(conn, logger, "migrate-legacy-comments", func(tx *sql.Tx) error {
2077 _, err := tx.Exec(`
2078 insert into pds_migration (name, did, collection, rkey)
2079 select
2080 'use-feed-comment',
2081 did,
2082 collection,
2083 rkey
2084 from comments
2085 where collection <> 'sh.tangled.feed.comment';
2086 `)
2087 return err
2088 })
2089
2090 return &DB{
2091 db,
2092 logger,
2093 }, nil
2094}
2095
2096func (d *DB) Close() error {
2097 return d.DB.Close()
2098}