diff options
Diffstat (limited to '')
8 files changed, 98 insertions, 0 deletions
diff --git a/cmd/icingadb-migrate/embed/comment_query.sql b/cmd/icingadb-migrate/embed/comment_query.sql new file mode 100644 index 0000000..774ccf9 --- /dev/null +++ b/cmd/icingadb-migrate/embed/comment_query.sql @@ -0,0 +1,11 @@ +SELECT ch.commenthistory_id, UNIX_TIMESTAMP(ch.entry_time) entry_time, + ch.entry_time_usec, ch.entry_type, ch.author_name, ch.comment_data, ch.is_persistent, + COALESCE(UNIX_TIMESTAMP(ch.expiration_time), 0) expiration_time, + COALESCE(UNIX_TIMESTAMP(ch.deletion_time), 0) deletion_time, + ch.deletion_time_usec, ch.name, o.objecttype_id, o.name1, COALESCE(o.name2, '') name2 +FROM icinga_commenthistory ch USE INDEX (PRIMARY) +INNER JOIN icinga_objects o ON o.object_id=ch.object_id +WHERE ch.commenthistory_id BETWEEN :fromid AND :toid +AND ch.commenthistory_id > :checkpoint -- where we were interrupted +ORDER BY ch.commenthistory_id -- this way we know what has already been migrated from just the last row's ID +LIMIT :bulk diff --git a/cmd/icingadb-migrate/embed/downtime_query.sql b/cmd/icingadb-migrate/embed/downtime_query.sql new file mode 100644 index 0000000..e3d36bf --- /dev/null +++ b/cmd/icingadb-migrate/embed/downtime_query.sql @@ -0,0 +1,14 @@ +SELECT dh.downtimehistory_id, UNIX_TIMESTAMP(dh.entry_time) entry_time, dh.author_name, dh.comment_data, + dh.is_fixed, dh.duration, UNIX_TIMESTAMP(dh.scheduled_start_time) scheduled_start_time, + COALESCE(UNIX_TIMESTAMP(dh.scheduled_end_time), 0) scheduled_end_time, + COALESCE(UNIX_TIMESTAMP(dh.actual_start_time), 0) actual_start_time, dh.actual_start_time_usec, + COALESCE(UNIX_TIMESTAMP(dh.actual_end_time), 0) actual_end_time, dh.actual_end_time_usec, dh.was_cancelled, + COALESCE(UNIX_TIMESTAMP(dh.trigger_time), 0) trigger_time, dh.name, o.objecttype_id, + o.name1, COALESCE(o.name2, '') name2, COALESCE(sd.name, '') triggered_by +FROM icinga_downtimehistory dh USE INDEX (PRIMARY) +INNER JOIN icinga_objects o ON o.object_id=dh.object_id +LEFT JOIN icinga_scheduleddowntime sd ON sd.scheduleddowntime_id=dh.triggered_by_id +WHERE dh.downtimehistory_id BETWEEN :fromid AND :toid +AND dh.downtimehistory_id > :checkpoint -- where we were interrupted +ORDER BY dh.downtimehistory_id -- this way we know what has already been migrated from just the last row's ID +LIMIT :bulk diff --git a/cmd/icingadb-migrate/embed/event_time_cache_schema.sql b/cmd/icingadb-migrate/embed/event_time_cache_schema.sql new file mode 100644 index 0000000..5940754 --- /dev/null +++ b/cmd/icingadb-migrate/embed/event_time_cache_schema.sql @@ -0,0 +1,15 @@ +PRAGMA main.auto_vacuum = 1; + +-- Icinga DB's flapping_history#start_time per flapping_end row (IDO's icinga_flappinghistory#flappinghistory_id). +CREATE TABLE IF NOT EXISTS end_start_time ( + history_id INT PRIMARY KEY, + event_time INT NOT NULL, + event_time_usec INT NOT NULL +); + +-- Helper table, the last start_time per icinga_statehistory#object_id. +CREATE TABLE IF NOT EXISTS last_start_time ( + object_id INT PRIMARY KEY, + event_time INT NOT NULL, + event_time_usec INT NOT NULL +); diff --git a/cmd/icingadb-migrate/embed/flapping_query.sql b/cmd/icingadb-migrate/embed/flapping_query.sql new file mode 100644 index 0000000..5e25bde --- /dev/null +++ b/cmd/icingadb-migrate/embed/flapping_query.sql @@ -0,0 +1,9 @@ +SELECT fh.flappinghistory_id, UNIX_TIMESTAMP(fh.event_time) event_time, + fh.event_time_usec, fh.event_type, fh.percent_state_change, fh.low_threshold, + fh.high_threshold, o.objecttype_id, o.name1, COALESCE(o.name2, '') name2 +FROM icinga_flappinghistory fh USE INDEX (PRIMARY) +INNER JOIN icinga_objects o ON o.object_id=fh.object_id +WHERE fh.flappinghistory_id BETWEEN :fromid AND :toid +AND fh.flappinghistory_id > :checkpoint -- where we were interrupted +ORDER BY fh.flappinghistory_id -- this way we know what has already been migrated from just the last row's ID +LIMIT :bulk diff --git a/cmd/icingadb-migrate/embed/ido_migration_progress_schema.sql b/cmd/icingadb-migrate/embed/ido_migration_progress_schema.sql new file mode 100644 index 0000000..54c1c00 --- /dev/null +++ b/cmd/icingadb-migrate/embed/ido_migration_progress_schema.sql @@ -0,0 +1,9 @@ +CREATE TABLE IF NOT EXISTS ido_migration_progress ( + environment_id CHAR(40) NOT NULL, -- Hex SHA1. Rationale: CHAR(40) is not RDBMS-specific + history_type VARCHAR(63) NOT NULL, + from_ts BIGINT NOT NULL, + to_ts BIGINT NOT NULL, + last_ido_id BIGINT NOT NULL, + + CONSTRAINT pk_ido_migration_progress PRIMARY KEY (environment_id, history_type, from_ts, to_ts) +); diff --git a/cmd/icingadb-migrate/embed/notification_query.sql b/cmd/icingadb-migrate/embed/notification_query.sql new file mode 100644 index 0000000..f963b2e --- /dev/null +++ b/cmd/icingadb-migrate/embed/notification_query.sql @@ -0,0 +1,9 @@ +SELECT n.notification_id, n.notification_reason, UNIX_TIMESTAMP(n.end_time) end_time, + n.end_time_usec, n.state, COALESCE(n.output, '') output, n.long_output, + n.contacts_notified, o.objecttype_id, o.name1, COALESCE(o.name2, '') name2 +FROM icinga_notifications n USE INDEX (PRIMARY) +INNER JOIN icinga_objects o ON o.object_id=n.object_id +WHERE n.notification_id BETWEEN :fromid AND :toid +AND n.notification_id <= :cache_limit AND n.notification_id > :checkpoint -- where we were interrupted +ORDER BY n.notification_id -- this way we know what has already been migrated from just the last row's ID +LIMIT :bulk diff --git a/cmd/icingadb-migrate/embed/previous_hard_state_cache_schema.sql b/cmd/icingadb-migrate/embed/previous_hard_state_cache_schema.sql new file mode 100644 index 0000000..315f22d --- /dev/null +++ b/cmd/icingadb-migrate/embed/previous_hard_state_cache_schema.sql @@ -0,0 +1,22 @@ +PRAGMA main.auto_vacuum = 1; + +-- Icinga DB's state_history#previous_hard_state per IDO's icinga_statehistory#statehistory_id. +CREATE TABLE IF NOT EXISTS previous_hard_state ( + history_id INT PRIMARY KEY, + previous_hard_state INT NOT NULL +); + +-- Helper table, the current last_hard_state per icinga_statehistory#object_id. +CREATE TABLE IF NOT EXISTS next_hard_state ( + object_id INT PRIMARY KEY, + next_hard_state INT NOT NULL +); + +-- Helper table for stashing icinga_statehistory#statehistory_id until last_hard_state changes. +CREATE TABLE IF NOT EXISTS next_ids ( + object_id INT NOT NULL, + history_id INT NOT NULL +); + +CREATE INDEX IF NOT EXISTS next_ids_object_id ON next_ids (object_id); +CREATE INDEX IF NOT EXISTS next_ids_history_id ON next_ids (history_id); diff --git a/cmd/icingadb-migrate/embed/state_query.sql b/cmd/icingadb-migrate/embed/state_query.sql new file mode 100644 index 0000000..3e95d48 --- /dev/null +++ b/cmd/icingadb-migrate/embed/state_query.sql @@ -0,0 +1,9 @@ +SELECT sh.statehistory_id, UNIX_TIMESTAMP(sh.state_time) state_time, sh.state_time_usec, sh.state, + sh.state_type, sh.current_check_attempt, sh.max_check_attempts, sh.last_state, sh.last_hard_state, + sh.output, sh.long_output, sh.check_source, o.objecttype_id, o.name1, COALESCE(o.name2, '') name2 +FROM icinga_statehistory sh USE INDEX (PRIMARY) +INNER JOIN icinga_objects o ON o.object_id=sh.object_id +WHERE sh.statehistory_id BETWEEN :fromid AND :toid +AND sh.statehistory_id <= :cache_limit AND sh.statehistory_id > :checkpoint -- where we were interrupted +ORDER BY sh.statehistory_id -- this way we know what has already been migrated from just the last row's ID +LIMIT :bulk |