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

sea_query/backend/
query_builder.rs
