packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/TableSampleTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\PostgreSql16Container;
8use PDO;
9use PHPUnit\Framework\Attributes\CoversNothing;
10use PHPUnit\Framework\Attributes\Large;
11use PHPUnit\Framework\TestCase;
12use ZtdQuery\Adapter\Pdo\ZtdPdo;
13
14/**
15 * @requires extension pdo_pgsql
16 * @group integration
17 * @group postgres
18 */
19#[CoversNothing]
20#[Large]
21final class TableSampleTest extends TestCase
22{
23    public function testTableSampleUsesShadowRows(): void
24    {
25        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
26        /** @var PDO $pdo */
27        $pdo = new PDO(
28            sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
29            'test',
30            'test',
31            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
32        );
33
34        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
35        $pdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
36        $pdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
37
38
39        try {
40            $pdo->exec('CREATE TABLE sample_data (id INTEGER PRIMARY KEY, label TEXT NOT NULL)');
41            $ztdPdo = ZtdPdo::fromPdo($pdo);
42            self::assertSame(4, $ztdPdo->exec(
43                "INSERT INTO sample_data VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D')",
44            ));
45
46            $bernoulli = $ztdPdo->query(
47                'SELECT sampled.id FROM sample_data AS sampled TABLESAMPLE BERNOULLI (100) ORDER BY sampled.id',
48            );
49            self::assertNotFalse($bernoulli);
50            self::assertSame([1, 2, 3, 4], $bernoulli->fetchAll(PDO::FETCH_COLUMN));
51
52            $system = $ztdPdo->query(
53                'SELECT id FROM sample_data TABLESAMPLE SYSTEM (100) REPEATABLE (17.5) ORDER BY id',
54            );
55            self::assertNotFalse($system);
56            self::assertSame([1, 2, 3, 4], $system->fetchAll(PDO::FETCH_COLUMN));
57
58            $empty = $ztdPdo->query('SELECT id FROM sample_data TABLESAMPLE BERNOULLI (0)');
59            self::assertNotFalse($empty);
60            self::assertSame([], $empty->fetchAll(PDO::FETCH_COLUMN));
61
62            $first = $ztdPdo->query(
63                'SELECT id FROM sample_data TABLESAMPLE BERNOULLI (50) REPEATABLE (42) ORDER BY id',
64            );
65            $second = $ztdPdo->query(
66                'SELECT id FROM sample_data TABLESAMPLE BERNOULLI (50) REPEATABLE (42) ORDER BY id',
67            );
68            self::assertNotFalse($first);
69            self::assertNotFalse($second);
70            self::assertSame($first->fetchAll(PDO::FETCH_COLUMN), $second->fetchAll(PDO::FETCH_COLUMN));
71
72            $physical = $pdo->query('SELECT COUNT(*) FROM sample_data');
73            self::assertNotFalse($physical);
74            self::assertSame(0, (int) $physical->fetchColumn());
75        } finally {
76            $pdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
77        }
78    }
79}
80