packages/ztd-query-mysql/tests/Unit/Sql/Upsert/MySqlUpsertAssignmentExtractorTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Sql\Upsert;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Platform\MySql\Sql\Upsert\MySqlUpsertAssignmentExtractor;
12
13#[CoversClass(MySqlUpsertAssignmentExtractor::class)]
14#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
15#[CoversClass(\ZtdQuery\Platform\MySql\Sql\Upsert\AssignmentReader::class)]
16final class MySqlUpsertAssignmentExtractorTest extends TestCase
17{
18    /**
19     * @param array<string, string> $expected
20     */
21    #[DataProvider('providerAssignments')]
22    public function testExtract(string $sql, array $expected): void
23    {
24        self::assertSame($expected, (new MySqlUpsertAssignmentExtractor())->extract($sql));
25    }
26
27    /**
28     * @return iterable<string, array{string, array<string, string>}>
29     */
30    public static function providerAssignments(): iterable
31    {
32        yield 'row alias' => [
33            "INSERT INTO t VALUES (1, 'updated', 99) AS new ON DUPLICATE KEY UPDATE name = new.name, score = new.score",
34            ['name' => 'new.name', 'score' => 'new.score'],
35        ];
36        yield 'quoted row alias and columns' => [
37            "INSERT INTO t VALUES (1, 'updated') AS `incoming` ON DUPLICATE KEY UPDATE `t`.`name` = `incoming`.`name`",
38            ['name' => '`incoming`.`name`'],
39        ];
40        yield 'case-insensitive row alias' => [
41            "INSERT INTO t VALUES (1, 'updated') AS Incoming ON DUPLICATE KEY UPDATE name = incoming.name",
42            ['name' => 'incoming.name'],
43        ];
44        yield 'values function and nested commas' => [
45            'INSERT INTO t VALUES (1, 2) ON DUPLICATE KEY UPDATE value = IF(VALUES(value) > value, VALUES(value), value)',
46            ['value' => 'IF(VALUES(value) > value, VALUES(value), value)'],
47        ];
48        yield 'multiple alias references after other identifiers' => [
49            'INSERT INTO t VALUES (1, 2) AS incoming ON DUPLICATE KEY UPDATE value = value + incoming.value + incoming.value',
50            ['value' => 'value + incoming.value + incoming.value'],
51        ];
52        yield 'alias word without qualification' => [
53            'INSERT INTO t VALUES (1, 2) AS incoming ON DUPLICATE KEY UPDATE value = incoming + incoming.value',
54            ['value' => 'incoming + incoming.value'],
55        ];
56        yield 'select alias is not a row alias' => [
57            'INSERT INTO t SELECT other.value AS projected FROM other ON DUPLICATE KEY UPDATE value = projected.value',
58            ['value' => 'projected.value'],
59        ];
60        yield 'quoted keywords' => [
61            "INSERT INTO t VALUES (1, 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE value = 'RETURNING'",
62            ['value' => "'RETURNING'"],
63        ];
64        yield 'not upsert' => ['INSERT INTO t VALUES (1)', []];
65        yield 'missing on keyword' => [
66            'INSERT INTO t VALUES (1) DUPLICATE KEY UPDATE value = 2',
67            [],
68        ];
69        yield 'malformed assignments' => [
70            'INSERT INTO t VALUES (1) ON DUPLICATE KEY UPDATE missing, value =',
71            [],
72        ];
73    }
74
75    public function testIncomingAliasExtractsIncomingRowAliasWithoutRewritingExpressions(): void
76    {
77        $extractor = new MySqlUpsertAssignmentExtractor();
78
79        self::assertSame(
80            'incoming',
81            $extractor->incomingAlias(
82                'INSERT INTO t VALUES (1) AS `incoming` ON DUPLICATE KEY UPDATE value = incoming.value',
83            ),
84        );
85        self::assertSame(
86            'incoming',
87            $extractor->incomingAlias(
88                'WITH payload AS (SELECT 1) INSERT INTO t VALUES (1) AS incoming '
89                . 'ON DUPLICATE KEY UPDATE value = incoming.value',
90            ),
91        );
92        self::assertNull($extractor->incomingAlias('INSERT INTO t VALUES (1) ON DUPLICATE KEY UPDATE value = 1'));
93    }
94}
95