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