[MDRM] 4.6.1.x 컴포넌트 사용현황 조사 쿼리

Symptoms
  • MDRM의 컴포넌트에서 적용된 컴포넌트 속성값 조회용 쿼리(업그레이드 및 패치 시 현황 파악용)
Environment
  • MDRM 4.6.1.x
Related Versions
  • MDRM 4.6.1.x
Cause
  • 4.6.1 에서 4.6.2 업그레이드시 스펙 제외되거나 기능 변경된 컴포넌트를 조회할 수 있는 쿼리입니다.
  • 연결선(수동실행) / 연결선(승인실행) / 컴포넌트 그룹 / 실패시 다음단계 진행 / 선행 확인 스크립트 / 완료 확인 스크립트 / 원격 워크플로우 사용현황
Solution

쿼리실행방법

1. 컨테이너 접속

docker exec -it postgres bash

2. 쿼리 내용 복사 후 파일 생성

/tmp/mdrm/1.sql

3. 쿼리실행

# psql -d mccs -f '쿼리 파일 경로'

psql -d mccs -f /tmp/mdrm/1.sql
psql -d mccs -f /tmp/mdrm/1.sql > /tmp/mdrm/1.out

✅ 쿼리내용

  1. 연결선-수동실행
with recursive grps(grp_id, path) AS(
select g.grp_id
, g.grp_id::text
from groups g
where pgrp_id  is null
and g.del_tm is null
union all
select a.grp_id
, path || '>' || a.grp_name
from groups a
inner join grps
on a.pgrp_id = grps.grp_id
and a.del_tm is null
where a.grp_type = '2'
)
, wf_path as (
select b.path, a.wf_id
from workflows a, grps b
where a.grp_id = b.grp_id
)
select p.path || '>'|| w.title wf_name , a.title cmp_name, w.wf_id ,a.cmp_id
from wf_components a, wf_component_updates b
, workflows w , wf_path p
where a.wf_cmp_upt_id = b.wf_cmp_upt_id
and w.wf_id = p.wf_id
and b.wf_upt_id = w.wf_upt_id
and a.cmp_type = '0'
and b.cmp_param_value->>'con_type' ='1'
and a.del_tm  is null;

2. 연결선-승인실행

with recursive grps(grp_id, path) AS(
select g.grp_id
, g.grp_id::text
from groups g
where pgrp_id  is null
and g.del_tm is null
union all
select a.grp_id
, path || '>' || a.grp_name
from groups a
inner join grps
on a.pgrp_id = grps.grp_id
and a.del_tm is null
where a.grp_type = '2'
)
, wf_path as (
select b.path, a.wf_id
from workflows a, grps b
where a.grp_id = b.grp_id
)
select p.path || '>'|| w.title wf_name , a.title cmp_name, w.wf_id ,a.cmp_id
from wf_components a, wf_component_updates b
, workflows w , wf_path p
where a.wf_cmp_upt_id = b.wf_cmp_upt_id
and w.wf_id = p.wf_id
and b.wf_upt_id = w.wf_upt_id
and a.cmp_type = '0'
and b.cmp_param_value->>'con_type' ='2'
and a.del_tm  is null;

3.컴포넌트 그룹

with recursive grps(grp_id, path) AS(
    select g.grp_id
         , g.grp_id::text
    from groups g
    where pgrp_id  is null
    and g.del_tm is null    
    union all    
    select a.grp_id
         , path || '>' || a.grp_name 
    from groups a
    inner join grps
    on a.pgrp_id = grps.grp_id
    and a.del_tm is null
    where a.grp_type = '2'
)
, wf_path as (
 select b.path, a.wf_id, a.title, a.wf_upt_id
   from workflows a, grps b
  where a.grp_id = b.grp_id 
)
select p.path || '>'|| p.title wf_name , a.title cmp_name, p.wf_id ,a.cmp_id 		
from wf_components a, wf_component_updates b, wf_path p
where a.wf_cmp_upt_id = b.wf_cmp_upt_id
 and b.wf_upt_id = p.wf_upt_id 
and a.cmp_type  = '4'

4.실패시 다음단계진행

