Skip to main content

che_orm/
connection.rs

1use deadpool_sqlite::{Config, Pool, Runtime};
2use rusqlite::{OptionalExtension, types::Value};
3use std::collections::{HashMap, HashSet};
4use time::format_description::well_known::Rfc3339;
5
6use crate::{
7    BelongsTo, CompiledQuery, DatabaseValue, Expr, HasMany, Model, ModelField, QueryAst,
8    QueryBuildError, QueryValue, SelectQuery, SqlCompiler, SqliteDialect, ValidatedWrite,
9};
10
11#[derive(Debug, thiserror::Error)]
12/// Errors returned by the SQLite pool and ORM runtime.
13pub enum OrmError {
14    #[error("database pool error: {0}")]
15    Pool(#[from] deadpool_sqlite::PoolError),
16    #[error("database interaction error: {0}")]
17    Interaction(String),
18    #[error("sqlite error: {0}")]
19    Sqlite(#[from] rusqlite::Error),
20    #[error("query build error: {0:?}")]
21    QueryBuild(QueryBuildError),
22}
23
24#[derive(Debug, Clone, Copy, PartialEq, Eq)]
25/// Result metadata returned by an executed mutation or DDL statement.
26pub struct ExecuteResult {
27    pub rows_affected: usize,
28    pub last_insert_rowid: Option<i64>,
29}
30
31#[derive(Clone)]
32/// Async SQLite database backed by a `deadpool-sqlite` connection pool.
33pub struct Database {
34    pool: Pool,
35}
36
37impl Database {
38    /// Returns the underlying SQLite pool for advanced driver-specific use.
39    pub fn pool(&self) -> &Pool {
40        &self.pool
41    }
42
43    /// Opens a SQLite database with a pool size of four connections.
44    pub fn connect(path: impl Into<String>) -> Result<Self, OrmError> {
45        Self::connect_with_pool_size(path, 4)
46    }
47
48    /// Opens a SQLite database with an explicit maximum pool size.
49    pub fn connect_with_pool_size(
50        path: impl Into<String>,
51        pool_size: usize,
52    ) -> Result<Self, OrmError> {
53        let config = Config::new(path.into());
54        let mut config = config;
55        config.pool = Some(deadpool_sqlite::PoolConfig::new(pool_size));
56        let pool = config
57            .create_pool(Runtime::Tokio1)
58            .map_err(|error| OrmError::Interaction(error.to_string()))?;
59        Ok(Self { pool })
60    }
61
62    /// Opens an in-memory SQLite database with a single connection.
63    pub fn connect_in_memory() -> Result<Self, OrmError> {
64        Self::connect_with_pool_size(":memory:", 1)
65    }
66
67    /// Executes a compiled AST query asynchronously.
68    pub async fn execute(&self, ast: QueryAst) -> Result<ExecuteResult, OrmError> {
69        ast.validate().map_err(OrmError::QueryBuild)?;
70        let compiled = SqlCompiler::<SqliteDialect>::compile(&ast);
71        self.execute_compiled(compiled).await
72    }
73
74    /// Creates a model table and its indexes.
75    pub async fn create_table<M: Model>(&self) -> Result<ExecuteResult, OrmError> {
76        let schema = M::schema();
77        let compiled = SqlCompiler::<SqliteDialect>::compile_schema(&schema);
78        let pool = self.pool.clone();
79        pool.get()
80            .await?
81            .interact(move |connection| -> rusqlite::Result<ExecuteResult> {
82                configure_connection(connection)?;
83                connection.execute_batch(&compiled.table)?;
84                for index in compiled.indexes {
85                    connection.execute_batch(&index)?;
86                }
87                Ok(ExecuteResult {
88                    rows_affected: 0,
89                    last_insert_rowid: None,
90                })
91            })
92            .await
93            .map_err(|error| OrmError::Interaction(error.to_string()))?
94            .map_err(OrmError::from)
95    }
96
97    /// Inserts a model. Primary key and managed timestamp fields are omitted.
98    pub async fn insert<M: Model>(&self, model: &M) -> Result<ExecuteResult, OrmError> {
99        let ast = QueryAst::Insert(crate::InsertAst {
100            table: crate::TableRef::new(M::table_name()),
101            values: model.insert_values(),
102            returning: Vec::new(),
103        });
104        self.execute(ast).await
105    }
106
107    /// Fetches all rows matching a typed select query.
108    pub async fn fetch_all<M: Model + Send + 'static>(
109        &self,
110        query: crate::SelectQuery<M>,
111    ) -> Result<Vec<M>, OrmError> {
112        let ast = query.into_ast().map_err(OrmError::QueryBuild)?;
113        let compiled = SqlCompiler::<SqliteDialect>::compile(&ast);
114        let pool = self.pool.clone();
115        pool.get()
116            .await?
117            .interact(move |connection| {
118                configure_connection(connection)?;
119                let mut statement = connection.prepare(&compiled.sql)?;
120                let params = sqlite_params(&compiled)?;
121                let rows = statement.query_map(rusqlite::params_from_iter(params), M::from_row)?;
122                rows.collect::<rusqlite::Result<Vec<_>>>()
123            })
124            .await
125            .map_err(|error| OrmError::Interaction(error.to_string()))?
126            .map_err(OrmError::from)
127    }
128
129    /// Fetches at most one row matching a typed select query.
130    pub async fn fetch_one<M: Model + Send + 'static>(
131        &self,
132        query: crate::SelectQuery<M>,
133    ) -> Result<Option<M>, OrmError> {
134        let mut rows = self.fetch_all(query.limit(1)).await?;
135        Ok(rows.pop())
136    }
137
138    /// Fetches a model by its generated integer primary key.
139    pub async fn get<M: Model + Send + 'static>(&self, id: i64) -> Result<Option<M>, OrmError> {
140        self.fetch_one(M::query().filter(M::primary_key().eq(id)))
141            .await
142    }
143
144    /// Fetches every row in a model's table.
145    pub async fn all<M: Model + Send + 'static>(&self) -> Result<Vec<M>, OrmError> {
146        self.fetch_all(M::query()).await
147    }
148
149    pub async fn count<M: Model + Send + 'static>(&self) -> Result<usize, OrmError> {
150        let table = M::table_name();
151        let pool = self.pool.clone();
152        pool.get()
153            .await?
154            .interact(move |connection| {
155                configure_connection(connection)?;
156                connection.query_row(&format!("SELECT COUNT(*) FROM {table}"), [], |row| {
157                    row.get::<_, i64>(0)
158                })
159            })
160            .await
161            .map_err(|error| OrmError::Interaction(error.to_string()))?
162            .map(|count| count as usize)
163            .map_err(OrmError::from)
164    }
165
166    pub async fn count_query<M: Model + Send + 'static>(
167        &self,
168        query: SelectQuery<M>,
169    ) -> Result<usize, OrmError> {
170        let ast = query.into_select_ast().map_err(OrmError::QueryBuild)?;
171        let compiled = SqlCompiler::<SqliteDialect>::compile(&QueryAst::Select(ast));
172        let sql = format!("SELECT COUNT(*) FROM ({})", compiled.sql);
173        let pool = self.pool.clone();
174        pool.get()
175            .await?
176            .interact(move |connection| {
177                configure_connection(connection)?;
178                let params = sqlite_params(&compiled)?;
179                connection.query_row(&sql, rusqlite::params_from_iter(params), |row| {
180                    row.get::<_, i64>(0)
181                })
182            })
183            .await
184            .map_err(|error| OrmError::Interaction(error.to_string()))?
185            .map(|count| count as usize)
186            .map_err(OrmError::from)
187    }
188
189    /// Starts a high-level insert builder.
190    pub fn create<M: Model>(&self) -> CreateBuilder<'_, M> {
191        CreateBuilder {
192            database: self,
193            query: M::insert(),
194        }
195    }
196
197    /// Starts a high-level update builder scoped to one primary key.
198    pub fn update<M: Model>(&self, id: i64) -> UpdateBuilder<'_, M> {
199        UpdateBuilder {
200            database: self,
201            query: M::update().filter(M::primary_key().eq(id)),
202        }
203    }
204
205    /// Deletes one row by its generated integer primary key.
206    pub async fn delete<M: Model + Send + 'static>(&self, id: i64) -> Result<bool, OrmError> {
207        let result = self
208            .execute(
209                M::delete()
210                    .filter(M::primary_key().eq(id))
211                    .into_ast()
212                    .map_err(OrmError::QueryBuild)?,
213            )
214            .await?;
215        Ok(result.rows_affected == 1)
216    }
217
218    /// Starts a high-level typed select builder bound to this database.
219    pub fn query<M: Model>(&self) -> DatabaseQuery<M> {
220        DatabaseQuery { query: M::query() }
221    }
222
223    /// Fetches related rows by a typed model field.
224    ///
225    /// This is the low-level building block for `has_many` and `belongs_to`:
226    /// pass the foreign-key field on the target model and the owner key.
227    pub async fn fetch_by<M, T, V>(
228        &self,
229        field: ModelField<M, T>,
230        value: V,
231    ) -> Result<Vec<M>, OrmError>
232    where
233        M: Model + Send + 'static,
234        V: QueryValue<T>,
235    {
236        self.fetch_all(M::query().filter(field.eq(value))).await
237    }
238
239    /// Fetches rows whose typed field matches one of the supplied values.
240    pub async fn fetch_by_many<M, T, I, V>(
241        &self,
242        field: ModelField<M, T>,
243        values: I,
244    ) -> Result<Vec<M>, OrmError>
245    where
246        M: Model + Send + 'static,
247        I: IntoIterator<Item = V>,
248        V: QueryValue<T>,
249    {
250        const SQLITE_SAFE_VARIABLE_LIMIT: usize = 900;
251        let values = values
252            .into_iter()
253            .map(QueryValue::<T>::into_query_value)
254            .collect::<Vec<_>>();
255        if values.is_empty() {
256            return Ok(Vec::new());
257        }
258        let mut result = Vec::new();
259        for chunk in values.chunks(SQLITE_SAFE_VARIABLE_LIMIT) {
260            let filter = Expr::In {
261                left: Box::new(Expr::Column(field.column())),
262                values: chunk.to_vec(),
263            };
264            result.extend(self.fetch_all(M::query().filter(filter)).await?);
265        }
266        Ok(result)
267    }
268
269    /// Runs a blocking SQLite closure in a pool worker transaction.
270    pub async fn transaction<T, F>(&self, action: F) -> Result<T, OrmError>
271    where
272        T: Send + 'static,
273        F: FnOnce(&rusqlite::Connection) -> rusqlite::Result<T> + Send + 'static,
274    {
275        let pool = self.pool.clone();
276        pool.get()
277            .await?
278            .interact(move |connection| -> rusqlite::Result<T> {
279                configure_connection(connection)?;
280                connection.execute_batch("BEGIN")?;
281                match std::panic::catch_unwind(std::panic::AssertUnwindSafe(|| action(connection)))
282                {
283                    Ok(Ok(value)) => match connection.execute_batch("COMMIT") {
284                        Ok(()) => Ok(value),
285                        Err(error) => {
286                            let _ = connection.execute_batch("ROLLBACK");
287                            Err(error)
288                        }
289                    },
290                    Ok(Err(error)) => {
291                        let _ = connection.execute_batch("ROLLBACK");
292                        Err(error)
293                    }
294                    Err(payload) => {
295                        let _ = connection.execute_batch("ROLLBACK");
296                        std::panic::resume_unwind(payload);
297                    }
298                }
299            })
300            .await
301            .map_err(|error| OrmError::Interaction(error.to_string()))?
302            .map_err(OrmError::from)
303    }
304
305    async fn execute_compiled(&self, compiled: CompiledQuery) -> Result<ExecuteResult, OrmError> {
306        let pool = self.pool.clone();
307        pool.get()
308            .await?
309            .interact(move |connection| -> rusqlite::Result<ExecuteResult> {
310                configure_connection(connection)?;
311                let params = sqlite_params(&compiled)?;
312                let rows_affected =
313                    connection.execute(&compiled.sql, rusqlite::params_from_iter(params))?;
314                Ok(ExecuteResult {
315                    rows_affected,
316                    last_insert_rowid: Some(connection.last_insert_rowid()),
317                })
318            })
319            .await
320            .map_err(|error| OrmError::Interaction(error.to_string()))?
321            .map_err(OrmError::from)
322    }
323
324    async fn execute_returning<M: Model + Send + 'static>(
325        &self,
326        ast: QueryAst,
327    ) -> Result<Option<M>, OrmError> {
328        ast.validate().map_err(OrmError::QueryBuild)?;
329        let compiled = SqlCompiler::<SqliteDialect>::compile(&ast);
330        let pool = self.pool.clone();
331        pool.get()
332            .await?
333            .interact(move |connection| {
334                configure_connection(connection)?;
335                let mut statement = connection.prepare(&compiled.sql)?;
336                let params = sqlite_params(&compiled)?;
337                statement
338                    .query_row(rusqlite::params_from_iter(params), M::from_row)
339                    .optional()
340            })
341            .await
342            .map_err(|error| OrmError::Interaction(error.to_string()))?
343            .map_err(OrmError::from)
344    }
345}
346
347impl<M: Model + Send + 'static> ValidatedWrite<M> {
348    pub async fn save(self, database: &Database) -> Result<Option<M>, OrmError> {
349        let ast = match self {
350            Self::Create(query) => query
351                .returning_all()
352                .into_ast()
353                .map_err(OrmError::QueryBuild)?,
354            Self::Update(query) => query
355                .returning_all()
356                .into_ast()
357                .map_err(OrmError::QueryBuild)?,
358        };
359        database.execute_returning(ast).await
360    }
361}
362
363/// High-level insert builder. The executed result is the complete inserted model.
364pub struct CreateBuilder<'db, M> {
365    database: &'db Database,
366    query: crate::InsertQuery<M>,
367}
368
369impl<M: Model> CreateBuilder<'_, M> {
370    pub fn set<T, V>(self, field: ModelField<M, T>, value: V) -> Self
371    where
372        V: QueryValue<T>,
373    {
374        Self {
375            database: self.database,
376            query: self.query.set(field, value),
377        }
378    }
379
380    pub async fn execute(self) -> Result<M, OrmError>
381    where
382        M: Send + 'static,
383    {
384        let ast = self
385            .query
386            .returning_all()
387            .into_ast()
388            .map_err(OrmError::QueryBuild)?;
389        self.database
390            .execute_returning(ast)
391            .await?
392            .ok_or_else(|| OrmError::Interaction("insert did not return a row".into()))
393    }
394}
395
396/// High-level update builder scoped to one model primary key.
397pub struct UpdateBuilder<'db, M> {
398    database: &'db Database,
399    query: crate::UpdateQuery<M>,
400}
401
402impl<M: Model> UpdateBuilder<'_, M> {
403    pub fn set<T, V>(self, field: ModelField<M, T>, value: V) -> Self
404    where
405        V: QueryValue<T>,
406    {
407        Self {
408            database: self.database,
409            query: self.query.set(field, value),
410        }
411    }
412
413    pub async fn execute(self) -> Result<Option<M>, OrmError>
414    where
415        M: Send + 'static,
416    {
417        let ast = self
418            .query
419            .returning_all()
420            .into_ast()
421            .map_err(OrmError::QueryBuild)?;
422        self.database.execute_returning(ast).await
423    }
424}
425
426/// High-level typed select builder bound to a database.
427pub struct DatabaseQuery<M> {
428    query: SelectQuery<M>,
429}
430
431impl<M: Model> DatabaseQuery<M> {
432    pub fn new(query: SelectQuery<M>) -> Self {
433        Self { query }
434    }
435
436    pub fn filter(self, expr: Expr) -> Self {
437        Self {
438            query: self.query.filter(expr),
439        }
440    }
441
442    pub fn order_by(self, order: crate::OrderBy) -> Self {
443        Self {
444            query: self.query.order_by(order),
445        }
446    }
447
448    pub fn limit(self, limit: u64) -> Self {
449        Self {
450            query: self.query.limit(limit),
451        }
452    }
453
454    pub fn offset(self, offset: u64) -> Self {
455        Self {
456            query: self.query.offset(offset),
457        }
458    }
459
460    /// Loads one foreign-key relation without per-row queries.
461    pub fn select_related<R, Relation, Key>(
462        self,
463        relation: BelongsTo<M, R, Relation, Key>,
464    ) -> SelectRelatedQuery<M, R, Relation, Key> {
465        SelectRelatedQuery {
466            query: self.query,
467            relation,
468        }
469    }
470
471    /// Loads a reverse foreign-key relation with one batched query.
472    pub fn prefetch_related<P>(self, plan: P) -> P::Query
473    where
474        P: PrefetchRelation<M>,
475    {
476        plan.into_query(self.query)
477    }
478
479    pub async fn all(self, database: &Database) -> Result<Vec<M>, OrmError>
480    where
481        M: Send + 'static,
482    {
483        database.fetch_all(self.query).await
484    }
485
486    pub async fn first(self, database: &Database) -> Result<Option<M>, OrmError>
487    where
488        M: Send + 'static,
489    {
490        database.fetch_one(self.query).await
491    }
492
493    pub async fn count(self, database: &Database) -> Result<usize, OrmError>
494    where
495        M: Send + 'static,
496    {
497        database.count_query(self.query).await
498    }
499
500    pub fn into_select_query(self) -> SelectQuery<M> {
501        self.query
502    }
503}
504
505pub trait PrefetchRelation<Owner: Model> {
506    type Query;
507
508    fn into_query(self, query: SelectQuery<Owner>) -> Self::Query;
509}
510
511/// A model and its eagerly loaded `belongs_to` relation.
512#[derive(Debug)]
513pub struct WithOne<M, R, Relation = (), Key = i64> {
514    pub model: M,
515    pub related: R,
516    pub(crate) _relation: std::marker::PhantomData<Relation>,
517    pub(crate) _key: std::marker::PhantomData<Key>,
518}
519
520#[derive(Debug)]
521pub struct WithOptionalOne<M, R, Relation = ()> {
522    pub model: M,
523    pub related: Option<R>,
524    pub(crate) _relation: std::marker::PhantomData<Relation>,
525}
526
527/// A model and its eagerly loaded reverse relation.
528#[derive(Debug)]
529pub struct WithMany<M, R, Relation = (), Key = i64> {
530    pub model: M,
531    pub related: Vec<R>,
532    pub(crate) _relation: std::marker::PhantomData<Relation>,
533    pub(crate) _key: std::marker::PhantomData<Key>,
534}
535
536/// A materialized model and a tuple of preloaded relations.
537#[derive(Debug)]
538pub struct Loaded<M, Relations> {
539    pub model: M,
540    pub relations: Relations,
541}
542
543/// One reverse relation inside a materialized relation tuple.
544#[derive(Debug)]
545pub struct LoadedMany<R, Relation = ()> {
546    pub related: Vec<R>,
547    pub(crate) _relation: std::marker::PhantomData<Relation>,
548}
549
550#[derive(Debug)]
551pub struct LoadedOne<R, Relation = ()> {
552    pub related: R,
553    pub(crate) _relation: std::marker::PhantomData<Relation>,
554}
555
556/// Query which materializes a model together with one related model.
557pub struct SelectRelatedQuery<M, R, Relation = (), Key = i64> {
558    query: SelectQuery<M>,
559    relation: BelongsTo<M, R, Relation, Key>,
560}
561
562impl<M, R, Relation> SelectRelatedQuery<M, R, Relation, i64>
563where
564    M: Model + Send + 'static,
565    R: Model + Send + 'static,
566{
567    pub fn filter(self, expr: Expr) -> Self {
568        Self {
569            query: self.query.filter(expr),
570            relation: self.relation,
571        }
572    }
573
574    pub fn order_by(self, order: crate::OrderBy) -> Self {
575        Self {
576            query: self.query.order_by(order),
577            relation: self.relation,
578        }
579    }
580
581    pub fn limit(self, limit: u64) -> Self {
582        Self {
583            query: self.query.limit(limit),
584            relation: self.relation,
585        }
586    }
587
588    pub fn offset(self, offset: u64) -> Self {
589        Self {
590            query: self.query.offset(offset),
591            relation: self.relation,
592        }
593    }
594
595    pub fn select_related<R2, Relation2>(
596        self,
597        relation: BelongsTo<M, R2, Relation2, i64>,
598    ) -> MultiSelectRelatedQuery<M, R, Relation, R2, Relation2> {
599        MultiSelectRelatedQuery {
600            query: self.query,
601            first: self.relation,
602            second: relation,
603        }
604    }
605
606    pub async fn all(self, database: &Database) -> Result<Vec<WithOne<M, R, Relation>>, OrmError>
607    where
608        Relation: Send + 'static,
609    {
610        let ast = self
611            .query
612            .into_joined_ast(self.relation)
613            .map_err(OrmError::QueryBuild)?;
614        let compiled = SqlCompiler::<SqliteDialect>::compile(&ast);
615        let pool = database.pool.clone();
616        pool.get()
617            .await?
618            .interact(move |connection| {
619                configure_connection(connection)?;
620                load_joined_models::<M, R, Relation>(connection, &compiled)
621            })
622            .await
623            .map_err(|error| OrmError::Interaction(error.to_string()))?
624            .map_err(OrmError::from)
625    }
626
627    pub async fn count(self, database: &Database) -> Result<usize, OrmError> {
628        database.count_query(self.query).await
629    }
630}
631
632pub struct MultiSelectRelatedQuery<M, R1, Relation1, R2, Relation2> {
633    query: SelectQuery<M>,
634    first: BelongsTo<M, R1, Relation1, i64>,
635    second: BelongsTo<M, R2, Relation2, i64>,
636}
637
638impl<M, R1, Relation1, R2, Relation2> MultiSelectRelatedQuery<M, R1, Relation1, R2, Relation2>
639where
640    M: Model + Send + 'static,
641    R1: Model + Send + 'static,
642    R2: Model + Send + 'static,
643    Relation1: Send + 'static,
644    Relation2: Send + 'static,
645{
646    pub fn filter(self, expr: Expr) -> Self {
647        Self {
648            query: self.query.filter(expr),
649            first: self.first,
650            second: self.second,
651        }
652    }
653
654    pub fn order_by(self, order: crate::OrderBy) -> Self {
655        Self {
656            query: self.query.order_by(order),
657            first: self.first,
658            second: self.second,
659        }
660    }
661
662    pub async fn all(
663        self,
664        database: &Database,
665    ) -> Result<Vec<Loaded<M, (LoadedOne<R1, Relation1>, LoadedOne<R2, Relation2>)>>, OrmError>
666    {
667        let first = self.first;
668        let second = self.second;
669        let mut ast = self
670            .query
671            .into_joined_ast(first)
672            .map_err(OrmError::QueryBuild)?;
673        if let QueryAst::Select(select) = &mut ast {
674            select.columns.extend(
675                R2::columns()
676                    .iter()
677                    .map(|column| crate::ColumnRef::new(second.alias(), column)),
678            );
679            select.joins.push(crate::JoinAst {
680                table: crate::TableRef::new(R2::table_name()),
681                alias: second.alias(),
682                kind: crate::JoinType::Inner,
683                on: crate::Expr::Compare {
684                    left: Box::new(crate::Expr::Column(second.field())),
685                    op: crate::CompareOp::Eq,
686                    right: Box::new(crate::Expr::Column(crate::ColumnRef::new(
687                        second.alias(),
688                        R2::primary_key().column().name,
689                    ))),
690                },
691            });
692        }
693        let compiled = SqlCompiler::<SqliteDialect>::compile(&ast);
694        let pool = database.pool.clone();
695        pool.get()
696            .await?
697            .interact(move |connection| {
698                configure_connection(connection)?;
699                load_multi_joined_models::<M, R1, Relation1, R2, Relation2>(connection, &compiled)
700            })
701            .await
702            .map_err(|error| OrmError::Interaction(error.to_string()))?
703            .map_err(OrmError::from)
704    }
705}
706
707impl<M, R, Relation> SelectRelatedQuery<M, R, Relation, Option<i64>>
708where
709    M: Model + Send + 'static,
710    R: Model + Send + 'static,
711{
712    pub fn filter(self, expr: Expr) -> Self {
713        Self {
714            query: self.query.filter(expr),
715            relation: self.relation,
716        }
717    }
718
719    pub fn order_by(self, order: crate::OrderBy) -> Self {
720        Self {
721            query: self.query.order_by(order),
722            relation: self.relation,
723        }
724    }
725
726    pub fn limit(self, limit: u64) -> Self {
727        Self {
728            query: self.query.limit(limit),
729            relation: self.relation,
730        }
731    }
732
733    pub fn offset(self, offset: u64) -> Self {
734        Self {
735            query: self.query.offset(offset),
736            relation: self.relation,
737        }
738    }
739
740    pub async fn all(
741        self,
742        database: &Database,
743    ) -> Result<Vec<WithOptionalOne<M, R, Relation>>, OrmError>
744    where
745        Relation: Send + 'static,
746    {
747        let ast = self
748            .query
749            .into_optional_joined_ast(self.relation)
750            .map_err(OrmError::QueryBuild)?;
751        let compiled = SqlCompiler::<SqliteDialect>::compile(&ast);
752        let pool = database.pool.clone();
753        pool.get()
754            .await?
755            .interact(move |connection| {
756                configure_connection(connection)?;
757                load_optional_joined_models::<M, R, Relation>(connection, &compiled)
758            })
759            .await
760            .map_err(|error| OrmError::Interaction(error.to_string()))?
761            .map_err(OrmError::from)
762    }
763
764    pub async fn count(self, database: &Database) -> Result<usize, OrmError> {
765        database.count_query(self.query).await
766    }
767}
768
769/// Query which materializes a model together with its reverse relation.
770pub struct PrefetchRelatedQuery<M, R, Relation = (), Key = i64> {
771    query: SelectQuery<M>,
772    relation: HasMany<M, R, Relation, Key>,
773}
774
775impl<M: Model, R, Relation> PrefetchRelation<M> for HasMany<M, R, Relation, i64> {
776    type Query = PrefetchRelatedQuery<M, R, Relation, i64>;
777
778    fn into_query(self, query: SelectQuery<M>) -> Self::Query {
779        PrefetchRelatedQuery {
780            query,
781            relation: self,
782        }
783    }
784}
785
786pub struct NestedPrefetchPlan<M, R, Relation, C, ChildRelation> {
787    outer: HasMany<M, R, Relation, i64>,
788    inner: HasMany<R, C, ChildRelation, i64>,
789}
790
791impl<M, R, Relation> HasMany<M, R, Relation, i64> {
792    pub fn prefetch<C, ChildRelation>(
793        self,
794        inner: HasMany<R, C, ChildRelation, i64>,
795    ) -> NestedPrefetchPlan<M, R, Relation, C, ChildRelation> {
796        NestedPrefetchPlan { outer: self, inner }
797    }
798}
799
800impl<M: Model, R, Relation, C, ChildRelation> PrefetchRelation<M>
801    for NestedPrefetchPlan<M, R, Relation, C, ChildRelation>
802{
803    type Query = NestedPrefetchQuery<M, R, Relation, C, ChildRelation>;
804
805    fn into_query(self, query: SelectQuery<M>) -> Self::Query {
806        NestedPrefetchQuery {
807            query,
808            outer: self.outer,
809            inner: self.inner,
810        }
811    }
812}
813
814pub struct NestedPrefetchQuery<M, R, Relation, C, ChildRelation> {
815    query: SelectQuery<M>,
816    outer: HasMany<M, R, Relation, i64>,
817    inner: HasMany<R, C, ChildRelation, i64>,
818}
819
820impl<M, R, Relation> PrefetchRelatedQuery<M, R, Relation, i64>
821where
822    M: Model + Send + 'static,
823    R: Model + Send + 'static,
824{
825    pub fn filter(self, expr: Expr) -> Self {
826        Self {
827            query: self.query.filter(expr),
828            relation: self.relation,
829        }
830    }
831
832    pub fn order_by(self, order: crate::OrderBy) -> Self {
833        Self {
834            query: self.query.order_by(order),
835            relation: self.relation,
836        }
837    }
838
839    pub fn limit(self, limit: u64) -> Self {
840        Self {
841            query: self.query.limit(limit),
842            relation: self.relation,
843        }
844    }
845
846    pub fn offset(self, offset: u64) -> Self {
847        Self {
848            query: self.query.offset(offset),
849            relation: self.relation,
850        }
851    }
852
853    pub fn prefetch_related<R2, Relation2>(
854        self,
855        relation: HasMany<M, R2, Relation2>,
856    ) -> MultiPrefetchRelatedQuery<M, R, Relation, R2, Relation2> {
857        MultiPrefetchRelatedQuery {
858            query: self.query,
859            first: self.relation,
860            second: relation,
861        }
862    }
863
864    pub async fn all(
865        self,
866        database: &Database,
867    ) -> Result<Vec<Loaded<M, (LoadedMany<R, Relation>,)>>, OrmError>
868    where
869        Relation: Send + 'static,
870    {
871        let parent_ast = self.query.into_ast().map_err(OrmError::QueryBuild)?;
872        let parent_compiled = SqlCompiler::<SqliteDialect>::compile(&parent_ast);
873        let relation = self.relation;
874        let pool = database.pool.clone();
875        pool.get()
876            .await?
877            .interact(move |connection| {
878                configure_connection(connection)?;
879                connection.execute_batch("BEGIN")?;
880                let result: rusqlite::Result<Vec<Loaded<M, (LoadedMany<R, Relation>,)>>> = (|| {
881                    let parents = load_models::<M>(connection, &parent_compiled)?;
882                    let parent_ids = parents
883                        .iter()
884                        .map(Model::primary_key_value)
885                        .collect::<HashSet<_>>()
886                        .into_iter()
887                        .collect::<Vec<_>>();
888                    let field = relation.field();
889                    let related = load_models_by_ids(
890                        connection,
891                        ModelField::<R, i64>::new(field.table, field.name),
892                        parent_ids,
893                    )?;
894                    let mut related_by_parent = HashMap::<i64, Vec<R>>::new();
895                    for model in related {
896                        related_by_parent
897                            .entry(relation.foreign_key(&model))
898                            .or_default()
899                            .push(model);
900                    }
901                    Ok(parents
902                        .into_iter()
903                        .map(|model| {
904                            let id = model.primary_key_value();
905                            Loaded {
906                                model,
907                                relations: (LoadedMany {
908                                    related: related_by_parent.remove(&id).unwrap_or_default(),
909                                    _relation: std::marker::PhantomData,
910                                },),
911                            }
912                        })
913                        .collect())
914                })(
915                );
916                match result {
917                    Ok(value) => {
918                        connection.execute_batch("COMMIT")?;
919                        Ok(value)
920                    }
921                    Err(error) => {
922                        let _ = connection.execute_batch("ROLLBACK");
923                        Err(error)
924                    }
925                }
926            })
927            .await
928            .map_err(|error| OrmError::Interaction(error.to_string()))?
929            .map_err(OrmError::from)
930    }
931
932    pub async fn count(self, database: &Database) -> Result<usize, OrmError> {
933        database.count_query(self.query).await
934    }
935}
936
937/// Query which materializes two reverse relations in one typed result.
938pub struct MultiPrefetchRelatedQuery<M, R1, Relation1, R2, Relation2> {
939    query: SelectQuery<M>,
940    first: HasMany<M, R1, Relation1>,
941    second: HasMany<M, R2, Relation2>,
942}
943
944impl<M, R, Relation, C, ChildRelation> NestedPrefetchQuery<M, R, Relation, C, ChildRelation>
945where
946    M: Model + Send + 'static,
947    R: Model + Send + 'static,
948    C: Model + Send + 'static,
949    Relation: Send + 'static,
950    ChildRelation: Send + 'static,
951{
952    pub async fn all(
953        self,
954        database: &Database,
955    ) -> Result<
956        Vec<Loaded<M, (LoadedMany<Loaded<R, (LoadedMany<C, ChildRelation>,)>, Relation>,)>>,
957        OrmError,
958    > {
959        let parents = database.fetch_all(self.query).await?;
960        let parent_ids = parents
961            .iter()
962            .map(Model::primary_key_value)
963            .collect::<HashSet<_>>()
964            .into_iter()
965            .collect::<Vec<_>>();
966        let outer_field = self.outer.field();
967        let children = database
968            .fetch_by_many(
969                ModelField::<R, i64>::new(outer_field.table, outer_field.name),
970                parent_ids,
971            )
972            .await?;
973        let child_ids = children
974            .iter()
975            .map(Model::primary_key_value)
976            .collect::<HashSet<_>>()
977            .into_iter()
978            .collect::<Vec<_>>();
979        let inner_field = self.inner.field();
980        let grandchildren = database
981            .fetch_by_many(
982                ModelField::<C, i64>::new(inner_field.table, inner_field.name),
983                child_ids,
984            )
985            .await?;
986        let mut grandchildren_by_child = HashMap::<i64, Vec<C>>::new();
987        for model in grandchildren {
988            grandchildren_by_child
989                .entry(self.inner.foreign_key(&model))
990                .or_default()
991                .push(model);
992        }
993        let children = children
994            .into_iter()
995            .map(|model| {
996                let id = model.primary_key_value();
997                Loaded {
998                    model,
999                    relations: (LoadedMany {
1000                        related: grandchildren_by_child.remove(&id).unwrap_or_default(),
1001                        _relation: std::marker::PhantomData,
1002                    },),
1003                }
1004            })
1005            .collect::<Vec<_>>();
1006        let mut children_by_parent = HashMap::<i64, Vec<_>>::new();
1007        for child in children {
1008            children_by_parent
1009                .entry(self.outer.foreign_key(&child.model))
1010                .or_default()
1011                .push(child);
1012        }
1013        Ok(parents
1014            .into_iter()
1015            .map(|model| {
1016                let id = model.primary_key_value();
1017                Loaded {
1018                    model,
1019                    relations: (LoadedMany {
1020                        related: children_by_parent.remove(&id).unwrap_or_default(),
1021                        _relation: std::marker::PhantomData,
1022                    },),
1023                }
1024            })
1025            .collect())
1026    }
1027}
1028
1029impl<M, R1, Relation1, R2, Relation2> MultiPrefetchRelatedQuery<M, R1, Relation1, R2, Relation2>
1030where
1031    M: Model + Send + 'static,
1032    R1: Model + Send + 'static,
1033    R2: Model + Send + 'static,
1034    Relation1: Send + 'static,
1035    Relation2: Send + 'static,
1036{
1037    pub async fn all(
1038        self,
1039        database: &Database,
1040    ) -> Result<Vec<Loaded<M, (LoadedMany<R1, Relation1>, LoadedMany<R2, Relation2>)>>, OrmError>
1041    {
1042        let parents = database.fetch_all(self.query).await?;
1043        let parent_ids = parents
1044            .iter()
1045            .map(Model::primary_key_value)
1046            .collect::<HashSet<_>>()
1047            .into_iter()
1048            .collect::<Vec<_>>();
1049        let first_field = self.first.field();
1050        let second_field = self.second.field();
1051        let first = database
1052            .fetch_by_many(
1053                ModelField::<R1, i64>::new(first_field.table, first_field.name),
1054                parent_ids.clone(),
1055            )
1056            .await?;
1057        let second = database
1058            .fetch_by_many(
1059                ModelField::<R2, i64>::new(second_field.table, second_field.name),
1060                parent_ids,
1061            )
1062            .await?;
1063        let mut first_by_parent = HashMap::<i64, Vec<R1>>::new();
1064        for model in first {
1065            first_by_parent
1066                .entry(self.first.foreign_key(&model))
1067                .or_default()
1068                .push(model);
1069        }
1070        let mut second_by_parent = HashMap::<i64, Vec<R2>>::new();
1071        for model in second {
1072            second_by_parent
1073                .entry(self.second.foreign_key(&model))
1074                .or_default()
1075                .push(model);
1076        }
1077
1078        Ok(parents
1079            .into_iter()
1080            .map(|model| {
1081                let id = model.primary_key_value();
1082                Loaded {
1083                    model,
1084                    relations: (
1085                        LoadedMany {
1086                            related: first_by_parent.remove(&id).unwrap_or_default(),
1087                            _relation: std::marker::PhantomData,
1088                        },
1089                        LoadedMany {
1090                            related: second_by_parent.remove(&id).unwrap_or_default(),
1091                            _relation: std::marker::PhantomData,
1092                        },
1093                    ),
1094                }
1095            })
1096            .collect())
1097    }
1098}
1099
1100fn load_joined_models<M: Model, R: Model, Relation>(
1101    connection: &rusqlite::Connection,
1102    compiled: &CompiledQuery,
1103) -> rusqlite::Result<Vec<WithOne<M, R, Relation>>> {
1104    let mut statement = connection.prepare(&compiled.sql)?;
1105    let params = sqlite_params(compiled)?;
1106    let offset = M::columns().len();
1107    let rows = statement.query_map(rusqlite::params_from_iter(params), |row| {
1108        Ok(WithOne {
1109            model: M::from_row_at(row, 0)?,
1110            related: R::from_row_at(row, offset)?,
1111            _relation: std::marker::PhantomData,
1112            _key: std::marker::PhantomData,
1113        })
1114    })?;
1115    rows.collect()
1116}
1117
1118fn load_optional_joined_models<M: Model, R: Model, Relation>(
1119    connection: &rusqlite::Connection,
1120    compiled: &CompiledQuery,
1121) -> rusqlite::Result<Vec<WithOptionalOne<M, R, Relation>>> {
1122    let mut statement = connection.prepare(&compiled.sql)?;
1123    let params = sqlite_params(compiled)?;
1124    let offset = M::columns().len();
1125    let related_primary_key_offset = R::columns()
1126        .iter()
1127        .position(|column| *column == R::primary_key().column().name)
1128        .expect("primary key must be one of the model columns");
1129    let rows = statement.query_map(rusqlite::params_from_iter(params), |row| {
1130        let related_id: Option<i64> = row.get(offset + related_primary_key_offset)?;
1131        Ok(WithOptionalOne {
1132            model: M::from_row_at(row, 0)?,
1133            related: related_id
1134                .map(|_| R::from_row_at(row, offset))
1135                .transpose()?,
1136            _relation: std::marker::PhantomData,
1137        })
1138    })?;
1139    rows.collect()
1140}
1141
1142fn load_multi_joined_models<M: Model, R1: Model, Relation1, R2: Model, Relation2>(
1143    connection: &rusqlite::Connection,
1144    compiled: &CompiledQuery,
1145) -> rusqlite::Result<Vec<Loaded<M, (LoadedOne<R1, Relation1>, LoadedOne<R2, Relation2>)>>> {
1146    let mut statement = connection.prepare(&compiled.sql)?;
1147    let params = sqlite_params(compiled)?;
1148    let first_offset = M::columns().len();
1149    let second_offset = first_offset + R1::columns().len();
1150    let rows = statement.query_map(rusqlite::params_from_iter(params), |row| {
1151        Ok(Loaded {
1152            model: M::from_row_at(row, 0)?,
1153            relations: (
1154                LoadedOne {
1155                    related: R1::from_row_at(row, first_offset)?,
1156                    _relation: std::marker::PhantomData,
1157                },
1158                LoadedOne {
1159                    related: R2::from_row_at(row, second_offset)?,
1160                    _relation: std::marker::PhantomData,
1161                },
1162            ),
1163        })
1164    })?;
1165    rows.collect()
1166}
1167
1168fn load_models<M: Model>(
1169    connection: &rusqlite::Connection,
1170    compiled: &CompiledQuery,
1171) -> rusqlite::Result<Vec<M>> {
1172    let mut statement = connection.prepare(&compiled.sql)?;
1173    let params = sqlite_params(compiled)?;
1174    let rows = statement.query_map(rusqlite::params_from_iter(params), M::from_row)?;
1175    rows.collect()
1176}
1177
1178fn load_models_by_ids<M: Model>(
1179    connection: &rusqlite::Connection,
1180    field: ModelField<M, i64>,
1181    ids: Vec<i64>,
1182) -> rusqlite::Result<Vec<M>> {
1183    const SQLITE_SAFE_VARIABLE_LIMIT: usize = 900;
1184    let mut result = Vec::new();
1185    for chunk in ids.chunks(SQLITE_SAFE_VARIABLE_LIMIT) {
1186        let ast = M::query()
1187            .filter(Expr::In {
1188                left: Box::new(Expr::Column(field.column())),
1189                values: chunk.iter().copied().map(DatabaseValue::Integer).collect(),
1190            })
1191            .into_ast()
1192            .map_err(|_| rusqlite::Error::InvalidQuery)?;
1193        let compiled = SqlCompiler::<SqliteDialect>::compile(&ast);
1194        result.extend(load_models(connection, &compiled)?);
1195    }
1196    Ok(result)
1197}
1198
1199fn configure_connection(connection: &rusqlite::Connection) -> rusqlite::Result<()> {
1200    connection.execute_batch("PRAGMA foreign_keys = ON")
1201}
1202
1203fn sqlite_params(compiled: &CompiledQuery) -> rusqlite::Result<Vec<Value>> {
1204    compiled.params.iter().map(database_value).collect()
1205}
1206
1207fn database_value(value: &DatabaseValue) -> rusqlite::Result<Value> {
1208    match value {
1209        DatabaseValue::Integer(value) => Ok(Value::Integer(*value)),
1210        DatabaseValue::Text(value) => Ok(Value::Text(value.clone())),
1211        DatabaseValue::Boolean(value) => Ok(Value::Integer(i64::from(*value))),
1212        DatabaseValue::DateTime(value) => value
1213            .format(&Rfc3339)
1214            .map(Value::Text)
1215            .map_err(|error| rusqlite::Error::ToSqlConversionFailure(error.into())),
1216        DatabaseValue::Null => Ok(Value::Null),
1217    }
1218}