1 files changed, 56 insertions, 0 deletions
diff --git a/src/storage/video_database/schema.sql b/src/storage/video_database/schema.sql
new file mode 100644
index 0000000..b05d908
--- /dev/null
+++ b/src/storage/video_database/schema.sql
@@ -0,0 +1,56 @@
+-- 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>.
+
+-- The base schema
+
+-- 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                    FLOAT,
+    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
+);
+
+-- 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)
+);
+
+-- Store subscriptions
+CREATE TABLE IF NOT EXISTS subscriptions (
+    name              TEXT UNIQUE NOT NULL PRIMARY KEY,
+    url               TEXT        NOT NULL
+);