i2b2_and_deapp.sql

-- 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;

-- Subject Sample Mapping (aka Assay) table
alter table deapp.de_subject_sample_mapping add constraint de_subject_sample_mapping_pk primary key (assay_id);

alter table deapp.de_subject_sample_mapping add constraint de_subject_sample_mapping_patient_id_fk foreign key (patient_id) references i2b2demodata.patient_dimension(patient_num) on delete cascade;
alter table deapp.de_subject_sample_mapping add constraint de_subject_sample_mapping_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform);
alter table deapp.de_subject_sample_mapping alter concept_code type character varying(50);
alter table deapp.de_subject_sample_mapping add constraint de_subject_sample_mapping_concept_code_fk foreign key (concept_code) references i2b2demodata.concept_dimension(concept_cd) on delete cascade;

-- HD data tables
-- Add PKs for data tables that miss ones
drop index de_microarray_data_idx1;
drop index de_microarray_data_idx2;
drop index de_microarray_data_idx4;
drop index de_microarray_data_idx5;
alter table deapp.de_subject_microarray_data add constraint de_subject_microarray_data_pk primary key (assay_id, probeset_id);
alter table deapp.de_subject_metabolomics_data add constraint de_subject_metabolomics_data_pk primary key (assay_id, metabolite_annotation_id);
alter table deapp.de_subject_mirna_data add constraint de_subject_mirna_data_pk primary key (assay_id, probeset_id);
alter table deapp.de_subject_protein_data add constraint de_subject_protein_data_pk primary key (assay_id, protein_annotation_id);
alter table deapp.de_subject_rna_data add constraint de_subject_rna_data_pk primary key (assay_id, probeset_id);

-- FKs to SSM
alter table deapp.de_subject_metabolomics_data add constraint de_subject_metabolomics_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;
alter table deapp.de_subject_acgh_data add constraint de_subject_acgh_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;
alter table deapp.de_subject_microarray_data add constraint de_subject_microarray_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;
alter table deapp.de_subject_mirna_data alter assay_id type bigint;
alter table deapp.de_subject_mirna_data add constraint de_subject_mirna_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;
alter table deapp.de_subject_rbm_data add constraint de_subject_rbm_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;
alter table deapp.de_subject_protein_data alter assay_id type bigint;
alter table deapp.de_subject_protein_data add constraint de_subject_protein_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;
alter table deapp.de_subject_rna_data add constraint de_subject_rna_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;
alter table deapp.de_variant_subject_summary add constraint de_variant_subject_summary_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;
alter table deapp.de_subject_rnaseq_data add constraint de_subject_rnaseq_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade;

-- Fix PKs for Annotation Table
drop trigger trg_de_mrna_annotation_id on deapp.de_mrna_annotation;
drop function deapp.tf_trg_de_mrna_annotation_id();
alter table deapp.de_mrna_annotation drop constraint de_mrna_annotation_pkey;
alter table deapp.de_mrna_annotation drop column de_mrna_annotation_id;
alter table deapp.de_mrna_annotation add constraint de_mrna_annotation_pk primary key (probeset_id);

alter table deapp.de_qpcr_mirna_annotation add constraint de_qpcr_mirna_annotation_pk primary key (probeset_id);
alter table deapp.de_rnaseq_annotation add constraint de_rnaseq_annotation_pk primary key (transcript_id);

-- FKs to Annotation Table
alter table deapp.de_subject_microarray_data add constraint de_subject_microarray_data_probeset_id_fk foreign key (probeset_id) references de_mrna_annotation(probeset_id);
alter table deapp.de_subject_mirna_data add constraint de_subject_mirna_data_probeset_id_fk foreign key (probeset_id) references de_qpcr_mirna_annotation(probeset_id);
alter table deapp.de_subject_rna_data add constraint de_subject_rna_data_probeset_id_fk foreign key (probeset_id) references de_rnaseq_annotation(transcript_id);

-- FKs to Platform Definition
alter table deapp.de_metabolite_annotation add constraint de_metabolite_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade;
alter table deapp.de_mrna_annotation add constraint de_mrna_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade;
alter table deapp.de_qpcr_mirna_annotation add constraint de_qpcr_mirna_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade;
alter table deapp.de_rbm_annotation add constraint de_rbm_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade;
alter table deapp.de_protein_annotation add constraint de_protein_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade;
alter table deapp.de_rnaseq_annotation add constraint de_rnaseq_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade;
alter table deapp.de_metabolite_super_pathways add constraint de_metabolite_super_pathways_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform);
alter table deapp.de_metabolite_sub_pathways add constraint de_metabolite_sub_pathways_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform);