From 420f9c87abe3a3480a2345cbad5ec427636b2cb5 Mon Sep 17 00:00:00 2001 From: Benedikt Peetz Date: Fri, 21 Mar 2025 17:08:48 +0100 Subject: refactor(yt/storage/migrate/sql): Use predictable SQL paths I.e. `__to_.sql` --- yt/src/storage/migrate/mod.rs | 14 ++--- yt/src/storage/migrate/sql/00_empty_to_zero.sql | 72 ------------------------- yt/src/storage/migrate/sql/01_zero_to_one.sql | 28 ---------- yt/src/storage/migrate/sql/02_one_to_two.sql | 11 ---- yt/src/storage/migrate/sql/0_Empty_to_Zero.sql | 72 +++++++++++++++++++++++++ yt/src/storage/migrate/sql/1_Zero_to_One.sql | 28 ++++++++++ yt/src/storage/migrate/sql/2_One_to_Two.sql | 11 ++++ 7 files changed, 119 insertions(+), 117 deletions(-) delete mode 100644 yt/src/storage/migrate/sql/00_empty_to_zero.sql delete mode 100644 yt/src/storage/migrate/sql/01_zero_to_one.sql delete mode 100644 yt/src/storage/migrate/sql/02_one_to_two.sql create mode 100644 yt/src/storage/migrate/sql/0_Empty_to_Zero.sql create mode 100644 yt/src/storage/migrate/sql/1_Zero_to_One.sql create mode 100644 yt/src/storage/migrate/sql/2_One_to_Two.sql diff --git a/yt/src/storage/migrate/mod.rs b/yt/src/storage/migrate/mod.rs index 50a080a..da6b0be 100644 --- a/yt/src/storage/migrate/mod.rs +++ b/yt/src/storage/migrate/mod.rs @@ -22,7 +22,7 @@ use sqlx::{Sqlite, SqlitePool, Transaction, query}; use crate::app::App; macro_rules! make_upgrade { - ($app:expr, $old_version:expr, $new_version:expr, $update_path:expr) => { + ($app:expr, $old_version:expr, $new_version:expr, $sql_name:expr) => { add_error_context( async { let mut tx = $app @@ -32,7 +32,7 @@ macro_rules! make_upgrade { .context("Failed to start the update transaction")?; debug!("Migrating: {} -> {}", $old_version, $new_version); - sqlx::raw_sql(include_str!($update_path)) + sqlx::raw_sql(include_str!($sql_name)) .execute(&mut *tx) .await .context("Failed to run the update sql script")?; @@ -127,12 +127,14 @@ async fn set_db_version( impl DbVersion { fn as_sql_integer(self) -> i32 { match self { - DbVersion::Empty => unreachable!("A empty version does not have an associated integer"), DbVersion::Zero => 0, DbVersion::One => 1, DbVersion::Two => 2, + + DbVersion::Empty => unreachable!("A empty version does not have an associated integer"), } } + fn from_db(number: i64, namespace: &str) -> Result { match (number, namespace) { (0, "yt") => Ok(DbVersion::Zero), @@ -156,15 +158,15 @@ impl DbVersion { async fn update(self, app: &App) -> Result<()> { match self { Self::Empty => { - make_upgrade! {app, Self::Empty, Self::Zero, "./sql/00_empty_to_zero.sql"} + make_upgrade! {app, Self::Empty, Self::Zero, "./sql/0_Empty_to_Zero.sql"} } Self::Zero => { - make_upgrade! {app, Self::Zero, Self::One, "./sql/01_zero_to_one.sql"} + make_upgrade! {app, Self::Zero, Self::One, "./sql/1_Zero_to_One.sql"} } Self::One => { - make_upgrade! {app, Self::One, Self::Two, "./sql/02_one_to_two.sql"} + make_upgrade! {app, Self::One, Self::Two, "./sql/2_One_to_Two.sql"} } // This is the current_version diff --git a/yt/src/storage/migrate/sql/00_empty_to_zero.sql b/yt/src/storage/migrate/sql/00_empty_to_zero.sql deleted file mode 100644 index d703bfc..0000000 --- a/yt/src/storage/migrate/sql/00_empty_to_zero.sql +++ /dev/null @@ -1,72 +0,0 @@ --- yt - A fully featured command line YouTube client --- --- Copyright (C) 2024 Benedikt Peetz --- Copyright (C) 2025 Benedikt Peetz --- 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 . - --- 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 `DbVersion` enumeration. -CREATE TABLE version ( - -- The `namespace` is only useful, if other tools ever build on this database - namespace TEXT NOT NULL, - - -- The version. - number INTEGER UNIQUE NOT NULL PRIMARY KEY, - - -- The validity of this version as UNIX time stamp - valid_from INTEGER NOT NULL CHECK (valid_from < valid_to), - -- If set to `NULL`, represents the current version - valid_to INTEGER UNIQUE CHECK (valid_to > valid_from) -) STRICT; - --- Keep this table in sync with the `Video` structure -CREATE TABLE 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 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 subscriptions ( - name TEXT UNIQUE NOT NULL PRIMARY KEY, - url TEXT NOT NULL -) STRICT; diff --git a/yt/src/storage/migrate/sql/01_zero_to_one.sql b/yt/src/storage/migrate/sql/01_zero_to_one.sql deleted file mode 100644 index da9315b..0000000 --- a/yt/src/storage/migrate/sql/01_zero_to_one.sql +++ /dev/null @@ -1,28 +0,0 @@ --- yt - A fully featured command line YouTube client --- --- Copyright (C) 2025 Benedikt Peetz --- 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 . - --- Is the video currently in a playlist? -ALTER TABLE videos ADD in_playlist INTEGER NOT NULL DEFAULT 0 CHECK (in_playlist IN (0, 1)); -UPDATE videos SET in_playlist = 0; - --- Is it 'focused' (i.e., the select video)? --- Only of video should be focused at a time. -ALTER TABLE videos -ADD COLUMN is_focused INTEGER NOT NULL DEFAULT 0 -CHECK (is_focused IN (0, 1)); -UPDATE videos SET is_focused = 0; - --- The progress the user made in watching the video. -ALTER TABLE videos ADD watch_progress INTEGER NOT NULL DEFAULT 0 CHECK (watch_progress <= duration); --- Assume, that the user has watched the video to end, if it is marked as watched -UPDATE videos SET watch_progress = ifnull(duration, 0) WHERE status = 3; -UPDATE videos SET watch_progress = 0 WHERE status != 3; - -ALTER TABLE videos DROP COLUMN status_change; diff --git a/yt/src/storage/migrate/sql/02_one_to_two.sql b/yt/src/storage/migrate/sql/02_one_to_two.sql deleted file mode 100644 index 806de07..0000000 --- a/yt/src/storage/migrate/sql/02_one_to_two.sql +++ /dev/null @@ -1,11 +0,0 @@ --- yt - A fully featured command line YouTube client --- --- Copyright (C) 2025 Benedikt Peetz --- 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 . - -ALTER TABLE videos DROP in_playlist; diff --git a/yt/src/storage/migrate/sql/0_Empty_to_Zero.sql b/yt/src/storage/migrate/sql/0_Empty_to_Zero.sql new file mode 100644 index 0000000..d703bfc --- /dev/null +++ b/yt/src/storage/migrate/sql/0_Empty_to_Zero.sql @@ -0,0 +1,72 @@ +-- yt - A fully featured command line YouTube client +-- +-- Copyright (C) 2024 Benedikt Peetz +-- Copyright (C) 2025 Benedikt Peetz +-- 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 . + +-- 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 `DbVersion` enumeration. +CREATE TABLE version ( + -- The `namespace` is only useful, if other tools ever build on this database + namespace TEXT NOT NULL, + + -- The version. + number INTEGER UNIQUE NOT NULL PRIMARY KEY, + + -- The validity of this version as UNIX time stamp + valid_from INTEGER NOT NULL CHECK (valid_from < valid_to), + -- If set to `NULL`, represents the current version + valid_to INTEGER UNIQUE CHECK (valid_to > valid_from) +) STRICT; + +-- Keep this table in sync with the `Video` structure +CREATE TABLE 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 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 subscriptions ( + name TEXT UNIQUE NOT NULL PRIMARY KEY, + url TEXT NOT NULL +) STRICT; diff --git a/yt/src/storage/migrate/sql/1_Zero_to_One.sql b/yt/src/storage/migrate/sql/1_Zero_to_One.sql new file mode 100644 index 0000000..da9315b --- /dev/null +++ b/yt/src/storage/migrate/sql/1_Zero_to_One.sql @@ -0,0 +1,28 @@ +-- yt - A fully featured command line YouTube client +-- +-- Copyright (C) 2025 Benedikt Peetz +-- 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 . + +-- Is the video currently in a playlist? +ALTER TABLE videos ADD in_playlist INTEGER NOT NULL DEFAULT 0 CHECK (in_playlist IN (0, 1)); +UPDATE videos SET in_playlist = 0; + +-- Is it 'focused' (i.e., the select video)? +-- Only of video should be focused at a time. +ALTER TABLE videos +ADD COLUMN is_focused INTEGER NOT NULL DEFAULT 0 +CHECK (is_focused IN (0, 1)); +UPDATE videos SET is_focused = 0; + +-- The progress the user made in watching the video. +ALTER TABLE videos ADD watch_progress INTEGER NOT NULL DEFAULT 0 CHECK (watch_progress <= duration); +-- Assume, that the user has watched the video to end, if it is marked as watched +UPDATE videos SET watch_progress = ifnull(duration, 0) WHERE status = 3; +UPDATE videos SET watch_progress = 0 WHERE status != 3; + +ALTER TABLE videos DROP COLUMN status_change; diff --git a/yt/src/storage/migrate/sql/2_One_to_Two.sql b/yt/src/storage/migrate/sql/2_One_to_Two.sql new file mode 100644 index 0000000..806de07 --- /dev/null +++ b/yt/src/storage/migrate/sql/2_One_to_Two.sql @@ -0,0 +1,11 @@ +-- yt - A fully featured command line YouTube client +-- +-- Copyright (C) 2025 Benedikt Peetz +-- 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 . + +ALTER TABLE videos DROP in_playlist; -- cgit 1.4.1