SetMonitoringPointBoundsFromTriggersMigration
| Database |
Schema |
Description |
| Transaction |
ahm |
Set bound columns on monitoring points from monitoring point triggers Type Migration |
SQL Statements
create temporary table monitoring_points_temp
(
monitoring_point_id bigint not null,
upper_critical_bound numeric(19, 4),
upper_warning_bound numeric(19, 4),
lower_warning_bound numeric(19, 4),
lower_critical_bound numeric(19, 4),
upper_critical_alarm_type_id bigint,
upper_warning_alarm_type_id bigint,
lower_warning_alarm_type_id bigint,
lower_critical_alarm_type_id bigint,
monitoring_point_expression_ids bigint[]
);
insert into monitoring_points_temp
select
mp_param.mp_id,
uc.bound uc_bound, uw.bound uw_bound, lw.bound lw_bound, lc.bound lc_bound,
uc.alarm_type_id,
uw.alarm_type_id,
lw.alarm_type_id,
lc.alarm_type_id,
array(select uc.monitoring_point_trigger_expression_id) ||
array(select uw.monitoring_point_trigger_expression_id) ||
array(select lw.monitoring_point_trigger_expression_id) ||
array(select lc.monitoring_point_trigger_expression_id)
from
ahm.monitoring_point_trigger triggers
-- get the monitoring point parameter for the trigger. They must all be the same for a given trigger
join lateral (
select p.monitoring_point_id mp_id, count(p.monitoring_point_id)
from ahm.monitoring_point_trigger_expression_parameter p
join ahm.monitoring_point_trigger_expression exp on exp.monitoring_point_trigger_expression_id = p.monitoring_point_trigger_expression_id
where
p.monitoring_point_id is not null and
p.parameter_type = 'MonitoringPoint' and
exp.monitoring_point_trigger_id = triggers.monitoring_point_trigger_id
group by p.monitoring_point_id
having count (distinct p.monitoring_point_id) = 1
limit 1
) mp_param on true
-- upper critical data
left join lateral (
select
exp.monitoring_point_trigger_expression_id, (p.parameter_value).value::numeric(19, 4) bound, alarm_type.alarm_type_id,
alarm_type.has_more_than_one_alarm_type, alarm_expression.has_alarm_expression
from ahm.monitoring_point_trigger_expression exp
join ahm.monitoring_point_trigger_expression_parameter p on
p.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id and p.parameter_name = 'upperCriticalBound'
left join lateral (
select at.alarm_type_id, case when count(at.alarm_type_id) > 1 then true else false end has_more_than_one_alarm_type
from ahm.monitoring_point_trigger_expression_alarm_type at
where at.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id
group by at.alarm_type_id
) alarm_type on true
left join lateral (
select exists (select alarm_expression_id from ahm.alarm_expression a_exp where a_exp.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id) has_alarm_expression
) alarm_expression on true
where
exp.monitoring_point_trigger_id = triggers.monitoring_point_trigger_id and
exp.description = 'Upper Critical Bound' and
exp.expression = 'monitoring_point_reading(@monitoringPointId) > @upperCriticalBound'
) uc on true
-- upper warning data
left join lateral (
select
exp.monitoring_point_trigger_expression_id, (p.parameter_value).value::numeric(19, 4) bound, alarm_type.alarm_type_id,
alarm_type.has_more_than_one_alarm_type, alarm_expression.has_alarm_expression
from ahm.monitoring_point_trigger_expression exp
join ahm.monitoring_point_trigger_expression_parameter p on
p.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id and p.parameter_name = 'upperWarningBound'
left join lateral (
select at.alarm_type_id, case when count(at.alarm_type_id) > 1 then true else false end has_more_than_one_alarm_type
from ahm.monitoring_point_trigger_expression_alarm_type at
where at.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id
group by at.alarm_type_id
) alarm_type on true
left join lateral (
select exists (select alarm_expression_id from ahm.alarm_expression a_exp where a_exp.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id) has_alarm_expression
) alarm_expression on true
where
exp.monitoring_point_trigger_id = triggers.monitoring_point_trigger_id and
exp.description = 'Upper Warning Bound' and
exp.expression = '(monitoring_point_reading(@monitoringPointId) > @upperWarningBound) AND (monitoring_point_reading(@monitoringPointId) <= @upperCriticalBound)'
) uw on true
-- lower warning data
left join lateral (
select
exp.monitoring_point_trigger_expression_id, (p.parameter_value).value::numeric(19, 4) bound, alarm_type.alarm_type_id,
alarm_type.has_more_than_one_alarm_type, alarm_expression.has_alarm_expression
from ahm.monitoring_point_trigger_expression exp
join ahm.monitoring_point_trigger_expression_parameter p on
p.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id and p.parameter_name = 'lowerWarningBound'
left join lateral (
select at.alarm_type_id, case when count(at.alarm_type_id) > 1 then true else false end has_more_than_one_alarm_type
from ahm.monitoring_point_trigger_expression_alarm_type at
where at.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id
group by at.alarm_type_id
) alarm_type on true
left join lateral (
select exists (select alarm_expression_id from ahm.alarm_expression a_exp where a_exp.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id) has_alarm_expression
) alarm_expression on true
where
exp.monitoring_point_trigger_id = triggers.monitoring_point_trigger_id and
exp.description = 'Lower Warning Bound' and
exp.expression = '(monitoring_point_reading(@monitoringPointId) < @lowerWarningBound) AND (monitoring_point_reading(@monitoringPointId) >= @lowerCriticalBound)'
) lw on true
-- lower critical data
left join lateral (
select
exp.monitoring_point_trigger_expression_id, (p.parameter_value).value::numeric(19, 4) bound, alarm_type.alarm_type_id,
alarm_type.has_more_than_one_alarm_type, alarm_expression.has_alarm_expression
from ahm.monitoring_point_trigger_expression exp
join ahm.monitoring_point_trigger_expression_parameter p on
p.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id and p.parameter_name = 'lowerCriticalBound'
left join lateral (
select at.alarm_type_id, case when count(at.alarm_type_id) > 1 then true else false end has_more_than_one_alarm_type
from ahm.monitoring_point_trigger_expression_alarm_type at
where at.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id
group by at.alarm_type_id
) alarm_type on true
left join lateral (
select exists (select alarm_expression_id from ahm.alarm_expression a_exp where a_exp.monitoring_point_trigger_expression_id = exp.monitoring_point_trigger_expression_id) has_alarm_expression
) alarm_expression on true
where
exp.monitoring_point_trigger_id = triggers.monitoring_point_trigger_id and
exp.description = 'Lower Critical Bound' and
exp.expression = 'monitoring_point_reading(@monitoringPointId) < @lowerCriticalBound'
) lc on true
join lateral (
select exists (select asset_type_id from ampc.asset_type_monitoring_point_trigger asset_type_trigger where asset_type_trigger.monitoring_point_trigger_id = triggers.monitoring_point_trigger_id) has_reference
) asset_type_reference on true
join lateral (
select exists (select asset_id from ampc.asset_monitoring_point_trigger asset_trigger where asset_trigger.monitoring_point_trigger_id = triggers.monitoring_point_trigger_id) has_reference
) asset_reference on true
where
uc.has_more_than_one_alarm_type is distinct from true and
uw.has_more_than_one_alarm_type is distinct from true and
lw.has_more_than_one_alarm_type is distinct from true and
lc.has_more_than_one_alarm_type is distinct from true and
uc.has_alarm_expression is distinct from true and
uw.has_alarm_expression is distinct from true and
lw.has_alarm_expression is distinct from true and
lc.has_alarm_expression is distinct from true and
asset_type_reference.has_reference = false and
asset_reference.has_reference = false and
(uc.bound is not null or uw.bound is not null or lw.bound is not null or lc.bound is not null);
update ahm.monitoring_point mp_new
set
upper_critical_bound = monitoring_points_temp.upper_critical_bound,
upper_warning_bound = monitoring_points_temp.upper_warning_bound,
lower_warning_bound = monitoring_points_temp.lower_warning_bound,
lower_critical_bound = monitoring_points_temp.lower_critical_bound,
upper_critical_alarm_type_id = monitoring_points_temp.upper_critical_alarm_type_id,
upper_warning_alarm_type_id = monitoring_points_temp.upper_warning_alarm_type_id,
lower_warning_alarm_type_id = monitoring_points_temp.lower_warning_alarm_type_id,
lower_critical_alarm_type_id = monitoring_points_temp.lower_critical_alarm_type_id
from
monitoring_points_temp
where
mp_new.monitoring_point_id = monitoring_points_temp.monitoring_point_id;
create temporary table expressions_temp (monitoring_point_trigger_expression_id bigint);
with expressions as
(
select unnest(monitoring_point_expression_ids) monitoring_point_expression_id from monitoring_points_temp
)
insert into expressions_temp
select monitoring_point_expression_id from expressions where monitoring_point_expression_id is not null;
delete from ahm.monitoring_point_trigger_expression_parameter where monitoring_point_trigger_expression_id in (select monitoring_point_trigger_expression_id from expressions_temp);
delete from ahm.monitoring_point_trigger_expression_alarm_type where monitoring_point_trigger_expression_id in (select monitoring_point_trigger_expression_id from expressions_temp);
delete from ahm.monitoring_point_trigger_expression where monitoring_point_trigger_expression_id in (select monitoring_point_trigger_expression_id from expressions_temp);
delete from ahm.monitoring_point_trigger
where
monitoring_point_trigger_id in (select monitoring_point_trigger_id from monitoring_points_temp) and
monitoring_point_trigger_id not in (select monitoring_point_trigger_id from ahm.monitoring_point_trigger_expression);