use std::collections::HashSet;
use wabot_core::repository::query::{Direction, Query, QueryOperator};
#[derive(Debug, Clone)]
pub enum FieldPlacement {
Promoted(HashSet<String>),
All,
}
impl FieldPlacement {
pub fn promoted<S: Into<String>>(fields: impl IntoIterator<Item = S>) -> Self {
Self::Promoted(fields.into_iter().map(Into::into).collect())
}
pub fn none() -> Self {
Self::Promoted(HashSet::new())
}
fn is_column(&self, field: &str) -> bool {
matches!(field, "id" | "created_at" | "createdAt")
|| match self {
FieldPlacement::All => true,
FieldPlacement::Promoted(fields) => fields.contains(field),
}
}
}
#[derive(Debug, thiserror::Error, PartialEq)]
pub enum QuerySqlError {
#[error("unknown field {field:?}: the columnar store has no such column")]
UnknownField { field: String },
#[error("{operator:?} on {field:?} needs a value")]
MissingValue { field: String, operator: String },
#[error("{operator:?} on {field:?} needs an array of values")]
NotAList { field: String, operator: String },
}
#[derive(Debug, Clone, PartialEq)]
pub struct BuiltQuery {
pub sql: String,
pub placeholders: usize,
}
fn column_for(field: &str) -> &str {
match field {
"createdAt" => "created_at",
other => other,
}
}
fn quote_ident(name: &str) -> String {
format!("\"{}\"", name.replace('"', "\"\""))
}
fn json_path(field: &str) -> String {
let label = field.replace('\\', "\\\\").replace('"', "\\\"");
format!("'$.\"{}\"'", label.replace('\'', "''"))
}
pub fn field_expression(field: &str, placement: &FieldPlacement) -> String {
if placement.is_column(field) {
quote_ident(column_for(field))
} else {
format!("\"data\"->>{}", json_path(field))
}
}
pub fn bind_values(query: &Query) -> Result<Vec<serde_json::Value>, QuerySqlError> {
let mut values = Vec::with_capacity(query.conditions.len());
for condition in &query.conditions {
if !condition.operator.takes_value() {
continue;
}
let Some(value) = &condition.value else {
return Err(QuerySqlError::MissingValue {
field: condition.field.clone(),
operator: format!("{:?}", condition.operator),
});
};
match condition.operator {
QueryOperator::In | QueryOperator::NotIn => match value {
serde_json::Value::Array(items) => values.extend(items.iter().cloned()),
_ => {
return Err(QuerySqlError::NotAList {
field: condition.field.clone(),
operator: format!("{:?}", condition.operator),
})
}
},
_ => values.push(value.clone()),
}
}
Ok(values)
}
pub fn build_query_sql(
query: &Query,
placement: &FieldPlacement,
columns: Option<&HashSet<String>>,
) -> Result<BuiltQuery, QuerySqlError> {
let mut sql = String::new();
let mut placeholders = 0usize;
let known = |field: &str| -> Result<(), QuerySqlError> {
match columns {
Some(columns)
if !columns.contains(field)
&& !matches!(field, "id" | "created_at" | "createdAt") =>
{
Err(QuerySqlError::UnknownField {
field: field.to_string(),
})
}
_ => Ok(()),
}
};
if !query.conditions.is_empty() {
let mut rendered = Vec::with_capacity(query.conditions.len());
for condition in &query.conditions {
known(&condition.field)?;
let lhs = field_expression(&condition.field, placement);
if condition.operator.takes_value() && condition.value.is_none() {
return Err(QuerySqlError::MissingValue {
field: condition.field.clone(),
operator: format!("{:?}", condition.operator),
});
}
let mut slot = || {
placeholders += 1;
format!("?{placeholders}")
};
rendered.push(match condition.operator {
QueryOperator::IsNull => format!("{lhs} IS NULL"),
QueryOperator::IsNotNull => format!("{lhs} IS NOT NULL"),
QueryOperator::Equals => format!("{lhs} = {}", slot()),
QueryOperator::Not => format!("{lhs} <> {}", slot()),
QueryOperator::Gt => format!("{lhs} > {}", slot()),
QueryOperator::Gte => format!("{lhs} >= {}", slot()),
QueryOperator::Lt => format!("{lhs} < {}", slot()),
QueryOperator::Lte => format!("{lhs} <= {}", slot()),
QueryOperator::Like => format!("{lhs} LIKE {}", slot()),
QueryOperator::NotLike => format!("{lhs} NOT LIKE {}", slot()),
QueryOperator::In | QueryOperator::NotIn => {
let list = expand_in(condition, &mut placeholders)?;
let negate = matches!(condition.operator, QueryOperator::NotIn);
match (negate, list.is_empty()) {
(false, true) => "0 = 1".to_string(),
(true, true) => "1 = 1".to_string(),
(false, false) => format!("{lhs} IN ({})", list.join(", ")),
(true, false) => format!("{lhs} NOT IN ({})", list.join(", ")),
}
}
});
}
sql.push_str(" WHERE ");
sql.push_str(&rendered.join(" AND "));
}
if !query.order.is_empty() {
let mut parts = Vec::with_capacity(query.order.len());
for (field, direction) in &query.order {
known(field)?;
parts.push(format!(
"{} {}",
field_expression(field, placement),
match direction {
Direction::Asc => "ASC",
Direction::Desc => "DESC",
}
));
}
sql.push_str(" ORDER BY ");
sql.push_str(&parts.join(", "));
}
if let Some(limit) = query.limit {
sql.push_str(&format!(" LIMIT {limit}"));
}
if let Some(offset) = query.offset {
if query.limit.is_none() {
sql.push_str(" LIMIT -1");
}
sql.push_str(&format!(" OFFSET {offset}"));
}
Ok(BuiltQuery { sql, placeholders })
}
fn expand_in(
condition: &wabot_core::repository::query::Condition,
placeholders: &mut usize,
) -> Result<Vec<String>, QuerySqlError> {
let Some(serde_json::Value::Array(items)) = &condition.value else {
return Err(QuerySqlError::NotAList {
field: condition.field.clone(),
operator: format!("{:?}", condition.operator),
});
};
Ok(items
.iter()
.map(|_| {
*placeholders += 1;
format!("?{placeholders}")
})
.collect())
}
#[cfg(test)]
mod tests {
use super::*;
use serde_json::json;
fn columnar() -> FieldPlacement {
FieldPlacement::All
}
fn built(query: &Query, placement: &FieldPlacement) -> String {
build_query_sql(query, placement, None).unwrap().sql
}
#[test]
fn an_empty_query_adds_nothing() {
assert_eq!(built(&Query::new(), &columnar()), "");
}
#[test]
fn conditions_become_and_ed_placeholders() {
let sql = built(
&Query::new().eq("owner_id", "u-1").gt("total", 100),
&columnar(),
);
assert_eq!(sql, " WHERE \"owner_id\" = ?1 AND \"total\" > ?2");
}
#[test]
fn every_operator_renders() {
let query = Query::new()
.eq("a", 1)
.not("b", 1)
.gt("c", 1)
.gte("d", 1)
.lt("e", 1)
.lte("f", 1)
.like("g", "x%")
.not_like("h", "x%")
.is_in("i", json!([1]))
.not_in("j", json!([1]))
.is_null("k")
.is_not_null("l");
let built = build_query_sql(&query, &columnar(), None).unwrap();
assert!(built.sql.contains("\"a\" = ?1"));
assert!(built.sql.contains("\"b\" <> ?2"));
assert!(built.sql.contains("\"g\" LIKE ?7"));
assert!(built.sql.contains("\"i\" IN (?9)"));
assert!(built.sql.contains("\"j\" NOT IN (?10)"));
assert!(built.sql.contains("\"k\" IS NULL"));
assert!(built.sql.contains("\"l\" IS NOT NULL"));
assert_eq!(built.placeholders, 10);
assert_eq!(
built.placeholders,
bind_values(&query).unwrap().len(),
"placeholders and bind values must agree, or every value lands on the wrong condition"
);
}
#[test]
fn membership_expands_to_one_placeholder_per_element() {
let query = Query::new()
.eq("before", "x")
.is_in("status", json!(["open", "paid", "void"]))
.eq("after", "y");
let built = build_query_sql(&query, &columnar(), None).unwrap();
assert_eq!(
built.sql,
" WHERE \"before\" = ?1 AND \"status\" IN (?2, ?3, ?4) AND \"after\" = ?5"
);
assert_eq!(built.placeholders, 5);
let values = bind_values(&query).unwrap();
assert_eq!(values.len(), 5, "the array was flattened");
assert_eq!(
values[4],
json!("y"),
"the value after the list still lines up"
);
}
#[test]
fn an_empty_membership_list_is_a_constant() {
let sql = built(&Query::new().is_in("s", json!([])), &columnar());
assert_eq!(sql, " WHERE 0 = 1");
let sql = built(&Query::new().not_in("s", json!([])), &columnar());
assert_eq!(sql, " WHERE 1 = 1");
}
#[test]
fn a_json_field_needs_no_cast() {
let placement = FieldPlacement::promoted(["owner_id"]);
assert_eq!(
built(&Query::new().gt("total", 100), &placement),
" WHERE \"data\"->>'$.\"total\"' > ?1"
);
assert_eq!(
built(&Query::new().gt("owner_id", "a"), &placement),
" WHERE \"owner_id\" > ?1",
"a promoted column is already typed"
);
}
#[test]
fn the_reserved_columns_are_always_native() {
let placement = FieldPlacement::none();
assert_eq!(
built(&Query::new().gt("createdAt", 1).eq("id", "x"), &placement),
" WHERE \"created_at\" > ?1 AND \"id\" = ?2",
"`createdAt` is the field name; `created_at` is the column"
);
}
#[test]
fn ordering_limit_and_offset_are_rendered() {
let sql = built(
&Query::new()
.order_desc("created_at")
.order_asc("id")
.limit(20)
.offset(40),
&columnar(),
);
assert_eq!(
sql,
" ORDER BY \"created_at\" DESC, \"id\" ASC LIMIT 20 OFFSET 40"
);
}
#[test]
fn an_offset_without_a_limit_still_parses() {
let sql = built(&Query::new().offset(10), &columnar());
assert_eq!(sql, " LIMIT -1 OFFSET 10");
}
#[test]
fn an_unknown_field_is_refused_when_the_columns_are_known() {
let columns: HashSet<String> = ["owner_id".to_string()].into_iter().collect();
assert!(build_query_sql(
&Query::new().eq("owner_id", "u-1"),
&columnar(),
Some(&columns)
)
.is_ok());
let error = build_query_sql(
&Query::new().eq("total; DROP TABLE orders", 1),
&columnar(),
Some(&columns),
)
.unwrap_err();
assert!(
matches!(error, QuerySqlError::UnknownField { .. }),
"{error}"
);
let error = build_query_sql(&Query::new().order_asc("nope"), &columnar(), Some(&columns))
.unwrap_err();
assert!(
matches!(error, QuerySqlError::UnknownField { .. }),
"ordering too"
);
}
#[test]
fn identifiers_and_json_labels_are_escaped() {
assert_eq!(
built(&Query::new().eq("we\"ird", 1), &columnar()),
" WHERE \"we\"\"ird\" = ?1"
);
assert_eq!(
built(&Query::new().eq("we'ird", 1), &FieldPlacement::none()),
" WHERE \"data\"->>'$.\"we''ird\"' = ?1"
);
assert_eq!(
built(&Query::new().eq("we\"ird", 1), &FieldPlacement::none()),
" WHERE \"data\"->>'$.\"we\\\"ird\"' = ?1"
);
}
#[test]
fn a_value_less_comparison_is_an_error_not_a_broken_statement() {
let query = Query::new().condition(wabot_core::repository::query::Condition {
field: "total".into(),
operator: QueryOperator::Gt,
value: None,
});
assert!(matches!(
build_query_sql(&query, &columnar(), None),
Err(QuerySqlError::MissingValue { .. })
));
}
#[test]
fn membership_against_a_non_list_is_an_error() {
let query = Query::new().is_in("s", json!("not-a-list"));
assert!(matches!(
build_query_sql(&query, &columnar(), None),
Err(QuerySqlError::NotAList { .. })
));
assert!(matches!(
bind_values(&query),
Err(QuerySqlError::NotAList { .. })
));
}
}