packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/DeleteBasicTest.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 DeleteBasicTest extends TestCase
21{
22 public function testDeleteSingleRow(): 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 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
30
31 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
32 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
33
34 $rawPdo->exec('DELETE FROM users WHERE id = 1');
35 $ztdPdo->exec('DELETE FROM users WHERE id = 1');
36
37 $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
38 self::assertNotFalse($stmt);
39 /** @var list<Row> $rawRows */
40 $rawRows = $stmt->fetchAll();
41 $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
42 self::assertNotFalse($stmt);
43 /** @var list<Row> $ztdRows */
44 $ztdRows = $stmt->fetchAll();
45
46 self::assertSame($rawRows, $ztdRows);
47 }
48
49 public function testDeleteAllRowsWithCondition(): void
50 {
51 $rawPdo = new PDO('sqlite::memory:', null, null, [
52 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
53 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
54 ]);
55 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
56 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
57
58 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
59 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
60
61 $rawPdo->exec('DELETE FROM users WHERE 1=1');
62 $ztdPdo->exec('DELETE FROM users WHERE 1=1');
63
64 $stmt = $rawPdo->query('SELECT * FROM users');
65 self::assertNotFalse($stmt);
66 /** @var list<Row> $rawRows */
67 $rawRows = $stmt->fetchAll();
68 $stmt = $ztdPdo->query('SELECT * FROM users');
69 self::assertNotFalse($stmt);
70 /** @var list<Row> $ztdRows */
71 $ztdRows = $stmt->fetchAll();
72
73 self::assertSame($rawRows, $ztdRows);
74 }
75
76 public function testDeleteDoesNotModifyPhysicalDatabase(): void
77 {
78 $rawPdo = new PDO('sqlite::memory:', null, null, [
79 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
80 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
81 ]);
82 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
83 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
84
85 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
86 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
87
88 $ztdPdo->exec('DELETE FROM users WHERE id = 1');
89
90 $stmt = $rawPdo->query('SELECT * FROM users');
91 self::assertNotFalse($stmt);
92 /** @var list<Row> $rawRows */
93 $rawRows = $stmt->fetchAll();
94 self::assertCount(3, $rawRows);
95 }
96
97 public function testDeleteWithCondition(): 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, name TEXT NOT NULL, age INTEGER NOT NULL)');
104 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
105
106 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
107 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
108
109 $rawPdo->exec('DELETE FROM users WHERE age > 28');
110 $ztdPdo->exec('DELETE FROM users WHERE age > 28');
111
112 $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
113 self::assertNotFalse($stmt);
114 /** @var list<Row> $rawRows */
115 $rawRows = $stmt->fetchAll();
116 $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
117 self::assertNotFalse($stmt);
118 /** @var list<Row> $ztdRows */
119 $ztdRows = $stmt->fetchAll();
120
121 self::assertSame($rawRows, $ztdRows);
122 }
123
124 public function testDeleteWithGroupedInSubquery(): void
125 {
126 $rawPdo = new PDO('sqlite::memory:', null, null, [
127 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
128 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
129 ]);
130 $rawPdo->exec('CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT)');
131 $rawPdo->exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL, status TEXT)');
132 $rawPdo->exec("INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob')");
133 $rawPdo->exec("INSERT INTO orders VALUES (1, 1, 50, 'completed'), (2, 1, 75, 'completed'), (3, 2, 10, 'completed')");
134 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
135 $ztdPdo->exec("INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob')");
136 $ztdPdo->exec("INSERT INTO orders VALUES (1, 1, 50, 'completed'), (2, 1, 75, 'completed'), (3, 2, 10, 'completed')");
137
138 $sql = "DELETE FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'completed' GROUP BY customer_id HAVING SUM(amount) < 50)";
139 self::assertSame($rawPdo->exec($sql), $ztdPdo->exec($sql));
140
141 $raw = $rawPdo->query('SELECT * FROM customers ORDER BY id');
142 $shadow = $ztdPdo->query('SELECT * FROM customers ORDER BY id');
143 self::assertNotFalse($raw);
144 self::assertNotFalse($shadow);
145 self::assertSame($raw->fetchAll(), $shadow->fetchAll());
146 }
147}
148