use crate::sql_types::QualifiedIdentifier;
use crate::DbError;
use std::fmt;
#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
pub struct TableRef {
raw: String,
}
impl TableRef {
pub fn new(value: impl Into<String>) -> Result<Self, DbError> {
let raw = value.into();
#[cfg(feature = "postgres")]
QualifiedIdentifier::parse_postgres_with_max_parts(&raw, 2)?;
#[cfg(not(feature = "postgres"))]
QualifiedIdentifier::parse_with_max_parts(&raw, 2)?;
Ok(Self { raw })
}
pub fn as_str(&self) -> &str {
&self.raw
}
#[doc(hidden)]
pub fn __from_validated_literal(value: &'static str) -> Self {
Self {
raw: value.to_string(),
}
}
#[doc(hidden)]
pub fn __from_validated_owned(raw: String) -> Self {
Self { raw }
}
}
impl fmt::Display for TableRef {
fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
formatter.write_str(&self.raw)
}
}
#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
pub struct FieldRef {
raw: String,
#[cfg(feature = "mysql")]
mysql: String,
#[cfg(feature = "postgres")]
postgres: String,
}
impl FieldRef {
pub fn new(value: impl Into<String>) -> Result<Self, DbError> {
let raw = value.into();
#[cfg(feature = "postgres")]
{
let postgres = QualifiedIdentifier::parse_postgres(&raw)?.render_postgres();
Ok(Self {
#[cfg(feature = "mysql")]
mysql: quote_qualified(&raw, '`'),
postgres,
raw,
})
}
#[cfg(not(feature = "postgres"))]
{
let identifier = QualifiedIdentifier::parse(&raw)?;
let _ = &identifier;
Ok(Self {
#[cfg(feature = "mysql")]
mysql: identifier.render('`'),
raw,
})
}
}
pub fn as_str(&self) -> &str {
&self.raw
}
#[doc(hidden)]
pub fn __from_validated_literal(value: &'static str) -> Self {
Self {
#[cfg(feature = "mysql")]
mysql: value
.split('.')
.map(|part| format!("`{part}`"))
.collect::<Vec<_>>()
.join("."),
#[cfg(feature = "postgres")]
postgres: value
.split('.')
.map(|part| format!("\"{part}\""))
.collect::<Vec<_>>()
.join("."),
raw: value.to_string(),
}
}
#[doc(hidden)]
pub fn __from_validated_owned(raw: String) -> Self {
Self {
#[cfg(feature = "mysql")]
mysql: raw
.split('.')
.map(|part| format!("`{part}`"))
.collect::<Vec<_>>()
.join("."),
#[cfg(feature = "postgres")]
postgres: raw
.split('.')
.map(|part| format!("\"{part}\""))
.collect::<Vec<_>>()
.join("."),
raw,
}
}
#[cfg(feature = "mysql")]
#[doc(hidden)]
pub fn mysql_quoted(&self) -> &str {
&self.mysql
}
#[cfg(feature = "postgres")]
#[doc(hidden)]
pub fn postgres_quoted(&self) -> &str {
&self.postgres
}
}
#[doc(hidden)]
pub const fn __validate_table_literal(value: &str) {
validate_literal(value, 2);
}
#[doc(hidden)]
pub const fn __validate_field_literal(value: &str) {
validate_literal(value, 3);
}
const fn validate_literal(value: &str, max_parts: usize) {
let bytes = value.as_bytes();
assert!(!bytes.is_empty(), "SQL identifier cannot be empty");
let mut index = 0;
let mut segment_start = true;
let mut dots = 0;
while index < bytes.len() {
let byte = bytes[index];
if byte == b'.' {
assert!(!segment_start, "identifier segment cannot be empty");
dots += 1;
assert!(dots < max_parts, "SQL identifier has too many segments");
segment_start = true;
} else if segment_start {
assert!(
byte == b'_' || byte.is_ascii_alphabetic(),
"identifier must start with ASCII letter or underscore"
);
segment_start = false;
} else {
assert!(
byte == b'_' || byte.is_ascii_alphanumeric(),
"identifier contains invalid character"
);
}
index += 1;
}
assert!(!segment_start, "identifier segment cannot be empty");
}
impl fmt::Display for FieldRef {
fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
formatter.write_str(&self.raw)
}
}
#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
#[non_exhaustive]
pub enum CompareOp {
Eq,
Ne,
Gt,
Lt,
Gte,
Lte,
Like,
ILike,
NotILike,
RegexMatch,
RegexIMatch,
NotRegexMatch,
NotRegexIMatch,
IsDistinctFrom,
IsNotDistinctFrom,
}
#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
pub enum SortOrder {
Asc,
Desc,
}
#[derive(Debug, Clone, PartialEq)]
pub enum Predicate {
Compare(FieldRef, CompareOp, serde_json::Value),
In(FieldRef, Vec<serde_json::Value>),
NotIn(FieldRef, Vec<serde_json::Value>),
Between(FieldRef, serde_json::Value, serde_json::Value),
IsNull(FieldRef),
IsNotNull(FieldRef),
And(Vec<Predicate>),
Or(Vec<Predicate>),
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct SelectExpr {
aggregate: Aggregate,
field: Option<FieldRef>,
alias: Option<FieldRef>,
cast_double: bool,
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
enum Aggregate {
Count,
Sum,
Avg,
Min,
Max,
}
impl SelectExpr {
pub fn count_all() -> Self {
Self {
aggregate: Aggregate::Count,
field: None,
alias: None,
cast_double: false,
}
}
pub fn count(field: &FieldRef) -> Self {
Self::new(Aggregate::Count, field)
}
pub fn sum(field: &FieldRef) -> Self {
Self::new(Aggregate::Sum, field)
}
pub fn avg(field: &FieldRef) -> Self {
Self::new(Aggregate::Avg, field)
}
pub fn min(field: &FieldRef) -> Self {
Self::new(Aggregate::Min, field)
}
pub fn max(field: &FieldRef) -> Self {
Self::new(Aggregate::Max, field)
}
fn new(aggregate: Aggregate, field: &FieldRef) -> Self {
Self {
aggregate,
field: Some(field.clone()),
alias: None,
cast_double: false,
}
}
pub fn cast_double(mut self) -> Self {
self.cast_double = true;
self
}
pub fn alias(mut self, alias: &FieldRef) -> Self {
self.alias = Some(alias.clone());
self
}
#[cfg(feature = "mysql")]
pub(crate) fn mysql_sql(&self) -> String {
self.render('`', "DOUBLE")
}
#[cfg(feature = "postgres")]
pub(crate) fn postgres_sql(&self) -> String {
self.render('"', "DOUBLE PRECISION")
}
#[cfg(feature = "postgres")]
pub(crate) fn postgres_sql_without_alias(&self) -> String {
self.render_expression('"', "DOUBLE PRECISION")
}
#[cfg(any(feature = "mysql", feature = "postgres"))]
fn render(&self, quote: char, double_type: &str) -> String {
let expression = self.render_expression(quote, double_type);
self.alias.as_ref().map_or(expression.clone(), |alias| {
format!("{expression} AS {}", quote_qualified(alias.as_str(), quote))
})
}
#[cfg(any(feature = "mysql", feature = "postgres"))]
fn render_expression(&self, quote: char, double_type: &str) -> String {
let function = match self.aggregate {
Aggregate::Count => "COUNT",
Aggregate::Sum => "SUM",
Aggregate::Avg => "AVG",
Aggregate::Min => "MIN",
Aggregate::Max => "MAX",
};
let operand = self.field.as_ref().map_or_else(
|| "*".to_string(),
|field| quote_qualified(field.as_str(), quote),
);
let aggregate = format!("{function}({operand})");
if self.cast_double {
format!("CAST({aggregate} AS {double_type})")
} else {
aggregate
}
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
#[non_exhaustive]
pub enum ExprOperand {
Column(FieldRef),
Int(i64),
Text(String),
}
impl ExprOperand {
pub fn column(field: &FieldRef) -> Self {
Self::Column(field.clone())
}
pub fn int(value: i64) -> Self {
Self::Int(value)
}
pub fn text(value: impl Into<String>) -> Self {
Self::Text(value.into())
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
#[non_exhaustive]
pub enum SqlExprParam {
Int(i64),
Numeric(String),
Bool(bool),
Text(String),
}
impl SqlExprParam {
pub fn text(value: impl Into<String>) -> Self {
Self::Text(value.into())
}
fn infer(value: &str) -> Self {
match value {
"true" => Self::Bool(true),
"false" => Self::Bool(false),
_ => {
if let Ok(int) = value.parse::<i64>() {
Self::Int(int)
} else if value.parse::<f64>().is_ok_and(f64::is_finite) {
Self::Numeric(value.to_string())
} else {
Self::Text(value.to_string())
}
}
}
}
}
impl From<&str> for SqlExprParam {
fn from(value: &str) -> Self {
Self::infer(value)
}
}
impl From<String> for SqlExprParam {
fn from(value: String) -> Self {
Self::infer(&value)
}
}
impl From<&String> for SqlExprParam {
fn from(value: &String) -> Self {
Self::infer(value)
}
}
impl From<i64> for SqlExprParam {
fn from(value: i64) -> Self {
Self::Int(value)
}
}
impl From<f64> for SqlExprParam {
fn from(value: f64) -> Self {
if value.is_finite() {
Self::Numeric(value.to_string())
} else {
Self::Text(value.to_string())
}
}
}
impl From<bool> for SqlExprParam {
fn from(value: bool) -> Self {
Self::Bool(value)
}
}
#[cfg(any(feature = "mysql", feature = "postgres"))]
#[derive(Debug, Clone, PartialEq, Eq)]
pub(crate) struct RenderedSqlExpr {
pub(crate) sql: String,
pub(crate) params: Vec<SqlExprParam>,
}
#[cfg(feature = "mysql")]
impl From<SqlExprParam> for crate::mysql::SqlValue {
fn from(param: SqlExprParam) -> Self {
match param {
SqlExprParam::Int(value) => Self::Int(value),
SqlExprParam::Numeric(value) => match value.parse::<f64>() {
Ok(number) => Self::Float(number),
Err(_) => Self::String(value),
},
SqlExprParam::Bool(value) => Self::Bool(value),
SqlExprParam::Text(value) => Self::String(value),
}
}
}
#[cfg(feature = "postgres")]
impl From<SqlExprParam> for crate::postgres::SqlValue {
fn from(param: SqlExprParam) -> Self {
match param {
SqlExprParam::Int(value) => Self::Int(value),
SqlExprParam::Numeric(value) => match value.parse::<bigdecimal::BigDecimal>() {
Ok(number) => Self::Decimal(number),
Err(_) => Self::String(value),
},
SqlExprParam::Bool(value) => Self::Bool(value),
SqlExprParam::Text(value) => Self::String(value),
}
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
#[non_exhaustive]
pub enum SqlExpr {
UnixTimestamp,
UnixTimestampAdd(i64),
Now,
Coalesce {
field: FieldRef,
fallback: SqlExprParam,
},
Greatest(Vec<ExprOperand>),
Least(Vec<ExprOperand>),
NullIf {
field: FieldRef,
value: SqlExprParam,
},
Strpos { field: FieldRef, needle: String },
Lpad {
field: FieldRef,
length: i64,
fill: String,
},
Rpad {
field: FieldRef,
length: i64,
fill: String,
},
ToChar { field: FieldRef, format: String },
}
impl SqlExpr {
pub fn unix_timestamp() -> Self {
Self::UnixTimestamp
}
pub fn unix_timestamp_add(seconds: i64) -> Self {
Self::UnixTimestampAdd(seconds)
}
pub fn now() -> Self {
Self::Now
}
pub fn coalesce(field: &FieldRef, fallback: impl Into<SqlExprParam>) -> Self {
Self::Coalesce {
field: field.clone(),
fallback: fallback.into(),
}
}
pub fn greatest(operands: Vec<ExprOperand>) -> Self {
debug_assert!(operands.len() >= 2, "GREATEST 至少需要两个操作数");
Self::Greatest(operands)
}
pub fn least(operands: Vec<ExprOperand>) -> Self {
debug_assert!(operands.len() >= 2, "LEAST 至少需要两个操作数");
Self::Least(operands)
}
pub fn nullif(field: &FieldRef, value: impl Into<SqlExprParam>) -> Self {
Self::NullIf {
field: field.clone(),
value: value.into(),
}
}
pub fn strpos(field: &FieldRef, needle: impl Into<String>) -> Self {
Self::Strpos {
field: field.clone(),
needle: needle.into(),
}
}
pub fn lpad(field: &FieldRef, length: i64, fill: impl Into<String>) -> Self {
Self::Lpad {
field: field.clone(),
length,
fill: fill.into(),
}
}
pub fn rpad(field: &FieldRef, length: i64, fill: impl Into<String>) -> Self {
Self::Rpad {
field: field.clone(),
length,
fill: fill.into(),
}
}
pub fn to_char(field: &FieldRef, format: impl Into<String>) -> Self {
Self::ToChar {
field: field.clone(),
format: format.into(),
}
}
#[cfg(feature = "mysql")]
pub(crate) fn mysql_render(&self) -> Result<RenderedSqlExpr, DbError> {
let mut params = Vec::new();
let sql = match self {
Self::UnixTimestamp => "UNIX_TIMESTAMP()".to_string(),
Self::UnixTimestampAdd(seconds) => {
params.push(SqlExprParam::Int(*seconds));
"UNIX_TIMESTAMP() + ?".to_string()
}
Self::Now => "CURRENT_TIMESTAMP".to_string(),
Self::Coalesce { field, fallback } => {
params.push(fallback.clone());
format!("COALESCE({}, ?)", field.mysql_quoted())
}
Self::Greatest(operands) => mysql_function("GREATEST", operands, &mut params),
Self::Least(operands) => mysql_function("LEAST", operands, &mut params),
Self::NullIf { field, value } => {
params.push(value.clone());
format!("NULLIF({}, ?)", field.mysql_quoted())
}
Self::Strpos { field, needle } => {
params.push(SqlExprParam::Text(needle.clone()));
format!("LOCATE(?, {})", field.mysql_quoted())
}
Self::Lpad {
field,
length,
fill,
} => {
params.push(SqlExprParam::Int(*length));
params.push(SqlExprParam::Text(fill.clone()));
format!("LPAD({}, ?, ?)", field.mysql_quoted())
}
Self::Rpad {
field,
length,
fill,
} => {
params.push(SqlExprParam::Int(*length));
params.push(SqlExprParam::Text(fill.clone()));
format!("RPAD({}, ?, ?)", field.mysql_quoted())
}
Self::ToChar { .. } => {
return Err(DbError::UnsupportedOperator(
"to_char 仅支持 PostgreSQL:PG 格式串与 MySQL DATE_FORMAT 记号不等价"
.to_string(),
));
}
};
Ok(RenderedSqlExpr { sql, params })
}
#[cfg(feature = "postgres")]
pub(crate) fn postgres_render(&self, placeholder: usize) -> RenderedSqlExpr {
let mut params = Vec::new();
let sql = match self {
Self::UnixTimestamp => "EXTRACT(EPOCH FROM now())".to_string(),
Self::UnixTimestampAdd(seconds) => {
params.push(SqlExprParam::Int(*seconds));
format!("EXTRACT(EPOCH FROM now()) + ${placeholder}")
}
Self::Now => "now()".to_string(),
Self::Coalesce { field, fallback } => {
params.push(fallback.clone());
format!("COALESCE({}, ${placeholder})", field.postgres_quoted())
}
Self::Greatest(operands) => {
postgres_function("GREATEST", operands, placeholder, &mut params)
}
Self::Least(operands) => postgres_function("LEAST", operands, placeholder, &mut params),
Self::NullIf { field, value } => {
params.push(value.clone());
format!("NULLIF({}, ${placeholder})", field.postgres_quoted())
}
Self::Strpos { field, needle } => {
params.push(SqlExprParam::Text(needle.clone()));
format!("strpos({}, ${placeholder})", field.postgres_quoted())
}
Self::Lpad {
field,
length,
fill,
} => {
params.push(SqlExprParam::Int(*length));
params.push(SqlExprParam::Text(fill.clone()));
format!(
"lpad({}, ${}::integer, ${})",
field.postgres_quoted(),
placeholder,
placeholder + 1
)
}
Self::Rpad {
field,
length,
fill,
} => {
params.push(SqlExprParam::Int(*length));
params.push(SqlExprParam::Text(fill.clone()));
format!(
"rpad({}, ${}::integer, ${})",
field.postgres_quoted(),
placeholder,
placeholder + 1
)
}
Self::ToChar { field, format } => {
params.push(SqlExprParam::Text(format.clone()));
format!("to_char({}, ${placeholder})", field.postgres_quoted())
}
};
RenderedSqlExpr { sql, params }
}
}
#[cfg(feature = "mysql")]
fn mysql_function(name: &str, operands: &[ExprOperand], params: &mut Vec<SqlExprParam>) -> String {
let rendered = operands
.iter()
.map(|operand| match operand {
ExprOperand::Column(field) => field.mysql_quoted().to_string(),
ExprOperand::Int(value) => {
params.push(SqlExprParam::Int(*value));
"?".to_string()
}
ExprOperand::Text(value) => {
params.push(SqlExprParam::Text(value.clone()));
"?".to_string()
}
})
.collect::<Vec<_>>();
format!("{name}({})", rendered.join(", "))
}
#[cfg(feature = "postgres")]
fn postgres_function(
name: &str,
operands: &[ExprOperand],
placeholder: usize,
params: &mut Vec<SqlExprParam>,
) -> String {
let rendered = operands
.iter()
.map(|operand| match operand {
ExprOperand::Column(field) => field.postgres_quoted().to_string(),
ExprOperand::Int(value) => {
let index = placeholder + params.len();
params.push(SqlExprParam::Int(*value));
format!("${index}")
}
ExprOperand::Text(value) => {
let index = placeholder + params.len();
params.push(SqlExprParam::Text(value.clone()));
format!("${index}")
}
})
.collect::<Vec<_>>();
format!("{name}({})", rendered.join(", "))
}
#[cfg(any(feature = "mysql", feature = "postgres"))]
fn quote_qualified(value: &str, quote: char) -> String {
value
.split('.')
.map(|segment| format!("{quote}{segment}{quote}"))
.collect::<Vec<_>>()
.join(".")
}
#[cfg(any(feature = "mysql", feature = "postgres"))]
impl SortOrder {
pub(crate) const fn is_ascending(self) -> bool {
matches!(self, Self::Asc)
}
}
#[cfg(test)]
#[allow(clippy::expect_used)]
mod tests {
use super::*;
#[test]
fn controlled_references_validate_once_and_render_both_dialects() {
let table = TableRef::new("org_user").expect("合法表名应创建成功");
let field = FieldRef::new("org_user.username").expect("合法限定字段应创建成功");
assert_eq!(table.as_str(), "org_user");
assert_eq!(field.as_str(), "org_user.username");
#[cfg(feature = "mysql")]
assert_eq!(field.mysql_quoted(), "`org_user`.`username`");
#[cfg(feature = "postgres")]
assert_eq!(field.postgres_quoted(), "\"org_user\".\"username\"");
}
#[test]
fn controlled_references_reject_expressions_and_injection_payloads() {
for invalid in ["", "users;DROP", "COUNT(*)", "a.b.c.d", "a..b"] {
assert!(TableRef::new(invalid).is_err(), "表引用应拒绝 {invalid:?}");
assert!(
FieldRef::new(invalid).is_err(),
"字段引用应拒绝 {invalid:?}"
);
}
#[cfg(not(feature = "postgres"))]
for invalid in ["用户", "a$b", "a\"b"] {
assert!(TableRef::new(invalid).is_err(), "表引用应拒绝 {invalid:?}");
assert!(
FieldRef::new(invalid).is_err(),
"字段引用应拒绝 {invalid:?}"
);
}
assert!(FieldRef::new("users.id.extra").is_ok());
assert!(TableRef::new("users.id.extra").is_err());
}
#[cfg(feature = "postgres")]
#[test]
#[allow(clippy::expect_used)]
fn postgres_references_accept_extended_identifiers_and_cap_63_bytes() {
let table = TableRef::new("订单").expect("中文表名应合法");
assert_eq!(table.as_str(), "订单");
let field = FieldRef::new("金额").expect("中文字段名应合法");
assert_eq!(field.as_str(), "金额");
assert_eq!(field.postgres_quoted(), "\"金额\"");
let qualified = FieldRef::new("订单.金额").expect("中文限定字段名应合法");
assert_eq!(qualified.postgres_quoted(), "\"订单\".\"金额\"");
let dollar = FieldRef::new("a$b").expect("非首字符 `$` 应合法");
assert_eq!(dollar.postgres_quoted(), "\"a$b\"");
let quoted = FieldRef::new("a\"b").expect("内嵌双引号应合法");
assert_eq!(quoted.postgres_quoted(), "\"a\"\"b\"");
assert!(TableRef::new("a".repeat(63)).is_ok());
assert!(FieldRef::new("a".repeat(63)).is_ok());
assert!(TableRef::new("用".repeat(21)).is_ok());
for oversized in ["a".repeat(64), "用".repeat(22)] {
let error = TableRef::new(oversized.clone()).expect_err("超长表名必须拒绝");
assert!(matches!(error, DbError::InvalidArgument(_)), "{error:?}");
assert!(FieldRef::new(oversized).is_err(), "超长字段名必须拒绝");
}
}
#[test]
fn qualified_schema_references_render_every_segment() {
let table = TableRef::new("audit.users").expect("schema.table 应合法");
let field = FieldRef::new("audit.users.id").expect("schema.table.column 应合法");
assert_eq!(table.as_str(), "audit.users");
assert_eq!(field.as_str(), "audit.users.id");
#[cfg(feature = "mysql")]
assert_eq!(field.mysql_quoted(), "`audit`.`users`.`id`");
#[cfg(feature = "postgres")]
assert_eq!(field.postgres_quoted(), "\"audit\".\"users\".\"id\"");
}
#[test]
fn server_expressions_render_fixed_fragments_with_bound_dynamic_parts() {
#[cfg(feature = "mysql")]
{
let rendered = SqlExpr::unix_timestamp()
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "UNIX_TIMESTAMP()");
assert!(rendered.params.is_empty());
let rendered = SqlExpr::unix_timestamp_add(900)
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "UNIX_TIMESTAMP() + ?");
assert_eq!(rendered.params, vec![SqlExprParam::Int(900)]);
let rendered = SqlExpr::unix_timestamp_add(-30)
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "UNIX_TIMESTAMP() + ?");
assert_eq!(rendered.params, vec![SqlExprParam::Int(-30)]);
}
#[cfg(feature = "postgres")]
{
let rendered = SqlExpr::unix_timestamp().postgres_render(1);
assert_eq!(rendered.sql, "EXTRACT(EPOCH FROM now())");
assert!(rendered.params.is_empty());
let rendered = SqlExpr::unix_timestamp_add(900).postgres_render(3);
assert_eq!(rendered.sql, "EXTRACT(EPOCH FROM now()) + $3");
assert_eq!(rendered.params, vec![SqlExprParam::Int(900)]);
let rendered = SqlExpr::now().postgres_render(1);
assert_eq!(rendered.sql, "now()");
assert!(rendered.params.is_empty());
}
}
#[test]
fn string_expressions_render_dialect_specific_sql_with_bound_operands() {
#[cfg(any(feature = "mysql", feature = "postgres"))]
let field = FieldRef::new("t.c").expect("限定字段名应合法");
#[cfg(feature = "mysql")]
{
let rendered = SqlExpr::coalesce(&field, "x")
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "COALESCE(`t`.`c`, ?)");
assert_eq!(rendered.params, vec![SqlExprParam::Text("x".into())]);
let operands = vec![
ExprOperand::column(&field),
ExprOperand::int(7),
ExprOperand::text("z"),
];
let rendered = SqlExpr::greatest(operands)
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "GREATEST(`t`.`c`, ?, ?)");
assert_eq!(
rendered.params,
vec![SqlExprParam::Int(7), SqlExprParam::Text("z".into())]
);
let operands = vec![ExprOperand::column(&field), ExprOperand::int(1)];
let rendered = SqlExpr::least(operands)
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "LEAST(`t`.`c`, ?)");
assert_eq!(rendered.params, vec![SqlExprParam::Int(1)]);
let rendered = SqlExpr::nullif(&field, "")
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "NULLIF(`t`.`c`, ?)");
assert_eq!(rendered.params, vec![SqlExprParam::Text(String::new())]);
let rendered = SqlExpr::strpos(&field, "needle")
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "LOCATE(?, `t`.`c`)");
assert_eq!(rendered.params, vec![SqlExprParam::Text("needle".into())]);
let rendered = SqlExpr::lpad(&field, 5, "*")
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "LPAD(`t`.`c`, ?, ?)");
assert_eq!(
rendered.params,
vec![SqlExprParam::Int(5), SqlExprParam::Text("*".into())]
);
let rendered = SqlExpr::rpad(&field, 6, "-")
.mysql_render()
.expect("MySQL 表达式渲染应成功");
assert_eq!(rendered.sql, "RPAD(`t`.`c`, ?, ?)");
assert_eq!(
rendered.params,
vec![SqlExprParam::Int(6), SqlExprParam::Text("-".into())]
);
let error = SqlExpr::to_char(&field, "%Y-%m")
.mysql_render()
.expect_err("MySQL 不支持 to_char");
assert!(
matches!(error, DbError::UnsupportedOperator(_)),
"{error:?}"
);
}
#[cfg(feature = "postgres")]
{
let rendered = SqlExpr::coalesce(&field, "x").postgres_render(1);
assert_eq!(rendered.sql, "COALESCE(\"t\".\"c\", $1)");
assert_eq!(rendered.params, vec![SqlExprParam::Text("x".into())]);
let operands = vec![
ExprOperand::column(&field),
ExprOperand::int(7),
ExprOperand::text("z"),
];
let rendered = SqlExpr::greatest(operands).postgres_render(1);
assert_eq!(rendered.sql, "GREATEST(\"t\".\"c\", $1, $2)");
assert_eq!(
rendered.params,
vec![SqlExprParam::Int(7), SqlExprParam::Text("z".into())]
);
let operands = vec![ExprOperand::column(&field), ExprOperand::int(1)];
let rendered = SqlExpr::least(operands).postgres_render(2);
assert_eq!(rendered.sql, "LEAST(\"t\".\"c\", $2)");
assert_eq!(rendered.params, vec![SqlExprParam::Int(1)]);
let rendered = SqlExpr::nullif(&field, "").postgres_render(1);
assert_eq!(rendered.sql, "NULLIF(\"t\".\"c\", $1)");
assert_eq!(rendered.params, vec![SqlExprParam::Text(String::new())]);
let rendered = SqlExpr::strpos(&field, "needle").postgres_render(1);
assert_eq!(rendered.sql, "strpos(\"t\".\"c\", $1)");
assert_eq!(rendered.params, vec![SqlExprParam::Text("needle".into())]);
let rendered = SqlExpr::lpad(&field, 5, "*").postgres_render(1);
assert_eq!(rendered.sql, "lpad(\"t\".\"c\", $1::integer, $2)");
assert_eq!(
rendered.params,
vec![SqlExprParam::Int(5), SqlExprParam::Text("*".into())]
);
let rendered = SqlExpr::rpad(&field, 6, "-").postgres_render(1);
assert_eq!(rendered.sql, "rpad(\"t\".\"c\", $1::integer, $2)");
assert_eq!(
rendered.params,
vec![SqlExprParam::Int(6), SqlExprParam::Text("-".into())]
);
let rendered = SqlExpr::to_char(&field, "YYYY-MM").postgres_render(4);
assert_eq!(rendered.sql, "to_char(\"t\".\"c\", $4)");
assert_eq!(rendered.params, vec![SqlExprParam::Text("YYYY-MM".into())]);
}
}
#[test]
fn expression_literals_infer_binding_types() {
assert_eq!(SqlExprParam::from("0"), SqlExprParam::Int(0));
assert_eq!(SqlExprParam::from("-7"), SqlExprParam::Int(-7));
assert_eq!(
SqlExprParam::from("0.00"),
SqlExprParam::Numeric("0.00".into())
);
assert_eq!(SqlExprParam::from("true"), SqlExprParam::Bool(true));
assert_eq!(SqlExprParam::from("false"), SqlExprParam::Bool(false));
assert_eq!(SqlExprParam::from("x"), SqlExprParam::Text("x".into()));
assert_eq!(
SqlExprParam::text("0"),
SqlExprParam::Text("0".into()),
"text() 必须绕过数值推断"
);
#[cfg(feature = "postgres")]
{
let field = FieldRef::new("t.c").expect("限定字段名应合法");
let rendered = SqlExpr::coalesce(&field, "0").postgres_render(1);
assert_eq!(rendered.sql, "COALESCE(\"t\".\"c\", $1)");
assert_eq!(rendered.params, vec![SqlExprParam::Int(0)]);
let rendered = SqlExpr::nullif(&field, "7").postgres_render(2);
assert_eq!(rendered.sql, "NULLIF(\"t\".\"c\", $2)");
assert_eq!(rendered.params, vec![SqlExprParam::Int(7)]);
let rendered = SqlExpr::coalesce(&field, "0.00").postgres_render(1);
assert_eq!(rendered.params, vec![SqlExprParam::Numeric("0.00".into())]);
let rendered = SqlExpr::coalesce(&field, SqlExprParam::text("0")).postgres_render(1);
assert_eq!(rendered.params, vec![SqlExprParam::Text("0".into())]);
}
}
#[cfg(feature = "mysql")]
#[tokio::test]
async fn mysql_query_builder_consumes_controlled_references_without_value_interpolation() {
let pool = sqlx::mysql::MySqlPoolOptions::new()
.connect_lazy("mysql://root:test@127.0.0.1:3306/test")
.expect("lazy MySQL pool 应创建成功");
let database = crate::Database::from_pool(pool, crate::DatabaseConfig::default())
.expect("默认 MySQL 配置应有效");
let table = TableRef::new("org_user").expect("固定表名有效");
let username = FieldRef::new("username").expect("固定字段名有效");
let sql = database
.table(&table)
.field(&username)
.where_and(&username, CompareOp::Eq, "alice")
.expect("固定受控条件应合法")
.order(&username, SortOrder::Asc)
.try_to_sql()
.expect("受控 MySQL 查询应生成 SQL");
assert!(sql.contains("`org_user`"));
assert!(sql.contains("`username`"));
assert!(sql.contains('?'));
assert!(!sql.contains("alice"));
}
#[cfg(feature = "postgres")]
#[tokio::test]
async fn postgres_query_builder_consumes_controlled_references_without_value_interpolation() {
let pool = sqlx::postgres::PgPoolOptions::new()
.connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")
.expect("lazy PostgreSQL pool 应创建成功");
let database = crate::PgDatabase::from_pool(pool, crate::PgDatabaseConfig::default())
.expect("默认 PostgreSQL 配置应有效");
let table = TableRef::new("org_user").expect("固定表名有效");
let username = FieldRef::new("username").expect("固定字段名有效");
let sql = database
.table(&table)
.field(&username)
.where_and(&username, CompareOp::Eq, "alice")
.expect("固定受控条件应合法")
.order(&username, SortOrder::Desc)
.try_to_sql()
.expect("受控 PostgreSQL 查询应生成 SQL");
assert!(sql.contains("\"org_user\""));
assert!(sql.contains("\"username\""));
assert!(sql.contains("$1"));
assert!(!sql.contains("alice"));
}
}