Ejemplo n.º 1
0
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;
}
Ejemplo n.º 2
0
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);
    }
}