packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/CreateTableTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\PostgreSql16Container;
8use PDO;
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_pgsql
16 * @group integration
17 * @group postgres
18 *
19 * @phpstan-type Row array<string, mixed>
20 */
21#[CoversNothing]
22#[Large]
23final class CreateTableTest extends TestCase
24{
25 public function testCreateTableAndInsert(): void
26 {
27 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
28 /** @var PDO $rawPdo */
29 $rawPdo = new PDO(
30 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
31 'test',
32 'test',
33 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
34 );
35
36 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
37 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
38 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
39
40 $table = 'prefix_' . bin2hex(random_bytes(8));
41
42 try {
43 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
44
45 $ztdPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
46
47 $ztdPdo->exec("INSERT INTO {$table} (id, name) VALUES (1, 'Alice')");
48
49 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
50 self::assertNotFalse($stmt);
51 /** @var list<Row> */
52 $ztdRows = $stmt->fetchAll();
53
54 self::assertCount(1, $ztdRows);
55 self::assertSame(1, $ztdRows[0]['id']);
56 self::assertSame('Alice', $ztdRows[0]['name']);
57 } finally {
58 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
59 }
60 }
61
62 public function testCreateTableIfNotExists(): void
63 {
64 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
65 /** @var PDO $rawPdo */
66 $rawPdo = new PDO(
67 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
68 'test',
69 'test',
70 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
71 );
72
73 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
74 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
75 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
76
77 $table = 'prefix_' . bin2hex(random_bytes(8));
78
79 try {
80 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
81
82 $ztdPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
83
84 $ztdPdo->exec("CREATE TABLE IF NOT EXISTS {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
85
86 $ztdPdo->exec("INSERT INTO {$table} (id, name) VALUES (1, 'Test')");
87
88 $stmt = $ztdPdo->query("SELECT * FROM {$table}");
89 self::assertNotFalse($stmt);
90 /** @var list<Row> */
91 $ztdRows = $stmt->fetchAll();
92
93 self::assertCount(1, $ztdRows);
94 } finally {
95 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
96 }
97 }
98
99 public function testCreateTableDoesNotModifyPhysicalDatabase(): void
100 {
101 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
102 /** @var PDO $rawPdo */
103 $rawPdo = new PDO(
104 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
105 'test',
106 'test',
107 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
108 );
109
110 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
111 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
112 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
113
114 $table = 'prefix_' . bin2hex(random_bytes(8));
115
116 try {
117 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
118
119 $ztdPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
120
121 $stmt = $rawPdo->prepare(
122 'SELECT table_name FROM information_schema.tables WHERE table_name = ? AND table_schema = current_schema()'
123 );
124 $stmt->execute([$table]);
125 $rows = $stmt->fetchAll();
126 self::assertCount(0, $rows);
127 } finally {
128 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
129 }
130 }
131
132 public function testCreateTableAsSelectPreservesColumnsForEmptyResult(): void
133 {
134 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
135 /** @var PDO $rawPdo */
136 $rawPdo = new PDO(
137 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
138 'test',
139 'test',
140 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
141 );
142
143 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
144 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
145 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
146
147 $source = 'source_' . bin2hex(random_bytes(8));
148 $copy = 'copy_' . bin2hex(random_bytes(8));
149
150 try {
151 $rawPdo->exec("CREATE TABLE {$source} (id INTEGER, name TEXT)");
152 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
153 $ztdPdo->exec("INSERT INTO {$source} VALUES (1, 'Alice')");
154
155 self::assertSame(0, $ztdPdo->exec("CREATE TABLE {$copy} AS SELECT * FROM {$source} WHERE FALSE"));
156 self::assertSame(1, $ztdPdo->exec("INSERT INTO {$copy} VALUES (2, 'Bob')"));
157
158 $statement = $ztdPdo->query("SELECT * FROM {$copy} WHERE id = 2");
159 self::assertNotFalse($statement);
160 self::assertSame([['id' => 2, 'name' => 'Bob']], $statement->fetchAll());
161 } finally {
162 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
163 }
164 }
165
166 public function testCreateTableAsSelectPreservesProjectedPostgreSqlTypes(): void
167 {
168 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
169 /** @var PDO $rawPdo */
170 $rawPdo = new PDO(
171 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
172 'test',
173 'test',
174 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
175 );
176
177 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
178 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
179 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
180
181 $source = 'source_' . bin2hex(random_bytes(8));
182 $copy = 'copy_' . bin2hex(random_bytes(8));
183
184 try {
185 $rawPdo->exec("CREATE TABLE {$source} (id INTEGER, name VARCHAR(40), score NUMERIC(8, 2))");
186 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
187 $ztdPdo->exec("INSERT INTO {$source} VALUES (1, 'Alice', 95.25)");
188
189 $ztdPdo->exec(
190 "CREATE TABLE {$copy} AS SELECT id + 1 AS next_id, name, score FROM {$source}"
191 );
192
193 $statement = $ztdPdo->query("SELECT next_id, name, score FROM {$copy} WHERE next_id = 2");
194 self::assertNotFalse($statement);
195 self::assertSame(
196 [['next_id' => 2, 'name' => 'Alice', 'score' => '95.25']],
197 $statement->fetchAll(),
198 );
199
200 $typeStatement = $ztdPdo->query(
201 'SELECT pg_typeof(next_id)::text AS id_type, pg_typeof(name)::text AS name_type, '
202 . "pg_typeof(score)::text AS score_type FROM {$copy} LIMIT 1"
203 );
204 self::assertNotFalse($typeStatement);
205 self::assertSame(
206 [['id_type' => 'integer', 'name_type' => 'character varying', 'score_type' => 'numeric']],
207 $typeStatement->fetchAll(),
208 );
209 } finally {
210 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
211 }
212 }
213}
214