void lfRetroGene(struct track *tg)
/* Load the items in one custom track - just move beds in
* window... */
{
struct linkedFeatures *lfList = NULL, *lf;
struct ucscRetroInfo *pg = NULL, *list = NULL;
struct sqlConnection *conn = hAllocConn(database);
struct sqlResult *sr;
char **row;
int rowOffset;
int colCount ;
sr = hRangeQuery(conn, tg->table, chromName, winStart, winEnd, NULL, &rowOffset);
colCount = sqlCountColumns(sr);
while ((row = sqlNextRow(sr)) != NULL)
{
//if (colCount == 56)
// pg = retroMrnaInfo56Load(row+rowOffset);
//else
pg = ucscRetroInfoLoad(row+rowOffset);
slAddHead(&list, pg);
}
sqlFreeResult(&sr);
hFreeConn(&conn);
for (pg = list; pg != NULL; pg = pg->next)
{
lf = lfFromRetroGene(pg);
lf->grayIx = 9;
slReverse(&lf->components);
slAddHead(&lfList,lf);
}
tg->items = lfList;
}
void dbFindFieldsWith(char *database, char *regExp, char *output)
/* dbFindFieldsWith - Look through database and find fields that have elements matching a certain pattern in the first N rows.. */
{
regex_t re;
int err = regcomp(&re, regExp, REG_NOSUB|REG_EXTENDED);
if (err < 0)
errAbort("regcomp failed code %d", err);
struct sqlConnection *conn = sqlConnect(database);
struct slName *table, *tableList = sqlQuickList(conn, "NOSQLINJ show tables");
FILE *f = mustOpen(output, "w");
for (table = tableList; table != NULL; table = table->next)
{
char query[256];
sqlSafef(query, sizeof(query), "select * from %s limit %d", table->name, maxRows);
verbose(2, "%s.%s\n", database, table->name);
struct sqlResult *sr = sqlGetResult(conn, query);
if (sr != NULL)
{
int colCount = sqlCountColumns(sr);
/* Get labels for columns */
char **labels;
AllocArray(labels, colCount);
int i;
for (i=0; i<colCount; ++i)
labels[i] = sqlFieldName(sr);
/* Get flags that say which fields we've reported. */
bool *flags;
AllocArray(flags, colCount);
char **row;
while ((row = sqlNextRow(sr)) != NULL)
{
int i;
for (i=0; i<colCount; ++i)
{
char *field = row[i];
if (field != NULL && field[0] != 0)
{
if (regexec(&re, row[i], 0, NULL, 0) == 0)
{
if (!flags[i])
{
flags[i] = TRUE;
fprintf(f, "%s\t%s\t%s\n", table->name, labels[i], row[i]);
}
}
}
}
}
sqlFreeResult(&sr);
freez(&flags);
freez(&labels);
}
}
carefulClose(&f);
}
static struct slName *storeRow(struct sqlConnection *conn, char *query)
/* Just save the results of a single row query in a string list. */
{
struct sqlResult *sr = sqlGetResult(conn, query);
char **row;
struct slName *list = NULL, *el;
int i, colCount = sqlCountColumns(sr);
if ((row = sqlNextRow(sr)) != NULL)
{
for (i=0; i<colCount; ++i)
{
el = slNameNew(row[i]);
slAddTail(&list, el);
}
}
sqlFreeResult(&sr);
return list;
}
static void selectFromTable(char *table, struct hTableInfo *tblInfo,
struct sqlConnection *conn, FILE *outFh)
/* select from a table and output rows */
{
struct dyString *query = dyStringNew(0);
dyStringPrintf(query, "SELECT %s.* FROM %s", table, table);
if (joinTbls != NULL)
dyStringPrintf(query, ",%s", joinTbls);
addWhereClause(tblInfo, query);
verbose(2, "query: %s\n", query->string);
struct sqlResult *sr = sqlGetResult(conn, query->string);
int numCols = sqlCountColumns(sr);
char **row;
while ((row = sqlNextRow(sr)) != NULL)
outputRow(outFh, tblInfo, numCols, row);
sqlFreeResult(&sr);
dyStringFree(&query);
}
struct psl *pslLoadByQuery(struct sqlConnection *conn, char *query)
/* Load all psl from table that satisfy the query given.
* Where query is of the form 'select * from example where something=something'
* or 'select example.* from example, anotherTable where example.something =
* anotherTable.something'.
* Dispose of this with pslFreeList(). */
{
struct psl *list = NULL, *el;
struct sqlResult *sr;
char **row;
int offSet = 0;
sr = sqlGetResult(conn, query);
offSet = sqlCountColumns(sr) - 21;
while ((row = sqlNextRow(sr)) != NULL)
{
el = pslLoad(row+offSet);
slAddHead(&list, el);
}
slReverse(&list);
sqlFreeResult(&sr);
return list;
}
/* bedGraphLoadItems - an ordinary bed load, but we are interested
* in only the chrom, start, end, and the graphColumn
*/
static void bedGraphLoadItems(struct track *tg)
{
struct sqlConnection *conn;
struct sqlResult *sr = (struct sqlResult *) NULL;
char **row = (char **)NULL;
int rowOffset = 0;
struct bedGraphItem *bgList = NULL;
int itemsLoaded = 0;
int colCount = 0;
struct wigCartOptions *wigCart = (struct wigCartOptions *) tg->wigCartData;
int graphColumn = 5;
char *tableName;
if(sameString(tg->table, "affyTranscription"))
wigCart->colorTrack = "affyTransfrags";
graphColumn = wigCart->graphColumn;
#ifndef GBROWSE
if (isCustomTrack(tg->table) && tg->customPt)
{
struct customTrack *ct = (struct customTrack *) tg->customPt;
tableName = ct->dbTableName;
conn = hAllocConn(CUSTOM_TRASH);
}
else
#endif /* GBROWSE */
{
tableName = tg->table;
conn = hAllocConnTrack(database, tg->tdb);
}
sr = hRangeQuery(conn, tableName, chromName, winStart, winEnd, NULL,
&rowOffset);
colCount = sqlCountColumns(sr) - rowOffset;
/* Must have at least four good columns */
if (colCount < 4)
errAbort("bedGraphLoadItems: table %s only has %d data columns, must be at least 4", tableName, colCount);
if (colCount < graphColumn)
errAbort("bedGraphLoadItems: table %s only has %d data columns, specified graph column %d does not exist",
tableName, colCount, graphColumn);
/* before loop, determine actual row[graphColumn] index */
graphColumn += (rowOffset - 1);
while ((row = sqlNextRow(sr)) != NULL)
{
struct bedGraphItem *bg;
struct bed *bed;
++itemsLoaded;
/* load chrom, start, end */
bed = bedLoadN(row+rowOffset, 3);
AllocVar(bg);
bg->start = bed->chromStart;
bg->end = bed->chromEnd;
if ((colCount > 4) && ((graphColumn + rowOffset) != 4))
bg->name = cloneString(row[3+rowOffset]);
else
{
char name[128];
safef(name,ArraySize(name),"%s.%d", bed->chrom, itemsLoaded);
bg->name = cloneString(name);
}
bg->dataValue = sqlFloat(row[graphColumn]);
/* filled in by DrawItems */
bg->graphUpperLimit = wigEncodeStartingUpperLimit;
bg->graphLowerLimit = wigEncodeStartingLowerLimit;
slAddHead(&bgList, bg);
bedFree(&bed);
}
sqlFreeResult(&sr);
hFreeConn(&conn);
slReverse(&bgList);
tg->items = bgList;
} /* bedGraphLoadItems() */
void getInvariants(char *db, struct snpExceptions *exceptionList,
struct slName *chromList, char *fileBase)
/* write list of invariants to output file */
{
struct sqlConnection *conn = hAllocConn(db);
struct sqlResult *sr = NULL;
struct snpExceptions *el = NULL;
struct slName *chrom = NULL;
char **row = NULL;
char query[1024];
unsigned long int invariantCount;
char thisFile[64];
FILE *outFile;
int colCount, i;
char idString[3];
for (el=exceptionList; el!=NULL; el=el->next)
{
if (el->exceptionId<10)
safef(idString,sizeof(idString), "0%d", el->exceptionId);
else
safef(idString,sizeof(idString), "%d", el->exceptionId);
invariantCount = 0;
if (startsWith("select",el->query))
{
safef(thisFile, sizeof(thisFile), "%s.%s.bed", fileBase, idString);
outFile = mustOpen(thisFile, "w");
fprintf(outFile, "# exceptionId:\t%d\n# query:\t%s;\n", el->exceptionId, el->query);
for (chrom=chromList; chrom!=NULL; chrom=chrom->next)
{
fflush(outFile); /* to keep an eye on output progress */
sqlSafef(query, sizeof(query),
"%-s and chrom='%s'", el->query, chrom->name);
sr = sqlGetResult(conn, query);
colCount = sqlCountColumns(sr);
while ((row = sqlNextRow(sr))!=NULL)
{
invariantCount++;
fprintf(outFile, "%s", row[0]);
for (i=1; i<colCount; i++)
fprintf(outFile, "\t%s", row[i]);
fprintf(outFile, "\n");
}
}
}
else if (startsWith("group",el->query))
{
struct slName *nameList = NULL;
struct slName *name = NULL;
safef(thisFile, sizeof(thisFile), "%s.%s.bed", fileBase, idString);
outFile = mustOpen(thisFile, "w");
fprintf(outFile, "# exceptionId:\t%d\n# query:\t%s;\n", el->exceptionId, el->query);
nameList = getGroupList(db, el->query);
for (name=nameList; name!=NULL; name=name->next)
{
sqlSafef(query, sizeof(query),
"select chrom,chromStart,chromEnd,name,%d as score,class,locType,observed "
"from snp where name='%s'", el->exceptionId, name->name);
sr = sqlGetResult(conn, query);
colCount = sqlCountColumns(sr);
while ((row = sqlNextRow(sr))!=NULL)
{
invariantCount++;
fprintf(outFile, "%s", row[0]);
for (i=1; i<colCount; i++)
fprintf(outFile, "\t%s", row[i]);
fprintf(outFile, "\n");
}
}
}
else
{
printf("Invariant %d has no query string\n", el->exceptionId);
continue;
}
carefulClose(&outFile);
printf("Invariant %d has %lu exceptions, written to this file: %s\n",
el->exceptionId, invariantCount, thisFile);
fflush(stdout);
sqlSafef(query, sizeof(query),
"update snpExceptions set num=%lu where exceptionId=%d",
invariantCount, el->exceptionId);
sr=sqlGetResult(conn, query); /* there's probably a better way to do this */
}
}