int createSubmissionId(struct sqlConnection *conn,
char *contributors, char *publication,
char *pubUrl, char *setUrl, char *itemUrl)
/* Add submission and contributors to database and return submission ID */
{
struct slName *slNameListFromString(char *s, char delimiter);
struct slName *contribList = NULL, *contrib;
int submissionSetId;
char query[1024];
safef(query, sizeof(query),
"insert into submissionSet "
"values(default, \"%s\", \"%s\", '%s', '%s', '%s')",
contributors, publication, pubUrl, setUrl, itemUrl);
sqlUpdate(conn, query);
submissionSetId = sqlLastAutoId(conn);
contribList = slNameListFromComma(contributors);
for (contrib = contribList; contrib != NULL; contrib = contrib->next)
{
int contribId = findOrAddIdTable(conn, "contributor", "name",
skipLeadingSpaces(contrib->name));
safef(query, sizeof(query),
"insert into submissionContributor values(%d, %d)",
submissionSetId, contribId);
sqlUpdate(conn, query);
}
slFreeList(&contribList);
return submissionSetId;
}
void gensatFixFull(char *captionFile)
/* Fix missing captions. */
{
struct lineFile *lf = lineFileOpen(captionFile, TRUE);
char *row[2];
struct dyString *sql = dyStringNew(0);
struct sqlConnection *conn = sqlConnect(database);
struct hash *capHash = newHash(16);
while (lineFileRowTab(lf, row))
{
int captionId;
char *submitId = row[0];
char *caption = row[1];
captionId = hashIntValDefault(capHash, caption, 0);
if (captionId == 0)
{
dyStringClear(sql);
dyStringAppend(sql, "insert into caption values(default, \"");
dyStringAppend(sql, caption);
dyStringAppend(sql, "\")");
sqlUpdate(conn, sql->string);
verbose(1, "%s\n", sql->string);
captionId = sqlLastAutoId(conn);
hashAddInt(capHash, caption, captionId);
}
dyStringClear(sql);
dyStringPrintf(sql, "update imageFile set caption=%d ", captionId);
dyStringPrintf(sql, "where submissionSet=%d ", gensatId);
dyStringPrintf(sql, "and submitId = \"%s\"", submitId);
sqlUpdate(conn, sql->string);
verbose(1, "%s\n", sql->string);
}
dyStringFree(&sql);
}
int dummyInsert(struct sqlConnection *conn, char *table)
/* Insert new row into cart table and return ID */
{
char query[256];
sqlSafef(query, sizeof(query), "INSERT %s VALUES(0,\"\",0,now(),now(),0)",
table);
sqlUpdate(conn, query);
return sqlLastAutoId(conn);
}
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 int getLocalHost(struct sqlConnection *conn)
/* Make up record for local host if it is not there already. */
{
char query[256];
sqlSafef(query, sizeof(query), "select id from edwHost where name = '%s'", localHostName);
int hostId = sqlQuickNum(conn, query);
if (hostId == 0)
{
sqlSafef(query, sizeof(query), "insert edwHost(name, firstAdded) values('%s', %lld)",
localHostName, edwNow());
sqlUpdate(conn, query);
hostId = sqlLastAutoId(conn);
}
return hostId;
}
int edwGetHost(struct sqlConnection *conn, char *hostName)
/* Look up host name in table and return associated ID. If not found
* make up new table entry. */
{
/* If it's already in table, just return ID. */
char query[512];
sqlSafef(query, sizeof(query), "select id from edwHost where name='%s'", hostName);
int hostId = sqlQuickNum(conn, query);
if (hostId > 0)
return hostId;
sqlSafef(query, sizeof(query), "insert edwHost (name, firstAdded, paraFetchStreams) values('%s', %lld, 10)",
hostName, edwNow());
sqlUpdate(conn, query);
return sqlLastAutoId(conn);
}
static int getLocalSubmit(struct sqlConnection *conn)
/* Get the submission that covers all of our local additions. */
{
int dirId = getLocalSubmitDir(conn);
char query[256];
sqlSafef(query, sizeof(query), "select id from edwSubmit where submitDirId='%d'", dirId);
int submitId = sqlQuickNum(conn, query);
if (submitId == 0)
{
sqlSafef(query, sizeof(query), "insert edwSubmit (submitDirId,startUploadTime) values(%d,%lld)",
dirId, edwNow());
sqlUpdate(conn, query);
submitId = sqlLastAutoId(conn);
}
return submitId;
}
int edwGetSubmitDir(struct sqlConnection *conn, int hostId, char *submitDir)
/* Get submitDir from database, creating it if it doesn't already exist. */
{
/* If it's already in table, just return ID. */
char query[512];
sqlSafef(query, sizeof(query), "select id from edwSubmitDir where url='%s'", submitDir);
int dirId = sqlQuickNum(conn, query);
if (dirId > 0)
return dirId;
sqlSafef(query, sizeof(query),
"insert edwSubmitDir (url, firstAdded, hostId) values('%s', %lld, %d)",
submitDir, edwNow(), hostId);
sqlUpdate(conn, query);
return sqlLastAutoId(conn);
}
static int getLocalSubmitDir(struct sqlConnection *conn)
/* Get submit dir for local submissions, making it up if it does not exist. */
{
int hostId = getLocalHost(conn);
char query[256];
sqlSafef(query, sizeof(query), "select id from edwSubmitDir where url='%s' and hostId=%d",
localHostDir, hostId);
int dirId = sqlQuickNum(conn, query);
if (dirId == 0)
{
sqlSafef(query, sizeof(query), "insert edwSubmitDir(url,hostId,firstAdded) values('%s',%d,%lld)",
localHostDir, hostId, edwNow());
sqlUpdate(conn, query);
dirId = sqlLastAutoId(conn);
}
return dirId;
}
int findOrAddIdTable(struct sqlConnection *conn, char *table, char *field,
char *value)
/* Get ID associated with field.value in table. */
{
char query[256];
int id;
safef(query, sizeof(query), "select id from %s where %s = \"%s\"",
table, field, value);
id = sqlQuickNum(conn, query);
if (id == 0)
{
safef(query, sizeof(query), "insert into %s values(default, \"%s\")",
table, value);
sqlUpdate(conn, query);
id = sqlLastAutoId(conn);
}
return id;
}
struct edwFile *edwGetLocalFile(struct sqlConnection *conn, char *localAbsolutePath,
char *symLinkMd5Sum)
/* Get record of local file from database, adding it if it doesn't already exist.
* Can make it a symLink rather than a copy in which case pass in valid MD5 sum
* for symLinkM5dSum. */
{
/* First do a reality check on the local absolute path. Is there a file there? */
if (localAbsolutePath[0] != '/')
errAbort("Using relative path in edwAddLocalFile.");
long long size = fileSize(localAbsolutePath);
if (size == -1)
errAbort("%s does not exist", localAbsolutePath);
long long updateTime = fileModTime(localAbsolutePath);
/* Get file if it's in database already. */
int submitDirId = getLocalSubmitDir(conn);
int submitId = getLocalSubmit(conn);
char query[256+PATH_LEN];
sqlSafef(query, sizeof(query), "select * from edwFile where submitId=%d and submitFileName='%s'",
submitId, localAbsolutePath);
struct edwFile *ef = edwFileLoadByQuery(conn, query);
/* If we got something in database, check update time and size, and if it's no change just
* return existing database id. */
if (ef != NULL && ef->updateTime == updateTime && ef->size == size)
return ef;
/* If we got here, then we need to make a new file record. Start with pretty empty record
* that just has file ID, submitted file name and a few things*/
sqlSafef(query, sizeof(query),
"insert edwFile (submitId,submitDirId,submitFileName,startUploadTime) "
" values(%d, %d, '%s', %lld)"
, submitId, submitDirId, localAbsolutePath, edwNow());
sqlUpdate(conn, query);
long long fileId = sqlLastAutoId(conn);
/* Create big data warehouse file/path name. */
char edwFile[PATH_LEN], edwPath[PATH_LEN];
edwMakeFileNameAndPath(fileId, localAbsolutePath, edwFile, edwPath);
/* We're a little paranoid so md5 it */
char *md5;
/* Do copy or symbolic linking of file into warehouse managed dir. */
if (symLinkMd5Sum)
{
md5 = symLinkMd5Sum;
makeSymLink(localAbsolutePath, edwPath);
}
else
{
copyFile(localAbsolutePath, edwPath);
md5 = md5HexForFile(localAbsolutePath);
}
/* Update file record. */
sqlSafef(query, sizeof(query),
"update edwFile set edwFileName='%s', endUploadTime=%lld,"
"updateTime=%lld, size=%lld, md5='%s' where id=%lld"
, edwFile, edwNow(), updateTime, size, md5, fileId);
sqlUpdate(conn, query);
/* Now, it's a bit of a time waste, but cheap in code, to just load it back from DB. */
sqlSafef(query, sizeof(query), "select * from edwFile where id=%lld", fileId);
return edwFileLoadByQuery(conn, 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, "insert into sectionSet values(default)");
sectionId = sqlLastAutoId(conn);
hashAdd(sectionSetHash, sectionSet, intToPt(sectionId));
}
}
dyStringClear(dy);
dyStringAppend(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);
dyStringPrintf(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);
dyStringAppend(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);
}
}
static void populateMissingVgPrb(struct sqlConnection *conn)
/* populate vgPrb where missing, usually after new records added to visiGene */
{
struct sqlResult *sr;
char **row;
struct dyString *dy = dyStringNew(0);
struct sqlConnection *conn2 = sqlConnect(database);
struct sqlConnection *conn3 = sqlConnect(database);
int probeCount=0, vgPrbCount=0;
dyStringAppend(dy,
"select p.id,p.gene,antibody,probeType,fPrimer,rPrimer,p.seq,bac,g.taxon"
" from probe p join gene g"
" left join vgPrbMap m on m.probe = p.id"
" where g.id = p.gene"
" and m.probe is NULL");
sr = sqlGetResult(conn, dy->string);
while ((row = sqlNextRow(sr)) != NULL)
{
int id = sqlUnsigned(row[0]);
/* int gene = sqlUnsigned(row[1]); */
/* int antibody = sqlUnsigned(row[2]); */
/* int probeType = sqlUnsigned(row[3]); */
char *fPrimer = row[4];
char *rPrimer = row[5];
char *seq = row[6];
int bac = sqlUnsigned(row[7]);
int taxon = sqlUnsigned(row[8]);
char *peType = "none";
int peProbe = id;
char *peSeq = seq;
char *tName = "";
int tStart = 0;
int tEnd = 0;
char *tStrand = " ";
/*
char *peGene = "";
int bacInfo = 0;
int seqid = 0;
int pslid = 0;
*/
char *state = "new";
char *db = "";
int vgPrb = 0;
if (isNotEmpty(seq))
{
peType = "probe";
state = "seq";
}
else if (isNotEmpty(fPrimer) && isNotEmpty(rPrimer))
{
peType = "primersMrna";
}
else if (isNotEmpty(fPrimer) && isEmpty(rPrimer))
{ /* only have fPrimer, it's probably a comment, not dna seq */
peType = "refSeq"; /* use accession or gene */
}
else if (bac > 0)
{
peType = "bac"; /* use bacEndPairs */
}
else
{
peType = "refSeq"; /* use accession or gene */
}
if (!sameString(peSeq,""))
{
vgPrb = findVgPrbBySeq(conn3,peSeq,taxon);
}
if (vgPrb == 0)
{
dyStringClear(dy);
dyStringAppend(dy, "insert into vgPrb set");
dyStringPrintf(dy, " id=default,\n");
dyStringPrintf(dy, " type='%s',\n", peType);
dyStringAppend(dy, " seq='");
dyStringAppend(dy, peSeq);
dyStringAppend(dy, "',\n");
dyStringPrintf(dy, " tName='%s',\n", tName);
dyStringPrintf(dy, " tStart=%d,\n", tStart);
dyStringPrintf(dy, " tEnd=%d,\n", tEnd);
dyStringPrintf(dy, " tStrand='%s',\n", tStrand);
dyStringPrintf(dy, " db='%s',\n", db);
dyStringPrintf(dy, " taxon='%d',\n", taxon);
dyStringPrintf(dy, " state='%s'\n", state);
verbose(2, "%s\n", dy->string);
sqlUpdate(conn2, dy->string);
vgPrb = sqlLastAutoId(conn2);
vgPrbCount++;
}
dyStringClear(dy);
dyStringAppend(dy, "insert into vgPrbMap set");
dyStringPrintf(dy, " probe=%d,\n", peProbe);
dyStringPrintf(dy, " vgPrb=%d \n", vgPrb);
verbose(2, "%s\n", dy->string);
sqlUpdate(conn2, dy->string);
probeCount++;
}
verbose(1, "# new probe records found = %d, # new vgPrb records added = %d\n", probeCount, vgPrbCount);
dyStringFree(&dy);
sqlFreeResult(&sr);
sqlDisconnect(&conn3);
sqlDisconnect(&conn2);
}