Player Backup & Corpses

cybernine186 · #1 10-23-2008, 01:00 PM

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;

cybernine186 · #2 10-24-2008, 09:23 PM

Awesome... Simply awesome George.....