From 71dd006316add35b0740e9ecc8f81b67c0c3f736 Mon Sep 17 00:00:00 2001 From: Don Armstrong Date: Fri, 17 Aug 2012 17:37:02 -0700 Subject: [PATCH] add shared schema changes for b137 --- schema/shared_schema/dbSNP_main_constraint.sql | 5 ++--- schema/shared_schema/dbSNP_main_constraint_postgresql.sql | 4 +--- schema/shared_schema/dbSNP_main_table.sql | 7 ++++--- schema/shared_schema/dbSNP_main_table_postgresql.sql | 7 ++++--- 4 files changed, 11 insertions(+), 12 deletions(-) diff --git a/schema/shared_schema/dbSNP_main_constraint.sql b/schema/shared_schema/dbSNP_main_constraint.sql index 57326f7..01537c8 100644 --- 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) +GO ALTER TABLE [GtyAllele] ADD CONSTRAINT [pk_GtyAllele] PRIMARY KEY CLUSTERED ([gty_id] ASC,[rev_flag] ASC,[chr_num] ASC) GO diff --git a/schema/shared_schema/dbSNP_main_constraint_postgresql.sql b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql index e01f634..e51fe4b 100644 --- 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 ddfe6cf..21f1a8c 100644 --- 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 ) 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 ) GO diff --git a/schema/shared_schema/dbSNP_main_table_postgresql.sql b/schema/shared_schema/dbSNP_main_table_postgresql.sql index 210b470..e9cf36b 100644 --- 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 -- 2.39.2