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

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\MySql;
6
7use Container\Endpoint;
8use Container\MySqlRelease;
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 PreparedUpsertTest extends TestCase
18{
19    public function testPreparedReplaceRemovesExistingPrimaryKey(): void
20    {
21        $endpoint = \Testcontainers\Testcontainers::run(MySqlRelease::fromEnvironment())->getData(Endpoint::class);
22        /** @var PDO $rawPdo */
23        $rawPdo = new PDO(
24            $endpoint->dsn(),
25            $endpoint->username,
26            $endpoint->password,
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), value INT)");
38            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
39            $ztdPdo->exec("INSERT INTO `{$table}` VALUES (1, 'original', 100)");
40            $statement = $ztdPdo->prepare("REPLACE INTO `{$table}` VALUES (?, ?, ?)");
41            self::assertNotFalse($statement);
42
43            self::assertTrue($statement->execute(['1', 'replaced', 999]));
44
45            $rows = $ztdPdo->query("SELECT * FROM `{$table}` WHERE id = 1");
46            self::assertNotFalse($rows);
47            self::assertSame([['id' => 1, 'name' => 'replaced', 'value' => 999]], $rows->fetchAll());
48        } finally {
49            $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
50        }
51    }
52
53    public function testPreparedOnDuplicateKeyUpdateReplacesExistingValues(): void
54    {
55        $endpoint = \Testcontainers\Testcontainers::run(MySqlRelease::fromEnvironment())->getData(Endpoint::class);
56        /** @var PDO $rawPdo */
57        $rawPdo = new PDO(
58            $endpoint->dsn(),
59            $endpoint->username,
60            $endpoint->password,
61            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
62        );
63
64        $databaseName = 'ztd_' . bin2hex(random_bytes(8));
65        $rawPdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
66        $rawPdo->exec(sprintf('USE `%s`', $databaseName));
67
68        $table = 'prefix_' . bin2hex(random_bytes(8));
69
70        try {
71            $rawPdo->exec("CREATE TABLE `{$table}` (id INT PRIMARY KEY, name VARCHAR(50))");
72            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
73            $ztdPdo->exec("INSERT INTO `{$table}` VALUES (1, 'original')");
74            $statement = $ztdPdo->prepare(
75                "INSERT INTO `{$table}` VALUES (?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name)"
76            );
77            self::assertNotFalse($statement);
78
79            self::assertTrue($statement->execute(['1', 'updated']));
80
81            $rows = $ztdPdo->query("SELECT * FROM `{$table}` WHERE id = 1");
82            self::assertNotFalse($rows);
83            self::assertSame([['id' => 1, 'name' => 'updated']], $rows->fetchAll());
84        } finally {
85            $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
86        }
87    }
88}
89