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_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(&entity(), &SelectQuery::new("Order").search_with_text("AI"))
245 .unwrap();
246
247 assert_eq!(
248 query.sql,
249 format!("SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (\"name\" LIKE $1)")
250 );
251 assert_eq!(query.params, vec![Value::from("%AI%")]);
252 }
253
254 #[test]
255 fn dialect_schema_setup_defaults_to_empty() {
256 assert!(TestDialect.schema_setup_sqls().is_empty());
257 }
258
259 #[test]
260 fn compiles_insert_update_delete_and_recover() {
261 let insert = TestDialect
262 .compile_insert(
263 &entity(),
264 &InsertCommand::new("Order")
265 .value("id", 1_u64)
266 .value("name", "A"),
267 )
268 .unwrap();
269 assert_eq!(
270 insert.sql,
271 "INSERT INTO \"orders\" (\"id\", \"name\") VALUES ($1, $2)"
272 );
273
274 let update = TestDialect
275 .compile_update(
276 &entity(),
277 &UpdateCommand::new("Order", 1_u64)
278 .expected_version(3)
279 .value("name", "B"),
280 )
281 .unwrap();
282 assert_eq!(
283 update.sql,
284 "UPDATE \"orders\" SET \"name\" = $1, \"version\" = $2 WHERE \"id\" = $3 AND \"version\" = $4"
285 );
286
287 let delete = TestDialect
288 .compile_delete(
289 &entity(),
290 &DeleteCommand::new("Order", 1_u64).expected_version(3),
291 )
292 .unwrap();
293 assert_eq!(
294 delete.sql,
295 "UPDATE \"orders\" SET \"version\" = $1 WHERE \"id\" = $2 AND \"version\" = $3"
296 );
297
298 let recover = TestDialect
299 .compile_recover(&entity(), &RecoverCommand::new("Order", 1_u64, -4))
300 .unwrap();
301 assert_eq!(
302 recover.sql,
303 "UPDATE \"orders\" SET \"version\" = $1 WHERE \"id\" = $2 AND \"version\" = $3"
304 );
305 }
306
307 #[test]
308 fn compiles_in_expression_and_validates_empty_list() {
309 let query = TestDialect
310 .compile_select(
311 &entity(),
312 &SelectQuery::new("Order").filter(Expr::Binary {
313 left: Box::new(Expr::column("id")),
314 op: BinaryOp::In,
315 right: Box::new(Expr::Value(Value::List(vec![1_u64.into(), 2_u64.into()]))),
316 }),
317 )
318 .unwrap();
319 assert_eq!(
320 query.sql,
321 format!("SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (\"id\" IN ($1, $2))")
322 );
323
324 let err = TestDialect
325 .compile_select(
326 &entity(),
327 &SelectQuery::new("Order").filter(Expr::Binary {
328 left: Box::new(Expr::column("id")),
329 op: BinaryOp::In,
330 right: Box::new(Expr::Value(Value::List(vec![]))),
331 }),
332 )
333 .unwrap_err();
334 assert!(matches!(err, SqlCompileError::EmptyInList));
335 }
336
337 #[test]
338 fn generic_dialect_expands_large_in_expressions() {
339 let query = TestDialect
340 .compile_select(
341 &entity(),
342 &SelectQuery::new("Order").filter(
343 Expr::in_large("id", vec![Value::from(1_u64), Value::from(2_u64)])
344 .and_expr(Expr::not_in_large("name", vec![Value::from("archived")])),
345 ),
346 )
347 .unwrap();
348
349 assert_eq!(
350 query.sql,
351 format!(
352 "SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE ((\"id\" IN ($1, $2)) AND (\"name\" NOT IN ($3)))"
353 )
354 );
355 assert_eq!(
356 query.params,
357 vec![
358 Value::from(1_u64),
359 Value::from(2_u64),
360 Value::from("archived")
361 ]
362 );
363 }
364
365 #[test]
366 fn compiles_property_to_property_filters() {
367 let query = TestDialect
368 .compile_select(
369 &entity(),
370 &SelectQuery::new("Order").filter(Expr::compare_columns(
371 "version",
372 BinaryOp::Gte,
373 "id",
374 )),
375 )
376 .unwrap();
377
378 assert_eq!(
379 query.sql,
380 format!(
381 "SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (\"version\" >= \"id\")"
382 )
383 );
384 assert!(query.params.is_empty());
385 }
386
387 #[test]
388 fn compiles_raw_escape_hatches_and_dynamic_properties() {
389 let query = TestDialect
390 .compile_select(
391 &entity(),
392 &SelectQuery::new("Order")
393 .comment("audit")
394 .project("id")
395 .project_raw("name", "upper(name)")
396 .dynamic_property_raw("score", "42")
397 .raw_sql_search_criteria("name <> ''")
398 .raw_sql_search_criteria("payload @> '{\"active\":true}'"),
399 )
400 .unwrap();
401
402 assert_eq!(
403 query.sql,
404 "SELECT \"id\", upper(name) AS \"name\", 42 AS \"score\" FROM \"orders\" WHERE name <> '' AND payload @> '{\"active\":true}'"
405 );
406 assert_eq!(query.comment.as_deref(), Some("audit"));
407 assert_eq!(
408 query.sql_with_comment(),
409 "/* audit */ SELECT \"id\", upper(name) AS \"name\", 42 AS \"score\" FROM \"orders\" WHERE name <> '' AND payload @> '{\"active\":true}'"
410 );
411 }
412
413 #[test]
414 fn compiles_raw_sql_override_with_comment() {
415 let query = TestDialect
416 .compile_select(
417 &entity(),
418 &SelectQuery::new("Order")
419 .comment("manual")
420 .raw_sql("SELECT 1 AS id"),
421 )
422 .unwrap();
423
424 assert_eq!(query.sql, "SELECT 1 AS id");
425 assert_eq!(query.comment.as_deref(), Some("manual"));
426 assert_eq!(query.sql_with_comment(), "/* manual */ SELECT 1 AS id");
427 }
428
429 #[test]
430 fn compiles_subquery_expression_and_appends_params_in_order() {
431 let query = TestDialect
432 .compile_select(
433 &entity(),
434 &SelectQuery::new("Order").filter(
435 Expr::in_subquery(
436 "id",
437 line_entity(),
438 SelectQuery::new("OrderLine")
439 .filter(Expr::eq("name", "line-1"))
440 .order_asc("id")
441 .limit(10),
442 "order_id",
443 )
444 .and_expr(Expr::eq("name", "order-1")),
445 ),
446 )
447 .unwrap();
448
449 assert_eq!(
450 query.sql,
451 format!(
452 "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))"
453 )
454 );
455 assert_eq!(
456 query.params,
457 vec![Value::from("line-1"), Value::from("order-1")]
458 );
459 }
460
461 #[test]
462 fn compiles_extended_aggregates_expression_projection_function_order_and_having() {
463 let query = TestDialect
464 .compile_select(
465 &entity(),
466 &SelectQuery::new("Order")
467 .group_by("name")
468 .project_expr("nameSound", Expr::soundex(Expr::column("name")))
469 .stddev("version", "stddevVersion")
470 .stddev_pop("version", "stddevPopVersion")
471 .var_samp("version", "varSampVersion")
472 .var_pop("version", "varPopVersion")
473 .bit_and("version", "bitAndVersion")
474 .bit_or("version", "bitOrVersion")
475 .bit_xor("version", "bitXorVersion")
476 .having(Expr::binary(
477 Expr::count_all(),
478 BinaryOp::Gt,
479 Expr::value(1_i64),
480 ))
481 .order_gbk_asc("name"),
482 )
483 .unwrap();
484
485 assert_eq!(
486 query.sql,
487 "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"
488 );
489 assert_eq!(query.params, vec![Value::I64(1)]);
490 }
491
492 #[test]
493 fn renders_postgres_debug_sql_with_inlined_params() {
494 let query = CompiledQuery {
495 sql: "SELECT * FROM \"orders\" WHERE ((\"name\" = $1) AND (\"id\" = ANY($2)) AND ('$3' = '$3'))".to_owned(),
496 params: vec![
497 Value::from("Bob's Shop"),
498 Value::List(vec![Value::from(1_u64), Value::from(2_u64)]),
499 ],
500 comment: None,
501 };
502
503 assert_eq!(
504 query.debug_sql(crate::DatabaseKind::PostgreSql),
505 "SELECT * FROM \"orders\" WHERE ((\"name\" = 'Bob''s Shop') AND (\"id\" = ANY(ARRAY[1, 2])) AND ('$3' = '$3'))"
506 );
507 }
508
509 #[test]
510 fn renders_sqlite_debug_sql_with_inlined_params() {
511 let query = CompiledQuery {
512 sql: "UPDATE \"orders\" SET \"name\" = ? WHERE ((\"id\" = ?) AND ('?' = '?'))"
513 .to_owned(),
514 params: vec![Value::from("Alice's Shop"), Value::from(7_u64)],
515 comment: None,
516 };
517
518 assert_eq!(
519 query.debug_sql(crate::DatabaseKind::Sqlite),
520 "UPDATE \"orders\" SET \"name\" = 'Alice''s Shop' WHERE ((\"id\" = 7) AND ('?' = '?'))"
521 );
522 }
523}