// 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(()) }