use crate::{
db::sql::parser::{
SqlAggregateCall, SqlExpr, SqlExprBinaryOp, SqlOrderDirection, SqlOrderTerm, SqlProjection,
SqlSelectItem, SqlSelectStatement, SqlStatement, parse_sql,
},
value::Value,
};
fn sql_order_expr(term: &str) -> SqlExpr {
let sql = format!("SELECT id FROM NormalizeOrderEntity ORDER BY {term}");
let SqlStatement::Select(statement) =
parse_sql(&sql).expect("normalize ORDER BY term helper SQL should parse")
else {
unreachable!("normalize ORDER BY term helper should always produce one SELECT");
};
statement
.order_by
.into_iter()
.next()
.expect("normalize ORDER BY term helper SQL should carry one ORDER BY term")
.field
}
#[test]
fn order_and_having_aliases_share_projection_families_and_case_matching() {
for projection_sql in ["age", "age + 1", "COUNT(*)"] {
let sql = format!("SELECT {projection_sql} AS total FROM E");
let SqlStatement::Select(statement) = parse_sql(&sql).expect("projection") else {
panic!("select projection");
};
let SqlProjection::Items(items) = &statement.projection else {
panic!("projection items");
};
let expected = SqlExpr::from_select_item(&items[0]);
let aliases = &statement.projection_aliases;
let having = crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_having_clauses(
vec![
SqlExpr::Field("TOTAL".into()),
SqlExpr::Field("missing".into()),
],
&statement.projection,
aliases,
&[],
work,
)
})
.expect("normalization");
let order = crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_select_order_terms(
vec![SqlOrderTerm {
field: SqlExpr::Field("TOTAL".into()),
direction: SqlOrderDirection::Desc,
}],
&statement.projection,
aliases,
&[],
work,
)
})
.expect("normalization");
assert_eq!(having, [expected.clone(), SqlExpr::Field("missing".into())]);
assert_eq!(order[0].field, expected);
assert_eq!(order[0].direction, SqlOrderDirection::Desc);
}
}
#[test]
fn alias_replacements_are_single_pass_and_aggregate_inputs_remain_opaque() {
let projection = SqlProjection::Items(vec![
SqlSelectItem::Field("second".into()),
SqlSelectItem::Field("third".into()),
]);
let aliases = [Some("first".into()), Some("second".into())];
let aggregate = SqlExpr::Aggregate(SqlAggregateCall {
kind: crate::db::sql::parser::SqlAggregateKind::Count,
input: Some(Box::new(SqlExpr::Field("first".into()))),
filter_expr: None,
distinct: false,
});
let clauses = crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_having_clauses(
vec![SqlExpr::Field("first".into()), aggregate.clone()],
&projection,
&aliases,
&[],
work,
)
})
.expect("normalization");
assert_eq!(clauses, [SqlExpr::Field("second".into()), aggregate]);
let order = crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_select_order_terms(
vec![SqlOrderTerm {
field: SqlExpr::Field("first".into()),
direction: SqlOrderDirection::Asc,
}],
&projection,
&aliases,
&[],
work,
)
})
.expect("normalization");
assert_eq!(order[0].field, SqlExpr::Field("second".into()));
}
#[test]
fn normalized_field_paths_move_retained_payloads_and_keep_longest_scope_match() {
use super::normalize_field_path_to_scope;
for (scope, root, tail, expected_root, expected_tail) in [
(
vec!["users"],
"profile",
vec!["address", "city"],
"profile",
vec!["address", "city"],
),
(
vec!["users"],
"users",
vec!["profile", "city"],
"profile",
vec!["city"],
),
(
vec!["app.users", "app"],
"app",
vec!["users", "name"],
"name",
vec![],
),
(vec!["USERS"], "users", vec!["name"], "name", vec![]),
] {
let root = root.to_string();
let tail: Vec<String> = tail.into_iter().map(str::to_string).collect();
let retained: Vec<_> = std::iter::once(&root)
.chain(&tail)
.filter(|part| part.as_str() == expected_root || expected_tail.contains(&part.as_str()))
.map(|part| part.as_ptr())
.collect();
let scope: Vec<_> = scope.into_iter().map(str::to_string).collect();
let result = crate::db::query::preparation::with_preparation_work(|work| {
normalize_field_path_to_scope(root, tail, &scope, work)
})
.expect("path normalization");
let (root, tail): (&String, &[String]) = match &result {
SqlExpr::Field(root) => (root, &[]),
SqlExpr::FieldPath { root, segments } => (root, segments),
other => panic!("unexpected normalized field: {other:?}"),
};
assert_eq!(root, expected_root);
assert_eq!(tail, expected_tail.as_slice());
assert_eq!(
std::iter::once(root)
.chain(tail)
.map(|part| part.as_ptr())
.collect::<Vec<_>>(),
retained
);
}
}
#[test]
fn plain_field_normalization_keeps_its_owned_string() {
let field = "profile".to_string();
let pointer = field.as_ptr();
let result = crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_field_identifier_expr_to_scope(field, &["users".into()], work)
})
.expect("field normalization");
let SqlExpr::Field(field) = &result else {
panic!("expected plain field")
};
assert_eq!(field, "profile");
assert_eq!(field.as_ptr(), pointer);
}
#[test]
fn scoped_select_preserves_record_paths_with_inner_scope_names() {
for path in ["owner.account.id", "owner.s.id", "other.Account.id"] {
let sql = format!(
"SELECT {path} FROM app.Account s WHERE {path} = 5 GROUP BY {path} \
HAVING {path} = 5 ORDER BY {path}"
);
let SqlStatement::Select(statement) = parse_sql(&sql).unwrap() else {
panic!("SELECT");
};
let mut reference = statement.clone();
reference.entity = "Different".into();
reference.table_alias = None;
let reference = crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_select_statement_to_expected_entity(reference, "Different", work)
})
.unwrap();
let normalized = crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_select_statement_to_expected_entity(statement, "Account", work)
})
.unwrap();
assert_eq!(normalized.projection, reference.projection, "{path}");
assert_eq!(normalized.predicate, reference.predicate, "{path}");
assert_eq!(normalized.group_by, reference.group_by, "{path}");
assert_eq!(normalized.having, reference.having, "{path}");
assert_eq!(normalized.order_by, reference.order_by, "{path}");
}
}
#[test]
fn scope_qualification_is_reduced_once_for_every_expression_surface() {
for (qualifier, local) in [
("s", "s.id"),
("Account", "Account.id"),
("app.Account", "Account.id"),
] {
for condition in ["= 5", "IS NULL", "IS NOT NULL"] {
let qualified = format!("{qualifier}.{local}");
let normalize = |path: &str, entity: &str, alias: &str| {
let sql = format!(
"SELECT {path} FROM {entity} {alias} WHERE {path} {condition} \
GROUP BY {path} HAVING {path} {condition} ORDER BY {path}"
);
let SqlStatement::Select(statement) = parse_sql(&sql).unwrap() else {
panic!("SELECT");
};
crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_select_statement_to_expected_entity(statement, entity, work)
})
.unwrap()
};
let actual = normalize(&qualified, "app.Account", "s");
let mut expected = normalize(local, "Different", "z");
expected.entity.clone_from(&actual.entity);
assert_eq!(actual, expected, "{qualified} {condition}");
}
}
}
#[test]
fn scoped_writes_preserve_paths_in_predicates_values_order_and_returning() {
for (input, local) in [
("owner.account.id", "owner.account.id"),
("owner.s.id", "owner.s.id"),
("s.owner.s.id", "owner.s.id"),
("s.s.id", "s.id"),
("app.Account.Account.id", "Account.id"),
] {
for update in [false, true] {
let normalize = |path: &str, entity: &str, alias: &str| {
let prefix = if update {
format!("UPDATE {entity} {alias} SET marker = {path}")
} else {
format!("DELETE FROM {entity} {alias}")
};
let sql = format!(
"{prefix} WHERE {path} IS NOT NULL ORDER BY {path} LIMIT 1 RETURNING {path}"
);
crate::db::query::preparation::with_preparation_work(|work| {
match parse_sql(&sql).unwrap() {
SqlStatement::Update(statement) => {
super::normalize_update_statement_to_expected_entity(
statement, entity, work,
)
.map(SqlStatement::Update)
}
SqlStatement::Delete(statement) => {
super::normalize_delete_statement_to_expected_entity(
statement, entity, work,
)
.map(SqlStatement::Delete)
}
_ => panic!("write statement"),
}
})
.unwrap()
};
let actual = normalize(input, "app.Account", "s");
let mut expected = normalize(local, "Different", "z");
match &mut expected {
SqlStatement::Update(statement) => statement.entity = "app.Account".into(),
SqlStatement::Delete(statement) => statement.entity = "app.Account".into(),
_ => panic!("write statement"),
}
assert_eq!(actual, expected, "{input}");
}
}
}
#[test]
fn local_scalar_select_is_already_local_canonical() {
let statement = SqlSelectStatement {
entity: "PerfAuditUser".to_string(),
table_alias: None,
projection: SqlProjection::Items(vec![
SqlSelectItem::Field("id".to_string()),
SqlSelectItem::Field("age".to_string()),
]),
projection_aliases: vec![None, None],
predicate: Some(SqlExpr::Binary {
op: SqlExprBinaryOp::And,
left: Box::new(SqlExpr::Binary {
op: SqlExprBinaryOp::Ne,
left: Box::new(SqlExpr::Field("age".to_string())),
right: Box::new(SqlExpr::Literal(Value::Int64(24))),
}),
right: Box::new(SqlExpr::Binary {
op: SqlExprBinaryOp::Ne,
left: Box::new(SqlExpr::Field("age".to_string())),
right: Box::new(SqlExpr::Literal(Value::Int64(31))),
}),
}),
distinct: false,
group_by: vec![],
having: vec![],
order_by: vec![SqlOrderTerm {
field: sql_order_expr("id"),
direction: SqlOrderDirection::Asc,
}],
limit: Some(3),
offset: None,
};
assert!(statement.is_already_local_canonical());
}
#[test]
fn local_scalar_select_with_supported_order_expr_is_already_local_canonical() {
let statement = SqlSelectStatement {
entity: "PerfAuditUser".to_string(),
table_alias: None,
projection: SqlProjection::Items(vec![
SqlSelectItem::Field("id".to_string()),
SqlSelectItem::Field("name".to_string()),
]),
projection_aliases: vec![None, None],
predicate: None,
distinct: false,
group_by: vec![],
having: vec![],
order_by: vec![SqlOrderTerm {
field: sql_order_expr("LOWER(name)"),
direction: SqlOrderDirection::Asc,
}],
limit: Some(3),
offset: None,
};
assert!(statement.is_already_local_canonical());
}
#[test]
fn local_grouped_select_with_local_aggregate_is_already_local_canonical() {
let statement = SqlSelectStatement {
entity: "PerfAuditUser".to_string(),
table_alias: None,
projection: SqlProjection::Items(vec![
SqlSelectItem::Field("age".to_string()),
SqlSelectItem::Aggregate(SqlAggregateCall {
kind: crate::db::sql::parser::SqlAggregateKind::Count,
input: None,
filter_expr: None,
distinct: false,
}),
]),
projection_aliases: vec![None, None],
predicate: None,
distinct: false,
group_by: vec!["age".to_string()],
having: vec![],
order_by: vec![SqlOrderTerm {
field: sql_order_expr("age"),
direction: SqlOrderDirection::Asc,
}],
limit: Some(10),
offset: None,
};
assert!(statement.is_already_local_canonical());
}
#[test]
fn qualified_field_select_is_not_already_local_canonical() {
let statement = SqlSelectStatement {
entity: "public.PerfAuditUser".to_string(),
table_alias: None,
projection: SqlProjection::Items(vec![SqlSelectItem::Field(
"PerfAuditUser.id".to_string(),
)]),
projection_aliases: vec![None],
predicate: Some(SqlExpr::Binary {
op: SqlExprBinaryOp::Eq,
left: Box::new(SqlExpr::Field("PerfAuditUser.age".to_string())),
right: Box::new(SqlExpr::Literal(Value::Int64(24))),
}),
distinct: false,
group_by: vec![],
having: vec![],
order_by: vec![SqlOrderTerm {
field: sql_order_expr("PerfAuditUser.id"),
direction: SqlOrderDirection::Asc,
}],
limit: Some(1),
offset: None,
};
assert!(!statement.is_already_local_canonical());
}
#[test]
fn predicate_identifier_normalization_preserves_nested_field_paths() {
let statement = SqlSelectStatement {
entity: "users".to_string(),
table_alias: Some("u".to_string()),
projection: SqlProjection::All,
projection_aliases: vec![],
predicate: Some(SqlExpr::Binary {
op: SqlExprBinaryOp::And,
left: Box::new(SqlExpr::Binary {
op: SqlExprBinaryOp::Eq,
left: Box::new(SqlExpr::Field("profile.rank".to_string())),
right: Box::new(SqlExpr::Literal(Value::Int64(5))),
}),
right: Box::new(SqlExpr::Binary {
op: SqlExprBinaryOp::Eq,
left: Box::new(SqlExpr::Field("u.age".to_string())),
right: Box::new(SqlExpr::Literal(Value::Int64(21))),
}),
}),
distinct: false,
group_by: vec![],
having: vec![],
order_by: vec![],
limit: None,
offset: None,
};
let normalized = crate::db::query::preparation::with_preparation_work(|work| {
super::normalize_select_statement_to_expected_entity(statement, "users", work)
})
.expect("predicate identifiers should normalize");
assert_eq!(
normalized.predicate,
Some(SqlExpr::Binary {
op: SqlExprBinaryOp::And,
left: Box::new(SqlExpr::Binary {
op: SqlExprBinaryOp::Eq,
left: Box::new(SqlExpr::FieldPath {
root: "profile".to_string(),
segments: vec!["rank".to_string()],
}),
right: Box::new(SqlExpr::Literal(Value::Int64(5))),
}),
right: Box::new(SqlExpr::Binary {
op: SqlExprBinaryOp::Eq,
left: Box::new(SqlExpr::Field("age".to_string())),
right: Box::new(SqlExpr::Literal(Value::Int64(21))),
}),
}),
);
}
#[test]
fn sql_insert_aliases_normalize_default_values_and_nested_target_paths() {
use crate::db::{
query::preparation::with_preparation_work,
sql::{lowering::prepare::prepare_sql_statement, parser::SqlReturningProjection},
};
for (sql, expected_columns, expected_returning) in [
(
"INSERT INTO app.users AS u DEFAULT VALUES RETURNING u.id",
vec![],
vec!["id"],
),
(
"INSERT INTO app.users AS u (u.profile.rank) VALUES (1) RETURNING u.profile.rank",
vec!["profile.rank"],
vec!["profile.rank"],
),
] {
let parsed = parse_sql(sql).unwrap();
let prepared =
with_preparation_work(|work| prepare_sql_statement(&parsed, "users", work)).unwrap();
let SqlStatement::Insert(insert) = prepared.into_statement() else {
panic!("prepared INSERT");
};
assert_eq!(insert.columns, expected_columns);
assert_eq!(
insert.returning,
Some(SqlReturningProjection::Fields(
expected_returning.into_iter().map(str::to_string).collect()
))
);
assert_eq!(insert.table_alias, None);
}
}