lewis.moe
Monorepo for Tangled
1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log/slog"
8 "os"
9 "strings"
10
11 securejoin "github.com/cyphar/filepath-securejoin"
12 _ "github.com/mattn/go-sqlite3"
13 "tangled.org/core/log"
14)
15
16type DB struct {
17 db *sql.DB
18 logger *slog.Logger
19}
20
21func Setup(ctx context.Context, dbPath string) (*DB, error) {
22 // https://github.com/mattn/go-sqlite3#connection-string
23 opts := []string{
24 "_foreign_keys=1",
25 "_journal_mode=WAL",
26 "_synchronous=NORMAL",
27 "_auto_vacuum=incremental",
28 }
29
30 logger := log.FromContext(ctx)
31 logger = log.SubLogger(logger, "db")
32
33 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
34 if err != nil {
35 return nil, err
36 }
37
38 conn, err := db.Conn(ctx)
39 if err != nil {
40 return nil, err
41 }
42 defer conn.Close()
43
44 _, err = conn.ExecContext(ctx, `
45 create table if not exists known_dids (
46 did text primary key
47 );
48
49 create table if not exists public_keys (
50 id integer primary key autoincrement,
51 did text not null,
52 key text not null,
53 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
54 unique(did, key),
55 foreign key (did) references known_dids(did) on delete cascade
56 );
57
58 create table if not exists _jetstream (
59 id integer primary key autoincrement,
60 last_time_us integer not null
61 );
62
63 create table if not exists events (
64 rkey text not null,
65 nsid text not null,
66 event text not null, -- json
67 created integer not null default (strftime('%s', 'now')),
68 primary key (rkey, nsid)
69 );
70
71 create table if not exists repo_keys (
72 repo_did text primary key,
73 signing_key blob not null,
74 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
75 );
76
77 create table if not exists migrations (
78 id integer primary key autoincrement,
79 name text unique
80 );
81 `)
82 if err != nil {
83 return nil, err
84 }
85
86 migrationCheck := func(name string) bool {
87 var count int
88 conn.QueryRowContext(ctx, `SELECT count(1) FROM migrations WHERE name = ?`, name).Scan(&count)
89 return count > 0
90 }
91
92 runMigration := func(name string, fn func() error) error {
93 if migrationCheck(name) {
94 return nil
95 }
96 if err := fn(); err != nil {
97 return fmt.Errorf("migration %q failed: %w", name, err)
98 }
99 _, err := conn.ExecContext(ctx, `INSERT INTO migrations (name) VALUES (?)`, name)
100 if err != nil {
101 return fmt.Errorf("recording migration %q: %w", name, err)
102 }
103 return nil
104 }
105
106 if err := runMigration("add-owner-did-to-repo-keys", func() error {
107 _, mErr := conn.ExecContext(ctx, `ALTER TABLE repo_keys ADD COLUMN owner_did TEXT`)
108 return mErr
109 }); err != nil {
110 return nil, err
111 }
112
113 if err := runMigration("add-repo-name-to-repo-keys", func() error {
114 _, mErr := conn.ExecContext(ctx, `ALTER TABLE repo_keys ADD COLUMN repo_name TEXT`)
115 return mErr
116 }); err != nil {
117 return nil, err
118 }
119
120 if err := runMigration("add-unique-owner-repo-on-repo-keys", func() error {
121 _, mErr := conn.ExecContext(ctx, `CREATE UNIQUE INDEX IF NOT EXISTS idx_repo_keys_owner_repo ON repo_keys(owner_did, repo_name)`)
122 return mErr
123 }); err != nil {
124 return nil, err
125 }
126
127 if err := runMigration("add-key-type-and-nullable-signing-key", func() error {
128 tx, txErr := conn.BeginTx(ctx, nil)
129 if txErr != nil {
130 return txErr
131 }
132 defer tx.Rollback()
133
134 _, mErr := tx.ExecContext(ctx, `
135 create table repo_keys_new (
136 repo_did text primary key,
137 signing_key blob,
138 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
139 owner_did text,
140 repo_name text,
141 key_type text not null default 'k256'
142 );
143 insert into repo_keys_new
144 select repo_did, signing_key, created_at, owner_did, repo_name, 'k256'
145 from repo_keys;
146 drop table repo_keys;
147 alter table repo_keys_new rename to repo_keys;
148 create unique index if not exists idx_repo_keys_owner_repo
149 on repo_keys(owner_did, repo_name);
150 `)
151 if mErr != nil {
152 return mErr
153 }
154 return tx.Commit()
155 }); err != nil {
156 return nil, err
157 }
158
159 return &DB{
160 db: db,
161 logger: logger,
162 }, nil
163}
164
165func (d *DB) StoreRepoKey(repoDid string, signingKey []byte, ownerDid, repoName string) error {
166 _, err := d.db.Exec(
167 `INSERT INTO repo_keys (repo_did, signing_key, owner_did, repo_name, key_type) VALUES (?, ?, ?, ?, 'k256')`,
168 repoDid, signingKey, ownerDid, repoName,
169 )
170 return err
171}
172
173func (d *DB) StoreRepoDidWeb(repoDid, ownerDid, repoName string) error {
174 _, err := d.db.Exec(
175 `INSERT INTO repo_keys (repo_did, signing_key, owner_did, repo_name, key_type) VALUES (?, NULL, ?, ?, 'web')`,
176 repoDid, ownerDid, repoName,
177 )
178 return err
179}
180
181func (d *DB) DeleteRepoKey(repoDid string) error {
182 _, err := d.db.Exec(`DELETE FROM repo_keys WHERE repo_did = ?`, repoDid)
183 return err
184}
185
186func (d *DB) RepoDidExists(repoDid string) (bool, error) {
187 var count int
188 err := d.db.QueryRow(`SELECT count(1) FROM repo_keys WHERE repo_did = ?`, repoDid).Scan(&count)
189 return count > 0, err
190}
191
192func (d *DB) GetRepoDid(ownerDid, repoName string) (string, error) {
193 var repoDid string
194 err := d.db.QueryRow(
195 `SELECT repo_did FROM repo_keys WHERE owner_did = ? AND repo_name = ?`,
196 ownerDid, repoName,
197 ).Scan(&repoDid)
198 return repoDid, err
199}
200
201func (d *DB) GetRepoSigningKey(repoDid string) ([]byte, error) {
202 var signingKey []byte
203 err := d.db.QueryRow(
204 `SELECT signing_key FROM repo_keys WHERE repo_did = ? AND key_type = 'k256'`,
205 repoDid,
206 ).Scan(&signingKey)
207 if err != nil {
208 return nil, fmt.Errorf("retrieving signing key for %s: %w", repoDid, err)
209 }
210 if signingKey == nil {
211 return nil, fmt.Errorf("signing key for %s is null (did:web repo?)", repoDid)
212 }
213 return signingKey, nil
214}
215
216func (d *DB) GetRepoKeyOwner(repoDid string) (ownerDid string, repoName string, err error) {
217 var nullOwner, nullName sql.NullString
218 err = d.db.QueryRow(
219 `SELECT owner_did, repo_name FROM repo_keys WHERE repo_did = ?`,
220 repoDid,
221 ).Scan(&nullOwner, &nullName)
222 if err != nil {
223 return
224 }
225 if !nullOwner.Valid || !nullName.Valid || nullOwner.String == "" || nullName.String == "" {
226 err = fmt.Errorf("repo_keys row for %s has empty or null owner_did or repo_name", repoDid)
227 return
228 }
229 ownerDid = nullOwner.String
230 repoName = nullName.String
231 return
232}
233
234func (d *DB) ResolveRepoDIDOnDisk(scanPath, repoDid string) (repoPath, ownerDid, repoName string, err error) {
235 ownerDid, repoName, err = d.GetRepoKeyOwner(repoDid)
236 if err != nil {
237 return
238 }
239
240 didPath, joinErr := securejoin.SecureJoin(scanPath, repoDid)
241 if joinErr != nil {
242 err = fmt.Errorf("securejoin failed for repo DID path %s: %w", repoDid, joinErr)
243 return
244 }
245
246 if _, statErr := os.Stat(didPath); statErr != nil {
247 err = fmt.Errorf("repo DID directory not found on disk: %s", didPath)
248 return
249 }
250
251 repoPath = didPath
252 return
253}