use crate::error::DbError;
use crate::postgres::condition::{Condition, SqlValue};
use crate::postgres::field::FieldType;
use crate::postgres::query_builder::QueryBuilder;
use sqlx::Transaction as SqlxTransaction;
use std::collections::HashMap;
pub struct Transaction {
tx: Option<SqlxTransaction<'static, sqlx::Postgres>>,
enable_logging: bool,
}
impl Transaction {
pub(crate) fn new(tx: SqlxTransaction<'static, sqlx::Postgres>, enable_logging: bool) -> Self {
Self {
tx: Some(tx),
enable_logging,
}
}
pub async fn commit(mut self) -> Result<(), DbError> {
if self.enable_logging {
log::debug!("提交事务");
}
if let Some(tx) = self.tx.take() {
tx.commit().await?;
}
Ok(())
}
pub async fn rollback(mut self) -> Result<(), DbError> {
if self.enable_logging {
log::debug!("回滚事务");
}
if let Some(tx) = self.tx.take() {
tx.rollback().await?;
}
Ok(())
}
#[deprecated(
since = "0.1.0",
note = "使用 execute_with_params / query_with_params 等参数化方法替代"
)]
pub async fn execute(&mut self, sql: &str) -> Result<u64, DbError> {
if self.enable_logging {
log::debug!("事务中执行: {}", sql);
}
if let Some(tx) = &mut self.tx {
let result = sqlx::query(sql).execute(&mut **tx).await?;
Ok(result.rows_affected())
} else {
Err(DbError::TransactionError("事务已提交或回滚".to_string()))
}
}
pub async fn execute_with_params(
&mut self,
sql: &str,
params: Vec<serde_json::Value>,
) -> Result<u64, DbError> {
if self.enable_logging {
log::debug!("事务中执行参数化语句: {}, 参数数量: {}", sql, params.len());
}
if let Some(tx) = &mut self.tx {
let mut query = sqlx::query(sql);
for param in ¶ms {
query = bind_json_param_tx(query, param);
}
let result = query.execute(&mut **tx).await?;
Ok(result.rows_affected())
} else {
Err(DbError::TransactionError("事务已提交或回滚".to_string()))
}
}
pub async fn query_with_params<T>(
&mut self,
sql: &str,
params: Vec<serde_json::Value>,
) -> Result<Vec<T>, DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
if self.enable_logging {
log::debug!("事务中执行参数化查询: {}, 参数数量: {}", sql, params.len());
}
if let Some(tx) = &mut self.tx {
let mut query = sqlx::query_as::<_, T>(sql);
for param in ¶ms {
query = bind_json_param_as_tx(query, param);
}
let rows = query.fetch_all(&mut **tx).await?;
Ok(rows)
} else {
Err(DbError::TransactionError("事务已提交或回滚".to_string()))
}
}
pub async fn select<T>(&mut self, query: QueryBuilder<'_>) -> Result<Vec<T>, DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
self.select_with_optional_lock(query, None).await
}
pub async fn select_for_update<T>(&mut self, query: QueryBuilder<'_>) -> Result<Vec<T>, DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
self.select_locked(query, crate::RowLock::ForUpdate).await
}
pub async fn select_for_share<T>(&mut self, query: QueryBuilder<'_>) -> Result<Vec<T>, DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
self.select_locked(query, crate::RowLock::ForShare).await
}
pub async fn select_locked<T>(
&mut self,
query: QueryBuilder<'_>,
lock: crate::RowLock,
) -> Result<Vec<T>, DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
self.select_with_optional_lock(query, Some(lock)).await
}
async fn select_with_optional_lock<T>(
&mut self,
query: QueryBuilder<'_>,
lock: Option<crate::RowLock>,
) -> Result<Vec<T>, DbError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
let (sql, params) = query.render_for_transaction(lock)?;
if self.enable_logging {
log::debug!("事务中执行 SELECT: {}, 参数: {:?}", sql, params);
}
let tx = self
.tx
.as_mut()
.ok_or_else(|| DbError::TransactionError("事务已提交或回滚".to_string()))?;
let mut sql_query = sqlx::query_as::<_, T>(&sql);
for param in ¶ms {
sql_query = crate::postgres::query_builder::bind_param(sql_query, param);
}
Ok(sql_query.fetch_all(&mut **tx).await?)
}
pub fn table(&mut self, table_name: &str) -> TransactionQueryBuilder<'_> {
TransactionQueryBuilder::new(self, table_name)
}
#[doc(hidden)]
pub fn executor(&mut self) -> Option<&mut sqlx::PgConnection> {
self.tx.as_deref_mut()
}
}
impl Drop for Transaction {
fn drop(&mut self) {
if self.tx.is_some() {
log::warn!(
"PG 事务被丢弃而未提交/回滚——底层将自动回滚。请显式调用 commit() 或 rollback()。"
);
}
}
}
pub struct TransactionQueryBuilder<'a> {
tx: &'a mut Transaction,
table: String,
conditions: Vec<Condition>,
field_types: HashMap<String, FieldType>,
error: Option<DbError>,
returning: String,
}
impl<'a> TransactionQueryBuilder<'a> {
fn new(tx: &'a mut Transaction, table_name: &str) -> Self {
Self {
tx,
table: table_name.to_string(),
conditions: Vec::new(),
field_types: HashMap::new(),
error: None,
returning: "id".to_string(),
}
}
pub fn json(mut self, field: &str) -> Self {
self.field_types.insert(field.to_string(), FieldType::Json);
self
}
pub fn returning(mut self, column: &str) -> Self {
self.returning = column.to_string();
self
}
pub fn datetime(mut self, field: &str) -> Self {
self.field_types
.insert(field.to_string(), FieldType::DateTime);
self
}
pub fn timestamp(mut self, field: &str) -> Self {
self.field_types
.insert(field.to_string(), FieldType::Timestamp);
self
}
pub fn decimal(mut self, field: &str) -> Self {
self.field_types
.insert(field.to_string(), FieldType::Decimal);
self
}
pub fn blob(mut self, field: &str) -> Self {
self.field_types.insert(field.to_string(), FieldType::Blob);
self
}
pub fn text(mut self, field: &str) -> Self {
self.field_types.insert(field.to_string(), FieldType::Text);
self
}
pub fn where_and<V>(mut self, field: &str, op: &str, value: V) -> Self
where
V: Into<SqlValue>,
{
let sql_value = value.into();
let condition = match op {
"=" => Condition::Eq(field.to_string(), sql_value),
"!=" => Condition::Ne(field.to_string(), sql_value),
">" => Condition::Gt(field.to_string(), sql_value),
"<" => Condition::Lt(field.to_string(), sql_value),
">=" => Condition::Gte(field.to_string(), sql_value),
"<=" => Condition::Lte(field.to_string(), sql_value),
"like" | "LIKE" => {
if let SqlValue::String(s) = sql_value {
Condition::Like(field.to_string(), s)
} else {
Condition::Like(field.to_string(), format!("{:?}", sql_value))
}
}
_ => {
if self.error.is_none() {
self.error = Some(DbError::UnsupportedOperator(op.to_string()));
}
return self;
}
};
self.conditions.push(condition);
self
}
pub async fn insert<T>(self, data: &T) -> Result<u64, DbError>
where
T: serde::Serialize,
{
if let Some(err) = self.error {
return Err(err);
}
if self.tx.enable_logging {
log::debug!("事务中执行 insert() 操作,表: {}", self.table);
}
let json_data = serde_json::to_value(data)
.map_err(|e| DbError::SerializationError(format!("数据序列化失败: {}", e)))?;
let mut generator = crate::postgres::query_builder::SqlGenerator::new();
generator.build_insert(&self.table, &json_data, &self.field_types)?;
let quoted_ret = crate::postgres::identifier::quote_identifier(&self.returning)?;
let sql = format!(
"{} RETURNING CAST({} AS BIGINT)",
generator.get_sql(),
quoted_ret
);
let params = generator.get_params();
if self.tx.enable_logging {
log::debug!("事务中执行 insert() SQL: {}", sql);
log::debug!("参数: {:?}", params);
}
let mut query = sqlx::query_scalar::<_, i64>(&sql);
for param in params {
query = bind_scalar_param(query, param);
}
if let Some(tx) = &mut self.tx.tx {
let last_insert_id: i64 = query.fetch_one(&mut **tx).await?;
if self.tx.enable_logging {
log::debug!("事务中 insert() 成功,插入 ID: {}", last_insert_id);
}
u64::try_from(last_insert_id)
.map_err(|_| DbError::InvalidArgument("返回主键为负".into()))
} else {
Err(DbError::TransactionError("事务已提交或回滚".to_string()))
}
}
pub async fn update<T>(self, data: &T) -> Result<u64, DbError>
where
T: serde::Serialize,
{
if let Some(err) = self.error {
return Err(err);
}
if self.tx.enable_logging {
log::debug!("事务中执行 update() 操作,表: {}", self.table);
}
if self.conditions.is_empty() {
log::warn!("事务中 update() 操作缺少 WHERE 条件,禁止全表更新");
return Err(DbError::MissingWhereClause);
}
let json_data = serde_json::to_value(data)
.map_err(|e| DbError::SerializationError(format!("数据序列化失败: {}", e)))?;
let mut generator = crate::postgres::query_builder::SqlGenerator::new();
generator.build_update(&self.table, &json_data, &self.field_types, &self.conditions)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.tx.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);
}
if let Some(tx) = &mut self.tx.tx {
let result = query.execute(&mut **tx).await?;
let rows_affected = result.rows_affected();
if self.tx.enable_logging {
log::debug!("事务中 update() 成功,影响 {} 行", rows_affected);
}
Ok(rows_affected)
} else {
Err(DbError::TransactionError("事务已提交或回滚".to_string()))
}
}
pub async fn increment(self, field: &str, amount: i64) -> Result<u64, DbError> {
self.execute_arithmetic_update(
field,
amount,
crate::postgres::query_builder::ArithmeticOperator::Add,
)
.await
}
pub async fn decrement(self, field: &str, amount: i64) -> Result<u64, DbError> {
self.execute_arithmetic_update(
field,
amount,
crate::postgres::query_builder::ArithmeticOperator::Subtract,
)
.await
}
async fn execute_arithmetic_update(
self,
field: &str,
amount: i64,
operator: crate::postgres::query_builder::ArithmeticOperator,
) -> Result<u64, DbError> {
if let Some(error) = self.error {
return Err(error);
}
let mut generator = crate::postgres::query_builder::SqlGenerator::new();
generator.build_arithmetic_update(
&self.table,
field,
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 tx = self
.tx
.tx
.as_mut()
.ok_or_else(|| DbError::TransactionError("事务已提交或回滚".to_string()))?;
Ok(query.execute(&mut **tx).await?.rows_affected())
}
pub async fn delete(self) -> Result<u64, DbError> {
if let Some(err) = self.error {
return Err(err);
}
if self.tx.enable_logging {
log::debug!("事务中执行 delete() 操作,表: {}", self.table);
}
if self.conditions.is_empty() {
log::warn!("事务中 delete() 操作缺少 WHERE 条件,禁止全表删除");
return Err(DbError::MissingWhereClause);
}
let mut generator = crate::postgres::query_builder::SqlGenerator::new();
generator.build_delete(&self.table, &self.conditions)?;
let sql = generator.get_sql();
let params = generator.get_params();
if self.tx.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);
}
if let Some(tx) = &mut self.tx.tx {
let result = query.execute(&mut **tx).await?;
let rows_affected = result.rows_affected();
if self.tx.enable_logging {
log::debug!("事务中 delete() 成功,影响 {} 行", rows_affected);
}
Ok(rows_affected)
} else {
Err(DbError::TransactionError("事务已提交或回滚".to_string()))
}
}
}
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> {
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),
}
}
fn bind_scalar_param<'q>(
query: sqlx::query::QueryScalar<'q, sqlx::Postgres, i64, sqlx::postgres::PgArguments>,
param: &SqlValue,
) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, i64, sqlx::postgres::PgArguments> {
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),
}
}
fn bind_json_param_tx<'q>(
query: sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>,
param: &serde_json::Value,
) -> sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments> {
match param {
serde_json::Value::String(s) => query.bind(s.clone()),
serde_json::Value::Number(n) => {
if let Some(i) = n.as_i64() {
query.bind(i)
} else if let Some(f) = n.as_f64() {
query.bind(f)
} else {
query.bind(Option::<String>::None)
}
}
serde_json::Value::Bool(b) => query.bind(*b),
serde_json::Value::Null => query.bind(Option::<String>::None),
other => query.bind(other.clone()),
}
}
fn bind_json_param_as_tx<'q, T>(
query: sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
param: &serde_json::Value,
) -> sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
where
T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow>,
{
match param {
serde_json::Value::String(s) => query.bind(s.clone()),
serde_json::Value::Number(n) => {
if let Some(i) = n.as_i64() {
query.bind(i)
} else if let Some(f) = n.as_f64() {
query.bind(f)
} else {
query.bind(Option::<String>::None)
}
}
serde_json::Value::Bool(b) => query.bind(*b),
serde_json::Value::Null => query.bind(Option::<String>::None),
other => query.bind(other.clone()),
}
}