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