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