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