use crate::postgres::condition::{ComparisonOperator, Condition, SqlValue};
use crate::postgres::field::{FieldType, OrderClause};
use sqlx::postgres::PgPool;
use sqlx::{TypeInfo, ValueRef};
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),
SqlValue::Decimal(decimal) => $query.bind(decimal.clone()),
SqlValue::Uuid(uuid) => $query.bind(*uuid),
SqlValue::DateTimeTz(dt) => $query.bind(*dt),
SqlValue::Date(date) => $query.bind(*date),
SqlValue::Time(time) => $query.bind(*time),
SqlValue::Interval(micros) => $query.bind(sqlx::postgres::types::PgInterval {
months: 0,
days: 0,
microseconds: *micros,
}),
SqlValue::Inet(s)
| SqlValue::Cidr(s)
| SqlValue::MacAddr(s)
| SqlValue::MacAddr8(s)
| SqlValue::Range(s)
| SqlValue::Multirange(s) => $query.bind(s.clone()),
SqlValue::Array(values) => match values
.iter()
.find(|value| !matches!(value, SqlValue::Null))
{
Some(SqlValue::Bool(_)) => $query.bind(
values
.iter()
.map(array_element_bool)
.collect::<Vec<Option<bool>>>(),
),
Some(SqlValue::Int(_)) => $query.bind(
values
.iter()
.map(array_element_i64)
.collect::<Vec<Option<i64>>>(),
),
Some(SqlValue::Float(_)) => $query.bind(
values
.iter()
.map(array_element_f64)
.collect::<Vec<Option<f64>>>(),
),
Some(SqlValue::Uuid(_)) => $query.bind(
values
.iter()
.map(array_element_uuid)
.collect::<Vec<Option<uuid::Uuid>>>(),
),
Some(SqlValue::Date(_)) => $query.bind(
values
.iter()
.map(array_element_date)
.collect::<Vec<Option<chrono::NaiveDate>>>(),
),
Some(SqlValue::Time(_)) => $query.bind(
values
.iter()
.map(array_element_time)
.collect::<Vec<Option<chrono::NaiveTime>>>(),
),
Some(SqlValue::DateTimeTz(_)) => $query.bind(
values
.iter()
.map(array_element_datetime_tz)
.collect::<Vec<Option<chrono::DateTime<chrono::Utc>>>>(),
),
Some(SqlValue::DateTime(_)) => $query.bind(
values
.iter()
.map(array_element_datetime)
.collect::<Vec<Option<chrono::NaiveDateTime>>>(),
),
Some(SqlValue::Decimal(_)) => $query.bind(
values
.iter()
.map(array_element_decimal)
.collect::<Vec<Option<bigdecimal::BigDecimal>>>(),
),
Some(SqlValue::Timestamp(_)) => $query.bind(
values
.iter()
.map(array_element_timestamp)
.collect::<Vec<Option<i64>>>(),
),
Some(SqlValue::Bytes(_)) => $query.bind(
values
.iter()
.map(array_element_bytes)
.collect::<Vec<Option<Vec<u8>>>>(),
),
Some(SqlValue::Json(_)) => $query.bind(
values
.iter()
.map(array_element_json)
.collect::<Vec<Option<serde_json::Value>>>(),
),
_ => $query.bind(
values
.iter()
.map(array_element_string)
.collect::<Vec<Option<String>>>(),
),
},
SqlValue::TypedArray {
element_type,
values,
} => match *element_type {
"int2" => $query.bind(
values
.iter()
.map(array_element_i16)
.collect::<Vec<Option<i16>>>(),
),
"int4" => $query.bind(
values
.iter()
.map(array_element_i32)
.collect::<Vec<Option<i32>>>(),
),
"int8" => $query.bind(
values
.iter()
.map(array_element_i64)
.collect::<Vec<Option<i64>>>(),
),
"float4" => $query.bind(
values
.iter()
.map(array_element_f32)
.collect::<Vec<Option<f32>>>(),
),
"float8" => $query.bind(
values
.iter()
.map(array_element_f64)
.collect::<Vec<Option<f64>>>(),
),
"bool" => $query.bind(
values
.iter()
.map(array_element_bool)
.collect::<Vec<Option<bool>>>(),
),
"bytea" => $query.bind(
values
.iter()
.map(array_element_bytes)
.collect::<Vec<Option<Vec<u8>>>>(),
),
"uuid" => $query.bind(
values
.iter()
.map(array_element_uuid)
.collect::<Vec<Option<uuid::Uuid>>>(),
),
"date" => $query.bind(
values
.iter()
.map(array_element_date)
.collect::<Vec<Option<chrono::NaiveDate>>>(),
),
"time" => $query.bind(
values
.iter()
.map(array_element_time)
.collect::<Vec<Option<chrono::NaiveTime>>>(),
),
"timestamp" => $query.bind(
values
.iter()
.map(array_element_datetime)
.collect::<Vec<Option<chrono::NaiveDateTime>>>(),
),
"timestamptz" => $query.bind(
values
.iter()
.map(array_element_datetime_tz)
.collect::<Vec<Option<chrono::DateTime<chrono::Utc>>>>(),
),
"numeric" => $query.bind(
values
.iter()
.map(array_element_decimal)
.collect::<Vec<Option<bigdecimal::BigDecimal>>>(),
),
"jsonb" => $query.bind(
values
.iter()
.map(array_element_json)
.collect::<Vec<Option<serde_json::Value>>>(),
),
_ => $query.bind(
values
.iter()
.map(array_element_string)
.collect::<Vec<Option<String>>>(),
),
},
}
};
}
const INSERT_BATCH_SIZE: usize = 500;
const UPDATE_BATCH_SIZE: usize = 1000;
const MAX_BIND_PARAMS: usize = 65_535;
const MERGE_MIN_SERVER_VERSION: i32 = 150_000;
const MERGE_BY_SOURCE_MIN_SERVER_VERSION: i32 = 170_000;
fn merge_version_supported(version: i32) -> bool {
version >= MERGE_MIN_SERVER_VERSION
}
async fn server_version_num(
executor: &mut QueryExecutor<'_>,
) -> Result<i32, crate::error::DbError> {
let sql = "SELECT current_setting('server_version_num')::int";
match executor {
QueryExecutor::Pool(pool) => Ok(sqlx::query_scalar::<_, i32>(sql).fetch_one(*pool).await?),
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
Ok(sqlx::query_scalar::<_, i32>(sql)
.fetch_one(&mut *connection)
.await?)
}
}
}
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 derive_insert_batch_size(column_count: usize, requested: usize) -> usize {
if column_count == 0 {
return requested;
}
(MAX_BIND_PARAMS / column_count).max(1).min(requested)
}
fn derive_upsert_batch_size(column_count: usize, requested: usize) -> usize {
if column_count == 0 {
return requested;
}
(MAX_BIND_PARAMS / column_count).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())
}
fn pg_value_cast(field_type: Option<&FieldType>) -> Option<String> {
let field_type = field_type?;
match field_type {
FieldType::Inet => Some("::inet".to_string()),
FieldType::Cidr => Some("::cidr".to_string()),
FieldType::MacAddr => Some("::macaddr".to_string()),
FieldType::MacAddr8 => Some("::macaddr8".to_string()),
FieldType::Array(inner) => pg_value_cast(Some(inner)).map(|suffix| format!("{suffix}[]")),
FieldType::Range(_) | FieldType::Multirange(_) => field_type
.postgres_type_name()
.map(|name| format!("::{name}")),
_ => None,
}
}
fn push_typed_placeholder(
params: &mut Vec<SqlValue>,
value: SqlValue,
field_type: Option<&FieldType>,
) -> String {
let placeholder = push_placeholder(params, value);
match pg_value_cast(field_type) {
Some(suffix) => format!("{placeholder}{suffix}"),
None => placeholder,
}
}
fn pg_array_element_type_name(field_type: &FieldType) -> Option<&'static str> {
match field_type {
FieldType::Standard => Some("int8"),
FieldType::Bool => Some("bool"),
FieldType::Text => Some("text"),
FieldType::Blob => Some("bytea"),
FieldType::Uuid => Some("uuid"),
FieldType::Date => Some("date"),
FieldType::Time => Some("time"),
FieldType::DateTime => Some("timestamp"),
FieldType::TimestampTz => Some("timestamptz"),
FieldType::Timestamp => Some("int8"),
FieldType::Decimal => Some("numeric"),
FieldType::Json | FieldType::Jsonb => Some("jsonb"),
FieldType::Inet => Some("inet"),
FieldType::Cidr => Some("cidr"),
FieldType::MacAddr => Some("macaddr"),
FieldType::MacAddr8 => Some("macaddr8"),
FieldType::Array(_)
| FieldType::Bit
| FieldType::Varbit
| FieldType::Range(_)
| FieldType::Multirange(_) => None,
}
}
#[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 = self.render_condition_fragment(condition)?;
self.sql.push_str(&rendered);
Ok(())
}
fn render_condition_fragment(
&mut self,
condition: Condition,
) -> Result<String, crate::error::DbError> {
let rendered =
crate::postgres::condition::render_condition_checked(condition, self.params.len())?;
self.params.extend(rendered.params);
Ok(rendered.sql)
}
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> {
if !builder.ctes.is_empty() {
self.build_with(&builder.ctes)?;
}
self.build_set_chain(builder, &builder.set_ops, builder.set_ops.len())?;
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_with(&mut self, ctes: &[Cte<'_>]) -> Result<(), crate::error::DbError> {
self.append("WITH ");
if ctes.iter().any(|cte| cte.recursive) {
self.append("RECURSIVE ");
}
for (index, cte) in ctes.iter().enumerate() {
if index > 0 {
self.append(", ");
}
self.append(&super::identifier::quote_identifier(&cte.name)?);
self.append(" AS (");
self.build_compound_select(&cte.query)?;
self.append(")");
}
self.append(" ");
Ok(())
}
fn build_set_chain(
&mut self,
head: &QueryBuilder,
ops: &[(SetOperator, Box<QueryBuilder>)],
count: usize,
) -> Result<(), crate::error::DbError> {
if count == 0 {
return self.build_select_core(head);
}
let (operator, branch) = &ops[count - 1];
let needs_parens = count > 1
&& (ops[..count - 1].iter().any(|(op, _)| !op.is_union()) || !operator.is_union());
if needs_parens {
self.append("(");
}
self.build_set_chain(head, ops, count - 1)?;
if needs_parens {
self.append(")");
}
self.append(operator.sql());
self.append("(");
self.build_compound_select(branch)?;
self.append(")");
Ok(())
}
fn build_select_core(&mut self, builder: &QueryBuilder) -> Result<(), crate::error::DbError> {
self.append("SELECT ");
if !builder.distinct_on.is_empty() {
if builder.distinct {
return Err(crate::error::DbError::InvalidArgument(
"distinct 与 distinct_on 不能同时使用".to_string(),
));
}
let matched = builder.order_by.len() >= builder.distinct_on.len()
&& builder
.order_by
.iter()
.take(builder.distinct_on.len())
.zip(&builder.distinct_on)
.all(|(order, expected)| order.field == *expected);
if !matched {
return Err(crate::error::DbError::InvalidArgument(
"distinct_on 要求最左 ORDER BY 与 DISTINCT ON 字段完全一致(顺序相同)"
.to_string(),
));
}
self.append("DISTINCT ON (");
self.append(&builder.distinct_on.join(", "));
self.append(") ");
} else if builder.distinct {
self.append("DISTINCT ");
}
if builder.fields.is_empty()
&& builder.projection_exprs.is_empty()
&& builder.window_exprs.is_empty()
{
self.append("*");
} else {
let mut projection = builder.fields.clone();
for (expression, alias) in &builder.projection_exprs {
let placeholder = self.params.len() + 1;
let rendered = expression.postgres_render(placeholder);
for param in rendered.params {
self.params.push(param.into());
}
let mut piece = rendered.sql;
if let Some(alias) = alias {
piece.push_str(" AS ");
piece.push_str(&super::identifier::quote_identifier(alias)?);
}
projection.push(piece);
}
for window in &builder.window_exprs {
projection.push(window.render(self)?);
}
self.append(&projection.join(", "));
}
self.append(" FROM ");
self.append(&super::identifier::quote_table(&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() || !builder.grouping_constructs.is_empty() {
self.build_group_by(&builder.group_by, &builder.grouping_constructs)?;
}
if !builder.having_clause.is_empty() || !builder.having_exprs.is_empty() {
if !builder.having_clause.is_empty()
&& builder.group_by.is_empty()
&& builder.grouping_constructs.is_empty()
{
return Err(crate::error::DbError::MissingGroupByClause);
}
self.build_having(&builder.having_clause, &builder.having_exprs)?;
}
if !builder.named_windows.is_empty() {
self.build_window_clause(&builder.named_windows)?;
}
Ok(())
}
fn build_window_clause(
&mut self,
windows: &[(String, WindowSpec)],
) -> Result<(), crate::error::DbError> {
self.append(" WINDOW ");
for (index, (name, spec)) in windows.iter().enumerate() {
if index > 0 {
self.append(", ");
}
self.append(&super::identifier::quote_identifier(name)?);
self.append(" AS (");
self.append(&spec.render_body()?);
self.append(")");
}
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: &[JoinSpec<'_>]) -> Result<(), crate::error::DbError> {
for join in joins {
let join_keyword = match join.kind {
JoinKind::Inner => " INNER JOIN ",
JoinKind::Left => " LEFT JOIN ",
JoinKind::Right => " RIGHT JOIN ",
JoinKind::Full => " FULL JOIN ",
JoinKind::Cross => " CROSS JOIN ",
JoinKind::Natural => " NATURAL JOIN ",
JoinKind::LeftLateral => " LEFT JOIN LATERAL ",
};
self.append(join_keyword);
match &join.target {
JoinTarget::Table(table) => {
self.append(&super::identifier::quote_table(table)?);
}
JoinTarget::Lateral(query, alias) => {
self.append("(");
self.build_compound_select(query)?;
self.append(") AS ");
self.append(&super::identifier::quote_identifier(alias)?);
}
}
match &join.condition {
JoinCondition::On(expression) => {
self.append(" ON ");
self.append(expression);
}
JoinCondition::Using(columns) => {
let quoted: Result<Vec<String>, crate::error::DbError> = columns
.iter()
.map(|column| super::identifier::quote_identifier(column))
.collect();
self.append(" USING (");
self.append("ed?.join(", "));
self.append(")");
}
JoinCondition::None => {}
}
}
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],
constructs: &[GroupingConstruct],
) -> Result<(), crate::error::DbError> {
if groups.is_empty() && constructs.is_empty() {
return Ok(());
}
self.append(" GROUP BY ");
let mut first = true;
for group in groups {
if !first {
self.sql.push_str(", ");
}
first = false;
self.sql.push_str(group);
}
for construct in constructs {
if !first {
self.sql.push_str(", ");
}
first = false;
match construct {
GroupingConstruct::GroupingSets(sets) => {
if sets.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"grouping_sets 不能为空".to_string(),
));
}
let rendered: Vec<String> = sets
.iter()
.map(|set| {
let fields: Vec<&str> =
set.iter().map(crate::FieldRef::postgres_quoted).collect();
format!("({})", fields.join(", "))
})
.collect();
self.sql
.push_str(&format!("GROUPING SETS ({})", rendered.join(", ")));
}
GroupingConstruct::Rollup(fields) => {
if fields.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"rollup 的字段列表不能为空".to_string(),
));
}
let rendered: Vec<&str> = fields
.iter()
.map(crate::FieldRef::postgres_quoted)
.collect();
self.sql
.push_str(&format!("ROLLUP ({})", rendered.join(", ")));
}
GroupingConstruct::Cube(fields) => {
if fields.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"cube 的字段列表不能为空".to_string(),
));
}
let rendered: Vec<&str> = fields
.iter()
.map(crate::FieldRef::postgres_quoted)
.collect();
self.sql
.push_str(&format!("CUBE ({})", rendered.join(", ")));
}
}
}
Ok(())
}
fn build_having(
&mut self,
conditions: &[Condition],
exprs: &[HavingExpr],
) -> Result<(), crate::error::DbError> {
if conditions.is_empty() && exprs.is_empty() {
return Ok(());
}
self.append(" HAVING ");
let mut first = true;
for condition in conditions {
if !first {
self.sql.push_str(" AND ");
}
first = false;
self.append_condition(condition.clone())?;
}
for expr in exprs {
if !first {
self.sql.push_str(" AND ");
}
first = false;
self.sql
.push_str(&expr.expression.postgres_sql_without_alias());
self.sql.push(' ');
self.sql.push_str(comparison_operator_sql(expr.operator));
self.sql.push(' ');
match &expr.value {
SqlValue::Null => self.sql.push_str("NULL"),
value => {
let placeholder = push_placeholder(&mut self.params, value.clone());
self.sql.push_str(&placeholder);
}
}
}
Ok(())
}
pub(crate) fn build_insert(
&mut self,
table: &str,
data: &serde_json::Value,
field_types: &HashMap<String, FieldType>,
set_exprs: &[(String, crate::SqlExpr)],
) -> 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() && set_exprs.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_typed_placeholder(
&mut self.params,
v,
field_types.get(key),
)),
}
}
for (key, expression) in set_exprs {
fields.push(super::identifier::quote_identifier(key)?);
let placeholder = self.params.len() + 1;
let rendered = expression.postgres_render(placeholder);
for param in rendered.params {
self.params.push(param.into());
}
placeholders.push(rendered.sql);
}
self.append("INSERT INTO ");
self.append(&super::identifier::quote_table(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_table(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_typed_placeholder(&mut self.params, v, field_types.get(field));
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],
set_exprs: &[(String, crate::SqlExpr)],
) -> 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() && set_exprs.is_empty() {
return Err(crate::error::DbError::SerializationError(
"更新数据不能为空".to_string(),
));
}
self.append("UPDATE ");
self.append(&super::identifier::quote_table(table)?);
self.append(" SET ");
self.build_assignment_list(obj, field_types, set_exprs)?;
self.build_where(conditions)?;
Ok(())
}
fn build_assignment_list(
&mut self,
obj: &serde_json::Map<String, serde_json::Value>,
field_types: &HashMap<String, FieldType>,
set_exprs: &[(String, crate::SqlExpr)],
) -> Result<(), crate::error::DbError> {
let mut first = true;
for (key, value) in obj.iter() {
if !first {
self.sql.push_str(", ");
}
first = false;
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_typed_placeholder(&mut self.params, v, field_types.get(key));
self.sql.push_str(&ph);
}
}
}
for (key, expression) in set_exprs {
if !first {
self.sql.push_str(", ");
}
first = false;
self.sql
.push_str(&super::identifier::quote_identifier(key)?);
self.sql.push_str(" = ");
let placeholder = self.params.len() + 1;
let rendered = expression.postgres_render(placeholder);
for param in rendered.params {
self.params.push(param.into());
}
self.sql.push_str(&rendered.sql);
}
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_table(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_table(table)?);
self.build_where(conditions)?;
Ok(())
}
pub(crate) fn build_insert_from_select(
&mut self,
table: &str,
columns: &[&crate::FieldRef],
source: &QueryBuilder<'_>,
) -> Result<(), crate::error::DbError> {
self.clear();
self.append("INSERT INTO ");
self.append(&super::identifier::quote_table(table)?);
if !columns.is_empty() {
let quoted: Result<Vec<String>, crate::error::DbError> = columns
.iter()
.map(|column| super::identifier::quote_identifier(column.as_str()))
.collect();
self.append(" (");
self.append("ed?.join(", "));
self.append(")");
}
self.append(" ");
self.build_compound_select(source)?;
Ok(())
}
#[allow(clippy::too_many_arguments)]
pub(crate) fn build_update_from(
&mut self,
table: &str,
data: &serde_json::Value,
field_types: &HashMap<String, FieldType>,
set_exprs: &[(String, crate::SqlExpr)],
conditions: &[Condition],
from_table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> 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() && set_exprs.is_empty() {
return Err(crate::error::DbError::SerializationError(
"更新数据不能为空".to_string(),
));
}
self.append("UPDATE ");
self.append(&super::identifier::quote_table(table)?);
self.append(" SET ");
self.build_assignment_list(obj, field_types, set_exprs)?;
self.append(" FROM ");
self.append(&super::identifier::quote_table(from_table.as_str())?);
let mut all_conditions = conditions.to_vec();
all_conditions.push(join_condition(left, right));
self.build_where(&all_conditions)?;
Ok(())
}
pub(crate) fn build_delete_using(
&mut self,
table: &str,
conditions: &[Condition],
using_table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> 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_table(table)?);
self.append(" USING ");
self.append(&super::identifier::quote_table(using_table.as_str())?);
let mut all_conditions = conditions.to_vec();
all_conditions.push(join_condition(left, right));
self.build_where(&all_conditions)?;
Ok(())
}
pub(crate) fn build_merge(
&mut self,
table: &str,
source: &crate::TableRef,
target_key: &crate::FieldRef,
source_key: &crate::FieldRef,
columns: &[&crate::FieldRef],
) -> Result<(), crate::error::DbError> {
self.build_merge_with_options(
table,
source,
target_key,
source_key,
columns,
&MergeOptions::default(),
)
}
pub(crate) fn build_merge_with_options(
&mut self,
table: &str,
source: &crate::TableRef,
target_key: &crate::FieldRef,
source_key: &crate::FieldRef,
columns: &[&crate::FieldRef],
options: &MergeOptions,
) -> Result<(), crate::error::DbError> {
self.clear();
if columns.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"merge 的列列表不能为空".to_string(),
));
}
let target_qualifier = table.rsplit('.').next().unwrap_or(table);
let source_qualifier = source
.as_str()
.rsplit('.')
.next()
.unwrap_or(source.as_str());
self.append("MERGE INTO ");
self.append(&super::identifier::quote_table(table)?);
self.append(" USING ");
self.append(&super::identifier::quote_table(source.as_str())?);
self.append(" ON ");
self.append(&qualify_merge_field(target_key, target_qualifier)?);
self.append(" = ");
self.append(&qualify_merge_field(source_key, source_qualifier)?);
self.append(" WHEN MATCHED");
if let Some(condition) = &options.matched_condition {
self.append(" AND ");
self.append_condition(condition.clone())?;
}
self.append(" THEN UPDATE SET ");
for (index, column) in columns.iter().enumerate() {
if index > 0 {
self.sql.push_str(", ");
}
let quoted = super::identifier::quote_identifier(column.as_str())?;
self.sql.push_str("ed);
self.sql.push_str(" = ");
self.sql
.push_str(&qualify_merge_field(column, source_qualifier)?);
}
self.append(" WHEN NOT MATCHED THEN INSERT (");
let quoted: Result<Vec<String>, crate::error::DbError> = columns
.iter()
.map(|column| super::identifier::quote_identifier(column.as_str()))
.collect();
self.append("ed?.join(", "));
self.append(") VALUES (");
let values: Result<Vec<String>, crate::error::DbError> = columns
.iter()
.map(|column| qualify_merge_field(column, source_qualifier))
.collect();
self.append(&values?.join(", "));
self.append(")");
match &options.not_matched_by_source {
None => {}
Some(MergeNotMatchedBySource::Delete) => {
self.append(" WHEN NOT MATCHED BY SOURCE THEN DELETE");
}
Some(MergeNotMatchedBySource::Update(assignments)) => {
if assignments.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"WHEN NOT MATCHED BY SOURCE 的 UPDATE 赋值列表不能为空".to_string(),
));
}
self.append(" WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ");
for (index, (column, expression)) in assignments.iter().enumerate() {
if index > 0 {
self.sql.push_str(", ");
}
self.append(&super::identifier::quote_identifier(column.as_str())?);
self.append(" = ");
let rendered = expression.postgres_render(self.params.len() + 1);
for param in rendered.params {
self.params.push(param.into());
}
self.append(&rendered.sql);
}
}
}
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_table(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_typed_placeholder(&mut self.params, v, field_types.get(id_field));
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_typed_placeholder(
&mut self.params,
v,
field_types.get(field.as_str()),
);
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_typed_placeholder(&mut self.params, v, field_types.get(id_field));
self.sql.push_str(&ph);
}
}
}
self.sql.push(')');
Ok(())
}
#[allow(clippy::too_many_arguments)]
pub(crate) fn build_upsert(
&mut self,
table: &str,
data: &serde_json::Value,
field_types: &HashMap<String, FieldType>,
conflict: &ConflictTarget,
conflict_predicate: Option<&str>,
do_nothing: bool,
update_columns: Option<&[String]>,
update_where: &[Condition],
) -> 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_table(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_typed_placeholder(
&mut self.params,
v,
field_types.get(field.as_str()),
);
self.sql.push_str(&ph);
}
}
}
self.sql.push(')');
self.append_on_conflict(
table,
&fields,
conflict,
conflict_predicate,
do_nothing,
update_columns,
update_where,
)
}
#[allow(clippy::too_many_arguments)]
fn append_on_conflict(
&mut self,
table: &str,
fields: &[String],
conflict: &ConflictTarget,
predicate: Option<&str>,
do_nothing: bool,
update_columns: Option<&[String]>,
update_where: &[Condition],
) -> Result<(), crate::error::DbError> {
if predicate.is_some()
&& !matches!(conflict, ConflictTarget::Columns(columns) if !columns.is_empty())
{
return Err(crate::error::DbError::InvalidArgument(
"ON CONFLICT 谓词只能与显式冲突列列表一起使用".to_string(),
));
}
let quoted_conflict: Vec<String> = match conflict {
ConflictTarget::Columns(columns) => columns
.iter()
.map(|column| super::identifier::quote_identifier(column))
.collect::<Result<_, _>>()?,
ConflictTarget::Unspecified | ConflictTarget::Constraint(_) => Vec::new(),
};
let has_column_target =
matches!(conflict, ConflictTarget::Columns(columns) if !columns.is_empty());
if !has_column_target && !matches!(conflict, ConflictTarget::Constraint(_)) && !do_nothing {
return Err(crate::error::DbError::InvalidArgument(
"upsert 必须显式指定冲突目标(on_conflict / on_conflict_constraint / \
on_conflict_with_predicate);如只想忽略冲突请使用 do_nothing()"
.to_string(),
));
}
match conflict {
ConflictTarget::Unspecified => {
self.sql.push_str(" ON CONFLICT");
}
ConflictTarget::Columns(_) if !has_column_target => {
self.sql.push_str(" ON CONFLICT");
}
ConflictTarget::Columns(_) => {
self.sql.push_str(" ON CONFLICT (");
self.sql.push_str("ed_conflict.join(", "));
self.sql.push(')');
}
ConflictTarget::Constraint(name) => {
self.sql.push_str(" ON CONFLICT ON CONSTRAINT ");
self.sql
.push_str(&super::identifier::quote_identifier(name)?);
}
}
if let Some(predicate) = predicate {
let predicate = validate_conflict_predicate(predicate)?;
self.sql.push_str(" WHERE (");
self.sql.push_str(predicate);
self.sql.push(')');
}
if do_nothing || matches!(conflict, ConflictTarget::Columns(columns) if columns.is_empty())
{
self.sql.push_str(" DO NOTHING");
return Ok(());
}
let selected: Vec<&String> = match update_columns {
Some(columns) => {
if columns.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"update_columns 不能为空;如不想更新任何列请使用 do_nothing()".to_string(),
));
}
for column in columns {
if !fields.contains(column) {
return Err(crate::error::DbError::InvalidArgument(format!(
"update_columns 中的列 `{column}` 不在插入数据中"
)));
}
}
columns.iter().collect()
}
None => fields
.iter()
.filter(|field| match conflict {
ConflictTarget::Columns(columns) => !columns.contains(field),
ConflictTarget::Unspecified | ConflictTarget::Constraint(_) => {
!field.eq_ignore_ascii_case("id")
}
})
.collect(),
};
if selected.is_empty() && matches!(conflict, ConflictTarget::Constraint(_)) {
return Err(crate::error::DbError::InvalidArgument(
"ON CONFLICT ON CONSTRAINT 无法推断可更新列;请显式 update_columns(),\
或改用 on_conflict(columns) / do_nothing()"
.to_string(),
));
}
self.sql.push_str(" DO UPDATE SET ");
if selected.is_empty() {
let fallback = match conflict {
ConflictTarget::Columns(columns) => columns.first(),
ConflictTarget::Unspecified | ConflictTarget::Constraint(_) => fields.first(),
}
.ok_or_else(|| crate::error::DbError::InvalidArgument("没有可更新的列".to_string()))?;
let quoted = super::identifier::quote_identifier(fallback)?;
self.sql.push_str("ed);
self.sql.push_str(" = EXCLUDED.");
self.sql.push_str("ed);
} else {
for (index, field) in selected.iter().enumerate() {
if index > 0 {
self.sql.push_str(", ");
}
let quoted = super::identifier::quote_identifier(field.as_str())?;
self.sql.push_str("ed);
self.sql.push_str(" = EXCLUDED.");
self.sql.push_str("ed);
}
}
if !update_where.is_empty() {
let qualifier = table.rsplit('.').next().unwrap_or(table);
let qualified_conditions: Vec<Condition> = update_where
.iter()
.cloned()
.map(|condition| qualify_condition_fields(condition, qualifier))
.collect();
self.build_where(&qualified_conditions)?;
}
Ok(())
}
#[allow(clippy::too_many_arguments)]
pub(crate) fn build_upsert_batch(
&mut self,
table: &str,
data_list: &[serde_json::Value],
field_types: &HashMap<String, FieldType>,
conflict_columns: &[String],
conflict_predicate: Option<&str>,
do_nothing: bool,
update_columns: Option<&[String]>,
update_where: &[Condition],
) -> Result<(), crate::error::DbError> {
self.build_insert_batch(table, data_list, field_types)?;
if conflict_columns.is_empty() && !do_nothing {
return Err(crate::error::DbError::InvalidArgument(
"批量 upsert 必须显式指定冲突列;如使用 id 请显式传入,\
或调用 do_nothing() 走 ON CONFLICT DO NOTHING"
.to_string(),
));
}
let fields: Vec<String> = data_list[0]
.as_object()
.map(|object| object.keys().cloned().collect())
.unwrap_or_default();
self.append_on_conflict(
table,
&fields,
&ConflictTarget::Columns(conflict_columns.to_vec()),
conflict_predicate,
do_nothing,
update_columns,
update_where,
)
}
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 | FieldType::Jsonb => return Ok(SqlValue::Json(value.clone())),
FieldType::Uuid => {
if let Some(s) = value.as_str() {
let uuid = uuid::Uuid::parse_str(s).map_err(|e| {
crate::error::DbError::TypeConversionError(format!(
"无法解析 UUID 字符串: {e}"
))
})?;
return Ok(SqlValue::Uuid(uuid));
} else if value.is_null() {
return Ok(SqlValue::Null);
} else {
return Err(crate::error::DbError::TypeConversionError(format!(
"Uuid 字段期望字符串,实得 {value}"
)));
}
}
FieldType::TimestampTz => {
if let Some(s) = value.as_str() {
let parsed = chrono::DateTime::parse_from_rfc3339(s)
.map(|dt| dt.with_timezone(&chrono::Utc))
.or_else(|_| {
chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S")
.map(|dt| dt.and_utc())
})
.or_else(|_| {
chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.f")
.map(|dt| dt.and_utc())
})
.map_err(|e| {
crate::error::DbError::TypeConversionError(format!(
"无法解析 TIMESTAMPTZ 字符串: {e}"
))
})?;
return Ok(SqlValue::DateTimeTz(parsed));
} else if value.is_null() {
return Ok(SqlValue::Null);
} else {
return Err(crate::error::DbError::TypeConversionError(format!(
"TimestampTz 字段期望字符串,实得 {value}"
)));
}
}
FieldType::Date => {
if let Some(s) = value.as_str() {
let date =
chrono::NaiveDate::parse_from_str(s, "%Y-%m-%d").map_err(|e| {
crate::error::DbError::TypeConversionError(format!(
"无法解析 DATE 字符串: {e}"
))
})?;
return Ok(SqlValue::Date(date));
} else if value.is_null() {
return Ok(SqlValue::Null);
} else {
return Err(crate::error::DbError::TypeConversionError(format!(
"Date 字段期望字符串,实得 {value}"
)));
}
}
FieldType::Time => {
if let Some(s) = value.as_str() {
let time = chrono::NaiveTime::parse_from_str(s, "%H:%M:%S")
.or_else(|_| chrono::NaiveTime::parse_from_str(s, "%H:%M:%S%.f"))
.or_else(|_| chrono::NaiveTime::parse_from_str(s, "%H:%M"))
.map_err(|e| {
crate::error::DbError::TypeConversionError(format!(
"无法解析 TIME 字符串: {e}"
))
})?;
return Ok(SqlValue::Time(time));
} else if value.is_null() {
return Ok(SqlValue::Null);
} else {
return Err(crate::error::DbError::TypeConversionError(format!(
"Time 字段期望字符串,实得 {value}"
)));
}
}
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 => {
if value.is_null() {
return Ok(SqlValue::Null);
}
let parse_error = |detail: String| {
crate::error::DbError::TypeConversionError(format!(
"Decimal 字段期望数字或数字字符串,实得 {value}({detail})"
))
};
if let Some(s) = value.as_str() {
return s
.parse::<bigdecimal::BigDecimal>()
.map(SqlValue::Decimal)
.map_err(|error| parse_error(error.to_string()));
}
if let Some(i) = value.as_i64() {
return Ok(SqlValue::Decimal(bigdecimal::BigDecimal::from(i)));
}
if let Some(u) = value.as_u64() {
return Ok(SqlValue::Decimal(bigdecimal::BigDecimal::from(u)));
}
if let Some(f) = value.as_f64() {
return f
.to_string()
.parse::<bigdecimal::BigDecimal>()
.map(SqlValue::Decimal)
.map_err(|error| parse_error(error.to_string()));
}
return Err(parse_error("无法识别的 JSON 数值".to_string()));
}
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::Array(inner) => {
if value.is_null() {
return Ok(SqlValue::Null);
}
let elements = value.as_array().ok_or_else(|| {
crate::error::DbError::TypeConversionError(format!(
"数组字段期望 JSON 数组,实得 {value}"
))
})?;
let mut converted = Vec::with_capacity(elements.len());
for element in elements {
converted.push(self.json_value_to_sql_value(element, Some(inner))?);
}
let typeless = converted
.iter()
.all(|value| matches!(value, SqlValue::Null));
if typeless {
if let Some(element_type) = pg_array_element_type_name(inner) {
return Ok(SqlValue::TypedArray {
element_type,
values: converted,
});
}
}
return Ok(SqlValue::Array(converted));
}
FieldType::Inet | FieldType::Cidr | FieldType::MacAddr | FieldType::MacAddr8 => {
if value.is_null() {
return Ok(SqlValue::Null);
}
let text = value.as_str().ok_or_else(|| {
crate::error::DbError::TypeConversionError(format!(
"网络字段期望字符串,实得 {value}"
))
})?;
return Ok(match ft {
FieldType::Inet => SqlValue::Inet(text.to_string()),
FieldType::Cidr => SqlValue::Cidr(text.to_string()),
FieldType::MacAddr => SqlValue::MacAddr(text.to_string()),
FieldType::MacAddr8 => SqlValue::MacAddr8(text.to_string()),
_ => unreachable!("分支只覆盖四种网络类型"),
});
}
FieldType::Bit | FieldType::Varbit => {
return Err(crate::error::DbError::InvalidArgument(
"Bit/Varbit 字段的写入需要位长信息,当前结构化 API 不支持;请使用原生 SQL 逃生舱"
.to_string(),
));
}
FieldType::Range(_) | FieldType::Multirange(_) => {
if value.is_null() {
return Ok(SqlValue::Null);
}
if value.as_str().is_none() {
return Err(crate::error::DbError::TypeConversionError(format!(
"Range/Multirange 字段期望字符串,实得 {value}"
)));
}
}
FieldType::Standard | FieldType::Bool => {}
}
}
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, PartialEq, Eq)]
enum SetOperator {
Union,
UnionAll,
Intersect,
IntersectAll,
Except,
ExceptAll,
}
impl SetOperator {
const fn sql(self) -> &'static str {
match self {
Self::Union => " UNION ",
Self::UnionAll => " UNION ALL ",
Self::Intersect => " INTERSECT ",
Self::IntersectAll => " INTERSECT ALL ",
Self::Except => " EXCEPT ",
Self::ExceptAll => " EXCEPT ALL ",
}
}
const fn is_union(self) -> bool {
matches!(self, Self::Union | Self::UnionAll)
}
}
#[derive(Debug, Clone, Copy)]
pub(crate) enum ArithmeticOperator {
Add,
Subtract,
}
struct Cte<'a> {
name: String,
recursive: bool,
query: Box<QueryBuilder<'a>>,
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
enum JoinKind {
Inner,
Left,
Right,
Full,
Cross,
Natural,
LeftLateral,
}
enum JoinTarget<'a> {
Table(String),
Lateral(Box<QueryBuilder<'a>>, String),
}
#[derive(Debug, Clone, PartialEq, Eq)]
enum JoinCondition {
On(String),
Using(Vec<String>),
None,
}
struct JoinSpec<'a> {
kind: JoinKind,
target: JoinTarget<'a>,
condition: JoinCondition,
}
#[derive(Debug, Clone, PartialEq, Eq)]
enum GroupingConstruct {
GroupingSets(Vec<Vec<crate::FieldRef>>),
Rollup(Vec<crate::FieldRef>),
Cube(Vec<crate::FieldRef>),
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum FrameMode {
Rows,
Range,
Groups,
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum FrameBound {
UnboundedPreceding,
Preceding(u64),
CurrentRow,
Following(u64),
UnboundedFollowing,
}
impl FrameBound {
fn sql(self) -> String {
match self {
Self::UnboundedPreceding => "UNBOUNDED PRECEDING".to_string(),
Self::Preceding(offset) => format!("{offset} PRECEDING"),
Self::CurrentRow => "CURRENT ROW".to_string(),
Self::Following(offset) => format!("{offset} FOLLOWING"),
Self::UnboundedFollowing => "UNBOUNDED FOLLOWING".to_string(),
}
}
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct WindowFrame {
mode: FrameMode,
start: FrameBound,
end: FrameBound,
}
impl WindowFrame {
pub fn rows_between(start: FrameBound, end: FrameBound) -> Self {
Self {
mode: FrameMode::Rows,
start,
end,
}
}
pub fn range_between(start: FrameBound, end: FrameBound) -> Self {
Self {
mode: FrameMode::Range,
start,
end,
}
}
pub fn groups_between(start: FrameBound, end: FrameBound) -> Self {
Self {
mode: FrameMode::Groups,
start,
end,
}
}
fn render(&self) -> Result<String, crate::error::DbError> {
if matches!(self.start, FrameBound::UnboundedFollowing) {
return Err(crate::error::DbError::InvalidArgument(
"窗口帧的起始边界不能是 UNBOUNDED FOLLOWING".to_string(),
));
}
if matches!(self.end, FrameBound::UnboundedPreceding) {
return Err(crate::error::DbError::InvalidArgument(
"窗口帧的结束边界不能是 UNBOUNDED PRECEDING".to_string(),
));
}
let mode = match self.mode {
FrameMode::Rows => "ROWS",
FrameMode::Range => "RANGE",
FrameMode::Groups => "GROUPS",
};
Ok(format!(
"{mode} BETWEEN {} AND {}",
self.start.sql(),
self.end.sql()
))
}
}
#[derive(Debug, Clone, Default, PartialEq, Eq)]
pub struct WindowSpec {
partition_by: Vec<crate::FieldRef>,
order_by: Vec<(crate::FieldRef, crate::SortOrder)>,
frame: Option<WindowFrame>,
reference: Option<String>,
}
impl WindowSpec {
pub fn new() -> Self {
Self::default()
}
pub fn reference(name: &str) -> Self {
Self {
reference: Some(name.to_string()),
..Self::default()
}
}
pub fn partition_by(mut self, fields: &[&crate::FieldRef]) -> Self {
self.partition_by
.extend(fields.iter().map(|field| (*field).clone()));
self
}
pub fn order_by(mut self, field: &crate::FieldRef, order: crate::SortOrder) -> Self {
self.order_by.push((field.clone(), order));
self
}
pub fn frame(mut self, frame: WindowFrame) -> Self {
self.frame = Some(frame);
self
}
fn render_body(&self) -> Result<String, crate::error::DbError> {
if matches!(self.frame.map(|frame| frame.mode), Some(FrameMode::Groups))
&& self.order_by.is_empty()
{
return Err(crate::error::DbError::InvalidArgument(
"GROUPS 帧要求窗口定义包含 ORDER BY".to_string(),
));
}
let mut parts: Vec<String> = Vec::new();
if !self.partition_by.is_empty() {
let fields: Vec<&str> = self
.partition_by
.iter()
.map(crate::FieldRef::postgres_quoted)
.collect();
parts.push(format!("PARTITION BY {}", fields.join(", ")));
}
if !self.order_by.is_empty() {
let orders: Vec<String> = self
.order_by
.iter()
.map(|(field, order)| {
format!(
"{} {}",
field.postgres_quoted(),
if order.is_ascending() { "ASC" } else { "DESC" }
)
})
.collect();
parts.push(format!("ORDER BY {}", orders.join(", ")));
}
if let Some(frame) = &self.frame {
parts.push(frame.render()?);
}
Ok(parts.join(" "))
}
fn render(&self) -> Result<String, crate::error::DbError> {
if let Some(name) = &self.reference {
if !self.partition_by.is_empty() || !self.order_by.is_empty() || self.frame.is_some() {
return Err(crate::error::DbError::InvalidArgument(
"引用命名窗口时不能再附带 PARTITION BY / ORDER BY / 帧".to_string(),
));
}
return Ok(format!(
"OVER {}",
super::identifier::quote_identifier(name)?
));
}
Ok(format!("OVER ({})", self.render_body()?))
}
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
enum WindowFunctionKind {
RowNumber,
Rank,
DenseRank,
Sum,
Avg,
Count,
Min,
Max,
Lag,
Lead,
}
#[derive(Debug, Clone)]
pub struct WindowFunction {
kind: WindowFunctionKind,
field: Option<crate::FieldRef>,
filter: Option<Condition>,
}
impl WindowFunction {
fn rank_like(kind: WindowFunctionKind) -> Self {
Self {
kind,
field: None,
filter: None,
}
}
fn with_field(kind: WindowFunctionKind, field: &crate::FieldRef) -> Self {
Self {
kind,
field: Some(field.clone()),
filter: None,
}
}
pub fn row_number() -> Self {
Self::rank_like(WindowFunctionKind::RowNumber)
}
pub fn rank() -> Self {
Self::rank_like(WindowFunctionKind::Rank)
}
pub fn dense_rank() -> Self {
Self::rank_like(WindowFunctionKind::DenseRank)
}
pub fn sum(field: &crate::FieldRef) -> Self {
Self::with_field(WindowFunctionKind::Sum, field)
}
pub fn avg(field: &crate::FieldRef) -> Self {
Self::with_field(WindowFunctionKind::Avg, field)
}
pub fn count(field: &crate::FieldRef) -> Self {
Self::with_field(WindowFunctionKind::Count, field)
}
pub fn count_all() -> Self {
Self::rank_like(WindowFunctionKind::Count)
}
pub fn min(field: &crate::FieldRef) -> Self {
Self::with_field(WindowFunctionKind::Min, field)
}
pub fn max(field: &crate::FieldRef) -> Self {
Self::with_field(WindowFunctionKind::Max, field)
}
pub fn lag(field: &crate::FieldRef) -> Self {
Self::with_field(WindowFunctionKind::Lag, field)
}
pub fn lead(field: &crate::FieldRef) -> Self {
Self::with_field(WindowFunctionKind::Lead, field)
}
pub fn filter(mut self, condition: Condition) -> Self {
self.filter = Some(condition);
self
}
fn render(&self, generator: &mut SqlGenerator) -> Result<String, crate::error::DbError> {
let name = match self.kind {
WindowFunctionKind::RowNumber => "ROW_NUMBER",
WindowFunctionKind::Rank => "RANK",
WindowFunctionKind::DenseRank => "DENSE_RANK",
WindowFunctionKind::Sum => "SUM",
WindowFunctionKind::Avg => "AVG",
WindowFunctionKind::Count => "COUNT",
WindowFunctionKind::Min => "MIN",
WindowFunctionKind::Max => "MAX",
WindowFunctionKind::Lag => "LAG",
WindowFunctionKind::Lead => "LEAD",
};
let call = match &self.field {
Some(field) => format!("{name}({})", field.postgres_quoted()),
None if self.kind == WindowFunctionKind::Count => "COUNT(*)".to_string(),
None => format!("{name}()"),
};
let Some(filter) = &self.filter else {
return Ok(call);
};
if !matches!(
self.kind,
WindowFunctionKind::Sum
| WindowFunctionKind::Avg
| WindowFunctionKind::Count
| WindowFunctionKind::Min
| WindowFunctionKind::Max
) {
return Err(crate::error::DbError::InvalidArgument(format!(
"{name} 不是聚合函数,不能使用 FILTER (WHERE ...)"
)));
}
let fragment = generator.render_condition_fragment(filter.clone())?;
Ok(format!("{call} FILTER (WHERE {fragment})"))
}
}
struct WindowProjection {
function: WindowFunction,
spec: WindowSpec,
alias: String,
}
impl WindowProjection {
fn render(&self, generator: &mut SqlGenerator) -> Result<String, crate::error::DbError> {
let alias = super::identifier::quote_identifier(&self.alias)?;
Ok(format!(
"{} {} AS {alias}",
self.function.render(generator)?,
self.spec.render()?
))
}
}
struct HavingExpr {
expression: crate::SelectExpr,
operator: ComparisonOperator,
value: SqlValue,
}
fn comparison_operator_sql(operator: ComparisonOperator) -> &'static str {
match operator {
ComparisonOperator::Eq => "=",
ComparisonOperator::Ne => "!=",
ComparisonOperator::Gt => ">",
ComparisonOperator::Lt => "<",
ComparisonOperator::Gte => ">=",
ComparisonOperator::Lte => "<=",
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub(crate) enum ConflictTarget {
Unspecified,
Columns(Vec<String>),
Constraint(String),
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub(crate) struct ReturningColumn {
pub(crate) column: String,
pub(crate) alias: Option<String>,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub(crate) enum ReturningTarget {
Column(String),
Columns(Vec<ReturningColumn>),
All,
}
#[derive(Debug, Clone, PartialEq, Eq)]
#[non_exhaustive]
pub enum MergeNotMatchedBySource {
Delete,
Update(Vec<(crate::FieldRef, crate::SqlExpr)>),
}
#[derive(Debug, Clone, Default)]
pub struct MergeOptions {
matched_condition: Option<Condition>,
not_matched_by_source: Option<MergeNotMatchedBySource>,
}
impl MergeOptions {
pub fn matched_condition(mut self, condition: Condition) -> Self {
self.matched_condition = Some(condition);
self
}
pub fn not_matched_by_source(mut self, action: MergeNotMatchedBySource) -> Self {
self.not_matched_by_source = Some(action);
self
}
}
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),
}
fn qualify_condition_fields(condition: Condition, qualifier: &str) -> Condition {
fn qualify(field: String, qualifier: &str) -> String {
if field.contains('.') {
field
} else {
format!("{qualifier}.{field}")
}
}
match condition {
Condition::Eq(field, value) => Condition::Eq(qualify(field, qualifier), value),
Condition::Ne(field, value) => Condition::Ne(qualify(field, qualifier), value),
Condition::Gt(field, value) => Condition::Gt(qualify(field, qualifier), value),
Condition::Lt(field, value) => Condition::Lt(qualify(field, qualifier), value),
Condition::Gte(field, value) => Condition::Gte(qualify(field, qualifier), value),
Condition::Lte(field, value) => Condition::Lte(qualify(field, qualifier), value),
Condition::In(field, values) => Condition::In(qualify(field, qualifier), values),
Condition::NotIn(field, values) => Condition::NotIn(qualify(field, qualifier), values),
Condition::Between(field, start, end) => {
Condition::Between(qualify(field, qualifier), start, end)
}
Condition::Like(field, pattern) => Condition::Like(qualify(field, qualifier), pattern),
Condition::ILike(field, pattern) => Condition::ILike(qualify(field, qualifier), pattern),
Condition::NotILike(field, pattern) => {
Condition::NotILike(qualify(field, qualifier), pattern)
}
Condition::RegexMatch(field, pattern) => {
Condition::RegexMatch(qualify(field, qualifier), pattern)
}
Condition::RegexIMatch(field, pattern) => {
Condition::RegexIMatch(qualify(field, qualifier), pattern)
}
Condition::NotRegexMatch(field, pattern) => {
Condition::NotRegexMatch(qualify(field, qualifier), pattern)
}
Condition::NotRegexIMatch(field, pattern) => {
Condition::NotRegexIMatch(qualify(field, qualifier), pattern)
}
Condition::IsDistinctFrom(field, value) => {
Condition::IsDistinctFrom(qualify(field, qualifier), value)
}
Condition::IsNotDistinctFrom(field, value) => {
Condition::IsNotDistinctFrom(qualify(field, qualifier), value)
}
Condition::IsNull(field) => Condition::IsNull(qualify(field, qualifier)),
Condition::IsNotNull(field) => Condition::IsNotNull(qualify(field, qualifier)),
Condition::ColumnComparison(left, operator, right) => {
Condition::ColumnComparison(qualify(left, qualifier), operator, right)
}
Condition::FieldExpr(field, operator, expression) => {
Condition::FieldExpr(qualify(field, qualifier), operator, expression)
}
Condition::Not(inner) => {
Condition::Not(Box::new(qualify_condition_fields(*inner, qualifier)))
}
Condition::And(conditions) => Condition::And(
conditions
.into_iter()
.map(|condition| qualify_condition_fields(condition, qualifier))
.collect(),
),
Condition::Or(conditions) => Condition::Or(
conditions
.into_iter()
.map(|condition| qualify_condition_fields(condition, qualifier))
.collect(),
),
Condition::JsonContains(field, value) => {
Condition::JsonContains(qualify(field, qualifier), value)
}
Condition::JsonContainedBy(field, value) => {
Condition::JsonContainedBy(qualify(field, qualifier), value)
}
Condition::JsonHasKey(field, key) => Condition::JsonHasKey(qualify(field, qualifier), key),
Condition::Any(field, value) => Condition::Any(qualify(field, qualifier), value),
Condition::All(field, value) => Condition::All(qualify(field, qualifier), value),
Condition::RangeContains(field, value) => {
Condition::RangeContains(qualify(field, qualifier), value)
}
Condition::RangeOverlaps(field, value) => {
Condition::RangeOverlaps(qualify(field, qualifier), value)
}
Condition::JsonPathExists(field, path) => {
Condition::JsonPathExists(qualify(field, qualifier), path)
}
Condition::JsonPathMatch(field, path, vars) => {
Condition::JsonPathMatch(qualify(field, qualifier), path, vars)
}
Condition::FullText(field, query) => Condition::FullText(qualify(field, qualifier), query),
Condition::FullTextQuery(field, query) => {
Condition::FullTextQuery(qualify(field, qualifier), query)
}
Condition::Exists(subquery) => Condition::Exists(subquery),
Condition::NotExists(subquery) => Condition::NotExists(subquery),
Condition::InSubquery(field, subquery) => Condition::InSubquery(field, subquery),
}
}
fn join_condition(left: &crate::FieldRef, right: &crate::FieldRef) -> Condition {
Condition::ColumnComparison(
left.as_str().to_string(),
ComparisonOperator::Eq,
right.as_str().to_string(),
)
}
fn qualify_merge_field(
field: &crate::FieldRef,
qualifier: &str,
) -> Result<String, crate::error::DbError> {
if field.as_str().contains('.') {
super::identifier::quote_qualified(field.as_str())
} else {
super::identifier::quote_qualified(&format!("{qualifier}.{}", field.as_str()))
}
}
struct IntegerPk(i64);
const NEGATIVE_PK_DECODE_PREFIX: &str = "insert() 返回的主键为负数";
fn integer_pk_from_i64(raw: i64) -> Result<IntegerPk, sqlx::error::BoxDynError> {
if raw < 0 {
return Err(format!("{NEGATIVE_PK_DECODE_PREFIX}: {raw}").into());
}
Ok(IntegerPk(raw))
}
impl sqlx::Type<sqlx::Postgres> for IntegerPk {
fn type_info() -> sqlx::postgres::PgTypeInfo {
sqlx::postgres::PgTypeInfo::with_name("INT8")
}
fn compatible(ty: &sqlx::postgres::PgTypeInfo) -> bool {
matches!(ty.name(), "INT2" | "INT4" | "INT8")
}
}
impl<'r> sqlx::Decode<'r, sqlx::Postgres> for IntegerPk {
fn decode(value: sqlx::postgres::PgValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
let raw = match value.type_info().name() {
"INT2" => i64::from(<i16 as sqlx::Decode<'r, sqlx::Postgres>>::decode(value)?),
"INT4" => i64::from(<i32 as sqlx::Decode<'r, sqlx::Postgres>>::decode(value)?),
"INT8" => <i64 as sqlx::Decode<'r, sqlx::Postgres>>::decode(value)?,
other => {
return Err(format!(
"insert() 主键类型 {other} 不是整型;请使用 insert_returning::<T, R>() 按真实类型取回"
)
.into())
}
};
integer_pk_from_i64(raw)
}
}
impl<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> for IntegerPk {
fn from_row(row: &'r sqlx::postgres::PgRow) -> Result<Self, sqlx::Error> {
sqlx::Row::try_get(row, 0)
}
}
fn map_insert_pk_decode_error(detail: String) -> crate::error::DbError {
if let Some((_, value)) = detail.split_once(NEGATIVE_PK_DECODE_PREFIX) {
return crate::error::DbError::InvalidArgument(format!(
"{NEGATIVE_PK_DECODE_PREFIX}{value};insert() 的返回类型为 u64,不支持负数主键。\
需要取回负值请使用 insert_returning::<T, R>() 按真实类型解码"
));
}
crate::error::DbError::InvalidArgument(format!(
"insert() 仅支持整型主键:{detail}。非整型主键请使用 \
insert_returning::<T, R>() 按列的真实类型取回"
))
}
fn validate_conflict_predicate(predicate: &str) -> Result<&str, crate::error::DbError> {
let trimmed = predicate.trim();
if trimmed.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"ON CONFLICT 谓词不能为空".to_string(),
));
}
if trimmed.contains('\0')
|| trimmed.contains(';')
|| trimmed.contains("--")
|| trimmed.contains("/*")
|| trimmed.contains("*/")
{
return Err(crate::error::DbError::InvalidArgument(
"ON CONFLICT 谓词不能包含分号、注释标记或 NUL".to_string(),
));
}
Ok(trimmed)
}
fn comparison_operator(op: crate::CompareOp) -> Option<ComparisonOperator> {
match op {
crate::CompareOp::Eq => Some(ComparisonOperator::Eq),
crate::CompareOp::Ne => Some(ComparisonOperator::Ne),
crate::CompareOp::Gt => Some(ComparisonOperator::Gt),
crate::CompareOp::Lt => Some(ComparisonOperator::Lt),
crate::CompareOp::Gte => Some(ComparisonOperator::Gte),
crate::CompareOp::Lte => Some(ComparisonOperator::Lte),
_ => None,
}
}
fn comparison_condition(
field: &str,
op: crate::CompareOp,
value: SqlValue,
) -> Result<Condition, crate::error::DbError> {
let field = field.to_string();
let pattern = |value: SqlValue| match value {
SqlValue::String(pattern) => Ok(pattern),
_ => Err(crate::error::DbError::UnsupportedOperator(
"模式匹配操作符仅支持字符串模式值".to_string(),
)),
};
match op {
crate::CompareOp::Like => Ok(Condition::Like(field, pattern(value)?)),
crate::CompareOp::ILike => Ok(Condition::ILike(field, pattern(value)?)),
crate::CompareOp::NotILike => Ok(Condition::NotILike(field, pattern(value)?)),
crate::CompareOp::RegexMatch => Ok(Condition::RegexMatch(field, pattern(value)?)),
crate::CompareOp::RegexIMatch => Ok(Condition::RegexIMatch(field, pattern(value)?)),
crate::CompareOp::NotRegexMatch => Ok(Condition::NotRegexMatch(field, pattern(value)?)),
crate::CompareOp::NotRegexIMatch => Ok(Condition::NotRegexIMatch(field, pattern(value)?)),
crate::CompareOp::IsDistinctFrom => Ok(Condition::IsDistinctFrom(field, value)),
crate::CompareOp::IsNotDistinctFrom => Ok(Condition::IsNotDistinctFrom(field, value)),
_ => {
let operator = comparison_operator(op).ok_or_else(|| {
crate::error::DbError::UnsupportedOperator("不支持的比较操作符".to_string())
})?;
Ok(match operator {
ComparisonOperator::Eq => Condition::Eq(field, value),
ComparisonOperator::Ne => Condition::Ne(field, value),
ComparisonOperator::Gt => Condition::Gt(field, value),
ComparisonOperator::Lt => Condition::Lt(field, value),
ComparisonOperator::Gte => Condition::Gte(field, value),
ComparisonOperator::Lte => Condition::Lte(field, value),
})
}
}
}
pub struct QueryBuilder<'a> {
executor: QueryExecutor<'a>,
table: String,
fields: Vec<String>,
projection_exprs: Vec<(crate::SqlExpr, Option<String>)>,
window_exprs: Vec<WindowProjection>,
named_windows: Vec<(String, WindowSpec)>,
conditions: Vec<Condition>,
joins: Vec<JoinSpec<'a>>,
order_by: Vec<OrderClause>,
group_by: Vec<String>,
grouping_constructs: Vec<GroupingConstruct>,
having_clause: Vec<Condition>,
having_exprs: Vec<HavingExpr>,
limit: Option<u64>,
offset: Option<u64>,
distinct: bool,
distinct_on: Vec<String>,
ctes: Vec<Cte<'a>>,
set_ops: Vec<(SetOperator, Box<QueryBuilder<'a>>)>,
field_types: HashMap<String, FieldType>,
set_exprs: Vec<(String, crate::SqlExpr)>,
conflict_target: ConflictTarget,
conflict_predicate: Option<String>,
do_nothing: bool,
update_columns: Option<Vec<String>>,
upsert_where: Vec<Condition>,
returning: ReturningTarget,
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(),
projection_exprs: Vec::new(),
window_exprs: Vec::new(),
named_windows: Vec::new(),
conditions: Vec::new(),
joins: Vec::new(),
order_by: Vec::new(),
group_by: Vec::new(),
grouping_constructs: Vec::new(),
having_clause: Vec::new(),
having_exprs: Vec::new(),
limit: None,
offset: None,
distinct: false,
distinct_on: Vec::new(),
ctes: Vec::new(),
set_ops: Vec::new(),
field_types: HashMap::new(),
set_exprs: Vec::new(),
conflict_target: ConflictTarget::Unspecified,
conflict_predicate: None,
do_nothing: false,
update_columns: None,
upsert_where: Vec::new(),
returning: ReturningTarget::Column("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(),
projection_exprs: Vec::new(),
window_exprs: Vec::new(),
named_windows: Vec::new(),
conditions: Vec::new(),
joins: Vec::new(),
order_by: Vec::new(),
group_by: Vec::new(),
grouping_constructs: Vec::new(),
having_clause: Vec::new(),
having_exprs: Vec::new(),
limit: None,
offset: None,
distinct: false,
distinct_on: Vec::new(),
ctes: Vec::new(),
set_ops: Vec::new(),
field_types: HashMap::new(),
set_exprs: Vec::new(),
conflict_target: ConflictTarget::Unspecified,
conflict_predicate: None,
do_nothing: false,
update_columns: None,
upsert_where: Vec::new(),
returning: ReturningTarget::Column("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 select_expr(mut self, expression: crate::SqlExpr, alias: &crate::FieldRef) -> Self {
self.projection_exprs
.push((expression, Some(alias.as_str().to_string())));
self
}
pub fn set_expr(mut self, field: &crate::FieldRef, expression: crate::SqlExpr) -> Self {
self.set_exprs
.push((field.as_str().to_string(), expression));
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 jsonb(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Jsonb);
self
}
pub fn uuid(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Uuid);
self
}
pub fn date(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Date);
self
}
pub fn time(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::Time);
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 timestamp_tz(mut self, field: &crate::FieldRef) -> Self {
self.field_types
.insert(field.as_str().to_string(), FieldType::TimestampTz);
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 field_type(mut self, field: &crate::FieldRef, field_type: FieldType) -> Self {
self.field_types
.insert(field.as_str().to_string(), field_type);
self
}
pub fn distinct(mut self) -> Self {
self.distinct = true;
self
}
pub fn distinct_on(mut self, fields: &[&crate::FieldRef]) -> Self {
self.distinct_on = fields
.iter()
.map(|field| field.postgres_quoted().to_string())
.collect();
self
}
pub fn with(mut self, name: &str, query: QueryBuilder<'a>) -> Self {
self.ctes.push(Cte {
name: name.to_string(),
recursive: false,
query: Box::new(query),
});
self
}
pub fn with_recursive(mut self, name: &str, query: QueryBuilder<'a>) -> Self {
self.ctes.push(Cte {
name: name.to_string(),
recursive: true,
query: Box::new(query),
});
self
}
pub fn window_expr(
mut self,
function: WindowFunction,
spec: WindowSpec,
alias: &crate::FieldRef,
) -> Self {
self.window_exprs.push(WindowProjection {
function,
spec,
alias: alias.as_str().to_string(),
});
self
}
pub fn window(mut self, name: &str, spec: WindowSpec) -> Self {
self.named_windows.push((name.to_string(), spec));
self
}
pub fn union(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
self.add_set_operation(other, SetOperator::Union)
}
pub fn union_all(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
self.add_set_operation(other, SetOperator::UnionAll)
}
pub fn intersect(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
self.add_set_operation(other, SetOperator::Intersect)
}
pub fn intersect_all(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
self.add_set_operation(other, SetOperator::IntersectAll)
}
pub fn except(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
self.add_set_operation(other, SetOperator::Except)
}
pub fn except_all(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
self.add_set_operation(other, SetOperator::ExceptAll)
}
fn add_set_operation(
mut self,
other: QueryBuilder<'a>,
operator: SetOperator,
) -> Result<Self, crate::error::DbError> {
let left_len = self.projection_len();
let right_len = other.projection_len();
if left_len == 0 || right_len == 0 {
return Err(crate::error::DbError::InvalidArgument(
"集合运算各分支必须显式声明输出字段,不能使用未知列数的 *".to_string(),
));
}
if left_len != right_len {
return Err(crate::error::DbError::InvalidArgument(format!(
"集合运算输出列数不一致:左侧 {left_len} 列,右侧 {right_len} 列"
)));
}
self.set_ops.push((operator, Box::new(other)));
Ok(self)
}
fn projection_len(&self) -> usize {
self.fields.len() + self.projection_exprs.len() + self.window_exprs.len()
}
pub fn grouping_sets(mut self, sets: Vec<Vec<crate::FieldRef>>) -> Self {
self.grouping_constructs
.push(GroupingConstruct::GroupingSets(sets));
self
}
pub fn rollup(mut self, fields: Vec<crate::FieldRef>) -> Self {
self.grouping_constructs
.push(GroupingConstruct::Rollup(fields));
self
}
pub fn cube(mut self, fields: Vec<crate::FieldRef>) -> Self {
self.grouping_constructs
.push(GroupingConstruct::Cube(fields));
self
}
pub fn on_conflict(mut self, columns: &[&crate::FieldRef]) -> Self {
self.conflict_target = ConflictTarget::Columns(
columns
.iter()
.map(|column| column.as_str().to_string())
.collect(),
);
self.conflict_predicate = None;
self
}
pub fn on_conflict_constraint(mut self, constraint: &crate::FieldRef) -> Self {
self.conflict_target = ConflictTarget::Constraint(constraint.as_str().to_string());
self.conflict_predicate = None;
self
}
pub fn on_conflict_with_predicate(
mut self,
columns: &[&crate::FieldRef],
predicate: &str,
) -> Self {
self.conflict_target = ConflictTarget::Columns(
columns
.iter()
.map(|column| column.as_str().to_string())
.collect(),
);
self.conflict_predicate = Some(predicate.to_string());
self
}
pub fn update_columns(mut self, columns: &[&crate::FieldRef]) -> Self {
self.update_columns = Some(
columns
.iter()
.map(|column| column.as_str().to_string())
.collect(),
);
self
}
pub fn do_nothing(mut self) -> Self {
self.do_nothing = true;
self
}
pub fn upsert_where_and<V>(
mut self,
field: &crate::FieldRef,
op: crate::CompareOp,
value: V,
) -> Result<Self, crate::error::DbError>
where
V: Into<SqlValue>,
{
let condition = comparison_condition(field.as_str(), op, value.into())?;
self.upsert_where.push(condition);
Ok(self)
}
pub fn returning(mut self, column: &crate::FieldRef) -> Self {
self.returning = ReturningTarget::Column(column.as_str().to_string());
self
}
pub fn returning_columns(mut self, columns: &[&crate::FieldRef]) -> Self {
self.returning = ReturningTarget::Columns(
columns
.iter()
.map(|column| ReturningColumn {
column: column.as_str().to_string(),
alias: None,
})
.collect(),
);
self
}
pub fn returning_column_as(
mut self,
column: &crate::FieldRef,
alias: &crate::FieldRef,
) -> Self {
let target = ReturningColumn {
column: column.as_str().to_string(),
alias: Some(alias.as_str().to_string()),
};
match &mut self.returning {
ReturningTarget::Columns(columns) => columns.push(target),
ReturningTarget::Column(_) | ReturningTarget::All => {
self.returning = ReturningTarget::Columns(vec![target]);
}
}
self
}
pub fn returning_all(mut self) -> Self {
self.returning = ReturningTarget::All;
self
}
fn returning_clause(&self) -> Result<String, crate::error::DbError> {
match &self.returning {
ReturningTarget::Column(column) => Ok(format!(
" RETURNING {}",
super::identifier::quote_identifier(column)?
)),
ReturningTarget::Columns(columns) => {
if columns.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"returning_columns 至少需要一个列".to_string(),
));
}
let rendered = columns
.iter()
.map(|target| {
let column = super::identifier::quote_identifier(&target.column)?;
match &target.alias {
Some(alias) => Ok(format!(
"{column} AS {}",
super::identifier::quote_identifier(alias)?
)),
None => Ok(column),
}
})
.collect::<Result<Vec<_>, crate::error::DbError>>()?;
Ok(format!(" RETURNING {}", rendered.join(", ")))
}
ReturningTarget::All => Ok(" RETURNING *".to_string()),
}
}
fn coerce_condition_value(&self, field: &str, value: SqlValue) -> SqlValue {
let Some(field_type) = self.field_types.get(field) else {
return value;
};
match (field_type, value) {
(FieldType::Inet, SqlValue::String(value)) => SqlValue::Inet(value),
(FieldType::Cidr, SqlValue::String(value)) => SqlValue::Cidr(value),
(FieldType::MacAddr, SqlValue::String(value)) => SqlValue::MacAddr(value),
(FieldType::MacAddr8, SqlValue::String(value)) => SqlValue::MacAddr8(value),
(_, value) => value,
}
}
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 = self.coerce_condition_value(&field, value.into());
let condition = comparison_condition(&field, op, value)?;
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 name = field.as_str().to_string();
let value = self.coerce_condition_value(&name, value.into());
let condition = comparison_condition(&name, op, value)?;
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 name = field.as_str().to_string();
let sql_values: Vec<_> = values
.into_iter()
.map(|v| self.coerce_condition_value(&name, v.into()))
.collect();
self.conditions.push(Condition::In(name, 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>,
{
let name = field.as_str().to_string();
let start = self.coerce_condition_value(&name, start.into());
let end = self.coerce_condition_value(&name, end.into());
self.conditions.push(Condition::Between(name, start, end));
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 where_not(mut self, condition: Condition) -> Self {
self.conditions.push(Condition::Not(Box::new(condition)));
self
}
pub fn where_column(
mut self,
left: &crate::FieldRef,
op: crate::CompareOp,
right: &crate::FieldRef,
) -> Result<Self, crate::error::DbError> {
let operator = comparison_operator(op).ok_or_else(|| {
crate::error::DbError::UnsupportedOperator(
"列比较不支持 LIKE/ILIKE/正则/NULL-safe 比较符".to_string(),
)
})?;
self.conditions.push(Condition::ColumnComparison(
left.as_str().to_string(),
operator,
right.as_str().to_string(),
));
Ok(self)
}
pub fn where_json_contains<V>(mut self, field: &crate::FieldRef, value: V) -> Self
where
V: Into<SqlValue>,
{
self.conditions.push(Condition::JsonContains(
field.as_str().to_string(),
value.into(),
));
self
}
pub fn where_json_contained_by<V>(mut self, field: &crate::FieldRef, value: V) -> Self
where
V: Into<SqlValue>,
{
self.conditions.push(Condition::JsonContainedBy(
field.as_str().to_string(),
value.into(),
));
self
}
pub fn where_json_has_key(mut self, field: &crate::FieldRef, key: &str) -> Self {
self.conditions.push(Condition::JsonHasKey(
field.as_str().to_string(),
key.to_string(),
));
self
}
pub fn where_json_path_exists(mut self, field: &crate::FieldRef, path: &str) -> Self {
self.conditions.push(Condition::JsonPathExists(
field.as_str().to_string(),
path.to_string(),
));
self
}
pub fn where_json_path_match<V>(mut self, field: &crate::FieldRef, path: &str, vars: V) -> Self
where
V: Into<SqlValue>,
{
self.conditions.push(Condition::JsonPathMatch(
field.as_str().to_string(),
path.to_string(),
vars.into(),
));
self
}
pub fn where_range_contains<V>(mut self, field: &crate::FieldRef, value: V) -> Self
where
V: Into<SqlValue>,
{
self.conditions.push(Condition::RangeContains(
field.as_str().to_string(),
value.into(),
));
self
}
pub fn where_range_overlaps<V>(mut self, field: &crate::FieldRef, value: V) -> Self
where
V: Into<SqlValue>,
{
self.conditions.push(Condition::RangeOverlaps(
field.as_str().to_string(),
value.into(),
));
self
}
pub fn where_full_text(mut self, field: &crate::FieldRef, query: &str) -> Self {
self.conditions.push(Condition::FullText(
field.as_str().to_string(),
query.to_string(),
));
self
}
pub fn where_full_text_query(mut self, field: &crate::FieldRef, query: &str) -> Self {
self.conditions.push(Condition::FullTextQuery(
field.as_str().to_string(),
query.to_string(),
));
self
}
pub fn where_any<V>(mut self, field: &crate::FieldRef, values: Vec<V>) -> Self
where
V: Into<SqlValue>,
{
self.conditions.push(Condition::Any(
field.as_str().to_string(),
SqlValue::Array(values.into_iter().map(Into::into).collect()),
));
self
}
pub fn where_all<V>(mut self, field: &crate::FieldRef, values: Vec<V>) -> Self
where
V: Into<SqlValue>,
{
self.conditions.push(Condition::All(
field.as_str().to_string(),
SqlValue::Array(values.into_iter().map(Into::into).collect()),
));
self
}
pub fn where_expr(
mut self,
field: &crate::FieldRef,
op: crate::CompareOp,
expression: crate::SqlExpr,
) -> Result<Self, crate::error::DbError> {
let operator = comparison_operator(op).ok_or_else(|| {
crate::error::DbError::UnsupportedOperator(
"表达式比较不支持 LIKE/ILIKE/正则/NULL-safe 比较符".to_string(),
)
})?;
self.conditions.push(Condition::FieldExpr(
field.as_str().to_string(),
operator,
expression,
));
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 = self.coerce_condition_value(&field, value.into());
let condition = comparison_condition(&field, op, value)?;
self.having_clause.push(condition);
Ok(self)
}
pub fn having_expr<V>(
mut self,
expression: crate::SelectExpr,
op: crate::CompareOp,
value: V,
) -> Result<Self, crate::error::DbError>
where
V: Into<SqlValue>,
{
let operator = comparison_operator(op).ok_or_else(|| {
crate::error::DbError::UnsupportedOperator(
"HAVING 聚合表达式不支持 LIKE/ILIKE/正则/NULL-safe 比较符".to_string(),
)
})?;
self.having_exprs.push(HavingExpr {
expression,
operator,
value: value.into(),
});
Ok(self)
}
pub fn join(
mut self,
table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
self.joins.push(JoinSpec {
kind: JoinKind::Inner,
target: JoinTarget::Table(table.as_str().to_string()),
condition: JoinCondition::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 {
self.joins.push(JoinSpec {
kind: JoinKind::Left,
target: JoinTarget::Table(table.as_str().to_string()),
condition: JoinCondition::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 {
self.joins.push(JoinSpec {
kind: JoinKind::Right,
target: JoinTarget::Table(table.as_str().to_string()),
condition: JoinCondition::On(format!(
"{} = {}",
left.postgres_quoted(),
right.postgres_quoted()
)),
});
self
}
pub fn full_join(
mut self,
table: &crate::TableRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
self.joins.push(JoinSpec {
kind: JoinKind::Full,
target: JoinTarget::Table(table.as_str().to_string()),
condition: JoinCondition::On(format!(
"{} = {}",
left.postgres_quoted(),
right.postgres_quoted()
)),
});
self
}
pub fn cross_join(mut self, table: &crate::TableRef) -> Self {
self.joins.push(JoinSpec {
kind: JoinKind::Cross,
target: JoinTarget::Table(table.as_str().to_string()),
condition: JoinCondition::None,
});
self
}
pub fn natural_join(mut self, table: &crate::TableRef) -> Self {
self.joins.push(JoinSpec {
kind: JoinKind::Natural,
target: JoinTarget::Table(table.as_str().to_string()),
condition: JoinCondition::None,
});
self
}
pub fn join_using(mut self, table: &crate::TableRef, columns: &[&crate::FieldRef]) -> Self {
self.joins.push(JoinSpec {
kind: JoinKind::Inner,
target: JoinTarget::Table(table.as_str().to_string()),
condition: JoinCondition::Using(
columns
.iter()
.map(|column| column.as_str().to_string())
.collect(),
),
});
self
}
pub fn left_join_lateral(
mut self,
query: QueryBuilder<'a>,
alias: &crate::FieldRef,
left: &crate::FieldRef,
right: &crate::FieldRef,
) -> Self {
self.joins.push(JoinSpec {
kind: JoinKind::LeftLateral,
target: JoinTarget::Lateral(Box::new(query), alias.as_str().to_string()),
condition: JoinCondition::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.ensure_row_lock_supported()?;
}
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))
}
fn ensure_row_lock_supported(&self) -> Result<(), crate::error::DbError> {
if self.distinct
|| !self.distinct_on.is_empty()
|| !self.group_by.is_empty()
|| !self.grouping_constructs.is_empty()
|| !self.having_clause.is_empty()
|| !self.having_exprs.is_empty()
|| !self.window_exprs.is_empty()
|| !self.ctes.is_empty()
|| !self.set_ops.is_empty()
{
return Err(crate::error::DbError::InvalidArgument(
"行锁不支持 DISTINCT、GROUP BY/分组集、HAVING、窗口函数、CTE 或集合运算查询"
.to_string(),
));
}
Ok(())
}
pub fn try_to_sql_with_lock(
&self,
lock: crate::RowLock,
of: &[&crate::FieldRef],
nowait: bool,
skip_locked: bool,
) -> Result<String, crate::error::DbError> {
Ok(self.render_with_lock(lock, of, nowait, skip_locked)?.0)
}
pub(crate) fn render_with_lock(
&self,
lock: crate::RowLock,
of: &[&crate::FieldRef],
nowait: bool,
skip_locked: bool,
) -> Result<(String, Vec<SqlValue>), crate::error::DbError> {
let implied_skip = matches!(
lock,
crate::RowLock::ForUpdateSkipLocked | crate::RowLock::ForShareSkipLocked
);
if nowait && (skip_locked || implied_skip) {
return Err(crate::error::DbError::InvalidArgument(
"行锁的 NOWAIT 与 SKIP LOCKED 不能同时使用".to_string(),
));
}
self.ensure_row_lock_supported()?;
let mut generator = SqlGenerator::new();
generator.build_select(self)?;
let base = match lock {
crate::RowLock::ForUpdate | crate::RowLock::ForUpdateSkipLocked => "FOR UPDATE",
crate::RowLock::ForShare | crate::RowLock::ForShareSkipLocked => "FOR SHARE",
crate::RowLock::NoKeyUpdate => "FOR NO KEY UPDATE",
crate::RowLock::KeyShare => "FOR KEY SHARE",
};
let mut clause = String::from(base);
if !of.is_empty() {
let quoted = of
.iter()
.map(|table| super::identifier::quote_qualified(table.as_str()))
.collect::<Result<Vec<_>, _>>()?;
clause.push_str(" OF ");
clause.push_str("ed.join(", "));
}
if skip_locked
|| matches!(
lock,
crate::RowLock::ForUpdateSkipLocked | crate::RowLock::ForShareSkipLocked
)
{
clause.push_str(" SKIP LOCKED");
} else if nowait {
clause.push_str(" NOWAIT");
}
generator.sql.push(' ');
generator.sql.push_str(&clause);
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 !matches!(self.returning, ReturningTarget::Column(_)) {
return Err(crate::error::DbError::InvalidArgument(
"insert() 仅支持单列 RETURNING;多列或整行请使用 insert_returning::<T, R>()"
.to_string(),
));
}
let id: IntegerPk = self
.insert_returning(data)
.await
.map_err(|error| match error {
crate::error::DbError::TypeConversionError(detail) => {
map_insert_pk_decode_error(detail)
}
other => other,
})?;
u64::try_from(id.0).map_err(|_| {
crate::error::DbError::InvalidArgument(NEGATIVE_PK_DECODE_PREFIX.to_string())
})
}
#[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_returning<T, R>(self, data: &T) -> Result<R, crate::error::DbError>
where
T: serde::Serialize,
R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
if self.enable_logging {
log::debug!("执行 insert_returning() 操作,表: {}", 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, &self.set_exprs)?;
let sql = format!("{}{}", generator.get_sql(), self.returning_clause()?);
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 insert_returning() SQL: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query_as::<_, R>(&sql);
for param in params {
query = bind_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => {
let mut tx = pool.begin().await.map_err(crate::error::DbError::from)?;
match query.fetch_one(&mut *tx).await {
Ok(row) => {
tx.commit().await.map_err(crate::error::DbError::from)?;
Ok(row)
}
Err(error) => {
let _ = tx.rollback().await;
Err(error)
}
}
}
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(row) => Ok(row),
Err(error) => {
let db_err = crate::error::DbError::from(error);
if db_err.is_constraint_violation() {
log::warn!(
"insert_returning() 失败: {}(约束冲突,可能为预期分支)",
db_err
);
} else {
log::error!("insert_returning() 失败: {}", db_err);
}
Err(db_err)
}
}
}
#[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.set_exprs.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"insert_batch 不支持 set_expr;请逐条 insert/insert_returning".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 column_count = serde_json::to_value(&data[0])
.map_err(|e| crate::error::DbError::SerializationError(format!("数据序列化失败: {e}")))?
.as_object()
.map(|object| object.len())
.unwrap_or(0);
let batch_size = derive_insert_batch_size(column_count, batch_size);
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) => {
let db_err = crate::error::DbError::from(e);
if db_err.is_constraint_violation() {
log::warn!(
"insert_chunk() 失败: {}(约束冲突,可能为预期分支)",
db_err
);
} else {
log::error!("insert_chunk() 失败: {}", db_err);
}
Err(db_err)
}
}
}
#[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_returning<T, R>(
self,
data: &[T],
) -> Result<Vec<R>, crate::error::DbError>
where
T: serde::Serialize,
R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
if !self.set_exprs.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"insert_batch_returning 不支持 set_expr;请逐条 insert_returning".to_string(),
));
}
if data.is_empty() {
return Err(crate::error::DbError::SerializationError(
"批量插入数据不能为空".to_string(),
));
}
let returning = self.returning_clause()?;
let json_data_list: Vec<serde_json::Value> = data
.iter()
.map(|item| {
serde_json::to_value(item).map_err(|error| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
})
})
.collect::<Result<_, _>>()?;
let column_count = json_data_list[0]
.as_object()
.map(|object| object.len())
.unwrap_or(0);
let batch_size = derive_insert_batch_size(column_count, INSERT_BATCH_SIZE);
let mut rows = Vec::new();
match self.executor {
QueryExecutor::Pool(pool) => {
let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
for chunk in json_data_list.chunks(batch_size) {
rows.extend(
execute_insert_batch_returning::<R>(
&mut transaction,
&self.table,
chunk,
&self.field_types,
&returning,
)
.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_data_list.chunks(batch_size) {
rows.extend(
execute_insert_batch_returning::<R>(
&mut *connection,
&self.table,
chunk,
&self.field_types,
&returning,
)
.await?,
);
}
}
}
Ok(rows)
}
#[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_batch_returning<T, R>(
self,
data: &[T],
conflict_columns: &[&crate::FieldRef],
) -> Result<Vec<R>, crate::error::DbError>
where
T: serde::Serialize,
R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
if !self.set_exprs.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"upsert_batch_returning 不支持 set_expr;请逐条 upsert_returning".to_string(),
));
}
if data.is_empty() {
return Err(crate::error::DbError::SerializationError(
"批量 upsert 数据不能为空".to_string(),
));
}
let conflict: Vec<String> = conflict_columns
.iter()
.map(|column| column.as_str().to_string())
.collect();
if conflict.is_empty() && !self.do_nothing {
return Err(crate::error::DbError::InvalidArgument(
"批量 upsert 必须显式指定冲突列;如使用 id 请显式传入,\
或调用 do_nothing() 走 ON CONFLICT DO NOTHING"
.to_string(),
));
}
let returning = self.returning_clause()?;
let json_data_list: Vec<serde_json::Value> = data
.iter()
.map(|item| {
serde_json::to_value(item).map_err(|error| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
})
})
.collect::<Result<_, _>>()?;
let column_count = json_data_list[0]
.as_object()
.map(|object| object.len())
.unwrap_or(0);
let batch_size = derive_upsert_batch_size(column_count, INSERT_BATCH_SIZE);
let mut rows = Vec::new();
match self.executor {
QueryExecutor::Pool(pool) => {
let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
for chunk in json_data_list.chunks(batch_size) {
rows.extend(
execute_upsert_batch_returning::<R>(
&mut transaction,
&self.table,
chunk,
&self.field_types,
&conflict,
self.conflict_predicate.as_deref(),
self.do_nothing,
self.update_columns.as_deref(),
&self.upsert_where,
&returning,
)
.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_data_list.chunks(batch_size) {
rows.extend(
execute_upsert_batch_returning::<R>(
&mut *connection,
&self.table,
chunk,
&self.field_types,
&conflict,
self.conflict_predicate.as_deref(),
self.do_nothing,
self.update_columns.as_deref(),
&self.upsert_where,
&returning,
)
.await?,
);
}
}
}
Ok(rows)
}
#[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,
&self.set_exprs,
)?;
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) => {
let db_err = crate::error::DbError::from(e);
if db_err.is_constraint_violation() {
log::warn!("update() 失败: {}(约束冲突,可能为预期分支)", db_err);
} else {
log::error!("update() 失败: {}", db_err);
}
Err(db_err)
}
}
}
#[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_returning<T, R>(self, data: &T) -> Result<Vec<R>, crate::error::DbError>
where
T: serde::Serialize,
R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
if self.enable_logging {
log::debug!("执行 update_returning() 操作,表: {}", self.table);
}
if self.conditions.is_empty() {
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,
&self.set_exprs,
)?;
let sql = format!("{}{}", generator.get_sql(), self.returning_clause()?);
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 update_returning() SQL: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query_as::<_, R>(&sql);
for param in params {
query = bind_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => {
let mut tx = pool.begin().await.map_err(crate::error::DbError::from)?;
match query.fetch_all(&mut *tx).await {
Ok(rows) => {
tx.commit().await.map_err(crate::error::DbError::from)?;
Ok(rows)
}
Err(error) => {
let _ = tx.rollback().await;
Err(error)
}
}
}
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(error) => {
let db_err = crate::error::DbError::from(error);
if db_err.is_constraint_violation() {
log::warn!(
"update_returning() 失败: {}(约束冲突,可能为预期分支)",
db_err
);
} else {
log::error!("update_returning() 失败: {}", db_err);
}
Err(db_err)
}
}
}
#[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) => {
let db_err = crate::error::DbError::from(e);
if db_err.is_constraint_violation() {
log::warn!("delete() 失败: {}(约束冲突,可能为预期分支)", db_err);
} else {
log::error!("delete() 失败: {}", db_err);
}
Err(db_err)
}
}
}
#[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_returning<R>(self) -> Result<Vec<R>, crate::error::DbError>
where
R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
if self.enable_logging {
log::debug!("执行 delete_returning() 操作,表: {}", self.table);
}
if self.conditions.is_empty() {
return Err(crate::error::DbError::MissingWhereClause);
}
let mut generator = SqlGenerator::new();
generator.build_delete(&self.table, &self.conditions)?;
let sql = format!("{}{}", generator.get_sql(), self.returning_clause()?);
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 delete_returning() SQL: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query_as::<_, R>(&sql);
for param in params {
query = bind_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => {
let mut tx = pool.begin().await.map_err(crate::error::DbError::from)?;
match query.fetch_all(&mut *tx).await {
Ok(rows) => {
tx.commit().await.map_err(crate::error::DbError::from)?;
Ok(rows)
}
Err(error) => {
let _ = tx.rollback().await;
Err(error)
}
}
}
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(error) => {
let db_err = crate::error::DbError::from(error);
if db_err.is_constraint_violation() {
log::warn!(
"delete_returning() 失败: {}(约束冲突,可能为预期分支)",
db_err
);
} else {
log::error!("delete_returning() 失败: {}", db_err);
}
Err(db_err)
}
}
}
#[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_from_select(
self,
columns: &[&crate::FieldRef],
source: QueryBuilder<'a>,
) -> Result<u64, crate::error::DbError> {
let mut generator = SqlGenerator::new();
generator.build_insert_from_select(&self.table, columns, &source)?;
if self.enable_logging {
log::debug!("执行 insert_from_select() SQL: {}", generator.get_sql());
}
execute_generated(self.executor, generator.get_sql(), generator.get_params()).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_from<T>(
self,
data: &T,
from: &crate::TableRef,
target_field: &crate::FieldRef,
source_field: &crate::FieldRef,
) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
if self.conditions.is_empty() {
return Err(crate::error::DbError::MissingWhereClause);
}
let json_data = serde_json::to_value(data).map_err(|error| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
})?;
let mut generator = SqlGenerator::new();
generator.build_update_from(
&self.table,
&json_data,
&self.field_types,
&self.set_exprs,
&self.conditions,
from,
target_field,
source_field,
)?;
if self.enable_logging {
log::debug!("执行 update_from() SQL: {}", generator.get_sql());
}
execute_generated(self.executor, generator.get_sql(), generator.get_params()).await
}
#[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_using(
self,
using: &crate::TableRef,
target_field: &crate::FieldRef,
source_field: &crate::FieldRef,
) -> Result<u64, crate::error::DbError> {
if self.conditions.is_empty() {
return Err(crate::error::DbError::MissingWhereClause);
}
let mut generator = SqlGenerator::new();
generator.build_delete_using(
&self.table,
&self.conditions,
using,
target_field,
source_field,
)?;
if self.enable_logging {
log::debug!("执行 delete_using() SQL: {}", generator.get_sql());
}
execute_generated(self.executor, generator.get_sql(), generator.get_params()).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<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(),
));
}
if !self.set_exprs.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"update_batch 不支持 set_expr();请改用逐条 update() 或在数据中提供字面量"
.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.set_exprs.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"upsert 不支持 set_expr;请在插入数据中直接提供列值".to_string(),
));
}
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_target,
self.conflict_predicate.as_deref(),
self.do_nothing,
self.update_columns.as_deref(),
&self.upsert_where,
)?;
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)
}
#[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_returning<T, R>(self, data: &T) -> Result<R, crate::error::DbError>
where
T: serde::Serialize,
R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
if !self.set_exprs.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"upsert 不支持 set_expr;请在插入数据中直接提供列值".to_string(),
));
}
if self.enable_logging {
log::debug!("执行 upsert_returning() 操作,表: {}", 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_target,
self.conflict_predicate.as_deref(),
self.do_nothing,
self.update_columns.as_deref(),
&self.upsert_where,
)?;
let sql = format!("{}{}", generator.get_sql(), self.returning_clause()?);
let params = generator.get_params();
if self.enable_logging {
log::debug!("执行 upsert_returning() SQL: {}", sql);
}
let mut query = sqlx::query_as::<_, R>(&sql);
for param in params {
query = bind_param(query, param);
}
let result = match self.executor {
QueryExecutor::Pool(pool) => {
let mut tx = pool.begin().await.map_err(crate::error::DbError::from)?;
match query.fetch_one(&mut *tx).await {
Ok(row) => {
tx.commit().await.map_err(crate::error::DbError::from)?;
Ok(row)
}
Err(error) => {
let _ = tx.rollback().await;
Err(error)
}
}
}
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
query.fetch_one(&mut *connection).await
}
};
result.map_err(|error| {
let db_err = crate::error::DbError::from(error);
if db_err.is_constraint_violation() {
log::warn!(
"upsert_returning() 失败: {}(约束冲突,可能为预期分支)",
db_err
);
} else {
log::error!("upsert_returning() 失败: {}", db_err);
}
db_err
})
}
#[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_batch<T>(
self,
data: &[T],
conflict_columns: &[&crate::FieldRef],
) -> Result<u64, crate::error::DbError>
where
T: serde::Serialize,
{
self.upsert_batch_with_size(data, conflict_columns, INSERT_BATCH_SIZE)
.await
}
#[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_batch_with_size<T>(
self,
data: &[T],
conflict_columns: &[&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 !self.set_exprs.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"upsert_batch 不支持 set_expr;请改用 upsert() 或 update()".to_string(),
));
}
if data.is_empty() {
return Err(crate::error::DbError::SerializationError(
"批量 upsert 数据不能为空".to_string(),
));
}
let conflict: Vec<String> = conflict_columns
.iter()
.map(|column| column.as_str().to_string())
.collect();
if conflict.is_empty() && !self.do_nothing {
return Err(crate::error::DbError::InvalidArgument(
"批量 upsert 必须显式指定冲突列;如使用 id 请显式传入,\
或调用 do_nothing() 走 ON CONFLICT DO NOTHING"
.to_string(),
));
}
let json_data_list: Vec<serde_json::Value> = data
.iter()
.map(|item| {
serde_json::to_value(item).map_err(|error| {
crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
})
})
.collect::<Result<_, _>>()?;
let column_count = json_data_list[0]
.as_object()
.map(|object| object.len())
.unwrap_or(0);
let effective = derive_upsert_batch_size(column_count, batch_size);
let chunk_count = json_data_list.len().div_ceil(effective);
let mut total = 0u64;
if chunk_count == 1 {
match self.executor {
QueryExecutor::Pool(pool) => {
let mut connection =
pool.acquire().await.map_err(crate::error::DbError::from)?;
total += execute_upsert_batch_chunk(
&mut connection,
&self.table,
&json_data_list,
&self.field_types,
&conflict,
self.conflict_predicate.as_deref(),
self.do_nothing,
self.update_columns.as_deref(),
&self.upsert_where,
)
.await?;
}
QueryExecutor::Transaction(transaction) => {
let connection = transaction.executor().ok_or_else(|| {
crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
})?;
total += execute_upsert_batch_chunk(
&mut *connection,
&self.table,
&json_data_list,
&self.field_types,
&conflict,
self.conflict_predicate.as_deref(),
self.do_nothing,
self.update_columns.as_deref(),
&self.upsert_where,
)
.await?;
}
}
return Ok(total);
}
match self.executor {
QueryExecutor::Pool(pool) => {
let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
for chunk in json_data_list.chunks(effective) {
total += execute_upsert_batch_chunk(
&mut transaction,
&self.table,
chunk,
&self.field_types,
&conflict,
self.conflict_predicate.as_deref(),
self.do_nothing,
self.update_columns.as_deref(),
&self.upsert_where,
)
.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_data_list.chunks(effective) {
total += execute_upsert_batch_chunk(
&mut *connection,
&self.table,
chunk,
&self.field_types,
&conflict,
self.conflict_predicate.as_deref(),
self.do_nothing,
self.update_columns.as_deref(),
&self.upsert_where,
)
.await?;
}
}
}
if self.enable_logging {
log::debug!("upsert_batch_with_size() 完成,rows_affected: {total}");
}
Ok(total)
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "merge", db.collection = %self.table, otel.kind = "client")
)]
pub async fn merge(
self,
source: &crate::TableRef,
target_key: &crate::FieldRef,
source_key: &crate::FieldRef,
columns: &[&crate::FieldRef],
) -> Result<u64, crate::error::DbError> {
self.merge_with_options(
source,
target_key,
source_key,
columns,
MergeOptions::default(),
)
.await
}
#[tracing::instrument(
name = "db.query",
skip_all,
fields(db.system = "postgresql", db.operation = "merge", db.collection = %self.table, otel.kind = "client")
)]
pub async fn merge_with_options(
mut self,
source: &crate::TableRef,
target_key: &crate::FieldRef,
source_key: &crate::FieldRef,
columns: &[&crate::FieldRef],
options: MergeOptions,
) -> Result<u64, crate::error::DbError> {
if !self.conditions.is_empty() || !self.set_exprs.is_empty() {
return Err(crate::error::DbError::InvalidArgument(
"merge 不支持 where / set_expr;匹配条件请放入 ON 参数".to_string(),
));
}
let version = server_version_num(&mut self.executor).await?;
if !merge_version_supported(version) {
return Err(crate::error::DbError::UnsupportedOperator(format!(
"MERGE 需要 PostgreSQL 15+(server_version_num >= {MERGE_MIN_SERVER_VERSION}),当前为 {version}"
)));
}
if options.not_matched_by_source.is_some() && version < MERGE_BY_SOURCE_MIN_SERVER_VERSION {
return Err(crate::error::DbError::UnsupportedOperator(format!(
"WHEN NOT MATCHED BY SOURCE 需要 PostgreSQL 17+(server_version_num >= {MERGE_BY_SOURCE_MIN_SERVER_VERSION}),当前为 {version}"
)));
}
let mut generator = SqlGenerator::new();
generator.build_merge_with_options(
&self.table,
source,
target_key,
source_key,
columns,
&options,
)?;
if self.enable_logging {
log::debug!("执行 merge_with_options() SQL: {}", generator.get_sql());
}
execute_generated(self.executor, generator.get_sql(), generator.get_params()).await
}
}
#[allow(clippy::too_many_arguments)]
async fn execute_upsert_batch_chunk(
connection: &mut sqlx::PgConnection,
table: &str,
data: &[serde_json::Value],
field_types: &HashMap<String, FieldType>,
conflict_columns: &[String],
conflict_predicate: Option<&str>,
do_nothing: bool,
update_columns: Option<&[String]>,
update_where: &[Condition],
) -> Result<u64, crate::error::DbError> {
let mut generator = SqlGenerator::new();
generator.build_upsert_batch(
table,
data,
field_types,
conflict_columns,
conflict_predicate,
do_nothing,
update_columns,
update_where,
)?;
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 array_element_bool(value: &SqlValue) -> Option<bool> {
match value {
SqlValue::Bool(value) => Some(*value),
_ => None,
}
}
fn array_element_i64(value: &SqlValue) -> Option<i64> {
match value {
SqlValue::Int(value) => Some(*value),
_ => None,
}
}
fn array_element_i16(value: &SqlValue) -> Option<i16> {
array_element_i64(value).and_then(|value| i16::try_from(value).ok())
}
fn array_element_i32(value: &SqlValue) -> Option<i32> {
array_element_i64(value).and_then(|value| i32::try_from(value).ok())
}
fn array_element_f32(value: &SqlValue) -> Option<f32> {
match value {
SqlValue::Float(value) => Some(*value as f32),
SqlValue::Int(value) => Some(*value as f32),
_ => None,
}
}
fn array_element_bytes(value: &SqlValue) -> Option<Vec<u8>> {
match value {
SqlValue::Bytes(value) => Some(value.clone()),
_ => None,
}
}
fn array_element_json(value: &SqlValue) -> Option<serde_json::Value> {
match value {
SqlValue::Json(value) => Some(value.clone()),
_ => None,
}
}
fn array_element_f64(value: &SqlValue) -> Option<f64> {
match value {
SqlValue::Float(value) => Some(*value),
_ => None,
}
}
fn array_element_string(value: &SqlValue) -> Option<String> {
match value {
SqlValue::String(value)
| SqlValue::Inet(value)
| SqlValue::Cidr(value)
| SqlValue::MacAddr(value)
| SqlValue::MacAddr8(value)
| SqlValue::Range(value)
| SqlValue::Multirange(value) => Some(value.clone()),
_ => None,
}
}
fn array_element_timestamp(value: &SqlValue) -> Option<i64> {
match value {
SqlValue::Timestamp(value) => Some(*value),
_ => None,
}
}
fn array_element_uuid(value: &SqlValue) -> Option<uuid::Uuid> {
match value {
SqlValue::Uuid(value) => Some(*value),
_ => None,
}
}
fn array_element_date(value: &SqlValue) -> Option<chrono::NaiveDate> {
match value {
SqlValue::Date(value) => Some(*value),
_ => None,
}
}
fn array_element_time(value: &SqlValue) -> Option<chrono::NaiveTime> {
match value {
SqlValue::Time(value) => Some(*value),
_ => None,
}
}
fn array_element_datetime(value: &SqlValue) -> Option<chrono::NaiveDateTime> {
match value {
SqlValue::DateTime(value) => Some(*value),
_ => None,
}
}
fn array_element_datetime_tz(value: &SqlValue) -> Option<chrono::DateTime<chrono::Utc>> {
match value {
SqlValue::DateTimeTz(value) => Some(*value),
_ => None,
}
}
fn array_element_decimal(value: &SqlValue) -> Option<bigdecimal::BigDecimal> {
match value {
SqlValue::Decimal(value) => Some(value.clone()),
_ => None,
}
}
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_generated(
executor: QueryExecutor<'_>,
sql: &str,
params: &[SqlValue],
) -> Result<u64, crate::error::DbError> {
let mut query = sqlx::query(sql);
for param in params {
query = bind_execute_param(query, param);
}
let result = match 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)?;
Ok(result.rows_affected())
}
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_insert_batch_returning<R>(
connection: &mut sqlx::PgConnection,
table: &str,
data: &[serde_json::Value],
field_types: &HashMap<String, FieldType>,
returning: &str,
) -> Result<Vec<R>, crate::error::DbError>
where
R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
let mut generator = SqlGenerator::new();
generator.build_insert_batch(table, data, field_types)?;
let sql = format!("{}{}", generator.get_sql(), returning);
let mut query = sqlx::query_as::<_, R>(&sql);
for param in generator.get_params() {
query = bind_param(query, param);
}
Ok(query.fetch_all(connection).await?)
}
#[allow(clippy::too_many_arguments)]
async fn execute_upsert_batch_returning<R>(
connection: &mut sqlx::PgConnection,
table: &str,
data: &[serde_json::Value],
field_types: &HashMap<String, FieldType>,
conflict_columns: &[String],
conflict_predicate: Option<&str>,
do_nothing: bool,
update_columns: Option<&[String]>,
update_where: &[Condition],
returning: &str,
) -> Result<Vec<R>, crate::error::DbError>
where
R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
let mut generator = SqlGenerator::new();
generator.build_upsert_batch(
table,
data,
field_types,
conflict_columns,
conflict_predicate,
do_nothing,
update_columns,
update_where,
)?;
let sql = format!("{}{}", generator.get_sql(), returning);
let mut query = sqlx::query_as::<_, R>(&sql);
for param in generator.get_params() {
query = bind_param(query, param);
}
Ok(query.fetch_all(connection).await?)
}
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) => {
comparison_condition(field.as_str(), *operator, predicate_value(value))?
}
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.d", "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.d",
"users.id --",
"users/*x*/.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.d",
"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);
}
#[tokio::test]
async fn test_insert_rejects_returning_all_before_io() {
let pool = PgPoolOptions::new()
.max_connections(1)
.connect_lazy("postgres://postgres:postgres@localhost:5432/test")
.expect("无法解析测试数据库 URL");
let error = QueryBuilder::new(&pool, "users", false)
.returning_all()
.insert(&serde_json::json!({"name": "张三"}))
.await
.expect_err("returning_all 的 insert 应在触库前失败");
assert!(matches!(error, crate::DbError::InvalidArgument(_)));
}
#[test]
fn test_insert_set_expr_appends_after_json_columns() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"name": "张三"});
let set_exprs = vec![(
"created_at".to_string(),
crate::SqlExpr::unix_timestamp_add(5),
)];
g.build_insert("users", &data, &empty_types(), &set_exprs)
.unwrap();
assert_eq!(
g.get_sql(),
"INSERT INTO \"users\" (\"name\", \"created_at\") VALUES ($1, \
EXTRACT(EPOCH FROM now()) + $2)"
);
assert_eq!(g.get_params().len(), 2);
}
#[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 = ConflictTarget::Columns(vec!["id".to_string()]);
g.build_upsert(
"users",
&data,
&empty_types(),
&conflict,
None,
false,
None,
&[],
)
.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(),
&ConflictTarget::Columns(vec![]),
None,
true,
None,
&[],
)
.unwrap();
assert!(g.get_sql().contains("ON CONFLICT DO NOTHING"));
}
#[test]
fn test_build_upsert_excluded_field_is_lowercase_pseudo_table() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"id": 1, "name": "a"});
let where_cond = vec![Condition::Gt(
"EXCLUDED.name".to_string(),
SqlValue::String("x".to_string()),
)];
g.build_upsert(
"users",
&data,
&empty_types(),
&ConflictTarget::Columns(vec!["id".to_string()]),
None,
false,
None,
&where_cond,
)
.unwrap();
let sql = g.get_sql();
assert!(sql.contains("excluded.\"name\""), "SQL: {sql}");
assert!(!sql.contains("\"EXCLUDED\""), "SQL: {sql}");
}
#[test]
fn test_build_upsert_constraint_excludes_id_by_default() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"id": 999, "email": "a@x", "name": "n"});
g.build_upsert(
"users",
&data,
&empty_types(),
&ConflictTarget::Constraint("users_email_key".to_string()),
None,
false,
None,
&[],
)
.unwrap();
let sql = g.get_sql();
assert!(sql.contains("\"name\" = EXCLUDED.\"name\""), "SQL: {sql}");
assert!(sql.contains("\"email\" = EXCLUDED.\"email\""), "SQL: {sql}");
assert!(!sql.contains("\"id\" = EXCLUDED.\"id\""), "SQL: {sql}");
}
#[test]
fn test_build_upsert_constraint_without_updatable_columns_errors() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"id": 1});
let err = g
.build_upsert(
"users",
&data,
&empty_types(),
&ConflictTarget::Constraint("users_pkey".to_string()),
None,
false,
None,
&[],
)
.unwrap_err();
assert!(matches!(err, crate::error::DbError::InvalidArgument(_)));
}
#[tokio::test]
async fn test_update_batch_rejects_set_expr_before_io() {
let pool = PgPoolOptions::new()
.max_connections(1)
.connect_lazy("postgres://postgres:postgres@localhost:5432/test")
.expect("无法解析测试数据库 URL");
let error = QueryBuilder::new(&pool, "users", false)
.set_expr(crate::field!("updated_at"), crate::SqlExpr::now())
.update_batch(&[serde_json::json!({"id": 1})], crate::field!("id"))
.await
.expect_err("update_batch 应拒绝 set_expr");
assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
}
#[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, ReturningTarget::Column("id".to_string()));
assert_eq!(qb.conflict_target, ConflictTarget::Unspecified);
let qb2 = qb
.returning(yang_db::field!("uid"))
.on_conflict(&[yang_db::field!("email"), yang_db::field!("tenant_id")]);
assert_eq!(qb2.returning, ReturningTarget::Column("uid".to_string()));
assert_eq!(
qb2.conflict_target,
ConflictTarget::Columns(vec!["email".to_string(), "tenant_id".to_string()])
);
}
#[test]
fn test_returning_all_renders_star() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users", false).returning_all();
assert_eq!(builder.returning_clause().unwrap(), " RETURNING *");
assert_eq!(
builder.returning_clause().unwrap(),
" RETURNING *".to_string()
);
}
#[test]
fn test_on_conflict_constraint_and_update_columns() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"id": 1, "name": "张三", "email": "z@example.com"});
g.build_upsert(
"users",
&data,
&empty_types(),
&ConflictTarget::Constraint("users_email_key".to_string()),
None,
false,
Some(&["name".to_string()]),
&[],
)
.unwrap();
let sql = g.get_sql();
assert!(sql.contains("ON CONFLICT ON CONSTRAINT \"users_email_key\" DO UPDATE SET"));
assert!(sql.contains("\"name\" = EXCLUDED.\"name\""));
assert!(!sql.contains("\"email\" = EXCLUDED.\"email\""));
}
#[test]
fn test_upsert_do_nothing_ignores_update_columns() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"id": 1, "name": "张三"});
g.build_upsert(
"users",
&data,
&empty_types(),
&ConflictTarget::Columns(vec!["id".to_string()]),
None,
true,
Some(&["name".to_string()]),
&[],
)
.unwrap();
let sql = g.get_sql();
assert!(sql.contains("ON CONFLICT (\"id\") DO NOTHING"));
assert!(!sql.contains("DO UPDATE"));
}
#[test]
fn test_upsert_where_continues_placeholder_numbering() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"id": 1, "name": "张三"});
let update_where = vec![Condition::Gt("version".to_string(), SqlValue::Int(2))];
g.build_upsert(
"users",
&data,
&empty_types(),
&ConflictTarget::Columns(vec!["id".to_string()]),
None,
false,
None,
&update_where,
)
.unwrap();
let sql = g.get_sql();
assert!(
sql.ends_with("WHERE \"users\".\"version\" > $3"),
"实际 SQL: {sql}"
);
assert_eq!(g.get_params().len(), 3);
}
#[test]
fn test_update_set_expr_continues_numbering() {
let mut g = SqlGenerator::new();
let data = serde_json::json!({"name": "张三"});
let conditions = vec![Condition::Eq("id".to_string(), SqlValue::Int(1))];
let set_exprs = vec![(
"updated_at".to_string(),
crate::SqlExpr::unix_timestamp_add(30),
)];
g.build_update("users", &data, &empty_types(), &conditions, &set_exprs)
.unwrap();
let sql = g.get_sql();
assert_eq!(
sql,
"UPDATE \"users\" SET \"name\" = $1, \"updated_at\" = EXTRACT(EPOCH FROM now()) + $2 \
WHERE \"id\" = $3"
);
assert_eq!(g.get_params().len(), 3);
}
#[test]
fn test_select_expr_numbering_precedes_where_params() {
let pool = make_sync_test_pool();
let builder = QueryBuilder::new(pool, "users", false)
.select_expr(
crate::SqlExpr::unix_timestamp_add(60),
yang_db::field!("ts"),
)
.where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 7)
.expect("固定受控条件应合法");
let sql = builder.try_to_sql().unwrap();
assert_eq!(
sql,
"SELECT EXTRACT(EPOCH FROM now()) + $1 AS \"ts\" FROM \"users\" WHERE \"id\" = $2"
);
}
#[test]
fn test_where_expr_renders_controlled_expression() {
let pool = make_sync_test_pool();
let sql = QueryBuilder::new(pool, "users", false)
.where_expr(
yang_db::field!("updated_at"),
yang_db::CompareOp::Lt,
crate::SqlExpr::now(),
)
.expect("受控表达式比较应合法")
.where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 3)
.expect("固定受控条件应合法")
.try_to_sql()
.unwrap();
assert!(sql.contains("\"updated_at\" < now()"), "实际 SQL: {sql}");
assert!(sql.contains("\"id\" = $1"), "实际 SQL: {sql}");
}
#[test]
fn test_schema_qualified_table_is_quoted_per_segment() {
let pool = make_sync_test_pool();
let sql = QueryBuilder::new(pool, "audit.users", false)
.field(yang_db::field!("audit.users.id"))
.try_to_sql()
.unwrap();
assert_eq!(
sql,
"SELECT \"audit\".\"users\".\"id\" FROM \"audit\".\"users\""
);
}
}
#[cfg(test)]
#[allow(clippy::unwrap_used, clippy::expect_used)]
mod phase_b_tests {
use super::*;
use crate::{field, table, CompareOp, SelectExpr, SortOrder};
use sqlx::postgres::PgPoolOptions;
fn test_pool() -> &'static PgPool {
use std::sync::OnceLock;
static RT: OnceLock<tokio::runtime::Runtime> = OnceLock::new();
static POOL: OnceLock<PgPool> = OnceLock::new();
let runtime = RT.get_or_init(|| {
tokio::runtime::Builder::new_multi_thread()
.enable_all()
.build()
.expect("无法创建测试用 Tokio 运行时")
});
POOL.get_or_init(|| {
runtime.block_on(async {
PgPoolOptions::new()
.max_connections(1)
.connect_lazy("postgres://postgres:postgres@localhost:5432/test")
.expect("无法解析测试数据库 URL")
})
})
}
fn render(builder: &QueryBuilder<'_>) -> String {
builder.try_to_sql().expect("SQL 应可渲染")
}
#[test]
fn cte_renders_with_recursive_and_keeps_placeholder_order() {
let recent = QueryBuilder::new(test_pool(), "events", false)
.field(field!("user_id"))
.where_and(field!("kind"), CompareOp::Eq, "login")
.expect("受控条件应合法");
let tree = QueryBuilder::new(test_pool(), "nodes", false)
.field(field!("id"))
.where_null(field!("parent_id"));
let builder = QueryBuilder::new(test_pool(), "users", false)
.field(field!("id"))
.with("recent", recent)
.with_recursive("tree", tree)
.where_and(field!("active"), CompareOp::Eq, true)
.expect("受控条件应合法")
.order(field!("id"), SortOrder::Asc)
.limit(5);
assert_eq!(
render(&builder),
"WITH RECURSIVE \"recent\" AS (SELECT \"user_id\" FROM \"events\" WHERE \"kind\" = $1), \
\"tree\" AS (SELECT \"id\" FROM \"nodes\" WHERE \"parent_id\" IS NULL) \
SELECT \"id\" FROM \"users\" WHERE \"active\" = $2 ORDER BY \"id\" ASC LIMIT 5"
);
let mut generator = SqlGenerator::new();
generator.build_select(&builder).expect("CTE 应可渲染");
assert!(matches!(
generator.get_params(),
[SqlValue::String(kind), SqlValue::Bool(true)] if kind == "login"
));
}
#[test]
fn cte_name_is_validated_before_rendering() {
let body = QueryBuilder::new(test_pool(), "users", false).field(field!("id"));
let builder = QueryBuilder::new(test_pool(), "users", false)
.field(field!("id"))
.with("bad; DROP TABLE users", body);
assert!(matches!(
builder.try_to_sql(),
Err(crate::error::DbError::InvalidArgument(_))
));
}
#[test]
fn window_functions_render_partition_order_and_frames() {
let spec = WindowSpec::new()
.partition_by(&[field!("dept")])
.order_by(field!("amount"), SortOrder::Desc);
let running = WindowSpec::new()
.partition_by(&[field!("dept")])
.order_by(field!("created_at"), SortOrder::Asc)
.frame(WindowFrame::rows_between(
FrameBound::UnboundedPreceding,
FrameBound::CurrentRow,
));
let builder = QueryBuilder::new(test_pool(), "sales", false)
.field(field!("dept"))
.window_expr(WindowFunction::row_number(), spec, field!("rn"))
.window_expr(
WindowFunction::sum(field!("amount")),
running,
field!("running"),
);
assert_eq!(
render(&builder),
"SELECT \"dept\", \
ROW_NUMBER() OVER (PARTITION BY \"dept\" ORDER BY \"amount\" DESC) AS \"rn\", \
SUM(\"amount\") OVER (PARTITION BY \"dept\" ORDER BY \"created_at\" ASC \
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"running\" \
FROM \"sales\""
);
}
#[test]
fn every_whitelisted_window_function_renders_expected_call() {
let cases = [
(WindowFunction::rank(), "RANK()"),
(WindowFunction::dense_rank(), "DENSE_RANK()"),
(WindowFunction::avg(field!("amount")), "AVG(\"amount\")"),
(WindowFunction::count(field!("amount")), "COUNT(\"amount\")"),
(WindowFunction::count_all(), "COUNT(*)"),
(WindowFunction::min(field!("amount")), "MIN(\"amount\")"),
(WindowFunction::max(field!("amount")), "MAX(\"amount\")"),
(WindowFunction::lag(field!("amount")), "LAG(\"amount\")"),
(WindowFunction::lead(field!("amount")), "LEAD(\"amount\")"),
];
for (function, expected) in cases {
let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
function,
WindowSpec::new().frame(WindowFrame::range_between(
FrameBound::Preceding(1),
FrameBound::Following(1),
)),
field!("value"),
);
let sql = render(&builder);
assert!(
sql.contains(&format!(
"{} OVER (RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS \"value\"",
expected
)),
"窗口函数片段不符: {sql}"
);
}
}
#[test]
fn invalid_window_frames_fail_closed() {
for frame in [
WindowFrame::rows_between(FrameBound::UnboundedFollowing, FrameBound::CurrentRow),
WindowFrame::rows_between(FrameBound::CurrentRow, FrameBound::UnboundedPreceding),
] {
let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
WindowFunction::row_number(),
WindowSpec::new().frame(frame),
field!("rn"),
);
assert!(matches!(
builder.try_to_sql(),
Err(crate::error::DbError::InvalidArgument(_))
));
}
}
#[test]
fn groups_frame_filter_and_named_window_render() {
let named = WindowSpec::new()
.partition_by(&[field!("dept")])
.order_by(field!("id"), SortOrder::Asc);
let builder = QueryBuilder::new(test_pool(), "sales", false)
.field(field!("dept"))
.window("w", named)
.window_expr(
WindowFunction::sum(field!("amount"))
.filter(Condition::Gt("amount".to_string(), SqlValue::Int(100))),
WindowSpec::new()
.order_by(field!("id"), SortOrder::Asc)
.frame(WindowFrame::groups_between(
FrameBound::Preceding(1),
FrameBound::CurrentRow,
)),
field!("running"),
)
.window_expr(
WindowFunction::row_number(),
WindowSpec::reference("w"),
field!("rn"),
);
assert_eq!(
render(&builder),
"SELECT \"dept\", \
SUM(\"amount\") FILTER (WHERE \"amount\" > $1) \
OVER (ORDER BY \"id\" ASC GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS \"running\", \
ROW_NUMBER() OVER \"w\" AS \"rn\" \
FROM \"sales\" WINDOW \"w\" AS (PARTITION BY \"dept\" ORDER BY \"id\" ASC)"
);
let mut generator = SqlGenerator::new();
generator.build_select(&builder).expect("窗口 SQL 应可渲染");
assert!(matches!(generator.get_params(), [SqlValue::Int(100)]));
}
#[test]
fn groups_frame_requires_order_by() {
let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
WindowFunction::sum(field!("amount")),
WindowSpec::new().frame(WindowFrame::groups_between(
FrameBound::Preceding(1),
FrameBound::CurrentRow,
)),
field!("running"),
);
assert!(matches!(
builder.try_to_sql(),
Err(crate::error::DbError::InvalidArgument(_))
));
}
#[test]
fn filter_on_non_aggregate_window_function_fails_closed() {
let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
WindowFunction::row_number()
.filter(Condition::Gt("amount".to_string(), SqlValue::Int(0))),
WindowSpec::new(),
field!("rn"),
);
assert!(matches!(
builder.try_to_sql(),
Err(crate::error::DbError::InvalidArgument(_))
));
}
#[test]
fn named_window_reference_rejects_extra_spec_parts() {
let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
WindowFunction::row_number(),
WindowSpec::reference("w").partition_by(&[field!("dept")]),
field!("rn"),
);
assert!(matches!(
builder.try_to_sql(),
Err(crate::error::DbError::InvalidArgument(_))
));
let invalid_name = QueryBuilder::new(test_pool(), "sales", false)
.window("bad name", WindowSpec::new())
.window_expr(
WindowFunction::row_number(),
WindowSpec::reference("w"),
field!("rn"),
);
assert!(invalid_name.try_to_sql().is_err());
}
#[test]
fn grouping_sets_rollup_and_cube_render() {
let grouping_sets = QueryBuilder::new(test_pool(), "sales", false)
.field(field!("region"))
.expr(SelectExpr::sum(field!("amount")).alias(field!("total")))
.group(field!("region"))
.grouping_sets(vec![vec![field!("product").clone()], vec![]]);
assert_eq!(
render(&grouping_sets),
"SELECT \"region\", SUM(\"amount\") AS \"total\" FROM \"sales\" \
GROUP BY \"region\", GROUPING SETS ((\"product\"), ())"
);
let rollup = QueryBuilder::new(test_pool(), "sales", false)
.field(field!("region"))
.rollup(vec![field!("region").clone(), field!("product").clone()]);
assert_eq!(
render(&rollup),
"SELECT \"region\" FROM \"sales\" GROUP BY ROLLUP (\"region\", \"product\")"
);
let cube = QueryBuilder::new(test_pool(), "sales", false)
.field(field!("region"))
.cube(vec![field!("region").clone(), field!("product").clone()]);
assert_eq!(
render(&cube),
"SELECT \"region\" FROM \"sales\" GROUP BY CUBE (\"region\", \"product\")"
);
}
#[test]
fn empty_grouping_constructs_fail_closed() {
let empty_sets = QueryBuilder::new(test_pool(), "sales", false)
.field(field!("region"))
.grouping_sets(vec![]);
assert!(matches!(
empty_sets.try_to_sql(),
Err(crate::error::DbError::InvalidArgument(_))
));
let empty_rollup = QueryBuilder::new(test_pool(), "sales", false)
.field(field!("region"))
.rollup(vec![]);
assert!(empty_rollup.try_to_sql().is_err());
let empty_cube = QueryBuilder::new(test_pool(), "sales", false)
.field(field!("region"))
.cube(vec![]);
assert!(empty_cube.try_to_sql().is_err());
}
#[test]
fn distinct_on_requires_matching_leftmost_order_by() {
let builder = QueryBuilder::new(test_pool(), "events", false)
.field(field!("user_id"))
.field(field!("created_at"))
.distinct_on(&[field!("user_id")])
.order(field!("user_id"), SortOrder::Asc)
.order(field!("created_at"), SortOrder::Desc);
assert_eq!(
render(&builder),
"SELECT DISTINCT ON (\"user_id\") \"user_id\", \"created_at\" FROM \"events\" \
ORDER BY \"user_id\" ASC, \"created_at\" DESC"
);
let missing = QueryBuilder::new(test_pool(), "events", false)
.field(field!("user_id"))
.distinct_on(&[field!("user_id")]);
assert!(matches!(
missing.try_to_sql(),
Err(crate::error::DbError::InvalidArgument(_))
));
let mismatched = QueryBuilder::new(test_pool(), "events", false)
.field(field!("user_id"))
.distinct_on(&[field!("user_id")])
.order(field!("created_at"), SortOrder::Desc);
assert!(mismatched.try_to_sql().is_err());
let both = QueryBuilder::new(test_pool(), "events", false)
.field(field!("user_id"))
.distinct()
.distinct_on(&[field!("user_id")])
.order(field!("user_id"), SortOrder::Asc);
assert!(both.try_to_sql().is_err());
}
#[test]
fn join_extensions_render_expected_keywords() {
let builder = QueryBuilder::new(test_pool(), "a", false)
.field(field!("a.id"))
.full_join(table!("b"), field!("a.id"), field!("b.a_id"))
.cross_join(table!("c"))
.natural_join(table!("d"))
.join_using(table!("e"), &[field!("id")]);
assert_eq!(
render(&builder),
"SELECT \"a\".\"id\" FROM \"a\" \
FULL JOIN \"b\" ON \"a\".\"id\" = \"b\".\"a_id\" \
CROSS JOIN \"c\" NATURAL JOIN \"d\" INNER JOIN \"e\" USING (\"id\")"
);
}
#[test]
fn lateral_left_join_embeds_subquery_and_alias() {
let lateral = QueryBuilder::new(test_pool(), "orders", false)
.field(field!("id"))
.field(field!("amount"))
.where_and(field!("orders.status"), CompareOp::Eq, "paid")
.expect("受控条件应合法")
.where_column(field!("orders.user_id"), CompareOp::Eq, field!("users.id"))
.expect("受控列比较应合法")
.order(field!("amount"), SortOrder::Desc)
.limit(3);
let builder = QueryBuilder::new(test_pool(), "users", false)
.field(field!("users.id"))
.left_join_lateral(
lateral,
field!("o"),
field!("users.id"),
field!("o.user_id"),
);
assert_eq!(
render(&builder),
"SELECT \"users\".\"id\" FROM \"users\" \
LEFT JOIN LATERAL (SELECT \"id\", \"amount\" FROM \"orders\" \
WHERE (\"orders\".\"status\" = $1 AND \"orders\".\"user_id\" = \"users\".\"id\") \
ORDER BY \"amount\" DESC LIMIT 3) AS \"o\" \
ON \"users\".\"id\" = \"o\".\"user_id\""
);
}
#[test]
fn set_operations_render_and_validate_column_counts() {
let left = || {
QueryBuilder::new(test_pool(), "a", false)
.field(field!("id"))
.field(field!("name"))
};
let right = |table_name| {
QueryBuilder::new(test_pool(), table_name, false)
.field(field!("id"))
.field(field!("name"))
};
assert_eq!(
render(&left().intersect(right("b")).expect("列数一致")),
"SELECT \"id\", \"name\" FROM \"a\" INTERSECT (SELECT \"id\", \"name\" FROM \"b\")"
);
assert_eq!(
render(&left().intersect_all(right("b")).expect("列数一致")),
"SELECT \"id\", \"name\" FROM \"a\" INTERSECT ALL (SELECT \"id\", \"name\" FROM \"b\")"
);
assert_eq!(
render(&left().except(right("b")).expect("列数一致")),
"SELECT \"id\", \"name\" FROM \"a\" EXCEPT (SELECT \"id\", \"name\" FROM \"b\")"
);
assert_eq!(
render(&left().except_all(right("b")).expect("列数一致")),
"SELECT \"id\", \"name\" FROM \"a\" EXCEPT ALL (SELECT \"id\", \"name\" FROM \"b\")"
);
let mixed = left()
.union(right("b"))
.expect("列数一致")
.intersect(right("c"))
.expect("列数一致");
assert_eq!(
render(&mixed),
"(SELECT \"id\", \"name\" FROM \"a\" UNION (SELECT \"id\", \"name\" FROM \"b\")) \
INTERSECT (SELECT \"id\", \"name\" FROM \"c\")"
);
let one_column = QueryBuilder::new(test_pool(), "c", false).field(field!("id"));
assert!(left().intersect(one_column).is_err());
}
#[test]
fn having_expr_renders_aggregate_comparison_with_bound_value() {
let builder = QueryBuilder::new(test_pool(), "orders", false)
.field(field!("user_id"))
.expr(SelectExpr::count_all())
.group(field!("user_id"))
.having_expr(SelectExpr::count_all(), CompareOp::Gt, 1)
.expect("受控 HAVING 表达式应合法");
assert_eq!(
render(&builder),
"SELECT \"user_id\", COUNT(*) FROM \"orders\" GROUP BY \"user_id\" \
HAVING COUNT(*) > $1"
);
let without_group = QueryBuilder::new(test_pool(), "orders", false)
.expr(SelectExpr::count_all())
.having_expr(SelectExpr::count_all(), CompareOp::Gt, 1)
.expect("无 GROUP BY 的聚合 HAVING 应合法");
assert_eq!(
render(&without_group),
"SELECT COUNT(*) FROM \"orders\" HAVING COUNT(*) > $1"
);
let field_having_without_group = QueryBuilder::new(test_pool(), "orders", false)
.field(field!("user_id"))
.having_cond(field!("user_id"), CompareOp::Gt, 1)
.expect("受控条件应合法");
assert!(matches!(
field_having_without_group.try_to_sql(),
Err(crate::error::DbError::MissingGroupByClause)
));
let like = QueryBuilder::new(test_pool(), "orders", false)
.field(field!("user_id"))
.group(field!("user_id"))
.having_expr(SelectExpr::count_all(), CompareOp::Like, "%x%");
assert!(matches!(
like,
Err(crate::error::DbError::UnsupportedOperator(_))
));
}
#[test]
fn upsert_batch_builds_multi_row_values_and_conflict_clause() {
let data = [
serde_json::json!({"id": 1, "name": "a"}),
serde_json::json!({"id": 2, "name": "b"}),
];
let mut generator = SqlGenerator::new();
generator
.build_upsert_batch(
"users",
&data,
&HashMap::new(),
&["id".to_string()],
None,
false,
None,
&[],
)
.expect("批量 UPSERT 应可渲染");
assert_eq!(
generator.get_sql(),
"INSERT INTO \"users\" (\"id\", \"name\") VALUES ($1, $2), ($3, $4) \
ON CONFLICT (\"id\") DO UPDATE SET \"name\" = EXCLUDED.\"name\""
);
assert_eq!(generator.get_params().len(), 4);
let mut generator = SqlGenerator::new();
assert!(matches!(
generator.build_upsert_batch(
"users",
&data,
&HashMap::new(),
&[],
None,
false,
None,
&[]
),
Err(crate::error::DbError::InvalidArgument(_))
));
let mut generator = SqlGenerator::new();
generator
.build_upsert_batch("users", &data, &HashMap::new(), &[], None, true, None, &[])
.expect("DO NOTHING 批量 UPSERT 应可渲染");
assert!(generator.get_sql().ends_with("ON CONFLICT DO NOTHING"));
let mut generator = SqlGenerator::new();
assert!(matches!(
generator.build_upsert_batch(
"users",
&data,
&HashMap::new(),
&["id".to_string()],
None,
false,
Some(&["missing".to_string()]),
&[]
),
Err(crate::error::DbError::InvalidArgument(_))
));
}
#[tokio::test]
async fn upsert_batch_rejects_invalid_input_before_io() {
let pool = PgPoolOptions::new()
.max_connections(1)
.connect_lazy("postgres://postgres:postgres@localhost:5432/test")
.expect("无法解析测试数据库 URL");
let error = QueryBuilder::new(&pool, "users", false)
.upsert_batch(&[] as &[serde_json::Value], &[field!("id")])
.await
.expect_err("空数据应在触库前失败");
assert!(matches!(
error,
crate::error::DbError::SerializationError(_)
));
let error = QueryBuilder::new(&pool, "users", false)
.upsert_batch(&[serde_json::json!({"id": 1})], &[])
.await
.expect_err("缺少冲突列应在触库前失败");
assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
let error = QueryBuilder::new(&pool, "users", false)
.upsert_batch_with_size(&[serde_json::json!({"id": 1})], &[field!("id")], 0)
.await
.expect_err("batch_size 为 0 应在触库前失败");
assert!(matches!(
error,
crate::error::DbError::SerializationError(_)
));
}
#[test]
fn insert_from_select_renders_target_columns_and_source_query() {
let source = QueryBuilder::new(test_pool(), "users", false)
.field(field!("id"))
.field(field!("name"))
.where_and(field!("active"), CompareOp::Eq, true)
.expect("受控条件应合法");
let mut generator = SqlGenerator::new();
generator
.build_insert_from_select("archive", &[field!("id"), field!("name")], &source)
.expect("INSERT ... SELECT 应可渲染");
assert_eq!(
generator.get_sql(),
"INSERT INTO \"archive\" (\"id\", \"name\") \
SELECT \"id\", \"name\" FROM \"users\" WHERE \"active\" = $1"
);
}
#[test]
fn update_from_renders_join_predicate_in_where() {
let mut generator = SqlGenerator::new();
generator
.build_update_from(
"users",
&serde_json::json!({"name": "x"}),
&HashMap::new(),
&[],
&[Condition::Eq(
"profiles.active".to_string(),
SqlValue::Bool(true),
)],
&crate::TableRef::new("profiles").expect("合法表名"),
field!("users.id"),
field!("profiles.user_id"),
)
.expect("UPDATE ... FROM 应可渲染");
assert_eq!(
generator.get_sql(),
"UPDATE \"users\" SET \"name\" = $1 FROM \"profiles\" \
WHERE (\"profiles\".\"active\" = $2 AND \"users\".\"id\" = \"profiles\".\"user_id\")"
);
}
#[test]
fn delete_using_renders_join_predicate_in_where() {
let mut generator = SqlGenerator::new();
generator
.build_delete_using(
"users",
&[Condition::Eq(
"users.active".to_string(),
SqlValue::Bool(false),
)],
&crate::TableRef::new("profiles").expect("合法表名"),
field!("users.id"),
field!("profiles.user_id"),
)
.expect("DELETE ... USING 应可渲染");
assert_eq!(
generator.get_sql(),
"DELETE FROM \"users\" USING \"profiles\" \
WHERE (\"users\".\"active\" = $1 AND \"users\".\"id\" = \"profiles\".\"user_id\")"
);
}
#[test]
fn merge_renders_matched_update_and_not_matched_insert() {
let mut generator = SqlGenerator::new();
generator
.build_merge(
"users",
&crate::TableRef::new("incoming").expect("合法表名"),
field!("id"),
field!("id"),
&[field!("name"), field!("email")],
)
.expect("MERGE 应可渲染");
assert_eq!(
generator.get_sql(),
"MERGE INTO \"users\" USING \"incoming\" ON \"users\".\"id\" = \"incoming\".\"id\" \
WHEN MATCHED THEN UPDATE SET \"name\" = \"incoming\".\"name\", \
\"email\" = \"incoming\".\"email\" \
WHEN NOT MATCHED THEN INSERT (\"name\", \"email\") \
VALUES (\"incoming\".\"name\", \"incoming\".\"email\")"
);
}
#[test]
fn merge_renders_matched_condition_and_by_source_delete() {
let mut generator = SqlGenerator::new();
generator
.build_merge_with_options(
"users",
&crate::TableRef::new("incoming").expect("合法表名"),
field!("id"),
field!("id"),
&[field!("name")],
&MergeOptions::default()
.matched_condition(Condition::Eq(
"users.name".to_string(),
SqlValue::String("x".to_string()),
))
.not_matched_by_source(MergeNotMatchedBySource::Delete),
)
.expect("带条件的 MERGE 应可渲染");
assert_eq!(
generator.get_sql(),
"MERGE INTO \"users\" USING \"incoming\" ON \"users\".\"id\" = \"incoming\".\"id\" \
WHEN MATCHED AND \"users\".\"name\" = $1 THEN UPDATE SET \"name\" = \"incoming\".\"name\" \
WHEN NOT MATCHED THEN INSERT (\"name\") VALUES (\"incoming\".\"name\") \
WHEN NOT MATCHED BY SOURCE THEN DELETE"
);
assert!(matches!(generator.get_params(), [SqlValue::String(x)] if x == "x"));
}
#[test]
fn merge_renders_by_source_update_with_controlled_expr() {
let mut generator = SqlGenerator::new();
generator
.build_merge_with_options(
"users",
&crate::TableRef::new("incoming").expect("合法表名"),
field!("id"),
field!("id"),
&[field!("name")],
&MergeOptions::default().not_matched_by_source(MergeNotMatchedBySource::Update(
vec![(field!("updated_at").clone(), crate::SqlExpr::now())],
)),
)
.expect("BY SOURCE UPDATE 应可渲染");
assert!(
generator
.get_sql()
.ends_with("WHEN NOT MATCHED BY SOURCE THEN UPDATE SET \"updated_at\" = now()"),
"{}",
generator.get_sql()
);
}
#[test]
fn merge_by_source_update_rejects_empty_assignments() {
let mut generator = SqlGenerator::new();
let error = generator
.build_merge_with_options(
"users",
&crate::TableRef::new("incoming").expect("合法表名"),
field!("id"),
field!("id"),
&[field!("name")],
&MergeOptions::default()
.not_matched_by_source(MergeNotMatchedBySource::Update(vec![])),
)
.expect_err("空赋值列表必须报错");
assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
}
#[test]
fn array_field_type_converts_json_array_recursively() {
let mut field_types = HashMap::new();
field_types.insert(
"tags".to_string(),
FieldType::Array(Box::new(FieldType::Text)),
);
let mut generator = SqlGenerator::new();
generator
.build_insert(
"docs",
&serde_json::json!({"tags": ["a", "b"]}),
&field_types,
&[],
)
.expect("数组字段插入应可渲染");
assert_eq!(
generator.get_sql(),
"INSERT INTO \"docs\" (\"tags\") VALUES ($1)"
);
assert!(matches!(
generator.get_params(),
[SqlValue::Array(values)]
if matches!(values.as_slice(), [SqlValue::String(a), SqlValue::String(b)] if a == "a" && b == "b")
));
let mut generator = SqlGenerator::new();
assert!(matches!(
generator.build_insert(
"docs",
&serde_json::json!({"tags": "not-an-array"}),
&field_types,
&[]
),
Err(crate::error::DbError::TypeConversionError(_))
));
let mut field_types = HashMap::new();
field_types.insert(
"ips".to_string(),
FieldType::Array(Box::new(FieldType::Inet)),
);
let mut generator = SqlGenerator::new();
generator
.build_insert(
"docs",
&serde_json::json!({"ips": ["10.0.0.1"]}),
&field_types,
&[],
)
.expect("INET[] 插入应可渲染");
assert_eq!(
generator.get_sql(),
"INSERT INTO \"docs\" (\"ips\") VALUES ($1::inet[])"
);
assert!(matches!(
generator.get_params(),
[SqlValue::Array(values)] if matches!(values.as_slice(), [SqlValue::Inet(ip)] if ip == "10.0.0.1")
));
}
#[test]
fn empty_and_all_null_boolean_arrays_bind_as_bool_array() {
let mut field_types = HashMap::new();
field_types.insert(
"flags".to_string(),
FieldType::Array(Box::new(FieldType::Bool)),
);
for value in [serde_json::json!([]), serde_json::json!([null, null])] {
let mut generator = SqlGenerator::new();
generator
.build_insert(
"docs",
&serde_json::json!({"flags": value}),
&field_types,
&[],
)
.expect("布尔数组插入应可渲染");
match generator.get_params() {
[SqlValue::TypedArray {
element_type,
values,
}] => {
assert_eq!(
*element_type, "bool",
"空/全 NULL 布尔数组必须按 bool[] 绑定"
);
assert!(values.iter().all(|value| matches!(value, SqlValue::Null)));
}
other => panic!("期望 TypedArray(bool),实得 {other:?}"),
}
}
}
#[test]
fn network_field_types_accept_text_literals() {
let mut field_types = HashMap::new();
field_types.insert("ip".to_string(), FieldType::Inet);
field_types.insert("mac".to_string(), FieldType::MacAddr);
let mut generator = SqlGenerator::new();
generator
.build_insert(
"hosts",
&serde_json::json!({"ip": "10.0.0.1", "mac": "08:00:2b:01:02:03"}),
&field_types,
&[],
)
.expect("网络类型插入应可渲染");
assert_eq!(
generator.get_sql(),
"INSERT INTO \"hosts\" (\"ip\", \"mac\") VALUES ($1::inet, $2::macaddr)"
);
}
#[test]
fn json_predicates_and_array_quantifiers_render() {
let builder = QueryBuilder::new(test_pool(), "docs", false)
.where_json_contains(
field!("data"),
SqlValue::Json(serde_json::json!({"role": "admin"})),
)
.where_json_has_key(field!("data"), "active")
.where_any(field!("id"), vec![1_i64, 2])
.where_all(field!("score"), vec![1.5_f64]);
assert_eq!(
render(&builder),
"SELECT * FROM \"docs\" WHERE ((\"data\")::jsonb @> $1 AND (\"data\")::jsonb ? $2 \
AND \"id\" = ANY($3) AND \"score\" = ALL($4))"
);
let mut generator = SqlGenerator::new();
generator.build_select(&builder).expect("JSON 条件应可渲染");
assert!(matches!(
generator.get_params(),
[SqlValue::Json(_), SqlValue::String(key), SqlValue::Array(ids), SqlValue::Array(scores)]
if key == "active" && ids.len() == 2 && scores.len() == 1
));
}
#[test]
fn wave2_compare_ops_render_postgres_operators() {
let cases: &[(crate::CompareOp, &str)] = &[
(crate::CompareOp::ILike, "ILIKE"),
(crate::CompareOp::NotILike, "NOT ILIKE"),
(crate::CompareOp::RegexMatch, "~"),
(crate::CompareOp::RegexIMatch, "~*"),
(crate::CompareOp::NotRegexMatch, "!~"),
(crate::CompareOp::NotRegexIMatch, "!~*"),
];
for (op, operator) in cases {
let builder = QueryBuilder::new(test_pool(), "users", false)
.where_and(field!("name"), *op, "%a%")
.expect("模式匹配条件应合法");
assert_eq!(
render(&builder),
format!("SELECT * FROM \"users\" WHERE \"name\" {operator} $1")
);
}
let builder = QueryBuilder::new(test_pool(), "users", false)
.where_and(field!("name"), crate::CompareOp::IsDistinctFrom, "a")
.expect("NULL-safe 比较应合法");
assert_eq!(
render(&builder),
"SELECT * FROM \"users\" WHERE \"name\" IS DISTINCT FROM $1"
);
let builder = QueryBuilder::new(test_pool(), "users", false)
.where_and(
field!("name"),
crate::CompareOp::IsNotDistinctFrom,
SqlValue::Null,
)
.expect("NULL-safe NULL 比较应合法");
assert_eq!(
render(&builder),
"SELECT * FROM \"users\" WHERE \"name\" IS NOT DISTINCT FROM NULL"
);
let result = QueryBuilder::new(test_pool(), "users", false).where_and(
field!("name"),
crate::CompareOp::ILike,
42,
);
assert!(matches!(
result,
Err(crate::error::DbError::UnsupportedOperator(_))
));
}
#[test]
fn wave2_returning_columns_aliases_and_validation() {
let builder = QueryBuilder::new(test_pool(), "users", false)
.returning_columns(&[field!("id"), field!("email")]);
assert_eq!(
builder.returning_clause().unwrap(),
" RETURNING \"id\", \"email\""
);
let builder = QueryBuilder::new(test_pool(), "users", false)
.returning_column_as(field!("id"), field!("user_id"))
.returning_column_as(field!("email"), field!("mail"));
assert_eq!(
builder.returning_clause().unwrap(),
" RETURNING \"id\" AS \"user_id\", \"email\" AS \"mail\""
);
assert_eq!(
QueryBuilder::new(test_pool(), "users", false)
.returning_all()
.returning_clause()
.unwrap(),
" RETURNING *"
);
let mut invalid = QueryBuilder::new(test_pool(), "users", false);
invalid.returning = ReturningTarget::Columns(vec![ReturningColumn {
column: "id".to_string(),
alias: Some("bad alias".to_string()),
}]);
assert!(matches!(
invalid.returning_clause(),
Err(crate::error::DbError::InvalidArgument(_))
));
assert!(matches!(
QueryBuilder::new(test_pool(), "users", false)
.returning_columns(&[])
.returning_clause(),
Err(crate::error::DbError::InvalidArgument(_))
));
}
#[test]
fn wave2_conflict_predicate_and_unspecified_target() {
let data = serde_json::json!({"email": "a@x", "active": true});
let conflict = ConflictTarget::Columns(vec!["email".to_string()]);
let mut generator = SqlGenerator::new();
generator
.build_upsert(
"users",
&data,
&HashMap::new(),
&conflict,
Some("active"),
false,
None,
&[],
)
.expect("带谓词的 UPSERT 应可渲染");
assert_eq!(
generator.get_sql(),
"INSERT INTO \"users\" (\"active\", \"email\") VALUES ($1, $2) \
ON CONFLICT (\"email\") WHERE (active) DO UPDATE SET \"active\" = EXCLUDED.\"active\""
);
for payload in ["active; DROP TABLE users", "active -- x", "active /* x */"] {
let mut generator = SqlGenerator::new();
let error = generator
.build_upsert(
"users",
&data,
&HashMap::new(),
&conflict,
Some(payload),
false,
None,
&[],
)
.expect_err("注入型谓词必须被拒绝");
assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
}
let mut generator = SqlGenerator::new();
let error = generator
.build_upsert(
"users",
&data,
&HashMap::new(),
&ConflictTarget::Unspecified,
None,
false,
None,
&[],
)
.expect_err("无冲突目标的 DO UPDATE 必须被拒绝");
assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
let mut generator = SqlGenerator::new();
generator
.build_upsert(
"users",
&data,
&HashMap::new(),
&ConflictTarget::Unspecified,
None,
true,
None,
&[],
)
.expect("无冲突目标的 DO NOTHING 应可渲染");
assert!(generator.get_sql().ends_with("ON CONFLICT DO NOTHING"));
}
#[test]
fn wave2_insert_batch_shrinks_by_column_count() {
assert_eq!(derive_insert_batch_size(0, 500), 500);
assert_eq!(derive_insert_batch_size(1, 500), 500);
assert_eq!(derive_insert_batch_size(1_000, 500), 65);
assert_eq!(derive_insert_batch_size(100_000, 500), 1);
}
#[test]
fn wave2_merge_version_gate() {
assert!(!merge_version_supported(149_999));
assert!(!merge_version_supported(140_000));
assert!(merge_version_supported(150_000));
assert!(merge_version_supported(160_015));
}
#[test]
fn wave2_row_lock_variants_and_combinations() {
let builder = QueryBuilder::new(test_pool(), "users", false);
assert_eq!(
builder
.try_to_sql_with_lock(crate::RowLock::NoKeyUpdate, &[field!("users")], false, true,)
.unwrap(),
"SELECT * FROM \"users\" FOR NO KEY UPDATE OF \"users\" SKIP LOCKED"
);
assert_eq!(
builder
.try_to_sql_with_lock(crate::RowLock::KeyShare, &[], true, false)
.unwrap(),
"SELECT * FROM \"users\" FOR KEY SHARE NOWAIT"
);
assert_eq!(
builder
.try_to_sql_with_lock(crate::RowLock::ForUpdateSkipLocked, &[], false, false)
.unwrap(),
"SELECT * FROM \"users\" FOR UPDATE SKIP LOCKED"
);
assert!(matches!(
builder.try_to_sql_with_lock(crate::RowLock::ForUpdate, &[], true, true),
Err(crate::error::DbError::InvalidArgument(_))
));
assert!(matches!(
builder.try_to_sql_with_lock(crate::RowLock::ForUpdateSkipLocked, &[], true, false),
Err(crate::error::DbError::InvalidArgument(_))
));
}
#[test]
fn insert_pk_decode_rejects_negative_but_keeps_zero_and_positive() {
assert_eq!(integer_pk_from_i64(0).expect("零值主键应可解码").0, 0);
assert_eq!(integer_pk_from_i64(42).expect("正数主键应可解码").0, 42);
let error = match integer_pk_from_i64(-5) {
Err(error) => error,
Ok(_) => panic!("负值主键必须被拒绝"),
};
assert!(
error.to_string().contains(NEGATIVE_PK_DECODE_PREFIX),
"负值错误应携带专属前缀: {error}"
);
assert!(error.to_string().contains("-5"), "错误应保留负值: {error}");
}
#[test]
fn insert_maps_negative_pk_to_distinct_invalid_argument() {
let negative_detail = format!("列 0 解码失败: {NEGATIVE_PK_DECODE_PREFIX}: -5");
match map_insert_pk_decode_error(negative_detail) {
crate::error::DbError::InvalidArgument(message) => {
assert!(message.contains("负数"), "文案应指出负数主键: {message}");
assert!(message.contains("-5"), "文案应保留负值: {message}");
assert!(
!message.contains("仅支持整型主键"),
"负值不得被误报为非整型主键: {message}"
);
}
other => panic!("期望 InvalidArgument,实得 {other:?}"),
}
let type_detail = "列 0 解码失败: UUID 不是整型".to_string();
match map_insert_pk_decode_error(type_detail) {
crate::error::DbError::InvalidArgument(message) => {
assert!(message.contains("仅支持整型主键"));
assert!(message.contains("insert_returning"));
}
other => panic!("期望 InvalidArgument,实得 {other:?}"),
}
}
}