[PHP] Bans database converter

[elf]

<?php
mysql_connect("localhost", "root", "");
mysql_select_db("theforgottenserver");

@mysql_query("DROP TABLE `bans2`;");
@mysql_query("DROP TABLE `bans_backup`;");
if(mysql_error())
	echo "1. Database cleanup error: ".mysql_error()."<br />
(If error(s) is/are about missing tables, ignore it.)<br />";
else
	echo "1. Cleaned up database.<br />";

@mysql_query("CREATE TABLE `bans_backup` (
`type` int( 11 ) NOT NULL COMMENT 'this field defines if its ip, accountban or namelock',
`ip` int( 10 ) unsigned NOT NULL default '0',
`mask` int( 10 ) unsigned NOT NULL default '4294967295',
`player` int( 10 ) unsigned NOT NULL default '0',
`account` int( 10 ) unsigned NOT NULL default '0',
`time` int( 10 ) unsigned NOT NULL default '0',
`reason_id` int( 11 ) NOT NULL default '0',
`action_id` int( 11 ) NOT NULL default '0',
`comment` text NOT NULL ,
`banned_by` int( 10 ) unsigned NOT NULL default '0',
`expired` tinyint( 1 ) unsigned NOT NULL default '0'
) ENGINE = InnoDB");
@mysql_query("INSERT INTO `bans_backup` 
SELECT * 
FROM `bans`;");
if(mysql_error())
{
	echo "2. Creating table backup error: ".mysql_error()."<br />";
	die();
}
else
	echo "2. Table backup created.<br />";

@mysql_query("	CREATE TABLE `bans2`
	(
		`id` INT UNSIGNED NOT NULL auto_increment,
		`type` TINYINT(1) NOT NULL COMMENT 'this field defines if its ip, account, player, or any else ban',
		`value` INT UNSIGNED NOT NULL COMMENT 'ip, player guid, account number',
		`param` INT UNSIGNED NOT NULL DEFAULT 4294967295 COMMENT 'mask',
		`active` TINYINT(1) NOT NULL DEFAULT TRUE,
		`expires` INT UNSIGNED NOT NULL,
		`added` INT UNSIGNED NOT NULL,
		`admin_id` INT UNSIGNED NOT NULL DEFAULT 0,
		`comment` TEXT NOT NULL DEFAULT '',
		`reason` INT UNSIGNED NOT NULL DEFAULT 0,
		`action` INT UNSIGNED NOT NULL DEFAULT 0,
		PRIMARY KEY  (`id`),
		KEY `type` (`type`, `value`)
	) ENGINE = InnoDB;");
if(mysql_error())
{
	echo "3. Table creation error: ".mysql_error()."<br />";
	die();
}
else
	echo "3. Table created.<br />";

$query = mysql_query("SELECT * FROM `bans`");
while($data = mysql_fetch_assoc($query))
{
	switch($data['type'])
	{
		case 1:
			@mysql_query("INSERT INTO `bans2`
				(`type`, `value`, `param`, `active`, `expires`, `added`, `admin_id`, `comment`, `reason`, `action`)
				VALUES
				(1, ".$data['ip'].", ".$data['mask'].", ".($data['expired'] ? 0 : 1).", ".$data['time'].", 0, ".$data['banned_by'].", '".$data['comment']."', ".$data['reason_id'].", ".$data['action_id'].")
			;");
			break;
		case 2:
			@mysql_query("INSERT INTO `bans2`
				(`type`, `value`, `active`, `expires`, `added`, `admin_id`, `comment`, `reason`, `action`)
				VALUES
				(2, ".$data['player'].", ".($data['expired'] ? 0 : 1).", 0, ".$data['time'].", ".$data['banned_by'].", '".$data['comment']."', ".$data['reason_id'].", ".$data['action_id'].")
			;");
			break;
		case 3:
			@mysql_query("INSERT INTO `bans2`
				(`type`, `value`, `active`, `expires`, `added`, `admin_id`, `comment`, `reason`, `action`)
				VALUES
				(3, ".$data['player'].", ".($data['expired'] ? 0 : 1).", ".$data['time'].", 0, ".$data['banned_by'].", '".$data['comment']."', ".$data['reason_id'].", ".$data['action_id'].")
			;");
			break;
		case 4:
		case 5:
			@mysql_query("INSERT INTO `bans2`
				(`type`, `value`, `active`, `expires`, `added`, `admin_id`, `comment`, `reason`, `action`)
				VALUES
				(".$data['type'].", ".$data['player'].", ".($data['expired'] ? 0 : 1).", 0, ".$data['time'].", ".$data['banned_by'].", '".$data['comment']."', ".$data['reason_id'].", ".$data['action_id'].")
			;");
			break;
		default:
			break;
	}
}
if(mysql_error())
{
	echo "4. Bans copying error: ".mysql_error()."<br />";
	die();
}
else
	echo "4. Bans copied.<br />";

@mysql_query("DROP TABLE `bans`;");
@mysql_query("RENAME TABLE `bans2` TO `bans`;");
if(mysql_error())
{
	echo "5. Table renaming error: ".mysql_error()."<br />";
	die();
}
else
	echo "5. Table renamed.<br />";

echo "Please execute now these lines manually for trigger re-creation (its a need, for proper work):<br />
<textarea style=\"width: 400px; height: 400px;\">DROP TRIGGER IF EXISTS `ondelete_accounts`;
DROP TRIGGER IF EXISTS `ondelete_players`;

DELIMITER |
CREATE TRIGGER `ondelete_accounts`
BEFORE DELETE
ON `accounts`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` != 1 AND `type` != 2 AND `value` = OLD.`id`;
END|

CREATE TRIGGER `ondelete_players`
BEFORE DELETE
ON `players`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 2 AND `value` = OLD.`id`;
    UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
END|
DELIMITER ;</textarea>";

mysql_close();
?>

Description: It converts database structure from 0.2 to latest trunk (0.3 pre-alpha) one (since rev852, when structure was introduced)
Have fun

 

[Ronaldino]

Thanks ;dd

 

[Master-m]

I removed your password if it was correct

 

[elf]

It was correct to my localhost

 

[KaczooH]

Nice! I like it. Useful. If it isn't still, it should be added to SVN ;>

 

[elf]

*Refreshing*

This is very usefull tool for everyone migrating from 0.2!