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