{{ message }}
Repository navigation
Expand file tree
/
Copy pathDBDiffComprehensivePostgresTest.php
More file actions
436 lines (389 loc) · 16.4 KB
/
Copy pathDBDiffComprehensivePostgresTest.php
File metadata and controls
436 lines (389 loc) · 16.4 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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
<?php
require_once __DIR__ . '/AbstractComprehensiveTest.php';
/**
* PostgreSQL implementation of the comprehensive test suite.
* All test methods live in AbstractComprehensiveTest.
*
* Skips automatically when:
* - pdo_pgsql is not loaded
* - DB_HOST_POSTGRES is not set (not running inside a Postgres CLI container)
*/
class DBDiffComprehensivePostgresTest extends AbstractComprehensiveTest
{
private $host;
private $port = 5432;
private $user = 'dbdiff';
private $pass = 'rootpass';
private $defaultDb = 'diff1'; // pre-created by POSTGRES_DB in compose
/** @var \PDO Connection used for DDL (CREATE / DROP DATABASE) */
private $adminDb;
/** @var int Detected Postgres major version */
private $pgMajorVersion;
// ── Abstract method implementations ───────────────────────────────────
protected function connectAndBootstrap(): void
{
if (!extension_loaded('pdo_pgsql')) {
$this->markTestSkipped('pdo_pgsql extension not loaded — skipping Postgres comprehensive tests.');
}
$this->host = getenv('DB_HOST_POSTGRES') ?: null;
if (!$this->host) {
$this->markTestSkipped('DB_HOST_POSTGRES env var not set — skipping Postgres comprehensive tests.');
}
$this->db1 = 'dbdiff_comp1';
$this->db2 = 'dbdiff_comp2';
$this->adminDb = $this->connectWithRetry(
"pgsql:host={$this->host};port={$this->port};dbname={$this->defaultDb}",
$this->user,
$this->pass
);
// Detect major version early so getVersionSuffix() works during the test
$row = $this->adminDb->query(
"SELECT current_setting('server_version_num') AS v"
)->fetch(PDO::FETCH_ASSOC);
$this->pgMajorVersion = intdiv((int) ($row['v'] ?? 0), 10000);
$this->adminDb->exec("DROP DATABASE IF EXISTS {$this->db1}");
$this->adminDb->exec("DROP DATABASE IF EXISTS {$this->db2}");
$this->adminDb->exec("CREATE DATABASE {$this->db1}");
$this->adminDb->exec("CREATE DATABASE {$this->db2}");
}
protected function getVersionSuffix(): string
{
return 'pgsql_' . $this->pgMajorVersion;
}
protected function loadFixture(string $fixtureName): void
{
foreach (['db1' => $this->db1, 'db2' => $this->db2] as $key => $dbName) {
$file = "tests/fixtures/{$fixtureName}/{$key}-pgsql.sql";
if (!file_exists($file)) {
$this->fail("Postgres fixture not found: $file");
}
$pdo = new PDO(
"pgsql:host={$this->host};port={$this->port};dbname={$dbName}",
$this->user,
$this->pass
);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec(file_get_contents($file));
}
}
protected function driverArgs(): array
{
return [
'--driver=pgsql',
"--server1={$this->user}:{$this->pass}@{$this->host}:{$this->port}",
];
}
protected function dbInputArg(): string
{
return "server1.{$this->db1}:server1.{$this->db2}";
}
protected function tableInputArg(string $table): ?string
{
return "server1.{$this->db1}.{$table}:server1.{$this->db2}.{$table}";
}
protected function getServerConfig(): array
{
return [
'user' => $this->user,
'password' => $this->pass,
'host' => $this->host,
'port' => $this->port,
];
}
protected function configDefaults(): array
{
return [
'driver' => 'pgsql',
'type' => 'all',
'include' => 'all',
'nocomments' => true,
];
}
/**
* Partial indexes, expression indexes, and multi-column index changes.
* PostgreSQL-specific: MySQL and SQLite don't support partial indexes.
*/
public function testPartialIndexChanges(): void
{
$this->loadFixture('partial_indexes');
$output = $this->runDBDiff(array_merge(
$this->driverArgs(),
['--type=schema', '--include=all', '--nocomments', $this->dbInputArg()]
));
$this->assertExpectedOutput('partial_indexes', $output);
$this->assertNotEmpty(
trim($output),
'Partial index changes must produce a non-empty diff'
);
$this->assertStringContainsString(
'events',
$output,
'Diff should reference the events table'
);
}
/**
* A generated migration has to be valid SQL, not merely the expected text.
*
* Every other test here compares output against tests/expected/*.txt, so a
* migration PostgreSQL refuses to run still passes as long as the text is
* stable. Not one of the 114 expected files contains a CREATE TABLE, so
* creating a table that exists only on the source was entirely uncovered —
* and it emitted the primary key twice, once bare and once as a named
* constraint:
*
* CREATE TABLE "t" ("id" bigint NOT NULL, PRIMARY KEY ("id"),
* CONSTRAINT "t_pkey" PRIMARY KEY ("id"));
* ERROR: multiple primary keys for table "t" are not allowed
*
* This replays the migration into the target and asserts it applies, which
* is the only assertion that catches invalid DDL.
*/
public function testGeneratedCreateTableIsValidSql(): void
{
$db1 = $this->connectTo($this->db1);
$db1->exec(
'CREATE TABLE replay_target (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
email text NOT NULL,
CONSTRAINT replay_target_email_uniq UNIQUE (email)
)'
);
$output = $this->runDBDiff(array_merge(
$this->driverArgs(),
['--type=schema', '--include=up', '--nocomments', $this->dbInputArg()]
));
$this->assertStringContainsString('CREATE TABLE', $output, 'Diff should create the missing table');
$this->assertSame(
1,
preg_match_all('/PRIMARY KEY/i', $output),
"The primary key must be emitted exactly once, got:\n$output"
);
// The real assertion: PostgreSQL accepts what we generated.
$db2 = $this->connectTo($this->db2);
$db2->exec($this->stripMigrationMarkers($output));
$exists = $db2->query(
"SELECT count(*) FROM information_schema.tables
WHERE table_schema = 'public' AND table_name = 'replay_target'"
)->fetchColumn();
$this->assertEquals(1, $exists, 'Replayed migration should create the table');
}
/**
* A serial column carries DEFAULT nextval('<table>_<col>_seq'), but that
* sequence is owned by the table and was never emitted by the migration,
* so replaying the CREATE TABLE failed with:
*
* ERROR: relation "books_id_seq" does not exist
*
* Writing the column back as serial re-creates the owned sequence
* implicitly. Asserting on the resulting default (rather than the DDL text)
* proves the column round-trips to the same thing it started as.
*/
public function testGeneratedSerialColumnIsValidSql(): void
{
$this->connectTo($this->db1)->exec(
'CREATE TABLE serial_target (
id serial PRIMARY KEY,
big bigserial,
small smallserial,
title text
)'
);
$output = $this->runDBDiff(array_merge(
$this->driverArgs(),
['--type=schema', '--include=up', '--nocomments', $this->dbInputArg()]
));
$db2 = $this->connectTo($this->db2);
$db2->exec($this->stripMigrationMarkers($output));
$defaults = $db2->query(
"SELECT column_name, column_default FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'serial_target'
AND column_name IN ('id','big','small')
ORDER BY column_name"
)->fetchAll(PDO::FETCH_KEY_PAIR);
$this->assertSame(
[
'big' => "nextval('serial_target_big_seq'::regclass)",
'id' => "nextval('serial_target_id_seq'::regclass)",
'small' => "nextval('serial_target_small_seq'::regclass)",
],
$defaults,
'Serial columns must round-trip to their own sequence'
);
}
/**
* information_schema reports every enum, composite and extension type as
* the literal string 'USER-DEFINED'; the real name lives in udt_name. That
* string was being written straight into the DDL:
*
* "status" USER-DEFINED
* ERROR: syntax error at or near "USER"
*
* so no table using an enum could be created — and enums are ubiquitous in
* Supabase schemas.
*/
public function testGeneratedEnumColumnIsValidSql(): void
{
$db1 = $this->connectTo($this->db1);
$db1->exec("CREATE TYPE enum_target_status AS ENUM ('active','paused','closed')");
$db1->exec(
'CREATE TABLE enum_target (
id bigint PRIMARY KEY,
status enum_target_status NOT NULL DEFAULT \'active\'
)'
);
$output = $this->runDBDiff(array_merge(
$this->driverArgs(),
['--type=schema', '--include=up', '--nocomments', $this->dbInputArg()]
));
$this->assertStringNotContainsString(
'USER-DEFINED',
$output,
'Enum columns must be emitted with their type name, not the information_schema placeholder'
);
$db2 = $this->connectTo($this->db2);
$db2->exec($this->stripMigrationMarkers($output));
$type = $db2->query(
"SELECT udt_name FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'enum_target'
AND column_name = 'status'"
)->fetchColumn();
$this->assertSame('enum_target_status', $type, 'Enum column should keep its type');
}
/**
* Partitioned tables were reconstructed as ordinary tables: the parent lost
* its PARTITION BY clause and each partition became a detached standalone
* table. Nothing errored — rows still inserted — so the data model quietly
* changed shape, which is worse than a failure.
*
* Asserts on the target's catalog (relkind and relpartbound) rather than
* the DDL text, because that is what "is it actually partitioned?" means.
*/
public function testPartitionedTablesArePreserved(): void
{
$this->connectTo($this->db1)->exec(
"CREATE TABLE part_target (
id bigint GENERATED BY DEFAULT AS IDENTITY,
order_date date NOT NULL,
amount bigint,
PRIMARY KEY (order_date, id)
) PARTITION BY RANGE (order_date);
CREATE TABLE part_target_2024 PARTITION OF part_target
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE part_target_2025 PARTITION OF part_target
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');"
);
$output = $this->runDBDiff(array_merge(
$this->driverArgs(),
['--type=schema', '--include=up', '--nocomments', $this->dbInputArg()]
));
$db2 = $this->connectTo($this->db2);
$db2->exec($this->stripMigrationMarkers($output));
$parentKind = $db2->query(
"SELECT relkind::text FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname = 'part_target'"
)->fetchColumn();
$this->assertSame('p', $parentKind, 'Parent must remain a partitioned table');
$bounds = $db2->query(
// relkind filter matters: index partitions are also relispartition.
"SELECT c.relname, pg_get_expr(c.relpartbound, c.oid) FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relispartition
AND c.relkind IN ('r', 'p')
ORDER BY c.relname"
)->fetchAll(PDO::FETCH_KEY_PAIR);
$this->assertSame(
[
'part_target_2024' => "FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')",
'part_target_2025' => "FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')",
],
$bounds,
'Each partition must be attached with its original bound'
);
}
/**
* A trigger declared on a partitioned parent is propagated by PostgreSQL to
* every partition, recorded with tgparentid pointing at the parent's.
* Reproducing those clones made the migration create the parent trigger
* (which propagates) and then try to create the copies too:
*
* ERROR: trigger "..." for relation "..." already exists
*
* Only the parent's declaration is ours to emit. Surfaced by replaying the
* Supabase docs corpus once partitions were being attached properly rather
* than flattened into standalone tables.
*/
public function testPartitionTriggerClonesAreNotDuplicated(): void
{
$this->connectTo($this->db1)->exec(
"CREATE TABLE trg_part (
id bigint NOT NULL,
region text NOT NULL,
PRIMARY KEY (region, id)
) PARTITION BY LIST (region);
CREATE TABLE trg_part_emea PARTITION OF trg_part FOR VALUES IN ('emea');
CREATE TABLE trg_part_apac PARTITION OF trg_part FOR VALUES IN ('apac');
CREATE FUNCTION trg_part_noop() RETURNS trigger LANGUAGE plpgsql
AS $$ BEGIN RETURN NEW; END $$;
CREATE TRIGGER trg_part_after_ins AFTER INSERT ON trg_part
FOR EACH ROW EXECUTE FUNCTION trg_part_noop();"
);
$output = $this->runDBDiff(array_merge(
$this->driverArgs(),
['--type=schema', '--include=up', '--nocomments', $this->dbInputArg()]
));
$this->assertSame(
1,
substr_count($output, 'CREATE TRIGGER trg_part_after_ins'),
"The trigger must be emitted once, for the parent only:\n$output"
);
// The real assertion: PostgreSQL accepts the whole migration.
$db2 = $this->connectTo($this->db2);
$db2->exec($this->stripMigrationMarkers($output));
// Count every row, internal or not: PostgreSQL 14 marks a propagated
// clone tgisinternal = true, while 15+ leaves it false. That change is
// exactly why the duplicate only appears on 15+ — on 14 the existing
// NOT tgisinternal filter already excluded clones by accident.
$count = $db2->query(
"SELECT count(*) FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
WHERE t.tgname = 'trg_part_after_ins'"
)->fetchColumn();
// One declared on the parent, propagated to both partitions.
$this->assertEquals(3, $count, 'Parent trigger should propagate to each partition');
}
/** Connect to one of the scratch databases with exceptions enabled. */
private function connectTo(string $dbName): PDO
{
$pdo = new PDO(
"pgsql:host={$this->host};port={$this->port};dbname={$dbName}",
$this->user,
$this->pass
);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
}
/**
* Drop the "#---------- UP ----------" banners so the migration body can be
* executed as-is. Both marker styles appear depending on --nocomments.
*/
private function stripMigrationMarkers(string $sql): string
{
return preg_replace('/^\s*(#|--).*$/m', '', $sql);
}
protected function tearDownDatabases(): void
{
if (!$this->adminDb) {
return;
}
// Terminate active connections before dropping
foreach ([$this->db1, $this->db2] as $db) {
$this->adminDb->exec(
"SELECT pg_terminate_backend(pid) " .
"FROM pg_stat_activity " .
"WHERE datname = '$db' AND pid <> pg_backend_pid()"
);
$this->adminDb->exec("DROP DATABASE IF EXISTS $db");
}
}
}
You can’t perform that action at this time.