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