void doFlyreg(struct trackDb *tdb, char *item) /* flyreg.org: Drosophila DNase I Footprint db. */ { struct dyString *query = newDyString(256); struct sqlConnection *conn = hAllocConn(database); struct sqlResult *sr = NULL; char **row; int start = cartInt(cart, "o"); int end = cartInt(cart, "t"); char fullTable[HDB_MAX_TABLE_STRING]; boolean hasBin = FALSE; char *motifTable = "flyregMotif"; struct dnaMotif *motif = NULL; boolean isVersion2 = sameString(tdb->table, "flyreg2"); genericHeader(tdb, item); if (!hFindSplitTable(database, seqName, tdb->table, fullTable, sizeof fullTable, &hasBin)) errAbort("track %s not found", tdb->table); sqlDyStringPrintf(query, "select * from %s where chrom = '%s' and ", fullTable, seqName); hAddBinToQuery(start, end, query); sqlDyStringPrintf(query, "chromStart = %d and name = '%s'", start, item); sr = sqlGetResult(conn, query->string); if ((row = sqlNextRow(sr)) != NULL) { struct flyreg2 fr; if (isVersion2) flyreg2StaticLoad(row+hasBin, &fr); else flyregStaticLoad(row+hasBin, (struct flyreg *)(&fr)); printf("<B>Factor:</B> %s<BR>\n", fr.name); printf("<B>Target:</B> %s<BR>\n", fr.target); if (isVersion2) printf("<B>Footprint ID:</B> %06d<BR>\n", fr.fpid); printf("<B>PubMed ID:</B> <A HREF=\""); printEntrezPubMedUidUrl(stdout, fr.pmid); printf("\" TARGET=_BLANK>%d</A><BR>\n", fr.pmid); bedPrintPos((struct bed *)(&fr), 3, tdb); if (hTableExists(database, motifTable)) { motif = loadDnaMotif(item, motifTable); if (motif != NULL) motifHitSection(NULL, motif); } } else errAbort("query returned no results: \"%s\"", query->string); dyStringFree(&query); sqlFreeResult(&sr); hFreeConn(&conn); if (motif != NULL) webNewSection("%s",tdb->longLabel); printTrackHtml(tdb); }
static void doQuery(struct sqlConnection *conn, char *fullName, struct lm *lm, struct hash *hash, int start, int end, char * chainId, boolean isSplit) /* doQuery- check the database for chain elements between * start and end. Use the passed hash to resolve chain * id's and place the elements into the right * linkedFeatures structure */ { struct sqlResult *sr = NULL; char **row; struct linkedFeatures *lf; struct simpleFeature *sf; struct dyString *query = newDyString(1024); char *force = ""; if (isSplit) force = "force index (bin)"; if (chainId == NULL) dyStringPrintf(query, "select chainId,tStart,tEnd,qStart from %sLink %s where ", fullName, force); else dyStringPrintf(query, "select chainId, tStart,tEnd,qStart from %sLink where chainId=%s and ", fullName, chainId); if (!isSplit) dyStringPrintf(query, "tName='%s' and ", chromName); hAddBinToQuery(start, end, query); dyStringPrintf(query, "tStart<%u and tEnd>%u", end, start); sr = sqlGetResult(conn, query->string); /* Loop through making up simple features and adding them * to the corresponding linkedFeature. */ while ((row = sqlNextRow(sr)) != NULL) { lf = hashFindVal(hash, row[0]); if (lf != NULL) { lmAllocVar(lm, sf); sf->start = sqlUnsigned(row[1]); sf->end = sqlUnsigned(row[2]); sf->qStart = sqlUnsigned(row[3]); sf->qEnd = sf->qStart + (sf->end - sf->start); slAddHead(&lf->components, sf); } } sqlFreeResult(&sr); dyStringFree(&query); }
static void asdDoQuerySimple(struct annoStreamDb *self, char *minChrom, uint minEnd) /* Return a sqlResult for a query on table items in position range. * If doing a whole genome query. just select all rows from table. */ // NOTE: it would be possible to implement filters at this level, as in hgTables. { struct annoStreamer *streamer = &(self->streamer); boolean hasWhere = FALSE; struct dyString *query = self->makeBaselineQuery(self, &hasWhere); if (!streamer->positionIsGenome) { if (minChrom && differentString(minChrom, streamer->chrom)) errAbort("annoStreamDb %s: nextRow minChrom='%s' but region chrom='%s'", streamer->name, minChrom, streamer->chrom); if (self->hasBin) { // Results will be in bin order, but we can restore chromStart order by // accumulating initial coarse-bin items and merge-sorting them with // subsequent finest-bin items which will be in chromStart order. resetMergeState(self); self->mergeBins = TRUE; self->qLm = lmInit(0); } if (self->endFieldIndexName != NULL) // Don't let mysql use a (chrom, chromEnd) index because that messes up // sorting by chromStart. sqlDyStringPrintf(query, " IGNORE INDEX (%s)", self->endFieldIndexName); sqlDyStringAppend(query, hasWhere ? " and " : " where "); sqlDyStringPrintf(query, "%s='%s'", self->chromField, streamer->chrom); int chromSize = annoAssemblySeqSize(streamer->assembly, streamer->chrom); if (streamer->regionStart != 0 || streamer->regionEnd != chromSize) { dyStringAppend(query, " and "); if (self->hasBin) hAddBinToQuery(streamer->regionStart, streamer->regionEnd, query); sqlDyStringPrintf(query, "%s < %u and %s > %u", self->startField, streamer->regionEnd, self->endField, streamer->regionStart); } if (self->notSorted) sqlDyStringPrintf(query, " order by %s", self->startField); } else if (self->notSorted) sqlDyStringPrintf(query, " order by %s,%s", self->chromField, self->startField); if (self->maxOutRows > 0) dyStringPrintf(query, " limit %d", self->maxOutRows); struct sqlResult *sr = sqlGetResult(self->conn, query->string); dyStringFree(&query); self->sr = sr; self->needQuery = FALSE; }
static void testOneSql(int start, int end, char *expected) { struct dyString *sqlQuery; sqlQuery = newDyString(1024); hAddBinToQuery(start, end, sqlQuery); if (NULL != expected) if (differentString(sqlQuery->string,expected)) { verbose(2,"#\tERROR: SQL incorrect at (%d, %d)\n", start, end); ++failureCount; } verbose(3,"# (%d, %d):\nsql:\"%s\",\n", start, end, sqlQuery->string); freeDyString(&sqlQuery); }
static void asdDoQueryChunking(struct annoStreamDb *self, char *minChrom, uint minEnd) /* Return a sqlResult for a query on table items in position range. * If doing a whole genome query, just select all rows from table. */ { struct annoStreamer *sSelf = &(self->streamer); boolean hasWhere = FALSE; struct dyString *query = self->makeBaselineQuery(self, &hasWhere); if (sSelf->chrom != NULL && self->rowBuf.size > 0 && !self->doNextChunk) { // We're doing a region query, we already got some rows, and don't need another chunk: resetRowBuf(&self->rowBuf); self->eof = TRUE; } if (self->useMaxOutRows) { self->maxOutRows -= self->rowBuf.size; if (self->maxOutRows <= 0) self->eof = TRUE; } if (self->eof) return; int queryMaxItems = ASD_CHUNK_SIZE; if (self->useMaxOutRows && self->maxOutRows < queryMaxItems) queryMaxItems = self->maxOutRows; if (self->hasBin) { // Results will be in bin order, but we can restore chromStart order by // accumulating initial coarse-bin items and merge-sorting them with // subsequent finest-bin items which will be in chromStart order. if (self->doNextChunk && self->mergeBins && !self->gotFinestBin) errAbort("annoStreamDb %s: can't continue merge in chunking query; " "increase ASD_CHUNK_SIZE", sSelf->name); self->mergeBins = TRUE; if (self->qLm == NULL) self->qLm = lmInit(0); } if (self->endFieldIndexName != NULL) // Don't let mysql use a (chrom, chromEnd) index because that messes up // sorting by chromStart. sqlDyStringPrintf(query, " IGNORE INDEX (%s) ", self->endFieldIndexName); if (sSelf->chrom != NULL) { uint start = sSelf->regionStart; if (minChrom) { if (differentString(minChrom, sSelf->chrom)) errAbort("annoStreamDb %s: nextRow minChrom='%s' but region chrom='%s'", sSelf->name, minChrom, sSelf->chrom); if (start < minEnd) start = minEnd; } if (self->doNextChunk && start < self->nextChunkStart) start = self->nextChunkStart; sqlDyStringAppend(query, hasWhere ? " and " : " where "); sqlDyStringPrintf(query, "%s = '%s' and ", self->chromField, sSelf->chrom); if (self->hasBin) { if (self->doNextChunk && self->gotFinestBin) // It would be way more elegant to make a hAddBinTopLevelOnly but this will do: dyStringPrintf(query, "bin > %d and ", self->minFinestBin); hAddBinToQuery(start, sSelf->regionEnd, query); } if (self->doNextChunk) sqlDyStringPrintf(query, "%s >= %u and ", self->startField, self->nextChunkStart); sqlDyStringPrintf(query, "%s < %u and %s > %u ", self->startField, sSelf->regionEnd, self->endField, start); if (self->notSorted) sqlDyStringPrintf(query, "order by %s ", self->startField); sqlDyStringPrintf(query, "limit %d", queryMaxItems); bufferRowsFromSqlQuery(self, query->string, queryMaxItems); if (self->rowBuf.size == 0) self->eof = TRUE; } else { // Genome-wide query: break it into chrom-by-chrom queries. if (self->queryChrom == NULL) self->queryChrom = self->chromList; else if (!self->doNextChunk) { self->queryChrom = self->queryChrom->next; resetMergeState(self); } if (minChrom != NULL) { // Skip chroms that precede minChrom while (self->queryChrom != NULL && strcmp(self->queryChrom->name, minChrom) < 0) { self->queryChrom = self->queryChrom->next; self->doNextChunk = FALSE; resetMergeState(self); } if (self->hasBin) { self->mergeBins = TRUE; if (self->qLm == NULL) self->qLm = lmInit(0); } } if (self->queryChrom == NULL) self->eof = TRUE; else { char *chrom = self->queryChrom->name; int start = 0; if (minChrom != NULL && sameString(chrom, minChrom)) start = minEnd; if (self->doNextChunk && start < self->nextChunkStart) start = self->nextChunkStart; uint end = annoAssemblySeqSize(self->streamer.assembly, self->queryChrom->name); sqlDyStringAppend(query, hasWhere ? " and " : " where "); sqlDyStringPrintf(query, "%s = '%s' ", self->chromField, chrom); if (start > 0 || self->doNextChunk) { dyStringAppend(query, "and "); if (self->hasBin) { if (self->doNextChunk && self->gotFinestBin) // It would be way more elegant to make a hAddBinTopLevelOnly but this will do: dyStringPrintf(query, "bin > %d and ", self->minFinestBin); hAddBinToQuery(start, end, query); } if (self->doNextChunk) sqlDyStringPrintf(query, "%s >= %u and ", self->startField, self->nextChunkStart); // region end is chromSize, so no need to constrain startField here: sqlDyStringPrintf(query, "%s > %u ", self->endField, start); } if (self->notSorted) sqlDyStringPrintf(query, "order by %s ", self->startField); dyStringPrintf(query, "limit %d", queryMaxItems); bufferRowsFromSqlQuery(self, query->string, queryMaxItems); // If there happens to be no items on chrom, try again with the next chrom: if (! self->eof && self->rowBuf.size == 0) asdDoQueryChunking(self, minChrom, minEnd); } } dyStringFree(&query); }