packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/PartialIndexOnConflictTest.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#[CoversNothing]
21#[Large]
22final class PartialIndexOnConflictTest extends TestCase
23{
24 public function testPartialUniqueIndexUpsertsRemainInTheShadowStore(): void
25 {
26 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
27 /** @var PDO $rawPdo */
28 $rawPdo = new PDO(
29 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
30 'test',
31 'test',
32 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
33 );
34
35 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
36 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
37 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
38
39 $table = 'partial_' . bin2hex(random_bytes(8));
40
41 try {
42 $rawPdo->exec(
43 "CREATE TABLE {$table} (email TEXT NOT NULL, status TEXT NOT NULL, login_count INTEGER NOT NULL)",
44 );
45 $rawPdo->exec(
46 "CREATE UNIQUE INDEX {$table}_active_email ON {$table} (email) WHERE status = 'active'",
47 );
48 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
49 self::assertSame(1, $ztdPdo->exec(
50 "INSERT INTO {$table} VALUES ('alice@example.com', 'active', 5)",
51 ));
52
53 $prepared = $ztdPdo->prepare(
54 "INSERT INTO {$table} VALUES (\$1, \$2, \$3) "
55 . "ON CONFLICT (email) WHERE status = 'active' "
56 . "DO UPDATE SET login_count = {$table}.login_count + EXCLUDED.login_count",
57 );
58 self::assertInstanceOf(PDOStatement::class, $prepared);
59 self::assertTrue($prepared->execute(['alice@example.com', 'active', 1]));
60 self::assertSame(1, $prepared->rowCount());
61
62 self::assertSame(1, $ztdPdo->exec(
63 "INSERT INTO {$table} VALUES ('alice@example.com', 'active', 4) "
64 . "ON CONFLICT (email) WHERE status = 'active' "
65 . "DO UPDATE SET login_count = GREATEST({$table}.login_count, EXCLUDED.login_count)",
66 ));
67 self::assertSame(0, $ztdPdo->exec(
68 "INSERT INTO {$table} VALUES ('alice@example.com', 'active', 99) "
69 . "ON CONFLICT (email) WHERE status = 'active' DO NOTHING",
70 ));
71 self::assertSame(1, $ztdPdo->exec(
72 "INSERT INTO {$table} VALUES ('alice@example.com', 'inactive', 7) "
73 . "ON CONFLICT (email) WHERE status = 'active' DO NOTHING",
74 ));
75
76 $rows = $ztdPdo->query("SELECT email, status, login_count FROM {$table} ORDER BY status");
77 self::assertNotFalse($rows);
78 self::assertSame([
79 ['email' => 'alice@example.com', 'status' => 'active', 'login_count' => 6],
80 ['email' => 'alice@example.com', 'status' => 'inactive', 'login_count' => 7],
81 ], $rows->fetchAll(PDO::FETCH_ASSOC));
82
83 $physical = $rawPdo->query("SELECT COUNT(*) FROM {$table}");
84 self::assertNotFalse($physical);
85 self::assertSame(0, (int) $physical->fetchColumn());
86 } finally {
87 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
88 }
89 }
90}
91