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