#![cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
use super::{QueryBuilder, SelectClause};
use crate::{
FieldName,
comparison::sql::{QuoteIdent, SqlConjunction},
};
#[cfg(feature = "sqlite")]
#[test]
fn the_where_clause_opens_once_and_joins_after() {
let mut qb = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"t\"")
.begin_where();
{
let mut conjunction = SqlConjunction::new(qb.inner_mut());
conjunction.open().push("\"a\" = ").push_bind(1i64);
conjunction.open().push("\"b\" = ").push_bind(2i64);
conjunction.open().push("\"c\" = ").push_bind(3i64);
}
assert_eq!(
qb.finalize().into_string(),
"SELECT \"id\" FROM \"t\" WHERE \"a\" = ? AND \"b\" = ? AND \"c\" = ?"
);
}
#[cfg(feature = "sqlite")]
#[test]
fn entering_the_predicate_list_renders_nothing() {
let sql = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"t\"")
.begin_where()
.begin_where()
.finalize()
.into_string();
assert_eq!(sql, "SELECT \"id\" FROM \"t\"");
}
#[cfg(feature = "sqlite")]
#[test]
fn the_clause_walk_renders_in_transition_order() {
let mut qb = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.push_field("\"n\"")
.from("\"t\"")
.begin_where();
{
SqlConjunction::new(qb.inner_mut())
.open()
.push("\"a\" = ")
.push_bind(1i64);
}
let sql = qb
.order_by(FieldName::new("n"), true)
.limit(10)
.offset(20)
.finalize()
.into_string();
assert_eq!(
sql,
"SELECT \"id\", \"n\" FROM \"t\" WHERE \"a\" = ? ORDER BY \"n\" DESC LIMIT ? OFFSET ?"
);
}
#[cfg(feature = "sqlite")]
#[test]
fn two_ordering_terms_join_with_a_comma() {
let mut qb = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"t\"")
.begin_where();
{
SqlConjunction::new(qb.inner_mut())
.open()
.push("\"a\" = ")
.push_bind(1i64);
}
let sql = qb
.order_by(FieldName::new("score"), true)
.order_by(FieldName::new("id"), false)
.finalize()
.into_string();
assert_eq!(
sql,
"SELECT \"id\" FROM \"t\" WHERE \"a\" = ? ORDER BY \"score\" DESC, \"id\" ASC"
);
}
#[cfg(feature = "sqlite")]
#[test]
fn an_ordering_term_can_be_a_correlated_subquery() {
use sqlx::Execute;
use crate::{
Carrier,
comparison::sql::{NullPosition, tests::Probe},
};
let bound = core::cell::RefCell::new(Vec::new());
let mut qb = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"media\"")
.begin_where();
{
let mut accumulator = SqlConjunction::new(qb.inner_mut());
let slot = accumulator.open();
slot.push("\"kind\" = ");
Probe {
value: 1,
bound: &bound,
}
.push_bind(slot);
}
let mut statement = qb
.order_by_with(
|qb| {
qb.push("(");
let mut nested = QueryBuilder::<SelectClause, sqlx::Sqlite, _>::select_into(&mut *qb)
.push_field("\"title\"")
.from("\"keyframe\"")
.begin_where();
{
let mut correlation = SqlConjunction::new(nested.inner_mut());
let slot = correlation.open();
slot.push("\"id\" = ");
Probe {
value: 2,
bound: &bound,
}
.push_bind(slot);
}
qb.push(")");
},
true,
Some(NullPosition::Last),
)
.order_by(FieldName::new("id"), false)
.limit(10)
.finalize();
let mut built = statement.build();
let binds = match built.take_arguments() {
Ok(Some(arguments)) => sqlx::Arguments::len(&arguments),
Ok(None) | Err(_) => 0,
};
assert_eq!(
built.sql().as_str(),
"SELECT \"id\" FROM \"media\" WHERE \"kind\" = ? \
ORDER BY (SELECT \"title\" FROM \"keyframe\" WHERE \"id\" = ?) DESC NULLS LAST, \"id\" ASC \
LIMIT ?"
);
assert_eq!(*bound.borrow(), [1, 2]);
assert_eq!(binds, 3);
}
#[cfg(feature = "sqlite")]
#[test]
fn two_expression_terms_join_with_a_comma() {
let sql = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("*")
.from("t")
.order_by_with(
|qb| {
qb.push("(SELECT 1)");
},
false,
None,
)
.order_by_with(
|qb| {
qb.push("(SELECT 2)");
},
true,
None,
)
.finalize()
.into_string();
assert_eq!(
sql,
"SELECT * FROM t ORDER BY (SELECT 1) ASC, (SELECT 2) DESC"
);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_sub_select_term_pairs_the_brackets_the_expression_form_leaves_to_its_caller() {
use sqlx::Execute;
use crate::{
Carrier,
comparison::sql::{NullPosition, tests::Probe},
};
let bound = core::cell::RefCell::new(Vec::new());
let mut statement = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("*")
.from("t")
.order_by_sub_select(
|qb| {
qb.push("SELECT \"title\" FROM \"keyframe\" WHERE \"id\" = ");
Probe {
value: 4,
bound: &bound,
}
.push_bind(qb);
},
true,
Some(NullPosition::Last),
)
.order_by(FieldName::new("id"), false)
.finalize();
let built = statement.build();
assert_eq!(
built.sql().as_str(),
"SELECT * FROM t \
ORDER BY (SELECT \"title\" FROM \"keyframe\" WHERE \"id\" = ?) DESC NULLS LAST, \"id\" ASC"
);
assert_eq!(*bound.borrow(), [4]);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_column_term_is_the_expression_form_with_its_name_bracketed() {
use crate::comparison::sql::quote_ident;
let named = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("*")
.from("t")
.order_by(FieldName::new("title"), true)
.order_by(FieldName::new("id"), false)
.finalize()
.into_string();
let written = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("*")
.from("t")
.order_by_with(
|qb| {
qb.push(quote_ident::<sqlx::Sqlite>(FieldName::new("title")));
},
true,
None,
)
.order_by_with(
|qb| {
qb.push(quote_ident::<sqlx::Sqlite>(FieldName::new("id")));
},
false,
None,
)
.finalize()
.into_string();
assert_eq!(named, "SELECT * FROM t ORDER BY \"title\" DESC, \"id\" ASC");
assert_eq!(named, written);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_statement_with_no_predicate_can_still_be_ordered() {
let sql = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"t\"")
.order_by(FieldName::new("id"), false)
.limit(10)
.finalize()
.into_string();
assert_eq!(sql, "SELECT \"id\" FROM \"t\" ORDER BY \"id\" ASC LIMIT ?");
}
#[cfg(feature = "sqlite")]
#[test]
fn an_unordered_statement_can_still_be_limited() {
let bare = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"t\"")
.limit(10)
.finalize()
.into_string();
assert_eq!(bare, "SELECT \"id\" FROM \"t\" LIMIT ?");
let mut qb = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"t\"")
.begin_where();
{
SqlConjunction::new(qb.inner_mut())
.open()
.push("\"a\" = ")
.push_bind(1i64);
}
assert_eq!(
qb.limit(10).finalize().into_string(),
"SELECT \"id\" FROM \"t\" WHERE \"a\" = ? LIMIT ?"
);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_sub_select_nests_a_walk_and_gives_the_accumulator_back() {
let mut qb = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"parent\"")
.begin_where();
{
let mut inner = SqlConjunction::new(qb.inner_mut())
.sub_select(FieldName::new("child_id"))
.push_field("\"id\"")
.from("\"child\"")
.begin_where();
{
SqlConjunction::new(inner.inner_mut())
.open()
.push("\"kind\" = ")
.push_bind(7i64);
}
let accumulator = inner.finalize();
assert_eq!(accumulator.written(), 1);
let accumulator = accumulator
.sub_select(FieldName::new("tag_id"))
.push_field("\"id\"")
.from("\"tag\"")
.finalize();
assert_eq!(accumulator.written(), 2);
}
let sql = qb.finalize().into_string();
assert_eq!(
sql,
"SELECT \"id\" FROM \"parent\" WHERE \"child_id\" IN ( SELECT \"id\" FROM \"child\" \
WHERE \"kind\" = ? ) AND \"tag_id\" IN ( SELECT \"id\" FROM \"tag\" )"
);
assert_eq!(sql.matches("WHERE").count(), 2);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_sub_select_joins_a_clause_a_plain_conjunct_opened() {
let mut qb = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"parent\"")
.begin_where();
{
let mut accumulator = SqlConjunction::new(qb.inner_mut());
accumulator.open().push("\"z\" = ").push_bind(1i64);
accumulator
.sub_select(FieldName::new("child_id"))
.push_field("\"id\"")
.from("\"child\"")
.finalize();
}
assert_eq!(
qb.finalize().into_string(),
"SELECT \"id\" FROM \"parent\" WHERE \"z\" = ? AND \"child_id\" IN \
( SELECT \"id\" FROM \"child\" )"
);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_sub_select_takes_a_written_subject_where_a_name_cannot_reach() {
use crate::{At, RecordAddress, comparison::sql::push_column};
const PARENT: RecordAddress =
RecordAddress::new(FieldName::new("parent"), &[FieldName::new("id")]);
fn rendered(at: At<'_>) -> String {
let mut qb = sqlx::QueryBuilder::<sqlx::Sqlite>::default();
SqlConjunction::with_lead(&mut qb, "")
.sub_select_with(|qb| push_column(qb, at))
.push_field("\"id\"")
.from("\"child\"")
.finalize();
qb.into_string()
}
let shallow = At::new(&PARENT).frame(FieldName::new("child_id"));
let cover = At::new(&PARENT).frame(FieldName::new("cover"));
let deep = cover.at().frame(FieldName::new("child_id"));
assert_eq!(
rendered(shallow.at()),
"\"child_id\" IN ( SELECT \"id\" FROM \"child\" )"
);
assert_eq!(
rendered(deep.at()),
"\"cover_child_id\" IN ( SELECT \"id\" FROM \"child\" )"
);
let mut named = sqlx::QueryBuilder::<sqlx::Sqlite>::default();
SqlConjunction::with_lead(&mut named, "")
.sub_select(FieldName::new("child_id"))
.push_field("\"id\"")
.from("\"child\"")
.finalize();
assert_eq!(named.into_string(), rendered(shallow.at()));
}
#[cfg(feature = "sqlite")]
#[test]
fn a_nested_statement_binds_in_render_order() {
use crate::{Carrier, comparison::sql::tests::Probe};
let bound = core::cell::RefCell::new(Vec::new());
let mut qb = QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field("\"id\"")
.from("\"parent\"")
.begin_where();
{
let mut accumulator = SqlConjunction::new(qb.inner_mut());
{
let slot = accumulator.open();
slot.push("\"z\" = ");
Probe {
value: 1,
bound: &bound,
}
.push_bind(slot);
}
let mut inner = accumulator
.sub_select(FieldName::new("child_id"))
.push_field("\"id\"")
.from("\"child\"")
.begin_where();
{
let mut nested = SqlConjunction::new(inner.inner_mut());
let slot = nested.open();
slot.push("\"kind\" = ");
Probe {
value: 2,
bound: &bound,
}
.push_bind(slot);
}
let mut accumulator = inner.finalize();
{
let slot = accumulator.open();
slot.push("\"y\" = ");
Probe {
value: 3,
bound: &bound,
}
.push_bind(slot);
}
}
assert_eq!(
qb.finalize().into_string(),
"SELECT \"id\" FROM \"parent\" WHERE \"z\" = ? AND \"child_id\" IN \
( SELECT \"id\" FROM \"child\" WHERE \"kind\" = ? ) AND \"y\" = ?"
);
assert_eq!(*bound.borrow(), [1, 2, 3]);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_conjunct_can_host_a_hand_bracketed_nested_statement() {
let mut qb = sqlx::QueryBuilder::<sqlx::Sqlite>::default();
{
let mut accumulator = SqlConjunction::with_lead(&mut qb, "");
{
let slot = accumulator.open();
slot.push("\"child_id\" IN ( ");
let mut nested = QueryBuilder::<SelectClause, sqlx::Sqlite, _>::select_into(&mut *slot)
.push_field("\"id\"")
.from("\"child\"")
.begin_where();
{
SqlConjunction::new(nested.inner_mut())
.open()
.push("\"kind\" = ")
.push_bind(7i64);
}
slot.push(" )");
}
accumulator.open().push("\"z\" = ").push_bind(1i64);
}
assert_eq!(
qb.into_string(),
"\"child_id\" IN ( SELECT \"id\" FROM \"child\" WHERE \"kind\" = ? ) AND \"z\" = ?"
);
}
fn ordered_and_nested<Db: sqlx::Database + QuoteIdent>(column: &str) -> String {
let mut qb = QueryBuilder::<SelectClause, Db>::select()
.push_field("*")
.from("t")
.begin_where();
{
SqlConjunction::new(qb.inner_mut())
.sub_select(FieldName::new(column))
.push_field("*")
.from("u")
.finalize();
}
qb.order_by(FieldName::new(column), true)
.finalize()
.into_string()
}
#[cfg(feature = "sqlite")]
#[test]
fn sqlite_brackets_an_ordering_term_and_a_subquery_field() {
assert_eq!(
ordered_and_nested::<sqlx::Sqlite>("n"),
"SELECT * FROM t WHERE \"n\" IN ( SELECT * FROM u ) ORDER BY \"n\" DESC"
);
assert_eq!(
ordered_and_nested::<sqlx::Sqlite>("n\" = 1 OR \"x"),
"SELECT * FROM t WHERE \"n\"\" = 1 OR \"\"x\" IN ( SELECT * FROM u ) \
ORDER BY \"n\"\" = 1 OR \"\"x\" DESC"
);
}
#[cfg(feature = "postgres")]
#[test]
fn postgres_brackets_an_ordering_term_and_a_subquery_field() {
assert_eq!(
ordered_and_nested::<sqlx::Postgres>("a\"b"),
"SELECT * FROM t WHERE \"a\"\"b\" IN ( SELECT * FROM u ) ORDER BY \"a\"\"b\" DESC"
);
}
#[cfg(feature = "mysql")]
#[test]
fn mysql_brackets_an_ordering_term_and_a_subquery_field() {
assert_eq!(
ordered_and_nested::<sqlx::MySql>("a`b"),
"SELECT * FROM t WHERE `a``b` IN ( SELECT * FROM u ) ORDER BY `a``b` DESC"
);
assert_eq!(
ordered_and_nested::<sqlx::MySql>("a\"b"),
"SELECT * FROM t WHERE `a\"b` IN ( SELECT * FROM u ) ORDER BY `a\"b` DESC"
);
}
fn generated_order<Db: sqlx::Database + QuoteIdent>() -> String {
QueryBuilder::<SelectClause, Db>::select()
.push_field("*")
.from("t")
.order_by(FieldName::new("title"), true)
.order_by(FieldName::new("reel_id"), false)
.order_by(FieldName::new("id"), false)
.finalize()
.into_string()
}
#[cfg(feature = "sqlite")]
#[test]
fn sqlite_renders_a_term_and_then_the_key() {
assert_eq!(
generated_order::<sqlx::Sqlite>(),
"SELECT * FROM t ORDER BY \"title\" DESC, \"reel_id\" ASC, \"id\" ASC"
);
}
#[cfg(feature = "postgres")]
#[test]
fn postgres_renders_a_term_and_then_the_key() {
assert_eq!(
generated_order::<sqlx::Postgres>(),
"SELECT * FROM t ORDER BY \"title\" DESC, \"reel_id\" ASC, \"id\" ASC"
);
}
#[cfg(feature = "mysql")]
#[test]
fn mysql_renders_a_term_and_then_the_key() {
assert_eq!(
generated_order::<sqlx::MySql>(),
"SELECT * FROM t ORDER BY `title` DESC, `reel_id` ASC, `id` ASC"
);
}