int cleanCount = 0;
int curCount = 0;
conn = L2DatabaseFactory.getInstance().getConnection();
if((curCount = stmt.executeUpdate("DELETE FROM character_friends WHERE character_friends.char_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_friends.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_friends WHERE character_friends.friend_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_friends.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_hennas WHERE character_hennas.char_obj_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_hennas.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_macroses WHERE character_macroses.char_obj_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_macroses.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_quests WHERE character_quests.char_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_quests.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_recipebook WHERE character_recipebook.char_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_recipebook.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_shortcuts WHERE character_shortcuts.char_obj_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_shortcuts.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_skills WHERE character_skills.char_obj_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_skills.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_effects_save WHERE character_effects_save.char_obj_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_effects_save.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_skills_save WHERE character_skills_save.char_obj_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_skills_save.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_subclasses WHERE character_subclasses.char_obj_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_subclasses.");
}
if((curCount = stmt.executeUpdate("DELETE FROM character_variables WHERE character_variables.obj_id = '0';")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table character_variables.");
}
if((curCount = stmt.executeUpdate("DELETE FROM clan_data WHERE clan_data.leader_id NOT IN (SELECT obj_Id FROM characters);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table clan_data.");
}
if((curCount = stmt.executeUpdate("DELETE FROM clan_subpledges WHERE clan_subpledges.clan_id NOT IN (SELECT clan_id FROM clan_data);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table clan_subpledges.");
}
if((curCount = stmt.executeUpdate("DELETE FROM ally_data WHERE ally_data.leader_id NOT IN (SELECT clan_id FROM clan_data);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table ally_data.");
}
if((curCount = stmt.executeUpdate("DELETE FROM pets WHERE pets.item_obj_id NOT IN (SELECT object_id FROM items);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table pets.");
}
if((curCount = stmt.executeUpdate("DELETE FROM siege_clans WHERE siege_clans.clan_id NOT IN (SELECT clan_id FROM clan_data);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table siege_clans.");
}
if((curCount = stmt.executeUpdate("DELETE FROM items WHERE owner_id NOT IN (SELECT obj_Id FROM characters) AND owner_id NOT IN (SELECT clan_id FROM clan_data) AND owner_id NOT IN (SELECT objId FROM pets) AND owner_id NOT IN (SELECT id FROM npc);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table items.");
}
if((curCount = stmt.executeUpdate("DELETE FROM `clan_wars` where clan1 not in (select clan_id FROM clan_data) or clan2 not in (select clan_id FROM clan_data);")) > 0)
{
cleanCount += curCount;
_log.info("Cleaned " + curCount + " elements from table clan_wars.");
}
if((curCount = stmt.executeUpdate("UPDATE characters SET clanid=0,pledge_type=0,pledge_rank=0,lvl_joined_academy=0,apprentice=0 WHERE clanid!=0 AND clanid NOT IN (SELECT clan_id FROM clan_data);")) > 0)
_log.info("Updated " + curCount + " elements from table characters.");
if((curCount = stmt.executeUpdate("UPDATE clan_data SET ally_id=0 WHERE ally_id!=0 AND ally_id NOT IN (SELECT ally_id FROM ally_data);")) > 0)
_log.info("Updated " + curCount + " elements from table clan_data");
stmt.close();
_log.info("Total cleaned " + cleanCount + " elements from database.");
}