src/hg/instinct/bioInt2/bioIntDb.sql 1.1

Index: src/hg/instinct/bioInt2/bioIntDb.sql
===================================================================
RCS file: src/hg/instinct/bioInt2/bioIntDb.sql
diff -N src/hg/instinct/bioInt2/bioIntDb.sql
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ src/hg/instinct/bioInt2/bioIntDb.sql	20 Mar 2009 06:06:31 -0000	1.1
@@ -0,0 +1,156 @@
+# 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)
+);
+
+#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 analysis features
+CREATE TABLE analysisFeatures (
+    feature_id int unsigned not null,	# Feature Id
+    feature_name varchar(255) not null,	# Feature Name
+              #Indices
+    PRIMARY KEY(feature_id)
+);
+
+#All analysis vals
+CREATE TABLE analysisVals (
+    sample_id int unsigned not null,	# Sample Id
+    feature_id int unsigned not null,	# Feature Id
+    val float not null,	# Val
+    conf float not null,	# Confidence
+              #Indices
+    PRIMARY KEY(sample_id)
+);