-- yt - A fully featured command line YouTube client
--
-- Copyright (C) 2024 Benedikt Peetz <benedikt.peetz@b-peetz.de>
-- SPDX-License-Identifier: GPL-3.0-or-later
--
-- This file is part of Yt.
--
-- You should have received a copy of the License along with this program.
-- If not, see <https://www.gnu.org/licenses/gpl-3.0.txt>.
-- All tables should be declared STRICT, as I actually like to have types checking (and a
-- db that doesn't lie to me).
-- Keep this table in sync with the `Video` structure
CREATE TABLE IF NOT EXISTS videos (
cache_path TEXT UNIQUE CHECK (CASE WHEN cache_path IS NOT NULL THEN
status == 2
ELSE
1
END),
description TEXT,
duration REAL,
extractor_hash TEXT UNIQUE NOT NULL PRIMARY KEY,
last_status_change INTEGER NOT NULL,
parent_subscription_name TEXT,
priority INTEGER NOT NULL DEFAULT 0,
publish_date INTEGER,
status INTEGER NOT NULL DEFAULT 0 CHECK (status IN (0, 1, 2, 3, 4, 5) AND
CASE WHEN status == 2 THEN
cache_path IS NOT NULL
ELSE
1
END AND
CASE WHEN status != 2 THEN
cache_path IS NULL
ELSE
1
END),
status_change INTEGER NOT NULL DEFAULT 0 CHECK (status_change IN (0, 1)),
thumbnail_url TEXT,
title TEXT NOT NULL,
url TEXT UNIQUE NOT NULL
) STRICT;
-- Store additional metadata for the videos marked to be watched
CREATE TABLE IF NOT EXISTS video_options (
extractor_hash TEXT UNIQUE NOT NULL PRIMARY KEY,
subtitle_langs TEXT NOT NULL,
playback_speed REAL NOT NULL,
FOREIGN KEY(extractor_hash) REFERENCES videos (extractor_hash)
) STRICT;
-- Store subscriptions
CREATE TABLE IF NOT EXISTS subscriptions (
name TEXT UNIQUE NOT NULL PRIMARY KEY,
url TEXT NOT NULL
) STRICT;