static void removeGenBankMrna(char *db, struct sqlConnection *conn, struct gbSelect* select,
struct sqlDeleter* deleter)
/* delete all genbank mRNA alignments */
{
if (select->orgCats & GB_NATIVE)
{
struct slName* chrom;
char table[64];
sqlDropTable(conn, ALL_MRNA_TBL);
sqlDeleterDel(deleter, conn, MRNA_ORIENTINFO_TBL, "name");
for (chrom = getChromNames(db); chrom != NULL; chrom = chrom->next)
{
safef(table, sizeof(table), "%s_mrna", chrom->name);
sqlDropTable(conn, table);
}
if (haveMgc)
sqlDropTable(conn, MGC_FULL_MRNA_TBL);
if (haveOrfeome)
sqlDropTable(conn, ORFEOME_MRNA_TBL);
}
if (select->orgCats & GB_XENO)
{
sqlDropTable(conn, XENO_MRNA_TBL);
}
}
static void removeRefSeq(struct sqlConnection *conn, struct gbSelect* select,
struct sqlDeleter* deleter)
/* delete all refseq alignments */
{
if (select->orgCats & GB_NATIVE)
{
sqlDropTable(conn, REFSEQ_ALI_TBL);
sqlDeleterDel(deleter, conn, MRNA_ORIENTINFO_TBL, "name");
}
if (select->orgCats & GB_XENO)
{
sqlDropTable(conn, XENO_REFSEQ_ALI_TBL);
}
}
void hgsqlSwapTables(char *database, char *table1, char *table2, char *table3)
/* hgsqlSwapTables - swaps tables in database. */
{
struct sqlConnection *conn = hAllocConn(database);
boolean noTable2 = FALSE;
if (!sqlTableExists(conn, table1))
errAbort("%s does not exist",table1);
if (!sqlTableExists(conn, table2))
{
if (okNoTable2)
noTable2 = TRUE;
else
errAbort("%s does not exist", table2);
}
if (sqlTableExists(conn, table3))
{
if (dropTable3)
sqlDropTable(conn, table3);
else
errAbort("%s exists", table3);
}
if (!noTable2)
sqlRenameTable(conn, table2, table3);
sqlRenameTable(conn, table1, table2);
}
void ccdsRenameTable(struct sqlConnection *conn, char *oldTable, char *newTable)
/* rename a database table */
{
char query[2048];
sqlSafef(query, sizeof(query), "RENAME TABLE %s TO %s", oldTable, newTable);
sqlDropTable(conn, newTable);
sqlUpdate(conn, query);
}
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 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 dropAll(char *database)
/* Drop all gbLoadRna tables from database. */
{
struct slName *tables, *tbl;
struct sqlConnection *conn;
gbVerbEnter(1, "dropAll");
conn = hAllocConn(database);
gbLockDb(conn, NULL);
tables = getTableList(conn);
for (tbl = tables; tbl != NULL; tbl = tbl->next)
sqlDropTable(conn, tbl->name);
slFreeList(&tables);
gbUnlockDb(conn, NULL);
hFreeConn(&conn);
gbVerbLeave(1, "dropAll");
}
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;
// expiredTableNames: table exists and is in metaInfo and subject to age limits
struct slName *expiredTableNames = NULL;
struct slName *lostTables = NULL; // tables existing but not in metaInfo
unsigned long long lostTableCount = 0;
struct hash *expiredHash = newHash(10); // as determined by metaInfo
struct hash *notExpiredHash = newHash(10);
struct sqlConnection *conn = sqlConnect(db);
if (extFileCheck)
checkExtFile(conn);
time_t ageSeconds = (time_t)(ageHours * 3600); /* age in seconds */
sqlSafef(query,sizeof(query),"select name,UNIX_TIMESTAMP(lastUse) 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], sqlSigned(row[1]));
sqlFreeResult(&sr);
sqlSafef(query,sizeof(query),"select name,UNIX_TIMESTAMP(lastUse) 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], sqlSigned(row[1]));
sqlFreeResult(&sr);
if (tableStatus) // show table status is very expensive, use only when asked
{
/* run through the table status business to get table size information */
sqlSafef(query,sizeof(query),"show table status");
STATUS_INIT;
while ((row = sqlNextRow(sr)) != NULL)
{
/* 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;
SCAN_STATUS;
if (hashLookup(expiredHash,row[nameIx]))
{
slNameAddHead(&expiredTableNames, 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(&expiredTableNames, row[nameIx]);
verbose(2,"%s %ld dropt %s lost table\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);
}
else
{ // simple 'show tables' is more efficient than 'show table status'
sqlSafef(query,sizeof(query),"show tables");
sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
{
if (hashLookup(expiredHash,row[0]))
{
slNameAddHead(&expiredTableNames, row[0]);
time_t lastUse = (time_t)hashIntVal(expiredHash,row[0]);
struct tm *lastUseTm = localtime(&lastUse);
verbose(3,"%4d-%02d-%02d %02d:%02d:%02d %ld drop %s\n",
lastUseTm->tm_year+1900, lastUseTm->tm_mon+1,
lastUseTm->tm_mday, lastUseTm->tm_hour, lastUseTm->tm_min,
lastUseTm->tm_sec, (unsigned long)lastUse,row[0]);
hashRemove(expiredHash, row[0]);
}
else if (hashLookup(notExpiredHash,row[0]))
{
time_t lastUse = (time_t)hashIntVal(notExpiredHash,row[0]);
struct tm *lastUseTm = localtime(&lastUse);
verbose(3,"%4d-%02d-%02d %02d:%02d:%02d %ld OK %s\n",
lastUseTm->tm_year+1900, lastUseTm->tm_mon+1,
lastUseTm->tm_mday, lastUseTm->tm_hour, lastUseTm->tm_min,
lastUseTm->tm_sec, (unsigned long)lastUse,row[0]);
}
else
{
struct slName *el = slNameNew(row[0]);
slAddHead(&lostTables, el);
}
}
sqlFreeResult(&sr);
lostTableCount = slCount(lostTables);
// If tables exist, but not in metaInfo, check their age to expire them.
// It turns out even this show table status is slow too, so, only
// run thru it if asked to eliminate lost tables. It is better to
// do this operation with the stand-alone perl script on the customTrash
// database machine.
if (delLostTable && lostTables)
{
struct slName *el;
for (el = lostTables; el != NULL; el = el->next)
{
if (sameWord(el->name,"history"))
continue;
if (sameWord(el->name,CT_META_INFO))
continue;
if (sameWord(el->name,CT_EXTFILE))
continue;
boolean oneTableOnly = FALSE; // protect against multiple tables
/* get table time information to see if it is expired */
sqlSafef(query,sizeof(query),"show table status like '%s'", el->name);
STATUS_INIT;
while ((row = sqlNextRow(sr)) != NULL)
{
if (oneTableOnly)
errAbort("ERROR: query: '%s' returned more than one table "
"name\n", query);
else
oneTableOnly = TRUE;
if (differentWord(row[nameIx], el->name))
errAbort("ERROR: query: '%s' did not return table name '%s' != '%s'\n", query, el->name, row[nameIx]);
SCAN_STATUS;
if (timep < dropTime)
{
slNameAddHead(&expiredTableNames, row[nameIx]);
verbose(2,"%s %ld dropt %s lost table\n",
row[timeIxUsed], (unsigned long)timep, row[nameIx]);
}
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)
{
char comment[256];
if (expiredTableNames)
{
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 = expiredTableNames; 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 */
if (tableStatus)
safef(comment, sizeof(comment), "Dropped %d tables with "
"total size %llu, %llu lost tables",
droppedCount, totalSize, lostTableCount);
else
safef(comment, sizeof(comment),
"Dropped %d tables, no size info, %llu lost tables",
droppedCount, lostTableCount);
verbose(2,"# %s\n", comment);
hgHistoryComment(conn, "%s", comment);
}
else
{
safef(comment, sizeof(comment),
"Dropped no tables, none expired, %llu lost tables",
lostTableCount);
verbose(2,"# %s\n", comment);
}
}
else
{
char comment[256];
if (expiredTableNames)
{
int droppedCount = slCount(expiredTableNames);
if (tableStatus)
safef(comment, sizeof(comment), "Would have dropped %d tables with "
"total size %llu, %llu lost tables",
droppedCount, totalSize, lostTableCount);
else
safef(comment, sizeof(comment),
"Would have dropped %d tables, no size info, %llu lost tables",
droppedCount, lostTableCount);
verbose(2,"# %s\n", comment);
}
else
{
safef(comment, sizeof(comment),
"Would have dropped no tables, none expired, %llu lost tables",
lostTableCount);
verbose(2,"# %s\n", comment);
}
}
sqlDisconnect(&conn);
}
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);
}
