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