45 lines
2.0 KiB
SQL
45 lines
2.0 KiB
SQL
CREATE TABLE download_jobs (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
episode_id TEXT NOT NULL UNIQUE,
|
|
series_id TEXT NOT NULL,
|
|
series_title TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
broadcaster_name TEXT NOT NULL,
|
|
duration_seconds INTEGER NOT NULL CHECK (duration_seconds >= 0),
|
|
end_at TIMESTAMPTZ,
|
|
thumbnail_path TEXT NOT NULL,
|
|
source TEXT NOT NULL CHECK (source IN ('manual', 'subscription')),
|
|
status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'succeeded', 'failed', 'canceled')),
|
|
phase TEXT CHECK (phase IN ('video', 'audio', 'merging', 'moving')),
|
|
downloaded_bytes BIGINT CHECK (downloaded_bytes >= 0),
|
|
total_bytes BIGINT CHECK (total_bytes >= 0),
|
|
total_is_estimate BOOLEAN NOT NULL DEFAULT FALSE,
|
|
speed_bps DOUBLE PRECISION CHECK (speed_bps >= 0),
|
|
eta_seconds DOUBLE PRECISION CHECK (eta_seconds >= 0),
|
|
fragment_index INTEGER CHECK (fragment_index >= 0),
|
|
fragment_count INTEGER CHECK (fragment_count >= 0),
|
|
format_id TEXT,
|
|
error_kind TEXT CHECK (error_kind IN ('drm', 'geo', 'network', 'unavailable', 'extract', 'canceled', 'invalid_output', 'unknown')),
|
|
error_message TEXT,
|
|
file_path TEXT,
|
|
file_size_bytes BIGINT CHECK (file_size_bytes >= 0),
|
|
attempts INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),
|
|
heartbeat_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
started_at TIMESTAMPTZ,
|
|
finished_at TIMESTAMPTZ,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX download_jobs_claim_idx ON download_jobs (created_at, id) WHERE status = 'queued';
|
|
CREATE INDEX download_jobs_status_newest_idx ON download_jobs (status, created_at DESC, id DESC);
|
|
CREATE INDEX download_jobs_stale_idx ON download_jobs (heartbeat_at) WHERE status = 'running';
|
|
|
|
CREATE TABLE subscriptions (
|
|
series_id TEXT PRIMARY KEY,
|
|
series_title TEXT NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_checked_at TIMESTAMPTZ
|
|
);
|