1 files changed, 153 insertions, 0 deletions
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);