packages/ztd-query-sqlite/tests/Unit/Rewrite/Upsert/SqliteNativeUpsertProjectorTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\Upsert;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Platform\Sqlite\Rewrite\Upsert\SqliteNativeUpsertProjector;
11
12#[CoversClass(SqliteNativeUpsertProjector::class)]
13#[UsesClass(\ZtdQuery\Platform\Sqlite\Rewrite\SqlEdits::class)]
14#[UsesClass(\ZtdQuery\Platform\Sqlite\Rewrite\Upsert\UpsertConflictPredicate::class)]
15#[UsesClass(\ZtdQuery\Platform\Sqlite\Rewrite\Upsert\UpsertExpressionBinder::class)]
16#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteIdentifierQuoter::class)]
17#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexerProfile::class)]
18final class SqliteNativeUpsertProjectorTest extends TestCase
19{
20    public function testProjectKeepsIdentifiersInsideScalarSubqueryInTheirNativeScope(): void
21    {
22        $projector = new SqliteNativeUpsertProjector();
23
24        $result = $projector->project(
25            'SELECT 1 AS "id", 0 AS "price"',
26            'prices',
27            ['id', 'price'],
28            ['PRIMARY' => ['id']],
29            ['price' => 'price + (SELECT price FROM price_list WHERE id = prices.id) + COALESCE(EXCLUDED.price, 0)'],
30        );
31
32        self::assertSame(
33            'SELECT "__ztd_incoming"."id" AS "id", "__ztd_incoming"."price" AS "price", '
34            . '(SELECT "__ztd_existing"."price" + (SELECT price FROM price_list WHERE id = prices.id) '
35            . '+ COALESCE("__ztd_incoming"."price", 0) FROM "prices" AS "__ztd_existing" '
36            . 'WHERE (("__ztd_existing"."id" = "__ztd_incoming"."id")) LIMIT 1) AS "__ztd_upsert_value_0" '
37            . 'FROM (SELECT 1 AS "id", 0 AS "price") AS "__ztd_incoming"',
38            $result,
39        );
40    }
41
42    public function testBindsQualifiedAndQuotedIdentifiersWithoutChangingForeignNamespaces(): void
43    {
44        $projector = new SqliteNativeUpsertProjector();
45
46        $result = $projector->project(
47            'SELECT 1 AS "id", 2 AS "odd""name", 3 AS "price"',
48            'prices',
49            ['id', 'odd"name', 'price'],
50            ['PRIMARY' => ['id']],
51            ['price' => 'EXCLUDED."odd""name" + prices.price + foreign.price + price(price) + CONSTANT'],
52        );
53
54        self::assertSame(
55            'SELECT "__ztd_incoming"."id" AS "id", "__ztd_incoming"."odd""name" AS "odd""name", '
56            . '"__ztd_incoming"."price" AS "price", '
57            . '(SELECT "__ztd_incoming"."odd""name" + "__ztd_existing"."price" + foreign.price '
58            . '+ price("__ztd_existing"."price") + CONSTANT FROM "prices" AS "__ztd_existing" '
59            . 'WHERE (("__ztd_existing"."id" = "__ztd_incoming"."id")) LIMIT 1) AS "__ztd_upsert_value_0" '
60            . 'FROM (SELECT 1 AS "id", 2 AS "odd""name", 3 AS "price") AS "__ztd_incoming"',
61            $result,
62        );
63    }
64
65    public function testMatchesColumnsAndIncomingNamespaceCaseInsensitively(): void
66    {
67        $projector = new SqliteNativeUpsertProjector();
68
69        $result = $projector->project(
70            'SELECT 1 AS "ID", 2 AS "PRICE"',
71            'ITEMS',
72            ['ID', 'PRICE'],
73            ['PRIMARY' => ['ID']],
74            ['PRICE' => 'EXCLUDED.PRICE + PRICE + ITEMS.PRICE'],
75        );
76
77        self::assertSame(
78            'SELECT "__ztd_incoming"."ID" AS "ID", "__ztd_incoming"."PRICE" AS "PRICE", '
79            . '(SELECT "__ztd_incoming"."PRICE" + "__ztd_existing"."PRICE" + "__ztd_existing"."PRICE" '
80            . 'FROM "ITEMS" AS "__ztd_existing" WHERE (("__ztd_existing"."ID" = "__ztd_incoming"."ID")) '
81            . 'LIMIT 1) AS "__ztd_upsert_value_0" '
82            . 'FROM (SELECT 1 AS "ID", 2 AS "PRICE") AS "__ztd_incoming"',
83            $result,
84        );
85    }
86
87    public function testReturnsIncomingProjectionWhenConflictMetadataIsUnavailable(): void
88    {
89        $projector = new SqliteNativeUpsertProjector();
90
91        self::assertSame(
92            'SELECT 1 AS "id"',
93            $projector->project('SELECT 1 AS "id"', 'items', ['id'], [], ['id' => 'EXCLUDED.id']),
94        );
95        self::assertSame(
96            'SELECT 1 AS "id"',
97            $projector->project('SELECT 1 AS "id"', 'items', ['id'], ['PRIMARY' => ['id']], []),
98        );
99    }
100
101    public function testBindsUnqualifiedQuotedIdentifier(): void
102    {
103        $result = (new SqliteNativeUpsertProjector())->project(
104            'SELECT 1 AS "id", 2 AS "odd""name"',
105            'items',
106            ['id', 'odd"name'],
107            ['PRIMARY' => ['id']],
108            ['odd"name' => '"odd""name" + 1'],
109        );
110
111        self::assertStringContainsString(
112            '(SELECT "__ztd_existing"."odd""name" + 1 FROM "items" AS "__ztd_existing"',
113            $result,
114        );
115    }
116
117    public function testBindsConflictPredicateToExistingAndIncomingRows(): void
118    {
119        $result = (new SqliteNativeUpsertProjector())->project(
120            'SELECT \'alice@example.com\' AS "email", \'active\' AS "status", 1 AS "login_count"',
121            'users',
122            ['email', 'status', 'login_count'],
123            ['users_active_email' => ['email']],
124            ['login_count' => 'MAX(login_count, EXCLUDED.login_count)'],
125            conflictPredicate: "status = 'active'",
126        );
127
128        self::assertStringContainsString(
129            '"__ztd_existing"."email" = "__ztd_incoming"."email"',
130            $result,
131        );
132        self::assertStringContainsString(
133            '("__ztd_existing"."status" = \'active\') AND ("__ztd_incoming"."status" = \'active\')',
134            $result,
135        );
136    }
137}
138