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