Пример #1
0
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;
}
Пример #2
0
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;
}
Пример #3
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);
}