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 edwUpdateFileTags(struct sqlConnection *conn, long long fileId, struct dyString *tags)
/* Update tags field in edwFile with given value */
{
struct dyString *query = dyStringNew(0);
sqlDyStringAppend(query, "update edwFile set tags='");
dyStringAppend(query, tags->string);
dyStringPrintf(query, "' where id=%lld", fileId);
sqlUpdate(conn, query->string);
dyStringFree(&query);
}
int makeNewEmptySubmitRecord(struct sqlConnection *conn, char *submitUrl, unsigned userId)
/* Create a submit record around URL and return it's id. */
{
struct dyString *query = dyStringNew(0);
sqlDyStringAppend(query, "insert edwSubmit (url, startUploadTime, userId) ");
sqlDyStringPrintf(query, "VALUES('%s', %lld, %d)", submitUrl, edwNow(), userId);
sqlUpdate(conn, query->string);
dyStringFree(&query);
return sqlLastAutoId(conn);
}
int makeNewEmptyFileRecord(struct sqlConnection *conn, unsigned submitId, unsigned submitDirId,
char *submitFileName, long long size)
/* Make a new, largely empty, record around file and submit info. */
{
struct dyString *query = dyStringNew(0);
sqlDyStringAppend(query, "insert edwFile (submitId, submitDirId, submitFileName, size) ");
dyStringPrintf(query, "VALUES(%u, %u, '%s', %lld)", submitId, submitDirId, submitFileName, size);
sqlUpdate(conn, query->string);
dyStringFree(&query);
return sqlLastAutoId(conn);
}
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 visiSearcherWeedResults(struct visiSearcher *searcher,
struct sqlConnection *conn)
/* Get rid of images that are just partial matches, and also
* images that are private. This leaks a little memory - the
* matches that are weeded out.*/
{
struct visiMatch *newList = NULL, *match, *next, key;
int wordCount = searcher->wordCount;
struct dyString *query = dyStringNew(0);
struct sqlResult *sr;
char **row;
int passCount = 0;
/* Construct query to fetch all non-private imageId's in matchList. */
sqlDyStringAppend(query, "select image.id from image,submissionSet "
"where submissionSet.privateUser = 0 "
"and submissionSet.id = image.submissionSet "
"and image.id in (");
for (match = searcher->matchList; match != NULL; match = next)
{
next = match->next;
if (bitCountRange(match->wordBits, 0, wordCount) == wordCount)
{
if (passCount != 0)
dyStringAppendC(query, ',');
dyStringPrintf(query, "%d", match->imageId);
++passCount;
}
}
dyStringAppendC(query, ')');
/* Execute query, and put corresponding images on newList. */
if (passCount > 0)
{
sr = sqlGetResult(conn, query->string);
while ((row = sqlNextRow(sr)) != NULL)
{
key.imageId = sqlUnsigned(row[0]);
match = rbTreeFind(searcher->tree, &key);
if (match == NULL)
internalErr();
slAddHead(&newList, match);
}
slReverse(&newList);
}
searcher->matchList = newList;
dyStringFree(&query);
}
static void visiGeneMatchSex(struct visiSearcher *searcher,
struct sqlConnection *conn, struct slName *wordList)
/* Add images matching bodyPart to searcher.
* This is a little complicated by some body parts containing
* multiple words, like "choroid plexus". */
{
struct dyString *query = dyStringNew(0);
struct slName *word;
int wordIx;
for (word = wordList, wordIx=0; word != NULL; word = word->next, ++wordIx)
{
dyStringClear(query);
sqlDyStringAppend(query, "select image.id from sex,specimen,image ");
sqlDyStringPrintf(query, "where sex.name = \"%s\" ", word->name);
dyStringAppend(query, "and sex.id = specimen.sex ");
dyStringAppend(query, "and specimen.id = image.specimen");
addImagesMatchingQuery(searcher, conn, query->string, NULL, NULL,
wordIx, 1);
}
}
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);
}
void bioImageLoad(char *setRaFile, char *itemTabFile)
/* bioImageLoad - Load data into bioImage database. */
{
struct hash *raHash = raReadSingle(setRaFile);
struct hash *rowHash;
struct lineFile *lf = lineFileOpen(itemTabFile, TRUE);
char *line, *words[256];
struct sqlConnection *conn = sqlConnect(database);
int rowSize;
int submissionSetId;
struct hash *fullDirHash = newHash(0);
struct hash *screenDirHash = newHash(0);
struct hash *thumbDirHash = newHash(0);
struct hash *treatmentHash = newHash(0);
struct hash *bodyPartHash = newHash(0);
struct hash *sliceTypeHash = newHash(0);
struct hash *imageTypeHash = newHash(0);
struct hash *sectionSetHash = newHash(0);
struct dyString *dy = dyStringNew(0);
/* Read first line of tab file, and from it get all the field names. */
if (!lineFileNext(lf, &line, NULL))
errAbort("%s appears to be empty", lf->fileName);
if (line[0] != '#')
errAbort("First line of %s needs to start with #, and then contain field names",
lf->fileName);
rowHash = hashRowOffsets(line+1);
rowSize = rowHash->elCount;
if (rowSize >= ArraySize(words))
errAbort("Too many fields in %s", lf->fileName);
/* Check that have all required fields */
{
char *fieldName;
int i;
for (i=0; i<ArraySize(requiredSetFields); ++i)
{
fieldName = requiredSetFields[i];
if (!hashLookup(raHash, fieldName))
errAbort("Field %s is not in %s", fieldName, setRaFile);
}
for (i=0; i<ArraySize(requiredItemFields); ++i)
{
fieldName = requiredItemFields[i];
if (!hashLookup(rowHash, fieldName))
errAbort("Field %s is not in %s", fieldName, itemTabFile);
}
for (i=0; i<ArraySize(requiredFields); ++i)
{
fieldName = requiredFields[i];
if (!hashLookup(rowHash, fieldName) && !hashLookup(raHash, fieldName))
errAbort("Field %s is not in %s or %s", fieldName, setRaFile, itemTabFile);
}
}
/* Create/find submission record. */
submissionSetId = saveSubmissionSet(conn, raHash);
/* Process rest of tab file. */
while (lineFileNextRowTab(lf, words, rowSize))
{
int fullDir = cachedId(conn, "location", "name",
fullDirHash, "fullDir", raHash, rowHash, words);
int screenDir = cachedId(conn, "location", "name",
screenDirHash, "screenDir", raHash, rowHash, words);
int thumbDir = cachedId(conn, "location",
"name", thumbDirHash, "thumbDir", raHash, rowHash, words);
int bodyPart = cachedId(conn, "bodyPart",
"name", bodyPartHash, "bodyPart", raHash, rowHash, words);
int sliceType = cachedId(conn, "sliceType",
"name", sliceTypeHash, "sliceType", raHash, rowHash, words);
int imageType = cachedId(conn, "imageType",
"name", imageTypeHash, "imageType", raHash, rowHash, words);
int treatment = cachedId(conn, "treatment",
"conditions", treatmentHash, "treatment", raHash, rowHash, words);
char *fileName = getVal("fileName", raHash, rowHash, words, NULL);
char *submitId = getVal("submitId", raHash, rowHash, words, NULL);
char *taxon = getVal("taxon", raHash, rowHash, words, NULL);
char *isEmbryo = getVal("isEmbryo", raHash, rowHash, words, NULL);
char *age = getVal("age", raHash, rowHash, words, NULL);
char *sectionSet = getVal("sectionSet", raHash, rowHash, words, "");
char *sectionIx = getVal("sectionIx", raHash, rowHash, words, "0");
char *gene = getVal("gene", raHash, rowHash, words, "");
char *locusLink = getVal("locusLink", raHash, rowHash, words, "");
char *refSeq = getVal("refSeq", raHash, rowHash, words, "");
char *genbank = getVal("genbank", raHash, rowHash, words, "");
char *priority = getVal("priority", raHash, rowHash, words, "200");
int sectionId = 0;
int oldId;
// char *xzy = getVal("xzy", raHash, rowHash, words, xzy);
if (sectionSet[0] != 0 && !sameString(sectionSet, "0"))
{
struct hashEl *hel = hashLookup(sectionSetHash, sectionSet);
if (hel != NULL)
sectionId = ptToInt(hel->val);
else
{
sqlUpdate(conn, "NOSQLINJ insert into sectionSet values(default)");
sectionId = sqlLastAutoId(conn);
hashAdd(sectionSetHash, sectionSet, intToPt(sectionId));
}
}
dyStringClear(dy);
sqlDyStringAppend(dy, "select id from image ");
dyStringPrintf(dy, "where fileName = '%s' ", fileName);
dyStringPrintf(dy, "and fullLocation = %d", fullDir);
oldId = sqlQuickNum(conn, dy->string);
if (oldId != 0)
{
if (replace)
{
dyStringClear(dy);
sqlDyStringPrintf(dy, "delete from image where id = %d", oldId);
sqlUpdate(conn, dy->string);
}
else
errAbort("%s is already in database line %d of %s",
fileName, lf->lineIx, lf->fileName);
}
dyStringClear(dy);
sqlDyStringAppend(dy, "insert into image set\n");
dyStringPrintf(dy, " id = default,\n");
dyStringPrintf(dy, " fileName = '%s',\n", fileName);
dyStringPrintf(dy, " fullLocation = %d,\n", fullDir);
dyStringPrintf(dy, " screenLocation = %d,\n", screenDir);
dyStringPrintf(dy, " thumbLocation = %d,\n", thumbDir);
dyStringPrintf(dy, " submissionSet = %d,\n", submissionSetId);
dyStringPrintf(dy, " sectionSet = %d,\n", sectionId);
dyStringPrintf(dy, " sectionIx = %s,\n", sectionIx);
dyStringPrintf(dy, " submitId = '%s',\n", submitId);
dyStringPrintf(dy, " gene = '%s',\n", gene);
dyStringPrintf(dy, " locusLink = '%s',\n", locusLink);
dyStringPrintf(dy, " refSeq = '%s',\n", refSeq);
dyStringPrintf(dy, " genbank = '%s',\n", genbank);
dyStringPrintf(dy, " priority = %s,\n", priority);
dyStringPrintf(dy, " taxon = %s,\n", taxon);
dyStringPrintf(dy, " isEmbryo = %s,\n", isEmbryo);
dyStringPrintf(dy, " age = %s,\n", age);
dyStringPrintf(dy, " bodyPart = %d,\n", bodyPart);
dyStringPrintf(dy, " sliceType = %d,\n", sliceType);
dyStringPrintf(dy, " imageType = %d,\n", imageType);
dyStringPrintf(dy, " treatment = %d\n", treatment);
sqlUpdate(conn, dy->string);
}
}
