diff options
| -rw-r--r-- | src/server/game/Guilds/GuildMgr.cpp | 70 | ||||
| -rwxr-xr-x | src/server/shared/Database/Implementation/CharacterDatabase.cpp | 42 | ||||
| -rwxr-xr-x | src/server/shared/Database/Implementation/CharacterDatabase.h | 14 |
3 files changed, 37 insertions, 89 deletions
diff --git a/src/server/game/Guilds/GuildMgr.cpp b/src/server/game/Guilds/GuildMgr.cpp index 45844823912..44619c6fb46 100644 --- a/src/server/game/Guilds/GuildMgr.cpp +++ b/src/server/game/Guilds/GuildMgr.cpp @@ -97,8 +97,11 @@ void GuildMgr::LoadGuilds() { uint32 oldMSTime = getMSTime(); - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GUILDS); - PreparedQueryResult result = CharacterDatabase.Query(stmt); + // 0 1 2 3 4 5 6 + QueryResult result = CharacterDatabase.Query("SELECT g.guildid, g.name, g.leaderguid, g.EmblemStyle, g.EmblemColor, g.BorderStyle, g.BorderColor, " + // 7 8 9 10 11 12 + "g.BackgroundColor, g.info, g.motd, g.createdate, g.BankMoney, COUNT(gbt.guildid) " + "FROM guild g LEFT JOIN guild_bank_tab gbt ON g.guildid = gbt.guildid GROUP BY g.guildid ORDER BY g.guildid ASC"); if (!result) { @@ -136,11 +139,10 @@ void GuildMgr::LoadGuilds() uint32 oldMSTime = getMSTime(); // Delete orphaned guild rank entries before loading the valid ones - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_NONEXISTENT_GUILD_RANKS); - CharacterDatabase.Execute(stmt); + CharacterDatabase.DirectExecute("DELETE gr FROM guild_rank gr LEFT JOIN guild g ON gr.guildId = g.guildId WHERE g.guildId IS NULL"); - stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GUILD_RANKS); - PreparedQueryResult result = CharacterDatabase.Query(stmt); + // 0 1 2 3 4 + QueryResult result = CharacterDatabase.Query("SELECT guildid, rid, rname, rights, BankMoneyPerDay FROM guild_rank ORDER BY guildid ASC, rid ASC"); if (!result) { @@ -173,11 +175,17 @@ void GuildMgr::LoadGuilds() uint32 oldMSTime = getMSTime(); // Delete orphaned guild member entries before loading the valid ones - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_NONEXISTENT_GUILD_MEMBERS); - CharacterDatabase.Execute(stmt); - - stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GUILD_MEMBERS); - PreparedQueryResult result = CharacterDatabase.Query(stmt); + CharacterDatabase.DirectExecute("DELETE gm FROM guild_member gm LEFT JOIN guild g ON gm.guildId = g.guildId WHERE g.guildId IS NULL"); + + // 0 1 2 3 4 5 6 + QueryResult result = CharacterDatabase.Query("SELECT guildid, gm.guid, rank, pnote, offnote, BankResetTimeMoney, BankRemMoney, " + // 7 8 9 10 11 12 + "BankResetTimeTab0, BankRemSlotsTab0, BankResetTimeTab1, BankRemSlotsTab1, BankResetTimeTab2, BankRemSlotsTab2, " + // 13 14 15 16 17 18 + "BankResetTimeTab3, BankRemSlotsTab3, BankResetTimeTab4, BankRemSlotsTab4, BankResetTimeTab5, BankRemSlotsTab5, " + // 19 20 21 22 23 24 + "c.name, c.level, c.class, c.zone, c.account, c.logout_time " + "FROM guild_member gm LEFT JOIN characters c ON c.guid = gm.guid ORDER BY guildid ASC"); if (!result) { @@ -211,11 +219,11 @@ void GuildMgr::LoadGuilds() uint32 oldMSTime = getMSTime(); // Delete orphaned guild bank right entries before loading the valid ones - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_NONEXISTENT_GUILD_BANK_RIGHTS); - CharacterDatabase.Execute(stmt); + CharacterDatabase.DirectExecute("DELETE gbr FROM guild_bank_right gbr LEFT JOIN guild g ON gbr.guildId = g.guildId WHERE g.guildId IS NULL"); + - stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GUILD_BANK_RIGHTS); - PreparedQueryResult result = CharacterDatabase.Query(stmt); + // 0 1 2 3 4 + QueryResult result = CharacterDatabase.Query("SELECT guildid, TabId, rid, gbright, SlotPerDay FROM guild_bank_right ORDER BY guildid ASC, TabId ASC"); if (!result) { @@ -247,12 +255,10 @@ void GuildMgr::LoadGuilds() { uint32 oldMSTime = getMSTime(); - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_OLD_GUILD_EVENT_LOGS); - stmt->setUInt32(0, sWorld->getIntConfig(CONFIG_GUILD_EVENT_LOG_COUNT)); - CharacterDatabase.Execute(stmt); + CharacterDatabase.DirectPExecute("DELETE FROM guild_eventlog WHERE LogGuid > %u", sWorld->getIntConfig(CONFIG_GUILD_EVENT_LOG_COUNT)); - stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GUILD_EVENTLOGS); - PreparedQueryResult result = CharacterDatabase.Query(stmt); + // 0 1 2 3 4 5 6 + QueryResult result = CharacterDatabase.Query("SELECT guildid, LogGuid, EventType, PlayerGuid1, PlayerGuid2, NewRank, TimeStamp FROM guild_eventlog ORDER BY TimeStamp DESC, LogGuid DESC"); if (!result) { @@ -285,12 +291,10 @@ void GuildMgr::LoadGuilds() uint32 oldMSTime = getMSTime(); // Remove log entries that exceed the number of allowed entries per guild - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_OLD_GUILD_BANK_EVENT_LOGS); - stmt->setUInt32(0, sWorld->getIntConfig(CONFIG_GUILD_BANK_EVENT_LOG_COUNT)); - CharacterDatabase.Execute(stmt); + CharacterDatabase.DirectPExecute("DELETE FROM guild_bank_eventlog WHERE LogGuid > %u", sWorld->getIntConfig(CONFIG_GUILD_BANK_EVENT_LOG_COUNT)); - stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GUILD_BANK_EVENTLOGS); - PreparedQueryResult result = CharacterDatabase.Query(stmt); + // 0 1 2 3 4 5 6 7 8 + QueryResult result = CharacterDatabase.Query("SELECT guildid, TabId, LogGuid, EventType, PlayerGuid, ItemOrMoney, ItemStackCount, DestTabId, TimeStamp FROM guild_bank_eventlog ORDER BY TimeStamp DESC, LogGuid DESC"); if (!result) { @@ -323,11 +327,10 @@ void GuildMgr::LoadGuilds() uint32 oldMSTime = getMSTime(); // Delete orphaned guild bank tab entries before loading the valid ones - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_NONEXISTENT_GUILD_BANK_TABS); - CharacterDatabase.Execute(stmt); + CharacterDatabase.DirectExecute("DELETE gbt FROM guild_bank_tab gbt LEFT JOIN guild g ON gbt.guildId = g.guildId WHERE g.guildId IS NULL"); - stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GUILD_BANK_TABS); - PreparedQueryResult result = CharacterDatabase.Query(stmt); + // 0 1 2 3 4 + QueryResult result = CharacterDatabase.Query("SELECT guildid, TabId, TabName, TabIcon, TabText FROM guild_bank_tab ORDER BY guildid ASC, TabId ASC"); if (!result) { @@ -360,11 +363,12 @@ void GuildMgr::LoadGuilds() uint32 oldMSTime = getMSTime(); // Delete orphan guild bank items - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_NONEXISTENT_GUILD_BANK_ITEMS); - CharacterDatabase.Execute(stmt); + CharacterDatabase.DirectExecute("DELETE gbi FROM guild_bank_item gbi LEFT JOIN guild g ON gbi.guildId = g.guildId WHERE g.guildId IS NULL"); - stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GUILD_BANK_ITEMS); - PreparedQueryResult result = CharacterDatabase.Query(stmt); + // 0 1 2 3 4 5 6 7 8 9 10 + QueryResult result = CharacterDatabase.Query("SELECT creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, " + // 11 12 13 14 15 + "guildid, TabId, SlotId, item_guid, itemEntry FROM guild_bank_item gbi INNER JOIN item_instance ii ON gbi.item_guid = ii.guid"); if (!result) { diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp index 5a05e53f97a..89ddcf10a2f 100755 --- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp +++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp @@ -24,8 +24,6 @@ void CharacterDatabaseConnection::DoPrepareStatements() PREPARE_STATEMENT(CHAR_DEL_QUEST_POOL_SAVE, "DELETE FROM pool_quest_save WHERE pool_id = ?", CONNECTION_ASYNC) PREPARE_STATEMENT(CHAR_ADD_QUEST_POOL_SAVE, "INSERT INTO pool_quest_save (pool_id, quest_id) VALUES (?, ?)", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_DEL_OLD_GUILD_EVENT_LOGS, "DELETE FROM guild_eventlog WHERE LogGuid > ?", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_DEL_OLD_GUILD_BANK_EVENT_LOGS, "DELETE FROM guild_bank_eventlog WHERE LogGuid > ?", CONNECTION_ASYNC) PREPARE_STATEMENT(CHAR_DEL_NONEXISTENT_GUILD_BANK_ITEM, "DELETE FROM guild_bank_item WHERE guildid = ? AND TabId = ? AND SlotId = ?", CONNECTION_ASYNC) PREPARE_STATEMENT(CHAR_DEL_EXPIRED_BANS, "UPDATE character_banned SET active = 0 WHERE unbandate <= UNIX_TIMESTAMP() AND unbandate <> bandate", CONNECTION_ASYNC) PREPARE_STATEMENT(CHAR_GET_GUID_BY_NAME, "SELECT guid FROM characters WHERE name = ?", CONNECTION_SYNCH) @@ -185,47 +183,7 @@ void CharacterDatabaseConnection::DoPrepareStatements() PREPARE_STATEMENT(CHAR_RESET_GUILD_RANK_BANK_TIME3, "UPDATE guild_member SET BankResetTimeTab3 = 0 WHERE guildid = ? AND rank = ?", CONNECTION_ASYNC) PREPARE_STATEMENT(CHAR_RESET_GUILD_RANK_BANK_TIME4, "UPDATE guild_member SET BankResetTimeTab4 = 0 WHERE guildid = ? AND rank = ?", CONNECTION_ASYNC) PREPARE_STATEMENT(CHAR_RESET_GUILD_RANK_BANK_TIME5, "UPDATE guild_member SET BankResetTimeTab5 = 0 WHERE guildid = ? AND rank = ?", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_LOAD_GUILDS, - // 0 1 2 3 4 5 6 - "SELECT g.guildid, g.name, g.leaderguid, g.EmblemStyle, g.EmblemColor, g.BorderStyle, g.BorderColor, " - // 7 8 9 10 11 12 - "g.BackgroundColor, g.info, g.motd, g.createdate, g.BankMoney, COUNT(gbt.guildid) " - "FROM guild g LEFT JOIN guild_bank_tab gbt ON g.guildid = gbt.guildid GROUP BY g.guildid ORDER BY g.guildid ASC", CONNECTION_SYNCH) - // 0 1 2 3 4 - PREPARE_STATEMENT(CHAR_LOAD_GUILD_RANKS, "SELECT guildid, rid, rname, rights, BankMoneyPerDay FROM guild_rank ORDER BY guildid ASC, rid ASC", CONNECTION_SYNCH) PREPARE_STATEMENT(CHAR_LOAD_CHAR_DATA_FOR_GUILD, "SELECT name, level, class, zone, account FROM characters WHERE guid = ?", CONNECTION_SYNCH) - PREPARE_STATEMENT(CHAR_LOAD_GUILD_MEMBERS, - // 0 1 2 3 4 5 6 - "SELECT guildid, gm.guid, rank, pnote, offnote, BankResetTimeMoney, BankRemMoney, " - // 7 8 9 10 11 12 - "BankResetTimeTab0, BankRemSlotsTab0, BankResetTimeTab1, BankRemSlotsTab1, BankResetTimeTab2, BankRemSlotsTab2, " - // 13 14 15 16 17 18 - "BankResetTimeTab3, BankRemSlotsTab3, BankResetTimeTab4, BankRemSlotsTab4, BankResetTimeTab5, BankRemSlotsTab5, " - // 19 20 21 22 23 24 - "c.name, c.level, c.class, c.zone, c.account, c.logout_time " - "FROM guild_member gm LEFT JOIN characters c ON c.guid = gm.guid ORDER BY guildid ASC", CONNECTION_SYNCH) - PREPARE_STATEMENT(CHAR_LOAD_GUILD_BANK_RIGHTS, - // 0 1 2 3 4 - "SELECT guildid, TabId, rid, gbright, SlotPerDay FROM guild_bank_right ORDER BY guildid ASC, TabId ASC", CONNECTION_SYNCH) - // 0 1 2 3 4 - PREPARE_STATEMENT(CHAR_LOAD_GUILD_BANK_TABS, "SELECT guildid, TabId, TabName, TabIcon, TabText FROM guild_bank_tab ORDER BY guildid ASC, TabId ASC", CONNECTION_SYNCH) - PREPARE_STATEMENT(CHAR_LOAD_GUILD_EVENTLOGS, - // 0 1 2 3 4 5 6 - "SELECT guildid, LogGuid, EventType, PlayerGuid1, PlayerGuid2, NewRank, TimeStamp FROM guild_eventlog ORDER BY TimeStamp DESC, LogGuid DESC", CONNECTION_SYNCH) - PREPARE_STATEMENT(CHAR_LOAD_GUILD_BANK_EVENTLOGS, - // 0 1 2 3 4 5 6 7 8 - "SELECT guildid, TabId, LogGuid, EventType, PlayerGuid, ItemOrMoney, ItemStackCount, DestTabId, TimeStamp FROM guild_bank_eventlog ORDER BY TimeStamp DESC, LogGuid DESC", CONNECTION_SYNCH) - PREPARE_STATEMENT(CHAR_LOAD_GUILD_BANK_ITEMS, - // 0 1 2 3 4 5 6 7 8 9 10 - "SELECT creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, " - // 11 12 13 14 15 - "guildid, TabId, SlotId, item_guid, itemEntry FROM guild_bank_item gbi INNER JOIN item_instance ii ON gbi.item_guid = ii.guid", CONNECTION_SYNCH) - - PREPARE_STATEMENT(CHAR_DEL_NONEXISTENT_GUILD_RANKS, "DELETE FROM guild_rank WHERE guildId NOT IN (SELECT guildid FROM guild)", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_DEL_NONEXISTENT_GUILD_MEMBERS, "DELETE FROM guild_member WHERE guildId NOT IN (SELECT guildid FROM guild)", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_DEL_NONEXISTENT_GUILD_BANK_TABS, "DELETE FROM guild_bank_tab WHERE guildId NOT IN (SELECT guildid FROM guild)", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_DEL_NONEXISTENT_GUILD_BANK_RIGHTS, "DELETE FROM guild_bank_right WHERE guildId NOT IN (SELECT guildid FROM guild)", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_DEL_NONEXISTENT_GUILD_BANK_ITEMS, "DELETE FROM guild_bank_item WHERE guildId NOT IN (SELECT guildid FROM guild)", CONNECTION_ASYNC) // Chat channel handling PREPARE_STATEMENT(CHAR_LOAD_CHANNEL, "SELECT announce, ownership, password, bannedList FROM channels WHERE name = ? AND team = ?", CONNECTION_SYNCH) diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.h b/src/server/shared/Database/Implementation/CharacterDatabase.h index 372bbfe62ee..55edf264f38 100755 --- a/src/server/shared/Database/Implementation/CharacterDatabase.h +++ b/src/server/shared/Database/Implementation/CharacterDatabase.h @@ -44,8 +44,6 @@ enum CharacterDatabaseStatements CHAR_DEL_QUEST_POOL_SAVE, CHAR_ADD_QUEST_POOL_SAVE, - CHAR_DEL_OLD_GUILD_EVENT_LOGS, - CHAR_DEL_OLD_GUILD_BANK_EVENT_LOGS, CHAR_DEL_NONEXISTENT_GUILD_BANK_ITEM, CHAR_DEL_EXPIRED_BANS, CHAR_GET_GUID_BY_NAME, @@ -180,19 +178,7 @@ enum CharacterDatabaseStatements CHAR_RESET_GUILD_RANK_BANK_TIME3, CHAR_RESET_GUILD_RANK_BANK_TIME4, CHAR_RESET_GUILD_RANK_BANK_TIME5, - CHAR_LOAD_GUILDS, - CHAR_LOAD_GUILD_RANKS, CHAR_LOAD_CHAR_DATA_FOR_GUILD, - CHAR_LOAD_GUILD_MEMBERS, - CHAR_LOAD_GUILD_BANK_RIGHTS, - CHAR_LOAD_GUILD_BANK_TABS, - CHAR_LOAD_GUILD_EVENTLOGS, - CHAR_LOAD_GUILD_BANK_EVENTLOGS, - CHAR_DEL_NONEXISTENT_GUILD_RANKS, - CHAR_DEL_NONEXISTENT_GUILD_MEMBERS, - CHAR_DEL_NONEXISTENT_GUILD_BANK_TABS, - CHAR_DEL_NONEXISTENT_GUILD_BANK_RIGHTS, - CHAR_DEL_NONEXISTENT_GUILD_BANK_ITEMS, CHAR_LOAD_CHANNEL, CHAR_ADD_CHANNEL, |