add shared schema changes for b137

diff --git a/schema/shared_schema/dbSNP_main_constraint.sql b/schema/shared_schema/dbSNP_main_constraint.sql
index 57326f775b2a1d1a74d96eb4bb188c2f9785ff3c..01537c83bad7d33a4e6976e87e178f22464cec18 100644 (file)
--- a/schema/shared_schema/dbSNP_main_constraint.sql
+++ b/schema/shared_schema/dbSNP_main_constraint.sql
@@ -20,9 +20,8 @@ GO
 
 
 
-ALTER TABLE [GenomeBuildInfo] ADD CONSTRAINT [df_GenomeBuildInfo_insert_time] DEFAULT (GETDATE()) FOR [insert_time]
-ALTER TABLE [GenomeBuildInfo] ADD CONSTRAINT [df_GenomeBuildInfo_is_major] DEFAULT ((1)) FOR [is_major]
-ALTER TABLE [GenomeBuildInfo] ADD CONSTRAINT [df_GenomeBuildInfo_ncbi_genome_tax_id] DEFAULT ((0)) FOR [ncbi_genome_tax_id]
+ALTER TABLE [GenomeBuildInfo] ADD CONSTRAINT [PK_GenomeBuildInfo]  PRIMARY KEY  CLUSTERED ([snp_build_id] ASC,[tax_id] ASC,[is_major] ASC)\r
+GO
 
 ALTER TABLE [GtyAllele] ADD CONSTRAINT [pk_GtyAllele]  PRIMARY KEY  CLUSTERED ([gty_id] ASC,[rev_flag] ASC,[chr_num] ASC)\r
 GO

diff --git a/schema/shared_schema/dbSNP_main_constraint_postgresql.sql b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql
index e01f634b7b4d8d3d3cf263afa3795cac3604e1a1..e51fe4b5403a528bcb6a625ad50223765520d01e 100644 (file)
--- a/schema/shared_schema/dbSNP_main_constraint_postgresql.sql
+++ b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql
@@ -8,9 +8,7 @@ ALTER TABLE Author ADD CONSTRAINT pk_Author  PRIMARY KEY   (pub_id,position);
 ALTER TABLE CpGCode ADD CONSTRAINT pk_CpGCode  PRIMARY KEY   (code);
 
 
-ALTER TABLE GenomeBuildInfo ALTER COLUMN insert_time SET DEFAULT NOW();
-ALTER TABLE GenomeBuildInfo ALTER COLUMN is_major SET DEFAULT (1);
-ALTER TABLE GenomeBuildInfo ALTER COLUMN ncbi_genome_tax_id SET DEFAULT (0);
+ALTER TABLE GenomeBuildInfo ADD CONSTRAINT PK_GneomeBuildInfo PRIMARY KEY (snp_build_id,tax_id,is_major);
 
 ALTER TABLE GtyAllele ADD CONSTRAINT pk_GtyAllele  PRIMARY KEY   (gty_id,rev_flag,chr_num);
 ALTER TABLE IUPACna ADD CONSTRAINT pk_IUPACna  PRIMARY KEY   (allele);

diff --git a/schema/shared_schema/dbSNP_main_table.sql b/schema/shared_schema/dbSNP_main_table.sql
index ddfe6cf99acd8a9efca3af7ec6ec24a767fc9998..21f1a8cdd70b2a6f10f4f1ef1077037c1fa63bd7 100644 (file)
--- a/schema/shared_schema/dbSNP_main_table.sql
+++ b/schema/shared_schema/dbSNP_main_table.sql
@@ -372,8 +372,9 @@ CREATE TABLE [SnpFunctionCode]
 [top_level_class] [char](5) NOT NULL ,
 [is_coding] [tinyint] NOT NULL ,
 [is_exon] [bit] NULL ,
-[var_prop_loc_code] [int] NULL ,
-[var_prop_effect_code] [int] NULL
+[var_prop_effect_code] [int] NULL ,
+[var_prop_gene_loc_code] [int] NULL ,
+[SO_id] [varchar](32) NULL
 )\r
 GO
 
@@ -400,7 +401,7 @@ GO
 CREATE TABLE [SubSNPDelComm]
 (
 [comment_id] [smallint] NOT NULL ,
-[comment] [varchar](255) NOT NULL ,
+[comment] [varchar](400) NULL ,
 [create_time] [smalldatetime] NULL
 )\r
 GO

diff --git a/schema/shared_schema/dbSNP_main_table_postgresql.sql b/schema/shared_schema/dbSNP_main_table_postgresql.sql
index 210b47058386e3a7ed9aa508389791d6d77a6e33..e9cf36b343206f8538d7c07c68d6323568d8e310 100644 (file)
--- a/schema/shared_schema/dbSNP_main_table_postgresql.sql
+++ b/schema/shared_schema/dbSNP_main_table_postgresql.sql
@@ -308,8 +308,9 @@ create_time TIMESTAMP NOT NULL ,
 top_level_class char(5) NOT NULL ,
 is_coding smallint NOT NULL ,
 is_exon bit NULL ,
-var_prop_loc_code int NULL ,
-var_prop_effect_code int NULL
+var_prop_effect_code int NULL ,
+var_prop_gene_loc_code int NULL ,
+SO_id varchar(32) NULL
 );
 CREATE TABLE SnpValidationCode
 (
@@ -330,7 +331,7 @@ create_time TIMESTAMP NOT NULL
 CREATE TABLE SubSNPDelComm
 (
 comment_id smallint NOT NULL ,
-comment varchar(255) NOT NULL ,
+comment varchar(400) NOT NULL ,
 create_time TIMESTAMP NULL
 );
 CREATE TABLE SubSNPSeqTypeCode