use crate::BindRow;
use crate::Schema;
use sqlx::{
Arguments, Database, Encode, Executor, FromRow, IntoArguments, Type, query, query_as,
query_with,
};
pub trait FormatPlaceholder {
fn format_placeholder(idx: usize) -> String;
}
pub trait RowsAffected {
fn rows_affected(&self) -> u64;
}
pub trait LastInsertedRowId {
fn last_insert_rowid(&self) -> i64;
}
pub(super) fn format_placeholders<DB: FormatPlaceholder>(n: usize) -> String {
(0..n)
.map(|idx| DB::format_placeholder(idx))
.collect::<Vec<String>>()
.join(",")
}
pub(super) fn comma_delimited_columns_with_id<S: Schema<DB>, DB: Database>() -> String {
std::iter::once(format!("\"{}\"", S::id_column()))
.chain(S::columns().iter().map(|c| format!("\"{c}\"")))
.collect::<Vec<String>>()
.join(",")
}
pub async fn generic_select_all<S, DB: Database>(
executor: impl Executor<'_, Database = DB>,
) -> sqlx::Result<Vec<S>>
where
S: Schema<DB> + for<'r> FromRow<'r, DB::Row> + Unpin,
for<'e> <DB as Database>::Arguments<'e>: IntoArguments<'e, DB>,
{
let columns = comma_delimited_columns_with_id::<S, DB>();
let table_name = S::table_name();
let sql = format!("SELECT {columns} FROM {table_name};");
query_as(&sql).fetch_all(executor).await
}
pub async fn generic_select_by_id<S, DB>(
executor: impl Executor<'_, Database = DB>,
id: &S::Id,
) -> sqlx::Result<Option<S>>
where
DB: Database + FormatPlaceholder,
S::Id: for<'q> Encode<'q, DB> + Type<DB>,
S: Schema<DB> + for<'r> FromRow<'r, DB::Row> + Unpin,
for<'e> <DB as Database>::Arguments<'e>: IntoArguments<'e, DB>,
{
let columns = comma_delimited_columns_with_id::<S, DB>();
let table_name = S::table_name();
let id_column = S::id_column();
let placeholder = DB::format_placeholder(0);
let sql = format!("SELECT {columns} FROM {table_name} WHERE {id_column} = {placeholder};");
query_as(&sql).bind(id).fetch_optional(executor).await
}
pub async fn generic_id_exists<S, DB>(
executor: impl Executor<'_, Database = DB>,
id: &S::Id,
) -> sqlx::Result<bool>
where
DB: Database + FormatPlaceholder,
S::Id: for<'q> Encode<'q, DB> + Type<DB>,
S: Schema<DB>,
for<'e> <DB as Database>::Arguments<'e>: IntoArguments<'e, DB>,
{
let table_name = S::table_name();
let id_column = S::id_column();
let placeholder = DB::format_placeholder(0);
let sql = format!("SELECT 1 FROM {table_name} WHERE \"{id_column}\" = {placeholder};");
query(&sql)
.bind(id)
.fetch_optional(executor)
.await
.map(|option| option.is_some())
}
pub async fn generic_insert_with_id<S, DB>(
executor: impl for<'e> Executor<'e, Database = DB>,
entity: S,
) -> sqlx::Result<()>
where
DB: Database + FormatPlaceholder,
S::Id: for<'q> Encode<'q, DB> + Type<DB>,
S: BindRow<DB>,
for<'e> <DB as Database>::Arguments<'e>: IntoArguments<'e, DB>,
{
let table_name = S::table_name();
let columns = comma_delimited_columns_with_id::<S, DB>();
let placeholders = format_placeholders::<DB>(S::columns().len() + 1);
let sql = format!("INSERT INTO {table_name} ({columns}) VALUES ({placeholders})");
let mut arguments = DB::Arguments::default();
arguments.add(entity.id()).map_err(sqlx::Error::Encode)?;
entity.bind_arguments(&mut arguments)?;
query_with(&sql, arguments).execute(executor).await?;
Ok(())
}
pub async fn generic_insert_returning_id<S, DB>(
executor: impl for<'e> Executor<'e, Database = DB>,
entity: S,
) -> sqlx::Result<i64>
where
DB: Database + FormatPlaceholder,
DB::QueryResult: LastInsertedRowId,
S: BindRow<DB>,
for<'e> <DB as Database>::Arguments<'e>: IntoArguments<'e, DB>,
{
let table_name = S::table_name();
let columns = S::columns()
.into_iter()
.map(|c| format!("\"{c}\""))
.collect::<Vec<String>>()
.join(",");
let placeholders = format_placeholders::<DB>(S::columns().len());
let sql = format!("INSERT INTO {table_name} ({columns}) VALUES ({placeholders})");
let mut arguments = DB::Arguments::default();
entity.bind_arguments(&mut arguments)?;
let result = query_with(&sql, arguments).execute(executor).await?;
Ok(result.last_insert_rowid())
}
pub async fn generic_update_by_id<S, DB>(
executor: impl for<'e> Executor<'e, Database = DB>,
entity: S,
) -> sqlx::Result<bool>
where
DB: Database + FormatPlaceholder,
DB::QueryResult: RowsAffected,
S::Id: for<'q> Encode<'q, DB> + Type<DB>,
S: BindRow<DB>,
for<'e> <DB as Database>::Arguments<'e>: IntoArguments<'e, DB>,
{
let table_name = S::table_name();
let id_column = S::id_column();
let mut set_sql = String::new();
for (idx, column) in S::columns().iter().enumerate() {
if idx > 0 {
set_sql.push(',');
}
set_sql.push_str(&format!(
"\"{column}\"={placeholder}",
placeholder = DB::format_placeholder(idx)
));
}
let entity_id = entity.id();
let id_placeholder = DB::format_placeholder(S::columns().len());
let sql = format!("UPDATE {table_name} SET {set_sql} WHERE {id_column} = {id_placeholder}");
let mut arguments = DB::Arguments::default();
entity.bind_arguments(&mut arguments)?;
arguments.add(entity_id).map_err(sqlx::Error::Encode)?;
let result = query_with(&sql, arguments).execute(executor).await?;
Ok(result.rows_affected() > 0)
}
pub async fn generic_delete_by_id<S, DB>(
executor: impl for<'e> Executor<'e, Database = DB>,
id: &S::Id,
) -> sqlx::Result<bool>
where
DB: Database + FormatPlaceholder,
DB::QueryResult: RowsAffected,
S::Id: for<'q> Encode<'q, DB> + Type<DB>,
S: Schema<DB>,
for<'e> <DB as Database>::Arguments<'e>: IntoArguments<'e, DB>,
{
let table_name = S::table_name();
let id_column = S::id_column();
let id_placeholder = DB::format_placeholder(0);
let sql = format!("DELETE FROM {table_name} WHERE {id_column} = {id_placeholder}");
let result = query(&sql).bind(id).execute(executor).await?;
Ok(result.rows_affected() > 0)
}