packages/ztd-query-pdo-adapter/tests/Integration/MySql/ViewTest.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/**
16 * @requires extension pdo_mysql
17 * @group integration
18 * @group mysql
19 */
20#[CoversNothing]
21#[Large]
22final class ViewTest extends TestCase
23{
24    public function testViewsReadShadowWritesAcrossFiltersJoinsAggregatesAndPreparation(): void
25    {
26        $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
27        /** @var PDO $pdo */
28        $pdo = new PDO(
29            sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
30            'root',
31            'root',
32            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
33        );
34
35        $databaseName = 'ztd_' . bin2hex(random_bytes(8));
36        $pdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
37        $pdo->exec(sprintf('USE `%s`', $databaseName));
38
39
40        try {
41            $pdo->exec('CREATE TABLE accounts (id INT PRIMARY KEY, region VARCHAR(20), amount INT, active BOOLEAN)');
42            $pdo->exec('CREATE TABLE regions (code VARCHAR(20) PRIMARY KEY, label VARCHAR(20))');
43            $pdo->exec('CREATE VIEW active_accounts AS SELECT id, region, amount FROM accounts WHERE active = 1');
44            $pdo->exec('CREATE VIEW account_labels AS SELECT a.id, r.label, a.amount FROM active_accounts a JOIN regions r ON r.code = a.region');
45            $pdo->exec('CREATE VIEW region_totals AS SELECT region, COUNT(*) AS account_count, SUM(amount) AS total_amount FROM active_accounts GROUP BY region');
46            $ztdPdo = ZtdPdo::fromPdo($pdo);
47
48            $ztdPdo->exec("INSERT INTO accounts VALUES (1, 'north', 100, 1), (2, 'south', 200, 0), (3, 'north', 300, 1)");
49            $ztdPdo->exec("INSERT INTO regions VALUES ('north', 'North'), ('south', 'South')");
50
51            $simple = $ztdPdo->query('SELECT id FROM active_accounts ORDER BY id');
52            self::assertNotFalse($simple);
53            self::assertSame([1, 3], $simple->fetchAll(PDO::FETCH_COLUMN));
54
55            $prepared = $ztdPdo->prepare('SELECT id FROM active_accounts WHERE amount >= ? ORDER BY id');
56            self::assertNotFalse($prepared);
57            self::assertTrue($prepared->execute([150]));
58            self::assertSame([3], $prepared->fetchAll(PDO::FETCH_COLUMN));
59
60            $joined = $ztdPdo->query('SELECT id, label FROM account_labels ORDER BY id');
61            self::assertNotFalse($joined);
62            self::assertSame(
63                [['id' => 1, 'label' => 'North'], ['id' => 3, 'label' => 'North']],
64                $joined->fetchAll(),
65            );
66
67            $aggregate = $ztdPdo->query('SELECT region, CAST(account_count AS SIGNED) AS account_count, CAST(total_amount AS SIGNED) AS total_amount FROM region_totals');
68            self::assertNotFalse($aggregate);
69            self::assertSame(
70                [['region' => 'north', 'account_count' => 2, 'total_amount' => 400]],
71                $aggregate->fetchAll(),
72            );
73        } finally {
74            $pdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
75        }
76    }
77}
78