From ed0f5925de1931a13e6713d73e174a5362fc5d68 Mon Sep 17 00:00:00 2001 From: niliara-edu Date: Sun, 19 Jan 2025 18:04:33 +0100 Subject: database rework update (uncomplete, broken) --- src/database.rs | 229 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 229 insertions(+) create mode 100644 src/database.rs (limited to 'src/database.rs') diff --git a/src/database.rs b/src/database.rs new file mode 100644 index 0000000..299bd8d --- /dev/null +++ b/src/database.rs @@ -0,0 +1,229 @@ +use serde::Serialize; +use sqlx::mysql::{MySqlPool, MySqlPoolOptions}; +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, + artist_name: Option, + artist_id: Option, +} + +#[derive(Serialize)] +pub struct Artist { + name: Option, + 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 variables are *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> { + return 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: String) -> Result, sqlx::Error> { + return 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_all(&self.db_pool) + .await; + } + + pub async fn select_songs_by_album(&self, album_id: String) -> Result, sqlx::Error> { + return 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: String, + ) -> Result, sqlx::Error> { + return 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: String) -> Result, sqlx::Error> { + let name: String = format!("{}{}{}", "%", name_raw, "%"); + return 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> { + return sqlx::query_as!( + Album, + "SELECT album.name, album.id, + 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: String) -> Result, sqlx::Error> { + return sqlx::query_as!( + Album, + "SELECT album.name, album.id, + 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_all(&self.db_pool) + .await; + } + + pub async fn select_albums_by_name(&self, name_raw: String) -> Result, sqlx::Error> { + let name : String = format!("{}{}{}", "%", name_raw, "%"); + return sqlx::query_as!( + Album, + "SELECT album.name, album.id, + 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_id(&self, artist_id: String) -> Result, sqlx::Error> { + return sqlx::query_as!( + Album, + "SELECT album.name, album.id, + 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> { + return sqlx::query_as!( + Artist, + "SELECT name, id + FROM artist" + , + ) + .fetch_all(&self.db_pool) + .await; + } + + pub async fn select_artist_by_id(&self, id: String) -> Result, sqlx::Error> { + return sqlx::query_as!( + Artist, + "SELECT name, id + FROM artist + WHERE id = ?" + , + id, + ) + .fetch_all(&self.db_pool) + .await; + } + + pub async fn select_artists_by_name(&self, name_raw: String) -> Result, sqlx::Error> { + let name : String = format!("{}{}{}", "%", name_raw, "%"); + return sqlx::query_as!( + Artist, + "SELECT name, id + FROM artist + WHERE name = ?" + , + name, + ) + .fetch_all(&self.db_pool) + .await; + } +} -- cgit v1.2.3