packages/ztd-query-sqlite/tests/Unit/Schema/SqliteSchemaReflectorTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Schema;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Connection\ConnectionInterface;
11use ZtdQuery\Connection\StatementInterface;
12use ZtdQuery\Platform\Sqlite\Schema\SqliteSchemaReflector;
13
14#[CoversClass(SqliteSchemaReflector::class)]
15#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\RelationSourceParser::class)]
16#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexerProfile::class)]
17#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\SqliteSelectRelationParser::class)]
18#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\View\SqliteViewDefinitionParser::class)]
19final class SqliteSchemaReflectorTest extends TestCase
20{
21 public function testReflectViewsReturnsEmptyWhenQueryFails(): void
22 {
23 $connection = self::createStub(ConnectionInterface::class);
24 $connection->method('query')->willReturn(false);
25
26 self::assertSame([], (new SqliteSchemaReflector($connection))->reflectViews());
27 }
28
29 public function testReflectViewsPrefersTemporaryAndSkipsMalformedRows(): void
30 {
31 $rows = [
32 ['name' => null, 'sql' => 'CREATE VIEW ignored AS SELECT 1'],
33 ['name' => '', 'sql' => 'CREATE VIEW ignored AS SELECT 1'],
34 ['name' => 'non_string', 'sql' => null],
35 ['name' => 'invalid', 'sql' => 'CREATE VIEW invalid'],
36 ['name' => 'active_users', 'sql' => 'CREATE TEMP VIEW active_users AS SELECT * FROM temp.users'],
37 ['name' => 'active_users', 'sql' => 'CREATE VIEW active_users AS SELECT * FROM main.users'],
38 ['name' => 'all_users', 'sql' => 'CREATE VIEW all_users AS SELECT * FROM main.users'],
39 ];
40 $expectedQuery = 'SELECT name, sql FROM (SELECT name, sql, 0 AS precedence FROM sqlite_temp_master '
41 . "WHERE type='view' UNION ALL SELECT name, sql, 1 AS precedence FROM sqlite_master "
42 . "WHERE type='view') ORDER BY precedence, name";
43
44 $statement = self::createStub(StatementInterface::class);
45 $statement->method('fetchAll')->willReturn($rows);
46 $connection = self::createMock(ConnectionInterface::class);
47 $connection->expects(self::once())->method('query')->with($expectedQuery)->willReturn($statement);
48
49 $definitions = (new SqliteSchemaReflector($connection))->reflectViews();
50
51 self::assertSame(['active_users', 'all_users'], array_keys($definitions));
52 self::assertSame('SELECT * FROM temp.users', $definitions['active_users']->query);
53 }
54
55 public function testGetCreateStatementReturnsNullWhenQueryFails(): void
56 {
57 $connection = static::createStub(ConnectionInterface::class);
58 $connection->method('query')->willReturn(false);
59
60 $reflector = new SqliteSchemaReflector($connection);
61 self::assertNull($reflector->getCreateStatement('users'));
62 }
63
64 public function testGetCreateStatementReturnsNullWhenNoRows(): void
65 {
66 $statement = static::createStub(StatementInterface::class);
67 $statement->method('fetchAll')->willReturn([]);
68
69 $connection = static::createStub(ConnectionInterface::class);
70 $connection->method('query')->willReturn($statement);
71
72 $reflector = new SqliteSchemaReflector($connection);
73 self::assertNull($reflector->getCreateStatement('users'));
74 }
75
76 public function testGetCreateStatementReturnsSql(): void
77 {
78 $createSql = 'CREATE TABLE users (id INTEGER PRIMARY KEY)';
79 $statement = static::createStub(StatementInterface::class);
80 $statement->method('fetchAll')->willReturn([['sql' => $createSql]]);
81
82 $connection = static::createStub(ConnectionInterface::class);
83 $connection->method('query')->willReturn($statement);
84
85 $reflector = new SqliteSchemaReflector($connection);
86 self::assertSame($createSql, $reflector->getCreateStatement('users'));
87 }
88
89 public function testGetCreateStatementReturnsNullWhenSqlNotString(): void
90 {
91 $statement = static::createStub(StatementInterface::class);
92 $statement->method('fetchAll')->willReturn([['sql' => null]]);
93
94 $connection = static::createStub(ConnectionInterface::class);
95 $connection->method('query')->willReturn($statement);
96
97 $reflector = new SqliteSchemaReflector($connection);
98 self::assertNull($reflector->getCreateStatement('users'));
99 }
100
101 public function testReflectAllReturnsEmptyWhenQueryFails(): void
102 {
103 $connection = static::createStub(ConnectionInterface::class);
104 $connection->method('query')->willReturn(false);
105
106 $reflector = new SqliteSchemaReflector($connection);
107 self::assertSame([], $reflector->reflectAll());
108 }
109
110 public function testReflectAllReturnsTables(): void
111 {
112 $statement = static::createStub(StatementInterface::class);
113 $statement->method('fetchAll')->willReturn([
114 ['name' => 'users', 'sql' => 'CREATE TABLE users (id INTEGER PRIMARY KEY)'],
115 ['name' => 'orders', 'sql' => 'CREATE TABLE orders (id INTEGER PRIMARY KEY)'],
116 ]);
117
118 $connection = static::createStub(ConnectionInterface::class);
119 $connection->method('query')->willReturn($statement);
120
121 $reflector = new SqliteSchemaReflector($connection);
122 $result = $reflector->reflectAll();
123
124 self::assertCount(2, $result);
125 self::assertArrayHasKey('users', $result);
126 self::assertArrayHasKey('orders', $result);
127 }
128
129 public function testReflectAllPrefersTemporaryTableWhenNamesCollide(): void
130 {
131 $rows = [
132 ['name' => 'staging', 'sql' => 'CREATE TABLE staging (temporary_value TEXT)'],
133 ['name' => 'staging', 'sql' => 'CREATE TABLE staging (persistent_value TEXT)'],
134 ['name' => 'after', 'sql' => 'CREATE TABLE after (id INTEGER)'],
135 ];
136 $expectedQuery = 'SELECT name, sql FROM (SELECT name, sql, 0 AS precedence FROM sqlite_temp_master '
137 . "WHERE type='table' AND name NOT LIKE 'sqlite_%' UNION ALL "
138 . 'SELECT name, sql, 1 AS precedence FROM sqlite_master '
139 . "WHERE type='table' AND name NOT LIKE 'sqlite_%') ORDER BY precedence, name";
140
141 $statement = self::createStub(StatementInterface::class);
142 $statement->method('fetchAll')->willReturn($rows);
143 $connection = self::createMock(ConnectionInterface::class);
144 $connection->expects(self::once())->method('query')->with($expectedQuery)->willReturn($statement);
145
146 $result = (new SqliteSchemaReflector($connection))->reflectAll();
147
148 self::assertSame('CREATE TABLE staging (temporary_value TEXT)', $result['staging']);
149 self::assertSame('CREATE TABLE after (id INTEGER)', $result['after']);
150 }
151
152 public function testReflectAllSkipsInvalidRows(): void
153 {
154 $statement = static::createStub(StatementInterface::class);
155 $statement->method('fetchAll')->willReturn([
156 ['name' => 'users', 'sql' => 'CREATE TABLE users (id INTEGER PRIMARY KEY)'],
157 ['name' => '', 'sql' => 'CREATE TABLE empty (id INTEGER)'],
158 ['name' => 'orders', 'sql' => ''],
159 ['name' => null, 'sql' => 'CREATE TABLE null_name (id INTEGER)'],
160 ]);
161
162 $connection = static::createStub(ConnectionInterface::class);
163 $connection->method('query')->willReturn($statement);
164
165 $reflector = new SqliteSchemaReflector($connection);
166 $result = $reflector->reflectAll();
167
168 self::assertCount(1, $result);
169 self::assertArrayHasKey('users', $result);
170 }
171
172 public function testGetCreateStatementEscapesSingleQuotesInTableName(): void
173 {
174 $statement = static::createStub(StatementInterface::class);
175 $statement->method('fetchAll')->willReturn([['sql' => 'CREATE TABLE t (id INTEGER)']]);
176
177 $connection = static::createStub(ConnectionInterface::class);
178 $connection->method('query')->willReturn($statement);
179
180 $reflector = new SqliteSchemaReflector($connection);
181 $result = $reflector->getCreateStatement("user's");
182 self::assertSame('CREATE TABLE t (id INTEGER)', $result);
183 }
184
185 public function testReflectAllReturnsSqlValues(): void
186 {
187 $statement = static::createStub(StatementInterface::class);
188 $statement->method('fetchAll')->willReturn([
189 ['name' => 'users', 'sql' => 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)'],
190 ]);
191
192 $connection = static::createStub(ConnectionInterface::class);
193 $connection->method('query')->willReturn($statement);
194
195 $reflector = new SqliteSchemaReflector($connection);
196 $result = $reflector->reflectAll();
197 self::assertSame('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)', $result['users']);
198 }
199
200 public function testReflectAllSkipsRowsWithMissingName(): void
201 {
202 $statement = static::createStub(StatementInterface::class);
203 $statement->method('fetchAll')->willReturn([
204 ['sql' => 'CREATE TABLE t (id INTEGER)'],
205 ]);
206
207 $connection = static::createStub(ConnectionInterface::class);
208 $connection->method('query')->willReturn($statement);
209
210 $reflector = new SqliteSchemaReflector($connection);
211 $result = $reflector->reflectAll();
212 self::assertSame([], $result);
213 }
214
215 public function testReflectAllSkipsRowsWithNonStringSql(): void
216 {
217 $statement = static::createStub(StatementInterface::class);
218 $statement->method('fetchAll')->willReturn([
219 ['name' => 'users', 'sql' => 42],
220 ]);
221
222 $connection = static::createStub(ConnectionInterface::class);
223 $connection->method('query')->willReturn($statement);
224
225 $reflector = new SqliteSchemaReflector($connection);
226 $result = $reflector->reflectAll();
227 self::assertSame([], $result);
228 }
229
230 public function testReflectAllSkipsRowsWithNonStringName(): void
231 {
232 $statement = static::createStub(StatementInterface::class);
233 $statement->method('fetchAll')->willReturn([
234 ['name' => 42, 'sql' => 'CREATE TABLE t (id INTEGER)'],
235 ]);
236
237 $connection = static::createStub(ConnectionInterface::class);
238 $connection->method('query')->willReturn($statement);
239
240 $reflector = new SqliteSchemaReflector($connection);
241 $result = $reflector->reflectAll();
242 self::assertSame([], $result);
243 }
244
245 public function testReflectAllMultipleValidTables(): void
246 {
247 $statement = static::createStub(StatementInterface::class);
248 $statement->method('fetchAll')->willReturn([
249 ['name' => 'users', 'sql' => 'CREATE TABLE users (id INTEGER)'],
250 ['name' => 'orders', 'sql' => 'CREATE TABLE orders (id INTEGER)'],
251 ['name' => 'items', 'sql' => 'CREATE TABLE items (id INTEGER)'],
252 ]);
253
254 $connection = static::createStub(ConnectionInterface::class);
255 $connection->method('query')->willReturn($statement);
256
257 $reflector = new SqliteSchemaReflector($connection);
258 $result = $reflector->reflectAll();
259 self::assertCount(3, $result);
260 self::assertSame('CREATE TABLE users (id INTEGER)', $result['users']);
261 self::assertSame('CREATE TABLE orders (id INTEGER)', $result['orders']);
262 self::assertSame('CREATE TABLE items (id INTEGER)', $result['items']);
263 }
264
265 public function testReflectAllSkipsRowWithEmptyNameAndValidSql(): void
266 {
267 $statement = static::createStub(StatementInterface::class);
268 $statement->method('fetchAll')->willReturn([
269 ['name' => '', 'sql' => 'CREATE TABLE t (id INTEGER)'],
270 ]);
271
272 $connection = static::createStub(ConnectionInterface::class);
273 $connection->method('query')->willReturn($statement);
274
275 $reflector = new SqliteSchemaReflector($connection);
276 $result = $reflector->reflectAll();
277 self::assertSame([], $result);
278 }
279
280 public function testReflectAllSkipsRowWithValidNameAndEmptySql(): void
281 {
282 $statement = static::createStub(StatementInterface::class);
283 $statement->method('fetchAll')->willReturn([
284 ['name' => 'users', 'sql' => ''],
285 ]);
286
287 $connection = static::createStub(ConnectionInterface::class);
288 $connection->method('query')->willReturn($statement);
289
290 $reflector = new SqliteSchemaReflector($connection);
291 $result = $reflector->reflectAll();
292 self::assertSame([], $result);
293 }
294
295 public function testGetCreateStatementWithSingleQuoteInTableName(): void
296 {
297 $rows = [
298 ['sql' => "CREATE TABLE \"it's\" (id INTEGER)"],
299 ];
300 $expectedQuery = "SELECT sql FROM (SELECT sql, 0 AS precedence FROM sqlite_temp_master WHERE type='table' AND name='it''s' "
301 . "UNION ALL SELECT sql, 1 AS precedence FROM sqlite_master WHERE type='table' AND name='it''s') "
302 . 'ORDER BY precedence LIMIT 1';
303
304 $statement = self::createStub(StatementInterface::class);
305 $statement->method('fetchAll')->willReturn($rows);
306 $connection = self::createMock(ConnectionInterface::class);
307 $connection->expects(self::once())->method('query')->with($expectedQuery)->willReturn($statement);
308
309 $reflector = new SqliteSchemaReflector($connection);
310 $result = $reflector->getCreateStatement("it's");
311 self::assertSame("CREATE TABLE \"it's\" (id INTEGER)", $result);
312 }
313
314 public function testReflectAllKeepsOnlyValidRowsAmongInvalid(): void
315 {
316 $statement = static::createStub(StatementInterface::class);
317 $statement->method('fetchAll')->willReturn([
318 ['name' => null, 'sql' => 'CREATE TABLE a (id INTEGER)'],
319 ['name' => 'good', 'sql' => 'CREATE TABLE good (id INTEGER)'],
320 ['name' => 'bad', 'sql' => null],
321 ['name' => '', 'sql' => 'CREATE TABLE empty (id INTEGER)'],
322 ['name' => 'also_good', 'sql' => 'CREATE TABLE also_good (id INTEGER)'],
323 ]);
324
325 $connection = static::createStub(ConnectionInterface::class);
326 $connection->method('query')->willReturn($statement);
327
328 $reflector = new SqliteSchemaReflector($connection);
329 $result = $reflector->reflectAll();
330 self::assertCount(2, $result);
331 self::assertArrayHasKey('good', $result);
332 self::assertArrayHasKey('also_good', $result);
333 }
334
335 public function testGetCreateStatementReturnsNullWhenSqlKeyMissing(): void
336 {
337 $statement = static::createStub(StatementInterface::class);
338 $statement->method('fetchAll')->willReturn([
339 ['other_key' => 'value'],
340 ]);
341
342 $connection = static::createStub(ConnectionInterface::class);
343 $connection->method('query')->willReturn($statement);
344
345 $reflector = new SqliteSchemaReflector($connection);
346 self::assertNull($reflector->getCreateStatement('users'));
347 }
348
349 public function testReflectAllSkipsMissingSqlKey(): void
350 {
351 $statement = static::createStub(StatementInterface::class);
352 $statement->method('fetchAll')->willReturn([
353 ['name' => 'users'],
354 ]);
355
356 $connection = static::createStub(ConnectionInterface::class);
357 $connection->method('query')->willReturn($statement);
358
359 $reflector = new SqliteSchemaReflector($connection);
360 $result = $reflector->reflectAll();
361 self::assertSame([], $result);
362 }
363}
364