Add string id for project and reduce usage of numeric ids in general

User: username must be unique, use as primary key and drop id.

1 files changed, 29 insertions, 38 deletions

diff --git a/server/src/main.rs b/server/src/main.rs
index 88546cb..e48cfb7 100644
--- a/server/src/main.rs
+++ b/server/src/main.rs
@@ -10,6 +10,7 @@ use rocket::serde::json::Json;
 use rocket::{futures, Build, Rocket};
 use rocket_db_pools::{sqlx, Connection, Database};
 use sqlx::Acquire;
+use std::path::PathBuf;
 use utoipa::OpenApi;
 use utoipa_swagger_ui::SwaggerUi;
 
@@ -132,16 +133,15 @@ async fn projects(
 
 async fn get_project(
     db: &mut Connection<Db>,
-    projectid: u64,
+    projectid: &str,
 ) -> Result<Json<api_model::Project>, NotFound<&'static str>> {
     let users = sqlx::query!(
-        "SELECT id, username, name, dn, default_role, maintainer FROM users JOIN project_users ON project_users.user=users.id WHERE project_users.project=?",
+        "SELECT id, name, dn, default_role, maintainer FROM users JOIN project_users ON project_users.user=users.id WHERE project_users.project=?",
         projectid)
         .fetch(&mut ***db)
         .map_ok(|r| api_model::ProjectUserEntry {
             user: api_model::User {
                 id: r.id,
-                username: r.username,
                 name: r.name,
                 active: r.dn.is_some(),
             },
@@ -184,7 +184,7 @@ async fn get_project(
 async fn project(
     mut db: Connection<Db>,
     _session: auth::Session,
-    projectid: u64,
+    projectid: &str,
 ) -> Result<Json<api_model::Project>, NotFound<&'static str>> {
     get_project(&mut db, projectid).await
 }
@@ -197,26 +197,25 @@ async fn project(
         ("session" = []),
     ),
 )]
-#[post("/project/new", data = "<data>")]
+#[post("/project/<projectid>/new", data = "<data>")]
 async fn project_new(
     mut db: Connection<Db>,
     session: auth::Session,
+    projectid: &str,
     data: Json<api_model::ProjectData<'_>>,
 ) -> Json<api_model::Project> {
-    let projectid: u64;
-
     {
         let mut tx = db.begin().await.unwrap();
 
-        projectid = sqlx::query!(
-            "INSERT INTO projects (title, description, remote, main_branch) VALUES (?, ?, ?, ?)",
+        sqlx::query!(
+            "INSERT INTO projects (id, title, description, remote, main_branch) VALUES (?, ?, ?, ?, ?)",
+            projectid,
             data.title.unwrap_or("Unnamed"),
             data.description.unwrap_or(""),
             data.remote.unwrap_or(""),
             data.main_branch.unwrap_or("main"),
         )
         .execute(&mut *tx)
-        .map_ok(|r| r.last_insert_id())
         .await
         .unwrap();
 
@@ -236,7 +235,7 @@ async fn project_new(
 async fn project_check_maintainer(
     db: &mut Connection<Db>,
     session: auth::Session,
-    projectid: u64,
+    projectid: &str,
 ) -> Result<&'static str, Custom<&'static str>> {
     let is_maintainer = sqlx::query!(
         "SELECT COUNT(user) AS count FROM project_users WHERE project=? AND user=? AND maintainer=TRUE",
@@ -273,7 +272,7 @@ async fn project_check_maintainer(
 async fn project_update(
     mut db: Connection<Db>,
     session: auth::Session,
-    projectid: u64,
+    projectid: &str,
     data: Json<api_model::ProjectData<'_>>,
 ) -> Result<&'static str, Custom<&'static str>> {
     project_check_maintainer(&mut db, session, projectid).await?;
@@ -316,12 +315,12 @@ async fn project_update(
         ("session" = []),
     ),
 )]
-#[post("/project/<projectid>/user/new?<userid>", data = "<data>")]
+#[post("/project/<projectid>/user/<userid>/new", data = "<data>")]
 async fn project_user_add(
     mut db: Connection<Db>,
     session: auth::Session,
-    projectid: u64,
-    userid: u64,
+    projectid: &str,
+    userid: &str,
     data: Json<api_model::ProjectUserEntryData>,
 ) -> Result<&'static str, Custom<&'static str>> {
     project_check_maintainer(&mut db, session, projectid).await?;
@@ -357,8 +356,8 @@ async fn project_user_add(
 async fn project_user_update(
     mut db: Connection<Db>,
     session: auth::Session,
-    projectid: u64,
-    userid: u64,
+    projectid: &str,
+    userid: &str,
     data: Json<api_model::ProjectUserEntryData>,
 ) -> Result<&'static str, Custom<&'static str>> {
     let need_maintainer = data.maintainer.is_some() || userid != session.user_id;
@@ -404,8 +403,8 @@ async fn project_user_update(
 async fn project_user_del(
     mut db: Connection<Db>,
     session: auth::Session,
-    projectid: u64,
-    userid: u64,
+    projectid: &str,
+    userid: &str,
 ) -> Result<&'static str, Custom<&'static str>> {
     let need_maintainer = userid != session.user_id;
 
@@ -437,14 +436,14 @@ async fn project_user_del(
 async fn reviews(
     mut db: Connection<Db>,
     _session: auth::Session,
-    projectid: u64,
+    projectid: &str,
     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.dn AS user_dn FROM reviews JOIN users ON users.id=owner WHERE project=? ORDER BY id DESC LIMIT ? OFFSET ?",
+        "SELECT reviews.id AS id,title,state,progress,users.id AS user_id,users.name AS name,users.dn AS user_dn 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 {
@@ -452,7 +451,6 @@ async fn reviews(
             title: r.title,
             owner: api_model::User {
                 id: r.user_id,
-                username: r.username,
                 name: r.name,
                 active: r.user_dn.is_some(),
             },
@@ -492,28 +490,24 @@ async fn reviews(
         ("session" = []),
     ),
 )]
-#[get("/review/<reviewid>")]
+#[get("/review/<projectid>/<branch..>")]
 async fn review(
     mut db: Connection<Db>,
     _session: auth::Session,
-    reviewid: u64,
+    projectid: &str,
+    branch: PathBuf,
 ) -> Result<Json<api_model::Review>, NotFound<&'static str>> {
-    let mut projectid = 0;
-
     let mut review = sqlx::query!(
-        "SELECT reviews.id AS id,project,title,description,state,progress,branch,archived,users.id AS user_id,users.username AS username,users.name AS name,users.dn AS user_dn FROM reviews JOIN users ON users.id=owner WHERE reviews.id=?",
-        reviewid)
+        "SELECT reviews.id AS id,title,description,state,progress,branch,archived,users.id AS user_id,users.name AS name,users.dn AS user_dn FROM reviews JOIN users ON users.id=owner WHERE project=? AND branch=?",
+        projectid, branch.as_path().to_str().unwrap())
         .fetch_one(&mut **db)
         .map_ok(|r| {
-            projectid = r.project;
-
             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_dn.is_some(),
                 },
@@ -528,13 +522,12 @@ async fn review(
         .await?;
 
     let mut users = sqlx::query!(
-        "SELECT id,username,name,dn,project_users.default_role AS role FROM users JOIN project_users ON project_users.user=id WHERE project_users.project=? ORDER BY role,username,id",
+        "SELECT id,name,dn,project_users.default_role AS role FROM users JOIN project_users ON project_users.user=id WHERE project_users.project=? ORDER BY role,id",
         projectid)
         .fetch(&mut **db)
         .map_ok(|r| api_model::ReviewUserEntry {
             user: api_model::User {
                 id: r.id,
-                username: r.username,
                 name: r.name,
                 active: r.dn.is_some(),
             },
@@ -545,13 +538,12 @@ async fn review(
         .unwrap();
 
     let override_users = sqlx::query!(
-        "SELECT id,username,name,dn,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)
+        "SELECT id,name,dn,review_users.role AS role FROM users JOIN review_users ON review_users.user=id WHERE review_users.review=? ORDER BY role,id",
+        review.id)
         .fetch(&mut **db)
         .map_ok(|r| api_model::ReviewUserEntry {
             user: api_model::User {
                 id: r.id,
-                username: r.username,
                 name: r.name,
                 active: r.dn.is_some(),
             },
@@ -595,14 +587,13 @@ async fn users(
     let uw_offset = offset.unwrap_or(0);
     let uw_limit = limit.unwrap_or(10);
     let entries = sqlx::query!(
-        "SELECT id,username,name,dn FROM users ORDER BY username LIMIT ? OFFSET ?",
+        "SELECT id,name,dn FROM users ORDER BY id LIMIT ? OFFSET ?",
         uw_limit,
         uw_offset
     )
     .fetch(&mut **db)
     .map_ok(|r| api_model::User {
         id: r.id,
-        username: r.username,
         name: r.name,
         active: r.dn.is_some(),
     })