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