From 26f2adab3126c5b76a93c3220ccfb4aec7db07e1 Mon Sep 17 00:00:00 2001 From: jaBote <jaBote@users.noreply.github.com> Date: Fri, 21 Mar 2014 21:39:26 +0100 Subject: 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. --- sql-files/upgrades/rAthena-main-upgrade.sql | 258 +++++++++++++++++++++++++--- 1 file changed, 231 insertions(+), 27 deletions(-) (limited to 'sql-files') 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`; -- cgit v1.2.3-70-g09d2 From aa120720cec6ba1ddf6ec8ac6098fef6377dddc0 Mon Sep 17 00:00:00 2001 From: panikon <panikon@zoho.com> Date: Sat, 22 Mar 2014 00:07:10 -0300 Subject: Fixed issue 7338 (added new char configuration char_aegis_delete) http://hercules.ws/board/tracker/issue-7338-deleting-character/ As @jaBote requested in 26f2ada, renamed "rathena-logs-database-upgrade" to "rAthena-logs-upgrade.sql" and updated 'last revision'. Added a 'FIXME' in db_obj_put --- conf/char-server.conf | 5 +++ sql-files/upgrades/rAthena-logs-upgrade.sql | 13 +++++++ sql-files/upgrades/rAthena-main-upgrade.sql | 2 +- sql-files/upgrades/rathena-logs-database-upgrade | 13 ------- src/char/char.c | 47 ++++++++++++++++-------- src/common/db.c | 2 + 6 files changed, 53 insertions(+), 29 deletions(-) create mode 100644 sql-files/upgrades/rAthena-logs-upgrade.sql delete mode 100644 sql-files/upgrades/rathena-logs-database-upgrade (limited to 'sql-files') diff --git a/conf/char-server.conf b/conf/char-server.conf index b1172e654..7ebb7ed00 100644 --- a/conf/char-server.conf +++ b/conf/char-server.conf @@ -150,6 +150,11 @@ char_del_level: 0 // NOTE: Requires client 2010-08-03aragexeRE or newer. char_del_delay: 86400 +// Block deletion if character is inside a guild or a party? (BOOL) +// default: 0 official: 1 +// !!This check is imposed by Aegis to avoid dead entries in databases and _is_not_needed_ as we clear data properly!! +char_aegis_delete: 0 + // What folder the DB files are in (item_db.txt, etc.) db_path: db diff --git a/sql-files/upgrades/rAthena-logs-upgrade.sql b/sql-files/upgrades/rAthena-logs-upgrade.sql new file mode 100644 index 000000000..b523f8444 --- /dev/null +++ b/sql-files/upgrades/rAthena-logs-upgrade.sql @@ -0,0 +1,13 @@ +-- rAthena to Hercules log 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 logs.sql +-- Last revised: March 21, 2014 20:30 GMT + +-- Upgrades to table `picklog` +ALTER TABLE `picklog` MODIFY `type` enum('M','P','L','T','V','S','N','C','A','R','G','E','B','O','I','X','D','U') NOT NULL default 'P'; + +-- Drop table `cashlog` since it's not used in Hercules +DROP TABLE IF EXISTS `cashlog`; diff --git a/sql-files/upgrades/rAthena-main-upgrade.sql b/sql-files/upgrades/rAthena-main-upgrade.sql index 165584795..9982322e5 100644 --- a/sql-files/upgrades/rAthena-main-upgrade.sql +++ b/sql-files/upgrades/rAthena-main-upgrade.sql @@ -4,7 +4,7 @@ -- 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 +-- Last revised: March 21, 2014 20:30 GMT -- 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 diff --git a/sql-files/upgrades/rathena-logs-database-upgrade b/sql-files/upgrades/rathena-logs-database-upgrade deleted file mode 100644 index 7cdf58992..000000000 --- a/sql-files/upgrades/rathena-logs-database-upgrade +++ /dev/null @@ -1,13 +0,0 @@ --- rAthena to Hercules log 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 logs.sql --- Last revision: November 10, 2013, 19:00 - --- Upgrades to table `picklog` -ALTER TABLE `picklog` MODIFY `type` enum('M','P','L','T','V','S','N','C','A','R','G','E','B','O','I','X','D','U') NOT NULL default 'P'; - --- Drop table `cashlog` since it's not used in Hercules -DROP TABLE IF EXISTS `cashlog`; diff --git a/src/char/char.c b/src/char/char.c index bf19a0012..0769067fd 100644 --- a/src/char/char.c +++ b/src/char/char.c @@ -125,6 +125,8 @@ int char_del_delay = 86400; int log_char = 1; // loggin char or not [devil] int log_inter = 1; // loggin inter or not [devil] +int char_aegis_delete = 0; // Verify if char is in guild/party or char and reacts as Aegis does (doesn't allow deletion), see char_delete2_req for more information + // Advanced subnet check [LuzZza] struct s_subnet { uint32 mask; @@ -3854,6 +3856,7 @@ int lan_subnetcheck(uint32 ip) } +/// Answers to deletion request (HC_DELETE_CHAR3_RESERVED) /// @param result /// 0 (0x718): An unknown error has occurred. /// 1: none/success @@ -3919,7 +3922,7 @@ void char_delete2_cancel_ack(int fd, int char_id, uint32 result) static void char_delete2_req(int fd, struct char_session_data* sd) {// CH: <0827>.W <char id>.L - int char_id, i; + int char_id, party_id, guild_id, i; char* data; time_t delete_date; @@ -3946,22 +3949,34 @@ static void char_delete2_req(int fd, struct char_session_data* sd) return; } -/* - // Aegis imposes these checks probably to avoid dead member - // entries in guilds/parties, otherwise they are not required. - // TODO: Figure out how these are enforced during waiting. - if( guild_id ) - {// character in guild - char_delete2_ack(fd, char_id, 4, 0); - return; - } + // This check is imposed by Aegis to avoid dead entries in databases + // _it is not needed_ as we clear data properly + // see issue: 7338 + if( char_aegis_delete ) + { + if( SQL_SUCCESS != SQL->Query(sql_handle, "SELECT `party_id`, `guild_id` FROM `%s` WHERE `char_id`='%d'", char_db, char_id) + || SQL_SUCCESS != SQL->NextRow(sql_handle) + ) + { + Sql_ShowDebug(sql_handle); + char_delete2_ack(fd, char_id, 3, 0); + return; + } + SQL->GetData(sql_handle, 0, &data, NULL); party_id = atoi(data); + SQL->GetData(sql_handle, 1, &data, NULL); guild_id = atoi(data); - if( party_id ) - {// character in party - char_delete2_ack(fd, char_id, 5, 0); - return; + if( guild_id ) + { + char_delete2_ack(fd, char_id, 4, 0); + return; + } + + if( party_id ) + { + char_delete2_ack(fd, char_id, 5, 0); + return; + } } -*/ // success delete_date = time(NULL)+char_del_delay; @@ -5258,6 +5273,8 @@ int char_config_read(const char* cfgName) char_del_level = atoi(w2); } else if (strcmpi(w1, "char_del_delay") == 0) { char_del_delay = atoi(w2); + } else if (strcmpi(w1, "char_aegis_delete") == 0) { + char_aegis_delete = atoi(w2); } else if(strcmpi(w1,"db_path")==0) { safestrncpy(db_path, w2, sizeof(db_path)); } else if (strcmpi(w1, "fame_list_alchemist") == 0) { diff --git a/src/common/db.c b/src/common/db.c index 8c15c0feb..8d6b08815 100644 --- a/src/common/db.c +++ b/src/common/db.c @@ -1846,6 +1846,8 @@ static DBData* db_obj_ensure(DBMap* self, DBKey key, DBCreateData create, ...) * @protected * @see #db_malloc_dbn(void) * @see DBMap#put + * FIXME: If this method fails shouldn't it return another value? + * Other functions rely on this to know if they were able to put something [Panikon] */ static int db_obj_put(DBMap* self, DBKey key, DBData data, DBData *out_data) { -- cgit v1.2.3-70-g09d2