src/hg/instinct/bioInt2/bioIntDb.sql 1.2
1.2 2009/03/21 19:54:10 jsanborn
added routine to set cohorts
Index: src/hg/instinct/bioInt2/bioIntDb.sql
===================================================================
RCS file: /projects/compbio/cvsroot/kent/src/hg/instinct/bioInt2/bioIntDb.sql,v
retrieving revision 1.1
retrieving revision 1.2
diff -b -B -U 1000000 -r1.1 -r1.2
--- src/hg/instinct/bioInt2/bioIntDb.sql 20 Mar 2009 06:06:31 -0000 1.1
+++ src/hg/instinct/bioInt2/bioIntDb.sql 21 Mar 2009 19:54:10 -0000 1.2
@@ -1,156 +1,191 @@
# bioIntDb.sql was originally generated by the autoSql program, which also
# generated bioIntDb.c and bioIntDb.h. This creates the database representation of
# an object which can be loaded and saved from RAM in a fairly
# automatic way.
#Pathway List
CREATE TABLE pathways (
id int unsigned not null, # Unique id
name varchar(255) not null, # Pathway name
source varchar(255) not null, # Pathway Source, i.e. KEGG, BioCarta
#Indices
PRIMARY KEY(id)
);
#Pathway Genes
CREATE TABLE pathwayGenes (
id int unsigned not null, # Unique id
gene_id int unsigned not null, # Gene id
#Indices
PRIMARY KEY(id)
);
#Pathway Information
CREATE TABLE pathwayInfo (
id int unsigned not null, # Unique id
description longblob not null, # Description of pathway
#Indices
PRIMARY KEY(id)
);
#All available issues
CREATE TABLE tissues (
id int unsigned not null, # Unique id
name varchar(255) not null, # Tissue Type
#Indices
PRIMARY KEY(id)
);
#All available data types
CREATE TABLE dataTypes (
id int unsigned not null, # Unique id
format varchar(255) not null, # Data Format
name varchar(255) not null, # Data type
#Indices
PRIMARY KEY(id)
);
#All available datasets
CREATE TABLE datasets (
id int unsigned not null, # Unique Id
tissue_id int unsigned not null, # Numeric id denoting tissue type
type_id int unsigned not null, # Type of genomics data
num_samples int unsigned not null, # Number of samples in study
name varchar(255) not null, # Dataset name
data_table varchar(255) not null, # Array Data tablename
probe_table varchar(255) not null, # Probe tablename
probe_to_gene_table varchar(255) not null, # Probe to gene tablename
#Indices
PRIMARY KEY(id)
);
+#All cohorts
+CREATE TABLE cohorts (
+ id int unsigned not null, # Cohort Id
+ name varchar(255) not null, # Cohort name
+ #Indices
+ PRIMARY KEY(id)
+);
+
+#Dataset cohort lookup
+CREATE TABLE datasetCohort (
+ dataset_id int unsigned not null, # Dataset Id
+ cohort_id int unsigned not null, # Cohort Id
+ #Indices
+ PRIMARY KEY(dataset_id)
+);
+
#Lookup table linking knownGene
CREATE TABLE geneLookup (
id int unsigned not null, # Unique Id
kgId varchar(255) not null, # Known Gene Id
#Indices
PRIMARY KEY(id)
);
#Probe Information
CREATE TABLE probeInfo (
id int unsigned not null, # Unique Id
chrom varchar(255) not null, # Chromosome
start int unsigned not null, # Start Base
stop int unsigned not null, # Stop Base
name varchar(255) not null, # Probe Name
#Indices
PRIMARY KEY(id)
);
#Lookup table linking probe id and gene id
CREATE TABLE probeToGene (
probe_id int unsigned not null, # Probe Id
gene_id int unsigned not null, # Gene Id
#Indices
PRIMARY KEY(probe_id)
);
#Probe values for single sample
CREATE TABLE probeSampleVal (
probe_id int unsigned not null, # Probe Id
sample_id int unsigned not null, # Sample Id
val float not null, # Exp Value
#Indices
PRIMARY KEY(probe_id)
);
#Probe values for all samples
CREATE TABLE probeVals (
probe_id int unsigned not null, # Probe Id
sample_count int unsigned not null, # Sample Count
sample_data longblob not null, # Sample Data
#Indices
PRIMARY KEY(probe_id)
);
#All sample info
CREATE TABLE samples (
id int unsigned not null, # Unique Id
name varchar(255) not null, # Sample Name
patient_id int unsigned not null, # Patient Id
patient_name varchar(255) not null, # Patient Name
dataset_id int unsigned not null, # Dataset Id
exp_id int unsigned not null, # Index in dataset -- for probeVals format
tissue_id int unsigned not null, # Tissue Type Id
#Indices
PRIMARY KEY(id)
);
#All Features
CREATE TABLE features (
id int unsigned not null, # Unique Id
name varchar(255) not null, # Feature Name
shortLabel varchar(255) not null, # Short Label
longLabel varchar(255) not null, # Long Label
#Indices
PRIMARY KEY(id)
);
#All clinical data
CREATE TABLE clinicalData (
sample_id int unsigned not null, # Sample Id
feature_id int unsigned not null, # Feature Id
val double not null, # Value
code varchar(255) not null, # Coded Value
#Indices
PRIMARY KEY(sample_id)
);
+#All analyses run
+CREATE TABLE analyses (
+ id int unsigned not null, # Analysis id
+ cohort_id int unsigned not null, # Cohort Id
+ module varchar(255) not null, # Analysis Module
+ result_table varchar(255) not null, # Table containing result
+ #Indices
+ PRIMARY KEY(id)
+);
+
+#All analysis parameters
+CREATE TABLE analysisParams (
+ analysis_id int unsigned not null, # Module Id
+ key varchar(255) not null, # Parameter key
+ val varchar(255) not null, # Parameter val
+ #Indices
+ PRIMARY KEY(analysis_id)
+);
+
#All analysis features
CREATE TABLE analysisFeatures (
- feature_id int unsigned not null, # Feature Id
+ id int unsigned not null, # Feature Id
feature_name varchar(255) not null, # Feature Name
#Indices
- PRIMARY KEY(feature_id)
+ PRIMARY KEY(id)
);
#All analysis vals
CREATE TABLE analysisVals (
sample_id int unsigned not null, # Sample Id
- feature_id int unsigned not null, # Feature Id
+ feature_id int unsigned not null, # Analysis Feature Id
val float not null, # Val
conf float not null, # Confidence
#Indices
PRIMARY KEY(sample_id)
);