35 lines
1.6 KiB
SQL
35 lines
1.6 KiB
SQL
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
-- Active Audiobookshelf-compatible play sessions. One row per
|
||
|
|
-- /abs/api/items/{id}/play that hasn't yet been closed via
|
||
|
|
-- /abs/api/session/{sid}/close. Used to track per-session listening
|
||
|
|
-- time for ABS apps' "time listened today" stats.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS public.abs_playback_sessions (
|
||
|
|
id TEXT PRIMARY KEY, -- ULID issued at play start
|
||
|
|
user_id INTEGER NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
|
||
|
|
profile_id TEXT NOT NULL,
|
||
|
|
content_id TEXT NOT NULL REFERENCES public.media_items(content_id) ON DELETE CASCADE,
|
||
|
|
media_file_id INTEGER REFERENCES public.media_files(id) ON DELETE SET NULL,
|
||
|
|
started_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
||
|
|
last_sync_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
||
|
|
closed_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
time_listening_seconds INTEGER NOT NULL DEFAULT 0,
|
||
|
|
current_position_seconds DOUBLE PRECISION NOT NULL DEFAULT 0
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_abs_playback_sessions_user_profile
|
||
|
|
ON public.abs_playback_sessions (user_id, profile_id);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_abs_playback_sessions_open
|
||
|
|
ON public.abs_playback_sessions (closed_at)
|
||
|
|
WHERE closed_at IS NULL;
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
DROP INDEX IF EXISTS public.idx_abs_playback_sessions_open;
|
||
|
|
DROP INDEX IF EXISTS public.idx_abs_playback_sessions_user_profile;
|
||
|
|
DROP TABLE IF EXISTS public.abs_playback_sessions;
|
||
|
|
-- +goose StatementEnd
|