Database connection

1 files changed, 220 insertions, 64 deletions

diff --git a/server/src/main.rs b/server/src/main.rs
index 702d954..9f7204a 100644
--- a/server/src/main.rs
+++ b/server/src/main.rs
@@ -1,54 +1,19 @@
 #[macro_use] extern crate rocket;
 
+use futures::{stream::TryStreamExt, future::TryFutureExt};
+
+use rocket::{Rocket, Build, futures};
+use rocket::fairing::{self, AdHoc};
 use rocket::request::{self, Outcome, Request, FromRequest};
 use rocket::response::{status::NotFound};
 use rocket::serde::json::Json;
+use rocket_db_pools::{sqlx, Database, Connection};
 
 mod api_model;
 
-static USER1: api_model::User = api_model::User {
-    username: "u1",
-    name: "User #1",
-    active: true,
-};
-
-static USER2: api_model::User = api_model::User {
-    username: "u2",
-    name: "User #2",
-    active: true,
-};
-
-static USER3: api_model::User = api_model::User {
-    username: "u3",
-    name: "User #3",
-    active: true,
-};
-
-fn make_r1<'r>() -> api_model::Review<'r> {
-    api_model::Review {
-        id: 1,
-        title: "Review #1",
-        description: "Description for review #1",
-        owner: &USER1,
-        reviewers: vec![&USER2, &USER3],
-        watchers: vec![],
-        state: api_model::ReviewState::OPEN,
-        progress: 0.42,
-    }
-}
-
-fn make_r2<'r>() -> api_model::Review<'r> {
-    api_model::Review {
-        id: 2,
-        title: "Review #2",
-        description: "Description for review #2",
-        owner: &USER2,
-        reviewers: vec![&USER1],
-        watchers: vec![&USER3],
-        state: api_model::ReviewState::OPEN,
-        progress: 0.9999,
-    }
-}
+#[derive(Database)]
+#[database("eyeballs")]
+struct Db(sqlx::MySqlPool);
 
 struct User {
     username: String,
@@ -69,42 +34,233 @@ impl<'r> FromRequest<'r> for User {
     }
 }
 
-fn review_entry<'r>(review: &api_model::Review<'r>) -> api_model::ReviewEntry<'r> {
-    api_model::ReviewEntry {
-        id: review.id,
-        title: review.title,
-        owner: review.owner,
-        state: review.state,
-        progress: review.progress,
+enum Role {
+    Reviewer,
+    Watcher,
+}
+
+struct UserRole {
+    user: api_model::User,
+    role: Role,
+}
+
+impl TryFrom<u8> for Role {
+    type Error = &'static str;
+
+    fn try_from(value: u8) -> Result<Self, Self::Error> {
+        match value {
+            0 => Ok(Role::Reviewer),
+            1 => Ok(Role::Watcher),
+            _ => Err("Invalid role")
+        }
+    }
+}
+
+impl TryFrom<u8> for api_model::ReviewState {
+    type Error = &'static str;
+
+    fn try_from(value: u8) -> Result<Self, Self::Error> {
+        match value {
+            0 => Ok(api_model::ReviewState::Draft),
+            1 => Ok(api_model::ReviewState::Open),
+            2 => Ok(api_model::ReviewState::Dropped),
+            3 => Ok(api_model::ReviewState::Closed),
+            _ => Err("Invalid review state")
+        }
     }
 }
 
-#[get("/reviews")]
-async fn reviews<'r>(_user: User) -> Json<api_model::Reviews<'r>> {
+#[get("/projects?<limit>&<offset>")]
+async fn projects<'r>(mut db: Connection<Db>, _user: User, limit: Option<u32>, offset: Option<u32>) -> Json<api_model::Projects> {
+    let uw_offset = offset.unwrap_or(0);
+    let uw_limit = limit.unwrap_or(10);
+
+    let entries = sqlx::query!(
+        "SELECT id,title FROM projects ORDER BY title,id LIMIT ? OFFSET ?",
+        uw_limit, uw_offset)
+        .fetch(&mut **db)
+        .map_ok(|r| api_model::ProjectEntry {
+            id: r.id,
+            title: r.title,
+        })
+        .try_collect::<Vec<_>>()
+        .await
+        .unwrap();
+
+    let count = sqlx::query!(
+        "SELECT COUNT(id) AS count FROM projects")
+        .fetch_one(&mut **db)
+        .map_ok(|r| r.count)
+        .await
+        .unwrap();
+
+    let u32_count = u32::try_from(count).unwrap();
+
+    Json(
+        api_model::Projects {
+            offset: uw_offset,
+            limit: uw_limit,
+            total_count: u32_count,
+            more: uw_offset + uw_limit < u32_count,
+            projects: entries,
+        },
+    )
+}
+
+#[get("/project/<projectid>")]
+async fn project<'r>(mut db: Connection<Db>, _user: User, projectid: u64) -> Result<Json<api_model::Project>, NotFound<String>> {
+   let members = sqlx::query!(
+        "SELECT id, username, name, active FROM users JOIN project_users ON project_users.user=users.id WHERE project_users.project=?",
+        projectid)
+        .fetch(&mut **db)
+        .map_ok(|r| api_model::User {
+            id: r.id,
+            username: r.username,
+            name: r.name,
+            active: r.active != 0,
+        })
+        .try_collect::<Vec<_>>()
+        .await
+        .unwrap();
+
+    let project = sqlx::query!(
+        "SELECT id,title,description FROM projects WHERE id=?",
+        projectid)
+        .fetch_one(&mut **db)
+        .map_ok(|r| api_model::Project {
+            id: r.id,
+            title: r.title,
+            description: r.description,
+            members: members,
+        })
+        .await
+        .map_err(|e| NotFound(e.to_string()))
+        .unwrap();
+
+    Ok(Json(project))
+}
+
+#[get("/reviews/<projectid>?<limit>&<offset>")]
+async fn reviews<'r>(mut db: Connection<Db>, _user: User, projectid: u64, limit: Option<u32>, offset: Option<u32>) -> Json<api_model::Reviews> {
+    let uw_offset = offset.unwrap_or(0);
+    let uw_limit = limit.unwrap_or(10);
+    let entries = sqlx::query!(
+        "SELECT reviews.id AS id,title,state,progress,users.id AS user_id,users.username AS username,users.name AS name,users.active AS user_active FROM reviews JOIN users ON users.id=owner WHERE project=? ORDER BY id DESC LIMIT ? OFFSET ?",
+        projectid, uw_limit, uw_offset)
+        .fetch(&mut **db)
+        .map_ok(|r| api_model::ReviewEntry {
+            id: r.id,
+            title: r.title,
+            owner: api_model::User {
+                id: r.user_id,
+                username: r.username,
+                name: r.name,
+                active: r.user_active != 0,
+            },
+            state: api_model::ReviewState::try_from(r.state).unwrap(),
+            progress: r.progress,
+        })
+        .try_collect::<Vec<_>>()
+        .await
+        .unwrap();
+
+    let count = sqlx::query!(
+        "SELECT COUNT(id) AS count FROM reviews WHERE project=?",
+        projectid)
+        .fetch_one(&mut **db)
+        .map_ok(|r| r.count)
+        .await
+        .unwrap();
+
+    let u32_count = u32::try_from(count).unwrap();
+
     Json(
         api_model::Reviews {
-            offset: 0,
-            limit: 10,
-            total_count: 2,
-            more: false,
-            reviews: vec![review_entry(&make_r1()), review_entry(&make_r2())],
+            offset: uw_offset,
+            limit: uw_limit,
+            total_count: u32_count,
+            more: uw_offset + uw_limit < u32_count,
+            reviews: entries,
         },
     )
 }
 
