packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/InsertOrReplaceTest.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 * @phpstan-type Row array<string, mixed>
17 */
18#[CoversNothing]
19#[Large]
20final class InsertOrReplaceTest extends TestCase
21{
22    public function testPreparedInsertOrReplaceRemovesExistingPrimaryKey(): void
23    {
24        $rawPdo = new PDO('sqlite::memory:', null, null, [
25            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
26            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
27        ]);
28        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, value INTEGER)');
29        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
30        $ztdPdo->exec("INSERT INTO users VALUES (1, 'original', 100)");
31
32        $statement = $ztdPdo->prepare('INSERT OR REPLACE INTO users VALUES (?, ?, ?)');
33        self::assertNotFalse($statement);
34        self::assertTrue($statement->execute(['1', 'replaced', 999]));
35
36        $rows = $ztdPdo->query('SELECT * FROM users WHERE id = 1');
37        self::assertNotFalse($rows);
38        self::assertSame([['id' => 1, 'name' => 'replaced', 'value' => 999]], $rows->fetchAll());
39    }
40
41    public function testInsertOrReplace(): void
42    {
43        $rawPdo = new PDO('sqlite::memory:', null, null, [
44            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
45            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
46        ]);
47        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
48
49        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
50
51        $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
52        $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
53
54        $rawPdo->exec("INSERT OR REPLACE INTO users (id, name, age) VALUES (1, 'Alice Updated', 31)");
55        $ztdPdo->exec("INSERT OR REPLACE INTO users (id, name, age) VALUES (1, 'Alice Updated', 31)");
56
57        $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
58        self::assertNotFalse($stmt);
59        /** @var list<Row> $rawRows */
60        $rawRows = $stmt->fetchAll();
61        $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
62        self::assertNotFalse($stmt);
63        /** @var list<Row> $ztdRows */
64        $ztdRows = $stmt->fetchAll();
65
66        self::assertSame($rawRows, $ztdRows);
67    }
68
69    public function testReplaceInto(): void
70    {
71        $rawPdo = new PDO('sqlite::memory:', null, null, [
72            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
73            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
74        ]);
75        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
76
77        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
78
79        $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
80        $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
81
82        $rawPdo->exec("REPLACE INTO users (id, name, age) VALUES (1, 'Bob', 25)");
83        $ztdPdo->exec("REPLACE INTO users (id, name, age) VALUES (1, 'Bob', 25)");
84
85        $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
86        self::assertNotFalse($stmt);
87        /** @var list<Row> $rawRows */
88        $rawRows = $stmt->fetchAll();
89        $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
90        self::assertNotFalse($stmt);
91        /** @var list<Row> $ztdRows */
92        $ztdRows = $stmt->fetchAll();
93
94        self::assertSame($rawRows, $ztdRows);
95    }
96
97    public function testReplaceRemovesRowsConflictingWithPrimaryAndUniqueKeys(): void
98    {
99        $rawPdo = new PDO('sqlite::memory:', null, null, [
100            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
101            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
102        ]);
103        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, name TEXT)');
104        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
105
106        $firstInsert = "INSERT INTO users VALUES (1, 'alice@example.com', 'Alice')";
107        $secondInsert = "INSERT INTO users VALUES (2, 'bob@example.com', 'Bob')";
108        $replace = "REPLACE INTO users VALUES (1, 'bob@example.com', 'Replacement')";
109        $rawPdo->exec($firstInsert);
110        $ztdPdo->exec($firstInsert);
111        $rawPdo->exec($secondInsert);
112        $ztdPdo->exec($secondInsert);
113        $rawPdo->exec($replace);
114        $ztdPdo->exec($replace);
115
116        $rawStatement = $rawPdo->query('SELECT * FROM users ORDER BY id');
117        $ztdStatement = $ztdPdo->query('SELECT * FROM users ORDER BY id');
118        self::assertNotFalse($rawStatement);
119        self::assertNotFalse($ztdStatement);
120        self::assertSame($rawStatement->fetchAll(), $ztdStatement->fetchAll());
121    }
122}
123