Fixed problems with map-bound world state variables

Due to a wrong primary key, which covered only the state name, it was
impossible to use the same state name on different maps. This has now
been fixed.

Another problem was that the map variables were being included in the
global variables, because the related database query did not filter on
the map_id column properly. While fixing that, the map_id column now
allows explicitly marking a state variable as global (with the value 0)
or system variables (with the value -1).

System variables are currently not accessible from scripts, but that
could be changed later.

Reviewed-by: Yohann Ferreira
Reviewed-by: Erik Schilling

5 files changed, 98 insertions, 11 deletions

diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
index a48644b..dc405d9 100644
--- a/src/sql/mysql/createTables.sql
+++ b/src/sql/mysql/createTables.sql
@@ -278,10 +278,10 @@ DEFAULT CHARSET=utf8;
 CREATE TABLE IF NOT EXISTS mana_world_states
 (
    state_name   varchar(100)NOT NULL,
-   map_id       INTEGER     NULL,
-   value        TEXT        NULL,
+   map_id       INTEGER     NOT NULL,
+   value        TEXT        NOT NULL,
    moddate      INTEGER     NOT NULL,
-   PRIMARY KEY (`state_name`)
+   PRIMARY KEY (`state_name`, `map_id`)
 );
 
 --
diff --git a/src/sql/mysql/updates/update_19_to_20.sql b/src/sql/mysql/updates/update_19_to_20.sql
new file mode 100644
index 0000000..affdc99
--- /dev/null
+++ b/src/sql/mysql/updates/update_19_to_20.sql
@@ -0,0 +1,25 @@
+BEGIN;
+
+-- Set existing world variables to the world map (0)
+UPDATE mana_world_states SET map_id = 0 WHERE map_id IS NULL;
+
+-- Set known system variables to the system map (-1)
+UPDATE mana_world_states SET map_id = -1 WHERE
+    state_name = 'database_version' OR
+    state_name = 'accountserver_version' OR
+    state_name = 'accountserver_startup';
+
+-- Fix up the world states table to disallow map_id to be NULL, and to have
+-- the correct primary key.
+ALTER TABLE mana_world_states MODIFY map_id INTEGER NOT NULL;
+ALTER TABLE mana_world_states MODIFY value TEXT NOT NULL;
+ALTER TABLE mana_world_states DROP PRIMARY KEY;
+ALTER TABLE mana_world_states ADD PRIMARY KEY (state_name, map_id);
+
+-- Update database version.
+UPDATE mana_world_states
+    SET value = '20',
+        moddate = UNIX_TIMESTAMP()
+    WHERE state_name = 'database_version';
+
+END;
diff --git a/src/sql/postgresql/createTables.sql b/src/sql/postgresql/createTables.sql
index 278790a..53526dc 100644
--- a/src/sql/postgresql/createTables.sql
+++ b/src/sql/postgresql/createTables.sql
@@ -110,10 +110,11 @@ CREATE TABLE mana_quests
 
 CREATE TABLE mana_world_states
 (
-   state_name   TEXT        PRIMARY KEY,
-   map_id       INTEGER     NULL,
-   value        TEXT        NULL,
-   moddate      INTEGER     NOT NULL
+   state_name   TEXT        NOT NULL,
+   map_id       INTEGER     NOT NULL,
+   value        TEXT        NOT NULL,
+   moddate      INTEGER     NOT NULL,
+   PRIMARY KEY (`state_name`, `map_id`)
 );
 
 INSERT INTO "mana_world_states" VALUES('accountserver_startup',NULL,NULL,1221633910);
diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql
index 9a9dfe2..a5822f3 100644
--- a/src/sql/sqlite/createTables.sql
+++ b/src/sql/sqlite/createTables.sql
@@ -250,10 +250,12 @@ CREATE TABLE mana_quests
 
 CREATE TABLE mana_world_states
 (
-   state_name   TEXT        PRIMARY KEY,
-   map_id       INTEGER     NULL,
-   value        TEXT        NULL,
-   moddate      INTEGER     NOT NULL
+   state_name   TEXT        NOT NULL,
+   map_id       INTEGER     NOT NULL,
+   value        TEXT        NOT NULL,
+   moddate      INTEGER     NOT NULL,
+   --
+   PRIMARY KEY (state_name, map_id)
 );
 
 -----------------------------------------------------------------------------
diff --git a/src/sql/sqlite/updates/update_19_to_20.sql b/src/sql/sqlite/updates/update_19_to_20.sql
new file mode 100644
index 0000000..2dea3ef
--- /dev/null
+++ b/src/sql/sqlite/updates/update_19_to_20.sql
@@ -0,0 +1,59 @@
+--
+-- SQLite does not support altering of columns, so we'll need to recreate the
+-- table and copy the data over.
+--
+
+BEGIN;
+
+CREATE TEMPORARY TABLE mana_world_states_backup
+(
+   state_name   TEXT        PRIMARY KEY,
+   map_id       INTEGER     NULL,
+   value        TEXT        NULL,
+   moddate      INTEGER     NOT NULL
+);
+
+INSERT INTO mana_world_states_backup SELECT
+    state_name, map_id, value, moddate FROM mana_world_states;
+
+DROP TABLE mana_world_states;
+
+-- Create the new world states table with the corrected primary key, and move
+-- the existing data over
+CREATE TABLE mana_world_states
+(
+   state_name   TEXT        NOT NULL,
+   map_id       INTEGER     NOT NULL,
+   value        TEXT        NOT NULL,
+   moddate      INTEGER     NOT NULL,
+   --
+   PRIMARY KEY (state_name, map_id)
+);
+
+-- Copy over all map states
+INSERT INTO mana_world_states (state_name, map_id, value, moddate)
+    SELECT state_name, map_id, value, moddate
+      FROM mana_world_states_backup
+      WHERE map_id > 0;
+
+-- Copy over all world states
+INSERT INTO mana_world_states (state_name, map_id, value, moddate)
+    SELECT state_name, 0, value, moddate
+      FROM mana_world_states_backup
+      WHERE map_id ISNULL;
+
+-- Move some known system variables into the system scope
+UPDATE mana_world_states SET map_id = -1 WHERE
+    state_name = 'database_version' OR
+    state_name = 'accountserver_version' OR
+    state_name = 'accountserver_startup';
+
+DROP TABLE mana_world_states_backup;
+
+-- Update the database version, and set date of update
+UPDATE mana_world_states
+   SET value      = '20',
+       moddate    = strftime('%s','now')
+   WHERE state_name = 'database_version';
+
+END;