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