Core/CharDB Cleanup: alter `corpse` table making column names lowerCamel and move all queries to prepared statements.

NOTICE: column can be named `guid` only if it represents character guid. All other guids will be renamed to reflect their purpose (like corpseGuid in this specific case)

8 files changed, 88 insertions, 64 deletions

diff --git a/src/server/game/Entities/Corpse/Corpse.cpp b/src/server/game/Entities/Corpse/Corpse.cpp
index 48c7448e32c..1e643f88c1e 100755
--- a/src/server/game/Entities/Corpse/Corpse.cpp
+++ b/src/server/game/Entities/Corpse/Corpse.cpp
@@ -106,29 +106,27 @@ void Corpse::SaveToDB()
     SQLTransaction trans = CharacterDatabase.BeginTransaction();
     DeleteFromDB(trans);
 
-    std::ostringstream ss;
-    ss  << "INSERT INTO corpse (guid,player,position_x,position_y,position_z,orientation,map,displayId,itemCache,bytes1,bytes2,guild,flags,dynFlags,time,corpse_type,instance,phaseMask) VALUES ("
-        << GetGUIDLow() << ", "
-        << GUID_LOPART(GetOwnerGUID()) << ", "
-        << GetPositionX() << ", "
-        << GetPositionY() << ", "
-        << GetPositionZ() << ", "
-        << GetOrientation() << ", "
-        << GetMapId() << ", "
-        << GetUInt32Value(CORPSE_FIELD_DISPLAY_ID) << ", '";
-    for (uint16 i = 0; i < EQUIPMENT_SLOT_END; ++i)
-        ss << GetUInt32Value(CORPSE_FIELD_ITEM+i) << " ";
-    ss  << "', "
-        << GetUInt32Value(CORPSE_FIELD_BYTES_1) << ", "
-        << GetUInt32Value(CORPSE_FIELD_BYTES_2) << ", "
-        << GetUInt32Value(CORPSE_FIELD_GUILD) << ", "
-        << GetUInt32Value(CORPSE_FIELD_FLAGS) << ", "
-        << GetUInt32Value(CORPSE_FIELD_DYNAMIC_FLAGS) << ", "
-        << uint32(m_time) << ", "
-        << uint32(GetType()) << ", "
-        << int(GetInstanceId()) << ", "
-        << uint16(GetPhaseMask()) << ")";           // prevent out of range error
-    trans->Append(ss.str().c_str());
+    PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_CORPSE);
+    stmt->setUInt32(0, GetGUIDLow());                                           // corpseGuid
+    stmt->setUInt32(1, GUID_LOPART(GetOwnerGUID()));                            // guid
+    stmt->setFloat (2, GetPositionX());                                         // posX
+    stmt->setFloat (3, GetPositionY());                                         // posY
+    stmt->setFloat (4, GetPositionZ());                                         // posZ
+    stmt->setFloat (5, GetOrientation());                                       // orientation
+    stmt->setUInt16(6, GetMapId());                                             // mapId
+    stmt->setUInt32(7, GetUInt32Value(CORPSE_FIELD_DISPLAY_ID));                // displayId
+    stmt->setString(8, _ConcatFields(CORPSE_FIELD_ITEM, EQUIPMENT_SLOT_END));   // itemCache
+    stmt->setUInt32(9, GetUInt32Value(CORPSE_FIELD_BYTES_1));                   // bytes1
+    stmt->setUInt32(10, GetUInt32Value(CORPSE_FIELD_BYTES_2));                  // bytes2
+    stmt->setUInt32(11, GetUInt32Value(CORPSE_FIELD_GUILD));                    // guildId
+    stmt->setUInt8 (12, GetUInt32Value(CORPSE_FIELD_FLAGS));                    // flags
+    stmt->setUInt8 (13, GetUInt32Value(CORPSE_FIELD_DYNAMIC_FLAGS));            // dynFlags
+    stmt->setUInt32(14, uint32(m_time));                                        // time
+    stmt->setUInt8 (15, GetType());                                             // corpseType
+    stmt->setUInt32(16, GetInstanceId());                                       // instanceId
+    stmt->setUInt16(17, GetPhaseMask());                                        // phaseMask
+    trans->Append(stmt);
+
     CharacterDatabase.CommitTransaction(trans);
 }
 
