use crate::error::BaseError;
use crate::table::{QueryParams, SortOrder, TableConfig, WhereCondition};
use serde::Serialize;
use serde_json::Value;
use std::sync::Arc;
#[derive(Debug, Clone)]
pub struct TableQuery {
table_config: Arc<TableConfig>,
user_roles: Vec<String>,
query_params: QueryParams,
#[allow(dead_code)]
pool: Option<Arc<sqlx::MySqlPool>>,
}
impl TableQuery {
pub fn new(
table_config: Arc<TableConfig>,
user_roles: Vec<String>,
pool: Option<Arc<sqlx::MySqlPool>>,
) -> Self {
Self {
table_config,
user_roles,
query_params: QueryParams::new(),
pool,
}
}
pub fn select_fields(mut self, fields: &[&str]) -> Result<Self, BaseError> {
for field_name in fields {
let field_config = self.table_config.get_field(field_name).ok_or_else(|| {
BaseError::FieldNotFound(
self.table_config.table_name.clone(),
field_name.to_string(),
)
})?;
if !field_config.permissions.can_read(&self.user_roles) {
return Err(BaseError::FieldPermissionDenied(
self.table_config.table_name.clone(),
field_name.to_string(),
"用户无读取权限".to_string(),
));
}
}
self.query_params.fields = Some(fields.iter().map(|s| s.to_string()).collect());
Ok(self)
}
pub fn where_eq(mut self, field: &str, value: Value) -> Result<Self, BaseError> {
self.validate_filter_field(field)?;
self.query_params.where_conditions.push(WhereCondition::Eq {
field: field.to_string(),
value,
});
Ok(self)
}
pub fn where_in(mut self, field: &str, values: Vec<Value>) -> Result<Self, BaseError> {
self.validate_filter_field(field)?;
self.query_params.where_conditions.push(WhereCondition::In {
field: field.to_string(),
values,
});
Ok(self)
}
pub fn where_like(mut self, field: &str, pattern: String) -> Result<Self, BaseError> {
self.validate_filter_field(field)?;
self.query_params
.where_conditions
.push(WhereCondition::Like {
field: field.to_string(),
pattern,
});
Ok(self)
}
pub fn order_by(mut self, field: &str, order: SortOrder) -> Result<Self, BaseError> {
let field_config = self.table_config.get_field(field).ok_or_else(|| {
BaseError::FieldNotFound(self.table_config.table_name.clone(), field.to_string())
})?;
if !field_config.permissions.can_sort(&self.user_roles) {
return Err(BaseError::FieldPermissionDenied(
self.table_config.table_name.clone(),
field.to_string(),
"用户无排序权限".to_string(),
));
}
self.query_params.order_by.push((field.to_string(), order));
Ok(self)
}
pub fn page(mut self, page: usize, page_size: usize) -> Result<Self, BaseError> {
self.query_params.page = Some(page);
self.query_params.page_size = Some(page_size);
Ok(self)
}
fn validate_filter_field(&self, field: &str) -> Result<(), BaseError> {
let field_config = self.table_config.get_field(field).ok_or_else(|| {
BaseError::FieldNotFound(self.table_config.table_name.clone(), field.to_string())
})?;
if !field_config.permissions.can_filter(&self.user_roles) {
return Err(BaseError::FieldPermissionDenied(
self.table_config.table_name.clone(),
field.to_string(),
"用户无筛选权限".to_string(),
));
}
Ok(())
}
#[allow(dead_code)]
pub fn get_query_params(&self) -> &QueryParams {
&self.query_params
}
#[allow(dead_code)]
pub fn get_table_config(&self) -> &Arc<TableConfig> {
&self.table_config
}
#[allow(dead_code)]
pub fn get_user_roles(&self) -> &[String] {
&self.user_roles
}
pub async fn paginate<T>(self) -> Result<crate::table::PaginatedResult<T>, BaseError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::mysql::MySqlRow> + Send + Unpin + Serialize,
{
let _pool = self
.pool
.as_ref()
.ok_or(BaseError::DatabaseNotInitialized)?;
let page = self.query_params.page.unwrap_or(1);
let page_size = self.query_params.page_size.unwrap_or(20);
let total = self.count().await?;
if total == 0 {
return Ok(crate::table::PaginatedResult::empty(page, page_size));
}
let data = self.select().await?;
Ok(crate::table::PaginatedResult::new(
data, total, page, page_size,
))
}
async fn count(&self) -> Result<usize, BaseError> {
let pool = self
.pool
.as_ref()
.ok_or(BaseError::DatabaseNotInitialized)?;
let (sql, params) = self.build_count_sql()?;
let mut query = sqlx::query_scalar::<_, i64>(&sql);
for param in params {
query = Self::bind_count_param(query, ¶m);
}
let count = query
.fetch_one(pool.as_ref())
.await
.map_err(|e| BaseError::DatabaseQueryFailed(e.to_string()))?;
Ok(count as usize)
}
fn build_count_sql(&self) -> Result<(String, Vec<SqlParam>), BaseError> {
let mut sql = format!("SELECT COUNT(*) FROM {}", self.table_config.table_name);
let mut params = Vec::new();
if !self.query_params.where_conditions.is_empty() {
sql.push_str(" WHERE ");
let mut first = true;
for condition in &self.query_params.where_conditions {
if !first {
sql.push_str(" AND ");
}
first = false;
match condition {
WhereCondition::Eq { field, value } => {
sql.push_str(&format!("{} = ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::In { field, values } => {
let placeholders = vec!["?"; values.len()].join(", ");
sql.push_str(&format!("{} IN ({})", field, placeholders));
for value in values {
params.push(SqlParam::from_json(value)?);
}
}
WhereCondition::Like { field, pattern } => {
sql.push_str(&format!("{} LIKE ?", field));
params.push(SqlParam::String(pattern.clone()));
}
WhereCondition::Gt { field, value } => {
sql.push_str(&format!("{} > ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Gte { field, value } => {
sql.push_str(&format!("{} >= ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Lt { field, value } => {
sql.push_str(&format!("{} < ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Lte { field, value } => {
sql.push_str(&format!("{} <= ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::IsNull { field } => {
sql.push_str(&format!("{} IS NULL", field));
}
WhereCondition::IsNotNull { field } => {
sql.push_str(&format!("{} IS NOT NULL", field));
}
}
}
}
Ok((sql, params))
}
fn bind_count_param<'q>(
query: sqlx::query::QueryScalar<'q, sqlx::MySql, i64, sqlx::mysql::MySqlArguments>,
param: &SqlParam,
) -> sqlx::query::QueryScalar<'q, sqlx::MySql, i64, sqlx::mysql::MySqlArguments> {
match param {
SqlParam::Null => query.bind(Option::<i32>::None),
SqlParam::Bool(b) => query.bind(*b),
SqlParam::Int(i) => query.bind(*i),
SqlParam::Float(f) => query.bind(*f),
SqlParam::String(s) => query.bind(s.clone()),
}
}
pub async fn select<T>(self) -> Result<Vec<T>, BaseError>
where
T: for<'r> sqlx::FromRow<'r, sqlx::mysql::MySqlRow> + Send + Unpin,
{
let pool = self
.pool
.as_ref()
.ok_or(BaseError::DatabaseNotInitialized)?;
let (sql, params) = self.build_select_sql()?;
let mut query = sqlx::query_as::<_, T>(&sql);
for param in params {
query = Self::bind_param(query, ¶m);
}
let results = query
.fetch_all(pool.as_ref())
.await
.map_err(|e| BaseError::DatabaseQueryFailed(e.to_string()))?;
Ok(results)
}
fn build_select_sql(&self) -> Result<(String, Vec<SqlParam>), BaseError> {
let mut sql = String::from("SELECT ");
let mut params = Vec::new();
if let Some(fields) = &self.query_params.fields {
if fields.is_empty() {
sql.push('*');
} else {
sql.push_str(&fields.join(", "));
}
} else {
sql.push('*');
}
sql.push_str(&format!(" FROM {}", self.table_config.table_name));
if !self.query_params.where_conditions.is_empty() {
sql.push_str(" WHERE ");
let mut first = true;
for condition in &self.query_params.where_conditions {
if !first {
sql.push_str(" AND ");
}
first = false;
match condition {
WhereCondition::Eq { field, value } => {
sql.push_str(&format!("{} = ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::In { field, values } => {
let placeholders = vec!["?"; values.len()].join(", ");
sql.push_str(&format!("{} IN ({})", field, placeholders));
for value in values {
params.push(SqlParam::from_json(value)?);
}
}
WhereCondition::Like { field, pattern } => {
sql.push_str(&format!("{} LIKE ?", field));
params.push(SqlParam::String(pattern.clone()));
}
WhereCondition::Gt { field, value } => {
sql.push_str(&format!("{} > ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Gte { field, value } => {
sql.push_str(&format!("{} >= ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Lt { field, value } => {
sql.push_str(&format!("{} < ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Lte { field, value } => {
sql.push_str(&format!("{} <= ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::IsNull { field } => {
sql.push_str(&format!("{} IS NULL", field));
}
WhereCondition::IsNotNull { field } => {
sql.push_str(&format!("{} IS NOT NULL", field));
}
}
}
}
if !self.query_params.order_by.is_empty() {
sql.push_str(" ORDER BY ");
let order_clauses: Vec<String> = self
.query_params
.order_by
.iter()
.map(|(field, order)| {
let direction = match order {
SortOrder::Asc => "ASC",
SortOrder::Desc => "DESC",
};
format!("{} {}", field, direction)
})
.collect();
sql.push_str(&order_clauses.join(", "));
}
if let (Some(page), Some(page_size)) = (self.query_params.page, self.query_params.page_size)
{
let offset = (page - 1) * page_size;
sql.push_str(&format!(" LIMIT {} OFFSET {}", page_size, offset));
}
Ok((sql, params))
}
fn bind_param<'q, T>(
query: sqlx::query::QueryAs<'q, sqlx::MySql, T, sqlx::mysql::MySqlArguments>,
param: &SqlParam,
) -> sqlx::query::QueryAs<'q, sqlx::MySql, T, sqlx::mysql::MySqlArguments>
where
T: for<'r> sqlx::FromRow<'r, sqlx::mysql::MySqlRow> + Send + Unpin,
{
match param {
SqlParam::Null => query.bind(Option::<i32>::None),
SqlParam::Bool(b) => query.bind(*b),
SqlParam::Int(i) => query.bind(*i),
SqlParam::Float(f) => query.bind(*f),
SqlParam::String(s) => query.bind(s.clone()),
}
}
pub async fn insert(
self,
data: std::collections::HashMap<String, Value>,
) -> Result<u64, BaseError> {
let pool = self
.pool
.as_ref()
.ok_or(BaseError::DatabaseNotInitialized)?;
self.validate_insert_data(&data)?;
let (sql, params) = self.build_insert_sql(&data)?;
let mut query = sqlx::query(&sql);
for param in params {
query = Self::bind_execute_param(query, ¶m);
}
let result = query
.execute(pool.as_ref())
.await
.map_err(|e| BaseError::DatabaseExecuteFailed(e.to_string()))?;
Ok(result.rows_affected())
}
fn validate_insert_data(
&self,
data: &std::collections::HashMap<String, Value>,
) -> Result<(), BaseError> {
for (field_name, field_config) in &self.table_config.fields {
let value = data.get(field_name).unwrap_or(&Value::Null);
if !field_config.permissions.can_write(&self.user_roles) {
if !value.is_null() {
return Err(BaseError::FieldPermissionDenied(
self.table_config.table_name.clone(),
field_name.clone(),
"用户无写入权限".to_string(),
));
}
continue;
}
field_config.validate(value)?;
}
Ok(())
}
fn build_insert_sql(
&self,
data: &std::collections::HashMap<String, Value>,
) -> Result<(String, Vec<SqlParam>), BaseError> {
let mut fields = Vec::new();
let mut placeholders = Vec::new();
let mut params = Vec::new();
for (field_name, value) in data {
if !self.table_config.fields.contains_key(field_name) {
return Err(BaseError::FieldNotFound(
self.table_config.table_name.clone(),
field_name.clone(),
));
}
if let Some(field_config) = self.table_config.fields.get(field_name) {
if !field_config.permissions.can_write(&self.user_roles) {
if !value.is_null() {
continue;
}
}
}
fields.push(field_name.clone());
placeholders.push("?".to_string());
params.push(SqlParam::from_json(value)?);
}
let sql = format!(
"INSERT INTO {} ({}) VALUES ({})",
self.table_config.table_name,
fields.join(", "),
placeholders.join(", ")
);
Ok((sql, params))
}
pub async fn update(
self,
data: std::collections::HashMap<String, Value>,
) -> Result<u64, BaseError> {
let pool = self
.pool
.as_ref()
.ok_or(BaseError::DatabaseNotInitialized)?;
self.validate_update_data(&data)?;
let (sql, params) = self.build_update_sql(&data)?;
let mut query = sqlx::query(&sql);
for param in params {
query = Self::bind_execute_param(query, ¶m);
}
let result = query
.execute(pool.as_ref())
.await
.map_err(|e| BaseError::DatabaseExecuteFailed(e.to_string()))?;
Ok(result.rows_affected())
}
#[cfg(test)]
pub fn validate_update_data(
&self,
data: &std::collections::HashMap<String, Value>,
) -> Result<(), BaseError> {
self.validate_update_data_impl(data)
}
#[cfg(not(test))]
fn validate_update_data(
&self,
data: &std::collections::HashMap<String, Value>,
) -> Result<(), BaseError> {
self.validate_update_data_impl(data)
}
fn validate_update_data_impl(
&self,
data: &std::collections::HashMap<String, Value>,
) -> Result<(), BaseError> {
for (field_name, value) in data {
let field_config = self.table_config.get_field(field_name).ok_or_else(|| {
BaseError::FieldNotFound(self.table_config.table_name.clone(), field_name.clone())
})?;
if !field_config.permissions.can_write(&self.user_roles) {
return Err(BaseError::FieldPermissionDenied(
self.table_config.table_name.clone(),
field_name.clone(),
"用户无写入权限".to_string(),
));
}
field_config.validate(value)?;
}
Ok(())
}
#[cfg(test)]
#[allow(private_interfaces)]
pub fn build_update_sql(
&self,
data: &std::collections::HashMap<String, Value>,
) -> Result<(String, Vec<SqlParam>), BaseError> {
self.build_update_sql_impl(data)
}
#[cfg(not(test))]
fn build_update_sql(
&self,
data: &std::collections::HashMap<String, Value>,
) -> Result<(String, Vec<SqlParam>), BaseError> {
self.build_update_sql_impl(data)
}
fn build_update_sql_impl(
&self,
data: &std::collections::HashMap<String, Value>,
) -> Result<(String, Vec<SqlParam>), BaseError> {
let mut set_clauses = Vec::new();
let mut params = Vec::new();
for (field_name, value) in data {
if !self.table_config.fields.contains_key(field_name) {
return Err(BaseError::FieldNotFound(
self.table_config.table_name.clone(),
field_name.clone(),
));
}
set_clauses.push(format!("{} = ?", field_name));
params.push(SqlParam::from_json(value)?);
}
let mut sql = format!(
"UPDATE {} SET {}",
self.table_config.table_name,
set_clauses.join(", ")
);
if !self.query_params.where_conditions.is_empty() {
sql.push_str(" WHERE ");
let mut first = true;
for condition in &self.query_params.where_conditions {
if !first {
sql.push_str(" AND ");
}
first = false;
match condition {
WhereCondition::Eq { field, value } => {
sql.push_str(&format!("{} = ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::In { field, values } => {
let placeholders = vec!["?"; values.len()].join(", ");
sql.push_str(&format!("{} IN ({})", field, placeholders));
for value in values {
params.push(SqlParam::from_json(value)?);
}
}
WhereCondition::Like { field, pattern } => {
sql.push_str(&format!("{} LIKE ?", field));
params.push(SqlParam::String(pattern.clone()));
}
WhereCondition::Gt { field, value } => {
sql.push_str(&format!("{} > ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Gte { field, value } => {
sql.push_str(&format!("{} >= ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Lt { field, value } => {
sql.push_str(&format!("{} < ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Lte { field, value } => {
sql.push_str(&format!("{} <= ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::IsNull { field } => {
sql.push_str(&format!("{} IS NULL", field));
}
WhereCondition::IsNotNull { field } => {
sql.push_str(&format!("{} IS NOT NULL", field));
}
}
}
}
Ok((sql, params))
}
pub async fn delete(self) -> Result<u64, BaseError> {
let pool = self
.pool
.as_ref()
.ok_or(BaseError::DatabaseNotInitialized)?;
if let Some(soft_delete_field) = &self.table_config.soft_delete_field {
let now = chrono::Utc::now().timestamp();
let mut data = std::collections::HashMap::new();
data.insert(soft_delete_field.clone(), Value::Number(now.into()));
return self.update(data).await;
}
let (sql, params) = self.build_delete_sql()?;
let mut query = sqlx::query(&sql);
for param in params {
query = Self::bind_execute_param(query, ¶m);
}
let result = query
.execute(pool.as_ref())
.await
.map_err(|e| BaseError::DatabaseExecuteFailed(e.to_string()))?;
Ok(result.rows_affected())
}
#[cfg(test)]
#[allow(private_interfaces)]
pub fn build_delete_sql(&self) -> Result<(String, Vec<SqlParam>), BaseError> {
self.build_delete_sql_impl()
}
#[cfg(not(test))]
fn build_delete_sql(&self) -> Result<(String, Vec<SqlParam>), BaseError> {
self.build_delete_sql_impl()
}
fn build_delete_sql_impl(&self) -> Result<(String, Vec<SqlParam>), BaseError> {
let mut sql = format!("DELETE FROM {}", self.table_config.table_name);
let mut params = Vec::new();
if !self.query_params.where_conditions.is_empty() {
sql.push_str(" WHERE ");
let mut first = true;
for condition in &self.query_params.where_conditions {
if !first {
sql.push_str(" AND ");
}
first = false;
match condition {
WhereCondition::Eq { field, value } => {
sql.push_str(&format!("{} = ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::In { field, values } => {
let placeholders = vec!["?"; values.len()].join(", ");
sql.push_str(&format!("{} IN ({})", field, placeholders));
for value in values {
params.push(SqlParam::from_json(value)?);
}
}
WhereCondition::Like { field, pattern } => {
sql.push_str(&format!("{} LIKE ?", field));
params.push(SqlParam::String(pattern.clone()));
}
WhereCondition::Gt { field, value } => {
sql.push_str(&format!("{} > ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Gte { field, value } => {
sql.push_str(&format!("{} >= ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Lt { field, value } => {
sql.push_str(&format!("{} < ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::Lte { field, value } => {
sql.push_str(&format!("{} <= ?", field));
params.push(SqlParam::from_json(value)?);
}
WhereCondition::IsNull { field } => {
sql.push_str(&format!("{} IS NULL", field));
}
WhereCondition::IsNotNull { field } => {
sql.push_str(&format!("{} IS NOT NULL", field));
}
}
}
}
Ok((sql, params))
}
fn bind_execute_param<'q>(
query: sqlx::query::Query<'q, sqlx::MySql, sqlx::mysql::MySqlArguments>,
param: &SqlParam,
) -> sqlx::query::Query<'q, sqlx::MySql, sqlx::mysql::MySqlArguments> {
match param {
SqlParam::Null => query.bind(Option::<i32>::None),
SqlParam::Bool(b) => query.bind(*b),
SqlParam::Int(i) => query.bind(*i),
SqlParam::Float(f) => query.bind(*f),
SqlParam::String(s) => query.bind(s.clone()),
}
}
}
#[derive(Debug, Clone)]
#[cfg_attr(test, derive(PartialEq))]
pub(crate) enum SqlParam {
Null,
Bool(bool),
Int(i64),
Float(f64),
String(String),
}
impl SqlParam {
fn from_json(value: &Value) -> Result<Self, BaseError> {
match value {
Value::Null => Ok(SqlParam::Null),
Value::Bool(b) => Ok(SqlParam::Bool(*b)),
Value::Number(n) => {
if let Some(i) = n.as_i64() {
Ok(SqlParam::Int(i))
} else if let Some(f) = n.as_f64() {
Ok(SqlParam::Float(f))
} else {
Err(BaseError::DatabaseQueryFailed(format!(
"不支持的数字类型: {}",
n
)))
}
}
Value::String(s) => Ok(SqlParam::String(s.clone())),
_ => Err(BaseError::DatabaseQueryFailed(format!(
"不支持的值类型: {:?}",
value
))),
}
}
}