8b6cd76349b34c42eb04ef082f72926337b3501a tdreszer Thu Jan 27 17:17:02 2011 -0800 Made mdbUpdate -recreate faster by doing minimal error checking and loading with all insert statements. diff --git src/hg/lib/mdb.c src/hg/lib/mdb.c index 71e52d6..6baeb4f 100644 --- src/hg/lib/mdb.c +++ src/hg/lib/mdb.c @@ -1076,30 +1076,88 @@ } // 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; } +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); + +for(mdbObj = mdbObjs;mdbObj != NULL; mdbObj = mdbObj->next) + { + if (mdbObj->deleteThis) + continue; + + for(mdbVar = mdbObj->vars;mdbVar != NULL; mdbVar = mdbVar->next) + { + stripEnclosingDoubleQuotes(mdbVar->val); // Ensures values are stripped of enclosing quotes + + // 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); @@ -1653,42 +1711,50 @@ if(mdbObjContains(mdbObj, var, NULL)) return TRUE; } return FALSE; } struct mdbObj *mdbObjsCommonVars(struct mdbObj *mdbObjs) // Returns a new mdbObj with all vars that are contained in every obj passed in. // Note that the returnd mdbObj has a meaningles obj name and vals. { if (mdbObjs == NULL || mdbObjs->vars == NULL) return NULL; struct mdbObj *mdbObj = mdbObjs; struct mdbObj *commonVars = mdbObjClone(mdbObj); // Clone the first obj then prune it commonVars->next = NULL; -struct mdbVar *mdbVar = mdbObj->vars; // Will walk through the first obj's vars mdbObj=mdbObj->next; // No need to include first obj in search if (mdbObj != NULL) { + int count = 1; + // NOTE: This should not loop through all, as the list could be huge. Just compare the first 10 for now struct dyString *dyPruneVars = dyStringNew(512); + for(;mdbObj != NULL && count < 10;mdbObj=mdbObj->next, count++) + { + struct mdbVar *mdbVar = commonVars->vars; // Will walk through the first obj's vars for(; mdbVar != NULL; mdbVar = mdbVar->next ) { if (mdbObjsContainAtleastOne(mdbObj, mdbVar->var) == FALSE) dyStringPrintf(dyPruneVars,"%s ",mdbVar->var); // var not found so add to prune list } if (dyStringLen(dyPruneVars) > 0) + { mdbObjRemoveVars(commonVars,dyStringContents(dyPruneVars)); + dyStringClear(dyPruneVars); + } + } dyStringFree(&dyPruneVars); } return commonVars; } 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); @@ -2502,77 +2568,71 @@ struct hashCookie hc = hashFirst(termTypeHash); struct hashEl *hEl; while ((hEl = hashNext(&hc)) != NULL) { char *setting = NULL; struct hash *typeHash = (struct hash *)hEl->val; //if (!includeHidden) { setting = hashFindVal(typeHash,"hidden"); if(SETTING_IS_ON(setting)) continue; } if (searchTracks) { setting = hashFindVal(typeHash,"searchable"); -#ifdef CV_SEARCH_SUPPORTS_FREETEXT if (setting == NULL || (differentWord(setting,"select") && differentWord(setting,"freeText"))) -#else///ifndef CV_SEARCH_SUPPORTS_FREETEXT - if (setting == NULL || differentWord(setting,"select")) // TODO: Currently only 'select's are supported -#endif///ndef CV_SEARCH_SUPPORTS_FREETEXT continue; } if (cvDefined) { setting = hashFindVal(typeHash,"cvDefined"); if(SETTING_NOT_ON(setting)) continue; } char *term = hEl->name; char *label = hashFindVal(typeHash,"label"); if (label == NULL) label = term; slPairAdd(&whitePairs, term, cloneString(label)); // Term gets cloned in slPairAdd } if (whitePairs != NULL) slPairValSortCase(&whitePairs); return whitePairs; } -#ifdef CV_SEARCH_SUPPORTS_FREETEXT enum mdbCvSearchable mdbCvSearchMethod(char *term) // returns whether the term is searchable // TODO: replace with mdbCvWhiteList() returning struct { // Get the list of term types from thew cv struct hash *termTypeHash = mdbCvTermTypeHash(); struct hash *termHash = hashFindVal(termTypeHash,term); if (termHash != NULL) { char *searchable = hashFindVal(termHash,"searchable"); if (searchable != NULL) { if (sameWord(searchable,"select")) return cvsSearchBySingleSelect; if (sameWord(searchable,"freeText")) return cvsSearchByFreeText; } } return cvsNotSearchable; } -#endif///ndef CV_SEARCH_SUPPORTS_FREETEXT const char *cvLabel(char *term) // returns cv label if term found or else just term { // Get the list of term types from thew cv struct hash *termTypeHash = mdbCvTermTypeHash(); struct hash *termHash = hashFindVal(termTypeHash,term); if (termHash != NULL) { char *label = hashFindVal(termHash,"label"); if (label != NULL) return label; } return term; }