struct estOrientInfo *estOrientInfoLoadWhere(struct sqlConnection *conn, char *table, char *where)
/* Load all estOrientInfo from table that satisfy where clause. The
* where clause may be NULL in which case whole table is loaded
* Dispose of this with estOrientInfoFreeList(). */
{
struct estOrientInfo *list = NULL, *el;
struct dyString *query = dyStringNew(256);
struct sqlResult *sr;
char **row;
sqlDyStringPrintf(query, "select * from %s", table);
if (where != NULL)
dyStringPrintf(query, " where %s", where);
sr = sqlGetResult(conn, query->string);
int off = sqlFieldColumn(sr, "bin") + 1; // offset of data; function returns -1 if no bin
while ((row = sqlNextRow(sr)) != NULL)
{
el = estOrientInfoLoad(row+off);
slAddHead(&list, el);
}
slReverse(&list);
sqlFreeResult(&sr);
dyStringFree(&query);
return list;
}
static double getSignalAt(char *table, struct bed *cluster)
/* Get (average) signal from table entries that overlap cluster */
{
struct sqlConnection *conn = hAllocConn(database);
int count = 0;
double sum = 0;
if (sqlTableExists(conn, table)) // Table might be withdrawn from data thrash
{
int rowOffset;
struct sqlResult *sr = hRangeQuery(conn, table, cluster->chrom, cluster->chromStart,
cluster->chromEnd, NULL, &rowOffset);
int signalCol = sqlFieldColumn(sr, "signalValue");
if (signalCol < 0)
internalErr();
char **row;
while ((row = sqlNextRow(sr)) != NULL)
{
count += 1;
sum += sqlDouble(row[signalCol]);
}
sqlFreeResult(&sr);
}
hFreeConn(&conn);
if (count > 0)
return sum/count;
else
return 0;
}
boolean checkMaxTableSizeExceeded(char *table)
/* check if max table size has been exceeded, send email warning if so */
{
boolean squealed = FALSE;
long long dataLength = 0;
long long dataFree = 0;
struct sqlResult *sr;
char **row;
char query[256];
sqlSafef(query, sizeof(query), "show table status like '%s'", table );
sr = sqlGetResult(conn, query);
row = sqlNextRow(sr);
if (!row)
errAbort("error fetching table status");
int dlField = sqlFieldColumn(sr, "Data_length");
if (dlField == -1)
errAbort("error finding field 'Data_length' in show table status resultset");
dataLength = sqlLongLong(row[dlField]);
int dfField = sqlFieldColumn(sr, "Data_free");
if (dfField == -1)
errAbort("error finding field 'Data_free' in show table status resultset");
dataFree = sqlLongLong(row[dfField]);
verbose(1, "%s: Data_length=%lld Data_free=%lld\n\n", table, dataLength, dataFree);
if ((dataLength / (1024 * 1024 * 1024)) >= squealSize)
{
char msg[256];
char cmdLine[256];
char *emailList = "[email protected] [email protected] [email protected]";
safef(msg, sizeof(msg), "BIG HGCENTRAL TABLE %s data_length: %lld data_free: %lld\n"
, table, dataLength, dataFree);
printf("%s", msg);
safef(cmdLine, sizeof(cmdLine),
"echo '%s'|mail -s 'WARNING hgcentral cleanup detected data_length max size %d GB exceeded' %s"
, msg
, squealSize
, emailList
);
system(cmdLine);
squealed = TRUE;
}
sqlFreeResult(&sr);
return squealed;
}
void dbTrash(char *db)
/* dbTrash - drop tables from a database older than specified N hours. */
{
char query[256];
struct sqlResult *sr;
char **row;
int updateTimeIx;
int createTimeIx;
int dataLengthIx;
int indexLengthIx;
int nameIx;
int timeIxUsed;
unsigned long long totalSize = 0;
struct slName *tableNames = NULL; /* subject to age limits */
struct hash *expiredHash = newHash(10);
struct hash *notExpiredHash = newHash(10);
struct sqlConnection *conn = sqlConnect(db);
if (extFileCheck)
checkExtFile(conn);
time_t ageSeconds = (time_t)(ageHours * 3600); /* age in seconds */
safef(query,sizeof(query),"select name from %s WHERE "
"lastUse < DATE_SUB(NOW(), INTERVAL %ld SECOND);", CT_META_INFO,ageSeconds);
sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
hashAddInt(expiredHash, row[0], 1);
sqlFreeResult(&sr);
safef(query,sizeof(query),"select name from %s WHERE "
"lastUse >= DATE_SUB(NOW(), INTERVAL %ld SECOND);",CT_META_INFO,ageSeconds);
sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
hashAddInt(notExpiredHash, row[0], 1);
sqlFreeResult(&sr);
/* run through the table status business to get table size information */
safef(query,sizeof(query),"show table status");
sr = sqlGetResult(conn, query);
nameIx = sqlFieldColumn(sr, "Name");
createTimeIx = sqlFieldColumn(sr, "Create_time");
updateTimeIx = sqlFieldColumn(sr, "Update_time");
dataLengthIx = sqlFieldColumn(sr, "Data_length");
indexLengthIx = sqlFieldColumn(sr, "Index_length");
while ((row = sqlNextRow(sr)) != NULL)
{
struct tm tm;
time_t timep = 0;
/* if not doing history too, and this is the history table, next row */
if ((!historyToo) && (sameWord(row[nameIx],"history")))
continue;
/* also skip the metaInfo table */
if ((!historyToo) && (sameWord(row[nameIx],CT_META_INFO)))
continue;
/* don't delete the extFile table */
if (sameWord(row[nameIx],CT_EXTFILE))
continue;
/* Update_time is sometimes NULL on MySQL 5
* so if it fails, then check the Create_time
*/
timeIxUsed = updateTimeIx;
if ((row[updateTimeIx] != NULL) &&
(sscanf(row[updateTimeIx], "%4d-%2d-%2d %2d:%2d:%2d",
&(tm.tm_year), &(tm.tm_mon), &(tm.tm_mday),
&(tm.tm_hour), &(tm.tm_min), &(tm.tm_sec)) != 6) )
{
timeIxUsed = createTimeIx;
if (sscanf(row[createTimeIx], "%4d-%2d-%2d %2d:%2d:%2d",
&(tm.tm_year), &(tm.tm_mon), &(tm.tm_mday),
&(tm.tm_hour), &(tm.tm_min), &(tm.tm_sec)) != 6)
{
verbose(2,"%s %s %s\n",
row[createTimeIx],row[updateTimeIx],row[nameIx]);
errAbort("could not parse date %s or %s on table %s\n",
row[createTimeIx], row[updateTimeIx], row[nameIx]);
}
}
tm.tm_year -= 1900;
tm.tm_mon -= 1;
tm.tm_isdst = -1; /* do not know timezone, figure it out */
timep = mktime(&tm);
if (hashLookup(expiredHash,row[nameIx]))
{
slNameAddHead(&tableNames, row[nameIx]);
verbose(3,"%s %ld drop %s\n",row[timeIxUsed], (unsigned long)timep,
row[nameIx]);
/* If sizes are non-NULL, add them up */
if ( ((char *)NULL != row[dataLengthIx]) &&
((char *)NULL != row[indexLengthIx]) )
totalSize += sqlLongLong(row[dataLengthIx])
+ sqlLongLong(row[indexLengthIx]);
hashRemove(expiredHash, row[nameIx]);
}
else
{
if (hashLookup(notExpiredHash,row[nameIx]))
verbose(3,"%s %ld OK %s\n",row[timeIxUsed], (unsigned long)timep,
row[nameIx]);
else
{ /* table exists, but not in metaInfo, is it old enough ? */
if (timep < dropTime)
{
slNameAddHead(&tableNames, row[nameIx]);
verbose(2,"%s %ld dropt %s\n",
row[timeIxUsed], (unsigned long)timep, row[nameIx]);
/* If sizes are non-NULL, add them up */
if ( ((char *)NULL != row[dataLengthIx]) &&
((char *)NULL != row[indexLengthIx]) )
totalSize += sqlLongLong(row[dataLengthIx])
+ sqlLongLong(row[indexLengthIx]);
}
else
verbose(3,"%s %ld OKt %s\n",row[timeIxUsed],
(unsigned long)timep, row[nameIx]);
}
}
}
sqlFreeResult(&sr);
/* perhaps the table was already dropped, but not from the metaInfo */
struct hashEl *elList = hashElListHash(expiredHash);
struct hashEl *el;
for (el = elList; el != NULL; el = el->next)
{
verbose(2,"%s exists in %s only\n", el->name, CT_META_INFO);
if (drop)
ctTouchLastUse(conn, el->name, FALSE); /* removes metaInfo row */
}
if (drop)
{
if (tableNames)
{
char comment[256];
struct slName *el;
int droppedCount = 0;
/* customTrash DB user permissions do not have permissions to
* drop tables. Must use standard special user that has all
* permissions. If we are not using the standard user at this
* point, then switch to it.
*/
if (sameWord(db,CUSTOM_TRASH))
{
sqlDisconnect(&conn);
conn = sqlConnect(db);
}
for (el = tableNames; el != NULL; el = el->next)
{
verbose(2,"# drop %s\n", el->name);
sqlDropTable(conn, el->name);
ctTouchLastUse(conn, el->name, FALSE); /* removes metaInfo row */
++droppedCount;
}
/* add a comment to the history table and finish up connection */
safef(comment, sizeof(comment),
"Dropped %d tables with total size %llu", droppedCount, totalSize);
verbose(2,"# %s\n", comment);
hgHistoryComment(conn, comment);
}
}
sqlDisconnect(&conn);
}
