From b3f0062ac62a75c87fcccc7cf4b271e4453ab0aa Mon Sep 17 00:00:00 2001 From: Andrei Karas Date: Sat, 5 Sep 2015 20:30:10 +0300 Subject: update sql files. --- sql-files/main.sql | 24 +++----------- sql-files/upgrades/2015-07-02--18-14.sql | 56 ++++++++++++++++++++++++++++++++ sql-files/upgrades/2015-07-08--13-08.sql | 6 ++++ sql-files/upgrades/2015-08-27--20-42.sql | 3 ++ 4 files changed, 69 insertions(+), 20 deletions(-) create mode 100644 sql-files/upgrades/2015-07-02--18-14.sql create mode 100644 sql-files/upgrades/2015-07-08--13-08.sql create mode 100644 sql-files/upgrades/2015-08-27--20-42.sql (limited to 'sql-files') 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`), @@ -494,16 +495,6 @@ CREATE TABLE IF NOT EXISTS `interlog` ( `log` VARCHAR(255) NOT NULL DEFAULT '' ) 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); -- cgit v1.2.3-60-g2f50