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

sea_query/backend/
query_builder.rs
