static struct visiMatch *matchGeneName(struct sqlConnection *conn, char *symbol, struct hash *privateHash) /* Return matching list if it's a gene symbol. */ { struct visiMatch *matchList = NULL, *match; struct slInt *imageList, *image; char *sqlPat = sqlLikeFromWild(symbol); if (sqlWildcardIn(sqlPat)) imageList = visiGeneSelectNamed(conn, sqlPat, vgsLike); else imageList = visiGeneSelectNamed(conn, sqlPat, vgsExact); for (image = imageList; image != NULL; image = image->next) { char asciiId[16]; safef(asciiId, sizeof(asciiId), "%d", image->val); if (!isPrivate(conn, privateHash, asciiId)) { AllocVar(match); match->imageId = image->val; slAddHead(&matchList, match); } } slReverse(&matchList); return matchList; }
char *escapeAltFixTerm(char *term) /* Special tweaks for SQL search of alt/fix terms that may include '.' and '_' characters. */ { // If there is a ".", make it into a single-character wildcard so that "GL383518.1" // can match "chr1_GL383518v1_alt". char termCpy[strlen(term)+1]; safecpy(termCpy, sizeof termCpy, term); subChar(termCpy, '.', '?'); // Escape '_' because that is an important character in alt/fix sequence names, and support // wildcards: return sqlLikeFromWild(termCpy); }
static void visiGeneMatchGene(struct visiSearcher *searcher, struct sqlConnection *conn, struct slName *wordList) /* Add images matching genes in wordList to searcher. * The wordList can include wildcards. */ { struct slName *word; int wordIx; for (word = wordList, wordIx=0; word != NULL; word = word->next, ++wordIx) { char *sqlPat = sqlLikeFromWild(word->name); struct slInt *imageList; if (sqlWildcardIn(sqlPat)) imageList = visiGeneSelectNamed(conn, sqlPat, vgsLike); else imageList = visiGeneSelectNamed(conn, sqlPat, vgsExact); addImageListAndFree(searcher, imageList, wordIx, 1); freez(&sqlPat); } }
static struct genePos *pfamAdvFilter(struct column *col, struct sqlConnection *defaultConn, struct genePos *list) /* Do advanced filter on for pfam. */ { char *terms = advFilterVal(col, "terms"); if (terms != NULL) { struct sqlConnection *conn = sqlConnect(col->protDb); char query[256]; struct sqlResult *sr; struct dyString *dy = newDyString(1024); char **row; boolean orLogic = advFilterOrLogic(col, "logic", TRUE); struct slName *term, *termList = stringToSlNames(terms); struct hash *passHash = newHash(17); struct hash *prevHash = NULL; struct genePos *gp; /* Build up hash of all genes. */ struct hash *geneHash = newHash(18); for (gp = list; gp != NULL; gp = gp->next) hashAdd(geneHash, gp->name, gp); for (term = termList; term != NULL; term = term->next) { /* Build up a list of IDs of descriptions that match term. */ struct slName *idList = NULL, *id; if (isPfamId(term->name)) { idList = slNameNew(term->name); } else { char *sqlWild = sqlLikeFromWild(term->name); sqlSafef(query, sizeof(query), "select pfamAC from pfamDesc where description like '%s'", sqlWild); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { id = slNameNew(row[0]); slAddHead(&idList, id); } sqlFreeResult(&sr); } if (idList != NULL) { /* Build up query that includes all IDs. */ dyStringClear(dy); sqlDyStringPrintf(dy, "select name from %s where ", col->table); sqlDyStringPrintf(dy, "value='%s'", idList->name); for (id = idList->next; id != NULL; id = id->next) sqlDyStringPrintf(dy, "or value='%s'", id->name); /* Execute query and put matchers into hash. */ sr = sqlGetResult(defaultConn, dy->string); while ((row = sqlNextRow(sr)) != NULL) { gp = hashFindVal(geneHash, row[0]); if (gp != NULL) { char *name = gp->name; if (prevHash == NULL || hashLookup(prevHash, name) != NULL) hashStore(passHash, name); } } sqlFreeResult(&sr); slFreeList(&idList); } if (!orLogic) { hashFree(&prevHash); if (term->next != NULL) { prevHash = passHash; passHash = newHash(17); } } } list = weedUnlessInHash(list, passHash); hashFree(&prevHash); hashFree(&passHash); dyStringFree(&dy); sqlDisconnect(&conn); } return list; }
void webTableBuildQuery(struct cart *cart, char *from, char *initialWhere, char *varPrefix, char *fields, boolean withFilters, struct dyString **retQuery, struct dyString **retWhere) /* Construct select, from and where clauses in query, keeping an additional copy of where * Returns the SQL query and the SQL where expression as two dyStrings (need to be freed) */ { struct dyString *query = dyStringNew(0); struct dyString *where = dyStringNew(0); struct slName *field, *fieldList = commaSepToSlNames(fields); boolean gotWhere = FALSE; sqlDyStringPrintf(query, "%s", ""); // TODO check with Galt on how to get reasonable checking back. dyStringPrintf(query, "select %s from %s", fields, from); if (!isEmpty(initialWhere)) { dyStringPrintf(where, " where "); sqlSanityCheckWhere(initialWhere, where); gotWhere = TRUE; } /* If we're doing filters, have to loop through the row of filter controls */ if (withFilters) { for (field = fieldList; field != NULL; field = field->next) { char varName[128]; safef(varName, sizeof(varName), "%s_f_%s", varPrefix, field->name); char *val = trimSpaces(cartUsualString(cart, varName, "")); if (!isEmpty(val)) { if (gotWhere) dyStringPrintf(where, " and "); else { dyStringPrintf(where, " where "); gotWhere = TRUE; } if (anyWild(val)) { char *converted = sqlLikeFromWild(val); char *escaped = makeEscapedString(converted, '"'); dyStringPrintf(where, "%s like \"%s\"", field->name, escaped); freez(&escaped); freez(&converted); } else if (val[0] == '>' || val[0] == '<') { char *remaining = val+1; if (remaining[0] == '=') remaining += 1; remaining = skipLeadingSpaces(remaining); if (isNumericString(remaining)) dyStringPrintf(where, "%s %s", field->name, val); else { warn("Filter for %s doesn't parse: %s", field->name, val); dyStringPrintf(where, "%s is not null", field->name); // Let query continue } } else { char *escaped = makeEscapedString(val, '"'); dyStringPrintf(where, "%s = \"%s\"", field->name, escaped); freez(&escaped); } } } } dyStringAppend(query, where->string); /* We do order here so as to keep order when working with tables bigger than a page. */ char orderVar[256]; safef(orderVar, sizeof(orderVar), "%s_order", varPrefix); char *orderFields = cartUsualString(cart, orderVar, ""); if (!isEmpty(orderFields)) { if (orderFields[0] == '-') dyStringPrintf(query, " order by %s desc", orderFields+1); else dyStringPrintf(query, " order by %s", orderFields); } // return query and where expression *retQuery = query; *retWhere = where; }
char *filterClause(char *db, char *table, char *chrom, char *extraClause) /* Get filter clause (something to put after 'where') * for table */ { struct sqlConnection *conn = NULL; char varPrefix[128]; int varPrefixSize, fieldNameSize; struct hashEl *varList, *var; struct dyString *dy = NULL; boolean needAnd = FALSE; char oldDb[128]; char dbTableBuf[256]; char explicitDb[128]; char splitTable[256]; char explicitDbTable[512]; /* Return just extraClause (which may be NULL) if no filter on us. */ if (! (anyFilter() && filteredOrLinked(db, table))) return cloneString(extraClause); safef(oldDb, sizeof(oldDb), "%s", db); dbOverrideFromTable(dbTableBuf, &db, &table); if (!sameString(oldDb, db)) safef(explicitDb, sizeof(explicitDb), "%s.", db); else explicitDb[0] = 0; /* Cope with split table and/or custom tracks. */ if (isCustomTrack(table)) { conn = hAllocConn(CUSTOM_TRASH); struct customTrack *ct = ctLookupName(table); safef(explicitDbTable, sizeof(explicitDbTable), "%s", ct->dbTableName); } else { conn = hAllocConn(db); safef(splitTable, sizeof(splitTable), "%s_%s", chrom, table); if (!sqlTableExists(conn, splitTable)) safef(splitTable, sizeof(splitTable), "%s", table); safef(explicitDbTable, sizeof(explicitDbTable), "%s%s", explicitDb, splitTable); } /* Get list of filter variables for this table. */ safef(varPrefix, sizeof(varPrefix), "%s%s.%s.", hgtaFilterVarPrefix, db, table); varPrefixSize = strlen(varPrefix); varList = cartFindPrefix(cart, varPrefix); if (varList == NULL) { hFreeConn(&conn); return cloneString(extraClause); } /* Create filter clause string, stepping through vars. */ dy = dyStringNew(0); for (var = varList; var != NULL; var = var->next) { /* Parse variable name into field and type. */ char field[64], *s, *type; s = var->name + varPrefixSize; type = strchr(s, '.'); if (type == NULL) internalErr(); fieldNameSize = type - s; if (fieldNameSize >= sizeof(field)) internalErr(); memcpy(field, s, fieldNameSize); field[fieldNameSize] = 0; sqlCkId(field); type += 1; /* rawLogic and rawQuery are handled below; * filterMaxOutputVar is not really a filter variable and is handled * in wiggle.c. */ if (startsWith("raw", type) || sameString(filterMaxOutputVar, type)) continue; /* Any other variables that are missing a name: * <varPrefix>..<type> * are illegal */ if (fieldNameSize < 1) { warn("Missing name in cart variable: %s\n", var->name); continue; } if (sameString(type, filterDdVar)) { char *patVar = filterPatternVarName(db, table, field); struct slName *patList = cartOptionalSlNameList(cart, patVar); normalizePatList(&patList); if (slCount(patList) > 0) { char *ddVal = cartString(cart, var->name); boolean neg = sameString(ddVal, ddOpMenu[1]); char *fieldType = getSqlType(conn, explicitDbTable, field); boolean needOr = FALSE; if (needAnd) dyStringAppend(dy, " and "); needAnd = TRUE; if (neg) dyStringAppend(dy, "not "); boolean composite = (slCount(patList) > 1); if (composite || neg) dyStringAppendC(dy, '('); struct slName *pat; for (pat = patList; pat != NULL; pat = pat->next) { char *sqlPat = sqlLikeFromWild(pat->name); if (needOr) dyStringAppend(dy, " OR "); needOr = TRUE; if (isSqlSetType(fieldType)) { sqlDyStringPrintfFrag(dy, "FIND_IN_SET('%s', %s.%s)>0 ", sqlPat, explicitDbTable , field); } else { sqlDyStringPrintfFrag(dy, "%s.%s ", explicitDbTable, field); if (sqlWildcardIn(sqlPat)) dyStringAppend(dy, "like "); else dyStringAppend(dy, "= "); sqlDyStringPrintf(dy, "'%s'", sqlPat); } freez(&sqlPat); } if (composite || neg) dyStringAppendC(dy, ')'); } } else if (sameString(type, filterCmpVar)) { char *patVar = filterPatternVarName(db, table, field); char *pat = trimSpaces(cartOptionalString(cart, patVar)); char *cmpVal = cartString(cart, var->name); if (cmpReal(pat, cmpVal)) { if (needAnd) dyStringAppend(dy, " and "); needAnd = TRUE; if (sameString(cmpVal, "in range")) { char *words[2]; int wordCount; char *dupe = cloneString(pat); wordCount = chopString(dupe, ", \t\n", words, ArraySize(words)); if (wordCount < 2) /* Fake short input */ words[1] = "2000000000"; if (strchr(pat, '.')) /* Assume floating point */ { double a = atof(words[0]), b = atof(words[1]); sqlDyStringPrintfFrag(dy, "%s.%s >= %f && %s.%s <= %f", explicitDbTable, field, a, explicitDbTable, field, b); } else { int a = atoi(words[0]), b = atoi(words[1]); sqlDyStringPrintfFrag(dy, "%s.%s >= %d && %s.%s <= %d", explicitDbTable, field, a, explicitDbTable, field, b); } freez(&dupe); } else { // cmpVal has been checked already above in cmpReal for legal values. sqlDyStringPrintfFrag(dy, "%s.%s %-s ", explicitDbTable, field, cmpVal); if (strchr(pat, '.')) /* Assume floating point. */ dyStringPrintf(dy, "%f", atof(pat)); else dyStringPrintf(dy, "%d", atoi(pat)); } } } } /* Handle rawQuery if any */ { char *varName; char *logic, *query; varName = filterFieldVarName(db, table, "", filterRawLogicVar); logic = cartUsualString(cart, varName, logOpMenu[0]); varName = filterFieldVarName(db, table, "", filterRawQueryVar); query = trimSpaces(cartOptionalString(cart, varName)); if (query != NULL && query[0] != 0) { if (needAnd) dyStringPrintf(dy, " %s ", logic); sqlSanityCheckWhere(query, dy); } } /* Clean up and return */ hFreeConn(&conn); hashElFreeList(&varList); if (dy->stringSize == 0) { dyStringFree(&dy); return cloneString(extraClause); } else { if (isNotEmpty(extraClause)) dyStringPrintf(dy, " and %s", extraClause); return dyStringCannibalize(&dy); } }