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