packages/ztd-query-sqlite/tests/Unit/Shadow/Mutation/Upsert/SqliteUpsertExpressionParserTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Shadow\Mutation\Upsert;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Exception\UnsupportedSqlException;
12use ZtdQuery\Platform\Sqlite\Shadow\Mutation\Upsert\SqliteUpsertExpressionParser;
13
14#[CoversClass(SqliteUpsertExpressionParser::class)]
15#[UsesClass(\ZtdQuery\Platform\Sqlite\Shadow\Mutation\Upsert\ArithmeticExpressionParser::class)]
16#[UsesClass(\ZtdQuery\Platform\Sqlite\Shadow\Mutation\Upsert\ComparisonExpressionParser::class)]
17#[UsesClass(\ZtdQuery\Platform\Sqlite\Shadow\Mutation\Upsert\ExpressionCursor::class)]
18#[UsesClass(\ZtdQuery\Platform\Sqlite\Shadow\Mutation\Upsert\ExpressionTokenDecoder::class)]
19#[UsesClass(\ZtdQuery\Platform\Sqlite\Shadow\Mutation\Upsert\LogicalExpressionParser::class)]
20#[UsesClass(\ZtdQuery\Platform\Sqlite\Shadow\Mutation\Upsert\PrimaryExpressionParser::class)]
21#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexerProfile::class)]
22final class SqliteUpsertExpressionParserTest extends TestCase
23{
24    #[DataProvider('providerSqliteExpressionCases')]
25    public function testParsesSqliteExpressionCases(string $sql, mixed $expected): void
26    {
27        self::assertSame(
28            $expected,
29            (new SqliteUpsertExpressionParser())->parse($sql, 'items')->evaluate([], [], 'items'),
30        );
31    }
32
33    /**
34     * @return iterable<string, array{string, mixed}>
35     */
36    public static function providerSqliteExpressionCases(): iterable
37    {
38        yield 'chained or' => ['1 OR 0 OR 0', true];
39        yield 'chained and' => ['1 AND 1 AND 0', false];
40        yield 'equal' => ['1 = 2', false];
41        yield 'bang not equal' => ['1 != 2', true];
42        yield 'angle not equal' => ['1 <> 2', true];
43        yield 'less' => ['1 < 2', true];
44        yield 'less or equal' => ['1 <= 2', true];
45        yield 'greater' => ['2 > 1', true];
46        yield 'greater or equal' => ['2 >= 1', true];
47        yield 'chained additive' => ['10 - 3 + 2', 9];
48        yield 'chained multiplicative' => ['20 / 5 % 3 * 2', 2];
49        yield 'nested unary minus' => ['- -2', 2];
50        yield 'nested unary plus' => ['+ +2', 2];
51        yield 'unary minus' => ['-2', -2];
52        yield 'unary plus' => ['+2', 2];
53        yield 'nested not' => ['NOT NOT TRUE', true];
54        yield 'parenthesized precedence' => ['(1 + 2) * 3', 9];
55        yield 'null' => ['NULL', null];
56        yield 'false' => ['FALSE', false];
57        yield 'underscored integer' => ['1_000', 1000];
58        yield 'exponent' => ['1.5e1', 15.0];
59        yield 'escaped string' => ["'it''s'", "it's"];
60    }
61
62    public function testParsesExcludedAndExistingReferences(): void
63    {
64        $expression = (new SqliteUpsertExpressionParser())->parse(
65            '`items`.`quantity` + EXCLUDED.`quantity` * 2',
66            'items',
67        );
68
69        self::assertSame(11, $expression->evaluate(['quantity' => 5], ['quantity' => 3], 'items'));
70    }
71
72    public function testUnescapesQuotedSqliteIdentifiers(): void
73    {
74        $parser = new SqliteUpsertExpressionParser();
75
76        self::assertSame(
77            5,
78            $parser->parse('"quan""tity"', 'items')->evaluate(['quan"tity' => 5], [], 'items'),
79        );
80        self::assertSame(
81            5,
82            $parser->parse('`quan``tity`', 'items')->evaluate(['quan`tity' => 5], [], 'items'),
83        );
84    }
85
86    public function testParsesPredicate(): void
87    {
88        $expression = (new SqliteUpsertExpressionParser())->parse(
89            "score >= 80 AND excluded.name <> 'blocked'",
90            'items',
91        );
92
93        self::assertTrue($expression->matches(['score' => 80], ['name' => 'ready'], 'items'));
94    }
95
96    public function testParseIfSupportedReturnsNullForUnsupportedFunction(): void
97    {
98        self::assertNull((new SqliteUpsertExpressionParser())->parseIfSupported('COALESCE(score, 0)', 'items'));
99    }
100
101    public function testRejectsMySqlValuesFunction(): void
102    {
103        $this->expectException(UnsupportedSqlException::class);
104
105        (new SqliteUpsertExpressionParser())->parse('VALUES(quantity)', 'items');
106    }
107
108    #[DataProvider('providerInvalidSqliteExpression')]
109    public function testRejectsInvalidSqliteExpression(string $sql): void
110    {
111        self::assertNull((new SqliteUpsertExpressionParser())->parseIfSupported($sql, 'items'));
112    }
113
114    /**
115     * @return iterable<string, array{string}>
116     */
117    public static function providerInvalidSqliteExpression(): iterable
118    {
119        yield 'empty' => [''];
120        yield 'missing close parenthesis' => ['(1 + 2'];
121        yield 'wrong close parenthesis' => ['(1 + 2 value'];
122        yield 'extra close parenthesis' => ['1 + 2)'];
123        yield 'missing additive operand' => ['1 +'];
124        yield 'missing comparison operand' => ['1 ='];
125        yield 'missing qualified column' => ['items.'];
126        yield 'invalid qualified column' => ['items.+'];
127        yield 'invalid comparison pair' => ['1 ! 2'];
128        yield 'double equals' => ['1 == 1'];
129        yield 'unknown qualifier' => ['other.value'];
130        yield 'symbol primary' => ['*'];
131    }
132}
133