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), 0755); 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
34 if err := migrate(db); err != nil {
35 return nil, fmt.Errorf("migrate: %w", err)
36 }
37
38 return db, nil
39}
40
41func migrate(db *sql.DB) error {
42 if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
43 return fmt.Errorf("create migrations table: %w", err)
44 }
45
46 var currentVersion int
47 if err := db.QueryRow(`SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1`).Scan(&currentVersion); err != nil && err != sql.ErrNoRows {
48 return fmt.Errorf("get current version: %w", err)
49 }
50
51 migrations := []struct {
52 version int
53 sql string
54 }{
55 {1, `CREATE TABLE IF NOT EXISTS presets (
56 id INTEGER PRIMARY KEY AUTOINCREMENT,
57 name TEXT NOT NULL UNIQUE,
58 description TEXT,
59 is_default INTEGER DEFAULT 0,
60 format TEXT DEFAULT 'best',
61 quality TEXT,
62 extract_audio INTEGER DEFAULT 0,
63 audio_format TEXT DEFAULT 'mp3',
64 embed_subs INTEGER DEFAULT 0,
65 sub_langs TEXT DEFAULT 'en',
66 embed_thumbnail INTEGER DEFAULT 1,
67 embed_metadata INTEGER DEFAULT 1,
68 write_info_json INTEGER DEFAULT 1,
69 output_template TEXT DEFAULT '%(title)s.%(ext)s',
70 custom_flags TEXT,
71 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
72 )`},
73 {3, `CREATE TABLE IF NOT EXISTS downloads (
74 id INTEGER PRIMARY KEY AUTOINCREMENT,
75 url TEXT NOT NULL,
76 status TEXT DEFAULT 'queued',
77 logs TEXT,
78 error_message TEXT,
79 preset_id INTEGER,
80 format_override TEXT,
81 custom_flags TEXT,
82 started_at DATETIME,
83 completed_at DATETIME,
84 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
85 FOREIGN KEY (preset_id) REFERENCES presets(id)
86 )`},
87 {4, `CREATE TABLE IF NOT EXISTS settings (
88 key TEXT PRIMARY KEY,
89 value TEXT
90 )`},
91 {5, `INSERT OR IGNORE INTO settings (key, value) VALUES ('refresh_interval', '5')`},
92 {6, `INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_refresh_library', '0')`},
93 {7, `INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_refresh_downloads', '1')`},
94 {8, `ALTER TABLE presets ADD COLUMN format_mode TEXT DEFAULT 'preset'`},
95 {9, `ALTER TABLE presets ADD COLUMN custom_format TEXT`},
96 {10, `ALTER TABLE downloads ADD COLUMN output_dir TEXT`},
97 {11, `DROP TABLE IF EXISTS media`},
98 {12, `ALTER TABLE presets DROP COLUMN output_template`},
99 }
100
101 for _, m := range migrations {
102 if m.version <= currentVersion {
103 continue
104 }
105 if _, err := db.Exec(m.sql); err != nil {
106 return fmt.Errorf("migration %d: %w", m.version, err)
107 }
108 if _, err := db.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, m.version); err != nil {
109 return fmt.Errorf("record migration %d: %w", m.version, err)
110 }
111 }
112 return nil
113}
114