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