fa9a4052cbc6e42d290560755e5f8302c267ce7e tdreszer Mon Jun 28 16:15:42 2010 -0700 Added special features to mdbPrint to print SQL insert statements for expTable and spacial case extension for adding expIds to exisiting mdb objects. diff --git src/hg/lib/mdb.c src/hg/lib/mdb.c index 0e8a9a7..ffc8c7e 100644 --- src/hg/lib/mdb.c +++ src/hg/lib/mdb.c @@ -1,2053 +1,2136 @@ /* mdb.c was originally generated by the autoSql program, which also * generated mdb.h and mdb.sql. This module links the database and * the RAM representation of objects. */ #include "common.h" #include "linefile.h" #include "dystring.h" #include "jksql.h" #include "mdb.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]; } 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) { el = mdbLoad(row); slAddHead(&list, el); } 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); 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; 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); 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]); 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); } lineFileClose(&lf); slReverse(&list); return list; } struct mdb *mdbLoadAllByChar(char *fileName, char chopper) /* Load all mdb from a chopper separated file. * Dispose of this with mdbFreeList(). */ { struct mdb *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[4]; while (lineFileNextCharRow(lf, chopper, row, ArraySize(row))) { el = mdbLoad(row); slAddHead(&list, el); } lineFileClose(&lf); 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" #define MDB_METADATA_KEY "metadata" #define MDB_METAOBJ_RAKEY "metaObject" #define MDB_METAVAR_RAKEY "metaVariable" #define MDB_OBJ_TYPE "objType" // ------- (static) convert from autoSql ------- static void mdbVarFree(struct mdbVar **mdbVarPtr) // Frees a single mdbVar struct { freeMem((*mdbVarPtr)->val); freeMem((*mdbVarPtr)->var); freez(mdbVarPtr); } static void mdbLeafObjFree(struct mdbLeafObj **leafObjPtr) // Frees a single mdbVar struct { freeMem((*leafObjPtr)->obj); freez(leafObjPtr); } static void mdbLimbValFree(struct mdbLimbVal **limbValPtr) // Frees a single mdbVar struct { struct mdbLimbVal *limbVal = *limbValPtr; // Free hash first (shared memory) hashFree(&(limbVal->objHash)); struct mdbLeafObj *leafObj = NULL; while((leafObj = slPopHead(&(limbVal->objs))) != NULL) mdbLeafObjFree(&leafObj); freeMem(limbVal->val); freez(limbValPtr); } static struct mdbObj *mdbObjsLoadFromMemory(struct mdb **mdbPtr,boolean buildHashes) // Load all mdbObjs from in memory mdb struct, cannibalize strings. Expects sorted order. { struct mdbObj *mdbObj = NULL; struct mdbObj *mdbObjs = NULL; struct mdbVar *mdbVar; struct mdb *thisRow; while((thisRow = slPopHead(mdbPtr)) != NULL) { if (mdbObj == NULL || differentString(thisRow->obj,mdbObj->obj) ) { // Finish last object before starting next! if(mdbObj!= NULL) 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; } static struct mdbByVar *mdbByVarsLoadFromMemory(struct mdb **mdbPtr,boolean buildHashes) // Load all mdbVars from in memorys mdb struct, cannibalize strings. Expects sorted order. { struct mdbByVar *rootVars = NULL; struct mdbByVar *rootVar = NULL; struct mdbLimbVal *limbVal = NULL; struct mdbLeafObj *leafObj; 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? 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! slAddHead(&(limbVal->objs),leafObj); freeMem(thisRow); } // Finish very last object if(rootVars && rootVars->vals && rootVars->vals->objs) slReverse(&(rootVars->vals->objs)); if(rootVars && rootVars->vals) slReverse(&(rootVars->vals)); if(rootVars && rootVars->vals) slReverse(&rootVars); return rootVars; } 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) 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) { errAbort("This is not formatted var=val pairs:\n\t%s\n",varPairs); } verbose(3, "mdbObjAddVarPairs() word count:%d\n\t%s\n",count,varPairs); 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); } } freeMem(words); freeMem(cloneVars); // Special for old style ENCODE metadata #define ENCODE_ALN "Alignments" #define ENCODE_RSIG "RawSignal" if(mdbObj->obj == NULL) { char * tableName = NULL; char * fileName = NULL; for(mdbVar = mdbObj->vars; mdbVar != NULL && (tableName == NULL || fileName == NULL); mdbVar = mdbVar->next) { if(sameString(mdbVar->var,"tableName")) tableName = mdbVar->val; else if(sameString(mdbVar->var,"fileName")) fileName = mdbVar->val; } mdbVar = NULL; // assertably so, but this is conditioanally created below if(tableName != NULL) { verbose(3, "tableName:%s\n",tableName); if(fileName == NULL || startsWithWordByDelimiter(tableName,'.',fileName)) { mdbObj->obj = cloneString(tableName); AllocVar(mdbVar); mdbVar->var = cloneString(MDB_OBJ_TYPE); mdbVar->val = cloneString("table"); } else if(stringIn(ENCODE_ALN,fileName) && stringIn(ENCODE_RSIG,tableName))// Messier case where the file has "Alignment" but the table has "RawSignal" { char *tmpFilName = cloneString(fileName); strSwapStrs(tmpFilName, strlen(tmpFilName),ENCODE_ALN, ENCODE_RSIG); if(startsWithWordByDelimiter(tableName,'.',tmpFilName)) { mdbObj->obj = cloneString(tableName); AllocVar(mdbVar); mdbVar->var = cloneString(MDB_OBJ_TYPE); mdbVar->val = cloneString("table"); } freeMem(tmpFilName); } } else if(fileName != NULL) { verbose(3, "fileName:%s\n",fileName); // NOTE: that the file object is the root of the name, so both file.fastq.gz and file.fastq are same obj! mdbObj->obj = cloneFirstWordByDelimiter(fileName,'.'); AllocVar(mdbVar); mdbVar->var = cloneString(MDB_OBJ_TYPE); mdbVar->val = cloneString("file"); } if(mdbVar != NULL) // Just determined an objType { verbose(3, "mdbObjAddVarPairs() var=val: %s=%s\n",mdbVar->var,mdbVar->val); struct mdbVar *oldVar = (struct mdbVar *)hashFindVal(mdbObj->varHash, mdbVar->var); if(oldVar) mdbVarFree(&mdbVar); else { hashAdd(mdbObj->varHash, mdbVar->var, mdbVar); // pointer to struct to resolve type slAddHead(&(mdbObj->vars),mdbVar); } } } if(mdbObj->obj == NULL) // NOTE: Should this be a hard error! errAbort("No obj found. This is not properly formatted metadata:\n\t%s\n",varPairs); //slReverse(&(mdbObj->vars)); Could have added vars so sort instead slSort(&(mdbObj->vars),&mdbVarCmp); // Should be in determined order mdbVar = (struct mdbVar *)hashFindVal(mdbObj->varHash, MDB_OBJ_TYPE); if(mdbVar == NULL) mdbVar = mdbObj->vars; verbose(3, "mdbObjAddVarPairs() obj=%s %s=%s\n", mdbObj->obj, mdbVar->var,mdbVar->val); return mdbObj; } struct mdbObj *metadataLineParse(char *line) /* Parses a single formatted metadata line into mdbObj for updates or queries. */ { char *fromTheTop = line; char*nibbledWord = cloneNextWordByDelimiter(&line,' '); if(nibbledWord == NULL || differentWord(nibbledWord,MDB_METADATA_KEY)) errAbort("This is not a formatted metadata line:\n\t%s\n",fromTheTop); freeMem(nibbledWord); struct mdbObj *mdbObj = NULL; char*varPairs = line; nibbledWord = cloneNextWordByDelimiter(&line,' ');; if(nibbledWord == NULL) errAbort("This is not a formatted metadata line:\n\t%s\n",fromTheTop); if(strchr(nibbledWord, '=') == NULL) // If this is not a var=val then it should be obj { AllocVar(mdbObj); mdbObj->obj = nibbledWord; verbose(3, "metadataLineParse() obj=%s\n",mdbObj->obj); varPairs = line; while(strlen(line) > 0) { nibbledWord = cloneNextWordByDelimiter(&line,' ');; if(nibbledWord == NULL) errAbort("This is not a formatted metadata line:\n\t%s\n",fromTheTop); if(*nibbledWord == '#' || strchr(nibbledWord, '=') != NULL) // IS commnet OR start of var=val pairs break; if(sameWord(nibbledWord,"delete")) mdbObj->deleteThis = TRUE; else errAbort("This is not a formatted metadata line:\n\t%s\n",fromTheTop); varPairs = line; freeMem(nibbledWord); } } if(varPairs != NULL && strlen(varPairs) > 0 && *varPairs != '#') mdbObj = mdbObjAddVarPairs(mdbObj,varPairs); else if(mdbObj->deleteThis == FALSE) errAbort("This is not a formatted metadata line:\n\t%s\n",fromTheTop); return mdbObj; } struct mdbByVar *mdbByVarsLineParse(char *line) /* Parses a line of "var1=val1 var2=val2 into a mdbByVar object for queries. */ { int thisWord = 0; struct mdbByVar *mdbByVars = NULL; struct mdbByVar *rootVar = NULL; struct mdbLimbVal *limbVal = NULL; char *cloneLine = cloneString(line); struct hash* varHash; // There must not be multiple occurrances of the same var // initial chop and determine if this looks like metadata int count = chopByWhiteRespectDoubleQuotes(cloneLine,NULL,0); char **words = needMem(sizeof(char *) * count); count = chopByWhiteRespectDoubleQuotes(cloneLine,words,count); verbose(3, "mdbByVarsLineParse() word count:%d\n\t%s\n",count,line); // Get obj and figure out if this is a delete line varHash = hashNew(0); // All words are expected to be var=val pairs! for(thisWord=0;thisWord<count;thisWord++) { if(strchr(words[thisWord], '=') == NULL) { errAbort("Expected 'var=val' but found '%s'. This is not properly formatted metadata:\n\t%s\n",words[thisWord],line); //mdbObjsFree(&mdbObj); //return NULL; } AllocVar(rootVar); rootVar->var = cloneNextWordByDelimiter(&(words[thisWord]),'='); rootVar->notEqual = (rootVar->var[strlen(rootVar->var)-1] == '!'); // requested not equal if(rootVar->notEqual) rootVar->var[strlen(rootVar->var)-1] = 0; char *val = cloneString(words[thisWord]); if(sameWord(val,"?")) // "var=?" or "var=" will query by var name only freez(&val); struct mdbByVar *oldVar = (struct mdbByVar *)hashFindVal(varHash, rootVar->var); if(oldVar) { // FIXME: Could build this for 'or' queries! verbose(1, "The same variable appears twice: %s=%s and %s=%s. Ignoring second value.\n", oldVar->var,oldVar->vals->val,rootVar->var,val); freeMem(rootVar->var); freeMem(rootVar); freeMem(val); } else { AllocVar(limbVal); 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->vals),mdbByVars->var,(mdbByVars->notEqual?"!":""),mdbByVars->vals->val); return mdbByVars; } // ------ Loading from args, hashes and tdb ------ struct mdbByVar*mdbByVarCreate(char *var, char *varType,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; } struct mdbObj *mdbObjCreate(char *obj,char *var, char *varType,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) { 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 { struct mdbObj *mdbObjs = NULL; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *line; while (lineFileNext(lf, &line,NULL)) { char *start = skipLeadingSpaces(line); if(start == NULL || *start == '#') continue; if(startsWithWord(MDB_METAOBJ_RAKEY,line)) { // This is the RA style file!! lineFileClose(&lf); return mdbObjsLoadFromRAFile(fileName,validated); } struct mdbObj *mdbObj = metadataLineParse(line); if(mdbObj == NULL) { mdbObjsFree(&mdbObjs); return NULL; } slAddHead(&mdbObjs,mdbObj); } lineFileClose(&lf); slReverse(&mdbObjs); // Go ahead and keep this in file order if(validated) *validated = FALSE; return mdbObjs; } #define MDB_MAGIC_PREFIX "# MAGIC: " struct mdbObj *mdbObjsLoadFromRAFile(char *fileName,boolean *validated) // Load all mdbObjs from a file containing RA formatted 'metaObjects' { struct hash *mdHash = raReadAll(fileName, MDB_METAOBJ_RAKEY); if(mdHash == NULL) { verbose(1,"Missing, empty or badly formated RA file:%s\n",fileName); return NULL; } struct mdbObj *mdbObjs = mdbObjsLoadFromHashes(mdHash); hashFree(&mdHash); // Try to validate file if(validated) { *validated = FALSE; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *line = lineFileSkipToLineStartingWith(lf,MDB_MAGIC_PREFIX,1000000); if(line != NULL) { int fileMagic = atoi(line+strlen(MDB_MAGIC_PREFIX)); int objsMagic = mdbObjCRC(mdbObjs); verbose(3,"Objects magic: %d Files magic: %d (%s)\n",objsMagic,fileMagic,line+strlen(MDB_MAGIC_PREFIX)); *validated = (fileMagic == objsMagic); } else verbose(3,"Can't find magic number on this file.\n"); } return mdbObjs; } // ------ Table name and creation ------ #define MDB_SPEC_LOCATION "/cluster/bin/sqlCreate/metaDb.sql" void mdbReCreate(struct sqlConnection *conn,char *tblName,boolean testOnly) // Creates ore Recreates the named mdb. { if(sqlTableExists(conn,tblName)) verbose(2, "Table '%s' already exists. It will be recreated.\n",tblName); char *sql = NULL; readInGulp(MDB_SPEC_LOCATION, &sql, NULL); char *pos = strchr(sql, ';'); if ( pos != NULL) *pos = 0; char *oldSql = cloneString(sql); pos = stringIn("CREATE TABLE ", oldSql); if (pos == NULL) errAbort("Can't find CREATE TABLE in %s\n", MDB_SPEC_LOCATION); nextWord(&pos); nextWord(&pos); char *oldName = nextWord(&pos); if(differentWord(oldName, tblName)) { char *saveSql = cloneString(sql); freeMem(sql); sql = replaceChars(saveSql, oldName, tblName); freeMem(saveSql); } freeMem(oldSql); verbose(2, "Requesting table creation:\n\t%s;\n", sql); if(!testOnly) sqlRemakeTable(conn,tblName, sql); freeMem(sql); } char*mdbTableName(struct sqlConnection *conn,boolean mySandBox) // returns the mdb table name or NULL if conn supplied but the table doesn't exist { char *tblName = NULL; char *root = NULL; char *sand = NULL; char *name = cfgOption("db.metaDb"); if(name == NULL) { name = cfgOption("db.trackDb"); if(name == NULL) root = cloneString(MDB_DEFAULT_NAME); } // Divide name into root and sand if(root == NULL) { char delimit = '_'; if((sand = strchr(name,delimit)) == NULL) { delimit = '-'; if((sand = strchr(name,delimit)) == NULL) root = cloneString(name); // No sandBox portion } if(root == NULL) // There should be a sandbox portion { root = cloneNextWordByDelimiter(&name,delimit); if(mySandBox && *name != 0) sand = name; } } // Since db.trackDb was used, make sure to swap it if(sameWord("trackDb",root)) { freeMem(root); root = cloneString(MDB_DEFAULT_NAME); } if(!mySandBox || sand == NULL) tblName = root; else { int size = strlen(root) + strlen(sand) + 2; tblName = needMem(size); safef(tblName,size,"%s_%s",root,sand); } // Test for table if(conn != NULL && !sqlTableExists(conn,tblName)) { if(sand == NULL || sameWord(tblName,root)) // Then try the root return NULL; freeMem(tblName); tblName = root; if(!sqlTableExists(conn,tblName)) return NULL; } return tblName; } // -------------- 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)) 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), "delete from %s where obj = '%s'",tableName,mdbObj->obj); verbose(2, "Requesting delete of %d rows:\n\t%s;\n",delCnt, query); if(!testOnly) sqlUpdate(conn, query); count += delCnt; } } else // deletes selected vars { for(mdbVar = mdbObj->vars;mdbVar != NULL; mdbVar = mdbVar->next) { safef(query, sizeof(query), "select obj from %s where obj = '%s' and var = '%s'", tableName,mdbObj->obj,mdbVar->var); if(sqlExists(conn,query)) { safef(query, sizeof(query), "delete from %s where obj = '%s' and var = '%s'", tableName,mdbObj->obj,mdbVar->var); verbose(2, "Requesting delete of 1 row:\n\t%s;\n",query); if(!testOnly) sqlUpdate(conn, query); count++; } } } continue; // Done with this mdbObj } else if (replace) // If replace then clear out deadwood before inserting new vars { safef(query, sizeof(query),"%s where obj = '%s'",tableName,mdbObj->obj); int delCnt = sqlRowCount(conn,query); if(delCnt>0) { safef(query, sizeof(query), "delete from %s where obj = '%s'",tableName,mdbObj->obj); verbose(2, "Requesting replacement of %d rows:\n\t%s;\n",delCnt, query); if(!testOnly) sqlUpdate(conn, query); 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) { safef(query, sizeof(query), "update %s set varType = '%s', val = '%s' where obj = '%s' and var = '%s'", tableName, mdbVarTypeEnumToString(mdbVar->varType),sqlEscapeString(mdbVar->val), // FIXME: binary 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 verbose(2, "Requesting insert of one row:\n\t%s;\n",query); if(!testOnly) sqlUpdate(conn, query); count++; } } 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); 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) dyStringPrintf(dy, "("); dyStringPrintf(dy, "var %s '%s'", (strchr(mdbVar->var,'%')?"like":"="),mdbVar->var); } if(mdbVar->val != NULL) { 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 obj, var"); 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 *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); 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'", (strchr(rootVar->var,'%')?"like":"="), rootVar->var); } else dyStringPrintf(dy, "EXISTS"); struct mdbLimbVal *limbVal; boolean multiVals = FALSE; for(limbVal=rootVar->vals;limbVal!=NULL;limbVal=limbVal->next) { if(limbVal->val == NULL || strlen(limbVal->val) < 1) continue; if(!multiVals) { dyStringPrintf(dy, " and val "); if(rootVar->notEqual) dyStringPrintf(dy, "%s",strchr(limbVal->val,'%')?"NOT ":"!"); if(limbVal->next == NULL) // only one val { dyStringPrintf(dy, "%s '%s'", (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 var, val, obj"); 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 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 *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. { // select obj,var,val where (var= [and val in (val1,val2)]) or (var= [and val in (val1,val2)]) order by obj,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); struct mdbByVar *rootVar; boolean gotVar = FALSE; int tix; for(rootVar=mdbByVars,tix=2;rootVar!=NULL;rootVar=rootVar->next,tix++) { /////////////// 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 "); if(!hasVal && rootVar->notEqual) dyStringPrintf(dy, "NOT EXISTS "); else dyStringPrintf(dy, "EXISTS "); dyStringPrintf(dy, "(SELECT T%d.obj FROM %s T%d WHERE T%d.obj = T1.obj AND T%d.var ",tix,table,tix,tix,tix); if(hasVal && rootVar->notEqual && rootVar->vals == NULL) dyStringPrintf(dy, "%s",strchr(rootVar->var,'%')?"NOT ":"!"); dyStringPrintf(dy, "%s '%s'", (strchr(rootVar->var,'%')?"LIKE":"="), rootVar->var); /////////////// // if(!gotVar) // { // dyStringPrintf(dy, " where t1.obj in "); // gotVar=TRUE; // } // else // dyStringPrintf(dy, " AND t1.obj in "); // dyStringPrintf(dy, "(select t%d.obj from %s t%d where t%d.obj = t1.obj and t%d.var ",tix,table,tix,tix,tix); // // if(rootVar->notEqual && rootVar->vals == NULL) // dyStringPrintf(dy, "%s",strchr(rootVar->var,'%')?"NOT ":"!"); // // dyStringPrintf(dy, "%s '%s'", // (strchr(rootVar->var,'%')?"like":"="), rootVar->var); struct mdbLimbVal *limbVal; boolean multiVals = FALSE; for(limbVal=rootVar->vals;limbVal!=NULL;limbVal=limbVal->next) { if(limbVal->val == NULL || strlen(limbVal->val) < 1) continue; if(!multiVals) { dyStringPrintf(dy, " AND T%d.val ",tix); if(rootVar->notEqual) dyStringPrintf(dy, "%s",strchr(limbVal->val,'%')?"NOT ":"!"); if(limbVal->next == NULL) // only one val { dyStringPrintf(dy, "%s '%s'", (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 T1.obj, T1.var"); 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) { if(mdbVar != NULL && mdbVar->var != NULL) { if(raStyle) printf("\n%s ",mdbVar->var); else printf(" %s=",mdbVar->var); if(mdbVar->val != NULL) { if(mdbVar->varType == vtBinary) printf("binary"); else if(!raStyle && strchr(mdbVar->val, ' ') != NULL) // Has blanks printf("\"%s\"",mdbVar->val); else printf("%s",mdbVar->val); } } } void mdbObjPrint(struct mdbObj *mdbObjs,boolean raStyle) // 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 // lucy ricky // ethy fred // TODO: Expand for mutilple var types; strip quotes from vals on ra style struct mdbObj *mdbObj = NULL; for(mdbObj=mdbObjs;mdbObj!=NULL;mdbObj=mdbObj->next) { if(mdbObj->obj == NULL) continue; printf("%s %s",(raStyle?MDB_METAOBJ_RAKEY:MDB_METADATA_KEY),mdbObj->obj); if(mdbObj->deleteThis) printf(" delete"); struct mdbVar *mdbVar = NULL; // If hash available, force objType to front if(mdbObj->varHash != NULL) { mdbVar = hashFindVal(mdbObj->varHash,MDB_OBJ_TYPE); mdbVarValPrint(mdbVar,raStyle); } for(mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) { if(mdbObj->varHash == NULL || !sameOk(MDB_OBJ_TYPE,mdbVar->var)) mdbVarValPrint(mdbVar,raStyle); } printf("%s",(raStyle?"\n\n":"\n")); } if(raStyle) // NOTE: currently only supporting validation of RA files printf("%s%d\n",MDB_MAGIC_PREFIX,mdbObjCRC(mdbObjs)); } 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 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); } printf("\n"); if(raStyle) printf("\n"); } } } int mdbObjCount(struct mdbObj *mdbObjs,boolean objs) // returns the count of vars belonging to this obj or objs; { int count = 0; struct mdbObj *mdbObj = NULL; for(mdbObj=mdbObjs;mdbObj!=NULL;mdbObj=mdbObj->next) { if(mdbObj->obj == NULL) continue; if(objs) count++; else { struct mdbVar *mdbVar = NULL; for(mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) { if(mdbVar->var != NULL && mdbVar->val != NULL) count++; } } } return count; } int mdbByVarCount(struct mdbByVar *mdbByVars,boolean vars, boolean vals) // returns the count of objs belonging to this set of vars; { int count = 0; struct mdbByVar *rootVar = NULL; for(rootVar=mdbByVars;rootVar!=NULL;rootVar=rootVar->next) { if(rootVar->var == NULL) continue; if(vars) count++; else { struct mdbLimbVal *limbVal = NULL; for(limbVal=rootVar->vals;limbVal!=NULL;limbVal=limbVal->next) { if(limbVal->val == NULL) continue; if(vals) count++; else { struct mdbLeafObj *leafObj = NULL; for(leafObj=limbVal->objs;leafObj!=NULL;leafObj=leafObj->next) { if(leafObj->obj != NULL) count++; } } } } } return count; } void mdbObjPrintUpdateLines(struct mdbObj **mdbObjs,char *dbToUpdate,char *tableToUpdate, char *varsToSelect,char *varsToSet) // prints mdbUpdate lines to allow taking vars from one db to another (sorts mdbObjs so pass pointer) { if(dbToUpdate == NULL || tableToUpdate == NULL || varsToSelect == NULL || varsToSet == NULL) errAbort("mdbObjPrintUpdateLines is missing important parameter.\n"); int selCount = 0; char **selectVars = NULL; if(differentWord(varsToSelect,"obj")) { // Sort objs to avoid duplicate mdbUpdate statements mdbObjsSortOnVars(mdbObjs, varsToSelect); // Parse list of selcting vars (could be simply expId or expId,replicate,view) selCount = chopByChar(varsToSelect,',',NULL,0); if(selCount <= 0) errAbort("mdbObjPrintUpdateLines is missing experiment defining variables.\n"); selectVars = needMem(sizeof(char *) * selCount); selCount = chopByChar(varsToSelect,',',selectVars,selCount); } // Parse list of vars to update int updCount = chopByChar(varsToSet,',',NULL,0); if(updCount <= 0) errAbort("mdbObjPrintUpdateLines is missing variables to set.\n"); char **updateVars = needMem(sizeof(char *) * updCount); updCount = chopByChar(varsToSet,',',updateVars,updCount); int ix=0; +boolean updExpId = (updCount == 1 && startsWithWordByDelimiter("expId",'=',updateVars[0])); +int startingId=0; +if(updExpId) + { + startingId = sqlSigned(skipBeyondDelimit(updateVars[0],'=')); + updateVars[0][strlen("expId")] = '\0'; + } struct mdbObj *mdbObj = NULL; struct dyString *thisSelection = newDyString(256); struct dyString *lastSelection = newDyString(256); for(mdbObj=*mdbObjs;mdbObj!=NULL;mdbObj=mdbObj->next) { if(mdbObj->obj == NULL || mdbObj->deleteThis) continue; // Build this selection string dyStringClear(thisSelection); if(sameWord(varsToSelect,"obj")) { - dyStringPrintf(thisSelection,"obj=%s",mdbObj->obj); + dyStringPrintf(thisSelection,"-obj=%s",mdbObj->obj); } else { - dyStringPrintf(thisSelection,"vars=\""); + dyStringPrintf(thisSelection,"-vars=\""); for(ix = 0;ix < selCount; ix++) { char *val = mdbObjFindValue(mdbObj,selectVars[ix]); if(val != NULL) // TODO what to do for NULLS? { if(strchr(val, ' ') != NULL) // Has blanks dyStringPrintf(thisSelection,"%s='%s' ",selectVars[ix],val);// FIXME: Need to make single quotes work since already within double quotes! else dyStringPrintf(thisSelection,"%s=%s ",selectVars[ix],val); } } dyStringPrintf(thisSelection,"\""); } // Don't bother making another mdpUpdate line if selection is the same. if(sameString(dyStringContents(lastSelection),dyStringContents(thisSelection))) continue; dyStringClear(lastSelection); dyStringAppend(lastSelection,dyStringContents(thisSelection)); printf("mdbUpdate %s table=%s %s",dbToUpdate,tableToUpdate,dyStringContents(thisSelection)); // Now look up the value of each var to update - printf(" setVars=\""); + printf(" -setVars=\""); for(ix = 0;ix < updCount; ix++) { + if(updExpId) + printf("expId=%u",startingId++);// FIXME: Need to make single quotes work since already within double quotes! + else + { char *val = mdbObjFindValue(mdbObj,updateVars[ix]); if(val != NULL) // What to do for NULLS? Ignore { printf("%s=",updateVars[ix]); if(strchr(val, ' ') != NULL) // Has blanks printf("'%s' ",val);// FIXME: Need to make single quotes work since already within double quotes! else printf("%s ",val); } } + } printf("\" -test\n"); // Always test first } dyStringFree(&thisSelection); dyStringFree(&lastSelection); } +void mdbObjPrintInsertToExperimentsTable(struct mdbObj **mdbObjs,char *expTableName, char *expDefiningVars) +// prints insert statments for the experiments taable to backfile experiments submitted before the experiments table existed +{ +if(expTableName == NULL || expDefiningVars == NULL) + errAbort("mdbObjPrintInsertToExpTbl is missing important parameter.\n"); + +int varCount = 0; +char **expVars = NULL; +if(sameWord(expDefiningVars,"obj")) + errAbort("mdbObjPrintInsertToExpTbl 'obj' is an invalid experiment defining variable.\n"); + +// Sort objs to avoid duplicate mdbUpdate statements +mdbObjsSortOnVars(mdbObjs, expDefiningVars); + +// Parse list of selcting vars (could be simply expId or expId,replicate,view) +varCount = chopByChar(expDefiningVars,',',NULL,0); +if(varCount <= 0) + errAbort("mdbObjPrintInsertToExpTbl is missing experiment defining variables.\n"); +expVars = needMem(sizeof(char *) * varCount); +varCount = chopByChar(expDefiningVars,',',expVars,varCount); +int ix=0; + +struct mdbObj *mdbObj = NULL; +struct dyString *varNames = newDyString(256); +struct dyString *varVals = newDyString(256); +struct dyString *lastVals = newDyString(256); +for(mdbObj=*mdbObjs;mdbObj!=NULL;mdbObj=mdbObj->next) + { + if(mdbObj->obj == NULL || mdbObj->deleteThis) + continue; + + // Build this selection string + dyStringClear(varNames); + dyStringClear(varVals); + + boolean first=TRUE; + // "insert into expTable (cell,antibody) values ('GM12878','CTCF'); + for(ix = 0;ix < varCount; ix++) + { + char *val = mdbObjFindValue(mdbObj,expVars[ix]); + if(val != NULL) // TODO what to do for NULLS? + { + if(first) + first=FALSE; + else + { + dyStringPrintf(varNames,","); + dyStringPrintf(varVals,","); + } + dyStringPrintf(varNames,"%s", expVars[ix]); + if(countLeadingDigits(val) == strlen(val)) + dyStringPrintf(varVals,"%s",val); + else + dyStringPrintf(varVals,"'%s'",val); + } + } + + // Don't bother making another mdpUpdate line if selection is the same. + if(sameString(dyStringContents(lastVals),dyStringContents(varVals))) + continue; + dyStringClear(lastVals); + dyStringAppend(lastVals,dyStringContents(varVals)); + + printf("INSERT INTO %s (%s) VALUES (%s);\n",expTableName,dyStringContents(varNames),dyStringContents(varVals)); + + } +dyStringFree(&varNames); +dyStringFree(&varVals); +dyStringFree(&lastVals); +} + // ----------------- Utilities ----------------- char *mdbObjFindValue(struct mdbObj *mdbObj, char *var) // Finds the val associated with the var or retruns NULL { if (mdbObj == NULL) return NULL; struct mdbVar *mdbVar = NULL; if(mdbObj->varHash != NULL) mdbVar = hashFindVal(mdbObj->varHash,var); else { for(mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) { if(sameOk(var,mdbVar->var)) break; } } if(mdbVar == NULL) return NULL; return mdbVar->val; } boolean mdbObjContains(struct mdbObj *mdbObj, char *var, char *val) // Returns TRUE if object contains var, val or both { if (mdbObj == NULL) return FALSE; if(var != NULL) { char *foundVal = mdbObjFindValue(mdbObj,var); if(foundVal == NULL) return FALSE; if(val == NULL) return TRUE; return sameOk(foundVal,val); } struct mdbVar *mdbVar = NULL; for(mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) { if(differentStringNullOk(var,mdbVar->var) != 0) continue; if(differentStringNullOk(val,mdbVar->val) != 0) continue; return TRUE; } return FALSE; } boolean mdbByVarContains(struct mdbByVar *mdbByVar, char *val, char *obj) // Returns TRUE if var contains val, obj or both { if (mdbByVar != NULL) { struct mdbLimbVal *limbVal = NULL; struct mdbLeafObj *leafObj = NULL; if(mdbByVar->valHash != NULL && val != NULL) { limbVal = hashFindVal(mdbByVar->valHash,val); if(limbVal == NULL || limbVal->val == NULL) return FALSE; if(limbVal->objHash != NULL && obj != NULL) { leafObj = hashFindVal(limbVal->objHash,obj); if(leafObj == NULL) return FALSE; return sameOk(leafObj->obj,obj); } } for(limbVal=mdbByVar->vals;limbVal!=NULL;limbVal=limbVal->next) { if(differentStringNullOk(val,limbVal->val) != 0) continue; for(leafObj=limbVal->objs;leafObj!=NULL;leafObj=leafObj->next) { if(differentStringNullOk(obj,leafObj->obj) != 0) continue; return TRUE; } } } return FALSE; } void mdbObjReorderVars(struct mdbObj *mdbObjs, char *vars,boolean back) // Reorders vars list based upon list of vars "cell antibody treatment". Send to front or back. { //char *words[48]; char *cloneLine = cloneString(vars); char **words = NULL; int count = chopByWhite(cloneLine,NULL,0); if(count) { words = needMem(sizeof(char *) * count); count = chopByWhite(cloneLine,words,count); } else { char try = ','; count = chopByChar(cloneLine,try,NULL,0); if(count <= 0) { char try = '\t'; count = chopByChar(cloneLine,try,NULL,0); } if(count) { words = needMem(sizeof(char *) * count); count = chopByChar(cloneLine,try,words,count); } } if(count == 0) errAbort("mdbObjReorderVars cannot parse vars argument.\n"); struct mdbObj *mdbObj = NULL; for( mdbObj=mdbObjs; mdbObj!=NULL; mdbObj=mdbObj->next ) { int ix; struct mdbVar *orderedVars = NULL; struct mdbVar **varsToReorder = needMem(sizeof(struct mdbVar *) * count); struct mdbVar *mdbVar = NULL; while((mdbVar = slPopHead(&(mdbObj->vars))) != NULL) { ix = stringArrayIx(mdbVar->var,words,count); if(ix < 0) slAddHead(&orderedVars,mdbVar); else varsToReorder[ix] = mdbVar; } if(back) // add to front of backward list { for( ix=0; ix<count; ix++ ) { if(varsToReorder[ix] != NULL) slAddHead(&orderedVars,varsToReorder[ix]); } } slReverse(&orderedVars); if(!back) // Add to front of forward list { for( ix=count-1; ix>=0; ix-- ) { if(varsToReorder[ix] != NULL) slAddHead(&orderedVars,varsToReorder[ix]); } } mdbObj->vars = orderedVars; freeMem(varsToReorder); } freeMem(words); } int mdbObjVarCmp(const void *va, const void *vb) /* Compare to sort on full list of vars and vals. */ { const struct mdbObj *a = *((struct mdbObj **)va); const struct mdbObj *b = *((struct mdbObj **)vb); struct mdbVar* aVar = a->vars; struct mdbVar* bVar = b->vars; for(;aVar != NULL && bVar != NULL;aVar=aVar->next,bVar=bVar->next) { int ret = strcmp(aVar->var, bVar->var); if(ret != 0) return ret; ret = strcmp(aVar->val, bVar->val); if(ret != 0) return ret; } if(aVar != NULL) return -1; if(bVar != NULL) return 1; return 0; } void mdbObjsSortOnVars(struct mdbObj **mdbObjs, char *vars) // Sorts on var,val pairs vars lists: fwd case-sensitive. Assumes all objs' vars are in identical order. // Optionally give list of vars "cell antibody treatment" to sort on (bringing to front of vars lists). { // NOTE: assumes all var pairs match (e.g. every obj has cell,treatment,antibody,... and missing treatment messes up sort) if(vars != NULL) mdbObjReorderVars(*mdbObjs,vars,FALSE); slSort(mdbObjs, mdbObjVarCmp); } void mdbObjRemoveVars(struct mdbObj *mdbObjs, char *vars) // Prunes list of vars for an object, freeing the memory. Doesn't touch DB. { char *cloneLine = NULL; int count = 0; char **words = NULL; if(vars != NULL) { cloneLine = cloneString(vars); count = chopByWhite(cloneLine,NULL,0); words = needMem(sizeof(char *) * count); count = chopByWhite(cloneLine,words,count); } struct mdbObj *mdbObj = NULL; for( mdbObj=mdbObjs; mdbObj!=NULL; mdbObj=mdbObj->next ) { int ix; struct mdbVar *keepTheseVars = NULL; if(count == 0 && mdbObj->varHash != NULL) hashFree(&mdbObj->varHash); struct mdbVar *mdbVar = NULL; while((mdbVar = slPopHead(&(mdbObj->vars))) != NULL) { if(count == 0) ix = 1; else ix = stringArrayIx(mdbVar->var,words,count); if(ix < 0) slAddHead(&keepTheseVars,mdbVar); else { if(count != 0 && mdbObj->varHash != NULL) hashRemove(mdbObj->varHash, mdbVar->var); mdbVarFree(&mdbVar); } } if(keepTheseVars != NULL) slReverse(&keepTheseVars); mdbObj->vars = keepTheseVars; } if(words != NULL) freeMem(words); } void mdbObjSwapVars(struct mdbObj *mdbObjs, char *vars,boolean deleteThis) // Replaces objs' vars with var=vap pairs provided, preparing for DB update. { 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); mdbObjAddVarPairs(mdbObj,vars); } } void mdbObjTransformToUpdate(struct mdbObj *mdbObjs, char *var, char *varType,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); if(mdbObj->obj != NULL) newObj->obj = cloneString(mdbObj->obj); 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; } // --------------- Free at last ---------------- void mdbObjsFree(struct mdbObj **mdbObjsPtr) // Frees one or more metadata objects and any contained mdbVars. Will free any hashes as well. { if(mdbObjsPtr != NULL && *mdbObjsPtr != NULL) { // free all roots struct mdbObj *mdbObj = NULL; while((mdbObj = slPopHead(mdbObjsPtr)) != NULL) { // Free hash first (shared memory) hashFree(&(mdbObj->varHash)); // free all leaves struct mdbVar *mdbVar = NULL; while((mdbVar = slPopHead(&(mdbObj->vars))) != NULL) mdbVarFree(&mdbVar); // The rest of root freeMem(mdbObj->obj); freeMem(mdbObj); } freez(mdbObjsPtr); } } void mdbByVarsFree(struct mdbByVar **mdbByVarsPtr) // Frees one or more metadata vars and any contained vals and objs. Will free any hashes as well. { if(mdbByVarsPtr != NULL && *mdbByVarsPtr != NULL) { // free all roots struct mdbByVar *rootVar = NULL; while((rootVar = slPopHead(mdbByVarsPtr)) != NULL) { // Free hash first (shared memory) hashFree(&(rootVar->valHash)); // free all limbs struct mdbLimbVal *limbVal = NULL; while((limbVal = slPopHead(&(rootVar->vals))) != NULL) mdbLimbValFree(&limbVal); // The rest of root if(rootVar->var) freeMem(rootVar->var); freeMem(rootVar); } freez(mdbByVarsPtr); } } // ----------------- CGI specific routines for use with tdb ----------------- #define MDB_NOT_FOUND ((struct mdbObj *)-666) #define METADATA_NOT_FOUND ((struct mdbObj *)-999) #define MDB_OBJ_KEY "mdbObj" static struct mdbObj *metadataForTableFromTdb(struct trackDb *tdb) // Returns the metadata for a table from a tdb setting. { char *setting = trackDbSetting(tdb, MDB_METADATA_KEY); if(setting == NULL) return NULL; struct mdbObj *mdbObj; AllocVar(mdbObj); mdbObj->obj = cloneString(tdb->table); AllocVar(mdbObj->vars); mdbObj->vars->var = cloneString(MDB_OBJ_TYPE); mdbObj->vars->val = cloneString("table"); mdbObj->varHash = hashNew(0); hashAdd(mdbObj->varHash, mdbObj->vars->var, mdbObj->vars); return mdbObjAddVarPairs(mdbObj,setting); } const struct mdbObj *metadataForTable(char *db,struct trackDb *tdb,char *table) // Returns the metadata for a table. NEVER FREE THIS STRUCT! { struct mdbObj *mdbObj = NULL; // See of the mdbObj was already built if(tdb != NULL) { mdbObj = tdbExtrasGetOrDefault(tdb, MDB_OBJ_KEY,NULL); if(mdbObj == METADATA_NOT_FOUND) // NOT in mtatbl, not in tdb metadata setting! return NULL; else if(mdbObj == MDB_NOT_FOUND) // looked mdb already and not found! return metadataForTableFromTdb(tdb); else if(mdbObj != NULL) { return mdbObj; // No reason to query the table again! } } struct sqlConnection *conn = sqlConnect(db); char *mdb = mdbTableName(conn,TRUE); // Look for sandbox name first if(tdb != NULL && tdb->table != NULL) table = tdb->table; if(mdb != NULL) mdbObj = mdbObjQueryByObj(conn,mdb,table,NULL); sqlDisconnect(&conn); // save the mdbObj for next time if(tdb) { if(mdbObj != NULL) tdbExtrasAddOrUpdate(tdb,MDB_OBJ_KEY,mdbObj); else { tdbExtrasAddOrUpdate(tdb,MDB_OBJ_KEY,MDB_NOT_FOUND); return metadataForTableFromTdb(tdb); // FIXME: metadata setting in TDB is soon to be obsolete } } // FIXME: Temporary to distinguish mdb metadata from trackDb metadata: mdbObjRemoveVars(mdbObj,"tableName"); return mdbObj; } const char *metadataFindValue(struct trackDb *tdb, char *var) // Finds the val associated with the var or retruns NULL { struct mdbObj *mdbObj = tdbExtrasGetOrDefault(tdb, MDB_OBJ_KEY,NULL); if(mdbObj == MDB_NOT_FOUND) // Note, only we if already looked for mdb (which requires db) mdbObj = metadataForTableFromTdb(tdb); if (mdbObj == NULL || mdbObj == METADATA_NOT_FOUND) return NULL; return mdbObjFindValue(mdbObj,var); }