packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/InsertBasicTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\Endpoint;
8use Container\PostgreSqlRelease;
9use PDO;
10use PDOStatement;
11use PHPUnit\Framework\Attributes\CoversNothing;
12use PHPUnit\Framework\Attributes\Large;
13use PHPUnit\Framework\TestCase;
14use ZtdQuery\Adapter\Pdo\ZtdPdo;
15
16/**
17 * @requires extension pdo_pgsql
18 * @group integration
19 * @group postgres
20 *
21 * @phpstan-type Row array<string, mixed>
22 */
23#[CoversNothing]
24#[Large]
25final class InsertBasicTest extends TestCase
26{
27 public function testSingleRowInsert(): void
28 {
29 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
30 /** @var PDO $rawPdo */
31 $rawPdo = new PDO(
32 $endpoint->dsn(),
33 $endpoint->username,
34 $endpoint->password,
35 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
36 );
37
38 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
39 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
40 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
41
42 $table = 'prefix_' . bin2hex(random_bytes(8));
43
44 try {
45 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
46
47 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
48
49 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30)");
50 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30)");
51
52 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY id");
53 self::assertNotFalse($stmt);
54 /** @var list<Row> */
55 $rawRows = $stmt->fetchAll();
56
57 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
58 self::assertNotFalse($stmt);
59 /** @var list<Row> */
60 $ztdRows = $stmt->fetchAll();
61
62 self::assertSame($rawRows, $ztdRows);
63 } finally {
64 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
65 }
66 }
67
68 public function testMultiRowInsert(): void
69 {
70 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
71 /** @var PDO $rawPdo */
72 $rawPdo = new PDO(
73 $endpoint->dsn(),
74 $endpoint->username,
75 $endpoint->password,
76 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
77 );
78
79 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
80 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
81 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
82
83 $table = 'prefix_' . bin2hex(random_bytes(8));
84
85 try {
86 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
87
88 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
89
90 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25)");
91 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25)");
92
93 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY id");
94 self::assertNotFalse($stmt);
95 /** @var list<Row> */
96 $rawRows = $stmt->fetchAll();
97
98 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
99 self::assertNotFalse($stmt);
100 /** @var list<Row> */
101 $ztdRows = $stmt->fetchAll();
102
103 self::assertSame($rawRows, $ztdRows);
104 } finally {
105 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
106 }
107 }
108
109 public function testInsertDoesNotModifyPhysicalDatabase(): void
110 {
111 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
112 /** @var PDO $rawPdo */
113 $rawPdo = new PDO(
114 $endpoint->dsn(),
115 $endpoint->username,
116 $endpoint->password,
117 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
118 );
119
120 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
121 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
122 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
123
124 $table = 'prefix_' . bin2hex(random_bytes(8));
125
126 try {
127 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
128
129 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
130
131 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30)");
132
133 $stmt = $rawPdo->query("SELECT * FROM {$table}");
134 self::assertNotFalse($stmt);
135 /** @var list<Row> */
136 $rawRows = $stmt->fetchAll();
137
138 self::assertCount(0, $rawRows);
139 } finally {
140 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
141 }
142 }
143
144 public function testOmittedExplicitAndDefaultValuesMatchPostgreSql(): void
145 {
146 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
147 /** @var PDO $rawPdo */
148 $rawPdo = new PDO(
149 $endpoint->dsn(),
150 $endpoint->username,
151 $endpoint->password,
152 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
153 );
154
155 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
156 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
157 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
158
159 $table = 'prefix_' . bin2hex(random_bytes(8));
160
161 try {
162 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER DEFAULT 7, status TEXT DEFAULT 'active', note TEXT)");
163 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
164
165 $rawPdo->exec("INSERT INTO {$table} (id, status) VALUES (1, DEFAULT)");
166 $rawPdo->exec("INSERT INTO {$table} DEFAULT VALUES");
167 $ztdPdo->exec("INSERT INTO {$table} (id, status) VALUES (1, DEFAULT)");
168 $ztdPdo->exec("INSERT INTO {$table} DEFAULT VALUES");
169
170 $raw = $rawPdo->query("SELECT * FROM {$table} ORDER BY id");
171 $ztd = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
172 self::assertNotFalse($raw);
173 self::assertNotFalse($ztd);
174 self::assertSame($raw->fetchAll(), $ztd->fetchAll());
175 } finally {
176 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
177 }
178 }
179
180 public function testSerialUsesShadowSequenceWithoutAdvancingPhysicalSequence(): void
181 {
182 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
183 /** @var PDO $rawPdo */
184 $rawPdo = new PDO(
185 $endpoint->dsn(),
186 $endpoint->username,
187 $endpoint->password,
188 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
189 );
190
191 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
192 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
193 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
194
195 $table = 'prefix_' . bin2hex(random_bytes(8));
196
197 try {
198 $rawPdo->exec("CREATE TABLE {$table} (id SERIAL PRIMARY KEY, name TEXT NOT NULL)");
199 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
200
201 $ztdPdo->exec("INSERT INTO {$table} (name) VALUES ('Alice'), ('Bob')");
202
203 $rows = $ztdPdo->query("SELECT id, name FROM {$table} ORDER BY id");
204 $sequence = $rawPdo->query("SELECT last_value, is_called FROM {$table}_id_seq");
205 self::assertNotFalse($rows);
206 self::assertNotFalse($sequence);
207 self::assertSame([['id' => 1, 'name' => 'Alice'], ['id' => 2, 'name' => 'Bob']], $rows->fetchAll());
208 self::assertSame(['last_value' => 1, 'is_called' => false], $sequence->fetch());
209 } finally {
210 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
211 }
212 }
213
214 public function testInsertSelectPreservesExpressionsDistinctAndWindows(): void
215 {
216 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
217 /** @var PDO $rawPdo */
218 $rawPdo = new PDO(
219 $endpoint->dsn(),
220 $endpoint->username,
221 $endpoint->password,
222 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
223 );
224
225 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
226 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
227 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
228
229 $products = 'prefix_' . bin2hex(random_bytes(8));
230 $archive = 'prefix_' . bin2hex(random_bytes(8));
231 $departments = 'prefix_' . bin2hex(random_bytes(8));
232 $popular = 'prefix_' . bin2hex(random_bytes(8));
233 $conditional = 'prefix_' . bin2hex(random_bytes(8));
234
235 try {
236 $rawPdo->exec("CREATE TABLE {$products} (id INTEGER PRIMARY KEY, name TEXT, price REAL, dept TEXT)");
237 $rawPdo->exec("CREATE TABLE {$archive} (id INTEGER PRIMARY KEY, name TEXT, doubled REAL, rank_in_dept INTEGER)");
238 $rawPdo->exec("CREATE TABLE {$departments} (id SERIAL PRIMARY KEY, name TEXT)");
239 $rawPdo->exec("CREATE TABLE {$popular} (dept TEXT, total REAL, item_count INTEGER)");
240 $rawPdo->exec("CREATE TABLE {$conditional} (id INTEGER PRIMARY KEY, name TEXT)");
241 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
242
243 $ztdPdo->exec("INSERT INTO {$products} VALUES (1, 'A', 10, 'x'), (2, 'B', 30, 'x'), (3, 'C', 20, 'y')");
244 $ztdPdo->exec("INSERT INTO {$archive} SELECT id, name, price * 2, CAST(ROW_NUMBER() OVER (PARTITION BY dept ORDER BY price DESC) AS INTEGER) FROM {$products}");
245 $ztdPdo->exec("INSERT INTO {$departments} (name) SELECT DISTINCT dept FROM {$products} ORDER BY dept");
246 $popularInsert = $ztdPdo->prepare("INSERT INTO {$popular} SELECT dept, SUM(price), COUNT(*) FROM {$products} GROUP BY dept HAVING SUM(price) > ?");
247 self::assertInstanceOf(PDOStatement::class, $popularInsert);
248 $popularInsert->execute([15]);
249 $ztdPdo->exec("INSERT INTO {$conditional} SELECT 1, 'alice' WHERE NOT EXISTS (SELECT 1 FROM {$conditional} WHERE name = 'alice')");
250 $ztdPdo->exec("INSERT INTO {$conditional} SELECT 1, 'alice' WHERE NOT EXISTS (SELECT 1 FROM {$conditional} WHERE name = 'alice')");
251
252 $archiveRows = $ztdPdo->query("SELECT * FROM {$archive} ORDER BY id");
253 $departmentRows = $ztdPdo->query("SELECT * FROM {$departments} ORDER BY id");
254 $popularRows = $ztdPdo->query("SELECT * FROM {$popular} ORDER BY dept");
255 $conditionalRows = $ztdPdo->query("SELECT * FROM {$conditional}");
256 self::assertNotFalse($archiveRows);
257 self::assertNotFalse($departmentRows);
258 self::assertNotFalse($popularRows);
259 self::assertNotFalse($conditionalRows);
260 self::assertEquals([
261 ['id' => 1, 'name' => 'A', 'doubled' => 20, 'rank_in_dept' => 2],
262 ['id' => 2, 'name' => 'B', 'doubled' => 60, 'rank_in_dept' => 1],
263 ['id' => 3, 'name' => 'C', 'doubled' => 40, 'rank_in_dept' => 1],
264 ], $archiveRows->fetchAll());
265 self::assertSame([['id' => 1, 'name' => 'x'], ['id' => 2, 'name' => 'y']], $departmentRows->fetchAll());
266 self::assertEquals([['dept' => 'x', 'total' => 40, 'item_count' => 2], ['dept' => 'y', 'total' => 20, 'item_count' => 1]], $popularRows->fetchAll());
267 self::assertSame([['id' => 1, 'name' => 'alice']], $conditionalRows->fetchAll());
268 } finally {
269 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
270 }
271 }
272}
273