diff options
Diffstat (limited to 'src/storage')
| -rw-r--r-- | src/storage/mod.rs | 12 | ||||
| -rw-r--r-- | src/storage/subscriptions.rs | 140 | ||||
| -rw-r--r-- | src/storage/video_database/downloader.rs | 210 | ||||
| -rw-r--r-- | src/storage/video_database/extractor_hash.rs | 151 | ||||
| -rw-r--r-- | src/storage/video_database/getters.rs | 339 | ||||
| -rw-r--r-- | src/storage/video_database/mod.rs | 170 | ||||
| -rw-r--r-- | src/storage/video_database/schema.sql | 56 | ||||
| -rw-r--r-- | src/storage/video_database/setters.rs | 270 |
8 files changed, 1348 insertions, 0 deletions
diff --git a/src/storage/mod.rs b/src/storage/mod.rs new file mode 100644 index 0000000..6a12d8b --- /dev/null +++ b/src/storage/mod.rs @@ -0,0 +1,12 @@ +// 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>. + +pub mod subscriptions; +pub mod video_database; diff --git a/src/storage/subscriptions.rs b/src/storage/subscriptions.rs new file mode 100644 index 0000000..22edd08 --- /dev/null +++ b/src/storage/subscriptions.rs @@ -0,0 +1,140 @@ +// 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>. + +//! Handle subscriptions + +use std::collections::HashMap; + +use anyhow::Result; +use log::debug; +use serde_json::{json, Value}; +use sqlx::query; +use url::Url; +use yt_dlp::wrapper::info_json::InfoType; + +use crate::app::App; + +#[derive(Clone, Debug)] +pub struct Subscription { + /// The human readable name of this subscription + pub name: String, + + /// The URL this subscription subscribes to + pub url: Url, +} + +impl Subscription { + pub fn new(name: String, url: Url) -> Self { + Self { name, url } + } +} + +/// Check whether an URL could be used as a subscription URL +pub async fn check_url(url: &Url) -> Result<bool> { + let yt_opts = match json!( { + "playliststart": 1, + "playlistend": 10, + "noplaylist": false, + "extract_flat": "in_playlist", + }) { + Value::Object(map) => map, + _ => unreachable!("This is hardcoded"), + }; + + let info = yt_dlp::extract_info(&yt_opts, url, false, false).await?; + + debug!("{:#?}", info); + + Ok(info._type == Some(InfoType::Playlist)) +} + +#[derive(Default)] +pub struct Subscriptions(pub(crate) HashMap<String, Subscription>); + +pub async fn remove_all_subscriptions(app: &App) -> Result<()> { + query!( + " + DELETE FROM subscriptions; + ", + ) + .execute(&app.database) + .await?; + + Ok(()) +} + +/// Get a list of subscriptions +pub async fn get_subscriptions(app: &App) -> Result<Subscriptions> { + let raw_subs = query!( + " + SELECT * + FROM subscriptions; + " + ) + .fetch_all(&app.database) + .await?; + + let subscriptions: HashMap<String, Subscription> = raw_subs + .into_iter() + .map(|sub| { + ( + sub.name.clone(), + Subscription::new( + sub.name, + Url::parse(&sub.url).expect("This should be valid"), + ), + ) + }) + .collect(); + + Ok(Subscriptions(subscriptions)) +} + +pub async fn add_subscription(app: &App, sub: &Subscription) -> Result<()> { + let url = sub.url.to_string(); + + query!( + " + INSERT INTO subscriptions ( + name, + url + ) VALUES (?, ?); + ", + sub.name, + url + ) + .execute(&app.database) + .await?; + + println!("Subscribed to '{}' at '{}'", sub.name, sub.url); + Ok(()) +} + +pub async fn remove_subscription(app: &App, sub: &Subscription) -> Result<()> { + let output = query!( + " + DELETE FROM subscriptions + WHERE name = ? + ", + sub.name, + ) + .execute(&app.database) + .await?; + + assert_eq!( + output.rows_affected(), + 1, + "The remove subscriptino query did effect more (or less) than one row. This is a bug." + ); + + println!("Unsubscribed from '{}' at '{}'", sub.name, sub.url); + + Ok(()) +} diff --git a/src/storage/video_database/downloader.rs b/src/storage/video_database/downloader.rs new file mode 100644 index 0000000..c04ab8d --- /dev/null +++ b/src/storage/video_database/downloader.rs @@ -0,0 +1,210 @@ +// 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>. + +use std::path::{Path, PathBuf}; + +use anyhow::Result; +use log::debug; +use sqlx::query; +use url::Url; + +use crate::{app::App, storage::video_database::VideoStatus}; + +use super::{ExtractorHash, Video}; + +/// Returns to next video which should be downloaded. This respects the priority assigned by select. +/// It does not return videos, which are already cached. +pub async fn get_next_uncached_video(app: &App) -> Result<Option<Video>> { + let status = VideoStatus::Watch.as_db_integer(); + + let result = query!( + r#" + SELECT * + FROM videos + WHERE status = ? AND cache_path IS NULL + ORDER BY priority ASC + LIMIT 1; + "#, + status + ) + .fetch_one(&app.database) + .await; + + if let Err(sqlx::Error::RowNotFound) = result { + Ok(None) + } else { + let base = result?; + + let thumbnail_url = if let Some(url) = &base.thumbnail_url { + Some(Url::parse(&url)?) + } else { + None + }; + + let status_change = if base.status_change == 1 { + true + } else { + assert_eq!(base.status_change, 0, "Can only be 1 or 0"); + false + }; + + let video = Video { + cache_path: base.cache_path.as_ref().map(|val| PathBuf::from(val)), + description: base.description.clone(), + duration: base.duration, + extractor_hash: ExtractorHash::from_hash( + base.extractor_hash + .parse() + .expect("The hash in the db should be valid"), + ), + last_status_change: base.last_status_change, + parent_subscription_name: base.parent_subscription_name.clone(), + priority: base.priority, + publish_date: base.publish_date, + status: VideoStatus::from_db_integer(base.status), + status_change, + thumbnail_url, + title: base.title.clone(), + url: Url::parse(&base.url)?, + }; + + Ok(Some(video)) + } +} + +/// Returns to next video which can be watched (i.e. is cached). +/// This respects the priority assigned by select. +pub async fn get_next_video_watchable(app: &App) -> Result<Option<Video>> { + let result = query!( + r#" + SELECT * + FROM videos + WHERE status = 'Watching' AND cache_path IS NOT NULL + ORDER BY priority ASC + LIMIT 1; + "# + ) + .fetch_one(&app.database) + .await; + + if let Err(sqlx::Error::RowNotFound) = result { + Ok(None) + } else { + let base = result?; + + let thumbnail_url = if let Some(url) = &base.thumbnail_url { + Some(Url::parse(&url)?) + } else { + None + }; + + let status_change = if base.status_change == 1 { + true + } else { + assert_eq!(base.status_change, 0, "Can only be 1 or 0"); + false + }; + + let video = Video { + cache_path: base.cache_path.as_ref().map(|val| PathBuf::from(val)), + description: base.description.clone(), + duration: base.duration, + extractor_hash: ExtractorHash::from_hash( + base.extractor_hash + .parse() + .expect("The db extractor_hash should be valid blake3 hash"), + ), + last_status_change: base.last_status_change, + parent_subscription_name: base.parent_subscription_name.clone(), + priority: base.priority, + publish_date: base.publish_date, + status: VideoStatus::from_db_integer(base.status), + status_change, + thumbnail_url, + title: base.title.clone(), + url: Url::parse(&base.url)?, + }; + + Ok(Some(video)) + } +} + +/// Update the cached path of a video. Will be set to NULL if the path is None +/// This will also set the status to `Cached` when path is Some, otherwise it set's the status to +/// `Watch`. +pub async fn set_video_cache_path( + app: &App, + video: &ExtractorHash, + path: Option<&Path>, +) -> Result<()> { + if let Some(path) = path { + debug!( + "Setting cache path from '{}' to '{}'", + video.into_short_hash(app).await?, + path.display() + ); + + let path_str = path.display().to_string(); + let extractor_hash = video.hash().to_string(); + let status = VideoStatus::Cached.as_db_integer(); + + query!( + r#" + UPDATE videos + SET cache_path = ?, status = ? + WHERE extractor_hash = ?; + "#, + path_str, + status, + extractor_hash + ) + .execute(&app.database) + .await?; + + Ok(()) + } else { + debug!( + "Setting cache path from '{}' to NULL", + video.into_short_hash(app).await?, + ); + + let extractor_hash = video.hash().to_string(); + let status = VideoStatus::Watch.as_db_integer(); + + query!( + r#" + UPDATE videos + SET cache_path = NULL, status = ? + WHERE extractor_hash = ?; + "#, + status, + extractor_hash + ) + .execute(&app.database) + .await?; + + Ok(()) + } +} + +/// Returns the number of cached videos +pub async fn get_allocated_cache(app: &App) -> Result<u32> { + let count = query!( + r#" + SELECT COUNT(cache_path) as count + FROM videos + WHERE cache_path IS NOT NULL; +"#, + ) + .fetch_one(&app.database) + .await?; + + Ok(count.count as u32) +} diff --git a/src/storage/video_database/extractor_hash.rs b/src/storage/video_database/extractor_hash.rs new file mode 100644 index 0000000..3af4f60 --- /dev/null +++ b/src/storage/video_database/extractor_hash.rs @@ -0,0 +1,151 @@ +// 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>. + +use std::{collections::HashMap, fmt::Display, str::FromStr}; + +use anyhow::{bail, Result}; +use blake3::Hash; +use log::debug; +use tokio::sync::OnceCell; + +use crate::{app::App, storage::video_database::getters::get_all_hashes}; + +static EXTRACTOR_HASH_LENGTH: OnceCell<usize> = OnceCell::const_new(); + +#[derive(Debug, Clone, PartialEq, Eq)] +pub struct ExtractorHash { + hash: Hash, +} + +#[derive(Debug, Clone)] +pub struct ShortHash(String); + +impl Display for ShortHash { + fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result { + self.0.fmt(f) + } +} + +#[derive(Debug, Clone)] +pub struct LazyExtractorHash { + value: ShortHash, +} + +impl FromStr for LazyExtractorHash { + type Err = anyhow::Error; + + fn from_str(s: &str) -> std::result::Result<Self, Self::Err> { + // perform some cheap validation + if s.len() > 64 { + bail!("A hash can only contain 64 bytes!"); + } + + Ok(Self { + value: ShortHash(s.to_owned()), + }) + } +} + +impl LazyExtractorHash { + /// Turn the [`LazyExtractorHash`] into the [`ExtractorHash`] + pub async fn realize(self, app: &App) -> Result<ExtractorHash> { + ExtractorHash::from_short_hash(app, &self.value).await + } +} + +impl ExtractorHash { + pub fn from_hash(hash: Hash) -> Self { + Self { hash } + } + pub async fn from_short_hash(app: &App, s: &ShortHash) -> Result<Self> { + Ok(Self { + hash: Self::short_hash_to_full_hash(app, s).await?, + }) + } + + pub fn hash(&self) -> &Hash { + &self.hash + } + + pub async fn into_short_hash(&self, app: &App) -> Result<ShortHash> { + let needed_chars = if let Some(needed_chars) = EXTRACTOR_HASH_LENGTH.get() { + debug!("Using cached char length: {}", needed_chars); + *needed_chars + } else { + let needed_chars = self.get_needed_char_len(app).await?; + debug!("Setting the needed has char lenght."); + EXTRACTOR_HASH_LENGTH + .set(needed_chars) + .expect("This should work at this stage"); + + needed_chars + }; + + debug!("Formatting a hash with char length: {}", needed_chars); + + Ok(ShortHash( + self.hash() + .to_hex() + .chars() + .into_iter() + .take(needed_chars) + .collect::<String>(), + )) + } + + async fn short_hash_to_full_hash(app: &App, s: &ShortHash) -> Result<Hash> { + let all_hashes = get_all_hashes(app).await?; + + let needed_chars = s.0.len(); + + for hash in all_hashes { + if &hash.to_hex()[..needed_chars] == s.0 { + return Ok(hash); + } + } + + bail!("Your shortend hash, does not match a real hash (this is probably a bug)!"); + } + + async fn get_needed_char_len(&self, app: &App) -> Result<usize> { + debug!("Calculating the needed hash char length"); + let all_hashes = get_all_hashes(app).await?; + + let all_char_vec_hashes = all_hashes + .into_iter() + .map(|hash| hash.to_hex().chars().collect::<Vec<char>>()) + .collect::<Vec<Vec<_>>>(); + + // This value should be updated later, if not rust will panic in the assertion. + let mut needed_chars: usize = 1000; + 'outer: for i in 1..64 { + let i_chars: Vec<String> = all_char_vec_hashes + .iter() + .map(|vec| vec.iter().take(i).collect::<String>()) + .collect(); + + let mut uniqnes_hashmap: HashMap<String, ()> = HashMap::new(); + for ch in i_chars { + if let Some(()) = uniqnes_hashmap.insert(ch, ()) { + // The key was already in the hash map, thus we have a duplicated char and need + // at least one char more + continue 'outer; + } + } + + needed_chars = i; + break 'outer; + } + + assert!(needed_chars <= 64, "Hashes are only 64 bytes long"); + + Ok(needed_chars) + } +} diff --git a/src/storage/video_database/getters.rs b/src/storage/video_database/getters.rs new file mode 100644 index 0000000..ca4164d --- /dev/null +++ b/src/storage/video_database/getters.rs @@ -0,0 +1,339 @@ +// 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>. + +//! These functions interact with the storage db in a read-only way. They are added on-demaned (as +//! you could theoretically just could do everything with the `get_videos` function), as +//! performance or convince requires. +use std::{fs::File, path::PathBuf}; + +use anyhow::{bail, Context, Result}; +use blake3::Hash; +use log::debug; +use sqlx::{query, QueryBuilder, Row, Sqlite}; +use url::Url; +use yt_dlp::wrapper::info_json::InfoJson; + +use crate::{ + app::App, + storage::{ + subscriptions::Subscription, + video_database::{extractor_hash::ExtractorHash, Video}, + }, +}; + +use super::{MpvOptions, VideoOptions, VideoStatus, YtDlpOptions}; + +macro_rules! video_from_record { + ($record:expr) => { + let thumbnail_url = if let Some(url) = &$record.thumbnail_url { + Some(Url::parse(&url)?) + } else { + None + }; + + Ok(Video { + cache_path: $record.cache_path.as_ref().map(|val| PathBuf::from(val)), + description: $record.description.clone(), + duration: $record.duration, + extractor_hash: ExtractorHash::from_hash( + $record + .extractor_hash + .parse() + .expect("The db hash should be a valid blake3 hash"), + ), + last_status_change: $record.last_status_change, + parent_subscription_name: $record.parent_subscription_name.clone(), + publish_date: $record.publish_date, + status: VideoStatus::from_db_integer($record.status), + thumbnail_url, + title: $record.title.clone(), + url: Url::parse(&$record.url)?, + priority: $record.priority, + status_change: if $record.status_change == 1 { + true + } else { + assert_eq!($record.status_change, 0); + false + }, + }) + }; +} + +/// Get the lines to display at the selection file +/// [`changing` = true]: Means that we include *only* videos, that have the `status_changing` flag set +/// [`changing` = None]: Means that we include *both* videos, that have the `status_changing` flag set and not set +pub async fn get_videos( + app: &App, + allowed_states: &[VideoStatus], + changing: Option<bool>, +) -> Result<Vec<Video>> { + let mut qb: QueryBuilder<Sqlite> = QueryBuilder::new( + "\ + SELECT * + FROM videos + WHERE status IN ", + ); + + qb.push("("); + allowed_states + .iter() + .enumerate() + .for_each(|(index, state)| { + qb.push("'"); + qb.push(state.as_db_integer()); + qb.push("'"); + + if index != allowed_states.len() - 1 { + qb.push(","); + } + }); + qb.push(")"); + + if let Some(val) = changing { + if val { + qb.push(" AND status_change = 1"); + } else { + qb.push(" AND status_change = 0"); + } + } + + qb.push("\n ORDER BY priority DESC;"); + + debug!("Will run: \"{}\"", qb.sql()); + + let videos = qb.build().fetch_all(&app.database).await.with_context(|| { + format!( + "Failed to query videos with states: '{}'", + allowed_states.iter().fold(String::new(), |mut acc, state| { + acc.push(' '); + acc.push_str(&state.as_str()); + acc + }), + ) + })?; + + let real_videos: Vec<Video> = videos + .iter() + .map(|base| -> Result<Video> { + let thumbnail_url = if let Some(url) = base.get("thumbnail_url") { + Some(Url::parse(url)?) + } else { + None + }; + Ok(Video { + cache_path: base + .get::<Option<String>, &str>("cache_path") + .as_ref() + .map(|val| PathBuf::from(val)), + description: base.get::<Option<String>, &str>("description").clone(), + duration: base.get("duration"), + extractor_hash: ExtractorHash::from_hash( + base.get::<String, &str>("extractor_hash") + .parse() + .expect("The db hash should be a valid blake3 hash"), + ), + last_status_change: base.get("last_status_change"), + parent_subscription_name: base + .get::<Option<String>, &str>("parent_subscription_name") + .clone(), + publish_date: base.get("publish_date"), + status: VideoStatus::from_db_integer(base.get("status")), + thumbnail_url, + title: base.get::<String, &str>("title").to_owned(), + url: Url::parse(base.get("url"))?, + priority: base.get("priority"), + status_change: { + let val = base.get::<i64, &str>("status_change"); + if val == 1 { + true + } else { + assert_eq!(val, 0, "Can only be 1 or 0"); + false + } + }, + }) + }) + .collect::<Result<Vec<Video>>>()?; + + Ok(real_videos) +} + +pub async fn get_video_info_json(video: &Video) -> Result<Option<InfoJson>> { + if let Some(mut path) = video.cache_path.clone() { + if !path.set_extension("info.json") { + bail!( + "Failed to change path extension to 'info.json': {}", + path.display() + ); + } + let info_json_string = File::open(path)?; + let info_json: InfoJson = serde_json::from_reader(&info_json_string)?; + + Ok(Some(info_json)) + } else { + Ok(None) + } +} + +pub async fn get_video_by_hash(app: &App, hash: &ExtractorHash) -> Result<Video> { + let ehash = hash.hash().to_string(); + + let raw_video = query!( + " + SELECT * FROM videos WHERE extractor_hash = ?; + ", + ehash + ) + .fetch_one(&app.database) + .await?; + + video_from_record! {raw_video} +} + +pub async fn get_currently_playing_video(app: &App) -> Result<Option<Video>> { + let mut videos: Vec<Video> = get_changing_videos(app, VideoStatus::Cached).await?; + + if videos.is_empty() { + Ok(None) + } else { + assert_eq!( + videos.len(), + 1, + "Only one video can change from cached to watched at once!" + ); + + Ok(Some(videos.remove(0))) + } +} + +pub async fn get_changing_videos(app: &App, old_state: VideoStatus) -> Result<Vec<Video>> { + let status = old_state.as_db_integer(); + + let matching = query!( + r#" + SELECT * + FROM videos + WHERE status_change = 1 AND status = ?; + "#, + status + ) + .fetch_all(&app.database) + .await?; + + let real_videos: Vec<Video> = matching + .iter() + .map(|base| -> Result<Video> { + video_from_record! {base} + }) + .collect::<Result<Vec<Video>>>()?; + + Ok(real_videos) +} + +pub async fn get_all_hashes(app: &App) -> Result<Vec<Hash>> { + let hashes_hex = query!( + r#" + SELECT extractor_hash + FROM videos; + "# + ) + .fetch_all(&app.database) + .await?; + + Ok(hashes_hex + .iter() + .map(|hash| { + Hash::from_hex(&hash.extractor_hash) + .expect("These values started as blake3 hashes, they should stay blake3 hashes") + }) + .collect()) +} + +pub async fn get_video_hashes(app: &App, subs: &Subscription) -> Result<Vec<Hash>> { + let hashes_hex = query!( + r#" + SELECT extractor_hash + FROM videos + WHERE parent_subscription_name = ?; + "#, + subs.name + ) + .fetch_all(&app.database) + .await?; + + Ok(hashes_hex + .iter() + .map(|hash| { + Hash::from_hex(&hash.extractor_hash) + .expect("These values started as blake3 hashes, they should stay blake3 hashes") + }) + .collect()) +} + +pub async fn get_video_yt_dlp_opts(app: &App, hash: &ExtractorHash) -> Result<YtDlpOptions> { + let ehash = hash.hash().to_string(); + + let yt_dlp_options = query!( + r#" + SELECT subtitle_langs + FROM video_options + WHERE extractor_hash = ?; + "#, + ehash + ) + .fetch_one(&app.database) + .await?; + + Ok(YtDlpOptions { + subtitle_langs: yt_dlp_options.subtitle_langs, + }) +} +pub async fn get_video_mpv_opts(app: &App, hash: &ExtractorHash) -> Result<MpvOptions> { + let ehash = hash.hash().to_string(); + + let mpv_options = query!( + r#" + SELECT playback_speed + FROM video_options + WHERE extractor_hash = ?; + "#, + ehash + ) + .fetch_one(&app.database) + .await?; + + Ok(MpvOptions { + playback_speed: mpv_options.playback_speed, + }) +} + +pub async fn get_video_opts(app: &App, hash: &ExtractorHash) -> Result<VideoOptions> { + let ehash = hash.hash().to_string(); + + let opts = query!( + r#" + SELECT playback_speed, subtitle_langs + FROM video_options + WHERE extractor_hash = ?; + "#, + ehash + ) + .fetch_one(&app.database) + .await?; + + let mpv = MpvOptions { + playback_speed: opts.playback_speed, + }; + let yt_dlp = YtDlpOptions { + subtitle_langs: opts.subtitle_langs, + }; + + Ok(VideoOptions { mpv, yt_dlp }) +} diff --git a/src/storage/video_database/mod.rs b/src/storage/video_database/mod.rs new file mode 100644 index 0000000..28263ca --- /dev/null +++ b/src/storage/video_database/mod.rs @@ -0,0 +1,170 @@ +// 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>. + +use std::{fmt::Write, path::PathBuf}; + +use url::Url; + +use crate::{ + constants::{DEFAULT_MPV_PLAYBACK_SPEED, DEFAULT_SUBTITLE_LANGS}, + storage::video_database::extractor_hash::ExtractorHash, +}; + +pub mod downloader; +pub mod extractor_hash; +pub mod getters; +pub mod setters; + +#[derive(Debug)] +pub struct Video { + pub cache_path: Option<PathBuf>, + pub description: Option<String>, + pub duration: Option<f64>, + pub extractor_hash: ExtractorHash, + pub last_status_change: i64, + /// The associated subscription this video was fetched from (null, when the video was `add`ed) + pub parent_subscription_name: Option<String>, + pub priority: i64, + pub publish_date: Option<i64>, + pub status: VideoStatus, + /// The video is currently changing its state (for example from being `SELECT` to being `CACHE`) + pub status_change: bool, + pub thumbnail_url: Option<Url>, + pub title: String, + pub url: Url, +} + +#[derive(Debug)] +pub struct VideoOptions { + pub yt_dlp: YtDlpOptions, + pub mpv: MpvOptions, +} +impl VideoOptions { + pub(crate) fn new(subtitle_langs: String, playback_speed: f64) -> Self { + let yt_dlp = YtDlpOptions { subtitle_langs }; + let mpv = MpvOptions { playback_speed }; + Self { yt_dlp, mpv } + } + + /// This will write out the options that are different from the defaults. + /// Beware, that this does not set the priority. + pub fn to_cli_flags(self) -> String { + let mut f = String::new(); + + if self.mpv.playback_speed != DEFAULT_MPV_PLAYBACK_SPEED { + write!(f, " --speed '{}'", self.mpv.playback_speed).expect("Works"); + } + if self.yt_dlp.subtitle_langs != DEFAULT_SUBTITLE_LANGS { + write!(f, " --subtitle-langs '{}'", self.yt_dlp.subtitle_langs).expect("Works"); + } + + f.trim().to_owned() + } +} + +#[derive(Debug)] +/// Additionally settings passed to mpv on watch +pub struct MpvOptions { + /// The playback speed. (1 is 100%, 2.7 is 270%, and so on) + pub playback_speed: f64, +} + +#[derive(Debug)] +/// Additionally configuration options, passed to yt-dlp on download +pub struct YtDlpOptions { + /// In the form of `lang1,lang2,lang3` (e.g. `en,de,sv`) + pub subtitle_langs: String, +} + +/// # Video Lifetime (words in <brackets> are commands): +/// <Pick> +/// / \ +/// <Watch> <Drop> -> Dropped // yt select +/// | +/// Cache // yt cache +/// | +/// Watched // yt watch +#[derive(Default, Debug, PartialEq, Eq, PartialOrd, Ord)] +pub enum VideoStatus { + #[default] + Pick, + + /// The video has been select to be watched + Watch, + /// The video has been cached and is ready to be watched + Cached, + /// The video has been watched + Watched, + + /// The video has been select to be dropped + Drop, + /// The video has been dropped + Dropped, +} + +impl VideoStatus { + pub fn as_command(&self) -> &str { + // NOTE: Keep the serialize able variants synced with the main `select` function <2024-06-14> + match self { + VideoStatus::Pick => "pick", + + VideoStatus::Watch => "watch", + VideoStatus::Cached => "watch", + VideoStatus::Watched => "watch", + + VideoStatus::Drop => "drop", + VideoStatus::Dropped => "drop", + } + } + + pub fn as_db_integer(&self) -> i64 { + // These numbers should not change their mapping! + // Oh, and keep them in sync with the SQLite check constraint. + match self { + VideoStatus::Pick => 0, + + VideoStatus::Watch => 1, + VideoStatus::Cached => 2, + VideoStatus::Watched => 3, + + VideoStatus::Drop => 4, + VideoStatus::Dropped => 5, + } + } + pub fn from_db_integer(num: i64) -> Self { + match num { + 0 => Self::Pick, + + 1 => Self::Watch, + 2 => Self::Cached, + 3 => Self::Watched, + + 4 => Self::Drop, + 5 => Self::Dropped, + other => unreachable!( + "The database returned a enum discriminator, unknown to us: '{}'", + other + ), + } + } + + pub fn as_str(&self) -> &'static str { + match self { + VideoStatus::Pick => "Pick", + + VideoStatus::Watch => "Watch", + VideoStatus::Cached => "Cache", + VideoStatus::Watched => "Watched", + + VideoStatus::Drop => "Drop", + VideoStatus::Dropped => "Dropped", + } + } +} 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 +); diff --git a/src/storage/video_database/setters.rs b/src/storage/video_database/setters.rs new file mode 100644 index 0000000..ec5a5e1 --- /dev/null +++ b/src/storage/video_database/setters.rs @@ -0,0 +1,270 @@ +// 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>. + +//! These functions change the database. They are added on a demand basis. + +use anyhow::Result; +use chrono::Utc; +use log::debug; +use sqlx::query; +use tokio::fs; + +use crate::{app::App, constants, storage::video_database::extractor_hash::ExtractorHash}; + +use super::{Video, VideoOptions, VideoStatus}; + +/// Set a new status for a video. +/// This will only update the status time stamp/priority when the status or the priority has changed . +pub async fn set_video_status( + app: &App, + video_hash: &ExtractorHash, + new_status: VideoStatus, + new_priority: Option<i64>, +) -> Result<()> { + let video_hash = video_hash.hash().to_string(); + + let old = query!( + r#" + SELECT status, priority, cache_path + FROM videos + WHERE extractor_hash = ? + "#, + video_hash + ) + .fetch_one(&app.database) + .await?; + + let cache_path = if (VideoStatus::from_db_integer(old.status) == VideoStatus::Cached) + && (new_status != VideoStatus::Cached) + { + None + } else { + old.cache_path.as_deref() + }; + + let new_status = new_status.as_db_integer(); + + if let Some(new_priority) = new_priority { + if old.status == new_status && old.priority == new_priority { + return Ok(()); + } + + let now = Utc::now().timestamp(); + + debug!( + "Running status change: {:#?} -> {:#?}...", + VideoStatus::from_db_integer(old.status), + VideoStatus::from_db_integer(new_status), + ); + + query!( + r#" + UPDATE videos + SET status = ?, last_status_change = ?, priority = ?, cache_path = ? + WHERE extractor_hash = ?; + "#, + new_status, + now, + new_priority, + cache_path, + video_hash + ) + .execute(&app.database) + .await?; + } else { + if old.status == new_status { + return Ok(()); + } + + let now = Utc::now().timestamp(); + + debug!( + "Running status change: {:#?} -> {:#?}...", + VideoStatus::from_db_integer(old.status), + VideoStatus::from_db_integer(new_status), + ); + + query!( + r#" + UPDATE videos + SET status = ?, last_status_change = ?, cache_path = ? + WHERE extractor_hash = ?; + "#, + new_status, + now, + cache_path, + video_hash + ) + .execute(&app.database) + .await?; + } + + debug!("Finished status change."); + Ok(()) +} + +/// Mark a video as watched. +/// This will both set the status to `Watched` and the cache_path to Null. +pub async fn set_video_watched(app: &App, video: &Video) -> Result<()> { + let video_hash = video.extractor_hash.hash().to_string(); + let new_status = VideoStatus::Watched.as_db_integer(); + + let old = query!( + r#" + SELECT status, priority + FROM videos + WHERE extractor_hash = ? + "#, + video_hash + ) + .fetch_one(&app.database) + .await?; + + if old.status == new_status { + return Ok(()); + } + + let now = Utc::now().timestamp(); + + if let Some(path) = &video.cache_path { + if let Ok(true) = path.try_exists() { + fs::remove_file(path).await? + } + } + + query!( + r#" + UPDATE videos + SET status = ?, last_status_change = ?, cache_path = NULL + WHERE extractor_hash = ?; + "#, + new_status, + now, + video_hash + ) + .execute(&app.database) + .await?; + + Ok(()) +} + +pub async fn set_state_change( + app: &App, + video_extractor_hash: &ExtractorHash, + changing: bool, +) -> Result<()> { + let state_change = if changing { 1 } else { 0 }; + let video_extractor_hash = video_extractor_hash.hash().to_string(); + + query!( + r#" + UPDATE videos + SET status_change = ? + WHERE extractor_hash = ?; + "#, + state_change, + video_extractor_hash, + ) + .execute(&app.database) + .await?; + + Ok(()) +} + +pub async fn set_video_options( + app: &App, + hash: ExtractorHash, + video_options: &VideoOptions, +) -> Result<()> { + let video_extractor_hash = hash.hash().to_string(); + let playback_speed = video_options.mpv.playback_speed; + let subtitle_langs = &video_options.yt_dlp.subtitle_langs; + + query!( + r#" + UPDATE video_options + SET playback_speed = ?, subtitle_langs = ? + WHERE extractor_hash = ?; + "#, + playback_speed, + subtitle_langs, + video_extractor_hash, + ) + .execute(&app.database) + .await?; + + Ok(()) +} + +pub async fn add_video(app: &App, video: Video) -> Result<()> { + let parent_subscription_name = if let Some(subs) = video.parent_subscription_name { + subs + } else { + "NULL".to_owned() + }; + + let thumbnail_url = if let Some(thum) = video.thumbnail_url { + thum.to_string() + } else { + "NULL".to_owned() + }; + + let status = video.status.as_db_integer(); + let status_change = if video.status_change { 1 } else { 0 }; + let url = video.url.to_string(); + let extractor_hash = video.extractor_hash.hash().to_string(); + + let default_subtitle_langs = constants::DEFAULT_SUBTITLE_LANGS; + let default_mpv_playback_speed = constants::DEFAULT_MPV_PLAYBACK_SPEED; + + query!( + r#" + BEGIN; + INSERT INTO videos ( + parent_subscription_name, + status, + status_change, + last_status_change, + title, + url, + description, + duration, + publish_date, + thumbnail_url, + extractor_hash) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); + + INSERT INTO video_options ( + extractor_hash, + subtitle_langs, + playback_speed) + VALUES (?, ?, ?); + COMMIT; + "#, + parent_subscription_name, + status, + status_change, + video.last_status_change, + video.title, + url, + video.description, + video.duration, + video.publish_date, + thumbnail_url, + extractor_hash, + extractor_hash, + default_subtitle_langs, + default_mpv_playback_speed + ) + .execute(&app.database) + .await?; + + Ok(()) +} |