| cybernine186 |
10-23-2008 01:00 PM |
Player Backup & Corpses
I modified some code to help with restoring corpses after they have rotted. It inserts the corpses that are due to rot in the `player_corpses_backup` table.
After 4 weeks the corpses are then deleted from the table.
This saves me a lot of time doing it this way instead of pulling sql backups for 30 minutes.
common\shareddb.h
Code:
sint32 DeleteStalePlayerCorpses();
sint32 DeleteStalePlayerBackups();
sint32 DeleteStaleCorpseBackups();
common\shareddb.cpp
Copy the below code and replace it over sint32 SharedDatabase::DeleteStalePlayerCorpses()
Code:
sint32 SharedDatabase::DeleteStalePlayerCorpses() {
char errbuf[MYSQL_ERRMSG_SIZE];
char *query = 0;
int32 affected_rows = 0;
// Insert corpses that are due to be deleted into the `player_corpses_backup` table
// The Unix Timestamp is in seconds: 604800 seconds = 1 week
if (RunQuery(query, MakeAnyLenString(&query, "INSERT INTO `player_corpses_backup` (`charid`, `charname`, `parent_corpse_id`, `zoneid`, `x`, `y`, `z`, `heading`, `data`, `timeofdeath`) (SELECT `charid`, `charname`, `id`, `zoneid`, `x`, `y`, `z`, `heading`, `data`, `timeofdeath` FROM `player_corpses` WHERE (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(`timeofdeath`)) > 604800 AND NOT `timeofdeath` = 0)"), errbuf, 0)) {
safe_delete_array(query);
// Delete the corpses from the `player_corpses` table
if (!RunQuery(query, MakeAnyLenString(&query, "DELETE FROM `player_corpses` WHERE (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(`timeofdeath`)) > 604800 AND NOT `timeofdeath` = 0"), errbuf, 0, &affected_rows)) {
safe_delete_array(query);
return -1;
}
return affected_rows;
} else {
safe_delete_array(query);
return -1;
}
}
sint32 SharedDatabase::DeleteStaleCorpseBackups() {
char errbuf[MYSQL_ERRMSG_SIZE];
char *query = 0;
int32 affected_rows = 0;
// 2419200 seconds = 4 weeks
if (!RunQuery(query, MakeAnyLenString(&query, "DELETE FROM `player_corpses_backup` WHERE (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(`timeofdelete`)) > 2419200"), errbuf, 0, &affected_rows)) {
safe_delete_array(query);
return -1;
}
safe_delete_array(query);
return affected_rows;
}
world\net.cpp
The 3rd line should be added on line 298 in net.cpp
Code:
_log(WORLD__INIT, "Deleted %i stale player corpses from database", database.DeleteStalePlayerCorpses());
_log(WORLD__INIT, "Deleted %i stale player backups from database", database.DeleteStalePlayerBackups());
_log(WORLD__INIT, "Deleted %i stale corpse backups from database", database.DeleteStaleCorpseBackups());
SQL Changes
Code:
DROP TABLE IF EXISTS `player_corpses_backup`;
CREATE TABLE IF NOT EXISTS `player_corpses_backup` (
`id` int(11) unsigned NOT NULL auto_increment,
`charid` int(11) unsigned NOT NULL default '0',
`charname` varchar(64) NOT NULL,
`parent_corpse_id` int(11) unsigned NOT NULL default '0',
`zoneid` smallint(11) NOT NULL default '0',
`x` float NOT NULL default '0',
`y` float NOT NULL default '0',
`z` float NOT NULL default '0',
`heading` float NOT NULL default '0',
`data` blob,
`timeofdeath` datetime NOT NULL default '0000-00-00 00:00:00',
`timeofdelete` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `charid` (`charid`)
) ENGINE=InnoDB;
|