diff options
Diffstat (limited to 'yt/src/storage/migrate')
| -rw-r--r-- | yt/src/storage/migrate/mod.rs | 178 | ||||
| -rw-r--r-- | yt/src/storage/migrate/sql/00_empty_to_zero.sql | 71 |
2 files changed, 249 insertions, 0 deletions
diff --git a/yt/src/storage/migrate/mod.rs b/yt/src/storage/migrate/mod.rs new file mode 100644 index 0000000..4e956de --- /dev/null +++ b/yt/src/storage/migrate/mod.rs @@ -0,0 +1,178 @@ +use std::{ + fmt::Display, + time::{SystemTime, UNIX_EPOCH}, +}; + +use anyhow::{Context, Result, bail}; +use chrono::TimeDelta; +use log::{debug, info}; +use sqlx::{Sqlite, Transaction, query}; + +use crate::app::App; + +#[derive(Debug, Clone, Copy, PartialEq, PartialOrd)] +pub enum DbVersion { + /// The database is not yet initialized. + Empty, + + /// The first database version. + /// Introduced: 2025-02-16. + Zero, +} +const CURRENT_VERSION: DbVersion = DbVersion::Zero; + +async fn set_db_version( + tx: &mut Transaction<'_, Sqlite>, + old_version: Option<DbVersion>, + new_version: DbVersion, +) -> Result<()> { + let valid_from = get_current_date(); + + if let Some(old_version) = old_version { + let valid_to = valid_from + 1; + let old_version = old_version.as_sql_integer(); + + query!( + "UPDATE version SET valid_to = ? WHERE namespace = 'yt' AND number = ?;", + valid_to, + old_version + ) + .execute(&mut *(*tx)) + .await?; + } + + let version = new_version.as_sql_integer(); + + query!( + "INSERT INTO version (namespace, number, valid_from, valid_to) VALUES ('yt', ?, ?, NULL);", + version, + valid_from + ) + .execute(&mut *(*tx)) + .await?; + + Ok(()) +} + +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, + } + } + fn from_db(number: i64, namespace: &str) -> Result<Self> { + match (number, namespace) { + (0, "yt") => Ok(DbVersion::Zero), + + (0, other) => bail!("Db version is Zero, but got unknown namespace: '{other}'"), + + (other, "yt") => bail!("Got unkown version for 'yt' namespace: {other}"), + (num, nasp) => bail!("Got unkown version number ({num}) and namespace ('{nasp}')"), + } + } + + /// Try to update the database from version [`self`] to the [`CURRENT_VERSION`]. + /// + /// Each update is atomic, so if this function fails you are still guaranteed to have a + /// database at version `get_version`. + async fn update(self, app: &App) -> Result<()> { + match self { + DbVersion::Empty => { + let mut tx = app.database.begin().await?; + debug!("Migrate: Empty -> Zero"); + + sqlx::raw_sql(include_str!("./sql/00_empty_to_zero.sql")) + .execute(&mut *tx) + .await?; + + set_db_version(&mut tx, None, DbVersion::Zero).await?; + + tx.commit().await?; + Box::pin(Self::Zero.update(app)).await + } + + // This is the current version + DbVersion::Zero => { + debug!("Migrate: Zero -> One"); + + assert_eq!(self, CURRENT_VERSION); + assert_eq!(self, get_version(app).await?); + Ok(()) + } + } + } +} +impl Display for DbVersion { + fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result { + // It is a unit only enum, thus we can simply use the Debug formatting + <Self as std::fmt::Debug>::fmt(self, f) + } +} + +/// Returns the current data as UNIX time stamp. +fn get_current_date() -> i64 { + let start = SystemTime::now(); + let seconds_since_epoch: TimeDelta = TimeDelta::from_std( + start + .duration_since(UNIX_EPOCH) + .expect("Time went backwards"), + ) + .expect("Time does not go backwards"); + + debug!( + "Adding a date with timestamp: {}", + seconds_since_epoch.num_seconds() + ); + + // All database dates should be after the UNIX_EPOCH (and thus positiv) + seconds_since_epoch.num_seconds() +} + +/// Return the current database version. +/// +/// # Panics +/// Only if internal assertions fail. +pub async fn get_version(app: &App) -> Result<DbVersion> { + let version_table_exists = { + let query = query!( + "SELECT 1 as result FROM sqlite_master WHERE type = 'table' AND name = 'version'" + ) + .fetch_optional(&app.database) + .await?; + if let Some(output) = query { + assert_eq!(output.result, 1); + true + } else { + false + } + }; + if !version_table_exists { + return Ok(DbVersion::Empty); + } + + let current_version = query!( + " + SELECT namespace, number FROM version WHERE valid_to IS NULL; + " + ) + .fetch_one(&app.database) + .await + .context("Failed to fetch version number")?; + + DbVersion::from_db(current_version.number, current_version.namespace.as_str()) +} + +pub async fn migrate_db(app: &App) -> Result<()> { + let current_version = get_version(app).await?; + + if current_version == CURRENT_VERSION { + return Ok(()); + } + + info!("Migrate database from version '{current_version}' to version '{CURRENT_VERSION}'"); + + current_version.update(app).await?; + + Ok(()) +} diff --git a/yt/src/storage/migrate/sql/00_empty_to_zero.sql b/yt/src/storage/migrate/sql/00_empty_to_zero.sql new file mode 100644 index 0000000..2e890e5 --- /dev/null +++ b/yt/src/storage/migrate/sql/00_empty_to_zero.sql @@ -0,0 +1,71 @@ +-- 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 `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; |