dzming.li
A vibe coded tangled fork which supports pijul.
1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log/slog"
8 "strings"
9
10 _ "github.com/mattn/go-sqlite3"
11 "tangled.org/core/log"
12 "tangled.org/core/orm"
13)
14
15type DB struct {
16 *sql.DB
17 logger *slog.Logger
18}
19
20type Execer interface {
21 Query(query string, args ...any) (*sql.Rows, error)
22 QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
23 QueryRow(query string, args ...any) *sql.Row
24 QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
25 Exec(query string, args ...any) (sql.Result, error)
26 ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
27 Prepare(query string) (*sql.Stmt, error)
28 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
29}
30
31func Make(ctx context.Context, dbPath string) (*DB, error) {
32 // https://github.com/mattn/go-sqlite3#connection-string
33 opts := []string{
34 "_foreign_keys=1",
35 "_journal_mode=WAL",
36 "_synchronous=NORMAL",
37 "_auto_vacuum=incremental",
38 }
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 avatar text,
265 description text not null,
266 include_bluesky integer not null default 0,
267 location text,
268
269 -- constraints
270 unique(did)
271 );
272 create table if not exists profile_links (
273 -- id
274 id integer primary key autoincrement,
275 did text not null,
276
277 -- data
278 link text not null,
279
280 -- constraints
281 foreign key (did) references profile(did) on delete cascade
282 );
283 create table if not exists profile_stats (
284 -- id
285 id integer primary key autoincrement,
286 did text not null,
287
288 -- data
289 kind text not null check (kind in (
290 "merged-pull-request-count",
291 "closed-pull-request-count",
292 "open-pull-request-count",
293 "open-issue-count",
294 "closed-issue-count",
295 "repository-count"
296 )),
297
298 -- constraints
299 foreign key (did) references profile(did) on delete cascade
300 );
301 create table if not exists profile_pinned_repositories (
302 -- id
303 id integer primary key autoincrement,
304 did text not null,
305
306 -- data
307 at_uri text not null,
308
309 -- constraints
310 unique(did, at_uri),
311 foreign key (did) references profile(did) on delete cascade,
312 foreign key (at_uri) references repos(at_uri) on delete cascade
313 );
314
315 create table if not exists oauth_requests (
316 id integer primary key autoincrement,
317 auth_server_iss text not null,
318 state text not null,
319 did text not null,
320 handle text not null,
321 pds_url text not null,
322 pkce_verifier text not null,
323 dpop_auth_server_nonce text not null,
324 dpop_private_jwk text not null
325 );
326
327 create table if not exists oauth_sessions (
328 id integer primary key autoincrement,
329 did text not null,
330 handle text not null,
331 pds_url text not null,
332 auth_server_iss text not null,
333 access_jwt text not null,
334 refresh_jwt text not null,
335 dpop_pds_nonce text,
336 dpop_auth_server_nonce text not null,
337 dpop_private_jwk text not null,
338 expiry text not null
339 );
340
341 create table if not exists punchcard (
342 did text not null,
343 date text not null, -- yyyy-mm-dd
344 count integer,
345 primary key (did, date)
346 );
347
348 create table if not exists spindles (
349 id integer primary key autoincrement,
350 owner text not null,
351 instance text not null,
352 verified text, -- time of verification
353 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
354
355 unique(owner, instance)
356 );
357
358 create table if not exists spindle_members (
359 -- identifiers for the record
360 id integer primary key autoincrement,
361 did text not null,
362 rkey text not null,
363
364 -- data
365 instance text not null,
366 subject text not null,
367 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
368
369 -- constraints
370 unique (did, instance, subject)
371 );
372
373 create table if not exists pipelines (
374 -- identifiers
375 id integer primary key autoincrement,
376 knot text not null,
377 rkey text not null,
378
379 repo_owner text not null,
380 repo_name text not null,
381
382 -- every pipeline must be associated with exactly one commit
383 sha text not null check (length(sha) = 40),
384 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
385
386 -- trigger data
387 trigger_id integer not null,
388
389 unique(knot, rkey),
390 foreign key (trigger_id) references triggers(id) on delete cascade
391 );
392
393 create table if not exists triggers (
394 -- primary key
395 id integer primary key autoincrement,
396
397 -- top-level fields
398 kind text not null,
399
400 -- pushTriggerData fields
401 push_ref text,
402 push_new_sha text check (length(push_new_sha) = 40),
403 push_old_sha text check (length(push_old_sha) = 40),
404
405 -- pullRequestTriggerData fields
406 pr_source_branch text,
407 pr_target_branch text,
408 pr_source_sha text check (length(pr_source_sha) = 40),
409 pr_action text
410 );
411
412 create table if not exists pipeline_statuses (
413 -- identifiers
414 id integer primary key autoincrement,
415 spindle text not null,
416 rkey text not null,
417
418 -- referenced pipeline. these form the (did, rkey) pair
419 pipeline_knot text not null,
420 pipeline_rkey text not null,
421
422 -- content
423 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
424 workflow text not null,
425 status text not null,
426 error text,
427 exit_code integer not null default 0,
428
429 unique (spindle, rkey),
430 foreign key (pipeline_knot, pipeline_rkey)
431 references pipelines (knot, rkey)
432 on delete cascade
433 );
434
435 create table if not exists repo_languages (
436 -- identifiers
437 id integer primary key autoincrement,
438
439 -- repo identifiers
440 repo_at text not null,
441 ref text not null,
442 is_default_ref integer not null default 0,
443
444 -- language breakdown
445 language text not null,
446 bytes integer not null check (bytes >= 0),
447
448 unique(repo_at, ref, language)
449 );
450
451 create table if not exists signups_inflight (
452 id integer primary key autoincrement,
453 email text not null unique,
454 invite_code text not null,
455 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
456 );
457
458 create table if not exists strings (
459 -- identifiers
460 did text not null,
461 rkey text not null,
462
463 -- content
464 filename text not null,
465 description text,
466 content text not null,
467 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
468 edited text,
469
470 primary key (did, rkey)
471 );
472
473 create table if not exists label_definitions (
474 -- identifiers
475 id integer primary key autoincrement,
476 did text not null,
477 rkey text not null,
478 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.definition' || '/' || rkey) stored,
479
480 -- content
481 name text not null,
482 value_type text not null check (value_type in (
483 "null",
484 "boolean",
485 "integer",
486 "string"
487 )),
488 value_format text not null default "any",
489 value_enum text, -- comma separated list
490 scope text not null, -- comma separated list of nsid
491 color text,
492 multiple integer not null default 0,
493 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
494
495 -- constraints
496 unique (did, rkey)
497 unique (at_uri)
498 );
499
500 -- ops are flattened, a record may contain several additions and deletions, but the table will include one row per add/del
501 create table if not exists label_ops (
502 -- identifiers
503 id integer primary key autoincrement,
504 did text not null,
505 rkey text not null,
506 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.label.op' || '/' || rkey) stored,
507
508 -- content
509 subject text not null,
510 operation text not null check (operation in ("add", "del")),
511 operand_key text not null,
512 operand_value text not null,
513 -- we need two time values: performed is declared by the user, indexed is calculated by the av
514 performed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
515 indexed text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
516
517 -- constraints
518 -- traditionally (did, rkey) pair should be unique, but not in this case
519 -- operand_key should reference a label definition
520 foreign key (operand_key) references label_definitions (at_uri) on delete cascade,
521 unique (did, rkey, subject, operand_key, operand_value)
522 );
523
524 create table if not exists repo_labels (
525 -- identifiers
526 id integer primary key autoincrement,
527
528 -- repo identifiers
529 repo_at text not null,
530
531 -- label to subscribe to
532 label_at text not null,
533
534 unique (repo_at, label_at)
535 );
536
537 create table if not exists notifications (
538 id integer primary key autoincrement,
539 recipient_did text not null,
540 actor_did text not null,
541 type text not null,
542 entity_type text not null,
543 entity_id text not null,
544 read integer not null default 0,
545 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
546 repo_id integer references repos(id),
547 issue_id integer references issues(id),
548 pull_id integer references pulls(id)
549 );
550
551 create table if not exists notification_preferences (
552 id integer primary key autoincrement,
553 user_did text not null unique,
554 repo_starred integer not null default 1,
555 issue_created integer not null default 1,
556 issue_commented integer not null default 1,
557 pull_created integer not null default 1,
558 pull_commented integer not null default 1,
559 followed integer not null default 1,
560 pull_merged integer not null default 1,
561 issue_closed integer not null default 1,
562 email_notifications integer not null default 0
563 );
564
565 create table if not exists reference_links (
566 id integer primary key autoincrement,
567 from_at text not null,
568 to_at text not null,
569 unique (from_at, to_at)
570 );
571
572 create table if not exists migrations (
573 id integer primary key autoincrement,
574 name text unique
575 );
576
577 -- indexes for better performance
578 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
579 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
580 create index if not exists idx_references_from_at on reference_links(from_at);
581 create index if not exists idx_references_to_at on reference_links(to_at);
582 `)
583 if err != nil {
584 return nil, err
585 }
586
587 // run migrations
588 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
589 tx.Exec(`
590 alter table repos add column description text check (length(description) <= 200);
591 `)
592 return nil
593 })
594
595 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
596 // add unconstrained column
597 _, err := tx.Exec(`
598 alter table public_keys
599 add column rkey text;
600 `)
601 if err != nil {
602 return err
603 }
604
605 // backfill
606 _, err = tx.Exec(`
607 update public_keys
608 set rkey = ''
609 where rkey is null;
610 `)
611 if err != nil {
612 return err
613 }
614
615 return nil
616 })
617
618 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
619 _, err := tx.Exec(`
620 alter table comments drop column comment_at;
621 alter table comments add column rkey text;
622 `)
623 return err
624 })
625
626 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
627 _, err := tx.Exec(`
628 alter table comments add column deleted text; -- timestamp
629 alter table comments add column edited text; -- timestamp
630 `)
631 return err
632 })
633
634 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
635 _, err := tx.Exec(`
636 alter table pulls add column source_branch text;
637 alter table pulls add column source_repo_at text;
638 alter table pull_submissions add column source_rev text;
639 `)
640 return err
641 })
642
643 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
644 _, err := tx.Exec(`
645 alter table repos add column source text;
646 `)
647 return err
648 })
649
650 // disable foreign-keys for the next migration
651 // NOTE: this cannot be done in a transaction, so it is run outside [0]
652 //
653 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
654 conn.ExecContext(ctx, "pragma foreign_keys = off;")
655 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
656 _, err := tx.Exec(`
657 create table pulls_new (
658 -- identifiers
659 id integer primary key autoincrement,
660 pull_id integer not null,
661
662 -- at identifiers
663 repo_at text not null,
664 owner_did text not null,
665 rkey text not null,
666
667 -- content
668 title text not null,
669 body text not null,
670 target_branch text not null,
671 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
672
673 -- source info
674 source_branch text,
675 source_repo_at text,
676
677 -- stacking
678 stack_id text,
679 change_id text,
680 parent_change_id text,
681
682 -- meta
683 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
684
685 -- constraints
686 unique(repo_at, pull_id),
687 foreign key (repo_at) references repos(at_uri) on delete cascade
688 );
689
690 insert into pulls_new (
691 id, pull_id,
692 repo_at, owner_did, rkey,
693 title, body, target_branch, state,
694 source_branch, source_repo_at,
695 created
696 )
697 select
698 id, pull_id,
699 repo_at, owner_did, rkey,
700 title, body, target_branch, state,
701 source_branch, source_repo_at,
702 created
703 FROM pulls;
704
705 drop table pulls;
706 alter table pulls_new rename to pulls;
707 `)
708 return err
709 })
710 conn.ExecContext(ctx, "pragma foreign_keys = on;")
711
712 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
713 tx.Exec(`
714 alter table repos add column spindle text;
715 `)
716 return nil
717 })
718
719 // drop all knot secrets, add unique constraint to knots
720 //
721 // knots will henceforth use service auth for signed requests
722 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
723 _, err := tx.Exec(`
724 create table registrations_new (
725 id integer primary key autoincrement,
726 domain text not null,
727 did text not null,
728 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
729 registered text,
730 read_only integer not null default 0,
731 unique(domain, did)
732 );
733
734 insert into registrations_new (id, domain, did, created, registered, read_only)
735 select id, domain, did, created, registered, 1 from registrations
736 where registered is not null;
737
738 drop table registrations;
739 alter table registrations_new rename to registrations;
740 `)
741 return err
742 })
743
744 // recreate and add rkey + created columns with default constraint
745 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
746 // create new table
747 // - repo_at instead of repo integer
748 // - rkey field
749 // - created field
750 _, err := tx.Exec(`
751 create table collaborators_new (
752 -- identifiers for the record
753 id integer primary key autoincrement,
754 did text not null,
755 rkey text,
756
757 -- content
758 subject_did text not null,
759 repo_at text not null,
760
761 -- meta
762 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
763
764 -- constraints
765 foreign key (repo_at) references repos(at_uri) on delete cascade
766 )
767 `)
768 if err != nil {
769 return err
770 }
771
772 // copy data
773 _, err = tx.Exec(`
774 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
775 select
776 c.id,
777 r.did,
778 '',
779 c.did,
780 r.at_uri
781 from collaborators c
782 join repos r on c.repo = r.id
783 `)
784 if err != nil {
785 return err
786 }
787
788 // drop old table
789 _, err = tx.Exec(`drop table collaborators`)
790 if err != nil {
791 return err
792 }
793
794 // rename new table
795 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
796 return err
797 })
798
799 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
800 _, err := tx.Exec(`
801 alter table issues add column rkey text not null default '';
802
803 -- get last url section from issue_at and save to rkey column
804 update issues
805 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
806 `)
807 return err
808 })
809
810 // repurpose the read-only column to "needs-upgrade"
811 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
812 _, err := tx.Exec(`
813 alter table registrations rename column read_only to needs_upgrade;
814 `)
815 return err
816 })
817
818 // require all knots to upgrade after the release of total xrpc
819 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
820 _, err := tx.Exec(`
821 update registrations set needs_upgrade = 1;
822 `)
823 return err
824 })
825
826 // require all knots to upgrade after the release of total xrpc
827 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
828 _, err := tx.Exec(`
829 alter table spindles add column needs_upgrade integer not null default 0;
830 `)
831 return err
832 })
833
834 // remove issue_at from issues and replace with generated column
835 //
836 // this requires a full table recreation because stored columns
837 // cannot be added via alter
838 //
839 // couple other changes:
840 // - columns renamed to be more consistent
841 // - adds edited and deleted fields
842 //
843 // disable foreign-keys for the next migration
844 conn.ExecContext(ctx, "pragma foreign_keys = off;")
845 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
846 _, err := tx.Exec(`
847 create table if not exists issues_new (
848 -- identifiers
849 id integer primary key autoincrement,
850 did text not null,
851 rkey text not null,
852 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
853
854 -- at identifiers
855 repo_at text not null,
856
857 -- content
858 issue_id integer not null,
859 title text not null,
860 body text not null,
861 open integer not null default 1,
862 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
863 edited text, -- timestamp
864 deleted text, -- timestamp
865
866 unique(did, rkey),
867 unique(repo_at, issue_id),
868 unique(at_uri),
869 foreign key (repo_at) references repos(at_uri) on delete cascade
870 );
871 `)
872 if err != nil {
873 return err
874 }
875
876 // transfer data
877 _, err = tx.Exec(`
878 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
879 select
880 i.id,
881 i.owner_did,
882 i.rkey,
883 i.repo_at,
884 i.issue_id,
885 i.title,
886 i.body,
887 i.open,
888 i.created
889 from issues i;
890 `)
891 if err != nil {
892 return err
893 }
894
895 // drop old table
896 _, err = tx.Exec(`drop table issues`)
897 if err != nil {
898 return err
899 }
900
901 // rename new table
902 _, err = tx.Exec(`alter table issues_new rename to issues`)
903 return err
904 })
905 conn.ExecContext(ctx, "pragma foreign_keys = on;")
906
907 // - renames the comments table to 'issue_comments'
908 // - rework issue comments to update constraints:
909 // * unique(did, rkey)
910 // * remove comment-id and just use the global ID
911 // * foreign key (repo_at, issue_id)
912 // - new columns
913 // * column "reply_to" which can be any other comment
914 // * column "at-uri" which is a generated column
915 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
916 _, err := tx.Exec(`
917 create table if not exists issue_comments (
918 -- identifiers
919 id integer primary key autoincrement,
920 did text not null,
921 rkey text,
922 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
923
924 -- at identifiers
925 issue_at text not null,
926 reply_to text, -- at_uri of parent comment
927
928 -- content
929 body text not null,
930 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
931 edited text,
932 deleted text,
933
934 -- constraints
935 unique(did, rkey),
936 unique(at_uri),
937 foreign key (issue_at) references issues(at_uri) on delete cascade
938 );
939 `)
940 if err != nil {
941 return err
942 }
943
944 // transfer data
945 _, err = tx.Exec(`
946 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
947 select
948 c.id,
949 c.owner_did,
950 c.rkey,
951 i.at_uri, -- get at_uri from issues table
952 c.body,
953 c.created,
954 c.edited,
955 c.deleted
956 from comments c
957 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
958 `)
959 if err != nil {
960 return err
961 }
962
963 // drop old table
964 _, err = tx.Exec(`drop table comments`)
965 return err
966 })
967
968 // add generated at_uri column to pulls table
969 //
970 // this requires a full table recreation because stored columns
971 // cannot be added via alter
972 //
973 // disable foreign-keys for the next migration
974 conn.ExecContext(ctx, "pragma foreign_keys = off;")
975 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
976 _, err := tx.Exec(`
977 create table if not exists pulls_new (
978 -- identifiers
979 id integer primary key autoincrement,
980 pull_id integer not null,
981 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
982
983 -- at identifiers
984 repo_at text not null,
985 owner_did text not null,
986 rkey text not null,
987
988 -- content
989 title text not null,
990 body text not null,
991 target_branch text not null,
992 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
993
994 -- source info
995 source_branch text,
996 source_repo_at text,
997
998 -- stacking
999 stack_id text,
1000 change_id text,
1001 parent_change_id text,
1002
1003 -- meta
1004 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1005
1006 -- constraints
1007 unique(repo_at, pull_id),
1008 unique(at_uri),
1009 foreign key (repo_at) references repos(at_uri) on delete cascade
1010 );
1011 `)
1012 if err != nil {
1013 return err
1014 }
1015
1016 // transfer data
1017 _, err = tx.Exec(`
1018 insert into pulls_new (
1019 id, pull_id, repo_at, owner_did, rkey,
1020 title, body, target_branch, state,
1021 source_branch, source_repo_at,
1022 stack_id, change_id, parent_change_id,
1023 created
1024 )
1025 select
1026 id, pull_id, repo_at, owner_did, rkey,
1027 title, body, target_branch, state,
1028 source_branch, source_repo_at,
1029 stack_id, change_id, parent_change_id,
1030 created
1031 from pulls;
1032 `)
1033 if err != nil {
1034 return err
1035 }
1036
1037 // drop old table
1038 _, err = tx.Exec(`drop table pulls`)
1039 if err != nil {
1040 return err
1041 }
1042
1043 // rename new table
1044 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1045 return err
1046 })
1047 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1048
1049 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1050 //
1051 // this requires a full table recreation because stored columns
1052 // cannot be added via alter
1053 //
1054 // disable foreign-keys for the next migration
1055 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1056 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1057 _, err := tx.Exec(`
1058 create table if not exists pull_submissions_new (
1059 -- identifiers
1060 id integer primary key autoincrement,
1061 pull_at text not null,
1062
1063 -- content, these are immutable, and require a resubmission to update
1064 round_number integer not null default 0,
1065 patch text,
1066 source_rev text,
1067
1068 -- meta
1069 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1070
1071 -- constraints
1072 unique(pull_at, round_number),
1073 foreign key (pull_at) references pulls(at_uri) on delete cascade
1074 );
1075 `)
1076 if err != nil {
1077 return err
1078 }
1079
1080 // transfer data, constructing pull_at from pulls table
1081 _, err = tx.Exec(`
1082 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1083 select
1084 ps.id,
1085 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1086 ps.round_number,
1087 ps.patch,
1088 ps.created
1089 from pull_submissions ps
1090 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1091 `)
1092 if err != nil {
1093 return err
1094 }
1095
1096 // drop old table
1097 _, err = tx.Exec(`drop table pull_submissions`)
1098 if err != nil {
1099 return err
1100 }
1101
1102 // rename new table
1103 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1104 return err
1105 })
1106 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1107
1108 // knots may report the combined patch for a comparison, we can store that on the appview side
1109 // (but not on the pds record), because calculating the combined patch requires a git index
1110 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1111 _, err := tx.Exec(`
1112 alter table pull_submissions add column combined text;
1113 `)
1114 return err
1115 })
1116
1117 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1118 _, err := tx.Exec(`
1119 alter table profile add column pronouns text;
1120 `)
1121 return err
1122 })
1123
1124 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1125 _, err := tx.Exec(`
1126 alter table repos add column website text;
1127 alter table repos add column topics text;
1128 `)
1129 return err
1130 })
1131
1132 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1133 _, err := tx.Exec(`
1134 alter table notification_preferences add column user_mentioned integer not null default 1;
1135 `)
1136 return err
1137 })
1138
1139 // remove the foreign key constraints from stars.
1140 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1141 _, err := tx.Exec(`
1142 create table stars_new (
1143 id integer primary key autoincrement,
1144 did text not null,
1145 rkey text not null,
1146
1147 subject_at text not null,
1148
1149 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1150 unique(did, rkey),
1151 unique(did, subject_at)
1152 );
1153
1154 insert into stars_new (
1155 id,
1156 did,
1157 rkey,
1158 subject_at,
1159 created
1160 )
1161 select
1162 id,
1163 starred_by_did,
1164 rkey,
1165 repo_at,
1166 created
1167 from stars;
1168
1169 drop table stars;
1170 alter table stars_new rename to stars;
1171
1172 create index if not exists idx_stars_created on stars(created);
1173 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1174 `)
1175 return err
1176 })
1177
1178 orm.RunMigration(conn, logger, "add-avatar-to-profile", func(tx *sql.Tx) error {
1179 _, err := tx.Exec(`
1180 alter table profile add column avatar text;
1181 `)
1182 return err
1183 })
1184
1185 // we cannot modify user-owned record on repository delete
1186 orm.RunMigration(conn, logger, "remove-foreign-key-profile_pinned_repositories-and-repos", func(tx *sql.Tx) error {
1187 _, err := tx.Exec(`
1188 create table profile_pinned_repositories_new (
1189 did text not null,
1190
1191 -- data
1192 at_uri text not null,
1193
1194 -- constraints
1195 unique(did, at_uri),
1196 foreign key (did) references profile(did) on delete cascade
1197 );
1198
1199 insert into profile_pinned_repositories_new (did, at_uri)
1200 select did, at_uri from profile_pinned_repositories;
1201
1202 drop table profile_pinned_repositories;
1203 alter table profile_pinned_repositories_new rename to profile_pinned_repositories;
1204 `)
1205 return err
1206 })
1207
1208 // several changes here
1209 // 1. remove autoincrement id for these tables
1210 // 2. remove unique constraints other than (did, rkey) to handle non-unique atproto records
1211 // 3. add generated at_uri field
1212 //
1213 // see comments below and commit message for details
1214 orm.RunMigration(conn, logger, "flexible-stars-reactions-follows-public_keys", func(tx *sql.Tx) error {
1215 // - add at_uri
1216 // - remove unique constraint (did, subject_at)
1217 if _, err := tx.Exec(`
1218 create table stars_new (
1219 did text not null,
1220 rkey text not null,
1221 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.feed.star' || '/' || rkey) stored,
1222
1223 subject_at text not null,
1224 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1225
1226 unique(did, rkey)
1227 );
1228
1229 insert into stars_new (did, rkey, subject_at, created)
1230 select did, rkey, subject_at, created from stars;
1231
1232 drop table stars;
1233 alter table stars_new rename to stars;
1234 `); err != nil {
1235 return fmt.Errorf("migrating stars: %w", err)
1236 }
1237
1238 // - add at_uri
1239 // - reacted_by_did -> did
1240 // - thread_at -> subject_at
1241 // - remove unique constraint
1242 if _, err := tx.Exec(`
1243 create table reactions_new (
1244 did text not null,
1245 rkey text not null,
1246 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.feed.reaction' || '/' || rkey) stored,
1247
1248 subject_at text not null,
1249 kind text not null,
1250 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1251
1252 unique(did, rkey)
1253 );
1254
1255 insert into reactions_new (did, rkey, subject_at, kind, created)
1256 select reacted_by_did, rkey, thread_at, kind, created from reactions;
1257
1258 drop table reactions;
1259 alter table reactions_new rename to reactions;
1260 `); err != nil {
1261 return fmt.Errorf("migrating reactions: %w", err)
1262 }
1263
1264 // - add at_uri column
1265 // - user_did -> did
1266 // - followed_at -> created
1267 // - remove unique constraint
1268 // - remove check constraint
1269 if _, err := tx.Exec(`
1270 create table follows_new (
1271 did text not null,
1272 rkey text not null,
1273 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.graph.follow' || '/' || rkey) stored,
1274
1275 subject_did text not null,
1276 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1277
1278 unique(did, rkey)
1279 );
1280
1281 insert into follows_new (did, rkey, subject_did, created)
1282 select user_did, rkey, subject_did, followed_at from follows;
1283
1284 drop table follows;
1285 alter table follows_new rename to follows;
1286 `); err != nil {
1287 return fmt.Errorf("migrating follows: %w", err)
1288 }
1289
1290 // - add at_uri column
1291 // - remove foreign key relationship from repos
1292 if _, err := tx.Exec(`
1293 create table public_keys_new (
1294 did text not null,
1295 rkey text not null,
1296 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.publicKey' || '/' || rkey) stored,
1297
1298 name text not null,
1299 key text not null,
1300 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1301
1302 unique(did, rkey)
1303 );
1304
1305 insert into public_keys_new (did, rkey, name, key, created)
1306 select did, rkey, name, key, created from public_keys;
1307
1308 drop table public_keys;
1309 alter table public_keys_new rename to public_keys;
1310 `); err != nil {
1311 return fmt.Errorf("migrating public_keys: %w", err)
1312 }
1313
1314 return nil
1315 })
1316
1317 return &DB{
1318 db,
1319 logger,
1320 }, nil
1321}
1322
1323func (d *DB) Close() error {
1324 return d.DB.Close()
1325}