this repo has no description
tangled.org
1package db
2
3import (
4 "context"
5 "database/sql"
6 "log"
7
8 _ "github.com/mattn/go-sqlite3"
9)
10
11type DB struct {
12 *sql.DB
13}
14
15type Execer interface {
16 Query(query string, args ...any) (*sql.Rows, error)
17 QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
18 QueryRow(query string, args ...any) *sql.Row
19 QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
20 Exec(query string, args ...any) (sql.Result, error)
21 ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
22 Prepare(query string) (*sql.Stmt, error)
23 PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
24}
25
26func Make(dbPath string) (*DB, error) {
27 db, err := sql.Open("sqlite3", dbPath)
28 if err != nil {
29 return nil, err
30 }
31 _, err = db.Exec(`
32 pragma journal_mode = WAL;
33 pragma synchronous = normal;
34 pragma foreign_keys = on;
35 pragma temp_store = memory;
36 pragma mmap_size = 30000000000;
37 pragma page_size = 32768;
38 pragma auto_vacuum = incremental;
39 pragma busy_timeout = 5000;
40
41 create table if not exists registrations (
42 id integer primary key autoincrement,
43 domain text not null unique,
44 did text not null,
45 secret text not null,
46 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
47 registered text
48 );
49 create table if not exists public_keys (
50 id integer primary key autoincrement,
51 did text not null,
52 name text not null,
53 key text not null,
54 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
55 unique(did, name, key)
56 );
57 create table if not exists repos (
58 id integer primary key autoincrement,
59 did text not null,
60 name text not null,
61 knot text not null,
62 rkey text not null,
63 at_uri text not null unique,
64 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
65 unique(did, name, knot, rkey)
66 );
67 create table if not exists collaborators (
68 id integer primary key autoincrement,
69 did text not null,
70 repo integer not null,
71 foreign key (repo) references repos(id) on delete cascade
72 );
73 create table if not exists follows (
74 user_did text not null,
75 subject_did text not null,
76 rkey text not null,
77 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
78 primary key (user_did, subject_did),
79 check (user_did <> subject_did)
80 );
81 create table if not exists issues (
82 id integer primary key autoincrement,
83 owner_did text not null,
84 repo_at text not null,
85 issue_id integer not null,
86 title text not null,
87 body text not null,
88 open integer not null default 1,
89 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
90 issue_at text,
91 unique(repo_at, issue_id),
92 foreign key (repo_at) references repos(at_uri) on delete cascade
93 );
94 create table if not exists comments (
95 id integer primary key autoincrement,
96 owner_did text not null,
97 issue_id integer not null,
98 repo_at text not null,
99 comment_id integer not null,
100 comment_at text not null,
101 body text not null,
102 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
103 unique(issue_id, comment_id),
104 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
105 );
106 create table if not exists pulls (
107 id integer primary key autoincrement,
108 owner_did text not null,
109 repo_at text not null,
110 pull_id integer not null,
111 title text not null,
112 patch text,
113 patch_at text not null,
114 open integer not null default 1,
115 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
116 unique(repo_at, pull_id),
117 foreign key (repo_at) references repos(at_uri) on delete cascade
118 );
119 create table if not exists pull_comments (
120 id integer primary key autoincrement,
121 owner_did text not null,
122 pull_id integer not null,
123 repo_at text not null,
124 comment_id integer not null,
125 comment_at text not null,
126 body text not null,
127 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
128 unique(pull_id, comment_id),
129 foreign key (repo_at, pull_id) references pulls(repo_at, pull_id) on delete cascade
130 );
131 create table if not exists _jetstream (
132 id integer primary key autoincrement,
133 last_time_us integer not null
134 );
135
136 create table if not exists repo_issue_seqs (
137 repo_at text primary key,
138 next_issue_id integer not null default 1
139 );
140
141 create table if not exists repo_pull_seqs (
142 repo_at text primary key,
143 next_pull_id integer not null default 1
144 );
145
146 create table if not exists stars (
147 id integer primary key autoincrement,
148 starred_by_did text not null,
149 repo_at text not null,
150 rkey text not null,
151 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
152 foreign key (repo_at) references repos(at_uri) on delete cascade,
153 unique(starred_by_did, repo_at)
154 );
155
156 create table if not exists migrations (
157 id integer primary key autoincrement,
158 name text unique
159 )
160 `)
161 if err != nil {
162 return nil, err
163 }
164
165 // run migrations
166 runMigration(db, "add-description-to-repos", func(tx *sql.Tx) error {
167 tx.Exec(`
168 alter table repos add column description text check (length(description) <= 200);
169 `)
170 return nil
171 })
172
173 runMigration(db, "add-rkey-to-pubkeys", func(tx *sql.Tx) error {
174 // add unconstrained column
175 _, err := tx.Exec(`
176 alter table public_keys
177 add column rkey text;
178 `)
179 if err != nil {
180 return err
181 }
182
183 // backfill
184 _, err = tx.Exec(`
185 update public_keys
186 set rkey = ''
187 where rkey is null;
188 `)
189 if err != nil {
190 return err
191 }
192
193 return nil
194 })
195
196 return &DB{db}, nil
197}
198
199type migrationFn = func(*sql.Tx) error
200
201func runMigration(d *sql.DB, name string, migrationFn migrationFn) error {
202 tx, err := d.Begin()
203 if err != nil {
204 return err
205 }
206 defer tx.Rollback()
207
208 var exists bool
209 err = tx.QueryRow("select exists (select 1 from migrations where name = ?)", name).Scan(&exists)
210 if err != nil {
211 return err
212 }
213
214 if !exists {
215 // run migration
216 err = migrationFn(tx)
217 if err != nil {
218 log.Printf("Failed to run migration %s: %v", name, err)
219 return err
220 }
221
222 // mark migration as complete
223 _, err = tx.Exec("insert into migrations (name) values (?)", name)
224 if err != nil {
225 log.Printf("Failed to mark migration %s as complete: %v", name, err)
226 return err
227 }
228
229 // commit the transaction
230 if err := tx.Commit(); err != nil {
231 return err
232 }
233
234 log.Printf("migration %s applied successfully", name)
235 } else {
236 log.Printf("skipped migration %s, already applied", name)
237 }
238
239 return nil
240}