464 lines
21 KiB
SQL
464 lines
21 KiB
SQL
-- VibeSonic initial schema.
|
|
--
|
|
-- IDs are opaque strings assigned by internal/idgen (see that package for
|
|
-- why) and are stable across restarts and rescans. Timestamps are ISO-8601
|
|
-- UTC strings. Booleans are INTEGER 0/1.
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Auth
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
email TEXT NOT NULL DEFAULT '',
|
|
password_hash TEXT, -- NULL for external-auth-only users
|
|
is_admin INTEGER NOT NULL DEFAULT 0,
|
|
disabled INTEGER NOT NULL DEFAULT 0,
|
|
external_auth INTEGER NOT NULL DEFAULT 0, -- created via trusted-proxy header auth
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
last_login_at TEXT
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
expires_at TEXT NOT NULL,
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
ip_address TEXT NOT NULL DEFAULT ''
|
|
);
|
|
CREATE INDEX idx_sessions_user ON sessions(user_id);
|
|
CREATE INDEX idx_sessions_expires ON sessions(expires_at);
|
|
|
|
CREATE TABLE api_keys (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL DEFAULT '',
|
|
key_hash TEXT NOT NULL UNIQUE,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
last_used_at TEXT,
|
|
revoked_at TEXT
|
|
);
|
|
CREATE INDEX idx_api_keys_user ON api_keys(user_id);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Library / scanning
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE music_folders (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
path TEXT NOT NULL UNIQUE,
|
|
kind TEXT NOT NULL DEFAULT 'music' CHECK (kind IN ('music','audiobooks','podcasts','video')),
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
|
|
CREATE TABLE media_files (
|
|
id TEXT PRIMARY KEY,
|
|
music_folder_id TEXT NOT NULL REFERENCES music_folders(id) ON DELETE CASCADE,
|
|
relative_path TEXT NOT NULL, -- path within the music folder; the stable natural key for rescans
|
|
size_bytes INTEGER NOT NULL DEFAULT 0,
|
|
mtime_unix INTEGER NOT NULL DEFAULT 0,
|
|
content_hash TEXT NOT NULL DEFAULT '', -- cheap fingerprint (size+mtime or partial hash), used for change/rename detection
|
|
format TEXT NOT NULL DEFAULT '', -- container/codec, e.g. "flac", "mp3"
|
|
kind TEXT NOT NULL DEFAULT 'song' CHECK (kind IN ('song','video')),
|
|
duration_seconds REAL NOT NULL DEFAULT 0,
|
|
bitrate_kbps INTEGER NOT NULL DEFAULT 0,
|
|
sample_rate_hz INTEGER NOT NULL DEFAULT 0,
|
|
channels INTEGER NOT NULL DEFAULT 0,
|
|
missing INTEGER NOT NULL DEFAULT 0, -- set when the file disappeared from disk but the row is kept (e.g. it has stars/playlists)
|
|
first_seen_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
last_scanned_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (music_folder_id, relative_path)
|
|
);
|
|
CREATE INDEX idx_media_files_folder ON media_files(music_folder_id);
|
|
CREATE INDEX idx_media_files_hash ON media_files(content_hash);
|
|
CREATE INDEX idx_media_files_missing ON media_files(missing);
|
|
|
|
CREATE TABLE scan_state (
|
|
id TEXT PRIMARY KEY,
|
|
music_folder_id TEXT NOT NULL UNIQUE REFERENCES music_folders(id) ON DELETE CASCADE,
|
|
status TEXT NOT NULL DEFAULT 'idle' CHECK (status IN ('idle','scanning','error')),
|
|
started_at TEXT,
|
|
finished_at TEXT,
|
|
files_seen INTEGER NOT NULL DEFAULT 0,
|
|
files_total_estimate INTEGER NOT NULL DEFAULT 0,
|
|
files_added INTEGER NOT NULL DEFAULT 0,
|
|
files_updated INTEGER NOT NULL DEFAULT 0,
|
|
files_removed INTEGER NOT NULL DEFAULT 0,
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
last_full_scan_at TEXT
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Tags / ID3 browsing model
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE artists (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
sort_name TEXT NOT NULL DEFAULT '',
|
|
musicbrainz_id TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (name, musicbrainz_id)
|
|
);
|
|
CREATE INDEX idx_artists_name ON artists(name COLLATE NOCASE);
|
|
|
|
CREATE TABLE genres (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE albums (
|
|
id TEXT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
sort_title TEXT NOT NULL DEFAULT '',
|
|
release_date TEXT NOT NULL DEFAULT '',
|
|
original_release_date TEXT NOT NULL DEFAULT '',
|
|
year INTEGER,
|
|
original_year INTEGER,
|
|
record_label TEXT NOT NULL DEFAULT '',
|
|
musicbrainz_id TEXT NOT NULL DEFAULT '',
|
|
primary_album_artist_id TEXT REFERENCES artists(id) ON DELETE SET NULL, -- denormalized for fast "by artist" grouping; album_artists below is authoritative
|
|
cover_art_id TEXT REFERENCES cover_art(id) ON DELETE SET NULL, -- forward reference; SQLite resolves FKs at DML time, not CREATE TABLE time
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (title, primary_album_artist_id)
|
|
);
|
|
CREATE INDEX idx_albums_title ON albums(title COLLATE NOCASE);
|
|
CREATE INDEX idx_albums_artist ON albums(primary_album_artist_id);
|
|
|
|
CREATE TABLE album_artists (
|
|
album_id TEXT NOT NULL REFERENCES albums(id) ON DELETE CASCADE,
|
|
artist_id TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (album_id, artist_id)
|
|
);
|
|
|
|
CREATE TABLE album_genres (
|
|
album_id TEXT NOT NULL REFERENCES albums(id) ON DELETE CASCADE,
|
|
genre_id TEXT NOT NULL REFERENCES genres(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (album_id, genre_id)
|
|
);
|
|
|
|
CREATE TABLE songs (
|
|
id TEXT PRIMARY KEY,
|
|
media_file_id TEXT NOT NULL UNIQUE REFERENCES media_files(id) ON DELETE CASCADE,
|
|
album_id TEXT REFERENCES albums(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
sort_title TEXT NOT NULL DEFAULT '',
|
|
track_number INTEGER,
|
|
track_total INTEGER,
|
|
disc_number INTEGER,
|
|
disc_total INTEGER,
|
|
disc_subtitle TEXT NOT NULL DEFAULT '',
|
|
composer TEXT NOT NULL DEFAULT '',
|
|
conductor TEXT NOT NULL DEFAULT '',
|
|
comment TEXT NOT NULL DEFAULT '',
|
|
isrc TEXT NOT NULL DEFAULT '',
|
|
bpm INTEGER,
|
|
musicbrainz_track_id TEXT NOT NULL DEFAULT '',
|
|
musicbrainz_recording_id TEXT NOT NULL DEFAULT '',
|
|
replaygain_track_gain_db REAL,
|
|
replaygain_track_peak REAL,
|
|
replaygain_album_gain_db REAL,
|
|
replaygain_album_peak REAL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
CREATE INDEX idx_songs_album ON songs(album_id);
|
|
CREATE INDEX idx_songs_title ON songs(title COLLATE NOCASE);
|
|
|
|
-- Main performer credits (primary artist, featured artists). role
|
|
-- distinguishes e.g. 'artist' vs 'featured'.
|
|
CREATE TABLE song_artists (
|
|
song_id TEXT NOT NULL REFERENCES songs(id) ON DELETE CASCADE,
|
|
artist_id TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL DEFAULT 'artist',
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (song_id, artist_id, role)
|
|
);
|
|
CREATE INDEX idx_song_artists_artist ON song_artists(artist_id);
|
|
|
|
-- Non-performer credits (producer, engineer, remixer, arranger, ...).
|
|
CREATE TABLE contributors (
|
|
song_id TEXT NOT NULL REFERENCES songs(id) ON DELETE CASCADE,
|
|
artist_id TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (song_id, artist_id, role)
|
|
);
|
|
|
|
CREATE TABLE song_genres (
|
|
song_id TEXT NOT NULL REFERENCES songs(id) ON DELETE CASCADE,
|
|
genre_id TEXT NOT NULL REFERENCES genres(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (song_id, genre_id)
|
|
);
|
|
CREATE INDEX idx_song_genres_genre ON song_genres(genre_id);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Cover art
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE cover_art (
|
|
id TEXT PRIMARY KEY,
|
|
album_id TEXT REFERENCES albums(id) ON DELETE CASCADE,
|
|
media_file_id TEXT REFERENCES media_files(id) ON DELETE CASCADE,
|
|
source TEXT NOT NULL CHECK (source IN ('embedded','folder','external')),
|
|
source_path TEXT NOT NULL DEFAULT '', -- absolute path (folder image) or empty (embedded, read via ffprobe on demand)
|
|
mime_type TEXT NOT NULL DEFAULT '',
|
|
content_hash TEXT NOT NULL DEFAULT '', -- also used as the on-disk resized-cache key
|
|
width INTEGER NOT NULL DEFAULT 0,
|
|
height INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
CHECK ((album_id IS NOT NULL) OR (media_file_id IS NOT NULL))
|
|
);
|
|
CREATE INDEX idx_cover_art_album ON cover_art(album_id);
|
|
CREATE INDEX idx_cover_art_media_file ON cover_art(media_file_id);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Lyrics
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE lyrics (
|
|
id TEXT PRIMARY KEY,
|
|
song_id TEXT NOT NULL REFERENCES songs(id) ON DELETE CASCADE,
|
|
language TEXT NOT NULL DEFAULT 'xxx',
|
|
source TEXT NOT NULL DEFAULT 'embedded' CHECK (source IN ('embedded','external')),
|
|
synced INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
CREATE INDEX idx_lyrics_song ON lyrics(song_id);
|
|
|
|
CREATE TABLE lyrics_lines (
|
|
id TEXT PRIMARY KEY,
|
|
lyrics_id TEXT NOT NULL REFERENCES lyrics(id) ON DELETE CASCADE,
|
|
line_index INTEGER NOT NULL,
|
|
text TEXT NOT NULL DEFAULT ''
|
|
);
|
|
CREATE INDEX idx_lyrics_lines_lyrics ON lyrics_lines(lyrics_id, line_index);
|
|
|
|
CREATE TABLE lyrics_cues (
|
|
id TEXT PRIMARY KEY,
|
|
lyrics_id TEXT NOT NULL REFERENCES lyrics(id) ON DELETE CASCADE,
|
|
line_index INTEGER NOT NULL,
|
|
start_ms INTEGER NOT NULL,
|
|
text TEXT NOT NULL DEFAULT ''
|
|
);
|
|
CREATE INDEX idx_lyrics_cues_lyrics ON lyrics_cues(lyrics_id, start_ms);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Playlists
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE playlists (
|
|
id TEXT PRIMARY KEY,
|
|
owner_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
is_public INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
CREATE INDEX idx_playlists_owner ON playlists(owner_id);
|
|
|
|
CREATE TABLE playlist_entries (
|
|
id TEXT PRIMARY KEY,
|
|
playlist_id TEXT NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
|
|
song_id TEXT NOT NULL REFERENCES songs(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL,
|
|
added_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (playlist_id, position)
|
|
);
|
|
CREATE INDEX idx_playlist_entries_playlist ON playlist_entries(playlist_id);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Favorites / ratings / listening activity
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE stars (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
item_type TEXT NOT NULL CHECK (item_type IN ('song','album','artist')),
|
|
item_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (user_id, item_type, item_id)
|
|
);
|
|
CREATE INDEX idx_stars_user ON stars(user_id, item_type);
|
|
|
|
CREATE TABLE ratings (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
item_type TEXT NOT NULL CHECK (item_type IN ('song','album','artist')),
|
|
item_id TEXT NOT NULL,
|
|
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (user_id, item_type, item_id)
|
|
);
|
|
|
|
CREATE TABLE scrobbles (
|
|
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,
|
|
played_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
client TEXT NOT NULL DEFAULT '',
|
|
submitted_to_lastfm INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX idx_scrobbles_user ON scrobbles(user_id, played_at);
|
|
|
|
CREATE TABLE playback_history (
|
|
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,
|
|
played_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
play_duration_seconds REAL NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX idx_playback_history_user ON playback_history(user_id, played_at);
|
|
|
|
CREATE TABLE now_playing (
|
|
user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
song_id TEXT NOT NULL REFERENCES songs(id) ON DELETE CASCADE,
|
|
started_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
client TEXT NOT NULL DEFAULT '',
|
|
position_seconds REAL NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE bookmarks (
|
|
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,
|
|
comment TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (user_id, song_id)
|
|
);
|
|
|
|
CREATE TABLE play_queues (
|
|
user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
song_ids_json TEXT NOT NULL DEFAULT '[]', -- ordered list of song IDs; genuinely variable-length, JSON is appropriate here
|
|
current_index INTEGER NOT NULL DEFAULT 0,
|
|
position_ms INTEGER NOT NULL DEFAULT 0,
|
|
client TEXT NOT NULL DEFAULT '',
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Sharing
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE shares (
|
|
id TEXT PRIMARY KEY,
|
|
owner_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token TEXT NOT NULL UNIQUE,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
expires_at TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
last_visited_at TEXT,
|
|
visit_count INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX idx_shares_owner ON shares(owner_id);
|
|
|
|
CREATE TABLE share_items (
|
|
id TEXT PRIMARY KEY,
|
|
share_id TEXT NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
|
|
song_id TEXT REFERENCES songs(id) ON DELETE CASCADE,
|
|
album_id TEXT REFERENCES albums(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
CHECK ((song_id IS NOT NULL) OR (album_id IS NOT NULL))
|
|
);
|
|
CREATE INDEX idx_share_items_share ON share_items(share_id);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Podcasts
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE podcast_channels (
|
|
id TEXT PRIMARY KEY,
|
|
url TEXT NOT NULL UNIQUE,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
cover_art_id TEXT REFERENCES cover_art(id) ON DELETE SET NULL,
|
|
status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new','downloading','completed','error')),
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
|
|
CREATE TABLE podcast_episodes (
|
|
id TEXT PRIMARY KEY,
|
|
channel_id TEXT NOT NULL REFERENCES podcast_channels(id) ON DELETE CASCADE,
|
|
guid TEXT NOT NULL,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
publish_date TEXT NOT NULL DEFAULT '',
|
|
duration_seconds REAL NOT NULL DEFAULT 0,
|
|
enclosure_url TEXT NOT NULL DEFAULT '',
|
|
media_file_id TEXT REFERENCES media_files(id) ON DELETE SET NULL,
|
|
status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new','downloading','completed','error','skipped')),
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
UNIQUE (channel_id, guid)
|
|
);
|
|
CREATE INDEX idx_podcast_episodes_channel ON podcast_episodes(channel_id);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Internet radio
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE internet_radio_stations (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
stream_url TEXT NOT NULL,
|
|
homepage_url TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Chat (Subsonic's simple shared chat room)
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE chat_messages (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
message TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
CREATE INDEX idx_chat_messages_created ON chat_messages(created_at);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Transcoding
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE transcode_profiles (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
target_format TEXT NOT NULL,
|
|
target_bitrate_kbps INTEGER NOT NULL DEFAULT 0,
|
|
command_template TEXT NOT NULL DEFAULT '', -- ffmpeg argument template; empty means use the built-in default for target_format
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Analysis (future audio fingerprinting / recommendations)
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE audio_features (
|
|
song_id TEXT PRIMARY KEY REFERENCES songs(id) ON DELETE CASCADE,
|
|
tempo_bpm REAL,
|
|
musical_key TEXT NOT NULL DEFAULT '',
|
|
loudness_lufs REAL,
|
|
extra_json TEXT NOT NULL DEFAULT '{}', -- forward-compatible bucket for analyzer-specific fields
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Settings
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL DEFAULT '',
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|