packages/ztd-query-postgres/tests/Unit/Sql/Relation/PgSqlSelectRelationParserTest.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\Postgres\Sql\PgSqlLexerProfile;
12use ZtdQuery\Platform\Postgres\Sql\Relation\PgSqlSelectRelationParser;
13
14#[CoversClass(PgSqlSelectRelationParser::class)]
15#[UsesClass(PgSqlLexerProfile::class)]
16#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Relation\FromClause::class)]
17#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Relation\RelationReference::class)]
18final class PgSqlSelectRelationParserTest extends TestCase
19{
20 public function testTableNamesFindsPostgreSqlRelationsWithoutTableFunctions(): void
21 {
22 $sql = 'SELECT * FROM generate_series(1, 3) day LEFT JOIN LATERAL (SELECT * FROM ONLY public."orders") totals ON TRUE JOIN audit_log a ON TRUE';
23
24 self::assertSame(
25 ['audit_log', 'orders'],
26 (new PgSqlSelectRelationParser())->tableNames($sql),
27 );
28 }
29
30 public function testFromClausesUsePostgreSqlBoundaries(): void
31 {
32 $parser = new PgSqlSelectRelationParser();
33
34 self::assertSame(
35 ['users', 'archived'],
36 $parser->fromClauses('SELECT * FROM users FETCH FIRST 1 ROW; SELECT * FROM archived FOR UPDATE'),
37 );
38 }
39
40 public function testUnqualifyReferencesPreserveOffsetsAndUnqualifyOnlyTargets(): void
41 {
42 $parser = new PgSqlSelectRelationParser();
43 $sql = 'SELECT * FROM public."users" u JOIN logs.audit_log a ON TRUE';
44
45 self::assertSame(['users', 'audit_log'], array_column($parser->references($sql), 'name'));
46 self::assertSame(
47 'SELECT * FROM "users" u JOIN logs.audit_log a ON TRUE',
48 $parser->unqualify($sql, ['users']),
49 );
50 }
51
52 public function testReferencesExposeExactQualifiedIdentifierOffsets(): void
53 {
54 $parser = new PgSqlSelectRelationParser();
55 $sql = 'SELECT * FROM public."a""b" a JOIN users u ON TRUE JOIN logs.orders o ON TRUE';
56
57 self::assertSame([
58 [
59 'name' => 'a"b',
60 'start' => 14,
61 'unqualifiedStart' => 21,
62 'end' => 27,
63 ],
64 [
65 'name' => 'users',
66 'start' => 35,
67 'unqualifiedStart' => 35,
68 'end' => 40,
69 ],
70 [
71 'name' => 'orders',
72 'start' => 56,
73 'unqualifiedStart' => 61,
74 'end' => 67,
75 ],
76 ], $parser->references($sql));
77 }
78
79 public function testUnqualifiesMultipleTargetsCaseInsensitivelyFromRightToLeft(): void
80 {
81 $sql = 'SELECT * FROM users u JOIN public.Users u2 ON TRUE JOIN logs.orders o ON TRUE';
82
83 self::assertSame(
84 'SELECT * FROM users u JOIN Users u2 ON TRUE JOIN orders o ON TRUE',
85 (new PgSqlSelectRelationParser())->unqualify($sql, ['USERS', 'ORDERS']),
86 );
87 }
88
89 public function testKeepsOneCaseInsensitiveRelationName(): void
90 {
91 self::assertSame(
92 ['Users'],
93 (new PgSqlSelectRelationParser())->tableNames('SELECT * FROM Users JOIN users ON TRUE'),
94 );
95 }
96
97 public function testSetOperatorsAndRepeatedFromCloseTheCurrentSelectScope(): void
98 {
99 $parser = new PgSqlSelectRelationParser();
100 $sql = 'FROM orphan; SELECT 1 UNION FROM union_hidden; SELECT 1 INTERSECT FROM intersect_hidden; SELECT 1 EXCEPT FROM except_hidden; SELECT * FROM visible';
101
102 self::assertSame(['visible'], $parser->fromClauses($sql));
103 self::assertSame(['users'], $parser->tableNames('SELECT * FROM users FROM ignored'));
104 self::assertSame(['users FROM ignored'], $parser->fromClauses('SELECT * FROM users FROM ignored'));
105 self::assertSame([], $parser->tableNames('FROM orphan'));
106 }
107
108 public function testFindsParenthesizedJoinedRelationsButNotDerivedProducers(): void
109 {
110 $parser = new PgSqlSelectRelationParser();
111 $groupedSql = 'SELECT * FROM (public.users JOIN logs.orders ON TRUE) grouped, extra';
112
113 self::assertSame(['users', 'orders', 'extra'], $parser->tableNames($groupedSql));
114 self::assertSame([
115 ['name' => 'users', 'start' => 15, 'unqualifiedStart' => 22, 'end' => 27],
116 ['name' => 'orders', 'start' => 33, 'unqualifiedStart' => 38, 'end' => 44],
117 ['name' => 'extra', 'start' => 63, 'unqualifiedStart' => 63, 'end' => 68],
118 ], $parser->references($groupedSql));
119 self::assertSame([], $parser->tableNames('SELECT * FROM () empty_group'));
120 self::assertSame(['extra'], $parser->tableNames('SELECT * FROM (), extra'));
121 self::assertSame([], $parser->tableNames('SELECT * FROM (VALUES rogue, leaked) produced'));
122 self::assertSame([], $parser->tableNames('SELECT * FROM (WITH rogue, leaked) produced'));
123 self::assertSame(
124 ['users'],
125 $parser->tableNames('SELECT * FROM (SELECT id, name FROM public.users) produced'),
126 );
127 self::assertSame(['users', 'orders'], $parser->tableNames('SELECT * FROM users, orders'));
128 }
129
130 public function testNestedSelectFromClausesStopAtTheirOwnScopeBoundary(): void
131 {
132 $parser = new PgSqlSelectRelationParser();
133
134 self::assertSame(
135 ['(SELECT * FROM inner_table) derived', 'inner_table'],
136 $parser->fromClauses('SELECT * FROM (SELECT * FROM inner_table) derived WHERE active = 1'),
137 );
138 self::assertSame(
139 ['(SELECT * FROM inner_table WHERE id = 1) derived', 'inner_table'],
140 $parser->fromClauses(
141 'SELECT * FROM (SELECT * FROM inner_table WHERE id = 1) derived WHERE active = 1',
142 ),
143 );
144 }
145
146 public function testReferencesDoNotReadPastTheFromClauseBoundary(): void
147 {
148 self::assertSame(
149 ['users'],
150 (new PgSqlSelectRelationParser())->tableNames('SELECT * FROM users WHERE TRUE JOIN leaked ON TRUE'),
151 );
152 }
153
154 public function testParenthesizedRelationOffsetsDoNotIncludeFollowingAlias(): void
155 {
156 $sql = 'SELECT * FROM (public.users) grouped, extra';
157
158 self::assertSame([
159 ['name' => 'users', 'start' => 24, 'unqualifiedStart' => 31, 'end' => 36],
160 ['name' => 'extra', 'start' => 47, 'unqualifiedStart' => 47, 'end' => 52],
161 ], (new PgSqlSelectRelationParser())->references($sql));
162 }
163
164 public function testDoesNotTreatQuotedFunctionNameAsRelation(): void
165 {
166 self::assertSame(
167 [],
168 (new PgSqlSelectRelationParser())->tableNames('SELECT * FROM "generate_series"(1, 3) day'),
169 );
170 }
171
172 /**
173 * @param list<string> $expected
174 */
175 #[DataProvider('providerPostgresFromTerminators')]
176 public function testStopsFromClauseAtEveryPostgresBoundary(string $suffix, array $expected): void
177 {
178 self::assertSame(
179 $expected,
180 (new PgSqlSelectRelationParser())->fromClauses('SELECT * FROM users ' . $suffix),
181 );
182 }
183
184 /**
185 * @return iterable<string, array{string, list<string>}>
186 */
187 public static function providerPostgresFromTerminators(): iterable
188 {
189 yield 'where' => ['WHERE active = TRUE', ['users']];
190 yield 'group by' => ['GROUP BY id', ['users']];
191 yield 'having' => ['HAVING count(*) > 1', ['users']];
192 yield 'window' => ['WINDOW w AS ()', ['users']];
193 yield 'order by' => ['ORDER BY id', ['users']];
194 yield 'limit' => ['LIMIT 1', ['users']];
195 yield 'offset' => ['OFFSET 1', ['users']];
196 yield 'fetch' => ['FETCH FIRST 1 ROW', ['users']];
197 yield 'for' => ['FOR UPDATE', ['users']];
198 yield 'returning' => ['RETURNING id', ['users']];
199 yield 'union' => ['UNION SELECT * FROM archived', ['users', 'archived']];
200 yield 'intersect' => ['INTERSECT SELECT * FROM archived', ['users', 'archived']];
201 yield 'except' => ['EXCEPT SELECT * FROM archived', ['users', 'archived']];
202 }
203
204 public function testRejectsMySqlAndLiteralSourceForms(): void
205 {
206 $parser = new PgSqlSelectRelationParser();
207
208 self::assertSame([], $parser->tableNames('SELECT * FROM `users`'));
209 self::assertSame([], $parser->tableNames("SELECT * FROM 'literal'"));
210 self::assertSame([], $parser->tableNames('SELECT * FROM VALUES (1)'));
211 self::assertSame([], $parser->tableNames('SELECT * FROM ""'));
212 self::assertSame([], $parser->tableNames('SELECT * FROM "unterminated'));
213 self::assertSame([], $parser->tableNames('SELECT * FROM SELECT'));
214 self::assertSame([], $parser->tableNames('SELECT * FROM WITH'));
215 }
216}
217