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) --- README.md | 4 + src/database.rs | 229 +++++++++++++++++++++++++++++++++++++++++++++++++++ src/main.rs | 21 ++--- src/routes/album.rs | 2 +- src/routes/artist.rs | 2 +- src/routes/song.rs | 2 +- 6 files changed, 242 insertions(+), 18 deletions(-) create mode 100644 src/database.rs diff --git a/README.md b/README.md index 879e026..5cc8773 100644 --- a/README.md +++ b/README.md @@ -27,3 +27,7 @@ If not, just start/stop the mariadb service manually. You can only search a parameter at a time. This is because Rust needs to process the sql queries ahead of time in order to make them safe to injection attacks. + +### SearchResults route ignored +It's a little hard to get SearchResults to work, +so it has been skipped for now. 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; + } +} diff --git a/src/main.rs b/src/main.rs index bddb0b6..f1a58ec 100644 --- a/src/main.rs +++ b/src/main.rs @@ -1,34 +1,25 @@ mod routes; mod structs; +mod database; use actix_web::{web, App, HttpServer}; use dotenv::dotenv; -use sqlx::mysql::{MySqlPool, MySqlPoolOptions}; -use std::env; +use std::sync::Arc; -#[derive(Clone)] struct AppState { - pool: MySqlPool, + database: database::DatabaseWrapper } #[actix_web::main] async fn main() -> std::io::Result<()> { dotenv().ok(); - 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(); - let app_state = AppState { pool }; + let app_state_raw = database::DatabaseWrapper::new().await.expect("Something went wrong -- DatabaseWrapper::new()"); + let app_state = Arc::new(app_state_raw); HttpServer::new(move || { App::new() - .app_data(web::Data::new(app_state.clone())) + .app_data(web::Data::new(Arc::clone(&app_state))) .route("/", web::get().to(root)) .service(routes::song::song) .service(routes::album::album) diff --git a/src/routes/album.rs b/src/routes/album.rs index 1948339..02116d4 100644 --- a/src/routes/album.rs +++ b/src/routes/album.rs @@ -10,7 +10,7 @@ struct AlbumQueryOptions { artist: Option, } -#[get("/api/album")] +#[get("/album")] pub async fn album( app_state: web::Data, get_args: web::Query, diff --git a/src/routes/artist.rs b/src/routes/artist.rs index bf18e34..6a16ace 100644 --- a/src/routes/artist.rs +++ b/src/routes/artist.rs @@ -9,7 +9,7 @@ struct ArtistQueryOptions { name: Option, } -#[get("/api/artist")] +#[get("/artist")] pub async fn artist( app_state: web::Data, get_args: web::Query, diff --git a/src/routes/song.rs b/src/routes/song.rs index 8b28b8d..0cdd4b5 100644 --- a/src/routes/song.rs +++ b/src/routes/song.rs @@ -11,7 +11,7 @@ struct SongQueryOptions { album: Option, } -#[get("/api/song")] +#[get("/song")] pub async fn song( app_state: web::Data, get_args: web::Query, -- cgit v1.2.3