packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/SelectFromTest.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 SelectFromTest extends TestCase
25{
26 public function testSelectAllColumns(): 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 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
45 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
46
47 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
48
49 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
50
51 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY id");
52 self::assertNotFalse($stmt);
53 /** @var list<Row> */
54 $rawRows = $stmt->fetchAll();
55
56 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY id");
57 self::assertNotFalse($stmt);
58 /** @var list<Row> */
59 $ztdRows = $stmt->fetchAll();
60
61 self::assertSame($rawRows, $ztdRows);
62 } finally {
63 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
64 }
65 }
66
67 public function testSelectSpecificColumns(): void
68 {
69 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
70 /** @var PDO $rawPdo */
71 $rawPdo = new PDO(
72 $endpoint->dsn(),
73 $endpoint->username,
74 $endpoint->password,
75 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
76 );
77
78 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
79 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
80 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
81
82 $table = 'prefix_' . bin2hex(random_bytes(8));
83
84 try {
85 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
86 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
87
88 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
89
90 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
91
92 $stmt = $rawPdo->query("SELECT name, age FROM {$table} ORDER BY id");
93 self::assertNotFalse($stmt);
94 /** @var list<Row> */
95 $rawRows = $stmt->fetchAll();
96
97 $stmt = $ztdPdo->query("SELECT name, age FROM {$table} ORDER BY id");
98 self::assertNotFalse($stmt);
99 /** @var list<Row> */
100 $ztdRows = $stmt->fetchAll();
101
102 self::assertSame($rawRows, $ztdRows);
103 } finally {
104 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
105 }
106 }
107
108 public function testSelectWithAlias(): void
109 {
110 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
111 /** @var PDO $rawPdo */
112 $rawPdo = new PDO(
113 $endpoint->dsn(),
114 $endpoint->username,
115 $endpoint->password,
116 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
117 );
118
119 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
120 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
121 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
122
123 $table = 'prefix_' . bin2hex(random_bytes(8));
124
125 try {
126 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
127 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
128
129 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
130
131 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
132
133 $stmt = $rawPdo->query("SELECT name AS user_name, age AS user_age FROM {$table} ORDER BY id");
134 self::assertNotFalse($stmt);
135 /** @var list<Row> */
136 $rawRows = $stmt->fetchAll();
137
138 $stmt = $ztdPdo->query("SELECT name AS user_name, age AS user_age FROM {$table} ORDER BY id");
139 self::assertNotFalse($stmt);
140 /** @var list<Row> */
141 $ztdRows = $stmt->fetchAll();
142
143 self::assertSame($rawRows, $ztdRows);
144 } finally {
145 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
146 }
147 }
148
149 public function testSelectWithTableAlias(): void
150 {
151 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
152 /** @var PDO $rawPdo */
153 $rawPdo = new PDO(
154 $endpoint->dsn(),
155 $endpoint->username,
156 $endpoint->password,
157 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
158 );
159
160 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
161 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
162 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
163
164 $table = 'prefix_' . bin2hex(random_bytes(8));
165
166 try {
167 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
168 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
169
170 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
171
172 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
173
174 $stmt = $rawPdo->query("SELECT u.name, u.age FROM {$table} u ORDER BY u.id");
175 self::assertNotFalse($stmt);
176 /** @var list<Row> */
177 $rawRows = $stmt->fetchAll();
178
179 $stmt = $ztdPdo->query("SELECT u.name, u.age FROM {$table} u ORDER BY u.id");
180 self::assertNotFalse($stmt);
181 /** @var list<Row> */
182 $ztdRows = $stmt->fetchAll();
183
184 self::assertSame($rawRows, $ztdRows);
185 } finally {
186 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
187 }
188 }
189
190 public function testFromKeywordInsideExtractIsNotTreatedAsTableClause(): void
191 {
192 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
193 /** @var PDO $rawPdo */
194 $rawPdo = new PDO(
195 $endpoint->dsn(),
196 $endpoint->username,
197 $endpoint->password,
198 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
199 );
200
201 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
202 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
203 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
204
205 $table = 'prefix_' . bin2hex(random_bytes(8));
206
207 try {
208 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, event_date DATE NOT NULL)");
209 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
210 $ztdPdo->exec("INSERT INTO {$table} (id, event_date) VALUES (1, '2024-01-10')");
211
212 $statement = $ztdPdo->query("SELECT EXTRACT(YEAR FROM event_date) AS year FROM {$table} WHERE id = 1");
213
214 self::assertNotFalse($statement);
215 self::assertSame('2024', $statement->fetchColumn());
216 } finally {
217 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
218 }
219 }
220}
221