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