yang-db 0.2.1

个人使用数据库操作
Documentation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
//! 链式构建方法:字段选择、条件、JOIN、分组、排序、分页与 UNION。

use crate::mysql::condition::Condition;
use crate::mysql::field::FieldType;

use super::predicate::predicate_condition;
use super::{QueryBuilder, UnionOperator};

impl<'a> QueryBuilder<'a> {
    /// 选择字段
    ///
    /// # 安全
    ///
    /// 本方法按设计接受 SQL 表达式(如 `COUNT(*) AS c`、`users.id`、`YEAR(d)`),
    /// 故**不**对入参做标识符校验/转义——属可信输入。若需把外部输入当列名,请先用
    /// [`is_valid_identifier`](crate::mysql::is_valid_identifier) /
    /// [`quote_identifier`](crate::mysql::quote_identifier) 校验或转义后再传入。
    /// 写入路径(INSERT/UPDATE/UPSERT 的列名与各 DML 表名)已在生成层强制 quote。
    pub fn field(mut self, field: &crate::FieldRef) -> Self {
        self.fields.push(field.mysql_quoted().to_string());
        self
    }

    /// 添加受控聚合表达式。
    pub fn expr(mut self, expression: crate::SelectExpr) -> Self {
        self.fields.push(expression.mysql_sql());
        self
    }

    /// 在 SELECT 投影中追加受控服务端标量表达式,并以受控字段引用作为输出别名。
    ///
    /// 渲染形如 ``UNIX_TIMESTAMP() AS `now```;表达式片段是 [`crate::SqlExpr`]
    /// 白名单内的固定文本,别名经标识符校验转义,动态部分(如偏移秒数)以绑定
    /// 参数传递。可与 [`crate::mysql::Transaction::select_for_update`] 等行锁查询
    /// 组合,覆盖「读取行的同时取服务端当前时间」的场景。
    pub fn select_expr(mut self, expression: crate::SqlExpr, alias: &crate::FieldRef) -> Self {
        self.select_exprs
            .push((expression, alias.as_str().to_string()));
        self
    }

    /// 把字段的写入值设为受控服务端表达式,UPDATE 的 SET 子句与 INSERT 的
    /// VALUES 子句同样生效(如 `used_at = UNIX_TIMESTAMP()`)。
    ///
    /// 与 JSON 数据列混用时表达式列追加在其后;表达式只能由 [`crate::SqlExpr`]
    /// 的白名单构造函数创建,动态部分一律走绑定参数,调用方无法注入 SQL 片段。
    /// `insert`/`update` 时若 JSON 数据为空对象但存在表达式赋值,语句仍然合法。
    ///
    /// **生效范围**:仅 `insert`/`insert_returning_id`/`update` 消费表达式赋值;
    /// `upsert`/`insert_batch`/`insert_batch_with_size`/`update_batch` 在存在表达式
    /// 赋值时返回 [`crate::DbError::InvalidArgument`],不做静默丢弃。
    pub fn set_expr(mut self, field: &crate::FieldRef, expression: crate::SqlExpr) -> Self {
        self.expr_assignments
            .push((field.as_str().to_string(), expression));
        self
    }

    /// 选择多个字段
    pub fn fields(mut self, fields: &[&crate::FieldRef]) -> Self {
        for field in fields {
            self.fields.push(field.mysql_quoted().to_string());
        }
        self
    }

    /// 标记字段为 JSON 类型
    pub fn json(mut self, field: &crate::FieldRef) -> Self {
        self.field_types
            .insert(field.as_str().to_string(), FieldType::Json);
        self
    }

    /// 标记字段为 DATETIME 类型
    pub fn datetime(mut self, field: &crate::FieldRef) -> Self {
        self.field_types
            .insert(field.as_str().to_string(), FieldType::DateTime);
        self
    }

    /// 标记字段为 TIMESTAMP 类型
    pub fn timestamp(mut self, field: &crate::FieldRef) -> Self {
        self.field_types
            .insert(field.as_str().to_string(), FieldType::Timestamp);
        self
    }

