Example #1
0
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;
}
Example #4
0
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;
}
Example #5
0
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;
}
Example #7
0
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);
}
Example #10
0
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);
}
Example #13
0
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);
    }
}
Example #14
0
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;
}
Example #15
0
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);
}
Example #16
0
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;
}
Example #17
0
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;
}
Example #19
0
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);
    }
}
Example #20
0
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));
}
Example #21
0
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);
}
Example #22
0
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);
    }
}
Example #23
0
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);
}
Example #24
0
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 );
}
Example #25
0
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()	*/
Example #30
0
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");
	}

    }
}