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 */ } }