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 webhooks (
573 id integer primary key autoincrement,
574 repo_at text not null,
575 url text not null,
576 secret text,
577 active integer not null default 1,
578 events text not null, -- comma-separated list of events
579 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
580 updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
581
582 foreign key (repo_at) references repos(at_uri) on delete cascade
583 );
584
585 create table if not exists webhook_deliveries (
586 id integer primary key autoincrement,
587 webhook_id integer not null,
588 event text not null,
589 delivery_id text not null,
590 url text not null,
591 request_body text not null,
592 response_code integer,
593 response_body text,
594 success integer not null default 0,
595 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
596
597 foreign key (webhook_id) references webhooks(id) on delete cascade
598 );
599
600 create table if not exists migrations (
601 id integer primary key autoincrement,
602 name text unique
603 );
604
605 create table if not exists punchcard_preferences (
606 id integer primary key autoincrement,
607 user_did text not null unique,
608 hide_mine integer default 0,
609 hide_others integer default 0
610 );
611
612 -- indexes for better performance
613 create index if not exists idx_notifications_recipient_created on notifications(recipient_did, created desc);
614 create index if not exists idx_notifications_recipient_read on notifications(recipient_did, read);
615 create index if not exists idx_references_from_at on reference_links(from_at);
616 create index if not exists idx_references_to_at on reference_links(to_at);
617 create index if not exists idx_webhooks_repo_at on webhooks(repo_at);
618 create index if not exists idx_webhook_deliveries_webhook_id on webhook_deliveries(webhook_id);
619 `)
620 if err != nil {
621 return nil, err
622 }
623
624 // run migrations
625 orm.RunMigration(conn, logger, "add-description-to-repos", func(tx *sql.Tx) error {
626 tx.Exec(`
627 alter table repos add column description text check (length(description) <= 200);
628 `)
629 return nil
630 })
631
632 orm.RunMigration(conn, logger, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
633 // add unconstrained column
634 _, err := tx.Exec(`
635 alter table public_keys
636 add column rkey text;
637 `)
638 if err != nil {
639 return err
640 }
641
642 // backfill
643 _, err = tx.Exec(`
644 update public_keys
645 set rkey = ''
646 where rkey is null;
647 `)
648 if err != nil {
649 return err
650 }
651
652 return nil
653 })
654
655 orm.RunMigration(conn, logger, "add-rkey-to-comments", func(tx *sql.Tx) error {
656 _, err := tx.Exec(`
657 alter table comments drop column comment_at;
658 alter table comments add column rkey text;
659 `)
660 return err
661 })
662
663 orm.RunMigration(conn, logger, "add-deleted-and-edited-to-issue-comments", func(tx *sql.Tx) error {
664 _, err := tx.Exec(`
665 alter table comments add column deleted text; -- timestamp
666 alter table comments add column edited text; -- timestamp
667 `)
668 return err
669 })
670
671 orm.RunMigration(conn, logger, "add-source-info-to-pulls-and-submissions", func(tx *sql.Tx) error {
672 _, err := tx.Exec(`
673 alter table pulls add column source_branch text;
674 alter table pulls add column source_repo_at text;
675 alter table pull_submissions add column source_rev text;
676 `)
677 return err
678 })
679
680 orm.RunMigration(conn, logger, "add-source-to-repos", func(tx *sql.Tx) error {
681 _, err := tx.Exec(`
682 alter table repos add column source text;
683 `)
684 return err
685 })
686
687 // disable foreign-keys for the next migration
688 // NOTE: this cannot be done in a transaction, so it is run outside [0]
689 //
690 // [0]: https://sqlite.org/pragma.html#pragma_foreign_keys
691 conn.ExecContext(ctx, "pragma foreign_keys = off;")
692 orm.RunMigration(conn, logger, "recreate-pulls-column-for-stacking-support", func(tx *sql.Tx) error {
693 _, err := tx.Exec(`
694 create table pulls_new (
695 -- identifiers
696 id integer primary key autoincrement,
697 pull_id integer not null,
698
699 -- at identifiers
700 repo_at text not null,
701 owner_did text not null,
702 rkey text not null,
703
704 -- content
705 title text not null,
706 body text not null,
707 target_branch text not null,
708 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
709
710 -- source info
711 source_branch text,
712 source_repo_at text,
713
714 -- stacking
715 stack_id text,
716 change_id text,
717 parent_change_id text,
718
719 -- meta
720 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
721
722 -- constraints
723 unique(repo_at, pull_id),
724 foreign key (repo_at) references repos(at_uri) on delete cascade
725 );
726
727 insert into pulls_new (
728 id, pull_id,
729 repo_at, owner_did, rkey,
730 title, body, target_branch, state,
731 source_branch, source_repo_at,
732 created
733 )
734 select
735 id, pull_id,
736 repo_at, owner_did, rkey,
737 title, body, target_branch, state,
738 source_branch, source_repo_at,
739 created
740 FROM pulls;
741
742 drop table pulls;
743 alter table pulls_new rename to pulls;
744 `)
745 return err
746 })
747 conn.ExecContext(ctx, "pragma foreign_keys = on;")
748
749 orm.RunMigration(conn, logger, "add-spindle-to-repos", func(tx *sql.Tx) error {
750 tx.Exec(`
751 alter table repos add column spindle text;
752 `)
753 return nil
754 })
755
756 // drop all knot secrets, add unique constraint to knots
757 //
758 // knots will henceforth use service auth for signed requests
759 orm.RunMigration(conn, logger, "no-more-secrets", func(tx *sql.Tx) error {
760 _, err := tx.Exec(`
761 create table registrations_new (
762 id integer primary key autoincrement,
763 domain text not null,
764 did text not null,
765 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
766 registered text,
767 read_only integer not null default 0,
768 unique(domain, did)
769 );
770
771 insert into registrations_new (id, domain, did, created, registered, read_only)
772 select id, domain, did, created, registered, 1 from registrations
773 where registered is not null;
774
775 drop table registrations;
776 alter table registrations_new rename to registrations;
777 `)
778 return err
779 })
780
781 // recreate and add rkey + created columns with default constraint
782 orm.RunMigration(conn, logger, "rework-collaborators-table", func(tx *sql.Tx) error {
783 // create new table
784 // - repo_at instead of repo integer
785 // - rkey field
786 // - created field
787 _, err := tx.Exec(`
788 create table collaborators_new (
789 -- identifiers for the record
790 id integer primary key autoincrement,
791 did text not null,
792 rkey text,
793
794 -- content
795 subject_did text not null,
796 repo_at text not null,
797
798 -- meta
799 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
800
801 -- constraints
802 foreign key (repo_at) references repos(at_uri) on delete cascade
803 )
804 `)
805 if err != nil {
806 return err
807 }
808
809 // copy data
810 _, err = tx.Exec(`
811 insert into collaborators_new (id, did, rkey, subject_did, repo_at)
812 select
813 c.id,
814 r.did,
815 '',
816 c.did,
817 r.at_uri
818 from collaborators c
819 join repos r on c.repo = r.id
820 `)
821 if err != nil {
822 return err
823 }
824
825 // drop old table
826 _, err = tx.Exec(`drop table collaborators`)
827 if err != nil {
828 return err
829 }
830
831 // rename new table
832 _, err = tx.Exec(`alter table collaborators_new rename to collaborators`)
833 return err
834 })
835
836 orm.RunMigration(conn, logger, "add-rkey-to-issues", func(tx *sql.Tx) error {
837 _, err := tx.Exec(`
838 alter table issues add column rkey text not null default '';
839
840 -- get last url section from issue_at and save to rkey column
841 update issues
842 set rkey = replace(issue_at, rtrim(issue_at, replace(issue_at, '/', '')), '');
843 `)
844 return err
845 })
846
847 // repurpose the read-only column to "needs-upgrade"
848 orm.RunMigration(conn, logger, "rename-registrations-read-only-to-needs-upgrade", func(tx *sql.Tx) error {
849 _, err := tx.Exec(`
850 alter table registrations rename column read_only to needs_upgrade;
851 `)
852 return err
853 })
854
855 // require all knots to upgrade after the release of total xrpc
856 orm.RunMigration(conn, logger, "migrate-knots-to-total-xrpc", func(tx *sql.Tx) error {
857 _, err := tx.Exec(`
858 update registrations set needs_upgrade = 1;
859 `)
860 return err
861 })
862
863 // require all knots to upgrade after the release of total xrpc
864 orm.RunMigration(conn, logger, "migrate-spindles-to-xrpc-owner", func(tx *sql.Tx) error {
865 _, err := tx.Exec(`
866 alter table spindles add column needs_upgrade integer not null default 0;
867 `)
868 return err
869 })
870
871 // remove issue_at from issues and replace with generated column
872 //
873 // this requires a full table recreation because stored columns
874 // cannot be added via alter
875 //
876 // couple other changes:
877 // - columns renamed to be more consistent
878 // - adds edited and deleted fields
879 //
880 // disable foreign-keys for the next migration
881 conn.ExecContext(ctx, "pragma foreign_keys = off;")
882 orm.RunMigration(conn, logger, "remove-issue-at-from-issues", func(tx *sql.Tx) error {
883 _, err := tx.Exec(`
884 create table if not exists issues_new (
885 -- identifiers
886 id integer primary key autoincrement,
887 did text not null,
888 rkey text not null,
889 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue' || '/' || rkey) stored,
890
891 -- at identifiers
892 repo_at text not null,
893
894 -- content
895 issue_id integer not null,
896 title text not null,
897 body text not null,
898 open integer not null default 1,
899 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
900 edited text, -- timestamp
901 deleted text, -- timestamp
902
903 unique(did, rkey),
904 unique(repo_at, issue_id),
905 unique(at_uri),
906 foreign key (repo_at) references repos(at_uri) on delete cascade
907 );
908 `)
909 if err != nil {
910 return err
911 }
912
913 // transfer data
914 _, err = tx.Exec(`
915 insert into issues_new (id, did, rkey, repo_at, issue_id, title, body, open, created)
916 select
917 i.id,
918 i.owner_did,
919 i.rkey,
920 i.repo_at,
921 i.issue_id,
922 i.title,
923 i.body,
924 i.open,
925 i.created
926 from issues i;
927 `)
928 if err != nil {
929 return err
930 }
931
932 // drop old table
933 _, err = tx.Exec(`drop table issues`)
934 if err != nil {
935 return err
936 }
937
938 // rename new table
939 _, err = tx.Exec(`alter table issues_new rename to issues`)
940 return err
941 })
942 conn.ExecContext(ctx, "pragma foreign_keys = on;")
943
944 // - renames the comments table to 'issue_comments'
945 // - rework issue comments to update constraints:
946 // * unique(did, rkey)
947 // * remove comment-id and just use the global ID
948 // * foreign key (repo_at, issue_id)
949 // - new columns
950 // * column "reply_to" which can be any other comment
951 // * column "at-uri" which is a generated column
952 orm.RunMigration(conn, logger, "rework-issue-comments", func(tx *sql.Tx) error {
953 _, err := tx.Exec(`
954 create table if not exists issue_comments (
955 -- identifiers
956 id integer primary key autoincrement,
957 did text not null,
958 rkey text,
959 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.issue.comment' || '/' || rkey) stored,
960
961 -- at identifiers
962 issue_at text not null,
963 reply_to text, -- at_uri of parent comment
964
965 -- content
966 body text not null,
967 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
968 edited text,
969 deleted text,
970
971 -- constraints
972 unique(did, rkey),
973 unique(at_uri),
974 foreign key (issue_at) references issues(at_uri) on delete cascade
975 );
976 `)
977 if err != nil {
978 return err
979 }
980
981 // transfer data
982 _, err = tx.Exec(`
983 insert into issue_comments (id, did, rkey, issue_at, body, created, edited, deleted)
984 select
985 c.id,
986 c.owner_did,
987 c.rkey,
988 i.at_uri, -- get at_uri from issues table
989 c.body,
990 c.created,
991 c.edited,
992 c.deleted
993 from comments c
994 join issues i on c.repo_at = i.repo_at and c.issue_id = i.issue_id;
995 `)
996 if err != nil {
997 return err
998 }
999
1000 // drop old table
1001 _, err = tx.Exec(`drop table comments`)
1002 return err
1003 })
1004
1005 // add generated at_uri column to pulls table
1006 //
1007 // this requires a full table recreation because stored columns
1008 // cannot be added via alter
1009 //
1010 // disable foreign-keys for the next migration
1011 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1012 orm.RunMigration(conn, logger, "add-at-uri-to-pulls", func(tx *sql.Tx) error {
1013 _, err := tx.Exec(`
1014 create table if not exists pulls_new (
1015 -- identifiers
1016 id integer primary key autoincrement,
1017 pull_id integer not null,
1018 at_uri text generated always as ('at://' || owner_did || '/' || 'sh.tangled.repo.pull' || '/' || rkey) stored,
1019
1020 -- at identifiers
1021 repo_at text not null,
1022 owner_did text not null,
1023 rkey text not null,
1024
1025 -- content
1026 title text not null,
1027 body text not null,
1028 target_branch text not null,
1029 state integer not null default 0 check (state in (0, 1, 2, 3)), -- closed, open, merged, deleted
1030
1031 -- source info
1032 source_branch text,
1033 source_repo_at text,
1034
1035 -- stacking
1036 stack_id text,
1037 change_id text,
1038 parent_change_id text,
1039
1040 -- meta
1041 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1042
1043 -- constraints
1044 unique(repo_at, pull_id),
1045 unique(at_uri),
1046 foreign key (repo_at) references repos(at_uri) on delete cascade
1047 );
1048 `)
1049 if err != nil {
1050 return err
1051 }
1052
1053 // transfer data
1054 _, err = tx.Exec(`
1055 insert into pulls_new (
1056 id, pull_id, repo_at, owner_did, rkey,
1057 title, body, target_branch, state,
1058 source_branch, source_repo_at,
1059 stack_id, change_id, parent_change_id,
1060 created
1061 )
1062 select
1063 id, pull_id, repo_at, owner_did, rkey,
1064 title, body, target_branch, state,
1065 source_branch, source_repo_at,
1066 stack_id, change_id, parent_change_id,
1067 created
1068 from pulls;
1069 `)
1070 if err != nil {
1071 return err
1072 }
1073
1074 // drop old table
1075 _, err = tx.Exec(`drop table pulls`)
1076 if err != nil {
1077 return err
1078 }
1079
1080 // rename new table
1081 _, err = tx.Exec(`alter table pulls_new rename to pulls`)
1082 return err
1083 })
1084 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1085
1086 // remove repo_at and pull_id from pull_submissions and replace with pull_at
1087 //
1088 // this requires a full table recreation because stored columns
1089 // cannot be added via alter
1090 //
1091 // disable foreign-keys for the next migration
1092 conn.ExecContext(ctx, "pragma foreign_keys = off;")
1093 orm.RunMigration(conn, logger, "remove-repo-at-pull-id-from-pull-submissions", func(tx *sql.Tx) error {
1094 _, err := tx.Exec(`
1095 create table if not exists pull_submissions_new (
1096 -- identifiers
1097 id integer primary key autoincrement,
1098 pull_at text not null,
1099
1100 -- content, these are immutable, and require a resubmission to update
1101 round_number integer not null default 0,
1102 patch text,
1103 source_rev text,
1104
1105 -- meta
1106 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1107
1108 -- constraints
1109 unique(pull_at, round_number),
1110 foreign key (pull_at) references pulls(at_uri) on delete cascade
1111 );
1112 `)
1113 if err != nil {
1114 return err
1115 }
1116
1117 // transfer data, constructing pull_at from pulls table
1118 _, err = tx.Exec(`
1119 insert into pull_submissions_new (id, pull_at, round_number, patch, created)
1120 select
1121 ps.id,
1122 'at://' || p.owner_did || '/sh.tangled.repo.pull/' || p.rkey,
1123 ps.round_number,
1124 ps.patch,
1125 ps.created
1126 from pull_submissions ps
1127 join pulls p on ps.repo_at = p.repo_at and ps.pull_id = p.pull_id;
1128 `)
1129 if err != nil {
1130 return err
1131 }
1132
1133 // drop old table
1134 _, err = tx.Exec(`drop table pull_submissions`)
1135 if err != nil {
1136 return err
1137 }
1138
1139 // rename new table
1140 _, err = tx.Exec(`alter table pull_submissions_new rename to pull_submissions`)
1141 return err
1142 })
1143 conn.ExecContext(ctx, "pragma foreign_keys = on;")
1144
1145 // knots may report the combined patch for a comparison, we can store that on the appview side
1146 // (but not on the pds record), because calculating the combined patch requires a git index
1147 orm.RunMigration(conn, logger, "add-combined-column-submissions", func(tx *sql.Tx) error {
1148 _, err := tx.Exec(`
1149 alter table pull_submissions add column combined text;
1150 `)
1151 return err
1152 })
1153
1154 orm.RunMigration(conn, logger, "add-pronouns-profile", func(tx *sql.Tx) error {
1155 _, err := tx.Exec(`
1156 alter table profile add column pronouns text;
1157 `)
1158 return err
1159 })
1160
1161 orm.RunMigration(conn, logger, "add-meta-column-repos", func(tx *sql.Tx) error {
1162 _, err := tx.Exec(`
1163 alter table repos add column website text;
1164 alter table repos add column topics text;
1165 `)
1166 return err
1167 })
1168
1169 orm.RunMigration(conn, logger, "add-usermentioned-preference", func(tx *sql.Tx) error {
1170 _, err := tx.Exec(`
1171 alter table notification_preferences add column user_mentioned integer not null default 1;
1172 `)
1173 return err
1174 })
1175
1176 // remove the foreign key constraints from stars.
1177 orm.RunMigration(conn, logger, "generalize-stars-subject", func(tx *sql.Tx) error {
1178 _, err := tx.Exec(`
1179 create table stars_new (
1180 id integer primary key autoincrement,
1181 did text not null,
1182 rkey text not null,
1183
1184 subject_at text not null,
1185
1186 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1187 unique(did, rkey),
1188 unique(did, subject_at)
1189 );
1190
1191 insert into stars_new (
1192 id,
1193 did,
1194 rkey,
1195 subject_at,
1196 created
1197 )
1198 select
1199 id,
1200 starred_by_did,
1201 rkey,
1202 repo_at,
1203 created
1204 from stars;
1205
1206 drop table stars;
1207 alter table stars_new rename to stars;
1208
1209 create index if not exists idx_stars_created on stars(created);
1210 create index if not exists idx_stars_subject_at_created on stars(subject_at, created);
1211 `)
1212 return err
1213 })
1214
1215 orm.RunMigration(conn, logger, "add-avatar-to-profile", func(tx *sql.Tx) error {
1216 _, err := tx.Exec(`
1217 alter table profile add column avatar text;
1218 `)
1219 return err
1220 })
1221
1222 orm.RunMigration(conn, logger, "remove-profile-stats-column-constraint", func(tx *sql.Tx) error {
1223 _, err := tx.Exec(`
1224 -- create new table without the check constraint
1225 create table profile_stats_new (
1226 id integer primary key autoincrement,
1227 did text not null,
1228 kind text not null, -- no constraint this time
1229 foreign key (did) references profile(did) on delete cascade
1230 );
1231
1232 -- copy data from old table
1233 insert into profile_stats_new (id, did, kind)
1234 select id, did, kind
1235 from profile_stats;
1236
1237 -- drop old table
1238 drop table profile_stats;
1239
1240 -- rename new table
1241 alter table profile_stats_new rename to profile_stats;
1242 `)
1243 return err
1244 })
1245
1246 // we cannot modify user-owned record on repository delete
1247 orm.RunMigration(conn, logger, "remove-foreign-key-profile_pinned_repositories-and-repos", func(tx *sql.Tx) error {
1248 _, err := tx.Exec(`
1249 create table profile_pinned_repositories_new (
1250 did text not null,
1251
1252 -- data
1253 at_uri text not null,
1254
1255 -- constraints
1256 unique(did, at_uri),
1257 foreign key (did) references profile(did) on delete cascade
1258 );
1259
1260 insert into profile_pinned_repositories_new (did, at_uri)
1261 select did, at_uri from profile_pinned_repositories;
1262
1263 drop table profile_pinned_repositories;
1264 alter table profile_pinned_repositories_new rename to profile_pinned_repositories;
1265 `)
1266 return err
1267 })
1268
1269 // several changes here
1270 // 1. remove autoincrement id for these tables
1271 // 2. remove unique constraints other than (did, rkey) to handle non-unique atproto records
1272 // 3. add generated at_uri field
1273 //
1274 // see comments below and commit message for details
1275 orm.RunMigration(conn, logger, "flexible-stars-reactions-follows-public_keys", func(tx *sql.Tx) error {
1276 // - add at_uri
1277 // - remove unique constraint (did, subject_at)
1278 if _, err := tx.Exec(`
1279 create table stars_new (
1280 did text not null,
1281 rkey text not null,
1282 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.feed.star' || '/' || rkey) stored,
1283
1284 subject_at text not null,
1285 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1286
1287 unique(did, rkey)
1288 );
1289
1290 insert into stars_new (did, rkey, subject_at, created)
1291 select did, rkey, subject_at, created from stars;
1292
1293 drop table stars;
1294 alter table stars_new rename to stars;
1295 `); err != nil {
1296 return fmt.Errorf("migrating stars: %w", err)
1297 }
1298
1299 // - add at_uri
1300 // - reacted_by_did -> did
1301 // - thread_at -> subject_at
1302 // - remove unique constraint
1303 if _, err := tx.Exec(`
1304 create table reactions_new (
1305 did text not null,
1306 rkey text not null,
1307 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.feed.reaction' || '/' || rkey) stored,
1308
1309 subject_at text not null,
1310 kind text not null,
1311 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1312
1313 unique(did, rkey)
1314 );
1315
1316 insert into reactions_new (did, rkey, subject_at, kind, created)
1317 select reacted_by_did, rkey, thread_at, kind, created from reactions;
1318
1319 drop table reactions;
1320 alter table reactions_new rename to reactions;
1321 `); err != nil {
1322 return fmt.Errorf("migrating reactions: %w", err)
1323 }
1324
1325 // - add at_uri column
1326 // - user_did -> did
1327 // - followed_at -> created
1328 // - remove unique constraint
1329 // - remove check constraint
1330 if _, err := tx.Exec(`
1331 create table follows_new (
1332 did text not null,
1333 rkey text not null,
1334 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.graph.follow' || '/' || rkey) stored,
1335
1336 subject_did text not null,
1337 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1338
1339 unique(did, rkey)
1340 );
1341
1342 insert into follows_new (did, rkey, subject_did, created)
1343 select user_did, rkey, subject_did, followed_at from follows;
1344
1345 drop table follows;
1346 alter table follows_new rename to follows;
1347 `); err != nil {
1348 return fmt.Errorf("migrating follows: %w", err)
1349 }
1350
1351 // - add at_uri column
1352 // - remove foreign key relationship from repos
1353 if _, err := tx.Exec(`
1354 create table public_keys_new (
1355 did text not null,
1356 rkey text not null,
1357 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.publicKey' || '/' || rkey) stored,
1358
1359 name text not null,
1360 key text not null,
1361 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
1362
1363 unique(did, rkey)
1364 );
1365
1366 insert into public_keys_new (did, rkey, name, key, created)
1367 select did, rkey, name, key, created from public_keys;
1368
1369 drop table public_keys;
1370 alter table public_keys_new rename to public_keys;
1371 `); err != nil {
1372 return fmt.Errorf("migrating public_keys: %w", err)
1373 }
1374
1375 return nil
1376 })
1377
1378 return &DB{
1379 db,
1380 logger,
1381 }, nil
1382}
1383
1384func (d *DB) Close() error {
1385 return d.DB.Close()
1386}