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