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

sz_orm_query/
quick_query.rs
