diff options
author | Andreas Habel <mail@exceptionfault.de> | 2008-12-01 18:27:14 +0100 |
---|---|---|
committer | Andreas Habel <mail@exceptionfault.de> | 2008-12-01 18:27:14 +0100 |
commit | b3d6d3889390843374fe0b9184dd77c5fcb84e8b (patch) | |
tree | 1601f16d933b8c77b2021b44d659de5a23fd8f3f /src | |
parent | 82b412fa1d77c50689327d949029ff7f5d45fb6b (diff) | |
download | manaserv-b3d6d3889390843374fe0b9184dd77c5fcb84e8b.tar.gz manaserv-b3d6d3889390843374fe0b9184dd77c5fcb84e8b.tar.bz2 manaserv-b3d6d3889390843374fe0b9184dd77c5fcb84e8b.tar.xz manaserv-b3d6d3889390843374fe0b9184dd77c5fcb84e8b.zip |
Added new table to store online users. See mantis #553
This upgrade will be the first, we provide database installation scripts
and update scripts to upgrade from the previous version. For more details
about database upgrades see
http://wiki.themanaworld.org/index.php/Upgrade_Database and feel free to
comment.
Diffstat (limited to 'src')
-rw-r--r-- | src/account-server/dalstorage.cpp | 44 | ||||
-rw-r--r-- | src/account-server/dalstorage.hpp | 8 | ||||
-rw-r--r-- | src/account-server/dalstoragesql.hpp | 6 | ||||
-rw-r--r-- | src/account-server/serverhandler.cpp | 9 | ||||
-rw-r--r-- | src/defines.h | 1 | ||||
-rw-r--r-- | src/game-server/accountconnection.cpp | 16 | ||||
-rw-r--r-- | src/game-server/accountconnection.hpp | 9 | ||||
-rw-r--r-- | src/game-server/main-game.cpp | 8 | ||||
-rw-r--r-- | src/game-server/state.cpp | 9 | ||||
-rw-r--r-- | src/sql/mysql/createTables.sql | 37 | ||||
-rw-r--r-- | src/sql/mysql/updates/update_1_to_2.sql | 33 | ||||
-rw-r--r-- | src/sql/sqlite/createTables.sql | 30 | ||||
-rw-r--r-- | src/sql/sqlite/updates/update_1_to_2.sql | 30 |
13 files changed, 227 insertions, 13 deletions
diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp index 20815a7d..6547d84a 100644 --- a/src/account-server/dalstorage.cpp +++ b/src/account-server/dalstorage.cpp @@ -41,7 +41,7 @@ // defines the supported db version #define DB_VERSION_PARAMETER "database_version" -#define SUPPORTED_DB_VERSION "1" +#define SUPPORTED_DB_VERSION "2" /** @@ -95,6 +95,11 @@ void DALStorage::open() // synchronize base data from xml files SyncDatabase(); + + // clean list of online users, this should be empty after restart + std::ostringstream sql; + sql << "DELETE FROM " << ONLINE_USERS_TBL_NAME; + mDb->execSql(sql.str()); } catch (const DbConnectionFailure& e) { std::ostringstream errmsg; @@ -1585,3 +1590,40 @@ void DALStorage::SyncDatabase(void) mDb->commitTransaction(); xmlFreeDoc(doc); } + +void DALStorage::setOnlineStatus(int charId, bool online) +{ + try + { + std::ostringstream sql; + if (online) + { + // first we try to update the online status. this prevents errors + // in case we get the online status twice + sql << "SELECT COUNT(*) FROM " << ONLINE_USERS_TBL_NAME + << " WHERE char_id = " << charId; + const std::string res = mDb->execSql(sql.str())(0, 0); + + if (res != "0") + return; + + sql.clear(); + sql.str(""); + sql << "INSERT INTO " << ONLINE_USERS_TBL_NAME + << " VALUES (" << charId << ", " << time(NULL) << ")"; + mDb->execSql(sql.str()); + } + else + { + sql << "DELETE FROM " << ONLINE_USERS_TBL_NAME + << " WHERE char_id = " << charId; + mDb->execSql(sql.str()); + } + + + } + catch (dal::DbSqlQueryExecFailure const &e) + { + LOG_ERROR("(DALStorage::setOnlineStatus) SQL query failure: " << e.what()); + } +} diff --git a/src/account-server/dalstorage.hpp b/src/account-server/dalstorage.hpp index 896151d1..71c3dfa3 100644 --- a/src/account-server/dalstorage.hpp +++ b/src/account-server/dalstorage.hpp @@ -357,6 +357,14 @@ class DALStorage unsigned int getItemDatabaseVersion(void) const { return mItemDbVersion; } + /** + * Sets the status of a character to online (true) or offline (false). + * + * @param charId Id of the character. + * @param online True to mark the character as being online. + */ + void setOnlineStatus(int charId, bool online); + private: /** * Copy constructor. diff --git a/src/account-server/dalstoragesql.hpp b/src/account-server/dalstoragesql.hpp index 3e9bca38..4b9d27db 100644 --- a/src/account-server/dalstoragesql.hpp +++ b/src/account-server/dalstoragesql.hpp @@ -131,4 +131,10 @@ static char const *AUCTION_TBL_NAME = "tmw_auctions"; */ static char const *AUCTION_BIDS_TBL_NAME = "tmw_auction_bids"; +/** + * TABLE: tmw_online_list + * List currently online users. + */ +static char const *ONLINE_USERS_TBL_NAME = "tmw_online_list"; + #endif // _TMWSERV_DALSTORAGE_SQL_H_ diff --git a/src/account-server/serverhandler.cpp b/src/account-server/serverhandler.cpp index 1c4a5219..23f2cd32 100644 --- a/src/account-server/serverhandler.cpp +++ b/src/account-server/serverhandler.cpp @@ -544,6 +544,15 @@ void GameServerHandler::syncDatabase(MessageIn &msg) int SkillValue = msg.readLong(); storage->updateExperience(CharId, SkillId, SkillValue); } break; + + case SYNC_ONLINE_STATUS: + { + LOG_DEBUG("received SYNC_ONLINE_STATUS"); + int CharId = msg.readLong(); + bool online; + msg.readByte() == 0x00 ? online = false : online = true; + storage->setOnlineStatus(CharId, online); + } } // read next message type from buffer diff --git a/src/defines.h b/src/defines.h index a0499377..ea3aab57 100644 --- a/src/defines.h +++ b/src/defines.h @@ -313,6 +313,7 @@ enum { enum { SYNC_CHARACTER_POINTS = 0x01, // L charId, L charPoints, L corrPoints, B attribute id, L attribute value SYNC_CHARACTER_SKILL = 0x02, // L charId, B skillId, L skill value + SYNC_ONLINE_STATUS = 0x03, // L charId, B 0x00 = offline, 0x01 = online SYNC_END_OF_BUFFER = 0xFF // shows, that the buffer ends here. }; diff --git a/src/game-server/accountconnection.cpp b/src/game-server/accountconnection.cpp index bbdcdd13..6d6276d8 100644 --- a/src/game-server/accountconnection.cpp +++ b/src/game-server/accountconnection.cpp @@ -362,3 +362,19 @@ void AccountConnection::updateExperience(const int CharId, const int SkillId, mSyncBuffer->writeLong(SkillValue); syncChanges(); } + +void AccountConnection::updateOnlineStatus(const int CharId, const bool Online) +{ + mSyncMessages++; + mSyncBuffer->writeByte(SYNC_ONLINE_STATUS); + mSyncBuffer->writeLong(CharId); + if (Online) + { + mSyncBuffer->writeByte(0x01); + } + else + { + mSyncBuffer->writeByte(0x00); + } + syncChanges(); +} diff --git a/src/game-server/accountconnection.hpp b/src/game-server/accountconnection.hpp index 22c422a7..5b21c0ac 100644 --- a/src/game-server/accountconnection.hpp +++ b/src/game-server/accountconnection.hpp @@ -129,7 +129,6 @@ class AccountConnection : public Connection const int CorrPoints, const int AttribId, const int AttribValue); - /** * Write a modification message about character skills to the sync buffer. * @param CharId ID of the character @@ -139,6 +138,14 @@ class AccountConnection : public Connection void updateExperience(const int CharId, const int SkillId, const int SkillValue); + /** + * Update the status of a character to online (true) or offline (false). + * + * @param CharId Id of the character. + * @param Online True to flag the character as being online. + */ + void updateOnlineStatus(const int CharId, const bool Online); + protected: /** * Processes server messages. diff --git a/src/game-server/main-game.cpp b/src/game-server/main-game.cpp index 93c2f1fa..86dc7e22 100644 --- a/src/game-server/main-game.cpp +++ b/src/game-server/main-game.cpp @@ -310,8 +310,7 @@ int main(int argc, char *argv[]) // Print world time at 10 second intervals to show we're alive if (worldTime % 100 == 0) { LOG_INFO("World time: " << worldTime); - // force sending changes to the account serber every 10 secs. - accountHandler->syncChanges(true); + } if (accountHandler->isConnected()) @@ -319,6 +318,11 @@ int main(int argc, char *argv[]) // Handle all messages that are in the message queues accountHandler->process(); + if (worldTime % 100 == 0) { + accountHandler->syncChanges(true); + // force sending changes to the account server every 10 secs. + } + if (worldTime % 300 == 0) { accountHandler->sendStatistics(); diff --git a/src/game-server/state.cpp b/src/game-server/state.cpp index 3b6dd624..a42c689f 100644 --- a/src/game-server/state.cpp +++ b/src/game-server/state.cpp @@ -558,6 +558,11 @@ bool GameState::insert(Thing *ptr) mapChangeMessage.writeShort(pos.x); mapChangeMessage.writeShort(pos.y); gameHandler->sendTo(static_cast< Character * >(obj), mapChangeMessage); + + // update the online state of the character + accountHandler->updateOnlineStatus( + static_cast< Character * >(obj)->getDatabaseID(), true); + return true; } @@ -580,6 +585,10 @@ void GameState::remove(Thing *ptr) if (ptr->getType() == OBJECT_CHARACTER) { static_cast< Character * >(ptr)->cancelTransaction(); + + // remove characters online status + accountHandler->updateOnlineStatus( + static_cast< Character * >(ptr)->getDatabaseID(), false); } MovingObject *obj = static_cast< MovingObject * >(ptr); diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql index 9eda05a9..5d412095 100644 --- a/src/sql/mysql/createTables.sql +++ b/src/sql/mysql/createTables.sql @@ -156,9 +156,9 @@ CREATE TABLE IF NOT EXISTS `tmw_world_states` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL,1226042339); -INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL,1226042339); -INSERT INTO tmw_world_states VALUES('database_version', NULL,'1', 1226042339); +INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL,UNIX_TIMESTAMP()); +INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL,UNIX_TIMESTAMP()); +INSERT INTO tmw_world_states VALUES('database_version', NULL,'2', UNIX_TIMESTAMP()); -- -- table: `tmw_guilds` @@ -286,6 +286,7 @@ AUTO_INCREMENT=1 ; -- -- table: `tmw_post_attachements` -- + CREATE TABLE IF NOT EXISTS `tmw_post_attachments` ( `attachment_id` int(10) unsigned NOT NULL auto_increment, `letter_id` int(10) unsigned NOT NULL, @@ -305,3 +306,33 @@ CREATE TABLE IF NOT EXISTS `tmw_post_attachments` ( DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; +-- +-- table: `tmw_online_list` +-- + +CREATE TABLE IF NOT EXISTS `tmw_online_list` ( + `char_id` int(10) unsigned NOT NULL, + `login_date` int(10) NOT NULL, + -- + PRIMARY KEY (`char_id`), + FOREIGN KEY (`char_id`) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8 ; + +-- create a view to show more details about online users +CREATE VIEW tmw_v_online_chars +AS + SELECT l.char_id as char_id, + l.login_date as login_date, + c.user_id as user_id, + c.name as name, + c.gender as gender, + c.level as level, + c.map_id as map_id + FROM tmw_online_list l + JOIN tmw_characters c + ON l.char_id = c.id; + +
\ No newline at end of file diff --git a/src/sql/mysql/updates/update_1_to_2.sql b/src/sql/mysql/updates/update_1_to_2.sql new file mode 100644 index 00000000..76ba63df --- /dev/null +++ b/src/sql/mysql/updates/update_1_to_2.sql @@ -0,0 +1,33 @@ + +-- add table tmw_online_list to store online users +CREATE TABLE IF NOT EXISTS `tmw_online_list` ( + `char_id` int(10) unsigned NOT NULL, + `login_date` int(10) NOT NULL, + -- + PRIMARY KEY (`char_id`), + FOREIGN KEY (`char_id`) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8 ; + +-- create a view to show more details about online users +CREATE VIEW tmw_v_online_chars +AS + SELECT l.char_id as char_id, + l.login_date as login_date, + c.user_id as user_id, + c.name as name, + c.gender as gender, + c.level as level, + c.map_id as map_id + FROM tmw_online_list l + JOIN tmw_characters c + ON l.char_id = c.id; + +-- update the database version, and set date of update +UPDATE tmw_world_states + SET value = '2', + moddate = UNIX_TIMESTAMP() + WHERE state_name = 'database_version'; +
\ No newline at end of file diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql index d22ba99b..68d4ed58 100644 --- a/src/sql/sqlite/createTables.sql +++ b/src/sql/sqlite/createTables.sql @@ -140,9 +140,9 @@ CREATE TABLE tmw_world_states moddate INTEGER NOT NULL ); -INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL,1226042339); -INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL,1226042339); -INSERT INTO tmw_world_states VALUES('database_version', NULL,'1', 1226042339); +INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL, strftime('%s','now')); +INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL, strftime('%s','now')); +INSERT INTO tmw_world_states VALUES('database_version', NULL,'2', strftime('%s','now')); CREATE TABLE tmw_auctions ( @@ -198,9 +198,6 @@ CREATE TABLE tmw_post CREATE INDEX tmw_post_sender ON tmw_post ( sender_id ); CREATE INDEX tmw_post_receiver ON tmw_post ( receiver_id ); --- --- table: `tmw_post_attachements` --- CREATE TABLE tmw_post_attachments ( @@ -215,3 +212,24 @@ CREATE TABLE tmw_post_attachments CREATE INDEX tmw_post_attachments_ltr ON tmw_post_attachments ( letter_id ); CREATE INDEX tmw_post_attachments_itm ON tmw_post_attachments ( item_id ); + +CREATE TABLE tmw_online_list +( + char_id INTEGER PRIMARY KEY, + login_date INTEGER NOT NULL, + -- + FOREIGN KEY (char_id) REFERENCES tmw_characters(id) +); + +CREATE VIEW tmw_v_online_chars +AS + SELECT l.char_id as char_id, + l.login_date as login_date, + c.user_id as user_id, + c.name as name, + c.gender as gender, + c.level as level, + c.map_id as map_id + FROM tmw_online_list l + JOIN tmw_characters c + ON l.char_id = c.id; diff --git a/src/sql/sqlite/updates/update_1_to_2.sql b/src/sql/sqlite/updates/update_1_to_2.sql new file mode 100644 index 00000000..8187cd34 --- /dev/null +++ b/src/sql/sqlite/updates/update_1_to_2.sql @@ -0,0 +1,30 @@ + +-- add table tmw_online_list to store online users +CREATE TABLE tmw_online_list +( + char_id INTEGER PRIMARY KEY, + login_date INTEGER NOT NULL, + -- + FOREIGN KEY (char_id) REFERENCES tmw_characters(id) +); + +-- create a view to show more details about online users +CREATE VIEW tmw_v_online_chars +AS + SELECT l.char_id as char_id, + l.login_date as login_date, + c.user_id as user_id, + c.name as name, + c.gender as gender, + c.level as level, + c.map_id as map_id + FROM tmw_online_list l + JOIN tmw_characters c + ON l.char_id = c.id; + +-- update the database version, and set date of update +UPDATE tmw_world_states + SET value = '2', + moddate = strftime('%s','now') + WHERE state_name = 'database_version'; +
\ No newline at end of file |