database rework update (uncomplete, broken)

6 files changed, 242 insertions, 18 deletions

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<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)]
+pub struct Album {
+    name: Option<String>,
+    id: Option<i32>,
+    artist_name: Option<String>,
+    artist_id: Option<i32>,
+}
+
+#[derive(Serialize)]
+pub struct Artist {
+    name: Option<String>,
+    id: Option<i32>,
+}
+
+pub struct DatabaseWrapper {
+    db_pool: MySqlPool,
+}
+
+impl DatabaseWrapper {
+    pub async fn new() -> Result<DatabaseWrapper, sqlx::Error> {
+        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<Vec<Song>, 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<Vec<Song>, 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<Vec<Song>, 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<Vec<Song>, 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<Vec<Song>, 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<Vec<Album>, 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<Vec<Album>, 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<Vec<Album>, 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<Vec<Album>, 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<Vec<Artist>, 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<Vec<Artist>, 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<Vec<Artist>, 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<String>,
 }
 
-#[get("/api/album")]
+#[get("/album")]
 pub async fn album(
     app_state: web::Data<AppState>,
     get_args: web::Query<AlbumQueryOptions>,
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<String>,
 }
 
-#[get("/api/artist")]
+#[get("/artist")]
 pub async fn artist(
     app_state: web::Data<AppState>,
     get_args: web::Query<ArtistQueryOptions>,
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<String>,
 }
 
-#[get("/api/song")]
+#[get("/song")]
 pub async fn song(
     app_state: web::Data<AppState>,
     get_args: web::Query<SongQueryOptions>,