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