void addImagesMatchingStage(struct visiSearcher *searcher,
struct sqlConnection *conn, int schemeId, int taxon,
char *minAge, int wordIx, int wordCount)
/* Given a developmental stage scheme (schemeId) and a specific
* stage, return all images that match stage */
{
struct dyString *dy = dyStringNew(0);
char *maxAge;
// note in the code below minAge and maxAge are strings
// but they should contain float values. Putting single-quotes
// around them and escaping their contents is something that will
// protect against sql injection.
dyStringClear(dy);
sqlDyStringPrintf(dy,
"select age from lifeStage where lifeStageScheme = %d ",
schemeId);
sqlDyStringPrintf(dy,
"and age > '%s' order by age", minAge);
maxAge = sqlQuickString(conn, dy->string);
dyStringClear(dy);
sqlDyStringPrintf(dy, "select image.id from specimen,image ");
sqlDyStringPrintf(dy, "where specimen.age >= '%s' ", minAge);
if (maxAge != NULL)
sqlDyStringPrintf(dy, "and specimen.age < '%s' ", maxAge);
dyStringPrintf(dy, "and specimen.taxon = %d ", taxon);
dyStringPrintf(dy, "and specimen.id = image.specimen");
addImagesMatchingQuery(searcher, conn, dy->string, NULL, NULL,
wordIx, wordCount);
dyStringFree(&dy);
}
static void addImagesMatchingBodyPart(struct visiSearcher *searcher,
struct sqlConnection *conn, struct dyString *dy, char *bodyPart,
int startWord, int wordCount)
/* Add images that are contributed by given contributor to
* searcher with a weight of one. Use dy for scratch space for
* the query. */
{
struct hash *uniqHash = newHash(0);
dyStringClear(dy);
sqlDyStringPrintf(dy,
"select imageProbe.image from "
"bodyPart,expressionLevel,imageProbe "
"where bodyPart.name = \"%s\" "
"and bodyPart.id = expressionLevel.bodyPart "
"and expressionLevel.imageProbe = imageProbe.id "
"and expressionLevel.level > 0"
, bodyPart);
addImagesMatchingQuery(searcher, conn, dy->string, uniqHash, bodyPart,
startWord, wordCount);
dyStringClear(dy);
sqlDyStringPrintf(dy,
"select image.id from bodyPart,specimen,image "
"where bodyPart.name = \"%s\" "
"and bodyPart.id = specimen.bodyPart "
"and specimen.id = image.specimen", bodyPart);
addImagesMatchingQuery(searcher, conn, dy->string, uniqHash, bodyPart,
startWord, wordCount);
hashFree(&uniqHash);
}
static struct slName *getListFromCgapSageLibs(struct sqlConnection *conn, char *column, boolean returnIds, boolean distinct)
/* Return [unique] list of tissues sorted alphabetically. */
{
struct slName *list = NULL;
struct dyString *dy = dyStringNew(0);
char **row;
struct sqlResult *sr;
sqlDyStringPrintf(dy, "select ");
if (distinct)
dyStringAppend(dy, "distinct ");
sqlDyStringPrintf(dy, "%s", column);
if (returnIds)
dyStringAppend(dy, ",libId");
sqlDyStringPrintf(dy, " from cgapSageLib order by %s", column);
sr = sqlGetResult(conn, dy->string);
while ((row = sqlNextRow(sr)) != NULL)
{
char *word = (returnIds) ? row[1] : row[0];
slNameAddHead(&list, word);
}
slReverse(&list);
sqlFreeResult(&sr);
dyStringFree(&dy);
return list;
}
void weighMatches(struct sqlConnection *conn, struct visiMatch *matchList)
/* Set match field in match list according to priority, etc. */
{
struct visiMatch *match;
struct dyString *dy = dyStringNew(0);
/* Weigh matches by priority, and secondarily by age. */
for (match = matchList; match != NULL; match = match->next)
{
double priority;
double age;
/* Fetch priority. */
dyStringClear(dy);
sqlDyStringPrintf(dy,
"select imageFile.priority from imageFile,image "
"where image.id = %d and image.imageFile = imageFile.id"
, match->imageId);
priority = sqlQuickDouble(conn, dy->string);
/* Fetch age. */
dyStringClear(dy);
sqlDyStringPrintf(dy,
"select specimen.age from image,specimen "
"where image.id = %d and image.specimen = specimen.id"
, match->imageId);
age = sqlQuickDouble(conn, dy->string);
match->weight = -priority - age*0.0001;
}
}
static void transformToCanonical(struct searchResult *list,
struct sqlConnection *conn)
/* Transform search results to canonical versions. */
{
struct dyString *dy = newDyString(1024);
char *cannon = genomeSetting("canonicalTable");
char *isoform = genomeSetting("isoformTable");
struct sqlResult *sr;
char **row;
struct searchResult *el;
for (el = list; el != NULL; el = el->next)
{
dyStringClear(dy);
sqlDyStringPrintf(dy,
"select %s.transcript,%s.chrom,%s.chromStart,%s.chromEnd,%s.protein ",
cannon, cannon, cannon, cannon, cannon);
sqlDyStringPrintf(dy,
"from %s,%s ",
isoform, cannon);
sqlDyStringPrintf(dy,
"where %s.transcript = '%s' ",
isoform, el->gp.name);
sqlDyStringPrintf(dy,
"and %s.clusterId = %s.clusterId",
isoform, cannon);
sr = sqlGetResult(conn, dy->string);
if ((row = sqlNextRow(sr)) != NULL)
genePosFillFrom5(&el->gp, row);
sqlFreeResult(&sr);
}
dyStringFree(&dy);
}
void hgSoftberryHom(char *database, int fileCount, char *files[])
/* hgSoftberryHom - Make table storing Softberry protein homology information. */
{
int i;
char *fileName;
char *table = "softberryHom";
char *tabFileName = "softberryHom.tab";
FILE *f = mustOpen(tabFileName, "w");
struct sqlConnection *conn = NULL;
struct dyString *ds = newDyString(2048);
for (i=0; i<fileCount; ++i)
{
fileName = files[i];
printf("Processing %s\n", fileName);
makeTabLines(fileName, f);
}
carefulClose(&f);
/* Create table if it doesn't exist, delete whatever is
* already in it, and fill it up from tab file. */
conn = sqlConnect(database);
printf("Loading %s table\n", table);
sqlMaybeMakeTable(conn, table, createTable);
sqlDyStringPrintf(ds, "DELETE from %s", table);
sqlUpdate(conn, ds->string);
dyStringClear(ds);
sqlDyStringPrintf(ds, "LOAD data local infile '%s' into table %s",
tabFileName, table);
sqlUpdate(conn, ds->string);
sqlDisconnect(&conn);
}
void loadOneTable(char *database, struct sqlConnection *conn, char *tempName, char *tableName)
/* Load .tab file tempName into tableName and remove tempName. */
{
struct dyString *query = newDyString(1024);
verbose(1, "Loading up table %s\n", tableName);
if (sqlTableExists(conn, tableName))
{
sqlDyStringPrintf(query, "DROP table %s", tableName);
sqlUpdate(conn, query->string);
}
/* Create first part of table definitions, the fields. */
dyStringClear(query);
sqlDyStringPrintf(query, createRmskOut, tableName);
/* Create the indexes */
int indexLen = hGetMinIndexLength(database);
sqlDyStringPrintf(query, " INDEX(genoName(%d),bin))\n", indexLen);
sqlUpdate(conn, query->string);
/* Load database from tab-file. */
dyStringClear(query);
sqlDyStringPrintf(query, "LOAD data local infile '%s' into table %s",
tempName, tableName);
sqlUpdate(conn, query->string);
remove(tempName);
}
void encode2ExpDumpFlat(char *outFile, char *viewSql, char *flatTable)
/* encode2ExpDumpFlat - Dump the experiment table in a semi-flat way from a relationalized Encode2 metadatabase.. */
{
FILE *f = mustOpen(outFile, "w");
struct sqlConnection *conn = sqlConnect(database);
struct starTableInfo **stiArray;
AllocArray(stiArray, ArraySize(starFields));
int i;
for (i=0; i<ArraySize(starFields); ++i)
stiArray[i] = starTableInfoNew(conn, starFields[i]);
struct dyString *query = dyStringNew(2000);
sqlDyStringPrintf(query, "select ");
for (i=0; i<ArraySize(flatFields); ++i)
{
if (i != 0)
dyStringAppendC(query, ',');
sqlDyStringPrintf(query, "%s", flatFields[i]);
}
for (i=0; i<ArraySize(starFields); ++i)
{
dyStringAppendC(query, ',');
sqlDyStringPrintf(query, "%s", starFields[i]);
}
dyStringPrintf(query, " from %s%s", tablePrefix, "experiment");
struct sqlResult *sr = sqlGetResult(conn, query->string);
char **row;
while ((row = sqlNextRow(sr)) != NULL)
{
int flatSize = ArraySize(flatFields);
int starSize = ArraySize(starFields);
int i;
for (i=0; i<flatSize; ++i)
{
if (i != 0)
fputc('\t', f);
fputs(row[i], f);
}
for (i=0; i<starSize; ++i)
{
int id = sqlUnsigned(row[i+flatSize]);
fputc('\t', f);
if (id == 0)
fputs("n/a", f);
else
fputs(stiArray[i]->termsForIds[id], f);
}
fputc('\n', f);
}
sqlFreeResult(&sr);
sqlDisconnect(&conn);
carefulClose(&f);
makeViewSql(viewSql);
makeFlatTableSql(flatTable);
}
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);
}
void gapFileToTable(struct sqlConnection *conn, char *gapFileName,
char *gapTableName)
/* Build a single gap table from a single gap file. */
{
struct lineFile *lf = lineFileOpen(gapFileName, TRUE);
char tabFileName[256];
FILE *tabFile = NULL;
char *words[16];
int wordCount;
safef(tabFileName, sizeof(tabFileName), "%s.tab", gapTableName);
tabFile = mustOpen(tabFileName, "w");
while ((wordCount = lineFileChop(lf, words)) > 0)
{
if (wordCount < 5)
errAbort("Short line %d of %s", lf->lineIx, lf->fileName);
if (words[4][0] == 'N' || words[4][0] == 'U')
{
int len = strlen(words[0]);
if (len > maxChromNameSize)
{
maxChromNameSize = len;
if (maxChromNameSize > 254)
errAbort("ERROR: chrom name size is over 254(%d) characters: "
"'%s'", maxChromNameSize, words[0]);
}
struct agpGap gap;
agpGapStaticLoad(words, &gap);
gap.chromStart -= 1;
fprintf(tabFile, "%u\t", hFindBin(gap.chromStart, gap.chromEnd));
agpGapTabOut(&gap, tabFile);
}
}
lineFileClose(&lf);
fclose(tabFile);
if (! noLoad)
{
struct dyString *ds = newDyString(2048);
if (unsplit)
sqlDyStringPrintf(ds, createGapUnsplit, gapTableName,
maxChromNameSize, maxChromNameSize);
else
sqlDyStringPrintf(ds, createGapSplit, gapTableName);
char query[1024];
sqlRemakeTable(conn, gapTableName, ds->string);
sqlSafef(query, sizeof(query), "LOAD data local infile '%s' into table %s",
tabFileName, gapTableName);
sqlUpdate(conn, query);
remove(tabFileName);
freeDyString(&ds);
}
}
void intInClause(struct dyString *query, struct slInt *list)
/* Add in clause to query for all integers in list. */
{
struct slInt *el;
sqlDyStringPrintf(query, " in (");
for (el = list; el != NULL; el = el->next)
{
sqlDyStringPrintf(query, "%d", el->val);
if (el->next != NULL)
sqlDyStringPrintf(query, ",");
}
sqlDyStringPrintf(query, ") ");
}
void loadIntoDatabase(char *database, char *createString, char *table, char *tabName)
/* Load tabbed file into database table. */
{
struct sqlConnection *conn = sqlConnect(database);
struct dyString *ds = newDyString(2048);
sqlDyStringPrintf(ds, createString, table);
sqlRemakeTable(conn, table, ds->string);
dyStringClear(ds);
sqlDyStringPrintf(ds,
"LOAD data local infile '%s' into table %s", tabName, table);
sqlUpdate(conn, ds->string);
sqlDisconnect(&conn);
freeDyString(&ds);
}
void genotypeAndStrainFromKey(char *genotypeKey, struct sqlConnection *conn,
char **retGenotype, char **retStrain)
/* Return dynamically allocated string describing genotype */
{
int key = atoi(genotypeKey);
char *genotype = NULL, *strain = NULL;
if (key > 0)
{
struct dyString *query = dyStringNew(0);
struct dyString *geno = dyStringNew(256);
struct sqlResult *sr;
char **row;
/* Figure out genotype. Create string that looks something like:
* adh:cheap date,antp:+,
* That is a comma separated list gene:allele. */
sqlDyStringPrintf(query,
"select MRK_Marker.symbol,ALL_Allele.symbol "
"from GXD_AlleleGenotype,MRK_Marker,ALL_Allele "
"where GXD_AlleleGenotype._Genotype_key = %s "
"and GXD_AlleleGenotype._Marker_key = MRK_Marker._Marker_key "
"and GXD_AlleleGenotype._Allele_key = ALL_Allele._Allele_key "
, genotypeKey);
sr = sqlGetResultVerbose(conn, query->string);
while ((row = sqlNextRow(sr)) != NULL)
dyStringPrintf(geno, "%s:%s,", row[0], row[1]);
sqlFreeResult(&sr);
genotype = dyStringCannibalize(&geno);
/* Figure out strain */
dyStringClear(query);
sqlDyStringPrintf(query,
"select PRB_Strain.strain from GXD_Genotype,PRB_Strain "
"where GXD_Genotype._Genotype_key = %s "
"and GXD_Genotype._Strain_key = PRB_Strain._Strain_key"
, genotypeKey);
strain = sqlQuickStringVerbose(conn, query->string);
if (isUnknown(strain))
freez(&strain);
dyStringFree(&query);
}
if (genotype == NULL)
genotype = cloneString("");
if (strain == NULL)
strain = cloneString("");
*retGenotype = genotype;
*retStrain = strain;
}
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 queryInputTrackTable(struct dyString *query, char *inputTrackTable,
struct slName *fieldList)
/* Construct query in dyString to return contents of inputTrackTable ordered appropriately */
{
struct dyString *fields = dyStringNew(0);
struct slName *field;
sqlDyStringPrintf(query, "select tableName ");
for (field = fieldList; field != NULL; field = field->next)
sqlDyStringPrintfFrag(fields, ",%s", field->name);
sqlDyStringPrintf(query, "%-s from %s", fields->string, inputTrackTable);
if (fieldList != NULL)
// skip leading comma
dyStringPrintf(query, " order by %s", fields->string+1);
dyStringFree(&fields);
}
struct estOrientInfo *estOrientInfoLoadWhere(struct sqlConnection *conn, char *table, char *where)
/* Load all estOrientInfo from table that satisfy where clause. The
* where clause may be NULL in which case whole table is loaded
* Dispose of this with estOrientInfoFreeList(). */
{
struct estOrientInfo *list = NULL, *el;
struct dyString *query = dyStringNew(256);
struct sqlResult *sr;
char **row;
sqlDyStringPrintf(query, "select * from %s", table);
if (where != NULL)
dyStringPrintf(query, " where %s", where);
sr = sqlGetResult(conn, query->string);
int off = sqlFieldColumn(sr, "bin") + 1; // offset of data; function returns -1 if no bin
while ((row = sqlNextRow(sr)) != NULL)
{
el = estOrientInfoLoad(row+off);
slAddHead(&list, el);
}
slReverse(&list);
sqlFreeResult(&sr);
dyStringFree(&query);
return list;
}
void createTable(char *chromName)
/* create a chrN_snpTmp table */
{
struct sqlConnection *conn = hAllocConn();
char tableName[64];
char *createString =
"CREATE TABLE %s (\n"
" snp_id int(11) not null,\n"
" ctg_id int(11) not null,\n"
" chromName char(32) not null,\n"
" loc_type tinyint(4) not null,\n"
" phys_pos_from int(11) not null,\n"
" phys_pos varchar(32),\n"
" orientation tinyint(4) not null,\n"
" allele blob\n"
");\n";
struct dyString *dy = newDyString(1024);
safef(tableName, ArraySize(tableName), "chr%s_snpTmp", chromName);
sqlDyStringPrintf(dy, createString, tableName);
sqlRemakeTable(conn, tableName, dy->string);
dyStringFree(&dy);
hFreeConn(&conn);
}
void recreateDatabaseTable(char *chromName)
/* create a new chrN_snpTmp table with new definition */
{
struct sqlConnection *conn = hAllocConn();
char tableName[64];
char *createString =
"CREATE TABLE %s (\n"
" snp_id int(11) not null,\n"
" chromStart int(11) not null,\n"
" chromEnd int(11) not null,\n"
" loc_type tinyint(4) not null,\n"
" class varchar(255) not null,\n"
" orientation tinyint(4) not null,\n"
" fxn_class varchar(255) not null,\n"
" allele blob,\n"
" refUCSC blob,\n"
" refUCSCReverseComp blob,\n"
" observed blob,\n"
" weight int\n"
");\n";
struct dyString *dy = newDyString(1024);
safef(tableName, ArraySize(tableName), "%s_snpTmp", chromName);
sqlDyStringPrintf(dy, createString, tableName);
sqlRemakeTable(conn, tableName, dy->string);
dyStringFree(&dy);
hFreeConn(&conn);
}
void scoredRefTableCreate(struct sqlConnection *conn, char *tableName, int indexSize)
/* Create a scored-ref table with the given name. */
{
static char *createString =
"#High level information about a multiple alignment. Link to details in maf file.\n"
"CREATE TABLE %s (\n"
" bin smallint unsigned not null,\n"
" chrom varchar(255) not null, # Chromosome (this species)\n"
" chromStart int unsigned not null, # Start position in chromosome (forward strand)\n"
" chromEnd int unsigned not null, # End position in chromosome\n"
" extFile int unsigned not null, # Pointer to associated MAF file\n"
" offset bigint not null, # Offset in MAF file\n"
" score double not null, # Score\n"
" #Indices\n"
" INDEX(chrom(%d),bin)\n"
#ifdef OLD /* The other two indexes actually slow things down these days. */
" INDEX(chrom(%d),bin),\n"
" INDEX(chrom(%d),chromStart),\n"
" INDEX(chrom(%d),chromEnd)\n"
#endif /* OLD */
")\n";
struct dyString *dy = newDyString(1024);
sqlDyStringPrintf(dy, createString, tableName, indexSize, indexSize, indexSize);
sqlRemakeTable(conn, tableName, dy->string);
dyStringFree(&dy);
}
void constructQueryForEvent(struct dyString *query, char *skipPSet, char *tableName)
/* Construct a query for all the probe sets in a particular event for
a matrix table. */
{
int i = 0;
char eventQuery[256];
struct mouseAPSetEventMap *event = NULL;
struct sqlConnection *conn = hAllocConn();
sqlSafef(eventQuery, sizeof(eventQuery),
"select * from mouseAPSetEventMap where skipPSet = '%s';",
skipPSet);
/* Check our little cache. */
if(altEvent == NULL)
altEvent = event = mouseAPSetEventMapLoadByQuery(conn, eventQuery);
else
event = altEvent;
if(event == NULL)
errAbort("Couldn't find an alternative events for: %s", skipPSet);
assert(query);
dyStringClear(query);
sqlDyStringPrintf(query, "select * from %s where ", tableName);
for(i = 0; i < event->incCount; i++)
dyStringPrintf(query, "name like '%s' or ", event->incPSets[i]);
for(i = 0; i < event->geneCount; i++)
dyStringPrintf(query, "name like '%s' or ", event->genePSets[i]);
dyStringPrintf(query, "name like '%s' order by name;", skipPSet);
hFreeConn(&conn);
}
void fixMotif(struct dnaMotif *motif, int targetSize, char *motifTable, struct sqlConnection *conn)
/* Try and fix motif by trimming degenerate columns. */
{
struct dyString *dy = dyStringNew(0);
printf("Fixing %s in %s from:\n", motif->name, motifTable);
dnaMotifPrintProb(motif, uglyOut);
while (targetSize < motif->columnCount)
{
double startInfo = dnaMotifBitsOfInfo(motif, 0);
double endInfo = dnaMotifBitsOfInfo(motif, motif->columnCount-1);
motif->columnCount -= 1;
if (startInfo < endInfo)
{
memcpy(motif->aProb, motif->aProb+1, sizeof(motif->aProb[0]) * motif->columnCount);
memcpy(motif->cProb, motif->cProb+1, sizeof(motif->cProb[0]) * motif->columnCount);
memcpy(motif->gProb, motif->gProb+1, sizeof(motif->gProb[0]) * motif->columnCount);
memcpy(motif->tProb, motif->tProb+1, sizeof(motif->tProb[0]) * motif->columnCount);
}
}
printf("to:\n");
dnaMotifPrintProb(motif, uglyOut);
sqlDyStringPrintf(dy, "update %s set ", motifTable);
formatProb(dy, "aProb", motif->aProb, motif->columnCount);
formatProb(dy, "cProb", motif->cProb, motif->columnCount);
formatProb(dy, "gProb", motif->gProb, motif->columnCount);
formatProb(dy, "tProb", motif->tProb, motif->columnCount);
dyStringPrintf(dy, "columnCount=%d ", motif->columnCount);
dyStringPrintf(dy, "where name = '%s'", motif->name);
sqlUpdate(conn, dy->string);
dyStringFree(&dy);
}
int nextGapPos(char *chrom, int desiredPos, struct sqlConnection *conn)
{
/* Find next gap on the chrom and return midpoint */
struct sqlResult *sr;
char **row;
int pos = -1;
int start, end;
struct hTableInfo *hti = hFindTableInfo(db, chrom, "gap");
struct dyString *query = newDyString(1024);
if (hti == NULL)
errAbort("table %s.gap doesn't exist", db);
sqlDyStringPrintf(query, "select chromStart,chromEnd from ");
if (hti->isSplit)
dyStringPrintf(query, "%s_gap where ", chrom);
else
dyStringPrintf(query, "gap where %s='%s' AND ", hti->chromField, chrom);
dyStringPrintf(query, "(chromStart >= %d and chromEnd-chromStart > %d)\
order by chromStart limit 1",
desiredPos, minGap);
sr = sqlGetResult(conn, query->string);
freeDyString(&query);
if ((row = sqlNextRow(sr)) != NULL)
{
start = sqlSigned(row[0]);
end = sqlSigned(row[1]);
pos = start + (end - start)/2;
}
sqlFreeResult(&sr);
return pos;
}
static void addPrimaryIdsToHash(struct sqlConnection *conn, struct hash *hash,
char *idField, struct slName *tableList,
struct lm *lm, char *extraWhere)
/* For each table in tableList, query all idField values and add to hash,
* id -> uppercased id for case-insensitive matching. */
{
struct slName *table;
struct sqlResult *sr;
char **row;
struct dyString *query = dyStringNew(0);
for (table = tableList; table != NULL; table = table->next)
{
dyStringClear(query);
sqlDyStringPrintf(query, "select %s from %s", idField, table->name);
if (extraWhere != NULL)
dyStringPrintf(query, " where %s", extraWhere);
sr = sqlGetResult(conn, query->string);
while ((row = sqlNextRow(sr)) != NULL)
{
if (isNotEmpty(row[0]))
{
char *origCase = lmCloneString(lm, row[0]);
touppers(row[0]);
hashAdd(hash, row[0], origCase);
}
}
sqlFreeResult(&sr);
}
}
void chainDbAddBlocks(struct chain *chain, char *track, struct sqlConnection *conn)
/* Add blocks to chain header. */
{
struct dyString *query = newDyString(1024);
struct sqlResult *sr = NULL;
char **row;
struct cBlock *b;
char fullName[64];
safef(fullName, sizeof(fullName), "%s_%s", chain->tName, track);
if (!sqlTableExists(conn, fullName))
strcpy(fullName, track);
sqlDyStringPrintf(query,
"select tStart,tEnd,qStart from %sLink where chainId = %d",fullName, chain->id);
sr = sqlGetResult(conn, query->string);
while ((row = sqlNextRow(sr)) != NULL)
{
AllocVar(b);
b->tStart = sqlUnsigned(row[0]);
b->tEnd = sqlUnsigned(row[1]);
b->qStart = sqlUnsigned(row[2]);
b->qEnd = b->qStart + (b->tEnd - b->tStart);
slAddHead(&chain->blockList, b);
}
slReverse(&chain->blockList);
sqlFreeResult(&sr);
dyStringFree(&query);
}
void saveClonePos(struct clonePos *cloneList, char *database)
/* Save sorted clone position list to database. */
{
struct sqlConnection *conn = sqlConnect(database);
struct clonePos *clone;
struct tempName tn;
FILE *f;
struct dyString *ds = newDyString(2048);
/* Create tab file from clone list. */
printf("Creating tab file\n");
makeTempName(&tn, "hgCP", ".tab");
f = mustOpen(tn.forCgi, "w");
for (clone = cloneList; clone != NULL; clone = clone->next)
clonePosTabOut(clone, f);
fclose(f);
/* Create table if it doesn't exist, delete whatever is
* already in it, and fill it up from tab file. */
printf("Loading clonePos table\n");
sqlMaybeMakeTable(conn, "clonePos", createClonePos);
sqlUpdate(conn, "NOSQLINJ DELETE from clonePos");
sqlDyStringPrintf(ds, "LOAD data local infile '%s' into table clonePos",
tn.forCgi);
sqlUpdate(conn, ds->string);
/* Clean up. */
remove(tn.forCgi);
sqlDisconnect(&conn);
}
struct dyString *readAndReplaceTableName(char *fileName, char *table)
/* Read file into string. While doing so strip any leading comments
* and insist that the first non-comment line contain the words
* "create table" followed by a table name. Replace the table name,
* and copy the rest of the file verbatem. */
{
struct lineFile *lf = lineFileOpen(fileName, TRUE);
struct dyString *dy = dyStringNew(0);
char *line, *word;
if (!lineFileNextReal(lf, &line))
errAbort("No real lines in %s\n", fileName);
word = nextWord(&line);
if (!sameWord(word, "create"))
errAbort("Expecting first word in file to be CREATE. Got %s", word);
word = nextWord(&line);
if (word == NULL || !sameWord(word, "table"))
errAbort("Expecting second word in file to be table. Got %s", emptyForNull(word));
word = nextWord(&line);
if (word == NULL)
errAbort("Expecting table name on same line as CREATE TABLE");
sqlDyStringPrintf(dy, "CREATE TABLE %s ", table);
if (line != NULL)
dyStringAppend(dy, line);
dyStringAppendC(dy, '\n');
while (lineFileNext(lf, &line, NULL))
{
dyStringAppend(dy, line);
dyStringAppendC(dy, '\n');
}
lineFileClose(&lf);
return dy;
}
int nextRepeatPos(char *chrom, int desiredPos, struct sqlConnection *conn)
/* Find next 0% diverged repeat on the chrom and return midpoint */
{
struct sqlResult *sr;
char **row;
int pos = -1;
int start, end;
struct hTableInfo *hti = hFindTableInfo(db, chrom, "rmsk");
struct dyString *query = newDyString(1024);
if (hti == NULL)
errAbort("table %s.rmsk doesn't exist", db);
sqlDyStringPrintf(query, "select genoStart,genoEnd from ");
if (hti->isSplit)
dyStringPrintf(query, "%s_rmsk where ", chrom);
else
dyStringPrintf(query, "rmsk where %s='%s' AND ", hti->chromField, chrom);
dyStringPrintf(query,
"(genoStart >= %d AND \
milliDiv=0 AND \
repClass<>'Simple_repeat' AND repClass<>'Low_complexity' AND \
genoEnd-genoStart>%d) order by genoStart limit 1",
desiredPos, minRepeat);
sr = sqlGetResult(conn, query->string);
freeDyString(&query);
if ((row = sqlNextRow(sr)) != NULL)
{
start = sqlSigned(row[0]);
end = sqlSigned(row[1]);
pos = start + (end - start)/2;
}
sqlFreeResult(&sr);
return pos;
}
void saveCtgPos(struct ctgPos *ctgList, char *database)
/* Save ctgList to database. */
{
struct sqlConnection *conn = sqlConnect(database);
struct ctgPos *ctg;
char *tabFileName = "ctgPos.tab";
FILE *f;
struct dyString *ds = newDyString(2048);
/* Create tab file from ctg list. */
printf("Creating tab file\n");
f = mustOpen(tabFileName, "w");
for (ctg = ctgList; ctg != NULL; ctg = ctg->next)
ctgPosTabOut(ctg, f);
fclose(f);
/* Create table if it doesn't exist, delete whatever is
* already in it, and fill it up from tab file. */
printf("Loading ctgPos table\n");
sqlMaybeMakeTable(conn, "ctgPos", createCtgPos);
sqlUpdate(conn, "NOSQLINJ DELETE from ctgPos");
sqlDyStringPrintf(ds, "LOAD data local infile '%s' into table ctgPos",
tabFileName);
sqlUpdate(conn, ds->string);
/* Clean up. */
remove(tabFileName);
sqlDisconnect(&conn);
}
struct spFeature *spFeatures(struct sqlConnection *conn, char *acc,
int classId, /* Feature class ID, 0 for all */
int typeId) /* Feature type ID, 0 for all */
/* Get feature list. slFreeList this when done. */
{
struct dyString *dy = dyStringNew(0);
struct spFeature *list = NULL, *el;
char **row;
struct sqlResult *sr;
sqlDyStringAppend(dy,
"select start,end,featureClass,featureType,softEndBits from feature ");
sqlDyStringPrintf(dy,
"where acc = '%s'", acc);
if (classId != 0)
dyStringPrintf(dy, " and featureClass=%d", classId);
if (typeId != 0)
dyStringPrintf(dy, " and featureType=%d", typeId);
sr = sqlGetResult(conn, dy->string);
while ((row = sqlNextRow(sr)) != NULL)
{
AllocVar(el);
el->start = sqlUnsigned(row[0]);
el->end = sqlUnsigned(row[1]);
el->featureClass = sqlUnsigned(row[2]);
el->featureType = sqlUnsigned(row[3]);
el->softEndBits = sqlUnsigned(row[4]);
slAddHead(&list, el);
}
sqlFreeResult(&sr);
dyStringFree(&dy);
slReverse(&list);
return list;
}
void snpTmpTableCreate(struct sqlConnection *conn, char *tableName)
/* create a chrN_snpTmp table */
{
char *createString =
"CREATE TABLE %s (\n"
" chrom varchar(255) not null,\n"
" chromStart int unsigned not null,\n"
" chromEnd int unsigned not null,\n"
" name varchar(255) not null,\n"
" strand char(1) not null,\n"
" refNCBI longblob not null,\n"
" locType enum('unknown', 'range', 'exact', 'between',\n"
" 'rangeInsertion', 'rangeSubstitution', 'rangeDeletion') \n"
" DEFAULT 'unknown' NOT NULL,\n"
" func set( 'unknown', 'locus', 'coding', 'coding-synon', 'coding-nonsynon', \n"
" 'untranslated', 'intron', 'splice-site', 'cds-reference') \n"
" DEFAULT 'unknown' NOT NULL,\n"
" # The functional category of the SNP\n"
" contigName varchar(255) not null,\n"
" INDEX name(name)\n"
")\n";
struct dyString *dy = newDyString(512);
sqlDyStringPrintf(dy, createString, tableName);
sqlRemakeTable(conn, tableName, dy->string);
dyStringFree(&dy);
}
