{{ message }}
Repository navigation
Expand file tree
/
Copy pathPartitionedTableRendererPostgresTest.php
More file actions
240 lines (219 loc) · 9.22 KB
/
Copy pathPartitionedTableRendererPostgresTest.php
File metadata and controls
240 lines (219 loc) · 9.22 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
<?php
use DBDiff\DB\Adapters\PostgresAdapter;
use DBDiff\DB\Support\PgDumpRenderer;
use Illuminate\Database\Capsule\Manager as Capsule;
use Illuminate\Database\Events\StatementPrepared;
use Illuminate\Events\Dispatcher;
use PHPUnit\Framework\TestCase;
/**
* A partitioned table's DDL has to survive being reordered.
*
* DBDiff emits one statement per difference and says nothing about how they are
* applied. Consumers reorder: grouping a fix set by object kind — every table
* created before any constraint is added — is a reasonable thing to do, and
* SupaForge does exactly that. DDL whose correctness depends on DBDiff's own
* output order is therefore DDL that will sometimes be applied wrongly.
*
* pg_dump renders a partitioned parent's primary key as
*
* ALTER TABLE ONLY parent ADD CONSTRAINT parent_pkey PRIMARY KEY (...)
*
* and ONLY means the index reaches the partitions existing when it runs and no
* others. In pg_dump's own order the key precedes every CREATE TABLE ...
* PARTITION OF, so the partitions inherit it. Move the table creations first and
* the partitions never receive it — the migration applies cleanly and the
* primary keys are gone. That reached a release: every partitioned table diffed
* by 3.0.0-rc.10 on a machine with pg_dump lost its partition keys when applied
* by a consumer that reorders.
*
* The conformance suite could not see it, because it applies DBDiff's statements
* in DBDiff's order. These cases fix both halves: the rendering is asserted
* directly, and the result is asserted after a deliberate reordering.
*
* Skips when pdo_pgsql, DB_HOST_POSTGRES, or pg_dump is absent — without
* pg_dump the built-in renderer runs anyway, which is the behaviour being
* required here, so there would be nothing to prove.
*/
class PartitionedTableRendererPostgresTest extends TestCase
{
private string $database = 'dbdiff_partitioned_renderer';
private $connection;
private $capsule;
private PostgresAdapter $adapter;
private $adminDb;
private static function commandExists(string $bin): bool
{
exec('command -v ' . escapeshellarg($bin), $out, $code);
return $code === 0;
}
private function connect(string $host, string $port, string $user, string $pass, string $db)
{
$capsule = new Capsule;
$dispatcher = new Dispatcher();
$dispatcher->listen(StatementPrepared::class, function ($event) {
$event->statement->setFetchMode(PDO::FETCH_ASSOC);
});
$capsule->setEventDispatcher($dispatcher);
$capsule->addConnection([
'driver' => 'pgsql',
'host' => $host,
'port' => $port,
'database' => $db,
'username' => $user,
'password' => $pass,
'charset' => 'utf8',
'schema' => 'public',
], 'partitioned_' . $db);
$this->capsule = $capsule;
return $capsule->getConnection('partitioned_' . $db);
}
protected function setUp(): void
{
if (!extension_loaded('pdo_pgsql')) {
$this->markTestSkipped('pdo_pgsql extension not loaded.');
}
$host = getenv('DB_HOST_POSTGRES') ?: null;
if (!$host) {
$this->markTestSkipped('DB_HOST_POSTGRES env var not set.');
}
if (!self::commandExists('pg_dump') || !self::commandExists('pg_restore')) {
$this->markTestSkipped('pg_dump/pg_restore not on PATH — the built-in renderer runs regardless.');
}
// The suite pins the renderer off; this case is about what happens when
// it is available, so it opts back in.
putenv('DBDIFF_PG_DUMP_RENDERER=');
$port = '5432';
$user = 'dbdiff';
$pass = 'rootpass';
$this->adminDb = new PDO(
"pgsql:host=$host;port=$port;dbname=diff1", $user, $pass,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$this->dropDb();
$this->adminDb->exec("CREATE DATABASE {$this->database}");
$this->connection = $this->connect($host, $port, $user, $pass, $this->database);
$this->adapter = new PostgresAdapter();
$this->connection->unprepared(<<<'SQL'
CREATE TABLE readings (
id bigint GENERATED BY DEFAULT AS IDENTITY,
taken_on date NOT NULL,
value numeric(10,2),
PRIMARY KEY (taken_on, id)
) PARTITION BY RANGE (taken_on);
CREATE TABLE readings_2025 PARTITION OF readings
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE readings_2026 PARTITION OF readings
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
SQL);
// The availability probe dumps the database, and that archive is cached.
// The tables above were created after it, so without this the renderer
// would look for them in a snapshot taken before they existed.
PgDumpRenderer::reset();
}
protected function tearDown(): void
{
PgDumpRenderer::reset();
putenv('DBDIFF_PG_DUMP_RENDERER=off');
if ($this->connection) {
$this->connection->disconnect();
$this->connection = null;
}
$this->capsule = null;
if ($this->adminDb) {
$this->dropDb();
}
}
private function dropDb(): void
{
try {
$this->adminDb->exec("DROP DATABASE IF EXISTS {$this->database} WITH (FORCE)");
} catch (PDOException $e) {
$this->adminDb->exec("DROP DATABASE IF EXISTS {$this->database}");
}
}
/**
* The key has to be part of CREATE TABLE, not a later ALTER, so that a
* partition created afterwards inherits it no matter when the statements run.
*/
public function testPartitionedParentRendersItsPrimaryKeyInline(): void
{
$ddl = $this->adapter->getCreateStatement($this->connection, 'readings');
$this->assertStringContainsString('PARTITION BY RANGE', $ddl);
$this->assertMatchesRegularExpression(
'/CREATE\s+TABLE.*PRIMARY\s+KEY/s',
$ddl,
'the primary key must be inline in CREATE TABLE'
);
$this->assertStringNotContainsStringIgnoringCase(
'ALTER TABLE ONLY',
$ddl,
'ALTER TABLE ONLY reaches only the partitions that already exist, '
. 'so the key is lost whenever the statements are reordered'
);
}
/**
* The property that matters, asserted the way a consumer breaks it: create
* every table first, then everything else.
*/
public function testPartitionsKeepTheKeyWhenTablesAreCreatedFirst(): void
{
$statements = [];
foreach (['readings', 'readings_2025', 'readings_2026'] as $table) {
foreach (explode(";\n", $this->adapter->getCreateStatement($this->connection, $table)) as $stmt) {
$stmt = trim(rtrim(trim($stmt), ';'));
if ($stmt !== '') {
$statements[] = $stmt;
}
}
}
// Group by kind the way a consumer ordering a fix set would: tables
// before anything that alters them. Relative order within each group is
// kept, so the parent still precedes its partitions.
$creates = array_values(array_filter(
$statements,
fn(string $s): bool => (bool) preg_match('/^\s*CREATE\s+TABLE/i', $s)
));
$rest = array_values(array_filter(
$statements,
fn(string $s): bool => !preg_match('/^\s*CREATE\s+TABLE/i', $s)
));
$this->adminDb->exec('DROP DATABASE IF EXISTS ' . $this->database . '_replay');
$this->adminDb->exec('CREATE DATABASE ' . $this->database . '_replay');
$replay = $this->connect(
getenv('DB_HOST_POSTGRES'), '5432', 'dbdiff', 'rootpass', $this->database . '_replay'
);
try {
foreach (array_merge($creates, $rest) as $stmt) {
$replay->unprepared($stmt);
}
foreach (['readings_2025', 'readings_2026'] as $partition) {
$rows = $replay->select(
"SELECT con.conname
FROM pg_constraint con
JOIN pg_class c ON c.oid = con.conrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relname = ?
AND con.contype = 'p'",
[$partition]
);
$this->assertCount(
1,
$rows,
"$partition lost its primary key: the parent's key did not reach it, "
. 'which is what ALTER TABLE ONLY does once the partitions already exist'
);
}
} finally {
$replay->disconnect();
$this->capsule = null;
try {
$this->adminDb->exec(
'DROP DATABASE IF EXISTS ' . $this->database . '_replay WITH (FORCE)'
);
} catch (PDOException $e) {
$this->adminDb->exec('DROP DATABASE IF EXISTS ' . $this->database . '_replay');
}
}
}
}
You can’t perform that action at this time.