this repo has no description
tangled.org
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 repo_collaborators (
62 -- identifiers
63 id integer primary key autoincrement,
64 did text not null,
65 rkey text not null,
66 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.collaborator' || '/' || rkey) stored,
67
68 repo text not null,
69 subject text not null,
70
71 addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
72 unique(did, rkey)
73 );
74
75 create table if not exists spindle_members (
76 -- identifiers for the record
77 id integer primary key autoincrement,
78 did text not null,
79 rkey text not null,
80
81 -- data
82 instance text not null,
83 subject text not null,
84 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
85
86 -- constraints
87 unique (did, instance, subject)
88 );
89
90 -- status event for a single workflow
91 create table if not exists events (
92 rkey text not null,
93 nsid text not null,
94 event text not null, -- json
95 created integer not null -- unix nanos
96 );
97 `)
98 if err != nil {
99 return nil, err
100 }
101
102 // run migrations
103
104 // NOTE: this won't migrate existing records
105 // they will be fetched again with tap instead
106 orm.RunMigration(conn, logger, "add-rkey-to-repos", func(tx *sql.Tx) error {
107 // archive legacy repos (just in case)
108 _, err = tx.Exec(`alter table repos rename to repos_old`)
109 if err != nil {
110 return err
111 }
112
113 _, err := tx.Exec(`
114 create table repos (
115 -- identifiers
116 id integer primary key autoincrement,
117 did text not null,
118 rkey text not null,
119 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo' || '/' || rkey) stored,
120
121 name text not null,
122 knot text not null,
123
124 addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
125 unique(did, rkey)
126 );
127 `)
128 if err != nil {
129 return err
130 }
131
132 return nil
133 })
134
135 return &DB{db}, nil
136}
137
138func (d *DB) IsKnownDid(did syntax.DID) (bool, error) {
139 // is spindle member / repo collaborator
140 var exists bool
141 err := d.QueryRow(
142 `select exists (
143 select 1 from repo_collaborators where subject = ?
144 union all
145 select 1 from spindle_members where did = ?
146 )`,
147 did,
148 did,
149 ).Scan(&exists)
150 return exists, err
151}