packages/sql-formatter/tests/Unit/Facade/FormatterTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Facade;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\Medium;
10use PHPUnit\Framework\TestCase;
11use SqlFormatter\Core\FormatOptions;
12use SqlFormatter\Core\Style;
13use SqlFormatter\Facade\Formatter;
14use SqlParser\MySql\MySqlParser;
15use SqlParser\MySql\SqlMode;
16use SqlParser\Parser\SyntaxException;
17use SqlParser\PostgreSql\PostgreSqlParser;
18use SqlParser\Sqlite\SqliteParser;
19
20#[CoversClass(Formatter::class)]
21#[CoversClass(FormatOptions::class)]
22#[CoversClass(\SqlFormatter\Core\Layout\Renderer::class)]
23#[CoversClass(\SqlFormatter\Core\Layout\Block::class)]
24#[CoversClass(\SqlFormatter\Core\Layout\Elements::class)]
25#[CoversClass(\SqlFormatter\Core\Layout\Headers::class)]
26#[CoversClass(\SqlFormatter\Core\Layout\Policy::class)]
27#[CoversClass(\SqlFormatter\Core\Layout\Spacing::class)]
28#[CoversClass(\SqlFormatter\Core\Layout\Writer::class)]
29#[CoversClass(\SqlFormatter\Core\Syntax\Analyzer::class)]
30#[CoversClass(\SqlFormatter\Core\Syntax\Document::class)]
31#[CoversClass(\SqlFormatter\Core\Syntax\Brackets::class)]
32#[CoversClass(\SqlFormatter\Core\Syntax\Headers::class)]
33#[CoversClass(\SqlFormatter\Core\Syntax\Markers::class)]
34#[CoversClass(\SqlFormatter\Core\Syntax\Expressions::class)]
35#[CoversClass(\SqlFormatter\Core\Syntax\Fingerprint::class)]
36#[CoversClass(\SqlFormatter\Core\Syntax\Rules::class)]
37#[Medium]
38#[CoversClass(\SqlFormatter\Core\Syntax\Lists::class)]
39#[CoversClass(\SqlFormatter\Core\Compact\Reductions::class)]
40#[CoversClass(\SqlFormatter\Core\Compact\Document::class)]
41#[CoversClass(\SqlFormatter\Core\Compact\Visitor::class)]
42#[CoversClass(\SqlFormatter\Core\Compact\Rules::class)]
43#[CoversClass(\SqlFormatter\Core\Compact\Keywords::class)]
44#[CoversClass(\SqlFormatter\Core\Compact\Grouping::class)]
45#[CoversClass(\SqlFormatter\Core\Compact\Shape::class)]
46#[CoversClass(\SqlFormatter\Core\Compact\Renderer::class)]
47#[CoversClass(\SqlFormatter\Core\Compact\Spacing::class)]
48#[CoversClass(\SqlFormatter\Core\Compact\Trivia::class)]
49#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Core\Formatter::class)]
50#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Core\Compact\Settings::class)]
51#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Platform\MySql\Dialect::class)]
52#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Platform\PostgreSql\Dialect::class)]
53#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Platform\Sqlite\Dialect::class)]
54#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Facade\DialectFactory::class)]
55final class FormatterTest extends TestCase
56{
57 public function testFormatKeepsHostnamesAdjacentToAtSigns(): void
58 {
59 $formatter = new Formatter(new MySqlParser(), new FormatOptions(Style::Compact));
60 $sql = 'DROP ROLE IF EXISTS user_name @$tag$host';
61 self::assertSame('DROP ROLE IF EXISTS user_name @$tag$host', $formatter->format($sql));
62 self::assertSame($formatter->format($sql), $formatter->format($formatter->format($sql)));
63 }
64
65 /**
66 * @param class-string<MySqlParser|PostgreSqlParser|SqliteParser> $parserClass
67 */
68 #[DataProvider('providerStyles')]
69 public function testFormatFourLayouts(string $parserClass, ?string $version, Style $style, string $expected): void
70 {
71 $formatter = new Formatter(new $parserClass($version), new FormatOptions($style));
72 self::assertSame($expected, $formatter->format('SELECT id,name FROM users WHERE active=1 AND age>=18 ORDER BY name;'));
73 self::assertSame($expected, $formatter->format($expected));
74 }
75
76 /**
77 * @return iterable<string, array{class-string<MySqlParser|PostgreSqlParser|SqliteParser>, string|null, Style, string}>
78 */
79 public static function providerStyles(): iterable
80 {
81 $layouts = [
82 'compact' => 'SELECT id,name FROM users WHERE active=1 AND age>=18 ORDER BY name',
83 'expanded' => "SELECT\n id,\n name\nFROM\n users\nWHERE\n active = 1\n AND age >= 18\nORDER BY\n name;",
84 'tabular' => "SELECT id,\n name\nFROM users\nWHERE active = 1\nAND age >= 18\nORDER BY name;",
85 'river' => " SELECT id,\n name\n FROM users\n WHERE active = 1\n AND age >= 18\nORDER BY name;",
86 ];
87 foreach ([...MySqlParser::versions(), 'pg', 'sqlite'] as $dialect) {
88 foreach ($layouts as $style => $expected) {
89 yield $dialect . '-' . $style => [match ($dialect) {
90 'pg' => PostgreSqlParser::class, 'sqlite' => SqliteParser::class, default => MySqlParser::class
91 }, str_starts_with($dialect, 'mysql-') ? $dialect : null, Style::from($style), $expected];
92 }
93 }
94 }
95
96 /**
97 * @param class-string<MySqlParser|PostgreSqlParser|SqliteParser> $parserClass
98 */
99 #[DataProvider('providerTokenPreservation')]
100 public function testFormatPreservesTokensAndIsIdempotent(string $parserClass, ?string $version, Style $style, string $sql): void
101 {
102 $parser = new $parserClass($version);
103 $formatter = new Formatter($parser, new FormatOptions($style));
104 $result = $formatter->format($sql);
105 self::assertSame(array_column($parser->parse($sql)->tokens(), 'text'), array_column($parser->parse($result)->tokens(), 'text'));
106 self::assertSame($result, $formatter->format($result));
107 }
108
109 /**
110 * @param class-string<MySqlParser|PostgreSqlParser|SqliteParser> $parserClass
111 */
112 #[DataProvider('providerPreservation')]
113 public function testFormatRemainsCanonicalAcrossLayouts(string $parserClass, ?string $version, Style $style, string $sql): void
114 {
115 $parser = new $parserClass($version);
116 $layout = new Formatter($parser, new FormatOptions($style));
117 $compact = new Formatter($parser, new FormatOptions(Style::Compact));
118 self::assertSame($compact->format($sql), $compact->format($layout->format($sql)));
119 }
120
121 /**
122 * @return iterable<string, array{class-string<MySqlParser|PostgreSqlParser|SqliteParser>, string|null, Style, string}>
123 */
124 public static function providerTokenPreservation(): iterable
125 {
126 foreach (self::providerPreservation() as $name => $case) {
127 if ($case[2] !== Style::Compact) {
128 yield $name => $case;
129 }
130 }
131 }
132
133 /**
134 * @return iterable<string, array{class-string<MySqlParser|PostgreSqlParser|SqliteParser>, string|null, Style, string}>
135 */
136 public static function providerPreservation(): iterable
137 {
138 $common = [
139 'SELECT a,b FROM t WHERE a BETWEEN 1 AND 3 AND b NOT BETWEEN 4 AND 5;',
140 'SELECT a FROM t WHERE a IN (1,2) AND (b=2 OR c=3);',
141 'SELECT coalesce(a,b), count(*), sum(a+1) FROM t GROUP BY a,b HAVING count(*)>1;',
142 'SELECT a FROM t UNION ALL SELECT b FROM u ORDER BY a;',
143 'SELECT CASE WHEN a=1 THEN CASE WHEN b=2 THEN 3 ELSE 4 END ELSE 5 END AS n FROM t;',
144 'SELECT (SELECT max(a) FROM t) AS x, name FROM u;',
145 'SELECT t.a,u.b FROM t LEFT OUTER JOIN u ON t.a=u.a AND u.b>0;',
146 'INSERT INTO t (a,b) VALUES (1,2),(3,4);',
147 'INSERT INTO t (a,b) SELECT a,b FROM u;',
148 'UPDATE t SET a=1,b=b+1 WHERE c=3;',
149 'DELETE FROM t WHERE a=1;',
150 'CREATE TABLE t (id INTEGER PRIMARY KEY, name VARCHAR(20), n INT DEFAULT -1, CHECK (n>0));',
151 'CREATE INDEX ix ON t (a,b);',
152 'ALTER TABLE t ADD COLUMN score INT;',
153 'DROP TABLE t;',
154 'BEGIN;',
155 'COMMIT;',
156 "SELECT /* keep */ 'a b', 'x; y' AS v FROM t -- tail\nWHERE a=1; -- final\n",
157 "-- lead\nSELECT 1 /* block\n body */; /* end */\n",
158 'SELECT 1 - -2, +3, -4, 5/2, a.b FROM t;',
159 ];
160 foreach (['mysql-8.4.7', 'pg', 'sqlite'] as $dialect) {
161 foreach (Style::cases() as $style) {
162 foreach ($common as $index => $sql) {
163 yield $dialect . '-' . $style->value . '-' . $index => [match ($dialect) {
164 'pg' => PostgreSqlParser::class, 'sqlite' => SqliteParser::class, 'mysql-8.4.7' => MySqlParser::class
165 }, str_starts_with($dialect, 'mysql-') ? $dialect : null, $style, $sql];
166 }
167 }
168 }
169 $specific = [
170 'mysql-8.4.7' => ['SELECT /*!80000 DISTINCT */ a FROM t;', 'SELECT /*+ MAX_EXECUTION_TIME(1000) */ `order`, @name FROM t;', 'SELECT 1; # final', 'SELECT count(1), count (2);', 'SHOW CREATE TABLE ACTION ._sqlfaker_identifier'],
171 'pg' => ["SELECT 'a'\n'b', \$tag\$keep\nthis\$tag\$;", 'SELECT a::text, $1, ARRAY[1,2] FROM t;', 'SELECT 1; SELECT 2;', 'SELECT U&\'d!0061t\' UESCAPE \'!\';'],
172 'sqlite' => ['SELECT [select], :name, ?1 FROM t;', 'SELECT 1; SELECT 2;', 'CREATE TRIGGER tr AFTER INSERT ON t BEGIN UPDATE t SET a=1; UPDATE t SET b=2; END;'],
173 ];
174 foreach ($specific as $dialect => $queries) {
175 foreach (Style::cases() as $style) {
176 foreach ($queries as $index => $sql) {
177 yield $dialect . '-special-' . $style->value . '-' . $index => [match ($dialect) {
178 'pg' => PostgreSqlParser::class, 'sqlite' => SqliteParser::class, 'mysql-8.4.7' => MySqlParser::class
179 }, str_starts_with($dialect, 'mysql-') ? $dialect : null, $style, $sql];
180 }
181 }
182 }
183 }
184
185 #[DataProvider('providerVersions')]
186 public function testFormatEveryGrammarRelease(string $version, Style $style): void
187 {
188 $formatter = new Formatter(new MySqlParser($version), new FormatOptions($style));
189 $output = $formatter->format('SELECT a,b FROM t WHERE a=1 ORDER BY b;');
190 self::assertSame($output, $formatter->format($output));
191 self::assertStringContainsString('SELECT', $output);
192 }
193
194 /**
195 * @return iterable<string, array{string, Style}>
196 */
197 public static function providerVersions(): iterable
198 {
199 foreach (MySqlParser::versions() as $version) {
200 foreach (Style::cases() as $style) {
201 yield $version . '-' . $style->value => [$version, $style];
202 }
203 }
204 }
205
206 /**
207 * @param class-string<MySqlParser|PostgreSqlParser|SqliteParser> $parserClass
208 */
209 #[DataProvider('providerGeneratedStatements')]
210 public function testFormatGeneratedStatements(string $parserClass, string $version, Style $style, string $sql): void
211 {
212 $formatter = new Formatter(new $parserClass($version), new FormatOptions($style));
213 $output = $formatter->format($sql);
214 self::assertSame($output, $formatter->format($output));
215 $compact = new Formatter(new $parserClass($version), new FormatOptions(Style::Compact));
216 self::assertSame($compact->format($sql), $compact->format($output));
217 }
218
219 /**
220 * @return iterable<string, array{class-string<MySqlParser|PostgreSqlParser|SqliteParser>, string, Style, string}>
221 */
222 public static function providerGeneratedStatements(): iterable
223 {
224 $faker = \Faker\Factory::create();
225 $faker->seed(927);
226 $releases = [
227 ...array_map(static fn (string $version): array => [MySqlParser::class, \SqlFaker\MySql\MySqlProvider::class, $version, in_array($version, ['mysql-5.6.51', 'mysql-5.7.44'], true) ? 'statement' : 'simple_statement_or_begin'], MySqlParser::versions()),
228 [PostgreSqlParser::class, \SqlFaker\PostgreSql\PostgreSqlProvider::class, 'pg-17.2', 'stmt'],
229 [SqliteParser::class, \SqlFaker\Sqlite\SqliteProvider::class, 'sqlite-3.47.2', 'cmd'],
230 ];
231 foreach ($releases as [$parserClass, $providerClass, $version, $root]) {
232 $provider = new $providerClass($faker, $version);
233 $plan = \SqlFaker\Generation\Plan\GenerationPlan::fromRule($root)->requiringNonEmpty()->withMaxDepth(6);
234 for ($index = 0; $index < 16; $index++) {
235 $sql = $provider->generate($plan);
236 foreach (Style::cases() as $style) {
237 yield $version . '-' . $index . '-' . $style->value => [$parserClass, $version, $style, $sql];
238 }
239 }
240 }
241 }
242
243 /**
244 * @param class-string<MySqlParser|PostgreSqlParser|SqliteParser> $parserClass
245 */
246 #[DataProvider('providerExpandedLayouts')]
247 public function testFormatExpandedStructures(string $parserClass, string $sql, string $expected): void
248 {
249 self::assertSame($expected, (new Formatter(new $parserClass()))->format($sql));
250 }
251
252 /**
253 * @return iterable<string, array{class-string<MySqlParser|PostgreSqlParser|SqliteParser>, string, string}>
254 */
255 public static function providerExpandedLayouts(): iterable
256 {
257 $queries = [
258 ['SELECT a,b FROM t GROUP BY a,b ORDER BY a,b;', "SELECT\n a,\n b\nFROM\n t\nGROUP BY\n a,\n b\nORDER BY\n a,\n b;"],
259 ['INSERT INTO t (a,b) VALUES (1,2),(3,4);', "INSERT INTO\n t (a, b)\nVALUES\n (1, 2),\n (3, 4);"],
260 ['UPDATE t SET a=1,b=b+1 WHERE c=3;', "UPDATE\n t\nSET\n a = 1,\n b = b + 1\nWHERE\n c = 3;"],
261 ['DELETE FROM t WHERE a=1;', "DELETE FROM\n t\nWHERE\n a = 1;"],
262 ['CREATE TABLE t (id INT, name VARCHAR(20));', "CREATE TABLE t (\n id INT,\n name VARCHAR(20)\n);"],
263 ['SELECT a FROM t LEFT OUTER JOIN u ON t.a=u.a;', "SELECT\n a\nFROM\n t\nLEFT OUTER JOIN\n u\nON\n t.a = u.a;"],
264 ['SELECT CASE WHEN a BETWEEN 1 AND 3 THEN 1 ELSE 0 END FROM t;', "SELECT\n CASE\n WHEN a BETWEEN 1 AND 3 THEN 1\n ELSE 0\n END\nFROM\n t;"],
265 ['SELECT a FROM t UNION ALL SELECT b FROM u;', "SELECT\n a\nFROM\n t\nUNION ALL\nSELECT\n b\nFROM\n u;"],
266 ['SELECT row_number() OVER (PARTITION BY a,b ORDER BY b ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS n FROM t;', "SELECT\n row_number() OVER (\n PARTITION BY\n a,\n b\n ORDER BY\n b\n ROWS\n BETWEEN 1 PRECEDING AND CURRENT ROW\n ) AS n\nFROM\n t;"],
267 ];
268 foreach ([MySqlParser::class, PostgreSqlParser::class, SqliteParser::class] as $parserClass) {
269 foreach ($queries as $index => [$sql, $expected]) {
270 yield $parserClass . '-' . $index => [$parserClass, $sql, $expected];
271 }
272 }
273 }
274
275 public function testFormatNestedCteWithConfiguredIndent(): void
276 {
277 $formatter = new Formatter(new SqliteParser(), new FormatOptions(Style::Expanded, 2));
278 self::assertSame("WITH\n q AS (\n SELECT\n a,\n b\n FROM\n t\n )\nSELECT\n *\nFROM\n q;", $formatter->format('WITH q AS (SELECT a,b FROM t) SELECT * FROM q;'));
279 }
280
281 public function testFormatPreservesMysqlSqlMode(): void
282 {
283 $formatter = new Formatter(new MySqlParser(mode: new SqlMode(ansiQuotes: true, pipesAsConcat: true)), new FormatOptions(Style::Compact));
284 self::assertSame('SELECT"name"||\'!\'FROM"users"', $formatter->format('SELECT "name"||\'!\' FROM "users";'));
285 }
286
287 public function testFormatRejectsInvalidInput(): void
288 {
289 $formatter = new Formatter(new SqliteParser());
290 $this->expectException(SyntaxException::class);
291 $formatter->format('SELECT FROM');
292 }
293}
294