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);
dyStringPrintf(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);
dyStringPrintf(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);
}
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;
dyStringClear(dy);
dyStringPrintf(dy,
"select age from lifeStage where lifeStageScheme = %d ",
schemeId);
dyStringPrintf(dy,
"and age > %s order by age", minAge);
maxAge = sqlQuickString(conn, dy->string);
dyStringClear(dy);
dyStringPrintf(dy, "select image.id from specimen,image ");
dyStringPrintf(dy, "where specimen.age >= %s ", minAge);
if (maxAge != NULL)
dyStringPrintf(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);
}
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 addImagesMatchingYears(struct visiSearcher *searcher,
struct sqlConnection *conn, int minYear, int maxYear, int wordIx)
/* Fold in images that are published between given years */
{
struct dyString *dy = dyStringNew(0);
dyStringPrintf(dy,
"select image.id from submissionSet,imageFile,image "
"where submissionSet.year >= %d and submissionSet.year <= %d "
"and submissionSet.id = imageFile.submissionSet "
"and imageFile.id = image.imageFile"
, minYear, maxYear);
addImagesMatchingQuery(searcher, conn, dy->string, NULL, NULL,
wordIx, 1);
dyStringFree(&dy);
}
static void addImagesMatchingCommonName(struct visiSearcher *searcher,
struct sqlConnection *conn, struct dyString *dy, char *commonName,
int startWord, int wordCount)
/* Add images that match common name. */
{
dyStringClear(dy);
dyStringPrintf(dy,
"select distinct image.id from "
"image,specimen,uniProt.commonName "
"where uniProt.commonName.val = \"%s\" "
"and specimen.taxon = uniProt.commonName.taxon "
"and image.specimen = specimen.id"
, commonName);
addImagesMatchingQuery(searcher, conn, dy->string, NULL, commonName,
startWord, wordCount);
}
static void addImagesMatchingBinomial(struct visiSearcher *searcher,
struct sqlConnection *conn, struct dyString *dy, char *binomial,
int startWord, int wordCount)
/* Add images that match binomial name. */
{
dyStringClear(dy);
sqlDyStringPrintf(dy,
"select distinct image.id from "
"image,specimen,uniProt.taxon "
"where uniProt.taxon.binomial = \"%s\" "
"and specimen.taxon = uniProt.taxon.id "
"and image.specimen = specimen.id"
, binomial);
addImagesMatchingQuery(searcher, conn, dy->string, NULL, binomial,
startWord, wordCount);
}
static void addImagesMatchingName(struct visiSearcher *searcher,
struct sqlConnection *conn, struct dyString *dy, char *contributor,
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. */
{
dyStringClear(dy);
dyStringPrintf(dy,
"select image.id from "
"contributor,submissionContributor,imageFile,image "
"where contributor.name = \"%s\" "
"and contributor.id = submissionContributor.contributor "
"and submissionContributor.submissionSet = imageFile.submissionSet "
"and imageFile.id = image.imageFile"
, contributor);
addImagesMatchingQuery(searcher, conn, dy->string, NULL, NULL,
startWord, wordCount);
}
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);
dyStringAppend(query, "select image.id from sex,specimen,image ");
dyStringPrintf(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);
}
}
