Skip to main content

quokka_database/
service.rs

1use std::future::Future;
2
3pub use quokka_macros::{
4    repository_execute, repository_execute as execute, repository_query_all,
5    repository_query_all as query_all, repository_query_one, repository_query_one as query_one,
6    repository_query_optional, repository_query_optional as query_optional,
7};
8use sqlx::{query_builder::Separated, QueryBuilder};
9
10use crate::{
11    error::{Error, Result},
12    Database,
13};
14use quokka_state::{FromState, ProvideState};
15
16///
17/// Provides paginated access to any entity of your liking by using the [PaginatedResult].
18///
19#[derive(Clone, Debug)]
20pub struct SearchService {
21    database: Database,
22}
23
24#[derive(Clone, Debug, serde::Deserialize, serde::Serialize)]
25pub struct PaginatedResult<E> {
26    pub page: u64,
27    pub total_pages: u64,
28    pub total_items: u64,
29    pub per_page: u64,
30    pub next_page: Option<u64>,
31    pub previous_page: Option<u64>,
32    pub items: Vec<E>,
33}
34
35pub trait SearchCriteria: Send {
36    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>);
37}
38
39/// `AND`s any amount of statements
40pub struct AndCriteria {
41    pub criteria: Vec<Box<dyn SearchCriteria>>,
42}
43
44/// `OR`s any amount of statements
45pub struct OrCriteria {
46    pub criteria: Vec<Box<dyn SearchCriteria>>,
47}
48
49/// Checks a column aainst a value using the `=` operator
50pub struct EqCriteria {
51    pub column: &'static str,
52    pub value: Box<dyn Bindable<sqlx::Postgres>>,
53}
54
55/// Checks a column aainst a value using the `!=` operator
56pub struct NeCriteria {
57    pub column: &'static str,
58    pub value: Box<dyn Bindable<sqlx::Postgres>>,
59}
60
61/// Checks a column aainst a value using the `LIKE` operator
62pub struct LikeCriteria {
63    pub column: &'static str,
64    pub value: Box<dyn Bindable<sqlx::Postgres>>,
65}
66
67/// Checks a column aainst a value using the `>` operator
68pub struct GtCriteria {
69    pub column: &'static str,
70    pub value: Box<dyn Bindable<sqlx::Postgres>>,
71}
72
73/// Checks a column aainst a value using the `>=` operator
74pub struct GteCriteria {
75    pub column: &'static str,
76    pub value: Box<dyn Bindable<sqlx::Postgres>>,
77}
78
79/// Checks a column aainst a value using the `<` operator
80pub struct LtCriteria {
81    pub column: &'static str,
82    pub value: Box<dyn Bindable<sqlx::Postgres>>,
83}
84
85/// Checks a column aainst a value using the `<=` operator
86pub struct LteCriteria {
87    pub column: &'static str,
88    pub value: Box<dyn Bindable<sqlx::Postgres>>,
89}
90
91/// Checks a column aainst a set of values using the `IN` operator
92pub struct InCriteria {
93    pub column: &'static str,
94    pub values: Vec<Box<dyn Bindable<sqlx::Postgres>>>,
95}
96
97/// Checks that a column `IS NOT NULL`
98pub struct NotNullCriteria {
99    pub column: &'static str,
100}
101
102/// Checks that a column `IS NULL`
103pub struct IsNullCriteria {
104    pub column: &'static str,
105}
106
107impl SearchService {
108    ///
109    /// Searches for a set of entities using the given criteria.
110    ///
111    #[tracing::instrument(skip(self, search, pagination))]
112    pub async fn search<
113        E: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow>,
114        S: SearchCriteria + Send,
115    >(
116        &self,
117        table_name: &'static str,
118        search: impl Into<Option<S>>,
119        pagination: impl Into<Option<PaginationQuery>>,
120    ) -> Result<PaginatedResult<E>> {
121        let search: Option<S> = search.into();
122        let pagination: Option<PaginationQuery> = pagination.into();
123        let table_name = table_name
124            .chars()
125            .filter(|char| char.is_alphanumeric() || char.eq(&'_'))
126            .collect::<String>();
127
128        let mut data_query =
129            QueryBuilder::<'_, sqlx::Postgres>::new(format!("SELECT * FROM \"{table_name}\" "));
130
131        let mut count_query = QueryBuilder::<'_, sqlx::Postgres>::new(format!(
132            "SELECT COUNT(*) FROM \"{table_name}\" "
133        ));
134
135        if let Some(search) = search {
136            data_query.push(" WHERE ");
137            count_query.push(" WHERE ");
138
139            search.extend_query_builder(&mut data_query);
140            search.extend_query_builder(&mut count_query);
141        }
142
143        if let Some(order) = pagination.as_ref().and_then(|value| value.order.as_ref()) {
144            let order_column = order
145                .0
146                .chars()
147                .skip_while(|char| !char.is_alphabetic())
148                .filter(|char| char.is_alphanumeric() || '_'.eq(char))
149                .collect::<String>();
150
151            data_query.push(format!(" ORDER BY {} {} ", order_column, order.1));
152        }
153
154        if let Some(page_size) = pagination
155            .as_ref()
156            .and_then(|value| value.page_size.as_ref())
157        {
158            data_query.push(format!(" LIMIT {page_size} "));
159        }
160
161        if let (Some(page), Some(page_size)) = (
162            pagination.as_ref().and_then(|value| value.page.as_ref()),
163            pagination
164                .as_ref()
165                .and_then(|value| value.page_size.as_ref()),
166        ) {
167            data_query.push(format!(" OFFSET {} ", page * page_size));
168        }
169
170        let data = async {
171            data_query
172                .build()
173                .fetch_all(self.database.ro())
174                .await
175                .inspect_err(|error| tracing::error!(?error, "Unable to dispatch search query"))
176                .map_err(|_| Error::SearchError)
177        };
178
179        let pagination_info = async {
180            count_query
181                .build()
182                .fetch_one(self.database.ro())
183                .await
184                .inspect_err(|error| tracing::error!(?error, "Unable to dispatch count query"))
185                .map_err(|_| Error::CountError)
186        };
187
188        let (data, pagination_info) = futures::future::try_join(data, pagination_info).await?;
189
190        let items = data
191            .iter()
192            .map(E::from_row)
193            .collect::<std::result::Result<Vec<_>, sqlx::Error>>()
194            .inspect_err(|error| {
195                tracing::error!(
196                    ?error,
197                    entity = std::any::type_name::<E>(),
198                    "Unable to map database data to entity"
199                )
200            })
201            .map_err(|_| Error::EntityMapError(std::any::type_name::<E>().to_string()))?;
202
203        use sqlx::Row;
204
205        let page = pagination
206            .as_ref()
207            .and_then(|value| value.page)
208            .unwrap_or_default();
209        let per_page = pagination
210            .as_ref()
211            .and_then(|value| value.page_size)
212            .unwrap_or_default();
213
214        let total_items = pagination_info
215            .try_get::<i64, _>(0)
216            .inspect_err(|error| tracing::error!(?error, "Unable to get count"))
217            .map_err(|_| Error::CountError)? as u64;
218
219        let total_pages = if per_page == 0 {
220            1
221        } else {
222            total_items.div_ceil(per_page)
223        };
224
225        Ok(PaginatedResult {
226            total_items,
227            total_pages,
228            next_page: if total_pages == 0 {
229                None
230            } else if page < (total_pages - 1) {
231                Some(page + 1)
232            } else {
233                None
234            },
235            previous_page: if page > 0 { Some(page - 1) } else { None },
236            page,
237            items,
238            per_page,
239        })
240    }
241}
242
243#[derive(Clone, Debug, Default, serde::Deserialize, serde::Serialize)]
244pub enum PaginationOrder {
245    #[default]
246    Ascending,
247    Descending,
248}
249
250///
251/// This type contains meta information for a pagination
252///
253#[derive(Clone, Debug, Default, serde::Deserialize, serde::Serialize)]
254pub struct PaginationQuery {
255    pub page: Option<u64>,
256    #[serde(alias = "per_page")]
257    pub page_size: Option<u64>,
258    #[serde(default)]
259    pub order: Option<(String, PaginationOrder)>,
260}
261
262///
263/// This trait will be used for everything that wants to play around with your entity on a very basic level.
264///
265/// To get access to filtering the [SearchService] should be used as this trait already handles the entity itself most of the time.
266///
267pub trait BaseRepository {
268    /// The entity that this repository will output
269    type Entity: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow>
270        + serde::de::DeserializeOwned
271        + serde::Serialize;
272
273    /// The type of the primary key. It can consist of a tuple of multiple types if a compound key is used
274    type PkType: for<'r> sqlx::Type<sqlx::Postgres>;
275
276    ///
277    /// Gets a list of entities respecting the [PaginationQuery]
278    ///
279    /// To do a filtered list use the [SearchService] helper
280    ///
281    /// # Important Note
282    ///
283    /// The `order_by` is supposed to be only a column name. Whoever implements this trait can rely on that. That said whoever calls this
284    /// method has to make sure, no SQL data is inserted here.
285    ///
286    fn get_entities(
287        &self,
288        page: i32,
289        page_size: i32,
290        order_by: &'static str,
291        direction: PaginationOrder,
292    ) -> impl Future<Output = Result<Vec<Self::Entity>>> + Send;
293
294    ///
295    /// Gets a single entity by it's primary key(s)
296    ///
297    fn get_entity(&self, pk: Self::PkType) -> impl Future<Output = Result<Self::Entity>> + Send;
298
299    ///
300    /// Updates an entity. Reads the primary key(s) from the input
301    ///
302    fn update_entity(&self, entity: Self::Entity) -> impl Future<Output = Result<u64>> + Send;
303
304    ///
305    /// Creates a new entity. The implementation has to make sure, that the primary key(s) are handled properly (eg. by ignoring them)
306    ///
307    fn create_entity(
308        &self,
309        entity: Self::Entity,
310    ) -> impl Future<Output = Result<Self::Entity>> + Send;
311
312    ///
313    /// Deletes the given entity. Returns the amount of affected rows (ideally it's a 0 if the entity does not exist anymore or a 1 if the
314    /// PK was matched and the entry deleted)
315    ///
316    fn delete_entity(&self, pk: Self::PkType) -> impl Future<Output = Result<u64>> + Send;
317}
318
319/// An object-safe bindble value for sqlx
320pub trait Bindable<DB: sqlx::Database>: Send + Sync {
321    fn bind_to_query_builder<'q>(&self, qb: &mut QueryBuilder<'q, DB>)
322    where
323        Self: 'q;
324
325    fn bind_to_separated<'qb, 'args>(
326        &self,
327        separated: &mut Separated<'qb, 'args, DB, &'static str>,
328    ) where
329        Self: 'qb,
330        Self: 'args;
331}
332
333impl<
334        T: for<'e> sqlx::encode::Encode<'e, sqlx::Postgres>
335            + sqlx::Type<sqlx::Postgres>
336            + Send
337            + Sync
338            + Clone,
339    > Bindable<sqlx::Postgres> for T
340{
341    fn bind_to_query_builder<'q>(&self, qb: &mut QueryBuilder<'q, sqlx::Postgres>)
342    where
343        Self: 'q,
344    {
345        qb.push_bind(self.clone());
346    }
347
348    fn bind_to_separated<'qb, 'args>(
349        &self,
350        separated: &mut Separated<'qb, 'args, sqlx::Postgres, &'static str>,
351    ) where
352        Self: 'qb,
353        Self: 'args,
354    {
355        separated.push_bind(self.clone());
356    }
357}
358
359impl std::fmt::Display for PaginationOrder {
360    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
361        match self {
362            PaginationOrder::Ascending => f.write_str("ASC"),
363            PaginationOrder::Descending => f.write_str("DESC"),
364        }
365    }
366}
367
368impl<S> FromState<S> for SearchService
369where
370    S: ProvideState<Database>,
371{
372    fn from_state(state: &S) -> Self {
373        Self {
374            database: ProvideState::<Database>::provide(state),
375        }
376    }
377}
378
379impl Default for AndCriteria {
380    fn default() -> Self {
381        Self::new()
382    }
383}
384
385impl AndCriteria {
386    pub fn new() -> Self {
387        Self {
388            criteria: Vec::new(),
389        }
390    }
391
392    pub fn and(mut self, criteria: impl SearchCriteria + 'static) -> Self {
393        self.criteria.push(Box::new(criteria));
394
395        self
396    }
397}
398
399impl Default for OrCriteria {
400    fn default() -> Self {
401        Self::new()
402    }
403}
404
405impl OrCriteria {
406    pub fn new() -> Self {
407        Self {
408            criteria: Vec::new(),
409        }
410    }
411
412    pub fn or(mut self, criteria: impl SearchCriteria + 'static) -> Self {
413        self.criteria.push(Box::new(criteria));
414
415        self
416    }
417}
418
419impl EqCriteria {
420    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
421        Self {
422            column,
423            value: Box::new(value),
424        }
425    }
426}
427
428impl NeCriteria {
429    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
430        Self {
431            column,
432            value: Box::new(value),
433        }
434    }
435}
436
437impl LikeCriteria {
438    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
439        Self {
440            column,
441            value: Box::new(value),
442        }
443    }
444}
445
446impl GtCriteria {
447    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
448        Self {
449            column,
450            value: Box::new(value),
451        }
452    }
453}
454
455impl GteCriteria {
456    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
457        Self {
458            column,
459            value: Box::new(value),
460        }
461    }
462}
463
464impl LtCriteria {
465    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
466        Self {
467            column,
468            value: Box::new(value),
469        }
470    }
471}
472
473impl LteCriteria {
474    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
475        Self {
476            column,
477            value: Box::new(value),
478        }
479    }
480}
481
482impl NotNullCriteria {
483    pub fn new(column: &'static str) -> Self {
484        Self { column }
485    }
486}
487
488impl IsNullCriteria {
489    pub fn new(column: &'static str) -> Self {
490        Self { column }
491    }
492}
493
494impl InCriteria {
495    pub fn new<I: Bindable<sqlx::Postgres> + 'static>(
496        column: &'static str,
497        values: impl IntoIterator<Item = I>,
498    ) -> Self {
499        Self {
500            column,
501            values: values
502                .into_iter()
503                .map(|value| Box::new(value) as Box<dyn Bindable<sqlx::Postgres>>)
504                .collect::<Vec<_>>(),
505        }
506    }
507}
508
509impl SearchCriteria for AndCriteria {
510    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
511        let mut peekable = self.criteria.iter().peekable();
512        qb.push(" ( ");
513        while let Some(criteria) = peekable.next() {
514            criteria.extend_query_builder(qb);
515
516            if peekable.peek().is_some() {
517                qb.push(" AND ");
518            }
519        }
520        qb.push(" ) ");
521    }
522}
523
524impl SearchCriteria for OrCriteria {
525    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
526        let mut peekable = self.criteria.iter().peekable();
527        qb.push(" ( ");
528        while let Some(criteria) = peekable.next() {
529            criteria.extend_query_builder(qb);
530
531            if peekable.peek().is_some() {
532                qb.push(" OR ");
533            }
534        }
535        qb.push(" ) ");
536    }
537}
538
539impl SearchCriteria for EqCriteria {
540    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
541        self.value
542            .bind_to_query_builder(qb.push(self.column).push(" = "));
543    }
544}
545
546impl SearchCriteria for NeCriteria {
547    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
548        self.value
549            .bind_to_query_builder(qb.push(self.column).push(" != "));
550    }
551}
552
553impl SearchCriteria for LikeCriteria {
554    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
555        self.value
556            .bind_to_query_builder(qb.push(self.column).push(" LIKE "));
557    }
558}
559
560impl SearchCriteria for GtCriteria {
561    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
562        self.value
563            .bind_to_query_builder(qb.push(self.column).push(" > "));
564    }
565}
566
567impl SearchCriteria for GteCriteria {
568    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
569        self.value
570            .bind_to_query_builder(qb.push(self.column).push(" >= "));
571    }
572}
573
574impl SearchCriteria for LtCriteria {
575    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
576        self.value
577            .bind_to_query_builder(qb.push(self.column).push(" < "));
578    }
579}
580
581impl SearchCriteria for LteCriteria {
582    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
583        self.value
584            .bind_to_query_builder(qb.push(self.column).push(" <= "));
585    }
586}
587
588impl SearchCriteria for NotNullCriteria {
589    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
590        qb.push(self.column).push(" IS NOT NULL ");
591    }
592}
593
594impl SearchCriteria for IsNullCriteria {
595    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
596        qb.push(self.column).push(" IS NULL ");
597    }
598}
599
600impl SearchCriteria for InCriteria {
601    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
602        qb.push(self.column)
603            .push(" IN ")
604            .push_tuples(&self.values, |mut builder, value| {
605                value.bind_to_separated(&mut builder);
606            });
607    }
608}
609
610impl ProvideState<Database> for sqlx::PgPool {
611    fn provide(&self) -> Database {
612        Database::from(self.clone())
613    }
614}