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