diff options
author | jaBote <jaBote@users.noreply.github.com> | 2014-03-21 21:39:26 +0100 |
---|---|---|
committer | jaBote <jaBote@users.noreply.github.com> | 2014-03-21 21:39:26 +0100 |
commit | 26f2adab3126c5b76a93c3220ccfb4aec7db07e1 (patch) | |
tree | fc87a24991fc882858c77d7966e865e288dc9178 /sql-files | |
parent | 6564295157aee6f0d4e7288b77f68dec253c977f (diff) | |
download | hercules-26f2adab3126c5b76a93c3220ccfb4aec7db07e1.tar.gz hercules-26f2adab3126c5b76a93c3220ccfb4aec7db07e1.tar.bz2 hercules-26f2adab3126c5b76a93c3220ccfb4aec7db07e1.tar.xz hercules-26f2adab3126c5b76a93c3220ccfb4aec7db07e1.zip |
Update rAthena-main-upgrade.sql
Added in the differences from some months ago. Very special thanks to @dastgirpojee for the autotrade saving script!
None of us could test with actual data, so feel free to try it and point out any flaws you can see.
Diffstat (limited to 'sql-files')
-rw-r--r-- | sql-files/upgrades/rAthena-main-upgrade.sql | 258 |
1 files changed, 231 insertions, 27 deletions
diff --git a/sql-files/upgrades/rAthena-main-upgrade.sql b/sql-files/upgrades/rAthena-main-upgrade.sql index 19f6d55ec..165584795 100644 --- a/sql-files/upgrades/rAthena-main-upgrade.sql +++ b/sql-files/upgrades/rAthena-main-upgrade.sql @@ -4,36 +4,55 @@ -- Remember to make a backup before applying. -- We are not liable for any data loss this may cause. -- Apply in the same database you applied your main.sql +-- Last revised: March 21, 2014 20:30 -- Drop table contents from ´sc_data´ since we use a different status order than rAthena -- /!\ WARNING /!\ This will remove _ALL_ of the status effects active on the server +-- You can disable this, but this is a SECURITY MEASURE -- This will remove even jailed status from users! TRUNCATE TABLE `sc_data`; - -- Drop table `skillcooldown` since it's not used in Hercules DROP TABLE IF EXISTS `skillcooldown`; + -- Upgrades for table `cart_inventory` ALTER TABLE `cart_inventory` MODIFY `bound` tinyint(1) unsigned NOT NULL default '0'; + -- Upgrades for table `char` ALTER TABLE `char` CHANGE `moves` `slotchange` SMALLINT(3) UNSIGNED NOT NULL default '0', - ADD `char_opt` INT( 11 ) UNSIGNED NOT NULL default '0', - ADD `font` TINYINT( 3 ) UNSIGNED NOT NULL DEFAULT '0'; - ADD `unban_time` int(11) unsigned NOT NULL default '0'; + ADD `char_opt` INT( 11 ) UNSIGNED NOT NULL default '0' AFTER `slotchange`, + ADD `font` TINYINT( 3 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `char_opt`; + -- Upgrades for table `guild_storage` ALTER TABLE `guild_storage` MODIFY `bound` tinyint(1) unsigned NOT NULL default '0'; + -- Upgrades for table `inventory` ALTER TABLE `inventory` MODIFY `bound` tinyint(1) unsigned NOT NULL default '0'; --- Bank vault is saved later since we need to make a table rAthena doesn't have first + +-- Login table will be upgraded at a later point on this file +-- so that we can save the bank vault. + + +-- Upgrades for table `mapreg` +ALTER TABLE `mapreg` MODIFY `varname` varchar(32) BINARY NOT NULL, + DROP KEY `varname`, + DROP KEY `index`, + ADD PRIMARY KEY (`varname`,`index`); + + +-- Upgrades for table `sc_data` +ALTER TABLE `sc_data` ADD PRIMARY KEY (`account_id`,`char_id`,`type`); + -- -- Table structure for table `sql_updates` -- + CREATE TABLE IF NOT EXISTS `sql_updates` ( `timestamp` int(11) unsigned NOT NULL, `ignored` enum('Yes','No') NOT NULL DEFAULT 'No', @@ -41,27 +60,35 @@ CREATE TABLE IF NOT EXISTS `sql_updates` ( ) ENGINE=MyISAM; -- Existent updates to enter -INSERT INTO `sql_updates` (`timestamp`) VALUES (1360858500); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1360951560); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1362445531); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1362528000); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1362794218); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1364409316); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1366075474); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1366078541); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1381354728); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1381423003); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1382892428); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1383162785); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1383167577); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1383205740); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1383955424); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1384545461); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1384588175); +INSERT INTO `sql_updates` (`timestamp`) VALUES (1360858500); -- 2013-02-14--16-15.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1360951560); -- 2013-02-15--18-06.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1362445531); -- 2013-03-05--01-05.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1362528000); -- 2013-03-06--00-00.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1362794218); -- 2013-03-09--01-56.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1364409316); -- 2013-03-27--18-35.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1366075474); -- 2013-04-16--01-24.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1366078541); -- 2013-04-16--02-15.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1381354728); -- 2013-10-09--21-38.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1381423003); -- 2013-10-10--16-36.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1382892428); -- 2013-10-27--16-47.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1383162785); -- 2013-10-30--19-53.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1383167577); -- 2013-10-30--21-12.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1383205740); -- 2013-10-31--07-49.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1383955424); -- 2013-11-09--00-03.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1384473995); -- 2013-11-15--00-06.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1384545461); -- 2013-11-15--19-57.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1384588175); -- 2013-11-16--07-49.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1384763034); -- 2013-11-18--08-23.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1387844126); -- 2013-12-24--00-15.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1388854043); -- 2014-01-04--16-47.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1389028967); -- 2014-01-06--17-22.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1392832626); -- 2014-02-19--17-57.sql + -- Updates to table `storage` ALTER TABLE `storage` MODIFY `bound` tinyint(1) unsigned NOT NULL default '0'; + -- -- Table structure for table `account_data` -- @@ -72,16 +99,193 @@ CREATE TABLE IF NOT EXISTS `account_data` ( `base_exp` TINYINT( 4 ) UNSIGNED NOT NULL default '100', `base_drop` TINYINT( 4 ) UNSIGNED NOT NULL default '100', `base_death` TINYINT( 4 ) UNSIGNED NOT NULL default '100', - PRIMARY KEY (`account_id`) -) ENGINE=MyISAM; + PRIMARY KEY (`account_id`) +) ENGINE=MyISAM; -- Saving bank_vault data from rAthena's login table -- to our account_data table. There may be some not working cases. - INSERT INTO `account_data` (`account_id`, `bank_vault`) SELECT `account_id`, `bank_vault` FROM `login` WHERE `bank_vault` > 0 ; --- Dropping bank_vault column from login table -ALTER TABLE `login` DROP COLUMN `bank_vault`; + +-- Upgrades for table `login` +ALTER TABLE `login` DROP COLUMN `vip_time`, + DROP COLUMN `old_group`, + DROP COLUMN `bank_vault`; + -- Drop table `bonus_script` since it's not used in Hercules DROP TABLE IF EXISTS `bonus_script`; + + +-- +-- Table structure for table `npc_market_data` +-- + +CREATE TABLE IF NOT EXISTS `npc_market_data` ( + `name` varchar(24) NOT NULL default '', + `itemid` int(11) unsigned NOT NULL default '0', + `amount` int(11) unsigned NOT NULL default '0', + PRIMARY KEY (`name`,`itemid`) +) ENGINE=MyISAM; + + +-- Autotrade saving. Very special thanks to Dastgir Pojee! +-- +-- Vending Database Update +-- + +-- Vending_Items Update +ALTER TABLE `vending_items` + ADD `char_id` INT(11) NOT NULL DEFAULT '0' AFTER `index`; + +UPDATE `vending_items` v1, `vendings` v2 + SET v1.char_id = v2.char_id + WHERE v1.vending_id = v2.id; + +ALTER TABLE `vending_items` + DROP `vending_id`, + DROP `index`, + CHANGE `cartinventory_id` `itemkey` INT(11) NOT NULL DEFAULT '0', + MODIFY `amount` INT(11) NOT NULL DEFAULT '0', + MODIFY `price` INT(11) NOT NULL DEFAULT '0'; + +ALTER TABLE `vending_items` + ADD PRIMARY KEY( `char_id`, `itemkey`); + +RENAME TABLE `vending_items` TO `autotrade_data`; + +-- Vending Data Update +ALTER TABLE `vendings` + DROP `id`, + DROP `map`, + DROP `x`, + DROP `y`, + DROP `autotrade`; + +ALTER TABLE `vendings` + CHANGE `sex` `sex_ref` ENUM('F','M') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'M'; + +ALTER TABLE `vendings` + MODIFY `account_id` INT(11) NOT NULL DEFAULT '0', + MODIFY `char_id` INT(11) NOT NULL DEFAULT '0', + ADD `sex` TINYINT(2) NOT NULL DEFAULT '0' AFTER `char_id`, + MODIFY `title` VARCHAR(80) NOT NULL DEFAULT 'Buy From Me!'; + +UPDATE `vendings` + SET `sex` = 0 + WHERE `sex_ref` = 'F'; + +UPDATE `vendings` + SET `sex` = 1 + WHERE `sex_ref` = 'M'; + +ALTER TABLE `vendings` DROP `sex_ref`; + +ALTER TABLE `vendings` ADD PRIMARY KEY( `account_id`, `char_id`); + +RENAME TABLE `vendings` TO `autotrade_merchants`; + +-- Autotrade saving ended + + +-- We don't support saving buyingstores yet... +-- Comment next statement if you want to preserve them anyways +DROP TABLE IF EXISTS `buyingstores`, `buyingstore_items`; + + +-- Saving contents of `global_reg_value` + +-- +-- Table structure for table `acc_reg_num_db` +-- + +CREATE TABLE IF NOT EXISTS `acc_reg_num_db` ( + `account_id` int(11) unsigned NOT NULL default '0', + `key` varchar(32) BINARY NOT NULL default '', + `index` int(11) unsigned NOT NULL default '0', + `value` int(11) NOT NULL default '0', + PRIMARY KEY (`account_id`,`key`,`index`), + KEY `account_id` (`account_id`) +) ENGINE=MyISAM; + + +-- +-- Table structure for table `acc_reg_str_db` +-- + +CREATE TABLE IF NOT EXISTS `acc_reg_str_db` ( + `account_id` int(11) unsigned NOT NULL default '0', + `key` varchar(32) BINARY NOT NULL default '', + `index` int(11) unsigned NOT NULL default '0', + `value` varchar(254) NOT NULL default '0', + PRIMARY KEY (`account_id`,`key`,`index`), + KEY `account_id` (`account_id`) +) ENGINE=MyISAM; + + +-- +-- Table structure for table `char_reg_num_db` +-- + +CREATE TABLE IF NOT EXISTS `char_reg_num_db` ( + `char_id` int(11) unsigned NOT NULL default '0', + `key` varchar(32) BINARY NOT NULL default '', + `index` int(11) unsigned NOT NULL default '0', + `value` int(11) NOT NULL default '0', + PRIMARY KEY (`char_id`,`key`,`index`), + KEY `char_id` (`char_id`) +) ENGINE=MyISAM; + + +-- +-- Table structure for table `char_reg_str_db` +-- + +CREATE TABLE IF NOT EXISTS `char_reg_str_db` ( + `char_id` int(11) unsigned NOT NULL default '0', + `key` varchar(32) BINARY NOT NULL default '', + `index` int(11) unsigned NOT NULL default '0', + `value` varchar(254) NOT NULL default '0', + PRIMARY KEY (`char_id`,`key`,`index`), + KEY `char_id` (`char_id`) +) ENGINE=MyISAM; + + +-- +-- Table structure for table `global_acc_reg_num_db` +-- + +CREATE TABLE IF NOT EXISTS `global_acc_reg_num_db` ( + `account_id` int(11) unsigned NOT NULL default '0', + `key` varchar(32) BINARY NOT NULL default '', + `index` int(11) unsigned NOT NULL default '0', + `value` int(11) NOT NULL default '0', + PRIMARY KEY (`account_id`,`key`,`index`), + KEY `account_id` (`account_id`) +) ENGINE=MyISAM; + + +-- +-- Table structure for table `global_acc_reg_str_db` +-- + +CREATE TABLE IF NOT EXISTS `global_acc_reg_str_db` ( + `account_id` int(11) unsigned NOT NULL default '0', + `key` varchar(32) BINARY NOT NULL default '', + `index` int(11) unsigned NOT NULL default '0', + `value` varchar(254) NOT NULL default '0', + PRIMARY KEY (`account_id`,`key`,`index`), + KEY `account_id` (`account_id`) +) ENGINE=MyISAM; + + +-- Saving the data +INSERT INTO `acc_reg_num_db` (`account_id`, `key`, `index`, `value`) SELECT `account_id`, `str`, 0, `value` FROM `global_reg_value` WHERE `type` = 2 AND `str` NOT LIKE '%$'; +INSERT INTO `acc_reg_str_db` (`account_id`, `key`, `index`, `value`) SELECT `account_id`, `str`, 0, `value` FROM `global_reg_value` WHERE `type` = 2 AND `str` LIKE '%$'; +INSERT INTO `char_reg_num_db` (`char_id`, `key`, `index`, `value`) SELECT `char_id`, `str`, 0, `value` FROM `global_reg_value` WHERE `type` = 3 AND `str` NOT LIKE '%$'; +INSERT INTO `char_reg_str_db` (`char_id`, `key`, `index`, `value`) SELECT `char_id`, `str`, 0, `value` FROM `global_reg_value` WHERE `type` = 3 AND `str` LIKE '%$'; +INSERT INTO `global_acc_reg_num_db` (`account_id`, `key`, `index`, `value`) SELECT `account_id`, `str`, 0, `value` FROM `global_reg_value` WHERE `type` = 1 AND `str` NOT LIKE '%$'; +INSERT INTO `global_acc_reg_str_db` (`account_id`, `key`, `index`, `value`) SELECT `account_id`, `str`, 0, `value` FROM `global_reg_value` WHERE `type` = 1 AND `str` LIKE '%$'; + +-- Dropping now useless table +DROP TABLE `global_reg_value`; |