summaryrefslogtreecommitdiffstats
path: root/schema
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-28 12:36:04 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-28 12:36:04 +0000
commitb09c6d56832eb1718c07d74abf3bc6ae3fe4e030 (patch)
treed2caec2610d4ea887803ec9e9c3cd77136c448ba /schema
parentInitial commit. (diff)
downloadicingadb-b09c6d56832eb1718c07d74abf3bc6ae3fe4e030.tar.xz
icingadb-b09c6d56832eb1718c07d74abf3bc6ae3fe4e030.zip
Adding upstream version 1.1.0.upstream/1.1.0upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'schema')
-rw-r--r--schema/mysql/schema.sql1333
-rw-r--r--schema/mysql/upgrades/1.0.0-rc2.sql468
-rw-r--r--schema/mysql/upgrades/1.0.0.sql291
-rw-r--r--schema/pgsql/schema.sql2163
4 files changed, 4255 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);
diff --git a/schema/mysql/upgrades/1.0.0-rc2.sql b/schema/mysql/upgrades/1.0.0-rc2.sql
new file mode 100644
index 0000000..50fb2f9
--- /dev/null
+++ b/schema/mysql/upgrades/1.0.0-rc2.sql
@@ -0,0 +1,468 @@
+ALTER TABLE host_state DROP PRIMARY KEY;
+ALTER TABLE host_state ADD COLUMN id binary(20) NOT NULL COMMENT 'host.id' FIRST;
+UPDATE host_state SET id = host_id;
+ALTER TABLE host_state ADD PRIMARY KEY (id);
+ALTER TABLE host_state ADD COLUMN properties_checksum binary(20) AFTER environment_id;
+UPDATE host_state SET properties_checksum = 0;
+ALTER TABLE host_state MODIFY COLUMN properties_checksum binary(20) COMMENT 'sha1(all properties)' NOT NULL;
+ALTER TABLE host_state ADD UNIQUE INDEX idx_host_state_host_id (host_id);
+
+ALTER TABLE service_state DROP PRIMARY KEY;
+ALTER TABLE service_state ADD COLUMN id binary(20) NOT NULL COMMENT 'service.id' FIRST;
+UPDATE service_state SET id = service_id;
+ALTER TABLE service_state ADD PRIMARY KEY (id);
+ALTER TABLE service_state ADD COLUMN properties_checksum binary(20) AFTER environment_id;
+UPDATE service_state SET properties_checksum = 0;
+ALTER TABLE service_state MODIFY COLUMN properties_checksum binary(20) COMMENT 'sha1(all properties)' NOT NULL;
+ALTER TABLE service_state ADD UNIQUE INDEX idx_service_state_service_id (service_id);
+
+ALTER TABLE downtime
+ ADD COLUMN parent_id binary(20) 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.' AFTER triggered_by_id,
+ MODIFY COLUMN triggered_by_id binary(20) 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.';
+ALTER TABLE downtime_history
+ ADD COLUMN parent_id binary(20) 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.' AFTER triggered_by_id,
+ MODIFY COLUMN triggered_by_id binary(20) 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.';
+
+ALTER TABLE checkcommand_argument MODIFY COLUMN argument_order smallint DEFAULT NULL;
+ALTER TABLE eventcommand_argument MODIFY COLUMN argument_order smallint DEFAULT NULL;
+ALTER TABLE notificationcommand_argument MODIFY COLUMN argument_order smallint DEFAULT NULL;
+
+ALTER TABLE acknowledgement_history MODIFY COLUMN id binary(20) NOT NULL COMMENT 'sha1(environment.name + "Host"|"Service" + host|service.name + set_time)';
+ALTER TABLE flapping_history MODIFY COLUMN id binary(20) NOT NULL COMMENT 'sha1(environment.name + "Host"|"Service" + host|service.name + start_time)';
+
+ALTER TABLE host ADD INDEX idx_host_name_ci (name_ci) COMMENT 'Host list filtered using quick search';
+ALTER TABLE service ADD INDEX idx_service_name_ci (name_ci) COMMENT 'Service list filtered using quick search';
+
+ALTER TABLE user ADD INDEX idx_user_name_ci (name_ci) COMMENT 'User list filtered using quick search';
+ALTER TABLE user ADD INDEX idx_user_name (name) COMMENT 'User list filtered/ordered by name; User detail filter';
+
+ALTER TABLE usergroup ADD INDEX `idx_usergroup_display_name` (`display_name`) COMMENT 'Usergroup list filtered/ordered by display_name';
+ALTER TABLE usergroup ADD INDEX idx_usergroup_name_ci (name_ci) COMMENT 'Usergroup list filtered using quick search';
+ALTER TABLE usergroup ADD INDEX idx_usergroup_name (name) COMMENT 'Usergroup list filtered/ordered by name; Usergroup detail filter';
+
+ALTER TABLE hostgroup_customvar
+ ADD INDEX idx_hostgroup_customvar_hostgroup_id (hostgroup_id, customvar_id),
+ ADD INDEX idx_hostgroup_customvar_customvar_id (customvar_id, hostgroup_id);
+ALTER TABLE servicegroup_customvar
+ ADD INDEX idx_servicegroup_customvar_servicegroup_id (servicegroup_id, customvar_id),
+ ADD INDEX idx_servicegroup_customvar_customvar_id (customvar_id, servicegroup_id);
+ALTER TABLE checkcommand_customvar
+ ADD INDEX idx_checkcommand_customvar_command_id (command_id, customvar_id),
+ ADD INDEX idx_checkcommand_customvar_customvar_id (customvar_id, command_id);
+ALTER TABLE eventcommand_customvar
+ ADD INDEX idx_eventcommand_customvar_command_id (command_id, customvar_id),
+ ADD INDEX idx_eventcommand_customvar_customvar_id (customvar_id, command_id);
+ALTER TABLE notificationcommand_customvar
+ ADD INDEX idx_notificationcommand_customvar_command_id (command_id, customvar_id),
+ ADD INDEX idx_notificationcommand_customvar_customvar_id (customvar_id, command_id);
+ALTER TABLE notification_customvar
+ ADD INDEX idx_notification_customvar_notification_id (notification_id, customvar_id),
+ ADD INDEX idx_notification_customvar_customvar_id (customvar_id, notification_id);
+ALTER TABLE timeperiod_customvar
+ ADD INDEX idx_timeperiod_customvar_timeperiod_id (timeperiod_id, customvar_id),
+ ADD INDEX idx_timeperiod_customvar_customvar_id (customvar_id, timeperiod_id);
+ALTER TABLE user_customvar
+ ADD INDEX idx_user_customvar_user_id (user_id, customvar_id),
+ ADD INDEX idx_user_customvar_customvar_id (customvar_id, user_id);
+ALTER TABLE usergroup_customvar
+ ADD INDEX idx_usergroup_customvar_usergroup_id (usergroup_id, customvar_id),
+ ADD INDEX idx_usergroup_customvar_customvar_id (customvar_id, usergroup_id);
+
+ALTER TABLE host
+ MODIFY active_checks_enabled enum('n','y') NOT NULL,
+ MODIFY passive_checks_enabled enum('n','y') NOT NULL,
+ MODIFY event_handler_enabled enum('n','y') NOT NULL,
+ MODIFY notifications_enabled enum('n','y') NOT NULL,
+ MODIFY flapping_enabled enum('n','y') NOT NULL,
+ MODIFY perfdata_enabled enum('n','y') NOT NULL,
+ MODIFY is_volatile enum('n','y') NOT NULL;
+ALTER TABLE host_state
+ ADD COLUMN normalized_performance_data longtext DEFAULT NULL AFTER performance_data,
+ ADD COLUMN last_comment_id binary(20) DEFAULT NULL COMMENT 'comment.id' AFTER acknowledgement_comment_id,
+ ADD COLUMN scheduling_source text DEFAULT NULL AFTER check_source,
+ MODIFY is_problem enum('n','y') NOT NULL,
+ MODIFY is_handled enum('n','y') NOT NULL,
+ MODIFY is_reachable enum('n','y') NOT NULL,
+ MODIFY is_flapping enum('n','y') NOT NULL,
+ MODIFY is_overdue enum('n','y') NOT NULL,
+ MODIFY is_acknowledged enum('n','y','sticky') NOT NULL,
+ MODIFY in_downtime enum('n','y') NOT NULL;
+ALTER TABLE service
+ MODIFY active_checks_enabled enum('n','y') NOT NULL,
+ MODIFY passive_checks_enabled enum('n','y') NOT NULL,
+ MODIFY event_handler_enabled enum('n','y') NOT NULL,
+ MODIFY notifications_enabled enum('n','y') NOT NULL,
+ MODIFY flapping_enabled enum('n','y') NOT NULL,
+ MODIFY perfdata_enabled enum('n','y') NOT NULL,
+ MODIFY is_volatile enum('n','y') NOT NULL;
+ALTER TABLE service_state
+ ADD COLUMN normalized_performance_data longtext DEFAULT NULL AFTER performance_data,
+ ADD COLUMN last_comment_id binary(20) DEFAULT NULL COMMENT 'comment.id' AFTER acknowledgement_comment_id,
+ ADD COLUMN scheduling_source text DEFAULT NULL AFTER check_source,
+ MODIFY is_problem enum('n','y') NOT NULL,
+ MODIFY is_handled enum('n','y') NOT NULL,
+ MODIFY is_reachable enum('n','y') NOT NULL,
+ MODIFY is_flapping enum('n','y') NOT NULL,
+ MODIFY is_overdue enum('n','y') NOT NULL,
+ MODIFY is_acknowledged enum('n','y','sticky') NOT NULL,
+ MODIFY in_downtime enum('n','y') NOT NULL;
+ALTER TABLE icingadb_instance
+ MODIFY responsible enum('n','y') NOT NULL,
+ MODIFY icinga2_notifications_enabled enum('n','y') NOT NULL,
+ MODIFY icinga2_active_service_checks_enabled enum('n','y') NOT NULL,
+ MODIFY icinga2_active_host_checks_enabled enum('n','y') NOT NULL,
+ MODIFY icinga2_event_handlers_enabled enum('n','y') NOT NULL,
+ MODIFY icinga2_flap_detection_enabled enum('n','y') NOT NULL,
+ MODIFY icinga2_performance_data_enabled enum('n','y') NOT NULL;
+ALTER TABLE checkcommand_argument
+ MODIFY repeat_key enum('n','y') NOT NULL,
+ MODIFY required enum('n','y') NOT NULL,
+ MODIFY skip_key enum('n','y') NOT NULL;
+ALTER TABLE eventcommand_argument
+ MODIFY repeat_key enum('n','y') NOT NULL,
+ MODIFY required enum('n','y') NOT NULL,
+ MODIFY skip_key enum('n','y') NOT NULL;
+ALTER TABLE notificationcommand_argument
+ MODIFY repeat_key enum('n','y') NOT NULL,
+ MODIFY required enum('n','y') NOT NULL,
+ MODIFY skip_key enum('n','y') NOT NULL;
+ALTER TABLE comment
+ MODIFY name varchar(548) NOT NULL COMMENT '255+1+255+1+36, i.e. "host.name!service.name!UUID"',
+ MODIFY is_persistent enum('n','y') NOT NULL,
+ MODIFY is_sticky enum('n','y') NOT NULL;
+ALTER TABLE downtime
+ ADD COLUMN 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"' AFTER end_time,
+ MODIFY name varchar(548) NOT NULL COMMENT '255+1+255+1+36, i.e. "host.name!service.name!UUID"',
+ MODIFY is_flexible enum('n','y') NOT NULL,
+ MODIFY is_in_effect enum('n','y') NOT NULL;
+ALTER TABLE timeperiod
+ MODIFY prefer_includes enum('n','y') NOT NULL;
+ALTER TABLE user
+ MODIFY notifications_enabled enum('n','y') NOT NULL;
+ALTER TABLE zone
+ MODIFY is_global enum('n','y') NOT NULL;
+ALTER TABLE downtime_history
+ ADD COLUMN 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"' AFTER end_time,
+ MODIFY is_flexible enum('n','y') NOT NULL,
+ MODIFY has_been_cancelled enum('n','y') NOT NULL;
+ALTER TABLE comment_history
+ MODIFY is_persistent enum('n','y') NOT NULL,
+ MODIFY is_sticky enum('n','y') NOT NULL,
+ MODIFY has_been_removed enum('n','y') NOT NULL;
+ALTER TABLE acknowledgement_history
+ MODIFY author varchar(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci COMMENT 'NULL if ack_set event happened before Icinga DB history recording',
+ MODIFY comment text DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording',
+ MODIFY is_sticky enum('n','y') DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording',
+ MODIFY is_persistent enum('n','y') DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording';
+
+INSERT INTO icingadb_schema (version, timestamp)
+ VALUES (2, CURRENT_TIMESTAMP() * 1000);
+
+ALTER TABLE host_state
+ MODIFY output longtext DEFAULT NULL,
+ MODIFY long_output longtext DEFAULT NULL,
+ MODIFY performance_data longtext DEFAULT NULL;
+
+ALTER TABLE state_history
+ ADD COLUMN scheduling_source text DEFAULT NULL AFTER check_source,
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.name + host|service.name + event_time)',
+ MODIFY output longtext DEFAULT NULL,
+ MODIFY long_output longtext DEFAULT NULL;
+
+ALTER TABLE service_state
+ MODIFY output longtext DEFAULT NULL,
+ MODIFY long_output longtext DEFAULT NULL,
+ MODIFY performance_data longtext DEFAULT NULL;
+
+ALTER TABLE notification_history
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.name + notification.name + type + send_time)';
+
+ALTER TABLE user_notification_history
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(notification_history_id + user_id)',
+ MODIFY notification_history_id binary(20) NOT NULL COMMENT 'UUID notification_history.id';
+
+ALTER TABLE history
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.name + event_type + x...) given that sha1(environment.name + x...) = *_history_id',
+ MODIFY notification_history_id binary(20) DEFAULT NULL COMMENT 'notification_history.id',
+ MODIFY state_history_id binary(20) DEFAULT NULL COMMENT 'state_history.id',
+ ADD CONSTRAINT fk_history_acknowledgement_history FOREIGN KEY (acknowledgement_history_id) REFERENCES acknowledgement_history (id) ON DELETE CASCADE,
+ ADD CONSTRAINT fk_history_comment_history FOREIGN KEY (comment_history_id) REFERENCES comment_history (comment_id) ON DELETE CASCADE,
+ ADD CONSTRAINT fk_history_downtime_history FOREIGN KEY (downtime_history_id) REFERENCES downtime_history (downtime_id) ON DELETE CASCADE,
+ ADD CONSTRAINT fk_history_flapping_history FOREIGN KEY (flapping_history_id) REFERENCES flapping_history (id) ON DELETE CASCADE,
+ ADD CONSTRAINT fk_history_notification_history FOREIGN KEY (notification_history_id) REFERENCES notification_history (id) ON DELETE CASCADE,
+ ADD CONSTRAINT fk_history_state_history FOREIGN KEY (state_history_id) REFERENCES state_history (id) ON DELETE CASCADE;
+
+ALTER TABLE user_notification_history
+ ADD CONSTRAINT fk_user_notification_history_notification_history FOREIGN KEY (notification_history_id) REFERENCES notification_history (id) ON DELETE CASCADE;
+
+ALTER TABLE host
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE hostgroup
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE hostgroup_member
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + host_id + hostgroup_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE host_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + host_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE hostgroup_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + hostgroup_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE host_state
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE service
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE servicegroup
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE servicegroup_member
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + servicegroup_id + service_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE service_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + service_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE servicegroup_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + servicegroup_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE service_state
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE endpoint
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE environment
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(Icinga CA public key)';
+ALTER TABLE checkcommand
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)';
+ALTER TABLE checkcommand_argument
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + argument_key)';
+ALTER TABLE checkcommand_envvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + envvar_key)';
+ALTER TABLE checkcommand_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE eventcommand
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)';
+ALTER TABLE eventcommand_argument
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + argument_key)';
+ALTER TABLE eventcommand_envvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + envvar_key)';
+ALTER TABLE eventcommand_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE notificationcommand
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)';
+ALTER TABLE notificationcommand_argument
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + argument_key)';
+ALTER TABLE notificationcommand_envvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + envvar_key)';
+ALTER TABLE notificationcommand_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE comment
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)';
+ALTER TABLE downtime
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)';
+ALTER TABLE notification
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE notification_user
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + user_id)';
+ALTER TABLE notification_usergroup
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + usergroup_id)';
+ALTER TABLE notification_recipient
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + (user_id | usergroup_id))';
+ALTER TABLE notification_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE icon_image
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE action_url
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE notes_url
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE timeperiod
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)';
+ALTER TABLE timeperiod_range
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + range_id + timeperiod_id)';
+ALTER TABLE timeperiod_override_include
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + include_id + timeperiod_id)';
+ALTER TABLE timeperiod_override_exclude
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + exclude_id + timeperiod_id)';
+ALTER TABLE timeperiod_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + timeperiod_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name + value)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE customvar_flat
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + flatname + flatvalue)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE user
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE usergroup
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE usergroup_member
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + usergroup_id + user_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE user_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + user_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE usergroup_customvar
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + usergroup_id + customvar_id)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE zone
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
+ MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
+ALTER TABLE flapping_history
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + "Host"|"Service" + host|service.name + start_time)';
+ALTER TABLE acknowledgement_history
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + "Host"|"Service" + host|service.name + set_time)';
+
+/*
+ * Schema changes after https://github.com/Icinga/icingadb/pull/403:
+ */
+
+ALTER TABLE checkcommand_customvar
+ DROP INDEX idx_checkcommand_customvar_command_id,
+ DROP INDEX idx_checkcommand_customvar_customvar_id;
+
+ALTER TABLE eventcommand_customvar
+ DROP INDEX idx_eventcommand_customvar_command_id,
+ DROP INDEX idx_eventcommand_customvar_customvar_id;
+
+ALTER TABLE notificationcommand_customvar
+ DROP INDEX idx_notificationcommand_customvar_command_id,
+ DROP INDEX idx_notificationcommand_customvar_customvar_id;
+
+ALTER TABLE notification
+ RENAME COLUMN command_id TO notificationcommand_id;
+ALTER TABLE notification
+ MODIFY notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id';
+
+ALTER TABLE checkcommand_argument
+ RENAME COLUMN command_id TO checkcommand_id;
+ALTER TABLE checkcommand_argument
+ MODIFY checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + argument_key)';
+
+ALTER TABLE checkcommand_envvar
+ RENAME COLUMN command_id TO checkcommand_id;
+ALTER TABLE checkcommand_envvar
+ MODIFY checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + envvar_key)';
+
+ALTER TABLE checkcommand_customvar
+ RENAME COLUMN command_id TO checkcommand_id;
+ALTER TABLE checkcommand_customvar
+ MODIFY checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + customvar_id)';
+
+ALTER TABLE eventcommand_argument
+ RENAME COLUMN command_id TO eventcommand_id;
+ALTER TABLE eventcommand_argument
+ MODIFY eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + argument_key)';
+
+ALTER TABLE eventcommand_envvar
+ RENAME COLUMN command_id TO eventcommand_id;
+ALTER TABLE eventcommand_envvar
+ MODIFY eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + envvar_key)';
+
+ALTER TABLE eventcommand_customvar
+ RENAME COLUMN command_id TO eventcommand_id;
+ALTER TABLE eventcommand_customvar
+ MODIFY eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + customvar_id)';
+
+ALTER TABLE notificationcommand_argument
+ RENAME COLUMN command_id TO notificationcommand_id;
+ALTER TABLE notificationcommand_argument
+ MODIFY notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + argument_key)';
+
+ALTER TABLE notificationcommand_envvar
+ RENAME COLUMN command_id TO notificationcommand_id;
+ALTER TABLE notificationcommand_envvar
+ MODIFY notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + envvar_key)';
+
+ALTER TABLE notificationcommand_customvar
+ RENAME COLUMN command_id TO notificationcommand_id;
+ALTER TABLE notificationcommand_customvar
+ MODIFY notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id',
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + customvar_id)';
+
+ALTER TABLE checkcommand_customvar
+ ADD INDEX idx_checkcommand_customvar_checkcommand_id (checkcommand_id, customvar_id),
+ ADD INDEX idx_checkcommand_customvar_customvar_id (customvar_id, checkcommand_id);
+
+ALTER TABLE eventcommand_customvar
+ ADD INDEX idx_eventcommand_customvar_eventcommand_id (eventcommand_id, customvar_id),
+ ADD INDEX idx_eventcommand_customvar_customvar_id (customvar_id, eventcommand_id);
+
+ALTER TABLE notificationcommand_customvar
+ ADD INDEX idx_notificationcommand_customvar_notificationcommand_id (notificationcommand_id, customvar_id),
+ ADD INDEX idx_notificationcommand_customvar_customvar_id (customvar_id, notificationcommand_id);
+
+ALTER TABLE downtime
+ ADD COLUMN scheduled_duration bigint unsigned NOT NULL AFTER scheduled_end_time,
+ ADD COLUMN 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' AFTER end_time,
+ MODIFY COLUMN flexible_duration bigint unsigned NOT NULL AFTER is_flexible;
+UPDATE downtime SET scheduled_duration = scheduled_end_time - scheduled_start_time, duration = (CASE WHEN is_flexible = 'y' THEN flexible_duration ELSE scheduled_end_time - scheduled_start_time END) WHERE scheduled_duration = 0;
+
+ALTER TABLE service_state ADD COLUMN host_id binary(20) NOT NULL COMMENT 'host.id' AFTER id;
+UPDATE service_state INNER JOIN service ON service.id = service_state.service_id SET service_state.host_id = service.host_id WHERE service_state.host_id = REPEAT('\0', 20);
+
+ALTER TABLE comment
+ ADD INDEX idx_comment_author (author) COMMENT 'Comment list filtered/ordered by author',
+ ADD INDEX idx_comment_expire_time (expire_time) COMMENT 'Comment list filtered/ordered by expire_time';
+
+ALTER TABLE downtime
+ ADD INDEX idx_downtime_entry_time (entry_time) COMMENT 'Downtime list filtered/ordered by entry_time',
+ ADD INDEX idx_downtime_start_time (start_time) COMMENT 'Downtime list filtered/ordered by start_time',
+ ADD INDEX idx_downtime_end_time (end_time) COMMENT 'Downtime list filtered/ordered by end_time',
+ ADD INDEX idx_downtime_scheduled_start_time (scheduled_start_time) COMMENT 'Downtime list filtered/ordered by scheduled_start_time',
+ ADD INDEX idx_downtime_scheduled_end_time (scheduled_end_time) COMMENT 'Downtime list filtered/ordered by scheduled_end_time',
+ ADD INDEX idx_downtime_author (author) COMMENT 'Downtime list filtered/ordered by author',
+ ADD INDEX idx_downtime_duration (duration) COMMENT 'Downtime list filtered/ordered by duration';
+
+ALTER TABLE service_state
+ ADD INDEX idx_service_state_is_problem (is_problem, severity) COMMENT 'Service list filtered by is_problem ordered by severity',
+ ADD INDEX idx_service_state_severity (severity) COMMENT 'Service list filtered/ordered by severity',
+ ADD INDEX idx_service_state_soft_state (soft_state, last_state_change) COMMENT 'Service list filtered/ordered by soft_state; recently recovered filter',
+ ADD INDEX idx_service_state_last_state_change (last_state_change) COMMENT 'Service list filtered/ordered by last_state_change';
+
+ALTER TABLE host_state
+ ADD INDEX idx_host_state_is_problem (is_problem, severity) COMMENT 'Host list filtered by is_problem ordered by severity',
+ ADD INDEX idx_host_state_severity (severity) COMMENT 'Host list filtered/ordered by severity',
+ ADD INDEX idx_host_state_soft_state (soft_state, last_state_change) COMMENT 'Host list filtered/ordered by soft_state; recently recovered filter',
+ ADD INDEX idx_host_state_last_state_change (last_state_change) COMMENT 'Host list filtered/ordered by last_state_change';
+
+ALTER TABLE hostgroup
+ ADD INDEX idx_hostroup_name (name) COMMENT 'Host/service/host group list filtered by host group name';
+
+ALTER TABLE servicegroup
+ ADD INDEX idx_servicegroup_name (name) COMMENT 'Host/service/service group list filtered by service group name';
+
+ALTER TABLE notification
+ DROP INDEX idx_host_id,
+ DROP INDEX idx_service_id,
+ ADD INDEX idx_notification_host_id (host_id),
+ ADD INDEX idx_notification_service_id (service_id);
+
+ALTER TABLE zone
+ DROP INDEX idx_parent_id,
+ ADD INDEX idx_zone_parent_id (parent_id);
+
+ALTER TABLE history
+ ADD INDEX idx_history_host_service_id (host_id, service_id, event_time) COMMENT 'Host/service history detail filter';
+
+ALTER TABLE notification_history
+ DROP INDEX idx_notification_history_event_time,
+ ADD INDEX idx_notification_history_send_time (send_time) COMMENT 'Notification list filtered/ordered by send_time';
diff --git a/schema/mysql/upgrades/1.0.0.sql b/schema/mysql/upgrades/1.0.0.sql
new file mode 100644
index 0000000..16bb45e
--- /dev/null
+++ b/schema/mysql/upgrades/1.0.0.sql
@@ -0,0 +1,291 @@
+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 ;
+
+ALTER TABLE hostgroup
+ DROP INDEX idx_hostroup_name,
+ ADD INDEX idx_hostgroup_name (name) COMMENT 'Host/service/host group list filtered by host group name';
+
+ALTER TABLE notification_history
+ MODIFY `text` longtext NOT NULL;
+
+ALTER TABLE host_state
+ ADD COLUMN previous_soft_state tinyint unsigned NOT NULL AFTER hard_state,
+ CHANGE attempt check_attempt tinyint unsigned NOT NULL,
+ CHANGE timeout check_timeout int unsigned DEFAULT NULL;
+
+ALTER TABLE service_state
+ ADD COLUMN previous_soft_state tinyint unsigned NOT NULL AFTER hard_state,
+ CHANGE attempt check_attempt tinyint unsigned NOT NULL,
+ CHANGE timeout check_timeout int unsigned DEFAULT NULL;
+
+ALTER TABLE checkcommand_argument
+ ADD COLUMN `separator` varchar(255) DEFAULT NULL AFTER set_if;
+
+ALTER TABLE eventcommand_argument
+ ADD COLUMN `separator` varchar(255) DEFAULT NULL AFTER set_if;
+
+ALTER TABLE notificationcommand_argument
+ ADD COLUMN `separator` varchar(255) DEFAULT NULL AFTER set_if;
+
+ALTER TABLE notification_history
+ ADD INDEX idx_notification_history_env_send_time (environment_id, send_time) COMMENT 'Filter for history retention';
+
+ALTER TABLE acknowledgement_history
+ ADD INDEX idx_acknowledgement_history_env_clear_time (environment_id, clear_time) COMMENT 'Filter for history retention';
+
+ALTER TABLE comment_history
+ ADD INDEX idx_comment_history_env_remove_time (environment_id, remove_time) COMMENT 'Filter for history retention';
+
+ALTER TABLE downtime_history
+ ADD INDEX idx_downtime_history_env_end_time (environment_id, end_time) COMMENT 'Filter for history retention';
+
+ALTER TABLE flapping_history
+ ADD INDEX idx_flapping_history_env_end_time (environment_id, end_time) COMMENT 'Filter for history retention';
+
+ALTER TABLE state_history
+ ADD INDEX idx_state_history_env_event_time (environment_id, event_time) COMMENT 'Filter for history retention',
+ CHANGE attempt check_attempt tinyint unsigned NOT NULL;
+
+ALTER TABLE icon_image
+ DROP PRIMARY KEY,
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + icon_image)',
+ ADD PRIMARY KEY (id);
+
+ALTER TABLE action_url
+ DROP PRIMARY KEY,
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + action_url)',
+ ADD PRIMARY KEY (id);
+
+ALTER TABLE notes_url
+ DROP PRIMARY KEY,
+ MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notes_url)',
+ ADD PRIMARY KEY (id);
+
+ALTER TABLE customvar
+ MODIFY name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL;
+
+ALTER TABLE customvar_flat
+ MODIFY flatname varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Path converted with `.` and `[ ]`';
+
+ALTER TABLE host
+ CHANGE checkcommand checkcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'checkcommand.name',
+ CHANGE check_timeperiod
+ check_timeperiod_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'timeperiod.name',
+ CHANGE eventcommand eventcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'eventcommand.name',
+ CHANGE zone zone_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'zone.name',
+ CHANGE command_endpoint command_endpoint_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'endpoint.name';
+
+ALTER TABLE service
+ CHANGE checkcommand checkcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'checkcommand.name',
+ CHANGE check_timeperiod
+ check_timeperiod_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'timeperiod.name',
+ CHANGE eventcommand eventcommand_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'eventcommand.name',
+ CHANGE zone zone_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'zone.name',
+ CHANGE command_endpoint command_endpoint_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'endpoint.name';
+
+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;
+
+INSERT INTO sla_history_state
+ (id, environment_id, endpoint_id, object_type, host_id, service_id, event_time, hard_state, previous_hard_state)
+ SELECT id, environment_id, endpoint_id, object_type, host_id, service_id, event_time, hard_state, previous_hard_state
+ FROM state_history
+ WHERE state_type = 'hard'
+ ON DUPLICATE KEY UPDATE sla_history_state.id = sla_history_state.id;
+
+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;
+
+INSERT INTO sla_history_downtime
+ (environment_id, endpoint_id, object_type, host_id, service_id, downtime_id, downtime_start, downtime_end)
+ SELECT environment_id, endpoint_id, object_type, host_id, service_id, downtime_id,
+ start_time AS downtime_start, IF(has_been_cancelled = 'y', cancel_time, end_time) AS downtime_end
+ FROM downtime_history
+ ON DUPLICATE KEY UPDATE sla_history_downtime.downtime_id = sla_history_downtime.downtime_id;
+
+INSERT INTO icingadb_schema (version, TIMESTAMP)
+ VALUES (3, CURRENT_TIMESTAMP() * 1000);
diff --git a/schema/pgsql/schema.sql b/schema/pgsql/schema.sql
new file mode 100644
index 0000000..b3b5be0
--- /dev/null
+++ b/schema/pgsql/schema.sql
@@ -0,0 +1,2163 @@
+-- Icinga DB | (c) 2021 Icinga GmbH | GPLv2+
+
+-- Postgres in Docker: ensure CITEXT columns are available during schema import. DB user is a superuser and can do this unconditionally.
+-- Everything else: assert CITEXT columns are available during schema import. DB user isn't the superuser and can do this only if it's a no-op (`NOTICE: extension "citext" already exists, skipping`), i.e. if CITEXT columns are already available.
+CREATE EXTENSION IF NOT EXISTS citext;
+
+CREATE DOMAIN bytea20 AS bytea CONSTRAINT exactly_20_bytes_long CHECK ( VALUE IS NULL OR octet_length(VALUE) = 20 );
+CREATE DOMAIN bytea16 AS bytea CONSTRAINT exactly_16_bytes_long CHECK ( VALUE IS NULL OR octet_length(VALUE) = 16 );
+CREATE DOMAIN bytea4 AS bytea CONSTRAINT exactly_4_bytes_long CHECK ( VALUE IS NULL OR octet_length(VALUE) = 4 );
+
+CREATE DOMAIN biguint AS bigint CONSTRAINT positive CHECK ( VALUE IS NULL OR 0 <= VALUE );
+CREATE DOMAIN uint AS bigint CONSTRAINT between_0_and_4294967295 CHECK ( VALUE IS NULL OR VALUE BETWEEN 0 AND 4294967295 );
+CREATE DOMAIN smalluint AS int CONSTRAINT between_0_and_65535 CHECK ( VALUE IS NULL OR VALUE BETWEEN 0 AND 65535 );
+CREATE DOMAIN tinyuint AS smallint CONSTRAINT between_0_and_255 CHECK ( VALUE IS NULL OR VALUE BETWEEN 0 AND 255 );
+
+CREATE TYPE boolenum AS ENUM ( 'n', 'y' );
+CREATE TYPE acked AS ENUM ( 'n', 'y', 'sticky' );
+CREATE TYPE state_type AS ENUM ( 'hard', 'soft' );
+CREATE TYPE checkable_type AS ENUM ( 'host', 'service' );
+CREATE TYPE comment_type AS ENUM ( 'comment', 'ack' );
+CREATE TYPE notification_type AS ENUM ( 'downtime_start', 'downtime_end', 'downtime_removed', 'custom', 'acknowledgement', 'problem', 'recovery', 'flapping_start', 'flapping_end' );
+CREATE TYPE history_type AS ENUM ( 'notification', 'state_change', 'downtime_start', 'downtime_end', 'comment_add', 'comment_remove', 'flapping_start', 'flapping_end', 'ack_set', 'ack_clear' );
+
+CREATE OR REPLACE FUNCTION get_sla_ok_percent(
+ in_host_id bytea20,
+ in_service_id bytea20,
+ in_start_time biguint,
+ in_end_time biguint
+)
+RETURNS decimal(7, 4)
+LANGUAGE plpgsql
+STABLE
+PARALLEL RESTRICTED
+AS $$
+DECLARE
+ last_event_time biguint := in_start_time;
+ last_hard_state tinyuint;
+ active_downtimes uint := 0;
+ problem_time biguint := 0;
+ total_time biguint;
+ row record;
+BEGIN
+ IF in_end_time <= in_start_time THEN
+ RAISE 'end time must be greater than start time';
+ END IF;
+
+ total_time := in_end_time - in_start_time;
+
+ -- 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
+ last_hard_state := 0;
+ END IF;
+
+ FOR row IN
+ (
+ -- 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::tinyuint AS hard_state,
+ NULL::tinyuint 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::tinyuint AS hard_state,
+ NULL::tinyuint 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::tinyuint AS hard_state,
+ NULL::tinyuint AS previous_hard_state
+ )
+ ORDER BY event_time, event_prio
+ LOOP
+ IF row.previous_hard_state = 99 THEN
+ 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
+ problem_time := problem_time + row.event_time - last_event_time;
+ END IF;
+
+ last_event_time := row.event_time;
+ IF row.event_type = 'state_change' THEN
+ last_hard_state := row.hard_state;
+ ELSEIF row.event_type = 'downtime_start' THEN
+ active_downtimes := active_downtimes + 1;
+ ELSEIF row.event_type = 'downtime_end' THEN
+ active_downtimes := active_downtimes - 1;
+ END IF;
+ END LOOP;
+
+ RETURN 100 * (total_time - problem_time) / total_time;
+END;
+$$;
+
+/* At the moment Icinga DB Web doesn't know the column types,
+ so it sends SQL queries with LIKE operators for all suggestions in the search bar,
+ which fails for numeric and enum types on PostgreSQL.
+ To support this, the LIKE operator (internally translated to ~~) is overloaded.
+ Note that this is only a temporary solution until Icinga DB Web provides column type support.
+ */
+CREATE OR REPLACE FUNCTION anynonarrayliketext(anynonarray, text)
+ RETURNS bool
+ LANGUAGE plpgsql
+ IMMUTABLE
+ PARALLEL SAFE
+ AS $$
+ BEGIN
+ RETURN $1::TEXT LIKE $2;
+ END;
+ $$;
+CREATE OPERATOR ~~ (LEFTARG=anynonarray, RIGHTARG=text, PROCEDURE=anynonarrayliketext);
+
+CREATE TABLE host (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ display_name citext NOT NULL,
+
+ address varchar(255) NOT NULL,
+ address6 varchar(255) NOT NULL,
+ address_bin bytea4 DEFAULT NULL,
+ address6_bin bytea16 DEFAULT NULL,
+
+ checkcommand_name citext NOT NULL,
+ checkcommand_id bytea20 NOT NULL,
+
+ max_check_attempts uint NOT NULL,
+
+ check_timeperiod_name citext NOT NULL,
+ check_timeperiod_id bytea20 DEFAULT NULL,
+
+ check_timeout uint DEFAULT NULL,
+ check_interval uint NOT NULL,
+ check_retry_interval uint NOT NULL,
+
+ active_checks_enabled boolenum NOT NULL DEFAULT 'n',
+ passive_checks_enabled boolenum NOT NULL DEFAULT 'n',
+ event_handler_enabled boolenum NOT NULL DEFAULT 'n',
+ notifications_enabled boolenum NOT NULL DEFAULT 'n',
+
+ flapping_enabled boolenum NOT NULL DEFAULT 'n',
+ flapping_threshold_low float NOT NULL,
+ flapping_threshold_high float NOT NULL,
+
+ perfdata_enabled boolenum NOT NULL DEFAULT 'n',
+
+ eventcommand_name citext NOT NULL,
+ eventcommand_id bytea20 DEFAULT NULL,
+
+ is_volatile boolenum NOT NULL DEFAULT 'n',
+
+ action_url_id bytea20 DEFAULT NULL,
+ notes_url_id bytea20 DEFAULT NULL,
+ notes text NOT NULL,
+ icon_image_id bytea20 DEFAULT NULL,
+ icon_image_alt varchar(32) NOT NULL,
+
+ zone_name citext NOT NULL,
+ zone_id bytea20 DEFAULT NULL,
+
+ command_endpoint_name citext NOT NULL,
+ command_endpoint_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_host PRIMARY KEY (id)
+);
+
+ALTER TABLE host ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN address_bin SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN address6_bin SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN checkcommand_id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN check_timeperiod_id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN eventcommand_id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN action_url_id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN notes_url_id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN icon_image_id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN zone_id SET STORAGE PLAIN;
+ALTER TABLE host ALTER COLUMN command_endpoint_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_action_url_checksum ON host(action_url_id);
+CREATE INDEX idx_notes_url_checksum ON host(notes_url_id);
+CREATE INDEX idx_icon_image_checksum ON host(icon_image_id);
+CREATE INDEX idx_host_display_name ON host(display_name);
+CREATE INDEX idx_host_name_ci ON host(name_ci);
+CREATE INDEX idx_host_name ON host(name);
+
+COMMENT ON COLUMN host.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN host.environment_id IS 'environment.id';
+COMMENT ON COLUMN host.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN host.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN host.checkcommand_name IS 'checkcommand.name';
+COMMENT ON COLUMN host.checkcommand_id IS 'checkcommand.id';
+COMMENT ON COLUMN host.check_timeperiod_name IS 'timeperiod.name';
+COMMENT ON COLUMN host.check_timeperiod_id IS 'timeperiod.id';
+COMMENT ON COLUMN host.eventcommand_name IS 'eventcommand.name';
+COMMENT ON COLUMN host.eventcommand_id IS 'eventcommand.id';
+COMMENT ON COLUMN host.action_url_id IS 'action_url.id';
+COMMENT ON COLUMN host.notes_url_id IS 'notes_url.id';
+COMMENT ON COLUMN host.icon_image_id IS 'icon_image.id';
+COMMENT ON COLUMN host.zone_name IS 'zone.name';
+COMMENT ON COLUMN host.zone_id IS 'zone.id';
+COMMENT ON COLUMN host.command_endpoint_name IS 'endpoint.name';
+COMMENT ON COLUMN host.command_endpoint_id IS 'endpoint.id';
+
+COMMENT ON INDEX idx_action_url_checksum IS 'cleanup';
+COMMENT ON INDEX idx_notes_url_checksum IS 'cleanup';
+COMMENT ON INDEX idx_icon_image_checksum IS 'cleanup';
+COMMENT ON INDEX idx_host_display_name IS 'Host list filtered/ordered by display_name';
+COMMENT ON INDEX idx_host_name_ci IS 'Host list filtered using quick search';
+COMMENT ON INDEX idx_host_name IS 'Host list filtered/ordered by name; Host detail filter';
+
+CREATE TABLE hostgroup (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ display_name citext NOT NULL,
+
+ zone_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_hostgroup PRIMARY KEY (id)
+);
+
+ALTER TABLE hostgroup ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE hostgroup ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE hostgroup ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE hostgroup ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE hostgroup ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_hostgroup_name ON hostgroup(name);
+
+COMMENT ON COLUMN hostgroup.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN hostgroup.environment_id IS 'environment.id';
+COMMENT ON COLUMN hostgroup.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN hostgroup.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN hostgroup.zone_id IS 'zone.id';
+
+COMMENT ON INDEX idx_hostgroup_name IS 'Host/service/host group list filtered by host group name';
+
+CREATE TABLE hostgroup_member (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ host_id bytea20 NOT NULL,
+ hostgroup_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_hostgroup_member PRIMARY KEY (id)
+);
+
+ALTER TABLE hostgroup_member ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE hostgroup_member ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE hostgroup_member ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE hostgroup_member ALTER COLUMN hostgroup_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_hostgroup_member_host_id ON hostgroup_member(host_id, hostgroup_id);
+CREATE INDEX idx_hostgroup_member_hostgroup_id ON hostgroup_member(hostgroup_id, host_id);
+
+COMMENT ON COLUMN hostgroup_member.id IS 'sha1(environment.id + host_id + hostgroup_id)';
+COMMENT ON COLUMN hostgroup_member.environment_id IS 'environment.id';
+COMMENT ON COLUMN hostgroup_member.host_id IS 'host.id';
+COMMENT ON COLUMN hostgroup_member.hostgroup_id IS 'hostgroup.id';
+
+CREATE TABLE host_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ host_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_host_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE host_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE host_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE host_customvar ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE host_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_host_customvar_host_id ON host_customvar(host_id, customvar_id);
+CREATE INDEX idx_host_customvar_customvar_id ON host_customvar(customvar_id, host_id);
+
+COMMENT ON COLUMN host_customvar.id IS 'sha1(environment.id + host_id + customvar_id)';
+COMMENT ON COLUMN host_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN host_customvar.host_id IS 'host.id';
+COMMENT ON COLUMN host_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE hostgroup_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ hostgroup_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_hostgroup_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE hostgroup_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE hostgroup_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE hostgroup_customvar ALTER COLUMN hostgroup_id SET STORAGE PLAIN;
+ALTER TABLE hostgroup_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_hostgroup_customvar_hostgroup_id ON hostgroup_customvar(hostgroup_id, customvar_id);
+CREATE INDEX idx_hostgroup_customvar_customvar_id ON hostgroup_customvar(customvar_id, hostgroup_id);
+
+COMMENT ON COLUMN hostgroup_customvar.id IS 'sha1(environment.id + hostgroup_id + customvar_id)';
+COMMENT ON COLUMN hostgroup_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN hostgroup_customvar.hostgroup_id IS 'hostgroup.id';
+COMMENT ON COLUMN hostgroup_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE host_state (
+ id bytea20 NOT NULL,
+ host_id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ state_type state_type NOT NULL DEFAULT 'hard',
+ soft_state tinyuint NOT NULL,
+ hard_state tinyuint NOT NULL,
+ previous_soft_state tinyuint NOT NULL,
+ previous_hard_state tinyuint NOT NULL,
+ check_attempt tinyuint NOT NULL,
+ severity smalluint NOT NULL,
+
+ output text DEFAULT NULL,
+ long_output text DEFAULT NULL,
+ performance_data text DEFAULT NULL,
+ normalized_performance_data text DEFAULT NULL,
+
+ check_commandline text DEFAULT NULL,
+
+ is_problem boolenum NOT NULL DEFAULT 'n',
+ is_handled boolenum NOT NULL DEFAULT 'n',
+ is_reachable boolenum NOT NULL DEFAULT 'n',
+ is_flapping boolenum NOT NULL DEFAULT 'n',
+ is_overdue boolenum NOT NULL DEFAULT 'n',
+
+ is_acknowledged acked NOT NULL DEFAULT 'n',
+ acknowledgement_comment_id bytea20 DEFAULT NULL,
+ last_comment_id bytea20 DEFAULT NULL,
+
+ in_downtime boolenum NOT NULL DEFAULT 'n',
+
+ execution_time uint DEFAULT NULL,
+ latency uint DEFAULT NULL,
+ check_timeout uint DEFAULT NULL,
+ check_source text DEFAULT NULL,
+ scheduling_source text DEFAULT NULL,
+
+ last_update biguint DEFAULT NULL,
+ last_state_change biguint NOT NULL,
+ next_check biguint NOT NULL,
+ next_update biguint NOT NULL,
+
+ CONSTRAINT pk_host_state PRIMARY KEY (id)
+);
+
+ALTER TABLE host_state ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE host_state ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE host_state ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE host_state ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE host_state ALTER COLUMN acknowledgement_comment_id SET STORAGE PLAIN;
+ALTER TABLE host_state ALTER COLUMN last_comment_id SET STORAGE PLAIN;
+
+CREATE UNIQUE INDEX idx_host_state_host_id ON host_state(host_id);
+CREATE INDEX idx_host_state_is_problem ON host_state(is_problem, severity);
+CREATE INDEX idx_host_state_severity ON host_state(severity);
+CREATE INDEX idx_host_state_soft_state ON host_state(soft_state, last_state_change);
+CREATE INDEX idx_host_state_last_state_change ON host_state(last_state_change);
+
+COMMENT ON COLUMN host_state.id IS 'host.id';
+COMMENT ON COLUMN host_state.host_id IS 'host.id';
+COMMENT ON COLUMN host_state.environment_id IS 'environment.id';
+COMMENT ON COLUMN host_state.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN host_state.acknowledgement_comment_id IS 'comment.id';
+COMMENT ON COLUMN host_state.last_comment_id IS 'comment.id';
+
+COMMENT ON INDEX idx_host_state_is_problem IS 'Host list filtered by is_problem ordered by severity';
+COMMENT ON INDEX idx_host_state_severity IS 'Host list filtered/ordered by severity';
+COMMENT ON INDEX idx_host_state_soft_state IS 'Host list filtered/ordered by soft_state; recently recovered filter';
+COMMENT ON INDEX idx_host_state_last_state_change IS 'Host list filtered/ordered by last_state_change';
+
+CREATE TABLE service (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+ host_id bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ display_name citext NOT NULL,
+
+ checkcommand_name citext NOT NULL,
+ checkcommand_id bytea20 NOT NULL,
+
+ max_check_attempts uint NOT NULL,
+
+ check_timeperiod_name citext NOT NULL,
+ check_timeperiod_id bytea20 DEFAULT NULL,
+
+ check_timeout uint DEFAULT NULL,
+ check_interval uint NOT NULL,
+ check_retry_interval uint NOT NULL,
+
+ active_checks_enabled boolenum NOT NULL DEFAULT 'n',
+ passive_checks_enabled boolenum NOT NULL DEFAULT 'n',
+ event_handler_enabled boolenum NOT NULL DEFAULT 'n',
+ notifications_enabled boolenum NOT NULL DEFAULT 'n',
+
+ flapping_enabled boolenum NOT NULL DEFAULT 'n',
+ flapping_threshold_low float NOT NULL,
+ flapping_threshold_high float NOT NULL,
+
+ perfdata_enabled boolenum NOT NULL DEFAULT 'n',
+
+ eventcommand_name citext NOT NULL,
+ eventcommand_id bytea20 DEFAULT NULL,
+
+ is_volatile boolenum NOT NULL DEFAULT 'n',
+
+ action_url_id bytea20 DEFAULT NULL,
+ notes_url_id bytea20 DEFAULT NULL,
+ notes text NOT NULL,
+ icon_image_id bytea20 DEFAULT NULL,
+ icon_image_alt varchar(32) NOT NULL,
+
+ zone_name citext NOT NULL,
+ zone_id bytea20 DEFAULT NULL,
+
+ command_endpoint_name citext NOT NULL,
+ command_endpoint_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_service PRIMARY KEY (id)
+);
+
+ALTER TABLE service ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN checkcommand_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN check_timeperiod_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN eventcommand_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN action_url_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN notes_url_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN icon_image_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN zone_id SET STORAGE PLAIN;
+ALTER TABLE service ALTER COLUMN command_endpoint_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_service_display_name ON service(display_name);
+CREATE INDEX idx_service_host_id ON service(host_id, display_name);
+CREATE INDEX idx_service_name_ci ON service(name_ci);
+CREATE INDEX idx_service_name ON service(name);
+
+COMMENT ON COLUMN service.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN service.environment_id IS 'environment.id';
+COMMENT ON COLUMN service.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN service.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN service.host_id IS 'sha1(host.id)';
+COMMENT ON COLUMN service.checkcommand_name IS 'checkcommand.name';
+COMMENT ON COLUMN service.checkcommand_id IS 'checkcommand.id';
+COMMENT ON COLUMN service.check_timeperiod_name IS 'timeperiod.name';
+COMMENT ON COLUMN service.check_timeperiod_id IS 'timeperiod.id';
+COMMENT ON COLUMN service.eventcommand_name IS 'eventcommand.name';
+COMMENT ON COLUMN service.eventcommand_id IS 'eventcommand.id';
+COMMENT ON COLUMN service.action_url_id IS 'action_url.id';
+COMMENT ON COLUMN service.notes_url_id IS 'notes_url.id';
+COMMENT ON COLUMN service.icon_image_id IS 'icon_image.id';
+COMMENT ON COLUMN service.zone_name IS 'zone.name';
+COMMENT ON COLUMN service.zone_id IS 'zone.id';
+COMMENT ON COLUMN service.command_endpoint_name IS 'endpoint.name';
+COMMENT ON COLUMN service.command_endpoint_id IS 'endpoint.id';
+
+COMMENT ON INDEX idx_service_display_name IS 'Service list filtered/ordered by display_name';
+COMMENT ON INDEX idx_service_host_id IS 'Service list filtered by host and ordered by display_name';
+COMMENT ON INDEX idx_service_name_ci IS 'Service list filtered using quick search';
+COMMENT ON INDEX idx_service_name IS 'Service list filtered/ordered by name; Service detail filter';
+
+CREATE TABLE servicegroup (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ display_name citext NOT NULL,
+
+ zone_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_servicegroup PRIMARY KEY (id)
+);
+
+ALTER TABLE servicegroup ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE servicegroup ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE servicegroup ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE servicegroup ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE servicegroup ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN servicegroup.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN servicegroup.environment_id IS 'environment.id';
+COMMENT ON COLUMN servicegroup.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN servicegroup.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN servicegroup.zone_id IS 'zone.id';
+
+CREATE INDEX idx_servicegroup_name ON servicegroup(name);
+COMMENT ON INDEX idx_servicegroup_name IS 'Host/service/service group list filtered by service group name';
+
+CREATE TABLE servicegroup_member (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ service_id bytea20 NOT NULL,
+ servicegroup_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_servicegroup_member PRIMARY KEY (id)
+);
+
+ALTER TABLE servicegroup_member ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE servicegroup_member ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE servicegroup_member ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE servicegroup_member ALTER COLUMN servicegroup_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_servicegroup_member_service_id ON servicegroup_member(service_id, servicegroup_id);
+CREATE INDEX idx_servicegroup_member_servicegroup_id ON servicegroup_member(servicegroup_id, service_id);
+
+COMMENT ON COLUMN servicegroup_member.id IS 'sha1(environment.id + servicegroup_id + service_id)';
+COMMENT ON COLUMN servicegroup_member.environment_id IS 'environment.id';
+COMMENT ON COLUMN servicegroup_member.service_id IS 'service.id';
+COMMENT ON COLUMN servicegroup_member.servicegroup_id IS 'servicegroup.id';
+
+CREATE TABLE service_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ service_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_service_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE service_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE service_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE service_customvar ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE service_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_service_customvar_service_id ON service_customvar(service_id, customvar_id);
+CREATE INDEX idx_service_customvar_customvar_id ON service_customvar(customvar_id, service_id);
+
+COMMENT ON COLUMN service_customvar.id IS 'sha1(environment.id + service_id + customvar_id)';
+COMMENT ON COLUMN service_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN service_customvar.service_id IS 'service.id';
+COMMENT ON COLUMN service_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE servicegroup_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ servicegroup_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_servicegroup_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE servicegroup_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE servicegroup_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE servicegroup_customvar ALTER COLUMN servicegroup_id SET STORAGE PLAIN;
+ALTER TABLE servicegroup_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_servicegroup_customvar_servicegroup_id ON servicegroup_customvar(servicegroup_id, customvar_id);
+CREATE INDEX idx_servicegroup_customvar_customvar_id ON servicegroup_customvar(customvar_id, servicegroup_id);
+
+COMMENT ON COLUMN servicegroup_customvar.id IS 'sha1(environment.id + servicegroup_id + customvar_id)';
+COMMENT ON COLUMN servicegroup_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN servicegroup_customvar.servicegroup_id IS 'servicegroup.id';
+COMMENT ON COLUMN servicegroup_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE service_state (
+ id bytea20 NOT NULL,
+ host_id bytea20 NOT NULL,
+ service_id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ state_type state_type NOT NULL DEFAULT 'hard',
+ soft_state tinyuint NOT NULL,
+ hard_state tinyuint NOT NULL,
+ previous_soft_state tinyuint NOT NULL,
+ previous_hard_state tinyuint NOT NULL,
+ check_attempt tinyuint NOT NULL,
+ severity smalluint NOT NULL,
+
+ output text DEFAULT NULL,
+ long_output text DEFAULT NULL,
+ performance_data text DEFAULT NULL,
+ normalized_performance_data text DEFAULT NULL,
+
+ check_commandline text DEFAULT NULL,
+
+ is_problem boolenum NOT NULL DEFAULT 'n',
+ is_handled boolenum NOT NULL DEFAULT 'n',
+ is_reachable boolenum NOT NULL DEFAULT 'n',
+ is_flapping boolenum NOT NULL DEFAULT 'n',
+ is_overdue boolenum NOT NULL DEFAULT 'n',
+
+ is_acknowledged acked NOT NULL DEFAULT 'n',
+ acknowledgement_comment_id bytea20 DEFAULT NULL,
+ last_comment_id bytea20 DEFAULT NULL,
+
+ in_downtime boolenum NOT NULL DEFAULT 'n',
+
+ execution_time uint DEFAULT NULL,
+ latency uint DEFAULT NULL,
+ check_timeout uint DEFAULT NULL,
+ check_source text DEFAULT NULL,
+ scheduling_source text DEFAULT NULL,
+
+ last_update biguint DEFAULT NULL,
+ last_state_change biguint NOT NULL,
+ next_check biguint NOT NULL,
+ next_update biguint NOT NULL,
+
+ CONSTRAINT pk_service_state PRIMARY KEY (id)
+);
+
+ALTER TABLE service_state ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE service_state ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE service_state ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE service_state ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE service_state ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE service_state ALTER COLUMN acknowledgement_comment_id SET STORAGE PLAIN;
+ALTER TABLE service_state ALTER COLUMN last_comment_id SET STORAGE PLAIN;
+
+CREATE UNIQUE INDEX idx_service_state_service_id ON service_state(service_id);
+CREATE INDEX idx_service_state_is_problem ON service_state(is_problem, severity);
+CREATE INDEX idx_service_state_severity ON service_state(severity);
+CREATE INDEX idx_service_state_soft_state ON service_state(soft_state, last_state_change);
+CREATE INDEX idx_service_state_last_state_change ON service_state(last_state_change);
+
+COMMENT ON COLUMN service_state.id IS 'service.id';
+COMMENT ON COLUMN service_state.host_id IS 'host.id';
+COMMENT ON COLUMN service_state.service_id IS 'service.id';
+COMMENT ON COLUMN service_state.environment_id IS 'environment.id';
+COMMENT ON COLUMN service_state.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN service_state.acknowledgement_comment_id IS 'comment.id';
+COMMENT ON COLUMN service_state.last_comment_id IS 'comment.id';
+
+COMMENT ON INDEX idx_service_state_is_problem IS 'Service list filtered by is_problem ordered by severity';
+COMMENT ON INDEX idx_service_state_severity IS 'Service list filtered/ordered by severity';
+COMMENT ON INDEX idx_service_state_soft_state IS 'Service list filtered/ordered by soft_state; recently recovered filter';
+COMMENT ON INDEX idx_service_state_last_state_change IS 'Service list filtered/ordered by last_state_change';
+
+CREATE TABLE endpoint (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+
+ zone_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_endpoint PRIMARY KEY (id)
+);
+
+ALTER TABLE endpoint ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE endpoint ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE endpoint ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE endpoint ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE endpoint ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN endpoint.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN endpoint.environment_id IS 'environment.id';
+COMMENT ON COLUMN endpoint.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN endpoint.zone_id IS 'zone.id';
+
+CREATE TABLE environment (
+ id bytea20 NOT NULL,
+ name varchar(255) NOT NULL,
+
+ CONSTRAINT pk_environment PRIMARY KEY (id)
+);
+
+ALTER TABLE environment ALTER COLUMN id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN environment.id IS 'sha1(Icinga CA public key)';
+
+CREATE TABLE icingadb_instance (
+ id bytea16 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ heartbeat biguint NOT NULL,
+ responsible boolenum NOT NULL DEFAULT 'n',
+
+ icinga2_version varchar(255) NOT NULL,
+ icinga2_start_time biguint NOT NULL,
+ icinga2_notifications_enabled boolenum NOT NULL DEFAULT 'n',
+ icinga2_active_service_checks_enabled boolenum NOT NULL DEFAULT 'n',
+ icinga2_active_host_checks_enabled boolenum NOT NULL DEFAULT 'n',
+ icinga2_event_handlers_enabled boolenum NOT NULL DEFAULT 'n',
+ icinga2_flap_detection_enabled boolenum NOT NULL DEFAULT 'n',
+ icinga2_performance_data_enabled boolenum NOT NULL DEFAULT 'n',
+
+ CONSTRAINT pk_icingadb_instance PRIMARY KEY (id)
+);
+
+ALTER TABLE icingadb_instance ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE icingadb_instance ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE icingadb_instance ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN icingadb_instance.id IS 'UUIDv4';
+COMMENT ON COLUMN icingadb_instance.environment_id IS 'environment.id';
+COMMENT ON COLUMN icingadb_instance.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN icingadb_instance.heartbeat IS '*nix timestamp';
+
+CREATE TABLE checkcommand (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ zone_id bytea20 DEFAULT NULL,
+
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ command text NOT NULL,
+ timeout uint NOT NULL,
+
+ CONSTRAINT pk_checkcommand PRIMARY KEY (id)
+);
+
+ALTER TABLE checkcommand ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE checkcommand ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE checkcommand ALTER COLUMN zone_id SET STORAGE PLAIN;
+ALTER TABLE checkcommand ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE checkcommand ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN checkcommand.id IS 'sha1(environment.id + type + name)';
+COMMENT ON COLUMN checkcommand.environment_id IS 'env.id';
+COMMENT ON COLUMN checkcommand.zone_id IS 'zone.id';
+COMMENT ON COLUMN checkcommand.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN checkcommand.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE checkcommand_argument (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ checkcommand_id bytea20 NOT NULL,
+ argument_key varchar(64) NOT NULL,
+
+ properties_checksum bytea20 NOT NULL,
+
+ argument_value text DEFAULT NULL,
+ argument_order smallint DEFAULT NULL,
+ description text DEFAULT NULL,
+ argument_key_override citext DEFAULT NULL,
+ repeat_key boolenum NOT NULL DEFAULT 'n',
+ required boolenum NOT NULL DEFAULT 'n',
+ set_if varchar(255) DEFAULT NULL,
+ separator varchar(255) DEFAULT NULL,
+ skip_key boolenum NOT NULL DEFAULT 'n',
+
+ CONSTRAINT pk_checkcommand_argument PRIMARY KEY (id)
+);
+
+ALTER TABLE checkcommand_argument ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_argument ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_argument ALTER COLUMN checkcommand_id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_argument ALTER COLUMN argument_key SET STORAGE PLAIN;
+ALTER TABLE checkcommand_argument ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN checkcommand_argument.id IS 'sha1(environment.id + checkcommand_id + argument_key)';
+COMMENT ON COLUMN checkcommand_argument.environment_id IS 'env.id';
+COMMENT ON COLUMN checkcommand_argument.checkcommand_id IS 'checkcommand.id';
+COMMENT ON COLUMN checkcommand_argument.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE checkcommand_envvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ checkcommand_id bytea20 NOT NULL,
+ envvar_key varchar(64) NOT NULL,
+
+ properties_checksum bytea20 NOT NULL,
+
+ envvar_value text NOT NULL,
+
+ CONSTRAINT pk_checkcommand_envvar PRIMARY KEY (id)
+);
+
+ALTER TABLE checkcommand_envvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_envvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_envvar ALTER COLUMN checkcommand_id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_envvar ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN checkcommand_envvar.id IS 'sha1(environment.id + checkcommand_id + envvar_key)';
+COMMENT ON COLUMN checkcommand_envvar.environment_id IS 'env.id';
+COMMENT ON COLUMN checkcommand_envvar.checkcommand_id IS 'checkcommand.id';
+COMMENT ON COLUMN checkcommand_envvar.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE checkcommand_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+
+ checkcommand_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_checkcommand_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE checkcommand_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_customvar ALTER COLUMN checkcommand_id SET STORAGE PLAIN;
+ALTER TABLE checkcommand_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_checkcommand_customvar_checkcommand_id ON checkcommand_customvar(checkcommand_id, customvar_id);
+CREATE INDEX idx_checkcommand_customvar_customvar_id ON checkcommand_customvar(customvar_id, checkcommand_id);
+
+COMMENT ON COLUMN checkcommand_customvar.id IS 'sha1(environment.id + checkcommand_id + customvar_id)';
+COMMENT ON COLUMN checkcommand_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN checkcommand_customvar.checkcommand_id IS 'checkcommand.id';
+COMMENT ON COLUMN checkcommand_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE eventcommand (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ zone_id bytea20 DEFAULT NULL,
+
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ command text NOT NULL,
+ timeout smalluint NOT NULL,
+
+ CONSTRAINT pk_eventcommand PRIMARY KEY (id)
+);
+
+ALTER TABLE eventcommand ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE eventcommand ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE eventcommand ALTER COLUMN zone_id SET STORAGE PLAIN;
+ALTER TABLE eventcommand ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE eventcommand ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN eventcommand.id IS 'sha1(environment.id + type + name)';
+COMMENT ON COLUMN eventcommand.environment_id IS 'env.id';
+COMMENT ON COLUMN eventcommand.zone_id IS 'zone.id';
+COMMENT ON COLUMN eventcommand.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN eventcommand.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE eventcommand_argument (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ eventcommand_id bytea20 NOT NULL,
+ argument_key varchar(64) NOT NULL,
+
+ properties_checksum bytea20 NOT NULL,
+
+ argument_value text DEFAULT NULL,
+ argument_order smallint DEFAULT NULL,
+ description text DEFAULT NULL,
+ argument_key_override citext DEFAULT NULL,
+ repeat_key boolenum NOT NULL DEFAULT 'n',
+ required boolenum NOT NULL DEFAULT 'n',
+ set_if varchar(255) DEFAULT NULL,
+ separator varchar(255) DEFAULT NULL,
+ skip_key boolenum NOT NULL DEFAULT 'n',
+
+ CONSTRAINT pk_eventcommand_argument PRIMARY KEY (id)
+);
+
+ALTER TABLE eventcommand_argument ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_argument ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_argument ALTER COLUMN eventcommand_id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_argument ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN eventcommand_argument.id IS 'sha1(environment.id + eventcommand_id + argument_key)';
+COMMENT ON COLUMN eventcommand_argument.environment_id IS 'env.id';
+COMMENT ON COLUMN eventcommand_argument.eventcommand_id IS 'eventcommand.id';
+COMMENT ON COLUMN eventcommand_argument.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE eventcommand_envvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ eventcommand_id bytea20 NOT NULL,
+ envvar_key varchar(64) NOT NULL,
+
+ properties_checksum bytea20 NOT NULL,
+
+ envvar_value text NOT NULL,
+
+ CONSTRAINT pk_eventcommand_envvar PRIMARY KEY (id)
+);
+
+ALTER TABLE eventcommand_envvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_envvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_envvar ALTER COLUMN eventcommand_id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_envvar ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN eventcommand_envvar.id IS 'sha1(environment.id + eventcommand_id + envvar_key)';
+COMMENT ON COLUMN eventcommand_envvar.environment_id IS 'env.id';
+COMMENT ON COLUMN eventcommand_envvar.eventcommand_id IS 'eventcommand.id';
+COMMENT ON COLUMN eventcommand_envvar.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE eventcommand_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ eventcommand_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_eventcommand_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE eventcommand_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_customvar ALTER COLUMN eventcommand_id SET STORAGE PLAIN;
+ALTER TABLE eventcommand_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_eventcommand_customvar_eventcommand_id ON eventcommand_customvar(eventcommand_id, customvar_id);
+CREATE INDEX idx_eventcommand_customvar_customvar_id ON eventcommand_customvar(customvar_id, eventcommand_id);
+
+COMMENT ON COLUMN eventcommand_customvar.id IS 'sha1(environment.id + eventcommand_id + customvar_id)';
+COMMENT ON COLUMN eventcommand_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN eventcommand_customvar.eventcommand_id IS 'eventcommand.id';
+COMMENT ON COLUMN eventcommand_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE notificationcommand (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ zone_id bytea20 DEFAULT NULL,
+
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ command text NOT NULL,
+ timeout smalluint NOT NULL,
+
+ CONSTRAINT pk_notificationcommand PRIMARY KEY (id)
+);
+
+ALTER TABLE notificationcommand ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand ALTER COLUMN zone_id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE notificationcommand ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN notificationcommand.id IS 'sha1(environment.id + type + name)';
+COMMENT ON COLUMN notificationcommand.environment_id IS 'env.id';
+COMMENT ON COLUMN notificationcommand.zone_id IS 'zone.id';
+COMMENT ON COLUMN notificationcommand.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN notificationcommand.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE notificationcommand_argument (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notificationcommand_id bytea20 NOT NULL,
+ argument_key varchar(64) NOT NULL,
+
+ properties_checksum bytea20 NOT NULL,
+
+ argument_value text DEFAULT NULL,
+ argument_order smallint DEFAULT NULL,
+ description text DEFAULT NULL,
+ argument_key_override citext DEFAULT NULL,
+ repeat_key boolenum NOT NULL DEFAULT 'n',
+ required boolenum NOT NULL DEFAULT 'n',
+ set_if varchar(255) DEFAULT NULL,
+ separator varchar(255) DEFAULT NULL,
+ skip_key boolenum NOT NULL DEFAULT 'n',
+
+ CONSTRAINT pk_notificationcommand_argument PRIMARY KEY (id)
+);
+
+ALTER TABLE notificationcommand_argument ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_argument ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_argument ALTER COLUMN notificationcommand_id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_argument ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN notificationcommand_argument.id IS 'sha1(environment.id + notificationcommand_id + argument_key)';
+COMMENT ON COLUMN notificationcommand_argument.environment_id IS 'env.id';
+COMMENT ON COLUMN notificationcommand_argument.notificationcommand_id IS 'notificationcommand.id';
+COMMENT ON COLUMN notificationcommand_argument.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE notificationcommand_envvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notificationcommand_id bytea20 NOT NULL,
+ envvar_key varchar(64) NOT NULL,
+
+ properties_checksum bytea20 NOT NULL,
+
+ envvar_value text NOT NULL,
+
+ CONSTRAINT pk_notificationcommand_envvar PRIMARY KEY (id)
+);
+
+ALTER TABLE notificationcommand_envvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_envvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_envvar ALTER COLUMN notificationcommand_id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_envvar ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN notificationcommand_envvar.id IS 'sha1(environment.id + notificationcommand_id + envvar_key)';
+COMMENT ON COLUMN notificationcommand_envvar.environment_id IS 'env.id';
+COMMENT ON COLUMN notificationcommand_envvar.notificationcommand_id IS 'notificationcommand.id';
+COMMENT ON COLUMN notificationcommand_envvar.properties_checksum IS 'sha1(all properties)';
+
+CREATE TABLE notificationcommand_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notificationcommand_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_notificationcommand_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE notificationcommand_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_customvar ALTER COLUMN notificationcommand_id SET STORAGE PLAIN;
+ALTER TABLE notificationcommand_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_notificationcommand_customvar_notificationcommand_id ON notificationcommand_customvar(notificationcommand_id, customvar_id);
+CREATE INDEX idx_notificationcommand_customvar_customvar_id ON notificationcommand_customvar(customvar_id, notificationcommand_id);
+
+COMMENT ON COLUMN notificationcommand_customvar.id IS 'sha1(environment.id + notificationcommand_id + customvar_id)';
+COMMENT ON COLUMN notificationcommand_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN notificationcommand_customvar.notificationcommand_id IS 'notificationcommand.id';
+COMMENT ON COLUMN notificationcommand_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE comment (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+ name varchar(548) NOT NULL,
+
+ author citext NOT NULL,
+ text text NOT NULL,
+ entry_type comment_type NOT NULL DEFAULT 'comment',
+ entry_time biguint NOT NULL,
+ is_persistent boolenum NOT NULL DEFAULT 'n',
+ is_sticky boolenum NOT NULL DEFAULT 'n',
+ expire_time biguint DEFAULT NULL,
+
+ zone_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_comment PRIMARY KEY (id)
+);
+
+ALTER TABLE comment ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE comment ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE comment ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE comment ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE comment ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE comment ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE comment ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_comment_name ON comment(name);
+CREATE INDEX idx_comment_entry_time ON comment(entry_time);
+CREATE INDEX idx_comment_author ON comment(author);
+CREATE INDEX idx_comment_expire_time ON comment(expire_time);
+
+COMMENT ON COLUMN comment.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN comment.environment_id IS 'environment.id';
+COMMENT ON COLUMN comment.host_id IS 'host.id';
+COMMENT ON COLUMN comment.service_id IS 'service.id';
+COMMENT ON COLUMN comment.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN comment.name IS '255+1+255+1+36, i.e. "host.name!service.name!UUID"';
+COMMENT ON COLUMN comment.zone_id IS 'zone.id';
+
+COMMENT ON INDEX idx_comment_name IS 'Comment detail filter';
+COMMENT ON INDEX idx_comment_entry_time IS 'Comment list fileted/ordered by entry_time';
+COMMENT ON INDEX idx_comment_author IS 'Comment list filtered/ordered by author';
+COMMENT ON INDEX idx_comment_expire_time IS 'Comment list filtered/ordered by expire_time';
+
+CREATE TABLE downtime (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+
+ triggered_by_id bytea20 DEFAULT NULL,
+ parent_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+ name varchar(548) NOT NULL,
+
+ author citext NOT NULL,
+ comment text NOT NULL,
+ entry_time biguint NOT NULL,
+ scheduled_start_time biguint NOT NULL,
+ scheduled_end_time biguint NOT NULL,
+ scheduled_duration biguint NOT NULL,
+ is_flexible boolenum NOT NULL DEFAULT 'n',
+ flexible_duration biguint NOT NULL,
+
+ is_in_effect boolenum NOT NULL DEFAULT 'n',
+ start_time biguint DEFAULT NULL,
+ end_time biguint DEFAULT NULL,
+ duration biguint NOT NULL,
+ scheduled_by varchar(767) DEFAULT NULL,
+
+ zone_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_downtime PRIMARY KEY (id)
+);
+
+ALTER TABLE downtime ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE downtime ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE downtime ALTER COLUMN triggered_by_id SET STORAGE PLAIN;
+ALTER TABLE downtime ALTER COLUMN parent_id SET STORAGE PLAIN;
+ALTER TABLE downtime ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE downtime ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE downtime ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE downtime ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE downtime ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_downtime_is_in_effect ON downtime(is_in_effect, start_time);
+CREATE INDEX idx_downtime_name ON downtime(name);
+CREATE INDEX idx_downtime_entry_time ON downtime(entry_time);
+CREATE INDEX idx_downtime_start_time ON downtime(start_time);
+CREATE INDEX idx_downtime_end_time ON downtime(end_time);
+CREATE INDEX idx_downtime_scheduled_start_time ON downtime(scheduled_start_time);
+CREATE INDEX idx_downtime_scheduled_end_time ON downtime(scheduled_end_time);
+CREATE INDEX idx_downtime_author ON downtime(author);
+CREATE INDEX idx_downtime_duration ON downtime(duration);
+
+COMMENT ON COLUMN downtime.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN downtime.environment_id IS 'environment.id';
+COMMENT ON COLUMN downtime.triggered_by_id IS '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.';
+COMMENT ON COLUMN downtime.parent_id IS 'For service downtimes, the ID of the host downtime that created this downtime by using the "all_services" flag of the schedule-downtime API.';
+COMMENT ON COLUMN downtime.host_id IS 'host.id';
+COMMENT ON COLUMN downtime.service_id IS 'service.id';
+COMMENT ON COLUMN downtime.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN downtime.name IS '255+1+255+1+36, i.e. "host.name!service.name!UUID"';
+COMMENT ON COLUMN downtime.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN downtime.start_time IS 'Time when the host went into a problem state during the downtimes timeframe';
+COMMENT ON COLUMN downtime.end_time IS 'Problem state assumed: scheduled_end_time if fixed, start_time + flexible_duration otherwise';
+COMMENT ON COLUMN downtime.duration IS 'Duration of the downtime: When the downtime is flexible, this is the same as flexible_duration otherwise scheduled_duration';
+COMMENT ON COLUMN downtime.scheduled_by IS 'Name of the ScheduledDowntime which created this Downtime. 255+1+255+1+255, i.e. "host.name!service.name!scheduled-downtime-name"';
+COMMENT ON COLUMN downtime.zone_id IS 'zone.id';
+
+COMMENT ON INDEX idx_downtime_is_in_effect IS 'Downtime list filtered/ordered by severity';
+COMMENT ON INDEX idx_downtime_name IS 'Downtime detail filter';
+COMMENT ON INDEX idx_downtime_entry_time IS 'Downtime list filtered/ordered by entry_time';
+COMMENT ON INDEX idx_downtime_start_time IS 'Downtime list filtered/ordered by start_time';
+COMMENT ON INDEX idx_downtime_end_time IS 'Downtime list filtered/ordered by end_time';
+COMMENT ON INDEX idx_downtime_scheduled_start_time IS 'Downtime list filtered/ordered by scheduled_start_time';
+COMMENT ON INDEX idx_downtime_scheduled_end_time IS 'Downtime list filtered/ordered by scheduled_end_time';
+COMMENT ON INDEX idx_downtime_author IS 'Downtime list filtered/ordered by author';
+COMMENT ON INDEX idx_downtime_duration IS 'Downtime list filtered/ordered by duration';
+
+CREATE TABLE notification (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+ notificationcommand_id bytea20 NOT NULL,
+
+ times_begin uint DEFAULT NULL,
+ times_end uint DEFAULT NULL,
+ notification_interval uint NOT NULL,
+ timeperiod_id bytea20 DEFAULT NULL,
+
+ states tinyuint NOT NULL,
+ types smalluint NOT NULL,
+
+ zone_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_notification PRIMARY KEY (id)
+);
+
+ALTER TABLE notification ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notification ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notification ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE notification ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE notification ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE notification ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE notification ALTER COLUMN notificationcommand_id SET STORAGE PLAIN;
+ALTER TABLE notification ALTER COLUMN timeperiod_id SET STORAGE PLAIN;
+ALTER TABLE notification ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_notification_host_id ON notification(host_id);
+CREATE INDEX idx_notification_service_id ON notification(service_id);
+
+COMMENT ON COLUMN notification.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN notification.environment_id IS 'environment.id';
+COMMENT ON COLUMN notification.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN notification.host_id IS 'host.id';
+COMMENT ON COLUMN notification.service_id IS 'service.id';
+COMMENT ON COLUMN notification.notificationcommand_id IS 'command.id';
+COMMENT ON COLUMN notification.timeperiod_id IS 'timeperiod.id';
+COMMENT ON COLUMN notification.zone_id IS 'zone.id';
+
+CREATE TABLE notification_user (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notification_id bytea20 NOT NULL,
+ user_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_notification_user PRIMARY KEY (id)
+);
+
+ALTER TABLE notification_user ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notification_user ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notification_user ALTER COLUMN notification_id SET STORAGE PLAIN;
+ALTER TABLE notification_user ALTER COLUMN user_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_notification_user_user_id ON notification_user(user_id, notification_id);
+CREATE INDEX idx_notification_user_notification_id ON notification_user(notification_id, user_id);
+
+COMMENT ON COLUMN notification_user.id IS 'sha1(environment.id + notification_id + user_id)';
+COMMENT ON COLUMN notification_user.environment_id IS 'environment.id';
+COMMENT ON COLUMN notification_user.notification_id IS 'notification.id';
+COMMENT ON COLUMN notification_user.user_id IS 'user.id';
+
+CREATE TABLE notification_usergroup (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notification_id bytea20 NOT NULL,
+ usergroup_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_notification_usergroup PRIMARY KEY (id)
+);
+
+ALTER TABLE notification_usergroup ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notification_usergroup ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notification_usergroup ALTER COLUMN notification_id SET STORAGE PLAIN;
+ALTER TABLE notification_usergroup ALTER COLUMN usergroup_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_notification_usergroup_usergroup_id ON notification_usergroup(usergroup_id, notification_id);
+CREATE INDEX idx_notification_usergroup_notification_id ON notification_usergroup(notification_id, usergroup_id);
+
+COMMENT ON COLUMN notification_usergroup.id IS 'sha1(environment.id + notification_id + usergroup_id)';
+COMMENT ON COLUMN notification_usergroup.environment_id IS 'environment.id';
+COMMENT ON COLUMN notification_usergroup.notification_id IS 'notification.id';
+COMMENT ON COLUMN notification_usergroup.usergroup_id IS 'usergroup.id';
+
+CREATE TABLE notification_recipient (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notification_id bytea20 NOT NULL,
+ user_id bytea20 NULL,
+ usergroup_id bytea20 NULL,
+
+ CONSTRAINT pk_notification_recipient PRIMARY KEY (id)
+);
+
+ALTER TABLE notification_recipient ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notification_recipient ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notification_recipient ALTER COLUMN notification_id SET STORAGE PLAIN;
+ALTER TABLE notification_recipient ALTER COLUMN user_id SET STORAGE PLAIN;
+ALTER TABLE notification_recipient ALTER COLUMN usergroup_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_notification_recipient_user_id ON notification_recipient(user_id, notification_id);
+CREATE INDEX idx_notification_recipient_notification_id_user ON notification_recipient(notification_id, user_id);
+CREATE INDEX idx_notification_recipient_usergroup_id ON notification_recipient(usergroup_id, notification_id);
+CREATE INDEX idx_notification_recipient_notification_id_usergroup ON notification_recipient(notification_id, usergroup_id);
+
+COMMENT ON COLUMN notification_recipient.id IS 'sha1(environment.id + notification_id + (user_id | usergroup_id))';
+COMMENT ON COLUMN notification_recipient.environment_id IS 'environment.id';
+COMMENT ON COLUMN notification_recipient.notification_id IS 'notification.id';
+COMMENT ON COLUMN notification_recipient.user_id IS 'user.id';
+COMMENT ON COLUMN notification_recipient.usergroup_id IS 'usergroup.id';
+
+CREATE TABLE notification_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notification_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_notification_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE notification_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notification_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notification_customvar ALTER COLUMN notification_id SET STORAGE PLAIN;
+ALTER TABLE notification_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_notification_customvar_notification_id ON notification_customvar(notification_id, customvar_id);
+CREATE INDEX idx_notification_customvar_customvar_id ON notification_customvar(customvar_id, notification_id);
+
+COMMENT ON COLUMN notification_customvar.id IS 'sha1(environment.id + notification_id + customvar_id)';
+COMMENT ON COLUMN notification_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN notification_customvar.notification_id IS 'notification.id';
+COMMENT ON COLUMN notification_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE icon_image (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ icon_image citext NOT NULL,
+
+ CONSTRAINT pk_icon_image PRIMARY KEY (id)
+);
+
+ALTER TABLE icon_image ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE icon_image ALTER COLUMN environment_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_icon_image ON icon_image(icon_image);
+
+COMMENT ON COLUMN icon_image.id IS 'sha1(environment.id + icon_image)';
+COMMENT ON COLUMN icon_image.environment_id IS 'environment.id';
+
+CREATE TABLE action_url (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ action_url citext NOT NULL,
+
+ CONSTRAINT pk_action_url PRIMARY KEY (id)
+);
+
+ALTER TABLE action_url ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE action_url ALTER COLUMN environment_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_action_url ON action_url(action_url);
+
+COMMENT ON COLUMN action_url.id IS 'sha1(environment.id + action_url)';
+COMMENT ON COLUMN action_url.environment_id IS 'environment.id';
+
+CREATE TABLE notes_url (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notes_url citext NOT NULL,
+
+ CONSTRAINT pk_notes_url PRIMARY KEY (id)
+);
+
+ALTER TABLE notes_url ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notes_url ALTER COLUMN environment_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_notes_url ON notes_url(notes_url);
+
+COMMENT ON COLUMN notes_url.id IS 'sha1(environment.id + notes_url)';
+COMMENT ON COLUMN notes_url.environment_id IS 'environment.id';
+
+CREATE TABLE timeperiod (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ display_name citext NOT NULL,
+ prefer_includes boolenum NOT NULL DEFAULT 'n',
+
+ zone_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_timeperiod PRIMARY KEY (id)
+);
+
+ALTER TABLE timeperiod ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE timeperiod ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE timeperiod ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE timeperiod ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE timeperiod ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN timeperiod.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN timeperiod.environment_id IS 'env.id';
+COMMENT ON COLUMN timeperiod.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN timeperiod.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN timeperiod.zone_id IS 'zone.id';
+
+CREATE TABLE timeperiod_range (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ timeperiod_id bytea20 NOT NULL,
+ range_key citext NOT NULL,
+
+ range_value varchar(255) NOT NULL,
+
+ CONSTRAINT pk_timeperiod_range PRIMARY KEY (id)
+);
+
+ALTER TABLE timeperiod_range ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_range ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_range ALTER COLUMN timeperiod_id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN timeperiod_range.id IS 'sha1(environment.id + range_id + timeperiod_id)';
+COMMENT ON COLUMN timeperiod_range.environment_id IS 'env.id';
+COMMENT ON COLUMN timeperiod_range.timeperiod_id IS 'timeperiod.id';
+
+CREATE TABLE timeperiod_override_include (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ timeperiod_id bytea20 NOT NULL,
+ override_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_timeperiod_override_include PRIMARY KEY (id)
+);
+
+ALTER TABLE timeperiod_override_include ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_override_include ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_override_include ALTER COLUMN timeperiod_id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_override_include ALTER COLUMN override_id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN timeperiod_override_include.id IS 'sha1(environment.id + include_id + timeperiod_id)';
+COMMENT ON COLUMN timeperiod_override_include.environment_id IS 'env.id';
+COMMENT ON COLUMN timeperiod_override_include.timeperiod_id IS 'timeperiod.id';
+COMMENT ON COLUMN timeperiod_override_include.override_id IS 'timeperiod.id';
+
+CREATE TABLE timeperiod_override_exclude (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ timeperiod_id bytea20 NOT NULL,
+ override_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_timeperiod_override_exclude PRIMARY KEY (id)
+);
+
+ALTER TABLE timeperiod_override_exclude ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_override_exclude ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_override_exclude ALTER COLUMN timeperiod_id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_override_exclude ALTER COLUMN override_id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN timeperiod_override_exclude.id IS 'sha1(environment.id + exclude_id + timeperiod_id)';
+COMMENT ON COLUMN timeperiod_override_exclude.environment_id IS 'env.id';
+COMMENT ON COLUMN timeperiod_override_exclude.timeperiod_id IS 'timeperiod.id';
+COMMENT ON COLUMN timeperiod_override_exclude.override_id IS 'timeperiod.id';
+
+CREATE TABLE timeperiod_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ timeperiod_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_timeperiod_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE timeperiod_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_customvar ALTER COLUMN timeperiod_id SET STORAGE PLAIN;
+ALTER TABLE timeperiod_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_timeperiod_customvar_timeperiod_id ON timeperiod_customvar(timeperiod_id, customvar_id);
+CREATE INDEX idx_timeperiod_customvar_customvar_id ON timeperiod_customvar(customvar_id, timeperiod_id);
+
+COMMENT ON COLUMN timeperiod_customvar.id IS 'sha1(environment.id + timeperiod_id + customvar_id)';
+COMMENT ON COLUMN timeperiod_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN timeperiod_customvar.timeperiod_id IS 'timeperiod.id';
+COMMENT ON COLUMN timeperiod_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+
+ name citext NOT NULL,
+ value text NOT NULL,
+
+ CONSTRAINT pk_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE customvar ALTER COLUMN name_checksum SET STORAGE PLAIN;
+
+COMMENT ON COLUMN customvar.id IS 'sha1(environment.id + name + value)';
+COMMENT ON COLUMN customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN customvar.name_checksum IS 'sha1(name)';
+
+CREATE TABLE customvar_flat (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+ flatname_checksum bytea20 NOT NULL,
+
+ flatname citext NOT NULL,
+ flatvalue text NOT NULL,
+
+ CONSTRAINT pk_customvar_flat PRIMARY KEY (id)
+);
+
+ALTER TABLE customvar_flat ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE customvar_flat ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE customvar_flat ALTER COLUMN customvar_id SET STORAGE PLAIN;
+ALTER TABLE customvar_flat ALTER COLUMN flatname_checksum SET STORAGE PLAIN;
+
+CREATE INDEX idx_customvar_flat_customvar_id ON customvar_flat(customvar_id);
+
+COMMENT ON COLUMN customvar_flat.id IS 'sha1(environment.id + flatname + flatvalue)';
+COMMENT ON COLUMN customvar_flat.environment_id IS 'environment.id';
+COMMENT ON COLUMN customvar_flat.customvar_id IS 'sha1(customvar.id)';
+COMMENT ON COLUMN customvar_flat.flatname_checksum IS 'sha1(flatname after conversion)';
+COMMENT ON COLUMN customvar_flat.flatname IS 'Path converted with `.` and `[ ]`';
+
+CREATE TABLE "user" (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ display_name citext NOT NULL,
+
+ email varchar(255) NOT NULL,
+ pager varchar(255) NOT NULL,
+
+ notifications_enabled boolenum NOT NULL DEFAULT 'n',
+
+ timeperiod_id bytea20 DEFAULT NULL,
+
+ states tinyuint NOT NULL,
+ types smalluint NOT NULL,
+
+ zone_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_user PRIMARY KEY (id)
+);
+
+ALTER TABLE "user" ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE "user" ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE "user" ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE "user" ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE "user" ALTER COLUMN timeperiod_id SET STORAGE PLAIN;
+ALTER TABLE "user" ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_user_display_name ON "user"(display_name);
+CREATE INDEX idx_user_name_ci ON "user"(name_ci);
+CREATE INDEX idx_user_name ON "user"(name);
+
+COMMENT ON COLUMN "user".id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN "user".environment_id IS 'environment.id';
+COMMENT ON COLUMN "user".name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN "user".properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN "user".timeperiod_id IS 'timeperiod.id';
+COMMENT ON COLUMN "user".zone_id IS 'zone.id';
+
+COMMENT ON INDEX idx_user_display_name IS 'User list filtered/ordered by display_name';
+COMMENT ON INDEX idx_user_name_ci IS 'User list filtered using quick search';
+COMMENT ON INDEX idx_user_name IS 'User list filtered/ordered by name; User detail filter';
+
+CREATE TABLE usergroup (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+ display_name citext NOT NULL,
+
+ zone_id bytea20 DEFAULT NULL,
+
+ CONSTRAINT pk_usergroup PRIMARY KEY (id)
+);
+
+ALTER TABLE usergroup ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE usergroup ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE usergroup ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE usergroup ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE usergroup ALTER COLUMN zone_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_usergroup_display_name ON usergroup(display_name);
+CREATE INDEX idx_usergroup_name_ci ON usergroup(name_ci);
+CREATE INDEX idx_usergroup_name ON usergroup(name);
+
+COMMENT ON COLUMN usergroup.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN usergroup.environment_id IS 'environment.id';
+COMMENT ON COLUMN usergroup.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN usergroup.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN usergroup.zone_id IS 'zone.id';
+
+COMMENT ON INDEX idx_usergroup_display_name IS 'Usergroup list filtered/ordered by display_name';
+COMMENT ON INDEX idx_usergroup_name_ci IS 'Usergroup list filtered using quick search';
+COMMENT ON INDEX idx_usergroup_name IS 'Usergroup list filtered/ordered by name; User detail filter';
+
+CREATE TABLE usergroup_member (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ user_id bytea20 NOT NULL,
+ usergroup_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_usergroup_member PRIMARY KEY (id)
+);
+
+ALTER TABLE usergroup_member ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE usergroup_member ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE usergroup_member ALTER COLUMN user_id SET STORAGE PLAIN;
+ALTER TABLE usergroup_member ALTER COLUMN usergroup_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_usergroup_member_user_id ON usergroup_member(user_id, usergroup_id);
+CREATE INDEX idx_usergroup_member_usergroup_id ON usergroup_member(usergroup_id, user_id);
+
+COMMENT ON COLUMN usergroup_member.id IS 'sha1(environment.id + usergroup_id + user_id)';
+COMMENT ON COLUMN usergroup_member.environment_id IS 'environment.id';
+COMMENT ON COLUMN usergroup_member.user_id IS 'user.id';
+COMMENT ON COLUMN usergroup_member.usergroup_id IS 'usergroup.id';
+
+CREATE TABLE user_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ user_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_user_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE user_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE user_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE user_customvar ALTER COLUMN user_id SET STORAGE PLAIN;
+ALTER TABLE user_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_user_customvar_user_id ON user_customvar(user_id, customvar_id);
+CREATE INDEX idx_user_customvar_customvar_id ON user_customvar(customvar_id, user_id);
+
+COMMENT ON COLUMN user_customvar.id IS 'sha1(environment.id + user_id + customvar_id)';
+COMMENT ON COLUMN user_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN user_customvar.user_id IS 'user.id';
+COMMENT ON COLUMN user_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE usergroup_customvar (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ usergroup_id bytea20 NOT NULL,
+ customvar_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_usergroup_customvar PRIMARY KEY (id)
+);
+
+ALTER TABLE usergroup_customvar ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE usergroup_customvar ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE usergroup_customvar ALTER COLUMN usergroup_id SET STORAGE PLAIN;
+ALTER TABLE usergroup_customvar ALTER COLUMN customvar_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_usergroup_customvar_usergroup_id ON usergroup_customvar(usergroup_id, customvar_id);
+CREATE INDEX idx_usergroup_customvar_customvar_id ON usergroup_customvar(customvar_id, usergroup_id);
+
+COMMENT ON COLUMN usergroup_customvar.id IS 'sha1(environment.id + usergroup_id + customvar_id)';
+COMMENT ON COLUMN usergroup_customvar.environment_id IS 'environment.id';
+COMMENT ON COLUMN usergroup_customvar.usergroup_id IS 'usergroup.id';
+COMMENT ON COLUMN usergroup_customvar.customvar_id IS 'customvar.id';
+
+CREATE TABLE zone (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ name_checksum bytea20 NOT NULL,
+ properties_checksum bytea20 NOT NULL,
+
+ name varchar(255) NOT NULL,
+ name_ci citext NOT NULL,
+
+ is_global boolenum NOT NULL DEFAULT 'n',
+ parent_id bytea20 DEFAULT NULL,
+
+ depth tinyuint NOT NULL,
+
+ CONSTRAINT pk_zone PRIMARY KEY (id)
+);
+
+ALTER TABLE zone ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE zone ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE zone ALTER COLUMN name_checksum SET STORAGE PLAIN;
+ALTER TABLE zone ALTER COLUMN properties_checksum SET STORAGE PLAIN;
+ALTER TABLE zone ALTER COLUMN parent_id SET STORAGE PLAIN;
+
+CREATE UNIQUE INDEX idx_environment_id_id ON zone(environment_id, id);
+CREATE INDEX idx_zone_parent_id ON zone(parent_id);
+
+COMMENT ON COLUMN zone.id IS 'sha1(environment.id + name)';
+COMMENT ON COLUMN zone.environment_id IS 'environment.id';
+COMMENT ON COLUMN zone.name_checksum IS 'sha1(name)';
+COMMENT ON COLUMN zone.properties_checksum IS 'sha1(all properties)';
+COMMENT ON COLUMN zone.parent_id IS 'zone.id';
+
+CREATE TABLE notification_history (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+ notification_id bytea20 NOT NULL,
+
+ type notification_type NOT NULL DEFAULT 'downtime_start',
+ send_time biguint NOT NULL,
+ state tinyuint NOT NULL,
+ previous_hard_state tinyuint NOT NULL,
+ author text NOT NULL,
+ "text" text NOT NULL,
+ users_notified smalluint NOT NULL,
+
+ CONSTRAINT pk_notification_history PRIMARY KEY (id)
+);
+
+ALTER TABLE notification_history ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE notification_history ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE notification_history ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE notification_history ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE notification_history ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE notification_history ALTER COLUMN notification_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_notification_history_send_time ON notification_history(send_time DESC);
+CREATE INDEX idx_notification_history_env_send_time ON notification_history(environment_id, send_time);
+
+COMMENT ON COLUMN notification_history.id IS 'sha1(environment.name + notification.name + type + send_time)';
+COMMENT ON COLUMN notification_history.environment_id IS 'environment.id';
+COMMENT ON COLUMN notification_history.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN notification_history.host_id IS 'host.id';
+COMMENT ON COLUMN notification_history.service_id IS 'service.id';
+COMMENT ON COLUMN notification_history.notification_id IS 'notification.id';
+
+COMMENT ON INDEX idx_notification_history_send_time IS 'Notification list filtered/ordered by send_time';
+COMMENT ON INDEX idx_notification_history_env_send_time IS 'Filter for history retention';
+
+CREATE TABLE user_notification_history (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ notification_history_id bytea20 NOT NULL,
+ user_id bytea20 NOT NULL,
+
+ CONSTRAINT pk_user_notification_history PRIMARY KEY (id),
+
+ CONSTRAINT fk_user_notification_history_notification_history FOREIGN KEY (notification_history_id) REFERENCES notification_history (id) ON DELETE CASCADE
+);
+
+ALTER TABLE user_notification_history ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE user_notification_history ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE user_notification_history ALTER COLUMN notification_history_id SET STORAGE PLAIN;
+ALTER TABLE user_notification_history ALTER COLUMN user_id SET STORAGE PLAIN;
+
+COMMENT ON COLUMN user_notification_history.id IS 'sha1(notification_history_id + user_id)';
+COMMENT ON COLUMN user_notification_history.environment_id IS 'environment.id';
+COMMENT ON COLUMN user_notification_history.notification_history_id IS 'UUID notification_history.id';
+COMMENT ON COLUMN user_notification_history.user_id IS 'user.id';
+
+CREATE TABLE state_history (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ event_time biguint NOT NULL,
+ state_type state_type NOT NULL DEFAULT 'hard',
+ soft_state tinyuint NOT NULL,
+ hard_state tinyuint NOT NULL,
+ previous_soft_state tinyuint NOT NULL,
+ previous_hard_state tinyuint NOT NULL,
+ check_attempt tinyuint NOT NULL,
+ output text DEFAULT NULL,
+ long_output text DEFAULT NULL,
+ max_check_attempts uint NOT NULL,
+ check_source text DEFAULT NULL,
+ scheduling_source text DEFAULT NULL,
+
+ CONSTRAINT pk_state_history PRIMARY KEY (id)
+);
+
+ALTER TABLE state_history ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE state_history ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE state_history ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE state_history ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE state_history ALTER COLUMN service_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_state_history_env_event_time ON state_history(environment_id, event_time);
+
+COMMENT ON COLUMN state_history.id IS 'sha1(environment.name + host|service.name + event_time)';
+COMMENT ON COLUMN state_history.environment_id IS 'environment.id';
+COMMENT ON COLUMN state_history.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN state_history.host_id IS 'host.id';
+COMMENT ON COLUMN state_history.service_id IS 'service.id';
+
+COMMENT ON INDEX idx_state_history_env_event_time IS 'Filter for history retention';
+
+CREATE TABLE downtime_history (
+ downtime_id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ triggered_by_id bytea20 DEFAULT NULL,
+ parent_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ entry_time biguint NOT NULL,
+ author citext NOT NULL,
+ cancelled_by citext DEFAULT NULL,
+ comment text NOT NULL,
+ is_flexible boolenum NOT NULL DEFAULT 'n',
+ flexible_duration biguint NOT NULL,
+ scheduled_start_time biguint NOT NULL,
+ scheduled_end_time biguint NOT NULL,
+ start_time biguint NOT NULL,
+ end_time biguint NOT NULL,
+ scheduled_by varchar(767) DEFAULT NULL,
+ has_been_cancelled boolenum NOT NULL DEFAULT 'n',
+ trigger_time biguint NOT NULL,
+ cancel_time biguint DEFAULT NULL,
+
+ CONSTRAINT pk_downtime_history PRIMARY KEY (downtime_id)
+);
+
+ALTER TABLE downtime_history ALTER COLUMN downtime_id SET STORAGE PLAIN;
+ALTER TABLE downtime_history ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE downtime_history ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE downtime_history ALTER COLUMN triggered_by_id SET STORAGE PLAIN;
+ALTER TABLE downtime_history ALTER COLUMN parent_id SET STORAGE PLAIN;
+ALTER TABLE downtime_history ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE downtime_history ALTER COLUMN service_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_downtime_history_env_end_time ON downtime_history(environment_id, end_time);
+
+COMMENT ON COLUMN downtime_history.downtime_id IS 'downtime.id';
+COMMENT ON COLUMN downtime_history.environment_id IS 'environment.id';
+COMMENT ON COLUMN downtime_history.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN downtime_history.triggered_by_id IS '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.';
+COMMENT ON COLUMN downtime_history.parent_id IS 'For service downtimes, the ID of the host downtime that created this downtime by using the "all_services" flag of the schedule-downtime API.';
+COMMENT ON COLUMN downtime_history.host_id IS 'host.id';
+COMMENT ON COLUMN downtime_history.service_id IS 'service.id';
+COMMENT ON COLUMN downtime_history.start_time IS 'Time when the host went into a problem state during the downtimes timeframe';
+COMMENT ON COLUMN downtime_history.end_time IS 'Problem state assumed: scheduled_end_time if fixed, start_time + duration otherwise';
+COMMENT ON COLUMN downtime_history.scheduled_by IS 'Name of the ScheduledDowntime which created this Downtime. 255+1+255+1+255, i.e. "host.name!service.name!scheduled-downtime-name"';
+
+COMMENT ON INDEX idx_downtime_history_env_end_time IS 'Filter for history retention';
+
+CREATE TABLE comment_history (
+ comment_id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ entry_time biguint NOT NULL,
+ author citext NOT NULL,
+ removed_by citext DEFAULT NULL,
+ comment text NOT NULL,
+ entry_type comment_type NOT NULL DEFAULT 'comment',
+ is_persistent boolenum NOT NULL DEFAULT 'n',
+ is_sticky boolenum NOT NULL DEFAULT 'n',
+ expire_time biguint DEFAULT NULL,
+ remove_time biguint DEFAULT NULL,
+ has_been_removed boolenum NOT NULL DEFAULT 'n',
+
+ CONSTRAINT pk_comment_history PRIMARY KEY (comment_id)
+);
+
+ALTER TABLE comment_history ALTER COLUMN comment_id SET STORAGE PLAIN;
+ALTER TABLE comment_history ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE comment_history ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE comment_history ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE comment_history ALTER COLUMN service_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_comment_history_env_remove_time ON comment_history(environment_id, remove_time);
+
+COMMENT ON COLUMN comment_history.comment_id IS 'comment.id';
+COMMENT ON COLUMN comment_history.environment_id IS 'environment.id';
+COMMENT ON COLUMN comment_history.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN comment_history.host_id IS 'host.id';
+COMMENT ON COLUMN comment_history.service_id IS 'service.id';
+
+COMMENT ON INDEX idx_comment_history_env_remove_time IS 'Filter for history retention';
+
+CREATE TABLE flapping_history (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ start_time biguint NOT NULL,
+ end_time biguint 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,
+
+ CONSTRAINT pk_flapping_history PRIMARY KEY (id)
+);
+
+ALTER TABLE flapping_history ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE flapping_history ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE flapping_history ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE flapping_history ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE flapping_history ALTER COLUMN service_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_flapping_history_env_end_time ON flapping_history(environment_id, end_time);
+
+COMMENT ON COLUMN flapping_history.id IS 'sha1(environment.id + "Host"|"Service" + host|service.name + start_time)';
+COMMENT ON COLUMN flapping_history.environment_id IS 'environment.id';
+COMMENT ON COLUMN flapping_history.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN flapping_history.host_id IS 'host.id';
+COMMENT ON COLUMN flapping_history.service_id IS 'service.id';
+
+COMMENT ON INDEX idx_flapping_history_env_end_time IS 'Filter for history retention';
+
+CREATE TABLE acknowledgement_history (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ set_time biguint NOT NULL,
+ clear_time biguint DEFAULT NULL,
+ author citext DEFAULT NULL,
+ cleared_by citext DEFAULT NULL,
+ comment text DEFAULT NULL,
+ expire_time biguint DEFAULT NULL,
+ is_sticky boolenum DEFAULT NULL,
+ is_persistent boolenum DEFAULT NULL,
+
+ CONSTRAINT pk_acknowledgement_history PRIMARY KEY (id)
+);
+
+ALTER TABLE acknowledgement_history ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE acknowledgement_history ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE acknowledgement_history ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE acknowledgement_history ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE acknowledgement_history ALTER COLUMN service_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_acknowledgement_history_env_clear_time ON acknowledgement_history(environment_id, clear_time);
+
+COMMENT ON COLUMN acknowledgement_history.id IS 'sha1(environment.id + "Host"|"Service" + host|service.name + set_time)';
+COMMENT ON COLUMN acknowledgement_history.environment_id IS 'environment.id';
+COMMENT ON COLUMN acknowledgement_history.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN acknowledgement_history.host_id IS 'host.id';
+COMMENT ON COLUMN acknowledgement_history.service_id IS 'service.id';
+COMMENT ON COLUMN acknowledgement_history.author IS 'NULL if ack_set event happened before Icinga DB history recording';
+COMMENT ON COLUMN acknowledgement_history.comment IS 'NULL if ack_set event happened before Icinga DB history recording';
+COMMENT ON COLUMN acknowledgement_history.is_sticky IS 'NULL if ack_set event happened before Icinga DB history recording';
+COMMENT ON COLUMN acknowledgement_history.is_persistent IS 'NULL if ack_set event happened before Icinga DB history recording';
+
+COMMENT ON INDEX idx_acknowledgement_history_env_clear_time IS 'Filter for history retention';
+
+CREATE TABLE history (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL DEFAULT 'host',
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+ notification_history_id bytea20 DEFAULT NULL,
+ state_history_id bytea20 DEFAULT NULL,
+ downtime_history_id bytea20 DEFAULT NULL,
+ comment_history_id bytea20 DEFAULT NULL,
+ flapping_history_id bytea20 DEFAULT NULL,
+ acknowledgement_history_id bytea20 DEFAULT NULL,
+
+ event_type history_type NOT NULL DEFAULT 'notification',
+ event_time biguint NOT NULL,
+
+ CONSTRAINT pk_history 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
+);
+
+ALTER TABLE history ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN notification_history_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN state_history_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN downtime_history_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN comment_history_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN flapping_history_id SET STORAGE PLAIN;
+ALTER TABLE history ALTER COLUMN acknowledgement_history_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_history_event_time ON history(event_time);
+CREATE INDEX idx_history_acknowledgement ON history(acknowledgement_history_id);
+CREATE INDEX idx_history_comment ON history(comment_history_id);
+CREATE INDEX idx_history_downtime ON history(downtime_history_id);
+CREATE INDEX idx_history_flapping ON history(flapping_history_id);
+CREATE INDEX idx_history_notification ON history(notification_history_id);
+CREATE INDEX idx_history_state ON history(state_history_id);
+CREATE INDEX idx_history_host_service_id ON history(host_id, service_id, event_time);
+
+COMMENT ON COLUMN history.id IS 'sha1(environment.name + event_type + x...) given that sha1(environment.name + x...) = *_history_id';
+COMMENT ON COLUMN history.environment_id IS 'environment.id';
+COMMENT ON COLUMN history.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN history.host_id IS 'host.id';
+COMMENT ON COLUMN history.service_id IS 'service.id';
+COMMENT ON COLUMN history.notification_history_id IS 'notification_history.id';
+COMMENT ON COLUMN history.state_history_id IS 'state_history.id';
+COMMENT ON COLUMN history.downtime_history_id IS 'downtime_history.downtime_id';
+COMMENT ON COLUMN history.comment_history_id IS 'comment_history.comment_id';
+COMMENT ON COLUMN history.flapping_history_id IS 'flapping_history.id';
+COMMENT ON COLUMN history.acknowledgement_history_id IS 'acknowledgement_history.id';
+
+COMMENT ON INDEX idx_history_event_time IS 'History filtered/ordered by event_time';
+COMMENT ON INDEX idx_history_host_service_id IS 'Host/service history detail filter';
+
+CREATE TABLE sla_history_state (
+ id bytea20 NOT NULL,
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL,
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ event_time biguint NOT NULL,
+ hard_state tinyuint NOT NULL,
+ previous_hard_state tinyuint NOT NULL,
+
+ CONSTRAINT pk_sla_history_state PRIMARY KEY (id)
+);
+
+ALTER TABLE sla_history_state ALTER COLUMN id SET STORAGE PLAIN;
+ALTER TABLE sla_history_state ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE sla_history_state ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE sla_history_state ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE sla_history_state ALTER COLUMN service_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_sla_history_state_event ON sla_history_state(host_id, service_id, event_time);
+CREATE INDEX idx_sla_history_state_env_event_time ON sla_history_state (environment_id, event_time);
+
+COMMENT ON COLUMN sla_history_state.id IS 'state_history.id (may reference already deleted rows)';
+COMMENT ON COLUMN sla_history_state.environment_id IS 'environment.id';
+COMMENT ON COLUMN sla_history_state.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN sla_history_state.host_id IS 'host.id';
+COMMENT ON COLUMN sla_history_state.service_id IS 'service.id';
+COMMENT ON COLUMN sla_history_state.event_time IS 'unix timestamp the event occurred';
+COMMENT ON COLUMN sla_history_state.hard_state IS 'hard state after this event';
+COMMENT ON COLUMN sla_history_state.previous_hard_state IS 'hard state before this event';
+
+COMMENT ON INDEX idx_sla_history_state_event IS 'Filter for calculating the sla reports';
+COMMENT ON INDEX idx_sla_history_state_env_event_time IS 'Filter for history retention';
+
+CREATE TABLE sla_history_downtime (
+ environment_id bytea20 NOT NULL,
+ endpoint_id bytea20 DEFAULT NULL,
+ object_type checkable_type NOT NULL,
+ host_id bytea20 NOT NULL,
+ service_id bytea20 DEFAULT NULL,
+
+ downtime_id bytea20 NOT NULL,
+ downtime_start biguint NOT NULL,
+ downtime_end biguint NOT NULL,
+
+ CONSTRAINT pk_sla_history_downtime PRIMARY KEY (downtime_id)
+);
+
+ALTER TABLE sla_history_downtime ALTER COLUMN environment_id SET STORAGE PLAIN;
+ALTER TABLE sla_history_downtime ALTER COLUMN endpoint_id SET STORAGE PLAIN;
+ALTER TABLE sla_history_downtime ALTER COLUMN host_id SET STORAGE PLAIN;
+ALTER TABLE sla_history_downtime ALTER COLUMN service_id SET STORAGE PLAIN;
+ALTER TABLE sla_history_downtime ALTER COLUMN downtime_id SET STORAGE PLAIN;
+
+CREATE INDEX idx_sla_history_downtime_event ON sla_history_downtime(host_id, service_id, downtime_start, downtime_end);
+CREATE INDEX idx_sla_history_downtime_env_downtime_end ON sla_history_downtime (environment_id, downtime_end);
+
+COMMENT ON INDEX idx_sla_history_downtime_event IS 'Filter for calculating the sla reports';
+COMMENT ON INDEX idx_sla_history_downtime_env_downtime_end IS 'Filter for sla history retention';
+
+COMMENT ON COLUMN sla_history_downtime.environment_id IS 'environment.id';
+COMMENT ON COLUMN sla_history_downtime.endpoint_id IS 'endpoint.id';
+COMMENT ON COLUMN sla_history_downtime.host_id IS 'host.id';
+COMMENT ON COLUMN sla_history_downtime.service_id IS 'service.id';
+COMMENT ON COLUMN sla_history_downtime.downtime_id IS 'downtime.id (may reference already deleted rows)';
+COMMENT ON COLUMN sla_history_downtime.downtime_start IS 'start time of the downtime';
+COMMENT ON COLUMN sla_history_downtime.downtime_end IS 'end time of the downtime';
+
+CREATE SEQUENCE icingadb_schema_id_seq;
+
+CREATE TABLE icingadb_schema (
+ id uint NOT NULL DEFAULT nextval('icingadb_schema_id_seq'),
+ version smalluint NOT NULL,
+ timestamp biguint NOT NULL,
+
+ CONSTRAINT pk_icingadb_schema PRIMARY KEY (id)
+);
+
+ALTER SEQUENCE icingadb_schema_id_seq OWNED BY icingadb_schema.id;
+
+INSERT INTO icingadb_schema (version, timestamp)
+ VALUES (1, extract(epoch from now()) * 1000);