void recordIntoHistory(struct sqlConnection *conn, unsigned id, char *table, boolean success)
/* Record success/failure into uploadAttempts and historyBits fields of table. */
{
/* Get historyBits and fold status into it. */
char quickResult[32];
char query[256];
sqlSafef(query, sizeof(query), "select historyBits from %s where id=%u", table, id);
if (sqlQuickQuery(conn, query, quickResult, sizeof(quickResult)) == NULL)
internalErr();
char *lastTimeField;
char *openResultField;
long long historyBits = sqlLongLong(quickResult);
historyBits <<= 1;
if (success)
{
historyBits |= 1;
lastTimeField = "lastOkTime";
openResultField = "openSuccesses";
}
else
{
lastTimeField = "lastNotOkTime";
openResultField = "openFails";
}
sqlSafef(query, sizeof(query),
"update %s set historyBits=%lld, %s=%s+1, %s=%lld "
"where id=%lld",
table, historyBits, openResultField, openResultField, lastTimeField, edwNow(),
(long long)id);
sqlUpdate(conn, query);
}
char* lookupName( struct sqlConnection *conn , char *id)
{
char *name = cloneString(id);
char infoTable[128];
safef(infoTable, sizeof(infoTable), "%sInfo","bdgpGene");
if (hTableExists(infoTable))
{
struct sqlConnection *conn = hAllocConn();
char *symbol = NULL;
char *ptr = strchr(name, '-');
char query[256];
char buf[64];
if (ptr != NULL)
*ptr = 0;
safef(query, sizeof(query),
"select symbol from %s where bdgpName = '%s';", infoTable, name);
symbol = sqlQuickQuery(conn, query, buf, sizeof(buf));
hFreeConn(&conn);
if (symbol != NULL)
{
char *ptr = stringIn("{}", symbol);
if (ptr != NULL)
*ptr = 0;
freeMem(name);
name = cloneString(symbol);
}
}
return(name);
}
boolean chromSeqFileExists(char *db, char *chrom)
/* Check whether chromInfo exists for a database, find the path of the */
/* sequence file for this chromosome and check if the file exists. */
{
char seqFile[512];
struct sqlConnection *conn = sqlConnect(db);
char query[256];
char *res = NULL;
boolean exists = FALSE;
/* if the database exists, check for the chromInfo file */
if (sqlDatabaseExists(db))
{
safef(query, sizeof(query), "select fileName from chromInfo where chrom = '%s'", chrom);
res = sqlQuickQuery(conn, query, seqFile, 512);
sqlDisconnect(&conn);
}
/* if there is not table or no information in the table or if the table */
/* exists but the file can not be opened return false, otherwise sequence */
/* file exists and return true */
if (res != NULL)
{
/* chromInfo table exists so check that sequence file can be opened */
FILE *f = fopen(seqFile, "rb");
if (f != NULL)
{
exists = TRUE;
fclose(f);
}
}
return exists;
}
boolean chromSeqFileExists(char *db, char *chrom)
/* Check whether chromInfo exists for a database, find the path of the */
/* sequence file for this chromosome and check if the file exists. */
{
char seqFile[512];
struct sqlConnection *conn = sqlConnect(db);
char query[256];
char *res = NULL;
boolean exists = FALSE;
/* if the database exists, check for the chromInfo file */
if (sqlDatabaseExists(db))
{
sqlSafef(query, sizeof(query), "select fileName from chromInfo where chrom = '%s'", chrom);
res = sqlQuickQuery(conn, query, seqFile, 512);
sqlDisconnect(&conn);
}
/* if there is not table or no information in the table or if the table */
/* exists but the file can not be opened return false, otherwise sequence */
/* file exists and return true */
if (res != NULL)
{
char *seqFile2 = hReplaceGbdb(seqFile);
exists = udcExists(seqFile2);
freeMem(seqFile2);
}
return exists;
}
boolean lsSnpPdbHasPdb(struct sqlConnection *conn, char *pdbId)
/* determine if the specified PDB has any entries in LS-SNP */
{
if (!sqlTableExists(conn, "lsSnpPdb"))
return FALSE;
char query[256], buf[64];
sqlSafef(query, sizeof(query), "SELECT chain FROM lsSnpPdb WHERE (pdbId = \"%s\")", pdbId);
return (sqlQuickQuery(conn, query, buf, sizeof(buf)) != NULL);
}
boolean spIsPrimaryAcc(struct sqlConnection *conn, char *acc)
/* Return TRUE if this is a primary accession in database. */
{
char query[256];
SpAcc temp;
safef(query, sizeof(query), "select acc from displayId where acc = '%s'",
acc);
return sqlQuickQuery(conn, query, temp, sizeof(temp)) != NULL;
}
struct serverTable *findServer(char *db, boolean isTrans)
/* Return server for given database. Db can either be
* database name or description. */
{
if (trackHubDatabase(db))
{
struct serverTable *hubSt = trackHubServerTable(db, isTrans);
if (hubSt != NULL)
return hubSt;
errAbort("Cannot get blat server parameters for track hub with database %s\n", db);
}
static struct serverTable st;
struct sqlConnection *conn = hConnectCentral();
char query[256];
struct sqlResult *sr;
char **row;
char dbActualName[32];
/* If necessary convert database description to name. */
sqlSafef(query, sizeof(query), "select name from dbDb where name = '%s'", db);
if (!sqlExists(conn, query))
{
sqlSafef(query, sizeof(query), "select name from dbDb where description = '%s'", db);
if (sqlQuickQuery(conn, query, dbActualName, sizeof(dbActualName)) != NULL)
db = dbActualName;
}
/* Do a little join to get data to fit into the serverTable. */
sqlSafef(query, sizeof(query), "select dbDb.name,dbDb.description,blatServers.isTrans"
",blatServers.host,blatServers.port,dbDb.nibPath "
"from dbDb,blatServers where blatServers.isTrans = %d and "
"dbDb.name = '%s' and dbDb.name = blatServers.db",
isTrans, db);
sr = sqlGetResult(conn, query);
if ((row = sqlNextRow(sr)) == NULL)
{
errAbort("Can't find a server for %s database %s. Click "
"<A HREF=\"/cgi-bin/hgBlat?%s&command=start&db=%s\">here</A> "
"to reset to default database.",
(isTrans ? "translated" : "DNA"), db,
cartSidUrlString(cart), hDefaultDb());
}
st.db = cloneString(row[0]);
st.genome = cloneString(row[1]);
st.isTrans = atoi(row[2]);
st.host = cloneString(row[3]);
st.port = cloneString(row[4]);
st.nibDir = hReplaceGbdbSeqDir(row[5], st.db);
sqlFreeResult(&sr);
hDisconnectCentral(&conn);
return &st;
}
static char *orfToGene(struct sqlConnection *conn, char *orf)
/* Return gene name of given orf, or NULL if it
* doesn't exist. */
{
char gene[256];
char query[256];
safef(query, sizeof(query), "select value from sgdToName where name = '%s'",
orf);
if (sqlQuickQuery(conn, query, gene, sizeof(gene)) == NULL)
return NULL;
return cloneString(gene);
}
unsigned getChromSize(char *chrom, char *db)
/* need size of a chromosome so we don't pick random
probes that are off one end */
{
char chromSize[512];
char query[512];
struct sqlConnection *conn = sqlConnect(db);
sprintf(query, "select size from chromInfo where chrom = '%s'", chrom);
sqlQuickQuery(conn, query, chromSize, sizeof(chromSize));
sqlDisconnect(&conn);
assert(chromSize != NULL);
return atoi(chromSize);
}
char *hDbOrganism(char *database)
/* Function to get organism from the genome db */
{
struct sqlConnection *connCentral = hConnectCentral();
char buf[128];
char query[256];
char *res;
sqlSafef(query, sizeof(query), "select organism from dbDb where name = '%s'",
database);
res = strdup(sqlQuickQuery(connCentral, query, buf, sizeof(buf)));
hDisconnectCentral(&connCentral);
return res;
}
static boolean isPrivate(struct sqlConnection *conn,
struct hash *privateHash, char *imageId)
/* Return TRUE if image is associated with private submissionSet. */
{
char *src, buf[16];
char query[256];
safef(query, sizeof(query), "select submissionSet from image where id=%s",
imageId);
src = sqlQuickQuery(conn, query, buf, sizeof(buf));
if (src != NULL && hashLookup(privateHash, src) != NULL)
return TRUE;
return FALSE;
}
char *mayRenameFile(struct sqlConnection *conn, char *table, char *oldFileName, char *newFileName, char *downDir)
// this is a table with a fileName field, this may point to the same file
// that we renamed earlier, so we don't want to rename it again
{
char buffer[10 * 1024];
char fileName[10 * 1024];
safef(buffer, sizeof buffer, "select fileName from %s limit 1", table);
if (sqlQuickQuery(conn, buffer, fileName, sizeof fileName) == NULL)
errAbort("couldn't get fileName from table %s\n", table);
char *link = getSymLinkFile(fileName);
#ifdef NOTNOW
verbose(2,"got fileName %s\n", fileName);
FILE *f = fopen(fileName, "r");
if (f == NULL)
errAbort("fileName %s from table %s can't be opened", fileName, table);
else
fclose(f);
#endif
safef(buffer, sizeof buffer, "%s/%s", downDir, oldFileName);
if (differentString(link, buffer))
errAbort("symlink to '%s' in table '%s', is not the same as '%s'\n", link,
table, buffer);
if (!doTest)
{
verbose(2, "unlinking file %s\n", fileName);
}
char *ptr = strrchr(fileName, '/');
if (ptr == NULL)
errAbort("can't find '/' in %s\n", fileName);
ptr++;
int bufferLen = sizeof(fileName) - (ptr - fileName);
safef(ptr, bufferLen, "%s", newFileName);
safef(buffer, sizeof buffer, "%s/%s", downDir, newFileName);
if (!doTest)
{
verbose(2, "linking %s to %s\n", buffer, fileName);
}
return cloneString(fileName);
}
char *cdsQuery(struct sqlConnection *conn, char *acc, char *cdsBuf, int cdsBufSize)
/* query for a CDS, either in the hash table or database */
{
if (gCdsTable != NULL)
return hashFindVal(gCdsTable, acc);
else
{
char query[512];
sqlSafef(query, sizeof(query),
"SELECT cds.name FROM cds,gbCdnaInfo WHERE (gbCdnaInfo.acc = '%s') AND (gbCdnaInfo.cds !=0) AND (gbCdnaInfo.cds = cds.id)",
acc);
return sqlQuickQuery(conn, query, cdsBuf, cdsBufSize);
}
}
struct searchResult *knownGeneSearchResult(struct sqlConnection *conn,
char *kgID, char *alias)
/* Return a searchResult for a known gene. */
{
struct searchResult *sr;
struct dyString *dy = dyStringNew(1024);
char description[512];
char query[256];
char name[64];
/* Allocate and fill in with geneID. */
AllocVar(sr);
sr->gp.name = cloneString(kgID);
/* Get gene symbol into short label if possible. */
sqlSafef(query, sizeof(query),
"select geneSymbol from kgXref where kgID = '%s'", kgID);
if (sqlQuickQuery(conn, query, name, sizeof(name)))
sr->shortLabel = cloneString(name);
else
sr->shortLabel = cloneString(kgID);
/* Add alias to long label if need be */
if (alias != NULL && !sameWord(name, alias))
dyStringPrintf(dy, "(aka %s) ", alias);
/* Add description to long label. */
sqlSafef(query, sizeof(query),
"select description from kgXref where kgID = '%s'", kgID);
if (sqlQuickQuery(conn, query, description, sizeof(description)))
dyStringAppend(dy, description);
sr->longLabel = cloneString(dy->string);
/* Cleanup and go home. */
dyStringFree(&dy);
return sr;
}
void dnaseHg38AddTreatments(char *inTab, char *outTab)
/* dnaseHg38AddTreatments - Add treatments to dnase hg38 metadata. */
{
struct sqlConnection *conn = sqlConnect("hgFixed");
struct lineFile *lf = lineFileOpen(inTab, TRUE);
FILE *f = mustOpen(outTab, "w");
char *line;
while (lineFileNext(lf, &line, NULL))
{
if (line[0] == '#')
fprintf(f, "%s\ttreatment\tlabel\n", line);
else
{
char *inRow[5];
int wordCount = chopByWhite(line, inRow, ArraySize(inRow));
lineFileExpectWords(lf, 4, wordCount);
char *acc = inRow[0];
char *biosample = inRow[1];
char query[512];
sqlSafef(query, sizeof(query), "select expVars from encodeExp where accession = '%s'", acc);
char varBuf[1024];
char *treatment = "n/a";
char *label = biosample;
char labelBuf[256];
char *vars = sqlQuickQuery(conn, query, varBuf, sizeof(varBuf));
if (!isEmpty(vars))
{
treatment = vars + strlen("treatment=");
if (sameString(treatment, "4OHTAM_20nM_72hr"))
safef(labelBuf, sizeof(labelBuf), "%s 40HTAM", biosample);
else if (sameString(treatment, "diffProtA_14d"))
safef(labelBuf, sizeof(labelBuf), "%s diff 14d", biosample);
else if (sameString(treatment, "diffProtA_5d"))
safef(labelBuf, sizeof(labelBuf), "%s diff 5d", biosample);
else if (sameString(treatment, "DIFF_4d"))
safef(labelBuf, sizeof(labelBuf), "%s diff 4d", biosample);
else if (sameString(treatment, "Estradiol_100nM_1hr"))
safef(labelBuf, sizeof(labelBuf), "%s estradi 1h", biosample);
else if (sameString(treatment, "Estradiol_ctrl_0hr"))
safef(labelBuf, sizeof(labelBuf), "%s estradi 0h", biosample);
else
errAbort("Unknown treatment %s", treatment);
label = labelBuf;
}
fprintf(f, "%s\t%s\t%s\t%s\t%s\t%s\n", inRow[0], inRow[1], inRow[2], inRow[3], treatment, label);
}
}
carefulClose(&f);
}
char *lookupName(struct sqlConnection *conn, char *table, unsigned id)
/* Look up name based on numerical id in given table. Return "n/a" if
* not found. The return value is overwritten by the next call to this
* routine. */
{
char query[256];
static char buf[256];
char *result;
sqlSafef(query, sizeof query, "select name from %s where id = %u", table, id);
result = sqlQuickQuery(conn, query, buf, sizeof(buf));
if (result == NULL)
result = "n/a";
return result;
}
static boolean isInMBLabValidDb(char *acc)
/* check if an accession is in the Brent lab validation database */
{
boolean inMBLabValidDb = FALSE;
struct sqlConnection *fconn = sqlMayConnect("hgFixed");
if ((fconn != NULL) && sqlTableExists(fconn, "mgcMBLabValid"))
{
char query[64], buf[32];
sqlSafef(query, sizeof(query), "select acc from mgcMBLabValid where acc=\"%s\"",
acc);
if (sqlQuickQuery(fconn, query, buf, sizeof(buf)) != NULL)
inMBLabValidDb = TRUE;
sqlDisconnect(&fconn);
}
return inMBLabValidDb;
}
static char *ucscToEnsembl(char *database, char *chrom)
/* if table UCSC_TO_ENSEMBL exists in the given database, return the
Ensembl name for this chrom */
{
static char ensemblName[256];
struct sqlConnection *conn = hAllocConn(database);
ensemblName[0] = 0;
if (sqlTableExists(conn, UCSC_TO_ENSEMBL))
{
char query[256];
safef(query, ArraySize(query), "select ensembl from %s where ucsc='%s'",
UCSC_TO_ENSEMBL, chrom);
(void) sqlQuickQuery(conn,query,ensemblName,ArraySize(ensemblName));
}
return ensemblName;
}
void redoOne(struct sqlConnection *conn, struct edwFile *redoEf)
/* Redo one file. */
{
/* Figure out submit file name of the gtf file. */
char gtfFileName[PATH_LEN];
strcpy(gtfFileName, redoEf->submitFileName);
chopSuffix(gtfFileName);
strcat(gtfFileName, ".gz");
/* Get edwFile record for gtf file. */
char query[PATH_LEN+64];
safef(query, sizeof(query), "select * from edwFile where submitFileName='%s'", gtfFileName);
struct edwFile *sourceEf = edwFileLoadByQuery(conn, query);
assert(slCount(sourceEf) == 1);
/* Get UCSC database */
safef(query, sizeof(query), "select ucscDb from edwValidFile where fileId=%u", sourceEf->id);
char ucscDb[64] = "";
sqlQuickQuery(conn, query, ucscDb, sizeof(ucscDb));
assert(ucscDb[0] != 0);
/* Remake the big bed file. */
char sourceFileName[PATH_LEN], destFileName[PATH_LEN];
safef(sourceFileName, sizeof(sourceFileName), "%s%s", edwRootDir, sourceEf->edwFileName);
safef(destFileName, sizeof(destFileName), "%s%s", edwRootDir, redoEf->edwFileName);
makeGtfBigBed(ucscDb, sourceFileName, destFileName);
/* Recalculate size and md5 sum and validation key. */
char *md5 = md5HexForFile(destFileName);
long long size = fileSize(destFileName);
char *validKey = encode3CalcValidationKey(md5, size);
/* Issue command to update md5 in database. */
char command[2*PATH_LEN];
safef(command, sizeof(command),
"hgsql -e 'update edwFile set md5=\"%s\" where id=%u' encodeDataWarehouse", md5, redoEf->id);
doSystem(command);
/* Issue command to update tags in database. */
char *newTags = cgiStringNewValForVar(redoEf->tags, "valid_key", validKey);
if (doReal)
{
edwFileResetTags(conn, redoEf, newTags);
}
}
static char *transcriptToCanonical(struct sqlConnection *conn, char *transcript)
/* Translate transcript to canonical ID if possible, otherwise just return
* a copy of transcript. */
{
struct dyString *dy = newDyString(1024);
char *cannon = genomeSetting("canonicalTable");
char *isoform = genomeSetting("isoformTable");
char buf[128];
char *result = NULL;
sqlDyStringPrintf(dy, "select %s.transcript from %s,%s where %s.transcript = '%s'",
cannon, isoform, cannon, isoform, transcript);
sqlDyStringPrintf(dy, " and %s.clusterId = %s.clusterId", isoform, cannon);
result = sqlQuickQuery(conn, dy->string, buf, sizeof(buf));
if (result != NULL)
return(cloneString(result));
else
return(cloneString(transcript));
}
void getChromNamesAndDirForDb(char *db)
{
struct sqlConnection *conn = hConnectCentral();
char query[512];
char buff[512];
char *tmpMark = NULL;
int buffSize = 512;
sqlSafef(query, sizeof(query), "select nibPath from dbDb where name='%s'", db);
if(sqlQuickQuery(conn, query, buff, buffSize) == NULL)
errAbort("Coun't find nib dir for genome %s\n", db);
dirName = needMem(buffSize*sizeof(char));
tmpMark = strrchr(buff, '/');
if(tmpMark != NULL)
*tmpMark = '\0';
snprintf(dirName, buffSize, "%s/mixedNib/", buff);
chromNames = hAllChromNames();
hDisconnectCentral(&conn);
}
void cdwReallyRemoveFiles(char *email, char *submitUrl, int fileCount, char *fileIds[])
/* cdwReallyRemoveFiles - Remove files from data warehouse. Generally you want to depricate them
* instead. */
{
/* First convert all fileIds to binary. Do this first so bad command lines get caught. */
long long ids[fileCount];
int i;
for (i = 0; i<fileCount; ++i)
ids[i] = sqlLongLong(fileIds[i]);
/* Get hash of all submissions by user from that URL. Hash is keyed by ascii version of
* submitId. */
struct sqlConnection *conn = cdwConnectReadWrite();
struct cdwUser *user = cdwMustGetUserFromEmail(conn, email);
char query[256];
sqlSafef(query, sizeof(query),
" select cdwSubmit.id,cdwSubmitDir.id from cdwSubmit,cdwSubmitDir "
" where cdwSubmit.submitDirId=cdwSubmitDir.id and userId=%d "
" and cdwSubmitDir.url='%s' ",
user->id, submitUrl);
struct hash *submitHash = sqlQuickHash(conn, query);
/* Make sure that files and submission really go together. */
for (i=0; i<fileCount; ++i)
{
long long fileId = ids[i];
char buf[64];
sqlSafef(query, sizeof(query), "select submitId from cdwFile where id=%lld", fileId);
char *result = sqlQuickQuery(conn, query, buf, sizeof(buf));
if (result == NULL)
errAbort("%lld is not a fileId in the warehouse", fileId);
if (hashLookup(submitHash, result) == NULL)
errAbort("File ID %lld does not belong to submission set based on %s", fileId, submitUrl);
}
/* OK - paranoid checking is done, now let's remove each file from the tables it is in. */
for (i=0; i<fileCount; ++i)
{
cdwReallyRemoveFile(conn, ids[i], really);
}
}
char *getSwiss( struct sqlConnection *conn , char *id)
{
char *name = cloneString(id);
char infoTable[128];
safef(infoTable, sizeof(infoTable), "sgdToSwissProt");
if (hTableExists(infoTable))
{
struct sqlConnection *conn = hAllocConn();
char *symbol = NULL;
//char *ptr = strchr(name, '-');
char query[256];
char buf[64];
//if (ptr != NULL)
// *ptr = 0;
sqlSafef(query, sizeof(query),
"select value from %s where name = '%s';", infoTable, name);
if ((symbol = sqlQuickQuery(conn, query, buf, sizeof(buf))) != NULL)
name = symbol;
hFreeConn(&conn);
}
return(name);
}
static char *getGencodeGeneId(struct sqlConnection *conn, char *id, char *buffer, int bufSize)
{
char query[256];
sqlSafef(query, sizeof(query), "select protein from knownCanonical c, knownIsoforms i where i.clusterId=c.clusterId and i.transcript='%s'" ,id);
return sqlQuickQuery(conn, query, buffer, bufSize );
}
void txGeneAlias(char *genomeDb, char *uniProtDb, char *xrefFile,
char *evFile, char *oldToNew, char *aliasFile, char *protAliasFile)
/* txGeneAlias - Make kgAlias and kgProtAlias tables.. */
{
/* Read and hash oldToNew */
struct hash *newToOldHash = loadNewToOldHash(oldToNew);
/* Load evidence into hash */
struct hash *evHash = newHash(18);
struct txRnaAccs *ev, *evList = txRnaAccsLoadAll(evFile);
for (ev = evList; ev != NULL; ev = ev->next)
hashAdd(evHash, ev->name, ev);
/* Open connections to our databases */
struct sqlConnection *gConn = sqlConnect(genomeDb);
struct sqlConnection *uConn = sqlConnect(uniProtDb);
struct sqlResult *sr;
char **row;
char query[256];
/* Open files. */
struct lineFile *lf = lineFileOpen(xrefFile, TRUE);
FILE *fAlias = mustOpen(aliasFile, "w");
FILE *fProt = mustOpen(protAliasFile, "w");
/* Stream through xref file, which has much of the info we need,
* and which contains a line for each gene. */
char *words[KGXREF_NUM_COLS];
while (lineFileRowTab(lf, words))
{
/* Load the xref, and output most of it's fields as aliases. */
struct kgXref *x = kgXrefLoad(words);
char *id = x->kgID;
outAlias(fAlias, id, x->kgID);
outAlias(fAlias, id, x->mRNA);
outAlias(fAlias, id, x->spID);
outAlias(fAlias, id, x->spDisplayID);
outAlias(fAlias, id, x->geneSymbol);
outAlias(fAlias, id, x->refseq);
outAlias(fAlias, id, x->protAcc);
char *old = hashFindVal(newToOldHash, id);
if (old != NULL)
outAlias(fAlias, id, old);
/* If we've got a uniProt ID, use that to get more info from uniProt. */
char *acc = x->spID;
if ((acc[0] != 0) && (acc = spLookupPrimaryAccMaybe(uConn, acc)) != NULL)
{
/* Get current accession and output a bunch of easy protein aliases. */
outProt(fProt, id, acc, acc);
outProt(fProt, id, acc, x->spDisplayID);
outProt(fProt, id, acc, x->geneSymbol);
outProt(fProt, id, acc, x->protAcc);
if (old != NULL)
outProt(fProt, id, acc, old);
/* Throw in old swissProt accessions. */
sqlSafef(query, sizeof(query), "select val from otherAcc where acc = '%s'", acc);
sr = sqlGetResult(uConn, query);
while ((row = sqlNextRow(sr)) != NULL)
{
outAlias(fAlias, id, row[0]);
outProt(fProt, id, acc, row[0]);
}
/* Throw in gene names that SwissProt knows about */
struct slName *gene, *geneList = spGenes(uConn, acc);
for (gene = geneList; gene != NULL; gene = gene->next)
{
outAlias(fAlias, id, gene->name);
outProt(fProt, id, acc, gene->name);
}
slFreeList(&geneList);
}
/* Throw in gene names from genbank. */
/* At some point we may want to restrict this to the primary transcript in a cluster. */
ev = hashFindVal(evHash, id);
if (ev != NULL)
{
int i;
for (i=0; i<ev->accCount; ++i)
{
sqlSafef(query, sizeof(query), "select geneName from gbCdnaInfo where acc='%s'", acc);
int nameId = sqlQuickNum(gConn, query);
if (nameId != 0)
{
char name[64];
sqlSafef(query, sizeof(query), "select name from geneName where id=%d", nameId);
if (sqlQuickQuery(gConn, query, name, sizeof(name)))
outAlias(fAlias, id, name);
}
}
}
kgXrefFree(&x);
}
carefulClose(&fAlias);
carefulClose(&fProt);
}
void doMiddle(struct cart *theCart)
/* Set up pretty web display. */
{
struct sqlConnection *conn, *conn2;
struct sqlConnection *connCentral = hConnectCentral();
char query[256], query2[256];
struct sqlResult *sr, *sr2;
char **row, **row2;
char buf[128];
char *answer;
char *database;
char *genome, *genomeDesc;
char *kgID, *chrom, *txStart, *txEnd;
int iCnt = 1;
cart = theCart;
cartWebStart(theCart, database, "UCSC Known Genes List \n");
getDbAndGenome(cart, &database, &genome, oldVars);
if (!hTableExistsDb(database, "knownGene"))
{
printf("<br>Database %s currently does not have UCSC Known Genes.", database);
cartWebEnd();
return;
}
sprintf(query, "select description from dbDb where name = '%s'", database);
genomeDesc = strdup(sqlQuickQuery(connCentral, query, buf, sizeof(buf)));
hDisconnectCentral(&connCentral);
printf("<H2>%s Genome (%s Assembly)</H2>\n", genome, genomeDesc);
conn = hAllocConn(database);
conn2= hAllocConn(database);
sprintf(query2,"select kgID from %s.kgXref order by geneSymbol;",
database);
/* use the following for quck testing */
/*sprintf(query2,"select kgID, geneSymbol, description, spID, refseq from %s.kgXref order by geneSymbol limit 10;", database);
*/
sr2 = sqlMustGetResult(conn2, query2);
row2 = sqlNextRow(sr2);
while (row2 != NULL)
{
kgID = row2[0];
sprintf(query,"select chrom, txSTart, txEnd from %s.knownGene where name='%s'", database, kgID);
sr = sqlMustGetResult(conn, query);
row = sqlNextRow(sr);
chrom = row[0];
txStart = row[1];
txEnd = row[2];
printf("<A href=\"/cgi-bin/hgGene?db=%s&hgg_gene=%s",
database, kgID);
printf("&hgg_chrom=%s&hgg_start=%s&hgg_end=%s\">", chrom, txStart, txEnd);
printf("%d</A><BR>\n", iCnt);
iCnt++;
if ((iCnt % 1000) == 0) fflush(stdout);
sqlFreeResult(&sr);
row2 = sqlNextRow(sr2);
}
sqlFreeResult(&sr2);
cartWebEnd();
}
void loadAffyTranscriptome(struct track *tg)
/* Convert sample info in window to linked feature. */
{
struct sqlConnection *conn = hAllocConn(database);
struct sqlResult *sr;
char **row;
int rowOffset;
struct sample *sample;
struct linkedFeatures *lfList = NULL, *lf;
char *hasDense = NULL;
char *where = NULL;
char query[256];
char tableName[256];
int zoom1 = 23924, zoom2 = 2991; /* bp per data point */
float pixPerBase = 0;
/* Set it up so we don't have linear interpretation.. */
char *noLinearInterpString = wiggleEnumToString(wiggleNoInterpolation);
cartSetString(cart, "affyTranscriptome.linear.interp", noLinearInterpString);
if(tl.picWidth == 0)
errAbort("hgTracks.c::loadAffyTranscriptome() - can't have pixel width of 0");
pixPerBase = (winEnd - winStart)/ tl.picWidth;
/* Determine zoom level. */
if(pixPerBase >= zoom1)
safef(tableName, sizeof(tableName), "%s_%s", "zoom1", tg->table);
else if(pixPerBase >= zoom2)
safef(tableName, sizeof(tableName), "%s_%s", "zoom2", tg->table);
else
safef(tableName, sizeof(tableName), "%s", tg->table);
/*see if we have a summary table*/
if(hTableExists(database, tableName))
safef(query, sizeof(query), "select name from %s where name = '%s' limit 1", tableName, tg->shortLabel);
else
{
warn("<p>Couldn't find table %s<br><br>", tableName);
safef(query, sizeof(query), "select name from %s where name = '%s' limit 1", tg->table, tg->shortLabel);
safef(tableName, sizeof(tableName), "%s", tg->table);
}
hasDense = sqlQuickQuery(conn, query, query, sizeof(query));
/* If we're in dense mode and have a summary table load it. */
if(tg->visibility == tvDense)
{
if(hasDense != NULL)
{
safef(query, sizeof(query), " name = '%s' ", tg->shortLabel);
where = cloneString(query);
}
}
sr = hRangeQuery(conn, tableName, chromName, winStart, winEnd, where, &rowOffset);
while ((row = sqlNextRow(sr)) != NULL)
{
sample = sampleLoad(row+rowOffset);
lf = lfFromSample(sample);
slAddHead(&lfList, lf);
sampleFree(&sample);
}
if(where != NULL)
freez(&where);
sqlFreeResult(&sr);
hFreeConn(&conn);
slReverse(&lfList);
/* sort to bring items with common names to the same line
but only for tracks with a summary table (with name=shortLabel) in
dense mode*/
if( hasDense != NULL )
{
sortGroupList = tg; /* used to put track name at top of sorted list. */
slSort(&lfList, lfNamePositionCmp);
sortGroupList = NULL;
}
tg->items = lfList;
}
void loadSampleZoo(struct track *tg)
/* Convert sample info in window to linked feature. */
{
int maxWiggleTrackHeight = 2500;
struct sqlConnection *conn = hAllocConn(database);
struct sqlResult *sr;
char **row;
int rowOffset;
struct sample *sample;
struct linkedFeatures *lfList = NULL, *lf;
char *hasDense = NULL;
char *where = NULL;
char query[256];
char option[64];
zooSpeciesHashInit();
/*see if we have a summary table*/
safef(query, sizeof(query), "select name from %s where name = '%s' limit 1", tg->table, tg->shortLabel);
//errAbort( "%s", query );
hasDense = sqlQuickQuery(conn, query, query, sizeof(query));
/* If we're in dense mode and have a summary table load it. */
if(tg->visibility == tvDense)
{
if(hasDense != NULL)
{
safef(query, sizeof(query), " name = '%s' ", tg->shortLabel);
where = cloneString(query);
}
}
sr = hRangeQuery(conn, tg->table, chromName, winStart, winEnd, where, &rowOffset);
while ((row = sqlNextRow(sr)) != NULL)
{
sample = sampleLoad(row + rowOffset);
lf = lfFromSample(sample);
safef( option, sizeof(option), "zooSpecies.%s", sample->name );
if( cartUsualBoolean(cart, option, TRUE ))
slAddHead(&lfList, lf);
sampleFree(&sample);
}
if(where != NULL)
freez(&where);
sqlFreeResult(&sr);
hFreeConn(&conn);
slReverse(&lfList);
/* sort to bring items with common names to the same line
* but only for tracks with a summary table
* (with name=shortLabel) in dense mode */
if( hasDense != NULL )
{
sortGroupList = tg; /* used to put track name at top of sorted list. */
slSort(&lfList, lfNamePositionCmp);
sortGroupList = NULL;
}
/* Sort in species phylogenetic order */
slSort(&lfList, lfZooCmp);
tg->items = lfList;
/*turn off full mode if there are too many rows or each row is too
* large. A total of maxWiggleTrackHeight is allowed for number of
* rows times the rowHeight*/
if( tg->visibility == tvFull && sampleTotalHeight( tg, tvFull ) > maxWiggleTrackHeight )
{
tg->limitedVisSet = TRUE;
tg->limitedVis = tvDense;
}
}
static void loadDatabase(char *database, char *track, int bedSize, struct bedStub *bedList)
/* Load database from bedList. */
{
struct sqlConnection *conn;
struct dyString *dy = newDyString(1024);
char *tab = (char *)NULL;
int loadOptions = (optionExists("onServer") ? SQL_TAB_FILE_ON_SERVER : 0);
if ( ! noLoad )
conn = sqlConnect(database);
if ((char *)NULL != tmpDir)
tab = cloneString(rTempName(tmpDir,"loadBed",".tab"));
else
tab = cloneString("bed.tab");
if (bedDetail && sqlTable == NULL)
errAbort("bedDetail format requires sqlTable option");
if (bedDetail && !strictTab)
errAbort("bedDetail format must be tab separated");
if (bedDetail && !noBin)
noBin = TRUE;
/* First make table definition. */
if (sqlTable != NULL && !oldTable)
{
/* Read from file. */
char *sql, *s;
readInGulp(sqlTable, &sql, NULL);
/* Chop off end-of-statement semicolon if need be. */
s = strchr(sql, ';');
if (s != NULL) *s = 0;
if ( !noLoad )
{
if (renameSqlTable)
{
char *pos = stringIn("CREATE TABLE ", sql);
if (pos == NULL)
errAbort("Can't find CREATE TABLE in %s\n", sqlTable);
char *oldSql = cloneString(sql);
nextWord(&pos); nextWord(&pos);
char *tableName = nextWord(&pos);
sql = replaceChars(oldSql, tableName, track);
}
verbose(1, "Creating table definition for %s\n", track);
sqlRemakeTable(conn, track, sql);
if (!noBin)
addBinToEmptyTable(conn, track);
adjustSqlTableColumns(conn, track, bedSize);
}
freez(&sql);
}
else if (!oldTable)
{
int minLength;
if (noLoad)
minLength=6;
else if (maxChromNameLength)
minLength = maxChromNameLength;
else
minLength = hGetMinIndexLength(database);
verbose(2, "INDEX chrom length: %d\n", minLength);
/* Create definition statement. */
verbose(1, "Creating table definition for %s\n", track);
dyStringPrintf(dy, "CREATE TABLE %s (\n", track);
if (!noBin)
dyStringAppend(dy, " bin smallint unsigned not null,\n");
dyStringAppend(dy, " chrom varchar(255) not null,\n");
dyStringAppend(dy, " chromStart int unsigned not null,\n");
dyStringAppend(dy, " chromEnd int unsigned not null,\n");
if (bedSize >= 4)
maybeBedGraph(4, dy, " name varchar(255) not null,\n");
if (bedSize >= 5)
{
if (allowNegativeScores)
maybeBedGraph(5, dy, " score int not null,\n");
else
maybeBedGraph(5, dy, " score int unsigned not null,\n");
}
if (bedSize >= 6)
maybeBedGraph(6, dy, " strand char(1) not null,\n");
if (bedSize >= 7)
maybeBedGraph(7, dy, " thickStart int unsigned not null,\n");
if (bedSize >= 8)
maybeBedGraph(8, dy, " thickEnd int unsigned not null,\n");
/* As of 2004-11-22 the reserved field is used as itemRgb in code */
if (bedSize >= 9)
maybeBedGraph(9, dy, " reserved int unsigned not null,\n");
if (bedSize >= 10)
maybeBedGraph(10, dy, " blockCount int unsigned not null,\n");
if (bedSize >= 11)
maybeBedGraph(11, dy, " blockSizes longblob not null,\n");
if (bedSize >= 12)
maybeBedGraph(12, dy, " chromStarts longblob not null,\n");
if (bedSize >= 13)
maybeBedGraph(13, dy, " expCount int unsigned not null,\n");
if (bedSize >= 14)
maybeBedGraph(14, dy, " expIds longblob not null,\n");
if (bedSize >= 15)
maybeBedGraph(15, dy, " expScores longblob not null,\n");
dyStringAppend(dy, "#Indices\n");
if (nameIx && (bedSize >= 4) && (0 == bedGraph))
dyStringAppend(dy, " INDEX(name(16)),\n");
if (noBin)
{
dyStringPrintf(dy, " INDEX(chrom(%d),chromStart)\n", minLength);
}
else
{
dyStringPrintf(dy, " INDEX(chrom(%d),bin)\n", minLength);
}
dyStringAppend(dy, ")\n");
if (noLoad)
verbose(2,"%s", dy->string);
else
sqlRemakeTable(conn, track, dy->string);
}
verbose(1, "Saving %s\n", tab);
writeBedTab(tab, bedList, bedSize);
if ( ! noLoad )
{
verbose(1, "Loading %s\n", database);
if (customTrackLoader)
sqlLoadTabFile(conn, tab, track, loadOptions|SQL_TAB_FILE_WARN_ON_WARN);
else
sqlLoadTabFile(conn, tab, track, loadOptions);
if (! noHistory)
{
char comment[256];
/* add a comment to the history table and finish up connection */
safef(comment, sizeof(comment),
"Add %d element(s) from bed list to %s table",
slCount(bedList), track);
hgHistoryComment(conn, comment);
}
if(fillInScoreColumn != NULL)
{
char query[500];
char buf[500];
struct sqlResult *sr;
safef(query, sizeof(query), "select sum(score) from %s", track);
if(sqlQuickQuery(conn, query, buf, sizeof(buf)))
{
unsigned sum = sqlUnsigned(buf);
if (!sum)
{
safef(query, sizeof(query), "select min(%s), max(%s) from %s", fillInScoreColumn, fillInScoreColumn, track);
if ((sr = sqlGetResult(conn, query)) != NULL)
{
char **row = sqlNextRow(sr);
if(row != NULL)
{
float min = sqlFloat(row[0]);
float max = sqlFloat(row[1]);
if ( !(max == -1 && min == -1)) // if score is -1 then ignore, as if it werent present
{
if (max == min || sameString(row[0],row[1])) // this will lead to 'inf' score value in SQL update causing an error
errAbort("Could not set score in table %s max(%s)=min(%s)=%s\n", track, fillInScoreColumn, fillInScoreColumn, row[0]);
sqlFreeResult(&sr);
// Calculate a, b s/t f(x) = ax + b maps min-max => minScore-1000
float a = (1000-minScore) / (max - min);
float b = 1000 - ((1000-minScore) * max) / (max - min);
safef(query, sizeof(query), "update %s set score = round((%f * %s) + %f)", track, a, fillInScoreColumn, b);
int changed = sqlUpdateRows(conn, query, NULL);
verbose(2, "update query: %s; changed: %d\n", query, changed);
}
else
{
sqlFreeResult(&sr);
verbose(2, "score not updated; all values for column %s are -1\n", fillInScoreColumn);
}
}
}
}
}
}
sqlDisconnect(&conn);
/* if temp dir specified, unlink file to make it disappear */
if ((char *)NULL != tmpDir)
unlink(tab);
}
else
verbose(1, "No load option selected, see file: %s\n", tab);
} /* static void loadDatabase() */
void checkFilename(struct sqlConnection *conn, char *table, struct hash *allBbiNames)
{
char buffer[10 * 1024];
char fileName[10 * 1024];
char oldSymlink[10 * 1024];
verbose(2, "checking for fileName field in table %s \n", table);
// see if this is even a bbi table
boolean bbiTable = FALSE;
struct slName *fnames = sqlFieldNames(conn, table);
if ((slCount(fnames) == 1) && (sameString(fnames->name, "fileName")))
bbiTable = TRUE;
slFreeList(&fnames);
if (!bbiTable)
return;
sqlSafef(buffer, sizeof buffer, "select fileName from %s limit 1", table);
if (sqlQuickQuery(conn, buffer, fileName, sizeof fileName) != NULL)
{
while(1) // loop to catch .bai as well as .bam
{
hashAdd(allBbiNames, fileName, NULL);
verbose(2,"got table.fileName %s\n", fileName);
// file exists
FILE *f = fopen(fileName, "r");
if (f == NULL)
{
warn("fileName %s from table %s can't be opened", fileName, table);
return;
}
else
fclose(f);
// check that the filename and object base match
char *base = strrchr(fileName, '/');
if (base == NULL)
{
warn("fileName %s in table %s not absolute path", fileName, table);
return;
}
else
{
base++;
char *dot = strchr(base, '.');
if (dot == NULL)
{
warn("fileName %s in table %s does not have suffix", fileName, table);
return;
}
else
{
char saveChar = *dot;
*dot = 0;
if (!sameString(table, base))
{
warn("fileName %s doesn't match table %s", base, table);
return;
}
*dot = saveChar;
}
}
// this file is really a symlink, so check its link target
ssize_t bufRead = readlink(fileName, oldSymlink, sizeof oldSymlink);
if (bufRead == -1)
{
errnoWarn("error reading symlink %s", fileName);
return;
}
else
{
oldSymlink[bufRead] = 0; // needs termination.
if (!fileExists(oldSymlink))
{
warn("symlink target %s does not exist!", oldSymlink);
return;
}
else
verbose(2,"got symlink %s\n", oldSymlink);
}
// check that the symlink and object base match
base = strrchr(oldSymlink, '/');
if (base == NULL)
{
warn("symlink %s in fileName %s not absolute path",oldSymlink, fileName);
return;
}
else
{
base++;
char *dot = strchr(base, '.');
if (dot == NULL)
{
warn("symlink %s in fileName %s does not have suffix", oldSymlink, fileName);
return;
}
else
{
char saveChar = *dot;
*dot = 0;
if (!sameString(table, base))
{
warn("symlink %s doesn't match table %s", base, table);
return;
}
*dot = saveChar;
}
}
/* Note "fileIndex" for .bai has been made obsolete
so we'll just hard-wire in the .bai support */
if (!endsWith(fileName, ".bam"))
break;
safecat(fileName, sizeof(fileName), ".bai");
}
}
}