packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/InsertBasicTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use PDO;
8use PDOStatement;
9use PHPUnit\Framework\Attributes\CoversNothing;
10use PHPUnit\Framework\Attributes\Large;
11use PHPUnit\Framework\TestCase;
12use ZtdQuery\Adapter\Pdo\ZtdPdo;
13
14/**
15 * @requires extension pdo_sqlite
16 *
17 * @phpstan-type Row array<string, mixed>
18 */
19#[CoversNothing]
20#[Large]
21final class InsertBasicTest extends TestCase
22{
23 public function testSingleRowInsert(): void
24 {
25 $rawPdo = new PDO('sqlite::memory:', null, null, [
26 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
27 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
28 ]);
29 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
30
31 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
32
33 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
34 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
35
36 $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
37 self::assertNotFalse($stmt);
38 /** @var list<Row> $rawRows */
39 $rawRows = $stmt->fetchAll();
40 $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
41 self::assertNotFalse($stmt);
42 /** @var list<Row> $ztdRows */
43 $ztdRows = $stmt->fetchAll();
44
45 self::assertSame($rawRows, $ztdRows);
46 }
47
48 public function testMultiRowInsert(): void
49 {
50 $rawPdo = new PDO('sqlite::memory:', null, null, [
51 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
52 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
53 ]);
54 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
55
56 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
57
58 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25)");
59 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25)");
60
61 $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
62 self::assertNotFalse($stmt);
63 /** @var list<Row> $rawRows */
64 $rawRows = $stmt->fetchAll();
65 $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
66 self::assertNotFalse($stmt);
67 /** @var list<Row> $ztdRows */
68 $ztdRows = $stmt->fetchAll();
69
70 self::assertSame($rawRows, $ztdRows);
71 }
72
73 public function testInsertDoesNotModifyPhysicalDatabase(): void
74 {
75 $rawPdo = new PDO('sqlite::memory:', null, null, [
76 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
77 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
78 ]);
79 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
80
81 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
82
83 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)");
84
85 $stmt = $rawPdo->query('SELECT * FROM users');
86 self::assertNotFalse($stmt);
87 /** @var list<Row> $rawRows */
88 $rawRows = $stmt->fetchAll();
89 self::assertCount(0, $rawRows);
90 }
91
92 public function testOmittedColumnsAndDefaultValuesMatchSqlite(): void
93 {
94 $rawPdo = new PDO('sqlite::memory:', null, null, [
95 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
96 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
97 ]);
98 $rawPdo->exec("CREATE TABLE settings (id INTEGER DEFAULT 7, status TEXT DEFAULT 'active', note TEXT)");
99 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
100
101 $rawPdo->exec('INSERT INTO settings (id) VALUES (1)');
102 $rawPdo->exec('INSERT INTO settings DEFAULT VALUES');
103 $ztdPdo->exec('INSERT INTO settings (id) VALUES (1)');
104 $ztdPdo->exec('INSERT INTO settings DEFAULT VALUES');
105
106 $raw = $rawPdo->query('SELECT * FROM settings ORDER BY id');
107 $ztd = $ztdPdo->query('SELECT * FROM settings ORDER BY id');
108 self::assertNotFalse($raw);
109 self::assertNotFalse($ztd);
110 self::assertSame($raw->fetchAll(), $ztd->fetchAll());
111 }
112
113 public function testOmittedIntegerPrimaryKeyUsesShadowIdentity(): void
114 {
115 $rawPdo = new PDO('sqlite::memory:', null, null, [
116 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
117 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
118 ]);
119 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)');
120 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
121
122 $ztdPdo->exec("INSERT INTO users (name) VALUES ('Alice'), ('Bob')");
123
124 $ztdRows = $ztdPdo->query('SELECT id, name FROM users ORDER BY id');
125 $rawRows = $rawPdo->query('SELECT * FROM users');
126 self::assertNotFalse($ztdRows);
127 self::assertNotFalse($rawRows);
128 self::assertSame([['id' => 1, 'name' => 'Alice'], ['id' => 2, 'name' => 'Bob']], $ztdRows->fetchAll());
129 self::assertSame([], $rawRows->fetchAll());
130 }
131
132 public function testInsertSelectPreservesExpressionsDistinctAndWindows(): void
133 {
134 $rawPdo = new PDO('sqlite::memory:', null, null, [
135 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
136 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
137 ]);
138 $rawPdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, dept TEXT)');
139 $rawPdo->exec('CREATE TABLE archive (id INTEGER PRIMARY KEY, name TEXT, doubled REAL, rank_in_dept INTEGER)');
140 $rawPdo->exec('CREATE TABLE departments (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)');
141 $rawPdo->exec('CREATE TABLE popular (dept TEXT, total REAL, item_count INTEGER)');
142 $rawPdo->exec('CREATE TABLE conditional_users (id INTEGER PRIMARY KEY, name TEXT)');
143 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
144
145 $ztdPdo->exec("INSERT INTO products VALUES (1, 'A', 10, 'x'), (2, 'B', 30, 'x'), (3, 'C', 20, 'y')");
146 $ztdPdo->exec('INSERT INTO archive SELECT id, name, price * 2, CAST(ROW_NUMBER() OVER (PARTITION BY dept ORDER BY price DESC) AS INTEGER) FROM products');
147 $ztdPdo->exec('INSERT INTO departments (name) SELECT DISTINCT dept FROM products ORDER BY dept');
148 $popularInsert = $ztdPdo->prepare('INSERT INTO popular SELECT dept, SUM(price), COUNT(*) FROM products GROUP BY dept HAVING SUM(price) > ?');
149 self::assertInstanceOf(PDOStatement::class, $popularInsert);
150 $popularInsert->bindValue(1, 15, PDO::PARAM_INT);
151 $popularInsert->execute();
152 $ztdPdo->exec("INSERT INTO conditional_users SELECT 1, 'alice' WHERE NOT EXISTS (SELECT 1 FROM conditional_users WHERE name = 'alice')");
153 $ztdPdo->exec("INSERT INTO conditional_users SELECT 1, 'alice' WHERE NOT EXISTS (SELECT 1 FROM conditional_users WHERE name = 'alice')");
154
155 $archive = $ztdPdo->query('SELECT * FROM archive ORDER BY id');
156 $departments = $ztdPdo->query('SELECT * FROM departments ORDER BY id');
157 $popular = $ztdPdo->query('SELECT * FROM popular ORDER BY dept');
158 $conditional = $ztdPdo->query('SELECT * FROM conditional_users');
159 self::assertNotFalse($archive);
160 self::assertNotFalse($departments);
161 self::assertNotFalse($popular);
162 self::assertNotFalse($conditional);
163 self::assertEquals([
164 ['id' => 1, 'name' => 'A', 'doubled' => 20, 'rank_in_dept' => 2],
165 ['id' => 2, 'name' => 'B', 'doubled' => 60, 'rank_in_dept' => 1],
166 ['id' => 3, 'name' => 'C', 'doubled' => 40, 'rank_in_dept' => 1],
167 ], $archive->fetchAll());
168 self::assertSame([['id' => 1, 'name' => 'x'], ['id' => 2, 'name' => 'y']], $departments->fetchAll());
169 self::assertEquals([['dept' => 'x', 'total' => 40, 'item_count' => 2], ['dept' => 'y', 'total' => 20, 'item_count' => 1]], $popular->fetchAll());
170 self::assertSame([['id' => 1, 'name' => 'alice']], $conditional->fetchAll());
171 }
172}
173