packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/UpsertExpressionTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use PDO;
8use PHPUnit\Framework\Attributes\CoversNothing;
9use PHPUnit\Framework\Attributes\Large;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Adapter\Pdo\ZtdPdo;
12
13/**
14 * @requires extension pdo_sqlite
15 */
16#[CoversNothing]
17#[Large]
18final class UpsertExpressionTest extends TestCase
19{
20    public function testSelfReferencingUpsertMatchesNativeSqlite(): void
21    {
22        $rawPdo = new PDO('sqlite::memory:', null, null, [
23            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
24            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
25        ]);
26        $rawPdo->exec('CREATE TABLE native_items (id INTEGER PRIMARY KEY, quantity INTEGER NOT NULL)');
27        $rawPdo->exec('CREATE TABLE shadow_items (id INTEGER PRIMARY KEY, quantity INTEGER NOT NULL)');
28        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
29
30        $rawPdo->exec('INSERT INTO native_items VALUES (1, 100)');
31        $ztdPdo->exec('INSERT INTO shadow_items VALUES (1, 100)');
32        $rawPdo->exec('INSERT INTO native_items VALUES (1, 5), (1, 7) ON CONFLICT(id) DO UPDATE SET quantity = native_items.quantity + excluded.quantity');
33        $ztdPdo->exec('INSERT INTO shadow_items VALUES (1, 5), (1, 7) ON CONFLICT(id) DO UPDATE SET quantity = shadow_items.quantity + excluded.quantity');
34
35        $rawStatement = $rawPdo->query('SELECT id, quantity FROM native_items');
36        $ztdStatement = $ztdPdo->query('SELECT id, quantity FROM shadow_items');
37        $physicalShadowStatement = $rawPdo->query('SELECT * FROM shadow_items');
38        self::assertNotFalse($rawStatement);
39        self::assertNotFalse($ztdStatement);
40        self::assertNotFalse($physicalShadowStatement);
41        self::assertSame($rawStatement->fetchAll(), $ztdStatement->fetchAll());
42        self::assertSame([], $physicalShadowStatement->fetchAll());
43    }
44
45    public function testConditionalUpsertAndReturningMatchNativeSqlite(): void
46    {
47        $rawPdo = new PDO('sqlite::memory:', null, null, [
48            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
49            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
50        ]);
51        $rawPdo->exec('CREATE TABLE native_items (id INTEGER PRIMARY KEY, name TEXT, score INTEGER)');
52        $rawPdo->exec('CREATE TABLE shadow_items (id INTEGER PRIMARY KEY, name TEXT, score INTEGER)');
53        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
54        $rawPdo->exec("INSERT INTO native_items VALUES (1, 'original', 50)");
55        $ztdPdo->exec("INSERT INTO shadow_items VALUES (1, 'original', 50)");
56
57        $rawSkipped = $rawPdo->query("INSERT INTO native_items VALUES (1, 'skipped', 95) ON CONFLICT(id) DO UPDATE SET name = excluded.name WHERE native_items.score >= 80 RETURNING id, name, score");
58        $ztdSkipped = $ztdPdo->query("INSERT INTO shadow_items VALUES (1, 'skipped', 95) ON CONFLICT(id) DO UPDATE SET name = excluded.name WHERE shadow_items.score >= 80 RETURNING id, name, score");
59        self::assertNotFalse($rawSkipped);
60        self::assertNotFalse($ztdSkipped);
61        self::assertSame($rawSkipped->fetchAll(), $ztdSkipped->fetchAll());
62
63        $rawPdo->exec('UPDATE native_items SET score = 85 WHERE id = 1');
64        $ztdPdo->exec('UPDATE shadow_items SET score = 85 WHERE id = 1');
65        $rawUpdated = $rawPdo->query("INSERT INTO native_items VALUES (1, 'updated', 95) ON CONFLICT(id) DO UPDATE SET name = excluded.name WHERE native_items.score >= 80 RETURNING id, name, score");
66        $ztdUpdated = $ztdPdo->query("INSERT INTO shadow_items VALUES (1, 'updated', 95) ON CONFLICT(id) DO UPDATE SET name = excluded.name WHERE shadow_items.score >= 80 RETURNING id, name, score");
67        self::assertNotFalse($rawUpdated);
68        self::assertNotFalse($ztdUpdated);
69        self::assertSame($rawUpdated->fetchAll(), $ztdUpdated->fetchAll());
70    }
71}
72