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);
}
}
