void enumFilterOption(char *db, char *table, char *field, char *type,
char *logOp)
/* Print out a table row with filter constraint options for an enum/set. */
{
char *name = NULL;
char **valMenu = NULL;
int valMenuSize = 0;
hPrintf("<TR VALIGN=BOTTOM align='left'><TD valign=top align='left'colspan=2> %s </TD>"
"<TD valign=top>\n", field);
name = filterFieldVarName(db, table, field, filterDdVar);
cgiMakeDropListClassWithStyle(name, ddOpMenu, ddOpMenuSize,
cartUsualString(cart, name, ddOpMenu[0]),"normalText","width: 76px");
hPrintf("<TD valign=top>%s</TD><TD colspan=4 nowrap>\n", isSqlSetType(type) ? "include" : "match");
name = filterPatternVarName(db, table, field);
makeEnumValMenu(type, &valMenu, &valMenuSize);
if (logOp == NULL)
logOp = "";
if (valMenuSize-1 > 2)
{
struct slName *defaults = cartOptionalSlNameList(cart, name);
if (defaults == NULL)
defaults = slNameNew("*");
cgiMakeCheckboxGroup(name, valMenu, valMenuSize, defaults, 5);
hPrintf("</TD><TD>%s </TD></TR>\n", logOp);
}
else
{
cgiMakeDropList(name, valMenu, valMenuSize,cartUsualString(cart, name, valMenu[0]));
hPrintf(" %s </TD></TR>\n", logOp);
}
}
void describeFields(char *db, char *table,
struct asObject *asObj, struct sqlConnection *conn)
/* Print out an HTML table showing table fields and types, and optionally
* offering histograms for the text/enum fields. */
{
struct sqlResult *sr;
char **row;
#define TOO_BIG_FOR_HISTO 500000
boolean tooBig = (sqlTableSize(conn, table) > TOO_BIG_FOR_HISTO);
char query[256];
struct slName *exampleList, *example;
boolean showItemRgb = FALSE;
showItemRgb=bedItemRgb(findTdbForTable(db, curTrack, table, ctLookupName));
// should we expect itemRgb instead of "reserved"
sqlSafef(query, sizeof(query), "select * from %s limit 1", table);
exampleList = storeRow(conn, query);
sqlSafef(query, sizeof(query), "describe %s", table);
sr = sqlGetResult(conn, query);
hTableStart();
hPrintf("<TR><TH>field</TH>");
if (exampleList != NULL)
hPrintf("<TH>example</TH>");
hPrintf("<TH>SQL type</TH> ");
if (!tooBig)
hPrintf("<TH>info</TH> ");
if (asObj != NULL)
hPrintf("<TH>description</TH> ");
puts("</TR>\n");
example = exampleList;
while ((row = sqlNextRow(sr)) != NULL)
{
if (showItemRgb && (sameWord(row[0],"reserved")))
hPrintf("<TR><TD><TT>itemRgb</TT></TD> ");
else
hPrintf("<TR><TD><TT>%s</TT></TD> ", row[0]);
if (exampleList != NULL)
{
hPrintf("<TD>");
if (example != NULL)
hPrintf("%s", cleanExample(example->name));
else
hPrintf("n/a");
hPrintf("</TD>");
}
// enums/sets with many items can make for painfully wide rows in the table --
// add spaces between quoted list values:
if (stringIn("','", row[1]))
{
struct dyString *spaced = dyStringSub(row[1], "','", "', '");
hPrintf("<TD><TT>%s</TT></TD>", spaced->string);
}
else
hPrintf("<TD><TT>%s</TT></TD>", row[1]);
if (!tooBig)
{
hPrintf(" <TD>");
if ((isSqlStringType(row[1]) && !sameString(row[1], "longblob")) ||
isSqlEnumType(row[1]) || isSqlSetType(row[1]))
{
hPrintf("<A HREF=\"%s", getScriptName());
hPrintf("?%s", cartSidUrlString(cart));
hPrintf("&%s=%s", hgtaDatabase, db);
hPrintf("&%s=%s", hgtaHistoTable, table);
hPrintf("&%s=%s", hgtaDoValueHistogram, row[0]);
hPrintf("\">");
hPrintf("values");
hPrintf("</A>");
}
else if (isSqlNumType(row[1]))
{
hPrintf("<A HREF=\"%s", getScriptName());
hPrintf("?%s", cartSidUrlString(cart));
hPrintf("&%s=%s", hgtaDatabase, db);
hPrintf("&%s=%s", hgtaHistoTable, table);
hPrintf("&%s=%s", hgtaDoValueRange, row[0]);
hPrintf("\">");
hPrintf("range");
hPrintf("</A>");
}
else
{
hPrintf(" ");
}
hPrintf("</TD>");
}
if (asObj != NULL)
{
struct asColumn *asCol = asColumnFind(asObj, row[0]);
hPrintf(" <TD>");
if (asCol != NULL)
hPrintf("%s", asCol->comment);
else
{
if (sameString("bin", row[0]))
hPrintf("Indexing field to speed chromosome range queries.");
else
hPrintf(" ");
}
hPrintf("</TD>");
}
puts("</TR>");
if (example != NULL)
example = example->next;
}
hTableEnd();
sqlFreeResult(&sr);
}
static void printSqlFieldListAsControlTable(struct sqlFieldType *ftList, char *db,
char *rootTable, struct trackDb *tdb, boolean isBedGr)
/* Print out table of controls for fields. */
{
int fieldNum = 0;
int bedGraphColumn = 5; /* default score column */
int noBinBedGraphColumn = bedGraphColumn;
boolean gotFirst = FALSE;
struct sqlFieldType *ft;
hPrintf("<TABLE BORDER=0>\n");
for (ft = ftList; ft != NULL; ft = ft->next)
{
char *field = ft->name;
char *type = ft->type;
char *logic = "";
if ((0 == fieldNum) && (!sameWord(field,"bin")))
noBinBedGraphColumn -= 1;
if (!sameWord(type, "longblob"))
{
if (!gotFirst)
gotFirst = TRUE;
else if (!isBedGr)
logic = " AND ";
}
if (!isBedGr || (noBinBedGraphColumn == fieldNum))
{
if (isSqlEnumType(type) || isSqlSetType(type))
{
enumFilterOption(db, rootTable, field, type, logic);
}
else if(isSqlIntType(type))
{
integerFilter(db, rootTable, field, field, logic);
}
else if(isSqlNumType(type))
{
if(isBedGr)
{
double min, max;
double tDbMin, tDbMax;
wigFetchMinMaxLimits(tdb, &min, &max, &tDbMin, &tDbMax);
if (tDbMin < min)
min = tDbMin;
if (tDbMax > max)
max = tDbMax;
numericFilterWithLimits(db, rootTable, field, field, min, max, logic);
hPrintf("<TR><TD COLSPAN=3 ALIGN=RIGHT> (%s range: [%g:%g]) "
"</TD></TR>\n", field, min, max);
}
else
{
numericFilter(db, rootTable, field, field, logic);
}
}
else //if (isSqlStringType(type))
{
stringFilterOption(db, rootTable, field, logic);
}
}
++fieldNum;
}
hPrintf("</TABLE>\n");
}
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);
}
}
