margin.at
Write on the margins of the internet. Powered by the AT Protocol.
margin.at
extension
web
atproto
comments
scanash.com
1package db
2
3import (
4 "database/sql"
5 "encoding/json"
6 "fmt"
7 "strings"
8 "time"
9
10 _ "github.com/lib/pq"
11 _ "github.com/mattn/go-sqlite3"
12)
13
14type DB struct {
15 *sql.DB
16 driver string
17}
18
19type Annotation struct {
20 URI string `json:"uri"`
21 AuthorDID string `json:"authorDid"`
22 Motivation string `json:"motivation,omitempty"`
23 BodyValue *string `json:"bodyValue,omitempty"`
24 BodyFormat *string `json:"bodyFormat,omitempty"`
25 BodyURI *string `json:"bodyUri,omitempty"`
26 TargetSource string `json:"targetSource"`
27 TargetHash string `json:"targetHash"`
28 TargetTitle *string `json:"targetTitle,omitempty"`
29 SelectorJSON *string `json:"selector,omitempty"`
30 TagsJSON *string `json:"tags,omitempty"`
31 CreatedAt time.Time `json:"createdAt"`
32 IndexedAt time.Time `json:"indexedAt"`
33 CID *string `json:"cid,omitempty"`
34}
35
36type Selector struct {
37 Type string `json:"type"`
38 Exact string `json:"exact,omitempty"`
39 Prefix string `json:"prefix,omitempty"`
40 Suffix string `json:"suffix,omitempty"`
41 Start *int `json:"start,omitempty"`
42 End *int `json:"end,omitempty"`
43 Value string `json:"value,omitempty"`
44}
45
46type Highlight struct {
47 URI string `json:"uri"`
48 AuthorDID string `json:"authorDid"`
49 TargetSource string `json:"targetSource"`
50 TargetHash string `json:"targetHash"`
51 TargetTitle *string `json:"targetTitle,omitempty"`
52 SelectorJSON *string `json:"selector,omitempty"`
53 Color *string `json:"color,omitempty"`
54 TagsJSON *string `json:"tags,omitempty"`
55 CreatedAt time.Time `json:"createdAt"`
56 IndexedAt time.Time `json:"indexedAt"`
57 CID *string `json:"cid,omitempty"`
58}
59
60type Bookmark struct {
61 URI string `json:"uri"`
62 AuthorDID string `json:"authorDid"`
63 Source string `json:"source"`
64 SourceHash string `json:"sourceHash"`
65 Title *string `json:"title,omitempty"`
66 Description *string `json:"description,omitempty"`
67 TagsJSON *string `json:"tags,omitempty"`
68 CreatedAt time.Time `json:"createdAt"`
69 IndexedAt time.Time `json:"indexedAt"`
70 CID *string `json:"cid,omitempty"`
71}
72
73type Reply struct {
74 URI string `json:"uri"`
75 AuthorDID string `json:"authorDid"`
76 ParentURI string `json:"parentUri"`
77 RootURI string `json:"rootUri"`
78 Text string `json:"text"`
79 Format *string `json:"format,omitempty"`
80 CreatedAt time.Time `json:"createdAt"`
81 IndexedAt time.Time `json:"indexedAt"`
82 CID *string `json:"cid,omitempty"`
83}
84
85type Like struct {
86 URI string `json:"uri"`
87 AuthorDID string `json:"authorDid"`
88 SubjectURI string `json:"subjectUri"`
89 CreatedAt time.Time `json:"createdAt"`
90 IndexedAt time.Time `json:"indexedAt"`
91}
92
93type Collection struct {
94 URI string `json:"uri"`
95 AuthorDID string `json:"authorDid"`
96 Name string `json:"name"`
97 Description *string `json:"description,omitempty"`
98 Icon *string `json:"icon,omitempty"`
99 CreatedAt time.Time `json:"createdAt"`
100 IndexedAt time.Time `json:"indexedAt"`
101}
102
103type CollectionItem struct {
104 URI string `json:"uri"`
105 AuthorDID string `json:"authorDid"`
106 CollectionURI string `json:"collectionUri"`
107 AnnotationURI string `json:"annotationUri"`
108 Position int `json:"position"`
109 CreatedAt time.Time `json:"createdAt"`
110 IndexedAt time.Time `json:"indexedAt"`
111}
112
113type Notification struct {
114 ID int `json:"id"`
115 RecipientDID string `json:"recipientDid"`
116 ActorDID string `json:"actorDid"`
117 Type string `json:"type"`
118 SubjectURI string `json:"subjectUri"`
119 CreatedAt time.Time `json:"createdAt"`
120 ReadAt *time.Time `json:"readAt,omitempty"`
121}
122
123type APIKey struct {
124 ID string `json:"id"`
125 OwnerDID string `json:"ownerDid"`
126 Name string `json:"name"`
127 KeyHash string `json:"-"`
128 CreatedAt time.Time `json:"createdAt"`
129 LastUsedAt *time.Time `json:"lastUsedAt,omitempty"`
130}
131
132type Profile struct {
133 URI string `json:"uri"`
134 AuthorDID string `json:"authorDid"`
135 Bio *string `json:"bio,omitempty"`
136 Website *string `json:"website,omitempty"`
137 LinksJSON *string `json:"links,omitempty"`
138 CreatedAt time.Time `json:"createdAt"`
139 IndexedAt time.Time `json:"indexedAt"`
140 CID *string `json:"cid,omitempty"`
141}
142
143func New(dsn string) (*DB, error) {
144 driver := "sqlite3"
145 if strings.HasPrefix(dsn, "postgres://") || strings.HasPrefix(dsn, "postgresql://") {
146 driver = "postgres"
147 }
148
149 db, err := sql.Open(driver, dsn)
150 if err != nil {
151 return nil, err
152 }
153
154 if driver == "sqlite3" {
155 if _, err := db.Exec("PRAGMA journal_mode=WAL;"); err != nil {
156 return nil, fmt.Errorf("failed to set WAL mode: %w", err)
157 }
158 db.Exec("PRAGMA synchronous=NORMAL;")
159 db.Exec("PRAGMA busy_timeout=5000;")
160 db.Exec("PRAGMA cache_size=-2000;")
161 db.Exec("PRAGMA foreign_keys=ON;")
162
163 db.SetMaxOpenConns(25)
164 db.SetMaxIdleConns(25)
165 db.SetConnMaxLifetime(5 * time.Minute)
166 } else {
167 db.SetMaxOpenConns(50)
168 db.SetMaxIdleConns(25)
169 db.SetConnMaxLifetime(10 * time.Minute)
170 }
171
172 if err := db.Ping(); err != nil {
173 return nil, err
174 }
175
176 return &DB{DB: db, driver: driver}, nil
177}
178
179func (db *DB) Migrate() error {
180
181 dateType := "DATETIME"
182 if db.driver == "postgres" {
183 dateType = "TIMESTAMP"
184 }
185
186 _, err := db.Exec(`
187 CREATE TABLE IF NOT EXISTS annotations (
188 uri TEXT PRIMARY KEY,
189 author_did TEXT NOT NULL,
190 motivation TEXT,
191 body_value TEXT,
192 body_format TEXT DEFAULT 'text/plain',
193 body_uri TEXT,
194 target_source TEXT NOT NULL,
195 target_hash TEXT NOT NULL,
196 target_title TEXT,
197 selector_json TEXT,
198 tags_json TEXT,
199 created_at ` + dateType + ` NOT NULL,
200 indexed_at ` + dateType + ` NOT NULL,
201 cid TEXT
202 )`)
203 if err != nil {
204 return err
205 }
206
207 db.Exec(`CREATE INDEX IF NOT EXISTS idx_annotations_target_hash ON annotations(target_hash)`)
208 db.Exec(`CREATE INDEX IF NOT EXISTS idx_annotations_author_did ON annotations(author_did)`)
209 db.Exec(`CREATE INDEX IF NOT EXISTS idx_annotations_motivation ON annotations(motivation)`)
210 db.Exec(`CREATE INDEX IF NOT EXISTS idx_annotations_created_at ON annotations(created_at DESC)`)
211
212 db.Exec(`CREATE TABLE IF NOT EXISTS highlights (
213 uri TEXT PRIMARY KEY,
214 author_did TEXT NOT NULL,
215 target_source TEXT NOT NULL,
216 target_hash TEXT NOT NULL,
217 target_title TEXT,
218 selector_json TEXT,
219 color TEXT,
220 tags_json TEXT,
221 created_at ` + dateType + ` NOT NULL,
222 indexed_at ` + dateType + ` NOT NULL,
223 cid TEXT
224 )`)
225 db.Exec(`CREATE INDEX IF NOT EXISTS idx_highlights_target_hash ON highlights(target_hash)`)
226 db.Exec(`CREATE INDEX IF NOT EXISTS idx_highlights_author_did ON highlights(author_did)`)
227
228 db.Exec(`CREATE TABLE IF NOT EXISTS bookmarks (
229 uri TEXT PRIMARY KEY,
230 author_did TEXT NOT NULL,
231 source TEXT NOT NULL,
232 source_hash TEXT NOT NULL,
233 title TEXT,
234 description TEXT,
235 tags_json TEXT,
236 created_at ` + dateType + ` NOT NULL,
237 indexed_at ` + dateType + ` NOT NULL,
238 cid TEXT
239 )`)
240 db.Exec(`CREATE INDEX IF NOT EXISTS idx_bookmarks_source_hash ON bookmarks(source_hash)`)
241 db.Exec(`CREATE INDEX IF NOT EXISTS idx_bookmarks_author_did ON bookmarks(author_did)`)
242
243 db.Exec(`CREATE TABLE IF NOT EXISTS replies (
244 uri TEXT PRIMARY KEY,
245 author_did TEXT NOT NULL,
246 parent_uri TEXT NOT NULL,
247 root_uri TEXT NOT NULL,
248 text TEXT NOT NULL,
249 format TEXT DEFAULT 'text/plain',
250 created_at ` + dateType + ` NOT NULL,
251 indexed_at ` + dateType + ` NOT NULL,
252 cid TEXT
253 )`)
254 db.Exec(`CREATE INDEX IF NOT EXISTS idx_replies_parent_uri ON replies(parent_uri)`)
255 db.Exec(`CREATE INDEX IF NOT EXISTS idx_replies_root_uri ON replies(root_uri)`)
256
257 db.Exec(`CREATE TABLE IF NOT EXISTS likes (
258 uri TEXT PRIMARY KEY,
259 author_did TEXT NOT NULL,
260 subject_uri TEXT NOT NULL,
261 created_at ` + dateType + ` NOT NULL,
262 indexed_at ` + dateType + ` NOT NULL
263 )`)
264 db.Exec(`CREATE INDEX IF NOT EXISTS idx_likes_subject_uri ON likes(subject_uri)`)
265 db.Exec(`CREATE INDEX IF NOT EXISTS idx_likes_author_did ON likes(author_did)`)
266 db.Exec(`CREATE INDEX IF NOT EXISTS idx_likes_author_subject ON likes(author_did, subject_uri)`)
267
268 db.Exec(`CREATE TABLE IF NOT EXISTS collections (
269 uri TEXT PRIMARY KEY,
270 author_did TEXT NOT NULL,
271 name TEXT NOT NULL,
272 description TEXT,
273 icon TEXT,
274 created_at ` + dateType + ` NOT NULL,
275 indexed_at ` + dateType + ` NOT NULL
276 )`)
277 db.Exec(`CREATE INDEX IF NOT EXISTS idx_collections_author_did ON collections(author_did)`)
278
279 db.Exec(`CREATE TABLE IF NOT EXISTS collection_items (
280 uri TEXT PRIMARY KEY,
281 author_did TEXT NOT NULL,
282 collection_uri TEXT NOT NULL,
283 annotation_uri TEXT NOT NULL,
284 position INTEGER DEFAULT 0,
285 created_at ` + dateType + ` NOT NULL,
286 indexed_at ` + dateType + ` NOT NULL
287 )`)
288 db.Exec(`CREATE INDEX IF NOT EXISTS idx_collection_items_collection ON collection_items(collection_uri)`)
289 db.Exec(`CREATE INDEX IF NOT EXISTS idx_collection_items_annotation ON collection_items(annotation_uri)`)
290
291 db.Exec(`CREATE TABLE IF NOT EXISTS sessions (
292 id TEXT PRIMARY KEY,
293 did TEXT NOT NULL,
294 handle TEXT NOT NULL,
295 access_token TEXT NOT NULL,
296 refresh_token TEXT NOT NULL,
297 dpop_key TEXT,
298 created_at ` + dateType + ` NOT NULL,
299 expires_at ` + dateType + ` NOT NULL
300 )`)
301 db.Exec(`CREATE INDEX IF NOT EXISTS idx_sessions_did ON sessions(did)`)
302
303 autoInc := "INTEGER PRIMARY KEY AUTOINCREMENT"
304 if db.driver == "postgres" {
305 autoInc = "SERIAL PRIMARY KEY"
306 }
307
308 db.Exec(`CREATE TABLE IF NOT EXISTS edit_history (
309 id ` + autoInc + `,
310 uri TEXT NOT NULL,
311 record_type TEXT NOT NULL,
312 previous_content TEXT NOT NULL,
313 previous_cid TEXT,
314 edited_at ` + dateType + ` NOT NULL
315 )`)
316 db.Exec(`CREATE INDEX IF NOT EXISTS idx_edit_history_uri ON edit_history(uri)`)
317 db.Exec(`CREATE INDEX IF NOT EXISTS idx_edit_history_edited_at ON edit_history(edited_at DESC)`)
318
319 db.Exec(`CREATE TABLE IF NOT EXISTS notifications (
320 id ` + autoInc + `,
321 recipient_did TEXT NOT NULL,
322 actor_did TEXT NOT NULL,
323 type TEXT NOT NULL,
324 subject_uri TEXT NOT NULL,
325 created_at ` + dateType + ` NOT NULL,
326 read_at ` + dateType + `
327 )`)
328 db.Exec(`CREATE INDEX IF NOT EXISTS idx_notifications_recipient ON notifications(recipient_did)`)
329 db.Exec(`CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications(created_at DESC)`)
330
331 db.Exec(`CREATE TABLE IF NOT EXISTS api_keys (
332 id TEXT PRIMARY KEY,
333 owner_did TEXT NOT NULL,
334 name TEXT NOT NULL,
335 key_hash TEXT NOT NULL,
336 created_at ` + dateType + ` NOT NULL,
337 last_used_at ` + dateType + `
338 )`)
339 db.Exec(`CREATE INDEX IF NOT EXISTS idx_api_keys_owner ON api_keys(owner_did)`)
340 db.Exec(`CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(key_hash)`)
341
342 db.Exec(`CREATE TABLE IF NOT EXISTS profiles (
343 uri TEXT PRIMARY KEY,
344 author_did TEXT NOT NULL,
345 bio TEXT,
346 website TEXT,
347 links_json TEXT,
348 created_at ` + dateType + ` NOT NULL,
349 indexed_at ` + dateType + ` NOT NULL,
350 cid TEXT
351 )`)
352 db.Exec(`CREATE INDEX IF NOT EXISTS idx_profiles_author_did ON profiles(author_did)`)
353
354 db.runMigrations()
355
356 db.Exec(`CREATE TABLE IF NOT EXISTS cursors (
357 id TEXT PRIMARY KEY,
358 last_cursor BIGINT NOT NULL,
359 updated_at ` + dateType + ` NOT NULL
360 )`)
361
362 db.runMigrations()
363
364 return nil
365}
366
367func (db *DB) GetCursor(id string) (int64, error) {
368 var cursor int64
369 err := db.QueryRow("SELECT last_cursor FROM cursors WHERE id = $1", id).Scan(&cursor)
370 if err == sql.ErrNoRows {
371 return 0, nil
372 }
373 if err != nil {
374 return 0, err
375 }
376 return cursor, nil
377}
378
379func (db *DB) SetCursor(id string, cursor int64) error {
380 query := `
381 INSERT INTO cursors (id, last_cursor, updated_at)
382 VALUES ($1, $2, $3)
383 ON CONFLICT(id) DO UPDATE SET
384 last_cursor = EXCLUDED.last_cursor,
385 updated_at = EXCLUDED.updated_at
386 `
387 _, err := db.Exec(query, id, cursor, time.Now())
388 return err
389}
390
391func (db *DB) GetProfile(did string) (*Profile, error) {
392 var p Profile
393 err := db.QueryRow("SELECT uri, author_did, bio, website, links_json, created_at, indexed_at FROM profiles WHERE author_did = $1", did).Scan(
394 &p.URI, &p.AuthorDID, &p.Bio, &p.Website, &p.LinksJSON, &p.CreatedAt, &p.IndexedAt,
395 )
396 if err == sql.ErrNoRows {
397 return nil, nil
398 }
399 if err != nil {
400 return nil, err
401 }
402 return &p, nil
403}
404
405func (db *DB) UpsertProfile(p *Profile) error {
406 query := `
407 INSERT INTO profiles (uri, author_did, bio, website, links_json, created_at, indexed_at)
408 VALUES ($1, $2, $3, $4, $5, $6, $7)
409 ON CONFLICT(uri) DO UPDATE SET
410 bio = EXCLUDED.bio,
411 website = EXCLUDED.website,
412 links_json = EXCLUDED.links_json,
413 indexed_at = EXCLUDED.indexed_at
414 `
415 _, err := db.Exec(db.Rebind(query), p.URI, p.AuthorDID, p.Bio, p.Website, p.LinksJSON, p.CreatedAt, p.IndexedAt)
416 return err
417}
418
419func (db *DB) DeleteProfile(uri string) error {
420 _, err := db.Exec("DELETE FROM profiles WHERE uri = $1", uri)
421 return err
422}
423
424func (db *DB) runMigrations() {
425
426 db.Exec(`ALTER TABLE sessions ADD COLUMN dpop_key TEXT`)
427
428 db.Exec(`ALTER TABLE annotations ADD COLUMN motivation TEXT`)
429 db.Exec(`ALTER TABLE annotations ADD COLUMN body_value TEXT`)
430 db.Exec(`ALTER TABLE annotations ADD COLUMN body_format TEXT DEFAULT 'text/plain'`)
431 db.Exec(`ALTER TABLE annotations ADD COLUMN body_uri TEXT`)
432 db.Exec(`ALTER TABLE annotations ADD COLUMN target_source TEXT`)
433 db.Exec(`ALTER TABLE annotations ADD COLUMN target_hash TEXT`)
434 db.Exec(`ALTER TABLE annotations ADD COLUMN target_title TEXT`)
435 db.Exec(`ALTER TABLE annotations ADD COLUMN selector_json TEXT`)
436 db.Exec(`ALTER TABLE annotations ADD COLUMN tags_json TEXT`)
437 db.Exec(`ALTER TABLE annotations ADD COLUMN cid TEXT`)
438
439 db.Exec(`UPDATE annotations SET target_source = url WHERE target_source IS NULL AND url IS NOT NULL`)
440 db.Exec(`UPDATE annotations SET target_hash = url_hash WHERE target_hash IS NULL AND url_hash IS NOT NULL`)
441 db.Exec(`UPDATE annotations SET body_value = text WHERE body_value IS NULL AND text IS NOT NULL`)
442 db.Exec(`UPDATE annotations SET target_title = title WHERE target_title IS NULL AND title IS NOT NULL`)
443 db.Exec(`UPDATE annotations SET motivation = 'commenting' WHERE motivation IS NULL`)
444
445 db.Exec(`ALTER TABLE profiles ADD COLUMN website TEXT`)
446
447 if db.driver == "postgres" {
448 db.Exec(`ALTER TABLE cursors ALTER COLUMN last_cursor TYPE BIGINT`)
449 }
450}
451
452func (db *DB) Close() error {
453 return db.DB.Close()
454}
455
456func (db *DB) Rebind(query string) string {
457 if db.driver != "postgres" {
458 return query
459 }
460
461 if !strings.Contains(query, "?") {
462 return query
463 }
464
465 var builder strings.Builder
466 builder.Grow(len(query) + 20)
467
468 paramCount := 1
469 for _, r := range query {
470 if r == '?' {
471 fmt.Fprintf(&builder, "$%d", paramCount)
472 paramCount++
473 } else {
474 builder.WriteRune(r)
475 }
476 }
477 return builder.String()
478}
479
480func ParseSelector(selectorJSON *string) (*Selector, error) {
481 if selectorJSON == nil || *selectorJSON == "" {
482 return nil, nil
483 }
484 var s Selector
485 err := json.Unmarshal([]byte(*selectorJSON), &s)
486 if err != nil {
487 return nil, err
488 }
489 return &s, nil
490}
491
492func ParseTags(tagsJSON *string) ([]string, error) {
493 if tagsJSON == nil || *tagsJSON == "" {
494 return nil, nil
495 }
496 var tags []string
497 err := json.Unmarshal([]byte(*tagsJSON), &tags)
498 if err != nil {
499 return nil, err
500 }
501 return tags, nil
502}