packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/DomainTypeTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\Endpoint;
8use Container\PostgreSqlRelease;
9use PDO;
10use PDOException;
11use PDOStatement;
12use PHPUnit\Framework\Attributes\CoversNothing;
13use PHPUnit\Framework\Attributes\Large;
14use PHPUnit\Framework\TestCase;
15use ZtdQuery\Adapter\Pdo\ZtdPdo;
16
17/**
18 * @requires extension pdo_pgsql
19 * @group integration
20 * @group postgres
21 */
22#[CoversNothing]
23#[Large]
24final class DomainTypeTest extends TestCase
25{
26    public function testDomainConstraintsAndDmlRemainNativeInTheShadowStore(): void
27    {
28        $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
29        /** @var PDO $rawPdo */
30        $rawPdo = new PDO(
31            $endpoint->dsn(),
32            $endpoint->username,
33            $endpoint->password,
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        $suffix = bin2hex(random_bytes(8));
42        $positiveDomain = 'positive_int_' . $suffix;
43        $percentageDomain = 'percentage_' . $suffix;
44        $table = 'contacts_' . $suffix;
45
46        try {
47            $rawPdo->exec("CREATE DOMAIN {$positiveDomain} AS INTEGER CHECK (VALUE > 0)");
48            $rawPdo->exec(
49                "CREATE DOMAIN {$percentageDomain} AS NUMERIC(5,2) CHECK (VALUE >= 0 AND VALUE <= 100)",
50            );
51            $rawPdo->exec(
52                "CREATE TABLE {$table} (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "
53                . "age {$positiveDomain} NOT NULL, satisfaction {$percentageDomain} NOT NULL)",
54            );
55            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
56
57            $insert = $ztdPdo->prepare(
58                "INSERT INTO {$table} (age, satisfaction) VALUES (\$1, \$2) RETURNING id, age, satisfaction",
59            );
60            self::assertInstanceOf(PDOStatement::class, $insert);
61            self::assertTrue($insert->execute([30, '85.50']));
62            self::assertSame(
63                ['id' => 1, 'age' => 30, 'satisfaction' => '85.50'],
64                $insert->fetch(PDO::FETCH_ASSOC),
65            );
66
67            self::assertSame(1, $ztdPdo->exec("UPDATE {$table} SET satisfaction = 99.99 WHERE id = 1"));
68            $updated = $ztdPdo->query("SELECT satisfaction::text FROM {$table} WHERE id = 1");
69            self::assertNotFalse($updated);
70            self::assertSame('99.99', $updated->fetchColumn());
71
72            self::assertSame(1, $ztdPdo->exec("DELETE FROM {$table} WHERE satisfaction > 90"));
73            $remaining = $ztdPdo->query("SELECT COUNT(*) FROM {$table}");
74            self::assertNotFalse($remaining);
75            self::assertSame(0, (int) $remaining->fetchColumn());
76
77            try {
78                $ztdPdo->exec("INSERT INTO {$table} (age, satisfaction) VALUES (-5, 101)");
79                self::fail('The domain CHECK constraint must reject invalid shadow data.');
80            } catch (PDOException $exception) {
81                self::assertStringContainsString('violates check constraint', $exception->getMessage());
82            }
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