src/hg/instinct/bioInt2/bioIntDb.sql 1.5
1.5 2009/04/27 06:15:48 jsanborn
updated lots of stuff, will break older implementation of database
Index: src/hg/instinct/bioInt2/bioIntDb.sql
===================================================================
RCS file: /projects/compbio/cvsroot/kent/src/hg/instinct/bioInt2/bioIntDb.sql,v
retrieving revision 1.4
retrieving revision 1.5
diff -b -B -U 1000000 -r1.4 -r1.5
--- src/hg/instinct/bioInt2/bioIntDb.sql 29 Mar 2009 01:40:43 -0000 1.4
+++ src/hg/instinct/bioInt2/bioIntDb.sql 27 Apr 2009 06:15:48 -0000 1.5
@@ -1,218 +1,189 @@
# 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 (
+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 pathwayGenes (
+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 pathwayInfo (
+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
- 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_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)
);
#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)
);