void rowsToCols(char *in, char *out)
/* rowsToCols - Convert rows to columns and vice versa in a text file.. */
{
struct slName *lineList = readAllLines(in);
struct tabRow *row, *rowList;
FILE *f;
int i, origCols;
char *s;
if (fs != NULL)
rowList = tabRowByChar(lineList, fs[0], in, varCol);
else if (fixed)
rowList = tabRowByFixedGuess(lineList, in);
else if (offsets)
{
struct slName *nameList = commaSepToSlNames(offsets), *name;
struct slInt *offList=NULL, *off;
for (name = nameList; name != NULL; name = name->next)
{
off = slIntNew(atoi(name->name));
slAddTail(&offList, off);
}
rowList = tabRowByFixedOffsets(lineList, offList, in);
}
else
rowList = tabRowByWhite(lineList, in, varCol);
origCols = tabRowMaxColCount(rowList);
f = mustOpen(out, "w");
for (i=0; i<origCols; ++i)
{
for (row = rowList; row != NULL; row = row->next)
{
if (i < row->colCount)
s = row->columns[i];
else
s = "";
fprintf(f, "%s", s);
if (row->next == NULL)
fprintf(f, "\n");
else
fprintf(f, "\t");
}
}
carefulClose(&f);
}
struct hash *makeNameHash(char *fileName)
/* Parse file in format:
* name probeId,probeId,
* into hash keyed by probeId with name values. */
{
struct hash *hash = newHash(0);
struct lineFile *lf = lineFileOpen(fileName, TRUE);
char *row[2];
while (lineFileRow(lf, row))
{
struct slName *probe, *probeList = commaSepToSlNames(row[1]);
for (probe = probeList; probe != NULL; probe = probe->next)
hashAdd(hash, probe->name, cloneString(row[0]));
slFreeList(&probeList);
}
lineFileClose(&lf);
return hash;
}
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;
}
