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