Skip to main content

crudly/sql/
generic.rs

1use crate::HasColumns;
2use crate::HasId;
3use crate::IntoRow;
4use crate::Schema;
5use crate::sql::reusable_executor::ReusableExecutor;
6use sqlx::AssertSqlSafe;
7use sqlx::{
8    Arguments, Database, Encode, Executor, FromRow, IntoArguments, Type, query, query_as,
9    query_with,
10};
11
12/// There is a similar function here: [sqlx::Arguments::format_placeholder] but thats
13/// only really usable via the [sqlx::QueryBuilder] which is highly
14/// undocumented and does not seem to be of great use here.
15pub trait FormatPlaceholder {
16    /// # Arguments
17    /// - `idx`: The index of the placeholder starting at 0
18    fn format_placeholder(idx: usize) -> String;
19}
20
21pub trait RowsAffected {
22    fn rows_affected(&self) -> u64;
23}
24
25pub trait LastInsertedRowId {
26    fn last_insert_rowid(&self) -> i64;
27}
28
29/// # Returns
30/// A string of `n` placeholders separated by commas.
31pub(super) fn format_placeholders<DB: FormatPlaceholder>(n: usize) -> String {
32    (0..n)
33        .map(|idx| DB::format_placeholder(idx))
34        .collect::<Vec<String>>()
35        .join(",")
36}
37
38/// # Returns
39/// A comma-delimited, double-quoted string of every column returned by [`Schema::columns`].
40/// For types with an id field this includes the id column (it is listed first by the derive).
41pub(super) fn comma_delimited_columns<S>() -> String
42where
43    S: Schema,
44{
45    <S as Schema>::columns()
46        .into_iter()
47        .map(|c| format!("\"{c}\""))
48        .collect::<Vec<String>>()
49        .join(",")
50}
51
52pub async fn generic_select_all<S, DB: Database>(
53    executor: impl Executor<'_, Database = DB>,
54) -> sqlx::Result<Vec<S>>
55where
56    S: Schema + HasId + for<'r> FromRow<'r, DB::Row> + Unpin,
57    <DB as Database>::Arguments: IntoArguments<DB>,
58{
59    let columns = comma_delimited_columns::<S>();
60    let table_name = S::table_name();
61    let id_column = S::id_column();
62
63    let sql = format!("SELECT {columns} FROM {table_name} ORDER BY {id_column} ASC;");
64    query_as(AssertSqlSafe(sql)).fetch_all(executor).await
65}
66
67/// Like [`generic_select_all`] but for entities without an id column; selects all columns from
68/// [`Schema::columns`] with no `ORDER BY`.
69pub async fn generic_select_all_no_id<S, DB: Database>(
70    executor: impl Executor<'_, Database = DB>,
71) -> sqlx::Result<Vec<S>>
72where
73    S: Schema + for<'r> FromRow<'r, DB::Row> + Unpin,
74    <DB as Database>::Arguments: IntoArguments<DB>,
75{
76    let columns = comma_delimited_columns::<S>();
77    let table_name = S::table_name();
78    let sql = format!("SELECT {columns} FROM {table_name};");
79    query_as(AssertSqlSafe(sql)).fetch_all(executor).await
80}
81
82pub async fn generic_delete_all<S, DB: Database>(
83    executor: impl Executor<'_, Database = DB>,
84) -> sqlx::Result<()>
85where
86    S: Schema,
87    <DB as Database>::Arguments: IntoArguments<DB>,
88{
89    let table_name = S::table_name();
90    let sql = format!("DELETE FROM {table_name};");
91    query(AssertSqlSafe(sql)).execute(executor).await?;
92    Ok(())
93}
94
95pub async fn generic_select_by_id<S, DB>(
96    executor: impl Executor<'_, Database = DB>,
97    id: &S::Id,
98) -> sqlx::Result<Option<S>>
99where
100    DB: Database + FormatPlaceholder,
101    S::Id: for<'q> Encode<'q, DB> + Type<DB>,
102    S: Schema + HasId + for<'r> FromRow<'r, DB::Row> + Unpin,
103    <DB as Database>::Arguments: IntoArguments<DB>,
104{
105    let columns = comma_delimited_columns::<S>();
106    let table_name = S::table_name();
107    let id_column = S::id_column();
108
109    let placeholder = DB::format_placeholder(0);
110
111    let sql = format!("SELECT {columns} FROM {table_name} WHERE {id_column} = {placeholder};");
112    query_as(AssertSqlSafe(sql))
113        .bind(id)
114        .fetch_optional(executor)
115        .await
116}
117
118pub async fn generic_select_by_ids<S, DB>(
119    executor: impl Executor<'_, Database = DB>,
120    ids: Vec<S::Id>,
121    batch_size: usize,
122) -> sqlx::Result<Vec<S>>
123where
124    DB: Database + FormatPlaceholder,
125    S::Id: for<'q> Encode<'q, DB> + Type<DB>,
126    S: Schema + HasId + for<'r> FromRow<'r, DB::Row> + Unpin,
127    <DB as Database>::Arguments: IntoArguments<DB>,
128{
129    if ids.is_empty() {
130        return Ok(Vec::new());
131    }
132
133    let columns = comma_delimited_columns::<S>();
134    let table_name = S::table_name();
135    let id_column = S::id_column();
136    let ids_len = ids.len();
137    let in_group_size = if batch_size == 0 {
138        ids_len
139    } else {
140        batch_size.min(ids_len)
141    };
142
143    let mut next_placeholder_idx = 0;
144    let mut in_clauses = Vec::new();
145    let mut remaining = ids_len;
146    while remaining > 0 {
147        let current_group = in_group_size.min(remaining);
148        let placeholders = (0..current_group)
149            .map(|_| {
150                let placeholder = DB::format_placeholder(next_placeholder_idx);
151                next_placeholder_idx += 1;
152                placeholder
153            })
154            .collect::<Vec<String>>()
155            .join(",");
156        in_clauses.push(format!("{id_column} IN ({placeholders})"));
157        remaining -= current_group;
158    }
159
160    let sql = format!(
161        "SELECT {columns} FROM {table_name} WHERE {} ORDER BY {id_column} ASC;",
162        in_clauses.join(" OR ")
163    );
164
165    let mut query = query_as(AssertSqlSafe(sql));
166    for id in ids {
167        query = query.bind(id);
168    }
169
170    query.fetch_all(executor).await
171}
172
173pub async fn generic_id_exists<S, DB>(
174    executor: impl Executor<'_, Database = DB>,
175    id: &S::Id,
176) -> sqlx::Result<bool>
177where
178    DB: Database + FormatPlaceholder,
179    S::Id: for<'q> Encode<'q, DB> + Type<DB>,
180    S: Schema + HasId,
181    <DB as Database>::Arguments: IntoArguments<DB>,
182{
183    let table_name = S::table_name();
184    let id_column = S::id_column();
185
186    let placeholder = DB::format_placeholder(0);
187
188    let sql = format!("SELECT 1 FROM {table_name} WHERE \"{id_column}\" = {placeholder};");
189
190    query(AssertSqlSafe(sql))
191        .bind(id)
192        .fetch_optional(executor)
193        .await
194        .map(|option| option.is_some())
195}
196
197pub async fn generic_insert_with_id<S, DB>(
198    executor: impl Executor<'_, Database = DB>,
199    entity: S,
200) -> sqlx::Result<()>
201where
202    DB: Database + FormatPlaceholder,
203    S::Id: for<'q> Encode<'q, DB> + Type<DB>,
204    S: Schema + IntoRow<DB> + HasId,
205    <DB as Database>::Arguments: IntoArguments<DB>,
206{
207    let table_name = S::table_name();
208    let columns = comma_delimited_columns::<S>();
209    let placeholders = format_placeholders::<DB>(<S as Schema>::columns().len());
210
211    let sql = format!("INSERT INTO {table_name} ({columns}) VALUES ({placeholders})");
212
213    let mut arguments = DB::Arguments::default();
214    arguments.add(entity.id()).map_err(sqlx::Error::Encode)?;
215    entity.bind_arguments(&mut arguments)?;
216
217    query_with(AssertSqlSafe(sql), arguments)
218        .execute(executor)
219        .await?;
220    Ok(())
221}
222
223pub async fn generic_insert_without_id<S, DB>(
224    executor: impl Executor<'_, Database = DB>,
225    entity: S,
226) -> sqlx::Result<DB::QueryResult>
227where
228    DB: Database + FormatPlaceholder,
229    S: Schema + IntoRow<DB>,
230    <DB as Database>::Arguments: IntoArguments<DB>,
231{
232    let table_name = S::table_name();
233    let non_id_columns = <S as HasColumns>::columns();
234    let columns = non_id_columns
235        .iter()
236        .map(|c| format!("\"{c}\""))
237        .collect::<Vec<String>>()
238        .join(",");
239
240    let placeholders = format_placeholders::<DB>(non_id_columns.len());
241
242    let sql = format!("INSERT INTO {table_name} ({columns}) VALUES ({placeholders})");
243
244    let mut arguments = DB::Arguments::default();
245    entity.bind_arguments(&mut arguments)?;
246
247    query_with(AssertSqlSafe(sql), arguments)
248        .execute(executor)
249        .await
250}
251
252pub async fn generic_insert_returning_id<S, DB>(
253    executor: impl Executor<'_, Database = DB>,
254    entity: S,
255) -> sqlx::Result<i64>
256where
257    DB: Database + FormatPlaceholder,
258    DB::QueryResult: LastInsertedRowId,
259    S: Schema + IntoRow<DB>,
260    <DB as Database>::Arguments: IntoArguments<DB>,
261{
262    let result = generic_insert_without_id::<S, DB>(executor, entity).await?;
263    Ok(result.last_insert_rowid())
264}
265
266/// Inserts many rows, omitting the id column (same shape as [`generic_insert_returning_id`] per row).
267///
268/// When `batch_size` is `0`, runs a single `INSERT` for all rows. Otherwise splits into multiple
269/// statements of at most `batch_size` rows each.
270pub async fn generic_insert_many_without_id<S, DB, E>(
271    mut executor: E,
272    entities: Vec<S>,
273    batch_size: usize,
274) -> sqlx::Result<()>
275where
276    E: ReusableExecutor<DB>,
277    DB: Database + FormatPlaceholder,
278    S: Schema + IntoRow<DB>,
279    <DB as Database>::Arguments: IntoArguments<DB>,
280{
281    if entities.is_empty() {
282        return Ok(());
283    }
284
285    let table_name = S::table_name();
286    let non_id_columns = <S as HasColumns>::columns();
287    let columns = non_id_columns
288        .iter()
289        .map(|c| format!("\"{c}\""))
290        .collect::<Vec<String>>()
291        .join(",");
292
293    let cols_per_row = non_id_columns.len();
294
295    let mut rest = entities;
296
297    while !rest.is_empty() {
298        let chunk_len = if batch_size == 0 {
299            rest.len()
300        } else {
301            batch_size.min(rest.len())
302        };
303        let chunk = rest.split_off(chunk_len);
304        let chunk_entities = std::mem::replace(&mut rest, chunk);
305
306        let mut placeholder_idx = 0;
307        let mut value_tuples = Vec::with_capacity(chunk_len);
308        for _ in 0..chunk_len {
309            let placeholders = (0..cols_per_row)
310                .map(|_| {
311                    let p = DB::format_placeholder(placeholder_idx);
312                    placeholder_idx += 1;
313                    p
314                })
315                .collect::<Vec<String>>()
316                .join(",");
317            value_tuples.push(format!("({placeholders})"));
318        }
319
320        let sql = format!(
321            "INSERT INTO {table_name} ({columns}) VALUES {}",
322            value_tuples.join(",")
323        );
324
325        let mut arguments = DB::Arguments::default();
326        for entity in chunk_entities {
327            entity.bind_arguments(&mut arguments)?;
328        }
329        executor
330            .execute_query_with(AssertSqlSafe(sql), arguments)
331            .await?;
332    }
333
334    Ok(())
335}
336
337/// Inserts many rows, with the id column (same shape as [`generic_insert_with_id`] per row).
338///
339/// When `batch_size` is `0`, runs a single `INSERT` for all rows. Otherwise splits into multiple
340/// statements of at most `batch_size` rows each.
341pub async fn generic_insert_many_with_id<'c, S, DB, E>(
342    mut executor: E,
343    entities: Vec<S>,
344    batch_size: usize,
345) -> sqlx::Result<()>
346where
347    S::Id: for<'q> Encode<'q, DB> + Type<DB>,
348    E: ReusableExecutor<DB> + Send,
349    DB: Database + FormatPlaceholder,
350    S: Schema + IntoRow<DB> + HasId,
351    <DB as Database>::Arguments: IntoArguments<DB>,
352{
353    if entities.is_empty() {
354        return Ok(());
355    }
356
357    let table_name = S::table_name();
358    let columns = comma_delimited_columns::<S>();
359
360    let cols_per_row = <S as Schema>::columns().len();
361
362    let mut rest = entities;
363
364    while !rest.is_empty() {
365        let chunk_len = if batch_size == 0 {
366            rest.len()
367        } else {
368            batch_size.min(rest.len())
369        };
370        let remainder = rest.split_off(chunk_len);
371
372        let mut placeholder_idx = 0;
373        let mut value_tuples = Vec::with_capacity(chunk_len);
374        for _ in 0..chunk_len {
375            let placeholders = (0..cols_per_row)
376                .map(|_| {
377                    let p = DB::format_placeholder(placeholder_idx);
378                    placeholder_idx += 1;
379                    p
380                })
381                .collect::<Vec<String>>()
382                .join(",");
383            value_tuples.push(format!("({placeholders})"));
384        }
385
386        let sql = format!(
387            "INSERT INTO {table_name} ({columns}) VALUES {}",
388            value_tuples.join(",")
389        );
390
391        let mut arguments = DB::Arguments::default();
392        for entity in rest {
393            arguments.add(entity.id()).map_err(sqlx::Error::Encode)?;
394            entity.bind_arguments(&mut arguments)?;
395        }
396        executor
397            .execute_query_with(AssertSqlSafe(sql), arguments)
398            .await?;
399        rest = remainder;
400    }
401
402    Ok(())
403}
404
405/// # Returns
406/// - Ok(true) if the entity was updated
407/// - Ok(false) if it didn't exist
408/// - Err(e) on error
409pub async fn generic_update_by_id<S, DB>(
410    executor: impl Executor<'_, Database = DB>,
411    entity: S,
412) -> sqlx::Result<bool>
413where
414    DB: Database + FormatPlaceholder,
415    DB::QueryResult: RowsAffected,
416    S::Id: for<'q> Encode<'q, DB> + Type<DB>,
417    S: Schema + IntoRow<DB> + HasId,
418    <DB as Database>::Arguments: IntoArguments<DB>,
419{
420    let table_name = S::table_name();
421    let id_column = S::id_column();
422
423    let mut set_sql = String::new();
424
425    // columns are expected not to be empty
426    for (idx, column) in <S as HasColumns>::columns().iter().enumerate() {
427        if idx > 0 {
428            set_sql.push(',');
429        }
430        set_sql.push_str(&format!(
431            "\"{column}\"={placeholder}",
432            placeholder = DB::format_placeholder(idx)
433        ));
434    }
435
436    let entity_id = entity.id();
437
438    let id_placeholder = DB::format_placeholder(<S as HasColumns>::columns().len());
439    let sql = format!("UPDATE {table_name} SET {set_sql} WHERE {id_column} = {id_placeholder}");
440
441    let mut arguments = DB::Arguments::default();
442    entity.bind_arguments(&mut arguments)?;
443    arguments.add(entity_id).map_err(sqlx::Error::Encode)?;
444
445    let result = query_with(AssertSqlSafe(sql), arguments)
446        .execute(executor)
447        .await?;
448    Ok(result.rows_affected() > 0)
449}
450
451/// # Returns
452/// - Ok(true) if the entity was deleted
453/// - Ok(false) if it didn't exist
454/// - Err(e) on error
455pub async fn generic_delete_by_id<S, DB>(
456    executor: impl Executor<'_, Database = DB>,
457    id: &S::Id,
458) -> sqlx::Result<bool>
459where
460    DB: Database + FormatPlaceholder,
461    DB::QueryResult: RowsAffected,
462    S::Id: for<'q> Encode<'q, DB> + Type<DB>,
463    S: Schema + HasId,
464    <DB as Database>::Arguments: IntoArguments<DB>,
465{
466    let table_name = S::table_name();
467    let id_column = S::id_column();
468    let id_placeholder = DB::format_placeholder(0);
469
470    let sql = format!("DELETE FROM {table_name} WHERE {id_column} = {id_placeholder}");
471
472    let result = query(AssertSqlSafe(sql)).bind(id).execute(executor).await?;
473
474    Ok(result.rows_affected() > 0)
475}
476
477#[cfg(test)]
478pub mod tests {
479    use super::*;
480    use crate::{HasColumns, HasId, IntoRow, Schema};
481    use sqlx::{Sqlite, SqliteConnection, SqlitePool};
482
483    pub struct Dummy;
484
485    impl HasColumns for Dummy {
486        fn columns() -> Vec<&'static str> {
487            unimplemented!()
488        }
489    }
490
491    impl IntoRow<Sqlite> for Dummy {
492        fn bind_arguments(
493            self,
494            _arguments: &mut <Sqlite as Database>::Arguments,
495        ) -> sqlx::Result<()> {
496            unimplemented!()
497        }
498    }
499
500    impl Schema for Dummy {
501        fn table_name() -> &'static str {
502            unimplemented!()
503        }
504
505        fn columns() -> Vec<&'static str> {
506            unimplemented!()
507        }
508    }
509
510    impl HasId for Dummy {
511        type Id = i64;
512
513        fn id(&self) -> Self::Id {
514            unimplemented!()
515        }
516
517        fn id_column() -> &'static str {
518            unimplemented!()
519        }
520    }
521
522    #[allow(dead_code)]
523    async fn test_exec_pool_owned(pool: SqlitePool) {
524        generic_insert_many_without_id(&pool, vec![Dummy], 0)
525            .await
526            .unwrap();
527    }
528
529    #[allow(dead_code)]
530    async fn test_exec_pool_borrowed(pool: &SqlitePool) {
531        generic_insert_many_without_id(pool, vec![Dummy], 0)
532            .await
533            .unwrap();
534
535        generic_insert_many_without_id(pool, vec![Dummy], 0)
536            .await
537            .unwrap();
538    }
539
540    #[allow(dead_code)]
541    async fn test_exec_pool_exclusive(pool: &mut SqlitePool) {
542        generic_insert_many_without_id(&mut *pool, vec![Dummy], 0)
543            .await
544            .unwrap();
545
546        generic_insert_many_without_id(&mut *pool, vec![Dummy], 0)
547            .await
548            .unwrap();
549    }
550
551    #[allow(dead_code)]
552    async fn test_exec_con(con: &mut SqliteConnection) {
553        generic_insert_many_without_id(&mut *con, vec![Dummy], 100)
554            .await
555            .unwrap();
556
557        generic_insert_many_without_id(&mut *con, vec![Dummy], 100)
558            .await
559            .unwrap();
560    }
561}