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