103 lines
2.5 KiB
Go
103 lines
2.5 KiB
Go
// Package database owns the SQLite connection, pragmas, and migrations,
|
|
// plus query helpers ("stores") for every table in the schema. It is the
|
|
// only package that talks to SQLite directly.
|
|
package database
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"io/fs"
|
|
"sort"
|
|
"strings"
|
|
|
|
_ "modernc.org/sqlite"
|
|
|
|
"git.noroutes.net/NoRoutes/vibetunes/migrations"
|
|
)
|
|
|
|
// Open opens (creating if needed) the SQLite database at path, applies
|
|
// pragmas suited for a long-running server (WAL, foreign keys, busy
|
|
// timeout), and runs any pending migrations.
|
|
func Open(path string) (*sql.DB, error) {
|
|
dsn := fmt.Sprintf("file:%s?_pragma=busy_timeout(5000)", path)
|
|
db, err := sql.Open("sqlite", dsn)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
// SQLite only tolerates a single writer; keep the pool modest so
|
|
// writers serialize instead of colliding on SQLITE_BUSY.
|
|
db.SetMaxOpenConns(8)
|
|
|
|
pragmas := []string{
|
|
"PRAGMA journal_mode=WAL",
|
|
"PRAGMA synchronous=NORMAL",
|
|
"PRAGMA foreign_keys=ON",
|
|
"PRAGMA busy_timeout=5000",
|
|
}
|
|
for _, p := range pragmas {
|
|
if _, err := db.Exec(p); err != nil {
|
|
db.Close()
|
|
return nil, fmt.Errorf("pragma %q: %w", p, err)
|
|
}
|
|
}
|
|
|
|
if err := Migrate(db); err != nil {
|
|
db.Close()
|
|
return nil, fmt.Errorf("migrate: %w", err)
|
|
}
|
|
return db, nil
|
|
}
|
|
|
|
// Migrate applies any pending migrations from the embedded migrations FS,
|
|
// tracked in a schema_migrations table. It is safe to call repeatedly.
|
|
func Migrate(db *sql.DB) error {
|
|
if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (
|
|
version TEXT PRIMARY KEY,
|
|
applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
)`); err != nil {
|
|
return err
|
|
}
|
|
|
|
entries, err := fs.ReadDir(migrations.FS, ".")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
var names []string
|
|
for _, e := range entries {
|
|
if !e.IsDir() && strings.HasSuffix(e.Name(), ".sql") {
|
|
names = append(names, e.Name())
|
|
}
|
|
}
|
|
sort.Strings(names)
|
|
|
|
for _, name := range names {
|
|
var applied int
|
|
if err := db.QueryRow(`SELECT COUNT(*) FROM schema_migrations WHERE version = ?`, name).Scan(&applied); err != nil {
|
|
return err
|
|
}
|
|
if applied > 0 {
|
|
continue
|
|
}
|
|
content, err := migrations.FS.ReadFile(name)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
tx, err := db.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.Exec(string(content)); err != nil {
|
|
tx.Rollback()
|
|
return fmt.Errorf("applying %s: %w", name, err)
|
|
}
|
|
if _, err := tx.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, name); err != nil {
|
|
tx.Rollback()
|
|
return err
|
|
}
|
|
if err := tx.Commit(); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|