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.

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