Update SQL database

 * Add separate tables for global integer and string variables.
 * Copy data from mapreg table to new map_reg_*_db tables.
 * Remove mapreg table.

3 files changed, 61 insertions, 5 deletions

diff --git a/sql-files/main.sql b/sql-files/main.sql
index d5b7735fc..7aebd73fd 100644
--- a/sql-files/main.sql
+++ b/sql-files/main.sql
@@ -655,14 +655,25 @@ CREATE TABLE IF NOT EXISTS `login` (
 INSERT IGNORE INTO `login` (`account_id`, `userid`, `user_pass`, `sex`, `email`) VALUES ('1', 's1', 'p1', 'S','athena@athena.com');
 
 --
--- Table structure for table `mapreg`
+-- Table structure for table `map_reg_num_db`
 --
 
-CREATE TABLE IF NOT EXISTS `mapreg` (
-  `varname` VARCHAR(32) BINARY NOT NULL,
+CREATE TABLE IF NOT EXISTS `map_reg_num_db` (
+  `key` VARCHAR(32) BINARY NOT NULL DEFAULT '',
+  `index` INT UNSIGNED NOT NULL DEFAULT '0',
+  `value` INT NOT NULL DEFAULT '0',
+  PRIMARY KEY (`key`, `index`)
+) ENGINE=MyISAM;
+
+--
+-- Table structure for table `map_reg_str_db`
+--
+
+CREATE TABLE IF NOT EXISTS `map_reg_str_db` (
+  `key` VARCHAR(32) BINARY NOT NULL DEFAULT '',
   `index` INT UNSIGNED NOT NULL DEFAULT '0',
-  `value` VARCHAR(255) NOT NULL,
-  PRIMARY KEY (`varname`,`index`)
+  `value` VARCHAR(255) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`key`, `index`)
 ) ENGINE=MyISAM;
 
 --
@@ -939,6 +950,7 @@ INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1579817630); -- 2020-01-2
 INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1584838560); -- 2020-03-22--01-56.sql
 INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1584842940); -- 2020-03-22--03-09.sql
 INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1588301040); -- 2020-05-01--04-44.sql
+INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1589145060); -- 2020-05-10--23-11.sql
 
 --
 -- Table structure for table `storage`
diff --git a/sql-files/upgrades/2020-05-10--23-11.sql b/sql-files/upgrades/2020-05-10--23-11.sql
new file mode 100644
index 000000000..9f2755e76
--- /dev/null
+++ b/sql-files/upgrades/2020-05-10--23-11.sql
@@ -0,0 +1,43 @@
+#1589145060
+
+-- This file is part of Hercules.
+-- http://herc.ws - http://github.com/HerculesWS/Hercules
+--
+-- Copyright (C) 2019-2020 Hercules Dev Team
+--
+-- Hercules is free software: you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation, either version 3 of the License, or
+-- (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+-- Add separate tables for global integer and string variables.
+CREATE TABLE IF NOT EXISTS `map_reg_num_db` (
+  `key` VARCHAR(32) BINARY NOT NULL DEFAULT '',
+  `index` INT UNSIGNED NOT NULL DEFAULT '0',
+  `value` INT NOT NULL DEFAULT '0',
+  PRIMARY KEY (`key`, `index`)
+) ENGINE=MyISAM;
+CREATE TABLE IF NOT EXISTS `map_reg_str_db` (
+  `key` VARCHAR(32) BINARY NOT NULL DEFAULT '',
+  `index` INT UNSIGNED NOT NULL DEFAULT '0',
+  `value` VARCHAR(255) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`key`, `index`)
+) ENGINE=MyISAM;
+
+-- Copy data from mapreg table to new map_reg_*_db tables.
+INSERT INTO `map_reg_num_db` (`key`, `index`, `value`) SELECT `varname`, `index`, CAST(`value` AS SIGNED) FROM `mapreg` WHERE NOT RIGHT(`varname`, 1)='$';
+INSERT INTO `map_reg_str_db` (`key`, `index`, `value`) SELECT `varname`, `index`, `value` FROM `mapreg` WHERE RIGHT(`varname`, 1)='$';
+
+-- Remove mapreg table.
+DROP TABLE IF EXISTS `mapreg`;
+
+-- Add update timestamp.
+INSERT INTO `sql_updates` (`timestamp`) VALUES (1589145060);
diff --git a/sql-files/upgrades/index.txt b/sql-files/upgrades/index.txt
index 64a7793f8..fb889c280 100644
--- a/sql-files/upgrades/index.txt
+++ b/sql-files/upgrades/index.txt
@@ -63,3 +63,4 @@
 2020-03-22--01-56.sql
 2020-03-22--03-09.sql
 2020-05-01--04-44.sql
+2020-05-10--23-11.sql