packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/InsertOnConflictTest.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 InsertOnConflictTest extends TestCase
24{
25 public function testPreparedOnConflictDoUpdateReplacesExistingRow(): 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 NOT NULL)");
44 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
45 $ztdPdo->exec("INSERT INTO {$table} VALUES (1, 'original')");
46 $statement = $ztdPdo->prepare(
47 "INSERT INTO {$table} VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name"
48 );
49 self::assertNotFalse($statement);
50
51 self::assertTrue($statement->execute(['1', 'updated']));
52
53 $rows = $ztdPdo->query("SELECT * FROM {$table} WHERE id = 1");
54 self::assertNotFalse($rows);
55 self::assertSame([['id' => 1, 'name' => 'updated']], $rows->fetchAll());
56 } finally {
57 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
58 }
59 }
60
61 public function testOnConflictDoNothing(): void
62 {
63 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
64 /** @var PDO $rawPdo */
65 $rawPdo = new PDO(
66 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
67 'test',
68 'test',
69 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
70 );
71
72 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
73 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
74 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
75
76 $table = 'prefix_' . bin2hex(random_bytes(8));
77
78 try {
79 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
80
81 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
82
83 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30)");
84 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30)");
85
86 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Duplicate', 99) ON CONFLICT (id) DO NOTHING");
87 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Duplicate', 99) ON CONFLICT (id) DO NOTHING");
88
89 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY id");
90 self::assertNotFalse($stmt);
91 /** @var list<Row> */
92 $rawRows = $stmt->fetchAll();
93
94 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
95 self::assertNotFalse($stmt);
96 /** @var list<Row> */
97 $ztdRows = $stmt->fetchAll();
98
99 self::assertSame($rawRows, $ztdRows);
100 } finally {
101 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
102 }
103 }
104
105 public function testOnConflictDoUpdate(): void
106 {
107 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
108 /** @var PDO $rawPdo */
109 $rawPdo = new PDO(
110 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
111 'test',
112 'test',
113 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
114 );
115
116 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
117 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
118 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
119
120 $table = 'prefix_' . bin2hex(random_bytes(8));
121
122 try {
123 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
124
125 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
126
127 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30)");
128 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30)");
129
130 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice Updated', 31) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age");
131 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice Updated', 31) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age");
132
133 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY id");
134 self::assertNotFalse($stmt);
135 /** @var list<Row> */
136 $rawRows = $stmt->fetchAll();
137
138 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
139 self::assertNotFalse($stmt);
140 /** @var list<Row> */
141 $ztdRows = $stmt->fetchAll();
142
143 self::assertSame($rawRows, $ztdRows);
144 } finally {
145 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
146 }
147 }
148
149 public function testConditionalOnConflictAndReturningMatchPostgres(): void
150 {
151 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
152 /** @var PDO $rawPdo */
153 $rawPdo = new PDO(
154 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
155 'test',
156 'test',
157 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
158 );
159
160 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
161 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
162 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
163
164 $nativeTable = 'prefix_' . bin2hex(random_bytes(8));
165 $shadowTable = 'prefix_' . bin2hex(random_bytes(8));
166
167 try {
168 $rawPdo->exec("CREATE TABLE {$nativeTable} (id INTEGER PRIMARY KEY, name TEXT, score INTEGER)");
169 $rawPdo->exec("CREATE TABLE {$shadowTable} (id INTEGER PRIMARY KEY, name TEXT, score INTEGER)");
170 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
171 $rawPdo->exec("INSERT INTO {$nativeTable} VALUES (1, 'original', 50)");
172 $ztdPdo->exec("INSERT INTO {$shadowTable} VALUES (1, 'original', 50)");
173
174 $rawSkipped = $rawPdo->query("INSERT INTO {$nativeTable} VALUES (1, 'skipped', 95) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name WHERE {$nativeTable}.score >= 80 RETURNING id, name, score");
175 $ztdSkipped = $ztdPdo->query("INSERT INTO {$shadowTable} VALUES (1, 'skipped', 95) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name WHERE {$shadowTable}.score >= 80 RETURNING id, name, score");
176 self::assertNotFalse($rawSkipped);
177 self::assertNotFalse($ztdSkipped);
178 self::assertSame($rawSkipped->fetchAll(), $ztdSkipped->fetchAll());
179
180 $rawPdo->exec("UPDATE {$nativeTable} SET score = 85 WHERE id = 1");
181 $ztdPdo->exec("UPDATE {$shadowTable} SET score = 85 WHERE id = 1");
182 $rawUpdated = $rawPdo->query("INSERT INTO {$nativeTable} VALUES (1, 'updated', 95) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name WHERE {$nativeTable}.score >= 80 RETURNING id, name, score");
183 $ztdUpdated = $ztdPdo->query("INSERT INTO {$shadowTable} VALUES (1, 'updated', 95) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name WHERE {$shadowTable}.score >= 80 RETURNING id, name, score");
184 self::assertNotFalse($rawUpdated);
185 self::assertNotFalse($ztdUpdated);
186 self::assertSame($rawUpdated->fetchAll(), $ztdUpdated->fetchAll());
187 } finally {
188 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
189 }
190 }
191}
192