void showSeqFromTable(struct sqlConnection *conn, char *geneId, char *geneName, char *table) /* Show some sequence from given table. */ { char query[512]; struct sqlResult *sr; char **row; hPrintf("<TT><PRE>"); safef(query, sizeof(query), "select seq from %s where name = '%s'", table, geneId); sr = sqlGetResult(conn, query); if ((row = sqlNextRow(sr)) != NULL) { char *seq = row[0]; hPrintf(">%s (%s) length=%d\n", geneId, geneName, (seq!=NULL) ? (int)strlen(seq): 0); writeSeqWithBreaks(stdout, seq, strlen(seq), 60); } sqlFreeResult(&sr); hPrintf("</PRE></TT>"); }
struct refLink *loadRefLink(char *database, struct hash *hash) /* Load refLink into hash keyed by mrna accession and * return list too. */ { struct sqlConnection *conn = hAllocConn(database); struct sqlResult *sr; char **row; struct refLink *list = NULL, *el; sr = sqlGetResult(conn, "select * from refLink"); while ((row = sqlNextRow(sr)) != NULL) { el = refLinkLoad(row); hashAdd(hash, el->mrnaAcc, el); slAddHead(&list, el); } sqlFreeResult(&sr); hFreeConn(&conn); slReverse(&list); return list; }
void addSizes(char *database, struct hash *eiHash, struct hash *dateHash) /* Add and date info to ests in eiHash. Date strings are stored in dateHash */ { struct sqlConnection *conn = sqlConnect(database); struct sqlResult *sr = NULL; char **row; sr = sqlGetResult(conn, "NOSQLINJ select acc,size,gb_date from seq"); while ((row = sqlNextRow(sr)) != NULL) { char *acc = row[0]; struct estInfo *ei = hashFindVal(eiHash, acc); if (ei != NULL) { ei->size = sqlUnsigned(row[1]); ei->date = hashStoreName(dateHash, row[2]); } } sqlFreeResult(&sr); sqlDisconnect(&conn); }
struct hash *posHashFromTable(struct sqlConnection *conn, char *table) /* Store name, chrom, chromStart in a hash of slPairs. (cheap) */ { struct hash *posHash = newHash(24); struct sqlResult *sr = NULL; char query[256]; char **row; sqlSafef(query, sizeof(query), "select name,chrom,chromStart from %s", table); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { int chromStart = (int)sqlUnsigned(row[2]); struct slPair *pair; AllocVar(pair); pair->name = cloneString(row[1]); pair->val = intToPt(chromStart); hashAdd(posHash, row[0], pair); } sqlFreeResult(&sr); return posHash; }
struct rbTree *getSeqGaps(struct sqlConnection *conn, char *chrom) /* Return a tree of ranges for sequence gaps in chromosome */ { struct rbTree *tree = rbTreeNew(simpleRangeCmp); int rowOffset; struct sqlResult *sr = hChromQuery(conn, "gap", chrom, NULL, &rowOffset); char **row; while ((row = sqlNextRow(sr)) != NULL) { struct agpGap gap; struct simpleRange *range; agpGapStaticLoad(row+rowOffset, &gap); lmAllocVar(tree->lm, range); range->start = gap.chromStart; range->end = gap.chromEnd; rbTreeAdd(tree, range); } sqlFreeResult(&sr); return tree; }
static struct genbankCds getCds(struct sqlConnection *conn, struct mappingInfo *mi) /* Get CDS, return empty genebankCds if not found or can't parse */ { char query[256]; struct sqlResult *sr; struct genbankCds cds; char **row; sqlSafef(query, sizeof(query), "select cds.name " "from %s.gbCdnaInfo, %s.cds " "where gbCdnaInfo.acc=\"%s\" and gbCdnaInfo.cds=cds.id", database, database, mi->gbAcc); sr = sqlMustGetResult(conn, query); row = sqlNextRow(sr); if ((row == NULL) || !genbankCdsParse(row[0], &cds)) ZeroVar(&cds); /* can't get or parse cds */ sqlFreeResult(&sr); return cds; }
void loadBed6(struct track *tg) /* Load the items in one custom track - just move beds in * window... */ { struct bed *bed, *list = NULL; struct sqlConnection *conn = hAllocConn(database); struct sqlResult *sr; char **row; int rowOffset; sr = hRangeQuery(conn, tg->table, chromName, winStart, winEnd, NULL, &rowOffset); while ((row = sqlNextRow(sr)) != NULL) { bed = bedLoadN(row+rowOffset, 6); slAddHead(&list, bed); } sqlFreeResult(&sr); hFreeConn(&conn); slReverse(&list); tg->items = list; }
static void altGraphXLoadItemsPack(struct track *tg) /* load the altGraphX data to a track and caclulate full height. */ { struct sqlConnection *conn = hAllocConn(database); int rowOffSet; char **row; struct altGraphX *ag=NULL, *agList=NULL; struct sqlResult *sr = hRangeQuery(conn, tg->table, chromName, winStart, winEnd, NULL, &rowOffSet); while((row = sqlNextRow(sr)) != NULL) { ag = altGraphXLoad(row + rowOffSet); slAddHead(&agList, ag); } slReverse(&agList); tg->items = agList; altGraphXCalcHeight(tg, tg->visibility); sqlFreeResult(&sr); hFreeConn(&conn); tg->items = agList; }
struct genePred *loadGenePred(char *database, char *chrom, char *track, struct binKeeper *bk) /* Load in a gene prediction track to bk. */ { struct sqlConnection *conn = hAllocConn(database); struct sqlResult *sr; char **row; int rowOffset; struct genePred *list = NULL, *el; sr = hChromQuery(conn, track, chrom, NULL, &rowOffset); while ((row = sqlNextRow(sr)) != NULL) { el = genePredLoad(row + rowOffset); binKeeperAdd(bk, el->txStart, el->txEnd, el); slAddHead(&list, el); } sqlFreeResult(&sr); hFreeConn(&conn); slReverse(&list); return list; }
struct gvLink *gvLinkLoadByQuery(struct sqlConnection *conn, char *query) /* Load all gvLink 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 gvLinkFreeList(). */ { struct gvLink *list = NULL, *el; struct sqlResult *sr; char **row; sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { el = gvLinkLoad(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; }
static void bufferRowsFromSqlQuery(struct annoStreamDb *self, char *query, int queryMaxItems) /* Store all rows from query in rowBuf. */ { struct sqlResult *sr = sqlGetResult(self->conn, query); struct rowBuf *rowBuf = &(self->rowBuf); rowBufInit(rowBuf, ASD_CHUNK_SIZE); struct annoStreamer *sSelf = &(self->streamer); char **row = NULL; int ix = 0; while ((row = sqlNextRow(sr)) != NULL) { if (ix >= rowBuf->size) errAbort("annoStreamDb %s: rowBuf overflow, got more than %d rows", sSelf->name, rowBuf->size); rowBuf->buf[ix++] = lmCloneRow(rowBuf->lm, row, sSelf->numCols+self->omitBin); } // Set rowBuf->size to the number of rows we actually stored. rowBuf->size = ix; sqlFreeResult(&sr); updateNextChunkState(self, queryMaxItems); }
struct slName *getChromList(char *db) /* Get list of all chromosomes. */ { struct sqlConnection *conn = hAllocConn(db); struct sqlResult *sr = NULL; char **row = NULL; struct slName *list = NULL; struct slName *el = NULL; char *query = NOSQLINJ "select chrom from chromInfo"; sr = sqlGetResult(conn, query); while ((row=sqlNextRow(sr))!=NULL) { el = newSlName(row[0]); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); hFreeConn(&conn); return list; }
static struct ccdsLocationsJoin *loadLocations(struct sqlConnection *conn, struct genomeInfo *genome, struct hash* ignoreTbl, struct hash *gotCcds) /* load all exon locations into a list, sort by ccds id, and then * chrom and start */ { verbose(2, "begin loadLocations\n"); char *query = mkCcdsGeneSelect(genome, conn); struct sqlResult *sr = sqlGetResult(conn, query); struct ccdsLocationsJoin *locs = NULL; char **row; int cnt = 0; while ((row = sqlNextRow(sr)) != NULL) { locationProcessRow(row, &locs, ignoreTbl, gotCcds); cnt++; } sqlFreeResult(&sr); slSort(&locs, ccdsLocationsJoinCmp); verbose(2, "end loadLocations: %d exons\n", cnt); return locs; }
void dropDownAdvFilterControls(struct column *col, struct sqlConnection *conn) /* Print out controls for dropdown list filter. */ { struct sqlResult *sr; char **row; char query[256]; struct slName *list=NULL, *el; sqlSafef(query, sizeof(query), "select distinct %s from gisaidSubjInfo", col->name); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { char *val = row[0]; if (col->remap) val = hashFindVal(col->remap,val); slNameAddHead(&list, val); } sqlFreeResult(&sr); slNameSort(&list); hPrintf("choose: "); hPrintf("<SELECT NAME=\"%s\"", "countVarName"); hPrintf(">\n"); hPrintf("<OPTION VALUE=\"\">"); for (el = list; el; el = el->next) { hPrintf("<OPTION VALUE=\"%s\"", el->name); if (sameString(el->name, "displayCountString")) hPrintf(" SELECTED"); hPrintf(">%s\n", el->name); } hPrintf("</SELECT>\n"); slFreeList(&list); }
void cloneAliPosTab(char *fileName, struct hash *cloneHash) /* Write out clonePos.tab. */ { char query[256]; struct sqlResult *sr; char **row; struct clonePos *posList = NULL, *pos; struct cloneInfo *info; struct sqlConnection *conn = hAllocConn(); sprintf(query, "select * from chr18_frags"); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { struct psl *psl = pslLoad(row); fragNameToCloneName(psl->qName); info = findClone(cloneHash, psl->qName); if ((pos = info->aliPos) == NULL) { AllocVar(pos); pos->info = info; info->aliPos = pos; pos->start = psl->tStart; pos->end = psl->tEnd; slAddHead(&posList, pos); } else { if (pos->start > psl->tStart) pos->start = psl->tStart; if (pos->end < psl->tEnd) pos->end = psl->tEnd; } pslFree(&psl); } sqlFreeResult(&sr); hFreeConn(&conn); slSort(&posList, cmpClonePos); writePosList(fileName, posList, "chr18"); }
void clonePosTab(char *fileName, struct hash *cloneHash) /* Write out clonePos.tab. */ { char query[256]; struct sqlResult *sr; char **row; struct clonePos *posList = NULL, *pos; struct gl gl; struct cloneInfo *info; struct sqlConnection *conn = hAllocConn(); sprintf(query, "select * from chr18_gl"); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { glStaticLoad(row, &gl); fragNameToCloneName(gl.frag); info = findClone(cloneHash, gl.frag); if ((pos = info->pos) == NULL) { AllocVar(pos); pos->info = info; info->pos = pos; pos->start = gl.start; pos->end = gl.end; slAddHead(&posList, pos); } else { if (pos->start > gl.start) pos->start = gl.start; if (pos->end < gl.end) pos->end = gl.end; } } sqlFreeResult(&sr); hFreeConn(&conn); slSort(&posList, cmpClonePos); writePosList(fileName, posList, "chr18"); }
static void printPeakClusterInfo(struct trackDb *tdb, struct cart *cart, struct sqlConnection *conn, char *inputTrackTable, struct slName *fieldList, struct bed *cluster) /* Print an HTML table showing sources with hits in the cluster, along with signal. If cluster is NULL, show all sources assayed */ { /* Make the SQL query to get the table and all other fields we want to show * from inputTrackTable. */ struct dyString *query = dyStringNew(0); queryInputTrackTable(query, inputTrackTable, fieldList); char *vocabFile = NULL; struct hash *vocabHash = NULL; getVocab(tdb, cart, &vocabFile, &vocabHash); int displayNo = 0; int fieldCount = slCount(fieldList); struct sqlResult *sr = sqlGetResult(conn, query->string); char **row; while ((row = sqlNextRow(sr)) != NULL) { double signal = 0; if (cluster != NULL) { char *table = row[0]; signal = getSignalAt(table, cluster); if (signal == 0) continue; } printf("</TR><TR>\n"); webPrintIntCell(++displayNo); if (signal != 0) webPrintDoubleCell(signal); printControlledVocabFields(row+1, fieldCount, fieldList, vocabFile, vocabHash); printMetadataForTable(row[0]); } sqlFreeResult(&sr); freez(&vocabFile); dyStringFree(&query); }
void txCdsGoodBed(char *database, char *outBed, char *outCds) /* txCdsGoodBed - Create positive example training set for SVM. This is based on * the refSeq reviewed genes, but we fragment a certain percentage of them so as * not to end up with a SVM that *requires* a complete transcript. */ { struct sqlConnection *conn = sqlConnect(database); char *refTrack = "refGene"; char *statusTable = "refSeqStatus"; if (!sqlTableExists(conn, refTrack)) errAbort("table %s doesn't exist in %s", refTrack, database); if (!sqlTableExists(conn, statusTable)) errAbort("table %s doesn't exist in %s", statusTable, database); FILE *fBed = mustOpen(outBed, "w"); FILE *fCds = mustOpen(outCds, "w"); char *query = "NOSQLINJ select name,chrom,strand,txStart,txEnd,cdsStart,cdsEnd,exonCount,exonStarts,exonEnds " "from refGene r,refSeqStatus s where r.name=s.mrnaAcc and s.status='Reviewed'"; struct sqlResult *sr = sqlGetResult(conn, query); char **row; double randScale = 1.0/RAND_MAX; int id = 0; while ((row = sqlNextRow(sr)) != NULL) { struct genePred *gp = genePredLoad(row); int start = gp->txStart, end = gp->txEnd; char *type = "refReviewed"; if (rand()*randScale < frag) { double midRatio = rand()*randScale; if (midRatio > 0.5) gpFragLimits(gp, 0, midRatio, &start, &end); else gpFragLimits(gp, midRatio, 1.0, &start, &end); type = "refFrag"; } gpPartOutAsBed(gp, start, end, fBed, type, ++id, 0); gpPartOutAsCds(gp, start, end, fCds, type, id); } carefulClose(&fBed); }
struct targetPcrServer *getTargetServerList(char *db, char *name) /* Get list of available non-genomic-assembly target pcr servers associated * with db (and name, if not NULL). There may be none -- that's fine. */ { struct targetPcrServer *serverList = NULL, *server; struct sqlConnection *conn = hConnectCentral(); struct sqlConnection *conn2 = hAllocConn(db); struct sqlResult *sr; char **row; struct dyString *dy = dyStringNew(0); sqlDyStringPrintf(dy, "select b.host, b.port, t.* from targetDb as t, blatServers as b " "where b.db = t.name and t.db = '%s' and b.canPcr = 1 ", db); if (isNotEmpty(name)) sqlDyStringPrintf(dy, "and t.name = '%s' ", name); dyStringAppend(dy, "order by t.priority"); sr = sqlGetResult(conn, dy->string); while ((row = sqlNextRow(sr)) != NULL) { /* Keep this server only if its timestamp is newer than the tables * and file on which it depends. */ struct targetDb *target = targetDbMaybeLoad(conn2, row+2); if (target != NULL) { AllocVar(server); server->host = cloneString(row[0]); server->port = cloneString(row[1]); server->targetDb = target; slAddHead(&serverList, server); } } dyStringFree(&dy); sqlFreeResult(&sr); hDisconnectCentral(&conn); hFreeConn(&conn2); slReverse(&serverList); return serverList; }
struct rbTree *getTrf(struct sqlConnection *conn, char *chrom) /* Return a tree of ranges for simple repeats in chromosome. */ { struct rbTree *tree = rbTreeNew(simpleRangeCmp); struct simpleRange *range, *prevRange = NULL; char query[256]; struct sqlResult *sr; char **row; sqlSafef(query, sizeof query, "select chromStart,chromEnd from simpleRepeat " "where chrom = '%s'", chrom); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { lmAllocVar(tree->lm, range); range->start = sqlUnsigned(row[0]); range->end = sqlUnsigned(row[1]); if (prevRange == NULL) prevRange = range; else if (overlap(range, prevRange)) { /* merge r into prevR & discard; prevR gets passed forward. */ if (range->end > prevRange->end) prevRange->end = range->end; if (range->start < prevRange->start) prevRange->start = range->start; } else { rbTreeAdd(tree, prevRange); prevRange = range; } } if (prevRange != NULL) rbTreeAdd(tree, prevRange); sqlFreeResult(&sr); return tree; }
void getChroms(struct sqlConnection *conn, struct hash **retHash, struct chrom **retList) /* Get hash of chromosomes from database. */ { struct sqlResult *sr; char **row; struct chrom *chromList = NULL, *chrom; struct hash *hash = hashNew(8); sr = sqlGetResult(conn, "NOSQLINJ select chrom,size from chromInfo"); while ((row = sqlNextRow(sr)) != NULL) { AllocVar(chrom); hashAddSaveName(hash, row[0], chrom, &chrom->name); chrom->size = atoi(row[1]); slAddHead(&chromList, chrom); } sqlFreeResult(&sr); slReverse(&chromList); *retHash = hash; *retList = chromList; }
struct slName *readSnps(char *chromName, int start, int end) /* change to save coords of snps */ { char query[512]; struct sqlConnection *conn = hAllocConn(); struct sqlResult *sr; char **row; struct slName *el = NULL; struct slName *ret = NULL; sqlSafef(query, sizeof(query), "select name from snp125 where chrom = '%s' and chromStart >= %d and chromEnd <= %d", chromName, start, end); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { el = slNameNew(row[0]); slAddHead(&ret, el); } sqlFreeResult(&sr); hFreeConn(&conn); return ret; }
static void getGenePosition(struct sqlConnection *conn) /* Get gene position from database. */ { char *table = genomeSetting("knownGene"); char query[256]; struct sqlResult *sr; char **row; sqlSafef(query, sizeof(query), "select chrom,txStart,txEnd from %s where name = '%s'" , table, curGeneId); sr = sqlGetResult(conn, query); row = sqlNextRow(sr); if (row != NULL) { curGeneChrom = cloneString(row[0]); curGeneStart = atoi(row[1]); curGeneEnd = atoi(row[2]); } else hUserAbort("Couldn't find %s in %s.%s", curGeneId, database, table); sqlFreeResult(&sr); }
void check(struct sqlConnection *conn, char *table) /* Check it's as planned. */ { char query[256], **row; struct sqlResult *sr; int lastEnd = -1, lastStart = -1, start, end; sqlSafef(query, sizeof query, "select chromStart,chromEnd from %s", table); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { start = atoi(row[0]); end = atoi(row[1]); if (start < lastStart) fprintf(stderr,"Out of order: %d,%d\n", lastStart, start); if (rangeIntersection(lastStart, lastEnd, start-1, end) > 0) fprintf(stderr,"Overlapping: (%d %d) (%d %d)\n", lastStart, lastEnd, start, end); lastStart = start; lastEnd = end; } sqlFreeResult(&sr); errAbort("All for now"); }
/* Copied from hgLoadWiggle. */ static struct hash *loadAllChromInfo() /* Load up all chromosome infos. */ { struct chromInfo *el; struct sqlConnection *conn = sqlConnect(database); struct sqlResult *sr = NULL; struct hash *ret; char **row; ret = newHash(0); sr = sqlGetResult(conn, "NOSQLINJ select * from chromInfo"); while ((row = sqlNextRow(sr)) != NULL) { el = chromInfoLoad(row); verbose(4, "Add hash %s value %u (%#lx)\n", el->chrom, el->size, (unsigned long)&el->size); hashAdd(ret, el->chrom, (void *)(& el->size)); } sqlFreeResult(&sr); sqlDisconnect(&conn); return ret; }
void addSimple(char *id, char *table, char *idField, char *valField, struct sqlConnection *conn, struct hash *uniqHash, FILE *f) /* Append all values associated with ID in table to line of file. */ { char query[256], **row; struct sqlResult *sr; fprintf(f, "\t"); sqlSafef(query, sizeof(query), "select %s from %s where %s='%s'", valField, table, idField, id); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { char *val = row[0]; if (!hashLookup(uniqHash, val)) { hashAdd(uniqHash, val, NULL); fprintf(f, "%s ", val); } } sqlFreeResult(&sr); }
static struct genePred *queryNext(struct genePredReader* gpr) /* read the next record from a query */ { int iFld, iCol; char **row = sqlNextRow(gpr->sr); char *reorderedRow[GENEPREDX_NUM_COLS]; if (row == NULL) return NULL; /* fill in row defaults */ for (iFld = 0; iFld < GENEPREDX_NUM_COLS; iFld++) reorderedRow[iFld] = fieldTbl[iFld].defaultVal; /* reorder row */ for (iCol = 0; iCol < gpr->queryCols; iCol++) { iFld = gpr->queryToFldMap[iCol]; if (iFld >= 0) reorderedRow[iFld] = row[iCol]; } return genePredExtLoad(reorderedRow, gpr->numFields); }
struct chromPos *snpIndexLookup(struct sqlConnection *conn, char *table, char *query, char *name) /* Lookup the marker name in the snp table using the name index. * As the snp table grew beyond 150 million rows, * loading the entire table into a hash from the database became too slow. */ { static struct chromPos pos; char fullQuery[256]; sqlSafef(fullQuery, sizeof fullQuery, query, table); sqlSafefAppend(fullQuery, sizeof fullQuery, " where name='%s'", name); struct sqlResult *sr; char **row; sr = sqlGetResult(conn, fullQuery); if ((row = sqlNextRow(sr)) != NULL) { pos.chrom = cloneString(row[0]); pos.pos = sqlUnsigned(row[1]); } sqlFreeResult(&sr); if (!row) return NULL; return &pos; }
int tetCountInChrom(struct sqlConnection *conn, char *chrom) /* Number of tets in chromosome. */ { char query[512]; int tetCount = 0; int lastPos = -1000000; struct sqlResult *sr; char **row; int chromPos; sqlSafef(query, sizeof query, "select chromStart from %s_tet_waba", chrom); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { chromPos = sqlUnsigned(row[0]); if (chromPos - lastPos > 1000) ++tetCount; lastPos = chromPos; } sqlFreeResult(&sr); return tetCount; }
struct hash *makeOtherHash(char *database, char *table) /* Make otherSeq valued hash of other sequences */ { char query[256]; struct hash *hash = newHash(7); struct sqlConnection *conn = hAllocConn(database); struct sqlResult *sr; char **row; struct otherSeq *os; sprintf(query, "select chrom,fileName from %s", table); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { AllocVar(os); hashAddSaveName(hash, row[0], os, &os->name); os->nibFile = cloneString(row[1]); nibOpenVerify(os->nibFile, &os->f, &os->chromSize); } hFreeConn(&conn); return hash; }