nodedb_sql/parser/
query_function.rs

1// SPDX-License-Identifier: Apache-2.0
2
3//! Recognition of the Origin query functions from the parsed statement.
4//!
5//! `VERIFY_AUDIT_CHAIN`, `VERIFY_HASH_CHAIN`, `BALANCE_AS_OF`,
6//! `TEMPORAL_LOOKUP`, `VERIFY_BALANCE` and `CONVERT_CURRENCY_LOOKUP` each run
7//! as a whole statement: `SELECT <FUNC>(<literal>, ...)`. A lexer gate keeps
8//! the extra parse off ordinary statements. It passes only a `SELECT` whose
9//! plain text (outside literals, quoted identifiers and comments) names one of
10//! the functions. The parsed AST then decides.
11
12use std::ops::ControlFlow;
13
14use sqlparser::ast::{
15    Expr, FunctionArg, FunctionArgExpr, FunctionArguments, SelectItem, SetExpr, Statement,
16    UnaryOperator, Value,
17};
18
19use crate::error::SqlError;
20use crate::parser::preprocess::lex;
21use crate::parser::statement::parse_sql;
22
23/// One query function.
24#[derive(Debug, Clone, Copy, PartialEq, Eq)]
25pub enum QueryFunction {
26    VerifyAuditChain,
27    VerifyHashChain,
28    BalanceAsOf,
29    TemporalLookup,
30    VerifyBalance,
31    ConvertCurrencyLookup,
32}
33
34impl QueryFunction {
35    pub const ALL: [Self; 6] = [
36        Self::VerifyAuditChain,
37        Self::VerifyHashChain,
38        Self::BalanceAsOf,
39        Self::TemporalLookup,
40        Self::VerifyBalance,
41        Self::ConvertCurrencyLookup,
42    ];
43
44    /// The function name as SQL spells it.
45    pub fn name(self) -> &'static str {
46        match self {
47            Self::VerifyAuditChain => "VERIFY_AUDIT_CHAIN",
48            Self::VerifyHashChain => "VERIFY_HASH_CHAIN",
49            Self::BalanceAsOf => "BALANCE_AS_OF",
50            Self::TemporalLookup => "TEMPORAL_LOOKUP",
51            Self::VerifyBalance => "VERIFY_BALANCE",
52            Self::ConvertCurrencyLookup => "CONVERT_CURRENCY_LOOKUP",
53        }
54    }
55
56    /// The argument list, as error messages show it.
57    fn signature(self) -> &'static str {
58        match self {
59            Self::VerifyAuditChain => "([from_seq [, to_seq]])",
60            Self::VerifyHashChain => "(collection)",
61            Self::BalanceAsOf => "(collection, key, column, timestamp)",
62            Self::TemporalLookup => "(table, key_value, as_of, key_column, time_column)",
63            Self::VerifyBalance => "(collection, column)",
64            Self::ConvertCurrencyLookup => {
65                "(amount, from, to, rate_table, as_of, key_column, rate_column, time_column, precision)"
66            }
67        }
68    }
69
70    fn from_name(name: &str) -> Option<Self> {
71        Self::ALL
72            .into_iter()
73            .find(|function| function.name().eq_ignore_ascii_case(name))
74    }
75}
76
77/// `VERIFY_AUDIT_CHAIN([from_seq [, to_seq]])`.
78#[derive(Debug, Clone, PartialEq, Eq)]
79pub struct VerifyAuditChainArgs {
80    pub from_seq: Option<String>,
81    pub to_seq: Option<String>,
82}
83
84/// `VERIFY_HASH_CHAIN(collection)`.
85#[derive(Debug, Clone, PartialEq, Eq)]
86pub struct VerifyHashChainArgs {
87    pub collection: String,
88}
89
90/// `BALANCE_AS_OF(collection, key, column, timestamp)`.
91#[derive(Debug, Clone, PartialEq, Eq)]
92pub struct BalanceAsOfArgs {
93    pub collection: String,
94    pub key: String,
95    pub column: String,
96    pub timestamp: String,
97}
98
99/// `TEMPORAL_LOOKUP(table, key_value, as_of, key_column, time_column)`.
100#[derive(Debug, Clone, PartialEq, Eq)]
101pub struct TemporalLookupArgs {
102    pub table: String,
103    pub key_value: String,
104    pub as_of: String,
105    pub key_column: String,
106    pub time_column: String,
107}
108
109/// `VERIFY_BALANCE(collection, column)`.
110#[derive(Debug, Clone, PartialEq, Eq)]
111pub struct VerifyBalanceArgs {
112    pub collection: String,
113    pub column: String,
114}
115
116/// `CONVERT_CURRENCY_LOOKUP(amount, from, to, rate_table, as_of, key_column,
117/// rate_column, time_column, precision)`.
118#[derive(Debug, Clone, PartialEq, Eq)]
119pub struct ConvertCurrencyLookupArgs {
120    pub amount: String,
121    pub from: String,
122    pub to: String,
123    pub rate_table: String,
124    pub as_of: String,
125    pub key_column: String,
126    pub rate_column: String,
127    pub time_column: String,
128    pub precision: String,
129}
130
131/// One recognized call. Each argument holds the literal's text: a string's
132/// content (quotes removed, escapes resolved) or a number as written.
133#[derive(Debug, Clone, PartialEq, Eq)]
134pub enum QueryFunctionCall {
135    VerifyAuditChain(VerifyAuditChainArgs),
136    VerifyHashChain(VerifyHashChainArgs),
137    BalanceAsOf(BalanceAsOfArgs),
138    TemporalLookup(TemporalLookupArgs),
139    VerifyBalance(VerifyBalanceArgs),
140    ConvertCurrencyLookup(ConvertCurrencyLookupArgs),
141}
142
143/// Recognize a query-function statement.
144///
145/// `None` means the statement is not a query-function call. `Some(Err)` means
146/// it names a query function in a form the function does not accept.
147pub fn parse_query_function(sql: &str) -> Option<Result<QueryFunctionCall, SqlError>> {
148    if !lex::first_sql_word(sql)?.eq_ignore_ascii_case("SELECT") {
149        return None;
150    }
151    if !QueryFunction::ALL
152        .iter()
153        .any(|function| lex::keyword_position_outside_literals(sql, function.name()).is_some())
154    {
155        return None;
156    }
157    // A statement sqlparser refuses goes to the planner, which reports the
158    // parse error itself.
159    let statements = parse_sql(sql).ok()?;
160    if let [statement] = statements.as_slice()
161        && let Some((function, args)) = standalone_call(statement)
162    {
163        return Some(bind(function, args));
164    }
165    statements
166        .iter()
167        .find_map(named_function)
168        .map(|function| Err(misplaced(function)))
169}
170
171/// The call when `statement` is exactly `SELECT <FUNC>(...)`.
172fn standalone_call(statement: &Statement) -> Option<(QueryFunction, &[FunctionArg])> {
173    let Statement::Query(query) = statement else {
174        return None;
175    };
176    let SetExpr::Select(select) = query.body.as_ref() else {
177        return None;
178    };
179    let [SelectItem::UnnamedExpr(expr)] = select.projection.as_slice() else {
180        return None;
181    };
182    let Expr::Function(func) = expr else {
183        return None;
184    };
185    // Any other clause (FROM, WHERE, LIMIT, WITH, DISTINCT, ...) changes the
186    // rendered statement.
187    if statement.to_string() != format!("SELECT {expr}") {
188        return None;
189    }
190    if func.over.is_some()
191        || func.filter.is_some()
192        || !func.within_group.is_empty()
193        || func.null_treatment.is_some()
194        || func.uses_odbc_syntax
195        || !matches!(func.parameters, FunctionArguments::None)
196    {
197        return None;
198    }
199    let FunctionArguments::List(list) = &func.args else {
200        return None;
201    };
202    if list.duplicate_treatment.is_some() || !list.clauses.is_empty() {
203        return None;
204    }
205    let function = QueryFunction::from_name(&func.name.to_string())?;
206    Some((function, list.args.as_slice()))
207}
208
209/// The first query function the statement calls anywhere.
210fn named_function(statement: &Statement) -> Option<QueryFunction> {
211    let found = sqlparser::ast::visit_expressions(statement, |expr| match expr {
212        Expr::Function(func) => match QueryFunction::from_name(&func.name.to_string()) {
213            Some(function) => ControlFlow::Break(function),
214            None => ControlFlow::Continue(()),
215        },
216        _ => ControlFlow::Continue(()),
217    });
218    match found {
219        ControlFlow::Break(function) => Some(function),
220        ControlFlow::Continue(()) => None,
221    }
222}
223
224fn bind(function: QueryFunction, args: &[FunctionArg]) -> Result<QueryFunctionCall, SqlError> {
225    let values = args
226        .iter()
227        .enumerate()
228        .map(|(index, arg)| literal(function, index + 1, arg))
229        .collect::<Result<Vec<_>, _>>()?;
230    Ok(match function {
231        QueryFunction::VerifyAuditChain => {
232            if values.len() > 2 {
233                return Err(arity(function));
234            }
235            let mut values = values.into_iter();
236            QueryFunctionCall::VerifyAuditChain(VerifyAuditChainArgs {
237                from_seq: values.next(),
238                to_seq: values.next(),
239            })
240        }
241        QueryFunction::VerifyHashChain => {
242            let [collection] = exact(function, values)?;
243            QueryFunctionCall::VerifyHashChain(VerifyHashChainArgs { collection })
244        }
245        QueryFunction::BalanceAsOf => {
246            let [collection, key, column, timestamp] = exact(function, values)?;
247            QueryFunctionCall::BalanceAsOf(BalanceAsOfArgs {
248                collection,
249                key,
250                column,
251                timestamp,
252            })
253        }
254        QueryFunction::TemporalLookup => {
255            let [table, key_value, as_of, key_column, time_column] = exact(function, values)?;
256            QueryFunctionCall::TemporalLookup(TemporalLookupArgs {
257                table,
258                key_value,
259                as_of,
260                key_column,
261                time_column,
262            })
263        }
264        QueryFunction::VerifyBalance => {
265            let [collection, column] = exact(function, values)?;
266            QueryFunctionCall::VerifyBalance(VerifyBalanceArgs { collection, column })
267        }
268        QueryFunction::ConvertCurrencyLookup => {
269            let [
270                amount,
271                from,
272                to,
273                rate_table,
274                as_of,
275                key_column,
276                rate_column,
277                time_column,
278                precision,
279            ] = exact(function, values)?;
280            QueryFunctionCall::ConvertCurrencyLookup(ConvertCurrencyLookupArgs {
281                amount,
282                from,
283                to,
284                rate_table,
285                as_of,
286                key_column,
287                rate_column,
288                time_column,
289                precision,
290            })
291        }
292    })
293}
294
295fn exact<const N: usize>(
296    function: QueryFunction,
297    values: Vec<String>,
298) -> Result<[String; N], SqlError> {
299    <[String; N]>::try_from(values).map_err(|_| arity(function))
300}
301
302/// The text of one literal argument.
303fn literal(
304    function: QueryFunction,
305    position: usize,
306    arg: &FunctionArg,
307) -> Result<String, SqlError> {
308    let FunctionArg::Unnamed(FunctionArgExpr::Expr(expr)) = arg else {
309        return Err(not_literal(function, position, &arg.to_string()));
310    };
311    let refused = || not_literal(function, position, &expr.to_string());
312    match expr {
313        Expr::Value(value) => match &value.value {
314            Value::SingleQuotedString(text) | Value::EscapedStringLiteral(text) => Ok(text.clone()),
315            Value::Number(number, _) => Ok(number.clone()),
316            _ => Err(refused()),
317        },
318        Expr::UnaryOp {
319            op: UnaryOperator::Minus,
320            expr: inner,
321        } => match inner.as_ref() {
322            Expr::Value(value) => match &value.value {
323                Value::Number(number, _) => Ok(format!("-{number}")),
324                _ => Err(refused()),
325            },
326            _ => Err(refused()),
327        },
328        _ => Err(refused()),
329    }
330}
331
332fn arity(function: QueryFunction) -> SqlError {
333    SqlError::Parse {
334        detail: format!("{} requires {}", function.name(), function.signature()),
335    }
336}
337
338fn not_literal(function: QueryFunction, position: usize, found: &str) -> SqlError {
339    SqlError::Parse {
340        detail: format!(
341            "{} argument {position} must be a string or number literal — found {found}",
342            function.name()
343        ),
344    }
345}
346
347fn misplaced(function: QueryFunction) -> SqlError {
348    let name = function.name();
349    SqlError::Unsupported {
350        detail: format!(
351            "{name}(...) runs only as a whole statement: SELECT {name}{}",
352            function.signature()
353        ),
354    }
355}
356
357#[cfg(test)]
358mod tests {
359    use super::*;
360
361    fn call(sql: &str) -> QueryFunctionCall {
362        parse_query_function(sql)
363            .expect("statement is a query-function call")
364            .expect("call is well-formed")
365    }
366
367    fn refusal(sql: &str) -> SqlError {
368        parse_query_function(sql)
369            .expect("statement names a query function")
370            .expect_err("call must be refused")
371    }
372
373    #[test]
374    fn routes_standalone_call() {
375        let expected = QueryFunctionCall::VerifyBalance(VerifyBalanceArgs {
376            collection: "c".into(),
377            column: "col".into(),
378        });
379        assert_eq!(call("SELECT VERIFY_BALANCE('c','col')"), expected);
380        assert_eq!(call("select verify_balance('c', 'col')"), expected);
381        assert_eq!(call("/* x */ SELECT VERIFY_BALANCE('c', 'col');"), expected);
382    }
383
384    #[test]
385    fn ignores_name_in_literal_comment_or_identifier() {
386        for sql in [
387            "INSERT INTO t (id) VALUES ('tmp_verify_balance_x')",
388            "SELECT 'verify_balance' FROM t",
389            "SELECT 1 -- verify_balance note",
390            "SELECT \"verify_balance\" FROM t",
391            "SELECT verify_balance FROM t",
392        ] {
393            assert!(parse_query_function(sql).is_none(), "{sql}");
394        }
395    }
396
397    #[test]
398    fn comma_and_escaped_quote_inside_literal() {
399        match call("SELECT TEMPORAL_LOOKUP('t', 'a,b', '2024-01-01', 'k', 'ts')") {
400            QueryFunctionCall::TemporalLookup(args) => {
401                assert_eq!(args.table, "t");
402                assert_eq!(args.key_value, "a,b");
403                assert_eq!(args.time_column, "ts");
404            }
405            other => panic!("expected TemporalLookup, got {other:?}"),
406        }
407        assert_eq!(
408            call("SELECT VERIFY_HASH_CHAIN('it''s')"),
409            QueryFunctionCall::VerifyHashChain(VerifyHashChainArgs {
410                collection: "it's".into()
411            })
412        );
413    }
414
415    #[test]
416    fn arity_is_exact() {
417        for sql in [
418            "SELECT VERIFY_BALANCE('c')",
419            "SELECT VERIFY_BALANCE('a', 'b', 'c')",
420        ] {
421            let error = refusal(sql);
422            assert!(
423                error
424                    .to_string()
425                    .contains("VERIFY_BALANCE requires (collection, column)"),
426                "{sql}: {error}"
427            );
428        }
429        assert!(matches!(
430            refusal("SELECT VERIFY_AUDIT_CHAIN(1, 2, 3)"),
431            SqlError::Parse { .. }
432        ));
433    }
434
435    #[test]
436    fn audit_chain_without_args_defaults() {
437        assert_eq!(
438            call("SELECT VERIFY_AUDIT_CHAIN()"),
439            QueryFunctionCall::VerifyAuditChain(VerifyAuditChainArgs {
440                from_seq: None,
441                to_seq: None,
442            })
443        );
444        assert_eq!(
445            call("SELECT VERIFY_AUDIT_CHAIN(1, 100)"),
446            QueryFunctionCall::VerifyAuditChain(VerifyAuditChainArgs {
447                from_seq: Some("1".into()),
448                to_seq: Some("100".into()),
449            })
450        );
451    }
452
453    #[test]
454    fn misplaced_call_is_refused() {
455        for sql in [
456            "SELECT id, VERIFY_BALANCE('c', 'col') FROM t",
457            "SELECT VERIFY_BALANCE('c', 'col') AS r",
458            "SELECT VERIFY_BALANCE('c', 'col') FROM t",
459        ] {
460            let error = refusal(sql);
461            assert!(
462                matches!(error, SqlError::Unsupported { .. }),
463                "{sql}: {error}"
464            );
465            assert!(error.to_string().contains("whole statement"), "{error}");
466        }
467    }
468
469    #[test]
470    fn non_literal_argument_is_refused() {
471        let error = refusal("SELECT VERIFY_HASH_CHAIN(upper('x'))");
472        assert!(error.to_string().contains("argument 1"), "{error}");
473    }
474
475    #[test]
476    fn negative_number_keeps_its_sign() {
477        match call(
478            "SELECT CONVERT_CURRENCY_LOOKUP(-5, 'USD', 'EUR', 'rates', '2024-01-01', \
479             'pair', 'rate', 'ts', 2)",
480        ) {
481            QueryFunctionCall::ConvertCurrencyLookup(args) => {
482                assert_eq!(args.amount, "-5");
483                assert_eq!(args.precision, "2");
484            }
485            other => panic!("expected ConvertCurrencyLookup, got {other:?}"),
486        }
487    }
488}
nodedb_sql/parser/query_function.rs

nodedb_sql/parser/
query_function.rs
