packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/FullTextSearchTest.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 FullTextSearchTest extends TestCase
23{
24    public function testTextSearchFunctionsPreserveTsvectorShadowType(): void
25    {
26        $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
27        /** @var PDO $rawPdo */
28        $rawPdo = 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        $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
37        $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
38
39
40        try {
41            $rawPdo->exec(
42                'CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, body TEXT, '
43                . 'search_document TSVECTOR GENERATED ALWAYS AS '
44                . "(to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED)",
45            );
46            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
47            self::assertSame(3, $ztdPdo->exec(
48                'INSERT INTO articles (id, title, body) VALUES '
49                . "(1, 'Search guide', 'exact search terms'), "
50                . "(2, 'Body match', 'needle in body'), "
51                . "(3, 'Other', 'unrelated')",
52            ));
53
54            $typed = $ztdPdo->query(
55                'SELECT id, pg_typeof(search_document)::text AS type FROM articles '
56                . "WHERE search_document @@ plainto_tsquery('english', 'search terms')",
57            );
58            self::assertNotFalse($typed);
59            self::assertSame([['id' => 1, 'type' => 'tsvector']], $typed->fetchAll());
60
61            $prepared = $ztdPdo->prepare(
62                "SELECT id FROM articles WHERE to_tsvector('english', body) @@ plainto_tsquery('english', $1)",
63            );
64            self::assertNotFalse($prepared);
65            self::assertTrue($prepared->execute(['needle']));
66            self::assertSame([2], $prepared->fetchAll(PDO::FETCH_COLUMN));
67
68            $physical = $rawPdo->query('SELECT COUNT(*) FROM articles');
69            self::assertNotFalse($physical);
70            self::assertSame(0, (int) $physical->fetchColumn());
71        } finally {
72            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
73        }
74    }
75}
76