mod dialect;
mod types;
pub use dialect::SqlDialect;
pub use types::{CompiledQuery, DatabaseKind, SqlCompileError};
#[cfg(test)]
mod tests {
use teaql_core::{
BinaryOp, DataType, DeleteCommand, EntityDescriptor, Expr, InsertCommand, OrderBy,
PropertyDescriptor, RecoverCommand, SelectQuery, UpdateCommand, Value,
};
use crate::{CompiledQuery, SqlCompileError, SqlDialect};
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
struct TestDialect;
impl SqlDialect for TestDialect {
fn kind(&self) -> crate::DatabaseKind {
crate::DatabaseKind::PostgreSql
}
fn quote_ident(&self, ident: &str) -> String {
format!("\"{}\"", ident)
}
fn placeholder(&self, index: usize) -> String {
format!("${index}")
}
}
const ORDER_DEFAULT_PROJECTION: &str = "\"id\", \"version\", \"name\"";
fn entity() -> EntityDescriptor {
EntityDescriptor::new("Order")
.table_name("orders")
.property(
PropertyDescriptor::new("id", DataType::U64)
.column_name("id")
.id()
.not_null(),
)
.property(
PropertyDescriptor::new("version", DataType::I64)
.column_name("version")
.version()
.not_null(),
)
.property(PropertyDescriptor::new("name", DataType::Text).column_name("name"))
}
fn line_entity() -> EntityDescriptor {
EntityDescriptor::new("OrderLine")
.table_name("orderline")
.property(
PropertyDescriptor::new("id", DataType::U64)
.column_name("id")
.id()
.not_null(),
)
.property(PropertyDescriptor::new("order_id", DataType::U64).column_name("order_id"))
.property(PropertyDescriptor::new("name", DataType::Text).column_name("name"))
}
#[test]
fn compiles_select_with_filters_order_and_limit() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order")
.project("id")
.project("name")
.filter(Expr::eq("name", "A"))
.order_by(OrderBy::desc("id"))
.limit(10)
.offset(5),
)
.unwrap();
assert_eq!(
query,
CompiledQuery {
sql: "SELECT \"id\", \"name\" FROM \"orders\" WHERE (\"name\" = $1) ORDER BY \"id\" DESC LIMIT 10 OFFSET 5".to_owned(),
params: vec![Value::from("A")],
}
);
}
#[test]
fn compiles_aggregate_projection() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order").count_field("id", "count"),
)
.unwrap();
assert_eq!(
query.sql,
"SELECT COUNT(\"id\") AS \"count\" FROM \"orders\""
);
}
#[test]
fn compiles_grouped_aggregate_and_extended_predicates() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order")
.group_by("name")
.count("total")
.sum("version", "versionSum")
.filter(
Expr::between("version", 1_i64, 9_i64)
.and_expr(Expr::not_like("name", "tmp%"))
.and_expr(Expr::not_in_list(
"name",
vec![Value::from("x"), Value::from("y")],
))
.and_expr(Expr::is_not_null("name")),
)
.order_asc("name"),
)
.unwrap();
assert_eq!(
query.sql,
"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"
);
assert_eq!(
query.params,
vec![
Value::I64(1),
Value::I64(9),
Value::from("tmp%"),
Value::from("x"),
Value::from("y"),
]
);
}
#[test]
fn compiles_sound_like_expression() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order").filter(Expr::sound_like("name", "Robert")),
)
.unwrap();
assert_eq!(
query,
CompiledQuery {
sql: format!(
"SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (SOUNDEX(\"name\") = SOUNDEX($1))"
),
params: vec![Value::from("Robert")],
}
);
}
#[test]
fn compiles_java_style_string_match_builders() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order").filter(
Expr::contain("name", "tea")
.and_expr(Expr::begin_with("name", "t"))
.and_expr(Expr::end_with("name", "a"))
.and_expr(Expr::not_contain("name", "coffee"))
.and_expr(Expr::not_begin_with("name", "x"))
.and_expr(Expr::not_end_with("name", "z")),
),
)
.unwrap();
assert_eq!(
query.sql,
format!(
"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))"
)
);
assert_eq!(
query.params,
vec![
Value::from("%tea%"),
Value::from("t%"),
Value::from("%a"),
Value::from("%coffee%"),
Value::from("x%"),
Value::from("%z"),
]
);
}
#[test]
fn dialect_schema_setup_defaults_to_empty() {
assert!(TestDialect.schema_setup_sqls().is_empty());
}
#[test]
fn compiles_insert_update_delete_and_recover() {
let insert = TestDialect
.compile_insert(
&entity(),
&InsertCommand::new("Order")
.value("id", 1_u64)
.value("name", "A"),
)
.unwrap();
assert_eq!(
insert.sql,
"INSERT INTO \"orders\" (\"id\", \"name\") VALUES ($1, $2)"
);
let update = TestDialect
.compile_update(
&entity(),
&UpdateCommand::new("Order", 1_u64)
.expected_version(3)
.value("name", "B"),
)
.unwrap();
assert_eq!(
update.sql,
"UPDATE \"orders\" SET \"name\" = $1, \"version\" = $2 WHERE \"id\" = $3 AND \"version\" = $4"
);
let delete = TestDialect
.compile_delete(
&entity(),
&DeleteCommand::new("Order", 1_u64).expected_version(3),
)
.unwrap();
assert_eq!(
delete.sql,
"UPDATE \"orders\" SET \"version\" = $1 WHERE \"id\" = $2 AND \"version\" = $3"
);
let recover = TestDialect
.compile_recover(&entity(), &RecoverCommand::new("Order", 1_u64, -4))
.unwrap();
assert_eq!(
recover.sql,
"UPDATE \"orders\" SET \"version\" = $1 WHERE \"id\" = $2 AND \"version\" = $3"
);
}
#[test]
fn compiles_in_expression_and_validates_empty_list() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order").filter(Expr::Binary {
left: Box::new(Expr::column("id")),
op: BinaryOp::In,
right: Box::new(Expr::Value(Value::List(vec![1_u64.into(), 2_u64.into()]))),
}),
)
.unwrap();
assert_eq!(
query.sql,
format!("SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (\"id\" IN ($1, $2))")
);
let err = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order").filter(Expr::Binary {
left: Box::new(Expr::column("id")),
op: BinaryOp::In,
right: Box::new(Expr::Value(Value::List(vec![]))),
}),
)
.unwrap_err();
assert!(matches!(err, SqlCompileError::EmptyInList));
}
#[test]
fn generic_dialect_expands_large_in_expressions() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order").filter(
Expr::in_large("id", vec![Value::from(1_u64), Value::from(2_u64)])
.and_expr(Expr::not_in_large("name", vec![Value::from("archived")])),
),
)
.unwrap();
assert_eq!(
query.sql,
format!(
"SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE ((\"id\" IN ($1, $2)) AND (\"name\" NOT IN ($3)))"
)
);
assert_eq!(
query.params,
vec![
Value::from(1_u64),
Value::from(2_u64),
Value::from("archived")
]
);
}
#[test]
fn compiles_property_to_property_filters() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order").filter(Expr::compare_columns(
"version",
BinaryOp::Gte,
"id",
)),
)
.unwrap();
assert_eq!(
query.sql,
format!(
"SELECT {ORDER_DEFAULT_PROJECTION} FROM \"orders\" WHERE (\"version\" >= \"id\")"
)
);
assert!(query.params.is_empty());
}
#[test]
fn compiles_raw_escape_hatches_and_dynamic_properties() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order")
.comment("audit")
.project("id")
.project_raw("name", "upper(name)")
.dynamic_property_raw("score", "42")
.raw_sql_search_criteria("name <> ''")
.json_expr("payload @> '{\"active\":true}'"),
)
.unwrap();
assert_eq!(
query.sql,
"/* audit */ SELECT \"id\", upper(name) AS \"name\", 42 AS \"score\" FROM \"orders\" WHERE name <> '' AND payload @> '{\"active\":true}'"
);
}
#[test]
fn compiles_raw_sql_override_with_comment() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order")
.comment("manual")
.raw_sql("SELECT 1 AS id"),
)
.unwrap();
assert_eq!(query.sql, "/* manual */ SELECT 1 AS id");
}
#[test]
fn compiles_subquery_expression_and_appends_params_in_order() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order").filter(
Expr::in_subquery(
"id",
line_entity(),
SelectQuery::new("OrderLine")
.filter(Expr::eq("name", "line-1"))
.order_asc("id")
.limit(10),
"order_id",
)
.and_expr(Expr::eq("name", "order-1")),
),
)
.unwrap();
assert_eq!(
query.sql,
format!(
"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))"
)
);
assert_eq!(
query.params,
vec![Value::from("line-1"), Value::from("order-1")]
);
}
#[test]
fn compiles_extended_aggregates_expression_projection_function_order_and_having() {
let query = TestDialect
.compile_select(
&entity(),
&SelectQuery::new("Order")
.group_by("name")
.project_expr("nameSound", Expr::soundex(Expr::column("name")))
.stddev("version", "stddevVersion")
.stddev_pop("version", "stddevPopVersion")
.var_samp("version", "varSampVersion")
.var_pop("version", "varPopVersion")
.bit_and("version", "bitAndVersion")
.bit_or("version", "bitOrVersion")
.bit_xor("version", "bitXorVersion")
.having(Expr::binary(
Expr::count_all(),
BinaryOp::Gt,
Expr::value(1_i64),
))
.order_gbk_asc("name"),
)
.unwrap();
assert_eq!(
query.sql,
"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"
);
assert_eq!(query.params, vec![Value::I64(1)]);
}
#[test]
fn renders_postgres_debug_sql_with_inlined_params() {
let query = CompiledQuery {
sql: "SELECT * FROM \"orders\" WHERE ((\"name\" = $1) AND (\"id\" = ANY($2)) AND ('$3' = '$3'))".to_owned(),
params: vec![
Value::from("Bob's Shop"),
Value::List(vec![Value::from(1_u64), Value::from(2_u64)]),
],
};
assert_eq!(
query.debug_sql(crate::DatabaseKind::PostgreSql),
"SELECT * FROM \"orders\" WHERE ((\"name\" = 'Bob''s Shop') AND (\"id\" = ANY(ARRAY[1, 2])) AND ('$3' = '$3'))"
);
}
#[test]
fn renders_sqlite_debug_sql_with_inlined_params() {
let query = CompiledQuery {
sql: "UPDATE \"orders\" SET \"name\" = ? WHERE ((\"id\" = ?) AND ('?' = '?'))"
.to_owned(),
params: vec![Value::from("Alice's Shop"), Value::from(7_u64)],
};
assert_eq!(
query.debug_sql(crate::DatabaseKind::Sqlite),
"UPDATE \"orders\" SET \"name\" = 'Alice''s Shop' WHERE ((\"id\" = 7) AND ('?' = '?'))"
);
}
}