use crate::mysql::condition::{Condition, SqlValue};
use crate::mysql::field::{FieldType, JoinClause, OrderClause};
use sqlx::mysql::MySqlPool;
use std::collections::HashMap;
macro_rules! bind_value_match {
($query:expr, $param:expr) => {
match $param {
SqlValue::Null => $query.bind(Option::<i32>::None),
SqlValue::Bool(b) => $query.bind(*b),
SqlValue::Int(i) => $query.bind(*i),
SqlValue::Float(f) => $query.bind(*f),
SqlValue::String(s) => $query.bind(s.clone()),
SqlValue::Bytes(b) => $query.bind(b.clone()),
SqlValue::Json(j) => $query.bind(j.to_string()),
SqlValue::DateTime(dt) => $query.bind(*dt),
SqlValue::Timestamp(ts) => $query.bind(*ts),
}
};
}
const INSERT_BATCH_SIZE: usize = 500;
const UPDATE_BATCH_SIZE: usize = 1000;
#[allow(dead_code)]
pub(crate) struct SqlGenerator {
sql: String,
params: Vec<SqlValue>,
}
#[allow(dead_code)]
impl SqlGenerator {
pub(crate) fn new() -> Self {
Self {
sql: String::with_capacity(256),
params: Vec::with_capacity(8),
}
}
pub(crate) fn get_sql(&self) -> &str {
&self.sql
}
pub(crate) fn get_params(&self) -> &[SqlValue] {
&self.params
}
fn append(&mut self, fragment: &str) {
self.sql.push_str(fragment);
}
fn add_param(&mut self, param: SqlValue) {
self.params.push(param);
}
fn append_condition(&mut self, condition: Condition) -> Result<(), crate::error::DbError> {
let rendered = crate::mysql::condition::render_condition_checked(condition)?;
self.sql.push_str(&rendered.sql);
self.params.extend(rendered.params);
Ok(())
}
fn clear(&mut self) {
self.sql.clear();
self.params.clear();
}
#[cfg(test)]
pub(crate) fn clear_for_test(&mut self) {
self.clear();
}
#[cfg(test)]
pub(crate) fn sql_capacity(&self) -> usize {
self.sql.capacity()
}
#[cfg(test)]
pub(crate) fn params_capacity(&self) -> usize {
self.params.capacity()
}
fn build_select(&mut self, builder: &QueryBuilder) -> Result<(), crate::error::DbError> {
self.clear();
self.build_compound_select(builder)
}
fn build_compound_select(
&mut self,
builder: &QueryBuilder,
) -> Result<(), crate::error::DbError> {
self.append("SELECT ");
if builder.distinct {
self.append("DISTINCT ");
}
if builder.fields.is_empty() {
self.append("*");
} else {
self.append(&builder.fields.join(", "));
}
self.append(" FROM ");
self.append(&super::identifier::quote_identifier(&builder.table)?);
if !builder.joins.is_empty() {
self.build_joins(&builder.joins);
}
if !builder.conditions.is_empty() {
self.build_where(&builder.conditions)?;
}
if !builder.group_by.is_empty() {
self.build_group_by(&builder.group_by);
}
if !builder.having_clause.is_empty() {
if builder.group_by.is_empty() {
return Err(crate::error::DbError::MissingGroupByClause);
}
self.build_having(&builder.having_clause)?;
}
for (operator, branch) in &builder.unions {
match operator {
UnionOperator::Distinct => self.append(" UNION "),
UnionOperator::All => self.append(" UNION ALL "),
}
self.append("(");
self.build_compound_select(branch)?;
self.append(")");
}
if !builder.order_by.is_empty() {
self.build_order_by(&builder.order_by);
}
if let Some(limit) = builder.limit {
self.append(" LIMIT ");
self.append(&limit.to_string());
}
if let Some(offset) = builder.offset {
self.append(" OFFSET ");
self.append(&offset.to_string());
}
Ok(())
}
fn build_where(&mut self, conditions: &[Condition]) -> Result<(), crate::error::DbError> {
if conditions.is_empty() {
return Ok(());
}
self.append(" WHERE ");
if conditions.len() == 1 {
self.append_condition(conditions[0].clone())?;
} else {
self.sql.push('(');
for (i, cond) in conditions.iter().enumerate() {
if i > 0 {
self.sql.push_str(" AND ");
}
self.append_condition(cond.clone())?;
}
self.sql.push(')');
}
Ok(())
}
fn build_joins(&mut self, joins: &[JoinClause]) {
use crate::mysql::field::JoinType;
for join in joins {
let join_type_str = match join.join_type {
JoinType::Inner => " INNER JOIN ",
JoinType::Left => " LEFT JOIN ",
JoinType::Right => " RIGHT JOIN ",
};
self.append(join_type_str);
self.append(&join.table);
self.append(" ON ");
self.append(&join.on);
}
}
fn build_order_by(&mut self, orders: &[OrderClause]) {
if orders.is_empty() {
return;
}
self.append(" ORDER BY ");
for (i, order) in orders.iter().enumerate() {
if i > 0 {
self.sql.push_str(", ");
}
self.sql.push_str(&order.field);
self.sql.push(' ');
self.sql.push_str(if order.asc { "ASC" } else { "DESC" });
}
}
fn build_group_by(&mut self, groups: &[String]) {
if groups.is_empty() {
return;
}
self.append(" GROUP BY ");
self.append(&groups.join(", "));
}
fn build_having(&mut self, conditions: &[Condition]) -> Result<(), crate::error::DbError> {
self.append(" HAVING ");
if conditions.len() == 1 {
self.append_condition(conditions[0].clone())?;
} else {
self.sql.push('(');
for (i, cond) in conditions.iter().enumerate() {
if i > 0 {
self.sql.push_str(" AND ");
}
self.append_condition(cond.clone())?;
}
self.sql.push(')');
}
Ok(())
}
pub(crate) fn build_insert(
&mut self,
table: &str,
data: &serde_json::Value,
field_types: &HashMap<String, FieldType>,
) -> Result<(), crate::error::DbError> {
self.clear();
let obj = data.as_object().ok_or_else(|| {
crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
})?;
if obj.is_empty() {
return Err(crate::error::DbError::SerializationError(
"插入数据不能为空".to_string(),
));
}
let mut fields = Vec::with_capacity(obj.len());
let mut placeholders = Vec::with_capacity(obj.len());
for (key, value) in obj.iter() {
fields.push(super::identifier::quote_identifier(key)?);
placeholders.push("?".to_string());
let sql_value = self.json_value_to_sql_value(value, field_types.get(key))?;
self.add_param(sql_value);
}
self.append("INSERT INTO ");
self.append(&super::identifier::quote_identifier(table)?);
self.append(" (");
self.append(&fields.join(", "));
self.append(") VALUES (");
self.append(&placeholders.join(", "));
self.append(")");
Ok(())
}
pub(crate) fn build_insert_batch(
&mut self,
table: &str,
data_list: &[serde_json::Value],
field_types: &HashMap<String, FieldType>,
) -> Result<(), crate::error::DbError> {
self.clear();
if data_list.is_empty() {
return Err(crate::error::DbError::SerializationError(
"批量插入数据不能为空".to_string(),
));
}
let first_obj = data_list[0].as_object().ok_or_else(|| {
crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
})?;
if first_obj.is_empty() {
return Err(crate::error::DbError::SerializationError(
"插入数据不能为空".to_string(),
));
}
let fields: Vec<String> = first_obj.keys().cloned().collect();
for (idx, data) in data_list.iter().enumerate().skip(1) {
let obj = data.as_object().ok_or_else(|| {
crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
})?;
if obj.len() != fields.len() || !fields.iter().all(|f| obj.contains_key(f)) {
return Err(crate::error::DbError::InvalidArgument(format!(
"批量插入第 {idx} 条记录的列集与首条不一致"
)));
}
}
let quoted_fields: Result<Vec<String>, crate::error::DbError> = fields
.iter()
.map(|f| super::identifier::quote_identifier(f))
.collect();
let quoted_fields = quoted_fields?;
self.append("INSERT INTO ");
self.append(&super::identifier::quote_identifier(table)?);
self.append(" (");
self.append("ed_fields.join(", "));
self.append(") VALUES ");
for (record_idx, data) in data_list.iter().enumerate() {
if record_idx > 0 {
self.sql.push_str(", ");
}
let obj = data.as_object().ok_or_else(|| {
crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
})?;
self.sql.push('(');
for (field_idx, field) in fields.iter().enumerate() {
if field_idx > 0 {
self.sql.push_str(", ");
}
self.sql.push('?');
let value = obj.get(field).unwrap_or(&serde_json::Value::Null);
let sql_value = self.json_value_to_sql_value(value, field_types.get(field))?;
self.add_param(sql_value);
}
self.sql.push(')');
}
Ok(())
}
pub(crate) fn build_update(
&mut self,
table: &str,
data: &serde_json::Value,
field_types: &HashMap<String, FieldType>,
conditions: &[Condition],
) -> Result<(), crate::error::DbError> {
self.clear();
if conditions.is_empty() {
return Err(crate::error::DbError::MissingWhereClause);
}
let obj = data.as_object().ok_or_else(|| {
crate::error::DbError::SerializationError("更新数据必须是 JSON 对象".to_string())
})?;
if obj.is_empty() {
return Err(crate::error::DbError::SerializationError(
"更新数据不能为空".to_string(),
));
}
self.append("UPDATE ");
self.append(&super::identifier::quote_identifier(table)?);
self.append(" SET ");
let mut set_clauses = Vec::with_capacity(obj.len());
for (key, value) in obj.iter() {
set_clauses.push(format!("{} = ?", super::identifier::quote_identifier(key)?));
let sql_value = self.json_value_to_sql_value(value, field_types.get(key))?;
self.add_param(sql_value);
}
self.append(&set_clauses.join(", "));
self.build_where(conditions)?;
Ok(())
}
pub(crate) fn build_arithmetic_update(
&mut self,
table: &str,
field: &str,
operator: ArithmeticOperator,
amount: i64,
conditions: &[Condition],
) -> Result<(), crate::error::DbError> {
self.clear();
if conditions.is_empty() {
return Err(crate::error::DbError::MissingWhereClause);
}
let table = super::identifier::quote_identifier(table)?;
let field = super::identifier::quote_identifier(field)?;
self.sql.push_str("UPDATE ");
self.sql.push_str(&table);
self.sql.push_str(" SET ");
self.sql.push_str(&field);
self.sql.push_str(" = ");
self.sql.push_str(&field);
self.sql.push(' ');
self.sql.push_str(operator.as_sql());
self.sql.push_str(" ?");
self.params.push(SqlValue::Int(amount));
self.build_where(conditions)
}
pub(crate) fn build_delete(
&mut self,
table: &str,
conditions: &[Condition],
) -> Result<(), crate::error::DbError> {
self.clear();
if conditions.is_empty() {
return Err(crate::error::DbError::MissingWhereClause);
}
self.append("DELETE FROM ");
self.append(&super::identifier::quote_identifier(table)?);
self.build_where(conditions)?;
Ok(())
}
pub(crate) fn build_update_batch(
&mut self,
table: &str,
records: &[serde_json::Value],
id_field: &str,
field_types: &std::collections::HashMap<String, FieldType>,
) -> Result<(), crate::error::DbError> {
self.clear();
if records.is_empty() {
return Err(crate::error::DbError::SerializationError(
"批量更新数据不能为空".to_string(),
));
}
let first = records[0].as_object().ok_or_else(|| {
crate::error::DbError::SerializationError("更新数据必须是 JSON 对象".to_string())
})?;
let update_fields: Vec<String> = first
.keys()
.filter(|k| k.as_str() != id_field)
.cloned()
.collect();
if update_fields.is_empty() {
return Err(crate::error::DbError::SerializationError(
"没有可更新的字段".to_string(),
));
}
for (idx, record) in records.iter().enumerate() {
let obj = record.as_object().ok_or_else(|| {
crate::error::DbError::SerializationError("更新数据必须是 JSON 对象".to_string())
})?;
if !obj.contains_key(id_field) {
return Err(crate::error::DbError::InvalidArgument(format!(
"批量更新第 {idx} 条记录缺少主键字段 {id_field}"
)));
}
let non_id_len = obj.keys().filter(|k| k.as_str() != id_field).count();
if non_id_len != update_fields.len()
|| !update_fields.iter().all(|f| obj.contains_key(f))
{
return Err(crate::error::DbError::InvalidArgument(format!(
"批量更新第 {idx} 条记录的列集与首条不一致"
)));
}
}
let quoted_id = super::identifier::quote_identifier(id_field)?;
self.sql.push_str("UPDATE ");
self.sql
.push_str(&super::identifier::quote_identifier(table)?);
self.sql.push_str(" SET ");
for (field_idx, field) in update_fields.iter().enumerate() {
if field_idx > 0 {
self.sql.push_str(", ");
}
self.sql
.push_str(&super::identifier::quote_identifier(field)?);
self.sql.push_str(" = CASE ");
for record in records {
let id_val = record.get(id_field).unwrap_or(&serde_json::Value::Null);
let field_val = record
.get(field.as_str())
.unwrap_or(&serde_json::Value::Null);
let id_sql_val = self.json_value_to_sql_value(id_val, field_types.get(id_field))?;
let field_sql_val =
self.json_value_to_sql_value(field_val, field_types.get(field.as_str()))?;
self.sql.push_str("WHEN ");
self.sql.push_str("ed_id);
self.sql.push_str("=? THEN ? ");
self.params.push(id_sql_val);
self.params.push(field_sql_val);
}
self.sql.push_str("END");
}
self.sql.push_str(" WHERE ");
self.sql.push_str("ed_id);
self.sql.push_str(" IN (");
for (idx, record) in records.iter().enumerate() {
if idx > 0 {
self.sql.push(',');
}
self.sql.push('?');
let id_val = record.get(id_field).unwrap_or(&serde_json::Value::Null);
let id_sql_val = self.json_value_to_sql_value(id_val, field_types.get(id_field))?;
self.params.push(id_sql_val);
}
self.sql.push(')');
Ok(())
}
pub(crate) fn build_upsert(
&mut self,
table: &str,
data: &serde_json::Value,
field_types: &std::collections::HashMap<String, FieldType>,
) -> Result<(), crate::error::DbError> {
self.clear();
let obj = data.as_object().ok_or_else(|| {
crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
})?;
if obj.is_empty() {
return Err(crate::error::DbError::SerializationError(
"插入数据不能为空".to_string(),
));
}
let fields: Vec<String> = obj.keys().cloned().collect();
let placeholders: Vec<&str> = fields.iter().map(|_| "?").collect();
let quoted_fields: Result<Vec<String>, crate::error::DbError> = fields
.iter()
.map(|f| super::identifier::quote_identifier(f))
.collect();
let quoted_fields = quoted_fields?;
self.sql.push_str("INSERT INTO ");
self.sql
.push_str(&super::identifier::quote_identifier(table)?);
self.sql.push_str(" (");
self.sql.push_str("ed_fields.join(", "));
self.sql.push_str(") VALUES (");
self.sql.push_str(&placeholders.join(", "));
self.sql.push(')');
for field in &fields {
let val = obj.get(field.as_str()).unwrap_or(&serde_json::Value::Null);
self.add_param(self.json_value_to_sql_value(val, field_types.get(field.as_str()))?);
}
self.sql.push_str(" ON DUPLICATE KEY UPDATE ");
for (i, qf) in quoted_fields.iter().enumerate() {
if i > 0 {
self.sql.push_str(", ");
}
self.sql.push_str(qf);
self.sql.push_str("=VALUES(");
self.sql.push_str(qf);
self.sql.push(')');
}
Ok(())
}
fn json_value_to_sql_value(
&self,
value: &serde_json::Value,
field_type: Option<&FieldType>,
) -> Result<SqlValue, crate::error::DbError> {
use serde_json::Value;
if let Some(ft) = field_type {
match ft {
FieldType::Json => {
return Ok(SqlValue::Json(value.clone()));
}
FieldType::DateTime => {
if let Some(s) = value.as_str() {
let dt = chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S")
.map_err(|e| {
crate::error::DbError::TypeConversionError(format!(
"无法解析 DATETIME 字符串: {}",
e
))
})?;
return Ok(SqlValue::DateTime(dt));
} else if value.is_null() {
return Ok(SqlValue::Null);
} else {
return Err(crate::error::DbError::TypeConversionError(format!(
"DateTime 字段期望字符串,实得 {value}"
)));
}
}
FieldType::Timestamp => {
if let Some(i) = value.as_i64() {
return Ok(SqlValue::Timestamp(i));
} else if value.is_null() {
return Ok(SqlValue::Null);
} else {
return Err(crate::error::DbError::TypeConversionError(format!(
"Timestamp 字段期望整数,实得 {value}"
)));
}
}
FieldType::Decimal => {
const SAFE_INT: f64 = 9_007_199_254_740_992.0; if let Some(i) = value.as_i64() {
if (i.unsigned_abs() as f64) < SAFE_INT {
return Ok(SqlValue::Float(i as f64));
}
return Ok(SqlValue::String(i.to_string()));
}
if let Some(f) = value.as_f64() {
if f.is_finite() && f.abs() < SAFE_INT {
return Ok(SqlValue::Float(f));
}
}
if value.is_null() {
return Ok(SqlValue::Null);
}
if value.is_number() {
return Ok(SqlValue::String(value.to_string()));
}
if let Some(s) = value.as_str() {
return Ok(SqlValue::String(s.to_string()));
}
return Err(crate::error::DbError::TypeConversionError(format!(
"Decimal 字段期望数字或数字字符串,实得 {value}"
)));
}
FieldType::Blob => {
if let Some(s) = value.as_str() {
use base64::Engine;
if let Ok(bytes) = base64::engine::general_purpose::STANDARD.decode(s) {
return Ok(SqlValue::Bytes(bytes));
}
return Ok(SqlValue::Bytes(s.as_bytes().to_vec()));
} else if value.is_null() {
return Ok(SqlValue::Null);
} else {
return Err(crate::error::DbError::TypeConversionError(format!(
"Blob 字段期望字符串,实得 {value}"
)));
}
}
FieldType::Text => {
if let Some(s) = value.as_str() {
return Ok(SqlValue::String(s.to_string()));
} else if value.is_null() {
return Ok(SqlValue::Null);
} else {
return Err(crate::error::DbError::TypeConversionError(format!(
"Text 字段期望字符串,实得 {value}"
)));
}
}
FieldType::Standard => {
}
}
}
match value {
Value::Null => Ok(SqlValue::Null),
Value::Bool(b) => Ok(SqlValue::Bool(*b)),
Value::Number(n) => {
if let Some(i) = n.as_i64() {
Ok(SqlValue::Int(i))
} else if let Some(f) = n.as_f64() {
Ok(SqlValue::Float(f))
} else {
Err(crate::error::DbError::TypeConversionError(
"无法转换数字类型".to_string(),
))
}
}
Value::String(s) => Ok(SqlValue::String(s.clone())),
Value::Array(_) | Value::Object(_) => {
Ok(SqlValue::Json(value.clone()))
}
}
}
}
#[derive(Debug, Clone, Copy)]
enum UnionOperator {
Distinct,
All,
}
#[derive(Debug, Clone, Copy)]
pub(crate) enum ArithmeticOperator {
Add,
Subtract,
}
impl ArithmeticOperator {
const fn as_sql(self) -> &'static str {
match self {
Self::Add => "+",
Self::Subtract => "-",
}
}
}
enum QueryExecutor<'a> {
Pool(&'a MySqlPool),
Transaction(&'a mut crate::mysql::Transaction),
}
pub struct QueryBuilder<'a> {
executor: QueryExecutor<'a>,
table: String,
fields: Vec<String>,
#[allow(dead_code)]
conditions: Vec<Condition>,
#[allow(dead_code)]
joins: Vec<JoinClause>,
#[allow(dead_code)]
order_by: Vec<OrderClause>,
#[allow(dead_code)]
group_by: Vec<String>,
#[allow(dead_code)]
having_clause: Vec<Condition>,
limit: Option<u64>,
offset: Option<u64>,
distinct: bool,
unions: Vec<(UnionOperator, Box<QueryBuilder<'a>>)>,
field_types: HashMap<String, FieldType>,
#[allow(dead_code)]
enable_logging: bool,
}
impl<'a> QueryBuilder<'a> {
#[doc(hidden)]
pub fn from_pool(pool: &'a MySqlPool, table: &crate::TableRef) -> Self {
Self::new(pool, table.as_str(), false)
}
pub(crate) fn new(pool: &'a MySqlPool, table_name: &str, enable_logging: bool) -> Self {
Self {
executor: QueryExecutor::Pool(pool),
table: table_name.to_string(),
fields: Vec::new(),
conditions: Vec::new(),
joins: Vec::new(),
order_by: Vec::new(),
group_by: Vec::new(),
having_clause: Vec::new(),
limit: None,
offset: None,
distinct: false,
unions: Vec::new(),
field_types: HashMap::new(),
enable_logging,
}
}
pub(crate) fn new_transaction(
transaction: &'a mut crate::mysql::Transaction,
table_name: &str,
enable_logging: bool,
) -> Self {
Self {
executor: QueryExecutor::Transaction(transaction),
table: table_name.to_string(),
fields: Vec::new(),
conditions: Vec::new(),
joins: Vec::new(),
order_by: Vec::new(),
group_by: Vec::new(),
having_clause: Vec::new(),
limit: None,
offset: None,
distinct: false,
unions: Vec::new(),
field_types: HashMap::new(),
enable_logging,
}
}
pub fn field(mut self, field: &crate::FieldRef) -> Self {
self.fields.push(field.mysql_quoted().to_string());
self
}
pub fn expr(mut self, expression: crate::SelectExpr) -> Self {
self.fields.push(expression.mysql_sql());
self
}
pub fn fields(mut self, fields: &[&crate::FieldRef]) -> Self {
for field in fields {
self.fields.push(field.mysql_quoted().to_string());
}
self
}
pub fn json(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Json);
self
}
pub fn datetime(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::DateTime);
self
}
pub fn timestamp(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Timestamp);
self
}
pub fn decimal(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Decimal);
self
}
pub fn blob(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Blob);
self
}
pub fn text(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Text);
self
}
pub fn distinct(mut self) -> Self {
self.distinct = true;
self
}
pub fn union(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
self.add_union(other, UnionOperator::Distinct)
}
pub fn union_all(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
self.add_union(other, UnionOperator::All)
}
fn add_union(
mut self,
other: QueryBuilder<'a>,
operator: UnionOperator,
) -> Result<Self, crate::error::DbError> {
if self.fields.is_empty() || other.fields.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"UNION 各分支必须显式声明输出字段,不能使用未知列数的 *".to_string(),
));
}
if self.fields.len() != other.fields.len() {
return Err(crate::error::DbError::InvalidArgument(format!(
"UNION 输出列数不一致:左侧 {} 列,右侧 {} 列",
self.fields.len(),
other.fields.len()
)));
}
self.unions.push((operator, Box::new(other)));
Ok(self)
}
pub fn where_and<V>(mut self, field: &crate::FieldRef, op: crate::CompareOp, value: V) -> Self
where
V: Into<crate::mysql::condition::SqlValue>,
{
use crate::mysql::condition::{Condition, SqlValue};
let field = field.as_str().to_string();
let value = value.into();
let condition = match op {
crate::CompareOp::Eq => Condition::Eq(field, value),
crate::CompareOp::Ne => Condition::Ne(field, value),
crate::CompareOp::Gt => Condition::Gt(field, value),
crate::CompareOp::Lt => Condition::Lt(field, value),
crate::CompareOp::Gte => Condition::Gte(field, value),
crate::CompareOp::Lte => Condition::Lte(field, value),
crate::CompareOp::Like => Condition::Like(
field,
match value {
SqlValue::String(value) => value,
other => format!("{other:?}"),
},
),
};
self.conditions.push(condition);
self
}
pub fn where_or<V>(mut self, field: &crate::FieldRef, op: crate::CompareOp, value: V) -> Self
where
V: Into<crate::mysql::condition::SqlValue>,
{
use crate::mysql::condition::{Condition, SqlValue};
let field = field.as_str().to_string();
let value = value.into();
let condition = match op {
crate::CompareOp::Eq => Condition::Eq(field, value),
crate::CompareOp::Ne => Condition::Ne(field, value),
crate::CompareOp::Gt => Condition::Gt(field, value),
crate::CompareOp::Lt => Condition::Lt(field, value),
crate::CompareOp::Gte => Condition::Gte(field, value),
crate::CompareOp::Lte => Condition::Lte(field, value),
crate::CompareOp::Like => Condition::Like(
field,
match value {
SqlValue::String(value) => value,
other => format!("{other:?}"),
},
),
};
if !self.conditions.is_empty() {
let mut existing = std::mem::take(&mut self.conditions);
self.conditions.push(Condition::Or(vec![
if existing.len() == 1 {
existing.remove(0)
} else {
Condition::And(existing)
},
condition,
]));
} else {
self.conditions.push(condition);
}
self
}
pub fn where_in<V>(mut self, field: &crate::FieldRef, values: Vec<V>) -> Self
where
V: Into<crate::mysql::condition::SqlValue>,
{
use crate::mysql::condition::Condition;
let sql_values: Vec<_> = values.into_iter().map(|v| v.into()).collect();
self.conditions
.push(Condition::In(field.as_str().to_string(), sql_values));
self
}
pub fn where_exists(mut self, subquery: crate::mysql::Subquery) -> Self {
self.conditions.push(Condition::Exists(Box::new(subquery)));
self
}
pub fn where_not_exists(mut self, subquery: crate::mysql::Subquery) -> Self {
self.conditions
.push(Condition::NotExists(Box::new(subquery)));
self
}
pub fn where_in_subquery(
mut self,
field: &crate::FieldRef,
subquery: crate::mysql::Subquery,
) -> Self {
self.conditions.push(Condition::InSubquery(
field.as_str().to_string(),
Box::new(subquery),
));
self
}
pub fn where_between<V>(mut self, field: &crate::FieldRef, start: V, end: V) -> Self
where
V: Into<crate::mysql::condition::SqlValue>,
{
use crate::mysql::condition::Condition;
self.conditions.push(Condition::Between(
field.as_str().to_string(),
start.into(),
end.into(),
));
self
}
pub fn where_null(mut self, field: &crate::FieldRef) -> Self {
self.conditions
.push(Condition::IsNull(field.as_str().to_string()));
self
}
pub fn where_not_null(mut self, field: &crate::FieldRef) -> Self {
self.conditions
.push(Condition::IsNotNull(field.as_str().to_string()));
self
}
pub fn where_predicate(
mut self,
predicate: &crate::Predicate,
) -> Result<Self, crate::error::DbError> {
self.conditions.push(predicate_condition(predicate)?);
Ok(self)
}
pub fn having_cond<V>(mut self, field: &crate::FieldRef, op: crate::CompareOp, value: V) -> Self
where
V: Into<crate::mysql::condition::SqlValue>,
{
let field = field.as_str().to_string();
let value = value.into();
let condition = match op {
crate::CompareOp::Eq => Condition::Eq(field, value),
crate::CompareOp::Ne => Condition::Ne(field, value),
crate::CompareOp::Gt => Condition::Gt(field, value),
crate::CompareOp::Lt => Condition::Lt(field, value),
crate::CompareOp::Gte => Condition::Gte(field, value),
crate::CompareOp::Lte => Condition::Lte(field, value),
crate::CompareOp::Like => Condition::Like(
field,
match value {
crate::mysql::condition::SqlValue::String(value) => value,
other => format!("{other:?}"),
},
),
};
self.having_clause.push(condition);
self
}
pub fn join(
mut self,
table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
use crate::mysql::field::{JoinClause, JoinType};
self.joins.push(JoinClause {
join_type: JoinType::Inner,
table: format!("`{}`", table.as_str()),
on: format!("{} = {}", left.mysql_quoted(), right.mysql_quoted()),
});
self
}
pub fn left_join(
mut self,
table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
use crate::mysql::field::{JoinClause, JoinType};
self.joins.push(JoinClause {
join_type: JoinType::Left,
table: format!("`{}`", table.as_str()),
on: format!("{} = {}", left.mysql_quoted(), right.mysql_quoted()),
});
self
}
pub fn right_join(
mut self,
table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
use crate::mysql::field::{JoinClause, JoinType};
self.joins.push(JoinClause {
join_type: JoinType::Right,
table: format!("`{}`", table.as_str()),
on: format!("{} = {}", left.mysql_quoted(), right.mysql_quoted()),
});
self
}
pub fn order(mut self, field: &crate::FieldRef, order: crate::SortOrder) -> Self {
use crate::mysql::field::OrderClause;
self.order_by.push(OrderClause {
field: field.mysql_quoted().to_string(),
asc: order.is_ascending(),
});
self
}
pub fn group(mut self, field: &crate::FieldRef) -> Self {
self.group_by.push(field.mysql_quoted().to_string());
self
}
pub fn limit(mut self, limit: u64) -> Self {
self.limit = Some(limit);
self
}
pub fn offset(mut self, offset: u64) -> Self {
self.offset = Some(offset);
self
}
pub fn try_to_sql(&self) -> Result<String, crate::error::DbError> {
let mut generator = SqlGenerator::new();
generator.build_select(self)?;
Ok(generator.get_sql().to_string())
}
pub(crate) fn render_for_transaction(
&self,
lock: Option<crate::RowLock>,
) -> Result<(String, Vec<SqlValue>), crate::error::DbError> {
if lock.is_some()
&& (self.distinct
|| !self.group_by.is_empty()
|| !self.having_clause.is_empty()
|| !self.unions.is_empty())
{
return Err(crate::error::DbError::InvalidArgument(
"行锁不支持 DISTINCT、GROUP BY、HAVING 或 UNION 查询".to_string(),
));
}
let mut generator = SqlGenerator::new();
generator.build_select(self)?;
if let Some(lock) = lock {
generator.sql.push(' ');
generator.sql.push_str(lock.as_sql());
}
Ok((generator.sql, generator.params))
}
pub fn to_sql(&self) -> String {
match self.try_to_sql() {
Ok(sql) => sql,
Err(err) => {
if self.enable_logging {
log::warn!("生成 SELECT SQL 失败: {err}");
}
"/* SQL generation failed */".to_string()
}
}
}
pub async fn find<T>(mut self) -> Result<Option<T>, crate::error::DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::mysql::MySqlRow> + Send + Unpin,
{
self.limit = Some(1);
let mut generator = SqlGenerator::new();
generator.build_select(&self)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 find() 查询: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query_as::<_, T>(sql);
for param in params {
query = bind_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.fetch_optional(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.fetch_optional(&mut *connection).await
}
};
match result {
Ok(row) => {
if self.enable_logging {
if row.is_some() {
log::debug!("find() 查询成功,返回 1 条记录");
} else {
log::debug!("find() 查询成功,未找到匹配记录");
}
}
Ok(row)
}
Err(e) => {
log::error!("find() 查询失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
pub async fn select<T>(self) -> Result<Vec<T>, crate::error::DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::mysql::MySqlRow> + Send + Unpin,
{
let mut generator = SqlGenerator::new();
generator.build_select(&self)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 select() 查询: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query_as::<_, T>(sql);
for param in params {
query = bind_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.fetch_all(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.fetch_all(&mut *connection).await
}
};
match result {
Ok(rows) => {
if self.enable_logging {
log::debug!("select() 查询成功,返回 {} 条记录", rows.len());
}
Ok(rows)
}
Err(e) => {
log::error!("select() 查询失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
async fn fetch_scalar<C>(
mut self,
select_expr: &str,
) -> Result<Option<C>, crate::error::DbError>
where
C: for<'r> sqlx::Decode<'r, sqlx::MySql> + sqlx::Type<sqlx::MySql> + Send + Unpin,
{
self.fields.clear();
self.fields.push(select_expr.to_string());
self.limit = Some(1);
let mut generator = SqlGenerator::new();
generator.build_select(&self)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行标量查询: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query_scalar::<_, C>(sql);
for param in params {
query = bind_scalar_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.fetch_optional(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.fetch_optional(&mut *connection).await
}
};
match result {
Ok(value) => Ok(value),
Err(e) => {
log::error!("标量查询失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
pub async fn value<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
where
T: for<'r> sqlx::Decode<'r, sqlx::MySql> + sqlx::Type<sqlx::MySql> + Send + Unpin,
{
if self.enable_logging {
log::debug!("执行 value() 查询,字段: {}", field.as_str());
}
let field = super::identifier::quote_identifier(field.as_str())?;
self.fetch_scalar::<T>(&field).await
}
pub async fn count(self) -> Result<i64, crate::error::DbError> {
if self.enable_logging {
log::debug!("执行 count() 查询");
}
let result = self.fetch_scalar::<i64>("COUNT(*)").await?;
Ok(result.unwrap_or(0))
}
pub async fn sum(self, field: &crate::FieldRef) -> Result<Option<f64>, crate::error::DbError> {
if self.enable_logging {
log::debug!("执行 sum() 查询,字段: {}", field.as_str());
}
let quoted_field = super::identifier::quote_identifier(field.as_str())?;
let sum_expr = format!("CAST(SUM({quoted_field}) AS DOUBLE)");
self.fetch_scalar::<Option<f64>>(&sum_expr)
.await
.map(Option::flatten)
}
pub async fn avg(self, field: &crate::FieldRef) -> Result<Option<f64>, crate::error::DbError> {
if self.enable_logging {
log::debug!("执行 avg() 查询,字段: {}", field.as_str());
}
let quoted_field = super::identifier::quote_identifier(field.as_str())?;
let avg_expr = format!("CAST(AVG({quoted_field}) AS DOUBLE)");
self.fetch_scalar::<Option<f64>>(&avg_expr)
.await
.map(Option::flatten)
}
pub async fn min<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
where
T: for<'r> sqlx::Decode<'r, sqlx::MySql> + sqlx::Type<sqlx::MySql> + Send + Unpin,
{
if self.enable_logging {
log::debug!("执行 min() 查询,字段: {}", field.as_str());
}
let quoted_field = super::identifier::quote_identifier(field.as_str())?;
let min_expr = format!("MIN({quoted_field})");
self.fetch_scalar::<Option<T>>(&min_expr)
.await
.map(Option::flatten)
}
pub async fn max<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
where
T: for<'r> sqlx::Decode<'r, sqlx::MySql> + sqlx::Type<sqlx::MySql> + Send + Unpin,
{
if self.enable_logging {
log::debug!("执行 max() 查询,字段: {}", field.as_str());
}
let quoted_field = super::identifier::quote_identifier(field.as_str())?;
let max_expr = format!("MAX({quoted_field})");
self.fetch_scalar::<Option<T>>(&max_expr)
.await
.map(Option::flatten)
}
pub async fn insert<T>(self, data: &T) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
if self.enable_logging {
log::debug!("执行 insert() 操作,表: {}", self.table);
}
let json_data = serde_json::to_value(data).map_err(|e| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
})?;
let mut generator = SqlGenerator::new();
generator.build_insert(&self.table, &json_data, &self.field_types)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 insert() SQL: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query(sql);
for param in params {
query = bind_execute_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.execute(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.execute(&mut *connection).await
}
};
match result {
Ok(query_result) => {
let last_insert_id = query_result.last_insert_id();
if self.enable_logging {
log::debug!("insert() 成功,插入 ID: {}", last_insert_id);
}
Ok(last_insert_id)
}
Err(e) => {
log::error!("insert() 失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
pub async fn insert_batch<T>(self, data: &[T]) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
self.insert_batch_with_size(data, INSERT_BATCH_SIZE).await
}
pub async fn insert_batch_with_size<T>(
self,
data: &[T],
batch_size: usize,
) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
if batch_size == 0 {
return Err(crate::error::DbError::SerializationError(
"batch_size 不能为 0".to_string(),
));
}
if self.enable_logging {
log::debug!(
"执行 insert_batch_with_size() 操作,表: {},记录数: {},批次大小: {}",
self.table,
data.len(),
batch_size
);
}
if data.is_empty() {
return Err(crate::error::DbError::SerializationError(
"批量插入数据不能为空".to_string(),
));
}
let chunk_count = data.len().div_ceil(batch_size);
if chunk_count == 1 {
let enable_logging = self.enable_logging;
let affected = self.insert_chunk(data).await?;
if enable_logging {
log::debug!("insert_batch_with_size() 单批完成,影响 {} 行", affected);
}
return Ok(affected);
}
let mut total_affected = 0u64;
match self.executor {
QueryExecutor::Pool(pool) => {
let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
for (batch_index, chunk) in data.chunks(batch_size).enumerate() {
let result = execute_insert_chunk(
&mut transaction,
&self.table,
chunk,
&self.field_types,
)
.await?;
total_affected += result;
if self.enable_logging {
log::debug!("第 {} 批插入成功,影响 {} 行", batch_index + 1, result);
}
}
transaction
.commit()
.await
.map_err(crate::error::DbError::from)?;
}
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
for (batch_index, chunk) in data.chunks(batch_size).enumerate() {
let result = execute_insert_chunk(
&mut *connection,
&self.table,
chunk,
&self.field_types,
)
.await?;
total_affected += result;
if self.enable_logging {
log::debug!("第 {} 批插入成功,影响 {} 行", batch_index + 1, result);
}
}
}
}
if self.enable_logging {
log::debug!(
"insert_batch_with_size() 多批事务提交完成,总共影响 {} 行",
total_affected
);
}
Ok(total_affected)
}
async fn insert_chunk<T>(self, data: &[T]) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
let json_data_list: Result<Vec<_>, _> = data
.iter()
.map(|item| {
serde_json::to_value(item).map_err(|e| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
})
})
.collect();
let json_data_list = json_data_list?;
let mut generator = SqlGenerator::new();
generator.build_insert_batch(&self.table, &json_data_list, &self.field_types)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 insert_chunk() SQL: {}", sql);
log::debug!("参数数量: {}", params.len());
}
let mut query = sqlx::query(sql);
for param in params {
query = bind_execute_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.execute(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.execute(&mut *connection).await
}
};
match result {
Ok(query_result) => {
let rows_affected = query_result.rows_affected();
if self.enable_logging {
log::debug!("insert_chunk() 成功,影响 {} 行", rows_affected);
}
Ok(rows_affected)
}
Err(e) => {
log::error!("insert_chunk() 失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
pub async fn update<T>(self, data: &T) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
if self.enable_logging {
log::debug!("执行 update() 操作,表: {}", self.table);
}
if self.conditions.is_empty() {
log::warn!("update() 操作缺少 WHERE 条件,禁止全表更新");
return Err(crate::error::DbError::MissingWhereClause);
}
let json_data = serde_json::to_value(data).map_err(|e| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
})?;
let mut generator = SqlGenerator::new();
generator.build_update(&self.table, &json_data, &self.field_types, &self.conditions)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 update() SQL: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query(sql);
for param in params {
query = bind_execute_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.execute(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.execute(&mut *connection).await
}
};
match result {
Ok(query_result) => {
let rows_affected = query_result.rows_affected();
if self.enable_logging {
log::debug!("update() 成功,影响 {} 行", rows_affected);
}
Ok(rows_affected)
}
Err(e) => {
log::error!("update() 失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
pub async fn increment(
self,
field: &crate::FieldRef,
amount: i64,
) -> Result<u64, crate::error::DbError> {
self.execute_arithmetic_update(field, amount, ArithmeticOperator::Add)
.await
}
pub async fn decrement(
self,
field: &crate::FieldRef,
amount: i64,
) -> Result<u64, crate::error::DbError> {
self.execute_arithmetic_update(field, amount, ArithmeticOperator::Subtract)
.await
}
async fn execute_arithmetic_update(
self,
field: &crate::FieldRef,
amount: i64,
operator: ArithmeticOperator,
) -> Result<u64, crate::error::DbError> {
let mut generator = SqlGenerator::new();
generator.build_arithmetic_update(
&self.table,
field.as_str(),
operator,
amount,
&self.conditions,
)?;
let mut query = sqlx::query(generator.get_sql());
for param in generator.get_params() {
query = bind_execute_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.execute(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.execute(&mut *connection).await
}
}?;
Ok(result.rows_affected())
}
pub async fn delete(self) -> Result<u64, crate::error::DbError> {
if self.enable_logging {
log::debug!("执行 delete() 操作,表: {}", self.table);
}
if self.conditions.is_empty() {
log::warn!("delete() 操作缺少 WHERE 条件,禁止全表删除");
return Err(crate::error::DbError::MissingWhereClause);
}
let mut generator = SqlGenerator::new();
generator.build_delete(&self.table, &self.conditions)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 delete() SQL: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query(sql);
for param in params {
query = bind_execute_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.execute(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.execute(&mut *connection).await
}
};
match result {
Ok(query_result) => {
let rows_affected = query_result.rows_affected();
if self.enable_logging {
log::debug!("delete() 成功,影响 {} 行", rows_affected);
}
Ok(rows_affected)
}
Err(e) => {
log::error!("delete() 失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
pub async fn update_batch<T>(
self,
records: &[T],
where_field: &crate::FieldRef,
) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
if records.is_empty() {
return Err(crate::error::DbError::SerializationError(
"批量更新数据不能为空".to_string(),
));
}
let json_records: Vec<serde_json::Value> = records
.iter()
.map(|r| {
serde_json::to_value(r).map_err(|e| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
})
})
.collect::<Result<_, _>>()?;
let mut total = 0u64;
match self.executor {
QueryExecutor::Pool(pool) => {
let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
for chunk in json_records.chunks(UPDATE_BATCH_SIZE) {
total += execute_update_chunk(
&mut transaction,
&self.table,
chunk,
where_field,
&self.field_types,
)
.await?;
}
transaction
.commit()
.await
.map_err(crate::error::DbError::from)?;
}
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
for chunk in json_records.chunks(UPDATE_BATCH_SIZE) {
total += execute_update_chunk(
&mut *connection,
&self.table,
chunk,
where_field,
&self.field_types,
)
.await?;
}
}
}
Ok(total)
}
pub async fn upsert<T>(self, data: &T) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
if self.enable_logging {
log::debug!("执行 upsert() 操作,表: {}", self.table);
}
let json_data = serde_json::to_value(data).map_err(|e| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
})?;
let mut generator = SqlGenerator::new();
generator.build_upsert(&self.table, &json_data, &self.field_types)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 upsert() SQL: {}", sql);
}
let mut query = sqlx::query(sql);
for param in params {
query = bind_execute_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.execute(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.execute(&mut *connection).await
}
}
.map_err(crate::error::DbError::from)?;
let rows = result.rows_affected();
if self.enable_logging {
log::debug!("upsert() 完成,rows_affected: {}", rows);
}
Ok(rows)
}
}
async fn execute_insert_chunk<T>(
connection: &mut sqlx::MySqlConnection,
table: &str,
data: &[T],
field_types: &HashMap<String, FieldType>,
) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
let json_data_list = data
.iter()
.map(|item| {
serde_json::to_value(item).map_err(|error| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
})
})
.collect::<Result<Vec<_>, _>>()?;
let mut generator = SqlGenerator::new();
generator.build_insert_batch(table, &json_data_list, field_types)?;
let mut query = sqlx::query(generator.get_sql());
for param in generator.get_params() {
query = bind_execute_param(query, param);
}
Ok(query.execute(connection).await?.rows_affected())
}
async fn execute_update_chunk(
connection: &mut sqlx::MySqlConnection,
table: &str,
records: &[serde_json::Value],
where_field: &crate::FieldRef,
field_types: &HashMap<String, FieldType>,
) -> Result<u64, crate::error::DbError> {
let mut generator = SqlGenerator::new();
generator.build_update_batch(table, records, where_field.as_str(), field_types)?;
let mut query = sqlx::query(generator.get_sql());
for param in generator.get_params() {
query = bind_execute_param(query, param);
}
Ok(query.execute(connection).await?.rows_affected())
}
fn predicate_condition(predicate: &crate::Predicate) -> Result<Condition, crate::error::DbError> {
let condition = match predicate {
crate::Predicate::Compare(field, operator, value) => {
let name = field.as_str().to_string();
if matches!(operator, crate::CompareOp::Like) {
return value
.as_str()
.map(|pattern| Condition::Like(name, pattern.to_string()))
.ok_or_else(|| {
crate::error::DbError::InvalidArgument(
"LIKE predicate requires string value".to_string(),
)
});
}
let value = predicate_value(value);
match operator {
crate::CompareOp::Eq => Condition::Eq(name, value),
crate::CompareOp::Ne => Condition::Ne(name, value),
crate::CompareOp::Gt => Condition::Gt(name, value),
crate::CompareOp::Lt => Condition::Lt(name, value),
crate::CompareOp::Gte => Condition::Gte(name, value),
crate::CompareOp::Lte => Condition::Lte(name, value),
crate::CompareOp::Like => {
return Err(crate::error::DbError::InvalidArgument(
"LIKE predicate normalization failed".to_string(),
));
}
}
}
crate::Predicate::In(field, values) => Condition::In(
field.as_str().to_string(),
values.iter().map(predicate_value).collect(),
),
crate::Predicate::NotIn(field, values) => Condition::NotIn(
field.as_str().to_string(),
values.iter().map(predicate_value).collect(),
),
crate::Predicate::Between(field, start, end) => Condition::Between(
field.as_str().to_string(),
predicate_value(start),
predicate_value(end),
),
crate::Predicate::IsNull(field) => Condition::IsNull(field.as_str().to_string()),
crate::Predicate::IsNotNull(field) => Condition::IsNotNull(field.as_str().to_string()),
crate::Predicate::And(values) => Condition::And(
values
.iter()
.map(predicate_condition)
.collect::<Result<Vec<_>, _>>()?,
),
crate::Predicate::Or(values) => Condition::Or(
values
.iter()
.map(predicate_condition)
.collect::<Result<Vec<_>, _>>()?,
),
};
Ok(condition)
}
fn predicate_value(value: &serde_json::Value) -> SqlValue {
match value {
serde_json::Value::Null => SqlValue::Null,
serde_json::Value::Bool(value) => SqlValue::Bool(*value),
serde_json::Value::Number(value) => {
value.as_i64().map(SqlValue::Int).unwrap_or_else(|| {
value
.as_u64()
.map(SqlValue::from)
.or_else(|| value.as_f64().map(SqlValue::Float))
.unwrap_or_else(|| SqlValue::String(value.to_string()))
})
}
serde_json::Value::String(value) => SqlValue::String(value.clone()),
serde_json::Value::Array(_) | serde_json::Value::Object(_) => SqlValue::Json(value.clone()),
}
}
#[cfg(test)]
mod predicate_scalar_tests {
use super::{predicate_value, SqlValue};
#[test]
fn controlled_predicates_bind_json_scalars_as_sql_scalars() {
assert!(matches!(
predicate_value(&serde_json::json!("alice")),
SqlValue::String(value) if value == "alice"
));
assert!(matches!(
predicate_value(&serde_json::json!(42)),
SqlValue::Int(42)
));
assert!(matches!(
predicate_value(&serde_json::json!(true)),
SqlValue::Bool(true)
));
assert!(matches!(
predicate_value(&serde_json::json!({"role": "admin"})),
SqlValue::Json(_)
));
}
}
fn bind_execute_param<'q>(
query: sqlx::query::Query<'q, sqlx::MySql, sqlx::mysql::MySqlArguments>,
param: &SqlValue,
) -> sqlx::query::Query<'q, sqlx::MySql, sqlx::mysql::MySqlArguments> {
bind_value_match!(query, param)
}
pub(crate) fn bind_param<'q, T>(
query: sqlx::query::QueryAs<'q, sqlx::MySql, T, sqlx::mysql::MySqlArguments>,
param: &SqlValue,
) -> sqlx::query::QueryAs<'q, sqlx::MySql, T, sqlx::mysql::MySqlArguments>
where
T: for<'r> sqlx::FromRow<'r, sqlx::mysql::MySqlRow> + Send + Unpin,
{
bind_value_match!(query, param)
}
fn bind_scalar_param<'q, T>(
query: sqlx::query::QueryScalar<'q, sqlx::MySql, T, sqlx::mysql::MySqlArguments>,
param: &SqlValue,
) -> sqlx::query::QueryScalar<'q, sqlx::MySql, T, sqlx::mysql::MySqlArguments>
where
T: for<'r> sqlx::Decode<'r, sqlx::MySql> + sqlx::Type<sqlx::MySql> + Send + Unpin,
{
bind_value_match!(query, param)
}
#[allow(dead_code)]
fn bind_scalar_param_option<'q, T>(
query: sqlx::query::QueryScalar<'q, sqlx::MySql, Option<T>, sqlx::mysql::MySqlArguments>,
param: &SqlValue,
) -> sqlx::query::QueryScalar<'q, sqlx::MySql, Option<T>, sqlx::mysql::MySqlArguments>
where
T: for<'r> sqlx::Decode<'r, sqlx::MySql> + sqlx::Type<sqlx::MySql> + Send + Unpin,
{
bind_value_match!(query, param)
}
#[cfg(test)]
macro_rules! test_field {
($value:expr) => {{
&crate::FieldRef::new(($value).to_string()).expect("测试策略必须生成合法字段名")
}};
}
#[cfg(test)]
macro_rules! test_table {
($value:expr) => {{
&crate::TableRef::new(($value).to_string()).expect("测试策略必须生成合法表名")
}};
}
#[cfg(test)]
#[allow(deprecated)]
#[allow(clippy::unwrap_used, clippy::expect_used)]
mod tests {
use super::*;
use crate::mysql::Subquery;
use sqlx::mysql::MySqlPoolOptions;
async fn create_test_pool() -> MySqlPool {
MySqlPoolOptions::new()
.max_connections(1)
.connect_lazy("mysql://root:111111@localhost:3306/test")
.expect("无法解析测试数据库 URL")
}
fn make_sync_test_pool() -> &'static MySqlPool {
use std::sync::OnceLock;
static RT: OnceLock<tokio::runtime::Runtime> = OnceLock::new();
static POOL: OnceLock<MySqlPool> = OnceLock::new();
let rt = RT.get_or_init(|| {
tokio::runtime::Builder::new_multi_thread()
.enable_all()
.build()
.expect("无法创建测试用 Tokio 运行时")
});
POOL.get_or_init(|| {
rt.block_on(async {
MySqlPoolOptions::new()
.max_connections(1)
.connect_lazy("mysql://root:111111@localhost:3306/test")
.expect("无法解析测试数据库 URL")
})
})
}
#[test]
fn test_controlled_subqueries_render_exists_not_exists_and_in_in_parameter_order() {
let pool = make_sync_test_pool();
let paid_order = Subquery::new("orders", "id")
.expect("合法子查询")
.where_column("orders.user_id", "=", "users.id")
.expect("合法关联列")
.where_value("orders.status", "=", "paid")
.expect("合法绑定条件");
let active_user = Subquery::new("memberships", "user_id")
.expect("合法子查询")
.where_value("memberships.active", "=", true)
.expect("合法绑定条件");
let banned_user = Subquery::new("bans", "id")
.expect("合法子查询")
.where_column("bans.user_id", "=", "users.id")
.expect("合法关联列")
.where_value("bans.reason", "=", "fraud")
.expect("合法绑定条件");
let builder = QueryBuilder::new(pool, "users", false)
.where_and(
yang_db::field!("users.tenant_id"),
yang_db::CompareOp::Eq,
7,
)
.where_exists(paid_order)
.where_in_subquery(yang_db::field!("users.id"), active_user)
.where_not_exists(banned_user);
let mut generator = SqlGenerator::new();
generator.build_select(&builder).expect("子查询应可渲染");
assert_eq!(
generator.get_sql(),
"SELECT * FROM `users` WHERE (`users`.`tenant_id` = ? AND EXISTS (SELECT `id` FROM `orders` WHERE `orders`.`user_id` = `users`.`id` AND `orders`.`status` = ?) AND `users`.`id` IN (SELECT `user_id` FROM `memberships` WHERE `memberships`.`active` = ?) AND NOT EXISTS (SELECT `id` FROM `bans` WHERE `bans`.`user_id` = `users`.`id` AND `bans`.`reason` = ?))"
);
assert!(
matches!(generator.get_params(), [SqlValue::Int(7), SqlValue::String(status), SqlValue::Bool(true), SqlValue::String(reason)] if status == "paid" && reason == "fraud")
);
}
#[test]
fn test_controlled_subqueries_reject_adversarial_structure_before_rendering() {
for payload in [
"",
"orders; DROP TABLE users",
"orders --",
"a.b",
"orders\0",
] {
assert!(
Subquery::new(payload, "id").is_err(),
"非法表名被接受: {payload:?}"
);
}
for payload in ["", "id) FROM users --", "COUNT(*)", "a.b.c", "id\0"] {
assert!(
Subquery::new("orders", payload).is_err(),
"非法投影被接受: {payload:?}"
);
}
assert!(Subquery::new("orders", "id")
.expect("合法子查询")
.where_column("orders.user_id", "= 1 OR 1=1 --", "users.id")
.is_err());
assert!(Subquery::new("orders", "id")
.expect("合法子查询")
.where_value("orders.status --", "=", "paid")
.is_err());
assert!(crate::FieldRef::new("users.id) OR 1=1 --").is_err());
}
#[test]
fn test_union_all_keeps_branch_scope_and_parameter_order() {
let pool = make_sync_test_pool();
let branch = QueryBuilder::new(pool, "archived_users", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("kind"))
.where_and(yang_db::field!("tenant_id"), yang_db::CompareOp::Eq, 8)
.order(yang_db::field!("id"), yang_db::SortOrder::Desc)
.limit(2);
let builder = QueryBuilder::new(pool, "users", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("kind"))
.where_and(yang_db::field!("tenant_id"), yang_db::CompareOp::Eq, 7)
.union_all(branch)
.expect("输出列数一致")
.order(yang_db::field!("id"), yang_db::SortOrder::Asc)
.limit(5);
let mut generator = SqlGenerator::new();
generator
.build_select(&builder)
.expect("UNION ALL 应可渲染");
assert_eq!(generator.get_sql(), "SELECT `id`, `kind` FROM `users` WHERE `tenant_id` = ? UNION ALL (SELECT `id`, `kind` FROM `archived_users` WHERE `tenant_id` = ? ORDER BY `id` DESC LIMIT 2) ORDER BY `id` ASC LIMIT 5");
assert!(matches!(
generator.get_params(),
[SqlValue::Int(7), SqlValue::Int(8)]
));
}
#[test]
fn test_union_rejects_unknown_or_mismatched_output_and_bad_branch_table() {
let pool = make_sync_test_pool();
assert!(QueryBuilder::new(pool, "users", false)
.union(QueryBuilder::new(pool, "archive", false))
.is_err());
assert!(QueryBuilder::new(pool, "users", false)
.field(yang_db::field!("id"))
.union(
QueryBuilder::new(pool, "archive", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("kind")),
)
.is_err());
let builder = QueryBuilder::new(pool, "users", false)
.field(yang_db::field!("id"))
.union(
QueryBuilder::new(pool, "archive; DROP TABLE users", false)
.field(yang_db::field!("id")),
)
.expect("列数一致");
let mut generator = SqlGenerator::new();
assert!(generator.build_select(&builder).is_err());
assert!(generator.get_params().is_empty());
}
#[test]
fn test_transaction_row_lock_rendering_is_typed_and_parameterized() {
let builder = QueryBuilder::new(make_sync_test_pool(), "accounts", false)
.field(yang_db::field!("balance"))
.where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 42)
.limit(1);
let (sql, params) = builder
.render_for_transaction(Some(crate::RowLock::ForUpdate))
.expect("事务锁查询应可渲染");
assert_eq!(
sql,
"SELECT `balance` FROM `accounts` WHERE `id` = ? LIMIT 1 FOR UPDATE"
);
assert!(matches!(params.as_slice(), [SqlValue::Int(42)]));
}
#[test]
fn test_transaction_row_lock_rejects_unsupported_query_shapes() {
let pool = make_sync_test_pool();
let grouped = QueryBuilder::new(pool, "accounts", false)
.expr(crate::SelectExpr::count_all())
.group(yang_db::field!("tenant_id"));
assert!(grouped
.render_for_transaction(Some(crate::RowLock::ForShare))
.is_err());
let union = QueryBuilder::new(pool, "accounts", false)
.field(yang_db::field!("id"))
.union(QueryBuilder::new(pool, "archived_accounts", false).field(yang_db::field!("id")))
.expect("列数一致的 UNION 应构建成功");
assert!(union
.render_for_transaction(Some(crate::RowLock::ForUpdate))
.is_err());
}
#[tokio::test]
async fn test_atomic_update_rejects_missing_where_and_adversarial_field_before_io() {
let pool = MySqlPoolOptions::new()
.max_connections(1)
.connect_lazy("mysql://root:111111@localhost:3306/test")
.expect("合法测试数据库 URL");
let missing_where = QueryBuilder::new(&pool, "accounts", false)
.increment(yang_db::field!("balance"), 1)
.await;
assert!(matches!(
missing_where,
Err(crate::DbError::MissingWhereClause)
));
assert!(crate::FieldRef::new("balance = 0; DROP TABLE accounts --").is_err());
}
#[test]
fn test_atomic_update_renderer_binds_negative_amount_before_where_params() {
let mut generator = SqlGenerator::new();
generator
.build_arithmetic_update(
"accounts",
"balance",
ArithmeticOperator::Add,
-3,
&[Condition::Eq("id".to_string(), SqlValue::Int(9))],
)
.expect("原子更新应可渲染");
assert_eq!(
generator.get_sql(),
"UPDATE `accounts` SET `balance` = `balance` + ? WHERE `id` = ?"
);
assert!(matches!(
generator.get_params(),
[SqlValue::Int(-3), SqlValue::Int(9)]
));
}
#[tokio::test]
async fn test_table_name_in_sql() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false);
let sql = builder.to_sql();
assert!(sql.contains("FROM `users`"));
}
#[test]
fn test_sql_generator_new() {
let generator = SqlGenerator::new();
assert_eq!(generator.get_sql(), "");
assert_eq!(generator.get_params().len(), 0);
}
#[test]
fn test_sql_generator_append() {
let mut generator = SqlGenerator::new();
generator.append("SELECT * FROM users");
assert_eq!(generator.get_sql(), "SELECT * FROM users");
}
#[test]
fn test_sql_generator_add_param() {
let mut generator = SqlGenerator::new();
generator.add_param(SqlValue::Int(42));
generator.add_param(SqlValue::String("test".to_string()));
assert_eq!(generator.get_params().len(), 2);
}
#[test]
fn test_sql_generator_clear() {
let mut generator = SqlGenerator::new();
generator.append("SELECT * FROM users");
generator.add_param(SqlValue::Int(1));
generator.clear();
assert_eq!(generator.get_sql(), "");
assert_eq!(generator.get_params().len(), 0);
}
#[test]
fn test_sql_generator_multiple_operations() {
let mut generator = SqlGenerator::new();
generator.append("SELECT * FROM users WHERE id = ?");
generator.add_param(SqlValue::Int(1));
generator.append(" AND name = ?");
generator.add_param(SqlValue::String("test".to_string()));
assert_eq!(
generator.get_sql(),
"SELECT * FROM users WHERE id = ? AND name = ?"
);
assert_eq!(generator.get_params().len(), 2);
}
#[tokio::test]
async fn test_field_selection() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("name"));
let sql = builder.to_sql();
assert!(sql.contains("`id`, `name`"));
}
#[tokio::test]
async fn test_fields_selection() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false).fields(&[
yang_db::field!("id"),
yang_db::field!("name"),
yang_db::field!("email"),
]);
let sql = builder.to_sql();
assert!(sql.contains("`id`, `name`, `email`"));
}
#[tokio::test]
async fn test_distinct() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("name"))
.distinct();
let sql = builder.to_sql();
assert!(sql.contains("SELECT DISTINCT"));
}
#[tokio::test]
async fn test_field_type_marking() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.json(yang_db::field!("data"))
.datetime(yang_db::field!("created_at"))
.timestamp(yang_db::field!("updated_at"))
.decimal(yang_db::field!("price"))
.blob(yang_db::field!("content"))
.text(yang_db::field!("description"));
assert_eq!(builder.field_types.get("data"), Some(&FieldType::Json));
assert_eq!(
builder.field_types.get("created_at"),
Some(&FieldType::DateTime)
);
assert_eq!(
builder.field_types.get("updated_at"),
Some(&FieldType::Timestamp)
);
assert_eq!(builder.field_types.get("price"), Some(&FieldType::Decimal));
assert_eq!(builder.field_types.get("content"), Some(&FieldType::Blob));
assert_eq!(
builder.field_types.get("description"),
Some(&FieldType::Text)
);
}
#[tokio::test]
async fn test_where_and() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.where_and(yang_db::field!("name"), yang_db::CompareOp::Eq, "test")
.where_and(yang_db::field!("age"), yang_db::CompareOp::Gt, 18);
assert_eq!(builder.conditions.len(), 2);
}
#[tokio::test]
async fn test_where_or() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.where_or(yang_db::field!("status"), yang_db::CompareOp::Eq, 1)
.where_or(yang_db::field!("status"), yang_db::CompareOp::Eq, 2);
assert_eq!(builder.conditions.len(), 1);
}
#[tokio::test]
async fn test_where_in() {
let pool = create_test_pool().await;
let builder =
QueryBuilder::new(&pool, "users", false).where_in(yang_db::field!("id"), vec![1, 2, 3]);
assert_eq!(builder.conditions.len(), 1);
}
#[tokio::test]
async fn test_where_between() {
let pool = create_test_pool().await;
let builder =
QueryBuilder::new(&pool, "users", false).where_between(yang_db::field!("age"), 18, 65);
assert_eq!(builder.conditions.len(), 1);
}
#[tokio::test]
async fn test_join() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false).join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
);
assert_eq!(builder.joins.len(), 1);
}
#[tokio::test]
async fn test_left_join() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false).left_join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
);
assert_eq!(builder.joins.len(), 1);
}
#[tokio::test]
async fn test_right_join() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false).right_join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
);
assert_eq!(builder.joins.len(), 1);
}
#[tokio::test]
async fn test_order() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.order(yang_db::field!("name"), yang_db::SortOrder::Asc)
.order(yang_db::field!("age"), yang_db::SortOrder::Desc);
assert_eq!(builder.order_by.len(), 2);
}
#[tokio::test]
async fn test_group() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.group(yang_db::field!("status"))
.group(yang_db::field!("role"));
assert_eq!(builder.group_by.len(), 2);
}
#[tokio::test]
async fn test_select_with_where() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("name"))
.where_and(yang_db::field!("status"), yang_db::CompareOp::Eq, 1);
let sql = builder.to_sql();
assert!(sql.contains("SELECT `id`, `name` FROM `users`"));
assert!(sql.contains("WHERE"));
}
#[tokio::test]
async fn test_select_with_join() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("users.id"))
.field(yang_db::field!("orders.total"))
.join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
);
let sql = builder.to_sql();
assert!(sql.contains("SELECT `users`.`id`, `orders`.`total` FROM `users`"));
assert!(sql.contains("INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`"));
}
#[tokio::test]
async fn test_select_with_order_by() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("name"))
.order(yang_db::field!("name"), yang_db::SortOrder::Asc)
.order(yang_db::field!("age"), yang_db::SortOrder::Desc);
let sql = builder.to_sql();
assert!(sql.contains("ORDER BY `name` ASC, `age` DESC"));
}
#[tokio::test]
async fn test_select_with_group_by() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("status"))
.group(yang_db::field!("status"));
let sql = builder.to_sql();
assert!(sql.contains("GROUP BY `status`"));
}
#[tokio::test]
async fn test_select_with_limit_offset() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("id"))
.limit(10)
.offset(20);
let sql = builder.to_sql();
assert!(sql.contains("LIMIT 10"));
assert!(sql.contains("OFFSET 20"));
}
#[tokio::test]
async fn test_select_complex_query() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("users.id"))
.field(yang_db::field!("users.name"))
.field(yang_db::field!("orders.total"))
.distinct()
.join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
)
.where_and(yang_db::field!("users.status"), yang_db::CompareOp::Eq, 1)
.where_and(yang_db::field!("orders.total"), yang_db::CompareOp::Gt, 100)
.group(yang_db::field!("users.id"))
.order(yang_db::field!("orders.total"), yang_db::SortOrder::Desc)
.limit(50);
let sql = builder.to_sql();
assert!(sql.contains("SELECT DISTINCT"));
assert!(sql.contains("`users`.`id`, `users`.`name`, `orders`.`total`"));
assert!(sql.contains("FROM `users`"));
assert!(sql.contains("INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`"));
assert!(sql.contains("WHERE"));
assert!(sql.contains("GROUP BY `users`.`id`"));
assert!(sql.contains("ORDER BY `orders`.`total` DESC"));
assert!(sql.contains("LIMIT 50"));
}
#[tokio::test]
async fn test_select_with_multiple_joins() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("users.name"))
.field(yang_db::field!("orders.total"))
.field(yang_db::field!("products.name"))
.join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
)
.left_join(
yang_db::table!("products"),
yang_db::field!("orders.product_id"),
yang_db::field!("products.id"),
);
let sql = builder.to_sql();
assert!(sql.contains("INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`"));
assert!(sql.contains("LEFT JOIN `products` ON `orders`.`product_id` = `products`.`id`"));
}
#[tokio::test]
async fn test_select_with_in_condition() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("name"))
.where_in(yang_db::field!("id"), vec![1, 2, 3, 4, 5]);
let sql = builder.to_sql();
assert!(sql.contains("WHERE"));
assert!(sql.contains("IN"));
}
#[tokio::test]
async fn test_select_with_between_condition() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("name"))
.where_between(yang_db::field!("age"), 18, 65);
let sql = builder.to_sql();
assert!(sql.contains("WHERE"));
assert!(sql.contains("BETWEEN"));
}
#[test]
fn test_where_null_generates_is_null_sql() {
let pool = make_sync_test_pool();
let builder =
QueryBuilder::new(pool, "users", false).where_null(yang_db::field!("deleted_at"));
let sql = builder.to_sql();
assert!(sql.contains("`deleted_at` IS NULL"));
}
#[test]
fn test_where_not_null_generates_is_not_null_sql() {
let pool = make_sync_test_pool();
let builder =
QueryBuilder::new(pool, "users", false).where_not_null(yang_db::field!("email"));
let sql = builder.to_sql();
assert!(sql.contains("`email` IS NOT NULL"));
}
#[test]
fn test_is_null_with_and_condition() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users", false)
.where_and(yang_db::field!("status"), yang_db::CompareOp::Eq, 1i64)
.where_null(yang_db::field!("deleted_at"));
let sql = builder.to_sql();
assert!(sql.contains("`status` = ?"));
assert!(sql.contains("`deleted_at` IS NULL"));
}
#[test]
fn test_having_clause_sql_generation() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "orders", false)
.field(yang_db::field!("user_id"))
.expr(crate::SelectExpr::count_all().alias(yang_db::field!("cnt")))
.group(yang_db::field!("user_id"))
.having_cond(yang_db::field!("cnt"), yang_db::CompareOp::Gt, 5i64);
let sql = builder.to_sql();
assert!(sql.contains("HAVING"));
assert!(sql.contains("`cnt` > ?"));
}
#[test]
fn test_having_without_group_returns_error() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "orders", false).having_cond(
yang_db::field!("cnt"),
yang_db::CompareOp::Gt,
5i64,
);
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_err());
assert!(matches!(
result.unwrap_err(),
crate::DbError::MissingGroupByClause
));
}
#[test]
fn test_identifier_apis_quote_every_structural_fragment() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users", false)
.field(yang_db::field!("users.id"))
.expr(crate::SelectExpr::count_all().alias(yang_db::field!("cnt")))
.join(
yang_db::table!("profiles"),
yang_db::field!("users.id"),
yang_db::field!("profiles.user_id"),
)
.group(yang_db::field!("users.id"))
.order(yang_db::field!("users.id"), yang_db::SortOrder::Asc);
let sql = builder.try_to_sql().expect("安全 API 应生成 SQL");
assert!(sql.contains("SELECT `users`.`id`, COUNT(*) AS `cnt`"));
assert!(sql.contains("INNER JOIN `profiles` ON `users`.`id` = `profiles`.`user_id`"));
assert!(sql.contains("GROUP BY `users`.`id`"));
assert!(sql.contains("ORDER BY `users`.`id` ASC"));
}
#[test]
fn test_identifier_apis_reject_adversarial_fragments() {
let payloads = [
"",
".id",
"users.",
"a.b.c",
"users.id --",
"users/*x*/.id",
"用户.id",
"users.`id`",
"users.id\0",
];
for payload in payloads {
assert!(crate::FieldRef::new(payload).is_err());
}
assert!(crate::TableRef::new("profiles p").is_err());
}
#[test]
fn test_try_to_sql_surfaces_invalid_table_identifier() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users; DROP TABLE users", false);
let result = builder.try_to_sql();
assert!(matches!(result, Err(crate::DbError::InvalidArgument(_))));
}
#[test]
fn test_try_to_sql_rejects_invalid_condition_identifier() {
assert!(crate::FieldRef::new("id;DROP").is_err());
}
#[test]
fn test_try_to_sql_accepts_qualified_where_and_having_identifiers() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users", false)
.where_and(
yang_db::field!("users.status"),
yang_db::CompareOp::Eq,
1i64,
)
.group(yang_db::field!("users.id"))
.having_cond(
yang_db::field!("users.score"),
yang_db::CompareOp::Gt,
10i64,
);
let sql = builder
.try_to_sql()
.expect("合法的两段限定条件字段应生成 SQL");
assert!(sql.contains("WHERE `users`.`status` = ?"));
assert!(sql.contains("HAVING `users`.`score` > ?"));
}
#[test]
fn test_try_to_sql_rejects_malicious_qualified_where_and_having_identifiers() {
let invalid_fields = [
"",
".id",
"users.",
"a.b.c",
"users.id;DROP",
"users.id --",
"users.`id`",
"COUNT(users.id)",
];
for field in invalid_fields {
assert!(crate::FieldRef::new(field).is_err());
}
}
#[test]
fn test_try_to_sql_surfaces_missing_group_by() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "orders", false).having_cond(
yang_db::field!("cnt"),
yang_db::CompareOp::Gt,
5i64,
);
let result = builder.try_to_sql();
assert!(matches!(result, Err(crate::DbError::MissingGroupByClause)));
}
#[test]
fn test_try_to_sql_rejects_empty_in_condition() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users", false)
.where_in(yang_db::field!("id"), Vec::<i64>::new());
let result = builder.try_to_sql();
assert!(matches!(result, Err(crate::DbError::InvalidArgument(_))));
}
#[test]
fn test_try_to_sql_rejects_empty_boolean_condition() {
let pool = make_sync_test_pool();
let mut builder = QueryBuilder::new(pool, "users", false);
builder.conditions.push(Condition::And(vec![]));
let result = builder.try_to_sql();
assert!(matches!(result, Err(crate::DbError::InvalidArgument(_))));
}
#[test]
fn test_to_sql_does_not_fallback_to_raw_untrusted_table() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users; DROP TABLE users", false);
let sql = builder.to_sql();
assert_eq!(sql, "/* SQL generation failed */");
assert!(!sql.contains("DROP TABLE"));
assert!(!sql.contains("users;"));
}
#[test]
fn test_having_clause_order() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "orders", false)
.group(yang_db::field!("user_id"))
.having_cond(yang_db::field!("cnt"), yang_db::CompareOp::Gt, 5i64)
.order(yang_db::field!("cnt"), yang_db::SortOrder::Desc);
let sql = builder.to_sql();
let group_pos = sql.find("GROUP BY").unwrap();
let having_pos = sql.find("HAVING").unwrap();
let order_pos = sql.find("ORDER BY").unwrap();
assert!(group_pos < having_pos);
assert!(having_pos < order_pos);
}
#[test]
fn test_update_batch_case_when_sql() {
let records = vec![
serde_json::json!({"id": 1, "name": "Alice", "age": 25}),
serde_json::json!({"id": 2, "name": "Bob", "age": 30}),
];
let mut generator = SqlGenerator::new();
generator
.build_update_batch("users", &records, "id", &std::collections::HashMap::new())
.unwrap();
let sql = generator.get_sql();
assert!(sql.starts_with("UPDATE `users` SET "));
assert!(sql.contains("CASE WHEN `id`=? THEN ?"));
assert!(sql.contains("WHERE `id` IN ("));
}
#[test]
fn test_update_batch_empty_returns_error() {
let records: Vec<serde_json::Value> = vec![];
let mut generator = SqlGenerator::new();
let result = generator.build_update_batch(
"users",
&records,
"id",
&std::collections::HashMap::new(),
);
assert!(result.is_err());
}
#[test]
fn test_upsert_sql_generation() {
let data = serde_json::json!({"id": 1, "name": "Alice", "email": "a@b.com"});
let mut generator = SqlGenerator::new();
generator
.build_upsert("users", &data, &std::collections::HashMap::new())
.unwrap();
let sql = generator.get_sql();
assert!(sql.starts_with("INSERT INTO `users`"));
assert!(sql.contains("ON DUPLICATE KEY UPDATE"));
assert!(sql.contains("`name`=VALUES(`name`)"));
}
#[test]
fn test_upsert_empty_data_returns_error() {
let data = serde_json::json!({});
let mut generator = SqlGenerator::new();
let result = generator.build_upsert("users", &data, &std::collections::HashMap::new());
assert!(result.is_err());
}
#[tokio::test]
async fn test_sql_generator_build_select_basic() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("name"));
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
assert_eq!(generator.get_sql(), "SELECT `id`, `name` FROM `users`");
}
#[tokio::test]
async fn test_sql_generator_build_select_with_distinct() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("name"))
.distinct();
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
assert_eq!(generator.get_sql(), "SELECT DISTINCT `name` FROM `users`");
}
#[tokio::test]
async fn test_sql_generator_build_select_all_fields() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false);
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
assert_eq!(generator.get_sql(), "SELECT * FROM `users`");
}
#[tokio::test]
async fn test_sql_generator_build_where() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.where_and(yang_db::field!("status"), yang_db::CompareOp::Eq, 1)
.where_and(yang_db::field!("age"), yang_db::CompareOp::Gt, 18);
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
let sql = generator.get_sql();
assert!(sql.contains("WHERE"));
assert!(sql.contains("status"));
assert!(sql.contains("age"));
}
#[tokio::test]
async fn test_sql_generator_build_joins() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
)
.left_join(
yang_db::table!("profiles"),
yang_db::field!("users.id"),
yang_db::field!("profiles.user_id"),
);
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
let sql = generator.get_sql();
assert!(sql.contains("INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`"));
assert!(sql.contains("LEFT JOIN `profiles` ON `users`.`id` = `profiles`.`user_id`"));
}
#[tokio::test]
async fn test_sql_generator_build_order_by() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.order(yang_db::field!("name"), yang_db::SortOrder::Asc)
.order(yang_db::field!("created_at"), yang_db::SortOrder::Desc);
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
let sql = generator.get_sql();
assert!(sql.contains("ORDER BY `name` ASC, `created_at` DESC"));
}
#[tokio::test]
async fn test_sql_generator_build_group_by() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.group(yang_db::field!("status"))
.group(yang_db::field!("role"));
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
let sql = generator.get_sql();
assert!(sql.contains("GROUP BY `status`, `role`"));
}
#[tokio::test]
async fn test_sql_generator_build_limit_offset() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.limit(10)
.offset(20);
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
let sql = generator.get_sql();
assert!(sql.contains("LIMIT 10"));
assert!(sql.contains("OFFSET 20"));
}
#[tokio::test]
async fn test_sql_generator_complex_query() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("users.id"))
.field(yang_db::field!("users.name"))
.expr(
crate::SelectExpr::count(yang_db::field!("orders.id"))
.alias(yang_db::field!("order_count")),
)
.distinct()
.join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
)
.where_and(yang_db::field!("users.status"), yang_db::CompareOp::Eq, 1)
.where_and(yang_db::field!("orders.total"), yang_db::CompareOp::Gt, 100)
.group(yang_db::field!("users.id"))
.group(yang_db::field!("users.name"))
.order(yang_db::field!("order_count"), yang_db::SortOrder::Desc)
.limit(20)
.offset(10);
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
let sql = generator.get_sql();
assert!(sql.starts_with("SELECT DISTINCT"));
assert!(sql.contains("`users`.`id`, `users`.`name`, COUNT(`orders`.`id`) AS `order_count`"));
assert!(sql.contains("FROM `users`"));
assert!(sql.contains("INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`"));
assert!(sql.contains("WHERE"));
assert!(sql.contains("GROUP BY `users`.`id`, `users`.`name`"));
assert!(sql.contains("ORDER BY `order_count` DESC"));
assert!(sql.contains("LIMIT 20"));
assert!(sql.contains("OFFSET 10"));
}
#[tokio::test]
async fn test_find_adds_limit_one() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("name"))
.where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 1);
assert_eq!(builder.limit, None);
let builder_with_limit = QueryBuilder::new(&pool, "users", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("name"))
.where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 1)
.limit(1);
let sql = builder_with_limit.to_sql();
assert!(sql.contains("LIMIT 1"), "find() 应该自动添加 LIMIT 1");
}
#[test]
fn test_sql_generator_build_insert_basic() {
let mut generator = SqlGenerator::new();
let data = serde_json::json!({
"name": "张三",
"age": 25,
"email": "zhangsan@example.com"
});
let field_types = HashMap::new();
let result = generator.build_insert("users", &data, &field_types);
assert!(result.is_ok());
let sql = generator.get_sql();
assert!(sql.starts_with("INSERT INTO `users`"));
assert!(sql.contains("name"));
assert!(sql.contains("age"));
assert!(sql.contains("email"));
assert!(sql.contains("VALUES"));
assert_eq!(generator.get_params().len(), 3);
}
#[test]
fn test_sql_generator_build_insert_with_json_field() {
let mut generator = SqlGenerator::new();
let data = serde_json::json!({
"name": "测试用户",
"data": {"role": "admin", "permissions": ["read", "write"]}
});
let mut field_types = HashMap::new();
field_types.insert("data".to_string(), FieldType::Json);
let result = generator.build_insert("users", &data, &field_types);
assert!(result.is_ok());
let sql = generator.get_sql();
assert!(sql.contains("INSERT INTO `users`"));
assert!(sql.contains("name"));
assert!(sql.contains("data"));
assert_eq!(generator.get_params().len(), 2);
let params = generator.get_params();
let has_json = params.iter().any(|p| matches!(p, SqlValue::Json(_)));
assert!(has_json, "应该包含 JSON 类型的参数");
}
#[test]
fn test_sql_generator_build_insert_empty_data() {
let mut generator = SqlGenerator::new();
let data = serde_json::json!({});
let field_types = HashMap::new();
let result = generator.build_insert("users", &data, &field_types);
assert!(result.is_err());
assert!(matches!(
result.unwrap_err(),
crate::error::DbError::SerializationError(_)
));
}
#[test]
fn test_sql_generator_build_insert_not_object() {
let mut generator = SqlGenerator::new();
let data = serde_json::json!([1, 2, 3]); let field_types = HashMap::new();
let result = generator.build_insert("users", &data, &field_types);
assert!(result.is_err());
assert!(matches!(
result.unwrap_err(),
crate::error::DbError::SerializationError(_)
));
}
#[tokio::test]
async fn test_avg_sql_generation() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "products", false);
test_builder.fields.clear();
test_builder
.fields
.push("CAST(AVG(price) AS DOUBLE)".to_string());
test_builder.limit = Some(1);
let sql = test_builder.to_sql();
assert!(sql.contains("SELECT CAST(AVG(price) AS DOUBLE)"));
assert!(sql.contains("FROM `products`"));
assert!(sql.contains("LIMIT 1"));
}
#[tokio::test]
async fn test_avg_with_where_sql() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "products", false).where_and(
yang_db::field!("status"),
yang_db::CompareOp::Eq,
1,
);
test_builder.fields.clear();
test_builder
.fields
.push("CAST(AVG(price) AS DOUBLE)".to_string());
test_builder.limit = Some(1);
let sql = test_builder.to_sql();
assert!(sql.contains("SELECT CAST(AVG(price) AS DOUBLE)"));
assert!(sql.contains("FROM `products`"));
assert!(sql.contains("WHERE"));
assert!(sql.contains("status"));
}
#[tokio::test]
async fn test_min_sql_generation() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "products", false);
test_builder.fields.clear();
test_builder.fields.push("MIN(price)".to_string());
test_builder.limit = Some(1);
let sql = test_builder.to_sql();
assert!(sql.contains("SELECT MIN(price)"));
assert!(sql.contains("FROM `products`"));
assert!(sql.contains("LIMIT 1"));
}
#[tokio::test]
async fn test_max_sql_generation() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "products", false);
test_builder.fields.clear();
test_builder.fields.push("MAX(price)".to_string());
test_builder.limit = Some(1);
let sql = test_builder.to_sql();
assert!(sql.contains("SELECT MAX(price)"));
assert!(sql.contains("FROM `products`"));
assert!(sql.contains("LIMIT 1"));
}
#[tokio::test]
async fn test_min_max_different_types() {
let pool = create_test_pool().await;
let mut builder_int = QueryBuilder::new(&pool, "products", false);
builder_int.fields.clear();
builder_int.fields.push("MIN(stock)".to_string());
let sql_int = builder_int.to_sql();
assert!(sql_int.contains("MIN(stock)"));
let mut builder_float = QueryBuilder::new(&pool, "products", false);
builder_float.fields.clear();
builder_float.fields.push("MAX(price)".to_string());
let sql_float = builder_float.to_sql();
assert!(sql_float.contains("MAX(price)"));
let mut builder_string = QueryBuilder::new(&pool, "users", false);
builder_string.fields.clear();
builder_string.fields.push("MIN(name)".to_string());
let sql_string = builder_string.to_sql();
assert!(sql_string.contains("MIN(name)"));
let mut builder_datetime = QueryBuilder::new(&pool, "users", false);
builder_datetime.fields.clear();
builder_datetime.fields.push("MAX(created_at)".to_string());
let sql_datetime = builder_datetime.to_sql();
assert!(sql_datetime.contains("MAX(created_at)"));
}
#[tokio::test]
async fn test_aggregates_with_group_by_sql() {
let pool = create_test_pool().await;
let mut test_builder =
QueryBuilder::new(&pool, "orders", false).group(yang_db::field!("user_id"));
test_builder.fields.clear();
test_builder.fields.push("user_id".to_string());
test_builder
.fields
.push("CAST(AVG(amount) AS DOUBLE) as avg_amount".to_string());
let sql = test_builder.to_sql();
assert!(sql.contains("SELECT user_id, CAST(AVG(amount) AS DOUBLE) as avg_amount"));
assert!(sql.contains("FROM `orders`"));
assert!(sql.contains("GROUP BY `user_id`"));
}
#[tokio::test]
async fn test_multiple_aggregates_sql() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "orders", false).where_and(
yang_db::field!("status"),
yang_db::CompareOp::Eq,
"completed",
);
test_builder.fields.clear();
test_builder
.fields
.push("CAST(AVG(amount) AS DOUBLE) as avg_amount".to_string());
test_builder
.fields
.push("CAST(MIN(amount) AS DOUBLE) as min_amount".to_string());
test_builder
.fields
.push("CAST(MAX(amount) AS DOUBLE) as max_amount".to_string());
test_builder
.fields
.push("COUNT(*) as order_count".to_string());
let sql = test_builder.to_sql();
assert!(sql.contains("CAST(AVG(amount) AS DOUBLE) as avg_amount"));
assert!(sql.contains("CAST(MIN(amount) AS DOUBLE) as min_amount"));
assert!(sql.contains("CAST(MAX(amount) AS DOUBLE) as max_amount"));
assert!(sql.contains("COUNT(*) as order_count"));
assert!(sql.contains("WHERE"));
assert!(sql.contains("status"));
}
#[tokio::test]
async fn test_sql_clause_order_with_aggregates() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "orders", false)
.where_and(
yang_db::field!("status"),
yang_db::CompareOp::Eq,
"completed",
)
.group(yang_db::field!("user_id"))
.order(yang_db::field!("total_amount"), yang_db::SortOrder::Desc);
test_builder.fields.clear();
test_builder.fields.push("user_id".to_string());
test_builder
.fields
.push("SUM(amount) as total_amount".to_string());
let sql = test_builder.to_sql();
let where_pos = sql.find("WHERE").expect("应该包含 WHERE");
let group_pos = sql.find("GROUP BY").expect("应该包含 GROUP BY");
let order_pos = sql.find("ORDER BY").expect("应该包含 ORDER BY");
assert!(where_pos < group_pos, "WHERE 应该在 GROUP BY 之前");
assert!(group_pos < order_pos, "GROUP BY 应该在 ORDER BY 之前");
}
#[tokio::test]
async fn test_aggregates_empty_result_sql() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "products", false).where_and(
yang_db::field!("id"),
yang_db::CompareOp::Eq,
-1,
); test_builder.fields.clear();
test_builder
.fields
.push("CAST(AVG(price) AS DOUBLE)".to_string());
test_builder.limit = Some(1);
let sql = test_builder.to_sql();
assert!(sql.contains("SELECT CAST(AVG(price) AS DOUBLE)"));
assert!(sql.contains("WHERE"));
}
#[tokio::test]
async fn test_aggregates_with_special_field_names() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "products", false);
test_builder.fields.clear();
test_builder.fields.push("AVG(unit_price)".to_string());
let sql = test_builder.to_sql();
assert!(sql.contains("AVG(unit_price)"));
let mut test_builder2 = QueryBuilder::new(&pool, "products", false);
test_builder2.fields.clear();
test_builder2.fields.push("MAX(`order`)".to_string());
let sql2 = test_builder2.to_sql();
assert!(sql2.contains("MAX(`order`)"));
}
#[tokio::test]
async fn test_aggregates_with_distinct() {
let pool = create_test_pool().await;
let mut test_builder = QueryBuilder::new(&pool, "orders", false);
test_builder.fields.clear();
test_builder
.fields
.push("COUNT(DISTINCT user_id) as unique_users".to_string());
let sql = test_builder.to_sql();
assert!(sql.contains("COUNT(DISTINCT user_id)"));
}
#[tokio::test]
async fn test_aggregates_with_join() {
let pool = create_test_pool().await;
let test_builder = QueryBuilder::new(&pool, "users", false)
.join(
yang_db::table!("orders"),
yang_db::field!("users.id"),
yang_db::field!("orders.user_id"),
)
.group(yang_db::field!("users.id"))
.field(yang_db::field!("users.id"))
.field(yang_db::field!("users.name"))
.expr(
crate::SelectExpr::count(yang_db::field!("orders.id"))
.alias(yang_db::field!("order_count")),
)
.expr(
crate::SelectExpr::sum(yang_db::field!("orders.amount"))
.alias(yang_db::field!("total_amount")),
);
let sql = test_builder.to_sql();
assert!(sql.contains("INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`"));
assert!(sql.contains("COUNT(`orders`.`id`) AS `order_count`"));
assert!(sql.contains("SUM(`orders`.`amount`) AS `total_amount`"));
assert!(sql.contains("GROUP BY `users`.`id`"));
}
#[tokio::test]
async fn test_aggregates_sql_injection_prevention() {
let pool = create_test_pool().await;
let builder = QueryBuilder::new(&pool, "products", false).where_and(
yang_db::field!("category"),
yang_db::CompareOp::Eq,
"'; DROP TABLE products; --",
);
let mut generator = SqlGenerator::new();
let result = generator.build_select(&builder);
assert!(result.is_ok());
let sql = generator.get_sql();
let params = generator.get_params();
assert!(sql.contains("?"));
assert!(!sql.contains("DROP TABLE"));
assert_eq!(params.len(), 1);
}
}
#[cfg(test)]
#[allow(deprecated)]
#[allow(clippy::unwrap_used, clippy::expect_used)]
mod property_tests {
use super::*;
use proptest::prelude::*;
use sqlx::mysql::MySqlPoolOptions;
fn table_name_strategy() -> impl Strategy<Value = String> {
"[a-z][a-z0-9_]{0,30}"
}
fn field_name_strategy() -> impl Strategy<Value = String> {
"[a-z][a-z0-9_]{0,30}"
}
fn create_test_pool_sync() -> MySqlPool {
tokio::runtime::Runtime::new().unwrap().block_on(async {
MySqlPoolOptions::new()
.max_connections(1)
.connect_lazy("mysql://root:111111@localhost:3306/test")
.expect("无法解析测试数据库 URL")
})
}
fn make_sync_test_pool() -> &'static MySqlPool {
use std::sync::OnceLock;
static RT: OnceLock<tokio::runtime::Runtime> = OnceLock::new();
static POOL: OnceLock<MySqlPool> = OnceLock::new();
let rt = RT.get_or_init(|| {
tokio::runtime::Builder::new_multi_thread()
.enable_all()
.build()
.expect("无法创建测试用 Tokio 运行时")
});
POOL.get_or_init(|| {
rt.block_on(async {
MySqlPoolOptions::new()
.max_connections(1)
.connect_lazy("mysql://root:111111@localhost:3306/test")
.expect("无法解析测试数据库 URL")
})
})
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_table_name_in_sql(table_name in table_name_strategy()) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false);
let sql = builder.to_sql();
let expected = format!("FROM `{}`", table_name);
prop_assert!(sql.contains(&expected));
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_table_name_override(
table_name1 in table_name_strategy(),
table_name2 in table_name_strategy()
) {
prop_assume!(table_name1 != table_name2);
let pool = create_test_pool_sync();
let builder1 = QueryBuilder::new(&pool, &table_name1, false);
let sql1 = builder1.to_sql();
let expected1 = format!("FROM `{}`", table_name1);
prop_assert!(sql1.contains(&expected1));
let builder2 = QueryBuilder::new(&pool, &table_name2, false);
let sql2 = builder2.to_sql();
let expected2 = format!("FROM `{}`", table_name2);
prop_assert!(sql2.contains(&expected2));
let pattern1 = format!("FROM {} ", table_name1);
let pattern1_alt = format!("FROM {}\n", table_name1);
prop_assert!(!sql2.contains(&pattern1) && !sql2.contains(&pattern1_alt));
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_field_selection(
table_name in table_name_strategy(),
fields in prop::collection::vec(field_name_strategy(), 1..10)
) {
let pool = create_test_pool_sync();
let mut builder = QueryBuilder::new(&pool, &table_name, false);
for field in &fields {
builder = builder.field(test_field!(field));
}
let sql = builder.to_sql();
for field in &fields {
prop_assert!(sql.contains(field));
}
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_distinct_keyword(
table_name in table_name_strategy(),
field in field_name_strategy()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.field(test_field!(field))
.distinct();
let sql = builder.to_sql();
prop_assert!(sql.contains("SELECT DISTINCT"));
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_special_field_type_marking(
table_name in table_name_strategy(),
json_field in field_name_strategy(),
datetime_field in field_name_strategy(),
timestamp_field in field_name_strategy(),
decimal_field in field_name_strategy(),
blob_field in field_name_strategy(),
text_field in field_name_strategy()
) {
prop_assume!(json_field != datetime_field);
prop_assume!(json_field != timestamp_field);
prop_assume!(json_field != decimal_field);
prop_assume!(json_field != blob_field);
prop_assume!(json_field != text_field);
prop_assume!(datetime_field != timestamp_field);
prop_assume!(datetime_field != decimal_field);
prop_assume!(datetime_field != blob_field);
prop_assume!(datetime_field != text_field);
prop_assume!(timestamp_field != decimal_field);
prop_assume!(timestamp_field != blob_field);
prop_assume!(timestamp_field != text_field);
prop_assume!(decimal_field != blob_field);
prop_assume!(decimal_field != text_field);
prop_assume!(blob_field != text_field);
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.json(test_field!(json_field))
.datetime(test_field!(datetime_field))
.timestamp(test_field!(timestamp_field))
.decimal(test_field!(decimal_field))
.blob(test_field!(blob_field))
.text(test_field!(text_field));
prop_assert_eq!(builder.field_types.get(&json_field), Some(&FieldType::Json));
prop_assert_eq!(builder.field_types.get(&datetime_field), Some(&FieldType::DateTime));
prop_assert_eq!(builder.field_types.get(×tamp_field), Some(&FieldType::Timestamp));
prop_assert_eq!(builder.field_types.get(&decimal_field), Some(&FieldType::Decimal));
prop_assert_eq!(builder.field_types.get(&blob_field), Some(&FieldType::Blob));
prop_assert_eq!(builder.field_types.get(&text_field), Some(&FieldType::Text));
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_where_and_condition_added(
table_name in table_name_strategy(),
field in field_name_strategy(),
value in any::<i32>()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, value);
prop_assert_eq!(builder.conditions.len(), 1);
}
#[test]
fn prop_where_or_condition_added(
table_name in table_name_strategy(),
field in field_name_strategy(),
value1 in any::<i32>(),
value2 in any::<i32>()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_or(test_field!(&field), crate::CompareOp::Eq, value1)
.where_or(test_field!(&field), crate::CompareOp::Eq, value2);
prop_assert_eq!(builder.conditions.len(), 1);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_in_operator_array_support(
table_name in table_name_strategy(),
field in field_name_strategy(),
values in prop::collection::vec(any::<i32>(), 1..10)
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_in(test_field!(field), values);
prop_assert_eq!(builder.conditions.len(), 1);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_between_operator_boundary_support(
table_name in table_name_strategy(),
field in field_name_strategy(),
start in any::<i32>(),
end in any::<i32>()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_between(test_field!(field), start, end);
prop_assert_eq!(builder.conditions.len(), 1);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_multiple_and_conditions(
table_name in table_name_strategy(),
field in field_name_strategy(),
values in prop::collection::vec(any::<i32>(), 2..5)
) {
let pool = create_test_pool_sync();
let mut builder = QueryBuilder::new(&pool, &table_name, false);
for value in &values {
builder = builder.where_and(test_field!(&field), crate::CompareOp::Eq, *value);
}
prop_assert_eq!(builder.conditions.len(), values.len());
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_join_clause_generation(
table_name in table_name_strategy(),
join_table in table_name_strategy(),
on_condition in "[a-z][a-z0-9_]{0,20}\\.[a-z][a-z0-9_]{0,20} = [a-z][a-z0-9_]{0,20}\\.[a-z][a-z0-9_]{0,20}"
) {
let pool = create_test_pool_sync();
let (left, right) = on_condition.split_once(" = ").expect("测试策略生成等值连接");
let builder_inner = QueryBuilder::new(&pool, &table_name, false)
.join(test_table!(&join_table), test_field!(left), test_field!(right));
prop_assert_eq!(builder_inner.joins.len(), 1);
let builder_left = QueryBuilder::new(&pool, &table_name, false)
.left_join(test_table!(&join_table), test_field!(left), test_field!(right));
prop_assert_eq!(builder_left.joins.len(), 1);
let builder_right = QueryBuilder::new(&pool, &table_name, false)
.right_join(test_table!(&join_table), test_field!(left), test_field!(right));
prop_assert_eq!(builder_right.joins.len(), 1);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_multiple_join_support(
table_name in table_name_strategy(),
join_tables in prop::collection::vec(table_name_strategy(), 1..5)
) {
let pool = create_test_pool_sync();
let mut builder = QueryBuilder::new(&pool, &table_name, false);
for join_table in &join_tables {
let left = format!("{}.id", table_name);
let right = format!("{}.id", join_table);
builder = builder.join(
test_table!(join_table),
test_field!(left),
test_field!(right),
);
}
prop_assert_eq!(builder.joins.len(), join_tables.len());
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_qualified_table_support(
base_table in table_name_strategy(),
join_table in table_name_strategy()
) {
prop_assume!(base_table != join_table);
let pool = create_test_pool_sync();
let base_id = format!("{}.id", base_table);
let base_name = format!("{}.name", base_table);
let join_id = format!("{}.id", join_table);
let builder = QueryBuilder::new(&pool, &base_table, false)
.field(test_field!(&base_id))
.field(test_field!(&base_name))
.join(
test_table!(&join_table),
test_field!(&base_id),
test_field!(&join_id),
);
let sql = builder.to_sql();
prop_assert!(sql.contains(&base_table));
prop_assert!(sql.contains(&join_table));
prop_assert!(sql.contains(" JOIN "));
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_order_by_clause_generation(
table_name in table_name_strategy(),
field in field_name_strategy(),
asc in any::<bool>()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.order(
test_field!(field),
if asc { crate::SortOrder::Asc } else { crate::SortOrder::Desc },
);
prop_assert_eq!(builder.order_by.len(), 1);
prop_assert_eq!(&builder.order_by[0].field, &format!("`{}`", field));
prop_assert_eq!(builder.order_by[0].asc, asc);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_multiple_order_by_support(
table_name in table_name_strategy(),
fields in prop::collection::vec(field_name_strategy(), 1..5)
) {
let pool = create_test_pool_sync();
let mut builder = QueryBuilder::new(&pool, &table_name, false);
for field in &fields {
builder = builder.order(test_field!(field), crate::SortOrder::Asc);
}
prop_assert_eq!(builder.order_by.len(), fields.len());
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_group_by_clause_generation(
table_name in table_name_strategy(),
field in field_name_strategy()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.group(test_field!(field));
prop_assert_eq!(builder.group_by.len(), 1);
prop_assert_eq!(&builder.group_by[0], &format!("`{}`", field));
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_multiple_group_by_support(
table_name in table_name_strategy(),
fields in prop::collection::vec(field_name_strategy(), 1..5)
) {
let pool = create_test_pool_sync();
let mut builder = QueryBuilder::new(&pool, &table_name, false);
for field in &fields {
builder = builder.group(test_field!(field));
}
prop_assert_eq!(builder.group_by.len(), fields.len());
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_to_sql_returns_valid_sql(
table_name in table_name_strategy(),
fields in prop::collection::vec(field_name_strategy(), 0..5),
use_distinct in any::<bool>(),
limit_opt in prop::option::of(1u64..100),
offset_opt in prop::option::of(0u64..100)
) {
let pool = create_test_pool_sync();
let mut builder = QueryBuilder::new(&pool, &table_name, false);
for field in &fields {
builder = builder.field(test_field!(field));
}
if use_distinct {
builder = builder.distinct();
}
if let Some(limit) = limit_opt {
builder = builder.limit(limit);
}
if let Some(offset) = offset_opt {
builder = builder.offset(offset);
}
let sql = builder.to_sql();
prop_assert!(!sql.is_empty(), "SQL 字符串不应为空");
prop_assert!(sql.contains("SELECT"), "SQL 应包含 SELECT 关键字");
prop_assert!(sql.contains("FROM"), "SQL 应包含 FROM 关键字");
prop_assert!(sql.contains(&table_name), "SQL 应包含表名");
if use_distinct {
prop_assert!(sql.contains("DISTINCT"), "SQL 应包含 DISTINCT 关键字");
}
if let Some(limit) = limit_opt {
prop_assert!(sql.contains("LIMIT"), "SQL 应包含 LIMIT 关键字");
prop_assert!(sql.contains(&limit.to_string()), "SQL 应包含 LIMIT 值");
}
if let Some(offset) = offset_opt {
prop_assert!(sql.contains("OFFSET"), "SQL 应包含 OFFSET 关键字");
prop_assert!(sql.contains(&offset.to_string()), "SQL 应包含 OFFSET 值");
}
if !fields.is_empty() {
for field in &fields {
prop_assert!(sql.contains(field), "SQL 应包含字段 {}", field);
}
} else {
prop_assert!(sql.contains("*"), "SQL 应包含 * 表示所有字段");
}
}
#[test]
fn prop_to_sql_with_conditions(
table_name in table_name_strategy(),
field in field_name_strategy(),
value in any::<i32>()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, value);
let sql = builder.to_sql();
prop_assert!(!sql.is_empty());
prop_assert!(sql.contains("SELECT"));
prop_assert!(sql.contains("FROM"));
prop_assert!(sql.contains(&table_name));
prop_assert!(sql.contains("WHERE"), "SQL 应包含 WHERE 关键字");
}
#[test]
fn prop_to_sql_with_joins(
table_name in table_name_strategy(),
join_table in table_name_strategy(),
on_field1 in field_name_strategy(),
on_field2 in field_name_strategy()
) {
let pool = create_test_pool_sync();
let left = format!("{}.{}", table_name, on_field1);
let right = format!("{}.{}", join_table, on_field2);
let builder = QueryBuilder::new(&pool, &table_name, false)
.join(test_table!(&join_table), test_field!(left), test_field!(right));
let sql = builder.to_sql();
prop_assert!(!sql.is_empty());
prop_assert!(sql.contains("SELECT"));
prop_assert!(sql.contains("FROM"));
prop_assert!(sql.contains("JOIN"), "SQL 应包含 JOIN 关键字");
prop_assert!(sql.contains(&join_table), "SQL 应包含连接的表名");
}
#[test]
fn prop_to_sql_with_order_and_group(
table_name in table_name_strategy(),
order_field in field_name_strategy(),
group_field in field_name_strategy(),
asc in any::<bool>()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.order(
test_field!(order_field),
if asc { crate::SortOrder::Asc } else { crate::SortOrder::Desc },
)
.group(test_field!(group_field));
let sql = builder.to_sql();
prop_assert!(!sql.is_empty());
prop_assert!(sql.contains("SELECT"));
prop_assert!(sql.contains("FROM"));
prop_assert!(sql.contains("ORDER BY"), "SQL 应包含 ORDER BY 关键字");
prop_assert!(sql.contains("GROUP BY"), "SQL 应包含 GROUP BY 关键字");
prop_assert!(sql.contains(&order_field), "SQL 应包含排序字段");
prop_assert!(sql.contains(&group_field), "SQL 应包含分组字段");
}
#[test]
fn prop_to_sql_complex_query(
table_name in table_name_strategy(),
fields in prop::collection::vec(field_name_strategy(), 1..3),
join_table in table_name_strategy(),
where_field in field_name_strategy(),
order_field in field_name_strategy(),
group_field in field_name_strategy()
) {
let pool = create_test_pool_sync();
let mut builder = QueryBuilder::new(&pool, &table_name, false);
for field in &fields {
builder = builder.field(test_field!(field));
}
let left = format!("{}.id", table_name);
let right = format!("{}.id", join_table);
builder = builder.join(
test_table!(&join_table),
test_field!(left),
test_field!(right),
);
builder = builder.where_and(test_field!(&where_field), crate::CompareOp::Eq, 1);
builder = builder.order(test_field!(order_field), crate::SortOrder::Asc);
builder = builder.group(test_field!(group_field));
builder = builder.limit(10);
let sql = builder.to_sql();
prop_assert!(!sql.is_empty());
prop_assert!(sql.contains("SELECT"));
prop_assert!(sql.contains("FROM"));
prop_assert!(sql.contains(&table_name));
prop_assert!(sql.contains("JOIN"));
prop_assert!(sql.contains("WHERE"));
prop_assert!(sql.contains("ORDER BY"));
prop_assert!(sql.contains("GROUP BY"));
prop_assert!(sql.contains("LIMIT"));
let select_pos = sql.find("SELECT").unwrap();
let from_pos = sql.find("FROM").unwrap();
let join_pos = sql.find("JOIN").unwrap();
let where_pos = sql.find("WHERE").unwrap();
let group_pos = sql.find("GROUP BY").unwrap();
let order_pos = sql.find("ORDER BY").unwrap();
let limit_pos = sql.find("LIMIT").unwrap();
prop_assert!(select_pos < from_pos, "SELECT 应在 FROM 之前");
prop_assert!(from_pos < join_pos, "FROM 应在 JOIN 之前");
prop_assert!(join_pos < where_pos, "JOIN 应在 WHERE 之前");
prop_assert!(where_pos < group_pos, "WHERE 应在 GROUP BY 之前");
prop_assert!(group_pos < order_pos, "GROUP BY 应在 ORDER BY 之前");
prop_assert!(order_pos < limit_pos, "ORDER BY 应在 LIMIT 之前");
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_sql_injection_prevention_single_quote(
table_name in table_name_strategy(),
field in field_name_strategy(),
malicious_input in ".*'.*"
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, malicious_input.as_str());
let sql = builder.to_sql();
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询(? 占位符)");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(&malicious_input),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
#[test]
fn prop_sql_injection_prevention_semicolon(
table_name in table_name_strategy(),
field in field_name_strategy(),
malicious_input in ".*;.*"
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, malicious_input.as_str());
let sql = builder.to_sql();
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(&malicious_input),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
#[test]
fn prop_sql_injection_prevention_comment(
table_name in table_name_strategy(),
field in field_name_strategy(),
malicious_input in ".*--.*"
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, malicious_input.as_str());
let sql = builder.to_sql();
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(&malicious_input),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
#[test]
fn prop_sql_injection_prevention_drop_table(
table_name in table_name_strategy(),
field in field_name_strategy()
) {
let pool = create_test_pool_sync();
let malicious_input = "'; DROP TABLE users; --";
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, malicious_input);
let sql = builder.to_sql();
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
prop_assert!(!sql.to_uppercase().contains("DROP TABLE"),
"SQL 不应该包含 DROP TABLE 语句");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(malicious_input),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
#[test]
fn prop_sql_injection_prevention_union_select(
table_name in table_name_strategy(),
field in field_name_strategy()
) {
let pool = create_test_pool_sync();
let malicious_input = "' UNION SELECT * FROM passwords --";
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, malicious_input);
let sql = builder.to_sql();
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
let sql_upper = sql.to_uppercase();
let union_count = sql_upper.matches("UNION").count();
prop_assert_eq!(union_count, 0, "SQL 不应该包含 UNION 注入");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(malicious_input),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
#[test]
fn prop_sql_injection_prevention_or_always_true(
table_name in table_name_strategy(),
field in field_name_strategy()
) {
let pool = create_test_pool_sync();
let malicious_input = "' OR '1'='1";
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, malicious_input);
let sql = builder.to_sql();
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(malicious_input),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
let or_count = where_clause.matches(" OR ").count();
prop_assert_eq!(or_count, 0, "不应该因为用户输入而产生 OR 条件");
}
#[test]
fn prop_sql_injection_prevention_multiple_special_chars(
table_name in table_name_strategy(),
field in field_name_strategy(),
malicious_input in "[a-z0-9]*[';\"\\-][a-z0-9]*[';\"\\-][a-z0-9]*"
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Eq, malicious_input.as_str());
let sql = builder.to_sql();
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(&malicious_input),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
#[test]
fn prop_sql_injection_prevention_in_operator(
table_name in table_name_strategy(),
field in field_name_strategy(),
malicious_values in prop::collection::vec(".*[';].*", 1..5)
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_in(test_field!(field), malicious_values.clone());
let sql = builder.to_sql();
prop_assert!(sql.contains("IN"), "SQL 应该包含 IN 操作符");
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
let placeholder_count = sql.matches("?").count();
prop_assert!(placeholder_count >= malicious_values.len(),
"每个 IN 值都应该有对应的参数占位符");
for malicious_value in &malicious_values {
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(malicious_value),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
}
#[test]
fn prop_sql_injection_prevention_like_operator(
table_name in table_name_strategy(),
field in field_name_strategy(),
malicious_pattern in ".*[';].*"
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field), crate::CompareOp::Like, malicious_pattern.as_str());
let sql = builder.to_sql();
prop_assert!(sql.contains("LIKE"), "SQL 应该包含 LIKE 操作符");
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
prop_assert!(!where_clause.contains(&malicious_pattern),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
#[test]
fn prop_sql_injection_prevention_between_operator(
table_name in table_name_strategy(),
field in field_name_strategy(),
malicious_start in ".*[';].*",
malicious_end in ".*[';].*"
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_between(test_field!(field), malicious_start.as_str(), malicious_end.as_str());
let sql = builder.to_sql();
prop_assert!(sql.contains("BETWEEN"), "SQL 应该包含 BETWEEN 操作符");
prop_assert!(sql.contains("?"), "SQL 应该使用参数化查询");
let where_clause = sql.split("WHERE").nth(1).unwrap_or("");
let placeholder_count = where_clause.matches("?").count();
prop_assert!(placeholder_count >= 2, "BETWEEN 应该有两个参数占位符");
prop_assert!(!where_clause.contains(&malicious_start),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
prop_assert!(!where_clause.contains(&malicious_end),
"WHERE 子句不应该直接包含用户输入的恶意字符串");
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_find_adds_limit_one(
table_name in table_name_strategy(),
field in field_name_strategy(),
value in any::<i32>()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.field(test_field!(field))
.where_and(test_field!(&field), crate::CompareOp::Eq, value)
.limit(1);
let sql = builder.to_sql();
prop_assert!(sql.contains("LIMIT 1"),
"find() 方法应该自动添加 LIMIT 1 到查询中");
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_count_aggregation_function(
table_name in table_name_strategy()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.expr(crate::SelectExpr::count_all());
let sql = builder.to_sql();
prop_assert!(
sql.contains("COUNT(*)") || sql.contains("COUNT("),
"count() 方法应该生成包含 COUNT(*) 或 COUNT(field) 的 SQL 语句,实际 SQL: {}",
sql
);
prop_assert!(
sql.to_uppercase().contains("SELECT"),
"count() 方法应该生成 SELECT 语句,实际 SQL: {}",
sql
);
prop_assert!(
sql.contains(&format!("FROM `{}`", table_name)),
"count() 方法应该包含正确的表名,实际 SQL: {}",
sql
);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_count_with_where_condition(
table_name in table_name_strategy(),
field_name in field_name_strategy(),
field_value in 1i32..1000i32,
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&field_name), crate::CompareOp::Eq, field_value)
.expr(crate::SelectExpr::count_all());
let sql = builder.to_sql();
prop_assert!(
sql.contains("COUNT(*)"),
"带条件的 count() 查询应该包含 COUNT(*),实际 SQL: {}",
sql
);
prop_assert!(
sql.to_uppercase().contains("WHERE"),
"带条件的 count() 查询应该包含 WHERE 子句,实际 SQL: {}",
sql
);
prop_assert!(
sql.contains(&format!("FROM `{}`", table_name)),
"count() 方法应该包含正确的表名,实际 SQL: {}",
sql
);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_count_specific_field(
table_name in table_name_strategy(),
field_name in field_name_strategy(),
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.expr(crate::SelectExpr::count(test_field!(&field_name)));
let sql = builder.to_sql();
prop_assert!(
sql.contains(&format!("COUNT(`{}`)", field_name)),
"COUNT 特定字段应该包含 COUNT(field_name),实际 SQL: {}",
sql
);
prop_assert!(
sql.to_uppercase().contains("SELECT"),
"COUNT 查询应该是 SELECT 语句,实际 SQL: {}",
sql
);
prop_assert!(
sql.contains(&format!("FROM `{}`", table_name)),
"COUNT 查询应该包含正确的表名,实际 SQL: {}",
sql
);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_sum_aggregation_function(
table_name in table_name_strategy(),
field in field_name_strategy()
) {
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.expr(crate::SelectExpr::sum(test_field!(&field)).cast_double());
let sql = builder.to_sql();
prop_assert!(
sql.contains("SUM("),
"sum() 方法应该生成包含 SUM(field) 的 SQL 语句,实际 SQL: {}",
sql
);
prop_assert!(
sql.contains(&field),
"sum() 方法生成的 SQL 应该包含指定的字段名 {},实际 SQL: {}",
field,
sql
);
prop_assert!(
sql.to_uppercase().contains("SELECT"),
"sum() 方法应该生成 SELECT 语句,实际 SQL: {}",
sql
);
prop_assert!(
sql.contains(&format!("FROM `{}`", table_name)),
"sum() 方法应该包含正确的表名,实际 SQL: {}",
sql
);
prop_assert!(
sql.to_uppercase().contains("CAST"),
"sum() 方法应该使用 CAST 转换结果为 DOUBLE,实际 SQL: {}",
sql
);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_sum_with_where_condition(
table_name in table_name_strategy(),
sum_field in field_name_strategy(),
where_field in field_name_strategy(),
where_value in 1i32..1000i32,
) {
prop_assume!(sum_field != where_field);
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&where_field), crate::CompareOp::Eq, where_value)
.expr(crate::SelectExpr::sum(test_field!(&sum_field)).cast_double());
let sql = builder.to_sql();
prop_assert!(
sql.contains("SUM("),
"带条件的 sum() 查询应该包含 SUM(field),实际 SQL: {}",
sql
);
prop_assert!(
sql.contains(&sum_field),
"sum() 方法应该包含求和字段名 {},实际 SQL: {}",
sum_field,
sql
);
prop_assert!(
sql.to_uppercase().contains("WHERE"),
"带条件的 sum() 查询应该包含 WHERE 子句,实际 SQL: {}",
sql
);
prop_assert!(
sql.contains(&format!("FROM `{}`", table_name)),
"sum() 方法应该包含正确的表名,实际 SQL: {}",
sql
);
}
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(100))]
#[test]
fn prop_sum_with_multiple_conditions(
table_name in table_name_strategy(),
sum_field in field_name_strategy(),
where_field1 in field_name_strategy(),
where_field2 in field_name_strategy(),
value1 in 1i32..1000i32,
value2 in 1i32..1000i32,
) {
prop_assume!(sum_field != where_field1);
prop_assume!(sum_field != where_field2);
prop_assume!(where_field1 != where_field2);
let pool = create_test_pool_sync();
let builder = QueryBuilder::new(&pool, &table_name, false)
.where_and(test_field!(&where_field1), crate::CompareOp::Eq, value1)
.where_and(test_field!(&where_field2), crate::CompareOp::Gt, value2)
.expr(crate::SelectExpr::sum(test_field!(&sum_field)).cast_double());
let sql = builder.to_sql();
prop_assert!(
sql.contains("SUM("),
"多条件 sum() 查询应该包含 SUM(field),实际 SQL: {}",
sql
);
prop_assert!(
sql.contains(&sum_field),
"sum() 方法应该包含求和字段名 {},实际 SQL: {}",
sum_field,
sql
);
prop_assert!(
sql.to_uppercase().contains("WHERE"),
"多条件查询应该包含 WHERE 子句,实际 SQL: {}",
sql
);
prop_assert!(
sql.to_uppercase().contains(" AND "),
"多个 where_and 条件应该用 AND 连接,实际 SQL: {}",
sql
);
}
}
#[test]
fn typed_compare_operators_preserve_condition_semantics() {
let pool = make_sync_test_pool();
for operator in [
crate::CompareOp::Eq,
crate::CompareOp::Ne,
crate::CompareOp::Gt,
crate::CompareOp::Lt,
crate::CompareOp::Gte,
crate::CompareOp::Lte,
crate::CompareOp::Like,
] {
let builder = QueryBuilder::new(pool, "users", false).where_and(
yang_db::field!("age"),
operator,
18i64,
);
assert_eq!(builder.conditions.len(), 1);
}
}
#[test]
fn typed_where_or_having_and_chaining_work() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users", false)
.where_and(yang_db::field!("age"), crate::CompareOp::Gt, 18i64)
.where_and(yang_db::field!("status"), crate::CompareOp::Eq, 1i64)
.where_or(yang_db::field!("status"), crate::CompareOp::Eq, 2i64)
.group(yang_db::field!("age"))
.having_cond(yang_db::field!("age"), crate::CompareOp::Gt, 0i64);
assert_eq!(builder.conditions.len(), 1);
assert_eq!(builder.having_clause.len(), 1);
}
proptest! {
#![proptest_config(ProptestConfig::with_cases(500))]
#[test]
fn prop_batch_size_chunk_count(
n in 0usize..=1000,
b in 1usize..=200
) {
let data: Vec<u32> = (0..n as u32).collect();
let actual_chunk_count = if data.is_empty() {
0
} else {
data.chunks(b).count()
};
let expected_chunk_count = if n == 0 {
0
} else {
n.div_ceil(b) };
prop_assert_eq!(
actual_chunk_count,
expected_chunk_count,
"n={} 条记录,批次大小 b={},实际分批数 {} 应等于 ceil(n/b)={}",
n, b, actual_chunk_count, expected_chunk_count
);
for chunk in data.chunks(b) {
prop_assert!(
chunk.len() <= b,
"每个分批的大小 {} 不应超过批次大小 {}",
chunk.len(), b
);
}
let total_records: usize = data.chunks(b).map(|c| c.len()).sum();
prop_assert_eq!(
total_records,
n,
"所有分批的记录总数 {} 应等于原始记录数 {}",
total_records, n
);
}
}
#[tokio::test]
async fn test_insert_batch_with_size_zero_batch_size_returns_error() {
let pool = MySqlPoolOptions::new()
.max_connections(1)
.connect_lazy("mysql://root:111111@localhost:3306/test")
.expect("合法测试数据库 URL");
let builder = QueryBuilder::new(&pool, "users", false);
let data = vec![serde_json::json!({"name": "张三"})];
let result = builder.insert_batch_with_size(&data, 0).await;
assert!(
matches!(result, Err(crate::DbError::SerializationError(_))),
"batch_size 为 0 应返回 SerializationError,实际结果: {:?}",
result.as_ref().map(|_| "Ok")
);
if let Err(crate::DbError::SerializationError(msg)) = result {
assert!(
msg.contains("batch_size") || msg.contains('0'),
"错误消息应提及 batch_size 不能为 0,实际消息: {}",
msg
);
}
}
#[test]
fn test_batch_chunk_logic_boundary_cases() {
let data: Vec<u32> = (0..10).collect();
let chunks: Vec<_> = data.chunks(10).collect();
assert_eq!(chunks.len(), 1, "数据量等于批次大小时应只有 1 个分批");
assert_eq!(chunks[0].len(), 10);
let data: Vec<u32> = (0..5).collect();
let chunks: Vec<_> = data.chunks(10).collect();
assert_eq!(chunks.len(), 1, "数据量小于批次大小时应只有 1 个分批");
assert_eq!(chunks[0].len(), 5);
let data: Vec<u32> = (0..20).collect();
let chunks: Vec<_> = data.chunks(5).collect();
assert_eq!(chunks.len(), 4, "20 条记录按批次大小 5 分批应得到 4 个分批");
for chunk in &chunks {
assert_eq!(chunk.len(), 5);
}
let data: Vec<u32> = (0..11).collect();
let chunks: Vec<_> = data.chunks(5).collect();
assert_eq!(chunks.len(), 3, "11 条记录按批次大小 5 分批应得到 3 个分批");
assert_eq!(chunks[0].len(), 5);
assert_eq!(chunks[1].len(), 5);
assert_eq!(chunks[2].len(), 1, "最后一批应只有 1 条记录");
let data: Vec<u32> = (0..5).collect();
let chunks: Vec<_> = data.chunks(1).collect();
assert_eq!(chunks.len(), 5, "批次大小为 1 时,分批数应等于记录数");
}
}