Updated rA > Herc conversion tool

[Tranquility 12]

Greetings (future) Herc users, in the past I've asked a lot about migrating from rA to Herc. One of the issues I came across was the non-updated SQL conversion tool.

Below in the spoiler is my attempt to update the current available tool, which is to be carried out after a complete rA update.

Since I doubt my adjustments I post it here, wondering if a few of the interested people could try this out on their test servers, hoping they'll yield the same results as I do.

 

Do note, the last two queries are to convert your item databases and are thus optional. Remove them from your query if you're already going straight to the conf-file setup.

 

-- rAthena to Hercules main database upgrade query.-- This upgrades a FULLY UPGRADED rAthena to a FULLY UPGRADED Hercules-- Please don't use if either rAthena or Hercules launched a SQL update after last revision date of this file.-- 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: October 13, 2015 05:00 CET-- 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 HerculesDROP TABLE IF EXISTS `skillcooldown`;-- Upgrades for table `auction`ALTER TABLE `auction` MODIFY `nameid` INT(11) NOT NULL DEFAULT '0',    MODIFY `card0` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card1` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card2` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card3` SMALLINT(11) NOT NULL DEFAULT '0';-- Upgrades for table `cart_inventory`ALTER TABLE `cart_inventory` MODIFY `nameid` INT(11) NOT NULL DEFAULT '0',    MODIFY `card0` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card1` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card2` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card3` SMALLINT(11) NOT NULL DEFAULT '0',    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' AFTER `slotchange`,    MODIFY `font` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER `char_opt`;-- Upgrades for table `charlog`ALTER TABLE `charlog` ADD COLUMN `char_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `account_id`;-- Upgrades for table `guild_storage`ALTER TABLE `guild_storage` MODIFY `nameid` INT(11) NOT NULL DEFAULT '0',    MODIFY `card0` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card1` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card2` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card3` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `bound` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0';-- Upgrades for table `inventory`ALTER TABLE `inventory` MODIFY `nameid` INT(11) NOT NULL DEFAULT '0',    MODIFY `card0` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card1` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card2` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card3` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `bound` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0';-- Login table will be upgraded at a later point on this file-- so that we can save the bank vault.-- Upgrades for table `mail`ALTER TABLE `mail` MODIFY `nameid` INT(11) NOT NULL DEFAULT '0',    MODIFY `card0` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card1` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card2` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card3` SMALLINT(11) NOT NULL DEFAULT '0',    DROP COLUMN `bound`;-- Upgrades for table `pet`ALTER TABLE `pet` MODIFY `egg_id` SMALLINT(11) UNSIGNED NOT NULL DEFAULT '0';-- 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',  PRIMARY KEY (`timestamp`)) ENGINE=MyISAM;-- Existent updates to enterINSERT INTO `sql_updates` (`timestamp`) VALUES (1360858500); -- 2013-02-14--16-15.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1360951560); -- 2013-02-15--18-06.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1362445531); -- 2013-03-05--01-05.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1362528000); -- 2013-03-06--00-00.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1362794218); -- 2013-03-09--01-56.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1364409316); -- 2013-03-27--18-35.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1366075474); -- 2013-04-16--01-24.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1366078541); -- 2013-04-16--02-15.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1381354728); -- 2013-10-09--21-38.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1381423003); -- 2013-10-10--16-36.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1382892428); -- 2013-10-27--16-47.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1383162785); -- 2013-10-30--19-53.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1383167577); -- 2013-10-30--21-12.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1383205740); -- 2013-10-31--07-49.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1383955424); -- 2013-11-09--00-03.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1384473995); -- 2013-11-15--00-06.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1384545461); -- 2013-11-15--19-57.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1384588175); -- 2013-11-16--07-49.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1384763034); -- 2013-11-18--08-23.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1387844126); -- 2013-12-24--00-15.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1388854043); -- 2014-01-04--16-47.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1389028967); -- 2014-01-06--17-22.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1392832626); -- 2014-02-19--17-57.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1395789302); -- 2014-03-25--23-57.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1396893866); -- 2014-04-07--22-04.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1398477600); -- 2014-04-26--10-00.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1400256139); -- 2014-05-17--00-06.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1409590380); -- 2014-09-01--16-53.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1414975503); -- 2014-11-03--00-45.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1435860840); -- 2014-07-02--18-14.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1436360978); -- 2015-07-08--13-08.sqlINSERT INTO `sql_updates` (`timestamp`) VALUES (1440688342); -- 2015-08-27--13-08.sql---- Updates to table `storage`ALTER TABLE `storage` MODIFY `nameid` INT(11) NOT NULL DEFAULT '0',    MODIFY `card0` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card1` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card2` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `card3` SMALLINT(11) NOT NULL DEFAULT '0',    MODIFY `bound` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0';---- Table structure for table `account_data`--CREATE TABLE IF NOT EXISTS `account_data` (  `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',  `bank_vault` INT(11) UNSIGNED NOT NULL DEFAULT '0',  `base_exp` SMALLINT(6) UNSIGNED NOT NULL DEFAULT  '100',  `base_drop` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '100',  `base_death` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '100',  PRIMARY KEY (`account_id`)) ENGINE=MyISAM;-- Upgrades for table `login`ALTER TABLE `login` DROP COLUMN `vip_time`,    DROP COLUMN `old_group`;-- Drop table `bonus_script` since it's not used in HerculesDROP 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;---- Customised script for transfering data from rA's market to npc_market_data--DROP TABLE IF EXISTS `npc_market_data`;ALTER TABLE `market`  DROP `flag`,  DROP `price`,  MODIFY `name` varchar(24) NOT NULL DEFAULT '',  CHANGE `nameid` `itemid` INT(11) UNSIGNED NOT NULL DEFAULT '0',  MODIFY `amount` INT(11) UNSIGNED NOT NULL DEFAULT '0';RENAME TABLE `market` TO `npc_market_data`;---- Renaming rA tables to fit with the Hercules format--RENAME TABlE `acc_reg_num` to `acc_reg_num_db`;RENAME TABlE `acc_reg_str` to `acc_reg_str_db`;RENAME TABlE `char_reg_num` to `char_reg_num_db`;RENAME TABlE `char_reg_str` to `char_reg_str_db`;RENAME TABlE `global_acc_reg_num` to `global_acc_reg_num_db`;RENAME TABlE `global_acc_reg_str` to `global_acc_reg_str_db`;-- Autotrade saving. Very special thanks to Dastgir Pojee!---- Vending Database Update---- Vending_Items UpdateALTER 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 UpdateALTER 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 anywaysDROP TABLE IF EXISTS `buyingstores`, `buyingstore_items`;-- Dropping now useless tablesDROP TABLE `global_reg_value`;DROP TABLE IF EXISTS interreg;DROP TABLE IF EXISTS sstatus;DROP TABLE IF EXISTS db_roulette;---- !! OPTIONAL !!-- Updating the item databases to match with rA's weapon/armor type differences.-- Hercules will soon no longer support SQL item databases-- ? no conversion tool available to conf yet ?--UPDATE item_db SET type =  CASE  WHEN type = 4 THEN 5  WHEN type = 5 THEN 4  ELSE typeEND    UPDATE item_db2 SET type =  CASE  WHEN type = 4 THEN 5  WHEN type = 5 THEN 4  ELSE typeEND

 

 

 

Kindly leave your feedback in the comments below. If I'm missing a ton of things, I'll revise the entire script soon™ by manually comparing both database tables...

EDIT: Made some small modifications after recomparing the databases, thus updated the above script.

 

Thanks in advance,

 

Tranquility

Edited October 13, 2015 by Tranquility

[Aeromesi 180]

I'll gladly test this one out thanks, though I don't use rA I still use it as a platform to develop npc scripts

[Tranquility 12]

Thanks!

The feedback will be appreciated. As soon it works (flawlessly) for everyone, I or someone else can make a pull request to improve the current conversion tool at the git repository. Which was my original intention to use this post for.

[Tranquility 12]

Tried and used on my live server, conversion completed without any errors. However there could still be some duplicates flaws in the script that I just fail to see.

Other than that, it works and can be used for rA > Herc conversion.

 

Made a pull request for it yesterday too, so if the core devs could take a look at it, it'll be able to be used by the masses.