Add clients to the API

2 files changed, 472 insertions, 0 deletions

diff --git a/src/services/db/client.rs b/src/services/db/client.rs
new file mode 100644
index 0000000..d1531be
--- /dev/null
+++ b/src/services/db/client.rs
@@ -0,0 +1,236 @@
+use std::str::FromStr;
+
+use exun::{RawUnexpected, ResultErrorExt};
+use sqlx::{mysql::MySqlQueryResult, query, query_as, query_scalar, Executor, MySql, Transaction};
+use url::Url;
+use uuid::Uuid;
+
+use crate::{
+	models::client::{Client, ClientResponse, ClientType},
+	services::crypto::PasswordHash,
+};
+
+pub async fn client_id_exists<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+) -> Result<bool, RawUnexpected> {
+	query_scalar!(
+		r"SELECT EXISTS(SELECT id FROM clients WHERE id = ?) as `e: bool`",
+		id
+	)
+	.fetch_one(executor)
+	.await
+	.unexpect()
+}
+
+pub async fn client_alias_exists<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	alias: &str,
+) -> Result<bool, RawUnexpected> {
+	query_scalar!(
+		"SELECT EXISTS(SELECT alias FROM clients WHERE alias = ?) as `e: bool`",
+		alias
+	)
+	.fetch_one(executor)
+	.await
+	.unexpect()
+}
+
+pub async fn get_client_response<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+) -> Result<Option<ClientResponse>, RawUnexpected> {
+	let record = query_as!(
+		ClientResponse,
+		r"SELECT id as `id: Uuid`,
+		         alias,
+				 type as `client_type: ClientType`
+		  FROM clients WHERE id = ?",
+		id
+	)
+	.fetch_optional(executor)
+	.await?;
+
+	Ok(record)
+}
+
+pub async fn get_client_alias<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+) -> Result<Option<Box<str>>, RawUnexpected> {
+	let alias = query_scalar!("SELECT alias FROM clients WHERE id = ?", id)
+		.fetch_optional(executor)
+		.await
+		.unexpect()?;
+
+	Ok(alias.map(String::into_boxed_str))
+}
+
+pub async fn get_client_type<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+) -> Result<Option<ClientType>, RawUnexpected> {
+	let ty = query_scalar!(
+		"SELECT type as `type: ClientType` FROM clients WHERE id = ?",
+		id
+	)
+	.fetch_optional(executor)
+	.await
+	.unexpect()?;
+
+	Ok(ty)
+}
+
+pub async fn get_client_redirect_uris<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+) -> Result<Box<[Url]>, RawUnexpected> {
+	let uris = query_scalar!(
+		"SELECT redirect_uri FROM client_redirect_uris WHERE client_id = ?",
+		id
+	)
+	.fetch_all(executor)
+	.await
+	.unexpect()?;
+
+	uris.into_iter()
+		.map(|s| Url::from_str(&s).unexpect())
+		.collect()
+}
+
+async fn delete_client_redirect_uris<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+) -> Result<(), sqlx::Error> {
+	query!("DELETE FROM client_redirect_uris WHERE client_id = ?", id)
+		.execute(executor)
+		.await?;
+	Ok(())
+}
+
+async fn create_client_redirect_uris<'c>(
+	mut transaction: Transaction<'c, MySql>,
+	client_id: Uuid,
+	uris: &[Url],
+) -> Result<(), sqlx::Error> {
+	for uri in uris {
+		query!(
+			r"INSERT INTO client_redirect_uris (client_id, redirect_uri)
+									    VALUES (        ?,            ?)",
+			client_id,
+			uri.to_string()
+		)
+		.execute(&mut transaction)
+		.await?;
+	}
+
+	transaction.commit().await?;
+
+	Ok(())
+}
+
+pub async fn create_client<'c>(
+	mut transaction: Transaction<'c, MySql>,
+	client: &Client,
+) -> Result<(), sqlx::Error> {
+	query!(
+		r"INSERT INTO clients (id, alias, type, secret_hash, secret_salt, secret_version)
+					   VALUES ( ?,     ?,    ?,           ?,           ?,              ?)",
+		client.id(),
+		client.alias(),
+		client.client_type(),
+		client.secret_hash(),
+		client.secret_salt(),
+		client.secret_version(),
+	)
+	.execute(&mut transaction)
+	.await?;
+
+	create_client_redirect_uris(transaction, client.id(), client.redirect_uris()).await?;
+
+	Ok(())
+}
+
+pub async fn update_client<'c>(
+	mut transaction: Transaction<'c, MySql>,
+	client: &Client,
+) -> Result<(), sqlx::Error> {
+	query!(
+		r"UPDATE clients SET
+		alias = ?,
+		type = ?,
+		secret_hash = ?,
+		secret_salt = ?,
+		secret_version = ?
+		WHERE id = ?",
+		client.client_type(),
+		client.alias(),
+		client.secret_hash(),
+		client.secret_salt(),
+		client.secret_version(),
+		client.id()
+	)
+	.execute(&mut transaction)
+	.await?;
+
+	update_client_redirect_uris(transaction, client.id(), client.redirect_uris()).await?;
+
+	Ok(())
+}
+
+pub async fn update_client_alias<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+	alias: &str,
+) -> Result<MySqlQueryResult, sqlx::Error> {
+	query!("UPDATE clients SET alias = ? WHERE id = ?", alias, id)
+		.execute(executor)
+		.await
+}
+
+pub async fn update_client_type<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+	ty: ClientType,
+) -> Result<MySqlQueryResult, sqlx::Error> {
+	query!("UPDATE clients SET type = ? WHERE id = ?", ty, id)
+		.execute(executor)
+		.await
+}
+
+pub async fn update_client_redirect_uris<'c>(
+	mut transaction: Transaction<'c, MySql>,
+	id: Uuid,
+	uris: &[Url],
+) -> Result<(), sqlx::Error> {
+	delete_client_redirect_uris(&mut transaction, id).await?;
+	create_client_redirect_uris(transaction, id, uris).await?;
+	Ok(())
+}
+
+pub async fn update_client_secret<'c>(
+	executor: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+	secret: Option<PasswordHash>,
+) -> Result<MySqlQueryResult, sqlx::Error> {
+	if let Some(secret) = secret {
+		query!(
+			"UPDATE clients SET secret_hash = ?, secret_salt = ?, secret_version = ? WHERE id = ?",
+			secret.hash(),
+			secret.salt(),
+			secret.version(),
+			id
+		)
+		.execute(executor)
+		.await
+	} else {
+		query!(
+			r"UPDATE clients
+			  SET secret_hash = NULL, secret_salt = NULL, secret_version = NULL
+			  WHERE id = ?",
+			id
+		)
+		.execute(executor)
+		.await
+	}
+}
diff --git a/src/services/db/user.rs b/src/services/db/user.rs
new file mode 100644
index 0000000..09a09da
--- /dev/null
+++ b/src/services/db/user.rs
@@ -0,0 +1,236 @@
+use exun::RawUnexpected;
+use sqlx::{mysql::MySqlQueryResult, query, query_as, query_scalar, Executor, MySql};
+use uuid::Uuid;
+
+use crate::{models::user::User, services::crypto::PasswordHash};
+
+struct UserRow {
+	id: Uuid,
+	username: String,
+	password_hash: Vec<u8>,
+	password_salt: Vec<u8>,
+	password_version: u32,
+}
+
+impl TryFrom<UserRow> for User {
+	type Error = RawUnexpected;
+
+	fn try_from(row: UserRow) -> Result<Self, Self::Error> {
+		let password = PasswordHash::from_fields(
+			&row.password_hash,
+			&row.password_salt,
+			row.password_version as u8,
+		);
+		let user = User {
+			id: row.id,
+			username: row.username.into_boxed_str(),
+			password,
+		};
+		Ok(user)
+	}
+}
+
+/// Check if a user with a given user ID exists
+pub async fn user_id_exists<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	id: Uuid,
+) -> Result<bool, RawUnexpected> {
+	let exists = query_scalar!(
+		r#"SELECT EXISTS(SELECT id FROM users WHERE id = ?) as `e: bool`"#,
+		id
+	)
+	.fetch_one(conn)
+	.await?;
+
+	Ok(exists)
+}
+
+/// Check if a given username is taken
+pub async fn username_is_used<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	username: &str,
+) -> Result<bool, RawUnexpected> {
+	let exists = query_scalar!(
+		r#"SELECT EXISTS(SELECT id FROM users WHERE username = ?) as "e: bool""#,
+		username
+	)
+	.fetch_one(conn)
+	.await?;
+
+	Ok(exists)
+}
+
+/// Get a user from their ID
+pub async fn get_user<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	user_id: Uuid,
+) -> Result<Option<User>, RawUnexpected> {
+	let record = query_as!(
+		UserRow,
+		r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version
+		  FROM users WHERE id = ?",
+		user_id
+	)
+	.fetch_optional(conn)
+	.await?;
+
+	let Some(record) = record else { return Ok(None) };
+
+	Ok(Some(record.try_into()?))
+}
+
+/// Get a user from their username
+pub async fn get_user_by_username<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	username: &str,
+) -> Result<Option<User>, RawUnexpected> {
+	let record = query_as!(
+		UserRow,
+		r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version
+		  FROM users WHERE username = ?",
+		username
+	)
+	.fetch_optional(conn)
+	.await?;
+
+	let Some(record) = record else { return Ok(None) };
+
+	Ok(Some(record.try_into()?))
+}
+
+/// Search the list of users for a given username
+pub async fn search_users<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	username: &str,
+) -> Result<Box<[User]>, RawUnexpected> {
+	let records = query_as!(
+		UserRow,
+		r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version
+		  FROM users
+		  WHERE LOCATE(?, username) != 0",
+		username,
+	)
+	.fetch_all(conn)
+	.await?;
+
+	Ok(records
+		.into_iter()
+		.map(|u| u.try_into())
+		.collect::<Result<Box<[User]>, RawUnexpected>>()?)
+}
+
+/// Search the list of users, only returning a certain range of results
+pub async fn search_users_limit<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	username: &str,
+	offset: u32,
+	limit: u32,
+) -> Result<Box<[User]>, RawUnexpected> {
+	let records = query_as!(
+		UserRow,
+		r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version
+		  FROM users
+		  WHERE LOCATE(?, username) != 0
+		  LIMIT ?
+		  OFFSET ?",
+		username,
+		offset,
+		limit
+	)
+	.fetch_all(conn)
+	.await?;
+
+	Ok(records
+		.into_iter()
+		.map(|u| u.try_into())
+		.collect::<Result<Box<[User]>, RawUnexpected>>()?)
+}
+
+/// Get the username of a user with a certain ID
+pub async fn get_username<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	user_id: Uuid,
+) -> Result<Option<Box<str>>, RawUnexpected> {
+	let username = query_scalar!(r"SELECT username FROM users where id = ?", user_id)
+		.fetch_optional(conn)
+		.await?
+		.map(String::into_boxed_str);
+
+	Ok(username)
+}
+
+/// Create a new user
+pub async fn create_user<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	user: &User,
+) -> Result<MySqlQueryResult, sqlx::Error> {
+	query!(
+		r"INSERT INTO users (id, username, password_hash, password_salt, password_version)
+					 VALUES ( ?,        ?,             ?,             ?,                ?)",
+		user.id,
+		user.username(),
+		user.password_hash(),
+		user.password_salt(),
+		user.password_version()
+	)
+	.execute(conn)
+	.await
+}
+
+/// Update a user
+pub async fn update_user<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	user: &User,
+) -> Result<MySqlQueryResult, sqlx::Error> {
+	query!(
+		r"UPDATE users SET
+		  username = ?,
+		  password_hash = ?,
+		  password_salt = ?,
+		  password_version = ?
+		  WHERE id = ?",
+		user.username(),
+		user.password_hash(),
+		user.password_salt(),
+		user.password_version(),
+		user.id
+	)
+	.execute(conn)
+	.await
+}
+
+/// Update the username of a user with the given ID
+pub async fn update_username<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	user_id: Uuid,
+	username: &str,
+) -> Result<MySqlQueryResult, sqlx::Error> {
+	query!(
+		r"UPDATE users SET username = ? WHERE id = ?",
+		username,
+		user_id
+	)
+	.execute(conn)
+	.await
+}
+
+/// Update the password of a user with the given ID
+pub async fn update_password<'c>(
+	conn: impl Executor<'c, Database = MySql>,
+	user_id: Uuid,
+	password: &PasswordHash,
+) -> Result<MySqlQueryResult, sqlx::Error> {
+	query!(
+		r"UPDATE users SET
+		password_hash = ?,
+		password_salt = ?,
+		password_version = ?
+		WHERE id = ?",
+		password.hash(),
+		password.salt(),
+		password.version(),
+		user_id
+	)
+	.execute(conn)
+	.await
+}