DB Schema/Characters: removed columns auctionhouse.item_template, character_inventory.item_template, guild_bank_item.item_entry and mail_items.item_template and created item_instance.itemEntry column instead of them.

As a side effect moved related queries to prepared statements.
As one more side effect fixed not shown and lost mail items in returned expired mail (patch by Quriq14).

--HG--
branch : trunk

1 files changed, 27 insertions, 12 deletions

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