sz_orm_core/
quick_query.rs

1//! 快捷查询（Db::name 风格）
2//!
3//! 对应 think-orm 的 `Db::name('user')->where(...)->select()` API。
4//! 无需定义 Model 即可直接基于表名查询/插入/更新/删除。
5//!
6//! # 与 QueryBuilder 的关系
7//!
8//! `QueryBuilder<M>` 要求泛型参数 `M: Model`，适合已知 Model 类型的场景。
9//! `QuickQuery` 则用 `()` 占位 Model，仅依赖表名，避免为临时查询定义 Model。
10//!
11//! # 用法
12//!
13//! ```no_run
14//! use sz_orm_core::quick_query::Db;
15//! use sz_orm_core::{get_dialect, DbType, Value};
16//!
17//! let dialect = get_dialect(DbType::MySQL).unwrap();
18//! // SELECT * FROM users WHERE age > 18 ORDER BY id DESC LIMIT 10
19//! let sql = Db::new(dialect).name("users")
20//!     .where_gt("age", Value::I64(18))
21//!     .order_desc("id")
22//!     .limit(10)
23//!     .build_select();
24//! ```
25
26use crate::dialect::Dialect;
27use crate::query::QueryBuilder;
28use crate::query::{AggExpr, HavingOp};
29use crate::value::Value;
30use std::collections::HashMap;
31use sz_orm_macros::api_stable;
32
33/// 内部占位 Model：仅用于满足 `QueryBuilder<M>` 的泛型约束，不携带任何行为
34#[derive(Clone)]
35struct AnonymousModel;
36
37impl crate::model::Model for AnonymousModel {
38    type PrimaryKey = i64;
39    fn table_name() -> &'static str {
40        ""
41    }
42    fn pk(&self) -> Self::PrimaryKey {
43        0
44    }
45    fn set_pk(&mut self, _pk: Self::PrimaryKey) {}
46}
47
48/// 快捷查询入口（think-orm `Db::name()` 风格）
49///
50/// 不要求定义 Model，仅靠表名 + 方言即可生成 SQL。
51pub struct Db {
52    qb: QueryBuilder<AnonymousModel>,
53}
54
55impl Db {
56    /// 创建快捷查询入口
57    pub fn new(dialect: Box<dyn Dialect>) -> Self {
58        Self {
59            qb: QueryBuilder::new(dialect),
60        }
61    }
62
63    /// 指定表名（等价于 think-orm 的 `Db::name('user')`）
64    #[must_use]
65    pub fn name(mut self, table: impl Into<String>) -> Self {
66        self.qb = self.qb.table(table);
67        self
68    }
69
70    /// 选择列（列名经校验 + quote，审计 M-6）
71    pub fn select(mut self, columns: Vec<&str>) -> Result<Self, crate::DbError> {
72        self.qb = self.qb.select(columns)?;
73        Ok(self)
74    }
75
76    /// 选择表达式列（审计 M-6 逃生口）：原样拼接，调用方须确保来自可信来源
77    #[must_use]
78    pub fn select_expr(mut self, columns: Vec<&str>) -> Self {
79        self.qb = self.qb.select_expr(columns);
80        self
81    }
82    #[api_stable]
83
84    /// P0-2：参数化等值条件 `field = ?`（AND 关系）
85    #[must_use]
86    pub fn where_eq(mut self, field: impl Into<String>, value: Value) -> Self {
87        self.qb = self.qb.where_eq(field, value);
88        self
89    }
90    #[api_stable]
91
92    /// P0-2：参数化不等条件 `field != ?`（AND 关系）
93    #[must_use]
94    pub fn where_ne(mut self, field: impl Into<String>, value: Value) -> Self {
95        self.qb = self.qb.where_ne(field, value);
96        self
97    }
98    #[api_stable]
99
100    /// P0-2：参数化大于条件 `field > ?`（AND 关系）
101    #[must_use]
102    pub fn where_gt(mut self, field: impl Into<String>, value: Value) -> Self {
103        self.qb = self.qb.where_gt(field, value);
104        self
105    }
106    #[api_stable]
107
108    /// P0-2：参数化大于等于条件 `field >= ?`（AND 关系）
109    #[must_use]
110    pub fn where_ge(mut self, field: impl Into<String>, value: Value) -> Self {
111        self.qb = self.qb.where_ge(field, value);
112        self
113    }
114    #[api_stable]
115
116    /// P0-2：参数化小于条件 `field < ?`（AND 关系）
117    #[must_use]
118    pub fn where_lt(mut self, field: impl Into<String>, value: Value) -> Self {
119        self.qb = self.qb.where_lt(field, value);
120        self
121    }
122    #[api_stable]
123
124    /// P0-2：参数化小于等于条件 `field <= ?`（AND 关系）
125    #[must_use]
126    pub fn where_le(mut self, field: impl Into<String>, value: Value) -> Self {
127        self.qb = self.qb.where_le(field, value);
128        self
129    }
130    #[api_stable]
131
132    /// P0-2：参数化 LIKE 条件 `field LIKE ?`（AND 关系）
133    #[must_use]
134    pub fn where_like(mut self, field: impl Into<String>, pattern: Value) -> Self {
135        self.qb = self.qb.where_like(field, pattern);
136        self
137    }
138    #[api_stable]
139
140    /// P0-2：参数化 OR 等值条件 `OR field = ?`
141    #[must_use]
142    pub fn or_where_eq(mut self, field: impl Into<String>, value: Value) -> Self {
143        self.qb = self.qb.or_where_eq(field, value);
144        self
145    }
146    #[api_stable]
147
148    /// P0-2：参数化 OR 不等条件 `OR field != ?`
149    #[must_use]
150    pub fn or_where_ne(mut self, field: impl Into<String>, value: Value) -> Self {
151        self.qb = self.qb.or_where_ne(field, value);
152        self
153    }
154    #[api_stable]
155
156    /// P0-2：参数化 OR 大于条件 `OR field > ?`
157    #[must_use]
158    pub fn or_where_gt(mut self, field: impl Into<String>, value: Value) -> Self {
159        self.qb = self.qb.or_where_gt(field, value);
160        self
161    }
162    #[api_stable]
163
164    /// P0-2：参数化 OR 大于等于条件 `OR field >= ?`
165    #[must_use]
166    pub fn or_where_ge(mut self, field: impl Into<String>, value: Value) -> Self {
167        self.qb = self.qb.or_where_ge(field, value);
168        self
169    }
170    #[api_stable]
171
172    /// P0-2：参数化 OR 小于条件 `OR field < ?`
173    #[must_use]
174    pub fn or_where_lt(mut self, field: impl Into<String>, value: Value) -> Self {
175        self.qb = self.qb.or_where_lt(field, value);
176        self
177    }
178    #[api_stable]
179
180    /// P0-2：参数化 OR 小于等于条件 `OR field <= ?`
181    #[must_use]
182    pub fn or_where_le(mut self, field: impl Into<String>, value: Value) -> Self {
183        self.qb = self.qb.or_where_le(field, value);
184        self
185    }
186    #[api_stable]
187
188    /// P0-2：参数化 OR LIKE 条件 `OR field LIKE ?`
189    #[must_use]
190    pub fn or_where_like(mut self, field: impl Into<String>, pattern: Value) -> Self {
191        self.qb = self.qb.or_where_like(field, pattern);
192        self
193    }
194    #[api_stable]
195
196    /// WHERE IN
197    #[must_use]
198    pub fn where_in(mut self, field: impl Into<String>, values: Vec<Value>) -> Self {
199        self.qb = self.qb.where_in(field, values);
200        self
201    }
202
203    /// WHERE NOT IN
204    #[must_use]
205    pub fn where_not_in(mut self, field: impl Into<String>, values: Vec<Value>) -> Self {
206        self.qb = self.qb.where_not_in(field, values);
207        self
208    }
209    #[api_stable]
210
211    /// WHERE BETWEEN
212    #[must_use]
213    pub fn where_between(mut self, field: impl Into<String>, start: Value, end: Value) -> Self {
214        self.qb = self.qb.where_between(field, start, end);
215        self
216    }
217    #[api_stable]
218
219    /// WHERE IS NULL
220    #[must_use]
221    pub fn where_null(mut self, field: impl Into<String>) -> Self {
222        self.qb = self.qb.where_null(field);
223        self
224    }
225
226    /// WHERE IS NOT NULL
227    #[must_use]
228    pub fn where_not_null(mut self, field: impl Into<String>) -> Self {
229        self.qb = self.qb.where_not_null(field);
230        self
231    }
232
233    /// ORDER BY field ASC
234    #[must_use]
235    pub fn order_by(mut self, field: impl Into<String>) -> Self {
236        self.qb = self.qb.order_by(field);
237        self
238    }
239
240    /// ORDER BY field DESC
241    #[must_use]
242    pub fn order_desc(mut self, field: impl Into<String>) -> Self {
243        self.qb = self.qb.order_desc(field);
244        self
245    }
246
247    /// GROUP BY
248    #[must_use]
249    pub fn group_by(mut self, field: impl Into<String>) -> Self {
250        self.qb = self.qb.group_by(field);
251        self
252    }
253
254    /// HAVING（参数化，审计 M-5）：`<聚合> <op> ?`，值走绑定参数
255    pub fn having(
256        mut self,
257        agg: AggExpr,
258        op: HavingOp,
259        value: Value,
260    ) -> Result<Self, crate::DbError> {
261        self.qb = self.qb.having(agg, op, value)?;
262        Ok(self)
263    }
264
265    /// LIMIT
266    #[must_use]
267    pub fn limit(mut self, limit: usize) -> Self {
268        self.qb = self.qb.limit(limit);
269        self
270    }
271
272    /// OFFSET
273    #[must_use]
274    pub fn offset(mut self, offset: usize) -> Self {
275        self.qb = self.qb.offset(offset);
276        self
277    }
278
279    /// 分页（page 从 1 开始）
280    #[must_use]
281    pub fn page(mut self, page: usize, page_size: usize) -> Self {
282        self.qb = self.qb.page(page, page_size);
283        self
284    }
285
286    /// INNER JOIN
287    #[must_use]
288    pub fn join_inner(
289        mut self,
290        table: impl Into<String>,
291        on_left: impl Into<String>,
292        on_right: impl Into<String>,
293    ) -> Self {
294        self.qb = self.qb.join_inner(table, on_left, on_right);
295        self
296    }
297
298    /// LEFT JOIN
299    #[must_use]
300    pub fn join_left(
301        mut self,
302        table: impl Into<String>,
303        on_left: impl Into<String>,
304        on_right: impl Into<String>,
305    ) -> Self {
306        self.qb = self.qb.join_left(table, on_left, on_right);
307        self
308    }
309
310    /// RIGHT JOIN
311    #[must_use]
312    pub fn join_right(
313        mut self,
314        table: impl Into<String>,
315        on_left: impl Into<String>,
316        on_right: impl Into<String>,
317    ) -> Self {
318        self.qb = self.qb.join_right(table, on_left, on_right);
319        self
320    }
321    #[api_stable]
322
323    /// 构建 SELECT SQL + 参数绑定（v5.0.0 统一签名）
324    pub fn build_select(&self) -> (String, Vec<Value>) {
325        self.qb.build_select()
326    }
327    #[api_stable]
328
329    /// 构建 INSERT SQL + 参数绑定（v5.0.0 统一签名）
330    pub fn build_insert(&self, data: &HashMap<String, Value>) -> (String, Vec<Value>) {
331        self.qb.build_insert(data)
332    }
333    #[api_stable]
334
335    /// 构建 UPDATE SQL + 参数绑定（v5.0.0 统一签名）
336    pub fn build_update(&self, data: &HashMap<String, Value>) -> (String, Vec<Value>) {
337        self.qb.build_update(data)
338    }
339    #[api_stable]
340
341    /// 构建 DELETE SQL + 参数绑定（v5.0.0 统一签名）
342    pub fn build_delete(&self) -> (String, Vec<Value>) {
343        self.qb.build_delete()
344    }
345    #[api_stable]
346
347    /// 生成纯 SELECT SQL（参数内联渲染，仅用于日志/调试）
348    pub fn sql(&self) -> String {
349        self.qb.sql()
350    }
351
352    /// 生成纯 INSERT SQL（参数内联渲染）
353    pub fn sql_insert(&self, data: &HashMap<String, Value>) -> String {
354        self.qb.sql_insert(data)
355    }
356
357    /// 生成纯 UPDATE SQL（参数内联渲染）
358    pub fn sql_update(&self, data: &HashMap<String, Value>) -> String {
359        self.qb.sql_update(data)
360    }
361
362    /// 生成纯 DELETE SQL（参数内联渲染）
363    pub fn sql_delete(&self) -> String {
364        self.qb.sql_delete()
365    }
366
367    /// 构建 COUNT SQL
368    pub fn build_count(&self) -> String {
369        self.qb.build_count()
370    }
371
372    /// 构建 EXISTS SQL
373    pub fn build_exists(&self) -> String {
374        self.qb.build_exists()
375    }
376
377    /// 构建 MAX SQL
378    pub fn build_max(&self, field: &str) -> String {
379        self.qb.build_max(field)
380    }
381
382    /// 构建 MIN SQL
383    pub fn build_min(&self, field: &str) -> String {
384        self.qb.build_min(field)
385    }
386
387    /// 构建 SUM SQL
388    pub fn build_sum(&self, field: &str) -> String {
389        self.qb.build_sum(field)
390    }
391
392    /// 构建 AVG SQL
393    pub fn build_avg(&self, field: &str) -> String {
394        self.qb.build_avg(field)
395    }
396}
397
398#[cfg(test)]
399#[allow(deprecated)]
400mod tests {
401    use super::*;
402    use crate::db_type::DbType;
403    use crate::dialect::get_dialect;
404
405    fn mysql() -> Box<dyn Dialect> {
406        get_dialect(DbType::MySQL).expect("MySQL dialect")
407    }
408
409    fn pg() -> Box<dyn Dialect> {
410        get_dialect(DbType::PostgreSQL).expect("PG dialect")
411    }
412
413    #[test]
414    fn db_name_basic_select() {
415        let sql = Db::new(mysql()).name("users").sql();
416        assert_eq!(sql, "SELECT * FROM `users`");
417    }
418
419    #[test]
420    fn db_name_with_where_and_limit() {
421        let sql = Db::new(mysql())
422            .name("users")
423            .where_gt("age", Value::I64(18))
424            .order_desc("id")
425            .limit(10)
426            .sql();
427        assert!(sql.contains("SELECT * FROM `users`"));
428        assert!(sql.contains("WHERE `age` > 18"));
429        assert!(sql.contains("ORDER BY `id` DESC"));
430        assert!(sql.contains("LIMIT 10"));
431    }
432
433    #[test]
434    fn db_name_insert() {
435        let mut data = HashMap::new();
436        data.insert("name".to_string(), Value::String("Alice".to_string()));
437        data.insert("age".to_string(), Value::I64(30));
438        let sql = Db::new(mysql()).name("users").sql_insert(&data);
439        assert!(sql.starts_with("INSERT INTO `users`"));
440        assert!(sql.contains("`name`"));
441        assert!(sql.contains("`age`"));
442        assert!(sql.contains("'Alice'"));
443        assert!(sql.contains("30"));
444    }
445
446    #[test]
447    fn db_name_update_with_where() {
448        let mut data = HashMap::new();
449        data.insert("name".to_string(), Value::String("Bob".to_string()));
450        let sql = Db::new(mysql())
451            .name("users")
452            .where_eq("id", Value::I64(1))
453            .sql_update(&data);
454        assert!(sql.starts_with("UPDATE `users` SET"));
455        assert!(sql.contains("`name` = 'Bob'"));
456        assert!(sql.contains("WHERE `id` = 1"));
457    }
458
459    #[test]
460    fn db_name_delete_with_where() {
461        let sql = Db::new(mysql())
462            .name("users")
463            .where_eq("id", Value::I64(1))
464            .sql_delete();
465        assert!(sql.contains("DELETE FROM `users` WHERE `id` = 1"));
466    }
467
468    #[test]
469    fn db_name_count() {
470        let sql = Db::new(mysql())
471            .name("users")
472            .where_gt("age", Value::I64(18))
473            .build_count();
474        assert!(sql.contains("SELECT COUNT(*)"));
475        assert!(sql.contains("FROM `users`"));
476        assert!(sql.contains("WHERE `age` > 18"));
477    }
478
479    #[test]
480    fn db_name_with_in_clause() {
481        let sql = Db::new(mysql())
482            .name("users")
483            .where_in("id", vec![Value::I64(1), Value::I64(2), Value::I64(3)])
484            .sql();
485        assert!(sql.contains("WHERE `id` IN (1, 2, 3)"));
486    }
487
488    #[test]
489    fn db_name_with_between() {
490        let sql = Db::new(mysql())
491            .name("orders")
492            .where_between("amount", Value::I64(100), Value::I64(1000))
493            .sql();
494        assert!(sql.contains("`amount` BETWEEN 100 AND 1000"));
495    }
496
497    #[test]
498    fn db_name_pg_dialect() {
499        let sql = Db::new(pg()).name("users").sql();
500        assert_eq!(sql, "SELECT * FROM \"users\"");
501    }
502
503    #[test]
504    fn db_name_join_inner() {
505        let sql = Db::new(mysql())
506            .name("orders")
507            .join_inner("users", "orders.user_id", "users.id")
508            .sql();
509        assert!(sql.contains("INNER JOIN `users` ON `orders.user_id` = `users.id`"));
510    }
511
512    #[test]
513    fn db_name_pagination() {
514        let sql = Db::new(mysql()).name("users").page(3, 20).sql();
515        // 第 3 页，每页 20 条 → LIMIT 20 OFFSET 40
516        assert!(sql.contains("LIMIT 20"));
517        assert!(sql.contains("OFFSET 40"));
518    }
519
520    #[test]
521    fn db_name_aggregate_functions() {
522        let db = Db::new(mysql())
523            .name("orders")
524            .where_eq("status", Value::String("paid".into()));
525        assert!(db.build_sum("amount").contains("SUM(`amount`)"));
526        assert!(db.build_max("amount").contains("MAX(`amount`)"));
527        assert!(db.build_min("amount").contains("MIN(`amount`)"));
528        assert!(db.build_avg("amount").contains("AVG(`amount`)"));
529        assert!(db.build_exists().contains("SELECT EXISTS("));
530    }
531
532    #[test]
533    fn db_name_chained_or_where() {
534        let sql = Db::new(mysql())
535            .name("users")
536            .where_lt("age", Value::I64(18))
537            .or_where_gt("age", Value::I64(65))
538            .sql();
539        assert!(sql.contains("WHERE (`age` < 18 OR `age` > 65)"));
540    }
541
542    #[test]
543    fn db_name_group_having() {
544        let sql = Db::new(mysql())
545            .name("orders")
546            .select_expr(vec!["user_id", "COUNT(*) as cnt"])
547            .group_by("user_id")
548            .having(AggExpr::CountStar, HavingOp::Gt, Value::I64(5))
549            .expect("valid aggregate")
550            .sql();
551        assert!(sql.contains("GROUP BY `user_id`"));
552        assert!(sql.contains("HAVING COUNT(*) > 5"));
553    }
554}
sz_orm_core/quick_query.rs

sz_orm_core/
quick_query.rs
