packages/ztd-query-pdo-adapter/tests/Integration/MySql/CaseWhereTest.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 CaseWhereTest extends TestCase
18{
19    public function testUpdateAndDeleteRestrictRowsWithCaseExpression(): 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        $updates = 'prefix_' . bin2hex(random_bytes(8));
35        $deletes = 'prefix_' . bin2hex(random_bytes(8));
36
37        try {
38            $rawPdo->exec("CREATE TABLE `{$updates}` (id INT PRIMARY KEY, score INT)");
39            $rawPdo->exec("CREATE TABLE `{$deletes}` (id INT PRIMARY KEY, score INT)");
40            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
41            $rows = 'VALUES (1, 85), (2, 60), (3, 95), (4, 45)';
42            $ztdPdo->exec("INSERT INTO `{$updates}` {$rows}");
43            $ztdPdo->exec("INSERT INTO `{$deletes}` {$rows}");
44
45            self::assertSame(2, $ztdPdo->exec("UPDATE `{$updates}` SET score = 0 WHERE CASE WHEN score > 80 THEN 1 ELSE 0 END = 1"));
46            self::assertSame(2, $ztdPdo->exec("DELETE FROM `{$deletes}` WHERE CASE WHEN score > 80 THEN 1 ELSE 0 END = 1"));
47
48            $updated = $ztdPdo->query("SELECT id, score FROM `{$updates}` ORDER BY id");
49            $remaining = $ztdPdo->query("SELECT id, score FROM `{$deletes}` ORDER BY id");
50            self::assertNotFalse($updated);
51            self::assertNotFalse($remaining);
52            self::assertSame([
53                ['id' => 1, 'score' => 0],
54                ['id' => 2, 'score' => 60],
55                ['id' => 3, 'score' => 0],
56                ['id' => 4, 'score' => 45],
57            ], $updated->fetchAll(PDO::FETCH_ASSOC));
58            self::assertSame([
59                ['id' => 2, 'score' => 60],
60                ['id' => 4, 'score' => 45],
61            ], $remaining->fetchAll(PDO::FETCH_ASSOC));
62
63            $physicalUpdates = $rawPdo->query("SELECT * FROM `{$updates}`");
64            $physicalDeletes = $rawPdo->query("SELECT * FROM `{$deletes}`");
65            self::assertNotFalse($physicalUpdates);
66            self::assertNotFalse($physicalDeletes);
67            self::assertSame([], $physicalUpdates->fetchAll(PDO::FETCH_ASSOC));
68            self::assertSame([], $physicalDeletes->fetchAll(PDO::FETCH_ASSOC));
69        } finally {
70            $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
71        }
72    }
73}
74