Mini Video Library

package database

import (
	"database/sql"
	"fmt"
	_ "modernc.org/sqlite"
	"os"
	"path/filepath"
)

func Open(path string) (*sql.DB, error) {
	if err := os.MkdirAll(filepath.Dir(path), 0755); err != nil {
		return nil, err
	}
	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1)
	if _, err = db.Exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000; PRAGMA synchronous=NORMAL;`); err != nil {
		db.Close()
		return nil, err
	}
	if err = Migrate(db); err != nil {
		db.Close()
		return nil, err
	}
	return db, nil
}

func Migrate(db *sql.DB) error {
	_, err := db.Exec(schema)
	if err != nil {
		return fmt.Errorf("migrate: %w", err)
	}
	for _, statement := range []string{
		"ALTER TABLE videos ADD COLUMN thumbnail_json TEXT NOT NULL DEFAULT '[]'",
		"ALTER TABLE videos ADD COLUMN probe_error TEXT NOT NULL DEFAULT ''",
		"ALTER TABLE videos ADD COLUMN probe_failed_at INTEGER",
		"ALTER TABLE videos ADD COLUMN anidb_json TEXT NOT NULL DEFAULT '{}'",
		"ALTER TABLE videos ADD COLUMN parsed_series TEXT NOT NULL DEFAULT ''",
		"ALTER TABLE videos ADD COLUMN parsed_season INTEGER NOT NULL DEFAULT 0",
		"ALTER TABLE videos ADD COLUMN parsed_episode INTEGER NOT NULL DEFAULT 0",
		"ALTER TABLE videos ADD COLUMN release_group TEXT NOT NULL DEFAULT ''",
		"ALTER TABLE videos ADD COLUMN detected_quality TEXT NOT NULL DEFAULT ''",
	} {
		_, _ = db.Exec(statement)
	}
	return nil
}

const schema = `
CREATE TABLE IF NOT EXISTS videos (
 id INTEGER PRIMARY KEY, path TEXT NOT NULL UNIQUE, filename TEXT NOT NULL, title TEXT NOT NULL DEFAULT '',
 size INTEGER NOT NULL, hash TEXT NOT NULL DEFAULT '', modified_at INTEGER NOT NULL, duration REAL NOT NULL DEFAULT 0,
 video_codec TEXT NOT NULL DEFAULT '', width INTEGER NOT NULL DEFAULT 0, height INTEGER NOT NULL DEFAULT 0,
 fps REAL NOT NULL DEFAULT 0, bitrate INTEGER NOT NULL DEFAULT 0, container TEXT NOT NULL DEFAULT '', hdr INTEGER NOT NULL DEFAULT 0,
 audio_json TEXT NOT NULL DEFAULT '[]', subtitle_json TEXT NOT NULL DEFAULT '[]', chapters_json TEXT NOT NULL DEFAULT '[]',
 thumbnail TEXT NOT NULL DEFAULT '', poster TEXT NOT NULL DEFAULT '', added_at INTEGER NOT NULL,
 last_played INTEGER, playback_position REAL NOT NULL DEFAULT 0, watched INTEGER NOT NULL DEFAULT 0,
 favorite INTEGER NOT NULL DEFAULT 0, tags TEXT NOT NULL DEFAULT '', description TEXT NOT NULL DEFAULT '', external_ids TEXT NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS idx_videos_filename ON videos(filename);
CREATE INDEX IF NOT EXISTS idx_videos_added ON videos(added_at DESC);
CREATE INDEX IF NOT EXISTS idx_videos_played ON videos(last_played DESC);
CREATE INDEX IF NOT EXISTS idx_videos_codec ON videos(video_codec);
CREATE INDEX IF NOT EXISTS idx_videos_resolution ON videos(width,height);
CREATE TABLE IF NOT EXISTS scan_state (key TEXT PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS scan_errors (
 path TEXT PRIMARY KEY, error TEXT NOT NULL, failed_at INTEGER NOT NULL, size INTEGER NOT NULL DEFAULT 0, modified_at INTEGER NOT NULL DEFAULT 0, ignored INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_scan_errors_failed ON scan_errors(failed_at DESC);
CREATE TABLE IF NOT EXISTS plugin_settings (plugin TEXT NOT NULL, key TEXT NOT NULL, value TEXT NOT NULL, PRIMARY KEY(plugin,key));
CREATE TABLE IF NOT EXISTS plugin_cache (plugin TEXT NOT NULL, cache_key TEXT NOT NULL, payload TEXT NOT NULL, fetched_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, PRIMARY KEY(plugin,cache_key));
`