use std::{fmt, sync::Arc};
use regex::Regex;
use sea_orm::{DbBackend, Statement, Value, sea_query::ValueType};
use sqlparser::tokenizer::{Token, Whitespace};
use crate::parse::{Parsed, parse, tokens};
type StmtPredicate = Arc<dyn Fn(&Statement) -> bool + Send + Sync>;
type ValuePredicate = Arc<dyn Fn(&Value) -> bool + Send + Sync>;
#[derive(Clone)]
pub(crate) enum SqlMatcher {
Exact(String),
Regex(Regex),
Contains(String),
Statement {
stmt: Statement,
ignore_limit: bool,
unpaginated: Option<(String, Vec<Value>)>,
},
Fn(StmtPredicate),
}
impl SqlMatcher {
pub(crate) fn statement(stmt: Statement, ignore_limit: bool) -> Self {
let unpaginated = parse(&stmt).map(|parsed| parsed.unpaginated);
SqlMatcher::Statement { stmt, ignore_limit, unpaginated }
}
pub(crate) fn matches(&self, stmt: &Statement, parsed: Option<&Parsed>) -> bool {
match self {
SqlMatcher::Exact(sql) => normalize(sql, stmt.db_backend) == normalize(&stmt.sql, stmt.db_backend),
SqlMatcher::Regex(re) => re.is_match(&stmt.sql),
SqlMatcher::Contains(needle) => normalize(&stmt.sql, stmt.db_backend).contains(&normalize(needle, stmt.db_backend)),
SqlMatcher::Statement {
stmt: expected, ignore_limit: false, ..
} => same_statement(expected, stmt),
SqlMatcher::Statement {
stmt: expected,
ignore_limit: true,
unpaginated,
} => same_unpaginated(expected, unpaginated.as_ref(), stmt, parsed),
SqlMatcher::Fn(predicate) => predicate(stmt),
}
}
pub(crate) fn hint(&self, stmt: &Statement, parsed: Option<&Parsed>) -> Option<&'static str> {
match self {
SqlMatcher::Statement {
stmt: expected,
ignore_limit: false,
unpaginated,
} if same_unpaginated(expected, unpaginated.as_ref(), stmt, parsed) => Some("they only differ by LIMIT/OFFSET, which `.one()` and paginators add: use `matching_ignoring_limit`"),
_ => None,
}
}
}
fn same_statement(expected: &Statement, stmt: &Statement) -> bool {
normalize(&expected.sql, expected.db_backend) == normalize(&stmt.sql, stmt.db_backend) && expected.args() == stmt.args()
}
fn same_unpaginated(expected: &Statement, expected_unpaginated: Option<&(String, Vec<Value>)>, stmt: &Statement, parsed: Option<&Parsed>) -> bool {
match (expected_unpaginated, parsed) {
(Some(expected), Some(actual)) => *expected == actual.unpaginated,
_ => same_statement(expected, stmt),
}
}
impl fmt::Display for SqlMatcher {
fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
match self {
SqlMatcher::Exact(sql) => write!(f, "SQL equal to `{}`", normalize_text(sql)),
SqlMatcher::Regex(re) => write!(f, "SQL matching /{re}/"),
SqlMatcher::Contains(needle) => write!(f, "SQL containing `{needle}`"),
SqlMatcher::Statement { stmt, ignore_limit, .. } => {
write!(f, "statement `{}` with {:?}", normalize(&stmt.sql, stmt.db_backend), stmt.args())?;
if *ignore_limit {
write!(f, " (ignoring LIMIT/OFFSET)")?;
}
Ok(())
}
SqlMatcher::Fn(_) => write!(f, "SQL accepted by a custom predicate"),
}
}
}
#[derive(Clone, Copy, Debug)]
pub struct Any;
#[derive(Clone)]
pub struct Arg(ArgMatcher);
#[derive(Clone)]
enum ArgMatcher {
Eq(Value),
Any,
Fn(ValuePredicate),
}
impl Arg {
pub fn matching(predicate: impl Fn(&Value) -> bool + Send + Sync + 'static) -> Self {
Arg(ArgMatcher::Fn(Arc::new(predicate)))
}
pub(crate) fn value(&self) -> Option<&Value> {
match &self.0 {
ArgMatcher::Eq(value) => Some(value),
_ => None,
}
}
pub(crate) fn is_any(&self) -> bool {
matches!(self.0, ArgMatcher::Any)
}
pub(crate) fn matches(&self, value: &Value) -> bool {
match &self.0 {
ArgMatcher::Eq(expected) => expected == value || matches!((integer(expected), integer(value)), (Some(lhs), Some(rhs)) if lhs == rhs),
ArgMatcher::Any => true,
ArgMatcher::Fn(predicate) => predicate(value),
}
}
}
impl fmt::Debug for Arg {
fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
match &self.0 {
ArgMatcher::Eq(value) => write!(f, "{value:?}"),
ArgMatcher::Any => write!(f, "<any>"),
ArgMatcher::Fn(_) => write!(f, "<predicate>"),
}
}
}
pub trait IntoArgs: sealed::IntoArgs {}
impl<T: sealed::IntoArgs> IntoArgs for T {}
pub(crate) mod sealed {
use sea_orm::Value;
use super::{Any, Arg, ArgMatcher};
pub trait IntoArg {
fn into_arg(self) -> Arg;
}
impl<T: Into<Value>> IntoArg for T {
fn into_arg(self) -> Arg {
Arg(ArgMatcher::Eq(self.into()))
}
}
impl IntoArg for Any {
fn into_arg(self) -> Arg {
Arg(ArgMatcher::Any)
}
}
impl IntoArg for Arg {
fn into_arg(self) -> Arg {
self
}
}
pub trait IntoArgs {
fn into_args(self) -> Vec<Arg>;
}
impl IntoArgs for () {
fn into_args(self) -> Vec<Arg> {
Vec::new()
}
}
impl<T: IntoArg> IntoArgs for Vec<T> {
fn into_args(self) -> Vec<Arg> {
self.into_iter().map(IntoArg::into_arg).collect()
}
}
macro_rules! impl_into_args {
($($name:ident),+) => {
impl<$($name: IntoArg),+> IntoArgs for ($($name,)+) {
#[allow(non_snake_case)]
fn into_args(self) -> Vec<Arg> {
let ($($name,)+) = self;
vec![$($name.into_arg()),+]
}
}
};
}
impl_into_args!(A);
impl_into_args!(A, B);
impl_into_args!(A, B, C);
impl_into_args!(A, B, C, D);
impl_into_args!(A, B, C, D, E);
impl_into_args!(A, B, C, D, E, F);
impl_into_args!(A, B, C, D, E, F, G);
impl_into_args!(A, B, C, D, E, F, G, H);
}
pub(crate) fn integer(value: &Value) -> Option<Option<i128>> {
Some(match value {
Value::TinyInt(n) => n.map(i128::from),
Value::SmallInt(n) => n.map(i128::from),
Value::Int(n) => n.map(i128::from),
Value::BigInt(n) => n.map(i128::from),
Value::TinyUnsigned(n) => n.map(i128::from),
Value::SmallUnsigned(n) => n.map(i128::from),
Value::Unsigned(n) => n.map(i128::from),
Value::BigUnsigned(n) => n.map(i128::from),
_ => return None,
})
}
pub(crate) fn wrong_args(expected: &[Arg], actual: &[Value]) -> Option<Vec<usize>> {
if expected.len() != actual.len() {
return Some(Vec::new());
}
let wrong: Vec<usize> = expected
.iter()
.zip(actual)
.enumerate()
.filter(|(_, (arg, value))| !arg.matches(value))
.map(|(index, _)| index)
.collect();
(!wrong.is_empty()).then_some(wrong)
}
pub trait StatementExt {
fn args(&self) -> &[Value];
fn arg<T: ValueType>(&self, index: usize) -> Option<T>;
}
impl StatementExt for Statement {
fn args(&self) -> &[Value] {
self.values.as_ref().map(|values| values.0.as_slice()).unwrap_or_default()
}
fn arg<T: ValueType>(&self, index: usize) -> Option<T> {
self.args().get(index).cloned().and_then(|value| T::try_from(value).ok())
}
}
pub(crate) fn normalize(sql: &str, backend: DbBackend) -> String {
let Some(tokens) = tokens(sql, backend) else {
return normalize_text(sql);
};
let mut normalized = String::with_capacity(sql.len());
let mut space = false;
let mut after_line_comment = false;
for (token, range) in tokens {
match token {
Token::Whitespace(Whitespace::Space | Whitespace::Newline | Whitespace::Tab) => space = true,
token => {
if after_line_comment {
normalized.push('\n');
} else if space && !normalized.is_empty() {
normalized.push(' ');
}
normalized.push_str(&sql[range]);
space = false;
after_line_comment = matches!(token, Token::Whitespace(Whitespace::SingleLineComment { .. }));
}
}
}
normalized
}
fn normalize_text(sql: &str) -> String {
let mut normalized = String::with_capacity(sql.len());
let mut chars = sql.chars().peekable();
let mut space = false;
while let Some(c) = chars.next() {
if c.is_whitespace() {
space = true;
continue;
}
if space && !normalized.is_empty() {
normalized.push(' ');
}
space = false;
normalized.push(c);
if matches!(c, '\'' | '"' | '`') {
while let Some(quoted) = chars.next() {
normalized.push(quoted);
if quoted == c {
match chars.next_if_eq(&c) {
Some(escaped) => normalized.push(escaped),
None => break,
}
}
}
}
}
normalized
}
#[cfg(test)]
mod tests {
use sea_orm::{DbBackend, Values};
use super::{sealed::IntoArgs as _, *};
fn stmt(sql: &str, values: Vec<Value>) -> Statement {
Statement::from_sql_and_values(DbBackend::Postgres, sql, values)
}
fn args_match(expected: &[Arg], stmt: &Statement) -> bool {
wrong_args(expected, stmt.args()).is_none()
}
#[test]
fn sql_matchers() {
let s = stmt("SELECT *\n FROM \"cake\" WHERE id = $1", vec![1i32.into()]);
assert!(SqlMatcher::Exact(r#"SELECT * FROM "cake" WHERE id = $1"#.into()).matches(&s, parse(&s).as_ref()));
assert!(!SqlMatcher::Exact("SELECT 1".into()).matches(&s, parse(&s).as_ref()));
assert!(SqlMatcher::Regex(Regex::new(r#"FROM\s+"cake""#).unwrap()).matches(&s, parse(&s).as_ref()));
assert!(SqlMatcher::Contains(r#"FROM "cake""#.into()).matches(&s, parse(&s).as_ref()));
assert!(SqlMatcher::Fn(Arc::new(|s| s.sql.contains("cake"))).matches(&s, parse(&s).as_ref()));
let same = stmt(r#"SELECT * FROM "cake" WHERE id = $1"#, vec![1i32.into()]);
let other = stmt(r#"SELECT * FROM "cake" WHERE id = $1"#, vec![2i32.into()]);
let strict = |stmt| SqlMatcher::statement(stmt, false);
assert!(strict(same).matches(&s, parse(&s).as_ref()));
assert!(!strict(other).matches(&s, parse(&s).as_ref()));
}
#[test]
fn statement_ignoring_limit() {
let loose = |stmt| SqlMatcher::statement(stmt, true);
let expected = stmt(r#"SELECT * FROM "cake" WHERE id = $1"#, vec![1i32.into()]);
let paginated = stmt(r#"SELECT * FROM "cake" WHERE id = $1 LIMIT $2 OFFSET $3"#, vec![1i32.into(), 10u64.into(), 20u64.into()]);
let literal = stmt(r#"SELECT * FROM "cake" WHERE id = $1 LIMIT 1"#, vec![1i32.into()]);
let other = stmt(r#"SELECT * FROM "cake" WHERE id = $1 LIMIT $2"#, vec![2i32.into(), 1u64.into()]);
assert!(loose(expected.clone()).matches(&paginated, parse(&paginated).as_ref()));
assert!(loose(expected.clone()).matches(&literal, parse(&literal).as_ref()));
assert!(!loose(expected.clone()).matches(&other, parse(&other).as_ref()));
let strict = SqlMatcher::statement(expected, false);
assert!(!strict.matches(&paginated, parse(&paginated).as_ref()));
assert!(strict.hint(&paginated, parse(&paginated).as_ref()).is_some());
assert!(strict.hint(&other, parse(&other).as_ref()).is_none());
}
#[test]
fn normalization_keeps_quoted_whitespace() {
let pg = |sql| normalize(sql, DbBackend::Postgres);
assert_eq!(pg(" SELECT 'a b' ,\n\t\"my col\" FROM t "), r#"SELECT 'a b' , "my col" FROM t"#);
assert_eq!(pg("SELECT 'it''s ok' FROM t"), "SELECT 'it''s ok' FROM t");
assert_eq!(normalize("SELECT `a b` FROM t", DbBackend::MySql), "SELECT `a b` FROM t");
assert_eq!(normalize_text(" SELECT 'a b' ,\n x "), "SELECT 'a b' , x");
assert_eq!(pg("SELECT 'unterminated x"), "SELECT 'unterminated x");
let s = stmt("SELECT 'a b'", vec![]);
assert!(!SqlMatcher::Exact("SELECT 'a b'".into()).matches(&s, parse(&s).as_ref()));
assert!(SqlMatcher::Exact("SELECT 'a b'".into()).matches(&s, parse(&s).as_ref()));
assert!(!SqlMatcher::Contains("'a b'".into()).matches(&s, parse(&s).as_ref()));
}
#[test]
fn normalization_follows_the_dialect() {
let pg = |sql| normalize(sql, DbBackend::Postgres);
let mysql = |sql| normalize(sql, DbBackend::MySql);
assert_ne!(pg("SELECT 1 -- comment\nFROM cake"), pg("SELECT 1 -- comment FROM cake"));
assert_eq!(pg("SELECT 1 -- comment\n FROM cake"), pg("SELECT 1 -- comment\nFROM cake"));
assert_eq!(pg("SELECT 1 /* a b */ FROM cake"), "SELECT 1 /* a b */ FROM cake");
assert_ne!(pg("SELECT $$a b$$"), pg("SELECT $$a b$$"));
assert_ne!(mysql("SELECT 'it\\'s x'"), mysql("SELECT 'it\\'s x'"));
assert_eq!(mysql("SELECT 'it\\'s x' FROM t"), mysql("SELECT 'it\\'s x' FROM t"));
assert_ne!(pg("SELECT 'x'' || ''y'"), pg("SELECT 'x' || 'y'"));
assert_eq!(pg("SELECT 'it''s'"), "SELECT 'it''s'");
let s = stmt("SELECT 1 -- comment FROM cake", vec![]);
assert!(!SqlMatcher::Exact("SELECT 1 -- comment\nFROM cake".into()).matches(&s, parse(&s).as_ref()));
}
#[test]
fn integer_args_match_whatever_their_width() {
let s = stmt(
"SELECT * FROM t WHERE a = $1 AND b = $2 AND c = $3",
vec![Value::BigInt(Some(1)), Value::BigUnsigned(Some(7)), Value::SmallInt(None)],
);
assert!(args_match(&(1, 7u8, None::<i64>).into_args(), &s));
assert!(!args_match(&(2, 7, None::<i64>).into_args(), &s));
assert!(!args_match(&(1, 7, None::<String>).into_args(), &s));
assert!(!args_match(&("1", 7, None::<i64>).into_args(), &s));
}
#[test]
fn typed_args() {
let s = stmt("UPDATE x SET a = $1, b = $2", vec![1i32.into(), Value::String(None)]);
assert_eq!(s.arg::<i32>(0), Some(1));
assert_eq!(s.arg::<String>(0), None);
assert_eq!(s.arg::<String>(1), None);
assert_eq!(s.arg::<Option<String>>(1), Some(None));
assert_eq!(s.arg::<i32>(2), None);
let none = Statement::from_string(DbBackend::Postgres, "SELECT 1");
assert!(none.args().is_empty());
assert_eq!(none.arg::<i32>(0), None);
}
#[test]
fn arg_matchers() {
let s = stmt("UPDATE x SET a = $1, b = $2", vec![1i32.into(), "foo".into()]);
assert!(args_match(&(1i32, "foo").into_args(), &s));
assert!(args_match(&(Any, "foo").into_args(), &s));
assert!(args_match(&(Arg::matching(|v| *v == Value::Int(Some(1))), Any).into_args(), &s));
assert!(!args_match(&(2i32, "foo").into_args(), &s));
assert!(!args_match(&(1i32,).into_args(), &s));
assert!(args_match(
&().into_args(),
&Statement {
values: Some(Values(vec![])),
..s.clone()
}
));
}
}