- Joined
- Aug 12, 2008
- Messages
- 1,351
- Reaction score
- 36
Downgrading database from 0.3.2 to 0.2 TEST VERSION
So Let's start.
1. Make backup of your exisitng database!:
For safety of course. To restore database use:
mysql -u<user> -p<password> <database> < backup.sql
2. Create file x.sql. Paste this code there:
3.Now run:
4.If your players were using account names you can provide them website interfrace to retrieve their account id.
Create subpage with following code:
If there will be any errors - paste in this topic or via PM.
So Let's start.
1. Make backup of your exisitng database!:
Code:
mysqldump -u<user> -p<password> <databasename> > backup.sql
mysql -u<user> -p<password> <database> < backup.sql
2. Create file x.sql. Paste this code there:
Code:
DROP TRIGGER IF EXISTS `oncreate_players`;
DROP TRIGGER IF EXISTS `oncreate_guilds`;
DROP TRIGGER IF EXISTS `ondelete_players`;
DROP TRIGGER IF EXISTS `ondelete_guilds`;
DROP TRIGGER IF EXISTS `ondelete_accounts`;
ALTER TABLE `groups` DROP `customflags`;
ALTER TABLE `groups` DROP `violationaccess`;
ALTER TABLE `players` DROP `balance`;
ALTER TABLE `players` DROP `stamina`;
ALTER TABLE `accounts` ADD `type` INT NOT NULL DEFAULT 1;
DROP TABLE `player_deaths`;
CREATE TABLE `player_deaths`
(
`player_id` INT NOT NULL,
`time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`level` INT NOT NULL DEFAULT 1,
`killed_by` VARCHAR(255) NOT NULL,
`altkilled_by` VARCHAR(255) NOT NULL,
`is_player` INT NOT NULL,
KEY (`player_id`),
FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;
DROP TABLE IF EXISTS `bans`;
CREATE TABLE `bans`
(
`type` INT NOT NULL COMMENT 'this field defines if its ip, accountban or namelock',
`ip` INT UNSIGNED NOT NULL DEFAULT 0,
`mask` INT UNSIGNED NOT NULL DEFAULT 4294967295,
`player` INT UNSIGNED NOT NULL DEFAULT 0,
`account` INT UNSIGNED NOT NULL DEFAULT 0,
`time` INT UNSIGNED NOT NULL DEFAULT 0,
`reason_id` INT NOT NULL DEFAULT 0,
`action_id` INT NOT NULL DEFAULT 0,
`comment` VARCHAR(60) NOT NULL DEFAULT '',
`banned_by` INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE = InnoDB;
ALTER TABLE `tiles` DROP KEY (`id`);
ALTER TABLE `tiles` DROP COLUMN `world_id`;
ALTER TABLE `tile_items` DROP KEY `tile_id`;
ALTER TABLE `tile_items` DROP KEY `sid`;
ALTER TABLE `tiles` ADD UNIQUE INDEX (`id`);
ALTER TABLE `tiles` ADD FOREIGN KEY (`tile_id`) REFERENCES `tiles`(`id`) ON DELETE CASCADE;
DELIMITER |
CREATE TRIGGER `ondelete_accounts`
BEFORE DELETE
ON `accounts`
FOR EACH ROW
BEGIN
DELETE FROM `bans` WHERE `account` = OLD.`id`;
END|
CREATE TRIGGER `ondelete_guilds`
BEFORE DELETE
ON `guilds`
FOR EACH ROW
BEGIN
UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
END|
CREATE TRIGGER `ondelete_players`
BEFORE DELETE
ON `players`
FOR EACH ROW
BEGIN
DELETE FROM `bans` WHERE `type` = 2 AND `player` = OLD.`id`;
UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
END|
CREATE TRIGGER `oncreate_guilds`
AFTER INSERT
ON `guilds`
FOR EACH ROW
BEGIN
INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('the Leader', 3, NEW.`id`);
INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Vice-Leader', 2, NEW.`id`);
INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Member', 1, NEW.`id`);
END|
CREATE TRIGGER `oncreate_players`
AFTER INSERT
ON `players`
FOR EACH ROW
BEGIN
INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
END|
DELIMITER ;
3.Now run:
Code:
mysql -u<user> -p<password> <database> < x.sql
4.If your players were using account names you can provide them website interfrace to retrieve their account id.
Create subpage with following code:
Code:
<?PHP
if($action == "")
{
$main_content .= 'To play on '.$config['server']['serverName'].' you need an account.
All you have to do to create your new account is to enter your email address, password to new account, verification code from picture and to agree to the terms presented below.
If you have done so, your account number, password and e-mail address will be shown on the following page and your account and password will be sent
to your email address along with further instructions.<BR><BR>
<FORM ACTION="index.php?subtopic=name&action=shownumber" onsubmit="return validate_form(this)" METHOD=post>
<TABLE WIDTH=100% BORDER=0 CELLSPACING=1 CELLPADDING=4>
<TR><TD BGCOLOR="'.$config['site']['vdarkborder'].'" CLASS=white><B>Create a '.$config['server']['serverName'].' Account</B></TD></TR>
<TR><TD BGCOLOR="'.$config['site']['darkborder'].'"><TABLE BORDER=0 CELLSPACING=8 CELLPADDING=0>
<TR><TD>
<TABLE BORDER=0 CELLSPACING=5 CELLPADDING=0>';
$main_content .= '<script type="text/javascript">var accountcustom=1;</script>
<TR><TD width="150" valign="top"><B>Account name: </B></TD><TD colspan="2"><INPUT id="account_number" NAME="reg_account"" VALUE="" SIZE=30 MAXLENGTH=50><BR><font size="1" face="verdana,arial,helvetica">(Wpisz account name)</font></TD></TR>
';
$main_content .= '<script type="text/javascript">var accountcustom=0;</script>';
$main_content .= '<TR><TD width="150" valign="top"><B>Password: </B></TD><TD colspan="2"><INPUT TYPE="password" id="passor" NAME="reg_password" VALUE="" SIZE=30 MAXLENGTH=50><BR><font size="1" face="verdana,arial,helvetica">(Here write your password to new account on '.$config['server']['serverName'].')</font></TD></TR>';
if($config['site']['verify_code'] == 'yes')
$main_content .= '<script type="text/javascript">var verifya=1;</script><TR><TD width="150"><B>Code: </B></TD><TD colspan="2"><img src="imgverification/imagebuilder.php" border="0" alt="Image Verification is missing, please contact the administrator"></TD></TR>
<TR><TD width="150" valign="top"><B>Verification Code: </B></TD><TD colspan="2"><INPUT id="verify" NAME="reg_code" VALUE="" SIZE=30 MAXLENGTH=50><BR><font size="1" face="verdana,arial,helvetica">(Here write verification code from picture)</font></TD></TR>';
else
$main_content .= '<script type="text/javascript">var verifya=0;</script>';
$main_content .= '</TABLE>
</TD></TR>
<TR><TD>
<TABLE BORDER=0 CELLSPACING=5 CELLPADDING=0><TR><TD>
Please review the following terms and state your agreement below.
</TD></TR>
<TR><TD>
<B>'.$config['server']['serverName'].' Rules</B><BR>
<h1>Za chwilę zostanie wyświetlony Twój Account Number. Kliknij <b>I Agree</b> aby kontynuowac</h1>
</TD></TR></TABLE>
</TD></TR>
<TR><TD>
<TABLE BORDER=0 CELLSPACING=5 CELLPADDING=0>
<TR><TD>
</TD></TR></TABLE>
</TD></TR>
</TABLE></TD></TR>
</TABLE>
<BR>
<TABLE BORDER=0 WIDTH=100%>
<TR><TD ALIGN=center>
<IMG SRC="'.$layout_name.'/images/general/blank.gif" WIDTH=120 HEIGHT=1 BORDER=0><BR>
</TD><TD ALIGN=center VALIGN=top>
<INPUT TYPE=image NAME="I Agree" SRC="'.$layout_name.'/images/buttons/sbutton_iagree.gif" BORDER=0 WIDTH=120 HEIGHT=18>
</FORM>
</TD><TD ALIGN=center>
<FORM ACTION="index.php?subtopic=latestnews" METHOD=post>
<INPUT TYPE=image NAME="Cancel" SRC="'.$layout_name.'/images/buttons/sbutton_cancel.gif" BORDER=0 WIDTH=120 HEIGHT=18>
</FORM>
</TD><TD ALIGN=center>
<IMG SRC="/images/general/blank.gif" WIDTH=120 HEIGHT=1 BORDER=0><BR>
</TD></TR>
</TABLE>
</TD>
<TD><IMG SRC="'.$layout_name.'/images/general/blank.gif" WIDTH=10 HEIGHT=1 BORDER=0></TD>
</TR>
</TABLE>';
}
if($action == "shownumber") {
$posted_reg_account = trim($_POST['reg_account']);
$reg_password = trim($_POST['reg_password']);
$reg_code = trim($_POST['reg_code']);
if($config['site']['verify_code'] == 'yes')
{
//check verification code
$string = strtoupper($_SESSION['string']);
$userstring = strtoupper($reg_code);
session_destroy();
if(empty($string))
$reg_form_errors[] = "Information about verification code in session is empty.";
else
{
if(empty($userstring))
$reg_form_errors[] = "Please enter verification code.";
else
{
if($string != $userstring)
$reg_form_errors[] = "Verification code is incorrect.";
}
}
}
//check password
if(empty($reg_password))
$reg_form_errors[] = "Please enter password to your account.";
if(empty($reg_form_errors))
{
//create object 'account' and generate new acc. number
$ac_id = $SQL->query('SELECT id FROM `accounts` WHERE `name`='.$SQL->quote($posted_reg_account).' AND`password` = '.$SQL->quote($reg_password).' LIMIT 1;');//$_REQUEST['reg_account']
while($data = $ac_id->fetch()) {
print_r($data);
if(!empty($data['id'])) {
$main_content .= '<BR><BR>';
$main_content .= '<TABLE WIDTH=100% BORDER=0 CELLSPACING=1 CELLPADDING=4>
<TR><TD BGCOLOR="'.$config['site']['vdarkborder'].'" CLASS=white><B>Account ID</B></TD></TR>
<TR><TD BGCOLOR="'.$config['site']['darkborder'].'">
<TABLE BORDER=0 CELLPADDING=1><TR><TD>
<BR><FONT SIZE=5>Twój account number to: <B>'.$data['id'].'</B></FONT><BR><BR>';
$main_content .= '</TD></TR></TABLE>
</TD></TR>
</TABLE><BR><BR>';
}
else {
$main_content .='<h1>Prawdopodobnie podałeś błędne dane</h1>';
}
}
// saves account information in database
//show information about registration
}
else
{
//SHOW ERRORs if data from form is wrong
$main_content .= '<div class="SmallBox" > <div class="MessageContainer" > <div class="BoxFrameHorizontal" style="background-image:url('.$layout_name.'/images/content/box-frame-horizontal.gif);" /></div> <div class="BoxFrameEdgeLeftTop" style="background-image:url('.$layout_name.'/images/content/box-frame-edge.gif);" /></div> <div class="BoxFrameEdgeRightTop" style="background-image:url('.$layout_name.'/images/content/box-frame-edge.gif);" /></div> <div class="ErrorMessage" > <div class="BoxFrameVerticalLeft" style="background-image:url('.$layout_name.'/images/content/box-frame-vertical.gif);" /></div> <div class="BoxFrameVerticalRight" style="background-image:url('.$layout_name.'/images/content/box-frame-vertical.gif);" /></div> <div class="AttentionSign" style="background-image:url('.$layout_name.'/images/content/attentionsign.gif);" /></div><b>The Following Errors Have Occurred:</b><br/>';
foreach($reg_form_errors as $show_msg)
{
$main_content .= '<li>'.$show_msg;
}
$main_content .= '</div> <div class="BoxFrameHorizontal" style="background-image:url('.$layout_name.'/images/content/box-frame-horizontal.gif);" /></div> <div class="BoxFrameEdgeRightBottom" style="background-image:url('.$layout_name.'/images/content/box-frame-edge.gif);" /></div> <div class="BoxFrameEdgeLeftBottom" style="background-image:url('.$layout_name.'/images/content/box-frame-edge.gif);" /></div> </div></div><br/>
<BR>
<CENTER>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0><FORM ACTION=index.php?subtopic=name METHOD=post><TR><TD>
<INPUT TYPE=hidden NAME=email VALUE="">
<INPUT TYPE=image NAME="Back" ALT="Back" SRC="'.$layout_name.'/images/buttons/sbutton_back.gif" BORDER=0 WIDTH=120 HEIGHT=18>
</TD></TR></FORM></TABLE>
</CENTER>';
}
}
?>
If there will be any errors - paste in this topic or via PM.