packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/MergeTest.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 MergeTest extends TestCase
23{
24    public function testMergeSimulatesEveryActionSourceShapeAndPreparedParameter(): void
25    {
26        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
27        /** @var PDO $pdo */
28        $pdo = 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        $pdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
37        $pdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
38
39
40        try {
41            $pdo->exec('CREATE TABLE merge_target (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
42            $pdo->exec('CREATE TABLE merge_source (id INTEGER PRIMARY KEY, name TEXT NOT NULL, remove_row BOOLEAN NOT NULL)');
43            $ztdPdo = ZtdPdo::fromPdo($pdo);
44            self::assertSame(2, $ztdPdo->exec(
45                "INSERT INTO merge_target VALUES (1, 'old'), (3, 'deleted')",
46            ));
47            self::assertSame(3, $ztdPdo->exec(
48                "INSERT INTO merge_source VALUES (1, 'updated', FALSE), (2, 'inserted', FALSE), (3, 'unused', TRUE)",
49            ));
50
51            self::assertSame(3, $ztdPdo->exec(
52                'MERGE INTO merge_target AS target USING merge_source AS source '
53                . 'ON target.id = source.id '
54                . 'WHEN MATCHED AND source.remove_row THEN DELETE '
55                . 'WHEN MATCHED THEN UPDATE SET name = source.name '
56                . 'WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name)',
57            ));
58            $afterMixedActions = $ztdPdo->query('SELECT id, name FROM merge_target ORDER BY id');
59            self::assertNotFalse($afterMixedActions);
60            self::assertSame([
61                ['id' => 1, 'name' => 'updated'],
62                ['id' => 2, 'name' => 'inserted'],
63            ], $afterMixedActions->fetchAll());
64
65            self::assertSame(0, $ztdPdo->exec(
66                'MERGE INTO merge_target AS target USING (VALUES (2, TRUE)) AS source(id, skip_row) '
67                . 'ON target.id = source.id '
68                . 'WHEN MATCHED AND source.skip_row THEN DO NOTHING '
69                . 'WHEN MATCHED THEN DELETE',
70            ));
71
72            $prepared = $ztdPdo->prepare(
73                'MERGE INTO merge_target AS target '
74                . 'USING (VALUES ($1::INTEGER, $2::TEXT, $3::BOOLEAN)) AS source(id, name, remove_row) '
75                . 'ON target.id = source.id '
76                . 'WHEN MATCHED AND source.remove_row THEN DELETE '
77                . 'WHEN MATCHED THEN UPDATE SET name = source.name '
78                . 'WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name)',
79            );
80            self::assertInstanceOf(PDOStatement::class, $prepared);
81            self::assertTrue($prepared->execute([1, 'ignored', true]));
82            self::assertSame(1, $prepared->rowCount());
83            self::assertTrue($prepared->execute([4, 'prepared', false]));
84            self::assertSame(1, $prepared->rowCount());
85
86            self::assertSame(1, $ztdPdo->exec(
87                "WITH incoming(id, name) AS (VALUES (5, 'cte')) "
88                . 'MERGE INTO merge_target AS target USING incoming AS source '
89                . 'ON target.id = source.id '
90                . 'WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name)',
91            ));
92
93            $final = $ztdPdo->query('SELECT id, name FROM merge_target ORDER BY id');
94            self::assertNotFalse($final);
95            self::assertSame([
96                ['id' => 2, 'name' => 'inserted'],
97                ['id' => 4, 'name' => 'prepared'],
98                ['id' => 5, 'name' => 'cte'],
99            ], $final->fetchAll(PDO::FETCH_ASSOC));
100
101            $physical = $pdo->query(
102                'SELECT (SELECT COUNT(*) FROM merge_target) + (SELECT COUNT(*) FROM merge_source)',
103            );
104            self::assertNotFalse($physical);
105            self::assertSame(0, (int) $physical->fetchColumn());
106        } finally {
107            $pdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
108        }
109    }
110}
111