kot.pink
1package db
2
3import (
4 "database/sql"
5
6 _ "github.com/mattn/go-sqlite3"
7)
8
9type DB struct {
10 db *sql.DB
11}
12
13func Make(dbPath string) (*DB, error) {
14 db, err := sql.Open("sqlite3", dbPath)
15 if err != nil {
16 return nil, err
17 }
18 _, err = db.Exec(`
19 pragma journal_mode = WAL;
20 pragma synchronous = normal;
21 pragma foreign_keys = on;
22 pragma temp_store = memory;
23 pragma mmap_size = 30000000000;
24 pragma page_size = 32768;
25 pragma auto_vacuum = incremental;
26 pragma busy_timeout = 5000;
27
28 create table if not exists registrations (
29 id integer primary key autoincrement,
30 domain text not null unique,
31 did text not null,
32 secret text not null,
33 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
34 registered text
35 );
36 create table if not exists public_keys (
37 id integer primary key autoincrement,
38 did text not null,
39 name text not null,
40 key text not null,
41 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
42 unique(did, name, key)
43 );
44 create table if not exists repos (
45 id integer primary key autoincrement,
46 did text not null,
47 name text not null,
48 knot text not null,
49 rkey text not null,
50 at_uri text not null unique,
51 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
52 unique(did, name, knot, rkey)
53 );
54 create table if not exists collaborators (
55 id integer primary key autoincrement,
56 did text not null,
57 repo integer not null,
58 foreign key (repo) references repos(id) on delete cascade
59 );
60 create table if not exists follows (
61 user_did text not null,
62 subject_did text not null,
63 at_uri text not null unique,
64 rkey text not null,
65 followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
66 primary key (user_did, subject_did),
67 check (user_did <> subject_did)
68 );
69 create table if not exists issues (
70 id integer primary key autoincrement,
71 owner_did text not null,
72 repo_at text not null,
73 issue_id integer not null unique,
74 title text not null,
75 body text not null,
76 open integer not null default 1,
77 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
78 issue_at text,
79 unique(repo_at, issue_id),
80 foreign key (repo_at) references repos(at_uri) on delete cascade
81 );
82 create table if not exists comments (
83 id integer primary key autoincrement,
84 owner_did text not null,
85 issue_id integer not null,
86 repo_at text not null,
87 comment_id integer not null,
88 comment_at text not null,
89 body text not null,
90 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
91 unique(issue_id, comment_id),
92 foreign key (issue_id) references issues(issue_id) on delete cascade
93 );
94 create table if not exists _jetstream (
95 id integer primary key autoincrement,
96 last_time_us integer not null
97 );
98
99 create table if not exists repo_issue_seqs (
100 repo_at text primary key,
101 next_issue_id integer not null default 1
102 );
103
104 `)
105 if err != nil {
106 return nil, err
107 }
108 return &DB{db: db}, nil
109}