1mod dialect;
2mod types;
3
4pub use dialect::SqlDialect;
5pub use types::{CompiledQuery, DatabaseKind, SqlCompileError};
6
7#[cfg(test)]
8mod tests {
9 use teaql_core::{
10 BinaryOp, DataType, DeleteCommand, EntityDescriptor, Expr, InsertCommand, OrderBy,
11 PropertyDescriptor, RecoverCommand, SelectQuery, UpdateCommand, Value,
12 };
13
14 use crate::{CompiledQuery, SqlCompileError, SqlDialect};
15
16 #[derive(Debug, Clone, Copy, PartialEq, Eq)]
17 struct TestDialect;
18
19 impl SqlDialect for TestDialect {
20 fn kind(&self) -> crate::DatabaseKind {
21 crate::DatabaseKind::PostgreSql
22 }
23
24 fn quote_ident(&self, ident: &str) -> String {
25 format!("\"{}\"", ident)
26 }
27
28 fn placeholder(&self, index: usize) -> String {
29 format!("${index}")
30 }
31 }
32
33 fn entity() -> EntityDescriptor {
34 EntityDescriptor::new("Order")
35 .table_name("orders")
36 .property(
37 PropertyDescriptor::new("id", DataType::U64)
38 .column_name("id")
39 .id()
40 .not_null(),
41 )
42 .property(
43 PropertyDescriptor::new("version", DataType::I64)
44 .column_name("version")
45 .version()
46 .not_null(),
47 )
48 .property(PropertyDescriptor::new("name", DataType::Text).column_name("name"))
49 }
50
51 fn line_entity() -> EntityDescriptor {
52 EntityDescriptor::new("OrderLine")
53 .table_name("orderline")
54 .property(
55 PropertyDescriptor::new("id", DataType::U64)
56 .column_name("id")
57 .id()
58 .not_null(),
59 )
60 .property(PropertyDescriptor::new("order_id", DataType::U64).column_name("order_id"))
61 .property(PropertyDescriptor::new("name", DataType::Text).column_name("name"))
62 }
63
64 #[test]
65 fn compiles_select_with_filters_order_and_limit() {
66 let query = TestDialect
67 .compile_select(
68 &entity(),
69 &SelectQuery::new("Order")
70 .project("id")
71 .project("name")
72 .filter(Expr::eq("name", "A"))
73 .order_by(OrderBy::desc("id"))
74 .limit(10)
75 .offset(5),
76 )
77 .unwrap();
78
79 assert_eq!(
80 query,
81 CompiledQuery {
82 sql: "SELECT \"id\", \"name\" FROM \"orders\" WHERE (\"name\" = $1) ORDER BY \"id\" DESC LIMIT 10 OFFSET 5".to_owned(),
83 params: vec![Value::from("A")],
84 }
85 );
86 }
87
88 #[test]
89 fn compiles_aggregate_projection() {
90 let query = TestDialect
91 .compile_select(
92 &entity(),
93 &SelectQuery::new("Order").count_field("id", "count"),
94 )
95 .unwrap();
96
97 assert_eq!(
98 query.sql,
99 "SELECT COUNT(\"id\") AS \"count\" FROM \"orders\""
100 );
101 }
102
103 #[test]
104 fn compiles_grouped_aggregate_and_extended_predicates() {
105 let query = TestDialect
106 .compile_select(
107 &entity(),
108 &SelectQuery::new("Order")
109 .group_by("name")
110 .count("total")
111 .sum("version", "versionSum")
112 .filter(
113 Expr::between("version", 1_i64, 9_i64)
114 .and_expr(Expr::not_like("name", "tmp%"))
115 .and_expr(Expr::not_in_list(
116 "name",
117 vec![Value::from("x"), Value::from("y")],
118 ))
119 .and_expr(Expr::is_not_null("name")),
120 )
121 .order_asc("name"),
122 )
123 .unwrap();
124
125 assert_eq!(
126 query.sql,
127 "SELECT \"name\", COUNT(*) AS \"total\", SUM(\"version\") AS \"versionSum\" FROM \"orders\" WHERE ((\"version\" BETWEEN $1 AND $2) AND (\"name\" NOT LIKE $3) AND (\"name\" NOT IN ($4, $5)) AND (\"name\" IS NOT NULL)) GROUP BY \"name\" ORDER BY \"name\" ASC"
128 );
129 assert_eq!(
130 query.params,
131 vec![
132 Value::I64(1),
133 Value::I64(9),
134 Value::from("tmp%"),
135 Value::from("x"),
136 Value::from("y"),
137 ]
138 );
139 }
140
141 #[test]
142 fn compiles_sound_like_expression() {
143 let query = TestDialect
144 .compile_select(
145 &entity(),
146 &SelectQuery::new("Order").filter(Expr::sound_like("name", "Robert")),
147 )
148 .unwrap();
149
150 assert_eq!(
151 query,
152 CompiledQuery {
153 sql: "SELECT * FROM \"orders\" WHERE (SOUNDEX(\"name\") = SOUNDEX($1))".to_owned(),
154 params: vec![Value::from("Robert")],
155 }
156 );
157 }
158
159 #[test]
160 fn compiles_java_style_string_match_builders() {
161 let query = TestDialect
162 .compile_select(
163 &entity(),
164 &SelectQuery::new("Order").filter(
165 Expr::contain("name", "tea")
166 .and_expr(Expr::begin_with("name", "t"))
167 .and_expr(Expr::end_with("name", "a"))
168 .and_expr(Expr::not_contain("name", "coffee"))
169 .and_expr(Expr::not_begin_with("name", "x"))
170 .and_expr(Expr::not_end_with("name", "z")),
171 ),
172 )
173 .unwrap();
174
175 assert_eq!(
176 query.sql,
177 "SELECT * FROM \"orders\" WHERE ((\"name\" LIKE $1) AND (\"name\" LIKE $2) AND (\"name\" LIKE $3) AND (\"name\" NOT LIKE $4) AND (\"name\" NOT LIKE $5) AND (\"name\" NOT LIKE $6))"
178 );
179 assert_eq!(
180 query.params,
181 vec![
182 Value::from("%tea%"),
183 Value::from("t%"),
184 Value::from("%a"),
185 Value::from("%coffee%"),
186 Value::from("x%"),
187 Value::from("%z"),
188 ]
189 );
190 }
191
192 #[test]
193 fn dialect_schema_setup_defaults_to_empty() {
194 assert!(TestDialect.schema_setup_sqls().is_empty());
195 }
196
197 #[test]
198 fn compiles_insert_update_delete_and_recover() {
199 let insert = TestDialect
200 .compile_insert(
201 &entity(),
202 &InsertCommand::new("Order")
203 .value("id", 1_u64)
204 .value("name", "A"),
205 )
206 .unwrap();
207 assert_eq!(
208 insert.sql,
209 "INSERT INTO \"orders\" (\"id\", \"name\") VALUES ($1, $2)"
210 );
211
212 let update = TestDialect
213 .compile_update(
214 &entity(),
215 &UpdateCommand::new("Order", 1_u64)
216 .expected_version(3)
217 .value("name", "B"),
218 )
219 .unwrap();
220 assert_eq!(
221 update.sql,
222 "UPDATE \"orders\" SET \"name\" = $1, \"version\" = $2 WHERE \"id\" = $3 AND \"version\" = $4"
223 );
224
225 let delete = TestDialect
226 .compile_delete(
227 &entity(),
228 &DeleteCommand::new("Order", 1_u64).expected_version(3),
229 )
230 .unwrap();
231 assert_eq!(
232 delete.sql,
233 "UPDATE \"orders\" SET \"version\" = $1 WHERE \"id\" = $2 AND \"version\" = $3"
234 );
235
236 let recover = TestDialect
237 .compile_recover(&entity(), &RecoverCommand::new("Order", 1_u64, -4))
238 .unwrap();
239 assert_eq!(
240 recover.sql,
241 "UPDATE \"orders\" SET \"version\" = $1 WHERE \"id\" = $2 AND \"version\" = $3"
242 );
243 }
244
245 #[test]
246 fn compiles_in_expression_and_validates_empty_list() {
247 let query = TestDialect
248 .compile_select(
249 &entity(),
250 &SelectQuery::new("Order").filter(Expr::Binary {
251 left: Box::new(Expr::column("id")),
252 op: BinaryOp::In,
253 right: Box::new(Expr::Value(Value::List(vec![1_u64.into(), 2_u64.into()]))),
254 }),
255 )
256 .unwrap();
257 assert_eq!(
258 query.sql,
259 "SELECT * FROM \"orders\" WHERE (\"id\" IN ($1, $2))"
260 );
261
262 let err = TestDialect
263 .compile_select(
264 &entity(),
265 &SelectQuery::new("Order").filter(Expr::Binary {
266 left: Box::new(Expr::column("id")),
267 op: BinaryOp::In,
268 right: Box::new(Expr::Value(Value::List(vec![]))),
269 }),
270 )
271 .unwrap_err();
272 assert!(matches!(err, SqlCompileError::EmptyInList));
273 }
274
275 #[test]
276 fn generic_dialect_expands_large_in_expressions() {
277 let query = TestDialect
278 .compile_select(
279 &entity(),
280 &SelectQuery::new("Order").filter(
281 Expr::in_large("id", vec![Value::from(1_u64), Value::from(2_u64)])
282 .and_expr(Expr::not_in_large("name", vec![Value::from("archived")])),
283 ),
284 )
285 .unwrap();
286
287 assert_eq!(
288 query.sql,
289 "SELECT * FROM \"orders\" WHERE ((\"id\" IN ($1, $2)) AND (\"name\" NOT IN ($3)))"
290 );
291 assert_eq!(
292 query.params,
293 vec![
294 Value::from(1_u64),
295 Value::from(2_u64),
296 Value::from("archived")
297 ]
298 );
299 }
300
301 #[test]
302 fn compiles_property_to_property_filters() {
303 let query = TestDialect
304 .compile_select(
305 &entity(),
306 &SelectQuery::new("Order").filter(Expr::compare_columns(
307 "version",
308 BinaryOp::Gte,
309 "id",
310 )),
311 )
312 .unwrap();
313
314 assert_eq!(
315 query.sql,
316 "SELECT * FROM \"orders\" WHERE (\"version\" >= \"id\")"
317 );
318 assert!(query.params.is_empty());
319 }
320
321 #[test]
322 fn compiles_raw_escape_hatches_and_dynamic_properties() {
323 let query = TestDialect
324 .compile_select(
325 &entity(),
326 &SelectQuery::new("Order")
327 .comment("audit")
328 .project("id")
329 .project_raw("name", "upper(name)")
330 .dynamic_property_raw("score", "42")
331 .raw_sql_search_criteria("name <> ''")
332 .json_expr("payload @> '{\"active\":true}'"),
333 )
334 .unwrap();
335
336 assert_eq!(
337 query.sql,
338 "/* audit */ SELECT \"id\", upper(name) AS \"name\", 42 AS \"score\" FROM \"orders\" WHERE name <> '' AND payload @> '{\"active\":true}'"
339 );
340 }
341
342 #[test]
343 fn compiles_raw_sql_override_with_comment() {
344 let query = TestDialect
345 .compile_select(
346 &entity(),
347 &SelectQuery::new("Order")
348 .comment("manual")
349 .raw_sql("SELECT 1 AS id"),
350 )
351 .unwrap();
352
353 assert_eq!(query.sql, "/* manual */ SELECT 1 AS id");
354 }
355
356 #[test]
357 fn compiles_subquery_expression_and_appends_params_in_order() {
358 let query = TestDialect
359 .compile_select(
360 &entity(),
361 &SelectQuery::new("Order").filter(
362 Expr::in_subquery(
363 "id",
364 line_entity(),
365 SelectQuery::new("OrderLine")
366 .filter(Expr::eq("name", "line-1"))
367 .order_asc("id")
368 .limit(10),
369 "order_id",
370 )
371 .and_expr(Expr::eq("name", "order-1")),
372 ),
373 )
374 .unwrap();
375
376 assert_eq!(
377 query.sql,
378 "SELECT * FROM \"orders\" WHERE ((\"id\" IN (SELECT \"order_id\" FROM \"orderline\" WHERE (\"name\" = $1) ORDER BY \"id\" ASC LIMIT 10)) AND (\"name\" = $2))"
379 );
380 assert_eq!(
381 query.params,
382 vec![Value::from("line-1"), Value::from("order-1")]
383 );
384 }
385
386 #[test]
387 fn compiles_extended_aggregates_expression_projection_function_order_and_having() {
388 let query = TestDialect
389 .compile_select(
390 &entity(),
391 &SelectQuery::new("Order")
392 .group_by("name")
393 .project_expr("nameSound", Expr::soundex(Expr::column("name")))
394 .stddev("version", "stddevVersion")
395 .stddev_pop("version", "stddevPopVersion")
396 .var_samp("version", "varSampVersion")
397 .var_pop("version", "varPopVersion")
398 .bit_and("version", "bitAndVersion")
399 .bit_or("version", "bitOrVersion")
400 .bit_xor("version", "bitXorVersion")
401 .having(Expr::binary(
402 Expr::count_all(),
403 BinaryOp::Gt,
404 Expr::value(1_i64),
405 ))
406 .order_gbk_asc("name"),
407 )
408 .unwrap();
409
410 assert_eq!(
411 query.sql,
412 "SELECT \"name\", SOUNDEX(\"name\") AS \"nameSound\", STDDEV(\"version\") AS \"stddevVersion\", STDDEV_POP(\"version\") AS \"stddevPopVersion\", VAR_SAMP(\"version\") AS \"varSampVersion\", VAR_POP(\"version\") AS \"varPopVersion\", BIT_AND(\"version\") AS \"bitAndVersion\", BIT_OR(\"version\") AS \"bitOrVersion\", BIT_XOR(\"version\") AS \"bitXorVersion\" FROM \"orders\" GROUP BY \"name\" HAVING (COUNT(*) > $1) ORDER BY convert_to(\"name\", 'GBK') ASC"
413 );
414 assert_eq!(query.params, vec![Value::I64(1)]);
415 }
416
417 #[test]
418 fn renders_postgres_debug_sql_with_inlined_params() {
419 let query = CompiledQuery {
420 sql: "SELECT * FROM \"orders\" WHERE ((\"name\" = $1) AND (\"id\" = ANY($2)) AND ('$3' = '$3'))".to_owned(),
421 params: vec![
422 Value::from("Bob's Shop"),
423 Value::List(vec![Value::from(1_u64), Value::from(2_u64)]),
424 ],
425 };
426
427 assert_eq!(
428 query.debug_sql(crate::DatabaseKind::PostgreSql),
429 "SELECT * FROM \"orders\" WHERE ((\"name\" = 'Bob''s Shop') AND (\"id\" = ANY(ARRAY[1, 2])) AND ('$3' = '$3'))"
430 );
431 }
432
433 #[test]
434 fn renders_sqlite_debug_sql_with_inlined_params() {
435 let query = CompiledQuery {
436 sql: "UPDATE \"orders\" SET \"name\" = ? WHERE ((\"id\" = ?) AND ('?' = '?'))"
437 .to_owned(),
438 params: vec![Value::from("Alice's Shop"), Value::from(7_u64)],
439 };
440
441 assert_eq!(
442 query.debug_sql(crate::DatabaseKind::Sqlite),
443 "UPDATE \"orders\" SET \"name\" = 'Alice''s Shop' WHERE ((\"id\" = 7) AND ('?' = '?'))"
444 );
445 }
446}