diff options
Diffstat (limited to 'schema/pgsql-legacy-changes')
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-10.sql | 20 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-11.sql | 20 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-2.sql | 2 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-3.sql | 21 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-4.sql | 20 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-5.sql | 20 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-6.sql | 20 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-7.sql | 20 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-8.sql | 20 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade-9.sql | 20 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade_1.sql | 13 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade_21.sql | 17 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade_22.sql | 55 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade_23.sql | 60 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade_34.sql | 189 | ||||
| -rw-r--r-- | schema/pgsql-legacy-changes/upgrade_35.sql | 2 |
16 files changed, 519 insertions, 0 deletions
diff --git a/schema/pgsql-legacy-changes/upgrade-10.sql b/schema/pgsql-legacy-changes/upgrade-10.sql new file mode 100644 index 0000000..4fd3cc1 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-10.sql @@ -0,0 +1,20 @@ +CREATE TABLE icinga_usergroup_inheritance ( + usergroup_id integer NOT NULL, + parent_usergroup_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (usergroup_id, parent_usergroup_id), + CONSTRAINT icinga_usergroup_inheritance_usergroup + FOREIGN KEY (usergroup_id) + REFERENCES icinga_usergroup (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_usergroup_inheritance_parent_usergroup + FOREIGN KEY (parent_usergroup_id) + REFERENCES icinga_usergroup (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX usergroup_inheritance_unique_order ON icinga_usergroup_inheritance (usergroup_id, weight); +CREATE INDEX usergroup_inheritance_usergroup ON icinga_usergroup_inheritance (usergroup_id); +CREATE INDEX usergroup_inheritance_usergroup_parent ON icinga_usergroup_inheritance (parent_usergroup_id); diff --git a/schema/pgsql-legacy-changes/upgrade-11.sql b/schema/pgsql-legacy-changes/upgrade-11.sql new file mode 100644 index 0000000..fce4e61 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-11.sql @@ -0,0 +1,20 @@ +CREATE TABLE icinga_endpoint_inheritance ( + endpoint_id integer NOT NULL, + parent_endpoint_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (endpoint_id, parent_endpoint_id), + CONSTRAINT icinga_endpoint_inheritance_endpoint + FOREIGN KEY (endpoint_id) + REFERENCES icinga_endpoint (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_endpoint_inheritance_parent_endpoint + FOREIGN KEY (parent_endpoint_id) + REFERENCES icinga_endpoint (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX endpoint_inheritance_unique_order ON icinga_endpoint_inheritance (endpoint_id, weight); +CREATE INDEX endpoint_inheritance_endpoint ON icinga_endpoint_inheritance (endpoint_id); +CREATE INDEX endpoint_inheritance_endpoint_parent ON icinga_endpoint_inheritance (parent_endpoint_id); diff --git a/schema/pgsql-legacy-changes/upgrade-2.sql b/schema/pgsql-legacy-changes/upgrade-2.sql new file mode 100644 index 0000000..9b73e52 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-2.sql @@ -0,0 +1,2 @@ +ALTER TABLE icinga_zone ADD is_global enum_boolean NOT NULL DEFAULT 'n'; + diff --git a/schema/pgsql-legacy-changes/upgrade-3.sql b/schema/pgsql-legacy-changes/upgrade-3.sql new file mode 100644 index 0000000..77cb345 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-3.sql @@ -0,0 +1,21 @@ +CREATE TABLE icinga_service_inheritance ( + service_id integer NOT NULL, + parent_service_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (service_id, parent_service_id), + CONSTRAINT icinga_service_inheritance_service + FOREIGN KEY (service_id) + REFERENCES icinga_service (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_service_inheritance_parent_service + FOREIGN KEY (parent_service_id) + REFERENCES icinga_service (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX service_inheritance_unique_order ON icinga_service_inheritance (service_id, weight); +CREATE INDEX service_inheritance_service ON icinga_service_inheritance (service_id); +CREATE INDEX service_inheritance_service_parent ON icinga_service_inheritance (parent_service_id); + diff --git a/schema/pgsql-legacy-changes/upgrade-4.sql b/schema/pgsql-legacy-changes/upgrade-4.sql new file mode 100644 index 0000000..7e81f1a --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-4.sql @@ -0,0 +1,20 @@ +CREATE TABLE icinga_user_inheritance ( + user_id integer NOT NULL, + parent_user_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (user_id, parent_user_id), + CONSTRAINT icinga_user_inheritance_user + FOREIGN KEY (user_id) + REFERENCES icinga_user (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_user_inheritance_parent_user + FOREIGN KEY (parent_user_id) + REFERENCES icinga_user (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX user_inheritance_unique_order ON icinga_user_inheritance (user_id, weight); +CREATE INDEX user_inheritance_user ON icinga_user_inheritance (user_id); +CREATE INDEX user_inheritance_user_parent ON icinga_user_inheritance (parent_user_id);
\ No newline at end of file diff --git a/schema/pgsql-legacy-changes/upgrade-5.sql b/schema/pgsql-legacy-changes/upgrade-5.sql new file mode 100644 index 0000000..2671177 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-5.sql @@ -0,0 +1,20 @@ +CREATE TABLE icinga_timeperiod_inheritance ( + timeperiod_id integer NOT NULL, + parent_timeperiod_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (timeperiod_id, parent_timeperiod_id), + CONSTRAINT icinga_timeperiod_inheritance_timeperiod + FOREIGN KEY (timeperiod_id) + REFERENCES icinga_timeperiod (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_timeperiod_inheritance_parent_timeperiod + FOREIGN KEY (parent_timeperiod_id) + REFERENCES icinga_timeperiod (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX timeperiod_inheritance_unique_order ON icinga_timeperiod_inheritance (timeperiod_id, weight); +CREATE INDEX timeperiod_inheritance_timeperiod ON icinga_timeperiod_inheritance (timeperiod_id); +CREATE INDEX timeperiod_inheritance_timeperiod_parent ON icinga_timeperiod_inheritance (parent_timeperiod_id);
\ No newline at end of file diff --git a/schema/pgsql-legacy-changes/upgrade-6.sql b/schema/pgsql-legacy-changes/upgrade-6.sql new file mode 100644 index 0000000..2bc32a8 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-6.sql @@ -0,0 +1,20 @@ +CREATE TABLE icinga_hostgroup_inheritance ( + hostgroup_id integer NOT NULL, + parent_hostgroup_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (hostgroup_id, parent_hostgroup_id), + CONSTRAINT icinga_hostgroup_inheritance_hostgroup + FOREIGN KEY (hostgroup_id) + REFERENCES icinga_hostgroup (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_hostgroup_inheritance_parent_hostgroup + FOREIGN KEY (parent_hostgroup_id) + REFERENCES icinga_hostgroup (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX hostgroup_inheritance_unique_order ON icinga_hostgroup_inheritance (hostgroup_id, weight); +CREATE INDEX hostgroup_inheritance_hostgroup ON icinga_hostgroup_inheritance (hostgroup_id); +CREATE INDEX hostgroup_inheritance_hostgroup_parent ON icinga_hostgroup_inheritance (parent_hostgroup_id);
\ No newline at end of file diff --git a/schema/pgsql-legacy-changes/upgrade-7.sql b/schema/pgsql-legacy-changes/upgrade-7.sql new file mode 100644 index 0000000..0d781f6 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-7.sql @@ -0,0 +1,20 @@ +CREATE TABLE icinga_servicegroup_inheritance ( + servicegroup_id integer NOT NULL, + parent_servicegroup_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (servicegroup_id, parent_servicegroup_id), + CONSTRAINT icinga_servicegroup_inheritance_servicegroup + FOREIGN KEY (servicegroup_id) + REFERENCES icinga_servicegroup (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_servicegroup_inheritance_parent_servicegroup + FOREIGN KEY (parent_servicegroup_id) + REFERENCES icinga_servicegroup (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX servicegroup_inheritance_unique_order ON icinga_servicegroup_inheritance (servicegroup_id, weight); +CREATE INDEX servicegroup_inheritance_servicegroup ON icinga_servicegroup_inheritance (servicegroup_id); +CREATE INDEX servicegroup_inheritance_servicegroup_parent ON icinga_servicegroup_inheritance (parent_servicegroup_id); diff --git a/schema/pgsql-legacy-changes/upgrade-8.sql b/schema/pgsql-legacy-changes/upgrade-8.sql new file mode 100644 index 0000000..61a9abe --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-8.sql @@ -0,0 +1,20 @@ +CREATE TABLE icinga_command_inheritance ( + command_id integer NOT NULL, + parent_command_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (command_id, parent_command_id), + CONSTRAINT icinga_command_inheritance_command + FOREIGN KEY (command_id) + REFERENCES icinga_command (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_command_inheritance_parent_command + FOREIGN KEY (parent_command_id) + REFERENCES icinga_command (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX command_inheritance_unique_order ON icinga_command_inheritance (command_id, weight); +CREATE INDEX command_inheritance_command ON icinga_command_inheritance (command_id); +CREATE INDEX command_inheritance_command_parent ON icinga_command_inheritance (parent_command_id); diff --git a/schema/pgsql-legacy-changes/upgrade-9.sql b/schema/pgsql-legacy-changes/upgrade-9.sql new file mode 100644 index 0000000..401a864 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade-9.sql @@ -0,0 +1,20 @@ +CREATE TABLE icinga_zone_inheritance ( + zone_id integer NOT NULL, + parent_zone_id integer NOT NULL, + weight integer DEFAULT NULL, + PRIMARY KEY (zone_id, parent_zone_id), + CONSTRAINT icinga_zone_inheritance_zone + FOREIGN KEY (zone_id) + REFERENCES icinga_zone (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_zone_inheritance_parent_zone + FOREIGN KEY (parent_zone_id) + REFERENCES icinga_zone (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX zone_inheritance_unique_order ON icinga_zone_inheritance (zone_id, weight); +CREATE INDEX zone_inheritance_zone ON icinga_zone_inheritance (zone_id); +CREATE INDEX zone_inheritance_zone_parent ON icinga_zone_inheritance (parent_zone_id); diff --git a/schema/pgsql-legacy-changes/upgrade_1.sql b/schema/pgsql-legacy-changes/upgrade_1.sql new file mode 100644 index 0000000..11921c7 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade_1.sql @@ -0,0 +1,13 @@ +ALTER TABLE icinga_hostgroup_parent DROP CONSTRAINT icinga_hostgroup_parent_parent; +ALTER TABLE icinga_hostgroup_parent ADD CONSTRAINT icinga_hostgroup_parent_parent + FOREIGN KEY (parent_hostgroup_id) + REFERENCES icinga_hostgroup (id) + ON DELETE RESTRICT + ON UPDATE CASCADE; + +ALTER TABLE icinga_usergroup_parent DROP CONSTRAINT icinga_usergroup_parent_parent; +ALTER TABLE icinga_usergroup_parent ADD CONSTRAINT icinga_usergroup_parent_parent + FOREIGN KEY (parent_usergroup_id) + REFERENCES icinga_usergroup (id) + ON DELETE RESTRICT + ON UPDATE CASCADE;
\ No newline at end of file diff --git a/schema/pgsql-legacy-changes/upgrade_21.sql b/schema/pgsql-legacy-changes/upgrade_21.sql new file mode 100644 index 0000000..1a4fc5a --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade_21.sql @@ -0,0 +1,17 @@ +DROP TABLE director_datalist_value; + +CREATE TABLE director_datalist_entry ( + list_id integer NOT NULL, + entry_name character varying(255) DEFAULT NULL, + entry_value text DEFAULT NULL, + format enum_property_format, + PRIMARY KEY (list_id, entry_name), + CONSTRAINT director_datalist_entry_datalist + FOREIGN KEY (list_id) + REFERENCES director_datalist (id) + ON DELETE CASCADE + ON UPDATE CASCADE +); + +CREATE INDEX datalist_entry_datalist ON director_datalist_entry (list_id); + diff --git a/schema/pgsql-legacy-changes/upgrade_22.sql b/schema/pgsql-legacy-changes/upgrade_22.sql new file mode 100644 index 0000000..d7aaf51 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade_22.sql @@ -0,0 +1,55 @@ +CREATE TABLE icinga_host_field ( + host_id integer NOT NULL, + fieldname character varying(64) NOT NULL, + caption character varying(255) NOT NULL, + datatype_id integer NOT NULL, +-- datatype_param? multiple ones? + default_value text DEFAULT NULL, + format enum_property_format, + PRIMARY KEY (host_id, fieldname), + CONSTRAINT icinga_host_field_host + FOREIGN KEY (host_id) + REFERENCES icinga_host (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_host_field_datatype + FOREIGN KEY (datatype_id) + REFERENCES director_datatype (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX host_field_key ON icinga_host_field (host_id, fieldname); +CREATE INDEX host_field_search_idx ON icinga_host_field (fieldname); +CREATE INDEX host_field_host ON icinga_host_field (host_id); +CREATE INDEX host_field_datatype ON icinga_host_field (datatype); + +COMMENT ON COLUMN icinga_host_field.host_id IS 'Makes only sense for templates'; + + +CREATE TABLE icinga_service_field ( + service_id integer NOT NULL, + fieldname character varying(64) NOT NULL, + caption character varying(255) NOT NULL, + datatype_id integer NOT NULL, +-- datatype_param? multiple ones? + default_value text DEFAULT NULL, + format enum_property_format, + PRIMARY KEY (service_id, fieldname), + CONSTRAINT icinga_service_field_service + FOREIGN KEY (service_id) + REFERENCES icinga_service (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_service_field_datatype + FOREIGN KEY datatype (datatype_id) + REFERENCES director_datatype (id) + ON DELETE RESTRICT + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX service_field_key ON icinga_service_field (service_id, fieldname); +CREATE INDEX service_field_search_idx ON icinga_service_field (fieldname); +CREATE INDEX service_field_service ON icinga_service_field (service_id); +CREATE INDEX service_field_datatype ON icinga_service_field (datatype); +COMMENT ON COLUMN icinga_service_field.service_id IS 'Makes only sense for templates'; diff --git a/schema/pgsql-legacy-changes/upgrade_23.sql b/schema/pgsql-legacy-changes/upgrade_23.sql new file mode 100644 index 0000000..9d02ca6 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade_23.sql @@ -0,0 +1,60 @@ +DROP TABLE director_datatype; + +CREATE TABLE director_datafield ( + id serial, + varname character varying(255) NOT NULL, + caption character varying(255) NOT NULL, + description text DEFAULT NULL, + datatype character varying(255) NOT NULL, +-- datatype_param? multiple ones? + format enum_property_format, + PRIMARY KEY (id) +); + +DROP TABLE icinga_host_field; + +CREATE TABLE icinga_host_field ( + host_id integer NOT NULL, + datafield_id integer NOT NULL, + is_required enum_boolean DEFAULT NULL, + PRIMARY KEY (host_id, datafield_id), + CONSTRAINT icinga_host_field_host + FOREIGN KEY (host_id) + REFERENCES icinga_host (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_host_field_datafield + FOREIGN KEY (datafield_id) + REFERENCES director_datafield (id) + ON DELETE CASCADE + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX host_field_key ON icinga_host_field (host_id, datafield_id); +CREATE INDEX host_field_host ON icinga_host_field (host_id); +CREATE INDEX host_field_datafield ON icinga_host_field (datafield_id); +COMMENT ON COLUMN icinga_host_field.host_id IS 'Makes only sense for templates'; + +DROP TABLE icinga_service_field; + +CREATE TABLE icinga_service_field ( + service_id integer NOT NULL, + datafield_id integer NOT NULL, + is_required enum_boolean DEFAULT NULL, + PRIMARY KEY (service_id, datafield_id), + CONSTRAINT icinga_service_field_service + FOREIGN KEY (service_id) + REFERENCES icinga_service (id) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT icinga_service_field_datafield + FOREIGN KEY (datafield_id) + REFERENCES director_datafield (id) + ON DELETE CASCADE + ON UPDATE CASCADE +); + +CREATE UNIQUE INDEX service_field_key ON icinga_service_field (service_id, datafield_id); +CREATE INDEX service_field_service ON icinga_service_field (service_id); +CREATE INDEX service_field_datafield ON icinga_service_field (datafield_id); +COMMENT ON COLUMN icinga_service_field.service_id IS 'Makes only sense for templates'; diff --git a/schema/pgsql-legacy-changes/upgrade_34.sql b/schema/pgsql-legacy-changes/upgrade_34.sql new file mode 100644 index 0000000..c8e57ab --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade_34.sql @@ -0,0 +1,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); diff --git a/schema/pgsql-legacy-changes/upgrade_35.sql b/schema/pgsql-legacy-changes/upgrade_35.sql new file mode 100644 index 0000000..b5e1294 --- /dev/null +++ b/schema/pgsql-legacy-changes/upgrade_35.sql @@ -0,0 +1,2 @@ +ALTER TABLE icinga_command_argument DROP COLUMN value_format, ADD COLUMN argument_format enum_property_format NOT NULL DEFAULT 'string'; + |