packages/ztd-query-pdo-adapter/tests/Integration/MySql/FullTextSearchTest.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 FullTextSearchTest extends TestCase
23{
24    public function testMatchAgainstReadsOnlyShadowRows(): void
25    {
26        $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
27        /** @var PDO $rawPdo */
28        $rawPdo = 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        $rawPdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
37        $rawPdo->exec(sprintf('USE `%s`', $databaseName));
38
39
40        try {
41            $rawPdo->exec(
42                'CREATE TABLE articles (id INT PRIMARY KEY, title VARCHAR(255), body TEXT, '
43                . 'FULLTEXT KEY article_search (title, body)) ENGINE=InnoDB',
44            );
45            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
46            self::assertSame(3, $ztdPdo->exec(
47                'INSERT INTO articles VALUES '
48                . "(1, 'Search guide', 'exact search terms'), "
49                . "(2, 'Body match', 'needle in body'), "
50                . "(3, 'Other', 'unrelated')",
51            ));
52
53            $literal = $ztdPdo->query(
54                "SELECT id, MATCH(title, body) AGAINST ('search terms') AS score "
55                . "FROM articles WHERE MATCH(title, body) AGAINST ('search terms') ORDER BY score DESC",
56            );
57            self::assertNotFalse($literal);
58            self::assertSame([['id' => 1, 'score' => '1.0']], $literal->fetchAll());
59
60            $prepared = $ztdPdo->prepare(
61                'SELECT id FROM articles WHERE MATCH(title, body) AGAINST (?)',
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 DATABASE IF EXISTS `%s`', $databaseName));
72        }
73    }
74}
75