From ea7460b34b8123c023d3f73296a9cb5f7703abbe Mon Sep 17 00:00:00 2001 From: Don Armstrong Date: Tue, 6 Dec 2011 16:20:36 -0800 Subject: [PATCH] add human 9606 schema --- .../dbSNP_sup_constraint.sql | 130 +++ .../dbSNP_sup_constraint_postgresql.sql | 118 +++ schema/human_9606_schema/dbSNP_sup_index.sql | 13 + .../dbSNP_sup_index_postgresql.sql | 13 + schema/human_9606_schema/dbSNP_sup_table.sql | 119 +++ .../dbSNP_sup_table_postgresql.sql | 106 +++ .../human_9606_constraint.sql | 511 ++++++++++ .../human_9606_constraint_postgresql.sql | 457 +++++++++ schema/human_9606_schema/human_9606_index.sql | 283 ++++++ .../human_9606_index_postgresql.sql | 212 +++++ schema/human_9606_schema/human_9606_table.sql | 896 ++++++++++++++++++ .../human_9606_table_postgresql.sql | 826 ++++++++++++++++ .../human_gty1_constraint.sql | 78 ++ .../human_gty1_constraint_postgresql.sql | 71 ++ schema/human_9606_schema/human_gty1_index.sql | 53 ++ .../human_gty1_index_postgresql.sql | 49 + .../human_gty1_indexes_creation.pl | 67 ++ schema/human_9606_schema/human_gty1_table.sql | 105 ++ .../human_gty1_table_postgresql.sql | 57 ++ .../human_gty2_constraint.sql | 265 ++++++ .../human_gty2_constraint_postgresql.sql | 241 +++++ schema/human_9606_schema/human_gty2_index.sql | 172 ++++ .../human_gty2_index_postgresql.sql | 123 +++ schema/human_9606_schema/human_gty2_table.sql | 326 +++++++ .../human_gty2_table_postgresql.sql | 301 ++++++ 25 files changed, 5592 insertions(+) create mode 100644 schema/human_9606_schema/dbSNP_sup_constraint.sql create mode 100644 schema/human_9606_schema/dbSNP_sup_constraint_postgresql.sql create mode 100644 schema/human_9606_schema/dbSNP_sup_index.sql create mode 100644 schema/human_9606_schema/dbSNP_sup_index_postgresql.sql create mode 100644 schema/human_9606_schema/dbSNP_sup_table.sql create mode 100644 schema/human_9606_schema/dbSNP_sup_table_postgresql.sql create mode 100644 schema/human_9606_schema/human_9606_constraint.sql create mode 100644 schema/human_9606_schema/human_9606_constraint_postgresql.sql create mode 100644 schema/human_9606_schema/human_9606_index.sql create mode 100644 schema/human_9606_schema/human_9606_index_postgresql.sql create mode 100644 schema/human_9606_schema/human_9606_table.sql create mode 100644 schema/human_9606_schema/human_9606_table_postgresql.sql create mode 100644 schema/human_9606_schema/human_gty1_constraint.sql create mode 100644 schema/human_9606_schema/human_gty1_constraint_postgresql.sql create mode 100644 schema/human_9606_schema/human_gty1_index.sql create mode 100644 schema/human_9606_schema/human_gty1_index_postgresql.sql create mode 100755 schema/human_9606_schema/human_gty1_indexes_creation.pl create mode 100644 schema/human_9606_schema/human_gty1_table.sql create mode 100644 schema/human_9606_schema/human_gty1_table_postgresql.sql create mode 100644 schema/human_9606_schema/human_gty2_constraint.sql create mode 100644 schema/human_9606_schema/human_gty2_constraint_postgresql.sql create mode 100644 schema/human_9606_schema/human_gty2_index.sql create mode 100644 schema/human_9606_schema/human_gty2_index_postgresql.sql create mode 100644 schema/human_9606_schema/human_gty2_table.sql create mode 100644 schema/human_9606_schema/human_gty2_table_postgresql.sql diff --git a/schema/human_9606_schema/dbSNP_sup_constraint.sql b/schema/human_9606_schema/dbSNP_sup_constraint.sql new file mode 100644 index 0000000..3525ab5 --- /dev/null +++ b/schema/human_9606_schema/dbSNP_sup_constraint.sql @@ -0,0 +1,130 @@ +ALTER TABLE [SubSNPAcc_p1_human] ADD + CONSTRAINT [pk_SubSNPAcc_p1_human] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [acc_type_ind], + [acc_part] + ), + CONSTRAINT [ck_SubSNPAcc_p1_human] CHECK ([subsnp_id] >= 1 and [subsnp_id] <= 10000000) +GO + + +ALTER TABLE [SubSNPAcc_p2_human] ADD + CONSTRAINT [pk_SubSNPAcc_p2_human] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [acc_type_ind], + [acc_part] + ), + CONSTRAINT [ck_SubSNPAcc_p2_human] CHECK ([subsnp_id] >= 10000001 and [subsnp_id] <= 20000000) +GO + + +ALTER TABLE [SubSNPAcc_p3_human] ADD + CONSTRAINT [pk_SubSNPAcc_p3] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [acc_type_ind], + [acc_part] + ), + CONSTRAINT [ck_SubSNPAcc_p3] CHECK ([subsnp_id] >= 20000001 and [subsnp_id] <= 30000000) +GO + + +ALTER TABLE [SubSNPCommLine_p1_human] ADD + CONSTRAINT [pk_SubSNPCommLine_p1_human] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [line_num] + ), + CONSTRAINT [ck_SubSNPCommLine_p1_human] CHECK ([subsnp_id] >= 1 and [subsnp_id] <= 10000000) +GO + + +ALTER TABLE [SubSNPCommLine_p2_human] ADD + CONSTRAINT [pk_SubSNPCommLine_p2_human] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [line_num] + ), + CONSTRAINT [ck_SubSNPCommLine_p2_human] CHECK ([subsnp_id] >= 10000001 and [subsnp_id] <= 20000000) +GO + + +ALTER TABLE [SubSNPCommLine_p3_human] ADD + CONSTRAINT [pk_SubSNPCommLine_p3] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [line_num] + ), + CONSTRAINT [ck_SubSNPCommLine_p3] CHECK ([subsnp_id] >= 20000001 and [subsnp_id] <= 30000000) +GO + + +ALTER TABLE [SubSNPSeq3_p1_human] ADD + CONSTRAINT [pk_SubSNPSeq3_p1_human] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [type], + [line_num] + ), + CONSTRAINT [ck_SubSNPSeq3_p1_human] CHECK ([subsnp_id] >= 1 and [subsnp_id] <= 10000000) +GO + + +ALTER TABLE [SubSNPSeq3_p2_human] ADD + CONSTRAINT [pk_SubSNPSeq3_p2_human] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [type], + [line_num] + ), + CONSTRAINT [ck_SubSNPSeq3_p2_human] CHECK ([subsnp_id] >= 10000001 and [subsnp_id] <= 20000000) +GO + + +ALTER TABLE [SubSNPSeq3_p3_human] ADD + CONSTRAINT [pk_SubSNPSeq3_p3] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [type], + [line_num] + ), + CONSTRAINT [ck_SubSNPSeq3_p3] CHECK ([subsnp_id] >= 20000001 and [subsnp_id] <= 30000000) +GO + + +ALTER TABLE [SubSNPSeq5_p1_human] ADD + CONSTRAINT [pk_SubSNPSeq5_p1_human] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [type], + [line_num] + ), + CONSTRAINT [ck_SubSNPSeq5_p1_human] CHECK ([subsnp_id] >= 1 and [subsnp_id] <= 10000000) +GO + + +ALTER TABLE [SubSNPSeq5_p2_human] ADD + CONSTRAINT [pk_SubSNPSeq5_p2_human] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [type], + [line_num] + ), + CONSTRAINT [ck_SubSNPSeq5_p2_human] CHECK ([subsnp_id] >= 10000001 and [subsnp_id] <= 20000000) +GO + + +ALTER TABLE [SubSNPSeq5_p3_human] ADD + CONSTRAINT [pk_SubSNPSeq5_p3] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [type], + [line_num] + ), + CONSTRAINT [ck_SubSNPSeq5_p3] CHECK ([subsnp_id] >= 20000001 and [subsnp_id] <= 30000000) +GO + + + diff --git a/schema/human_9606_schema/dbSNP_sup_constraint_postgresql.sql b/schema/human_9606_schema/dbSNP_sup_constraint_postgresql.sql new file mode 100644 index 0000000..11b5424 --- /dev/null +++ b/schema/human_9606_schema/dbSNP_sup_constraint_postgresql.sql @@ -0,0 +1,118 @@ +ALTER TABLE SubSNPAcc_p1_human ADD + CONSTRAINT pk_SubSNPAcc_p1_human PRIMARY KEY + ( + subsnp_id, + acc_type_ind, + acc_part + ), + ADD CONSTRAINT ck_SubSNPAcc_p1_human CHECK (subsnp_id >= 1 and subsnp_id <= 10000000) +; + +ALTER TABLE SubSNPAcc_p2_human ADD + CONSTRAINT pk_SubSNPAcc_p2_human PRIMARY KEY + ( + subsnp_id, + acc_type_ind, + acc_part + ), ADD + CONSTRAINT ck_SubSNPAcc_p2_human CHECK (subsnp_id >= 10000001 and subsnp_id <= 20000000) +; + +ALTER TABLE SubSNPAcc_p3_human ADD + CONSTRAINT pk_SubSNPAcc_p3 PRIMARY KEY + ( + subsnp_id, + acc_type_ind, + acc_part + ), ADD + CONSTRAINT ck_SubSNPAcc_p3 CHECK (subsnp_id >= 20000001 and subsnp_id <= 30000000) +; + +ALTER TABLE SubSNPCommLine_p1_human ADD + CONSTRAINT pk_SubSNPCommLine_p1_human PRIMARY KEY + ( + subsnp_id, + line_num + ), ADD + CONSTRAINT ck_SubSNPCommLine_p1_human CHECK (subsnp_id >= 1 and subsnp_id <= 10000000) +; + +ALTER TABLE SubSNPCommLine_p2_human ADD + CONSTRAINT pk_SubSNPCommLine_p2_human PRIMARY KEY + ( + subsnp_id, + line_num + ), ADD + CONSTRAINT ck_SubSNPCommLine_p2_human CHECK (subsnp_id >= 10000001 and subsnp_id <= 20000000) +; + +ALTER TABLE SubSNPCommLine_p3_human ADD + CONSTRAINT pk_SubSNPCommLine_p3 PRIMARY KEY + ( + subsnp_id, + line_num + ), ADD + CONSTRAINT ck_SubSNPCommLine_p3 CHECK (subsnp_id >= 20000001 and subsnp_id <= 30000000) +; + +ALTER TABLE SubSNPSeq3_p1_human ADD + CONSTRAINT pk_SubSNPSeq3_p1_human PRIMARY KEY + ( + subsnp_id, + type, + line_num + ), ADD + CONSTRAINT ck_SubSNPSeq3_p1_human CHECK (subsnp_id >= 1 and subsnp_id <= 10000000) +; + +ALTER TABLE SubSNPSeq3_p2_human ADD + CONSTRAINT pk_SubSNPSeq3_p2_human PRIMARY KEY + ( + subsnp_id, + type, + line_num + ), ADD + CONSTRAINT ck_SubSNPSeq3_p2_human CHECK (subsnp_id >= 10000001 and subsnp_id <= 20000000) +; + +ALTER TABLE SubSNPSeq3_p3_human ADD + CONSTRAINT pk_SubSNPSeq3_p3 PRIMARY KEY + ( + subsnp_id, + type, + line_num + ), ADD + CONSTRAINT ck_SubSNPSeq3_p3 CHECK (subsnp_id >= 20000001 and subsnp_id <= 30000000) +; + +ALTER TABLE SubSNPSeq5_p1_human ADD + CONSTRAINT pk_SubSNPSeq5_p1_human PRIMARY KEY + ( + subsnp_id, + type, + line_num + ), ADD + CONSTRAINT ck_SubSNPSeq5_p1_human CHECK (subsnp_id >= 1 and subsnp_id <= 10000000) +; + +ALTER TABLE SubSNPSeq5_p2_human ADD + CONSTRAINT pk_SubSNPSeq5_p2_human PRIMARY KEY + ( + subsnp_id, + type, + line_num + ), ADD + CONSTRAINT ck_SubSNPSeq5_p2_human CHECK (subsnp_id >= 10000001 and subsnp_id <= 20000000) +; + +ALTER TABLE SubSNPSeq5_p3_human ADD + CONSTRAINT pk_SubSNPSeq5_p3 PRIMARY KEY + ( + subsnp_id, + type, + line_num + ), ADD + CONSTRAINT ck_SubSNPSeq5_p3 CHECK (subsnp_id >= 20000001 and subsnp_id <= 30000000) +; + + diff --git a/schema/human_9606_schema/dbSNP_sup_index.sql b/schema/human_9606_schema/dbSNP_sup_index.sql new file mode 100644 index 0000000..384ee9a --- /dev/null +++ b/schema/human_9606_schema/dbSNP_sup_index.sql @@ -0,0 +1,13 @@ + + + + + + + + + + + + + diff --git a/schema/human_9606_schema/dbSNP_sup_index_postgresql.sql b/schema/human_9606_schema/dbSNP_sup_index_postgresql.sql new file mode 100644 index 0000000..384ee9a --- /dev/null +++ b/schema/human_9606_schema/dbSNP_sup_index_postgresql.sql @@ -0,0 +1,13 @@ + + + + + + + + + + + + + diff --git a/schema/human_9606_schema/dbSNP_sup_table.sql b/schema/human_9606_schema/dbSNP_sup_table.sql new file mode 100644 index 0000000..0e0915d --- /dev/null +++ b/schema/human_9606_schema/dbSNP_sup_table.sql @@ -0,0 +1,119 @@ +CREATE TABLE [SubSNPAcc_p1_human] ( + [subsnp_id] [int] NOT NULL , + [acc_type_ind] [char] (1) NOT NULL , + [acc_part] [varchar] (16) NOT NULL , + [acc_ver] [int] NULL +) +GO + + +CREATE TABLE [SubSNPAcc_p2_human] ( + [subsnp_id] [int] NOT NULL , + [acc_type_ind] [char] (1) NOT NULL , + [acc_part] [varchar] (16) NOT NULL , + [acc_ver] [int] NULL +) +GO + + +CREATE TABLE [SubSNPAcc_p3_human] ( + [subsnp_id] [int] NOT NULL , + [acc_type_ind] [char] (1) NOT NULL , + [acc_part] [varchar] (16) NOT NULL , + [acc_ver] [int] NULL +) +GO + + +CREATE TABLE [SubSNPCommLine_p1_human] ( + [subsnp_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPCommLine_p2_human] ( + [subsnp_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPCommLine_p3_human] ( + [subsnp_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPSeq3_p1_human] ( + [subsnp_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPSeq3_p2_human] ( + [subsnp_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPSeq3_p3_human] ( + [subsnp_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPSeq5_p1_human] ( + [subsnp_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPSeq5_p2_human] ( + [subsnp_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPSeq5_p3_human] ( + [subsnp_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [dn_table_rowcount] ( + [tabname] [varchar] (64) NOT NULL , + [row_cnt] [int] NOT NULL , + [build_id] [int] NOT NULL , + [update_time] [datetime] NOT NULL , + [rows_in_spaceused] [int] NULL , + [reserved_KB_spaceused] [int] NULL , + [data_KB_spaceused] [int] NULL , + [index_size_KB_spaceused] [int] NULL , + [unused_KB_spaceused] [int] NULL +) +GO + + diff --git a/schema/human_9606_schema/dbSNP_sup_table_postgresql.sql b/schema/human_9606_schema/dbSNP_sup_table_postgresql.sql new file mode 100644 index 0000000..8ef0491 --- /dev/null +++ b/schema/human_9606_schema/dbSNP_sup_table_postgresql.sql @@ -0,0 +1,106 @@ +CREATE TABLE SubSNPAcc_p1_human ( + subsnp_id int NOT NULL , + acc_type_ind char (1) NOT NULL , + acc_part varchar (16) NOT NULL , + acc_ver int NULL +) +; + +CREATE TABLE SubSNPAcc_p2_human ( + subsnp_id int NOT NULL , + acc_type_ind char (1) NOT NULL , + acc_part varchar (16) NOT NULL , + acc_ver int NULL +) +; + +CREATE TABLE SubSNPAcc_p3_human ( + subsnp_id int NOT NULL , + acc_type_ind char (1) NOT NULL , + acc_part varchar (16) NOT NULL , + acc_ver int NULL +) +; + +CREATE TABLE SubSNPCommLine_p1_human ( + subsnp_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPCommLine_p2_human ( + subsnp_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPCommLine_p3_human ( + subsnp_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPSeq3_p1_human ( + subsnp_id int NOT NULL , + type smallint NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPSeq3_p2_human ( + subsnp_id int NOT NULL , + type smallint NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPSeq3_p3_human ( + subsnp_id int NOT NULL , + type smallint NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPSeq5_p1_human ( + subsnp_id int NOT NULL , + type smallint NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPSeq5_p2_human ( + subsnp_id int NOT NULL , + type smallint NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPSeq5_p3_human ( + subsnp_id int NOT NULL , + type smallint NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE dn_table_rowcount ( + tabname varchar (64) NOT NULL , + row_cnt int NOT NULL , + build_id int NOT NULL , + update_time TIMESTAMP NOT NULL , + rows_in_spaceused int NULL , + reserved_KB_spaceused int NULL , + data_KB_spaceused int NULL , + index_size_KB_spaceused int NULL , + unused_KB_spaceused int NULL +) +; + diff --git a/schema/human_9606_schema/human_9606_constraint.sql b/schema/human_9606_schema/human_9606_constraint.sql new file mode 100644 index 0000000..b2c9097 --- /dev/null +++ b/schema/human_9606_schema/human_9606_constraint.sql @@ -0,0 +1,511 @@ +ALTER TABLE [AlleleFreqBySsPop] ADD + CONSTRAINT [pk_AlleleFreqBySsPop_b129] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [pop_id], + [allele_id] + ) +GO + + +ALTER TABLE [Batch] ADD + CONSTRAINT [pk_Batch] PRIMARY KEY CLUSTERED + ( + [batch_id] + ), + CONSTRAINT [ck_batch_type] CHECK ([batch_type]='ANC' OR ([batch_type]='MNT' OR ([batch_type]='VAL' OR ([batch_type]='POP' OR ([batch_type]='IND' OR ([batch_type]='SNP' OR [batch_type]='NOV')))))) +GO + + +ALTER TABLE [BatchCita] ADD + CONSTRAINT [pk_BatchCita] PRIMARY KEY NONCLUSTERED + ( + [batch_id], + [position] + ) +GO + + +ALTER TABLE [BatchCommLine] ADD + CONSTRAINT [pk_BatchCommLine] PRIMARY KEY NONCLUSTERED + ( + [batch_id], + [line_num] + ) +GO + + +ALTER TABLE [BatchCultivar] ADD + CONSTRAINT [pk_BatchCultivar] PRIMARY KEY NONCLUSTERED + ( + [batch_id], + [line_num] + ) +GO + + +ALTER TABLE [BatchMeExLine] ADD + CONSTRAINT [pk_BatchMeExLine] PRIMARY KEY NONCLUSTERED + ( + [batch_id], + [line_num] + ) +GO + + +ALTER TABLE [BatchStrain] ADD + CONSTRAINT [pk_BatchStrain] PRIMARY KEY NONCLUSTERED + ( + [batch_id], + [line_num] + ) +GO + + +ALTER TABLE [BatchValCode] ADD + CONSTRAINT [pk_BatchValCode] PRIMARY KEY CLUSTERED + ( + [batch_id] + ) +GO + + +ALTER TABLE [Contact] ADD + CONSTRAINT [pk_Contact] PRIMARY KEY NONCLUSTERED + ( + [batch_id], + [handle] + ) +GO + + +ALTER TABLE [FreqSummaryBySsPop] ADD + CONSTRAINT [pk_FreqSummaryBySsPop] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [pop_id] + ) +GO + + +ALTER TABLE [GeneIdToName] ADD + CONSTRAINT [pk_GeneIdToName] PRIMARY KEY CLUSTERED + ( + [gene_id] + ) +GO + + +ALTER TABLE [GtyFreqBySsPop] ADD + CONSTRAINT [pk_GtyFreqBySsPop_b129] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [pop_id], + [unigty_id] + ) +GO + + +ALTER TABLE [IndGrpCode] ADD + CONSTRAINT [pk_IndGrpCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [IndivBySource] ADD + CONSTRAINT [pk_IndivBySource] PRIMARY KEY NONCLUSTERED + ( + [src_id], + [src_ind_id] + ) +GO + + + +ALTER TABLE [Individual] ADD + CONSTRAINT [pk_Individual] PRIMARY KEY CLUSTERED + ( + [ind_id] + ) +GO + + + +ALTER TABLE [Pedigree] ADD + CONSTRAINT [p_ped_id] PRIMARY KEY CLUSTERED + ( + [ped_id] + ) +GO + + +ALTER TABLE [PedigreeIndividual] ADD + CONSTRAINT [pk_PedigreeIndividual] PRIMARY KEY CLUSTERED + ( + [ped_id], + [ind_id] + ) +GO + + +ALTER TABLE [PopLine] ADD + CONSTRAINT [pk_PopLine] PRIMARY KEY CLUSTERED + ( + [pop_id], + [line_num] + ) +GO + + +ALTER TABLE [PopMandLine] ADD + CONSTRAINT [pk_PopMandLine] PRIMARY KEY NONCLUSTERED + ( + [pop_id], + [line_num] + ) +GO + + +ALTER TABLE [Population] ADD + CONSTRAINT [pk_Population_pop_id] PRIMARY KEY CLUSTERED + ( + [pop_id] + ) +GO + + + + + +ALTER TABLE [SNPAlleleFreq] ADD + CONSTRAINT [pk_SNPAlleleFreq] PRIMARY KEY CLUSTERED + ( + [snp_id], + [allele_id] + ) +GO + + +ALTER TABLE [SNPAncestralAllele] ADD + CONSTRAINT [DF__SNPAncest__batch__664F5149] DEFAULT ((0)) FOR [batch_id], + CONSTRAINT [pk_SNPAncestralAllele] PRIMARY KEY CLUSTERED + ( + [snp_id], + [batch_id] + ) +GO + + +ALTER TABLE [SNPGtyFreq] ADD + CONSTRAINT [pk_SNPGtyFreq] PRIMARY KEY CLUSTERED + ( + [snp_id], + [unigty_id] + ) +GO + + +ALTER TABLE [SNPHWProb] ADD + CONSTRAINT [pk_SNPHWProb] PRIMARY KEY CLUSTERED + ( + [snp_id] + ) +GO + + +ALTER TABLE [SNPHistory] ADD + CONSTRAINT [pk_SNPHistory] PRIMARY KEY CLUSTERED + ( + [snp_id] + ) +GO + + +ALTER TABLE [SNPPubmed] ADD + CONSTRAINT [pk_SNPPubmed] PRIMARY KEY CLUSTERED + ( + [snp_id], + [subsnp_id], + [pubmed_id], + [type] + ) +GO + + + + +ALTER TABLE [SNPVal] ADD + CONSTRAINT [pk_SNPVal] PRIMARY KEY CLUSTERED + ( + [batch_id], + [snp_id] + ) +GO + + + +ALTER TABLE [SNP_bitfield] ADD + CONSTRAINT [pk_SNP_bitfield] PRIMARY KEY CLUSTERED + ( + [snp_id] + ) +GO + + +ALTER TABLE [SubPop] ADD + CONSTRAINT [DF__SubPopB10__last___19AB9A98] DEFAULT (getdate()) FOR [last_updated_time], + PRIMARY KEY CLUSTERED + ( + [batch_id], + [subsnp_id], + [pop_id], + [type] + ), + CONSTRAINT [fk_SubPop_batch_id] FOREIGN KEY + ( + [batch_id] + ) REFERENCES [Batch] ( + [batch_id] + ), + CONSTRAINT [fk_SubPop_pop] FOREIGN KEY + ( + [pop_id] + ) REFERENCES [Population] ( + [pop_id] + ), + CHECK ([type] = 'GTY' or ([type] = 'HET' or [type] = 'ALE')) +GO + + +ALTER TABLE [SubPopAllele] ADD + CONSTRAINT [fk_SubPopAllele_bid_ss_pop_type] FOREIGN KEY + ( + [batch_id], + [subsnp_id], + [pop_id], + [type] + ) REFERENCES [SubPop] ( + [batch_id], + [subsnp_id], + [pop_id], + [type] + ) ON DELETE CASCADE +GO + + +ALTER TABLE [SubPopGty] ADD + CONSTRAINT [pk_SubPopGty] PRIMARY KEY CLUSTERED + ( + [subpop_id], + [gty_id] + ) +GO + + +ALTER TABLE [SubSNP] ADD + CONSTRAINT [pk_SubSNP] PRIMARY KEY CLUSTERED + ( + [subsnp_id] + ) +GO + + +ALTER TABLE [SubSNPAcc_ins] ADD + CONSTRAINT [pk_SubSNPAcc] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [acc_part], + [acc_type_ind] + ), + CONSTRAINT [ck_SubSNPAcc_ins] CHECK ([subsnp_id]>=(30000001) AND [subsnp_id]<=(300000000)) +GO + + +ALTER TABLE [SubSNPCommLine_ins] ADD + CONSTRAINT [pk_SubSNPCommLine] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [line_num] + ), + CONSTRAINT [ck_SubSNPCommLine_ins] CHECK ([subsnp_id]>=(30000001) AND [subsnp_id]<=(300000000)) +GO + + +ALTER TABLE [SubSNPHGVS] ADD + CONSTRAINT [pk_SubSNPHGVS] PRIMARY KEY CLUSTERED + ( + [subsnp_id] + ) +GO + + +ALTER TABLE [SubSNPLinkout] ADD + CONSTRAINT [DF__SubSNPLin__link___660506BC] DEFAULT ('NA') FOR [link_type], + CONSTRAINT [pk_SubSNPLinkout] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [link_type] + ), + CONSTRAINT [ck_SubSNPLinkout_link_type] CHECK ([link_type]='NA' OR [link_type]='OTH' OR [link_type]='SRC') +GO + + +ALTER TABLE [SubSNPMdFailLn] ADD + CONSTRAINT [pk_SubSNPMdFailLn] PRIMARY KEY NONCLUSTERED + ( + [subsnp_id], + [line_num] + ) +GO + + +ALTER TABLE [SubSNPNoVariSeq] ADD + CONSTRAINT [pk_SubSNPNoVariSeq] PRIMARY KEY NONCLUSTERED + ( + [subsnp_id], + [line_num] + ) +GO + + +ALTER TABLE [SubSNPOmim] ADD + CONSTRAINT [pk_SubSNPOmim] PRIMARY KEY CLUSTERED + ( + [subsnp_id] + ) +GO + + +ALTER TABLE [SubSNPPubmed] ADD + CONSTRAINT [pk_SubSNPPubmed] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [line_num] + ) +GO + + +ALTER TABLE [SubSNPSeq3_ins] ADD + CONSTRAINT [pk_SubSNPSeq3] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [type], + [line_num] + ), + CONSTRAINT [ck_SubSNPSeq3_ins] CHECK ([subsnp_id]>=(30000001) AND [subsnp_id]<=(300000000)) +GO + + +ALTER TABLE [SubSNPSeq5_ins] ADD + CONSTRAINT [pk_SubSNPSeq5] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [type], + [line_num] + ), + CONSTRAINT [ck_SubSNPSeq5_ins] CHECK ([subsnp_id]>=(30000001) AND [subsnp_id]<=(300000000)) +GO + + +ALTER TABLE [SubSNPSeqPos] ADD + CONSTRAINT [pk_SubSNPSeqPos] PRIMARY KEY CLUSTERED + ( + [subsnp_id] + ) +GO + + +ALTER TABLE [SubSNP_top_or_bot] ADD + CONSTRAINT [PK__t_ss_top_or_bot__0D5DFFA8] PRIMARY KEY CLUSTERED + ( + [subsnp_id] + ) +GO + + +ALTER TABLE [SubmittedIndividual] ADD + CONSTRAINT [DF__Submitted__ploid__41661CE5] DEFAULT (2) FOR [ploidy], + CONSTRAINT [pk_SubmittedIndividual] PRIMARY KEY CLUSTERED + ( + [pop_id], + [loc_ind_id_upp] + ) +GO + + +ALTER TABLE [Synonym] ADD + CONSTRAINT [pk_Synonym] PRIMARY KEY NONCLUSTERED + ( + [subsnp_id], + [type] + ) +GO + + +ALTER TABLE [b132_ContigInfo_37_1] ADD + CONSTRAINT [pk_b132_ContigInfo] PRIMARY KEY CLUSTERED + ( + [ctg_id] + ) +GO + + + + + + + +ALTER TABLE [b132_SNPContigLoc_37_1] ADD + CONSTRAINT [ck_snp_type] CHECK ([snp_type]='rs') +GO + + +ALTER TABLE [b132_SNPContigLocusId_37_1] ADD + CONSTRAINT [ck_fxn] CHECK ([fxn_class]=(75) OR [fxn_class]=(73) OR [fxn_class]=(55) OR [fxn_class]=(53) OR [fxn_class]=(45) OR [fxn_class]=(44) OR [fxn_class]=(43) OR [fxn_class]=(42) OR [fxn_class]=(41) OR [fxn_class]=(15) OR [fxn_class]=(13) OR [fxn_class]=(8) OR [fxn_class]=(6) OR [fxn_class]=(3)) +GO + + +ALTER TABLE [b132_SNPMapInfo_37_1] ADD + CONSTRAINT [ck_SNPMapInfo_snp_type] CHECK ([snp_type]='rs') +GO + + +ALTER TABLE [dn_IND_batchCount] ADD + CONSTRAINT [pk_dn_IND_batchCount] PRIMARY KEY NONCLUSTERED + ( + [batch_id], + [pop_id] + ) +GO + + + +ALTER TABLE [dn_PopulationIndGrp] ADD + CONSTRAINT [pk_dn_PopulationIndGrp] PRIMARY KEY CLUSTERED + ( + [pop_id] + ) +GO + + +ALTER TABLE [dn_batchCount] ADD + CONSTRAINT [pk_dn_batchCount] PRIMARY KEY NONCLUSTERED + ( + [batch_id] + ) +GO + + +ALTER TABLE [dn_handleCount] ADD + CONSTRAINT [pk_dn_handleCount] PRIMARY KEY NONCLUSTERED + ( + [handle], + [batch_type] + ) +GO + + + + diff --git a/schema/human_9606_schema/human_9606_constraint_postgresql.sql b/schema/human_9606_schema/human_9606_constraint_postgresql.sql new file mode 100644 index 0000000..38eca52 --- /dev/null +++ b/schema/human_9606_schema/human_9606_constraint_postgresql.sql @@ -0,0 +1,457 @@ +ALTER TABLE AlleleFreqBySsPop ADD + CONSTRAINT pk_AlleleFreqBySsPop_b129 PRIMARY KEY + ( + subsnp_id, + pop_id, + allele_id + ) +; + +ALTER TABLE Batch ADD + CONSTRAINT pk_Batch PRIMARY KEY + ( + batch_id + ), ADD + CONSTRAINT ck_batch_type CHECK (batch_type='ANC' OR (batch_type='MNT' OR (batch_type='VAL' OR (batch_type='POP' OR (batch_type='IND' OR (batch_type='SNP' OR batch_type='NOV')))))) +; + +ALTER TABLE BatchCita ADD + CONSTRAINT pk_BatchCita PRIMARY KEY + ( + batch_id, + position + ) +; + +ALTER TABLE BatchCommLine ADD + CONSTRAINT pk_BatchCommLine PRIMARY KEY + ( + batch_id, + line_num + ) +; + +ALTER TABLE BatchCultivar ADD + CONSTRAINT pk_BatchCultivar PRIMARY KEY + ( + batch_id, + line_num + ) +; + +ALTER TABLE BatchMeExLine ADD + CONSTRAINT pk_BatchMeExLine PRIMARY KEY + ( + batch_id, + line_num + ) +; + +ALTER TABLE BatchStrain ADD + CONSTRAINT pk_BatchStrain PRIMARY KEY + ( + batch_id, + line_num + ) +; + +ALTER TABLE BatchValCode ADD + CONSTRAINT pk_BatchValCode PRIMARY KEY + ( + batch_id + ) +; + +ALTER TABLE Contact ADD + CONSTRAINT pk_Contact PRIMARY KEY + ( + batch_id, + handle + ) +; + +ALTER TABLE FreqSummaryBySsPop ADD + CONSTRAINT pk_FreqSummaryBySsPop PRIMARY KEY + ( + subsnp_id, + pop_id + ) +; + +ALTER TABLE GeneIdToName ADD + CONSTRAINT pk_GeneIdToName PRIMARY KEY + ( + gene_id + ) +; + +ALTER TABLE GtyFreqBySsPop ADD + CONSTRAINT pk_GtyFreqBySsPop_b129 PRIMARY KEY + ( + subsnp_id, + pop_id, + unigty_id + ) +; + +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 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 + ) +; + +ALTER TABLE PopMandLine ADD + CONSTRAINT pk_PopMandLine PRIMARY KEY + ( + pop_id, + line_num + ) +; + +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 ADD +-- CONSTRAINT DF__SNPAncest__batch__664F5149 DEFAULT ((0)) FOR batch_id, + CONSTRAINT pk_SNPAncestralAllele PRIMARY KEY + ( + snp_id, + batch_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 SNPPubmed ADD + CONSTRAINT pk_SNPPubmed PRIMARY KEY + ( + snp_id, + subsnp_id, + pubmed_id, + type + ) +; + + + +ALTER TABLE SNPVal ADD + CONSTRAINT pk_SNPVal PRIMARY KEY + ( + batch_id, + snp_id + ) +; + + +ALTER TABLE SNP_bitfield ADD + CONSTRAINT pk_SNP_bitfield PRIMARY KEY + ( + snp_id + ) +; + +ALTER TABLE SubPop ADD +-- CONSTRAINT DF__SubPopB10__last___19AB9A98 DEFAULT (getdate()) FOR last_updated_time, + PRIMARY KEY + ( + batch_id, + subsnp_id, + pop_id, + type + ), ADD + CONSTRAINT fk_SubPop_batch_id FOREIGN KEY + ( + batch_id + ) REFERENCES Batch ( + batch_id + ), ADD + CONSTRAINT fk_SubPop_pop FOREIGN KEY + ( + pop_id + ) REFERENCES Population ( + pop_id + ), ADD + CHECK (type = 'GTY' or (type = 'HET' or type = 'ALE')) +; + +ALTER TABLE SubPopAllele ADD + CONSTRAINT fk_SubPopAllele_bid_ss_pop_type FOREIGN KEY + ( + batch_id, + subsnp_id, + pop_id, + type + ) REFERENCES SubPop ( + batch_id, + subsnp_id, + pop_id, + type + ) ON DELETE CASCADE +; + +ALTER TABLE SubPopGty ADD + CONSTRAINT pk_SubPopGty PRIMARY KEY + ( + subpop_id, + gty_id + ) +; + +ALTER TABLE SubSNP ADD + CONSTRAINT pk_SubSNP PRIMARY KEY + ( + subsnp_id + ) +; + +ALTER TABLE SubSNPAcc_ins ADD + CONSTRAINT pk_SubSNPAcc PRIMARY KEY + ( + subsnp_id, + acc_part, + acc_type_ind + ), ADD + CONSTRAINT ck_SubSNPAcc_ins CHECK (subsnp_id>=(30000001) AND subsnp_id<=(300000000)) +; + +ALTER TABLE SubSNPCommLine_ins ADD + CONSTRAINT pk_SubSNPCommLine PRIMARY KEY + ( + subsnp_id, + line_num + ), ADD + CONSTRAINT ck_SubSNPCommLine_ins CHECK (subsnp_id>=(30000001) AND subsnp_id<=(300000000)) +; + +ALTER TABLE SubSNPHGVS ADD + CONSTRAINT pk_SubSNPHGVS PRIMARY KEY + ( + subsnp_id + ) +; + +ALTER TABLE SubSNPLinkout ADD +-- CONSTRAINT DF__SubSNPLin__link___660506BC DEFAULT ('NA') FOR link_type, + CONSTRAINT pk_SubSNPLinkout PRIMARY KEY + ( + subsnp_id, + link_type + ), ADD + CONSTRAINT ck_SubSNPLinkout_link_type CHECK (link_type='NA' OR link_type='OTH' OR link_type='SRC') +; + +ALTER TABLE SubSNPMdFailLn ADD + CONSTRAINT pk_SubSNPMdFailLn PRIMARY KEY + ( + subsnp_id, + line_num + ) +; + +ALTER TABLE SubSNPNoVariSeq ADD + CONSTRAINT pk_SubSNPNoVariSeq PRIMARY KEY + ( + subsnp_id, + line_num + ) +; + +ALTER TABLE SubSNPOmim ADD + CONSTRAINT pk_SubSNPOmim PRIMARY KEY + ( + subsnp_id + ) +; + +ALTER TABLE SubSNPPubmed ADD + CONSTRAINT pk_SubSNPPubmed PRIMARY KEY + ( + subsnp_id, + line_num + ) +; + +ALTER TABLE SubSNPSeq3_ins ADD + CONSTRAINT pk_SubSNPSeq3 PRIMARY KEY + ( + subsnp_id, + type, + line_num + ), ADD + CONSTRAINT ck_SubSNPSeq3_ins CHECK (subsnp_id>=(30000001) AND subsnp_id<=(300000000)) +; + +ALTER TABLE SubSNPSeq5_ins ADD + CONSTRAINT pk_SubSNPSeq5 PRIMARY KEY + ( + subsnp_id, + type, + line_num + ), ADD + CONSTRAINT ck_SubSNPSeq5_ins CHECK (subsnp_id>=(30000001) AND subsnp_id<=(300000000)) +; + +ALTER TABLE SubSNPSeqPos ADD + CONSTRAINT pk_SubSNPSeqPos PRIMARY KEY + ( + subsnp_id +x ) +; + +ALTER TABLE SubSNP_top_or_bot ADD + CONSTRAINT PK__t_ss_top_or_bot__0D5DFFA8 PRIMARY KEY + ( + subsnp_id + ) +; + +ALTER TABLE SubmittedIndividual ADD +-- CONSTRAINT DF__Submitted__ploid__41661CE5 DEFAULT (2) FOR ploidy, + CONSTRAINT pk_SubmittedIndividual PRIMARY KEY + ( + pop_id, + loc_ind_id_upp + ) +; + +ALTER TABLE Synonym ADD + CONSTRAINT pk_Synonym PRIMARY KEY + ( + subsnp_id, + type + ) +; + +ALTER TABLE b132_ContigInfo_37_1 ADD + CONSTRAINT pk_b132_ContigInfo PRIMARY KEY + ( + ctg_id + ) +; + + + + + + +ALTER TABLE b132_SNPContigLoc_37_1 ADD + CONSTRAINT ck_snp_type CHECK (snp_type='rs') +; + +ALTER TABLE b132_SNPContigLocusId_37_1 ADD + CONSTRAINT ck_fxn CHECK (fxn_class=(75) OR fxn_class=(73) OR fxn_class=(55) OR fxn_class=(53) OR fxn_class=(45) OR fxn_class=(44) OR fxn_class=(43) OR fxn_class=(42) OR fxn_class=(41) OR fxn_class=(15) OR fxn_class=(13) OR fxn_class=(8) OR fxn_class=(6) OR fxn_class=(3)) +; + +ALTER TABLE b132_SNPMapInfo_37_1 ADD + CONSTRAINT ck_SNPMapInfo_snp_type CHECK (snp_type='rs') +; + +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 + ) +; + +ALTER TABLE dn_handleCount ADD + CONSTRAINT pk_dn_handleCount PRIMARY KEY + ( + handle, + batch_type + ) +; + + + diff --git a/schema/human_9606_schema/human_9606_index.sql b/schema/human_9606_schema/human_9606_index.sql new file mode 100644 index 0000000..5602cb2 --- /dev/null +++ b/schema/human_9606_schema/human_9606_index.sql @@ -0,0 +1,283 @@ + + CREATE INDEX [i_tax_id] ON [Batch]([tax_id]) +GO + + CREATE INDEX [i_submitted_time] ON [Batch]([submitted_time]) +GO + + CREATE INDEX [i_handle_loc_batch_id] ON [Batch]([handle], [loc_batch_id]) +GO + + CREATE INDEX [i_pop_id] ON [Batch]([pop_id]) +GO + + CREATE INDEX [i_success_rate_int] ON [Batch]([success_rate_int]) +GO + + CREATE INDEX [i_method_id] ON [Batch]([method_id]) +GO + + CREATE INDEX [i_lbid_u] ON [Batch]([loc_batch_id_upp]) +GO + + CREATE INDEX [i_last_updated] ON [Batch]([last_updated_time]) +GO + + + CREATE INDEX [i_pub_id] ON [BatchCita]([pub_id]) +GO + + + + CREATE INDEX [i_BatchCultivar2] ON [BatchCultivar]([line]) +GO + + + + CREATE INDEX [i_strain] ON [BatchStrain]([line]) +GO + + + + + + + + + + + + + CREATE UNIQUE INDEX [i_curator_ped_id] ON [Pedigree]([curator], [curator_ped_id]) +GO + + + CREATE UNIQUE INDEX [i_ind_ped] ON [PedigreeIndividual]([ind_id], [ped_id]) +GO + + + + CREATE UNIQUE CLUSTERED INDEX [i_pid_line_num] ON [PopMandLine]([pop_id], [line_num]) +GO + + + CREATE UNIQUE INDEX [i_handle_loc_pop_id_upp] ON [Population]([handle], [loc_pop_id_upp]) +GO + + CREATE UNIQUE INDEX [i_handle_loc_pop_id] ON [Population]([handle], [loc_pop_id]) +GO + + + + CREATE UNIQUE CLUSTERED INDEX [i_rs] ON [SNP]([snp_id]) +GO + + CREATE INDEX [i_exemplarSs] ON [SNP]([exemplar_subsnp_id], [snp_id]) +GO + + + CREATE INDEX [i_rs] ON [SNP3D]([snp_id]) +GO + + + + + + + CREATE INDEX [i_hist_time] ON [SNPHistory]([history_create_time]) +GO + + + + CREATE UNIQUE CLUSTERED INDEX [i_ss] ON [SNPSubSNPLink]([subsnp_id]) +GO + + CREATE INDEX [i_rs] ON [SNPSubSNPLink]([snp_id], [subsnp_id], [substrand_reversed_flag]) +GO + + + CREATE CLUSTERED INDEX [i_snp_id] ON [SNPSubSNPLinkHistory]([snp_id]) +GO + + CREATE UNIQUE INDEX [i_ss_rs] ON [SNPSubSNPLinkHistory]([subsnp_id], [snp_id]) +GO + + CREATE INDEX [i_build_id] ON [SNPSubSNPLinkHistory]([build_id]) +GO + + CREATE INDEX [i_build_id_when_history_made] ON [SNPSubSNPLinkHistory]([build_id_when_history_made]) +GO + + + CREATE INDEX [i_rs] ON [SNPVal]([snp_id]) +GO + + + CREATE UNIQUE CLUSTERED INDEX [i_rs_hgvs] ON [SNP_HGVS]([snp_id], [hgvs_name]) +GO + + + CREATE INDEX [i_link_prop_b2] ON [SNP_bitfield]([link_prop_b2]) +GO + + + CREATE INDEX [i_ss] ON [SubPop]([subsnp_id]) +GO + + CREATE INDEX [i_pop_ss] ON [SubPop]([pop_id], [subsnp_id]) +GO + + CREATE INDEX [i_subpop_id] ON [SubPop]([subpop_id]) +GO + + + CREATE UNIQUE CLUSTERED INDEX [iuc_SubPopAllele] ON [SubPopAllele]([batch_id], [pop_id], [subsnp_id], [type], [allele], [other]) +GO + + CREATE INDEX [iSubPopAllele1] ON [SubPopAllele]([freq]) +GO + + CREATE INDEX [iSubPopAllele2] ON [SubPopAllele]([freq_max]) +GO + + CREATE INDEX [iSubPopAllele3] ON [SubPopAllele]([freq_min]) +GO + + CREATE INDEX [i_subsnp_id] ON [SubPopAllele]([subsnp_id]) +GO + + CREATE INDEX [i_type] ON [SubPopAllele]([type]) +GO + + CREATE INDEX [i_last_updated_time] ON [SubPopAllele]([last_updated_time]) +GO + + CREATE INDEX [i_allele_id] ON [SubPopAllele]([allele_id]) +GO + + CREATE INDEX [i_subpop_id] ON [SubPopAllele]([subpop_id]) +GO + + CREATE UNIQUE INDEX [i_sp_ale] ON [SubPopAllele]([subpop_id], [type], [allele], [other]) +GO + + + CREATE INDEX [i_subpop_id] ON [SubPopGty]([subpop_id]) +GO + + CREATE INDEX [i_gty_id] ON [SubPopGty]([gty_id]) +GO + + + CREATE INDEX [i_loc_snp] ON [SubSNP]([loc_snp_id_upp], [subsnp_id]) +GO + + CREATE INDEX [i_bid_ss] ON [SubSNP]([batch_id], [subsnp_id]) +GO + + + CREATE INDEX [i_acc_part_ind] ON [SubSNPAcc_ins]([acc_part], [acc_type_ind], [subsnp_id]) +GO + + + + + + CREATE INDEX [i_subsnp_id] ON [SubSNPMdFailLn]([subsnp_id]) +GO + + + + + CREATE INDEX [i_pmid] ON [SubSNPPubmed]([pubmed_id]) +GO + + + + + + + CREATE UNIQUE INDEX [i_submitted_ind_id] ON [SubmittedIndividual]([submitted_ind_id]) +GO + + CREATE INDEX [i_ind] ON [SubmittedIndividual]([ind_id]) +GO + + + + CREATE UNIQUE INDEX [idxContigName] ON [b132_ContigInfo_37_1]([contig_name]) +GO + + CREATE INDEX [idxContigLabel] ON [b132_ContigInfo_37_1]([contig_label]) +GO + + CREATE INDEX [idxGroupTerm] ON [b132_ContigInfo_37_1]([group_term]) +GO + + CREATE UNIQUE INDEX [idxCtgGi] ON [b132_ContigInfo_37_1]([contig_gi]) +GO + + CREATE INDEX [idxContigId] ON [b132_ContigInfo_37_1]([ctg_id]) +GO + + CREATE INDEX [idxContigNameAcc] ON [b132_ContigInfo_37_1]([contig_name], [contig_acc]) +GO + + + CREATE INDEX [i_order_assem_132] ON [b132_GenomeAssemblyInfo_37_1]([rep_order], [assembly]) +GO + + + CREATE INDEX [idxGi] ON [b132_MapLinkInfo_37_1]([gi]) +GO + + + CREATE CLUSTERED INDEX [i_rs_gi_pos] ON [b132_MapLink_37_1]([snp_id], [gi]) +GO + + CREATE INDEX [idxSnpLoc] ON [b132_MapLink_37_1]([gi], [offset], [asn_to]) +GO + + + + CREATE UNIQUE CLUSTERED INDEX [i_rs] ON [b132_SNPChrPosOnRef_37_1]([snp_id]) +GO + + + CREATE UNIQUE CLUSTERED INDEX [i_rs] ON [b132_SNPContigLoc_37_1]([snp_id], [ctg_id], [asn_from]) +GO + + CREATE INDEX [i_ctgPos] ON [b132_SNPContigLoc_37_1]([ctg_id], [asn_from]) +GO + + CREATE INDEX [idxSnpId] ON [b132_SNPContigLoc_37_1]([snp_type], [snp_id]) +GO + + CREATE INDEX [iCtgSnp] ON [b132_SNPContigLoc_37_1]([ctg_id], [snp_id]) +GO + + + CREATE UNIQUE CLUSTERED INDEX [i_rsCtgMrna] ON [b132_SNPContigLocusId_37_1]([locus_id], [contig_acc], [mrna_gi], [snp_id], [asn_from], [allele], [mrna_start]) +GO + + CREATE INDEX [i_rs] ON [b132_SNPContigLocusId_37_1]([snp_id]) +GO + + + CREATE UNIQUE CLUSTERED INDEX [i_rs] ON [b132_SNPMapInfo_37_1]([snp_id], [assembly]) +GO + + + + CREATE CLUSTERED INDEX [i_pid] ON [dn_IND_batch_pop]([pop_id]) +GO + + + + + + + CREATE CLUSTERED INDEX [i_b_size] ON [dn_table_rowcount]([build_id] DESC , [reserved_KB_spaceused] DESC ) +GO + + diff --git a/schema/human_9606_schema/human_9606_index_postgresql.sql b/schema/human_9606_schema/human_9606_index_postgresql.sql new file mode 100644 index 0000000..0c7f146 --- /dev/null +++ b/schema/human_9606_schema/human_9606_index_postgresql.sql @@ -0,0 +1,212 @@ + + CREATE INDEX ON Batch(tax_id) +; + CREATE INDEX ON Batch(submitted_time) +; + CREATE INDEX ON Batch(handle, loc_batch_id) +; + CREATE INDEX ON Batch(pop_id) +; + CREATE INDEX ON Batch(success_rate_int) +; + CREATE INDEX ON Batch(method_id) +; + CREATE INDEX ON Batch(loc_batch_id_upp) +; + CREATE INDEX ON Batch(last_updated_time) +; + + CREATE INDEX ON BatchCita(pub_id) +; + + + CREATE INDEX ON BatchCultivar(line) +; + + + CREATE INDEX ON BatchStrain(line) +; + + + + + + + + + + + + CREATE UNIQUE INDEX ON Pedigree(curator, curator_ped_id) +; + + CREATE UNIQUE INDEX ON PedigreeIndividual(ind_id, ped_id) +; + + + CREATE UNIQUE INDEX ON PopMandLine(pop_id, line_num) +; + + CREATE UNIQUE INDEX ON Population(handle, loc_pop_id_upp) +; + CREATE UNIQUE INDEX ON Population(handle, loc_pop_id) +; + + + CREATE UNIQUE INDEX ON SNP(snp_id) +; + CREATE INDEX ON SNP(exemplar_subsnp_id, snp_id) +; + + CREATE INDEX ON SNP3D(snp_id) +; + + + + + + CREATE INDEX ON SNPHistory(history_create_time) +; + + + CREATE UNIQUE INDEX ON SNPSubSNPLink(subsnp_id) +; + CREATE INDEX ON SNPSubSNPLink(snp_id, subsnp_id, substrand_reversed_flag) +; + + CREATE INDEX ON SNPSubSNPLinkHistory(snp_id) +; + CREATE UNIQUE INDEX ON SNPSubSNPLinkHistory(subsnp_id, snp_id) +; + CREATE INDEX ON SNPSubSNPLinkHistory(build_id) +; + CREATE INDEX ON SNPSubSNPLinkHistory(build_id_when_history_made) +; + + CREATE INDEX ON SNPVal(snp_id) +; + + CREATE UNIQUE INDEX ON SNP_HGVS(snp_id, hgvs_name) +; + + CREATE INDEX ON SNP_bitfield(link_prop_b2) +; + + CREATE INDEX ON SubPop(subsnp_id) +; + CREATE INDEX ON SubPop(pop_id, subsnp_id) +; + CREATE INDEX ON SubPop(subpop_id) +; + + CREATE UNIQUE INDEX ON SubPopAllele(batch_id, pop_id, subsnp_id, type, allele, other) +; + CREATE INDEX ON SubPopAllele(freq) +; + CREATE INDEX ON SubPopAllele(freq_max) +; + CREATE INDEX ON SubPopAllele(freq_min) +; + CREATE INDEX ON SubPopAllele(subsnp_id) +; + CREATE INDEX ON SubPopAllele(type) +; + CREATE INDEX ON SubPopAllele(last_updated_time) +; + CREATE INDEX ON SubPopAllele(allele_id) +; + CREATE INDEX ON SubPopAllele(subpop_id) +; + CREATE UNIQUE INDEX ON SubPopAllele(subpop_id, type, allele, other) +; + + CREATE INDEX ON SubPopGty(subpop_id) +; + 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 SubSNPAcc_ins(acc_part, acc_type_ind, subsnp_id) +; + + + + + CREATE INDEX ON SubSNPMdFailLn(subsnp_id) +; + + + + CREATE INDEX ON SubSNPPubmed(pubmed_id) +; + + + + + + CREATE UNIQUE INDEX ON SubmittedIndividual(submitted_ind_id) +; + CREATE INDEX ON SubmittedIndividual(ind_id) +; + + + CREATE UNIQUE INDEX ON b132_ContigInfo_37_1(contig_name) +; + CREATE INDEX ON b132_ContigInfo_37_1(contig_label) +; + CREATE INDEX ON b132_ContigInfo_37_1(group_term) +; + CREATE UNIQUE INDEX ON b132_ContigInfo_37_1(contig_gi) +; + CREATE INDEX ON b132_ContigInfo_37_1(ctg_id) +; + CREATE INDEX ON b132_ContigInfo_37_1(contig_name, contig_acc) +; + + CREATE INDEX ON b132_GenomeAssemblyInfo_37_1(rep_order, assembly) +; + + CREATE INDEX ON b132_MapLinkInfo_37_1(gi) +; + + CREATE INDEX ON b132_MapLink_37_1(snp_id, gi) +; + CREATE INDEX ON b132_MapLink_37_1(gi, "offset", asn_to) +; + + + CREATE UNIQUE INDEX ON b132_SNPChrPosOnRef_37_1(snp_id) +; + + CREATE UNIQUE INDEX ON b132_SNPContigLoc_37_1(snp_id, ctg_id, asn_from) +; + CREATE INDEX ON b132_SNPContigLoc_37_1(ctg_id, asn_from) +; + CREATE INDEX ON b132_SNPContigLoc_37_1(snp_type, snp_id) +; + CREATE INDEX ON b132_SNPContigLoc_37_1(ctg_id, snp_id) +; + + CREATE UNIQUE INDEX ON b132_SNPContigLocusId_37_1(locus_id, contig_acc, mrna_gi, snp_id, asn_from, allele, mrna_start) +; + CREATE INDEX ON b132_SNPContigLocusId_37_1(snp_id) +; + + CREATE UNIQUE INDEX ON b132_SNPMapInfo_37_1(snp_id, assembly) +; + + + 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 new file mode 100644 index 0000000..555e45c --- /dev/null +++ b/schema/human_9606_schema/human_9606_table.sql @@ -0,0 +1,896 @@ +CREATE TABLE [AlleleFreqBySsPop] ( + [subsnp_id] [int] NOT NULL , + [pop_id] [int] NOT NULL , + [allele_id] [int] NOT NULL , + [source] [varchar] (2) NOT NULL , + [cnt] [real] NULL , + [freq] [real] NULL , + [last_updated_time] [datetime] NOT NULL +) +GO + + +CREATE TABLE [Batch] ( + [batch_id] [int] NOT NULL , + [handle] [varchar] (20) NOT NULL , + [loc_batch_id] [varchar] (64) NOT NULL , + [loc_batch_id_upp] [varchar] (64) NOT NULL , + [batch_type] [char] (3) NOT NULL , + [status] [tinyint] NULL , + [simul_sts_status] [tinyint] NOT NULL , + [moltype] [varchar] (8) NOT NULL , + [method_id] [int] NOT NULL , + [samplesize] [int] NULL , + [synonym_type] [varchar] (255) NULL , + [submitted_time] [smalldatetime] NOT NULL , + [linkout_url] [varchar] (255) NULL , + [pop_id] [int] NULL , + [last_updated_time] [smalldatetime] NULL , + [success_rate_int] [int] NULL , + [build_id] [int] NULL , + [tax_id] [int] NOT NULL , + [ss_cnt] [int] NULL +) +GO + + +CREATE TABLE [BatchCita] ( + [batch_id] [int] NOT NULL , + [position] [int] NOT NULL , + [pub_id] [int] NOT NULL , + [citation] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [BatchCommLine] ( + [batch_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [BatchCultivar] ( + [batch_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [BatchMeExLine] ( + [batch_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [BatchStrain] ( + [batch_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [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 [Contact] ( + [batch_id] [int] NOT NULL , + [handle] [varchar] (20) NOT NULL , + [name] [varchar] (255) NOT NULL , + [fax] [varchar] (255) NULL , + [phone] [varchar] (255) NULL , + [email] [varchar] (255) NULL , + [lab] [varchar] (255) NULL , + [institution] [varchar] (255) NULL , + [address] [varchar] (255) NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [FreqSummaryBySsPop] ( + [subsnp_id] [int] NOT NULL , + [pop_id] [int] NOT NULL , + [source] [varchar] (1) NOT NULL , + [chr_cnt] [int] NOT NULL , + [ind_cnt] [int] NOT NULL , + [non_founder_ind_cnt] [int] NOT NULL , + [chisq] [real] NULL , + [df] [tinyint] NULL , + [hwp] [real] NULL , + [het] [real] NULL , + [het_se] [real] NULL , + [last_updated_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [GeneIdToName] ( + [gene_id] [int] NOT NULL , + [gene_symbol] [varchar] (64) NOT NULL , + [gene_name] [varchar] (255) NULL , + [gene_type] [varchar] (255) NULL , + [tax_id] [int] NOT NULL , + [last_update_time] [smalldatetime] NOT NULL , + [ref_tax_id] [int] NOT NULL , + [dbSNP_tax_id] [int] NOT NULL , + [ins_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [GtyFreqBySsPop] ( + [subsnp_id] [int] NOT NULL , + [pop_id] [int] NOT NULL , + [unigty_id] [int] NOT NULL , + [source] [varchar] (1) NULL , + [cnt] [real] NULL , + [freq] [real] NULL , + [last_updated_time] [datetime] NOT NULL +) +GO + + +CREATE TABLE [IndGrpCode] ( + [code] [tinyint] NOT NULL , + [name] [varchar] (32) NOT NULL , + [descrip] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [IndivBySource] ( + [ind_id] [int] NOT NULL , + [src_id] [int] NOT NULL , + [src_ind_id] [varchar] (64) NOT NULL , + [create_time] [smalldatetime] NOT NULL , + [src_ind_grp] [varchar] (64) NULL +) +GO + + +CREATE TABLE [IndivSourceCode] ( + [code] [int] NOT NULL , + [name] [varchar] (22) NOT NULL , + [descrip] [varchar] (255) NULL , + [create_time] [smalldatetime] NOT NULL , + [src_type] [varchar] (10) NULL , + [display_order] [tinyint] NULL +) +GO + + +CREATE TABLE [Individual] ( + [ind_id] [int] NOT NULL , + [descrip] [varchar] (255) NULL , + [create_time] [smalldatetime] NOT NULL , + [tax_id] [int] NULL , + [ind_grp] [tinyint] NULL +) +GO + + +CREATE TABLE [OmimVarLocusIdSNP] ( + [omim_id] [int] NOT NULL , + [locus_id] [int] NULL , + [omimvar_id] [char] (4) NULL , + [locus_symbol] [char] (10) NULL , + [var1] [char] (20) NULL , + [aa_position] [int] NULL , + [var2] [char] (20) NULL , + [var_class] [int] NOT NULL , + [snp_id] [int] NOT NULL +) +GO + + +CREATE TABLE [Pedigree] ( + [ped_id] [numeric](7, 0) NOT NULL , + [curator] [varchar] (12) NOT NULL , + [curator_ped_id] [varchar] (12) NOT NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [PedigreeIndividual] ( + [ped_id] [decimal](7, 0) NOT NULL , + [ind_id] [int] NOT NULL , + [ma_ind_id] [int] NULL , + [pa_ind_id] [int] NULL , + [sex] [char] (1) NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [PopLine] ( + [pop_id] [int] NOT NULL , + [line_num] [int] NOT NULL , + [line] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [PopMandLine] ( + [pop_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [Population] ( + [pop_id] [int] NOT NULL , + [handle] [varchar] (20) NOT NULL , + [loc_pop_id] [varchar] (64) NOT NULL , + [loc_pop_id_upp] [varchar] (64) NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL , + [src_id] [int] NULL +) +GO + + +CREATE TABLE [RsMergeArch] ( + [rsHigh] [int] NULL , + [rsLow] [int] NULL , + [build_id] [int] NULL , + [orien] [tinyint] NULL , + [create_time] [datetime] NOT NULL , + [last_updated_time] [datetime] NOT NULL , + [rsCurrent] [int] NULL , + [orien2Current] [tinyint] NULL , + [comment] [varchar] (255) NULL +) +GO + + +CREATE TABLE [SNP] ( + [snp_id] [int] NULL , + [avg_heterozygosity] [real] NULL , + [het_se] [real] NULL , + [create_time] [datetime] NULL , + [last_updated_time] [datetime] NULL , + [CpG_code] [tinyint] NULL , + [tax_id] [int] NULL , + [validation_status] [tinyint] NULL , + [exemplar_subsnp_id] [int] NULL , + [univar_id] [int] NULL , + [cnt_subsnp] [int] NULL , + [map_property] [tinyint] NULL +) +GO + + +CREATE TABLE [SNP3D] ( + [snp_id] [int] NOT NULL , + [protein_acc] [char] (50) NOT NULL , + [master_gi] [int] NOT NULL , + [neighbor_gi] [int] NOT NULL , + [aa_position] [int] NOT NULL , + [var_res] [char] (1) NOT NULL , + [contig_res] [char] (1) NOT NULL , + [neighbor_res] [char] (1) NOT NULL , + [neighbor_pos] [int] NOT NULL , + [var_color] [int] NOT NULL , + [var_label] [int] NOT NULL +) +GO + + +CREATE TABLE [SNPAlleleFreq] ( + [snp_id] [int] NOT NULL , + [allele_id] [int] NOT NULL , + [chr_cnt] [float] NULL , + [freq] [float] NULL , + [last_updated_time] [datetime] NOT NULL +) +GO + + +CREATE TABLE [SNPAncestralAllele] ( + [snp_id] [int] NOT NULL , + [ancestral_allele_id] [int] NOT NULL , + [batch_id] [int] NOT NULL +) +GO + + +CREATE TABLE [SNPGtyFreq] ( + [snp_id] [int] NOT NULL , + [unigty_id] [int] NOT NULL , + [ind_cnt] [float] NULL , + [freq] [float] NULL , + [last_updated_time] [datetime] NOT NULL +) +GO + + +CREATE TABLE [SNPHWProb] ( + [snp_id] [int] NOT NULL , + [df] [tinyint] NULL , + [chisq] [real] NULL , + [hwp] [real] NULL , + [ind_cnt] [smallint] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [SNPHistory] ( + [snp_id] [int] NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NOT NULL , + [history_create_time] [smalldatetime] NULL , + [comment] [varchar] (255) NULL +) +GO + + +CREATE TABLE [SNPPubmed] ( + [snp_id] [int] NOT NULL , + [subsnp_id] [int] NOT NULL , + [pubmed_id] [int] NOT NULL , + [type] [varchar] (16) NOT NULL , + [score] [int] NOT NULL , + [upd_date] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [SNPSubSNPLink] ( + [subsnp_id] [int] NULL , + [snp_id] [int] NULL , + [substrand_reversed_flag] [tinyint] NULL , + [create_time] [datetime] NULL , + [last_updated_time] [datetime] NULL , + [build_id] [int] NULL , + [comment] [varchar] (255) NULL +) +GO + + +CREATE TABLE [SNPSubSNPLinkHistory] ( + [subsnp_id] [int] NULL , + [snp_id] [int] NULL , + [build_id] [int] NULL , + [history_create_time] [datetime] NOT NULL , + [link_create_time] [datetime] NULL , + [link_last_updated_time] [datetime] NULL , + [orien] [tinyint] NULL , + [build_id_when_history_made] [int] NULL , + [comment] [varchar] (255) NULL +) +GO + + +CREATE TABLE [SNPVal] ( + [batch_id] [int] NOT NULL , + [snp_id] [int] NOT NULL +) +GO + + +CREATE TABLE [SNP_HGVS] ( + [snp_id] [int] NULL , + [hgvs_name] [varchar] (256) NULL , + [source] [varchar] (8) NOT NULL , + [upd_time] [datetime] NOT NULL +) +GO + + +CREATE TABLE [SNP_bitfield] ( + [snp_id] [int] NOT NULL , + [ver_code] [tinyint] NULL , + [link_prop_b1] [tinyint] NULL , + [link_prop_b2] [tinyint] NULL , + [gene_prop_b1] [tinyint] NULL , + [gene_prop_b2] [tinyint] NULL , + [map_prop] [tinyint] NULL , + [freq_prop] [tinyint] NULL , + [gty_prop] [tinyint] NULL , + [hapmap_prop] [tinyint] NULL , + [pheno_prop] [tinyint] NULL , + [variation_class] [tinyint] NOT NULL , + [quality_check] [tinyint] NULL , + [upd_time] [datetime] NOT NULL , + [encoding] [binary] (12) NULL +) +GO + + +CREATE TABLE [SubPop] ( + [batch_id] [int] NOT NULL , + [subsnp_id] [int] NOT NULL , + [pop_id] [int] NOT NULL , + [type] [char] (3) NOT NULL , + [samplesize] [int] NOT NULL , + [submitted_strand_code] [tinyint] NULL , + [submitted_rs] [int] NULL , + [allele_flag] [tinyint] NULL , + [ambiguity_status] [tinyint] NULL , + [sub_heterozygosity] [real] NULL , + [est_heterozygosity] [real] NULL , + [est_het_se_sq] [real] NULL , + [last_updated_time] [smalldatetime] NOT NULL , + [observed] [varchar] (1000) NULL , + [sub_het_se_sq] [real] NULL , + [subpop_id] [int] IDENTITY (1, 1) NOT NULL +) +GO + + +CREATE TABLE [SubPopAllele] ( + [batch_id] [int] NOT NULL , + [subsnp_id] [int] NOT NULL , + [pop_id] [int] NOT NULL , + [allele] [char] (1) NOT NULL , + [other] [varchar] (255) NULL , + [freq] [real] NULL , + [cnt_int] [int] NULL , + [freq_min] [real] NULL , + [freq_max] [real] NULL , + [data_src] [varchar] (6) NULL , + [type] [char] (3) NULL , + [last_updated_time] [smalldatetime] NULL , + [allele_flag] [tinyint] NULL , + [cnt] [real] NULL , + [allele_id] [int] NULL , + [subpop_id] [int] NOT NULL +) +GO + + +CREATE TABLE [SubPopGty] ( + [subpop_id] [int] NOT NULL , + [gty_id] [int] NOT NULL , + [gty_str] [varchar] (255) NULL , + [cnt] [real] NULL , + [freq] [real] NULL , + [last_updated_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [SubSNP] ( + [subsnp_id] [int] NOT NULL , + [known_snp_handle] [varchar] (20) NULL , + [known_snp_loc_id] [varchar] (64) NULL , + [known_snp_loc_id_upp] [varchar] (64) NULL , + [batch_id] [int] NOT NULL , + [loc_snp_id] [varchar] (64) NULL , + [loc_snp_id_upp] [varchar] (64) NULL , + [synonym_names] [varchar] (255) NULL , + [loc_sts_id] [varchar] (64) NULL , + [loc_sts_id_upp] [varchar] (64) NULL , + [segregate] [char] (1) NOT NULL , + [indiv_homozygosity_detected] [char] (1) NULL , + [PCR_confirmed_ind] [char] (1) NULL , + [gene_name] [varchar] (64) NULL , + [sequence_len] [int] NULL , + [samplesize] [int] NULL , + [EXPRESSED_SEQUENCE_ind] [char] (1) NULL , + [SOMATIC_ind] [char] (1) NULL , + [sub_locus_id] [int] NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL , + [ancestral_allele] [varchar] (255) NULL , + [CpG_code] [tinyint] NULL , + [variation_id] [int] NULL , + [top_or_bot_strand] [char] (1) NULL , + [validation_status] [tinyint] NULL , + [snp_id] [int] NULL , + [tax_id] [int] NOT NULL , + [chr_id] [tinyint] NULL +) +GO + + +CREATE TABLE [SubSNPAcc_ins] ( + [subsnp_id] [int] NOT NULL , + [acc_type_ind] [char] (1) NOT NULL , + [acc_part] [varchar] (16) NOT NULL , + [acc_ver] [int] NULL +) +GO + + +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] (32) 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 , + [upd_time] [smalldatetime] NULL , + [gene_id] [int] NULL +) +GO + + +CREATE TABLE [SubSNPLinkout] ( + [subsnp_id] [int] NOT NULL , + [url_val] [varchar] (255) NOT NULL , + [updated_time] [smalldatetime] NULL , + [link_type] [varchar] (3) NOT NULL +) +GO + + +CREATE TABLE [SubSNPMdFailLn] ( + [subsnp_id] [int] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +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 , + [allele_variant_id] [varchar] (32) NULL , + [update_time] [smalldatetime] NULL , + [mutObsCount] [int] NULL +) +GO + + +CREATE TABLE [SubSNPPubmed] ( + [subsnp_id] [int] NOT NULL , + [line_num] [int] NOT NULL , + [pubmed_id] [int] NOT NULL , + [updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [SubSNPSeq3_ins] ( + [subsnp_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPSeq5_ins] ( + [subsnp_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [line_num] [tinyint] NOT NULL , + [line] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [SubSNPSeqPos] ( + [subsnp_id] [int] NOT NULL , + [contig_acc] [varchar] (20) NOT NULL , + [contig_pos] [int] NOT NULL , + [chr] [varchar] (2) NULL , + [upstream_len] [int] NOT NULL , + [downstream_len] [int] NOT NULL , + [last_update_time] [smalldatetime] NOT NULL , + [mrna_acc] [varchar] (24) NULL +) +GO + + +CREATE TABLE [SubSNP_top_or_bot] ( + [subsnp_id] [int] NOT NULL , + [top_or_bot] [char] (1) NULL , + [step] [tinyint] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [SubmittedIndividual] ( + [submitted_ind_id] [int] NOT NULL , + [pop_id] [int] NOT NULL , + [loc_ind_id_upp] [varchar] (64) NOT NULL , + [ind_id] [int] NULL , + [create_time] [smalldatetime] NOT NULL , + [last_updated_time] [smalldatetime] NULL , + [tax_id] [int] NOT NULL , + [loc_ind_alias] [varchar] (64) NULL , + [loc_ind_id] [varchar] (64) NULL , + [loc_ind_grp] [varchar] (64) NULL , + [ploidy] [tinyint] NULL +) +GO + + +CREATE TABLE [Synonym] ( + [subsnp_id] [int] NOT NULL , + [type] [varchar] (64) NOT NULL , + [name] [varchar] (64) NULL +) +GO + + +CREATE TABLE [b132_ContigInfo_37_1] ( + [ctg_id] [int] NOT NULL , + [tax_id] [int] NOT NULL , + [contig_acc] [varchar] (32) NOT NULL , + [contig_ver] [tinyint] NULL , + [contig_name] [varchar] (32) NOT NULL , + [contig_chr] [varchar] (32) NULL , + [contig_start] [int] NULL , + [contig_end] [int] NULL , + [orient] [tinyint] NULL , + [contig_gi] [int] NULL , + [group_term] [varchar] (32) NULL , + [group_label] [varchar] (32) NULL , + [contig_label] [varchar] (32) NULL , + [build_id] [int] NULL , + [build_ver] [int] NULL , + [last_updated_time] [datetime] NOT NULL , + [placement_status] [tinyint] NOT NULL +) +GO + + +CREATE TABLE [b132_GenomeAssemblyInfo_37_1] ( + [assembly] [varchar] (32) NOT NULL , + [assembly_type] [varchar] (8) NOT NULL , + [rep_order] [tinyint] NULL , + [comment] [varchar] (255) NULL , + [upd_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [b132_MapLinkInfo_37_1] ( + [gi] [int] NULL , + [accession] [varchar] (127) NULL , + [accession_ver] [int] NULL +) +GO + + +CREATE TABLE [b132_MapLink_37_1] ( + [snp_type] [char] (2) NOT NULL , + [snp_id] [int] NULL , + [gi] [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 , + [build_id] [int] NULL , + [process_time] [datetime] NULL , + [process_status] [int] NULL , + [orientation] [int] NULL , + [allele] [varchar] (1024) NULL , + [aln_quality] [float] NULL , + [num_mism] [int] NULL , + [num_ins] [int] NULL , + [num_del] [int] NULL , + [tier] [int] NULL , + [ctg_gi] [int] NULL , + [ctg_from] [int] NULL , + [ctg_to] [int] NULL , + [ctg_orient] [tinyint] NULL , + [source] [varchar] (5) NOT NULL +) +GO + + +CREATE TABLE [b132_ProteinInfo_37_1] ( + [prot_gi] [int] NULL , + [prot_acc] [varchar] (128) NULL , + [prot_ver] [int] NULL +) +GO + + +CREATE TABLE [b132_SNPChrPosOnRef_37_1] ( + [snp_id] [int] NULL , + [chr] [varchar] (32) NULL , + [pos] [int] NULL , + [orien] [int] NULL , + [neighbor_snp_list] [int] NULL , + [isPAR] [varchar] (1) NOT NULL +) +GO + + +CREATE TABLE [b132_SNPContigLoc_37_1] ( + [snp_type] [char] (2) NOT NULL , + [snp_id] [int] NOT NULL , + [ctg_id] [int] NOT NULL , + [asn_from] [int] NOT NULL , + [asn_to] [int] NOT NULL , + [lf_ngbr] [int] NULL , + [rf_ngbr] [int] NULL , + [lc_ngbr] [int] NOT NULL , + [rc_ngbr] [int] NOT NULL , + [loc_type] [tinyint] NOT NULL , + [phys_pos_from] [int] NULL , + [snp_bld_id] [int] NOT NULL , + [last_updated_time] [datetime] NOT NULL , + [process_status] [int] NOT NULL , + [orientation] [tinyint] NULL , + [allele] [varchar] (1024) NULL , + [loc_sts_uid] [int] NULL , + [aln_quality] [float] NULL , + [num_mism] [int] NULL , + [num_del] [int] NULL , + [num_ins] [int] NULL , + [tier] [tinyint] NULL +) +GO + + +CREATE TABLE [b132_SNPContigLocusId_37_1] ( + [snp_id] [int] NULL , + [contig_acc] [varchar] (32) NULL , + [contig_ver] [tinyint] NULL , + [asn_from] [int] NULL , + [asn_to] [int] NULL , + [locus_id] [int] NULL , + [locus_symbol] [varchar] (128) NULL , + [mrna_acc] [varchar] (128) NULL , + [mrna_ver] [int] NULL , + [protein_acc] [varchar] (128) NULL , + [protein_ver] [int] NULL , + [fxn_class] [int] NULL , + [reading_frame] [int] NULL , + [allele] [varchar] (256) NULL , + [residue] [varchar] (1024) 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 , + [protRes] [char] (3) NULL , + [contig_gi] [int] NULL , + [mrna_gi] [int] NULL , + [mrna_orien] [tinyint] NULL , + [cp_mrna_ver] [int] NULL , + [cp_mrna_gi] [int] NULL , + [verComp] [varchar] (7) NOT NULL +) +GO + + +CREATE TABLE [b132_SNPMapInfo_37_1] ( + [snp_type] [char] (2) NOT NULL , + [snp_id] [int] NOT NULL , + [chr_cnt] [int] NOT NULL , + [contig_cnt] [int] NOT NULL , + [loc_cnt] [int] NOT NULL , + [weight] [int] NOT NULL , + [hap_cnt] [int] NOT NULL , + [placed_cnt] [int] NOT NULL , + [grouped_cnt] [int] NOT NULL , + [unplaced_cnt] [int] NOT NULL , + [md5] [char] (32) NULL , + [assembly] [varchar] (32) NULL +) +GO + + +CREATE TABLE [dn_IND_batchCount] ( + [batch_id] [int] NOT NULL , + [pop_id] [int] NOT NULL , + [ss_cnt] [int] NOT NULL , + [rs_cnt] [int] NOT NULL , + [ind_cnt] [int] NOT NULL , + [create_time] [datetime] NOT NULL +) +GO + + +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] ( + [pop_id] [int] NOT NULL , + [ind_grp_name] [varchar] (32) NOT NULL , + [ind_grp_code] [tinyint] NOT NULL +) +GO + + +CREATE TABLE [dn_batchCount] ( + [batch_id] [int] NOT NULL , + [ss_cnt] [int] NOT NULL , + [rs_cnt] [int] NOT NULL , + [rs_validated_cnt] [int] NOT NULL , + [create_time] [smalldatetime] NOT NULL , + [pop_cnt] [int] NULL , + [ind_cnt] [int] NULL +) +GO + + +CREATE TABLE [dn_handleCount] ( + [handle] [varchar] (20) NOT NULL , + [batch_type] [char] (3) NOT NULL , + [ss_cnt] [int] NOT NULL , + [rs_cnt] [int] NULL , + [rs_validated_cnt] [int] NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [dn_snpFxnCnt] ( + [build_id] [int] NOT NULL , + [fxn_class] [tinyint] NULL , + [snp_cnt] [int] NOT NULL , + [gene_cnt] [int] NOT NULL , + [create_time] [smalldatetime] NOT NULL , + [last_updated_time] [smalldatetime] NOT NULL , + [tax_id] [int] NOT NULL +) +GO + + +CREATE TABLE [dn_table_rowcount] ( + [tabname] [varchar] (64) NOT NULL , + [row_cnt] [int] NOT NULL , + [build_id] [int] NOT NULL , + [update_time] [datetime] NOT NULL , + [rows_in_spaceused] [int] NULL , + [reserved_KB_spaceused] [int] NULL , + [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 new file mode 100644 index 0000000..21f0181 --- /dev/null +++ b/schema/human_9606_schema/human_9606_table_postgresql.sql @@ -0,0 +1,826 @@ +CREATE TABLE AlleleFreqBySsPop ( + subsnp_id int NOT NULL , + pop_id int NOT NULL , + allele_id int NOT NULL , + source varchar (2) NOT NULL , + cnt real NULL , + freq real NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE Batch ( + batch_id int NOT NULL , + handle varchar (20) NOT NULL , + loc_batch_id varchar (64) NOT NULL , + loc_batch_id_upp varchar (64) NOT NULL , + batch_type char (3) NOT NULL , + status smallint NULL , + simul_sts_status smallint NOT NULL , + moltype varchar (8) NOT NULL , + method_id int NOT NULL , + samplesize int NULL , + synonym_type varchar (255) NULL , + submitted_time TIMESTAMP NOT NULL , + linkout_url varchar (255) NULL , + pop_id int NULL , + last_updated_time TIMESTAMP NULL , + success_rate_int int NULL , + build_id int NULL , + tax_id int NOT NULL , + ss_cnt int NULL +) +; + +CREATE TABLE BatchCita ( + batch_id int NOT NULL , + position int NOT NULL , + pub_id int NOT NULL , + citation varchar (255) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE BatchCommLine ( + batch_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE BatchCultivar ( + batch_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE BatchMeExLine ( + batch_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE BatchStrain ( + batch_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE BatchValCode ( + batch_id int NOT NULL , + validation_status smallint NOT NULL +) +; + +CREATE TABLE Contact ( + batch_id int NOT NULL , + handle varchar (20) NOT NULL , + name varchar (255) NOT NULL , + fax varchar (255) NULL , + phone varchar (255) NULL , + email varchar (255) NULL , + lab varchar (255) NULL , + institution varchar (255) NULL , + address varchar (255) NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE FreqSummaryBySsPop ( + subsnp_id int NOT NULL , + pop_id int NOT NULL , + source varchar (1) NOT NULL , + chr_cnt int NOT NULL , + ind_cnt int NOT NULL , + non_founder_ind_cnt int NOT NULL , + chisq real NULL , + df smallint NULL , + hwp real NULL , + het real NULL , + het_se real NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE GeneIdToName ( + gene_id int NOT NULL , + gene_symbol varchar (64) NOT NULL , + gene_name varchar (255) NULL , + gene_type varchar (255) NULL , + tax_id int NOT NULL , + last_update_time TIMESTAMP NOT NULL , + ref_tax_id int NOT NULL , + dbSNP_tax_id int NOT NULL , + ins_time TIMESTAMP NULL +) +; + +CREATE TABLE GtyFreqBySsPop ( + subsnp_id int NOT NULL , + pop_id int NOT NULL , + unigty_id int NOT NULL , + source varchar (1) NULL , + cnt real NULL , + freq real NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE IndGrpCode ( + code smallint NOT NULL , + name varchar (32) NOT NULL , + descrip varchar (255) NOT NULL +) +; + +CREATE TABLE IndivBySource ( + ind_id int NOT NULL , + src_id int NOT NULL , + src_ind_id varchar (64) NOT NULL , + create_time TIMESTAMP NOT NULL , + src_ind_grp varchar (64) NULL +) +; + +CREATE TABLE IndivSourceCode ( + code int NOT NULL , + name varchar (22) NOT NULL , + descrip varchar (255) NULL , + create_time TIMESTAMP NOT NULL , + src_type varchar (10) NULL , + display_order smallint NULL +) +; + +CREATE TABLE Individual ( + ind_id int NOT NULL , + descrip varchar (255) NULL , + create_time TIMESTAMP NOT NULL , + tax_id int NULL , + ind_grp smallint NULL +) +; + +CREATE TABLE OmimVarLocusIdSNP ( + omim_id int NOT NULL , + locus_id int NULL , + omimvar_id char (4) NULL , + locus_symbol char (10) NULL , + var1 char (20) NULL , + aa_position int NULL , + var2 char (20) NULL , + var_class int NOT NULL , + snp_id int NOT NULL +) +; + +CREATE TABLE Pedigree ( + ped_id numeric(7, 0) NOT NULL , + curator varchar (12) NOT NULL , + curator_ped_id varchar (12) NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE PedigreeIndividual ( + ped_id decimal(7, 0) NOT NULL , + ind_id int NOT NULL , + ma_ind_id int NULL , + pa_ind_id int NULL , + sex char (1) NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE PopLine ( + pop_id int NOT NULL , + line_num int NOT NULL , + line varchar (255) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE PopMandLine ( + pop_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE Population ( + pop_id int NOT NULL , + handle varchar (20) NOT NULL , + loc_pop_id varchar (64) NOT NULL , + loc_pop_id_upp varchar (64) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL , + src_id int NULL +) +; + +CREATE TABLE RsMergeArch ( + rsHigh int NULL , + rsLow int NULL , + build_id int NULL , + orien smallint NULL , + create_time TIMESTAMP NOT NULL , + last_updated_time TIMESTAMP NOT NULL , + rsCurrent int NULL , + orien2Current smallint NULL , + comment varchar (255) NULL +) +; + +CREATE TABLE SNP ( + snp_id int NULL , + avg_heterozygosity real NULL , + het_se real NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL , + CpG_code smallint NULL , + tax_id int NULL , + validation_status smallint NULL , + exemplar_subsnp_id int NULL , + univar_id int NULL , + cnt_subsnp int NULL , + map_property smallint NULL +) +; + +CREATE TABLE SNP3D ( + snp_id int NOT NULL , + protein_acc char (50) NOT NULL , + master_gi int NOT NULL , + neighbor_gi int NOT NULL , + aa_position int NOT NULL , + var_res char (1) NOT NULL , + contig_res char (1) NOT NULL , + neighbor_res char (1) NOT NULL , + neighbor_pos int NOT NULL , + var_color int NOT NULL , + var_label int NOT NULL +) +; + +CREATE TABLE SNPAlleleFreq ( + snp_id int NOT NULL , + allele_id int NOT NULL , + chr_cnt float NULL , + freq float NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE SNPAncestralAllele ( + snp_id int NOT NULL , + ancestral_allele_id int NOT NULL , + batch_id int DEFAULT 0 NOT NULL +) +; + +CREATE TABLE SNPGtyFreq ( + snp_id int NOT NULL , + unigty_id int NOT NULL , + ind_cnt float NULL , + freq float NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE SNPHWProb ( + snp_id int NOT NULL , + df smallint NULL , + chisq real NULL , + hwp real NULL , + ind_cnt smallint NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE SNPHistory ( + snp_id int NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NOT NULL , + history_create_time TIMESTAMP NULL , + comment varchar (255) NULL +) +; + +CREATE TABLE SNPPubmed ( + snp_id int NOT NULL , + subsnp_id int NOT NULL , + pubmed_id int NOT NULL , + type varchar (16) NOT NULL , + score int NOT NULL , + upd_date TIMESTAMP NOT NULL +) +; + +CREATE TABLE SNPSubSNPLink ( + subsnp_id int NULL , + snp_id int NULL , + substrand_reversed_flag smallint NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL , + build_id int NULL , + comment varchar (255) NULL +) +; + +CREATE TABLE SNPSubSNPLinkHistory ( + subsnp_id int NULL , + snp_id int NULL , + build_id int NULL , + history_create_time TIMESTAMP NOT NULL , + link_create_time TIMESTAMP NULL , + link_last_updated_time TIMESTAMP NULL , + orien smallint NULL , + build_id_when_history_made int NULL , + comment varchar (255) NULL +) +; + +CREATE TABLE SNPVal ( + batch_id int NOT NULL , + snp_id int NOT NULL +) +; + +CREATE TABLE SNP_HGVS ( + snp_id int NULL , + hgvs_name varchar (256) NULL , + source varchar (8) NOT NULL , + upd_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE SNP_bitfield ( + snp_id int NOT NULL , + ver_code smallint NULL , + link_prop_b1 smallint NULL , + link_prop_b2 smallint NULL , + gene_prop_b1 smallint NULL , + gene_prop_b2 smallint NULL , + map_prop smallint NULL , + freq_prop smallint NULL , + gty_prop smallint NULL , + hapmap_prop smallint NULL , + pheno_prop smallint NULL , + variation_class smallint NOT NULL , + quality_check smallint NULL , + upd_time TIMESTAMP NOT NULL , + encoding bytea NULL +) +; + +CREATE TABLE SubPop ( + batch_id int NOT NULL , + subsnp_id int NOT NULL , + pop_id int NOT NULL , + type char (3) NOT NULL , + samplesize int NOT NULL , + submitted_strand_code smallint NULL , + submitted_rs int NULL , + allele_flag smallint NULL , + ambiguity_status smallint NULL , + sub_heterozygosity real NULL , + est_heterozygosity real NULL , + est_het_se_sq real NULL , + last_updated_time TIMESTAMP DEFAULT NOW() NOT NULL , + observed varchar (1000) NULL , + sub_het_se_sq real NULL , + subpop_id SERIAL NOT NULL +) +; + +CREATE TABLE SubPopAllele ( + batch_id int NOT NULL , + subsnp_id int NOT NULL , + pop_id int NOT NULL , + allele char (1) NOT NULL , + other varchar (255) NULL , + freq real NULL , + cnt_int int NULL , + freq_min real NULL , + freq_max real NULL , + data_src varchar (6) NULL , + type char (3) NULL , + last_updated_time TIMESTAMP NULL , + allele_flag smallint NULL , + cnt real NULL , + allele_id int NULL , + subpop_id int NOT NULL +) +; + +CREATE TABLE SubPopGty ( + subpop_id int NOT NULL , + gty_id int NOT NULL , + gty_str varchar (255) NULL , + cnt real NULL , + freq real NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE SubSNP ( + subsnp_id int NOT NULL , + known_snp_handle varchar (20) NULL , + known_snp_loc_id varchar (64) NULL , + known_snp_loc_id_upp varchar (64) NULL , + batch_id int NOT NULL , + loc_snp_id varchar (64) NULL , + loc_snp_id_upp varchar (64) NULL , + synonym_names varchar (255) NULL , + loc_sts_id varchar (64) NULL , + loc_sts_id_upp varchar (64) NULL , + segregate char (1) NOT NULL , + indiv_homozygosity_detected char (1) NULL , + PCR_confirmed_ind char (1) NULL , + gene_name varchar (64) NULL , + sequence_len int NULL , + samplesize int NULL , + EXPRESSED_SEQUENCE_ind char (1) NULL , + SOMATIC_ind char (1) NULL , + sub_locus_id int NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL , + ancestral_allele varchar (255) NULL , + CpG_code smallint NULL , + variation_id int NULL , + top_or_bot_strand char (1) NULL , + validation_status smallint NULL , + snp_id int NULL , + tax_id int NOT NULL , + chr_id smallint NULL +) +; + +CREATE TABLE SubSNPAcc_ins ( + subsnp_id int NOT NULL , + acc_type_ind char (1) NOT NULL , + acc_part varchar (16) NOT NULL , + acc_ver int NULL +) +; + +CREATE TABLE SubSNPCommLine_ins ( + subsnp_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPHGVS ( + subsnp_id int NOT NULL , + sub_hgvs_c varchar (32) NULL , + sub_hgvs_g varchar (32) 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 , + upd_time TIMESTAMP NULL , + gene_id int NULL +) +; + +CREATE TABLE SubSNPLinkout ( + subsnp_id int NOT NULL , + url_val varchar (255) NOT NULL , + updated_time TIMESTAMP NULL , + link_type varchar (3) DEFAULT 'NA' NOT NULL +) +; + +CREATE TABLE SubSNPMdFailLn ( + subsnp_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPNoVariSeq ( + subsnp_id int NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPOmim ( + subsnp_id int NOT NULL , + omim_id int NOT NULL , + allele_variant_id varchar (32) NULL , + update_time TIMESTAMP NULL , + mutObsCount int NULL +) +; + +CREATE TABLE SubSNPPubmed ( + subsnp_id int NOT NULL , + line_num int NOT NULL , + pubmed_id int NOT NULL , + updated_time TIMESTAMP NULL +) +; + +CREATE TABLE SubSNPSeq3_ins ( + subsnp_id int NOT NULL , + type smallint NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPSeq5_ins ( + subsnp_id int NOT NULL , + type smallint NOT NULL , + line_num smallint NOT NULL , + line varchar (255) NOT NULL +) +; + +CREATE TABLE SubSNPSeqPos ( + subsnp_id int NOT NULL , + contig_acc varchar (20) NOT NULL , + contig_pos int NOT NULL , + chr varchar (2) NULL , + upstream_len int NOT NULL , + downstream_len int NOT NULL , + last_update_time TIMESTAMP NOT NULL , + mrna_acc varchar (24) NULL +) +; + +CREATE TABLE SubSNP_top_or_bot ( + subsnp_id int NOT NULL , + top_or_bot char (1) NULL , + step smallint NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE SubmittedIndividual ( + submitted_ind_id int NOT NULL , + pop_id int NOT NULL , + loc_ind_id_upp varchar (64) NOT NULL , + ind_id int NULL , + create_time TIMESTAMP NOT NULL , + last_updated_time TIMESTAMP NULL , + tax_id int NOT NULL , + loc_ind_alias varchar (64) NULL , + loc_ind_id varchar (64) NULL , + loc_ind_grp varchar (64) NULL , + ploidy smallint DEFAULT 2 NULL +) +; + +CREATE TABLE Synonym ( + subsnp_id int NOT NULL , + type varchar (64) NOT NULL , + name varchar (64) NULL +) +; + +CREATE TABLE b132_ContigInfo_37_1 ( + ctg_id int NOT NULL , + tax_id int NOT NULL , + contig_acc varchar (32) NOT NULL , + contig_ver smallint NULL , + contig_name varchar (32) NOT NULL , + contig_chr varchar (32) NULL , + contig_start int NULL , + contig_end int NULL , + orient smallint NULL , + contig_gi int NULL , + group_term varchar (32) NULL , + group_label varchar (32) NULL , + contig_label varchar (32) NULL , + build_id int NULL , + build_ver int NULL , + last_updated_time TIMESTAMP NOT NULL , + placement_status smallint NOT NULL +) +; + +CREATE TABLE b132_GenomeAssemblyInfo_37_1 ( + assembly varchar (32) NOT NULL , + assembly_type varchar (8) NOT NULL , + rep_order smallint NULL , + comment varchar (255) NULL , + upd_time TIMESTAMP NULL +) +; + +CREATE TABLE b132_MapLinkInfo_37_1 ( + gi int NULL , + accession varchar (127) NULL , + accession_ver int NULL +) +; + +CREATE TABLE b132_MapLink_37_1 ( + snp_type char (2) NOT NULL , + snp_id int NULL , + gi 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 smallint NULL , + build_id int NULL , + process_time TIMESTAMP NULL , + process_status int NULL , + orientation int NULL , + allele varchar (1024) NULL , + aln_quality float NULL , + num_mism int NULL , + num_ins int NULL , + num_del int NULL , + tier int NULL , + ctg_gi int NULL , + ctg_from int NULL , + ctg_to int NULL , + ctg_orient smallint NULL , + source varchar (5) NOT NULL +) +; + +CREATE TABLE b132_ProteinInfo_37_1 ( + prot_gi int NULL , + prot_acc varchar (128) NULL , + prot_ver int NULL +) +; + +CREATE TABLE b132_SNPChrPosOnRef_37_1 ( + snp_id int NULL , + chr varchar (32) NULL , + pos int NULL , + orien int NULL , + neighbor_snp_list int NULL , + isPAR varchar (1) NOT NULL +) +; + +CREATE TABLE b132_SNPContigLoc_37_1 ( + snp_type char (2) NOT NULL , + snp_id int NOT NULL , + ctg_id int NOT NULL , + asn_from int NOT NULL , + asn_to int NOT NULL , + lf_ngbr int NULL , + rf_ngbr int NULL , + lc_ngbr int NOT NULL , + rc_ngbr int NOT NULL , + loc_type smallint NOT NULL , + phys_pos_from int NULL , + snp_bld_id int NOT NULL , + last_updated_time TIMESTAMP NOT NULL , + process_status int NOT NULL , + orientation smallint NULL , + allele varchar (1024) NULL , + loc_sts_uid int NULL , + aln_quality float NULL , + num_mism int NULL , + num_del int NULL , + num_ins int NULL , + tier smallint NULL +) +; + +CREATE TABLE b132_SNPContigLocusId_37_1 ( + snp_id int NULL , + contig_acc varchar (32) NULL , + contig_ver smallint NULL , + asn_from int NULL , + asn_to int NULL , + locus_id int NULL , + locus_symbol varchar (128) NULL , + mrna_acc varchar (128) NULL , + mrna_ver int NULL , + protein_acc varchar (128) NULL , + protein_ver int NULL , + fxn_class int NULL , + reading_frame int NULL , + allele varchar (256) NULL , + residue varchar (1024) 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 , + protRes char (3) NULL , + contig_gi int NULL , + mrna_gi int NULL , + mrna_orien smallint NULL , + cp_mrna_ver int NULL , + cp_mrna_gi int NULL , + verComp varchar (7) NOT NULL +) +; + +CREATE TABLE b132_SNPMapInfo_37_1 ( + snp_type char (2) NOT NULL , + snp_id int NOT NULL , + chr_cnt int NOT NULL , + contig_cnt int NOT NULL , + loc_cnt int NOT NULL , + weight int NOT NULL , + hap_cnt int NOT NULL , + placed_cnt int NOT NULL , + grouped_cnt int NOT NULL , + unplaced_cnt int NOT NULL , + md5 char (32) NULL , + assembly varchar (32) NULL +) +; + +CREATE TABLE dn_IND_batchCount ( + batch_id int NOT NULL , + pop_id int NOT NULL , + ss_cnt int NOT NULL , + rs_cnt int NOT NULL , + ind_cnt int NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE dn_IND_batch_pop ( + batch_id smallint NOT NULL , + pop_id int NOT NULL , + update_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE dn_PopulationIndGrp ( + pop_id int NOT NULL , + ind_grp_name varchar (32) NOT NULL , + ind_grp_code smallint NOT NULL +) +; + +CREATE TABLE dn_batchCount ( + batch_id int NOT NULL , + ss_cnt int NOT NULL , + rs_cnt int NOT NULL , + rs_validated_cnt int NOT NULL , + create_time TIMESTAMP NOT NULL , + pop_cnt int NULL , + ind_cnt int NULL +) +; + +CREATE TABLE dn_handleCount ( + handle varchar (20) NOT NULL , + batch_type char (3) NOT NULL , + ss_cnt int NOT NULL , + rs_cnt int NULL , + rs_validated_cnt int NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE dn_snpFxnCnt ( + build_id int NOT NULL , + fxn_class smallint NULL , + snp_cnt int NOT NULL , + gene_cnt int NOT NULL , + create_time TIMESTAMP NOT NULL , + last_updated_time TIMESTAMP NOT NULL , + tax_id int NOT NULL +) +; + +CREATE TABLE dn_table_rowcount ( + tabname varchar (64) NOT NULL , + row_cnt int NOT NULL , + build_id int NOT NULL , + update_time TIMESTAMP NOT NULL , + rows_in_spaceused int NULL , + reserved_KB_spaceused int NULL , + data_KB_spaceused int NULL , + index_size_KB_spaceused int NULL , + unused_KB_spaceused int NULL +) +; + diff --git a/schema/human_9606_schema/human_gty1_constraint.sql b/schema/human_9606_schema/human_gty1_constraint.sql new file mode 100644 index 0000000..f2a495a --- /dev/null +++ b/schema/human_9606_schema/human_gty1_constraint.sql @@ -0,0 +1,78 @@ +ALTER TABLE [SubInd_ch1] ADD + CONSTRAINT [pk_ch_t_SubInd_ch1] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [submitted_ind_id], + [batch_id], + [chr] + ) +GO + + +ALTER TABLE [SubInd_ch2] ADD + CONSTRAINT [pk_ch_t_SubInd_ch2] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [submitted_ind_id], + [batch_id], + [chr] + ) +GO + + +ALTER TABLE [SubInd_ch3] ADD + CONSTRAINT [pk_ch_t_SubInd_ch3] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [submitted_ind_id], + [batch_id], + [chr] + ) +GO + + +ALTER TABLE [SubInd_ch4] ADD + CONSTRAINT [pk_ch_t_SubInd_ch4] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [submitted_ind_id], + [batch_id], + [chr] + ) +GO + + +ALTER TABLE [SubInd_ch5] ADD + CONSTRAINT [pk_ch_t_SubInd_ch5] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [submitted_ind_id], + [batch_id], + [chr] + ) +GO + + +ALTER TABLE [SubInd_ch6] ADD + CONSTRAINT [pk_ch_t_SubInd_ch6] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [submitted_ind_id], + [batch_id], + [chr] + ) +GO + + +ALTER TABLE [SubInd_ch7] ADD + CONSTRAINT [pk_ch_t_SubInd_ch7] PRIMARY KEY CLUSTERED + ( + [subsnp_id], + [submitted_ind_id], + [batch_id], + [chr] + ) +GO + + + diff --git a/schema/human_9606_schema/human_gty1_constraint_postgresql.sql b/schema/human_9606_schema/human_gty1_constraint_postgresql.sql new file mode 100644 index 0000000..083750b --- /dev/null +++ b/schema/human_9606_schema/human_gty1_constraint_postgresql.sql @@ -0,0 +1,71 @@ +ALTER TABLE SubInd_ch1 ADD + CONSTRAINT pk_ch_t_SubInd_ch1 PRIMARY KEY + ( + subsnp_id, + submitted_ind_id, + batch_id, + chr + ) +; + +ALTER TABLE SubInd_ch2 ADD + CONSTRAINT pk_ch_t_SubInd_ch2 PRIMARY KEY + ( + subsnp_id, + submitted_ind_id, + batch_id, + chr + ) +; + +ALTER TABLE SubInd_ch3 ADD + CONSTRAINT pk_ch_t_SubInd_ch3 PRIMARY KEY + ( + subsnp_id, + submitted_ind_id, + batch_id, + chr + ) +; + +ALTER TABLE SubInd_ch4 ADD + CONSTRAINT pk_ch_t_SubInd_ch4 PRIMARY KEY + ( + subsnp_id, + submitted_ind_id, + batch_id, + chr + ) +; + +ALTER TABLE SubInd_ch5 ADD + CONSTRAINT pk_ch_t_SubInd_ch5 PRIMARY KEY + ( + subsnp_id, + submitted_ind_id, + batch_id, + chr + ) +; + +ALTER TABLE SubInd_ch6 ADD + CONSTRAINT pk_ch_t_SubInd_ch6 PRIMARY KEY + ( + subsnp_id, + submitted_ind_id, + batch_id, + chr + ) +; + +ALTER TABLE SubInd_ch7 ADD + CONSTRAINT pk_ch_t_SubInd_ch7 PRIMARY KEY + ( + subsnp_id, + submitted_ind_id, + batch_id, + chr + ) +; + + diff --git a/schema/human_9606_schema/human_gty1_index.sql b/schema/human_9606_schema/human_gty1_index.sql new file mode 100644 index 0000000..5dd05bf --- /dev/null +++ b/schema/human_9606_schema/human_gty1_index.sql @@ -0,0 +1,53 @@ + CREATE INDEX [i_bs] ON [SubInd_ch1]([batch_id], [subsnp_id]) +GO + + CREATE INDEX [i_ind_ss] ON [SubInd_ch1]([submitted_ind_id], [subsnp_id], [gty_id]) +GO + + + CREATE INDEX [i_bs] ON [SubInd_ch2]([batch_id], [subsnp_id]) +GO + + CREATE INDEX [i_ind_ss] ON [SubInd_ch2]([submitted_ind_id], [subsnp_id], [gty_id]) +GO + + + CREATE INDEX [i_bs] ON [SubInd_ch3]([batch_id], [subsnp_id]) +GO + + CREATE INDEX [i_ind_ss] ON [SubInd_ch3]([submitted_ind_id], [subsnp_id], [gty_id]) +GO + + + CREATE INDEX [i_bs] ON [SubInd_ch4]([batch_id], [subsnp_id]) +GO + + CREATE INDEX [i_ind_ss] ON [SubInd_ch4]([submitted_ind_id], [subsnp_id], [gty_id]) +GO + + + CREATE INDEX [i_bs] ON [SubInd_ch5]([batch_id], [subsnp_id]) +GO + + CREATE INDEX [i_ind_ss] ON [SubInd_ch5]([submitted_ind_id], [subsnp_id], [gty_id]) +GO + + + CREATE INDEX [i_bs] ON [SubInd_ch6]([batch_id], [subsnp_id]) +GO + + CREATE INDEX [i_ind_ss] ON [SubInd_ch6]([submitted_ind_id], [subsnp_id], [gty_id]) +GO + + + CREATE INDEX [i_bs] ON [SubInd_ch7]([batch_id], [subsnp_id]) +GO + + CREATE INDEX [i_ind_ss] ON [SubInd_ch7]([submitted_ind_id], [subsnp_id], [gty_id]) +GO + + + CREATE CLUSTERED INDEX [i_b_size] ON [dn_table_rowcount]([build_id] DESC , [reserved_KB_spaceused] DESC ) +GO + + diff --git a/schema/human_9606_schema/human_gty1_index_postgresql.sql b/schema/human_9606_schema/human_gty1_index_postgresql.sql new file mode 100644 index 0000000..0c98d90 --- /dev/null +++ b/schema/human_9606_schema/human_gty1_index_postgresql.sql @@ -0,0 +1,49 @@ + CREATE INDEX ON SubInd_ch1(batch_id, subsnp_id) +; + CREATE INDEX ON SubInd_ch1(submitted_ind_id, subsnp_id, gty_id) +; +CREATE INDEX ON SubInd_ch1(subsnp_id); +CREATE INDEX ON SubInd_ch2(subsnp_id); +CREATE INDEX ON SubInd_ch3(subsnp_id); +CREATE INDEX ON SubInd_ch4(subsnp_id); +CREATE INDEX ON SubInd_ch5(subsnp_id); +CREATE INDEX ON SubInd_ch6(subsnp_id); +CREATE INDEX ON SubInd_ch7(subsnp_id); +CREATE INDEX ON SubInd_ch8(subsnp_id); +CREATE INDEX ON SubInd_ch9(subsnp_id); + + + CREATE INDEX ON SubInd_ch2(batch_id, subsnp_id) +; + CREATE INDEX ON SubInd_ch2(submitted_ind_id, subsnp_id, gty_id) +; + + + CREATE INDEX ON SubInd_ch3(batch_id, subsnp_id) +; + CREATE INDEX ON SubInd_ch3(submitted_ind_id, subsnp_id, gty_id) +; + + CREATE INDEX ON SubInd_ch4(batch_id, subsnp_id) +; + CREATE INDEX ON SubInd_ch4(submitted_ind_id, subsnp_id, gty_id) +; + + CREATE INDEX ON SubInd_ch5(batch_id, subsnp_id) +; + CREATE INDEX ON SubInd_ch5(submitted_ind_id, subsnp_id, gty_id) +; + + CREATE INDEX ON SubInd_ch6(batch_id, subsnp_id) +; + CREATE INDEX ON SubInd_ch6(submitted_ind_id, subsnp_id, gty_id) +; + + CREATE INDEX ON SubInd_ch7(batch_id, subsnp_id) +; + CREATE INDEX ON SubInd_ch7(submitted_ind_id, subsnp_id, gty_id) +; + + CREATE INDEX ON dn_table_rowcount(build_id DESC , reserved_KB_spaceused DESC ) +; + diff --git a/schema/human_9606_schema/human_gty1_indexes_creation.pl b/schema/human_9606_schema/human_gty1_indexes_creation.pl new file mode 100755 index 0000000..c4f0340 --- /dev/null +++ b/schema/human_9606_schema/human_gty1_indexes_creation.pl @@ -0,0 +1,67 @@ +#!/usr/bin/perl + +use warnings; +use strict; +my @chr = (1..22, + qw(AltOnly MT NewSs NotOn PAR Un X Y) + ); +my %chr; +@chr{@chr} = @chr; +$chr{NewSs} = 'ss'; + +my %args; +@args{map {my $a = $_; lc($a)} @ARGV} = 1; + +if ($args{drop}) { + for my $chr (@chr) { + print "DROP TABLE SubInd_ch${chr};\n"; + } + print "DROP TABLE SubInd;\n"; +} +if ($args{create}) { + print <