1#![type_length_limit = "99999999"]
6#![forbid(unsafe_code)]
7#![allow(clippy::manual_strip)]
9#![allow(clippy::needless_lifetimes)]
11
12mod formatter;
13mod indentation;
14mod inline_block;
15mod params;
16mod tokenizer;
17
18pub fn format(query: &str, params: &QueryParams, options: &FormatOptions) -> String {
21 let named_placeholders = matches!(params, QueryParams::Named(_));
22
23 let tokens = tokenizer::tokenize(query, named_placeholders, options);
24 formatter::format(&tokens, params, options)
25}
26
27#[derive(Debug, Clone)]
29pub struct FormatOptions<'a> {
30 pub indent: Indent,
34 pub uppercase: Option<bool>,
38 pub lines_between_queries: u8,
42 pub ignore_case_convert: Option<Vec<&'a str>>,
46 pub inline: bool,
50 pub max_inline_block: usize,
54 pub max_inline_arguments: Option<usize>,
60 pub max_inline_top_level: Option<usize>,
64 pub joins_as_top_level: bool,
68}
69
70impl<'a> Default for FormatOptions<'a> {
71 fn default() -> Self {
72 FormatOptions {
73 indent: Indent::Spaces(2),
74 uppercase: None,
75 lines_between_queries: 1,
76 ignore_case_convert: None,
77 inline: false,
78 max_inline_block: 50,
79 max_inline_arguments: None,
80 max_inline_top_level: None,
81 joins_as_top_level: false,
82 }
83 }
84}
85
86#[derive(Debug, Clone, Copy)]
87pub enum Indent {
88 Spaces(u8),
89 Tabs,
90}
91
92#[derive(Debug, Clone, Default)]
93pub enum QueryParams {
94 Named(Vec<(String, String)>),
95 Indexed(Vec<String>),
96 #[default]
97 None,
98}
99
100#[derive(Default, Debug, Clone)]
101pub(crate) struct SpanInfo {
102 pub full_span: usize,
103 pub blocks: usize,
104 pub newline_before: bool,
105 pub newline_after: bool,
106 pub arguments: usize,
107}
108
109#[cfg(test)]
110mod tests {
111 use super::*;
112 use indoc::indoc;
113 use pretty_assertions::assert_eq;
114
115 #[test]
116 fn test_sqlite_blob_literal_fmt() {
117 let options = FormatOptions::default();
118
119 let input = "SELECT x'73716c69676874' AS BLOB_VAL;";
120 let expected = indoc!(
121 "
122 SELECT
123 x'73716c69676874' AS BLOB_VAL;"
124 );
125 assert_eq!(format(input, &QueryParams::None, &options), expected);
126
127 let input = "SELECT X'73716c69676874' AS BLOB_VAL;";
128 let expected = indoc!(
129 "
130 SELECT
131 X'73716c69676874' AS BLOB_VAL;"
132 );
133 assert_eq!(format(input, &QueryParams::None, &options), expected);
134 }
135
136 #[test]
137 fn it_uses_given_indent_config_for_indentation() {
138 let input = "SELECT count(*),Column1 FROM Table1;";
139 let options = FormatOptions {
140 indent: Indent::Spaces(4),
141 ..FormatOptions::default()
142 };
143 let expected = indoc!(
144 "
145 SELECT
146 count(*),
147 Column1
148 FROM
149 Table1;"
150 );
151
152 assert_eq!(format(input, &QueryParams::None, &options), expected);
153 }
154
155 #[test]
156 fn it_formats_simple_set_schema_queries() {
157 let input = "SET SCHEMA schema1; SET CURRENT SCHEMA schema2;";
158 let options = FormatOptions::default();
159 let expected = indoc!(
160 "
161 SET SCHEMA
162 schema1;
163 SET CURRENT SCHEMA
164 schema2;"
165 );
166
167 assert_eq!(format(input, &QueryParams::None, &options), expected);
168 }
169
170 #[test]
171 fn it_formats_simple_select_query() {
172 let input = "SELECT count(*),Column1 FROM Table1;";
173 let options = FormatOptions::default();
174 let expected = indoc!(
175 "
176 SELECT
177 count(*),
178 Column1
179 FROM
180 Table1;"
181 );
182
183 assert_eq!(format(input, &QueryParams::None, &options), expected);
184 }
185
186 #[test]
187 fn it_formats_complex_select() {
188 let input =
189 "SELECT DISTINCT name, ROUND(age/7) field1, 18 + 20 AS field2, 'some string' FROM foo;";
190 let options = FormatOptions::default();
191 let expected = indoc!(
192 "
193 SELECT DISTINCT
194 name,
195 ROUND(age / 7) field1,
196 18 + 20 AS field2,
197 'some string'
198 FROM
199 foo;"
200 );
201
202 assert_eq!(format(input, &QueryParams::None, &options), expected);
203 }
204
205 #[test]
206 fn it_formats_over_with_window() {
207 let input =
208 "SELECT id, val, at, SUM(val) OVER win AS cumulative FROM data WINDOW win AS (PARTITION BY id ORDER BY at);";
209 let options = FormatOptions::default();
210 let expected = indoc!(
211 "
212 SELECT
213 id,
214 val,
215 at,
216 SUM(val) OVER win AS cumulative
217 FROM
218 data
219 WINDOW
220 win AS (
221 PARTITION BY
222 id
223 ORDER BY
224 at
225 );"
226 );
227
228 assert_eq!(format(input, &QueryParams::None, &options), expected);
229 }
230
231 #[test]
232 fn it_formats_distinct_from() {
233 let input = "SELECT bar IS DISTINCT FROM 'baz', IS NOT DISTINCT FROM 'foo' FROM foo;";
234 let options = FormatOptions::default();
235 let expected = indoc!(
236 "
237 SELECT
238 bar IS DISTINCT FROM 'baz',
239 IS NOT DISTINCT FROM 'foo'
240 FROM
241 foo;"
242 );
243
244 assert_eq!(format(input, &QueryParams::None, &options), expected);
245 }
246
247 #[test]
248 fn keep_select_arguments_inline() {
249 let input = indoc! {
250 "
251 SELECT
252 a,
253 b,
254 c,
255 d,
256 e,
257 f,
258 g,
259 h
260 FROM foo;"
261 };
262 let options = FormatOptions {
263 max_inline_arguments: Some(50),
264 ..Default::default()
265 };
266 let expected = indoc! {
267 "
268 SELECT
269 a, b, c, d, e, f, g, h
270 FROM
271 foo;"
272 };
273 assert_eq!(format(input, &QueryParams::None, &options), expected);
274 }
275
276 #[test]
277 fn split_select_arguments_inline_top_level() {
278 let input = indoc! {
279 "
280 SELECT
281 a,
282 b,
283 c,
284 d,
285 e,
286 f,
287 g,
288 h
289 FROM foo;"
290 };
291 let options = FormatOptions {
292 max_inline_arguments: Some(50),
293 max_inline_top_level: Some(50),
294 ..Default::default()
295 };
296 let expected = indoc! {
297 "
298 SELECT a, b, c, d, e, f, g, h
299 FROM foo;"
300 };
301 assert_eq!(format(input, &QueryParams::None, &options), expected);
302 }
303
304 #[test]
305 fn inline_arguments_when_possible() {
306 let input = indoc! {
307 "
308 SELECT
309 a,
310 b,
311 c,
312 d,
313 e,
314 f,
315 g,
316 h
317 FROM foo;"
318 };
319 let options = FormatOptions {
320 max_inline_arguments: Some(50),
321 max_inline_top_level: Some(20),
322 ..Default::default()
323 };
324 let expected = indoc! {
325 "
326 SELECT
327 a, b, c, d, e, f, g, h
328 FROM foo;"
329 };
330 assert_eq!(format(input, &QueryParams::None, &options), expected);
331 }
332
333 #[test]
334 fn inline_single_block_argument() {
335 let input = "SELECT a, b, c FROM ( SELECT (e+f) AS a, (m+o) AS b FROM d) WHERE (a != b) OR (c IS NULL AND a == b)";
336 let options = FormatOptions {
337 max_inline_arguments: Some(10),
338 max_inline_top_level: Some(20),
339 ..Default::default()
340 };
341 let expected = indoc! {
342 "
343 SELECT a, b, c
344 FROM (
345 SELECT
346 (e + f) AS a,
347 (m + o) AS b
348 FROM d
349 )
350 WHERE
351 (a != b)
352 OR (
353 c IS NULL
354 AND a == b
355 )"
356 };
357 assert_eq!(format(input, &QueryParams::None, &options), expected);
358 }
359
360 #[test]
361 fn it_formats_select_with_complex_where() {
362 let input = indoc!(
363 "
364 SELECT * FROM foo WHERE Column1 = 'testing'
365 AND ( (Column2 = Column3 OR Column4 >= NOW()) );
366 "
367 );
368 let options = FormatOptions::default();
369 let expected = indoc!(
370 "
371 SELECT
372 *
373 FROM
374 foo
375 WHERE
376 Column1 = 'testing'
377 AND (
378 (
379 Column2 = Column3
380 OR Column4 >= NOW()
381 )
382 );"
383 );
384
385 assert_eq!(format(input, &QueryParams::None, &options), expected);
386 }
387
388 #[test]
389 fn it_formats_select_with_complex_where_inline() {
390 let input = indoc!(
391 "
392 SELECT * FROM foo WHERE Column1 = 'testing'
393 AND ( (Column2 = Column3 OR Column4 >= NOW()) );
394 "
395 );
396 let options = FormatOptions {
397 max_inline_arguments: Some(100),
398 ..Default::default()
399 };
400 let expected = indoc!(
401 "
402 SELECT
403 *
404 FROM
405 foo
406 WHERE
407 Column1 = 'testing' AND ((Column2 = Column3 OR Column4 >= NOW()));"
408 );
409
410 assert_eq!(format(input, &QueryParams::None, &options), expected);
411 }
412
413 #[test]
414 fn it_formats_select_with_top_level_reserved_words() {
415 let input = indoc!(
416 "
417 SELECT * FROM foo WHERE name = 'John' GROUP BY some_column
418 HAVING column > 10 ORDER BY other_column LIMIT 5;
419 "
420 );
421 let options = FormatOptions::default();
422 let expected = indoc!(
423 "
424 SELECT
425 *
426 FROM
427 foo
428 WHERE
429 name = 'John'
430 GROUP BY
431 some_column
432 HAVING
433 column > 10
434 ORDER BY
435 other_column
436 LIMIT
437 5;"
438 );
439
440 assert_eq!(format(input, &QueryParams::None, &options), expected);
441 }
442
443 #[test]
444 fn it_formats_limit_with_two_comma_separated_values_on_single_line() {
445 let input = "LIMIT 5, 10;";
446 let options = FormatOptions::default();
447 let expected = indoc!(
448 "
449 LIMIT
450 5, 10;"
451 );
452
453 assert_eq!(format(input, &QueryParams::None, &options), expected);
454 }
455
456 #[test]
457 fn it_formats_limit_of_single_value_followed_by_another_select_using_commas() {
458 let input = "LIMIT 5; SELECT foo, bar;";
459 let options = FormatOptions::default();
460 let expected = indoc!(
461 "
462 LIMIT
463 5;
464 SELECT
465 foo,
466 bar;"
467 );
468
469 assert_eq!(format(input, &QueryParams::None, &options), expected);
470 }
471
472 #[test]
473 fn it_formats_type_specifiers() {
474 let input = "SELECT id, ARRAY [] :: UUID [] FROM UNNEST($1 :: UUID []) WHERE $1::UUID[] IS NOT NULL;";
475 let options = FormatOptions::default();
476 let expected = indoc!(
477 "
478 SELECT
479 id,
480 ARRAY[]::UUID[]
481 FROM
482 UNNEST($1::UUID[])
483 WHERE
484 $1::UUID[] IS NOT NULL;"
485 );
486
487 assert_eq!(format(input, &QueryParams::None, &options), expected);
488 }
489
490 #[test]
491 fn it_formats_limit_of_single_value_and_offset() {
492 let input = "LIMIT 5 OFFSET 8;";
493 let options = FormatOptions::default();
494 let expected = indoc!(
495 "
496 LIMIT
497 5 OFFSET 8;"
498 );
499
500 assert_eq!(format(input, &QueryParams::None, &options), expected);
501 }
502
503 #[test]
504 fn it_recognizes_limit_in_lowercase() {
505 let input = "limit 5, 10;";
506 let options = FormatOptions::default();
507 let expected = indoc!(
508 "
509 limit
510 5, 10;"
511 );
512
513 assert_eq!(format(input, &QueryParams::None, &options), expected);
514 }
515
516 #[test]
517 fn it_preserves_case_of_keywords() {
518 let input = "select distinct * frOM foo left join bar WHERe a > 1 and b = 3";
519 let options = FormatOptions::default();
520 let expected = indoc!(
521 "
522 select distinct
523 *
524 frOM
525 foo
526 left join bar
527 WHERe
528 a > 1
529 and b = 3"
530 );
531
532 assert_eq!(format(input, &QueryParams::None, &options), expected);
533 }
534
535 #[test]
536 fn it_formats_select_query_with_select_query_inside_it() {
537 let input = "SELECT *, SUM(*) AS sum FROM (SELECT * FROM Posts LIMIT 30) WHERE a > b";
538 let options = FormatOptions::default();
539 let expected = indoc!(
540 "
541 SELECT
542 *,
543 SUM(*) AS sum
544 FROM
545 (
546 SELECT
547 *
548 FROM
549 Posts
550 LIMIT
551 30
552 )
553 WHERE
554 a > b"
555 );
556
557 assert_eq!(format(input, &QueryParams::None, &options), expected);
558 }
559
560 #[test]
561 fn it_formats_select_query_with_inner_join() {
562 let input = indoc!(
563 "
564 SELECT customer_id.from, COUNT(order_id) AS total FROM customers
565 INNER JOIN orders ON customers.customer_id = orders.customer_id;"
566 );
567 let options = FormatOptions::default();
568 let expected = indoc!(
569 "
570 SELECT
571 customer_id.from,
572 COUNT(order_id) AS total
573 FROM
574 customers
575 INNER JOIN orders ON customers.customer_id = orders.customer_id;"
576 );
577
578 assert_eq!(format(input, &QueryParams::None, &options), expected);
579 }
580
581 #[test]
582 fn it_formats_select_query_with_non_standard_join() {
583 let input = indoc!(
584 "
585 SELECT customer_id.from, COUNT(order_id) AS total FROM customers
586 INNER ANY JOIN orders ON customers.customer_id = orders.customer_id
587 LEFT
588 SEMI JOIN foo ON foo.id = customers.id
589 PASTE
590 JOIN bar
591 ;"
592 );
593 let options = FormatOptions::default();
594 let expected = indoc!(
595 "
596 SELECT
597 customer_id.from,
598 COUNT(order_id) AS total
599 FROM
600 customers
601 INNER ANY JOIN orders ON customers.customer_id = orders.customer_id
602 LEFT SEMI JOIN foo ON foo.id = customers.id
603 PASTE JOIN bar;"
604 );
605
606 assert_eq!(format(input, &QueryParams::None, &options), expected);
607 }
608
609 #[test]
610 fn it_formats_select_query_with_non_standard_join_as_toplevel() {
611 let input = indoc!(
612 "
613 SELECT customer_id.from, COUNT(order_id) AS total FROM customers
614 INNER ANY JOIN orders ON customers.customer_id = orders.customer_id
615 LEFT
616 SEMI JOIN foo ON foo.id = customers.id
617 PASTE
618 JOIN bar
619 ;"
620 );
621 let options = FormatOptions {
622 joins_as_top_level: true,
623 max_inline_top_level: Some(40),
624 max_inline_arguments: Some(40),
625 ..Default::default()
626 };
627 let expected = indoc!(
628 "
629 SELECT
630 customer_id.from,
631 COUNT(order_id) AS total
632 FROM customers
633 INNER ANY JOIN
634 orders ON customers.customer_id = orders.customer_id
635 LEFT SEMI JOIN foo ON foo.id = customers.id
636 PASTE JOIN bar;"
637 );
638
639 assert_eq!(format(input, &QueryParams::None, &options), expected);
640 }
641
642 #[test]
643 fn it_formats_select_query_with_different_comments() {
644 let input = indoc!(
645 "
646 SELECT
647 /*
648 * This is a block comment
649 */
650 * FROM
651 -- This is another comment
652 MyTable # One final comment
653 WHERE 1 = 2;"
654 );
655 let options = FormatOptions::default();
656 let expected = indoc!(
657 "
658 SELECT
659 /*
660 * This is a block comment
661 */
662 *
663 FROM
664 -- This is another comment
665 MyTable # One final comment
666 WHERE
667 1 = 2;"
668 );
669
670 assert_eq!(format(input, &QueryParams::None, &options), expected);
671 }
672
673 #[test]
674 fn it_maintains_block_comment_indentation() {
675 let input = indoc!(
676 "
677 SELECT
678 /*
679 * This is a block comment
680 */
681 *
682 FROM
683 MyTable
684 WHERE
685 1 = 2;"
686 );
687 let options = FormatOptions::default();
688
689 assert_eq!(format(input, &QueryParams::None, &options), input);
690 }
691
692 #[test]
693 fn it_formats_simple_insert_query() {
694 let input = "INSERT INTO Customers (ID, MoneyBalance, Address, City) VALUES (12,-123.4, 'Skagen 2111','Stv');";
695 let options = FormatOptions::default();
696 let expected = indoc!(
697 "
698 INSERT INTO
699 Customers (ID, MoneyBalance, Address, City)
700 VALUES
701 (12, -123.4, 'Skagen 2111', 'Stv');"
702 );
703
704 assert_eq!(format(input, &QueryParams::None, &options), expected);
705 }
706
707 #[test]
708 fn it_formats_complex_insert_query() {
709 let input = "
710 INSERT INTO t(id, a, min, max) SELECT input.id, input.a, input.min, input.max FROM ( SELECT id, a, min, max FROM foo WHERE a IN ('a', 'b') ) AS input WHERE (SELECT true FROM condition) ON CONFLICT ON CONSTRAINT a_id_key DO UPDATE SET id = EXCLUDED.id, a = EXCLUDED.severity, min = EXCLUDED.min, max = EXCLUDED.max RETURNING *; ";
711 let max_line = 50;
712 let options = FormatOptions {
713 max_inline_block: max_line,
714 max_inline_arguments: Some(max_line),
715 max_inline_top_level: Some(max_line),
716 ..Default::default()
717 };
718
719 let expected = indoc!(
720 "
721 INSERT INTO t(id, a, min, max)
722 SELECT input.id, input.a, input.min, input.max
723 FROM (
724 SELECT id, a, min, max
725 FROM foo
726 WHERE a IN ('a', 'b')
727 ) AS input
728 WHERE (SELECT true FROM condition)
729 ON CONFLICT ON CONSTRAINT a_id_key DO UPDATE SET
730 id = EXCLUDED.id,
731 a = EXCLUDED.severity,
732 min = EXCLUDED.min,
733 max = EXCLUDED.max
734 RETURNING *;"
735 );
736
737 assert_eq!(format(input, &QueryParams::None, &options), expected);
738 }
739
740 #[test]
741 fn it_keeps_short_parenthesized_list_with_nested_parenthesis_on_single_line() {
742 let input = "SELECT (a + b * (c - NOW()));";
743 let options = FormatOptions::default();
744 let expected = indoc!(
745 "
746 SELECT
747 (a + b * (c - NOW()));"
748 );
749
750 assert_eq!(format(input, &QueryParams::None, &options), expected);
751 }
752
753 #[test]
754 fn it_breaks_long_parenthesized_lists_to_multiple_lines() {
755 let input = indoc!(
756 "
757 INSERT INTO some_table (id_product, id_shop, id_currency, id_country, id_registration) (
758 SELECT IF(dq.id_discounter_shopping = 2, dq.value, dq.value / 100),
759 IF (dq.id_discounter_shopping = 2, 'amount', 'percentage') FROM foo);"
760 );
761 let options = FormatOptions::default();
762 let expected = indoc!(
763 "
764 INSERT INTO
765 some_table (
766 id_product,
767 id_shop,
768 id_currency,
769 id_country,
770 id_registration
771 ) (
772 SELECT
773 IF (
774 dq.id_discounter_shopping = 2,
775 dq.value,
776 dq.value / 100
777 ),
778 IF (
779 dq.id_discounter_shopping = 2,
780 'amount',
781 'percentage'
782 )
783 FROM
784 foo
785 );"
786 );
787
788 assert_eq!(format(input, &QueryParams::None, &options), expected);
789 }
790
791 #[test]
792 fn it_keep_long_parenthesized_lists_to_multiple_lines() {
793 let input = indoc!(
794 "
795 INSERT INTO some_table (id_product, id_shop, id_currency, id_country, id_registration) (
796 SELECT IF (dq.id_discounter_shopping = 2, dq.value, dq.value / 100),
797 IF (dq.id_discounter_shopping = 2, 'amount', 'percentage') FROM foo);"
798 );
799 let options = FormatOptions {
800 max_inline_block: 100,
801 ..Default::default()
802 };
803 let expected = indoc!(
804 "
805 INSERT INTO
806 some_table (id_product, id_shop, id_currency, id_country, id_registration) (
807 SELECT
808 IF (dq.id_discounter_shopping = 2, dq.value, dq.value / 100),
809 IF (dq.id_discounter_shopping = 2, 'amount', 'percentage')
810 FROM
811 foo
812 );"
813 );
814
815 assert_eq!(format(input, &QueryParams::None, &options), expected);
816 }
817
818 #[test]
819 fn it_formats_simple_update_query() {
820 let input = "UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste';";
821 let options = FormatOptions::default();
822 let expected = indoc!(
823 "
824 UPDATE
825 Customers
826 SET
827 ContactName = 'Alfred Schmidt',
828 City = 'Hamburg'
829 WHERE
830 CustomerName = 'Alfreds Futterkiste';"
831 );
832
833 assert_eq!(format(input, &QueryParams::None, &options), expected);
834 }
835
836 #[test]
837 fn it_formats_simple_update_query_inlining_set() {
838 let input = "UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste';";
839 let options = FormatOptions {
840 max_inline_top_level: Some(20),
841 max_inline_arguments: Some(10),
842 ..Default::default()
843 };
844 let expected = indoc!(
845 "
846 UPDATE Customers SET
847 ContactName = 'Alfred Schmidt',
848 City = 'Hamburg'
849 WHERE
850 CustomerName = 'Alfreds Futterkiste';"
851 );
852
853 assert_eq!(format(input, &QueryParams::None, &options), expected);
854 }
855
856 #[test]
857 fn it_formats_simple_delete_query() {
858 let input = "DELETE FROM Customers WHERE CustomerName='Alfred' AND Phone=5002132;";
859 let options = FormatOptions::default();
860 let expected = indoc!(
861 "
862 DELETE FROM
863 Customers
864 WHERE
865 CustomerName = 'Alfred'
866 AND Phone = 5002132;"
867 );
868
869 assert_eq!(format(input, &QueryParams::None, &options), expected);
870 }
871
872 #[test]
873 fn it_formats_full_delete_query() {
874 let input =
875 "DELETE FROM Customers USING Phonebook WHERE CustomerName='Alfred' AND Phone=5002132;";
876 let options = FormatOptions::default();
877 let expected = indoc!(
878 "
879 DELETE FROM
880 Customers
881 USING
882 Phonebook
883 WHERE
884 CustomerName = 'Alfred'
885 AND Phone = 5002132;"
886 );
887
888 assert_eq!(format(input, &QueryParams::None, &options), expected);
889 }
890
891 #[test]
892 fn it_formats_simple_drop_query() {
893 let input = "DROP TABLE IF EXISTS admin_role;";
894 let options = FormatOptions::default();
895
896 assert_eq!(format(input, &QueryParams::None, &options), input);
897 }
898
899 #[test]
900 fn it_formats_incomplete_query() {
901 let input = "SELECT count(";
902 let options = FormatOptions::default();
903 let expected = indoc!(
904 "
905 SELECT
906 count("
907 );
908
909 assert_eq!(format(input, &QueryParams::None, &options), expected);
910 }
911
912 #[test]
913 fn it_formats_query_that_ends_with_open_comment() {
914 let input = indoc!(
915 "
916 SELECT count(*)
917 /*Comment"
918 );
919 let options = FormatOptions::default();
920 let expected = indoc!(
921 "
922 SELECT
923 count(*)
924 /*Comment"
925 );
926
927 assert_eq!(format(input, &QueryParams::None, &options), expected);
928 }
929
930 #[test]
931 fn it_formats_update_query_with_as_part() {
932 let input = "UPDATE customers SET total_orders = order_summary.total FROM ( SELECT * FROM bank) AS order_summary";
933 let options = FormatOptions::default();
934 let expected = indoc!(
935 "
936 UPDATE
937 customers
938 SET
939 total_orders = order_summary.total
940 FROM
941 (
942 SELECT
943 *
944 FROM
945 bank
946 ) AS order_summary"
947 );
948
949 assert_eq!(format(input, &QueryParams::None, &options), expected);
950 }
951
952 #[test]
953 fn it_formats_update_query_with_as_part_inline() {
954 let options = FormatOptions {
955 inline: true,
956 ..Default::default()
957 };
958 let expected = "UPDATE customers SET total_orders = order_summary.total FROM ( SELECT * FROM bank ) AS order_summary";
959 let input = indoc!(
960 "
961 UPDATE
962 customers
963 SET
964 total_orders = order_summary.total
965 FROM
966 (
967 SELECT
968 *
969 FROM
970 bank
971 ) AS order_summary"
972 );
973
974 assert_eq!(format(input, &QueryParams::None, &options), expected);
975 }
976
977 #[test]
978 fn it_formats_top_level_and_newline_multi_word_reserved_words_with_inconsistent_spacing() {
979 let input = "SELECT * FROM foo LEFT \t OUTER \n JOIN bar ORDER \n BY blah";
980 let options = FormatOptions::default();
981 let expected = indoc!(
982 "
983 SELECT
984 *
985 FROM
986 foo
987 LEFT OUTER JOIN bar
988 ORDER BY
989 blah"
990 );
991
992 assert_eq!(format(input, &QueryParams::None, &options), expected);
993 }
994
995 #[test]
996 fn it_formats_long_double_parenthesized_queries_to_multiple_lines() {
997 let input = "((foo = '0123456789-0123456789-0123456789-0123456789'))";
998 let options = FormatOptions::default();
999 let expected = indoc!(
1000 "
1001 (
1002 (
1003 foo = '0123456789-0123456789-0123456789-0123456789'
1004 )
1005 )"
1006 );
1007
1008 assert_eq!(format(input, &QueryParams::None, &options), expected);
1009 }
1010
1011 #[test]
1012 fn it_formats_short_double_parenthesizes_queries_to_one_line() {
1013 let input = "((foo = 'bar'))";
1014 let options = FormatOptions::default();
1015
1016 assert_eq!(format(input, &QueryParams::None, &options), input);
1017 }
1018
1019 #[test]
1020 fn it_formats_single_char_operators() {
1021 let inputs = [
1022 "foo = bar",
1023 "foo < bar",
1024 "foo > bar",
1025 "foo + bar",
1026 "foo - bar",
1027 "foo * bar",
1028 "foo / bar",
1029 "foo % bar",
1030 ];
1031 let options = FormatOptions::default();
1032 for input in &inputs {
1033 assert_eq!(&format(input, &QueryParams::None, &options), input);
1034 }
1035 }
1036
1037 #[test]
1038 fn it_formats_multi_char_operators() {
1039 let inputs = [
1040 "foo != bar",
1041 "foo <> bar",
1042 "foo == bar",
1043 "foo || bar",
1044 "foo <= bar",
1045 "foo >= bar",
1046 "foo !< bar",
1047 "foo !> bar",
1048 ];
1049 let options = FormatOptions::default();
1050 for input in &inputs {
1051 assert_eq!(&format(input, &QueryParams::None, &options), input);
1052 }
1053 }
1054
1055 #[test]
1056 fn it_formats_logical_operators() {
1057 let inputs = [
1058 "foo ALL bar",
1059 "foo = ANY (1, 2, 3)",
1060 "EXISTS bar",
1061 "foo IN (1, 2, 3)",
1062 "foo LIKE 'hello%'",
1063 "foo IS NULL",
1064 "UNIQUE foo",
1065 ];
1066 let options = FormatOptions::default();
1067 for input in &inputs {
1068 assert_eq!(&format(input, &QueryParams::None, &options), input);
1069 }
1070 }
1071
1072 #[test]
1073 fn it_formats_and_or_operators() {
1074 let strings = [
1075 ("foo BETWEEN bar AND baz", "foo BETWEEN bar AND baz"),
1076 ("foo BETWEEN\nbar\nAND baz", "foo BETWEEN bar AND baz"),
1077 ("foo AND bar", "foo\nAND bar"),
1078 ("foo OR bar", "foo\nOR bar"),
1079 ];
1080 let options = FormatOptions::default();
1081 for (input, output) in &strings {
1082 assert_eq!(&format(input, &QueryParams::None, &options), output);
1083 }
1084 }
1085
1086 #[test]
1087 fn it_recognizes_strings() {
1088 let inputs = ["\"foo JOIN bar\"", "'foo JOIN bar'", "`foo JOIN bar`"];
1089 let options = FormatOptions::default();
1090 for input in &inputs {
1091 assert_eq!(&format(input, &QueryParams::None, &options), input);
1092 }
1093 }
1094
1095 #[test]
1096 fn it_recognizes_escaped_strings() {
1097 let inputs = [
1098 r#""foo \" JOIN bar""#,
1099 r#"'foo \' JOIN bar'"#,
1100 r#"`foo `` JOIN bar`"#,
1101 r#"'foo '' JOIN bar'"#,
1102 r#"'two households"'"#,
1103 r#"'two households'''"#,
1104 r#"E'alice'''"#,
1105 ];
1106 let options = FormatOptions::default();
1107 for input in &inputs {
1108 assert_eq!(&format(input, &QueryParams::None, &options), input);
1109 }
1110 }
1111
1112 #[test]
1113 fn it_formats_postgres_specific_operators() {
1114 let strings = [
1115 ("column::int", "column::int"),
1116 ("v->2", "v -> 2"),
1117 ("v->>2", "v ->> 2"),
1118 ("foo ~~ 'hello'", "foo ~~ 'hello'"),
1119 ("foo !~ 'hello'", "foo !~ 'hello'"),
1120 ("foo ~* 'hello'", "foo ~* 'hello'"),
1121 ("foo ~~* 'hello'", "foo ~~* 'hello'"),
1122 ("foo !~~ 'hello'", "foo !~~ 'hello'"),
1123 ("foo !~* 'hello'", "foo !~* 'hello'"),
1124 ("foo !~~* 'hello'", "foo !~~* 'hello'"),
1125 ];
1126 let options = FormatOptions::default();
1127 for (input, output) in &strings {
1128 assert_eq!(&format(input, &QueryParams::None, &options), output);
1129 }
1130 }
1131
1132 #[test]
1133 fn it_keeps_separation_between_multiple_statements() {
1134 let strings = [
1135 ("foo;bar;", "foo;\nbar;"),
1136 ("foo\n;bar;", "foo;\nbar;"),
1137 ("foo\n\n\n;bar;\n\n", "foo;\nbar;"),
1138 ];
1139 let options = FormatOptions::default();
1140 for (input, output) in &strings {
1141 assert_eq!(&format(input, &QueryParams::None, &options), output);
1142 }
1143
1144 let input = indoc!(
1145 "
1146 SELECT count(*),Column1 FROM Table1;
1147 SELECT count(*),Column1 FROM Table2;"
1148 );
1149 let options = FormatOptions::default();
1150 let expected = indoc!(
1151 "
1152 SELECT
1153 count(*),
1154 Column1
1155 FROM
1156 Table1;
1157 SELECT
1158 count(*),
1159 Column1
1160 FROM
1161 Table2;"
1162 );
1163
1164 assert_eq!(format(input, &QueryParams::None, &options), expected);
1165 }
1166
1167 #[test]
1168 fn it_formats_unicode_correctly() {
1169 let input = "SELECT test, тест FROM table;";
1170 let options = FormatOptions::default();
1171 let expected = indoc!(
1172 "
1173 SELECT
1174 test,
1175 тест
1176 FROM
1177 table;"
1178 );
1179
1180 assert_eq!(format(input, &QueryParams::None, &options), expected);
1181 }
1182
1183 #[test]
1184 fn it_converts_keywords_to_uppercase_when_option_passed_in() {
1185 let input = "select distinct * frOM foo left join bar WHERe cola > 1 and colb = 3";
1186 let options = FormatOptions {
1187 uppercase: Some(true),
1188 ..FormatOptions::default()
1189 };
1190 let expected = indoc!(
1191 "
1192 SELECT DISTINCT
1193 *
1194 FROM
1195 foo
1196 LEFT JOIN bar
1197 WHERE
1198 cola > 1
1199 AND colb = 3"
1200 );
1201
1202 assert_eq!(format(input, &QueryParams::None, &options), expected);
1203 }
1204
1205 #[test]
1206 fn it_line_breaks_between_queries_with_config() {
1207 let input = "SELECT * FROM foo; SELECT * FROM bar;";
1208 let options = FormatOptions {
1209 lines_between_queries: 2,
1210 ..FormatOptions::default()
1211 };
1212 let expected = indoc!(
1213 "
1214 SELECT
1215 *
1216 FROM
1217 foo;
1218
1219 SELECT
1220 *
1221 FROM
1222 bar;"
1223 );
1224
1225 assert_eq!(format(input, &QueryParams::None, &options), expected);
1226 }
1227
1228 #[test]
1229 fn it_correctly_indents_create_statement_after_select() {
1230 let input = indoc!(
1231 "
1232 SELECT * FROM test;
1233 CREATE TABLE TEST(id NUMBER NOT NULL, col1 VARCHAR2(20), col2 VARCHAR2(20));
1234 "
1235 );
1236 let options = FormatOptions::default();
1237 let expected = indoc!(
1238 "
1239 SELECT
1240 *
1241 FROM
1242 test;
1243 CREATE TABLE TEST(
1244 id NUMBER NOT NULL,
1245 col1 VARCHAR2(20),
1246 col2 VARCHAR2(20)
1247 );"
1248 );
1249
1250 assert_eq!(format(input, &QueryParams::None, &options), expected);
1251 }
1252
1253 #[test]
1254 fn it_formats_short_create_table() {
1255 let input = "CREATE TABLE items (a INT PRIMARY KEY, b TEXT);";
1256 let options = FormatOptions::default();
1257
1258 assert_eq!(format(input, &QueryParams::None, &options), input);
1259 }
1260
1261 #[test]
1262 fn it_formats_long_create_table() {
1263 let input =
1264 "CREATE TABLE items (a INT PRIMARY KEY, b TEXT, c INT NOT NULL, d INT NOT NULL);";
1265 let options = FormatOptions::default();
1266 let expected = indoc!(
1267 "
1268 CREATE TABLE items (
1269 a INT PRIMARY KEY,
1270 b TEXT,
1271 c INT NOT NULL,
1272 d INT NOT NULL
1273 );"
1274 );
1275
1276 assert_eq!(format(input, &QueryParams::None, &options), expected);
1277 }
1278
1279 #[test]
1280 fn it_formats_insert_without_into() {
1281 let input =
1282 "INSERT Customers (ID, MoneyBalance, Address, City) VALUES (12,-123.4, 'Skagen 2111','Stv');";
1283 let options = FormatOptions::default();
1284 let expected = indoc!(
1285 "
1286 INSERT
1287 Customers (ID, MoneyBalance, Address, City)
1288 VALUES
1289 (12, -123.4, 'Skagen 2111', 'Stv');"
1290 );
1291
1292 assert_eq!(format(input, &QueryParams::None, &options), expected);
1293 }
1294
1295 #[test]
1296 fn it_formats_alter_table_modify_query() {
1297 let input = "ALTER TABLE supplier MODIFY supplier_name char(100) NOT NULL;";
1298 let options = FormatOptions::default();
1299 let expected = indoc!(
1300 "
1301 ALTER TABLE
1302 supplier
1303 MODIFY
1304 supplier_name char(100) NOT NULL;"
1305 );
1306
1307 assert_eq!(format(input, &QueryParams::None, &options), expected);
1308 }
1309
1310 #[test]
1311 fn it_formats_alter_table_alter_column_query() {
1312 let input = "ALTER TABLE supplier ALTER COLUMN supplier_name VARCHAR(100) NOT NULL;";
1313 let options = FormatOptions::default();
1314 let expected = indoc!(
1315 "
1316 ALTER TABLE
1317 supplier
1318 ALTER COLUMN
1319 supplier_name VARCHAR(100) NOT NULL;"
1320 );
1321
1322 assert_eq!(format(input, &QueryParams::None, &options), expected);
1323 }
1324
1325 #[test]
1326 fn it_recognizes_bracketed_strings() {
1327 let inputs = ["[foo JOIN bar]", "[foo ]] JOIN bar]"];
1328 let options = FormatOptions::default();
1329 for input in &inputs {
1330 assert_eq!(&format(input, &QueryParams::None, &options), input);
1331 }
1332 }
1333
1334 #[test]
1335 fn it_recognizes_at_variables() {
1336 let input =
1337 "SELECT @variable, @a1_2.3$, @'var name', @\"var name\", @`var name`, @[var name];";
1338 let options = FormatOptions::default();
1339 let expected = indoc!(
1340 "
1341 SELECT
1342 @variable,
1343 @a1_2.3$,
1344 @'var name',
1345 @\"var name\",
1346 @`var name`,
1347 @[var name];"
1348 );
1349
1350 assert_eq!(format(input, &QueryParams::None, &options), expected);
1351 }
1352
1353 #[test]
1354 fn it_recognizes_at_variables_with_param_values() {
1355 let input =
1356 "SELECT @variable, @a1_2.3$, @'var name', @\"var name\", @`var name`, @[var name], @'var\\name';";
1357 let params = vec![
1358 ("variable".to_string(), "\"variable value\"".to_string()),
1359 ("a1_2.3$".to_string(), "'weird value'".to_string()),
1360 ("var name".to_string(), "'var value'".to_string()),
1361 ("var\\name".to_string(), "'var\\ value'".to_string()),
1362 ];
1363 let options = FormatOptions::default();
1364 let expected = indoc!(
1365 "
1366 SELECT
1367 \"variable value\",
1368 'weird value',
1369 'var value',
1370 'var value',
1371 'var value',
1372 'var value',
1373 'var\\ value';"
1374 );
1375
1376 assert_eq!(
1377 format(input, &QueryParams::Named(params), &options),
1378 expected
1379 );
1380 }
1381
1382 #[test]
1383 fn it_recognizes_colon_variables() {
1384 let input =
1385 "SELECT :variable, :a1_2.3$, :'var name', :\"var name\", :`var name`, :[var name];";
1386 let options = FormatOptions::default();
1387 let expected = indoc!(
1388 "
1389 SELECT
1390 :variable,
1391 :a1_2.3$,
1392 :'var name',
1393 :\"var name\",
1394 :`var name`,
1395 :[var name];"
1396 );
1397
1398 assert_eq!(format(input, &QueryParams::None, &options), expected);
1399 }
1400
1401 #[test]
1402 fn it_recognizes_colon_variables_with_param_values() {
1403 let input = indoc!(
1404 "
1405 SELECT :variable, :a1_2.3$, :'var name', :\"var name\", :`var name`,
1406 :[var name], :'escaped \\'var\\'', :\"^*& weird \\\" var \";
1407 "
1408 );
1409 let params = vec![
1410 ("variable".to_string(), "\"variable value\"".to_string()),
1411 ("a1_2.3$".to_string(), "'weird value'".to_string()),
1412 ("var name".to_string(), "'var value'".to_string()),
1413 ("escaped 'var'".to_string(), "'weirder value'".to_string()),
1414 (
1415 "^*& weird \" var ".to_string(),
1416 "'super weird value'".to_string(),
1417 ),
1418 ];
1419 let options = FormatOptions::default();
1420 let expected = indoc!(
1421 "
1422 SELECT
1423 \"variable value\",
1424 'weird value',
1425 'var value',
1426 'var value',
1427 'var value',
1428 'var value',
1429 'weirder value',
1430 'super weird value';"
1431 );
1432
1433 assert_eq!(
1434 format(input, &QueryParams::Named(params), &options),
1435 expected
1436 );
1437 }
1438
1439 #[test]
1440 fn it_recognizes_question_numbered_placeholders() {
1441 let input = "SELECT ?1, ?25, ?;";
1442 let options = FormatOptions::default();
1443 let expected = indoc!(
1444 "
1445 SELECT
1446 ?1,
1447 ?25,
1448 ?;"
1449 );
1450
1451 assert_eq!(format(input, &QueryParams::None, &options), expected);
1452 }
1453
1454 #[test]
1455 fn it_recognizes_question_numbered_placeholders_with_param_values() {
1456 let input = "SELECT ?1, ?2, ?0;";
1457 let params = vec![
1458 "first".to_string(),
1459 "second".to_string(),
1460 "third".to_string(),
1461 ];
1462 let options = FormatOptions::default();
1463 let expected = indoc!(
1464 "
1465 SELECT
1466 second,
1467 third,
1468 first;"
1469 );
1470
1471 assert_eq!(
1472 format(input, &QueryParams::Indexed(params), &options),
1473 expected
1474 );
1475
1476 format("?62666666121266666612", &QueryParams::None, &options);
1477 }
1478
1479 #[test]
1480 fn it_recognizes_question_indexed_placeholders_with_param_values() {
1481 let input = "SELECT ?, ?, ?;";
1482 let params = vec![
1483 "first".to_string(),
1484 "second".to_string(),
1485 "third".to_string(),
1486 ];
1487 let options = FormatOptions::default();
1488 let expected = indoc!(
1489 "
1490 SELECT
1491 first,
1492 second,
1493 third;"
1494 );
1495
1496 assert_eq!(
1497 format(input, &QueryParams::Indexed(params), &options),
1498 expected
1499 );
1500 }
1501
1502 #[test]
1503 fn it_recognizes_dollar_sign_numbered_placeholders() {
1504 let input = "SELECT $1, $2;";
1505 let options = FormatOptions::default();
1506 let expected = indoc!(
1507 "
1508 SELECT
1509 $1,
1510 $2;"
1511 );
1512
1513 assert_eq!(format(input, &QueryParams::None, &options), expected);
1514 }
1515
1516 #[test]
1517 fn it_recognizes_dollar_sign_alphanumeric_placeholders() {
1518 let input = "SELECT $hash, $foo, $bar;";
1519 let options = FormatOptions::default();
1520 let expected = indoc!(
1521 "
1522 SELECT
1523 $hash,
1524 $foo,
1525 $bar;"
1526 );
1527
1528 assert_eq!(format(input, &QueryParams::None, &options), expected);
1529 }
1530
1531 #[test]
1532 fn it_recognizes_dollar_sign_numbered_placeholders_with_param_values() {
1533 let input = "SELECT $2, $3, $1, $named, $4, $alias;";
1534 let params = vec![
1535 "first".to_string(),
1536 "second".to_string(),
1537 "third".to_string(),
1538 "4th".to_string(),
1539 ];
1540 let options = FormatOptions::default();
1541 let expected = indoc!(
1542 "
1543 SELECT
1544 second,
1545 third,
1546 first,
1547 $named,
1548 4th,
1549 $alias;"
1550 );
1551
1552 assert_eq!(
1553 format(input, &QueryParams::Indexed(params), &options),
1554 expected
1555 );
1556 }
1557
1558 #[test]
1559 fn it_recognizes_dollar_sign_alphanumeric_placeholders_with_param_values() {
1560 let input = "SELECT $hash, $salt, $1, $2;";
1561 let params = vec![
1562 ("hash".to_string(), "hash value".to_string()),
1563 ("salt".to_string(), "salt value".to_string()),
1564 ("1".to_string(), "number 1".to_string()),
1565 ("2".to_string(), "number 2".to_string()),
1566 ];
1567 let options = FormatOptions::default();
1568 let expected = indoc!(
1569 "
1570 SELECT
1571 hash value,
1572 salt value,
1573 number 1,
1574 number 2;"
1575 );
1576
1577 assert_eq!(
1578 format(input, &QueryParams::Named(params), &options),
1579 expected
1580 );
1581 }
1582
1583 #[test]
1584 fn it_formats_query_with_go_batch_separator() {
1585 let input = "SELECT 1 GO SELECT 2";
1586 let params = vec![
1587 "first".to_string(),
1588 "second".to_string(),
1589 "third".to_string(),
1590 ];
1591 let options = FormatOptions::default();
1592 let expected = indoc!(
1593 "
1594 SELECT
1595 1
1596 GO
1597 SELECT
1598 2"
1599 );
1600
1601 assert_eq!(
1602 format(input, &QueryParams::Indexed(params), &options),
1603 expected
1604 );
1605 }
1606
1607 #[test]
1608 fn it_formats_select_query_with_cross_join() {
1609 let input = "SELECT a, b FROM t CROSS JOIN t2 on t.id = t2.id_t";
1610 let options = FormatOptions::default();
1611 let expected = indoc!(
1612 "
1613 SELECT
1614 a,
1615 b
1616 FROM
1617 t
1618 CROSS JOIN t2 on t.id = t2.id_t"
1619 );
1620
1621 assert_eq!(format(input, &QueryParams::None, &options), expected);
1622 }
1623
1624 #[test]
1625 fn it_formats_select_query_with_cross_apply() {
1626 let input = "SELECT a, b FROM t CROSS APPLY fn(t.id)";
1627 let options = FormatOptions::default();
1628 let expected = indoc!(
1629 "
1630 SELECT
1631 a,
1632 b
1633 FROM
1634 t
1635 CROSS APPLY fn(t.id)"
1636 );
1637
1638 assert_eq!(format(input, &QueryParams::None, &options), expected);
1639 }
1640
1641 #[test]
1642 fn it_formats_simple_select() {
1643 let input = "SELECT N, M FROM t";
1644 let options = FormatOptions::default();
1645 let expected = indoc!(
1646 "
1647 SELECT
1648 N,
1649 M
1650 FROM
1651 t"
1652 );
1653
1654 assert_eq!(format(input, &QueryParams::None, &options), expected);
1655 }
1656
1657 #[test]
1658 fn it_formats_simple_select_with_national_characters_mssql() {
1659 let input = "SELECT N'value'";
1660 let options = FormatOptions::default();
1661 let expected = indoc!(
1662 "
1663 SELECT
1664 N'value'"
1665 );
1666
1667 assert_eq!(format(input, &QueryParams::None, &options), expected);
1668 }
1669
1670 #[test]
1671 fn it_formats_select_query_with_outer_apply() {
1672 let input = "SELECT a, b FROM t OUTER APPLY fn(t.id)";
1673 let options = FormatOptions::default();
1674 let expected = indoc!(
1675 "
1676 SELECT
1677 a,
1678 b
1679 FROM
1680 t
1681 OUTER APPLY fn(t.id)"
1682 );
1683
1684 assert_eq!(format(input, &QueryParams::None, &options), expected);
1685 }
1686
1687 #[test]
1688 fn it_formats_fetch_first_like_limit() {
1689 let input = "SELECT * FETCH FIRST 2 ROWS ONLY;";
1690 let options = FormatOptions::default();
1691 let expected = indoc!(
1692 "
1693 SELECT
1694 *
1695 FETCH FIRST
1696 2 ROWS ONLY;"
1697 );
1698
1699 assert_eq!(format(input, &QueryParams::None, &options), expected);
1700 }
1701
1702 #[test]
1703 fn it_formats_case_when_with_a_blank_expression() {
1704 let input = "CASE WHEN option = 'foo' THEN 1 WHEN option = 'bar' THEN 2 WHEN option = 'baz' THEN 3 ELSE 4 END;";
1705 let options = FormatOptions::default();
1706 let expected = indoc!(
1707 "
1708 CASE
1709 WHEN option = 'foo' THEN 1
1710 WHEN option = 'bar' THEN 2
1711 WHEN option = 'baz' THEN 3
1712 ELSE 4
1713 END;"
1714 );
1715
1716 assert_eq!(format(input, &QueryParams::None, &options), expected);
1717 }
1718
1719 #[test]
1720 fn it_formats_case_when_inside_select() {
1721 let input =
1722 "SELECT foo, bar, CASE baz WHEN 'one' THEN 1 WHEN 'two' THEN 2 ELSE 3 END FROM table";
1723 let options = FormatOptions::default();
1724 let expected = indoc!(
1725 "
1726 SELECT
1727 foo,
1728 bar,
1729 CASE
1730 baz
1731 WHEN 'one' THEN 1
1732 WHEN 'two' THEN 2
1733 ELSE 3
1734 END
1735 FROM
1736 table"
1737 );
1738
1739 assert_eq!(format(input, &QueryParams::None, &options), expected);
1740 }
1741
1742 #[test]
1743 fn it_formats_case_when_inside_select_inlined_top_level() {
1744 let input =
1745 "SELECT foo, bar, CASE baz WHEN 'one' THEN 1 WHEN 'two' THEN 2 ELSE 3 END FROM table";
1746 let options = FormatOptions {
1747 max_inline_top_level: Some(50),
1748 ..Default::default()
1749 };
1750 let expected = indoc!(
1751 "
1752 SELECT
1753 foo,
1754 bar,
1755 CASE
1756 baz
1757 WHEN 'one' THEN 1
1758 WHEN 'two' THEN 2
1759 ELSE 3
1760 END
1761 FROM table"
1762 );
1763
1764 assert_eq!(format(input, &QueryParams::None, &options), expected);
1765 }
1766
1767 #[test]
1768 fn it_formats_case_when_with_an_expression() {
1769 let input = "CASE toString(getNumber()) WHEN 'one' THEN 1 WHEN 'two' THEN 2 WHEN 'three' THEN 3 ELSE 4 END;";
1770 let options = FormatOptions::default();
1771 let expected = indoc!(
1772 "
1773 CASE
1774 toString(getNumber())
1775 WHEN 'one' THEN 1
1776 WHEN 'two' THEN 2
1777 WHEN 'three' THEN 3
1778 ELSE 4
1779 END;"
1780 );
1781
1782 assert_eq!(format(input, &QueryParams::None, &options), expected);
1783 }
1784
1785 #[test]
1786 fn it_formats_case_when_inside_an_order_by() {
1787 let input = "SELECT a, created_at FROM b ORDER BY (CASE $3 WHEN 'created_at_asc' THEN created_at END) ASC, (CASE $3 WHEN 'created_at_desc' THEN created_at END) DESC;";
1788 let max_line = 80;
1789 let options = FormatOptions {
1790 max_inline_block: max_line,
1791 max_inline_arguments: Some(max_line),
1792 ..Default::default()
1793 };
1794
1795 let expected = indoc!(
1796 "
1797 SELECT
1798 a, created_at
1799 FROM
1800 b
1801 ORDER BY
1802 (CASE $3 WHEN 'created_at_asc' THEN created_at END) ASC,
1803 (CASE $3 WHEN 'created_at_desc' THEN created_at END) DESC;"
1804 );
1805
1806 assert_eq!(format(input, &QueryParams::None, &options), expected);
1807 }
1808
1809 #[test]
1810 fn it_recognizes_lowercase_case_end() {
1811 let input = "case when option = 'foo' then 1 else 2 end;";
1812 let options = FormatOptions::default();
1813 let expected = indoc!(
1814 "
1815 case
1816 when option = 'foo' then 1
1817 else 2
1818 end;"
1819 );
1820
1821 assert_eq!(format(input, &QueryParams::None, &options), expected);
1822 }
1823
1824 #[test]
1825 fn it_ignores_words_case_and_end_inside_other_strings() {
1826 let input = "SELECT CASEDATE, ENDDATE FROM table1;";
1827 let options = FormatOptions::default();
1828 let expected = indoc!(
1829 "
1830 SELECT
1831 CASEDATE,
1832 ENDDATE
1833 FROM
1834 table1;"
1835 );
1836
1837 assert_eq!(format(input, &QueryParams::None, &options), expected);
1838 }
1839
1840 #[test]
1841 fn it_formats_tricky_line_comments() {
1842 let input = "SELECT a#comment, here\nFROM b--comment";
1843 let options = FormatOptions::default();
1844 let expected = indoc!(
1845 "
1846 SELECT
1847 a #comment, here
1848 FROM
1849 b --comment"
1850 );
1851
1852 assert_eq!(format(input, &QueryParams::None, &options), expected);
1853 }
1854
1855 #[test]
1856 fn it_formats_line_comments_followed_by_semicolon() {
1857 let input = indoc!(
1858 "
1859 SELECT a FROM b
1860 --comment
1861 ;"
1862 );
1863 let options = FormatOptions::default();
1864 let expected = indoc!(
1865 "
1866 SELECT
1867 a
1868 FROM
1869 b --comment
1870 ;"
1871 );
1872
1873 assert_eq!(format(input, &QueryParams::None, &options), expected);
1874 }
1875
1876 #[test]
1877 fn it_formats_line_comments_followed_by_comma() {
1878 let input = indoc!(
1879 "
1880 SELECT a --comment
1881 , b"
1882 );
1883 let options = FormatOptions::default();
1884 let expected = indoc!(
1885 "
1886 SELECT
1887 a --comment
1888 ,
1889 b"
1890 );
1891
1892 assert_eq!(format(input, &QueryParams::None, &options), expected);
1893 }
1894
1895 #[test]
1896 fn it_formats_line_comments_followed_by_close_paren() {
1897 let input = "SELECT ( a --comment\n )";
1898 let options = FormatOptions::default();
1899 let expected = indoc!(
1900 "
1901 SELECT
1902 (
1903 a --comment
1904 )"
1905 );
1906
1907 assert_eq!(format(input, &QueryParams::None, &options), expected);
1908 }
1909
1910 #[test]
1911 fn it_formats_line_comments_followed_by_open_paren() {
1912 let input = "SELECT a --comment\n()";
1913 let options = FormatOptions::default();
1914 let expected = indoc!(
1915 "
1916 SELECT
1917 a --comment
1918 ()"
1919 );
1920
1921 assert_eq!(format(input, &QueryParams::None, &options), expected);
1922 }
1923
1924 #[test]
1925 fn it_formats_lonely_semicolon() {
1926 let input = ";";
1927 let options = FormatOptions::default();
1928
1929 assert_eq!(format(input, &QueryParams::None, &options), input);
1930 }
1931
1932 #[test]
1933 fn it_formats_multibyte_chars() {
1934 let input = "\nSELECT 'главная'";
1935 let options = FormatOptions::default();
1936 let expected = "SELECT\n 'главная'";
1937
1938 assert_eq!(format(input, &QueryParams::None, &options), expected);
1939 }
1940
1941 #[test]
1942 fn it_recognizes_scientific_notation() {
1943 let input = "SELECT *, 1e-7 as small, 1e2 as medium, 1e+7 as large FROM t";
1944 let options = FormatOptions::default();
1945 let expected = indoc!(
1946 "
1947 SELECT
1948 *,
1949 1e-7 as small,
1950 1e2 as medium,
1951 1e+7 as large
1952 FROM
1953 t"
1954 );
1955
1956 assert_eq!(format(input, &QueryParams::None, &options), expected);
1957 }
1958
1959 #[test]
1960 fn it_keeps_double_dollar_signs_together() {
1961 let input = "CREATE FUNCTION abc() AS $$ SELECT * FROM table $$ LANGUAGE plpgsql;";
1962 let options = FormatOptions::default();
1963 let expected = indoc!(
1964 "
1965 CREATE FUNCTION abc() AS
1966 $$
1967 SELECT
1968 *
1969 FROM
1970 table
1971 $$
1972 LANGUAGE plpgsql;"
1973 );
1974
1975 assert_eq!(format(input, &QueryParams::None, &options), expected);
1976 }
1977
1978 #[test]
1979 fn it_formats_pgplsql() {
1980 let input = "CREATE FUNCTION abc() AS $$ DECLARE a int := 1; b int := 2; BEGIN SELECT * FROM table $$ LANGUAGE plpgsql;";
1981 let options = FormatOptions::default();
1982 let expected = indoc!(
1983 "
1984 CREATE FUNCTION abc() AS
1985 $$
1986 DECLARE
1987 a int := 1;
1988 b int := 2;
1989 BEGIN
1990 SELECT
1991 *
1992 FROM
1993 table
1994 $$
1995 LANGUAGE plpgsql;"
1996 );
1997
1998 assert_eq!(format(input, &QueryParams::None, &options), expected);
1999 }
2000
2001 #[test]
2002 fn it_handles_comments_correctly() {
2003 let input = indoc!(
2004 "
2005 -- 创建一个外部表，存储销售数据
2006 CREATE EXTERNAL TABLE IF NOT EXISTS sales_data (
2007 -- 唯一标识订单ID
2008 order_id BIGINT COMMENT 'Unique identifier for the order',
2009
2010 -- 客户ID
2011 customer_id BIGINT COMMENT 'Unique identifier for the customer',
2012 )
2013 COMMENT 'Sales data table for storing transaction records';
2014
2015 -- 按销售日期和城市进行分区
2016 PARTITIONED BY (
2017 sale_year STRING COMMENT 'Year of the sale',
2018 sale_month STRING COMMENT 'Month of the sale'
2019 )
2020
2021 -- 设置数据存储位置
2022 LOCATION '/user/hive/warehouse/sales_data'
2023
2024 -- 使用 ORC 存储格式
2025 STORED AS ORC
2026
2027 -- 设置表的行格式
2028 ROW FORMAT DELIMITED
2029 FIELDS TERMINATED BY ','
2030 LINES TERMINATED BY '\n'
2031
2032 -- 设置表属性
2033 TBLPROPERTIES (
2034 'orc.compress' = 'SNAPPY', -- 使用SNAPPY压缩
2035 'transactional' = 'true', -- 启用事务支持
2036 'orc.create.index' = 'true', -- 创建索引
2037 'skip.header.line.count' = '1', -- 跳过CSV文件的第一行
2038 'external.table.purge' = 'true' -- 在删除表时自动清理数据
2039 );
2040
2041 -- 自动加载数据到 Hive 分区中
2042 ALTER TABLE sales_data
2043 ADD PARTITION (sale_year = '2024', sale_month = '08')
2044 LOCATION '/user/hive/warehouse/sales_data/2024/08';"
2045 );
2046 let options = FormatOptions {
2047 indent: Indent::Spaces(4),
2048 ..Default::default()
2049 };
2050 let expected = indoc!(
2051 "
2052 -- 创建一个外部表，存储销售数据
2053 CREATE EXTERNAL TABLE IF NOT EXISTS sales_data (
2054 -- 唯一标识订单ID
2055 order_id BIGINT COMMENT 'Unique identifier for the order',
2056 -- 客户ID
2057 customer_id BIGINT COMMENT 'Unique identifier for the customer',
2058 ) COMMENT 'Sales data table for storing transaction records';
2059 -- 按销售日期和城市进行分区
2060 PARTITIONED BY (
2061 sale_year STRING COMMENT 'Year of the sale',
2062 sale_month STRING COMMENT 'Month of the sale'
2063 )
2064 -- 设置数据存储位置
2065 LOCATION '/user/hive/warehouse/sales_data'
2066 -- 使用 ORC 存储格式
2067 STORED AS ORC
2068 -- 设置表的行格式
2069 ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
2070 -- 设置表属性
2071 TBLPROPERTIES (
2072 'orc.compress' = 'SNAPPY', -- 使用SNAPPY压缩
2073 'transactional' = 'true', -- 启用事务支持
2074 'orc.create.index' = 'true', -- 创建索引
2075 'skip.header.line.count' = '1', -- 跳过CSV文件的第一行
2076 'external.table.purge' = 'true' -- 在删除表时自动清理数据
2077 );
2078 -- 自动加载数据到 Hive 分区中
2079 ALTER TABLE
2080 sales_data
2081 ADD
2082 PARTITION (sale_year = '2024', sale_month = '08') LOCATION '/user/hive/warehouse/sales_data/2024/08';"
2083 );
2084
2085 assert_eq!(format(input, &QueryParams::None, &options), expected);
2086 }
2087
2088 #[test]
2089 fn it_recognizes_returning_clause() {
2090 let input = indoc!(
2091 "
2092 INSERT INTO
2093 users (name, email)
2094 VALUES
2095 ($1, $2) RETURNING name,
2096 email"
2097 );
2098 let options = FormatOptions::default();
2099 let expected = indoc!(
2100 "
2101 INSERT INTO
2102 users (name, email)
2103 VALUES
2104 ($1, $2)
2105 RETURNING
2106 name,
2107 email"
2108 );
2109
2110 assert_eq!(format(input, &QueryParams::None, &options), expected);
2111 }
2112
2113 #[test]
2114 fn it_recognizes_on_update_clause() {
2115 let input = indoc!(
2116 "CREATE TABLE a (b integer REFERENCES c (id) ON UPDATE RESTRICT, other integer);"
2117 );
2118 let options = FormatOptions::default();
2119 let expected = indoc!(
2120 "
2121 CREATE TABLE a (
2122 b integer REFERENCES c (id) ON UPDATE RESTRICT,
2123 other integer
2124 );"
2125 );
2126 assert_eq!(format(input, &QueryParams::None, &options), expected);
2127 }
2128
2129 #[test]
2130 fn it_formats_except_on_columns() {
2131 let input = indoc!(
2132 "SELECT table_0.* EXCEPT (profit),
2133 details.* EXCEPT (item_id),
2134 table_0.profit
2135 FROM table_0"
2136 );
2137 let options = FormatOptions {
2138 indent: Indent::Spaces(4),
2139 ..Default::default()
2140 };
2141 let expected = indoc!(
2142 "
2143 SELECT
2144 table_0.* EXCEPT (profit),
2145 details.* EXCEPT (item_id),
2146 table_0.profit
2147 FROM
2148 table_0"
2149 );
2150
2151 assert_eq!(format(input, &QueryParams::None, &options), expected);
2152 }
2153
2154 #[test]
2155 fn it_uses_given_ignore_case_convert_config() {
2156 let input = "select count(*),Column1 from Table1;";
2157 let options = FormatOptions {
2158 uppercase: Some(true),
2159 ignore_case_convert: Some(vec!["from"]),
2160 ..FormatOptions::default()
2161 };
2162 let expected = indoc!(
2163 "
2164 SELECT
2165 count(*),
2166 Column1
2167 from
2168 Table1;"
2169 );
2170
2171 assert_eq!(format(input, &QueryParams::None, &options), expected);
2172 }
2173
2174 #[test]
2175 fn it_recognizes_fmt_off() {
2176 let input = indoc!(
2177 "SELECT * FROM sometable
2178 WHERE
2179 -- comment test here
2180 -- fmt: off
2181 first_key.second_key = 1
2182 -- json:first_key.second_key = 1
2183 -- fmt: on
2184 AND
2185 -- fm1t: off
2186 first_key.second_key = 1
2187 -- json:first_key.second_key = 1
2188 -- fmt:on"
2189 );
2190 let options = FormatOptions {
2191 indent: Indent::Spaces(4),
2192 ..Default::default()
2193 };
2194 let expected = indoc!(
2195 "
2196 SELECT
2197 *
2198 FROM
2199 sometable
2200 WHERE
2201 -- comment test here
2202 first_key.second_key = 1
2203 -- json:first_key.second_key = 1
2204 AND
2205 -- fm1t: off
2206 first_key.second_key = 1
2207 -- json:first_key.second_key = 1"
2208 );
2209
2210 assert_eq!(format(input, &QueryParams::None, &options), expected);
2211 }
2212
2213 #[test]
2214 fn it_converts_keywords_to_lowercase_when_option_passed_in() {
2215 let input = "select distinct * frOM foo left join bar WHERe cola > 1 and colb = 3";
2216 let options = FormatOptions {
2217 uppercase: Some(false),
2218 ..FormatOptions::default()
2219 };
2220 let expected = indoc!(
2221 "
2222 select distinct
2223 *
2224 from
2225 foo
2226 left join bar
2227 where
2228 cola > 1
2229 and colb = 3"
2230 );
2231
2232 assert_eq!(format(input, &QueryParams::None, &options), expected);
2233 }
2234
2235 #[test]
2236 fn format_nested_select() {
2237 let input = "WITH a AS ( SELECT a, b, c FROM t WHERE a > 100 ), aa AS ( SELECT field FROM table ) SELECT b, field FROM a, aa;";
2238 let options = FormatOptions {
2239 max_inline_arguments: Some(10),
2240 max_inline_top_level: Some(9),
2241 ..Default::default()
2242 };
2243 let expected = indoc! {
2244 "
2245 WITH
2246 a AS (
2247 SELECT a, b, c
2248 FROM t
2249 WHERE a > 100
2250 ),
2251 aa AS (
2252 SELECT field
2253 FROM table
2254 )
2255 SELECT
2256 b,
2257 field
2258 FROM a, aa;"
2259 };
2260 assert_eq!(format(input, &QueryParams::None, &options), expected);
2261 }
2262
2263 #[test]
2264 fn format_short_with() {
2265 let input = "WITH a AS ( SELECT a, b, c FROM t WHERE a > 100 ) SELECT b, field FROM a, aa;";
2266 let max_line = 80;
2267 let options = FormatOptions {
2268 max_inline_block: max_line,
2269 max_inline_arguments: Some(max_line),
2270 max_inline_top_level: Some(max_line),
2271 joins_as_top_level: true,
2272 ..Default::default()
2273 };
2274 let expected = indoc! {
2275 "
2276 WITH a AS (SELECT a, b, c FROM t WHERE a > 100)
2277 SELECT b, field
2278 FROM a, aa;"
2279 };
2280 assert_eq!(format(input, &QueryParams::None, &options), expected);
2281 }
2282
2283 #[test]
2284 fn format_nested_select_nested_blocks() {
2285 let input =
2286 "WITH a AS ( SELECT a, b, c FROM t WHERE a > 100 ), aa AS ( SELECT field FROM table ),
2287 bb AS ( SELECT count(*) as c FROM d ), cc AS ( INSERT INTO C (a, b, c, d) VALUES (1 ,2 ,3 ,4) )
2288 SELECT b, field FROM a, aa;";
2289 let max_line = 20;
2290 let options = FormatOptions {
2291 max_inline_block: max_line,
2292 max_inline_arguments: Some(max_line),
2293 max_inline_top_level: Some(max_line / 2),
2294 joins_as_top_level: true,
2295 ..Default::default()
2296 };
2297 let expected = indoc! {
2298 "
2299 WITH
2300 a AS (
2301 SELECT a, b, c
2302 FROM t
2303 WHERE a > 100
2304 ),
2305 aa AS (
2306 SELECT field
2307 FROM table
2308 ),
2309 bb AS (
2310 SELECT
2311 count(*) as c
2312 FROM d
2313 ),
2314 cc AS (
2315 INSERT INTO
2316 C (a, b, c, d)
2317 VALUES
2318 (1, 2, 3, 4)
2319 )
2320 SELECT b, field
2321 FROM a, aa;"
2322 };
2323 assert_eq!(format(input, &QueryParams::None, &options), expected);
2324 }
2325
2326 #[test]
2327 fn it_converts_keywords_nothing_when_no_option_passed_in() {
2328 let input = "select distinct * frOM foo left join bar WHERe cola > 1 and colb = 3";
2329 let options = FormatOptions {
2330 uppercase: None,
2331 ..FormatOptions::default()
2332 };
2333 let expected = indoc!(
2334 "
2335 select distinct
2336 *
2337 frOM
2338 foo
2339 left join bar
2340 WHERe
2341 cola > 1
2342 and colb = 3"
2343 );
2344
2345 assert_eq!(format(input, &QueryParams::None, &options), expected);
2346 }
2347 #[test]
2348 fn it_correctly_parses_all_operators() {
2349 let operators = [
2350 "!!", "!~~*", "!~~", "!~*", "!~", "##", "#>>", "#>", "#-", "&<|", "&<", "&>", "&&",
2351 "*<>", "*<=", "*>=", "*>", "*=", "*<", "<<|", "<<=", "<<", "<->", "<@", "<^", "<=",
2352 "<>", "<", ">=", ">>=", ">>", ">^", "->>", "->", "-|-", "-", "+", "/", "=", "%", "?||",
2353 "?|", "?-|", "?-", "?#", "?&", "?", "@@@", "@@", "@>", "@?", "@-@", "@", "^@", "^",
2354 "|&>", "|>>", "|/", "|", "||/", "||", "~>=~", "~>~", "~<=~", "~<~", "~=", "~*", "~~*",
2355 "~~", "~", "%", "<%", "%>", "<<%", "%>>", "<<->", "<->>", "<<<->", "<->>>",
2356 ];
2357
2358 for &operator in &operators {
2360 let input = format!("left {} right", operator);
2361 let expected = format!("left {} right", operator);
2362 let options = FormatOptions {
2363 uppercase: None,
2364 ..FormatOptions::default()
2365 };
2366
2367 assert_eq!(
2368 format(&input, &QueryParams::None, &options),
2369 expected,
2370 "Failed to parse operator: {}",
2371 operator
2372 );
2373 }
2374 }
2375 #[test]
2376 fn it_correctly_splits_operators() {
2377 let input = "
2378 SELECT
2379 left <@ right,
2380 left << right,
2381 left >> right,
2382 left &< right,
2383 left &> right,
2384 left -|- right,
2385 @@ left,
2386 @-@ left,
2387 left <-> right,
2388 left <<| right,
2389 left |>> right,
2390 left &<| right,
2391 left |>& right,
2392 left <^ right,
2393 left >^ right,
2394 left <% right,
2395 left %> right,
2396 ?- left,
2397 left ?-| right,
2398 left ?|| right,
2399 left ~= right";
2400 let options = FormatOptions {
2401 uppercase: None,
2402 ..FormatOptions::default()
2403 };
2404 let expected = indoc!(
2405 "
2406SELECT
2407 left <@ right,
2408 left << right,
2409 left >> right,
2410 left &< right,
2411 left &> right,
2412 left -|- right,
2413 @@ left,
2414 @-@ left,
2415 left <-> right,
2416 left <<| right,
2417 left |>> right,
2418 left &<| right,
2419 left |>& right,
2420 left <^ right,
2421 left >^ right,
2422 left <% right,
2423 left %> right,
2424 ?- left,
2425 left ?-| right,
2426 left ?|| right,
2427 left ~= right"
2428 );
2429
2430 assert_eq!(format(input, &QueryParams::None, &options), expected);
2431 }
2432 #[test]
2433 fn it_formats_double_colons() {
2434 let input = "select text :: text, num::integer, data::json, (x - y)::integer frOM foo";
2435 let options = FormatOptions {
2436 uppercase: Some(false),
2437 ..FormatOptions::default()
2438 };
2439 let expected = indoc!(
2440 "
2441select
2442 text::text,
2443 num::integer,
2444 data::json,
2445 (x - y)::integer
2446from
2447 foo"
2448 );
2449
2450 assert_eq!(format(input, &QueryParams::None, &options), expected);
2451 }
2452
2453 #[test]
2454 fn it_formats_blocks_inline_or_not() {
2455 let input = " UPDATE t
2456
2457
2458 SET o = ($5 + $6 + $7 + $8),a = CASE WHEN $2
2459 THEN NULL ELSE COALESCE($3, b) END, b = CASE WHEN $4 THEN NULL ELSE
2460 COALESCE($5, b) END, s = (SELECT true FROM bar WHERE bar.foo = $99 AND bar.foo > $100),
2461 c = CASE WHEN $6 THEN NULL ELSE COALESCE($7, c) END,
2462 d = CASE WHEN $8 THEN NULL ELSE COALESCE($9, dddddddd) + bbbbb END,
2463 e = (SELECT true FROM bar) WHERE id = $1";
2464 let options = FormatOptions {
2465 max_inline_arguments: Some(60),
2466 max_inline_block: 60,
2467 max_inline_top_level: Some(60),
2468 ..Default::default()
2469 };
2470 let expected = indoc!(
2471 "
2472 UPDATE t SET
2473 o = ($5 + $6 + $7 + $8),
2474 a = CASE WHEN $2 THEN NULL ELSE COALESCE($3, b) END,
2475 b = CASE WHEN $4 THEN NULL ELSE COALESCE($5, b) END,
2476 s = (
2477 SELECT true
2478 FROM bar
2479 WHERE bar.foo = $99
2480 AND bar.foo > $100
2481 ),
2482 c = CASE WHEN $6 THEN NULL ELSE COALESCE($7, c) END,
2483 d = CASE
2484 WHEN $8 THEN NULL
2485 ELSE COALESCE($9, dddddddd) + bbbbb
2486 END,
2487 e = (SELECT true FROM bar)
2488 WHERE id = $1"
2489 );
2490
2491 assert_eq!(format(input, &QueryParams::None, &options), expected);
2492 }
2493
2494 #[test]
2495 fn parse_union_all() {
2496 let input = "SELECT id FROM a UNION ALL SELECT id FROM b WHERE c = $12 AND f";
2497 let options = FormatOptions::default();
2498 let expected = indoc!(
2499 "
2500 SELECT
2501 id
2502 FROM
2503 a
2504 UNION ALL
2505 SELECT
2506 id
2507 FROM
2508 b
2509 WHERE
2510 c = $12
2511 AND f"
2512 );
2513 assert_eq!(format(input, &QueryParams::None, &options), expected);
2514 }
2515}