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

Index: src/hg/instinct/bioInt2/createTables.c
===================================================================
RCS file: src/hg/instinct/bioInt2/createTables.c
diff -N src/hg/instinct/bioInt2/createTables.c
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ src/hg/instinct/bioInt2/createTables.c	27 Apr 2009 17:00:16 -0000	1.1
@@ -0,0 +1,281 @@
+/* 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, "probe_table varchar(255) not null,\n");
+//dyStringPrintf(dy, "probe_to_gene_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 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);
+}  
+