julien.rbrt.fr
forked from
tangled.org/core
Monorepo for Tangled — https://tangled.org
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 }
38
39 logger := log.FromContext(ctx)
40 logger = log.SubLogger(logger, "db")
41
42 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
43 if err != nil {
44 return nil, err
45 }
46
47 conn, err := db.Conn(ctx)
48 if err != nil {
49 return nil, err
50 }
51 defer conn.Close()
52
53 _, err = conn.ExecContext(ctx, `
54 create table if not exists registrations (
55 id integer primary key autoincrement,
56 domain text not null unique,
57 did text not null,
58 secret text not null,
59 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
60 registered text
61 );
62 create table if not exists public_keys (
63 id integer primary key autoincrement,
64 did text not null,
65 name text not null,
66 key text not null,
67 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
68 unique(did, name, key)
69 );
70 create table if not exists repos (
71 id integer primary key autoincrement,
72 did text not null,
73 name text not null,
74 knot text not null,
75 rkey text not null,
76 at_uri text not null unique,
77 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
78 unique(did, name, knot, rkey)
79 );
80 create table if not exists collaborators (
81 id integer primary key autoincrement,
82 did text not null,
83 repo integer not null,
84 foreign key (repo) references repos(id) on delete cascade
85 );
86 create table if not exists follows (
87 user_did text not null,
88 subject_did text not null,
89 rkey text not null,
90 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
91 primary key (user_did, subject_did),
92 check (user_did <> subject_did)
93 );
94 create table if not exists issues (
95 id integer primary key autoincrement,
96 owner_did text not null,
97 repo_at text not null,
98 issue_id integer not null,
99 title text not null,
100 body text not null,
101 open integer not null default 1,
102 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
103 issue_at text,
104 unique(repo_at, issue_id),
105 foreign key (repo_at) references repos(at_uri) on delete cascade
106 );
107 create table if not exists comments (
108 id integer primary key autoincrement,
109 owner_did text not null,
110 issue_id integer not null,
111 repo_at text not null,
112 comment_id integer not null,
113 comment_at text not null,
114 body text not null,
115 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
116 unique(issue_id, comment_id),
117 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
118 );
119 create table if not exists pulls (
120 -- identifiers
121 id integer primary key autoincrement,
122 pull_id integer not null,
123
124 -- at identifiers
125 repo_at text not null,
126 owner_did text not null,
127 rkey text not null,
128 pull_at text,
129
130 -- content
131 title text not null,
132 body text not null,
133 target_branch text not null,
134 state integer not null default 0 check (state in (0, 1, 2)), -- open, merged, closed
135
136 -- meta
137 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
138
139 -- constraints
140 unique(repo_at, pull_id),
141 foreign key (repo_at) references repos(at_uri) on delete cascade
142 );
143
144 -- every pull must have atleast 1 submission: the initial submission
145 create table if not exists pull_submissions (
146 -- identifiers
147 id integer primary key autoincrement,
148 pull_id integer not null,
149
150 -- at identifiers
151 repo_at text not null,
152
153 -- content, these are immutable, and require a resubmission to update
154 round_number integer not null default 0,
155 patch text,
156
157 -- meta
158 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
159
160 -- constraints
161 unique(repo_at, pull_id, round_number),
162 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade
163 );
164
165 create table if not exists pull_comments (
166 -- identifiers
167 id integer primary key autoincrement,
168 pull_id integer not null,
169 submission_id integer not null,
170
171 -- at identifiers
172 repo_at text not null,
173 owner_did text not null,
174 comment_at text not null,
175
176 -- content
177 body text not null,
178
179 -- meta
180 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
181
182 -- constraints
183 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade,
184 foreign key (submission_id) references pull_submissions(id) on delete cascade
185 );
186
187 create table if not exists _jetstream (
188 id integer primary key autoincrement,
189 last_time_us integer not null
190 );
191
192 create table if not exists repo_issue_seqs (
193 repo_at text primary key,
194 next_issue_id integer not null default 1
195 );
196
197 create table if not exists repo_pull_seqs (
198 repo_at text primary key,
199 next_pull_id integer not null default 1
200 );
201
202 create table if not exists stars (
203 id integer primary key autoincrement,
204 starred_by_did text not null,
205 repo_at text not null,
206 rkey text not null,
207 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
208 foreign key (repo_at) references repos(at_uri) on delete cascade,
209 unique(starred_by_did, repo_at)
210 );
211
212 create table if not exists reactions (
213 id integer primary key autoincrement,
214 reacted_by_did text not null,
215 thread_at text not null,
216 kind text not null,
217 rkey text not null,
218 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
219 unique(reacted_by_did, thread_at, kind)
220 );
221
222 create table if not exists emails (
223 id integer primary key autoincrement,
224 did text not null,
225 email text not null,
226 verified integer not null default 0,
227 verification_code text not null,
228 last_sent text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
229 is_primary integer not null default 0,
230 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
231 unique(did, email)
232 );
233
234 create table if not exists artifacts (
235 -- id
236 id integer primary key autoincrement,
237 did text not null,
238 rkey text not null,
239
240 -- meta
241 repo_at text not null,
242 tag binary(20) not null,
243 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
244
245 -- data
246 blob_cid text not null,
247 name text not null,
248 size integer not null default 0,
249 mimetype string not null default "*/*",
250
251 -- constraints
252 unique(did, rkey), -- record must be unique
253 unique(repo_at, tag, name), -- for a given tag object, each file must be unique
254 foreign key (repo_at) references repos(at_uri) on delete cascade
255 );
256
257 create table if not exists profile (
258 -- id
259 id integer primary key autoincrement,
260 did text not null,
261
262 -- data
263 description text not null,
264 include_bluesky integer not null default 0,
265 location text,
266
267 -- constraints
268 unique(did)
269 );
270 create table if not exists profile_links (
271 -- id
272 id integer primary key autoincrement,
273 did text not null,
274
275 -- data
276 link text not null,
277
278 -- constraints
279 foreign key (did) references profile(did) on delete cascade
280 );
281 create table if not exists profile_stats (
282 -- id
283 id integer primary key autoincrement,
284 did text not null,
285
286 -- data
287 kind text not null check (kind in (
288 "merged-pull-request-count",
289 "closed-pull-request-count",
290 "open-pull-request-count",
291 "open-issue-count",
292 "closed-issue-count",
293 "repository-count"
294 )),
295
296 -- constraints
297 foreign key (did) references profile(did) on delete cascade
298 );
299 create table if not exists profile_pinned_repositories (
300 -- id
301 id integer primary key autoincrement,
302 did text not null,
303
304 -- data
305 at_uri text not null,
306
307 -- constraints
308 unique(did, at_uri),
309 foreign key (did) references profile(did) on delete cascade,
310 foreign key (at_uri) references repos(at_uri) on delete cascade
311 );
312
313 create table if not exists oauth_requests (
314 id integer primary key autoincrement,
315 auth_server_iss text not null,
316 state text not null,
317 did text not null,
318 handle text not null,
319 pds_url text not null,
320 pkce_verifier text not null,
321 dpop_auth_server_nonce text not null,
322 dpop_private_jwk text not null
323 );
324
325 create table if not exists oauth_sessions (
326 id integer primary key autoincrement,
327 did text not null,
328 handle text not null,
329 pds_url text not null,
330 auth_server_iss text not null,
331 access_jwt text not null,
332 refresh_jwt text not null,
333 dpop_pds_nonce text,
334 dpop_auth_server_nonce text not null,
335 dpop_private_jwk text not null,
336 expiry text not null
337 );
338
339 create table if not exists punchcard (
340 did text not null,
341 date text not null, -- yyyy-mm-dd
342 count integer,
343 primary key (did, date)
344 );
345
346 create table if not exists spindles (
347 id integer primary key autoincrement,
348 owner text not null,
349 instance text not null,
350 verified text, -- time of verification
351 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
352
353 unique(owner, instance)
354 );
355
356 create table if not exists spindle_members (
357 -- identifiers for the record
358 id integer primary key autoincrement,
359 did text not null,
360 rkey text not null,
361
362 -- data
363 instance text not null,
364 subject text not null,
365 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
366
367 -- constraints
368 unique (did, instance, subject)
369 );
370
371 create table if not exists pipelines (
372 -- identifiers
373 id integer primary key autoincrement,
374 knot text not null,
375 rkey text not null,
376
377 repo_owner text not null,
378 repo_name text not null,
379
380 -- every pipeline must be associated with exactly one commit
381 sha text not null check (length(sha) = 40),
382 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
383
384 -- trigger data
385 trigger_id integer not null,
386
387 unique(knot, rkey),
388 foreign key (trigger_id) references triggers(id) on delete cascade
389 );
390
391 create table if not exists triggers (
392 -- primary key
393 id integer primary key autoincrement,
394
395 -- top-level fields
396 kind text not null,
397
398 -- pushTriggerData fields
399 push_ref text,
400 push_new_sha text check (length(push_new_sha) = 40),
401 push_old_sha text check (length(push_old_sha) = 40),
402
403 -- pullRequestTriggerData fields
404 pr_source_branch text,
405 pr_target_branch text,
406 pr_source_sha text check (length(pr_source_sha) = 40),
407 pr_action text
408 );
409
410 create table if not exists pipeline_statuses (
411 -- identifiers
412 id integer primary key autoincrement,
413 spindle text not null,
414 rkey text not null,
415
416 -- referenced pipeline. these form the (did, rkey) pair
417 pipeline_knot text not null,
418 pipeline_rkey text not null,
419
420 -- content
421 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
422 workflow text not null,
423 status text not null,
424 error text,
425 exit_code integer not null default 0,
426
427 unique (spindle, rkey),
428 foreign key (pipeline_knot, pipeline_rkey)
429 references pipelines (knot, rkey)
430 on delete cascade
431 );
432
433 create table if not exists repo_languages (
434 -- identifiers
435 id integer primary key autoincrement,
436
437 -- repo identifiers
438 repo_at text not null,
439 ref text not null,
440 is_default_ref integer not null default 0,
441
442 -- language breakdown
443 language text not null,
444 bytes integer not null check (bytes >= 0),
445
446 unique(repo_at, ref, language)
447 );
448
449 create table if not exists signups_inflight (
450 id integer primary key autoincrement,
451 email text not null unique,
452 invite_code text not null,
453 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
454 );
455
456 create table if not exists strings (
457 -- identifiers
458 did text not null,
459 rkey text not null,
460
461 -- content
462 filename text not null,
463 description text,
464 content text not null,
465 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
466 edited text,
467
468 primary key (did, rkey)
469 );
470
471 create table if not exists label_definitions (
472 -- identifiers
473 id integer primary key autoincrement,
474 did text not null,
475 rkey text not null,
476 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.definition' || '/' || rkey) stored,
477
478 -- content
479 name text not null,
480 value_type text not null check (value_type in (
481 "null",
482 "boolean",
483 "integer",
484 "string"
485 )),
486 value_format text not null default "any",
487 value_enum text, -- comma separated list
488 scope text not null, -- comma separated list of nsid
489 color text,
490 multiple integer not null default 0,
491 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
492
493 -- constraints
494 unique (did, rkey)
495 unique (at_uri)
496 );
497
498 -- ops are flattened, a record may contain several additions and deletions, but the table will include one row per add/del
499 create table if not exists label_ops (
500 -- identifiers
501 id integer primary key autoincrement,
502 did text not null,
503 rkey text not null,
504 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.op' || '/' || rkey) stored,
505
506 -- content
507 subject text not null,
508 operation text not null check (operation in ("add", "del")),
509 operand_key text not null,
510 operand_value text not null,
511 -- we need two time values: performed is declared by the user, indexed is calculated by the av
512 performed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
513 indexed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
514
515 -- constraints
516 -- traditionally (did, rkey) pair should be unique, but not in this case
517 -- operand_key should reference a label definition
518 foreign key (operand_key) references label_definitions (at_uri) on delete cascade,
519 unique (did, rkey, subject, operand_key, operand_value)
520 );
521
522 create table if not exists repo_labels (
523 -- identifiers
524 id integer primary key autoincrement,
525
526 -- repo identifiers
527 repo_at text not null,
528
529 -- label to subscribe to
530 label_at text not null,
531
532 unique (repo_at, label_at)
533 );
534
535 create table if not exists notifications (
536 id integer primary key autoincrement,
537 recipient_did text not null,
538 actor_did text not null,
539 type text not null,
540 entity_type text not null,
541 entity_id text not null,
542 read integer not null default 0,
543 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
544 repo_id integer references repos(id),
545 issue_id integer references issues(id),
546 pull_id integer references pulls(id)
547 );
548
549 create table if not exists notification_preferences (
550 id integer primary key autoincrement,
551 user_did text not null unique,
552 repo_starred integer not null default 1,
553 issue_created integer not null default 1,
554 issue_commented integer not null default 1,
555 pull_created integer not null default 1,
556 pull_commented integer not null default 1,
557 followed integer not null default 1,
558 pull_merged integer not null default 1,
559 issue_closed integer not null default 1,
560 email_notifications integer not null default 0
561 );
562
563 create table if not exists reference_links (
564 id integer primary key autoincrement,
565 from_at text not null,
566 to_at text not null,
567 unique (from_at, to_at)
568 );
569
570 create table if not exists migrations (
571 id integer primary key autoincrement,
572 name text unique
573 );
574
575 -- indexes for better performance
576 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
577 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
578 create index if not exists idx_references_from_at on reference_links(from_at);
579 create index if not exists idx_references_to_at on reference_links(to_at);
580 `)
581 if err != nil {
582 return nil, err
583 }
584
585 // run migrations
586 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
587 tx.Exec(`
588 alter table repos add column description text check (length(description) <= 200);
589 `)
590 return nil
591 })
592
593 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
594 // add unconstrained column
595 _, err := tx.Exec(`
596 alter table public_keys
597 add column rkey text;
598 `)
599 if err != nil {
600 return err
601 }
602
603 // backfill
604 _, err = tx.Exec(`
605 update public_keys
606 set rkey = ''
607 where rkey is null;
608 `)
609 if err != nil {
610 return err
611 }
612
613 return nil
614 })
615
616 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
617 _, err := tx.Exec(`
618 alter table comments drop column comment_at;
619 alter table comments add column rkey text;
620 `)
621 return err
622 })
623
624 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
625 _, err := tx.Exec(`
626 alter table comments add column deleted text; -- timestamp
627 alter table comments add column edited text; -- timestamp
628 `)
629 return err
630 })
631
632 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
633 _, err := tx.Exec(`
634 alter table pulls add column source_branch text;
635 alter table pulls add column source_repo_at text;
636 alter table pull_submissions add column source_rev text;
637 `)
638 return err
639 })
640
641 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
642 _, err := tx.Exec(`
643 alter table repos add column source text;
644 `)
645 return err
646 })
647
648 // disable foreign-keys for the next migration
649 // NOTE: this cannot be done in a transaction, so it is run outside [0]
650 //
651 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
652 conn.ExecContext(ctx, "pragma foreign_keys = off;")
653 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
654 _, err := tx.Exec(`
655 create table pulls_new (
656 -- identifiers
657 id integer primary key autoincrement,
658 pull_id integer not null,
659
660 -- at identifiers
661 repo_at text not null,
662 owner_did text not null,
663 rkey text not null,
664
665 -- content
666 title text not null,
667 body text not null,
668 target_branch text not null,
669 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
670
671 -- source info
672 source_branch text,
673 source_repo_at text,
674
675 -- stacking
676 stack_id text,
677 change_id text,
678 parent_change_id text,
679
680 -- meta
681 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
682
683 -- constraints
684 unique(repo_at, pull_id),
685 foreign key (repo_at) references repos(at_uri) on delete cascade
686 );
687
688 insert into pulls_new (
689 id, pull_id,
690 repo_at, owner_did, rkey,
691 title, body, target_branch, state,
692 source_branch, source_repo_at,
693 created
694 )
695 select
696 id, pull_id,
697 repo_at, owner_did, rkey,
698 title, body, target_branch, state,
699 source_branch, source_repo_at,
700 created
701 FROM pulls;
702
703 drop table pulls;
704 alter table pulls_new rename to pulls;
705 `)
706 return err
707 })
708 conn.ExecContext(ctx, "pragma foreign_keys = on;")
709
710 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
711 tx.Exec(`
712 alter table repos add column spindle text;
713 `)
714 return nil
715 })
716
717 // drop all knot secrets, add unique constraint to knots
718 //
719 // knots will henceforth use service auth for signed requests
720 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
721 _, err := tx.Exec(`
722 create table registrations_new (
723 id integer primary key autoincrement,
724 domain text not null,
725 did text not null,
726 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
727 registered text,
728 read_only integer not null default 0,
729 unique(domain, did)
730 );
731
732 insert into registrations_new (id, domain, did, created, registered, read_only)
733 select id, domain, did, created, registered, 1 from registrations
734 where registered is not null;
735
736 drop table registrations;
737 alter table registrations_new rename to registrations;
738 `)
739 return err
740 })
741
742 // recreate and add rkey + created columns with default constraint
743 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
744 // create new table
745 // - repo_at instead of repo integer
746 // - rkey field
747 // - created field
748 _, err := tx.Exec(`
749 create table collaborators_new (
750 -- identifiers for the record
751 id integer primary key autoincrement,
752 did text not null,
753 rkey text,
754
755 -- content
756 subject_did text not null,
757 repo_at text not null,
758
759 -- meta
760 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
761
762 -- constraints
763 foreign key (repo_at) references repos(at_uri) on delete cascade
764 )
765 `)
766 if err != nil {
767 return err
768 }
769
770 // copy data
771 _, err = tx.Exec(`
772 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
773 select
774 c.id,
775 r.did,
776 '',
777 c.did,
778 r.at_uri
779 from collaborators c
780 join repos r on c.repo = r.id
781 `)
782 if err != nil {
783 return err
784 }
785
786 // drop old table
787 _, err = tx.Exec(`drop table collaborators`)
788 if err != nil {
789 return err
790 }
791
792 // rename new table
793 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
794 return err
795 })
796
797 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
798 _, err := tx.Exec(`
799 alter table issues add column rkey text not null default '';
800
801 -- get last url section from issue_at and save to rkey column
802 update issues
803 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
804 `)
805 return err
806 })
807
808 // repurpose the read-only column to "needs-upgrade"
809 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
810 _, err := tx.Exec(`
811 alter table registrations rename column read_only to needs_upgrade;
812 `)
813 return err
814 })
815
816 // require all knots to upgrade after the release of total xrpc
817 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
818 _, err := tx.Exec(`
819 update registrations set needs_upgrade = 1;
820 `)
821 return err
822 })
823
824 // require all knots to upgrade after the release of total xrpc
825 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
826 _, err := tx.Exec(`
827 alter table spindles add column needs_upgrade integer not null default 0;
828 `)
829 return err
830 })
831
832 // remove issue_at from issues and replace with generated column
833 //
834 // this requires a full table recreation because stored columns
835 // cannot be added via alter
836 //
837 // couple other changes:
838 // - columns renamed to be more consistent
839 // - adds edited and deleted fields
840 //
841 // disable foreign-keys for the next migration
842 conn.ExecContext(ctx, "pragma foreign_keys = off;")
843 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
844 _, err := tx.Exec(`
845 create table if not exists issues_new (
846 -- identifiers
847 id integer primary key autoincrement,
848 did text not null,
849 rkey text not null,
850 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
851
852 -- at identifiers
853 repo_at text not null,
854
855 -- content
856 issue_id integer not null,
857 title text not null,
858 body text not null,
859 open integer not null default 1,
860 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
861 edited text, -- timestamp
862 deleted text, -- timestamp
863
864 unique(did, rkey),
865 unique(repo_at, issue_id),
866 unique(at_uri),
867 foreign key (repo_at) references repos(at_uri) on delete cascade
868 );
869 `)
870 if err != nil {
871 return err
872 }
873
874 // transfer data
875 _, err = tx.Exec(`
876 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
877 select
878 i.id,
879 i.owner_did,
880 i.rkey,
881 i.repo_at,
882 i.issue_id,
883 i.title,
884 i.body,
885 i.open,
886 i.created
887 from issues i;
888 `)
889 if err != nil {
890 return err
891 }
892
893 // drop old table
894 _, err = tx.Exec(`drop table issues`)
895 if err != nil {
896 return err
897 }
898
899 // rename new table
900 _, err = tx.Exec(`alter table issues_new rename to issues`)
901 return err
902 })
903 conn.ExecContext(ctx, "pragma foreign_keys = on;")
904
905 // - renames the comments table to 'issue_comments'
906 // - rework issue comments to update constraints:
907 // * unique(did, rkey)
908 // * remove comment-id and just use the global ID
909 // * foreign key (repo_at, issue_id)
910 // - new columns
911 // * column "reply_to" which can be any other comment
912 // * column "at-uri" which is a generated column
913 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
914 _, err := tx.Exec(`
915 create table if not exists issue_comments (
916 -- identifiers
917 id integer primary key autoincrement,
918 did text not null,
919 rkey text,
920 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
921
922 -- at identifiers
923 issue_at text not null,
924 reply_to text, -- at_uri of parent comment
925
926 -- content
927 body text not null,
928 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
929 edited text,
930 deleted text,
931
932 -- constraints
933 unique(did, rkey),
934 unique(at_uri),
935 foreign key (issue_at) references issues(at_uri) on delete cascade
936 );
937 `)
938 if err != nil {
939 return err
940 }
941
942 // transfer data
943 _, err = tx.Exec(`
944 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
945 select
946 c.id,
947 c.owner_did,
948 c.rkey,
949 i.at_uri, -- get at_uri from issues table
950 c.body,
951 c.created,
952 c.edited,
953 c.deleted
954 from comments c
955 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
956 `)
957 if err != nil {
958 return err
959 }
960
961 // drop old table
962 _, err = tx.Exec(`drop table comments`)
963 return err
964 })
965
966 // add generated at_uri column to pulls table
967 //
968 // this requires a full table recreation because stored columns
969 // cannot be added via alter
970 //
971 // disable foreign-keys for the next migration
972 conn.ExecContext(ctx, "pragma foreign_keys = off;")
973 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
974 _, err := tx.Exec(`
975 create table if not exists pulls_new (
976 -- identifiers
977 id integer primary key autoincrement,
978 pull_id integer not null,
979 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
980
981 -- at identifiers
982 repo_at text not null,
983 owner_did text not null,
984 rkey text not null,
985
986 -- content
987 title text not null,
988 body text not null,
989 target_branch text not null,
990 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
991
992 -- source info
993 source_branch text,
994 source_repo_at text,
995
996 -- stacking
997 stack_id text,
998 change_id text,
999 parent_change_id text,
1000
1001 -- meta
1002 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1003
1004 -- constraints
1005 unique(repo_at, pull_id),
1006 unique(at_uri),
1007 foreign key (repo_at) references repos(at_uri) on delete cascade
1008 );
1009 `)
1010 if err != nil {
1011 return err
1012 }
1013
1014 // transfer data
1015 _, err = tx.Exec(`
1016 insert into pulls_new (
1017 id, pull_id, repo_at, owner_did, rkey,
1018 title, body, target_branch, state,
1019 source_branch, source_repo_at,
1020 stack_id, change_id, parent_change_id,
1021 created
1022 )
1023 select
1024 id, pull_id, repo_at, owner_did, rkey,
1025 title, body, target_branch, state,
1026 source_branch, source_repo_at,
1027 stack_id, change_id, parent_change_id,
1028 created
1029 from pulls;
1030 `)
1031 if err != nil {
1032 return err
1033 }
1034
1035 // drop old table
1036 _, err = tx.Exec(`drop table pulls`)
1037 if err != nil {
1038 return err
1039 }
1040
1041 // rename new table
1042 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1043 return err
1044 })
1045 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1046
1047 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1048 //
1049 // this requires a full table recreation because stored columns
1050 // cannot be added via alter
1051 //
1052 // disable foreign-keys for the next migration
1053 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1054 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1055 _, err := tx.Exec(`
1056 create table if not exists pull_submissions_new (
1057 -- identifiers
1058 id integer primary key autoincrement,
1059 pull_at text not null,
1060
1061 -- content, these are immutable, and require a resubmission to update
1062 round_number integer not null default 0,
1063 patch text,
1064 source_rev text,
1065
1066 -- meta
1067 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1068
1069 -- constraints
1070 unique(pull_at, round_number),
1071 foreign key (pull_at) references pulls(at_uri) on delete cascade
1072 );
1073 `)
1074 if err != nil {
1075 return err
1076 }
1077
1078 // transfer data, constructing pull_at from pulls table
1079 _, err = tx.Exec(`
1080 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1081 select
1082 ps.id,
1083 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1084 ps.round_number,
1085 ps.patch,
1086 ps.created
1087 from pull_submissions ps
1088 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1089 `)
1090 if err != nil {
1091 return err
1092 }
1093
1094 // drop old table
1095 _, err = tx.Exec(`drop table pull_submissions`)
1096 if err != nil {
1097 return err
1098 }
1099
1100 // rename new table
1101 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1102 return err
1103 })
1104 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1105
1106 // knots may report the combined patch for a comparison, we can store that on the appview side
1107 // (but not on the pds record), because calculating the combined patch requires a git index
1108 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1109 _, err := tx.Exec(`
1110 alter table pull_submissions add column combined text;
1111 `)
1112 return err
1113 })
1114
1115 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1116 _, err := tx.Exec(`
1117 alter table profile add column pronouns text;
1118 `)
1119 return err
1120 })
1121
1122 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1123 _, err := tx.Exec(`
1124 alter table repos add column website text;
1125 alter table repos add column topics text;
1126 `)
1127 return err
1128 })
1129
1130 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1131 _, err := tx.Exec(`
1132 alter table notification_preferences add column user_mentioned integer not null default 1;
1133 `)
1134 return err
1135 })
1136
1137 // remove the foreign key constraints from stars.
1138 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1139 _, err := tx.Exec(`
1140 create table stars_new (
1141 id integer primary key autoincrement,
1142 did text not null,
1143 rkey text not null,
1144
1145 subject_at text not null,
1146
1147 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1148 unique(did, rkey),
1149 unique(did, subject_at)
1150 );
1151
1152 insert into stars_new (
1153 id,
1154 did,
1155 rkey,
1156 subject_at,
1157 created
1158 )
1159 select
1160 id,
1161 starred_by_did,
1162 rkey,
1163 repo_at,
1164 created
1165 from stars;
1166
1167 drop table stars;
1168 alter table stars_new rename to stars;
1169
1170 create index if not exists idx_stars_created on stars(created);
1171 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1172 `)
1173 return err
1174 })
1175
1176 return &DB{
1177 db,
1178 logger,
1179 }, nil
1180}
1181
1182func (d *DB) Close() error {
1183 return d.DB.Close()
1184}