static void queryInputTrackTable(struct dyString *query, char *inputTrackTable,
struct slName *fieldList)
/* Construct query in dyString to return contents of inputTrackTable ordered appropriately */
{
struct dyString *fields = dyStringNew(0);
struct slName *field;
sqlDyStringPrintf(query, "select tableName ");
for (field = fieldList; field != NULL; field = field->next)
sqlDyStringPrintfFrag(fields, ",%s", field->name);
sqlDyStringPrintf(query, "%-s from %s", fields->string, inputTrackTable);
if (fieldList != NULL)
// skip leading comma
dyStringPrintf(query, " order by %s", fields->string+1);
dyStringFree(&fields);
}
static char *slNameToInExpression(char *field, struct slName *allTerms)
/* Given an slName list, return a SQL "field IN ('term1', 'term2', ...)" expression
* to be used in a WHERE clause. */
{
struct dyString *dy = dyStringNew(0);
sqlDyStringPrintfFrag(dy, "%s in (", field);
boolean first = TRUE;
struct slName *term;
for (term = allTerms; term != NULL; term = term->next)
{
if (first)
first = FALSE;
else
dyStringAppend(dy, ", ");
sqlDyStringPrintf(dy, "'%s'", term->name);
}
dyStringAppend(dy, ")");
return dyStringCannibalize(&dy);
}
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);
}
}
static void pubsLoadKeywordYearItems(struct track *tg)
/* load items that fulfill keyword and year filter */
{
pubsParseClassColors();
struct sqlConnection *conn = hAllocConn(database);
char *keywords = cartOptionalStringClosestToHome(cart, tg->tdb, FALSE, "pubsFilterKeywords");
char *yearFilter = cartOptionalStringClosestToHome(cart, tg->tdb, FALSE, "pubsFilterYear");
char *publFilter = cartOptionalStringClosestToHome(cart, tg->tdb, FALSE, "pubsFilterPublisher");
char *articleTable = pubsArticleTable(tg);
if(sameOk(yearFilter, "anytime"))
yearFilter = NULL;
if(sameOk(publFilter, "all"))
publFilter = NULL;
if(isNotEmpty(keywords))
keywords = makeMysqlMatchStr(keywords);
if (isEmpty(yearFilter) && isEmpty(keywords) && isEmpty(publFilter))
{
loadGappedBed(tg);
}
else
{
// put together an "extra" query to hExtendedRangeQuery that removes articles
// without the keywords specified in hgTrackUi
char *oldLabel = tg->longLabel;
tg->longLabel = catTwoStrings(oldLabel, " (filter activated)");
freeMem(oldLabel);
char **row;
struct linkedFeatures *lfList = NULL;
struct trackDb *tdb = tg->tdb;
int scoreMin = atoi(trackDbSettingClosestToHomeOrDefault(tdb, "scoreMin", "0"));
int scoreMax = atoi(trackDbSettingClosestToHomeOrDefault(tdb, "scoreMax", "1000"));
boolean useItemRgb = bedItemRgb(tdb);
char *extra = NULL;
struct dyString *extraDy = dyStringNew(0);
struct hash *articleIds = searchForKeywords(conn, articleTable, keywords);
if (!sameWord(tg->table, "pubsBlat"))
// new table schema: filter fields are on main bed table
{
if (isNotEmpty(yearFilter))
sqlDyStringPrintfWithSep(extraDy, " AND ", " year >= '%s'", yearFilter);
if (isNotEmpty(publFilter))
sqlDyStringPrintfWithSep(extraDy, " AND ", " publisher = '%s'", publFilter);
}
else
// old table schema, filter by doing a join on article table
{
if(isNotEmpty(yearFilter))
sqlDyStringPrintfFrag(extraDy, "name IN (SELECT articleId FROM %s WHERE year>='%s')", articleTable, \
yearFilter);
}
if (extraDy->stringSize > 0)
extra = extraDy->string;
else
extra = NULL;
int rowOffset = 0;
struct sqlResult *sr = hExtendedRangeQuery(conn, tg->table, chromName, winStart, winEnd, extra,
FALSE, NULL, &rowOffset);
freeDyString(&extraDy);
while ((row = sqlNextRow(sr)) != NULL)
{
struct bed *bed = bedLoad12(row+rowOffset);
if (articleIds==NULL || hashFindVal(articleIds, bed->name))
slAddHead(&lfList, bedMungToLinkedFeatures(&bed, tdb, 12, scoreMin, scoreMax, useItemRgb));
}
sqlFreeResult(&sr);
slReverse(&lfList);
slSort(&lfList, linkedFeaturesCmp);
tg->items = lfList;
}
hFreeConn(&conn);
}
