1use crate::ast::*;
2use crate::dialects::Dialect;
3
4pub struct Generator {
10 output: String,
11 pretty: bool,
13 indent: usize,
14 dialect: Option<Dialect>,
16}
17
18impl Generator {
19 #[must_use]
20 pub fn new() -> Self {
21 Self {
22 output: String::new(),
23 pretty: false,
24 indent: 0,
25 dialect: None,
26 }
27 }
28
29 #[must_use]
31 pub fn pretty() -> Self {
32 Self {
33 output: String::new(),
34 pretty: true,
35 indent: 0,
36 dialect: None,
37 }
38 }
39
40 #[must_use]
42 pub fn with_dialect(dialect: Dialect) -> Self {
43 Self {
44 output: String::new(),
45 pretty: false,
46 indent: 0,
47 dialect: Some(dialect),
48 }
49 }
50
51 #[must_use]
53 pub fn generate(&mut self, statement: &Statement) -> String {
54 self.output.clear();
55 self.gen_statement(statement);
56 self.output.clone()
57 }
58
59 #[must_use]
61 pub fn expr_to_sql(expr: &Expr) -> String {
62 let mut g = Self::new();
63 g.gen_expr(expr);
64 g.output
65 }
66
67 fn write(&mut self, s: &str) {
68 self.output.push_str(s);
69 }
70
71 fn newline(&mut self) {
73 if self.pretty {
74 self.output.push('\n');
75 for _ in 0..self.indent {
76 self.output.push_str(" ");
77 }
78 }
79 }
80
81 fn sep(&mut self) {
83 if self.pretty {
84 self.newline();
85 } else {
86 self.output.push(' ');
87 }
88 }
89
90 fn indent_up(&mut self) {
91 self.indent += 1;
92 }
93
94 fn indent_down(&mut self) {
95 self.indent = self.indent.saturating_sub(1);
96 }
97
98 fn write_keyword(&mut self, s: &str) {
99 self.write(s);
100 }
101
102 fn write_quoted(&mut self, name: &str, style: QuoteStyle) {
106 let effective_style = if style.is_quoted() {
107 if let Some(dialect) = self.dialect {
108 QuoteStyle::for_dialect(dialect)
109 } else {
110 style
111 }
112 } else {
113 style
114 };
115 match effective_style {
116 QuoteStyle::None => self.write(name),
117 QuoteStyle::DoubleQuote => {
118 self.write("\"");
119 self.write(&name.replace('"', "\"\""));
120 self.write("\"");
121 }
122 QuoteStyle::Backtick => {
123 self.write("`");
124 self.write(&name.replace('`', "``"));
125 self.write("`");
126 }
127 QuoteStyle::Bracket => {
128 self.write("[");
129 self.write(&name.replace(']', "]]"));
130 self.write("]");
131 }
132 }
133 }
134
135 fn gen_comments(&mut self, comments: &[String]) {
143 for comment in comments {
144 let normalized = self.normalize_comment(comment);
145 self.write(&normalized);
146 self.newline_or_space();
147 }
148 }
149
150 fn normalize_comment(&self, comment: &str) -> String {
153 if comment.starts_with('#') {
154 let is_mysql_target = matches!(
155 self.dialect,
156 Some(
157 Dialect::Mysql
158 | Dialect::Doris
159 | Dialect::SingleStore
160 | Dialect::StarRocks
161 )
162 );
163 if !is_mysql_target {
164 return format!("--{}", &comment[1..]);
165 }
166 }
167 comment.to_string()
168 }
169
170 fn newline_or_space(&mut self) {
172 if self.pretty {
173 self.output.push('\n');
174 for _ in 0..self.indent {
175 self.output.push_str(" ");
176 }
177 } else {
178 self.output.push('\n');
179 }
180 }
181
182 fn gen_statement(&mut self, statement: &Statement) {
183 match statement {
185 Statement::Select(s) => {
186 self.gen_comments(&s.comments);
187 self.gen_select(s);
188 }
189 Statement::Insert(s) => {
190 self.gen_comments(&s.comments);
191 self.gen_insert(s);
192 }
193 Statement::Update(s) => {
194 self.gen_comments(&s.comments);
195 self.gen_update(s);
196 }
197 Statement::Delete(s) => {
198 self.gen_comments(&s.comments);
199 self.gen_delete(s);
200 }
201 Statement::CreateTable(s) => {
202 self.gen_comments(&s.comments);
203 self.gen_create_table(s);
204 }
205 Statement::DropTable(s) => {
206 self.gen_comments(&s.comments);
207 self.gen_drop_table(s);
208 }
209 Statement::SetOperation(s) => {
210 self.gen_comments(&s.comments);
211 self.gen_set_operation(s);
212 }
213 Statement::AlterTable(s) => {
214 self.gen_comments(&s.comments);
215 self.gen_alter_table(s);
216 }
217 Statement::CreateView(s) => {
218 self.gen_comments(&s.comments);
219 self.gen_create_view(s);
220 }
221 Statement::DropView(s) => {
222 self.gen_comments(&s.comments);
223 self.gen_drop_view(s);
224 }
225 Statement::Truncate(s) => {
226 self.gen_comments(&s.comments);
227 self.gen_truncate(s);
228 }
229 Statement::Transaction(s) => self.gen_transaction(s),
230 Statement::Explain(s) => {
231 self.gen_comments(&s.comments);
232 self.gen_explain(s);
233 }
234 Statement::Use(s) => {
235 self.gen_comments(&s.comments);
236 self.gen_use(s);
237 }
238 Statement::Merge(s) => {
239 self.gen_comments(&s.comments);
240 self.gen_merge(s);
241 }
242 Statement::Expression(e) => self.gen_expr(e),
243 }
244 }
245
246 fn gen_select(&mut self, sel: &SelectStatement) {
249 if !sel.ctes.is_empty() {
251 self.gen_ctes(&sel.ctes);
252 self.sep();
253 }
254
255 self.write_keyword("SELECT");
256 if sel.distinct {
257 self.write(" ");
258 self.write_keyword("DISTINCT");
259 }
260 if let Some(top) = &sel.top {
261 self.write(" ");
262 self.write_keyword("TOP ");
263 self.gen_expr(top);
264 }
265
266 if self.pretty {
268 self.indent_up();
269 for (i, item) in sel.columns.iter().enumerate() {
270 self.newline();
271 self.gen_select_item(item);
272 if i < sel.columns.len() - 1 {
273 self.write(",");
274 }
275 }
276 self.indent_down();
277 } else {
278 self.write(" ");
279 for (i, item) in sel.columns.iter().enumerate() {
280 if i > 0 {
281 self.write(", ");
282 }
283 self.gen_select_item(item);
284 }
285 }
286
287 if let Some(from) = &sel.from {
288 self.sep();
289 self.write_keyword("FROM");
290 if self.pretty {
291 self.indent_up();
292 self.newline();
293 self.gen_table_source(&from.source);
294 self.indent_down();
295 } else {
296 self.write(" ");
297 self.gen_table_source(&from.source);
298 }
299 }
300
301 for join in &sel.joins {
302 self.gen_join(join);
303 }
304
305 if let Some(wh) = &sel.where_clause {
306 self.sep();
307 self.write_keyword("WHERE");
308 if self.pretty {
309 self.indent_up();
310 self.newline();
311 self.gen_expr(wh);
312 self.indent_down();
313 } else {
314 self.write(" ");
315 self.gen_expr(wh);
316 }
317 }
318
319 if !sel.group_by.is_empty() {
320 self.sep();
321 self.write_keyword("GROUP BY");
322 if self.pretty {
323 self.indent_up();
324 self.newline();
325 self.gen_expr_list(&sel.group_by);
326 self.indent_down();
327 } else {
328 self.write(" ");
329 self.gen_expr_list(&sel.group_by);
330 }
331 }
332
333 if let Some(having) = &sel.having {
334 self.sep();
335 self.write_keyword("HAVING");
336 if self.pretty {
337 self.indent_up();
338 self.newline();
339 self.gen_expr(having);
340 self.indent_down();
341 } else {
342 self.write(" ");
343 self.gen_expr(having);
344 }
345 }
346
347 if let Some(qualify) = &sel.qualify {
348 self.sep();
349 self.write_keyword("QUALIFY");
350 if self.pretty {
351 self.indent_up();
352 self.newline();
353 self.gen_expr(qualify);
354 self.indent_down();
355 } else {
356 self.write(" ");
357 self.gen_expr(qualify);
358 }
359 }
360
361 if !sel.window_definitions.is_empty() {
362 self.sep();
363 self.write_keyword("WINDOW ");
364 for (i, wd) in sel.window_definitions.iter().enumerate() {
365 if i > 0 {
366 self.write(", ");
367 }
368 self.write(&wd.name);
369 self.write(" AS (");
370 self.gen_window_spec(&wd.spec);
371 self.write(")");
372 }
373 }
374
375 self.gen_order_by(&sel.order_by);
376
377 if let Some(limit) = &sel.limit {
378 self.sep();
379 self.write_keyword("LIMIT ");
380 self.gen_expr(limit);
381 }
382
383 if let Some(offset) = &sel.offset {
384 self.sep();
385 self.write_keyword("OFFSET ");
386 self.gen_expr(offset);
387 }
388
389 if let Some(fetch) = &sel.fetch_first {
390 self.sep();
391 self.write_keyword("FETCH FIRST ");
392 self.gen_expr(fetch);
393 self.write(" ");
394 self.write_keyword("ROWS ONLY");
395 }
396 }
397
398 fn gen_ctes(&mut self, ctes: &[Cte]) {
399 self.write_keyword("WITH ");
400 if ctes.iter().any(|c| c.recursive) {
401 self.write_keyword("RECURSIVE ");
402 }
403 for (i, cte) in ctes.iter().enumerate() {
404 if i > 0 {
405 self.write(",");
406 self.sep();
407 }
408 self.write_quoted(&cte.name, cte.name_quote_style);
409 if !cte.columns.is_empty() {
410 self.write("(");
411 self.write(&cte.columns.join(", "));
412 self.write(")");
413 }
414 self.write(" ");
415 self.write_keyword("AS ");
416 if let Some(true) = cte.materialized {
417 self.write_keyword("MATERIALIZED ");
418 } else if let Some(false) = cte.materialized {
419 self.write_keyword("NOT MATERIALIZED ");
420 }
421 self.write("(");
422 if self.pretty {
423 self.indent_up();
424 self.newline();
425 self.gen_statement(&cte.query);
426 self.indent_down();
427 self.newline();
428 } else {
429 self.gen_statement(&cte.query);
430 }
431 self.write(")");
432 }
433 }
434
435 fn gen_select_item(&mut self, item: &SelectItem) {
436 match item {
437 SelectItem::Wildcard => self.write("*"),
438 SelectItem::QualifiedWildcard { table } => {
439 self.write(table);
440 self.write(".*");
441 }
442 SelectItem::Expr { expr, alias, alias_quote_style } => {
443 self.gen_expr(expr);
444 if let Some(alias) = alias {
445 self.write(" ");
446 self.write_keyword("AS ");
447 self.write_quoted(alias, *alias_quote_style);
448 }
449 }
450 }
451 }
452
453 fn gen_table_source(&mut self, source: &TableSource) {
454 match source {
455 TableSource::Table(table_ref) => self.gen_table_ref(table_ref),
456 TableSource::Subquery { query, alias, alias_quote_style } => {
457 self.write("(");
458 self.gen_statement(query);
459 self.write(")");
460 if let Some(alias) = alias {
461 self.write(" ");
462 if !self.omit_table_alias_as() {
463 self.write_keyword("AS ");
464 }
465 self.write_quoted(alias, *alias_quote_style);
466 }
467 }
468 TableSource::TableFunction { name, args, alias, alias_quote_style } => {
469 self.write(name);
470 self.write("(");
471 self.gen_expr_list(args);
472 self.write(")");
473 if let Some(alias) = alias {
474 self.write(" ");
475 if !self.omit_table_alias_as() {
476 self.write_keyword("AS ");
477 }
478 self.write_quoted(alias, *alias_quote_style);
479 }
480 }
481 TableSource::Lateral { source } => {
482 self.write_keyword("LATERAL ");
483 self.gen_table_source(source);
484 }
485 TableSource::Unnest {
486 expr,
487 alias,
488 alias_quote_style,
489 with_offset,
490 } => {
491 self.write_keyword("UNNEST(");
492 self.gen_expr(expr);
493 self.write(")");
494 if let Some(alias) = alias {
495 self.write(" ");
496 if !self.omit_table_alias_as() {
497 self.write_keyword("AS ");
498 }
499 self.write_quoted(alias, *alias_quote_style);
500 }
501 if *with_offset {
502 self.write(" ");
503 self.write_keyword("WITH OFFSET");
504 }
505 }
506 TableSource::Pivot {
507 source,
508 aggregate,
509 for_column,
510 in_values,
511 alias,
512 alias_quote_style,
513 } => {
514 self.gen_table_source(source);
515 self.write(" ");
516 self.write_keyword("PIVOT");
517 self.write(" (");
518 self.gen_expr(aggregate);
519 self.write(" ");
520 self.write_keyword("FOR ");
521 self.write(for_column);
522 self.write(" ");
523 self.write_keyword("IN ");
524 self.write("(");
525 self.gen_pivot_values(in_values);
526 self.write("))");
527 if let Some(alias) = alias {
528 self.write(" ");
529 if !self.omit_table_alias_as() {
530 self.write_keyword("AS ");
531 }
532 self.write_quoted(alias, *alias_quote_style);
533 }
534 }
535 TableSource::Unpivot {
536 source,
537 value_column,
538 for_column,
539 in_columns,
540 alias,
541 alias_quote_style,
542 } => {
543 self.gen_table_source(source);
544 self.write(" ");
545 self.write_keyword("UNPIVOT");
546 self.write(" (");
547 self.write(value_column);
548 self.write(" ");
549 self.write_keyword("FOR ");
550 self.write(for_column);
551 self.write(" ");
552 self.write_keyword("IN ");
553 self.write("(");
554 self.gen_pivot_values(in_columns);
555 self.write("))");
556 if let Some(alias) = alias {
557 self.write(" ");
558 if !self.omit_table_alias_as() {
559 self.write_keyword("AS ");
560 }
561 self.write_quoted(alias, *alias_quote_style);
562 }
563 }
564 }
565 }
566
567 fn gen_pivot_values(&mut self, values: &[PivotValue]) {
568 for (i, pv) in values.iter().enumerate() {
569 if i > 0 {
570 self.write(", ");
571 }
572 self.gen_expr(&pv.value);
573 if let Some(alias) = &pv.alias {
574 self.write(" ");
575 self.write_keyword("AS ");
576 self.write_quoted(alias, pv.alias_quote_style);
577 }
578 }
579 }
580
581 fn omit_table_alias_as(&self) -> bool {
583 matches!(self.dialect, Some(Dialect::Oracle))
584 }
585
586 fn gen_table_ref(&mut self, table: &TableRef) {
587 if let Some(catalog) = &table.catalog {
588 self.write(catalog);
589 self.write(".");
590 }
591 if let Some(schema) = &table.schema {
592 self.write(schema);
593 self.write(".");
594 }
595 self.write_quoted(&table.name, table.name_quote_style);
596 if let Some(alias) = &table.alias {
597 self.write(" ");
598 if !self.omit_table_alias_as() {
599 self.write_keyword("AS ");
600 }
601 self.write_quoted(alias, table.alias_quote_style);
602 }
603 }
604
605 fn gen_join(&mut self, join: &JoinClause) {
606 let join_kw = match join.join_type {
607 JoinType::Inner => "INNER JOIN",
608 JoinType::Left => "LEFT JOIN",
609 JoinType::Right => "RIGHT JOIN",
610 JoinType::Full => "FULL JOIN",
611 JoinType::Cross => "CROSS JOIN",
612 JoinType::Natural => "NATURAL JOIN",
613 JoinType::Lateral => "LATERAL JOIN",
614 };
615 self.sep();
616 self.write_keyword(join_kw);
617 if self.pretty {
618 self.indent_up();
619 self.newline();
620 self.gen_table_source(&join.table);
621 } else {
622 self.write(" ");
623 self.gen_table_source(&join.table);
624 }
625 if let Some(on) = &join.on {
626 if self.pretty {
627 self.newline();
628 } else {
629 self.write(" ");
630 }
631 self.write_keyword("ON ");
632 self.gen_expr(on);
633 }
634 if !join.using.is_empty() {
635 if self.pretty {
636 self.newline();
637 } else {
638 self.write(" ");
639 }
640 self.write_keyword("USING (");
641 self.write(&join.using.join(", "));
642 self.write(")");
643 }
644 if self.pretty {
645 self.indent_down();
646 }
647 }
648
649 fn gen_order_by(&mut self, items: &[OrderByItem]) {
650 if items.is_empty() {
651 return;
652 }
653 self.sep();
654 self.write_keyword("ORDER BY");
655 if self.pretty {
656 self.indent_up();
657 self.newline();
658 } else {
659 self.write(" ");
660 }
661 for (i, item) in items.iter().enumerate() {
662 if i > 0 {
663 self.write(", ");
664 }
665 self.gen_expr(&item.expr);
666 if !item.ascending {
667 self.write(" ");
668 self.write_keyword("DESC");
669 }
670 if let Some(nulls_first) = item.nulls_first {
671 if nulls_first {
672 self.write(" ");
673 self.write_keyword("NULLS FIRST");
674 } else {
675 self.write(" ");
676 self.write_keyword("NULLS LAST");
677 }
678 }
679 }
680 if self.pretty {
681 self.indent_down();
682 }
683 }
684
685 fn gen_set_operation(&mut self, sop: &SetOperationStatement) {
688 self.gen_statement(&sop.left);
689 let op_kw = match sop.op {
690 SetOperationType::Union => "UNION",
691 SetOperationType::Intersect => "INTERSECT",
692 SetOperationType::Except => "EXCEPT",
693 };
694 self.sep();
695 self.write_keyword(op_kw);
696 if sop.all {
697 self.write(" ");
698 self.write_keyword("ALL");
699 }
700 self.sep();
701 self.gen_statement(&sop.right);
702
703 self.gen_order_by(&sop.order_by);
704
705 if let Some(limit) = &sop.limit {
706 self.sep();
707 self.write_keyword("LIMIT ");
708 self.gen_expr(limit);
709 }
710 if let Some(offset) = &sop.offset {
711 self.sep();
712 self.write_keyword("OFFSET ");
713 self.gen_expr(offset);
714 }
715 }
716
717 fn gen_insert(&mut self, ins: &InsertStatement) {
720 self.write_keyword("INSERT INTO ");
721 self.gen_table_ref(&ins.table);
722
723 if !ins.columns.is_empty() {
724 self.write(" (");
725 self.write(&ins.columns.join(", "));
726 self.write(")");
727 }
728
729 match &ins.source {
730 InsertSource::Values(rows) => {
731 self.sep();
732 self.write_keyword("VALUES");
733 if self.pretty {
734 self.indent_up();
735 for (i, row) in rows.iter().enumerate() {
736 self.newline();
737 self.write("(");
738 self.gen_expr_list(row);
739 self.write(")");
740 if i < rows.len() - 1 {
741 self.write(",");
742 }
743 }
744 self.indent_down();
745 } else {
746 self.write(" ");
747 for (i, row) in rows.iter().enumerate() {
748 if i > 0 {
749 self.write(", ");
750 }
751 self.write("(");
752 self.gen_expr_list(row);
753 self.write(")");
754 }
755 }
756 }
757 InsertSource::Query(query) => {
758 self.sep();
759 self.gen_statement(query);
760 }
761 InsertSource::Default => {
762 self.sep();
763 self.write_keyword("DEFAULT VALUES");
764 }
765 }
766
767 if let Some(on_conflict) = &ins.on_conflict {
768 self.sep();
769 self.write_keyword("ON CONFLICT");
770 if !on_conflict.columns.is_empty() {
771 self.write(" (");
772 self.write(&on_conflict.columns.join(", "));
773 self.write(")");
774 }
775 match &on_conflict.action {
776 ConflictAction::DoNothing => {
777 self.write(" ");
778 self.write_keyword("DO NOTHING");
779 }
780 ConflictAction::DoUpdate(assignments) => {
781 self.write(" ");
782 self.write_keyword("DO UPDATE SET ");
783 for (i, (col, val)) in assignments.iter().enumerate() {
784 if i > 0 {
785 self.write(", ");
786 }
787 self.write(col);
788 self.write(" = ");
789 self.gen_expr(val);
790 }
791 }
792 }
793 }
794
795 if !ins.returning.is_empty() {
796 self.sep();
797 self.write_keyword("RETURNING ");
798 for (i, item) in ins.returning.iter().enumerate() {
799 if i > 0 {
800 self.write(", ");
801 }
802 self.gen_select_item(item);
803 }
804 }
805 }
806
807 fn gen_update(&mut self, upd: &UpdateStatement) {
810 self.write_keyword("UPDATE ");
811 self.gen_table_ref(&upd.table);
812 self.sep();
813 self.write_keyword("SET");
814
815 if self.pretty {
816 self.indent_up();
817 for (i, (col, val)) in upd.assignments.iter().enumerate() {
818 self.newline();
819 self.write(col);
820 self.write(" = ");
821 self.gen_expr(val);
822 if i < upd.assignments.len() - 1 {
823 self.write(",");
824 }
825 }
826 self.indent_down();
827 } else {
828 self.write(" ");
829 for (i, (col, val)) in upd.assignments.iter().enumerate() {
830 if i > 0 {
831 self.write(", ");
832 }
833 self.write(col);
834 self.write(" = ");
835 self.gen_expr(val);
836 }
837 }
838
839 if let Some(from) = &upd.from {
840 self.sep();
841 self.write_keyword("FROM ");
842 self.gen_table_source(&from.source);
843 }
844
845 if let Some(wh) = &upd.where_clause {
846 self.sep();
847 self.write_keyword("WHERE");
848 if self.pretty {
849 self.indent_up();
850 self.newline();
851 self.gen_expr(wh);
852 self.indent_down();
853 } else {
854 self.write(" ");
855 self.gen_expr(wh);
856 }
857 }
858
859 if !upd.returning.is_empty() {
860 self.sep();
861 self.write_keyword("RETURNING ");
862 for (i, item) in upd.returning.iter().enumerate() {
863 if i > 0 {
864 self.write(", ");
865 }
866 self.gen_select_item(item);
867 }
868 }
869 }
870
871 fn gen_delete(&mut self, del: &DeleteStatement) {
874 self.write_keyword("DELETE FROM ");
875 self.gen_table_ref(&del.table);
876
877 if let Some(using) = &del.using {
878 self.sep();
879 self.write_keyword("USING ");
880 self.gen_table_source(&using.source);
881 }
882
883 if let Some(wh) = &del.where_clause {
884 self.sep();
885 self.write_keyword("WHERE");
886 if self.pretty {
887 self.indent_up();
888 self.newline();
889 self.gen_expr(wh);
890 self.indent_down();
891 } else {
892 self.write(" ");
893 self.gen_expr(wh);
894 }
895 }
896
897 if !del.returning.is_empty() {
898 self.sep();
899 self.write_keyword("RETURNING ");
900 for (i, item) in del.returning.iter().enumerate() {
901 if i > 0 {
902 self.write(", ");
903 }
904 self.gen_select_item(item);
905 }
906 }
907 }
908
909 fn gen_merge(&mut self, merge: &MergeStatement) {
914 self.write_keyword("MERGE INTO ");
915 self.gen_table_ref(&merge.target);
916
917 self.sep();
918 self.write_keyword("USING ");
919 self.gen_table_source(&merge.source);
920
921 self.sep();
922 self.write_keyword("ON");
923 if self.pretty {
924 self.indent_up();
925 self.newline();
926 self.gen_expr(&merge.on);
927 self.indent_down();
928 } else {
929 self.write(" ");
930 self.gen_expr(&merge.on);
931 }
932
933 for clause in &merge.clauses {
934 self.sep();
935 self.gen_merge_clause(clause);
936 }
937
938 if !merge.output.is_empty() {
939 self.sep();
940 self.write_keyword("OUTPUT ");
941 for (i, item) in merge.output.iter().enumerate() {
942 if i > 0 {
943 self.write(", ");
944 }
945 self.gen_select_item(item);
946 }
947 }
948 }
949
950 fn gen_merge_clause(&mut self, clause: &MergeClause) {
951 self.write_keyword("WHEN ");
952 match &clause.kind {
953 MergeClauseKind::Matched => self.write_keyword("MATCHED"),
954 MergeClauseKind::NotMatched => self.write_keyword("NOT MATCHED"),
955 MergeClauseKind::NotMatchedBySource => self.write_keyword("NOT MATCHED BY SOURCE"),
956 }
957
958 if let Some(cond) = &clause.condition {
959 self.write_keyword(" AND ");
960 self.gen_expr(cond);
961 }
962
963 self.write_keyword(" THEN");
964
965 match &clause.action {
966 MergeAction::Update(assignments) => {
967 self.sep();
968 self.write_keyword("UPDATE SET");
969 if self.pretty {
970 self.indent_up();
971 for (i, (col, val)) in assignments.iter().enumerate() {
972 self.newline();
973 self.write(col);
974 self.write(" = ");
975 self.gen_expr(val);
976 if i < assignments.len() - 1 {
977 self.write(",");
978 }
979 }
980 self.indent_down();
981 } else {
982 self.write(" ");
983 for (i, (col, val)) in assignments.iter().enumerate() {
984 if i > 0 {
985 self.write(", ");
986 }
987 self.write(col);
988 self.write(" = ");
989 self.gen_expr(val);
990 }
991 }
992 }
993 MergeAction::Insert { columns, values } => {
994 self.sep();
995 self.write_keyword("INSERT");
996 if !columns.is_empty() {
997 self.write(" (");
998 self.write(&columns.join(", "));
999 self.write(")");
1000 }
1001 self.write_keyword(" VALUES");
1002 self.write(" (");
1003 for (i, val) in values.iter().enumerate() {
1004 if i > 0 {
1005 self.write(", ");
1006 }
1007 self.gen_expr(val);
1008 }
1009 self.write(")");
1010 }
1011 MergeAction::InsertRow => {
1012 self.sep();
1013 self.write_keyword("INSERT ROW");
1014 }
1015 MergeAction::Delete => {
1016 self.sep();
1017 self.write_keyword("DELETE");
1018 }
1019 }
1020 }
1021
1022 fn gen_create_table(&mut self, ct: &CreateTableStatement) {
1025 self.write_keyword("CREATE ");
1026 if ct.temporary {
1027 self.write_keyword("TEMPORARY ");
1028 }
1029 self.write_keyword("TABLE ");
1030 if ct.if_not_exists {
1031 self.write_keyword("IF NOT EXISTS ");
1032 }
1033 self.gen_table_ref(&ct.table);
1034
1035 if let Some(as_select) = &ct.as_select {
1036 self.write(" ");
1037 self.write_keyword("AS ");
1038 self.gen_statement(as_select);
1039 return;
1040 }
1041
1042 self.write(" (");
1043
1044 if self.pretty {
1045 self.indent_up();
1046 for (i, col) in ct.columns.iter().enumerate() {
1047 self.newline();
1048 self.gen_column_def(col);
1049 if i < ct.columns.len() - 1 || !ct.constraints.is_empty() {
1050 self.write(",");
1051 }
1052 }
1053 for (i, constraint) in ct.constraints.iter().enumerate() {
1054 self.newline();
1055 self.gen_table_constraint(constraint);
1056 if i < ct.constraints.len() - 1 {
1057 self.write(",");
1058 }
1059 }
1060 self.indent_down();
1061 self.newline();
1062 } else {
1063 for (i, col) in ct.columns.iter().enumerate() {
1064 if i > 0 {
1065 self.write(", ");
1066 }
1067 self.gen_column_def(col);
1068 }
1069 for (i, constraint) in ct.constraints.iter().enumerate() {
1070 if i + ct.columns.len() > 0 {
1071 self.write(", ");
1072 }
1073 self.gen_table_constraint(constraint);
1074 }
1075 }
1076
1077 self.write(")");
1078 }
1079
1080 fn gen_column_def(&mut self, col: &ColumnDef) {
1081 self.write(&col.name);
1082 self.write(" ");
1083 self.gen_data_type(&col.data_type);
1084
1085 if col.primary_key {
1086 self.write(" ");
1087 self.write_keyword("PRIMARY KEY");
1088 }
1089 if col.unique {
1090 self.write(" ");
1091 self.write_keyword("UNIQUE");
1092 }
1093 if col.auto_increment {
1094 self.write(" ");
1095 self.write_keyword("AUTOINCREMENT");
1096 }
1097
1098 match col.nullable {
1099 Some(false) => {
1100 self.write(" ");
1101 self.write_keyword("NOT NULL");
1102 }
1103 Some(true) => {
1104 self.write(" ");
1105 self.write_keyword("NULL");
1106 }
1107 None => {}
1108 }
1109
1110 if let Some(default) = &col.default {
1111 self.write(" ");
1112 self.write_keyword("DEFAULT ");
1113 self.gen_expr(default);
1114 }
1115
1116 if let Some(collation) = &col.collation {
1117 self.write(" ");
1118 self.write_keyword("COLLATE ");
1119 self.write(collation);
1120 }
1121
1122 if let Some(comment) = &col.comment {
1123 self.write(" ");
1124 self.write_keyword("COMMENT '");
1125 self.write(&comment.replace('\'', "''"));
1126 self.write("'");
1127 }
1128 }
1129
1130 fn gen_table_constraint(&mut self, constraint: &TableConstraint) {
1131 match constraint {
1132 TableConstraint::PrimaryKey { name, columns } => {
1133 if let Some(name) = name {
1134 self.write_keyword("CONSTRAINT ");
1135 self.write(name);
1136 self.write(" ");
1137 }
1138 self.write_keyword("PRIMARY KEY (");
1139 self.write(&columns.join(", "));
1140 self.write(")");
1141 }
1142 TableConstraint::Unique { name, columns } => {
1143 if let Some(name) = name {
1144 self.write_keyword("CONSTRAINT ");
1145 self.write(name);
1146 self.write(" ");
1147 }
1148 self.write_keyword("UNIQUE (");
1149 self.write(&columns.join(", "));
1150 self.write(")");
1151 }
1152 TableConstraint::ForeignKey {
1153 name,
1154 columns,
1155 ref_table,
1156 ref_columns,
1157 on_delete,
1158 on_update,
1159 } => {
1160 if let Some(name) = name {
1161 self.write_keyword("CONSTRAINT ");
1162 self.write(name);
1163 self.write(" ");
1164 }
1165 self.write_keyword("FOREIGN KEY (");
1166 self.write(&columns.join(", "));
1167 self.write(") ");
1168 self.write_keyword("REFERENCES ");
1169 self.gen_table_ref(ref_table);
1170 self.write(" (");
1171 self.write(&ref_columns.join(", "));
1172 self.write(")");
1173 if let Some(action) = on_delete {
1174 self.write(" ");
1175 self.write_keyword("ON DELETE ");
1176 self.gen_referential_action(action);
1177 }
1178 if let Some(action) = on_update {
1179 self.write(" ");
1180 self.write_keyword("ON UPDATE ");
1181 self.gen_referential_action(action);
1182 }
1183 }
1184 TableConstraint::Check { name, expr } => {
1185 if let Some(name) = name {
1186 self.write_keyword("CONSTRAINT ");
1187 self.write(name);
1188 self.write(" ");
1189 }
1190 self.write_keyword("CHECK (");
1191 self.gen_expr(expr);
1192 self.write(")");
1193 }
1194 }
1195 }
1196
1197 fn gen_referential_action(&mut self, action: &ReferentialAction) {
1198 match action {
1199 ReferentialAction::Cascade => self.write_keyword("CASCADE"),
1200 ReferentialAction::Restrict => self.write_keyword("RESTRICT"),
1201 ReferentialAction::NoAction => self.write_keyword("NO ACTION"),
1202 ReferentialAction::SetNull => self.write_keyword("SET NULL"),
1203 ReferentialAction::SetDefault => self.write_keyword("SET DEFAULT"),
1204 }
1205 }
1206
1207 fn gen_drop_table(&mut self, dt: &DropTableStatement) {
1210 self.write_keyword("DROP TABLE ");
1211 if dt.if_exists {
1212 self.write_keyword("IF EXISTS ");
1213 }
1214 self.gen_table_ref(&dt.table);
1215 if dt.cascade {
1216 self.write(" ");
1217 self.write_keyword("CASCADE");
1218 }
1219 }
1220
1221 fn gen_alter_table(&mut self, alt: &AlterTableStatement) {
1224 self.write_keyword("ALTER TABLE ");
1225 self.gen_table_ref(&alt.table);
1226
1227 for (i, action) in alt.actions.iter().enumerate() {
1228 if i > 0 {
1229 self.write(",");
1230 }
1231 self.write(" ");
1232 match action {
1233 AlterTableAction::AddColumn(col) => {
1234 self.write_keyword("ADD COLUMN ");
1235 self.gen_column_def(col);
1236 }
1237 AlterTableAction::DropColumn { name, if_exists } => {
1238 self.write_keyword("DROP COLUMN ");
1239 if *if_exists {
1240 self.write_keyword("IF EXISTS ");
1241 }
1242 self.write(name);
1243 }
1244 AlterTableAction::RenameColumn { old_name, new_name } => {
1245 self.write_keyword("RENAME COLUMN ");
1246 self.write(old_name);
1247 self.write(" ");
1248 self.write_keyword("TO ");
1249 self.write(new_name);
1250 }
1251 AlterTableAction::AlterColumnType { name, data_type } => {
1252 self.write_keyword("ALTER COLUMN ");
1253 self.write(name);
1254 self.write(" ");
1255 self.write_keyword("TYPE ");
1256 self.gen_data_type(data_type);
1257 }
1258 AlterTableAction::AddConstraint(constraint) => {
1259 self.write_keyword("ADD ");
1260 self.gen_table_constraint(constraint);
1261 }
1262 AlterTableAction::DropConstraint { name } => {
1263 self.write_keyword("DROP CONSTRAINT ");
1264 self.write(name);
1265 }
1266 AlterTableAction::RenameTable { new_name } => {
1267 self.write_keyword("RENAME TO ");
1268 self.write(new_name);
1269 }
1270 }
1271 }
1272 }
1273
1274 fn gen_create_view(&mut self, cv: &CreateViewStatement) {
1277 self.write_keyword("CREATE ");
1278 if cv.or_replace {
1279 self.write_keyword("OR REPLACE ");
1280 }
1281 if cv.materialized {
1282 self.write_keyword("MATERIALIZED ");
1283 }
1284 self.write_keyword("VIEW ");
1285 if cv.if_not_exists {
1286 self.write_keyword("IF NOT EXISTS ");
1287 }
1288 self.gen_table_ref(&cv.name);
1289
1290 if !cv.columns.is_empty() {
1291 self.write(" (");
1292 self.write(&cv.columns.join(", "));
1293 self.write(")");
1294 }
1295
1296 self.write(" ");
1297 self.write_keyword("AS ");
1298 self.gen_statement(&cv.query);
1299 }
1300
1301 fn gen_drop_view(&mut self, dv: &DropViewStatement) {
1302 self.write_keyword("DROP ");
1303 if dv.materialized {
1304 self.write_keyword("MATERIALIZED ");
1305 }
1306 self.write_keyword("VIEW ");
1307 if dv.if_exists {
1308 self.write_keyword("IF EXISTS ");
1309 }
1310 self.gen_table_ref(&dv.name);
1311 }
1312
1313 fn gen_truncate(&mut self, t: &TruncateStatement) {
1316 self.write_keyword("TRUNCATE TABLE ");
1317 self.gen_table_ref(&t.table);
1318 }
1319
1320 fn gen_transaction(&mut self, t: &TransactionStatement) {
1323 match t {
1324 TransactionStatement::Begin => self.write_keyword("BEGIN"),
1325 TransactionStatement::Commit => self.write_keyword("COMMIT"),
1326 TransactionStatement::Rollback => self.write_keyword("ROLLBACK"),
1327 TransactionStatement::Savepoint(name) => {
1328 self.write_keyword("SAVEPOINT ");
1329 self.write(name);
1330 }
1331 TransactionStatement::ReleaseSavepoint(name) => {
1332 self.write_keyword("RELEASE SAVEPOINT ");
1333 self.write(name);
1334 }
1335 TransactionStatement::RollbackTo(name) => {
1336 self.write_keyword("ROLLBACK TO SAVEPOINT ");
1337 self.write(name);
1338 }
1339 }
1340 }
1341
1342 fn gen_explain(&mut self, e: &ExplainStatement) {
1345 self.write_keyword("EXPLAIN ");
1346 if e.analyze {
1347 self.write_keyword("ANALYZE ");
1348 }
1349 self.gen_statement(&e.statement);
1350 }
1351
1352 fn gen_use(&mut self, u: &UseStatement) {
1355 self.write_keyword("USE ");
1356 self.write(&u.name);
1357 }
1358
1359 fn gen_data_type(&mut self, dt: &DataType) {
1364 match dt {
1365 DataType::TinyInt => self.write("TINYINT"),
1366 DataType::SmallInt => self.write("SMALLINT"),
1367 DataType::Int => self.write("INT"),
1368 DataType::BigInt => self.write("BIGINT"),
1369 DataType::Float => self.write("FLOAT"),
1370 DataType::Double => self.write("DOUBLE"),
1371 DataType::Real => self.write("REAL"),
1372 DataType::Decimal { precision, scale } | DataType::Numeric { precision, scale } => {
1373 self.write(if matches!(dt, DataType::Numeric { .. }) {
1374 "NUMERIC"
1375 } else {
1376 "DECIMAL"
1377 });
1378 if let Some(p) = precision {
1379 self.write(&format!("({p}"));
1380 if let Some(s) = scale {
1381 self.write(&format!(", {s}"));
1382 }
1383 self.write(")");
1384 }
1385 }
1386 DataType::Varchar(len) => {
1387 self.write("VARCHAR");
1388 if let Some(n) = len {
1389 self.write(&format!("({n})"));
1390 }
1391 }
1392 DataType::Char(len) => {
1393 self.write("CHAR");
1394 if let Some(n) = len {
1395 self.write(&format!("({n})"));
1396 }
1397 }
1398 DataType::Text => self.write("TEXT"),
1399 DataType::String => self.write("STRING"),
1400 DataType::Binary(len) => {
1401 self.write("BINARY");
1402 if let Some(n) = len {
1403 self.write(&format!("({n})"));
1404 }
1405 }
1406 DataType::Varbinary(len) => {
1407 self.write("VARBINARY");
1408 if let Some(n) = len {
1409 self.write(&format!("({n})"));
1410 }
1411 }
1412 DataType::Boolean => self.write("BOOLEAN"),
1413 DataType::Date => self.write("DATE"),
1414 DataType::Time { precision } => {
1415 self.write("TIME");
1416 if let Some(p) = precision {
1417 self.write(&format!("({p})"));
1418 }
1419 }
1420 DataType::Timestamp { precision, with_tz } => {
1421 self.write("TIMESTAMP");
1422 if let Some(p) = precision {
1423 self.write(&format!("({p})"));
1424 }
1425 if *with_tz {
1426 self.write(" WITH TIME ZONE");
1427 }
1428 }
1429 DataType::Interval => self.write("INTERVAL"),
1430 DataType::DateTime => self.write("DATETIME"),
1431 DataType::Blob => self.write("BLOB"),
1432 DataType::Bytea => self.write("BYTEA"),
1433 DataType::Bytes => self.write("BYTES"),
1434 DataType::Json => self.write("JSON"),
1435 DataType::Jsonb => self.write("JSONB"),
1436 DataType::Uuid => self.write("UUID"),
1437 DataType::Array(inner) => {
1438 let is_postgres = matches!(
1439 self.dialect,
1440 Some(
1441 Dialect::Postgres
1442 | Dialect::Redshift
1443 | Dialect::Materialize
1444 | Dialect::RisingWave
1445 )
1446 );
1447 if is_postgres {
1448 if let Some(inner) = inner {
1450 self.gen_data_type(inner);
1451 self.write("[]");
1452 } else {
1453 self.write("ARRAY");
1454 }
1455 } else {
1456 self.write("ARRAY");
1457 if let Some(inner) = inner {
1458 self.write("<");
1459 self.gen_data_type(inner);
1460 self.write(">");
1461 }
1462 }
1463 }
1464 DataType::Map { key, value } => {
1465 self.write("MAP<");
1466 self.gen_data_type(key);
1467 self.write(", ");
1468 self.gen_data_type(value);
1469 self.write(">");
1470 }
1471 DataType::Struct(fields) => {
1472 self.write("STRUCT<");
1473 for (i, (name, dt)) in fields.iter().enumerate() {
1474 if i > 0 {
1475 self.write(", ");
1476 }
1477 self.write(name);
1478 self.write(" ");
1479 self.gen_data_type(dt);
1480 }
1481 self.write(">");
1482 }
1483 DataType::Tuple(types) => {
1484 self.write("TUPLE(");
1485 for (i, dt) in types.iter().enumerate() {
1486 if i > 0 {
1487 self.write(", ");
1488 }
1489 self.gen_data_type(dt);
1490 }
1491 self.write(")");
1492 }
1493 DataType::Null => self.write("NULL"),
1494 DataType::Variant => self.write("VARIANT"),
1495 DataType::Object => self.write("OBJECT"),
1496 DataType::Xml => self.write("XML"),
1497 DataType::Inet => self.write("INET"),
1498 DataType::Cidr => self.write("CIDR"),
1499 DataType::Macaddr => self.write("MACADDR"),
1500 DataType::Bit(len) => {
1501 self.write("BIT");
1502 if let Some(n) = len {
1503 self.write(&format!("({n})"));
1504 }
1505 }
1506 DataType::Money => self.write("MONEY"),
1507 DataType::Serial => self.write("SERIAL"),
1508 DataType::BigSerial => self.write("BIGSERIAL"),
1509 DataType::SmallSerial => self.write("SMALLSERIAL"),
1510 DataType::Regclass => self.write("REGCLASS"),
1511 DataType::Regtype => self.write("REGTYPE"),
1512 DataType::Hstore => self.write("HSTORE"),
1513 DataType::Geography => self.write("GEOGRAPHY"),
1514 DataType::Geometry => self.write("GEOMETRY"),
1515 DataType::Super => self.write("SUPER"),
1516 DataType::Unknown(name) => self.write(name),
1517 }
1518 }
1519
1520 fn binary_op_str(op: &BinaryOperator) -> &'static str {
1525 match op {
1526 BinaryOperator::Plus => " + ",
1527 BinaryOperator::Minus => " - ",
1528 BinaryOperator::Multiply => " * ",
1529 BinaryOperator::Divide => " / ",
1530 BinaryOperator::Modulo => " % ",
1531 BinaryOperator::Eq => " = ",
1532 BinaryOperator::Neq => " <> ",
1533 BinaryOperator::Lt => " < ",
1534 BinaryOperator::Gt => " > ",
1535 BinaryOperator::LtEq => " <= ",
1536 BinaryOperator::GtEq => " >= ",
1537 BinaryOperator::And => " AND ",
1538 BinaryOperator::Or => " OR ",
1539 BinaryOperator::Xor => " XOR ",
1540 BinaryOperator::Concat => " || ",
1541 BinaryOperator::BitwiseAnd => " & ",
1542 BinaryOperator::BitwiseOr => " | ",
1543 BinaryOperator::BitwiseXor => " ^ ",
1544 BinaryOperator::ShiftLeft => " << ",
1545 BinaryOperator::ShiftRight => " >> ",
1546 BinaryOperator::Arrow => " -> ",
1547 BinaryOperator::DoubleArrow => " ->> ",
1548 }
1549 }
1550
1551 fn gen_expr_list(&mut self, exprs: &[Expr]) {
1552 for (i, expr) in exprs.iter().enumerate() {
1553 if i > 0 {
1554 self.write(", ");
1555 }
1556 self.gen_expr(expr);
1557 }
1558 }
1559
1560 fn gen_expr(&mut self, expr: &Expr) {
1561 match expr {
1562 Expr::Column {
1563 table,
1564 name,
1565 quote_style,
1566 table_quote_style,
1567 } => {
1568 if let Some(t) = table {
1569 self.write_quoted(t, *table_quote_style);
1570 self.write(".");
1571 }
1572 self.write_quoted(name, *quote_style);
1573 }
1574 Expr::Number(n) => self.write(n),
1575 Expr::StringLiteral(s) => {
1576 self.write("'");
1577 self.write(&s.replace('\'', "''"));
1578 self.write("'");
1579 }
1580 Expr::Boolean(b) => self.write(if *b { "TRUE" } else { "FALSE" }),
1581 Expr::Null => self.write("NULL"),
1582 Expr::Default => self.write_keyword("DEFAULT"),
1583 Expr::Wildcard | Expr::Star => self.write("*"),
1584
1585 Expr::Cube { exprs } => {
1586 self.write_keyword("CUBE");
1587 self.write("(");
1588 self.gen_expr_list(exprs);
1589 self.write(")");
1590 }
1591 Expr::Rollup { exprs } => {
1592 self.write_keyword("ROLLUP");
1593 self.write("(");
1594 self.gen_expr_list(exprs);
1595 self.write(")");
1596 }
1597 Expr::GroupingSets { sets } => {
1598 self.write_keyword("GROUPING SETS");
1599 self.write("(");
1600 self.gen_expr_list(sets);
1601 self.write(")");
1602 }
1603
1604 Expr::BinaryOp { left, op, right } => {
1605 self.gen_expr(left);
1606 self.write(Self::binary_op_str(op));
1607 self.gen_expr(right);
1608 }
1609 Expr::AnyOp { expr, op, right } => {
1610 self.gen_expr(expr);
1611 self.write(Self::binary_op_str(op));
1612 self.write_keyword("ANY");
1613 self.write("(");
1614 if let Expr::Subquery(query) = right.as_ref() {
1615 self.gen_statement(query);
1616 } else {
1617 self.gen_expr(right);
1618 }
1619 self.write(")");
1620 }
1621 Expr::AllOp { expr, op, right } => {
1622 self.gen_expr(expr);
1623 self.write(Self::binary_op_str(op));
1624 self.write_keyword("ALL");
1625 self.write("(");
1626 if let Expr::Subquery(query) = right.as_ref() {
1627 self.gen_statement(query);
1628 } else {
1629 self.gen_expr(right);
1630 }
1631 self.write(")");
1632 }
1633 Expr::UnaryOp { op, expr } => {
1634 let op_str = match op {
1635 UnaryOperator::Not => "NOT ",
1636 UnaryOperator::Minus => "-",
1637 UnaryOperator::Plus => "+",
1638 UnaryOperator::BitwiseNot => "~",
1639 };
1640 self.write(op_str);
1641 self.gen_expr(expr);
1642 }
1643 Expr::Function {
1644 name,
1645 args,
1646 distinct,
1647 filter,
1648 over,
1649 } => {
1650 self.write(name);
1651 self.write("(");
1652 if *distinct {
1653 self.write_keyword("DISTINCT ");
1654 }
1655 self.gen_expr_list(args);
1656 self.write(")");
1657
1658 if let Some(filter_expr) = filter {
1659 self.write(" ");
1660 self.write_keyword("FILTER (WHERE ");
1661 self.gen_expr(filter_expr);
1662 self.write(")");
1663 }
1664 if let Some(spec) = over {
1665 self.write(" ");
1666 self.write_keyword("OVER ");
1667 if let Some(wref) = &spec.window_ref {
1668 if spec.partition_by.is_empty()
1669 && spec.order_by.is_empty()
1670 && spec.frame.is_none()
1671 {
1672 self.write(wref);
1673 } else {
1674 self.write("(");
1675 self.gen_window_spec(spec);
1676 self.write(")");
1677 }
1678 } else {
1679 self.write("(");
1680 self.gen_window_spec(spec);
1681 self.write(")");
1682 }
1683 }
1684 }
1685 Expr::Between {
1686 expr,
1687 low,
1688 high,
1689 negated,
1690 } => {
1691 self.gen_expr(expr);
1692 if *negated {
1693 self.write(" ");
1694 self.write_keyword("NOT");
1695 }
1696 self.write(" ");
1697 self.write_keyword("BETWEEN ");
1698 self.gen_expr(low);
1699 self.write(" ");
1700 self.write_keyword("AND ");
1701 self.gen_expr(high);
1702 }
1703 Expr::InList {
1704 expr,
1705 list,
1706 negated,
1707 } => {
1708 self.gen_expr(expr);
1709 if *negated {
1710 self.write(" ");
1711 self.write_keyword("NOT");
1712 }
1713 self.write(" ");
1714 self.write_keyword("IN (");
1715 self.gen_expr_list(list);
1716 self.write(")");
1717 }
1718 Expr::InSubquery {
1719 expr,
1720 subquery,
1721 negated,
1722 } => {
1723 self.gen_expr(expr);
1724 if *negated {
1725 self.write(" ");
1726 self.write_keyword("NOT");
1727 }
1728 self.write(" ");
1729 self.write_keyword("IN (");
1730 self.gen_statement(subquery);
1731 self.write(")");
1732 }
1733 Expr::IsNull { expr, negated } => {
1734 self.gen_expr(expr);
1735 if *negated {
1736 self.write(" ");
1737 self.write_keyword("IS NOT NULL");
1738 } else {
1739 self.write(" ");
1740 self.write_keyword("IS NULL");
1741 }
1742 }
1743 Expr::IsBool {
1744 expr,
1745 value,
1746 negated,
1747 } => {
1748 self.gen_expr(expr);
1749 self.write(" ");
1750 match (negated, value) {
1751 (false, true) => self.write_keyword("IS TRUE"),
1752 (false, false) => self.write_keyword("IS FALSE"),
1753 (true, true) => self.write_keyword("IS NOT TRUE"),
1754 (true, false) => self.write_keyword("IS NOT FALSE"),
1755 }
1756 }
1757 Expr::Like {
1758 expr,
1759 pattern,
1760 negated,
1761 escape,
1762 } => {
1763 self.gen_expr(expr);
1764 if *negated {
1765 self.write(" ");
1766 self.write_keyword("NOT");
1767 }
1768 self.write(" ");
1769 self.write_keyword("LIKE ");
1770 self.gen_expr(pattern);
1771 if let Some(esc) = escape {
1772 self.write(" ");
1773 self.write_keyword("ESCAPE ");
1774 self.gen_expr(esc);
1775 }
1776 }
1777 Expr::ILike {
1778 expr,
1779 pattern,
1780 negated,
1781 escape,
1782 } => {
1783 self.gen_expr(expr);
1784 if *negated {
1785 self.write(" ");
1786 self.write_keyword("NOT");
1787 }
1788 self.write(" ");
1789 self.write_keyword("ILIKE ");
1790 self.gen_expr(pattern);
1791 if let Some(esc) = escape {
1792 self.write(" ");
1793 self.write_keyword("ESCAPE ");
1794 self.gen_expr(esc);
1795 }
1796 }
1797 Expr::Case {
1798 operand,
1799 when_clauses,
1800 else_clause,
1801 } => {
1802 self.write_keyword("CASE");
1803 if let Some(op) = operand {
1804 self.write(" ");
1805 self.gen_expr(op);
1806 }
1807 for (cond, result) in when_clauses {
1808 self.write(" ");
1809 self.write_keyword("WHEN ");
1810 self.gen_expr(cond);
1811 self.write(" ");
1812 self.write_keyword("THEN ");
1813 self.gen_expr(result);
1814 }
1815 if let Some(el) = else_clause {
1816 self.write(" ");
1817 self.write_keyword("ELSE ");
1818 self.gen_expr(el);
1819 }
1820 self.write(" ");
1821 self.write_keyword("END");
1822 }
1823 Expr::Nested(inner) => {
1824 self.write("(");
1825 self.gen_expr(inner);
1826 self.write(")");
1827 }
1828 Expr::Subquery(query) => {
1829 self.write("(");
1830 self.gen_statement(query);
1831 self.write(")");
1832 }
1833 Expr::Exists { subquery, negated } => {
1834 if *negated {
1835 self.write_keyword("NOT ");
1836 }
1837 self.write_keyword("EXISTS (");
1838 self.gen_statement(subquery);
1839 self.write(")");
1840 }
1841 Expr::Cast { expr, data_type } => {
1842 let is_postgres = matches!(
1843 self.dialect,
1844 Some(
1845 Dialect::Postgres
1846 | Dialect::Redshift
1847 | Dialect::Materialize
1848 | Dialect::RisingWave
1849 )
1850 );
1851 if is_postgres {
1852 self.gen_expr(expr);
1853 self.write("::");
1854 self.gen_data_type(data_type);
1855 } else {
1856 self.write_keyword("CAST(");
1857 self.gen_expr(expr);
1858 self.write(" ");
1859 self.write_keyword("AS ");
1860 self.gen_data_type(data_type);
1861 self.write(")");
1862 }
1863 }
1864 Expr::TryCast { expr, data_type } => {
1865 self.write_keyword("TRY_CAST(");
1866 self.gen_expr(expr);
1867 self.write(" ");
1868 self.write_keyword("AS ");
1869 self.gen_data_type(data_type);
1870 self.write(")");
1871 }
1872 Expr::Extract { field, expr } => {
1873 self.write_keyword("EXTRACT(");
1874 self.gen_datetime_field(field);
1875 self.write(" ");
1876 self.write_keyword("FROM ");
1877 self.gen_expr(expr);
1878 self.write(")");
1879 }
1880 Expr::Interval { value, unit } => {
1881 self.write_keyword("INTERVAL ");
1882 self.gen_expr(value);
1883 if let Some(unit) = unit {
1884 self.write(" ");
1885 self.gen_datetime_field(unit);
1886 }
1887 }
1888 Expr::ArrayLiteral(items) => {
1889 self.write_keyword("ARRAY[");
1890 self.gen_expr_list(items);
1891 self.write("]");
1892 }
1893 Expr::Tuple(items) => {
1894 self.write("(");
1895 self.gen_expr_list(items);
1896 self.write(")");
1897 }
1898 Expr::Coalesce(items) => {
1899 self.write_keyword("COALESCE(");
1900 self.gen_expr_list(items);
1901 self.write(")");
1902 }
1903 Expr::If {
1904 condition,
1905 true_val,
1906 false_val,
1907 } => {
1908 self.write_keyword("IF(");
1909 self.gen_expr(condition);
1910 self.write(", ");
1911 self.gen_expr(true_val);
1912 if let Some(fv) = false_val {
1913 self.write(", ");
1914 self.gen_expr(fv);
1915 }
1916 self.write(")");
1917 }
1918 Expr::NullIf { expr, r#else } => {
1919 self.write_keyword("NULLIF(");
1920 self.gen_expr(expr);
1921 self.write(", ");
1922 self.gen_expr(r#else);
1923 self.write(")");
1924 }
1925 Expr::Collate { expr, collation } => {
1926 self.gen_expr(expr);
1927 self.write(" ");
1928 self.write_keyword("COLLATE ");
1929 self.write(collation);
1930 }
1931 Expr::Parameter(p) => self.write(p),
1932 Expr::TypeExpr(dt) => self.gen_data_type(dt),
1933 Expr::QualifiedWildcard { table } => {
1934 self.write(table);
1935 self.write(".*");
1936 }
1937 Expr::Alias { expr, name } => {
1938 self.gen_expr(expr);
1939 self.write(" ");
1940 self.write_keyword("AS ");
1941 self.write(name);
1942 }
1943 Expr::ArrayIndex { expr, index } => {
1944 self.gen_expr(expr);
1945 self.write("[");
1946 self.gen_expr(index);
1947 self.write("]");
1948 }
1949 Expr::JsonAccess {
1950 expr,
1951 path,
1952 as_text,
1953 } => {
1954 self.gen_expr(expr);
1955 if *as_text {
1956 self.write("->>");
1957 } else {
1958 self.write("->");
1959 }
1960 self.gen_expr(path);
1961 }
1962 Expr::Lambda { params, body } => {
1963 if params.len() == 1 {
1964 self.write(¶ms[0]);
1965 } else {
1966 self.write("(");
1967 self.write(¶ms.join(", "));
1968 self.write(")");
1969 }
1970 self.write(" -> ");
1971 self.gen_expr(body);
1972 }
1973 Expr::TypedFunction { func, filter, over } => {
1974 self.gen_typed_function(func);
1975
1976 if let Some(filter_expr) = filter {
1977 self.write(" ");
1978 self.write_keyword("FILTER (WHERE ");
1979 self.gen_expr(filter_expr);
1980 self.write(")");
1981 }
1982 if let Some(spec) = over {
1983 self.write(" ");
1984 self.write_keyword("OVER ");
1985 if let Some(wref) = &spec.window_ref {
1986 if spec.partition_by.is_empty()
1987 && spec.order_by.is_empty()
1988 && spec.frame.is_none()
1989 {
1990 self.write(wref);
1991 } else {
1992 self.write("(");
1993 self.gen_window_spec(spec);
1994 self.write(")");
1995 }
1996 } else {
1997 self.write("(");
1998 self.gen_window_spec(spec);
1999 self.write(")");
2000 }
2001 }
2002 }
2003 Expr::Commented { expr, comments } => {
2004 for comment in comments {
2005 let normalized = self.normalize_comment(comment);
2006 self.write(&normalized);
2007 self.write(" ");
2008 }
2009 self.gen_expr(expr);
2010 }
2011 }
2012 }
2013
2014 fn gen_window_spec(&mut self, spec: &WindowSpec) {
2015 if let Some(wref) = &spec.window_ref {
2016 self.write(wref);
2017 if !spec.partition_by.is_empty() || !spec.order_by.is_empty() || spec.frame.is_some() {
2018 self.write(" ");
2019 }
2020 }
2021 if !spec.partition_by.is_empty() {
2022 self.write_keyword("PARTITION BY ");
2023 self.gen_expr_list(&spec.partition_by);
2024 }
2025 if !spec.order_by.is_empty() {
2026 if !spec.partition_by.is_empty() {
2027 self.write(" ");
2028 }
2029 self.write_keyword("ORDER BY ");
2030 for (i, item) in spec.order_by.iter().enumerate() {
2031 if i > 0 {
2032 self.write(", ");
2033 }
2034 self.gen_expr(&item.expr);
2035 if !item.ascending {
2036 self.write(" ");
2037 self.write_keyword("DESC");
2038 }
2039 if let Some(nulls_first) = item.nulls_first {
2040 if nulls_first {
2041 self.write(" ");
2042 self.write_keyword("NULLS FIRST");
2043 } else {
2044 self.write(" ");
2045 self.write_keyword("NULLS LAST");
2046 }
2047 }
2048 }
2049 }
2050 if let Some(frame) = &spec.frame {
2051 self.write(" ");
2052 self.gen_window_frame(frame);
2053 }
2054 }
2055
2056 fn gen_window_frame(&mut self, frame: &WindowFrame) {
2057 match frame.kind {
2058 WindowFrameKind::Rows => self.write_keyword("ROWS "),
2059 WindowFrameKind::Range => self.write_keyword("RANGE "),
2060 WindowFrameKind::Groups => self.write_keyword("GROUPS "),
2061 }
2062 if let Some(end) = &frame.end {
2063 self.write_keyword("BETWEEN ");
2064 self.gen_window_frame_bound(&frame.start);
2065 self.write(" ");
2066 self.write_keyword("AND ");
2067 self.gen_window_frame_bound(end);
2068 } else {
2069 self.gen_window_frame_bound(&frame.start);
2070 }
2071 }
2072
2073 fn gen_window_frame_bound(&mut self, bound: &WindowFrameBound) {
2074 match bound {
2075 WindowFrameBound::CurrentRow => self.write_keyword("CURRENT ROW"),
2076 WindowFrameBound::Preceding(None) => self.write_keyword("UNBOUNDED PRECEDING"),
2077 WindowFrameBound::Preceding(Some(n)) => {
2078 self.gen_expr(n);
2079 self.write(" ");
2080 self.write_keyword("PRECEDING");
2081 }
2082 WindowFrameBound::Following(None) => self.write_keyword("UNBOUNDED FOLLOWING"),
2083 WindowFrameBound::Following(Some(n)) => {
2084 self.gen_expr(n);
2085 self.write(" ");
2086 self.write_keyword("FOLLOWING");
2087 }
2088 }
2089 }
2090
2091 fn gen_datetime_field(&mut self, field: &DateTimeField) {
2092 let name = match field {
2093 DateTimeField::Year => "YEAR",
2094 DateTimeField::Quarter => "QUARTER",
2095 DateTimeField::Month => "MONTH",
2096 DateTimeField::Week => "WEEK",
2097 DateTimeField::Day => "DAY",
2098 DateTimeField::DayOfWeek => "DOW",
2099 DateTimeField::DayOfYear => "DOY",
2100 DateTimeField::Hour => "HOUR",
2101 DateTimeField::Minute => "MINUTE",
2102 DateTimeField::Second => "SECOND",
2103 DateTimeField::Millisecond => "MILLISECOND",
2104 DateTimeField::Microsecond => "MICROSECOND",
2105 DateTimeField::Nanosecond => "NANOSECOND",
2106 DateTimeField::Epoch => "EPOCH",
2107 DateTimeField::Timezone => "TIMEZONE",
2108 DateTimeField::TimezoneHour => "TIMEZONE_HOUR",
2109 DateTimeField::TimezoneMinute => "TIMEZONE_MINUTE",
2110 };
2111 self.write(name);
2112 }
2113
2114 fn gen_typed_function(&mut self, func: &TypedFunction) {
2116 let dialect = self.dialect;
2117 let is_tsql = matches!(dialect, Some(Dialect::Tsql) | Some(Dialect::Fabric));
2118 let is_mysql = matches!(
2119 dialect,
2120 Some(Dialect::Mysql)
2121 | Some(Dialect::SingleStore)
2122 | Some(Dialect::Doris)
2123 | Some(Dialect::StarRocks)
2124 );
2125 let is_bigquery = matches!(dialect, Some(Dialect::BigQuery));
2126 let is_snowflake = matches!(dialect, Some(Dialect::Snowflake));
2127 let is_oracle = matches!(dialect, Some(Dialect::Oracle));
2128 let is_hive_family = matches!(
2129 dialect,
2130 Some(Dialect::Hive) | Some(Dialect::Spark) | Some(Dialect::Databricks)
2131 );
2132
2133 match func {
2134 TypedFunction::DateAdd {
2136 expr,
2137 interval,
2138 unit,
2139 } => {
2140 if is_tsql || is_snowflake {
2141 self.write_keyword("DATEADD(");
2142 if let Some(u) = unit {
2143 self.gen_datetime_field(u);
2144 } else {
2145 self.write_keyword("DAY");
2146 }
2147 self.write(", ");
2148 self.gen_expr(interval);
2149 self.write(", ");
2150 self.gen_expr(expr);
2151 self.write(")");
2152 } else if is_bigquery {
2153 self.write_keyword("DATE_ADD(");
2154 self.gen_expr(expr);
2155 self.write(", ");
2156 self.write_keyword("INTERVAL ");
2157 self.gen_expr(interval);
2158 self.write(" ");
2159 if let Some(u) = unit {
2160 self.gen_datetime_field(u);
2161 } else {
2162 self.write_keyword("DAY");
2163 }
2164 self.write(")");
2165 } else {
2166 self.write_keyword("DATE_ADD(");
2167 self.gen_expr(expr);
2168 self.write(", ");
2169 self.gen_expr(interval);
2170 if let Some(u) = unit {
2171 self.write(", ");
2172 self.gen_datetime_field(u);
2173 }
2174 self.write(")");
2175 }
2176 }
2177 TypedFunction::DateDiff { start, end, unit } => {
2178 if is_tsql || is_snowflake {
2179 self.write_keyword("DATEDIFF(");
2180 if let Some(u) = unit {
2181 self.gen_datetime_field(u);
2182 } else {
2183 self.write_keyword("DAY");
2184 }
2185 self.write(", ");
2186 self.gen_expr(start);
2187 self.write(", ");
2188 self.gen_expr(end);
2189 self.write(")");
2190 } else if is_bigquery {
2191 self.write_keyword("DATE_DIFF(");
2192 self.gen_expr(end);
2193 self.write(", ");
2194 self.gen_expr(start);
2195 self.write(", ");
2196 if let Some(u) = unit {
2197 self.gen_datetime_field(u);
2198 } else {
2199 self.write_keyword("DAY");
2200 }
2201 self.write(")");
2202 } else {
2203 self.write_keyword("DATEDIFF(");
2204 self.gen_expr(start);
2205 self.write(", ");
2206 self.gen_expr(end);
2207 if let Some(u) = unit {
2208 self.write(", ");
2209 self.gen_datetime_field(u);
2210 }
2211 self.write(")");
2212 }
2213 }
2214 TypedFunction::DateTrunc { unit, expr } => {
2215 if is_tsql {
2216 self.write_keyword("DATETRUNC(");
2217 self.gen_datetime_field(unit);
2218 self.write(", ");
2219 self.gen_expr(expr);
2220 self.write(")");
2221 } else if is_oracle {
2222 self.write_keyword("TRUNC(");
2223 self.gen_expr(expr);
2224 self.write(", '");
2225 self.gen_datetime_field(unit);
2226 self.write("')");
2227 } else {
2228 self.write_keyword("DATE_TRUNC(");
2229 self.write("'");
2230 self.gen_datetime_field(unit);
2231 self.write("'");
2232 self.write(", ");
2233 self.gen_expr(expr);
2234 self.write(")");
2235 }
2236 }
2237 TypedFunction::DateSub {
2238 expr,
2239 interval,
2240 unit,
2241 } => {
2242 if is_tsql || is_snowflake {
2243 self.write_keyword("DATEADD(");
2244 if let Some(u) = unit {
2245 self.gen_datetime_field(u);
2246 } else {
2247 self.write_keyword("DAY");
2248 }
2249 self.write(", -(");
2250 self.gen_expr(interval);
2251 self.write("), ");
2252 self.gen_expr(expr);
2253 self.write(")");
2254 } else if is_bigquery {
2255 self.write_keyword("DATE_SUB(");
2256 self.gen_expr(expr);
2257 self.write(", ");
2258 self.write_keyword("INTERVAL ");
2259 self.gen_expr(interval);
2260 self.write(" ");
2261 if let Some(u) = unit {
2262 self.gen_datetime_field(u);
2263 } else {
2264 self.write_keyword("DAY");
2265 }
2266 self.write(")");
2267 } else {
2268 self.write_keyword("DATE_SUB(");
2269 self.gen_expr(expr);
2270 self.write(", ");
2271 self.gen_expr(interval);
2272 if let Some(u) = unit {
2273 self.write(", ");
2274 self.gen_datetime_field(u);
2275 }
2276 self.write(")");
2277 }
2278 }
2279 TypedFunction::CurrentDate => {
2280 if is_tsql {
2281 self.write_keyword("CAST(GETDATE() AS DATE)");
2282 } else if is_mysql || is_hive_family {
2283 self.write_keyword("CURRENT_DATE()");
2284 } else {
2285 self.write_keyword("CURRENT_DATE");
2286 }
2287 }
2288 TypedFunction::CurrentTimestamp => {
2289 if is_tsql {
2290 self.write_keyword("GETDATE()");
2291 } else if is_mysql
2292 || matches!(
2293 dialect,
2294 Some(Dialect::Postgres)
2295 | Some(Dialect::DuckDb)
2296 | Some(Dialect::Sqlite)
2297 | Some(Dialect::Redshift)
2298 )
2299 {
2300 self.write_keyword("NOW()");
2301 } else {
2302 self.write_keyword("CURRENT_TIMESTAMP()");
2303 }
2304 }
2305 TypedFunction::StrToTime { expr, format } => {
2306 if is_mysql {
2307 self.write_keyword("STR_TO_DATE(");
2308 } else if is_bigquery {
2309 self.write_keyword("PARSE_TIMESTAMP(");
2310 } else {
2311 self.write_keyword("TO_TIMESTAMP(");
2312 }
2313 self.gen_expr(expr);
2314 self.write(", ");
2315 self.gen_expr(format);
2316 self.write(")");
2317 }
2318 TypedFunction::TimeToStr { expr, format } => {
2319 if is_mysql || is_hive_family {
2320 self.write_keyword("DATE_FORMAT(");
2321 } else if is_bigquery {
2322 self.write_keyword("FORMAT_TIMESTAMP(");
2323 } else if is_tsql {
2324 self.write_keyword("FORMAT(");
2325 } else {
2326 self.write_keyword("TO_CHAR(");
2327 }
2328 self.gen_expr(expr);
2329 self.write(", ");
2330 self.gen_expr(format);
2331 self.write(")");
2332 }
2333 TypedFunction::TsOrDsToDate { expr } => {
2334 if is_mysql {
2335 self.write_keyword("DATE(");
2336 self.gen_expr(expr);
2337 self.write(")");
2338 } else {
2339 self.write_keyword("CAST(");
2340 self.gen_expr(expr);
2341 self.write(" ");
2342 self.write_keyword("AS DATE)");
2343 }
2344 }
2345 TypedFunction::Year { expr } => {
2346 if is_tsql {
2347 self.write_keyword("YEAR(");
2348 self.gen_expr(expr);
2349 self.write(")");
2350 } else {
2351 self.write_keyword("EXTRACT(YEAR FROM ");
2352 self.gen_expr(expr);
2353 self.write(")");
2354 }
2355 }
2356 TypedFunction::Month { expr } => {
2357 if is_tsql {
2358 self.write_keyword("MONTH(");
2359 self.gen_expr(expr);
2360 self.write(")");
2361 } else {
2362 self.write_keyword("EXTRACT(MONTH FROM ");
2363 self.gen_expr(expr);
2364 self.write(")");
2365 }
2366 }
2367 TypedFunction::Day { expr } => {
2368 if is_tsql {
2369 self.write_keyword("DAY(");
2370 self.gen_expr(expr);
2371 self.write(")");
2372 } else {
2373 self.write_keyword("EXTRACT(DAY FROM ");
2374 self.gen_expr(expr);
2375 self.write(")");
2376 }
2377 }
2378
2379 TypedFunction::Trim {
2381 expr,
2382 trim_type,
2383 trim_chars,
2384 } => {
2385 self.write_keyword("TRIM(");
2386 match trim_type {
2387 TrimType::Leading => self.write_keyword("LEADING "),
2388 TrimType::Trailing => self.write_keyword("TRAILING "),
2389 TrimType::Both => {} }
2391 if let Some(chars) = trim_chars {
2392 self.gen_expr(chars);
2393 self.write(" ");
2394 self.write_keyword("FROM ");
2395 }
2396 self.gen_expr(expr);
2397 self.write(")");
2398 }
2399 TypedFunction::Substring {
2400 expr,
2401 start,
2402 length,
2403 } => {
2404 let name = if is_oracle
2405 || is_hive_family
2406 || is_mysql
2407 || matches!(
2408 dialect,
2409 Some(Dialect::Sqlite)
2410 | Some(Dialect::Doris)
2411 | Some(Dialect::SingleStore)
2412 | Some(Dialect::StarRocks)
2413 ) {
2414 "SUBSTR"
2415 } else {
2416 "SUBSTRING"
2417 };
2418 self.write_keyword(name);
2419 self.write("(");
2420 self.gen_expr(expr);
2421 self.write(", ");
2422 self.gen_expr(start);
2423 if let Some(l) = length {
2424 self.write(", ");
2425 self.gen_expr(l);
2426 }
2427 self.write(")");
2428 }
2429 TypedFunction::Upper { expr } => {
2430 self.write_keyword("UPPER(");
2431 self.gen_expr(expr);
2432 self.write(")");
2433 }
2434 TypedFunction::Lower { expr } => {
2435 self.write_keyword("LOWER(");
2436 self.gen_expr(expr);
2437 self.write(")");
2438 }
2439 TypedFunction::RegexpLike {
2440 expr,
2441 pattern,
2442 flags,
2443 } => {
2444 self.write_keyword("REGEXP_LIKE(");
2445 self.gen_expr(expr);
2446 self.write(", ");
2447 self.gen_expr(pattern);
2448 if let Some(f) = flags {
2449 self.write(", ");
2450 self.gen_expr(f);
2451 }
2452 self.write(")");
2453 }
2454 TypedFunction::RegexpExtract {
2455 expr,
2456 pattern,
2457 group_index,
2458 } => {
2459 if is_bigquery || is_hive_family {
2460 self.write_keyword("REGEXP_EXTRACT(");
2461 } else {
2462 self.write_keyword("REGEXP_SUBSTR(");
2463 }
2464 self.gen_expr(expr);
2465 self.write(", ");
2466 self.gen_expr(pattern);
2467 if let Some(g) = group_index {
2468 self.write(", ");
2469 self.gen_expr(g);
2470 }
2471 self.write(")");
2472 }
2473 TypedFunction::RegexpReplace {
2474 expr,
2475 pattern,
2476 replacement,
2477 flags,
2478 } => {
2479 self.write_keyword("REGEXP_REPLACE(");
2480 self.gen_expr(expr);
2481 self.write(", ");
2482 self.gen_expr(pattern);
2483 self.write(", ");
2484 self.gen_expr(replacement);
2485 if let Some(f) = flags {
2486 self.write(", ");
2487 self.gen_expr(f);
2488 }
2489 self.write(")");
2490 }
2491 TypedFunction::ConcatWs { separator, exprs } => {
2492 self.write_keyword("CONCAT_WS(");
2493 self.gen_expr(separator);
2494 for e in exprs {
2495 self.write(", ");
2496 self.gen_expr(e);
2497 }
2498 self.write(")");
2499 }
2500 TypedFunction::Split { expr, delimiter } => {
2501 if is_tsql {
2502 self.write_keyword("STRING_SPLIT(");
2503 } else {
2504 self.write_keyword("SPLIT(");
2505 }
2506 self.gen_expr(expr);
2507 self.write(", ");
2508 self.gen_expr(delimiter);
2509 self.write(")");
2510 }
2511 TypedFunction::Initcap { expr } => {
2512 self.write_keyword("INITCAP(");
2513 self.gen_expr(expr);
2514 self.write(")");
2515 }
2516 TypedFunction::Length { expr } => {
2517 let name = if is_tsql || is_bigquery || is_snowflake {
2518 "LEN"
2519 } else {
2520 "LENGTH"
2521 };
2522 self.write_keyword(name);
2523 self.write("(");
2524 self.gen_expr(expr);
2525 self.write(")");
2526 }
2527 TypedFunction::Replace { expr, from, to } => {
2528 self.write_keyword("REPLACE(");
2529 self.gen_expr(expr);
2530 self.write(", ");
2531 self.gen_expr(from);
2532 self.write(", ");
2533 self.gen_expr(to);
2534 self.write(")");
2535 }
2536 TypedFunction::Reverse { expr } => {
2537 self.write_keyword("REVERSE(");
2538 self.gen_expr(expr);
2539 self.write(")");
2540 }
2541 TypedFunction::Left { expr, n } => {
2542 self.write_keyword("LEFT(");
2543 self.gen_expr(expr);
2544 self.write(", ");
2545 self.gen_expr(n);
2546 self.write(")");
2547 }
2548 TypedFunction::Right { expr, n } => {
2549 self.write_keyword("RIGHT(");
2550 self.gen_expr(expr);
2551 self.write(", ");
2552 self.gen_expr(n);
2553 self.write(")");
2554 }
2555 TypedFunction::Lpad { expr, length, pad } => {
2556 self.write_keyword("LPAD(");
2557 self.gen_expr(expr);
2558 self.write(", ");
2559 self.gen_expr(length);
2560 if let Some(p) = pad {
2561 self.write(", ");
2562 self.gen_expr(p);
2563 }
2564 self.write(")");
2565 }
2566 TypedFunction::Rpad { expr, length, pad } => {
2567 self.write_keyword("RPAD(");
2568 self.gen_expr(expr);
2569 self.write(", ");
2570 self.gen_expr(length);
2571 if let Some(p) = pad {
2572 self.write(", ");
2573 self.gen_expr(p);
2574 }
2575 self.write(")");
2576 }
2577
2578 TypedFunction::Count { expr, distinct } => {
2580 self.write_keyword("COUNT(");
2581 if *distinct {
2582 self.write_keyword("DISTINCT ");
2583 }
2584 self.gen_expr(expr);
2585 self.write(")");
2586 }
2587 TypedFunction::Sum { expr, distinct } => {
2588 self.write_keyword("SUM(");
2589 if *distinct {
2590 self.write_keyword("DISTINCT ");
2591 }
2592 self.gen_expr(expr);
2593 self.write(")");
2594 }
2595 TypedFunction::Avg { expr, distinct } => {
2596 self.write_keyword("AVG(");
2597 if *distinct {
2598 self.write_keyword("DISTINCT ");
2599 }
2600 self.gen_expr(expr);
2601 self.write(")");
2602 }
2603 TypedFunction::Min { expr } => {
2604 self.write_keyword("MIN(");
2605 self.gen_expr(expr);
2606 self.write(")");
2607 }
2608 TypedFunction::Max { expr } => {
2609 self.write_keyword("MAX(");
2610 self.gen_expr(expr);
2611 self.write(")");
2612 }
2613 TypedFunction::ArrayAgg { expr, distinct } => {
2614 let name = if matches!(dialect, Some(Dialect::DuckDb)) {
2615 "LIST"
2616 } else if is_hive_family {
2617 "COLLECT_LIST"
2618 } else {
2619 "ARRAY_AGG"
2620 };
2621 self.write_keyword(name);
2622 self.write("(");
2623 if *distinct {
2624 self.write_keyword("DISTINCT ");
2625 }
2626 self.gen_expr(expr);
2627 self.write(")");
2628 }
2629 TypedFunction::ApproxDistinct { expr } => {
2630 let name = if is_hive_family
2631 || matches!(
2632 dialect,
2633 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2634 ) {
2635 "APPROX_DISTINCT"
2636 } else {
2637 "APPROX_COUNT_DISTINCT"
2638 };
2639 self.write_keyword(name);
2640 self.write("(");
2641 self.gen_expr(expr);
2642 self.write(")");
2643 }
2644 TypedFunction::Variance { expr } => {
2645 let name = if is_tsql || is_oracle {
2646 "VAR"
2647 } else {
2648 "VARIANCE"
2649 };
2650 self.write_keyword(name);
2651 self.write("(");
2652 self.gen_expr(expr);
2653 self.write(")");
2654 }
2655 TypedFunction::Stddev { expr } => {
2656 self.write_keyword("STDDEV(");
2657 self.gen_expr(expr);
2658 self.write(")");
2659 }
2660
2661 TypedFunction::ArrayConcat { arrays } => {
2663 let name = if matches!(
2664 dialect,
2665 Some(Dialect::Postgres) | Some(Dialect::Redshift) | Some(Dialect::DuckDb)
2666 ) {
2667 "ARRAY_CAT"
2668 } else {
2669 "ARRAY_CONCAT"
2670 };
2671 self.write_keyword(name);
2672 self.write("(");
2673 self.gen_expr_list(arrays);
2674 self.write(")");
2675 }
2676 TypedFunction::ArrayContains { array, element } => {
2677 self.write_keyword("ARRAY_CONTAINS(");
2678 self.gen_expr(array);
2679 self.write(", ");
2680 self.gen_expr(element);
2681 self.write(")");
2682 }
2683 TypedFunction::ArraySize { expr } => {
2684 let name = if matches!(dialect, Some(Dialect::Postgres) | Some(Dialect::Redshift)) {
2685 "ARRAY_LENGTH"
2686 } else if is_hive_family {
2687 "SIZE"
2688 } else {
2689 "ARRAY_SIZE"
2690 };
2691 self.write_keyword(name);
2692 self.write("(");
2693 self.gen_expr(expr);
2694 self.write(")");
2695 }
2696 TypedFunction::Explode { expr } => {
2697 self.write_keyword("EXPLODE(");
2698 self.gen_expr(expr);
2699 self.write(")");
2700 }
2701 TypedFunction::GenerateSeries { start, stop, step } => {
2702 self.write_keyword("GENERATE_SERIES(");
2703 self.gen_expr(start);
2704 self.write(", ");
2705 self.gen_expr(stop);
2706 if let Some(s) = step {
2707 self.write(", ");
2708 self.gen_expr(s);
2709 }
2710 self.write(")");
2711 }
2712 TypedFunction::Flatten { expr } => {
2713 self.write_keyword("FLATTEN(");
2714 self.gen_expr(expr);
2715 self.write(")");
2716 }
2717
2718 TypedFunction::JSONExtract { expr, path } => {
2720 if is_tsql {
2721 self.write_keyword("JSON_VALUE(");
2722 } else {
2723 self.write_keyword("JSON_EXTRACT(");
2724 }
2725 self.gen_expr(expr);
2726 self.write(", ");
2727 self.gen_expr(path);
2728 self.write(")");
2729 }
2730 TypedFunction::JSONExtractScalar { expr, path } => {
2731 if is_bigquery {
2732 self.write_keyword("JSON_EXTRACT_SCALAR(");
2733 } else if is_tsql {
2734 self.write_keyword("JSON_VALUE(");
2735 } else {
2736 self.write_keyword("JSON_EXTRACT_SCALAR(");
2737 }
2738 self.gen_expr(expr);
2739 self.write(", ");
2740 self.gen_expr(path);
2741 self.write(")");
2742 }
2743 TypedFunction::ParseJSON { expr } => {
2744 if is_snowflake {
2745 self.write_keyword("PARSE_JSON(");
2746 } else if is_bigquery {
2747 self.write_keyword("JSON_PARSE(");
2748 } else {
2749 self.write_keyword("PARSE_JSON(");
2750 }
2751 self.gen_expr(expr);
2752 self.write(")");
2753 }
2754 TypedFunction::JSONFormat { expr } => {
2755 if is_bigquery {
2756 self.write_keyword("TO_JSON_STRING(");
2757 } else {
2758 self.write_keyword("JSON_FORMAT(");
2759 }
2760 self.gen_expr(expr);
2761 self.write(")");
2762 }
2763
2764 TypedFunction::RowNumber => self.write_keyword("ROW_NUMBER()"),
2766 TypedFunction::Rank => self.write_keyword("RANK()"),
2767 TypedFunction::DenseRank => self.write_keyword("DENSE_RANK()"),
2768 TypedFunction::NTile { n } => {
2769 self.write_keyword("NTILE(");
2770 self.gen_expr(n);
2771 self.write(")");
2772 }
2773 TypedFunction::Lead {
2774 expr,
2775 offset,
2776 default,
2777 } => {
2778 self.write_keyword("LEAD(");
2779 self.gen_expr(expr);
2780 if let Some(o) = offset {
2781 self.write(", ");
2782 self.gen_expr(o);
2783 }
2784 if let Some(d) = default {
2785 self.write(", ");
2786 self.gen_expr(d);
2787 }
2788 self.write(")");
2789 }
2790 TypedFunction::Lag {
2791 expr,
2792 offset,
2793 default,
2794 } => {
2795 self.write_keyword("LAG(");
2796 self.gen_expr(expr);
2797 if let Some(o) = offset {
2798 self.write(", ");
2799 self.gen_expr(o);
2800 }
2801 if let Some(d) = default {
2802 self.write(", ");
2803 self.gen_expr(d);
2804 }
2805 self.write(")");
2806 }
2807 TypedFunction::FirstValue { expr } => {
2808 self.write_keyword("FIRST_VALUE(");
2809 self.gen_expr(expr);
2810 self.write(")");
2811 }
2812 TypedFunction::LastValue { expr } => {
2813 self.write_keyword("LAST_VALUE(");
2814 self.gen_expr(expr);
2815 self.write(")");
2816 }
2817
2818 TypedFunction::Abs { expr } => {
2820 self.write_keyword("ABS(");
2821 self.gen_expr(expr);
2822 self.write(")");
2823 }
2824 TypedFunction::Ceil { expr } => {
2825 let name = if is_tsql { "CEILING" } else { "CEIL" };
2826 self.write_keyword(name);
2827 self.write("(");
2828 self.gen_expr(expr);
2829 self.write(")");
2830 }
2831 TypedFunction::Floor { expr } => {
2832 self.write_keyword("FLOOR(");
2833 self.gen_expr(expr);
2834 self.write(")");
2835 }
2836 TypedFunction::Round { expr, decimals } => {
2837 self.write_keyword("ROUND(");
2838 self.gen_expr(expr);
2839 if let Some(d) = decimals {
2840 self.write(", ");
2841 self.gen_expr(d);
2842 }
2843 self.write(")");
2844 }
2845 TypedFunction::Log { expr, base } => {
2846 if let Some(b) = base {
2847 self.write_keyword("LOG(");
2848 if matches!(dialect, Some(Dialect::Postgres) | Some(Dialect::DuckDb)) {
2849 self.gen_expr(b);
2851 self.write(", ");
2852 self.gen_expr(expr);
2853 } else {
2854 self.gen_expr(expr);
2856 self.write(", ");
2857 self.gen_expr(b);
2858 }
2859 self.write(")");
2860 } else {
2861 self.write_keyword("LOG(");
2863 self.gen_expr(expr);
2864 self.write(")");
2865 }
2866 }
2867 TypedFunction::Ln { expr } => {
2868 self.write_keyword("LN(");
2869 self.gen_expr(expr);
2870 self.write(")");
2871 }
2872 TypedFunction::Pow { base, exponent } => {
2873 let name = if is_tsql || is_oracle { "POWER" } else { "POW" };
2874 self.write_keyword(name);
2875 self.write("(");
2876 self.gen_expr(base);
2877 self.write(", ");
2878 self.gen_expr(exponent);
2879 self.write(")");
2880 }
2881 TypedFunction::Sqrt { expr } => {
2882 self.write_keyword("SQRT(");
2883 self.gen_expr(expr);
2884 self.write(")");
2885 }
2886 TypedFunction::Greatest { exprs } => {
2887 self.write_keyword("GREATEST(");
2888 self.gen_expr_list(exprs);
2889 self.write(")");
2890 }
2891 TypedFunction::Least { exprs } => {
2892 self.write_keyword("LEAST(");
2893 self.gen_expr_list(exprs);
2894 self.write(")");
2895 }
2896 TypedFunction::Mod { left, right } => {
2897 self.write_keyword("MOD(");
2898 self.gen_expr(left);
2899 self.write(", ");
2900 self.gen_expr(right);
2901 self.write(")");
2902 }
2903
2904 TypedFunction::Hex { expr } => {
2906 let name = if matches!(
2907 dialect,
2908 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2909 ) {
2910 "TO_HEX"
2911 } else {
2912 "HEX"
2913 };
2914 self.write_keyword(name);
2915 self.write("(");
2916 self.gen_expr(expr);
2917 self.write(")");
2918 }
2919 TypedFunction::Unhex { expr } => {
2920 let name = if matches!(
2921 dialect,
2922 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2923 ) {
2924 "FROM_HEX"
2925 } else {
2926 "UNHEX"
2927 };
2928 self.write_keyword(name);
2929 self.write("(");
2930 self.gen_expr(expr);
2931 self.write(")");
2932 }
2933 TypedFunction::Md5 { expr } => {
2934 self.write_keyword("MD5(");
2935 self.gen_expr(expr);
2936 self.write(")");
2937 }
2938 TypedFunction::Sha { expr } => {
2939 let name = if is_mysql { "SHA1" } else { "SHA" };
2940 self.write_keyword(name);
2941 self.write("(");
2942 self.gen_expr(expr);
2943 self.write(")");
2944 }
2945 TypedFunction::Sha2 { expr, bit_length } => {
2946 self.write_keyword("SHA2(");
2947 self.gen_expr(expr);
2948 self.write(", ");
2949 self.gen_expr(bit_length);
2950 self.write(")");
2951 }
2952 }
2953 }
2954}
2955
2956impl Default for Generator {
2957 fn default() -> Self {
2958 Self::new()
2959 }
2960}
2961
2962#[cfg(test)]
2963mod tests {
2964 use super::*;
2965 use crate::parser::Parser;
2966
2967 fn roundtrip(sql: &str) -> String {
2968 let stmt = Parser::new(sql).unwrap().parse_statement().unwrap();
2969 let mut g = Generator::new();
2970 g.generate(&stmt)
2971 }
2972
2973 #[test]
2974 fn test_select_roundtrip() {
2975 assert_eq!(roundtrip("SELECT a, b FROM t"), "SELECT a, b FROM t");
2976 }
2977
2978 #[test]
2979 fn test_select_where() {
2980 assert_eq!(
2981 roundtrip("SELECT x FROM t WHERE x > 10"),
2982 "SELECT x FROM t WHERE x > 10"
2983 );
2984 }
2985
2986 #[test]
2987 fn test_select_wildcard() {
2988 assert_eq!(roundtrip("SELECT * FROM users"), "SELECT * FROM users");
2989 }
2990
2991 #[test]
2992 fn test_insert_values() {
2993 assert_eq!(
2994 roundtrip("INSERT INTO t (a, b) VALUES (1, 'hello')"),
2995 "INSERT INTO t (a, b) VALUES (1, 'hello')"
2996 );
2997 }
2998
2999 #[test]
3000 fn test_delete() {
3001 assert_eq!(
3002 roundtrip("DELETE FROM users WHERE id = 1"),
3003 "DELETE FROM users WHERE id = 1"
3004 );
3005 }
3006
3007 #[test]
3008 fn test_join() {
3009 assert_eq!(
3010 roundtrip("SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"),
3011 "SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"
3012 );
3013 }
3014
3015 #[test]
3016 fn test_create_table() {
3017 assert_eq!(
3018 roundtrip("CREATE TABLE users (id INT NOT NULL, name VARCHAR(255), email TEXT)"),
3019 "CREATE TABLE users (id INT NOT NULL, name VARCHAR(255), email TEXT)"
3020 );
3021 }
3022
3023 #[test]
3024 fn test_cte_roundtrip() {
3025 let sql = "WITH cte AS (SELECT 1 AS x) SELECT x FROM cte";
3026 assert_eq!(
3027 roundtrip(sql),
3028 "WITH cte AS (SELECT 1 AS x) SELECT x FROM cte"
3029 );
3030 }
3031
3032 #[test]
3033 fn test_union_roundtrip() {
3034 let sql = "SELECT 1 UNION ALL SELECT 2";
3035 assert_eq!(roundtrip(sql), "SELECT 1 UNION ALL SELECT 2");
3036 }
3037
3038 #[test]
3039 fn test_cast_roundtrip() {
3040 assert_eq!(
3041 roundtrip("SELECT CAST(x AS INT) FROM t"),
3042 "SELECT CAST(x AS INT) FROM t"
3043 );
3044 }
3045
3046 #[test]
3047 fn test_exists_roundtrip() {
3048 assert_eq!(
3049 roundtrip("SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2)"),
3050 "SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2)"
3051 );
3052 }
3053
3054 #[test]
3055 fn test_extract_roundtrip() {
3056 assert_eq!(
3057 roundtrip("SELECT EXTRACT(YEAR FROM created_at) FROM t"),
3058 "SELECT EXTRACT(YEAR FROM created_at) FROM t"
3059 );
3060 }
3061
3062 #[test]
3063 fn test_window_function_roundtrip() {
3064 assert_eq!(
3065 roundtrip("SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) FROM emp"),
3066 "SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) FROM emp"
3067 );
3068 }
3069
3070 #[test]
3071 fn test_subquery_from_roundtrip() {
3072 assert_eq!(
3073 roundtrip("SELECT * FROM (SELECT 1 AS x) AS sub"),
3074 "SELECT * FROM (SELECT 1 AS x) AS sub"
3075 );
3076 }
3077
3078 #[test]
3079 fn test_in_subquery_roundtrip() {
3080 assert_eq!(
3081 roundtrip("SELECT * FROM t WHERE id IN (SELECT id FROM t2)"),
3082 "SELECT * FROM t WHERE id IN (SELECT id FROM t2)"
3083 );
3084 }
3085
3086 fn pretty_print(sql: &str) -> String {
3091 let stmt = Parser::new(sql).unwrap().parse_statement().unwrap();
3092 let mut g = Generator::pretty();
3093 g.generate(&stmt)
3094 }
3095
3096 #[test]
3097 fn test_pretty_simple_select() {
3098 assert_eq!(
3099 pretty_print("SELECT a, b, c FROM t"),
3100 "SELECT\n a,\n b,\n c\nFROM\n t"
3101 );
3102 }
3103
3104 #[test]
3105 fn test_pretty_select_where() {
3106 assert_eq!(
3107 pretty_print("SELECT a FROM t WHERE a > 1"),
3108 "SELECT\n a\nFROM\n t\nWHERE\n a > 1"
3109 );
3110 }
3111
3112 #[test]
3113 fn test_pretty_select_group_by_having() {
3114 assert_eq!(
3115 pretty_print("SELECT a, COUNT(*) FROM t GROUP BY a HAVING COUNT(*) > 1"),
3116 "SELECT\n a,\n COUNT(*)\nFROM\n t\nGROUP BY\n a\nHAVING\n COUNT(*) > 1"
3117 );
3118 }
3119
3120 #[test]
3121 fn test_pretty_select_order_by_limit() {
3122 assert_eq!(
3123 pretty_print("SELECT a FROM t ORDER BY a DESC LIMIT 10"),
3124 "SELECT\n a\nFROM\n t\nORDER BY\n a DESC\nLIMIT 10"
3125 );
3126 }
3127
3128 #[test]
3129 fn test_pretty_join() {
3130 assert_eq!(
3131 pretty_print("SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"),
3132 "SELECT\n a.id,\n b.name\nFROM\n a\nINNER JOIN\n b\n ON a.id = b.a_id"
3133 );
3134 }
3135
3136 #[test]
3137 fn test_pretty_cte() {
3138 assert_eq!(
3139 pretty_print("WITH cte AS (SELECT 1 AS x) SELECT x FROM cte"),
3140 "WITH cte AS (\n SELECT\n 1 AS x\n)\nSELECT\n x\nFROM\n cte"
3141 );
3142 }
3143
3144 #[test]
3145 fn test_pretty_union() {
3146 assert_eq!(
3147 pretty_print("SELECT 1 UNION ALL SELECT 2"),
3148 "SELECT\n 1\nUNION ALL\nSELECT\n 2"
3149 );
3150 }
3151
3152 #[test]
3153 fn test_pretty_insert() {
3154 assert_eq!(
3155 pretty_print("INSERT INTO t (a, b) VALUES (1, 'hello'), (2, 'world')"),
3156 "INSERT INTO t (a, b)\nVALUES\n (1, 'hello'),\n (2, 'world')"
3157 );
3158 }
3159
3160 #[test]
3161 fn test_pretty_update() {
3162 assert_eq!(
3163 pretty_print("UPDATE t SET a = 1, b = 2 WHERE c = 3"),
3164 "UPDATE t\nSET\n a = 1,\n b = 2\nWHERE\n c = 3"
3165 );
3166 }
3167
3168 #[test]
3169 fn test_pretty_delete() {
3170 assert_eq!(
3171 pretty_print("DELETE FROM t WHERE id = 1"),
3172 "DELETE FROM t\nWHERE\n id = 1"
3173 );
3174 }
3175
3176 #[test]
3177 fn test_pretty_create_table() {
3178 assert_eq!(
3179 pretty_print("CREATE TABLE t (id INT NOT NULL, name VARCHAR(255), email TEXT)"),
3180 "CREATE TABLE t (\n id INT NOT NULL,\n name VARCHAR(255),\n email TEXT\n)"
3181 );
3182 }
3183
3184 #[test]
3185 fn test_pretty_complex_query() {
3186 let sql = "SELECT a, SUM(b) FROM t1 INNER JOIN t2 ON t1.id = t2.id WHERE t1.x > 1 GROUP BY a HAVING SUM(b) > 10 ORDER BY a LIMIT 100";
3187 let expected = "SELECT\n a,\n SUM(b)\nFROM\n t1\nINNER JOIN\n t2\n ON t1.id = t2.id\nWHERE\n t1.x > 1\nGROUP BY\n a\nHAVING\n SUM(b) > 10\nORDER BY\n a\nLIMIT 100";
3188 assert_eq!(pretty_print(sql), expected);
3189 }
3190
3191 #[test]
3192 fn test_pretty_select_distinct() {
3193 assert_eq!(
3194 pretty_print("SELECT DISTINCT a, b FROM t"),
3195 "SELECT DISTINCT\n a,\n b\nFROM\n t"
3196 );
3197 }
3198}