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::BinaryOp { left, op, right } => {
1270 self.gen_expr(left);
1271 self.write(Self::binary_op_str(op));
1272 self.gen_expr(right);
1273 }
1274 Expr::AnyOp { expr, op, right } => {
1275 self.gen_expr(expr);
1276 self.write(Self::binary_op_str(op));
1277 self.write_keyword("ANY");
1278 self.write("(");
1279 if let Expr::Subquery(query) = right.as_ref() {
1280 self.gen_statement(query);
1281 } else {
1282 self.gen_expr(right);
1283 }
1284 self.write(")");
1285 }
1286 Expr::AllOp { expr, op, right } => {
1287 self.gen_expr(expr);
1288 self.write(Self::binary_op_str(op));
1289 self.write_keyword("ALL");
1290 self.write("(");
1291 if let Expr::Subquery(query) = right.as_ref() {
1292 self.gen_statement(query);
1293 } else {
1294 self.gen_expr(right);
1295 }
1296 self.write(")");
1297 }
1298 Expr::UnaryOp { op, expr } => {
1299 let op_str = match op {
1300 UnaryOperator::Not => "NOT ",
1301 UnaryOperator::Minus => "-",
1302 UnaryOperator::Plus => "+",
1303 UnaryOperator::BitwiseNot => "~",
1304 };
1305 self.write(op_str);
1306 self.gen_expr(expr);
1307 }
1308 Expr::Function {
1309 name,
1310 args,
1311 distinct,
1312 filter,
1313 over,
1314 } => {
1315 self.write(name);
1316 self.write("(");
1317 if *distinct {
1318 self.write_keyword("DISTINCT ");
1319 }
1320 self.gen_expr_list(args);
1321 self.write(")");
1322
1323 if let Some(filter_expr) = filter {
1324 self.write(" ");
1325 self.write_keyword("FILTER (WHERE ");
1326 self.gen_expr(filter_expr);
1327 self.write(")");
1328 }
1329 if let Some(spec) = over {
1330 self.write(" ");
1331 self.write_keyword("OVER ");
1332 if let Some(wref) = &spec.window_ref {
1333 if spec.partition_by.is_empty()
1334 && spec.order_by.is_empty()
1335 && spec.frame.is_none()
1336 {
1337 self.write(wref);
1338 } else {
1339 self.write("(");
1340 self.gen_window_spec(spec);
1341 self.write(")");
1342 }
1343 } else {
1344 self.write("(");
1345 self.gen_window_spec(spec);
1346 self.write(")");
1347 }
1348 }
1349 }
1350 Expr::Between {
1351 expr,
1352 low,
1353 high,
1354 negated,
1355 } => {
1356 self.gen_expr(expr);
1357 if *negated {
1358 self.write(" ");
1359 self.write_keyword("NOT");
1360 }
1361 self.write(" ");
1362 self.write_keyword("BETWEEN ");
1363 self.gen_expr(low);
1364 self.write(" ");
1365 self.write_keyword("AND ");
1366 self.gen_expr(high);
1367 }
1368 Expr::InList {
1369 expr,
1370 list,
1371 negated,
1372 } => {
1373 self.gen_expr(expr);
1374 if *negated {
1375 self.write(" ");
1376 self.write_keyword("NOT");
1377 }
1378 self.write(" ");
1379 self.write_keyword("IN (");
1380 self.gen_expr_list(list);
1381 self.write(")");
1382 }
1383 Expr::InSubquery {
1384 expr,
1385 subquery,
1386 negated,
1387 } => {
1388 self.gen_expr(expr);
1389 if *negated {
1390 self.write(" ");
1391 self.write_keyword("NOT");
1392 }
1393 self.write(" ");
1394 self.write_keyword("IN (");
1395 self.gen_statement(subquery);
1396 self.write(")");
1397 }
1398 Expr::IsNull { expr, negated } => {
1399 self.gen_expr(expr);
1400 if *negated {
1401 self.write(" ");
1402 self.write_keyword("IS NOT NULL");
1403 } else {
1404 self.write(" ");
1405 self.write_keyword("IS NULL");
1406 }
1407 }
1408 Expr::IsBool {
1409 expr,
1410 value,
1411 negated,
1412 } => {
1413 self.gen_expr(expr);
1414 self.write(" ");
1415 match (negated, value) {
1416 (false, true) => self.write_keyword("IS TRUE"),
1417 (false, false) => self.write_keyword("IS FALSE"),
1418 (true, true) => self.write_keyword("IS NOT TRUE"),
1419 (true, false) => self.write_keyword("IS NOT FALSE"),
1420 }
1421 }
1422 Expr::Like {
1423 expr,
1424 pattern,
1425 negated,
1426 escape,
1427 } => {
1428 self.gen_expr(expr);
1429 if *negated {
1430 self.write(" ");
1431 self.write_keyword("NOT");
1432 }
1433 self.write(" ");
1434 self.write_keyword("LIKE ");
1435 self.gen_expr(pattern);
1436 if let Some(esc) = escape {
1437 self.write(" ");
1438 self.write_keyword("ESCAPE ");
1439 self.gen_expr(esc);
1440 }
1441 }
1442 Expr::ILike {
1443 expr,
1444 pattern,
1445 negated,
1446 escape,
1447 } => {
1448 self.gen_expr(expr);
1449 if *negated {
1450 self.write(" ");
1451 self.write_keyword("NOT");
1452 }
1453 self.write(" ");
1454 self.write_keyword("ILIKE ");
1455 self.gen_expr(pattern);
1456 if let Some(esc) = escape {
1457 self.write(" ");
1458 self.write_keyword("ESCAPE ");
1459 self.gen_expr(esc);
1460 }
1461 }
1462 Expr::Case {
1463 operand,
1464 when_clauses,
1465 else_clause,
1466 } => {
1467 self.write_keyword("CASE");
1468 if let Some(op) = operand {
1469 self.write(" ");
1470 self.gen_expr(op);
1471 }
1472 for (cond, result) in when_clauses {
1473 self.write(" ");
1474 self.write_keyword("WHEN ");
1475 self.gen_expr(cond);
1476 self.write(" ");
1477 self.write_keyword("THEN ");
1478 self.gen_expr(result);
1479 }
1480 if let Some(el) = else_clause {
1481 self.write(" ");
1482 self.write_keyword("ELSE ");
1483 self.gen_expr(el);
1484 }
1485 self.write(" ");
1486 self.write_keyword("END");
1487 }
1488 Expr::Nested(inner) => {
1489 self.write("(");
1490 self.gen_expr(inner);
1491 self.write(")");
1492 }
1493 Expr::Subquery(query) => {
1494 self.write("(");
1495 self.gen_statement(query);
1496 self.write(")");
1497 }
1498 Expr::Exists { subquery, negated } => {
1499 if *negated {
1500 self.write_keyword("NOT ");
1501 }
1502 self.write_keyword("EXISTS (");
1503 self.gen_statement(subquery);
1504 self.write(")");
1505 }
1506 Expr::Cast { expr, data_type } => {
1507 self.write_keyword("CAST(");
1508 self.gen_expr(expr);
1509 self.write(" ");
1510 self.write_keyword("AS ");
1511 self.gen_data_type(data_type);
1512 self.write(")");
1513 }
1514 Expr::TryCast { expr, data_type } => {
1515 self.write_keyword("TRY_CAST(");
1516 self.gen_expr(expr);
1517 self.write(" ");
1518 self.write_keyword("AS ");
1519 self.gen_data_type(data_type);
1520 self.write(")");
1521 }
1522 Expr::Extract { field, expr } => {
1523 self.write_keyword("EXTRACT(");
1524 self.gen_datetime_field(field);
1525 self.write(" ");
1526 self.write_keyword("FROM ");
1527 self.gen_expr(expr);
1528 self.write(")");
1529 }
1530 Expr::Interval { value, unit } => {
1531 self.write_keyword("INTERVAL ");
1532 self.gen_expr(value);
1533 if let Some(unit) = unit {
1534 self.write(" ");
1535 self.gen_datetime_field(unit);
1536 }
1537 }
1538 Expr::ArrayLiteral(items) => {
1539 self.write_keyword("ARRAY[");
1540 self.gen_expr_list(items);
1541 self.write("]");
1542 }
1543 Expr::Tuple(items) => {
1544 self.write("(");
1545 self.gen_expr_list(items);
1546 self.write(")");
1547 }
1548 Expr::Coalesce(items) => {
1549 self.write_keyword("COALESCE(");
1550 self.gen_expr_list(items);
1551 self.write(")");
1552 }
1553 Expr::If {
1554 condition,
1555 true_val,
1556 false_val,
1557 } => {
1558 self.write_keyword("IF(");
1559 self.gen_expr(condition);
1560 self.write(", ");
1561 self.gen_expr(true_val);
1562 if let Some(fv) = false_val {
1563 self.write(", ");
1564 self.gen_expr(fv);
1565 }
1566 self.write(")");
1567 }
1568 Expr::NullIf { expr, r#else } => {
1569 self.write_keyword("NULLIF(");
1570 self.gen_expr(expr);
1571 self.write(", ");
1572 self.gen_expr(r#else);
1573 self.write(")");
1574 }
1575 Expr::Collate { expr, collation } => {
1576 self.gen_expr(expr);
1577 self.write(" ");
1578 self.write_keyword("COLLATE ");
1579 self.write(collation);
1580 }
1581 Expr::Parameter(p) => self.write(p),
1582 Expr::TypeExpr(dt) => self.gen_data_type(dt),
1583 Expr::QualifiedWildcard { table } => {
1584 self.write(table);
1585 self.write(".*");
1586 }
1587 Expr::Alias { expr, name } => {
1588 self.gen_expr(expr);
1589 self.write(" ");
1590 self.write_keyword("AS ");
1591 self.write(name);
1592 }
1593 Expr::ArrayIndex { expr, index } => {
1594 self.gen_expr(expr);
1595 self.write("[");
1596 self.gen_expr(index);
1597 self.write("]");
1598 }
1599 Expr::JsonAccess {
1600 expr,
1601 path,
1602 as_text,
1603 } => {
1604 self.gen_expr(expr);
1605 if *as_text {
1606 self.write("->>");
1607 } else {
1608 self.write("->");
1609 }
1610 self.gen_expr(path);
1611 }
1612 Expr::Lambda { params, body } => {
1613 if params.len() == 1 {
1614 self.write(¶ms[0]);
1615 } else {
1616 self.write("(");
1617 self.write(¶ms.join(", "));
1618 self.write(")");
1619 }
1620 self.write(" -> ");
1621 self.gen_expr(body);
1622 }
1623 Expr::TypedFunction { func, filter, over } => {
1624 self.gen_typed_function(func);
1625
1626 if let Some(filter_expr) = filter {
1627 self.write(" ");
1628 self.write_keyword("FILTER (WHERE ");
1629 self.gen_expr(filter_expr);
1630 self.write(")");
1631 }
1632 if let Some(spec) = over {
1633 self.write(" ");
1634 self.write_keyword("OVER ");
1635 if let Some(wref) = &spec.window_ref {
1636 if spec.partition_by.is_empty()
1637 && spec.order_by.is_empty()
1638 && spec.frame.is_none()
1639 {
1640 self.write(wref);
1641 } else {
1642 self.write("(");
1643 self.gen_window_spec(spec);
1644 self.write(")");
1645 }
1646 } else {
1647 self.write("(");
1648 self.gen_window_spec(spec);
1649 self.write(")");
1650 }
1651 }
1652 }
1653 }
1654 }
1655
1656 fn gen_window_spec(&mut self, spec: &WindowSpec) {
1657 if let Some(wref) = &spec.window_ref {
1658 self.write(wref);
1659 if !spec.partition_by.is_empty() || !spec.order_by.is_empty() || spec.frame.is_some() {
1660 self.write(" ");
1661 }
1662 }
1663 if !spec.partition_by.is_empty() {
1664 self.write_keyword("PARTITION BY ");
1665 self.gen_expr_list(&spec.partition_by);
1666 }
1667 if !spec.order_by.is_empty() {
1668 if !spec.partition_by.is_empty() {
1669 self.write(" ");
1670 }
1671 self.write_keyword("ORDER BY ");
1672 for (i, item) in spec.order_by.iter().enumerate() {
1673 if i > 0 {
1674 self.write(", ");
1675 }
1676 self.gen_expr(&item.expr);
1677 if !item.ascending {
1678 self.write(" ");
1679 self.write_keyword("DESC");
1680 }
1681 if let Some(nulls_first) = item.nulls_first {
1682 if nulls_first {
1683 self.write(" ");
1684 self.write_keyword("NULLS FIRST");
1685 } else {
1686 self.write(" ");
1687 self.write_keyword("NULLS LAST");
1688 }
1689 }
1690 }
1691 }
1692 if let Some(frame) = &spec.frame {
1693 self.write(" ");
1694 self.gen_window_frame(frame);
1695 }
1696 }
1697
1698 fn gen_window_frame(&mut self, frame: &WindowFrame) {
1699 match frame.kind {
1700 WindowFrameKind::Rows => self.write_keyword("ROWS "),
1701 WindowFrameKind::Range => self.write_keyword("RANGE "),
1702 WindowFrameKind::Groups => self.write_keyword("GROUPS "),
1703 }
1704 if let Some(end) = &frame.end {
1705 self.write_keyword("BETWEEN ");
1706 self.gen_window_frame_bound(&frame.start);
1707 self.write(" ");
1708 self.write_keyword("AND ");
1709 self.gen_window_frame_bound(end);
1710 } else {
1711 self.gen_window_frame_bound(&frame.start);
1712 }
1713 }
1714
1715 fn gen_window_frame_bound(&mut self, bound: &WindowFrameBound) {
1716 match bound {
1717 WindowFrameBound::CurrentRow => self.write_keyword("CURRENT ROW"),
1718 WindowFrameBound::Preceding(None) => self.write_keyword("UNBOUNDED PRECEDING"),
1719 WindowFrameBound::Preceding(Some(n)) => {
1720 self.gen_expr(n);
1721 self.write(" ");
1722 self.write_keyword("PRECEDING");
1723 }
1724 WindowFrameBound::Following(None) => self.write_keyword("UNBOUNDED FOLLOWING"),
1725 WindowFrameBound::Following(Some(n)) => {
1726 self.gen_expr(n);
1727 self.write(" ");
1728 self.write_keyword("FOLLOWING");
1729 }
1730 }
1731 }
1732
1733 fn gen_datetime_field(&mut self, field: &DateTimeField) {
1734 let name = match field {
1735 DateTimeField::Year => "YEAR",
1736 DateTimeField::Quarter => "QUARTER",
1737 DateTimeField::Month => "MONTH",
1738 DateTimeField::Week => "WEEK",
1739 DateTimeField::Day => "DAY",
1740 DateTimeField::DayOfWeek => "DOW",
1741 DateTimeField::DayOfYear => "DOY",
1742 DateTimeField::Hour => "HOUR",
1743 DateTimeField::Minute => "MINUTE",
1744 DateTimeField::Second => "SECOND",
1745 DateTimeField::Millisecond => "MILLISECOND",
1746 DateTimeField::Microsecond => "MICROSECOND",
1747 DateTimeField::Nanosecond => "NANOSECOND",
1748 DateTimeField::Epoch => "EPOCH",
1749 DateTimeField::Timezone => "TIMEZONE",
1750 DateTimeField::TimezoneHour => "TIMEZONE_HOUR",
1751 DateTimeField::TimezoneMinute => "TIMEZONE_MINUTE",
1752 };
1753 self.write(name);
1754 }
1755
1756 fn gen_typed_function(&mut self, func: &TypedFunction) {
1758 let dialect = self.dialect;
1759 let is_tsql = matches!(dialect, Some(Dialect::Tsql) | Some(Dialect::Fabric));
1760 let is_mysql = matches!(
1761 dialect,
1762 Some(Dialect::Mysql)
1763 | Some(Dialect::SingleStore)
1764 | Some(Dialect::Doris)
1765 | Some(Dialect::StarRocks)
1766 );
1767 let is_bigquery = matches!(dialect, Some(Dialect::BigQuery));
1768 let is_snowflake = matches!(dialect, Some(Dialect::Snowflake));
1769 let is_oracle = matches!(dialect, Some(Dialect::Oracle));
1770 let is_hive_family = matches!(
1771 dialect,
1772 Some(Dialect::Hive) | Some(Dialect::Spark) | Some(Dialect::Databricks)
1773 );
1774
1775 match func {
1776 TypedFunction::DateAdd {
1778 expr,
1779 interval,
1780 unit,
1781 } => {
1782 if is_tsql || is_snowflake {
1783 self.write_keyword("DATEADD(");
1784 if let Some(u) = unit {
1785 self.gen_datetime_field(u);
1786 } else {
1787 self.write_keyword("DAY");
1788 }
1789 self.write(", ");
1790 self.gen_expr(interval);
1791 self.write(", ");
1792 self.gen_expr(expr);
1793 self.write(")");
1794 } else if is_bigquery {
1795 self.write_keyword("DATE_ADD(");
1796 self.gen_expr(expr);
1797 self.write(", ");
1798 self.write_keyword("INTERVAL ");
1799 self.gen_expr(interval);
1800 self.write(" ");
1801 if let Some(u) = unit {
1802 self.gen_datetime_field(u);
1803 } else {
1804 self.write_keyword("DAY");
1805 }
1806 self.write(")");
1807 } else {
1808 self.write_keyword("DATE_ADD(");
1809 self.gen_expr(expr);
1810 self.write(", ");
1811 self.gen_expr(interval);
1812 if let Some(u) = unit {
1813 self.write(", ");
1814 self.gen_datetime_field(u);
1815 }
1816 self.write(")");
1817 }
1818 }
1819 TypedFunction::DateDiff { start, end, unit } => {
1820 if is_tsql || is_snowflake {
1821 self.write_keyword("DATEDIFF(");
1822 if let Some(u) = unit {
1823 self.gen_datetime_field(u);
1824 } else {
1825 self.write_keyword("DAY");
1826 }
1827 self.write(", ");
1828 self.gen_expr(start);
1829 self.write(", ");
1830 self.gen_expr(end);
1831 self.write(")");
1832 } else if is_bigquery {
1833 self.write_keyword("DATE_DIFF(");
1834 self.gen_expr(end);
1835 self.write(", ");
1836 self.gen_expr(start);
1837 self.write(", ");
1838 if let Some(u) = unit {
1839 self.gen_datetime_field(u);
1840 } else {
1841 self.write_keyword("DAY");
1842 }
1843 self.write(")");
1844 } else {
1845 self.write_keyword("DATEDIFF(");
1846 self.gen_expr(start);
1847 self.write(", ");
1848 self.gen_expr(end);
1849 if let Some(u) = unit {
1850 self.write(", ");
1851 self.gen_datetime_field(u);
1852 }
1853 self.write(")");
1854 }
1855 }
1856 TypedFunction::DateTrunc { unit, expr } => {
1857 if is_tsql {
1858 self.write_keyword("DATETRUNC(");
1859 self.gen_datetime_field(unit);
1860 self.write(", ");
1861 self.gen_expr(expr);
1862 self.write(")");
1863 } else if is_oracle {
1864 self.write_keyword("TRUNC(");
1865 self.gen_expr(expr);
1866 self.write(", '");
1867 self.gen_datetime_field(unit);
1868 self.write("')");
1869 } else {
1870 self.write_keyword("DATE_TRUNC(");
1871 self.write("'");
1872 self.gen_datetime_field(unit);
1873 self.write("'");
1874 self.write(", ");
1875 self.gen_expr(expr);
1876 self.write(")");
1877 }
1878 }
1879 TypedFunction::DateSub {
1880 expr,
1881 interval,
1882 unit,
1883 } => {
1884 if is_tsql || is_snowflake {
1885 self.write_keyword("DATEADD(");
1886 if let Some(u) = unit {
1887 self.gen_datetime_field(u);
1888 } else {
1889 self.write_keyword("DAY");
1890 }
1891 self.write(", -(");
1892 self.gen_expr(interval);
1893 self.write("), ");
1894 self.gen_expr(expr);
1895 self.write(")");
1896 } else if is_bigquery {
1897 self.write_keyword("DATE_SUB(");
1898 self.gen_expr(expr);
1899 self.write(", ");
1900 self.write_keyword("INTERVAL ");
1901 self.gen_expr(interval);
1902 self.write(" ");
1903 if let Some(u) = unit {
1904 self.gen_datetime_field(u);
1905 } else {
1906 self.write_keyword("DAY");
1907 }
1908 self.write(")");
1909 } else {
1910 self.write_keyword("DATE_SUB(");
1911 self.gen_expr(expr);
1912 self.write(", ");
1913 self.gen_expr(interval);
1914 if let Some(u) = unit {
1915 self.write(", ");
1916 self.gen_datetime_field(u);
1917 }
1918 self.write(")");
1919 }
1920 }
1921 TypedFunction::CurrentDate => {
1922 if is_tsql {
1923 self.write_keyword("CAST(GETDATE() AS DATE)");
1924 } else if is_mysql || is_hive_family {
1925 self.write_keyword("CURRENT_DATE()");
1926 } else {
1927 self.write_keyword("CURRENT_DATE");
1928 }
1929 }
1930 TypedFunction::CurrentTimestamp => {
1931 if is_tsql {
1932 self.write_keyword("GETDATE()");
1933 } else if is_mysql
1934 || matches!(
1935 dialect,
1936 Some(Dialect::Postgres)
1937 | Some(Dialect::DuckDb)
1938 | Some(Dialect::Sqlite)
1939 | Some(Dialect::Redshift)
1940 )
1941 {
1942 self.write_keyword("NOW()");
1943 } else {
1944 self.write_keyword("CURRENT_TIMESTAMP()");
1945 }
1946 }
1947 TypedFunction::StrToTime { expr, format } => {
1948 if is_mysql {
1949 self.write_keyword("STR_TO_DATE(");
1950 } else if is_bigquery {
1951 self.write_keyword("PARSE_TIMESTAMP(");
1952 } else {
1953 self.write_keyword("TO_TIMESTAMP(");
1954 }
1955 self.gen_expr(expr);
1956 self.write(", ");
1957 self.gen_expr(format);
1958 self.write(")");
1959 }
1960 TypedFunction::TimeToStr { expr, format } => {
1961 if is_mysql {
1962 self.write_keyword("DATE_FORMAT(");
1963 } else if is_bigquery {
1964 self.write_keyword("FORMAT_TIMESTAMP(");
1965 } else if is_tsql {
1966 self.write_keyword("FORMAT(");
1967 } else {
1968 self.write_keyword("TO_CHAR(");
1969 }
1970 self.gen_expr(expr);
1971 self.write(", ");
1972 self.gen_expr(format);
1973 self.write(")");
1974 }
1975 TypedFunction::TsOrDsToDate { expr } => {
1976 if is_mysql {
1977 self.write_keyword("DATE(");
1978 self.gen_expr(expr);
1979 self.write(")");
1980 } else {
1981 self.write_keyword("CAST(");
1982 self.gen_expr(expr);
1983 self.write(" ");
1984 self.write_keyword("AS DATE)");
1985 }
1986 }
1987 TypedFunction::Year { expr } => {
1988 if is_tsql {
1989 self.write_keyword("YEAR(");
1990 self.gen_expr(expr);
1991 self.write(")");
1992 } else {
1993 self.write_keyword("EXTRACT(YEAR FROM ");
1994 self.gen_expr(expr);
1995 self.write(")");
1996 }
1997 }
1998 TypedFunction::Month { expr } => {
1999 if is_tsql {
2000 self.write_keyword("MONTH(");
2001 self.gen_expr(expr);
2002 self.write(")");
2003 } else {
2004 self.write_keyword("EXTRACT(MONTH FROM ");
2005 self.gen_expr(expr);
2006 self.write(")");
2007 }
2008 }
2009 TypedFunction::Day { expr } => {
2010 if is_tsql {
2011 self.write_keyword("DAY(");
2012 self.gen_expr(expr);
2013 self.write(")");
2014 } else {
2015 self.write_keyword("EXTRACT(DAY FROM ");
2016 self.gen_expr(expr);
2017 self.write(")");
2018 }
2019 }
2020
2021 TypedFunction::Trim {
2023 expr,
2024 trim_type,
2025 trim_chars,
2026 } => {
2027 self.write_keyword("TRIM(");
2028 match trim_type {
2029 TrimType::Leading => self.write_keyword("LEADING "),
2030 TrimType::Trailing => self.write_keyword("TRAILING "),
2031 TrimType::Both => {} }
2033 if let Some(chars) = trim_chars {
2034 self.gen_expr(chars);
2035 self.write(" ");
2036 self.write_keyword("FROM ");
2037 }
2038 self.gen_expr(expr);
2039 self.write(")");
2040 }
2041 TypedFunction::Substring {
2042 expr,
2043 start,
2044 length,
2045 } => {
2046 let name = if is_oracle
2047 || is_hive_family
2048 || is_mysql
2049 || matches!(
2050 dialect,
2051 Some(Dialect::Sqlite)
2052 | Some(Dialect::Doris)
2053 | Some(Dialect::SingleStore)
2054 | Some(Dialect::StarRocks)
2055 ) {
2056 "SUBSTR"
2057 } else {
2058 "SUBSTRING"
2059 };
2060 self.write_keyword(name);
2061 self.write("(");
2062 self.gen_expr(expr);
2063 self.write(", ");
2064 self.gen_expr(start);
2065 if let Some(l) = length {
2066 self.write(", ");
2067 self.gen_expr(l);
2068 }
2069 self.write(")");
2070 }
2071 TypedFunction::Upper { expr } => {
2072 self.write_keyword("UPPER(");
2073 self.gen_expr(expr);
2074 self.write(")");
2075 }
2076 TypedFunction::Lower { expr } => {
2077 self.write_keyword("LOWER(");
2078 self.gen_expr(expr);
2079 self.write(")");
2080 }
2081 TypedFunction::RegexpLike {
2082 expr,
2083 pattern,
2084 flags,
2085 } => {
2086 self.write_keyword("REGEXP_LIKE(");
2087 self.gen_expr(expr);
2088 self.write(", ");
2089 self.gen_expr(pattern);
2090 if let Some(f) = flags {
2091 self.write(", ");
2092 self.gen_expr(f);
2093 }
2094 self.write(")");
2095 }
2096 TypedFunction::RegexpExtract {
2097 expr,
2098 pattern,
2099 group_index,
2100 } => {
2101 if is_bigquery || is_hive_family {
2102 self.write_keyword("REGEXP_EXTRACT(");
2103 } else {
2104 self.write_keyword("REGEXP_SUBSTR(");
2105 }
2106 self.gen_expr(expr);
2107 self.write(", ");
2108 self.gen_expr(pattern);
2109 if let Some(g) = group_index {
2110 self.write(", ");
2111 self.gen_expr(g);
2112 }
2113 self.write(")");
2114 }
2115 TypedFunction::RegexpReplace {
2116 expr,
2117 pattern,
2118 replacement,
2119 flags,
2120 } => {
2121 self.write_keyword("REGEXP_REPLACE(");
2122 self.gen_expr(expr);
2123 self.write(", ");
2124 self.gen_expr(pattern);
2125 self.write(", ");
2126 self.gen_expr(replacement);
2127 if let Some(f) = flags {
2128 self.write(", ");
2129 self.gen_expr(f);
2130 }
2131 self.write(")");
2132 }
2133 TypedFunction::ConcatWs { separator, exprs } => {
2134 self.write_keyword("CONCAT_WS(");
2135 self.gen_expr(separator);
2136 for e in exprs {
2137 self.write(", ");
2138 self.gen_expr(e);
2139 }
2140 self.write(")");
2141 }
2142 TypedFunction::Split { expr, delimiter } => {
2143 if is_tsql {
2144 self.write_keyword("STRING_SPLIT(");
2145 } else {
2146 self.write_keyword("SPLIT(");
2147 }
2148 self.gen_expr(expr);
2149 self.write(", ");
2150 self.gen_expr(delimiter);
2151 self.write(")");
2152 }
2153 TypedFunction::Initcap { expr } => {
2154 self.write_keyword("INITCAP(");
2155 self.gen_expr(expr);
2156 self.write(")");
2157 }
2158 TypedFunction::Length { expr } => {
2159 let name = if is_tsql || is_bigquery || is_snowflake {
2160 "LEN"
2161 } else {
2162 "LENGTH"
2163 };
2164 self.write_keyword(name);
2165 self.write("(");
2166 self.gen_expr(expr);
2167 self.write(")");
2168 }
2169 TypedFunction::Replace { expr, from, to } => {
2170 self.write_keyword("REPLACE(");
2171 self.gen_expr(expr);
2172 self.write(", ");
2173 self.gen_expr(from);
2174 self.write(", ");
2175 self.gen_expr(to);
2176 self.write(")");
2177 }
2178 TypedFunction::Reverse { expr } => {
2179 self.write_keyword("REVERSE(");
2180 self.gen_expr(expr);
2181 self.write(")");
2182 }
2183 TypedFunction::Left { expr, n } => {
2184 self.write_keyword("LEFT(");
2185 self.gen_expr(expr);
2186 self.write(", ");
2187 self.gen_expr(n);
2188 self.write(")");
2189 }
2190 TypedFunction::Right { expr, n } => {
2191 self.write_keyword("RIGHT(");
2192 self.gen_expr(expr);
2193 self.write(", ");
2194 self.gen_expr(n);
2195 self.write(")");
2196 }
2197 TypedFunction::Lpad { expr, length, pad } => {
2198 self.write_keyword("LPAD(");
2199 self.gen_expr(expr);
2200 self.write(", ");
2201 self.gen_expr(length);
2202 if let Some(p) = pad {
2203 self.write(", ");
2204 self.gen_expr(p);
2205 }
2206 self.write(")");
2207 }
2208 TypedFunction::Rpad { expr, length, pad } => {
2209 self.write_keyword("RPAD(");
2210 self.gen_expr(expr);
2211 self.write(", ");
2212 self.gen_expr(length);
2213 if let Some(p) = pad {
2214 self.write(", ");
2215 self.gen_expr(p);
2216 }
2217 self.write(")");
2218 }
2219
2220 TypedFunction::Count { expr, distinct } => {
2222 self.write_keyword("COUNT(");
2223 if *distinct {
2224 self.write_keyword("DISTINCT ");
2225 }
2226 self.gen_expr(expr);
2227 self.write(")");
2228 }
2229 TypedFunction::Sum { expr, distinct } => {
2230 self.write_keyword("SUM(");
2231 if *distinct {
2232 self.write_keyword("DISTINCT ");
2233 }
2234 self.gen_expr(expr);
2235 self.write(")");
2236 }
2237 TypedFunction::Avg { expr, distinct } => {
2238 self.write_keyword("AVG(");
2239 if *distinct {
2240 self.write_keyword("DISTINCT ");
2241 }
2242 self.gen_expr(expr);
2243 self.write(")");
2244 }
2245 TypedFunction::Min { expr } => {
2246 self.write_keyword("MIN(");
2247 self.gen_expr(expr);
2248 self.write(")");
2249 }
2250 TypedFunction::Max { expr } => {
2251 self.write_keyword("MAX(");
2252 self.gen_expr(expr);
2253 self.write(")");
2254 }
2255 TypedFunction::ArrayAgg { expr, distinct } => {
2256 let name = if matches!(dialect, Some(Dialect::DuckDb)) {
2257 "LIST"
2258 } else if is_hive_family {
2259 "COLLECT_LIST"
2260 } else {
2261 "ARRAY_AGG"
2262 };
2263 self.write_keyword(name);
2264 self.write("(");
2265 if *distinct {
2266 self.write_keyword("DISTINCT ");
2267 }
2268 self.gen_expr(expr);
2269 self.write(")");
2270 }
2271 TypedFunction::ApproxDistinct { expr } => {
2272 let name = if is_hive_family
2273 || matches!(
2274 dialect,
2275 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2276 ) {
2277 "APPROX_DISTINCT"
2278 } else {
2279 "APPROX_COUNT_DISTINCT"
2280 };
2281 self.write_keyword(name);
2282 self.write("(");
2283 self.gen_expr(expr);
2284 self.write(")");
2285 }
2286 TypedFunction::Variance { expr } => {
2287 let name = if is_tsql || is_oracle {
2288 "VAR"
2289 } else {
2290 "VARIANCE"
2291 };
2292 self.write_keyword(name);
2293 self.write("(");
2294 self.gen_expr(expr);
2295 self.write(")");
2296 }
2297 TypedFunction::Stddev { expr } => {
2298 self.write_keyword("STDDEV(");
2299 self.gen_expr(expr);
2300 self.write(")");
2301 }
2302
2303 TypedFunction::ArrayConcat { arrays } => {
2305 let name = if matches!(
2306 dialect,
2307 Some(Dialect::Postgres) | Some(Dialect::Redshift) | Some(Dialect::DuckDb)
2308 ) {
2309 "ARRAY_CAT"
2310 } else {
2311 "ARRAY_CONCAT"
2312 };
2313 self.write_keyword(name);
2314 self.write("(");
2315 self.gen_expr_list(arrays);
2316 self.write(")");
2317 }
2318 TypedFunction::ArrayContains { array, element } => {
2319 self.write_keyword("ARRAY_CONTAINS(");
2320 self.gen_expr(array);
2321 self.write(", ");
2322 self.gen_expr(element);
2323 self.write(")");
2324 }
2325 TypedFunction::ArraySize { expr } => {
2326 let name = if matches!(dialect, Some(Dialect::Postgres) | Some(Dialect::Redshift)) {
2327 "ARRAY_LENGTH"
2328 } else if is_hive_family {
2329 "SIZE"
2330 } else {
2331 "ARRAY_SIZE"
2332 };
2333 self.write_keyword(name);
2334 self.write("(");
2335 self.gen_expr(expr);
2336 self.write(")");
2337 }
2338 TypedFunction::Explode { expr } => {
2339 self.write_keyword("EXPLODE(");
2340 self.gen_expr(expr);
2341 self.write(")");
2342 }
2343 TypedFunction::GenerateSeries { start, stop, step } => {
2344 self.write_keyword("GENERATE_SERIES(");
2345 self.gen_expr(start);
2346 self.write(", ");
2347 self.gen_expr(stop);
2348 if let Some(s) = step {
2349 self.write(", ");
2350 self.gen_expr(s);
2351 }
2352 self.write(")");
2353 }
2354 TypedFunction::Flatten { expr } => {
2355 self.write_keyword("FLATTEN(");
2356 self.gen_expr(expr);
2357 self.write(")");
2358 }
2359
2360 TypedFunction::JSONExtract { expr, path } => {
2362 if is_tsql {
2363 self.write_keyword("JSON_VALUE(");
2364 } else {
2365 self.write_keyword("JSON_EXTRACT(");
2366 }
2367 self.gen_expr(expr);
2368 self.write(", ");
2369 self.gen_expr(path);
2370 self.write(")");
2371 }
2372 TypedFunction::JSONExtractScalar { expr, path } => {
2373 if is_bigquery {
2374 self.write_keyword("JSON_EXTRACT_SCALAR(");
2375 } else if is_tsql {
2376 self.write_keyword("JSON_VALUE(");
2377 } else {
2378 self.write_keyword("JSON_EXTRACT_SCALAR(");
2379 }
2380 self.gen_expr(expr);
2381 self.write(", ");
2382 self.gen_expr(path);
2383 self.write(")");
2384 }
2385 TypedFunction::ParseJSON { expr } => {
2386 if is_snowflake {
2387 self.write_keyword("PARSE_JSON(");
2388 } else if is_bigquery {
2389 self.write_keyword("JSON_PARSE(");
2390 } else {
2391 self.write_keyword("PARSE_JSON(");
2392 }
2393 self.gen_expr(expr);
2394 self.write(")");
2395 }
2396 TypedFunction::JSONFormat { expr } => {
2397 if is_bigquery {
2398 self.write_keyword("TO_JSON_STRING(");
2399 } else {
2400 self.write_keyword("JSON_FORMAT(");
2401 }
2402 self.gen_expr(expr);
2403 self.write(")");
2404 }
2405
2406 TypedFunction::RowNumber => self.write_keyword("ROW_NUMBER()"),
2408 TypedFunction::Rank => self.write_keyword("RANK()"),
2409 TypedFunction::DenseRank => self.write_keyword("DENSE_RANK()"),
2410 TypedFunction::NTile { n } => {
2411 self.write_keyword("NTILE(");
2412 self.gen_expr(n);
2413 self.write(")");
2414 }
2415 TypedFunction::Lead {
2416 expr,
2417 offset,
2418 default,
2419 } => {
2420 self.write_keyword("LEAD(");
2421 self.gen_expr(expr);
2422 if let Some(o) = offset {
2423 self.write(", ");
2424 self.gen_expr(o);
2425 }
2426 if let Some(d) = default {
2427 self.write(", ");
2428 self.gen_expr(d);
2429 }
2430 self.write(")");
2431 }
2432 TypedFunction::Lag {
2433 expr,
2434 offset,
2435 default,
2436 } => {
2437 self.write_keyword("LAG(");
2438 self.gen_expr(expr);
2439 if let Some(o) = offset {
2440 self.write(", ");
2441 self.gen_expr(o);
2442 }
2443 if let Some(d) = default {
2444 self.write(", ");
2445 self.gen_expr(d);
2446 }
2447 self.write(")");
2448 }
2449 TypedFunction::FirstValue { expr } => {
2450 self.write_keyword("FIRST_VALUE(");
2451 self.gen_expr(expr);
2452 self.write(")");
2453 }
2454 TypedFunction::LastValue { expr } => {
2455 self.write_keyword("LAST_VALUE(");
2456 self.gen_expr(expr);
2457 self.write(")");
2458 }
2459
2460 TypedFunction::Abs { expr } => {
2462 self.write_keyword("ABS(");
2463 self.gen_expr(expr);
2464 self.write(")");
2465 }
2466 TypedFunction::Ceil { expr } => {
2467 let name = if is_tsql { "CEILING" } else { "CEIL" };
2468 self.write_keyword(name);
2469 self.write("(");
2470 self.gen_expr(expr);
2471 self.write(")");
2472 }
2473 TypedFunction::Floor { expr } => {
2474 self.write_keyword("FLOOR(");
2475 self.gen_expr(expr);
2476 self.write(")");
2477 }
2478 TypedFunction::Round { expr, decimals } => {
2479 self.write_keyword("ROUND(");
2480 self.gen_expr(expr);
2481 if let Some(d) = decimals {
2482 self.write(", ");
2483 self.gen_expr(d);
2484 }
2485 self.write(")");
2486 }
2487 TypedFunction::Log { expr, base } => {
2488 if let Some(b) = base {
2489 self.write_keyword("LOG(");
2490 if matches!(dialect, Some(Dialect::Postgres) | Some(Dialect::DuckDb)) {
2491 self.gen_expr(b);
2493 self.write(", ");
2494 self.gen_expr(expr);
2495 } else {
2496 self.gen_expr(expr);
2498 self.write(", ");
2499 self.gen_expr(b);
2500 }
2501 self.write(")");
2502 } else {
2503 self.write_keyword("LOG(");
2505 self.gen_expr(expr);
2506 self.write(")");
2507 }
2508 }
2509 TypedFunction::Ln { expr } => {
2510 self.write_keyword("LN(");
2511 self.gen_expr(expr);
2512 self.write(")");
2513 }
2514 TypedFunction::Pow { base, exponent } => {
2515 let name = if is_tsql || is_oracle { "POWER" } else { "POW" };
2516 self.write_keyword(name);
2517 self.write("(");
2518 self.gen_expr(base);
2519 self.write(", ");
2520 self.gen_expr(exponent);
2521 self.write(")");
2522 }
2523 TypedFunction::Sqrt { expr } => {
2524 self.write_keyword("SQRT(");
2525 self.gen_expr(expr);
2526 self.write(")");
2527 }
2528 TypedFunction::Greatest { exprs } => {
2529 self.write_keyword("GREATEST(");
2530 self.gen_expr_list(exprs);
2531 self.write(")");
2532 }
2533 TypedFunction::Least { exprs } => {
2534 self.write_keyword("LEAST(");
2535 self.gen_expr_list(exprs);
2536 self.write(")");
2537 }
2538 TypedFunction::Mod { left, right } => {
2539 self.write_keyword("MOD(");
2540 self.gen_expr(left);
2541 self.write(", ");
2542 self.gen_expr(right);
2543 self.write(")");
2544 }
2545
2546 TypedFunction::Hex { expr } => {
2548 let name = if matches!(
2549 dialect,
2550 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2551 ) {
2552 "TO_HEX"
2553 } else {
2554 "HEX"
2555 };
2556 self.write_keyword(name);
2557 self.write("(");
2558 self.gen_expr(expr);
2559 self.write(")");
2560 }
2561 TypedFunction::Unhex { expr } => {
2562 let name = if matches!(
2563 dialect,
2564 Some(Dialect::Presto) | Some(Dialect::Trino) | Some(Dialect::Athena)
2565 ) {
2566 "FROM_HEX"
2567 } else {
2568 "UNHEX"
2569 };
2570 self.write_keyword(name);
2571 self.write("(");
2572 self.gen_expr(expr);
2573 self.write(")");
2574 }
2575 TypedFunction::Md5 { expr } => {
2576 self.write_keyword("MD5(");
2577 self.gen_expr(expr);
2578 self.write(")");
2579 }
2580 TypedFunction::Sha { expr } => {
2581 let name = if is_mysql { "SHA1" } else { "SHA" };
2582 self.write_keyword(name);
2583 self.write("(");
2584 self.gen_expr(expr);
2585 self.write(")");
2586 }
2587 TypedFunction::Sha2 { expr, bit_length } => {
2588 self.write_keyword("SHA2(");
2589 self.gen_expr(expr);
2590 self.write(", ");
2591 self.gen_expr(bit_length);
2592 self.write(")");
2593 }
2594 }
2595 }
2596}
2597
2598impl Default for Generator {
2599 fn default() -> Self {
2600 Self::new()
2601 }
2602}
2603
2604#[cfg(test)]
2605mod tests {
2606 use super::*;
2607 use crate::parser::Parser;
2608
2609 fn roundtrip(sql: &str) -> String {
2610 let stmt = Parser::new(sql).unwrap().parse_statement().unwrap();
2611 let mut g = Generator::new();
2612 g.generate(&stmt)
2613 }
2614
2615 #[test]
2616 fn test_select_roundtrip() {
2617 assert_eq!(roundtrip("SELECT a, b FROM t"), "SELECT a, b FROM t");
2618 }
2619
2620 #[test]
2621 fn test_select_where() {
2622 assert_eq!(
2623 roundtrip("SELECT x FROM t WHERE x > 10"),
2624 "SELECT x FROM t WHERE x > 10"
2625 );
2626 }
2627
2628 #[test]
2629 fn test_select_wildcard() {
2630 assert_eq!(roundtrip("SELECT * FROM users"), "SELECT * FROM users");
2631 }
2632
2633 #[test]
2634 fn test_insert_values() {
2635 assert_eq!(
2636 roundtrip("INSERT INTO t (a, b) VALUES (1, 'hello')"),
2637 "INSERT INTO t (a, b) VALUES (1, 'hello')"
2638 );
2639 }
2640
2641 #[test]
2642 fn test_delete() {
2643 assert_eq!(
2644 roundtrip("DELETE FROM users WHERE id = 1"),
2645 "DELETE FROM users WHERE id = 1"
2646 );
2647 }
2648
2649 #[test]
2650 fn test_join() {
2651 assert_eq!(
2652 roundtrip("SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"),
2653 "SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"
2654 );
2655 }
2656
2657 #[test]
2658 fn test_create_table() {
2659 assert_eq!(
2660 roundtrip("CREATE TABLE users (id INT NOT NULL, name VARCHAR(255), email TEXT)"),
2661 "CREATE TABLE users (id INT NOT NULL, name VARCHAR(255), email TEXT)"
2662 );
2663 }
2664
2665 #[test]
2666 fn test_cte_roundtrip() {
2667 let sql = "WITH cte AS (SELECT 1 AS x) SELECT x FROM cte";
2668 assert_eq!(
2669 roundtrip(sql),
2670 "WITH cte AS (SELECT 1 AS x) SELECT x FROM cte"
2671 );
2672 }
2673
2674 #[test]
2675 fn test_union_roundtrip() {
2676 let sql = "SELECT 1 UNION ALL SELECT 2";
2677 assert_eq!(roundtrip(sql), "SELECT 1 UNION ALL SELECT 2");
2678 }
2679
2680 #[test]
2681 fn test_cast_roundtrip() {
2682 assert_eq!(
2683 roundtrip("SELECT CAST(x AS INT) FROM t"),
2684 "SELECT CAST(x AS INT) FROM t"
2685 );
2686 }
2687
2688 #[test]
2689 fn test_exists_roundtrip() {
2690 assert_eq!(
2691 roundtrip("SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2)"),
2692 "SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2)"
2693 );
2694 }
2695
2696 #[test]
2697 fn test_extract_roundtrip() {
2698 assert_eq!(
2699 roundtrip("SELECT EXTRACT(YEAR FROM created_at) FROM t"),
2700 "SELECT EXTRACT(YEAR FROM created_at) FROM t"
2701 );
2702 }
2703
2704 #[test]
2705 fn test_window_function_roundtrip() {
2706 assert_eq!(
2707 roundtrip("SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) FROM emp"),
2708 "SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) FROM emp"
2709 );
2710 }
2711
2712 #[test]
2713 fn test_subquery_from_roundtrip() {
2714 assert_eq!(
2715 roundtrip("SELECT * FROM (SELECT 1 AS x) AS sub"),
2716 "SELECT * FROM (SELECT 1 AS x) AS sub"
2717 );
2718 }
2719
2720 #[test]
2721 fn test_in_subquery_roundtrip() {
2722 assert_eq!(
2723 roundtrip("SELECT * FROM t WHERE id IN (SELECT id FROM t2)"),
2724 "SELECT * FROM t WHERE id IN (SELECT id FROM t2)"
2725 );
2726 }
2727
2728 fn pretty_print(sql: &str) -> String {
2733 let stmt = Parser::new(sql).unwrap().parse_statement().unwrap();
2734 let mut g = Generator::pretty();
2735 g.generate(&stmt)
2736 }
2737
2738 #[test]
2739 fn test_pretty_simple_select() {
2740 assert_eq!(
2741 pretty_print("SELECT a, b, c FROM t"),
2742 "SELECT\n a,\n b,\n c\nFROM\n t"
2743 );
2744 }
2745
2746 #[test]
2747 fn test_pretty_select_where() {
2748 assert_eq!(
2749 pretty_print("SELECT a FROM t WHERE a > 1"),
2750 "SELECT\n a\nFROM\n t\nWHERE\n a > 1"
2751 );
2752 }
2753
2754 #[test]
2755 fn test_pretty_select_group_by_having() {
2756 assert_eq!(
2757 pretty_print("SELECT a, COUNT(*) FROM t GROUP BY a HAVING COUNT(*) > 1"),
2758 "SELECT\n a,\n COUNT(*)\nFROM\n t\nGROUP BY\n a\nHAVING\n COUNT(*) > 1"
2759 );
2760 }
2761
2762 #[test]
2763 fn test_pretty_select_order_by_limit() {
2764 assert_eq!(
2765 pretty_print("SELECT a FROM t ORDER BY a DESC LIMIT 10"),
2766 "SELECT\n a\nFROM\n t\nORDER BY\n a DESC\nLIMIT 10"
2767 );
2768 }
2769
2770 #[test]
2771 fn test_pretty_join() {
2772 assert_eq!(
2773 pretty_print("SELECT a.id, b.name FROM a INNER JOIN b ON a.id = b.a_id"),
2774 "SELECT\n a.id,\n b.name\nFROM\n a\nINNER JOIN\n b\n ON a.id = b.a_id"
2775 );
2776 }
2777
2778 #[test]
2779 fn test_pretty_cte() {
2780 assert_eq!(
2781 pretty_print("WITH cte AS (SELECT 1 AS x) SELECT x FROM cte"),
2782 "WITH cte AS (\n SELECT\n 1 AS x\n)\nSELECT\n x\nFROM\n cte"
2783 );
2784 }
2785
2786 #[test]
2787 fn test_pretty_union() {
2788 assert_eq!(
2789 pretty_print("SELECT 1 UNION ALL SELECT 2"),
2790 "SELECT\n 1\nUNION ALL\nSELECT\n 2"
2791 );
2792 }
2793
2794 #[test]
2795 fn test_pretty_insert() {
2796 assert_eq!(
2797 pretty_print("INSERT INTO t (a, b) VALUES (1, 'hello'), (2, 'world')"),
2798 "INSERT INTO t (a, b)\nVALUES\n (1, 'hello'),\n (2, 'world')"
2799 );
2800 }
2801
2802 #[test]
2803 fn test_pretty_update() {
2804 assert_eq!(
2805 pretty_print("UPDATE t SET a = 1, b = 2 WHERE c = 3"),
2806 "UPDATE t\nSET\n a = 1,\n b = 2\nWHERE\n c = 3"
2807 );
2808 }
2809
2810 #[test]
2811 fn test_pretty_delete() {
2812 assert_eq!(
2813 pretty_print("DELETE FROM t WHERE id = 1"),
2814 "DELETE FROM t\nWHERE\n id = 1"
2815 );
2816 }
2817
2818 #[test]
2819 fn test_pretty_create_table() {
2820 assert_eq!(
2821 pretty_print("CREATE TABLE t (id INT NOT NULL, name VARCHAR(255), email TEXT)"),
2822 "CREATE TABLE t (\n id INT NOT NULL,\n name VARCHAR(255),\n email TEXT\n)"
2823 );
2824 }
2825
2826 #[test]
2827 fn test_pretty_complex_query() {
2828 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";
2829 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";
2830 assert_eq!(pretty_print(sql), expected);
2831 }
2832
2833 #[test]
2834 fn test_pretty_select_distinct() {
2835 assert_eq!(
2836 pretty_print("SELECT DISTINCT a, b FROM t"),
2837 "SELECT DISTINCT\n a,\n b\nFROM\n t"
2838 );
2839 }
2840}