use super::{FetchClause, InsertBody, RowLimit, SelectItem, SetOperator};
use crate::{
BoolExpr, BoolOp, Field, Meta, OpSet, OrderItem, Param, RawStmt, Select, SetQuery, Source,
ValueExpr, cte, cte_ref, delete_from, insert, merge_into, raw, select, subquery, update,
};
static ID_META: Meta = Meta::new("id", "id", "int4").ops(OpSet::ordered());
static ID_FIELDS: [&Meta; 1] = [&ID_META];
const ID: Field<i32> = Field::new(&ID_META);
fn users() -> Source {
Source::Table {
name: "app_users",
alias: None,
fields: &ID_FIELDS,
}
}
#[test]
fn subquery_value_expr_renders_nested_params_at_expression_position() {
let subquery = crate::Stmt::Select(Box::new(Select {
ctes: Vec::new(),
source: users(),
joins: Vec::new(),
distinct: false,
distinct_on: Vec::new(),
projection: vec![SelectItem {
expr: ID.expr(),
alias: None,
}],
filter: Some(ID.eq(10)),
group_by: Vec::new(),
having: None,
order: Vec::new(),
row_limit: None,
offset: None,
lock: None,
}));
let outer = ValueExpr::Subquery(Box::new(subquery));
let built = select(users())
.expr(outer)
.filter(ID.eq(20))
.build()
.unwrap();
assert_eq!(
built.sql,
"SELECT (SELECT \"id\" FROM \"app_users\" WHERE \"id\" = $1) FROM \"app_users\" WHERE \"id\" = $2"
);
assert_eq!(built.params.len(), 2);
}
#[test]
fn select_params_follow_sql_text_order() {
let source = Source::Raw {
sql: "select ?::int4 as id".to_owned(),
alias: "generated".to_owned(),
params: vec![crate::Param::typed(1_i32)],
fields: vec![ID_META],
};
let stmt = crate::Stmt::Select(Box::new(Select {
ctes: Vec::new(),
source,
joins: Vec::new(),
distinct: false,
distinct_on: Vec::new(),
projection: vec![SelectItem {
expr: ValueExpr::Param(crate::Param::typed(2_i32)),
alias: Some("projected".to_owned()),
}],
filter: Some(BoolExpr::Compare {
left: ID.expr(),
op: BoolOp::Eq,
right: ValueExpr::Param(crate::Param::typed(3_i32)),
}),
group_by: Vec::new(),
having: None,
order: vec![OrderItem::asc(ID)],
row_limit: Some(RowLimit::Limit(crate::Param::typed(10_i64))),
offset: Some(crate::Param::typed(5_i64)),
lock: None,
}));
let params = stmt.build().unwrap().params;
assert_eq!(params.len(), 5);
}
#[test]
fn fetch_with_ties_requires_order() {
let without_order = crate::Stmt::Select(Box::new(Select {
ctes: Vec::new(),
source: users(),
joins: Vec::new(),
distinct: false,
distinct_on: Vec::new(),
projection: Vec::new(),
filter: None,
group_by: Vec::new(),
having: None,
order: Vec::new(),
row_limit: Some(RowLimit::Fetch(FetchClause {
count: ValueExpr::from(10_i32),
with_ties: true,
})),
offset: None,
lock: None,
}));
assert!(matches!(
without_order.validate().unwrap_err(),
crate::Error::InvalidSelectShape { message }
if message == "fetch with ties requires order_by"
));
}
#[test]
fn projection_and_returning_aliases_cannot_be_empty() {
let projection_err = select(users())
.expr_as(ID.expr(), "")
.validate()
.unwrap_err();
assert!(matches!(
projection_err,
crate::Error::InvalidSelectShape { message }
if message == "projection alias cannot be empty"
));
let returning_err = insert(users())
.set(ID.set(1))
.returning_as(ID.expr(), "")
.validate()
.unwrap_err();
assert!(matches!(
returning_err,
crate::Error::InvalidSelectShape { message }
if message == "returning alias cannot be empty"
));
}
#[test]
fn set_query_fetch_with_ties_requires_order() {
let without_order = select(users()).column(ID).union(select(users()).column(ID));
let err = without_order
.clone()
.fetch_first_with_ties(ValueExpr::from(10_i32))
.validate()
.unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidSelectShape { message }
if message == "fetch with ties requires order_by"
));
let with_limit = SetQuery {
left: without_order.left,
operator: SetOperator::Union,
right: without_order.right,
order: vec![OrderItem::asc(ID)],
row_limit: Some(RowLimit::Fetch(FetchClause {
count: ValueExpr::from(10_i32),
with_ties: false,
})),
offset: None,
};
with_limit.validate().unwrap();
}
#[test]
fn delete_requires_filter() {
let stmt = crate::Stmt::Delete(Box::new(crate::Delete {
ctes: Vec::new(),
target: users(),
using: Vec::new(),
filter: None,
returning: Vec::new(),
}));
assert!(matches!(
stmt.validate().unwrap_err(),
crate::Error::DeleteWithoutFilter
));
}
#[test]
fn returning_all_uses_source_fields_with_api_aliases() {
static NAME_META: Meta = Meta::new("displayName", "display_name", "text");
static RETURN_FIELDS: [&Meta; 2] = [&ID_META, &NAME_META];
let source = Source::Table {
name: "public.users",
alias: None,
fields: &RETURN_FIELDS,
};
let stmt = insert(source).set(ID.set(1)).returning_all();
let built = stmt.build().unwrap();
assert_eq!(
built.sql,
"INSERT INTO \"public\".\"users\" (\"id\") VALUES ($1) RETURNING \"users\".\"id\", \"users\".\"display_name\" AS \"displayName\""
);
}
#[test]
fn returning_all_replaces_existing_returning_fields() {
static NAME_META: Meta = Meta::new("displayName", "display_name", "text");
static RETURN_FIELDS: [&Meta; 2] = [&ID_META, &NAME_META];
let source = Source::Table {
name: "public.users",
alias: None,
fields: &RETURN_FIELDS,
};
let stmt = insert(source)
.set(ID.set(1))
.returning(ID)
.returning_all()
.returning_all();
let built = stmt.build().unwrap();
assert_eq!(
built.sql,
"INSERT INTO \"public\".\"users\" (\"id\") VALUES ($1) RETURNING \"users\".\"id\", \"users\".\"display_name\" AS \"displayName\""
);
}
#[test]
fn merge_returning_all_uses_target_qualifier_and_api_aliases() {
static NAME_META: Meta = Meta::new("displayName", "display_name", "text");
static RETURN_FIELDS: [&Meta; 2] = [&ID_META, &NAME_META];
let source = Source::Table {
name: "app_users",
alias: None,
fields: &RETURN_FIELDS,
};
let stmt = merge_into(
source.clone().alias("target"),
source.alias("incoming"),
ID.at("target").eq_field(ID.at("incoming")),
)
.when_matched()
.do_nothing()
.returning_all();
let built = stmt.build().unwrap();
assert_eq!(
built.sql,
"MERGE INTO \"app_users\" AS \"target\" USING \"app_users\" AS \"incoming\" ON \"target\".\"id\" = \"incoming\".\"id\" WHEN MATCHED THEN DO NOTHING RETURNING \"target\".\"id\", \"target\".\"display_name\" AS \"displayName\""
);
}
#[test]
fn select_filter_and_having_chain_with_and_semantics() {
let built = select(users())
.column(ID)
.filter(ID.gt(10))
.filter(ID.lt(20))
.group_by(ID)
.having(ID.gt(0))
.having(ID.lt(100))
.build()
.unwrap();
assert_eq!(
built.sql,
"SELECT \"id\" FROM \"app_users\" WHERE (\"id\" > $1 AND \"id\" < $2) GROUP BY \"id\" HAVING (\"id\" > $3 AND \"id\" < $4)"
);
}
#[test]
fn insert_from_select_rejects_projection_count_mismatch() {
static EMAIL_META: Meta = Meta::new("email", "email", "text").ops(OpSet::text());
const EMAIL: Field<String> = Field::new(&EMAIL_META);
let insert = insert(users()).from_select((ID, EMAIL), select(users()).column(ID));
let err = insert.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidInsertShape { message }
if message == "insert-select column count must match SELECT projection count"
));
}
#[test]
fn insert_from_select_requires_target_columns() {
let insert = insert(users()).from_select((), select(users()).column(ID));
let err = insert.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::EmptyColumns { statement } if statement == "insert-select"
));
}
#[test]
fn insert_from_select_all_requires_source_fields() {
let source = crate::raw_source("SELECT 1 AS id", "incoming", Vec::<Param>::new(), ());
let insert = insert(users()).from_select_all(source);
let err = insert.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::EmptyColumns { statement } if statement == "insert-select"
));
}
#[test]
fn insert_from_select_replaces_values_body() {
let insert = insert(users())
.set(ID.set(1))
.from_select(ID, select(users()).column(ID));
insert.validate().unwrap();
assert!(matches!(insert.body, InsertBody::Select { .. }));
}
#[test]
fn empty_conflict_constraint_name_is_rejected() {
let insert = insert(users())
.set(ID.set(1))
.on_conflict_constraint("")
.do_nothing();
let err = insert.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidInsertShape { message }
if message == "conflict constraint name cannot be empty"
));
}
#[test]
fn column_conflict_target_supports_multiple_fields_and_predicate() {
static EMAIL_META: Meta = Meta::new("email", "email", "text").ops(OpSet::text());
const EMAIL: Field<String> = Field::new(&EMAIL_META);
let built = insert(users())
.set(ID.set(1))
.on_conflict((ID, EMAIL))
.target_where(ID.gt(0))
.do_nothing()
.build()
.unwrap();
assert_eq!(
built.sql,
"INSERT INTO \"app_users\" (\"id\") VALUES ($1) ON CONFLICT (\"id\", \"email\") WHERE \"id\" > $2 DO NOTHING"
);
}
#[test]
fn conflict_update_requires_assignments() {
let insert = insert(users())
.set(ID.set(1))
.on_conflict(ID)
.do_update_set([]);
let err = insert.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::EmptyAssignments { statement } if statement == "conflict update"
));
}
#[test]
fn conflict_update_filter_is_validated() {
let insert = insert(users())
.set(ID.set(1))
.on_conflict(ID)
.do_update_set_where(
[ID.set(2)],
BoolExpr::Raw {
sql: "? = ?".to_owned(),
params: vec![Param::typed(1_i32)],
},
);
assert!(matches!(
insert.validate().unwrap_err(),
crate::Error::RawBindMismatch {
placeholders: 2,
binds: 1
}
));
}
#[test]
fn update_requires_assignments() {
let update = update(users()).filter(ID.eq(1));
let err = update.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::EmptyAssignments { statement } if statement == "update"
));
}
#[test]
fn update_or_filter_and_replace_filter_compose_where_predicates() {
let built = update(users())
.set(ID.set(1))
.filter(ID.gt(10))
.or_filter(ID.lt(3))
.build()
.unwrap();
assert_eq!(
built.sql,
"UPDATE \"app_users\" SET \"id\" = $1 WHERE (\"id\" > $2 OR \"id\" < $3)"
);
let built = update(users())
.set(ID.set(1))
.filter(ID.gt(10))
.replace_filter(ID.eq(7))
.build()
.unwrap();
assert_eq!(
built.sql,
"UPDATE \"app_users\" SET \"id\" = $1 WHERE \"id\" = $2"
);
}
#[test]
fn update_or_filter_conditionals_skip_or_apply_predicates() {
let built = update(users())
.set(ID.set(1))
.filter(ID.gt(10))
.or_filter_if(false, ID.lt(3))
.or_filter_option(Some(7), |id| ID.eq(id))
.or_filter_option(None::<i32>, |id| ID.eq(id))
.build()
.unwrap();
assert_eq!(
built.sql,
"UPDATE \"app_users\" SET \"id\" = $1 WHERE (\"id\" > $2 OR \"id\" = $3)"
);
}
#[test]
fn delete_or_filter_and_replace_filter_compose_where_predicates() {
let built = delete_from(users())
.filter(ID.gt(10))
.or_filter(ID.lt(3))
.build()
.unwrap();
assert_eq!(
built.sql,
"DELETE FROM \"app_users\" WHERE (\"id\" > $1 OR \"id\" < $2)"
);
let built = delete_from(users())
.filter(ID.gt(10))
.replace_filter(ID.eq(7))
.build()
.unwrap();
assert_eq!(built.sql, "DELETE FROM \"app_users\" WHERE \"id\" = $1");
}
#[test]
fn delete_or_filter_conditionals_skip_or_apply_predicates() {
let built = delete_from(users())
.filter(ID.gt(10))
.or_filter_if(false, ID.lt(3))
.or_filter_option(Some(7), |id| ID.eq(id))
.or_filter_option(None::<i32>, |id| ID.eq(id))
.build()
.unwrap();
assert_eq!(
built.sql,
"DELETE FROM \"app_users\" WHERE (\"id\" > $1 OR \"id\" = $2)"
);
}
#[test]
fn write_targets_reject_subquery_sources() {
let target = subquery(select(users()).column(ID), "u", ID);
let err = insert(target).set(ID.set(1)).validate().unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidWriteTarget(err) if err.statement == "insert" && err.source_kind == "subquery"
));
}
#[test]
fn cte_accepts_raw_statement_without_manual_stmt_variant() {
let ids = cte("ids", raw("SELECT ?::int4 AS id").bind(1_i32), ID);
assert!(select(ids.source()).with(ids).column(ID).validate().is_ok());
}
#[test]
fn update_and_delete_accept_empty_cte_lists_and_reject_duplicate_names() {
insert(users()).set(ID.set(1)).validate().unwrap();
update(users()).set(ID.set(1)).validate().unwrap();
delete_from(users()).filter(ID.eq(1)).validate().unwrap();
let first = cte("dupe", select(users()).column(ID), ID);
let second = cte("dupe", select(users()).column(ID), ID);
let err = insert(users())
.with(first)
.with(second)
.set(ID.set(1))
.validate()
.unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidCteShape(err) if err.name == "dupe" && err.message == "duplicate CTE name"
));
let first = cte("dupe", select(users()).column(ID), ID);
let second = cte("dupe", select(users()).column(ID), ID);
let err = update(users())
.with(first)
.with(second)
.set(ID.set(1))
.validate()
.unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidCteShape(err) if err.name == "dupe" && err.message == "duplicate CTE name"
));
let first = cte("dupe", select(users()).column(ID), ID);
let second = cte("dupe", select(users()).column(ID), ID);
let err = delete_from(users())
.with(first)
.with(second)
.filter(ID.eq(1))
.validate()
.unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidCteShape(err) if err.name == "dupe" && err.message == "duplicate CTE name"
));
}
#[test]
fn insert_default_values_body_can_be_replaced() {
insert(users()).default_values().validate().unwrap();
insert(users())
.default_values()
.returning(ID)
.validate()
.unwrap();
let values = crate::Insert::from(insert(users()).set(ID.set(1)));
values.validate().unwrap();
assert!(matches!(values.body, InsertBody::Values(_)));
let insert_select = insert(users())
.default_values()
.from_select(ID, select(users()).column(ID));
insert_select.validate().unwrap();
assert!(matches!(insert_select.body, InsertBody::Select { .. }));
}
#[test]
fn subquery_sources_reject_write_statements_after_into_stmt_conversion() {
let source = subquery(delete_from(users()).filter(ID.eq(1)), "deleted", ID);
let err = select(source).validate().unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidSelectShape { message }
if message == "subquery source must be SELECT, set, or raw statement"
));
}
#[test]
fn set_queries_reject_write_statement_operands() {
let set = select(users()).union(delete_from(users()).filter(ID.eq(1)));
let err = set.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidSelectShape { message }
if message == "set query operands must be SELECT, set, or raw statements"
));
}
#[test]
fn update_from_sources_are_validated() {
let update = update(users()).set(ID.set(1)).from(Source::Raw {
sql: "select ? as id".to_owned(),
alias: "incoming".to_owned(),
params: Vec::new(),
fields: vec![ID_META],
});
assert!(matches!(
update.validate().unwrap_err(),
crate::Error::RawBindMismatch {
placeholders: 1,
binds: 0
}
));
}
#[test]
fn returning_expressions_are_validated_for_writes() {
let insert = insert(users()).set(ID.set(1)).returning_as(
ValueExpr::Raw {
sql: "?".to_owned(),
params: Vec::new(),
},
"broken",
);
assert!(matches!(
insert.validate().unwrap_err(),
crate::Error::RawBindMismatch {
placeholders: 1,
binds: 0
}
));
}
#[test]
fn duplicate_cte_names_are_rejected_before_rendering() {
let first = cte("dupe", select(users()).column(ID), vec![ID_META]);
let second = cte("dupe", select(users()).column(ID), vec![ID_META]);
let err = select(users())
.with(first)
.with(second)
.validate()
.unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidCteShape(err) if err.name == "dupe" && err.message == "duplicate CTE name"
));
}
#[test]
fn merge_requires_at_least_one_action() {
let merge = merge_into(
users(),
users().alias("source"),
ID.eq_field(ID.at("source")),
);
let err = merge.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::InvalidMergeShape { message }
if message == "merge requires at least one action"
));
}
#[test]
fn merge_insert_requires_assignments() {
let merge = merge_into(
users(),
users().alias("source"),
ID.eq_field(ID.at("source")),
)
.when_not_matched()
.insert([]);
let err = merge.validate().unwrap_err();
assert!(matches!(
err,
crate::Error::EmptyAssignments { statement } if statement == "merge-insert"
));
}
#[test]
fn merge_rejects_non_table_target() {
let target = cte_ref("target_cte", vec![ID_META]);
let merge = merge_into(
target,
users().alias("source"),
ID.eq_field(ID.at("source")),
)
.when_matched()
.do_nothing();
assert!(matches!(
merge.validate().unwrap_err(),
crate::Error::InvalidWriteTarget(err) if err.statement == "merge" && err.source_kind == "cte"
));
}
#[test]
fn merge_validates_using_source_and_on_condition() {
let using = Source::Raw {
sql: "select ? as id".to_owned(),
alias: "source".to_owned(),
params: Vec::new(),
fields: vec![ID_META],
};
let merge = merge_into(users(), using, ID.eq_field(ID.at("source")))
.when_matched()
.do_nothing();
assert!(matches!(
merge.validate().unwrap_err(),
crate::Error::RawBindMismatch {
placeholders: 1,
binds: 0
}
));
}
#[test]
fn delete_using_preserves_required_filter() {
let built = delete_from(users())
.using(users().alias("old"))
.filter(ID.eq_field(ID.at("old")))
.returning(ID)
.build()
.unwrap();
assert_eq!(
built.sql,
"DELETE FROM \"app_users\" USING \"app_users\" AS \"old\" WHERE \"id\" = \"old\".\"id\" RETURNING \"id\""
);
}
#[test]
fn raw_statement_validates_bind_count() {
let err = raw("select ? + ?").bind(1_i32).validate().unwrap_err();
assert!(matches!(
err,
crate::Error::RawBindMismatch {
placeholders: 2,
binds: 1
}
));
let err = RawStmt {
sql: "select ?".to_owned(),
params: Vec::new(),
}
.validate()
.unwrap_err();
assert!(matches!(
err,
crate::Error::RawBindMismatch {
placeholders: 1,
binds: 0
}
));
}