    /// 标记字段为 DECIMAL 类型
    pub fn decimal(mut self, field: &crate::FieldRef) -> Self {
        self.field_types
            .insert(field.as_str().to_string(), FieldType::Decimal);
        self
    }

    /// 标记字段为 BLOB 类型
    pub fn blob(mut self, field: &crate::FieldRef) -> Self {
        self.field_types
            .insert(field.as_str().to_string(), FieldType::Blob);
        self
    }

    /// 标记字段为 TEXT 类型
    pub fn text(mut self, field: &crate::FieldRef) -> Self {
        self.field_types
            .insert(field.as_str().to_string(), FieldType::Text);
        self
    }

    /// 去重
    pub fn distinct(mut self) -> Self {
        self.distinct = true;
        self
    }

    /// 使用 UNION 组合一个显式投影且输出列数相同的查询。
    pub fn union(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
        self.add_union(other, UnionOperator::Distinct)
    }

    /// 使用 UNION ALL 组合一个显式投影且输出列数相同的查询。
    pub fn union_all(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
        self.add_union(other, UnionOperator::All)
    }

    fn add_union(
        mut self,
        other: QueryBuilder<'a>,
        operator: UnionOperator,
    ) -> Result<Self, crate::error::DbError> {
        if self.fields.is_empty() || other.fields.is_empty() {
            return Err(crate::error::DbError::InvalidArgument(
                "UNION 各分支必须显式声明输出字段,不能使用未知列数的 *".to_string(),
            ));
        }
        if self.fields.len() != other.fields.len() {
            return Err(crate::error::DbError::InvalidArgument(format!(
                "UNION 输出列数不一致:左侧 {} 列,右侧 {} 列",
                self.fields.len(),
                other.fields.len()
            )));
        }
        self.unions.push((operator, Box::new(other)));
        Ok(self)
    }

    /// 添加 AND 条件
    ///
    /// 遇到不支持的操作符时返回 `Err(DbError::UnsupportedOperator)`。
    /// 支持的操作符:`=`、`!=`、`>`、`<`、`>=`、`<=`、`like`、`LIKE`。
    ///
    /// # 参数
    /// - `field`: 字段名
    /// - `op`: 比较操作符
    /// - `value`: 比较值
    ///
    /// # 返回
    /// - `Ok(Self)`: 操作符合法,条件已添加
    /// - `Err(DbError::UnsupportedOperator)`: 操作符不在支持集合中,或 LIKE 收到非字符串模式
    pub fn where_and<V>(
        mut self,
        field: &crate::FieldRef,
        op: crate::CompareOp,
        value: V,
    ) -> Result<Self, crate::error::DbError>
    where
        V: Into<crate::mysql::condition::SqlValue>,
    {
        use crate::mysql::condition::{Condition, SqlValue};

        let field = field.as_str().to_string();
        let value = value.into();
        let condition = match op {
            crate::CompareOp::Eq => Condition::Eq(field, value),
            crate::CompareOp::Ne => Condition::Ne(field, value),
            crate::CompareOp::Gt => Condition::Gt(field, value),
            crate::CompareOp::Lt => Condition::Lt(field, value),
            crate::CompareOp::Gte => Condition::Gte(field, value),
            crate::CompareOp::Lte => Condition::Lte(field, value),
            crate::CompareOp::Like => match value {
                SqlValue::String(pattern) => Condition::Like(field, pattern),
                // 不把值 Debug 回显,避免把调用方数据带进错误日志/响应
                _ => {
                    return Err(crate::error::DbError::UnsupportedOperator(
                        "LIKE 仅支持字符串模式值".to_string(),
                    ))
                }
            },
        };
        self.conditions.push(condition);
        Ok(self)
    }

    /// 添加 OR 条件
    ///
    /// 遇到不支持的操作符时返回 `Err(DbError::UnsupportedOperator)`。
    /// 支持的操作符:`=`、`!=`、`>`、`<`、`>=`、`<=`、`like`、`LIKE`。
    ///
    /// # 参数
    /// - `field`: 字段名
    /// - `op`: 比较操作符
    /// - `value`: 比较值
    ///
    /// # 返回
    /// - `Ok(Self)`: 操作符合法,条件已添加
    /// - `Err(DbError::UnsupportedOperator)`: 操作符不在支持集合中,或 LIKE 收到非字符串模式
    pub fn where_or<V>(
        mut self,
        field: &crate::FieldRef,
        op: crate::CompareOp,
        value: V,
    ) -> Result<Self, crate::error::DbError>
    where
        V: Into<crate::mysql::condition::SqlValue>,
    {
        use crate::mysql::condition::{Condition, SqlValue};

        let field = field.as_str().to_string();
        let value = value.into();
        let condition = match op {
            crate::CompareOp::Eq => Condition::Eq(field, value),
            crate::CompareOp::Ne => Condition::Ne(field, value),
            crate::CompareOp::Gt => Condition::Gt(field, value),
            crate::CompareOp::Lt => Condition::Lt(field, value),
            crate::CompareOp::Gte => Condition::Gte(field, value),
            crate::CompareOp::Lte => Condition::Lte(field, value),
            crate::CompareOp::Like => match value {
                SqlValue::String(pattern) => Condition::Like(field, pattern),
                _ => {
                    return Err(crate::error::DbError::UnsupportedOperator(
                        "LIKE 仅支持字符串模式值".to_string(),
                    ))
                }
            },
        };

        // 如果已有条件,将新条件与现有条件用 OR 组合
        if !self.conditions.is_empty() {
            let mut existing = std::mem::take(&mut self.conditions);
            self.conditions.push(Condition::Or(vec![
                if existing.len() == 1 {
                    // remove(0) 在 len == 1 时安全,不会 panic
                    existing.remove(0)
                } else {
                    Condition::And(existing)
                },
                condition,
            ]));
        } else {
            self.conditions.push(condition);
        }

        Ok(self)
    }

    /// 添加 IN 条件
    pub fn where_in<V>(mut self, field: &crate::FieldRef, values: Vec<V>) -> Self
    where
        V: Into<crate::mysql::condition::SqlValue>,
    {
        use crate::mysql::condition::Condition;

        let sql_values: Vec<_> = values.into_iter().map(|v| v.into()).collect();
        self.conditions
            .push(Condition::In(field.as_str().to_string(), sql_values));
        self
    }

    /// 添加受控 EXISTS 子查询。
    pub fn where_exists(mut self, subquery: crate::mysql::Subquery) -> Self {
        self.conditions.push(Condition::Exists(Box::new(subquery)));
        self
    }

    /// 添加受控 NOT EXISTS 子查询。
    pub fn where_not_exists(mut self, subquery: crate::mysql::Subquery) -> Self {
        self.conditions
            .push(Condition::NotExists(Box::new(subquery)));
        self
    }

    /// 添加受控 IN 子查询,外层字段必须是合法的单段或两段标识符。
    pub fn where_in_subquery(
        mut self,
        field: &crate::FieldRef,
        subquery: crate::mysql::Subquery,
    ) -> Self {
        self.conditions.push(Condition::InSubquery(
            field.as_str().to_string(),
            Box::new(subquery),
        ));
        self
    }

    /// 添加 BETWEEN 条件
    pub fn where_between<V>(mut self, field: &crate::FieldRef, start: V, end: V) -> Self
    where
        V: Into<crate::mysql::condition::SqlValue>,
    {
        use crate::mysql::condition::Condition;

        self.conditions.push(Condition::Between(
            field.as_str().to_string(),
            start.into(),
            end.into(),
        ));
        self
    }

    /// 添加 IS NULL 条件
    ///
    /// 生成 `field IS NULL` 子句,用于查询字段值为 NULL 的记录。
    ///
    /// # 参数
    /// - `field`: 字段名
    ///
    /// # 示例
    /// ```no_run
    /// # use yang_db::Database;
    /// # async fn example() -> Result<(), yang_db::DbError> {
    /// # let db = Database::connect("mysql://root:password@localhost/test").await?;
    /// # #[derive(serde::Deserialize, sqlx::FromRow)] struct User { id: i64 }
    /// let users = db.table(yang_db::table!("users"))
    ///     .where_null(yang_db::field!("deleted_at"))
    ///     .select::<User>()
    ///     .await?;
    /// # Ok(())
    /// # }
    /// ```
    pub fn where_null(mut self, field: &crate::FieldRef) -> Self {
        self.conditions
            .push(Condition::IsNull(field.as_str().to_string()));
        self
    }

    /// 添加 IS NOT NULL 条件
    ///
    /// 生成 `field IS NOT NULL` 子句,用于查询字段值不为 NULL 的记录。
    ///
    /// # 参数
    /// - `field`: 字段名
    pub fn where_not_null(mut self, field: &crate::FieldRef) -> Self {
        self.conditions
            .push(Condition::IsNotNull(field.as_str().to_string()));
        self
    }

    /// 应用由上层权限模块编译的受控布尔查询树。
    pub fn where_predicate(
        mut self,
        predicate: &crate::Predicate,
    ) -> Result<Self, crate::error::DbError> {
        self.conditions.push(predicate_condition(predicate)?);
        Ok(self)
    }

    /// 添加字段与受控服务端表达式的 AND 比较条件(如 `expires_at > UNIX_TIMESTAMP()`)。
    ///
    /// `op` 仅支持六个比较操作符;传入 [`crate::CompareOp::Like`] 时返回
    /// `Err(DbError::UnsupportedOperator)`。表达式右值由 [`crate::SqlExpr`] 白名单
    /// 构造函数创建,动态部分(如偏移秒数)以绑定参数传递。
    pub fn where_expr(
        mut self,
        field: &crate::FieldRef,
        op: crate::CompareOp,
        expression: crate::SqlExpr,
    ) -> Result<Self, crate::error::DbError> {
        use crate::mysql::condition::ComparisonOperator;

        let operator = match op {
            crate::CompareOp::Eq => ComparisonOperator::Eq,
            crate::CompareOp::Ne => ComparisonOperator::Ne,
            crate::CompareOp::Gt => ComparisonOperator::Gt,
            crate::CompareOp::Lt => ComparisonOperator::Lt,
            crate::CompareOp::Gte => ComparisonOperator::Gte,
            crate::CompareOp::Lte => ComparisonOperator::Lte,
            crate::CompareOp::Like => {
                return Err(crate::error::DbError::UnsupportedOperator(
                    "LIKE 不支持服务端表达式右值".to_string(),
                ));
            }
        };
        self.conditions.push(Condition::ColumnExprComparison(
            field.as_str().to_string(),
            operator,
            expression,
        ));
        Ok(self)
    }

