diff options
Diffstat (limited to 'lib/db_ido_pgsql/schema/upgrade')
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.0.2.sql | 17 | ||||
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.1.0.sql | 17 | ||||
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.2.0.sql | 21 | ||||
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.3.0.sql | 26 | ||||
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.4.0.sql | 185 | ||||
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.5.0.sql | 85 | ||||
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.6.0.sql | 161 | ||||
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.8.0.sql | 32 | ||||
| -rw-r--r-- | lib/db_ido_pgsql/schema/upgrade/2.8.1.sql | 19 |
9 files changed, 563 insertions, 0 deletions
diff --git a/lib/db_ido_pgsql/schema/upgrade/2.0.2.sql b/lib/db_ido_pgsql/schema/upgrade/2.0.2.sql new file mode 100644 index 0000000..60710ef --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.0.2.sql @@ -0,0 +1,17 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.0.2 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +UPDATE icinga_objects SET name2 = NULL WHERE name2 = ''; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.11.6'); + diff --git a/lib/db_ido_pgsql/schema/upgrade/2.1.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.1.0.sql new file mode 100644 index 0000000..a32ecea --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.1.0.sql @@ -0,0 +1,17 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.1.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +ALTER TABLE icinga_programstatus ADD COLUMN endpoint_name TEXT default NULL; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.11.7'); + diff --git a/lib/db_ido_pgsql/schema/upgrade/2.2.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.2.0.sql new file mode 100644 index 0000000..d105a34 --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.2.0.sql @@ -0,0 +1,21 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.2.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +ALTER TABLE icinga_programstatus ADD COLUMN program_version TEXT default NULL; + +ALTER TABLE icinga_customvariables ADD COLUMN is_json INTEGER default 0; +ALTER TABLE icinga_customvariablestatus ADD COLUMN is_json INTEGER default 0; + + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.12.0'); + diff --git a/lib/db_ido_pgsql/schema/upgrade/2.3.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.3.0.sql new file mode 100644 index 0000000..91764de --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.3.0.sql @@ -0,0 +1,26 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.3.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #7765 drop unique constraint +-- ----------------------------------------- + +ALTER TABLE icinga_servicedependencies DROP CONSTRAINT uq_servicedependencies; +ALTER TABLE icinga_hostdependencies DROP CONSTRAINT uq_hostdependencies; + +CREATE INDEX idx_servicedependencies ON icinga_servicedependencies(instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical); +CREATE INDEX idx_hostdependencies ON icinga_hostdependencies(instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable); + + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.13.0'); + diff --git a/lib/db_ido_pgsql/schema/upgrade/2.4.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.4.0.sql new file mode 100644 index 0000000..4a6e45e --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.4.0.sql @@ -0,0 +1,185 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.4.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #9027 Default timestamps lack time zone +-- ----------------------------------------- + +ALTER TABLE icinga_acknowledgements ALTER COLUMN entry_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_acknowledgements ALTER COLUMN end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_commenthistory ALTER COLUMN entry_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_commenthistory ALTER COLUMN comment_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_commenthistory ALTER COLUMN expiration_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_commenthistory ALTER COLUMN deletion_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_comments ALTER COLUMN entry_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_comments ALTER COLUMN comment_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_comments ALTER COLUMN expiration_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_conninfo ALTER COLUMN connect_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_conninfo ALTER COLUMN disconnect_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_conninfo ALTER COLUMN last_checkin_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_conninfo ALTER COLUMN data_start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_conninfo ALTER COLUMN data_end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_contactnotificationmethods ALTER COLUMN start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_contactnotificationmethods ALTER COLUMN end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_contactnotifications ALTER COLUMN start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_contactnotifications ALTER COLUMN end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_contactstatus ALTER COLUMN status_update_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_contactstatus ALTER COLUMN last_host_notification SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_contactstatus ALTER COLUMN last_service_notification SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_customvariablestatus ALTER COLUMN status_update_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_dbversion ALTER COLUMN create_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_dbversion ALTER COLUMN modify_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_downtimehistory ALTER COLUMN entry_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_downtimehistory ALTER COLUMN scheduled_start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_downtimehistory ALTER COLUMN scheduled_end_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_downtimehistory ALTER COLUMN actual_start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_downtimehistory ALTER COLUMN actual_end_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_downtimehistory ALTER COLUMN trigger_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_eventhandlers ALTER COLUMN start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_eventhandlers ALTER COLUMN end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_externalcommands ALTER COLUMN entry_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_flappinghistory ALTER COLUMN event_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_flappinghistory ALTER COLUMN comment_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_hostchecks ALTER COLUMN start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hostchecks ALTER COLUMN end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_hoststatus ALTER COLUMN status_update_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN last_check SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN next_check SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN last_state_change SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN last_hard_state_change SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN last_time_up SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN last_time_down SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN last_time_unreachable SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN last_notification SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_hoststatus ALTER COLUMN next_notification SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_logentries ALTER COLUMN logentry_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_logentries ALTER COLUMN entry_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_notifications ALTER COLUMN start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_notifications ALTER COLUMN end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_processevents ALTER COLUMN event_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_programstatus ALTER COLUMN status_update_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_programstatus ALTER COLUMN program_start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_programstatus ALTER COLUMN program_end_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_programstatus ALTER COLUMN last_command_check SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_programstatus ALTER COLUMN last_log_rotation SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_programstatus ALTER COLUMN disable_notif_expire_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_scheduleddowntime ALTER COLUMN entry_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_scheduleddowntime ALTER COLUMN scheduled_start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_scheduleddowntime ALTER COLUMN scheduled_end_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_scheduleddowntime ALTER COLUMN actual_start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_scheduleddowntime ALTER COLUMN trigger_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_servicechecks ALTER COLUMN start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicechecks ALTER COLUMN end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_servicestatus ALTER COLUMN status_update_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN last_check SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN next_check SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN last_state_change SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN last_hard_state_change SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN last_time_ok SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN last_time_warning SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN last_time_unknown SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN last_time_critical SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN last_notification SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_servicestatus ALTER COLUMN next_notification SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_statehistory ALTER COLUMN state_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_systemcommands ALTER COLUMN start_time SET DEFAULT '1970-01-01 00:00:00+00'; +ALTER TABLE icinga_systemcommands ALTER COLUMN end_time SET DEFAULT '1970-01-01 00:00:00+00'; + +ALTER TABLE icinga_endpointstatus ALTER COLUMN status_update_time SET DEFAULT '1970-01-01 00:00:00+00'; + +-- ----------------------------------------- +-- #9455 check_source data type +-- ----------------------------------------- + +ALTER TABLE icinga_statehistory ALTER COLUMN check_source TYPE TEXT; +ALTER TABLE icinga_statehistory ALTER COLUMN check_source SET default ''; + +-- ----------------------------------------- +-- #9286 zones table +-- ----------------------------------------- + +ALTER TABLE icinga_endpoints ADD COLUMN zone_object_id bigint default 0; +ALTER TABLE icinga_endpointstatus ADD COLUMN zone_object_id bigint default 0; + +CREATE TABLE icinga_zones ( + zone_id bigserial, + instance_id bigint default 0, + zone_object_id bigint default 0, + parent_zone_object_id bigint default 0, + config_type integer default 0, + is_global integer default 0, + CONSTRAINT PK_zone_id PRIMARY KEY (zone_id) , + CONSTRAINT UQ_zones UNIQUE (instance_id,config_type,zone_object_id) +) ; + +CREATE TABLE icinga_zonestatus ( + zonestatus_id bigserial, + instance_id bigint default 0, + zone_object_id bigint default 0, + parent_zone_object_id bigint default 0, + status_update_time timestamp with time zone default '1970-01-01 00:00:00+00', + CONSTRAINT PK_zonestatus_id PRIMARY KEY (zonestatus_id) , + CONSTRAINT UQ_zonestatus UNIQUE (zone_object_id) +) ; + +-- ----------------------------------------- +-- #10392 original attributes +-- ----------------------------------------- + +ALTER TABLE icinga_servicestatus ADD COLUMN original_attributes TEXT default NULL; +ALTER TABLE icinga_hoststatus ADD COLUMN original_attributes TEXT default NULL; + +-- ----------------------------------------- +-- #10436 deleted custom vars +-- ----------------------------------------- + +ALTER TABLE icinga_customvariables ADD COLUMN session_token INTEGER default NULL; +ALTER TABLE icinga_customvariablestatus ADD COLUMN session_token INTEGER default NULL; + +CREATE INDEX cv_session_del_idx ON icinga_customvariables (session_token); +CREATE INDEX cvs_session_del_idx ON icinga_customvariablestatus (session_token); + +-- ----------------------------------------- +-- #10431 comment/downtime name +-- ----------------------------------------- + +ALTER TABLE icinga_comments ADD COLUMN name TEXT default NULL; +ALTER TABLE icinga_commenthistory ADD COLUMN name TEXT default NULL; + +ALTER TABLE icinga_scheduleddowntime ADD COLUMN name TEXT default NULL; +ALTER TABLE icinga_downtimehistory ADD COLUMN name TEXT default NULL; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.14.0'); diff --git a/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql new file mode 100644 index 0000000..063a812 --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.5.0.sql @@ -0,0 +1,85 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.5.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #10069 IDO: check_source should not be a TEXT field +-- ----------------------------------------- + +ALTER TABLE icinga_hoststatus ALTER COLUMN check_source TYPE varchar(255); +ALTER TABLE icinga_servicestatus ALTER COLUMN check_source TYPE varchar(255); +ALTER TABLE icinga_statehistory ALTER COLUMN check_source TYPE varchar(255); + +-- ----------------------------------------- +-- #10070 +-- ----------------------------------------- + +CREATE INDEX idx_comments_object_id on icinga_comments(object_id); +CREATE INDEX idx_scheduleddowntime_object_id on icinga_scheduleddowntime(object_id); + +-- ----------------------------------------- +-- #10066 +-- ----------------------------------------- + +CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id); +CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id); + +CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id); +CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id); + +CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id); +CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id); + +CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id); +CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id); + +-- ----------------------------------------- +-- #12258 +-- ----------------------------------------- +ALTER TABLE icinga_comments ADD COLUMN session_token INTEGER default NULL; +ALTER TABLE icinga_scheduleddowntime ADD COLUMN session_token INTEGER default NULL; + +CREATE INDEX idx_comments_session_del ON icinga_comments (instance_id, session_token); +CREATE INDEX idx_downtimes_session_del ON icinga_scheduleddowntime (instance_id, session_token); + +-- ----------------------------------------- +-- #12107 +-- ----------------------------------------- +CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time); + +-- ----------------------------------------- +-- #12435 +-- ----------------------------------------- +ALTER TABLE icinga_commands ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_contactgroups ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_contacts ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_hostgroups ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_hosts ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_servicegroups ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_services ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_timeperiods ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_endpoints ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_zones ADD config_hash VARCHAR(64) DEFAULT NULL; + +ALTER TABLE icinga_customvariables DROP session_token; +ALTER TABLE icinga_customvariablestatus DROP session_token; + +CREATE INDEX idx_customvariables_object_id on icinga_customvariables(object_id); +CREATE INDEX idx_contactgroup_members_object_id on icinga_contactgroup_members(contact_object_id); +CREATE INDEX idx_hostgroup_members_object_id on icinga_hostgroup_members(host_object_id); +CREATE INDEX idx_servicegroup_members_object_id on icinga_servicegroup_members(service_object_id); +CREATE INDEX idx_servicedependencies_dependent_service_object_id on icinga_servicedependencies(dependent_service_object_id); +CREATE INDEX idx_hostdependencies_dependent_host_object_id on icinga_hostdependencies(dependent_host_object_id); +CREATE INDEX idx_service_contacts_service_id on icinga_service_contacts(service_id); +CREATE INDEX idx_host_contacts_host_id on icinga_host_contacts(host_id); + +-- ----------------------------------------- +-- set dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.14.1'); diff --git a/lib/db_ido_pgsql/schema/upgrade/2.6.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.6.0.sql new file mode 100644 index 0000000..aa538a6 --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.6.0.sql @@ -0,0 +1,161 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.6.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #13221 IDO: PostgreSQL: Don't use timestamp with timezone for unix timestamp columns +-- ----------------------------------------- + +DROP FUNCTION IF EXISTS from_unixtime(bigint); +CREATE FUNCTION from_unixtime(bigint) RETURNS timestamp AS $$ + SELECT to_timestamp($1) AT TIME ZONE 'UTC' AS result +$$ LANGUAGE sql; + +DROP FUNCTION IF EXISTS unix_timestamp(timestamp WITH TIME ZONE); +CREATE OR REPLACE FUNCTION unix_timestamp(timestamp) RETURNS bigint AS ' + SELECT CAST(EXTRACT(EPOCH FROM $1) AS bigint) AS result; +' LANGUAGE sql; + +ALTER TABLE icinga_acknowledgements + ALTER COLUMN entry_time DROP DEFAULT, ALTER COLUMN entry_time TYPE timestamp, + ALTER COLUMN end_time DROP DEFAULT, ALTER COLUMN end_time TYPE timestamp; + +ALTER TABLE icinga_commenthistory + ALTER COLUMN entry_time DROP DEFAULT, ALTER COLUMN entry_time TYPE timestamp, + ALTER COLUMN comment_time DROP DEFAULT, ALTER COLUMN comment_time TYPE timestamp, + ALTER COLUMN expiration_time DROP DEFAULT, ALTER COLUMN expiration_time TYPE timestamp, + ALTER COLUMN deletion_time DROP DEFAULT, ALTER COLUMN deletion_time TYPE timestamp; + +ALTER TABLE icinga_comments + ALTER COLUMN entry_time DROP DEFAULT, ALTER COLUMN entry_time TYPE timestamp, + ALTER COLUMN comment_time DROP DEFAULT, ALTER COLUMN comment_time TYPE timestamp, + ALTER COLUMN expiration_time DROP DEFAULT, ALTER COLUMN expiration_time TYPE timestamp; + +ALTER TABLE icinga_conninfo + ALTER COLUMN connect_time DROP DEFAULT, ALTER COLUMN connect_time TYPE timestamp, + ALTER COLUMN disconnect_time DROP DEFAULT, ALTER COLUMN disconnect_time TYPE timestamp, + ALTER COLUMN last_checkin_time DROP DEFAULT, ALTER COLUMN last_checkin_time TYPE timestamp, + ALTER COLUMN data_start_time DROP DEFAULT, ALTER COLUMN data_start_time TYPE timestamp, + ALTER COLUMN data_end_time DROP DEFAULT, ALTER COLUMN data_end_time TYPE timestamp; + +ALTER TABLE icinga_contactnotificationmethods + ALTER COLUMN start_time DROP DEFAULT, ALTER COLUMN start_time TYPE timestamp, + ALTER COLUMN end_time DROP DEFAULT, ALTER COLUMN end_time TYPE timestamp; + +ALTER TABLE icinga_contactnotifications + ALTER COLUMN start_time DROP DEFAULT, ALTER COLUMN start_time TYPE timestamp, + ALTER COLUMN end_time DROP DEFAULT, ALTER COLUMN end_time TYPE timestamp; + +ALTER TABLE icinga_contactstatus + ALTER COLUMN status_update_time DROP DEFAULT, ALTER COLUMN status_update_time TYPE timestamp, + ALTER COLUMN last_host_notification DROP DEFAULT, ALTER COLUMN last_host_notification TYPE timestamp, + ALTER COLUMN last_service_notification DROP DEFAULT, ALTER COLUMN last_service_notification TYPE timestamp; + +ALTER TABLE icinga_customvariablestatus + ALTER COLUMN status_update_time DROP DEFAULT, ALTER COLUMN status_update_time TYPE timestamp; + +ALTER TABLE icinga_dbversion + ALTER COLUMN create_time DROP DEFAULT, ALTER COLUMN create_time TYPE timestamp, + ALTER COLUMN modify_time DROP DEFAULT, ALTER COLUMN modify_time TYPE timestamp; + +ALTER TABLE icinga_downtimehistory + ALTER COLUMN entry_time DROP DEFAULT, ALTER COLUMN entry_time TYPE timestamp, + ALTER COLUMN scheduled_start_time DROP DEFAULT, ALTER COLUMN scheduled_start_time TYPE timestamp, + ALTER COLUMN scheduled_end_time DROP DEFAULT, ALTER COLUMN scheduled_end_time TYPE timestamp, + ALTER COLUMN actual_start_time DROP DEFAULT, ALTER COLUMN actual_start_time TYPE timestamp, + ALTER COLUMN actual_end_time DROP DEFAULT, ALTER COLUMN actual_end_time TYPE timestamp, + ALTER COLUMN trigger_time DROP DEFAULT, ALTER COLUMN trigger_time TYPE timestamp; + +ALTER TABLE icinga_eventhandlers + ALTER COLUMN start_time DROP DEFAULT, ALTER COLUMN start_time TYPE timestamp, + ALTER COLUMN end_time DROP DEFAULT, ALTER COLUMN end_time TYPE timestamp; + +ALTER TABLE icinga_externalcommands + ALTER COLUMN entry_time DROP DEFAULT, ALTER COLUMN entry_time TYPE timestamp; + +ALTER TABLE icinga_flappinghistory + ALTER COLUMN event_time DROP DEFAULT, ALTER COLUMN event_time TYPE timestamp, + ALTER COLUMN comment_time DROP DEFAULT, ALTER COLUMN comment_time TYPE timestamp; + +ALTER TABLE icinga_hostchecks + ALTER COLUMN start_time DROP DEFAULT, ALTER COLUMN start_time TYPE timestamp, + ALTER COLUMN end_time DROP DEFAULT, ALTER COLUMN end_time TYPE timestamp; + +ALTER TABLE icinga_hoststatus + ALTER COLUMN status_update_time DROP DEFAULT, ALTER COLUMN status_update_time TYPE timestamp, + ALTER COLUMN last_check DROP DEFAULT, ALTER COLUMN last_check TYPE timestamp, + ALTER COLUMN next_check DROP DEFAULT, ALTER COLUMN next_check TYPE timestamp, + ALTER COLUMN last_state_change DROP DEFAULT, ALTER COLUMN last_state_change TYPE timestamp, + ALTER COLUMN last_hard_state_change DROP DEFAULT, ALTER COLUMN last_hard_state_change TYPE timestamp, + ALTER COLUMN last_time_up DROP DEFAULT, ALTER COLUMN last_time_up TYPE timestamp, + ALTER COLUMN last_time_down DROP DEFAULT, ALTER COLUMN last_time_down TYPE timestamp, + ALTER COLUMN last_time_unreachable DROP DEFAULT, ALTER COLUMN last_time_unreachable TYPE timestamp, + ALTER COLUMN last_notification DROP DEFAULT, ALTER COLUMN last_notification TYPE timestamp, + ALTER COLUMN next_notification DROP DEFAULT, ALTER COLUMN next_notification TYPE timestamp; + +ALTER TABLE icinga_logentries + ALTER COLUMN logentry_time DROP DEFAULT, ALTER COLUMN logentry_time TYPE timestamp, + ALTER COLUMN entry_time DROP DEFAULT, ALTER COLUMN entry_time TYPE timestamp; + +ALTER TABLE icinga_notifications + ALTER COLUMN start_time DROP DEFAULT, ALTER COLUMN start_time TYPE timestamp, + ALTER COLUMN end_time DROP DEFAULT, ALTER COLUMN end_time TYPE timestamp; + +ALTER TABLE icinga_processevents + ALTER COLUMN event_time DROP DEFAULT, ALTER COLUMN event_time TYPE timestamp; + +ALTER TABLE icinga_programstatus + ALTER COLUMN status_update_time DROP DEFAULT, ALTER COLUMN status_update_time TYPE timestamp, + ALTER COLUMN program_start_time DROP DEFAULT, ALTER COLUMN program_start_time TYPE timestamp, + ALTER COLUMN program_end_time DROP DEFAULT, ALTER COLUMN program_end_time TYPE timestamp, + ALTER COLUMN last_command_check DROP DEFAULT, ALTER COLUMN last_command_check TYPE timestamp, + ALTER COLUMN last_log_rotation DROP DEFAULT, ALTER COLUMN last_log_rotation TYPE timestamp, + ALTER COLUMN disable_notif_expire_time DROP DEFAULT, ALTER COLUMN disable_notif_expire_time TYPE timestamp; + +ALTER TABLE icinga_scheduleddowntime + ALTER COLUMN entry_time DROP DEFAULT, ALTER COLUMN entry_time TYPE timestamp, + ALTER COLUMN scheduled_start_time DROP DEFAULT, ALTER COLUMN scheduled_start_time TYPE timestamp, + ALTER COLUMN scheduled_end_time DROP DEFAULT, ALTER COLUMN scheduled_end_time TYPE timestamp, + ALTER COLUMN actual_start_time DROP DEFAULT, ALTER COLUMN actual_start_time TYPE timestamp, + ALTER COLUMN trigger_time DROP DEFAULT, ALTER COLUMN trigger_time TYPE timestamp; + +ALTER TABLE icinga_servicechecks + ALTER COLUMN start_time DROP DEFAULT, ALTER COLUMN start_time TYPE timestamp, + ALTER COLUMN end_time DROP DEFAULT, ALTER COLUMN end_time TYPE timestamp; + +ALTER TABLE icinga_servicestatus + ALTER COLUMN status_update_time DROP DEFAULT, ALTER COLUMN status_update_time TYPE timestamp, + ALTER COLUMN last_check DROP DEFAULT, ALTER COLUMN last_check TYPE timestamp, + ALTER COLUMN next_check DROP DEFAULT, ALTER COLUMN next_check TYPE timestamp, + ALTER COLUMN last_state_change DROP DEFAULT, ALTER COLUMN last_state_change TYPE timestamp, + ALTER COLUMN last_hard_state_change DROP DEFAULT, ALTER COLUMN last_hard_state_change TYPE timestamp, + ALTER COLUMN last_time_ok DROP DEFAULT, ALTER COLUMN last_time_ok TYPE timestamp, + ALTER COLUMN last_time_warning DROP DEFAULT, ALTER COLUMN last_time_warning TYPE timestamp, + ALTER COLUMN last_time_unknown DROP DEFAULT, ALTER COLUMN last_time_unknown TYPE timestamp, + ALTER COLUMN last_time_critical DROP DEFAULT, ALTER COLUMN last_time_critical TYPE timestamp, + ALTER COLUMN last_notification DROP DEFAULT, ALTER COLUMN last_notification TYPE timestamp, + ALTER COLUMN next_notification DROP DEFAULT, ALTER COLUMN next_notification TYPE timestamp; + +ALTER TABLE icinga_statehistory + ALTER COLUMN state_time DROP DEFAULT, ALTER COLUMN state_time TYPE timestamp; + +ALTER TABLE icinga_systemcommands + ALTER COLUMN start_time DROP DEFAULT, ALTER COLUMN start_time TYPE timestamp, + ALTER COLUMN end_time DROP DEFAULT, ALTER COLUMN end_time TYPE timestamp; + +ALTER TABLE icinga_endpointstatus + ALTER COLUMN status_update_time DROP DEFAULT, ALTER COLUMN status_update_time TYPE timestamp; + +ALTER TABLE icinga_zonestatus + ALTER COLUMN status_update_time DROP DEFAULT, ALTER COLUMN status_update_time TYPE timestamp; + +-- ----------------------------------------- +-- set dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.14.2'); diff --git a/lib/db_ido_pgsql/schema/upgrade/2.8.0.sql b/lib/db_ido_pgsql/schema/upgrade/2.8.0.sql new file mode 100644 index 0000000..31ab324 --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.8.0.sql @@ -0,0 +1,32 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.8.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +ALTER TABLE icinga_downtimehistory DROP CONSTRAINT IF EXISTS UQ_downtimehistory; +ALTER TABLE icinga_scheduleddowntime DROP CONSTRAINT IF EXISTS UQ_scheduleddowntime; +ALTER TABLE icinga_commenthistory DROP CONSTRAINT IF EXISTS UQ_commenthistory; +ALTER TABLE icinga_comments DROP CONSTRAINT IF EXISTS UQ_comments; + +-- ----------------------------------------- +-- #5458 IDO: Improve downtime removal/cancel +-- ----------------------------------------- + +CREATE INDEX idx_downtimehistory_remove ON icinga_downtimehistory (object_id, entry_time, scheduled_start_time, scheduled_end_time); +CREATE INDEX idx_scheduleddowntime_remove ON icinga_scheduleddowntime (object_id, entry_time, scheduled_start_time, scheduled_end_time); + +-- ----------------------------------------- +-- #5492 +-- ----------------------------------------- + +CREATE INDEX idx_commenthistory_remove ON icinga_commenthistory (object_id, entry_time); +CREATE INDEX idx_comments_remove ON icinga_comments (object_id, entry_time); +-- ----------------------------------------- +-- set dbversion +-- ----------------------------------------- + +SELECT updatedbversion('1.14.3'); diff --git a/lib/db_ido_pgsql/schema/upgrade/2.8.1.sql b/lib/db_ido_pgsql/schema/upgrade/2.8.1.sql new file mode 100644 index 0000000..05202c0 --- /dev/null +++ b/lib/db_ido_pgsql/schema/upgrade/2.8.1.sql @@ -0,0 +1,19 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.8.1 (fix for fresh 2.8.0 installation only) +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +ALTER TABLE icinga_downtimehistory DROP CONSTRAINT IF EXISTS UQ_downtimehistory; +ALTER TABLE icinga_scheduleddowntime DROP CONSTRAINT IF EXISTS UQ_scheduleddowntime; +ALTER TABLE icinga_commenthistory DROP CONSTRAINT IF EXISTS UQ_commenthistory; +ALTER TABLE icinga_comments DROP CONSTRAINT IF EXISTS UQ_comments; + +-- ----------------------------------------- +-- set dbversion (same as 2.8.0) +-- ----------------------------------------- + +SELECT updatedbversion('1.14.3'); |