From 57a2df34f4986f1f6062f22804021925afec0419 Mon Sep 17 00:00:00 2001
From: niliara-edu <nil.jimeno@estudiant.fjaverianas.com>
Date: Sat, 25 Jan 2025 20:50:21 +0100
Subject: refactor database

---
 src/database/song.rs | 169 +++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 169 insertions(+)
 create mode 100644 src/database/song.rs

(limited to 'src/database/song.rs')

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<String>,
+    id: Option<i32>,
+    lyrics: Option<String>,
+    album_name: Option<String>,
+    album_id: Option<i32>,
+    artist_name: Option<String>,
+    artist_id: Option<i32>,
+}
+
+#[derive(Serialize, Deserialize)]
+pub struct SongPost {
+    name: Option<String>,
+    lyrics: Option<String>,
+    album_id: Option<String>,
+}
+
+#[derive(Serialize, Deserialize)]
+pub struct SongPut {
+    id: Option<String>,
+    name: Option<String>,
+    lyrics: Option<String>,
+    album_id: Option<String>,
+}
+
+impl DatabaseWrapper {
+    pub async fn select_songs(&self) -> Result<Vec<Song>, 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<Option<Song>, 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<Vec<Song>, 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<Vec<Song>, 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<Vec<Song>, 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<MySqlQueryResult, sqlx::Error> {
+        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<MySqlQueryResult, sqlx::Error> {
+        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<MySqlQueryResult, sqlx::Error> {
+        sqlx::query!(
+            "DELETE FROM song
+            WHERE id = ?",
+            id
+        )
+        .execute(&self.db_pool)
+        .await
+    }
+}
-- 
cgit v1.2.3