Something went wrong. Try again.
A fork of @slices.network/slices forked from slices.network/slices
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318//! SQL query building utilities for dynamic WHERE and ORDER BY clauses.//!//! This module provides helpers for constructing SQL queries dynamically//! based on user input while preventing SQL injection attacks.
use super::cursor::build_field_reference;use super::types::{SortField, WhereClause, WhereCondition};use crate::models::Record;
/// Builds an ORDER BY clause with optional datetime field information.////// # Arguments/// * `sort_by` - Optional array of fields to sort by/// * `field_is_datetime` - Optional boolean indicating if primary field is datetime////// # Returns/// SQL ORDER BY clause string (without "ORDER BY" prefix)pub fn build_order_by_clause_with_field_info( sort_by: Option<&Vec<SortField>>, field_is_datetime: Option<bool>,) -> String { match sort_by { Some(sort_fields) if !sort_fields.is_empty() => { let mut order_clauses = Vec::new(); for (index, sort_field) in sort_fields.iter().enumerate() { let field = &sort_field.field; let direction = match sort_field.direction.to_lowercase().as_str() { "desc" => "DESC", _ => "ASC", };
let is_primary = index == 0; let is_datetime = is_primary && field_is_datetime == Some(true);
if field .chars() .all(|c| c.is_alphanumeric() || c == '_' || c == '.') { let field_ref = build_field_reference(field, is_datetime);
if matches!(field.as_str(), "indexed_at" | "uri" | "cid" | "did" | "collection") { order_clauses.push(format!("{field_ref} {direction}")); } else { order_clauses.push(format!("{field_ref} {direction} NULLS LAST")); } } } if !order_clauses.is_empty() { let has_indexed_at = order_clauses .iter() .any(|clause| clause.contains("indexed_at")); if !has_indexed_at { order_clauses.push("indexed_at DESC".to_string()); } order_clauses.join(", ") } else { "indexed_at DESC".to_string() } } _ => "indexed_at DESC".to_string(), }}
/// Builds WHERE conditions from a WhereClause structure.////// Returns separate arrays for AND conditions and OR conditions/// to be combined in the final query.////// # Arguments/// * `where_clause` - Optional where clause with AND/OR conditions/// * `param_count` - Mutable counter for parameter numbering ($1, $2, etc)////// # Returns/// Tuple of (and_conditions, or_conditions) as SQL stringspub fn build_where_conditions( where_clause: Option<&WhereClause>, param_count: &mut usize,) -> (Vec<String>, Vec<String>) { let mut where_clauses = Vec::new(); let mut or_clauses = Vec::new();
if let Some(clause) = where_clause { for (field, condition) in &clause.conditions { let field_clause = build_single_condition(field, condition, param_count); where_clauses.push(field_clause); }
if let Some(or_conditions) = &clause.or_conditions { for (field, condition) in or_conditions { let field_clause = build_single_condition(field, condition, param_count); or_clauses.push(field_clause); } } }
(where_clauses, or_clauses)}
/// Builds a single SQL condition clause for a field.////// Supports equality (eq), array membership (in_values), and pattern matching (contains)/// for both table columns and JSON fields with nested paths.////// # Arguments/// * `field` - Field name (table column or JSON path)/// * `condition` - The condition to apply (eq, in_values, or contains)/// * `param_count` - Mutable counter for parameter numbering////// # Returns/// SQL condition string with parameter placeholderpub fn build_single_condition( field: &str, condition: &WhereCondition, param_count: &mut usize,) -> String { if let Some(_eq_value) = &condition.eq { let clause = match field { "did" | "collection" | "uri" | "cid" => { format!("{} = ${}", field, param_count) } _ => { let json_path = build_json_path(field); format!("{} = ${}", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_in_values) = &condition.in_values { let clause = match field { "did" | "collection" | "uri" | "cid" => { format!("{} = ANY(${})", field, param_count) } _ => { let json_path = build_json_path(field); format!("{} = ANY(${})", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_contains_value) = &condition.contains { let clause = if field == "json" { format!("json::text ILIKE '%' || ${} || '%'", param_count) } else { let json_path = build_json_path(field); format!("{} ILIKE '%' || ${} || '%'", json_path, param_count) }; *param_count += 1; clause } else { String::new() }}
/// Builds a PostgreSQL JSON path accessor string.////// Converts dot notation (e.g., "user.name") into PostgreSQL JSON operators/// (e.g., "json->'user'->>'name'").fn build_json_path(field: &str) -> String { if field.contains('.') { let parts: Vec<&str> = field.split('.').collect(); let mut path = String::from("json"); for (i, part) in parts.iter().enumerate() { if i == parts.len() - 1 { path.push_str(&format!("->>'{}'", part)); } else { path.push_str(&format!("->'{}'", part)); } } path } else { format!("json->>'{}'", field) }}
/// Binds WHERE clause parameters to a sqlx query.////// Iterates through all conditions and binds their values in the correct order.////// # Arguments/// * `query_builder` - The sqlx query to bind parameters to/// * `where_clause` - Optional where clause with parameter values////// # Returns/// Query builder with all parameters boundpub fn bind_where_parameters<'q>( mut query_builder: sqlx::query::QueryAs< 'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments, >, where_clause: Option<&'q WhereClause>,) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> { if let Some(clause) = where_clause { for condition in clause.conditions.values() { query_builder = bind_single_condition(query_builder, condition); }
if let Some(or_conditions) = &clause.or_conditions { for condition in or_conditions.values() { query_builder = bind_single_condition(query_builder, condition); } } } query_builder}
/// Binds parameters for a single condition to a sqlx query.////// Handles eq (single value), in_values (array), and contains (pattern) conditions.fn bind_single_condition<'q>( mut query_builder: sqlx::query::QueryAs< 'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments, >, condition: &'q WhereCondition,) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> { if let Some(eq_value) = &condition.eq { if let Some(str_val) = eq_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(eq_value); } }
if let Some(in_values) = &condition.in_values { let str_values: Vec<String> = in_values .iter() .filter_map(|v| v.as_str().map(|s| s.to_string())) .collect(); query_builder = query_builder.bind(str_values); }
if let Some(contains_value) = &condition.contains { query_builder = query_builder.bind(contains_value); }
query_builder}
#[cfg(test)]mod tests { use super::*;
#[test] fn test_build_order_by_clause_default() { let result = build_order_by_clause_with_field_info(None, None); assert_eq!(result, "indexed_at DESC"); }
#[test] fn test_build_order_by_clause_single_field() { let sort_by = vec![SortField { field: "createdAt".to_string(), direction: "desc".to_string(), }];
let result = build_order_by_clause_with_field_info(Some(&sort_by), None); assert_eq!(result, "json->>'createdAt' DESC NULLS LAST, indexed_at DESC"); }
#[test] fn test_build_order_by_clause_datetime_field() { let sort_by = vec![SortField { field: "createdAt".to_string(), direction: "desc".to_string(), }];
let result = build_order_by_clause_with_field_info(Some(&sort_by), Some(true)); assert_eq!(result, "(json->>'createdAt')::timestamp DESC NULLS LAST, indexed_at DESC"); }
#[test] fn test_build_order_by_clause_table_column() { let sort_by = vec![SortField { field: "indexed_at".to_string(), direction: "asc".to_string(), }];
let result = build_order_by_clause_with_field_info(Some(&sort_by), None); assert_eq!(result, "indexed_at ASC"); }
#[test] fn test_build_order_by_clause_nested_json() { let sort_by = vec![SortField { field: "author.name".to_string(), direction: "asc".to_string(), }];
let result = build_order_by_clause_with_field_info(Some(&sort_by), None); assert_eq!(result, "json->'author'->>'name' ASC NULLS LAST, indexed_at DESC"); }
#[test] fn test_build_order_by_clause_multiple_fields() { let sort_by = vec![ SortField { field: "text".to_string(), direction: "desc".to_string(), }, SortField { field: "createdAt".to_string(), direction: "asc".to_string(), }, ];
let result = build_order_by_clause_with_field_info(Some(&sort_by), None); assert_eq!( result, "json->>'text' DESC NULLS LAST, json->>'createdAt' ASC NULLS LAST, indexed_at DESC" ); }}