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