{{ message }}
Repository navigation
Expand file tree
/
Copy pathCrossVersionPostgresTest.php
More file actions
221 lines (203 loc) · 11.6 KB
/
Copy pathCrossVersionPostgresTest.php
File metadata and controls
221 lines (203 loc) · 11.6 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
<?php
/**
* The same objects on two PostgreSQL versions are not a change.
*
* PostgreSQL 16 and later print a stored view without the table qualifiers 15
* writes (`SELECT id` against `SELECT vt.id`), so every unchanged view read as
* changed between a 15 and a 17 database, and the migration dropped and
* recreated it. Each case builds the same object on both servers and requires
* "identical"; a changed copy must still be reported.
*
* Needs a second server of another major version: DB_HOST_POSTGRES_OTHER and
* DB_PORT_POSTGRES_OTHER. Skips without one.
*/
class CrossVersionPostgresTest extends PostgresRoundTripTestCase
{
protected string $prefix = 'dbdiff_xv';
private string $otherHost;
private string $otherPort;
private ?PDO $otherAdmin = null;
/** @var string[] */
private array $otherCreated = [];
private const BASE = 'CREATE TABLE vt (id int PRIMARY KEY, name text, a int, b int, status varchar(20));';
/** Each kind, written once and built the same way on both servers. */
private const KINDS = [
'view' => 'CREATE VIEW vt_v AS SELECT vt.id, vt.name FROM vt WHERE vt.a = 1 AND vt.b = 2;',
'join' => 'CREATE TABLE vu (id int PRIMARY KEY, vt_id int); CREATE VIEW vj AS SELECT vt.id, vu.id AS u FROM vt JOIN vu ON vu.vt_id = vt.id WHERE vt.a > 0;',
'matview' => 'CREATE MATERIALIZED VIEW vt_m AS SELECT vt.id FROM vt WHERE (vt.a = 1);',
'check' => "ALTER TABLE vt ADD CONSTRAINT c CHECK (status IN ('draft', 'active') AND a > b);",
'policy' => 'ALTER TABLE vt ENABLE ROW LEVEL SECURITY; CREATE POLICY p ON vt USING (a = 1 AND b = 2);',
'index' => 'CREATE INDEX i ON vt (id) WHERE a = 1 AND b = 2;',
// An ICU collation's locale moved between catalog columns (collcollate
// before 15, colliculocale in 15 and 16, colllocale from 17).
'collation' => "CREATE COLLATION vt_ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
ALTER TABLE vt ADD COLUMN folded text COLLATE vt_ci;",
// From PostgreSQL 18 a NOT NULL is a constraint, and a partition's is
// inherited under its parent's name: read as a constraint of its own,
// each partition, and the parent, differed from an older server.
'partitions' => "CREATE TABLE ev (id int, at date NOT NULL, a int NOT NULL) PARTITION BY RANGE (at);
CREATE TABLE ev_2026 PARTITION OF ev FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE ev_2027 PARTITION OF ev FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');
CREATE INDEX ev_i ON ev (id) WHERE a = 1;",
// A renamed column keeps its NOT NULL's old name on PostgreSQL 18,
// where it is a constraint; an older server has no name for it.
'renamed' => "CREATE TABLE rn (id int, old_name text NOT NULL); ALTER TABLE rn RENAME COLUMN old_name TO name;
CREATE INDEX rn_i ON vt (id) WHERE a = 1;",
// A view recreated for nothing had its comments set again, which
// read as comments changed although they were identical.
'comments' => "CREATE VIEW vt_c AS SELECT vt.id, vt.name FROM vt WHERE vt.a = 1;
CREATE MATERIALIZED VIEW vt_cm AS SELECT vt.id FROM vt WHERE vt.a = 1;
COMMENT ON VIEW vt_c IS 'v'; COMMENT ON COLUMN vt_c.name IS 'vn'; COMMENT ON MATERIALIZED VIEW vt_cm IS 'm';",
];
protected function setUp(): void
{
parent::setUp();
$host = getenv('DB_HOST_POSTGRES_OTHER') ?: null;
$port = getenv('DB_PORT_POSTGRES_OTHER') ?: null;
if (!$host || !$port) {
$this->markTestSkipped('DB_HOST_POSTGRES_OTHER and DB_PORT_POSTGRES_OTHER are not set.');
}
$this->otherHost = $host;
$this->otherPort = $port;
$this->otherAdmin = new PDO("pgsql:host=$host;port=$port;dbname=diff1", $this->user, $this->pass,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$other = (int) $this->otherAdmin->query('SHOW server_version_num')->fetchColumn();
if (intdiv($other, 10000) === intdiv($this->serverVersion, 10000)) {
$this->markTestSkipped('Both servers run the same major version.');
}
}
protected function tearDown(): void
{
foreach ($this->otherCreated as $db) {
$this->otherAdmin->exec("DROP DATABASE IF EXISTS $db WITH (FORCE)");
}
$this->otherAdmin = null;
parent::tearDown();
}
/** A database on the second server. */
private function otherDb(string $name, string $sql): string
{
$db = "{$this->prefix}_$name";
$this->otherAdmin->exec("DROP DATABASE IF EXISTS $db WITH (FORCE)");
$this->otherAdmin->exec("CREATE DATABASE $db");
$this->otherCreated[] = $db;
(new PDO("pgsql:host={$this->otherHost};port={$this->otherPort};dbname=$db", $this->user, $this->pass,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]))->exec($sql);
return $db;
}
/** The UP from `$source` on this server to `$target` on the other, or null when identical. */
private function upAcross(string $source, string $target, bool $targetReadOnly = false): ?string
{
$out = tempnam(sys_get_temp_dir(), $this->prefix . '_') . '.sql';
$otherUrl = "postgres://{$this->user}:{$this->pass}@{$this->otherHost}:{$this->otherPort}/$target"
. ($targetReadOnly ? '?options=-c%20default_transaction_read_only%3Don' : '');
exec(escapeshellarg(PHP_BINARY) . ' ' . escapeshellarg(dirname(__DIR__) . '/dbdiff.php')
. ' diff --nocomments --allow-destructive'
. ' --server1-url=' . escapeshellarg($this->url($source))
. ' --server2-url=' . escapeshellarg($otherUrl)
. ' --output=' . escapeshellarg($out) . ' 2>&1', $lines, $status);
$this->assertSame(0, $status, "dbdiff failed:\n" . implode("\n", $lines));
if (!file_exists($out)) {
return null;
}
$sql = trim(preg_replace('/^--.*$/m', '', file_get_contents($out)));
unlink($out);
return $sql === '' ? null : $sql;
}
/**
* A table only the source has is created on the other server, and the
* migration runs there. From PostgreSQL 18 to an older one, a NOT NULL
* constraint under a name of its own was written `CONSTRAINT "x" NOT NULL
* "c"`, a syntax error there, and the whole migration failed.
*/
public function testATableOnlyTheSourceHasIsCreatedOnTheOtherServer(): void
{
$table = "CREATE TABLE moved (id int PRIMARY KEY, old_name text NOT NULL, at date NOT NULL) PARTITION BY RANGE (id);
CREATE TABLE moved_1 PARTITION OF moved FOR VALUES FROM (0) TO (1000);
ALTER TABLE moved RENAME COLUMN old_name TO name;";
$source = $this->db('ms', self::BASE . $table);
$target = $this->otherDb('mt', self::BASE);
$up = $this->upAcross($source, $target);
$this->assertNotNull($up);
(new PDO("pgsql:host={$this->otherHost};port={$this->otherPort};dbname=$target", $this->user, $this->pass,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]))->exec($up);
$this->assertNull($this->upAcross($source, $target), 'the table created on the other server still differs');
}
/**
* MAINTAIN arrived in PostgreSQL 17. A view a 17 source grants it on, and
* default privileges that give it, were written for an older target with
* it: GRANT MAINTAIN failed there, and the replaced view, and every view
* reading it, were left out with it.
*/
public function testMaintainIsLeftOutForAnOlderServer(): void
{
$other = intdiv((int) $this->otherAdmin->query('SHOW server_version_num')->fetchColumn(), 10000);
if (intdiv($this->serverVersion, 10000) < 17 || $other >= 17) {
$this->markTestSkipped('Needs a PostgreSQL 17 or newer source and an older target.');
}
$role = "DO \$\$ BEGIN CREATE ROLE dbdiff_xv_reader NOLOGIN; EXCEPTION WHEN duplicate_object THEN NULL; END \$\$;";
$this->admin->exec($role);
$this->otherAdmin->exec($role);
$source = $this->db('mn_s', self::BASE . "CREATE VIEW mv AS SELECT id FROM vt WHERE a = 2;
GRANT SELECT, MAINTAIN ON mv TO dbdiff_xv_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, MAINTAIN ON TABLES TO dbdiff_xv_reader;");
$target = $this->otherDb('mn_t', self::BASE . "CREATE VIEW mv AS SELECT id FROM vt WHERE a = 1;
GRANT SELECT ON mv TO dbdiff_xv_reader;");
$up = $this->upAcross($source, $target);
$this->assertNotNull($up);
$this->assertStringNotContainsStringIgnoringCase('MAINTAIN', $up);
(new PDO("pgsql:host={$this->otherHost};port={$this->otherPort};dbname=$target", $this->user, $this->pass,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]))->exec($up);
$this->assertNull($this->upAcross($source, $target), 'the older server still differs');
}
/** @return array<string, array{string}> */
public static function kinds(): array
{
return array_combine(array_keys(self::KINDS), array_map(fn($k) => [$k], array_keys(self::KINDS)));
}
/** @dataProvider kinds */
public function testTheSameObjectOnAnotherVersionIsIdentical(string $kind): void
{
$sql = self::BASE . self::KINDS[$kind];
$source = $this->db("{$kind}_s", $sql);
$target = $this->otherDb("{$kind}_t", $sql);
$this->assertNull($this->upAcross($source, $target), "$kind: the same definition on another version was reported as a change");
}
/** @dataProvider kinds */
public function testARealChangeIsStillReported(string $kind): void
{
$source = $this->db("{$kind}_rs", self::BASE . self::KINDS[$kind]);
$target = $this->otherDb("{$kind}_rt", self::BASE . str_replace(['a = 1', 'a > 0', 'a > b', 'ks-level2'], ['a = 9', 'a > 9', 'a < b', 'ks-level1'], self::KINDS[$kind]));
$this->assertNotNull($this->upAcross($source, $target), "$kind: a real change was hidden as equivalent");
}
/**
* A read-only server cannot render a definition, so the other renders
* both: the target, or here the source when it is the target that is
* read-only.
*
* @dataProvider kinds
*/
public function testTheSameObjectOverAReadOnlyTargetIsIdentical(string $kind): void
{
$sql = self::BASE . self::KINDS[$kind];
$source = $this->db("{$kind}_ros", $sql);
$target = $this->otherDb("{$kind}_rot", $sql);
$this->assertNull($this->upAcross($source, $target, true), "$kind: a read-only target turned the same definition into a change");
}
/** @dataProvider kinds */
public function testTheSameObjectOverAReadOnlySourceIsIdentical(string $kind): void
{
$sql = self::BASE . self::KINDS[$kind];
$source = $this->db("{$kind}_rss", $sql);
$target = $this->otherDb("{$kind}_rst", $sql);
$this->readOnly = [$source];
$this->assertNull($this->upAcross($source, $target), "$kind: a read-only source turned the same definition into a change");
}
/** @dataProvider kinds */
public function testARealChangeIsStillReportedOverAReadOnlyTarget(string $kind): void
{
$source = $this->db("{$kind}_rrs", self::BASE . self::KINDS[$kind]);
$target = $this->otherDb("{$kind}_rrt", self::BASE . str_replace(['a = 1', 'a > 0', 'a > b', 'ks-level2'], ['a = 9', 'a > 9', 'a < b', 'ks-level1'], self::KINDS[$kind]));
$this->assertNotNull($this->upAcross($source, $target, true), "$kind: a real change was hidden as equivalent");
}
}
You can’t perform that action at this time.