QuickandGitHub
3bdfc58512
* test(web): use safe auth placeholders * feat(settings): sync navigation and card customization * fix(settings): address customization review feedback * fix(settings): address customization review feedback * fix(settings): harden customization capability handling
915 lines
32 KiB
Go
915 lines
32 KiB
Go
// Package userdb provides per-user SQLite database management for Silo.
|
|
// Each user gets their own SQLite file storing profiles, watch progress,
|
|
// favorites, collections, playback sessions, and settings.
|
|
package userdb
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
)
|
|
|
|
// Schema is the full SQLite schema for per-user databases.
|
|
const Schema = `
|
|
CREATE TABLE IF NOT EXISTS profiles (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
avatar TEXT,
|
|
pin_hash TEXT,
|
|
is_child BOOLEAN DEFAULT false,
|
|
is_primary BOOLEAN NOT NULL DEFAULT false,
|
|
max_content_rating TEXT,
|
|
quality_preference TEXT DEFAULT '1080p',
|
|
language TEXT DEFAULT 'en',
|
|
subtitle_language TEXT,
|
|
subtitle_mode TEXT DEFAULT 'auto',
|
|
auto_skip_intro BOOLEAN DEFAULT false,
|
|
auto_skip_credits BOOLEAN DEFAULT false,
|
|
auto_skip_recap BOOLEAN DEFAULT false,
|
|
auto_play_next_preview BOOLEAN DEFAULT false,
|
|
show_forced_subtitles BOOLEAN NOT NULL DEFAULT true,
|
|
library_restrictions_enabled BOOLEAN DEFAULT false,
|
|
max_playback_quality TEXT DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS profile_allowed_libraries (
|
|
profile_id TEXT NOT NULL,
|
|
library_id INTEGER NOT NULL,
|
|
PRIMARY KEY (profile_id, library_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS watch_progress (
|
|
profile_id TEXT NOT NULL,
|
|
media_item_id TEXT NOT NULL,
|
|
position_seconds REAL NOT NULL,
|
|
duration_seconds REAL NOT NULL,
|
|
completed BOOLEAN DEFAULT false,
|
|
updated_at TEXT NOT NULL,
|
|
event_at TEXT,
|
|
synced_seq INTEGER,
|
|
last_file_id INTEGER,
|
|
last_resolution TEXT,
|
|
last_hdr BOOLEAN,
|
|
last_codec_video TEXT,
|
|
last_edition_key TEXT,
|
|
PRIMARY KEY (profile_id, media_item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS watch_history (
|
|
id TEXT PRIMARY KEY,
|
|
profile_id TEXT NOT NULL,
|
|
media_item_id TEXT NOT NULL,
|
|
watched_at TEXT NOT NULL,
|
|
duration_seconds REAL,
|
|
completed BOOLEAN DEFAULT false,
|
|
source TEXT NOT NULL DEFAULT 'legacy',
|
|
watch_identity TEXT NOT NULL DEFAULT '{}'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS hidden_history_items (
|
|
profile_id TEXT NOT NULL,
|
|
media_item_id TEXT NOT NULL,
|
|
hidden_before TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, media_item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS playback_sessions (
|
|
session_id TEXT PRIMARY KEY,
|
|
profile_id TEXT NOT NULL,
|
|
media_file_id INTEGER NOT NULL,
|
|
play_method TEXT NOT NULL,
|
|
position_seconds REAL DEFAULT 0,
|
|
is_paused BOOLEAN DEFAULT false,
|
|
started_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS favorites (
|
|
profile_id TEXT NOT NULL,
|
|
media_item_id TEXT NOT NULL,
|
|
added_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, media_item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS watchlist (
|
|
profile_id TEXT NOT NULL,
|
|
media_item_id TEXT NOT NULL,
|
|
added_at TEXT NOT NULL,
|
|
sort_index INTEGER,
|
|
PRIMARY KEY (profile_id, media_item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS home_item_dismissals (
|
|
profile_id TEXT NOT NULL,
|
|
surface TEXT NOT NULL,
|
|
media_item_id TEXT NOT NULL,
|
|
series_id TEXT,
|
|
progress_updated_at TEXT,
|
|
dismissed_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, surface, media_item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS personal_collections (
|
|
id TEXT PRIMARY KEY,
|
|
profile_id TEXT NOT NULL,
|
|
creator_profile_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
collection_type TEXT NOT NULL DEFAULT 'manual',
|
|
is_shared BOOLEAN DEFAULT false,
|
|
query_definition TEXT NOT NULL DEFAULT '{}',
|
|
sort_config TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS personal_collection_items (
|
|
collection_id TEXT NOT NULL,
|
|
media_item_id TEXT NOT NULL,
|
|
position INTEGER,
|
|
added_at TEXT NOT NULL,
|
|
PRIMARY KEY (collection_id, media_item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS personal_collection_profiles (
|
|
collection_id TEXT NOT NULL,
|
|
profile_id TEXT NOT NULL,
|
|
PRIMARY KEY (collection_id, profile_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS subtitle_preferences (
|
|
profile_id TEXT NOT NULL,
|
|
series_id TEXT NOT NULL,
|
|
subtitle_language TEXT,
|
|
subtitle_track_index INT,
|
|
external_subtitle_path TEXT,
|
|
subtitle_mode TEXT,
|
|
subtitle_track_signature TEXT NOT NULL DEFAULT '{}',
|
|
show_forced_subtitles BOOLEAN,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, series_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS audio_preferences (
|
|
profile_id TEXT NOT NULL,
|
|
series_id TEXT NOT NULL,
|
|
audio_track_index INT,
|
|
audio_language TEXT,
|
|
audio_track_signature TEXT NOT NULL DEFAULT '{}',
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, series_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS series_playback_preferences (
|
|
profile_id TEXT NOT NULL,
|
|
series_id TEXT NOT NULL,
|
|
resolution TEXT,
|
|
hdr BOOLEAN NOT NULL DEFAULT false,
|
|
codec_video TEXT,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, series_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS library_playback_preferences (
|
|
profile_id TEXT NOT NULL,
|
|
library_id INTEGER NOT NULL,
|
|
audio_language TEXT,
|
|
subtitle_language TEXT,
|
|
subtitle_mode TEXT,
|
|
show_forced_subtitles BOOLEAN,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, library_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS profile_onboarding (
|
|
profile_id TEXT NOT NULL,
|
|
tour_id TEXT NOT NULL,
|
|
last_step TEXT NOT NULL DEFAULT '',
|
|
completed_at TEXT,
|
|
skipped_at TEXT,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, tour_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_device_settings (
|
|
profile_id TEXT NOT NULL,
|
|
device_id TEXT NOT NULL,
|
|
key TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
device_name TEXT NOT NULL DEFAULT '',
|
|
device_platform TEXT NOT NULL DEFAULT '',
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, device_id, key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_devices (
|
|
profile_id TEXT NOT NULL,
|
|
device_id TEXT NOT NULL,
|
|
device_name TEXT NOT NULL DEFAULT '',
|
|
device_platform TEXT NOT NULL DEFAULT '',
|
|
last_seen_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, device_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS downloads (
|
|
id TEXT PRIMARY KEY,
|
|
profile_id TEXT NOT NULL,
|
|
media_item_id TEXT NOT NULL,
|
|
media_file_id INTEGER NOT NULL,
|
|
quality TEXT,
|
|
transcoded BOOLEAN DEFAULT false,
|
|
file_size INTEGER,
|
|
expires_at TEXT,
|
|
downloaded_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS profile_section_overrides (
|
|
id TEXT PRIMARY KEY,
|
|
profile_id TEXT NOT NULL,
|
|
scope TEXT NOT NULL CHECK (scope IN ('home', 'library')),
|
|
library_id TEXT,
|
|
section_id TEXT,
|
|
position INTEGER,
|
|
hidden INTEGER NOT NULL DEFAULT 0,
|
|
removed INTEGER NOT NULL DEFAULT 0,
|
|
section_type TEXT,
|
|
title TEXT,
|
|
featured INTEGER,
|
|
item_limit INTEGER,
|
|
config TEXT,
|
|
is_user_added INTEGER NOT NULL DEFAULT 0,
|
|
user_section_type TEXT,
|
|
user_config TEXT,
|
|
user_title TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_profile_section_overrides_lookup
|
|
ON profile_section_overrides(profile_id, scope, library_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_profile_allowed_libraries_lookup
|
|
ON profile_allowed_libraries(profile_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_personal_collection_profiles_lookup
|
|
ON personal_collection_profiles(profile_id, collection_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_home_item_dismissals_lookup
|
|
ON home_item_dismissals(profile_id, surface);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_hidden_history_items_lookup
|
|
ON hidden_history_items(profile_id, hidden_before);
|
|
` + settingContractSchema + jellycompatDisplayPrefsSchema
|
|
|
|
// jellycompatDisplayPrefsSchema is the dedicated home for Jellyfin
|
|
// DisplayPreferences blobs, which used to ride user_settings under synthetic
|
|
// jellycompat:* keys. It mirrors the PostgreSQL table in
|
|
// migrations/sql (jellycompat_displayprefs) with user_id omitted: this
|
|
// database is already scoped to one user. The value is opaque Jellyfin client
|
|
// JSON stored verbatim, so it is TEXT with no json_valid CHECK — this table
|
|
// stores what the client sent, it does not interpret it.
|
|
const jellycompatDisplayPrefsSchema = `
|
|
CREATE TABLE IF NOT EXISTS jellycompat_displayprefs (
|
|
prefs_id TEXT NOT NULL,
|
|
client TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (prefs_id, client)
|
|
);
|
|
`
|
|
|
|
// settingContractSchema is the per-user half of the canonical settings contract
|
|
// storage. It mirrors the PostgreSQL shape in
|
|
// migrations/sql/20260727010621_user_setting_values.sql with user_id omitted:
|
|
// this database is already scoped to one user. jsonb becomes TEXT plus a
|
|
// json_valid CHECK, bigserial becomes INTEGER PRIMARY KEY AUTOINCREMENT, and
|
|
// timestamptz becomes an RFC3339 TEXT column, matching the rest of this schema.
|
|
//
|
|
// This file declares no foreign keys, deliberately and consistently with every
|
|
// other table here, so deleting a profile, library, series or device removes
|
|
// these rows through the owning delete path rather than a cascade. The userstore
|
|
// conformance suite holds both backends to identical behavior there.
|
|
const settingContractSchema = `
|
|
CREATE TABLE IF NOT EXISTS user_setting_values (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT NOT NULL,
|
|
scope TEXT NOT NULL,
|
|
profile_id TEXT,
|
|
client_family TEXT,
|
|
device_id TEXT,
|
|
library_id INTEGER,
|
|
series_id TEXT,
|
|
value TEXT NOT NULL CHECK (json_valid(value)),
|
|
revision INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
CHECK (scope IN ('account', 'profile', 'profile_client', 'profile_device', 'profile_library', 'profile_series')),
|
|
CHECK (client_family IS NULL OR client_family IN ('tv', 'mobile', 'tablet', 'desktop', 'web')),
|
|
CHECK (
|
|
(scope = 'account' AND profile_id IS NULL AND client_family IS NULL AND device_id IS NULL AND library_id IS NULL AND series_id IS NULL) OR
|
|
(scope = 'profile' AND profile_id IS NOT NULL AND client_family IS NULL AND device_id IS NULL AND library_id IS NULL AND series_id IS NULL) OR
|
|
(scope = 'profile_client' AND profile_id IS NOT NULL AND client_family IS NOT NULL AND device_id IS NULL AND library_id IS NULL AND series_id IS NULL) OR
|
|
(scope = 'profile_device' AND profile_id IS NOT NULL AND client_family IS NULL AND device_id IS NOT NULL AND library_id IS NULL AND series_id IS NULL) OR
|
|
(scope = 'profile_library' AND profile_id IS NOT NULL AND client_family IS NULL AND device_id IS NULL AND library_id IS NOT NULL AND series_id IS NULL) OR
|
|
(scope = 'profile_series' AND profile_id IS NOT NULL AND client_family IS NULL AND device_id IS NULL AND library_id IS NULL AND series_id IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS user_setting_values_account_uq
|
|
ON user_setting_values (key) WHERE scope = 'account';
|
|
CREATE UNIQUE INDEX IF NOT EXISTS user_setting_values_profile_uq
|
|
ON user_setting_values (profile_id, key) WHERE scope = 'profile';
|
|
CREATE UNIQUE INDEX IF NOT EXISTS user_setting_values_profile_device_uq
|
|
ON user_setting_values (profile_id, device_id, key) WHERE scope = 'profile_device';
|
|
CREATE UNIQUE INDEX IF NOT EXISTS user_setting_values_profile_library_uq
|
|
ON user_setting_values (profile_id, library_id, key) WHERE scope = 'profile_library';
|
|
CREATE UNIQUE INDEX IF NOT EXISTS user_setting_values_profile_series_uq
|
|
ON user_setting_values (profile_id, series_id, key) WHERE scope = 'profile_series';
|
|
|
|
CREATE INDEX IF NOT EXISTS user_setting_values_resolution_idx
|
|
ON user_setting_values (profile_id, key, scope);
|
|
CREATE INDEX IF NOT EXISTS user_setting_values_series_idx
|
|
ON user_setting_values (profile_id, series_id);
|
|
CREATE INDEX IF NOT EXISTS user_setting_values_library_idx
|
|
ON user_setting_values (profile_id, library_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_setting_mutations (
|
|
mutation_id TEXT PRIMARY KEY,
|
|
request_hash TEXT NOT NULL,
|
|
result TEXT NOT NULL CHECK (json_valid(result)),
|
|
created_at TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_setting_mutations_expiry_idx
|
|
ON user_setting_mutations (expires_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_setting_migration_rejects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
source_table TEXT NOT NULL,
|
|
source_key TEXT NOT NULL,
|
|
identity TEXT NOT NULL CHECK (json_valid(identity)),
|
|
value TEXT,
|
|
reason TEXT NOT NULL,
|
|
recorded_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_setting_migration_rejects_source_idx
|
|
ON user_setting_migration_rejects (source_table);
|
|
`
|
|
|
|
// InitSchema creates all tables in the given SQLite database.
|
|
func InitSchema(db *sql.DB) error {
|
|
_, err := db.Exec(Schema)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if err := ensureSettingValuesClientFamily(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureProfileSectionOverridesRemovedColumn(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureProfileSectionOverridesUserAddedColumns(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureWatchHistorySourceColumn(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureShowForcedSubtitleColumns(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureProfileIsPrimaryColumn(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureDeviceSettingsProfileColumn(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureAutoSkipRecapPreviewColumns(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureWatchHistoryIdentityColumn(db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureWatchProgressSyncColumns(db); err != nil {
|
|
return err
|
|
}
|
|
if err := migratePlaybackSettingsToDeviceScope(db); err != nil {
|
|
return err
|
|
}
|
|
return backfillUserDevices(db)
|
|
}
|
|
|
|
// ensureSettingValuesClientFamily upgrades the canonical settings table before
|
|
// runMigrations reads it. InitSchema runs first on every open, and SQLite
|
|
// cannot widen a table CHECK constraint with ALTER TABLE, so an existing table
|
|
// must be rebuilt here rather than merely gaining a nullable column. The
|
|
// rebuild is transactional and preserves ids, revisions, values, and
|
|
// timestamps verbatim.
|
|
func ensureSettingValuesClientFamily(db *sql.DB) error {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"user_setting_values", "client_family",
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking user_setting_values.client_family column: %w", err)
|
|
}
|
|
if count > 0 {
|
|
_, err := db.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS user_setting_values_profile_client_uq
|
|
ON user_setting_values (profile_id, client_family, key) WHERE scope = 'profile_client'`)
|
|
if err != nil {
|
|
return fmt.Errorf("creating profile_client setting index: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
tx, err := db.Begin()
|
|
if err != nil {
|
|
return fmt.Errorf("beginning profile_client settings migration: %w", err)
|
|
}
|
|
defer tx.Rollback() //nolint:errcheck
|
|
|
|
if _, err := tx.Exec(`
|
|
CREATE TABLE user_setting_values_profile_client_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT NOT NULL,
|
|
scope TEXT NOT NULL,
|
|
profile_id TEXT,
|
|
client_family TEXT,
|
|
device_id TEXT,
|
|
library_id INTEGER,
|
|
series_id TEXT,
|
|
value TEXT NOT NULL CHECK (json_valid(value)),
|
|
revision INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
CHECK (scope IN ('account', 'profile', 'profile_client', 'profile_device', 'profile_library', 'profile_series')),
|
|
CHECK (client_family IS NULL OR client_family IN ('tv', 'mobile', 'tablet', 'desktop', 'web')),
|
|
CHECK (
|
|
(scope = 'account' AND profile_id IS NULL AND client_family IS NULL AND device_id IS NULL AND library_id IS NULL AND series_id IS NULL) OR
|
|
(scope = 'profile' AND profile_id IS NOT NULL AND client_family IS NULL AND device_id IS NULL AND library_id IS NULL AND series_id IS NULL) OR
|
|
(scope = 'profile_client' AND profile_id IS NOT NULL AND client_family IS NOT NULL AND device_id IS NULL AND library_id IS NULL AND series_id IS NULL) OR
|
|
(scope = 'profile_device' AND profile_id IS NOT NULL AND client_family IS NULL AND device_id IS NOT NULL AND library_id IS NULL AND series_id IS NULL) OR
|
|
(scope = 'profile_library' AND profile_id IS NOT NULL AND client_family IS NULL AND device_id IS NULL AND library_id IS NOT NULL AND series_id IS NULL) OR
|
|
(scope = 'profile_series' AND profile_id IS NOT NULL AND client_family IS NULL AND device_id IS NULL AND library_id IS NULL AND series_id IS NOT NULL)
|
|
)
|
|
);
|
|
INSERT INTO user_setting_values_profile_client_new
|
|
(id, key, scope, profile_id, client_family, device_id, library_id, series_id,
|
|
value, revision, created_at, updated_at)
|
|
SELECT id, key, scope, profile_id, NULL, device_id, library_id, series_id,
|
|
value, revision, created_at, updated_at
|
|
FROM user_setting_values;
|
|
DROP TABLE user_setting_values;
|
|
ALTER TABLE user_setting_values_profile_client_new RENAME TO user_setting_values;
|
|
CREATE UNIQUE INDEX user_setting_values_account_uq
|
|
ON user_setting_values (key) WHERE scope = 'account';
|
|
CREATE UNIQUE INDEX user_setting_values_profile_uq
|
|
ON user_setting_values (profile_id, key) WHERE scope = 'profile';
|
|
CREATE UNIQUE INDEX user_setting_values_profile_client_uq
|
|
ON user_setting_values (profile_id, client_family, key) WHERE scope = 'profile_client';
|
|
CREATE UNIQUE INDEX user_setting_values_profile_device_uq
|
|
ON user_setting_values (profile_id, device_id, key) WHERE scope = 'profile_device';
|
|
CREATE UNIQUE INDEX user_setting_values_profile_library_uq
|
|
ON user_setting_values (profile_id, library_id, key) WHERE scope = 'profile_library';
|
|
CREATE UNIQUE INDEX user_setting_values_profile_series_uq
|
|
ON user_setting_values (profile_id, series_id, key) WHERE scope = 'profile_series';
|
|
CREATE INDEX user_setting_values_resolution_idx
|
|
ON user_setting_values (profile_id, key, scope);
|
|
CREATE INDEX user_setting_values_series_idx
|
|
ON user_setting_values (profile_id, series_id);
|
|
CREATE INDEX user_setting_values_library_idx
|
|
ON user_setting_values (profile_id, library_id);
|
|
`); err != nil {
|
|
return fmt.Errorf("rebuilding user_setting_values for profile_client: %w", err)
|
|
}
|
|
if err := tx.Commit(); err != nil {
|
|
return fmt.Errorf("committing profile_client settings migration: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// watchProgressSyncTriggers stamps the server-owned cursor (synced_seq) on every
|
|
// watch_progress write and owns the LWW key: event_at defaults to the write
|
|
// time, and an UPDATE that changed updated_at without explicitly changing
|
|
// event_at advances it too (else a queued offline event older than that write
|
|
// would win SetProgressIfNewer's comparison and resurrect stale progress).
|
|
// Writes that DO set event_at — offline sync's clamped client event time —
|
|
// keep their value. With recursive_triggers OFF (the userdb default) the
|
|
// trigger's own UPDATE does not re-fire it. See the offline-sync design
|
|
// (invariant 1). CREATE TRIGGER IF NOT EXISTS never replaces an existing
|
|
// trigger, so changing a body requires a migrate.go step dropping the old one
|
|
// (see migrateToV13).
|
|
const watchProgressSyncTriggers = `
|
|
CREATE TRIGGER IF NOT EXISTS watch_progress_stamp_ins AFTER INSERT ON watch_progress
|
|
BEGIN
|
|
UPDATE watch_progress
|
|
SET synced_seq = (SELECT COALESCE(MAX(synced_seq), 0) + 1 FROM watch_progress),
|
|
event_at = COALESCE(event_at, updated_at)
|
|
WHERE rowid = NEW.rowid;
|
|
END;
|
|
CREATE TRIGGER IF NOT EXISTS watch_progress_stamp_upd AFTER UPDATE ON watch_progress
|
|
BEGIN
|
|
UPDATE watch_progress
|
|
SET synced_seq = (SELECT COALESCE(MAX(synced_seq), 0) + 1 FROM watch_progress),
|
|
event_at = CASE
|
|
WHEN NEW.event_at IS NULL THEN NEW.updated_at
|
|
WHEN NEW.event_at IS OLD.event_at AND NEW.updated_at IS NOT OLD.updated_at THEN NEW.updated_at
|
|
ELSE NEW.event_at
|
|
END
|
|
WHERE rowid = NEW.rowid;
|
|
END;
|
|
CREATE INDEX IF NOT EXISTS watch_progress_synced_idx ON watch_progress (profile_id, synced_seq);
|
|
`
|
|
|
|
// ensureWatchProgressSyncColumns adds the event_at (LWW key) and synced_seq
|
|
// (server cursor) columns, backfills them, and installs the stamping triggers.
|
|
// Idempotent: safe to run on every open for both fresh and existing databases.
|
|
func ensureWatchProgressSyncColumns(db *sql.DB) error {
|
|
columns := []struct{ name, definition string }{
|
|
{name: "event_at", definition: "TEXT"},
|
|
{name: "synced_seq", definition: "INTEGER"},
|
|
}
|
|
for _, column := range columns {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"watch_progress", column.name,
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking watch_progress.%s column: %w", column.name, err)
|
|
}
|
|
if count > 0 {
|
|
continue
|
|
}
|
|
if _, err := db.Exec(
|
|
fmt.Sprintf("ALTER TABLE watch_progress ADD COLUMN %s %s", column.name, column.definition),
|
|
); err != nil {
|
|
return fmt.Errorf("adding watch_progress.%s column: %w", column.name, err)
|
|
}
|
|
}
|
|
|
|
// Backfill before the triggers exist so these one-off writes don't trip them.
|
|
if _, err := db.Exec(`UPDATE watch_progress SET event_at = updated_at WHERE event_at IS NULL`); err != nil {
|
|
return fmt.Errorf("backfilling watch_progress.event_at: %w", err)
|
|
}
|
|
if _, err := db.Exec(`
|
|
UPDATE watch_progress SET synced_seq = (
|
|
SELECT COUNT(*) FROM watch_progress w2
|
|
WHERE w2.updated_at < watch_progress.updated_at
|
|
OR (w2.updated_at = watch_progress.updated_at AND w2.rowid <= watch_progress.rowid)
|
|
) WHERE synced_seq IS NULL`); err != nil {
|
|
return fmt.Errorf("backfilling watch_progress.synced_seq: %w", err)
|
|
}
|
|
|
|
if _, err := db.Exec(watchProgressSyncTriggers); err != nil {
|
|
return fmt.Errorf("installing watch_progress sync triggers: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureAutoSkipRecapPreviewColumns(db *sql.DB) error {
|
|
columns := []struct {
|
|
name string
|
|
definition string
|
|
}{
|
|
{name: "auto_skip_recap", definition: "BOOLEAN DEFAULT false"},
|
|
{name: "auto_play_next_preview", definition: "BOOLEAN DEFAULT false"},
|
|
}
|
|
|
|
for _, column := range columns {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"profiles",
|
|
column.name,
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking profiles.%s column: %w", column.name, err)
|
|
}
|
|
if count > 0 {
|
|
continue
|
|
}
|
|
if _, err := db.Exec(
|
|
fmt.Sprintf("ALTER TABLE profiles ADD COLUMN %s %s", column.name, column.definition),
|
|
); err != nil {
|
|
return fmt.Errorf("adding profiles.%s column: %w", column.name, err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureWatchHistoryIdentityColumn(db *sql.DB) error {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"watch_history",
|
|
"watch_identity",
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking watch_history.watch_identity column: %w", err)
|
|
}
|
|
if count > 0 {
|
|
return nil
|
|
}
|
|
if _, err := db.Exec("ALTER TABLE watch_history ADD COLUMN watch_identity TEXT NOT NULL DEFAULT '{}'"); err != nil {
|
|
return fmt.Errorf("adding watch_history.watch_identity: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureProfileIsPrimaryColumn(db *sql.DB) error {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"profiles",
|
|
"is_primary",
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking profiles.is_primary column: %w", err)
|
|
}
|
|
if count == 0 {
|
|
if _, err := db.Exec(
|
|
"ALTER TABLE profiles ADD COLUMN is_primary BOOLEAN NOT NULL DEFAULT false",
|
|
); err != nil {
|
|
return fmt.Errorf("adding profiles.is_primary column: %w", err)
|
|
}
|
|
}
|
|
// Backfill: the oldest existing profile (per-user sqlite file, so just the
|
|
// oldest row overall) is the primary. No-op if a primary already exists.
|
|
if _, err := db.Exec(`
|
|
UPDATE profiles
|
|
SET is_primary = 1
|
|
WHERE id = (
|
|
SELECT id FROM profiles
|
|
WHERE NOT EXISTS (SELECT 1 FROM profiles WHERE is_primary)
|
|
ORDER BY created_at ASC, id ASC
|
|
LIMIT 1
|
|
)
|
|
`); err != nil {
|
|
return fmt.Errorf("backfilling profiles.is_primary: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureDeviceSettingsProfileColumn(db *sql.DB) error {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"user_device_settings",
|
|
"profile_id",
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking user_device_settings.profile_id column: %w", err)
|
|
}
|
|
if count > 0 {
|
|
return nil
|
|
}
|
|
|
|
tx, err := db.Begin()
|
|
if err != nil {
|
|
return fmt.Errorf("beginning user_device_settings profile migration: %w", err)
|
|
}
|
|
defer tx.Rollback() //nolint:errcheck
|
|
|
|
if _, err := tx.Exec(`
|
|
CREATE TABLE user_device_settings_new (
|
|
profile_id TEXT NOT NULL,
|
|
device_id TEXT NOT NULL,
|
|
key TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
device_name TEXT NOT NULL DEFAULT '',
|
|
device_platform TEXT NOT NULL DEFAULT '',
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (profile_id, device_id, key)
|
|
)
|
|
`); err != nil {
|
|
return fmt.Errorf("creating user_device_settings_new: %w", err)
|
|
}
|
|
|
|
if _, err := tx.Exec(`
|
|
INSERT INTO profiles (id, name, is_primary, created_at, updated_at)
|
|
SELECT 'default', 'Default', 1, datetime('now'), datetime('now')
|
|
WHERE NOT EXISTS (SELECT 1 FROM profiles)
|
|
`); err != nil {
|
|
return fmt.Errorf("ensuring default profile for user_device_settings migration: %w", err)
|
|
}
|
|
|
|
if _, err := tx.Exec(`
|
|
INSERT INTO user_device_settings_new (
|
|
profile_id, device_id, key, value, device_name, device_platform, updated_at
|
|
)
|
|
SELECT p.id, uds.device_id, uds.key, uds.value, uds.device_name, uds.device_platform, uds.updated_at
|
|
FROM user_device_settings uds
|
|
JOIN (
|
|
SELECT id
|
|
FROM profiles
|
|
ORDER BY is_primary DESC, created_at ASC, id ASC
|
|
LIMIT 1
|
|
) p ON 1 = 1
|
|
`); err != nil {
|
|
return fmt.Errorf("backfilling profile-aware user_device_settings: %w", err)
|
|
}
|
|
|
|
if _, err := tx.Exec(`DROP TABLE user_device_settings`); err != nil {
|
|
return fmt.Errorf("dropping legacy user_device_settings: %w", err)
|
|
}
|
|
if _, err := tx.Exec(`ALTER TABLE user_device_settings_new RENAME TO user_device_settings`); err != nil {
|
|
return fmt.Errorf("renaming profile-aware user_device_settings: %w", err)
|
|
}
|
|
|
|
return tx.Commit()
|
|
}
|
|
|
|
func backfillUserDevices(db *sql.DB) error {
|
|
_, err := db.Exec(`
|
|
INSERT INTO user_devices (
|
|
profile_id, device_id, device_name, device_platform, last_seen_at
|
|
)
|
|
SELECT
|
|
profile_id,
|
|
device_id,
|
|
COALESCE(MAX(device_name), ''),
|
|
COALESCE(MAX(device_platform), ''),
|
|
MAX(updated_at)
|
|
FROM user_device_settings
|
|
WHERE TRIM(device_id) <> ''
|
|
GROUP BY profile_id, device_id
|
|
ON CONFLICT(profile_id, device_id) DO UPDATE SET
|
|
device_name = CASE
|
|
WHEN excluded.device_name <> '' THEN excluded.device_name
|
|
ELSE user_devices.device_name
|
|
END,
|
|
device_platform = CASE
|
|
WHEN excluded.device_platform <> '' THEN excluded.device_platform
|
|
ELSE user_devices.device_platform
|
|
END,
|
|
last_seen_at = CASE
|
|
WHEN excluded.last_seen_at > user_devices.last_seen_at THEN excluded.last_seen_at
|
|
ELSE user_devices.last_seen_at
|
|
END
|
|
`)
|
|
if err != nil {
|
|
return fmt.Errorf("backfilling user_devices: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureProfileSectionOverridesRemovedColumn(db *sql.DB) error {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"profile_section_overrides",
|
|
"removed",
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking profile_section_overrides.removed column: %w", err)
|
|
}
|
|
if count > 0 {
|
|
return nil
|
|
}
|
|
if _, err := db.Exec("ALTER TABLE profile_section_overrides ADD COLUMN removed INTEGER NOT NULL DEFAULT 0"); err != nil {
|
|
return fmt.Errorf("adding profile_section_overrides.removed: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// ensureProfileSectionOverridesUserAddedColumns adds the four columns that back
|
|
// user-added (profile-built) sections so SaveSectionOverrides can round-trip
|
|
// IsUserAdded / UserSectionType / UserConfig / UserTitle. Without these columns
|
|
// those fields are silently dropped on save and lost on subsequent reads.
|
|
func ensureProfileSectionOverridesUserAddedColumns(db *sql.DB) error {
|
|
columns := []struct {
|
|
name string
|
|
definition string
|
|
}{
|
|
{name: "is_user_added", definition: "INTEGER NOT NULL DEFAULT 0"},
|
|
{name: "user_section_type", definition: "TEXT"},
|
|
{name: "user_config", definition: "TEXT"},
|
|
{name: "user_title", definition: "TEXT"},
|
|
}
|
|
for _, c := range columns {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"profile_section_overrides",
|
|
c.name,
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking profile_section_overrides.%s column: %w", c.name, err)
|
|
}
|
|
if count > 0 {
|
|
continue
|
|
}
|
|
if _, err := db.Exec(fmt.Sprintf(
|
|
"ALTER TABLE profile_section_overrides ADD COLUMN %s %s", c.name, c.definition,
|
|
)); err != nil {
|
|
return fmt.Errorf("adding profile_section_overrides.%s: %w", c.name, err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureWatchHistorySourceColumn(db *sql.DB) error {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
"watch_history",
|
|
"source",
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking watch_history.source column: %w", err)
|
|
}
|
|
if count > 0 {
|
|
return nil
|
|
}
|
|
if _, err := db.Exec("ALTER TABLE watch_history ADD COLUMN source TEXT NOT NULL DEFAULT 'legacy'"); err != nil {
|
|
return fmt.Errorf("adding watch_history.source: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureShowForcedSubtitleColumns(db *sql.DB) error {
|
|
columns := []struct {
|
|
table string
|
|
column string
|
|
definition string
|
|
}{
|
|
{table: "profiles", column: "show_forced_subtitles", definition: "BOOLEAN NOT NULL DEFAULT true"},
|
|
{table: "library_playback_preferences", column: "show_forced_subtitles", definition: "BOOLEAN"},
|
|
{table: "subtitle_preferences", column: "show_forced_subtitles", definition: "BOOLEAN"},
|
|
}
|
|
|
|
for _, column := range columns {
|
|
var count int
|
|
if err := db.QueryRow(
|
|
"SELECT COUNT(*) FROM pragma_table_info(?) WHERE name = ?",
|
|
column.table,
|
|
column.column,
|
|
).Scan(&count); err != nil {
|
|
return fmt.Errorf("checking %s.%s column: %w", column.table, column.column, err)
|
|
}
|
|
if count > 0 {
|
|
continue
|
|
}
|
|
if _, err := db.Exec(
|
|
fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s %s", column.table, column.column, column.definition),
|
|
); err != nil {
|
|
return fmt.Errorf("adding %s.%s column: %w", column.table, column.column, err)
|
|
}
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func migratePlaybackSettingsToDeviceScope(db *sql.DB) error {
|
|
version, err := userVersion(db)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if version == 0 || version >= 9 {
|
|
return nil
|
|
}
|
|
|
|
tx, err := db.Begin()
|
|
if err != nil {
|
|
return fmt.Errorf("beginning playback settings device-scope migration: %w", err)
|
|
}
|
|
defer tx.Rollback() //nolint:errcheck
|
|
|
|
if _, err := tx.Exec(`
|
|
INSERT OR IGNORE INTO user_device_settings (
|
|
profile_id, device_id, key, value, device_name, device_platform, updated_at
|
|
)
|
|
SELECT
|
|
devices.profile_id,
|
|
devices.device_id,
|
|
settings.key,
|
|
settings.value,
|
|
devices.device_name,
|
|
devices.device_platform,
|
|
devices.updated_at
|
|
FROM user_settings AS settings
|
|
JOIN (
|
|
SELECT DISTINCT profile_id, device_id, device_name, device_platform, updated_at
|
|
FROM user_device_settings
|
|
) AS devices ON 1 = 1
|
|
WHERE settings.key IN (
|
|
'playback.preferred_quality',
|
|
'playback.audio_language',
|
|
'playback.auto_skip_intro',
|
|
'playback.auto_skip_credits',
|
|
'playback.auto_play_next'
|
|
)
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM user_device_settings AS existing
|
|
WHERE existing.profile_id = devices.profile_id
|
|
AND existing.device_id = devices.device_id
|
|
AND existing.key = settings.key
|
|
)
|
|
`); err != nil {
|
|
return fmt.Errorf("backfilling playback settings into user_device_settings: %w", err)
|
|
}
|
|
|
|
return tx.Commit()
|
|
}
|