@@ -148,23 +146,41 @@ void Corpse::DeleteBonesFromWorld()
 
 void Corpse::DeleteFromDB(SQLTransaction& trans)
 {
+    PreparedStatement* stmt = NULL;
     if (GetType() == CORPSE_BONES)
-        // only specific bones
-        trans->PAppend("DELETE FROM corpse WHERE guid = '%d'", GetGUIDLow());
+    {
+        // Only specific bones
+        stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_CORPSE);
+        stmt->setUInt32(0, GetGUIDLow());
+    }
     else
+    {
         // all corpses (not bones)
-        trans->PAppend("DELETE FROM corpse WHERE player = '%d' AND corpse_type <> '0'",  GUID_LOPART(GetOwnerGUID()));
+        stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_PLAYER_CORPSES);
+        stmt->setUInt32(0, GUID_LOPART(GetOwnerGUID()));
+    }
+    trans->Append(stmt);
 }
 
 bool Corpse::LoadFromDB(uint32 guid, Field *fields)
 {
-    //                0           1           2            3    4          5          6       7       8      9     10        11    12           13        14         15    16      17
-    //SELECT position_x, position_y, position_z, orientation, map, displayId, itemCache, bytes1, bytes2, guild, flags, dynFlags, time, corpse_type, instance, phaseMask, guid, player FROM corpse WHERE corpse_type <> 0
-    float positionX = fields[0].GetFloat();
-    float positionY = fields[1].GetFloat();
-    float positionZ = fields[2].GetFloat();
-    float ort       = fields[3].GetFloat();
-    uint32 mapid    = fields[4].GetUInt16();
+    uint32 ownerGuid = fields[17].GetUInt32();
+    //        0     1     2     3            4      5          6          7       8       9        10     11        12    13          14          15         16          17
+    // SELECT posX, posY, posZ, orientation, mapId, displayId, itemCache, bytes1, bytes2, guildId, flags, dynFlags, time, corpseType, instanceId, phaseMask, corpseGuid, guid FROM corpse WHERE corpseType <> 0
+    m_type = CorpseType(fields[13].GetUInt32());
+    if (m_type >= MAX_CORPSE_TYPE)
+    {
+        sLog->outError("Corpse (guid: %u, owner: %u) have wrong corpse type (%u), not loading.", guid, ownerGuid, m_type);
+        return false;
+    }
+    if (m_type != CORPSE_BONES)
+        m_isWorldObject = true;
+
+    float posX   = fields[0].GetFloat();
+    float posY   = fields[1].GetFloat();
+    float posZ   = fields[2].GetFloat();
+    float o      = fields[3].GetFloat();
+    uint32 mapId = fields[4].GetUInt16();
 
     Object::_Create(guid, 0, HIGHGUID_CORPSE);
 
@@ -175,33 +191,23 @@ bool Corpse::LoadFromDB(uint32 guid, Field *fields)
     SetUInt32Value(CORPSE_FIELD_GUILD, fields[9].GetUInt32());
     SetUInt32Value(CORPSE_FIELD_FLAGS, fields[10].GetUInt8());
     SetUInt32Value(CORPSE_FIELD_DYNAMIC_FLAGS, fields[11].GetUInt32());
-    SetUInt64Value(CORPSE_FIELD_OWNER, MAKE_NEW_GUID(fields[17].GetUInt32(), 0, HIGHGUID_PLAYER));
+    SetUInt64Value(CORPSE_FIELD_OWNER, MAKE_NEW_GUID(ownerGuid, 0, HIGHGUID_PLAYER));
 
     m_time = time_t(fields[12].GetUInt32());
-    m_type = CorpseType(fields[13].GetUInt32());
-
-    if (m_type >= MAX_CORPSE_TYPE)
-    {
-        sLog->outError("Corpse (guidlow %d, owner %d) have wrong corpse type, not load.",GetGUIDLow(),GUID_LOPART(GetOwnerGUID()));
-        return false;
-    }
-
-    if (m_type != CORPSE_BONES)
-        m_isWorldObject = true;
 
-    uint32 instanceid  = fields[14].GetUInt32();
+    uint32 instanceId  = fields[14].GetUInt32();
     uint32 phaseMask   = fields[15].GetUInt16();
 
     // place
-    SetLocationInstanceId(instanceid);
-    SetLocationMapId(mapid);
+    SetLocationInstanceId(instanceId);
+    SetLocationMapId(mapId);
     SetPhaseMask(phaseMask, false);
-    Relocate(positionX, positionY, positionZ, ort);
+    Relocate(posX, posY, posZ, o);
 
     if (!IsPositionValid())
     {
-        sLog->outError("Corpse (guidlow %d, owner %d) not created. Suggested coordinates isn't valid (X: %f Y: %f)",
-            GetGUIDLow(), GUID_LOPART(GetOwnerGUID()), GetPositionX(), GetPositionY());
+        sLog->outError("Corpse (guid: %u, owner: %u) is not created, given coordinates are not valid (X: %f, Y: %f, Z: %f)",
+            GetGUIDLow(), GUID_LOPART(GetOwnerGUID()), posX, posY, posZ);
         return false;
     }
 
diff --git a/src/server/game/Entities/Object/Object.cpp b/src/server/game/Entities/Object/Object.cpp
index 51aaa55aafe..a8ca18a04b1 100755
--- a/src/server/game/Entities/Object/Object.cpp
+++ b/src/server/game/Entities/Object/Object.cpp
@@ -151,6 +151,14 @@ void Object::_Create(uint32 guidlow, uint32 entry, HighGuid guidhigh)
     m_PackGUID.appendPackGUID(GetGUID());
 }
 
