6c6f1e8d5133cbd8f1a3bb148486c3f1c9c6b093 tdreszer Tue Apr 19 13:39:45 2011 -0700 When in doubt, should always prefer the sandbox table. diff --git src/hg/lib/mdb.c src/hg/lib/mdb.c index ae6a708..83f8351 100644 --- src/hg/lib/mdb.c +++ src/hg/lib/mdb.c @@ -994,33 +994,32 @@ } return table; } // -------------- Updating the DB -------------- int mdbObjsSetToDb(struct sqlConnection *conn,char *tableName,struct mdbObj *mdbObjs,boolean replace,boolean testOnly) // Adds or updates metadata obj/var pairs into the named table. Returns total rows affected { char query[8192]; struct mdbObj *mdbObj; struct mdbVar *mdbVar; int count = 0; if(tableName == NULL) - tableName = MDB_DEFAULT_NAME; - -if(!sqlTableExists(conn,tableName)) + tableName = mdbTableName(conn,TRUE); // defaults to sandbox, if it exists, else MDB_DEFAULT_NAME; +else if(!sqlTableExists(conn,tableName)) errAbort("mdbObjsSetToDb attempting to update non-existent table named '%s'.\n",tableName); for(mdbObj = mdbObjs;mdbObj != NULL; mdbObj = mdbObj->next) { // Handle delete requests first if(mdbObj->deleteThis) { if(mdbObj->vars == NULL) // deletes all { safef(query, sizeof(query),"%s where obj = '%s'",tableName,mdbObj->obj); int delCnt = sqlRowCount(conn,query); if(delCnt>0) { safef(query, sizeof(query), @@ -1103,33 +1102,32 @@ verbose(2, "Requesting insert of one row:\n\t%s;\n",query); if(!testOnly) sqlUpdate(conn, query); count++; } } return count; } int mdbObjsLoadToDb(struct sqlConnection *conn,char *tableName,struct mdbObj *mdbObjs,boolean testOnly) // Adds mdb Objs with minimal error checking { int count = 0; if (tableName == NULL) - tableName = MDB_DEFAULT_NAME; - -if (!sqlTableExists(conn,tableName)) + tableName = mdbTableName(conn,TRUE); // defaults to sandbox, if it exists, else MDB_DEFAULT_NAME; +else if (!sqlTableExists(conn,tableName)) errAbort("mdbObjsLoadToDb attempting to load non-existent table named '%s'.\n",tableName); assert(mdbObjs != NULL); // If this is the case, then be vocal #define MDB_TEMPORARY_TAB_FILE "temporaryMdb.tab" long lastTime = 0; count = mdbObjPrintToTabFile(mdbObjs,MDB_TEMPORARY_TAB_FILE); // Disable keys in hopes of speeding things up. No danger since it only disables non-unique keys char query[8192]; safef(query, sizeof(query),"alter table %s disable keys",tableName); sqlUpdate(conn, query); // Quick? load @@ -1143,33 +1141,32 @@ verbose(0,"%04ldms - Done loading mdb with 'LOAD DATA INFILE' mysql command.\n",(clock1000() - lastTime)); return count; } // ------------------ Querys ------------------- struct mdbObj *mdbObjQuery(struct sqlConnection *conn,char *table,struct mdbObj *mdbObj) // Query the metadata table by obj and optional vars and vals in metaObj struct. If mdbObj is NULL query all. // Returns new mdbObj struct fully populated and sorted in obj,var order. { // select obj,var,val where (var= [and val=]) or ([var= and] val=) order by obj,var boolean buildHash = TRUE; if(table == NULL) - table = MDB_DEFAULT_NAME; - - if(!sqlTableExists(conn,table)) + table = mdbTableName(conn,TRUE); // defaults to sandbox, if it exists, else MDB_DEFAULT_NAME; + else if(!sqlTableExists(conn,table)) return NULL; struct dyString *dy = newDyString(4096); dyStringPrintf(dy, "select obj,var,val from %s", table); if(mdbObj != NULL && mdbObj->obj != NULL) { dyStringPrintf(dy, " where obj %s '%s'", (strchr(mdbObj->obj,'%')?"like":"="),mdbObj->obj); struct mdbVar *mdbVar; for(mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) { if(mdbVar==mdbObj->vars) dyStringPrintf(dy, " and ("); else @@ -1214,32 +1211,32 @@ return mdbObjQuery(conn,table,NULL); struct mdbObj *queryObj = mdbObjCreate(obj,var,NULL); struct mdbObj *resultObj = mdbObjQuery(conn,table,queryObj); mdbObjsFree(&queryObj); return resultObj; } struct mdbByVar *mdbByVarsQuery(struct sqlConnection *conn,char *table,struct mdbByVar *mdbByVars) // Query the metadata table by one or more var=val pairs to find the distinct set of objs that satisfy ANY conditions. // Returns new mdbByVar struct fully populated and sorted in var,val,obj order. { // select obj,var,val where (var= [and val in (val1,val2)]) or (var= [and val in (val1,val2)]) order by var,val,obj if(table == NULL) - table = MDB_DEFAULT_NAME; - if(!sqlTableExists(conn,table)) + table = mdbTableName(conn,TRUE); // defaults to sandbox, if it exists, else MDB_DEFAULT_NAME; + else if(!sqlTableExists(conn,table)) return NULL; struct dyString *dy = newDyString(4096); dyStringPrintf(dy, "select obj,var,val from %s", table); struct mdbByVar *rootVar; for(rootVar=mdbByVars;rootVar!=NULL;rootVar=rootVar->next) { if(rootVar==mdbByVars) dyStringPrintf(dy, " where (var "); else dyStringPrintf(dy, " OR (var "); if(rootVar->notEqual && rootVar->vals == NULL) dyStringPrintf(dy, "%s",strchr(rootVar->var,'%')?"NOT ":"!"); // one of: "NOT LIKE". "!=" or "NOT EXISTS" @@ -1317,32 +1314,32 @@ // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 WHERE EXISTS (SELECT T2.obj FROM metaDb T2 WHERE T2.obj = T1.obj AND T2.var = 'cell' AND T2.val != 'GM12878') ORDER BY T1.obj, T1.var; // "cell=GM%" or "cell!=GM%" // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 WHERE EXISTS (SELECT T2.obj FROM metaDb T2 WHERE T2.obj = T1.obj AND T2.var = 'cell' AND T2.val LIKE 'GM%') ORDER BY T1.obj, T1.var; // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 WHERE EXISTS (SELECT T2.obj FROM metaDb T2 WHERE T2.obj = T1.obj AND T2.var = 'cell' AND T2.val NOT LIKE 'GM%') ORDER BY T1.obj, T1.var; // "cell=" or "cell!=" // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 WHERE EXISTS (SELECT T2.obj FROM metaDb T2 WHERE T2.obj = T1.obj AND T2.var = 'cell') ORDER BY T1.obj, T1.var; // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 WHERE NOT EXISTS (SELECT T2.obj FROM metaDb T2 WHERE T2.obj = T1.obj AND T2.var = 'cell') ORDER BY T1.obj, T1.var; // "cell=GM12878,K562" or "cell!=GM12878,K562" // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 WHERE EXISTS (SELECT T2.obj FROM metaDb T2 WHERE T2.obj = T1.obj AND T2.var = 'cell' AND T2.val IN ('GM12878','K562')) ORDER BY T1.obj, T1.var; // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 WHERE EXISTS (SELECT T2.obj FROM metaDb T2 WHERE T2.obj = T1.obj AND T2.var = 'cell' AND T2.val NOT IN ('K562','GM12878')) ORDER BY T1.obj, T1.var; // "cell=GM% cell!=GM12878" (very powerful) // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 WHERE EXISTS (SELECT T2.obj FROM metaDb T2 WHERE T2.obj = T1.obj AND T2.var = 'cell' AND T2.val LIKE 'GM%') // AND EXISTS (SELECT T3.obj FROM metaDb T3 WHERE T3.obj = T1.obj AND T3.var = 'cell' AND T3.val != 'GM12878') ORDER BY T1.obj, T1.var; if(table == NULL) - table = MDB_DEFAULT_NAME; - if(!sqlTableExists(conn,table)) + table = mdbTableName(conn,TRUE); // defaults to sandbox, if it exists, else MDB_DEFAULT_NAME; + else if(!sqlTableExists(conn,table)) return NULL; struct dyString *dy = newDyString(4096); dyStringPrintf(dy, "SELECT T1.obj,T1.var,T1.val FROM %s T1", table); struct mdbByVar *rootVar; boolean gotVar = FALSE; int tix; for(rootVar=mdbByVars,tix=2;rootVar!=NULL;rootVar=rootVar->next,tix++) { boolean hasVal = (rootVar->vals != NULL); //boolean hasVal = (rootVar->vals != NULL && rootVar->vals->val != NULL && strlen(rootVar->vals->val) > 0); if(!gotVar) { dyStringPrintf(dy, " WHERE ");