summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorHaru <haru@dotalux.com>2013-11-18 09:29:40 +0100
committerHaru <haru@dotalux.com>2013-11-18 09:29:40 +0100
commit4655f6611b14fca90ed38b021bf6a365cbfb335a (patch)
treef70c9f2392b7e8d2a95c2be4d99d8f8e0aa93375
parent3e9758706ecf80d7656daefb546c80bf6b549967 (diff)
downloadhercules-4655f6611b14fca90ed38b021bf6a365cbfb335a.tar.gz
hercules-4655f6611b14fca90ed38b021bf6a365cbfb335a.tar.bz2
hercules-4655f6611b14fca90ed38b021bf6a365cbfb335a.tar.xz
hercules-4655f6611b14fca90ed38b021bf6a365cbfb335a.zip
Follow-up to 1f5161a2bd3c7934373146d8cac3c131536758ba
- Added explicit column names in the item_db query, to ensure it will throw a SQL error in case some are missing, rahter than silently failing and returning incorrect data. - Added sql upgrade file to add the bindonequip column to the item_db2 table for those who have it. - As with all the item_db2 upgrade scripts, you need at least MySQL 5.0 in order to run it. If your version is not compatible, or if the script fails for any reason, manual upgrade instructions are provided within the script. - Special thanks to Mumbles. Signed-off-by: Haru <haru@dotalux.com>
-rw-r--r--sql-files/upgrades/2013-11-18--08-23.sql65
-rw-r--r--sql-files/upgrades/index.txt3
-rw-r--r--src/map/itemdb.c8
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;
}