df4ddaf7bd0cb5daad0a3e12346355a60951dfc6 tdreszer Thu Mar 3 15:26:48 2011 -0800 Removed unused 'varType'. Changed longblob to varchar(2084). Changed the recreate load object to print to a temp file and use mysql 'LOAD DATA' command. Made secondary key non-unique and use disable/enable keys to speed up load. diff --git src/hg/lib/mdb.c src/hg/lib/mdb.c index 72eb732..4765c94 100644 --- src/hg/lib/mdb.c +++ src/hg/lib/mdb.c @@ -9,32 +9,31 @@ #include "hdb.h" #include "cheapcgi.h" #include "hui.h" #include "mdb.h" #include <regex.h> static char const rcsid[] = "$Id: mdb.c,v 1.8 2010/06/11 17:11:28 tdreszer Exp $"; void mdbStaticLoad(char **row, struct mdb *ret) /* Load a row from mdb table into ret. The contents of ret will * be replaced at the next call to this function. */ { ret->obj = row[0]; ret->var = row[1]; -ret->varType = row[2]; -ret->val = row[3]; +ret->val = row[2]; } struct mdb *mdbLoadByQuery(struct sqlConnection *conn, char *query) /* Load all mdb from table that satisfy the query given. * Where query is of the form 'select * from example where something=something' * or 'select example.* from example, anotherTable where example.something = * anotherTable.something'. * Dispose of this with mdbFreeList(). */ { struct mdb *list = NULL, *el; struct sqlResult *sr; char **row; sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) @@ -45,73 +44,70 @@ slReverse(&list); sqlFreeResult(&sr); return list; } void mdbSaveToDb(struct sqlConnection *conn, struct mdb *el, char *tableName, int updateSize) /* Save mdb as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Note that strings must be escaped to allow insertion into the database. * For example "autosql's features include" --> "autosql\'s features include" * If worried about this use mdbSaveToDbEscaped() */ { struct dyString *update = newDyString(updateSize); -dyStringPrintf(update, "insert into %s values ( '%s','%s','%s',%s)", - tableName, el->obj, el->var, el->varType, el->val); +dyStringPrintf(update, "insert into %s values ( '%s','%s',%s)", + tableName, el->obj, el->var, el->val); sqlUpdate(conn, update->string); freeDyString(&update); } void mdbSaveToDbEscaped(struct sqlConnection *conn, struct mdb *el, char *tableName, int updateSize) /* Save mdb as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size. * of a string that would contain the entire query. Automatically * escapes all simple strings (not arrays of string) but may be slower than mdbSaveToDb(). * For example automatically copies and converts: * "autosql's features include" --> "autosql\'s features include" * before inserting into database. */ { struct dyString *update = newDyString(updateSize); -char *obj, *var, *varType, *val; +char *obj, *var, *val; obj = sqlEscapeString(el->obj); var = sqlEscapeString(el->var); -varType = sqlEscapeString(el->varType); val = sqlEscapeString(el->val); -dyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s')", - tableName, obj, var, varType, val); +dyStringPrintf(update, "insert into %s values ( '%s','%s','%s')", + tableName, obj, var, val); sqlUpdate(conn, update->string); freeDyString(&update); freez(&obj); freez(&var); -freez(&varType); freez(&val); } struct mdb *mdbLoad(char **row) /* Load a mdb from row fetched with select * from mdb * from database. Dispose of this with mdbFree(). */ { struct mdb *ret; AllocVar(ret); ret->obj = cloneString(row[0]); ret->var = cloneString(row[1]); -ret->varType = cloneString(row[2]); -ret->val = cloneString(row[3]); +ret->val = cloneString(row[2]); return ret; } struct mdb *mdbLoadAll(char *fileName) /* Load all mdb from a whitespace-separated file. * Dispose of this with mdbFreeList(). */ { struct mdb *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[4]; while (lineFileRow(lf, row)) { el = mdbLoad(row); slAddHead(&list, el); @@ -138,113 +134,99 @@ slReverse(&list); return list; } struct mdb *mdbCommaIn(char **pS, struct mdb *ret) /* Create a mdb out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new mdb */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->obj = sqlStringComma(&s); ret->var = sqlStringComma(&s); -ret->varType = sqlStringComma(&s); ret->val = sqlStringComma(&s); *pS = s; return ret; } void mdbFree(struct mdb **pEl) /* Free a single dynamically allocated mdb such as created * with mdbLoad(). */ { struct mdb *el; if ((el = *pEl) == NULL) return; freeMem(el->obj); freeMem(el->var); -freeMem(el->varType); freeMem(el->val); freez(pEl); } void mdbFreeList(struct mdb **pList) /* Free a list of dynamically allocated mdb's */ { struct mdb *el, *next; for (el = *pList; el != NULL; el = next) { next = el->next; mdbFree(&el); } *pList = NULL; } void mdbOutput(struct mdb *el, FILE *f, char sep, char lastSep) /* Print out mdb. Separate fields with sep. Follow last field with lastSep. */ { if (sep == ',') fputc('"',f); fprintf(f, "%s", el->obj); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->var); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); -fprintf(f, "%s", el->varType); -if (sep == ',') fputc('"',f); -fputc(sep,f); -if (sep == ',') fputc('"',f); fprintf(f, "%s", el->val); if (sep == ',') fputc('"',f); fputc(lastSep,f); } void mdbJsonOutput(struct mdb *el, FILE *f) /* Print out mdb in JSON format. */ { fputc('{',f); fputc('"',f); fprintf(f,"obj"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->obj); fputc('"',f); fputc(',',f); fputc('"',f); fprintf(f,"var"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->var); fputc('"',f); fputc(',',f); fputc('"',f); -fprintf(f,"varType"); -fputc('"',f); -fputc(':',f); -fputc('"',f); -fprintf(f, "%s", el->varType); -fputc('"',f); -fputc(',',f); -fputc('"',f); fprintf(f,"val"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->val); fputc('"',f); fputc('}',f); } /* -------------------------------- End autoSql Generated Code -------------------------------- */ #include "ra.h" #include "hgConfig.h" #include "obscure.h" @@ -302,31 +284,30 @@ slReverse(&(mdbObjs->vars)); // Start new object AllocVar(mdbObj); mdbObj->obj = thisRow->obj; if ( buildHashes ) mdbObj->varHash = hashNew(0); slAddHead(&mdbObjs,mdbObj); } else { freeMem(thisRow->obj); // Already got this from prev row } AllocVar(mdbVar); mdbVar->var = thisRow->var; - mdbVar->varType = mdbVarTypeStringToEnum(thisRow->varType); mdbVar->val = thisRow->val; slAddHead(&(mdbObj->vars),mdbVar); if ( buildHashes ) hashAddUnique(mdbObj->varHash, mdbVar->var, mdbVar); // pointer to struct to resolve type freeMem(thisRow); } // Finish very last object if(mdbObjs && mdbObjs->vars) slReverse(&(mdbObjs->vars)); if(mdbObjs) slReverse(&mdbObjs); return mdbObjs; @@ -342,51 +323,48 @@ struct mdb *thisRow; while((thisRow = slPopHead(mdbPtr)) != NULL) { // Start at root if (rootVar == NULL || differentString(thisRow->var,rootVar->var) ) { // Finish last var before starting next! if(rootVars && rootVars->vals && rootVars->vals->objs) slReverse(&(rootVars->vals->objs)); if(rootVars && rootVars->vals) slReverse(&(rootVars->vals)); // Start new var AllocVar(rootVar); limbVal = NULL; // Very important! rootVar->var = thisRow->var; - rootVar->varType = mdbVarTypeStringToEnum(thisRow->varType); - freeMem(thisRow->varType); if ( buildHashes ) rootVar->valHash = hashNew(0); slAddHead(&rootVars,rootVar); } else { freeMem(thisRow->var); // Already got this from prev row - freeMem(thisRow->varType); } // Continue with limb if (limbVal == NULL || differentString(thisRow->val,limbVal->val) ) { // Finish last val before starting next! if(limbVal != NULL && limbVal->objs != NULL) slReverse(&(limbVal->objs)); // Start new val AllocVar(limbVal); - limbVal->val = thisRow->val; // FIXME: binary? + limbVal->val = thisRow->val; if ( buildHashes ) { hashAddUnique(rootVar->valHash, limbVal->val, limbVal); // Pointer to struct to get to objHash limbVal->objHash = hashNew(0); } slAddHead(&(rootVar->vals),limbVal); } else freeMem(thisRow->val); // Already got this from prev row // End with leaf AllocVar(leafObj); leafObj->obj = thisRow->obj; if ( buildHashes ) hashAddUnique(limbVal->objHash, leafObj->obj, leafObj); // Pointer to struct to resolve type! @@ -410,78 +388,56 @@ static int mdbObjCRC(struct mdbObj *mdbObjs) // returns a summ of all individual CRC values of all metObj strings { int crc = 0; struct mdbObj *mdbObj = NULL; for(mdbObj=mdbObjs;mdbObj!=NULL;mdbObj=mdbObj->next) { if(mdbObj->obj != NULL) crc += hashCrc(mdbObj->obj); struct mdbVar *mdbVar = NULL; for(mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) { if(mdbVar->var != NULL) crc += hashCrc(mdbVar->var); - if(mdbVar->varType == vtTxt && mdbVar->val != NULL) + if(mdbVar->val != NULL) crc += hashCrc(mdbVar->val); } } return crc; } // -------------- Sort primitives -------------- int mdbObjCmp(const void *va, const void *vb) /* Compare to sort on label. */ { const struct mdbObj *a = *((struct mdbObj **)va); const struct mdbObj *b = *((struct mdbObj **)vb); return strcasecmp(a->obj, b->obj); } int mdbVarCmp(const void *va, const void *vb) /* Compare to sort on label. */ { const struct mdbVar *a = *((struct mdbVar **)va); const struct mdbVar *b = *((struct mdbVar **)vb); return strcasecmp(a->var, b->var); } -// -------------- Enum to Strings -------------- -enum mdbVarType mdbVarTypeStringToEnum(char *varType) -// Convert metadata varType string to enum -{ -if(sameWord(varType,"txt")) - return vtTxt; -if(sameWord(varType,"binary")) - return vtBinary; -return vtUnknown; -} - -char *mdbVarTypeEnumToString(enum mdbVarType varType) -// Convert metadata varType enum string -{ -switch (varType) - { - case vtTxt: return "txt"; - case vtBinary: return "binary"; - default: return "unknown"; - } -} - // ------ Parsing lines ------ struct mdbObj *mdbObjAddVarPairs(struct mdbObj *oldObj,char *varPairs) // Parses line of var=val pairs adding to a mdbObj. Creates mdbObj if NULL { struct mdbObj *mdbObj = oldObj; struct mdbVar *mdbVar; char *cloneVars = cloneString(varPairs); // initial chop and determine if this looks like metadata int count = chopByWhiteRespectDoubleQuotes(cloneVars,NULL,0); char **words = needMem(sizeof(char *) * count); count = chopByWhiteRespectDoubleQuotes(cloneVars,words,count); if(count < 1 || words[0] == NULL) { @@ -493,31 +449,30 @@ if(mdbObj == NULL) AllocVar(mdbObj); if(mdbObj->varHash == NULL) mdbObj->varHash = hashNew(0); int ix; for(ix = 0;ix<count;ix++) { if(*words[ix] == '#') break; if(strchr(words[ix], '=') == NULL) errAbort("This is not formatted var=val pairs: '%s'\n\t%s\n",words[ix],varPairs); AllocVar(mdbVar); mdbVar->var = cloneNextWordByDelimiter(&(words[ix]),'='); - mdbVar->varType = vtTxt; // FIXME: binary? mdbVar->val = cloneString(words[ix]); verbose(3, "mdbObjAddVarPairs() var=val: %s=%s\n",mdbVar->var,mdbVar->val); struct mdbVar *oldVar = (struct mdbVar *)hashFindVal(mdbObj->varHash, mdbVar->var); if(oldVar) { verbose(1, "The same variable appears twice: %s=%s and %s=%s. Ignoring second value.\n\t%s\n", oldVar->var,oldVar->val,mdbVar->var,mdbVar->val,varPairs); mdbVarFree(&mdbVar); } else { hashAdd(mdbObj->varHash, mdbVar->var, mdbVar); // pointer to struct to resolve type slAddHead(&(mdbObj->vars),mdbVar); } } @@ -702,105 +657,102 @@ limbVal->val = val; rootVar->vals = limbVal; } } hashAdd(varHash, rootVar->var, rootVar); slAddHead(&mdbByVars,rootVar); } freeMem(words); slReverse(&mdbByVars); verbose(3, "mdbByVarsLineParse() parsed:%d first: %s%s='%s'.\n", slCount(mdbByVars),mdbByVars->var,(mdbByVars->notEqual?"!":""),(mdbByVars->vals?mdbByVars->vals->val:"")); return mdbByVars; } // ------ Loading from args, hashes and tdb ------ -struct mdbByVar*mdbByVarCreate(char *var, char *varType,char *val) +struct mdbByVar*mdbByVarCreate(char *var, char *val) /* Creates a singular var=val pair struct for metadata queries. */ { struct mdbByVar *mdbByVar = NULL; if(var == NULL) errAbort("Need variable to create mdbByVar query object.\n"); AllocVar(mdbByVar); mdbByVar->var = cloneString(var); - mdbByVar->varType = (varType==NULL?vtUnknown:mdbVarTypeStringToEnum(varType)); if(val != NULL) { struct mdbLimbVal * limbVal; AllocVar(limbVal); limbVal->val = cloneString(val); mdbByVar->vals = limbVal; // Only one } return mdbByVar; } -boolean mdbByVarAppend(struct mdbByVar *mdbByVars,char *var, char *varType,char *val,boolean notEqual) +boolean mdbByVarAppend(struct mdbByVar *mdbByVars,char *var,char *val,boolean notEqual) /* Adds a another var to a list of mdbByVar pairs to be used in metadata queries. */ { // Does var already exist in mdbByVars? -enum mdbVarType newVarType = (varType==NULL?vtUnknown:mdbVarTypeStringToEnum(varType)); struct mdbByVar *mdbByVar = mdbByVars; for(;mdbByVar!=NULL;mdbByVar=mdbByVar->next) { - if (sameString(mdbByVar->var,var) && mdbByVar->varType == newVarType && mdbByVar->notEqual == notEqual) + if (sameString(mdbByVar->var,var) && mdbByVar->notEqual == notEqual) { struct mdbLimbVal * limbVal = mdbByVar->vals; for(;limbVal!=NULL;limbVal=limbVal->next) { if (sameString(limbVal->val,val)) return FALSE; // Nothing to do as this var is already there. } struct mdbLimbVal * newLimbVal; AllocVar(newLimbVal); newLimbVal->val = cloneString(val); slAddTail(&(mdbByVar->vals),newLimbVal); return TRUE; } } // Not found so add it -struct mdbByVar *newVar = mdbByVarCreate(var, varType,val); +struct mdbByVar *newVar = mdbByVarCreate(var, val); newVar->notEqual = notEqual; slAddTail(&mdbByVars,newVar); // Add to tail to avoid changing passed in pointer return TRUE; } -struct mdbObj *mdbObjCreate(char *obj,char *var, char *varType,char *val) +struct mdbObj *mdbObjCreate(char *obj,char *var, char *val) /* Creates a singular mdbObj query object based on obj and all other optional params. */ { struct mdbObj *mdbObj = NULL; if(obj == NULL) errAbort("Need obj to create mdbObj query object.\n"); AllocVar(mdbObj); mdbObj->obj = cloneString(obj); if(var != NULL) { struct mdbVar * mdbVar; AllocVar(mdbVar); mdbVar->var = cloneString(var); - mdbVar->varType = (varType==NULL?vtUnknown:mdbVarTypeStringToEnum(varType)); if(val != NULL) mdbVar->val = cloneString(val); mdbObj->vars = mdbVar; // Only one } return mdbObj; } struct mdbObj *mdbObjsLoadFromHashes(struct hash *objsHash) // Load all mdbObjs from a file containing metadata formatted lines { struct mdbObj *mdbObjs = NULL; struct hashEl* objEl = NULL; struct hashCookie objCookie = hashFirst(objsHash); while((objEl = hashNext(&objCookie)) != NULL) @@ -808,31 +760,30 @@ struct mdbObj *mdbObj; AllocVar(mdbObj); mdbObj->obj = cloneString(objEl->name); mdbObj->varHash = hashNew(0); struct hash *hashedVars = objEl->val; struct hashCookie varCookie = hashFirst(hashedVars); struct hashEl* varEl = NULL; while((varEl = hashNext(&varCookie)) != NULL) { if(sameString(varEl->name,MDB_METAOBJ_RAKEY)) continue; struct mdbVar * mdbVar; AllocVar(mdbVar); mdbVar->var = cloneString(varEl->name); - mdbVar->varType = vtTxt; // FIXME: binary? mdbVar->val = cloneString(varEl->val); hashAdd(mdbObj->varHash, mdbVar->var, mdbVar); // pointer to struct to resolve type slAddHead(&(mdbObj->vars),mdbVar); } slSort(&(mdbObj->vars),&mdbVarCmp); // Should be in determined order slAddHead(&mdbObjs,mdbObj); } slSort(&mdbObjs,&mdbObjCmp); // Should be in determined order return mdbObjs; } // ------ Loading from files ------ struct mdbObj *mdbObjsLoadFromFormattedFile(char *fileName,boolean *validated) // Load all mdbObjs from a file containing metadata formatted lines { @@ -896,36 +847,34 @@ verbose(3,"Can't find magic number on this file.\n"); } return mdbObjs; } // ------ Table name and creation ------ void mdbReCreate(struct sqlConnection *conn,char *tblName,boolean testOnly) // Creates ore Recreates the named mdb. { char *sqlCreate = "# Contains metadata for a table, file or other objects.\n" "CREATE TABLE %s (\n" " obj varchar(255) not null, # Object name or ID.\n" " var varchar(255) not null, # Metadata variable name.\n" -" varType enum ('txt','binary') # Most vars are txt\n" -" not null default 'txt',\n" -" val longblob not null, # Metadata value.\n" +" val varchar(2048) not null, # Metadata value.\n" " #Indices\n" " PRIMARY KEY(obj,var),\n" -" UNIQUE(var,val(32),obj)\n" +" INDEX varKey (var,val(32),obj)\n" ")"; if(sqlTableExists(conn,tblName)) verbose(2, "Table '%s' already exists. It will be recreated.\n",tblName); struct dyString *dy = newDyString(512); dyStringPrintf(dy, sqlCreate, tblName); verbose(2, "Requesting table creation:\n%s;\n", dyStringContents(dy)); if(!testOnly) sqlRemakeTable(conn, tblName, dyStringContents(dy)); dyStringFree(&dy); } static char*mdbTableNamePreferSandbox() @@ -1079,135 +1028,116 @@ count += delCnt; } } // Now it is time for update or add! for(mdbVar = mdbObj->vars;mdbVar != NULL; mdbVar = mdbVar->next) { stripEnclosingDoubleQuotes(mdbVar->val); // Ensures values are stripped of enclosing quotes // Be sure to check for var existence first, then update if (!replace) { struct mdbObj *objExists = mdbObjQueryByObj(conn,tableName,mdbObj->obj,mdbVar->var); if(objExists) { - if(differentString(mdbVar->val,objExists->vars->val) - || mdbVar->varType != objExists->vars->varType) + if(differentString(mdbVar->val,objExists->vars->val)) { safef(query, sizeof(query), - "update %s set varType = '%s', val = '%s' where obj = '%s' and var = '%s'", + "update %s set val = '%s' where obj = '%s' and var = '%s'", tableName, - mdbVarTypeEnumToString(mdbVar->varType),sqlEscapeString(mdbVar->val), // FIXME: binary val? + sqlEscapeString(mdbVar->val), mdbObj->obj,mdbVar->var); verbose(2, "Requesting update of 1 row:\n\t%s;\n",query); if(!testOnly) sqlUpdate(conn, query); count++; } mdbObjsFree(&objExists); continue; // The object was found/updated so done with it } } // Finally ready to insert new vars safef(query, sizeof(query), - "insert into %s values ( '%s','%s','%s','%s')", - tableName,mdbObj->obj,mdbVar->var,mdbVarTypeEnumToString(mdbVar->varType), - sqlEscapeString(mdbVar->val)); // FIXME: binary val? // FIXME Strip quotes + "insert into %s values ( '%s','%s','%s')", + tableName,mdbObj->obj,mdbVar->var, + sqlEscapeString(mdbVar->val)); // FIXME Strip quotes 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 { -char query[8192]; -struct mdbObj *mdbObj; -struct mdbVar *mdbVar; int count = 0; if (tableName == NULL) tableName = MDB_DEFAULT_NAME; if (!sqlTableExists(conn,tableName)) - errAbort("mdbObjsSetToDb attempting to update non-existent table named '%s'.\n",tableName); + errAbort("mdbObjsLoadToDb attempting to load non-existent table named '%s'.\n",tableName); -for(mdbObj = mdbObjs;mdbObj != NULL; mdbObj = mdbObj->next) - { - if (mdbObj->deleteThis) - continue; +assert(mdbObjs != NULL); // If this is the case, then be vocal - for(mdbVar = mdbObj->vars;mdbVar != NULL; mdbVar = mdbVar->next) - { - stripEnclosingDoubleQuotes(mdbVar->val); // Ensures values are stripped of enclosing quotes +#define MDB_TEMPORARY_TAB_FILE "temporaryMdb.tab" +#define SHOW_TIMING(last,msg) { int now = clock1000(); verbose(0,"%04ldms - %s\n",(now - (last)),(msg)); (last) = now; } +long lastTime = 0; +SHOW_TIMING(lastTime,"Before printing tab delimited file"); + +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 +SHOW_TIMING(lastTime,"Before loading the mdb from tab-delimited file"); +sqlLoadTabFile(conn, MDB_TEMPORARY_TAB_FILE, tableName, SQL_TAB_FILE_WARN_ON_ERROR|SQL_TAB_FILE_WARN_ON_WARN); +SHOW_TIMING(lastTime,"Done 'LOAD DATA INFILE' mysql command for the mdb"); + +// Enabling the keys again +safef(query, sizeof(query),"alter table %s enable keys",tableName); +sqlUpdate(conn, query); +SHOW_TIMING(lastTime,"Done re-enabling keys"); + +unlink(MDB_TEMPORARY_TAB_FILE); - // Finally ready to insert new vars - safef(query, sizeof(query), - "insert into %s values ( '%s','%s','%s','%s')", - tableName,mdbObj->obj,mdbVar->var,mdbVarTypeEnumToString(mdbVar->varType), - sqlEscapeString(mdbVar->val)); // FIXME: binary val? // FIXME Strip quotes - verbose(2, "Requesting insert of one row:\n\t%s;\n",query); - if (!testOnly) - { - // Use the sqlGetResultExt() instead of the normal sqlUpdate() in order to getany error messages - unsigned int errorNo = 0; - char *errorMsg = NULL; - struct sqlResult *sr = sqlGetResultExt(conn, query, &errorNo, &errorMsg); - if (errorNo != 0) - verbose(1, "INSERT failed: %s\n",errorMsg); - else - count++; - if (sr) // Should will be null, but just for good measure - sqlFreeResult(&sr); - } - else - { - struct mdbObj *objExists = mdbObjQueryByObj(conn,tableName,mdbObj->obj,mdbVar->var); - if(objExists) - { - verbose(1, "INSERT will fail for obj:%s and var:%s\n",mdbObj->obj,mdbVar->var); - mdbObjsFree(&objExists); - } - else - count++; // Of course this does not find duplicates within the mdbObjs list. - } - } - } 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)) return NULL; struct dyString *dy = newDyString(4096); - dyStringPrintf(dy, "select obj,var,varType,val from %s", table); + 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 dyStringPrintf(dy, " or "); if(mdbVar->var != NULL) { if(mdbVar->val != NULL) @@ -1219,65 +1149,65 @@ { if(mdbVar->var != NULL) dyStringPrintf(dy, " and "); dyStringPrintf(dy, "val %s '%s'", (strchr(mdbVar->val,'%')?"like":"="), sqlEscapeString(mdbVar->val)); if(mdbVar->var != NULL) dyStringPrintf(dy, ")"); } if(mdbVar->var == NULL && mdbVar->val) errAbort("mdbObjQuery has empty mdbVar struct.\n"); buildHash = FALSE; // too few variables } if(mdbObj->vars != NULL) dyStringPrintf(dy, ")"); } - dyStringPrintf(dy, " order by binary obj, var"); + dyStringPrintf(dy, " order by binary obj, var"); // binary forces case-sensitive sort verbose(2, "Requesting query:\n\t%s;\n",dyStringContents(dy)); struct mdb *mdb = mdbLoadByQuery(conn, dyStringCannibalize(&dy)); struct mdbObj *mdbObjs = mdbObjsLoadFromMemory(&mdb,buildHash); verbose(3, "Returned %d object(s) with %d var(s).\n", mdbObjCount(mdbObjs,TRUE),mdbObjCount(mdbObjs,FALSE)); return mdbObjs; } struct mdbObj *mdbObjQueryByObj(struct sqlConnection *conn,char *table,char *obj,char *var) // Query a single metadata object and optional var from a table (default mdb). { if(obj == NULL) return mdbObjQuery(conn,table,NULL); -struct mdbObj *queryObj = mdbObjCreate(obj,var,NULL,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)) return NULL; struct dyString *dy = newDyString(4096); - dyStringPrintf(dy, "select obj,var,varType,val from %s", table); + 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" if(rootVar->vals != NULL && rootVar->vals->val != NULL && strlen(rootVar->vals->val) > 0) { dyStringPrintf(dy, "%s '%s'", @@ -1323,64 +1253,64 @@ verbose(3, "rows (vars) returned: %d\n",slCount(mdb)); struct mdbByVar *mdbByVarsFromMem = mdbByVarsLoadFromMemory(&mdb,TRUE); verbose(3, "Returned %d vars(s) with %d val(s) with %d object(s).\n", mdbByVarCount(mdbByVarsFromMem,TRUE ,FALSE), mdbByVarCount(mdbByVarsFromMem,FALSE,TRUE ), mdbByVarCount(mdbByVarsFromMem,FALSE,FALSE)); return mdbByVarsFromMem; } struct mdbByVar *mdbByVarQueryByVar(struct sqlConnection *conn,char *table,char *varName,char *val) // Query a single metadata variable and optional val from a table (default mdb) for searching val->obj. { if(varName == NULL) return mdbByVarsQuery(conn,table,NULL); -struct mdbByVar *queryVar = mdbByVarCreate(varName,NULL,val); +struct mdbByVar *queryVar = mdbByVarCreate(varName,val); struct mdbByVar *resultVar = mdbByVarsQuery(conn,table,queryVar); mdbByVarsFree(&queryVar); return resultVar; } struct mdbObj *mdbObjsQueryByVars(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 ALL conditions. // Returns new mdbObj struct fully populated and sorted in obj,var order. { // MOST POPULAR WAY TO QUERY MDB. Building example queries like: // "cell=GM12878" or "cell!=GM12878" -// SELECT T1.obj,T1.var,T1.varType,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; -// SELECT T1.obj,T1.var,T1.varType,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; +// 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; +// 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.varType,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.varType,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; +// 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.varType,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.varType,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; +// 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.varType,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.varType,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; +// 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.varType,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%') +// 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)) return NULL; struct dyString *dy = newDyString(4096); - dyStringPrintf(dy, "SELECT T1.obj,T1.var,T1.varType,T1.val FROM %s T1", table); + 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 "); gotVar=TRUE; } else dyStringPrintf(dy, " AND "); @@ -1416,56 +1346,54 @@ (strchr(limbVal->val,'%')?"LIKE":"="), sqlEscapeString(limbVal->val)); break; } else dyStringPrintf(dy, "IN ("); multiVals=TRUE; } else dyStringPrintf(dy, ","); dyStringPrintf(dy, "'%s'", sqlEscapeString(limbVal->val)); } if(multiVals) dyStringPrintf(dy, ")"); dyStringPrintf(dy, ")"); } - dyStringPrintf(dy, " ORDER BY binary T1.obj, T1.var"); + dyStringPrintf(dy, " ORDER BY binary T1.obj, T1.var"); // binary forces case sensitive sort verbose(2, "Requesting query:\n\t%s;\n",dyStringContents(dy)); struct mdb *mdb = mdbLoadByQuery(conn, dyStringCannibalize(&dy)); verbose(3, "rows (vars) returned: %d\n",slCount(mdb)); struct mdbObj *mdbObjs = mdbObjsLoadFromMemory(&mdb,TRUE); verbose(3, "Returned %d object(s) with %d var(s).\n", mdbObjCount(mdbObjs,TRUE),mdbObjCount(mdbObjs,FALSE)); return mdbObjs; } // ----------- Printing and Counting ----------- static void mdbVarValPrint(struct mdbVar *mdbVar,boolean raStyle, FILE *outF) { if(mdbVar != NULL && mdbVar->var != NULL) { if(raStyle) fprintf(outF, "\n%s ",mdbVar->var); else fprintf(outF, " %s=",mdbVar->var); if(mdbVar->val != NULL) { - if(mdbVar->varType == vtBinary) - fprintf(outF, "binary"); - else if(!raStyle && strchr(mdbVar->val, ' ') != NULL) // Has blanks + if(!raStyle && strchr(mdbVar->val, ' ') != NULL) // Has blanks fprintf(outF, "\"%s\"",mdbVar->val); else fprintf(outF, "%s",mdbVar->val); } } } void mdbObjPrintToStream(struct mdbObj *mdbObjs,boolean raStyle, FILE *outF ) // prints objs and var=val pairs as formatted metadata lines or ra style { // Single line: // metadata iLoveLucy table lucy=ricky ethyl=fred // ra style // metadata iLoveLucy table @@ -1506,101 +1434,122 @@ { if (mdbObj!=NULL) { struct dyString *dyLine = dyStringNew(128); struct mdbVar *mdbVar = NULL; // If hash available, force objType to front if (!objTypeExclude && mdbObj->varHash != NULL) { mdbVar = hashFindVal(mdbObj->varHash,MDB_OBJ_TYPE); dyStringPrintf(dyLine,"%s=%s; ",mdbVar->var,mdbVar->val); } for(mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) { if (!sameOk(MDB_OBJ_TYPE,mdbVar->var) || (!objTypeExclude && mdbObj->varHash == NULL)) - { - if (mdbVar->varType == vtTxt) dyStringPrintf(dyLine,"%s=%s; ",mdbVar->var,mdbVar->val); } - } char *line = dyStringCannibalize(&dyLine); if (line) { int len = strlen(line); if (len == 0) { freeMem(line); return NULL; } if (line[len-1] == ' ') line[len-1] = '\0'; return line; } } return NULL; } void mdbObjPrint(struct mdbObj *mdbObjs,boolean raStyle) // prints objs and var=val pairs as formatted metadata lines or ra style { mdbObjPrintToStream(mdbObjs, raStyle, stdout); } void mdbObjPrintToFile(struct mdbObj *mdbObjs,boolean raStyle, char *file) -// prints objs and var=val pairs as formatted metadata lines or ra style +// prints (to file) objs and var=val pairs as formatted metadata lines or ra style { FILE *f = mustOpen(file, "w"); mdbObjPrintToStream(mdbObjs, raStyle, f); fclose(f); } +int mdbObjPrintToTabFile(struct mdbObj *mdbObjs, char *file) +// prints all objs as tab delimited obj var val into file for SQL LOAD DATA. Returns count. +{ +FILE *tabFile = mustOpen(file, "w"); +int count = 0; + +struct mdbObj *mdbObj = NULL; +for(mdbObj=mdbObjs;mdbObj!=NULL;mdbObj=mdbObj->next) + { + if(mdbObj->obj == NULL) + continue; + + struct mdbVar *mdbVar = NULL; + for(mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) + { + if (mdbVar->var == NULL || mdbVar->val == NULL) + continue; + fprintf(tabFile, "%s\t%s\t%s\n",mdbObj->obj,mdbVar->var,sqlEscapeString(mdbVar->val)); + count++; + } + } + +fclose(tabFile); +return count; +} + void mdbByVarPrint(struct mdbByVar *mdbByVars,boolean raStyle) // prints var=val pairs and objs that go with them single lines or ra style { // Single line: // mdbVariable lucy=ethyl bestFriends lifePartners // mdbVariable lucy=ricky iLoveLucy divorces // NOT QUITE ra style // metadata Fred wife=Ethyl // metadata Lucy wife=Ethyl // Results in: // mdbVariable wife Ethyl // metaObject Fred // metaObject Lucy struct mdbByVar *rootVar = NULL; for(rootVar=mdbByVars;rootVar!=NULL;rootVar=rootVar->next) { if(rootVar->var == NULL) continue; struct mdbLimbVal *limbVal = NULL; for(limbVal=rootVar->vals;limbVal!=NULL;limbVal=limbVal->next) { if(limbVal->val == NULL) continue; if(raStyle) printf("%s %s ",MDB_METAVAR_RAKEY,rootVar->var); else printf("%s %s=",MDB_METAVAR_RAKEY,rootVar->var); - if(rootVar->varType == vtBinary) - printf("binary"); - else if(!raStyle && strchr(limbVal->val, ' ') != NULL) // Has blanks + if(!raStyle && strchr(limbVal->val, ' ') != NULL) // Has blanks printf("\"%s\"",limbVal->val); else printf("%s",limbVal->val); struct mdbLeafObj *leafObj = NULL; for(leafObj=limbVal->objs;leafObj!=NULL;leafObj=leafObj->next) { if(leafObj->obj == NULL) continue; if(raStyle) printf("\n%s %s",MDB_METAOBJ_RAKEY,leafObj->obj); else printf(" %s",leafObj->obj); } @@ -2022,42 +1971,40 @@ mdbObjRemoveVars(mdb,var); } return val; } boolean mdbObjSetVar(struct mdbObj *mdbObj, char *var,char *val) // Sets the string value to a single var into an obj, preparing for DB update. // returns TRUE if updated, FALSE if added { assert(mdbObj != NULL && var != NULL && val != NULL); struct mdbVar *mdbVar = mdbObjFind(mdbObj, var); if (mdbVar != NULL) { if (mdbVar->val != NULL) freeMem(mdbVar->val); - mdbVar->varType = vtTxt; mdbVar->val = cloneString(val); if (mdbObj->varHash != NULL) hashReplace(mdbObj->varHash, mdbVar->var, mdbVar); // pointer to struct to resolve type return TRUE; } else { AllocVar(mdbVar); mdbVar->var = cloneString(var); - mdbVar->varType = vtTxt; mdbVar->val = cloneString(val); slAddHead(&mdbObj->vars,mdbVar); // Only one if (mdbObj->varHash != NULL) hashAdd(mdbObj->varHash, mdbVar->var, mdbVar); // pointer to struct to resolve type return FALSE; } } boolean mdbObjSetVarInt(struct mdbObj *mdbObj, char *var,int val) // Sets an integer value to a single var in an obj, preparing for DB update. // returns TRUE if updated, FALSE if added { char buf[128]; safef(buf,sizeof(buf),"%d",val); return mdbObjSetVar(mdbObj,var,buf); @@ -2206,51 +2153,50 @@ if (hashLookup(hashB, mdbObj->obj) != NULL) slAddHead(&mdbObjsIntersecting,mdbObj); else slAddHead(&mdbObjsDropped,mdbObj); } hashFree(&hashB); if (mdbObjsIntersecting) slReverse(&mdbObjsIntersecting); *pA = mdbObjsIntersecting; if (mdbObjsDropped) slReverse(&mdbObjsDropped); return mdbObjsDropped; } -void mdbObjTransformToUpdate(struct mdbObj *mdbObjs, char *var, char *varType,char *val,boolean deleteThis) +void mdbObjTransformToUpdate(struct mdbObj *mdbObjs, char *var, char *val,boolean deleteThis) // Turns one or more mdbObjs into the stucture needed to add/update or delete. { struct mdbObj *mdbObj = NULL; for( mdbObj=mdbObjs; mdbObj!=NULL; mdbObj=mdbObj->next ) { mdbObj->deleteThis = deleteThis; if(mdbObj->varHash != NULL) hashFree(&mdbObj->varHash); struct mdbVar *mdbVar = NULL; while((mdbVar = slPopHead(&(mdbObj->vars))) != NULL) mdbVarFree(&mdbVar); if(var != NULL) { AllocVar(mdbVar); mdbVar->var = cloneString(var); - mdbVar->varType = (varType==NULL?vtUnknown:mdbVarTypeStringToEnum(varType)); if(val != NULL) mdbVar->val = cloneString(val); mdbObj->vars = mdbVar; // Only one } } } struct mdbObj *mdbObjClone(const struct mdbObj *mdbObj) // Clones a single mdbObj, including hash and maintining order { if(mdbObj == NULL) return NULL; struct mdbObj *newObj; AllocVar(newObj); @@ -2260,31 +2206,30 @@ newObj->deleteThis = mdbObj->deleteThis; if(mdbObj->vars != NULL) { if(mdbObj->varHash != NULL) newObj->varHash = hashNew(0); struct mdbVar *mdbVar = NULL; for(mdbVar = mdbObj->vars; mdbVar != NULL; mdbVar = mdbVar->next ) { struct mdbVar *newVar = NULL; AllocVar(newVar); if(mdbVar->var != NULL) newVar->var = cloneString(mdbVar->var); if(mdbVar->val != NULL) newVar->val = cloneString(mdbVar->val); - newVar->varType = mdbVar->varType; if(newVar->var != NULL && newVar->val != NULL) hashAdd(newObj->varHash, newVar->var, newVar); // pointer to struct to resolve type slAddHead(&(newObj->vars),newVar); } slReverse(&(newObj->vars)); } return newObj; } struct slName *mdbObjToSlName(struct mdbObj *mdbObjs) // Creates slNames list of mdbObjs->obj. mdbObjs remains untouched { struct slName *mdbNames = NULL; struct mdbObj *mdbObj = mdbObjs; for( ;mdbObj!=NULL; mdbObj=mdbObj->next) @@ -2693,31 +2638,31 @@ if (warn > 0) printf(" %s obj='%s' has bad %s=%s.\n",experimentId,obj->obj,EXP_ID_NAME,val); } } else { updateObj = (expId != -1); if ((foundId && warn > 0) || warn > 1) printf(" %s obj='%s' has no %s.\n",experimentId,obj->obj,EXP_ID_NAME); } // This object needs to be updated. if (updateObj) { mdbObjSetVarInt(obj,EXP_ID_NAME,expId); - struct mdbObj *newObj = mdbObjCreate(obj->obj,EXP_ID_NAME, "txt" ,experimentId); + struct mdbObj *newObj = mdbObjCreate(obj->obj,EXP_ID_NAME, experimentId); char buf[128]; safef(buf,sizeof(buf),"wgEncode%c%06d",(startsWith("mm",db)?'M':'H'),expId); mdbObjSetVar(newObj,DCC_ACCESSION,buf); slAddHead(&mdbUpdateObjs,newObj); } slAddHead(&mdbProcessedObs,obj); } // Done with one experiment if (!foundId && errors > 0) { expMissing++; if (warn > 0) printf(" %s all %d objects are missing an %s.\n",experimentId,objsInExp,EXP_ID_NAME); } @@ -2874,31 +2819,31 @@ return mdbObjFindValue(mdbObj,var); } struct mdbObj *mdbObjSearch(struct sqlConnection *conn, char *var, char *val, char *op, int limit) // Search the metaDb table for objs by var and val. Can restrict by op "is", "like", "in" and accept (non-zero) limited string size // Search is via mysql, so it's case-insensitive. Return is sorted on obj. { if (var == NULL && val == NULL) errAbort("mdbObjSearch requests objects but provides no criteria.\n"); char *tableName = mdbTableName(conn,TRUE); // Look for sandBox name first // Build a query string struct dyString *dyQuery = dyStringNew(512); -dyStringPrintf(dyQuery,"select l1.obj, l1.var, l1.varType, l1.val from %s l1",tableName); +dyStringPrintf(dyQuery,"select l1.obj, l1.var, l1.val from %s l1",tableName); if (var != NULL || val != NULL) dyStringPrintf(dyQuery," where exists (select l2.obj from %s l2 where l2.obj = l1.obj and ",tableName); if(var != NULL) dyStringPrintf(dyQuery,"l2.var = '%s'", var); if(var != NULL && val != NULL) dyStringAppend(dyQuery," and "); if(val != NULL) { dyStringAppend(dyQuery,"l2.val "); if(sameString(op, "in")) dyStringPrintf(dyQuery,"in (%s)", val); // Note, must be a formatted string already: 'a','b','c' or 1,2,3 else if(sameString(op, "contains") || sameString(op, "like")) dyStringPrintf(dyQuery,"like '%%%s%%'", val); else if (limit > 0 && strlen(val) != limit)