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