From: Don Armstrong Date: Sat, 18 Aug 2012 01:21:33 +0000 (-0700) Subject: add b137 schema changes for the human tables X-Git-Url: https://git.donarmstrong.com/?p=dbsnp.git;a=commitdiff_plain;h=0171eebd400d97cb96607bf112f049d6b30f9b77 add b137 schema changes for the human tables --- diff --git a/schema/human_9606_schema/human_9606_constraint.sql b/schema/human_9606_schema/human_9606_constraint.sql index 396b03c..4e56b36 100644 --- a/schema/human_9606_schema/human_9606_constraint.sql +++ b/schema/human_9606_schema/human_9606_constraint.sql @@ -24,6 +24,7 @@ GO ALTER TABLE [ClinSigCode] ADD CONSTRAINT [DF__ClinSigCo__creat__5FBB8CD0] DEFAULT (GETDATE()) FOR [create_time] ALTER TABLE [ClinSigCode] ADD CONSTRAINT [DF__ClinSigCo__last___60AFB109] DEFAULT (GETDATE()) FOR [last_updated_time] +ALTER TABLE [ClinSigCode] ADD CONSTRAINT [DF__ClinSigCo__sever__58B68DF4] DEFAULT ((0)) FOR [severity_level] ALTER TABLE [ClinSigCode] ADD CONSTRAINT [PK__ClinSigC__357D4CF85DD3445E] PRIMARY KEY CLUSTERED ([code] ASC) GO @@ -47,7 +48,7 @@ GO ALTER TABLE [Individual] ADD CONSTRAINT [pk_Individual] PRIMARY KEY CLUSTERED ([ind_id] ASC) GO -ALTER TABLE [OmimVarLocusIdSNP] ADD CONSTRAINT [fk_OmimVarLocusIdSNP_snp_id] FOREIGN KEY (snp_id) REFERENCES [b134_SNP](snp_id) +ALTER TABLE [OmimVarLocusIdSNP] ADD CONSTRAINT [fk_OmimVarLocusIdSNP_snp_id] FOREIGN KEY (snp_id) REFERENCES [b135_SNP](snp_id) GO ALTER TABLE [Pedigree] ADD CONSTRAINT [p_ped_id] PRIMARY KEY CLUSTERED ([ped_id] ASC) @@ -76,9 +77,6 @@ ALTER TABLE [SNPAncestralAllele] ADD CONSTRAINT [DF__SNPAncest__batch__664F5149] ALTER TABLE [SNPAncestralAllele] ADD CONSTRAINT [pk_SNPAncestralAllele] PRIMARY KEY CLUSTERED ([snp_id] ASC,[batch_id] ASC) GO -ALTER TABLE [SNPClinSig] ADD CONSTRAINT [DF__SNPClinSi__upd_t__1BBC3395] DEFAULT (GETDATE()) FOR [upd_time] -ALTER TABLE [SNPClinSig] ADD CONSTRAINT [PK__SNPClinS__430A13A119D3EB23] PRIMARY KEY CLUSTERED ([snp_id] ASC) -GO ALTER TABLE [SNPGtyFreq] ADD CONSTRAINT [pk_SNPGtyFreq] PRIMARY KEY CLUSTERED ([snp_id] ASC,[unigty_id] ASC) GO @@ -90,8 +88,6 @@ ALTER TABLE [SNPHistory] ADD CONSTRAINT [pk_SNPHistory] PRIMARY KEY CLUSTERED GO -ALTER TABLE [SNPSubSNPLink] ADD CONSTRAINT [fk_SNPSubSNPLink_ss] FOREIGN KEY (subsnp_id) REFERENCES [SubSNP](subsnp_id) -GO ALTER TABLE [SNPSuspect] ADD CONSTRAINT [DF__SNPSuspec__upd_t__18BF0002] DEFAULT (GETDATE()) FOR [upd_time] @@ -101,6 +97,8 @@ GO ALTER TABLE [SNPVal] ADD CONSTRAINT [pk_SNPVal] PRIMARY KEY CLUSTERED ([batch_id] ASC,[snp_id] ASC) GO +ALTER TABLE [SNP_HGVS] ADD CONSTRAINT [fk_SNP_HGVS] FOREIGN KEY (snp_id) REFERENCES [b135_SNP](snp_id) +GO ALTER TABLE [SNP_bitfield] ADD CONSTRAINT [pk_SNP_bitfield] PRIMARY KEY CLUSTERED ([snp_id] ASC) GO @@ -163,38 +161,39 @@ ALTER TABLE [SubmittedIndividual] ADD CONSTRAINT [DF__Submitted__ploid__41661CE5 ALTER TABLE [SubmittedIndividual] ADD CONSTRAINT [pk_SubmittedIndividual] PRIMARY KEY CLUSTERED ([pop_id] ASC,[loc_ind_id_upp] ASC) GO +ALTER TABLE [SuspectReasonCode] ADD CONSTRAINT [DF__SuspectRe__creat__130626AC] DEFAULT (GETDATE()) FOR [create_time] +ALTER TABLE [SuspectReasonCode] ADD CONSTRAINT [DF__SuspectRe__last___13FA4AE5] DEFAULT (GETDATE()) FOR [last_update_time] +ALTER TABLE [SuspectReasonCode] ADD CONSTRAINT [PK__SuspectR__357D4CF8111DDE3A] PRIMARY KEY CLUSTERED ([code] ASC) +GO + ALTER TABLE [Synonym] ADD CONSTRAINT [pk_Synonym] PRIMARY KEY NONCLUSTERED ([subsnp_id] ASC,[type] ASC) GO -ALTER TABLE [b135_ContigInfo_37_3] ADD CONSTRAINT [df_b135_ContigInfo_37_3_last_updated_time] DEFAULT (GETDATE()) FOR [last_updated_time] -ALTER TABLE [b135_ContigInfo_37_3] ADD CONSTRAINT [pk_b135_ContigInfo_37_3] PRIMARY KEY CLUSTERED ([contig_gi] ASC) +ALTER TABLE [b137_ContigInfo] ADD CONSTRAINT [df_b137_ContigInfo_last_updated_time] DEFAULT (GETDATE()) FOR [last_updated_time] +ALTER TABLE [b137_ContigInfo] ADD CONSTRAINT [pk_b137_ContigInfo] PRIMARY KEY CLUSTERED ([contig_gi] ASC) GO +ALTER TABLE [b137_MapLink] ADD CONSTRAINT [fk_b137_MapLink_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) +GO -ALTER TABLE [b135_MapLink_37_3] ADD CONSTRAINT [fk_b135_MapLink_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) -GO -ALTER TABLE [b135_SNPChrPosOnRef_37_3] ADD CONSTRAINT [fk_b135_SNPChrPosOnRef_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) +ALTER TABLE [b137_SNPChrPosOnRef] ADD CONSTRAINT [fk_b137_SNPChrPosOnRef_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) GO -ALTER TABLE [b135_SNPContigLoc_37_3] ADD CONSTRAINT [fk_b135_SNPContigLoc_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) +ALTER TABLE [b137_SNPContigLoc] ADD CONSTRAINT [fk_b137_SNPContigLoc_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) GO - -ALTER TABLE [b135_SNPContigLocusId_37_3] ADD CONSTRAINT [fk_b135_SNPContigLocusId_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) +ALTER TABLE [b137_SNPContigLoc] ADD CONSTRAINT [pk_b137_SNPContigLoc] PRIMARY KEY CLUSTERED ([snp_type] ASC,[snp_id] ASC,[ctg_id] ASC,[asn_from] ASC) GO -ALTER TABLE [b135_SNPContigProtein_37_3] ADD CONSTRAINT [fk_b135_SNPContigProtein_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) -GO -ALTER TABLE [b135_SNPMapInfo_37_3] ADD CONSTRAINT [fk_b135_SNPMapInfo_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) -GO -ALTER TABLE [b135_SNPMapLinkProtein_37_3] ADD CONSTRAINT [fk_b135_SNPMapLinkProtein_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) +ALTER TABLE [b137_SNPMapInfo] ADD CONSTRAINT [fk_b137_SNPMapInfo_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id) GO + ALTER TABLE [dn_IND_batchCount] ADD CONSTRAINT [pk_dn_IND_batchCount] PRIMARY KEY NONCLUSTERED ([batch_id] ASC,[pop_id] ASC) GO diff --git a/schema/human_9606_schema/human_9606_constraint_postgresql.sql b/schema/human_9606_schema/human_9606_constraint_postgresql.sql index 92d5973..371c737 100644 --- a/schema/human_9606_schema/human_9606_constraint_postgresql.sql +++ b/schema/human_9606_schema/human_9606_constraint_postgresql.sql @@ -8,6 +8,7 @@ ALTER TABLE BatchStrain ADD CONSTRAINT pk_BatchStrain PRIMARY KEY (batch_id,li ALTER TABLE BatchValCode ADD CONSTRAINT pk_BatchValCode PRIMARY KEY (batch_id); ALTER TABLE ClinSigCode ALTER COLUMN create_time SET DEFAULT NOW(); ALTER TABLE ClinSigCode ALTER COLUMN last_updated_time SET DEFAULT NOW(); +ALTER TABLE ClinSigCode ALTER COLUMN severity_level SET DEFAULT (0); ALTER TABLE ClinSigCode ADD CONSTRAINT PK__ClinSigC__357D4CF85DD3445E PRIMARY KEY (code); ALTER TABLE Contact ADD CONSTRAINT pk_Contact PRIMARY KEY (batch_id,handle); ALTER TABLE GeneIdToName ADD CONSTRAINT pk_GeneIdToName PRIMARY KEY (gene_id); @@ -15,7 +16,7 @@ ALTER TABLE GtyFreqBySsPop ADD CONSTRAINT pk_GtyFreqBySsPop_b129 PRIMARY KEY ( ALTER TABLE IndGrpCode ADD CONSTRAINT pk_IndGrpCode PRIMARY KEY (code); ALTER TABLE IndivBySource ADD CONSTRAINT pk_IndivBySource PRIMARY KEY (src_id,src_ind_id); ALTER TABLE Individual ADD CONSTRAINT pk_Individual PRIMARY KEY (ind_id); -ALTER TABLE OmimVarLocusIdSNP ADD CONSTRAINT fk_OmimVarLocusIdSNP_snp_id FOREIGN KEY (snp_id) REFERENCES b134_SNP(snp_id); +ALTER TABLE OmimVarLocusIdSNP ADD CONSTRAINT fk_OmimVarLocusIdSNP_snp_id FOREIGN KEY (snp_id) REFERENCES b135_SNP(snp_id); ALTER TABLE Pedigree ADD CONSTRAINT p_ped_id PRIMARY KEY (ped_id); ALTER TABLE PedigreeIndividual ADD CONSTRAINT pk_PedigreeIndividual PRIMARY KEY (ped_id,ind_id); ALTER TABLE PopLine ADD CONSTRAINT pk_PopLine PRIMARY KEY (pop_id,line_num); @@ -24,15 +25,13 @@ ALTER TABLE Population ADD CONSTRAINT pk_Population_pop_id PRIMARY KEY (pop_id ALTER TABLE SNPAlleleFreq ADD CONSTRAINT pk_SNPAlleleFreq PRIMARY KEY (snp_id,allele_id); ALTER TABLE SNPAncestralAllele ALTER COLUMN batch_id SET DEFAULT (0); ALTER TABLE SNPAncestralAllele ADD CONSTRAINT pk_SNPAncestralAllele PRIMARY KEY (snp_id,batch_id); -ALTER TABLE SNPClinSig ALTER COLUMN upd_time SET DEFAULT NOW(); -ALTER TABLE SNPClinSig ADD CONSTRAINT PK__SNPClinS__430A13A119D3EB23 PRIMARY KEY (snp_id); ALTER TABLE SNPGtyFreq ADD CONSTRAINT pk_SNPGtyFreq PRIMARY KEY (snp_id,unigty_id); ALTER TABLE SNPHWProb ADD CONSTRAINT pk_SNPHWProb PRIMARY KEY (snp_id); ALTER TABLE SNPHistory ADD CONSTRAINT pk_SNPHistory PRIMARY KEY (snp_id); -ALTER TABLE SNPSubSNPLink ADD CONSTRAINT fk_SNPSubSNPLink_ss FOREIGN KEY (subsnp_id) REFERENCES SubSNP(subsnp_id); ALTER TABLE SNPSuspect ALTER COLUMN upd_time SET DEFAULT NOW(); ALTER TABLE SNPSuspect ADD CONSTRAINT PK__SNPSuspe__430A13A116D6B790 PRIMARY KEY (snp_id); ALTER TABLE SNPVal ADD CONSTRAINT pk_SNPVal PRIMARY KEY (batch_id,snp_id); +ALTER TABLE SNP_HGVS ADD CONSTRAINT fk_SNP_HGVS FOREIGN KEY (snp_id) REFERENCES b135_SNP(snp_id); ALTER TABLE SNP_bitfield ADD CONSTRAINT pk_SNP_bitfield PRIMARY KEY (snp_id); ALTER TABLE SubPop ALTER COLUMN last_updated_time SET DEFAULT NOW(); ALTER TABLE SubPop ADD CONSTRAINT fk_SubPop_batch_id FOREIGN KEY (batch_id) REFERENCES Batch(batch_id); @@ -56,16 +55,17 @@ ALTER TABLE SubSNPSeqPos ADD CONSTRAINT pk_SubSNPSeqPos PRIMARY KEY (subsnp_id ALTER TABLE SubSNP_top_or_bot ADD CONSTRAINT PK__t_ss_top_or_bot__0D5DFFA8 PRIMARY KEY (subsnp_id); ALTER TABLE SubmittedIndividual ALTER COLUMN ploidy SET DEFAULT 2; ALTER TABLE SubmittedIndividual ADD CONSTRAINT pk_SubmittedIndividual PRIMARY KEY (pop_id,loc_ind_id_upp); +ALTER TABLE SuspectReasonCode ALTER COLUMN create_time SET DEFAULT NOW(); +ALTER TABLE SuspectReasonCode ALTER COLUMN last_update_time SET DEFAULT NOW(); +ALTER TABLE SuspectReasonCode ADD CONSTRAINT PK__SuspectR__357D4CF8111DDE3A PRIMARY KEY (code); ALTER TABLE Synonym ADD CONSTRAINT pk_Synonym PRIMARY KEY (subsnp_id,type); -ALTER TABLE b135_ContigInfo_37_3 ALTER COLUMN last_updated_time SET DEFAULT NOW(); -ALTER TABLE b135_ContigInfo_37_3 ADD CONSTRAINT pk_b135_ContigInfo_37_3 PRIMARY KEY (contig_gi); -ALTER TABLE b135_MapLink_37_3 ADD CONSTRAINT fk_b135_MapLink_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); -ALTER TABLE b135_SNPChrPosOnRef_37_3 ADD CONSTRAINT fk_b135_SNPChrPosOnRef_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); -ALTER TABLE b135_SNPContigLoc_37_3 ADD CONSTRAINT fk_b135_SNPContigLoc_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); -ALTER TABLE b135_SNPContigLocusId_37_3 ADD CONSTRAINT fk_b135_SNPContigLocusId_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); -ALTER TABLE b135_SNPContigProtein_37_3 ADD CONSTRAINT fk_b135_SNPContigProtein_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); -ALTER TABLE b135_SNPMapInfo_37_3 ADD CONSTRAINT fk_b135_SNPMapInfo_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); -ALTER TABLE b135_SNPMapLinkProtein_37_3 ADD CONSTRAINT fk_b135_SNPMapLinkProtein_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b137_ContigInfo ALTER COLUMN last_updated_time SET DEFAULT NOW(); +ALTER TABLE b137_ContigInfo ADD CONSTRAINT pk_b137_ContigInfo PRIMARY KEY (contig_gi); +ALTER TABLE b137_MapLink ADD CONSTRAINT fk_b137_MapLink_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b137_SNPChrPosOnRef ADD CONSTRAINT fk_b137_SNPChrPosOnRef_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b137_SNPContigLoc ADD CONSTRAINT fk_b137_SNPContigLoc_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b137_SNPContigLoc ADD CONSTRAINT pk_b137_SNPContigLoc PRIMARY KEY (snp_type,snp_id,ctg_id,asn_from); +ALTER TABLE b137_SNPMapInfo ADD CONSTRAINT fk_b137_SNPMapInfo_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); ALTER TABLE dn_IND_batchCount ADD CONSTRAINT pk_dn_IND_batchCount PRIMARY KEY (batch_id,pop_id); ALTER TABLE dn_PopulationIndGrp ADD CONSTRAINT pk_dn_PopulationIndGrp PRIMARY KEY (pop_id); ALTER TABLE dn_batchCount ADD CONSTRAINT pk_dn_batchCount PRIMARY KEY (batch_id); diff --git a/schema/human_9606_schema/human_9606_index.sql b/schema/human_9606_schema/human_9606_index.sql index c935c66..27bf6fc 100644 --- a/schema/human_9606_schema/human_9606_index.sql +++ b/schema/human_9606_schema/human_9606_index.sql @@ -55,9 +55,7 @@ GO CREATE NONCLUSTERED INDEX [i_handle_loc_pop_id] ON [Population] ([handle] ASC,[loc_pop_id] ASC) GO -CREATE CLUSTERED INDEX [i_rsL] ON [RsMergeArch] ([rsLow] ASC) -GO -CREATE NONCLUSTERED INDEX [i_rsH] ON [RsMergeArch] ([rsHigh] ASC) +CREATE NONCLUSTERED INDEX [ndx_RsMergeArch_HI] ON [RsMergeArch] ([rsHigh] ASC) GO CREATE CLUSTERED INDEX [i_rs] ON [SNP] ([snp_id] ASC) @@ -71,6 +69,8 @@ GO +CREATE CLUSTERED INDEX [i_rs_hgvs] ON [SNPClinSig] ([hgvs_g] ASC,[snp_id] ASC) +GO @@ -143,6 +143,8 @@ CREATE NONCLUSTERED INDEX [i_bid_ss] ON [SubSNP] ([batch_id] ASC,[subsnp_id] ASC GO CREATE NONCLUSTERED INDEX [i_var] ON [SubSNP] ([variation_id] ASC,[subsnp_id] ASC) GO +CREATE NONCLUSTERED INDEX [i_ss_bid_loc] ON [SubSNP] ([subsnp_id] ASC,[batch_id] ASC,[loc_snp_id] ASC) +GO CREATE NONCLUSTERED INDEX [i_acc_part_ind] ON [SubSNPAcc_ins] ([acc_part] ASC,[acc_type_ind] ASC,[subsnp_id] ASC) GO @@ -168,65 +170,38 @@ CREATE NONCLUSTERED INDEX [i_ind] ON [SubmittedIndividual] ([ind_id] ASC) GO -CREATE NONCLUSTERED INDEX [i_asm] ON [b135_ContigInfo_37_3] ([asm_acc] ASC,[asm_version] ASC,[contig_start] ASC,[contig_end] ASC,[chr_gi] ASC) -GO -CREATE NONCLUSTERED INDEX [i_asm_chr_ctg] ON [b135_ContigInfo_37_3] ([asm_acc] ASC,[asm_version] ASC,[chr_gi] ASC,[contig_start] ASC,[contig_end] ASC) -GO -CREATE NONCLUSTERED INDEX [idxContigLabel] ON [b135_ContigInfo_37_3] ([contig_label] ASC) -GO -CREATE NONCLUSTERED INDEX [ndx_ContigInfo_pc_37_3] ON [b135_ContigInfo_37_3] ([placement_status] ASC,[ctg_id] ASC) -GO - -CREATE CLUSTERED INDEX [i_gi] ON [b135_MapLinkInfo_37_3] ([gi] ASC) +CREATE NONCLUSTERED INDEX [i_asm_chr_ctg] ON [b137_ContigInfo] ([asm_acc] ASC,[asm_version] ASC,[chr_gi] ASC,[contig_start] ASC,[contig_end] ASC) GO -CREATE CLUSTERED INDEX [i_rs_gi_pos] ON [b135_MapLink_37_3] ([snp_id] ASC,[gi] ASC,[offset] ASC) -GO -CREATE NONCLUSTERED INDEX [i_src] ON [b135_MapLink_37_3] ([source] ASC,[snp_type] ASC,[snp_id] ASC,[gi] ASC,[offset] ASC,[asn_to] ASC) +CREATE CLUSTERED INDEX [i_rs_gi_pos] ON [b137_MapLink] ([snp_id] ASC,[gi] ASC,[offset] ASC) GO -CREATE CLUSTERED INDEX [i_gi] ON [b135_ProteinInfo_37_3] ([gi] ASC) -GO -CREATE CLUSTERED INDEX [i_rs_pos] ON [b135_Remapped_37_3] ([snp_id] ASC,[src_gi] ASC,[src_from] ASC,[tgt_gi] ASC) -GO -CREATE NONCLUSTERED INDEX [i_tgt_gi] ON [b135_Remapped_37_3] ([tgt_gi] ASC,[snp_type] ASC,[snp_id] ASC) +CREATE CLUSTERED INDEX [i_gi] ON [b137_MapLinkInfo] ([gi] ASC) GO -CREATE CLUSTERED INDEX [i_rs] ON [b135_SNPChrPosOnRef_37_3] ([snp_id] ASC) +CREATE CLUSTERED INDEX [i_gi] ON [b137_ProteinInfo] ([gi] ASC) GO -CREATE CLUSTERED INDEX [i_rs] ON [b135_SNPContigLoc_37_3] ([snp_id] ASC,[ctg_id] ASC,[asn_from] ASC) -GO -CREATE NONCLUSTERED INDEX [i_ctg] ON [b135_SNPContigLoc_37_3] ([ctg_id] ASC) -GO -CREATE NONCLUSTERED INDEX [i_snp] ON [b135_SNPContigLoc_37_3] ([snp_type] ASC,[snp_id] ASC) -GO -CREATE NONCLUSTERED INDEX [idxAsnFrom] ON [b135_SNPContigLoc_37_3] ([asn_from] ASC) -GO -CREATE CLUSTERED INDEX [i_rsCtgMrna] ON [b135_SNPContigLocusId_37_3] ([snp_id] ASC,[contig_acc] ASC,[asn_from] ASC,[locus_id] ASC,[allele] ASC,[mrna_start] ASC,[mrna_gi] ASC) -GO -CREATE NONCLUSTERED INDEX [i_rs] ON [b135_SNPContigLocusId_37_3] ([snp_id] ASC) +CREATE CLUSTERED INDEX [i_rs] ON [b137_SNPChrPosOnRef] ([snp_id] ASC) GO -CREATE CLUSTERED INDEX [i_rs_gi] ON [b135_SNPContigProtein_37_3] ([snp_id] ASC,[contig_gi] ASC,[contig_start] ASC) +CREATE NONCLUSTERED INDEX [i_pos] ON [b137_SNPContigLoc] ([ctg_id] ASC,[asn_from] ASC,[snp_id] ASC) GO -CREATE CLUSTERED INDEX [i_rs_asm] ON [b135_SNPMapInfo_37_3] ([snp_id] ASC,[asm_acc] ASC) -GO -CREATE NONCLUSTERED INDEX [i_asm] ON [b135_SNPMapInfo_37_3] ([asm_acc] ASC,[asm_version] ASC) +CREATE CLUSTERED INDEX [i_rsCtgMrna] ON [b137_SNPContigLocusId] ([snp_id] ASC,[contig_acc] ASC,[asn_from] ASC,[locus_id] ASC,[allele] ASC,[mrna_start] ASC,[mrna_gi] ASC) GO -CREATE NONCLUSTERED INDEX [iuc_rs_assembly_37_3] ON [b135_SNPMapInfo_37_3] ([asm_acc] ASC,[weight] ASC,[snp_id] ASC) +CREATE NONCLUSTERED INDEX [i_rs] ON [b137_SNPContigLocusId] ([snp_id] ASC) GO -CREATE CLUSTERED INDEX [i_rs_pos] ON [b135_SNPMapLinkProtein_37_3] ([snp_id] ASC,[mrna_gi] ASC,[mrna_start] ASC,[mrna_stop] ASC) -GO -CREATE NONCLUSTERED INDEX [i_ctg] ON [b135_SNPMapLinkProtein_37_3] ([ctg_gi] ASC,[mrna_gi] ASC,[snp_id] ASC) + +CREATE CLUSTERED INDEX [i_rs_asm] ON [b137_SNPMapInfo] ([snp_id] ASC,[asm_acc] ASC) GO + CREATE CLUSTERED INDEX [i_pid] ON [dn_IND_batch_pop] ([pop_id] ASC) GO diff --git a/schema/human_9606_schema/human_9606_index_postgresql.sql b/schema/human_9606_schema/human_9606_index_postgresql.sql index 2dd804a..7d63ac4 100644 --- a/schema/human_9606_schema/human_9606_index_postgresql.sql +++ b/schema/human_9606_schema/human_9606_index_postgresql.sql @@ -16,11 +16,11 @@ CREATE INDEX ON PedigreeIndividual (ind_id,ped_id); CREATE INDEX ON PopMandLine (pop_id,line_num); CREATE INDEX ON Population (handle,loc_pop_id_upp); CREATE INDEX ON Population (handle,loc_pop_id); -CREATE INDEX ON RsMergeArch (rsLow); CREATE INDEX ON RsMergeArch (rsHigh); CREATE INDEX ON SNP (snp_id); CREATE INDEX ON SNP (exemplar_subsnp_id,snp_id); CREATE INDEX ON SNP3D (snp_id); +CREATE INDEX ON SNPClinSig (hgvs_g,snp_id); CREATE INDEX ON SNPHistory (history_create_time); CREATE INDEX ON SNPPubmed (snp_id,subsnp_id,pubmed_id,type); CREATE INDEX ON SNPSubSNPLink (subsnp_id); @@ -50,33 +50,20 @@ CREATE INDEX ON SubPopGty (gty_id); CREATE INDEX ON SubSNP (loc_snp_id_upp,subsnp_id); CREATE INDEX ON SubSNP (batch_id,subsnp_id); CREATE INDEX ON SubSNP (variation_id,subsnp_id); +CREATE INDEX ON SubSNP (subsnp_id,batch_id,loc_snp_id); CREATE INDEX ON SubSNPAcc_ins (acc_part,acc_type_ind,subsnp_id); CREATE INDEX ON SubSNPMdFailLn (subsnp_id); CREATE INDEX ON SubSNPPubmed (pubmed_id); CREATE INDEX ON SubmittedIndividual (submitted_ind_id); CREATE INDEX ON SubmittedIndividual (ind_id); -CREATE INDEX ON b135_ContigInfo_37_3 (asm_acc,asm_version,contig_start,contig_end,chr_gi); -CREATE INDEX ON b135_ContigInfo_37_3 (asm_acc,asm_version,chr_gi,contig_start,contig_end); -CREATE INDEX ON b135_ContigInfo_37_3 (contig_label); -CREATE INDEX ON b135_ContigInfo_37_3 (placement_status,ctg_id); -CREATE INDEX ON b135_MapLinkInfo_37_3 (gi); -CREATE INDEX ON b135_MapLink_37_3 (snp_id,gi,offset); -CREATE INDEX ON b135_MapLink_37_3 (source,snp_type,snp_id,gi,offset,asn_to); -CREATE INDEX ON b135_ProteinInfo_37_3 (gi); -CREATE INDEX ON b135_Remapped_37_3 (snp_id,src_gi,src_from,tgt_gi); -CREATE INDEX ON b135_Remapped_37_3 (tgt_gi,snp_type,snp_id); -CREATE INDEX ON b135_SNPChrPosOnRef_37_3 (snp_id); -CREATE INDEX ON b135_SNPContigLoc_37_3 (snp_id,ctg_id,asn_from); -CREATE INDEX ON b135_SNPContigLoc_37_3 (ctg_id); -CREATE INDEX ON b135_SNPContigLoc_37_3 (snp_type,snp_id); -CREATE INDEX ON b135_SNPContigLoc_37_3 (asn_from); -CREATE INDEX ON b135_SNPContigLocusId_37_3 (snp_id,contig_acc,asn_from,locus_id,allele,mrna_start,mrna_gi); -CREATE INDEX ON b135_SNPContigLocusId_37_3 (snp_id); -CREATE INDEX ON b135_SNPContigProtein_37_3 (snp_id,contig_gi,contig_start); -CREATE INDEX ON b135_SNPMapInfo_37_3 (snp_id,asm_acc); -CREATE INDEX ON b135_SNPMapInfo_37_3 (asm_acc,asm_version); -CREATE INDEX ON b135_SNPMapInfo_37_3 (asm_acc,weight,snp_id); -CREATE INDEX ON b135_SNPMapLinkProtein_37_3 (snp_id,mrna_gi,mrna_start,mrna_stop); -CREATE INDEX ON b135_SNPMapLinkProtein_37_3 (ctg_gi,mrna_gi,snp_id); +CREATE INDEX ON b137_ContigInfo (asm_acc,asm_version,chr_gi,contig_start,contig_end); +CREATE INDEX ON b137_MapLink (snp_id,gi,offset); +CREATE INDEX ON b137_MapLinkInfo (gi); +CREATE INDEX ON b137_ProteinInfo (gi); +CREATE INDEX ON b137_SNPChrPosOnRef (snp_id); +CREATE INDEX ON b137_SNPContigLoc (ctg_id,asn_from,snp_id); +CREATE INDEX ON b137_SNPContigLocusId (snp_id,contig_acc,asn_from,locus_id,allele,mrna_start,mrna_gi); +CREATE INDEX ON b137_SNPContigLocusId (snp_id); +CREATE INDEX ON b137_SNPMapInfo (snp_id,asm_acc); CREATE INDEX ON dn_IND_batch_pop (pop_id); CREATE INDEX ON dn_table_rowcount (build_id DESC,reserved_KB_spaceused DESC); diff --git a/schema/human_9606_schema/human_9606_table.sql b/schema/human_9606_schema/human_9606_table.sql index aa943de..f3f3e06 100644 --- a/schema/human_9606_schema/human_9606_table.sql +++ b/schema/human_9606_schema/human_9606_table.sql @@ -7,7 +7,7 @@ CREATE TABLE [AlleleFreqBySsPop] [cnt] [real] NULL , [freq] [real] NULL , [last_updated_time] [datetime] NOT NULL -) +) GO CREATE TABLE [Batch] @@ -31,7 +31,7 @@ CREATE TABLE [Batch] [build_id] [int] NULL , [tax_id] [int] NOT NULL , [ss_cnt] [int] NULL -) +) GO CREATE TABLE [BatchCita] @@ -42,7 +42,7 @@ CREATE TABLE [BatchCita] [citation] [varchar](255) NOT NULL , [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [BatchCommLine] @@ -52,7 +52,7 @@ CREATE TABLE [BatchCommLine] [line] [varchar](255) NOT NULL , [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [BatchCultivar] @@ -62,7 +62,7 @@ CREATE TABLE [BatchCultivar] [line] [varchar](255) NULL , [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [BatchMeExLine] @@ -72,7 +72,7 @@ CREATE TABLE [BatchMeExLine] [line] [varchar](255) NOT NULL , [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [BatchStrain] @@ -82,14 +82,14 @@ CREATE TABLE [BatchStrain] [line] [varchar](255) NOT NULL , [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [BatchValCode] ( [batch_id] [int] NOT NULL , [validation_status] [tinyint] NOT NULL -) +) GO CREATE TABLE [ClinSigCode] @@ -98,8 +98,9 @@ CREATE TABLE [ClinSigCode] [abbrev] [varchar](64) NULL , [descrip] [varchar](255) NULL , [create_time] [smalldatetime] NULL , -[last_updated_time] [smalldatetime] NULL -) +[last_updated_time] [smalldatetime] NULL , +[severity_level] [tinyint] NOT NULL +) GO CREATE TABLE [Contact] @@ -115,7 +116,7 @@ CREATE TABLE [Contact] [address] [varchar](255) NULL , [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [FreqSummaryBySsPop] @@ -132,7 +133,7 @@ CREATE TABLE [FreqSummaryBySsPop] [het] [int] NULL , [het_se] [int] NULL , [last_updated_time] [datetime] NOT NULL -) +) GO CREATE TABLE [GeneIdToName] @@ -146,7 +147,7 @@ CREATE TABLE [GeneIdToName] [ref_tax_id] [int] NOT NULL , [dbSNP_tax_id] [int] NOT NULL , [ins_time] [smalldatetime] NULL -) +) GO CREATE TABLE [GtyFreqBySsPop] @@ -158,7 +159,7 @@ CREATE TABLE [GtyFreqBySsPop] [cnt] [real] NULL , [freq] [real] NULL , [last_updated_time] [datetime] NOT NULL -) +) GO CREATE TABLE [IndGrpCode] @@ -166,7 +167,7 @@ CREATE TABLE [IndGrpCode] [code] [tinyint] NOT NULL , [name] [varchar](32) NOT NULL , [descrip] [varchar](255) NOT NULL -) +) GO CREATE TABLE [IndivBySource] @@ -176,7 +177,7 @@ CREATE TABLE [IndivBySource] [src_ind_id] [varchar](64) NOT NULL , [create_time] [smalldatetime] NOT NULL , [src_ind_grp] [varchar](64) NULL -) +) GO CREATE TABLE [IndivSourceCode] @@ -187,7 +188,7 @@ CREATE TABLE [IndivSourceCode] [create_time] [smalldatetime] NOT NULL , [src_type] [varchar](10) NULL , [display_order] [tinyint] NULL -) +) GO CREATE TABLE [Individual] @@ -197,7 +198,7 @@ CREATE TABLE [Individual] [create_time] [smalldatetime] NOT NULL , [tax_id] [int] NULL , [ind_grp] [tinyint] NULL -) +) GO CREATE TABLE [OmimVarLocusIdSNP] @@ -211,7 +212,7 @@ CREATE TABLE [OmimVarLocusIdSNP] [var2] [char](20) NULL , [var_class] [int] NOT NULL , [snp_id] [int] NOT NULL -) +) GO CREATE TABLE [Pedigree] @@ -220,7 +221,7 @@ CREATE TABLE [Pedigree] [curator] [varchar](12) NOT NULL , [curator_ped_id] [varchar](12) NOT NULL , [create_time] [smalldatetime] NOT NULL -) +) GO CREATE TABLE [PedigreeIndividual] @@ -231,7 +232,7 @@ CREATE TABLE [PedigreeIndividual] [pa_ind_id] [int] NULL , [sex] [char](1) NULL , [create_time] [smalldatetime] NOT NULL -) +) GO CREATE TABLE [PopLine] @@ -241,7 +242,7 @@ CREATE TABLE [PopLine] [line] [varchar](255) NOT NULL , [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [PopMandLine] @@ -251,7 +252,7 @@ CREATE TABLE [PopMandLine] [line] [varchar](255) NOT NULL , [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [Population] @@ -263,7 +264,7 @@ CREATE TABLE [Population] [create_time] [smalldatetime] NULL , [last_updated_time] [smalldatetime] NULL , [src_id] [int] NULL -) +) GO CREATE TABLE [RsMergeArch] @@ -277,7 +278,7 @@ CREATE TABLE [RsMergeArch] [rsCurrent] [int] NULL , [orien2Current] [tinyint] NULL , [comment] [varchar](255) NULL -) +) GO CREATE TABLE [SNP] @@ -290,11 +291,11 @@ CREATE TABLE [SNP] [CpG_code] [tinyint] NULL , [tax_id] [int] NULL , [validation_status] [tinyint] NULL , -[exemplar_subsnp_id] [int] NULL , +[exemplar_subsnp_id] [int] NOT NULL , [univar_id] [int] NULL , [cnt_subsnp] [int] NULL , [map_property] [tinyint] NULL -) +) GO CREATE TABLE [SNP3D] @@ -310,7 +311,7 @@ CREATE TABLE [SNP3D] [neighbor_pos] [int] NOT NULL , [var_color] [int] NOT NULL , [var_label] [int] NOT NULL -) +) GO CREATE TABLE [SNPAlleleFreq] @@ -320,17 +321,17 @@ CREATE TABLE [SNPAlleleFreq] [chr_cnt] [float] NULL , [freq] [float] NULL , [last_updated_time] [datetime] NOT NULL -) +) GO CREATE TABLE [SNPAlleleFreq_TGP] ( -[snp_id] [int] NOT NULL , +[snp_id] [int] NULL , [allele_id] [int] NOT NULL , -[freq] [float] NULL , -[count] [int] NULL , -[is_minor_allele] [bit] NULL -) +[freq] [float] NOT NULL , +[count] [int] NOT NULL , +[is_minor_allele] [int] NOT NULL +) GO CREATE TABLE [SNPAncestralAllele] @@ -338,16 +339,18 @@ CREATE TABLE [SNPAncestralAllele] [snp_id] [int] NOT NULL , [ancestral_allele_id] [int] NOT NULL , [batch_id] [int] NOT NULL -) +) GO CREATE TABLE [SNPClinSig] ( -[snp_id] [int] NOT NULL , -[clin_sig_id] [int] NULL , -[upd_time] [smalldatetime] NULL , -[clin_test_code] [varchar](16) NULL -) +[hgvs_g] [varchar](255) NULL , +[snp_id] [int] NULL , +[tested] [char](1) NULL , +[clin_sig_id] [int] NOT NULL , +[upd_time] [datetime] NOT NULL , +[clin_sig_id_by_rs] [int] NOT NULL +) GO CREATE TABLE [SNPGtyFreq] @@ -357,7 +360,7 @@ CREATE TABLE [SNPGtyFreq] [ind_cnt] [float] NULL , [freq] [float] NULL , [last_updated_time] [datetime] NOT NULL -) +) GO CREATE TABLE [SNPHWProb] @@ -368,7 +371,7 @@ CREATE TABLE [SNPHWProb] [hwp] [real] NULL , [ind_cnt] [smallint] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [SNPHistory] @@ -379,18 +382,18 @@ CREATE TABLE [SNPHistory] [history_create_time] [smalldatetime] NULL , [comment] [varchar](255) NULL , [reactivated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [SNPPubmed] ( [snp_id] [int] NULL , -[subsnp_id] [int] NOT NULL , +[subsnp_id] [int] NULL , [pubmed_id] [int] NULL , -[type] [varchar](9) NOT NULL , +[type] [varchar](16) NULL , [score] [int] NOT NULL , [upd_time] [datetime] NOT NULL -) +) GO CREATE TABLE [SNPSubSNPLink] @@ -402,7 +405,7 @@ CREATE TABLE [SNPSubSNPLink] [last_updated_time] [datetime] NULL , [build_id] [int] NULL , [comment] [varchar](255) NULL -) +) GO CREATE TABLE [SNPSubSNPLinkHistory] @@ -416,7 +419,7 @@ CREATE TABLE [SNPSubSNPLinkHistory] [orien] [tinyint] NULL , [build_id_when_history_made] [int] NULL , [comment] [varchar](255) NULL -) +) GO CREATE TABLE [SNPSuspect] @@ -424,26 +427,14 @@ CREATE TABLE [SNPSuspect] [snp_id] [int] NOT NULL , [reason_code] [int] NULL , [upd_time] [smalldatetime] NULL -) +) GO -CREATE TABLE [dbo].[SuspectReasonCode]( - [code] [int] NOT NULL, - [abbrev] [varchar](64) NULL, - [descrip] [varchar](255) NULL, - [create_time] [smalldatetime] NULL, - [last_update_time] [smalldatetime] NULL, -PRIMARY KEY CLUSTERED -( - [code] ASC -)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] -) ON [PRIMARY] - CREATE TABLE [SNPVal] ( [batch_id] [int] NOT NULL , [snp_id] [int] NOT NULL -) +) GO CREATE TABLE [SNP_HGVS] @@ -452,7 +443,7 @@ CREATE TABLE [SNP_HGVS] [hgvs_name] [varchar](4000) NULL , [source] [varchar](3) NOT NULL , [upd_time] [datetime] NOT NULL -) +) GO CREATE TABLE [SNP_bitfield] @@ -472,7 +463,7 @@ CREATE TABLE [SNP_bitfield] [quality_check] [tinyint] NULL , [upd_time] [datetime] NOT NULL , [encoding] [binary] NULL -) +) GO CREATE TABLE [SubPop] @@ -493,7 +484,7 @@ CREATE TABLE [SubPop] [observed] [varchar](1000) NULL , [sub_het_se_sq] [real] NULL , [subpop_id] [int] IDENTITY(1,1) NOT NULL -) +) GO CREATE TABLE [SubPopAllele] @@ -514,7 +505,7 @@ CREATE TABLE [SubPopAllele] [cnt] [real] NULL , [allele_id] [int] NULL , [subpop_id] [int] NOT NULL -) +) GO CREATE TABLE [SubPopGty] @@ -525,7 +516,7 @@ CREATE TABLE [SubPopGty] [cnt] [real] NULL , [freq] [real] NULL , [last_updated_time] [smalldatetime] NOT NULL -) +) GO CREATE TABLE [SubSNP] @@ -559,7 +550,7 @@ CREATE TABLE [SubSNP] [snp_id] [int] NULL , [tax_id] [int] NOT NULL , [chr_id] [tinyint] NULL -) +) GO CREATE TABLE [SubSNPAcc_ins] @@ -568,7 +559,7 @@ CREATE TABLE [SubSNPAcc_ins] [acc_type_ind] [char](1) NOT NULL , [acc_part] [varchar](16) NOT NULL , [acc_ver] [int] NULL -) +) GO CREATE TABLE [SubSNPCommLine_ins] @@ -576,21 +567,21 @@ CREATE TABLE [SubSNPCommLine_ins] [subsnp_id] [int] NOT NULL , [line_num] [tinyint] NOT NULL , [line] [varchar](255) NOT NULL -) +) GO CREATE TABLE [SubSNPHGVS] ( [subsnp_id] [int] NOT NULL , -[sub_hgvs_c] [varchar](32) NULL , -[sub_hgvs_g] [varchar](64) NULL , -[sub_hgvs_p] [varchar](32) NULL , -[cal_hgvs_c] [varchar](32) NULL , -[cal_hgvs_g] [varchar](32) NULL , -[cal_hgvs_p] [varchar](32) NULL , +[sub_hgvs_c] [varchar](255) NULL , +[sub_hgvs_g] [varchar](255) NULL , +[sub_hgvs_p] [varchar](255) NULL , +[cal_hgvs_c] [varchar](255) NULL , +[cal_hgvs_g] [varchar](255) NULL , +[cal_hgvs_p] [varchar](255) NULL , [upd_time] [smalldatetime] NULL , [gene_id] [int] NULL -) +) GO CREATE TABLE [SubSNPLinkout] @@ -599,7 +590,7 @@ CREATE TABLE [SubSNPLinkout] [url_val] [varchar](255) NOT NULL , [updated_time] [smalldatetime] NULL , [link_type] [varchar](3) NOT NULL -) +) GO CREATE TABLE [SubSNPMdFailLn] @@ -607,7 +598,7 @@ CREATE TABLE [SubSNPMdFailLn] [subsnp_id] [int] NOT NULL , [line_num] [tinyint] NOT NULL , [line] [varchar](255) NOT NULL -) +) GO CREATE TABLE [SubSNPNoVariSeq] @@ -615,17 +606,17 @@ CREATE TABLE [SubSNPNoVariSeq] [subsnp_id] [int] NOT NULL , [line_num] [tinyint] NOT NULL , [line] [varchar](255) NOT NULL -) +) GO CREATE TABLE [SubSNPOmim] ( [subsnp_id] [int] NOT NULL , -[omim_id] [int] NOT NULL , +[omim_id] [varchar](128) NOT NULL , [allele_variant_id] [varchar](32) NULL , [update_time] [smalldatetime] NULL , [mutObsCount] [int] NULL -) +) GO CREATE TABLE [SubSNPPubmed] @@ -634,7 +625,7 @@ CREATE TABLE [SubSNPPubmed] [line_num] [int] NOT NULL , [pubmed_id] [int] NOT NULL , [updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [SubSNPSeq3_ins] @@ -643,7 +634,7 @@ CREATE TABLE [SubSNPSeq3_ins] [type] [tinyint] NOT NULL , [line_num] [tinyint] NOT NULL , [line] [varchar](255) NOT NULL -) +) GO CREATE TABLE [SubSNPSeq5_ins] @@ -652,7 +643,7 @@ CREATE TABLE [SubSNPSeq5_ins] [type] [tinyint] NOT NULL , [line_num] [tinyint] NOT NULL , [line] [varchar](255) NOT NULL -) +) GO CREATE TABLE [SubSNPSeqPos] @@ -665,7 +656,7 @@ CREATE TABLE [SubSNPSeqPos] [downstream_len] [int] NOT NULL , [last_update_time] [smalldatetime] NOT NULL , [mrna_acc] [varchar](24) NULL -) +) GO CREATE TABLE [SubSNP_top_or_bot] @@ -674,7 +665,7 @@ CREATE TABLE [SubSNP_top_or_bot] [top_or_bot] [char](1) NULL , [step] [tinyint] NULL , [last_updated_time] [smalldatetime] NULL -) +) GO CREATE TABLE [SubmittedIndividual] @@ -690,7 +681,17 @@ CREATE TABLE [SubmittedIndividual] [loc_ind_id] [varchar](64) NULL , [loc_ind_grp] [varchar](64) NULL , [ploidy] [tinyint] NULL -) +) +GO + +CREATE TABLE [SuspectReasonCode] +( +[code] [int] NOT NULL , +[abbrev] [varchar](64) NULL , +[descrip] [varchar](255) NULL , +[create_time] [smalldatetime] NULL , +[last_update_time] [smalldatetime] NULL +) GO CREATE TABLE [Synonym] @@ -698,15 +699,15 @@ CREATE TABLE [Synonym] [subsnp_id] [int] NOT NULL , [type] [varchar](64) NOT NULL , [name] [varchar](64) NULL -) +) GO -CREATE TABLE [b135_ContigInfo_37_3] +CREATE TABLE [b137_ContigInfo] ( [ctg_id] [int] NOT NULL , [tax_id] [int] NOT NULL , [contig_acc] [varchar](32) NOT NULL , -[contig_ver] [tinyint] NOT NULL , +[contig_ver] [smallint] NOT NULL , [contig_name] [varchar](63) NULL , [contig_chr] [varchar](32) NULL , [contig_start] [int] NULL , @@ -716,6 +717,10 @@ CREATE TABLE [b135_ContigInfo_37_3] [group_term] [varchar](32) NULL , [group_label] [varchar](32) NULL , [contig_label] [varchar](32) NULL , +[primary_fl] [tinyint] NOT NULL , +[genbank_gi] [int] NULL , +[genbank_acc] [varchar](32) NULL , +[genbank_ver] [smallint] NULL , [build_id] [int] NOT NULL , [build_ver] [int] NOT NULL , [last_updated_time] [datetime] NOT NULL , @@ -726,86 +731,86 @@ CREATE TABLE [b135_ContigInfo_37_3] [par_fl] [tinyint] NULL , [top_level_fl] [tinyint] NOT NULL , [gen_rgn] [varchar](32) NULL -) +) GO -CREATE TABLE [b135_MapLinkHGVS_37_3] +CREATE TABLE [b137_MapLink] ( [snp_type] [char](2) NOT NULL , [snp_id] [int] NULL , [gi] [int] NULL , -[accession_how_cd] [int] NOT NULL , +[accession_how_cd] [int] NULL , [offset] [int] NULL , [asn_to] [int] NULL , [lf_ngbr] [int] NULL , [rf_ngbr] [int] NULL , [lc_ngbr] [int] NULL , [rc_ngbr] [int] NULL , -[loc_type] [tinyint] NULL , +[loc_type] [tinyint] NOT NULL , [build_id] [int] NULL , -[process_time] [smalldatetime] NULL , +[process_time] [smalldatetime] NOT NULL , [process_status] [int] NOT NULL , [orientation] [tinyint] NULL , -[allele] [varchar](255) NULL , +[allele] [varchar](1024) NULL , [aln_quality] [real] NULL , [num_mism] [int] NULL , -[num_del] [int] NULL , [num_ins] [int] NULL , +[num_del] [int] NULL , [tier] [tinyint] NULL , [ctg_gi] [int] NULL , [ctg_from] [int] NULL , [ctg_to] [int] NULL , -[ctg_orient] [tinyint] NULL -) -GO - -CREATE TABLE [b135_MapLinkInfo_37_3] -( -[gi] [int] NOT NULL , -[accession] [varchar](32) NOT NULL , -[accession_ver] [smallint] NOT NULL , -[acc] [varchar](32) NOT NULL , -[version] [smallint] NOT NULL , -[status] [varchar](32) NULL , -[create_dt] [smalldatetime] NULL , -[update_dt] [smalldatetime] NULL , -[cds_from] [int] NULL , -[cds_to] [int] NULL -) +[ctg_orient] [tinyint] NULL , +[source] [varchar](5) NOT NULL +) GO -CREATE TABLE [b135_MapLink_37_3] +CREATE TABLE [b137_MapLinkHGVS] ( [snp_type] [char](2) NOT NULL , [snp_id] [int] NULL , [gi] [int] NULL , -[accession_how_cd] [int] NULL , +[accession_how_cd] [int] NOT NULL , [offset] [int] NULL , [asn_to] [int] NULL , [lf_ngbr] [int] NULL , [rf_ngbr] [int] NULL , [lc_ngbr] [int] NULL , [rc_ngbr] [int] NULL , -[loc_type] [tinyint] NOT NULL , +[loc_type] [tinyint] NULL , [build_id] [int] NULL , -[process_time] [smalldatetime] NOT NULL , +[process_time] [smalldatetime] NULL , [process_status] [int] NOT NULL , [orientation] [tinyint] NULL , -[allele] [varchar](1024) NULL , +[allele] [varchar](255) NULL , [aln_quality] [real] NULL , [num_mism] [int] NULL , -[num_ins] [int] NULL , [num_del] [int] NULL , +[num_ins] [int] NULL , [tier] [tinyint] NULL , [ctg_gi] [int] NULL , [ctg_from] [int] NULL , [ctg_to] [int] NULL , -[ctg_orient] [tinyint] NULL , -[source] [varchar](5) NOT NULL -) +[ctg_orient] [tinyint] NULL +) +GO + +CREATE TABLE [b137_MapLinkInfo] +( +[gi] [int] NOT NULL , +[accession] [varchar](32) NOT NULL , +[accession_ver] [smallint] NOT NULL , +[acc] [varchar](32) NOT NULL , +[version] [smallint] NOT NULL , +[status] [varchar](32) NULL , +[create_dt] [smalldatetime] NULL , +[update_dt] [smalldatetime] NULL , +[cds_from] [int] NULL , +[cds_to] [int] NULL +) GO -CREATE TABLE [b135_ProteinInfo_37_3] +CREATE TABLE [b137_ProteinInfo] ( [gi] [int] NOT NULL , [acc] [varchar](32) NOT NULL , @@ -816,10 +821,10 @@ CREATE TABLE [b135_ProteinInfo_37_3] [status] [varchar](32) NULL , [create_dt] [smalldatetime] NULL , [update_dt] [smalldatetime] NULL -) +) GO -CREATE TABLE [b135_Remapped_37_3] +CREATE TABLE [b137_Remapped] ( [snp_type] [char](2) NOT NULL , [snp_id] [int] NULL , @@ -841,10 +846,10 @@ CREATE TABLE [b135_Remapped_37_3] [tgt_aln_quality] [real] NULL , [last_updated_time] [smalldatetime] NOT NULL , [comment] [varchar](255) NULL -) +) GO -CREATE TABLE [b135_SNPChrPosOnRef_37_3] +CREATE TABLE [b137_SNPChrPosOnRef] ( [snp_id] [int] NOT NULL , [chr] [varchar](32) NOT NULL , @@ -852,10 +857,10 @@ CREATE TABLE [b135_SNPChrPosOnRef_37_3] [orien] [int] NULL , [neighbor_snp_list] [int] NULL , [isPAR] [varchar](1) NOT NULL -) +) GO -CREATE TABLE [b135_SNPContigLoc_37_3] +CREATE TABLE [b137_SNPContigLoc] ( [snp_type] [char](2) NOT NULL , [snp_id] [int] NOT NULL , @@ -879,43 +884,43 @@ CREATE TABLE [b135_SNPContigLoc_37_3] [num_del] [int] NULL , [num_ins] [int] NULL , [tier] [tinyint] NULL -) +) GO -CREATE TABLE [b135_SNPContigLocusId_37_3] +CREATE TABLE [b137_SNPContigLocusId] ( -[snp_id] [int] NOT NULL , -[contig_acc] [varchar](32) NULL , +[snp_id] [int] NULL , +[contig_acc] [varchar](32) NOT NULL , [contig_ver] [tinyint] NULL , [asn_from] [int] NULL , [asn_to] [int] NULL , [locus_id] [int] NULL , -[locus_symbol] [varchar](128) NULL , +[locus_symbol] [varchar](64) NULL , [mrna_acc] [varchar](32) NOT NULL , [mrna_ver] [smallint] NOT NULL , [protein_acc] [varchar](32) NULL , [protein_ver] [smallint] NULL , -[fxn_class] [int] NOT NULL , +[fxn_class] [int] NULL , [reading_frame] [int] NULL , -[allele] [varchar](256) NULL , -[residue] [varchar](1024) NULL , +[allele] [varchar](255) NULL , +[residue] [varchar](1000) NULL , [aa_position] [int] NULL , [build_id] [varchar](4) NOT NULL , [ctg_id] [int] NULL , [mrna_start] [int] NULL , [mrna_stop] [int] NULL , -[codon] [varchar](1024) NULL , +[codon] [varchar](1000) NULL , [protRes] [char](3) NULL , [contig_gi] [int] NULL , -[mrna_gi] [int] NOT NULL , +[mrna_gi] [int] NULL , [mrna_orien] [tinyint] NULL , -[cp_mrna_ver] [smallint] NULL , +[cp_mrna_ver] [int] NULL , [cp_mrna_gi] [int] NULL , -[verComp] [varchar](7) NULL -) +[verComp] [int] NULL +) GO -CREATE TABLE [b135_SNPContigProtein_37_3] +CREATE TABLE [b137_SNPContigProtein] ( [snp_id] [int] NOT NULL , [contig_gi] [int] NOT NULL , @@ -943,10 +948,10 @@ CREATE TABLE [b135_SNPContigProtein_37_3] [codon] [varchar](1024) NULL , [fxn_class] [int] NOT NULL , [in_stop_codon] [tinyint] NULL -) +) GO -CREATE TABLE [b135_SNPMapInfo_37_3] +CREATE TABLE [b137_SNPMapInfo] ( [snp_type] [char](2) NOT NULL , [snp_id] [int] NOT NULL , @@ -963,10 +968,10 @@ CREATE TABLE [b135_SNPMapInfo_37_3] [asm_acc] [varchar](32) NULL , [asm_version] [smallint] NULL , [assembly] [varchar](32) NULL -) +) GO -CREATE TABLE [b135_SNPMapLinkProtein_37_3] +CREATE TABLE [b137_SNPMapLinkProtein] ( [snp_id] [int] NOT NULL , [mrna_acc] [varchar](32) NOT NULL , @@ -994,7 +999,7 @@ CREATE TABLE [b135_SNPMapLinkProtein_37_3] [ctg_to] [int] NULL , [ctg_orientation] [tinyint] NULL , [source] [varchar](7) NULL -) +) GO CREATE TABLE [dn_IND_batchCount] @@ -1005,7 +1010,7 @@ CREATE TABLE [dn_IND_batchCount] [rs_cnt] [int] NOT NULL , [ind_cnt] [int] NOT NULL , [create_time] [datetime] NOT NULL -) +) GO CREATE TABLE [dn_IND_batch_pop] @@ -1013,7 +1018,7 @@ CREATE TABLE [dn_IND_batch_pop] [batch_id] [smallint] NOT NULL , [pop_id] [int] NOT NULL , [update_time] [datetime] NOT NULL -) +) GO CREATE TABLE [dn_PopulationIndGrp] @@ -1021,7 +1026,7 @@ CREATE TABLE [dn_PopulationIndGrp] [pop_id] [int] NOT NULL , [ind_grp_name] [varchar](32) NOT NULL , [ind_grp_code] [tinyint] NOT NULL -) +) GO CREATE TABLE [dn_batchCount] @@ -1033,7 +1038,7 @@ CREATE TABLE [dn_batchCount] [create_time] [smalldatetime] NOT NULL , [pop_cnt] [int] NULL , [ind_cnt] [int] NULL -) +) GO CREATE TABLE [dn_handleCount] @@ -1044,7 +1049,7 @@ CREATE TABLE [dn_handleCount] [rs_cnt] [int] NULL , [rs_validated_cnt] [int] NULL , [create_time] [smalldatetime] NOT NULL -) +) GO CREATE TABLE [dn_snpFxnCnt] @@ -1056,7 +1061,7 @@ CREATE TABLE [dn_snpFxnCnt] [create_time] [smalldatetime] NOT NULL , [last_updated_time] [smalldatetime] NOT NULL , [tax_id] [int] NOT NULL -) +) GO CREATE TABLE [dn_table_rowcount] @@ -1070,6 +1075,6 @@ CREATE TABLE [dn_table_rowcount] [data_KB_spaceused] [int] NULL , [index_size_KB_spaceused] [int] NULL , [unused_KB_spaceused] [int] NULL -) +) GO diff --git a/schema/human_9606_schema/human_9606_table_postgresql.sql b/schema/human_9606_schema/human_9606_table_postgresql.sql index ab67e49..adbef0a 100644 --- a/schema/human_9606_schema/human_9606_table_postgresql.sql +++ b/schema/human_9606_schema/human_9606_table_postgresql.sql @@ -82,7 +82,8 @@ code int NOT NULL , abbrev varchar(64) NULL , descrip varchar(255) NULL , create_time TIMESTAMP NULL , -last_updated_time TIMESTAMP NULL +last_updated_time TIMESTAMP NULL , +severity_level smallint NOT NULL ); CREATE TABLE Contact ( @@ -242,7 +243,7 @@ last_updated_time TIMESTAMP NULL , CpG_code smallint NULL , tax_id int NULL , validation_status smallint NULL , -exemplar_subsnp_id int NULL , +exemplar_subsnp_id int NOT NULL , univar_id int NULL , cnt_subsnp int NULL , map_property smallint NULL @@ -271,11 +272,11 @@ last_updated_time TIMESTAMP NOT NULL ); CREATE TABLE SNPAlleleFreq_TGP ( -snp_id int NOT NULL , +snp_id int NULL , allele_id int NOT NULL , -freq float NULL , -count int NULL , -is_minor_allele bit NULL +freq float NOT NULL , +count int NOT NULL , +is_minor_allele int NOT NULL ); CREATE TABLE SNPAncestralAllele ( @@ -285,10 +286,12 @@ batch_id int NOT NULL ); CREATE TABLE SNPClinSig ( -snp_id int NOT NULL , -clin_sig_id int NULL , -upd_time TIMESTAMP NULL , -clin_test_code varchar(16) NULL +hgvs_g varchar(255) NULL , +snp_id int NULL , +tested char(1) NULL , +clin_sig_id int NOT NULL , +upd_time TIMESTAMP NOT NULL , +clin_sig_id_by_rs int NOT NULL ); CREATE TABLE SNPGtyFreq ( @@ -319,9 +322,9 @@ reactivated_time TIMESTAMP NULL CREATE TABLE SNPPubmed ( snp_id int NULL , -subsnp_id int NOT NULL , +subsnp_id int NULL , pubmed_id int NULL , -type varchar(9) NOT NULL , +type varchar(16) NULL , score int NOT NULL , upd_time TIMESTAMP NOT NULL ); @@ -353,13 +356,6 @@ snp_id int NOT NULL , reason_code int NULL , upd_time TIMESTAMP NULL ); -CREATE TABLE SuspectReasonCode( - code int NOT NULL, - abbrev varchar(64) NULL, - descrip varchar(255) NULL, - create_time TIMESTAMP NULL, - last_update_time TIMESTAMP NULL, -PRIMARY KEY (code)); CREATE TABLE SNPVal ( batch_id int NOT NULL , @@ -485,12 +481,12 @@ line varchar(255) NOT NULL CREATE TABLE SubSNPHGVS ( subsnp_id int NOT NULL , -sub_hgvs_c varchar(32) NULL , -sub_hgvs_g varchar(64) NULL , -sub_hgvs_p varchar(32) NULL , -cal_hgvs_c varchar(32) NULL , -cal_hgvs_g varchar(32) NULL , -cal_hgvs_p varchar(32) NULL , +sub_hgvs_c varchar(255) NULL , +sub_hgvs_g varchar(255) NULL , +sub_hgvs_p varchar(255) NULL , +cal_hgvs_c varchar(255) NULL , +cal_hgvs_g varchar(255) NULL , +cal_hgvs_p varchar(255) NULL , upd_time TIMESTAMP NULL , gene_id int NULL ); @@ -516,7 +512,7 @@ line varchar(255) NOT NULL CREATE TABLE SubSNPOmim ( subsnp_id int NOT NULL , -omim_id int NOT NULL , +omim_id varchar(128) NOT NULL , allele_variant_id varchar(32) NULL , update_time TIMESTAMP NULL , mutObsCount int NULL @@ -574,13 +570,21 @@ loc_ind_id varchar(64) NULL , loc_ind_grp varchar(64) NULL , ploidy smallint NULL ); +CREATE TABLE SuspectReasonCode +( +code int NOT NULL , +abbrev varchar(64) NULL , +descrip varchar(255) NULL , +create_time TIMESTAMP NULL , +last_update_time TIMESTAMP NULL +); CREATE TABLE Synonym ( subsnp_id int NOT NULL , type varchar(64) NOT NULL , name varchar(64) NULL ); -CREATE TABLE b135_ContigInfo_37_3 +CREATE TABLE b137_ContigInfo ( ctg_id int NOT NULL , tax_id int NOT NULL , @@ -595,6 +599,10 @@ contig_gi int NOT NULL , group_term varchar(32) NULL , group_label varchar(32) NULL , contig_label varchar(32) NULL , +primary_fl smallint NOT NULL , +genbank_gi int NULL , +genbank_acc varchar(32) NULL , +genbank_ver smallint NULL , build_id int NOT NULL , build_ver int NOT NULL , last_updated_time TIMESTAMP NOT NULL , @@ -606,77 +614,77 @@ par_fl smallint NULL , top_level_fl smallint NOT NULL , gen_rgn varchar(32) NULL ); -CREATE TABLE b135_MapLinkHGVS_37_3 +CREATE TABLE b137_MapLink ( snp_type char(2) NOT NULL , snp_id int NULL , gi int NULL , -accession_how_cd int NOT NULL , -offset_ int NULL , +accession_how_cd int NULL , +offset int NULL , asn_to int NULL , lf_ngbr int NULL , rf_ngbr int NULL , lc_ngbr int NULL , rc_ngbr int NULL , -loc_type smallint NULL , +loc_type smallint NOT NULL , build_id int NULL , -process_time TIMESTAMP NULL , +process_time TIMESTAMP NOT NULL , process_status int NOT NULL , orientation smallint NULL , -allele varchar(255) NULL , +allele varchar(1024) NULL , aln_quality real NULL , num_mism int NULL , -num_del int NULL , num_ins int NULL , +num_del int NULL , tier smallint NULL , ctg_gi int NULL , ctg_from int NULL , ctg_to int NULL , -ctg_orient smallint NULL -); -CREATE TABLE b135_MapLinkInfo_37_3 -( -gi int NOT NULL , -accession varchar(32) NOT NULL , -accession_ver smallint NOT NULL , -acc varchar(32) NOT NULL , -version smallint NOT NULL , -status varchar(32) NULL , -create_dt TIMESTAMP NULL , -update_dt TIMESTAMP NULL , -cds_from int NULL , -cds_to int NULL +ctg_orient smallint NULL , +source varchar(5) NOT NULL ); -CREATE TABLE b135_MapLink_37_3 +CREATE TABLE b137_MapLinkHGVS ( snp_type char(2) NOT NULL , snp_id int NULL , gi int NULL , -accession_how_cd int NULL , -offset_ int NULL , +accession_how_cd int NOT NULL , +offset int NULL , asn_to int NULL , lf_ngbr int NULL , rf_ngbr int NULL , lc_ngbr int NULL , rc_ngbr int NULL , -loc_type smallint NOT NULL , +loc_type smallint NULL , build_id int NULL , -process_time TIMESTAMP NOT NULL , +process_time TIMESTAMP NULL , process_status int NOT NULL , orientation smallint NULL , -allele varchar(1024) NULL , +allele varchar(255) NULL , aln_quality real NULL , num_mism int NULL , -num_ins int NULL , num_del int NULL , +num_ins int NULL , tier smallint NULL , ctg_gi int NULL , ctg_from int NULL , ctg_to int NULL , -ctg_orient smallint NULL , -source varchar(5) NOT NULL +ctg_orient smallint NULL +); +CREATE TABLE b137_MapLinkInfo +( +gi int NOT NULL , +accession varchar(32) NOT NULL , +accession_ver smallint NOT NULL , +acc varchar(32) NOT NULL , +version smallint NOT NULL , +status varchar(32) NULL , +create_dt TIMESTAMP NULL , +update_dt TIMESTAMP NULL , +cds_from int NULL , +cds_to int NULL ); -CREATE TABLE b135_ProteinInfo_37_3 +CREATE TABLE b137_ProteinInfo ( gi int NOT NULL , acc varchar(32) NOT NULL , @@ -688,7 +696,7 @@ status varchar(32) NULL , create_dt TIMESTAMP NULL , update_dt TIMESTAMP NULL ); -CREATE TABLE b135_Remapped_37_3 +CREATE TABLE b137_Remapped ( snp_type char(2) NOT NULL , snp_id int NULL , @@ -711,7 +719,7 @@ tgt_aln_quality real NULL , last_updated_time TIMESTAMP NOT NULL , comment varchar(255) NULL ); -CREATE TABLE b135_SNPChrPosOnRef_37_3 +CREATE TABLE b137_SNPChrPosOnRef ( snp_id int NOT NULL , chr varchar(32) NOT NULL , @@ -720,7 +728,7 @@ orien int NULL , neighbor_snp_list int NULL , isPAR varchar(1) NOT NULL ); -CREATE TABLE b135_SNPContigLoc_37_3 +CREATE TABLE b137_SNPContigLoc ( snp_type char(2) NOT NULL , snp_id int NOT NULL , @@ -745,38 +753,38 @@ num_del int NULL , num_ins int NULL , tier smallint NULL ); -CREATE TABLE b135_SNPContigLocusId_37_3 +CREATE TABLE b137_SNPContigLocusId ( -snp_id int NOT NULL , -contig_acc varchar(32) NULL , +snp_id int NULL , +contig_acc varchar(32) NOT NULL , contig_ver smallint NULL , asn_from int NULL , asn_to int NULL , locus_id int NULL , -locus_symbol varchar(128) NULL , +locus_symbol varchar(64) NULL , mrna_acc varchar(32) NOT NULL , mrna_ver smallint NOT NULL , protein_acc varchar(32) NULL , protein_ver smallint NULL , -fxn_class int NOT NULL , +fxn_class int NULL , reading_frame int NULL , -allele varchar(256) NULL , -residue varchar(1024) NULL , +allele varchar(255) NULL , +residue varchar(1000) NULL , aa_position int NULL , build_id varchar(4) NOT NULL , ctg_id int NULL , mrna_start int NULL , mrna_stop int NULL , -codon varchar(1024) NULL , +codon varchar(1000) NULL , protRes char(3) NULL , contig_gi int NULL , -mrna_gi int NOT NULL , +mrna_gi int NULL , mrna_orien smallint NULL , -cp_mrna_ver smallint NULL , +cp_mrna_ver int NULL , cp_mrna_gi int NULL , -verComp varchar(7) NULL +verComp int NULL ); -CREATE TABLE b135_SNPContigProtein_37_3 +CREATE TABLE b137_SNPContigProtein ( snp_id int NOT NULL , contig_gi int NOT NULL , @@ -805,7 +813,7 @@ codon varchar(1024) NULL , fxn_class int NOT NULL , in_stop_codon smallint NULL ); -CREATE TABLE b135_SNPMapInfo_37_3 +CREATE TABLE b137_SNPMapInfo ( snp_type char(2) NOT NULL , snp_id int NOT NULL , @@ -823,7 +831,7 @@ asm_acc varchar(32) NULL , asm_version smallint NULL , assembly varchar(32) NULL ); -CREATE TABLE b135_SNPMapLinkProtein_37_3 +CREATE TABLE b137_SNPMapLinkProtein ( snp_id int NOT NULL , mrna_acc varchar(32) NOT NULL ,