From: Don Armstrong Date: Tue, 6 Dec 2011 23:55:38 +0000 (-0800) Subject: add initial files X-Git-Url: https://git.donarmstrong.com/?p=dbsnp.git;a=commitdiff_plain;h=29955733afccef6bdefd2cfb746a2e4ae9e3b06a add initial files --- 29955733afccef6bdefd2cfb746a2e4ae9e3b06a diff --git a/schema/shared_schema/dbSNP_main_constraint.sql b/schema/shared_schema/dbSNP_main_constraint.sql new file mode 100644 index 0000000..304d119 --- /dev/null +++ b/schema/shared_schema/dbSNP_main_constraint.sql @@ -0,0 +1,398 @@ +ALTER TABLE [Allele] ADD + CONSTRAINT [pk_Allele] PRIMARY KEY CLUSTERED + ( + [allele_id] + ) +GO + + +ALTER TABLE [AlleleFlagCode] ADD + CONSTRAINT [pk_AlleleFlagCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [AlleleMotif] ADD + CONSTRAINT [pk_AlleleMotif] PRIMARY KEY CLUSTERED + ( + [allele_id], + [motif_order] + ) +GO + + +ALTER TABLE [AllocIds] ADD + CONSTRAINT [pk_AllocIds] PRIMARY KEY NONCLUSTERED + ( + [name] + ) +GO + + +ALTER TABLE [Author] ADD + CONSTRAINT [pk_Author] PRIMARY KEY NONCLUSTERED + ( + [pub_id], + [position] + ) +GO + + + + +ALTER TABLE [CpGCode] ADD + CONSTRAINT [pk_CpGCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + + + +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] +GO + + +ALTER TABLE [GtyAllele] ADD + CONSTRAINT [pk_GtyAllele] PRIMARY KEY CLUSTERED + ( + [gty_id], + [rev_flag], + [chr_num] + ) +GO + + +ALTER TABLE [IUPACna] ADD + CONSTRAINT [pk_IUPACna] PRIMARY KEY CLUSTERED + ( + [allele] + ) +GO + + +ALTER TABLE [LoadHistory] ADD + CONSTRAINT [pk_LoadHistory] PRIMARY KEY NONCLUSTERED + ( + [build_id] + ) +GO + + +ALTER TABLE [LocTypeCode] ADD + CONSTRAINT [DF__LocTypeCo__creat__75CD617E] DEFAULT (getdate()) FOR [create_time], + CONSTRAINT [pk_LocTypeCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [MapLinkCode] ADD + CONSTRAINT [pk_MapLinkCode] PRIMARY KEY CLUSTERED + ( + [which_code], + [code] + ) +GO + + +ALTER TABLE [Method] ADD + CONSTRAINT [pk_Method_mid] PRIMARY KEY CLUSTERED + ( + [method_id] + ) +GO + + +ALTER TABLE [MethodClass] ADD + CONSTRAINT [pk_MethodClass] PRIMARY KEY CLUSTERED + ( + [meth_class_id] + ) +GO + + +ALTER TABLE [MethodLine] ADD + CONSTRAINT [pk_MethodLine] PRIMARY KEY NONCLUSTERED + ( + [method_id], + [line_num] + ) +GO + + +ALTER TABLE [Moltype] ADD + CONSTRAINT [pk_Moltype] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [Motif] ADD + CONSTRAINT [pk_Motif] PRIMARY KEY CLUSTERED + ( + [motif_id] + ) +GO + + +ALTER TABLE [ObsGenotype] ADD + CONSTRAINT [pk_ObsGenotype] PRIMARY KEY CLUSTERED + ( + [gty_id] + ) +GO + + +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 CLUSTERED + ( + [var_id] + ) +GO + + +ALTER TABLE [OrgDbStatus] ADD + CONSTRAINT [pk_org_db_status] PRIMARY KEY CLUSTERED + ( + [database_name] + ) +GO + + +ALTER TABLE [OrganismTax] ADD + CONSTRAINT [pk_organism] PRIMARY KEY NONCLUSTERED + ( + [tax_id] + ) +GO + + +ALTER TABLE [PopClass] ADD + CONSTRAINT [pk_PopClass] PRIMARY KEY CLUSTERED + ( + [pop_id], + [pop_class_id] + ) +GO + + +ALTER TABLE [PopClassCode] ADD + CONSTRAINT [PK__PopClassCode__270595B6] PRIMARY KEY CLUSTERED + ( + [pop_class_id] + ) +GO + + +ALTER TABLE [Publication] ADD + CONSTRAINT [pk_Publication] PRIMARY KEY NONCLUSTERED + ( + [pub_id] + ) +GO + + +ALTER TABLE [SNPGlossary] ADD + CONSTRAINT [DF__SNPGlossa__last___4050666D] DEFAULT (getdate()) FOR [last_updated], + CONSTRAINT [pk_SNPGlossary] PRIMARY KEY CLUSTERED + ( + [term] + ) +GO + + +ALTER TABLE [SNP_tax_id] ADD + CONSTRAINT [DF__SNP_tax_i__statu__31583BA0] DEFAULT ('C') FOR [status], + CONSTRAINT [pk_SNP_tax_id] PRIMARY KEY CLUSTERED + ( + [snp_id] + ) +GO + + +ALTER TABLE [SnpChrCode] ADD + CONSTRAINT [pk_SnpChrCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [SnpClassCode] ADD + CONSTRAINT [pk_SnpClassCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [SnpFunctionCode] ADD + CONSTRAINT [pk_SnpFunctionCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [SnpValidationCode] ADD + CONSTRAINT [pk_SnpValidationCode] PRIMARY KEY NONCLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [StrandCode] ADD + CONSTRAINT [pk_StrandCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + + + +ALTER TABLE [Submitter] ADD + CONSTRAINT [DF_Submitter_create_time] DEFAULT (getdate()) FOR [create_time], + CONSTRAINT [pk_Submitter] PRIMARY KEY NONCLUSTERED + ( + [handle] + ) +GO + + +ALTER TABLE [TemplateType] ADD + CONSTRAINT [pk_TemplateType] PRIMARY KEY CLUSTERED + ( + [temp_type_id] + ) +GO + + +ALTER TABLE [UniGty] ADD + CONSTRAINT [pk_UniGty] PRIMARY KEY CLUSTERED + ( + [unigty_id] + ) +GO + + +ALTER TABLE [UniVariAllele] ADD + CONSTRAINT [pk_UniVariAllele] PRIMARY KEY CLUSTERED + ( + [univar_id], + [allele_id] + ) +GO + + +ALTER TABLE [UniVariation] ADD + CONSTRAINT [pk_UniVariation] PRIMARY KEY CLUSTERED + ( + [univar_id] + ) +GO + + +ALTER TABLE [UniVariationSrcCode] ADD + CONSTRAINT [pk_UniVariationSrcCode] PRIMARY KEY CLUSTERED + ( + [code] + ) +GO + + +ALTER TABLE [VariAllele] ADD + CONSTRAINT [pk_VariAllele] PRIMARY KEY CLUSTERED + ( + [var_id], + [allele_id] + ) +GO + + +ALTER TABLE [db_DataDictionaryNew] ADD + CONSTRAINT [DF__db_DataDi__updat__5BB889C0] DEFAULT (getdate()) FOR [update_time] +GO + + +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 CLUSTERED + ( + [table_name] + ) +GO + + + +ALTER TABLE [dn_Allele_rev] ADD + CONSTRAINT [pk_dn_Allele_rev] PRIMARY KEY CLUSTERED + ( + [allele_id], + [rev_flag] + ) +GO + + +ALTER TABLE [dn_Motif_rev] ADD + CONSTRAINT [pk_dn_Motif_rev] PRIMARY KEY CLUSTERED + ( + [motif_id], + [rev_flag] + ) +GO + + +ALTER TABLE [dn_UniGty_allele] ADD + CONSTRAINT [pk_dn_UniGty_allele] PRIMARY KEY CLUSTERED + ( + [unigty_id], + [chr_num] + ) +GO + + +ALTER TABLE [dn_UniGty_rev] ADD + CONSTRAINT [pk_dn_Gty_rev] PRIMARY KEY CLUSTERED + ( + [unigty_id], + [rev_flag] + ) +GO + + +ALTER TABLE [dn_UniVariation_rev] ADD + CONSTRAINT [pk_dn_UniVariation_rev] PRIMARY KEY CLUSTERED + ( + [univar_id], + [rev_flag] + ) +GO + + + + +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 CLUSTERED + ( + [tax_id], + [build_id], + [type] + ) +GO + + diff --git a/schema/shared_schema/dbSNP_main_constraint_postgresql.sql b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql new file mode 100644 index 0000000..b5ee5d2 --- /dev/null +++ b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql @@ -0,0 +1,352 @@ +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" + ) +; + diff --git a/schema/shared_schema/dbSNP_main_index.sql b/schema/shared_schema/dbSNP_main_index.sql new file mode 100644 index 0000000..7586c54 --- /dev/null +++ b/schema/shared_schema/dbSNP_main_index.sql @@ -0,0 +1,166 @@ + CREATE UNIQUE INDEX [i_allele] ON [Allele]([allele]) +GO + + CREATE INDEX [i_rev_allele_id] ON [Allele]([rev_allele_id]) +GO + + + + CREATE INDEX [i_motif] ON [AlleleMotif]([motif_id]) +GO + + + CREATE UNIQUE CLUSTERED INDEX [ind_AllocIds_name] ON [AllocIds]([name]) +GO + + + CREATE INDEX [i_auth] ON [Author]([author]) +GO + + + CREATE UNIQUE CLUSTERED INDEX [i_ss] ON [Batch_tax_id]([batch_id]) +GO + + + + + + + + CREATE INDEX [i_unigty] ON [GtyAllele]([unigty_id]) +GO + + CREATE INDEX [gty_id_idx] ON [GtyAllele]([gty_id]) +GO + + CREATE INDEX [i_gty_id] ON [GtyAllele]([gty_id]) +GO + + + + CREATE INDEX [i_loaddate] ON [LoadHistory]([loaddate]) +GO + + + + + CREATE UNIQUE INDEX [i_handle_loc_method_id] ON [Method]([handle], [loc_method_id]) +GO + + + + + + CREATE UNIQUE INDEX [i_motif] ON [Motif]([motif]) +GO + + + CREATE INDEX [i_obs] ON [ObsGenotype]([obs]) +GO + + CREATE INDEX [i_obs_upp_fix] ON [ObsGenotype]([obs_upp_fix]) +GO + + CREATE INDEX [i_gty_id] ON [ObsGenotype]([gty_id]) +GO + + + CREATE UNIQUE INDEX [i_pattern] ON [ObsVariation]([pattern]) +GO + + CREATE INDEX [i_univar] ON [ObsVariation]([univar_id]) +GO + + + + CREATE UNIQUE CLUSTERED INDEX [iuc_OrganismTax] ON [OrganismTax]([organism]) +GO + + CREATE INDEX [i_tax_id] ON [OrganismTax]([tax_id]) +GO + + CREATE INDEX [i_sp_tax] ON [OrganismTax]([species_tax_id]) +GO + + CREATE INDEX [i_dbname] ON [OrganismTax]([database_name]) +GO + + + + + CREATE UNIQUE CLUSTERED INDEX [i_handle_title] ON [Publication]([handle], [title]) +GO + + + + CREATE INDEX [i_tax_id] ON [SNP_tax_id]([tax_id], [snp_id]) +GO + + + + + + CREATE UNIQUE CLUSTERED INDEX [i_code] ON [SnpValidationCode]([code]) +GO + + + CREATE INDEX [i_abbrev_orien] ON [StrandCode]([abbrev], [rs_to_ss_orien], [code]) +GO + + + CREATE UNIQUE CLUSTERED INDEX [i_comment_id] ON [SubSNPDelComm]([comment_id]) +GO + + + + CREATE UNIQUE CLUSTERED INDEX [iuc_Submitter] ON [Submitter]([handle]) +GO + + CREATE INDEX [ic_Submitter] ON [Submitter]([name]) +GO + + + + CREATE UNIQUE INDEX [i_u_gty_str] ON [UniGty]([gty_str]) +GO + + CREATE UNIQUE INDEX [i_u_allele_ids] ON [UniGty]([allele_id_1], [allele_id_2]) +GO + + CREATE INDEX [i_allele_id_2] ON [UniGty]([allele_id_2]) +GO + + + CREATE INDEX [i_allele_id] ON [UniVariAllele]([allele_id]) +GO + + + CREATE UNIQUE INDEX [i_u_var_str] ON [UniVariation]([var_str]) +GO + + CREATE INDEX [cv_uvid_class] ON [UniVariation]([univar_id], [subsnp_class]) +GO + + + + CREATE INDEX [i_allele_id] ON [VariAllele]([allele_id]) +GO + + + CREATE UNIQUE INDEX [i_u] ON [db_DataDictionaryNew]([tab], [col_order]) +GO + + + + + + + + + + + CREATE UNIQUE CLUSTERED INDEX [i_gty] ON [dn_gty2unigty_trueSNP]([gty_id], [rev_flag]) +GO + + + diff --git a/schema/shared_schema/dbSNP_main_index_postgresql.sql b/schema/shared_schema/dbSNP_main_index_postgresql.sql new file mode 100644 index 0000000..e503a6a --- /dev/null +++ b/schema/shared_schema/dbSNP_main_index_postgresql.sql @@ -0,0 +1,129 @@ + 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") +; + + diff --git a/schema/shared_schema/dbSNP_main_table.sql b/schema/shared_schema/dbSNP_main_table.sql new file mode 100644 index 0000000..c3d151b --- /dev/null +++ b/schema/shared_schema/dbSNP_main_table.sql @@ -0,0 +1,582 @@ +CREATE TABLE [Allele] ( + [allele_id] [int] NOT NULL , + [allele] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NOT NULL , + [rev_allele_id] [int] NULL , + [src] [varchar] (10) NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [AlleleFlagCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (12) NOT NULL , + [descrip] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +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] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [AllocIds] ( + [name] [varchar] (30) NOT NULL , + [id] [int] NOT NULL , + [cycle] [int] NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [Author] ( + [pub_id] [int] NOT NULL , + [position] [tinyint] NOT NULL , + [author] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [Batch_tax_id] ( + [batch_id] [int] NOT NULL , + [tax_id] [int] NOT NULL , + [update_time] [datetime] NOT NULL +) +GO + + +CREATE TABLE [ChiSqPValueLookUp] ( + [df] [tinyint] NOT NULL , + [chisq_from] [float] NOT NULL , + [chisq_to] [float] NOT NULL , + [pvalue_upper_bound] [float] NOT NULL +) +GO + + +CREATE TABLE [CpGCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (12) NOT NULL , + [descrip] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [GenBankDivisionCode] ( + [code] [varchar] (6) NOT NULL , + [abbrev] [varchar] (40) NOT NULL , + [descrip] [varchar] (255) NOT NULL , + [display_order] [tinyint] NOT NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [GenderCode] ( + [code] [char] (1) NOT NULL , + [gender] [varchar] (6) NOT NULL +) +GO + + +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] [tinyint] NOT NULL , + [insert_time] [smalldatetime] NOT NULL , + [is_major] [tinyint] NOT NULL , + [ncbi_genome_tax_id] [int] NOT NULL , + [ref_assembly] [varchar] (255) NULL +) +GO + + +CREATE TABLE [GtyAllele] ( + [gty_id] [int] NOT NULL , + [rev_flag] [bit] NOT NULL , + [chr_num] [tinyint] NOT NULL , + [fwd_allele_id] [int] NOT NULL , + [unigty_id] [int] NULL , + [create_time] [smalldatetime] NOT NULL , + [last_updated_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [IUPACna] ( + [allele] [varchar] (1) NOT NULL , + [meaning] [varchar] (10) NOT NULL , + [bitcode] [varchar] (4) NOT NULL , + [value] [tinyint] NOT NULL , + [rev_base] [char] (1) NULL +) +GO + + +CREATE TABLE [LoadHistory] ( + [build_id] [int] NOT NULL , + [loaddate] [smalldatetime] NOT NULL , + [status] [char] (30) NOT NULL , + [comments] [varchar] (255) NULL , + [script] [varchar] (255) NULL , + [ftp_done_date] [smalldatetime] NULL , + [entrez_done_date] [smalldatetime] NULL , + [blastdb_done_date] [smalldatetime] NULL , + [web_date] [smalldatetime] NULL +) +GO + + +CREATE TABLE [LocTypeCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (12) NOT NULL , + [descrip] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [MapLinkCode] ( + [which_code] [varchar] (20) NOT NULL , + [code] [tinyint] NOT NULL , + [abbr] [varchar] (10) NULL , + [note] [varchar] (255) NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +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] [tinyint] NULL , + [template_type] [tinyint] NULL , + [seq_both_strands] [varchar] (3) NULL , + [mult_pcr_amplification] [varchar] (3) NULL , + [mult_clones_tested] [varchar] (3) NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [MethodClass] ( + [meth_class_id] [tinyint] NOT NULL , + [name] [varchar] (64) NOT NULL , + [last_updated_time] [smalldatetime] NOT NULL , + [validation_status] [tinyint] NOT NULL +) +GO + + +CREATE TABLE [MethodLine] ( + [method_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 [Moltype] ( + [code] [tinyint] NOT NULL , + [moltype] [varchar] (10) NOT NULL , + [descrip] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [Motif] ( + [motif_id] [int] NOT NULL , + [motif] [varchar] (253) NOT NULL , + [rev_motif_id] [int] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [ObsGenotype] ( + [gty_id] [int] NOT NULL , + [obs] [varchar] (512) NOT NULL , + [obs_upp_fix] [varchar] (512) NOT NULL , + [last_updated_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [ObsVariation] ( + [var_id] [int] NOT NULL , + [pattern] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NOT NULL , + [last_updated_time] [smalldatetime] NULL , + [univar_id] [int] NULL , + [var_flag] [tinyint] NULL +) +GO + + +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] [smalldatetime] NULL , + [cluster_time_max] [smalldatetime] NULL , + [create_time] [smalldatetime] NULL , + [last_SNPBatch_time] [smalldatetime] NULL , + [last_POPBatch_time] [smalldatetime] NULL , + [last_INDBatch_time] [smalldatetime] NULL , + [rsMax] [int] NULL , + [rsMissenseMax] [int] NULL +) +GO + + +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] [smalldatetime] NOT NULL , + [last_updated_time] [smalldatetime] 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 +) +GO + + +CREATE TABLE [PopClass] ( + [pop_id] [int] NOT NULL , + [pop_class_id] [int] NOT NULL , + [snp_count] [int] NULL +) +GO + + +CREATE TABLE [PopClassCode] ( + [pop_class_id] [int] NOT NULL , + [pop_class] [char] (50) NOT NULL , + [pop_class_text] [char] (255) NOT NULL +) +GO + + +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] [tinyint] NOT NULL , + [blobflag] [tinyint] NULL , + [last_updated] [smalldatetime] NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [SNPGlossary] ( + [term] [varchar] (256) NOT NULL , + [description] [varchar] (4000) NULL , + [last_updated] [smalldatetime] NULL , + [used_in] [varchar] (32) NULL +) +GO + + +CREATE TABLE [SNP_tax_id] ( + [snp_id] [int] NOT NULL , + [tax_id] [int] NULL , + [update_time] [smalldatetime] NULL , + [status] [char] (1) NULL +) +GO + + +CREATE TABLE [SnpChrCode] ( + [code] [varchar] (8) NOT NULL , + [abbrev] [varchar] (20) NOT NULL , + [descrip] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NOT NULL , + [sort_order] [tinyint] NULL , + [db_name] [varchar] (32) NULL +) +GO + + +CREATE TABLE [SnpClassCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (20) NOT NULL , + [descrip] [varchar] (255) NOT NULL , + [display_str] [varchar] (255) NULL +) +GO + + +CREATE TABLE [SnpFunctionCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (20) NOT NULL , + [descrip] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NOT NULL , + [top_level_class] [char] (5) NOT NULL , + [is_coding] [tinyint] NOT NULL , + [is_exon] [bit] NULL +) +GO + + +CREATE TABLE [SnpValidationCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (40) NOT NULL , + [descrip] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [StrandCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (20) NOT NULL , + [rs_to_ss_orien] [bit] NOT NULL , + [descrip] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [SubSNPDelComm] ( + [comment_id] [smallint] NOT NULL , + [comment] [varchar] (255) NOT NULL , + [create_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [SubSNPSeqTypeCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (20) NOT NULL , + [descrip] [varchar] (255) NOT NULL +) +GO + + +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] [smalldatetime] NULL , + [last_updated_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [TemplateType] ( + [temp_type_id] [tinyint] NOT NULL , + [name] [varchar] (64) NOT NULL , + [last_updated_time] [smalldatetime] NOT NULL +) +GO + + +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] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [UniVariAllele] ( + [univar_id] [int] NOT NULL , + [allele_id] [int] NOT NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [UniVariation] ( + [univar_id] [int] NOT NULL , + [var_str] [varchar] (900) NULL , + [allele_cnt] [smallint] NOT NULL , + [subsnp_class] [tinyint] NOT NULL , + [iupack_code] [char] (1) NOT NULL , + [top_or_bot_strand] [char] (1) NOT NULL , + [create_time] [smalldatetime] NOT NULL , + [last_updated_time] [smalldatetime] NOT NULL , + [src_code] [tinyint] NULL , + [rev_univar_id] [int] NULL +) +GO + + +CREATE TABLE [UniVariationSrcCode] ( + [code] [tinyint] NOT NULL , + [abbrev] [varchar] (20) NOT NULL , + [descrip] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [VariAllele] ( + [var_id] [int] NOT NULL , + [allele_id] [int] NOT NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +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] [smalldatetime] NULL +) +GO + + +CREATE TABLE [db_ftp_table_list] ( + [table_name] [varchar] (32) NOT NULL , + [db_str] [varchar] (64) NOT NULL , + [create_time] [smalldatetime] NULL +) +GO + + +CREATE TABLE [db_map_table_name] ( + [table_name] [varchar] (32) NOT NULL , + [to_index] [bit] NULL +) +GO + + +CREATE TABLE [dn_Allele_rev] ( + [allele_id] [int] NOT NULL , + [rev_flag] [tinyint] NOT NULL , + [fwd_allele_id] [int] NOT NULL , + [fwd_allele] [varchar] (255) NOT NULL +) +GO + + +CREATE TABLE [dn_Motif_rev] ( + [motif_id] [int] NOT NULL , + [rev_flag] [tinyint] NOT NULL , + [fwd_motif] [varchar] (253) NULL , + [fwd_motif_id] [int] NULL +) +GO + + +CREATE TABLE [dn_UniGty_allele] ( + [unigty_id] [int] NOT NULL , + [chr_num] [tinyint] NOT NULL , + [allele_id] [int] NOT NULL , + [create_time] [smalldatetime] NOT NULL +) +GO + + +CREATE TABLE [dn_UniGty_rev] ( + [unigty_id] [int] NOT NULL , + [rev_flag] [tinyint] NOT NULL , + [fwd_unigty_id] [int] NOT NULL , + [fwd_gty_str] [varchar] (255) NULL +) +GO + + +CREATE TABLE [dn_UniVariation_rev] ( + [univar_id] [int] NOT NULL , + [rev_flag] [tinyint] NOT NULL , + [fwd_univar_id] [int] NULL , + [fwd_univar_str] [varchar] (900) NULL +) +GO + + +CREATE TABLE [dn_baseFlip] ( + [base] [char] (1) NULL , + [rev_flag] [tinyint] NULL , + [fwd_base] [char] (1) NULL +) +GO + + +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 +) +GO + + +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] [smalldatetime] NOT NULL , + [last_updated_time] [smalldatetime] NOT NULL +) +GO + + diff --git a/schema/shared_schema/dbSNP_main_table_postgresql.sql b/schema/shared_schema/dbSNP_main_table_postgresql.sql new file mode 100644 index 0000000..497f9d9 --- /dev/null +++ b/schema/shared_schema/dbSNP_main_table_postgresql.sql @@ -0,0 +1,527 @@ +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 +) +; + diff --git a/schema/shared_schema/dbSNP_main_view.sql b/schema/shared_schema/dbSNP_main_view.sql new file mode 100644 index 0000000..7e96674 --- /dev/null +++ b/schema/shared_schema/dbSNP_main_view.sql @@ -0,0 +1,1051 @@ +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +create view vwAllele +as +select a.allele_id, a.rev_allele_id, a.allele, r.allele rev_allele +from dbo.Allele a (index (i_rev_allele_id)) join dbo.Allele r (index (pk_Allele)) on a.rev_allele_id = r.allele_id + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +create view vwAllele_rev_flag (allele_id, rev_flag, fwd_allele_id, fwd_allele) +as +select allele_id, 0, allele_id, allele From Allele +union +select allele_id, 1, rev_allele_id, rev_allele from vwAllele + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +create view vwMotif +as + +select distinct m.motif_id, m.motif, r.motif_id rev_motif_id, r.motif rev_motif, m.last_updated_time +from Motif m join Motif r on m.motif_id = r.rev_motif_id + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +create view vwMotif_rev ( motif_id, rev_flag, fwd_motif, fwd_motif_id) +as +/* log: 11/4/04 updated. when ref_flag=1, fwd_motif_id should be motif_id! + */ +select motif_id, 0, motif, motif_id from Motif +union +select motif_id, 1, rev_motif, rev_motif_id from vwMotif + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +CREATE VIEW dbo.vw_ABI_RsMergeArch +AS +SELECT human_9606.dbo.RsMergeArch.* FROM human_9606.dbo.RsMergeArch union +SELECT mouse_10090.dbo.RsMergeArch.* FROM mouse_10090.dbo.RsMergeArch + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +CREATE VIEW dbo.vw_ABI_SNP +AS +SELECT ss.* FROM human_9606.dbo.SNP ss union +SELECT ss.* FROM mouse_10090.dbo.SNP ss +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + +CREATE VIEW dbo.vw_ABI_SubSNP +AS +SELECT ss.* +FROM human_9606.dbo.SubSNP ss, human_9606.dbo.Batch b where b.handle = 'ABI' and b.batch_id = ss.batch_id union +SELECT ss.* +FROM mouse_10090.dbo.SubSNP ss, mouse_10090.dbo.Batch b where b.handle = 'ABI' and b.batch_id = ss.batch_id + + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + +create view vw_BatchCita as +select * from arabidopsis_3702..BatchCita union +select * from bison_9901..BatchCita union +select * from blackbird_39638..BatchCita union +select * from bonobo_9597..BatchCita union +select * from chicken_9031..BatchCita union +select * from chimpanzee_9598..BatchCita union +select * from cooperia_27828..BatchCita union +select * from corn_4577..BatchCita union +select * from cow_30522..BatchCita union +select * from cow_9913..BatchCita union +select * from dog_9615..BatchCita union +select * from flycatcher_46689..BatchCita union +select * from flycatcher_59894..BatchCita union +select * from gorilla_9593..BatchCita union +select * from horse_9796..BatchCita union +select * from human_9606..BatchCita union +select * from macaque_9544..BatchCita union +select * from mosquito_7165..BatchCita union +select * from mouse_10090..BatchCita union +select * from mouse_spretus_10096..BatchCita union +select * from nematode_6239..BatchCita union +select * from orangutan_9600..BatchCita union +select * from pig_9823..BatchCita union +select * from pine_71647..BatchCita union +select * from plasmodium_5833..BatchCita union +select * from rat_10116..BatchCita union +select * from rat_10118..BatchCita union +select * from rice_4530..BatchCita union +select * from salmon_8030..BatchCita union +select * from sheep_9940..BatchCita union +select * from soybean_3847..BatchCita union +select * from sugarcane_128810..BatchCita union +select * from trematode_6183..BatchCita union +select * from tubeworm_216498..BatchCita union +select * from zebrafish_7955..BatchCita + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + +create view vw_ContigExon as +select * from bison_9901..vw_ContigExon union +select * from chicken_9031..vw_ContigExon union +select * from chimpanzee_9598..vw_ContigExon union +select * from cow_30522..vw_ContigExon union +select * from cow_9913..vw_ContigExon union +select * from dog_9615..vw_ContigExon union +select * from human_9606..vw_ContigExon union +select * from mosquito_7165..vw_ContigExon union +select * from mouse_10090..vw_ContigExon union +select * from nematode_6239..vw_ContigExon union +select * from rat_10116..vw_ContigExon union +select * from rice_4530..vw_ContigExon union +select * from zebrafish_7955..vw_ContigExon + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + +create view vw_ContigInfo as +select * from bison_9901..vw_ContigInfo union +select * from chicken_9031..vw_ContigInfo union +select * from chimpanzee_9598..vw_ContigInfo union +select * from cow_30522..vw_ContigInfo union +select * from cow_9913..vw_ContigInfo union +select * from dog_9615..vw_ContigInfo union +select * from human_9606..vw_ContigInfo union +select * from mosquito_7165..vw_ContigInfo union +select * from mouse_10090..vw_ContigInfo union +select * from nematode_6239..vw_ContigInfo union +select * from rat_10116..vw_ContigInfo union +select * from rice_4530..vw_ContigInfo union +select * from zebrafish_7955..vw_ContigInfo + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + +CREATE VIEW dbo.vw_E_Maplink +AS +select * from bison_9901..vw_MapLink union +select * from chicken_9031..vw_MapLink union +select * from chimpanzee_9598..vw_MapLink union +select * from cow_30522..vw_MapLink union +select * from cow_9913..vw_MapLink union +select * from dog_9615..vw_MapLink union +select * from human_9606..vw_E_MapLink union +select * from mosquito_7165..vw_MapLink union +select * from mouse_10090..vw_MapLink union +select * from nematode_6239..vw_MapLink union +select * from rat_10116..vw_MapLink union +select * from rice_4530..vw_MapLink union +select * from zebrafish_7955..vw_MapLink + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_IndivSourceCode as select * from arabidopsis_3702..IndivSourceCode union select * from bee_7460..IndivSourceCode union select * from bison_9901..IndivSourceCode union select * from blackbird_39638..IndivSourceCode union select * from bonobo_9597..IndivSourceCode union select * from chicken_9031..IndivSourceCode union select * from chimpanzee_9598..IndivSourceCode union select * from cooperia_27828..IndivSourceCode union select * from corn_4577..IndivSourceCode union select * from cow_30522..IndivSourceCode union select * from cow_9913..IndivSourceCode union select * from dog_9615..IndivSourceCode union select * from flycatcher_46689..IndivSourceCode union select * from flycatcher_59894..IndivSourceCode union select * from fruitfly_7227..IndivSourceCode union select * from gorilla_9593..IndivSourceCode union select * from horse_9796..IndivSourceCode union select * from human_9606..IndivSourceCode union select * from macaque_9544..IndivSourceCode union select * from mosquito_7165..IndivSourceCode union select * from mouse_10090..IndivSourceCode union select * from mouse_spretus_10096..IndivSourceCode union select * from nematode_6239..IndivSourceCode union select * from orangutan_9600..IndivSourceCode union select * from pig_9823..IndivSourceCode union select * from pine_71647..IndivSourceCode union select * from plasmodium_5833..IndivSourceCode union select * from rat_10116..IndivSourceCode union select * from rat_10118..IndivSourceCode union select * from rice_4530..IndivSourceCode union select * from salmon_8030..IndivSourceCode union select * from sheep_9940..IndivSourceCode union select * from soybean_3847..IndivSourceCode union select * from sugarcane_128810..IndivSourceCode union select * from trematode_6183..IndivSourceCode union select * from trout_8022..IndivSourceCode union select * from tubeworm_216498..IndivSourceCode union select * from zebrafish_7955..IndivSourceCode +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + +create view vw_MapLink as +select * from bison_9901..vw_MapLink union +select * from chicken_9031..vw_MapLink union +select * from chimpanzee_9598..vw_MapLink union +select * from cow_30522..vw_MapLink union +select * from cow_9913..vw_MapLink union +select * from dog_9615..vw_MapLink union +select * from human_9606..vw_MapLink union +select * from mosquito_7165..vw_MapLink union +select * from mouse_10090..vw_MapLink union +select * from nematode_6239..vw_MapLink union +select * from rat_10116..vw_MapLink union +select * from rice_4530..vw_MapLink union +select * from zebrafish_7955..vw_MapLink + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_Pedigree as select * from arabidopsis_3702..Pedigree union select * from bee_7460..Pedigree union select * from bison_9901..Pedigree union select * from blackbird_39638..Pedigree union select * from bonobo_9597..Pedigree union select * from chicken_9031..Pedigree union select * from chimpanzee_9598..Pedigree union select * from cooperia_27828..Pedigree union select * from corn_4577..Pedigree union select * from cow_30522..Pedigree union select * from cow_9913..Pedigree union select * from dog_9615..Pedigree union select * from flycatcher_46689..Pedigree union select * from flycatcher_59894..Pedigree union select * from fruitfly_7227..Pedigree union select * from gorilla_9593..Pedigree union select * from horse_9796..Pedigree union select * from human_9606..Pedigree union select * from macaque_9544..Pedigree union select * from mosquito_7165..Pedigree union select * from mouse_10090..Pedigree union select * from mouse_spretus_10096..Pedigree union select * from nematode_6239..Pedigree union select * from orangutan_9600..Pedigree union select * from pig_9823..Pedigree union select * from pine_71647..Pedigree union select * from plasmodium_5833..Pedigree union select * from rat_10116..Pedigree union select * from rat_10118..Pedigree union select * from rice_4530..Pedigree union select * from salmon_8030..Pedigree union select * from sheep_9940..Pedigree union select * from soybean_3847..Pedigree union select * from sugarcane_128810..Pedigree union select * from trematode_6183..Pedigree union select * from trout_8022..Pedigree union select * from tubeworm_216498..Pedigree union select * from zebrafish_7955..Pedigree +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_Population as select * from arabidopsis_3702..Population union select * from bee_7460..Population union select * from bison_9901..Population union select * from blackbird_39638..Population union select * from bonobo_9597..Population union select * from chicken_9031..Population union select * from chimpanzee_9598..Population union select * from cooperia_27828..Population union select * from corn_4577..Population union select * from cow_30522..Population union select * from cow_9913..Population union select * from dog_9615..Population union select * from flycatcher_46689..Population union select * from flycatcher_59894..Population union select * from gorilla_9593..Population union select * from horse_9796..Population union select * from human_9606..Population union select * from macaque_9544..Population union select * from mosquito_7165..Population union select * from mouse_10090..Population union select * from mouse_spretus_10096..Population union select * from nematode_6239..Population union select * from orangutan_9600..Population union select * from pig_9823..Population union select * from pine_71647..Population union select * from plasmodium_5833..Population union select * from rat_10116..Population union select * from rat_10118..Population union select * from rice_4530..Population union select * from salmon_8030..Population union select * from sheep_9940..Population union select * from soybean_3847..Population union select * from sugarcane_128810..Population union select * from trematode_6183..Population union select * from tubeworm_216498..Population union select * from zebrafish_7955..Population +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS OFF +GO + +CREATE VIEW dbo.vw_ProteinInfo +AS +select * from cat_9685.dbo.b127_ProteinInfo_1_1 union +select * from zebrafish_7955.dbo.b127_ProteinInfo_0_0 union +select * from human_9606.dbo.b127_ProteinInfo_36_2 union +select * from bee_7460.dbo.b127_ProteinInfo_0_0 union +select * from rice_4530.dbo.b127_ProteinInfo_0_0 union +select * from mosquito_7165.dbo.b127_ProteinInfo_0_0 union +select * from plasmodium_5833.dbo.b127_ProteinInfo_0_0 union +select * from chicken_9031.dbo.b127_ProteinInfo_0_0 union +select * from cow_30522.dbo.b127_ProteinInfo_3_1 union +select * from chimpanzee_9598.dbo.b127_ProteinInfo_2_1 union +select * from cow_9913.dbo.b127_ProteinInfo_3_1 union +select * from nematode_6239.dbo.b127_ProteinInfo_0_0 union +select * from bison_9901.dbo.b127_ProteinInfo_3_1 + + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +CREATE VIEW dbo.vw_PubMedSNP +AS +SELECT DISTINCT snp_id_cited AS snp_id, pmid, 0 AS score +FROM dbSNP_oper.yuguo.PubMedSNP + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + +CREATE view vw_SNP3D as +select * from arabidopsis_3702..SNP3D union +select * from bee_7460..SNP3D union +select * from bison_9901..SNP3D union +select * from blackbird_39638..SNP3D union +select * from bonobo_9597..SNP3D union +select * from chicken_9031..SNP3D union +select * from chimpanzee_9598..SNP3D union +select * from cooperia_27828..SNP3D union +select * from corn_4577..SNP3D union +select * from cow_30522..SNP3D union + select * from cow_9913..SNP3D union +select * from dog_9615..SNP3D union +select * from flycatcher_46689..SNP3D union +select * from flycatcher_59894..SNP3D union +select * from gorilla_9593..SNP3D union +select * from horse_9796..SNP3D union +select * from human_9606..SNP3D union select * from macaque_9544..SNP3D union select * from mosquito_7165..SNP3D union select * from mouse_10090..SNP3D union select * from mouse_spretus_10096..SNP3D union select * from nematode_6239..SNP3D union select * from orangutan_9600..SNP3D union select * from pig_9823..SNP3D union select * from pine_71647..SNP3D union select * from plasmodium_5833..SNP3D union select * from rat_10116..SNP3D union select * from rat_10118..SNP3D union select * from rice_4530..SNP3D union select * from salmon_8030..SNP3D union select * from sheep_9940..SNP3D union select * from soybean_3847..SNP3D union select * from sugarcane_128810..SNP3D union select * from trematode_6183..SNP3D union select * from tubeworm_216498..SNP3D union select * from zebrafish_7955..SNP3D + + + + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + +CREATE view vw_SNPContigLoc as +select * from mosquito_7165..vw_SNPContigLoc +/* +union +select * from bee_7460..vw_SNPContigLoc + +union +select * from bison_9901..vw_SNPContigLoc + union +select * from cow_30522..vw_SNPContigLoc +union +select * from cow_9913..vw_SNPContigLoc +union +select * from nematode_6239..vw_SNPContigLoc +union +select * from dog_9615..vw_SNPContigLoc +union +select * from zebrafish_7955..vw_SNPContigLoc +union +select * from chicken_9031..vw_SNPContigLoc +union +select * from human_9606..vw_SNPContigLoc +union +select * from mouse_10090..vw_SNPContigLoc +union +select * from rice_4530..vw_SNPContigLoc +union +select * from chimpanzee_9598..vw_SNPContigLoc +union +select * from rat_10116..vw_SNPContigLoc + +*/ + + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + +CREATE view [dbo].[vw_SNPContigLocusId] as +select * from mosquito_7165..vw_SNPContigLocusId +union +select * from bee_7460..vw_SNPContigLocusId +union +select * from bison_9901..vw_SNPContigLocusId +union +select * from cow_30522..vw_SNPContigLocusId +union +select * from cow_9913..vw_SNPContigLocusId +union +select * from nematode_6239..vw_SNPContigLocusId +union +select * from dog_9615..vw_SNPContigLocusId +union +select * from zebrafish_7955..vw_SNPContigLocusId +union +select * from chicken_9031..vw_SNPContigLocusId +union +select * from human_9606..vw_SNPContigLocusId +union +select * from mouse_10090..vw_SNPContigLocusId +union +select * from rice_4530..vw_SNPContigLocusId +union +select * from chimpanzee_9598..vw_SNPContigLocusId +union +select * from rat_10116..vw_SNPContigLocusId + + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + +create view vw_SNPMapInfo as +select * from bison_9901..vw_SNPMapInfo union +select * from chicken_9031..vw_SNPMapInfo union +select * from chimpanzee_9598..vw_SNPMapInfo union +select * from cow_30522..vw_SNPMapInfo union +select * from cow_9913..vw_SNPMapInfo union +select * from dog_9615..vw_SNPMapInfo union +select * from human_9606..vw_SNPMapInfo union +select * from mosquito_7165..vw_SNPMapInfo union +select * from mouse_10090..vw_SNPMapInfo union +select * from nematode_6239..vw_SNPMapInfo union +select * from rat_10116..vw_SNPMapInfo union +select * from rice_4530..vw_SNPMapInfo union +select * from zebrafish_7955..vw_SNPMapInfo + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +CREATE VIEW dbo.vw_SNP_Organism +AS +SELECT DISTINCT + o.organism, o.tax_id, o.common_name, o.gpipe_abbr, o.create_time, o.last_updated_time, o.comment, o.division_cd, o.database_name, + o.short_common_name, o.tax_id_rank, o.species_tax_id, o.no_freq_summary, o.entrez_index, o.pub_genome_build +FROM dbo.SNP_tax_id AS s INNER JOIN + dbo.OrganismTax AS o ON s.tax_id = o.tax_id + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + +create view vw_SnpInSts as +select * from arabidopsis_3702..SnpInSts union +select * from bison_9901..SnpInSts union +select * from blackbird_39638..SnpInSts union +select * from bonobo_9597..SnpInSts union +select * from chicken_9031..SnpInSts union +select * from chimpanzee_9598..SnpInSts union +select * from cooperia_27828..SnpInSts union +select * from corn_4577..SnpInSts union +select * from cow_30522..SnpInSts union +select * from cow_9913..SnpInSts union +select * from dog_9615..SnpInSts union +select * from flycatcher_46689..SnpInSts union +select * from flycatcher_59894..SnpInSts union +select * from gorilla_9593..SnpInSts union +select * from horse_9796..SnpInSts union +select * from human_9606..SnpInSts union +select * from macaque_9544..SnpInSts union +select * from mosquito_7165..SnpInSts union +select * from mouse_10090..SnpInSts union +select * from mouse_spretus_10096..SnpInSts union +select * from nematode_6239..SnpInSts union +select * from orangutan_9600..SnpInSts union +select * from pig_9823..SnpInSts union +select * from pine_71647..SnpInSts union +select * from plasmodium_5833..SnpInSts union +select * from rat_10116..SnpInSts union +select * from rat_10118..SnpInSts union +select * from rice_4530..SnpInSts union +select * from salmon_8030..SnpInSts union +select * from sheep_9940..SnpInSts union +select * from soybean_3847..SnpInSts union +select * from sugarcane_128810..SnpInSts union +select * from trematode_6183..SnpInSts union +select * from tubeworm_216498..SnpInSts union +select * from zebrafish_7955..SnpInSts + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_SubSNPAcc as +select * from cow_30522..SubSNPAcc union +select * from cow_9913..SubSNPAcc union +select * from human_9606..SubSNPAcc union +select * from mouse_10090..SubSNPAcc union +select * from trout_8022..SubSNPAcc union +select * from arabidopsis_3702..SubSNPAcc union +select * from bison_9901..SubSNPAcc union +select * from blackbird_39638..SubSNPAcc union +select * from bonobo_9597..SubSNPAcc union +select * from chicken_9031..SubSNPAcc union +select * from chimpanzee_9598..SubSNPAcc union +select * from cooperia_27828..SubSNPAcc union +select * from corn_4577..SubSNPAcc union +select * from cow_30522..SubSNPAcc union +select * from cow_9913..SubSNPAcc union +select * from dog_9615..SubSNPAcc union +select * from flycatcher_46689..SubSNPAcc union +select * from flycatcher_59894..SubSNPAcc union +select * from gorilla_9593..SubSNPAcc union +select * from horse_9796..SubSNPAcc union +select * from human_9606..SubSNPAcc union +select * from macaque_9544..SubSNPAcc union +select * from mosquito_7165..SubSNPAcc union +select * from mouse_10090..SubSNPAcc union +select * from mouse_spretus_10096..SubSNPAcc union +select * from nematode_6239..SubSNPAcc union +select * from orangutan_9600..SubSNPAcc union +select * from pig_9823..SubSNPAcc union +select * from pine_71647..SubSNPAcc union +select * from plasmodium_5833..SubSNPAcc union +select * from rat_10116..SubSNPAcc union +select * from rat_10118..SubSNPAcc union +select * from rice_4530..SubSNPAcc union +select * from salmon_8030..SubSNPAcc union +select * from sheep_9940..SubSNPAcc union +select * from soybean_3847..SubSNPAcc union +select * from sugarcane_128810..SubSNPAcc union +select * from trematode_6183..SubSNPAcc union +select * from trout_8022..SubSNPAcc union +select * from tubeworm_216498..SubSNPAcc union +select * from zebrafish_7955..SubSNPAcc + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_SubmittedIndividual as select * from arabidopsis_3702..SubmittedIndividual union select * from bee_7460..SubmittedIndividual union select * from bison_9901..SubmittedIndividual union select * from blackbird_39638..SubmittedIndividual union select * from bonobo_9597..SubmittedIndividual union select * from chicken_9031..SubmittedIndividual union select * from chimpanzee_9598..SubmittedIndividual union select * from cooperia_27828..SubmittedIndividual union select * from corn_4577..SubmittedIndividual union select * from cow_30522..SubmittedIndividual union select * from cow_9913..SubmittedIndividual union select * from dog_9615..SubmittedIndividual union select * from flycatcher_46689..SubmittedIndividual union select * from flycatcher_59894..SubmittedIndividual union select * from fruitfly_7227..SubmittedIndividual union select * from gorilla_9593..SubmittedIndividual union select * from horse_9796..SubmittedIndividual union select * from human_9606..SubmittedIndividual union select * from macaque_9544..SubmittedIndividual union select * from mosquito_7165..SubmittedIndividual union select * from mouse_10090..SubmittedIndividual union select * from mouse_spretus_10096..SubmittedIndividual union select * from nematode_6239..SubmittedIndividual union select * from orangutan_9600..SubmittedIndividual union select * from pig_9823..SubmittedIndividual union select * from pine_71647..SubmittedIndividual union select * from plasmodium_5833..SubmittedIndividual union select * from rat_10116..SubmittedIndividual union select * from rat_10118..SubmittedIndividual union select * from rice_4530..SubmittedIndividual union select * from salmon_74940..SubmittedIndividual union select * from salmon_8030..SubmittedIndividual union select * from sheep_9940..SubmittedIndividual union select * from soybean_3847..SubmittedIndividual union select * from sugarcane_128810..SubmittedIndividual union select * from trematode_6183..SubmittedIndividual union select * from trout_8022..SubmittedIndividual union select * from tubeworm_216498..SubmittedIndividual union select * from zebrafish_7955..SubmittedIndividual +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +CREATE VIEW dbo.vw_Submitter +AS +SELECT * +FROM dbo.Submitter + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_Batch as select * from vw_all_Batch_part3 union all select * from vw_all_Batch_part2 union all select * from vw_all_Batch_part1 +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_BatchCita as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..BatchCita union all select *, 'bee_7460' as dbName from bee_7460..BatchCita union all select *, 'bison_9901' as dbName from bison_9901..BatchCita union all select *, 'blackbird_39638' as dbName from blackbird_39638..BatchCita union all select *, 'bonobo_9597' as dbName from bonobo_9597..BatchCita union all select *, 'chicken_9031' as dbName from chicken_9031..BatchCita union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..BatchCita union all select *, 'cooperia_27828' as dbName from cooperia_27828..BatchCita union all select *, 'corn_4577' as dbName from corn_4577..BatchCita union all select *, 'cow_30522' as dbName from cow_30522..BatchCita union all select *, 'cow_9913' as dbName from cow_9913..BatchCita union all select *, 'dog_9615' as dbName from dog_9615..BatchCita union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..BatchCita union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..BatchCita union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..BatchCita union all select *, 'gorilla_9593' as dbName from gorilla_9593..BatchCita union all select *, 'horse_9796' as dbName from horse_9796..BatchCita union all select *, 'human_9606' as dbName from human_9606..BatchCita union all select *, 'macaque_9544' as dbName from macaque_9544..BatchCita union all select *, 'mosquito_7165' as dbName from mosquito_7165..BatchCita union all select *, 'mouse_10090' as dbName from mouse_10090..BatchCita union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..BatchCita union all select *, 'nematode_6239' as dbName from nematode_6239..BatchCita union all select *, 'orangutan_9600' as dbName from orangutan_9600..BatchCita union all select *, 'pig_9823' as dbName from pig_9823..BatchCita union all select *, 'pine_71647' as dbName from pine_71647..BatchCita union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..BatchCita union all select *, 'rat_10116' as dbName from rat_10116..BatchCita union all select *, 'rat_10118' as dbName from rat_10118..BatchCita union all select *, 'rice_4530' as dbName from rice_4530..BatchCita union all select *, 'salmon_74940' as dbName from salmon_74940..BatchCita union all select *, 'salmon_8030' as dbName from salmon_8030..BatchCita union all select *, 'sheep_9940' as dbName from sheep_9940..BatchCita union all select *, 'soybean_3847' as dbName from soybean_3847..BatchCita union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..BatchCita union all select *, 'trematode_6183' as dbName from trematode_6183..BatchCita union all select *, 'trout_8022' as dbName from trout_8022..BatchCita union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..BatchCita union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..BatchCita uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_Batch_part1 as select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'arabidopsis_3702' as dbName from arabidopsis_3702..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'barrel_medic_3880' as dbName from barrel_medic_3880..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'bee_7460' as dbName from bee_7460..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'bison_9901' as dbName from bison_9901..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'blackbird_39638' as dbName from blackbird_39638..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'bonobo_9597' as dbName from bonobo_9597..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'cat_9685' as dbName from cat_9685..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'chicken_9031' as dbName from chicken_9031..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'chimpanzee_9598' as dbName from chimpanzee_9598..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'chimpanzee_9598' as dbName from chimpanzee_9598..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'chimpanzee_9598' as dbName from chimpanzee_9598..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'cooperia_27828' as dbName from cooperia_27828..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'corn_4577' as dbName from corn_4577..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'cow_30522' as dbName from cow_30522..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'cow_9913' as dbName from cow_9913..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'dog_9615' as dbName from dog_9615..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'flycatcher_46689' as dbName from flycatcher_46689..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'flycatcher_59894' as dbName from flycatcher_59894..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'fruitfly_7227' as dbName from fruitfly_7227..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'fusarium_5518' as dbName from fusarium_5518..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'gorilla_9593' as dbName from gorilla_9593..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'gorilla_9593' as dbName from gorilla_9593..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'grape_29760' as dbName from grape_29760..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'horse_9796' as dbName from horse_9796..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'human_9606' as dbName from human_9606..Batch +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_Batch_part2 as select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'macaque_9541' as dbName from macaque_9541..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'macaque_9544' as dbName from macaque_9544..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'mosquito_62324' as dbName from mosquito_62324..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'mosquito_7165' as dbName from mosquito_7165..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'mouse_10090' as dbName from mouse_10090..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'nematode_6238' as dbName from nematode_6238..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'nematode_6239' as dbName from nematode_6239..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'onion_4679' as dbName from onion_4679..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'opossum_13616' as dbName from opossum_13616..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'orangutan_9600' as dbName from orangutan_9600..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'pig_9823' as dbName from pig_9823..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'pine_71647' as dbName from pine_71647..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'plasmodium_5833' as dbName from plasmodium_5833..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'platypus_9258' as dbName from platypus_9258..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'rat_10116' as dbName from rat_10116..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'rat_10118' as dbName from rat_10118..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'rice_4530' as dbName from rice_4530..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_74940' as dbName from salmon_74940..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_8018' as dbName from salmon_8018..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_8019' as dbName from salmon_8019..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_8023' as dbName from salmon_8023..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_8030' as dbName from salmon_8030..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'sheep_9940' as dbName from sheep_9940..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'soybean_3847' as dbName from soybean_3847..Batch +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_Batch_part3 as select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'sugarcane_128810' as dbName from sugarcane_128810..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'tilapia_8128' as dbName from tilapia_8128..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'tomato_4081' as dbName from tomato_4081..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'trematode_6183' as dbName from trematode_6183..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'trout_8022' as dbName from trout_8022..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'tubeworm_216498' as dbName from tubeworm_216498..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'zebrafish_7955' as dbName from zebrafish_7955..Batch +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_IndivSourceCode as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..IndivSourceCode union all select *, 'bee_7460' as dbName from bee_7460..IndivSourceCode union all select *, 'bison_9901' as dbName from bison_9901..IndivSourceCode union all select *, 'blackbird_39638' as dbName from blackbird_39638..IndivSourceCode union all select *, 'bonobo_9597' as dbName from bonobo_9597..IndivSourceCode union all select *, 'chicken_9031' as dbName from chicken_9031..IndivSourceCode union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..IndivSourceCode union all select *, 'cooperia_27828' as dbName from cooperia_27828..IndivSourceCode union all select *, 'corn_4577' as dbName from corn_4577..IndivSourceCode union all select *, 'cow_30522' as dbName from cow_30522..IndivSourceCode union all select *, 'cow_9913' as dbName from cow_9913..IndivSourceCode union all select *, 'dog_9615' as dbName from dog_9615..IndivSourceCode union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..IndivSourceCode union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..IndivSourceCode union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..IndivSourceCode union all select *, 'gorilla_9593' as dbName from gorilla_9593..IndivSourceCode union all select *, 'horse_9796' as dbName from horse_9796..IndivSourceCode union all select *, 'human_9606' as dbName from human_9606..IndivSourceCode union all select *, 'macaque_9544' as dbName from macaque_9544..IndivSourceCode union all select *, 'mosquito_7165' as dbName from mosquito_7165..IndivSourceCode union all select *, 'mouse_10090' as dbName from mouse_10090..IndivSourceCode union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..IndivSourceCode union all select *, 'nematode_6239' as dbName from nematode_6239..IndivSourceCode union all select *, 'orangutan_9600' as dbName from orangutan_9600..IndivSourceCode union all select *, 'pig_9823' as dbName from pig_9823..IndivSourceCode union all select *, 'pine_71647' as dbName from pine_71647..IndivSourceCode union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..IndivSourceCode union all select *, 'rat_10116' as dbName from rat_10116..IndivSourceCode union all select *, 'rat_10118' as dbName from rat_10118..IndivSourceCode union all select *, 'rice_4530' as dbName from rice_4530..IndivSourceCode union all select *, 'salmon_74940' as dbName from salmon_74940..IndivSourceCode union all select *, 'salmon_8030' as dbName from salmon_8030..IndivSourceCode union all select *, 'sheep_9940' as dbName from sheep_9940..IndivSourceCode union all select *, 'soybean_3847' as dbName from soybean_3847..IndivSourceCode union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..IndivSourceCode union all select *, 'trematode_6183' as dbName from trematode_6183..IndivSourceCode union all select *, 'trout_8022' as dbName from trout_8022..IndivSourceCode union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..IndivSourceCode union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..IndivSourceCode uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_Individual as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..Individual union all select *, 'bee_7460' as dbName from bee_7460..Individual union all select *, 'bison_9901' as dbName from bison_9901..Individual union all select *, 'blackbird_39638' as dbName from blackbird_39638..Individual union all select *, 'bonobo_9597' as dbName from bonobo_9597..Individual union all select *, 'chicken_9031' as dbName from chicken_9031..Individual union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..Individual union all select *, 'cooperia_27828' as dbName from cooperia_27828..Individual union all select *, 'corn_4577' as dbName from corn_4577..Individual union all select *, 'cow_30522' as dbName from cow_30522..Individual union all select *, 'cow_9913' as dbName from cow_9913..Individual union all select *, 'dog_9615' as dbName from dog_9615..Individual union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..Individual union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..Individual union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..Individual union all select *, 'gorilla_9593' as dbName from gorilla_9593..Individual union all select *, 'horse_9796' as dbName from horse_9796..Individual union all select *, 'human_9606' as dbName from human_9606..Individual union all select *, 'macaque_9544' as dbName from macaque_9544..Individual union all select *, 'mosquito_7165' as dbName from mosquito_7165..Individual union all select *, 'mouse_10090' as dbName from mouse_10090..Individual union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..Individual union all select *, 'nematode_6239' as dbName from nematode_6239..Individual union all select *, 'onion_4679' as dbName from onion_4679..Individual union all select *, 'orangutan_9600' as dbName from orangutan_9600..Individual union all select *, 'pig_9823' as dbName from pig_9823..Individual union all select *, 'pine_71647' as dbName from pine_71647..Individual union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..Individual union all select *, 'rat_10116' as dbName from rat_10116..Individual union all select *, 'rat_10118' as dbName from rat_10118..Individual union all select *, 'rice_4530' as dbName from rice_4530..Individual union all select *, 'salmon_74940' as dbName from salmon_74940..Individual union all select *, 'salmon_8018' as dbName from salmon_8018..Individual union all select *, 'salmon_8019' as dbName from salmon_8019..Individual union all select *, 'salmon_8023' as dbName from salmon_8023..Individual union all select *, 'salmon_8030' as dbName from salmon_8030..Individual union all select *, 'sheep_9940' as dbName from sheep_9940..Individual union all select *, 'soybean_3847' as dbName from soybean_3847..Individual union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..Individual union all select *, 'tilapia_8128' as dbName from tilapia_8128..Individual union all select *, 'trematode_6183' as dbName from trematode_6183..Individual union all select *, 'trout_8022' as dbName from trout_8022..Individual union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..Individual union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..Individual uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_Pedigree as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..Pedigree union all select *, 'bee_7460' as dbName from bee_7460..Pedigree union all select *, 'bison_9901' as dbName from bison_9901..Pedigree union all select *, 'blackbird_39638' as dbName from blackbird_39638..Pedigree union all select *, 'bonobo_9597' as dbName from bonobo_9597..Pedigree union all select *, 'chicken_9031' as dbName from chicken_9031..Pedigree union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..Pedigree union all select *, 'cooperia_27828' as dbName from cooperia_27828..Pedigree union all select *, 'corn_4577' as dbName from corn_4577..Pedigree union all select *, 'cow_30522' as dbName from cow_30522..Pedigree union all select *, 'cow_9913' as dbName from cow_9913..Pedigree union all select *, 'dog_9615' as dbName from dog_9615..Pedigree union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..Pedigree union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..Pedigree union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..Pedigree union all select *, 'gorilla_9593' as dbName from gorilla_9593..Pedigree union all select *, 'horse_9796' as dbName from horse_9796..Pedigree union all select *, 'human_9606' as dbName from human_9606..Pedigree union all select *, 'macaque_9544' as dbName from macaque_9544..Pedigree union all select *, 'mosquito_7165' as dbName from mosquito_7165..Pedigree union all select *, 'mouse_10090' as dbName from mouse_10090..Pedigree union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..Pedigree union all select *, 'nematode_6239' as dbName from nematode_6239..Pedigree union all select *, 'onion_4679' as dbName from onion_4679..Pedigree union all select *, 'orangutan_9600' as dbName from orangutan_9600..Pedigree union all select *, 'pig_9823' as dbName from pig_9823..Pedigree union all select *, 'pine_71647' as dbName from pine_71647..Pedigree union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..Pedigree union all select *, 'rat_10116' as dbName from rat_10116..Pedigree union all select *, 'rat_10118' as dbName from rat_10118..Pedigree union all select *, 'rice_4530' as dbName from rice_4530..Pedigree union all select *, 'salmon_74940' as dbName from salmon_74940..Pedigree union all select *, 'salmon_8018' as dbName from salmon_8018..Pedigree union all select *, 'salmon_8019' as dbName from salmon_8019..Pedigree union all select *, 'salmon_8023' as dbName from salmon_8023..Pedigree union all select *, 'salmon_8030' as dbName from salmon_8030..Pedigree union all select *, 'sheep_9940' as dbName from sheep_9940..Pedigree union all select *, 'soybean_3847' as dbName from soybean_3847..Pedigree union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..Pedigree union all select *, 'tilapia_8128' as dbName from tilapia_8128..Pedigree union all select *, 'trematode_6183' as dbName from trematode_6183..Pedigree union all select *, 'trout_8022' as dbName from trout_8022..Pedigree union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..Pedigree union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..Pedigree uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_PopLine as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..PopLine union all select *, 'bee_7460' as dbName from bee_7460..PopLine union all select *, 'bison_9901' as dbName from bison_9901..PopLine union all select *, 'blackbird_39638' as dbName from blackbird_39638..PopLine union all select *, 'bonobo_9597' as dbName from bonobo_9597..PopLine union all select *, 'chicken_9031' as dbName from chicken_9031..PopLine union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..PopLine union all select *, 'cooperia_27828' as dbName from cooperia_27828..PopLine union all select *, 'corn_4577' as dbName from corn_4577..PopLine union all select *, 'cow_30522' as dbName from cow_30522..PopLine union all select *, 'cow_9913' as dbName from cow_9913..PopLine union all select *, 'dog_9615' as dbName from dog_9615..PopLine union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..PopLine union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..PopLine union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..PopLine union all select *, 'gorilla_9593' as dbName from gorilla_9593..PopLine union all select *, 'horse_9796' as dbName from horse_9796..PopLine union all select *, 'human_9606' as dbName from human_9606..PopLine union all select *, 'macaque_9544' as dbName from macaque_9544..PopLine union all select *, 'mosquito_7165' as dbName from mosquito_7165..PopLine union all select *, 'mouse_10090' as dbName from mouse_10090..PopLine union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..PopLine union all select *, 'nematode_6239' as dbName from nematode_6239..PopLine union all select *, 'orangutan_9600' as dbName from orangutan_9600..PopLine union all select *, 'pig_9823' as dbName from pig_9823..PopLine union all select *, 'pine_71647' as dbName from pine_71647..PopLine union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..PopLine union all select *, 'rat_10116' as dbName from rat_10116..PopLine union all select *, 'rat_10118' as dbName from rat_10118..PopLine union all select *, 'rice_4530' as dbName from rice_4530..PopLine union all select *, 'salmon_74940' as dbName from salmon_74940..PopLine union all select *, 'salmon_8030' as dbName from salmon_8030..PopLine union all select *, 'sheep_9940' as dbName from sheep_9940..PopLine union all select *, 'soybean_3847' as dbName from soybean_3847..PopLine union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..PopLine union all select *, 'trematode_6183' as dbName from trematode_6183..PopLine union all select *, 'trout_8022' as dbName from trout_8022..PopLine union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..PopLine union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..PopLine uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_Population as select * from vw_all_Population_part1 +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_Population_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..Population union all select *, 'bee_7460' as dbName from bee_7460..Population union all select *, 'bison_9901' as dbName from bison_9901..Population union all select *, 'blackbird_39638' as dbName from blackbird_39638..Population union all select *, 'bonobo_9597' as dbName from bonobo_9597..Population union all select *, 'chicken_9031' as dbName from chicken_9031..Population union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..Population union all select *, 'cooperia_27828' as dbName from cooperia_27828..Population union all select *, 'corn_4577' as dbName from corn_4577..Population union all select *, 'cow_30522' as dbName from cow_30522..Population union all select *, 'cow_9913' as dbName from cow_9913..Population union all select *, 'dog_9615' as dbName from dog_9615..Population union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..Population union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..Population union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..Population union all select *, 'gorilla_9593' as dbName from gorilla_9593..Population union all select *, 'horse_9796' as dbName from horse_9796..Population union all select *, 'human_9606' as dbName from human_9606..Population union all select *, 'macaque_9544' as dbName from macaque_9544..Population union all select *, 'mosquito_7165' as dbName from mosquito_7165..Population union all select *, 'mouse_10090' as dbName from mouse_10090..Population union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..Population union all select *, 'nematode_6239' as dbName from nematode_6239..Population union all select *, 'onion_4679' as dbName from onion_4679..Population union all select *, 'orangutan_9600' as dbName from orangutan_9600..Population union all select *, 'pig_9823' as dbName from pig_9823..Population union all select *, 'pine_71647' as dbName from pine_71647..Population union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..Population union all select *, 'rat_10116' as dbName from rat_10116..Population union all select *, 'rat_10118' as dbName from rat_10118..Population union all select *, 'rice_4530' as dbName from rice_4530..Population union all select *, 'salmon_74940' as dbName from salmon_74940..Population union all select *, 'salmon_8018' as dbName from salmon_8018..Population union all select *, 'salmon_8019' as dbName from salmon_8019..Population union all select *, 'salmon_8023' as dbName from salmon_8023..Population union all select *, 'salmon_8030' as dbName from salmon_8030..Population union all select *, 'sheep_9940' as dbName from sheep_9940..Population union all select *, 'soybean_3847' as dbName from soybean_3847..Population union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..Population union all select *, 'tilapia_8128' as dbName from tilapia_8128..Population union all select *, 'trematode_6183' as dbName from trematode_6183..Population union all select *, 'trout_8022' as dbName from trout_8022..Population union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..Population union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..Population +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_RsMergeArch as select * from vw_all_RsMergeArch_part1 +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_RsMergeArch_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..RsMergeArch union all select *, 'bee_7460' as dbName from bee_7460..RsMergeArch union all select *, 'bison_9901' as dbName from bison_9901..RsMergeArch union all select *, 'blackbird_39638' as dbName from blackbird_39638..RsMergeArch union all select *, 'bonobo_9597' as dbName from bonobo_9597..RsMergeArch union all select *, 'chicken_9031' as dbName from chicken_9031..RsMergeArch union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..RsMergeArch union all select *, 'cooperia_27828' as dbName from cooperia_27828..RsMergeArch union all select *, 'corn_4577' as dbName from corn_4577..RsMergeArch union all select *, 'cow_30522' as dbName from cow_30522..RsMergeArch union all select *, 'cow_9913' as dbName from cow_9913..RsMergeArch union all select *, 'dog_9615' as dbName from dog_9615..RsMergeArch union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..RsMergeArch union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..RsMergeArch union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..RsMergeArch union all select *, 'gorilla_9593' as dbName from gorilla_9593..RsMergeArch union all select *, 'horse_9796' as dbName from horse_9796..RsMergeArch union all select *, 'human_9606' as dbName from human_9606..RsMergeArch union all select *, 'macaque_9544' as dbName from macaque_9544..RsMergeArch union all select *, 'mosquito_7165' as dbName from mosquito_7165..RsMergeArch union all select *, 'mouse_10090' as dbName from mouse_10090..RsMergeArch union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..RsMergeArch union all select *, 'nematode_6239' as dbName from nematode_6239..RsMergeArch union all select *, 'onion_4679' as dbName from onion_4679..RsMergeArch union all select *, 'orangutan_9600' as dbName from orangutan_9600..RsMergeArch union all select *, 'pig_9823' as dbName from pig_9823..RsMergeArch union all select *, 'pine_71647' as dbName from pine_71647..RsMergeArch union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..RsMergeArch union all select *, 'rat_10116' as dbName from rat_10116..RsMergeArch union all select *, 'rat_10118' as dbName from rat_10118..RsMergeArch union all select *, 'rice_4530' as dbName from rice_4530..RsMergeArch union all select *, 'salmon_74940' as dbName from salmon_74940..RsMergeArch union all select *, 'salmon_8018' as dbName from salmon_8018..RsMergeArch union all select *, 'salmon_8019' as dbName from salmon_8019..RsMergeArch union all select *, 'salmon_8023' as dbName from salmon_8023..RsMergeArch union all select *, 'salmon_8030' as dbName from salmon_8030..RsMergeArch union all select *, 'sheep_9940' as dbName from sheep_9940..RsMergeArch union all select *, 'soybean_3847' as dbName from soybean_3847..RsMergeArch union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..RsMergeArch union all select *, 'tilapia_8128' as dbName from tilapia_8128..RsMergeArch union all select *, 'trematode_6183' as dbName from trematode_6183..RsMergeArch union all select *, 'trout_8022' as dbName from trout_8022..RsMergeArch union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..RsMergeArch union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..RsMergeArch +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_SNP as select * from vw_all_SNP_part1 +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_SNP_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..SNP union all select *, 'bee_7460' as dbName from bee_7460..SNP union all select *, 'bison_9901' as dbName from bison_9901..SNP union all select *, 'blackbird_39638' as dbName from blackbird_39638..SNP union all select *, 'bonobo_9597' as dbName from bonobo_9597..SNP union all select *, 'chicken_9031' as dbName from chicken_9031..SNP union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..SNP union all select *, 'cooperia_27828' as dbName from cooperia_27828..SNP union all select *, 'corn_4577' as dbName from corn_4577..SNP union all select *, 'cow_30522' as dbName from cow_30522..SNP union all select *, 'cow_9913' as dbName from cow_9913..SNP union all select *, 'dog_9615' as dbName from dog_9615..SNP union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..SNP union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..SNP union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..SNP union all select *, 'gorilla_9593' as dbName from gorilla_9593..SNP union all select *, 'horse_9796' as dbName from horse_9796..SNP union all select *, 'human_9606' as dbName from human_9606..SNP union all select *, 'macaque_9544' as dbName from macaque_9544..SNP union all select *, 'mosquito_7165' as dbName from mosquito_7165..SNP union all select *, 'mouse_10090' as dbName from mouse_10090..SNP union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..SNP union all select *, 'nematode_6239' as dbName from nematode_6239..SNP union all select *, 'onion_4679' as dbName from onion_4679..SNP union all select *, 'orangutan_9600' as dbName from orangutan_9600..SNP union all select *, 'pig_9823' as dbName from pig_9823..SNP union all select *, 'pine_71647' as dbName from pine_71647..SNP union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..SNP union all select *, 'rat_10116' as dbName from rat_10116..SNP union all select *, 'rat_10118' as dbName from rat_10118..SNP union all select *, 'rice_4530' as dbName from rice_4530..SNP union all select *, 'salmon_74940' as dbName from salmon_74940..SNP union all select *, 'salmon_8018' as dbName from salmon_8018..SNP union all select *, 'salmon_8019' as dbName from salmon_8019..SNP union all select *, 'salmon_8023' as dbName from salmon_8023..SNP union all select *, 'salmon_8030' as dbName from salmon_8030..SNP union all select *, 'sheep_9940' as dbName from sheep_9940..SNP union all select *, 'soybean_3847' as dbName from soybean_3847..SNP union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..SNP union all select *, 'tilapia_8128' as dbName from tilapia_8128..SNP union all select *, 'trematode_6183' as dbName from trematode_6183..SNP union all select *, 'trout_8022' as dbName from trout_8022..SNP union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..SNP union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..SNP +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_SubPop as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..SubPop union all select *, 'bee_7460' as dbName from bee_7460..SubPop union all select *, 'bison_9901' as dbName from bison_9901..SubPop union all select *, 'blackbird_39638' as dbName from blackbird_39638..SubPop union all select *, 'bonobo_9597' as dbName from bonobo_9597..SubPop union all select *, 'chicken_9031' as dbName from chicken_9031..SubPop union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..SubPop union all select *, 'cooperia_27828' as dbName from cooperia_27828..SubPop union all select *, 'corn_4577' as dbName from corn_4577..SubPop union all select *, 'cow_30522' as dbName from cow_30522..SubPop union all select *, 'cow_9913' as dbName from cow_9913..SubPop union all select *, 'dog_9615' as dbName from dog_9615..SubPop union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..SubPop union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..SubPop union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..SubPop union all select *, 'gorilla_9593' as dbName from gorilla_9593..SubPop union all select *, 'horse_9796' as dbName from horse_9796..SubPop union all select *, 'human_9606' as dbName from human_9606..SubPop union all select *, 'macaque_9544' as dbName from macaque_9544..SubPop union all select *, 'mosquito_7165' as dbName from mosquito_7165..SubPop union all select *, 'mouse_10090' as dbName from mouse_10090..SubPop union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..SubPop union all select *, 'nematode_6239' as dbName from nematode_6239..SubPop union all select *, 'orangutan_9600' as dbName from orangutan_9600..SubPop union all select *, 'pig_9823' as dbName from pig_9823..SubPop union all select *, 'pine_71647' as dbName from pine_71647..SubPop union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..SubPop union all select *, 'rat_10116' as dbName from rat_10116..SubPop union all select *, 'rat_10118' as dbName from rat_10118..SubPop union all select *, 'rice_4530' as dbName from rice_4530..SubPop union all select *, 'salmon_74940' as dbName from salmon_74940..SubPop union all select *, 'salmon_8030' as dbName from salmon_8030..SubPop union all select *, 'sheep_9940' as dbName from sheep_9940..SubPop union all select *, 'soybean_3847' as dbName from soybean_3847..SubPop union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..SubPop union all select *, 'trematode_6183' as dbName from trematode_6183..SubPop union all select *, 'trout_8022' as dbName from trout_8022..SubPop union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..SubPop union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..SubPop uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_SubSNP as select * from arabidopsis_3702..SubSNP union all select * from bee_7460..SubSNP union all select * from bison_9901..SubSNP union all select * from blackbird_39638..SubSNP union all select * from bonobo_9597..SubSNP union all select * from chicken_9031..SubSNP union all select * from chimpanzee_9598..SubSNP union all select * from cooperia_27828..SubSNP union all select * from corn_4577..SubSNP union all select * from cow_30522..SubSNP union all select * from cow_9913..SubSNP union all select * from dog_9615..SubSNP union all select * from flycatcher_46689..SubSNP union all select * from flycatcher_59894..SubSNP union all select * from fruitfly_7227..SubSNP union all select * from gorilla_9593..SubSNP union all select * from horse_9796..SubSNP union all select * from human_9606..SubSNP union all select * from macaque_9544..SubSNP union all select * from mosquito_7165..SubSNP union all select * from mouse_10090..SubSNP union all select * from mouse_spretus_10096..SubSNP union all select * from nematode_6239..SubSNP union all select * from orangutan_9600..SubSNP union all select * from pig_9823..SubSNP union all select * from pine_71647..SubSNP union all select * from plasmodium_5833..SubSNP union all select * from rat_10116..SubSNP union all select * from rat_10118..SubSNP union all select * from rice_4530..SubSNP union all select * from salmon_74940..SubSNP union all select * from salmon_8030..SubSNP union all select * from sheep_9940..SubSNP union all select * from soybean_3847..SubSNP union all select * from sugarcane_128810..SubSNP union all select * from trematode_6183..SubSNP union all select * from trout_8022..SubSNP union all select * from tubeworm_216498..SubSNP union all select * from zebrafish_7955..SubSNP uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_SubSNPDeletedBySubmitter as select * from vw_all_SubSNPDeletedBySubmitter_part2 union all select * from vw_all_SubSNPDeletedBySubmitter_part1 +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_SubSNPDeletedBySubmitter_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..SubSNPDeletedBySubmitter union all select *, 'bee_7460' as dbName from bee_7460..SubSNPDeletedBySubmitter union all select *, 'bison_9901' as dbName from bison_9901..SubSNPDeletedBySubmitter union all select *, 'blackbird_39638' as dbName from blackbird_39638..SubSNPDeletedBySubmitter union all select *, 'bonobo_9597' as dbName from bonobo_9597..SubSNPDeletedBySubmitter union all select *, 'chicken_9031' as dbName from chicken_9031..SubSNPDeletedBySubmitter union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..SubSNPDeletedBySubmitter union all select *, 'cooperia_27828' as dbName from cooperia_27828..SubSNPDeletedBySubmitter union all select *, 'corn_4577' as dbName from corn_4577..SubSNPDeletedBySubmitter union all select *, 'cow_30522' as dbName from cow_30522..SubSNPDeletedBySubmitter union all select *, 'cow_9913' as dbName from cow_9913..SubSNPDeletedBySubmitter union all select *, 'dog_9615' as dbName from dog_9615..SubSNPDeletedBySubmitter union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..SubSNPDeletedBySubmitter union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..SubSNPDeletedBySubmitter union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..SubSNPDeletedBySubmitter union all select *, 'gorilla_9593' as dbName from gorilla_9593..SubSNPDeletedBySubmitter union all select *, 'horse_9796' as dbName from horse_9796..SubSNPDeletedBySubmitter union all select *, 'human_9606' as dbName from human_9606..SubSNPDeletedBySubmitter union all select *, 'macaque_9544' as dbName from macaque_9544..SubSNPDeletedBySubmitter union all select *, 'mosquito_62324' as dbName from mosquito_62324..SubSNPDeletedBySubmitter union all select *, 'mosquito_7165' as dbName from mosquito_7165..SubSNPDeletedBySubmitter union all select *, 'mouse_10090' as dbName from mouse_10090..SubSNPDeletedBySubmitter union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..SubSNPDeletedBySubmitter union all select *, 'nematode_6239' as dbName from nematode_6239..SubSNPDeletedBySubmitter union all select *, 'onion_4679' as dbName from onion_4679..SubSNPDeletedBySubmitter union all select *, 'orangutan_9600' as dbName from orangutan_9600..SubSNPDeletedBySubmitter union all select *, 'pig_9823' as dbName from pig_9823..SubSNPDeletedBySubmitter union all select *, 'pine_71647' as dbName from pine_71647..SubSNPDeletedBySubmitter union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..SubSNPDeletedBySubmitter union all select *, 'rat_10116' as dbName from rat_10116..SubSNPDeletedBySubmitter union all select *, 'rat_10118' as dbName from rat_10118..SubSNPDeletedBySubmitter union all select *, 'rice_4530' as dbName from rice_4530..SubSNPDeletedBySubmitter union all select *, 'salmon_74940' as dbName from salmon_74940..SubSNPDeletedBySubmitter union all select *, 'salmon_8018' as dbName from salmon_8018..SubSNPDeletedBySubmitter union all select *, 'salmon_8019' as dbName from salmon_8019..SubSNPDeletedBySubmitter union all select *, 'salmon_8023' as dbName from salmon_8023..SubSNPDeletedBySubmitter union all select *, 'salmon_8030' as dbName from salmon_8030..SubSNPDeletedBySubmitter union all select *, 'sheep_9940' as dbName from sheep_9940..SubSNPDeletedBySubmitter union all select *, 'soybean_3847' as dbName from soybean_3847..SubSNPDeletedBySubmitter union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..SubSNPDeletedBySubmitter union all select *, 'tilapia_8128' as dbName from tilapia_8128..SubSNPDeletedBySubmitter union all select *, 'trematode_6183' as dbName from trematode_6183..SubSNPDeletedBySubmitter +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_SubSNPDeletedBySubmitter_part2 as select *, 'trout_8022' as dbName from trout_8022..SubSNPDeletedBySubmitter union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..SubSNPDeletedBySubmitter union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..SubSNPDeletedBySubmitter +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_SubmittedIndividual as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..SubmittedIndividual union all select *, 'bee_7460' as dbName from bee_7460..SubmittedIndividual union all select *, 'bison_9901' as dbName from bison_9901..SubmittedIndividual union all select *, 'blackbird_39638' as dbName from blackbird_39638..SubmittedIndividual union all select *, 'bonobo_9597' as dbName from bonobo_9597..SubmittedIndividual union all select *, 'chicken_9031' as dbName from chicken_9031..SubmittedIndividual union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..SubmittedIndividual union all select *, 'cooperia_27828' as dbName from cooperia_27828..SubmittedIndividual union all select *, 'corn_4577' as dbName from corn_4577..SubmittedIndividual union all select *, 'cow_30522' as dbName from cow_30522..SubmittedIndividual union all select *, 'cow_9913' as dbName from cow_9913..SubmittedIndividual union all select *, 'dog_9615' as dbName from dog_9615..SubmittedIndividual union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..SubmittedIndividual union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..SubmittedIndividual union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..SubmittedIndividual union all select *, 'gorilla_9593' as dbName from gorilla_9593..SubmittedIndividual union all select *, 'horse_9796' as dbName from horse_9796..SubmittedIndividual union all select *, 'human_9606' as dbName from human_9606..SubmittedIndividual union all select *, 'macaque_9544' as dbName from macaque_9544..SubmittedIndividual union all select *, 'mosquito_7165' as dbName from mosquito_7165..SubmittedIndividual union all select *, 'mouse_10090' as dbName from mouse_10090..SubmittedIndividual union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..SubmittedIndividual union all select *, 'nematode_6239' as dbName from nematode_6239..SubmittedIndividual union all select *, 'orangutan_9600' as dbName from orangutan_9600..SubmittedIndividual union all select *, 'pig_9823' as dbName from pig_9823..SubmittedIndividual union all select *, 'pine_71647' as dbName from pine_71647..SubmittedIndividual union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..SubmittedIndividual union all select *, 'rat_10116' as dbName from rat_10116..SubmittedIndividual union all select *, 'rat_10118' as dbName from rat_10118..SubmittedIndividual union all select *, 'rice_4530' as dbName from rice_4530..SubmittedIndividual union all select *, 'salmon_74940' as dbName from salmon_74940..SubmittedIndividual union all select *, 'salmon_8018' as dbName from salmon_8018..SubmittedIndividual union all select *, 'salmon_8019' as dbName from salmon_8019..SubmittedIndividual union all select *, 'salmon_8023' as dbName from salmon_8023..SubmittedIndividual union all select *, 'salmon_8030' as dbName from salmon_8030..SubmittedIndividual union all select *, 'sheep_9940' as dbName from sheep_9940..SubmittedIndividual union all select *, 'soybean_3847' as dbName from soybean_3847..SubmittedIndividual union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..SubmittedIndividual union all select *, 'trematode_6183' as dbName from trematode_6183..SubmittedIndividual union all select *, 'trout_8022' as dbName from trout_8022..SubmittedIndividual union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..SubmittedIndividual union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..SubmittedIndividual uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_dn_batchCount as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..dn_batchCount union all select *, 'bee_7460' as dbName from bee_7460..dn_batchCount union all select *, 'bison_9901' as dbName from bison_9901..dn_batchCount union all select *, 'blackbird_39638' as dbName from blackbird_39638..dn_batchCount union all select *, 'bonobo_9597' as dbName from bonobo_9597..dn_batchCount union all select *, 'chicken_9031' as dbName from chicken_9031..dn_batchCount union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..dn_batchCount union all select *, 'cooperia_27828' as dbName from cooperia_27828..dn_batchCount union all select *, 'corn_4577' as dbName from corn_4577..dn_batchCount union all select *, 'cow_30522' as dbName from cow_30522..dn_batchCount union all select *, 'cow_9913' as dbName from cow_9913..dn_batchCount union all select *, 'dog_9615' as dbName from dog_9615..dn_batchCount union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..dn_batchCount union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..dn_batchCount union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..dn_batchCount union all select *, 'gorilla_9593' as dbName from gorilla_9593..dn_batchCount union all select *, 'horse_9796' as dbName from horse_9796..dn_batchCount union all select *, 'human_9606' as dbName from human_9606..dn_batchCount union all select *, 'macaque_9544' as dbName from macaque_9544..dn_batchCount union all select *, 'mosquito_7165' as dbName from mosquito_7165..dn_batchCount union all select *, 'mouse_10090' as dbName from mouse_10090..dn_batchCount union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..dn_batchCount union all select *, 'nematode_6239' as dbName from nematode_6239..dn_batchCount union all select *, 'orangutan_9600' as dbName from orangutan_9600..dn_batchCount union all select *, 'pig_9823' as dbName from pig_9823..dn_batchCount union all select *, 'pine_71647' as dbName from pine_71647..dn_batchCount union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..dn_batchCount union all select *, 'rat_10116' as dbName from rat_10116..dn_batchCount union all select *, 'rat_10118' as dbName from rat_10118..dn_batchCount union all select *, 'rice_4530' as dbName from rice_4530..dn_batchCount union all select *, 'salmon_74940' as dbName from salmon_74940..dn_batchCount union all select *, 'salmon_8018' as dbName from salmon_8018..dn_batchCount union all select *, 'salmon_8019' as dbName from salmon_8019..dn_batchCount union all select *, 'salmon_8023' as dbName from salmon_8023..dn_batchCount union all select *, 'salmon_8030' as dbName from salmon_8030..dn_batchCount union all select *, 'sheep_9940' as dbName from sheep_9940..dn_batchCount union all select *, 'soybean_3847' as dbName from soybean_3847..dn_batchCount union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..dn_batchCount union all select *, 'trematode_6183' as dbName from trematode_6183..dn_batchCount union all select *, 'trout_8022' as dbName from trout_8022..dn_batchCount union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..dn_batchCount union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..dn_batchCount uni +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_dn_handleCount as select * from vw_all_dn_handleCount_part2 union all select * from vw_all_dn_handleCount_part1 +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_dn_handleCount_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..dn_handleCount union all select *, 'barrel_medic_3880' as dbName from barrel_medic_3880..dn_handleCount union all select *, 'bee_7460' as dbName from bee_7460..dn_handleCount union all select *, 'bison_9901' as dbName from bison_9901..dn_handleCount union all select *, 'blackbird_39638' as dbName from blackbird_39638..dn_handleCount union all select *, 'bonobo_9597' as dbName from bonobo_9597..dn_handleCount union all select *, 'cat_9685' as dbName from cat_9685..dn_handleCount union all select *, 'chicken_9031' as dbName from chicken_9031..dn_handleCount union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..dn_handleCount union all select *, 'cooperia_27828' as dbName from cooperia_27828..dn_handleCount union all select *, 'corn_4577' as dbName from corn_4577..dn_handleCount union all select *, 'cow_30522' as dbName from cow_30522..dn_handleCount union all select *, 'cow_9913' as dbName from cow_9913..dn_handleCount union all select *, 'dog_9615' as dbName from dog_9615..dn_handleCount union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..dn_handleCount union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..dn_handleCount union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..dn_handleCount union all select *, 'fusarium_5518' as dbName from fusarium_5518..dn_handleCount union all select *, 'goat_9925' as dbName from goat_9925..dn_handleCount union all select *, 'gorilla_9593' as dbName from gorilla_9593..dn_handleCount union all select *, 'grape_29760' as dbName from grape_29760..dn_handleCount union all select *, 'horse_9796' as dbName from horse_9796..dn_handleCount union all select *, 'human_9606' as dbName from human_9606..dn_handleCount union all select *, 'macaque_9541' as dbName from macaque_9541..dn_handleCount union all select *, 'macaque_9544' as dbName from macaque_9544..dn_handleCount union all select *, 'mosquito_62324' as dbName from mosquito_62324..dn_handleCount union all select *, 'mosquito_7165' as dbName from mosquito_7165..dn_handleCount union all select *, 'mouse_10090' as dbName from mouse_10090..dn_handleCount union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..dn_handleCount union all select *, 'nematode_51029' as dbName from nematode_51029..dn_handleCount union all select *, 'nematode_6238' as dbName from nematode_6238..dn_handleCount union all select *, 'nematode_6239' as dbName from nematode_6239..dn_handleCount union all select *, 'onion_4679' as dbName from onion_4679..dn_handleCount union all select *, 'opossum_13616' as dbName from opossum_13616..dn_handleCount union all select *, 'orangutan_9600' as dbName from orangutan_9600..dn_handleCount union all select *, 'pig_9823' as dbName from pig_9823..dn_handleCount union all select *, 'pine_71647' as dbName from pine_71647..dn_handleCount union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..dn_handleCount union all select *, 'platypus_9258' as dbName from platypus_9258..dn_handleCount union all select *, 'rat_10116' as dbName from rat_10116..dn_handleCount union all select *, 'rat_10118' as dbName from rat_10118..dn_handleCount union all select *, 'rice_4530' as dbName from rice_4530..dn_handleCount union all select *, 'salmon_74940' as dbName from salmon_74940..dn_handleCount union all select *, 'salmon_8018' as dbName from salmon_8018..dn_handleCount union all select *, 'salmon_8019' as dbName from salmon_8019..dn_handleCount union all select *, 'salmon_8023' as dbName from salmon_8023..dn_handleCount union all select *, 'salmon_8030' as dbName from salmon_8030..dn_handleCount +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS OFF +GO + + create view vw_all_dn_handleCount_part2 as select *, 'sheep_9940' as dbName from sheep_9940..dn_handleCount union all select *, 'soybean_3847' as dbName from soybean_3847..dn_handleCount union all select *, 'staphylococcus_1280' as dbName from staphylococcus_1280..dn_handleCount union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..dn_handleCount union all select *, 'tilapia_8128' as dbName from tilapia_8128..dn_handleCount union all select *, 'tomato_4081' as dbName from tomato_4081..dn_handleCount union all select *, 'trematode_6183' as dbName from trematode_6183..dn_handleCount union all select *, 'trout_8022' as dbName from trout_8022..dn_handleCount union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..dn_handleCount union all select *, 'white_shrimp_6689' as dbName from white_shrimp_6689..dn_handleCount union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..dn_handleCount union all select *, 'zostera_29655' as dbName from zostera_29655..dn_handleCount +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO + +CREATE VIEW dbo.vw_snp_nucleotide +AS +select * from human_9606..vw_snp_nucleotide + +/* +select * from mosquito_7165..vw_snp_nucleotide +union +select * from bee_7460..vw_snp_nucleotide +union +select * from bison_9901..vw_snp_nucleotide + union +select * from cow_30522..vw_snp_nucleotide +union +select * from cow_9913..vw_snp_nucleotide +union +select * from nematode_6239..vw_snp_nucleotide +union +select * from dog_9615..vw_snp_nucleotide +union +select * from zebrafish_7955..vw_snp_nucleotide +union +select * from chicken_9031..vw_snp_nucleotide +union +select * from mouse_10090..vw_snp_nucleotide +union +select * from rice_4530..vw_snp_nucleotide +union +select * from chimpanzee_9598..vw_snp_nucleotide +union +select * from rat_10116..vw_snp_nucleotide + +*/ + + +GO +SET QUOTED_IDENTIFIER OFF +GO +SET ANSI_NULLS ON +GO + + diff --git a/schema/shared_schema/dbSNP_main_view_postgresql.sql b/schema/shared_schema/dbSNP_main_view_postgresql.sql new file mode 100644 index 0000000..c5f71a7 --- /dev/null +++ b/schema/shared_schema/dbSNP_main_view_postgresql.sql @@ -0,0 +1,791 @@ +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +create view vwAllele +as +select a.allele_id, a.rev_allele_id, a.allele, r.allele rev_allele +from dbo.Allele a (index (i_rev_allele_id)) join dbo.Allele r (index (pk_Allele)) on a.rev_allele_id = r.allele_id + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +create view vwAllele_rev_flag (allele_id, rev_flag, fwd_allele_id, fwd_allele) +as +select allele_id, 0, allele_id, allele From Allele +union +select allele_id, 1, rev_allele_id, rev_allele from vwAllele + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +create view vwMotif +as + +select distinct m.motif_id, m.motif, r.motif_id rev_motif_id, r.motif rev_motif, m.last_updated_time +from Motif m join Motif r on m.motif_id = r.rev_motif_id + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +create view vwMotif_rev ( motif_id, rev_flag, fwd_motif, fwd_motif_id) +as +/* log: 11/4/04 updated. when ref_flag=1, fwd_motif_id should be motif_id! + */ +select motif_id, 0, motif, motif_id from Motif +union +select motif_id, 1, rev_motif, rev_motif_id from vwMotif + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +CREATE VIEW dbo.vw_ABI_RsMergeArch +AS +SELECT human_9606.dbo.RsMergeArch.* FROM human_9606.dbo.RsMergeArch union +SELECT mouse_10090.dbo.RsMergeArch.* FROM mouse_10090.dbo.RsMergeArch + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +CREATE VIEW dbo.vw_ABI_SNP +AS +SELECT ss.* FROM human_9606.dbo.SNP ss union +SELECT ss.* FROM mouse_10090.dbo.SNP ss +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; +CREATE VIEW dbo.vw_ABI_SubSNP +AS +SELECT ss.* +FROM human_9606.dbo.SubSNP ss, human_9606.dbo.Batch b where b.handle = 'ABI' and b.batch_id = ss.batch_id union +SELECT ss.* +FROM mouse_10090.dbo.SubSNP ss, mouse_10090.dbo.Batch b where b.handle = 'ABI' and b.batch_id = ss.batch_id + + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; +create view vw_BatchCita as +select * from arabidopsis_3702..BatchCita union +select * from bison_9901..BatchCita union +select * from blackbird_39638..BatchCita union +select * from bonobo_9597..BatchCita union +select * from chicken_9031..BatchCita union +select * from chimpanzee_9598..BatchCita union +select * from cooperia_27828..BatchCita union +select * from corn_4577..BatchCita union +select * from cow_30522..BatchCita union +select * from cow_9913..BatchCita union +select * from dog_9615..BatchCita union +select * from flycatcher_46689..BatchCita union +select * from flycatcher_59894..BatchCita union +select * from gorilla_9593..BatchCita union +select * from horse_9796..BatchCita union +select * from human_9606..BatchCita union +select * from macaque_9544..BatchCita union +select * from mosquito_7165..BatchCita union +select * from mouse_10090..BatchCita union +select * from mouse_spretus_10096..BatchCita union +select * from nematode_6239..BatchCita union +select * from orangutan_9600..BatchCita union +select * from pig_9823..BatchCita union +select * from pine_71647..BatchCita union +select * from plasmodium_5833..BatchCita union +select * from rat_10116..BatchCita union +select * from rat_10118..BatchCita union +select * from rice_4530..BatchCita union +select * from salmon_8030..BatchCita union +select * from sheep_9940..BatchCita union +select * from soybean_3847..BatchCita union +select * from sugarcane_128810..BatchCita union +select * from trematode_6183..BatchCita union +select * from tubeworm_216498..BatchCita union +select * from zebrafish_7955..BatchCita + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; +create view vw_ContigExon as +select * from bison_9901..vw_ContigExon union +select * from chicken_9031..vw_ContigExon union +select * from chimpanzee_9598..vw_ContigExon union +select * from cow_30522..vw_ContigExon union +select * from cow_9913..vw_ContigExon union +select * from dog_9615..vw_ContigExon union +select * from human_9606..vw_ContigExon union +select * from mosquito_7165..vw_ContigExon union +select * from mouse_10090..vw_ContigExon union +select * from nematode_6239..vw_ContigExon union +select * from rat_10116..vw_ContigExon union +select * from rice_4530..vw_ContigExon union +select * from zebrafish_7955..vw_ContigExon + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; +create view vw_ContigInfo as +select * from bison_9901..vw_ContigInfo union +select * from chicken_9031..vw_ContigInfo union +select * from chimpanzee_9598..vw_ContigInfo union +select * from cow_30522..vw_ContigInfo union +select * from cow_9913..vw_ContigInfo union +select * from dog_9615..vw_ContigInfo union +select * from human_9606..vw_ContigInfo union +select * from mosquito_7165..vw_ContigInfo union +select * from mouse_10090..vw_ContigInfo union +select * from nematode_6239..vw_ContigInfo union +select * from rat_10116..vw_ContigInfo union +select * from rice_4530..vw_ContigInfo union +select * from zebrafish_7955..vw_ContigInfo + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; +CREATE VIEW dbo.vw_E_Maplink +AS +select * from bison_9901..vw_MapLink union +select * from chicken_9031..vw_MapLink union +select * from chimpanzee_9598..vw_MapLink union +select * from cow_30522..vw_MapLink union +select * from cow_9913..vw_MapLink union +select * from dog_9615..vw_MapLink union +select * from human_9606..vw_E_MapLink union +select * from mosquito_7165..vw_MapLink union +select * from mouse_10090..vw_MapLink union +select * from nematode_6239..vw_MapLink union +select * from rat_10116..vw_MapLink union +select * from rice_4530..vw_MapLink union +select * from zebrafish_7955..vw_MapLink + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_IndivSourceCode as select * from arabidopsis_3702..IndivSourceCode union select * from bee_7460..IndivSourceCode union select * from bison_9901..IndivSourceCode union select * from blackbird_39638..IndivSourceCode union select * from bonobo_9597..IndivSourceCode union select * from chicken_9031..IndivSourceCode union select * from chimpanzee_9598..IndivSourceCode union select * from cooperia_27828..IndivSourceCode union select * from corn_4577..IndivSourceCode union select * from cow_30522..IndivSourceCode union select * from cow_9913..IndivSourceCode union select * from dog_9615..IndivSourceCode union select * from flycatcher_46689..IndivSourceCode union select * from flycatcher_59894..IndivSourceCode union select * from fruitfly_7227..IndivSourceCode union select * from gorilla_9593..IndivSourceCode union select * from horse_9796..IndivSourceCode union select * from human_9606..IndivSourceCode union select * from macaque_9544..IndivSourceCode union select * from mosquito_7165..IndivSourceCode union select * from mouse_10090..IndivSourceCode union select * from mouse_spretus_10096..IndivSourceCode union select * from nematode_6239..IndivSourceCode union select * from orangutan_9600..IndivSourceCode union select * from pig_9823..IndivSourceCode union select * from pine_71647..IndivSourceCode union select * from plasmodium_5833..IndivSourceCode union select * from rat_10116..IndivSourceCode union select * from rat_10118..IndivSourceCode union select * from rice_4530..IndivSourceCode union select * from salmon_8030..IndivSourceCode union select * from sheep_9940..IndivSourceCode union select * from soybean_3847..IndivSourceCode union select * from sugarcane_128810..IndivSourceCode union select * from trematode_6183..IndivSourceCode union select * from trout_8022..IndivSourceCode union select * from tubeworm_216498..IndivSourceCode union select * from zebrafish_7955..IndivSourceCode +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; +create view vw_MapLink as +select * from bison_9901..vw_MapLink union +select * from chicken_9031..vw_MapLink union +select * from chimpanzee_9598..vw_MapLink union +select * from cow_30522..vw_MapLink union +select * from cow_9913..vw_MapLink union +select * from dog_9615..vw_MapLink union +select * from human_9606..vw_MapLink union +select * from mosquito_7165..vw_MapLink union +select * from mouse_10090..vw_MapLink union +select * from nematode_6239..vw_MapLink union +select * from rat_10116..vw_MapLink union +select * from rice_4530..vw_MapLink union +select * from zebrafish_7955..vw_MapLink + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_Pedigree as select * from arabidopsis_3702..Pedigree union select * from bee_7460..Pedigree union select * from bison_9901..Pedigree union select * from blackbird_39638..Pedigree union select * from bonobo_9597..Pedigree union select * from chicken_9031..Pedigree union select * from chimpanzee_9598..Pedigree union select * from cooperia_27828..Pedigree union select * from corn_4577..Pedigree union select * from cow_30522..Pedigree union select * from cow_9913..Pedigree union select * from dog_9615..Pedigree union select * from flycatcher_46689..Pedigree union select * from flycatcher_59894..Pedigree union select * from fruitfly_7227..Pedigree union select * from gorilla_9593..Pedigree union select * from horse_9796..Pedigree union select * from human_9606..Pedigree union select * from macaque_9544..Pedigree union select * from mosquito_7165..Pedigree union select * from mouse_10090..Pedigree union select * from mouse_spretus_10096..Pedigree union select * from nematode_6239..Pedigree union select * from orangutan_9600..Pedigree union select * from pig_9823..Pedigree union select * from pine_71647..Pedigree union select * from plasmodium_5833..Pedigree union select * from rat_10116..Pedigree union select * from rat_10118..Pedigree union select * from rice_4530..Pedigree union select * from salmon_8030..Pedigree union select * from sheep_9940..Pedigree union select * from soybean_3847..Pedigree union select * from sugarcane_128810..Pedigree union select * from trematode_6183..Pedigree union select * from trout_8022..Pedigree union select * from tubeworm_216498..Pedigree union select * from zebrafish_7955..Pedigree +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_Population as select * from arabidopsis_3702..Population union select * from bee_7460..Population union select * from bison_9901..Population union select * from blackbird_39638..Population union select * from bonobo_9597..Population union select * from chicken_9031..Population union select * from chimpanzee_9598..Population union select * from cooperia_27828..Population union select * from corn_4577..Population union select * from cow_30522..Population union select * from cow_9913..Population union select * from dog_9615..Population union select * from flycatcher_46689..Population union select * from flycatcher_59894..Population union select * from gorilla_9593..Population union select * from horse_9796..Population union select * from human_9606..Population union select * from macaque_9544..Population union select * from mosquito_7165..Population union select * from mouse_10090..Population union select * from mouse_spretus_10096..Population union select * from nematode_6239..Population union select * from orangutan_9600..Population union select * from pig_9823..Population union select * from pine_71647..Population union select * from plasmodium_5833..Population union select * from rat_10116..Population union select * from rat_10118..Population union select * from rice_4530..Population union select * from salmon_8030..Population union select * from sheep_9940..Population union select * from soybean_3847..Population union select * from sugarcane_128810..Population union select * from trematode_6183..Population union select * from tubeworm_216498..Population union select * from zebrafish_7955..Population +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS OFF +; +CREATE VIEW dbo.vw_ProteinInfo +AS +select * from cat_9685.dbo.b127_ProteinInfo_1_1 union +select * from zebrafish_7955.dbo.b127_ProteinInfo_0_0 union +select * from human_9606.dbo.b127_ProteinInfo_36_2 union +select * from bee_7460.dbo.b127_ProteinInfo_0_0 union +select * from rice_4530.dbo.b127_ProteinInfo_0_0 union +select * from mosquito_7165.dbo.b127_ProteinInfo_0_0 union +select * from plasmodium_5833.dbo.b127_ProteinInfo_0_0 union +select * from chicken_9031.dbo.b127_ProteinInfo_0_0 union +select * from cow_30522.dbo.b127_ProteinInfo_3_1 union +select * from chimpanzee_9598.dbo.b127_ProteinInfo_2_1 union +select * from cow_9913.dbo.b127_ProteinInfo_3_1 union +select * from nematode_6239.dbo.b127_ProteinInfo_0_0 union +select * from bison_9901.dbo.b127_ProteinInfo_3_1 + + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +CREATE VIEW dbo.vw_PubMedSNP +AS +SELECT DISTINCT snp_id_cited AS snp_id, pmid, 0 AS score +FROM dbSNP_oper.yuguo.PubMedSNP + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; +CREATE view vw_SNP3D as +select * from arabidopsis_3702..SNP3D union +select * from bee_7460..SNP3D union +select * from bison_9901..SNP3D union +select * from blackbird_39638..SNP3D union +select * from bonobo_9597..SNP3D union +select * from chicken_9031..SNP3D union +select * from chimpanzee_9598..SNP3D union +select * from cooperia_27828..SNP3D union +select * from corn_4577..SNP3D union +select * from cow_30522..SNP3D union + select * from cow_9913..SNP3D union +select * from dog_9615..SNP3D union +select * from flycatcher_46689..SNP3D union +select * from flycatcher_59894..SNP3D union +select * from gorilla_9593..SNP3D union +select * from horse_9796..SNP3D union +select * from human_9606..SNP3D union select * from macaque_9544..SNP3D union select * from mosquito_7165..SNP3D union select * from mouse_10090..SNP3D union select * from mouse_spretus_10096..SNP3D union select * from nematode_6239..SNP3D union select * from orangutan_9600..SNP3D union select * from pig_9823..SNP3D union select * from pine_71647..SNP3D union select * from plasmodium_5833..SNP3D union select * from rat_10116..SNP3D union select * from rat_10118..SNP3D union select * from rice_4530..SNP3D union select * from salmon_8030..SNP3D union select * from sheep_9940..SNP3D union select * from soybean_3847..SNP3D union select * from sugarcane_128810..SNP3D union select * from trematode_6183..SNP3D union select * from tubeworm_216498..SNP3D union select * from zebrafish_7955..SNP3D + + + + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; +CREATE view vw_SNPContigLoc as +select * from mosquito_7165..vw_SNPContigLoc +/* +union +select * from bee_7460..vw_SNPContigLoc + +union +select * from bison_9901..vw_SNPContigLoc + union +select * from cow_30522..vw_SNPContigLoc +union +select * from cow_9913..vw_SNPContigLoc +union +select * from nematode_6239..vw_SNPContigLoc +union +select * from dog_9615..vw_SNPContigLoc +union +select * from zebrafish_7955..vw_SNPContigLoc +union +select * from chicken_9031..vw_SNPContigLoc +union +select * from human_9606..vw_SNPContigLoc +union +select * from mouse_10090..vw_SNPContigLoc +union +select * from rice_4530..vw_SNPContigLoc +union +select * from chimpanzee_9598..vw_SNPContigLoc +union +select * from rat_10116..vw_SNPContigLoc + +*/ + + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; +CREATE view "dbo"."vw_SNPContigLocusId" as +select * from mosquito_7165..vw_SNPContigLocusId +union +select * from bee_7460..vw_SNPContigLocusId +union +select * from bison_9901..vw_SNPContigLocusId +union +select * from cow_30522..vw_SNPContigLocusId +union +select * from cow_9913..vw_SNPContigLocusId +union +select * from nematode_6239..vw_SNPContigLocusId +union +select * from dog_9615..vw_SNPContigLocusId +union +select * from zebrafish_7955..vw_SNPContigLocusId +union +select * from chicken_9031..vw_SNPContigLocusId +union +select * from human_9606..vw_SNPContigLocusId +union +select * from mouse_10090..vw_SNPContigLocusId +union +select * from rice_4530..vw_SNPContigLocusId +union +select * from chimpanzee_9598..vw_SNPContigLocusId +union +select * from rat_10116..vw_SNPContigLocusId + + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; +create view vw_SNPMapInfo as +select * from bison_9901..vw_SNPMapInfo union +select * from chicken_9031..vw_SNPMapInfo union +select * from chimpanzee_9598..vw_SNPMapInfo union +select * from cow_30522..vw_SNPMapInfo union +select * from cow_9913..vw_SNPMapInfo union +select * from dog_9615..vw_SNPMapInfo union +select * from human_9606..vw_SNPMapInfo union +select * from mosquito_7165..vw_SNPMapInfo union +select * from mouse_10090..vw_SNPMapInfo union +select * from nematode_6239..vw_SNPMapInfo union +select * from rat_10116..vw_SNPMapInfo union +select * from rice_4530..vw_SNPMapInfo union +select * from zebrafish_7955..vw_SNPMapInfo + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +CREATE VIEW dbo.vw_SNP_Organism +AS +SELECT DISTINCT + o.organism, o.tax_id, o.common_name, o.gpipe_abbr, o.create_time, o.last_updated_time, o.comment, o.division_cd, o.database_name, + o.short_common_name, o.tax_id_rank, o.species_tax_id, o.no_freq_summary, o.entrez_index, o.pub_genome_build +FROM dbo.SNP_tax_id AS s INNER JOIN + dbo.OrganismTax AS o ON s.tax_id = o.tax_id + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; +create view vw_SnpInSts as +select * from arabidopsis_3702..SnpInSts union +select * from bison_9901..SnpInSts union +select * from blackbird_39638..SnpInSts union +select * from bonobo_9597..SnpInSts union +select * from chicken_9031..SnpInSts union +select * from chimpanzee_9598..SnpInSts union +select * from cooperia_27828..SnpInSts union +select * from corn_4577..SnpInSts union +select * from cow_30522..SnpInSts union +select * from cow_9913..SnpInSts union +select * from dog_9615..SnpInSts union +select * from flycatcher_46689..SnpInSts union +select * from flycatcher_59894..SnpInSts union +select * from gorilla_9593..SnpInSts union +select * from horse_9796..SnpInSts union +select * from human_9606..SnpInSts union +select * from macaque_9544..SnpInSts union +select * from mosquito_7165..SnpInSts union +select * from mouse_10090..SnpInSts union +select * from mouse_spretus_10096..SnpInSts union +select * from nematode_6239..SnpInSts union +select * from orangutan_9600..SnpInSts union +select * from pig_9823..SnpInSts union +select * from pine_71647..SnpInSts union +select * from plasmodium_5833..SnpInSts union +select * from rat_10116..SnpInSts union +select * from rat_10118..SnpInSts union +select * from rice_4530..SnpInSts union +select * from salmon_8030..SnpInSts union +select * from sheep_9940..SnpInSts union +select * from soybean_3847..SnpInSts union +select * from sugarcane_128810..SnpInSts union +select * from trematode_6183..SnpInSts union +select * from tubeworm_216498..SnpInSts union +select * from zebrafish_7955..SnpInSts + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_SubSNPAcc as +select * from cow_30522..SubSNPAcc union +select * from cow_9913..SubSNPAcc union +select * from human_9606..SubSNPAcc union +select * from mouse_10090..SubSNPAcc union +select * from trout_8022..SubSNPAcc union +select * from arabidopsis_3702..SubSNPAcc union +select * from bison_9901..SubSNPAcc union +select * from blackbird_39638..SubSNPAcc union +select * from bonobo_9597..SubSNPAcc union +select * from chicken_9031..SubSNPAcc union +select * from chimpanzee_9598..SubSNPAcc union +select * from cooperia_27828..SubSNPAcc union +select * from corn_4577..SubSNPAcc union +select * from cow_30522..SubSNPAcc union +select * from cow_9913..SubSNPAcc union +select * from dog_9615..SubSNPAcc union +select * from flycatcher_46689..SubSNPAcc union +select * from flycatcher_59894..SubSNPAcc union +select * from gorilla_9593..SubSNPAcc union +select * from horse_9796..SubSNPAcc union +select * from human_9606..SubSNPAcc union +select * from macaque_9544..SubSNPAcc union +select * from mosquito_7165..SubSNPAcc union +select * from mouse_10090..SubSNPAcc union +select * from mouse_spretus_10096..SubSNPAcc union +select * from nematode_6239..SubSNPAcc union +select * from orangutan_9600..SubSNPAcc union +select * from pig_9823..SubSNPAcc union +select * from pine_71647..SubSNPAcc union +select * from plasmodium_5833..SubSNPAcc union +select * from rat_10116..SubSNPAcc union +select * from rat_10118..SubSNPAcc union +select * from rice_4530..SubSNPAcc union +select * from salmon_8030..SubSNPAcc union +select * from sheep_9940..SubSNPAcc union +select * from soybean_3847..SubSNPAcc union +select * from sugarcane_128810..SubSNPAcc union +select * from trematode_6183..SubSNPAcc union +select * from trout_8022..SubSNPAcc union +select * from tubeworm_216498..SubSNPAcc union +select * from zebrafish_7955..SubSNPAcc + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_SubmittedIndividual as select * from arabidopsis_3702..SubmittedIndividual union select * from bee_7460..SubmittedIndividual union select * from bison_9901..SubmittedIndividual union select * from blackbird_39638..SubmittedIndividual union select * from bonobo_9597..SubmittedIndividual union select * from chicken_9031..SubmittedIndividual union select * from chimpanzee_9598..SubmittedIndividual union select * from cooperia_27828..SubmittedIndividual union select * from corn_4577..SubmittedIndividual union select * from cow_30522..SubmittedIndividual union select * from cow_9913..SubmittedIndividual union select * from dog_9615..SubmittedIndividual union select * from flycatcher_46689..SubmittedIndividual union select * from flycatcher_59894..SubmittedIndividual union select * from fruitfly_7227..SubmittedIndividual union select * from gorilla_9593..SubmittedIndividual union select * from horse_9796..SubmittedIndividual union select * from human_9606..SubmittedIndividual union select * from macaque_9544..SubmittedIndividual union select * from mosquito_7165..SubmittedIndividual union select * from mouse_10090..SubmittedIndividual union select * from mouse_spretus_10096..SubmittedIndividual union select * from nematode_6239..SubmittedIndividual union select * from orangutan_9600..SubmittedIndividual union select * from pig_9823..SubmittedIndividual union select * from pine_71647..SubmittedIndividual union select * from plasmodium_5833..SubmittedIndividual union select * from rat_10116..SubmittedIndividual union select * from rat_10118..SubmittedIndividual union select * from rice_4530..SubmittedIndividual union select * from salmon_74940..SubmittedIndividual union select * from salmon_8030..SubmittedIndividual union select * from sheep_9940..SubmittedIndividual union select * from soybean_3847..SubmittedIndividual union select * from sugarcane_128810..SubmittedIndividual union select * from trematode_6183..SubmittedIndividual union select * from trout_8022..SubmittedIndividual union select * from tubeworm_216498..SubmittedIndividual union select * from zebrafish_7955..SubmittedIndividual +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +CREATE VIEW dbo.vw_Submitter +AS +SELECT * +FROM dbo.Submitter + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_Batch as select * from vw_all_Batch_part3 union all select * from vw_all_Batch_part2 union all select * from vw_all_Batch_part1 +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_BatchCita as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..BatchCita union all select *, 'bee_7460' as dbName from bee_7460..BatchCita union all select *, 'bison_9901' as dbName from bison_9901..BatchCita union all select *, 'blackbird_39638' as dbName from blackbird_39638..BatchCita union all select *, 'bonobo_9597' as dbName from bonobo_9597..BatchCita union all select *, 'chicken_9031' as dbName from chicken_9031..BatchCita union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..BatchCita union all select *, 'cooperia_27828' as dbName from cooperia_27828..BatchCita union all select *, 'corn_4577' as dbName from corn_4577..BatchCita union all select *, 'cow_30522' as dbName from cow_30522..BatchCita union all select *, 'cow_9913' as dbName from cow_9913..BatchCita union all select *, 'dog_9615' as dbName from dog_9615..BatchCita union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..BatchCita union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..BatchCita union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..BatchCita union all select *, 'gorilla_9593' as dbName from gorilla_9593..BatchCita union all select *, 'horse_9796' as dbName from horse_9796..BatchCita union all select *, 'human_9606' as dbName from human_9606..BatchCita union all select *, 'macaque_9544' as dbName from macaque_9544..BatchCita union all select *, 'mosquito_7165' as dbName from mosquito_7165..BatchCita union all select *, 'mouse_10090' as dbName from mouse_10090..BatchCita union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..BatchCita union all select *, 'nematode_6239' as dbName from nematode_6239..BatchCita union all select *, 'orangutan_9600' as dbName from orangutan_9600..BatchCita union all select *, 'pig_9823' as dbName from pig_9823..BatchCita union all select *, 'pine_71647' as dbName from pine_71647..BatchCita union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..BatchCita union all select *, 'rat_10116' as dbName from rat_10116..BatchCita union all select *, 'rat_10118' as dbName from rat_10118..BatchCita union all select *, 'rice_4530' as dbName from rice_4530..BatchCita union all select *, 'salmon_74940' as dbName from salmon_74940..BatchCita union all select *, 'salmon_8030' as dbName from salmon_8030..BatchCita union all select *, 'sheep_9940' as dbName from sheep_9940..BatchCita union all select *, 'soybean_3847' as dbName from soybean_3847..BatchCita union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..BatchCita union all select *, 'trematode_6183' as dbName from trematode_6183..BatchCita union all select *, 'trout_8022' as dbName from trout_8022..BatchCita union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..BatchCita union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..BatchCita uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_Batch_part1 as select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'arabidopsis_3702' as dbName from arabidopsis_3702..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'barrel_medic_3880' as dbName from barrel_medic_3880..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'bee_7460' as dbName from bee_7460..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'bison_9901' as dbName from bison_9901..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'blackbird_39638' as dbName from blackbird_39638..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'bonobo_9597' as dbName from bonobo_9597..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'cat_9685' as dbName from cat_9685..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'chicken_9031' as dbName from chicken_9031..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'chimpanzee_9598' as dbName from chimpanzee_9598..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'chimpanzee_9598' as dbName from chimpanzee_9598..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'chimpanzee_9598' as dbName from chimpanzee_9598..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'cooperia_27828' as dbName from cooperia_27828..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'corn_4577' as dbName from corn_4577..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'cow_30522' as dbName from cow_30522..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'cow_9913' as dbName from cow_9913..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'dog_9615' as dbName from dog_9615..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'flycatcher_46689' as dbName from flycatcher_46689..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'flycatcher_59894' as dbName from flycatcher_59894..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'fruitfly_7227' as dbName from fruitfly_7227..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'fusarium_5518' as dbName from fusarium_5518..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'gorilla_9593' as dbName from gorilla_9593..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'gorilla_9593' as dbName from gorilla_9593..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'grape_29760' as dbName from grape_29760..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'horse_9796' as dbName from horse_9796..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'human_9606' as dbName from human_9606..Batch +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_Batch_part2 as select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'macaque_9541' as dbName from macaque_9541..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'macaque_9544' as dbName from macaque_9544..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'mosquito_62324' as dbName from mosquito_62324..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'mosquito_7165' as dbName from mosquito_7165..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'mouse_10090' as dbName from mouse_10090..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'nematode_6238' as dbName from nematode_6238..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'nematode_6239' as dbName from nematode_6239..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'onion_4679' as dbName from onion_4679..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'opossum_13616' as dbName from opossum_13616..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'orangutan_9600' as dbName from orangutan_9600..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'pig_9823' as dbName from pig_9823..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'pine_71647' as dbName from pine_71647..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'plasmodium_5833' as dbName from plasmodium_5833..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'platypus_9258' as dbName from platypus_9258..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'rat_10116' as dbName from rat_10116..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'rat_10118' as dbName from rat_10118..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'rice_4530' as dbName from rice_4530..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_74940' as dbName from salmon_74940..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_8018' as dbName from salmon_8018..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_8019' as dbName from salmon_8019..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_8023' as dbName from salmon_8023..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'salmon_8030' as dbName from salmon_8030..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'sheep_9940' as dbName from sheep_9940..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'soybean_3847' as dbName from soybean_3847..Batch +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_Batch_part3 as select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'sugarcane_128810' as dbName from sugarcane_128810..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'tilapia_8128' as dbName from tilapia_8128..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'tomato_4081' as dbName from tomato_4081..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'trematode_6183' as dbName from trematode_6183..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'trout_8022' as dbName from trout_8022..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'tubeworm_216498' as dbName from tubeworm_216498..Batch union all select batch_id,batch_type,handle,loc_batch_id,tax_id,build_id, method_id, submitted_time, 'zebrafish_7955' as dbName from zebrafish_7955..Batch +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_IndivSourceCode as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..IndivSourceCode union all select *, 'bee_7460' as dbName from bee_7460..IndivSourceCode union all select *, 'bison_9901' as dbName from bison_9901..IndivSourceCode union all select *, 'blackbird_39638' as dbName from blackbird_39638..IndivSourceCode union all select *, 'bonobo_9597' as dbName from bonobo_9597..IndivSourceCode union all select *, 'chicken_9031' as dbName from chicken_9031..IndivSourceCode union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..IndivSourceCode union all select *, 'cooperia_27828' as dbName from cooperia_27828..IndivSourceCode union all select *, 'corn_4577' as dbName from corn_4577..IndivSourceCode union all select *, 'cow_30522' as dbName from cow_30522..IndivSourceCode union all select *, 'cow_9913' as dbName from cow_9913..IndivSourceCode union all select *, 'dog_9615' as dbName from dog_9615..IndivSourceCode union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..IndivSourceCode union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..IndivSourceCode union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..IndivSourceCode union all select *, 'gorilla_9593' as dbName from gorilla_9593..IndivSourceCode union all select *, 'horse_9796' as dbName from horse_9796..IndivSourceCode union all select *, 'human_9606' as dbName from human_9606..IndivSourceCode union all select *, 'macaque_9544' as dbName from macaque_9544..IndivSourceCode union all select *, 'mosquito_7165' as dbName from mosquito_7165..IndivSourceCode union all select *, 'mouse_10090' as dbName from mouse_10090..IndivSourceCode union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..IndivSourceCode union all select *, 'nematode_6239' as dbName from nematode_6239..IndivSourceCode union all select *, 'orangutan_9600' as dbName from orangutan_9600..IndivSourceCode union all select *, 'pig_9823' as dbName from pig_9823..IndivSourceCode union all select *, 'pine_71647' as dbName from pine_71647..IndivSourceCode union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..IndivSourceCode union all select *, 'rat_10116' as dbName from rat_10116..IndivSourceCode union all select *, 'rat_10118' as dbName from rat_10118..IndivSourceCode union all select *, 'rice_4530' as dbName from rice_4530..IndivSourceCode union all select *, 'salmon_74940' as dbName from salmon_74940..IndivSourceCode union all select *, 'salmon_8030' as dbName from salmon_8030..IndivSourceCode union all select *, 'sheep_9940' as dbName from sheep_9940..IndivSourceCode union all select *, 'soybean_3847' as dbName from soybean_3847..IndivSourceCode union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..IndivSourceCode union all select *, 'trematode_6183' as dbName from trematode_6183..IndivSourceCode union all select *, 'trout_8022' as dbName from trout_8022..IndivSourceCode union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..IndivSourceCode union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..IndivSourceCode uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_Individual as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..Individual union all select *, 'bee_7460' as dbName from bee_7460..Individual union all select *, 'bison_9901' as dbName from bison_9901..Individual union all select *, 'blackbird_39638' as dbName from blackbird_39638..Individual union all select *, 'bonobo_9597' as dbName from bonobo_9597..Individual union all select *, 'chicken_9031' as dbName from chicken_9031..Individual union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..Individual union all select *, 'cooperia_27828' as dbName from cooperia_27828..Individual union all select *, 'corn_4577' as dbName from corn_4577..Individual union all select *, 'cow_30522' as dbName from cow_30522..Individual union all select *, 'cow_9913' as dbName from cow_9913..Individual union all select *, 'dog_9615' as dbName from dog_9615..Individual union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..Individual union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..Individual union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..Individual union all select *, 'gorilla_9593' as dbName from gorilla_9593..Individual union all select *, 'horse_9796' as dbName from horse_9796..Individual union all select *, 'human_9606' as dbName from human_9606..Individual union all select *, 'macaque_9544' as dbName from macaque_9544..Individual union all select *, 'mosquito_7165' as dbName from mosquito_7165..Individual union all select *, 'mouse_10090' as dbName from mouse_10090..Individual union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..Individual union all select *, 'nematode_6239' as dbName from nematode_6239..Individual union all select *, 'onion_4679' as dbName from onion_4679..Individual union all select *, 'orangutan_9600' as dbName from orangutan_9600..Individual union all select *, 'pig_9823' as dbName from pig_9823..Individual union all select *, 'pine_71647' as dbName from pine_71647..Individual union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..Individual union all select *, 'rat_10116' as dbName from rat_10116..Individual union all select *, 'rat_10118' as dbName from rat_10118..Individual union all select *, 'rice_4530' as dbName from rice_4530..Individual union all select *, 'salmon_74940' as dbName from salmon_74940..Individual union all select *, 'salmon_8018' as dbName from salmon_8018..Individual union all select *, 'salmon_8019' as dbName from salmon_8019..Individual union all select *, 'salmon_8023' as dbName from salmon_8023..Individual union all select *, 'salmon_8030' as dbName from salmon_8030..Individual union all select *, 'sheep_9940' as dbName from sheep_9940..Individual union all select *, 'soybean_3847' as dbName from soybean_3847..Individual union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..Individual union all select *, 'tilapia_8128' as dbName from tilapia_8128..Individual union all select *, 'trematode_6183' as dbName from trematode_6183..Individual union all select *, 'trout_8022' as dbName from trout_8022..Individual union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..Individual union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..Individual uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_Pedigree as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..Pedigree union all select *, 'bee_7460' as dbName from bee_7460..Pedigree union all select *, 'bison_9901' as dbName from bison_9901..Pedigree union all select *, 'blackbird_39638' as dbName from blackbird_39638..Pedigree union all select *, 'bonobo_9597' as dbName from bonobo_9597..Pedigree union all select *, 'chicken_9031' as dbName from chicken_9031..Pedigree union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..Pedigree union all select *, 'cooperia_27828' as dbName from cooperia_27828..Pedigree union all select *, 'corn_4577' as dbName from corn_4577..Pedigree union all select *, 'cow_30522' as dbName from cow_30522..Pedigree union all select *, 'cow_9913' as dbName from cow_9913..Pedigree union all select *, 'dog_9615' as dbName from dog_9615..Pedigree union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..Pedigree union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..Pedigree union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..Pedigree union all select *, 'gorilla_9593' as dbName from gorilla_9593..Pedigree union all select *, 'horse_9796' as dbName from horse_9796..Pedigree union all select *, 'human_9606' as dbName from human_9606..Pedigree union all select *, 'macaque_9544' as dbName from macaque_9544..Pedigree union all select *, 'mosquito_7165' as dbName from mosquito_7165..Pedigree union all select *, 'mouse_10090' as dbName from mouse_10090..Pedigree union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..Pedigree union all select *, 'nematode_6239' as dbName from nematode_6239..Pedigree union all select *, 'onion_4679' as dbName from onion_4679..Pedigree union all select *, 'orangutan_9600' as dbName from orangutan_9600..Pedigree union all select *, 'pig_9823' as dbName from pig_9823..Pedigree union all select *, 'pine_71647' as dbName from pine_71647..Pedigree union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..Pedigree union all select *, 'rat_10116' as dbName from rat_10116..Pedigree union all select *, 'rat_10118' as dbName from rat_10118..Pedigree union all select *, 'rice_4530' as dbName from rice_4530..Pedigree union all select *, 'salmon_74940' as dbName from salmon_74940..Pedigree union all select *, 'salmon_8018' as dbName from salmon_8018..Pedigree union all select *, 'salmon_8019' as dbName from salmon_8019..Pedigree union all select *, 'salmon_8023' as dbName from salmon_8023..Pedigree union all select *, 'salmon_8030' as dbName from salmon_8030..Pedigree union all select *, 'sheep_9940' as dbName from sheep_9940..Pedigree union all select *, 'soybean_3847' as dbName from soybean_3847..Pedigree union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..Pedigree union all select *, 'tilapia_8128' as dbName from tilapia_8128..Pedigree union all select *, 'trematode_6183' as dbName from trematode_6183..Pedigree union all select *, 'trout_8022' as dbName from trout_8022..Pedigree union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..Pedigree union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..Pedigree uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_PopLine as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..PopLine union all select *, 'bee_7460' as dbName from bee_7460..PopLine union all select *, 'bison_9901' as dbName from bison_9901..PopLine union all select *, 'blackbird_39638' as dbName from blackbird_39638..PopLine union all select *, 'bonobo_9597' as dbName from bonobo_9597..PopLine union all select *, 'chicken_9031' as dbName from chicken_9031..PopLine union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..PopLine union all select *, 'cooperia_27828' as dbName from cooperia_27828..PopLine union all select *, 'corn_4577' as dbName from corn_4577..PopLine union all select *, 'cow_30522' as dbName from cow_30522..PopLine union all select *, 'cow_9913' as dbName from cow_9913..PopLine union all select *, 'dog_9615' as dbName from dog_9615..PopLine union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..PopLine union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..PopLine union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..PopLine union all select *, 'gorilla_9593' as dbName from gorilla_9593..PopLine union all select *, 'horse_9796' as dbName from horse_9796..PopLine union all select *, 'human_9606' as dbName from human_9606..PopLine union all select *, 'macaque_9544' as dbName from macaque_9544..PopLine union all select *, 'mosquito_7165' as dbName from mosquito_7165..PopLine union all select *, 'mouse_10090' as dbName from mouse_10090..PopLine union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..PopLine union all select *, 'nematode_6239' as dbName from nematode_6239..PopLine union all select *, 'orangutan_9600' as dbName from orangutan_9600..PopLine union all select *, 'pig_9823' as dbName from pig_9823..PopLine union all select *, 'pine_71647' as dbName from pine_71647..PopLine union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..PopLine union all select *, 'rat_10116' as dbName from rat_10116..PopLine union all select *, 'rat_10118' as dbName from rat_10118..PopLine union all select *, 'rice_4530' as dbName from rice_4530..PopLine union all select *, 'salmon_74940' as dbName from salmon_74940..PopLine union all select *, 'salmon_8030' as dbName from salmon_8030..PopLine union all select *, 'sheep_9940' as dbName from sheep_9940..PopLine union all select *, 'soybean_3847' as dbName from soybean_3847..PopLine union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..PopLine union all select *, 'trematode_6183' as dbName from trematode_6183..PopLine union all select *, 'trout_8022' as dbName from trout_8022..PopLine union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..PopLine union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..PopLine uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_Population as select * from vw_all_Population_part1 +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_Population_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..Population union all select *, 'bee_7460' as dbName from bee_7460..Population union all select *, 'bison_9901' as dbName from bison_9901..Population union all select *, 'blackbird_39638' as dbName from blackbird_39638..Population union all select *, 'bonobo_9597' as dbName from bonobo_9597..Population union all select *, 'chicken_9031' as dbName from chicken_9031..Population union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..Population union all select *, 'cooperia_27828' as dbName from cooperia_27828..Population union all select *, 'corn_4577' as dbName from corn_4577..Population union all select *, 'cow_30522' as dbName from cow_30522..Population union all select *, 'cow_9913' as dbName from cow_9913..Population union all select *, 'dog_9615' as dbName from dog_9615..Population union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..Population union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..Population union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..Population union all select *, 'gorilla_9593' as dbName from gorilla_9593..Population union all select *, 'horse_9796' as dbName from horse_9796..Population union all select *, 'human_9606' as dbName from human_9606..Population union all select *, 'macaque_9544' as dbName from macaque_9544..Population union all select *, 'mosquito_7165' as dbName from mosquito_7165..Population union all select *, 'mouse_10090' as dbName from mouse_10090..Population union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..Population union all select *, 'nematode_6239' as dbName from nematode_6239..Population union all select *, 'onion_4679' as dbName from onion_4679..Population union all select *, 'orangutan_9600' as dbName from orangutan_9600..Population union all select *, 'pig_9823' as dbName from pig_9823..Population union all select *, 'pine_71647' as dbName from pine_71647..Population union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..Population union all select *, 'rat_10116' as dbName from rat_10116..Population union all select *, 'rat_10118' as dbName from rat_10118..Population union all select *, 'rice_4530' as dbName from rice_4530..Population union all select *, 'salmon_74940' as dbName from salmon_74940..Population union all select *, 'salmon_8018' as dbName from salmon_8018..Population union all select *, 'salmon_8019' as dbName from salmon_8019..Population union all select *, 'salmon_8023' as dbName from salmon_8023..Population union all select *, 'salmon_8030' as dbName from salmon_8030..Population union all select *, 'sheep_9940' as dbName from sheep_9940..Population union all select *, 'soybean_3847' as dbName from soybean_3847..Population union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..Population union all select *, 'tilapia_8128' as dbName from tilapia_8128..Population union all select *, 'trematode_6183' as dbName from trematode_6183..Population union all select *, 'trout_8022' as dbName from trout_8022..Population union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..Population union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..Population +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_RsMergeArch as select * from vw_all_RsMergeArch_part1 +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_RsMergeArch_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..RsMergeArch union all select *, 'bee_7460' as dbName from bee_7460..RsMergeArch union all select *, 'bison_9901' as dbName from bison_9901..RsMergeArch union all select *, 'blackbird_39638' as dbName from blackbird_39638..RsMergeArch union all select *, 'bonobo_9597' as dbName from bonobo_9597..RsMergeArch union all select *, 'chicken_9031' as dbName from chicken_9031..RsMergeArch union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..RsMergeArch union all select *, 'cooperia_27828' as dbName from cooperia_27828..RsMergeArch union all select *, 'corn_4577' as dbName from corn_4577..RsMergeArch union all select *, 'cow_30522' as dbName from cow_30522..RsMergeArch union all select *, 'cow_9913' as dbName from cow_9913..RsMergeArch union all select *, 'dog_9615' as dbName from dog_9615..RsMergeArch union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..RsMergeArch union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..RsMergeArch union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..RsMergeArch union all select *, 'gorilla_9593' as dbName from gorilla_9593..RsMergeArch union all select *, 'horse_9796' as dbName from horse_9796..RsMergeArch union all select *, 'human_9606' as dbName from human_9606..RsMergeArch union all select *, 'macaque_9544' as dbName from macaque_9544..RsMergeArch union all select *, 'mosquito_7165' as dbName from mosquito_7165..RsMergeArch union all select *, 'mouse_10090' as dbName from mouse_10090..RsMergeArch union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..RsMergeArch union all select *, 'nematode_6239' as dbName from nematode_6239..RsMergeArch union all select *, 'onion_4679' as dbName from onion_4679..RsMergeArch union all select *, 'orangutan_9600' as dbName from orangutan_9600..RsMergeArch union all select *, 'pig_9823' as dbName from pig_9823..RsMergeArch union all select *, 'pine_71647' as dbName from pine_71647..RsMergeArch union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..RsMergeArch union all select *, 'rat_10116' as dbName from rat_10116..RsMergeArch union all select *, 'rat_10118' as dbName from rat_10118..RsMergeArch union all select *, 'rice_4530' as dbName from rice_4530..RsMergeArch union all select *, 'salmon_74940' as dbName from salmon_74940..RsMergeArch union all select *, 'salmon_8018' as dbName from salmon_8018..RsMergeArch union all select *, 'salmon_8019' as dbName from salmon_8019..RsMergeArch union all select *, 'salmon_8023' as dbName from salmon_8023..RsMergeArch union all select *, 'salmon_8030' as dbName from salmon_8030..RsMergeArch union all select *, 'sheep_9940' as dbName from sheep_9940..RsMergeArch union all select *, 'soybean_3847' as dbName from soybean_3847..RsMergeArch union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..RsMergeArch union all select *, 'tilapia_8128' as dbName from tilapia_8128..RsMergeArch union all select *, 'trematode_6183' as dbName from trematode_6183..RsMergeArch union all select *, 'trout_8022' as dbName from trout_8022..RsMergeArch union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..RsMergeArch union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..RsMergeArch +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_SNP as select * from vw_all_SNP_part1 +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_SNP_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..SNP union all select *, 'bee_7460' as dbName from bee_7460..SNP union all select *, 'bison_9901' as dbName from bison_9901..SNP union all select *, 'blackbird_39638' as dbName from blackbird_39638..SNP union all select *, 'bonobo_9597' as dbName from bonobo_9597..SNP union all select *, 'chicken_9031' as dbName from chicken_9031..SNP union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..SNP union all select *, 'cooperia_27828' as dbName from cooperia_27828..SNP union all select *, 'corn_4577' as dbName from corn_4577..SNP union all select *, 'cow_30522' as dbName from cow_30522..SNP union all select *, 'cow_9913' as dbName from cow_9913..SNP union all select *, 'dog_9615' as dbName from dog_9615..SNP union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..SNP union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..SNP union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..SNP union all select *, 'gorilla_9593' as dbName from gorilla_9593..SNP union all select *, 'horse_9796' as dbName from horse_9796..SNP union all select *, 'human_9606' as dbName from human_9606..SNP union all select *, 'macaque_9544' as dbName from macaque_9544..SNP union all select *, 'mosquito_7165' as dbName from mosquito_7165..SNP union all select *, 'mouse_10090' as dbName from mouse_10090..SNP union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..SNP union all select *, 'nematode_6239' as dbName from nematode_6239..SNP union all select *, 'onion_4679' as dbName from onion_4679..SNP union all select *, 'orangutan_9600' as dbName from orangutan_9600..SNP union all select *, 'pig_9823' as dbName from pig_9823..SNP union all select *, 'pine_71647' as dbName from pine_71647..SNP union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..SNP union all select *, 'rat_10116' as dbName from rat_10116..SNP union all select *, 'rat_10118' as dbName from rat_10118..SNP union all select *, 'rice_4530' as dbName from rice_4530..SNP union all select *, 'salmon_74940' as dbName from salmon_74940..SNP union all select *, 'salmon_8018' as dbName from salmon_8018..SNP union all select *, 'salmon_8019' as dbName from salmon_8019..SNP union all select *, 'salmon_8023' as dbName from salmon_8023..SNP union all select *, 'salmon_8030' as dbName from salmon_8030..SNP union all select *, 'sheep_9940' as dbName from sheep_9940..SNP union all select *, 'soybean_3847' as dbName from soybean_3847..SNP union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..SNP union all select *, 'tilapia_8128' as dbName from tilapia_8128..SNP union all select *, 'trematode_6183' as dbName from trematode_6183..SNP union all select *, 'trout_8022' as dbName from trout_8022..SNP union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..SNP union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..SNP +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_SubPop as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..SubPop union all select *, 'bee_7460' as dbName from bee_7460..SubPop union all select *, 'bison_9901' as dbName from bison_9901..SubPop union all select *, 'blackbird_39638' as dbName from blackbird_39638..SubPop union all select *, 'bonobo_9597' as dbName from bonobo_9597..SubPop union all select *, 'chicken_9031' as dbName from chicken_9031..SubPop union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..SubPop union all select *, 'cooperia_27828' as dbName from cooperia_27828..SubPop union all select *, 'corn_4577' as dbName from corn_4577..SubPop union all select *, 'cow_30522' as dbName from cow_30522..SubPop union all select *, 'cow_9913' as dbName from cow_9913..SubPop union all select *, 'dog_9615' as dbName from dog_9615..SubPop union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..SubPop union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..SubPop union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..SubPop union all select *, 'gorilla_9593' as dbName from gorilla_9593..SubPop union all select *, 'horse_9796' as dbName from horse_9796..SubPop union all select *, 'human_9606' as dbName from human_9606..SubPop union all select *, 'macaque_9544' as dbName from macaque_9544..SubPop union all select *, 'mosquito_7165' as dbName from mosquito_7165..SubPop union all select *, 'mouse_10090' as dbName from mouse_10090..SubPop union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..SubPop union all select *, 'nematode_6239' as dbName from nematode_6239..SubPop union all select *, 'orangutan_9600' as dbName from orangutan_9600..SubPop union all select *, 'pig_9823' as dbName from pig_9823..SubPop union all select *, 'pine_71647' as dbName from pine_71647..SubPop union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..SubPop union all select *, 'rat_10116' as dbName from rat_10116..SubPop union all select *, 'rat_10118' as dbName from rat_10118..SubPop union all select *, 'rice_4530' as dbName from rice_4530..SubPop union all select *, 'salmon_74940' as dbName from salmon_74940..SubPop union all select *, 'salmon_8030' as dbName from salmon_8030..SubPop union all select *, 'sheep_9940' as dbName from sheep_9940..SubPop union all select *, 'soybean_3847' as dbName from soybean_3847..SubPop union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..SubPop union all select *, 'trematode_6183' as dbName from trematode_6183..SubPop union all select *, 'trout_8022' as dbName from trout_8022..SubPop union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..SubPop union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..SubPop uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_SubSNP as select * from arabidopsis_3702..SubSNP union all select * from bee_7460..SubSNP union all select * from bison_9901..SubSNP union all select * from blackbird_39638..SubSNP union all select * from bonobo_9597..SubSNP union all select * from chicken_9031..SubSNP union all select * from chimpanzee_9598..SubSNP union all select * from cooperia_27828..SubSNP union all select * from corn_4577..SubSNP union all select * from cow_30522..SubSNP union all select * from cow_9913..SubSNP union all select * from dog_9615..SubSNP union all select * from flycatcher_46689..SubSNP union all select * from flycatcher_59894..SubSNP union all select * from fruitfly_7227..SubSNP union all select * from gorilla_9593..SubSNP union all select * from horse_9796..SubSNP union all select * from human_9606..SubSNP union all select * from macaque_9544..SubSNP union all select * from mosquito_7165..SubSNP union all select * from mouse_10090..SubSNP union all select * from mouse_spretus_10096..SubSNP union all select * from nematode_6239..SubSNP union all select * from orangutan_9600..SubSNP union all select * from pig_9823..SubSNP union all select * from pine_71647..SubSNP union all select * from plasmodium_5833..SubSNP union all select * from rat_10116..SubSNP union all select * from rat_10118..SubSNP union all select * from rice_4530..SubSNP union all select * from salmon_74940..SubSNP union all select * from salmon_8030..SubSNP union all select * from sheep_9940..SubSNP union all select * from soybean_3847..SubSNP union all select * from sugarcane_128810..SubSNP union all select * from trematode_6183..SubSNP union all select * from trout_8022..SubSNP union all select * from tubeworm_216498..SubSNP union all select * from zebrafish_7955..SubSNP uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_SubSNPDeletedBySubmitter as select * from vw_all_SubSNPDeletedBySubmitter_part2 union all select * from vw_all_SubSNPDeletedBySubmitter_part1 +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_SubSNPDeletedBySubmitter_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..SubSNPDeletedBySubmitter union all select *, 'bee_7460' as dbName from bee_7460..SubSNPDeletedBySubmitter union all select *, 'bison_9901' as dbName from bison_9901..SubSNPDeletedBySubmitter union all select *, 'blackbird_39638' as dbName from blackbird_39638..SubSNPDeletedBySubmitter union all select *, 'bonobo_9597' as dbName from bonobo_9597..SubSNPDeletedBySubmitter union all select *, 'chicken_9031' as dbName from chicken_9031..SubSNPDeletedBySubmitter union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..SubSNPDeletedBySubmitter union all select *, 'cooperia_27828' as dbName from cooperia_27828..SubSNPDeletedBySubmitter union all select *, 'corn_4577' as dbName from corn_4577..SubSNPDeletedBySubmitter union all select *, 'cow_30522' as dbName from cow_30522..SubSNPDeletedBySubmitter union all select *, 'cow_9913' as dbName from cow_9913..SubSNPDeletedBySubmitter union all select *, 'dog_9615' as dbName from dog_9615..SubSNPDeletedBySubmitter union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..SubSNPDeletedBySubmitter union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..SubSNPDeletedBySubmitter union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..SubSNPDeletedBySubmitter union all select *, 'gorilla_9593' as dbName from gorilla_9593..SubSNPDeletedBySubmitter union all select *, 'horse_9796' as dbName from horse_9796..SubSNPDeletedBySubmitter union all select *, 'human_9606' as dbName from human_9606..SubSNPDeletedBySubmitter union all select *, 'macaque_9544' as dbName from macaque_9544..SubSNPDeletedBySubmitter union all select *, 'mosquito_62324' as dbName from mosquito_62324..SubSNPDeletedBySubmitter union all select *, 'mosquito_7165' as dbName from mosquito_7165..SubSNPDeletedBySubmitter union all select *, 'mouse_10090' as dbName from mouse_10090..SubSNPDeletedBySubmitter union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..SubSNPDeletedBySubmitter union all select *, 'nematode_6239' as dbName from nematode_6239..SubSNPDeletedBySubmitter union all select *, 'onion_4679' as dbName from onion_4679..SubSNPDeletedBySubmitter union all select *, 'orangutan_9600' as dbName from orangutan_9600..SubSNPDeletedBySubmitter union all select *, 'pig_9823' as dbName from pig_9823..SubSNPDeletedBySubmitter union all select *, 'pine_71647' as dbName from pine_71647..SubSNPDeletedBySubmitter union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..SubSNPDeletedBySubmitter union all select *, 'rat_10116' as dbName from rat_10116..SubSNPDeletedBySubmitter union all select *, 'rat_10118' as dbName from rat_10118..SubSNPDeletedBySubmitter union all select *, 'rice_4530' as dbName from rice_4530..SubSNPDeletedBySubmitter union all select *, 'salmon_74940' as dbName from salmon_74940..SubSNPDeletedBySubmitter union all select *, 'salmon_8018' as dbName from salmon_8018..SubSNPDeletedBySubmitter union all select *, 'salmon_8019' as dbName from salmon_8019..SubSNPDeletedBySubmitter union all select *, 'salmon_8023' as dbName from salmon_8023..SubSNPDeletedBySubmitter union all select *, 'salmon_8030' as dbName from salmon_8030..SubSNPDeletedBySubmitter union all select *, 'sheep_9940' as dbName from sheep_9940..SubSNPDeletedBySubmitter union all select *, 'soybean_3847' as dbName from soybean_3847..SubSNPDeletedBySubmitter union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..SubSNPDeletedBySubmitter union all select *, 'tilapia_8128' as dbName from tilapia_8128..SubSNPDeletedBySubmitter union all select *, 'trematode_6183' as dbName from trematode_6183..SubSNPDeletedBySubmitter +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_SubSNPDeletedBySubmitter_part2 as select *, 'trout_8022' as dbName from trout_8022..SubSNPDeletedBySubmitter union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..SubSNPDeletedBySubmitter union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..SubSNPDeletedBySubmitter +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_SubmittedIndividual as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..SubmittedIndividual union all select *, 'bee_7460' as dbName from bee_7460..SubmittedIndividual union all select *, 'bison_9901' as dbName from bison_9901..SubmittedIndividual union all select *, 'blackbird_39638' as dbName from blackbird_39638..SubmittedIndividual union all select *, 'bonobo_9597' as dbName from bonobo_9597..SubmittedIndividual union all select *, 'chicken_9031' as dbName from chicken_9031..SubmittedIndividual union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..SubmittedIndividual union all select *, 'cooperia_27828' as dbName from cooperia_27828..SubmittedIndividual union all select *, 'corn_4577' as dbName from corn_4577..SubmittedIndividual union all select *, 'cow_30522' as dbName from cow_30522..SubmittedIndividual union all select *, 'cow_9913' as dbName from cow_9913..SubmittedIndividual union all select *, 'dog_9615' as dbName from dog_9615..SubmittedIndividual union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..SubmittedIndividual union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..SubmittedIndividual union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..SubmittedIndividual union all select *, 'gorilla_9593' as dbName from gorilla_9593..SubmittedIndividual union all select *, 'horse_9796' as dbName from horse_9796..SubmittedIndividual union all select *, 'human_9606' as dbName from human_9606..SubmittedIndividual union all select *, 'macaque_9544' as dbName from macaque_9544..SubmittedIndividual union all select *, 'mosquito_7165' as dbName from mosquito_7165..SubmittedIndividual union all select *, 'mouse_10090' as dbName from mouse_10090..SubmittedIndividual union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..SubmittedIndividual union all select *, 'nematode_6239' as dbName from nematode_6239..SubmittedIndividual union all select *, 'orangutan_9600' as dbName from orangutan_9600..SubmittedIndividual union all select *, 'pig_9823' as dbName from pig_9823..SubmittedIndividual union all select *, 'pine_71647' as dbName from pine_71647..SubmittedIndividual union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..SubmittedIndividual union all select *, 'rat_10116' as dbName from rat_10116..SubmittedIndividual union all select *, 'rat_10118' as dbName from rat_10118..SubmittedIndividual union all select *, 'rice_4530' as dbName from rice_4530..SubmittedIndividual union all select *, 'salmon_74940' as dbName from salmon_74940..SubmittedIndividual union all select *, 'salmon_8018' as dbName from salmon_8018..SubmittedIndividual union all select *, 'salmon_8019' as dbName from salmon_8019..SubmittedIndividual union all select *, 'salmon_8023' as dbName from salmon_8023..SubmittedIndividual union all select *, 'salmon_8030' as dbName from salmon_8030..SubmittedIndividual union all select *, 'sheep_9940' as dbName from sheep_9940..SubmittedIndividual union all select *, 'soybean_3847' as dbName from soybean_3847..SubmittedIndividual union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..SubmittedIndividual union all select *, 'trematode_6183' as dbName from trematode_6183..SubmittedIndividual union all select *, 'trout_8022' as dbName from trout_8022..SubmittedIndividual union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..SubmittedIndividual union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..SubmittedIndividual uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_dn_batchCount as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..dn_batchCount union all select *, 'bee_7460' as dbName from bee_7460..dn_batchCount union all select *, 'bison_9901' as dbName from bison_9901..dn_batchCount union all select *, 'blackbird_39638' as dbName from blackbird_39638..dn_batchCount union all select *, 'bonobo_9597' as dbName from bonobo_9597..dn_batchCount union all select *, 'chicken_9031' as dbName from chicken_9031..dn_batchCount union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..dn_batchCount union all select *, 'cooperia_27828' as dbName from cooperia_27828..dn_batchCount union all select *, 'corn_4577' as dbName from corn_4577..dn_batchCount union all select *, 'cow_30522' as dbName from cow_30522..dn_batchCount union all select *, 'cow_9913' as dbName from cow_9913..dn_batchCount union all select *, 'dog_9615' as dbName from dog_9615..dn_batchCount union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..dn_batchCount union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..dn_batchCount union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..dn_batchCount union all select *, 'gorilla_9593' as dbName from gorilla_9593..dn_batchCount union all select *, 'horse_9796' as dbName from horse_9796..dn_batchCount union all select *, 'human_9606' as dbName from human_9606..dn_batchCount union all select *, 'macaque_9544' as dbName from macaque_9544..dn_batchCount union all select *, 'mosquito_7165' as dbName from mosquito_7165..dn_batchCount union all select *, 'mouse_10090' as dbName from mouse_10090..dn_batchCount union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..dn_batchCount union all select *, 'nematode_6239' as dbName from nematode_6239..dn_batchCount union all select *, 'orangutan_9600' as dbName from orangutan_9600..dn_batchCount union all select *, 'pig_9823' as dbName from pig_9823..dn_batchCount union all select *, 'pine_71647' as dbName from pine_71647..dn_batchCount union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..dn_batchCount union all select *, 'rat_10116' as dbName from rat_10116..dn_batchCount union all select *, 'rat_10118' as dbName from rat_10118..dn_batchCount union all select *, 'rice_4530' as dbName from rice_4530..dn_batchCount union all select *, 'salmon_74940' as dbName from salmon_74940..dn_batchCount union all select *, 'salmon_8018' as dbName from salmon_8018..dn_batchCount union all select *, 'salmon_8019' as dbName from salmon_8019..dn_batchCount union all select *, 'salmon_8023' as dbName from salmon_8023..dn_batchCount union all select *, 'salmon_8030' as dbName from salmon_8030..dn_batchCount union all select *, 'sheep_9940' as dbName from sheep_9940..dn_batchCount union all select *, 'soybean_3847' as dbName from soybean_3847..dn_batchCount union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..dn_batchCount union all select *, 'trematode_6183' as dbName from trematode_6183..dn_batchCount union all select *, 'trout_8022' as dbName from trout_8022..dn_batchCount union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..dn_batchCount union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..dn_batchCount uni +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_dn_handleCount as select * from vw_all_dn_handleCount_part2 union all select * from vw_all_dn_handleCount_part1 +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_dn_handleCount_part1 as select *, 'arabidopsis_3702' as dbName from arabidopsis_3702..dn_handleCount union all select *, 'barrel_medic_3880' as dbName from barrel_medic_3880..dn_handleCount union all select *, 'bee_7460' as dbName from bee_7460..dn_handleCount union all select *, 'bison_9901' as dbName from bison_9901..dn_handleCount union all select *, 'blackbird_39638' as dbName from blackbird_39638..dn_handleCount union all select *, 'bonobo_9597' as dbName from bonobo_9597..dn_handleCount union all select *, 'cat_9685' as dbName from cat_9685..dn_handleCount union all select *, 'chicken_9031' as dbName from chicken_9031..dn_handleCount union all select *, 'chimpanzee_9598' as dbName from chimpanzee_9598..dn_handleCount union all select *, 'cooperia_27828' as dbName from cooperia_27828..dn_handleCount union all select *, 'corn_4577' as dbName from corn_4577..dn_handleCount union all select *, 'cow_30522' as dbName from cow_30522..dn_handleCount union all select *, 'cow_9913' as dbName from cow_9913..dn_handleCount union all select *, 'dog_9615' as dbName from dog_9615..dn_handleCount union all select *, 'flycatcher_46689' as dbName from flycatcher_46689..dn_handleCount union all select *, 'flycatcher_59894' as dbName from flycatcher_59894..dn_handleCount union all select *, 'fruitfly_7227' as dbName from fruitfly_7227..dn_handleCount union all select *, 'fusarium_5518' as dbName from fusarium_5518..dn_handleCount union all select *, 'goat_9925' as dbName from goat_9925..dn_handleCount union all select *, 'gorilla_9593' as dbName from gorilla_9593..dn_handleCount union all select *, 'grape_29760' as dbName from grape_29760..dn_handleCount union all select *, 'horse_9796' as dbName from horse_9796..dn_handleCount union all select *, 'human_9606' as dbName from human_9606..dn_handleCount union all select *, 'macaque_9541' as dbName from macaque_9541..dn_handleCount union all select *, 'macaque_9544' as dbName from macaque_9544..dn_handleCount union all select *, 'mosquito_62324' as dbName from mosquito_62324..dn_handleCount union all select *, 'mosquito_7165' as dbName from mosquito_7165..dn_handleCount union all select *, 'mouse_10090' as dbName from mouse_10090..dn_handleCount union all select *, 'mouse_spretus_10096' as dbName from mouse_spretus_10096..dn_handleCount union all select *, 'nematode_51029' as dbName from nematode_51029..dn_handleCount union all select *, 'nematode_6238' as dbName from nematode_6238..dn_handleCount union all select *, 'nematode_6239' as dbName from nematode_6239..dn_handleCount union all select *, 'onion_4679' as dbName from onion_4679..dn_handleCount union all select *, 'opossum_13616' as dbName from opossum_13616..dn_handleCount union all select *, 'orangutan_9600' as dbName from orangutan_9600..dn_handleCount union all select *, 'pig_9823' as dbName from pig_9823..dn_handleCount union all select *, 'pine_71647' as dbName from pine_71647..dn_handleCount union all select *, 'plasmodium_5833' as dbName from plasmodium_5833..dn_handleCount union all select *, 'platypus_9258' as dbName from platypus_9258..dn_handleCount union all select *, 'rat_10116' as dbName from rat_10116..dn_handleCount union all select *, 'rat_10118' as dbName from rat_10118..dn_handleCount union all select *, 'rice_4530' as dbName from rice_4530..dn_handleCount union all select *, 'salmon_74940' as dbName from salmon_74940..dn_handleCount union all select *, 'salmon_8018' as dbName from salmon_8018..dn_handleCount union all select *, 'salmon_8019' as dbName from salmon_8019..dn_handleCount union all select *, 'salmon_8023' as dbName from salmon_8023..dn_handleCount union all select *, 'salmon_8030' as dbName from salmon_8030..dn_handleCount +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS OFF +; + create view vw_all_dn_handleCount_part2 as select *, 'sheep_9940' as dbName from sheep_9940..dn_handleCount union all select *, 'soybean_3847' as dbName from soybean_3847..dn_handleCount union all select *, 'staphylococcus_1280' as dbName from staphylococcus_1280..dn_handleCount union all select *, 'sugarcane_128810' as dbName from sugarcane_128810..dn_handleCount union all select *, 'tilapia_8128' as dbName from tilapia_8128..dn_handleCount union all select *, 'tomato_4081' as dbName from tomato_4081..dn_handleCount union all select *, 'trematode_6183' as dbName from trematode_6183..dn_handleCount union all select *, 'trout_8022' as dbName from trout_8022..dn_handleCount union all select *, 'tubeworm_216498' as dbName from tubeworm_216498..dn_handleCount union all select *, 'white_shrimp_6689' as dbName from white_shrimp_6689..dn_handleCount union all select *, 'zebrafish_7955' as dbName from zebrafish_7955..dn_handleCount union all select *, 'zostera_29655' as dbName from zostera_29655..dn_handleCount +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + +SET QUOTED_IDENTIFIER ON +;SET ANSI_NULLS ON +; +CREATE VIEW dbo.vw_snp_nucleotide +AS +select * from human_9606..vw_snp_nucleotide + +/* +select * from mosquito_7165..vw_snp_nucleotide +union +select * from bee_7460..vw_snp_nucleotide +union +select * from bison_9901..vw_snp_nucleotide + union +select * from cow_30522..vw_snp_nucleotide +union +select * from cow_9913..vw_snp_nucleotide +union +select * from nematode_6239..vw_snp_nucleotide +union +select * from dog_9615..vw_snp_nucleotide +union +select * from zebrafish_7955..vw_snp_nucleotide +union +select * from chicken_9031..vw_snp_nucleotide +union +select * from mouse_10090..vw_snp_nucleotide +union +select * from rice_4530..vw_snp_nucleotide +union +select * from chimpanzee_9598..vw_snp_nucleotide +union +select * from rat_10116..vw_snp_nucleotide + +*/ + + +;SET QUOTED_IDENTIFIER OFF +;SET ANSI_NULLS ON +; + diff --git a/utils/mssql_psql_conversion.pl b/utils/mssql_psql_conversion.pl new file mode 100755 index 0000000..d7aea89 --- /dev/null +++ b/utils/mssql_psql_conversion.pl @@ -0,0 +1,19 @@ +#!/usr/bin/perl + +use warnings; +use strict; + +while (<>) { + s/\[([^]]+)\]/"$1"/g; + s/^\s*GO\s*$/;/; + s/tinyint/smallint/; + s/binary(?:\s*\([^)]+\))?/bytea/ig; + s/smalldatetime/TIMESTAMP/ig; + s/DATETIME/TIMESTAMP/ig; + s/(?:NON)?CLUSTERED//g; + s/int\s*IDENTITY\s*\(\d+,\s*\d+\)/SERIAL/ig; + # mssql uses stupid names for indexes apparently; ditch them and + # let pgsql choose + s/(CREATE\s+(?:UNIQUE\s+)?INDEX\s+)\S+\s+(ON\s+)/$1$2/gi; + print $_; +}