From 914092f4d519aa7107439d48b882d149ddff1118 Mon Sep 17 00:00:00 2001 From: Paul Buetow Date: Sat, 9 May 2026 20:33:31 +0300 Subject: t1: remove repository migration shim --- internal/repository/migrate.go | 231 ------------------------------------- internal/repository/schema.go | 211 +++++++++++++++++++++++++++++++++ internal/repository/sqlite.go | 6 +- internal/repository/sqlite_test.go | 70 ++++++++++- 4 files changed, 282 insertions(+), 236 deletions(-) delete mode 100644 internal/repository/migrate.go create mode 100644 internal/repository/schema.go diff --git a/internal/repository/migrate.go b/internal/repository/migrate.go deleted file mode 100644 index 3c4702f..0000000 --- a/internal/repository/migrate.go +++ /dev/null @@ -1,231 +0,0 @@ -package repository - -import ( - "database/sql" - "fmt" -) - -// tablesSchema defines all CREATE TABLE statements. -const tablesSchema = ` -CREATE TABLE IF NOT EXISTS users ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - username TEXT UNIQUE NOT NULL, - password_hash TEXT NOT NULL, - is_admin INTEGER NOT NULL DEFAULT 0, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP -); - -CREATE TABLE IF NOT EXISTS sets ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - name TEXT NOT NULL, - root_path TEXT UNIQUE NOT NULL, - cover_thumbnail_path TEXT, - is_podcast INTEGER NOT NULL DEFAULT 0, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP -); - -CREATE TABLE IF NOT EXISTS set_permissions ( - set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE, - user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, - role TEXT CHECK(role IN ('owner','viewer')) NOT NULL DEFAULT 'viewer', - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (set_id, user_id) -); - -CREATE TABLE IF NOT EXISTS media ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE, - rel_path TEXT NOT NULL, - file_name TEXT NOT NULL, - abs_path TEXT NOT NULL, - type TEXT CHECK(type IN ('video','audio','image')) NOT NULL, - duration REAL, - codec TEXT, - resolution TEXT, - bitrate INTEGER, - file_size_bytes INTEGER, - width INTEGER, - height INTEGER, - exif_camera TEXT, - exif_lens TEXT, - exif_date TEXT, - exif_iso TEXT, - exif_f_number TEXT, - exif_exposure TEXT, - exif_focal_length TEXT, - thumbnail_path TEXT, - play_count INTEGER NOT NULL DEFAULT 0, - deleted_at DATETIME, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(set_id, rel_path) -); - -CREATE TABLE IF NOT EXISTS tags ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - name TEXT UNIQUE NOT NULL -); - -CREATE TABLE IF NOT EXISTS media_tags ( - media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, - tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE, - PRIMARY KEY (media_id, tag_id) -); - -CREATE TABLE IF NOT EXISTS favorites ( - user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, - media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (user_id, media_id) -); - -CREATE TABLE IF NOT EXISTS playback_progress ( - user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, - media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, - position_seconds REAL NOT NULL, - updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (user_id, media_id) -); - -CREATE TABLE IF NOT EXISTS sessions ( - id TEXT PRIMARY KEY, - user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, - expires_at DATETIME NOT NULL, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP -); - -CREATE TABLE IF NOT EXISTS playback_accumulator ( - session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE, - media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, - last_position REAL NOT NULL DEFAULT 0, - accumulated_seconds REAL NOT NULL DEFAULT 0, - counted INTEGER NOT NULL DEFAULT 0, - updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (session_id, media_id) -); - -CREATE TABLE IF NOT EXISTS shares ( - token TEXT PRIMARY KEY, - media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, - created_by INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - expires_at DATETIME NOT NULL, - max_uses INTEGER, - used_count INTEGER NOT NULL DEFAULT 0 -); - -CREATE TABLE IF NOT EXISTS media_notes ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, - user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, - content TEXT NOT NULL DEFAULT '', - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(media_id, user_id) -); - -CREATE TABLE IF NOT EXISTS podcast_feeds ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE, - feed_url TEXT NOT NULL, - title TEXT, - description TEXT, - image_url TEXT, - last_checked_at DATETIME, - last_etag TEXT, - check_interval_minutes INTEGER NOT NULL DEFAULT 60, - auto_download INTEGER NOT NULL DEFAULT 0, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP -); - -CREATE TABLE IF NOT EXISTS podcast_episodes ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - feed_id INTEGER NOT NULL REFERENCES podcast_feeds(id) ON DELETE CASCADE, - media_id INTEGER UNIQUE REFERENCES media(id) ON DELETE SET NULL, - guid TEXT NOT NULL, - title TEXT, - description TEXT, - published_at DATETIME, - episode_url TEXT NOT NULL, - duration_seconds REAL, - file_size INTEGER, - file_name TEXT, - is_downloaded INTEGER NOT NULL DEFAULT 0, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - UNIQUE(feed_id, guid) -); - -CREATE TABLE IF NOT EXISTS podcast_status ( - user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, - episode_id INTEGER NOT NULL REFERENCES podcast_episodes(id) ON DELETE CASCADE, - is_completed INTEGER NOT NULL DEFAULT 0, - position_seconds REAL NOT NULL DEFAULT 0, - updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (user_id, episode_id) -); -` - -// indexesSchema defines all CREATE INDEX statements. -const indexesSchema = ` -CREATE INDEX IF NOT EXISTS idx_media_set_id ON media(set_id); -CREATE INDEX IF NOT EXISTS idx_media_rel_path ON media(set_id, rel_path); -CREATE INDEX IF NOT EXISTS idx_media_deleted_at ON media(deleted_at); -CREATE INDEX IF NOT EXISTS idx_media_type ON media(type); -CREATE INDEX IF NOT EXISTS idx_media_filename ON media(file_name); -CREATE INDEX IF NOT EXISTS idx_permissions_user ON set_permissions(user_id); -CREATE INDEX IF NOT EXISTS idx_permissions_set ON set_permissions(set_id); -CREATE INDEX IF NOT EXISTS idx_shares_expires ON shares(expires_at); -CREATE INDEX IF NOT EXISTS idx_podcast_episodes_feed ON podcast_episodes(feed_id); -CREATE INDEX IF NOT EXISTS idx_podcast_episodes_media ON podcast_episodes(media_id); -CREATE INDEX IF NOT EXISTS idx_podcast_status_episode ON podcast_status(episode_id); -CREATE INDEX IF NOT EXISTS idx_sets_is_podcast ON sets(is_podcast); -` - -// execSchema executes a raw SQL schema block against the given database. -func execSchema(db *sql.DB, name, schema string) error { - if _, err := db.Exec(schema); err != nil { - return fmt.Errorf("execute %s schema: %w", name, err) - } - return nil -} - -// enableForeignKeys turns on SQLite foreign key enforcement. -func enableForeignKeys(db *sql.DB) error { - if _, err := db.Exec(`PRAGMA foreign_keys = ON;`); err != nil { - return fmt.Errorf("enable foreign keys: %w", err) - } - return nil -} - -// addPodcastColumn ensures the sets table has the is_podcast column. -// It safely ignores the error if the column already exists. -func addPodcastColumn(db *sql.DB) error { - _, err := db.Exec(`ALTER TABLE sets ADD COLUMN is_podcast INTEGER NOT NULL DEFAULT 0;`) - if err != nil { - // SQLite returns a generic error message for duplicate columns. - if err.Error() == "duplicate column name: is_podcast" || - err.Error() == "SQL logic error: duplicate column name: is_podcast (1)" { - return nil - } - return fmt.Errorf("add is_podcast column: %w", err) - } - return nil -} - -// Migrate creates the database schema if it does not exist. -func Migrate(db *sql.DB) error { - if err := enableForeignKeys(db); err != nil { - return err - } - if err := execSchema(db, "tables", tablesSchema); err != nil { - return err - } - // Backward-compatibility: older databases may have a sets table - // without the is_podcast column. Add it before creating the index. - if err := addPodcastColumn(db); err != nil { - return err - } - if err := execSchema(db, "indexes", indexesSchema); err != nil { - return err - } - return nil -} diff --git a/internal/repository/schema.go b/internal/repository/schema.go new file mode 100644 index 0000000..9ab8284 --- /dev/null +++ b/internal/repository/schema.go @@ -0,0 +1,211 @@ +package repository + +import ( + "database/sql" + "fmt" +) + +// tablesSchema defines all CREATE TABLE statements for a fresh database. +const tablesSchema = ` +CREATE TABLE IF NOT EXISTS users ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + username TEXT UNIQUE NOT NULL, + password_hash TEXT NOT NULL, + is_admin INTEGER NOT NULL DEFAULT 0, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE IF NOT EXISTS sets ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT NOT NULL, + root_path TEXT UNIQUE NOT NULL, + cover_thumbnail_path TEXT, + is_podcast INTEGER NOT NULL DEFAULT 0, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE IF NOT EXISTS set_permissions ( + set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + role TEXT CHECK(role IN ('owner','viewer')) NOT NULL DEFAULT 'viewer', + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (set_id, user_id) +); + +CREATE TABLE IF NOT EXISTS media ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE, + rel_path TEXT NOT NULL, + file_name TEXT NOT NULL, + abs_path TEXT NOT NULL, + type TEXT CHECK(type IN ('video','audio','image')) NOT NULL, + duration REAL, + codec TEXT, + resolution TEXT, + bitrate INTEGER, + file_size_bytes INTEGER, + width INTEGER, + height INTEGER, + exif_camera TEXT, + exif_lens TEXT, + exif_date TEXT, + exif_iso TEXT, + exif_f_number TEXT, + exif_exposure TEXT, + exif_focal_length TEXT, + thumbnail_path TEXT, + play_count INTEGER NOT NULL DEFAULT 0, + deleted_at DATETIME, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, + UNIQUE(set_id, rel_path) +); + +CREATE TABLE IF NOT EXISTS tags ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT UNIQUE NOT NULL +); + +CREATE TABLE IF NOT EXISTS media_tags ( + media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, + tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE, + PRIMARY KEY (media_id, tag_id) +); + +CREATE TABLE IF NOT EXISTS favorites ( + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (user_id, media_id) +); + +CREATE TABLE IF NOT EXISTS playback_progress ( + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, + position_seconds REAL NOT NULL, + updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (user_id, media_id) +); + +CREATE TABLE IF NOT EXISTS sessions ( + id TEXT PRIMARY KEY, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + expires_at DATETIME NOT NULL, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE IF NOT EXISTS playback_accumulator ( + session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE, + media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, + last_position REAL NOT NULL DEFAULT 0, + accumulated_seconds REAL NOT NULL DEFAULT 0, + counted INTEGER NOT NULL DEFAULT 0, + updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (session_id, media_id) +); + +CREATE TABLE IF NOT EXISTS shares ( + token TEXT PRIMARY KEY, + media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, + created_by INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, + expires_at DATETIME NOT NULL, + max_uses INTEGER, + used_count INTEGER NOT NULL DEFAULT 0 +); + +CREATE TABLE IF NOT EXISTS media_notes ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + content TEXT NOT NULL DEFAULT '', + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, + updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, + UNIQUE(media_id, user_id) +); + +CREATE TABLE IF NOT EXISTS podcast_feeds ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE, + feed_url TEXT NOT NULL, + title TEXT, + description TEXT, + image_url TEXT, + last_checked_at DATETIME, + last_etag TEXT, + check_interval_minutes INTEGER NOT NULL DEFAULT 60, + auto_download INTEGER NOT NULL DEFAULT 0, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE IF NOT EXISTS podcast_episodes ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + feed_id INTEGER NOT NULL REFERENCES podcast_feeds(id) ON DELETE CASCADE, + media_id INTEGER UNIQUE REFERENCES media(id) ON DELETE SET NULL, + guid TEXT NOT NULL, + title TEXT, + description TEXT, + published_at DATETIME, + episode_url TEXT NOT NULL, + duration_seconds REAL, + file_size INTEGER, + file_name TEXT, + is_downloaded INTEGER NOT NULL DEFAULT 0, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, + UNIQUE(feed_id, guid) +); + +CREATE TABLE IF NOT EXISTS podcast_status ( + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + episode_id INTEGER NOT NULL REFERENCES podcast_episodes(id) ON DELETE CASCADE, + is_completed INTEGER NOT NULL DEFAULT 0, + position_seconds REAL NOT NULL DEFAULT 0, + updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (user_id, episode_id) +); +` + +// indexesSchema defines all CREATE INDEX statements. +const indexesSchema = ` +CREATE INDEX IF NOT EXISTS idx_media_set_id ON media(set_id); +CREATE INDEX IF NOT EXISTS idx_media_rel_path ON media(set_id, rel_path); +CREATE INDEX IF NOT EXISTS idx_media_deleted_at ON media(deleted_at); +CREATE INDEX IF NOT EXISTS idx_media_type ON media(type); +CREATE INDEX IF NOT EXISTS idx_media_filename ON media(file_name); +CREATE INDEX IF NOT EXISTS idx_permissions_user ON set_permissions(user_id); +CREATE INDEX IF NOT EXISTS idx_permissions_set ON set_permissions(set_id); +CREATE INDEX IF NOT EXISTS idx_shares_expires ON shares(expires_at); +CREATE INDEX IF NOT EXISTS idx_podcast_episodes_feed ON podcast_episodes(feed_id); +CREATE INDEX IF NOT EXISTS idx_podcast_episodes_media ON podcast_episodes(media_id); +CREATE INDEX IF NOT EXISTS idx_podcast_status_episode ON podcast_status(episode_id); +CREATE INDEX IF NOT EXISTS idx_sets_is_podcast ON sets(is_podcast); +` + +// execSchema executes a raw SQL schema block against the given database. +func execSchema(db *sql.DB, name, schema string) error { + if _, err := db.Exec(schema); err != nil { + return fmt.Errorf("execute %s schema: %w", name, err) + } + return nil +} + +// enableForeignKeys turns on SQLite foreign key enforcement. +func enableForeignKeys(db *sql.DB) error { + if _, err := db.Exec(`PRAGMA foreign_keys = ON;`); err != nil { + return fmt.Errorf("enable foreign keys: %w", err) + } + return nil +} + +// initializeSchema creates the database schema for a fresh database. +func initializeSchema(db *sql.DB) error { + if err := enableForeignKeys(db); err != nil { + return err + } + if err := execSchema(db, "tables", tablesSchema); err != nil { + return err + } + if err := execSchema(db, "indexes", indexesSchema); err != nil { + return err + } + return nil +} diff --git a/internal/repository/sqlite.go b/internal/repository/sqlite.go index d324f8c..08a902c 100644 --- a/internal/repository/sqlite.go +++ b/internal/repository/sqlite.go @@ -14,10 +14,10 @@ type SQLite struct { db *sql.DB } -// New creates a SQLite store from an existing *sql.DB after migrating the schema. +// New creates a SQLite store from an existing *sql.DB after initializing the schema. func New(db *sql.DB) (*SQLite, error) { - if err := Migrate(db); err != nil { - return nil, fmt.Errorf("migrate: %w", err) + if err := initializeSchema(db); err != nil { + return nil, fmt.Errorf("initialize schema: %w", err) } return &SQLite{db: db}, nil } diff --git a/internal/repository/sqlite_test.go b/internal/repository/sqlite_test.go index fa15a80..eaf142f 100644 --- a/internal/repository/sqlite_test.go +++ b/internal/repository/sqlite_test.go @@ -1115,7 +1115,7 @@ func TestSQLite_OpenFailures(t *testing.T) { } }) - t.Run("closed db migrate failure", func(t *testing.T) { + t.Run("closed db schema initialization failure", func(t *testing.T) { db, err := sql.Open("sqlite", ":memory:") if err != nil { t.Fatalf("open: %v", err) @@ -1123,7 +1123,73 @@ func TestSQLite_OpenFailures(t *testing.T) { db.Close() _, err = New(db) if err == nil { - t.Fatal("expected error when migrating closed db") + t.Fatal("expected error when initializing schema on closed db") + } + }) +} + +func TestSQLite_SchemaInitialization(t *testing.T) { + t.Run("fresh database includes podcast column and foreign keys", func(t *testing.T) { + s := newTestStore(t) + defer s.Close() + + var isPodcastColumn int + rows, err := s.db.Query(`PRAGMA table_info(sets)`) + if err != nil { + t.Fatalf("table info: %v", err) + } + defer rows.Close() + for rows.Next() { + var cid int + var name, typ string + var notNull int + var defaultValue sql.NullString + var pk int + if err := rows.Scan(&cid, &name, &typ, ¬Null, &defaultValue, &pk); err != nil { + t.Fatalf("scan column: %v", err) + } + if name == "is_podcast" { + isPodcastColumn++ + } + } + if err := rows.Err(); err != nil { + t.Fatalf("rows: %v", err) + } + if isPodcastColumn != 1 { + t.Fatalf("expected one is_podcast column, got %d", isPodcastColumn) + } + + var foreignKeys int + if err := s.db.QueryRow(`PRAGMA foreign_keys`).Scan(&foreignKeys); err != nil { + t.Fatalf("foreign_keys pragma: %v", err) + } + if foreignKeys != 1 { + t.Fatalf("expected foreign keys enabled, got %d", foreignKeys) + } + }) + + t.Run("stale pre-podcast sets schema is not upgraded", func(t *testing.T) { + db, err := sql.Open("sqlite", ":memory:") + if err != nil { + t.Fatalf("open: %v", err) + } + defer db.Close() + + _, err = db.Exec(` +CREATE TABLE sets ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT NOT NULL, + root_path TEXT UNIQUE NOT NULL, + cover_thumbnail_path TEXT, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +);`) + if err != nil { + t.Fatalf("create stale schema: %v", err) + } + + _, err = New(db) + if err == nil { + t.Fatal("expected stale schema initialization to fail") } }) } -- cgit v1.2.3