packages/ztd-query-pdo-adapter/tests/Integration/SqliteCteShadowingTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration;
6
7use PDO;
8use PHPUnit\Framework\Attributes\CoversNothing;
9use PHPUnit\Framework\Attributes\Large;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Adapter\Pdo\ZtdPdo;
12
13/**
14 * @requires extension pdo_sqlite
15 * @phpstan-type Row array<string, mixed>
16 */
17#[CoversNothing]
18#[Large]
19final class SqliteCteShadowingTest extends TestCase
20{
21 public function testSelectOnCleanShadowReturnsEmpty(): void
22 {
23 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
24 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
25 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
26
27 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
28
29 $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
30 self::assertNotFalse($stmt);
31 /** @var list<Row> $rows */
32 $rows = $stmt->fetchAll();
33 self::assertCount(0, $rows);
34 }
35
36 public function testInsertDoesNotModifyPhysicalDatabase(): void
37 {
38 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
39 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
40 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
41
42 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
43
44 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
45
46 $stmt = $rawPdo->query('SELECT * FROM users');
47 self::assertNotFalse($stmt);
48 /** @var list<Row> $rawRows */
49 $rawRows = $stmt->fetchAll();
50 self::assertCount(2, $rawRows);
51 }
52
53 public function testInsertIsVisibleViaZtdSelect(): void
54 {
55 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
56 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
57 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
58
59 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
60
61 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
62
63 $stmt = $ztdPdo->query('SELECT name, age FROM users ORDER BY name');
64 self::assertNotFalse($stmt);
65 /** @var list<Row> $ztdRows */
66 $ztdRows = $stmt->fetchAll();
67 self::assertCount(1, $ztdRows);
68 self::assertSame('Charlie', $ztdRows[0]['name']);
69 self::assertEquals(35, $ztdRows[0]['age']);
70 }
71
72 public function testMultipleInsertsAccumulate(): void
73 {
74 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
75 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
76 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
77
78 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
79
80 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
81 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Diana', 28)");
82
83 $stmt = $ztdPdo->query('SELECT name FROM users ORDER BY name');
84 self::assertNotFalse($stmt);
85 /** @var list<Row> $ztdRows */
86 $ztdRows = $stmt->fetchAll();
87 self::assertCount(2, $ztdRows);
88
89 $names = array_column($ztdRows, 'name');
90 self::assertSame(['Charlie', 'Diana'], $names, 'Both inserted names must appear in exact order');
91 }
92
93 public function testSelectWithWhereOnShadowData(): void
94 {
95 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
96 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
97 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
98
99 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
100
101 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
102 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Diana', 28)");
103
104 $stmt = $ztdPdo->query('SELECT * FROM users WHERE age > 30');
105 self::assertNotFalse($stmt);
106 /** @var list<Row> $rows */
107 $rows = $stmt->fetchAll();
108 self::assertCount(1, $rows);
109 self::assertSame('Charlie', $rows[0]['name']);
110 }
111
112 public function testPhysicalDatabaseRemainsUnchangedAfterMutations(): void
113 {
114 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
115 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
116 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
117
118 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
119
120 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
121 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Diana', 28)");
122
123 $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
124 self::assertNotFalse($stmt);
125 /** @var list<Row> $rawRows */
126 $rawRows = $stmt->fetchAll();
127 self::assertCount(2, $rawRows);
128 self::assertSame('Alice', $rawRows[0]['name']);
129 self::assertSame('Bob', $rawRows[1]['name']);
130 }
131
132 public function testDisableZtdBypassesRewriting(): void
133 {
134 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
135 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
136 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
137
138 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
139 $ztdPdo->disableZtd();
140
141 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Direct', 40)");
142
143 $stmt = $rawPdo->query('SELECT * FROM users');
144 self::assertNotFalse($stmt);
145 /** @var list<Row> $rawRows */
146 $rawRows = $stmt->fetchAll();
147 self::assertCount(3, $rawRows);
148
149 $rawPdo->exec("DELETE FROM users WHERE name = 'Direct'");
150 }
151
152 public function testEnableDisableToggle(): void
153 {
154 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
155 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
156 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
157
158 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
159
160 self::assertTrue($ztdPdo->isZtdEnabled());
161 $ztdPdo->disableZtd();
162 self::assertFalse($ztdPdo->isZtdEnabled());
163 $ztdPdo->enableZtd();
164 self::assertTrue($ztdPdo->isZtdEnabled());
165 }
166
167 public function testPreparedStatementSelectWithZtd(): void
168 {
169 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
170 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
171 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
172
173 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
174
175 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
176
177 $stmt = $ztdPdo->prepare('SELECT * FROM users WHERE name = ?');
178 self::assertNotFalse($stmt);
179
180 $stmt->execute(['Charlie']);
181 /** @var list<Row> $rows */
182 $rows = $stmt->fetchAll();
183
184 self::assertCount(1, $rows);
185 self::assertSame('Charlie', $rows[0]['name']);
186 }
187
188 public function testPreparedStatementSelectNonExistent(): void
189 {
190 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
191 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
192 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
193
194 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
195
196 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
197
198 $stmt = $ztdPdo->prepare('SELECT * FROM users WHERE name = ?');
199 self::assertNotFalse($stmt);
200
201 $stmt->execute(['Alice']);
202 /** @var list<Row> $rows */
203 $rows = $stmt->fetchAll();
204
205 self::assertCount(0, $rows);
206 }
207
208 public function testMultipleInsertsExactRowComparison(): void
209 {
210 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
211 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
212 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
213
214 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
215
216 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
217 $ztdPdo->exec("INSERT INTO users (name, age) VALUES ('Diana', 28)");
218
219 $stmt = $ztdPdo->query('SELECT name, age FROM users ORDER BY name');
220 self::assertNotFalse($stmt);
221 /** @var list<Row> $ztdRows */
222 $ztdRows = $stmt->fetchAll();
223 self::assertCount(2, $ztdRows);
224 self::assertSame('Charlie', $ztdRows[0]['name']);
225 self::assertSame('Diana', $ztdRows[1]['name']);
226
227 $stmt = $rawPdo->query('SELECT name FROM users ORDER BY name');
228 self::assertNotFalse($stmt);
229 /** @var list<Row> $rawRows */
230 $rawRows = $stmt->fetchAll();
231 self::assertCount(2, $rawRows);
232 self::assertSame('Alice', $rawRows[0]['name']);
233 self::assertSame('Bob', $rawRows[1]['name']);
234 }
235
236 public function testUpdateShadowData(): void
237 {
238 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
239 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
240 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
241
242 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
243
244 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (100, 'Charlie', 35)");
245 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (101, 'Diana', 28)");
246 $ztdPdo->exec("UPDATE users SET age = 36 WHERE name = 'Charlie'");
247
248 $stmt = $ztdPdo->query("SELECT name, age FROM users WHERE name = 'Charlie'");
249 self::assertNotFalse($stmt);
250 /** @var list<Row> $ztdRows */
251 $ztdRows = $stmt->fetchAll();
252 self::assertCount(1, $ztdRows);
253 self::assertSame('Charlie', $ztdRows[0]['name']);
254 self::assertEquals(36, $ztdRows[0]['age']);
255
256 $stmt = $ztdPdo->query("SELECT name, age FROM users WHERE name = 'Diana'");
257 self::assertNotFalse($stmt);
258 /** @var list<Row> $dianaRows */
259 $dianaRows = $stmt->fetchAll();
260 self::assertCount(1, $dianaRows);
261 self::assertSame('Diana', $dianaRows[0]['name']);
262 self::assertEquals(28, $dianaRows[0]['age']);
263
264 $stmt = $rawPdo->query('SELECT name, age FROM users ORDER BY id');
265 self::assertNotFalse($stmt);
266 /** @var list<Row> $rawRows */
267 $rawRows = $stmt->fetchAll();
268 self::assertCount(2, $rawRows);
269 self::assertSame('Alice', $rawRows[0]['name']);
270 self::assertSame(30, $rawRows[0]['age']);
271 self::assertSame('Bob', $rawRows[1]['name']);
272 self::assertSame(25, $rawRows[1]['age']);
273 }
274
275 public function testDeleteShadowData(): void
276 {
277 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
278 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
279 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
280
281 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
282
283 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (100, 'Charlie', 35)");
284 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (101, 'Diana', 28)");
285 $ztdPdo->exec("DELETE FROM users WHERE name = 'Charlie'");
286
287 $stmt = $ztdPdo->query('SELECT name FROM users ORDER BY name');
288 self::assertNotFalse($stmt);
289 /** @var list<Row> $ztdRows */
290 $ztdRows = $stmt->fetchAll();
291 self::assertCount(1, $ztdRows);
292 self::assertSame('Diana', $ztdRows[0]['name']);
293
294 $stmt = $rawPdo->query('SELECT name FROM users ORDER BY id');
295 self::assertNotFalse($stmt);
296 /** @var list<Row> $rawRows */
297 $rawRows = $stmt->fetchAll();
298 self::assertCount(2, $rawRows);
299 self::assertSame('Alice', $rawRows[0]['name']);
300 self::assertSame('Bob', $rawRows[1]['name']);
301 }
302
303 public function testDeleteAllShadowData(): void
304 {
305 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
306 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
307 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
308
309 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
310
311 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (100, 'Charlie', 35)");
312 $ztdPdo->exec("DELETE FROM users WHERE name = 'Charlie'");
313
314 $stmt = $ztdPdo->query('SELECT * FROM users');
315 self::assertNotFalse($stmt);
316 /** @var list<Row> $ztdRows */
317 $ztdRows = $stmt->fetchAll();
318 self::assertCount(0, $ztdRows);
319
320 $stmt = $rawPdo->query('SELECT * FROM users');
321 self::assertNotFalse($stmt);
322 /** @var list<Row> $rawRows */
323 $rawRows = $stmt->fetchAll();
324 self::assertCount(2, $rawRows);
325 }
326
327 public function testInsertThenUpdateThenSelectRoundtrip(): void
328 {
329 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
330 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
331 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
332
333 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
334
335 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (100, 'Charlie', 35)");
336 $ztdPdo->exec("UPDATE users SET name = 'Charles' WHERE name = 'Charlie'");
337
338 $stmt = $ztdPdo->query('SELECT name FROM users');
339 self::assertNotFalse($stmt);
340 /** @var list<Row> $ztdRows */
341 $ztdRows = $stmt->fetchAll();
342 self::assertCount(1, $ztdRows);
343 self::assertSame('Charles', $ztdRows[0]['name']);
344
345 $stmt = $rawPdo->query('SELECT * FROM users');
346 self::assertNotFalse($stmt);
347 /** @var list<Row> $rawRows */
348 $rawRows = $stmt->fetchAll();
349 self::assertCount(2, $rawRows);
350 }
351
352 public function testUpdateIsVisibleViaZtdSelect(): void
353 {
354 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
355 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
356 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
357
358 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
359
360 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (100, 'Charlie', 35)");
361 $ztdPdo->exec("UPDATE users SET age = 36 WHERE name = 'Charlie'");
362
363 $stmt = $ztdPdo->query("SELECT name, age FROM users WHERE name = 'Charlie'");
364 self::assertNotFalse($stmt);
365 /** @var list<Row> $ztdRows */
366 $ztdRows = $stmt->fetchAll();
367 self::assertCount(1, $ztdRows);
368 self::assertSame('Charlie', $ztdRows[0]['name']);
369 self::assertEquals(36, $ztdRows[0]['age']);
370 }
371
372 public function testUpdateDoesNotModifyPhysicalDatabase(): void
373 {
374 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
375 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
376 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
377
378 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
379
380 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (100, 'Charlie', 35)");
381 $ztdPdo->exec("UPDATE users SET age = 99 WHERE name = 'Charlie'");
382
383 $stmt = $rawPdo->query('SELECT name, age FROM users ORDER BY id');
384 self::assertNotFalse($stmt);
385 /** @var list<Row> $rawRows */
386 $rawRows = $stmt->fetchAll();
387 self::assertCount(2, $rawRows);
388 self::assertSame('Alice', $rawRows[0]['name']);
389 self::assertSame(30, $rawRows[0]['age']);
390 self::assertSame('Bob', $rawRows[1]['name']);
391 self::assertSame(25, $rawRows[1]['age']);
392 }
393
394 public function testDeleteIsVisibleViaZtdSelect(): void
395 {
396 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
397 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
398 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
399
400 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
401
402 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (100, 'Charlie', 35)");
403 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (101, 'Diana', 28)");
404 $ztdPdo->exec("DELETE FROM users WHERE name = 'Charlie'");
405
406 $stmt = $ztdPdo->query('SELECT name FROM users ORDER BY name');
407 self::assertNotFalse($stmt);
408 /** @var list<Row> $ztdRows */
409 $ztdRows = $stmt->fetchAll();
410 $names = array_column($ztdRows, 'name');
411 self::assertSame(['Diana'], $names, 'After DELETE, only Diana must remain in ZTD view');
412 }
413
414 public function testDeleteDoesNotModifyPhysicalDatabase(): void
415 {
416 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
417 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)');
418 $rawPdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
419
420 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
421
422 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (100, 'Charlie', 35)");
423 $ztdPdo->exec("DELETE FROM users WHERE name = 'Charlie'");
424
425 $stmt = $rawPdo->query('SELECT name FROM users ORDER BY id');
426 self::assertNotFalse($stmt);
427 /** @var list<Row> $rawRows */
428 $rawRows = $stmt->fetchAll();
429 $names = array_column($rawRows, 'name');
430 self::assertSame(['Alice', 'Bob'], $names, 'Physical database must be unchanged after ZTD DELETE');
431 }
432
433 public function testInsertWithNullValues(): void
434 {
435 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
436 $rawPdo->exec('CREATE TABLE nullable_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, bio TEXT)');
437
438 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
439
440 $ztdPdo->exec("INSERT INTO nullable_table (name, bio) VALUES ('Test', NULL)");
441
442 $ztdRows = $ztdPdo->query('SELECT * FROM nullable_table');
443 self::assertNotFalse($ztdRows);
444
445 /** @var list<Row> $rows */
446 $rows = $ztdRows->fetchAll();
447 self::assertCount(1, $rows);
448 self::assertSame('Test', $rows[0]['name']);
449 self::assertNull($rows[0]['bio']);
450 }
451
452 public function testCommentsRemainLexicalWhitespaceAcrossSqliteMutations(): void
453 {
454 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
455 $rawPdo->exec('CREATE TABLE items (id INTEGER PRIMARY KEY, status INTEGER)');
456 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
457
458 $ztdPdo->exec('INSERT INTO items VALUES (1, 1)');
459 $ztdPdo->exec('INSERT INTO/* table */items VALUES (2, 1)');
460 $ztdPdo->exec('UPDATE/* table */items SET status = 0 WHERE id = 1');
461 $ztdPdo->exec('DELETE FROM/* table */items WHERE id = 2');
462
463 $status = $ztdPdo->query('SELECT status FROM/* table */items WHERE id = 1');
464 self::assertNotFalse($status);
465 self::assertSame(0, $status->fetchColumn());
466
467 $ids = $ztdPdo->query("-- SELECT * FROM other_table WHERE DELETE UPDATE INSERT\nSELECT id FROM items ORDER BY id");
468 self::assertNotFalse($ids);
469 self::assertSame([1], $ids->fetchAll(PDO::FETCH_COLUMN));
470 }
471
472 public function testSqliteStringLiteralsDoNotCreateTableReferences(): void
473 {
474 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
475 $rawPdo->exec('CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)');
476 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
477 $ztdPdo->exec("INSERT INTO items VALUES (1, 'test')");
478
479 $lower = $ztdPdo->query("SELECT id, 'from items' AS label FROM items LIMIT 1");
480 self::assertNotFalse($lower);
481 self::assertSame([['id' => 1, 'label' => 'from items']], $lower->fetchAll());
482
483 $upper = $ztdPdo->query("SELECT id, 'FROM items' AS label FROM items LIMIT 1");
484 self::assertNotFalse($upper);
485 self::assertSame([['id' => 1, 'label' => 'FROM items']], $upper->fetchAll());
486
487 $join = $ztdPdo->query("SELECT id, 'join items' AS label FROM items LIMIT 1");
488 self::assertNotFalse($join);
489 self::assertSame([['id' => 1, 'label' => 'join items']], $join->fetchAll());
490 }
491
492 public function testInsertWithoutColumnListSupportsConstraintKeywordPrefixes(): void
493 {
494 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
495 $rawPdo->exec('CREATE TABLE bookings (id INT PRIMARY KEY, guest TEXT, check_in TEXT, check_out TEXT)');
496 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
497
498 self::assertSame(1, $ztdPdo->exec("INSERT INTO bookings VALUES (1, 'Alice', '2024-01-01', '2024-01-03')"));
499
500 $bookings = $ztdPdo->query('SELECT * FROM bookings');
501 self::assertNotFalse($bookings);
502 self::assertSame([
503 [
504 'id' => 1,
505 'guest' => 'Alice',
506 'check_in' => '2024-01-01',
507 'check_out' => '2024-01-03',
508 ],
509 ], $bookings->fetchAll());
510 }
511
512 public function testQuotedInsertSourceKeywordsRemainIdentifiers(): void
513 {
514 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
515 $rawPdo->exec('CREATE TABLE "select" (id INTEGER PRIMARY KEY, val TEXT)');
516 $rawPdo->exec('CREATE TABLE "values" (id INTEGER PRIMARY KEY, val TEXT)');
517 $rawPdo->exec('CREATE TABLE keyword_columns (id INTEGER PRIMARY KEY, "select" TEXT, "values" TEXT)');
518 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
519
520 self::assertSame(1, $ztdPdo->exec("INSERT INTO \"select\" VALUES (1, 'table-select')"));
521 self::assertSame(1, $ztdPdo->exec("INSERT INTO \"values\" VALUES (2, 'table-values')"));
522 self::assertSame(1, $ztdPdo->exec("INSERT INTO keyword_columns (id, \"select\", \"values\") VALUES (3, 'column-select', 'column-values')"));
523 self::assertSame(1, $ztdPdo->exec("INSERT INTO \"select\" SELECT 4 AS id, 'insert-select' AS val"));
524
525 $selectRows = $ztdPdo->query('SELECT * FROM "select" ORDER BY id');
526 self::assertNotFalse($selectRows);
527 self::assertSame([
528 ['id' => 1, 'val' => 'table-select'],
529 ['id' => 4, 'val' => 'insert-select'],
530 ], $selectRows->fetchAll());
531
532 $valuesRows = $ztdPdo->query('SELECT * FROM "values"');
533 self::assertNotFalse($valuesRows);
534 self::assertSame([['id' => 2, 'val' => 'table-values']], $valuesRows->fetchAll());
535
536 $columnRows = $ztdPdo->query('SELECT id, "select", "values" FROM keyword_columns');
537 self::assertNotFalse($columnRows);
538 self::assertSame([
539 ['id' => 3, 'select' => 'column-select', 'values' => 'column-values'],
540 ], $columnRows->fetchAll());
541 }
542
543 public function testBinaryPreparedValueRoundTripsThroughShadowCte(): void
544 {
545 $rawPdo = new PDO('sqlite::memory:', null, null, [
546 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
547 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
548 ]);
549 $rawPdo->exec('CREATE TABLE events (id INTEGER PRIMARY KEY, payload BLOB)');
550 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
551 $payload = "\x00\x01\x02\xFF\xFE";
552
553 $insert = $ztdPdo->prepare('INSERT INTO events (id, payload) VALUES (?, ?)');
554 self::assertNotFalse($insert);
555 self::assertTrue($insert->execute([1, $payload]));
556
557 $result = $ztdPdo->query('SELECT payload FROM events WHERE id = 1');
558 self::assertNotFalse($result);
559 self::assertSame($payload, $result->fetchColumn());
560 }
561
562 public function testRealValuesRetainRoundTripPrecision(): void
563 {
564 $rawPdo = new PDO('sqlite::memory:', null, null, [
565 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
566 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
567 ]);
568 $rawPdo->exec('CREATE TABLE measurements (id INTEGER PRIMARY KEY, value REAL)');
569 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
570
571 $ztdPdo->exec('INSERT INTO measurements VALUES (1, 2.718281828459045), (2, 0.30000000000000004)');
572
573 $result = $ztdPdo->query('SELECT value FROM measurements ORDER BY id');
574 self::assertNotFalse($result);
575 self::assertSame([2.718281828459045, 0.30000000000000004], $result->fetchAll(PDO::FETCH_COLUMN));
576 }
577
578 public function testShadowValuesRetainSqliteStorageClasses(): void
579 {
580 $rawPdo = new PDO('sqlite::memory:', null, null, [
581 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
582 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
583 ]);
584 $rawPdo->exec('CREATE TABLE values_table (id INTEGER PRIMARY KEY, int_value INTEGER, real_value REAL, text_value TEXT, nullable_value TEXT)');
585 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
586
587 $ztdPdo->exec("INSERT INTO values_table VALUES (1, 42, 3.14, 'hello', NULL)");
588
589 $result = $ztdPdo->query('SELECT typeof(int_value), typeof(real_value), typeof(text_value), typeof(nullable_value) FROM values_table');
590 self::assertNotFalse($result);
591 self::assertSame(['integer', 'real', 'text', 'null'], $result->fetch(PDO::FETCH_NUM));
592 }
593}
594