mirror of
https://github.com/Icinga/icingadb.git
synced 2026-05-28 04:35:54 -04:00
f94a65f459
By introducing an explicit "AS" to set output names in the SELECT column list, there are no issues with reserved names. Unfortunately, this happened on PostgreSQL in the older version 13 with the reserved name "name". Adding "AS" mitigates this issue. Furthermore, I have put each column name in its own line for the SELECT queries to ease the readability of the query itself and of future diffs. Fixes #884.
28 lines
1.2 KiB
SQL
28 lines
1.2 KiB
SQL
SELECT
|
|
dh.downtimehistory_id,
|
|
UNIX_TIMESTAMP(dh.entry_time) AS entry_time,
|
|
dh.author_name,
|
|
dh.comment_data,
|
|
dh.is_fixed,
|
|
dh.duration,
|
|
UNIX_TIMESTAMP(dh.scheduled_start_time) AS scheduled_start_time,
|
|
COALESCE(UNIX_TIMESTAMP(dh.scheduled_end_time), 0) AS scheduled_end_time,
|
|
dh.was_started,
|
|
COALESCE(UNIX_TIMESTAMP(dh.actual_start_time), 0) AS actual_start_time,
|
|
dh.actual_start_time_usec,
|
|
COALESCE(UNIX_TIMESTAMP(dh.actual_end_time), 0) AS actual_end_time,
|
|
dh.actual_end_time_usec,
|
|
dh.was_cancelled,
|
|
COALESCE(UNIX_TIMESTAMP(dh.trigger_time), 0) AS trigger_time,
|
|
COALESCE(dh.name, CONCAT(o.name1, '!', COALESCE(o.name2, ''), '!', dh.downtimehistory_id, '-', dh.object_id)) AS name,
|
|
o.objecttype_id,
|
|
o.name1,
|
|
COALESCE(o.name2, '') AS name2,
|
|
COALESCE(sd.name, '') AS 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
|