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