Skip to main content

yang_base/table/table_query/
read.rs

1//! 读取执行:select/all/optional/one/count/paginate 及事务内变体。
2//!
3//! 公共入口按连接池后端运行时分派:MySQL 分支保持原有 `yang_db::QueryBuilder`
4//! 行为,PostgreSQL 分支由 `read_pg.rs` 提供(`postgres` feature)。
5
6#![cfg(any(feature = "mysql", feature = "postgres"))]
7
8use super::TableQuery;
9use crate::database::{AnyTransaction, AnyTransactionKind};
10use crate::error::BaseError;
11
12/// 分页窗口计数在结果行中的别名;读取后从 `Record` 中剥离。
13#[cfg(feature = "postgres")]
14const TOTAL_COUNT_ALIAS: &str = "__yang_total";
15
16#[cfg(feature = "postgres")]
17fn strip_total_count(mut record: crate::table::Record) -> crate::table::Record {
18    // 不能用 into_map() + Record::from 重建:那会丢弃 JSON null sidecar。
19    record.remove_column(TOTAL_COUNT_ALIAS);
20    record
21}
22
23/// 数据库执行方法(需要启用 `mysql` 或 `postgres` feature)
24impl TableQuery {
25    /// 为分页查询补齐默认页码/每页大小并写回查询参数。
26    ///
27    /// 同时为分页结果追加主键 tiebreaker:非唯一排序键(或默认排序)下,仅靠
28    /// 业务排序无法保证跨页稳定,主键提供确定性的最终排序。
29    pub(crate) fn with_effective_pagination(self) -> Result<(Self, usize, usize), BaseError> {
30        let page = self.query_params.page.unwrap_or(1);
31        let page_size = self
32            .query_params
33            .page_size
34            .unwrap_or(crate::table::query_params::DEFAULT_QUERY_PAGE_SIZE);
35        let mut query = self.page(page, page_size)?;
36
37        // 未显式排序时沿用表定义默认排序,再追加主键(复合主键逐列追加)。
38        if query.query_params.order_by.is_empty() {
39            query.query_params.order_by = query.table_config.default_order.clone();
40        }
41        for primary_key in query.table_config.primary_keys.clone() {
42            if !query
43                .query_params
44                .order_by
45                .iter()
46                .any(|(field, _)| field == &primary_key)
47            {
48                query
49                    .query_params
50                    .order_by
51                    .push((primary_key, crate::table::SortOrder::Asc));
52            }
53        }
54
55        Ok((query, page, page_size))
56    }
57
58    /// 执行分页查询并返回 schema-first [`Record`](crate::table::Record)。
59    ///
60    /// 未显式设置分页时会使用默认页码和默认每页大小,并确保数据查询带有 `LIMIT/OFFSET`。
61    pub async fn paginate_records(
62        self,
63    ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
64        self.paginate().await
65    }
66
67    /// 执行分页查询(内部方法,供 [`TableQuery::paginate_records`] 使用)。
68    pub(crate) async fn paginate(
69        self,
70    ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
71        match self.backend()? {
72            #[cfg(feature = "mysql")]
73            yang_db::BackendKind::MySql => self.paginate_mysql().await,
74            #[cfg(feature = "postgres")]
75            yang_db::BackendKind::PostgreSql => self.paginate_pg().await,
76            other => Err(BaseError::UnsupportedCapability(format!(
77                "TableQuery 不支持当前数据库后端: {other:?}"
78            ))),
79        }
80    }
81
82    /// MySQL 分页实现:COUNT → LIMIT/OFFSET 数据查询。
83    #[cfg(feature = "mysql")]
84    async fn paginate_mysql(
85        self,
86    ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
87        // 1. 检查数据库连接池是否存在
88        self.pool
89            .as_ref()
90            .ok_or(BaseError::DatabaseNotInitialized)?;
91
92        // 2. 获取分页参数,如果未设置则使用默认值,并写回数据查询
93        let (query, page, page_size) = self.with_effective_pagination()?;
94
95        // 3. 执行 COUNT(*) 查询获取总记录数
96        let total = query.count_internal().await?;
97
98        // 4. 如果总记录数为 0,直接返回空结果
99        if total == 0 {
100            return Ok(crate::table::PaginatedResult::empty(page, page_size));
101        }
102
103        // 5. 执行数据查询
104        let data = query.select_mysql().await?;
105
106        // 6. 构建并返回 PaginatedResult
107        Ok(crate::table::PaginatedResult::new(
108            data, total, page, page_size,
109        ))
110    }
111
112    /// PostgreSQL 分页实现:单查询窗口 `COUNT(*) OVER()` 同时返回当前页与总数。
113    ///
114    /// 窗口计数在同一条语句内完成,`total` 与 `items` 天然处于同一快照;仅当
115    /// 当前页为空(OFFSET 越界)无法从结果行取到 total 时,才回退到额外 COUNT。
116    #[cfg(feature = "postgres")]
117    async fn paginate_pg(
118        self,
119    ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
120        self.pool
121            .as_ref()
122            .ok_or(BaseError::DatabaseNotInitialized)?;
123
124        let (query, page, page_size) = self.with_effective_pagination()?;
125        let database = query.pg_database()?;
126        let total_alias = yang_db::FieldRef::new(TOTAL_COUNT_ALIAS)
127            .map_err(|error| BaseError::ConfigError(format!("窗口计数别名不合法: {error}")))?;
128        let builder = query
129            .apply_pg_read_plan(database.table(&query.table_config.table_ref))?
130            .window_expr(
131                yang_db::postgres::query_builder::WindowFunction::count_all(),
132                yang_db::postgres::query_builder::WindowSpec::new(),
133                &total_alias,
134            );
135        let rows = builder
136            .select::<crate::table::Record>()
137            .await
138            .map_err(BaseError::DatabaseQueryFailed)?;
139
140        let total = rows
141            .first()
142            .and_then(|row| row.get(TOTAL_COUNT_ALIAS))
143            .and_then(serde_json::Value::as_u64)
144            .map(|value| value as usize);
145        let data = rows.into_iter().map(strip_total_count).collect();
146        let total = match total {
147            Some(total) => total,
148            // OFFSET 越界时结果为空,窗口计数也随之丢失,回退到 COUNT。
149            None => query.count_internal().await?,
150        };
151
152        Ok(crate::table::PaginatedResult::new(
153            data, total, page, page_size,
154        ))
155    }
156
157    /// 执行 COUNT 查询获取总记录数(内部方法,供 paginate 使用)
158    ///
159    /// COUNT 会泄露表基数,因此与 SELECT 使用相同的可读权限守卫;分页只约束当前页
160    /// 数据,不改变总记录数,因此先清除分页参数再计数。
161    async fn count_internal(&self) -> Result<usize, BaseError> {
162        self.ensure_readable_projection()?;
163        let mut count_query = self.clone();
164        count_query.query_params.page = None;
165        count_query.query_params.page_size = None;
166
167        match count_query.backend()? {
168            #[cfg(feature = "mysql")]
169            yang_db::BackendKind::MySql => count_query.count_mysql().await,
170            #[cfg(feature = "postgres")]
171            yang_db::BackendKind::PostgreSql => count_query.count_pg().await,
172            other => Err(BaseError::UnsupportedCapability(format!(
173                "TableQuery 不支持当前数据库后端: {other:?}"
174            ))),
175        }
176    }
177
178    /// MySQL COUNT 实现。
179    #[cfg(feature = "mysql")]
180    async fn count_mysql(&self) -> Result<usize, BaseError> {
181        let count = self
182            .compile_db_query()?
183            .count()
184            .await
185            .map_err(BaseError::DatabaseQueryFailed)?;
186        usize::try_from(count).map_err(|_| {
187            BaseError::DatabaseQueryFailed(yang_db::DbError::QueryError(
188                "COUNT 结果超出 usize 范围".to_string(),
189            ))
190        })
191    }
192
193    /// PostgreSQL COUNT 实现。
194    ///
195    /// 只应用 WHERE/软删守卫:PostgreSQL 的 `SELECT COUNT(*) ... ORDER BY 非分组列`
196    /// 会被拒绝,且计数本身不需要投影、排序与分页。
197    #[cfg(feature = "postgres")]
198    async fn count_pg(&self) -> Result<usize, BaseError> {
199        let database = self.pg_database()?;
200        let count = self
201            .apply_pg_where_guards(database.table(&self.table_config.table_ref))?
202            .count()
203            .await
204            .map_err(BaseError::DatabaseQueryFailed)?;
205        usize::try_from(count).map_err(|_| {
206            BaseError::DatabaseQueryFailed(yang_db::DbError::QueryError(
207                "COUNT 结果为负数,超出 usize 范围".to_string(),
208            ))
209        })
210    }
211
212    /// 执行 COUNT 查询获取总记录数
213    ///
214    /// # 返回值
215    ///
216    /// - `Ok(u64)`:查询成功,返回总记录数
217    /// - `Err(BaseError)`:查询失败,返回错误
218    pub async fn count(self) -> Result<u64, BaseError> {
219        self.count_internal().await.map(|n| n as u64)
220    }
221
222    /// 判断是否存在至少一条匹配记录(`SELECT 1 ... LIMIT 1` 语义)。
223    ///
224    /// 与 `count() == 0` 相比不扫描全部匹配行,适合权限/去重前置判断。
225    pub async fn exists(self) -> Result<bool, BaseError> {
226        // 与 COUNT 一样,存在性也会泄露表基数,必须经过同一可读权限守卫。
227        self.ensure_readable_projection()?;
228        match self.backend()? {
229            #[cfg(feature = "mysql")]
230            yang_db::BackendKind::MySql => Ok(self
231                .compile_db_query()?
232                .limit(1)
233                .find::<crate::table::Record>()
234                .await
235                .map_err(BaseError::DatabaseQueryFailed)?
236                .is_some()),
237            #[cfg(feature = "postgres")]
238            yang_db::BackendKind::PostgreSql => {
239                let database = self.pg_database()?;
240                let key = self
241                    .table_config
242                    .primary_keys
243                    .first()
244                    .cloned()
245                    .or_else(|| self.table_config.fields.keys().min().cloned());
246                let Some(key) = key else {
247                    return Ok(false);
248                };
249                let key = self.field_ref(&key)?;
250                let query = self
251                    .apply_pg_where_guards(database.table(&self.table_config.table_ref))?
252                    .field(key);
253                Ok(query
254                    .limit(1)
255                    .find::<crate::table::Record>()
256                    .await
257                    .map_err(BaseError::DatabaseQueryFailed)?
258                    .is_some())
259            }
260            other => Err(BaseError::UnsupportedCapability(format!(
261                "TableQuery 不支持当前数据库后端: {other:?}"
262            ))),
263        }
264    }
265
266    /// 执行 SELECT 查询操作(内部方法)
267    async fn select(self) -> Result<Vec<crate::table::Record>, BaseError> {
268        match self.backend()? {
269            #[cfg(feature = "mysql")]
270            yang_db::BackendKind::MySql => self.select_mysql().await,
271            #[cfg(feature = "postgres")]
272            yang_db::BackendKind::PostgreSql => self.select_pg().await,
273            other => Err(BaseError::UnsupportedCapability(format!(
274                "TableQuery 不支持当前数据库后端: {other:?}"
275            ))),
276        }
277    }
278
279    /// MySQL SELECT 实现。
280    #[cfg(feature = "mysql")]
281    async fn select_mysql(self) -> Result<Vec<crate::table::Record>, BaseError> {
282        let query = if self.nulls_orders.is_empty() {
283            self.compile_db_query()?
284        } else {
285            self.compile_mysql_query_with_nulls()?
286        };
287        query
288            .select::<crate::table::Record>()
289            .await
290            .map_err(BaseError::DatabaseQueryFailed)
291    }
292
293    /// MySQL 读计划(含 NULLS FIRST/LAST 模拟)。
294    ///
295    /// `yang-db` 的排序入口无法表达 `field IS NULL` 表达式,因此仅在有 nulls 排序
296    /// 需求时重建一次读计划,与 [`super::plan::TableQuery::apply_read_plan`] 的
297    /// 投影/守卫/分页语义保持一致。
298    #[cfg(feature = "mysql")]
299    fn compile_mysql_query_with_nulls(&self) -> Result<yang_db::QueryBuilder<'_>, BaseError> {
300        let pool = self
301            .pool
302            .as_ref()
303            .and_then(|pool| pool.as_mysql())
304            .ok_or(BaseError::DatabaseNotInitialized)?;
305        let mut query = yang_db::QueryBuilder::from_pool(pool, &self.table_config.table_ref);
306        let selected = self.query_params.fields.as_ref().map_or_else(
307            || {
308                self.default_read_fields()
309                    .map(|values| values.into_iter().map(str::to_string).collect())
310            },
311            |values| Ok(values.clone()),
312        )?;
313        for name in selected {
314            query = query.field(self.field_ref(&name)?);
315        }
316        for condition in &self.query_params.where_conditions {
317            query = query
318                .where_predicate(&self.compile_predicate(condition)?)
319                .map_err(BaseError::DatabaseQueryFailed)?;
320        }
321        if !self.include_trashed {
322            if let Some(name) = &self.table_config.soft_delete_field {
323                query = query.where_null(self.field_ref(name)?);
324            }
325        }
326        let orders = if self.query_params.order_by.is_empty() {
327            &self.table_config.default_order
328        } else {
329            &self.query_params.order_by
330        };
331        for (name, order) in orders {
332            let db_order = match order {
333                crate::table::SortOrder::Asc => yang_db::SortOrder::Asc,
334                crate::table::SortOrder::Desc => yang_db::SortOrder::Desc,
335            };
336            if let Some(nulls_first) = self.nulls_orders.get(name) {
337                // MySQL 没有 NULLS 关键字:`field IS NULL`(1/0)前置到主排序键。
338                let nulls_dir = if *nulls_first { "DESC" } else { "ASC" };
339                let expr = yang_db::FieldRef::__from_validated_owned(format!(
340                    "{name}` IS NULL {nulls_dir}, `{name}"
341                ));
342                query = query.order(&expr, db_order);
343            } else {
344                query = query.order(self.field_ref(name)?, db_order);
345            }
346        }
347        if let Some(page_size) = self.query_params.page_size {
348            let page = self.query_params.page.unwrap_or(1).max(1);
349            let limit = u64::try_from(page_size).map_err(|_| {
350                BaseError::ParamInvalid("page_size".to_string(), "分页大小超出范围".to_string())
351            })?;
352            let offset = u64::try_from((page - 1).saturating_mul(page_size)).map_err(|_| {
353                BaseError::ParamInvalid("page".to_string(), "分页偏移超出范围".to_string())
354            })?;
355            query = query.limit(limit).offset(offset);
356        }
357        Ok(query)
358    }
359
360    /// PostgreSQL SELECT 实现(`Record` 实现 `PgRow` 解码)。
361    #[cfg(feature = "postgres")]
362    async fn select_pg(self) -> Result<Vec<crate::table::Record>, BaseError> {
363        let database = self.pg_database()?;
364        self.apply_pg_read_plan(database.table(&self.table_config.table_ref))?
365            .select::<crate::table::Record>()
366            .await
367            .map_err(BaseError::DatabaseQueryFailed)
368    }
369
370    /// 查询全部匹配记录。
371    pub async fn all(self) -> Result<Vec<crate::table::Record>, BaseError> {
372        self.select().await
373    }
374
375    /// 在事务中执行 SELECT 查询并返回多条记录
376    ///
377    /// 与 [`TableQuery::all`] 完全一致的建句与权限/软删逻辑,但在调用方提供的
378    /// [`AnyTransaction`] 内执行,使「读-改-写」场景的读取与后续写入处于同一
379    /// 事务、看到一致快照。
380    pub(crate) async fn select_in_tx(
381        self,
382        tx: &mut AnyTransaction,
383    ) -> Result<Vec<crate::table::Record>, BaseError> {
384        self.ensure_same_source(tx)?;
385        match tx.kind_mut() {
386            #[cfg(feature = "mysql")]
387            AnyTransactionKind::MySql(tx) => {
388                let query = tx.table(&self.table_config.table_ref);
389                self.apply_read_plan(query)?
390                    .select::<crate::table::Record>()
391                    .await
392                    .map_err(BaseError::DatabaseQueryFailed)
393            }
394            #[cfg(feature = "postgres")]
395            AnyTransactionKind::Postgres(tx) => {
396                let query = tx.table(&self.table_config.table_ref);
397                self.apply_pg_read_plan(query)?
398                    .select::<crate::table::Record>()
399                    .await
400                    .map_err(BaseError::DatabaseQueryFailed)
401            }
402        }
403    }
404
405    /// 在事务中查询全部匹配记录。
406    pub async fn all_in_tx(
407        self,
408        tx: &mut AnyTransaction,
409    ) -> Result<Vec<crate::table::Record>, BaseError> {
410        self.select_in_tx(tx).await
411    }
412
413    /// 执行查询并返回可选的单条记录(内部方法)
414    async fn fetch_optional(self) -> Result<Option<crate::table::Record>, BaseError> {
415        match self.backend()? {
416            #[cfg(feature = "mysql")]
417            yang_db::BackendKind::MySql => self.fetch_optional_mysql().await,
418            #[cfg(feature = "postgres")]
419            yang_db::BackendKind::PostgreSql => self.fetch_optional_pg().await,
420            other => Err(BaseError::UnsupportedCapability(format!(
421                "TableQuery 不支持当前数据库后端: {other:?}"
422            ))),
423        }
424    }
425
426    /// MySQL 单条查询实现。
427    #[cfg(feature = "mysql")]
428    async fn fetch_optional_mysql(self) -> Result<Option<crate::table::Record>, BaseError> {
429        let query = if self.nulls_orders.is_empty() {
430            self.compile_db_query()?
431        } else {
432            self.compile_mysql_query_with_nulls()?
433        };
434        query
435            .find::<crate::table::Record>()
436            .await
437            .map_err(BaseError::DatabaseQueryFailed)
438    }
439
440    /// PostgreSQL 单条查询实现。
441    #[cfg(feature = "postgres")]
442    async fn fetch_optional_pg(self) -> Result<Option<crate::table::Record>, BaseError> {
443        let database = self.pg_database()?;
444        self.apply_pg_read_plan(database.table(&self.table_config.table_ref))?
445            .find::<crate::table::Record>()
446            .await
447            .map_err(BaseError::DatabaseQueryFailed)
448    }
449
450    /// 查询可选单条记录。
451    pub async fn optional(self) -> Result<Option<crate::table::Record>, BaseError> {
452        self.fetch_optional().await
453    }
454
455    /// 查询单条记录;没有匹配记录时返回 [`BaseError::RecordNotFound`]。
456    pub async fn one(self) -> Result<crate::table::Record, BaseError> {
457        let table_name = self.table_config.table_name.clone();
458        self.optional()
459            .await?
460            .ok_or_else(|| BaseError::RecordNotFound(format!("表 {table_name} 中没有匹配记录")))
461    }
462}
463
464#[cfg(all(test, feature = "mysql"))]
465mod tests {
466    use super::*;
467    use crate::database::AnyPool;
468    use crate::table::{Field, SortOrder, Table};
469    use std::sync::Arc;
470
471    fn lazy_mysql_query(table: Table) -> TableQuery {
472        let definition = table.build().expect("测试表定义应有效");
473        let pool = sqlx::MySqlPool::connect_lazy("mysql://root@127.0.0.1:3306/test")
474            .expect("惰性 MySQL 连接池应创建成功");
475        TableQuery::new(
476            definition.shared_config(),
477            Arc::from(vec!["admin".to_string()]),
478            Some(AnyPool::MySql(Arc::new(pool))),
479        )
480    }
481
482    /// MySQL 没有 `NULLS FIRST/LAST`:用 `field IS NULL` 排序表达式模拟。
483    #[tokio::test]
484    async fn mysql_nulls_ordering_uses_is_null_expression() {
485        let nulls_last = lazy_mysql_query(
486            Table::new("tq_mysql_nulls").fields([Field::id("id"), Field::integer("bucket")]),
487        )
488        .order_nulls("bucket", SortOrder::Asc, false)
489        .expect("order_nulls 应通过校验")
490        .page(1, 10)
491        .expect("分页参数应合法");
492        let sql = nulls_last
493            .compile_mysql_query_with_nulls()
494            .expect("MySQL 读计划应编译成功")
495            .try_to_sql()
496            .expect("MySQL 读计划应能渲染 SQL");
497        assert!(
498            sql.contains("`bucket` IS NULL ASC, `bucket` ASC"),
499            "NULLS LAST 模拟缺失: {sql}"
500        );
501
502        let nulls_first = lazy_mysql_query(
503            Table::new("tq_mysql_nulls2").fields([Field::id("id"), Field::integer("bucket")]),
504        )
505        .order_nulls("bucket", SortOrder::Desc, true)
506        .expect("order_nulls 应通过校验")
507        .page(1, 10)
508        .expect("分页参数应合法");
509        let sql = nulls_first
510            .compile_mysql_query_with_nulls()
511            .expect("MySQL 读计划应编译成功")
512            .try_to_sql()
513            .expect("MySQL 读计划应能渲染 SQL");
514        assert!(
515            sql.contains("`bucket` IS NULL DESC, `bucket` DESC"),
516            "NULLS FIRST 模拟缺失: {sql}"
517        );
518    }
519}