Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/concepts_activate.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 4.8 KB
Line 
1/** concepts_activate -- Initialize the table access (so the top level concepts
2    can be visualized in i2b2) and make terms in the the Ontology Cell
3    (heron_terms) available in the CRC Cell (concept_dimension).
4
5Copyright (c) 2013 University of Kansas Medical Center
6part of the HERON* open source codebase; see NOTICE file for license details.
7* http://informatics.kumc.edu/work/wiki/HERON
8
9for performance work:
10set timing on;
11set echo on;
12*/
13
14-- Check that we're running in the de-id database.
15select c_table_cd from blueheronmetadata.table_access where 1=0;
16
17
18/**
19 * Set up TABLE_ACCESS using HERON_TERMS.
20 */
21create or replace view table_access_all as
22select
23  ut.c_table_cd,
24  case
25    when ut.c_fullname LIKE '\i2b2\redcap\%'
26    then 'REDCAP_TERMS'
27    else 'HERON_TERMS'
28  end c_table_name,
29  'N' c_protected_access,
30  ut.c_hlevel, ut.c_fullname, ut.c_name,
31  'N' c_synonym_cd, ut.c_visualattributes,
32  ut.c_totalnum, null c_basecode, null c_comment, null c_metadataxml,
33  'concept_cd' c_facttablecolumn, 'concept_dimension' c_dimtablename,
34  'concept_path' c_columnname, 'T' c_columndatatype,
35  'like' c_operator, ut.c_dimcode, ut.c_tooltip, null c_entry_date,
36  null c_change_date, null c_status_cd, null valuetype_cd
37from (
38  select
39    'i2b2_' || rtrim(substr(ht.c_fullname, 7), '\') c_table_cd,
40    0 c_hlevel, ht.c_fullname,
41    ht.c_name,
42    ht.c_visualattributes,
43    ht.c_totalnum,
44    ht.c_dimcode, ht.c_tooltip
45  from
46    (select * from BLUEHERONMETADATA.HERON_TERMS
47     union all
48     select * from BLUEHERONMETADATA.REDCAP_TERMS) ht
49  where ht.c_hlevel = 1
50    --Skip all the modifiers at level 1
51    and ht.c_visualattributes not like 'O%'
52    and ht.c_visualattributes not like 'D%'
53    and ht.c_visualattributes not like 'R%'
54    and ht.c_visualattributes not like '_H%'
55
56  union all
57     
58  select
59    'i2b2' c_table_cd,
60    1 c_hlevel, '\i2b2\' c_fullname,
61    'HERON (Backwards Compatibility) ' c_name,
62    'CH ' c_visualattributes,
63    null c_totalnum, 
64    '\i2b2\' c_dimcode, null c_tooltip
65  from dual
66  )ut;
67
68
69
70-- load the concept_dimension from the metadata schema
71truncate table BlueHeronData.concept_dimension;
72insert into BlueHeronData.concept_dimension(
73  concept_cd, concept_path, name_char,
74  update_date, download_date, import_date, sourcesystem_cd)
75select distinct ib.c_basecode, ib.c_fullname, max(ib.c_name)
76     , update_date, download_date, sysdate, sourcesystem_cd
77from (select * from
78BlueHeronMetadata.heron_terms
79union all
80select * from
81BlueHeronMetadata.redcap_terms) ib
82where ib.c_basecode is not null
83group by ib.c_basecode, ib.c_fullname
84     , update_date, download_date, sysdate, sourcesystem_cd
85;
86-- 1,068,020 rows inserted.
87
88/* Truncate results in "DDL operations are not allowed on a remote database" so
89using delete.
90
91Note that this perpetuates the use of the link from deid to id which we plan to
92address in #482.
93*/
94delete from NightHeronData.concept_dimension@id;
95insert into NightHeronData.concept_dimension@id
96  select * from BlueHeronData.concept_dimension;
97commit;
98
99delete from NightHeronData.modifier_dimension@id;
100insert into NightHeronData.modifier_dimension@id
101  select * from BlueHeronData.modifier_dimension;
102commit;
103
104 -- #1880 : to separate redcap terms
105truncate table BLUEHERONMETADATA.TABLE_ACCESS_REDCAP;
106insert into BLUEHERONMETADATA.TABLE_ACCESS_REDCAP (
107    C_TABLE_CD,
108    C_TABLE_NAME, C_PROTECTED_ACCESS,
109    C_HLEVEL, C_FULLNAME, C_NAME,
110    C_SYNONYM_CD, C_VISUALATTRIBUTES,
111    C_TOTALNUM, C_BASECODE, C_COMMENT, C_METADATAXML,
112    C_FACTTABLECOLUMN, C_DIMTABLENAME,
113    C_COLUMNNAME, C_COLUMNDATATYPE,
114    C_OPERATOR, C_DIMCODE, C_TOOLTIP, C_ENTRY_DATE,
115    C_CHANGE_DATE, C_STATUS_CD, VALUETYPE_CD
116  )
117  SELECT *
118  FROM table_access_all;
119 
120delete from BLUEHERONMETADATA.TABLE_ACCESS;
121INSERT INTO BLUEHERONMETADATA.TABLE_ACCESS (
122    C_TABLE_CD,
123    C_TABLE_NAME, C_PROTECTED_ACCESS,
124    C_HLEVEL, C_FULLNAME, C_NAME,
125    C_SYNONYM_CD, C_VISUALATTRIBUTES,
126    C_TOTALNUM, C_BASECODE, C_COMMENT, C_METADATAXML,
127    C_FACTTABLECOLUMN, C_DIMTABLENAME,
128    C_COLUMNNAME, C_COLUMNDATATYPE,
129    C_OPERATOR, C_DIMCODE, C_TOOLTIP, C_ENTRY_DATE,
130    C_CHANGE_DATE, C_STATUS_CD, VALUETYPE_CD
131  )
132  SELECT *
133  FROM table_access_all
134  where c_table_name = 'HERON_TERMS'
135  ;
136
137/*
138show dups...
139
140
141select count(*), c_fullname from (
142select distinct ib.c_basecode, ib.c_fullname, max(ib.c_name)
143     , update_date, download_date, sourcesystem_cd
144from BlueHeronMetadata.heron_terms ib
145where ib.c_basecode is not null
146group by ib.c_basecode, ib.c_fullname, update_date, download_date, sourcesystem_cd
147) group by c_fullname
148having count(*) > 1
149;
150*/
151
152/* Keep handy: reclaim space from deleted rows
153
154alter table BlueHeronMetadata.i2b2 enable row movement;
155alter table BlueHeronMetadata.i2b2 shrink space;
156
157alter index BlueHeronMetadata.metadata_by_path rebuild;
158alter index BlueHeronData.concept_dimension_pk rebuild;
159*/
Note: See TracBrowser for help on using the repository browser.