diff options
| -rwxr-xr-x | scripts/mkdb.sh | 5 | ||||
| -rw-r--r-- | yt/src/app.rs | 18 | ||||
| -rw-r--r-- | yt/src/storage/migrate/mod.rs | 178 | ||||
| -rw-r--r-- | yt/src/storage/migrate/sql/00_empty_to_zero.sql (renamed from yt/src/storage/video_database/schema.sql) | 20 | ||||
| -rw-r--r-- | yt/src/storage/mod.rs | 1 |
5 files changed, 210 insertions, 12 deletions
diff --git a/scripts/mkdb.sh b/scripts/mkdb.sh index afd94b3..6bcebaf 100755 --- a/scripts/mkdb.sh +++ b/scripts/mkdb.sh @@ -16,6 +16,9 @@ db="$root/target/database.sqlx" [ -f "$db" ] && rm "$db" [ -d "$root/target" ] || mkdir "$root/target" -sqlite3 "$db" <"$root/yt/src/storage/video_database/schema.sql" +fd . "$root/yt/src/storage/migrate/sql" | while read -r sql_file; do + echo "Applying sql migration file: $(basename "$sql_file").." + sqlite3 "$db" <"$sql_file" +done # vim: ft=sh diff --git a/yt/src/app.rs b/yt/src/app.rs index b9338af..937e58f 100644 --- a/yt/src/app.rs +++ b/yt/src/app.rs @@ -9,9 +9,9 @@ // If not, see <https://www.gnu.org/licenses/gpl-3.0.txt>. use anyhow::{Context, Result}; -use sqlx::{SqlitePool, query, sqlite::SqliteConnectOptions}; +use sqlx::{SqlitePool, sqlite::SqliteConnectOptions}; -use crate::config::Config; +use crate::{config::Config, storage::migrate::migrate_db}; #[derive(Debug)] pub struct App { @@ -30,13 +30,15 @@ impl App { .await .context("Failed to connect to database!")?; - query(include_str!("storage/video_database/schema.sql")) - .execute(&pool) - .await?; - - Ok(App { + let app = App { database: pool, config, - }) + }; + + migrate_db(&app) + .await + .context("Failed to migrate db to new version")?; + + Ok(app) } } 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/video_database/schema.sql b/yt/src/storage/migrate/sql/00_empty_to_zero.sql index 3afd091..2e890e5 100644 --- a/yt/src/storage/video_database/schema.sql +++ b/yt/src/storage/migrate/sql/00_empty_to_zero.sql @@ -11,8 +11,22 @@ -- 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 IF NOT EXISTS videos ( +CREATE TABLE videos ( cache_path TEXT UNIQUE CHECK (CASE WHEN cache_path IS NOT NULL THEN status == 2 ELSE @@ -43,7 +57,7 @@ CREATE TABLE IF NOT EXISTS videos ( ) STRICT; -- Store additional metadata for the videos marked to be watched -CREATE TABLE IF NOT EXISTS video_options ( +CREATE TABLE video_options ( extractor_hash TEXT UNIQUE NOT NULL PRIMARY KEY, subtitle_langs TEXT NOT NULL, playback_speed REAL NOT NULL, @@ -51,7 +65,7 @@ CREATE TABLE IF NOT EXISTS video_options ( ) STRICT; -- Store subscriptions -CREATE TABLE IF NOT EXISTS subscriptions ( +CREATE TABLE subscriptions ( name TEXT UNIQUE NOT NULL PRIMARY KEY, url TEXT NOT NULL ) STRICT; diff --git a/yt/src/storage/mod.rs b/yt/src/storage/mod.rs index 6a12d8b..d945d0d 100644 --- a/yt/src/storage/mod.rs +++ b/yt/src/storage/mod.rs @@ -10,3 +10,4 @@ pub mod subscriptions; pub mod video_database; +pub mod migrate; |