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/metadata_init.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: 2.9 KB
Line 
1/** metadata_init -- create ontology tables, concept schemes, audit sources.
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 * We create these here because
8 * 1. We don't want to reach back from the de-id DB to the id DB to
9 *    build metadata
10 * 2. We can't create tables in the de-id DB in a link from the id DB.
11 *
12 * see also: naacr_load.sql, concepts_merge.sql
13 */
14
15/* We'll eventually merge everything here. */
16create table BLUEHERONMETADATA.heron_terms as
17select *
18from BLUEHERONMETADATA.custom_meta where 1=0;
19
20CREATE INDEX BLUEHERONMETADATA.META_FULLNAME_HERON_IDX
21ON BLUEHERONMETADATA.heron_terms (C_FULLNAME) tablespace bheron_indexes;
22
23CREATE INDEX BLUEHERONMETADATA.META_APPLIED_PATH_HERON_IDX
24ON BLUEHERONMETADATA.heron_terms (M_APPLIED_PATH) tablespace bheron_indexes;
25
26/** Epic, SMS */
27create table BLUEHERONMETADATA.epic_demo_terms as
28select *
29from BLUEHERONMETADATA.custom_meta where 1=0;
30
31create table BLUEHERONMETADATA.epic_terms as
32select *
33from BLUEHERONMETADATA.custom_meta where 1=0;
34
35
36
37/** Tumor Registry */
38create table BLUEHERONMETADATA.naaccr_ontology as
39select *
40from BLUEHERONMETADATA.custom_meta where 1=0;
41
42
43/** UHC */
44create table BLUEHERONMETADATA.uhcterms as
45select *
46from BLUEHERONMETADATA.custom_meta where 1=0;
47
48
49
50/** REDCap */
51create table BLUEHERONMETADATA.redcap_terms as
52select *
53from BLUEHERONMETADATA.custom_meta where 1=0;
54
55CREATE INDEX BLUEHERONMETADATA.META_FULLNAME_HERON_RC_IDX
56ON BLUEHERONMETADATA.redcap_terms (C_FULLNAME) tablespace bheron_indexes;
57
58CREATE INDEX BLUEHERONMETADATA.META_APPLIED_PATH_HERON_RC_IDX
59ON BLUEHERONMETADATA.redcap_terms (M_APPLIED_PATH) tablespace bheron_indexes;
60
61CREATE TABLE BLUEHERONMETADATA.TABLE_ACCESS_REDCAP
62AS
63  SELECT *
64  FROM BLUEHERONMETADATA.TABLE_ACCESS
65  WHERE 1=0;
66
67CREATE TABLE BLUEHERONMETADATA.REDCAP_TERMS_ENHANCED
68as
69SELECT * FROM BLUEHERONMETADATA.HERON_TERMS
70WHERE 1=0;
71
72alter table BlueHeronMetadata.REDCAP_TERMS_ENHANCED add (term_id integer);
73
74/** RxNorm */
75create table BLUEHERONMETADATA.rxnorm_terms as
76select *
77from BLUEHERONMETADATA.custom_meta where 1=0;
78
79
80/** UMLS ICD9/ICD10 */
81create table blueheronmetadata.umls_icd9_10
82as select *
83from blueheronmetadata.custom_meta where 1=0;
84
85
86/** EPIC ICD9 #1319, ICD10 #3527 */
87create table blueheronmetadata.epic_icd9_10
88as select *
89from blueheronmetadata.custom_meta cm where 1=0;
90
91create table blueheronmetadata.unique_epic_dx_concepts
92as select c_basecode
93from blueheronmetadata.custom_meta cm where 1=0;
94
95alter table blueheronmetadata.epic_icd9_10
96add (
97icd9_10_code varchar2(255)
98);
99       
100/*******
101 * These columns don't seem to vary
102 */
103create or replace view BLUEHERONMETADATA.normal_concept as
104select
105  'N' c_synonym_cd,
106  'concept_cd' c_facttablecolumn,
107  'concept_dimension' c_tablename,
108  'concept_path' c_columnname,
109  'T' c_columndatatype,
110  'like' c_operator,
111  '@' m_applied_path
112  from dual;
Note: See TracBrowser for help on using the repository browser.