diff options
| -rw-r--r-- | sql-files/upgrades/2013-11-18--08-23.sql | 65 | ||||
| -rw-r--r-- | sql-files/upgrades/index.txt | 3 | ||||
| -rw-r--r-- | src/map/itemdb.c | 8 |
3 files changed, 74 insertions, 2 deletions
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..d5da5fd95 --- /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/index.txt b/sql-files/upgrades/index.txt index 1e76ee41b..9b7ad20d8 100644 --- a/sql-files/upgrades/index.txt +++ b/sql-files/upgrades/index.txt @@ -13,4 +13,5 @@ 2013-11-09--00-03.sql 2013-11-15--00-06.sql 2013-11-15--19-57.sql -2013-11-16--07-49.sql
\ No newline at end of file +2013-11-16--07-49.sql +2013-11-18--08-23.sql diff --git a/src/map/itemdb.c b/src/map/itemdb.c index 2214e9c64..2fcc60c70 100644 --- a/src/map/itemdb.c +++ b/src/map/itemdb.c @@ -1939,7 +1939,13 @@ int itemdb_readdb_sql(const char *tablename) { int i = 0, count = 0; // retrieve all rows from the item database - if( SQL_ERROR == SQL->Query(map->mysql_handle, "SELECT * FROM `%s`", tablename) ) { + if( SQL_ERROR == SQL->Query(map->mysql_handle, "SELECT `id`, `name_english`, `name_japanese`, `type`," + " `price_buy`, `price_sell`, `weight`, `atk`," + " `matk`, `defence`, `range`, `slots`," + " `equip_jobs`, `equip_upper`, `equip_genders`, `equip_locations`," + " `weapon_level`, `equip_level_min`, `equip_level_max`, `refineable`," + " `view`, `bindonequip`, `script`, `equip_script`, `unequip_script`" + "FROM `%s`", tablename) ) { Sql_ShowDebug(map->mysql_handle); return 0; } |