#![cfg(any(feature = "mysql", feature = "postgres"))]
use super::TableQuery;
use crate::database::{AnyTransaction, AnyTransactionKind};
use crate::error::BaseError;
#[cfg(feature = "postgres")]
const TOTAL_COUNT_ALIAS: &str = "__yang_total";
#[cfg(feature = "postgres")]
fn strip_total_count(mut record: crate::table::Record) -> crate::table::Record {
record.remove_column(TOTAL_COUNT_ALIAS);
record
}
impl TableQuery {
pub(crate) fn with_effective_pagination(self) -> Result<(Self, usize, usize), BaseError> {
let page = self.query_params.page.unwrap_or(1);
let page_size = self
.query_params
.page_size
.unwrap_or(crate::table::query_params::DEFAULT_QUERY_PAGE_SIZE);
let mut query = self.page(page, page_size)?;
if query.query_params.order_by.is_empty() {
query.query_params.order_by = query.table_config.default_order.clone();
}
for primary_key in query.table_config.primary_keys.clone() {
if !query
.query_params
.order_by
.iter()
.any(|(field, _)| field == &primary_key)
{
query
.query_params
.order_by
.push((primary_key, crate::table::SortOrder::Asc));
}
}
Ok((query, page, page_size))
}
pub async fn paginate_records(
self,
) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
self.paginate().await
}
pub(crate) async fn paginate(
self,
) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
match self.backend()? {
#[cfg(feature = "mysql")]
yang_db::BackendKind::MySql => self.paginate_mysql().await,
#[cfg(feature = "postgres")]
yang_db::BackendKind::PostgreSql => self.paginate_pg().await,
other => Err(BaseError::UnsupportedCapability(format!(
"TableQuery 不支持当前数据库后端: {other:?}"
))),
}
}
#[cfg(feature = "mysql")]
async fn paginate_mysql(
self,
) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
self.pool
.as_ref()
.ok_or(BaseError::DatabaseNotInitialized)?;
let (query, page, page_size) = self.with_effective_pagination()?;
let total = query.count_internal().await?;
if total == 0 {
return Ok(crate::table::PaginatedResult::empty(page, page_size));
}
let data = query.select_mysql().await?;
Ok(crate::table::PaginatedResult::new(
data, total, page, page_size,
))
}
#[cfg(feature = "postgres")]
async fn paginate_pg(
self,
) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
self.pool
.as_ref()
.ok_or(BaseError::DatabaseNotInitialized)?;
let (query, page, page_size) = self.with_effective_pagination()?;
let database = query.pg_database()?;
let total_alias = yang_db::FieldRef::new(TOTAL_COUNT_ALIAS)
.map_err(|error| BaseError::ConfigError(format!("窗口计数别名不合法: {error}")))?;
let builder = query
.apply_pg_read_plan(database.table(&query.table_config.table_ref))?
.window_expr(
yang_db::postgres::query_builder::WindowFunction::count_all(),
yang_db::postgres::query_builder::WindowSpec::new(),
&total_alias,
);
let rows = builder
.select::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)?;
let total = rows
.first()
.and_then(|row| row.get(TOTAL_COUNT_ALIAS))
.and_then(serde_json::Value::as_u64)
.map(|value| value as usize);
let data = rows.into_iter().map(strip_total_count).collect();
let total = match total {
Some(total) => total,
None => query.count_internal().await?,
};
Ok(crate::table::PaginatedResult::new(
data, total, page, page_size,
))
}
async fn count_internal(&self) -> Result<usize, BaseError> {
self.ensure_readable_projection()?;
let mut count_query = self.clone();
count_query.query_params.page = None;
count_query.query_params.page_size = None;
match count_query.backend()? {
#[cfg(feature = "mysql")]
yang_db::BackendKind::MySql => count_query.count_mysql().await,
#[cfg(feature = "postgres")]
yang_db::BackendKind::PostgreSql => count_query.count_pg().await,
other => Err(BaseError::UnsupportedCapability(format!(
"TableQuery 不支持当前数据库后端: {other:?}"
))),
}
}
#[cfg(feature = "mysql")]
async fn count_mysql(&self) -> Result<usize, BaseError> {
let count = self
.compile_db_query()?
.count()
.await
.map_err(BaseError::DatabaseQueryFailed)?;
usize::try_from(count).map_err(|_| {
BaseError::DatabaseQueryFailed(yang_db::DbError::QueryError(
"COUNT 结果超出 usize 范围".to_string(),
))
})
}
#[cfg(feature = "postgres")]
async fn count_pg(&self) -> Result<usize, BaseError> {
let database = self.pg_database()?;
let count = self
.apply_pg_where_guards(database.table(&self.table_config.table_ref))?
.count()
.await
.map_err(BaseError::DatabaseQueryFailed)?;
usize::try_from(count).map_err(|_| {
BaseError::DatabaseQueryFailed(yang_db::DbError::QueryError(
"COUNT 结果为负数,超出 usize 范围".to_string(),
))
})
}
pub async fn count(self) -> Result<u64, BaseError> {
self.count_internal().await.map(|n| n as u64)
}
pub async fn exists(self) -> Result<bool, BaseError> {
self.ensure_readable_projection()?;
match self.backend()? {
#[cfg(feature = "mysql")]
yang_db::BackendKind::MySql => Ok(self
.compile_db_query()?
.limit(1)
.find::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)?
.is_some()),
#[cfg(feature = "postgres")]
yang_db::BackendKind::PostgreSql => {
let database = self.pg_database()?;
let key = self
.table_config
.primary_keys
.first()
.cloned()
.or_else(|| self.table_config.fields.keys().min().cloned());
let Some(key) = key else {
return Ok(false);
};
let key = self.field_ref(&key)?;
let query = self
.apply_pg_where_guards(database.table(&self.table_config.table_ref))?
.field(key);
Ok(query
.limit(1)
.find::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)?
.is_some())
}
other => Err(BaseError::UnsupportedCapability(format!(
"TableQuery 不支持当前数据库后端: {other:?}"
))),
}
}
async fn select(self) -> Result<Vec<crate::table::Record>, BaseError> {
match self.backend()? {
#[cfg(feature = "mysql")]
yang_db::BackendKind::MySql => self.select_mysql().await,
#[cfg(feature = "postgres")]
yang_db::BackendKind::PostgreSql => self.select_pg().await,
other => Err(BaseError::UnsupportedCapability(format!(
"TableQuery 不支持当前数据库后端: {other:?}"
))),
}
}
#[cfg(feature = "mysql")]
async fn select_mysql(self) -> Result<Vec<crate::table::Record>, BaseError> {
let query = if self.nulls_orders.is_empty() {
self.compile_db_query()?
} else {
self.compile_mysql_query_with_nulls()?
};
query
.select::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)
}
#[cfg(feature = "mysql")]
fn compile_mysql_query_with_nulls(&self) -> Result<yang_db::QueryBuilder<'_>, BaseError> {
let pool = self
.pool
.as_ref()
.and_then(|pool| pool.as_mysql())
.ok_or(BaseError::DatabaseNotInitialized)?;
let mut query = yang_db::QueryBuilder::from_pool(pool, &self.table_config.table_ref);
let selected = self.query_params.fields.as_ref().map_or_else(
|| {
self.default_read_fields()
.map(|values| values.into_iter().map(str::to_string).collect())
},
|values| Ok(values.clone()),
)?;
for name in selected {
query = query.field(self.field_ref(&name)?);
}
for condition in &self.query_params.where_conditions {
query = query
.where_predicate(&self.compile_predicate(condition)?)
.map_err(BaseError::DatabaseQueryFailed)?;
}
if !self.include_trashed {
if let Some(name) = &self.table_config.soft_delete_field {
query = query.where_null(self.field_ref(name)?);
}
}
let orders = if self.query_params.order_by.is_empty() {
&self.table_config.default_order
} else {
&self.query_params.order_by
};
for (name, order) in orders {
let db_order = match order {
crate::table::SortOrder::Asc => yang_db::SortOrder::Asc,
crate::table::SortOrder::Desc => yang_db::SortOrder::Desc,
};
if let Some(nulls_first) = self.nulls_orders.get(name) {
let nulls_dir = if *nulls_first { "DESC" } else { "ASC" };
let expr = yang_db::FieldRef::__from_validated_owned(format!(
"{name}` IS NULL {nulls_dir}, `{name}"
));
query = query.order(&expr, db_order);
} else {
query = query.order(self.field_ref(name)?, db_order);
}
}
if let Some(page_size) = self.query_params.page_size {
let page = self.query_params.page.unwrap_or(1).max(1);
let limit = u64::try_from(page_size).map_err(|_| {
BaseError::ParamInvalid("page_size".to_string(), "分页大小超出范围".to_string())
})?;
let offset = u64::try_from((page - 1).saturating_mul(page_size)).map_err(|_| {
BaseError::ParamInvalid("page".to_string(), "分页偏移超出范围".to_string())
})?;
query = query.limit(limit).offset(offset);
}
Ok(query)
}
#[cfg(feature = "postgres")]
async fn select_pg(self) -> Result<Vec<crate::table::Record>, BaseError> {
let database = self.pg_database()?;
self.apply_pg_read_plan(database.table(&self.table_config.table_ref))?
.select::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)
}
pub async fn all(self) -> Result<Vec<crate::table::Record>, BaseError> {
self.select().await
}
pub(crate) async fn select_in_tx(
self,
tx: &mut AnyTransaction,
) -> Result<Vec<crate::table::Record>, BaseError> {
self.ensure_same_source(tx)?;
match tx.kind_mut() {
#[cfg(feature = "mysql")]
AnyTransactionKind::MySql(tx) => {
let query = tx.table(&self.table_config.table_ref);
self.apply_read_plan(query)?
.select::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)
}
#[cfg(feature = "postgres")]
AnyTransactionKind::Postgres(tx) => {
let query = tx.table(&self.table_config.table_ref);
self.apply_pg_read_plan(query)?
.select::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)
}
}
}
pub async fn all_in_tx(
self,
tx: &mut AnyTransaction,
) -> Result<Vec<crate::table::Record>, BaseError> {
self.select_in_tx(tx).await
}
async fn fetch_optional(self) -> Result<Option<crate::table::Record>, BaseError> {
match self.backend()? {
#[cfg(feature = "mysql")]
yang_db::BackendKind::MySql => self.fetch_optional_mysql().await,
#[cfg(feature = "postgres")]
yang_db::BackendKind::PostgreSql => self.fetch_optional_pg().await,
other => Err(BaseError::UnsupportedCapability(format!(
"TableQuery 不支持当前数据库后端: {other:?}"
))),
}
}
#[cfg(feature = "mysql")]
async fn fetch_optional_mysql(self) -> Result<Option<crate::table::Record>, BaseError> {
let query = if self.nulls_orders.is_empty() {
self.compile_db_query()?
} else {
self.compile_mysql_query_with_nulls()?
};
query
.find::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)
}
#[cfg(feature = "postgres")]
async fn fetch_optional_pg(self) -> Result<Option<crate::table::Record>, BaseError> {
let database = self.pg_database()?;
self.apply_pg_read_plan(database.table(&self.table_config.table_ref))?
.find::<crate::table::Record>()
.await
.map_err(BaseError::DatabaseQueryFailed)
}
pub async fn optional(self) -> Result<Option<crate::table::Record>, BaseError> {
self.fetch_optional().await
}
pub async fn one(self) -> Result<crate::table::Record, BaseError> {
let table_name = self.table_config.table_name.clone();
self.optional()
.await?
.ok_or_else(|| BaseError::RecordNotFound(format!("表 {table_name} 中没有匹配记录")))
}
}
#[cfg(all(test, feature = "mysql"))]
mod tests {
use super::*;
use crate::database::AnyPool;
use crate::table::{Field, SortOrder, Table};
use std::sync::Arc;
fn lazy_mysql_query(table: Table) -> TableQuery {
let definition = table.build().expect("测试表定义应有效");
let pool = sqlx::MySqlPool::connect_lazy("mysql://root@127.0.0.1:3306/test")
.expect("惰性 MySQL 连接池应创建成功");
TableQuery::new(
definition.shared_config(),
Arc::from(vec!["admin".to_string()]),
Some(AnyPool::MySql(Arc::new(pool))),
)
}
#[tokio::test]
async fn mysql_nulls_ordering_uses_is_null_expression() {
let nulls_last = lazy_mysql_query(
Table::new("tq_mysql_nulls").fields([Field::id("id"), Field::integer("bucket")]),
)
.order_nulls("bucket", SortOrder::Asc, false)
.expect("order_nulls 应通过校验")
.page(1, 10)
.expect("分页参数应合法");
let sql = nulls_last
.compile_mysql_query_with_nulls()
.expect("MySQL 读计划应编译成功")
.try_to_sql()
.expect("MySQL 读计划应能渲染 SQL");
assert!(
sql.contains("`bucket` IS NULL ASC, `bucket` ASC"),
"NULLS LAST 模拟缺失: {sql}"
);
let nulls_first = lazy_mysql_query(
Table::new("tq_mysql_nulls2").fields([Field::id("id"), Field::integer("bucket")]),
)
.order_nulls("bucket", SortOrder::Desc, true)
.expect("order_nulls 应通过校验")
.page(1, 10)
.expect("分页参数应合法");
let sql = nulls_first
.compile_mysql_query_with_nulls()
.expect("MySQL 读计划应编译成功")
.try_to_sql()
.expect("MySQL 读计划应能渲染 SQL");
assert!(
sql.contains("`bucket` IS NULL DESC, `bucket` DESC"),
"NULLS FIRST 模拟缺失: {sql}"
);
}
}