src/hg/instinct/bioInt2/bioIntDb.sql 1.6
1.6 2009/04/30 19:54:28 jsanborn
added factor graph pipeline and code
Index: src/hg/instinct/bioInt2/bioIntDb.sql
===================================================================
RCS file: /projects/compbio/cvsroot/kent/src/hg/instinct/bioInt2/bioIntDb.sql,v
retrieving revision 1.5
retrieving revision 1.6
diff -b -B -U 1000000 -r1.5 -r1.6
--- src/hg/instinct/bioInt2/bioIntDb.sql 27 Apr 2009 06:15:48 -0000 1.5
+++ src/hg/instinct/bioInt2/bioIntDb.sql 30 Apr 2009 19:54:28 -0000 1.6
@@ -1,189 +1,200 @@
# 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 genesets (
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 genesetGenes (
id int unsigned not null, # Unique id
gene_id int unsigned not null, # Gene id
#Indices
PRIMARY KEY(id)
);
#Pathway Information
CREATE TABLE genesetInfo (
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
#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)
);
#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
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_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, # 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
type varchar(255) not null, # Feature Type
#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)
);
+#All pathway vals
+CREATE TABLE pathwayVals (
+ pathway_id int unsigned not null, # Pathway Id
+ 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(pathway_id)
+);
+
#Cohort Correlation tables
CREATE TABLE cohortCorr (
cohort_id int unsigned not null, # Cohort Id
result_table varchar(255) not null, # Result Table
#Indices
PRIMARY KEY(cohort_id)
);
#Correlation results
CREATE TABLE corrResults (
feature_id1 int unsigned not null, # Feature Id1
feature_id2 int unsigned not null, # Feature Id2
val float not null, # Correlation Value
#Indices
PRIMARY KEY(feature_id1)
);