main
1use sqlparser::ast::{*, Insert, Delete, CreateTable, FromTable};
2
3pub fn format_statement(statement: &Statement, indent_level: usize) -> String {
4 match statement {
5 Statement::Query(query) => format_query(query, indent_level),
6 Statement::Insert(insert) => format_insert(insert, indent_level),
7 Statement::Update {
8 table,
9 assignments,
10 selection,
11 ..
12 } => format_update(table, assignments, selection, indent_level),
13 Statement::Delete(delete) => format_delete(delete, indent_level),
14 Statement::CreateTable(create_table) => format_create_table(create_table, indent_level),
15 _ => statement.to_string(),
16 }
17}
18
19fn format_query(query: &Query, indent_level: usize) -> String {
20 let mut result = String::new();
21
22 if let Some(with) = &query.with {
23 result.push_str(&format_with(with, indent_level));
24 result.push('\n');
25 }
26
27 result.push_str(&format_set_expr(&query.body, indent_level));
28
29 if let Some(order_by) = &query.order_by {
30 match &order_by.kind {
31 OrderByKind::Expressions(exprs) if !exprs.is_empty() => {
32 result.push('\n');
33 result.push_str(&format!("{}ORDER BY ", " ".repeat(indent_level)));
34 let order_items: Vec<String> = exprs
35 .iter()
36 .map(|e| format_order_by_expr(e, indent_level))
37 .collect();
38 result.push_str(&order_items.join(", "));
39 }
40 _ => {}
41 }
42 }
43
44 if let Some(limit_clause) = &query.limit_clause {
45 match limit_clause {
46 LimitClause::LimitOffset { limit, .. } => {
47 if let Some(limit) = limit {
48 result.push('\n');
49 result.push_str(&format!(
50 "{}LIMIT {}",
51 " ".repeat(indent_level),
52 format_expr(limit, indent_level)
53 ));
54 }
55 }
56 LimitClause::OffsetCommaLimit { limit, .. } => {
57 result.push('\n');
58 result.push_str(&format!(
59 "{}LIMIT {}",
60 " ".repeat(indent_level),
61 format_expr(limit, indent_level)
62 ));
63 }
64 }
65 }
66
67 result
68}
69
70fn format_with(with: &With, indent_level: usize) -> String {
71 let mut result = format!("{}WITH", " ".repeat(indent_level));
72 if with.recursive {
73 result.push_str(" RECURSIVE");
74 }
75
76 for (i, cte) in with.cte_tables.iter().enumerate() {
77 if i == 0 {
78 result.push('\n');
79 result.push_str(&format!(" {}", format_cte(cte, indent_level + 2)));
80 } else {
81 result.push_str(",\n");
82 result.push_str(&format!(" {}", format_cte(cte, indent_level + 2)));
83 }
84 }
85
86 result
87}
88
89fn format_cte(cte: &Cte, indent_level: usize) -> String {
90 let mut result = cte.alias.name.value.clone();
91
92 if !cte.alias.columns.is_empty() {
93 result.push('(');
94 let columns: Vec<String> = cte
95 .alias
96 .columns
97 .iter()
98 .map(|col| col.name.value.clone())
99 .collect();
100 result.push_str(&columns.join(", "));
101 result.push(')');
102 }
103
104 result.push_str(" AS (\n");
105 result.push_str(&format_query(&cte.query, indent_level + 2));
106 result.push('\n');
107 result.push_str(&format!("{})", " ".repeat(indent_level)));
108
109 result
110}
111
112fn format_set_expr(set_expr: &SetExpr, indent_level: usize) -> String {
113 match set_expr {
114 SetExpr::Select(select) => format_select(select, indent_level),
115 SetExpr::Query(query) => {
116 let mut result = String::new();
117 result.push_str(&" ".repeat(indent_level));
118 result.push('(');
119 result.push('\n');
120 result.push_str(&format_query(query, indent_level + 2));
121 result.push('\n');
122 result.push_str(&" ".repeat(indent_level));
123 result.push(')');
124 result
125 }
126 SetExpr::SetOperation {
127 op,
128 set_quantifier: _,
129 left,
130 right,
131 } => {
132 let mut result = format_set_expr(left, indent_level);
133 result.push('\n');
134 result.push_str(&format!("{}{}", " ".repeat(indent_level), op));
135 result.push('\n');
136 result.push_str(&format_set_expr(right, indent_level));
137 result
138 }
139 _ => set_expr.to_string(),
140 }
141}
142
143fn format_select(select: &Select, indent_level: usize) -> String {
144 let mut result = String::new();
145 let base_indent = " ".repeat(indent_level);
146 let item_indent = " ".repeat(indent_level + 2);
147
148 result.push_str(&format!("{}SELECT", base_indent));
149
150 if select.distinct.is_some() {
151 result.push_str(" DISTINCT");
152 }
153
154 if select.projection.len() == 1 && is_simple_projection(&select.projection[0]) {
155 result.push(' ');
156 result.push_str(&format_select_item(&select.projection[0], indent_level + 2));
157 } else {
158 for (i, item) in select.projection.iter().enumerate() {
159 if i == 0 {
160 result.push('\n');
161 } else {
162 result.push_str(",\n");
163 }
164 result.push_str(&format!(
165 "{}{}",
166 item_indent,
167 format_select_item(item, indent_level + 2)
168 ));
169 }
170 }
171
172 if !select.from.is_empty() {
173 result.push('\n');
174 result.push_str(&format!("{}FROM", base_indent));
175
176 for (i, table) in select.from.iter().enumerate() {
177 if i == 0 {
178 result.push('\n');
179 result.push_str(&format!(
180 "{}{}",
181 item_indent,
182 format_table_with_joins(table, indent_level + 2)
183 ));
184 } else {
185 result.push_str(",\n");
186 result.push_str(&format!(
187 "{}{}",
188 item_indent,
189 format_table_with_joins(table, indent_level + 2)
190 ));
191 }
192 }
193 }
194
195 if let Some(selection) = &select.selection {
196 result.push('\n');
197 result.push_str(&format!(
198 "{}WHERE {}",
199 base_indent,
200 format_where_expr(selection, indent_level + 2)
201 ));
202 }
203
204 match &select.group_by {
205 GroupByExpr::Expressions(exprs, _) if !exprs.is_empty() => {
206 result.push('\n');
207 result.push_str(&format!("{}GROUP BY ", base_indent));
208 let group_items: Vec<String> = exprs
209 .iter()
210 .map(|e| format_expr(e, indent_level))
211 .collect();
212 result.push_str(&group_items.join(", "));
213 }
214 _ => {}
215 }
216
217 if let Some(having) = &select.having {
218 result.push('\n');
219 result.push_str(&format!(
220 "{}HAVING {}",
221 base_indent,
222 format_expr(having, indent_level)
223 ));
224 }
225
226 result
227}
228
229fn format_select_item(item: &SelectItem, indent_level: usize) -> String {
230 match item {
231 SelectItem::UnnamedExpr(expr) => format_expr(expr, indent_level),
232 SelectItem::ExprWithAlias { expr, alias } => {
233 format!("{} AS {}", format_expr(expr, indent_level), alias.value)
234 }
235 SelectItem::QualifiedWildcard(kind, _) => match kind {
236 SelectItemQualifiedWildcardKind::ObjectName(object_name) => {
237 format!("{}.*", object_name)
238 }
239 SelectItemQualifiedWildcardKind::Expr(expr) => {
240 format!("{}.*", format_expr(expr, indent_level))
241 }
242 },
243 SelectItem::Wildcard(_) => "*".to_string(),
244 }
245}
246
247fn format_table_with_joins(table: &TableWithJoins, indent_level: usize) -> String {
248 let mut result = format_table_factor(&table.relation, indent_level);
249
250 for join in &table.joins {
251 result.push('\n');
252 result.push_str(&format!(
253 "{}{}",
254 " ".repeat(indent_level),
255 format_join(join, indent_level)
256 ));
257 }
258
259 result
260}
261
262fn format_table_factor(table: &TableFactor, indent_level: usize) -> String {
263 match table {
264 TableFactor::Table { name, alias, .. } => {
265 let mut result = name.to_string();
266 if let Some(alias) = alias {
267 result.push_str(&format!(" AS {}", alias.name.value));
268 }
269 result
270 }
271 TableFactor::Derived {
272 subquery, alias, ..
273 } => {
274 let inner_indent = indent_level + 2;
275 let mut result = String::new();
276 result.push('(');
277 result.push('\n');
278 result.push_str(&format_query(subquery, inner_indent));
279 result.push('\n');
280 result.push_str(&" ".repeat(indent_level));
281 result.push(')');
282 if let Some(alias) = alias {
283 result.push_str(&format!(" AS {}", alias.name.value));
284 }
285 result
286 }
287 TableFactor::UNNEST {
288 array_exprs,
289 alias,
290 with_offset,
291 with_offset_alias,
292 ..
293 } => {
294 let has_complex = array_exprs.iter().any(contains_complex_expr);
295 let mut result = String::from("UNNEST(");
296
297 if has_complex {
298 let inner_indent = indent_level + 2;
299 result.push('\n');
300 for (i, expr) in array_exprs.iter().enumerate() {
301 if i > 0 {
302 result.push_str(",\n");
303 }
304 result.push_str(&" ".repeat(inner_indent));
305 result.push_str(&format_expr(expr, inner_indent));
306 }
307 result.push('\n');
308 result.push_str(&" ".repeat(indent_level));
309 } else {
310 let exprs: Vec<String> = array_exprs
311 .iter()
312 .map(|e| format_expr(e, indent_level))
313 .collect();
314 result.push_str(&exprs.join(", "));
315 }
316 result.push(')');
317
318 if let Some(alias) = alias {
319 result.push_str(&format!(" AS {}", alias.name.value));
320 if !alias.columns.is_empty() {
321 let cols: Vec<String> =
322 alias.columns.iter().map(|c| c.name.value.clone()).collect();
323 result.push_str(&format!(" ({})", cols.join(", ")));
324 }
325 }
326
327 if *with_offset {
328 result.push_str(" WITH OFFSET");
329 if let Some(offset_alias) = with_offset_alias {
330 result.push_str(&format!(" AS {}", offset_alias.value));
331 }
332 }
333
334 result
335 }
336 TableFactor::Function {
337 name, args, alias, ..
338 } => {
339 let has_complex = args.iter().any(|arg| match arg {
340 FunctionArg::Unnamed(FunctionArgExpr::Expr(e)) => contains_complex_expr(e),
341 FunctionArg::Named { arg: FunctionArgExpr::Expr(e), .. } => contains_complex_expr(e),
342 _ => false,
343 });
344
345 let mut result = name.to_string().to_uppercase();
346 result.push('(');
347
348 if has_complex {
349 let inner_indent = indent_level + 2;
350 result.push('\n');
351 for (i, arg) in args.iter().enumerate() {
352 if i > 0 {
353 result.push_str(",\n");
354 }
355 result.push_str(&" ".repeat(inner_indent));
356 result.push_str(&format_table_function_arg(arg, inner_indent));
357 }
358 result.push('\n');
359 result.push_str(&" ".repeat(indent_level));
360 } else {
361 let formatted_args: Vec<String> = args
362 .iter()
363 .map(|a| format_table_function_arg(a, indent_level))
364 .collect();
365 result.push_str(&formatted_args.join(", "));
366 }
367 result.push(')');
368
369 if let Some(alias) = alias {
370 result.push_str(&format!(" AS {}", alias.name.value));
371 }
372
373 result
374 }
375 _ => table.to_string(),
376 }
377}
378
379fn format_table_function_arg(arg: &FunctionArg, indent_level: usize) -> String {
380 match arg {
381 FunctionArg::Named { name, arg, .. } => {
382 format!("{} => {}", name.value, format_function_arg_expr(arg, indent_level))
383 }
384 FunctionArg::Unnamed(arg) => format_function_arg_expr(arg, indent_level),
385 FunctionArg::ExprNamed { name, arg, .. } => {
386 format!("{} => {}", format_expr(name, indent_level), format_function_arg_expr(arg, indent_level))
387 }
388 }
389}
390
391fn format_join(join: &Join, indent_level: usize) -> String {
392 let mut result = String::new();
393
394 match &join.join_operator {
395 JoinOperator::Join(constraint) => {
396 result.push_str("JOIN ");
397 result.push_str(&format_table_factor(&join.relation, indent_level));
398 if let JoinConstraint::On(expr) = constraint {
399 result.push_str(&format!(" ON {}", format_expr(expr, indent_level)));
400 }
401 }
402 JoinOperator::Inner(constraint) => {
403 result.push_str("INNER JOIN ");
404 result.push_str(&format_table_factor(&join.relation, indent_level));
405 if let JoinConstraint::On(expr) = constraint {
406 result.push_str(&format!(" ON {}", format_expr(expr, indent_level)));
407 }
408 }
409 JoinOperator::Left(constraint) => {
410 result.push_str("LEFT OUTER JOIN ");
411 result.push_str(&format_table_factor(&join.relation, indent_level));
412 if let JoinConstraint::On(expr) = constraint {
413 result.push_str(&format!(" ON {}", format_expr(expr, indent_level)));
414 }
415 }
416 JoinOperator::LeftOuter(constraint) => {
417 result.push_str("LEFT OUTER JOIN ");
418 result.push_str(&format_table_factor(&join.relation, indent_level));
419 if let JoinConstraint::On(expr) = constraint {
420 result.push_str(&format!(" ON {}", format_expr(expr, indent_level)));
421 }
422 }
423 JoinOperator::Right(constraint) => {
424 result.push_str("RIGHT OUTER JOIN ");
425 result.push_str(&format_table_factor(&join.relation, indent_level));
426 if let JoinConstraint::On(expr) = constraint {
427 result.push_str(&format!(" ON {}", format_expr(expr, indent_level)));
428 }
429 }
430 JoinOperator::RightOuter(constraint) => {
431 result.push_str("RIGHT OUTER JOIN ");
432 result.push_str(&format_table_factor(&join.relation, indent_level));
433 if let JoinConstraint::On(expr) = constraint {
434 result.push_str(&format!(" ON {}", format_expr(expr, indent_level)));
435 }
436 }
437 JoinOperator::FullOuter(constraint) => {
438 result.push_str("FULL OUTER JOIN ");
439 result.push_str(&format_table_factor(&join.relation, indent_level));
440 if let JoinConstraint::On(expr) = constraint {
441 result.push_str(&format!(" ON {}", format_expr(expr, indent_level)));
442 }
443 }
444 _ => {
445 result.push_str("JOIN ");
446 result.push_str(&format_table_factor(&join.relation, indent_level));
447 }
448 }
449
450 result
451}
452
453fn format_expr(expr: &Expr, indent_level: usize) -> String {
454 match expr {
455 Expr::Identifier(ident) => ident.value.clone(),
456 Expr::CompoundIdentifier(idents) => idents
457 .iter()
458 .map(|i| i.value.clone())
459 .collect::<Vec<_>>()
460 .join("."),
461 Expr::Value(value) => format_value(&value.value),
462 Expr::BinaryOp { left, op, right } => {
463 format!(
464 "{} {} {}",
465 format_expr(left, indent_level),
466 op,
467 format_expr(right, indent_level)
468 )
469 }
470 Expr::UnaryOp { op, expr } => {
471 format!("{} {}", op, format_expr(expr, indent_level))
472 }
473 Expr::Cast {
474 expr, data_type, ..
475 } => {
476 format!("CAST({} AS {})", format_expr(expr, indent_level), data_type)
477 }
478 Expr::Case {
479 operand,
480 conditions,
481 else_result,
482 ..
483 } => format_case_expr(operand, conditions, else_result, indent_level),
484 Expr::Function(function) => format_function(function, indent_level),
485 Expr::Subquery(query) => {
486 let inner_indent = indent_level + 2;
487 let mut result = String::new();
488 result.push('(');
489 result.push('\n');
490 result.push_str(&format_query(query, inner_indent));
491 result.push('\n');
492 result.push_str(&" ".repeat(indent_level));
493 result.push(')');
494 result
495 }
496 Expr::InSubquery {
497 expr,
498 subquery,
499 negated,
500 } => {
501 let inner_indent = indent_level + 2;
502 let mut result = format_expr(expr, indent_level);
503 if *negated {
504 result.push_str(" NOT");
505 }
506 result.push_str(" IN (");
507 result.push('\n');
508 result.push_str(&format_query(subquery, inner_indent));
509 result.push('\n');
510 result.push_str(&" ".repeat(indent_level));
511 result.push(')');
512 result
513 }
514 Expr::Between {
515 expr,
516 negated,
517 low,
518 high,
519 } => {
520 let mut result = format_expr(expr, indent_level);
521 if *negated {
522 result.push_str(" NOT");
523 }
524 result.push_str(&format!(
525 " BETWEEN {} AND {}",
526 format_expr(low, indent_level),
527 format_expr(high, indent_level)
528 ));
529 result
530 }
531 Expr::Like {
532 expr,
533 negated,
534 pattern,
535 ..
536 } => {
537 let mut result = format_expr(expr, indent_level);
538 if *negated {
539 result.push_str(" NOT");
540 }
541 result.push_str(&format!(" LIKE {}", format_expr(pattern, indent_level)));
542 result
543 }
544 Expr::IsNull(expr) => format!("{} IS NULL", format_expr(expr, indent_level)),
545 Expr::IsNotNull(expr) => format!("{} IS NOT NULL", format_expr(expr, indent_level)),
546 Expr::Nested(expr) => format!("({})", format_expr(expr, indent_level)),
547 _ => expr.to_string(),
548 }
549}
550
551fn format_case_expr(
552 operand: &Option<Box<Expr>>,
553 conditions: &Vec<CaseWhen>,
554 else_result: &Option<Box<Expr>>,
555 indent_level: usize,
556) -> String {
557 let mut result = String::new();
558 let inner_indent = " ".repeat(indent_level + 2);
559
560 result.push_str("CASE");
561 if let Some(operand) = operand {
562 result.push_str(&format!(" {}", format_expr(operand, indent_level)));
563 }
564
565 for condition in conditions {
566 result.push_str(&format!(
567 "\n{}WHEN {} THEN {}",
568 inner_indent,
569 format_expr(&condition.condition, indent_level + 2),
570 format_expr(&condition.result, indent_level + 2)
571 ));
572 }
573
574 if let Some(else_result) = else_result {
575 result.push_str(&format!(
576 "\n{}ELSE {}",
577 inner_indent,
578 format_expr(else_result, indent_level + 2)
579 ));
580 }
581
582 result.push_str(&format!("\n{}END", " ".repeat(indent_level)));
583 result
584}
585
586fn format_function(function: &Function, indent_level: usize) -> String {
587 let mut result = function.name.to_string().to_uppercase();
588 result.push('(');
589
590 let has_distinct = if let FunctionArguments::List(list) = &function.args {
591 matches!(list.duplicate_treatment, Some(DuplicateTreatment::Distinct))
592 } else {
593 false
594 };
595
596 if has_distinct {
597 result.push_str("DISTINCT ");
598 }
599
600 match &function.args {
601 FunctionArguments::None => {}
602 FunctionArguments::Subquery(subquery) => {
603 let inner_indent = indent_level + 2;
604 result.push('\n');
605 result.push_str(&format_query(subquery, inner_indent));
606 result.push('\n');
607 result.push_str(&" ".repeat(indent_level));
608 }
609 FunctionArguments::List(list) => {
610 let has_complex = list.args.iter().any(|arg| match arg {
611 FunctionArg::Unnamed(FunctionArgExpr::Expr(e)) => contains_complex_expr(e),
612 FunctionArg::Named { arg: FunctionArgExpr::Expr(e), .. } => {
613 contains_complex_expr(e)
614 }
615 FunctionArg::ExprNamed { arg: FunctionArgExpr::Expr(e), .. } => {
616 contains_complex_expr(e)
617 }
618 _ => false,
619 });
620
621 if has_complex {
622 let inner_indent = indent_level + 2;
623 result.push('\n');
624 for (i, arg) in list.args.iter().enumerate() {
625 if i > 0 {
626 result.push_str(",\n");
627 }
628 result.push_str(&" ".repeat(inner_indent));
629 result.push_str(&format_table_function_arg(arg, inner_indent));
630 }
631 result.push('\n');
632 result.push_str(&" ".repeat(indent_level));
633 } else {
634 let args: Vec<String> = list
635 .args
636 .iter()
637 .map(|arg| format_table_function_arg(arg, indent_level))
638 .collect();
639 result.push_str(&args.join(", "));
640 }
641 }
642 };
643
644 result.push(')');
645 result
646}
647
648fn format_function_arg_expr(arg: &FunctionArgExpr, indent_level: usize) -> String {
649 match arg {
650 FunctionArgExpr::Expr(Expr::Subquery(query)) => {
651 let inner_indent = indent_level + 2;
652 format!(
653 "(\n{}\n{})",
654 format_query(query, inner_indent),
655 " ".repeat(indent_level)
656 )
657 }
658 FunctionArgExpr::Expr(expr) => format_expr(expr, indent_level),
659 FunctionArgExpr::QualifiedWildcard(name) => format!("{}.*", name),
660 FunctionArgExpr::Wildcard => "*".to_string(),
661 }
662}
663
664fn format_value(value: &Value) -> String {
665 match value {
666 Value::Number(n, _) => n.clone(),
667 Value::SingleQuotedString(s) => format!("'{}'", s),
668 Value::DoubleQuotedString(s) => format!("\"{}\"", s),
669 Value::Boolean(b) => b.to_string().to_uppercase(),
670 Value::Null => "NULL".to_string(),
671 _ => value.to_string(),
672 }
673}
674
675fn format_order_by_expr(order: &OrderByExpr, indent_level: usize) -> String {
676 let mut result = format_expr(&order.expr, indent_level);
677
678 if let Some(asc) = order.options.asc {
679 if asc {
680 result.push_str(" ASC");
681 } else {
682 result.push_str(" DESC");
683 }
684 }
685
686 if let Some(nulls_first) = order.options.nulls_first {
687 if nulls_first {
688 result.push_str(" NULLS FIRST");
689 } else {
690 result.push_str(" NULLS LAST");
691 }
692 }
693
694 result
695}
696
697fn format_update(
698 table: &TableWithJoins,
699 assignments: &[Assignment],
700 selection: &Option<Expr>,
701 indent_level: usize,
702) -> String {
703 let mut result = format!(
704 "{}UPDATE {}",
705 " ".repeat(indent_level),
706 format_table_with_joins(table, indent_level)
707 );
708
709 result.push('\n');
710 result.push_str(&format!("{}SET", " ".repeat(indent_level)));
711
712 for (i, assignment) in assignments.iter().enumerate() {
713 if i == 0 {
714 result.push('\n');
715 } else {
716 result.push_str(",\n");
717 }
718 result.push_str(&format!(
719 "{} {} = {}",
720 " ".repeat(indent_level),
721 match &assignment.target {
722 AssignmentTarget::ColumnName(object_name) => object_name.to_string(),
723 _ => "UNKNOWN".to_string(),
724 },
725 format_expr(&assignment.value, indent_level + 2)
726 ));
727 }
728
729 if let Some(selection) = selection {
730 result.push('\n');
731 result.push_str(&format!(
732 "{}WHERE {}",
733 " ".repeat(indent_level),
734 format_where_expr(selection, indent_level + 2)
735 ));
736 }
737
738 result
739}
740
741fn is_simple_projection(item: &SelectItem) -> bool {
742 match item {
743 SelectItem::UnnamedExpr(expr) => is_simple_expr(expr),
744 SelectItem::ExprWithAlias { expr, .. } => is_simple_expr(expr),
745 SelectItem::Wildcard(_) => true,
746 SelectItem::QualifiedWildcard(_, _) => true,
747 }
748}
749
750fn is_simple_expr(expr: &Expr) -> bool {
751 match expr {
752 Expr::Identifier(_) => true,
753 Expr::CompoundIdentifier(_) => true,
754 Expr::Value(_) => true,
755 _ => false,
756 }
757}
758
759fn contains_complex_expr(expr: &Expr) -> bool {
760 match expr {
761 Expr::Subquery(_) => true,
762 Expr::InSubquery { .. } => true,
763 Expr::Function(f) => {
764 if let FunctionArguments::List(list) = &f.args {
765 list.args.iter().any(|arg| match arg {
766 FunctionArg::Unnamed(FunctionArgExpr::Expr(e)) => contains_complex_expr(e),
767 FunctionArg::Named { arg: FunctionArgExpr::Expr(e), .. } => {
768 contains_complex_expr(e)
769 }
770 FunctionArg::ExprNamed { arg: FunctionArgExpr::Expr(e), .. } => {
771 contains_complex_expr(e)
772 }
773 _ => false,
774 })
775 } else {
776 matches!(f.args, FunctionArguments::Subquery(_))
777 }
778 }
779 Expr::Nested(inner) => contains_complex_expr(inner),
780 Expr::BinaryOp { left, right, .. } => {
781 contains_complex_expr(left) || contains_complex_expr(right)
782 }
783 _ => false,
784 }
785}
786
787fn format_where_expr(expr: &Expr, indent_level: usize) -> String {
788 match expr {
789 Expr::BinaryOp { left, op, right } if matches!(op, BinaryOperator::And) => {
790 let left_str = format_where_expr(left, indent_level);
791 let right_str = format_where_expr(right, indent_level);
792 format!(
793 "{}\n{}AND {}",
794 left_str,
795 " ".repeat(indent_level),
796 right_str
797 )
798 }
799 _ => format_expr(expr, indent_level),
800 }
801}
802
803#[cfg(test)]
804mod tests {
805 use super::*;
806 use sqlparser::dialect::GenericDialect;
807 use sqlparser::parser::Parser;
808
809 fn parse_and_format(sql: &str) -> String {
810 let dialect = GenericDialect {};
811 let statements = Parser::parse_sql(&dialect, sql).unwrap();
812 format_statement(&statements[0], 0)
813 }
814
815 #[test]
816 fn test_simple_select_single_column() {
817 let input = "SELECT id FROM users";
818 let expected = "SELECT id\nFROM\n users";
819 assert_eq!(parse_and_format(input), expected);
820 }
821
822 #[test]
823 fn test_simple_select_with_alias() {
824 let input = "SELECT 1 AS one FROM users";
825 let expected = "SELECT 1 AS one\nFROM\n users";
826 assert_eq!(parse_and_format(input), expected);
827 }
828
829 #[test]
830 fn test_multi_column_select() {
831 let input = "SELECT id, name, email FROM users";
832 let expected = "SELECT\n id,\n name,\n email\nFROM\n users";
833 assert_eq!(parse_and_format(input), expected);
834 }
835
836 #[test]
837 fn test_select_with_where() {
838 let input = "SELECT * FROM users WHERE active = true";
839 let expected = "SELECT *\nFROM\n users\nWHERE active = TRUE";
840 assert_eq!(parse_and_format(input), expected);
841 }
842
843 #[test]
844 fn test_select_with_multiple_and_conditions() {
845 let input = "SELECT * FROM users WHERE active = true AND age > 18 AND name LIKE 'A%'";
846 let expected =
847 "SELECT *\nFROM\n users\nWHERE active = TRUE\n AND age > 18\n AND name LIKE 'A%'";
848 assert_eq!(parse_and_format(input), expected);
849 }
850
851 #[test]
852 fn test_select_with_join() {
853 let input = "SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id";
854 let expected = "SELECT\n u.name,\n p.title\nFROM\n users AS u\n INNER JOIN posts AS p ON u.id = p.user_id";
855 assert_eq!(parse_and_format(input), expected);
856 }
857
858 #[test]
859 fn test_select_with_subquery() {
860 let input =
861 "SELECT * FROM (SELECT id, name FROM users WHERE active = true) AS active_users";
862 let expected = "SELECT *\nFROM\n (\n SELECT\n id,\n name\n FROM\n users\n WHERE active = TRUE\n ) AS active_users";
863 assert_eq!(parse_and_format(input), expected);
864 }
865
866 #[test]
867 fn test_select_with_case_expression() {
868 let input =
869 "SELECT CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END AS age_group FROM users";
870 let expected = "SELECT\n CASE\n WHEN age < 18 THEN 'minor'\n ELSE 'adult'\n END AS age_group\nFROM\n users";
871 assert_eq!(parse_and_format(input), expected);
872 }
873
874 #[test]
875 fn test_select_with_cte() {
876 let input = "WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users";
877 let expected = "WITH\n active_users AS (\n SELECT *\n FROM\n users\n WHERE active = TRUE\n )\nSELECT *\nFROM\n active_users";
878 assert_eq!(parse_and_format(input), expected);
879 }
880
881 #[test]
882 fn test_select_with_group_by_and_having() {
883 let input =
884 "SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5";
885 let expected = "SELECT\n department,\n COUNT(*)\nFROM\n employees\nGROUP BY department\nHAVING COUNT(*) > 5";
886 assert_eq!(parse_and_format(input), expected);
887 }
888
889 #[test]
890 fn test_select_with_order_by_and_limit() {
891 let input = "SELECT * FROM users ORDER BY name ASC LIMIT 10";
892 let expected = "SELECT *\nFROM\n users\nORDER BY name ASC\nLIMIT 10";
893 assert_eq!(parse_and_format(input), expected);
894 }
895
896 #[test]
897 fn test_union_query() {
898 let input = "SELECT id FROM users UNION SELECT id FROM customers";
899 let expected = "SELECT id\nFROM\n users\nUNION\nSELECT id\nFROM\n customers";
900 assert_eq!(parse_and_format(input), expected);
901 }
902
903 #[test]
904 fn test_function_calls() {
905 let input = "SELECT COUNT(*), MAX(age), UPPER(name) FROM users";
906 let expected = "SELECT\n COUNT(*),\n MAX(age),\n UPPER(name)\nFROM\n users";
907 assert_eq!(parse_and_format(input), expected);
908 }
909
910 #[test]
911 fn test_distinct_select() {
912 let input = "SELECT DISTINCT department FROM employees";
913 let expected = "SELECT DISTINCT department\nFROM\n employees";
914 assert_eq!(parse_and_format(input), expected);
915 }
916
917 #[test]
918 fn test_between_expression() {
919 let input = "SELECT * FROM products WHERE price BETWEEN 10 AND 100";
920 let expected = "SELECT *\nFROM\n products\nWHERE price BETWEEN 10 AND 100";
921 assert_eq!(parse_and_format(input), expected);
922 }
923
924 #[test]
925 fn test_in_subquery() {
926 let input = "SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)";
927 let expected =
928 "SELECT *\nFROM\n users\nWHERE id IN (\n SELECT user_id\n FROM\n orders\n )";
929 assert_eq!(parse_and_format(input), expected);
930 }
931
932 #[test]
933 fn test_is_null_expression() {
934 let input = "SELECT * FROM users WHERE email IS NOT NULL";
935 let expected = "SELECT *\nFROM\n users\nWHERE email IS NOT NULL";
936 assert_eq!(parse_and_format(input), expected);
937 }
938
939 #[test]
940 fn test_cast_expression() {
941 let input = "SELECT CAST(id AS VARCHAR) FROM users";
942 let expected = "SELECT\n CAST(id AS VARCHAR)\nFROM\n users";
943 assert_eq!(parse_and_format(input), expected);
944 }
945
946 #[test]
947 fn test_nested_expressions() {
948 let input = "SELECT * FROM users WHERE (active = true AND age > 18) OR role = 'admin'";
949 let expected =
950 "SELECT *\nFROM\n users\nWHERE (active = TRUE AND age > 18) OR role = 'admin'";
951 assert_eq!(parse_and_format(input), expected);
952 }
953
954 #[test]
955 fn test_multiple_joins() {
956 let input = "SELECT u.name, p.title, c.content FROM users u LEFT JOIN posts p ON u.id = p.user_id RIGHT JOIN comments c ON p.id = c.post_id";
957 let expected = "SELECT\n u.name,\n p.title,\n c.content\nFROM\n users AS u\n LEFT OUTER JOIN posts AS p ON u.id = p.user_id\n RIGHT OUTER JOIN comments AS c ON p.id = c.post_id";
958 assert_eq!(parse_and_format(input), expected);
959 }
960
961 #[test]
962 fn test_wildcard_expressions() {
963 let input = "SELECT *, users.* FROM users";
964 let expected = "SELECT\n *,\n users.*\nFROM\n users";
965 assert_eq!(parse_and_format(input), expected);
966 }
967
968 #[test]
969 fn test_complex_case_expression() {
970 let input =
971 "SELECT CASE status WHEN 'active' THEN 1 WHEN 'inactive' THEN 0 ELSE -1 END FROM users";
972 let expected = "SELECT\n CASE status\n WHEN 'active' THEN 1\n WHEN 'inactive' THEN 0\n ELSE - 1\n END\nFROM\n users";
973 assert_eq!(parse_and_format(input), expected);
974 }
975}
976
977fn format_insert(insert: &Insert, indent_level: usize) -> String {
978 let mut result = format!("{}INSERT INTO {}", " ".repeat(indent_level), insert.table);
979
980 result.push_str(&format_column_list(&insert.columns, indent_level));
981
982 if let Some(source) = &insert.source {
983 result.push('\n');
984 result.push_str(&format!("{}VALUES", " ".repeat(indent_level)));
985 if let SetExpr::Values(values) = &*source.body {
986 for (i, row) in values.rows.iter().enumerate() {
987 if i > 0 {
988 result.push_str(",\n");
989 } else {
990 result.push(' ');
991 }
992 result.push_str(&format_values_row(row, indent_level));
993 }
994 }
995 }
996
997 result
998}
999
1000fn format_delete(delete: &Delete, indent_level: usize) -> String {
1001 let mut result = format!("{}DELETE", " ".repeat(indent_level));
1002
1003 if !delete.tables.is_empty() {
1004 result.push(' ');
1005 for (i, table) in delete.tables.iter().enumerate() {
1006 if i > 0 {
1007 result.push_str(", ");
1008 }
1009 result.push_str(&table.to_string());
1010 }
1011 }
1012
1013 match &delete.from {
1014 FromTable::WithFromKeyword(tables) => {
1015 result.push_str(" FROM");
1016 if !tables.is_empty() {
1017 result.push(' ');
1018 result.push_str(&tables[0].to_string());
1019 }
1020 }
1021 FromTable::WithoutKeyword(tables) => {
1022 if !tables.is_empty() {
1023 result.push_str(" FROM ");
1024 result.push_str(&tables[0].to_string());
1025 }
1026 }
1027 }
1028
1029 if let Some(selection) = &delete.selection {
1030 result.push('\n');
1031 result.push_str(&format!(
1032 "{}WHERE {}",
1033 " ".repeat(indent_level),
1034 format_where_expr(selection, indent_level + 2)
1035 ));
1036 }
1037
1038 result
1039}
1040
1041fn format_create_table(create_table: &CreateTable, indent_level: usize) -> String {
1042 let mut result = format!(
1043 "{}CREATE TABLE {} (",
1044 " ".repeat(indent_level),
1045 create_table.name
1046 );
1047
1048 result.push_str(&format_indented_list(
1049 &create_table.columns,
1050 |col| format_column_def(col, indent_level + 2),
1051 indent_level,
1052 true,
1053 ));
1054
1055 result.push(')');
1056 result
1057}
1058
1059fn format_column_def(column: &ColumnDef, indent_level: usize) -> String {
1060 let mut result = format!("{} {}", column.name.value, column.data_type);
1061
1062 for option in &column.options {
1063 match &option.option {
1064 ColumnOption::NotNull => result.push_str(" NOT NULL"),
1065 ColumnOption::Null => result.push_str(" NULL"),
1066 ColumnOption::Default(expr) => {
1067 result.push_str(&format!(" DEFAULT {}", format_expr(expr, indent_level)));
1068 }
1069 ColumnOption::Unique { is_primary, .. } => {
1070 if *is_primary {
1071 result.push_str(" PRIMARY KEY");
1072 } else {
1073 result.push_str(" UNIQUE");
1074 }
1075 }
1076 _ => {}
1077 }
1078 }
1079
1080 result
1081}
1082
1083fn format_column_list(columns: &[Ident], indent_level: usize) -> String {
1084 if columns.is_empty() {
1085 return String::new();
1086 }
1087
1088 if columns.len() == 1 {
1089 format!(" ({})", columns[0].value)
1090 } else {
1091 let mut result = String::from(" (");
1092 result.push('\n');
1093 for (i, column) in columns.iter().enumerate() {
1094 if i > 0 {
1095 result.push_str(",\n");
1096 }
1097 result.push_str(&format!("{} {}", " ".repeat(indent_level), column.value));
1098 }
1099 result.push('\n');
1100 result.push_str(&format!("{}", " ".repeat(indent_level)));
1101 result.push(')');
1102 result
1103 }
1104}
1105
1106fn format_indented_list<T, F>(items: &[T], formatter: F, indent_level: usize, single_line: bool) -> String
1107where
1108 F: Fn(&T) -> String,
1109{
1110 if items.is_empty() {
1111 return String::new();
1112 }
1113
1114 if items.len() == 1 && single_line {
1115 formatter(&items[0])
1116 } else {
1117 let mut result = String::new();
1118 result.push('\n');
1119 for (i, item) in items.iter().enumerate() {
1120 if i > 0 {
1121 result.push_str(",\n");
1122 }
1123 result.push_str(&format!("{} {}", " ".repeat(indent_level), formatter(item)));
1124 }
1125 result.push('\n');
1126 result.push_str(&format!("{}", " ".repeat(indent_level)));
1127 result
1128 }
1129}
1130
1131fn format_values_row(row: &[Expr], indent_level: usize) -> String {
1132 if row.len() == 1 {
1133 format!("({})", format_expr(&row[0], indent_level))
1134 } else {
1135 let mut result = String::from("(");
1136 result.push('\n');
1137 for (i, value) in row.iter().enumerate() {
1138 if i > 0 {
1139 result.push_str(",\n");
1140 }
1141 result.push_str(&format!(
1142 "{} {}",
1143 " ".repeat(indent_level),
1144 format_expr(value, indent_level + 2)
1145 ));
1146 }
1147 result.push('\n');
1148 result.push_str(&" ".repeat(indent_level));
1149 result.push(')');
1150 result
1151 }
1152}