packages/sql-formatter/tests/Unit/Core/Compact/RendererTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Core\Compact;
6
7use PDO;
8use PHPUnit\Framework\Attributes\CoversClass;
9use PHPUnit\Framework\Attributes\DataProvider;
10use PHPUnit\Framework\TestCase;
11use SqlFormatter\Core\Compact\Document;
12use SqlFormatter\Core\Compact\Grouping;
13use SqlFormatter\Core\Compact\Keywords;
14use SqlFormatter\Core\Compact\Renderer;
15use SqlFormatter\Core\Compact\Rules;
16use SqlFormatter\Core\Compact\Shape;
17use SqlFormatter\Core\Compact\Spacing;
18use SqlFormatter\Core\Compact\Trivia;
19use SqlFormatter\Core\Compact\Visitor;
20use SqlParser\MySql\MySqlParser;
21use SqlParser\MySql\SqlMode;
22use SqlParser\PostgreSql\PostgreSqlParser;
23use SqlParser\Sqlite\SqliteParser;
24
25#[CoversClass(Renderer::class)]
26#[CoversClass(\SqlFormatter\Core\Compact\Reductions::class)]
27#[CoversClass(Document::class)]
28#[CoversClass(Grouping::class)]
29#[CoversClass(Keywords::class)]
30#[CoversClass(Rules::class)]
31#[CoversClass(Shape::class)]
32#[CoversClass(Spacing::class)]
33#[CoversClass(Trivia::class)]
34#[CoversClass(Visitor::class)]
35#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Core\Formatter::class)]
36#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Core\Compact\Settings::class)]
37#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Platform\MySql\Dialect::class)]
38#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Platform\PostgreSql\Dialect::class)]
39#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Platform\Sqlite\Dialect::class)]
40#[\PHPUnit\Framework\Attributes\UsesClass(\SqlFormatter\Facade\DialectFactory::class)]
41final class RendererTest extends TestCase
42{
43 /**
44 * @param class-string<MySqlParser|PostgreSqlParser|SqliteParser> $parserClass
45 */
46 #[DataProvider('providerCanonicalForms')]
47 public function testRenderEquivalentSpellings(string $parserClass, ?string $version, string $input, string $expected): void
48 {
49 $parser = new $parserClass($version);
50 $renderer = new Renderer($parser, \SqlFormatter\Facade\DialectFactory::forParser($parser)->compactRules());
51 self::assertSame($expected, $renderer->render($parser->parse($input)));
52 self::assertSame($expected, $renderer->render($parser->parse($expected)));
53 }
54
55 /**
56 * @return iterable<string, array{class-string<MySqlParser|PostgreSqlParser|SqliteParser>, string|null, string, string}>
57 */
58 public static function providerCanonicalForms(): iterable
59 {
60 $queries = [
61 ['select all ((a)) as x, b from t as q left outer join u on q.a != u.a order by b asc;', 'SELECT a x,b FROM t q LEFT JOIN u ON q.a<>u.a ORDER BY b'],
62 ['SELECT a x,b FROM t q LEFT JOIN u ON q.a<>u.a ORDER BY b', 'SELECT a x,b FROM t q LEFT JOIN u ON q.a<>u.a ORDER BY b'],
63 ["-- before\nselect /* ordinary */ a, b from t -- after\nwhere a = 1; /* tail */", 'SELECT a,b FROM t WHERE a=1'],
64 ['select a from t inner join u on (t.a=u.a)', 'SELECT a FROM t JOIN u ON t.a=u.a'],
65 ['SELECT a FROM t NATURAL INNER JOIN u', 'SELECT a FROM t NATURAL JOIN u'],
66 ['SELECT a FROM t CROSS JOIN u', 'SELECT a FROM t CROSS JOIN u'],
67 ['SELECT (a+b)*c AS x,a+(b*c) AS y,a-(b-c) AS z FROM t WHERE (a=1 AND b=2)', 'SELECT(a+b)*c x,a+b*c y,a-(b-c)z FROM t WHERE a=1 AND b=2'],
68 ['SELECT (a-b)-c AS x FROM t WHERE (a=1 OR b=2) AND c=3', 'SELECT a-b-c x FROM t WHERE(a=1 OR b=2)AND c=3'],
69 ['SELECT 1 - -2 AS x', 'SELECT 1- -2 x'],
70 ['select t . a from Action as t', 'SELECT t.a FROM Action t'],
71 ["select '/* literal */ -- text' as x", "SELECT'/* literal */ -- text'x"],
72 ['WITH q AS (SELECT 1 AS x) SELECT x FROM q', 'WITH q AS(SELECT 1 x)SELECT x FROM q'],
73 ['SELECT a FROM t UNION ALL SELECT a FROM u', 'SELECT a FROM t UNION ALL SELECT a FROM u'],
74 ['SELECT a FROM t ORDER BY a DESC', 'SELECT a FROM t ORDER BY a DESC'],
75 ['select /*+ KEEP(a) */ distinct a from t', 'SELECT/*+ KEEP(a) */DISTINCT a FROM t'],
76 ];
77 $releases = [
78 ...array_map(static fn (string $version): array => [MySqlParser::class, $version], MySqlParser::versions()),
79 [PostgreSqlParser::class, null], [SqliteParser::class, null],
80 ];
81 foreach ($releases as [$parser, $version]) {
82 foreach ($queries as $index => [$input, $expected]) {
83 if (in_array($index, [4, 11], true) && in_array($version, ['mysql-5.6.51', 'mysql-5.7.44'], true)) {
84 continue;
85 }
86 yield $parser . '-' . $version . '-' . $index => [$parser, $version, $input, $expected];
87 }
88 if ($parser === MySqlParser::class) {
89 yield $version . '-conflicting-options' => [$parser, $version, 'SELECT ALL DISTINCT 1', 'SELECT ALL DISTINCT 1'];
90 yield $version . '-reversed-conflicting-options' => [$parser, $version, 'select distinctrow all 1', 'SELECT DISTINCT ALL 1'];
91 yield $version . '-separated-conflicting-options' => [$parser, $version, 'SELECT ALL SQL_SMALL_RESULT DISTINCT 1', 'SELECT ALL SQL_SMALL_RESULT DISTINCT 1'];
92 yield $version . '-repeated-default' => [$parser, $version, 'SELECT ALL ALL 1', 'SELECT 1'];
93 yield $version . '-scoped-options' => [$parser, $version, 'SELECT ALL (SELECT DISTINCT 1)', 'SELECT(SELECT DISTINCT 1)'];
94 }
95 }
96 foreach ([MySqlParser::class, PostgreSqlParser::class] as $parser) {
97 yield $parser . '-set-distinct' => [$parser, null, 'SELECT a FROM t UNION DISTINCT SELECT a FROM u', 'SELECT a FROM t UNION SELECT a FROM u'];
98 }
99 yield 'sqlite-trailing-terminators' => [SqliteParser::class, null, 'SELECT 1;;; -- tail', 'SELECT 1'];
100 yield 'pg-trailing-terminators' => [PostgreSqlParser::class, null, 'SELECT 1;;; -- tail', 'SELECT 1'];
101 yield 'pg-empty-statements' => [PostgreSqlParser::class, null, ';;;', ''];
102 yield 'sqlite-empty-statements' => [SqliteParser::class, null, ';;;', ''];
103 yield 'sqlite-equals' => [SqliteParser::class, null, 'SELECT a FROM t WHERE a == 1 AND b != 2;', 'SELECT a FROM t WHERE a=1 AND b<>2'];
104 yield 'sqlite-statements' => [SqliteParser::class, null, 'SELECT 1; SELECT 2;', 'SELECT 1;SELECT 2'];
105 yield 'sqlite-identifier-fallback' => [SqliteParser::class, null, 'select Action from Action', 'SELECT Action FROM Action'];
106 yield 'sqlite-cast' => [SqliteParser::class, null, 'select cast(a as text) as v from t', 'SELECT CAST(a AS text)v FROM t'];
107 yield 'pg-keyword-alias' => [PostgreSqlParser::class, null, 'SELECT a AS value FROM t', 'SELECT a value FROM t'];
108 yield 'pg-mandatory-alias' => [PostgreSqlParser::class, null, 'SELECT 1 AS from', 'SELECT 1 AS from'];
109 yield 'mysql-merged-keywords' => [MySqlParser::class, null, 'select a from t group by a with /* remove */ rollup', 'SELECT a FROM t GROUP BY a WITH ROLLUP'];
110 yield 'pg-cast' => [PostgreSqlParser::class, null, 'select cast(a as text) as v from t', 'SELECT CAST(a AS text)v FROM t'];
111 yield 'pg-required-alias' => [PostgreSqlParser::class, null, 'select 1 as "select"', 'SELECT 1"select"'];
112 yield 'pg-nested-comment' => [PostgreSqlParser::class, null, 'SELECT /* outer /*+ hidden hint */ body */ 1', 'SELECT 1'];
113 yield 'pg-string-continuation' => [PostgreSqlParser::class, null, "SELECT 'a'\n'b' AS v;", "SELECT'a'\n'b'v"];
114 yield 'pg-dollar-literal' => [PostgreSqlParser::class, null, 'select $tag$ /* keep */ $tag$ as v', 'SELECT $tag$ /* keep */ $tag$v'];
115 yield 'pg-array' => [PostgreSqlParser::class, null, 'SELECT ARRAY[1,2], $1::text', 'SELECT ARRAY[1,2],$1::text'];
116 yield 'pg-group-distinct' => [PostgreSqlParser::class, null, 'SELECT a FROM t GROUP BY DISTINCT a', 'SELECT a FROM t GROUP BY DISTINCT a'];
117 yield 'mysql-distinctrow' => [MySqlParser::class, null, 'select distinctrow a from t where a regexp b', 'SELECT DISTINCT a FROM t WHERE a RLIKE b'];
118 yield 'mysql-string-alias' => [MySqlParser::class, null, "SELECT 'a' AS 'b'", "SELECT'a'AS'b'"];
119 yield 'mysql-string-concatenation' => [MySqlParser::class, null, "SELECT 'a' 'b'", "SELECT'a' 'b'"];
120 yield 'mysql-type-alias' => [MySqlParser::class, null, 'create table t (a integer,b decimal(8,2))', 'CREATE TABLE t(a INT,b DEC(8,2))'];
121 yield 'mysql-function-gap' => [MySqlParser::class, null, 'SELECT count(1), count (2)', 'SELECT COUNT(1),count (2)'];
122 yield 'mysql-variable' => [MySqlParser::class, null, 'SELECT @name, @@session.sql_mode', 'SELECT@name,@@SESSION.sql_mode'];
123 yield 'mysql-qualification' => [MySqlParser::class, null, 'SHOW CREATE TABLE ACTION ._sqlfaker_identifier', 'SHOW CREATE TABLE ACTION ._sqlfaker_identifier'];
124 yield 'mysql-hint' => [MySqlParser::class, null, 'SELECT /* ordinary */ /*+ MAX_EXECUTION_TIME(1000) */ `order` FROM t;', 'SELECT/*+ MAX_EXECUTION_TIME(1000) */`order`FROM t'];
125 yield 'mysql-active-comment' => [MySqlParser::class, null, 'select /*!80000 DISTINCT */ a from t; # tail', 'SELECT/*!80000 DISTINCT */a FROM t'];
126 yield 'mysql-inactive-comment' => [MySqlParser::class, null, 'select /*!99999 SQL_NO_CACHE */ a from t;', 'SELECT/*!99999 SQL_NO_CACHE */a FROM t'];
127 yield 'mysql-nested-executable-body' => [MySqlParser::class, null, '/*!80000 select 1 /* keep */ + 2 */', '/*!80000 select 1 /* keep */ + 2 */'];
128 yield 'mysql-executable-body' => [MySqlParser::class, null, '/*!80000 select (1 + 2) */', '/*!80000 select (1 + 2) */'];
129 }
130
131 public function testRenderHonorsMysqlLexicalModes(): void
132 {
133 $parser = new MySqlParser(mode: new SqlMode(ansiQuotes: true, pipesAsConcat: true, ignoreSpace: true));
134 $renderer = new Renderer($parser, \SqlFormatter\Facade\DialectFactory::forParser($parser)->compactRules());
135 self::assertSame('SELECT COUNT(1),"name"||\'!\'FROM"users"', $renderer->render($parser->parse('select count (1), "name" || \'!\' from "users";')));
136 }
137
138 #[DataProvider('providerSqliteExecution')]
139 public function testRenderPreservesSqliteResultsAndOperatorAssociation(string $sql): void
140 {
141 $database = new PDO('sqlite::memory:');
142 $database->exec('CREATE TABLE t(a INT,b INT,c INT); INSERT INTO t VALUES(1,2,3),(2,NULL,4),(5,3,2)');
143 $parser = new SqliteParser();
144 $renderer = new Renderer($parser, \SqlFormatter\Facade\DialectFactory::forParser($parser)->compactRules());
145 $original = $database->query($sql);
146 $formatted = $database->query($renderer->render($parser->parse($sql)));
147 self::assertNotFalse($original);
148 self::assertNotFalse($formatted);
149 self::assertSame($original->fetchAll(PDO::FETCH_ASSOC), $formatted->fetchAll(PDO::FETCH_ASSOC));
150 }
151
152 /**
153 * @return list<array{string}>
154 */
155 public static function providerSqliteExecution(): array
156 {
157 return [
158 ['SELECT (a+b)*c AS x,a+(b*c) AS y,a-(b-c) AS z FROM t ORDER BY a ASC'],
159 ['SELECT a AS x FROM t WHERE (a=1 OR b=3) AND c=2 ORDER BY a ASC'],
160 ['SELECT a AS x FROM t WHERE NOT (b=3 OR c=4) ORDER BY a ASC'],
161 ['SELECT a/(b/c) AS x FROM t ORDER BY a ASC'],
162 ['SELECT a AS x FROM t WHERE a == 1 OR a != 2 ORDER BY a ASC'],
163 ];
164 }
165
166 public function testWriteRetainsDirectiveOwnershipAndRequiredSeparators(): void
167 {
168 $renderer = new Renderer(new SqliteParser(), \SqlFormatter\Facade\DialectFactory::forParser(new SqliteParser())->compactRules());
169 self::assertSame('SELECT/*+ keep */a FROM t', $renderer->write([
170 new \SqlParser\Lexer\Token(1, 'SELECT', 'SELECT', 0),
171 new \SqlParser\Lexer\Token(2, 'ID', 'a', 20, '/*+ keep */'),
172 new \SqlParser\Lexer\Token(3, 'FROM', 'FROM', 22),
173 new \SqlParser\Lexer\Token(2, 'ID', 't', 27),
174 ], ''));
175 }
176
177 public function testDocumentSelectsDialectSpecificSpellings(): void
178 {
179 $parser = new MySqlParser();
180 $document = (new Renderer($parser, \SqlFormatter\Facade\DialectFactory::forParser($parser)->compactRules()))->document($parser->parse('SELECT a FROM t WHERE a REGEXP b'));
181 self::assertContains('RLIKE', array_column($document->tokens, 'text'));
182 }
183}
184