diff options
8 files changed, 67 insertions, 38 deletions
diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql index 1573f65efdd..3ac49e0b8b3 100644 --- a/sql/base/characters_database.sql +++ b/sql/base/characters_database.sql @@ -1213,7 +1213,7 @@ CREATE TABLE `corpse` ( `corpseType` tinyint(3) unsigned NOT NULL DEFAULT '0', `instanceId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Instance Identifier', PRIMARY KEY (`guid`), - KEY `idx_type` (`corpse_type`), + KEY `idx_type` (`corpseType`), KEY `instance` (`instanceId`), KEY `Idx_player` (`player`), KEY `Idx_time` (`time`) @@ -1238,10 +1238,10 @@ DROP TABLE IF EXISTS `creature_respawn`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `creature_respawn` ( `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', - `respawntime` int(10) unsigned NOT NULL DEFAULT '0', - `instance` int(10) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`guid`,`instance`), - KEY `instance` (`instance`) + `respawnTime` int(10) unsigned NOT NULL DEFAULT '0', + `instanceId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Instance Identifier', + PRIMARY KEY (`guid`,`instanceId`), + KEY `instance` (`instanceId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Grid Loading System'; /*!40101 SET character_set_client = @saved_cs_client */; @@ -1311,10 +1311,10 @@ DROP TABLE IF EXISTS `gameobject_respawn`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gameobject_respawn` ( `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', - `respawntime` int(10) unsigned NOT NULL DEFAULT '0', - `instance` int(10) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`guid`,`instance`), - KEY `instance` (`instance`) + `respawnTime` int(10) unsigned NOT NULL DEFAULT '0', + `instanceId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Instance Identifier', + PRIMARY KEY (`guid`,`instanceId`), + KEY `instance` (`instanceId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Grid Loading System'; /*!40101 SET character_set_client = @saved_cs_client */; diff --git a/sql/updates/auth_char/2011_04_13_00_characters_creature_respawn.sql b/sql/updates/auth_char/2011_04_13_00_characters_creature_respawn.sql new file mode 100644 index 00000000000..e7435e3f061 --- /dev/null +++ b/sql/updates/auth_char/2011_04_13_00_characters_creature_respawn.sql @@ -0,0 +1,3 @@ +ALTER TABLE `creature_respawn` + CHANGE `respawntime` `respawnTime` int(10) unsigned NOT NULL DEFAULT '0', + CHANGE `instance` `instanceId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Instance Identifier'; diff --git a/sql/updates/auth_char/2011_04_13_01_characters_gameobject_respawn.sql b/sql/updates/auth_char/2011_04_13_01_characters_gameobject_respawn.sql new file mode 100644 index 00000000000..541e49cd4ec --- /dev/null +++ b/sql/updates/auth_char/2011_04_13_01_characters_gameobject_respawn.sql @@ -0,0 +1,3 @@ +ALTER TABLE `gameobject_respawn` + CHANGE `respawntime` `respawnTime` int(10) unsigned NOT NULL DEFAULT '0', + CHANGE `instance` `instanceId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Instance Identifier'; diff --git a/src/server/game/Globals/ObjectMgr.cpp b/src/server/game/Globals/ObjectMgr.cpp index 626e0e307ea..9aa6ed36d53 100755 --- a/src/server/game/Globals/ObjectMgr.cpp +++ b/src/server/game/Globals/ObjectMgr.cpp @@ -1950,8 +1950,7 @@ void ObjectMgr::LoadCreatureRespawnTimes() uint32 count = 0; - QueryResult result = CharacterDatabase.Query("SELECT guid,respawntime,instance FROM creature_respawn"); - + PreparedQueryResult result = CharacterDatabase.Query(CharacterDatabase.GetPreparedStatement(CHAR_LOAD_CREATURE_RESPAWNS)); if (!result) { sLog->outString(">> Loaded 0 creature respawn time."); @@ -1981,13 +1980,11 @@ void ObjectMgr::LoadGameobjectRespawnTimes() uint32 oldMSTime = getMSTime(); // Remove outdated data - PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_EXPIRED_GO_RESPAWN_TIMES); - CharacterDatabase.DirectExecute(stmt); + CharacterDatabase.DirectExecute(CharacterDatabase.GetPreparedStatement(CHAR_DEL_EXPIRED_GO_RESPAWNS)); uint32 count = 0; - QueryResult result = CharacterDatabase.Query("SELECT guid,respawntime,instance FROM gameobject_respawn"); - + PreparedQueryResult result = CharacterDatabase.Query(CharacterDatabase.GetPreparedStatement(CHAR_LOAD_GO_RESPAWNS)); if (!result) { sLog->outString(">> Loaded 0 gameobject respawn times. DB table `gameobject_respawn` is empty!"); @@ -7725,7 +7722,7 @@ void ObjectMgr::SaveCreatureRespawnTime(uint32 loguid, uint32 instance, time_t t m_CreatureRespawnTimesMtx.release(); } - PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_CREATURE_RESPAWN_TIME); + PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_CREATURE_RESPAWN); stmt->setUInt32(0, loguid); stmt->setUInt64(1, uint64(t)); stmt->setUInt32(2, instance); @@ -7741,7 +7738,7 @@ void ObjectMgr::RemoveCreatureRespawnTime(uint32 loguid, uint32 instance) m_CreatureRespawnTimesMtx.release(); } - PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_CREATURE_RESPAWN_TIME); + PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_CREATURE_RESPAWN); stmt->setUInt32(0, loguid); stmt->setUInt32(1, instance); CharacterDatabase.Execute(stmt); @@ -7773,7 +7770,7 @@ void ObjectMgr::SaveGORespawnTime(uint32 loguid, uint32 instance, time_t t) m_GORespawnTimesMtx.release(); } - PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_GO_RESPAWN_TIME); + PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_ADD_GO_RESPAWN); stmt->setUInt32(0, loguid); stmt->setUInt64(1, uint64(t)); stmt->setUInt32(2, instance); @@ -7789,7 +7786,7 @@ void ObjectMgr::RemoveGORespawnTime(uint32 loguid, uint32 instance) m_GORespawnTimesMtx.release(); } - PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_GO_RESPAWN_TIME); + PreparedStatement *stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_GO_RESPAWN); stmt->setUInt32(0, loguid); stmt->setUInt32(1, instance); CharacterDatabase.Execute(stmt); @@ -7824,8 +7821,12 @@ void ObjectMgr::DeleteRespawnTimeForInstance(uint32 instance) } m_CreatureRespawnTimesMtx.release(); } - CharacterDatabase.PExecute("DELETE FROM creature_respawn WHERE instance = '%u'", instance); - CharacterDatabase.PExecute("DELETE FROM gameobject_respawn WHERE instance = '%u'", instance); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_CREATURE_RESPAWN_BY_INSTANCE); + stmt->setUInt32(0, instance); + CharacterDatabase.Execute(stmt); + stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_GO_RESPAWN_BY_INSTANCE); + stmt->setUInt32(0, instance); + CharacterDatabase.Execute(stmt); } void ObjectMgr::DeleteGOData(uint32 guid) diff --git a/src/server/game/Instances/InstanceSaveMgr.cpp b/src/server/game/Instances/InstanceSaveMgr.cpp index 74ee9ef4c37..1617a2008bd 100755 --- a/src/server/game/Instances/InstanceSaveMgr.cpp +++ b/src/server/game/Instances/InstanceSaveMgr.cpp @@ -269,8 +269,8 @@ void InstanceSaveManager::LoadInstances() CharacterDatabase.DirectExecute("DELETE i.* FROM instance AS i LEFT JOIN character_instance AS ci ON i.id = ci.instance LEFT JOIN group_instance AS gi ON i.id = gi.instance WHERE ci.guid IS NULL AND gi.guid IS NULL"); // Delete invalid references to instance - CharacterDatabase.DirectExecute("DELETE tmp.* FROM creature_respawn AS tmp LEFT JOIN instance ON tmp.instance = instance.id WHERE tmp.instance > 0 AND instance.id IS NULL"); - CharacterDatabase.DirectExecute("DELETE tmp.* FROM gameobject_respawn AS tmp LEFT JOIN instance ON tmp.instance = instance.id WHERE tmp.instance > 0 AND instance.id IS NULL"); + CharacterDatabase.DirectExecute(CharacterDatabase.GetPreparedStatement(CHAR_DEL_NONEXISTENT_INSTANCE_CREATURE_RESPAWNS)); + CharacterDatabase.DirectExecute(CharacterDatabase.GetPreparedStatement(CHAR_DEL_NONEXISTENT_INSTANCE_GO_RESPAWNS)); CharacterDatabase.DirectExecute("DELETE tmp.* FROM character_instance AS tmp LEFT JOIN instance ON tmp.instance = instance.id WHERE tmp.instance > 0 AND instance.id IS NULL"); 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"); @@ -335,8 +335,7 @@ void InstanceSaveManager::LoadResetTimes() while (result->NextRow()); // update reset time for normal instances with the max creature respawn time + X hours - result = CharacterDatabase.Query("SELECT MAX(respawntime), instance FROM creature_respawn WHERE instance > 0 GROUP BY instance"); - if (result) + if (PreparedQueryResult result = CharacterDatabase.Query(CharacterDatabase.GetPreparedStatement(CHAR_GET_MAX_CREATURE_RESPAWNS))) { do { diff --git a/src/server/game/OutdoorPvP/OutdoorPvP.cpp b/src/server/game/OutdoorPvP/OutdoorPvP.cpp index 4d5fc13ac41..d04266f7c29 100755 --- a/src/server/game/OutdoorPvP/OutdoorPvP.cpp +++ b/src/server/game/OutdoorPvP/OutdoorPvP.cpp @@ -166,7 +166,10 @@ bool OPvPCapturePoint::DelCreature(uint32 type) //if (Map * map = sMapMgr->FindMap(cr->GetMapId())) // map->Remove(cr,false); // delete respawn time for this creature - CharacterDatabase.PExecute("DELETE FROM creature_respawn WHERE guid = '%u'", guid); + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_CREATURE_RESPAWN_BY_GUID); + stmt->setUInt32(0, guid); + CharacterDatabase.Execute(stmt); + cr->AddObjectToRemoveList(); sObjectMgr->DeleteCreatureData(guid); m_CreatureTypes[m_Creatures[type]] = 0; diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp index 37d56b2ee1f..78cc2a2ea9a 100755 --- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp +++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp @@ -226,12 +226,6 @@ void CharacterDatabaseConnection::DoPrepareStatements() 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) - PREPARE_STATEMENT(CHAR_DEL_CREATURE_RESPAWN_TIME, "DELETE FROM creature_respawn WHERE guid = ? AND instance = ?", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_ADD_CREATURE_RESPAWN_TIME, "REPLACE INTO creature_respawn VALUES (?, ?, ?)", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_DEL_EXPIRED_GO_RESPAWN_TIMES, "DELETE FROM gameobject_respawn WHERE respawntime <= UNIX_TIMESTAMP()", CONNECTION_SYNCH) - PREPARE_STATEMENT(CHAR_DEL_GO_RESPAWN_TIME, "DELETE FROM gameobject_respawn WHERE guid = ? AND instance = ?", CONNECTION_ASYNC) - PREPARE_STATEMENT(CHAR_ADD_GO_RESPAWN_TIME, "REPLACE INTO gameobject_respawn VALUES (?, ?, ?)", CONNECTION_ASYNC) - // Chat channel handling PREPARE_STATEMENT(CHAR_LOAD_CHANNEL, "SELECT announce, ownership, password, bannedList FROM channels WHERE name = ? AND team = ?", CONNECTION_SYNCH) PREPARE_STATEMENT(CHAR_ADD_CHANNEL, "INSERT INTO channels(name, team, lastUsed) VALUES (?, ?, UNIX_TIMESTAMP())", CONNECTION_ASYNC) @@ -312,4 +306,21 @@ void CharacterDatabaseConnection::DoPrepareStatements() 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) + + // Creature respawn + PREPARE_STATEMENT(CHAR_LOAD_CREATURE_RESPAWNS, "SELECT guid, respawnTime, instanceId FROM creature_respawn", CONNECTION_SYNCH) + PREPARE_STATEMENT(CHAR_ADD_CREATURE_RESPAWN, "REPLACE INTO creature_respawn (guid, respawnTime, instanceId) VALUES (?, ?, ?)", CONNECTION_ASYNC) + PREPARE_STATEMENT(CHAR_DEL_CREATURE_RESPAWN, "DELETE FROM creature_respawn WHERE guid = ? AND instanceId = ?", CONNECTION_ASYNC) + PREPARE_STATEMENT(CHAR_DEL_CREATURE_RESPAWN_BY_GUID, "DELETE FROM creature_respawn WHERE guid = ?", CONNECTION_ASYNC) + PREPARE_STATEMENT(CHAR_DEL_CREATURE_RESPAWN_BY_INSTANCE, "DELETE FROM creature_respawn WHERE instanceId = ?", CONNECTION_ASYNC) + PREPARE_STATEMENT(CHAR_GET_MAX_CREATURE_RESPAWNS, "SELECT MAX(respawnTime), instanceId FROM creature_respawn WHERE instanceId > 0 GROUP BY instanceId", CONNECTION_SYNCH) + PREPARE_STATEMENT(CHAR_DEL_NONEXISTENT_INSTANCE_CREATURE_RESPAWNS, "DELETE FROM creature_respawn WHERE instanceId > 0 AND instanceId NOT IN (SELECT instanceId FROM instance)", CONNECTION_SYNCH) + + // Gameobject respawn + PREPARE_STATEMENT(CHAR_LOAD_GO_RESPAWNS, "SELECT guid, respawnTime, instanceId FROM gameobject_respawn", CONNECTION_SYNCH) + PREPARE_STATEMENT(CHAR_ADD_GO_RESPAWN, "REPLACE INTO gameobject_respawn (guid, respawnTime, instanceId) VALUES (?, ?, ?)", CONNECTION_ASYNC) + PREPARE_STATEMENT(CHAR_DEL_GO_RESPAWN, "DELETE FROM gameobject_respawn WHERE guid = ? AND instanceId = ?", CONNECTION_ASYNC) + PREPARE_STATEMENT(CHAR_DEL_GO_RESPAWN_BY_INSTANCE, "DELETE FROM gameobject_respawn WHERE instanceId = ?", CONNECTION_ASYNC) + PREPARE_STATEMENT(CHAR_DEL_EXPIRED_GO_RESPAWNS, "DELETE FROM gameobject_respawn WHERE respawnTime <= UNIX_TIMESTAMP(NOW())", CONNECTION_SYNCH) + PREPARE_STATEMENT(CHAR_DEL_NONEXISTENT_INSTANCE_GO_RESPAWNS, "DELETE FROM gameobject_respawn WHERE instanceId > 0 AND instanceId NOT IN (SELECT instanceId FROM instance)", CONNECTION_SYNCH) } diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.h b/src/server/shared/Database/Implementation/CharacterDatabase.h index 4e66cc7eadc..fec9791e701 100755 --- a/src/server/shared/Database/Implementation/CharacterDatabase.h +++ b/src/server/shared/Database/Implementation/CharacterDatabase.h @@ -193,12 +193,6 @@ enum CharacterDatabaseStatements CHAR_DEL_NONEXISTENT_GUILD_BANK_RIGHTS, CHAR_DEL_NONEXISTENT_GUILD_BANK_ITEMS, - CHAR_DEL_CREATURE_RESPAWN_TIME, - CHAR_ADD_CREATURE_RESPAWN_TIME, - CHAR_DEL_EXPIRED_GO_RESPAWN_TIMES, - CHAR_DEL_GO_RESPAWN_TIME, - CHAR_ADD_GO_RESPAWN_TIME, - CHAR_LOAD_CHANNEL, CHAR_ADD_CHANNEL, CHAR_SET_CHANNEL, @@ -265,6 +259,21 @@ enum CharacterDatabaseStatements CHAR_DEL_OLD_CORPSES, CHAR_RESET_NONEXISTENT_INSTANCE_FOR_CORPSES, + CHAR_LOAD_CREATURE_RESPAWNS, + CHAR_ADD_CREATURE_RESPAWN, + CHAR_DEL_CREATURE_RESPAWN, + CHAR_DEL_CREATURE_RESPAWN_BY_GUID, + CHAR_DEL_CREATURE_RESPAWN_BY_INSTANCE, + CHAR_GET_MAX_CREATURE_RESPAWNS, + CHAR_DEL_NONEXISTENT_INSTANCE_CREATURE_RESPAWNS, + + CHAR_LOAD_GO_RESPAWNS, + CHAR_ADD_GO_RESPAWN, + CHAR_DEL_GO_RESPAWN, + CHAR_DEL_GO_RESPAWN_BY_INSTANCE, + CHAR_DEL_EXPIRED_GO_RESPAWNS, + CHAR_DEL_NONEXISTENT_INSTANCE_GO_RESPAWNS, + MAX_CHARACTERDATABASE_STATEMENTS, }; |