* Вызывается при старте сервера, чистит базу от фигни
*/
public void cleanupBD()
{
ThreadConnection con = null;
FiltredStatement stmt = null;
ResultSet rs = null;
try
{
sendLock.lock();
con = L2DatabaseFactory.getInstance().getConnection();
// удаляем почту у удаленных чаров
stmt = con.createStatement();
stmt.executeUpdate("DELETE mail FROM mail LEFT JOIN characters ON mail.sender = characters.obj_Id WHERE characters.obj_Id IS NULL");
DatabaseUtils.closeStatement(stmt);
stmt = con.createStatement();
stmt.executeUpdate("DELETE mail FROM mail LEFT JOIN characters ON mail.receiver = characters.obj_Id WHERE characters.obj_Id IS NULL");
DatabaseUtils.closeStatement(stmt);
// удаляем протухшие письма
stmt = con.createStatement();
stmt.executeUpdate("DELETE FROM mail WHERE UNIX_TIMESTAMP(expire) < UNIX_TIMESTAMP()");
DatabaseUtils.closeStatement(stmt);
// удаляем некорректные аттачи
stmt = con.createStatement();
stmt.executeUpdate("DELETE mail_attachments FROM mail_attachments LEFT JOIN items ON mail_attachments.itemId = items.object_id WHERE items.object_id IS NULL");
DatabaseUtils.closeStatement(stmt);
// чистим письма с потерянными аттачами
stmt = con.createStatement();
stmt.executeUpdate("UPDATE mail LEFT JOIN mail_attachments ON mail.messageId = mail_attachments.messageId SET price=0,attachments=0 WHERE mail_attachments.messageId IS NULL");
DatabaseUtils.closeStatement(stmt);
// чистим от мусора в mail_attachments, возвращая вещи владельцам
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT itemId FROM mail_attachments LEFT JOIN mail ON mail.messageId = mail_attachments.messageId WHERE mail.messageId IS NULL");
while(rs.next())
{
L2ItemInstance item = L2ItemInstance.restoreFromDb(rs.getInt("itemId"), false);
if(item.getOwnerId() == 0)
{
item.removeFromDb(true);
}
else
{
returnItem(item);
}
}
DatabaseUtils.closeDatabaseSR(stmt, rs);
stmt = con.createStatement();
stmt.executeUpdate("DELETE mail_attachments FROM mail_attachments LEFT JOIN mail ON mail.messageId = mail_attachments.messageId WHERE mail.messageId IS NULL");
DatabaseUtils.closeStatement(stmt);
}
catch(SQLException e)
{
e.printStackTrace();