use crate::postgres::condition::{Condition, SqlValue};
use crate::postgres::field::{FieldType, JoinClause, OrderClause};
use sqlx::postgres::PgPool;
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.clone()),
SqlValue::DateTime(dt) => $query.bind(*dt),
SqlValue::Timestamp(ts) => $query.bind(*ts),
}
};
}
const INSERT_BATCH_SIZE: usize = 500;
const UPDATE_BATCH_SIZE: usize = 1000;
const MAX_BIND_PARAMS: usize = 65_535;
fn derive_update_batch_size(field_count: usize, requested: usize) -> usize {
let per_record = field_count.saturating_mul(2).saturating_add(1);
if per_record == 0 {
return requested;
}
(MAX_BIND_PARAMS / per_record).max(1).min(requested)
}
fn update_field_count(records: &[serde_json::Value], where_field: &str) -> usize {
records
.first()
.and_then(|r| r.as_object())
.map(|obj| obj.keys().filter(|k| k.as_str() != where_field).count())
.unwrap_or(0)
}
fn push_placeholder(params: &mut Vec<SqlValue>, value: SqlValue) -> String {
params.push(value);
format!("${}", params.len())
}
#[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::postgres::condition::render_condition_checked(condition, self.params.len())?;
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]) -> Result<(), crate::error::DbError> {
use crate::postgres::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(&super::identifier::quote_identifier(&join.table)?);
self.append(" ON ");
self.append(&join.on);
}
Ok(())
}
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 {
for (i, c) in conditions.iter().enumerate() {
if i > 0 {
self.sql.push_str(" AND ");
}
self.append_condition(c.clone())?;
}
}
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)?);
let sql_value = self.json_value_to_sql_value(value, field_types.get(key))?;
match sql_value {
SqlValue::Null => placeholders.push("NULL".to_string()),
v => placeholders.push(push_placeholder(&mut self.params, v)),
}
}
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(", ");
}
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))?;
match sql_value {
SqlValue::Null => self.sql.push_str("NULL"),
v => {
let ph = push_placeholder(&mut self.params, v);
self.sql.push_str(&ph);
}
}
}
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 ");
for (i, (key, value)) in obj.iter().enumerate() {
if i > 0 {
self.sql.push_str(", ");
}
let sql_value = self.json_value_to_sql_value(value, field_types.get(key))?;
self.sql
.push_str(&super::identifier::quote_identifier(key)?);
self.sql.push_str(" = ");
match sql_value {
SqlValue::Null => self.sql.push_str("NULL"),
v => {
let ph = push_placeholder(&mut self.params, v);
self.sql.push_str(&ph);
}
}
}
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(' ');
let placeholder = push_placeholder(&mut self.params, SqlValue::Int(amount));
self.sql.push_str(&placeholder);
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('=');
match id_sql_val {
SqlValue::Null => self.sql.push_str("NULL"),
v => {
let ph = push_placeholder(&mut self.params, v);
self.sql.push_str(&ph);
}
}
self.sql.push_str(" THEN ");
match field_sql_val {
SqlValue::Null => self.sql.push_str("NULL"),
v => {
let ph = push_placeholder(&mut self.params, v);
self.sql.push_str(&ph);
}
}
self.sql.push(' ');
}
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(',');
}
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))?;
match id_sql_val {
SqlValue::Null => self.sql.push_str("NULL"),
v => {
let ph = push_placeholder(&mut self.params, v);
self.sql.push_str(&ph);
}
}
}
self.sql.push(')');
Ok(())
}
pub(crate) fn build_upsert(
&mut self,
table: &str,
data: &serde_json::Value,
field_types: &HashMap<String, FieldType>,
conflict_columns: &[String],
) -> 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 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 (");
for (i, field) in fields.iter().enumerate() {
if i > 0 {
self.sql.push_str(", ");
}
let val = obj.get(field.as_str()).unwrap_or(&serde_json::Value::Null);
let sql_value = self.json_value_to_sql_value(val, field_types.get(field.as_str()))?;
match sql_value {
SqlValue::Null => self.sql.push_str("NULL"),
v => {
let ph = push_placeholder(&mut self.params, v);
self.sql.push_str(&ph);
}
}
}
self.sql.push(')');
if conflict_columns.is_empty() {
self.sql.push_str(" ON CONFLICT DO NOTHING");
return Ok(());
}
let quoted_conflict: Result<Vec<String>, crate::error::DbError> = conflict_columns
.iter()
.map(|c| super::identifier::quote_identifier(c))
.collect();
let quoted_conflict = quoted_conflict?;
self.sql.push_str(" ON CONFLICT (");
self.sql.push_str("ed_conflict.join(", "));
self.sql.push_str(") DO UPDATE SET ");
let mut first = true;
for (idx, f) in fields.iter().enumerate() {
if conflict_columns.iter().any(|c| c == f) {
continue;
}
if !first {
self.sql.push_str(", ");
}
first = false;
let qf = "ed_fields[idx];
self.sql.push_str(qf);
self.sql.push_str(" = EXCLUDED.");
self.sql.push_str(qf);
}
if first {
let first_conflict = "ed_conflict[0];
self.sql.push_str(first_conflict);
self.sql.push_str(" = EXCLUDED.");
self.sql.push_str(first_conflict);
}
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 PgPool),
Transaction(&'a mut crate::postgres::Transaction),
}
pub struct QueryBuilder<'a> {
executor: QueryExecutor<'a>,
table: String,
fields: Vec<String>,
conditions: Vec<Condition>,
joins: Vec<JoinClause>,
order_by: Vec<OrderClause>,
group_by: Vec<String>,
having_clause: Vec<Condition>,
limit: Option<u64>,
offset: Option<u64>,
distinct: bool,
unions: Vec<(UnionOperator, Box<QueryBuilder<'a>>)>,
field_types: HashMap<String, FieldType>,
conflict_columns: Vec<String>,
returning: String,
enable_logging: bool,
}
impl<'a> QueryBuilder<'a> {
pub(crate) fn new(pool: &'a PgPool, 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(),
conflict_columns: vec!["id".to_string()],
returning: "id".to_string(),
enable_logging,
}
}
pub(crate) fn new_transaction(
transaction: &'a mut crate::postgres::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(),
conflict_columns: vec!["id".to_string()],
returning: "id".to_string(),
enable_logging,
}
}
pub fn field(mut self, field: &crate::FieldRef) -> Self {
self.fields.push(field.postgres_quoted().to_string());
self
}
pub fn expr(mut self, expression: crate::SelectExpr) -> Self {
self.fields.push(expression.postgres_sql());
self
}
pub fn fields(mut self, fields: &[&crate::FieldRef]) -> Self {
for field in fields {
self.fields.push(field.postgres_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 on_conflict(mut self, columns: &[&crate::FieldRef]) -> Self {
self.conflict_columns = columns
.iter()
.map(|column| column.as_str().to_string())
.collect();
self
}
pub fn returning(mut self, column: &crate::FieldRef) -> Self {
self.returning = column.as_str().to_string();
self
}
pub fn where_and<V>(
mut self,
field: &crate::FieldRef,
op: crate::CompareOp,
value: V,
) -> Result<Self, crate::error::DbError>
where
V: Into<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 => match value {
SqlValue::String(value) => Condition::Like(field, value),
_ => {
return Err(crate::error::DbError::UnsupportedOperator(
"LIKE 仅支持字符串模式值".to_string(),
))
}
},
};
self.conditions.push(condition);
Ok(self)
}
pub fn where_or<V>(
mut self,
field: &crate::FieldRef,
op: crate::CompareOp,
value: V,
) -> Result<Self, crate::error::DbError>
where
V: Into<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 => match value {
SqlValue::String(value) => Condition::Like(field, value),
_ => {
return Err(crate::error::DbError::UnsupportedOperator(
"LIKE 仅支持字符串模式值".to_string(),
))
}
},
};
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);
}
Ok(self)
}
pub fn where_in<V>(mut self, field: &crate::FieldRef, values: Vec<V>) -> Self
where
V: Into<SqlValue>,
{
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::postgres::Subquery) -> Self {
self.conditions.push(Condition::Exists(Box::new(subquery)));
self
}
pub fn where_not_exists(mut self, subquery: crate::postgres::Subquery) -> Self {
self.conditions
.push(Condition::NotExists(Box::new(subquery)));
self
}
pub fn where_in_subquery(
mut self,
field: &crate::FieldRef,
subquery: crate::postgres::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<SqlValue>,
{
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,
) -> Result<Self, crate::error::DbError>
where
V: Into<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 => match value {
SqlValue::String(value) => Condition::Like(field, value),
_ => {
return Err(crate::error::DbError::UnsupportedOperator(
"LIKE 仅支持字符串模式值".to_string(),
))
}
},
};
self.having_clause.push(condition);
Ok(self)
}
pub fn join(
mut self,
table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
use crate::postgres::field::JoinType;
self.joins.push(JoinClause {
join_type: JoinType::Inner,
table: table.as_str().to_string(),
on: format!("{} = {}", left.postgres_quoted(), right.postgres_quoted()),
});
self
}
pub fn left_join(
mut self,
table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
use crate::postgres::field::JoinType;
self.joins.push(JoinClause {
join_type: JoinType::Left,
table: table.as_str().to_string(),
on: format!("{} = {}", left.postgres_quoted(), right.postgres_quoted()),
});
self
}
pub fn right_join(
mut self,
table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
use crate::postgres::field::JoinType;
self.joins.push(JoinClause {
join_type: JoinType::Right,
table: table.as_str().to_string(),
on: format!("{} = {}", left.postgres_quoted(), right.postgres_quoted()),
});
self
}
pub fn order(mut self, field: &crate::FieldRef, order: crate::SortOrder) -> Self {
self.order_by.push(OrderClause {
field: field.postgres_quoted().to_string(),
asc: order.is_ascending(),
});
self
}
pub fn group(mut self, field: &crate::FieldRef) -> Self {
self.group_by.push(field.postgres_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()
}
}
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
)]
pub async fn find<T>(mut self) -> Result<Option<T>, crate::error::DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + 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) => Ok(row),
Err(e) => {
log::error!("find() 查询失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
)]
pub async fn select<T>(self) -> Result<Vec<T>, crate::error::DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + 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) => 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::Postgres> + sqlx::Type<sqlx::Postgres> + 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))
}
}
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
)]
pub async fn value<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
where
T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + 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
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
)]
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))
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
)]
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 PRECISION)");
self.fetch_scalar::<Option<f64>>(&sum_expr)
.await
.map(Option::flatten)
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
)]
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 PRECISION)");
self.fetch_scalar::<Option<f64>>(&avg_expr)
.await
.map(Option::flatten)
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
)]
pub async fn min<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
where
T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + 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)
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
)]
pub async fn max<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
where
T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + 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)
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
)]
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 returning_quoted = super::identifier::quote_identifier(&self.returning)?;
let sql = format!(
"{} RETURNING CAST({} AS BIGINT)",
generator.get_sql(),
returning_quoted
);
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 insert() SQL: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query_scalar::<_, i64>(&sql);
for param in params {
query = bind_scalar_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => query.fetch_one(pool).await,
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.fetch_one(&mut *connection).await
}
};
match result {
Ok(id) => {
if self.enable_logging {
log::debug!("insert() 成功,插入 ID: {}", id);
}
u64::try_from(id)
.map_err(|_| crate::error::DbError::InvalidArgument("返回主键为负".into()))
}
Err(e) => {
log::error!("insert() 失败: {}", e);
Err(crate::error::DbError::from(e))
}
}
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
)]
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
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
)]
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 chunks: Vec<&[T]> = data.chunks(batch_size).collect();
if chunks.len() == 1 {
let enable_logging = self.enable_logging;
let affected = self.insert_chunk(chunks[0]).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 chunks.iter().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 chunks.iter().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))
}
}
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
)]
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))
}
}
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
)]
pub async fn increment(
self,
field: &crate::FieldRef,
amount: i64,
) -> Result<u64, crate::error::DbError> {
self.execute_arithmetic_update(field, amount, ArithmeticOperator::Add)
.await
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
)]
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())
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "delete", db.collection = %self.table, otel.kind = "client")
)]
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))
}
}
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
)]
pub async fn update_batch<T>(
self,
records: &[T],
where_field: &crate::FieldRef,
) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
self.update_batch_with_size(records, where_field, UPDATE_BATCH_SIZE)
.await
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
)]
pub async fn update_batch_with_size<T>(
self,
records: &[T],
where_field: &crate::FieldRef,
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 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 field_count = update_field_count(&json_records, where_field.as_str());
let per_record = field_count.saturating_mul(2).saturating_add(1);
if per_record > MAX_BIND_PARAMS {
return Err(crate::error::DbError::InvalidArgument(format!(
"批量更新单行需要 {per_record} 个占位符,超过协议上限 {MAX_BIND_PARAMS},请减少更新列数"
)));
}
let batch_size = derive_update_batch_size(field_count, batch_size);
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(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(batch_size) {
total += execute_update_chunk(
&mut *connection,
&self.table,
chunk,
where_field,
&self.field_types,
)
.await?;
}
}
}
Ok(total)
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "upsert", db.collection = %self.table, otel.kind = "client")
)]
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,
&self.conflict_columns,
)?;
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)
}
}
fn bind_execute_param<'q>(
query: sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>,
param: &SqlValue,
) -> sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments> {
bind_value_match!(query, param)
}
pub(crate) fn bind_param<'q, T>(
query: sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
param: &SqlValue,
) -> sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
bind_value_match!(query, param)
}
fn bind_scalar_param<'q, C>(
query: sqlx::query::QueryScalar<'q, sqlx::Postgres, C, sqlx::postgres::PgArguments>,
param: &SqlValue,
) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, C, sqlx::postgres::PgArguments>
where
C: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
{
bind_value_match!(query, param)
}
#[allow(dead_code)]
fn bind_scalar_param_option<'q, T>(
query: sqlx::query::QueryScalar<'q, sqlx::Postgres, Option<T>, sqlx::postgres::PgArguments>,
param: &SqlValue,
) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, Option<T>, sqlx::postgres::PgArguments>
where
T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
{
bind_value_match!(query, param)
}
async fn execute_insert_chunk<T>(
connection: &mut sqlx::PgConnection,
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::PgConnection,
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(_)
));
}
}
#[cfg(test)]
#[allow(deprecated)]
#[allow(clippy::unwrap_used, clippy::expect_used)]
mod tests {
use super::*;
use crate::postgres::Subquery;
use sqlx::postgres::PgPoolOptions;
fn make_sync_test_pool() -> &'static PgPool {
use std::sync::OnceLock;
static RT: OnceLock<tokio::runtime::Runtime> = OnceLock::new();
static POOL: OnceLock<PgPool> = 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 {
PgPoolOptions::new()
.max_connections(1)
.connect_lazy("postgres://postgres:postgres@localhost:5432/test")
.expect("无法解析测试数据库 URL")
})
})
}
#[test]
fn test_controlled_subqueries_preserve_nested_placeholder_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,
)
.expect("固定受控条件应合法")
.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\" = $1 AND EXISTS (SELECT \"id\" FROM \"orders\" WHERE \"orders\".\"user_id\" = \"users\".\"id\" AND \"orders\".\"status\" = $2) AND \"users\".\"id\" IN (SELECT \"user_id\" FROM \"memberships\" WHERE \"memberships\".\"active\" = $3) AND NOT EXISTS (SELECT \"id\" FROM \"bans\" WHERE \"bans\".\"user_id\" = \"users\".\"id\" AND \"bans\".\"reason\" = $4))"
);
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_numbered_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)
.expect("固定受控条件应合法")
.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)
.expect("固定受控条件应合法")
.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\" = $1 UNION ALL (SELECT \"id\", \"kind\" FROM \"archived_users\" WHERE \"tenant_id\" = $2 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_numbered() {
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)
.expect("固定受控条件应合法")
.limit(1);
let (sql, params) = builder
.render_for_transaction(Some(crate::RowLock::ForUpdate))
.expect("事务锁查询应可渲染");
assert_eq!(
sql,
"SELECT \"balance\" FROM \"accounts\" WHERE \"id\" = $1 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 = PgPoolOptions::new()
.max_connections(1)
.connect_lazy("postgres://postgres:postgres@localhost:5432/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\" + $1 WHERE \"id\" = $2"
);
assert!(matches!(
generator.get_params(),
[SqlValue::Int(-3), SqlValue::Int(9)]
));
}
#[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,
)
.expect("固定受控条件应合法")
.group(yang_db::field!("users.id"))
.having_cond(
yang_db::field!("users.score"),
yang_db::CompareOp::Gt,
10i64,
)
.expect("固定受控条件应合法");
let sql = builder
.try_to_sql()
.expect("合法的两段限定条件字段应生成 SQL");
assert!(sql.contains("WHERE \"users\".\"status\" = $1"));
assert!(sql.contains("HAVING \"users\".\"score\" > $2"));
}
#[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)
.expect("固定受控条件应合法");
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;"));
}
fn empty_types() -> HashMap<String, FieldType> {
HashMap::new()
}
#[test]
fn test_sql_generator_new_empty() {
let generator = SqlGenerator::new();
assert_eq!(generator.get_sql(), "");
assert_eq!(generator.get_params().len(), 0);
}
#[test]
fn test_build_insert_uses_dollar_placeholders() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"name": "张三", "age": 25});
g.build_insert("users", &data, &empty_types()).unwrap();
let sql = g.get_sql();
assert_eq!(
sql,
"INSERT INTO \"users\" (\"age\", \"name\") VALUES ($1, $2)"
);
assert!(!sql.contains("RETURNING"));
assert_eq!(g.get_params().len(), 2);
}
#[test]
fn test_build_insert_inlines_null_without_placeholder() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"name": "张三", "deleted_at": null});
g.build_insert("users", &data, &empty_types()).unwrap();
let sql = g.get_sql();
assert_eq!(
sql,
"INSERT INTO \"users\" (\"deleted_at\", \"name\") VALUES (NULL, $1)"
);
assert_eq!(g.get_params().len(), 1);
}
#[test]
fn test_build_update_set_then_where_numbering_continues() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"name": "李四", "age": 30});
let conditions = vec![Condition::Eq("id".to_string(), SqlValue::Int(1))];
g.build_update("users", &data, &empty_types(), &conditions)
.unwrap();
let sql = g.get_sql();
assert_eq!(
sql,
"UPDATE \"users\" SET \"age\" = $1, \"name\" = $2 WHERE \"id\" = $3"
);
assert_eq!(g.get_params().len(), 3);
}
#[test]
fn test_build_update_inlines_null_in_set() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"description": serde_json::Value::Null, "name": "张三"});
let conditions = vec![Condition::Eq("id".to_string(), SqlValue::Int(1))];
g.build_update("users", &data, &empty_types(), &conditions)
.unwrap();
let sql = g.get_sql();
assert_eq!(
sql,
"UPDATE \"users\" SET \"description\" = NULL, \"name\" = $1 WHERE \"id\" = $2"
);
assert_eq!(g.get_params().len(), 2);
}
#[test]
fn test_build_delete_dollar_placeholder() {
let mut g = SqlGenerator::new();
let conditions = vec![Condition::Eq("id".to_string(), SqlValue::Int(7))];
g.build_delete("users", &conditions).unwrap();
assert_eq!(g.get_sql(), "DELETE FROM \"users\" WHERE \"id\" = $1");
assert_eq!(g.get_params().len(), 1);
}
#[test]
fn test_build_delete_requires_where() {
let mut g = SqlGenerator::new();
let err = g.build_delete("users", &[]).unwrap_err();
assert!(matches!(err, crate::error::DbError::MissingWhereClause));
}
#[test]
fn test_build_upsert_on_conflict_excluded() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"id": 1, "name": "张三", "email": "z@example.com"});
let conflict = vec!["id".to_string()];
g.build_upsert("users", &data, &empty_types(), &conflict)
.unwrap();
let sql = g.get_sql();
assert!(sql.starts_with(
"INSERT INTO \"users\" (\"email\", \"id\", \"name\") VALUES ($1, $2, $3)"
));
assert!(sql.contains("ON CONFLICT (\"id\") DO UPDATE SET"));
assert!(sql.contains("\"name\" = EXCLUDED.\"name\""));
assert!(sql.contains("\"email\" = EXCLUDED.\"email\""));
assert!(!sql.contains("\"id\" = EXCLUDED.\"id\""));
assert_eq!(g.get_params().len(), 3);
}
#[test]
fn test_build_upsert_empty_conflict_do_nothing() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"name": "张三"});
g.build_upsert("users", &data, &empty_types(), &[]).unwrap();
assert!(g.get_sql().contains("ON CONFLICT DO NOTHING"));
}
#[test]
fn test_build_insert_batch_sequential_numbering() {
let mut g = SqlGenerator::new();
let list = vec![
serde_json::json!({"name": "a", "age": 1}),
serde_json::json!({"name": "b", "age": 2}),
];
g.build_insert_batch("users", &list, &empty_types())
.unwrap();
let sql = g.get_sql();
assert_eq!(
sql,
"INSERT INTO \"users\" (\"age\", \"name\") VALUES ($1, $2), ($3, $4)"
);
assert_eq!(g.get_params().len(), 4);
}
#[test]
fn test_sum_expr_uses_double_precision() {
let pool = make_sync_test_pool();
let sql = QueryBuilder::new(pool, "orders", false)
.expr(crate::SelectExpr::sum(yang_db::field!("amount")).cast_double())
.to_sql();
assert!(sql.contains("DOUBLE PRECISION"));
assert!(sql.contains("FROM \"orders\""));
}
#[test]
fn test_avg_expr_uses_double_precision() {
let expr = format!("CAST(AVG({}) AS DOUBLE PRECISION)", "score");
assert!(expr.contains("DOUBLE PRECISION"));
}
#[test]
fn test_to_sql_select_basic() {
let pool = make_sync_test_pool();
let sql = QueryBuilder::new(pool, "users", false)
.field(yang_db::field!("id"))
.field(yang_db::field!("name"))
.to_sql();
assert_eq!(sql, "SELECT \"id\", \"name\" FROM \"users\"");
}
#[test]
fn test_to_sql_where_uses_dollar() {
let pool = make_sync_test_pool();
let sql = QueryBuilder::new(pool, "users", false)
.where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 1)
.expect("固定受控条件应合法")
.to_sql();
assert_eq!(sql, "SELECT * FROM \"users\" WHERE \"id\" = $1");
}
#[test]
fn test_default_returning_and_conflict_columns() {
let pool = make_sync_test_pool();
let qb = QueryBuilder::new(pool, "users", false);
assert_eq!(qb.returning, "id");
assert_eq!(qb.conflict_columns, vec!["id".to_string()]);
let qb2 = qb
.returning(yang_db::field!("uid"))
.on_conflict(&[yang_db::field!("email"), yang_db::field!("tenant_id")]);
assert_eq!(qb2.returning, "uid");
assert_eq!(
qb2.conflict_columns,
vec!["email".to_string(), "tenant_id".to_string()]
);
}
}