sea_query/backend/
query_builder.rs

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

sea_query/backend/
query_builder.rs
