packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/AlterTableTest.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#[CoversNothing]
14#[Large]
15final class AlterTableTest extends TestCase
16{
17    public function testAddColumnMigratesExistingRowsAndDefaultsFutureRows(): void
18    {
19        $rawPdo = new PDO('sqlite::memory:', null, null, [
20            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
21            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
22        ]);
23        $rawPdo->exec('CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
24        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
25
26        self::assertSame(1, $ztdPdo->exec("INSERT INTO people VALUES (1, 'Alice')"));
27        self::assertSame(0, $ztdPdo->exec('ALTER TABLE people ADD COLUMN age INTEGER NOT NULL DEFAULT 7'));
28        self::assertSame(1, $ztdPdo->exec("INSERT INTO people (id, name) VALUES (2, 'Bob')"));
29        self::assertSame(1, $ztdPdo->exec('UPDATE people SET age = 30 WHERE id = 1'));
30
31        $statement = $ztdPdo->query('SELECT id, name, age FROM people WHERE age >= 7 ORDER BY age, id');
32        self::assertNotFalse($statement);
33        self::assertSame([
34            ['id' => 2, 'name' => 'Bob', 'age' => 7],
35            ['id' => 1, 'name' => 'Alice', 'age' => 30],
36        ], $statement->fetchAll());
37
38        $physicalColumns = $rawPdo->query('PRAGMA table_info(people)');
39        self::assertNotFalse($physicalColumns);
40        self::assertSame(['id', 'name'], array_column($physicalColumns->fetchAll(), 'name'));
41    }
42
43    public function testRenameTableMovesSchemaAndRowsWithoutTouchingPhysicalTable(): void
44    {
45        $rawPdo = new PDO('sqlite::memory:', null, null, [
46            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
47            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
48        ]);
49        $rawPdo->exec('CREATE TABLE source_table (id INTEGER PRIMARY KEY, value TEXT)');
50        $rawPdo->exec("INSERT INTO source_table VALUES (99, 'physical')");
51        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
52
53        $ztdPdo->exec("INSERT INTO source_table VALUES (1, 'shadow')");
54        $ztdPdo->exec('ALTER TABLE source_table RENAME TO target_table');
55
56        $statement = $ztdPdo->query('SELECT * FROM target_table');
57        self::assertNotFalse($statement);
58        self::assertSame([['id' => 1, 'value' => 'shadow']], $statement->fetchAll());
59        $physicalSource = $rawPdo->query("SELECT name FROM sqlite_master WHERE name = 'source_table'");
60        self::assertNotFalse($physicalSource);
61        self::assertSame('source_table', $physicalSource->fetchColumn());
62        $physicalTarget = $rawPdo->query("SELECT name FROM sqlite_master WHERE name = 'target_table'");
63        self::assertNotFalse($physicalTarget);
64        self::assertFalse($physicalTarget->fetchColumn());
65
66        $oldTable = $ztdPdo->query('SELECT * FROM source_table');
67        self::assertNotFalse($oldTable);
68        self::assertSame([], $oldTable->fetchAll());
69    }
70
71    public function testRenameAndDropColumnMigrateStoredRows(): void
72    {
73        $rawPdo = new PDO('sqlite::memory:', null, null, [
74            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
75            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
76        ]);
77        $rawPdo->exec('CREATE TABLE records (id INTEGER PRIMARY KEY, label TEXT, obsolete TEXT)');
78        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
79
80        $ztdPdo->exec("INSERT INTO records VALUES (1, 'kept', 'removed')");
81        $ztdPdo->exec('ALTER TABLE records RENAME COLUMN label TO title');
82        $ztdPdo->exec('ALTER TABLE records DROP COLUMN obsolete');
83
84        $statement = $ztdPdo->query('SELECT id, title FROM records');
85        self::assertNotFalse($statement);
86        self::assertSame([['id' => 1, 'title' => 'kept']], $statement->fetchAll());
87    }
88}
89