diff options
| -rw-r--r-- | sql/base/characters_database.sql | 5 | ||||
| -rw-r--r-- | sql/updates/10654_characters_item_instance.sql | 28 | ||||
| -rw-r--r-- | src/server/game/AuctionHouse/AuctionHouseMgr.cpp | 159 | ||||
| -rw-r--r-- | src/server/game/AuctionHouse/AuctionHouseMgr.h | 2 | ||||
| -rwxr-xr-x | src/server/game/Battlegrounds/Zones/BattlegroundEY.h | 2 | ||||
| -rwxr-xr-x | src/server/game/Chat/Commands/Level3.cpp | 89 | ||||
| -rwxr-xr-x | src/server/game/Entities/Item/Item.cpp | 94 | ||||
| -rwxr-xr-x | src/server/game/Entities/Player/Player.cpp | 18 | ||||
| -rwxr-xr-x | src/server/game/Globals/ObjectMgr.cpp | 66 | ||||
| -rwxr-xr-x | src/server/game/Guilds/Guild.cpp | 1 | ||||
| -rwxr-xr-x | src/server/game/Mails/Mail.cpp | 39 | ||||
| -rwxr-xr-x | src/server/game/Mails/Mail.h | 3 | ||||
| -rwxr-xr-x | src/server/game/Server/Protocol/Handlers/CharacterHandler.cpp | 3 | ||||
| -rwxr-xr-x | src/server/game/Server/Protocol/Handlers/MailHandler.cpp | 8 | ||||
| -rwxr-xr-x | src/server/shared/Database/Implementation/CharacterDatabase.cpp | 30 | ||||
| -rwxr-xr-x | src/server/shared/Database/Implementation/CharacterDatabase.h | 19 |
16 files changed, 336 insertions, 230 deletions
diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql index 3753593e694..11acfcc0ef7 100644 --- a/sql/base/characters_database.sql +++ b/sql/base/characters_database.sql @@ -184,7 +184,6 @@ CREATE TABLE `auctionhouse` ( `id` int(11) unsigned NOT NULL default '0', `auctioneerguid` int(11) unsigned NOT NULL default '0', `itemguid` int(11) unsigned NOT NULL default '0', - `item_template` int(11) unsigned NOT NULL default '0' COMMENT 'Item Identifier', `itemowner` int(11) unsigned NOT NULL default '0', `buyoutprice` int(11) NOT NULL default '0', `time` bigint(40) NOT NULL default '0', @@ -762,7 +761,6 @@ CREATE TABLE `character_inventory` ( `bag` int(11) unsigned NOT NULL default '0', `slot` tinyint(3) unsigned NOT NULL default '0', `item` int(11) unsigned NOT NULL default '0' COMMENT 'Item Global Unique Identifier', - `item_template` int(11) unsigned NOT NULL default '0' COMMENT 'Item Identifier', PRIMARY KEY (`item`), KEY `idx_guid` (`guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Player System'; @@ -1557,7 +1555,6 @@ CREATE TABLE `guild_bank_item` ( `TabId` tinyint(1) unsigned NOT NULL default '0', `SlotId` tinyint(3) unsigned NOT NULL default '0', `item_guid` int(11) unsigned NOT NULL default '0', - `item_entry` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`guildid`,`tabid`,`slotid`), KEY `guildid_key` (`guildid`), INDEX `Idx_item_guid`(`item_guid`) @@ -1790,6 +1787,7 @@ DROP TABLE IF EXISTS `item_instance`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `item_instance` ( `guid` int(11) unsigned NOT NULL default '0', + `itemEntry` mediumint(8) unsigned NOT NULL default '0', `owner_guid` int(11) unsigned NOT NULL default '0', `creatorGuid` int(10) unsigned NOT NULL default '0', `giftCreatorGuid` int(10) unsigned NOT NULL default '0', @@ -1938,7 +1936,6 @@ DROP TABLE IF EXISTS `mail_items`; CREATE TABLE `mail_items` ( `mail_id` int(11) NOT NULL default '0', `item_guid` int(11) NOT NULL default '0', - `item_template` int(11) NOT NULL default '0', `receiver` int(11) unsigned NOT NULL default '0' COMMENT 'Character Global Unique Identifier', PRIMARY KEY (`mail_id`,`item_guid`), KEY `idx_receiver` (`receiver`) diff --git a/sql/updates/10654_characters_item_instance.sql b/sql/updates/10654_characters_item_instance.sql new file mode 100644 index 00000000000..297250201cd --- /dev/null +++ b/sql/updates/10654_characters_item_instance.sql @@ -0,0 +1,28 @@ +-- Add new column to item_instance table +ALTER TABLE `item_instance` ADD COLUMN `itemEntry` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `guid`; + +-- Set values for new column from corresponding columns in other tables +UPDATE item_instance ii, auctionhouse ah +SET ii.itemEntry = ah.item_template +WHERE ii.guid = ah.itemguid; + +UPDATE item_instance ii, character_inventory ci +SET ii.itemEntry = ci.item_template +WHERE ii.guid = ci.item; + +UPDATE item_instance ii, guild_bank_item gbi +SET ii.itemEntry = gbi.item_entry +WHERE ii.guid = gbi.item_guid; + +UPDATE item_instance ii, mail_items mi +SET ii.itemEntry = mi.item_template +WHERE ii.guid = mi.item_guid; + +-- Remove unnecessary columns +ALTER TABLE `auctionhouse` DROP COLUMN `item_template`; +ALTER TABLE `character_inventory` DROP COLUMN `item_template`; +ALTER TABLE `guild_bank_item` DROP COLUMN `item_entry`; +ALTER TABLE `mail_items` DROP COLUMN `item_template`; + +-- Delete orphan records (use at your own risk) +-- DELETE FROM item_instance WHERE itemEntry = 0; diff --git a/src/server/game/AuctionHouse/AuctionHouseMgr.cpp b/src/server/game/AuctionHouse/AuctionHouseMgr.cpp index b305421feb9..70c437af71b 100644 --- a/src/server/game/AuctionHouse/AuctionHouseMgr.cpp +++ b/src/server/game/AuctionHouse/AuctionHouseMgr.cpp @@ -149,7 +149,10 @@ void AuctionHouseMgr::SendAuctionWonMail(AuctionEntry *auction, SQLTransaction& // set owner to bidder (to prevent delete item with sender char deleting) // owner in `data` will set at mail receive and item extracting - trans->PAppend("UPDATE item_instance SET owner_guid = '%u' WHERE guid='%u'",auction->bidder,pItem->GetGUIDLow()); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_SET_ITEM_OWNER); + stmt->setUInt32(0, auction->bidder); + stmt->setUInt32(1, pItem->GetGUIDLow()); + trans->Append(stmt); if (bidder) { @@ -323,7 +326,6 @@ void AuctionHouseMgr::LoadAuctionItems() barGoLink bar(result->GetRowCount()); uint32 count = 0; - do { bar.step(); @@ -334,7 +336,6 @@ void AuctionHouseMgr::LoadAuctionItems() uint32 item_template = fields[12].GetUInt32(); ItemPrototype const *proto = sObjectMgr.GetItemPrototype(item_template); - if (!proto) { sLog.outError("AuctionHouseMgr::LoadAuctionItems: Unknown item (GUID: %u id: #%u) in auction, skipped.", item_guid,item_template); @@ -342,7 +343,6 @@ void AuctionHouseMgr::LoadAuctionItems() } Item *item = NewItemOrBag(proto); - if (!item->LoadFromDB(item_guid, 0, fields, item_template)) { delete item; @@ -360,29 +360,8 @@ void AuctionHouseMgr::LoadAuctionItems() void AuctionHouseMgr::LoadAuctions() { - QueryResult result = CharacterDatabase.Query("SELECT COUNT(*) FROM auctionhouse"); - if (!result) - { - barGoLink bar(1); - bar.step(); - sLog.outString(); - sLog.outString(">> Loaded 0 auctions. DB table `auctionhouse` is empty."); - return; - } - - Field *fields = result->Fetch(); - uint32 AuctionCount=fields[0].GetUInt32(); - - if (!AuctionCount) - { - barGoLink bar(1); - bar.step(); - sLog.outString(); - sLog.outString(">> Loaded 0 auctions. DB table `auctionhouse` is empty."); - return; - } - - result = CharacterDatabase.Query("SELECT id,auctioneerguid,itemguid,item_template,itemowner,buyoutprice,time,buyguid,lastbid,startbid,deposit FROM auctionhouse"); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_LOAD_AUCTIONS); + PreparedQueryResult result = CharacterDatabase.Query(stmt); if (!result) { barGoLink bar(1); @@ -392,79 +371,34 @@ void AuctionHouseMgr::LoadAuctions() return; } - barGoLink bar(AuctionCount); + barGoLink bar(result->GetRowCount()); - //- TODO: Get rid of horrible design so we don't have to use transaction here to statisfy - //- function parameters. SQLTransaction trans = CharacterDatabase.BeginTransaction(); + uint32 count = 0; AuctionEntry *aItem; - do { - fields = result->Fetch(); + Field* fields = result->Fetch(); bar.step(); - aItem = new AuctionEntry; - aItem->Id = fields[0].GetUInt32(); - aItem->auctioneer = fields[1].GetUInt32(); - aItem->item_guidlow = fields[2].GetUInt32(); - aItem->item_template = fields[3].GetUInt32(); - aItem->owner = fields[4].GetUInt32(); - aItem->buyout = fields[5].GetUInt32(); - aItem->expire_time = fields[6].GetUInt32(); - aItem->bidder = fields[7].GetUInt32(); - aItem->bid = fields[8].GetUInt32(); - aItem->startbid = fields[9].GetUInt32(); - aItem->deposit = fields[10].GetUInt32(); - - CreatureData const* auctioneerData = sObjectMgr.GetCreatureData(aItem->auctioneer); - if (!auctioneerData) - { - aItem->DeleteFromDB(trans); - sLog.outError("Auction %u has not a existing auctioneer (GUID : %u)", aItem->Id, aItem->auctioneer); - delete aItem; - continue; - } - - CreatureInfo const* auctioneerInfo = sObjectMgr.GetCreatureTemplate(auctioneerData->id); - if (!auctioneerInfo) + aItem = new AuctionEntry(); + if (!aItem->LoadFromDB(fields)) { aItem->DeleteFromDB(trans); - sLog.outError("Auction %u has not a existing auctioneer (GUID : %u Entry: %u)", aItem->Id, aItem->auctioneer,auctioneerData->id); delete aItem; continue; } - aItem->auctionHouseEntry = AuctionHouseMgr::GetAuctionHouseEntry(auctioneerInfo->faction_A); - if (!aItem->auctionHouseEntry) - { - aItem->DeleteFromDB(trans); - sLog.outError("Auction %u has auctioneer (GUID : %u Entry: %u) with wrong faction %u", - aItem->Id, aItem->auctioneer,auctioneerData->id,auctioneerInfo->faction_A); - delete aItem; - continue; - } - - // check if sold item exists for guid - // and item_template in fact (GetAItem will fail if problematic in result check in AuctionHouseMgr::LoadAuctionItems) - if (!GetAItem(aItem->item_guidlow)) - { - aItem->DeleteFromDB(trans); - sLog.outError("Auction %u has not a existing item : %u", aItem->Id, aItem->item_guidlow); - delete aItem; - continue; - } - - GetAuctionsMap(auctioneerInfo->faction_A)->AddAuction(aItem); - + GetAuctionsMap(aItem->factionTemplateId)->AddAuction(aItem); + count++; } while (result->NextRow()); CharacterDatabase.CommitTransaction(trans); sLog.outString(); - sLog.outString(">> Loaded %u auctions", AuctionCount); + sLog.outString(">> Loaded %u auctions", count); } void AuctionHouseMgr::AddAItem(Item* it) @@ -782,12 +716,69 @@ uint32 AuctionEntry::GetAuctionOutBid() const void AuctionEntry::DeleteFromDB(SQLTransaction& trans) const { - trans->PAppend("DELETE FROM auctionhouse WHERE id = '%u'",Id); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_AUCTION); + stmt->setUInt32(0, Id); + trans->Append(stmt); } void AuctionEntry::SaveToDB(SQLTransaction& trans) const { - trans->PAppend("INSERT INTO auctionhouse (id,auctioneerguid,itemguid,item_template,itemowner,buyoutprice,time,buyguid,lastbid,startbid,deposit) " - "VALUES ('%u', '%u', '%u', '%u', '%u', '%u', '" UI64FMTD "', '%u', '%u', '%u', '%u')", - Id, auctioneer, item_guidlow, item_template, owner, buyout, (uint64)expire_time, bidder, bid, startbid, deposit); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_AUCTION); + stmt->setUInt32(0, Id); + stmt->setUInt32(1, auctioneer); + stmt->setUInt32(2, item_guidlow); + stmt->setUInt32(3, owner); + stmt->setInt32 (4, int32(buyout)); + stmt->setUInt64(5, uint64(expire_time)); + stmt->setUInt32(6, bidder); + stmt->setInt32 (7, int32(bid)); + stmt->setInt32 (8, int32(startbid)); + stmt->setInt32 (9, int32(deposit)); + trans->Append(stmt); } + +bool AuctionEntry::LoadFromDB(Field* fields) +{ + Id = fields[0].GetUInt32(); + auctioneer = fields[1].GetUInt32(); + item_guidlow = fields[2].GetUInt32(); + item_template = fields[3].GetUInt32(); + owner = fields[4].GetUInt32(); + buyout = fields[5].GetUInt32(); + expire_time = fields[6].GetUInt32(); + bidder = fields[7].GetUInt32(); + bid = fields[8].GetUInt32(); + startbid = fields[9].GetUInt32(); + deposit = fields[10].GetUInt32(); + + CreatureData const* auctioneerData = sObjectMgr.GetCreatureData(auctioneer); + if (!auctioneerData) + { + sLog.outError("Auction %u has not a existing auctioneer (GUID : %u)", Id, auctioneer); + return false; + } + + CreatureInfo const* auctioneerInfo = sObjectMgr.GetCreatureTemplate(auctioneerData->id); + if (!auctioneerInfo) + { + sLog.outError("Auction %u has not a existing auctioneer (GUID : %u Entry: %u)", Id, auctioneer, auctioneerData->id); + return false; + } + + factionTemplateId = auctioneerInfo->faction_A; + auctionHouseEntry = AuctionHouseMgr::GetAuctionHouseEntry(factionTemplateId); + if (!auctionHouseEntry) + { + sLog.outError("Auction %u has auctioneer (GUID : %u Entry: %u) with wrong faction %u", Id, auctioneer, auctioneerData->id, factionTemplateId); + return false; + } + + // check if sold item exists for guid + // and item_template in fact (GetAItem will fail if problematic in result check in AuctionHouseMgr::LoadAuctionItems) + if (!sAuctionMgr.GetAItem(item_guidlow)) + { + sLog.outError("Auction %u has not a existing item : %u", Id, item_guidlow); + return false; + } + return true; +}
\ No newline at end of file diff --git a/src/server/game/AuctionHouse/AuctionHouseMgr.h b/src/server/game/AuctionHouse/AuctionHouseMgr.h index 3add23e856c..d9692a631ed 100644 --- a/src/server/game/AuctionHouse/AuctionHouseMgr.h +++ b/src/server/game/AuctionHouse/AuctionHouseMgr.h @@ -61,6 +61,7 @@ struct AuctionEntry uint32 bidder; uint32 deposit; //deposit can be calculated only when creating auction AuctionHouseEntry const* auctionHouseEntry; // in AuctionHouse.dbc + uint32 factionTemplateId; // helpers uint32 GetHouseId() const { return auctionHouseEntry->houseId; } @@ -70,6 +71,7 @@ struct AuctionEntry bool BuildAuctionInfo(WorldPacket & data) const; void DeleteFromDB(SQLTransaction& trans) const; void SaveToDB(SQLTransaction& trans) const; + bool LoadFromDB(Field* fields); }; //this class is used as auctionhouse instance diff --git a/src/server/game/Battlegrounds/Zones/BattlegroundEY.h b/src/server/game/Battlegrounds/Zones/BattlegroundEY.h index 521e9465d1e..633ef375f01 100755 --- a/src/server/game/Battlegrounds/Zones/BattlegroundEY.h +++ b/src/server/game/Battlegrounds/Zones/BattlegroundEY.h @@ -370,7 +370,7 @@ class BattlegroundEY : public Battleground bool IsAllNodesConrolledByTeam(uint32 team) const; private: void EventPlayerCapturedFlag(Player *Source, uint32 BgObjectType); - void EventPlayerCapturedFlag(Player */*Source*/) {} + void EventPlayerCapturedFlag(Player * /*Source*/) {} void EventTeamCapturedPoint(Player *Source, uint32 Point); void EventTeamLostPoint(Player *Source, uint32 Point); void UpdatePointsCount(uint32 Team); diff --git a/src/server/game/Chat/Commands/Level3.cpp b/src/server/game/Chat/Commands/Level3.cpp index d1cb5c98bc0..d765b9e2a17 100755 --- a/src/server/game/Chat/Commands/Level3.cpp +++ b/src/server/game/Chat/Commands/Level3.cpp @@ -419,15 +419,17 @@ bool ChatHandler::HandleListItemCommand(const char *args) // inventory case uint32 inv_count = 0; - result=CharacterDatabase.PQuery("SELECT COUNT(item_template) FROM character_inventory WHERE item_template='%u'",item_id); + result = CharacterDatabase.PQuery("SELECT COUNT(itemEntry) FROM character_inventory ci INNER JOIN item_instance ii ON ii.guid = ci.item WHERE itemEntry = '%u'", item_id); if (result) inv_count = (*result)[0].GetUInt32(); result=CharacterDatabase.PQuery( - // 0 1 2 3 4 5 - "SELECT ci.item, cibag.slot AS bag, ci.slot, ci.guid, characters.account,characters.name " - "FROM character_inventory AS ci LEFT JOIN character_inventory AS cibag ON (cibag.item=ci.bag),characters " - "WHERE ci.item_template='%u' AND ci.guid = characters.guid LIMIT %u ", + // 0 1 2 3 4 5 + "SELECT ci.item, cb.slot AS bag, ci.slot, ci.guid, c.account, c.name FROM characters c " + "INNER JOIN character_inventory ci ON ci.guid = c.guid " + "INNER JOIN item_instance ii ON ii.guid = ci.item " + "LEFT JOIN character_inventory cb ON cb.item = ci.bag " + "WHERE ii.itemEntry = '%u' LIMIT %u ", item_id, count); if (result) @@ -443,40 +445,43 @@ bool ChatHandler::HandleListItemCommand(const char *args) std::string owner_name = fields[5].GetString(); char const* item_pos = 0; - if (Player::IsEquipmentPos(item_bag,item_slot)) + if (Player::IsEquipmentPos(item_bag, item_slot)) item_pos = "[equipped]"; - else if (Player::IsInventoryPos(item_bag,item_slot)) + else if (Player::IsInventoryPos(item_bag, item_slot)) item_pos = "[in inventory]"; - else if (Player::IsBankPos(item_bag,item_slot)) + else if (Player::IsBankPos(item_bag, item_slot)) item_pos = "[in bank]"; else item_pos = ""; - PSendSysMessage(LANG_ITEMLIST_SLOT, - item_guid,owner_name.c_str(),owner_guid,owner_acc,item_pos); - } while (result->NextRow()); + PSendSysMessage(LANG_ITEMLIST_SLOT, item_guid, owner_name.c_str(), owner_guid, owner_acc, item_pos); + } + while (result->NextRow()); uint32 res_count = uint32(result->GetRowCount()); if (count > res_count) - count-=res_count; + count -= res_count; else if (count) count = 0; } // mail case uint32 mail_count = 0; - result=CharacterDatabase.PQuery("SELECT COUNT(item_template) FROM mail_items WHERE item_template='%u'", item_id); + result = CharacterDatabase.PQuery("SELECT COUNT(itemEntry) FROM mail_items mi INNER JOIN item_instance ii ON ii.guid = mi.item_guid WHERE itemEntry = '%u'", item_id); if (result) mail_count = (*result)[0].GetUInt32(); if (count > 0) { - result=CharacterDatabase.PQuery( - // 0 1 2 3 4 5 6 - "SELECT mail_items.item_guid, mail.sender, mail.receiver, char_s.account, char_s.name, char_r.account, char_r.name " - "FROM mail,mail_items,characters as char_s,characters as char_r " - "WHERE mail_items.item_template='%u' AND char_s.guid = mail.sender AND char_r.guid = mail.receiver AND mail.id=mail_items.mail_id LIMIT %u", + result = CharacterDatabase.PQuery( + // 0 1 2 3 4 5 6 + "SELECT mi.item_guid, m.sender, m.receiver, cs.account, cs.name, cr.account, cr.name FROM mail m " + "INNER JOIN mail_items mi ON mi.mail_id = m.id " + "INNER JOIN item_instance ii ON ii.guid = mi.item_guid " + "INNER JOIN characters cs ON cs.guid = m.sender " + "INNER JOIN characters cr ON cr.guid = m.receiver " + "WHERE ii.itemEntry = '%u' LIMIT %u", item_id, count); } else @@ -497,31 +502,32 @@ bool ChatHandler::HandleListItemCommand(const char *args) char const* item_pos = "[in mail]"; - PSendSysMessage(LANG_ITEMLIST_MAIL, - item_guid,item_s_name.c_str(),item_s,item_s_acc,item_r_name.c_str(),item_r,item_r_acc,item_pos); - } while (result->NextRow()); + PSendSysMessage(LANG_ITEMLIST_MAIL, item_guid, item_s_name.c_str(), item_s, item_s_acc, item_r_name.c_str(), item_r, item_r_acc, item_pos); + } + while (result->NextRow()); uint32 res_count = uint32(result->GetRowCount()); if (count > res_count) - count-=res_count; + count -= res_count; else if (count) count = 0; } // auction case uint32 auc_count = 0; - result=CharacterDatabase.PQuery("SELECT COUNT(item_template) FROM auctionhouse WHERE item_template='%u'",item_id); + result=CharacterDatabase.PQuery("SELECT COUNT(itemEntry) FROM auctionhouse ah INNER JOIN item_instance ii ON ii.guid = ah.itemguid WHERE itemEntry = '%u'",item_id); if (result) auc_count = (*result)[0].GetUInt32(); if (count > 0) { - result=CharacterDatabase.PQuery( - // 0 1 2 3 - "SELECT auctionhouse.itemguid, auctionhouse.itemowner, characters.account, characters.name " - "FROM auctionhouse,characters WHERE auctionhouse.item_template='%u' AND characters.guid = auctionhouse.itemowner LIMIT %u", - item_id, count); + result = CharacterDatabase.PQuery( + // 0 1 2 3 + "SELECT ah.itemguid, ah.itemowner, c.account, c.name FROM auctionhouse ah " + "INNER JOIN characters c ON c.guid = ah.itemowner " + "INNER JOIN item_instance ii ON ii.guid = ah.itemguid " + "WHERE ii.itemEntry = '%u' AND LIMIT %u", item_id, count); } else result = QueryResult(NULL); @@ -538,20 +544,23 @@ bool ChatHandler::HandleListItemCommand(const char *args) char const* item_pos = "[in auction]"; - PSendSysMessage(LANG_ITEMLIST_AUCTION, item_guid, owner_name.c_str(), owner, owner_acc,item_pos); - } while (result->NextRow()); + PSendSysMessage(LANG_ITEMLIST_AUCTION, item_guid, owner_name.c_str(), owner, owner_acc, item_pos); + } + while (result->NextRow()); } // guild bank case uint32 guild_count = 0; - result=CharacterDatabase.PQuery("SELECT COUNT(item_entry) FROM guild_bank_item WHERE item_entry='%u'",item_id); + result = CharacterDatabase.PQuery("SELECT COUNT(itemEntry) FROM guild_bank_item gbi INNER JOIN item_instance ii ON ii.guid = gbi.item_guid WHERE itemEntry = '%u'", item_id); if (result) guild_count = (*result)[0].GetUInt32(); - result=CharacterDatabase.PQuery( + result = CharacterDatabase.PQuery( // 0 1 2 - "SELECT gi.item_guid, gi.guildid, guild.name " - "FROM guild_bank_item AS gi, guild WHERE gi.item_entry='%u' AND gi.guildid = guild.guildid LIMIT %u ", + "SELECT gi.item_guid, gi.guildid, g.name FROM guild_bank_item gi " + "INNER JOIN guild g ON g.guildid = gi.guildid " + "INNER JOIN item_instance ii ON ii.guid = gi.item_guid " + "WHERE ii.itemEntry = '%u' LIMIT %u ", item_id, count); if (result) @@ -565,26 +574,26 @@ bool ChatHandler::HandleListItemCommand(const char *args) char const* item_pos = "[in guild bank]"; - PSendSysMessage(LANG_ITEMLIST_GUILD,item_guid,guild_name.c_str(),guild_guid,item_pos); - } while (result->NextRow()); + PSendSysMessage(LANG_ITEMLIST_GUILD, item_guid, guild_name.c_str(), guild_guid, item_pos); + } + while (result->NextRow()); uint32 res_count = uint32(result->GetRowCount()); if (count > res_count) - count-=res_count; + count -= res_count; else if (count) count = 0; } - if (inv_count+mail_count+auc_count+guild_count == 0) + if (inv_count + mail_count + auc_count + guild_count == 0) { SendSysMessage(LANG_COMMAND_NOITEMFOUND); SetSentErrorMessage(true); return false; } - PSendSysMessage(LANG_COMMAND_LISTITEMMESSAGE,item_id,inv_count+mail_count+auc_count+guild_count,inv_count,mail_count,auc_count,guild_count); - + PSendSysMessage(LANG_COMMAND_LISTITEMMESSAGE, item_id, inv_count + mail_count + auc_count + guild_count, inv_count, mail_count, auc_count, guild_count); return true; } diff --git a/src/server/game/Entities/Item/Item.cpp b/src/server/game/Entities/Item/Item.cpp index 68be75501ae..496c1a37cfa 100755 --- a/src/server/game/Entities/Item/Item.cpp +++ b/src/server/game/Entities/Item/Item.cpp @@ -305,75 +305,62 @@ void Item::SaveToDB(SQLTransaction& trans) switch (uState) { case ITEM_NEW: + case ITEM_CHANGED: { - std::string text = m_text; - CharacterDatabase.escape_string(text); + uint8 index = 0; + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(uState == ITEM_NEW ? CHAR_ADD_ITEM_INSTANCE : CHAR_UPDATE_ITEM_INSTANCE); + stmt->setUInt32( index, GetEntry()); + stmt->setUInt32(++index, GUID_LOPART(GetOwnerGUID())); + stmt->setUInt32(++index, GUID_LOPART(GetUInt64Value(ITEM_FIELD_CREATOR))); + stmt->setUInt32(++index, GUID_LOPART(GetUInt64Value(ITEM_FIELD_GIFTCREATOR))); + stmt->setUInt32(++index, GetCount()); + stmt->setUInt32(++index, GetUInt32Value(ITEM_FIELD_DURATION)); + std::ostringstream ss; - ss << "REPLACE INTO item_instance (guid,owner_guid,creatorGuid,giftCreatorGuid,count,duration,charges,flags,enchantments,randomPropertyId,durability,playedTime,text) VALUES ("; - ss << guid << ","; - ss << GUID_LOPART(GetOwnerGUID()) << ","; - ss << GUID_LOPART(GetUInt64Value(ITEM_FIELD_CREATOR)) << ","; - ss << GUID_LOPART(GetUInt64Value(ITEM_FIELD_GIFTCREATOR)) << ","; - ss << GetCount() << ","; - ss << GetUInt32Value(ITEM_FIELD_DURATION) << ",'"; for (uint8 i = 0; i < MAX_ITEM_PROTO_SPELLS; ++i) ss << GetSpellCharges(i) << " "; + stmt->setString(++index, ss.str()); + + stmt->setUInt32(++index, GetUInt32Value(ITEM_FIELD_FLAGS)); - ss << "'," << GetUInt32Value(ITEM_FIELD_FLAGS) << ",'"; + ss.clear(); for (uint8 i = 0; i < MAX_ENCHANTMENT_SLOT; ++i) { ss << GetEnchantmentId(EnchantmentSlot(i)) << " "; ss << GetEnchantmentDuration(EnchantmentSlot(i)) << " "; ss << GetEnchantmentCharges(EnchantmentSlot(i)) << " "; } + stmt->setString(++index, ss.str()); - ss << "'," << GetItemRandomPropertyId() << ","; - ss << GetUInt32Value(ITEM_FIELD_DURABILITY) << ","; - ss << GetUInt32Value(ITEM_FIELD_CREATE_PLAYED_TIME) << ",'"; - ss << text << "')"; + stmt->setInt32 (++index, GetItemRandomPropertyId()); + stmt->setUInt32(++index, GetUInt32Value(ITEM_FIELD_DURABILITY)); + stmt->setUInt32(++index, GetUInt32Value(ITEM_FIELD_CREATE_PLAYED_TIME)); + stmt->setString(++index, m_text); + stmt->setUInt32(++index, guid); - trans->Append(ss.str().c_str()); - }break; - case ITEM_CHANGED: - { - std::string text = m_text; - CharacterDatabase.escape_string(text); - std::ostringstream ss; - ss << "UPDATE item_instance SET owner_guid = " << GUID_LOPART(GetOwnerGUID()); - ss << ", creatorGuid = " << GUID_LOPART(GetUInt64Value(ITEM_FIELD_CREATOR)); - ss << ", giftCreatorGuid = " << GUID_LOPART(GetUInt64Value(ITEM_FIELD_GIFTCREATOR)); - ss << ", count = " << GetCount(); - ss << ", duration = " << GetUInt32Value(ITEM_FIELD_DURATION); - ss << ", charges = '"; - for (uint8 i = 0; i < MAX_ITEM_PROTO_SPELLS; ++i) - ss << GetSpellCharges(i) << " "; + trans->Append(stmt); - ss << "', flags = " << GetUInt32Value(ITEM_FIELD_FLAGS); - ss << ", enchantments = '"; - for (uint8 i = 0; i < MAX_ENCHANTMENT_SLOT; ++i) + if ((uState == ITEM_CHANGED) && HasFlag(ITEM_FIELD_FLAGS, ITEM_FLAG_WRAPPED)) { - ss << GetEnchantmentId(EnchantmentSlot(i)) << " "; - ss << GetEnchantmentDuration(EnchantmentSlot(i)) << " "; - ss << GetEnchantmentCharges(EnchantmentSlot(i)) << " "; + stmt = CharacterDatabase.GetPreparedStatement(CHAR_UPDATE_GIFT_OWNER); + stmt->setUInt32(0, GUID_LOPART(GetOwnerGUID())); + stmt->setUInt32(1, guid); + trans->Append(stmt); } - - ss << "', randomPropertyId = " << GetItemRandomPropertyId(); - ss << ", durability = " << GetUInt32Value(ITEM_FIELD_DURABILITY); - ss << ", playedTime = " << GetUInt32Value(ITEM_FIELD_CREATE_PLAYED_TIME); - ss << ", text = '" << text << "' WHERE guid = " << guid; - - trans->Append(ss.str().c_str()); - - if (HasFlag(ITEM_FIELD_FLAGS, ITEM_FLAG_WRAPPED)) - trans->PAppend("UPDATE character_gifts SET guid = '%u' WHERE item_guid = '%u'", GUID_LOPART(GetOwnerGUID()),GetGUIDLow()); - }break; + break; + } case ITEM_REMOVED: { PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_ITEM_INSTANCE); stmt->setUInt32(0, guid); trans->Append(stmt); + if (HasFlag(ITEM_FIELD_FLAGS, ITEM_FLAG_WRAPPED)) - trans->PAppend("DELETE FROM character_gifts WHERE item_guid = '%u'", GetGUIDLow()); + { + stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_GIFT); + stmt->setUInt32(0, guid); + trans->Append(stmt); + } delete this; return; } @@ -453,13 +440,12 @@ bool Item::LoadFromDB(uint32 guid, uint64 owner_guid, Field* fields, uint32 entr if (need_save) // normal item changed state set not work at loading { - std::ostringstream ss; - ss << "UPDATE item_instance SET duration = " << GetUInt32Value(ITEM_FIELD_DURABILITY) - << ", flags = " << GetUInt32Value(ITEM_FIELD_FLAGS) - << ", durability = " << GetUInt32Value(ITEM_FIELD_DURABILITY) - << " WHERE guid = " << guid; - - CharacterDatabase.Execute(ss.str().c_str()); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_UPDATE_ITEM_INSTANCE_ON_LOAD); + stmt->setUInt32(0, GetUInt32Value(ITEM_FIELD_DURABILITY)); + stmt->setUInt32(1, GetUInt32Value(ITEM_FIELD_FLAGS)); + stmt->setUInt32(2, GetUInt32Value(ITEM_FIELD_DURABILITY)); + stmt->setUInt32(3, guid); + CharacterDatabase.Execute(stmt); } return true; diff --git a/src/server/game/Entities/Player/Player.cpp b/src/server/game/Entities/Player/Player.cpp index 2f1cb394578..293c170cec7 100755 --- a/src/server/game/Entities/Player/Player.cpp +++ b/src/server/game/Entities/Player/Player.cpp @@ -18168,18 +18168,22 @@ void Player::_SaveInventory(SQLTransaction& trans) } } + PreparedStatement* stmt = NULL; switch (item->GetState()) { case ITEM_NEW: - trans->PAppend("INSERT INTO character_inventory (guid,bag,slot,item,item_template) VALUES ('%u', '%u', '%u', '%u', '%u')", lowGuid, bag_guid, item->GetSlot(), item->GetGUIDLow(), item->GetEntry()); - break; case ITEM_CHANGED: - trans->PAppend("DELETE FROM character_inventory WHERE item = '%u'", item->GetGUIDLow()); - trans->PAppend("INSERT INTO character_inventory (guid,bag,slot,item,item_template) VALUES ('%u', '%u', '%u', '%u', '%u')", lowGuid, bag_guid, item->GetSlot(), item->GetGUIDLow(), item->GetEntry()); + stmt = CharacterDatabase.GetPreparedStatement(item->GetState() == ITEM_NEW ? CHAR_ADD_INVENTORY_ITEM : CHAR_UPDATE_INVENTORY_ITEM); + stmt->setUInt32(0, lowGuid); + stmt->setUInt32(1, bag_guid); + stmt->setUInt8 (2, item->GetSlot()); + stmt->setUInt32(3, item->GetGUIDLow()); + trans->Append(stmt); break; case ITEM_REMOVED: - trans->PAppend("DELETE FROM character_inventory WHERE item = '%u'", item->GetGUIDLow()); - break; + stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_INVENTORY_ITEM); + stmt->setUInt32(0, item->GetGUIDLow()); + trans->Append(stmt); case ITEM_UNCHANGED: break; } @@ -18214,7 +18218,7 @@ void Player::_SaveMail(SQLTransaction& trans) if (m->HasItems()) { PreparedStatement* stmt = NULL; - for (std::vector<MailItemInfo>::iterator itr2 = m->items.begin(); itr2 != m->items.end(); ++itr2) + for (MailItemInfoVec::iterator itr2 = m->items.begin(); itr2 != m->items.end(); ++itr2) { stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_ITEM_INSTANCE); stmt->setUInt32(0, itr2->item_guid); diff --git a/src/server/game/Globals/ObjectMgr.cpp b/src/server/game/Globals/ObjectMgr.cpp index 89c849ef331..154efc0ce70 100755 --- a/src/server/game/Globals/ObjectMgr.cpp +++ b/src/server/game/Globals/ObjectMgr.cpp @@ -5550,13 +5550,21 @@ void ObjectMgr::LoadNpcTextLocales() //not very fast function but it is called only once a day, or on starting-up void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp) { - time_t basetime = time(NULL); - sLog.outDebug("Returning mails current time: hour: %d, minute: %d, second: %d ", localtime(&basetime)->tm_hour, localtime(&basetime)->tm_min, localtime(&basetime)->tm_sec); - //delete all old mails without item and without body immediately, if starting server + time_t curTime = time(NULL); + tm* lt = localtime(&curTime); + uint64 basetime(curTime); + sLog.outDebug("Returning mails current time: hour: %d, minute: %d, second: %d ", lt->tm_hour, lt->tm_min, lt->tm_sec); + + // Delete all old mails without item and without body immediately, if starting server if (!serverUp) - CharacterDatabase.PExecute("DELETE FROM mail WHERE expire_time < '" UI64FMTD "' AND has_items = '0' AND body = ''", (uint64)basetime); - // 0 1 2 3 4 5 6 7 8 9 - QueryResult result = CharacterDatabase.PQuery("SELECT id,messageType,sender,receiver,has_items,expire_time,cod,checked,mailTemplateId FROM mail WHERE expire_time < '" UI64FMTD "'", (uint64)basetime); + { + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_EMPTY_EXPIRED_MAIL); + stmt->setUInt64(0, basetime); + CharacterDatabase.Execute(stmt); + } + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_GET_EXPIRED_MAIL); + stmt->setUInt64(0, basetime); + PreparedQueryResult result = CharacterDatabase.Query(stmt); if (!result) { barGoLink bar(1); @@ -5569,7 +5577,6 @@ void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp) barGoLink bar(result->GetRowCount()); uint32 count = 0; Field *fields; - do { bar.step(); @@ -5592,17 +5599,18 @@ void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp) pl = GetPlayer((uint64)m->receiver); if (pl && pl->m_mailsLoaded) - { //this code will run very improbably (the time is between 4 and 5 am, in game is online a player, who has old mail - //his in mailbox and he has already listed his mails) + { // this code will run very improbably (the time is between 4 and 5 am, in game is online a player, who has old mail + // his in mailbox and he has already listed his mails) delete m; continue; } - //delete or return mail: + // Delete or return mail if (has_items) { - QueryResult resultItems = CharacterDatabase.PQuery("SELECT item_guid,item_template FROM mail_items WHERE mail_id='%u'", m->messageID); - if (resultItems) + stmt = CharacterDatabase.GetPreparedStatement(CHAR_GET_MAIL_ITEM_LITE); + stmt->setUInt32(0, m->messageID); + if (PreparedQueryResult resultItems = CharacterDatabase.Query(stmt)) { do { @@ -5615,11 +5623,11 @@ void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp) } while (resultItems->NextRow()); } - //if it is mail from AH, it shouldn't be returned, but deleted - if (m->messageType != MAIL_NORMAL || m->messageType == MAIL_AUCTION || (m->checked & (MAIL_CHECK_MASK_COD_PAYMENT | MAIL_CHECK_MASK_RETURNED))) + // if it is mail from non-player, or if it's already return mail, it shouldn't be returned, but deleted + if (m->messageType != MAIL_NORMAL || (m->checked & (MAIL_CHECK_MASK_COD_PAYMENT | MAIL_CHECK_MASK_RETURNED))) { // mail open and then not returned - for (std::vector<MailItemInfo>::iterator itr2 = m->items.begin(); itr2 != m->items.end(); ++itr2) + for (MailItemInfoVec::iterator itr2 = m->items.begin(); itr2 != m->items.end(); ++itr2) { PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_ITEM_INSTANCE); stmt->setUInt32(0, itr2->item_guid); @@ -5628,14 +5636,36 @@ void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp) } else { - //mail will be returned: - CharacterDatabase.PExecute("UPDATE mail SET sender = '%u', receiver = '%u', expire_time = '" UI64FMTD "', deliver_time = '" UI64FMTD "',cod = '0', checked = '%u' WHERE id = '%u'", m->receiver, m->sender, (uint64)(basetime + 30*DAY), (uint64)basetime, MAIL_CHECK_MASK_RETURNED, m->messageID); + // Mail will be returned + stmt = CharacterDatabase.GetPreparedStatement(CHAR_SET_MAIL_RETURNED); + stmt->setUInt32(0, m->receiver); + stmt->setUInt32(1, m->sender); + stmt->setUInt64(2, basetime + 30 * DAY); + stmt->setUInt64(3, basetime); + stmt->setUInt8 (4, uint8(MAIL_CHECK_MASK_RETURNED)); + stmt->setUInt32(5, m->messageID); + CharacterDatabase.Execute(stmt); + for (MailItemInfoVec::iterator itr2 = m->items.begin(); itr2 != m->items.end(); ++itr2) + { + // Update receiver in mail items for its proper delivery, and in instance_item for avoid lost item at sender delete + stmt = CharacterDatabase.GetPreparedStatement(CHAR_SET_MAIL_ITEM_RECEIVER); + stmt->setUInt32(0, m->sender); + stmt->setUInt32(1, itr2->item_guid); + CharacterDatabase.Execute(stmt); + + stmt = CharacterDatabase.GetPreparedStatement(CHAR_SET_ITEM_OWNER); + stmt->setUInt32(0, m->sender); + stmt->setUInt32(1, itr2->item_guid); + CharacterDatabase.Execute(stmt); + } delete m; continue; } } - CharacterDatabase.PExecute("DELETE FROM mail WHERE id = '%u'", m->messageID); + stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_MAIL); + stmt->setUInt32(0, m->messageID); + CharacterDatabase.Execute(stmt); delete m; ++count; } while (result->NextRow()); diff --git a/src/server/game/Guilds/Guild.cpp b/src/server/game/Guilds/Guild.cpp index 813afcdefbd..0b8afcc8e43 100755 --- a/src/server/game/Guilds/Guild.cpp +++ b/src/server/game/Guilds/Guild.cpp @@ -476,7 +476,6 @@ bool Guild::BankTab::SetItem(SQLTransaction& trans, uint8 slotId, Item* pItem) stmt->setUInt8 (1, m_tabId); stmt->setUInt8 (2, slotId); stmt->setUInt32(3, pItem->GetGUIDLow()); - stmt->setUInt32(4, pItem->GetEntry()); CharacterDatabase.ExecuteOrAppend(trans, stmt); pItem->SetUInt64Value(ITEM_FIELD_CONTAINED, 0); diff --git a/src/server/game/Mails/Mail.cpp b/src/server/game/Mails/Mail.cpp index fb2bd22ca78..d35b3af467f 100755 --- a/src/server/game/Mails/Mail.cpp +++ b/src/server/game/Mails/Mail.cpp @@ -150,7 +150,10 @@ void MailDraft::SendReturnToSender(uint32 sender_acc, uint32 sender_guid, uint32 Item* item = mailItemIter->second; item->SaveToDB(trans); // item not in inventory and can be save standalone // owner in data will set at mail receive and item extracting - trans->PAppend("UPDATE item_instance SET owner_guid = '%u' WHERE guid='%u'", receiver_guid, item->GetGUIDLow()); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_SET_ITEM_OWNER); + stmt->setUInt32(0, receiver_guid); + stmt->setUInt32(1, item->GetGUIDLow()); + trans->Append(stmt); } } @@ -189,20 +192,32 @@ void MailDraft::SendMailTo(SQLTransaction& trans, MailReceiver const& receiver, time_t expire_time = deliver_time + expire_delay; // Add to DB - std::string safe_subject = GetSubject(); - std::string safe_body = GetBody(); - - CharacterDatabase.escape_string(safe_subject); - CharacterDatabase.escape_string(safe_body); - - trans->PAppend("INSERT INTO mail (id,messageType,stationery,mailTemplateId,sender,receiver,subject,body,has_items,expire_time,deliver_time,money,cod,checked) " - "VALUES ('%u', '%u', '%u', '%u', '%u', '%u', '%s', '%s', '%u', '" UI64FMTD "','" UI64FMTD "', '%u', '%u', '%d')", - mailId, sender.GetMailMessageType(), sender.GetStationery(), GetMailTemplateId(), sender.GetSenderId(), receiver.GetPlayerGUIDLow(), safe_subject.c_str(), safe_body.c_str(),(m_items.empty() ? 0 : 1), (uint64)expire_time, (uint64)deliver_time, m_money, m_COD, checked); + uint8 index = 0; + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_MAIL); + stmt->setUInt32( index, mailId); + stmt->setUInt8 (++index, uint8(sender.GetMailMessageType())); + stmt->setInt8 (++index, int8(sender.GetStationery())); + stmt->setUInt16(++index, GetMailTemplateId()); + stmt->setUInt32(++index, sender.GetSenderId()); + stmt->setUInt32(++index, receiver.GetPlayerGUIDLow()); + stmt->setString(++index, GetSubject()); + stmt->setString(++index, GetBody()); + stmt->setBool (++index, !m_items.empty()); + stmt->setUInt64(++index, uint64(expire_time)); + stmt->setUInt64(++index, uint64(deliver_time)); + stmt->setUInt32(++index, m_money); + stmt->setUInt32(++index, m_COD); + stmt->setUInt8 (++index, uint8(checked)); + trans->Append(stmt); for (MailItemMap::const_iterator mailItemIter = m_items.begin(); mailItemIter != m_items.end(); ++mailItemIter) { - Item* item = mailItemIter->second; - trans->PAppend("INSERT INTO mail_items (mail_id,item_guid,item_template,receiver) VALUES ('%u', '%u', '%u','%u')", mailId, item->GetGUIDLow(), item->GetEntry(), receiver.GetPlayerGUIDLow()); + Item* pItem = mailItemIter->second; + stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_MAIL_ITEM); + stmt->setUInt32(0, mailId); + stmt->setUInt32(1, pItem->GetGUIDLow()); + stmt->setUInt32(2, receiver.GetPlayerGUIDLow()); + trans->Append(stmt); } // For online receiver update in game mail status and data diff --git a/src/server/game/Mails/Mail.h b/src/server/game/Mails/Mail.h index 2e730dde1ef..4308d53676d 100755 --- a/src/server/game/Mails/Mail.h +++ b/src/server/game/Mails/Mail.h @@ -165,6 +165,7 @@ struct MailItemInfo uint32 item_guid; uint32 item_template; }; +typedef std::vector<MailItemInfo> MailItemInfoVec; struct Mail { @@ -195,7 +196,7 @@ struct Mail bool RemoveItem(uint32 item_guid) { - for (std::vector<MailItemInfo>::iterator itr = items.begin(); itr != items.end(); ++itr) + for (MailItemInfoVec::iterator itr = items.begin(); itr != items.end(); ++itr) { if (itr->item_guid == item_guid) { diff --git a/src/server/game/Server/Protocol/Handlers/CharacterHandler.cpp b/src/server/game/Server/Protocol/Handlers/CharacterHandler.cpp index 19ec43652bc..a07c2c20c28 100755 --- a/src/server/game/Server/Protocol/Handlers/CharacterHandler.cpp +++ b/src/server/game/Server/Protocol/Handlers/CharacterHandler.cpp @@ -1430,6 +1430,7 @@ void WorldSession::HandleEquipmentSetUse(WorldPacket &recv_data) void WorldSession::HandleCharFactionOrRaceChange(WorldPacket& recv_data) { + // TODO: Move queries to prepared statements uint64 guid; std::string newname; uint8 gender, skin, face, hairStyle, hairColor, facialHair, race; @@ -1714,7 +1715,7 @@ void WorldSession::HandleCharFactionOrRaceChange(WorldPacket& recv_data) { uint32 item_alliance = it->first; uint32 item_horde = it->second; - trans->PAppend("UPDATE `character_inventory` SET item_template = '%u' where item_template = '%u' AND guid = '%u'", + trans->PAppend("UPDATE `item_instance` ii, `character_inventory` ci SET ii.itemEntry = '%u' WHERE ii.itemEntry = '%u' AND ci.guid = '%u' AND ci.item = ii.guid", team == BG_TEAM_ALLIANCE ? item_alliance : item_horde, team == BG_TEAM_ALLIANCE ? item_horde : item_alliance, guid); } diff --git a/src/server/game/Server/Protocol/Handlers/MailHandler.cpp b/src/server/game/Server/Protocol/Handlers/MailHandler.cpp index f1ffd691b22..23948c174dc 100755 --- a/src/server/game/Server/Protocol/Handlers/MailHandler.cpp +++ b/src/server/game/Server/Protocol/Handlers/MailHandler.cpp @@ -258,7 +258,11 @@ void WorldSession::HandleSendMail(WorldPacket & recv_data) item->DeleteFromInventoryDB(trans); // deletes item from character's inventory item->SaveToDB(trans); // recursive and not have transaction guard into self, item not in inventory and can be save standalone // owner in data will set at mail receive and item extracting - trans->PAppend("UPDATE item_instance SET owner_guid = '%u' WHERE guid='%u'", GUID_LOPART(rc), item->GetGUIDLow()); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_SET_ITEM_OWNER); + stmt->setUInt32(0, GUID_LOPART(rc)); + stmt->setUInt32(1, item->GetGUIDLow()); + trans->Append(stmt); + draft.AddItem(item); } @@ -373,7 +377,7 @@ void WorldSession::HandleMailReturnToSender(WorldPacket & recv_data) if (m->HasItems()) { - for (std::vector<MailItemInfo>::iterator itr2 = m->items.begin(); itr2 != m->items.end(); ++itr2) + for (MailItemInfoVec::iterator itr2 = m->items.begin(); itr2 != m->items.end(); ++itr2) { Item *item = pl->GetMItem(itr2->item_guid); if (item) diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp index 4a2c100bc7b..d30db49d5cc 100755 --- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp +++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp @@ -62,7 +62,7 @@ bool CharacterDatabaseConnection::Open() PrepareStatement(CHAR_LOAD_PLAYER_WEKLYQUESTSTATUS, "SELECT quest FROM character_queststatus_weekly WHERE guid = ?"); PrepareStatement(CHAR_LOAD_PLAYER_REPUTATION, "SELECT faction,standing,flags FROM character_reputation WHERE guid = ?"); PrepareStatement(CHAR_LOAD_PLAYER_INVENTORY, "SELECT creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, bag, slot, " - "item, item_template FROM character_inventory JOIN item_instance ON character_inventory.item = item_instance.guid WHERE character_inventory.guid = ? ORDER BY bag, slot"); + "item, itemEntry FROM character_inventory ci JOIN item_instance ii ON ci.item = ii.guid WHERE ci.guid = ? ORDER BY bag, slot"); PrepareStatement(CHAR_LOAD_PLAYER_ACTIONS, "SELECT a.button, a.action, a.type FROM character_action as a, characters as c WHERE a.guid = c.guid AND a.spec = c.activespec AND a.guid = ? ORDER BY button"); PrepareStatement(CHAR_LOAD_PLAYER_ACTIONS_SPEC, "SELECT button, action, type FROM character_action WHERE guid = ? AND spec = ? ORDER BY button"); PrepareStatement(CHAR_LOAD_PLAYER_MAILCOUNT, "SELECT COUNT(id) FROM mail WHERE receiver = ? AND (checked & 1) = 0 AND deliver_time <= ?"); @@ -86,14 +86,34 @@ bool CharacterDatabaseConnection::Open() PrepareStatement(CHAR_LOAD_PLAYER_ARENASTATS, "SELECT slot, personal_rating, matchmaker_rating FROM character_arena_stats WHERE guid = ? ORDER BY slot ASC"); PrepareStatement(CHAR_LOAD_PLAYER_BANNED, "SELECT guid FROM character_banned WHERE guid = ? AND active = 1"); PrepareStatement(CHAR_LOAD_ACCOUNT_DATA, "SELECT type, time, data FROM account_data WHERE account = ?"); - PrepareStatement(CHAR_LOAD_PLAYER_MAILITEMS, "SELECT creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, item_guid, item_template, owner_guid FROM mail_items JOIN item_instance ON item_guid = guid WHERE mail_id = ?"); - PrepareStatement(CHAR_LOAD_AUCTION_ITEMS, "SELECT creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, itemguid, item_template FROM auctionhouse JOIN item_instance ON itemguid = guid"); + PrepareStatement(CHAR_LOAD_PLAYER_MAILITEMS, "SELECT creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, item_guid, itemEntry, owner_guid FROM mail_items mi JOIN item_instance ii ON mi.item_guid = ii.guid WHERE mail_id = ?"); + PrepareStatement(CHAR_LOAD_AUCTION_ITEMS, "SELECT creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, itemguid, itemEntry FROM auctionhouse ah JOIN item_instance ii ON ah.itemguid = ii.guid"); + PrepareStatement(CHAR_LOAD_AUCTIONS, "SELECT id, auctioneerguid, itemguid, itemEntry, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit FROM auctionhouse ah INNER JOIN item_instance ii ON ii.guid = ah.itemguid"); + PrepareStatement(CHAR_ADD_AUCTION, "INSERT INTO auctionhouse (id, auctioneerguid, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); + PrepareStatement(CHAR_DEL_AUCTION, "DELETE FROM auctionhouse WHERE id = ?"); + PrepareStatement(CHAR_ADD_MAIL, "INSERT INTO mail(id, messageType, stationery, mailTemplateId, sender, receiver, subject, body, has_items, expire_time, deliver_time, money,cod, checked) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); + PrepareStatement(CHAR_DEL_MAIL, "DELETE FROM mail WHERE id = ?"); + PrepareStatement(CHAR_ADD_MAIL_ITEM, "INSERT INTO mail_items(mail_id, item_guid, receiver) VALUES (?, ?, ?)"); + PrepareStatement(CHAR_DEL_EMPTY_EXPIRED_MAIL, "DELETE FROM mail WHERE expire_time < ? AND has_items = 0 AND body = ''"); + PrepareStatement(CHAR_GET_EXPIRED_MAIL, "SELECT id, messageType, sender, receiver, has_items, expire_time, cod, checked, mailTemplateId FROM mail WHERE expire_time < ?"); + PrepareStatement(CHAR_GET_MAIL_ITEM_LITE, "SELECT item_guid, itemEntry FROM mail_items mi INNER JOIN item_instance ii ON ii.guid = mi.item_guid WHERE mail_id = ?"); + PrepareStatement(CHAR_SET_MAIL_RETURNED, "UPDATE mail SET sender = ?, receiver = ?, expire_time = ?, deliver_time = ?, cod = 0, checked = ? WHERE id = ?"); + PrepareStatement(CHAR_SET_MAIL_ITEM_RECEIVER, "UPDATE mail_items SET receiver = ? WHERE item_guid = ?"); + PrepareStatement(CHAR_SET_ITEM_OWNER, "UPDATE item_instance SET owner_guid = ? WHERE guid = ?"); + PrepareStatement(CHAR_LOAD_ITEM_REFUNDS, "SELECT player_guid, paidMoney, paidExtendedCost FROM item_refund_instance WHERE item_guid = ? AND player_guid = ? LIMIT 1"); PrepareStatement(CHAR_LOAD_ITEM_BOP_TRADE, "SELECT allowedPlayers FROM item_soulbound_trade_data WHERE itemGuid = ? LIMIT 1"); PrepareStatement(CHAR_DEL_ITEM_BOP_TRADE, "DELETE FROM item_soulbound_trade_data WHERE itemGuid = ? LIMIT 1"); PrepareStatement(CHAR_ADD_ITEM_BOP_TRADE, "INSERT INTO item_soulbound_trade_data VALUES (?, ?)"); + PrepareStatement(CHAR_ADD_INVENTORY_ITEM, "INSERT INTO character_inventory (guid, bag, slot, item) VALUES (?, ?, ?, ?)"); + PrepareStatement(CHAR_UPDATE_INVENTORY_ITEM, "UPDATE character_inventory SET guid = ?, bag = ?, slot = ? WHERE item = ?"); PrepareStatement(CHAR_DEL_INVENTORY_ITEM, "DELETE FROM character_inventory WHERE item = ?"); + PrepareStatement(CHAR_ADD_ITEM_INSTANCE, "REPLACE INTO item_instance (itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); + PrepareStatement(CHAR_UPDATE_ITEM_INSTANCE, "UPDATE item_instance SET itemEntry = ?, owner_guid = ?, creatorGuid = ?, giftCreatorGuid = ?, count = ?, duration = ?, charges = ?, flags = ?, enchantments = ?, randomPropertyId = ?, durability = ?, playedTime = ?, text = ? WHERE guid = ?"); + PrepareStatement(CHAR_UPDATE_ITEM_INSTANCE_ON_LOAD, "UPDATE item_instance SET duration = ?, flags = ?, durability = ? WHERE guid = ?"); PrepareStatement(CHAR_DEL_ITEM_INSTANCE, "DELETE FROM item_instance WHERE guid = ?"); + PrepareStatement(CHAR_UPDATE_GIFT_OWNER, "UPDATE character_gifts SET guid = ? WHERE item_guid = ?"); + PrepareStatement(CHAR_DEL_GIFT, "DELETE FROM character_gifts WHERE item_guid = ?"); PrepareStatement(CHAR_GET_ACCOUNT_BY_NAME, "SELECT account FROM characters WHERE name = ?"); // Guild handling @@ -112,7 +132,7 @@ bool CharacterDatabaseConnection::Open() PrepareStatement(CHAR_DEL_GUILD_BANK_TAB, "DELETE FROM guild_bank_tab WHERE guildid = ? AND TabId = ?"); // 0: uint32, 1: uint8 PrepareStatement(CHAR_DEL_GUILD_BANK_TABS, "DELETE FROM guild_bank_tab WHERE guildid = ?"); // 0: uint32 // 0: uint32, 1: uint8, 2: uint8, 3: uint32, 4: uint32 - PrepareStatement(CHAR_ADD_GUILD_BANK_ITEM, "INSERT INTO guild_bank_item (guildid, TabId, SlotId, item_guid, item_entry) VALUES (?, ?, ?, ?, ?)"); + PrepareStatement(CHAR_ADD_GUILD_BANK_ITEM, "INSERT INTO guild_bank_item (guildid, TabId, SlotId, item_guid) VALUES (?, ?, ?, ?)"); PrepareStatement(CHAR_DEL_GUILD_BANK_ITEM, "DELETE FROM guild_bank_item WHERE guildid = ? AND TabId = ? AND SlotId = ?"); // 0: uint32, 1: uint8, 2: uint8 PrepareStatement(CHAR_DEL_GUILD_BANK_ITEMS, "DELETE FROM guild_bank_item WHERE guildid = ?"); // 0: uint32 PrepareStatement(CHAR_ADD_GUILD_BANK_RIGHT_DEFAULT, "INSERT INTO guild_bank_right (guildid, TabId, rid) VALUES (?, ?, ?)"); // 0: uint32, 1: uint8, 2: uint8 @@ -204,7 +224,7 @@ bool CharacterDatabaseConnection::Open() // 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, item_entry FROM guild_bank_item INNER JOIN item_instance ON item_guid = guid"); + "guildid, TabId, SlotId, item_guid, itemEntry FROM guild_bank_item gbi INNER JOIN item_instance ii ON gbi.item_guid = ii.guid"); PrepareStatement(CHAR_CLEAN_GUILD_RANKS, "DELETE FROM guild_rank WHERE guildId NOT IN (SELECT guildid FROM guild)"); PrepareStatement(CHAR_CLEAN_GUILD_MEMBERS, "DELETE FROM guild_member WHERE guildId NOT IN (SELECT guildid FROM guild)"); diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.h b/src/server/shared/Database/Implementation/CharacterDatabase.h index 3346632e75d..f53751065c7 100755 --- a/src/server/shared/Database/Implementation/CharacterDatabase.h +++ b/src/server/shared/Database/Implementation/CharacterDatabase.h @@ -94,13 +94,32 @@ enum CharacterDatabaseStatements CHAR_LOAD_ACCOUNT_DATA, CHAR_LOAD_PLAYER_MAILITEMS, CHAR_LOAD_AUCTION_ITEMS, + CHAR_ADD_AUCTION, + CHAR_DEL_AUCTION, + CHAR_LOAD_AUCTIONS, + CHAR_ADD_MAIL, + CHAR_DEL_MAIL, + CHAR_ADD_MAIL_ITEM, + CHAR_DEL_EMPTY_EXPIRED_MAIL, + CHAR_GET_EXPIRED_MAIL, + CHAR_GET_MAIL_ITEM_LITE, + CHAR_SET_MAIL_RETURNED, + CHAR_SET_MAIL_ITEM_RECEIVER, + CHAR_SET_ITEM_OWNER, CHAR_LOAD_GUILD_BANK_ITEMS, CHAR_LOAD_ITEM_REFUNDS, CHAR_LOAD_ITEM_BOP_TRADE, CHAR_DEL_ITEM_BOP_TRADE, CHAR_ADD_ITEM_BOP_TRADE, + CHAR_ADD_INVENTORY_ITEM, + CHAR_UPDATE_INVENTORY_ITEM, CHAR_DEL_INVENTORY_ITEM, + CHAR_ADD_ITEM_INSTANCE, + CHAR_UPDATE_ITEM_INSTANCE, + CHAR_UPDATE_ITEM_INSTANCE_ON_LOAD, CHAR_DEL_ITEM_INSTANCE, + CHAR_UPDATE_GIFT_OWNER, + CHAR_DEL_GIFT, CHAR_GET_ACCOUNT_BY_NAME, CHAR_ADD_GUILD, |