    /// 添加 HAVING 条件
    ///
    /// 对 GROUP BY 分组后的结果进行过滤。必须与 `group()` 方法配合使用,
    /// 否则查询执行时返回 `MissingGroupByClause` 错误。
    /// 多次调用将以 AND 连接所有条件。
    ///
    /// # 参数
    /// - `field`: 聚合字段或聚合表达式(如 `"cnt"`)
    /// - `op`: 比较运算符("="、"!="、">"、"<"、">="、"<=")
    /// - `value`: 比较值(参数化,防 SQL 注入)
    ///
    /// # 示例
    /// ```no_run
    /// # use yang_db::Database;
    /// # async fn example() -> Result<(), yang_db::DbError> {
    /// # let db = Database::connect("mysql://root:password@localhost/test").await?;
    /// # #[derive(serde::Deserialize, sqlx::FromRow)] struct OrderSummary { user_id: i64, cnt: i64 }
    /// let result = db.table(yang_db::table!("orders"))
    ///     .field(yang_db::field!("user_id"))
    ///     .expr(yang_db::SelectExpr::count_all().alias(yang_db::field!("cnt")))
    ///     .group(yang_db::field!("user_id"))
    ///     .having_cond(yang_db::field!("cnt"), yang_db::CompareOp::Gt, 5i64)?
    ///     .select::<OrderSummary>()
    ///     .await?;
    /// # Ok(())
    /// # }
    /// ```
    pub fn having_cond<V>(
        mut self,
        field: &crate::FieldRef,
        op: crate::CompareOp,
        value: V,
    ) -> Result<Self, crate::error::DbError>
    where
        V: Into<crate::mysql::condition::SqlValue>,
    {
        let field = field.as_str().to_string();
        let value = value.into();
        let condition = match op {
            crate::CompareOp::Eq => Condition::Eq(field, value),
            crate::CompareOp::Ne => Condition::Ne(field, value),
            crate::CompareOp::Gt => Condition::Gt(field, value),
            crate::CompareOp::Lt => Condition::Lt(field, value),
            crate::CompareOp::Gte => Condition::Gte(field, value),
            crate::CompareOp::Lte => Condition::Lte(field, value),
            crate::CompareOp::Like => match value {
                crate::mysql::condition::SqlValue::String(value) => Condition::Like(field, value),
                _ => {
                    return Err(crate::error::DbError::UnsupportedOperator(
                        "LIKE 仅支持字符串模式值".to_string(),
                    ))
                }
            },
        };
        self.having_clause.push(condition);
        Ok(self)
    }

    /// 使用可信表/ON 表达式的 INNER JOIN。
    ///
    /// 外部表名/列名请先用 [`crate::mysql::identifier::quote_identifier`] /
    /// [`crate::mysql::identifier::quote_qualified`] 校验后再拼入表达式。
    pub fn join(
        mut self,
        table: &crate::TableRef,
        left: &crate::FieldRef,
        right: &crate::FieldRef,
    ) -> Self {
        use crate::mysql::field::{JoinClause, JoinType};

        self.joins.push(JoinClause {
            join_type: JoinType::Inner,
            table: table.as_str().to_string(),
            on: format!("{} = {}", left.mysql_quoted(), right.mysql_quoted()),
        });
        self
    }

    /// LEFT JOIN
    pub fn left_join(
        mut self,
        table: &crate::TableRef,
        left: &crate::FieldRef,
        right: &crate::FieldRef,
    ) -> Self {
        use crate::mysql::field::{JoinClause, JoinType};

        self.joins.push(JoinClause {
            join_type: JoinType::Left,
            table: table.as_str().to_string(),
            on: format!("{} = {}", left.mysql_quoted(), right.mysql_quoted()),
        });
        self
    }

    /// RIGHT JOIN
    pub fn right_join(
        mut self,
        table: &crate::TableRef,
        left: &crate::FieldRef,
        right: &crate::FieldRef,
    ) -> Self {
        use crate::mysql::field::{JoinClause, JoinType};

        self.joins.push(JoinClause {
            join_type: JoinType::Right,
            table: table.as_str().to_string(),
            on: format!("{} = {}", left.mysql_quoted(), right.mysql_quoted()),
        });
        self
    }

    /// 按可信 SQL 表达式排序;外部列名请先用 [`crate::mysql::identifier::quote_identifier`]
    /// 校验后再拼入表达式。
    pub fn order(mut self, field: &crate::FieldRef, order: crate::SortOrder) -> Self {
        use crate::mysql::field::OrderClause;

        self.order_by.push(OrderClause {
            field: field.mysql_quoted().to_string(),
            asc: order.is_ascending(),
        });
        self
    }

    /// 按可信 SQL 表达式分组;外部列名请先用 [`crate::mysql::identifier::quote_identifier`]
    /// 校验后再拼入表达式。
    pub fn group(mut self, field: &crate::FieldRef) -> Self {
        self.group_by.push(field.mysql_quoted().to_string());
        self
    }

    /// 限制返回数量
    pub fn limit(mut self, limit: u64) -> Self {
        self.limit = Some(limit);
        self
    }

    /// 偏移量
    pub fn offset(mut self, offset: u64) -> Self {
        self.offset = Some(offset);
        self
    }
}