diff options
Diffstat (limited to 'sql-files/upgrades')
30 files changed, 761 insertions, 51 deletions
diff --git a/sql-files/upgrades/2013-02-14--16-15.sql b/sql-files/upgrades/2013-02-14--16-15.sql index 9afd034b1..b7db70d43 100644 --- a/sql-files/upgrades/2013-02-14--16-15.sql +++ b/sql-files/upgrades/2013-02-14--16-15.sql @@ -1,7 +1,7 @@ #1360858500 CREATE TABLE IF NOT EXISTS `sql_updates` ( - `timestamp` int(11) unsigned NOT NULL, - `ignored` enum('Yes','No') NOT NULL DEFAULT 'No' + `timestamp` INT(11) UNSIGNED NOT NULL, + `ignored` ENUM('Yes','No') NOT NULL DEFAULT 'No' ) ENGINE=MyISAM; ALTER TABLE `skill` ADD COLUMN `flag` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0; -INSERT INTO `sql_updates` (`timestamp`) VALUES (1360858500);
\ No newline at end of file +INSERT INTO `sql_updates` (`timestamp`) VALUES (1360858500); diff --git a/sql-files/upgrades/2013-02-15--18-06.sql b/sql-files/upgrades/2013-02-15--18-06.sql index 066693c70..efb0b2ad6 100644 --- a/sql-files/upgrades/2013-02-15--18-06.sql +++ b/sql-files/upgrades/2013-02-15--18-06.sql @@ -1,3 +1,3 @@ #1360951560 -ALTER TABLE `sql_updates` ADD PRIMARY KEY(`timestamp`); -INSERT INTO `sql_updates` (`timestamp`) VALUES (1360951560);
\ No newline at end of file +ALTER TABLE `sql_updates` ADD PRIMARY KEY (`timestamp`); +INSERT INTO `sql_updates` (`timestamp`) VALUES (1360951560); diff --git a/sql-files/upgrades/2013-03-05--01-05.sql b/sql-files/upgrades/2013-03-05--01-05.sql index 4a1e23807..b1a0e4029 100644 --- a/sql-files/upgrades/2013-03-05--01-05.sql +++ b/sql-files/upgrades/2013-03-05--01-05.sql @@ -1,3 +1,3 @@ #1362445531 -ALTER TABLE `login` ADD `character_slots` TINYINT( 3 ) UNSIGNED NOT NULL; -INSERT INTO `sql_updates` (`timestamp`) VALUES (1362445531);
\ No newline at end of file +ALTER TABLE `login` ADD `character_slots` TINYINT(3) UNSIGNED NOT NULL; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1362445531); diff --git a/sql-files/upgrades/2013-03-09--01-56.sql b/sql-files/upgrades/2013-03-09--01-56.sql index 43811ee33..6edfa79c9 100644 --- a/sql-files/upgrades/2013-03-09--01-56.sql +++ b/sql-files/upgrades/2013-03-09--01-56.sql @@ -1,4 +1,4 @@ #1362794218 -ALTER TABLE `login` ADD COLUMN `pincode` varchar(4) NOT NULL DEFAULT ''; -ALTER TABLE `login` ADD COLUMN `pincode_change` int(11) unsigned NOT NULL DEFAULT '0'; -INSERT INTO `sql_updates` (`timestamp`) VALUES (1362794218);
\ No newline at end of file +ALTER TABLE `login` ADD COLUMN `pincode` VARCHAR(4) NOT NULL DEFAULT ''; +ALTER TABLE `login` ADD COLUMN `pincode_change` INT(11) unsigned NOT NULL DEFAULT '0'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1362794218); diff --git a/sql-files/upgrades/2013-03-27--18-35.sql b/sql-files/upgrades/2013-03-27--18-35.sql index fc50953c4..323aa2ace 100644 --- a/sql-files/upgrades/2013-03-27--18-35.sql +++ b/sql-files/upgrades/2013-03-27--18-35.sql @@ -1,3 +1,3 @@ #1364409316 ALTER TABLE `char` ADD COLUMN `slotchange` SMALLINT(3) unsigned NOT NULL default '0'; -INSERT INTO `sql_updates` (`timestamp`) VALUES (1364409316);
\ No newline at end of file +INSERT INTO `sql_updates` (`timestamp`) VALUES (1364409316); diff --git a/sql-files/upgrades/2013-04-16--01-24.sql b/sql-files/upgrades/2013-04-16--01-24.sql index 69672b368..d011e27e3 100644 --- a/sql-files/upgrades/2013-04-16--01-24.sql +++ b/sql-files/upgrades/2013-04-16--01-24.sql @@ -1,3 +1,3 @@ #1366075474 -#Info http://hercules.ws/board/topic/410-skill-script-command-updatefix/ -UPDATE `skill` SET `flag` = 0 WHERE `flag` = 4 AND `id` != 2535 AND `id` != 681;
\ No newline at end of file +-- Info http://hercules.ws/board/topic/410-skill-script-command-updatefix/ +UPDATE `skill` SET `flag` = 0 WHERE `flag` = 4 AND `id` != 2535 AND `id` != 681; diff --git a/sql-files/upgrades/2013-04-16--02-15.sql b/sql-files/upgrades/2013-04-16--02-15.sql index 771bd6ec2..458d1dc37 100644 --- a/sql-files/upgrades/2013-04-16--02-15.sql +++ b/sql-files/upgrades/2013-04-16--02-15.sql @@ -1,4 +1,4 @@ #1366078541 -ALTER TABLE `char` ADD `char_opt` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0'; -INSERT INTO `sql_updates` (`timestamp`) VALUES (1366075474);#for the previous that missed it.. -INSERT INTO `sql_updates` (`timestamp`) VALUES (1366078541);
\ No newline at end of file +ALTER TABLE `char` ADD `char_opt` INT(11) UNSIGNED NOT NULL DEFAULT '0'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1366075474); -- for the previous that missed it.. +INSERT INTO `sql_updates` (`timestamp`) VALUES (1366078541); diff --git a/sql-files/upgrades/2013-10-09--21-38.sql b/sql-files/upgrades/2013-10-09--21-38.sql new file mode 100644 index 000000000..d8854bcdf --- /dev/null +++ b/sql-files/upgrades/2013-10-09--21-38.sql @@ -0,0 +1,3 @@ +#1381354728 +ALTER TABLE `zenylog` MODIFY `type` ENUM('T','V','P','M','S','N','D','C','A','E','I','B','K') NOT NULL DEFAULT 'S'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1381354728); diff --git a/sql-files/upgrades/2013-10-10--16-36.sql b/sql-files/upgrades/2013-10-10--16-36.sql new file mode 100644 index 000000000..88db7609e --- /dev/null +++ b/sql-files/upgrades/2013-10-10--16-36.sql @@ -0,0 +1,7 @@ +#1381423003 +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', + PRIMARY KEY (`account_id`) +) ENGINE=MyISAM; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1381423003); diff --git a/sql-files/upgrades/2013-10-27--16-47.sql b/sql-files/upgrades/2013-10-27--16-47.sql new file mode 100644 index 000000000..2a9a73f31 --- /dev/null +++ b/sql-files/upgrades/2013-10-27--16-47.sql @@ -0,0 +1,6 @@ +#1382892428 +ALTER TABLE `inventory` MODIFY `equip` INT(11) UNSIGNED NOT NULL DEFAULT '0'; +ALTER TABLE `storage` MODIFY `equip` INT(11) UNSIGNED NOT NULL DEFAULT '0'; +ALTER TABLE `cart_inventory` MODIFY `equip` INT(11) UNSIGNED NOT NULL DEFAULT '0'; +ALTER TABLE `guild_storage` MODIFY `equip` INT(11) UNSIGNED NOT NULL DEFAULT '0'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1382892428); diff --git a/sql-files/upgrades/2013-10-30--19-53.sql b/sql-files/upgrades/2013-10-30--19-53.sql new file mode 100644 index 000000000..6e89015e9 --- /dev/null +++ b/sql-files/upgrades/2013-10-30--19-53.sql @@ -0,0 +1,5 @@ +#1383162785 +ALTER TABLE `account_data` ADD `base_exp` TINYINT(4) UNSIGNED NOT NULL DEFAULT '100'; +ALTER TABLE `account_data` ADD `base_drop` TINYINT(4) UNSIGNED NOT NULL DEFAULT '100'; +ALTER TABLE `account_data` ADD `base_death` TINYINT(4) UNSIGNED NOT NULL DEFAULT '100'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1383162785); diff --git a/sql-files/upgrades/2013-10-30--21-12.sql b/sql-files/upgrades/2013-10-30--21-12.sql new file mode 100644 index 000000000..e0fbda874 --- /dev/null +++ b/sql-files/upgrades/2013-10-30--21-12.sql @@ -0,0 +1,3 @@ +#1383167577 +ALTER TABLE `char` ADD `font` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1383167577); diff --git a/sql-files/upgrades/2013-10-31--07-49.sql b/sql-files/upgrades/2013-10-31--07-49.sql new file mode 100644 index 000000000..25f489f57 --- /dev/null +++ b/sql-files/upgrades/2013-10-31--07-49.sql @@ -0,0 +1,6 @@ +#1383205740 +ALTER TABLE `inventory` ADD COLUMN `bound` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `favorite`; +ALTER TABLE `cart_inventory` ADD COLUMN `bound` TINYINT(1) UNSIGNED NOT NULL default '0' AFTER `expire_time`; +ALTER TABLE `storage` ADD COLUMN `bound` TINYINT(1) UNSIGNED NOT NULL default '0' AFTER `expire_time`; +ALTER TABLE `guild_storage` ADD COLUMN `bound` TINYINT(1) UNSIGNED NOT NULL default '0' AFTER `expire_time`; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1383205740); diff --git a/sql-files/upgrades/2013-11-09--00-03.sql b/sql-files/upgrades/2013-11-09--00-03.sql new file mode 100644 index 000000000..92fe1b76b --- /dev/null +++ b/sql-files/upgrades/2013-11-09--00-03.sql @@ -0,0 +1,5 @@ +#1383955424 +ALTER TABLE `account_data` MODIFY `base_exp` TINYINT(4) UNSIGNED NOT NULL DEFAULT '100'; +ALTER TABLE `account_data` MODIFY `base_drop` TINYINT(4) UNSIGNED NOT NULL DEFAULT '100'; +ALTER TABLE `account_data` MODIFY `base_death` TINYINT(4) UNSIGNED NOT NULL DEFAULT '100'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1383955424); diff --git a/sql-files/upgrades/2013-11-15--00-06.sql b/sql-files/upgrades/2013-11-15--00-06.sql new file mode 100644 index 000000000..dafc5615a --- /dev/null +++ b/sql-files/upgrades/2013-11-15--00-06.sql @@ -0,0 +1,105 @@ +#1384473995 + +-- Note: If you're running a MySQL version earlier than 5.0 (or if this scripts fails for you for any reason) +-- you'll need to run the following queries manually: +-- +-- [ Pre-Renewal only ] +-- ALTER TABLE item_db2 ADD COLUMN `matk` SMALLINT(5) UNSIGNED DEFAULT NULL AFTER atk; +-- ALTER TABLE item_db2 CHANGE COLUMN `equip_level` `equip_level_min` SMALLINT(5) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 ADD COLUMN `equip_level_max` SMALLINT(5) UNSIGNED DEFAULT NULL AFTER equip_level_min; +-- [ Both Pre-Renewal and Renewal ] +-- ALTER TABLE item_db2 MODIFY COLUMN `price_buy` MEDIUMINT(10) DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `price_sell` MEDIUMINT(10) DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `weight` SMALLINT(5) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `atk` SMALLINT(5) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `matk` SMALLINT(5) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `defence` SMALLINT(5) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `range` TINYINT(2) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `slots` TINYINT(2) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `equip_jobs` INT(12) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `equip_upper` TINYINT(8) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `equip_genders` TINYINT(2) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `equip_locations` SMALLINT(4) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `weapon_level` TINYINT(2) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `equip_level_min` SMALLINT(5) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `equip_level_max` SMALLINT(5) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `refineable` TINYINT(1) UNSIGNED DEFAULT NULL; +-- ALTER TABLE item_db2 MODIFY COLUMN `view` SMALLINT(3) UNSIGNED DEFAULT NULL; +-- INSERT INTO `sql_updates` (`timestamp`) VALUES (1384473995); +-- +-- [ End ] +-- What follows is the automated script that does all of the above. + +DELIMITER $$ + +DROP PROCEDURE IF EXISTS alter_if_not_exists $$ +DROP PROCEDURE IF EXISTS alter_if_exists $$ + +CREATE PROCEDURE alter_if_not_exists(my_table TINYTEXT, my_column TINYTEXT, my_command TINYTEXT, my_predicate TEXT) +BEGIN + set @dbname = DATABASE(); + IF EXISTS ( + SELECT * FROM information_schema.TABLES + WHERE TABLE_SCHEMA = @dbname + AND TABLE_NAME = my_table + ) AND NOT EXISTS ( + SELECT * FROM information_schema.COLUMNS + WHERE TABLE_SCHEMA = @dbname + AND TABLE_NAME = my_table + AND COLUMN_NAME = my_column + ) + THEN + SET @q = CONCAT('ALTER TABLE ', @dbname, '.', my_table, ' ', + my_command, ' `', my_column, '` ', my_predicate); + PREPARE STMT FROM @q; + EXECUTE STMT; + END IF; + +END $$ + +CREATE PROCEDURE alter_if_exists(my_table TINYTEXT, my_column TINYTEXT, my_command TINYTEXT, my_predicate TEXT) +BEGIN + set @dbname = DATABASE(); + IF EXISTS ( + SELECT * FROM information_schema.COLUMNS + WHERE TABLE_SCHEMA = @dbname + AND TABLE_NAME = my_table + AND COLUMN_NAME = my_column + ) + THEN + SET @q = CONCAT('ALTER TABLE ', @dbname, '.', my_table, ' ', + my_command, ' `', my_column, '` ', my_predicate); + PREPARE STMT FROM @q; + EXECUTE STMT; + END IF; + +END $$ + +CALL alter_if_not_exists('item_db2', 'matk', 'ADD COLUMN', 'SMALLINT(5) UNSIGNED DEFAULT NULL AFTER atk') $$ +CALL alter_if_exists('item_db2', 'equip_level', 'CHANGE COLUMN', 'equip_level_min SMALLINT(5) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_not_exists('item_db2', 'equip_level_max', 'ADD COLUMN', 'SMALLINT(5) UNSIGNED DEFAULT NULL AFTER equip_level_min') $$ + +CALL alter_if_exists('item_db2', 'price_buy', 'MODIFY COLUMN', 'MEDIUMINT(10) DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'price_sell', 'MODIFY COLUMN', 'MEDIUMINT(10) DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'weight', 'MODIFY COLUMN', 'SMALLINT(5) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'atk', 'MODIFY COLUMN', 'SMALLINT(5) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'matk', 'MODIFY COLUMN', 'SMALLINT(5) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'defence', 'MODIFY COLUMN', 'SMALLINT(5) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'range', 'MODIFY COLUMN', 'TINYINT(2) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'slots', 'MODIFY COLUMN', 'TINYINT(2) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'equip_jobs', 'MODIFY COLUMN', 'INT(12) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'equip_upper', 'MODIFY COLUMN', 'TINYINT(8) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'equip_genders', 'MODIFY COLUMN', 'TINYINT(2) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'equip_locations', 'MODIFY COLUMN', 'SMALLINT(4) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'weapon_level', 'MODIFY COLUMN', 'TINYINT(2) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'equip_level_min', 'MODIFY COLUMN', 'SMALLINT(5) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'equip_level_max', 'MODIFY COLUMN', 'SMALLINT(5) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'refineable', 'MODIFY COLUMN', 'TINYINT(1) UNSIGNED DEFAULT NULL') $$ +CALL alter_if_exists('item_db2', 'view', 'MODIFY COLUMN', 'SMALLINT(3) UNSIGNED DEFAULT NULL') $$ + +DROP PROCEDURE IF EXISTS alter_if_not_exists $$ +DROP PROCEDURE IF EXISTS alter_if_exists $$ + +DELIMITER ';' + +INSERT INTO `sql_updates` (`timestamp`) VALUES (1384473995); diff --git a/sql-files/upgrades/2013-11-15--19-57.sql b/sql-files/upgrades/2013-11-15--19-57.sql new file mode 100644 index 000000000..d4d8ec501 --- /dev/null +++ b/sql-files/upgrades/2013-11-15--19-57.sql @@ -0,0 +1,5 @@ +#1384545461 +UPDATE `account_data` SET `base_exp` = '100' WHERE `base_exp` = '0'; +UPDATE `account_data` SET `base_drop` = '100' WHERE `base_drop` = '0'; +UPDATE `account_data` SET `base_death` = '100' WHERE `base_death` = '0'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1384545461); diff --git a/sql-files/upgrades/2013-11-16--07-49.sql b/sql-files/upgrades/2013-11-16--07-49.sql new file mode 100644 index 000000000..fce74aab1 --- /dev/null +++ b/sql-files/upgrades/2013-11-16--07-49.sql @@ -0,0 +1,3 @@ +#1384588175 +ALTER TABLE `char` ADD COLUMN `unban_time` INT(11) UNSIGNED NOT NULL DEFAULT '0'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1384588175); diff --git a/sql-files/upgrades/2013-11-18--08-23.sql b/sql-files/upgrades/2013-11-18--08-23.sql new file mode 100644 index 000000000..d7100d78a --- /dev/null +++ b/sql-files/upgrades/2013-11-18--08-23.sql @@ -0,0 +1,65 @@ +#1384763034 + +-- Note: If you're running a MySQL version earlier than 5.0 (or if this scripts fails for you for any reason) +-- you'll need to run the following queries manually: +-- +-- [ Both Pre-Renewal and Renewal ] +-- ALTER TABLE item_db2 ADD COLUMN `bindonequip` TINYINT(1) UNSIGNED DEFAULT NULL AFTER `view`; +-- INSERT INTO `sql_updates` (`timestamp`) VALUES (1384763034); +-- +-- [ End ] +-- What follows is the automated script that does all of the above. + +DELIMITER $$ + +DROP PROCEDURE IF EXISTS alter_if_not_exists $$ +DROP PROCEDURE IF EXISTS alter_if_exists $$ + +CREATE PROCEDURE alter_if_not_exists(my_table TINYTEXT, my_column TINYTEXT, my_command TINYTEXT, my_predicate TEXT) +BEGIN + set @dbname = DATABASE(); + IF EXISTS ( + SELECT * FROM information_schema.TABLES + WHERE TABLE_SCHEMA = @dbname + AND TABLE_NAME = my_table + ) AND NOT EXISTS ( + SELECT * FROM information_schema.COLUMNS + WHERE TABLE_SCHEMA = @dbname + AND TABLE_NAME = my_table + AND COLUMN_NAME = my_column + ) + THEN + SET @q = CONCAT('ALTER TABLE ', @dbname, '.', my_table, ' ', + my_command, ' `', my_column, '` ', my_predicate); + PREPARE STMT FROM @q; + EXECUTE STMT; + END IF; + +END $$ + +CREATE PROCEDURE alter_if_exists(my_table TINYTEXT, my_column TINYTEXT, my_command TINYTEXT, my_predicate TEXT) +BEGIN + set @dbname = DATABASE(); + IF EXISTS ( + SELECT * FROM information_schema.COLUMNS + WHERE TABLE_SCHEMA = @dbname + AND TABLE_NAME = my_table + AND COLUMN_NAME = my_column + ) + THEN + SET @q = CONCAT('ALTER TABLE ', @dbname, '.', my_table, ' ', + my_command, ' `', my_column, '` ', my_predicate); + PREPARE STMT FROM @q; + EXECUTE STMT; + END IF; + +END $$ + +CALL alter_if_not_exists('item_db2', 'bindonequip', 'ADD COLUMN', 'TINYINT(1) UNSIGNED DEFAULT NULL AFTER `view`') $$ + +DROP PROCEDURE IF EXISTS alter_if_not_exists $$ +DROP PROCEDURE IF EXISTS alter_if_exists $$ + +DELIMITER ';' + +INSERT INTO `sql_updates` (`timestamp`) VALUES (1384763034); diff --git a/sql-files/upgrades/2013-12-24--00-15.sql b/sql-files/upgrades/2013-12-24--00-15.sql new file mode 100644 index 000000000..2de4771a6 --- /dev/null +++ b/sql-files/upgrades/2013-12-24--00-15.sql @@ -0,0 +1,8 @@ +#1387844126 +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; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1387844126); diff --git a/sql-files/upgrades/2014-01-04--16-47.sql b/sql-files/upgrades/2014-01-04--16-47.sql new file mode 100644 index 000000000..40be437d9 --- /dev/null +++ b/sql-files/upgrades/2014-01-04--16-47.sql @@ -0,0 +1,61 @@ +#1388854043 +ALTER TABLE `mapreg` ADD PRIMARY KEY (`varname`, `index`); +ALTER TABLE `mapreg` DROP INDEX `varname`; +ALTER TABLE `mapreg` DROP INDEX `index`; +ALTER TABLE `mapreg` MODIFY `varname` VARCHAR(32) BINARY NOT NULL; +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; +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; +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; +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; +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; +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; +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 '%$'; +# DROP TABLE `global_reg_value`; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1388854043); diff --git a/sql-files/upgrades/2014-01-06--17-22.sql b/sql-files/upgrades/2014-01-06--17-22.sql new file mode 100644 index 000000000..e3ca8935e --- /dev/null +++ b/sql-files/upgrades/2014-01-06--17-22.sql @@ -0,0 +1,16 @@ +#1389028967 +CREATE TABLE IF NOT EXISTS `autotrade_merchants` ( + `account_id` INT(11) NOT NULL DEFAULT '0', + `char_id` INT(11) NOT NULL DEFAULT '0', + `sex` TINYINT(2) NOT NULL DEFAULT '0', + `title` varchar(80) NOT NULL DEFAULT 'Buy From Me!', + PRIMARY KEY (`account_id`,`char_id`) +) ENGINE=MyISAM; +CREATE TABLE IF NOT EXISTS `autotrade_data` ( + `char_id` INT(11) NOT NULL DEFAULT '0', + `itemkey` INT(11) NOT NULL DEFAULT '0', + `amount` INT(11) NOT NULL DEFAULT '0', + `price` INT(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`char_id`,`itemkey`) +) ENGINE=MyISAM; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1389028967); diff --git a/sql-files/upgrades/2014-02-19--17-57.sql b/sql-files/upgrades/2014-02-19--17-57.sql new file mode 100644 index 000000000..63abc335f --- /dev/null +++ b/sql-files/upgrades/2014-02-19--17-57.sql @@ -0,0 +1,4 @@ +#1392832626 +DELETE FROM `sc_data` WHERE `tick` = '-1'; +ALTER TABLE `sc_data` ADD PRIMARY KEY (`account_id`,`char_id`,`type`); +INSERT INTO `sql_updates` (`timestamp`) VALUES (1392832626); diff --git a/sql-files/upgrades/2014-03-25--23-57.sql b/sql-files/upgrades/2014-03-25--23-57.sql new file mode 100644 index 000000000..40d3fb2fc --- /dev/null +++ b/sql-files/upgrades/2014-03-25--23-57.sql @@ -0,0 +1,3 @@ +#1395789302 +ALTER TABLE `charlog` ADD COLUMN `char_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `account_id`; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1395789302); diff --git a/sql-files/upgrades/2014-04-07--22-04.sql b/sql-files/upgrades/2014-04-07--22-04.sql new file mode 100644 index 000000000..56c54eac4 --- /dev/null +++ b/sql-files/upgrades/2014-04-07--22-04.sql @@ -0,0 +1,3 @@ +#1396893866 +ALTER TABLE `char` ADD COLUMN `uniqueitem_counter` BIGINT(20) NOT NULL AFTER `unban_time`; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1396893866); diff --git a/sql-files/upgrades/2014-04-26--10-00.sql b/sql-files/upgrades/2014-04-26--10-00.sql new file mode 100644 index 000000000..0c0b571e0 --- /dev/null +++ b/sql-files/upgrades/2014-04-26--10-00.sql @@ -0,0 +1,3 @@ +#1398477600 +ALTER TABLE `char` CHANGE COLUMN `uniqueitem_counter` `uniqueitem_counter` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1398477600); diff --git a/sql-files/upgrades/2014-05-17--00-06.sql b/sql-files/upgrades/2014-05-17--00-06.sql new file mode 100644 index 000000000..4e5d0d32a --- /dev/null +++ b/sql-files/upgrades/2014-05-17--00-06.sql @@ -0,0 +1,3 @@ +#1400256139 +ALTER TABLE `pet` CHANGE `incuvate` `incubate` int(11) unsigned NOT NULL default '0'; +INSERT INTO `sql_updates` (`timestamp`) VALUES (1400256139); diff --git a/sql-files/upgrades/eathena-upgrade.sql b/sql-files/upgrades/eathena-upgrade.sql index 63a0d1151..20678dc9f 100644 --- a/sql-files/upgrades/eathena-upgrade.sql +++ b/sql-files/upgrades/eathena-upgrade.sql @@ -1,78 +1,114 @@ -##### -#Upgrade file to be used when going from eAthena to Hercules -#Note: If you're not up to date with eAthena, go through their upgrade files first and run them before this file. -#Note: After runing this file run Hercules upgrade files. -##### +-- +-- Upgrade file to be used when going from eAthena to Hercules +-- Note: If you're not up to date with eAthena, go through their upgrade files first and run them before this file. +-- Note: After runing this file run Hercules upgrade files. +-- + ALTER TABLE `global_reg_value` MODIFY `type` TINYINT(1) UNSIGNED NOT NULL DEFAULT '3'; + -- Adds 'I' and 'X' to `type` in `picklog` table ALTER TABLE `picklog` MODIFY `type` ENUM('M','P','L','T','V','S','N','C','A','R','G','E','B','O','I','X') NOT NULL DEFAULT 'P'; + -- Adds 'D' and 'U' to `type` in `picklog` table 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'; + -- `ExpPer` column removed from `mob_db` and `mob_db2` tables ALTER TABLE `mob_db` DROP COLUMN `ExpPer`; ALTER TABLE `mob_db2` DROP COLUMN `ExpPer`; + -- Rename `level` column to `group_id` in `login` table ALTER TABLE `login` CHANGE COLUMN `level` `group_id` TINYINT(3) NOT NULL DEFAULT '0'; + -- Adds 'I' to `type` in `zenylog` ALTER TABLE `zenylog` MODIFY `type` ENUM('M','T','V','S','N','A','E','B','I') NOT NULL DEFAULT 'S'; -ALTER TABLE `char` ADD COLUMN `elemental_id` int(11) unsigned NOT NULL default '0'; + +ALTER TABLE `char` ADD COLUMN `elemental_id` INT(11) UNSIGNED NOT NULL DEFAULT '0'; + CREATE TABLE IF NOT EXISTS `elemental` ( - `ele_id` int(11) unsigned NOT NULL auto_increment, - `char_id` int(11) NOT NULL, - `class` mediumint(9) unsigned NOT NULL default '0', - `mode` int(11) unsigned NOT NULL default '1', - `hp` int(12) NOT NULL default '1', - `sp` int(12) NOT NULL default '1', - `max_hp` mediumint(8) unsigned NOT NULL default '0', - `max_sp` mediumint(6) unsigned NOT NULL default '0', - `str` smallint(4) unsigned NOT NULL default '0', - `agi` smallint(4) unsigned NOT NULL default '0', - `vit` smallint(4) unsigned NOT NULL default '0', - `int` smallint(4) unsigned NOT NULL default '0', - `dex` smallint(4) unsigned NOT NULL default '0', - `luk` smallint(4) unsigned NOT NULL default '0', - `life_time` int(11) NOT NULL default '0', - PRIMARY KEY (`ele_id`) + `ele_id` INT(11) UNSIGNED NOT NULL auto_increment, + `char_id` INT(11) NOT NULL, + `class` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0', + `mode` INT(11) UNSIGNED NOT NULL DEFAULT '1', + `hp` INT(12) NOT NULL DEFAULT '1', + `sp` INT(12) NOT NULL DEFAULT '1', + `max_hp` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0', + `max_sp` MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT '0', + `str` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + `agi` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + `vit` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + `INT` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + `dex` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + `luk` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + `life_time` INT(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`ele_id`) ) ENGINE=MyISAM; + -- Adds 'D' to `type` in `zenylog` ALTER TABLE `zenylog` MODIFY `type` ENUM('M','T','V','S','N','A','E','B','I','D') NOT NULL DEFAULT 'S'; + ALTER TABLE `char` ADD CONSTRAINT `name_key` UNIQUE (`name`); + ALTER TABLE `inventory` ADD COLUMN `favorite` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER `expire_time`; + ALTER TABLE `item_db_re` CHANGE `equip_level` `equip_level` VARCHAR(10) DEFAULT ''; + ALTER TABLE `item_db_re` MODIFY COLUMN `atk:matk` VARCHAR(11) DEFAULT ''; + ALTER TABLE `item_db_re` MODIFY COLUMN `defence` SMALLINT(5) UNSIGNED DEFAULT NULL; + ALTER TABLE `homunculus` ADD `prev_class` MEDIUMINT( 9 ) NOT NULL AFTER `class` + ALTER TABLE `item_db_re` MODIFY `defence` SMALLINT(5) DEFAULT NULL; + ALTER TABLE `item_db` MODIFY `defence` SMALLINT(5) DEFAULT NULL; + ALTER TABLE `zenylog` MODIFY `type` ENUM('T','V','P','M','S','N','D','C','A','E','I','B') NOT NULL DEFAULT 'S'; + ALTER TABLE `elemental` CHANGE COLUMN `str` `atk1` MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN `agi` `atk2` MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN `vit` `matk` MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN `int` `aspd` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN `dex` `def` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN `luk` `mdef` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN `life_time` `flee` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN `hit` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0 AFTER `flee`, - ADD COLUMN `life_time` INT(11) NOT NULL DEFAULT 0 AFTER `hit`; + CHANGE COLUMN `agi` `atk2` MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN `vit` `matk` MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN `INT` `aspd` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN `dex` `def` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN `luk` `mdef` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN `life_time` `flee` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN `hit` SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0 AFTER `flee`, + ADD COLUMN `life_time` INT(11) NOT NULL DEFAULT 0 AFTER `hit`; + ALTER TABLE `picklog` ADD `nsiuid` BIGINT NOT NULL DEFAULT '0' AFTER `card3`; + CREATE TABLE IF NOT EXISTS `interreg` ( - `varname` varchar(11) NOT NULL, - `value` varchar(20) NOT NULL, + `varname` VARCHAR(11) NOT NULL, + `value` VARCHAR(20) NOT NULL, PRIMARY KEY (`varname`) ) ENGINE=InnoDB; -INSERT INTO `interreg` (`varname`, `value`) VALUES -('nsiuid', '0'); + +INSERT INTO `interreg` (`varname`, `value`) VALUES ('nsiuid', '0'); + ALTER TABLE `auction` ADD `nsiuid` BIGINT NOT NULL DEFAULT '0'; + ALTER TABLE `cart_inventory` ADD `nsiuid` BIGINT NOT NULL DEFAULT '0'; + ALTER TABLE `guild_storage` ADD `nsiuid` BIGINT NOT NULL DEFAULT '0'; + ALTER TABLE `inventory` ADD `nsiuid` BIGINT NOT NULL DEFAULT '0'; + ALTER TABLE `mail` ADD `nsiuid` BIGINT NOT NULL DEFAULT '0'; + ALTER TABLE `storage` ADD `nsiuid` BIGINT NOT NULL DEFAULT '0'; + ALTER TABLE `picklog` CHANGE `nsiuid` `unique_id` BIGINT( 20 ) NOT NULL DEFAULT '0'; + UPDATE `interreg` SET `varname` = 'unique_id' WHERE `interreg`.`varname` = 'nsiuid'; + ALTER TABLE `auction` CHANGE `nsiuid` `unique_id` BIGINT( 20 ) NOT NULL DEFAULT '0'; + ALTER TABLE `cart_inventory` CHANGE `nsiuid` `unique_id` BIGINT( 20 ) NOT NULL DEFAULT '0'; + ALTER TABLE `guild_storage` CHANGE `nsiuid` `unique_id` BIGINT( 20 ) NOT NULL DEFAULT '0'; + ALTER TABLE `inventory` CHANGE `nsiuid` `unique_id` BIGINT( 20 ) NOT NULL DEFAULT '0'; + ALTER TABLE `mail` CHANGE `nsiuid` `unique_id` BIGINT( 20 ) NOT NULL DEFAULT '0'; + ALTER TABLE `storage` CHANGE `nsiuid` `unique_id` BIGINT( 20 ) NOT NULL DEFAULT '0'; + diff --git a/sql-files/upgrades/index.txt b/sql-files/upgrades/index.txt index 54502d48b..ab8ee772d 100644 --- a/sql-files/upgrades/index.txt +++ b/sql-files/upgrades/index.txt @@ -3,4 +3,23 @@ 2013-03-05--01-05.sql 2013-03-06--00-00.sql 2013-03-09--01-56.sql -2013-04-16--01-24.sql
\ No newline at end of file +2013-04-16--01-24.sql +2013-10-09--21-38.sql +2013-10-10--16-36.sql +2013-10-27--16-47.sql +2013-10-30--19-53.sql +2013-10-30--21-12.sql +2013-10-31--07-49.sql +2013-11-09--00-03.sql +2013-11-15--00-06.sql +2013-11-15--19-57.sql +2013-11-16--07-49.sql +2013-11-18--08-23.sql +2013-12-24--00-15.sql +2014-01-04--16-47.sql +2014-01-06--17-22.sql +2014-02-19--17-57.sql +2014-03-25--23-57.sql +2014-04-07--22-04.sql +2014-04-26--10-00.sql +2014-05-17--00-06.sql diff --git a/sql-files/upgrades/rAthena-logs-upgrade.sql b/sql-files/upgrades/rAthena-logs-upgrade.sql new file mode 100644 index 000000000..a286099a1 --- /dev/null +++ b/sql-files/upgrades/rAthena-logs-upgrade.sql @@ -0,0 +1,22 @@ +-- 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: July 22, 2014 20:45 GMT + +-- Drop table `cashlog` since it's not used in Hercules +-- Comment it if you wish to keep the table +DROP TABLE IF EXISTS `cashlog`; + +-- Upgrades to table `mvplog` +ALTER TABLE `mvplog` MODIFY `prize` INT(11) NOT NULL DEFAULT '0'; + +-- 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'; +ALTER TABLE `picklog` MODIFY `nameid` INT(11) NOT NULL DEFAULT '0'; +ALTER TABLE `picklog` MODIFY `card0` INT(11) NOT NULL DEFAULT '0'; +ALTER TABLE `picklog` MODIFY `card1` INT(11) NOT NULL DEFAULT '0'; +ALTER TABLE `picklog` MODIFY `card2` INT(11) NOT NULL DEFAULT '0'; +ALTER TABLE `picklog` MODIFY `card3` INT(11) NOT NULL DEFAULT '0'; diff --git a/sql-files/upgrades/rAthena-main-upgrade.sql b/sql-files/upgrades/rAthena-main-upgrade.sql new file mode 100644 index 000000000..f00386207 --- /dev/null +++ b/sql-files/upgrades/rAthena-main-upgrade.sql @@ -0,0 +1,319 @@ +-- 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: July 22, 2014 21:45 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 +-- 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 `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`, + ADD `uniqueitem_counter` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' AFTER `unban_time`; + +-- 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'; + +-- 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 `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 enter +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 +INSERT INTO `sql_updates` (`timestamp`) VALUES (1395789302); -- 2014-03-25--23-57.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1396893866); -- 2014-04-07--22-04.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1398477600); -- 2014-04-26--10-00.sql +INSERT INTO `sql_updates` (`timestamp`) VALUES (1400256139); -- 2014-05-17--00-06.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` 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; + +-- 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 ; + +-- 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`; + |