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
|
ALTER TABLE director_generated_file ALTER COLUMN content SET DEFAULT NULL;
ALTER TABLE icinga_host_field ALTER COLUMN is_required SET NOT NULL;
ALTER TABLE icinga_service_field ALTER COLUMN is_required SET NOT NULL;
CREATE TABLE import_source (
id serial,
source_name character varying(64) NOT NULL,
key_column character varying(64) NOT NULL,
provider_class character varying(72) NOT NULL,
PRIMARY KEY (id)
);
CREATE INDEX import_source_search_idx ON import_source (key_column);
CREATE TABLE import_source_setting (
source_id integer NOT NULL,
setting_name character varying(64) NOT NULL,
setting_value text NOT NULL,
PRIMARY KEY (source_id, setting_name),
CONSTRAINT import_source_settings_source
FOREIGN KEY (source_id)
REFERENCES import_source (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE INDEX import_source_setting_source ON import_source_setting (source_id);
CREATE TABLE imported_rowset (
checksum bytea CHECK(LENGTH(checksum) = 20),
PRIMARY KEY (checksum)
);
CREATE TABLE import_run (
id serial,
source_id integer NOT NULL,
rowset_checksum bytea CHECK(LENGTH(rowset_checksum) = 20),
start_time timestamp with time zone NOT NULL,
end_time timestamp with time zone NOT NULL,
succeeded enum_boolean DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT import_run_source
FOREIGN KEY (source_id)
REFERENCES import_source (id)
ON DELETE RESTRICT
ON UPDATE CASCADE,
CONSTRAINT import_run_rowset
FOREIGN KEY (rowset_checksum)
REFERENCES imported_rowset (checksum)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE INDEX import_run_import_source ON import_run (source_id);
CREATE INDEX import_run_rowset ON import_run (rowset_checksum);
CREATE TABLE imported_row (
checksum bytea CHECK(LENGTH(checksum) = 20),
object_name character varying(255) NOT NULL,
PRIMARY KEY (checksum)
);
COMMENT ON COLUMN imported_row.checksum IS 'sha1(object_name;property_checksum;...)';
CREATE TABLE imported_rowset_row (
rowset_checksum bytea CHECK(LENGTH(checksum) = 20),
row_checksum bytea CHECK(LENGTH(checksum) = 20),
PRIMARY KEY (rowset_checksum, row_checksum),
CONSTRAINT imported_rowset_row_rowset
FOREIGN KEY (rowset_checksum)
REFERENCES imported_rowset (checksum)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT imported_rowset_row_row
FOREIGN KEY (row_checksum)
REFERENCES imported_row (checksum)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE INDEX imported_rowset_row_rowset_checksum ON imported_rowset_row (rowset_checksum);
CREATE INDEX imported_rowset_row_row_checksum ON imported_rowset_row (row_checksum);
CREATE TABLE imported_property (
checksum bytea CHECK(LENGTH(checksum) = 20),
property_name character varying(64) NOT NULL,
property_value text NOT NULL,
format enum_property_format,
PRIMARY KEY (checksum)
);
CREATE INDEX imported_property_search_idx ON imported_property (property_name);
CREATE TABLE imported_row_property (
row_checksum bytea CHECK(LENGTH(row_checksum) = 20),
property_checksum bytea CHECK(LENGTH(property_checksum) = 20),
PRIMARY KEY (row_checksum, property_checksum),
CONSTRAINT imported_row_property_row
FOREIGN KEY (row_checksum)
REFERENCES imported_row (checksum)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT imported_row_property_property
FOREIGN KEY (property_checksum)
REFERENCES imported_property (checksum)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE INDEX imported_row_property_row_checksum ON imported_row_property (row_checksum);
CREATE INDEX imported_row_property_property_checksum ON imported_row_property (property_checksum);
CREATE TYPE enum_sync_rule_object_type AS ENUM('host', 'user');
CREATE TYPE enum_sync_rule_update_policy AS ENUM('merge', 'override', 'ignore');
CREATE TABLE sync_rule (
id serial,
rule_name character varying(255) NOT NULL,
object_type enum_sync_rule_object_type NOT NULL,
update_policy enum_sync_rule_update_policy NOT NULL,
purge_existing enum_boolean NOT NULL DEFAULT 'n',
filter_expression text DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TYPE enum_sync_property_merge_policy AS ENUM('override', 'merge');
CREATE TABLE sync_property (
id serial,
rule_id integer NOT NULL,
source_id integer NOT NULL,
source_expression character varying(255) NOT NULL,
destination_field character varying(64),
priority smallint NOT NULL,
filter_expression text DEFAULT NULL,
merge_policy enum_sync_property_merge_policy NOT NULL,
PRIMARY KEY (id),
CONSTRAINT sync_property_rule
FOREIGN KEY (rule_id)
REFERENCES sync_rule (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT sync_property_source
FOREIGN KEY (source_id)
REFERENCES import_source (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE INDEX sync_property_rule ON sync_property (rule_id);
CREATE INDEX sync_property_source ON sync_property (source_id);
CREATE TABLE import_row_modifier (
id serial,
property_id integer NOT NULL,
provider_class character varying(72) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE import_row_modifier_setting (
modifier_id integer NOT NULL,
setting_name character varying(64) NOT NULL,
setting_value text DEFAULT NULL,
PRIMARY KEY (modifier_id)
);
CREATE TABLE director_datafield_setting (
datafield_id integer NOT NULL,
setting_name character varying(64) NOT NULL,
setting_value text NOT NULL,
PRIMARY KEY (datafield_id, setting_name),
CONSTRAINT datafield_id_settings
FOREIGN KEY (datafield_id)
REFERENCES director_datafield (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE INDEX director_datafield_datafield ON director_datafield_setting (datafield_id);
|
