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