packages/ztd-query-pdo-adapter/tests/Integration/MySql/InsertCompoundSelectTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\MySql;
6
7use Container\MySql80Container;
8use Container\MySql84Container;
9use PDO;
10use PHPUnit\Framework\Attributes\CoversNothing;
11use PHPUnit\Framework\Attributes\Large;
12use PHPUnit\Framework\TestCase;
13use ZtdQuery\Adapter\Pdo\ZtdPdo;
14
15#[CoversNothing]
16#[Large]
17final class InsertCompoundSelectTest extends TestCase
18{
19    public function testInsertPreservesEveryCompoundSelectBranch(): void
20    {
21        $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
22        /** @var PDO $rawPdo */
23        $rawPdo = new PDO(
24            sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
25            'root',
26            'root',
27            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
28        );
29
30        $databaseName = 'ztd_' . bin2hex(random_bytes(8));
31        $rawPdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
32        $rawPdo->exec(sprintf('USE `%s`', $databaseName));
33
34        $archive = 'prefix_' . bin2hex(random_bytes(8));
35        $current = 'prefix_' . bin2hex(random_bytes(8));
36        $combined = 'prefix_' . bin2hex(random_bytes(8));
37        $prepared = 'prefix_' . bin2hex(random_bytes(8));
38
39        try {
40            $rawPdo->exec("CREATE TABLE `{$archive}` (name VARCHAR(50) PRIMARY KEY, amount DECIMAL(10,2))");
41            $rawPdo->exec("CREATE TABLE `{$current}` (name VARCHAR(50) PRIMARY KEY, amount DECIMAL(10,2))");
42            $rawPdo->exec("CREATE TABLE `{$combined}` (name VARCHAR(50) PRIMARY KEY, amount DECIMAL(10,2))");
43            $rawPdo->exec("CREATE TABLE `{$prepared}` (name VARCHAR(50) PRIMARY KEY, amount DECIMAL(10,2))");
44            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
45            $ztdPdo->exec("INSERT INTO `{$archive}` VALUES ('Alice', 100.00), ('Bob', 150.00)");
46            $ztdPdo->exec("INSERT INTO `{$current}` VALUES ('Carol', 300.00), ('Dave', 50.00)");
47
48            self::assertSame(4, $ztdPdo->exec("INSERT INTO `{$combined}` (name, amount) SELECT name, amount FROM `{$archive}` UNION ALL SELECT name, amount FROM `{$current}`"));
49
50            $statement = $ztdPdo->prepare("INSERT INTO `{$prepared}` (name, amount) SELECT name, amount FROM `{$archive}` WHERE amount >= ? UNION ALL SELECT name, amount FROM `{$current}` WHERE amount >= ?");
51            self::assertNotFalse($statement);
52            self::assertTrue($statement->execute([150, 100]));
53            self::assertSame(2, $statement->rowCount());
54
55            $combinedRows = $ztdPdo->query("SELECT name, amount FROM `{$combined}` ORDER BY name");
56            $preparedRows = $ztdPdo->query("SELECT name, amount FROM `{$prepared}` ORDER BY name");
57            self::assertNotFalse($combinedRows);
58            self::assertNotFalse($preparedRows);
59            self::assertSame([
60                ['name' => 'Alice', 'amount' => '100.00'],
61                ['name' => 'Bob', 'amount' => '150.00'],
62                ['name' => 'Carol', 'amount' => '300.00'],
63                ['name' => 'Dave', 'amount' => '50.00'],
64            ], $combinedRows->fetchAll(PDO::FETCH_ASSOC));
65            self::assertSame([
66                ['name' => 'Bob', 'amount' => '150.00'],
67                ['name' => 'Carol', 'amount' => '300.00'],
68            ], $preparedRows->fetchAll(PDO::FETCH_ASSOC));
69
70            $physicalCombined = $rawPdo->query("SELECT * FROM `{$combined}`");
71            $physicalPrepared = $rawPdo->query("SELECT * FROM `{$prepared}`");
72            self::assertNotFalse($physicalCombined);
73            self::assertNotFalse($physicalPrepared);
74            self::assertSame([], $physicalCombined->fetchAll(PDO::FETCH_ASSOC));
75            self::assertSame([], $physicalPrepared->fetchAll(PDO::FETCH_ASSOC));
76        } finally {
77            $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
78        }
79    }
80}
81