alice.pds.demo.boltless.dev
This repository has no description
1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log/slog"
8 "strings"
9
10 _ "github.com/mattn/go-sqlite3"
11 "tangled.org/core/log"
12 "tangled.org/core/orm"
13)
14
15type DB struct {
16 *sql.DB
17 logger *slog.Logger
18}
19
20type Execer interface {
21 Query(query string, args ...any) (*sql.Rows, error)
22 QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
23 QueryRow(query string, args ...any) *sql.Row
24 QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
25 Exec(query string, args ...any) (sql.Result, error)
26 ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
27 Prepare(query string) (*sql.Stmt, error)
28 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
29}
30
31func Make(ctx context.Context, dbPath string) (*DB, error) {
32 // https://github.com/mattn/go-sqlite3#connection-string
33 opts := []string{
34 "_foreign_keys=1",
35 "_journal_mode=WAL",
36 "_synchronous=NORMAL",
37 "_auto_vacuum=incremental",
38 "_busy_timeout=5000",
39 }
40
41 logger := log.FromContext(ctx)
42 logger = log.SubLogger(logger, "db")
43
44 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
45 if err != nil {
46 return nil, err
47 }
48
49 conn, err := db.Conn(ctx)
50 if err != nil {
51 return nil, err
52 }
53 defer conn.Close()
54
55 _, err = conn.ExecContext(ctx, `
56 create table if not exists registrations (
57 id integer primary key autoincrement,
58 domain text not null unique,
59 did text not null,
60 secret text not null,
61 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
62 registered text
63 );
64 create table if not exists public_keys (
65 id integer primary key autoincrement,
66 did text not null,
67 name text not null,
68 key text not null,
69 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
70 unique(did, name, key)
71 );
72 create table if not exists repos (
73 id integer primary key autoincrement,
74 did text not null,
75 name text not null,
76 knot text not null,
77 rkey text not null,
78 at_uri text not null unique,
79 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
80 unique(did, name, knot, rkey)
81 );
82 create table if not exists collaborators (
83 id integer primary key autoincrement,
84 did text not null,
85 repo integer not null,
86 foreign key (repo) references repos(id) on delete cascade
87 );
88 create table if not exists follows (
89 user_did text not null,
90 subject_did text not null,
91 rkey text not null,
92 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
93 primary key (user_did, subject_did),
94 check (user_did <> subject_did)
95 );
96 create table if not exists vouches (
97 did text not null,
98 subject_did text not null,
99 cid text not null,
100 kind text not null default 'vouch',
101 reason text,
102 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
103 primary key (did, subject_did),
104 check (did <> subject_did),
105 check (kind in ('vouch', 'denounce'))
106 );
107 create table if not exists issues (
108 id integer primary key autoincrement,
109 owner_did text not null,
110 repo_at text not null,
111 issue_id integer not null,
112 title text not null,
113 body text not null,
114 open integer not null default 1,
115 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
116 issue_at text,
117 unique(repo_at, issue_id),
118 foreign key (repo_at) references repos(at_uri) on delete cascade
119 );
120 create table if not exists 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 create table if not exists onboarding (
696 did text primary key,
697 step integer not null default 0,
698 status text not null default 'in_progress'
699 check (status in ('in_progress','completed','skipped')),
700 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
701 updated text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
702 );
703
704 create table if not exists issue_subscriptions (
705 user_did text not null,
706 issue_id integer not null references issues(id) on delete cascade,
707 subscribed integer not null default 1,
708 primary key(user_did, issue_id)
709 );
710
711 create table if not exists pull_subscriptions (
712 user_did text not null,
713 pull_id integer not null references pulls(id) on delete cascade,
714 subscribed integer not null default 1,
715 primary key(user_did, pull_id)
716 );
717
718 create table if not exists migrations (
719 id integer primary key autoincrement,
720 name text unique
721 );
722
723 -- indexes for better performance
724 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
725 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
726 create index if not exists idx_references_from_at on reference_links(from_at);
727 create index if not exists idx_references_to_at on reference_links(to_at);
728 create index if not exists idx_webhook_deliveries_webhook_id on webhook_deliveries(webhook_id);
729 create index if not exists idx_newsletter_prefs_user_did on newsletter_preferences(user_did);
730 create index if not exists idx_issue_subscriptions_issue on issue_subscriptions(issue_id, subscribed);
731 create index if not exists idx_pull_subscriptions_pull on pull_subscriptions(pull_id, subscribed);
732 `)
733 if err != nil {
734 return nil, err
735 }
736
737 // run migrations
738 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
739 tx.Exec(`
740 alter table repos add column description text check (length(description) <= 200);
741 `)
742 return nil
743 })
744
745 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
746 // add unconstrained column
747 _, err := tx.Exec(`
748 alter table public_keys
749 add column rkey text;
750 `)
751 if err != nil {
752 return err
753 }
754
755 // backfill
756 _, err = tx.Exec(`
757 update public_keys
758 set rkey = ''
759 where rkey is null;
760 `)
761 if err != nil {
762 return err
763 }
764
765 return nil
766 })
767
768 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
769 _, err := tx.Exec(`
770 alter table comments drop column comment_at;
771 alter table comments add column rkey text;
772 `)
773 return err
774 })
775
776 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
777 _, err := tx.Exec(`
778 alter table comments add column deleted text; -- timestamp
779 alter table comments add column edited text; -- timestamp
780 `)
781 return err
782 })
783
784 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
785 _, err := tx.Exec(`
786 alter table pulls add column source_branch text;
787 alter table pulls add column source_repo_at text;
788 alter table pull_submissions add column source_rev text;
789 `)
790 return err
791 })
792
793 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
794 _, err := tx.Exec(`
795 alter table repos add column source text;
796 `)
797 return err
798 })
799
800 // disable foreign-keys for the next migration
801 // NOTE: this cannot be done in a transaction, so it is run outside [0]
802 //
803 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
804 conn.ExecContext(ctx, "pragma foreign_keys = off;")
805 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
806 _, err := tx.Exec(`
807 create table pulls_new (
808 -- identifiers
809 id integer primary key autoincrement,
810 pull_id integer not null,
811
812 -- at identifiers
813 repo_at text not null,
814 owner_did text not null,
815 rkey text not null,
816
817 -- content
818 title text not null,
819 body text not null,
820 target_branch text not null,
821 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
822
823 -- source info
824 source_branch text,
825 source_repo_at text,
826
827 -- stacking
828 stack_id text,
829 change_id text,
830 parent_change_id text,
831
832 -- meta
833 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
834
835 -- constraints
836 unique(repo_at, pull_id),
837 foreign key (repo_at) references repos(at_uri) on delete cascade
838 );
839
840 insert into pulls_new (
841 id, pull_id,
842 repo_at, owner_did, rkey,
843 title, body, target_branch, state,
844 source_branch, source_repo_at,
845 created
846 )
847 select
848 id, pull_id,
849 repo_at, owner_did, rkey,
850 title, body, target_branch, state,
851 source_branch, source_repo_at,
852 created
853 FROM pulls;
854
855 drop table pulls;
856 alter table pulls_new rename to pulls;
857 `)
858 return err
859 })
860 conn.ExecContext(ctx, "pragma foreign_keys = on;")
861
862 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
863 tx.Exec(`
864 alter table repos add column spindle text;
865 `)
866 return nil
867 })
868
869 // drop all knot secrets, add unique constraint to knots
870 //
871 // knots will henceforth use service auth for signed requests
872 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
873 _, err := tx.Exec(`
874 create table registrations_new (
875 id integer primary key autoincrement,
876 domain text not null,
877 did text not null,
878 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
879 registered text,
880 read_only integer not null default 0,
881 unique(domain, did)
882 );
883
884 insert into registrations_new (id, domain, did, created, registered, read_only)
885 select id, domain, did, created, registered, 1 from registrations
886 where registered is not null;
887
888 drop table registrations;
889 alter table registrations_new rename to registrations;
890 `)
891 return err
892 })
893
894 // recreate and add rkey + created columns with default constraint
895 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
896 // create new table
897 // - repo_at instead of repo integer
898 // - rkey field
899 // - created field
900 _, err := tx.Exec(`
901 create table collaborators_new (
902 -- identifiers for the record
903 id integer primary key autoincrement,
904 did text not null,
905 rkey text,
906
907 -- content
908 subject_did text not null,
909 repo_at text not null,
910
911 -- meta
912 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
913
914 -- constraints
915 foreign key (repo_at) references repos(at_uri) on delete cascade
916 )
917 `)
918 if err != nil {
919 return err
920 }
921
922 // copy data
923 _, err = tx.Exec(`
924 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
925 select
926 c.id,
927 r.did,
928 '',
929 c.did,
930 r.at_uri
931 from collaborators c
932 join repos r on c.repo = r.id
933 `)
934 if err != nil {
935 return err
936 }
937
938 // drop old table
939 _, err = tx.Exec(`drop table collaborators`)
940 if err != nil {
941 return err
942 }
943
944 // rename new table
945 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
946 return err
947 })
948
949 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
950 _, err := tx.Exec(`
951 alter table issues add column rkey text not null default '';
952
953 -- get last url section from issue_at and save to rkey column
954 update issues
955 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
956 `)
957 return err
958 })
959
960 // repurpose the read-only column to "needs-upgrade"
961 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
962 _, err := tx.Exec(`
963 alter table registrations rename column read_only to needs_upgrade;
964 `)
965 return err
966 })
967
968 // require all knots to upgrade after the release of total xrpc
969 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
970 _, err := tx.Exec(`
971 update registrations set needs_upgrade = 1;
972 `)
973 return err
974 })
975
976 // require all knots to upgrade after the release of total xrpc
977 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
978 _, err := tx.Exec(`
979 alter table spindles add column needs_upgrade integer not null default 0;
980 `)
981 return err
982 })
983
984 // remove issue_at from issues and replace with generated column
985 //
986 // this requires a full table recreation because stored columns
987 // cannot be added via alter
988 //
989 // couple other changes:
990 // - columns renamed to be more consistent
991 // - adds edited and deleted fields
992 //
993 // disable foreign-keys for the next migration
994 conn.ExecContext(ctx, "pragma foreign_keys = off;")
995 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
996 _, err := tx.Exec(`
997 create table if not exists issues_new (
998 -- identifiers
999 id integer primary key autoincrement,
1000 did text not null,
1001 rkey text not null,
1002 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
1003
1004 -- at identifiers
1005 repo_at text not null,
1006
1007 -- content
1008 issue_id integer not null,
1009 title text not null,
1010 body text not null,
1011 open integer not null default 1,
1012 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1013 edited text, -- timestamp
1014 deleted text, -- timestamp
1015
1016 unique(did, rkey),
1017 unique(repo_at, issue_id),
1018 unique(at_uri),
1019 foreign key (repo_at) references repos(at_uri) on delete cascade
1020 );
1021 `)
1022 if err != nil {
1023 return err
1024 }
1025
1026 // transfer data
1027 _, err = tx.Exec(`
1028 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
1029 select
1030 i.id,
1031 i.owner_did,
1032 i.rkey,
1033 i.repo_at,
1034 i.issue_id,
1035 i.title,
1036 i.body,
1037 i.open,
1038 i.created
1039 from issues i;
1040 `)
1041 if err != nil {
1042 return err
1043 }
1044
1045 // drop old table
1046 _, err = tx.Exec(`drop table issues`)
1047 if err != nil {
1048 return err
1049 }
1050
1051 // rename new table
1052 _, err = tx.Exec(`alter table issues_new rename to issues`)
1053 return err
1054 })
1055 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1056
1057 // - renames the comments table to 'issue_comments'
1058 // - rework issue comments to update constraints:
1059 // * unique(did, rkey)
1060 // * remove comment-id and just use the global ID
1061 // * foreign key (repo_at, issue_id)
1062 // - new columns
1063 // * column "reply_to" which can be any other comment
1064 // * column "at-uri" which is a generated column
1065 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
1066 _, err := tx.Exec(`
1067 create table if not exists issue_comments (
1068 -- identifiers
1069 id integer primary key autoincrement,
1070 did text not null,
1071 rkey text,
1072 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
1073
1074 -- at identifiers
1075 issue_at text not null,
1076 reply_to text, -- at_uri of parent comment
1077
1078 -- content
1079 body text not null,
1080 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1081 edited text,
1082 deleted text,
1083
1084 -- constraints
1085 unique(did, rkey),
1086 unique(at_uri),
1087 foreign key (issue_at) references issues(at_uri) on delete cascade
1088 );
1089 `)
1090 if err != nil {
1091 return err
1092 }
1093
1094 // transfer data
1095 _, err = tx.Exec(`
1096 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
1097 select
1098 c.id,
1099 c.owner_did,
1100 c.rkey,
1101 i.at_uri, -- get at_uri from issues table
1102 c.body,
1103 c.created,
1104 c.edited,
1105 c.deleted
1106 from comments c
1107 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
1108 `)
1109 if err != nil {
1110 return err
1111 }
1112
1113 // drop old table
1114 _, err = tx.Exec(`drop table comments`)
1115 return err
1116 })
1117
1118 // add generated at_uri column to pulls table
1119 //
1120 // this requires a full table recreation because stored columns
1121 // cannot be added via alter
1122 //
1123 // disable foreign-keys for the next migration
1124 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1125 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
1126 _, err := tx.Exec(`
1127 create table if not exists pulls_new (
1128 -- identifiers
1129 id integer primary key autoincrement,
1130 pull_id integer not null,
1131 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
1132
1133 -- at identifiers
1134 repo_at text not null,
1135 owner_did text not null,
1136 rkey text not null,
1137
1138 -- content
1139 title text not null,
1140 body text not null,
1141 target_branch text not null,
1142 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
1143
1144 -- source info
1145 source_branch text,
1146 source_repo_at text,
1147
1148 -- stacking
1149 stack_id text,
1150 change_id text,
1151 parent_change_id text,
1152
1153 -- meta
1154 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1155
1156 -- constraints
1157 unique(repo_at, pull_id),
1158 unique(at_uri),
1159 foreign key (repo_at) references repos(at_uri) on delete cascade
1160 );
1161 `)
1162 if err != nil {
1163 return err
1164 }
1165
1166 // transfer data
1167 _, err = tx.Exec(`
1168 insert into pulls_new (
1169 id, pull_id, repo_at, owner_did, rkey,
1170 title, body, target_branch, state,
1171 source_branch, source_repo_at,
1172 stack_id, change_id, parent_change_id,
1173 created
1174 )
1175 select
1176 id, pull_id, repo_at, owner_did, rkey,
1177 title, body, target_branch, state,
1178 source_branch, source_repo_at,
1179 stack_id, change_id, parent_change_id,
1180 created
1181 from pulls;
1182 `)
1183 if err != nil {
1184 return err
1185 }
1186
1187 // drop old table
1188 _, err = tx.Exec(`drop table pulls`)
1189 if err != nil {
1190 return err
1191 }
1192
1193 // rename new table
1194 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1195 return err
1196 })
1197 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1198
1199 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1200 //
1201 // this requires a full table recreation because stored columns
1202 // cannot be added via alter
1203 //
1204 // disable foreign-keys for the next migration
1205 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1206 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1207 _, err := tx.Exec(`
1208 create table if not exists pull_submissions_new (
1209 -- identifiers
1210 id integer primary key autoincrement,
1211 pull_at text not null,
1212
1213 -- content, these are immutable, and require a resubmission to update
1214 round_number integer not null default 0,
1215 patch text,
1216 source_rev text,
1217
1218 -- meta
1219 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1220
1221 -- constraints
1222 unique(pull_at, round_number),
1223 foreign key (pull_at) references pulls(at_uri) on delete cascade
1224 );
1225 `)
1226 if err != nil {
1227 return err
1228 }
1229
1230 // transfer data, constructing pull_at from pulls table
1231 _, err = tx.Exec(`
1232 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1233 select
1234 ps.id,
1235 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1236 ps.round_number,
1237 ps.patch,
1238 ps.created
1239 from pull_submissions ps
1240 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1241 `)
1242 if err != nil {
1243 return err
1244 }
1245
1246 // drop old table
1247 _, err = tx.Exec(`drop table pull_submissions`)
1248 if err != nil {
1249 return err
1250 }
1251
1252 // rename new table
1253 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1254 return err
1255 })
1256 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1257
1258 // knots may report the combined patch for a comparison, we can store that on the appview side
1259 // (but not on the pds record), because calculating the combined patch requires a git index
1260 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1261 _, err := tx.Exec(`
1262 alter table pull_submissions add column combined text;
1263 `)
1264 return err
1265 })
1266
1267 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1268 _, err := tx.Exec(`
1269 alter table profile add column pronouns text;
1270 `)
1271 return err
1272 })
1273
1274 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1275 _, err := tx.Exec(`
1276 alter table repos add column website text;
1277 alter table repos add column topics text;
1278 `)
1279 return err
1280 })
1281
1282 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1283 _, err := tx.Exec(`
1284 alter table notification_preferences add column user_mentioned integer not null default 1;
1285 `)
1286 return err
1287 })
1288
1289 // remove the foreign key constraints from stars.
1290 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1291 _, err := tx.Exec(`
1292 create table stars_new (
1293 id integer primary key autoincrement,
1294 did text not null,
1295 rkey text not null,
1296
1297 subject_at text not null,
1298
1299 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1300 unique(did, rkey),
1301 unique(did, subject_at)
1302 );
1303
1304 insert into stars_new (
1305 id,
1306 did,
1307 rkey,
1308 subject_at,
1309 created
1310 )
1311 select
1312 id,
1313 starred_by_did,
1314 rkey,
1315 repo_at,
1316 created
1317 from stars;
1318
1319 drop table stars;
1320 alter table stars_new rename to stars;
1321
1322 create index if not exists idx_stars_created on stars(created);
1323 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1324 `)
1325 return err
1326 })
1327
1328 orm.RunMigration(conn, logger, "add-avatar-to-profile", func(tx *sql.Tx) error {
1329 _, err := tx.Exec(`
1330 alter table profile add column avatar text;
1331 `)
1332 return err
1333 })
1334
1335 orm.RunMigration(conn, logger, "remove-profile-stats-column-constraint", func(tx *sql.Tx) error {
1336 _, err := tx.Exec(`
1337 -- create new table without the check constraint
1338 create table profile_stats_new (
1339 id integer primary key autoincrement,
1340 did text not null,
1341 kind text not null, -- no constraint this time
1342 foreign key (did) references profile(did) on delete cascade
1343 );
1344
1345 -- copy data from old table
1346 insert into profile_stats_new (id, did, kind)
1347 select id, did, kind
1348 from profile_stats;
1349
1350 -- drop old table
1351 drop table profile_stats;
1352
1353 -- rename new table
1354 alter table profile_stats_new rename to profile_stats;
1355 `)
1356 return err
1357 })
1358
1359 orm.RunMigration(conn, logger, "add-preferred-handle-profile", func(tx *sql.Tx) error {
1360 _, err := tx.Exec(`
1361 alter table profile add column preferred_handle text;
1362 `)
1363 return err
1364 })
1365
1366 orm.RunMigration(conn, logger, "add-repo-did-column", func(tx *sql.Tx) error {
1367 _, err := tx.Exec(`
1368 alter table repos add column repo_did text;
1369 create unique index if not exists idx_repos_repo_did on repos(repo_did);
1370 `)
1371 return err
1372 })
1373
1374 orm.RunMigration(conn, logger, "add-pds-rewrite-status", func(tx *sql.Tx) error {
1375 _, err := tx.Exec(`
1376 create table if not exists pds_rewrite_status (
1377 id integer primary key autoincrement,
1378 user_did text not null,
1379 repo_did text not null,
1380 record_nsid text not null,
1381 record_rkey text not null,
1382 old_repo_at text not null,
1383 status text not null default 'pending',
1384 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1385 unique(user_did, record_nsid, record_rkey)
1386 );
1387 create index if not exists idx_pds_rewrite_user on pds_rewrite_status(user_did, status);
1388 `)
1389 return err
1390 })
1391
1392 orm.RunMigration(conn, logger, "add-pipelines-repo-did", func(tx *sql.Tx) error {
1393 _, err := tx.Exec(`
1394 alter table pipelines add column repo_did text;
1395 create index if not exists idx_pipelines_repo_did on pipelines(repo_did);
1396 `)
1397 return err
1398 })
1399
1400 orm.RunMigration(conn, logger, "migrate-knots-to-repo-dids", func(tx *sql.Tx) error {
1401 _, err := tx.Exec(`update registrations set needs_upgrade = 1`)
1402 return err
1403 })
1404
1405 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1406 orm.RunMigration(conn, logger, "drop-pinned-repos-at-uri-fk", func(tx *sql.Tx) error {
1407 _, err := tx.Exec(`
1408 create table if not exists profile_pinned_repositories_new (
1409 id integer primary key autoincrement,
1410 did text not null,
1411 pin text not null,
1412
1413 unique(did, pin),
1414 foreign key (did) references profile(did) on delete cascade
1415 );
1416
1417 insert into profile_pinned_repositories_new (id, did, pin)
1418 select id, did, at_uri from profile_pinned_repositories;
1419
1420 drop table profile_pinned_repositories;
1421
1422 alter table profile_pinned_repositories_new rename to profile_pinned_repositories;
1423 `)
1424 return err
1425 })
1426 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1427
1428 orm.RunMigration(conn, logger, "reset-profile-pin-rewrites", func(tx *sql.Tx) error {
1429 _, err := tx.Exec(`
1430 update pds_rewrite_status
1431 set status = 'pending',
1432 updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1433 where record_nsid = 'sh.tangled.actor.profile'
1434 and status = 'done'
1435 `)
1436 return err
1437 })
1438
1439 orm.RunMigration(conn, logger, "add-blob-data-to-pull-submissions", func(tx *sql.Tx) error {
1440 _, err := tx.Exec(`
1441 alter table pull_submissions add column patch_blob_ref text;
1442 alter table pull_submissions add column patch_blob_mime text;
1443 alter table pull_submissions add column patch_blob_size integer;
1444 `)
1445 return err
1446 })
1447
1448 orm.RunMigration(conn, logger, "replace-parent-change-id-with-aturi", func(tx *sql.Tx) error {
1449 // add new column
1450 _, err := tx.Exec(`
1451 alter table pulls add column dependent_on text;
1452 `)
1453 if err != nil {
1454 return err
1455 }
1456
1457 // populate dependent_on with at_uri of the parent
1458 _, err = tx.Exec(`
1459 update pulls
1460 set dependent_on = (
1461 select at_uri
1462 from pulls as parent
1463 where parent.stack_id = pulls.stack_id
1464 and parent.change_id = pulls.parent_change_id
1465 )
1466 where parent_change_id is not null;
1467 `)
1468 if err != nil {
1469 return err
1470 }
1471
1472 // drop old columns
1473 _, err = tx.Exec(`
1474 alter table pulls drop column parent_change_id;
1475 alter table pulls drop column stack_id;
1476 `)
1477
1478 return err
1479 })
1480
1481 orm.RunMigration(conn, logger, "add-pds-migration", func(tx *sql.Tx) error {
1482 _, err := tx.Exec(`
1483 create table if not exists pds_migration (
1484 name text not null,
1485
1486 -- record at_uri
1487 did text not null,
1488 collection text not null,
1489 rkey text not null,
1490
1491 status text not null default 'pending',
1492 error_msg text,
1493 retry_count integer not null default 0,
1494 retry_after integer not null default 0,
1495 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1496
1497 unique(name, did, collection, rkey)
1498 );
1499 `)
1500 return err
1501 })
1502
1503 orm.RunMigration(conn, logger, "unify-pds-record-migration-table", func(tx *sql.Tx) error {
1504 _, err := tx.Exec(`
1505 insert into pds_migration (
1506 name,
1507 did,
1508 collection,
1509 rkey,
1510 status,
1511 updated_at
1512 )
1513 select
1514 'add-repo-did',
1515 user_did,
1516 record_nsid,
1517 record_rkey,
1518 status,
1519 updated_at
1520 from pds_rewrite_status;
1521
1522 drop table pds_rewrite_status;
1523 `)
1524 return err
1525 })
1526
1527 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1528 orm.RunMigration(conn, logger, "add-id-to-vouches", func(tx *sql.Tx) error {
1529 _, err := tx.Exec(`
1530 create table vouches_new (
1531 id integer primary key autoincrement,
1532 did text not null,
1533 subject_did text not null,
1534 cid text not null,
1535 kind text not null default 'vouch',
1536 reason text,
1537 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1538 unique(did, subject_did),
1539 check (did <> subject_did),
1540 check (kind in ('vouch', 'denounce'))
1541 );
1542
1543 insert into vouches_new (did, subject_did, cid, kind, reason, created_at)
1544 select did, subject_did, cid, kind, reason, created_at
1545 from vouches;
1546
1547 drop table vouches;
1548 alter table vouches_new rename to vouches;
1549 `)
1550 return err
1551 })
1552 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1553
1554 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1555 orm.RunMigration(conn, logger, "drop-pipeline-statuses-pipeline-fk", func(tx *sql.Tx) error {
1556 _, err := tx.Exec(`
1557 create table if not exists pipeline_statuses_new (
1558 id integer primary key autoincrement,
1559 spindle text not null,
1560 rkey text not null,
1561
1562 pipeline_knot text not null,
1563 pipeline_rkey text not null,
1564
1565 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1566 workflow text not null,
1567 status text not null,
1568 error text,
1569 exit_code integer not null default 0,
1570
1571 unique (spindle, rkey)
1572 );
1573
1574 insert into pipeline_statuses_new
1575 select * from pipeline_statuses;
1576
1577 drop table pipeline_statuses;
1578 alter table pipeline_statuses_new rename to pipeline_statuses;
1579 `)
1580 return err
1581 })
1582 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1583
1584 orm.RunMigration(conn, logger, "add-repo-renames", func(tx *sql.Tx) error {
1585 res, err := tx.Exec(`
1586 update repos
1587 set name = name || '-renamed-' || id || '-' || lower(hex(randomblob(4)))
1588 where id in (
1589 select id from (
1590 select id, row_number() over (
1591 partition by did, knot, name
1592 order by created desc, id desc
1593 ) as rn
1594 from repos
1595 ) where rn > 1
1596 );
1597 `)
1598 if err != nil {
1599 return err
1600 }
1601 if n, _ := res.RowsAffected(); n > 0 {
1602 logger.Warn("suffixed legacy duplicate repo names before adding unique index", "rows", n)
1603 }
1604
1605 var remaining int
1606 if err := tx.QueryRow(`
1607 select count(*) from (
1608 select 1 from repos group by did, knot, name having count(*) > 1
1609 )
1610 `).Scan(&remaining); err != nil {
1611 return fmt.Errorf("checking for residual duplicate (did, knot, name) groups: %w", err)
1612 }
1613 if remaining > 0 {
1614 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)
1615 }
1616
1617 _, err = tx.Exec(`
1618 create table if not exists repo_renames (
1619 owner_did text not null,
1620 old_rkey text not null,
1621 repo_did text not null,
1622 renamed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1623 primary key (owner_did, old_rkey)
1624 );
1625 create unique index if not exists idx_repos_owner_knot_name
1626 on repos(did, knot, name);
1627 `)
1628 return err
1629 })
1630
1631 orm.RunMigration(conn, logger, "repos-canonical-rkey-uniqueness", func(tx *sql.Tx) error {
1632 _, err := tx.Exec(`
1633 drop index if exists idx_repos_owner_knot_name;
1634 create unique index if not exists idx_repos_did_rkey
1635 on repos(did, rkey);
1636 `)
1637 return err
1638 })
1639
1640 orm.RunMigration(conn, logger, "repo-did-references", func(tx *sql.Tx) error {
1641 tables := []struct{ table, oldCol, newCol string }{
1642 {"issues", "repo_at", "repo_did"},
1643 {"pulls", "repo_at", "repo_did"},
1644 {"pull_comments", "repo_at", "repo_did"},
1645 {"stars", "subject_at", "subject_did"},
1646 {"artifacts", "repo_at", "repo_did"},
1647 {"webhooks", "repo_at", "repo_did"},
1648 {"repo_sites", "repo_at", "repo_did"},
1649 {"site_deploys", "repo_at", "repo_did"},
1650 {"collaborators", "repo_at", "repo_did"},
1651 {"repo_issue_seqs", "repo_at", "repo_did"},
1652 {"repo_pull_seqs", "repo_at", "repo_did"},
1653 {"repo_languages", "repo_at", "repo_did"},
1654 {"repo_labels", "repo_at", "repo_did"},
1655 }
1656
1657 stmts := ""
1658 for _, t := range tables {
1659 stmts += fmt.Sprintf(
1660 `ALTER TABLE %s ADD COLUMN %s TEXT;
1661 UPDATE %s SET %s = (SELECT repos.repo_did FROM repos WHERE repos.at_uri = %s.%s);
1662 CREATE INDEX IF NOT EXISTS idx_%s_%s ON %s(%s);
1663 `, t.table, t.newCol, t.table, t.newCol, t.table, t.oldCol, t.table, t.newCol, t.table, t.newCol)
1664 }
1665
1666 stmts += `ALTER TABLE pulls ADD COLUMN source_repo_did TEXT;
1667 UPDATE pulls SET source_repo_did = (SELECT repos.repo_did FROM repos WHERE repos.at_uri = pulls.source_repo_at);
1668
1669 UPDATE profile_pinned_repositories SET pin = (
1670 SELECT repos.repo_did FROM repos WHERE repos.at_uri = profile_pinned_repositories.pin
1671 ) WHERE pin LIKE 'at://%'
1672 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 != '');
1673 `
1674
1675 _, err := tx.Exec(stmts)
1676 return err
1677 })
1678
1679 orm.RunMigration(conn, logger, "backfill-pds-rewrites-star-issue-pull-collab", func(tx *sql.Tx) error {
1680 type source struct {
1681 userDidCol string
1682 table string
1683 nsid string
1684 fkCol string
1685 }
1686 sources := []source{
1687 {"did", "stars", "sh.tangled.feed.star", "subject_at"},
1688 {"did", "issues", "sh.tangled.repo.issue", "repo_at"},
1689 {"owner_did", "pulls", "sh.tangled.repo.pull", "repo_at"},
1690 {"did", "collaborators", "sh.tangled.repo.collaborator", "repo_at"},
1691 }
1692
1693 for _, src := range sources {
1694 _, err := tx.Exec(fmt.Sprintf(`
1695 INSERT INTO pds_migration (name, did, collection, rkey, status)
1696 SELECT 'add-repo-did', t.%s, '%s', t.rkey, 'pending'
1697 FROM %s t
1698 JOIN repos r ON r.at_uri = t.%s
1699 WHERE r.repo_did IS NOT NULL AND r.repo_did != ''
1700 ON CONFLICT(name, did, collection, rkey) DO NOTHING
1701 `, src.userDidCol, src.nsid, src.table, src.fkCol))
1702 if err != nil {
1703 return fmt.Errorf("backfill pds rewrites for %s: %w", src.table, err)
1704 }
1705 }
1706
1707 return nil
1708 })
1709
1710 orm.RunMigration(conn, logger, "backfill-pds-rewrites-profiles", func(tx *sql.Tx) error {
1711 _, err := tx.Exec(`
1712 INSERT INTO pds_migration (name, did, collection, rkey, status)
1713 SELECT DISTINCT 'add-repo-did', pp.did, 'sh.tangled.actor.profile', 'self', 'pending'
1714 FROM profile_pinned_repositories pp
1715 JOIN repos r ON r.at_uri = pp.pin
1716 WHERE pp.pin LIKE 'at://%'
1717 AND r.repo_did IS NOT NULL AND r.repo_did != ''
1718 ON CONFLICT(name, did, collection, rkey) DO NOTHING
1719 `)
1720 if err != nil {
1721 return fmt.Errorf("backfill pds rewrites for profiles: %w", err)
1722 }
1723 return nil
1724 })
1725
1726 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1727 orm.RunMigration(conn, logger, "drop-old-at-uri-columns", func(tx *sql.Tx) error {
1728 _, err := tx.Exec(`
1729 CREATE TABLE repos_new (
1730 id INTEGER PRIMARY KEY AUTOINCREMENT,
1731 did TEXT NOT NULL,
1732 name TEXT NOT NULL,
1733 knot TEXT NOT NULL,
1734 rkey TEXT NOT NULL,
1735 at_uri TEXT NOT NULL UNIQUE,
1736 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1737 description TEXT CHECK (length(description) <= 200),
1738 source TEXT,
1739 spindle TEXT,
1740 website TEXT,
1741 topics TEXT,
1742 repo_did TEXT,
1743 UNIQUE(did, rkey)
1744 );
1745 INSERT INTO repos_new (id, did, name, knot, rkey, at_uri, created, description, source, spindle, website, topics, repo_did)
1746 SELECT id, did, name, knot, rkey, at_uri, created, description, source, spindle, website, topics, repo_did
1747 FROM repos;
1748 DROP TABLE repos;
1749 ALTER TABLE repos_new RENAME TO repos;
1750 CREATE UNIQUE INDEX idx_repos_repo_did ON repos(repo_did);
1751 CREATE UNIQUE INDEX idx_repos_did_rkey ON repos(did, rkey);
1752
1753 CREATE TABLE issues_new (
1754 id INTEGER PRIMARY KEY AUTOINCREMENT,
1755 did TEXT NOT NULL,
1756 rkey TEXT NOT NULL,
1757 at_uri TEXT GENERATED ALWAYS AS ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) STORED,
1758 repo_did TEXT NOT NULL,
1759 issue_id INTEGER NOT NULL,
1760 title TEXT NOT NULL,
1761 body TEXT NOT NULL,
1762 open INTEGER NOT NULL DEFAULT 1,
1763 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1764 edited TEXT,
1765 deleted TEXT,
1766 UNIQUE(did, rkey),
1767 UNIQUE(repo_did, issue_id),
1768 UNIQUE(at_uri),
1769 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1770 );
1771 INSERT INTO issues_new (id, did, rkey, repo_did, issue_id, title, body, open, created, edited, deleted)
1772 SELECT id, did, rkey, repo_did, issue_id, title, body, open, created, edited, deleted
1773 FROM issues WHERE repo_did IS NOT NULL AND repo_did != '';
1774 DROP TABLE issues;
1775 ALTER TABLE issues_new RENAME TO issues;
1776 CREATE INDEX idx_issues_repo_did ON issues(repo_did);
1777
1778 CREATE TABLE pulls_new (
1779 id INTEGER PRIMARY KEY AUTOINCREMENT,
1780 pull_id INTEGER NOT NULL,
1781 at_uri TEXT GENERATED ALWAYS AS ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) STORED,
1782 repo_did TEXT NOT NULL,
1783 owner_did TEXT NOT NULL,
1784 rkey TEXT NOT NULL,
1785 title TEXT NOT NULL,
1786 body TEXT NOT NULL,
1787 target_branch TEXT NOT NULL,
1788 state INTEGER NOT NULL DEFAULT 0 CHECK (state IN (0, 1, 2, 3)),
1789 source_branch TEXT,
1790 source_repo_did TEXT,
1791 change_id TEXT,
1792 dependent_on TEXT,
1793 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1794 UNIQUE(repo_did, pull_id),
1795 UNIQUE(at_uri),
1796 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1797 );
1798 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)
1799 SELECT id, pull_id, repo_did, owner_did, rkey, title, body, target_branch, state, source_branch, source_repo_did, change_id, dependent_on, created
1800 FROM pulls WHERE repo_did IS NOT NULL AND repo_did != '';
1801 DROP TABLE pulls;
1802 ALTER TABLE pulls_new RENAME TO pulls;
1803 CREATE INDEX idx_pulls_repo_did ON pulls(repo_did);
1804 CREATE INDEX idx_pulls_source_repo_did ON pulls(source_repo_did);
1805
1806 CREATE TABLE pull_comments_new (
1807 id INTEGER PRIMARY KEY AUTOINCREMENT,
1808 pull_id INTEGER NOT NULL,
1809 submission_id INTEGER NOT NULL,
1810 repo_did TEXT NOT NULL,
1811 owner_did TEXT NOT NULL,
1812 comment_at TEXT NOT NULL,
1813 body TEXT NOT NULL,
1814 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1815 FOREIGN KEY (repo_did, pull_id) REFERENCES pulls(repo_did, pull_id) ON DELETE CASCADE,
1816 FOREIGN KEY (submission_id) REFERENCES pull_submissions(id) ON DELETE CASCADE
1817 );
1818 INSERT INTO pull_comments_new (id, pull_id, submission_id, repo_did, owner_did, comment_at, body, created)
1819 SELECT id, pull_id, submission_id, repo_did, owner_did, comment_at, body, created
1820 FROM pull_comments WHERE repo_did IS NOT NULL AND repo_did != '';
1821 DROP TABLE pull_comments;
1822 ALTER TABLE pull_comments_new RENAME TO pull_comments;
1823 CREATE INDEX idx_pull_comments_repo_did ON pull_comments(repo_did);
1824
1825 CREATE TABLE stars_new (
1826 id INTEGER PRIMARY KEY AUTOINCREMENT,
1827 did TEXT NOT NULL,
1828 rkey TEXT NOT NULL,
1829 subject_type TEXT NOT NULL CHECK (subject_type IN ('repo', 'string')),
1830 subject TEXT NOT NULL,
1831 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1832 UNIQUE(did, rkey),
1833 UNIQUE(did, subject)
1834 );
1835 INSERT INTO stars_new (id, did, rkey, subject_type, subject, created)
1836 SELECT id, did, rkey, 'repo', subject_did, created
1837 FROM stars
1838 WHERE subject_did IS NOT NULL AND subject_did != '';
1839 INSERT OR IGNORE INTO stars_new (id, did, rkey, subject_type, subject, created)
1840 SELECT id, did, rkey, 'string', subject_at, created
1841 FROM stars
1842 WHERE (subject_did IS NULL OR subject_did = '')
1843 AND subject_at LIKE 'at://%/sh.tangled.string/%';
1844 DROP TABLE stars;
1845 ALTER TABLE stars_new RENAME TO stars;
1846 CREATE INDEX idx_stars_subject ON stars(subject);
1847 CREATE INDEX idx_stars_subject_type ON stars(subject_type);
1848 CREATE INDEX idx_stars_created ON stars(created);
1849
1850 CREATE TABLE collaborators_new (
1851 id INTEGER PRIMARY KEY AUTOINCREMENT,
1852 did TEXT NOT NULL,
1853 rkey TEXT,
1854 subject_did TEXT NOT NULL,
1855 repo_did TEXT NOT NULL,
1856 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1857 UNIQUE(did, rkey),
1858 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1859 );
1860 INSERT INTO collaborators_new (id, did, rkey, subject_did, repo_did, created)
1861 SELECT id, did, NULLIF(rkey, ''), subject_did, repo_did, created
1862 FROM collaborators WHERE repo_did IS NOT NULL AND repo_did != '';
1863 DROP TABLE collaborators;
1864 ALTER TABLE collaborators_new RENAME TO collaborators;
1865 CREATE INDEX idx_collaborators_repo_did ON collaborators(repo_did);
1866
1867 CREATE TABLE artifacts_new (
1868 id INTEGER PRIMARY KEY AUTOINCREMENT,
1869 did TEXT NOT NULL,
1870 rkey TEXT NOT NULL,
1871 repo_did TEXT NOT NULL,
1872 tag BINARY(20) NOT NULL,
1873 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1874 blob_cid TEXT NOT NULL,
1875 name TEXT NOT NULL,
1876 size INTEGER NOT NULL DEFAULT 0,
1877 mimetype TEXT NOT NULL DEFAULT '*/*',
1878 UNIQUE(did, rkey),
1879 UNIQUE(repo_did, tag, name),
1880 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1881 );
1882 INSERT INTO artifacts_new (id, did, rkey, repo_did, tag, created, blob_cid, name, size, mimetype)
1883 SELECT id, did, rkey, repo_did, tag, created, blob_cid, name, size, mimetype
1884 FROM artifacts WHERE repo_did IS NOT NULL AND repo_did != '';
1885 DROP TABLE artifacts;
1886 ALTER TABLE artifacts_new RENAME TO artifacts;
1887 CREATE INDEX idx_artifacts_repo_did ON artifacts(repo_did);
1888
1889 CREATE TABLE webhooks_new (
1890 id INTEGER PRIMARY KEY AUTOINCREMENT,
1891 repo_did TEXT NOT NULL,
1892 url TEXT NOT NULL,
1893 secret TEXT,
1894 active INTEGER NOT NULL DEFAULT 1,
1895 events TEXT NOT NULL,
1896 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1897 updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1898 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1899 );
1900 INSERT INTO webhooks_new (id, repo_did, url, secret, active, events, created_at, updated_at)
1901 SELECT id, repo_did, url, secret, active, events, created_at, updated_at
1902 FROM webhooks WHERE repo_did IS NOT NULL AND repo_did != '';
1903 DROP TABLE webhooks;
1904 ALTER TABLE webhooks_new RENAME TO webhooks;
1905 CREATE INDEX idx_webhooks_repo_did ON webhooks(repo_did);
1906
1907 CREATE TABLE repo_sites_new (
1908 id INTEGER PRIMARY KEY AUTOINCREMENT,
1909 repo_did TEXT NOT NULL UNIQUE,
1910 branch TEXT NOT NULL,
1911 dir TEXT NOT NULL DEFAULT '/',
1912 is_index INTEGER NOT NULL DEFAULT 0,
1913 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1914 updated TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1915 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1916 );
1917 INSERT INTO repo_sites_new (id, repo_did, branch, dir, is_index, created, updated)
1918 SELECT id, repo_did, branch, dir, is_index, created, updated
1919 FROM repo_sites WHERE repo_did IS NOT NULL AND repo_did != '';
1920 DROP TABLE repo_sites;
1921 ALTER TABLE repo_sites_new RENAME TO repo_sites;
1922
1923 CREATE TABLE site_deploys_new (
1924 id INTEGER PRIMARY KEY AUTOINCREMENT,
1925 repo_did TEXT NOT NULL,
1926 branch TEXT NOT NULL,
1927 dir TEXT NOT NULL DEFAULT '/',
1928 commit_sha TEXT NOT NULL DEFAULT '',
1929 status TEXT NOT NULL CHECK (status IN ('success', 'failure')),
1930 trigger TEXT NOT NULL CHECK (trigger IN ('config_change', 'push')),
1931 error TEXT NOT NULL DEFAULT '',
1932 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1933 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1934 );
1935 INSERT INTO site_deploys_new (id, repo_did, branch, dir, commit_sha, status, trigger, error, created_at)
1936 SELECT id, repo_did, branch, dir, commit_sha, status, trigger, error, created_at
1937 FROM site_deploys WHERE repo_did IS NOT NULL AND repo_did != '';
1938 DROP TABLE site_deploys;
1939 ALTER TABLE site_deploys_new RENAME TO site_deploys;
1940 CREATE INDEX idx_site_deploys_repo_did ON site_deploys(repo_did);
1941
1942 CREATE TABLE repo_issue_seqs_new (
1943 repo_did TEXT PRIMARY KEY,
1944 next_issue_id INTEGER NOT NULL DEFAULT 1,
1945 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1946 );
1947 INSERT INTO repo_issue_seqs_new (repo_did, next_issue_id)
1948 SELECT repo_did, next_issue_id
1949 FROM repo_issue_seqs WHERE repo_did IS NOT NULL AND repo_did != '';
1950 DROP TABLE repo_issue_seqs;
1951 ALTER TABLE repo_issue_seqs_new RENAME TO repo_issue_seqs;
1952
1953 CREATE TABLE repo_pull_seqs_new (
1954 repo_did TEXT PRIMARY KEY,
1955 next_pull_id INTEGER NOT NULL DEFAULT 1,
1956 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1957 );
1958 INSERT INTO repo_pull_seqs_new (repo_did, next_pull_id)
1959 SELECT repo_did, next_pull_id
1960 FROM repo_pull_seqs WHERE repo_did IS NOT NULL AND repo_did != '';
1961 DROP TABLE repo_pull_seqs;
1962 ALTER TABLE repo_pull_seqs_new RENAME TO repo_pull_seqs;
1963
1964 CREATE TABLE repo_languages_new (
1965 id INTEGER PRIMARY KEY AUTOINCREMENT,
1966 repo_did TEXT NOT NULL,
1967 ref TEXT NOT NULL,
1968 is_default_ref INTEGER NOT NULL DEFAULT 0,
1969 language TEXT NOT NULL,
1970 bytes INTEGER NOT NULL CHECK (bytes >= 0),
1971 UNIQUE(repo_did, ref, language),
1972 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1973 );
1974 INSERT INTO repo_languages_new (id, repo_did, ref, is_default_ref, language, bytes)
1975 SELECT id, repo_did, ref, is_default_ref, language, bytes
1976 FROM repo_languages WHERE repo_did IS NOT NULL AND repo_did != '';
1977 DROP TABLE repo_languages;
1978 ALTER TABLE repo_languages_new RENAME TO repo_languages;
1979
1980 CREATE TABLE repo_labels_new (
1981 id INTEGER PRIMARY KEY AUTOINCREMENT,
1982 repo_did TEXT NOT NULL,
1983 label_at TEXT NOT NULL,
1984 UNIQUE(repo_did, label_at),
1985 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
1986 );
1987 INSERT INTO repo_labels_new (id, repo_did, label_at)
1988 SELECT id, repo_did, label_at
1989 FROM repo_labels WHERE repo_did IS NOT NULL AND repo_did != '';
1990 DROP TABLE repo_labels;
1991 ALTER TABLE repo_labels_new RENAME TO repo_labels;
1992 `)
1993 return err
1994 })
1995 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1996
1997 orm.RunMigration(conn, logger, "migrate-knots-to-repo-did-rename", func(tx *sql.Tx) error {
1998 _, err := tx.Exec(`
1999 update registrations set needs_upgrade = 1;
2000 `)
2001 return err
2002 })
2003
2004 orm.RunMigration(conn, logger, "drop-ghost-comments-table", func(tx *sql.Tx) error {
2005 _, err := tx.Exec(`DROP TABLE IF EXISTS comments`)
2006 return err
2007 })
2008
2009 orm.RunMigration(conn, logger, "add-knot-members-table", func(tx *sql.Tx) error {
2010 _, err := tx.Exec(`
2011 create table if not exists knot_members (
2012 id integer primary key autoincrement,
2013 did text not null,
2014 rkey text not null,
2015 domain text not null,
2016 subject text not null,
2017 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2018 unique (did, domain, subject)
2019 );
2020 create index if not exists idx_knot_members_did_rkey on knot_members(did, rkey);
2021 `)
2022 return err
2023 })
2024
2025 orm.RunMigration(conn, logger, "add-comments-table", func(tx *sql.Tx) error {
2026 _, err := tx.Exec(`
2027 drop table if exists comments;
2028
2029 create table comments (
2030 -- identifiers
2031 id integer primary key autoincrement,
2032
2033 did text not null,
2034 collection text not null default 'sh.tangled.feed.comment',
2035 rkey text not null,
2036 at_uri text generated always as ('at://' || did || '/' || collection || '/' || rkey) stored,
2037 cid text,
2038
2039 -- content
2040 subject_uri text not null, -- at_uri of subject (issue, pr, string)
2041 subject_cid text not null, -- cid of subject
2042
2043 body_text text not null,
2044 body_original text,
2045 body_blobs text, -- json
2046
2047 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2048
2049 reply_to_uri text, -- at_uri of parent comment
2050 reply_to_cid text, -- cid of parent comment
2051
2052 pull_round_idx integer, -- pull round index. required when subject is sh.tangled.repo.pull
2053
2054 -- appview-local information
2055 edited text,
2056 deleted text,
2057
2058 unique(did, collection, rkey)
2059 );
2060
2061 insert into comments (
2062 did,
2063 collection,
2064 rkey,
2065 subject_uri,
2066 subject_cid, -- we need to know cid
2067 body_text,
2068 created,
2069 reply_to_uri,
2070 reply_to_cid, -- we need to know cid
2071 edited,
2072 deleted
2073 )
2074 select
2075 did,
2076 'sh.tangled.repo.issue.comment',
2077 rkey,
2078 issue_at,
2079 '',
2080 body,
2081 created,
2082 reply_to,
2083 '',
2084 edited,
2085 deleted
2086 from issue_comments
2087 where rkey is not null;
2088
2089 insert into comments (
2090 did,
2091 collection,
2092 rkey,
2093 subject_uri,
2094 subject_cid, -- we need to know cid
2095 body_text,
2096 created,
2097 pull_round_idx
2098 )
2099 select
2100 c.owner_did,
2101 'sh.tangled.repo.pull.comment',
2102 substr(
2103 substr(c.comment_at, 6 + instr(substr(c.comment_at, 6), '/')), -- nsid/rkey
2104 instr(
2105 substr(c.comment_at, 6 + instr(substr(c.comment_at, 6), '/')), -- nsid/rkey
2106 '/'
2107 ) + 1
2108 ), -- rkey
2109 p.at_uri,
2110 '',
2111 c.body,
2112 c.created,
2113 s.round_number
2114 from pull_comments c
2115 join pulls p on c.repo_did = p.repo_did and c.pull_id = p.pull_id
2116 join pull_submissions s on s.id = c.submission_id;
2117 `)
2118 return err
2119 })
2120
2121 orm.RunMigration(conn, logger, "migrate-legacy-comments", func(tx *sql.Tx) error {
2122 _, err := tx.Exec(`
2123 insert into pds_migration (name, did, collection, rkey)
2124 select
2125 'use-feed-comment',
2126 did,
2127 collection,
2128 rkey
2129 from comments
2130 where collection <> 'sh.tangled.feed.comment';
2131 `)
2132 return err
2133 })
2134
2135 conn.ExecContext(ctx, "pragma foreign_keys = off;")
2136 orm.RunMigration(conn, logger, "cascade-notification-entity-fks", func(tx *sql.Tx) error {
2137 _, err := tx.Exec(`
2138 CREATE TABLE notifications_new (
2139 id INTEGER PRIMARY KEY AUTOINCREMENT,
2140 recipient_did TEXT NOT NULL,
2141 actor_did TEXT NOT NULL,
2142 type TEXT NOT NULL,
2143 entity_type TEXT NOT NULL,
2144 entity_id TEXT NOT NULL,
2145 read INTEGER NOT NULL DEFAULT 0,
2146 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2147 repo_id INTEGER REFERENCES repos(id) ON DELETE CASCADE,
2148 issue_id INTEGER REFERENCES issues(id) ON DELETE CASCADE,
2149 pull_id INTEGER REFERENCES pulls(id) ON DELETE CASCADE
2150 );
2151 INSERT INTO notifications_new (id, recipient_did, actor_did, type, entity_type, entity_id, read, created, repo_id, issue_id, pull_id)
2152 SELECT id, recipient_did, actor_did, type, entity_type, entity_id, read, created, repo_id, issue_id, pull_id
2153 FROM notifications;
2154 DROP TABLE notifications;
2155 ALTER TABLE notifications_new RENAME TO notifications;
2156 CREATE INDEX idx_notifications_recipient_created ON notifications(recipient_did, created DESC);
2157 CREATE INDEX idx_notifications_recipient_read ON notifications(recipient_did, read);
2158 `)
2159 return err
2160 })
2161 conn.ExecContext(ctx, "pragma foreign_keys = on;")
2162
2163 orm.RunMigration(conn, logger, "collaborators-unique-on-repo-subject", func(tx *sql.Tx) error {
2164 _, err := tx.Exec(`
2165 CREATE TABLE collaborators_new (
2166 id INTEGER PRIMARY KEY AUTOINCREMENT,
2167 did TEXT NOT NULL,
2168 rkey TEXT,
2169 subject_did TEXT NOT NULL,
2170 repo_did TEXT NOT NULL,
2171 created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2172 UNIQUE(repo_did, subject_did),
2173 FOREIGN KEY (repo_did) REFERENCES repos(repo_did) ON DELETE CASCADE
2174 );
2175 INSERT INTO collaborators_new (id, did, rkey, subject_did, repo_did, created)
2176 SELECT id, did, rkey, subject_did, repo_did, created
2177 FROM (
2178 SELECT
2179 id, did, rkey, subject_did, repo_did, created,
2180 ROW_NUMBER() OVER (
2181 PARTITION BY repo_did, subject_did
2182 ORDER BY created DESC, id DESC
2183 ) AS rn
2184 FROM collaborators
2185 )
2186 WHERE rn = 1;
2187 DROP TABLE collaborators;
2188 ALTER TABLE collaborators_new RENAME TO collaborators;
2189 CREATE INDEX idx_collaborators_repo_did ON collaborators(repo_did);
2190 CREATE INDEX idx_collaborators_subject_did ON collaborators(subject_did);
2191 `)
2192 return err
2193 })
2194
2195 orm.RunMigration(conn, logger, "add-knot-acl-native", func(tx *sql.Tx) error {
2196 _, err := tx.Exec(`
2197 create table if not exists knot_acl_native (
2198 domain text primary key,
2199 since text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
2200 );
2201 `)
2202 return err
2203 })
2204
2205 orm.RunMigration(conn, logger, "delete-unused-pipeline-statuses", func(tx *sql.Tx) error {
2206 _, err := tx.Exec(`
2207 delete from pipeline_statuses as p
2208 where p.status = 'pending'
2209 and exists (
2210 select 1 from pipeline_statuses as q
2211 where q.pipeline_knot = p.pipeline_knot
2212 and q.pipeline_rkey = p.pipeline_rkey
2213 and q.workflow = p.workflow
2214 and q.status = 'pending'
2215 and q.created < p.created
2216 );
2217 `)
2218 return err
2219 })
2220
2221 orm.RunMigration(conn, logger, "timeline-query-indexes", func(tx *sql.Tx) error {
2222 _, err := tx.Exec(`
2223 -- following timeline: stars by a set of users, newest first
2224 create index if not exists idx_stars_did_type_created on stars(did, subject_type, created);
2225 -- follower counts and reverse lookups (no index on subject_did before)
2226 create index if not exists idx_follows_subject_did on follows(subject_did);
2227 -- global timeline: newest follows without a full sort
2228 create index if not exists idx_follows_followed_at on follows(followed_at);
2229 -- global timeline: newest repos without a full sort
2230 create index if not exists idx_repos_created on repos(created);
2231 `)
2232 return err
2233 })
2234
2235 orm.RunMigration(conn, logger, "add-focusing-table", func(tx *sql.Tx) error {
2236 _, err := tx.Exec(`
2237 create table if not exists focusing (
2238 did text primary key,
2239 started text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
2240 );
2241 `)
2242 return err
2243 })
2244
2245 orm.RunMigration(conn, logger, "add-knotacl-sync-table", func(tx *sql.Tx) error {
2246 _, err := tx.Exec(`
2247 create table if not exists knotacl_sync (
2248 scope_key text primary key,
2249 synced_at text not null
2250 );
2251 `)
2252 return err
2253 })
2254
2255 orm.RunMigration(conn, logger, "add-knotacl-delta-cursor-table", func(tx *sql.Tx) error {
2256 _, err := tx.Exec(`
2257 create table if not exists knotacl_delta_cursor (
2258 scope_key text not null,
2259 subject text not null,
2260 cursor integer not null,
2261 primary key (scope_key, subject)
2262 );
2263 `)
2264 return err
2265 })
2266
2267 orm.RunMigration(conn, logger, "migrate-knots-to-knot-owned-acl", func(tx *sql.Tx) error {
2268 _, err := tx.Exec(`
2269 update registrations set needs_upgrade = 1;
2270 `)
2271 return err
2272 })
2273
2274 // several changes here
2275 // 1. remove autoincrement id for these tables
2276 // 2. remove unique constraints other than (did, rkey) to handle non-unique atproto records
2277 // 3. add generated at_uri field
2278 //
2279 // see comments below and commit message for details
2280 orm.RunMigration(conn, logger, "flexible-stars-reactions-follows-public_keys", func(tx *sql.Tx) error {
2281 // - add at_uri
2282 // - remove autoincrement id and the (did, subject) unique constraint
2283 if _, err := tx.Exec(`
2284 create table stars_new (
2285 did text not null,
2286 rkey text not null,
2287 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.feed.star' || '/' || rkey) stored,
2288
2289 subject_type text not null,
2290 subject text not null,
2291 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2292
2293 unique(did, rkey)
2294 );
2295
2296 insert into stars_new (did, rkey, subject_type, subject, created)
2297 select did, rkey, subject_type, subject, created from stars;
2298
2299 drop table stars;
2300 alter table stars_new rename to stars;
2301
2302 create index if not exists idx_stars_subject on stars(subject);
2303 create index if not exists idx_stars_subject_type on stars(subject_type);
2304 create index if not exists idx_stars_created on stars(created);
2305 create index if not exists idx_stars_did_type_created on stars(did, subject_type, created);
2306 `); err != nil {
2307 return fmt.Errorf("migrating stars: %w", err)
2308 }
2309
2310 // - add at_uri
2311 // - reacted_by_did -> did
2312 // - thread_at -> subject_at
2313 // - remove unique constraint
2314 if _, err := tx.Exec(`
2315 create table reactions_new (
2316 did text not null,
2317 rkey text not null,
2318 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.feed.reaction' || '/' || rkey) stored,
2319
2320 subject_at text not null,
2321 kind text not null,
2322 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2323
2324 unique(did, rkey)
2325 );
2326
2327 insert into reactions_new (did, rkey, subject_at, kind, created)
2328 select reacted_by_did, rkey, thread_at, kind, created from reactions;
2329
2330 drop table reactions;
2331 alter table reactions_new rename to reactions;
2332 `); err != nil {
2333 return fmt.Errorf("migrating reactions: %w", err)
2334 }
2335
2336 // - add at_uri column
2337 // - user_did -> did
2338 // - followed_at -> created
2339 // - remove unique constraint
2340 // - remove check constraint
2341 if _, err := tx.Exec(`
2342 create table follows_new (
2343 did text not null,
2344 rkey text not null,
2345 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.graph.follow' || '/' || rkey) stored,
2346
2347 subject_did text not null,
2348 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2349
2350 unique(did, rkey)
2351 );
2352
2353 insert into follows_new (did, rkey, subject_did, created)
2354 select user_did, rkey, subject_did, followed_at from follows;
2355
2356 drop table follows;
2357 alter table follows_new rename to follows;
2358
2359 create index if not exists idx_follows_subject_did on follows(subject_did);
2360 create index if not exists idx_follows_created on follows(created);
2361 `); err != nil {
2362 return fmt.Errorf("migrating follows: %w", err)
2363 }
2364
2365 // - add at_uri column
2366 // - remove foreign key relationship from repos
2367 if _, err := tx.Exec(`
2368 create table public_keys_new (
2369 did text not null,
2370 rkey text not null,
2371 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.publicKey' || '/' || rkey) stored,
2372
2373 name text not null,
2374 key text not null,
2375 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2376
2377 unique(did, rkey)
2378 );
2379
2380 insert or ignore into public_keys_new (did, rkey, name, key, created)
2381 select did, rkey, name, key, created from public_keys;
2382
2383 drop table public_keys;
2384 alter table public_keys_new rename to public_keys;
2385 `); err != nil {
2386 return fmt.Errorf("migrating public_keys: %w", err)
2387 }
2388
2389 return nil
2390 })
2391
2392 orm.RunMigration(conn, logger, "add-author-to-bluesky-posts", func(tx *sql.Tx) error {
2393 _, err := tx.Exec(`
2394 alter table bluesky_posts add column author_did text not null default '';
2395 `)
2396 return err
2397 })
2398
2399 orm.RunMigration(conn, logger, "add-issue-pull-state-tables", func(tx *sql.Tx) error {
2400 _, err := tx.Exec(`
2401 create table if not exists issue_states (
2402 id integer primary key autoincrement,
2403 did text not null,
2404 rkey text not null,
2405 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.state' || '/' || rkey) stored,
2406
2407 subject text not null,
2408 state text not null check (state in ('open', 'closed')),
2409 created_micros integer not null,
2410
2411 unique(did, rkey),
2412 foreign key (subject) references issues(at_uri) on delete cascade
2413 );
2414 create index if not exists idx_issue_states_subject on issue_states(subject);
2415
2416 create table if not exists pull_states (
2417 id integer primary key autoincrement,
2418 did text not null,
2419 rkey text not null,
2420 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.pull.status' || '/' || rkey) stored,
2421
2422 subject text not null,
2423 status text not null check (status in ('open', 'closed', 'merged')),
2424 created_micros integer not null,
2425
2426 unique(did, rkey),
2427 foreign key (subject) references pulls(at_uri) on delete cascade
2428 );
2429 create index if not exists idx_pull_states_subject on pull_states(subject);
2430
2431 create table if not exists pending_state_records (
2432 id integer primary key autoincrement,
2433 did text not null,
2434 rkey text not null,
2435 nsid text not null,
2436
2437 subject text not null,
2438 record blob not null,
2439 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2440
2441 unique(did, rkey, nsid)
2442 );
2443 create index if not exists idx_pending_state_subject on pending_state_records(subject);
2444 `)
2445 return err
2446 })
2447
2448 orm.RunMigration(conn, logger, "add-theme-preferences", func(tx *sql.Tx) error {
2449 _, err := tx.Exec(`
2450 create table if not exists theme_preferences (
2451 id integer primary key autoincrement,
2452 user_did text not null unique,
2453 theme text not null check (theme in ('auto', 'light', 'dark')),
2454 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
2455 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
2456 );
2457 create index if not exists idx_theme_preferences_user_did on theme_preferences(user_did);
2458 `)
2459 return err
2460 })
2461
2462 orm.RunMigration(conn, logger, "drop-label-ops-indexed", func(tx *sql.Tx) error {
2463 _, err := tx.Exec(`alter table label_ops drop column indexed`)
2464 return err
2465 })
2466
2467 orm.RunMigration(conn, logger, "spindle-pipeline-ownership-migration", func(tx *sql.Tx) error {
2468 _, err := tx.Exec(`
2469 update spindles set needs_upgrade = 1;
2470 `)
2471 return err
2472 })
2473
2474 orm.RunMigration(conn, logger, "add-emailed-to-notifications", func(tx *sql.Tx) error {
2475 _, err := tx.Exec(`
2476 ALTER TABLE notifications ADD COLUMN emailed INTEGER NOT NULL DEFAULT 0;
2477 CREATE INDEX IF NOT EXISTS idx_notifications_emailed
2478 ON notifications(recipient_did, emailed, created);
2479 `)
2480 return err
2481 })
2482
2483 orm.RunMigration(conn, logger, "deduped-stars-view", func(tx *sql.Tx) error {
2484 _, err := tx.Exec(`
2485 create view deduped_stars as
2486 select did, subject_type, subject, min(created) as created
2487 from stars
2488 group by did, subject;
2489 `)
2490 return err
2491 })
2492
2493 // gets rid of a few b-tree order bys in getissue queries and replaces them with search
2494 orm.RunMigration(conn, logger, "add-issue-list-indexes", func(tx *sql.Tx) error {
2495 _, err := tx.Exec(`
2496 drop index if exists idx_issues_repo_did;
2497 create index if not exists idx_issues_repo_created on issues(repo_did, created desc);
2498 create index if not exists idx_comments_subject_uri on comments(subject_uri);
2499 create index if not exists idx_label_ops_subject on label_ops(subject);
2500 `)
2501 return err
2502 })
2503
2504 return &DB{
2505 db,
2506 logger,
2507 }, nil
2508}
2509
2510func (d *DB) Close() error {
2511 return d.DB.Close()
2512}