packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/CreateTableTest.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 CreateTableTest extends TestCase
21{
22    public function testCreateTableAndInsert(): 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
29        $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
30
31        $ztdPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
32        $ztdPdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice')");
33
34        $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
35        self::assertNotFalse($stmt);
36        /** @var list<Row> $ztdRows */
37        $ztdRows = $stmt->fetchAll();
38        self::assertCount(1, $ztdRows);
39        self::assertEquals(1, $ztdRows[0]['id']);
40        self::assertSame('Alice', $ztdRows[0]['name']);
41    }
42
43    public function testCreateTableIfNotExists(): 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
50        $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
51
52        $ztdPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
53        $ztdPdo->exec('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
54        $ztdPdo->exec("INSERT INTO users (id, name) VALUES (1, 'Test')");
55
56        $stmt = $ztdPdo->query('SELECT * FROM users');
57        self::assertNotFalse($stmt);
58        /** @var list<Row> $ztdRows */
59        $ztdRows = $stmt->fetchAll();
60        self::assertCount(1, $ztdRows);
61    }
62
63    public function testCreateTableDoesNotModifyPhysicalDatabase(): void
64    {
65        $rawPdo = new PDO('sqlite::memory:', null, null, [
66            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
67            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
68        ]);
69
70        $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
71
72        $ztdPdo->exec('CREATE TABLE virtual_table (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
73
74        $stmt = $rawPdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='virtual_table'");
75        self::assertNotFalse($stmt);
76        $rows = $stmt->fetchAll();
77        self::assertCount(0, $rows);
78    }
79}
80