src/hg/instinct/bioInt2/createTables.c 1.4

Index: src/hg/instinct/bioInt2/createTables.c
===================================================================
RCS file: /projects/compbio/cvsroot/kent/src/hg/instinct/bioInt2/createTables.c,v
retrieving revision 1.3
retrieving revision 1.4
diff -b -B -U 1000000 -r1.3 -r1.4
--- src/hg/instinct/bioInt2/createTables.c	1 Sep 2009 05:18:02 -0000	1.3
+++ src/hg/instinct/bioInt2/createTables.c	5 Sep 2009 01:12:01 -0000	1.4
@@ -1,309 +1,311 @@
 /* createTables.c */
 #include "common.h"
 #include "jksql.h"
 #include "hdb.h"
 
 void createDataTypesTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "format varchar(255) not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "PRIMARY KEY(id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }    
 
 void createTissuesTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "PRIMARY KEY(id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 } 
 
 void createDatasetsTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "tissue_id int unsigned not null,\n");
 dyStringPrintf(dy, "type_id int unsigned not null,\n");
 dyStringPrintf(dy, "num_samples int unsigned not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "data_table varchar(255) not null,\n");
 dyStringPrintf(dy, "PRIMARY KEY(id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 } 
 
 void createSamplesTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "patient_id int unsigned not null,\n");
 dyStringPrintf(dy, "patient_name varchar(255) not null,\n");
 dyStringPrintf(dy, "dataset_id int unsigned not null,\n");
 //dyStringPrintf(dy, "exp_id int unsigned not null,\n");
 dyStringPrintf(dy, "tissue_id int unsigned not null,\n");
 dyStringPrintf(dy, "KEY(id),\n");
 dyStringPrintf(dy, "KEY(dataset_id),\n");
 dyStringPrintf(dy, "KEY(id, dataset_id),\n");
 dyStringPrintf(dy, "KEY(dataset_id,id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }  
 
 void createFeaturesTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "shortLabel varchar(255) not null,\n");
 dyStringPrintf(dy, "longLabel varchar(255) not null,\n");
 dyStringPrintf(dy, "KEY(id),\n");
 dyStringPrintf(dy, "KEY(name),\n");
 dyStringPrintf(dy, "KEY(id,name)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }
 
 void createClinicalDataTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "sample_id int unsigned not null,\n");
 dyStringPrintf(dy, "feature_id int unsigned not null,\n");
 dyStringPrintf(dy, "val double not null,\n");
 dyStringPrintf(dy, "code varchar(255),\n");
 dyStringPrintf(dy, "KEY(sample_id),\n");
 dyStringPrintf(dy, "KEY(feature_id),\n");
 dyStringPrintf(dy, "KEY(sample_id,feature_id),\n");
 dyStringPrintf(dy, "KEY(feature_id,sample_id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 } 
 
 
 void createProbeValsTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "probe_id int unsigned not null,\n");
 dyStringPrintf(dy, "sample_count int unsigned not null,\n");
 dyStringPrintf(dy, "sample_data longblob not null,\n");
 dyStringPrintf(dy, "PRIMARY KEY(probe_id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }
 
 void createProbeInfoTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "chrom varchar(255) not null,\n");
 dyStringPrintf(dy, "start int unsigned not null,\n");
 dyStringPrintf(dy, "stop int unsigned not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "PRIMARY KEY(id),\n");
 dyStringPrintf(dy, "KEY(name),\n");
 dyStringPrintf(dy, "KEY(id,name)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 } 
 
 
 void createGeneLookupTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "kgId varchar(255) not null,\n");
 dyStringPrintf(dy, "PRIMARY KEY(id),\n");
 dyStringPrintf(dy, "KEY(kgId),\n");
 dyStringPrintf(dy, "KEY(id,kgId),\n");
 dyStringPrintf(dy, "KEY(kgId,id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }
 
 
 void createProbeToGeneTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "probe_id int unsigned not null,\n");
 dyStringPrintf(dy, "gene_id int unsigned not null,\n");
 dyStringPrintf(dy, "KEY(probe_id),\n");
 dyStringPrintf(dy, "KEY(gene_id),\n");
 dyStringPrintf(dy, "KEY(probe_id,gene_id),\n");
 dyStringPrintf(dy, "KEY(gene_id,probe_id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }    
 
 void createGenesetsTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "source varchar(255) not null,\n");
 dyStringPrintf(dy, "KEY(id),\n");
 dyStringPrintf(dy, "KEY(name),\n");
 dyStringPrintf(dy, "KEY(id,name),\n");
 dyStringPrintf(dy, "KEY(name,id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 } 
 
 void createGenesetGenesTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "gene_id int unsigned not null,\n");
 dyStringPrintf(dy, "KEY(id),\n");
 dyStringPrintf(dy, "KEY(gene_id),\n");
 dyStringPrintf(dy, "KEY(id,gene_id),\n");
 dyStringPrintf(dy, "KEY(gene_id,id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }
 
 void createGenesetInfoTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "description longblob not null,\n");
 dyStringPrintf(dy, "KEY(id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 } 
 
 void createAnalysisFeaturesTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "feature_name varchar(255) not null,\n");
 dyStringPrintf(dy, "type varchar(255) not null,\n");
 dyStringPrintf(dy, "KEY(id),\n");
 dyStringPrintf(dy, "KEY(feature_name),\n");
 dyStringPrintf(dy, "KEY(id, feature_name),\n");
 dyStringPrintf(dy, "KEY(feature_name, id),\n");
 dyStringPrintf(dy, "KEY(type(16), feature_name),\n");
 dyStringPrintf(dy, "KEY(feature_name, type(16))\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }
 
 void createAnalysisValsTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "sample_id int unsigned not null,\n");
 dyStringPrintf(dy, "feature_id int unsigned not null,\n");
 dyStringPrintf(dy, "val float not null,\n");
 dyStringPrintf(dy, "conf float not null,\n");
 dyStringPrintf(dy, "KEY(feature_id, sample_id),\n");
 dyStringPrintf(dy, "KEY(sample_id, feature_id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }        
 
 void createPathwayValsTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "pathway_id int unsigned not null,\n");
 dyStringPrintf(dy, "sample_id int unsigned not null,\n");
 dyStringPrintf(dy, "feature_id int unsigned not null,\n");
 dyStringPrintf(dy, "val float not null,\n");
 dyStringPrintf(dy, "conf float not null,\n");
 dyStringPrintf(dy, "KEY(pathway_id, feature_id, sample_id),\n");
 dyStringPrintf(dy, "KEY(pathway_id, sample_id, feature_id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }        
 
 void createAnalysisModulesTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "type varchar(255) not null,\n");
 dyStringPrintf(dy, "PRIMARY KEY(id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }
 
 void createAnalysesTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "id int unsigned not null,\n");
 dyStringPrintf(dy, "cohort_id int unsigned not null,\n");
 dyStringPrintf(dy, "module_id int unsigned not null,\n");
 dyStringPrintf(dy, "result_table varchar(255) not null,\n");
 dyStringPrintf(dy, "input_tables longblob not null,\n");
 dyStringPrintf(dy, "PRIMARY KEY(id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }      
 
 void createAnalysisParamsTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "analysis_id int unsigned not null,\n");
 dyStringPrintf(dy, "name varchar(255) not null,\n");
 dyStringPrintf(dy, "val varchar(255) not null,\n");
 dyStringPrintf(dy, "KEY(analysis_id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }  
 
 void createPathwayEMValsTable(struct sqlConnection *biConn, char *tableName)
 {
 struct dyString *dy = newDyString(1024);
 dyStringPrintf(dy, "CREATE TABLE %s (\n", tableName);
 dyStringPrintf(dy, "pathway_id int unsigned not null,\n");
+dyStringPrintf(dy, "cohort_id int unsigned not null,\n");
 dyStringPrintf(dy, "dataset_id int unsigned not null,\n");
 dyStringPrintf(dy, "parent_state int unsigned not null,\n");
 dyStringPrintf(dy, "child_state int unsigned not null,\n");
 dyStringPrintf(dy, "val float not null,\n");
+dyStringPrintf(dy, "KEY(cohort_id),\n");
 dyStringPrintf(dy, "KEY(pathway_id, dataset_id)\n");
 dyStringPrintf(dy, ")\n");
 sqlUpdate(biConn,dy->string);
 dyStringFree(&dy);
 }        
\ No newline at end of file