packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/UpdateBasicTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
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 *
16 * @phpstan-type Row array<string, mixed>
17 */
18#[CoversNothing]
19#[Large]
20final class UpdateBasicTest extends TestCase
21{
22 public function testUpdateSingleRow(): void
23 {
24 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
25 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
26 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
27 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
28 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
29
30 $rawPdo->exec("UPDATE users SET name = 'Alice Updated' WHERE id = 1");
31 $ztdPdo->exec("UPDATE users SET name = 'Alice Updated' WHERE id = 1");
32
33 $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
34 self::assertNotFalse($stmt);
35 /** @var list<Row> $rawRows */
36 $rawRows = $stmt->fetchAll();
37 $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
38 self::assertNotFalse($stmt);
39 /** @var list<Row> $ztdRows */
40 $ztdRows = $stmt->fetchAll();
41 self::assertSame($rawRows, $ztdRows);
42 }
43
44 public function testUpdateMultipleColumns(): void
45 {
46 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
47 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
48 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
49 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
50 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
51
52 $rawPdo->exec("UPDATE users SET name = 'Alice Updated', age = 31 WHERE id = 1");
53 $ztdPdo->exec("UPDATE users SET name = 'Alice Updated', age = 31 WHERE id = 1");
54
55 $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
56 self::assertNotFalse($stmt);
57 /** @var list<Row> $rawRows */
58 $rawRows = $stmt->fetchAll();
59 $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
60 self::assertNotFalse($stmt);
61 /** @var list<Row> $ztdRows */
62 $ztdRows = $stmt->fetchAll();
63 self::assertSame($rawRows, $ztdRows);
64 }
65
66 public function testUpdateDoesNotModifyPhysicalDatabase(): void
67 {
68 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
69 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
70 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
71 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
72 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
73
74 $ztdPdo->exec("UPDATE users SET name = 'Modified' WHERE id = 1");
75
76 $stmt = $rawPdo->query('SELECT name FROM users WHERE id = 1');
77 self::assertNotFalse($stmt);
78 /** @var list<Row> $rawRows */
79 $rawRows = $stmt->fetchAll();
80 self::assertSame('Alice', $rawRows[0]['name']);
81 }
82
83 public function testPrimaryKeyChangesAndColumnSwapsRetainOriginalRowIdentity(): void
84 {
85 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
86 $rawPdo->exec('CREATE TABLE pairs (id INTEGER PRIMARY KEY, left_value TEXT, right_value TEXT)');
87 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
88 $ztdPdo->exec("INSERT INTO pairs VALUES (1, 'left', 'right'), (2, 'second', 'row')");
89
90 self::assertSame(1, $ztdPdo->exec('UPDATE pairs SET id = 10, left_value = right_value, right_value = left_value WHERE id = 1'));
91 $statement = $ztdPdo->query('SELECT * FROM pairs ORDER BY id');
92 self::assertNotFalse($statement);
93 self::assertSame([
94 ['id' => 2, 'left_value' => 'second', 'right_value' => 'row'],
95 ['id' => 10, 'left_value' => 'right', 'right_value' => 'left'],
96 ], $statement->fetchAll());
97
98 self::assertSame(2, $ztdPdo->exec('DELETE FROM pairs'));
99 $statement = $ztdPdo->query('SELECT * FROM pairs');
100 self::assertNotFalse($statement);
101 self::assertSame([], $statement->fetchAll());
102 }
103
104 public function testUpdateWithGroupedInSubquery(): void
105 {
106 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
107 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, tier TEXT)');
108 $rawPdo->exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL, status TEXT)');
109 $rawPdo->exec("INSERT INTO users VALUES (1, 'Alice', 'standard'), (2, 'Bob', 'standard')");
110 $rawPdo->exec("INSERT INTO orders VALUES (1, 1, 500, 'completed'), (2, 1, 300, 'completed'), (3, 2, 100, 'completed')");
111 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
112 $ztdPdo->exec("INSERT INTO users VALUES (1, 'Alice', 'standard'), (2, 'Bob', 'standard')");
113 $ztdPdo->exec("INSERT INTO orders VALUES (1, 1, 500, 'completed'), (2, 1, 300, 'completed'), (3, 2, 100, 'completed')");
114
115 $sql = "UPDATE users SET tier = 'premium' WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed' GROUP BY user_id HAVING SUM(total) > 400)";
116 self::assertSame($rawPdo->exec($sql), $ztdPdo->exec($sql));
117
118 $raw = $rawPdo->query('SELECT * FROM users ORDER BY id');
119 $shadow = $ztdPdo->query('SELECT * FROM users ORDER BY id');
120 self::assertNotFalse($raw);
121 self::assertNotFalse($shadow);
122 self::assertSame($raw->fetchAll(), $shadow->fetchAll());
123 }
124}
125