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
12pub trait FormatPlaceholder {
16 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
29pub(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
38pub(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
67pub 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
266pub 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
337pub 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
405pub 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 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
451pub 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}