add b137 schema changes for the human tables

[dbsnp.git] / schema / human_9606_schema / human_9606_table_postgresql.sql

diff --git a/schema/human_9606_schema/human_9606_table_postgresql.sql b/schema/human_9606_schema/human_9606_table_postgresql.sql
index ab67e49241212a76f15006bbb77a7fd8328e01d2..adbef0a4ac0b9a38688887cc06e859caea3fb369 100644 (file)
--- 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 ,