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