sea_query/backend/
query_builder.rs

1use std::{fmt, ops::Deref};
2
3use crate::*;
4
5const QUOTE: Quote = Quote(b'"', b'"');
6
7pub trait QueryBuilder:
8    QuotedBuilder + EscapeBuilder + TableRefBuilder + OperLeftAssocDecider + PrecedenceDecider
9{
10    /// The type of placeholder the builder uses for values, and whether it is numbered.
11    fn placeholder(&self) -> (&'static str, bool) {
12        ("?", false)
13    }
14
15    /// Prefix for tuples in VALUES list (e.g. ROW for MySQL)
16    fn values_list_tuple_prefix(&self) -> &str {
17        ""
18    }
19
20    /// Translate [`InsertStatement`] into SQL statement.
21    fn prepare_insert_statement(&self, insert: &InsertStatement, sql: &mut dyn SqlWriter) {
22        if let Some(with) = &insert.with {
23            self.prepare_with_clause(with, sql);
24        }
25
26        self.prepare_insert(insert.replace, sql);
27
28        if let Some(table) = &insert.table {
29            sql.write_str(" INTO ").unwrap();
30
31            self.prepare_table_ref(table, sql);
32        }
33
34        if insert.default_values.unwrap_or_default() != 0
35            && insert.columns.is_empty()
36            && insert.source.is_none()
37        {
38            self.prepare_output(&insert.returning, sql);
39            sql.write_str(" ").unwrap();
40            let num_rows = insert.default_values.unwrap();
41            self.insert_default_values(num_rows, sql);
42        } else {
43            sql.write_str(" (").unwrap();
44            let mut cols = insert.columns.iter();
45            join_io!(
46                cols,
47                col,
48                join {
49                    sql.write_str(", ").unwrap();
50                },
51                do {
52                    self.prepare_iden(col, sql);
53                }
54            );
55
56            sql.write_str(")").unwrap();
57
58            self.prepare_output(&insert.returning, sql);
59
60            if let Some(source) = &insert.source {
61                sql.write_str(" ").unwrap();
62                match source {
63                    InsertValueSource::Values(values) => {
64                        sql.write_str("VALUES ").unwrap();
65                        let mut vals = values.iter();
66                        join_io!(
67                            vals,
68                            row,
69                            join {
70                                sql.write_str(", ").unwrap();
71                            },
72                            do {
73                                sql.write_str("(").unwrap();
74                                let mut cols = row.iter();
75                                join_io!(
76                                    cols,
77                                    col,
78                                    join {
79                                        sql.write_str(", ").unwrap();
80                                    },
81                                    do {
82                                        self.prepare_simple_expr(col, sql);
83                                    }
84                                );
85
86                                sql.write_str(")").unwrap();
87                            }
88                        );
89                    }
90                    InsertValueSource::Select(select_query) => {
91                        self.prepare_select_statement(select_query.deref(), sql);
92                    }
93                }
94            }
95        }
96
97        self.prepare_on_conflict(&insert.on_conflict, sql);
98
99        self.prepare_returning(&insert.returning, sql);
100    }
101
102    fn prepare_union_statement(
103        &self,
104        union_type: UnionType,
105        select_statement: &SelectStatement,
106        sql: &mut dyn SqlWriter,
107    ) {
108        match union_type {
109            UnionType::Intersect => sql.write_str(" INTERSECT (").unwrap(),
110            UnionType::Distinct => sql.write_str(" UNION (").unwrap(),
111            UnionType::Except => sql.write_str(" EXCEPT (").unwrap(),
112            UnionType::All => sql.write_str(" UNION ALL (").unwrap(),
113        }
114        self.prepare_select_statement(select_statement, sql);
115        sql.write_str(")").unwrap();
116    }
117
118    /// Translate [`SelectStatement`] into SQL statement.
119    fn prepare_select_statement(&self, select: &SelectStatement, sql: &mut dyn SqlWriter) {
120        if let Some(with) = &select.with {
121            self.prepare_with_clause(with, sql);
122        }
123
124        sql.write_str("SELECT ").unwrap();
125
126        if let Some(distinct) = &select.distinct {
127            self.prepare_select_distinct(distinct, sql);
128            sql.write_str(" ").unwrap();
129        }
130
131        let mut selects = select.selects.iter();
132        join_io!(
133            selects,
134            expr,
135            join {
136                sql.write_str(", ").unwrap();
137            },
138            do {
139                self.prepare_select_expr(expr, sql);
140            }
141        );
142
143        let mut from_tables = select.from.iter();
144        join_io!(
145            from_tables,
146            table_ref,
147            first {
148                sql.write_str(" FROM ").unwrap();
149            },
150            join {
151                sql.write_str(", ").unwrap();
152            },
153            do {
154                self.prepare_table_ref(table_ref, sql);
155            },
156            last {
157                self.prepare_index_hints(select, sql);
158                self.prepare_table_sample(select, sql);
159            }
160        );
161
162        for expr in select.join.iter() {
163            sql.write_str(" ").unwrap();
164            self.prepare_join_expr(expr, sql);
165        }
166
167        self.prepare_condition(&select.r#where, "WHERE", sql);
168
169        let mut groups = select.groups.iter();
170        join_io!(
171            groups,
172            expr,
173            first {
174                sql.write_str(" GROUP BY ").unwrap();
175            },
176            join {
177                sql.write_str(", ").unwrap();
178            },
179            do {
180                self.prepare_simple_expr(expr, sql);
181            }
182        );
183
184        self.prepare_condition(&select.having, "HAVING", sql);
185
186        if !select.unions.is_empty() {
187            select.unions.iter().for_each(|(union_type, query)| {
188                self.prepare_union_statement(*union_type, query, sql);
189            });
190        }
191
192        let mut orders = select.orders.iter();
193        join_io!(
194            orders,
195            expr,
196            first {
197                sql.write_str(" ORDER BY ").unwrap();
198            },
199            join {
200                sql.write_str(", ").unwrap();
201            },
202            do {
203                self.prepare_order_expr(expr, sql);
204            }
205        );
206
207        self.prepare_select_limit_offset(select, sql);
208
209        if let Some(lock) = &select.lock {
210            sql.write_str(" ").unwrap();
211            self.prepare_select_lock(lock, sql);
212        }
213
214        if let Some((name, query)) = &select.window {
215            sql.write_str(" WINDOW ").unwrap();
216            self.prepare_iden(name, sql);
217            sql.write_str(" AS (").unwrap();
218            self.prepare_window_statement(query, sql);
219            sql.write_str(")").unwrap();
220        }
221    }
222
223    // Translate the LIMIT and OFFSET expression in [`SelectStatement`]
224    fn prepare_select_limit_offset(&self, select: &SelectStatement, sql: &mut dyn SqlWriter) {
225        if let Some(limit) = &select.limit {
226            sql.write_str(" LIMIT ").unwrap();
227            self.prepare_value(limit.clone(), sql);
228        }
229
230        if let Some(offset) = &select.offset {
231            sql.write_str(" OFFSET ").unwrap();
232            self.prepare_value(offset.clone(), sql);
233        }
234    }
235
236    /// Translate [`UpdateStatement`] into SQL statement.
237    fn prepare_update_statement(&self, update: &UpdateStatement, sql: &mut dyn SqlWriter) {
238        if let Some(with) = &update.with {
239            self.prepare_with_clause(with, sql);
240        }
241
242        sql.write_str("UPDATE ").unwrap();
243
244        if let Some(table) = &update.table {
245            self.prepare_table_ref(table, sql);
246        }
247
248        self.prepare_update_join(&update.from, &update.r#where, sql);
249
250        sql.write_str(" SET ").unwrap();
251
252        let mut values = update.values.iter();
253        join_io!(
254            values,
255            row,
256            join {
257                sql.write_str(", ").unwrap();
258            },
259            do {
260                let (col, v) = row;
261                self.prepare_update_column(&update.table, &update.from, col, sql);
262                sql.write_str(" = ").unwrap();
263                self.prepare_simple_expr(v, sql);
264            }
265        );
266
267        self.prepare_update_from(&update.from, sql);
268
269        self.prepare_output(&update.returning, sql);
270
271        self.prepare_update_condition(&update.from, &update.r#where, sql);
272
273        self.prepare_update_order_by(update, sql);
274
275        self.prepare_update_limit(update, sql);
276
277        self.prepare_returning(&update.returning, sql);
278    }
279
280    fn prepare_update_join(&self, _: &[TableRef], _: &ConditionHolder, _: &mut dyn SqlWriter) {
281        // MySQL specific
282    }
283
284    fn prepare_update_from(&self, from: &[TableRef], sql: &mut dyn SqlWriter) {
285        let mut from_iter = from.iter();
286        join_io!(
287            from_iter,
288            table_ref,
289            first {
290                sql.write_str(" FROM ").unwrap();
291            },
292            join {
293                sql.write_str(", ").unwrap();
294            },
295            do {
296                self.prepare_table_ref(table_ref, sql);
297            }
298        );
299    }
300
301    fn prepare_update_column(
302        &self,
303        _: &Option<Box<TableRef>>,
304        _: &[TableRef],
305        column: &DynIden,
306        sql: &mut dyn SqlWriter,
307    ) {
308        self.prepare_iden(column, sql);
309    }
310
311    fn prepare_update_condition(
312        &self,
313        _: &[TableRef],
314        condition: &ConditionHolder,
315        sql: &mut dyn SqlWriter,
316    ) {
317        self.prepare_condition(condition, "WHERE", sql);
318    }
319
320    /// Translate ORDER BY expression in [`UpdateStatement`].
321    fn prepare_update_order_by(&self, update: &UpdateStatement, sql: &mut dyn SqlWriter) {
322        let mut orders = update.orders.iter();
323        join_io!(
324            orders,
325            expr,
326            first {
327                sql.write_str(" ORDER BY ").unwrap();
328            },
329            join {
330                sql.write_str(", ").unwrap();
331            },
332            do {
333                self.prepare_order_expr(expr, sql);
334            }
335        );
336    }
337
338    /// Translate LIMIT expression in [`UpdateStatement`].
339    fn prepare_update_limit(&self, update: &UpdateStatement, sql: &mut dyn SqlWriter) {
340        if let Some(limit) = &update.limit {
341            sql.write_str(" LIMIT ").unwrap();
342            self.prepare_value(limit.clone(), sql);
343        }
344    }
345
346    /// Translate [`DeleteStatement`] into SQL statement.
347    fn prepare_delete_statement(&self, delete: &DeleteStatement, sql: &mut dyn SqlWriter) {
348        if let Some(with) = &delete.with {
349            self.prepare_with_clause(with, sql);
350        }
351
352        sql.write_str("DELETE ").unwrap();
353
354        if let Some(table) = &delete.table {
355            sql.write_str("FROM ").unwrap();
356            self.prepare_table_ref(table, sql);
357        }
358
359        self.prepare_output(&delete.returning, sql);
360
361        self.prepare_condition(&delete.r#where, "WHERE", sql);
362
363        self.prepare_delete_order_by(delete, sql);
364
365        self.prepare_delete_limit(delete, sql);
366
367        self.prepare_returning(&delete.returning, sql);
368    }
369
370    /// Translate ORDER BY expression in [`DeleteStatement`].
371    fn prepare_delete_order_by(&self, delete: &DeleteStatement, sql: &mut dyn SqlWriter) {
372        let mut orders = delete.orders.iter();
373        join_io!(
374            orders,
375            expr,
376            first {
377                sql.write_str(" ORDER BY ").unwrap();
378            },
379            join {
380                sql.write_str(", ").unwrap();
381            },
382            do {
383                self.prepare_order_expr(expr, sql);
384            }
385        );
386    }
387
388    /// Translate LIMIT expression in [`DeleteStatement`].
389    fn prepare_delete_limit(&self, delete: &DeleteStatement, sql: &mut dyn SqlWriter) {
390        if let Some(limit) = &delete.limit {
391            sql.write_str(" LIMIT ").unwrap();
392            self.prepare_value(limit.clone(), sql);
393        }
394    }
395
396    /// Translate [`Expr`] into SQL statement.
397    fn prepare_simple_expr(&self, simple_expr: &Expr, sql: &mut dyn SqlWriter) {
398        self.prepare_simple_expr_common(simple_expr, sql);
399    }
400
401    fn prepare_simple_expr_common(&self, simple_expr: &Expr, sql: &mut dyn SqlWriter) {
402        match simple_expr {
403            Expr::Column(column_ref) => {
404                self.prepare_column_ref(column_ref, sql);
405            }
406            Expr::Tuple(exprs) => {
407                self.prepare_tuple(exprs, sql);
408            }
409            Expr::Unary(op, expr) => {
410                self.prepare_un_oper(op, sql);
411                sql.write_str(" ").unwrap();
412                let drop_expr_paren =
413                    self.inner_expr_well_known_greater_precedence(expr, &(*op).into());
414                if !drop_expr_paren {
415                    sql.write_str("(").unwrap();
416                }
417                self.prepare_simple_expr(expr, sql);
418                if !drop_expr_paren {
419                    sql.write_str(")").unwrap();
420                }
421            }
422            Expr::FunctionCall(func) => {
423                self.prepare_function_name(&func.func, sql);
424                self.prepare_function_arguments(func, sql);
425            }
426            Expr::Binary(left, op, right) => match (op, right.as_ref()) {
427                (BinOper::In, Expr::Tuple(t)) if t.is_empty() => {
428                    self.binary_expr(&1i32.into(), &BinOper::Equal, &2i32.into(), sql)
429                }
430                (BinOper::NotIn, Expr::Tuple(t)) if t.is_empty() => {
431                    self.binary_expr(&1i32.into(), &BinOper::Equal, &1i32.into(), sql)
432                }
433                _ => self.binary_expr(left, op, right, sql),
434            },
435            Expr::SubQuery(oper, sel) => {
436                if let Some(oper) = oper {
437                    self.prepare_sub_query_oper(oper, sql);
438                }
439                sql.write_str("(").unwrap();
440                self.prepare_query_statement(sel.deref(), sql);
441                sql.write_str(")").unwrap();
442            }
443            Expr::Value(val) => {
444                self.prepare_value(val.clone(), sql);
445            }
446            Expr::Values(list) => {
447                sql.write_str("(").unwrap();
448                let mut iter = list.iter();
449                join_io!(
450                    iter,
451                    val,
452                    join {
453                        sql.write_str(", ").unwrap();
454                    },
455                    do {
456                        self.prepare_value(val.clone(), sql);
457                    }
458                );
459                sql.write_str(")").unwrap();
460            }
461            Expr::Custom(s) => {
462                sql.write_str(s).unwrap();
463            }
464            Expr::CustomWithExpr(expr, values) => {
465                let (placeholder, numbered) = self.placeholder();
466                let mut tokenizer = Tokenizer::new(expr).iter().peekable();
467                let mut count = 0;
468                while let Some(token) = tokenizer.next() {
469                    match token {
470                        Token::Punctuation(mark) if mark == placeholder => match tokenizer.peek() {
471                            Some(Token::Punctuation(next_mark)) if next_mark == &placeholder => {
472                                sql.write_str(next_mark).unwrap();
473                                tokenizer.next();
474                            }
475                            Some(Token::Unquoted(tok)) if numbered => {
476                                if let Ok(num) = tok.parse::<usize>() {
477                                    self.prepare_simple_expr(&values[num - 1], sql);
478                                }
479                                tokenizer.next();
480                            }
481                            _ => {
482                                self.prepare_simple_expr(&values[count], sql);
483                                count += 1;
484                            }
485                        },
486                        _ => sql.write_str(token.as_str()).unwrap(),
487                    };
488                }
489            }
490            Expr::Keyword(keyword) => {
491                self.prepare_keyword(keyword, sql);
492            }
493            Expr::AsEnum(_, expr) => {
494                self.prepare_simple_expr(expr, sql);
495            }
496            Expr::Case(case_stmt) => {
497                self.prepare_case_statement(case_stmt, sql);
498            }
499            Expr::Constant(val) => {
500                self.prepare_constant(val, sql);
501            }
502            Expr::TypeName(type_name) => {
503                self.prepare_type_ref(type_name, sql);
504            }
505        }
506    }
507
508    /// Translate [`CaseStatement`] into SQL statement.
509    fn prepare_case_statement(&self, stmts: &CaseStatement, sql: &mut dyn SqlWriter) {
510        sql.write_str("(CASE").unwrap();
511
512        let CaseStatement { when, r#else } = stmts;
513
514        for case in when.iter() {
515            sql.write_str(" WHEN (").unwrap();
516            self.prepare_condition_where(&case.condition, sql);
517            sql.write_str(") THEN ").unwrap();
518
519            self.prepare_simple_expr(&case.result, sql);
520        }
521        if let Some(r#else) = r#else {
522            sql.write_str(" ELSE ").unwrap();
523            self.prepare_simple_expr(r#else, sql);
524        }
525
526        sql.write_str(" END)").unwrap();
527    }
528
529    /// Translate [`SelectDistinct`] into SQL statement.
530    fn prepare_select_distinct(&self, select_distinct: &SelectDistinct, sql: &mut dyn SqlWriter) {
531        match select_distinct {
532            SelectDistinct::All => sql.write_str("ALL").unwrap(),
533            SelectDistinct::Distinct => sql.write_str("DISTINCT").unwrap(),
534            _ => {}
535        }
536    }
537
538    /// Translate [`IndexHint`] into SQL statement.
539    fn prepare_index_hints(&self, _select: &SelectStatement, _sql: &mut dyn SqlWriter) {}
540
541    /// Translate [`TableSample`] into SQL statement.
542    fn prepare_table_sample(&self, _select: &SelectStatement, _sql: &mut dyn SqlWriter) {}
543
544    /// Translate [`LockType`] into SQL statement.
545    fn prepare_select_lock(&self, lock: &LockClause, sql: &mut dyn SqlWriter) {
546        sql.write_str("FOR ").unwrap();
547        sql.write_str(match lock.r#type {
548            LockType::Update => "UPDATE",
549            LockType::NoKeyUpdate => "NO KEY UPDATE",
550            LockType::Share => "SHARE",
551            LockType::KeyShare => "KEY SHARE",
552        })
553        .unwrap();
554        let mut tables = lock.tables.iter();
555        join_io!(
556            tables,
557            table_ref,
558            first {
559                sql.write_str(" OF ").unwrap();
560            },
561            join {
562                sql.write_str(", ").unwrap();
563            },
564            do {
565                self.prepare_table_ref(table_ref, sql);
566            }
567        );
568
569        if let Some(behavior) = lock.behavior {
570            match behavior {
571                LockBehavior::Nowait => sql.write_str(" NOWAIT").unwrap(),
572                LockBehavior::SkipLocked => sql.write_str(" SKIP LOCKED").unwrap(),
573            }
574        }
575    }
576
577    /// Translate [`SelectExpr`] into SQL statement.
578    fn prepare_select_expr(&self, select_expr: &SelectExpr, sql: &mut dyn SqlWriter) {
579        self.prepare_simple_expr(&select_expr.expr, sql);
580        match &select_expr.window {
581            Some(WindowSelectType::Name(name)) => {
582                sql.write_str(" OVER ").unwrap();
583                self.prepare_iden(name, sql);
584            }
585            Some(WindowSelectType::Query(window)) => {
586                sql.write_str(" OVER ").unwrap();
587                sql.write_str("( ").unwrap();
588                self.prepare_window_statement(window, sql);
589                sql.write_str(" )").unwrap();
590            }
591            None => {}
592        };
593
594        if let Some(alias) = &select_expr.alias {
595            sql.write_str(" AS ").unwrap();
596            self.prepare_iden(alias, sql);
597        };
598    }
599
600    /// Translate [`JoinExpr`] into SQL statement.
601    fn prepare_join_expr(&self, join_expr: &JoinExpr, sql: &mut dyn SqlWriter) {
602        self.prepare_join_type(&join_expr.join, sql);
603        sql.write_str(" ").unwrap();
604        self.prepare_join_table_ref(join_expr, sql);
605        if let Some(on) = &join_expr.on {
606            self.prepare_join_on(on, sql);
607        }
608    }
609
610    fn prepare_join_table_ref(&self, join_expr: &JoinExpr, sql: &mut dyn SqlWriter) {
611        if join_expr.lateral {
612            sql.write_str("LATERAL ").unwrap();
613        }
614        self.prepare_table_ref(&join_expr.table, sql);
615    }
616
617    /// Translate [`TableRef`] into SQL statement.
618    fn prepare_table_ref(&self, table_ref: &TableRef, sql: &mut dyn SqlWriter) {
619        match table_ref {
620            TableRef::SubQuery(query, alias) => {
621                sql.write_str("(").unwrap();
622                self.prepare_select_statement(query, sql);
623                sql.write_str(")").unwrap();
624                sql.write_str(" AS ").unwrap();
625                self.prepare_iden(alias, sql);
626            }
627            TableRef::ValuesList(values, alias) => {
628                sql.write_str("(").unwrap();
629                self.prepare_values_list(values, sql);
630                sql.write_str(")").unwrap();
631                sql.write_str(" AS ").unwrap();
632                self.prepare_iden(alias, sql);
633            }
634            TableRef::FunctionCall(func, alias) => {
635                self.prepare_function_name(&func.func, sql);
636                self.prepare_function_arguments(func, sql);
637                sql.write_str(" AS ").unwrap();
638                self.prepare_iden(alias, sql);
639            }
640            _ => self.prepare_table_ref_iden(table_ref, sql),
641        }
642    }
643
644    fn prepare_column_ref(&self, column_ref: &ColumnRef, sql: &mut dyn SqlWriter) {
645        match column_ref {
646            ColumnRef::Column(ColumnName(table_name, column)) => {
647                if let Some(table_name) = table_name {
648                    self.prepare_table_name(table_name, sql);
649                    sql.write_str(".").unwrap();
650                }
651                self.prepare_iden(column, sql);
652            }
653            ColumnRef::Asterisk(table_name) => {
654                if let Some(table_name) = table_name {
655                    self.prepare_table_name(table_name, sql);
656                    sql.write_str(".").unwrap();
657                }
658                sql.write_str("*").unwrap();
659            }
660        }
661    }
662
663    /// Translate [`UnOper`] into SQL statement.
664    fn prepare_un_oper(&self, un_oper: &UnOper, sql: &mut dyn SqlWriter) {
665        sql.write_str(match un_oper {
666            UnOper::Not => "NOT",
667        })
668        .unwrap();
669    }
670
671    fn prepare_bin_oper_common(&self, bin_oper: &BinOper, sql: &mut dyn SqlWriter) {
672        sql.write_str(match bin_oper {
673            BinOper::And => "AND",
674            BinOper::Or => "OR",
675            BinOper::Like => "LIKE",
676            BinOper::NotLike => "NOT LIKE",
677            BinOper::Is => "IS",
678            BinOper::IsNot => "IS NOT",
679            BinOper::In => "IN",
680            BinOper::NotIn => "NOT IN",
681            BinOper::Between => "BETWEEN",
682            BinOper::NotBetween => "NOT BETWEEN",
683            BinOper::Equal => "=",
684            BinOper::NotEqual => "<>",
685            BinOper::SmallerThan => "<",
686            BinOper::GreaterThan => ">",
687            BinOper::SmallerThanOrEqual => "<=",
688            BinOper::GreaterThanOrEqual => ">=",
689            BinOper::Add => "+",
690            BinOper::Sub => "-",
691            BinOper::Mul => "*",
692            BinOper::Div => "/",
693            BinOper::Mod => "%",
694            BinOper::LShift => "<<",
695            BinOper::RShift => ">>",
696            BinOper::As => "AS",
697            BinOper::Escape => "ESCAPE",
698            BinOper::Custom(raw) => raw,
699            BinOper::BitAnd => "&",
700            BinOper::BitOr => "|",
701            #[allow(unreachable_patterns)]
702            _ => unimplemented!(),
703        })
704        .unwrap();
705    }
706
707    /// Translate [`BinOper`] into SQL statement.
708    fn prepare_bin_oper(&self, bin_oper: &BinOper, sql: &mut dyn SqlWriter) {
709        self.prepare_bin_oper_common(bin_oper, sql);
710    }
711
712    /// Translate [`SubQueryOper`] into SQL statement.
713    fn prepare_sub_query_oper(&self, oper: &SubQueryOper, sql: &mut dyn SqlWriter) {
714        sql.write_str(match oper {
715            SubQueryOper::Exists => "EXISTS",
716            SubQueryOper::Any => "ANY",
717            SubQueryOper::Some => "SOME",
718            SubQueryOper::All => "ALL",
719        })
720        .unwrap();
721    }
722
723    /// Translate [`LogicalChainOper`] into SQL statement.
724    fn prepare_logical_chain_oper(
725        &self,
726        log_chain_oper: &LogicalChainOper,
727        i: usize,
728        length: usize,
729        sql: &mut dyn SqlWriter,
730    ) {
731        let (simple_expr, oper) = match log_chain_oper {
732            LogicalChainOper::And(simple_expr) => (simple_expr, "AND"),
733            LogicalChainOper::Or(simple_expr) => (simple_expr, "OR"),
734        };
735        if i > 0 {
736            sql.write_str(" ").unwrap();
737            sql.write_str(oper).unwrap();
738            sql.write_str(" ").unwrap();
739        }
740        let both_binary = match simple_expr {
741            Expr::Binary(_, _, right) => {
742                matches!(right.as_ref(), Expr::Binary(_, _, _))
743            }
744            _ => false,
745        };
746        let need_parentheses = length > 1 && both_binary;
747        if need_parentheses {
748            sql.write_str("(").unwrap();
749        }
750        self.prepare_simple_expr(simple_expr, sql);
751        if need_parentheses {
752            sql.write_str(")").unwrap();
753        }
754    }
755
756    /// Translate [`Function`] into SQL statement.
757    fn prepare_function_name_common(&self, function: &Func, sql: &mut dyn SqlWriter) {
758        if let Func::Custom(iden) = function {
759            sql.write_str(&iden.0)
760        } else {
761            sql.write_str(match function {
762                Func::Max => "MAX",
763                Func::Min => "MIN",
764                Func::Sum => "SUM",
765                Func::Avg => "AVG",
766                Func::Abs => "ABS",
767                Func::Coalesce => "COALESCE",
768                Func::Count => "COUNT",
769                Func::IfNull => self.if_null_function(),
770                Func::Greatest => self.greatest_function(),
771                Func::Least => self.least_function(),
772                Func::CharLength => self.char_length_function(),
773                Func::Cast => "CAST",
774                Func::Lower => "LOWER",
775                Func::Upper => "UPPER",
776                Func::BitAnd => "BIT_AND",
777                Func::BitOr => "BIT_OR",
778                Func::Custom(_) => "",
779                Func::Random => self.random_function(),
780                Func::Round => "ROUND",
781                Func::Md5 => "MD5",
782                #[cfg(feature = "backend-postgres")]
783                Func::PgFunction(_) => unimplemented!(),
784            })
785        }
786        .unwrap();
787    }
788
789    fn prepare_function_arguments(&self, func: &FunctionCall, sql: &mut dyn SqlWriter) {
790        sql.write_str("(").unwrap();
791        let mut args = func.args.iter().zip(func.mods.iter());
792
793        if let Some((arg, modifier)) = args.next() {
794            if modifier.distinct {
795                sql.write_str("DISTINCT ").unwrap();
796            }
797            self.prepare_simple_expr(arg, sql);
798        }
799
800        for (arg, modifier) in args {
801            sql.write_str(", ").unwrap();
802            if modifier.distinct {
803                sql.write_str("DISTINCT ").unwrap();
804            }
805            self.prepare_simple_expr(arg, sql);
806        }
807
808        sql.write_str(")").unwrap();
809    }
810
811    /// Translate [`QueryStatement`] into SQL statement.
812    fn prepare_query_statement(&self, query: &SubQueryStatement, sql: &mut dyn SqlWriter);
813
814    fn prepare_with_query(&self, query: &WithQuery, sql: &mut dyn SqlWriter) {
815        self.prepare_with_clause(&query.with_clause, sql);
816        self.prepare_query_statement(query.query.as_ref().unwrap().deref(), sql);
817    }
818
819    fn prepare_with_clause(&self, with_clause: &WithClause, sql: &mut dyn SqlWriter) {
820        self.prepare_with_clause_start(with_clause, sql);
821        self.prepare_with_clause_common_tables(with_clause, sql);
822        if with_clause.recursive {
823            self.prepare_with_clause_recursive_options(with_clause, sql);
824        }
825    }
826
827    fn prepare_with_clause_recursive_options(
828        &self,
829        with_clause: &WithClause,
830        sql: &mut dyn SqlWriter,
831    ) {
832        if with_clause.recursive {
833            if let Some(search) = &with_clause.search {
834                sql.write_str("SEARCH ").unwrap();
835                sql.write_str(match &search.order.as_ref().unwrap() {
836                    SearchOrder::BREADTH => "BREADTH",
837                    SearchOrder::DEPTH => "DEPTH",
838                })
839                .unwrap();
840                sql.write_str(" FIRST BY ").unwrap();
841
842                self.prepare_simple_expr(&search.expr.as_ref().unwrap().expr, sql);
843
844                sql.write_str(" SET ").unwrap();
845
846                self.prepare_iden(search.expr.as_ref().unwrap().alias.as_ref().unwrap(), sql);
847                sql.write_str(" ").unwrap();
848            }
849            if let Some(cycle) = &with_clause.cycle {
850                sql.write_str("CYCLE ").unwrap();
851
852                self.prepare_simple_expr(cycle.expr.as_ref().unwrap(), sql);
853
854                sql.write_str(" SET ").unwrap();
855
856                self.prepare_iden(cycle.set_as.as_ref().unwrap(), sql);
857                sql.write_str(" USING ").unwrap();
858                self.prepare_iden(cycle.using.as_ref().unwrap(), sql);
859                sql.write_str(" ").unwrap();
860            }
861        }
862    }
863
864    fn prepare_with_clause_common_tables(&self, with_clause: &WithClause, sql: &mut dyn SqlWriter) {
865        let mut cte_first = true;
866        assert_ne!(
867            with_clause.cte_expressions.len(),
868            0,
869            "Cannot build a with query that has no common table expression!"
870        );
871
872        for cte in &with_clause.cte_expressions {
873            if !cte_first {
874                sql.write_str(", ").unwrap();
875            }
876            cte_first = false;
877
878            self.prepare_with_query_clause_common_table(cte, sql);
879        }
880    }
881
882    fn prepare_with_query_clause_common_table(
883        &self,
884        cte: &CommonTableExpression,
885        sql: &mut dyn SqlWriter,
886    ) {
887        self.prepare_iden(cte.table_name.as_ref().unwrap(), sql);
888
889        if cte.cols.is_empty() {
890            sql.write_str(" ").unwrap();
891        } else {
892            sql.write_str(" (").unwrap();
893
894            let mut col_first = true;
895            for col in &cte.cols {
896                if !col_first {
897                    sql.write_str(", ").unwrap();
898                }
899                col_first = false;
900                self.prepare_iden(col, sql);
901            }
902
903            sql.write_str(") ").unwrap();
904        }
905
906        sql.write_str("AS ").unwrap();
907
908        self.prepare_with_query_clause_materialization(cte, sql);
909
910        sql.write_str("(").unwrap();
911
912        self.prepare_query_statement(cte.query.as_ref().unwrap().deref(), sql);
913
914        sql.write_str(") ").unwrap();
915    }
916
917    fn prepare_with_query_clause_materialization(
918        &self,
919        cte: &CommonTableExpression,
920        sql: &mut dyn SqlWriter,
921    ) {
922        if let Some(materialized) = cte.materialized {
923            if !materialized {
924                sql.write_str("NOT MATERIALIZED ")
925            } else {
926                sql.write_str(" MATERIALIZED ")
927            }
928            .unwrap()
929        }
930    }
931
932    fn prepare_with_clause_start(&self, with_clause: &WithClause, sql: &mut dyn SqlWriter) {
933        sql.write_str("WITH ").unwrap();
934
935        if with_clause.recursive {
936            sql.write_str("RECURSIVE ").unwrap();
937        }
938    }
939
940    fn prepare_insert(&self, replace: bool, sql: &mut dyn SqlWriter) {
941        if replace {
942            sql.write_str("REPLACE").unwrap();
943        } else {
944            sql.write_str("INSERT").unwrap();
945        }
946    }
947
948    fn prepare_function_name(&self, function: &Func, sql: &mut dyn SqlWriter) {
949        self.prepare_function_name_common(function, sql)
950    }
951
952    /// Translate [`TypeRef`] into an SQL statement.
953    fn prepare_type_ref(&self, type_name: &TypeRef, sql: &mut dyn SqlWriter) {
954        let TypeRef(schema_name, r#type) = type_name;
955        if let Some(schema_name) = schema_name {
956            self.prepare_schema_name(schema_name, sql);
957            write!(sql, ".").unwrap();
958        }
959        self.prepare_iden(r#type, sql);
960    }
961
962    /// Translate [`JoinType`] into SQL statement.
963    fn prepare_join_type(&self, join_type: &JoinType, sql: &mut dyn SqlWriter) {
964        self.prepare_join_type_common(join_type, sql)
965    }
966
967    fn prepare_join_type_common(&self, join_type: &JoinType, sql: &mut dyn SqlWriter) {
968        sql.write_str(match join_type {
969            JoinType::Join => "JOIN",
970            JoinType::CrossJoin => "CROSS JOIN",
971            JoinType::InnerJoin => "INNER JOIN",
972            JoinType::LeftJoin => "LEFT JOIN",
973            JoinType::RightJoin => "RIGHT JOIN",
974            JoinType::FullOuterJoin => "FULL OUTER JOIN",
975        })
976        .unwrap()
977    }
978
979    /// Translate [`OrderExpr`] into SQL statement.
980    fn prepare_order_expr(&self, order_expr: &OrderExpr, sql: &mut dyn SqlWriter) {
981        if !matches!(order_expr.order, Order::Field(_)) {
982            self.prepare_simple_expr(&order_expr.expr, sql);
983        }
984        self.prepare_order(order_expr, sql);
985    }
986
987    /// Translate [`JoinOn`] into SQL statement.
988    fn prepare_join_on(&self, join_on: &JoinOn, sql: &mut dyn SqlWriter) {
989        match join_on {
990            JoinOn::Condition(c) => self.prepare_condition(c, "ON", sql),
991            JoinOn::Columns(_c) => unimplemented!(),
992        }
993    }
994
995    /// Translate [`Order`] into SQL statement.
996    fn prepare_order(&self, order_expr: &OrderExpr, sql: &mut dyn SqlWriter) {
997        match &order_expr.order {
998            Order::Asc => sql.write_str(" ASC").unwrap(),
999            Order::Desc => sql.write_str(" DESC").unwrap(),
1000            Order::Field(values) => self.prepare_field_order(order_expr, values, sql),
1001        }
1002    }
1003
1004    /// Translate [`Order::Field`] into SQL statement
1005    fn prepare_field_order(
1006        &self,
1007        order_expr: &OrderExpr,
1008        values: &Values,
1009        sql: &mut dyn SqlWriter,
1010    ) {
1011        sql.write_str("CASE ").unwrap();
1012        let mut i = 0;
1013        for value in &values.0 {
1014            sql.write_str("WHEN ").unwrap();
1015            self.prepare_simple_expr(&order_expr.expr, sql);
1016            sql.write_str("=").unwrap();
1017            self.write_value(sql.as_writer(), value).unwrap();
1018            sql.write_str(" THEN ").unwrap();
1019            write!(sql, "{i}").unwrap();
1020            sql.write_str(" ").unwrap();
1021            i += 1;
1022        }
1023
1024        sql.write_str("ELSE ").unwrap();
1025        write!(sql, "{i}").unwrap();
1026        sql.write_str(" END").unwrap();
1027    }
1028
1029    /// Write [`Value`] into SQL statement as parameter.
1030    fn prepare_value(&self, value: Value, sql: &mut dyn SqlWriter);
1031
1032    /// Write [`Value`] inline.
1033    fn prepare_constant(&self, value: &Value, sql: &mut dyn SqlWriter) {
1034        self.write_value(sql.as_writer(), value).unwrap();
1035    }
1036
1037    /// Translate a `&[ValueTuple]` into a VALUES list.
1038    fn prepare_values_list(&self, value_tuples: &[ValueTuple], sql: &mut dyn SqlWriter) {
1039        sql.write_str("VALUES ").unwrap();
1040        let mut tuples = value_tuples.iter();
1041        join_io!(
1042            tuples,
1043            value_tuple,
1044            join {
1045                sql.write_str(", ").unwrap();
1046            },
1047            do {
1048                sql.write_str(self.values_list_tuple_prefix()).unwrap();
1049                sql.write_str("(").unwrap();
1050
1051                let mut values = value_tuple.clone().into_iter();
1052                join_io!(
1053                    values,
1054                    value,
1055                    join {
1056                        sql.write_str(", ").unwrap();
1057                    },
1058                    do {
1059                        self.prepare_value(value, sql);
1060                    }
1061                );
1062
1063                sql.write_str(")").unwrap();
1064            }
1065        );
1066    }
1067
1068    /// Translate [`Expr::Tuple`] into SQL statement.
1069    fn prepare_tuple(&self, exprs: &[Expr], sql: &mut dyn SqlWriter) {
1070        sql.write_str("(").unwrap();
1071        for (i, expr) in exprs.iter().enumerate() {
1072            if i != 0 {
1073                sql.write_str(", ").unwrap();
1074            }
1075            self.prepare_simple_expr(expr, sql);
1076        }
1077        sql.write_str(")").unwrap();
1078    }
1079
1080    /// Translate [`Keyword`] into SQL statement.
1081    fn prepare_keyword(&self, keyword: &Keyword, sql: &mut dyn SqlWriter) {
1082        match keyword {
1083            Keyword::Null => sql.write_str("NULL").unwrap(),
1084            Keyword::CurrentDate => sql.write_str("CURRENT_DATE").unwrap(),
1085            Keyword::CurrentTime => sql.write_str("CURRENT_TIME").unwrap(),
1086            Keyword::CurrentTimestamp => sql.write_str("CURRENT_TIMESTAMP").unwrap(),
1087            Keyword::Default => sql.write_str("DEFAULT").unwrap(),
1088            Keyword::Custom(iden) => sql.write_str(&iden.0).unwrap(),
1089        }
1090    }
1091
1092    /// Convert a SQL value into syntax-specific string
1093    fn value_to_string(&self, v: &Value) -> String {
1094        self.value_to_string_common(v)
1095    }
1096
1097    fn value_to_string_common(&self, v: &Value) -> String {
1098        let mut s = String::new();
1099        self.write_value(&mut s, v).unwrap();
1100        s
1101    }
1102
1103    #[doc(hidden)]
1104    fn write_value(&self, buf: &mut dyn Write, value: &Value) -> fmt::Result {
1105        match value {
1106            Value::Bool(None)
1107            | Value::TinyInt(None)
1108            | Value::SmallInt(None)
1109            | Value::Int(None)
1110            | Value::BigInt(None)
1111            | Value::TinyUnsigned(None)
1112            | Value::SmallUnsigned(None)
1113            | Value::Unsigned(None)
1114            | Value::BigUnsigned(None)
1115            | Value::Float(None)
1116            | Value::Double(None)
1117            | Value::String(None)
1118            | Value::Char(None)
1119            | Value::Bytes(None) => buf.write_str("NULL")?,
1120            #[cfg(feature = "with-json")]
1121            Value::Json(None) => buf.write_str("NULL")?,
1122            #[cfg(feature = "with-chrono")]
1123            Value::ChronoDate(None) => buf.write_str("NULL")?,
1124            #[cfg(feature = "with-chrono")]
1125            Value::ChronoTime(None) => buf.write_str("NULL")?,
1126            #[cfg(feature = "with-chrono")]
1127            Value::ChronoDateTime(None) => buf.write_str("NULL")?,
1128            #[cfg(feature = "with-chrono")]
1129            Value::ChronoDateTimeUtc(None) => buf.write_str("NULL")?,
1130            #[cfg(feature = "with-chrono")]
1131            Value::ChronoDateTimeLocal(None) => buf.write_str("NULL")?,
1132            #[cfg(feature = "with-chrono")]
1133            Value::ChronoDateTimeWithTimeZone(None) => buf.write_str("NULL")?,
1134            #[cfg(feature = "with-time")]
1135            Value::TimeDate(None) => buf.write_str("NULL")?,
1136            #[cfg(feature = "with-time")]
1137            Value::TimeTime(None) => buf.write_str("NULL")?,
1138            #[cfg(feature = "with-time")]
1139            Value::TimeDateTime(None) => buf.write_str("NULL")?,
1140            #[cfg(feature = "with-time")]
1141            Value::TimeDateTimeWithTimeZone(None) => buf.write_str("NULL")?,
1142            #[cfg(feature = "with-jiff")]
1143            Value::JiffDate(None) => buf.write_str("NULL")?,
1144            #[cfg(feature = "with-jiff")]
1145            Value::JiffTime(None) => buf.write_str("NULL")?,
1146            #[cfg(feature = "with-jiff")]
1147            Value::JiffDateTime(None) => buf.write_str("NULL")?,
1148            #[cfg(feature = "with-jiff")]
1149            Value::JiffTimestamp(None) => buf.write_str("NULL")?,
1150            #[cfg(feature = "with-jiff")]
1151            Value::JiffZoned(None) => buf.write_str("NULL")?,
1152            #[cfg(feature = "with-rust_decimal")]
1153            Value::Decimal(None) => buf.write_str("NULL")?,
1154            #[cfg(feature = "with-bigdecimal")]
1155            Value::BigDecimal(None) => buf.write_str("NULL")?,
1156            #[cfg(feature = "with-uuid")]
1157            Value::Uuid(None) => buf.write_str("NULL")?,
1158            #[cfg(feature = "with-ipnetwork")]
1159            Value::IpNetwork(None) => buf.write_str("NULL")?,
1160            #[cfg(feature = "with-mac_address")]
1161            Value::MacAddress(None) => buf.write_str("NULL")?,
1162            #[cfg(feature = "postgres-array")]
1163            Value::Array(_, None) => buf.write_str("NULL")?,
1164            #[cfg(feature = "postgres-vector")]
1165            Value::Vector(None) => buf.write_str("NULL")?,
1166            Value::Bool(Some(b)) => buf.write_str(if *b { "TRUE" } else { "FALSE" })?,
1167            Value::TinyInt(Some(v)) => write!(buf, "{v}")?,
1168            Value::SmallInt(Some(v)) => write!(buf, "{v}")?,
1169            Value::Int(Some(v)) => write!(buf, "{v}")?,
1170            Value::BigInt(Some(v)) => write!(buf, "{v}")?,
1171            Value::TinyUnsigned(Some(v)) => write!(buf, "{v}")?,
1172            Value::SmallUnsigned(Some(v)) => write!(buf, "{v}")?,
1173            Value::Unsigned(Some(v)) => write!(buf, "{v}")?,
1174            Value::BigUnsigned(Some(v)) => write!(buf, "{v}")?,
1175            Value::Float(Some(v)) => write!(buf, "{v}")?,
1176            Value::Double(Some(v)) => write!(buf, "{v}")?,
1177            Value::String(Some(v)) => self.write_string_quoted(v, buf),
1178            Value::Char(Some(v)) => {
1179                self.write_string_quoted(std::str::from_utf8(&[*v as u8]).unwrap(), buf)
1180            }
1181            Value::Bytes(Some(v)) => self.write_bytes(v, buf),
1182            #[cfg(feature = "with-json")]
1183            Value::Json(Some(v)) => self.write_string_quoted(&v.to_string(), buf),
1184            #[cfg(feature = "with-chrono")]
1185            Value::ChronoDate(Some(v)) => {
1186                buf.write_str("'")?;
1187                write!(buf, "{}", v.format("%Y-%m-%d"))?;
1188                buf.write_str("'")?;
1189            }
1190            #[cfg(feature = "with-chrono")]
1191            Value::ChronoTime(Some(v)) => {
1192                buf.write_str("'")?;
1193                write!(buf, "{}", v.format("%H:%M:%S%.6f"))?;
1194                buf.write_str("'")?;
1195            }
1196            #[cfg(feature = "with-chrono")]
1197            Value::ChronoDateTime(Some(v)) => {
1198                buf.write_str("'")?;
1199                write!(buf, "{}", v.format("%Y-%m-%d %H:%M:%S%.6f"))?;
1200                buf.write_str("'")?;
1201            }
1202            #[cfg(feature = "with-chrono")]
1203            Value::ChronoDateTimeUtc(Some(v)) => {
1204                buf.write_str("'")?;
1205                write!(buf, "{}", v.format("%Y-%m-%d %H:%M:%S%.6f %:z"))?;
1206                buf.write_str("'")?;
1207            }
1208            #[cfg(feature = "with-chrono")]
1209            Value::ChronoDateTimeLocal(Some(v)) => {
1210                buf.write_str("'")?;
1211                write!(buf, "{}", v.format("%Y-%m-%d %H:%M:%S%.6f %:z"))?;
1212                buf.write_str("'")?;
1213            }
1214            #[cfg(feature = "with-chrono")]
1215            Value::ChronoDateTimeWithTimeZone(Some(v)) => {
1216                buf.write_str("'")?;
1217                write!(buf, "{}", v.format("%Y-%m-%d %H:%M:%S%.6f %:z"))?;
1218                buf.write_str("'")?;
1219            }
1220            #[cfg(feature = "with-time")]
1221            Value::TimeDate(Some(v)) => {
1222                buf.write_str("'")?;
1223                buf.write_str(&v.format(time_format::FORMAT_DATE).unwrap())?;
1224                buf.write_str("'")?;
1225            }
1226            #[cfg(feature = "with-time")]
1227            Value::TimeTime(Some(v)) => {
1228                buf.write_str("'")?;
1229                buf.write_str(&v.format(time_format::FORMAT_TIME).unwrap())?;
1230                buf.write_str("'")?;
1231            }
1232            #[cfg(feature = "with-time")]
1233            Value::TimeDateTime(Some(v)) => {
1234                buf.write_str("'")?;
1235                buf.write_str(&v.format(time_format::FORMAT_DATETIME).unwrap())?;
1236                buf.write_str("'")?;
1237            }
1238            #[cfg(feature = "with-time")]
1239            Value::TimeDateTimeWithTimeZone(Some(v)) => {
1240                buf.write_str("'")?;
1241                buf.write_str(&v.format(time_format::FORMAT_DATETIME_TZ).unwrap())?;
1242                buf.write_str("'")?;
1243            }
1244            // Jiff date and time dosen't need format string
1245            // The default behavior is what we want
1246            #[cfg(feature = "with-jiff")]
1247            Value::JiffDate(Some(v)) => {
1248                buf.write_str("'")?;
1249                write!(buf, "{v}")?;
1250                buf.write_str("'")?;
1251            }
1252            #[cfg(feature = "with-jiff")]
1253            Value::JiffTime(Some(v)) => {
1254                buf.write_str("'")?;
1255                write!(buf, "{v}")?;
1256                buf.write_str("'")?;
1257            }
1258            // Both JiffDateTime and JiffTimestamp map to timestamp
1259            #[cfg(feature = "with-jiff")]
1260            Value::JiffDateTime(Some(v)) => {
1261                use crate::with_jiff::JIFF_DATE_TIME_FMT_STR;
1262                buf.write_str("'")?;
1263                write!(buf, "{}", v.strftime(JIFF_DATE_TIME_FMT_STR))?;
1264                buf.write_str("'")?;
1265            }
1266            #[cfg(feature = "with-jiff")]
1267            Value::JiffTimestamp(Some(v)) => {
1268                use crate::with_jiff::JIFF_TIMESTAMP_FMT_STR;
1269                buf.write_str("'")?;
1270                write!(buf, "{}", v.strftime(JIFF_TIMESTAMP_FMT_STR))?;
1271                buf.write_str("'")?;
1272            }
1273            #[cfg(feature = "with-jiff")]
1274            // Zoned map to timestamp with timezone
1275            Value::JiffZoned(Some(v)) => {
1276                use crate::with_jiff::JIFF_ZONE_FMT_STR;
1277                buf.write_str("'")?;
1278                write!(buf, "{}", v.strftime(JIFF_ZONE_FMT_STR))?;
1279                buf.write_str("'")?;
1280            }
1281            #[cfg(feature = "with-rust_decimal")]
1282            Value::Decimal(Some(v)) => write!(buf, "{v}")?,
1283            #[cfg(feature = "with-bigdecimal")]
1284            Value::BigDecimal(Some(v)) => write!(buf, "{v}")?,
1285            #[cfg(feature = "with-uuid")]
1286            Value::Uuid(Some(v)) => {
1287                buf.write_str("'")?;
1288                write!(buf, "{v}")?;
1289                buf.write_str("'")?;
1290            }
1291            #[cfg(feature = "postgres-array")]
1292            Value::Array(_, Some(v)) => {
1293                if v.is_empty() {
1294                    buf.write_str("'{}'")?;
1295                } else {
1296                    buf.write_str("ARRAY [")?;
1297
1298                    let mut viter = v.iter();
1299
1300                    if let Some(element) = viter.next() {
1301                        self.write_value(buf, element)?;
1302                    }
1303
1304                    for element in viter {
1305                        buf.write_str(",")?;
1306                        self.write_value(buf, element)?;
1307                    }
1308                    buf.write_str("]")?;
1309                }
1310            }
1311            #[cfg(feature = "postgres-vector")]
1312            Value::Vector(Some(v)) => {
1313                buf.write_str("'[")?;
1314                let mut viter = v.as_slice().iter();
1315
1316                if let Some(element) = viter.next() {
1317                    write!(buf, "{element}")?;
1318                }
1319
1320                for element in viter {
1321                    buf.write_str(",")?;
1322                    write!(buf, "{element}")?;
1323                }
1324                buf.write_str("]'")?;
1325            }
1326            #[cfg(feature = "with-ipnetwork")]
1327            Value::IpNetwork(Some(v)) => {
1328                buf.write_str("'")?;
1329                write!(buf, "{v}")?;
1330                buf.write_str("'")?;
1331            }
1332            #[cfg(feature = "with-mac_address")]
1333            Value::MacAddress(Some(v)) => {
1334                buf.write_str("'")?;
1335                write!(buf, "{v}")?;
1336                buf.write_str("'")?;
1337            }
1338        };
1339
1340        Ok(())
1341    }
1342
1343    #[doc(hidden)]
1344    /// Write ON CONFLICT expression
1345    fn prepare_on_conflict(&self, on_conflict: &Option<OnConflict>, sql: &mut dyn SqlWriter) {
1346        if let Some(on_conflict) = on_conflict {
1347            self.prepare_on_conflict_keywords(sql);
1348            self.prepare_on_conflict_target(&on_conflict.targets, sql);
1349            self.prepare_on_conflict_condition(&on_conflict.target_where, sql);
1350            self.prepare_on_conflict_action(&on_conflict.action, sql);
1351            self.prepare_on_conflict_condition(&on_conflict.action_where, sql);
1352        }
1353    }
1354
1355    #[doc(hidden)]
1356    /// Write ON CONFLICT target
1357    fn prepare_on_conflict_target(
1358        &self,
1359        on_conflict_targets: &[OnConflictTarget],
1360        sql: &mut dyn SqlWriter,
1361    ) {
1362        let mut targets = on_conflict_targets.iter();
1363        join_io!(
1364            targets,
1365            target,
1366            first {
1367                sql.write_str("(").unwrap();
1368            },
1369            join {
1370                sql.write_str(", ").unwrap();
1371            },
1372            do {
1373                match target {
1374                    OnConflictTarget::ConflictColumn(col) => {
1375                        self.prepare_iden(col, sql);
1376                    }
1377                    OnConflictTarget::ConflictExpr(expr) => {
1378                        self.prepare_simple_expr(expr, sql);
1379                    }
1380                }
1381            },
1382            last {
1383                sql.write_str(")").unwrap();
1384            }
1385        );
1386    }
1387
1388    #[doc(hidden)]
1389    /// Write ON CONFLICT action
1390    fn prepare_on_conflict_action(
1391        &self,
1392        on_conflict_action: &Option<OnConflictAction>,
1393        sql: &mut dyn SqlWriter,
1394    ) {
1395        self.prepare_on_conflict_action_common(on_conflict_action, sql);
1396    }
1397
1398    fn prepare_on_conflict_action_common(
1399        &self,
1400        on_conflict_action: &Option<OnConflictAction>,
1401        sql: &mut dyn SqlWriter,
1402    ) {
1403        if let Some(action) = on_conflict_action {
1404            match action {
1405                OnConflictAction::DoNothing(_) => {
1406                    sql.write_str(" DO NOTHING").unwrap();
1407                }
1408                OnConflictAction::Update(update_strats) => {
1409                    self.prepare_on_conflict_do_update_keywords(sql);
1410                    let mut update_strats_iter = update_strats.iter();
1411                    join_io!(
1412                        update_strats_iter,
1413                        update_strat,
1414                        join {
1415                            sql.write_str(", ").unwrap();
1416                        },
1417                        do {
1418                            match update_strat {
1419                                OnConflictUpdate::Column(col) => {
1420                                    self.prepare_iden(col, sql);
1421                                    sql.write_str(" = ").unwrap();
1422                                    self.prepare_on_conflict_excluded_table(col, sql);
1423                                }
1424                                OnConflictUpdate::Expr(col, expr) => {
1425                                    self.prepare_iden(col, sql);
1426                                    sql.write_str(" = ").unwrap();
1427                                    self.prepare_simple_expr(expr, sql);
1428                                }
1429                            }
1430                        }
1431                    );
1432                }
1433            }
1434        }
1435    }
1436
1437    #[doc(hidden)]
1438    /// Write ON CONFLICT keywords
1439    fn prepare_on_conflict_keywords(&self, sql: &mut dyn SqlWriter) {
1440        sql.write_str(" ON CONFLICT ").unwrap();
1441    }
1442
1443    #[doc(hidden)]
1444    /// Write ON CONFLICT keywords
1445    fn prepare_on_conflict_do_update_keywords(&self, sql: &mut dyn SqlWriter) {
1446        sql.write_str(" DO UPDATE SET ").unwrap();
1447    }
1448
1449    #[doc(hidden)]
1450    /// Write ON CONFLICT update action by retrieving value from the excluded table
1451    fn prepare_on_conflict_excluded_table(&self, col: &DynIden, sql: &mut dyn SqlWriter) {
1452        sql.write_char(self.quote().left()).unwrap();
1453        sql.write_str("excluded").unwrap();
1454        sql.write_char(self.quote().right()).unwrap();
1455        sql.write_str(".").unwrap();
1456        self.prepare_iden(col, sql);
1457    }
1458
1459    #[doc(hidden)]
1460    /// Write ON CONFLICT conditions
1461    fn prepare_on_conflict_condition(
1462        &self,
1463        on_conflict_condition: &ConditionHolder,
1464        sql: &mut dyn SqlWriter,
1465    ) {
1466        self.prepare_condition(on_conflict_condition, "WHERE", sql)
1467    }
1468
1469    #[doc(hidden)]
1470    /// Hook to insert "OUTPUT" expressions.
1471    fn prepare_output(&self, _returning: &Option<ReturningClause>, _sql: &mut dyn SqlWriter) {}
1472
1473    #[doc(hidden)]
1474    /// Hook to insert "RETURNING" statements.
1475    fn prepare_returning(&self, returning: &Option<ReturningClause>, sql: &mut dyn SqlWriter) {
1476        if let Some(returning) = returning {
1477            sql.write_str(" RETURNING ").unwrap();
1478            match &returning {
1479                ReturningClause::All => sql.write_str("*").unwrap(),
1480                ReturningClause::Columns(cols) => {
1481                    let mut cols_iter = cols.iter();
1482                    join_io!(
1483                        cols_iter,
1484                        column_ref,
1485                        join {
1486                            sql.write_str(", ").unwrap();
1487                        },
1488                        do {
1489                            self.prepare_column_ref(column_ref, sql);
1490                        }
1491                    );
1492                }
1493                ReturningClause::Exprs(exprs) => {
1494                    let mut exprs_iter = exprs.iter();
1495                    join_io!(
1496                        exprs_iter,
1497                        expr,
1498                        join {
1499                            sql.write_str(", ").unwrap();
1500                        },
1501                        do {
1502                            self.prepare_simple_expr(expr, sql);
1503                        }
1504                    );
1505                }
1506            }
1507        }
1508    }
1509
1510    #[doc(hidden)]
1511    /// Translate a condition to a "WHERE" clause.
1512    fn prepare_condition(
1513        &self,
1514        condition: &ConditionHolder,
1515        keyword: &str,
1516        sql: &mut dyn SqlWriter,
1517    ) {
1518        match &condition.contents {
1519            ConditionHolderContents::Empty => (),
1520            ConditionHolderContents::Chain(conditions) => {
1521                sql.write_str(" ").unwrap();
1522                sql.write_str(keyword).unwrap();
1523                sql.write_str(" ").unwrap();
1524                for (i, log_chain_oper) in conditions.iter().enumerate() {
1525                    self.prepare_logical_chain_oper(log_chain_oper, i, conditions.len(), sql);
1526                }
1527            }
1528            ConditionHolderContents::Condition(c) => {
1529                sql.write_str(" ").unwrap();
1530                sql.write_str(keyword).unwrap();
1531                sql.write_str(" ").unwrap();
1532                self.prepare_condition_where(c, sql);
1533            }
1534        }
1535    }
1536
1537    #[doc(hidden)]
1538    /// Translate part of a condition to part of a "WHERE" clause.
1539    fn prepare_condition_where(&self, condition: &Condition, sql: &mut dyn SqlWriter) {
1540        let simple_expr = condition.clone().into();
1541        self.prepare_simple_expr(&simple_expr, sql);
1542    }
1543
1544    #[doc(hidden)]
1545    /// Translate [`Frame`] into SQL statement.
1546    fn prepare_frame(&self, frame: &Frame, sql: &mut dyn SqlWriter) {
1547        match *frame {
1548            Frame::UnboundedPreceding => sql.write_str("UNBOUNDED PRECEDING").unwrap(),
1549            Frame::Preceding(v) => {
1550                self.prepare_value(v.into(), sql);
1551                sql.write_str("PRECEDING").unwrap();
1552            }
1553            Frame::CurrentRow => sql.write_str("CURRENT ROW").unwrap(),
1554            Frame::Following(v) => {
1555                self.prepare_value(v.into(), sql);
1556                sql.write_str("FOLLOWING").unwrap();
1557            }
1558            Frame::UnboundedFollowing => sql.write_str("UNBOUNDED FOLLOWING").unwrap(),
1559        }
1560    }
1561
1562    #[doc(hidden)]
1563    /// Translate [`WindowStatement`] into SQL statement.
1564    fn prepare_window_statement(&self, window: &WindowStatement, sql: &mut dyn SqlWriter) {
1565        let mut partition_iter = window.partition_by.iter();
1566        join_io!(
1567            partition_iter,
1568            expr,
1569            first {
1570                sql.write_str("PARTITION BY ").unwrap();
1571            },
1572            join {
1573                sql.write_str(", ").unwrap();
1574            },
1575            do {
1576                self.prepare_simple_expr(expr, sql);
1577            }
1578        );
1579
1580        let mut order_iter = window.order_by.iter();
1581        join_io!(
1582            order_iter,
1583            expr,
1584            first {
1585                sql.write_str(" ORDER BY ").unwrap();
1586            },
1587            join {
1588                sql.write_str(", ").unwrap();
1589            },
1590            do {
1591                self.prepare_order_expr(expr, sql);
1592            }
1593        );
1594
1595        if let Some(frame) = &window.frame {
1596            match frame.r#type {
1597                FrameType::Range => sql.write_str(" RANGE ").unwrap(),
1598                FrameType::Rows => sql.write_str(" ROWS ").unwrap(),
1599            };
1600            if let Some(end) = &frame.end {
1601                sql.write_str("BETWEEN ").unwrap();
1602                self.prepare_frame(&frame.start, sql);
1603                sql.write_str(" AND ").unwrap();
1604                self.prepare_frame(end, sql);
1605            } else {
1606                self.prepare_frame(&frame.start, sql);
1607            }
1608        }
1609    }
1610
1611    #[doc(hidden)]
1612    /// Translate a binary expr to SQL.
1613    fn binary_expr(&self, left: &Expr, op: &BinOper, right: &Expr, sql: &mut dyn SqlWriter) {
1614        // If left has higher precedence than op, we can drop parentheses around left
1615        let drop_left_higher_precedence =
1616            self.inner_expr_well_known_greater_precedence(left, &(*op).into());
1617
1618        // Figure out if left associativity rules allow us to drop the left parenthesis
1619        let drop_left_assoc = left.is_binary()
1620            && op == left.get_bin_oper().unwrap()
1621            && self.well_known_left_associative(op);
1622
1623        let left_paren = !drop_left_higher_precedence && !drop_left_assoc;
1624        if left_paren {
1625            sql.write_str("(").unwrap();
1626        }
1627        self.prepare_simple_expr(left, sql);
1628        if left_paren {
1629            sql.write_str(")").unwrap();
1630        }
1631
1632        sql.write_str(" ").unwrap();
1633        self.prepare_bin_oper(op, sql);
1634        sql.write_str(" ").unwrap();
1635
1636        // If right has higher precedence than op, we can drop parentheses around right
1637        let drop_right_higher_precedence =
1638            self.inner_expr_well_known_greater_precedence(right, &(*op).into());
1639
1640        let op_as_oper = Oper::BinOper(*op);
1641        // Due to representation of trinary op between as nested binary ops.
1642        let drop_right_between_hack = op_as_oper.is_between()
1643            && right.is_binary()
1644            && matches!(right.get_bin_oper(), Some(&BinOper::And));
1645
1646        // Due to representation of trinary op like/not like with optional arg escape as nested binary ops.
1647        let drop_right_escape_hack = op_as_oper.is_like()
1648            && right.is_binary()
1649            && matches!(right.get_bin_oper(), Some(&BinOper::Escape));
1650
1651        // Due to custom representation of casting AS datatype
1652        let drop_right_as_hack = (op == &BinOper::As) && matches!(right, Expr::Custom(_));
1653
1654        let right_paren = !drop_right_higher_precedence
1655            && !drop_right_escape_hack
1656            && !drop_right_between_hack
1657            && !drop_right_as_hack;
1658        if right_paren {
1659            sql.write_str("(").unwrap();
1660        }
1661        self.prepare_simple_expr(right, sql);
1662        if right_paren {
1663            sql.write_str(")").unwrap();
1664        }
1665    }
1666
1667    fn write_string_quoted(&self, string: &str, buffer: &mut dyn Write) {
1668        buffer.write_str("'").unwrap();
1669        self.write_escaped(buffer, string);
1670        buffer.write_str("'").unwrap();
1671    }
1672
1673    #[doc(hidden)]
1674    /// Write bytes enclosed with engine specific byte syntax
1675    fn write_bytes(&self, bytes: &[u8], buffer: &mut dyn Write) {
1676        buffer.write_str("x'").unwrap();
1677        for b in bytes {
1678            write!(buffer, "{b:02X}").unwrap()
1679        }
1680        buffer.write_str("'").unwrap();
1681    }
1682
1683    #[doc(hidden)]
1684    /// The name of the function that represents the "if null" condition.
1685    fn if_null_function(&self) -> &str {
1686        "IFNULL"
1687    }
1688
1689    #[doc(hidden)]
1690    /// The name of the function that represents the "greatest" function.
1691    fn greatest_function(&self) -> &str {
1692        "GREATEST"
1693    }
1694
1695    #[doc(hidden)]
1696    /// The name of the function that represents the "least" function.
1697    fn least_function(&self) -> &str {
1698        "LEAST"
1699    }
1700
1701    #[doc(hidden)]
1702    /// The name of the function that returns the char length.
1703    fn char_length_function(&self) -> &str {
1704        "CHAR_LENGTH"
1705    }
1706
1707    #[doc(hidden)]
1708    /// The name of the function that returns a random number
1709    fn random_function(&self) -> &str {
1710        // Returning it with parens as part of the name because the tuple preparer can't deal with empty lists
1711        "RANDOM"
1712    }
1713
1714    /// The keywords for insert default row.
1715    fn insert_default_keyword(&self) -> &str {
1716        "(DEFAULT)"
1717    }
1718
1719    /// Write insert default rows expression.
1720    fn insert_default_values(&self, num_rows: u32, sql: &mut dyn SqlWriter) {
1721        sql.write_str("VALUES ").unwrap();
1722        if num_rows > 0 {
1723            sql.write_str(self.insert_default_keyword()).unwrap();
1724
1725            for _ in 1..num_rows {
1726                sql.write_str(", ").unwrap();
1727                sql.write_str(self.insert_default_keyword()).unwrap();
1728            }
1729        }
1730    }
1731
1732    /// Write TRUE constant
1733    fn prepare_constant_true(&self, sql: &mut dyn SqlWriter) {
1734        self.prepare_constant(&true.into(), sql);
1735    }
1736
1737    /// Write FALSE constant
1738    fn prepare_constant_false(&self, sql: &mut dyn SqlWriter) {
1739        self.prepare_constant(&false.into(), sql);
1740    }
1741}
1742
1743impl SubQueryStatement {
1744    pub(crate) fn prepare_statement(
1745        &self,
1746        query_builder: &dyn QueryBuilder,
1747        sql: &mut dyn SqlWriter,
1748    ) {
1749        use SubQueryStatement::*;
1750        match self {
1751            SelectStatement(stmt) => query_builder.prepare_select_statement(stmt, sql),
1752            InsertStatement(stmt) => query_builder.prepare_insert_statement(stmt, sql),
1753            UpdateStatement(stmt) => query_builder.prepare_update_statement(stmt, sql),
1754            DeleteStatement(stmt) => query_builder.prepare_delete_statement(stmt, sql),
1755            WithStatement(stmt) => query_builder.prepare_with_query(stmt, sql),
1756        }
1757    }
1758}
1759
1760pub(crate) struct CommonSqlQueryBuilder;
1761
1762impl OperLeftAssocDecider for CommonSqlQueryBuilder {
1763    fn well_known_left_associative(&self, op: &BinOper) -> bool {
1764        common_well_known_left_associative(op)
1765    }
1766}
1767
1768impl PrecedenceDecider for CommonSqlQueryBuilder {
1769    fn inner_expr_well_known_greater_precedence(&self, inner: &Expr, outer_oper: &Oper) -> bool {
1770        common_inner_expr_well_known_greater_precedence(inner, outer_oper)
1771    }
1772}
1773
1774impl QueryBuilder for CommonSqlQueryBuilder {
1775    fn prepare_query_statement(&self, query: &SubQueryStatement, sql: &mut dyn SqlWriter) {
1776        query.prepare_statement(self, sql);
1777    }
1778
1779    fn prepare_value(&self, value: Value, sql: &mut dyn SqlWriter) {
1780        sql.push_param(value, self as _);
1781    }
1782}
1783
1784impl QuotedBuilder for CommonSqlQueryBuilder {
1785    fn quote(&self) -> Quote {
1786        QUOTE
1787    }
1788}
1789
1790impl EscapeBuilder for CommonSqlQueryBuilder {}
1791
1792impl TableRefBuilder for CommonSqlQueryBuilder {}
1793
1794#[cfg_attr(
1795    feature = "option-more-parentheses",
1796    allow(unreachable_code, unused_variables)
1797)]
1798pub(crate) fn common_inner_expr_well_known_greater_precedence(
1799    inner: &Expr,
1800    outer_oper: &Oper,
1801) -> bool {
1802    match inner {
1803        // We only consider the case where an inner expression is contained in either a
1804        // unary or binary expression (with an outer_oper).
1805        // We do not need to wrap with parentheses:
1806        // Columns, tuples (already wrapped), constants, function calls, values,
1807        // keywords, subqueries (already wrapped), case (already wrapped)
1808        Expr::Column(_)
1809        | Expr::Tuple(_)
1810        | Expr::Constant(_)
1811        | Expr::FunctionCall(_)
1812        | Expr::Value(_)
1813        | Expr::Keyword(_)
1814        | Expr::Case(_)
1815        | Expr::SubQuery(_, _)
1816        | Expr::TypeName(_) => true,
1817        Expr::Binary(_, inner_oper, _) => {
1818            #[cfg(feature = "option-more-parentheses")]
1819            {
1820                return false;
1821            }
1822            let inner_oper: Oper = (*inner_oper).into();
1823            if inner_oper.is_arithmetic() || inner_oper.is_shift() {
1824                outer_oper.is_comparison()
1825                    || outer_oper.is_between()
1826                    || outer_oper.is_in()
1827                    || outer_oper.is_like()
1828                    || outer_oper.is_logical()
1829            } else if inner_oper.is_comparison()
1830                || inner_oper.is_in()
1831                || inner_oper.is_like()
1832                || inner_oper.is_is()
1833            {
1834                outer_oper.is_logical()
1835            } else {
1836                false
1837            }
1838        }
1839        _ => false,
1840    }
1841}
1842
1843pub(crate) fn common_well_known_left_associative(op: &BinOper) -> bool {
1844    matches!(
1845        op,
1846        BinOper::And | BinOper::Or | BinOper::Add | BinOper::Sub | BinOper::Mul | BinOper::Mod
1847    )
1848}
1849
1850macro_rules! join_io {
1851    ($iter:ident, $item:ident $(, first $first:expr)?, join $join:expr, do $do:expr $(, last $last:expr)?) => {
1852        if let Some($item) = $iter.next() {
1853            $($first)?
1854            $do
1855
1856            for $item in $iter {
1857                $join
1858                $do
1859            }
1860
1861            $($last)?
1862        }
1863    };
1864}
1865
1866pub(crate) use join_io;
1867
1868#[cfg(test)]
1869mod tests {
1870    #[cfg(feature = "with-chrono")]
1871    use chrono::{DateTime, FixedOffset, NaiveDate, NaiveDateTime, NaiveTime, Utc};
1872
1873    #[cfg(feature = "with-chrono")]
1874    use crate::{MysqlQueryBuilder, PostgresQueryBuilder, QueryBuilder, SqliteQueryBuilder};
1875
1876    /// [Postgresql reference](https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIMES)
1877    ///
1878    /// [Mysql reference](https://dev.mysql.com/doc/refman/8.4/en/fractional-seconds.html)
1879    ///
1880    /// [Sqlite reference](https://sqlite.org/lang_datefunc.html)
1881    #[test]
1882    #[cfg(feature = "with-chrono")]
1883    fn format_time_constant() {
1884        use crate::{MysqlQueryBuilder, PostgresQueryBuilder, QueryBuilder, SqliteQueryBuilder};
1885
1886        let time = NaiveTime::from_hms_micro_opt(1, 2, 3, 123456)
1887            .unwrap()
1888            .into();
1889
1890        let mut string = String::new();
1891        macro_rules! compare {
1892            ($a:ident, $b:literal) => {
1893                PostgresQueryBuilder.prepare_constant(&$a, &mut string);
1894                assert_eq!(string, $b);
1895
1896                string.clear();
1897
1898                MysqlQueryBuilder.prepare_constant(&$a, &mut string);
1899                assert_eq!(string, $b);
1900
1901                string.clear();
1902
1903                SqliteQueryBuilder.prepare_constant(&$a, &mut string);
1904                assert_eq!(string, $b);
1905
1906                string.clear();
1907            };
1908        }
1909
1910        compare!(time, "'01:02:03.123456'");
1911
1912        let d = NaiveDate::from_ymd_opt(2015, 6, 3).unwrap();
1913        let t = NaiveTime::from_hms_micro_opt(12, 34, 56, 123456).unwrap();
1914
1915        let dt = NaiveDateTime::new(d, t);
1916
1917        let date_time = dt.into();
1918
1919        compare!(date_time, "'2015-06-03 12:34:56.123456'");
1920
1921        let date_time_utc = DateTime::<Utc>::from_naive_utc_and_offset(dt, Utc).into();
1922
1923        compare!(date_time_utc, "'2015-06-03 12:34:56.123456 +00:00'");
1924
1925        let date_time_tz = DateTime::<FixedOffset>::from_naive_utc_and_offset(
1926            dt,
1927            FixedOffset::east_opt(8 * 3600).unwrap(),
1928        )
1929        .into();
1930
1931        compare!(date_time_tz, "'2015-06-03 20:34:56.123456 +08:00'");
1932    }
1933}
sea_query/backend/query_builder.rs

sea_query/backend/
query_builder.rs
