packages/ztd-query-mysql/tests/Unit/Sql/Relation/MySqlSelectRelationParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Sql\Relation;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile;
12use ZtdQuery\Platform\MySql\Sql\Relation\MySqlSelectRelationParser;
13
14#[CoversClass(MySqlSelectRelationParser::class)]
15#[UsesClass(MySqlLexerProfile::class)]
16#[CoversClass(\ZtdQuery\Platform\MySql\Sql\Relation\ReferenceReader::class)]
17final class MySqlSelectRelationParserTest extends TestCase
18{
19 public function testTableNamesFindsPhysicalRelationsAcrossMySqlSelectScopes(): void
20 {
21 $sql = 'SELECT * FROM (SELECT * FROM app.`users` UNION ALL SELECT * FROM archived_users) p JOIN JSON_TABLE(p.data, \'$[*]\' COLUMNS(id INT PATH \'$.id\')) j ON TRUE JOIN audit_log a ON TRUE';
22
23 self::assertSame(
24 ['audit_log', 'users', 'archived_users'],
25 (new MySqlSelectRelationParser())->tableNames($sql),
26 );
27 }
28
29 public function testFromClausesUseMySqlBoundaries(): void
30 {
31 $parser = new MySqlSelectRelationParser();
32
33 self::assertSame(
34 ['users', 'archived'],
35 $parser->fromClauses('SELECT * FROM users PROCEDURE ANALYSE(); SELECT * FROM archived LOCK IN SHARE MODE'),
36 );
37 }
38
39 public function testUnqualifyReferencesPreserveOffsetsAndUnqualifyOnlyTargets(): void
40 {
41 $parser = new MySqlSelectRelationParser();
42 $sql = 'SELECT * FROM app.`users` u JOIN logs.audit_log a ON TRUE';
43
44 self::assertSame(['users', 'audit_log'], array_column($parser->references($sql), 'name'));
45 self::assertSame(
46 'SELECT * FROM `users` u JOIN logs.audit_log a ON TRUE',
47 $parser->unqualify($sql, ['users']),
48 );
49 }
50
51 public function testReferencesExposeExactQualifiedIdentifierOffsets(): void
52 {
53 $parser = new MySqlSelectRelationParser();
54 $sql = 'SELECT * FROM app.`a``b` a JOIN users u ON TRUE JOIN logs.orders o ON TRUE';
55
56 self::assertSame([
57 [
58 'name' => 'a`b',
59 'start' => 14,
60 'unqualifiedStart' => 18,
61 'end' => 24,
62 ],
63 [
64 'name' => 'users',
65 'start' => 32,
66 'unqualifiedStart' => 32,
67 'end' => 37,
68 ],
69 [
70 'name' => 'orders',
71 'start' => 53,
72 'unqualifiedStart' => 58,
73 'end' => 64,
74 ],
75 ], $parser->references($sql));
76 }
77
78 public function testUnqualifiesMultipleTargetsCaseInsensitivelyFromRightToLeft(): void
79 {
80 $sql = 'SELECT * FROM users u JOIN app.Users u2 ON TRUE JOIN logs.orders o ON TRUE';
81
82 self::assertSame(
83 'SELECT * FROM users u JOIN Users u2 ON TRUE JOIN orders o ON TRUE',
84 (new MySqlSelectRelationParser())->unqualify($sql, ['USERS', 'ORDERS']),
85 );
86 }
87
88 public function testKeepsOneCaseInsensitiveRelationName(): void
89 {
90 self::assertSame(
91 ['Users'],
92 (new MySqlSelectRelationParser())->tableNames('SELECT * FROM Users JOIN users ON TRUE'),
93 );
94 }
95
96 public function testSetOperatorsAndRepeatedFromCloseTheCurrentSelectScope(): void
97 {
98 $parser = new MySqlSelectRelationParser();
99 $sql = 'FROM orphan; SELECT 1 UNION FROM union_hidden; SELECT 1 INTERSECT FROM intersect_hidden; SELECT 1 EXCEPT FROM except_hidden; SELECT * FROM visible';
100
101 self::assertSame(['visible'], $parser->fromClauses($sql));
102 self::assertSame(['users'], $parser->tableNames('SELECT * FROM users FROM ignored'));
103 self::assertSame(['users FROM ignored'], $parser->fromClauses('SELECT * FROM users FROM ignored'));
104 self::assertSame([], $parser->tableNames('FROM orphan'));
105 }
106
107 public function testFindsParenthesizedJoinedRelationsButNotDerivedProducers(): void
108 {
109 $parser = new MySqlSelectRelationParser();
110 $groupedSql = 'SELECT * FROM (app.users JOIN logs.orders ON TRUE) grouped, extra';
111
112 self::assertSame(
113 ['users', 'orders', 'extra'],
114 $parser->tableNames($groupedSql),
115 );
116 self::assertSame([
117 ['name' => 'users', 'start' => 15, 'unqualifiedStart' => 19, 'end' => 24],
118 ['name' => 'orders', 'start' => 30, 'unqualifiedStart' => 35, 'end' => 41],
119 ['name' => 'extra', 'start' => 60, 'unqualifiedStart' => 60, 'end' => 65],
120 ], $parser->references($groupedSql));
121 self::assertSame([], $parser->tableNames('SELECT * FROM () empty_group'));
122 self::assertSame(['extra'], $parser->tableNames('SELECT * FROM (), extra'));
123 self::assertSame([], $parser->tableNames('SELECT * FROM (values (1)) produced'));
124 self::assertSame([], $parser->tableNames('SELECT * FROM (VALUES rogue, leaked) produced'));
125 self::assertSame([], $parser->tableNames('SELECT * FROM (WITH rogue, leaked) produced'));
126 self::assertSame(
127 ['users'],
128 $parser->tableNames('SELECT * FROM (SELECT id, name FROM app.users) produced'),
129 );
130 self::assertSame(
131 ['users', 'orders'],
132 $parser->tableNames('SELECT * FROM users, orders'),
133 );
134 }
135
136 public function testNestedSelectFromClausesStopAtTheirOwnScopeBoundary(): void
137 {
138 $parser = new MySqlSelectRelationParser();
139
140 self::assertSame(
141 ['(SELECT * FROM inner_table WHERE id = 1) derived', 'inner_table'],
142 $parser->fromClauses(
143 'SELECT * FROM (SELECT * FROM inner_table WHERE id = 1) derived WHERE active = 1',
144 ),
145 );
146 self::assertSame(
147 ['(SELECT * FROM inner_table) derived', 'inner_table'],
148 $parser->fromClauses('SELECT * FROM (SELECT * FROM inner_table) derived WHERE active = 1'),
149 );
150 }
151
152 public function testParenthesizedRelationOffsetsDoNotIncludeFollowingAlias(): void
153 {
154 $sql = 'SELECT * FROM (app.users) grouped, extra';
155
156 self::assertSame([
157 ['name' => 'users', 'start' => 24, 'unqualifiedStart' => 28, 'end' => 33],
158 ['name' => 'extra', 'start' => 44, 'unqualifiedStart' => 44, 'end' => 49],
159 ], (new MySqlSelectRelationParser())->references($sql));
160 }
161
162 public function testDoesNotTreatQuotedFunctionNameAsRelation(): void
163 {
164 self::assertSame(
165 [],
166 (new MySqlSelectRelationParser())->tableNames('SELECT * FROM `json_table`(payload, "$") j'),
167 );
168 }
169
170 /**
171
172 * @param list<string> $expected
173
174 */
175 #[DataProvider('providerMySqlFromTerminators')]
176 public function testStopsFromClauseAtEveryMySqlBoundary(string $suffix, array $expected): void
177 {
178 self::assertSame(
179 $expected,
180 (new MySqlSelectRelationParser())->fromClauses('SELECT * FROM users ' . $suffix),
181 );
182 }
183
184 /**
185
186 * @return iterable<string, array{string, list<string>}>
187
188 */
189 public static function providerMySqlFromTerminators(): iterable
190 {
191 yield 'where' => ['WHERE active = 1', ['users']];
192 yield 'group by' => ['GROUP BY id', ['users']];
193 yield 'having' => ['HAVING count(*) > 1', ['users']];
194 yield 'window' => ['WINDOW w AS ()', ['users']];
195 yield 'order by' => ['ORDER BY id', ['users']];
196 yield 'limit' => ['LIMIT 1', ['users']];
197 yield 'procedure' => ['PROCEDURE ANALYSE()', ['users']];
198 yield 'into' => ['INTO OUTFILE "/tmp/result"', ['users']];
199 yield 'for' => ['FOR UPDATE', ['users']];
200 yield 'lock' => ['LOCK IN SHARE MODE', ['users']];
201 yield 'union' => ['UNION SELECT * FROM archived', ['users', 'archived']];
202 yield 'intersect' => ['INTERSECT SELECT * FROM archived', ['users', 'archived']];
203 yield 'except' => ['EXCEPT SELECT * FROM archived', ['users', 'archived']];
204 }
205
206 public function testRejectsNonMySqlSourceForms(): void
207 {
208 $parser = new MySqlSelectRelationParser();
209
210 self::assertSame([], $parser->tableNames("SELECT * FROM 'literal'"));
211 self::assertSame([], $parser->tableNames('SELECT * FROM VALUES (1)'));
212 self::assertSame([], $parser->tableNames('SELECT * FROM +invalid'));
213 self::assertSame([], $parser->tableNames('SELECT * FROM ``'));
214 self::assertSame([], $parser->tableNames('SELECT * FROM `unterminated'));
215 self::assertSame([], $parser->tableNames('SELECT * FROM SELECT'));
216 self::assertSame([], $parser->tableNames('SELECT * FROM WITH'));
217 }
218}
219