packages/ztd-query-mysql/tests/Unit/Rewrite/Cte/MySqlCteShadowComposerTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\Cte;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Platform\MySql\Rewrite\Cte\MySqlCteShadowComposer;
11
12#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\ReferenceReader::class)]
13#[CoversClass(MySqlCteShadowComposer::class)]
14#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\MySqlSelectRelationParser::class)]
15#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
16#[CoversClass(\ZtdQuery\Platform\MySql\Sql\Cte\HeaderParser::class)]
17#[CoversClass(\ZtdQuery\Platform\MySql\Sql\Cte\IdentifierReferences::class)]
18final class MySqlCteShadowComposerTest extends TestCase
19{
20 public function testIncludesTransitivelyReferencedShadowCtes(): void
21 {
22 self::assertSame(
23 "WITH \"items\" AS (SELECT 1 AS id),\n\"item_view\" AS (SELECT * FROM items)\nSELECT * FROM item_view",
24 (new MySqlCteShadowComposer())->compose(
25 'SELECT * FROM item_view',
26 [
27 'items' => '"items" AS (SELECT 1 AS id)',
28 'item_view' => '"item_view" AS (SELECT * FROM items)',
29 'unrelated' => '"unrelated" AS (SELECT 2 AS id)',
30 ],
31 ),
32 );
33 }
34
35 public function testAddsShadowsAfterRecursiveModifier(): void
36 {
37 $sql = 'WITH RECURSIVE tree AS (SELECT id FROM nodes UNION ALL SELECT n.id FROM nodes n JOIN tree t ON n.parent_id = t.id) SELECT * FROM tree';
38
39 self::assertSame(
40 "WITH RECURSIVE \"nodes\" AS (SELECT 1 AS id),\n tree AS (SELECT id FROM nodes UNION ALL SELECT n.id FROM nodes n JOIN tree t ON n.parent_id = t.id) SELECT * FROM tree",
41 (new MySqlCteShadowComposer())->compose($sql, ['nodes' => '"nodes" AS (SELECT 1 AS id)']),
42 );
43 }
44
45 public function testPreservesUserCteThatOwnsThePhysicalTableName(): void
46 {
47 $sql = 'WITH users AS (SELECT 1 AS id), filtered AS (SELECT * FROM users) SELECT * FROM filtered';
48
49 self::assertSame(
50 $sql,
51 (new MySqlCteShadowComposer())->compose($sql, ['users' => '"users" AS (SELECT 2 AS id)']),
52 );
53 }
54
55 public function testInjectsReferencedBaseTableBeforeUserCtes(): void
56 {
57 $sql = 'WITH filtered AS (SELECT * FROM users WHERE active) SELECT * FROM filtered';
58
59 self::assertSame(
60 "WITH \"users\" AS (SELECT 1 AS id),\n filtered AS (SELECT * FROM users WHERE active) SELECT * FROM filtered",
61 (new MySqlCteShadowComposer())->compose($sql, ['users' => '"users" AS (SELECT 1 AS id)']),
62 );
63 }
64
65 public function testDoesNotTreatLiteralsCommentsOrIdentifierPrefixesAsReferences(): void
66 {
67 $sql = "SELECT 'users', superusers.id /* users */ FROM superusers";
68
69 self::assertSame(
70 $sql,
71 (new MySqlCteShadowComposer())->compose($sql, ['users' => '"users" AS (SELECT 1 AS id)']),
72 );
73 }
74
75 public function testDeclaredCteNamesReadsQuotedAndColumnListedCteNames(): void
76 {
77 self::assertSame(
78 ['first', 'second'],
79 (new MySqlCteShadowComposer())->declaredCteNames('WITH `first`(id) AS MATERIALIZED (SELECT 1), second AS (SELECT 2) SELECT * FROM second'),
80 );
81 }
82
83 public function testCarryPrefixCarriesTheCompleteCteHeaderOntoARewrittenDmlProjection(): void
84 {
85 $sql = 'WITH first AS (SELECT 1), second(id) AS (SELECT * FROM first) UPDATE users SET id = 2';
86
87 self::assertSame(
88 "WITH first AS (SELECT 1), second(id) AS (SELECT * FROM first)\nSELECT 2 AS id FROM users",
89 (new MySqlCteShadowComposer())->carryPrefix($sql, 'SELECT 2 AS id FROM users'),
90 );
91 }
92
93 public function testMergesProjectionCtesIntoTheOriginalHeader(): void
94 {
95 self::assertSame(
96 "WITH source AS (SELECT 1),\nprojected AS (SELECT * FROM source)\nSELECT * FROM projected",
97 (new MySqlCteShadowComposer())->carryPrefix(
98 'WITH source AS (SELECT 1) INSERT INTO target SELECT * FROM source',
99 'WITH projected AS (SELECT * FROM source) SELECT * FROM projected',
100 ),
101 );
102 }
103
104 public function testOrdersShadowCtesBeforeUserCtesThatReadThem(): void
105 {
106 self::assertSame(
107 "WITH users AS (SELECT 1 AS id),\nchosen AS (SELECT id FROM users)\nSELECT * FROM users WHERE id IN (SELECT id FROM chosen)",
108 (new MySqlCteShadowComposer())->carryPrefix(
109 'WITH chosen AS (SELECT id FROM users) UPDATE users SET id = 2',
110 'WITH users AS (SELECT 1 AS id) SELECT * FROM users WHERE id IN (SELECT id FROM chosen)',
111 ),
112 );
113 }
114
115 public function testStatementSqlExtractsTheStatementFollowingTheCteHeader(): void
116 {
117 self::assertSame(
118 'DELETE FROM users WHERE id IN (SELECT id FROM chosen)',
119 (new MySqlCteShadowComposer())->statementSql('WITH chosen AS (SELECT 1 AS id) DELETE FROM users WHERE id IN (SELECT id FROM chosen)'),
120 );
121 }
122
123 public function testExtractsMySqlDmlAfterHashCommentsInTheCteHeader(): void
124 {
125 $sql = "WITH RECURSIVE # modifier\nchosen AS (SELECT 1 AS id) # body\nDELETE FROM users WHERE id IN (SELECT id FROM chosen)";
126
127 self::assertSame(
128 'DELETE FROM users WHERE id IN (SELECT id FROM chosen)',
129 (new MySqlCteShadowComposer())->statementSql($sql),
130 );
131 }
132
133 public function testComposesAReferencedShadowWithoutAnExistingWithClause(): void
134 {
135 self::assertSame(
136 "WITH users AS (SELECT 1 AS id)\nSELECT * FROM users",
137 (new MySqlCteShadowComposer())->compose(
138 'SELECT * FROM users',
139 ['users' => 'users AS (SELECT 1 AS id)'],
140 ),
141 );
142 }
143
144 public function testComposesShadowOverSchemaQualifiedSelectSource(): void
145 {
146 self::assertSame(
147 "WITH users AS (SELECT 1 AS id)\nSELECT * FROM `users`",
148 (new MySqlCteShadowComposer())->compose(
149 'SELECT * FROM public.`users`',
150 ['users' => 'users AS (SELECT 1 AS id)'],
151 ),
152 );
153 }
154
155 public function testSkipsEntriesIndependentlyAndMatchesDeclaredNamesCaseInsensitively(): void
156 {
157 $composer = new MySqlCteShadowComposer();
158
159 self::assertSame(
160 "WITH orders AS (SELECT 2 AS id)\nSELECT * FROM orders",
161 $composer->compose(
162 'SELECT * FROM orders',
163 [
164 'users' => 'users AS (SELECT 1 AS id)',
165 'orders' => 'orders AS (SELECT 2 AS id)',
166 ],
167 ),
168 );
169 $declared = 'WITH users AS (SELECT 1 AS id) SELECT * FROM users';
170 self::assertSame(
171 $declared,
172 $composer->compose($declared, ['Users' => 'Users AS (SELECT 2 AS id)']),
173 );
174 self::assertStringContainsString(
175 'orders AS (SELECT 2 AS id)',
176 $composer->compose(
177 'WITH users AS (SELECT 1 AS id) SELECT * FROM users JOIN orders ON TRUE',
178 [
179 'orders' => 'orders AS (SELECT 2 AS id)',
180 'Users' => 'Users AS (SELECT 3 AS id)',
181 ],
182 ),
183 );
184 }
185
186 public function testHandlesAWithTokenWithoutFollowingHeaderTokens(): void
187 {
188 self::assertSame(
189 "WITH shadow AS (SELECT 1),\n",
190 (new MySqlCteShadowComposer())->compose('WITH', ['WITH' => 'shadow AS (SELECT 1)']),
191 );
192 }
193
194 public function testCarriesAnEmptyRewriteWithoutDereferencingMissingTokens(): void
195 {
196 self::assertSame(
197 "WITH source AS (SELECT 1)\n",
198 (new MySqlCteShadowComposer())->carryPrefix(
199 'WITH source AS (SELECT 1) UPDATE users SET id = 2',
200 '',
201 ),
202 );
203 }
204
205 public function testMergesRecursiveHeadersAndPreservesLeadingComments(): void
206 {
207 self::assertSame(
208 "/* lead */ WITH RECURSIVE projected AS (SELECT 2),\noriginal AS (SELECT 1)\nSELECT * FROM projected",
209 (new MySqlCteShadowComposer())->carryPrefix(
210 '/* lead */ WITH RECURSIVE original AS (SELECT 1) UPDATE users SET id = 2',
211 'WITH projected AS (SELECT 2) SELECT * FROM projected',
212 ),
213 );
214 }
215
216 public function testRecognizesRecursiveRewrittenHeaderDependencies(): void
217 {
218 self::assertSame(
219 "WITH source AS (SELECT 1),\nprojected AS (SELECT * FROM source)\nSELECT * FROM projected",
220 (new MySqlCteShadowComposer())->carryPrefix(
221 'WITH source AS (SELECT 1) INSERT INTO target SELECT * FROM source',
222 'WITH RECURSIVE projected AS (SELECT * FROM source) SELECT * FROM projected',
223 ),
224 );
225 }
226
227 public function testParsesRecursiveMaterializationAndNestedCteBodies(): void
228 {
229 $sql = 'WITH RECURSIVE `FIRST`(id) AS MATERIALIZED (SELECT (1)), second AS NOT MATERIALIZED (SELECT id FROM `FIRST`) DELETE FROM target';
230 $composer = new MySqlCteShadowComposer();
231
232 self::assertSame(['first', 'second'], $composer->declaredCteNames($sql));
233 self::assertSame('DELETE FROM target', $composer->statementSql($sql));
234 }
235
236 public function testHandlesNonHeadersIncompleteHeadersAndEmptyInput(): void
237 {
238 $composer = new MySqlCteShadowComposer();
239
240 self::assertSame([], $composer->declaredCteNames(''));
241 self::assertSame('SELECT 1', $composer->statementSql('SELECT 1'));
242 self::assertSame([], $composer->declaredCteNames('WITH only_name'));
243 self::assertSame(
244 'WITH x AS (SELECT 1)',
245 $composer->statementSql('WITH x AS (SELECT 1)'),
246 );
247 self::assertSame([], $composer->declaredCteNames('WITH x AS'));
248 self::assertSame([], $composer->declaredCteNames('WITH x AS NOT'));
249 self::assertSame([], $composer->declaredCteNames('WITH x AS (SELECT 1'));
250 self::assertSame(
251 ['first'],
252 $composer->declaredCteNames('WITH first AS (SELECT 1), broken'),
253 );
254 }
255
256 public function testUnquotesEmptyAndEscapedCteIdentifiers(): void
257 {
258 $composer = new MySqlCteShadowComposer();
259
260 self::assertSame([''], $composer->declaredCteNames('WITH `` AS (SELECT 1) SELECT 1'));
261 self::assertSame(
262 ['a`b', 'c`d'],
263 $composer->declaredCteNames(
264 'WITH `a``b` AS (SELECT 1), `c``d` AS (SELECT 2) SELECT 1',
265 ),
266 );
267 }
268}
269