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