with recursive grps(grp_id, path) AS(
    select g.grp_id
         , g.grp_id::text
    from groups g
    where pgrp_id  is null
    and g.del_tm is null    
    union all    
    select a.grp_id
         , path || '>' || a.grp_name 
    from groups a
    inner join grps
    on a.pgrp_id = grps.grp_id
    and a.del_tm is null
    where a.grp_type = '2'
)
, wf_path as (
 select b.path, a.wf_id
   from workflows a, grps b
  where a.grp_id = b.grp_id 
)
select p.path || '>'|| w.title wf_name , a.title cmp_name, w.wf_id ,a.cmp_id 
  from wf_components a, wf_component_updates b
       , workflows w , wf_path p  
 where a.wf_cmp_upt_id = b.wf_cmp_upt_id
   and w.wf_id = p.wf_id
   and b.wf_upt_id = w.wf_upt_id   
   and b.cmp_param_value->>'fail_type' ='1'
   and a.del_tm  is null       

5.선행 확인 스크립트

with recursive grps(grp_id, path) AS(
    select g.grp_id
         , g.grp_id::text
    from groups g
    where pgrp_id  is null
    and g.del_tm is null    
    union all    
    select a.grp_id
         , path || '>' || a.grp_name 
    from groups a
    inner join grps
    on a.pgrp_id = grps.grp_id
    and a.del_tm is null
    where a.grp_type = '2'
)
, wf_path as (
 select b.path, a.wf_id, a.title, a.wf_upt_id
   from workflows a, grps b
  where a.grp_id = b.grp_id 
)
select p.path || '>'|| p.title wf_name , a.title cmp_name, p.wf_id ,a.cmp_id 
		, b.cmp_param_value ->>'opt_pre_script'
from wf_components a, wf_component_updates b, wf_path p
where a.wf_cmp_upt_id = b.wf_cmp_upt_id
 and b.wf_upt_id = p.wf_upt_id 
and a.cmp_type  = '2'
and b.cmp_param_value->>'opt_pre_script' is not null
and b.cmp_param_value->>'opt_pre_script' <>'{}';

6.완료확인 스크립트

with recursive grps(grp_id, path) AS(
    select g.grp_id
         , g.grp_id::text
    from groups g
    where pgrp_id  is null
    and g.del_tm is null    
    union all    
    select a.grp_id
         , path || '>' || a.grp_name 
    from groups a
    inner join grps
    on a.pgrp_id = grps.grp_id
    and a.del_tm is null
    where a.grp_type = '2'
)
, wf_path as (
 select b.path, a.wf_id, a.title, a.wf_upt_id
   from workflows a, grps b
  where a.grp_id = b.grp_id 
)
select p.path || '>'|| p.title wf_name , a.title cmp_name, p.wf_id ,a.cmp_id 
		, b.cmp_param_value ->>'opt_fin_script'
from wf_components a, wf_component_updates b, wf_path p
where a.wf_cmp_upt_id = b.wf_cmp_upt_id
 and b.wf_upt_id = p.wf_upt_id 
and a.cmp_type  = '2'
and b.cmp_param_value->>'opt_fin_script' is not null
and b.cmp_param_value->>'opt_fin_script' <>'{}';

7. 원격워크플로우 제어

with recursive grps(grp_id, path) AS(
    select g.grp_id
         , g.grp_id::text
    from groups g
    where pgrp_id  is null
    and g.del_tm is null    
    union all    
    select a.grp_id
         , path || '>' || a.grp_name 
    from groups a
    inner join grps
    on a.pgrp_id = grps.grp_id
    and a.del_tm is null
    where a.grp_type = '2'
)
, wf_path as (
 select b.path, a.wf_id, a.title, a.wf_upt_id
   from workflows a, grps b
  where a.grp_id = b.grp_id 
)
select p.path || '>'|| p.title wf_name , a.title cmp_name, p.wf_id ,a.cmp_id 		
from wf_components a, wf_component_updates b, wf_path p
where a.wf_cmp_upt_id = b.wf_cmp_upt_id
 and b.wf_upt_id = p.wf_upt_id 
and b.cmp_tmp_id='TS181';

25 라인 cmp_tmp_id의 값을 아래를 참고하여 변경하십시오.

  • 181: 원격워크플로우 실행(동기)
  • 182: 원격워크플로우 실행(비동기)
  • 183: 원격워크플로우 종료
  • 184: 원격워크플로우 실행 취소
  • 185: 원격워크플로우 일시 중지

Workaround
  • N/A