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