sea_query/backend/
query_builder.rs

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

sea_query/backend/
query_builder.rs
