summaryrefslogtreecommitdiffstats
path: root/schema
diff options
context:
space:
mode:
Diffstat (limited to 'schema')
-rw-r--r--schema/mysql/schema.sql10
-rw-r--r--schema/mysql/upgrades/1.0.0-rc2.sql2
-rw-r--r--schema/mysql/upgrades/1.0.0.sql2
-rw-r--r--schema/mysql/upgrades/1.1.1.sql2
-rw-r--r--schema/mysql/upgrades/1.2.0.sql13
-rw-r--r--schema/mysql/upgrades/optional/1.2.0-history.sql1
-rw-r--r--schema/pgsql/schema.sql14
-rw-r--r--schema/pgsql/upgrades/1.2.0.sql153
-rw-r--r--schema/pgsql/upgrades/optional/1.2-0-history.sql3
9 files changed, 185 insertions, 15 deletions
diff --git a/schema/mysql/schema.sql b/schema/mysql/schema.sql
index f4434f1..745a5e6 100644
--- a/schema/mysql/schema.sql
+++ b/schema/mysql/schema.sql
@@ -292,7 +292,7 @@ CREATE TABLE host_state (
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,
+ check_attempt int unsigned NOT NULL,
severity smallint unsigned NOT NULL,
output longtext DEFAULT NULL,
@@ -460,7 +460,7 @@ CREATE TABLE service_state (
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,
+ check_attempt int unsigned NOT NULL,
severity smallint unsigned NOT NULL,
output longtext DEFAULT NULL,
@@ -1147,7 +1147,7 @@ CREATE TABLE state_history (
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,
+ check_attempt int unsigned NOT NULL, -- may be a tinyint unsigned, see https://icinga.com/docs/icinga-db/latest/doc/04-Upgrading/#upgrading-to-icinga-db-v112
output longtext DEFAULT NULL,
long_output longtext DEFAULT NULL,
max_check_attempts int unsigned NOT NULL,
@@ -1289,7 +1289,7 @@ CREATE TABLE history (
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_event_time_event_type (event_time, event_type) COMMENT 'History filtered/ordered by event_time/event_type',
INDEX idx_history_acknowledgement (acknowledgement_history_id),
INDEX idx_history_comment (comment_history_id),
INDEX idx_history_downtime (downtime_history_id),
@@ -1343,4 +1343,4 @@ CREATE TABLE icingadb_schema (
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
INSERT INTO icingadb_schema (version, timestamp)
- VALUES (4, CURRENT_TIMESTAMP() * 1000);
+ VALUES (5, UNIX_TIMESTAMP() * 1000);
diff --git a/schema/mysql/upgrades/1.0.0-rc2.sql b/schema/mysql/upgrades/1.0.0-rc2.sql
index 50fb2f9..d4695cd 100644
--- a/schema/mysql/upgrades/1.0.0-rc2.sql
+++ b/schema/mysql/upgrades/1.0.0-rc2.sql
@@ -156,7 +156,7 @@ ALTER TABLE acknowledgement_history
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);
+ VALUES (2, UNIX_TIMESTAMP() * 1000);
ALTER TABLE host_state
MODIFY output longtext DEFAULT NULL,
diff --git a/schema/mysql/upgrades/1.0.0.sql b/schema/mysql/upgrades/1.0.0.sql
index 16bb45e..054e10e 100644
--- a/schema/mysql/upgrades/1.0.0.sql
+++ b/schema/mysql/upgrades/1.0.0.sql
@@ -288,4 +288,4 @@ INSERT INTO sla_history_downtime
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);
+ VALUES (3, UNIX_TIMESTAMP() * 1000);
diff --git a/schema/mysql/upgrades/1.1.1.sql b/schema/mysql/upgrades/1.1.1.sql
index 264ecae..b0d5b69 100644
--- a/schema/mysql/upgrades/1.1.1.sql
+++ b/schema/mysql/upgrades/1.1.1.sql
@@ -34,4 +34,4 @@ ALTER TABLE history
UNLOCK TABLES;
INSERT INTO icingadb_schema (version, timestamp)
- VALUES (4, CURRENT_TIMESTAMP() * 1000);
+ VALUES (4, UNIX_TIMESTAMP() * 1000);
diff --git a/schema/mysql/upgrades/1.2.0.sql b/schema/mysql/upgrades/1.2.0.sql
new file mode 100644
index 0000000..74cbcb1
--- /dev/null
+++ b/schema/mysql/upgrades/1.2.0.sql
@@ -0,0 +1,13 @@
+UPDATE icingadb_schema SET timestamp = UNIX_TIMESTAMP(timestamp / 1000) * 1000 WHERE timestamp > 20000000000000000;
+
+ALTER TABLE history ADD INDEX idx_history_event_time_event_type (event_time, event_type) COMMENT 'History filtered/ordered by event_time/event_type';
+ALTER TABLE history DROP INDEX idx_history_event_time;
+
+ALTER TABLE host_state MODIFY COLUMN check_attempt int unsigned NOT NULL;
+
+ALTER TABLE service_state MODIFY COLUMN check_attempt int unsigned NOT NULL;
+
+ALTER TABLE state_history MODIFY COLUMN check_attempt tinyint unsigned NOT NULL COMMENT 'optional schema upgrade not applied yet, see https://icinga.com/docs/icinga-db/latest/doc/04-Upgrading/#upgrading-to-icinga-db-v112';
+
+INSERT INTO icingadb_schema (version, timestamp)
+ VALUES (5, UNIX_TIMESTAMP() * 1000);
diff --git a/schema/mysql/upgrades/optional/1.2.0-history.sql b/schema/mysql/upgrades/optional/1.2.0-history.sql
new file mode 100644
index 0000000..4081fcb
--- /dev/null
+++ b/schema/mysql/upgrades/optional/1.2.0-history.sql
@@ -0,0 +1 @@
+ALTER TABLE state_history MODIFY COLUMN check_attempt int unsigned NOT NULL;
diff --git a/schema/pgsql/schema.sql b/schema/pgsql/schema.sql
index 9027fac..708c914 100644
--- a/schema/pgsql/schema.sql
+++ b/schema/pgsql/schema.sql
@@ -166,7 +166,7 @@ BEGIN
END IF;
END LOOP;
- RETURN 100 * (total_time - problem_time) / total_time;
+ RETURN (100 * (total_time - problem_time)::decimal / total_time)::decimal(7, 4);
END;
$$;
@@ -405,7 +405,7 @@ CREATE TABLE host_state (
hard_state tinyuint NOT NULL,
previous_soft_state tinyuint NOT NULL,
previous_hard_state tinyuint NOT NULL,
- check_attempt tinyuint NOT NULL,
+ check_attempt uint NOT NULL,
severity smalluint NOT NULL,
output text DEFAULT NULL,
@@ -675,7 +675,7 @@ CREATE TABLE service_state (
hard_state tinyuint NOT NULL,
previous_soft_state tinyuint NOT NULL,
previous_hard_state tinyuint NOT NULL,
- check_attempt tinyuint NOT NULL,
+ check_attempt uint NOT NULL,
severity smalluint NOT NULL,
output text DEFAULT NULL,
@@ -1846,7 +1846,7 @@ CREATE TABLE state_history (
hard_state tinyuint NOT NULL,
previous_soft_state tinyuint NOT NULL,
previous_hard_state tinyuint NOT NULL,
- check_attempt tinyuint NOT NULL,
+ check_attempt uint NOT NULL, -- may be a tinyuint, see https://icinga.com/docs/icinga-db/latest/doc/04-Upgrading/#upgrading-to-icinga-db-v112
output text DEFAULT NULL,
long_output text DEFAULT NULL,
max_check_attempts uint NOT NULL,
@@ -2074,7 +2074,7 @@ 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_event_time_event_type ON history(event_time, event_type);
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);
@@ -2095,7 +2095,7 @@ 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_event_time_event_type IS 'History filtered/ordered by event_time/event_type';
COMMENT ON INDEX idx_history_host_service_id IS 'Host/service history detail filter';
CREATE TABLE sla_history_state (
@@ -2181,4 +2181,4 @@ CREATE TABLE icingadb_schema (
ALTER SEQUENCE icingadb_schema_id_seq OWNED BY icingadb_schema.id;
INSERT INTO icingadb_schema (version, timestamp)
- VALUES (2, extract(epoch from now()) * 1000);
+ VALUES (3, extract(epoch from now()) * 1000);
diff --git a/schema/pgsql/upgrades/1.2.0.sql b/schema/pgsql/upgrades/1.2.0.sql
new file mode 100644
index 0000000..2203ffb
--- /dev/null
+++ b/schema/pgsql/upgrades/1.2.0.sql
@@ -0,0 +1,153 @@
+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)::decimal / total_time)::decimal(7, 4);
+END;
+$$;
+
+CREATE INDEX CONCURRENTLY idx_history_event_time_event_type ON history(event_time, event_type);
+COMMENT ON INDEX idx_history_event_time_event_type IS 'History filtered/ordered by event_time/event_type';
+
+DROP INDEX idx_history_event_time;
+
+ALTER TABLE host_state ALTER COLUMN check_attempt TYPE uint;
+
+ALTER TABLE service_state ALTER COLUMN check_attempt TYPE uint;
+
+COMMENT ON COLUMN state_history.check_attempt IS 'optional schema upgrade not applied yet, see https://icinga.com/docs/icinga-db/latest/doc/04-Upgrading/#upgrading-to-icinga-db-v112';
+
+INSERT INTO icingadb_schema (version, timestamp)
+ VALUES (3, extract(epoch from now()) * 1000);
diff --git a/schema/pgsql/upgrades/optional/1.2-0-history.sql b/schema/pgsql/upgrades/optional/1.2-0-history.sql
new file mode 100644
index 0000000..ea95765
--- /dev/null
+++ b/schema/pgsql/upgrades/optional/1.2-0-history.sql
@@ -0,0 +1,3 @@
+ALTER TABLE state_history ALTER COLUMN check_attempt TYPE uint;
+
+COMMENT ON COLUMN state_history.check_attempt IS NULL;
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-21 20:01:56 +0000