From 57a2df34f4986f1f6062f22804021925afec0419 Mon Sep 17 00:00:00 2001 From: niliara-edu Date: Sat, 25 Jan 2025 20:50:21 +0100 Subject: refactor database --- src/database/album.rs | 153 +++++++++++++++++++++++++++++++++++++ src/database/artist.rs | 115 ++++++++++++++++++++++++++++ src/database/mod.rs | 34 +++++++++ src/database/search_results.rs | 36 +++++++++ src/database/song.rs | 169 +++++++++++++++++++++++++++++++++++++++++ 5 files changed, 507 insertions(+) create mode 100644 src/database/album.rs create mode 100644 src/database/artist.rs create mode 100644 src/database/mod.rs create mode 100644 src/database/search_results.rs create mode 100644 src/database/song.rs (limited to 'src/database') diff --git a/src/database/album.rs b/src/database/album.rs new file mode 100644 index 0000000..87054f1 --- /dev/null +++ b/src/database/album.rs @@ -0,0 +1,153 @@ +use crate::database::DatabaseWrapper; +use serde::{Deserialize, Serialize}; +use sqlx::mysql::MySqlQueryResult; + +#[derive(Serialize)] +pub struct Album { + name: Option, + id: Option, + cover: Option, + artist_name: Option, + artist_id: Option, +} + +#[derive(Serialize, Deserialize)] +pub struct AlbumPost { + name: Option, + cover: Option, + artist_id: Option, +} + +#[derive(Serialize, Deserialize)] +pub struct AlbumPut { + id: Option, + name: Option, + cover: Option, + artist_id: Option, +} + +impl DatabaseWrapper { + pub async fn select_albums(&self) -> Result, sqlx::Error> { + sqlx::query_as!( + Album, + "SELECT album.name, album.id, album.cover, + artist.name as artist_name, artist.id as artist_id + FROM album + INNER JOIN artist ON album.artist_id = artist.id + ", + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn select_album_by_id(&self, id: &str) -> Result, sqlx::Error> { + sqlx::query_as!( + Album, + "SELECT album.name, album.id, album.cover, + artist.name as artist_name, artist.id as artist_id + FROM album + INNER JOIN artist ON album.artist_id = artist.id + WHERE album.id=?", + id, + ) + .fetch_optional(&self.db_pool) + .await + } + + pub async fn select_albums_by_name(&self, name_raw: &str) -> Result, sqlx::Error> { + let name: String = format!("{}{}{}", "%", name_raw, "%"); + sqlx::query_as!( + Album, + "SELECT album.name, album.id, album.cover, + artist.name as artist_name, artist.id as artist_id + FROM album + INNER JOIN artist ON album.artist_id = artist.id + WHERE LOWER(album.name) LIKE LOWER(?) + ", + name, + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn select_albums_by_artist( + &self, + artist_id: &str, + ) -> Result, sqlx::Error> { + sqlx::query_as!( + Album, + "SELECT album.name, album.id, album.cover, + artist.name as artist_name, artist.id as artist_id + FROM album + INNER JOIN artist ON album.artist_id = artist.id + WHERE artist.id=?", + artist_id, + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn create_album(&self, data: AlbumPost) -> Result { + if data.name.is_none() || data.artist_id.is_none() { + return Err(sqlx::Error::RowNotFound); + } + + if match self + .select_artist_by_id(data.artist_id.as_ref().unwrap()) + .await + { + Ok(res) => res.is_none(), + Err(_) => true, + } { + return Err(sqlx::Error::RowNotFound); + } + + sqlx::query!( + "INSERT INTO album (name, cover, artist_id) + VALUE (?, ?, ?)", + data.name, + data.cover.unwrap_or(String::default()), + data.artist_id, + ) + .execute(&self.db_pool) + .await + } + + pub async fn edit_album(&self, data: AlbumPut) -> Result { + if data.id.is_none() { return Err(sqlx::Error::RowNotFound); } + let og_album: Album = match self.select_album_by_id(data.id.as_ref().unwrap()).await { + Ok(res) => match res.is_some() { + true => res.unwrap(), + false => return Err(sqlx::Error::RowNotFound), + } + Err(_) => return Err(sqlx::Error::RowNotFound), + }; + sqlx::query!( + "UPDATE album SET name=?, cover=? WHERE id=?", + data.name.unwrap_or(og_album.name.unwrap_or(String::default())), + data.cover.unwrap_or(og_album.cover.unwrap_or(String::default())), + data.id, + ) + .execute(&self.db_pool) + .await + } + + pub async fn delete_album(&self, id: i32) -> Result { + let _ = sqlx::query!( + "DELETE FROM song + WHERE album_id = ?", + id + ) + .execute(&self.db_pool) + .await; + + sqlx::query!( + "DELETE FROM album + WHERE id = ?", + id + ) + .execute(&self.db_pool) + .await + } + +} diff --git a/src/database/artist.rs b/src/database/artist.rs new file mode 100644 index 0000000..018bc17 --- /dev/null +++ b/src/database/artist.rs @@ -0,0 +1,115 @@ +use crate::database::DatabaseWrapper; +use serde::{Deserialize, Serialize}; +use sqlx::mysql::MySqlQueryResult; + +#[derive(Serialize)] +pub struct Artist { + name: Option, + id: Option, +} + +#[derive(Serialize, Deserialize)] +pub struct ArtistPost { + name: Option, +} + +#[derive(Serialize, Deserialize)] +pub struct ArtistPut { + id: Option, + name: Option, +} + +impl DatabaseWrapper { + pub async fn select_artists(&self) -> Result, sqlx::Error> { + sqlx::query_as!( + Artist, + "SELECT name, id + FROM artist", + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn select_artist_by_id(&self, id: &str) -> Result, sqlx::Error> { + sqlx::query_as!( + Artist, + "SELECT name, id + FROM artist + WHERE id = ?", + id, + ) + .fetch_optional(&self.db_pool) + .await + } + + pub async fn select_artists_by_name(&self, name_raw: &str) -> Result, sqlx::Error> { + let name: String = format!("{}{}{}", "%", name_raw, "%"); + sqlx::query_as!( + Artist, + "SELECT name, id + FROM artist + WHERE name LIKE ?", + name, + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn create_artist(&self, data: ArtistPost) -> Result { + if data.name.is_none() { + return Err(sqlx::Error::RowNotFound); + } + sqlx::query!( + "INSERT INTO artist (name) + VALUE (?)", + data.name + ) + .execute(&self.db_pool) + .await + } + + pub async fn edit_artist(&self, data: ArtistPut) -> Result { + if data.id.is_none() { return Err(sqlx::Error::RowNotFound); } + let og_artist: Artist = match self.select_artist_by_id(data.id.as_ref().unwrap()).await { + Ok(res) => match res.is_some() { + true => res.unwrap(), + false => return Err(sqlx::Error::RowNotFound), + } + Err(_) => return Err(sqlx::Error::RowNotFound), + }; + sqlx::query!( + "UPDATE artist SET name=? WHERE id=?", + data.name.unwrap_or(og_artist.name.unwrap_or(String::default())), + data.id, + ) + .execute(&self.db_pool) + .await + } + + pub async fn delete_artist(&self, id: i32) -> Result { + let _ = sqlx::query!( + "DELETE song FROM song + INNER JOIN album ON song.album_id = album.id + WHERE album.artist_id = ?", + id + ) + .execute(&self.db_pool) + .await; + + let _ = sqlx::query!( + "DELETE FROM album + WHERE artist_id = ?", + id + ) + .execute(&self.db_pool) + .await; + + sqlx::query!( + "DELETE FROM artist + WHERE id = ?", + id + ) + .execute(&self.db_pool) + .await + } +} diff --git a/src/database/mod.rs b/src/database/mod.rs new file mode 100644 index 0000000..d66b72b --- /dev/null +++ b/src/database/mod.rs @@ -0,0 +1,34 @@ +pub mod song; +pub mod album; +pub mod artist; +pub mod search_results; + +use serde::{Deserialize, Serialize}; +use sqlx::mysql::{MySqlPool, MySqlPoolOptions}; +use std::env; + +#[derive(Serialize, Deserialize)] +pub struct Delete { + pub id: Option, +} + +pub struct DatabaseWrapper { + db_pool: MySqlPool, +} + +impl DatabaseWrapper { + pub async fn new() -> Result { + let pool: MySqlPool = MySqlPoolOptions::new() + .max_connections(10) + .connect( + env::var("DATABASE_URL") + .expect("Environment variable DATABASE_URL is *probably not setted up!!") + .as_str(), + ) + .await + .unwrap(); /* This will break in case of error. It's intended. */ + + Ok(DatabaseWrapper { db_pool: pool }) + } +} + diff --git a/src/database/search_results.rs b/src/database/search_results.rs new file mode 100644 index 0000000..1468111 --- /dev/null +++ b/src/database/search_results.rs @@ -0,0 +1,36 @@ +use crate::database::DatabaseWrapper; +use crate::database::song::Song; +use crate::database::artist::Artist; +use crate::database::album::Album; + +impl DatabaseWrapper { + pub async fn search_results_by_id( + &self, + id: &str, + ) -> ( + Result, sqlx::Error>, + Result, sqlx::Error>, + Result, sqlx::Error>, + ) { + ( + self.select_artist_by_id(id).await, + self.select_album_by_id(id).await, + self.select_song_by_id(id).await, + ) + } + + pub async fn search_results_by_name( + &self, + name: &str, + ) -> ( + Result, sqlx::Error>, + Result, sqlx::Error>, + Result, sqlx::Error>, + ) { + return ( + self.select_artists_by_name(name).await, + self.select_albums_by_name(name).await, + self.select_songs_by_name(name).await, + ); + } +} diff --git a/src/database/song.rs b/src/database/song.rs new file mode 100644 index 0000000..2b45065 --- /dev/null +++ b/src/database/song.rs @@ -0,0 +1,169 @@ +use crate::database::DatabaseWrapper; +use serde::{Deserialize, Serialize}; +use sqlx::mysql::MySqlQueryResult; + +#[derive(Serialize)] +pub struct Song { + name: Option, + id: Option, + lyrics: Option, + album_name: Option, + album_id: Option, + artist_name: Option, + artist_id: Option, +} + +#[derive(Serialize, Deserialize)] +pub struct SongPost { + name: Option, + lyrics: Option, + album_id: Option, +} + +#[derive(Serialize, Deserialize)] +pub struct SongPut { + id: Option, + name: Option, + lyrics: Option, + album_id: Option, +} + +impl DatabaseWrapper { + pub async fn select_songs(&self) -> Result, sqlx::Error> { + sqlx::query_as!( + Song, + "SELECT song.name, song.lyrics, song.id, + album.name as album_name, album.id as album_id, + artist.name as artist_name, artist.id as artist_id + FROM song + INNER JOIN album ON song.album_id = album.id + INNER JOIN artist ON album.artist_id = artist.id + ", + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn select_song_by_id(&self, id: &str) -> Result, sqlx::Error> { + sqlx::query_as!( + Song, + "SELECT song.name, song.lyrics, song.id, + album.name as album_name, album.id as album_id, + artist.name as artist_name, artist.id as artist_id + FROM song + INNER JOIN album ON song.album_id = album.id + INNER JOIN artist ON album.artist_id = artist.id + WHERE song.id = ? + ", + id, + ) + .fetch_optional(&self.db_pool) + .await + } + + pub async fn select_songs_by_album(&self, album_id: &str) -> Result, sqlx::Error> { + sqlx::query_as!( + Song, + "SELECT song.name, song.lyrics, song.id, + album.name as album_name, album.id as album_id, + artist.name as artist_name, artist.id as artist_id + FROM song + INNER JOIN album ON song.album_id = album.id + INNER JOIN artist ON album.artist_id = artist.id + WHERE album.id = ? + ", + album_id, + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn select_songs_by_artist(&self, artist_id: &str) -> Result, sqlx::Error> { + sqlx::query_as!( + Song, + "SELECT song.name, song.lyrics, song.id, + album.name as album_name, album.id as album_id, + artist.name as artist_name, artist.id as artist_id + FROM song + INNER JOIN album ON song.album_id = album.id + INNER JOIN artist ON album.artist_id = artist.id + WHERE artist.id = ? + ", + artist_id, + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn select_songs_by_name(&self, name_raw: &str) -> Result, sqlx::Error> { + let name: String = format!("{}{}{}", "%", name_raw, "%"); + sqlx::query_as!( + Song, + "SELECT song.name, song.lyrics, song.id, + album.name as album_name, album.id as album_id, + artist.name as artist_name, artist.id as artist_id + FROM song + INNER JOIN album ON song.album_id = album.id + INNER JOIN artist ON album.artist_id = artist.id + WHERE LOWER(song.name) LIKE LOWER(?)", + name, + ) + .fetch_all(&self.db_pool) + .await + } + + pub async fn create_song(&self, data: SongPost) -> Result { + if data.name.is_none() || data.album_id.is_none() { + return Err(sqlx::Error::RowNotFound); + } + + if match self + .select_album_by_id(data.album_id.as_ref().unwrap()) + .await + { + Ok(res) => res.is_none(), + Err(_) => true, + } { + return Err(sqlx::Error::RowNotFound); + } + + sqlx::query!( + "INSERT INTO song (name, lyrics, album_id) + VALUE (?, ?, ?)", + data.name, + data.lyrics.unwrap_or(String::default()), + data.album_id, + ) + .execute(&self.db_pool) + .await + } + + pub async fn edit_song(&self, data: SongPut) -> Result { + if data.id.is_none() { return Err(sqlx::Error::RowNotFound); } + let og_song: Song = match self.select_song_by_id(data.id.as_ref().unwrap()).await { + Ok(res) => match res.is_some() { + true => res.unwrap(), + false => return Err(sqlx::Error::RowNotFound), + } + Err(_) => return Err(sqlx::Error::RowNotFound), + }; + sqlx::query!( + "UPDATE song SET name=?, lyrics=? WHERE id=?", + data.name.unwrap_or(og_song.name.unwrap_or(String::default())), + data.lyrics.unwrap_or(og_song.lyrics.unwrap_or(String::default())), + data.id, + ) + .execute(&self.db_pool) + .await + } + + pub async fn delete_song(&self, id: i32) -> Result { + sqlx::query!( + "DELETE FROM song + WHERE id = ?", + id + ) + .execute(&self.db_pool) + .await + } +} -- cgit v1.2.3