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