1mod dialect;
2mod executor;
3mod types;
4
5pub use dialect::{SqlDialect, quote_identifier_if_needed};
6pub use executor::{
7 SqlDataServiceExecutor, SqlDataServiceTransaction, SqlExecutorError, SqlTransaction,
8 SqlTransactionTransport, SqlTransport,
9};
10pub use types::{CompiledQuery, DatabaseKind, SqlCompileError};
11
12#[cfg(test)]
13mod tests {
14 use teaql_core::{
15 BinaryOp, DataType, DeleteCommand, EntityDescriptor, Expr, InsertCommand, OrderBy,
16 PropertyDescriptor, RecoverCommand, SelectQuery, UpdateCommand, Value,
17 };
18
19 use crate::{CompiledQuery, SqlCompileError, SqlDialect};
20
21 #[derive(Debug, Clone, Copy, PartialEq, Eq)]
22 struct TestDialect;
23
24 impl SqlDialect for TestDialect {
25 fn kind(&self) -> crate::DatabaseKind {
26 crate::DatabaseKind::PostgreSql
27 }
28
29 fn quote_ident(&self, ident: &str) -> String {
30 format!("\"{}\"", ident)
31 }
32
33 fn placeholder(&self, index: usize) -> String {
34 format!("${index}")
35 }
36
37 fn compile_gbk_function(
38 &self,
39 entity: &teaql_core::EntityDescriptor,
40 args: &[teaql_core::Expr],
41 params: &mut Vec<teaql_core::Value>,
42 ) -> Result<String, crate::SqlCompileError> {
43 let [arg] = args else {
44 return Err(crate::SqlCompileError::InvalidFunctionArguments(
45 "GBK expects exactly one argument".to_owned(),
46 ));
47 };
48 let arg = self.compile_expr(entity, arg, params)?;
49 Ok(format!("convert_to({arg}, 'GBK')"))
50 }
51 }
52
53 const ORDER_DEFAULT_PROJECTION: &str = "\"id\", \"version\", \"name\"";
54
55 fn entity() -> EntityDescriptor {
56 EntityDescriptor::new("Order")
57 .table_name("orders")
58 .property(
59 PropertyDescriptor::new("id", DataType::U64)
60 .column_name("id")
61 .id()
62 .not_null(),
63 )
64 .property(
65 PropertyDescriptor::new("version", DataType::I64)
66 .column_name("version")
67 .version()
68 .not_null(),
69 )
70 .property(PropertyDescriptor::new("name", DataType::Text).column_name("name"))
71 }
72
73 #[test]
74 fn quotes_identifiers_only_when_needed() {
75 assert_eq!(
76 crate::quote_identifier_if_needed("stock_item_data", '"'),
77 "stock_item_data"
78 );
79 assert_eq!(
80 crate::quote_identifier_if_needed("select", '"'),
81 "\"select\""
82 );
83 assert_eq!(crate::quote_identifier_if_needed("order", '`'), "`order`");
84 assert_eq!(
85 crate::quote_identifier_if_needed("has space", '"'),
86 "\"has space\""
87 );
88 assert_eq!(
89 crate::quote_identifier_if_needed("\"already_wrapped\"", '"'),
90 "\"already_wrapped\""
91 );
92 }
93
94 fn line_entity() -> EntityDescriptor {
95 EntityDescriptor::new("OrderLine")
96 .table_name("orderline")
97 .property(
98 PropertyDescriptor::new("id", DataType::U64)
99 .column_name("id")
100 .id()
101 .not_null(),
102 )
103 .property(PropertyDescriptor::new("order_id", DataType::U64).column_name("order_id"))
104 .property(PropertyDescriptor::new("name", DataType::Text).column_name("name"))
105 }
106
107 #[test]
108 fn compiles_select_with_filters_order_and_limit() {
109 let query = TestDialect
110 .compile_select(
111 &entity(),
112 &SelectQuery::new("Order")
113 .project("id")
114 .project("name")
115 .filter(Expr::eq("name", "A"))
116 .order_by(OrderBy::desc("id"))
117 .limit(10)
118 .offset(5),
119 )
120 .unwrap();
121
122 assert_eq!(
123 query,
124 CompiledQuery {
125 sql: "SELECT \"id\", \"name\" FROM \"orders\" WHERE (\"name\" = $1) ORDER BY \"id\" DESC LIMIT 10 OFFSET 5".to_owned(),
126 params: vec![Value::from("A")],
127 comment: None,
128 }
129 );
130 }
131
132 #[test]
133 fn compiles_partitioned_relation_limit_per_parent() {
134 let query = TestDialect
135 .compile_select(
136 &line_entity(),
137 &SelectQuery::new("OrderLine")
138 .project("id")
139 .project("order_id")
140 .project("name")
141 .filter(Expr::in_list(
142 "order_id",
143 vec![Value::U64(11), Value::U64(12)],
144 ))
145 .order_by(OrderBy::desc("id"))
146 .page(1, 3)
147 .partition_by("order_id"),
148 )
149 .unwrap();
150
151 assert_eq!(
152 query.sql,
153 "SELECT * FROM (SELECT \"id\", \"order_id\", \"name\", ROW_NUMBER() OVER (PARTITION BY \"order_id\" ORDER BY \"id\" DESC) AS \"__teaql_partition_rank\" FROM \"orderline\" WHERE (\"order_id\" IN ($1, $2))) AS \"__teaql_partitioned\" WHERE \"__teaql_partition_rank\" > 1 AND \"__teaql_partition_rank\" <= 4 ORDER BY \"__teaql_partition_rank\""
154 );
155 assert_eq!(query.params, vec![Value::U64(11), Value::U64(12)]);
156 }
157
158 #[test]
159 fn deduplicates_partition_projection_for_mysql_derived_tables() {
160 let query = TestDialect
161 .compile_select(
162 &line_entity(),
163 &SelectQuery::new("OrderLine")
164 .project("id")
165 .project("order_id")
166 .project("id")
167 .order_by(OrderBy::asc("id"))
168 .limit(3)
169 .partition_by("order_id"),
170 )
171 .unwrap();
172 assert_eq!(
173 query.sql.matches("\"id\"").count(),
174 2,
175 "one projection and one window order"
176 );
177 assert!(!query.sql.contains("\"id\", \"order_id\", \"id\""));
178 }
179
180 #[test]
181 fn compiles_aggregate_projection() {
182 let query = TestDialect
183 .compile_select(
184 &entity(),
185 &SelectQuery::new("Order").count_field("id", "count"),
186 )
187 .unwrap();
188
189 assert_eq!(
190 query.sql,
191 "SELECT COUNT(\"id\") AS \"count\" FROM \"orders\""
192 );
193 }
194
195 #[test]
196 fn aggregate_projection_ignores_ordinary_selected_fields() {
197 let query = TestDialect
198 .compile_select(
199 &entity(),
200 &SelectQuery::new("Order")
201 .project("id")
202 .project("name")
203 .count_field("id", "count"),
204 )
205 .unwrap();
206
207 assert_eq!(
208 query.sql,
209 "SELECT COUNT(\"id\") AS \"count\" FROM \"orders\""
210 );
211 }
212
213 #[test]
214 fn compiles_grouped_aggregate_and_extended_predicates() {
215 let query = TestDialect
216 .compile_select(
217 &entity(),
218 &SelectQuery::new("Order")
219 .group_by("name")
220 .count("total")
221 .sum("version", "versionSum")
222 .filter(
223 Expr::between("version", 1_i64, 9_i64)
224 .and_expr(Expr::not_like("name", "tmp%"))
225 .and_expr(Expr::not_in_list(
226 "name",
227 vec![Value::from("x"), Value::from("y")],
228 ))
229 .and_expr(Expr::is_not_null("name")),
230 )
231 .order_asc("name"),
232 )
233 .unwrap();
234
235 assert_eq!(
236 query.sql,
237 "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"
238 );
239 assert_eq!(
240 query.params,
241 vec![
242 Value::I64(1),
243 Value::I64(9),
244 Value::from("tmp%"),
245 Value::from("x"),
246 Value::from("y"),
247 ]
248 );
249 }
250
251 #[test]
252 fn compiles_sound_like_expression() {
253 let query = TestDialect
254 .compile_select(
255 &entity(),
256 &SelectQuery::new("Order").filter(Expr::sound_like("name", "Robert")),
257 )
258 .unwrap();
259
260 assert_eq!(
261 query,
262 CompiledQuery {
263 sql: format!(
264 "SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (SOUNDEX(\"name\") = SOUNDEX($1))"
265 ),
266 params: vec![Value::from("Robert")],
267 comment: None,
268 }
269 );
270 }
271
272 #[test]
273 fn compiles_java_style_string_match_builders() {
274 let query = TestDialect
275 .compile_select(
276 &entity(),
277 &SelectQuery::new("Order").filter(
278 Expr::contain("name", "tea")
279 .and_expr(Expr::begin_with("name", "t"))
280 .and_expr(Expr::end_with("name", "a"))
281 .and_expr(Expr::not_contain("name", "coffee"))
282 .and_expr(Expr::not_begin_with("name", "x"))
283 .and_expr(Expr::not_end_with("name", "z")),
284 ),
285 )
286 .unwrap();
287
288 assert_eq!(
289 query.sql,
290 format!(
291 "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))"
292 )
293 );
294 assert_eq!(
295 query.params,
296 vec![
297 Value::from("%tea%"),
298 Value::from("t%"),
299 Value::from("%a"),
300 Value::from("%coffee%"),
301 Value::from("x%"),
302 Value::from("%z"),
303 ]
304 );
305 }
306
307 #[test]
308 fn compiles_search_with_text() {
309 let query = TestDialect
310 .compile_select(&entity(), &SelectQuery::new("Order").search_with_text("AI"))
311 .unwrap();
312
313 assert_eq!(
314 query.sql,
315 format!("SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (\"name\" LIKE $1)")
316 );
317 assert_eq!(query.params, vec![Value::from("%AI%")]);
318 }
319
320 #[test]
321 fn dialect_schema_setup_defaults_to_empty() {
322 assert!(TestDialect.schema_setup_sqls().is_empty());
323 }
324
325 #[test]
326 fn compiles_insert_update_delete_and_recover() {
327 let insert = TestDialect
328 .compile_insert(
329 &entity(),
330 &InsertCommand::new("Order")
331 .value("id", 1_u64)
332 .value("name", "A"),
333 )
334 .unwrap();
335 assert_eq!(
336 insert.sql,
337 "INSERT INTO \"orders\" (\"id\", \"name\") VALUES ($1, $2)"
338 );
339
340 let update = TestDialect
341 .compile_update(
342 &entity(),
343 &UpdateCommand::new("Order", 1_u64)
344 .expected_version(3)
345 .value("name", "B"),
346 )
347 .unwrap();
348 assert_eq!(
349 update.sql,
350 "UPDATE \"orders\" SET \"name\" = $1, \"version\" = $2 WHERE \"id\" = $3 AND \"version\" = $4"
351 );
352
353 let delete = TestDialect
354 .compile_delete(
355 &entity(),
356 &DeleteCommand::new("Order", 1_u64).expected_version(3),
357 )
358 .unwrap();
359 assert_eq!(
360 delete.sql,
361 "UPDATE \"orders\" SET \"version\" = $1 WHERE \"id\" = $2 AND \"version\" = $3"
362 );
363
364 let recover = TestDialect
365 .compile_recover(&entity(), &RecoverCommand::new("Order", 1_u64, -4))
366 .unwrap();
367 assert_eq!(
368 recover.sql,
369 "UPDATE \"orders\" SET \"version\" = $1 WHERE \"id\" = $2 AND \"version\" = $3"
370 );
371 }
372
373 #[test]
374 fn compiles_in_expression_and_validates_empty_list() {
375 let query = TestDialect
376 .compile_select(
377 &entity(),
378 &SelectQuery::new("Order").filter(Expr::Binary {
379 left: Box::new(Expr::column("id")),
380 op: BinaryOp::In,
381 right: Box::new(Expr::Value(Value::List(vec![1_u64.into(), 2_u64.into()]))),
382 }),
383 )
384 .unwrap();
385 assert_eq!(
386 query.sql,
387 format!("SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (\"id\" IN ($1, $2))")
388 );
389
390 let err = TestDialect
391 .compile_select(
392 &entity(),
393 &SelectQuery::new("Order").filter(Expr::Binary {
394 left: Box::new(Expr::column("id")),
395 op: BinaryOp::In,
396 right: Box::new(Expr::Value(Value::List(vec![]))),
397 }),
398 )
399 .unwrap_err();
400 assert!(matches!(err, SqlCompileError::EmptyInList));
401 }
402
403 #[test]
404 fn generic_dialect_expands_large_in_expressions() {
405 let query = TestDialect
406 .compile_select(
407 &entity(),
408 &SelectQuery::new("Order").filter(
409 Expr::in_large("id", vec![Value::from(1_u64), Value::from(2_u64)])
410 .and_expr(Expr::not_in_large("name", vec![Value::from("archived")])),
411 ),
412 )
413 .unwrap();
414
415 assert_eq!(
416 query.sql,
417 format!(
418 "SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE ((\"id\" IN ($1, $2)) AND (\"name\" NOT IN ($3)))"
419 )
420 );
421 assert_eq!(
422 query.params,
423 vec![
424 Value::from(1_u64),
425 Value::from(2_u64),
426 Value::from("archived")
427 ]
428 );
429 }
430
431 #[test]
432 fn compiles_property_to_property_filters() {
433 let query = TestDialect
434 .compile_select(
435 &entity(),
436 &SelectQuery::new("Order").filter(Expr::compare_columns(
437 "version",
438 BinaryOp::Gte,
439 "id",
440 )),
441 )
442 .unwrap();
443
444 assert_eq!(
445 query.sql,
446 format!(
447 "SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (\"version\" >= \"id\")"
448 )
449 );
450 assert!(query.params.is_empty());
451 }
452
453 #[test]
454 fn compiles_raw_escape_hatches_and_dynamic_properties() {
455 let query = TestDialect
456 .compile_select(
457 &entity(),
458 &SelectQuery::new("Order")
459 .comment("audit")
460 .project("id")
461 .project_raw("name", "upper(name)")
462 .dynamic_property_raw("score", "42")
463 .raw_sql_search_criteria("name <> ''")
464 .raw_sql_search_criteria("payload @> '{\"active\":true}'"),
465 )
466 .unwrap();
467
468 assert_eq!(
469 query.sql,
470 "SELECT \"id\", upper(name) AS \"name\", 42 AS \"score\" FROM \"orders\" WHERE name <> '' AND payload @> '{\"active\":true}'"
471 );
472 assert_eq!(query.comment.as_deref(), Some("audit"));
473 assert_eq!(
474 query.sql_with_comment(),
475 "/* audit */ SELECT \"id\", upper(name) AS \"name\", 42 AS \"score\" FROM \"orders\" WHERE name <> '' AND payload @> '{\"active\":true}'"
476 );
477 }
478
479 #[test]
480 fn compiles_raw_sql_override_with_comment() {
481 let query = TestDialect
482 .compile_select(
483 &entity(),
484 &SelectQuery::new("Order")
485 .comment("manual")
486 .raw_sql("SELECT 1 AS id"),
487 )
488 .unwrap();
489
490 assert_eq!(query.sql, "SELECT 1 AS id");
491 assert_eq!(query.comment.as_deref(), Some("manual"));
492 assert_eq!(query.sql_with_comment(), "/* manual */ SELECT 1 AS id");
493 }
494
495 #[test]
496 fn compiles_subquery_expression_and_appends_params_in_order() {
497 let query = TestDialect
498 .compile_select(
499 &entity(),
500 &SelectQuery::new("Order").filter(
501 Expr::in_subquery(
502 "id",
503 line_entity(),
504 SelectQuery::new("OrderLine")
505 .filter(Expr::eq("name", "line-1"))
506 .order_asc("id")
507 .limit(10),
508 "order_id",
509 )
510 .and_expr(Expr::eq("name", "order-1")),
511 ),
512 )
513 .unwrap();
514
515 assert_eq!(
516 query.sql,
517 format!(
518 "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))"
519 )
520 );
521 assert_eq!(
522 query.params,
523 vec![Value::from("line-1"), Value::from("order-1")]
524 );
525 }
526
527 #[test]
528 fn compiles_extended_aggregates_expression_projection_function_order_and_having() {
529 let query = TestDialect
530 .compile_select(
531 &entity(),
532 &SelectQuery::new("Order")
533 .group_by("name")
534 .project_expr("nameSound", Expr::soundex(Expr::column("name")))
535 .stddev("version", "stddevVersion")
536 .stddev_pop("version", "stddevPopVersion")
537 .var_samp("version", "varSampVersion")
538 .var_pop("version", "varPopVersion")
539 .bit_and("version", "bitAndVersion")
540 .bit_or("version", "bitOrVersion")
541 .bit_xor("version", "bitXorVersion")
542 .having(Expr::binary(
543 Expr::count_all(),
544 BinaryOp::Gt,
545 Expr::value(1_i64),
546 ))
547 .order_gbk_asc("name"),
548 )
549 .unwrap();
550
551 assert_eq!(
552 query.sql,
553 "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"
554 );
555 assert_eq!(query.params, vec![Value::I64(1)]);
556 }
557
558 #[test]
559 fn renders_postgres_debug_sql_with_inlined_params() {
560 let query = CompiledQuery {
561 sql: "SELECT * FROM \"orders\" WHERE ((\"name\" = $1) AND (\"id\" = ANY($2)) AND ('$3' = '$3'))".to_owned(),
562 params: vec![
563 Value::from("Bob's Shop"),
564 Value::List(vec![Value::from(1_u64), Value::from(2_u64)]),
565 ],
566 comment: None,
567 };
568
569 assert_eq!(
570 query.debug_sql(crate::DatabaseKind::PostgreSql),
571 "SELECT * FROM \"orders\" WHERE ((\"name\" = 'Bob''s Shop') AND (\"id\" = ANY(ARRAY[1, 2])) AND ('$3' = '$3'))"
572 );
573 }
574
575 #[test]
576 fn renders_sqlite_debug_sql_with_inlined_params() {
577 let query = CompiledQuery {
578 sql: "UPDATE \"orders\" SET \"name\" = ? WHERE ((\"id\" = ?) AND ('?' = '?'))"
579 .to_owned(),
580 params: vec![Value::from("Alice's Shop"), Value::from(7_u64)],
581 comment: None,
582 };
583
584 assert_eq!(
585 query.debug_sql(crate::DatabaseKind::Sqlite),
586 "UPDATE \"orders\" SET \"name\" = 'Alice''s Shop' WHERE ((\"id\" = 7) AND ('?' = '?'))"
587 );
588 }
589}