alice.pds.demo.boltless.dev
This repository has no description
1package db
2
3import (
4 "context"
5 "database/sql"
6 "log/slog"
7 "strings"
8
9 _ "github.com/mattn/go-sqlite3"
10 "tangled.org/core/log"
11 "tangled.org/core/orm"
12)
13
14type DB struct {
15 *sql.DB
16 logger *slog.Logger
17}
18
19type Execer interface {
20 Query(query string, args ...any) (*sql.Rows, error)
21 QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
22 QueryRow(query string, args ...any) *sql.Row
23 QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
24 Exec(query string, args ...any) (sql.Result, error)
25 ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
26 Prepare(query string) (*sql.Stmt, error)
27 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
28}
29
30func Make(ctx context.Context, dbPath string) (*DB, error) {
31 // https://github.com/mattn/go-sqlite3#connection-string
32 opts := []string{
33 "_foreign_keys=1",
34 "_journal_mode=WAL",
35 "_synchronous=NORMAL",
36 "_auto_vacuum=incremental",
37 "_busy_timeout=5000",
38 }
39
40 logger := log.FromContext(ctx)
41 logger = log.SubLogger(logger, "db")
42
43 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
44 if err != nil {
45 return nil, err
46 }
47
48 conn, err := db.Conn(ctx)
49 if err != nil {
50 return nil, err
51 }
52 defer conn.Close()
53
54 _, err = conn.ExecContext(ctx, `
55 create table if not exists registrations (
56 id integer primary key autoincrement,
57 domain text not null unique,
58 did text not null,
59 secret text not null,
60 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
61 registered text
62 );
63 create table if not exists public_keys (
64 id integer primary key autoincrement,
65 did text not null,
66 name text not null,
67 key text not null,
68 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
69 unique(did, name, key)
70 );
71 create table if not exists repos (
72 id integer primary key autoincrement,
73 did text not null,
74 name text not null,
75 knot text not null,
76 rkey text not null,
77 at_uri text not null unique,
78 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
79 unique(did, name, knot, rkey)
80 );
81 create table if not exists collaborators (
82 id integer primary key autoincrement,
83 did text not null,
84 repo integer not null,
85 foreign key (repo) references repos(id) on delete cascade
86 );
87 create table if not exists follows (
88 user_did text not null,
89 subject_did text not null,
90 rkey text not null,
91 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
92 primary key (user_did, subject_did),
93 check (user_did <> subject_did)
94 );
95 create table if not exists vouches (
96 did text not null,
97 subject_did text not null,
98 cid text not null,
99 kind text not null default 'vouch',
100 reason text,
101 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
102 primary key (did, subject_did),
103 check (did <> subject_did),
104 check (kind in ('vouch', 'denounce'))
105 );
106 create table if not exists issues (
107 id integer primary key autoincrement,
108 owner_did text not null,
109 repo_at text not null,
110 issue_id integer not null,
111 title text not null,
112 body text not null,
113 open integer not null default 1,
114 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
115 issue_at text,
116 unique(repo_at, issue_id),
117 foreign key (repo_at) references repos(at_uri) on delete cascade
118 );
119 create table if not exists comments (
120 id integer primary key autoincrement,
121 owner_did text not null,
122 issue_id integer not null,
123 repo_at text not null,
124 comment_id integer not null,
125 comment_at text not null,
126 body text not null,
127 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
128 unique(issue_id, comment_id),
129 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
130 );
131 create table if not exists pulls (
132 -- identifiers
133 id integer primary key autoincrement,
134 pull_id integer not null,
135
136 -- at identifiers
137 repo_at text not null,
138 owner_did text not null,
139 rkey text not null,
140 pull_at text,
141
142 -- content
143 title text not null,
144 body text not null,
145 target_branch text not null,
146 state integer not null default 0 check (state in (0, 1, 2)), -- open, merged, closed
147
148 -- meta
149 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
150
151 -- constraints
152 unique(repo_at, pull_id),
153 foreign key (repo_at) references repos(at_uri) on delete cascade
154 );
155
156 -- every pull must have atleast 1 submission: the initial submission
157 create table if not exists pull_submissions (
158 -- identifiers
159 id integer primary key autoincrement,
160 pull_id integer not null,
161
162 -- at identifiers
163 repo_at text not null,
164
165 -- content, these are immutable, and require a resubmission to update
166 round_number integer not null default 0,
167 patch text,
168
169 -- meta
170 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
171
172 -- constraints
173 unique(repo_at, pull_id, round_number),
174 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade
175 );
176
177 create table if not exists pull_comments (
178 -- identifiers
179 id integer primary key autoincrement,
180 pull_id integer not null,
181 submission_id integer not null,
182
183 -- at identifiers
184 repo_at text not null,
185 owner_did text not null,
186 comment_at text not null,
187
188 -- content
189 body text not null,
190
191 -- meta
192 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
193
194 -- constraints
195 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade,
196 foreign key (submission_id) references pull_submissions(id) on delete cascade
197 );
198
199 create table if not exists _jetstream (
200 id integer primary key autoincrement,
201 last_time_us integer not null
202 );
203
204 create table if not exists repo_issue_seqs (
205 repo_at text primary key,
206 next_issue_id integer not null default 1
207 );
208
209 create table if not exists repo_pull_seqs (
210 repo_at text primary key,
211 next_pull_id integer not null default 1
212 );
213
214 create table if not exists stars (
215 id integer primary key autoincrement,
216 starred_by_did text not null,
217 repo_at text not null,
218 rkey text not null,
219 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
220 foreign key (repo_at) references repos(at_uri) on delete cascade,
221 unique(starred_by_did, repo_at)
222 );
223
224 create table if not exists reactions (
225 id integer primary key autoincrement,
226 reacted_by_did text not null,
227 thread_at text not null,
228 kind text not null,
229 rkey text not null,
230 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
231 unique(reacted_by_did, thread_at, kind)
232 );
233
234 create table if not exists emails (
235 id integer primary key autoincrement,
236 did text not null,
237 email text not null,
238 verified integer not null default 0,
239 verification_code text not null,
240 last_sent text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
241 is_primary integer not null default 0,
242 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
243 unique(did, email)
244 );
245
246 create table if not exists artifacts (
247 -- id
248 id integer primary key autoincrement,
249 did text not null,
250 rkey text not null,
251
252 -- meta
253 repo_at text not null,
254 tag binary(20) not null,
255 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
256
257 -- data
258 blob_cid text not null,
259 name text not null,
260 size integer not null default 0,
261 mimetype string not null default "*/*",
262
263 -- constraints
264 unique(did, rkey), -- record must be unique
265 unique(repo_at, tag, name), -- for a given tag object, each file must be unique
266 foreign key (repo_at) references repos(at_uri) on delete cascade
267 );
268
269 create table if not exists profile (
270 -- id
271 id integer primary key autoincrement,
272 did text not null,
273
274 -- data
275 description text not null,
276 include_bluesky integer not null default 0,
277 location text,
278
279 -- constraints
280 unique(did)
281 );
282 create table if not exists profile_links (
283 -- id
284 id integer primary key autoincrement,
285 did text not null,
286
287 -- data
288 link text not null,
289
290 -- constraints
291 foreign key (did) references profile(did) on delete cascade
292 );
293 create table if not exists profile_stats (
294 -- id
295 id integer primary key autoincrement,
296 did text not null,
297
298 -- data
299 kind text not null check (kind in (
300 "merged-pull-request-count",
301 "closed-pull-request-count",
302 "open-pull-request-count",
303 "open-issue-count",
304 "closed-issue-count",
305 "repository-count"
306 )),
307
308 -- constraints
309 foreign key (did) references profile(did) on delete cascade
310 );
311 create table if not exists profile_pinned_repositories (
312 -- id
313 id integer primary key autoincrement,
314 did text not null,
315
316 -- data
317 at_uri text not null,
318
319 -- constraints
320 unique(did, at_uri),
321 foreign key (did) references profile(did) on delete cascade,
322 foreign key (at_uri) references repos(at_uri) on delete cascade
323 );
324
325 create table if not exists oauth_requests (
326 id integer primary key autoincrement,
327 auth_server_iss text not null,
328 state text not null,
329 did text not null,
330 handle text not null,
331 pds_url text not null,
332 pkce_verifier text not null,
333 dpop_auth_server_nonce text not null,
334 dpop_private_jwk text not null
335 );
336
337 create table if not exists oauth_sessions (
338 id integer primary key autoincrement,
339 did text not null,
340 handle text not null,
341 pds_url text not null,
342 auth_server_iss text not null,
343 access_jwt text not null,
344 refresh_jwt text not null,
345 dpop_pds_nonce text,
346 dpop_auth_server_nonce text not null,
347 dpop_private_jwk text not null,
348 expiry text not null
349 );
350
351 create table if not exists punchcard (
352 did text not null,
353 date text not null, -- yyyy-mm-dd
354 count integer,
355 primary key (did, date)
356 );
357
358 create table if not exists spindles (
359 id integer primary key autoincrement,
360 owner text not null,
361 instance text not null,
362 verified text, -- time of verification
363 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
364
365 unique(owner, instance)
366 );
367
368 create table if not exists spindle_members (
369 -- identifiers for the record
370 id integer primary key autoincrement,
371 did text not null,
372 rkey text not null,
373
374 -- data
375 instance text not null,
376 subject text not null,
377 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
378
379 -- constraints
380 unique (did, instance, subject)
381 );
382
383 create table if not exists pipelines (
384 -- identifiers
385 id integer primary key autoincrement,
386 knot text not null,
387 rkey text not null,
388
389 repo_owner text not null,
390 repo_name text not null,
391
392 -- every pipeline must be associated with exactly one commit
393 sha text not null check (length(sha) = 40),
394 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
395
396 -- trigger data
397 trigger_id integer not null,
398
399 unique(knot, rkey),
400 foreign key (trigger_id) references triggers(id) on delete cascade
401 );
402
403 create table if not exists triggers (
404 -- primary key
405 id integer primary key autoincrement,
406
407 -- top-level fields
408 kind text not null,
409
410 -- pushTriggerData fields
411 push_ref text,
412 push_new_sha text check (length(push_new_sha) = 40),
413 push_old_sha text check (length(push_old_sha) = 40),
414
415 -- pullRequestTriggerData fields
416 pr_source_branch text,
417 pr_target_branch text,
418 pr_source_sha text check (length(pr_source_sha) = 40),
419 pr_action text
420 );
421
422 create table if not exists pipeline_statuses (
423 -- identifiers
424 id integer primary key autoincrement,
425 spindle text not null,
426 rkey text not null,
427
428 -- referenced pipeline. these form the (did, rkey) pair
429 pipeline_knot text not null,
430 pipeline_rkey text not null,
431
432 -- content
433 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
434 workflow text not null,
435 status text not null,
436 error text,
437 exit_code integer not null default 0,
438
439 unique (spindle, rkey),
440 foreign key (pipeline_knot, pipeline_rkey)
441 references pipelines (knot, rkey)
442 on delete cascade
443 );
444
445 create table if not exists repo_languages (
446 -- identifiers
447 id integer primary key autoincrement,
448
449 -- repo identifiers
450 repo_at text not null,
451 ref text not null,
452 is_default_ref integer not null default 0,
453
454 -- language breakdown
455 language text not null,
456 bytes integer not null check (bytes >= 0),
457
458 unique(repo_at, ref, language)
459 );
460
461 create table if not exists signups_inflight (
462 id integer primary key autoincrement,
463 email text not null unique,
464 invite_code text not null,
465 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
466 );
467
468 create table if not exists strings (
469 -- identifiers
470 did text not null,
471 rkey text not null,
472
473 -- content
474 filename text not null,
475 description text,
476 content text not null,
477 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
478 edited text,
479
480 primary key (did, rkey)
481 );
482
483 create table if not exists label_definitions (
484 -- identifiers
485 id integer primary key autoincrement,
486 did text not null,
487 rkey text not null,
488 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.definition' || '/' || rkey) stored,
489
490 -- content
491 name text not null,
492 value_type text not null check (value_type in (
493 "null",
494 "boolean",
495 "integer",
496 "string"
497 )),
498 value_format text not null default "any",
499 value_enum text, -- comma separated list
500 scope text not null, -- comma separated list of nsid
501 color text,
502 multiple integer not null default 0,
503 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
504
505 -- constraints
506 unique (did, rkey)
507 unique (at_uri)
508 );
509
510 -- ops are flattened, a record may contain several additions and deletions, but the table will include one row per add/del
511 create table if not exists label_ops (
512 -- identifiers
513 id integer primary key autoincrement,
514 did text not null,
515 rkey text not null,
516 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.op' || '/' || rkey) stored,
517
518 -- content
519 subject text not null,
520 operation text not null check (operation in ("add", "del")),
521 operand_key text not null,
522 operand_value text not null,
523 -- we need two time values: performed is declared by the user, indexed is calculated by the av
524 performed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
525 indexed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
526
527 -- constraints
528 -- traditionally (did, rkey) pair should be unique, but not in this case
529 -- operand_key should reference a label definition
530 foreign key (operand_key) references label_definitions (at_uri) on delete cascade,
531 unique (did, rkey, subject, operand_key, operand_value)
532 );
533
534 create table if not exists repo_labels (
535 -- identifiers
536 id integer primary key autoincrement,
537
538 -- repo identifiers
539 repo_at text not null,
540
541 -- label to subscribe to
542 label_at text not null,
543
544 unique (repo_at, label_at)
545 );
546
547 create table if not exists notifications (
548 id integer primary key autoincrement,
549 recipient_did text not null,
550 actor_did text not null,
551 type text not null,
552 entity_type text not null,
553 entity_id text not null,
554 read integer not null default 0,
555 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
556 repo_id integer references repos(id),
557 issue_id integer references issues(id),
558 pull_id integer references pulls(id)
559 );
560
561 create table if not exists notification_preferences (
562 id integer primary key autoincrement,
563 user_did text not null unique,
564 repo_starred integer not null default 1,
565 issue_created integer not null default 1,
566 issue_commented integer not null default 1,
567 pull_created integer not null default 1,
568 pull_commented integer not null default 1,
569 followed integer not null default 1,
570 pull_merged integer not null default 1,
571 issue_closed integer not null default 1,
572 email_notifications integer not null default 0
573 );
574
575 create table if not exists reference_links (
576 id integer primary key autoincrement,
577 from_at text not null,
578 to_at text not null,
579 unique (from_at, to_at)
580 );
581
582 create table if not exists webhooks (
583 id integer primary key autoincrement,
584 repo_at text not null,
585 url text not null,
586 secret text,
587 active integer not null default 1,
588 events text not null, -- comma-separated list of events
589 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
590 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
591
592 foreign key (repo_at) references repos(at_uri) on delete cascade
593 );
594
595 create table if not exists webhook_deliveries (
596 id integer primary key autoincrement,
597 webhook_id integer not null,
598 event text not null,
599 delivery_id text not null,
600 url text not null,
601 request_body text not null,
602 response_code integer,
603 response_body text,
604 success integer not null default 0,
605 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
606
607 foreign key (webhook_id) references webhooks(id) on delete cascade
608 );
609
610 create table if not exists bluesky_posts (
611 rkey text primary key,
612 text text not null,
613 created_at text not null,
614 langs text,
615 facets text,
616 embed text,
617 like_count integer not null default 0,
618 reply_count integer not null default 0,
619 repost_count integer not null default 0,
620 quote_count integer not null default 0
621 );
622
623 create table if not exists domain_claims (
624 id integer primary key autoincrement,
625 did text not null unique,
626 domain text not null unique,
627 deleted text -- timestamp when the domain was released/unclaimed; null means actively claimed
628 );
629
630 create table if not exists repo_sites (
631 id integer primary key autoincrement,
632 repo_at text not null unique,
633 branch text not null,
634 dir text not null default '/',
635 is_index integer not null default 0,
636 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
637 updated text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
638 foreign key (repo_at) references repos(at_uri) on delete cascade
639 );
640
641 create table if not exists site_deploys (
642 id integer primary key autoincrement,
643 repo_at text not null,
644 branch text not null,
645 dir text not null default '/',
646 commit_sha text not null default '',
647 status text not null check (status in ('success', 'failure')),
648 trigger text not null check (trigger in ('config_change', 'push')),
649 error text not null default '',
650 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
651 foreign key (repo_at) references repos(at_uri) on delete cascade
652 );
653
654 create table if not exists punchcard_preferences (
655 id integer primary key autoincrement,
656 user_did text not null unique,
657 hide_mine integer default 0,
658 hide_others integer default 0
659 );
660
661 create table if not exists newsletter_preferences (
662 id integer primary key autoincrement,
663 user_did text not null unique,
664 status text not null check (status in ('subscribed', 'dismissed')),
665 email text,
666 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
667 );
668
669 create table if not exists vouch_evidences (
670 id integer primary key autoincrement,
671 vouch_id integer not null,
672 at_uri text not null,
673 unique(vouch_id, at_uri),
674 foreign key (vouch_id) references vouches(id) on delete cascade
675 );
676
677 create table if not exists migrations (
678 id integer primary key autoincrement,
679 name text unique
680 );
681
682 -- indexes for better performance
683 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
684 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
685 create index if not exists idx_references_from_at on reference_links(from_at);
686 create index if not exists idx_references_to_at on reference_links(to_at);
687 create index if not exists idx_webhooks_repo_at on webhooks(repo_at);
688 create index if not exists idx_webhook_deliveries_webhook_id on webhook_deliveries(webhook_id);
689 create index if not exists idx_site_deploys_repo_at on site_deploys(repo_at);
690 create index if not exists idx_newsletter_prefs_user_did on newsletter_preferences(user_did);
691 `)
692 if err != nil {
693 return nil, err
694 }
695
696 // run migrations
697 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
698 tx.Exec(`
699 alter table repos add column description text check (length(description) <= 200);
700 `)
701 return nil
702 })
703
704 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
705 // add unconstrained column
706 _, err := tx.Exec(`
707 alter table public_keys
708 add column rkey text;
709 `)
710 if err != nil {
711 return err
712 }
713
714 // backfill
715 _, err = tx.Exec(`
716 update public_keys
717 set rkey = ''
718 where rkey is null;
719 `)
720 if err != nil {
721 return err
722 }
723
724 return nil
725 })
726
727 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
728 _, err := tx.Exec(`
729 alter table comments drop column comment_at;
730 alter table comments add column rkey text;
731 `)
732 return err
733 })
734
735 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
736 _, err := tx.Exec(`
737 alter table comments add column deleted text; -- timestamp
738 alter table comments add column edited text; -- timestamp
739 `)
740 return err
741 })
742
743 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
744 _, err := tx.Exec(`
745 alter table pulls add column source_branch text;
746 alter table pulls add column source_repo_at text;
747 alter table pull_submissions add column source_rev text;
748 `)
749 return err
750 })
751
752 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
753 _, err := tx.Exec(`
754 alter table repos add column source text;
755 `)
756 return err
757 })
758
759 // disable foreign-keys for the next migration
760 // NOTE: this cannot be done in a transaction, so it is run outside [0]
761 //
762 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
763 conn.ExecContext(ctx, "pragma foreign_keys = off;")
764 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
765 _, err := tx.Exec(`
766 create table pulls_new (
767 -- identifiers
768 id integer primary key autoincrement,
769 pull_id integer not null,
770
771 -- at identifiers
772 repo_at text not null,
773 owner_did text not null,
774 rkey text not null,
775
776 -- content
777 title text not null,
778 body text not null,
779 target_branch text not null,
780 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
781
782 -- source info
783 source_branch text,
784 source_repo_at text,
785
786 -- stacking
787 stack_id text,
788 change_id text,
789 parent_change_id text,
790
791 -- meta
792 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
793
794 -- constraints
795 unique(repo_at, pull_id),
796 foreign key (repo_at) references repos(at_uri) on delete cascade
797 );
798
799 insert into pulls_new (
800 id, pull_id,
801 repo_at, owner_did, rkey,
802 title, body, target_branch, state,
803 source_branch, source_repo_at,
804 created
805 )
806 select
807 id, pull_id,
808 repo_at, owner_did, rkey,
809 title, body, target_branch, state,
810 source_branch, source_repo_at,
811 created
812 FROM pulls;
813
814 drop table pulls;
815 alter table pulls_new rename to pulls;
816 `)
817 return err
818 })
819 conn.ExecContext(ctx, "pragma foreign_keys = on;")
820
821 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
822 tx.Exec(`
823 alter table repos add column spindle text;
824 `)
825 return nil
826 })
827
828 // drop all knot secrets, add unique constraint to knots
829 //
830 // knots will henceforth use service auth for signed requests
831 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
832 _, err := tx.Exec(`
833 create table registrations_new (
834 id integer primary key autoincrement,
835 domain text not null,
836 did text not null,
837 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
838 registered text,
839 read_only integer not null default 0,
840 unique(domain, did)
841 );
842
843 insert into registrations_new (id, domain, did, created, registered, read_only)
844 select id, domain, did, created, registered, 1 from registrations
845 where registered is not null;
846
847 drop table registrations;
848 alter table registrations_new rename to registrations;
849 `)
850 return err
851 })
852
853 // recreate and add rkey + created columns with default constraint
854 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
855 // create new table
856 // - repo_at instead of repo integer
857 // - rkey field
858 // - created field
859 _, err := tx.Exec(`
860 create table collaborators_new (
861 -- identifiers for the record
862 id integer primary key autoincrement,
863 did text not null,
864 rkey text,
865
866 -- content
867 subject_did text not null,
868 repo_at text not null,
869
870 -- meta
871 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
872
873 -- constraints
874 foreign key (repo_at) references repos(at_uri) on delete cascade
875 )
876 `)
877 if err != nil {
878 return err
879 }
880
881 // copy data
882 _, err = tx.Exec(`
883 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
884 select
885 c.id,
886 r.did,
887 '',
888 c.did,
889 r.at_uri
890 from collaborators c
891 join repos r on c.repo = r.id
892 `)
893 if err != nil {
894 return err
895 }
896
897 // drop old table
898 _, err = tx.Exec(`drop table collaborators`)
899 if err != nil {
900 return err
901 }
902
903 // rename new table
904 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
905 return err
906 })
907
908 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
909 _, err := tx.Exec(`
910 alter table issues add column rkey text not null default '';
911
912 -- get last url section from issue_at and save to rkey column
913 update issues
914 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
915 `)
916 return err
917 })
918
919 // repurpose the read-only column to "needs-upgrade"
920 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
921 _, err := tx.Exec(`
922 alter table registrations rename column read_only to needs_upgrade;
923 `)
924 return err
925 })
926
927 // require all knots to upgrade after the release of total xrpc
928 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
929 _, err := tx.Exec(`
930 update registrations set needs_upgrade = 1;
931 `)
932 return err
933 })
934
935 // require all knots to upgrade after the release of total xrpc
936 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
937 _, err := tx.Exec(`
938 alter table spindles add column needs_upgrade integer not null default 0;
939 `)
940 return err
941 })
942
943 // remove issue_at from issues and replace with generated column
944 //
945 // this requires a full table recreation because stored columns
946 // cannot be added via alter
947 //
948 // couple other changes:
949 // - columns renamed to be more consistent
950 // - adds edited and deleted fields
951 //
952 // disable foreign-keys for the next migration
953 conn.ExecContext(ctx, "pragma foreign_keys = off;")
954 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
955 _, err := tx.Exec(`
956 create table if not exists issues_new (
957 -- identifiers
958 id integer primary key autoincrement,
959 did text not null,
960 rkey text not null,
961 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
962
963 -- at identifiers
964 repo_at text not null,
965
966 -- content
967 issue_id integer not null,
968 title text not null,
969 body text not null,
970 open integer not null default 1,
971 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
972 edited text, -- timestamp
973 deleted text, -- timestamp
974
975 unique(did, rkey),
976 unique(repo_at, issue_id),
977 unique(at_uri),
978 foreign key (repo_at) references repos(at_uri) on delete cascade
979 );
980 `)
981 if err != nil {
982 return err
983 }
984
985 // transfer data
986 _, err = tx.Exec(`
987 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
988 select
989 i.id,
990 i.owner_did,
991 i.rkey,
992 i.repo_at,
993 i.issue_id,
994 i.title,
995 i.body,
996 i.open,
997 i.created
998 from issues i;
999 `)
1000 if err != nil {
1001 return err
1002 }
1003
1004 // drop old table
1005 _, err = tx.Exec(`drop table issues`)
1006 if err != nil {
1007 return err
1008 }
1009
1010 // rename new table
1011 _, err = tx.Exec(`alter table issues_new rename to issues`)
1012 return err
1013 })
1014 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1015
1016 // - renames the comments table to 'issue_comments'
1017 // - rework issue comments to update constraints:
1018 // * unique(did, rkey)
1019 // * remove comment-id and just use the global ID
1020 // * foreign key (repo_at, issue_id)
1021 // - new columns
1022 // * column "reply_to" which can be any other comment
1023 // * column "at-uri" which is a generated column
1024 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
1025 _, err := tx.Exec(`
1026 create table if not exists issue_comments (
1027 -- identifiers
1028 id integer primary key autoincrement,
1029 did text not null,
1030 rkey text,
1031 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
1032
1033 -- at identifiers
1034 issue_at text not null,
1035 reply_to text, -- at_uri of parent comment
1036
1037 -- content
1038 body text not null,
1039 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1040 edited text,
1041 deleted text,
1042
1043 -- constraints
1044 unique(did, rkey),
1045 unique(at_uri),
1046 foreign key (issue_at) references issues(at_uri) on delete cascade
1047 );
1048 `)
1049 if err != nil {
1050 return err
1051 }
1052
1053 // transfer data
1054 _, err = tx.Exec(`
1055 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
1056 select
1057 c.id,
1058 c.owner_did,
1059 c.rkey,
1060 i.at_uri, -- get at_uri from issues table
1061 c.body,
1062 c.created,
1063 c.edited,
1064 c.deleted
1065 from comments c
1066 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
1067 `)
1068 if err != nil {
1069 return err
1070 }
1071
1072 // drop old table
1073 _, err = tx.Exec(`drop table comments`)
1074 return err
1075 })
1076
1077 // add generated at_uri column to pulls table
1078 //
1079 // this requires a full table recreation because stored columns
1080 // cannot be added via alter
1081 //
1082 // disable foreign-keys for the next migration
1083 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1084 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
1085 _, err := tx.Exec(`
1086 create table if not exists pulls_new (
1087 -- identifiers
1088 id integer primary key autoincrement,
1089 pull_id integer not null,
1090 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
1091
1092 -- at identifiers
1093 repo_at text not null,
1094 owner_did text not null,
1095 rkey text not null,
1096
1097 -- content
1098 title text not null,
1099 body text not null,
1100 target_branch text not null,
1101 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
1102
1103 -- source info
1104 source_branch text,
1105 source_repo_at text,
1106
1107 -- stacking
1108 stack_id text,
1109 change_id text,
1110 parent_change_id text,
1111
1112 -- meta
1113 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1114
1115 -- constraints
1116 unique(repo_at, pull_id),
1117 unique(at_uri),
1118 foreign key (repo_at) references repos(at_uri) on delete cascade
1119 );
1120 `)
1121 if err != nil {
1122 return err
1123 }
1124
1125 // transfer data
1126 _, err = tx.Exec(`
1127 insert into pulls_new (
1128 id, pull_id, repo_at, owner_did, rkey,
1129 title, body, target_branch, state,
1130 source_branch, source_repo_at,
1131 stack_id, change_id, parent_change_id,
1132 created
1133 )
1134 select
1135 id, pull_id, repo_at, owner_did, rkey,
1136 title, body, target_branch, state,
1137 source_branch, source_repo_at,
1138 stack_id, change_id, parent_change_id,
1139 created
1140 from pulls;
1141 `)
1142 if err != nil {
1143 return err
1144 }
1145
1146 // drop old table
1147 _, err = tx.Exec(`drop table pulls`)
1148 if err != nil {
1149 return err
1150 }
1151
1152 // rename new table
1153 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1154 return err
1155 })
1156 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1157
1158 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1159 //
1160 // this requires a full table recreation because stored columns
1161 // cannot be added via alter
1162 //
1163 // disable foreign-keys for the next migration
1164 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1165 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1166 _, err := tx.Exec(`
1167 create table if not exists pull_submissions_new (
1168 -- identifiers
1169 id integer primary key autoincrement,
1170 pull_at text not null,
1171
1172 -- content, these are immutable, and require a resubmission to update
1173 round_number integer not null default 0,
1174 patch text,
1175 source_rev text,
1176
1177 -- meta
1178 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1179
1180 -- constraints
1181 unique(pull_at, round_number),
1182 foreign key (pull_at) references pulls(at_uri) on delete cascade
1183 );
1184 `)
1185 if err != nil {
1186 return err
1187 }
1188
1189 // transfer data, constructing pull_at from pulls table
1190 _, err = tx.Exec(`
1191 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1192 select
1193 ps.id,
1194 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1195 ps.round_number,
1196 ps.patch,
1197 ps.created
1198 from pull_submissions ps
1199 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1200 `)
1201 if err != nil {
1202 return err
1203 }
1204
1205 // drop old table
1206 _, err = tx.Exec(`drop table pull_submissions`)
1207 if err != nil {
1208 return err
1209 }
1210
1211 // rename new table
1212 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1213 return err
1214 })
1215 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1216
1217 // knots may report the combined patch for a comparison, we can store that on the appview side
1218 // (but not on the pds record), because calculating the combined patch requires a git index
1219 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1220 _, err := tx.Exec(`
1221 alter table pull_submissions add column combined text;
1222 `)
1223 return err
1224 })
1225
1226 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1227 _, err := tx.Exec(`
1228 alter table profile add column pronouns text;
1229 `)
1230 return err
1231 })
1232
1233 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1234 _, err := tx.Exec(`
1235 alter table repos add column website text;
1236 alter table repos add column topics text;
1237 `)
1238 return err
1239 })
1240
1241 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1242 _, err := tx.Exec(`
1243 alter table notification_preferences add column user_mentioned integer not null default 1;
1244 `)
1245 return err
1246 })
1247
1248 // remove the foreign key constraints from stars.
1249 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1250 _, err := tx.Exec(`
1251 create table stars_new (
1252 id integer primary key autoincrement,
1253 did text not null,
1254 rkey text not null,
1255
1256 subject_at text not null,
1257
1258 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1259 unique(did, rkey),
1260 unique(did, subject_at)
1261 );
1262
1263 insert into stars_new (
1264 id,
1265 did,
1266 rkey,
1267 subject_at,
1268 created
1269 )
1270 select
1271 id,
1272 starred_by_did,
1273 rkey,
1274 repo_at,
1275 created
1276 from stars;
1277
1278 drop table stars;
1279 alter table stars_new rename to stars;
1280
1281 create index if not exists idx_stars_created on stars(created);
1282 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1283 `)
1284 return err
1285 })
1286
1287 orm.RunMigration(conn, logger, "add-avatar-to-profile", func(tx *sql.Tx) error {
1288 _, err := tx.Exec(`
1289 alter table profile add column avatar text;
1290 `)
1291 return err
1292 })
1293
1294 orm.RunMigration(conn, logger, "remove-profile-stats-column-constraint", func(tx *sql.Tx) error {
1295 _, err := tx.Exec(`
1296 -- create new table without the check constraint
1297 create table profile_stats_new (
1298 id integer primary key autoincrement,
1299 did text not null,
1300 kind text not null, -- no constraint this time
1301 foreign key (did) references profile(did) on delete cascade
1302 );
1303
1304 -- copy data from old table
1305 insert into profile_stats_new (id, did, kind)
1306 select id, did, kind
1307 from profile_stats;
1308
1309 -- drop old table
1310 drop table profile_stats;
1311
1312 -- rename new table
1313 alter table profile_stats_new rename to profile_stats;
1314 `)
1315 return err
1316 })
1317
1318 orm.RunMigration(conn, logger, "add-preferred-handle-profile", func(tx *sql.Tx) error {
1319 _, err := tx.Exec(`
1320 alter table profile add column preferred_handle text;
1321 `)
1322 return err
1323 })
1324
1325 orm.RunMigration(conn, logger, "add-repo-did-column", func(tx *sql.Tx) error {
1326 _, err := tx.Exec(`
1327 alter table repos add column repo_did text;
1328 create unique index if not exists idx_repos_repo_did on repos(repo_did);
1329 `)
1330 return err
1331 })
1332
1333 orm.RunMigration(conn, logger, "add-pds-rewrite-status", func(tx *sql.Tx) error {
1334 _, err := tx.Exec(`
1335 create table if not exists pds_rewrite_status (
1336 id integer primary key autoincrement,
1337 user_did text not null,
1338 repo_did text not null,
1339 record_nsid text not null,
1340 record_rkey text not null,
1341 old_repo_at text not null,
1342 status text not null default 'pending',
1343 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1344 unique(user_did, record_nsid, record_rkey)
1345 );
1346 create index if not exists idx_pds_rewrite_user on pds_rewrite_status(user_did, status);
1347 `)
1348 return err
1349 })
1350
1351 orm.RunMigration(conn, logger, "add-pipelines-repo-did", func(tx *sql.Tx) error {
1352 _, err := tx.Exec(`
1353 alter table pipelines add column repo_did text;
1354 create index if not exists idx_pipelines_repo_did on pipelines(repo_did);
1355 `)
1356 return err
1357 })
1358
1359 orm.RunMigration(conn, logger, "migrate-knots-to-repo-dids", func(tx *sql.Tx) error {
1360 _, err := tx.Exec(`update registrations set needs_upgrade = 1`)
1361 return err
1362 })
1363
1364 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1365 orm.RunMigration(conn, logger, "drop-pinned-repos-at-uri-fk", func(tx *sql.Tx) error {
1366 _, err := tx.Exec(`
1367 create table if not exists profile_pinned_repositories_new (
1368 id integer primary key autoincrement,
1369 did text not null,
1370 pin text not null,
1371
1372 unique(did, pin),
1373 foreign key (did) references profile(did) on delete cascade
1374 );
1375
1376 insert into profile_pinned_repositories_new (id, did, pin)
1377 select id, did, at_uri from profile_pinned_repositories;
1378
1379 drop table profile_pinned_repositories;
1380
1381 alter table profile_pinned_repositories_new rename to profile_pinned_repositories;
1382 `)
1383 return err
1384 })
1385 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1386
1387 orm.RunMigration(conn, logger, "reset-profile-pin-rewrites", func(tx *sql.Tx) error {
1388 _, err := tx.Exec(`
1389 update pds_rewrite_status
1390 set status = 'pending',
1391 updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1392 where record_nsid = 'sh.tangled.actor.profile'
1393 and status = 'done'
1394 `)
1395 return err
1396 })
1397
1398 orm.RunMigration(conn, logger, "add-blob-data-to-pull-submissions", func(tx *sql.Tx) error {
1399 _, err := tx.Exec(`
1400 alter table pull_submissions add column patch_blob_ref text;
1401 alter table pull_submissions add column patch_blob_mime text;
1402 alter table pull_submissions add column patch_blob_size integer;
1403 `)
1404 return err
1405 })
1406
1407 orm.RunMigration(conn, logger, "replace-parent-change-id-with-aturi", func(tx *sql.Tx) error {
1408 // add new column
1409 _, err := tx.Exec(`
1410 alter table pulls add column dependent_on text;
1411 `)
1412 if err != nil {
1413 return err
1414 }
1415
1416 // populate dependent_on with at_uri of the parent
1417 _, err = tx.Exec(`
1418 update pulls
1419 set dependent_on = (
1420 select at_uri
1421 from pulls as parent
1422 where parent.stack_id = pulls.stack_id
1423 and parent.change_id = pulls.parent_change_id
1424 )
1425 where parent_change_id is not null;
1426 `)
1427 if err != nil {
1428 return err
1429 }
1430
1431 // drop old columns
1432 _, err = tx.Exec(`
1433 alter table pulls drop column parent_change_id;
1434 alter table pulls drop column stack_id;
1435 `)
1436
1437 return err
1438 })
1439
1440 orm.RunMigration(conn, logger, "add-pds-migration", func(tx *sql.Tx) error {
1441 _, err := tx.Exec(`
1442 create table if not exists pds_migration (
1443 name text not null,
1444
1445 -- record at_uri
1446 did text not null,
1447 collection text not null,
1448 rkey text not null,
1449
1450 status text not null default 'pending',
1451 error_msg text,
1452 retry_count integer not null default 0,
1453 retry_after integer not null default 0,
1454 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1455
1456 unique(name, did, collection, rkey)
1457 );
1458 `)
1459 return err
1460 })
1461
1462 orm.RunMigration(conn, logger, "unify-pds-record-migration-table", func(tx *sql.Tx) error {
1463 _, err := tx.Exec(`
1464 insert into pds_migration (
1465 name,
1466 did,
1467 collection,
1468 rkey,
1469 status,
1470 updated_at
1471 )
1472 select
1473 'add-repo-did',
1474 user_did,
1475 record_nsid,
1476 record_rkey,
1477 status,
1478 updated_at
1479 from pds_rewrite_status;
1480
1481 drop table pds_rewrite_status;
1482 `)
1483 return err
1484 })
1485
1486 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1487 orm.RunMigration(conn, logger, "add-id-to-vouches", func(tx *sql.Tx) error {
1488 _, err := tx.Exec(`
1489 create table vouches_new (
1490 id integer primary key autoincrement,
1491 did text not null,
1492 subject_did text not null,
1493 cid text not null,
1494 kind text not null default 'vouch',
1495 reason text,
1496 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1497 unique(did, subject_did),
1498 check (did <> subject_did),
1499 check (kind in ('vouch', 'denounce'))
1500 );
1501
1502 insert into vouches_new (did, subject_did, cid, kind, reason, created_at)
1503 select did, subject_did, cid, kind, reason, created_at
1504 from vouches;
1505
1506 drop table vouches;
1507 alter table vouches_new rename to vouches;
1508 `)
1509 return err
1510 })
1511 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1512
1513 return &DB{
1514 db,
1515 logger,
1516 }, nil
1517}
1518
1519func (d *DB) Close() error {
1520 return d.DB.Close()
1521}