-#[get("/review/<id>")]
-async fn review<'r>(id: u64, _user: User) -> Result<Json<api_model::Review<'r>>, NotFound<String>> {
-    match id {
-        1 => Ok(Json(make_r1())),
-        2 => Ok(Json(make_r2())),
-        _ => Err(NotFound(id.to_string()))
+#[get("/review/<projectid>/<reviewid>")]
+async fn review<'r>(mut db: Connection<Db>, _user: User, projectid: u64, reviewid: u64) -> Result<Json<api_model::Review>, NotFound<String>> {
+    let mut users = sqlx::query!(
+        "SELECT id,username,name,active,review_users.role AS role FROM users JOIN review_users ON review_users.user=id WHERE review_users.review=? ORDER BY role,username,id",
+        reviewid)
+        .fetch(&mut **db)
+        .map_ok(|r| UserRole {
+            user: api_model::User {
+                id: r.id,
+                username: r.username,
+                name: r.name,
+                active: r.active != 0,
+            },
+            role: Role::try_from(r.role).unwrap(),
+        })
+        .try_collect::<Vec<_>>()
+        .await
+        .unwrap();
+
+    let first_reviewer = users.iter()
+        .position(|u| matches!(u.role, Role::Reviewer))
+        .unwrap_or(users.len());
+    let mut reviewers: Vec<api_model::User> = Vec::with_capacity(first_reviewer);
+    for user_role in users.drain(0..first_reviewer) {
+        reviewers.push(user_role.user);
+    }
+    let mut watchers: Vec<api_model::User> = Vec::with_capacity(users.len());
+    for user_role in users.drain(0..) {
+        watchers.push(user_role.user);
+    }
+
+    let review = sqlx::query!(
+        "SELECT reviews.id AS id,title,description,state,progress,users.id AS user_id,users.username AS username,users.name AS name,users.active AS user_active FROM reviews JOIN users ON users.id=owner WHERE project=? AND reviews.id=?",
+        projectid, reviewid)
+        .fetch_one(&mut **db)
+        .map_ok(|r| api_model::Review {
+            id: r.id,
+            title: r.title,
+            description: r.description,
+            owner: api_model::User {
+                id: r.user_id,
+                username: r.username,
+                name: r.name,
+                active: r.user_active != 0,
+            },
+            reviewers: reviewers,
+            watchers: watchers,
+            state: api_model::ReviewState::try_from(r.state).unwrap(),
+            progress: r.progress,
+        })
+        .await
+        .map_err(|e| NotFound(e.to_string()))
+        .unwrap();
+
+    Ok(Json(review))
+}
+
+async fn run_migrations(rocket: Rocket<Build>) -> fairing::Result {
+    match Db::fetch(&rocket) {
+        Some(db) => match sqlx::migrate!("./migrations").run(&**db).await {
+            Ok(_) => Ok(rocket),
+            Err(e) => {
+                error!("Failed to initialize database: {}", e);
+                Err(rocket)
+            }
+        }
+        None => Err(rocket),
     }
 }
 
 #[rocket::main]
 async fn main() -> Result<(), rocket::Error> {
     let _rocket = rocket::build()
-        .mount("/api/v1", routes![reviews, review])
+        .attach(Db::init())
+        .attach(AdHoc::try_on_ignite("Database Migrations", run_migrations))
+        .mount("/api/v1", routes![projects, project, reviews, review])
         .launch()
         .await?;