pompy.dev
forked from
tangled.org/core
this repo has no description
1package db
2
3import (
4 "context"
5 "database/sql"
6 "strings"
7
8 "github.com/bluesky-social/indigo/atproto/syntax"
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}
17
18func Make(ctx context.Context, dbPath string) (*DB, error) {
19 // https://github.com/mattn/go-sqlite3#connection-string
20 opts := []string{
21 "_foreign_keys=1",
22 "_journal_mode=WAL",
23 "_synchronous=NORMAL",
24 "_auto_vacuum=incremental",
25 }
26
27 logger := log.FromContext(ctx)
28 logger = log.SubLogger(logger, "db")
29
30 db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
31 if err != nil {
32 return nil, err
33 }
34
35 conn, err := db.Conn(ctx)
36 if err != nil {
37 return nil, err
38 }
39 defer conn.Close()
40
41 _, err = db.Exec(`
42 create table if not exists _jetstream (
43 id integer primary key autoincrement,
44 last_time_us integer not null
45 );
46
47 create table if not exists known_dids (
48 did text primary key
49 );
50
51 create table if not exists repos (
52 id integer primary key autoincrement,
53 knot text not null,
54 owner text not null,
55 name text not null,
56 addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
57
58 unique(owner, name)
59 );
60
61 create table if not exists spindle_members (
62 -- identifiers for the record
63 id integer primary key autoincrement,
64 did text not null,
65 rkey text not null,
66
67 -- data
68 instance text not null,
69 subject text not null,
70 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
71
72 -- constraints
73 unique (did, instance, subject)
74 );
75
76 -- status event for a single workflow
77 create table if not exists events (
78 rkey text not null,
79 nsid text not null,
80 event text not null, -- json
81 created integer not null -- unix nanos
82 );
83 `)
84 if err != nil {
85 return nil, err
86 }
87
88 // run migrations
89
90 // NOTE: this won't migrate existing records
91 // they will be fetched again with tap instead
92 orm.RunMigration(conn, logger, "add-rkey-to-repos", func(tx *sql.Tx) error {
93 // archive legacy repos (just in case)
94 _, err = tx.Exec(`alter table repos rename to repos_old`)
95 if err != nil {
96 return err
97 }
98
99 _, err := tx.Exec(`
100 create table repos_new (
101 -- identifiers
102 id integer primary key autoincrement,
103 did text not null,
104 rkey text not null,
105 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo' || '/' || rkey) stored,
106
107 name text not null,
108 knot text not null,
109
110 addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
111 unique(did, rkey)
112 );
113 `)
114 if err != nil {
115 return err
116 }
117
118 return nil
119 })
120
121 return &DB{db}, nil
122}
123
124func (d *DB) IsKnownDid(did syntax.DID) (bool, error) {
125 // is spindle member / repo collaborator
126 var exists bool
127 err := d.QueryRow(
128 `select exists (
129 select 1 from repo_collaborators where did = ?
130 union all
131 select 1 from spindle_members where did = ?
132 )`,
133 did,
134 did,
135 ).Scan(&exists)
136 return exists, err
137}