vidarchive

vidarchive/internal/database/database.go
database.go
⎇
Raw
1package database
2
3import (
4 "database/sql"
5 "fmt"
6 "os"
7 "path/filepath"
8
9 "vidarchive/internal/config"
10
11 _ "modernc.org/sqlite"
12)
13
14func New(cfg *config.Config) (*sql.DB, error) {
15 if err := os.MkdirAll(filepath.Dir(cfg.DBPath), 0o755); err != nil {
16 return nil, fmt.Errorf("create db dir: %w", err)
17 }
18
19 db, err := sql.Open("sqlite", cfg.DBPath)
20 if err != nil {
21 return nil, fmt.Errorf("open db: %w", err)
22 }
23
24 db.SetMaxOpenConns(1)
25 db.SetMaxIdleConns(1)
26
27 if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil {
28 return nil, fmt.Errorf("enable wal mode: %w", err)
29 }
30 if _, err := db.Exec("PRAGMA busy_timeout=5000"); err != nil {
31 return nil, fmt.Errorf("set busy timeout: %w", err)
32 }
33 // Migrations run with foreign keys off: the table rebuilds below drop and
34 // rename tables, which enforcement would reject mid-flight.
35 if err := migrate(db); err != nil {
36 return nil, fmt.Errorf("migrate: %w", err)
37 }
38
39 // SQLite ignores FOREIGN KEY clauses unless this is enabled per connection,
40 // so without it the declared constraints are decorative.
41 if _, err := db.Exec("PRAGMA foreign_keys=ON"); err != nil {
42 return nil, fmt.Errorf("enable foreign keys: %w", err)
43 }
44
45 return db, nil
46}
47
48// Checkpoint flushes the WAL into the main database file. Call it before closing
49// so a killed process does not leave a large uncheckpointed WAL.
50func Checkpoint(db *sql.DB) error {
51 if _, err := db.Exec("PRAGMA wal_checkpoint(TRUNCATE)"); err != nil {
52 return fmt.Errorf("wal checkpoint: %w", err)
53 }
54 return nil
55}
56
57func migrate(db *sql.DB) error {
58 if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
59 return fmt.Errorf("create migrations table: %w", err)
60 }
61
62 var currentVersion int
63 if err := db.QueryRow(`SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1`).Scan(&currentVersion); err != nil && err != sql.ErrNoRows {
64 return fmt.Errorf("get current version: %w", err)
65 }
66
67 migrations := []struct {
68 version int
69 sql string
70 }{
71 {1, `CREATE TABLE IF NOT EXISTS presets (
72 id INTEGER PRIMARY KEY AUTOINCREMENT,
73 name TEXT NOT NULL UNIQUE,
74 description TEXT,
75 is_default INTEGER DEFAULT 0,
76 format TEXT DEFAULT 'best',
77 quality TEXT,
78 extract_audio INTEGER DEFAULT 0,
79 audio_format TEXT DEFAULT 'mp3',
80 embed_subs INTEGER DEFAULT 0,
81 sub_langs TEXT DEFAULT 'en',
82 embed_thumbnail INTEGER DEFAULT 1,
83 embed_metadata INTEGER DEFAULT 1,
84 write_info_json INTEGER DEFAULT 1,
85 output_template TEXT DEFAULT '%(title)s.%(ext)s',
86 custom_flags TEXT,
87 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
88 )`},
89 {3, `CREATE TABLE IF NOT EXISTS downloads (
90 id INTEGER PRIMARY KEY AUTOINCREMENT,
91 url TEXT NOT NULL,
92 status TEXT DEFAULT 'queued',
93 logs TEXT,
94 error_message TEXT,
95 preset_id INTEGER,
96 format_override TEXT,
97 custom_flags TEXT,
98 started_at DATETIME,
99 completed_at DATETIME,
100 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
101 FOREIGN KEY (preset_id) REFERENCES presets(id)
102 )`},
103 {4, `CREATE TABLE IF NOT EXISTS settings (
104 key TEXT PRIMARY KEY,
105 value TEXT
106 )`},
107 {5, `INSERT OR IGNORE INTO settings (key, value) VALUES ('refresh_interval', '5')`},
108 {6, `INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_refresh_library', '0')`},
109 {7, `INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_refresh_downloads', '1')`},
110 {8, `ALTER TABLE presets ADD COLUMN format_mode TEXT DEFAULT 'preset'`},
111 {9, `ALTER TABLE presets ADD COLUMN custom_format TEXT`},
112 {10, `ALTER TABLE downloads ADD COLUMN output_dir TEXT`},
113 {11, `DROP TABLE IF EXISTS media`},
114 {12, `ALTER TABLE presets DROP COLUMN output_template`},
115 {13, `CREATE TABLE IF NOT EXISTS subscriptions (
116 id INTEGER PRIMARY KEY AUTOINCREMENT,
117 name TEXT NOT NULL,
118 url TEXT NOT NULL,
119 enabled INTEGER DEFAULT 1,
120 refresh_mode TEXT DEFAULT 'overwrite',
121 schedule_kind TEXT DEFAULT 'daily',
122 cron_expr TEXT DEFAULT '0 3 * * *',
123 preset_id INTEGER,
124 format_override TEXT,
125 custom_flags TEXT,
126 output_dir TEXT NOT NULL,
127 prune_removed INTEGER DEFAULT 0,
128 last_run_at DATETIME,
129 next_run_at DATETIME,
130 last_status TEXT,
131 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
132 FOREIGN KEY (preset_id) REFERENCES presets(id)
133 )`},
134 {14, `ALTER TABLE downloads ADD COLUMN subscription_id INTEGER`},
135 // Rebuilt so the foreign keys survive enforcement: deleting a preset or
136 // subscription nulls the reference instead of failing, matching how
137 // ExecuteDownload degrades to the default preset.
138 {15, `CREATE TABLE downloads_new (
139 id INTEGER PRIMARY KEY AUTOINCREMENT,
140 url TEXT NOT NULL,
141 status TEXT DEFAULT 'queued',
142 logs TEXT,
143 error_message TEXT,
144 preset_id INTEGER,
145 format_override TEXT,
146 custom_flags TEXT,
147 output_dir TEXT,
148 subscription_id INTEGER,
149 started_at DATETIME,
150 completed_at DATETIME,
151 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
152 FOREIGN KEY (preset_id) REFERENCES presets(id) ON DELETE SET NULL,
153 FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
154 );
155 INSERT INTO downloads_new (id, url, status, logs, error_message, preset_id,
156 format_override, custom_flags, output_dir, subscription_id, started_at,
157 completed_at, created_at)
158 SELECT id, url, status, logs, error_message, preset_id, format_override,
159 custom_flags, output_dir, subscription_id, started_at, completed_at,
160 created_at FROM downloads;
161 DROP TABLE downloads;
162 ALTER TABLE downloads_new RENAME TO downloads;`},
163 {16, `CREATE TABLE subscriptions_new (
164 id INTEGER PRIMARY KEY AUTOINCREMENT,
165 name TEXT NOT NULL,
166 url TEXT NOT NULL,
167 enabled INTEGER DEFAULT 1,
168 refresh_mode TEXT DEFAULT 'overwrite',
169 schedule_kind TEXT DEFAULT 'daily',
170 cron_expr TEXT DEFAULT '0 3 * * *',
171 preset_id INTEGER,
172 format_override TEXT,
173 custom_flags TEXT,
174 output_dir TEXT NOT NULL,
175 prune_removed INTEGER DEFAULT 0,
176 last_run_at DATETIME,
177 next_run_at DATETIME,
178 last_status TEXT,
179 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
180 FOREIGN KEY (preset_id) REFERENCES presets(id) ON DELETE SET NULL
181 );
182 INSERT INTO subscriptions_new SELECT id, name, url, enabled, refresh_mode,
183 schedule_kind, cron_expr, preset_id, format_override, custom_flags,
184 output_dir, prune_removed, last_run_at, next_run_at, last_status,
185 created_at FROM subscriptions;
186 DROP TABLE subscriptions;
187 ALTER TABLE subscriptions_new RENAME TO subscriptions;`},
188 // The hot predicates: queue poll and claim on status, the scheduler's dedup
189 // check on subscription_id, GetDue on enabled + next_run_at.
190 {17, `CREATE INDEX IF NOT EXISTS idx_downloads_status ON downloads(status);
191 CREATE INDEX IF NOT EXISTS idx_downloads_subscription_id ON downloads(subscription_id);
192 CREATE INDEX IF NOT EXISTS idx_downloads_created_at ON downloads(created_at);
193 CREATE INDEX IF NOT EXISTS idx_subscriptions_due ON subscriptions(enabled, next_run_at);`},
194 {18, `ALTER TABLE presets ADD COLUMN write_comments INTEGER DEFAULT 0;
195 ALTER TABLE presets ADD COLUMN comment_sort TEXT;
196 ALTER TABLE presets ADD COLUMN max_comments INTEGER DEFAULT 0;
197 ALTER TABLE presets ADD COLUMN comment_extractor_args TEXT;`},
198 }
199
200 for _, m := range migrations {
201 if m.version <= currentVersion {
202 continue
203 }
204 // Each migration runs in its own transaction so a multi-statement failure
205 // can't leave the schema half-applied with no version recorded.
206 if err := applyMigration(db, m.version, m.sql); err != nil {
207 return err
208 }
209 }
210 return nil
211}
212
213func applyMigration(db *sql.DB, version int, stmt string) error {
214 tx, err := db.Begin()
215 if err != nil {
216 return fmt.Errorf("migration %d: begin: %w", version, err)
217 }
218 defer tx.Rollback()
219
220 if _, err := tx.Exec(stmt); err != nil {
221 return fmt.Errorf("migration %d: %w", version, err)
222 }
223 if _, err := tx.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, version); err != nil {
224 return fmt.Errorf("record migration %d: %w", version, err)
225 }
226 if err := tx.Commit(); err != nil {
227 return fmt.Errorf("migration %d: commit: %w", version, err)
228 }
229 return nil
230}
231