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