static void initTable(struct sqlConnection *conn, char *table, boolean nuke)
/* build tables */
{
char *sql = NULL;
char path[256];
if (nuke)
sqlDropTable(conn, table);
if (!sqlTableExists(conn, table))
{
safef(path,sizeof(path),"%s/%s.sql",sqlPath,table);
readInGulp(path, &sql, NULL);
sqlUpdate(conn, sql);
}
}
static void advanceType(struct sqlConnection *conn, int taxon, char *oldType, char *newType)
/* find any remaining type refSeq that couldn't be resolved and demote
* them to type genbank
*/
{
struct dyString *dy = dyStringNew(0);
dyStringClear(dy);
dyStringPrintf(dy,
"update vgPrb set type='%s', tName=''"
" where taxon = %d and state = 'new' and type='%s'"
,newType,taxon,oldType);
sqlUpdate(conn, dy->string);
dyStringFree(&dy);
}
void loadIntoDatabase(char *database, char *createString, char *table, char *tabName)
/* Load tabbed file into database table. */
{
struct sqlConnection *conn = sqlConnect(database);
struct dyString *ds = newDyString(2048);
dyStringPrintf(ds, createString, table);
sqlRemakeTable(conn, table, ds->string);
dyStringClear(ds);
dyStringPrintf(ds,
"LOAD data local infile '%s' into table %s", tabName, table);
sqlUpdate(conn, ds->string);
sqlDisconnect(&conn);
freeDyString(&ds);
}
static void deleteJoin(struct sqlDeleter* sd, struct sqlConnection *conn,
char* table, char* column)
/* delete by creating a new table with a join */
{
char query[512], newTmpTable[64], oldTmpTable[64];
if (sd->accLoader != NULL)
{
/* build table, free to indicate it's completed */
assert(!sd->deletesDone);
sqlRemakeTable(conn, GB_DELETE_TMP, createGbDeleteTmp);
sqlUpdaterCommit(sd->accLoader, conn);
sqlUpdaterFree(&sd->accLoader);
}
sd->deletesDone = TRUE;
/* remove existing tmp tables */
safef(newTmpTable, sizeof(newTmpTable), "%s_new_tmp", table);
safef(oldTmpTable, sizeof(oldTmpTable), "%s_old_tmp", table);
sqlDropTable(conn, newTmpTable);
sqlDropTable(conn, oldTmpTable);
gbSqlDupTableDef(conn, table, newTmpTable);
/* do join into new table of entries not in accession table */
safef(query, sizeof(query),
"INSERT INTO %s SELECT %s.* FROM %s LEFT JOIN %s "
"ON (%s.%s = %s.acc) WHERE %s.acc IS NULL",
newTmpTable, table, table, GB_DELETE_TMP, table, column,
GB_DELETE_TMP, GB_DELETE_TMP);
sqlUpdate(conn, query);
/* Now swap the table into place */
safef(query, sizeof(query), "RENAME TABLE %s TO %s, %s TO %s",
table, oldTmpTable, newTmpTable, table);
sqlUpdate(conn, query);
sqlDropTable(conn, oldTmpTable);
}
void dbRIPSaveToDbEscaped(struct sqlConnection *conn, struct dbRIP *el, char *tableName, int updateSize)
/* Save dbRIP as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size.
* of a string that would contain the entire query. Automatically
* escapes all simple strings (not arrays of string) but may be slower than dbRIPSaveToDb().
* For example automatically copies and converts:
* "autosql's features include" --> "autosql\'s features include"
* before inserting into database. */
{
struct dyString *update = newDyString(updateSize);
char *chrom, *name, *strand, *originalId, *forwardPrimer, *reversePrimer, *polyClass, *polyFamily, *polySubfamily, *polySeq, *polySource, *reference, *ascertainingMethod, *remarks, *disease, *genoRegion;
chrom = sqlEscapeString(el->chrom);
name = sqlEscapeString(el->name);
strand = sqlEscapeString(el->strand);
originalId = sqlEscapeString(el->originalId);
forwardPrimer = sqlEscapeString(el->forwardPrimer);
reversePrimer = sqlEscapeString(el->reversePrimer);
polyClass = sqlEscapeString(el->polyClass);
polyFamily = sqlEscapeString(el->polyFamily);
polySubfamily = sqlEscapeString(el->polySubfamily);
polySeq = sqlEscapeString(el->polySeq);
polySource = sqlEscapeString(el->polySource);
reference = sqlEscapeString(el->reference);
ascertainingMethod = sqlEscapeString(el->ascertainingMethod);
remarks = sqlEscapeString(el->remarks);
disease = sqlEscapeString(el->disease);
genoRegion = sqlEscapeString(el->genoRegion);
dyStringPrintf(update, "insert into %s values ( '%s',%u,%u,'%s',%u,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%g,%d,%d,'%s','%s')",
tableName, chrom, el->chromStart , el->chromEnd , name, el->score , strand, originalId, forwardPrimer, reversePrimer, polyClass, polyFamily, polySubfamily, polySeq, polySource, reference, ascertainingMethod, remarks, el->tm , el->filledSize , el->emptySize , disease, genoRegion);
sqlUpdate(conn, update->string);
freeDyString(&update);
freez(&chrom);
freez(&name);
freez(&strand);
freez(&originalId);
freez(&forwardPrimer);
freez(&reversePrimer);
freez(&polyClass);
freez(&polyFamily);
freez(&polySubfamily);
freez(&polySeq);
freez(&polySource);
freez(&reference);
freez(&ascertainingMethod);
freez(&remarks);
freez(&disease);
freez(&genoRegion);
}
void ggMrnaBlockSaveToDbEscaped(struct sqlConnection *conn, struct ggMrnaBlock *el, char *tableName, int updateSize)
/* Save ggMrnaBlock as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size.
* of a string that would contain the entire query. Automatically
* escapes all simple strings (not arrays of string) but may be slower than ggMrnaBlockSaveToDb().
* For example automatically copies and converts:
* "autosql's features include" --> "autosql\'s features include"
* before inserting into database. */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( %d,%d,%d,%d)",
tableName, el->qStart , el->qEnd , el->tStart , el->tEnd );
sqlUpdate(conn, update->string);
freeDyString(&update);
}
void ggMrnaAliSaveToDb(struct sqlConnection *conn, struct ggMrnaAli *el, char *tableName, int updateSize)
/* Save ggMrnaAli as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. Note that strings must be escaped to allow insertion into the database.
* For example "autosql's features include" --> "autosql\'s features include"
* If worried about this use ggMrnaAliSaveToDbEscaped() */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( '%s',%d,%d,'%s','%s',%d,%d,%u,%d,%d,%d,%d, NULL )",
tableName, el->tName, el->tStart, el->tEnd, el->strand, el->qName, el->qStart, el->qEnd, el->baseCount, el->orientation, el->hasIntrons, el->milliScore, el->blockCount);
sqlUpdate(conn, update->string);
freeDyString(&update);
}
void polyGenotypeSaveToDb(struct sqlConnection *conn, struct polyGenotype *el, char *tableName, int updateSize)
/* Save polyGenotype as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. Note that strings must be escaped to allow insertion into the database.
* For example "autosql's features include" --> "autosql\'s features include"
* If worried about this use polyGenotypeSaveToDbEscaped() */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( '%s','%s',%d,%d,%d,%d,%g,%g)",
tableName, el->name, el->ethnicGroup, el->plusPlus, el->plusMinus, el->minusMinus, el->sampleSize, el->alleleFrequency, el->unbiasedHeterozygosity);
sqlUpdate(conn, update->string);
freeDyString(&update);
}
void dbRIPSaveToDb(struct sqlConnection *conn, struct dbRIP *el, char *tableName, int updateSize)
/* Save dbRIP as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. Note that strings must be escaped to allow insertion into the database.
* For example "autosql's features include" --> "autosql\'s features include"
* If worried about this use dbRIPSaveToDbEscaped() */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( '%s',%u,%u,'%s',%u,'%s','%s','%s','%s','%s','%s','%s',%s,'%s',%s,'%s','%s',%g,%d,%d,'%s','%s')",
tableName, el->chrom, el->chromStart, el->chromEnd, el->name, el->score, el->strand, el->originalId, el->forwardPrimer, el->reversePrimer, el->polyClass, el->polyFamily, el->polySubfamily, el->polySeq, el->polySource, el->reference, el->ascertainingMethod, el->remarks, el->tm, el->filledSize, el->emptySize, el->disease, el->genoRegion);
sqlUpdate(conn, update->string);
freeDyString(&update);
}
void hapmapPrimateAllelesSaveToDb(struct sqlConnection *conn, struct hapmapPrimateAlleles *el, char *tableName, int updateSize)
/* Save hapmapPrimateAlleles as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. Note that strings must be escaped to allow insertion into the database.
* For example "autosql's features include" --> "autosql\'s features include"
* If worried about this use hapmapPrimateAllelesSaveToDbEscaped() */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( '%s',%u,%u,'%s',%u,'%c','%c','%c','%s',%u,'%c','%s',%u,'%s',%u,'%c','%s',%u)",
tableName, el->chrom, el->chromStart, el->chromEnd, el->name, el->score, el->strand, el->refAllele, el->otherAllele, el->chimpChrom, el->chimpPos, el->chimpStrand, el->chimpAllele, el->chimpQual, el->rhesusChrom, el->rhesusPos, el->rhesusStrand, el->rhesusAllele, el->rhesusQual);
sqlUpdate(conn, update->string);
freeDyString(&update);
}
void cnpSharpCutoffSaveToDb(struct sqlConnection *conn, struct cnpSharpCutoff *el, char *tableName, int updateSize)
/* Save cnpSharpCutoff as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. Note that strings must be escaped to allow insertion into the database.
* For example "autosql's features include" --> "autosql\'s features include"
* If worried about this use cnpSharpCutoffSaveToDbEscaped() */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( '%s',%u,%g)",
tableName, el->sample, el->batch, el->value);
sqlUpdate(conn, update->string);
freeDyString(&update);
}
void genotypeSaveToDb(struct sqlConnection *conn, struct genotype *el, char *tableName, int updateSize)
/* Save genotype as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. Note that strings must be escaped to allow insertion into the database.
* For example "autosql's features include" --> "autosql\'s features include"
* If worried about this use genotypeSaveToDbEscaped() */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( '%s','%s','%s',%d,'%c','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
tableName, el->rsId, el->SNPalleles, el->chrom, el->chromStart, el->strand, el->assembly, el->center, el->protLSID, el->assayLSID, el->panelLSID, el->NA06985, el->NA06991, el->NA06993, el->NA06993_dup, el->NA06994, el->NA07000, el->NA07019, el->NA07022, el->NA07029, el->NA07034, el->NA07048, el->NA07055, el->NA07056, el->NA07345, el->NA07348, el->NA07357, el->NA10830, el->NA10831, el->NA10835, el->NA10838, el->NA10839, el->NA10846, el->NA10847, el->NA10851, el->NA10854, el->NA10855, el->NA10856, el->NA10857, el->NA10859, el->NA10860, el->NA10861, el->NA10863, el->NA11829, el->NA11830, el->NA11831, el->NA11832, el->NA11839, el->NA11840, el->NA11881, el->NA11882, el->NA11992, el->NA11993, el->NA11993_dup, el->NA11994, el->NA11995, el->NA12003, el->NA12003_dup, el->NA12004, el->NA12005, el->NA12006, el->NA12043, el->NA12044, el->NA12056, el->NA12057, el->NA12144, el->NA12145, el->NA12146, el->NA12154, el->NA12155, el->NA12156, el->NA12156_dup, el->NA12234, el->NA12236, el->NA12239, el->NA12248, el->NA12248_dup, el->NA12249, el->NA12264, el->NA12707, el->NA12716, el->NA12717, el->NA12740, el->NA12750, el->NA12751, el->NA12752, el->NA12753, el->NA12760, el->NA12761, el->NA12762, el->NA12763, el->NA12801, el->NA12802, el->NA12812, el->NA12813, el->NA12814, el->NA12815, el->NA12864, el->NA12865, el->NA12872, el->NA12873, el->NA12874, el->NA12875, el->NA12878, el->NA12891, el->NA12892);
sqlUpdate(conn, update->string);
freeDyString(&update);
}
static void loadDatabase(char* database,
char* table,
struct traceInfo* traceInfoList)
/* Load the trace information into the database. */
{
struct sqlConnection *conn = NULL;
FILE *tmpFh = NULL;
struct traceInfo* trace = NULL;
char query[512];
/* Write it as tab-delimited file. */
printf("Writing tab-delimited file %s\n", TMP_TAB_FILE);
tmpFh = mustOpen(TMP_TAB_FILE, "w");
for (trace = traceInfoList; trace != NULL; trace = trace->next)
{
traceInfoTabOut(trace, tmpFh);
}
carefulClose(&tmpFh);
/* Import into database. */
printf("Importing into %d rows into %s\n",
slCount(traceInfoList), database);
conn = sqlConnect(database);
if (!sqlTableExists(conn, table))
errAbort("You need to create the table first (with %s.sql)", table);
sqlSafef(query, sizeof query, "delete from %s", table);
sqlUpdate(conn, query);
sqlSafef(query, sizeof query, "load data local infile '%s' into table %s", TMP_TAB_FILE,
table);
sqlUpdate(conn, query);
sqlDisconnect(&conn);
unlink(TMP_TAB_FILE);
printf("Import complete\n");
}
static void markNoneVgPrbAli(struct sqlConnection *conn, int fromTaxon, char *db, char *table)
/* mark records in vgPrbAli that did not find any alignment for db */
{
struct dyString *dy = dyStringNew(0);
dyStringClear(dy);
dyStringPrintf(dy,
"update %s a, vgPrb e"
" set a.status = 'none'"
" where a.status = 'new'"
" and a.db = '%s' and e.taxon = %d"
" and a.vgPrb = e.id"
,table,db,fromTaxon);
sqlUpdate(conn, dy->string);
dyStringFree(&dy);
}
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;
}
void loadOneTable(char *database, struct sqlConnection *conn, char *tempName, char *tableName)
/* Load .tab file tempName into tableName and remove tempName. */
{
struct dyString *query = newDyString(1024);
verbose(1, "Loading up table %s\n", tableName);
if (sqlTableExists(conn, tableName))
{
sqlDyStringPrintf(query, "DROP table %s", tableName);
sqlUpdate(conn, query->string);
}
/* Create first part of table definitions, the fields. */
dyStringClear(query);
sqlDyStringPrintf(query, createRmskOut, tableName);
/* Create the indexes */
if (!noSplit)
{
dyStringAppend(query, " INDEX(bin))\n");
}
else
{
int indexLen = hGetMinIndexLength(database);
sqlDyStringPrintf(query, " INDEX(genoName(%d),bin))\n", indexLen);
}
sqlUpdate(conn, query->string);
/* Load database from tab-file. */
dyStringClear(query);
sqlDyStringPrintf(query, "LOAD data local infile '%s' into table %s",
tempName, tableName);
sqlUpdate(conn, query->string);
remove(tempName);
}
void tigrCmrGeneSaveToDb(struct sqlConnection *conn, struct tigrCmrGene *el, char *tableName, int updateSize)
/* Save tigrCmrGene as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. Note that strings must be escaped to allow insertion into the database.
* For example "autosql's features include" --> "autosql\'s features include"
* If worried about this use tigrCmrGeneSaveToDbEscaped() */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( %d,'%s',%u,%u,'%s',%u,'%s',%s,'%s','%s','%s',%u,%u,%s,%s,'%s','%s',%f,%f,%f,'%s')",
tableName, el->bin, el->chrom, el->chromStart, el->chromEnd, el->name, el->score, el->strand, el->tigrCommon, el->tigrGene, el->tigrECN, el->primLocus, el->tigrLength, el->tigrPepLength, el->tigrMainRole, el->tigrSubRole, el->swissProt, el->genbank, el->tigrMw, el->tigrPi, el->tigrGc, el->goTerm);
sqlUpdate(conn, update->string);
freeDyString(&update);
}
void dropUserTable(char *host, char *user, char *password, char *database)
/* Drop database if it exists. */
{
if (databaseExists(host, user, password, database))
{
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
if (sqlTableExists(conn, userTable))
{
char query[512];
sqlSafef(query, sizeof(query), "drop table %s", userTable);
sqlUpdate(conn, query);
}
sqlDisconnect(&conn);
}
}
void makeItemsItemSaveToDb(struct sqlConnection *conn, struct makeItemsItem *el, char *tableName, int updateSize)
/* Save makeItemsItem as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. Note that strings must be escaped to allow insertion into the database.
* For example "autosql's features include" --> "autosql\'s features include"
* If worried about this use makeItemsItemSaveToDbEscaped() */
{
struct dyString *update = newDyString(updateSize);
dyStringPrintf(update, "insert into %s values ( %u,'%s',%u,%u,'%s',%u,'%s',%u,%u,%u,%s,%u)",
tableName, el->bin, el->chrom, el->chromStart, el->chromEnd, el->name, el->score, el->strand, el->thickStart, el->thickEnd, el->itemRgb, el->description, el->id);
sqlUpdate(conn, update->string);
freeDyString(&update);
}
static void populateMissingVgPrbAli(struct sqlConnection *conn, int taxon, char *db, char *table)
/* populate vgPrbAli for db */
{
struct dyString *dy = dyStringNew(0);
dyStringClear(dy);
dyStringPrintf(dy,
"insert into %s"
" select distinct '%s', e.id, 'new' from vgPrb e"
" left join %s a on e.id = a.vgPrb and a.db = '%s'"
" where a.vgPrb is NULL "
" and e.taxon = %d"
,table,db,table,db,taxon);
sqlUpdate(conn, dy->string);
dyStringFree(&dy);
}
void edwAddSubmitJob(struct sqlConnection *conn, char *userEmail, char *url, boolean update)
/* Add submission job to table and wake up daemon. */
{
/* Create command and add it to edwSubmitJob table. */
char command[strlen(url) + strlen(userEmail) + 256];
safef(command, sizeof(command), "edwSubmit %s'%s' %s", (update ? "-update " : ""), url, userEmail);
char query[strlen(command)+128];
sqlSafef(query, sizeof(query), "insert edwSubmitJob (commandLine) values('%s')", command);
sqlUpdate(conn, query);
/* Write sync signal (any string ending with newline) to fifo to wake up daemon. */
FILE *fifo = mustOpen("../userdata/edwSubmit.fifo", "w");
fputc('\n', fifo);
carefulClose(&fifo);
}
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);
}
void edwRunOnIds(char *program, char *queryString)
/* edwRunOnIds - Run a edw command line program (one that takes startId endId as it's two parameters) for a range of ids,
* putting it on edwJob queue. */
{
struct sqlConnection *conn = edwConnectReadWrite();
struct slName *id, *idList = sqlQuickList(conn, queryString);
for (id = idList; id != NULL; id = id->next)
{
char query[512];
sqlSafef(query, sizeof(query), "insert into %s (commandLine) values ('%s %s %s')",
runTable, program, id->name, id->name);
sqlUpdate(conn, query);
}
}
void gtexGeneBedSaveToDb(struct sqlConnection *conn, struct gtexGeneBed *el, char *tableName, int updateSize)
/* Save gtexGeneBed as a row to the table specified by tableName.
* As blob fields may be arbitrary size updateSize specifies the approx size
* of a string that would contain the entire query. Arrays of native types are
* converted to comma separated strings and loaded as such, User defined types are
* inserted as NULL. This function automatically escapes quoted strings for mysql. */
{
struct dyString *update = newDyString(updateSize);
char *expScoresArray;
expScoresArray = sqlFloatArrayToString(el->expScores, el->expCount);
sqlDyStringPrintf(update, "insert into %s values ( '%s',%u,%u,'%s',%u,'%s','%s','%s',%u,'%s')",
tableName, el->chrom, el->chromStart, el->chromEnd, el->name, el->score, el->strand, el->geneId, el->geneType, el->expCount, expScoresArray );
sqlUpdate(conn, update->string);
freeDyString(&update);
freez(&expScoresArray);
}
void edwFileResetTags(struct sqlConnection *conn, struct edwFile *ef, char *newTags)
/* Reset tags on file, strip out old validation and QA, schedule new validation and QA. */
/* Remove existing QA records and rerun QA agent on given file. */
{
long long fileId = ef->id;
/* Update database to let people know format revalidation is in progress. */
char query[4*1024];
sqlSafef(query, sizeof(query), "update edwFile set errorMessage = '%s' where id=%lld",
"Revalidation in progress.", fileId);
sqlUpdate(conn, query);
/* Update tags for file in edwFile table. */
sqlSafef(query, sizeof(query), "update edwFile set tags='%s' where id=%lld", newTags, fileId);
sqlUpdate(conn, query);
/* Get rid of records referring to file in other validation and qa tables. */
sqlSafef(query, sizeof(query), "delete from edwFastqFile where fileId=%lld", fileId);
sqlUpdate(conn, query);
sqlSafef(query, sizeof(query),
"delete from edwQaPairSampleOverlap where elderFileId=%lld or youngerFileId=%lld",
fileId, fileId);
sqlUpdate(conn, query);
sqlSafef(query, sizeof(query),
"delete from edwQaPairCorrelation where elderFileId=%lld or youngerFileId=%lld",
fileId, fileId);
sqlUpdate(conn, query);
sqlSafef(query, sizeof(query), "delete from edwQaEnrich where fileId=%lld", fileId);
sqlUpdate(conn, query);
sqlSafef(query, sizeof(query), "delete from edwQaContam where fileId=%lld", fileId);
sqlUpdate(conn, query);
sqlSafef(query, sizeof(query), "delete from edwQaRepeat where fileId=%lld", fileId);
sqlUpdate(conn, query);
sqlSafef(query, sizeof(query),
"delete from edwQaPairedEndFastq where fileId1=%lld or fileId2=%lld",
fileId, fileId);
sqlUpdate(conn, query);
/* schedule validator */
edwAddQaJob(conn, ef->id);
}
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 void insertHubUrlInStatus(char *url)
/* add a url to the hubStatus table */
{
struct sqlConnection *conn = hConnectCentral();
char query[4096];
char *statusTable = getHubStatusTableName();
if (sqlFieldIndex(conn, statusTable, "firstAdded") >= 0)
sqlSafef(query, sizeof(query), "insert into %s (hubUrl,firstAdded) values (\"%s\",now())",
statusTable, url);
else
sqlSafef(query, sizeof(query), "insert into %s (hubUrl) values (\"%s\")",
statusTable, url);
sqlUpdate(conn, query);
hDisconnectCentral(&conn);
}
void cdwRetryJob(char *database, char *jobTable)
/* cdwRetryJob - Add jobs that failed back to a cdwJob format queue.. */
{
/* Figure out thresholds for age. */
long long now = cdwNow();
long long maxTimeThreshold = now - 24*3600 * maxTime;
/* Get list of all jobs ordered by ID with most recent job first. */
struct sqlConnection *conn = sqlConnect(database);
char query[512];
sqlSafef(query, sizeof(query), "select * from %s order by id desc", jobTable);
struct cdwJob *job, *jobList = cdwJobLoadByQuery(conn, query);
/* Loop through job list looking for commandLines that failed or timed out in their
* most recent attempt. */
struct hash *jobHash = hashNew(0);
struct slRef *failRef, *failRefList = NULL;
for (job = jobList; job != NULL; job = job->next)
{
if (hashLookup(jobHash, job->commandLine) == NULL)
{
hashAdd(jobHash, job->commandLine, job);
if (job->id >= minId && job->startTime != 0)
{
boolean timedOut = (job->startTime < maxTimeThreshold && job->endTime == 0);
if (job->returnCode != 0 || timedOut)
{
refAdd(&failRefList, job);
}
}
}
}
/* Loop through failed list printing or retrying */
for (failRef = failRefList; failRef != NULL; failRef = failRef->next)
{
job = failRef->val;
if (dry)
printf("%s\n", job->commandLine);
else
{
sqlSafef(query, sizeof(query),
"insert into %s (commandLine) values ('%s')", jobTable, job->commandLine);
sqlUpdate(conn, query);
}
}
}
void edwCreateNewUser(char *email)
/* Create new user, checking that user does not already exist. */
{
/* Now make sure user is not already in user table. */
struct sqlConnection *conn = edwConnectReadWrite();
struct dyString *query = dyStringNew(0);
sqlDyStringPrintf(query, "select count(*) from edwUser where email = '%s'", email);
if (sqlQuickNum(conn, query->string) > 0)
errAbort("User %s already exists", email);
/* Do database insert. */
dyStringClear(query);
sqlDyStringPrintf(query, "insert into edwUser (email) values('%s')", email);
sqlUpdate(conn, query->string);
sqlDisconnect(&conn);
}
