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/redcap_concepts_load.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: 10.3 KB
Line 
1/* redcap_concepts_load -- Loading concepts for the REDCap projects
2
3Copyright (c) 2012 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6
7-- This is based on heron-load/uhc_concepts_load.sql --
8TODO: Project Name should be picked up from the staging data to name the folder
9***
10TODO: Accomodate verify against fields like sliders and yesno and truefalse
11NO such fields in this project
12***
13TODO: The concept_cd is going over 50 chars. How do we limit it?
14*/
15
16--  Check if we are in Identified Server
17select upload_id from NIGHTHERONDATA.observation_fact where 1=0;
18
19-- Test that we're in the KUMC sid.
20-- TODO: consider moving to the identified repository.
21select project_id from redcap.redcap_data where 1=0;
22 
23
24/*Required i2b2 metadata fields
25cf section 2.2 Ontology Table
26of i2b2 Design Document
27Ontology Management (ONT) Cell
281.6
29
30C_HLEVEL --
31C_FULLNAME --concept_path
32UPDATE_DATE
33M_APPLIED_PATH
34C_DIMCODE
35C_OPERATOR
36C_COLUMNDATATYPE
37C_COLUMNNAME
38C_TABLENAME
39C_FACTTABLECOLUMN
40C_VISUALATTRIBUTES
41C_SYNONYM_CD
42C_NAME
43*/
44
45whenever sqlerror continue;
46truncate table redcap_concepts;
47drop table redcap_concepts;
48whenever sqlerror exit;
49
50create global temporary table redcap_concepts
51on commit preserve rows as
52select
53  con.c_hlevel
54, con.concept_path
55, con.name_char
56, con.concept_cd
57, con.concept_path c_dimcode
58, con.c_visualattributes
59, norm.*
60, sysdate update_date
61, sysdate import_date
62, (select source_cd from blueherondata.source_master@deid where source_cd like 'REDCap@%') source_cd
63, c_metadataxml
64from (
65
66
67-- List all the text fields with validation attached as leaves
68select distinct
69      '\i2b2\redcap\'||project_id||'\'||form_name||'\'||field_name||'\'  as concept_path
70    , 4 as c_hlevel
71    , 'KUMCRC'||project_id||'|'||
72    case when length('KUMCRC'||project_id||'|'||field_name||':') > 50
73      then substr(field_name, 1, 3) || '*' || to_char(ora_hash(field_name))
74      else field_name end || ':' concept_cd   
75    , 'KUMCRC'||project_id||'|'||field_name||':' as concept_cd2
76    , dbms_lob.substr(NVL(element_label, field_name),2000,1) as name_char
77    -- element label is of mysql type medium text and hence the precaution
78    , 'LA' as c_visualattributes
79    -- from epic_concepts_load.sql
80    , '<?xml version="1.0"?>
81 <ValueMetadata>
82 <Version>3.02</Version>
83 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
84 <TestID>' ||'KUMCRC'||project_id||'|'||field_name||':'|| '</TestID>
85 <TestName>'|| dbms_lob.substr(NVL(element_label, field_name),2000,1) ||'</TestName>
86 <DataType>PosFloat</DataType>
87 <CodeType>GRP</CodeType>
88 <Loinc>' ||'KUMCRC'||project_id||field_name|| '</Loinc>
89 <Flagstouse>HL</Flagstouse>
90 <Oktousevalues>The units in this set of tests are not equivalent,
91 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
92 <MaxStringLength></MaxStringLength>
93 <LowofLowValue></LowofLowValue>
94 <HighofLowValue></HighofLowValue>
95 <LowofHighValue></LowofHighValue>
96 <HighofHighValue></HighofHighValue>
97 <LowofToxicValue></LowofToxicValue>
98 <HighofToxicValue></HighofToxicValue>
99 <EnumValues></EnumValues>
100 <CommentsDeterminingExclusion>
101   <Com></Com>
102 </CommentsDeterminingExclusion>
103 <UnitValues>
104   <NormalUnits>Default</NormalUnits>
105   <ConvertingUnits>
106     <Units></Units>
107     <MultiplyingFactor></MultiplyingFactor>
108   </ConvertingUnits>
109 </UnitValues>
110 <Analysis>
111   <Enums />
112   <Counts />
113   <New />
114 </Analysis>
115</ValueMetadata>' as c_metadataxml
116    from redcap.redcap_metadata rmd
117    where element_type='text' and element_validation_type  in ('int', 'float')
118    and field_phi IS NULL
119    -- eliminated the text fields that have no validation
120
121UNION ALL
122
123select distinct
124      '\i2b2\redcap\'||project_id||'\'||form_name||'\'||field_name||'\'  as concept_path
125    , 4 as c_hlevel
126    , 'KUMCRC'||project_id||'|'||
127      case when length('KUMCRC'||project_id||'|'||field_name||':') > 50
128      then substr(field_name, 1, 3) || '*' || to_char(ora_hash(field_name))
129      else field_name end || ':' concept_cd   
130    , 'KUMCRC'||project_id||'|'||field_name||':' as concept_cd2
131    , dbms_lob.substr(NVL(element_label, field_name),2000,1) as name_char
132    -- element label is of mysql type medium text and hence the precaution
133    , 'LA' as c_visualattributes
134    , NULL as c_metadataxml
135    from redcap.redcap_metadata rmd
136    where element_type='text' and element_validation_type in ('date_dmy','date_mdy','date_ymd')
137    and field_phi IS NULL
138    -- eliminated the text fields that have no validation
139
140UNION ALL
141
142-- TODO: SLIDERS AND OTHER FIELDS!!!!! (TEST for it)
143-- List all the radio, dropdown and radio fields as leaves
144-- ??? Should the concept path not have : and instead have a \ ???
145select distinct
146      '\i2b2\redcap\'||project_id||'\'||form_name||'\'||field_name||'\'||option_id||'\'  as concept_path
147    , 5 as c_hlevel
148    , 'KUMCRC'||project_id||'|'||
149    case when length('KUMCRC'||project_id||'|'||field_name||':'||option_id) > 50 and length(field_name)>18
150      then substr(field_name, 1, 3) || '*' || to_char(ora_hash(field_name))
151      else field_name end || ':' ||
152    case when length('KUMCRC'||project_id||'|'||field_name||':'||option_id) > 50 and length(option_id) > 18
153      then substr(option_id, 1, 3) || '*' || to_char(ora_hash(option_id))
154    else option_id end concept_cd
155    , 'KUMCRC'||project_id||'|'||field_name||':'||option_id as concept_cd2
156    , decode(option_desc, NULL, '',option_desc ) as name_char
157    , 'LA' as c_visualattributes
158    , NULL as c_metadataxml
159    from redcap.redcap_metadata rmd
160    where element_type IN ('radio','select','checkbox')
161    and field_phi IS NULL
162
163UNION ALL
164
165-- SQL fields similar to multiple choice fields
166select distinct
167      '\i2b2\redcap\'||project_id||'\'||form_name||'\'||field_name||'\'||option_id||'\'  as concept_path
168    , 5 as c_hlevel
169    , 'KUMCRC'||project_id||'|'||
170    case when length('KUMCRC'||project_id||'|'||field_name||':'||option_id) > 50 and length(field_name)>18
171      then substr(field_name, 1, 3) || '*' || to_char(ora_hash(field_name))
172      else field_name end || ':' ||
173    case when length('KUMCRC'||project_id||'|'||field_name||':'||option_id) > 50 and length(option_id) > 18
174      then substr(option_id, 1, 3) || '*' || to_char(ora_hash(option_id))
175    else option_id end concept_cd
176    , 'KUMCRC'||project_id||'|'||field_name||':'||option_id as concept_cd2
177    , decode(option_desc, NULL, '',option_desc ) as name_char
178    , 'LA' as c_visualattributes
179    , NULL as c_metadataxml
180    from
181    (
182    select rms.*, rm.form_name, rm.element_type, rm.field_phi
183    from redcap.redcap_metadata rm
184    join redcap.redcap_metadata_sql rms
185    on rms.project_id = rm.project_id
186    and rms.field_name = rm.field_name
187    where rm.element_type IN ('sql')
188    and rm.field_phi IS NULL) rmd
189
190UNION ALL
191
192-- yesno fields have yes, no as leaves
193select distinct
194      '\i2b2\redcap\'||project_id||'\'||form_name||'\'||field_name||'\1\'  as concept_path
195    , 5 as c_hlevel
196    , 'KUMCRC'||project_id||'|'||
197      case when length('KUMCRC'||project_id||'|'||field_name||':') > 50
198      then substr(field_name, 1, 3) || '*' || to_char(ora_hash(field_name))
199      else field_name end || ':1' concept_cd   
200    , 'KUMCRC'||project_id||'|'||field_name||':1' as concept_cd2
201    , 'yes' as name_char
202    , 'LA' as c_visualattributes   
203    , NULL as c_metadataxml   
204    from redcap.redcap_metadata rmd
205    where element_type IN ('yesno')
206    and field_phi IS NULL
207
208UNION ALL
209
210select distinct
211      '\i2b2\redcap\'||project_id||'\'||form_name||'\'||field_name||'\0\'  as concept_path
212    , 5 as c_hlevel
213    , 'KUMCRC'||project_id||'|'||
214      case when length('KUMCRC'||project_id||'|'||field_name||':') > 50
215      then substr(field_name, 1, 3) || '*' || to_char(ora_hash(field_name))
216      else field_name end || ':0' concept_cd       
217    , 'KUMCRC'||project_id||'|'||field_name||':0' as concept_cd2
218    , 'no' as name_char
219    , 'LA' as c_visualattributes
220    , NULL as c_metadataxml   
221    from redcap.redcap_metadata rmd
222    where element_type IN ('yesno')
223    and field_phi IS NULL
224
225UNION ALL
226
227-- List all the CRFs or forms as folders
228select distinct
229      '\i2b2\redcap\'||project_id||'\'||form_name||'\'  as concept_path
230    , 3 as c_hlevel
231    , NULL as concept_cd
232    , NULL as concept_cd2
233    , form_menu_description as name_char
234    , 'FA' as c_visualattributes
235    , NULL as c_metadataxml   
236    from redcap.redcap_metadata rmd
237    where form_menu_description IS NOT NULL
238
239UNION ALL
240
241-- List all the fields that have options as folders (this also includes the form complete fields)
242-- yesno fields also need to be listed as folders
243select distinct
244      '\i2b2\redcap\'||project_id||'\'||form_name||'\'||field_name||'\'  as concept_path
245    , 4 as c_hlevel
246    , NULL as concept_cd
247    , NULL as concept_cd2
248    , dbms_lob.substr(NVL(element_label, field_name)) as name_char
249    , 'FA' as c_visualattributes
250    , NULL as c_metadataxml   
251    from redcap.redcap_metadata rmd
252    where element_type IN ('select', 'checkbox', 'radio', 'yesno', 'sql')
253    and field_phi IS NULL
254
255UNION ALL
256
257-- List the projects as folder
258-- TODO: Stage the project details also so we do not have to enter it manually
259select distinct
260      '\i2b2\redcap\'||project_id||'\'  as concept_path
261    , 2 as c_hlevel     
262    , NULL as concept_cd
263    , NULL as concept_cd2
264    , app_title as name_char
265    , 'FA' as c_visualattributes 
266    , NULL as c_metadataxml   
267    from redcap.redcap_projects rmd
268 
269UNION ALL
270
271--List REDCap as a folder under i2b2
272select
273      '\i2b2\redcap\'  as concept_path
274    , 1 as c_hlevel
275    , NULL as concept_cd
276    , NULL as concept_cd2
277    , 'REDCap' as name_char
278    , 'FA' as c_visualattributes
279    , NULL as c_metadataxml   
280    from dual
281
282) con
283, BlueHeronMetadata.normal_concept@deid norm
284-- This is to exclude MRN field from being included in the data
285where con.concept_cd not in
286(select 'KUMCRC'||project_id||'|'||mrn_field||':' from redcap.redcap_mrn)
287or con.concept_cd is null
288order by concept_path, c_visualattributes;
289
290delete from BLUEHERONMETADATA.redcap_terms@deid;
291insert into BLUEHERONMETADATA.redcap_terms@deid (
292  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
293  c_visualattributes,
294  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
295  c_operator,  m_applied_path,
296  update_date, import_date,  sourcesystem_cd, c_metadataxml
297)
298select * from redcap_concepts
299;
300-- 10,800 rows inserted.
301-- select * from BLUEHERONMETADATA.redcap_terms@DEID;
Note: See TracBrowser for help on using the repository browser.