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
|
ALTER TABLE host ADD COLUMN total_children uint DEFAULT NULL;
ALTER TABLE host_state ADD COLUMN affects_children boolenum NOT NULL DEFAULT 'n';
ALTER TABLE host_state ALTER COLUMN affects_children DROP DEFAULT;
ALTER TABLE host_state ADD COLUMN is_sticky_acknowledgement boolenum NOT NULL DEFAULT 'n';
UPDATE host_state SET is_sticky_acknowledgement = 'y' WHERE is_acknowledged = 'sticky';
-- The USING clause used below to typecast is_acknowledged to boolenum doesn't apply to default value [^1]
-- of a column, so we need to drop the DEFAULT constraint and then recreate it after the typecast.
-- [^1]: https://www.postgresql.org/docs/9.6/sql-altertable.html#notes
ALTER TABLE host_state ALTER COLUMN is_acknowledged DROP DEFAULT;
ALTER TABLE host_state ALTER COLUMN is_acknowledged TYPE boolenum USING (
CASE is_acknowledged
WHEN 'y' THEN 'y'::boolenum
WHEN 'sticky' THEN 'y'::boolenum
ELSE 'n'::boolenum
END
);
ALTER TABLE host_state ALTER COLUMN is_acknowledged SET DEFAULT 'n';
ALTER TABLE service_state ADD COLUMN is_sticky_acknowledgement boolenum NOT NULL DEFAULT 'n';
UPDATE service_state SET is_sticky_acknowledgement = 'y' WHERE is_acknowledged = 'sticky';
ALTER TABLE service_state ALTER COLUMN is_acknowledged DROP DEFAULT; -- Same as above for host_state!
ALTER TABLE service_state ALTER COLUMN is_acknowledged TYPE boolenum USING (
CASE is_acknowledged
WHEN 'y' THEN 'y'::boolenum
WHEN 'sticky' THEN 'y'::boolenum
ELSE 'n'::boolenum
END
);
ALTER TABLE service_state ALTER COLUMN is_acknowledged SET DEFAULT 'n';
DROP TYPE acked;
ALTER TABLE service ADD COLUMN total_children uint DEFAULT NULL;
ALTER TABLE service_state ADD COLUMN affects_children boolenum NOT NULL DEFAULT 'n';
ALTER TABLE service_state ALTER COLUMN affects_children DROP DEFAULT;
CREATE TABLE redundancy_group (
id bytea20 NOT NULL,
environment_id bytea20 NOT NULL,
display_name text NOT NULL,
CONSTRAINT pk_redundancy_group PRIMARY KEY (id)
);
ALTER TABLE redundancy_group ALTER COLUMN id SET STORAGE PLAIN;
ALTER TABLE redundancy_group ALTER COLUMN environment_id SET STORAGE PLAIN;
COMMENT ON COLUMN redundancy_group.id IS 'sha1(name + all(member parent_name + timeperiod.name + states + ignore_soft_states))';
COMMENT ON COLUMN redundancy_group.environment_id IS 'environment.id';
CREATE TABLE redundancy_group_state (
id bytea20 NOT NULL,
environment_id bytea20 NOT NULL,
redundancy_group_id bytea20 NOT NULL,
failed boolenum NOT NULL,
is_reachable boolenum NOT NULL,
last_state_change biguint NOT NULL,
CONSTRAINT pk_redundancy_group_state PRIMARY KEY (id)
);
ALTER TABLE redundancy_group_state ALTER COLUMN id SET STORAGE PLAIN;
ALTER TABLE redundancy_group_state ALTER COLUMN environment_id SET STORAGE PLAIN;
ALTER TABLE redundancy_group_state ALTER COLUMN redundancy_group_id SET STORAGE PLAIN;
CREATE UNIQUE INDEX idx_redundancy_group_state_redundancy_group_id ON redundancy_group_state(redundancy_group_id);
COMMENT ON COLUMN redundancy_group_state.id IS 'redundancy_group.id';
COMMENT ON COLUMN redundancy_group_state.environment_id IS 'environment.id';
COMMENT ON COLUMN redundancy_group_state.redundancy_group_id IS 'redundancy_group.id';
CREATE TABLE dependency_node (
id bytea20 NOT NULL,
environment_id bytea20 NOT NULL,
host_id bytea20 DEFAULT NULL,
service_id bytea20 DEFAULT NULL,
redundancy_group_id bytea20 DEFAULT NULL,
CONSTRAINT pk_dependency_node PRIMARY KEY (id),
CONSTRAINT ck_dependency_node_either_checkable_or_redundancy_group_id CHECK (
CASE WHEN redundancy_group_id IS NULL THEN host_id IS NOT NULL ELSE host_id IS NULL AND service_id IS NULL END
)
);
ALTER TABLE dependency_node ALTER COLUMN id SET STORAGE PLAIN;
ALTER TABLE dependency_node ALTER COLUMN environment_id SET STORAGE PLAIN;
ALTER TABLE dependency_node ALTER COLUMN host_id SET STORAGE PLAIN;
ALTER TABLE dependency_node ALTER COLUMN service_id SET STORAGE PLAIN;
ALTER TABLE dependency_node ALTER COLUMN redundancy_group_id SET STORAGE PLAIN;
CREATE UNIQUE INDEX idx_dependency_node_host_service_redundancygroup_id ON dependency_node(host_id, service_id, redundancy_group_id);
COMMENT ON COLUMN dependency_node.id IS 'host.id|service.id|redundancy_group.id';
COMMENT ON COLUMN dependency_node.environment_id IS 'environment.id';
COMMENT ON COLUMN dependency_node.host_id IS 'host.id';
COMMENT ON COLUMN dependency_node.service_id IS 'service.id';
COMMENT ON COLUMN dependency_node.redundancy_group_id IS 'redundancy_group.id';
CREATE TABLE dependency_edge_state (
id bytea20 NOT NULL,
environment_id bytea20 NOT NULL,
failed boolenum NOT NULL,
CONSTRAINT pk_dependency_edge_state PRIMARY KEY (id)
);
ALTER TABLE dependency_edge_state ALTER COLUMN id SET STORAGE PLAIN;
ALTER TABLE dependency_edge_state ALTER COLUMN environment_id SET STORAGE PLAIN;
COMMENT ON COLUMN dependency_edge_state.id IS 'sha1([dependency_edge.from_node_id|parent_name + timeperiod.name + states + ignore_soft_states] + dependency_edge.to_node_id)';
COMMENT ON COLUMN dependency_edge_state.environment_id IS 'environment.id';
CREATE TABLE dependency_edge (
id bytea20 NOT NULL,
environment_id bytea20 NOT NULL,
from_node_id bytea20 NOT NULL,
to_node_id bytea20 NOT NULL,
dependency_edge_state_id bytea20 NOT NULL,
display_name text NOT NULL,
CONSTRAINT pk_dependency_edge PRIMARY KEY (id)
);
ALTER TABLE dependency_edge ALTER COLUMN id SET STORAGE PLAIN;
ALTER TABLE dependency_edge ALTER COLUMN environment_id SET STORAGE PLAIN;
ALTER TABLE dependency_edge ALTER COLUMN from_node_id SET STORAGE PLAIN;
ALTER TABLE dependency_edge ALTER COLUMN to_node_id SET STORAGE PLAIN;
ALTER TABLE dependency_edge ALTER COLUMN dependency_edge_state_id SET STORAGE PLAIN;
CREATE UNIQUE INDEX idx_dependency_edge_from_node_to_node_id ON dependency_edge(from_node_id, to_node_id);
COMMENT ON COLUMN dependency_edge.id IS 'sha1(from_node_id + to_node_id)';
COMMENT ON COLUMN dependency_edge.environment_id IS 'environment.id';
COMMENT ON COLUMN dependency_edge.from_node_id IS 'dependency_node.id';
COMMENT ON COLUMN dependency_edge.to_node_id IS 'dependency_node.id';
COMMENT ON COLUMN dependency_edge.dependency_edge_state_id IS 'sha1(dependency_edge_state.id)';
ALTER TABLE icingadb_instance ADD COLUMN icingadb_version varchar(255) NOT NULL DEFAULT 'unknown';
ALTER TABLE icingadb_instance ALTER COLUMN icingadb_version DROP DEFAULT;
INSERT INTO icingadb_schema (version, timestamp)
VALUES (5, extract(epoch from now()) * 1000);
|
