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

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\Endpoint;
8use Container\PostgreSqlRelease;
9use PDO;
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 NativeUpsertExpressionTest extends TestCase
23{
24    public function testDatabaseEvaluatesJsonUpsertExpression(): void
25    {
26        $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
27        /** @var PDO $rawPdo */
28        $rawPdo = new PDO(
29            $endpoint->dsn(),
30            $endpoint->username,
31            $endpoint->password,
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
40        try {
41            $rawPdo->exec('CREATE TABLE items (id INT PRIMARY KEY, meta JSONB)');
42            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
43            $seed = "INSERT INTO items VALUES (1, '{\"color\":\"red\"}')";
44            $rawPdo->exec($seed);
45            $ztdPdo->exec($seed);
46
47            $sql = "INSERT INTO items VALUES (1, '{\"color\":\"purple\"}') ON CONFLICT(id) DO UPDATE SET meta = jsonb_set(items.meta, '{color}', '\"purple\"')";
48            $rawPdo->exec($sql);
49            $ztdPdo->exec($sql);
50
51            $rawStatement = $rawPdo->query('SELECT id, meta::text FROM items');
52            $ztdStatement = $ztdPdo->query('SELECT id, meta::text FROM items');
53            self::assertNotFalse($rawStatement);
54            self::assertNotFalse($ztdStatement);
55            self::assertSame($rawStatement->fetchAll(), $ztdStatement->fetchAll());
56        } finally {
57            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
58        }
59    }
60}
61