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 self.write("ARRAY");
1425 if let Some(inner) = inner {
1426 self.write("<");
1427 self.gen_data_type(inner);
1428 self.write(">");
1429 }
1430 }
1431 DataType::Map { key, value } => {
1432 self.write("MAP<");
1433 self.gen_data_type(key);
1434 self.write(", ");
1435 self.gen_data_type(value);
1436 self.write(">");
1437 }
1438 DataType::Struct(fields) => {
1439 self.write("STRUCT<");
1440 for (i, (name, dt)) in fields.iter().enumerate() {
1441 if i > 0 {
1442 self.write(", ");
1443 }
1444 self.write(name);
1445 self.write(" ");
1446 self.gen_data_type(dt);
1447 }
1448 self.write(">");
1449 }
1450 DataType::Tuple(types) => {
1451 self.write("TUPLE(");
1452 for (i, dt) in types.iter().enumerate() {
1453 if i > 0 {
1454 self.write(", ");
1455 }
1456 self.gen_data_type(dt);
1457 }
1458 self.write(")");
1459 }
1460 DataType::Null => self.write("NULL"),
1461 DataType::Variant => self.write("VARIANT"),
1462 DataType::Object => self.write("OBJECT"),
1463 DataType::Xml => self.write("XML"),
1464 DataType::Inet => self.write("INET"),
1465 DataType::Cidr => self.write("CIDR"),
1466 DataType::Macaddr => self.write("MACADDR"),
1467 DataType::Bit(len) => {
1468 self.write("BIT");
1469 if let Some(n) = len {
1470 self.write(&format!("({n})"));
1471 }
1472 }
1473 DataType::Money => self.write("MONEY"),
1474 DataType::Serial => self.write("SERIAL"),
1475 DataType::BigSerial => self.write("BIGSERIAL"),
1476 DataType::SmallSerial => self.write("SMALLSERIAL"),
1477 DataType::Regclass => self.write("REGCLASS"),
1478 DataType::Regtype => self.write("REGTYPE"),
1479 DataType::Hstore => self.write("HSTORE"),
1480 DataType::Geography => self.write("GEOGRAPHY"),
1481 DataType::Geometry => self.write("GEOMETRY"),
1482 DataType::Super => self.write("SUPER"),
1483 DataType::Unknown(name) => self.write(name),
1484 }
1485 }
1486
1487 fn binary_op_str(op: &BinaryOperator) -> &'static str {
1492 match op {
1493 BinaryOperator::Plus => " + ",
1494 BinaryOperator::Minus => " - ",
1495 BinaryOperator::Multiply => " * ",
1496 BinaryOperator::Divide => " / ",
1497 BinaryOperator::Modulo => " % ",
1498 BinaryOperator::Eq => " = ",
1499 BinaryOperator::Neq => " <> ",
1500 BinaryOperator::Lt => " < ",
1501 BinaryOperator::Gt => " > ",
1502 BinaryOperator::LtEq => " <= ",
1503 BinaryOperator::GtEq => " >= ",
1504 BinaryOperator::And => " AND ",
1505 BinaryOperator::Or => " OR ",
1506 BinaryOperator::Xor => " XOR ",
1507 BinaryOperator::Concat => " || ",
1508 BinaryOperator::BitwiseAnd => " & ",
1509 BinaryOperator::BitwiseOr => " | ",
1510 BinaryOperator::BitwiseXor => " ^ ",
1511 BinaryOperator::ShiftLeft => " << ",
1512 BinaryOperator::ShiftRight => " >> ",
1513 BinaryOperator::Arrow => " -> ",
1514 BinaryOperator::DoubleArrow => " ->> ",
1515 }
1516 }
1517
1518 fn gen_expr_list(&mut self, exprs: &[Expr]) {
1519 for (i, expr) in exprs.iter().enumerate() {
1520 if i > 0 {
1521 self.write(", ");
1522 }
1523 self.gen_expr(expr);
1524 }
1525 }
1526
1527 fn gen_expr(&mut self, expr: &Expr) {
1528 match expr {
1529 Expr::Column {
1530 table,
1531 name,
1532 quote_style,
1533 table_quote_style,
1534 } => {
1535 if let Some(t) = table {
1536 self.write_quoted(t, *table_quote_style);
1537 self.write(".");
1538 }
1539 self.write_quoted(name, *quote_style);
1540 }
1541 Expr::Number(n) => self.write(n),
1542 Expr::StringLiteral(s) => {
1543 self.write("'");
1544 self.write(&s.replace('\'', "''"));
1545 self.write("'");
1546 }
1547 Expr::Boolean(b) => self.write(if *b { "TRUE" } else { "FALSE" }),
1548 Expr::Null => self.write("NULL"),
1549 Expr::Default => self.write_keyword("DEFAULT"),
1550 Expr::Wildcard | Expr::Star => self.write("*"),
1551
1552 Expr::Cube { exprs } => {
1553 self.write_keyword("CUBE");
1554 self.write("(");
1555 self.gen_expr_list(exprs);
1556 self.write(")");
1557 }
1558 Expr::Rollup { exprs } => {
1559 self.write_keyword("ROLLUP");
1560 self.write("(");
1561 self.gen_expr_list(exprs);
1562 self.write(")");
1563 }
1564 Expr::GroupingSets { sets } => {
1565 self.write_keyword("GROUPING SETS");
1566 self.write("(");
1567 self.gen_expr_list(sets);
1568 self.write(")");
1569 }
1570
1571 Expr::BinaryOp { left, op, right } => {
1572 self.gen_expr(left);
1573 self.write(Self::binary_op_str(op));
1574 self.gen_expr(right);
1575 }
1576 Expr::AnyOp { expr, op, right } => {
1577 self.gen_expr(expr);
1578 self.write(Self::binary_op_str(op));
1579 self.write_keyword("ANY");
1580 self.write("(");
1581 if let Expr::Subquery(query) = right.as_ref() {
1582 self.gen_statement(query);
1583 } else {
1584 self.gen_expr(right);
1585 }
1586 self.write(")");
1587 }
1588 Expr::AllOp { expr, op, right } => {
1589 self.gen_expr(expr);
1590 self.write(Self::binary_op_str(op));
1591 self.write_keyword("ALL");
1592 self.write("(");
1593 if let Expr::Subquery(query) = right.as_ref() {
1594 self.gen_statement(query);
1595 } else {
1596 self.gen_expr(right);
1597 }
1598 self.write(")");
1599 }
1600 Expr::UnaryOp { op, expr } => {
1601 let op_str = match op {
1602 UnaryOperator::Not => "NOT ",
1603 UnaryOperator::Minus => "-",
1604 UnaryOperator::Plus => "+",
1605 UnaryOperator::BitwiseNot => "~",
1606 };
1607 self.write(op_str);
1608 self.gen_expr(expr);
1609 }
1610 Expr::Function {
1611 name,
1612 args,
1613 distinct,
1614 filter,
1615 over,
1616 } => {
1617 self.write(name);
1618 self.write("(");
1619 if *distinct {
1620 self.write_keyword("DISTINCT ");
1621 }
1622 self.gen_expr_list(args);
1623 self.write(")");
1624
1625 if let Some(filter_expr) = filter {
1626 self.write(" ");
1627 self.write_keyword("FILTER (WHERE ");
1628 self.gen_expr(filter_expr);
1629 self.write(")");
1630 }
1631 if let Some(spec) = over {
1632 self.write(" ");
1633 self.write_keyword("OVER ");
1634 if let Some(wref) = &spec.window_ref {
1635 if spec.partition_by.is_empty()
1636 && spec.order_by.is_empty()
1637 && spec.frame.is_none()
1638 {
1639 self.write(wref);
1640 } else {
1641 self.write("(");
1642 self.gen_window_spec(spec);
1643 self.write(")");
1644 }
1645 } else {
1646 self.write("(");
1647 self.gen_window_spec(spec);
1648 self.write(")");
1649 }
1650 }
1651 }
1652 Expr::Between {
1653 expr,
1654 low,
1655 high,
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("BETWEEN ");
1665 self.gen_expr(low);
1666 self.write(" ");
1667 self.write_keyword("AND ");
1668 self.gen_expr(high);
1669 }
1670 Expr::InList {
1671 expr,
1672 list,
1673 negated,
1674 } => {
1675 self.gen_expr(expr);
1676 if *negated {
1677 self.write(" ");
1678 self.write_keyword("NOT");
1679 }
1680 self.write(" ");
1681 self.write_keyword("IN (");
1682 self.gen_expr_list(list);
1683 self.write(")");
1684 }
1685 Expr::InSubquery {
1686 expr,
1687 subquery,
1688 negated,
1689 } => {
1690 self.gen_expr(expr);
1691 if *negated {
1692 self.write(" ");
1693 self.write_keyword("NOT");
1694 }
1695 self.write(" ");
1696 self.write_keyword("IN (");
1697 self.gen_statement(subquery);
1698 self.write(")");
1699 }
1700 Expr::IsNull { expr, negated } => {
1701 self.gen_expr(expr);
1702 if *negated {
1703 self.write(" ");
1704 self.write_keyword("IS NOT NULL");
1705 } else {
1706 self.write(" ");
1707 self.write_keyword("IS NULL");
1708 }
1709 }
1710 Expr::IsBool {
1711 expr,
1712 value,
1713 negated,
1714 } => {
1715 self.gen_expr(expr);
1716 self.write(" ");
1717 match (negated, value) {
1718 (false, true) => self.write_keyword("IS TRUE"),
1719 (false, false) => self.write_keyword("IS FALSE"),
1720 (true, true) => self.write_keyword("IS NOT TRUE"),
1721 (true, false) => self.write_keyword("IS NOT FALSE"),
1722 }
1723 }
1724 Expr::Like {
1725 expr,
1726 pattern,
1727 negated,
1728 escape,
1729 } => {
1730 self.gen_expr(expr);
1731 if *negated {
1732 self.write(" ");
1733 self.write_keyword("NOT");
1734 }
1735 self.write(" ");
1736 self.write_keyword("LIKE ");
1737 self.gen_expr(pattern);
1738 if let Some(esc) = escape {
1739 self.write(" ");
1740 self.write_keyword("ESCAPE ");
1741 self.gen_expr(esc);
1742 }
1743 }
1744 Expr::ILike {
1745 expr,
1746 pattern,
1747 negated,
1748 escape,
1749 } => {
1750 self.gen_expr(expr);
1751 if *negated {
1752 self.write(" ");
1753 self.write_keyword("NOT");
1754 }
1755 self.write(" ");
1756 self.write_keyword("ILIKE ");
1757 self.gen_expr(pattern);
1758 if let Some(esc) = escape {
1759 self.write(" ");
1760 self.write_keyword("ESCAPE ");
1761 self.gen_expr(esc);
1762 }
1763 }
1764 Expr::Case {
1765 operand,
1766 when_clauses,
1767 else_clause,
1768 } => {
1769 self.write_keyword("CASE");
1770 if let Some(op) = operand {
1771 self.write(" ");
1772 self.gen_expr(op);
1773 }
1774 for (cond, result) in when_clauses {
1775 self.write(" ");
1776 self.write_keyword("WHEN ");
1777 self.gen_expr(cond);
1778 self.write(" ");
1779 self.write_keyword("THEN ");
1780 self.gen_expr(result);
1781 }
1782 if let Some(el) = else_clause {
1783 self.write(" ");
1784 self.write_keyword("ELSE ");
1785 self.gen_expr(el);
1786 }
1787 self.write(" ");
1788 self.write_keyword("END");
1789 }
1790 Expr::Nested(inner) => {
1791 self.write("(");
1792 self.gen_expr(inner);
1793 self.write(")");
1794 }
1795 Expr::Subquery(query) => {
1796 self.write("(");
1797 self.gen_statement(query);
1798 self.write(")");
1799 }
1800 Expr::Exists { subquery, negated } => {
1801 if *negated {
1802 self.write_keyword("NOT ");
1803 }
1804 self.write_keyword("EXISTS (");
1805 self.gen_statement(subquery);
1806 self.write(")");
1807 }
1808 Expr::Cast { expr, data_type } => {
1809 self.write_keyword("CAST(");
1810 self.gen_expr(expr);
1811 self.write(" ");
1812 self.write_keyword("AS ");
1813 self.gen_data_type(data_type);
1814 self.write(")");
1815 }
1816 Expr::TryCast { expr, data_type } => {
1817 self.write_keyword("TRY_CAST(");
1818 self.gen_expr(expr);
1819 self.write(" ");
1820 self.write_keyword("AS ");
1821 self.gen_data_type(data_type);
1822 self.write(")");
1823 }
1824 Expr::Extract { field, expr } => {
1825 self.write_keyword("EXTRACT(");
1826 self.gen_datetime_field(field);
1827 self.write(" ");
1828 self.write_keyword("FROM ");
1829 self.gen_expr(expr);
1830 self.write(")");
1831 }
1832 Expr::Interval { value, unit } => {
1833 self.write_keyword("INTERVAL ");
1834 self.gen_expr(value);
1835 if let Some(unit) = unit {
1836 self.write(" ");
1837 self.gen_datetime_field(unit);
1838 }
1839 }
1840 Expr::ArrayLiteral(items) => {
1841 self.write_keyword("ARRAY[");
1842 self.gen_expr_list(items);
1843 self.write("]");
1844 }
1845 Expr::Tuple(items) => {
1846 self.write("(");
1847 self.gen_expr_list(items);
1848 self.write(")");
1849 }
1850 Expr::Coalesce(items) => {
1851 self.write_keyword("COALESCE(");
1852 self.gen_expr_list(items);
1853 self.write(")");
1854 }
1855 Expr::If {
1856 condition,
1857 true_val,
1858 false_val,
1859 } => {
1860 self.write_keyword("IF(");
1861 self.gen_expr(condition);
1862 self.write(", ");
1863 self.gen_expr(true_val);
1864 if let Some(fv) = false_val {
1865 self.write(", ");
1866 self.gen_expr(fv);
1867 }
1868 self.write(")");
1869 }
1870 Expr::NullIf { expr, r#else } => {
1871 self.write_keyword("NULLIF(");
1872 self.gen_expr(expr);
1873 self.write(", ");
1874 self.gen_expr(r#else);
1875 self.write(")");
1876 }
1877 Expr::Collate { expr, collation } => {
1878 self.gen_expr(expr);
1879 self.write(" ");
1880 self.write_keyword("COLLATE ");
1881 self.write(collation);
1882 }
1883 Expr::Parameter(p) => self.write(p),
1884 Expr::TypeExpr(dt) => self.gen_data_type(dt),
1885 Expr::QualifiedWildcard { table } => {
1886 self.write(table);
1887 self.write(".*");
1888 }
1889 Expr::Alias { expr, name } => {
1890 self.gen_expr(expr);
1891 self.write(" ");
1892 self.write_keyword("AS ");
1893 self.write(name);
1894 }
1895 Expr::ArrayIndex { expr, index } => {
1896 self.gen_expr(expr);
1897 self.write("[");
1898 self.gen_expr(index);
1899 self.write("]");
1900 }
1901 Expr::JsonAccess {
1902 expr,
1903 path,
1904 as_text,
1905 } => {
1906 self.gen_expr(expr);
1907 if *as_text {
1908 self.write("->>");
1909 } else {
1910 self.write("->");
1911 }
1912 self.gen_expr(path);
1913 }
1914 Expr::Lambda { params, body } => {
1915 if params.len() == 1 {
1916 self.write(¶ms[0]);
1917 } else {
1918 self.write("(");
1919 self.write(¶ms.join(", "));
1920 self.write(")");
1921 }
1922 self.write(" -> ");
1923 self.gen_expr(body);
1924 }
1925 Expr::TypedFunction { func, filter, over } => {
1926 self.gen_typed_function(func);
1927
1928 if let Some(filter_expr) = filter {
1929 self.write(" ");
1930 self.write_keyword("FILTER (WHERE ");
1931 self.gen_expr(filter_expr);
1932 self.write(")");
1933 }
1934 if let Some(spec) = over {
1935 self.write(" ");
1936 self.write_keyword("OVER ");
1937 if let Some(wref) = &spec.window_ref {
1938 if spec.partition_by.is_empty()
1939 && spec.order_by.is_empty()
1940 && spec.frame.is_none()
1941 {
1942 self.write(wref);
1943 } else {
1944 self.write("(");
1945 self.gen_window_spec(spec);
1946 self.write(")");
1947 }
1948 } else {
1949 self.write("(");
1950 self.gen_window_spec(spec);
1951 self.write(")");
1952 }
1953 }
1954 }
1955 Expr::Commented { expr, comments } => {
1956 for comment in comments {
1957 let normalized = self.normalize_comment(comment);
1958 self.write(&normalized);
1959 self.write(" ");
1960 }
1961 self.gen_expr(expr);
1962 }
1963 }
1964 }
1965
1966 fn gen_window_spec(&mut self, spec: &WindowSpec) {
1967 if let Some(wref) = &spec.window_ref {
1968 self.write(wref);
1969 if !spec.partition_by.is_empty() || !spec.order_by.is_empty() || spec.frame.is_some() {
1970 self.write(" ");
1971 }
1972 }
1973 if !spec.partition_by.is_empty() {
1974 self.write_keyword("PARTITION BY ");
1975 self.gen_expr_list(&spec.partition_by);
1976 }
1977 if !spec.order_by.is_empty() {
1978 if !spec.partition_by.is_empty() {
1979 self.write(" ");
1980 }
1981 self.write_keyword("ORDER BY ");
1982 for (i, item) in spec.order_by.iter().enumerate() {
1983 if i > 0 {
1984 self.write(", ");
1985 }
1986 self.gen_expr(&item.expr);
1987 if !item.ascending {
1988 self.write(" ");
1989 self.write_keyword("DESC");
1990 }
1991 if let Some(nulls_first) = item.nulls_first {
1992 if nulls_first {
1993 self.write(" ");
1994 self.write_keyword("NULLS FIRST");
1995 } else {
1996 self.write(" ");
1997 self.write_keyword("NULLS LAST");
1998 }
1999 }
2000 }
2001 }
2002 if let Some(frame) = &spec.frame {
2003 self.write(" ");
2004 self.gen_window_frame(frame);
2005 }
2006 }
2007
2008 fn gen_window_frame(&mut self, frame: &WindowFrame) {
2009 match frame.kind {
2010 WindowFrameKind::Rows => self.write_keyword("ROWS "),
2011 WindowFrameKind::Range => self.write_keyword("RANGE "),
2012 WindowFrameKind::Groups => self.write_keyword("GROUPS "),
2013 }
2014 if let Some(end) = &frame.end {
2015 self.write_keyword("BETWEEN ");
2016 self.gen_window_frame_bound(&frame.start);
2017 self.write(" ");
2018 self.write_keyword("AND ");
2019 self.gen_window_frame_bound(end);
2020 } else {
2021 self.gen_window_frame_bound(&frame.start);
2022 }
2023 }
2024
2025 fn gen_window_frame_bound(&mut self, bound: &WindowFrameBound) {
2026 match bound {
2027 WindowFrameBound::CurrentRow => self.write_keyword("CURRENT ROW"),
2028 WindowFrameBound::Preceding(None) => self.write_keyword("UNBOUNDED PRECEDING"),
2029 WindowFrameBound::Preceding(Some(n)) => {
2030 self.gen_expr(n);
2031 self.write(" ");
2032 self.write_keyword("PRECEDING");
2033 }
2034 WindowFrameBound::Following(None) => self.write_keyword("UNBOUNDED FOLLOWING"),
2035 WindowFrameBound::Following(Some(n)) => {
2036 self.gen_expr(n);
2037 self.write(" ");
2038 self.write_keyword("FOLLOWING");
2039 }
2040 }
2041 }
2042
2043 fn gen_datetime_field(&mut self, field: &DateTimeField) {
2044 let name = match field {
2045 DateTimeField::Year => "YEAR",
2046 DateTimeField::Quarter => "QUARTER",
2047 DateTimeField::Month => "MONTH",
2048 DateTimeField::Week => "WEEK",
2049 DateTimeField::Day => "DAY",
2050 DateTimeField::DayOfWeek => "DOW",
2051 DateTimeField::DayOfYear => "DOY",
2052 DateTimeField::Hour => "HOUR",
2053 DateTimeField::Minute => "MINUTE",
2054 DateTimeField::Second => "SECOND",
2055 DateTimeField::Millisecond => "MILLISECOND",
2056 DateTimeField::Microsecond => "MICROSECOND",
2057 DateTimeField::Nanosecond => "NANOSECOND",
2058 DateTimeField::Epoch => "EPOCH",
2059 DateTimeField::Timezone => "TIMEZONE",
2060 DateTimeField::TimezoneHour => "TIMEZONE_HOUR",
2061 DateTimeField::TimezoneMinute => "TIMEZONE_MINUTE",
2062 };
2063 self.write(name);
2064 }
2065
2066 fn gen_typed_function(&mut self, func: &TypedFunction) {
2068 let dialect = self.dialect;
2069 let is_tsql = matches!(dialect, Some(Dialect::Tsql) | Some(Dialect::Fabric));
2070 let is_mysql = matches!(
2071 dialect,
2072 Some(Dialect::Mysql)
2073 | Some(Dialect::SingleStore)
2074 | Some(Dialect::Doris)
2075 | Some(Dialect::StarRocks)
2076 );
2077 let is_bigquery = matches!(dialect, Some(Dialect::BigQuery));
2078 let is_snowflake = matches!(dialect, Some(Dialect::Snowflake));
2079 let is_oracle = matches!(dialect, Some(Dialect::Oracle));
2080 let is_hive_family = matches!(
2081 dialect,
2082 Some(Dialect::Hive) | Some(Dialect::Spark) | Some(Dialect::Databricks)
2083 );
2084
2085 match func {
2086 TypedFunction::DateAdd {
2088 expr,
2089 interval,
2090 unit,
2091 } => {
2092 if is_tsql || is_snowflake {
2093 self.write_keyword("DATEADD(");
2094 if let Some(u) = unit {
2095 self.gen_datetime_field(u);
2096 } else {
2097 self.write_keyword("DAY");
2098 }
2099 self.write(", ");
2100 self.gen_expr(interval);
2101 self.write(", ");
2102 self.gen_expr(expr);
2103 self.write(")");
2104 } else if is_bigquery {
2105 self.write_keyword("DATE_ADD(");
2106 self.gen_expr(expr);
2107 self.write(", ");
2108 self.write_keyword("INTERVAL ");
2109 self.gen_expr(interval);
2110 self.write(" ");
2111 if let Some(u) = unit {
2112 self.gen_datetime_field(u);
2113 } else {
2114 self.write_keyword("DAY");
2115 }
2116 self.write(")");
2117 } else {
2118 self.write_keyword("DATE_ADD(");
2119 self.gen_expr(expr);
2120 self.write(", ");
2121 self.gen_expr(interval);
2122 if let Some(u) = unit {
2123 self.write(", ");
2124 self.gen_datetime_field(u);
2125 }
2126 self.write(")");
2127 }
2128 }
2129 TypedFunction::DateDiff { start, end, unit } => {
2130 if is_tsql || is_snowflake {
2131 self.write_keyword("DATEDIFF(");
2132 if let Some(u) = unit {
2133 self.gen_datetime_field(u);
2134 } else {
2135 self.write_keyword("DAY");
2136 }
2137 self.write(", ");
2138 self.gen_expr(start);
2139 self.write(", ");
2140 self.gen_expr(end);
2141 self.write(")");
2142 } else if is_bigquery {
2143 self.write_keyword("DATE_DIFF(");
2144 self.gen_expr(end);
2145 self.write(", ");
2146 self.gen_expr(start);
2147 self.write(", ");
2148 if let Some(u) = unit {
2149 self.gen_datetime_field(u);
2150 } else {
2151 self.write_keyword("DAY");
2152 }
2153 self.write(")");
2154 } else {
2155 self.write_keyword("DATEDIFF(");
2156 self.gen_expr(start);
2157 self.write(", ");
2158 self.gen_expr(end);
2159 if let Some(u) = unit {
2160 self.write(", ");
2161 self.gen_datetime_field(u);
2162 }
2163 self.write(")");
2164 }
2165 }
2166 TypedFunction::DateTrunc { unit, expr } => {
2167 if is_tsql {
2168 self.write_keyword("DATETRUNC(");
2169 self.gen_datetime_field(unit);
2170 self.write(", ");
2171 self.gen_expr(expr);
2172 self.write(")");
2173 } else if is_oracle {
2174 self.write_keyword("TRUNC(");
2175 self.gen_expr(expr);
2176 self.write(", '");
2177 self.gen_datetime_field(unit);
2178 self.write("')");
2179 } else {
2180 self.write_keyword("DATE_TRUNC(");
2181 self.write("'");
2182 self.gen_datetime_field(unit);
2183 self.write("'");
2184 self.write(", ");
2185 self.gen_expr(expr);
2186 self.write(")");
2187 }
2188 }
2189 TypedFunction::DateSub {
2190 expr,
2191 interval,
2192 unit,
2193 } => {
2194 if is_tsql || is_snowflake {
2195 self.write_keyword("DATEADD(");
2196 if let Some(u) = unit {
2197 self.gen_datetime_field(u);
2198 } else {
2199 self.write_keyword("DAY");
2200 }
2201 self.write(", -(");
2202 self.gen_expr(interval);
2203 self.write("), ");
2204 self.gen_expr(expr);
2205 self.write(")");
2206 } else if is_bigquery {
2207 self.write_keyword("DATE_SUB(");
2208 self.gen_expr(expr);
2209 self.write(", ");
2210 self.write_keyword("INTERVAL ");
2211 self.gen_expr(interval);
2212 self.write(" ");
2213 if let Some(u) = unit {
2214 self.gen_datetime_field(u);
2215 } else {
2216 self.write_keyword("DAY");
2217 }
2218 self.write(")");
2219 } else {
2220 self.write_keyword("DATE_SUB(");
2221 self.gen_expr(expr);
2222 self.write(", ");
2223 self.gen_expr(interval);
2224 if let Some(u) = unit {
2225 self.write(", ");
2226 self.gen_datetime_field(u);
2227 }
2228 self.write(")");
2229 }
2230 }
2231 TypedFunction::CurrentDate => {
2232 if is_tsql {
2233 self.write_keyword("CAST(GETDATE() AS DATE)");
2234 } else if is_mysql || is_hive_family {
2235 self.write_keyword("CURRENT_DATE()");
2236 } else {
2237 self.write_keyword("CURRENT_DATE");
2238 }
2239 }
2240 TypedFunction::CurrentTimestamp => {
2241 if is_tsql {
2242 self.write_keyword("GETDATE()");
2243 } else if is_mysql
2244 || matches!(
2245 dialect,
2246 Some(Dialect::Postgres)
2247 | Some(Dialect::DuckDb)
2248 | Some(Dialect::Sqlite)
2249 | Some(Dialect::Redshift)
2250 )
2251 {
2252 self.write_keyword("NOW()");
2253 } else {
2254 self.write_keyword("CURRENT_TIMESTAMP()");
2255 }
2256 }
2257 TypedFunction::StrToTime { expr, format } => {
2258 if is_mysql {
2259 self.write_keyword("STR_TO_DATE(");
2260 } else if is_bigquery {
2261 self.write_keyword("PARSE_TIMESTAMP(");
2262 } else {
2263 self.write_keyword("TO_TIMESTAMP(");
2264 }
2265 self.gen_expr(expr);
2266 self.write(", ");
2267 self.gen_expr(format);
2268 self.write(")");
2269 }
2270 TypedFunction::TimeToStr { expr, format } => {
2271 if is_mysql || is_hive_family {
2272 self.write_keyword("DATE_FORMAT(");
2273 } else if is_bigquery {
2274 self.write_keyword("FORMAT_TIMESTAMP(");
2275 } else if is_tsql {
2276 self.write_keyword("FORMAT(");
2277 } else {
2278 self.write_keyword("TO_CHAR(");
2279 }
2280 self.gen_expr(expr);
2281 self.write(", ");
2282 self.gen_expr(format);
2283 self.write(")");
2284 }
2285 TypedFunction::TsOrDsToDate { expr } => {
2286 if is_mysql {
2287 self.write_keyword("DATE(");
2288 self.gen_expr(expr);
2289 self.write(")");
2290 } else {
2291 self.write_keyword("CAST(");
2292 self.gen_expr(expr);
2293 self.write(" ");
2294 self.write_keyword("AS DATE)");
2295 }
2296 }
2297 TypedFunction::Year { expr } => {
2298 if is_tsql {
2299 self.write_keyword("YEAR(");
2300 self.gen_expr(expr);
2301 self.write(")");
2302 } else {
2303 self.write_keyword("EXTRACT(YEAR FROM ");
2304 self.gen_expr(expr);
2305 self.write(")");
2306 }
2307 }
2308 TypedFunction::Month { expr } => {
2309 if is_tsql {
2310 self.write_keyword("MONTH(");
2311 self.gen_expr(expr);
2312 self.write(")");
2313 } else {
2314 self.write_keyword("EXTRACT(MONTH FROM ");
2315 self.gen_expr(expr);
2316 self.write(")");
2317 }
2318 }
2319 TypedFunction::Day { expr } => {
2320 if is_tsql {
2321 self.write_keyword("DAY(");
2322 self.gen_expr(expr);
2323 self.write(")");
2324 } else {
2325 self.write_keyword("EXTRACT(DAY FROM ");
2326 self.gen_expr(expr);
2327 self.write(")");
2328 }
2329 }
2330
2331 TypedFunction::Trim {
2333 expr,
2334 trim_type,
2335 trim_chars,
2336 } => {
2337 self.write_keyword("TRIM(");
2338 match trim_type {
2339 TrimType::Leading => self.write_keyword("LEADING "),
2340 TrimType::Trailing => self.write_keyword("TRAILING "),
2341 TrimType::Both => {} }
2343 if let Some(chars) = trim_chars {
2344 self.gen_expr(chars);
2345 self.write(" ");
2346 self.write_keyword("FROM ");
2347 }
2348 self.gen_expr(expr);
2349 self.write(")");
2350 }
2351 TypedFunction::Substring {
2352 expr,
2353 start,
2354 length,
2355 } => {
2356 let name = if is_oracle
2357 || is_hive_family
2358 || is_mysql
2359 || matches!(
2360 dialect,
2361 Some(Dialect::Sqlite)
2362 | Some(Dialect::Doris)
2363 | Some(Dialect::SingleStore)
2364 | Some(Dialect::StarRocks)
2365 ) {
2366 "SUBSTR"
2367 } else {
2368 "SUBSTRING"
2369 };
2370 self.write_keyword(name);
2371 self.write("(");
2372 self.gen_expr(expr);
2373 self.write(", ");
2374 self.gen_expr(start);
2375 if let Some(l) = length {
2376 self.write(", ");
2377 self.gen_expr(l);
2378 }
2379 self.write(")");
2380 }
2381 TypedFunction::Upper { expr } => {
2382 self.write_keyword("UPPER(");
2383 self.gen_expr(expr);
2384 self.write(")");
2385 }
2386 TypedFunction::Lower { expr } => {
2387 self.write_keyword("LOWER(");
2388 self.gen_expr(expr);
2389 self.write(")");
2390 }
2391 TypedFunction::RegexpLike {
2392 expr,
2393 pattern,
2394 flags,
2395 } => {
2396 self.write_keyword("REGEXP_LIKE(");
2397 self.gen_expr(expr);
2398 self.write(", ");
2399 self.gen_expr(pattern);
2400 if let Some(f) = flags {
2401 self.write(", ");
2402 self.gen_expr(f);
2403 }
2404 self.write(")");
2405 }
2406 TypedFunction::RegexpExtract {
2407 expr,
2408 pattern,
2409 group_index,
2410 } => {
2411 if is_bigquery || is_hive_family {
2412 self.write_keyword("REGEXP_EXTRACT(");
2413 } else {
2414 self.write_keyword("REGEXP_SUBSTR(");
2415 }
2416 self.gen_expr(expr);
2417 self.write(", ");
2418 self.gen_expr(pattern);
2419 if let Some(g) = group_index {
2420 self.write(", ");
2421 self.gen_expr(g);
2422 }
2423 self.write(")");
2424 }
2425 TypedFunction::RegexpReplace {
2426 expr,
2427 pattern,
2428 replacement,
2429 flags,
2430 } => {
2431 self.write_keyword("REGEXP_REPLACE(");
2432 self.gen_expr(expr);
2433 self.write(", ");
2434 self.gen_expr(pattern);
2435 self.write(", ");
2436 self.gen_expr(replacement);
2437 if let Some(f) = flags {
2438 self.write(", ");
2439 self.gen_expr(f);
2440 }
2441 self.write(")");
2442 }
2443 TypedFunction::ConcatWs { separator, exprs } => {
2444 self.write_keyword("CONCAT_WS(");
2445 self.gen_expr(separator);
2446 for e in exprs {
2447 self.write(", ");
2448 self.gen_expr(e);
2449 }
2450 self.write(")");
2451 }
2452 TypedFunction::Split { expr, delimiter } => {
2453 if is_tsql {
2454 self.write_keyword("STRING_SPLIT(");
2455 } else {
2456 self.write_keyword("SPLIT(");
2457 }
2458 self.gen_expr(expr);
2459 self.write(", ");
2460 self.gen_expr(delimiter);
2461 self.write(")");
2462 }
2463 TypedFunction::Initcap { expr } => {
2464 self.write_keyword("INITCAP(");
2465 self.gen_expr(expr);
2466 self.write(")");
2467 }
2468 TypedFunction::Length { expr } => {
2469 let name = if is_tsql || is_bigquery || is_snowflake {
2470 "LEN"
2471 } else {
2472 "LENGTH"
2473 };
2474 self.write_keyword(name);
2475 self.write("(");
2476 self.gen_expr(expr);
2477 self.write(")");
2478 }
2479 TypedFunction::Replace { expr, from, to } => {
2480 self.write_keyword("REPLACE(");
2481 self.gen_expr(expr);
2482 self.write(", ");
2483 self.gen_expr(from);
2484 self.write(", ");
2485 self.gen_expr(to);
2486 self.write(")");
2487 }
2488 TypedFunction::Reverse { expr } => {
2489 self.write_keyword("REVERSE(");
2490 self.gen_expr(expr);
2491 self.write(")");
2492 }
2493 TypedFunction::Left { expr, n } => {
2494 self.write_keyword("LEFT(");
2495 self.gen_expr(expr);
2496 self.write(", ");
2497 self.gen_expr(n);
2498 self.write(")");
2499 }
2500 TypedFunction::Right { expr, n } => {
2501 self.write_keyword("RIGHT(");
2502 self.gen_expr(expr);
2503 self.write(", ");
2504 self.gen_expr(n);
2505 self.write(")");
2506 }
2507 TypedFunction::Lpad { expr, length, pad } => {
2508 self.write_keyword("LPAD(");
2509 self.gen_expr(expr);
2510 self.write(", ");
2511 self.gen_expr(length);
2512 if let Some(p) = pad {
2513 self.write(", ");
2514 self.gen_expr(p);
2515 }
2516 self.write(")");
2517 }
2518 TypedFunction::Rpad { expr, length, pad } => {
2519 self.write_keyword("RPAD(");
2520 self.gen_expr(expr);
2521 self.write(", ");
2522 self.gen_expr(length);
2523 if let Some(p) = pad {
2524 self.write(", ");
2525 self.gen_expr(p);
2526 }
2527 self.write(")");
2528 }
2529
2530 TypedFunction::Count { expr, distinct } => {
2532 self.write_keyword("COUNT(");
2533 if *distinct {
2534 self.write_keyword("DISTINCT ");
2535 }
2536 self.gen_expr(expr);
2537 self.write(")");
2538 }
2539 TypedFunction::Sum { expr, distinct } => {
2540 self.write_keyword("SUM(");
2541 if *distinct {
2542 self.write_keyword("DISTINCT ");
2543 }
2544 self.gen_expr(expr);
2545 self.write(")");
2546 }
2547 TypedFunction::Avg { expr, distinct } => {
2548 self.write_keyword("AVG(");
2549 if *distinct {
2550 self.write_keyword("DISTINCT ");
2551 }
2552 self.gen_expr(expr);
2553 self.write(")");
2554 }
2555 TypedFunction::Min { expr } => {
2556 self.write_keyword("MIN(");
2557 self.gen_expr(expr);
2558 self.write(")");
2559 }
2560 TypedFunction::Max { expr } => {
2561 self.write_keyword("MAX(");
2562 self.gen_expr(expr);
2563 self.write(")");
2564 }
2565 TypedFunction::ArrayAgg { expr, distinct } => {
2566 let name = if matches!(dialect, Some(Dialect::DuckDb)) {
2567 "LIST"
2568 } else if is_hive_family {
2569 "COLLECT_LIST"
2570 } else {
2571 "ARRAY_AGG"
2572 };
2573 self.write_keyword(name);
2574 self.write("(");
2575 if *distinct {
2576 self.write_keyword("DISTINCT ");
2577 }
2578 self.gen_expr(expr);
2579 self.write(")");
2580 }
2581 TypedFunction::ApproxDistinct { expr } => {
2582 let name = if is_hive_family
2583 || matches!(
2584 dialect,
2585 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2586 ) {
2587 "APPROX_DISTINCT"
2588 } else {
2589 "APPROX_COUNT_DISTINCT"
2590 };
2591 self.write_keyword(name);
2592 self.write("(");
2593 self.gen_expr(expr);
2594 self.write(")");
2595 }
2596 TypedFunction::Variance { expr } => {
2597 let name = if is_tsql || is_oracle {
2598 "VAR"
2599 } else {
2600 "VARIANCE"
2601 };
2602 self.write_keyword(name);
2603 self.write("(");
2604 self.gen_expr(expr);
2605 self.write(")");
2606 }
2607 TypedFunction::Stddev { expr } => {
2608 self.write_keyword("STDDEV(");
2609 self.gen_expr(expr);
2610 self.write(")");
2611 }
2612
2613 TypedFunction::ArrayConcat { arrays } => {
2615 let name = if matches!(
2616 dialect,
2617 Some(Dialect::Postgres) | Some(Dialect::Redshift) | Some(Dialect::DuckDb)
2618 ) {
2619 "ARRAY_CAT"
2620 } else {
2621 "ARRAY_CONCAT"
2622 };
2623 self.write_keyword(name);
2624 self.write("(");
2625 self.gen_expr_list(arrays);
2626 self.write(")");
2627 }
2628 TypedFunction::ArrayContains { array, element } => {
2629 self.write_keyword("ARRAY_CONTAINS(");
2630 self.gen_expr(array);
2631 self.write(", ");
2632 self.gen_expr(element);
2633 self.write(")");
2634 }
2635 TypedFunction::ArraySize { expr } => {
2636 let name = if matches!(dialect, Some(Dialect::Postgres) | Some(Dialect::Redshift)) {
2637 "ARRAY_LENGTH"
2638 } else if is_hive_family {
2639 "SIZE"
2640 } else {
2641 "ARRAY_SIZE"
2642 };
2643 self.write_keyword(name);
2644 self.write("(");
2645 self.gen_expr(expr);
2646 self.write(")");
2647 }
2648 TypedFunction::Explode { expr } => {
2649 self.write_keyword("EXPLODE(");
2650 self.gen_expr(expr);
2651 self.write(")");
2652 }
2653 TypedFunction::GenerateSeries { start, stop, step } => {
2654 self.write_keyword("GENERATE_SERIES(");
2655 self.gen_expr(start);
2656 self.write(", ");
2657 self.gen_expr(stop);
2658 if let Some(s) = step {
2659 self.write(", ");
2660 self.gen_expr(s);
2661 }
2662 self.write(")");
2663 }
2664 TypedFunction::Flatten { expr } => {
2665 self.write_keyword("FLATTEN(");
2666 self.gen_expr(expr);
2667 self.write(")");
2668 }
2669
2670 TypedFunction::JSONExtract { expr, path } => {
2672 if is_tsql {
2673 self.write_keyword("JSON_VALUE(");
2674 } else {
2675 self.write_keyword("JSON_EXTRACT(");
2676 }
2677 self.gen_expr(expr);
2678 self.write(", ");
2679 self.gen_expr(path);
2680 self.write(")");
2681 }
2682 TypedFunction::JSONExtractScalar { expr, path } => {
2683 if is_bigquery {
2684 self.write_keyword("JSON_EXTRACT_SCALAR(");
2685 } else if is_tsql {
2686 self.write_keyword("JSON_VALUE(");
2687 } else {
2688 self.write_keyword("JSON_EXTRACT_SCALAR(");
2689 }
2690 self.gen_expr(expr);
2691 self.write(", ");
2692 self.gen_expr(path);
2693 self.write(")");
2694 }
2695 TypedFunction::ParseJSON { expr } => {
2696 if is_snowflake {
2697 self.write_keyword("PARSE_JSON(");
2698 } else if is_bigquery {
2699 self.write_keyword("JSON_PARSE(");
2700 } else {
2701 self.write_keyword("PARSE_JSON(");
2702 }
2703 self.gen_expr(expr);
2704 self.write(")");
2705 }
2706 TypedFunction::JSONFormat { expr } => {
2707 if is_bigquery {
2708 self.write_keyword("TO_JSON_STRING(");
2709 } else {
2710 self.write_keyword("JSON_FORMAT(");
2711 }
2712 self.gen_expr(expr);
2713 self.write(")");
2714 }
2715
2716 TypedFunction::RowNumber => self.write_keyword("ROW_NUMBER()"),
2718 TypedFunction::Rank => self.write_keyword("RANK()"),
2719 TypedFunction::DenseRank => self.write_keyword("DENSE_RANK()"),
2720 TypedFunction::NTile { n } => {
2721 self.write_keyword("NTILE(");
2722 self.gen_expr(n);
2723 self.write(")");
2724 }
2725 TypedFunction::Lead {
2726 expr,
2727 offset,
2728 default,
2729 } => {
2730 self.write_keyword("LEAD(");
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::Lag {
2743 expr,
2744 offset,
2745 default,
2746 } => {
2747 self.write_keyword("LAG(");
2748 self.gen_expr(expr);
2749 if let Some(o) = offset {
2750 self.write(", ");
2751 self.gen_expr(o);
2752 }
2753 if let Some(d) = default {
2754 self.write(", ");
2755 self.gen_expr(d);
2756 }
2757 self.write(")");
2758 }
2759 TypedFunction::FirstValue { expr } => {
2760 self.write_keyword("FIRST_VALUE(");
2761 self.gen_expr(expr);
2762 self.write(")");
2763 }
2764 TypedFunction::LastValue { expr } => {
2765 self.write_keyword("LAST_VALUE(");
2766 self.gen_expr(expr);
2767 self.write(")");
2768 }
2769
2770 TypedFunction::Abs { expr } => {
2772 self.write_keyword("ABS(");
2773 self.gen_expr(expr);
2774 self.write(")");
2775 }
2776 TypedFunction::Ceil { expr } => {
2777 let name = if is_tsql { "CEILING" } else { "CEIL" };
2778 self.write_keyword(name);
2779 self.write("(");
2780 self.gen_expr(expr);
2781 self.write(")");
2782 }
2783 TypedFunction::Floor { expr } => {
2784 self.write_keyword("FLOOR(");
2785 self.gen_expr(expr);
2786 self.write(")");
2787 }
2788 TypedFunction::Round { expr, decimals } => {
2789 self.write_keyword("ROUND(");
2790 self.gen_expr(expr);
2791 if let Some(d) = decimals {
2792 self.write(", ");
2793 self.gen_expr(d);
2794 }
2795 self.write(")");
2796 }
2797 TypedFunction::Log { expr, base } => {
2798 if let Some(b) = base {
2799 self.write_keyword("LOG(");
2800 if matches!(dialect, Some(Dialect::Postgres) | Some(Dialect::DuckDb)) {
2801 self.gen_expr(b);
2803 self.write(", ");
2804 self.gen_expr(expr);
2805 } else {
2806 self.gen_expr(expr);
2808 self.write(", ");
2809 self.gen_expr(b);
2810 }
2811 self.write(")");
2812 } else {
2813 self.write_keyword("LOG(");
2815 self.gen_expr(expr);
2816 self.write(")");
2817 }
2818 }
2819 TypedFunction::Ln { expr } => {
2820 self.write_keyword("LN(");
2821 self.gen_expr(expr);
2822 self.write(")");
2823 }
2824 TypedFunction::Pow { base, exponent } => {
2825 let name = if is_tsql || is_oracle { "POWER" } else { "POW" };
2826 self.write_keyword(name);
2827 self.write("(");
2828 self.gen_expr(base);
2829 self.write(", ");
2830 self.gen_expr(exponent);
2831 self.write(")");
2832 }
2833 TypedFunction::Sqrt { expr } => {
2834 self.write_keyword("SQRT(");
2835 self.gen_expr(expr);
2836 self.write(")");
2837 }
2838 TypedFunction::Greatest { exprs } => {
2839 self.write_keyword("GREATEST(");
2840 self.gen_expr_list(exprs);
2841 self.write(")");
2842 }
2843 TypedFunction::Least { exprs } => {
2844 self.write_keyword("LEAST(");
2845 self.gen_expr_list(exprs);
2846 self.write(")");
2847 }
2848 TypedFunction::Mod { left, right } => {
2849 self.write_keyword("MOD(");
2850 self.gen_expr(left);
2851 self.write(", ");
2852 self.gen_expr(right);
2853 self.write(")");
2854 }
2855
2856 TypedFunction::Hex { expr } => {
2858 let name = if matches!(
2859 dialect,
2860 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2861 ) {
2862 "TO_HEX"
2863 } else {
2864 "HEX"
2865 };
2866 self.write_keyword(name);
2867 self.write("(");
2868 self.gen_expr(expr);
2869 self.write(")");
2870 }
2871 TypedFunction::Unhex { expr } => {
2872 let name = if matches!(
2873 dialect,
2874 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2875 ) {
2876 "FROM_HEX"
2877 } else {
2878 "UNHEX"
2879 };
2880 self.write_keyword(name);
2881 self.write("(");
2882 self.gen_expr(expr);
2883 self.write(")");
2884 }
2885 TypedFunction::Md5 { expr } => {
2886 self.write_keyword("MD5(");
2887 self.gen_expr(expr);
2888 self.write(")");
2889 }
2890 TypedFunction::Sha { expr } => {
2891 let name = if is_mysql { "SHA1" } else { "SHA" };
2892 self.write_keyword(name);
2893 self.write("(");
2894 self.gen_expr(expr);
2895 self.write(")");
2896 }
2897 TypedFunction::Sha2 { expr, bit_length } => {
2898 self.write_keyword("SHA2(");
2899 self.gen_expr(expr);
2900 self.write(", ");
2901 self.gen_expr(bit_length);
2902 self.write(")");
2903 }
2904 }
2905 }
2906}
2907
2908impl Default for Generator {
2909 fn default() -> Self {
2910 Self::new()
2911 }
2912}
2913
2914#[cfg(test)]
2915mod tests {
2916 use super::*;
2917 use crate::parser::Parser;
2918
2919 fn roundtrip(sql: &str) -> String {
2920 let stmt = Parser::new(sql).unwrap().parse_statement().unwrap();
2921 let mut g = Generator::new();
2922 g.generate(&stmt)
2923 }
2924
2925 #[test]
2926 fn test_select_roundtrip() {
2927 assert_eq!(roundtrip("SELECT a, b FROM t"), "SELECT a, b FROM t");
2928 }
2929
2930 #[test]
2931 fn test_select_where() {
2932 assert_eq!(
2933 roundtrip("SELECT x FROM t WHERE x > 10"),
2934 "SELECT x FROM t WHERE x > 10"
2935 );
2936 }
2937
2938 #[test]
2939 fn test_select_wildcard() {
2940 assert_eq!(roundtrip("SELECT * FROM users"), "SELECT * FROM users");
2941 }
2942
2943 #[test]
2944 fn test_insert_values() {
2945 assert_eq!(
2946 roundtrip("INSERT INTO t (a, b) VALUES (1, 'hello')"),
2947 "INSERT INTO t (a, b) VALUES (1, 'hello')"
2948 );
2949 }
2950
2951 #[test]
2952 fn test_delete() {
2953 assert_eq!(
2954 roundtrip("DELETE FROM users WHERE id = 1"),
2955 "DELETE FROM users WHERE id = 1"
2956 );
2957 }
2958
2959 #[test]
2960 fn test_join() {
2961 assert_eq!(
2962 roundtrip("SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"),
2963 "SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"
2964 );
2965 }
2966
2967 #[test]
2968 fn test_create_table() {
2969 assert_eq!(
2970 roundtrip("CREATE TABLE users (id INT NOT NULL, name VARCHAR(255), email TEXT)"),
2971 "CREATE TABLE users (id INT NOT NULL, name VARCHAR(255), email TEXT)"
2972 );
2973 }
2974
2975 #[test]
2976 fn test_cte_roundtrip() {
2977 let sql = "WITH cte AS (SELECT 1 AS x) SELECT x FROM cte";
2978 assert_eq!(
2979 roundtrip(sql),
2980 "WITH cte AS (SELECT 1 AS x) SELECT x FROM cte"
2981 );
2982 }
2983
2984 #[test]
2985 fn test_union_roundtrip() {
2986 let sql = "SELECT 1 UNION ALL SELECT 2";
2987 assert_eq!(roundtrip(sql), "SELECT 1 UNION ALL SELECT 2");
2988 }
2989
2990 #[test]
2991 fn test_cast_roundtrip() {
2992 assert_eq!(
2993 roundtrip("SELECT CAST(x AS INT) FROM t"),
2994 "SELECT CAST(x AS INT) FROM t"
2995 );
2996 }
2997
2998 #[test]
2999 fn test_exists_roundtrip() {
3000 assert_eq!(
3001 roundtrip("SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2)"),
3002 "SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2)"
3003 );
3004 }
3005
3006 #[test]
3007 fn test_extract_roundtrip() {
3008 assert_eq!(
3009 roundtrip("SELECT EXTRACT(YEAR FROM created_at) FROM t"),
3010 "SELECT EXTRACT(YEAR FROM created_at) FROM t"
3011 );
3012 }
3013
3014 #[test]
3015 fn test_window_function_roundtrip() {
3016 assert_eq!(
3017 roundtrip("SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) FROM emp"),
3018 "SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) FROM emp"
3019 );
3020 }
3021
3022 #[test]
3023 fn test_subquery_from_roundtrip() {
3024 assert_eq!(
3025 roundtrip("SELECT * FROM (SELECT 1 AS x) AS sub"),
3026 "SELECT * FROM (SELECT 1 AS x) AS sub"
3027 );
3028 }
3029
3030 #[test]
3031 fn test_in_subquery_roundtrip() {
3032 assert_eq!(
3033 roundtrip("SELECT * FROM t WHERE id IN (SELECT id FROM t2)"),
3034 "SELECT * FROM t WHERE id IN (SELECT id FROM t2)"
3035 );
3036 }
3037
3038 fn pretty_print(sql: &str) -> String {
3043 let stmt = Parser::new(sql).unwrap().parse_statement().unwrap();
3044 let mut g = Generator::pretty();
3045 g.generate(&stmt)
3046 }
3047
3048 #[test]
3049 fn test_pretty_simple_select() {
3050 assert_eq!(
3051 pretty_print("SELECT a, b, c FROM t"),
3052 "SELECT\n a,\n b,\n c\nFROM\n t"
3053 );
3054 }
3055
3056 #[test]
3057 fn test_pretty_select_where() {
3058 assert_eq!(
3059 pretty_print("SELECT a FROM t WHERE a > 1"),
3060 "SELECT\n a\nFROM\n t\nWHERE\n a > 1"
3061 );
3062 }
3063
3064 #[test]
3065 fn test_pretty_select_group_by_having() {
3066 assert_eq!(
3067 pretty_print("SELECT a, COUNT(*) FROM t GROUP BY a HAVING COUNT(*) > 1"),
3068 "SELECT\n a,\n COUNT(*)\nFROM\n t\nGROUP BY\n a\nHAVING\n COUNT(*) > 1"
3069 );
3070 }
3071
3072 #[test]
3073 fn test_pretty_select_order_by_limit() {
3074 assert_eq!(
3075 pretty_print("SELECT a FROM t ORDER BY a DESC LIMIT 10"),
3076 "SELECT\n a\nFROM\n t\nORDER BY\n a DESC\nLIMIT 10"
3077 );
3078 }
3079
3080 #[test]
3081 fn test_pretty_join() {
3082 assert_eq!(
3083 pretty_print("SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"),
3084 "SELECT\n a.id,\n b.name\nFROM\n a\nINNER JOIN\n b\n ON a.id = b.a_id"
3085 );
3086 }
3087
3088 #[test]
3089 fn test_pretty_cte() {
3090 assert_eq!(
3091 pretty_print("WITH cte AS (SELECT 1 AS x) SELECT x FROM cte"),
3092 "WITH cte AS (\n SELECT\n 1 AS x\n)\nSELECT\n x\nFROM\n cte"
3093 );
3094 }
3095
3096 #[test]
3097 fn test_pretty_union() {
3098 assert_eq!(
3099 pretty_print("SELECT 1 UNION ALL SELECT 2"),
3100 "SELECT\n 1\nUNION ALL\nSELECT\n 2"
3101 );
3102 }
3103
3104 #[test]
3105 fn test_pretty_insert() {
3106 assert_eq!(
3107 pretty_print("INSERT INTO t (a, b) VALUES (1, 'hello'), (2, 'world')"),
3108 "INSERT INTO t (a, b)\nVALUES\n (1, 'hello'),\n (2, 'world')"
3109 );
3110 }
3111
3112 #[test]
3113 fn test_pretty_update() {
3114 assert_eq!(
3115 pretty_print("UPDATE t SET a = 1, b = 2 WHERE c = 3"),
3116 "UPDATE t\nSET\n a = 1,\n b = 2\nWHERE\n c = 3"
3117 );
3118 }
3119
3120 #[test]
3121 fn test_pretty_delete() {
3122 assert_eq!(
3123 pretty_print("DELETE FROM t WHERE id = 1"),
3124 "DELETE FROM t\nWHERE\n id = 1"
3125 );
3126 }
3127
3128 #[test]
3129 fn test_pretty_create_table() {
3130 assert_eq!(
3131 pretty_print("CREATE TABLE t (id INT NOT NULL, name VARCHAR(255), email TEXT)"),
3132 "CREATE TABLE t (\n id INT NOT NULL,\n name VARCHAR(255),\n email TEXT\n)"
3133 );
3134 }
3135
3136 #[test]
3137 fn test_pretty_complex_query() {
3138 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";
3139 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";
3140 assert_eq!(pretty_print(sql), expected);
3141 }
3142
3143 #[test]
3144 fn test_pretty_select_distinct() {
3145 assert_eq!(
3146 pretty_print("SELECT DISTINCT a, b FROM t"),
3147 "SELECT DISTINCT\n a,\n b\nFROM\n t"
3148 );
3149 }
3150}