packages/ztd-query-pdo-adapter/tests/Integration/MySql/UpsertRowAliasTest.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 UpsertRowAliasTest extends TestCase
18{
19    public function testRowAliasResolvesIncomingValuesForLiteralAndPreparedUpserts(): 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        $table = 'prefix_' . bin2hex(random_bytes(8));
35
36        try {
37            $rawPdo->exec("CREATE TABLE `{$table}` (id INT PRIMARY KEY, name VARCHAR(50), score INT)");
38            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
39            $ztdPdo->exec("INSERT INTO `{$table}` VALUES (1, 'original', 10)");
40
41            self::assertSame(1, $ztdPdo->exec("INSERT INTO `{$table}` VALUES (1, 'literal', 20) AS incoming ON DUPLICATE KEY UPDATE name = incoming.name, score = incoming.score"));
42
43            $statement = $ztdPdo->prepare("INSERT INTO `{$table}` VALUES (?, ?, ?) AS incoming ON DUPLICATE KEY UPDATE name = incoming.name, score = incoming.score");
44            self::assertNotFalse($statement);
45            self::assertTrue($statement->execute([1, 'prepared', 30]));
46            self::assertSame(1, $statement->rowCount());
47
48            $row = $ztdPdo->query("SELECT id, name, score FROM `{$table}`");
49            self::assertNotFalse($row);
50            self::assertSame(['id' => 1, 'name' => 'prepared', 'score' => 30], $row->fetch(PDO::FETCH_ASSOC));
51
52            $physical = $rawPdo->query("SELECT * FROM `{$table}`");
53            self::assertNotFalse($physical);
54            self::assertSame([], $physical->fetchAll(PDO::FETCH_ASSOC));
55        } finally {
56            $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
57        }
58    }
59}
60