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

sz_orm_core/
quick_query.rs
