ravenrs_criteria/
sql.rs

1//! Translates criteria to SQL for the database to run, as object-relational
2//! mappers do. Values always travel as bound parameters and identifiers are
3//! always quoted, so data can never change the shape of the query. What SQL
4//! cannot express comes back as [`SqlError`]; evaluate that part in memory.
5
6use crate::ast::{BinaryOp, Criteria, GroupOp, UnaryOp};
7use crate::functions::{Aggregate, Function};
8use crate::value::Value;
9
10/// A database's SQL flavor.
11#[derive(Clone, Copy, Debug, PartialEq, Eq)]
12#[non_exhaustive]
13pub enum Dialect {
14    /// PostgreSQL: `"name"`, `$1`.
15    Postgres,
16    /// MySQL and MariaDB: `` `name` ``, `?`.
17    MySql,
18    /// Microsoft SQL Server: `[name]`, `@p1`.
19    SqlServer,
20    /// SQLite: `"name"`, `?1`.
21    Sqlite,
22}
23
24/// A bound parameter of a translated query.
25#[derive(Clone, Debug, PartialEq)]
26#[non_exhaustive]
27pub enum SqlParam {
28    /// A literal from the expression.
29    Value(Value),
30    /// A criteria parameter (`?Name`) for the caller to supply.
31    Named(String),
32}
33
34/// Translated SQL: text with placeholders, and what to bind to them, in order.
35#[derive(Clone, Debug, PartialEq)]
36pub struct SqlQuery {
37    /// The SQL text.
38    pub text: String,
39    /// The values for the placeholders, in order.
40    pub params: Vec<SqlParam>,
41}
42
43/// Why something cannot run in the database.
44#[derive(Clone, Debug, PartialEq, Eq)]
45#[non_exhaustive]
46pub enum SqlError {
47    /// This function or construct has no SQL translation here.
48    Unsupported(String),
49}
50
51impl std::fmt::Display for SqlError {
52    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
53        match self {
54            Self::Unsupported(what) => write!(f, "{what} cannot be translated to SQL"),
55        }
56    }
57}
58
59impl std::error::Error for SqlError {}
60
61/// Translates `criteria` to a SQL expression for a `WHERE` or a `SELECT` list.
62///
63/// ```
64/// use ravenrs_criteria::{Criteria, Dialect, SqlParam, Value, to_sql};
65///
66/// let c = Criteria::parse("[Total] > 100 And [Name] Like ?Pattern").expect("valid");
67/// let q = to_sql(&c, Dialect::Postgres).expect("translatable");
68/// assert_eq!(q.text, r#""Total" > $1 AND "Name" LIKE $2"#);
69/// assert_eq!(q.params, [SqlParam::Value(Value::Number(100.0)), SqlParam::Named("Pattern".into())]);
70/// ```
71pub fn to_sql(criteria: &Criteria, dialect: Dialect) -> Result<SqlQuery, SqlError> {
72    let mut w = Writer { dialect, params: Vec::new() };
73    let text = w.node(criteria)?;
74    Ok(SqlQuery { text, params: w.params })
75}
76
77struct Writer {
78    dialect: Dialect,
79    params: Vec<SqlParam>,
80}
81
82impl Writer {
83    fn bind(&mut self, p: SqlParam) -> String {
84        self.params.push(p);
85        let n = self.params.len();
86        match self.dialect {
87            Dialect::Postgres => format!("${n}"),
88            Dialect::MySql => "?".to_owned(),
89            Dialect::SqlServer => format!("@p{n}"),
90            Dialect::Sqlite => format!("?{n}"),
91        }
92    }
93
94    fn ident(&self, name: &str) -> String {
95        match self.dialect {
96            Dialect::Postgres | Dialect::Sqlite => format!("\"{}\"", name.replace('"', "\"\"")),
97            Dialect::MySql => format!("`{}`", name.replace('`', "``")),
98            Dialect::SqlServer => format!("[{}]", name.replace(']', "]]")),
99        }
100    }
101
102    /// A child, in parentheses unless it is a simple term.
103    fn term(&mut self, c: &Criteria) -> Result<String, SqlError> {
104        let s = self.node(c)?;
105        Ok(match c {
106            Criteria::Value(_)
107            | Criteria::Property(_)
108            | Criteria::Parameter(_)
109            | Criteria::Function(..)
110            | Criteria::Aggregate(_) => s,
111            _ => format!("({s})"),
112        })
113    }
114
115    fn node(&mut self, c: &Criteria) -> Result<String, SqlError> {
116        Ok(match c {
117            Criteria::Value(Value::Null) => "NULL".to_owned(),
118            Criteria::Value(v) => self.bind(SqlParam::Value(v.clone())),
119            Criteria::Parameter(p) => self.bind(SqlParam::Named(p.clone())),
120            Criteria::Property(path) => {
121                if path.first().is_some_and(|p| p == "^") {
122                    return Err(SqlError::Unsupported("a parent row reference".into()));
123                }
124                path.iter().map(|p| self.ident(p)).collect::<Vec<_>>().join(".")
125            }
126            Criteria::Unary(UnaryOp::Not, a) => format!("NOT {}", self.term(a)?),
127            Criteria::Unary(UnaryOp::Minus, a) => format!("-{}", self.term(a)?),
128            Criteria::Unary(UnaryOp::BitNot, a) => format!("~{}", self.term(a)?),
129            Criteria::Binary(BinaryOp::Eq, a, b) if **b == Criteria::Value(Value::Null) => {
130                format!("{} IS NULL", self.term(a)?)
131            }
132            Criteria::Binary(BinaryOp::Ne, a, b) if **b == Criteria::Value(Value::Null) => {
133                format!("{} IS NOT NULL", self.term(a)?)
134            }
135            Criteria::Binary(op, a, b) => {
136                let symbol = match op {
137                    BinaryOp::Add => "+",
138                    BinaryOp::Sub => "-",
139                    BinaryOp::Mul => "*",
140                    BinaryOp::Div => "/",
141                    BinaryOp::Rem => "%",
142                    BinaryOp::Eq => "=",
143                    BinaryOp::Ne => "<>",
144                    BinaryOp::Lt => "<",
145                    BinaryOp::Le => "<=",
146                    BinaryOp::Gt => ">",
147                    BinaryOp::Ge => ">=",
148                    BinaryOp::Like => "LIKE",
149                    BinaryOp::BitAnd => "&",
150                    BinaryOp::BitOr => "|",
151                    BinaryOp::BitXor => "^",
152                };
153                // `+` in criteria also joins text, which SQL cannot tell from the types here.
154                format!("{} {symbol} {}", self.term(a)?, self.term(b)?)
155            }
156            Criteria::Group(op, items) => {
157                let word = if *op == GroupOp::And { " AND " } else { " OR " };
158                let parts = items
159                    .iter()
160                    .map(|i| match i {
161                        // Comparisons bind tighter than AND and OR.
162                        Criteria::Binary(
163                            BinaryOp::Eq
164                            | BinaryOp::Ne
165                            | BinaryOp::Lt
166                            | BinaryOp::Le
167                            | BinaryOp::Gt
168                            | BinaryOp::Ge
169                            | BinaryOp::Like,
170                            ..,
171                        )
172                        | Criteria::In(..)
173                        | Criteria::Between(..)
174                        | Criteria::IsNull(_) => self.node(i),
175                        other => self.term(other),
176                    })
177                    .collect::<Result<Vec<_>, _>>()?;
178                parts.join(word)
179            }
180            Criteria::In(a, items) => {
181                let value = self.term(a)?;
182                let list = items.iter().map(|i| self.node(i)).collect::<Result<Vec<_>, _>>()?;
183                format!("{value} IN ({})", list.join(", "))
184            }
185            Criteria::Between(a, lo, hi) => {
186                let (a, lo, hi) = (self.term(a)?, self.term(lo)?, self.term(hi)?);
187                format!("{a} BETWEEN {lo} AND {hi}")
188            }
189            Criteria::IsNull(a) => format!("{} IS NULL", self.term(a)?),
190            Criteria::Function(f, args) => self.function(*f, args)?,
191            Criteria::Aggregate(call) => {
192                if call.collection.is_some() || call.condition.is_some() {
193                    return Err(SqlError::Unsupported("an aggregate over a nested collection".into()));
194                }
195                let name = match call.function {
196                    Aggregate::Sum => "SUM",
197                    Aggregate::Count => "COUNT",
198                    Aggregate::Avg => "AVG",
199                    Aggregate::Min => "MIN",
200                    Aggregate::Max => "MAX",
201                    other => return Err(SqlError::Unsupported(format!("the aggregate {}", other.name()))),
202                };
203                match call.args.first() {
204                    Some(a) => format!("{name}({})", self.node(a)?),
205                    None => format!("{name}(*)"),
206                }
207            }
208            Criteria::Custom(name, _) => {
209                return Err(SqlError::Unsupported(format!("the custom function {name}")));
210            }
211        })
212    }
213
214    fn call(&mut self, name: &str, args: &[Criteria]) -> Result<String, SqlError> {
215        let list = args.iter().map(|a| self.node(a)).collect::<Result<Vec<_>, _>>()?;
216        Ok(format!("{name}({})", list.join(", ")))
217    }
218
219    /// `text LIKE pattern`, with the part bound as data and its wildcards escaped.
220    fn like(&mut self, args: &[Criteria], before: bool, after: bool) -> Result<String, SqlError> {
221        let text = self.term(&args[0])?;
222        let Criteria::Value(Value::Text(part)) = &args[1] else {
223            return Err(SqlError::Unsupported("a text search with a computed pattern".into()));
224        };
225        let escaped = part.replace('\\', "\\\\").replace('%', "\\%").replace('_', "\\_");
226        let pattern = format!("{}{escaped}{}", if before { "%" } else { "" }, if after { "%" } else { "" });
227        let bound = self.bind(SqlParam::Value(Value::Text(pattern)));
228        Ok(format!("{text} LIKE {bound} ESCAPE '\\'"))
229    }
230
231    fn date_part(&mut self, part: &str, arg: &Criteria) -> Result<String, SqlError> {
232        let v = self.node(arg)?;
233        Ok(match self.dialect {
234            Dialect::Postgres => format!("EXTRACT({part} FROM {v})"),
235            Dialect::MySql => format!("{part}({v})"),
236            Dialect::SqlServer => format!("DATEPART({part}, {v})"),
237            Dialect::Sqlite => {
238                let code = match part {
239                    "YEAR" => "%Y",
240                    "MONTH" => "%m",
241                    _ => "%d",
242                };
243                format!("CAST(strftime('{code}', {v}) AS INTEGER)")
244            }
245        })
246    }
247
248    fn function(&mut self, f: Function, args: &[Criteria]) -> Result<String, SqlError> {
249        use Function as F;
250        Ok(match f {
251            F::Upper => self.call("UPPER", args)?,
252            F::Lower => self.call("LOWER", args)?,
253            F::Trim => self.call("TRIM", args)?,
254            F::Abs => self.call("ABS", args)?,
255            F::Round => self.call("ROUND", args)?,
256            F::Floor => self.call("FLOOR", args)?,
257            F::Ceiling => self.call("CEILING", args)?,
258            F::Power => self.call("POWER", args)?,
259            F::Sqrt | F::Sqr => self.call("SQRT", args)?,
260            F::Replace => self.call("REPLACE", args)?,
261            F::Concat => match self.dialect {
262                Dialect::Sqlite => {
263                    let parts = args.iter().map(|a| self.term(a)).collect::<Result<Vec<_>, _>>()?;
264                    parts.join(" || ")
265                }
266                _ => self.call("CONCAT", args)?,
267            },
268            F::Len => match self.dialect {
269                Dialect::SqlServer => self.call("LEN", args)?,
270                Dialect::MySql => self.call("CHAR_LENGTH", args)?,
271                _ => self.call("LENGTH", args)?,
272            },
273            F::IsNull if args.len() == 2 => self.call("COALESCE", args)?,
274            F::IsNull => format!("{} IS NULL", self.term(&args[0])?),
275            F::IsNullOrEmpty => {
276                let a = self.term(&args[0])?;
277                format!("({a} IS NULL OR {a} = '')")
278            }
279            F::Iif => {
280                let mut s = String::from("CASE");
281                let mut i = 0;
282                while i + 1 < args.len() {
283                    let (cond, value) = (self.node(&args[i])?, self.node(&args[i + 1])?);
284                    s.push_str(&format!(" WHEN {cond} THEN {value}"));
285                    i += 2;
286                }
287                let last = self.node(&args[args.len() - 1])?;
288                format!("{s} ELSE {last} END")
289            }
290            F::Contains => self.like(args, true, true)?,
291            F::StartsWith => self.like(args, false, true)?,
292            F::EndsWith => self.like(args, true, false)?,
293            F::GetYear => self.date_part("YEAR", &args[0])?,
294            F::GetMonth => self.date_part("MONTH", &args[0])?,
295            F::GetDay => self.date_part("DAY", &args[0])?,
296            other => return Err(SqlError::Unsupported(format!("the function {}", other.name()))),
297        })
298    }
299}
ravenrs_criteria/sql.rs

ravenrs_criteria/
sql.rs
