update sql files.

4 files changed, 69 insertions, 20 deletions

diff --git a/sql-files/main.sql b/sql-files/main.sql
index a00a3319..64bdfb5c 100644
--- a/sql-files/main.sql
+++ b/sql-files/main.sql
@@ -176,6 +176,7 @@ CREATE TABLE IF NOT EXISTS `char` (
   `unban_time` INT(11) UNSIGNED NOT NULL DEFAULT '0',
   `uniqueitem_counter` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
   `sex` ENUM('M','F','U') NOT NULL DEFAULT 'U',
+  `hotkey_rowshift` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
   PRIMARY KEY (`char_id`),
   UNIQUE KEY `name_key` (`name`),
   KEY `account_id` (`account_id`),
@@ -495,16 +496,6 @@ CREATE TABLE IF NOT EXISTS `interlog` (
 ) ENGINE=MyISAM; 
 
 --
--- Table structure for table `interreg`
---
-
-CREATE TABLE IF NOT EXISTS `interreg` (
-  `varname` VARCHAR(11) NOT NULL,
-  `value` VARCHAR(20) NOT NULL,
-   PRIMARY KEY (`varname`)
-) ENGINE=InnoDB;
-
---
 -- Table structure for table `inventory`
 --
 
@@ -808,16 +799,9 @@ INSERT INTO `sql_updates` (`timestamp`) VALUES (1398477600); -- 2014-04-26--10-0
 INSERT INTO `sql_updates` (`timestamp`) VALUES (1400256139); -- 2014-05-17--00-06.sql
 INSERT INTO `sql_updates` (`timestamp`) VALUES (1409590380); -- 2014-09-01--16-53.sql
 INSERT INTO `sql_updates` (`timestamp`) VALUES (1414975503); -- 2014-11-03--00-45.sql
-
---
--- Table structure for table `sstatus`
---
-
-CREATE TABLE IF NOT EXISTS `sstatus` (
-  `index` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
-  `name` VARCHAR(255) NOT NULL DEFAULT '',
-  `user` INT(11) UNSIGNED NOT NULL DEFAULT '0'
-) ENGINE=MyISAM;
+INSERT INTO `sql_updates` (`timestamp`) VALUES (1435860840); -- 2015-07-02--18-14.sql
+INSERT INTO `sql_updates` (`timestamp`) VALUES (1436360978); -- 2015-07-08--13-08.sql
+INSERT INTO `sql_updates` (`timestamp`) VALUES (1440688342); -- 2015-08-27--20-42.sql
 
 --
 -- Table structure for table `storage`
diff --git a/sql-files/upgrades/2015-07-02--18-14.sql b/sql-files/upgrades/2015-07-02--18-14.sql
new file mode 100644
index 00000000..49094a5d
--- /dev/null
+++ b/sql-files/upgrades/2015-07-02--18-14.sql
@@ -0,0 +1,56 @@
+#1435860840
+
+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_db', 'forceserial', 'ADD COLUMN', 'TINYINT(1) UNSIGNED DEFAULT NULL AFTER `bindonequip`') $$
+CALL alter_if_not_exists('item_db2', 'forceserial', 'ADD COLUMN', 'TINYINT(1) UNSIGNED DEFAULT NULL AFTER `bindonequip`') $$
+
+DROP PROCEDURE IF EXISTS alter_if_not_exists $$
+DROP PROCEDURE IF EXISTS alter_if_exists $$
+
+DELIMITER ';'
+
+INSERT INTO `sql_updates` (`timestamp`) VALUES (1435860840);
diff --git a/sql-files/upgrades/2015-07-08--13-08.sql b/sql-files/upgrades/2015-07-08--13-08.sql
new file mode 100644
index 00000000..4d720858
--- /dev/null
+++ b/sql-files/upgrades/2015-07-08--13-08.sql
@@ -0,0 +1,6 @@
+#1436360978
+
+DROP TABLE IF EXISTS interreg;
+DROP TABLE IF EXISTS sstatus;
+
+INSERT INTO `sql_updates` (`timestamp`) VALUES (1436360978);
diff --git a/sql-files/upgrades/2015-08-27--20-42.sql b/sql-files/upgrades/2015-08-27--20-42.sql
new file mode 100644
index 00000000..e95e1836
--- /dev/null
+++ b/sql-files/upgrades/2015-08-27--20-42.sql
@@ -0,0 +1,3 @@
+#1440688342
+ALTER TABLE  `char` ADD COLUMN `hotkey_rowshift` TINYINT(3) UNSIGNED NOT NULL DEFAULT  '0';
+INSERT INTO `sql_updates` (`timestamp`) VALUES (1440688342);