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

Index: src/hg/instinct/bioInt2/createTables.c
===================================================================
RCS file: /projects/compbio/cvsroot/kent/src/hg/instinct/bioInt2/createTables.c,v
retrieving revision 1.2
retrieving revision 1.3
diff -b -B -U 1000000 -r1.2 -r1.3
--- src/hg/instinct/bioInt2/createTables.c	30 Apr 2009 19:54:28 -0000	1.2
+++ src/hg/instinct/bioInt2/createTables.c	1 Sep 2009 05:18:02 -0000	1.3
@@ -1,295 +1,309 @@
 /* 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, "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(pathway_id, dataset_id)\n");
+dyStringPrintf(dy, ")\n");
+sqlUpdate(biConn,dy->string);
+dyStringFree(&dy);
+}        
\ No newline at end of file