Created
March 25, 2015 14:50
-
-
Save forus/f80cecf7068d595465a4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- i2b2 Metadata Schema | |
alter table i2b2metadata.i2b2 add constraint i2b2_c_fullname_uq unique (c_fullname); --could be pk. domain object refers to fullName+name as composite key | |
alter table i2b2metadata.i2b2_secure add constraint i2b2_secure_c_fullname_uq unique (c_fullname); --could be pk. domain object refers to fullName+name as composite key | |
-- Delete secure concepts that are not present in i2b2 table | |
--delete from i2b2metadata.i2b2_secure isc where not exists(select * from i2b2metadata.i2b2 where c_fullname = isc.c_fullname); | |
alter table i2b2metadata.i2b2_secure add constraint i2b2_secure_c_fullname_fk foreign key (c_fullname) references i2b2(c_fullname) on delete cascade; | |
alter table i2b2metadata.i2b2_tags add constraint i2b2_tags_pk primary key (tag_id); | |
alter table i2b2metadata.i2b2_tags add constraint i2b2_tags_path_fk foreign key (path) references i2b2(c_fullname) on delete cascade; | |
-- i2b2 Data Schema | |
alter table i2b2demodata.concept_dimension add constraint concept_dimension_concept_cd_uq unique (concept_cd); | |
--Delete "security" nodes that are not used anymore. Do not to fix i2b2_create_security_for_trial SP respectively. | |
--delete from i2b2demodata.observation_fact where concept_cd = 'SECURITY'; | |
alter table i2b2demodata.observation_fact add constraint observation_fact_concept_cd_fk foreign key (concept_cd) references concept_dimension(concept_cd) on delete cascade; | |
alter table i2b2demodata.observation_fact add constraint observation_fact_patient_num_fk foreign key (patient_num) references patient_dimension(patient_num) on delete cascade; | |
insert into modifier_dimension(modifier_path, modifier_cd) values ('/@', '@'); | |
alter table i2b2demodata.modifier_dimension add constraint modifier_dimension_modifier_cd_uq unique (modifier_cd); | |
alter table i2b2demodata.observation_fact add constraint observation_fact_modifier_cd_fk foreign key (modifier_cd) references modifier_dimension(modifier_cd) on delete cascade; | |
alter table i2b2demodata.patient_trial add constraint patient_trial_pk primary key (patient_num, trial); | |
alter table i2b2demodata.patient_trial add constraint patient_trial_patient_num_fk foreign key (patient_num) references patient_dimension(patient_num) on delete cascade; | |
alter table i2b2demodata.modifier_metadata add constraint modifier_metadata_pk primary key (modifier_cd); | |
alter table i2b2demodata.modifier_metadata add constraint modifier_metadata_modifier_cd_fk foreign key (modifier_cd) references modifier_dimension(modifier_cd) on delete cascade; | |
alter table i2b2demodata.concept_counts add constraint concept_counts_pk primary key (concept_path); | |
alter table i2b2demodata.concept_counts add constraint concept_counts_concept_path_fk foreign key (concept_path) references concept_dimension(concept_path) on delete cascade; | |
--TODO We would need to change ETL scripts to insert records for all parent concepts. | |
--insert into i2b2demodata.concept_dimension(concept_cd, concept_path) values ('public', '\Public Studies\'); | |
--insert into i2b2demodata.concept_counts(concept_path) values ('\Public Studies\'); | |
--insert into i2b2demodata.concept_dimension(concept_cd, concept_path) values ('public', '\Private Studies\'); | |
--insert into i2b2demodata.concept_counts(concept_path) values ('\Private Studies\'); | |
--alter table i2b2demodata.concept_counts add constraint concept_counts_parent_concept_path_fk foreign key (parent_concept_path) references concept_counts(concept_path) on delete cascade; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment