[XAseco2] Database Purge Plugin

Discuss everything about Xymph's Aseco flavoured server control scripts for TM Forever / classic TMN and for TM² Canyon.

Moderators: Xymph, TM-Patrol

Post Reply
User avatar
Mistral
Pit Crew
Pit Crew
Posts: 1304
Joined: 02 May 2006 15:13
Owned TM-games: tmuf
Contact:

[XAseco2] Database Purge Plugin

Post by Mistral » 18 Oct 2011 14:18

Here some code where you can create a purge plugin.

Usage: /dbcleanup (Masteradmins only)

What it does:
- Delete maps not in map pool anymore
- Delete records from deleted players to have accurate average
- Delete records from deleted maps to have accurate average

Xymph, feel free to build in.

Code: Select all

<?php
/****************************************************************************
 *
 * XAseco2 plugin to cleanup the database
 * - delete offline maps
 * - delete records if player or map got deleted
 *
 * (C) 2011 by Mistral
 *
 * Dependencies: plugin.rasp.php
 *
 ****************************************************************************/

Aseco::addChatCommand('dbcleanup', 'deletes all related data from deleted players/challenges', true);

function chat_dbcleanup($aseco, $command) {
	global $rasp, $prune_records_times;

	$admin = $command['author'];
	$login = $admin->login;

	// split params into arrays & insure optional parameters exist
	$arglist = explode(' ', $command['params'], 2);
	if (!isset($arglist[1])) $arglist[1] = '';
	$command['params'] = explode(' ', preg_replace('/ +/', ' ', $command['params']));
	if (!isset($command['params'][1])) $command['params'][1] = '';

	// check if chat command was allowed for a masteradmin/admin/operator
	if (!$aseco->isMasterAdmin($admin)) {
		// write warning in console
		$aseco->console($login . ' tried to use admin chat command (no permission!): ' . $arglist[0] . ' ' . $arglist[1]);
		// show chat message
		$aseco->client->query('ChatSendToLogin', $aseco->formatColors('{#error}You don\'t have the required admin rights to do that!'), $login);
		return false;
	}

	$maps = mistralGetOfflineMaps($aseco, $admin);
	$deleted = 0;
	foreach($maps as $map) {
		$id = $map['id'];
		$query = "DELETE FROM maps where id=$id;";
		mysql_query($query);
		$status = mysql_affected_rows();
		if ($status != -1) {
			$deleted += $status;
		}
	}
	$aseco->console("$deleted offline tracks deleted from database.");
	$aseco->client->query('ChatSendToLogin', $aseco->formatColors("{#error}$deleted offline tracks deleted from database."), $login);

	// Rasp will delete orphaned records
	$tmp = $prune_records_times;
	$prune_records_times = true;
	$rasp->cleanData();
	$prune_records_times = $tmp;
}

/** mistralGetOfflineMaps
***************************/
function mistralGetOfflineMaps($aseco, $player) {
 	$maps = array();
 	
	getAllMaps($player, "*", "*");

	$query = "DROP TABLE mistral_maps;";
	mysql_query($query);
	$query = "CREATE TABLE `mistral_maps` (
			`Id` mediumint(9) NOT NULL auto_increment,
			`Uid` varchar(27) NOT NULL default '',
			`Name` varchar(100) NOT NULL default '',
			PRIMARY KEY  (`Id`),
			UNIQUE KEY `Uid` (`Uid`)
			) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";
	if (!mysql_query($query)) {
		$aseco->console_text("Cannot create table \"mistral_maps\"!.");
		return $maps;
	}

	foreach ($player->maplist as $row) {
		$uid = $row['uid'];
		$name = $row['name'];
		$query = "INSERT INTO mistral_maps (Uid, Name) VALUES (".quotedString($uid).",".quotedString($name).");";
		if (!mysql_query($query)) {
			$aseco->console_text("Cannot insert map into 'mistral_maps': UId='".$uid."'; Name='".$name."'");
			return $maps;
		}
	}
		
	$query = "SELECT id,uid name FROM maps where uid not in (select uid from mistral_maps);";
	$result = mysql_query($query);
	while ($map = mysql_fetch_array($result))
		$maps[]=$map;
	mysql_free_result($result);
	$query = "DROP TABLE mistral_maps;";
	mysql_query($query);
	return $maps;
}

?>

Last edited by Mistral on 18 Oct 2011 21:43, edited 3 times in total.

