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