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