{{ message }}
Repository navigation
Expand file tree
/
Copy pathObjectKindsSQLTest.php
More file actions
272 lines (226 loc) · 11.3 KB
/
Copy pathObjectKindsSQLTest.php
File metadata and controls
272 lines (226 loc) · 11.3 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
<?php
namespace DBDiff\Tests\Unit;
use PHPUnit\Framework\TestCase;
use DBDiff\Diff\CreateSequence;
use DBDiff\Diff\DropSequence;
use DBDiff\Diff\AlterSequence;
use DBDiff\Diff\CreateCompositeType;
use DBDiff\Diff\DropCompositeType;
use DBDiff\Diff\AlterCompositeType;
use DBDiff\Diff\CreateDomain;
use DBDiff\Diff\DropDomain;
use DBDiff\Diff\AlterDomain;
use DBDiff\Diff\CreateMatView;
use DBDiff\Diff\DropMatView;
use DBDiff\Diff\AlterMatView;
use DBDiff\Diff\CreatePolicy;
use DBDiff\Diff\DropPolicy;
use DBDiff\Diff\AlterPolicy;
use DBDiff\Diff\AlterRowSecurity;
use DBDiff\SQLGen\DiffToSQL\CreateSequenceSQL;
use DBDiff\SQLGen\DiffToSQL\DropSequenceSQL;
use DBDiff\SQLGen\DiffToSQL\AlterSequenceSQL;
use DBDiff\SQLGen\DiffToSQL\CreateCompositeTypeSQL;
use DBDiff\SQLGen\DiffToSQL\DropCompositeTypeSQL;
use DBDiff\SQLGen\DiffToSQL\AlterCompositeTypeSQL;
use DBDiff\SQLGen\DiffToSQL\CreateDomainSQL;
use DBDiff\SQLGen\DiffToSQL\DropDomainSQL;
use DBDiff\SQLGen\DiffToSQL\AlterDomainSQL;
use DBDiff\SQLGen\DiffToSQL\CreateMatViewSQL;
use DBDiff\SQLGen\DiffToSQL\DropMatViewSQL;
use DBDiff\SQLGen\DiffToSQL\AlterMatViewSQL;
use DBDiff\SQLGen\DiffToSQL\CreatePolicySQL;
use DBDiff\SQLGen\DiffToSQL\DropPolicySQL;
use DBDiff\SQLGen\DiffToSQL\AlterPolicySQL;
use DBDiff\SQLGen\DiffToSQL\AlterRowSecuritySQL;
use DBDiff\SQLGen\Dialect\PostgresDialect;
/**
* SQL generation for the object kinds DBDiff previously did not model:
* standalone sequences, composite types, domains, materialised views, and row
* level security with its policies.
*
* Until these existed DBDiff reported "no differences" when only one of them
* had changed, which is the failure mode a schema diff can least afford.
*/
class ObjectKindsSQLTest extends TestCase
{
private PostgresDialect $pg;
protected function setUp(): void
{
$this->pg = new PostgresDialect();
}
// ── Sequences ──────────────────────────────────────────────────────────
private const SEQ = 'CREATE SEQUENCE "o_seq" AS bigint INCREMENT BY 5 '
. 'MINVALUE 100 MAXVALUE 100000 START WITH 100 CACHE 1 CYCLE';
public function testCreateSequenceUp(): void
{
$sql = new CreateSequenceSQL(new CreateSequence('o_seq', self::SEQ), $this->pg);
$this->assertSame(self::SEQ . ';', $sql->getUp());
}
public function testCreateSequenceDown(): void
{
$sql = new CreateSequenceSQL(new CreateSequence('o_seq', self::SEQ), $this->pg);
$this->assertSame('DROP SEQUENCE IF EXISTS "o_seq";', $sql->getDown());
}
public function testDropSequenceIsTheInverse(): void
{
$sql = new DropSequenceSQL(new DropSequence('o_seq', self::SEQ), $this->pg);
$this->assertSame('DROP SEQUENCE IF EXISTS "o_seq";', $sql->getUp());
$this->assertSame(self::SEQ . ';', $sql->getDown());
}
/**
* A sequence carries a value, so it is altered in place. Dropping and
* recreating it would silently restart the counter.
*/
public function testAlterSequenceRewritesCreateToAlter(): void
{
$target = 'CREATE SEQUENCE "o_seq" AS bigint INCREMENT BY 1 '
. 'MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1 NO CYCLE';
$sql = new AlterSequenceSQL(new AlterSequence('o_seq', self::SEQ, $target), $this->pg);
$this->assertSame(
'ALTER SEQUENCE "o_seq" AS bigint INCREMENT BY 5 '
. 'MINVALUE 100 MAXVALUE 100000 START WITH 100 CACHE 1 CYCLE;',
$sql->getUp()
);
$this->assertStringStartsWith('ALTER SEQUENCE "o_seq"', $sql->getDown());
$this->assertStringNotContainsString('DROP SEQUENCE', $sql->getUp());
$this->assertStringNotContainsString('DROP SEQUENCE', $sql->getDown());
}
// ── Composite types ───────────────────────────────────────────────────
public function testCreateCompositeType(): void
{
$def = 'CREATE TYPE "o_addr" AS (street text, city text)';
$diff = new CreateCompositeType('o_addr', $def);
$sql = new CreateCompositeTypeSQL($diff, $this->pg);
$this->assertSame($def . ';', $sql->getUp());
$this->assertSame('DROP TYPE IF EXISTS "o_addr";', $sql->getDown());
}
public function testDropCompositeTypeIsTheInverse(): void
{
$def = 'CREATE TYPE "o_addr" AS (street text, city text)';
$sql = new DropCompositeTypeSQL(new DropCompositeType('o_addr', $def), $this->pg);
$this->assertSame('DROP TYPE IF EXISTS "o_addr";', $sql->getUp());
$this->assertSame($def . ';', $sql->getDown());
}
public function testAlterCompositeTypeRecreates(): void
{
$src = 'CREATE TYPE "o_addr" AS (street text, city text, postcode text)';
$tgt = 'CREATE TYPE "o_addr" AS (street text, city text)';
$sql = new AlterCompositeTypeSQL(new AlterCompositeType('o_addr', $src, $tgt), $this->pg);
$this->assertSame("DROP TYPE IF EXISTS \"o_addr\";\n" . $src . ';', $sql->getUp());
$this->assertSame("DROP TYPE IF EXISTS \"o_addr\";\n" . $tgt . ';', $sql->getDown());
}
// ── Domains ───────────────────────────────────────────────────────────
public function testCreateDomain(): void
{
$def = 'CREATE DOMAIN "o_pos" AS integer CONSTRAINT o_pos_check CHECK ((VALUE > 0))';
$diff = new CreateDomain('o_pos', $def);
$sql = new CreateDomainSQL($diff, $this->pg);
$this->assertSame($def . ';', $sql->getUp());
$this->assertSame('DROP DOMAIN IF EXISTS "o_pos";', $sql->getDown());
}
public function testDropDomainIsTheInverse(): void
{
$def = 'CREATE DOMAIN "o_pos" AS integer';
$sql = new DropDomainSQL(new DropDomain('o_pos', $def), $this->pg);
$this->assertSame('DROP DOMAIN IF EXISTS "o_pos";', $sql->getUp());
$this->assertSame($def . ';', $sql->getDown());
}
public function testAlterDomainRecreates(): void
{
$src = 'CREATE DOMAIN "d" AS text NOT NULL';
$tgt = 'CREATE DOMAIN "d" AS text';
$sql = new AlterDomainSQL(new AlterDomain('d', $src, $tgt), $this->pg);
$this->assertSame("DROP DOMAIN IF EXISTS \"d\";\n" . $src . ';', $sql->getUp());
$this->assertSame("DROP DOMAIN IF EXISTS \"d\";\n" . $tgt . ';', $sql->getDown());
}
// ── Materialised views ────────────────────────────────────────────────
public function testCreateMatView(): void
{
$def = 'CREATE MATERIALIZED VIEW "o_mv" AS SELECT id, n FROM o_mt WHERE (n > 0)';
$diff = new CreateMatView('o_mv', $def);
$sql = new CreateMatViewSQL($diff, $this->pg);
$this->assertSame($def . ';', $sql->getUp());
$this->assertSame('DROP MATERIALIZED VIEW IF EXISTS "o_mv";', $sql->getDown());
}
public function testDropMatViewIsTheInverse(): void
{
$def = 'CREATE MATERIALIZED VIEW "o_mv" AS SELECT id FROM o_mt';
$sql = new DropMatViewSQL(new DropMatView('o_mv', $def), $this->pg);
$this->assertSame('DROP MATERIALIZED VIEW IF EXISTS "o_mv";', $sql->getUp());
$this->assertSame($def . ';', $sql->getDown());
}
/**
* PostgreSQL has no CREATE OR REPLACE for a materialised view, so a changed
* definition can only be applied by recreating it.
*/
public function testAlterMatViewRecreates(): void
{
$src = 'CREATE MATERIALIZED VIEW "m" AS SELECT id FROM t WHERE (n > 0)';
$tgt = 'CREATE MATERIALIZED VIEW "m" AS SELECT id FROM t WHERE (n < 0)';
$sql = new AlterMatViewSQL(new AlterMatView('m', $src, $tgt), $this->pg);
$this->assertSame("DROP MATERIALIZED VIEW IF EXISTS \"m\";\n" . $src . ';', $sql->getUp());
$this->assertSame("DROP MATERIALIZED VIEW IF EXISTS \"m\";\n" . $tgt . ';', $sql->getDown());
}
// ── Policies ──────────────────────────────────────────────────────────
public function testCreatePolicyDropsWithItsTable(): void
{
$def = 'CREATE POLICY "o_sec_read" ON "o_sec" FOR SELECT USING (true)';
$diff = new CreatePolicy('o_sec_read', 'o_sec', $def);
$sql = new CreatePolicySQL($diff, $this->pg);
$this->assertSame($def . ';', $sql->getUp());
// A policy name is unique per table, so the table has to be named.
$this->assertSame('DROP POLICY IF EXISTS "o_sec_read" ON "o_sec";', $sql->getDown());
}
public function testDropPolicyIsTheInverse(): void
{
$def = 'CREATE POLICY "p" ON "t" FOR ALL USING (true)';
$sql = new DropPolicySQL(new DropPolicy('p', 't', $def), $this->pg);
$this->assertSame('DROP POLICY IF EXISTS "p" ON "t";', $sql->getUp());
$this->assertSame($def . ';', $sql->getDown());
}
public function testAlterPolicyRecreates(): void
{
$src = 'CREATE POLICY "p" ON "t" FOR SELECT USING (true)';
$tgt = 'CREATE POLICY "p" ON "t" FOR SELECT USING (false)';
$sql = new AlterPolicySQL(new AlterPolicy('p', 't', $src, $tgt), $this->pg);
$this->assertSame("DROP POLICY IF EXISTS \"p\" ON \"t\";\n" . $src . ';', $sql->getUp());
$this->assertSame("DROP POLICY IF EXISTS \"p\" ON \"t\";\n" . $tgt . ';', $sql->getDown());
}
// ── Row level security flags ──────────────────────────────────────────
public function testEnablingRowSecurity(): void
{
$diff = new AlterRowSecurity('t', true, false, false, false);
$sql = new AlterRowSecuritySQL($diff, $this->pg);
$this->assertSame(
"ALTER TABLE \"t\" ENABLE ROW LEVEL SECURITY;\n"
. 'ALTER TABLE "t" NO FORCE ROW LEVEL SECURITY;',
$sql->getUp()
);
$this->assertSame(
"ALTER TABLE \"t\" DISABLE ROW LEVEL SECURITY;\n"
. 'ALTER TABLE "t" NO FORCE ROW LEVEL SECURITY;',
$sql->getDown()
);
}
public function testForcingRowSecurity(): void
{
$diff = new AlterRowSecurity('t', true, true, false, false);
$sql = new AlterRowSecuritySQL($diff, $this->pg);
$this->assertStringContainsString('ENABLE ROW LEVEL SECURITY', $sql->getUp());
$this->assertStringContainsString('FORCE ROW LEVEL SECURITY', $sql->getUp());
$this->assertStringNotContainsString('NO FORCE', $sql->getUp());
}
/**
* A table that exists only on the source side is dropped by its own DOWN
* migration, so restating its flags there would ALTER a relation that has
* already gone.
*/
public function testRowSecurityOnANewTableHasNoDown(): void
{
$diff = new AlterRowSecurity('t', true, false, false, false, true);
$sql = new AlterRowSecuritySQL($diff, $this->pg);
$this->assertStringContainsString('ENABLE ROW LEVEL SECURITY', $sql->getUp());
$this->assertSame('', $sql->getDown());
}
}
You can’t perform that action at this time.