sqlformat/
lib.rs

1//! This crate is a port of https://github.com/kufii/sql-formatter-plus
2//! written in Rust. It is intended to be usable as a pure-Rust library
3//! for formatting SQL queries.
4
5#![type_length_limit = "99999999"]
6#![forbid(unsafe_code)]
7// Maintains semver compatibility for older Rust versions
8#![allow(clippy::manual_strip)]
9
10mod formatter;
11mod indentation;
12mod inline_block;
13mod params;
14mod tokenizer;
15
16/// Formats whitespace in a SQL string to make it easier to read.
17/// Optionally replaces parameter placeholders with `params`.
18pub fn format(query: &str, params: &QueryParams, options: FormatOptions) -> String {
19    let named_placeholders = matches!(params, QueryParams::Named(_));
20
21    let tokens = tokenizer::tokenize(query, named_placeholders);
22    formatter::format(&tokens, params, options)
23}
24
25/// Options for controlling how the library formats SQL
26#[derive(Debug, Clone, Copy)]
27pub struct FormatOptions {
28    /// Controls the type and length of indentation to use
29    ///
30    /// Default: 2 spaces
31    pub indent: Indent,
32    /// When set, changes reserved keywords to ALL CAPS
33    ///
34    /// Default: false
35    pub uppercase: bool,
36    /// Controls the number of line breaks after a query
37    ///
38    /// Default: 1
39    pub lines_between_queries: u8,
40}
41
42impl Default for FormatOptions {
43    fn default() -> Self {
44        FormatOptions {
45            indent: Indent::Spaces(2),
46            uppercase: false,
47            lines_between_queries: 1,
48        }
49    }
50}
51
52#[derive(Debug, Clone, Copy)]
53pub enum Indent {
54    Spaces(u8),
55    Tabs,
56}
57
58#[derive(Debug, Clone, Default)]
59pub enum QueryParams {
60    Named(Vec<(String, String)>),
61    Indexed(Vec<String>),
62    #[default]
63    None,
64}
65
66#[cfg(test)]
67mod tests {
68    use super::*;
69    use indoc::indoc;
70
71    #[test]
72    fn it_uses_given_indent_config_for_indentation() {
73        let input = "SELECT count(*),Column1 FROM Table1;";
74        let options = FormatOptions {
75            indent: Indent::Spaces(4),
76            ..FormatOptions::default()
77        };
78        let expected = indoc!(
79            "
80            SELECT
81                count(*),
82                Column1
83            FROM
84                Table1;"
85        );
86
87        assert_eq!(format(input, &QueryParams::None, options), expected);
88    }
89
90    #[test]
91    fn it_formats_simple_set_schema_queries() {
92        let input = "SET SCHEMA schema1; SET CURRENT SCHEMA schema2;";
93        let options = FormatOptions::default();
94        let expected = indoc!(
95            "
96            SET SCHEMA
97              schema1;
98            SET CURRENT SCHEMA
99              schema2;"
100        );
101
102        assert_eq!(format(input, &QueryParams::None, options), expected);
103    }
104
105    #[test]
106    fn it_formats_simple_select_query() {
107        let input = "SELECT count(*),Column1 FROM Table1;";
108        let options = FormatOptions::default();
109        let expected = indoc!(
110            "
111            SELECT
112              count(*),
113              Column1
114            FROM
115              Table1;"
116        );
117
118        assert_eq!(format(input, &QueryParams::None, options), expected);
119    }
120
121    #[test]
122    fn it_formats_complex_select() {
123        let input =
124            "SELECT DISTINCT name, ROUND(age/7) field1, 18 + 20 AS field2, 'some string' FROM foo;";
125        let options = FormatOptions::default();
126        let expected = indoc!(
127            "
128            SELECT
129              DISTINCT name,
130              ROUND(age / 7) field1,
131              18 + 20 AS field2,
132              'some string'
133            FROM
134              foo;"
135        );
136
137        assert_eq!(format(input, &QueryParams::None, options), expected);
138    }
139
140    #[test]
141    fn it_formats_select_with_complex_where() {
142        let input = indoc!(
143            "
144            SELECT * FROM foo WHERE Column1 = 'testing'
145            AND ( (Column2 = Column3 OR Column4 >= NOW()) );
146      "
147        );
148        let options = FormatOptions::default();
149        let expected = indoc!(
150            "
151            SELECT
152              *
153            FROM
154              foo
155            WHERE
156              Column1 = 'testing'
157              AND (
158                (
159                  Column2 = Column3
160                  OR Column4 >= NOW()
161                )
162              );"
163        );
164
165        assert_eq!(format(input, &QueryParams::None, options), expected);
166    }
167
168    #[test]
169    fn it_formats_select_with_top_level_reserved_words() {
170        let input = indoc!(
171            "
172            SELECT * FROM foo WHERE name = 'John' GROUP BY some_column
173            HAVING column > 10 ORDER BY other_column LIMIT 5;
174      "
175        );
176        let options = FormatOptions::default();
177        let expected = indoc!(
178            "
179            SELECT
180              *
181            FROM
182              foo
183            WHERE
184              name = 'John'
185            GROUP BY
186              some_column
187            HAVING
188              column > 10
189            ORDER BY
190              other_column
191            LIMIT
192              5;"
193        );
194
195        assert_eq!(format(input, &QueryParams::None, options), expected);
196    }
197
198    #[test]
199    fn it_formats_limit_with_two_comma_separated_values_on_single_line() {
200        let input = "LIMIT 5, 10;";
201        let options = FormatOptions::default();
202        let expected = indoc!(
203            "
204            LIMIT
205              5, 10;"
206        );
207
208        assert_eq!(format(input, &QueryParams::None, options), expected);
209    }
210
211    #[test]
212    fn it_formats_limit_of_single_value_followed_by_another_select_using_commas() {
213        let input = "LIMIT 5; SELECT foo, bar;";
214        let options = FormatOptions::default();
215        let expected = indoc!(
216            "
217            LIMIT
218              5;
219            SELECT
220              foo,
221              bar;"
222        );
223
224        assert_eq!(format(input, &QueryParams::None, options), expected);
225    }
226
227    #[test]
228    fn it_formats_limit_of_single_value_and_offset() {
229        let input = "LIMIT 5 OFFSET 8;";
230        let options = FormatOptions::default();
231        let expected = indoc!(
232            "
233            LIMIT
234              5 OFFSET 8;"
235        );
236
237        assert_eq!(format(input, &QueryParams::None, options), expected);
238    }
239
240    #[test]
241    fn it_recognizes_limit_in_lowercase() {
242        let input = "limit 5, 10;";
243        let options = FormatOptions::default();
244        let expected = indoc!(
245            "
246            limit
247              5, 10;"
248        );
249
250        assert_eq!(format(input, &QueryParams::None, options), expected);
251    }
252
253    #[test]
254    fn it_preserves_case_of_keywords() {
255        let input = "select distinct * frOM foo left join bar WHERe a > 1 and b = 3";
256        let options = FormatOptions::default();
257        let expected = indoc!(
258            "
259            select
260              distinct *
261            frOM
262              foo
263              left join bar
264            WHERe
265              a > 1
266              and b = 3"
267        );
268
269        assert_eq!(format(input, &QueryParams::None, options), expected);
270    }
271
272    #[test]
273    fn it_formats_select_query_with_select_query_inside_it() {
274        let input = "SELECT *, SUM(*) AS sum FROM (SELECT * FROM Posts LIMIT 30) WHERE a > b";
275        let options = FormatOptions::default();
276        let expected = indoc!(
277            "
278            SELECT
279              *,
280              SUM(*) AS sum
281            FROM
282              (
283                SELECT
284                  *
285                FROM
286                  Posts
287                LIMIT
288                  30
289              )
290            WHERE
291              a > b"
292        );
293
294        assert_eq!(format(input, &QueryParams::None, options), expected);
295    }
296
297    #[test]
298    fn it_formats_select_query_with_inner_join() {
299        let input = indoc!(
300            "
301            SELECT customer_id.from, COUNT(order_id) AS total FROM customers
302            INNER JOIN orders ON customers.customer_id = orders.customer_id;"
303        );
304        let options = FormatOptions::default();
305        let expected = indoc!(
306            "
307            SELECT
308              customer_id.from,
309              COUNT(order_id) AS total
310            FROM
311              customers
312              INNER JOIN orders ON customers.customer_id = orders.customer_id;"
313        );
314
315        assert_eq!(format(input, &QueryParams::None, options), expected);
316    }
317
318    #[test]
319    fn it_formats_select_query_with_different_comments() {
320        let input = indoc!(
321            "
322            SELECT
323            /*
324             * This is a block comment
325             */
326            * FROM
327            -- This is another comment
328            MyTable # One final comment
329            WHERE 1 = 2;"
330        );
331        let options = FormatOptions::default();
332        let expected = indoc!(
333            "
334            SELECT
335              /*
336               * This is a block comment
337               */
338              *
339            FROM
340              -- This is another comment
341              MyTable # One final comment
342            WHERE
343              1 = 2;"
344        );
345
346        assert_eq!(format(input, &QueryParams::None, options), expected);
347    }
348
349    #[test]
350    fn it_maintains_block_comment_indentation() {
351        let input = indoc!(
352            "
353            SELECT
354              /*
355               * This is a block comment
356               */
357              *
358            FROM
359              MyTable
360            WHERE
361              1 = 2;"
362        );
363        let options = FormatOptions::default();
364
365        assert_eq!(format(input, &QueryParams::None, options), input);
366    }
367
368    #[test]
369    fn it_formats_simple_insert_query() {
370        let input = "INSERT INTO Customers (ID, MoneyBalance, Address, City) VALUES (12,-123.4, 'Skagen 2111','Stv');";
371        let options = FormatOptions::default();
372        let expected = indoc!(
373            "
374            INSERT INTO
375              Customers (ID, MoneyBalance, Address, City)
376            VALUES
377              (12, -123.4, 'Skagen 2111', 'Stv');"
378        );
379
380        assert_eq!(format(input, &QueryParams::None, options), expected);
381    }
382
383    #[test]
384    fn it_keeps_short_parenthesized_list_with_nested_parenthesis_on_single_line() {
385        let input = "SELECT (a + b * (c - NOW()));";
386        let options = FormatOptions::default();
387        let expected = indoc!(
388            "
389            SELECT
390              (a + b * (c - NOW()));"
391        );
392
393        assert_eq!(format(input, &QueryParams::None, options), expected);
394    }
395
396    #[test]
397    fn it_breaks_long_parenthesized_lists_to_multiple_lines() {
398        let input = indoc!(
399            "
400            INSERT INTO some_table (id_product, id_shop, id_currency, id_country, id_registration) (
401            SELECT IF(dq.id_discounter_shopping = 2, dq.value, dq.value / 100),
402            IF (dq.id_discounter_shopping = 2, 'amount', 'percentage') FROM foo);"
403        );
404        let options = FormatOptions::default();
405        let expected = indoc!(
406            "
407            INSERT INTO
408              some_table (
409                id_product,
410                id_shop,
411                id_currency,
412                id_country,
413                id_registration
414              ) (
415                SELECT
416                  IF(
417                    dq.id_discounter_shopping = 2,
418                    dq.value,
419                    dq.value / 100
420                  ),
421                  IF (
422                    dq.id_discounter_shopping = 2,
423                    'amount',
424                    'percentage'
425                  )
426                FROM
427                  foo
428              );"
429        );
430
431        assert_eq!(format(input, &QueryParams::None, options), expected);
432    }
433
434    #[test]
435    fn it_formats_simple_update_query() {
436        let input = "UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste';";
437        let options = FormatOptions::default();
438        let expected = indoc!(
439            "
440            UPDATE
441              Customers
442            SET
443              ContactName = 'Alfred Schmidt',
444              City = 'Hamburg'
445            WHERE
446              CustomerName = 'Alfreds Futterkiste';"
447        );
448
449        assert_eq!(format(input, &QueryParams::None, options), expected);
450    }
451
452    #[test]
453    fn it_formats_simple_delete_query() {
454        let input = "DELETE FROM Customers WHERE CustomerName='Alfred' AND Phone=5002132;";
455        let options = FormatOptions::default();
456        let expected = indoc!(
457            "
458            DELETE FROM
459              Customers
460            WHERE
461              CustomerName = 'Alfred'
462              AND Phone = 5002132;"
463        );
464
465        assert_eq!(format(input, &QueryParams::None, options), expected);
466    }
467
468    #[test]
469    fn it_formats_simple_drop_query() {
470        let input = "DROP TABLE IF EXISTS admin_role;";
471        let options = FormatOptions::default();
472
473        assert_eq!(format(input, &QueryParams::None, options), input);
474    }
475
476    #[test]
477    fn it_formats_incomplete_query() {
478        let input = "SELECT count(";
479        let options = FormatOptions::default();
480        let expected = indoc!(
481            "
482            SELECT
483              count("
484        );
485
486        assert_eq!(format(input, &QueryParams::None, options), expected);
487    }
488
489    #[test]
490    fn it_formats_query_that_ends_with_open_comment() {
491        let input = indoc!(
492            "
493            SELECT count(*)
494            /*Comment"
495        );
496        let options = FormatOptions::default();
497        let expected = indoc!(
498            "
499            SELECT
500              count(*)
501              /*Comment"
502        );
503
504        assert_eq!(format(input, &QueryParams::None, options), expected);
505    }
506
507    #[test]
508    fn it_formats_update_query_with_as_part() {
509        let input = "UPDATE customers SET total_orders = order_summary.total  FROM ( SELECT * FROM bank) AS order_summary";
510        let options = FormatOptions::default();
511        let expected = indoc!(
512            "
513            UPDATE
514              customers
515            SET
516              total_orders = order_summary.total
517            FROM
518              (
519                SELECT
520                  *
521                FROM
522                  bank
523              ) AS order_summary"
524        );
525
526        assert_eq!(format(input, &QueryParams::None, options), expected);
527    }
528
529    #[test]
530    fn it_formats_top_level_and_newline_multi_word_reserved_words_with_inconsistent_spacing() {
531        let input = "SELECT * FROM foo LEFT \t OUTER  \n JOIN bar ORDER \n BY blah";
532        let options = FormatOptions::default();
533        let expected = indoc!(
534            "
535            SELECT
536              *
537            FROM
538              foo
539              LEFT OUTER JOIN bar
540            ORDER BY
541              blah"
542        );
543
544        assert_eq!(format(input, &QueryParams::None, options), expected);
545    }
546
547    #[test]
548    fn it_formats_long_double_parenthesized_queries_to_multiple_lines() {
549        let input = "((foo = '0123456789-0123456789-0123456789-0123456789'))";
550        let options = FormatOptions::default();
551        let expected = indoc!(
552            "
553            (
554              (
555                foo = '0123456789-0123456789-0123456789-0123456789'
556              )
557            )"
558        );
559
560        assert_eq!(format(input, &QueryParams::None, options), expected);
561    }
562
563    #[test]
564    fn it_formats_short_double_parenthesizes_queries_to_one_line() {
565        let input = "((foo = 'bar'))";
566        let options = FormatOptions::default();
567
568        assert_eq!(format(input, &QueryParams::None, options), input);
569    }
570
571    #[test]
572    fn it_formats_single_char_operators() {
573        let inputs = [
574            "foo = bar",
575            "foo < bar",
576            "foo > bar",
577            "foo + bar",
578            "foo - bar",
579            "foo * bar",
580            "foo / bar",
581            "foo % bar",
582        ];
583        let options = FormatOptions::default();
584        for input in &inputs {
585            assert_eq!(&format(input, &QueryParams::None, options), input);
586        }
587    }
588
589    #[test]
590    fn it_formats_multi_char_operators() {
591        let inputs = [
592            "foo != bar",
593            "foo <> bar",
594            "foo == bar",
595            "foo || bar",
596            "foo <= bar",
597            "foo >= bar",
598            "foo !< bar",
599            "foo !> bar",
600        ];
601        let options = FormatOptions::default();
602        for input in &inputs {
603            assert_eq!(&format(input, &QueryParams::None, options), input);
604        }
605    }
606
607    #[test]
608    fn it_formats_logical_operators() {
609        let inputs = [
610            "foo ALL bar",
611            "foo = ANY (1, 2, 3)",
612            "EXISTS bar",
613            "foo IN (1, 2, 3)",
614            "foo LIKE 'hello%'",
615            "foo IS NULL",
616            "UNIQUE foo",
617        ];
618        let options = FormatOptions::default();
619        for input in &inputs {
620            assert_eq!(&format(input, &QueryParams::None, options), input);
621        }
622    }
623
624    #[test]
625    fn it_formats_and_or_operators() {
626        let strings = [
627            ("foo BETWEEN bar AND baz", "foo BETWEEN bar AND baz"),
628            ("foo BETWEEN\nbar\nAND baz", "foo BETWEEN bar AND baz"),
629            ("foo AND bar", "foo\nAND bar"),
630            ("foo OR bar", "foo\nOR bar"),
631        ];
632        let options = FormatOptions::default();
633        for (input, output) in &strings {
634            assert_eq!(&format(input, &QueryParams::None, options), output);
635        }
636    }
637
638    #[test]
639    fn it_recognizes_strings() {
640        let inputs = ["\"foo JOIN bar\"", "'foo JOIN bar'", "`foo JOIN bar`"];
641        let options = FormatOptions::default();
642        for input in &inputs {
643            assert_eq!(&format(input, &QueryParams::None, options), input);
644        }
645    }
646
647    #[test]
648    fn it_recognizes_escaped_strings() {
649        let inputs = [
650            r#""foo \" JOIN bar""#,
651            r#"'foo \' JOIN bar'"#,
652            r#"`foo `` JOIN bar`"#,
653            r#"'foo '' JOIN bar'"#,
654            r#"'two households"'"#,
655            r#"'two households'''"#,
656            r#"E'alice'''"#,
657        ];
658        let options = FormatOptions::default();
659        for input in &inputs {
660            assert_eq!(&format(input, &QueryParams::None, options), input);
661        }
662    }
663
664    #[test]
665    fn it_formats_postgres_specific_operators() {
666        let strings = [
667            ("column::int", "column :: int"),
668            ("v->2", "v -> 2"),
669            ("v->>2", "v ->> 2"),
670            ("foo ~~ 'hello'", "foo ~~ 'hello'"),
671            ("foo !~ 'hello'", "foo !~ 'hello'"),
672            ("foo ~* 'hello'", "foo ~* 'hello'"),
673            ("foo ~~* 'hello'", "foo ~~* 'hello'"),
674            ("foo !~~ 'hello'", "foo !~~ 'hello'"),
675            ("foo !~* 'hello'", "foo !~* 'hello'"),
676            ("foo !~~* 'hello'", "foo !~~* 'hello'"),
677        ];
678        let options = FormatOptions::default();
679        for (input, output) in &strings {
680            assert_eq!(&format(input, &QueryParams::None, options), output);
681        }
682    }
683
684    #[test]
685    fn it_keeps_separation_between_multiple_statements() {
686        let strings = [
687            ("foo;bar;", "foo;\nbar;"),
688            ("foo\n;bar;", "foo;\nbar;"),
689            ("foo\n\n\n;bar;\n\n", "foo;\nbar;"),
690        ];
691        let options = FormatOptions::default();
692        for (input, output) in &strings {
693            assert_eq!(&format(input, &QueryParams::None, options), output);
694        }
695
696        let input = indoc!(
697            "
698            SELECT count(*),Column1 FROM Table1;
699            SELECT count(*),Column1 FROM Table2;"
700        );
701        let options = FormatOptions::default();
702        let expected = indoc!(
703            "
704            SELECT
705              count(*),
706              Column1
707            FROM
708              Table1;
709            SELECT
710              count(*),
711              Column1
712            FROM
713              Table2;"
714        );
715
716        assert_eq!(format(input, &QueryParams::None, options), expected);
717    }
718
719    #[test]
720    fn it_formats_unicode_correctly() {
721        let input = "SELECT test, тест FROM table;";
722        let options = FormatOptions::default();
723        let expected = indoc!(
724            "
725            SELECT
726              test,
727              тест
728            FROM
729              table;"
730        );
731
732        assert_eq!(format(input, &QueryParams::None, options), expected);
733    }
734
735    #[test]
736    fn it_converts_keywords_to_uppercase_when_option_passed_in() {
737        let input = "select distinct * frOM foo left join bar WHERe cola > 1 and colb = 3";
738        let options = FormatOptions {
739            uppercase: true,
740            ..FormatOptions::default()
741        };
742        let expected = indoc!(
743            "
744            SELECT
745              DISTINCT *
746            FROM
747              foo
748              LEFT JOIN bar
749            WHERE
750              cola > 1
751              AND colb = 3"
752        );
753
754        assert_eq!(format(input, &QueryParams::None, options), expected);
755    }
756
757    #[test]
758    fn it_line_breaks_between_queries_with_config() {
759        let input = "SELECT * FROM foo; SELECT * FROM bar;";
760        let options = FormatOptions {
761            lines_between_queries: 2,
762            ..FormatOptions::default()
763        };
764        let expected = indoc!(
765            "
766            SELECT
767              *
768            FROM
769              foo;
770
771            SELECT
772              *
773            FROM
774              bar;"
775        );
776
777        assert_eq!(format(input, &QueryParams::None, options), expected);
778    }
779
780    #[test]
781    fn it_correctly_indents_create_statement_after_select() {
782        let input = indoc!(
783            "
784            SELECT * FROM test;
785            CREATE TABLE TEST(id NUMBER NOT NULL, col1 VARCHAR2(20), col2 VARCHAR2(20));
786        "
787        );
788        let options = FormatOptions::default();
789        let expected = indoc!(
790            "
791            SELECT
792              *
793            FROM
794              test;
795            CREATE TABLE TEST(
796              id NUMBER NOT NULL,
797              col1 VARCHAR2(20),
798              col2 VARCHAR2(20)
799            );"
800        );
801
802        assert_eq!(format(input, &QueryParams::None, options), expected);
803    }
804
805    #[test]
806    fn it_formats_short_create_table() {
807        let input = "CREATE TABLE items (a INT PRIMARY KEY, b TEXT);";
808        let options = FormatOptions::default();
809
810        assert_eq!(format(input, &QueryParams::None, options), input);
811    }
812
813    #[test]
814    fn it_formats_long_create_table() {
815        let input =
816            "CREATE TABLE items (a INT PRIMARY KEY, b TEXT, c INT NOT NULL, d INT NOT NULL);";
817        let options = FormatOptions::default();
818        let expected = indoc!(
819            "
820            CREATE TABLE items (
821              a INT PRIMARY KEY,
822              b TEXT,
823              c INT NOT NULL,
824              d INT NOT NULL
825            );"
826        );
827
828        assert_eq!(format(input, &QueryParams::None, options), expected);
829    }
830
831    #[test]
832    fn it_formats_insert_without_into() {
833        let input =
834            "INSERT Customers (ID, MoneyBalance, Address, City) VALUES (12,-123.4, 'Skagen 2111','Stv');";
835        let options = FormatOptions::default();
836        let expected = indoc!(
837            "
838            INSERT
839              Customers (ID, MoneyBalance, Address, City)
840            VALUES
841              (12, -123.4, 'Skagen 2111', 'Stv');"
842        );
843
844        assert_eq!(format(input, &QueryParams::None, options), expected);
845    }
846
847    #[test]
848    fn it_formats_alter_table_modify_query() {
849        let input = "ALTER TABLE supplier MODIFY supplier_name char(100) NOT NULL;";
850        let options = FormatOptions::default();
851        let expected = indoc!(
852            "
853            ALTER TABLE
854              supplier
855            MODIFY
856              supplier_name char(100) NOT NULL;"
857        );
858
859        assert_eq!(format(input, &QueryParams::None, options), expected);
860    }
861
862    #[test]
863    fn it_formats_alter_table_alter_column_query() {
864        let input = "ALTER TABLE supplier ALTER COLUMN supplier_name VARCHAR(100) NOT NULL;";
865        let options = FormatOptions::default();
866        let expected = indoc!(
867            "
868            ALTER TABLE
869              supplier
870            ALTER COLUMN
871              supplier_name VARCHAR(100) NOT NULL;"
872        );
873
874        assert_eq!(format(input, &QueryParams::None, options), expected);
875    }
876
877    #[test]
878    fn it_recognizes_bracketed_strings() {
879        let inputs = ["[foo JOIN bar]", "[foo ]] JOIN bar]"];
880        let options = FormatOptions::default();
881        for input in &inputs {
882            assert_eq!(&format(input, &QueryParams::None, options), input);
883        }
884    }
885
886    #[test]
887    fn it_recognizes_at_variables() {
888        let input =
889            "SELECT @variable, @a1_2.3$, @'var name', @\"var name\", @`var name`, @[var name];";
890        let options = FormatOptions::default();
891        let expected = indoc!(
892            "
893            SELECT
894              @variable,
895              @a1_2.3$,
896              @'var name',
897              @\"var name\",
898              @`var name`,
899              @[var name];"
900        );
901
902        assert_eq!(format(input, &QueryParams::None, options), expected);
903    }
904
905    #[test]
906    fn it_recognizes_at_variables_with_param_values() {
907        let input =
908            "SELECT @variable, @a1_2.3$, @'var name', @\"var name\", @`var name`, @[var name], @'var\\name';";
909        let params = vec![
910            ("variable".to_string(), "\"variable value\"".to_string()),
911            ("a1_2.3$".to_string(), "'weird value'".to_string()),
912            ("var name".to_string(), "'var value'".to_string()),
913            ("var\\name".to_string(), "'var\\ value'".to_string()),
914        ];
915        let options = FormatOptions::default();
916        let expected = indoc!(
917            "
918            SELECT
919              \"variable value\",
920              'weird value',
921              'var value',
922              'var value',
923              'var value',
924              'var value',
925              'var\\ value';"
926        );
927
928        assert_eq!(
929            format(input, &QueryParams::Named(params), options),
930            expected
931        );
932    }
933
934    #[test]
935    fn it_recognizes_colon_variables() {
936        let input =
937            "SELECT :variable, :a1_2.3$, :'var name', :\"var name\", :`var name`, :[var name];";
938        let options = FormatOptions::default();
939        let expected = indoc!(
940            "
941            SELECT
942              :variable,
943              :a1_2.3$,
944              :'var name',
945              :\"var name\",
946              :`var name`,
947              :[var name];"
948        );
949
950        assert_eq!(format(input, &QueryParams::None, options), expected);
951    }
952
953    #[test]
954    fn it_recognizes_colon_variables_with_param_values() {
955        let input = indoc!(
956            "
957            SELECT :variable, :a1_2.3$, :'var name', :\"var name\", :`var name`,
958            :[var name], :'escaped \\'var\\'', :\"^*& weird \\\" var   \";
959            "
960        );
961        let params = vec![
962            ("variable".to_string(), "\"variable value\"".to_string()),
963            ("a1_2.3$".to_string(), "'weird value'".to_string()),
964            ("var name".to_string(), "'var value'".to_string()),
965            ("escaped 'var'".to_string(), "'weirder value'".to_string()),
966            (
967                "^*& weird \" var   ".to_string(),
968                "'super weird value'".to_string(),
969            ),
970        ];
971        let options = FormatOptions::default();
972        let expected = indoc!(
973            "
974            SELECT
975              \"variable value\",
976              'weird value',
977              'var value',
978              'var value',
979              'var value',
980              'var value',
981              'weirder value',
982              'super weird value';"
983        );
984
985        assert_eq!(
986            format(input, &QueryParams::Named(params), options),
987            expected
988        );
989    }
990
991    #[test]
992    fn it_recognizes_question_numbered_placeholders() {
993        let input = "SELECT ?1, ?25, ?;";
994        let options = FormatOptions::default();
995        let expected = indoc!(
996            "
997            SELECT
998              ?1,
999              ?25,
1000              ?;"
1001        );
1002
1003        assert_eq!(format(input, &QueryParams::None, options), expected);
1004    }
1005
1006    #[test]
1007    fn it_recognizes_question_numbered_placeholders_with_param_values() {
1008        let input = "SELECT ?1, ?2, ?0;";
1009        let params = vec![
1010            "first".to_string(),
1011            "second".to_string(),
1012            "third".to_string(),
1013        ];
1014        let options = FormatOptions::default();
1015        let expected = indoc!(
1016            "
1017            SELECT
1018              second,
1019              third,
1020              first;"
1021        );
1022
1023        assert_eq!(
1024            format(input, &QueryParams::Indexed(params), options),
1025            expected
1026        );
1027
1028        format("?62666666121266666612", &QueryParams::None, options);
1029    }
1030
1031    #[test]
1032    fn it_recognizes_question_indexed_placeholders_with_param_values() {
1033        let input = "SELECT ?, ?, ?;";
1034        let params = vec![
1035            "first".to_string(),
1036            "second".to_string(),
1037            "third".to_string(),
1038        ];
1039        let options = FormatOptions::default();
1040        let expected = indoc!(
1041            "
1042            SELECT
1043              first,
1044              second,
1045              third;"
1046        );
1047
1048        assert_eq!(
1049            format(input, &QueryParams::Indexed(params), options),
1050            expected
1051        );
1052    }
1053
1054    #[test]
1055    fn it_recognizes_dollar_sign_numbered_placeholders() {
1056        let input = "SELECT $1, $2;";
1057        let options = FormatOptions::default();
1058        let expected = indoc!(
1059            "
1060            SELECT
1061              $1,
1062              $2;"
1063        );
1064
1065        assert_eq!(format(input, &QueryParams::None, options), expected);
1066    }
1067
1068    #[test]
1069    fn it_recognizes_dollar_sign_alphanumeric_placeholders() {
1070        let input = "SELECT $hash, $foo, $bar;";
1071        let options = FormatOptions::default();
1072        let expected = indoc!(
1073            "
1074            SELECT
1075              $hash,
1076              $foo,
1077              $bar;"
1078        );
1079
1080        assert_eq!(format(input, &QueryParams::None, options), expected);
1081    }
1082
1083    #[test]
1084    fn it_recognizes_dollar_sign_numbered_placeholders_with_param_values() {
1085        let input = "SELECT $2, $3, $1, $named, $4, $alias;";
1086        let params = vec![
1087            "first".to_string(),
1088            "second".to_string(),
1089            "third".to_string(),
1090            "4th".to_string(),
1091        ];
1092        let options = FormatOptions::default();
1093        let expected = indoc!(
1094            "
1095            SELECT
1096              second,
1097              third,
1098              first,
1099              $named,
1100              4th,
1101              $alias;"
1102        );
1103
1104        assert_eq!(
1105            format(input, &QueryParams::Indexed(params), options),
1106            expected
1107        );
1108    }
1109
1110    #[test]
1111    fn it_recognizes_dollar_sign_alphanumeric_placeholders_with_param_values() {
1112        let input = "SELECT $hash, $salt, $1, $2;";
1113        let params = vec![
1114            ("hash".to_string(), "hash value".to_string()),
1115            ("salt".to_string(), "salt value".to_string()),
1116            ("1".to_string(), "number 1".to_string()),
1117            ("2".to_string(), "number 2".to_string()),
1118        ];
1119        let options = FormatOptions::default();
1120        let expected = indoc!(
1121            "
1122            SELECT
1123              hash value,
1124              salt value,
1125              number 1,
1126              number 2;"
1127        );
1128
1129        assert_eq!(
1130            format(input, &QueryParams::Named(params), options),
1131            expected
1132        );
1133    }
1134
1135    #[test]
1136    fn it_formats_query_with_go_batch_separator() {
1137        let input = "SELECT 1 GO SELECT 2";
1138        let params = vec![
1139            "first".to_string(),
1140            "second".to_string(),
1141            "third".to_string(),
1142        ];
1143        let options = FormatOptions::default();
1144        let expected = indoc!(
1145            "
1146            SELECT
1147              1
1148            GO
1149            SELECT
1150              2"
1151        );
1152
1153        assert_eq!(
1154            format(input, &QueryParams::Indexed(params), options),
1155            expected
1156        );
1157    }
1158
1159    #[test]
1160    fn it_formats_select_query_with_cross_join() {
1161        let input = "SELECT a, b FROM t CROSS JOIN t2 on t.id = t2.id_t";
1162        let options = FormatOptions::default();
1163        let expected = indoc!(
1164            "
1165            SELECT
1166              a,
1167              b
1168            FROM
1169              t
1170              CROSS JOIN t2 on t.id = t2.id_t"
1171        );
1172
1173        assert_eq!(format(input, &QueryParams::None, options), expected);
1174    }
1175
1176    #[test]
1177    fn it_formats_select_query_with_cross_apply() {
1178        let input = "SELECT a, b FROM t CROSS APPLY fn(t.id)";
1179        let options = FormatOptions::default();
1180        let expected = indoc!(
1181            "
1182            SELECT
1183              a,
1184              b
1185            FROM
1186              t
1187              CROSS APPLY fn(t.id)"
1188        );
1189
1190        assert_eq!(format(input, &QueryParams::None, options), expected);
1191    }
1192
1193    #[test]
1194    fn it_formats_simple_select() {
1195        let input = "SELECT N, M FROM t";
1196        let options = FormatOptions::default();
1197        let expected = indoc!(
1198            "
1199            SELECT
1200              N,
1201              M
1202            FROM
1203              t"
1204        );
1205
1206        assert_eq!(format(input, &QueryParams::None, options), expected);
1207    }
1208
1209    #[test]
1210    fn it_formats_simple_select_with_national_characters_mssql() {
1211        let input = "SELECT N'value'";
1212        let options = FormatOptions::default();
1213        let expected = indoc!(
1214            "
1215            SELECT
1216              N'value'"
1217        );
1218
1219        assert_eq!(format(input, &QueryParams::None, options), expected);
1220    }
1221
1222    #[test]
1223    fn it_formats_select_query_with_outer_apply() {
1224        let input = "SELECT a, b FROM t OUTER APPLY fn(t.id)";
1225        let options = FormatOptions::default();
1226        let expected = indoc!(
1227            "
1228            SELECT
1229              a,
1230              b
1231            FROM
1232              t
1233              OUTER APPLY fn(t.id)"
1234        );
1235
1236        assert_eq!(format(input, &QueryParams::None, options), expected);
1237    }
1238
1239    #[test]
1240    fn it_formats_fetch_first_like_limit() {
1241        let input = "SELECT * FETCH FIRST 2 ROWS ONLY;";
1242        let options = FormatOptions::default();
1243        let expected = indoc!(
1244            "
1245            SELECT
1246              *
1247            FETCH FIRST
1248              2 ROWS ONLY;"
1249        );
1250
1251        assert_eq!(format(input, &QueryParams::None, options), expected);
1252    }
1253
1254    #[test]
1255    fn it_formats_case_when_with_a_blank_expression() {
1256        let input = "CASE WHEN option = 'foo' THEN 1 WHEN option = 'bar' THEN 2 WHEN option = 'baz' THEN 3 ELSE 4 END;";
1257        let options = FormatOptions::default();
1258        let expected = indoc!(
1259            "
1260            CASE
1261              WHEN option = 'foo' THEN 1
1262              WHEN option = 'bar' THEN 2
1263              WHEN option = 'baz' THEN 3
1264              ELSE 4
1265            END;"
1266        );
1267
1268        assert_eq!(format(input, &QueryParams::None, options), expected);
1269    }
1270
1271    #[test]
1272    fn it_formats_case_when_inside_select() {
1273        let input =
1274            "SELECT foo, bar, CASE baz WHEN 'one' THEN 1 WHEN 'two' THEN 2 ELSE 3 END FROM table";
1275        let options = FormatOptions::default();
1276        let expected = indoc!(
1277            "
1278            SELECT
1279              foo,
1280              bar,
1281              CASE
1282                baz
1283                WHEN 'one' THEN 1
1284                WHEN 'two' THEN 2
1285                ELSE 3
1286              END
1287            FROM
1288              table"
1289        );
1290
1291        assert_eq!(format(input, &QueryParams::None, options), expected);
1292    }
1293
1294    #[test]
1295    fn it_formats_case_when_with_an_expression() {
1296        let input = "CASE toString(getNumber()) WHEN 'one' THEN 1 WHEN 'two' THEN 2 WHEN 'three' THEN 3 ELSE 4 END;";
1297        let options = FormatOptions::default();
1298        let expected = indoc!(
1299            "
1300            CASE
1301              toString(getNumber())
1302              WHEN 'one' THEN 1
1303              WHEN 'two' THEN 2
1304              WHEN 'three' THEN 3
1305              ELSE 4
1306            END;"
1307        );
1308
1309        assert_eq!(format(input, &QueryParams::None, options), expected);
1310    }
1311
1312    #[test]
1313    fn it_recognizes_lowercase_case_end() {
1314        let input = "case when option = 'foo' then 1 else 2 end;";
1315        let options = FormatOptions::default();
1316        let expected = indoc!(
1317            "
1318            case
1319              when option = 'foo' then 1
1320              else 2
1321            end;"
1322        );
1323
1324        assert_eq!(format(input, &QueryParams::None, options), expected);
1325    }
1326
1327    #[test]
1328    fn it_ignores_words_case_and_end_inside_other_strings() {
1329        let input = "SELECT CASEDATE, ENDDATE FROM table1;";
1330        let options = FormatOptions::default();
1331        let expected = indoc!(
1332            "
1333            SELECT
1334              CASEDATE,
1335              ENDDATE
1336            FROM
1337              table1;"
1338        );
1339
1340        assert_eq!(format(input, &QueryParams::None, options), expected);
1341    }
1342
1343    #[test]
1344    fn it_formats_tricky_line_comments() {
1345        let input = "SELECT a#comment, here\nFROM b--comment";
1346        let options = FormatOptions::default();
1347        let expected = indoc!(
1348            "
1349            SELECT
1350              a #comment, here
1351            FROM
1352              b --comment"
1353        );
1354
1355        assert_eq!(format(input, &QueryParams::None, options), expected);
1356    }
1357
1358    #[test]
1359    fn it_formats_line_comments_followed_by_semicolon() {
1360        let input = indoc!(
1361            "
1362            SELECT a FROM b
1363            --comment
1364            ;"
1365        );
1366        let options = FormatOptions::default();
1367        let expected = indoc!(
1368            "
1369            SELECT
1370              a
1371            FROM
1372              b --comment
1373            ;"
1374        );
1375
1376        assert_eq!(format(input, &QueryParams::None, options), expected);
1377    }
1378
1379    #[test]
1380    fn it_formats_line_comments_followed_by_comma() {
1381        let input = indoc!(
1382            "
1383            SELECT a --comment
1384            , b"
1385        );
1386        let options = FormatOptions::default();
1387        let expected = indoc!(
1388            "
1389            SELECT
1390              a --comment
1391            ,
1392              b"
1393        );
1394
1395        assert_eq!(format(input, &QueryParams::None, options), expected);
1396    }
1397
1398    #[test]
1399    fn it_formats_line_comments_followed_by_close_paren() {
1400        let input = "SELECT ( a --comment\n )";
1401        let options = FormatOptions::default();
1402        let expected = indoc!(
1403            "
1404            SELECT
1405              (
1406                a --comment
1407              )"
1408        );
1409
1410        assert_eq!(format(input, &QueryParams::None, options), expected);
1411    }
1412
1413    #[test]
1414    fn it_formats_line_comments_followed_by_open_paren() {
1415        let input = "SELECT a --comment\n()";
1416        let options = FormatOptions::default();
1417        let expected = indoc!(
1418            "
1419            SELECT
1420              a --comment
1421              ()"
1422        );
1423
1424        assert_eq!(format(input, &QueryParams::None, options), expected);
1425    }
1426
1427    #[test]
1428    fn it_formats_lonely_semicolon() {
1429        let input = ";";
1430        let options = FormatOptions::default();
1431
1432        assert_eq!(format(input, &QueryParams::None, options), input);
1433    }
1434
1435    #[test]
1436    fn it_formats_multibyte_chars() {
1437        let input = "\nSELECT 'главная'";
1438        let options = FormatOptions::default();
1439        let expected = "SELECT\n  'главная'";
1440
1441        assert_eq!(format(input, &QueryParams::None, options), expected);
1442    }
1443
1444    #[test]
1445    fn it_recognizes_scientific_notation() {
1446        let input = "SELECT *, 1e-7 as small, 1e2 as medium, 1e+7 as large FROM t";
1447        let options = FormatOptions::default();
1448        let expected = indoc!(
1449            "
1450            SELECT
1451              *,
1452              1e-7 as small,
1453              1e2 as medium,
1454              1e+7 as large
1455            FROM
1456              t"
1457        );
1458
1459        assert_eq!(format(input, &QueryParams::None, options), expected);
1460    }
1461
1462    #[test]
1463    fn it_keeps_double_dollar_signs_together() {
1464        let input = "CREATE FUNCTION abc() AS $$ SELECT * FROM table $$ LANGUAGE plpgsql;";
1465        let options = FormatOptions::default();
1466        let expected = indoc!(
1467            "
1468            CREATE FUNCTION abc() AS
1469            $$
1470            SELECT
1471              *
1472            FROM
1473              table
1474            $$
1475            LANGUAGE plpgsql;"
1476        );
1477
1478        assert_eq!(format(input, &QueryParams::None, options), expected);
1479    }
1480
1481    #[test]
1482    fn it_formats_pgplsql() {
1483        let input = "CREATE FUNCTION abc() AS $$ DECLARE a int := 1; b int := 2; BEGIN SELECT * FROM table $$ LANGUAGE plpgsql;";
1484        let options = FormatOptions::default();
1485        let expected = indoc!(
1486            "
1487            CREATE FUNCTION abc() AS
1488            $$
1489            DECLARE
1490            a int := 1;
1491            b int := 2;
1492            BEGIN
1493            SELECT
1494              *
1495            FROM
1496              table
1497            $$
1498            LANGUAGE plpgsql;"
1499        );
1500
1501        assert_eq!(format(input, &QueryParams::None, options), expected);
1502    }
1503
1504    #[test]
1505    fn it_handles_comments_correctly() {
1506        let input = indoc!(
1507            "
1508                -- 创建一个外部表，存储销售数据
1509            CREATE EXTERNAL TABLE IF NOT EXISTS sales_data (
1510                -- 唯一标识订单ID
1511                order_id BIGINT COMMENT 'Unique identifier for the order',
1512
1513                -- 客户ID
1514                customer_id BIGINT COMMENT 'Unique identifier for the customer',
1515            )
1516            COMMENT 'Sales data table for storing transaction records';
1517
1518            -- 按销售日期和城市进行分区
1519            PARTITIONED BY (
1520                sale_year STRING COMMENT 'Year of the sale',
1521                sale_month STRING COMMENT 'Month of the sale'
1522            )
1523
1524            -- 设置数据存储位置
1525            LOCATION '/user/hive/warehouse/sales_data'
1526
1527            -- 使用 ORC 存储格式
1528            STORED AS ORC
1529
1530            -- 设置表的行格式
1531            ROW FORMAT DELIMITED
1532            FIELDS TERMINATED BY ','
1533            LINES TERMINATED BY '\n'
1534
1535            -- 设置表属性
1536            TBLPROPERTIES (
1537                'orc.compress' = 'SNAPPY',          -- 使用SNAPPY压缩
1538                'transactional' = 'true',           -- 启用事务支持
1539                'orc.create.index' = 'true',        -- 创建索引
1540                'skip.header.line.count' = '1',     -- 跳过CSV文件的第一行
1541                'external.table.purge' = 'true'     -- 在删除表时自动清理数据
1542            );
1543
1544            -- 自动加载数据到 Hive 分区中
1545            ALTER TABLE sales_data
1546            ADD PARTITION (sale_year = '2024', sale_month = '08')
1547            LOCATION '/user/hive/warehouse/sales_data/2024/08';"
1548        );
1549        let options = FormatOptions {
1550            indent: Indent::Spaces(4),
1551            ..Default::default()
1552        };
1553        let expected = indoc!(
1554            "
1555            -- 创建一个外部表，存储销售数据
1556            CREATE EXTERNAL TABLE IF NOT EXISTS sales_data (
1557                -- 唯一标识订单ID
1558                order_id BIGINT COMMENT 'Unique identifier for the order',
1559                -- 客户ID
1560                customer_id BIGINT COMMENT 'Unique identifier for the customer',
1561            ) COMMENT 'Sales data table for storing transaction records';
1562            -- 按销售日期和城市进行分区
1563            PARTITIONED BY (
1564                sale_year STRING COMMENT 'Year of the sale',
1565                sale_month STRING COMMENT 'Month of the sale'
1566            )
1567            -- 设置数据存储位置
1568            LOCATION '/user/hive/warehouse/sales_data'
1569            -- 使用 ORC 存储格式
1570            STORED AS ORC
1571            -- 设置表的行格式
1572            ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
1573            -- 设置表属性
1574            TBLPROPERTIES (
1575                'orc.compress' = 'SNAPPY',  -- 使用SNAPPY压缩
1576                'transactional' = 'true',  -- 启用事务支持
1577                'orc.create.index' = 'true',  -- 创建索引
1578                'skip.header.line.count' = '1',  -- 跳过CSV文件的第一行
1579                'external.table.purge' = 'true' -- 在删除表时自动清理数据
1580            );
1581            -- 自动加载数据到 Hive 分区中
1582            ALTER TABLE
1583                sales_data
1584            ADD
1585                PARTITION (sale_year = '2024', sale_month = '08') LOCATION '/user/hive/warehouse/sales_data/2024/08';"
1586        );
1587
1588        assert_eq!(format(input, &QueryParams::None, options), expected);
1589    }
1590
1591    #[test]
1592    fn it_recognizes_returning_clause() {
1593        let input = indoc!(
1594            "
1595          INSERT INTO
1596            users (name, email)
1597          VALUES
1598            ($1, $2) RETURNING name,
1599            email"
1600        );
1601        let options = FormatOptions::default();
1602        let expected = indoc!(
1603            "
1604          INSERT INTO
1605            users (name, email)
1606          VALUES
1607            ($1, $2)
1608          RETURNING
1609            name,
1610            email"
1611        );
1612
1613        assert_eq!(format(input, &QueryParams::None, options), expected);
1614    }
1615
1616    #[test]
1617    fn it_recognizes_on_update_clause() {
1618        let input = indoc!(
1619            "CREATE TABLE a (b integer REFERENCES c (id) ON                                     UPDATE RESTRICT, other integer);"
1620        );
1621        let options = FormatOptions::default();
1622        let expected = indoc!(
1623            "
1624          CREATE TABLE a (
1625            b integer REFERENCES c (id) ON UPDATE RESTRICT,
1626            other integer
1627          );"
1628        );
1629        assert_eq!(format(input, &QueryParams::None, options), expected);
1630    }
1631
1632    #[test]
1633    fn it_formats_except_on_columns() {
1634        let input = indoc!(
1635            "SELECT table_0.* EXCEPT (profit),
1636                    details.* EXCEPT (item_id),
1637                    table_0.profit
1638        FROM  table_0"
1639        );
1640        let options = FormatOptions {
1641            indent: Indent::Spaces(4),
1642            ..Default::default()
1643        };
1644        let expected = indoc!(
1645            "
1646            SELECT
1647                table_0.* EXCEPT (profit),
1648                details.* EXCEPT (item_id),
1649                table_0.profit
1650            FROM
1651                table_0"
1652        );
1653
1654        assert_eq!(format(input, &QueryParams::None, options), expected);
1655    }
1656}
sqlformat/lib.rs

sqlformat/
lib.rs
