src/hg/instinct/bioInt2/bioIntDb.sql 1.3
1.3 2009/03/22 01:07:28 jsanborn
updated
Index: src/hg/instinct/bioInt2/bioIntDb.sql
===================================================================
RCS file: /projects/compbio/cvsroot/kent/src/hg/instinct/bioInt2/bioIntDb.sql,v
retrieving revision 1.2
retrieving revision 1.3
diff -b -B -U 1000000 -r1.2 -r1.3
--- src/hg/instinct/bioInt2/bioIntDb.sql 21 Mar 2009 19:54:10 -0000 1.2
+++ src/hg/instinct/bioInt2/bioIntDb.sql 22 Mar 2009 01:07:28 -0000 1.3
@@ -1,191 +1,201 @@
# 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
+ module_id int unsigned not null, # Module Id
result_table varchar(255) not null, # Table containing result
+ input_tables longblob not null, # Comma-separated list of input tables
+ #Indices
+ PRIMARY KEY(id)
+);
+
+#All analysis modules
+CREATE TABLE analysisModules (
+ id int unsigned not null, # Module Id
+ name varchar(255) not null, # Module Name
+ type varchar(255) not null, # Module Type (gene, geneset, etc.)
#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
+ analysis_id int unsigned not null, # Analysis Id
+ name varchar(255) not null, # Parameter name
val varchar(255) not null, # Parameter val
#Indices
PRIMARY KEY(analysis_id)
);
#All analysis features
CREATE TABLE analysisFeatures (
id int unsigned not null, # Feature Id
feature_name varchar(255) not null, # Feature Name
#Indices
PRIMARY KEY(id)
);
#All analysis vals
CREATE TABLE analysisVals (
sample_id int unsigned not null, # Sample 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)
);