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/api/album.rs | 3 +- src/api/artist.rs | 3 +- src/api/search_results.rs | 12 +- src/api/song.rs | 3 +- src/database.rs | 493 ----------------------------------------- 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 ++++++++++++++ 10 files changed, 521 insertions(+), 500 deletions(-) delete mode 100644 src/database.rs 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') diff --git a/src/api/album.rs b/src/api/album.rs index b395010..2c6dfbb 100644 --- a/src/api/album.rs +++ b/src/api/album.rs @@ -1,5 +1,6 @@ use crate::api::{get_response_from_query, Response}; -use crate::database::{Album, AlbumPost, AlbumPut, Delete}; +use crate::database::Delete; +use crate::database::album::{Album, AlbumPost, AlbumPut}; use crate::extractors::auth_token::AuthenticationToken; use crate::AppState; use actix_web::{delete, get, post, put, web, HttpResponse}; diff --git a/src/api/artist.rs b/src/api/artist.rs index 6cc0f35..e8e5009 100644 --- a/src/api/artist.rs +++ b/src/api/artist.rs @@ -1,5 +1,6 @@ use crate::api::{get_response_from_query, Response}; -use crate::database::{Artist, ArtistPost, ArtistPut, Delete}; +use crate::database::Delete; +use crate::database::artist::{Artist, ArtistPost, ArtistPut}; use crate::extractors::auth_token::AuthenticationToken; use crate::AppState; use actix_web::{delete, get, post, put, web, HttpResponse}; diff --git a/src/api/search_results.rs b/src/api/search_results.rs index 585ba2c..8f01e56 100644 --- a/src/api/search_results.rs +++ b/src/api/search_results.rs @@ -1,4 +1,6 @@ -use crate::database::{Album, Artist, Song}; +use crate::database::album::Album; +use crate::database::artist::Artist; +use crate::database::song::Song; use crate::AppState; use actix_web::{get, web, HttpResponse}; use serde::Deserialize; @@ -42,9 +44,11 @@ pub async fn search_results( .search_results_by_name(&get_args.name.clone().unwrap()) .await } - _ => app_state.database.search_results().await, // Err(sqlx::Error::RowNotFound), - // Err(sqlx::Error::RowNotFound), - // Err(sqlx::Error::RowNotFound), + _ => ( + Err(sqlx::Error::RowNotFound), + Err(sqlx::Error::RowNotFound), + Err(sqlx::Error::RowNotFound) + ), }; return HttpResponse::Ok().json(( diff --git a/src/api/song.rs b/src/api/song.rs index 3748210..e046d22 100644 --- a/src/api/song.rs +++ b/src/api/song.rs @@ -1,5 +1,6 @@ use crate::api::{get_response_from_query, Response}; -use crate::database::{Delete, Song, SongPost, SongPut}; +use crate::database::Delete; +use crate::database::song::{Song, SongPost, SongPut}; use crate::extractors::auth_token::AuthenticationToken; use crate::AppState; use actix_web::{delete, get, post, put, web, HttpResponse}; diff --git a/src/database.rs b/src/database.rs deleted file mode 100644 index 37960e1..0000000 --- a/src/database.rs +++ /dev/null @@ -1,493 +0,0 @@ -use serde::{Deserialize, Serialize}; -use sqlx::mysql::{MySqlPool, MySqlPoolOptions, MySqlQueryResult}; -use std::env; - -#[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)] -pub struct Album { - name: Option, - id: Option, - cover: Option, - artist_name: Option, - artist_id: Option, -} - -#[derive(Serialize)] -pub struct Artist { - name: Option, - id: Option, -} - -#[derive(Serialize, Deserialize)] -pub struct SongPost { - name: Option, - lyrics: Option, - album_id: Option, -} - -#[derive(Serialize, Deserialize)] -pub struct AlbumPost { - name: Option, - cover: Option, - artist_id: Option, -} - -#[derive(Serialize, Deserialize)] -pub struct ArtistPost { - name: Option, -} - -#[derive(Serialize, Deserialize)] -pub struct SongPut { - id: Option, - name: Option, - lyrics: Option, - album_id: Option, -} - -#[derive(Serialize, Deserialize)] -pub struct AlbumPut { - id: Option, - name: Option, - cover: Option, - artist_id: Option, -} - -#[derive(Serialize, Deserialize)] -pub struct ArtistPut { - id: Option, - name: Option, -} - -#[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 }) - } - - 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 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 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 search_results( - &self, - ) -> ( - Result, sqlx::Error>, - Result, sqlx::Error>, - Result, sqlx::Error>, - ) { - ( - self.select_artists().await, - self.select_albums().await, - self.select_songs().await, - ) - } - - 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, - ); - } - - 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 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 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_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 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 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_song(&self, id: i32) -> Result { - sqlx::query!( - "DELETE FROM song - WHERE id = ?", - 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 - } - - 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/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