packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/SelectOrderByTest.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 SelectOrderByTest extends TestCase
25{
26 public function testOrderByAsc(): 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 age ASC");
52 self::assertNotFalse($stmt);
53 /** @var list<Row> */
54 $rawRows = $stmt->fetchAll();
55
56 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY age ASC");
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 testOrderByDesc(): 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 * FROM {$table} ORDER BY age DESC");
93 self::assertNotFalse($stmt);
94 /** @var list<Row> */
95 $rawRows = $stmt->fetchAll();
96
97 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY age DESC");
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 testOrderByNullsFirst(): 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, val INTEGER)");
127 $rawPdo->exec("INSERT INTO {$table} (id, val) VALUES (1, 1), (2, NULL), (3, 3), (4, NULL), (5, 2)");
128
129 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
130
131 $ztdPdo->exec("INSERT INTO {$table} (id, val) VALUES (1, 1), (2, NULL), (3, 3), (4, NULL), (5, 2)");
132
133 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY val ASC NULLS FIRST");
134 self::assertNotFalse($stmt);
135 /** @var list<Row> */
136 $rawRows = $stmt->fetchAll();
137
138 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY val ASC NULLS FIRST");
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 testOrderByNullsLast(): 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, val INTEGER)");
168 $rawPdo->exec("INSERT INTO {$table} (id, val) VALUES (1, 1), (2, NULL), (3, 3), (4, NULL), (5, 2)");
169
170 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
171
172 $ztdPdo->exec("INSERT INTO {$table} (id, val) VALUES (1, 1), (2, NULL), (3, 3), (4, NULL), (5, 2)");
173
174 $stmt = $rawPdo->query("SELECT * FROM {$table} ORDER BY val ASC NULLS LAST");
175 self::assertNotFalse($stmt);
176 /** @var list<Row> */
177 $rawRows = $stmt->fetchAll();
178
179 $stmt = $ztdPdo->query("SELECT * FROM {$table} ORDER BY val ASC NULLS LAST");
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