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