45 lines
2.3 KiB
SQL
45 lines
2.3 KiB
SQL
-- Phase 3: OpenSubsonic support.
|
|
|
|
-- API keys gain an optional client description (separate from the
|
|
-- key's own name), per the apiKeyAuthentication extension's "optionally
|
|
-- store client description" requirement.
|
|
ALTER TABLE api_keys ADD COLUMN client_description TEXT NOT NULL DEFAULT '';
|
|
|
|
-- Structured (word/syllable-level) lyric cues need end time and
|
|
-- precomputed UTF-8 byte offsets into their line's text, per the
|
|
-- songLyrics v2 extension. Stored at parse time rather than recomputed
|
|
-- per request, so nothing is lost or re-derived lossily later.
|
|
ALTER TABLE lyrics_cues ADD COLUMN end_ms INTEGER;
|
|
ALTER TABLE lyrics_cues ADD COLUMN byte_start INTEGER NOT NULL DEFAULT 0;
|
|
ALTER TABLE lyrics_cues ADD COLUMN byte_end INTEGER NOT NULL DEFAULT 0;
|
|
|
|
-- Optional, opt-in, cached external metadata (MusicBrainz / Cover Art
|
|
-- Archive), keyed by the MusicBrainz entity type+ID VibeSonic already
|
|
-- has from tags. Never consulted unless VIBESONIC_EXTERNAL_METADATA is
|
|
-- enabled and a local musicbrainz_id exists; always safe to be empty.
|
|
CREATE TABLE external_metadata_cache (
|
|
id TEXT PRIMARY KEY,
|
|
entity_type TEXT NOT NULL CHECK (entity_type IN ('artist','release')),
|
|
mbid TEXT NOT NULL,
|
|
source TEXT NOT NULL CHECK (source IN ('musicbrainz','coverartarchive')),
|
|
data_json TEXT NOT NULL DEFAULT '{}',
|
|
fetched_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (entity_type, mbid, source)
|
|
);
|
|
|
|
-- Real per-user playback timeline reporting (OpenSubsonic
|
|
-- reportPlayback extension), distinct from the classic scrobble
|
|
-- endpoint: tracks transient state (starting/playing/paused/stopped)
|
|
-- rather than only submission events.
|
|
CREATE TABLE playback_reports (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
song_id TEXT NOT NULL REFERENCES songs(id) ON DELETE CASCADE,
|
|
position_ms INTEGER NOT NULL DEFAULT 0,
|
|
state TEXT NOT NULL CHECK (state IN ('starting','playing','paused','stopped')),
|
|
playback_rate REAL NOT NULL DEFAULT 1,
|
|
client TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
CREATE INDEX idx_playback_reports_user ON playback_reports(user_id, created_at);
|