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 {2, `CREATE TABLE IF NOT EXISTS media (
74 id INTEGER PRIMARY KEY AUTOINCREMENT,
75 url TEXT NOT NULL,
76 title TEXT,
77 description TEXT,
78 duration INTEGER,
79 filepath TEXT NOT NULL,
80 relative_path TEXT NOT NULL,
81 is_audio INTEGER DEFAULT 0,
82 has_embedded_thumbnail INTEGER DEFAULT 0,
83 info_json_path TEXT,
84 playlist_id TEXT,
85 playlist_index INTEGER,
86 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
87 )`},
88 {3, `CREATE TABLE IF NOT EXISTS downloads (
89 id INTEGER PRIMARY KEY AUTOINCREMENT,
90 url TEXT NOT NULL,
91 status TEXT DEFAULT 'queued',
92 progress_percent INTEGER DEFAULT 0,
93 current_speed TEXT,
94 eta TEXT,
95 logs TEXT,
96 error_message TEXT,
97 preset_id INTEGER,
98 format_override TEXT,
99 custom_flags TEXT,
100 started_at DATETIME,
101 completed_at DATETIME,
102 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
103 FOREIGN KEY (preset_id) REFERENCES presets(id)
104 )`},
105 {4, `CREATE TABLE IF NOT EXISTS settings (
106 key TEXT PRIMARY KEY,
107 value TEXT
108 )`},
109 {5, `INSERT OR IGNORE INTO settings (key, value) VALUES ('refresh_interval', '5')`},
110 {6, `INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_refresh_library', '0')`},
111 {7, `INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_refresh_downloads', '1')`},
112 {8, `ALTER TABLE presets ADD COLUMN format_mode TEXT DEFAULT 'preset'`},
113 {9, `ALTER TABLE presets ADD COLUMN custom_format TEXT`},
114 {10, `ALTER TABLE downloads ADD COLUMN output_dir TEXT`},
115 {11, `ALTER TABLE downloads DROP COLUMN progress_percent`},
116 {12, `ALTER TABLE downloads DROP COLUMN current_speed`},
117 {13, `ALTER TABLE downloads DROP COLUMN eta`},
118 {14, `INSERT OR IGNORE INTO settings (key, value) VALUES ('cookies', '')`},
119 }
120
121 for _, m := range migrations {
122 if m.version <= currentVersion {
123 continue
124 }
125 if _, err := db.Exec(m.sql); err != nil {
126 return fmt.Errorf("migration %d: %w", m.version, err)
127 }
128 if _, err := db.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, m.version); err != nil {
129 return fmt.Errorf("record migration %d: %w", m.version, err)
130 }
131 }
132 return nil
133}
134