julien.rbrt.fr
forked from
tangled.org/core
Monorepo for Tangled — https://tangled.org
1package db
2
3import (
4 "context"
5 "database/sql"
6 "log/slog"
7 "strings"
8
9 _ "github.com/mattn/go-sqlite3"
10 "tangled.org/core/log"
11)
12
13type DB struct {
14 db *sql.DB
15 logger *slog.Logger
16}
17
18func Setup(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 = conn.ExecContext(ctx, `
42 create table if not exists known_dids (
43 did text primary key
44 );
45
46 create table if not exists public_keys (
47 id integer primary key autoincrement,
48 did text not null,
49 key text not null,
50 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
51 unique(did, key),
52 foreign key (did) references known_dids(did) on delete cascade
53 );
54
55 create table if not exists _jetstream (
56 id integer primary key autoincrement,
57 last_time_us integer not null
58 );
59
60 create table if not exists events (
61 rkey text not null,
62 nsid text not null,
63 event text not null, -- json
64 created integer not null default (strftime('%s', 'now')),
65 primary key (rkey, nsid)
66 );
67
68 create table if not exists migrations (
69 id integer primary key autoincrement,
70 name text unique
71 );
72 `)
73 if err != nil {
74 return nil, err
75 }
76
77 return &DB{
78 db: db,
79 logger: logger,
80 }, nil
81}