packages/ztd-query-sqlite/tests/Unit/Rewrite/FullText/SqliteFullTextSearchRewriterTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\FullText;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Platform\Sqlite\Rewrite\FullText\SqliteFullTextSearchRewriter;
12use ZtdQuery\Platform\Sqlite\Sql\SqliteIdentifierQuoter;
13use ZtdQuery\Platform\Sqlite\Sql\SqliteParser;
14
15#[CoversClass(SqliteFullTextSearchRewriter::class)]
16#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\AssignmentColumnParser::class)]
17#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\AssignmentParser::class)]
18#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\ValueListParser::class)]
19#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Insert\InsertClauseParser::class)]
20#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\ExpressionSpan::class)]
21#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\IdentifierDecoder::class)]
22#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\LiteralMasker::class)]
23#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\OpaqueSqlSpan::class)]
24#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\QuotedSpan::class)]
25#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\TopLevelKeywordScanner::class)]
26#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\RelationSourceParser::class)]
27#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\StatementClassifier::class)]
28#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\StatementStructure::class)]
29#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\TargetTableParser::class)]
30#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Update\UpdateClauseParser::class)]
31#[UsesClass(\ZtdQuery\Platform\Sqlite\Rewrite\FullText\FullTextColumns::class)]
32#[UsesClass(\ZtdQuery\Platform\Sqlite\Rewrite\FullText\MatchExpressionRewriter::class)]
33#[UsesClass(SqliteIdentifierQuoter::class)]
34#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexerProfile::class)]
35#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexicalMasker::class)]
36#[UsesClass(SqliteParser::class)]
37#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\SqliteSelectRelationParser::class)]
38final class SqliteFullTextSearchRewriterTest extends TestCase
39{
40 public function testRewritesTableMatchAcrossEveryFtsColumn(): void
41 {
42 $result = (new SqliteFullTextSearchRewriter())->rewrite(
43 "SELECT title FROM fts_articles WHERE fts_articles MATCH 'search'",
44 ['fts_articles' => ['columns' => ['title', 'body'], 'rows' => []]],
45 );
46
47 self::assertSame(
48 "SELECT title FROM fts_articles WHERE (INSTR(LOWER(COALESCE(CAST(\"title\" AS TEXT), '') "
49 . "|| ' ' || COALESCE(CAST(\"body\" AS TEXT), '')), "
50 . "LOWER(NULLIF(TRIM(CAST(('search') AS TEXT)), ''))) > 0)",
51 $result,
52 );
53 }
54
55 public function testRewritesColumnMatchAndTableEqualsWithSingleParameters(): void
56 {
57 $rewriter = new SqliteFullTextSearchRewriter();
58 $column = $rewriter->rewrite(
59 'SELECT title FROM fts_articles WHERE title MATCH ?',
60 ['fts_articles' => ['columns' => ['title', 'body'], 'rows' => []]],
61 );
62 $table = $rewriter->rewrite(
63 'SELECT title FROM fts_articles WHERE "fts_articles" = :query',
64 ['fts_articles' => ['columns' => ['title', 'body'], 'rows' => []]],
65 );
66
67 self::assertSame(1, substr_count($column, '?'));
68 self::assertStringContainsString('COALESCE(CAST("title" AS TEXT)', $column);
69 self::assertStringNotContainsString('"body"', $column);
70 self::assertSame(1, substr_count($table, ':query'));
71 self::assertStringContainsString('COALESCE(CAST("body" AS TEXT)', $table);
72 }
73
74 #[DataProvider('providerUnchangedSql')]
75 public function testLeavesUnknownAmbiguousAndNonQueryOperandsUntouched(string $sql): void
76 {
77 $rewriter = new SqliteFullTextSearchRewriter();
78 $tables = [
79 'fts_articles' => ['columns' => ['title', 'body'], 'rows' => []],
80 'other' => ['columns' => ['title'], 'rows' => []],
81 ];
82
83 self::assertSame($sql, $rewriter->rewrite($sql, $tables));
84 }
85
86 /**
87 * @return iterable<string, array{string}>
88 */
89 public static function providerUnchangedSql(): iterable
90 {
91 yield 'unknown column' => ['SELECT * FROM fts_articles WHERE missing MATCH ?'];
92 yield 'ambiguous column' => ['SELECT * FROM fts_articles, other WHERE title MATCH ?'];
93 yield 'non-query operand' => ['SELECT * FROM fts_articles WHERE fts_articles MATCH 1'];
94 yield 'ordinary equality' => ['SELECT * FROM fts_articles WHERE title = ?'];
95 yield 'ordinary symbol operator' => ["SELECT * FROM fts_articles WHERE title + 'suffix'"];
96 yield 'literal' => ["SELECT 'fts_articles MATCH search' FROM fts_articles"];
97 }
98
99 public function testRewritesMultipleExpressionsFromRightToLeft(): void
100 {
101 $result = (new SqliteFullTextSearchRewriter())->rewrite(
102 "SELECT title FROM fts_articles WHERE title MATCH 'search' OR body MATCH 'needle'",
103 ['fts_articles' => ['columns' => ['title', 'body'], 'rows' => []]],
104 );
105
106 self::assertSame(
107 "SELECT title FROM fts_articles WHERE (INSTR(LOWER(COALESCE(CAST(\"title\" AS TEXT), '')), "
108 . "LOWER(NULLIF(TRIM(CAST(('search') AS TEXT)), ''))) > 0) OR "
109 . "(INSTR(LOWER(COALESCE(CAST(\"body\" AS TEXT), '')), "
110 . "LOWER(NULLIF(TRIM(CAST(('needle') AS TEXT)), ''))) > 0)",
111 $result,
112 );
113 }
114
115 public function testSkipsAnUnknownExpressionBeforeAValidExpression(): void
116 {
117 $result = (new SqliteFullTextSearchRewriter())->rewrite(
118 "SELECT title FROM fts_articles WHERE missing MATCH ? OR body MATCH 'needle'",
119 ['fts_articles' => ['columns' => ['title', 'body'], 'rows' => []]],
120 );
121
122 self::assertSame(
123 'SELECT title FROM fts_articles WHERE missing MATCH ? OR '
124 . "(INSTR(LOWER(COALESCE(CAST(\"body\" AS TEXT), '')), "
125 . "LOWER(NULLIF(TRIM(CAST(('needle') AS TEXT)), ''))) > 0)",
126 $result,
127 );
128 }
129
130 public function testFindsATableAndColumnAfterEarlierNonMatches(): void
131 {
132 $result = (new SqliteFullTextSearchRewriter())->rewrite(
133 "SELECT title FROM target WHERE title MATCH 'needle'",
134 [
135 'other' => ['columns' => ['other_title'], 'rows' => []],
136 'target' => ['columns' => ['id', 'title'], 'rows' => []],
137 ],
138 );
139
140 self::assertSame(
141 "SELECT title FROM target WHERE (INSTR(LOWER(COALESCE(CAST(\"title\" AS TEXT), '')), "
142 . "LOWER(NULLIF(TRIM(CAST(('needle') AS TEXT)), ''))) > 0)",
143 $result,
144 );
145 }
146
147 public function testLeavesANonFtsSymbolAfterATableNameUntouched(): void
148 {
149 $sql = "SELECT * FROM fts_articles WHERE fts_articles + 'suffix'";
150
151 self::assertSame(
152 $sql,
153 (new SqliteFullTextSearchRewriter())->rewrite(
154 $sql,
155 ['fts_articles' => ['columns' => ['title'], 'rows' => []]],
156 ),
157 );
158 }
159
160 public function testLeavesColumnEqualityUntouchedWithASingleTable(): void
161 {
162 $sql = 'SELECT * FROM fts_articles WHERE title = ?';
163
164 self::assertSame(
165 $sql,
166 (new SqliteFullTextSearchRewriter())->rewrite(
167 $sql,
168 ['fts_articles' => ['columns' => ['title'], 'rows' => []]],
169 ),
170 );
171 }
172
173 public function testSkipsAnOperatorWithoutALeftOperandBeforeAValidExpression(): void
174 {
175 $result = (new SqliteFullTextSearchRewriter())->rewrite(
176 "MATCH ? OR title MATCH 'needle'",
177 ['fts_articles' => ['columns' => ['title'], 'rows' => []]],
178 );
179
180 self::assertStringStartsWith('MATCH ? OR (INSTR(', $result);
181 }
182
183 public function testFindsATableMatchAfterAnEarlierContext(): void
184 {
185 $result = (new SqliteFullTextSearchRewriter())->rewrite(
186 "SELECT * FROM target WHERE target MATCH 'needle'",
187 [
188 'other' => ['columns' => ['other_title'], 'rows' => []],
189 'target' => ['columns' => ['title'], 'rows' => []],
190 ],
191 );
192
193 self::assertStringContainsString('COALESCE(CAST("title" AS TEXT)', $result);
194 self::assertStringNotContainsString("target MATCH 'needle'", $result);
195 }
196
197 public function testRejectsANonIdentifierEvenWhenItsTextMatchesATableName(): void
198 {
199 $sql = "SELECT * FROM fts_articles WHERE :target MATCH 'needle'";
200
201 self::assertSame(
202 $sql,
203 (new SqliteFullTextSearchRewriter())->rewrite(
204 $sql,
205 [':target' => ['columns' => ['title'], 'rows' => []]],
206 ),
207 );
208 }
209}
210