#![cfg(feature = "sqlite")]
use ormer::{DbType, RowValueCompare, Select};
#[derive(ormer::Model)]
#[table = "expr_ast_users_1"]
struct ExprUser {
#[primary]
id: i32,
org_id: i32,
email: String,
status: String,
score: i32,
active: bool,
profile: String,
}
#[test]
fn scalar_expression_sql_is_shared_by_projection_filter_and_order() {
let (sql, params) = Select::<ExprUser>::new()
.filter(|u| u.email.to_lower().eq("alice@example.com"))
.map_to(|u| {
(
u.id.cast::<String>(),
ormer::expr!(match u.status {
"paid" => "done",
"new" => "open",
_ => "other",
}),
u.email.collate("nocase"),
)
})
.order_by(|u| u.email.collate("nocase").asc())
.to_sql_with_params(DbType::Sqlite);
assert!(sql.contains("CAST(id AS TEXT)"));
assert!(sql.contains("CASE status WHEN ? THEN ? WHEN ? THEN ? ELSE ? END"));
assert!(sql.contains("email COLLATE nocase"));
assert!(sql.contains("LOWER(email) = ?"));
assert!(sql.contains("ORDER BY email COLLATE nocase ASC"));
assert_eq!(params.len(), 6);
}
#[test]
fn aliased_projection_sql_is_rendered_per_column() {
let (sql, params) = Select::<ExprUser>::new()
.map_to(|u| {
(
u.id.alias("user_id"),
u.email,
ormer::expr!(match u.status {
"paid" => "done",
"new" => "open",
_ => "other",
})
.alias("status_label"),
)
})
.to_sql_with_params(DbType::Sqlite);
assert!(sql.contains("id AS"));
assert!(sql.contains("user_id"));
assert!(sql.contains("CASE status WHEN ? THEN ? WHEN ? THEN ? ELSE ? END AS"));
assert!(sql.contains("status_label"));
assert_eq!(params.len(), 5);
}
#[test]
fn aggregate_window_and_grouping_sql_are_expression_nodes() {
let sql = Select::<ExprUser>::new()
.select_column(|u| (u.org_id, u.score.sum().filter(|u| u.active.eq(true))))
.group_by(|u| u.org_id)
.to_sql();
assert!(sql.contains("SELECT org_id, SUM(score) FILTER (WHERE active = ?)"));
assert!(sql.contains("GROUP BY org_id"));
let window_sql = Select::<ExprUser>::new()
.map_to(|u| (u.org_id, u.score.sum().over(|w| w.partition_by(u.org_id))))
.to_sql();
assert!(window_sql.contains("SUM(score) OVER (PARTITION BY org_id)"));
let rollup_sql = Select::<ExprUser>::new()
.select_column(|u| (u.org_id, u.score.sum()))
.rollup(|u| (u.org_id, u.status))
.to_sql();
assert!(rollup_sql.contains("GROUP BY ROLLUP (org_id, status)"));
let cube_sql = Select::<ExprUser>::new()
.select_column(|u| (u.org_id, u.score.sum()))
.cube(|u| (u.org_id, u.status))
.to_sql();
assert!(cube_sql.contains("GROUP BY CUBE (org_id, status)"));
let grouping_sets_sql = Select::<ExprUser>::new()
.select_column(|u| (u.org_id, u.score.sum()))
.grouping_sets(|u| ((u.org_id,), ()))
.to_sql();
assert!(grouping_sets_sql.contains("GROUP BY GROUPING SETS ((org_id), ())"));
}
#[test]
fn row_value_json_text_search_distinct_and_lock_sql() {
let (sql, params) = Select::<ExprUser>::new()
.distinct_on(|u| u.org_id)
.filter(|u| (u.org_id, u.email).eq((1, "a@example.com")))
.filter(|u| u.profile.json_text("role").eq("admin"))
.filter(|u| u.email.matches_text("rust"))
.for_update()
.skip_locked()
.range(..10)
.to_sql_with_params(DbType::Sqlite);
assert!(sql.contains("SELECT DISTINCT ON (org_id)"));
assert!(sql.contains("(org_id, email) = (?, ?)"));
assert!(sql.contains("json_extract(profile, '$.role') = ?"));
assert!(sql.contains("email MATCH ?"));
assert!(sql.contains("LIMIT 10 FOR UPDATE SKIP LOCKED"));
assert_eq!(params.len(), 4);
}