packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/UpdateBasicTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\PostgreSql16Container;
8use PDO;
9use PHPUnit\Framework\Attributes\CoversNothing;
10use PHPUnit\Framework\Attributes\Large;
11use PHPUnit\Framework\TestCase;
12use ZtdQuery\Adapter\Pdo\ZtdPdo;
13
14/**
15 * @requires extension pdo_pgsql
16 * @group integration
17 * @group postgres
18 *
19 * @phpstan-type Row array<string, mixed>
20 */
21#[CoversNothing]
22#[Large]
23final class UpdateBasicTest extends TestCase
24{
25 public function testUpdateReplacesExistingTextWithEmptyString(): void
26 {
27 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
28 /** @var PDO $rawPdo */
29 $rawPdo = new PDO(
30 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
31 'test',
32 'test',
33 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
34 );
35
36 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
37 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
38 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
39
40 $table = 'prefix_' . bin2hex(random_bytes(8));
41
42 try {
43 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT, notes TEXT)");
44 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
45 $ztdPdo->exec("INSERT INTO {$table} VALUES (1, 'Alice', 'some notes')");
46
47 self::assertSame(1, $ztdPdo->exec("UPDATE {$table} SET notes = '' WHERE name = 'Alice'"));
48
49 $statement = $ztdPdo->query("SELECT notes FROM {$table} WHERE id = 1");
50 self::assertNotFalse($statement);
51 self::assertSame('', $statement->fetchColumn());
52 } finally {
53 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
54 }
55 }
56
57 public function testUpdateSingleRow(): void
58 {
59 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
60 /** @var PDO $rawPdo */
61 $rawPdo = new PDO(
62 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
63 'test',
64 'test',
65 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
66 );
67
68 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
69 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
70 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
71
72 $table = 'prefix_' . bin2hex(random_bytes(8));
73
74 try {
75 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
76 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
77
78 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
79
80 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
81
82 $rawPdo->exec("UPDATE {$table} SET name = 'Alice Updated' WHERE id = 1");
83 $ztdPdo->exec("UPDATE {$table} SET name = 'Alice Updated' WHERE id = 1");
84
85 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY id");
86 self::assertNotFalse($stmt);
87 /** @var list<Row> */
88 $rawRows = $stmt->fetchAll();
89
90 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
91 self::assertNotFalse($stmt);
92 /** @var list<Row> */
93 $ztdRows = $stmt->fetchAll();
94
95 self::assertSame($rawRows, $ztdRows);
96 } finally {
97 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
98 }
99 }
100
101 public function testUpdateMultipleColumns(): void
102 {
103 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
104 /** @var PDO $rawPdo */
105 $rawPdo = new PDO(
106 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
107 'test',
108 'test',
109 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
110 );
111
112 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
113 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
114 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
115
116 $table = 'prefix_' . bin2hex(random_bytes(8));
117
118 try {
119 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
120 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
121
122 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
123
124 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
125
126 $rawPdo->exec("UPDATE {$table} SET name = 'Alice Updated', age = 31 WHERE id = 1");
127 $ztdPdo->exec("UPDATE {$table} SET name = 'Alice Updated', age = 31 WHERE id = 1");
128
129 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY id");
130 self::assertNotFalse($stmt);
131 /** @var list<Row> */
132 $rawRows = $stmt->fetchAll();
133
134 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
135 self::assertNotFalse($stmt);
136 /** @var list<Row> */
137 $ztdRows = $stmt->fetchAll();
138
139 self::assertSame($rawRows, $ztdRows);
140 } finally {
141 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
142 }
143 }
144
145 public function testUpdateDoesNotModifyPhysicalDatabase(): void
146 {
147 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
148 /** @var PDO $rawPdo */
149 $rawPdo = new PDO(
150 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
151 'test',
152 'test',
153 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
154 );
155
156 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
157 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
158 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
159
160 $table = 'prefix_' . bin2hex(random_bytes(8));
161
162 try {
163 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
164 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
165
166 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
167
168 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
169
170 $ztdPdo->exec("UPDATE {$table} SET name = 'Modified' WHERE id = 1");
171
172 $stmt = $rawPdo->query("SELECT name FROM {$table} WHERE id = 1");
173 self::assertNotFalse($stmt);
174 /** @var list<Row> */
175 $rawRows = $stmt->fetchAll();
176
177 self::assertSame('Alice', $rawRows[0]['name']);
178 } finally {
179 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
180 }
181 }
182
183 public function testUpdateSetPreservesFromKeywordsInsideFunctions(): void
184 {
185 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
186 /** @var PDO $rawPdo */
187 $rawPdo = new PDO(
188 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
189 'test',
190 'test',
191 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
192 );
193
194 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
195 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
196 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
197
198 $table = 'prefix_' . bin2hex(random_bytes(8));
199
200 try {
201 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, code TEXT NOT NULL)");
202 $rawPdo->exec("INSERT INTO {$table} (id, name, code) VALUES (1, ' Alice ', 'abcdef')");
203
204 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
205 $ztdPdo->exec("INSERT INTO {$table} (id, name, code) VALUES (1, ' Alice ', 'abcdef')");
206
207 $sql = "UPDATE {$table} SET name = TRIM(BOTH ' ' FROM name), code = SUBSTRING(code FROM 2 FOR 3) WHERE id = 1";
208 $rawPdo->exec($sql);
209 $ztdPdo->exec($sql);
210
211 $rawStatement = $rawPdo->query("SELECT * FROM {$table}");
212 $ztdStatement = $ztdPdo->query("SELECT * FROM {$table}");
213 self::assertNotFalse($rawStatement);
214 self::assertNotFalse($ztdStatement);
215 self::assertSame($rawStatement->fetchAll(), $ztdStatement->fetchAll());
216 } finally {
217 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
218 }
219 }
220
221 public function testUpdateWithGroupedInSubquery(): void
222 {
223 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
224 /** @var PDO $rawPdo */
225 $rawPdo = new PDO(
226 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
227 'test',
228 'test',
229 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
230 );
231
232 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
233 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
234 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
235
236 $users = 'prefix_' . bin2hex(random_bytes(8));
237 $orders = 'prefix_' . bin2hex(random_bytes(8));
238
239 try {
240 $rawPdo->exec("CREATE TABLE {$users} (id INTEGER PRIMARY KEY, name TEXT, tier TEXT)");
241 $rawPdo->exec("CREATE TABLE {$orders} (id INTEGER PRIMARY KEY, user_id INTEGER, total NUMERIC, status TEXT)");
242 $rawPdo->exec("INSERT INTO {$users} VALUES (1, 'Alice', 'standard'), (2, 'Bob', 'standard')");
243 $rawPdo->exec("INSERT INTO {$orders} VALUES (1, 1, 500, 'completed'), (2, 1, 300, 'completed'), (3, 2, 100, 'completed')");
244 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
245 $ztdPdo->exec("INSERT INTO {$users} VALUES (1, 'Alice', 'standard'), (2, 'Bob', 'standard')");
246 $ztdPdo->exec("INSERT INTO {$orders} VALUES (1, 1, 500, 'completed'), (2, 1, 300, 'completed'), (3, 2, 100, 'completed')");
247
248 $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)";
249 self::assertSame($rawPdo->exec($sql), $ztdPdo->exec($sql));
250
251 $raw = $rawPdo->query("SELECT * FROM {$users} ORDER BY id");
252 $shadow = $ztdPdo->query("SELECT * FROM {$users} ORDER BY id");
253 self::assertNotFalse($raw);
254 self::assertNotFalse($shadow);
255 self::assertSame($raw->fetchAll(), $shadow->fetchAll());
256 } finally {
257 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
258 }
259 }
260}
261