sea_query/backend/
query_builder.rs

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

sea_query/backend/
query_builder.rs
