Пример #1
0
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;
}
Пример #2
0
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);
}
Пример #3
0
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);
}
Пример #4
0
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);
}
Пример #5
0
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;
}
Пример #6
0
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);
}
Пример #7
0
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);
    } 
}
Пример #8
0
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);
}
Пример #9
0
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);
    }
}