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/create_redcap_project_schemas.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: 3.8 KB
Line 
1/** create_redcap_project_schemas.sql -- create oracle schemas for redcap
2-- projects and set up the project in i2b2hive and i2b2pm.
3
4Copyright (c) 2012 University of Kansas Medical Center
5part of the HERON* open source codebase; see NOTICE file for license details.
6* http://informatics.kumc.edu/work/wiki/HERON
7
8*/
9
10
11
12-- Create REDCap project
13  DELETE FROM I2B2PM.PM_PROJECT_DATA WHERE PROJECT_ID = :project_id;
14  INSERT INTO I2B2PM.PM_PROJECT_DATA
15    (PROJECT_ID, PROJECT_NAME, PROJECT_WIKI, PROJECT_PATH, CHANGE_DATE, CHANGEBY_CHAR, STATUS_CD)
16  VALUES
17    (:project_id, :pname, 'http://informatics.kumc.edu/', '/' || :project_id, sysdate, 'badagarla', 'A');
18
19-- Insert metadata schema for the project
20  DELETE FROM I2B2HIVE.ONT_DB_LOOKUP WHERE C_PROJECT_PATH='REDCap_' || :pid || '/';
21  INSERT INTO I2B2HIVE.ONT_DB_LOOKUP
22    (C_DOMAIN_ID,C_PROJECT_PATH,C_OWNER_ID,C_DB_FULLSCHEMA,C_DB_DATASOURCE,
23    C_DB_SERVERTYPE,C_DB_NICENAME,C_DB_TOOLTIP,C_COMMENT,
24    C_ENTRY_DATE,C_CHANGE_DATE,C_STATUS_CD)
25  VALUES
26    ('i2b2demo',:project_id || '/','@','redcapmetadata' || :pid,
27    'java:/OntologyREDCAPDS' || :pid,'ORACLE','REDCap',
28    null, EMPTY_CLOB(),null,null,null);
29
30
31-- Insert data schema for the project
32  DELETE FROM I2B2HIVE.CRC_DB_LOOKUP
33  WHERE C_PROJECT_PATH=('/REDCap_' || :pid || '/');
34  INSERT INTO I2B2HIVE.CRC_DB_LOOKUP
35   (C_DOMAIN_ID,C_PROJECT_PATH,C_OWNER_ID,
36   C_DB_FULLSCHEMA,C_DB_DATASOURCE,C_DB_SERVERTYPE,
37   C_DB_NICENAME,C_DB_TOOLTIP,
38   C_ENTRY_DATE,C_CHANGE_DATE,C_STATUS_CD)
39  VALUES
40 ('i2b2demo','/REDCap_' || :pid || '/','@',
41 'BlueHerondata','java:/QueryToolBLUEHERONDS','ORACLE',
42 'REDCap_' || :pid,null,
43 null,null,null);
44 
45 -- Insert workplace schema for the project
46 DELETE FROM I2B2HIVE.WORK_DB_LOOKUP
47  WHERE C_PROJECT_PATH='REDCap_' || :pid || '/'; 
48  INSERT INTO I2B2HIVE.WORK_DB_LOOKUP
49   (C_DOMAIN_ID,C_PROJECT_PATH,C_OWNER_ID,C_DB_FULLSCHEMA,C_DB_DATASOURCE,
50   C_DB_SERVERTYPE,C_DB_NICENAME,C_DB_TOOLTIP,
51   C_ENTRY_DATE,C_CHANGE_DATE,C_STATUS_CD)
52  VALUES
53 ('i2b2demo','REDCap_' || :pid || '/','@','BlueHeronworkdata'
54 ,'java:/WorkplaceBLUEHERONDS','ORACLE','REDCap_workplace_' || :pid
55 ,null,null,null,null);
56 -- TODO: Factor out i2b2demo?
57
58-- Create permissions for OBFSC_SERVICE_ACCOUNT to the project
59 DELETE FROM I2B2PM.PM_PROJECT_USER_ROLES
60  WHERE PROJECT_ID = :project_id;
61  INSERT INTO I2B2PM.PM_PROJECT_USER_ROLES
62   (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
63  VALUES
64  ('REDCap_' || :pid, 'OBFSC_SERVICE_ACCOUNT', 'USER', 'A');
65  INSERT INTO I2B2PM.PM_PROJECT_USER_ROLES
66   (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
67  VALUES
68  (:project_id, 'OBFSC_SERVICE_ACCOUNT', 'DATA_OBFSC', 'A');
69
70-- create table_access
71CREATE SYNONYM "&&rc_schema".TABLE_ACCESS
72  FOR BLUEHERONMETADATA.TABLE_ACCESS_REDCAP;
73
74-- create heron_terms table
75CREATE SYNONYM "&&rc_schema".HERON_TERMS
76  FOR BLUEHERONMETADATA.HERON_TERMS;
77
78CREATE TABLE "&&rc_schema".REDCAP_TERMS  AS
79 SELECT * FROM BLUEHERONMETADATA.HERON_TERMS
80 WHERE 1=2;
81 
82GRANT SELECT, INSERT, UPDATE, DELETE ON
83"&&rc_schema".REDCAP_TERMS TO i2b2rcmgr;
84
85-- Metadata user permissions to access heron_terms etc. for creating views
86GRANT SELECT ON BLUEHERONMETADATA.HERON_TERMS to &&rc_schema;
87GRANT SELECT ON BLUEHERONMETADATA.TABLE_ACCESS to &&rc_schema; 
88GRANT SELECT ON BLUEHERONMETADATA.TABLE_ACCESS_REDCAP to &&rc_schema;
89GRANT SELECT ON BLUEHERONMETADATA.SCHEMES to &&rc_schema;
90
91-- Create schemes
92CREATE OR REPLACE VIEW "&&rc_schema".SCHEMES AS
93SELECT * FROM BLUEHERONMETADATA.SCHEMES;
94
95-- Create indexes
96CREATE INDEX "&&rc_schema".M_FULLNAME_RC_IDX
97 ON "&&rc_schema".REDCAP_TERMS (C_FULLNAME)
98 TABLESPACE bheron_indexes;
99
100CREATE INDEX "&&rc_schema".M_APPLIED_PATH_RC_IDX
101  ON "&&rc_schema".REDCAP_TERMS (M_APPLIED_PATH)
102  TABLESPACE bheron_indexes;
Note: See TracBrowser for help on using the repository browser.