packages/ztd-query-sqlite/tests/Unit/Rewrite/Index/SqliteIndexHintStripperTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\Index;
6
7use Generator;
8use PHPUnit\Framework\Attributes\CoversClass;
9use PHPUnit\Framework\Attributes\DataProvider;
10use PHPUnit\Framework\Attributes\UsesClass;
11use PHPUnit\Framework\TestCase;
12use ZtdQuery\Platform\Sqlite\Rewrite\Index\SqliteIndexHintStripper;
13use ZtdQuery\Sql\SqlToken;
14use ZtdQuery\Sql\SqlTokenKind;
15
16#[CoversClass(SqliteIndexHintStripper::class)]
17#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\RelationSourceParser::class)]
18#[UsesClass(\ZtdQuery\Platform\Sqlite\Rewrite\Index\IndexHintTokens::class)]
19#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexerProfile::class)]
20#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\SqliteSelectRelationParser::class)]
21final class SqliteIndexHintStripperTest extends TestCase
22{
23    public function testStripsIndexedByFromShadowSource(): void
24    {
25        self::assertSame(
26            'SELECT * FROM products  WHERE category = ?',
27            (new SqliteIndexHintStripper())->strip(
28                'SELECT * FROM products INDEXED BY idx_category WHERE category = ?',
29                ['products'],
30            ),
31        );
32    }
33
34    public function testStripsHintsAfterAliasesAndInNestedScopes(): void
35    {
36        self::assertSame(
37            'SELECT * FROM products AS p  WHERE EXISTS (SELECT 1 FROM products nested )',
38            (new SqliteIndexHintStripper())->strip(
39                'SELECT * FROM products AS p INDEXED BY [idx_category] WHERE EXISTS (SELECT 1 FROM products nested NOT INDEXED)',
40                ['products'],
41            ),
42        );
43    }
44
45    public function testPreservesHintsForPhysicalSources(): void
46    {
47        $sql = 'SELECT * FROM products INDEXED BY idx_category JOIN audit NOT INDEXED ON audit.id = products.id';
48
49        self::assertSame($sql, (new SqliteIndexHintStripper())->strip($sql, ['users']));
50    }
51
52    public function testDoesNotTreatExpressionsAsSourceHints(): void
53    {
54        $sql = "SELECT 'INDEXED BY idx_category' FROM products WHERE note = 'NOT INDEXED'";
55
56        self::assertSame($sql, (new SqliteIndexHintStripper())->strip($sql, ['products']));
57    }
58
59    public function testMatchesShadowNamesCaseInsensitivelyAfterPhysicalSource(): void
60    {
61        self::assertSame(
62            'SELECT * FROM audit INDEXED BY audit_idx JOIN Products  ON Products.id = audit.id',
63            (new SqliteIndexHintStripper())->strip(
64                'SELECT * FROM audit INDEXED BY audit_idx JOIN Products INDEXED BY products_idx ON Products.id = audit.id',
65                ['PRODUCTS'],
66            ),
67        );
68    }
69
70    public function testStripsEveryHintAcrossConsecutiveShadowSources(): void
71    {
72        self::assertSame(
73            'SELECT * FROM products  JOIN products  ON TRUE',
74            (new SqliteIndexHintStripper())->strip(
75                'SELECT * FROM products NOT INDEXED JOIN products INDEXED BY `idx_products` ON TRUE',
76                ['products'],
77            ),
78        );
79    }
80
81    #[DataProvider('providerIncompleteAndMalformedHints')]
82    public function testPreservesIncompleteAndMalformedHints(string $sql): void
83    {
84        self::assertSame($sql, (new SqliteIndexHintStripper())->strip($sql, ['products']));
85    }
86
87    /**
88     * @return Generator<string, array{string}>
89     */
90    public static function providerIncompleteAndMalformedHints(): Generator
91    {
92        yield 'no hint' => ['SELECT * FROM products'];
93        yield 'not without indexed' => ['SELECT * FROM products NOT'];
94        yield 'not followed by clause' => ['SELECT * FROM products NOT WHERE TRUE'];
95        yield 'indexed without by' => ['SELECT * FROM products INDEXED'];
96        yield 'indexed followed by names' => ['SELECT * FROM products INDEXED wrong hint'];
97        yield 'clause followed by by and name' => ['SELECT * FROM products WHERE BY hint'];
98        yield 'symbol as index name' => ['SELECT * FROM products INDEXED BY ) ] WHERE TRUE'];
99    }
100
101    public function testDoesNotConfuseCommaBoundaryWithAlias(): void
102    {
103        self::assertSame(
104            'SELECT * FROM products, users  WHERE TRUE',
105            (new SqliteIndexHintStripper())->strip(
106                'SELECT * FROM products, users INDEXED BY idx_users WHERE TRUE',
107                ['products', 'users'],
108            ),
109        );
110    }
111
112    public function testStripsEmptyBracketIndexAfterBracketAlias(): void
113    {
114        self::assertSame(
115            'SELECT * FROM products [p]  WHERE TRUE',
116            (new SqliteIndexHintStripper())->strip(
117                'SELECT * FROM products [p] INDEXED BY [] WHERE TRUE',
118                ['products'],
119            ),
120        );
121    }
122
123    public function testIdentifierEndAcceptsBracketSymbolTokens(): void
124    {
125        $tokens = [
126            new SqlToken(SqlTokenKind::Symbol, '[', 0, 0, 0),
127            new SqlToken(SqlTokenKind::Symbol, ']', 1, 0, 0),
128        ];
129
130        self::assertSame(2, \ZtdQuery\Platform\Sqlite\Rewrite\Index\IndexHintTokens::identifierEndIndex($tokens, 0));
131    }
132}
133