packages/ztd-query-mysql/tests/Unit/Rewrite/FullText/MySqlFullTextSearchRewriterTest.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\MySql\Rewrite\FullText\MySqlFullTextSearchRewriter;
12
13#[CoversClass(MySqlFullTextSearchRewriter::class)]
14#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
15#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\FullText\ExpressionEditor::class)]
16final class MySqlFullTextSearchRewriterTest extends TestCase
17{
18 public function testRewritesNaturalLanguageExpressionIntoCteSafeRelevance(): void
19 {
20 $sql = "SELECT MATCH(title, body) AGAINST ('search terms') AS score FROM articles "
21 . "WHERE MATCH(title, body) AGAINST ('search terms')";
22 $result = (new MySqlFullTextSearchRewriter())->rewrite($sql);
23
24 $expression = "(CASE WHEN LOCATE(LOWER(NULLIF(TRIM(CAST(('search terms') AS CHAR)), '')), "
25 . "LOWER(CONCAT_WS(' ', COALESCE(CAST((title) AS CHAR), ''), "
26 . "COALESCE(CAST((body) AS CHAR), '')))) > 0 THEN 1.0 ELSE 0.0 END)";
27 self::assertSame(
28 "SELECT $expression AS score FROM articles WHERE $expression",
29 $result,
30 );
31 }
32
33 #[DataProvider('providerSearchModifier')]
34 public function testPreservesOnePreparedParameterWhileRemovingSearchModifier(string $modifier): void
35 {
36 $sql = "SELECT * FROM articles WHERE MATCH(`articles`.`title`) AGAINST (?$modifier)";
37 $result = (new MySqlFullTextSearchRewriter())->rewrite($sql);
38
39 self::assertSame(
40 "SELECT * FROM articles WHERE (CASE WHEN LOCATE(LOWER(NULLIF(TRIM(CAST((?) AS CHAR)), '')), "
41 . "LOWER(CONCAT_WS(' ', COALESCE(CAST((`articles`.`title`) AS CHAR), '')))) "
42 . '> 0 THEN 1.0 ELSE 0.0 END)',
43 $result,
44 );
45 }
46
47 /**
48
49 * @return iterable<string, array{string}>
50
51 */
52 public static function providerSearchModifier(): iterable
53 {
54 yield 'natural language' => [' IN NATURAL LANGUAGE MODE'];
55 yield 'boolean' => [' IN BOOLEAN MODE'];
56 yield 'query expansion' => [' WITH QUERY EXPANSION'];
57 yield 'natural expansion' => [' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION'];
58 }
59
60 #[DataProvider('providerUnchangedSql')]
61 public function testLeavesLiteralsCommentsAndMalformedExpressionsUntouched(string $sql): void
62 {
63 self::assertSame($sql, (new MySqlFullTextSearchRewriter())->rewrite($sql));
64 }
65
66 /**
67
68 * @return iterable<string, array{string}>
69
70 */
71 public static function providerUnchangedSql(): iterable
72 {
73 yield 'literal' => ["SELECT 'MATCH(title) AGAINST (search)'"];
74 yield 'comment' => ['SELECT 1 /* MATCH(title) AGAINST (search) */'];
75 yield 'keyword only' => ['SELECT MATCH'];
76 yield 'missing columns parentheses' => ['SELECT MATCH title'];
77 yield 'wrong columns delimiter' => ["SELECT MATCH + title) AGAINST ('search')"];
78 yield 'empty columns' => ['SELECT MATCH()'];
79 yield 'missing against' => ['SELECT MATCH(title)'];
80 yield 'wrong against keyword' => ["SELECT MATCH(title) BEFORE ('search')"];
81 yield 'missing query' => ['SELECT MATCH(title) AGAINST'];
82 yield 'missing query parentheses' => ["SELECT MATCH(title) AGAINST 'search'"];
83 yield 'empty query' => ['SELECT MATCH(title) AGAINST ()'];
84 }
85
86 public function testKeepsNestedExpressionsInsideTheirStructuralBoundaries(): void
87 {
88 $sql = 'SELECT MATCH(COALESCE(title, subtitle), body) '
89 . "AGAINST (IF(flag IN (1), 'search', 'other')) FROM articles";
90
91 self::assertSame(
92 "SELECT (CASE WHEN LOCATE(LOWER(NULLIF(TRIM(CAST((IF(flag IN (1), 'search', 'other')) AS CHAR)), '')), "
93 . "LOWER(CONCAT_WS(' ', COALESCE(CAST((COALESCE(title, subtitle)) AS CHAR), ''), "
94 . "COALESCE(CAST((body) AS CHAR), '')))) > 0 THEN 1.0 ELSE 0.0 END) FROM articles",
95 (new MySqlFullTextSearchRewriter())->rewrite($sql),
96 );
97 }
98
99 public function testDoesNotTreatAnUnrecognizedInClauseAsASearchModifier(): void
100 {
101 $sql = 'SELECT MATCH(title) AGAINST (flag IN unknown_mode) FROM articles';
102
103 self::assertSame(
104 "SELECT (CASE WHEN LOCATE(LOWER(NULLIF(TRIM(CAST((flag IN unknown_mode) AS CHAR)), '')), "
105 . "LOWER(CONCAT_WS(' ', COALESCE(CAST((title) AS CHAR), '')))) "
106 . '> 0 THEN 1.0 ELSE 0.0 END) FROM articles',
107 (new MySqlFullTextSearchRewriter())->rewrite($sql),
108 );
109 }
110
111 public function testFindsAModeModifierAfterANestedQueryExpression(): void
112 {
113 $result = (new MySqlFullTextSearchRewriter())->rewrite(
114 "SELECT MATCH(title) AGAINST (COALESCE(?, 'fallback') IN BOOLEAN MODE)",
115 );
116
117 self::assertStringContainsString("CAST((COALESCE(?, 'fallback')) AS CHAR)", $result);
118 self::assertStringNotContainsString('BOOLEAN MODE', $result);
119 }
120
121 public function testKeepsQueryKeywordWhenItIsTheSearchExpression(): void
122 {
123 $result = (new MySqlFullTextSearchRewriter())->rewrite(
124 'SELECT MATCH(title) AGAINST (QUERY)',
125 );
126
127 self::assertStringContainsString('CAST((QUERY) AS CHAR)', $result);
128 }
129
130 public function testTrimsWhitespaceInsideTheQueryParentheses(): void
131 {
132 $result = (new MySqlFullTextSearchRewriter())->rewrite(
133 "SELECT MATCH(title) AGAINST ( 'needle' )",
134 );
135
136 self::assertStringContainsString("CAST(('needle') AS CHAR)", $result);
137 self::assertStringNotContainsString("CAST(( 'needle' ) AS CHAR)", $result);
138 }
139}
140