packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/FullTextSearchTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use PDO;
8use PHPUnit\Framework\Attributes\CoversNothing;
9use PHPUnit\Framework\Attributes\Large;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Adapter\Pdo\ZtdPdo;
12
13/**
14 * @requires extension pdo_sqlite
15 * @group integration
16 * @group sqlite
17 */
18#[CoversNothing]
19#[Large]
20final class FullTextSearchTest extends TestCase
21{
22    public function testFts5SearchReadsOnlyShadowRows(): void
23    {
24        $rawPdo = new PDO('sqlite::memory:', null, null, [
25            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
26            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
27        ]);
28        $rawPdo->exec('CREATE VIRTUAL TABLE fts_articles USING fts5(title, body)');
29        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
30
31        $empty = $ztdPdo->query("SELECT title FROM fts_articles WHERE fts_articles MATCH 'search'");
32        self::assertNotFalse($empty);
33        self::assertSame([], $empty->fetchAll());
34
35        self::assertSame(3, $ztdPdo->exec(
36            'INSERT INTO fts_articles (title, body) VALUES '
37            . "('Search guide', 'exact search terms'), "
38            . "('Body match', 'needle in body'), "
39            . "('Other', 'unrelated')",
40        ));
41
42        $tableMatch = $ztdPdo->query(
43            "SELECT title FROM fts_articles WHERE fts_articles MATCH 'search terms'",
44        );
45        self::assertNotFalse($tableMatch);
46        self::assertSame([['title' => 'Search guide']], $tableMatch->fetchAll());
47
48        $columnMatch = $ztdPdo->query(
49            "SELECT title FROM fts_articles WHERE body MATCH 'needle'",
50        );
51        self::assertNotFalse($columnMatch);
52        self::assertSame([['title' => 'Body match']], $columnMatch->fetchAll());
53
54        $prepared = $ztdPdo->prepare(
55            'SELECT title FROM fts_articles WHERE fts_articles MATCH ?',
56        );
57        self::assertNotFalse($prepared);
58        self::assertTrue($prepared->execute(['unrelated']));
59        self::assertSame([['title' => 'Other']], $prepared->fetchAll());
60
61        $equals = $ztdPdo->query(
62            "SELECT title FROM fts_articles WHERE fts_articles = 'exact search'",
63        );
64        self::assertNotFalse($equals);
65        self::assertSame([['title' => 'Search guide']], $equals->fetchAll());
66
67        $physical = $rawPdo->query('SELECT COUNT(*) FROM fts_articles');
68        self::assertNotFalse($physical);
69        self::assertSame(0, (int) $physical->fetchColumn());
70    }
71}
72