diff options
Diffstat (limited to 'schema/mysql/schema.sql')
| -rw-r--r-- | schema/mysql/schema.sql | 1333 |
1 files changed, 1333 insertions, 0 deletions
diff --git a/schema/mysql/schema.sql b/schema/mysql/schema.sql new file mode 100644 index 0000000..f13fe4c --- /dev/null +++ b/schema/mysql/schema.sql @@ -0,0 +1,1333 @@ +-- IcingaDB | (c) 2019 Icinga GmbH | GPLv2+ + +SET SESSION sql_mode = 'STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION'; +SET SESSION innodb_strict_mode = 1; + +DROP FUNCTION IF EXISTS get_sla_ok_percent; +DELIMITER // +CREATE FUNCTION get_sla_ok_percent( + in_host_id binary(20), + in_service_id binary(20), + in_start_time bigint unsigned, + in_end_time bigint unsigned +) +RETURNS decimal(7, 4) +READS SQL DATA +BEGIN + DECLARE result decimal(7, 4); + DECLARE row_event_time bigint unsigned; + DECLARE row_event_type enum('state_change', 'downtime_start', 'downtime_end', 'end'); + DECLARE row_event_prio int; + DECLARE row_hard_state tinyint unsigned; + DECLARE row_previous_hard_state tinyint unsigned; + DECLARE last_event_time bigint unsigned; + DECLARE last_hard_state tinyint unsigned; + DECLARE active_downtimes int unsigned; + DECLARE problem_time bigint unsigned; + DECLARE total_time bigint unsigned; + DECLARE done int; + DECLARE cur CURSOR FOR + ( + -- all downtime_start events before the end of the SLA interval + -- for downtimes that overlap the SLA interval in any way + SELECT + GREATEST(downtime_start, in_start_time) AS event_time, + 'downtime_start' AS event_type, + 1 AS event_prio, + NULL AS hard_state, + NULL AS previous_hard_state + FROM sla_history_downtime d + WHERE d.host_id = in_host_id + AND ((in_service_id IS NULL AND d.service_id IS NULL) OR d.service_id = in_service_id) + AND d.downtime_start < in_end_time + AND d.downtime_end >= in_start_time + ) UNION ALL ( + -- all downtime_end events before the end of the SLA interval + -- for downtimes that overlap the SLA interval in any way + SELECT + downtime_end AS event_time, + 'downtime_end' AS event_type, + 2 AS event_prio, + NULL AS hard_state, + NULL AS previous_hard_state + FROM sla_history_downtime d + WHERE d.host_id = in_host_id + AND ((in_service_id IS NULL AND d.service_id IS NULL) OR d.service_id = in_service_id) + AND d.downtime_start < in_end_time + AND d.downtime_end >= in_start_time + AND d.downtime_end < in_end_time + ) UNION ALL ( + -- all state events strictly in interval + SELECT + event_time, + 'state_change' AS event_type, + 0 AS event_prio, + hard_state, + previous_hard_state + FROM sla_history_state s + WHERE s.host_id = in_host_id + AND ((in_service_id IS NULL AND s.service_id IS NULL) OR s.service_id = in_service_id) + AND s.event_time > in_start_time + AND s.event_time < in_end_time + ) UNION ALL ( + -- end event to keep loop simple, values are not used + SELECT + in_end_time AS event_time, + 'end' AS event_type, + 3 AS event_prio, + NULL AS hard_state, + NULL AS previous_hard_state + ) + ORDER BY event_time, event_prio; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; + + IF in_end_time <= in_start_time THEN + SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'end time must be greater than start time'; + END IF; + + -- Use the latest event at or before the beginning of the SLA interval as the initial state. + SELECT hard_state INTO last_hard_state + FROM sla_history_state s + WHERE s.host_id = in_host_id + AND ((in_service_id IS NULL AND s.service_id IS NULL) OR s.service_id = in_service_id) + AND s.event_time <= in_start_time + ORDER BY s.event_time DESC + LIMIT 1; + + -- If this does not exist, use the previous state from the first event after the beginning of the SLA interval. + IF last_hard_state IS NULL THEN + SELECT previous_hard_state INTO last_hard_state + FROM sla_history_state s + WHERE s.host_id = in_host_id + AND ((in_service_id IS NULL AND s.service_id IS NULL) OR s.service_id = in_service_id) + AND s.event_time > in_start_time + ORDER BY s.event_time ASC + LIMIT 1; + END IF; + + -- If this also does not exist, use the current host/service state. + IF last_hard_state IS NULL THEN + IF in_service_id IS NULL THEN + SELECT hard_state INTO last_hard_state + FROM host_state s + WHERE s.host_id = in_host_id; + ELSE + SELECT hard_state INTO last_hard_state + FROM service_state s + WHERE s.host_id = in_host_id + AND s.service_id = in_service_id; + END IF; + END IF; + + IF last_hard_state IS NULL THEN + SET last_hard_state = 0; + END IF; + + SET problem_time = 0; + SET total_time = in_end_time - in_start_time; + SET last_event_time = in_start_time; + SET active_downtimes = 0; + + SET done = 0; + OPEN cur; + read_loop: LOOP + FETCH cur INTO row_event_time, row_event_type, row_event_prio, row_hard_state, row_previous_hard_state; + IF done THEN + LEAVE read_loop; + END IF; + + IF row_previous_hard_state = 99 THEN + SET total_time = total_time - (row_event_time - last_event_time); + ELSEIF ((in_service_id IS NULL AND last_hard_state > 0) OR (in_service_id IS NOT NULL AND last_hard_state > 1)) + AND last_hard_state != 99 + AND active_downtimes = 0 + THEN + SET problem_time = problem_time + row_event_time - last_event_time; + END IF; + + SET last_event_time = row_event_time; + IF row_event_type = 'state_change' THEN + SET last_hard_state = row_hard_state; + ELSEIF row_event_type = 'downtime_start' THEN + SET active_downtimes = active_downtimes + 1; + ELSEIF row_event_type = 'downtime_end' THEN + SET active_downtimes = active_downtimes - 1; + END IF; + END LOOP; + CLOSE cur; + + SET result = 100 * (total_time - problem_time) / total_time; + RETURN result; +END// +DELIMITER ; + +CREATE TABLE host ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + address varchar(255) NOT NULL, + address6 varchar(255) NOT NULL, + address_bin binary(4) DEFAULT NULL, + address6_bin binary(16) DEFAULT NULL, + + checkcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'checkcommand.name', + checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id', + + max_check_attempts int unsigned NOT NULL, + + check_timeperiod_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'timeperiod.name', + check_timeperiod_id binary(20) DEFAULT NULL COMMENT 'timeperiod.id', + + check_timeout int unsigned DEFAULT NULL, + check_interval int unsigned NOT NULL, + check_retry_interval int unsigned NOT NULL, + + active_checks_enabled enum('n', 'y') NOT NULL, + passive_checks_enabled enum('n', 'y') NOT NULL, + event_handler_enabled enum('n', 'y') NOT NULL, + notifications_enabled enum('n', 'y') NOT NULL, + + flapping_enabled enum('n', 'y') NOT NULL, + flapping_threshold_low float NOT NULL, + flapping_threshold_high float NOT NULL, + + perfdata_enabled enum('n', 'y') NOT NULL, + + eventcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'eventcommand.name', + eventcommand_id binary(20) DEFAULT NULL COMMENT 'eventcommand.id', + + is_volatile enum('n', 'y') NOT NULL, + + action_url_id binary(20) DEFAULT NULL COMMENT 'action_url.id', + notes_url_id binary(20) DEFAULT NULL COMMENT 'notes_url.id', + notes text NOT NULL, + icon_image_id binary(20) DEFAULT NULL COMMENT 'icon_image.id', + icon_image_alt varchar(32) NOT NULL, + + zone_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'zone.name', + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + command_endpoint_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'endpoint.name', + command_endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + + PRIMARY KEY (id), + KEY idx_action_url_checksum (action_url_id) COMMENT 'cleanup', + KEY idx_notes_url_checksum (notes_url_id) COMMENT 'cleanup', + KEY idx_icon_image_checksum (icon_image_id) COMMENT 'cleanup', + + INDEX idx_host_display_name (display_name) COMMENT 'Host list filtered/ordered by display_name', + INDEX idx_host_name_ci (name_ci) COMMENT 'Host list filtered using quick search', + INDEX idx_host_name (name) COMMENT 'Host list filtered/ordered by name; Host detail filter' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE hostgroup ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + PRIMARY KEY (id), + + INDEX idx_hostgroup_name (name) COMMENT 'Host/service/host group list filtered by host group name' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE hostgroup_member ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + host_id + hostgroup_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + host_id binary(20) NOT NULL COMMENT 'host.id', + hostgroup_id binary(20) NOT NULL COMMENT 'hostgroup.id', + + PRIMARY KEY (id), + + INDEX idx_hostgroup_member_host_id (host_id, hostgroup_id), + INDEX idx_hostgroup_member_hostgroup_id (hostgroup_id, host_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE host_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + host_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + host_id binary(20) NOT NULL COMMENT 'host.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_host_customvar_host_id (host_id, customvar_id), + INDEX idx_host_customvar_customvar_id (customvar_id, host_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE hostgroup_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + hostgroup_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + hostgroup_id binary(20) NOT NULL COMMENT 'hostgroup.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_hostgroup_customvar_hostgroup_id (hostgroup_id, customvar_id), + INDEX idx_hostgroup_customvar_customvar_id (customvar_id, hostgroup_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE host_state ( + id binary(20) NOT NULL COMMENT 'host.id', + host_id binary(20) NOT NULL COMMENT 'host.id', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + state_type enum('hard', 'soft') NOT NULL, + soft_state tinyint unsigned NOT NULL, + hard_state tinyint unsigned NOT NULL, + previous_soft_state tinyint unsigned NOT NULL, + previous_hard_state tinyint unsigned NOT NULL, + check_attempt tinyint unsigned NOT NULL, + severity smallint unsigned NOT NULL, + + output longtext DEFAULT NULL, + long_output longtext DEFAULT NULL, + performance_data longtext DEFAULT NULL, + normalized_performance_data longtext DEFAULT NULL, + check_commandline text DEFAULT NULL, + + is_problem enum('n', 'y') NOT NULL, + is_handled enum('n', 'y') NOT NULL, + is_reachable enum('n', 'y') NOT NULL, + is_flapping enum('n', 'y') NOT NULL, + is_overdue enum('n', 'y') NOT NULL, + + is_acknowledged enum('n', 'y', 'sticky') NOT NULL, + acknowledgement_comment_id binary(20) DEFAULT NULL COMMENT 'comment.id', + last_comment_id binary(20) DEFAULT NULL COMMENT 'comment.id', + + in_downtime enum('n', 'y') NOT NULL, + + execution_time int unsigned DEFAULT NULL, + latency int unsigned DEFAULT NULL, + check_timeout int unsigned DEFAULT NULL, + check_source text DEFAULT NULL, + scheduling_source text DEFAULT NULL, + + last_update bigint unsigned DEFAULT NULL, + last_state_change bigint unsigned NOT NULL, + next_check bigint unsigned NOT NULL, + next_update bigint unsigned NOT NULL, + + PRIMARY KEY (id), + + UNIQUE INDEX idx_host_state_host_id (host_id), + INDEX idx_host_state_is_problem (is_problem, severity) COMMENT 'Host list filtered by is_problem ordered by severity', + INDEX idx_host_state_severity (severity) COMMENT 'Host list filtered/ordered by severity', + INDEX idx_host_state_soft_state (soft_state, last_state_change) COMMENT 'Host list filtered/ordered by soft_state; recently recovered filter', + INDEX idx_host_state_last_state_change (last_state_change) COMMENT 'Host list filtered/ordered by last_state_change' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE service ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + host_id binary(20) NOT NULL COMMENT 'sha1(host.id)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + checkcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'checkcommand.name', + checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id', + + max_check_attempts int unsigned NOT NULL, + + check_timeperiod_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'timeperiod.name', + check_timeperiod_id binary(20) DEFAULT NULL COMMENT 'timeperiod.id', + + check_timeout int unsigned DEFAULT NULL, + check_interval int unsigned NOT NULL, + check_retry_interval int unsigned NOT NULL, + + active_checks_enabled enum('n', 'y') NOT NULL, + passive_checks_enabled enum('n', 'y') NOT NULL, + event_handler_enabled enum('n', 'y') NOT NULL, + notifications_enabled enum('n', 'y') NOT NULL, + + flapping_enabled enum('n', 'y') NOT NULL, + flapping_threshold_low float NOT NULL, + flapping_threshold_high float NOT NULL, + + perfdata_enabled enum('n', 'y') NOT NULL, + + eventcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'eventcommand.name', + eventcommand_id binary(20) DEFAULT NULL COMMENT 'eventcommand.id', + + is_volatile enum('n', 'y') NOT NULL, + + action_url_id binary(20) DEFAULT NULL COMMENT 'action_url.id', + notes_url_id binary(20) DEFAULT NULL COMMENT 'notes_url.id', + notes text NOT NULL, + icon_image_id binary(20) DEFAULT NULL COMMENT 'icon_image.id', + icon_image_alt varchar(32) NOT NULL, + + zone_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'zone.name', + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + command_endpoint_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'endpoint.name', + command_endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + + PRIMARY KEY (id), + + INDEX idx_service_display_name (display_name) COMMENT 'Service list filtered/ordered by display_name', + INDEX idx_service_host_id (host_id, display_name) COMMENT 'Service list filtered by host and ordered by display_name', + INDEX idx_service_name_ci (name_ci) COMMENT 'Service list filtered using quick search', + INDEX idx_service_name (name) COMMENT 'Service list filtered/ordered by name; Service detail filter' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE servicegroup ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + PRIMARY KEY (id), + + INDEX idx_servicegroup_name (name) COMMENT 'Host/service/service group list filtered by service group name' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE servicegroup_member ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + servicegroup_id + service_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + service_id binary(20) NOT NULL COMMENT 'service.id', + servicegroup_id binary(20) NOT NULL COMMENT 'servicegroup.id', + + PRIMARY KEY (id), + + INDEX idx_servicegroup_member_service_id (service_id, servicegroup_id), + INDEX idx_servicegroup_member_servicegroup_id (servicegroup_id, service_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE service_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + service_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + service_id binary(20) NOT NULL COMMENT 'service.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + + INDEX idx_service_customvar_service_id (service_id, customvar_id), + INDEX idx_service_customvar_customvar_id (customvar_id, service_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE servicegroup_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + servicegroup_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + servicegroup_id binary(20) NOT NULL COMMENT 'servicegroup.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_servicegroup_customvar_servicegroup_id (servicegroup_id, customvar_id), + INDEX idx_servicegroup_customvar_customvar_id (customvar_id, servicegroup_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE service_state ( + id binary(20) NOT NULL COMMENT 'service.id', + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) NOT NULL COMMENT 'service.id', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + state_type enum('hard', 'soft') NOT NULL, + soft_state tinyint unsigned NOT NULL, + hard_state tinyint unsigned NOT NULL, + previous_soft_state tinyint unsigned NOT NULL, + previous_hard_state tinyint unsigned NOT NULL, + check_attempt tinyint unsigned NOT NULL, + severity smallint unsigned NOT NULL, + + output longtext DEFAULT NULL, + long_output longtext DEFAULT NULL, + performance_data longtext DEFAULT NULL, + normalized_performance_data longtext DEFAULT NULL, + + check_commandline text DEFAULT NULL, + + is_problem enum('n', 'y') NOT NULL, + is_handled enum('n', 'y') NOT NULL, + is_reachable enum('n', 'y') NOT NULL, + is_flapping enum('n', 'y') NOT NULL, + is_overdue enum('n', 'y') NOT NULL, + + is_acknowledged enum('n', 'y', 'sticky') NOT NULL, + acknowledgement_comment_id binary(20) DEFAULT NULL COMMENT 'comment.id', + last_comment_id binary(20) DEFAULT NULL COMMENT 'comment.id', + + in_downtime enum('n', 'y') NOT NULL, + + execution_time int unsigned DEFAULT NULL, + latency int unsigned DEFAULT NULL, + check_timeout int unsigned DEFAULT NULL, + check_source text DEFAULT NULL, + scheduling_source text DEFAULT NULL, + + last_update bigint unsigned DEFAULT NULL, + last_state_change bigint unsigned NOT NULL, + next_check bigint unsigned NOT NULL, + next_update bigint unsigned NOT NULL, + + PRIMARY KEY (id), + + UNIQUE INDEX idx_service_state_service_id (service_id), + INDEX idx_service_state_is_problem (is_problem, severity) COMMENT 'Service list filtered by is_problem ordered by severity', + INDEX idx_service_state_severity (severity) COMMENT 'Service list filtered/ordered by severity', + INDEX idx_service_state_soft_state (soft_state, last_state_change) COMMENT 'Service list filtered/ordered by soft_state; recently recovered filter', + INDEX idx_service_state_last_state_change (last_state_change) COMMENT 'Service list filtered/ordered by last_state_change' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE endpoint ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL, + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + zone_id binary(20) NOT NULL COMMENT 'zone.id', + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE environment ( + id binary(20) NOT NULL COMMENT 'sha1(Icinga CA public key)', + name varchar(255) NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE icingadb_instance ( + id binary(16) NOT NULL COMMENT 'UUIDv4', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + heartbeat bigint unsigned NOT NULL COMMENT '*nix timestamp', + responsible enum('n', 'y') NOT NULL, + + icinga2_version varchar(255) NOT NULL, + icinga2_start_time bigint unsigned NOT NULL, + icinga2_notifications_enabled enum('n', 'y') NOT NULL, + icinga2_active_service_checks_enabled enum('n', 'y') NOT NULL, + icinga2_active_host_checks_enabled enum('n', 'y') NOT NULL, + icinga2_event_handlers_enabled enum('n', 'y') NOT NULL, + icinga2_flap_detection_enabled enum('n', 'y') NOT NULL, + icinga2_performance_data_enabled enum('n', 'y') NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE checkcommand ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + command text NOT NULL, + timeout int unsigned NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE checkcommand_argument ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + argument_key)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id', + argument_key varchar(64) NOT NULL, + + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + argument_value text DEFAULT NULL, + argument_order smallint DEFAULT NULL, + description text DEFAULT NULL, + argument_key_override varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + repeat_key enum('n', 'y') NOT NULL, + required enum('n', 'y') NOT NULL, + set_if varchar(255) DEFAULT NULL, + `separator` varchar(255) DEFAULT NULL, + skip_key enum('n', 'y') NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE checkcommand_envvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + envvar_key)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id', + envvar_key varchar(64) NOT NULL, + + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + envvar_value text NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE checkcommand_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + + checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_checkcommand_customvar_checkcommand_id (checkcommand_id, customvar_id), + INDEX idx_checkcommand_customvar_customvar_id (customvar_id, checkcommand_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + + +CREATE TABLE eventcommand ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + command text NOT NULL, + timeout smallint unsigned NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE eventcommand_argument ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + argument_key)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id', + argument_key varchar(64) NOT NULL, + + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + argument_value text DEFAULT NULL, + argument_order smallint DEFAULT NULL, + description text DEFAULT NULL, + argument_key_override varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + repeat_key enum('n', 'y') NOT NULL, + required enum('n', 'y') NOT NULL, + set_if varchar(255) DEFAULT NULL, + `separator` varchar(255) DEFAULT NULL, + skip_key enum('n', 'y') NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE eventcommand_envvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + envvar_key)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id', + envvar_key varchar(64) NOT NULL, + + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + envvar_value text NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE eventcommand_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_eventcommand_customvar_eventcommand_id (eventcommand_id, customvar_id), + INDEX idx_eventcommand_customvar_customvar_id (customvar_id, eventcommand_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notificationcommand ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + command text NOT NULL, + timeout smallint unsigned NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notificationcommand_argument ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + argument_key)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id', + argument_key varchar(64) NOT NULL, + + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + argument_value text DEFAULT NULL, + argument_order smallint DEFAULT NULL, + description text DEFAULT NULL, + argument_key_override varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + repeat_key enum('n', 'y') NOT NULL, + required enum('n', 'y') NOT NULL, + set_if varchar(255) DEFAULT NULL, + `separator` varchar(255) DEFAULT NULL, + skip_key enum('n', 'y') NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notificationcommand_envvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + envvar_key)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id', + envvar_key varchar(64) NOT NULL, + + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + envvar_value text NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notificationcommand_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_notificationcommand_customvar_notificationcommand_id (notificationcommand_id, customvar_id), + INDEX idx_notificationcommand_customvar_customvar_id (customvar_id, notificationcommand_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE comment ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL, + name varchar(548) NOT NULL COMMENT '255+1+255+1+36, i.e. "host.name!service.name!UUID"', + + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + text text NOT NULL, + entry_type enum('comment','ack') NOT NULL, + entry_time bigint unsigned NOT NULL, + is_persistent enum('n', 'y') NOT NULL, + is_sticky enum('n', 'y') NOT NULL, + expire_time bigint unsigned DEFAULT NULL, + + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + PRIMARY KEY (id), + + INDEX idx_comment_name (name) COMMENT 'Comment detail filter', + INDEX idx_comment_entry_time (entry_time) COMMENT 'Comment list fileted/ordered by entry_time', + INDEX idx_comment_author (author) COMMENT 'Comment list filtered/ordered by author', + INDEX idx_comment_expire_time (expire_time) COMMENT 'Comment list filtered/ordered by expire_time' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE downtime ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + + triggered_by_id binary(20) DEFAULT NULL COMMENT 'The ID of the downtime that triggered this downtime. This is set when creating downtimes on a host or service higher up in the dependency chain using the "child_option" "DowntimeTriggeredChildren" and can also be set manually via the API.', + parent_id binary(20) DEFAULT NULL COMMENT 'For service downtimes, the ID of the host downtime that created this downtime by using the "all_services" flag of the schedule-downtime API.', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + name varchar(548) NOT NULL COMMENT '255+1+255+1+36, i.e. "host.name!service.name!UUID"', + + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + comment text NOT NULL, + entry_time bigint unsigned NOT NULL, + scheduled_start_time bigint unsigned NOT NULL, + scheduled_end_time bigint unsigned NOT NULL, + scheduled_duration bigint unsigned NOT NULL, + is_flexible enum('n', 'y') NOT NULL, + flexible_duration bigint unsigned NOT NULL, + + is_in_effect enum('n', 'y') NOT NULL, + start_time bigint unsigned DEFAULT NULL COMMENT 'Time when the host went into a problem state during the downtimes timeframe', + end_time bigint unsigned DEFAULT NULL COMMENT 'Problem state assumed: scheduled_end_time if fixed, start_time + flexible_duration otherwise', + duration bigint unsigned NOT NULL COMMENT 'Duration of the downtime: When the downtime is flexible, this is the same as flexible_duration otherwise scheduled_duration', + scheduled_by varchar(767) DEFAULT NULL COMMENT 'Name of the ScheduledDowntime which created this Downtime. 255+1+255+1+255, i.e. "host.name!service.name!scheduled-downtime-name"', + + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + PRIMARY KEY (id), + + INDEX idx_downtime_is_in_effect (is_in_effect, start_time) COMMENT 'Downtime list filtered/ordered by severity', + INDEX idx_downtime_name (name) COMMENT 'Downtime detail filter', + INDEX idx_downtime_entry_time (entry_time) COMMENT 'Downtime list filtered/ordered by entry_time', + INDEX idx_downtime_start_time (start_time) COMMENT 'Downtime list filtered/ordered by start_time', + INDEX idx_downtime_end_time (end_time) COMMENT 'Downtime list filtered/ordered by end_time', + INDEX idx_downtime_scheduled_start_time (scheduled_start_time) COMMENT 'Downtime list filtered/ordered by scheduled_start_time', + INDEX idx_downtime_scheduled_end_time (scheduled_end_time) COMMENT 'Downtime list filtered/ordered by scheduled_end_time', + INDEX idx_downtime_author (author) COMMENT 'Downtime list filtered/ordered by author', + INDEX idx_downtime_duration (duration) COMMENT 'Downtime list filtered/ordered by duration' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notification ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL, + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id', + + times_begin int unsigned DEFAULT NULL, + times_end int unsigned DEFAULT NULL, + notification_interval int unsigned NOT NULL, + timeperiod_id binary(20) DEFAULT NULL COMMENT 'timeperiod.id', + + states tinyint unsigned NOT NULL, + types smallint unsigned NOT NULL, + + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + PRIMARY KEY (id), + + INDEX idx_notification_host_id (host_id), + INDEX idx_notification_service_id (service_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notification_user ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + user_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + notification_id binary(20) NOT NULL COMMENT 'notification.id', + user_id binary(20) NOT NULL COMMENT 'user.id', + + PRIMARY KEY (id), + + INDEX idx_notification_user_user_id (user_id, notification_id), + INDEX idx_notification_user_notification_id (notification_id, user_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notification_usergroup ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + usergroup_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + notification_id binary(20) NOT NULL COMMENT 'notification.id', + usergroup_id binary(20) NOT NULL COMMENT 'usergroup.id', + + PRIMARY KEY (id), + + INDEX idx_notification_usergroup_usergroup_id (usergroup_id, notification_id), + INDEX idx_notification_usergroup_notification_id (notification_id, usergroup_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notification_recipient ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + (user_id | usergroup_id))', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + notification_id binary(20) NOT NULL COMMENT 'notification.id', + user_id binary(20) NULL COMMENT 'user.id', + usergroup_id binary(20) NULL COMMENT 'usergroup.id', + + PRIMARY KEY (id), + + INDEX idx_notification_recipient_user_id (user_id, notification_id), + INDEX idx_notification_recipient_notification_id_user (notification_id, user_id), + INDEX idx_notification_recipient_usergroup_id (usergroup_id, notification_id), + INDEX idx_notification_recipient_notification_id_usergroup (notification_id, usergroup_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notification_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + notification_id binary(20) NOT NULL COMMENT 'notification.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_notification_customvar_notification_id (notification_id, customvar_id), + INDEX idx_notification_customvar_customvar_id (customvar_id, notification_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE icon_image ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + icon_image)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + icon_image text COLLATE utf8mb4_unicode_ci NOT NULL, + + PRIMARY KEY (id), + KEY idx_icon_image (icon_image(255)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE action_url ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + action_url)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + action_url text COLLATE utf8mb4_unicode_ci NOT NULL, + + PRIMARY KEY (id), + KEY idx_action_url (action_url(255)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notes_url ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + notes_url)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + notes_url text COLLATE utf8mb4_unicode_ci NOT NULL, + + PRIMARY KEY (id), + KEY idx_notes_url (notes_url(255)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE timeperiod ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + prefer_includes enum('n', 'y') NOT NULL, + + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE timeperiod_range ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + range_id + timeperiod_id)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + timeperiod_id binary(20) NOT NULL COMMENT 'timeperiod.id', + range_key varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + range_value varchar(255) NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE timeperiod_override_include ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + include_id + timeperiod_id)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + timeperiod_id binary(20) NOT NULL COMMENT 'timeperiod.id', + override_id binary(20) NOT NULL COMMENT 'timeperiod.id', + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE timeperiod_override_exclude ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + exclude_id + timeperiod_id)', + environment_id binary(20) NOT NULL COMMENT 'env.id', + timeperiod_id binary(20) NOT NULL COMMENT 'timeperiod.id', + override_id binary(20) NOT NULL COMMENT 'timeperiod.id', + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE timeperiod_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + timeperiod_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + timeperiod_id binary(20) NOT NULL COMMENT 'timeperiod.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_timeperiod_customvar_timeperiod_id (timeperiod_id, customvar_id), + INDEX idx_timeperiod_customvar_customvar_id (customvar_id, timeperiod_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name + value)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + + name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + value text NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE customvar_flat ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + flatname + flatvalue)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + customvar_id binary(20) NOT NULL COMMENT 'sha1(customvar.id)', + flatname_checksum binary(20) NOT NULL COMMENT 'sha1(flatname after conversion)', + + flatname varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Path converted with `.` and `[ ]`', + flatvalue text NOT NULL, + + PRIMARY KEY (id), + + INDEX idx_customvar_flat_customvar_id (customvar_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE user ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + email varchar(255) NOT NULL, + pager varchar(255) NOT NULL, + + notifications_enabled enum('n', 'y') NOT NULL, + + timeperiod_id binary(20) DEFAULT NULL COMMENT 'timeperiod.id', + + states tinyint unsigned NOT NULL, + types smallint unsigned NOT NULL, + + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + PRIMARY KEY (id), + + INDEX idx_user_display_name (display_name) COMMENT 'User list filtered/ordered by display_name', + INDEX idx_user_name_ci (name_ci) COMMENT 'User list filtered using quick search', + INDEX idx_user_name (name) COMMENT 'User list filtered/ordered by name; User detail filter' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE usergroup ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + zone_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + PRIMARY KEY (id), + + INDEX idx_usergroup_display_name (display_name) COMMENT 'Usergroup list filtered/ordered by display_name', + INDEX idx_usergroup_name_ci (name_ci) COMMENT 'Usergroup list filtered using quick search', + INDEX idx_usergroup_name (name) COMMENT 'Usergroup list filtered/ordered by name; Usergroup detail filter' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE usergroup_member ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + usergroup_id + user_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + user_id binary(20) NOT NULL COMMENT 'user.id', + usergroup_id binary(20) NOT NULL COMMENT 'usergroup.id', + + PRIMARY KEY (id), + + INDEX idx_usergroup_member_user_id (user_id, usergroup_id), + INDEX idx_usergroup_member_usergroup_id (usergroup_id, user_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE user_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + user_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + user_id binary(20) NOT NULL COMMENT 'user.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_user_customvar_user_id (user_id, customvar_id), + INDEX idx_user_customvar_customvar_id (customvar_id, user_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE usergroup_customvar ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + usergroup_id + customvar_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + usergroup_id binary(20) NOT NULL COMMENT 'usergroup.id', + customvar_id binary(20) NOT NULL COMMENT 'customvar.id', + + PRIMARY KEY (id), + + INDEX idx_usergroup_customvar_usergroup_id (usergroup_id, customvar_id), + INDEX idx_usergroup_customvar_customvar_id (customvar_id, usergroup_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE zone ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + name_checksum binary(20) NOT NULL COMMENT 'sha1(name)', + properties_checksum binary(20) NOT NULL COMMENT 'sha1(all properties)', + + name varchar(255) NOT NULL, + name_ci varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + + is_global enum('n', 'y') NOT NULL, + parent_id binary(20) DEFAULT NULL COMMENT 'zone.id', + + depth tinyint unsigned NOT NULL, + + PRIMARY KEY (id), + + UNIQUE INDEX idx_environment_id_id (environment_id, id), + INDEX idx_zone_parent_id (parent_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE notification_history ( + id binary(20) NOT NULL COMMENT 'sha1(environment.name + notification.name + type + send_time)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + notification_id binary(20) NOT NULL COMMENT 'notification.id', + + type enum('downtime_start', 'downtime_end', 'downtime_removed', 'custom', 'acknowledgement', 'problem', 'recovery', 'flapping_start', 'flapping_end') NOT NULL, + send_time bigint unsigned NOT NULL, + state tinyint unsigned NOT NULL, + previous_hard_state tinyint unsigned NOT NULL, + author text NOT NULL, + `text` longtext NOT NULL, + users_notified smallint unsigned NOT NULL, + + PRIMARY KEY (id), + + INDEX idx_notification_history_send_time (send_time DESC) COMMENT 'Notification list filtered/ordered by send_time', + INDEX idx_notification_history_env_send_time (environment_id, send_time) COMMENT 'Filter for history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE user_notification_history ( + id binary(20) NOT NULL COMMENT 'sha1(notification_history_id + user_id)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + notification_history_id binary(20) NOT NULL COMMENT 'UUID notification_history.id', + user_id binary(20) NOT NULL COMMENT 'user.id', + + PRIMARY KEY (id), + + CONSTRAINT fk_user_notification_history_notification_history FOREIGN KEY (notification_history_id) REFERENCES notification_history (id) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE state_history ( + id binary(20) NOT NULL COMMENT 'sha1(environment.name + host|service.name + event_time)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + event_time bigint unsigned NOT NULL, + state_type enum('hard', 'soft') NOT NULL, + soft_state tinyint unsigned NOT NULL, + hard_state tinyint unsigned NOT NULL, + previous_soft_state tinyint unsigned NOT NULL, + previous_hard_state tinyint unsigned NOT NULL, + check_attempt tinyint unsigned NOT NULL, + output longtext DEFAULT NULL, + long_output longtext DEFAULT NULL, + max_check_attempts int unsigned NOT NULL, + check_source text DEFAULT NULL, + scheduling_source text DEFAULT NULL, + + PRIMARY KEY (id), + + INDEX idx_state_history_env_event_time (environment_id, event_time) COMMENT 'Filter for history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE downtime_history ( + downtime_id binary(20) NOT NULL COMMENT 'downtime.id', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + triggered_by_id binary(20) DEFAULT NULL COMMENT 'The ID of the downtime that triggered this downtime. This is set when creating downtimes on a host or service higher up in the dependency chain using the "child_option" "DowntimeTriggeredChildren" and can also be set manually via the API.', + parent_id binary(20) DEFAULT NULL COMMENT 'For service downtimes, the ID of the host downtime that created this downtime by using the "all_services" flag of the schedule-downtime API.', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + entry_time bigint unsigned NOT NULL, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + cancelled_by varchar(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, + comment text NOT NULL, + is_flexible enum('n', 'y') NOT NULL, + flexible_duration bigint unsigned NOT NULL, + scheduled_start_time bigint unsigned NOT NULL, + scheduled_end_time bigint unsigned NOT NULL, + start_time bigint unsigned NOT NULL COMMENT 'Time when the host went into a problem state during the downtimes timeframe', + end_time bigint unsigned NOT NULL COMMENT 'Problem state assumed: scheduled_end_time if fixed, start_time + duration otherwise', + scheduled_by varchar(767) DEFAULT NULL COMMENT 'Name of the ScheduledDowntime which created this Downtime. 255+1+255+1+255, i.e. "host.name!service.name!scheduled-downtime-name"', + has_been_cancelled enum('n', 'y') NOT NULL, + trigger_time bigint unsigned NOT NULL, + cancel_time bigint unsigned DEFAULT NULL, + + PRIMARY KEY (downtime_id), + + INDEX idx_downtime_history_env_end_time (environment_id, end_time) COMMENT 'Filter for history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE comment_history ( + comment_id binary(20) NOT NULL COMMENT 'comment.id', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + entry_time bigint unsigned NOT NULL, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + removed_by varchar(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, + comment text NOT NULL, + entry_type enum('comment','ack') NOT NULL, + is_persistent enum('n', 'y') NOT NULL, + is_sticky enum('n', 'y') NOT NULL, + expire_time bigint unsigned DEFAULT NULL, + remove_time bigint unsigned DEFAULT NULL, + has_been_removed enum('n', 'y') NOT NULL, + + PRIMARY KEY (comment_id), + + INDEX idx_comment_history_env_remove_time (environment_id, remove_time) COMMENT 'Filter for history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE flapping_history ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + "Host"|"Service" + host|service.name + start_time)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + start_time bigint unsigned NOT NULL, + end_time bigint unsigned DEFAULT NULL, + percent_state_change_start float DEFAULT NULL, + percent_state_change_end float DEFAULT NULL, + flapping_threshold_low float NOT NULL, + flapping_threshold_high float NOT NULL, + + PRIMARY KEY (id), + + INDEX idx_flapping_history_env_end_time (environment_id, end_time) COMMENT 'Filter for history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE acknowledgement_history ( + id binary(20) NOT NULL COMMENT 'sha1(environment.id + "Host"|"Service" + host|service.name + set_time)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + set_time bigint unsigned NOT NULL, + clear_time bigint unsigned DEFAULT NULL, + author varchar(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci COMMENT 'NULL if ack_set event happened before Icinga DB history recording', + cleared_by varchar(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, + comment text DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording', + expire_time bigint unsigned DEFAULT NULL, + is_sticky enum('n', 'y') DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording', + is_persistent enum('n', 'y') DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording', + + PRIMARY KEY (id), + + INDEX idx_acknowledgement_history_env_clear_time (environment_id, clear_time) COMMENT 'Filter for history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE history ( + id binary(20) NOT NULL COMMENT 'sha1(environment.name + event_type + x...) given that sha1(environment.name + x...) = *_history_id', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + notification_history_id binary(20) DEFAULT NULL COMMENT 'notification_history.id', + state_history_id binary(20) DEFAULT NULL COMMENT 'state_history.id', + downtime_history_id binary(20) DEFAULT NULL COMMENT 'downtime_history.downtime_id', + comment_history_id binary(20) DEFAULT NULL COMMENT 'comment_history.comment_id', + flapping_history_id binary(20) DEFAULT NULL COMMENT 'flapping_history.id', + acknowledgement_history_id binary(20) DEFAULT NULL COMMENT 'acknowledgement_history.id', + + event_type enum('notification','state_change','downtime_start', 'downtime_end','comment_add','comment_remove','flapping_start','flapping_end','ack_set','ack_clear') NOT NULL, + event_time bigint unsigned NOT NULL, + + PRIMARY KEY (id), + + CONSTRAINT fk_history_acknowledgement_history FOREIGN KEY (acknowledgement_history_id) REFERENCES acknowledgement_history (id) ON DELETE CASCADE, + CONSTRAINT fk_history_comment_history FOREIGN KEY (comment_history_id) REFERENCES comment_history (comment_id) ON DELETE CASCADE, + CONSTRAINT fk_history_downtime_history FOREIGN KEY (downtime_history_id) REFERENCES downtime_history (downtime_id) ON DELETE CASCADE, + CONSTRAINT fk_history_flapping_history FOREIGN KEY (flapping_history_id) REFERENCES flapping_history (id) ON DELETE CASCADE, + CONSTRAINT fk_history_notification_history FOREIGN KEY (notification_history_id) REFERENCES notification_history (id) ON DELETE CASCADE, + CONSTRAINT fk_history_state_history FOREIGN KEY (state_history_id) REFERENCES state_history (id) ON DELETE CASCADE, + + INDEX idx_history_event_time (event_time) COMMENT 'History filtered/ordered by event_time', + INDEX idx_history_acknowledgement (acknowledgement_history_id), + INDEX idx_history_comment (comment_history_id), + INDEX idx_history_downtime (downtime_history_id), + INDEX idx_history_flapping (flapping_history_id), + INDEX idx_history_notification (notification_history_id), + INDEX idx_history_state (state_history_id), + INDEX idx_history_host_service_id (host_id, service_id, event_time) COMMENT 'Host/service history detail filter' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE sla_history_state ( + id binary(20) NOT NULL COMMENT 'state_history.id (may reference already deleted rows)', + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + event_time bigint unsigned NOT NULL COMMENT 'unix timestamp the event occurred', + hard_state TINYINT UNSIGNED NOT NULL COMMENT 'hard state after this event', + previous_hard_state TINYINT UNSIGNED NOT NULL COMMENT 'hard state before this event', + + PRIMARY KEY (id), + + INDEX idx_sla_history_state_event (host_id, service_id, event_time) COMMENT 'Filter for calculating the sla reports', + INDEX idx_sla_history_state_env_event_time (environment_id, event_time) COMMENT 'Filter for sla history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE sla_history_downtime ( + environment_id binary(20) NOT NULL COMMENT 'environment.id', + endpoint_id binary(20) DEFAULT NULL COMMENT 'endpoint.id', + object_type enum('host', 'service') NOT NULL, + host_id binary(20) NOT NULL COMMENT 'host.id', + service_id binary(20) DEFAULT NULL COMMENT 'service.id', + + downtime_id binary(20) NOT NULL COMMENT 'downtime.id (may reference already deleted rows)', + downtime_start BIGINT UNSIGNED NOT NULL COMMENT 'start time of the downtime', + downtime_end BIGINT UNSIGNED NOT NULL COMMENT 'end time of the downtime', + + PRIMARY KEY (downtime_id), + + INDEX idx_sla_history_downtime_event (host_id, service_id, downtime_start, downtime_end) COMMENT 'Filter for calculating the sla reports', + INDEX idx_sla_history_downtime_env_downtime_end (environment_id, downtime_end) COMMENT 'Filter for sla history retention' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +CREATE TABLE icingadb_schema ( + id int unsigned NOT NULL AUTO_INCREMENT, + version smallint unsigned NOT NULL, + timestamp bigint unsigned NOT NULL, + + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +INSERT INTO icingadb_schema (version, timestamp) + VALUES (3, CURRENT_TIMESTAMP() * 1000); |