+std::string Object::_ConcatFields(uint16 startIndex, uint16 size) const
+{
+    std::ostringstream ss;
+    for (uint16 index = 0; index < size; ++index)
+        ss << GetUInt32Value(index + startIndex) << " ";
+    return ss.str();
+}
+
 void Object::BuildMovementUpdateBlock(UpdateData * data, uint32 flags) const
 {
     ByteBuffer buf(500);
diff --git a/src/server/game/Entities/Object/Object.h b/src/server/game/Entities/Object/Object.h
index 12ef8d93f25..eba300feb43 100755
--- a/src/server/game/Entities/Object/Object.h
+++ b/src/server/game/Entities/Object/Object.h
@@ -336,6 +336,7 @@ class Object
 
         void _InitValues();
         void _Create (uint32 guidlow, uint32 entry, HighGuid guidhigh);
+        std::string _ConcatFields(uint16 startIndex, uint16 size) const;
         void _LoadIntoDataField(const char* data, uint32 startOffset, uint32 count);
 
         virtual void _SetUpdateBits(UpdateMask *updateMask, Player *target) const;
diff --git a/src/server/game/Globals/ObjectMgr.cpp b/src/server/game/Globals/ObjectMgr.cpp
index 41458d985bf..5fec0c4806b 100755
--- a/src/server/game/Globals/ObjectMgr.cpp
+++ b/src/server/game/Globals/ObjectMgr.cpp
@@ -6657,7 +6657,7 @@ void ObjectMgr::SetHighestGuids()
     if (result)
         m_mailid = (*result)[0].GetUInt32()+1;
 
-    result = CharacterDatabase.Query("SELECT MAX(guid) FROM corpse");
+    result = CharacterDatabase.Query("SELECT MAX(corpseGuid) FROM corpse");
     if (result)
         m_hiCorpseGuid = (*result)[0].GetUInt32()+1;
 
@@ -6669,7 +6669,7 @@ void ObjectMgr::SetHighestGuids()
     if (result)
         m_equipmentSetGuid = (*result)[0].GetUInt64()+1;
 
-    result = CharacterDatabase.Query("SELECT MAX(guildid) FROM guild");
+    result = CharacterDatabase.Query("SELECT MAX(guildId) FROM guild");
     if (result)
         m_guildId = (*result)[0].GetUInt32()+1;
 
@@ -7186,11 +7186,7 @@ void ObjectMgr::LoadCorpses()
 {
     uint32 oldMSTime = getMSTime();
 
-    //                                                      0           1           2            3         4      5          6         7       8       9     10      11
-    QueryResult result = CharacterDatabase.Query("SELECT position_x, position_y, position_z, orientation, map, displayId, itemCache, bytes1, bytes2, guild, flags, dynFlags"
-    //                                               12       13          14        15       16     17
-                                                 ", time, corpse_type, instance, phaseMask, guid, player FROM corpse WHERE corpse_type <> 0");
-
+    PreparedQueryResult result = CharacterDatabase.Query(CharacterDatabase.GetPreparedStatement(CHAR_LOAD_CORPSES));
     if (!result)
     {
         sLog->outString(">> Loaded 0 corpses. DB table `pet_name_generation` is empty.");
@@ -7199,15 +7195,12 @@ void ObjectMgr::LoadCorpses()
     }
 
     uint32 count = 0;
-
     do
     {
-
         Field *fields = result->Fetch();
-
         uint32 guid = fields[16].GetUInt32();
 
-        Corpse *corpse = new Corpse;
+        Corpse *corpse = new Corpse();
         if (!corpse->LoadFromDB(guid, fields))
         {
             delete corpse;
@@ -7215,7 +7208,6 @@ void ObjectMgr::LoadCorpses()
         }
 
         sObjectAccessor->AddCorpse(corpse);
-
         ++count;
     }
     while (result->NextRow());
diff --git a/src/server/game/Instances/InstanceSaveMgr.cpp b/src/server/game/Instances/InstanceSaveMgr.cpp
index a13c5b79ac6..74ee9ef4c37 100755
--- a/src/server/game/Instances/InstanceSaveMgr.cpp
+++ b/src/server/game/Instances/InstanceSaveMgr.cpp
@@ -275,7 +275,7 @@ void InstanceSaveManager::LoadInstances()
     CharacterDatabase.DirectExecute("DELETE tmp.* FROM group_instance     AS tmp LEFT JOIN instance ON tmp.instance = instance.id WHERE tmp.instance > 0 AND instance.id IS NULL");
 
     // Clean invalid references to instance
-    CharacterDatabase.DirectExecute("UPDATE corpse     AS tmp LEFT JOIN instance ON tmp.instance    = instance.id SET tmp.instance    = 0 WHERE tmp.instance    > 0 AND instance.id IS NULL");
+    CharacterDatabase.DirectExecute(CharacterDatabase.GetPreparedStatement(CHAR_RESET_NONEXISTENT_INSTANCE_FOR_CORPSES));
     CharacterDatabase.DirectExecute("UPDATE characters AS tmp LEFT JOIN instance ON tmp.instance_id = instance.id SET tmp.instance_id = 0 WHERE tmp.instance_id > 0 AND instance.id IS NULL");
 
     // Initialize instance id storage (Needs to be done after the trash has been clean out)
diff --git a/src/server/game/World/World.cpp b/src/server/game/World/World.cpp
index bfcf0d0125c..1c4022d152e 100755
--- a/src/server/game/World/World.cpp
+++ b/src/server/game/World/World.cpp
@@ -1243,7 +1243,9 @@ void World::SetInitialWorldSettings()
     LoginDatabase.PExecute("UPDATE realmlist SET icon = %u, timezone = %u WHERE id = '%d'", server_type, realm_zone, realmID);
 
     ///- Remove the bones (they should not exist in DB though) and old corpses after a restart
-    CharacterDatabase.PExecute("DELETE FROM corpse WHERE corpse_type = '0' OR time < (UNIX_TIMESTAMP()-'%u')", 3 * DAY);
+    PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_OLD_CORPSES);
+    stmt->setUInt32(0, 3 * DAY);
+    CharacterDatabase.Execute(stmt);
 
     ///- Load the DBC files
     sLog->outString("Initialize data stores...");
diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp
index 0a73bed003a..37d56b2ee1f 100755
--- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp
+++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp
@@ -304,4 +304,12 @@ void CharacterDatabaseConnection::DoPrepareStatements()
     PREPARE_STATEMENT(CHAR_ADD_PLAYER_HOMEBIND, "INSERT INTO character_homebind (guid, mapId, zoneId, posX, posY, posZ) VALUES (?, ?, ?, ?, ?, ?)", CONNECTION_ASYNC)
     PREPARE_STATEMENT(CHAR_SET_PLAYER_HOMEBIND, "UPDATE character_homebind SET mapId = ?, zoneId = ?, posX = ?, posY = ?, posZ = ? WHERE guid = ?", CONNECTION_ASYNC)
     PREPARE_STATEMENT(CHAR_DEL_PLAYER_HOMEBIND, "DELETE FROM character_homebind WHERE guid = ?", CONNECTION_ASYNC)
+
+    // Corpse
+    PREPARE_STATEMENT(CHAR_LOAD_CORPSES, "SELECT posX, posY, posZ, orientation, mapId, displayId, itemCache, bytes1, bytes2, guildId, flags, dynFlags, time, corpseType, instanceId, phaseMask, corpseGuid, guid FROM corpse WHERE corpseType <> 0", CONNECTION_SYNCH)
+    PREPARE_STATEMENT(CHAR_ADD_CORPSE, "INSERT INTO corpse (corpseGuid, guid, posX, posY, posZ, orientation, mapId, displayId, itemCache, bytes1, bytes2, guildId, flags, dynFlags, time, corpseType, instanceId, phaseMask) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", CONNECTION_ASYNC)
+    PREPARE_STATEMENT(CHAR_DEL_CORPSE, "DELETE FROM corpse WHERE corpseGuid = ?", CONNECTION_ASYNC)
+    PREPARE_STATEMENT(CHAR_DEL_PLAYER_CORPSES, "DELETE FROM corpse WHERE guid = ? AND corpseType <> 0", CONNECTION_ASYNC)
+    PREPARE_STATEMENT(CHAR_DEL_OLD_CORPSES, "DELETE FROM corpse WHERE corpseType = 0 OR time < (UNIX_TIMESTAMP(NOW()) - ?)", CONNECTION_ASYNC)
+    PREPARE_STATEMENT(CHAR_RESET_NONEXISTENT_INSTANCE_FOR_CORPSES, "UPDATE corpse SET instanceId = 0 WHERE instanceId > 0 AND instanceId NOT IN (SELECT id FROM instance)", CONNECTION_SYNCH)
 }
diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.h b/src/server/shared/Database/Implementation/CharacterDatabase.h
index cd68e7419ea..4e66cc7eadc 100755
--- a/src/server/shared/Database/Implementation/CharacterDatabase.h
+++ b/src/server/shared/Database/Implementation/CharacterDatabase.h
@@ -258,6 +258,13 @@ enum CharacterDatabaseStatements
     CHAR_SET_PLAYER_HOMEBIND,
     CHAR_DEL_PLAYER_HOMEBIND,
 
+    CHAR_LOAD_CORPSES,
+    CHAR_ADD_CORPSE,
+    CHAR_DEL_CORPSE,
+    CHAR_DEL_PLAYER_CORPSES,
+    CHAR_DEL_OLD_CORPSES,
+    CHAR_RESET_NONEXISTENT_INSTANCE_FOR_CORPSES,
+
     MAX_CHARACTERDATABASE_STATEMENTS,
 };