diff options
-rw-r--r-- | src/account-server/main-account.cpp | 6 | ||||
-rw-r--r-- | src/account-server/serverhandler.cpp | 6 | ||||
-rw-r--r-- | src/account-server/storage.cpp | 38 | ||||
-rw-r--r-- | src/account-server/storage.h | 37 | ||||
-rw-r--r-- | src/common/manaserv_protocol.h | 2 | ||||
-rw-r--r-- | src/sql/mysql/createTables.sql | 6 | ||||
-rw-r--r-- | src/sql/mysql/updates/update_19_to_20.sql | 25 | ||||
-rw-r--r-- | src/sql/postgresql/createTables.sql | 9 | ||||
-rw-r--r-- | src/sql/sqlite/createTables.sql | 10 | ||||
-rw-r--r-- | src/sql/sqlite/updates/update_19_to_20.sql | 59 |
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; |