133728e barerepo 1mo
1
// Package store holds chapter 10's closed list and nothing else, on SQLite or PostgreSQL.
133728e barerepo 1mo
2
package store
133728e barerepo 1mo
4
import (
133728e barerepo 1mo
5
"context"
133728e barerepo 1mo
6
"database/sql"
133728e barerepo 1mo
7
"fmt"
133728e barerepo 1mo
8
"os"
133728e barerepo 1mo
9
"path/filepath"
133728e barerepo 1mo
10
"strconv"
133728e barerepo 1mo
11
"strings"
133728e barerepo 1mo
13
_ "github.com/jackc/pgx/v5/stdlib"
133728e barerepo 1mo
14
_ "modernc.org/sqlite"
133728e barerepo 1mo
16
"github.com/barerepo/server/internal/config"
133728e barerepo 1mo
19
type DB struct {
133728e barerepo 1mo
20
*sql.DB
133728e barerepo 1mo
21
Kind config.Kind
133728e barerepo 1mo
24
// Open connects by URL and applies every migration, forward only. Chapter 41.8.
133728e barerepo 1mo
25
func Open(ctx context.Context, dbURL string) (*DB, error) {
133728e barerepo 1mo
26
kind, err := config.Config{Database: config.Database{URL: dbURL}}.DatabaseKind()
133728e barerepo 1mo
27
if err != nil {
133728e barerepo 1mo
28
return nil, err
133728e barerepo 1mo
30
driver, dsn, err := dataSource(kind, dbURL)
133728e barerepo 1mo
31
if err != nil {
133728e barerepo 1mo
32
return nil, err
133728e barerepo 1mo
34
sqldb, err := sql.Open(driver, dsn)
133728e barerepo 1mo
35
if err != nil {
133728e barerepo 1mo
36
return nil, err
133728e barerepo 1mo
38
if kind == config.SQLite {
133728e barerepo 1mo
39
// One writer, because SQLite serialises anyway and a pool only makes that SQLITE_BUSY.
133728e barerepo 1mo
40
sqldb.SetMaxOpenConns(1)
133728e barerepo 1mo
41
} else {
133728e barerepo 1mo
42
sqldb.SetMaxOpenConns(16)
133728e barerepo 1mo
43
sqldb.SetMaxIdleConns(4)
133728e barerepo 1mo
45
if err := sqldb.PingContext(ctx); err != nil {
133728e barerepo 1mo
46
sqldb.Close()
133728e barerepo 1mo
47
return nil, fmt.Errorf("%s: %w", dbURL, err)
133728e barerepo 1mo
49
db := &DB{DB: sqldb, Kind: kind}
133728e barerepo 1mo
50
if err := db.migrate(ctx); err != nil {
133728e barerepo 1mo
51
sqldb.Close()
133728e barerepo 1mo
52
return nil, err
133728e barerepo 1mo
54
return db, nil
133728e barerepo 1mo
57
// dataSource turns one config URL into a driver name and a DSN.
133728e barerepo 1mo
58
func dataSource(kind config.Kind, dbURL string) (driver, dsn string, err error) {
133728e barerepo 1mo
59
switch kind {
133728e barerepo 1mo
60
case config.SQLite:
133728e barerepo 1mo
61
path := strings.TrimPrefix(dbURL, "sqlite:")
133728e barerepo 1mo
62
path = strings.TrimPrefix(path, "//")
133728e barerepo 1mo
63
if path == "" {
133728e barerepo 1mo
64
return "", "", fmt.Errorf("database.url names no sqlite file")
133728e barerepo 1mo
66
if dir := filepath.Dir(path); dir != "." {
133728e barerepo 1mo
67
if err := os.MkdirAll(dir, 0o750); err != nil {
133728e barerepo 1mo
68
return "", "", err
133728e barerepo 1mo
71
// WAL so a reader never blocks the writer, and foreign keys because SQLite ignores them.
133728e barerepo 1mo
72
return "sqlite", "file:" + path +
133728e barerepo 1mo
73
"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)" +
133728e barerepo 1mo
74
"&_pragma=foreign_keys(1)&_pragma=synchronous(NORMAL)", nil
133728e barerepo 1mo
75
case config.Postgres:
133728e barerepo 1mo
76
return "pgx", dbURL, nil
133728e barerepo 1mo
78
return "", "", fmt.Errorf("unsupported database %q", kind)
133728e barerepo 1mo
81
// rebind turns ? into $1 for PostgreSQL, which is the only dialect difference above the DDL.
133728e barerepo 1mo
82
func (db *DB) rebind(q string) string {
133728e barerepo 1mo
83
if db.Kind != config.Postgres {
133728e barerepo 1mo
84
return q
133728e barerepo 1mo
86
var b strings.Builder
133728e barerepo 1mo
87
b.Grow(len(q) + 8)
133728e barerepo 1mo
88
n := 0
133728e barerepo 1mo
89
for i := 0; i < len(q); i++ {
133728e barerepo 1mo
90
if q[i] != '?' {
133728e barerepo 1mo
91
b.WriteByte(q[i])
133728e barerepo 1mo
92
continue
133728e barerepo 1mo
94
n++
133728e barerepo 1mo
95
b.WriteByte('$')
133728e barerepo 1mo
96
b.WriteString(strconv.Itoa(n))
133728e barerepo 1mo
98
return b.String()
133728e barerepo 1mo
101
func (db *DB) ExecContext(ctx context.Context, q string, args ...any) (sql.Result, error) {
133728e barerepo 1mo
102
return db.DB.ExecContext(ctx, db.rebind(q), args...)
133728e barerepo 1mo
105
func (db *DB) QueryContext(ctx context.Context, q string, args ...any) (*sql.Rows, error) {
133728e barerepo 1mo
106
return db.DB.QueryContext(ctx, db.rebind(q), args...)
133728e barerepo 1mo
109
func (db *DB) QueryRowContext(ctx context.Context, q string, args ...any) *sql.Row {
133728e barerepo 1mo
110
return db.DB.QueryRowContext(ctx, db.rebind(q), args...)
133728e barerepo 1mo
113
// Tx is a transaction that rebinds placeholders the same way DB does.
133728e barerepo 1mo
114
type Tx struct {
133728e barerepo 1mo
115
*sql.Tx
133728e barerepo 1mo
116
db *DB
133728e barerepo 1mo
119
func (db *DB) Begin(ctx context.Context) (*Tx, error) {
133728e barerepo 1mo
120
tx, err := db.DB.BeginTx(ctx, nil)
133728e barerepo 1mo
121
if err != nil {
133728e barerepo 1mo
122
return nil, err
133728e barerepo 1mo
123
}
133728e barerepo 1mo
124
return &Tx{Tx: tx, db: db}, nil
133728e barerepo 1mo
127
func (tx *Tx) ExecContext(ctx context.Context, q string, args ...any) (sql.Result, error) {
133728e barerepo 1mo
128
return tx.Tx.ExecContext(ctx, tx.db.rebind(q), args...)
133728e barerepo 1mo
131
func (tx *Tx) QueryRowContext(ctx context.Context, q string, args ...any) *sql.Row {
133728e barerepo 1mo
132
return tx.Tx.QueryRowContext(ctx, tx.db.rebind(q), args...)
133728e barerepo 1mo
135
// migrations are append-only in two dialects, and TestMigrationsAgree checks they agree.
133728e barerepo 1mo
136
type migration struct{ sqlite, postgres string }
133728e barerepo 1mo
138
var migrations = []migration{{
133728e barerepo 1mo
139
sqlite: `
133728e barerepo 1mo
140
CREATE TABLE accounts (
133728e barerepo 1mo
141
name TEXT PRIMARY KEY,
133728e barerepo 1mo
142
admin INTEGER NOT NULL DEFAULT 0,
133728e barerepo 1mo
143
created_at INTEGER NOT NULL
133728e barerepo 1mo
144
);
133728e barerepo 1mo
145
CREATE TABLE pubkeys (
133728e barerepo 1mo
146
id INTEGER PRIMARY KEY,
133728e barerepo 1mo
147
account TEXT NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
133728e barerepo 1mo
148
fingerprint TEXT NOT NULL UNIQUE,
133728e barerepo 1mo
149
algo TEXT NOT NULL,
133728e barerepo 1mo
150
blob TEXT NOT NULL,
133728e barerepo 1mo
151
comment TEXT NOT NULL DEFAULT '',
133728e barerepo 1mo
152
created_at INTEGER NOT NULL,
133728e barerepo 1mo
153
last_used INTEGER
133728e barerepo 1mo
154
);
133728e barerepo 1mo
155
CREATE INDEX pubkeys_account ON pubkeys(account);
133728e barerepo 1mo
156
CREATE TABLE repos (
133728e barerepo 1mo
157
owner TEXT NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
133728e barerepo 1mo
158
name TEXT NOT NULL,
133728e barerepo 1mo
159
created_at INTEGER NOT NULL,
133728e barerepo 1mo
160
PRIMARY KEY (owner, name)
133728e barerepo 1mo
161
);
133728e barerepo 1mo
162
CREATE TABLE tokens (
133728e barerepo 1mo
163
id INTEGER PRIMARY KEY,
133728e barerepo 1mo
164
kind TEXT NOT NULL,
133728e barerepo 1mo
165
hash TEXT NOT NULL UNIQUE,
133728e barerepo 1mo
166
account TEXT NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
133728e barerepo 1mo
167
scope TEXT NOT NULL DEFAULT '',
133728e barerepo 1mo
168
label TEXT NOT NULL DEFAULT '',
133728e barerepo 1mo
169
created_at INTEGER NOT NULL,
133728e barerepo 1mo
170
expires_at INTEGER,
133728e barerepo 1mo
171
last_used INTEGER
133728e barerepo 1mo
172
);
133728e barerepo 1mo
173
CREATE INDEX tokens_account ON tokens(account, kind);
133728e barerepo 1mo
174
CREATE TABLE redirects (
133728e barerepo 1mo
175
old_owner TEXT NOT NULL,
133728e barerepo 1mo
176
old_name TEXT NOT NULL,
133728e barerepo 1mo
177
new_owner TEXT NOT NULL,
133728e barerepo 1mo
178
new_name TEXT NOT NULL,
133728e barerepo 1mo
179
created_at INTEGER NOT NULL,
133728e barerepo 1mo
180
PRIMARY KEY (old_owner, old_name)
133728e barerepo 1mo
181
);`,
133728e barerepo 1mo
182
postgres: `
133728e barerepo 1mo
183
CREATE TABLE accounts (
133728e barerepo 1mo
184
name TEXT PRIMARY KEY,
133728e barerepo 1mo
185
admin SMALLINT NOT NULL DEFAULT 0,
133728e barerepo 1mo
186
created_at BIGINT NOT NULL
133728e barerepo 1mo
187
);
133728e barerepo 1mo
188
CREATE TABLE pubkeys (
133728e barerepo 1mo
189
id BIGSERIAL PRIMARY KEY,
133728e barerepo 1mo
190
account TEXT NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
133728e barerepo 1mo
191
fingerprint TEXT NOT NULL UNIQUE,
133728e barerepo 1mo
192
algo TEXT NOT NULL,
133728e barerepo 1mo
193
blob TEXT NOT NULL,
133728e barerepo 1mo
194
comment TEXT NOT NULL DEFAULT '',
133728e barerepo 1mo
195
created_at BIGINT NOT NULL,
133728e barerepo 1mo
196
last_used BIGINT
133728e barerepo 1mo
197
);
133728e barerepo 1mo
198
CREATE INDEX pubkeys_account ON pubkeys(account);
133728e barerepo 1mo
199
CREATE TABLE repos (
133728e barerepo 1mo
200
owner TEXT NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
133728e barerepo 1mo
201
name TEXT NOT NULL,
133728e barerepo 1mo
202
created_at BIGINT NOT NULL,
133728e barerepo 1mo
203
PRIMARY KEY (owner, name)
133728e barerepo 1mo
204
);
133728e barerepo 1mo
205
CREATE TABLE tokens (
133728e barerepo 1mo
206
id BIGSERIAL PRIMARY KEY,
133728e barerepo 1mo
207
kind TEXT NOT NULL,
133728e barerepo 1mo
208
hash TEXT NOT NULL UNIQUE,
133728e barerepo 1mo
209
account TEXT NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
133728e barerepo 1mo
210
scope TEXT NOT NULL DEFAULT '',
133728e barerepo 1mo
211
label TEXT NOT NULL DEFAULT '',
133728e barerepo 1mo
212
created_at BIGINT NOT NULL,
133728e barerepo 1mo
213
expires_at BIGINT,
133728e barerepo 1mo
214
last_used BIGINT
133728e barerepo 1mo
215
);
133728e barerepo 1mo
216
CREATE INDEX tokens_account ON tokens(account, kind);
133728e barerepo 1mo
217
CREATE TABLE redirects (
133728e barerepo 1mo
218
old_owner TEXT NOT NULL,
133728e barerepo 1mo
219
old_name TEXT NOT NULL,
133728e barerepo 1mo
220
new_owner TEXT NOT NULL,