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