summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorThorbjørn Lindeijer <thorbjorn@lindeijer.nl>2012-03-12 22:18:22 +0100
committerThorbjørn Lindeijer <thorbjorn@lindeijer.nl>2012-03-13 21:53:25 +0100
commit1afbfb7e5fb5c133924ed8d376c6064575fc1c36 (patch)
treee5ec51d13fe20350a0d6a450bc92df4080f1691d
parent90fde5774f1e6ee1a3b649753fa7338e386a3c45 (diff)
downloadmanaserv-1afbfb7e5fb5c133924ed8d376c6064575fc1c36.tar.gz
manaserv-1afbfb7e5fb5c133924ed8d376c6064575fc1c36.tar.bz2
manaserv-1afbfb7e5fb5c133924ed8d376c6064575fc1c36.tar.xz
manaserv-1afbfb7e5fb5c133924ed8d376c6064575fc1c36.zip
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
-rw-r--r--src/account-server/main-account.cpp6
-rw-r--r--src/account-server/serverhandler.cpp6
-rw-r--r--src/account-server/storage.cpp38
-rw-r--r--src/account-server/storage.h37
-rw-r--r--src/common/manaserv_protocol.h2
-rw-r--r--src/sql/mysql/createTables.sql6
-rw-r--r--src/sql/mysql/updates/update_19_to_20.sql25
-rw-r--r--src/sql/postgresql/createTables.sql9
-rw-r--r--src/sql/sqlite/createTables.sql10
-rw-r--r--src/sql/sqlite/updates/update_19_to_20.sql59
10 files changed, 134 insertions, 64 deletions
diff --git a/src/account-server/main-account.cpp b/src/account-server/main-account.cpp
index b0373db9..44e65c41 100644
--- a/src/account-server/main-account.cpp
+++ b/src/account-server/main-account.cpp
@@ -369,9 +369,11 @@ int main(int argc, char *argv[])
const time_t startup = time(NULL);
std::stringstream timestamp;
timestamp << startup;
- storage->setWorldStateVar("accountserver_startup", timestamp.str());
+ storage->setWorldStateVar("accountserver_startup", timestamp.str(),
+ Storage::SystemMap);
const std::string revision = "$Revision$";
- storage->setWorldStateVar("accountserver_version", revision);
+ storage->setWorldStateVar("accountserver_version", revision,
+ Storage::SystemMap);
// -------------------------------------------------------------------------
while (running)
diff --git a/src/account-server/serverhandler.cpp b/src/account-server/serverhandler.cpp
index c5104b8f..9224338f 100644
--- a/src/account-server/serverhandler.cpp
+++ b/src/account-server/serverhandler.cpp
@@ -208,7 +208,7 @@ void ServerHandler::processMessage(NetComputer *comp, MessageIn &msg)
// transmit global world state variables
std::map<std::string, std::string> variables;
- variables = storage->getAllWorldStateVars(0);
+ variables = storage->getAllWorldStateVars(Storage::WorldMap);
for (std::map<std::string, std::string>::iterator i = variables.begin();
i != variables.end();
i++)
@@ -385,7 +385,7 @@ void ServerHandler::processMessage(NetComputer *comp, MessageIn &msg)
std::string name = msg.readString();
std::string value = msg.readString();
// save the new value to the database
- storage->setWorldStateVar(name, value);
+ storage->setWorldStateVar(name, value, Storage::WorldMap);
// relay the new value to all gameservers
for (ServerHandler::NetComputers::iterator i = clients.begin();
i != clients.end();
@@ -403,7 +403,7 @@ void ServerHandler::processMessage(NetComputer *comp, MessageIn &msg)
int mapid = msg.readInt32();
std::string name = msg.readString();
std::string value = msg.readString();
- storage->setWorldStateVar(name, mapid, value);
+ storage->setWorldStateVar(name, value, mapid);
} break;
case GAMSG_BAN_PLAYER:
diff --git a/src/account-server/storage.cpp b/src/account-server/storage.cpp
index ea3cee39..da516979 100644
--- a/src/account-server/storage.cpp
+++ b/src/account-server/storage.cpp
@@ -122,7 +122,7 @@ void Storage::open()
// Check database version here
int dbversion = utils::stringToInt(
- getWorldStateVar(DB_VERSION_PARAMETER));
+ getWorldStateVar(DB_VERSION_PARAMETER, SystemMap));
int supportedDbVersion = ManaServ::SUPPORTED_DB_VERSION;
if (dbversion != supportedDbVersion)
{
@@ -1666,14 +1666,8 @@ std::map<std::string, std::string> Storage::getAllWorldStateVars(int mapId)
}
void Storage::setWorldStateVar(const std::string &name,
- const std::string &value)
-{
- return setWorldStateVar(name, -1, value);
-}
-
-void Storage::setWorldStateVar(const std::string &name,
- int mapId,
- const std::string &value)
+ const std::string &value,
+ int mapId)
{
try
{
@@ -1682,12 +1676,8 @@ void Storage::setWorldStateVar(const std::string &name,
{
std::ostringstream deleteStateVar;
deleteStateVar << "DELETE FROM " << WORLD_STATES_TBL_NAME
- << " WHERE state_name = '" << name << "'";
-
- if (mapId >= 0)
- deleteStateVar << " AND map_id = '" << mapId << "'";
-
- deleteStateVar << ";";
+ << " WHERE state_name = '" << name << "'"
+ << " AND map_id = '" << mapId << "';";
mDb->execSql(deleteStateVar.str());
return;
}
@@ -1697,12 +1687,8 @@ void Storage::setWorldStateVar(const std::string &name,
updateStateVar << "UPDATE " << WORLD_STATES_TBL_NAME
<< " SET value = '" << value << "', "
<< " moddate = '" << time(0) << "' "
- << " WHERE state_name = '" << name << "'";
-
- if (mapId >= 0)
- updateStateVar << " AND map_id = '" << mapId << "'";
-
- updateStateVar << ";";
+ << " WHERE state_name = '" << name << "'"
+ << " AND map_id = '" << mapId << "';";
mDb->execSql(updateStateVar.str());
// If we updated a row, were finished here
@@ -1713,13 +1699,9 @@ void Storage::setWorldStateVar(const std::string &name,
std::ostringstream insertStateVar;
insertStateVar << "INSERT INTO " << WORLD_STATES_TBL_NAME
<< " (state_name, map_id, value , moddate) VALUES ("
- << "'" << name << "', ";
- if (mapId >= 0)
- insertStateVar << "'" << mapId << "', ";
- else
- insertStateVar << "0 , ";
-
- insertStateVar << "'" << value << "', "
+ << "'" << name << "', "
+ << "'" << mapId << "', "
+ << "'" << value << "', "
<< "'" << time(0) << "');";
mDb->execSql(insertStateVar.str());
}
diff --git a/src/account-server/storage.h b/src/account-server/storage.h
index 81351240..d6fb3ed8 100644
--- a/src/account-server/storage.h
+++ b/src/account-server/storage.h
@@ -344,35 +344,34 @@ class Storage
*/
void setQuestVar(int id, const std::string &, const std::string &);
- /**
- * Gets the string value of a map specific world state variable.
- *
- * @param name Name of the requested world-state variable.
- * @param map_id Id of the specific map.
- */
- std::string getWorldStateVar(const std::string &name, int mapId = -1);
+ enum SpecialMapId {
+ WorldMap = 0,
+ SystemMap = -1
+ };
/**
- * Sets the value of a world state variable.
+ * Gets the string value of a world state variable. The \a mapId should
+ * be a valid map ID or either WorldMap or SystemMap.
*
- * @param name Name of the world-state vairable.
- * @param value New value of the world-state variable.
+ * @param name Name of the requested world variable.
+ * @param mapId ID of the specific map.
*/
- void setWorldStateVar(const std::string &name,
- const std::string &value);
+ std::string getWorldStateVar(const std::string &name, int mapId);
/**
- * Sets the value of a world state variable of a specific map.
+ * Sets the value of a world state variable. The \a mapId should be a
+ * valid map ID or either WorldMap or SystemMap.
*
- * @param name Name of the world-state vairable.
- * @param mapId ID of the specific map
- * @param value New value of the world-state variable.
+ * @param name Name of the world vairable.
+ * @param value New value of the world variable.
*/
- void setWorldStateVar(const std::string &name, int mapId,
- const std::string &value);
+ void setWorldStateVar(const std::string &name,
+ const std::string &value,
+ int mapId);
/**
- * Gets the value of all world state variable of a specific map.
+ * Gets the value of all world state variables of a specific map. The
+ * \a mapId should be a valid map ID or either WorldMap or SystemMap.
*
* @param mapId ID of the specific map
*/
diff --git a/src/common/manaserv_protocol.h b/src/common/manaserv_protocol.h
index a78c4734..1ead90e9 100644
--- a/src/common/manaserv_protocol.h
+++ b/src/common/manaserv_protocol.h
@@ -30,7 +30,7 @@ namespace ManaServ {
enum {
PROTOCOL_VERSION = 1,
- SUPPORTED_DB_VERSION = 19
+ SUPPORTED_DB_VERSION = 20
};
/**
diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
index a48644b3..dc405d95 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 00000000..affdc99c
--- /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 278790a2..53526dc3 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 9a9dfe2e..a5822f37 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 00000000..2dea3eff
--- /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;