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 write-ahead log into the main database file. Call it
49// before closing so a killed process doesn't 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 // Rebuild downloads so its foreign keys survive enforcement: deleting a
136 // preset or subscription now nulls the reference instead of failing, which
137 // matches how ExecuteDownload already degrades to the default preset.
138 // subscription_id gains the foreign key it never had.
139 {15, `CREATE TABLE downloads_new (
140 id INTEGER PRIMARY KEY AUTOINCREMENT,
141 url TEXT NOT NULL,
142 status TEXT DEFAULT 'queued',
143 logs TEXT,
144 error_message TEXT,
145 preset_id INTEGER,
146 format_override TEXT,
147 custom_flags TEXT,
148 output_dir TEXT,
149 subscription_id INTEGER,
150 started_at DATETIME,
151 completed_at DATETIME,
152 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
153 FOREIGN KEY (preset_id) REFERENCES presets(id) ON DELETE SET NULL,
154 FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
155 );
156 INSERT INTO downloads_new (id, url, status, logs, error_message, preset_id,
157 format_override, custom_flags, output_dir, subscription_id, started_at,
158 completed_at, created_at)
159 SELECT id, url, status, logs, error_message, preset_id, format_override,
160 custom_flags, output_dir, subscription_id, started_at, completed_at,
161 created_at FROM downloads;
162 DROP TABLE downloads;
163 ALTER TABLE downloads_new RENAME TO downloads;`},
164 {16, `CREATE TABLE subscriptions_new (
165 id INTEGER PRIMARY KEY AUTOINCREMENT,
166 name TEXT NOT NULL,
167 url TEXT NOT NULL,
168 enabled INTEGER DEFAULT 1,
169 refresh_mode TEXT DEFAULT 'overwrite',
170 schedule_kind TEXT DEFAULT 'daily',
171 cron_expr TEXT DEFAULT '0 3 * * *',
172 preset_id INTEGER,
173 format_override TEXT,
174 custom_flags TEXT,
175 output_dir TEXT NOT NULL,
176 prune_removed INTEGER DEFAULT 0,
177 last_run_at DATETIME,
178 next_run_at DATETIME,
179 last_status TEXT,
180 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
181 FOREIGN KEY (preset_id) REFERENCES presets(id) ON DELETE SET NULL
182 );
183 INSERT INTO subscriptions_new SELECT id, name, url, enabled, refresh_mode,
184 schedule_kind, cron_expr, preset_id, format_override, custom_flags,
185 output_dir, prune_removed, last_run_at, next_run_at, last_status,
186 created_at FROM subscriptions;
187 DROP TABLE subscriptions;
188 ALTER TABLE subscriptions_new RENAME TO subscriptions;`},
189 // Indexes for the hot predicates: the queue poll and claim filter on
190 // status, the scheduler's dedup check filters on subscription_id, and
191 // GetDue filters on enabled + next_run_at.
192 {17, `CREATE INDEX IF NOT EXISTS idx_downloads_status ON downloads(status);
193 CREATE INDEX IF NOT EXISTS idx_downloads_subscription_id ON downloads(subscription_id);
194 CREATE INDEX IF NOT EXISTS idx_downloads_created_at ON downloads(created_at);
195 CREATE INDEX IF NOT EXISTS idx_subscriptions_due ON subscriptions(enabled, next_run_at);`},
196 {18, `ALTER TABLE presets ADD COLUMN write_comments INTEGER DEFAULT 0;
197 ALTER TABLE presets ADD COLUMN comment_sort TEXT;
198 ALTER TABLE presets ADD COLUMN max_comments INTEGER DEFAULT 0;
199 ALTER TABLE presets ADD COLUMN comment_extractor_args TEXT;`},
200 }
201
202 for _, m := range migrations {
203 if m.version <= currentVersion {
204 continue
205 }
206 // Each migration runs in its own transaction so a multi-statement failure
207 // can't leave the schema half-applied with no version recorded.
208 if err := applyMigration(db, m.version, m.sql); err != nil {
209 return err
210 }
211 }
212 return nil
213}
214
215func applyMigration(db *sql.DB, version int, stmt string) error {
216 tx, err := db.Begin()
217 if err != nil {
218 return fmt.Errorf("migration %d: begin: %w", version, err)
219 }
220 defer tx.Rollback()
221
222 if _, err := tx.Exec(stmt); err != nil {
223 return fmt.Errorf("migration %d: %w", version, err)
224 }
225 if _, err := tx.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, version); err != nil {
226 return fmt.Errorf("record migration %d: %w", version, err)
227 }
228 if err := tx.Commit(); err != nil {
229 return fmt.Errorf("migration %d: commit: %w", version, err)
230 }
231 return nil
232}
233