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\Endpoint;
8use Container\PostgreSqlRelease;
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_pgsql
17 * @group integration
18 * @group postgres
19 */
20#[CoversNothing]
21#[Large]
22final class TableSampleTest extends TestCase
23{
24    public function testTableSampleUsesShadowRows(): void
25    {
26        $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
27        /** @var PDO $pdo */
28        $pdo = new PDO(
29            $endpoint->dsn(),
30            $endpoint->username,
31            $endpoint->password,
32            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
33        );
34
35        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
36        $pdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
37        $pdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
38
39
40        try {
41            $pdo->exec('CREATE TABLE sample_data (id INTEGER PRIMARY KEY, label TEXT NOT NULL)');
42            $ztdPdo = ZtdPdo::fromPdo($pdo);
43            self::assertSame(4, $ztdPdo->exec(
44                "INSERT INTO sample_data VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D')",
45            ));
46
47            $bernoulli = $ztdPdo->query(
48                'SELECT sampled.id FROM sample_data AS sampled TABLESAMPLE BERNOULLI (100) ORDER BY sampled.id',
49            );
50            self::assertNotFalse($bernoulli);
51            self::assertSame([1, 2, 3, 4], $bernoulli->fetchAll(PDO::FETCH_COLUMN));
52
53            $system = $ztdPdo->query(
54                'SELECT id FROM sample_data TABLESAMPLE SYSTEM (100) REPEATABLE (17.5) ORDER BY id',
55            );
56            self::assertNotFalse($system);
57            self::assertSame([1, 2, 3, 4], $system->fetchAll(PDO::FETCH_COLUMN));
58
59            $empty = $ztdPdo->query('SELECT id FROM sample_data TABLESAMPLE BERNOULLI (0)');
60            self::assertNotFalse($empty);
61            self::assertSame([], $empty->fetchAll(PDO::FETCH_COLUMN));
62
63            $first = $ztdPdo->query(
64                'SELECT id FROM sample_data TABLESAMPLE BERNOULLI (50) REPEATABLE (42) ORDER BY id',
65            );
66            $second = $ztdPdo->query(
67                'SELECT id FROM sample_data TABLESAMPLE BERNOULLI (50) REPEATABLE (42) ORDER BY id',
68            );
69            self::assertNotFalse($first);
70            self::assertNotFalse($second);
71            self::assertSame($first->fetchAll(PDO::FETCH_COLUMN), $second->fetchAll(PDO::FETCH_COLUMN));
72
73            $physical = $pdo->query('SELECT COUNT(*) FROM sample_data');
74            self::assertNotFalse($physical);
75            self::assertSame(0, (int) $physical->fetchColumn());
76        } finally {
77            $pdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
78        }
79    }
80}
81