immigrant-generator-postgres 0.2.0

Immigrant migrations generator for postgres SQL dialect
Documentation 
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

!!!TEST struct created and used in table, this struct depends on other two structs to make sure toposort works
table Test {
	a: mystruct;
};
scalar myint = sql"INTEGER";
struct mystruct {
	field "renamed": myint;
};
!!!UPDATE create struct with added field, migrate table
table Test {
	a "olda": mystruct;
	newa "a": newmystruct @initialize_as ((a.field, 2,)::newmystruct);
};
scalar myint = sql"INTEGER";
struct mystruct "oldmystruct" {
	field "renamed": myint;
};
struct newmystruct "mystruct" {
	field "renamed": myint;
	field2: myint;
};
!!!UPDATE remove old struct
table Test {
	newa "a": newmystruct;
};
scalar myint = sql"INTEGER";
struct newmystruct "mystruct" {
	field "renamed": myint;
	field2: myint;
};
!!!UPDATE finalize
table Test {
	a: mystruct;
};
scalar myint = sql"INTEGER";
struct mystruct {
	field "renamed": myint;
	field2: myint;
};
!!!RESULT
-- updated: struct created and used in table, this struct depends on other two structs to make sure toposort works --
CREATE DOMAIN myint AS INTEGER;
CREATE TYPE mystruct AS (
	renamed myint
);
CREATE TABLE tests (
	a mystruct NOT NULL
,	CONSTRAINT composite_nullability_check CHECK (a IS NULL OR a.renamed IS NOT NULL)
);
-- updated: create struct with added field, migrate table --
ALTER TYPE mystruct
	RENAME TO oldmystruct
;
CREATE TYPE mystruct AS (
	renamed myint
,	field2 myint
);
ALTER TABLE tests RENAME COLUMN a TO olda;
ALTER TABLE tests
	DROP CONSTRAINT composite_nullability_check
,	ADD COLUMN a mystruct
;
ALTER TABLE tests
	ALTER COLUMN a SET DATA TYPE mystruct USING (ROW(olda.renamed, 2))::mystruct
,	ALTER COLUMN a SET NOT NULL
,	ADD CONSTRAINT composite_nullability_check CHECK ((olda IS NULL OR olda.renamed IS NOT NULL) AND (a IS NULL OR a.renamed IS NOT NULL AND a.field2 IS NOT NULL))
;
-- updated: remove old struct --
ALTER TABLE tests
	DROP CONSTRAINT composite_nullability_check
,	ADD CONSTRAINT composite_nullability_check CHECK (a IS NULL OR a.renamed IS NOT NULL AND a.field2 IS NOT NULL)
;
ALTER TABLE tests DROP COLUMN olda;
DROP TYPE oldmystruct;
-- updated: finalize --

-- updated: cleanup schema changes --
DROP TABLE tests;
DROP TYPE mystruct;
DROP DOMAIN myint;