From: Don Armstrong Date: Tue, 6 Dec 2011 23:57:04 +0000 (-0800) Subject: add actual version of tables used X-Git-Url: https://git.donarmstrong.com/?p=dbsnp.git;a=commitdiff_plain;h=6b2ac925d51c30ffa1811f6d5e44fba29a94ff4b add actual version of tables used --- diff --git a/schema/shared_schema/dbSNP_main_constraint_postgresql.sql b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql index b5ee5d2..802b551 100644 --- a/schema/shared_schema/dbSNP_main_constraint_postgresql.sql +++ b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql @@ -1,352 +1,351 @@ -ALTER TABLE "Allele" ADD - CONSTRAINT "pk_Allele" PRIMARY KEY - ( - "allele_id" - ) -; - -ALTER TABLE "AlleleFlagCode" ADD - CONSTRAINT "pk_AlleleFlagCode" PRIMARY KEY - ( - "code" - ) -; - -ALTER TABLE "AlleleMotif" ADD - CONSTRAINT "pk_AlleleMotif" PRIMARY KEY - ( - "allele_id", - "motif_order" - ) -; - -ALTER TABLE "AllocIds" ADD - CONSTRAINT "pk_AllocIds" PRIMARY KEY - ( - "name" - ) -; - -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" ADD - CONSTRAINT "DF__GenomeBui__inser__15E52B55" DEFAULT (getdate()) FOR "insert_time", - CONSTRAINT "DF__GenomeBui__is_ma__18C19800" DEFAULT (1) FOR "is_major", - CONSTRAINT "DF__GenomeBui__ncbi___23FE4082" DEFAULT (0) FOR "ncbi_genome_tax_id" -; - -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" - ) -; - -ALTER TABLE "LoadHistory" ADD - CONSTRAINT "pk_LoadHistory" PRIMARY KEY - ( - "build_id" - ) -; - -ALTER TABLE "LocTypeCode" ADD - CONSTRAINT "DF__LocTypeCo__creat__75CD617E" DEFAULT (getdate()) FOR "create_time", - CONSTRAINT "pk_LocTypeCode" PRIMARY KEY - ( - "code" - ) -; - -ALTER TABLE "MapLinkCode" ADD - CONSTRAINT "pk_MapLinkCode" PRIMARY KEY - ( - "which_code", - "code" - ) -; - -ALTER TABLE "Method" ADD - CONSTRAINT "pk_Method_mid" PRIMARY KEY - ( - "method_id" - ) -; - -ALTER TABLE "MethodClass" ADD - CONSTRAINT "pk_MethodClass" PRIMARY KEY - ( - "meth_class_id" - ) -; - -ALTER TABLE "MethodLine" ADD - CONSTRAINT "pk_MethodLine" PRIMARY KEY - ( - "method_id", - "line_num" - ) -; - -ALTER TABLE "Moltype" ADD - CONSTRAINT "pk_Moltype" PRIMARY KEY - ( - "code" - ) -; - -ALTER TABLE "Motif" ADD - CONSTRAINT "pk_Motif" PRIMARY KEY - ( - "motif_id" - ) -; - -ALTER TABLE "ObsGenotype" ADD - CONSTRAINT "pk_ObsGenotype" PRIMARY KEY - ( - "gty_id" - ) -; - -ALTER TABLE "ObsVariation" ADD - CONSTRAINT "DF__ObsVariat__creat__3BB5CE82" DEFAULT (getdate()) FOR "create_time", - CONSTRAINT "DF__ObsVariat__last___3CA9F2BB" DEFAULT (getdate()) FOR "last_updated_time", - CONSTRAINT "pk_ObsVariation" PRIMARY KEY - ( - "var_id" - ) -; - -ALTER TABLE "OrgDbStatus" ADD - CONSTRAINT "pk_org_db_status" PRIMARY KEY - ( - "database_name" - ) -; - -ALTER TABLE "OrganismTax" ADD - CONSTRAINT "pk_organism" PRIMARY KEY - ( - "tax_id" - ) -; - -ALTER TABLE "PopClass" ADD - CONSTRAINT "pk_PopClass" PRIMARY KEY - ( - "pop_id", - "pop_class_id" - ) -; - -ALTER TABLE "PopClassCode" ADD - CONSTRAINT "PK__PopClassCode__270595B6" PRIMARY KEY - ( - "pop_class_id" - ) -; - -ALTER TABLE "Publication" ADD - CONSTRAINT "pk_Publication" PRIMARY KEY - ( - "pub_id" - ) -; - -ALTER TABLE "SNPGlossary" ADD - CONSTRAINT "DF__SNPGlossa__last___4050666D" DEFAULT (getdate()) FOR "last_updated", - CONSTRAINT "pk_SNPGlossary" PRIMARY KEY - ( - "term" - ) -; - -ALTER TABLE "SNP_tax_id" ADD - CONSTRAINT "DF__SNP_tax_i__statu__31583BA0" DEFAULT ('C') FOR "status", - CONSTRAINT "pk_SNP_tax_id" PRIMARY KEY - ( - "snp_id" - ) -; - -ALTER TABLE "SnpChrCode" ADD - CONSTRAINT "pk_SnpChrCode" PRIMARY KEY - ( - "code" - ) -; - -ALTER TABLE "SnpClassCode" ADD - CONSTRAINT "pk_SnpClassCode" PRIMARY KEY - ( - "code" - ) -; - -ALTER TABLE "SnpFunctionCode" ADD - CONSTRAINT "pk_SnpFunctionCode" PRIMARY KEY - ( - "code" - ) -; - -ALTER TABLE "SnpValidationCode" ADD - CONSTRAINT "pk_SnpValidationCode" PRIMARY KEY - ( - "code" - ) -; - -ALTER TABLE "StrandCode" ADD - CONSTRAINT "pk_StrandCode" PRIMARY KEY - ( - "code" - ) -; - - - -ALTER TABLE "Submitter" ADD - CONSTRAINT "DF_Submitter_create_time" DEFAULT (getdate()) FOR "create_time", - CONSTRAINT "pk_Submitter" PRIMARY KEY - ( - "handle" - ) -; - -ALTER TABLE "TemplateType" ADD - CONSTRAINT "pk_TemplateType" PRIMARY KEY - ( - "temp_type_id" - ) -; - -ALTER TABLE "UniGty" ADD - CONSTRAINT "pk_UniGty" PRIMARY KEY - ( - "unigty_id" - ) -; - -ALTER TABLE "UniVariAllele" ADD - CONSTRAINT "pk_UniVariAllele" PRIMARY KEY - ( - "univar_id", - "allele_id" - ) -; - -ALTER TABLE "UniVariation" ADD - CONSTRAINT "pk_UniVariation" PRIMARY KEY - ( - "univar_id" - ) -; - -ALTER TABLE "UniVariationSrcCode" ADD - CONSTRAINT "pk_UniVariationSrcCode" PRIMARY KEY - ( - "code" - ) -; - -ALTER TABLE "VariAllele" ADD - CONSTRAINT "pk_VariAllele" PRIMARY KEY - ( - "var_id", - "allele_id" - ) -; - -ALTER TABLE "db_DataDictionaryNew" ADD - CONSTRAINT "DF__db_DataDi__updat__5BB889C0" DEFAULT (getdate()) FOR "update_time" -; - -ALTER TABLE "db_ftp_table_list" ADD - CONSTRAINT "DF__db_ftp_ta__creat__27F9B9EF" DEFAULT (getdate()) FOR "create_time", - CONSTRAINT "pk_db_ftp_table_list" PRIMARY KEY - ( - "table_name" - ) -; - - -ALTER TABLE "dn_Allele_rev" ADD - CONSTRAINT "pk_dn_Allele_rev" PRIMARY KEY - ( - "allele_id", - "rev_flag" - ) -; - -ALTER TABLE "dn_Motif_rev" ADD - CONSTRAINT "pk_dn_Motif_rev" PRIMARY KEY - ( - "motif_id", - "rev_flag" - ) -; - -ALTER TABLE "dn_UniGty_allele" ADD - CONSTRAINT "pk_dn_UniGty_allele" PRIMARY KEY - ( - "unigty_id", - "chr_num" - ) -; - -ALTER TABLE "dn_UniGty_rev" ADD - CONSTRAINT "pk_dn_Gty_rev" PRIMARY KEY - ( - "unigty_id", - "rev_flag" - ) -; - -ALTER TABLE "dn_UniVariation_rev" ADD - CONSTRAINT "pk_dn_UniVariation_rev" PRIMARY KEY - ( - "univar_id", - "rev_flag" - ) -; - - - -ALTER TABLE "dn_summary" ADD - CONSTRAINT "DF__dn_summar__creat__383A4359" DEFAULT (getdate()) FOR "create_time", - CONSTRAINT "DF__dn_summar__last___392E6792" DEFAULT (getdate()) FOR "last_updated_time", - PRIMARY KEY - ( - "tax_id", - "build_id", - "type" - ) -; - +ALTER TABLE Allele ADD + CONSTRAINT pk_Allele PRIMARY KEY + ( + allele_id + ) +; + +ALTER TABLE AlleleFlagCode ADD + CONSTRAINT pk_AlleleFlagCode PRIMARY KEY + ( + code + ) +; + +ALTER TABLE AlleleMotif ADD + CONSTRAINT pk_AlleleMotif PRIMARY KEY + ( + allele_id, + motif_order + ) +; + +ALTER TABLE AllocIds ADD + CONSTRAINT pk_AllocIds PRIMARY KEY + ( + name + ) +; + +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 ADD +-- CONSTRAINT DF__GenomeBui__inser__15E52B55 DEFAULT (getdate()) FOR insert_time, +-- CONSTRAINT DF__GenomeBui__is_ma__18C19800 DEFAULT (1) FOR is_major, +-- CONSTRAINT DF__GenomeBui__ncbi___23FE4082 DEFAULT (0) FOR ncbi_genome_tax_id +-- ; + +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 + ) +; + +ALTER TABLE LoadHistory ADD + CONSTRAINT pk_LoadHistory PRIMARY KEY + ( + build_id + ) +; + +ALTER TABLE LocTypeCode ADD +-- CONSTRAINT DF__LocTypeCo__creat__75CD617E DEFAULT (getdate()) FOR create_time, + CONSTRAINT pk_LocTypeCode PRIMARY KEY + ( + code + ) +; + +ALTER TABLE MapLinkCode ADD + CONSTRAINT pk_MapLinkCode PRIMARY KEY + ( + which_code, + code + ) +; + +ALTER TABLE Method ADD + CONSTRAINT pk_Method_mid PRIMARY KEY + ( + method_id + ) +; + +ALTER TABLE MethodClass ADD + CONSTRAINT pk_MethodClass PRIMARY KEY + ( + meth_class_id + ) +; + +ALTER TABLE MethodLine ADD + CONSTRAINT pk_MethodLine PRIMARY KEY + ( + method_id, + line_num + ) +; + +ALTER TABLE Moltype ADD + CONSTRAINT pk_Moltype PRIMARY KEY + ( + code + ) +; + +ALTER TABLE Motif ADD + CONSTRAINT pk_Motif PRIMARY KEY + ( + motif_id + ) +; + +ALTER TABLE ObsGenotype ADD + CONSTRAINT pk_ObsGenotype PRIMARY KEY + ( + gty_id + ) +; + +ALTER TABLE ObsVariation ADD +-- CONSTRAINT DF__ObsVariat__creat__3BB5CE82 DEFAULT (getdate()) FOR create_time, +-- CONSTRAINT DF__ObsVariat__last___3CA9F2BB DEFAULT (getdate()) FOR last_updated_time, + CONSTRAINT pk_ObsVariation PRIMARY KEY + ( + var_id + ) +; + +ALTER TABLE OrgDbStatus ADD + CONSTRAINT pk_org_db_status PRIMARY KEY + ( + database_name + ) +; + +ALTER TABLE OrganismTax ADD + CONSTRAINT pk_organism PRIMARY KEY + ( + tax_id + ) +; + +ALTER TABLE PopClass ADD + CONSTRAINT pk_PopClass PRIMARY KEY + ( + pop_id, + pop_class_id + ) +; + +ALTER TABLE PopClassCode ADD + CONSTRAINT PK__PopClassCode__270595B6 PRIMARY KEY + ( + pop_class_id + ) +; + +ALTER TABLE Publication ADD + CONSTRAINT pk_Publication PRIMARY KEY + ( + pub_id + ) +; + +ALTER TABLE SNPGlossary ADD +-- CONSTRAINT DF__SNPGlossa__last___4050666D DEFAULT (getdate()) FOR last_updated, + CONSTRAINT pk_SNPGlossary PRIMARY KEY + ( + term + ) +; + +ALTER TABLE SNP_tax_id ADD +-- CONSTRAINT DF__SNP_tax_i__statu__31583BA0 DEFAULT ('C') FOR status, + CONSTRAINT pk_SNP_tax_id PRIMARY KEY + ( + snp_id + ) +; + +ALTER TABLE SnpChrCode ADD + CONSTRAINT pk_SnpChrCode PRIMARY KEY + ( + code + ) +; + +ALTER TABLE SnpClassCode ADD + CONSTRAINT pk_SnpClassCode PRIMARY KEY + ( + code + ) +; + +ALTER TABLE SnpFunctionCode ADD + CONSTRAINT pk_SnpFunctionCode PRIMARY KEY + ( + code + ) +; + +ALTER TABLE SnpValidationCode ADD + CONSTRAINT pk_SnpValidationCode PRIMARY KEY + ( + code + ) +; + +ALTER TABLE StrandCode ADD + CONSTRAINT pk_StrandCode PRIMARY KEY + ( + code + ) +; + + + +ALTER TABLE Submitter ADD +-- CONSTRAINT DF_Submitter_create_time DEFAULT (getdate()) FOR create_time, + CONSTRAINT pk_Submitter PRIMARY KEY + ( + handle + ) +; + +ALTER TABLE TemplateType ADD + CONSTRAINT pk_TemplateType PRIMARY KEY + ( + temp_type_id + ) +; + +ALTER TABLE UniGty ADD + CONSTRAINT pk_UniGty PRIMARY KEY + ( + unigty_id + ) +; + +ALTER TABLE UniVariAllele ADD + CONSTRAINT pk_UniVariAllele PRIMARY KEY + ( + univar_id, + allele_id + ) +; + +ALTER TABLE UniVariation ADD + CONSTRAINT pk_UniVariation PRIMARY KEY + ( + univar_id + ) +; + +ALTER TABLE UniVariationSrcCode ADD + CONSTRAINT pk_UniVariationSrcCode PRIMARY KEY + ( + code + ) +; + +ALTER TABLE VariAllele ADD + CONSTRAINT pk_VariAllele PRIMARY KEY + ( + var_id, + allele_id + ) +; + +ALTER TABLE db_ftp_table_list ADD +-- CONSTRAINT DF__db_ftp_ta__creat__27F9B9EF DEFAULT (getdate()) FOR create_time, + CONSTRAINT pk_db_ftp_table_list PRIMARY KEY + ( + table_name + ) +; + +-- ALTER TABLE db_map_table_name ADD +-- CONSTRAINT DF__db_map_ta__inNon__206E7217 DEFAULT ('N') FOR inNonHuman +-- ; + +ALTER TABLE dn_Allele_rev ADD + CONSTRAINT pk_dn_Allele_rev PRIMARY KEY + ( + allele_id, + rev_flag + ) +; + +ALTER TABLE dn_Motif_rev ADD + CONSTRAINT pk_dn_Motif_rev PRIMARY KEY + ( + motif_id, + rev_flag + ) +; + +ALTER TABLE dn_UniGty_allele ADD + CONSTRAINT pk_dn_UniGty_allele PRIMARY KEY + ( + unigty_id, + chr_num + ) +; + +ALTER TABLE dn_UniGty_rev ADD + CONSTRAINT pk_dn_Gty_rev PRIMARY KEY + ( + unigty_id, + rev_flag + ) +; + +ALTER TABLE dn_UniVariation_rev ADD + CONSTRAINT pk_dn_UniVariation_rev PRIMARY KEY + ( + univar_id, + rev_flag + ) +; + + + +ALTER TABLE dn_summary ADD +-- CONSTRAINT DF__dn_summar__creat__383A4359 DEFAULT (getdate()) FOR create_time, +-- CONSTRAINT DF__dn_summar__last___392E6792 DEFAULT (getdate()) FOR last_updated_time, + PRIMARY KEY + ( + tax_id, + build_id, + type + ) +; + diff --git a/schema/shared_schema/dbSNP_main_index_postgresql.sql b/schema/shared_schema/dbSNP_main_index_postgresql.sql index e503a6a..0bca28e 100644 --- a/schema/shared_schema/dbSNP_main_index_postgresql.sql +++ b/schema/shared_schema/dbSNP_main_index_postgresql.sql @@ -1,129 +1,126 @@ - CREATE UNIQUE INDEX ON "Allele"("allele") -; - CREATE INDEX ON "Allele"("rev_allele_id") -; - - - CREATE INDEX ON "AlleleMotif"("motif_id") -; - - CREATE UNIQUE INDEX ON "AllocIds"("name") -; - - CREATE INDEX ON "Author"("author") -; - - CREATE UNIQUE INDEX ON "Batch_tax_id"("batch_id") -; - - - - - - - CREATE INDEX ON "GtyAllele"("unigty_id") -; - CREATE INDEX ON "GtyAllele"("gty_id") -; - CREATE INDEX ON "GtyAllele"("gty_id") -; - - - CREATE INDEX ON "LoadHistory"("loaddate") -; - - - - CREATE UNIQUE INDEX ON "Method"("handle", "loc_method_id") -; - - - - - CREATE UNIQUE INDEX ON "Motif"("motif") -; - - CREATE INDEX ON "ObsGenotype"("obs") -; - CREATE INDEX ON "ObsGenotype"("obs_upp_fix") -; - CREATE INDEX ON "ObsGenotype"("gty_id") -; - - CREATE UNIQUE INDEX ON "ObsVariation"("pattern") -; - CREATE INDEX ON "ObsVariation"("univar_id") -; - - - CREATE UNIQUE INDEX ON "OrganismTax"("organism") -; - CREATE INDEX ON "OrganismTax"("tax_id") -; - CREATE INDEX ON "OrganismTax"("species_tax_id") -; - CREATE INDEX ON "OrganismTax"("database_name") -; - - - - CREATE UNIQUE INDEX ON "Publication"("handle", "title") -; - - - CREATE INDEX ON "SNP_tax_id"("tax_id", "snp_id") -; - - - - - CREATE UNIQUE INDEX ON "SnpValidationCode"("code") -; - - CREATE INDEX ON "StrandCode"("abbrev", "rs_to_ss_orien", "code") -; - - CREATE UNIQUE INDEX ON "SubSNPDelComm"("comment_id") -; - - - CREATE UNIQUE INDEX ON "Submitter"("handle") -; - CREATE INDEX ON "Submitter"("name") -; - - - CREATE UNIQUE INDEX ON "UniGty"("gty_str") -; - CREATE UNIQUE INDEX ON "UniGty"("allele_id_1", "allele_id_2") -; - CREATE INDEX ON "UniGty"("allele_id_2") -; - - CREATE INDEX ON "UniVariAllele"("allele_id") -; - - CREATE UNIQUE INDEX ON "UniVariation"("var_str") -; - CREATE INDEX ON "UniVariation"("univar_id", "subsnp_class") -; - - - CREATE INDEX ON "VariAllele"("allele_id") -; - - CREATE UNIQUE INDEX ON "db_DataDictionaryNew"("tab", "col_order") -; - - - - - - - - - - CREATE UNIQUE INDEX ON "dn_gty2unigty_trueSNP"("gty_id", "rev_flag") -; - - + CREATE UNIQUE INDEX ON Allele(allele) +; + CREATE INDEX ON Allele(rev_allele_id) +; + + + CREATE INDEX ON AlleleMotif(motif_id) +; + + CREATE UNIQUE INDEX ON AllocIds(name) +; + + CREATE INDEX ON Author(author) +; + + CREATE UNIQUE INDEX ON Batch_tax_id(batch_id) +; + + + + + + + CREATE INDEX ON GtyAllele(unigty_id) +; + CREATE INDEX ON GtyAllele(gty_id) +; + CREATE INDEX ON GtyAllele(gty_id) +; + + + CREATE INDEX ON LoadHistory(loaddate) +; + + + + CREATE UNIQUE INDEX ON Method(handle, loc_method_id) +; + + + + + CREATE UNIQUE INDEX ON Motif(motif) +; + + CREATE INDEX ON ObsGenotype(obs) +; + CREATE INDEX ON ObsGenotype(obs_upp_fix) +; + CREATE INDEX ON ObsGenotype(gty_id) +; + + CREATE UNIQUE INDEX ON ObsVariation(pattern) +; + CREATE INDEX ON ObsVariation(univar_id) +; + + + CREATE UNIQUE INDEX ON OrganismTax(organism) +; + CREATE INDEX ON OrganismTax(tax_id) +; + CREATE INDEX ON OrganismTax(species_tax_id) +; + CREATE INDEX ON OrganismTax(database_name) +; + + + + CREATE UNIQUE INDEX ON Publication(handle, title) +; + + + CREATE INDEX ON SNP_tax_id(tax_id, snp_id) +; + + + + + CREATE UNIQUE INDEX ON SnpValidationCode(code) +; + + CREATE INDEX ON StrandCode(abbrev, rs_to_ss_orien, code) +; + + CREATE UNIQUE INDEX ON SubSNPDelComm(comment_id) +; + + + CREATE UNIQUE INDEX ON Submitter(handle) +; + CREATE INDEX ON Submitter(name) +; + + + CREATE UNIQUE INDEX ON UniGty(gty_str) +; + CREATE UNIQUE INDEX ON UniGty(allele_id_1, allele_id_2) +; + CREATE INDEX ON UniGty(allele_id_2) +; + + CREATE INDEX ON UniVariAllele(allele_id) +; + + CREATE UNIQUE INDEX ON UniVariation(var_str) +; + CREATE INDEX ON UniVariation(univar_id, subsnp_class) +; + + + CREATE INDEX ON VariAllele(allele_id) +; + + + + + + + + + + CREATE UNIQUE INDEX ON dn_gty2unigty_trueSNP(gty_id, rev_flag) +; + + diff --git a/schema/shared_schema/dbSNP_main_table_postgresql.sql b/schema/shared_schema/dbSNP_main_table_postgresql.sql index 497f9d9..24295b0 100644 --- a/schema/shared_schema/dbSNP_main_table_postgresql.sql +++ b/schema/shared_schema/dbSNP_main_table_postgresql.sql @@ -1,527 +1,520 @@ -CREATE TABLE "Allele" ( - "allele_id" "int" NOT NULL , - "allele" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL , - "rev_allele_id" "int" NULL , - "src" "varchar" (10) NULL , - "last_updated_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "AlleleFlagCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (12) NOT NULL , - "descrip" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "AlleleMotif" ( - "allele_id" "int" NOT NULL , - "motif_order" "int" NOT NULL , - "motif_id" "int" NOT NULL , - "repeat_cnt" "real" NOT NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "AllocIds" ( - "name" "varchar" (30) NOT NULL , - "id" "int" NOT NULL , - "cycle" "int" NULL , - "create_time" "TIMESTAMP" NULL , - "last_updated_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "Author" ( - "pub_id" "int" NOT NULL , - "position" "smallint" NOT NULL , - "author" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NULL , - "last_updated_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "Batch_tax_id" ( - "batch_id" "int" NOT NULL , - "tax_id" "int" NOT NULL , - "update_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "ChiSqPValueLookUp" ( - "df" "smallint" NOT NULL , - "chisq_from" "float" NOT NULL , - "chisq_to" "float" NOT NULL , - "pvalue_upper_bound" "float" NOT NULL -) -; - -CREATE TABLE "CpGCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (12) NOT NULL , - "descrip" "varchar" (255) NOT NULL -) -; - -CREATE TABLE "GenBankDivisionCode" ( - "code" "varchar" (6) NOT NULL , - "abbrev" "varchar" (40) NOT NULL , - "descrip" "varchar" (255) NOT NULL , - "display_order" "smallint" NOT NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "GenderCode" ( - "code" "char" (1) NOT NULL , - "gender" "varchar" (6) NOT NULL -) -; - -CREATE TABLE "GenomeBuildInfo" ( - "snp_build_id" "int" NOT NULL , - "tax_id" "int" NOT NULL , - "database_name" "varchar" (32) NOT NULL , - "ncbi_build_id" "int" NOT NULL , - "ncbi_build_ver" "smallint" NOT NULL , - "insert_time" "TIMESTAMP" NOT NULL , - "is_major" "smallint" NOT NULL , - "ncbi_genome_tax_id" "int" NOT NULL , - "ref_assembly" "varchar" (255) NULL -) -; - -CREATE TABLE "GtyAllele" ( - "gty_id" "int" NOT NULL , - "rev_flag" "bit" NOT NULL , - "chr_num" "smallint" NOT NULL , - "fwd_allele_id" "int" NOT NULL , - "unigty_id" "int" NULL , - "create_time" "TIMESTAMP" NOT NULL , - "last_updated_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "IUPACna" ( - "allele" "varchar" (1) NOT NULL , - "meaning" "varchar" (10) NOT NULL , - "bitcode" "varchar" (4) NOT NULL , - "value" "smallint" NOT NULL , - "rev_base" "char" (1) NULL -) -; - -CREATE TABLE "LoadHistory" ( - "build_id" "int" NOT NULL , - "loaddate" "TIMESTAMP" NOT NULL , - "status" "char" (30) NOT NULL , - "comments" "varchar" (255) NULL , - "script" "varchar" (255) NULL , - "ftp_done_date" "TIMESTAMP" NULL , - "entrez_done_date" "TIMESTAMP" NULL , - "blastdb_done_date" "TIMESTAMP" NULL , - "web_date" "TIMESTAMP" NULL -) -; - -CREATE TABLE "LocTypeCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (12) NOT NULL , - "descrip" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "MapLinkCode" ( - "which_code" "varchar" (20) NOT NULL , - "code" "smallint" NOT NULL , - "abbr" "varchar" (10) NULL , - "note" "varchar" (255) NULL , - "create_time" "TIMESTAMP" NULL , - "last_updated_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "Method" ( - "method_id" "int" NOT NULL , - "handle" "varchar" (20) NOT NULL , - "loc_method_id" "varchar" (64) NOT NULL , - "loc_method_id_upp" "varchar" (64) NOT NULL , - "method_class" "smallint" NULL , - "template_type" "smallint" NULL , - "seq_both_strands" "varchar" (3) NULL , - "mult_pcr_amplification" "varchar" (3) NULL , - "mult_clones_tested" "varchar" (3) NULL , - "create_time" "TIMESTAMP" NULL , - "last_updated_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "MethodClass" ( - "meth_class_id" "smallint" NOT NULL , - "name" "varchar" (64) NOT NULL , - "last_updated_time" "TIMESTAMP" NOT NULL , - "validation_status" "smallint" NOT NULL -) -; - -CREATE TABLE "MethodLine" ( - "method_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 "Moltype" ( - "code" "smallint" NOT NULL , - "moltype" "varchar" (10) NOT NULL , - "descrip" "varchar" (255) NOT NULL -) -; - -CREATE TABLE "Motif" ( - "motif_id" "int" NOT NULL , - "motif" "varchar" (253) NOT NULL , - "rev_motif_id" "int" NULL , - "last_updated_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "ObsGenotype" ( - "gty_id" "int" NOT NULL , - "obs" "varchar" (512) NOT NULL , - "obs_upp_fix" "varchar" (512) NOT NULL , - "last_updated_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "ObsVariation" ( - "var_id" "int" NOT NULL , - "pattern" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL , - "last_updated_time" "TIMESTAMP" NULL , - "univar_id" "int" NULL , - "var_flag" "smallint" NULL -) -; - -CREATE TABLE "OrgDbStatus" ( - "database_name" "varchar" (32) NOT NULL , - "SNP_cnt" "int" NULL , - "SubSNP_cnt" "int" NULL , - "cluster_cnt" "int" NULL , - "unmapped_rs_cnt" "int" NULL , - "SubInd_cnt" "int" NULL , - "ind_cnt" "int" NULL , - "SubInd_ss_cnt" "int" NULL , - "SubPop_cnt" "int" NULL , - "pop_cnt" "int" NULL , - "SubPop_ss_cnt" "int" NULL , - "GtyFreqBySsPop_ss_cnt" "int" NULL , - "AlleleFreqBySsPop_ss_cnt" "int" NULL , - "SNPGtyFreq_rs_cnt" "int" NULL , - "SNPAlleleFreq_rs_cnt" "int" NULL , - "snp_build_max" "int" NULL , - "genome_build_max" "varchar" (8) NULL , - "map_time" "TIMESTAMP" NULL , - "cluster_time_max" "TIMESTAMP" NULL , - "create_time" "TIMESTAMP" NULL , - "last_SNPBatch_time" "TIMESTAMP" NULL , - "last_POPBatch_time" "TIMESTAMP" NULL , - "last_INDBatch_time" "TIMESTAMP" NULL , - "rsMax" "int" NULL , - "rsMissenseMax" "int" NULL -) -; - -CREATE TABLE "OrganismTax" ( - "organism" "varchar" (40) NOT NULL , - "tax_id" "int" NOT NULL , - "common_name" "varchar" (32) NULL , - "gpipe_abbr" "varchar" (7) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL , - "last_updated_time" "TIMESTAMP" NOT NULL , - "comment" "varchar" (255) NULL , - "division_cd" "varchar" (6) NULL , - "database_name" "varchar" (32) NOT NULL , - "short_common_name" "varchar" (32) NOT NULL , - "tax_id_rank" "varchar" (5) NOT NULL , - "species_tax_id" "int" NOT NULL , - "no_freq_summary" "bit" NULL , - "entrez_index" "smallint" NULL , - "pub_genome_build" "decimal"(4, 1) NULL -) -; - -CREATE TABLE "PopClass" ( - "pop_id" "int" NOT NULL , - "pop_class_id" "int" NOT NULL , - "snp_count" "int" NULL -) -; - -CREATE TABLE "PopClassCode" ( - "pop_class_id" "int" NOT NULL , - "pop_class" "char" (50) NOT NULL , - "pop_class_text" "char" (255) NOT NULL -) -; - -CREATE TABLE "Publication" ( - "pub_id" "int" NOT NULL , - "pmid" "int" NULL , - "handle" "varchar" (20) NOT NULL , - "meduid" "int" NULL , - "title" "varchar" (235) NOT NULL , - "journal" "varchar" (255) NULL , - "vol" "varchar" (255) NULL , - "suppl" "varchar" (128) NULL , - "issue" "varchar" (128) NULL , - "i_suppl" "varchar" (128) NULL , - "pages" "varchar" (255) NULL , - "year" "smallint" NOT NULL , - "status" "smallint" NOT NULL , - "blobflag" "smallint" NULL , - "last_updated" "TIMESTAMP" NOT NULL , - "create_time" "TIMESTAMP" NULL , - "last_updated_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "SNPGlossary" ( - "term" "varchar" (256) NOT NULL , - "description" "varchar" (4000) NULL , - "last_updated" "TIMESTAMP" NULL , - "used_in" "varchar" (32) NULL -) -; - -CREATE TABLE "SNP_tax_id" ( - "snp_id" "int" NOT NULL , - "tax_id" "int" NULL , - "update_time" "TIMESTAMP" NULL , - "status" "char" (1) NULL -) -; - -CREATE TABLE "SnpChrCode" ( - "code" "varchar" (8) NOT NULL , - "abbrev" "varchar" (20) NOT NULL , - "descrip" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL , - "sort_order" "smallint" NULL , - "db_name" "varchar" (32) NULL -) -; - -CREATE TABLE "SnpClassCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (20) NOT NULL , - "descrip" "varchar" (255) NOT NULL , - "display_str" "varchar" (255) NULL -) -; - -CREATE TABLE "SnpFunctionCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (20) NOT NULL , - "descrip" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL , - "top_level_class" "char" (5) NOT NULL , - "is_coding" "smallint" NOT NULL , - "is_exon" "bit" NULL -) -; - -CREATE TABLE "SnpValidationCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (40) NOT NULL , - "descrip" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NULL , - "last_updated_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "StrandCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (20) NOT NULL , - "rs_to_ss_orien" "bit" NOT NULL , - "descrip" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "SubSNPDelComm" ( - "comment_id" "smallint" NOT NULL , - "comment" "varchar" (255) NOT NULL , - "create_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "SubSNPSeqTypeCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (20) NOT NULL , - "descrip" "varchar" (255) NOT NULL -) -; - -CREATE TABLE "Submitter" ( - "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 "TemplateType" ( - "temp_type_id" "smallint" NOT NULL , - "name" "varchar" (64) NOT NULL , - "last_updated_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "UniGty" ( - "unigty_id" "int" NOT NULL , - "gty_str" "varchar" (255) NULL , - "allele_id_1" "int" NOT NULL , - "allele_id_2" "int" NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "UniVariAllele" ( - "univar_id" "int" NOT NULL , - "allele_id" "int" NOT NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "UniVariation" ( - "univar_id" "int" NOT NULL , - "var_str" "varchar" (900) NULL , - "allele_cnt" "smallint" NOT NULL , - "subsnp_class" "smallint" NOT NULL , - "iupack_code" "char" (1) NOT NULL , - "top_or_bot_strand" "char" (1) NOT NULL , - "create_time" "TIMESTAMP" NOT NULL , - "last_updated_time" "TIMESTAMP" NOT NULL , - "src_code" "smallint" NULL , - "rev_univar_id" "int" NULL -) -; - -CREATE TABLE "UniVariationSrcCode" ( - "code" "smallint" NOT NULL , - "abbrev" "varchar" (20) NOT NULL , - "descrip" "varchar" (255) NOT NULL -) -; - -CREATE TABLE "VariAllele" ( - "var_id" "int" NOT NULL , - "allele_id" "int" NOT NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "db_DataDictionaryNew" ( - "tab" "varchar" (64) NULL , - "col" "varchar" (32) NULL , - "col_order" "int" NULL , - "typ" "varchar" (16) NULL , - "length" "int" NULL , - "description" "varchar" (6000) NULL , - "todo" "varchar" (1000) NULL , - "update_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "db_ftp_table_list" ( - "table_name" "varchar" (32) NOT NULL , - "db_str" "varchar" (64) NOT NULL , - "create_time" "TIMESTAMP" NULL -) -; - -CREATE TABLE "db_map_table_name" ( - "table_name" "varchar" (32) NOT NULL , - "to_index" "bit" NULL -) -; - -CREATE TABLE "dn_Allele_rev" ( - "allele_id" "int" NOT NULL , - "rev_flag" "smallint" NOT NULL , - "fwd_allele_id" "int" NOT NULL , - "fwd_allele" "varchar" (255) NOT NULL -) -; - -CREATE TABLE "dn_Motif_rev" ( - "motif_id" "int" NOT NULL , - "rev_flag" "smallint" NOT NULL , - "fwd_motif" "varchar" (253) NULL , - "fwd_motif_id" "int" NULL -) -; - -CREATE TABLE "dn_UniGty_allele" ( - "unigty_id" "int" NOT NULL , - "chr_num" "smallint" NOT NULL , - "allele_id" "int" NOT NULL , - "create_time" "TIMESTAMP" NOT NULL -) -; - -CREATE TABLE "dn_UniGty_rev" ( - "unigty_id" "int" NOT NULL , - "rev_flag" "smallint" NOT NULL , - "fwd_unigty_id" "int" NOT NULL , - "fwd_gty_str" "varchar" (255) NULL -) -; - -CREATE TABLE "dn_UniVariation_rev" ( - "univar_id" "int" NOT NULL , - "rev_flag" "smallint" NOT NULL , - "fwd_univar_id" "int" NULL , - "fwd_univar_str" "varchar" (900) NULL -) -; - -CREATE TABLE "dn_baseFlip" ( - "base" "char" (1) NULL , - "rev_flag" "smallint" NULL , - "fwd_base" "char" (1) NULL -) -; - -CREATE TABLE "dn_gty2unigty_trueSNP" ( - "gty_id" "int" NOT NULL , - "rev_flag" "bit" NOT NULL , - "unigty_id" "int" NULL , - "obs" "varchar" (512) NOT NULL , - "gty_str" "varchar" (255) NULL -) -; - -CREATE TABLE "dn_summary" ( - "tax_id" "int" NOT NULL , - "build_id" "int" NOT NULL , - "type" "varchar" (20) NOT NULL , - "cnt" "int" NOT NULL , - "create_time" "TIMESTAMP" NOT NULL , - "last_updated_time" "TIMESTAMP" NOT NULL -) -; - +CREATE TABLE Allele ( + allele_id int NOT NULL , + allele varchar (255) NOT NULL , + create_time TIMESTAMP NOT NULL , + rev_allele_id int NULL , + src varchar (10) NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE AlleleFlagCode ( + code smallint NOT NULL , + abbrev varchar (12) NOT NULL , + descrip varchar (255) NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE AlleleMotif ( + allele_id int NOT NULL , + motif_order int NOT NULL , + motif_id int NOT NULL , + repeat_cnt real NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE AllocIds ( + name varchar (30) NOT NULL , + id int NOT NULL , + cycle int NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL , + comment varchar (255) NULL +) +; + +CREATE TABLE Author ( + pub_id int NOT NULL , + position smallint NOT NULL , + author varchar (255) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE Batch_tax_id ( + batch_id int NOT NULL , + tax_id int NOT NULL , + update_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE ChiSqPValueLookUp ( + df smallint NOT NULL , + chisq_from float NOT NULL , + chisq_to float NOT NULL , + pvalue_upper_bound float NOT NULL +) +; + +CREATE TABLE CpGCode ( + code smallint NOT NULL , + abbrev varchar (12) NOT NULL , + descrip varchar (255) NOT NULL +) +; + +CREATE TABLE GenBankDivisionCode ( + code varchar (6) NOT NULL , + abbrev varchar (40) NOT NULL , + descrip varchar (255) NOT NULL , + display_order smallint NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE GenderCode ( + code char (1) NOT NULL , + gender varchar (6) NOT NULL +) +; + +CREATE TABLE GenomeBuildInfo ( + snp_build_id int NOT NULL , + tax_id int NOT NULL , + database_name varchar (32) NOT NULL , + ncbi_build_id int NOT NULL , + ncbi_build_ver smallint NOT NULL , + insert_time TIMESTAMP DEFAULT NOW() NOT NULL , + is_major smallint DEFAULT 1 NOT NULL , + ncbi_genome_tax_id int DEFAULT 1 NOT NULL , + ref_assembly varchar (255) NULL +) +; + +CREATE TABLE GtyAllele ( + gty_id int NOT NULL , + rev_flag bit NOT NULL , + chr_num smallint NOT NULL , + fwd_allele_id int NOT NULL , + unigty_id int NULL , + create_time TIMESTAMP NOT NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE IUPACna ( + allele varchar (1) NOT NULL , + meaning varchar (10) NOT NULL , + bitcode varchar (4) NOT NULL , + value smallint NOT NULL , + rev_base char (1) NULL +) +; + +CREATE TABLE LoadHistory ( + build_id int NOT NULL , + loaddate TIMESTAMP NOT NULL , + status char (30) NOT NULL , + comments varchar (255) NULL , + script varchar (255) NULL , + ftp_done_date TIMESTAMP NULL , + entrez_done_date TIMESTAMP NULL , + blastdb_done_date TIMESTAMP NULL , + web_date TIMESTAMP NULL +) +; + +CREATE TABLE LocTypeCode ( + code smallint NOT NULL , + abbrev varchar (12) NOT NULL , + descrip varchar (255) NOT NULL , + create_time TIMESTAMP DEFAULT NOW() NOT NULL +) +; + +CREATE TABLE MapLinkCode ( + which_code varchar (20) NOT NULL , + code smallint NOT NULL , + abbr varchar (10) NULL , + note varchar (255) NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE Method ( + method_id int NOT NULL , + handle varchar (20) NOT NULL , + loc_method_id varchar (64) NOT NULL , + loc_method_id_upp varchar (64) NOT NULL , + method_class smallint NULL , + template_type smallint NULL , + seq_both_strands varchar (3) NULL , + mult_pcr_amplification varchar (3) NULL , + mult_clones_tested varchar (3) NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE MethodClass ( + meth_class_id smallint NOT NULL , + name varchar (64) NOT NULL , + last_updated_time TIMESTAMP NOT NULL , + validation_status smallint NOT NULL +) +; + +CREATE TABLE MethodLine ( + method_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 Moltype ( + code smallint NOT NULL , + moltype varchar (10) NOT NULL , + descrip varchar (255) NOT NULL +) +; + +CREATE TABLE Motif ( + motif_id int NOT NULL , + motif varchar (253) NOT NULL , + rev_motif_id int NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE ObsGenotype ( + gty_id int NOT NULL , + obs varchar (512) NOT NULL , + obs_upp_fix varchar (512) NOT NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE ObsVariation ( + var_id int NOT NULL , + pattern varchar (255) NOT NULL , + create_time TIMESTAMP DEFAULT NOW() NOT NULL , + last_updated_time TIMESTAMP DEFAULT NOW() NULL , + univar_id int NULL , + var_flag smallint NULL +) +; + +CREATE TABLE OrgDbStatus ( + database_name varchar (32) NOT NULL , + SNP_cnt int NULL , + SubSNP_cnt int NULL , + cluster_cnt int NULL , + unmapped_rs_cnt int NULL , + SubInd_cnt int NULL , + ind_cnt int NULL , + SubInd_ss_cnt int NULL , + SubPop_cnt int NULL , + pop_cnt int NULL , + SubPop_ss_cnt int NULL , + GtyFreqBySsPop_ss_cnt int NULL , + AlleleFreqBySsPop_ss_cnt int NULL , + SNPGtyFreq_rs_cnt int NULL , + SNPAlleleFreq_rs_cnt int NULL , + snp_build_max int NULL , + genome_build_max varchar (8) NULL , + map_time TIMESTAMP NULL , + cluster_time_max TIMESTAMP NULL , + create_time TIMESTAMP NULL , + last_SNPBatch_time TIMESTAMP NULL , + last_POPBatch_time TIMESTAMP NULL , + last_INDBatch_time TIMESTAMP NULL , + rsMax int NULL , + rsMissenseMax int NULL , + copy2FTP_time TIMESTAMP NULL +) +; + +CREATE TABLE OrganismTax ( + organism varchar (40) NOT NULL , + tax_id int NOT NULL , + common_name varchar (32) NULL , + gpipe_abbr varchar (7) NOT NULL , + create_time TIMESTAMP NOT NULL , + last_updated_time TIMESTAMP NOT NULL , + comment varchar (255) NULL , + division_cd varchar (6) NULL , + database_name varchar (32) NOT NULL , + short_common_name varchar (32) NOT NULL , + tax_id_rank varchar (5) NOT NULL , + species_tax_id int NOT NULL , + no_freq_summary bit NULL , + entrez_index smallint NULL , + pub_genome_build decimal(4, 1) NULL +) +; + +CREATE TABLE PopClass ( + pop_id int NOT NULL , + pop_class_id int NOT NULL , + snp_count int NULL +) +; + +CREATE TABLE PopClassCode ( + pop_class_id int NOT NULL , + pop_class char (50) NOT NULL , + pop_class_text char (255) NOT NULL +) +; + +CREATE TABLE Publication ( + pub_id int NOT NULL , + pmid int NULL , + handle varchar (20) NOT NULL , + meduid int NULL , + title varchar (235) NOT NULL , + journal varchar (255) NULL , + vol varchar (255) NULL , + suppl varchar (128) NULL , + issue varchar (128) NULL , + i_suppl varchar (128) NULL , + pages varchar (255) NULL , + year smallint NOT NULL , + status smallint NOT NULL , + blobflag smallint NULL , + last_updated TIMESTAMP NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE SNPGlossary ( + term varchar (256) NOT NULL , + description varchar (4000) NULL , + last_updated TIMESTAMP DEFAULT NOW() NULL , + used_in varchar (32) NULL +) +; + +CREATE TABLE SNP_tax_id ( + snp_id int NOT NULL , + tax_id int NULL , + update_time TIMESTAMP NULL , + status char (1) DEFAULT 'C' NULL +) +; + +CREATE TABLE SnpChrCode ( + code varchar (8) NOT NULL , + abbrev varchar (20) NOT NULL , + descrip varchar (255) NOT NULL , + create_time TIMESTAMP NOT NULL , + sort_order smallint NULL , + db_name varchar (32) NULL , + NC_acc varchar (16) NULL +) +; + +CREATE TABLE SnpClassCode ( + code smallint NOT NULL , + abbrev varchar (20) NOT NULL , + descrip varchar (255) NOT NULL , + display_str varchar (255) NULL +) +; + +CREATE TABLE SnpFunctionCode ( + code smallint NOT NULL , + abbrev varchar (20) NOT NULL , + descrip varchar (255) NOT NULL , + create_time TIMESTAMP NOT NULL , + top_level_class char (5) NOT NULL , + is_coding smallint NOT NULL , + is_exon bit NULL +) +; + +CREATE TABLE SnpValidationCode ( + code smallint NOT NULL , + abbrev varchar (64) NOT NULL , + descrip varchar (255) NOT NULL , + create_time TIMESTAMP NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE StrandCode ( + code smallint NOT NULL , + abbrev varchar (20) NOT NULL , + rs_to_ss_orien bit NOT NULL , + descrip varchar (255) NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE SubSNPDelComm ( + comment_id smallint NOT NULL , + comment varchar (255) NOT NULL , + create_time TIMESTAMP NULL +) +; + +CREATE TABLE SubSNPSeqTypeCode ( + code smallint NOT NULL , + abbrev varchar (20) NOT NULL , + descrip varchar (255) NOT NULL +) +; + +CREATE TABLE Submitter ( + 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 DEFAULT NOW() NULL , + last_updated_time TIMESTAMP NULL +) +; + +CREATE TABLE TemplateType ( + temp_type_id smallint NOT NULL , + name varchar (64) NOT NULL , + last_updated_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE UniGty ( + unigty_id int NOT NULL , + gty_str varchar (255) NULL , + allele_id_1 int NOT NULL , + allele_id_2 int NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE UniVariAllele ( + univar_id int NOT NULL , + allele_id int NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE UniVariation ( + univar_id int NOT NULL , + var_str varchar (900) NULL , + allele_cnt smallint NOT NULL , + subsnp_class smallint NOT NULL , + iupack_code char (1) NOT NULL , + top_or_bot_strand char (1) NOT NULL , + create_time TIMESTAMP NOT NULL , + last_updated_time TIMESTAMP NOT NULL , + src_code smallint NULL , + rev_univar_id int NULL +) +; + +CREATE TABLE UniVariationSrcCode ( + code smallint NOT NULL , + abbrev varchar (20) NOT NULL , + descrip varchar (255) NOT NULL +) +; + +CREATE TABLE VariAllele ( + var_id int NOT NULL , + allele_id int NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE db_ftp_table_list ( + table_name varchar (32) NOT NULL , + db_str varchar (64) NOT NULL , + create_time TIMESTAMP DEFAULT NOW() NULL +) +; + +CREATE TABLE db_map_table_name ( + table_name varchar (32) NOT NULL , + to_index bit NULL , + isCurrent char (1) NULL , + inNonHuman char (1) DEFAULT 'N' NOT NULL +) +; + +CREATE TABLE dn_Allele_rev ( + allele_id int NOT NULL , + rev_flag smallint NOT NULL , + fwd_allele_id int NOT NULL , + fwd_allele varchar (255) NOT NULL +) +; + +CREATE TABLE dn_Motif_rev ( + motif_id int NOT NULL , + rev_flag smallint NOT NULL , + fwd_motif varchar (253) NULL , + fwd_motif_id int NULL +) +; + +CREATE TABLE dn_UniGty_allele ( + unigty_id int NOT NULL , + chr_num smallint NOT NULL , + allele_id int NOT NULL , + create_time TIMESTAMP NOT NULL +) +; + +CREATE TABLE dn_UniGty_rev ( + unigty_id int NOT NULL , + rev_flag smallint NOT NULL , + fwd_unigty_id int NOT NULL , + fwd_gty_str varchar (255) NULL +) +; + +CREATE TABLE dn_UniVariation_rev ( + univar_id int NOT NULL , + rev_flag smallint NOT NULL , + fwd_univar_id int NULL , + fwd_univar_str varchar (900) NULL +) +; + +CREATE TABLE dn_baseFlip ( + base char (1) NULL , + rev_flag smallint NULL , + fwd_base char (1) NULL +) +; + +CREATE TABLE dn_gty2unigty_trueSNP ( + gty_id int NOT NULL , + rev_flag bit NOT NULL , + unigty_id int NULL , + obs varchar (512) NOT NULL , + gty_str varchar (255) NULL +) +; + +CREATE TABLE dn_summary ( + tax_id int NOT NULL , + build_id int NOT NULL , + type varchar (20) NOT NULL , + cnt int NOT NULL , + create_time TIMESTAMP DEFAULT NOW() NOT NULL , + last_updated_time TIMESTAMP DEFAULT NOW() NOT NULL +) +; +