Xymph
Pit Crew
Pit Crew
Posts: 5654
Joined: 19 Aug 2007 12:58
Owned TM-games: TMN, TMU, TMF, TM²
Contact:

Re: [XAseco2] Database Purge Plugin

Post by Xymph » 18 Oct 2011 18:17

Mistral wrote:Here some code where you can create a purge plugin. Yes i know it can be optimized, but the (sql)code is rather old. Anyway i totally miss this feature in every aseco/rasp version since the early beginning.
RASP and therefore XAseco has had this feature just about forever:
Mistral wrote:- Delete records from deleted maps
See $prune_records_times in rasp.settings.php. :wink:
Mistral wrote:What it does:
- Delete maps not in map pool anymore
- Delete records from deleted players

Xymph, feel free to build in.
Thanks for the offer, but there's a good reason why the latter feature isn't built into the main system, that has been discussed several times in the past few years: the 'records' table needs to be kept in sync with the 'rs_times' table for each map. This is what /admin delrec does for one track. Doing this for potentially dozens/hundreds of tracks on a big server can take quite a few seconds. During that time XAseco wouldn't be able to do anything else, as it's not a multi-threaded application. That's why I've always said and will keep saying that major database maintenance should be done by an external/offline script, such as Nouse Web Tools, and not in a tool where interactive responsiveness is important.

As for the other feature of deleting entries from the 'challenges' table for deleted maps, I don't see much benefit of that. It's just one entry per map and thus doesn't take up enough space to bother deleting it for that reason. And if the same map happens to get re-added then it will use the same Id number instead of incrementing to a new one, which is especially handy if the related records/rs_times entries haven't been deleted either. In a way the 'challenges' entries help admins keep track of what maps have already passed by on the server. Pruning them is therefore best done again via a separate script, not a catch-all built-in admin command.
Developer of XASECO for TMF/TMN ESWC & XASECO2 for TM²: see XAseco.org
Find your way around the Mania community from the TMN ESWC hub, TMF hub, TM² hub, and SM hub

User avatar
Mistral
Pit Crew
Pit Crew
Posts: 1304
Joined: 02 May 2006 15:13
Owned TM-games: tmuf
Contact:

Re: [XAseco2] Database Purge Plugin

Post by Mistral » 18 Oct 2011 18:53

I always disliked that "old" records from maps that dont exist anymore (but in the databasae) are calculated in the average rank ... no chance for new players to advance, because the maps are gone and only a few players might have finished the map with a high rank.

So its not because of the size of the database (esp. not for the maps), but for an accurate average. Easiest was to delete removed maps from db and then to purge the records.

Didnt know it was built in rasp after i forked. I see its only done at startup with the prune settings. Yeah it takes a few ... thats why i built a command so the admin can initiate.

Thanks for reminding me to the rs_times table ... i forgot about it. I never used it in my old script and therefor disabled it.

Thanks to Xymph i updated above code.

Xymph
Pit Crew
Pit Crew
Posts: 5654
Joined: 19 Aug 2007 12:58
Owned TM-games: TMN, TMU, TMF, TM²
Contact:

Re: [XAseco2] Database Purge Plugin

Post by Xymph » 18 Oct 2011 19:26

Mistral wrote:I always disliked that "old" records from maps that dont exist anymore (but in the databasae) are calculated in the average rank ... no chance for new players to advance, because the maps are gone and only a few players might have finished the map with a high rank.

So its not because of the size of the database (esp. not for the maps), but for an accurate average. Easiest was to delete removed maps from db and then to purge the records.
I don't know whether/when that ever was a problem, because even in the original RASP 1.5 that was my starting point for XAseco, the resetRanks code inplugin.rasp.php only takes database entries into account for maps that are currently on the dedicated server. So records driven on deleted tracks do not contribute to a player's rank, which would indeed be a bad thing.
Mistral wrote:Didnt know it was built in rasp after i forked. I see its only done at startup with the prune settings. Yeah it takes a few ... thats why i built a command so the admin can initiate.
I understand, then the admin can choose a quiet time. But I still think this kind of database crunching doesn't belong in a controller.

Nevertheless, your plugin may be useful to admins who choose to do it that way, so thanks for sharing.
Developer of XASECO for TMF/TMN ESWC & XASECO2 for TM²: see XAseco.org
Find your way around the Mania community from the TMN ESWC hub, TMF hub, TM² hub, and SM hub

Post Reply