easy_sqlx_core/sql/dialects/
condition.rs

1use crate::sql::utils::{pair::Pair, quote::Quoter};
2use chrono::NaiveDateTime;
3use sqlx::{Database, FromRow};
4
5#[derive(PartialEq, Debug, Clone)]
6pub enum Operator {
7    Eq,        // =
8    Neq,       // <>
9    Gt,        // >
10    Ge,        // >=
11    Lt,        // <
12    Le,        // <=
13    In,        // in
14    NotIn,     // not in
15    Like,      // like
16    IsNull,    // is null
17    IsNotNull, // is not null
18}
19
20impl ToString for Operator {
21    fn to_string(&self) -> String {
22        match self {
23            Operator::Eq => "eq".to_string(),
24            Operator::Neq => "neq".to_string(),
25            Operator::Gt => "gt".to_string(),
26            Operator::Ge => "ge".to_string(),
27            Operator::Lt => "lt".to_string(),
28            Operator::Le => "le".to_string(),
29            Operator::In => "in".to_string(),
30            Operator::NotIn => "not_in".to_string(),
31            Operator::Like => "like".to_string(),
32            Operator::IsNull => "is_null".to_string(),
33            Operator::IsNotNull => "is_not_null".to_string(),
34        }
35    }
36}
37
38impl Operator {
39    pub fn resolve(name: String) -> Self {
40        match name.as_str() {
41            "eq" => Self::Eq,
42            "neq" => Self::Neq,
43            "gt" => Self::Gt,
44            "ge" => Self::Ge,
45            "lt" => Self::Lt,
46            "le" => Self::Le,
47            "in" => Self::In,
48            "not_in" => Self::NotIn,
49            "like" => Self::Like,
50            "is_null" => Self::IsNull,
51            "is_not_null" => Self::IsNotNull,
52            _ => Self::Eq,
53        }
54    }
55
56    pub fn is_no_param(&self) -> bool {
57        match self {
58            Self::IsNull | Self::IsNotNull => true,
59            _ => false,
60        }
61    }
62    // pub fn name(&self) -> String {
63    //     match self {
64    //         Operator::Eq => "Eq".to_string(),
65    //         Operator::Neq => "Neq".to_string(),
66    //         Operator::Gt => "Gt".to_string(),
67    //         Operator::Ge => "Ge".to_string(),
68    //         Operator::Lt => "Lt".to_string(),
69    //         Operator::Le => "Le".to_string(),
70    //         Operator::In => "In".to_string(),
71    //         Operator::Like => "Like".to_string(),
72    //         Operator::Is => "Is".to_string(),
73    //         Operator::IsNot => "IsNot".to_string(),
74    //     }
75    // }
76    pub fn sql(&self) -> String {
77        match self {
78            Operator::Eq => "=".to_string(),
79            Operator::Neq => "<>".to_string(),
80            Operator::Gt => ">".to_string(),
81            Operator::Ge => ">=".to_string(),
82            Operator::Lt => "<".to_string(),
83            Operator::Le => "<=".to_string(),
84            Operator::In => "in".to_string(),
85            Operator::NotIn => "not in".to_string(),
86            Operator::Like => "like".to_string(),
87            Operator::IsNull => "is null".to_string(),
88            Operator::IsNotNull => "is not null".to_string(),
89        }
90    }
91}
92
93#[derive(Debug, Clone)]
94pub enum Condition {
95    Condition(Pair, Operator),
96    And(Box<Condition>, Box<Condition>),
97    Or(Box<Condition>, Box<Condition>),
98}
99
100impl Condition {
101    pub fn is_condition(&self) -> bool {
102        match self {
103            Condition::Condition(_, _) => true,
104            Condition::And(_, _) => false,
105            Condition::Or(_, _) => false,
106        }
107    }
108
109    pub fn is_and(&self) -> bool {
110        match self {
111            Condition::Condition(_, _) => false,
112            Condition::And(_, _) => true,
113            Condition::Or(_, _) => false,
114        }
115    }
116
117    pub fn is_or(&self) -> bool {
118        match self {
119            Condition::Condition(_, _) => false,
120            Condition::And(_, _) => false,
121            Condition::Or(_, _) => true,
122        }
123    }
124}
125
126impl Condition {
127    pub fn add_param_value(&self, str: &mut String) {
128        match self {
129            Condition::Condition(p, o) => {
130                if !o.is_no_param() {
131                    p.value.add_param_value(str)
132                }
133            }
134            Condition::And(left, right) => {
135                left.add_param_value(str);
136                right.add_param_value(str);
137            }
138            Condition::Or(left, right) => {
139                left.add_param_value(str);
140                right.add_param_value(str);
141            }
142        }
143    }
144    pub fn bind_to_query<'a, DB: Database>(
145        &self,
146        query: sqlx::query::Query<'a, DB, DB::Arguments<'a>>,
147    ) -> sqlx::query::Query<'a, DB, DB::Arguments<'a>>
148    where
149        Option<bool>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
150        bool: sqlx::Type<DB>,
151        Option<i16>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
152        i16: sqlx::Type<DB>,
153        Option<i32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
154        i32: sqlx::Type<DB>,
155        Option<i64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
156        i64: sqlx::Type<DB>,
157        Option<f64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
158        f64: sqlx::Type<DB>,
159        Option<f32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
160        f32: sqlx::Type<DB>,
161        Option<NaiveDateTime>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
162        NaiveDateTime: sqlx::Type<DB>,
163        Option<String>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
164        String: sqlx::Type<DB>,
165        Option<Vec<u8>>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
166        Vec<u8>: sqlx::Type<DB>,
167    {
168        match self {
169            Condition::Condition(p, o) => {
170                if o.is_no_param() {
171                    query
172                } else {
173                    p.value.bind_to_query(query)
174                }
175            }
176            Condition::And(left, right) => {
177                let qry = left.bind_to_query(query);
178                right.bind_to_query(qry)
179            }
180            Condition::Or(left, right) => {
181                let qry = left.bind_to_query(query);
182                right.bind_to_query(qry)
183            }
184        }
185    }
186
187    pub fn bind_to_query_as<'a, O, DB: Database>(
188        &self,
189        query: sqlx::query::QueryAs<'a, DB, O, DB::Arguments<'a>>,
190    ) -> sqlx::query::QueryAs<'a, DB, O, DB::Arguments<'a>>
191    where
192        Option<bool>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
193        bool: sqlx::Type<DB>,
194        Option<i16>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
195        i16: sqlx::Type<DB>,
196        Option<i32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
197        i32: sqlx::Type<DB>,
198        Option<i64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
199        i64: sqlx::Type<DB>,
200        Option<f64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
201        f64: sqlx::Type<DB>,
202        Option<f32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
203        f32: sqlx::Type<DB>,
204        Option<NaiveDateTime>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
205        NaiveDateTime: sqlx::Type<DB>,
206        Option<String>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
207        String: sqlx::Type<DB>,
208        Option<Vec<u8>>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
209        Vec<u8>: sqlx::Type<DB>,
210    {
211        match self {
212            Condition::Condition(p, o) => {
213                if o.is_no_param() {
214                    query
215                } else {
216                    p.value.bind_to_query_as(query)
217                }
218            }
219            Condition::And(left, right) => {
220                let qry = left.bind_to_query_as(query);
221                right.bind_to_query_as(qry)
222            }
223            Condition::Or(left, right) => {
224                let qry = left.bind_to_query_as(query);
225                right.bind_to_query_as(qry)
226            }
227        }
228    }
229
230    pub fn bind_to_query_scalar<'a, O, DB: Database>(
231        &self,
232        query: sqlx::query::QueryScalar<'a, DB, O, DB::Arguments<'a>>,
233    ) -> sqlx::query::QueryScalar<'a, DB, O, DB::Arguments<'a>>
234    where
235        Option<bool>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
236        bool: sqlx::Type<DB>,
237        Option<i16>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
238        i16: sqlx::Type<DB>,
239        Option<i32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
240        i32: sqlx::Type<DB>,
241        Option<i64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
242        i64: sqlx::Type<DB>,
243        Option<f64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
244        f64: sqlx::Type<DB>,
245        Option<f32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
246        f32: sqlx::Type<DB>,
247        Option<NaiveDateTime>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
248        NaiveDateTime: sqlx::Type<DB>,
249        Option<String>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
250        String: sqlx::Type<DB>,
251        Option<Vec<u8>>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
252        Vec<u8>: sqlx::Type<DB>,
253    {
254        match self {
255            Condition::Condition(p, o) => {
256                if o.is_no_param() {
257                    query
258                } else {
259                    p.value.bind_to_query_scalar(query)
260                }
261            }
262            Condition::And(left, right) => {
263                let qry = left.bind_to_query_scalar(query);
264                right.bind_to_query_scalar(qry)
265            }
266            Condition::Or(left, right) => {
267                let qry = left.bind_to_query_scalar(query);
268                right.bind_to_query_scalar(qry)
269            }
270        }
271    }
272
273    pub fn sql(&self, param_index: usize, quoter: &Quoter) -> (String, usize) {
274        match self {
275            Condition::Condition(p, o) => {
276                let field = p.name.clone();
277                let op = o.sql();
278
279                if o.is_no_param() {
280                    // 不需要参数
281                    return (format!("{} {op}", quoter.quote(&field)), param_index);
282                }
283
284                if *o == Operator::In || *o == Operator::NotIn {
285                    // in 操作
286                    // vec![0; p.value.get_len()].iter().map(|n| );
287                    let mut params = vec![];
288                    for n in 0..p.value.len() {
289                        params.push(format!("${}", param_index + n));
290                    }
291
292                    return (
293                        format!("{} {op} ({})", quoter.quote(&field), params.join(",")),
294                        param_index + p.value.len(),
295                    );
296                }
297                (
298                    format!("{} {op} ${param_index}", quoter.quote(&field)),
299                    param_index + 1,
300                )
301            }
302            Condition::And(left, right) => {
303                let (left_cond, index) = left.sql(param_index, quoter);
304                let (right_cond, index) = right.sql(index, quoter);
305                if left.is_or() {
306                    if right.is_or() {
307                        (format!("({left_cond}) and ({right_cond})"), index)
308                    } else {
309                        (format!("({left_cond}) and {right_cond}"), index)
310                    }
311                } else {
312                    if right.is_or() {
313                        (format!("{left_cond} and ({right_cond})"), index)
314                    } else {
315                        (format!("{left_cond} and {right_cond}"), index)
316                    }
317                }
318            }
319            Condition::Or(left, right) => {
320                let (left_cond, index) = left.sql(param_index, quoter);
321                let (right_cond, index) = right.sql(index, quoter);
322                if left.is_and() {
323                    if right.is_and() {
324                        (format!("({left_cond}) or ({right_cond})"), index)
325                    } else {
326                        (format!("({left_cond}) or {right_cond}"), index)
327                    }
328                } else {
329                    if right.is_and() {
330                        (format!("{left_cond} or ({right_cond})"), index)
331                    } else {
332                        (format!("{left_cond} or {right_cond}"), index)
333                    }
334                }
335            }
336        }
337    }
338}
339
340pub trait WhereAppend<T> {
341    fn and(self, cond: T) -> Self;
342    fn or(self, cond: T) -> Self;
343}
344
345#[derive(Default, Debug, Clone)]
346pub struct Where {
347    cond: Option<Box<Condition>>,
348    // params: Vec<Value>,
349}
350
351impl WhereAppend<Condition> for Where {
352    fn and(mut self, cond: Condition) -> Self {
353        if let Some(c) = self.cond {
354            self.cond = Some(Box::new(Condition::And(c, Box::new(cond))));
355        } else {
356            self.cond = Some(Box::new(cond));
357        }
358        self
359    }
360
361    fn or(mut self, cond: Condition) -> Self {
362        if let Some(c) = self.cond {
363            self.cond = Some(Box::new(Condition::Or(c, Box::new(cond))));
364        } else {
365            self.cond = Some(Box::new(cond));
366        }
367        self
368    }
369}
370
371impl WhereAppend<Where> for Where {
372    fn and(mut self, w: Where) -> Self {
373        if let Some(wcond) = w.cond {
374            if let Some(c) = self.cond {
375                self.cond = Some(Box::new(Condition::And(c, wcond)));
376            } else {
377                self.cond = Some(wcond);
378            }
379        }
380        self
381    }
382
383    fn or(mut self, w: Where) -> Self {
384        if let Some(wcond) = w.cond {
385            if let Some(c) = self.cond {
386                self.cond = Some(Box::new(Condition::Or(c, wcond)));
387            } else {
388                self.cond = Some(wcond);
389            }
390        }
391        self
392    }
393}
394
395impl From<Condition> for Where {
396    fn from(cond: Condition) -> Self {
397        Self {
398            cond: Some(Box::new(cond)),
399        }
400    }
401}
402
403impl Where {
404    pub fn new(cond: Condition) -> Self {
405        Self {
406            cond: Some(Box::new(cond)),
407        }
408    }
409
410    pub fn list_params(&self) -> String {
411        let mut params = "".to_string();
412        if let Some(c) = &self.cond {
413            c.add_param_value(&mut params);
414        }
415        params
416    }
417
418    pub fn bind_to_query<'a, DB: Database>(
419        &self,
420        query: sqlx::query::Query<'a, DB, DB::Arguments<'a>>,
421    ) -> sqlx::query::Query<'a, DB, DB::Arguments<'a>>
422    where
423        Option<bool>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
424        bool: sqlx::Type<DB>,
425        Option<i16>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
426        i16: sqlx::Type<DB>,
427        Option<i32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
428        i32: sqlx::Type<DB>,
429        Option<i64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
430        i64: sqlx::Type<DB>,
431        Option<f64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
432        f64: sqlx::Type<DB>,
433        Option<f32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
434        f32: sqlx::Type<DB>,
435        Option<NaiveDateTime>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
436        NaiveDateTime: sqlx::Type<DB>,
437        Option<String>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
438        String: sqlx::Type<DB>,
439        Option<Vec<u8>>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
440        Vec<u8>: sqlx::Type<DB>,
441    {
442        if let Some(c) = &self.cond {
443            return c.bind_to_query(query);
444        }
445        query
446    }
447
448    pub fn bind_to_query_as<'a, O, DB: Database>(
449        &self,
450        query: sqlx::query::QueryAs<'a, DB, O, DB::Arguments<'a>>,
451    ) -> sqlx::query::QueryAs<'a, DB, O, DB::Arguments<'a>>
452    where
453        O: std::marker::Send,
454        O: Unpin,
455        Option<bool>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
456        bool: sqlx::Type<DB>,
457        Option<i16>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
458        i16: sqlx::Type<DB>,
459        Option<i32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
460        i32: sqlx::Type<DB>,
461        Option<i64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
462        i64: sqlx::Type<DB>,
463        Option<f64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
464        f64: sqlx::Type<DB>,
465        Option<f32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
466        f32: sqlx::Type<DB>,
467        Option<NaiveDateTime>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
468        NaiveDateTime: sqlx::Type<DB>,
469        Option<String>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
470        String: sqlx::Type<DB>,
471        Option<Vec<u8>>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
472        Vec<u8>: sqlx::Type<DB>,
473    {
474        if let Some(c) = &self.cond {
475            return c.bind_to_query_as(query);
476        }
477        query
478    }
479
480    pub fn bind_to_query_scalar<'a, O, DB: Database>(
481        &self,
482        query: sqlx::query::QueryScalar<'a, DB, O, DB::Arguments<'a>>,
483    ) -> sqlx::query::QueryScalar<'a, DB, O, DB::Arguments<'a>>
484    where
485        // O: std::marker::Send,
486        // O: Unpin,
487        (O,): for<'r> FromRow<'r, DB::Row>,
488        Option<bool>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
489        bool: sqlx::Type<DB>,
490        Option<i16>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
491        i16: sqlx::Type<DB>,
492        Option<i32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
493        i32: sqlx::Type<DB>,
494        Option<i64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
495        i64: sqlx::Type<DB>,
496        Option<f64>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
497        f64: sqlx::Type<DB>,
498        Option<f32>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
499        f32: sqlx::Type<DB>,
500        Option<NaiveDateTime>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
501        NaiveDateTime: sqlx::Type<DB>,
502        Option<String>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
503        String: sqlx::Type<DB>,
504        Option<Vec<u8>>: sqlx::Encode<'a, DB> + sqlx::Decode<'a, DB>,
505        Vec<u8>: sqlx::Type<DB>,
506    {
507        if let Some(c) = &self.cond {
508            return c.bind_to_query_scalar(query);
509        }
510        query
511    }
512
513    pub fn sql(&self, param_index: usize, quoter: &Quoter) -> (String, usize) {
514        if let Some(cond) = &self.cond {
515            cond.sql(param_index, quoter)
516        } else {
517            ("".to_string(), param_index)
518        }
519    }
520}