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 comments (
121 id integer primary key autoincrement,
122 owner_did text not null,
123 issue_id integer not null,
124 repo_at text not null,
125 comment_id integer not null,
126 comment_at text not null,
127 body text not null,
128 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
129 unique(issue_id, comment_id),
130 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
131 );
132 create table if not exists pulls (
133 -- identifiers
134 id integer primary key autoincrement,
135 pull_id integer not null,
136
137 -- at identifiers
138 repo_at text not null,
139 owner_did text not null,
140 rkey text not null,
141 pull_at text,
142
143 -- content
144 title text not null,
145 body text not null,
146 target_branch text not null,
147 state integer not null default 0 check (state in (0, 1, 2)), -- open, merged, closed
148
149 -- meta
150 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
151
152 -- constraints
153 unique(repo_at, pull_id),
154 foreign key (repo_at) references repos(at_uri) on delete cascade
155 );
156
157 -- every pull must have atleast 1 submission: the initial submission
158 create table if not exists pull_submissions (
159 -- identifiers
160 id integer primary key autoincrement,
161 pull_id integer not null,
162
163 -- at identifiers
164 repo_at text not null,
165
166 -- content, these are immutable, and require a resubmission to update
167 round_number integer not null default 0,
168 patch text,
169
170 -- meta
171 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
172
173 -- constraints
174 unique(repo_at, pull_id, round_number),
175 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade
176 );
177
178 create table if not exists pull_comments (
179 -- identifiers
180 id integer primary key autoincrement,
181 pull_id integer not null,
182 submission_id integer not null,
183
184 -- at identifiers
185 repo_at text not null,
186 owner_did text not null,
187 comment_at text not null,
188
189 -- content
190 body text not null,
191
192 -- meta
193 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
194
195 -- constraints
196 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade,
197 foreign key (submission_id) references pull_submissions(id) on delete cascade
198 );
199
200 create table if not exists _jetstream (
201 id integer primary key autoincrement,
202 last_time_us integer not null
203 );
204
205 create table if not exists repo_issue_seqs (
206 repo_at text primary key,
207 next_issue_id integer not null default 1
208 );
209
210 create table if not exists repo_pull_seqs (
211 repo_at text primary key,
212 next_pull_id integer not null default 1
213 );
214
215 create table if not exists stars (
216 id integer primary key autoincrement,
217 starred_by_did text not null,
218 repo_at text not null,
219 rkey text not null,
220 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
221 foreign key (repo_at) references repos(at_uri) on delete cascade,
222 unique(starred_by_did, repo_at)
223 );
224
225 create table if not exists reactions (
226 id integer primary key autoincrement,
227 reacted_by_did text not null,
228 thread_at text not null,
229 kind text not null,
230 rkey text not null,
231 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
232 unique(reacted_by_did, thread_at, kind)
233 );
234
235 create table if not exists emails (
236 id integer primary key autoincrement,
237 did text not null,
238 email text not null,
239 verified integer not null default 0,
240 verification_code text not null,
241 last_sent text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
242 is_primary integer not null default 0,
243 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
244 unique(did, email)
245 );
246
247 create table if not exists artifacts (
248 -- id
249 id integer primary key autoincrement,
250 did text not null,
251 rkey text not null,
252
253 -- meta
254 repo_at text not null,
255 tag binary(20) not null,
256 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
257
258 -- data
259 blob_cid text not null,
260 name text not null,
261 size integer not null default 0,
262 mimetype string not null default "*/*",
263
264 -- constraints
265 unique(did, rkey), -- record must be unique
266 unique(repo_at, tag, name), -- for a given tag object, each file must be unique
267 foreign key (repo_at) references repos(at_uri) on delete cascade
268 );
269
270 create table if not exists profile (
271 -- id
272 id integer primary key autoincrement,
273 did text not null,
274
275 -- data
276 description text not null,
277 include_bluesky integer not null default 0,
278 location text,
279
280 -- constraints
281 unique(did)
282 );
283 create table if not exists profile_links (
284 -- id
285 id integer primary key autoincrement,
286 did text not null,
287
288 -- data
289 link text not null,
290
291 -- constraints
292 foreign key (did) references profile(did) on delete cascade
293 );
294 create table if not exists profile_stats (
295 -- id
296 id integer primary key autoincrement,
297 did text not null,
298
299 -- data
300 kind text not null check (kind in (
301 "merged-pull-request-count",
302 "closed-pull-request-count",
303 "open-pull-request-count",
304 "open-issue-count",
305 "closed-issue-count",
306 "repository-count"
307 )),
308
309 -- constraints
310 foreign key (did) references profile(did) on delete cascade
311 );
312 create table if not exists profile_pinned_repositories (
313 -- id
314 id integer primary key autoincrement,
315 did text not null,
316
317 -- data
318 at_uri text not null,
319
320 -- constraints
321 unique(did, at_uri),
322 foreign key (did) references profile(did) on delete cascade,
323 foreign key (at_uri) references repos(at_uri) on delete cascade
324 );
325
326 create table if not exists oauth_requests (
327 id integer primary key autoincrement,
328 auth_server_iss text not null,
329 state text not null,
330 did text not null,
331 handle text not null,
332 pds_url text not null,
333 pkce_verifier text not null,
334 dpop_auth_server_nonce text not null,
335 dpop_private_jwk text not null
336 );
337
338 create table if not exists oauth_sessions (
339 id integer primary key autoincrement,
340 did text not null,
341 handle text not null,
342 pds_url text not null,
343 auth_server_iss text not null,
344 access_jwt text not null,
345 refresh_jwt text not null,
346 dpop_pds_nonce text,
347 dpop_auth_server_nonce text not null,
348 dpop_private_jwk text not null,
349 expiry text not null
350 );
351
352 create table if not exists punchcard (
353 did text not null,
354 date text not null, -- yyyy-mm-dd
355 count integer,
356 primary key (did, date)
357 );
358
359 create table if not exists spindles (
360 id integer primary key autoincrement,
361 owner text not null,
362 instance text not null,
363 verified text, -- time of verification
364 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
365
366 unique(owner, instance)
367 );
368
369 create table if not exists spindle_members (
370 -- identifiers for the record
371 id integer primary key autoincrement,
372 did text not null,
373 rkey text not null,
374
375 -- data
376 instance text not null,
377 subject text not null,
378 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
379
380 -- constraints
381 unique (did, instance, subject)
382 );
383
384 create table if not exists pipelines (
385 -- identifiers
386 id integer primary key autoincrement,
387 knot text not null,
388 rkey text not null,
389
390 repo_owner text not null,
391 repo_name text not null,
392
393 -- every pipeline must be associated with exactly one commit
394 sha text not null check (length(sha) = 40),
395 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
396
397 -- trigger data
398 trigger_id integer not null,
399
400 unique(knot, rkey),
401 foreign key (trigger_id) references triggers(id) on delete cascade
402 );
403
404 create table if not exists triggers (
405 -- primary key
406 id integer primary key autoincrement,
407
408 -- top-level fields
409 kind text not null,
410
411 -- pushTriggerData fields
412 push_ref text,
413 push_new_sha text check (length(push_new_sha) = 40),
414 push_old_sha text check (length(push_old_sha) = 40),
415
416 -- pullRequestTriggerData fields
417 pr_source_branch text,
418 pr_target_branch text,
419 pr_source_sha text check (length(pr_source_sha) = 40),
420 pr_action text
421 );
422
423 create table if not exists pipeline_statuses (
424 -- identifiers
425 id integer primary key autoincrement,
426 spindle text not null,
427 rkey text not null,
428
429 -- referenced pipeline. these form the (did, rkey) pair
430 pipeline_knot text not null,
431 pipeline_rkey text not null,
432
433 -- content
434 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
435 workflow text not null,
436 status text not null,
437 error text,
438 exit_code integer not null default 0,
439
440 unique (spindle, rkey),
441 foreign key (pipeline_knot, pipeline_rkey)
442 references pipelines (knot, rkey)
443 on delete cascade
444 );
445
446 create table if not exists repo_languages (
447 -- identifiers
448 id integer primary key autoincrement,
449
450 -- repo identifiers
451 repo_at text not null,
452 ref text not null,
453 is_default_ref integer not null default 0,
454
455 -- language breakdown
456 language text not null,
457 bytes integer not null check (bytes >= 0),
458
459 unique(repo_at, ref, language)
460 );
461
462 create table if not exists signups_inflight (
463 id integer primary key autoincrement,
464 email text not null unique,
465 invite_code text not null,
466 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
467 );
468
469 create table if not exists strings (
470 -- identifiers
471 did text not null,
472 rkey text not null,
473
474 -- content
475 filename text not null,
476 description text,
477 content text not null,
478 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
479 edited text,
480
481 primary key (did, rkey)
482 );
483
484 create table if not exists label_definitions (
485 -- identifiers
486 id integer primary key autoincrement,
487 did text not null,
488 rkey text not null,
489 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.definition' || '/' || rkey) stored,
490
491 -- content
492 name text not null,
493 value_type text not null check (value_type in (
494 "null",
495 "boolean",
496 "integer",
497 "string"
498 )),
499 value_format text not null default "any",
500 value_enum text, -- comma separated list
501 scope text not null, -- comma separated list of nsid
502 color text,
503 multiple integer not null default 0,
504 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
505
506 -- constraints
507 unique (did, rkey)
508 unique (at_uri)
509 );
510
511 -- ops are flattened, a record may contain several additions and deletions, but the table will include one row per add/del
512 create table if not exists label_ops (
513 -- identifiers
514 id integer primary key autoincrement,
515 did text not null,
516 rkey text not null,
517 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.op' || '/' || rkey) stored,
518
519 -- content
520 subject text not null,
521 operation text not null check (operation in ("add", "del")),
522 operand_key text not null,
523 operand_value text not null,
524 -- we need two time values: performed is declared by the user, indexed is calculated by the av
525 performed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
526 indexed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
527
528 -- constraints
529 -- traditionally (did, rkey) pair should be unique, but not in this case
530 -- operand_key should reference a label definition
531 foreign key (operand_key) references label_definitions (at_uri) on delete cascade,
532 unique (did, rkey, subject, operand_key, operand_value)
533 );
534
535 create table if not exists repo_labels (
536 -- identifiers
537 id integer primary key autoincrement,
538
539 -- repo identifiers
540 repo_at text not null,
541
542 -- label to subscribe to
543 label_at text not null,
544
545 unique (repo_at, label_at)
546 );
547
548 create table if not exists notifications (
549 id integer primary key autoincrement,
550 recipient_did text not null,
551 actor_did text not null,
552 type text not null,
553 entity_type text not null,
554 entity_id text not null,
555 read integer not null default 0,
556 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
557 repo_id integer references repos(id),
558 issue_id integer references issues(id),
559 pull_id integer references pulls(id)
560 );
561
562 create table if not exists notification_preferences (
563 id integer primary key autoincrement,
564 user_did text not null unique,
565 repo_starred integer not null default 1,
566 issue_created integer not null default 1,
567 issue_commented integer not null default 1,
568 pull_created integer not null default 1,
569 pull_commented integer not null default 1,
570 followed integer not null default 1,
571 pull_merged integer not null default 1,
572 issue_closed integer not null default 1,
573 email_notifications integer not null default 0
574 );
575
576 create table if not exists reference_links (
577 id integer primary key autoincrement,
578 from_at text not null,
579 to_at text not null,
580 unique (from_at, to_at)
581 );
582
583 create table if not exists webhooks (
584 id integer primary key autoincrement,
585 repo_at text not null,
586 url text not null,
587 secret text,
588 active integer not null default 1,
589 events text not null, -- comma-separated list of events
590 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
591 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
592
593 foreign key (repo_at) references repos(at_uri) on delete cascade
594 );
595
596 create table if not exists webhook_deliveries (
597 id integer primary key autoincrement,
598 webhook_id integer not null,
599 event text not null,
600 delivery_id text not null,
601 url text not null,
602 request_body text not null,
603 response_code integer,
604 response_body text,
605 success integer not null default 0,
606 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
607
608 foreign key (webhook_id) references webhooks(id) on delete cascade
609 );
610
611 create table if not exists bluesky_posts (
612 rkey text primary key,
613 text text not null,
614 created_at text not null,
615 langs text,
616 facets text,
617 embed text,
618 like_count integer not null default 0,
619 reply_count integer not null default 0,
620 repost_count integer not null default 0,
621 quote_count integer not null default 0
622 );
623
624 create table if not exists domain_claims (
625 id integer primary key autoincrement,
626 did text not null unique,
627 domain text not null unique,
628 deleted text -- timestamp when the domain was released/unclaimed; null means actively claimed
629 );
630
631 create table if not exists repo_sites (
632 id integer primary key autoincrement,
633 repo_at text not null unique,
634 branch text not null,
635 dir text not null default '/',
636 is_index integer not null default 0,
637 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
638 updated text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
639 foreign key (repo_at) references repos(at_uri) on delete cascade
640 );
641
642 create table if not exists site_deploys (
643 id integer primary key autoincrement,
644 repo_at text not null,
645 branch text not null,
646 dir text not null default '/',
647 commit_sha text not null default '',
648 status text not null check (status in ('success', 'failure')),
649 trigger text not null check (trigger in ('config_change', 'push')),
650 error text not null default '',
651 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
652 foreign key (repo_at) references repos(at_uri) on delete cascade
653 );
654
655 create table if not exists punchcard_preferences (
656 id integer primary key autoincrement,
657 user_did text not null unique,
658 hide_mine integer default 0,
659 hide_others integer default 0
660 );
661
662 create table if not exists newsletter_preferences (
663 id integer primary key autoincrement,
664 user_did text not null unique,
665 status text not null check (status in ('subscribed', 'dismissed')),
666 email text,
667 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
668 );
669
670 create table if not exists vouch_evidences (
671 id integer primary key autoincrement,
672 vouch_id integer not null,
673 at_uri text not null,
674 unique(vouch_id, at_uri),
675 foreign key (vouch_id) references vouches(id) on delete cascade
676 );
677
678 create table if not exists vouch_skips (
679 did text not null,
680 subject_did text not null,
681 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
682 primary key (did, subject_did),
683 check (did <> subject_did)
684 );
685
686
687 create table if not exists migrations (
688 id integer primary key autoincrement,
689 name text unique
690 );
691
692 -- indexes for better performance
693 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
694 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
695 create index if not exists idx_references_from_at on reference_links(from_at);
696 create index if not exists idx_references_to_at on reference_links(to_at);
697 create index if not exists idx_webhook_deliveries_webhook_id on webhook_deliveries(webhook_id);
698 create index if not exists idx_newsletter_prefs_user_did on newsletter_preferences(user_did);
699 `)
700 if err != nil {
701 return nil, err
702 }
703
704 // run migrations
705 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
706 tx.Exec(`
707 alter table repos add column description text check (length(description) <= 200);
708 `)
709 return nil
710 })
711
712 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
713 // add unconstrained column
714 _, err := tx.Exec(`
715 alter table public_keys
716 add column rkey text;
717 `)
718 if err != nil {
719 return err
720 }
721
722 // backfill
723 _, err = tx.Exec(`
724 update public_keys
725 set rkey = ''
726 where rkey is null;
727 `)
728 if err != nil {
729 return err
730 }
731
732 return nil
733 })
734
735 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
736 _, err := tx.Exec(`
737 alter table comments drop column comment_at;
738 alter table comments add column rkey text;
739 `)
740 return err
741 })
742
743 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
744 _, err := tx.Exec(`
745 alter table comments add column deleted text; -- timestamp
746 alter table comments add column edited text; -- timestamp
747 `)
748 return err
749 })
750
751 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
752 _, err := tx.Exec(`
753 alter table pulls add column source_branch text;
754 alter table pulls add column source_repo_at text;
755 alter table pull_submissions add column source_rev text;
756 `)
757 return err
758 })
759
760 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
761 _, err := tx.Exec(`
762 alter table repos add column source text;
763 `)
764 return err
765 })
766
767 // disable foreign-keys for the next migration
768 // NOTE: this cannot be done in a transaction, so it is run outside [0]
769 //
770 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
771 conn.ExecContext(ctx, "pragma foreign_keys = off;")
772 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
773 _, err := tx.Exec(`
774 create table pulls_new (
775 -- identifiers
776 id integer primary key autoincrement,
777 pull_id integer not null,
778
779 -- at identifiers
780 repo_at text not null,
781 owner_did text not null,
782 rkey text not null,
783
784 -- content
785 title text not null,
786 body text not null,
787 target_branch text not null,
788 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
789
790 -- source info
791 source_branch text,
792 source_repo_at text,
793
794 -- stacking
795 stack_id text,
796 change_id text,
797 parent_change_id text,
798
799 -- meta
800 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
801
802 -- constraints
803 unique(repo_at, pull_id),
804 foreign key (repo_at) references repos(at_uri) on delete cascade
805 );
806
807 insert into pulls_new (
808 id, pull_id,
809 repo_at, owner_did, rkey,
810 title, body, target_branch, state,
811 source_branch, source_repo_at,
812 created
813 )
814 select
815 id, pull_id,
816 repo_at, owner_did, rkey,
817 title, body, target_branch, state,
818 source_branch, source_repo_at,
819 created
820 FROM pulls;
821
822 drop table pulls;
823 alter table pulls_new rename to pulls;
824 `)
825 return err
826 })
827 conn.ExecContext(ctx, "pragma foreign_keys = on;")
828
829 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
830 tx.Exec(`
831 alter table repos add column spindle text;
832 `)
833 return nil
834 })
835
836 // drop all knot secrets, add unique constraint to knots
837 //
838 // knots will henceforth use service auth for signed requests
839 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
840 _, err := tx.Exec(`
841 create table registrations_new (
842 id integer primary key autoincrement,
843 domain text not null,
844 did text not null,
845 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
846 registered text,
847 read_only integer not null default 0,
848 unique(domain, did)
849 );
850
851 insert into registrations_new (id, domain, did, created, registered, read_only)
852 select id, domain, did, created, registered, 1 from registrations
853 where registered is not null;
854
855 drop table registrations;
856 alter table registrations_new rename to registrations;
857 `)
858 return err
859 })
860
861 // recreate and add rkey + created columns with default constraint
862 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
863 // create new table
864 // - repo_at instead of repo integer
865 // - rkey field
866 // - created field
867 _, err := tx.Exec(`
868 create table collaborators_new (
869 -- identifiers for the record
870 id integer primary key autoincrement,
871 did text not null,
872 rkey text,
873
874 -- content
875 subject_did text not null,
876 repo_at text not null,
877
878 -- meta
879 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
880
881 -- constraints
882 foreign key (repo_at) references repos(at_uri) on delete cascade
883 )
884 `)
885 if err != nil {
886 return err
887 }
888
889 // copy data
890 _, err = tx.Exec(`
891 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
892 select
893 c.id,
894 r.did,
895 '',
896 c.did,
897 r.at_uri
898 from collaborators c
899 join repos r on c.repo = r.id
900 `)
901 if err != nil {
902 return err
903 }
904
905 // drop old table
906 _, err = tx.Exec(`drop table collaborators`)
907 if err != nil {
908 return err
909 }
910
911 // rename new table
912 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
913 return err
914 })
915
916 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
917 _, err := tx.Exec(`
918 alter table issues add column rkey text not null default '';
919
920 -- get last url section from issue_at and save to rkey column
921 update issues
922 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
923 `)
924 return err
925 })
926
927 // repurpose the read-only column to "needs-upgrade"
928 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
929 _, err := tx.Exec(`
930 alter table registrations rename column read_only to needs_upgrade;
931 `)
932 return err
933 })
934
935 // require all knots to upgrade after the release of total xrpc
936 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
937 _, err := tx.Exec(`
938 update registrations set needs_upgrade = 1;
939 `)
940 return err
941 })
942
943 // require all knots to upgrade after the release of total xrpc
944 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
945 _, err := tx.Exec(`
946 alter table spindles add column needs_upgrade integer not null default 0;
947 `)
948 return err
949 })
950
951 // remove issue_at from issues and replace with generated column
952 //
953 // this requires a full table recreation because stored columns
954 // cannot be added via alter
955 //
956 // couple other changes:
957 // - columns renamed to be more consistent
958 // - adds edited and deleted fields
959 //
960 // disable foreign-keys for the next migration
961 conn.ExecContext(ctx, "pragma foreign_keys = off;")
962 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
963 _, err := tx.Exec(`
964 create table if not exists issues_new (
965 -- identifiers
966 id integer primary key autoincrement,
967 did text not null,
968 rkey text not null,
969 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
970
971 -- at identifiers
972 repo_at text not null,
973
974 -- content
975 issue_id integer not null,
976 title text not null,
977 body text not null,
978 open integer not null default 1,
979 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
980 edited text, -- timestamp
981 deleted text, -- timestamp
982
983 unique(did, rkey),
984 unique(repo_at, issue_id),
985 unique(at_uri),
986 foreign key (repo_at) references repos(at_uri) on delete cascade
987 );
988 `)
989 if err != nil {
990 return err
991 }
992
993 // transfer data
994 _, err = tx.Exec(`
995 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
996 select
997 i.id,
998 i.owner_did,
999 i.rkey,
1000 i.repo_at,
1001 i.issue_id,
1002 i.title,
1003 i.body,
1004 i.open,
1005 i.created
1006 from issues i;
1007 `)
1008 if err != nil {
1009 return err
1010 }
1011
1012 // drop old table
1013 _, err = tx.Exec(`drop table issues`)
1014 if err != nil {
1015 return err
1016 }
1017
1018 // rename new table
1019 _, err = tx.Exec(`alter table issues_new rename to issues`)
1020 return err
1021 })
1022 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1023
1024 // - renames the comments table to 'issue_comments'
1025 // - rework issue comments to update constraints:
1026 // * unique(did, rkey)
1027 // * remove comment-id and just use the global ID
1028 // * foreign key (repo_at, issue_id)
1029 // - new columns
1030 // * column "reply_to" which can be any other comment
1031 // * column "at-uri" which is a generated column
1032 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
1033 _, err := tx.Exec(`
1034 create table if not exists issue_comments (
1035 -- identifiers
1036 id integer primary key autoincrement,
1037 did text not null,
1038 rkey text,
1039 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
1040
1041 -- at identifiers
1042 issue_at text not null,
1043 reply_to text, -- at_uri of parent comment
1044
1045 -- content
1046 body text not null,
1047 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1048 edited text,
1049 deleted text,
1050
1051 -- constraints
1052 unique(did, rkey),
1053 unique(at_uri),
1054 foreign key (issue_at) references issues(at_uri) on delete cascade
1055 );
1056 `)
1057 if err != nil {
1058 return err
1059 }
1060
1061 // transfer data
1062 _, err = tx.Exec(`
1063 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
1064 select
1065 c.id,
1066 c.owner_did,
1067 c.rkey,
1068 i.at_uri, -- get at_uri from issues table
1069 c.body,
1070 c.created,
1071 c.edited,
1072 c.deleted
1073 from comments c
1074 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
1075 `)
1076 if err != nil {
1077 return err
1078 }
1079
1080 // drop old table
1081 _, err = tx.Exec(`drop table comments`)
1082 return err
1083 })
1084
1085 // add generated at_uri column to pulls table
1086 //
1087 // this requires a full table recreation because stored columns
1088 // cannot be added via alter
1089 //
1090 // disable foreign-keys for the next migration
1091 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1092 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
1093 _, err := tx.Exec(`
1094 create table if not exists pulls_new (
1095 -- identifiers
1096 id integer primary key autoincrement,
1097 pull_id integer not null,
1098 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
1099
1100 -- at identifiers
1101 repo_at text not null,
1102 owner_did text not null,
1103 rkey text not null,
1104
1105 -- content
1106 title text not null,
1107 body text not null,
1108 target_branch text not null,
1109 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
1110
1111 -- source info
1112 source_branch text,
1113 source_repo_at text,
1114
1115 -- stacking
1116 stack_id text,
1117 change_id text,
1118 parent_change_id text,
1119
1120 -- meta
1121 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1122
1123 -- constraints
1124 unique(repo_at, pull_id),
1125 unique(at_uri),
1126 foreign key (repo_at) references repos(at_uri) on delete cascade
1127 );
1128 `)
1129 if err != nil {
1130 return err
1131 }
1132
1133 // transfer data
1134 _, err = tx.Exec(`
1135 insert into pulls_new (
1136 id, pull_id, repo_at, owner_did, rkey,
1137 title, body, target_branch, state,
1138 source_branch, source_repo_at,
1139 stack_id, change_id, parent_change_id,
1140 created
1141 )
1142 select
1143 id, pull_id, repo_at, owner_did, rkey,
1144 title, body, target_branch, state,
1145 source_branch, source_repo_at,
1146 stack_id, change_id, parent_change_id,
1147 created
1148 from pulls;
1149 `)
1150 if err != nil {
1151 return err
1152 }
1153
1154 // drop old table
1155 _, err = tx.Exec(`drop table pulls`)
1156 if err != nil {
1157 return err
1158 }
1159
1160 // rename new table
1161 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1162 return err
1163 })
1164 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1165
1166 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1167 //
1168 // this requires a full table recreation because stored columns
1169 // cannot be added via alter
1170 //
1171 // disable foreign-keys for the next migration
1172 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1173 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1174 _, err := tx.Exec(`
1175 create table if not exists pull_submissions_new (
1176 -- identifiers
1177 id integer primary key autoincrement,
1178 pull_at text not null,
1179
1180 -- content, these are immutable, and require a resubmission to update
1181 round_number integer not null default 0,
1182 patch text,
1183 source_rev text,
1184
1185 -- meta
1186 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1187
1188 -- constraints
1189 unique(pull_at, round_number),
1190 foreign key (pull_at) references pulls(at_uri) on delete cascade
1191 );
1192 `)
1193 if err != nil {
1194 return err
1195 }
1196
1197 // transfer data, constructing pull_at from pulls table
1198 _, err = tx.Exec(`
1199 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1200 select
1201 ps.id,
1202 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1203 ps.round_number,
1204 ps.patch,
1205 ps.created
1206 from pull_submissions ps
1207 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1208 `)
1209 if err != nil {
1210 return err
1211 }
1212
1213 // drop old table
1214 _, err = tx.Exec(`drop table pull_submissions`)
1215 if err != nil {
1216 return err
1217 }
1218
1219 // rename new table
1220 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1221 return err
1222 })
1223 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1224
1225 // knots may report the combined patch for a comparison, we can store that on the appview side
1226 // (but not on the pds record), because calculating the combined patch requires a git index
1227 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1228 _, err := tx.Exec(`
1229 alter table pull_submissions add column combined text;
1230 `)
1231 return err
1232 })
1233
1234 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1235 _, err := tx.Exec(`
1236 alter table profile add column pronouns text;
1237 `)
1238 return err
1239 })
1240
1241 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1242 _, err := tx.Exec(`
1243 alter table repos add column website text;
1244 alter table repos add column topics text;
1245 `)
1246 return err
1247 })
1248
1249 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1250 _, err := tx.Exec(`
1251 alter table notification_preferences add column user_mentioned integer not null default 1;
1252 `)
1253 return err
1254 })
1255
1256 // remove the foreign key constraints from stars.
1257 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1258 _, err := tx.Exec(`
1259 create table stars_new (
1260 id integer primary key autoincrement,
1261 did text not null,
1262 rkey text not null,
1263
1264 subject_at text not null,
1265
1266 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1267 unique(did, rkey),
1268 unique(did, subject_at)
1269 );
1270
1271 insert into stars_new (
1272 id,
1273 did,
1274 rkey,
1275 subject_at,
1276 created
1277 )
1278 select
1279 id,
1280 starred_by_did,
1281 rkey,
1282 repo_at,
1283 created
1284 from stars;
1285
1286 drop table stars;
1287 alter table stars_new rename to stars;
1288
1289 create index if not exists idx_stars_created on stars(created);
1290 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1291 `)
1292 return err
1293 })
1294
1295 orm.RunMigration(conn, logger, "add-avatar-to-profile", func(tx *sql.Tx) error {
1296 _, err := tx.Exec(`
1297 alter table profile add column avatar text;
1298 `)
1299 return err
1300 })
1301
1302 orm.RunMigration(conn, logger, "remove-profile-stats-column-constraint", func(tx *sql.Tx) error {
1303 _, err := tx.Exec(`
1304 -- create new table without the check constraint
1305 create table profile_stats_new (
1306 id integer primary key autoincrement,
1307 did text not null,
1308 kind text not null, -- no constraint this time
1309 foreign key (did) references profile(did) on delete cascade
1310 );
1311
1312 -- copy data from old table
1313 insert into profile_stats_new (id, did, kind)
1314 select id, did, kind
1315 from profile_stats;
1316
1317 -- drop old table
1318 drop table profile_stats;
1319
1320 -- rename new table
1321 alter table profile_stats_new rename to profile_stats;
1322 `)
1323 return err
1324 })
1325
1326 orm.RunMigration(conn, logger, "add-preferred-handle-profile", func(tx *sql.Tx) error {
1327 _, err := tx.Exec(`
1328 alter table profile add column preferred_handle text;
1329 `)
1330 return err
1331 })
1332
1333 orm.RunMigration(conn, logger, "add-repo-did-column", func(tx *sql.Tx) error {
1334 _, err := tx.Exec(`
1335 alter table repos add column repo_did text;
1336 create unique index if not exists idx_repos_repo_did on repos(repo_did);
1337 `)
1338 return err
1339 })
1340
1341 orm.RunMigration(conn, logger, "add-pds-rewrite-status", func(tx *sql.Tx) error {
1342 _, err := tx.Exec(`
1343 create table if not exists pds_rewrite_status (
1344 id integer primary key autoincrement,
1345 user_did text not null,
1346 repo_did text not null,
1347 record_nsid text not null,
1348 record_rkey text not null,
1349 old_repo_at text not null,
1350 status text not null default 'pending',
1351 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1352 unique(user_did, record_nsid, record_rkey)
1353 );
1354 create index if not exists idx_pds_rewrite_user on pds_rewrite_status(user_did, status);
1355 `)
1356 return err
1357 })
1358
1359 orm.RunMigration(conn, logger, "add-pipelines-repo-did", func(tx *sql.Tx) error {
1360 _, err := tx.Exec(`
1361 alter table pipelines add column repo_did text;
1362 create index if not exists idx_pipelines_repo_did on pipelines(repo_did);
1363 `)
1364 return err
1365 })
1366
1367 orm.RunMigration(conn, logger, "migrate-knots-to-repo-dids", func(tx *sql.Tx) error {
1368 _, err := tx.Exec(`update registrations set needs_upgrade = 1`)
1369 return err
1370 })
1371
1372 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1373 orm.RunMigration(conn, logger, "drop-pinned-repos-at-uri-fk", func(tx *sql.Tx) error {
1374 _, err := tx.Exec(`
1375 create table if not exists profile_pinned_repositories_new (
1376 id integer primary key autoincrement,
1377 did text not null,
1378 pin text not null,
1379
1380 unique(did, pin),
1381 foreign key (did) references profile(did) on delete cascade
1382 );
1383
1384 insert into profile_pinned_repositories_new (id, did, pin)
1385 select id, did, at_uri from profile_pinned_repositories;
1386
1387 drop table profile_pinned_repositories;
1388
1389 alter table profile_pinned_repositories_new rename to profile_pinned_repositories;
1390 `)
1391 return err
1392 })
1393 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1394
1395 orm.RunMigration(conn, logger, "reset-profile-pin-rewrites", func(tx *sql.Tx) error {
1396 _, err := tx.Exec(`
1397 update pds_rewrite_status
1398 set status = 'pending',
1399 updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1400 where record_nsid = 'sh.tangled.actor.profile'
1401 and status = 'done'
1402 `)
1403 return err
1404 })
1405
1406 orm.RunMigration(conn, logger, "add-blob-data-to-pull-submissions", func(tx *sql.Tx) error {
1407 _, err := tx.Exec(`
1408 alter table pull_submissions add column patch_blob_ref text;
1409 alter table pull_submissions add column patch_blob_mime text;
1410 alter table pull_submissions add column patch_blob_size integer;
1411 `)
1412 return err
1413 })
1414
1415 orm.RunMigration(conn, logger, "replace-parent-change-id-with-aturi", func(tx *sql.Tx) error {
1416 // add new column
1417 _, err := tx.Exec(`
1418 alter table pulls add column dependent_on text;
1419 `)
1420 if err != nil {
1421 return err
1422 }
1423
1424 // populate dependent_on with at_uri of the parent
1425 _, err = tx.Exec(`
1426 update pulls
1427 set dependent_on = (
1428 select at_uri
1429 from pulls as parent
1430 where parent.stack_id = pulls.stack_id
1431 and parent.change_id = pulls.parent_change_id
1432 )
1433 where parent_change_id is not null;
1434 `)
1435 if err != nil {
1436 return err
1437 }
1438
1439 // drop old columns
1440 _, err = tx.Exec(`
1441 alter table pulls drop column parent_change_id;
1442 alter table pulls drop column stack_id;
1443 `)
1444
1445 return err
1446 })
1447
1448 orm.RunMigration(conn, logger, "add-pds-migration", func(tx *sql.Tx) error {
1449 _, err := tx.Exec(`
1450 create table if not exists pds_migration (
1451 name text not null,
1452
1453 -- record at_uri
1454 did text not null,
1455 collection text not null,
1456 rkey text not null,
1457
1458 status text not null default 'pending',
1459 error_msg text,
1460 retry_count integer not null default 0,
1461 retry_after integer not null default 0,
1462 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1463
1464 unique(name, did, collection, rkey)
1465 );
1466 `)
1467 return err
1468 })
1469
1470 orm.RunMigration(conn, logger, "unify-pds-record-migration-table", func(tx *sql.Tx) error {
1471 _, err := tx.Exec(`
1472 insert into pds_migration (
1473 name,
1474 did,
1475 collection,
1476 rkey,
1477 status,
1478 updated_at
1479 )
1480 select
1481 'add-repo-did',
1482 user_did,
1483 record_nsid,
1484 record_rkey,
1485 status,
1486 updated_at
1487 from pds_rewrite_status;
1488
1489 drop table pds_rewrite_status;
1490 `)
1491 return err
1492 })
1493
1494 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1495 orm.RunMigration(conn, logger, "add-id-to-vouches", func(tx *sql.Tx) error {
1496 _, err := tx.Exec(`
1497 create table vouches_new (
1498 id integer primary key autoincrement,
1499 did text not null,
1500 subject_did text not null,
1501 cid text not null,
1502 kind text not null default 'vouch',
1503 reason text,
1504 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1505 unique(did, subject_did),
1506 check (did <> subject_did),
1507 check (kind in ('vouch', 'denounce'))
1508 );
1509
1510 insert into vouches_new (did, subject_did, cid, kind, reason, created_at)
1511 select did, subject_did, cid, kind, reason, created_at
1512 from vouches;
1513
1514 drop table vouches;
1515 alter table vouches_new rename to vouches;
1516 `)
1517 return err
1518 })
1519 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1520
1521 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1522 orm.RunMigration(conn, logger, "drop-pipeline-statuses-pipeline-fk", func(tx *sql.Tx) error {
1523 _, err := tx.Exec(`
1524 create table if not exists pipeline_statuses_new (
1525 id integer primary key autoincrement,
1526 spindle text not null,
1527 rkey text not null,
1528
1529 pipeline_knot text not null,
1530 pipeline_rkey text not null,
1531
1532 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1533 workflow text not null,
1534 status text not null,
1535 error text,
1536 exit_code integer not null default 0,
1537
1538 unique (spindle, rkey)
1539 );
1540
1541 insert into pipeline_statuses_new
1542 select * from pipeline_statuses;
1543
1544 drop table pipeline_statuses;
1545 alter table pipeline_statuses_new rename to pipeline_statuses;
1546 `)
1547 return err
1548 })
1549 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1550
1551 orm.RunMigration(conn, logger, "add-repo-renames", func(tx *sql.Tx) error {
1552 res, err := tx.Exec(`
1553 update repos
1554 set name = name || '-renamed-' || id || '-' || lower(hex(randomblob(4)))
1555 where id in (
1556 select id from (
1557 select id, row_number() over (
1558 partition by did, knot, name
1559 order by created desc, id desc
1560 ) as rn
1561 from repos
1562 ) where rn > 1
1563 );
1564 `)
1565 if err != nil {
1566 return err
1567 }
1568 if n, _ := res.RowsAffected(); n > 0 {
1569 logger.Warn("suffixed legacy duplicate repo names before adding unique index", "rows", n)
1570 }
1571
1572 var remaining int
1573 if err := tx.QueryRow(`
1574 select count(*) from (
1575 select 1 from repos group by did, knot, name having count(*) > 1
1576 )
1577 `).Scan(&remaining); err != nil {
1578 return fmt.Errorf("checking for residual duplicate (did, knot, name) groups: %w", err)
1579 }
1580 if remaining > 0 {
1581 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)
1582 }
1583
1584 _, err = tx.Exec(`
1585 create table if not exists repo_renames (
1586 owner_did text not null,
1587 old_rkey text not null,
1588 repo_did text not null,
1589 renamed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1590 primary key (owner_did, old_rkey)
1591 );
1592 create unique index if not exists idx_repos_owner_knot_name
1593 on repos(did, knot, name);
1594 `)
1595 return err
1596 })
1597
1598 orm.RunMigration(conn, logger, "repos-canonical-rkey-uniqueness", func(tx *sql.Tx) error {
1599 _, err := tx.Exec(`
1600 drop index if exists idx_repos_owner_knot_name;
1601 create unique index if not exists idx_repos_did_rkey
1602 on repos(did, rkey);
1603 `)
1604 return err
1605 })
1606
1607 orm.RunMigration(conn, logger, "repo-did-references", func(tx *sql.Tx) error {
1608 tables := []struct{ table, oldCol, newCol string }{
1609 {"issues", "repo_at", "repo_did"},
1610 {"pulls", "repo_at", "repo_did"},
1611 {"pull_comments", "repo_at", "repo_did"},
1612 {"stars", "subject_at", "subject_did"},
1613 {"artifacts", "repo_at", "repo_did"},
1614 {"webhooks", "repo_at", "repo_did"},
1615 {"repo_sites", "repo_at", "repo_did"},
1616 {"site_deploys", "repo_at", "repo_did"},
1617 {"collaborators", "repo_at", "repo_did"},
1618 {"repo_issue_seqs", "repo_at", "repo_did"},
1619 {"repo_pull_seqs", "repo_at", "repo_did"},
1620 {"repo_languages", "repo_at", "repo_did"},
1621 {"repo_labels", "repo_at", "repo_did"},
1622 }
1623
1624 stmts := ""
1625 for _, t := range tables {
1626 stmts += fmt.Sprintf(
1627 `ALTER TABLE %s ADD COLUMN %s TEXT;
1628 UPDATE %s SET %s = (SELECT repos.repo_did FROM repos WHERE repos.at_uri = %s.%s);
1629 CREATE INDEX IF NOT EXISTS idx_%s_%s ON %s(%s);
1630 `, t.table, t.newCol, t.table, t.newCol, t.table, t.oldCol, t.table, t.newCol, t.table, t.newCol)
1631 }
1632
1633 stmts += `ALTER TABLE pulls ADD COLUMN source_repo_did TEXT;
1634 UPDATE pulls SET source_repo_did = (SELECT repos.repo_did FROM repos WHERE repos.at_uri = pulls.source_repo_at);
1635
1636 UPDATE profile_pinned_repositories SET pin = (
1637 SELECT repos.repo_did FROM repos WHERE repos.at_uri = profile_pinned_repositories.pin
1638 ) WHERE pin LIKE 'at://%'
1639 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 != '');
1640 `
1641
1642 _, err := tx.Exec(stmts)
1643 return err
1644 })
1645
1646 orm.RunMigration(conn, logger, "backfill-pds-rewrites-star-issue-pull-collab", func(tx *sql.Tx) error {
1647 type source struct {
1648 userDidCol string
1649 table string
1650 nsid string
1651 fkCol string
1652 }
1653 sources := []source{
1654 {"did", "stars", "sh.tangled.feed.star", "subject_at"},
1655 {"did", "issues", "sh.tangled.repo.issue", "repo_at"},
1656 {"owner_did", "pulls", "sh.tangled.repo.pull", "repo_at"},
1657 {"did", "collaborators", "sh.tangled.repo.collaborator", "repo_at"},
1658 }
1659
1660 for _, src := range sources {
1661 _, err := tx.Exec(fmt.Sprintf(`
1662 INSERT INTO pds_migration (name, did, collection, rkey, status)
1663 SELECT 'add-repo-did', t.%s, '%s', t.rkey, 'pending'
1664 FROM %s t
1665 JOIN repos r ON r.at_uri = t.%s
1666 WHERE r.repo_did IS NOT NULL AND r.repo_did != ''
1667 ON CONFLICT(name, did, collection, rkey) DO NOTHING
1668 `, src.userDidCol, src.nsid, src.table, src.fkCol))
1669 if err != nil {
1670 return fmt.Errorf("backfill pds rewrites for %s: %w", src.table, err)
1671 }
1672 }
1673
1674 return nil
1675 })
1676
1677 orm.RunMigration(conn, logger, "backfill-pds-rewrites-profiles", func(tx *sql.Tx) error {
1678 _, err := tx.Exec(`
1679 INSERT INTO pds_migration (name, did, collection, rkey, status)
1680 SELECT DISTINCT 'add-repo-did', pp.did, 'sh.tangled.actor.profile', 'self', 'pending'
1681 FROM profile_pinned_repositories pp
1682 JOIN repos r ON r.at_uri = pp.pin
1683 WHERE pp.pin LIKE 'at://%'
1684 AND r.repo_did IS NOT NULL AND r.repo_did != ''
1685 ON CONFLICT(name, did, collection, rkey) DO NOTHING
1686 `)
1687 if err != nil {
1688 return fmt.Errorf("backfill pds rewrites for profiles: %w", err)
1689 }
1690 return nil
1691 })
1692
1693 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1694 orm.RunMigration(conn, logger, "drop-old-at-uri-columns", func(tx *sql.Tx) error {
1695 _, err := tx.Exec(`
1696 CREATE TABLE repos_new (
1697 id INTEGER PRIMARY KEY AUTOINCREMENT,
1698 did TEXT NOT NULL,
1699 name TEXT NOT NULL,
1700 knot TEXT NOT NULL,
1701 rkey TEXT NOT NULL,
1702 at_uri TEXT NOT NULL UNIQUE,
1703 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1704 description TEXT CHECK (length(description) <= 200),
1705 source TEXT,
1706 spindle TEXT,
1707 website TEXT,
1708 topics TEXT,
1709 repo_did TEXT,
1710 UNIQUE(did, rkey)
1711 );
1712 INSERT INTO repos_new (id, did, name, knot, rkey, at_uri, created, description, source, spindle, website, topics, repo_did)
1713 SELECT id, did, name, knot, rkey, at_uri, created, description, source, spindle, website, topics, repo_did
1714 FROM repos;
1715 DROP TABLE repos;
1716 ALTER TABLE repos_new RENAME TO repos;
1717 CREATE UNIQUE INDEX idx_repos_repo_did ON repos(repo_did);
1718 CREATE UNIQUE INDEX idx_repos_did_rkey ON repos(did, rkey);
1719
1720 CREATE TABLE issues_new (
1721 id INTEGER PRIMARY KEY AUTOINCREMENT,
1722 did TEXT NOT NULL,
1723 rkey TEXT NOT NULL,
1724 at_uri TEXT GENERATED ALWAYS AS ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) STORED,
1725 repo_did TEXT NOT NULL,
1726 issue_id INTEGER NOT NULL,
1727 title TEXT NOT NULL,
1728 body TEXT NOT NULL,
1729 open INTEGER NOT NULL DEFAULT 1,
1730 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1731 edited TEXT,
1732 deleted TEXT,
1733 UNIQUE(did, rkey),
1734 UNIQUE(repo_did, issue_id),
1735 UNIQUE(at_uri),
1736 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1737 );
1738 INSERT INTO issues_new (id, did, rkey, repo_did, issue_id, title, body, open, created, edited, deleted)
1739 SELECT id, did, rkey, repo_did, issue_id, title, body, open, created, edited, deleted
1740 FROM issues WHERE repo_did IS NOT NULL AND repo_did != '';
1741 DROP TABLE issues;
1742 ALTER TABLE issues_new RENAME TO issues;
1743 CREATE INDEX idx_issues_repo_did ON issues(repo_did);
1744
1745 CREATE TABLE pulls_new (
1746 id INTEGER PRIMARY KEY AUTOINCREMENT,
1747 pull_id INTEGER NOT NULL,
1748 at_uri TEXT GENERATED ALWAYS AS ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) STORED,
1749 repo_did TEXT NOT NULL,
1750 owner_did TEXT NOT NULL,
1751 rkey TEXT NOT NULL,
1752 title TEXT NOT NULL,
1753 body TEXT NOT NULL,
1754 target_branch TEXT NOT NULL,
1755 state INTEGER NOT NULL DEFAULT 0 CHECK (state IN (0, 1, 2, 3)),
1756 source_branch TEXT,
1757 source_repo_did TEXT,
1758 change_id TEXT,
1759 dependent_on TEXT,
1760 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1761 UNIQUE(repo_did, pull_id),
1762 UNIQUE(at_uri),
1763 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1764 );
1765 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)
1766 SELECT id, pull_id, repo_did, owner_did, rkey, title, body, target_branch, state, source_branch, source_repo_did, change_id, dependent_on, created
1767 FROM pulls WHERE repo_did IS NOT NULL AND repo_did != '';
1768 DROP TABLE pulls;
1769 ALTER TABLE pulls_new RENAME TO pulls;
1770 CREATE INDEX idx_pulls_repo_did ON pulls(repo_did);
1771 CREATE INDEX idx_pulls_source_repo_did ON pulls(source_repo_did);
1772
1773 CREATE TABLE pull_comments_new (
1774 id INTEGER PRIMARY KEY AUTOINCREMENT,
1775 pull_id INTEGER NOT NULL,
1776 submission_id INTEGER NOT NULL,
1777 repo_did TEXT NOT NULL,
1778 owner_did TEXT NOT NULL,
1779 comment_at TEXT NOT NULL,
1780 body TEXT NOT NULL,
1781 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1782 FOREIGN KEY (repo_did, pull_id) REFERENCES pulls(repo_did, pull_id) ON DELETE CASCADE,
1783 FOREIGN KEY (submission_id) REFERENCES pull_submissions(id) ON DELETE CASCADE
1784 );
1785 INSERT INTO pull_comments_new (id, pull_id, submission_id, repo_did, owner_did, comment_at, body, created)
1786 SELECT id, pull_id, submission_id, repo_did, owner_did, comment_at, body, created
1787 FROM pull_comments WHERE repo_did IS NOT NULL AND repo_did != '';
1788 DROP TABLE pull_comments;
1789 ALTER TABLE pull_comments_new RENAME TO pull_comments;
1790 CREATE INDEX idx_pull_comments_repo_did ON pull_comments(repo_did);
1791
1792 CREATE TABLE stars_new (
1793 id INTEGER PRIMARY KEY AUTOINCREMENT,
1794 did TEXT NOT NULL,
1795 rkey TEXT NOT NULL,
1796 subject_type TEXT NOT NULL CHECK (subject_type IN ('repo', 'string')),
1797 subject TEXT NOT NULL,
1798 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1799 UNIQUE(did, rkey),
1800 UNIQUE(did, subject)
1801 );
1802 INSERT INTO stars_new (id, did, rkey, subject_type, subject, created)
1803 SELECT id, did, rkey, 'repo', subject_did, created
1804 FROM stars
1805 WHERE subject_did IS NOT NULL AND subject_did != '';
1806 INSERT OR IGNORE INTO stars_new (id, did, rkey, subject_type, subject, created)
1807 SELECT id, did, rkey, 'string', subject_at, created
1808 FROM stars
1809 WHERE (subject_did IS NULL OR subject_did = '')
1810 AND subject_at LIKE 'at://%/sh.tangled.string/%';
1811 DROP TABLE stars;
1812 ALTER TABLE stars_new RENAME TO stars;
1813 CREATE INDEX idx_stars_subject ON stars(subject);
1814 CREATE INDEX idx_stars_subject_type ON stars(subject_type);
1815 CREATE INDEX idx_stars_created ON stars(created);
1816
1817 CREATE TABLE collaborators_new (
1818 id INTEGER PRIMARY KEY AUTOINCREMENT,
1819 did TEXT NOT NULL,
1820 rkey TEXT,
1821 subject_did TEXT NOT NULL,
1822 repo_did TEXT NOT NULL,
1823 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1824 UNIQUE(did, rkey),
1825 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1826 );
1827 INSERT INTO collaborators_new (id, did, rkey, subject_did, repo_did, created)
1828 SELECT id, did, NULLIF(rkey, ''), subject_did, repo_did, created
1829 FROM collaborators WHERE repo_did IS NOT NULL AND repo_did != '';
1830 DROP TABLE collaborators;
1831 ALTER TABLE collaborators_new RENAME TO collaborators;
1832 CREATE INDEX idx_collaborators_repo_did ON collaborators(repo_did);
1833
1834 CREATE TABLE artifacts_new (
1835 id INTEGER PRIMARY KEY AUTOINCREMENT,
1836 did TEXT NOT NULL,
1837 rkey TEXT NOT NULL,
1838 repo_did TEXT NOT NULL,
1839 tag BINARY(20) NOT NULL,
1840 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1841 blob_cid TEXT NOT NULL,
1842 name TEXT NOT NULL,
1843 size INTEGER NOT NULL DEFAULT 0,
1844 mimetype TEXT NOT NULL DEFAULT '*/*',
1845 UNIQUE(did, rkey),
1846 UNIQUE(repo_did, tag, name),
1847 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1848 );
1849 INSERT INTO artifacts_new (id, did, rkey, repo_did, tag, created, blob_cid, name, size, mimetype)
1850 SELECT id, did, rkey, repo_did, tag, created, blob_cid, name, size, mimetype
1851 FROM artifacts WHERE repo_did IS NOT NULL AND repo_did != '';
1852 DROP TABLE artifacts;
1853 ALTER TABLE artifacts_new RENAME TO artifacts;
1854 CREATE INDEX idx_artifacts_repo_did ON artifacts(repo_did);
1855
1856 CREATE TABLE webhooks_new (
1857 id INTEGER PRIMARY KEY AUTOINCREMENT,
1858 repo_did TEXT NOT NULL,
1859 url TEXT NOT NULL,
1860 secret TEXT,
1861 active INTEGER NOT NULL DEFAULT 1,
1862 events TEXT NOT NULL,
1863 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1864 updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1865 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1866 );
1867 INSERT INTO webhooks_new (id, repo_did, url, secret, active, events, created_at, updated_at)
1868 SELECT id, repo_did, url, secret, active, events, created_at, updated_at
1869 FROM webhooks WHERE repo_did IS NOT NULL AND repo_did != '';
1870 DROP TABLE webhooks;
1871 ALTER TABLE webhooks_new RENAME TO webhooks;
1872 CREATE INDEX idx_webhooks_repo_did ON webhooks(repo_did);
1873
1874 CREATE TABLE repo_sites_new (
1875 id INTEGER PRIMARY KEY AUTOINCREMENT,
1876 repo_did TEXT NOT NULL UNIQUE,
1877 branch TEXT NOT NULL,
1878 dir TEXT NOT NULL DEFAULT '/',
1879 is_index INTEGER NOT NULL DEFAULT 0,
1880 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1881 updated TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1882 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1883 );
1884 INSERT INTO repo_sites_new (id, repo_did, branch, dir, is_index, created, updated)
1885 SELECT id, repo_did, branch, dir, is_index, created, updated
1886 FROM repo_sites WHERE repo_did IS NOT NULL AND repo_did != '';
1887 DROP TABLE repo_sites;
1888 ALTER TABLE repo_sites_new RENAME TO repo_sites;
1889
1890 CREATE TABLE site_deploys_new (
1891 id INTEGER PRIMARY KEY AUTOINCREMENT,
1892 repo_did TEXT NOT NULL,
1893 branch TEXT NOT NULL,
1894 dir TEXT NOT NULL DEFAULT '/',
1895 commit_sha TEXT NOT NULL DEFAULT '',
1896 status TEXT NOT NULL CHECK (status IN ('success', 'failure')),
1897 trigger TEXT NOT NULL CHECK (trigger IN ('config_change', 'push')),
1898 error TEXT NOT NULL DEFAULT '',
1899 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1900 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1901 );
1902 INSERT INTO site_deploys_new (id, repo_did, branch, dir, commit_sha, status, trigger, error, created_at)
1903 SELECT id, repo_did, branch, dir, commit_sha, status, trigger, error, created_at
1904 FROM site_deploys WHERE repo_did IS NOT NULL AND repo_did != '';
1905 DROP TABLE site_deploys;
1906 ALTER TABLE site_deploys_new RENAME TO site_deploys;
1907 CREATE INDEX idx_site_deploys_repo_did ON site_deploys(repo_did);
1908
1909 CREATE TABLE repo_issue_seqs_new (
1910 repo_did TEXT PRIMARY KEY,
1911 next_issue_id INTEGER NOT NULL DEFAULT 1,
1912 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1913 );
1914 INSERT INTO repo_issue_seqs_new (repo_did, next_issue_id)
1915 SELECT repo_did, next_issue_id
1916 FROM repo_issue_seqs WHERE repo_did IS NOT NULL AND repo_did != '';
1917 DROP TABLE repo_issue_seqs;
1918 ALTER TABLE repo_issue_seqs_new RENAME TO repo_issue_seqs;
1919
1920 CREATE TABLE repo_pull_seqs_new (
1921 repo_did TEXT PRIMARY KEY,
1922 next_pull_id INTEGER NOT NULL DEFAULT 1,
1923 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1924 );
1925 INSERT INTO repo_pull_seqs_new (repo_did, next_pull_id)
1926 SELECT repo_did, next_pull_id
1927 FROM repo_pull_seqs WHERE repo_did IS NOT NULL AND repo_did != '';
1928 DROP TABLE repo_pull_seqs;
1929 ALTER TABLE repo_pull_seqs_new RENAME TO repo_pull_seqs;
1930
1931 CREATE TABLE repo_languages_new (
1932 id INTEGER PRIMARY KEY AUTOINCREMENT,
1933 repo_did TEXT NOT NULL,
1934 ref TEXT NOT NULL,
1935 is_default_ref INTEGER NOT NULL DEFAULT 0,
1936 language TEXT NOT NULL,
1937 bytes INTEGER NOT NULL CHECK (bytes >= 0),
1938 UNIQUE(repo_did, ref, language),
1939 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1940 );
1941 INSERT INTO repo_languages_new (id, repo_did, ref, is_default_ref, language, bytes)
1942 SELECT id, repo_did, ref, is_default_ref, language, bytes
1943 FROM repo_languages WHERE repo_did IS NOT NULL AND repo_did != '';
1944 DROP TABLE repo_languages;
1945 ALTER TABLE repo_languages_new RENAME TO repo_languages;
1946
1947 CREATE TABLE repo_labels_new (
1948 id INTEGER PRIMARY KEY AUTOINCREMENT,
1949 repo_did TEXT NOT NULL,
1950 label_at TEXT NOT NULL,
1951 UNIQUE(repo_did, label_at),
1952 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1953 );
1954 INSERT INTO repo_labels_new (id, repo_did, label_at)
1955 SELECT id, repo_did, label_at
1956 FROM repo_labels WHERE repo_did IS NOT NULL AND repo_did != '';
1957 DROP TABLE repo_labels;
1958 ALTER TABLE repo_labels_new RENAME TO repo_labels;
1959 `)
1960 return err
1961 })
1962 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1963
1964 orm.RunMigration(conn, logger, "migrate-knots-to-repo-did-rename", func(tx *sql.Tx) error {
1965 _, err := tx.Exec(`
1966 update registrations set needs_upgrade = 1;
1967 `)
1968 return err
1969 })
1970
1971 orm.RunMigration(conn, logger, "drop-ghost-comments-table", func(tx *sql.Tx) error {
1972 _, err := tx.Exec(`DROP TABLE IF EXISTS comments`)
1973 return err
1974 })
1975
1976 orm.RunMigration(conn, logger, "add-knot-members-table", func(tx *sql.Tx) error {
1977 _, err := tx.Exec(`
1978 create table if not exists knot_members (
1979 id integer primary key autoincrement,
1980 did text not null,
1981 rkey text not null,
1982 domain text not null,
1983 subject text not null,
1984 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1985 unique (did, domain, subject)
1986 );
1987 create index if not exists idx_knot_members_did_rkey on knot_members(did, rkey);
1988 `)
1989 return err
1990 })
1991
1992 orm.RunMigration(conn, logger, "add-comments-table", func(tx *sql.Tx) error {
1993 _, err := tx.Exec(`
1994 drop table if exists comments;
1995
1996 create table comments (
1997 -- identifiers
1998 id integer primary key autoincrement,
1999
2000 did text not null,
2001 collection text not null default 'sh.tangled.feed.comment',
2002 rkey text not null,
2003 at_uri text generated always as ('at://' || did || '/' || collection || '/' || rkey) stored,
2004 cid text,
2005
2006 -- content
2007 subject_uri text not null, -- at_uri of subject (issue, pr, string)
2008 subject_cid text not null, -- cid of subject
2009
2010 body_text text not null,
2011 body_original text,
2012 body_blobs text, -- json
2013
2014 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2015
2016 reply_to_uri text, -- at_uri of parent comment
2017 reply_to_cid text, -- cid of parent comment
2018
2019 pull_round_idx integer, -- pull round index. required when subject is sh.tangled.repo.pull
2020
2021 -- appview-local information
2022 edited text,
2023 deleted text,
2024
2025 unique(did, collection, rkey)
2026 );
2027
2028 insert into comments (
2029 did,
2030 collection,
2031 rkey,
2032 subject_uri,
2033 subject_cid, -- we need to know cid
2034 body_text,
2035 created,
2036 reply_to_uri,
2037 reply_to_cid, -- we need to know cid
2038 edited,
2039 deleted
2040 )
2041 select
2042 did,
2043 'sh.tangled.repo.issue.comment',
2044 rkey,
2045 issue_at,
2046 '',
2047 body,
2048 created,
2049 reply_to,
2050 '',
2051 edited,
2052 deleted
2053 from issue_comments
2054 where rkey is not null;
2055
2056 insert into comments (
2057 did,
2058 collection,
2059 rkey,
2060 subject_uri,
2061 subject_cid, -- we need to know cid
2062 body_text,
2063 created,
2064 pull_round_idx
2065 )
2066 select
2067 c.owner_did,
2068 'sh.tangled.repo.pull.comment',
2069 substr(
2070 substr(c.comment_at, 6 + instr(substr(c.comment_at, 6), '/')), -- nsid/rkey
2071 instr(
2072 substr(c.comment_at, 6 + instr(substr(c.comment_at, 6), '/')), -- nsid/rkey
2073 '/'
2074 ) + 1
2075 ), -- rkey
2076 p.at_uri,
2077 '',
2078 c.body,
2079 c.created,
2080 s.round_number
2081 from pull_comments c
2082 join pulls p on c.repo_did = p.repo_did and c.pull_id = p.pull_id
2083 join pull_submissions s on s.id = c.submission_id;
2084 `)
2085 return err
2086 })
2087
2088 orm.RunMigration(conn, logger, "migrate-legacy-comments", func(tx *sql.Tx) error {
2089 _, err := tx.Exec(`
2090 insert into pds_migration (name, did, collection, rkey)
2091 select
2092 'use-feed-comment',
2093 did,
2094 collection,
2095 rkey
2096 from comments
2097 where collection <> 'sh.tangled.feed.comment';
2098 `)
2099 return err
2100 })
2101
2102 conn.ExecContext(ctx, "pragma foreign_keys = off;")
2103 orm.RunMigration(conn, logger, "cascade-notification-entity-fks", func(tx *sql.Tx) error {
2104 _, err := tx.Exec(`
2105 CREATE TABLE notifications_new (
2106 id INTEGER PRIMARY KEY AUTOINCREMENT,
2107 recipient_did TEXT NOT NULL,
2108 actor_did TEXT NOT NULL,
2109 type TEXT NOT NULL,
2110 entity_type TEXT NOT NULL,
2111 entity_id TEXT NOT NULL,
2112 read INTEGER NOT NULL DEFAULT 0,
2113 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2114 repo_id INTEGER REFERENCES repos(id) ON DELETE CASCADE,
2115 issue_id INTEGER REFERENCES issues(id) ON DELETE CASCADE,
2116 pull_id INTEGER REFERENCES pulls(id) ON DELETE CASCADE
2117 );
2118 INSERT INTO notifications_new (id, recipient_did, actor_did, type, entity_type, entity_id, read, created, repo_id, issue_id, pull_id)
2119 SELECT id, recipient_did, actor_did, type, entity_type, entity_id, read, created, repo_id, issue_id, pull_id
2120 FROM notifications;
2121 DROP TABLE notifications;
2122 ALTER TABLE notifications_new RENAME TO notifications;
2123 CREATE INDEX idx_notifications_recipient_created ON notifications(recipient_did, created DESC);
2124 CREATE INDEX idx_notifications_recipient_read ON notifications(recipient_did, read);
2125 `)
2126 return err
2127 })
2128 conn.ExecContext(ctx, "pragma foreign_keys = on;")
2129
2130 orm.RunMigration(conn, logger, "collaborators-unique-on-repo-subject", func(tx *sql.Tx) error {
2131 _, err := tx.Exec(`
2132 CREATE TABLE collaborators_new (
2133 id INTEGER PRIMARY KEY AUTOINCREMENT,
2134 did TEXT NOT NULL,
2135 rkey TEXT,
2136 subject_did TEXT NOT NULL,
2137 repo_did TEXT NOT NULL,
2138 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2139 UNIQUE(repo_did, subject_did),
2140 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
2141 );
2142 INSERT INTO collaborators_new (id, did, rkey, subject_did, repo_did, created)
2143 SELECT id, did, rkey, subject_did, repo_did, created
2144 FROM (
2145 SELECT
2146 id, did, rkey, subject_did, repo_did, created,
2147 ROW_NUMBER() OVER (
2148 PARTITION BY repo_did, subject_did
2149 ORDER BY created DESC, id DESC
2150 ) AS rn
2151 FROM collaborators
2152 )
2153 WHERE rn = 1;
2154 DROP TABLE collaborators;
2155 ALTER TABLE collaborators_new RENAME TO collaborators;
2156 CREATE INDEX idx_collaborators_repo_did ON collaborators(repo_did);
2157 CREATE INDEX idx_collaborators_subject_did ON collaborators(subject_did);
2158 `)
2159 return err
2160 })
2161 return &DB{
2162 db,
2163 logger,
2164 }, nil
2165}
2166
2167func (d *DB) Close() error {
2168 return d.DB.Close()
2169}