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