diff options
Diffstat (limited to 'lib/db_ido_mysql/schema/upgrade')
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.0.2.sql | 20 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.1.0.sql | 17 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.11.0.sql | 89 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.12.7.sql | 15 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.13.0.sql | 23 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.13.3.sql | 15 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.2.0.sql | 23 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.3.0.sql | 26 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.4.0.sql | 75 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.5.0.sql | 103 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.6.0.sql | 151 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.8.0.sql | 81 | ||||
| -rw-r--r-- | lib/db_ido_mysql/schema/upgrade/2.8.1.sql | 67 |
13 files changed, 705 insertions, 0 deletions
diff --git a/lib/db_ido_mysql/schema/upgrade/2.0.2.sql b/lib/db_ido_mysql/schema/upgrade/2.0.2.sql new file mode 100644 index 0000000..c622ae9 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.0.2.sql @@ -0,0 +1,20 @@ +-- ----------------------------------------- +-- 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 = ''; + +ALTER TABLE `icinga_customvariables` MODIFY COLUMN `varname` varchar(255) character set latin1 collate latin1_general_cs default NULL; +ALTER TABLE `icinga_customvariablestatus` MODIFY COLUMN `varname` varchar(255) character set latin1 collate latin1_general_cs default NULL; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.11.6', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.11.6', modify_time=NOW(); + diff --git a/lib/db_ido_mysql/schema/upgrade/2.1.0.sql b/lib/db_ido_mysql/schema/upgrade/2.1.0.sql new file mode 100644 index 0000000..7bbed72 --- /dev/null +++ b/lib/db_ido_mysql/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` varchar(255) character set latin1 collate latin1_general_cs default NULL; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.11.7', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.11.7', modify_time=NOW(); + diff --git a/lib/db_ido_mysql/schema/upgrade/2.11.0.sql b/lib/db_ido_mysql/schema/upgrade/2.11.0.sql new file mode 100644 index 0000000..bafa93f --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.11.0.sql @@ -0,0 +1,89 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.11.0 +-- +-- ----------------------------------------- +-- Copyright (c) 2019 Icinga Development Team (https://icinga.com/) +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- -------------------------------------------------------- +-- Helper functions and procedures for DROP INDEX IF EXISTS +-- -------------------------------------------------------- + +DELIMITER // +DROP FUNCTION IF EXISTS ido_index_exists // +CREATE FUNCTION ido_index_exists( + f_table_name varchar(64), + f_index_name varchar(64) +) + RETURNS BOOL + DETERMINISTIC + READS SQL DATA + BEGIN + DECLARE index_exists BOOL DEFAULT FALSE; + SELECT EXISTS ( + SELECT 1 + FROM information_schema.statistics + WHERE table_schema = SCHEMA() + AND table_name = f_table_name + AND index_name = f_index_name + ) INTO index_exists; + RETURN index_exists; + END // + +DROP PROCEDURE IF EXISTS ido_drop_index_if_exists // +CREATE PROCEDURE ido_drop_index_if_exists ( + IN p_table_name varchar(64), + IN p_index_name varchar(64) +) + DETERMINISTIC + MODIFIES SQL DATA + BEGIN + IF ido_index_exists(p_table_name, p_index_name) + THEN + SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`'); + PREPARE stmt FROM @ido_drop_index_sql; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + SET @ido_drop_index_sql = NULL; + END IF; + END // +DELIMITER ; + +CALL ido_drop_index_if_exists('icinga_commands', 'commands_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_comments', 'idx_comments_object_id'); +CALL ido_drop_index_if_exists('icinga_comments', 'comments_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_configfiles', 'configfiles_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_contactgroups', 'contactgroups_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_contacts', 'contacts_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_customvariables', 'idx_customvariables_object_id'); +CALL ido_drop_index_if_exists('icinga_eventhandlers', 'eventhandlers_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hostdependencies', 'hostdependencies_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hostescalations', 'hostesc_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hostescalation_contacts', 'hostesc_contacts_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hostgroups', 'hostgroups_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hosts', 'host_object_id'); +CALL ido_drop_index_if_exists('icinga_hosts', 'hosts_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_objects', 'objects_objtype_id_idx'); +CALL ido_drop_index_if_exists('icinga_programstatus', 'programstatus_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_runtimevariables', 'runtimevariables_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'scheduleddowntime_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'idx_scheduleddowntime_object_id'); +CALL ido_drop_index_if_exists('icinga_serviceescalations', 'serviceesc_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_serviceescalation_contacts', 'serviceesc_contacts_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_servicegroups', 'servicegroups_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_services', 'services_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_services', 'service_object_id'); +CALL ido_drop_index_if_exists('icinga_systemcommands', 'systemcommands_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_timeperiods', 'timeperiods_i_id_idx'); + +DROP FUNCTION ido_index_exists; +DROP PROCEDURE ido_drop_index_if_exists; + +-- ----------------------------------------- +-- set dbversion (same as 2.11.0) +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.0', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.12.7.sql b/lib/db_ido_mysql/schema/upgrade/2.12.7.sql new file mode 100644 index 0000000..6319b37 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.12.7.sql @@ -0,0 +1,15 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.12.7 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- ------------- +-- set dbversion +-- ------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.0', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.13.0.sql b/lib/db_ido_mysql/schema/upgrade/2.13.0.sql new file mode 100644 index 0000000..462be6f --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.13.0.sql @@ -0,0 +1,23 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.13.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- ---------------------------------------- +-- #7472 Support hosts with >128 characters +-- ---------------------------------------- + +ALTER TABLE icinga_objects + MODIFY COLUMN name1 varchar(255) character set latin1 collate latin1_general_cs default '', + MODIFY COLUMN name2 varchar(255) character set latin1 collate latin1_general_cs default NULL; + +-- ------------- +-- set dbversion +-- ------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.13.3.sql b/lib/db_ido_mysql/schema/upgrade/2.13.3.sql new file mode 100644 index 0000000..577eb0a --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.13.3.sql @@ -0,0 +1,15 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.13.3 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- ------------- +-- set dbversion +-- ------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.2.0.sql b/lib/db_ido_mysql/schema/upgrade/2.2.0.sql new file mode 100644 index 0000000..22a6115 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.2.0.sql @@ -0,0 +1,23 @@ +-- ----------------------------------------- +-- 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` varchar(64) character set latin1 collate latin1_general_cs default NULL; + +ALTER TABLE icinga_contacts MODIFY alias TEXT character set latin1; +ALTER TABLE icinga_hosts MODIFY alias TEXT character set latin1; + +ALTER TABLE icinga_customvariables ADD COLUMN is_json smallint default 0; +ALTER TABLE icinga_customvariablestatus ADD COLUMN is_json smallint default 0; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.12.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.12.0', modify_time=NOW(); + diff --git a/lib/db_ido_mysql/schema/upgrade/2.3.0.sql b/lib/db_ido_mysql/schema/upgrade/2.3.0.sql new file mode 100644 index 0000000..f2fe463 --- /dev/null +++ b/lib/db_ido_mysql/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 KEY instance_id; +ALTER TABLE icinga_hostdependencies DROP KEY instance_id; + +ALTER TABLE icinga_servicedependencies ADD KEY instance_id (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); +ALTER TABLE icinga_hostdependencies ADD KEY instance_id (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 +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.13.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.13.0', modify_time=NOW(); + diff --git a/lib/db_ido_mysql/schema/upgrade/2.4.0.sql b/lib/db_ido_mysql/schema/upgrade/2.4.0.sql new file mode 100644 index 0000000..f6803f7 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.4.0.sql @@ -0,0 +1,75 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.4.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #9286 - zone tables +-- ----------------------------------------- + +ALTER TABLE icinga_endpoints ADD COLUMN zone_object_id bigint(20) unsigned DEFAULT '0'; +ALTER TABLE icinga_endpointstatus ADD COLUMN zone_object_id bigint(20) unsigned DEFAULT '0'; + +CREATE TABLE IF NOT EXISTS icinga_zones ( + zone_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + zone_object_id bigint(20) unsigned DEFAULT '0', + config_type smallint(6) DEFAULT '0', + parent_zone_object_id bigint(20) unsigned DEFAULT '0', + is_global smallint(6), + PRIMARY KEY (zone_id) +) ENGINE=InnoDB COMMENT='Zone configuration'; + +CREATE TABLE IF NOT EXISTS icinga_zonestatus ( + zonestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + zone_object_id bigint(20) unsigned DEFAULT '0', + status_update_time timestamp NOT NULL, + parent_zone_object_id bigint(20) unsigned DEFAULT '0', + PRIMARY KEY (zonestatus_id) +) ENGINE=InnoDB COMMENT='Zone status'; + + +-- ----------------------------------------- +-- #9576 - freshness_threshold +-- ----------------------------------------- + +ALTER TABLE icinga_services MODIFY freshness_threshold int; +ALTER TABLE icinga_hosts MODIFY freshness_threshold int; + +-- ----------------------------------------- +-- #10392 - original attributes +-- ----------------------------------------- + +ALTER TABLE icinga_servicestatus ADD COLUMN original_attributes TEXT character set latin1 default NULL; +ALTER TABLE icinga_hoststatus ADD COLUMN original_attributes TEXT character set latin1 default NULL; + +-- ----------------------------------------- +-- #10436 deleted custom vars +-- ----------------------------------------- + +ALTER TABLE icinga_customvariables ADD COLUMN session_token int default NULL; +ALTER TABLE icinga_customvariablestatus ADD COLUMN session_token int 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 character set latin1 default NULL; +ALTER TABLE icinga_commenthistory ADD COLUMN name TEXT character set latin1 default NULL; + +ALTER TABLE icinga_scheduleddowntime ADD COLUMN name TEXT character set latin1 default NULL; +ALTER TABLE icinga_downtimehistory ADD COLUMN name TEXT character set latin1 default NULL; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.0', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.5.0.sql b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql new file mode 100644 index 0000000..d5714a0 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql @@ -0,0 +1,103 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.5.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- ----------------------------------------- +-- #10069 IDO: check_source should not be a TEXT field +-- ----------------------------------------- + +ALTER TABLE icinga_hoststatus MODIFY COLUMN check_source varchar(255) character set latin1 default ''; +ALTER TABLE icinga_servicestatus MODIFY COLUMN check_source varchar(255) character set latin1 default ''; + +-- ----------------------------------------- +-- #10070 +-- ----------------------------------------- + +CREATE INDEX idx_comments_object_id on icinga_comments(object_id); +CREATE INDEX idx_scheduleddowntime_object_id on icinga_scheduleddowntime(object_id); + +-- ----------------------------------------- +-- #11962 +-- ----------------------------------------- + +ALTER TABLE icinga_hoststatus MODIFY COLUMN current_notification_number int unsigned default 0; +ALTER TABLE icinga_servicestatus MODIFY COLUMN current_notification_number int unsigned default 0; + +-- ----------------------------------------- +-- #10061 +-- ----------------------------------------- + +ALTER TABLE icinga_contactgroups MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_contacts MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_hostgroups MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_hosts MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_servicegroups MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_timeperiods MODIFY COLUMN alias varchar(255) character set latin1 default ''; + +-- ----------------------------------------- +-- #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); + +-- ----------------------------------------- +-- #12107 +-- ----------------------------------------- +CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time); + +-- ----------------------------------------- +-- #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); + +-- ----------------------------------------- +-- #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 +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.1', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.6.0.sql b/lib/db_ido_mysql/schema/upgrade/2.6.0.sql new file mode 100644 index 0000000..33dd780 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.6.0.sql @@ -0,0 +1,151 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.6.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #10502 IDO: Support NO_ZERO_DATE and NO_ZERO_IN_DATE SQL modes +-- ----------------------------------------- + +ALTER TABLE icinga_acknowledgements + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_commenthistory + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN comment_time timestamp NULL, + MODIFY COLUMN expiration_time timestamp NULL, + MODIFY COLUMN deletion_time timestamp NULL; + +ALTER TABLE icinga_comments + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN comment_time timestamp NULL, + MODIFY COLUMN expiration_time timestamp NULL; + +ALTER TABLE icinga_conninfo + MODIFY COLUMN connect_time timestamp NULL, + MODIFY COLUMN disconnect_time timestamp NULL, + MODIFY COLUMN last_checkin_time timestamp NULL, + MODIFY COLUMN data_start_time timestamp NULL, + MODIFY COLUMN data_end_time timestamp NULL; + +ALTER TABLE icinga_contactnotificationmethods + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_contactnotifications + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_contactstatus + MODIFY COLUMN status_update_time timestamp NULL, + MODIFY COLUMN last_host_notification timestamp NULL, + MODIFY COLUMN last_service_notification timestamp NULL; + +ALTER TABLE icinga_customvariablestatus + MODIFY COLUMN status_update_time timestamp NULL; + +ALTER TABLE icinga_dbversion + MODIFY COLUMN create_time timestamp NULL, + MODIFY COLUMN modify_time timestamp NULL; + +ALTER TABLE icinga_downtimehistory + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN scheduled_start_time timestamp NULL, + MODIFY COLUMN scheduled_end_time timestamp NULL, + MODIFY COLUMN actual_start_time timestamp NULL, + MODIFY COLUMN actual_end_time timestamp NULL, + MODIFY COLUMN trigger_time timestamp NULL; + +ALTER TABLE icinga_eventhandlers + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_externalcommands + MODIFY COLUMN entry_time timestamp NULL; + +ALTER TABLE icinga_flappinghistory + MODIFY COLUMN event_time timestamp NULL, + MODIFY COLUMN comment_time timestamp NULL; + +ALTER TABLE icinga_hostchecks + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_hoststatus + MODIFY COLUMN status_update_time timestamp NULL, + MODIFY COLUMN last_check timestamp NULL, + MODIFY COLUMN next_check timestamp NULL, + MODIFY COLUMN last_state_change timestamp NULL, + MODIFY COLUMN last_hard_state_change timestamp NULL, + MODIFY COLUMN last_time_up timestamp NULL, + MODIFY COLUMN last_time_down timestamp NULL, + MODIFY COLUMN last_time_unreachable timestamp NULL, + MODIFY COLUMN last_notification timestamp NULL, + MODIFY COLUMN next_notification timestamp NULL; + +ALTER TABLE icinga_logentries + MODIFY COLUMN logentry_time timestamp NULL, + MODIFY COLUMN entry_time timestamp NULL; + +ALTER TABLE icinga_notifications + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_processevents + MODIFY COLUMN event_time timestamp NULL; + +ALTER TABLE icinga_programstatus + MODIFY COLUMN status_update_time timestamp NULL, + MODIFY COLUMN program_start_time timestamp NULL, + MODIFY COLUMN program_end_time timestamp NULL, + MODIFY COLUMN last_command_check timestamp NULL, + MODIFY COLUMN last_log_rotation timestamp NULL, + MODIFY COLUMN disable_notif_expire_time timestamp NULL; + +ALTER TABLE icinga_scheduleddowntime + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN scheduled_start_time timestamp NULL, + MODIFY COLUMN scheduled_end_time timestamp NULL, + MODIFY COLUMN actual_start_time timestamp NULL, + MODIFY COLUMN trigger_time timestamp NULL; + +ALTER TABLE icinga_servicechecks + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_servicestatus + MODIFY COLUMN status_update_time timestamp NULL, + MODIFY COLUMN last_check timestamp NULL, + MODIFY COLUMN next_check timestamp NULL, + MODIFY COLUMN last_state_change timestamp NULL, + MODIFY COLUMN last_hard_state_change timestamp NULL, + MODIFY COLUMN last_time_ok timestamp NULL, + MODIFY COLUMN last_time_warning timestamp NULL, + MODIFY COLUMN last_time_unknown timestamp NULL, + MODIFY COLUMN last_time_critical timestamp NULL, + MODIFY COLUMN last_notification timestamp NULL, + MODIFY COLUMN next_notification timestamp NULL; + +ALTER TABLE icinga_statehistory + MODIFY COLUMN state_time timestamp NULL; + +ALTER TABLE icinga_systemcommands + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_endpointstatus + MODIFY COLUMN status_update_time timestamp NULL; + +ALTER TABLE icinga_zonestatus + MODIFY COLUMN status_update_time timestamp NULL; + +-- ----------------------------------------- +-- set dbversion +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.2', NOW(), NOW()) +ON DUPLICATE KEY UPDATE version='1.14.2', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.8.0.sql b/lib/db_ido_mysql/schema/upgrade/2.8.0.sql new file mode 100644 index 0000000..8d511a7 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.8.0.sql @@ -0,0 +1,81 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.8.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- -------------------------------------------------------- +-- Helper functions and procedures for DROP INDEX IF EXISTS +-- -------------------------------------------------------- + +DELIMITER // +DROP FUNCTION IF EXISTS ido_index_exists // +CREATE FUNCTION ido_index_exists( + f_table_name varchar(64), + f_index_name varchar(64) +) + RETURNS BOOL + DETERMINISTIC + READS SQL DATA + BEGIN + DECLARE index_exists BOOL DEFAULT FALSE; + SELECT EXISTS ( + SELECT 1 + FROM information_schema.statistics + WHERE table_schema = SCHEMA() + AND table_name = f_table_name + AND index_name = f_index_name + ) INTO index_exists; + RETURN index_exists; + END // + +DROP PROCEDURE IF EXISTS ido_drop_index_if_exists // +CREATE PROCEDURE ido_drop_index_if_exists ( + IN p_table_name varchar(64), + IN p_index_name varchar(64) +) + DETERMINISTIC + MODIFIES SQL DATA + BEGIN + IF ido_index_exists(p_table_name, p_index_name) + THEN + SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`'); + PREPARE stmt FROM @ido_drop_index_sql; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + SET @ido_drop_index_sql = NULL; + END IF; + END // +DELIMITER ; + +CALL ido_drop_index_if_exists('icinga_downtimehistory', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_commenthistory', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_comments', 'instance_id'); + +DROP FUNCTION ido_index_exists; +DROP PROCEDURE ido_drop_index_if_exists; + +-- ----------------------------------------- +-- #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 IDO: Improve comment removal +-- ----------------------------------------- + +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 +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.3', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.3', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.8.1.sql b/lib/db_ido_mysql/schema/upgrade/2.8.1.sql new file mode 100644 index 0000000..98f8511 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.8.1.sql @@ -0,0 +1,67 @@ +-- ----------------------------------------- +-- 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! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- -------------------------------------------------------- +-- Helper functions and procedures for DROP INDEX IF EXISTS +-- -------------------------------------------------------- + +DELIMITER // +DROP FUNCTION IF EXISTS ido_index_exists // +CREATE FUNCTION ido_index_exists( + f_table_name varchar(64), + f_index_name varchar(64) +) + RETURNS BOOL + DETERMINISTIC + READS SQL DATA + BEGIN + DECLARE index_exists BOOL DEFAULT FALSE; + SELECT EXISTS ( + SELECT 1 + FROM information_schema.statistics + WHERE table_schema = SCHEMA() + AND table_name = f_table_name + AND index_name = f_index_name + ) INTO index_exists; + RETURN index_exists; + END // + +DROP PROCEDURE IF EXISTS ido_drop_index_if_exists // +CREATE PROCEDURE ido_drop_index_if_exists ( + IN p_table_name varchar(64), + IN p_index_name varchar(64) +) + DETERMINISTIC + MODIFIES SQL DATA + BEGIN + IF ido_index_exists(p_table_name, p_index_name) + THEN + SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`'); + PREPARE stmt FROM @ido_drop_index_sql; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + SET @ido_drop_index_sql = NULL; + END IF; + END // +DELIMITER ; + +CALL ido_drop_index_if_exists('icinga_downtimehistory', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_commenthistory', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_comments', 'instance_id'); + +DROP FUNCTION ido_index_exists; +DROP PROCEDURE ido_drop_index_if_exists; + +-- ----------------------------------------- +-- set dbversion (same as 2.8.0) +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.3', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.3', modify_time=NOW(); |