aboutsummaryrefslogtreecommitdiffstats
path: root/yt/src/storage/migrate
diff options
context:
space:
mode:
Diffstat (limited to 'yt/src/storage/migrate')
-rw-r--r--yt/src/storage/migrate/mod.rs178
-rw-r--r--yt/src/storage/migrate/sql/00_empty_to_zero.sql71
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;
generated by cgit v1.3.1 (git 2.54.0) at 2026-06-23 01:34:19 +0000