108 lines
3.0 kB
1
package db
2
3
import (
4
"database/sql"
5
6
_ "github.com/mattn/go-sqlite3"
7
)
8
9
type DB struct {
10
db *sql.DB
11
}
12
13
func 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
rkey text not null,
64
followed_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
65
primary key (user_did, subject_did),
66
check (user_did <> subject_did)
67
);
68
create table if not exists issues (
69
id integer primary key autoincrement,
70
owner_did text not null,
71
repo_at text not null,
72
issue_id integer not null unique,
73
title text not null,
74
body text not null,
75
open integer not null default 1,
76
created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
77
issue_at text,
78
unique(repo_at, issue_id),
79
foreign key (repo_at) references repos(at_uri) on delete cascade
80
);
81
create table if not exists comments (
82
id integer primary key autoincrement,
83
owner_did text not null,
84
issue_id integer not null,
85
repo_at text not null,
86
comment_id integer not null,
87
comment_at text not null,
88
body text not null,
89
created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
90
unique(issue_id, comment_id),
91
foreign key (issue_id) references issues(issue_id) on delete cascade
92
);
93
create table if not exists _jetstream (
94
id integer primary key autoincrement,
95
last_time_us integer not null
96
);
97
98
create table if not exists repo_issue_seqs (
99
repo_at text primary key,
100
next_issue_id integer not null default 1
101
);
102
103
`)
104
if err != nil {
105
return nil, err
106
}
107
return &DB{db: db}, nil
108
}
109