1use std::collections::HashSet;
37
38use wabot_core::repository::query::{Direction, Query, QueryOperator};
39
40#[derive(Debug, Clone)]
42pub enum FieldPlacement {
43 Promoted(HashSet<String>),
46 All,
49}
50
51impl FieldPlacement {
52 pub fn promoted<S: Into<String>>(fields: impl IntoIterator<Item = S>) -> Self {
53 Self::Promoted(fields.into_iter().map(Into::into).collect())
54 }
55
56 pub fn none() -> Self {
57 Self::Promoted(HashSet::new())
58 }
59
60 fn is_column(&self, field: &str) -> bool {
61 matches!(field, "id" | "created_at" | "createdAt")
62 || match self {
63 FieldPlacement::All => true,
64 FieldPlacement::Promoted(fields) => fields.contains(field),
65 }
66 }
67}
68
69#[derive(Debug, thiserror::Error, PartialEq)]
70pub enum QuerySqlError {
71 #[error("unknown field {field:?}: the columnar store has no such column")]
74 UnknownField { field: String },
75 #[error("{operator:?} on {field:?} needs a value")]
76 MissingValue { field: String, operator: String },
77 #[error("{operator:?} on {field:?} needs an array of values")]
79 NotAList { field: String, operator: String },
80}
81
82#[derive(Debug, Clone, PartialEq)]
85pub struct BuiltQuery {
86 pub sql: String,
89 pub placeholders: usize,
92}
93
94fn column_for(field: &str) -> &str {
97 match field {
98 "createdAt" => "created_at",
99 other => other,
100 }
101}
102
103fn quote_ident(name: &str) -> String {
104 format!("\"{}\"", name.replace('"', "\"\""))
105}
106
107fn json_path(field: &str) -> String {
114 let label = field.replace('\\', "\\\\").replace('"', "\\\"");
115 format!("'$.\"{}\"'", label.replace('\'', "''"))
116}
117
118pub fn field_expression(field: &str, placement: &FieldPlacement) -> String {
124 if placement.is_column(field) {
125 quote_ident(column_for(field))
126 } else {
127 format!("\"data\"->>{}", json_path(field))
131 }
132}
133
134pub fn bind_values(query: &Query) -> Result<Vec<serde_json::Value>, QuerySqlError> {
141 let mut values = Vec::with_capacity(query.conditions.len());
142 for condition in &query.conditions {
143 if !condition.operator.takes_value() {
144 continue;
145 }
146 let Some(value) = &condition.value else {
147 return Err(QuerySqlError::MissingValue {
148 field: condition.field.clone(),
149 operator: format!("{:?}", condition.operator),
150 });
151 };
152 match condition.operator {
153 QueryOperator::In | QueryOperator::NotIn => match value {
154 serde_json::Value::Array(items) => values.extend(items.iter().cloned()),
155 _ => {
156 return Err(QuerySqlError::NotAList {
157 field: condition.field.clone(),
158 operator: format!("{:?}", condition.operator),
159 })
160 }
161 },
162 _ => values.push(value.clone()),
163 }
164 }
165 Ok(values)
166}
167
168pub fn build_query_sql(
175 query: &Query,
176 placement: &FieldPlacement,
177 columns: Option<&HashSet<String>>,
178) -> Result<BuiltQuery, QuerySqlError> {
179 let mut sql = String::new();
180 let mut placeholders = 0usize;
181
182 let known = |field: &str| -> Result<(), QuerySqlError> {
183 match columns {
184 Some(columns)
185 if !columns.contains(field)
186 && !matches!(field, "id" | "created_at" | "createdAt") =>
187 {
188 Err(QuerySqlError::UnknownField {
189 field: field.to_string(),
190 })
191 }
192 _ => Ok(()),
193 }
194 };
195
196 if !query.conditions.is_empty() {
197 let mut rendered = Vec::with_capacity(query.conditions.len());
198 for condition in &query.conditions {
199 known(&condition.field)?;
200 let lhs = field_expression(&condition.field, placement);
201
202 if condition.operator.takes_value() && condition.value.is_none() {
203 return Err(QuerySqlError::MissingValue {
204 field: condition.field.clone(),
205 operator: format!("{:?}", condition.operator),
206 });
207 }
208
209 let mut slot = || {
210 placeholders += 1;
211 format!("?{placeholders}")
212 };
213
214 rendered.push(match condition.operator {
215 QueryOperator::IsNull => format!("{lhs} IS NULL"),
216 QueryOperator::IsNotNull => format!("{lhs} IS NOT NULL"),
217 QueryOperator::Equals => format!("{lhs} = {}", slot()),
218 QueryOperator::Not => format!("{lhs} <> {}", slot()),
219 QueryOperator::Gt => format!("{lhs} > {}", slot()),
220 QueryOperator::Gte => format!("{lhs} >= {}", slot()),
221 QueryOperator::Lt => format!("{lhs} < {}", slot()),
222 QueryOperator::Lte => format!("{lhs} <= {}", slot()),
223 QueryOperator::Like => format!("{lhs} LIKE {}", slot()),
224 QueryOperator::NotLike => format!("{lhs} NOT LIKE {}", slot()),
225 QueryOperator::In | QueryOperator::NotIn => {
226 let list = expand_in(condition, &mut placeholders)?;
227 let negate = matches!(condition.operator, QueryOperator::NotIn);
228 match (negate, list.is_empty()) {
229 (false, true) => "0 = 1".to_string(),
232 (true, true) => "1 = 1".to_string(),
233 (false, false) => format!("{lhs} IN ({})", list.join(", ")),
234 (true, false) => format!("{lhs} NOT IN ({})", list.join(", ")),
235 }
236 }
237 });
238 }
239 sql.push_str(" WHERE ");
240 sql.push_str(&rendered.join(" AND "));
241 }
242
243 if !query.order.is_empty() {
244 let mut parts = Vec::with_capacity(query.order.len());
245 for (field, direction) in &query.order {
246 known(field)?;
247 parts.push(format!(
248 "{} {}",
249 field_expression(field, placement),
250 match direction {
251 Direction::Asc => "ASC",
252 Direction::Desc => "DESC",
253 }
254 ));
255 }
256 sql.push_str(" ORDER BY ");
257 sql.push_str(&parts.join(", "));
258 }
259
260 if let Some(limit) = query.limit {
264 sql.push_str(&format!(" LIMIT {limit}"));
265 }
266 if let Some(offset) = query.offset {
267 if query.limit.is_none() {
270 sql.push_str(" LIMIT -1");
271 }
272 sql.push_str(&format!(" OFFSET {offset}"));
273 }
274
275 Ok(BuiltQuery { sql, placeholders })
276}
277
278fn expand_in(
279 condition: &wabot_core::repository::query::Condition,
280 placeholders: &mut usize,
281) -> Result<Vec<String>, QuerySqlError> {
282 let Some(serde_json::Value::Array(items)) = &condition.value else {
283 return Err(QuerySqlError::NotAList {
284 field: condition.field.clone(),
285 operator: format!("{:?}", condition.operator),
286 });
287 };
288 Ok(items
289 .iter()
290 .map(|_| {
291 *placeholders += 1;
292 format!("?{placeholders}")
293 })
294 .collect())
295}
296
297#[cfg(test)]
312mod tests {
313 use super::*;
314 use serde_json::json;
315
316 fn columnar() -> FieldPlacement {
317 FieldPlacement::All
318 }
319
320 fn built(query: &Query, placement: &FieldPlacement) -> String {
321 build_query_sql(query, placement, None).unwrap().sql
322 }
323
324 #[test]
325 fn an_empty_query_adds_nothing() {
326 assert_eq!(built(&Query::new(), &columnar()), "");
327 }
328
329 #[test]
330 fn conditions_become_and_ed_placeholders() {
331 let sql = built(
332 &Query::new().eq("owner_id", "u-1").gt("total", 100),
333 &columnar(),
334 );
335 assert_eq!(sql, " WHERE \"owner_id\" = ?1 AND \"total\" > ?2");
336 }
337
338 #[test]
339 fn every_operator_renders() {
340 let query = Query::new()
341 .eq("a", 1)
342 .not("b", 1)
343 .gt("c", 1)
344 .gte("d", 1)
345 .lt("e", 1)
346 .lte("f", 1)
347 .like("g", "x%")
348 .not_like("h", "x%")
349 .is_in("i", json!([1]))
350 .not_in("j", json!([1]))
351 .is_null("k")
352 .is_not_null("l");
353
354 let built = build_query_sql(&query, &columnar(), None).unwrap();
355 assert!(built.sql.contains("\"a\" = ?1"));
356 assert!(built.sql.contains("\"b\" <> ?2"));
357 assert!(built.sql.contains("\"g\" LIKE ?7"));
358 assert!(built.sql.contains("\"i\" IN (?9)"));
359 assert!(built.sql.contains("\"j\" NOT IN (?10)"));
360 assert!(built.sql.contains("\"k\" IS NULL"));
361 assert!(built.sql.contains("\"l\" IS NOT NULL"));
362 assert_eq!(built.placeholders, 10);
363 assert_eq!(
364 built.placeholders,
365 bind_values(&query).unwrap().len(),
366 "placeholders and bind values must agree, or every value lands on the wrong condition"
367 );
368 }
369
370 #[test]
374 fn membership_expands_to_one_placeholder_per_element() {
375 let query = Query::new()
376 .eq("before", "x")
377 .is_in("status", json!(["open", "paid", "void"]))
378 .eq("after", "y");
379
380 let built = build_query_sql(&query, &columnar(), None).unwrap();
381 assert_eq!(
382 built.sql,
383 " WHERE \"before\" = ?1 AND \"status\" IN (?2, ?3, ?4) AND \"after\" = ?5"
384 );
385 assert_eq!(built.placeholders, 5);
386
387 let values = bind_values(&query).unwrap();
388 assert_eq!(values.len(), 5, "the array was flattened");
389 assert_eq!(
390 values[4],
391 json!("y"),
392 "the value after the list still lines up"
393 );
394 }
395
396 #[test]
399 fn an_empty_membership_list_is_a_constant() {
400 let sql = built(&Query::new().is_in("s", json!([])), &columnar());
401 assert_eq!(sql, " WHERE 0 = 1");
402
403 let sql = built(&Query::new().not_in("s", json!([])), &columnar());
404 assert_eq!(sql, " WHERE 1 = 1");
405 }
406
407 #[test]
410 fn a_json_field_needs_no_cast() {
411 let placement = FieldPlacement::promoted(["owner_id"]);
412
413 assert_eq!(
414 built(&Query::new().gt("total", 100), &placement),
415 " WHERE \"data\"->>'$.\"total\"' > ?1"
416 );
417 assert_eq!(
418 built(&Query::new().gt("owner_id", "a"), &placement),
419 " WHERE \"owner_id\" > ?1",
420 "a promoted column is already typed"
421 );
422 }
423
424 #[test]
425 fn the_reserved_columns_are_always_native() {
426 let placement = FieldPlacement::none();
427 assert_eq!(
428 built(&Query::new().gt("createdAt", 1).eq("id", "x"), &placement),
429 " WHERE \"created_at\" > ?1 AND \"id\" = ?2",
430 "`createdAt` is the field name; `created_at` is the column"
431 );
432 }
433
434 #[test]
435 fn ordering_limit_and_offset_are_rendered() {
436 let sql = built(
437 &Query::new()
438 .order_desc("created_at")
439 .order_asc("id")
440 .limit(20)
441 .offset(40),
442 &columnar(),
443 );
444 assert_eq!(
445 sql,
446 " ORDER BY \"created_at\" DESC, \"id\" ASC LIMIT 20 OFFSET 40"
447 );
448 }
449
450 #[test]
453 fn an_offset_without_a_limit_still_parses() {
454 let sql = built(&Query::new().offset(10), &columnar());
455 assert_eq!(sql, " LIMIT -1 OFFSET 10");
456 }
457
458 #[test]
461 fn an_unknown_field_is_refused_when_the_columns_are_known() {
462 let columns: HashSet<String> = ["owner_id".to_string()].into_iter().collect();
463
464 assert!(build_query_sql(
465 &Query::new().eq("owner_id", "u-1"),
466 &columnar(),
467 Some(&columns)
468 )
469 .is_ok());
470
471 let error = build_query_sql(
472 &Query::new().eq("total; DROP TABLE orders", 1),
473 &columnar(),
474 Some(&columns),
475 )
476 .unwrap_err();
477 assert!(
478 matches!(error, QuerySqlError::UnknownField { .. }),
479 "{error}"
480 );
481
482 let error = build_query_sql(&Query::new().order_asc("nope"), &columnar(), Some(&columns))
483 .unwrap_err();
484 assert!(
485 matches!(error, QuerySqlError::UnknownField { .. }),
486 "ordering too"
487 );
488 }
489
490 #[test]
495 fn identifiers_and_json_labels_are_escaped() {
496 assert_eq!(
497 built(&Query::new().eq("we\"ird", 1), &columnar()),
498 " WHERE \"we\"\"ird\" = ?1"
499 );
500 assert_eq!(
501 built(&Query::new().eq("we'ird", 1), &FieldPlacement::none()),
502 " WHERE \"data\"->>'$.\"we''ird\"' = ?1"
503 );
504 assert_eq!(
505 built(&Query::new().eq("we\"ird", 1), &FieldPlacement::none()),
506 " WHERE \"data\"->>'$.\"we\\\"ird\"' = ?1"
507 );
508 }
509
510 #[test]
511 fn a_value_less_comparison_is_an_error_not_a_broken_statement() {
512 let query = Query::new().condition(wabot_core::repository::query::Condition {
513 field: "total".into(),
514 operator: QueryOperator::Gt,
515 value: None,
516 });
517 assert!(matches!(
518 build_query_sql(&query, &columnar(), None),
519 Err(QuerySqlError::MissingValue { .. })
520 ));
521 }
522
523 #[test]
524 fn membership_against_a_non_list_is_an_error() {
525 let query = Query::new().is_in("s", json!("not-a-list"));
526 assert!(matches!(
527 build_query_sql(&query, &columnar(), None),
528 Err(QuerySqlError::NotAList { .. })
529 ));
530 assert!(matches!(
531 bind_values(&query),
532 Err(QuerySqlError::NotAList { .. })
533 ));
534 }
535}