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