packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/InsertOrIgnoreTest.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 InsertOrIgnoreTest extends TestCase
21{
22    public function testInsertOrIgnoreDuplicateKey(): 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 NOT NULL, age INTEGER NOT NULL)');
29
30        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
31
32        $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
33        $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
34
35        $rawPdo->exec("INSERT OR IGNORE INTO users (id, name, age) VALUES (1, 'Alice Duplicate', 31)");
36        $ztdPdo->exec("INSERT OR IGNORE INTO users (id, name, age) VALUES (1, 'Alice Duplicate', 31)");
37
38        $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
39        self::assertNotFalse($stmt);
40        /** @var list<Row> $rawRows */
41        $rawRows = $stmt->fetchAll();
42        $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
43        self::assertNotFalse($stmt);
44        /** @var list<Row> $ztdRows */
45        $ztdRows = $stmt->fetchAll();
46
47        self::assertSame($rawRows, $ztdRows);
48    }
49
50    public function testInsertOrIgnoreNewRow(): void
51    {
52        $rawPdo = new PDO('sqlite::memory:', null, null, [
53            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
54            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
55        ]);
56        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
57
58        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
59
60        $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
61        $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
62
63        $rawPdo->exec("INSERT OR IGNORE INTO users (id, name, age) VALUES (2, 'Bob', 25)");
64        $ztdPdo->exec("INSERT OR IGNORE INTO users (id, name, age) VALUES (2, 'Bob', 25)");
65
66        $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
67        self::assertNotFalse($stmt);
68        /** @var list<Row> $rawRows */
69        $rawRows = $stmt->fetchAll();
70        $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
71        self::assertNotFalse($stmt);
72        /** @var list<Row> $ztdRows */
73        $ztdRows = $stmt->fetchAll();
74
75        self::assertSame($rawRows, $ztdRows);
76    }
77
78    public function testInsertOrIgnoreUsesUniqueCandidateKeyAndNullSemantics(): void
79    {
80        $rawPdo = new PDO('sqlite::memory:', null, null, [
81            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
82            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
83        ]);
84        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE)');
85        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
86
87        $insert = "INSERT INTO users (id, email) VALUES (1, 'alice@example.com')";
88        $uniqueConflict = "INSERT OR IGNORE INTO users (id, email) VALUES (2, 'alice@example.com')";
89        $firstNull = 'INSERT OR IGNORE INTO users (id, email) VALUES (3, NULL)';
90        $secondNull = 'INSERT OR IGNORE INTO users (id, email) VALUES (4, NULL)';
91        $rawPdo->exec($insert);
92        $ztdPdo->exec($insert);
93        $rawPdo->exec($uniqueConflict);
94        $ztdPdo->exec($uniqueConflict);
95        $rawPdo->exec($firstNull);
96        $ztdPdo->exec($firstNull);
97        $rawPdo->exec($secondNull);
98        $ztdPdo->exec($secondNull);
99
100        $rawStatement = $rawPdo->query('SELECT * FROM users ORDER BY id');
101        $ztdStatement = $ztdPdo->query('SELECT * FROM users ORDER BY id');
102        self::assertNotFalse($rawStatement);
103        self::assertNotFalse($ztdStatement);
104        self::assertSame($rawStatement->fetchAll(), $ztdStatement->fetchAll());
105    }
106}
107