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