mithril_persistence/sqlite/
query.rs

1use super::{SqLiteEntity, WhereCondition};
2
3/// Define a query to perform on database and return iterator of a defined entity.
4///
5/// Used as a parameter of [fetch][crate::sqlite::ConnectionExtensions::fetch].
6///
7/// It aims at being easily testable and adaptable.
8pub trait Query {
9    /// Entity type returned by the result cursor.
10    type Entity: SqLiteEntity;
11
12    /// Return the filters to apply to the query.
13    fn filters(&self) -> WhereCondition;
14
15    /// Return the definition of this query, ie the actual SQL this query performs.
16    fn get_definition(&self, condition: &str) -> String;
17}
18
19#[cfg(test)]
20mod tests {
21    use sqlite::{Connection, Value};
22
23    use crate::sqlite::{
24        ConnectionExtensions, GetAllCondition, Projection, SourceAlias, SqliteConnection,
25    };
26
27    use super::super::{entity::HydrationError, SqLiteEntity};
28    use super::*;
29
30    #[derive(Debug, PartialEq)]
31    struct TestEntity {
32        text_data: String,
33        real_data: f64,
34        integer_data: i64,
35        maybe_null: Option<i64>,
36    }
37
38    impl SqLiteEntity for TestEntity {
39        fn hydrate(row: sqlite::Row) -> Result<Self, HydrationError> {
40            Ok(TestEntity {
41                text_data: row.read::<&str, _>(0).to_string(),
42                real_data: row.read::<f64, _>(1),
43                integer_data: row.read::<i64, _>(2),
44                maybe_null: row.read::<Option<i64>, _>(3),
45            })
46        }
47
48        fn get_projection() -> Projection {
49            let mut projection = Projection::default();
50
51            projection.add_field("text_data", "{:test:}.text_data", "text");
52            projection.add_field("real_data", "{:test:}.real_data", "real");
53            projection.add_field("integer_data", "{:test:}.integer_data", "integer");
54            projection.add_field("maybe_null", "{:test:}.maybe_null", "integer");
55
56            projection
57        }
58    }
59
60    struct GetTestEntityQuery {
61        condition: WhereCondition,
62    }
63
64    impl GetTestEntityQuery {
65        pub fn new(condition: WhereCondition) -> Self {
66            Self { condition }
67        }
68    }
69
70    impl Query for GetTestEntityQuery {
71        type Entity = TestEntity;
72
73        fn filters(&self) -> WhereCondition {
74            self.condition.clone()
75        }
76
77        fn get_definition(&self, condition: &str) -> String {
78            let aliases = SourceAlias::new(&[("{:test:}", "test")]);
79            let projection = Self::Entity::get_projection().expand(aliases);
80
81            format!("select {projection} from query_test as test where {condition}")
82        }
83    }
84
85    struct UpdateTestEntityQuery {
86        condition: WhereCondition,
87    }
88
89    impl UpdateTestEntityQuery {
90        pub fn new(condition: WhereCondition) -> Self {
91            Self { condition }
92        }
93    }
94
95    impl Query for UpdateTestEntityQuery {
96        type Entity = TestEntity;
97
98        fn filters(&self) -> WhereCondition {
99            self.condition.clone()
100        }
101
102        fn get_definition(&self, _condition: &str) -> String {
103            let aliases = SourceAlias::new(&[("{:test:}", "query_test")]);
104            let projection = Self::Entity::get_projection().expand(aliases);
105
106            format!(
107                r#"
108insert into query_test (text_data, real_data, integer_data, maybe_null) values (?1, ?2, ?3, ?4)
109  on conflict (text_data) do update set
110    real_data = excluded.real_data,
111    integer_data = excluded.integer_data,
112    maybe_null = excluded.maybe_null 
113returning {projection}
114"#
115            )
116        }
117    }
118
119    impl GetAllCondition for GetTestEntityQuery {}
120
121    fn init_database() -> SqliteConnection {
122        let connection = Connection::open_thread_safe(":memory:").unwrap();
123        connection
124            .execute(
125                "
126            drop table if exists query_test;
127            create table query_test(text_data text not null primary key, real_data real not null, integer_data integer not null, maybe_null integer);
128            insert into query_test(text_data, real_data, integer_data, maybe_null) values ('row 1', 1.23, -52, null);
129            insert into query_test(text_data, real_data, integer_data, maybe_null) values ('row 2', 2.34, 1789, 0);
130            ",
131            )
132            .unwrap();
133
134        connection
135    }
136
137    #[test]
138    pub fn simple_test() {
139        let connection = init_database();
140        let mut cursor = connection
141            .fetch(GetTestEntityQuery::new(WhereCondition::default()))
142            .unwrap();
143        let entity = cursor
144            .next()
145            .expect("there should be two results, none returned");
146        assert_eq!(
147            TestEntity {
148                text_data: "row 1".to_string(),
149                real_data: 1.23,
150                integer_data: -52,
151                maybe_null: None
152            },
153            entity
154        );
155        let entity = cursor
156            .next()
157            .expect("there should be two results, only one returned");
158        assert_eq!(
159            TestEntity {
160                text_data: "row 2".to_string(),
161                real_data: 2.34,
162                integer_data: 1789,
163                maybe_null: Some(0)
164            },
165            entity
166        );
167
168        assert!(cursor.next().is_none(), "there should be no result");
169    }
170
171    #[test]
172    pub fn test_condition() {
173        let connection = init_database();
174        let mut cursor = connection
175            .fetch(GetTestEntityQuery::new(WhereCondition::new(
176                "maybe_null is not null",
177                Vec::new(),
178            )))
179            .unwrap();
180        let entity = cursor
181            .next()
182            .expect("there should be one result, none returned");
183        assert_eq!(
184            TestEntity {
185                text_data: "row 2".to_string(),
186                real_data: 2.34,
187                integer_data: 1789,
188                maybe_null: Some(0)
189            },
190            entity
191        );
192        assert!(cursor.next().is_none());
193    }
194
195    #[test]
196    pub fn test_parameters() {
197        let connection = init_database();
198        let mut cursor = connection
199            .fetch(GetTestEntityQuery::new(WhereCondition::new(
200                "text_data like ?",
201                vec![Value::String("%1".to_string())],
202            )))
203            .unwrap();
204        let entity = cursor
205            .next()
206            .expect("there should be one result, none returned");
207        assert_eq!(
208            TestEntity {
209                text_data: "row 1".to_string(),
210                real_data: 1.23,
211                integer_data: -52,
212                maybe_null: None
213            },
214            entity
215        );
216        assert!(cursor.next().is_none());
217    }
218
219    #[test]
220    fn test_upsertion() {
221        let connection = init_database();
222        let params = [
223            Value::String("row 1".to_string()),
224            Value::Float(1.234),
225            Value::Integer(0),
226            Value::Null,
227        ]
228        .to_vec();
229        let mut cursor = connection
230            .fetch(UpdateTestEntityQuery::new(WhereCondition::new("", params)))
231            .unwrap();
232
233        let entity = cursor
234            .next()
235            .expect("there should be one result, none returned");
236        assert_eq!(
237            TestEntity {
238                text_data: "row 1".to_string(),
239                real_data: 1.234,
240                integer_data: 0,
241                maybe_null: None
242            },
243            entity
244        );
245        assert!(cursor.next().is_none());
246    }
247}