* Extended tmw_accounts table with columns for lastlogin and registration date. Modified account-server to fill the new columns on registration and login. Recreation of database needed!

* Added createIndex function to create indexes on tables.

6 files changed, 165 insertions, 23 deletions

diff --git a/src/account-server/account.cpp b/src/account-server/account.cpp
index c37be3a7..289ad8b5 100644
--- a/src/account-server/account.cpp
+++ b/src/account-server/account.cpp
@@ -65,3 +65,13 @@ void Account::setID(int id)
     assert(mID < 0);
     mID = id;
 }
+
+void Account::setRegistrationDate(time_t time)
+{
+    mRegistrationDate = time;
+};
+
+void Account::setLastLogin(time_t time)
+{
+    mLastLogin = time;
+};
diff --git a/src/account-server/account.hpp b/src/account-server/account.hpp
index 59f25f75..3099911a 100644
--- a/src/account-server/account.hpp
+++ b/src/account-server/account.hpp
@@ -25,6 +25,7 @@
 
 #include <string>
 #include <vector>
+#include <time.h>
 
 #include "account-server/character.hpp"
 
@@ -170,14 +171,40 @@ class Account
          *
          * @return the unique ID of the account, a negative number if none yet.
          */
-         int getID() const
-         { return mID; }
+        int getID() const
+        { return mID; }
 
         /**
          * Set account ID.
          * The account shall not have any ID yet.
          */
-         void setID(int);
+        void setID(int);
+
+        /**
+         * Get the time of the account registration.
+         */
+        time_t getRegistrationDate() const
+        { return mRegistrationDate; }
+
+        /**
+         * Sets the time of the account registration.
+         *
+         * @param time of the account registration.
+         */
+        void setRegistrationDate(time_t time);
+
+        /**
+         * Get the time of the last login.
+         */
+        time_t getLastLogin() const
+        { return mLastLogin; }
+
+        /**
+         * Sets the time of the last login.
+         *
+         * @param time of the last login.
+         */
+        void setLastLogin(time_t time);
 
     private:
         Account(Account const &rhs);
@@ -185,12 +212,14 @@ class Account
 
 
     private:
-        std::string mName;      /**< User name */
-        std::string mPassword;  /**< User password (hashed with salt) */
-        std::string mEmail;     /**< User email address (hashed) */
-        Characters mCharacters; /**< Character data */
-        int mID;                /**< Unique id */
-        unsigned char mLevel;   /**< Account level */
+        std::string mName;        /**< User name */
+        std::string mPassword;    /**< User password (hashed with salt) */
+        std::string mEmail;       /**< User email address (hashed) */
+        Characters mCharacters;   /**< Character data */
+        int mID;                  /**< Unique id */
+        unsigned char mLevel;     /**< Account level */
+        time_t mRegistrationDate; /**< Date and time of the account registration */
+        time_t mLastLogin;        /**< Date and time of the last login */
 };
 
 typedef std::vector< Account * > Accounts;
diff --git a/src/account-server/accounthandler.cpp b/src/account-server/accounthandler.cpp
index 649aa267..56c03b31 100644
--- a/src/account-server/accounthandler.cpp
+++ b/src/account-server/accounthandler.cpp
@@ -207,6 +207,13 @@ static void handleLoginMessage(AccountClient &computer, MessageIn &msg)
         return;
     }
 
+    // set lastLogin date of the account
+    time_t login;
+    time(&login);
+    acc->setLastLogin(login);
+    storage->updateLastLogin(acc);
+
+
     // Associate account with connection
     computer.setAccount(acc);
     computer.status = CLIENT_CONNECTED;
@@ -330,6 +337,12 @@ static void handleRegisterMessage(AccountClient &computer, MessageIn &msg)
         acc->setEmail(sha256(email));
         acc->setLevel(AL_NORMAL);
 
+        // set the date and time of the account registration, and the last login
+        time_t regdate;
+        time(&regdate);
+        acc->setRegistrationDate(regdate);
+        acc->setLastLogin(regdate);
+
         storage->addAccount(acc);
         reply.writeByte(ERRMSG_OK);
 
diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp
index 991acf19..26f99a12 100644
--- a/src/account-server/dalstorage.cpp
+++ b/src/account-server/dalstorage.cpp
@@ -130,6 +130,13 @@ void DALStorage::open()
         createTable(GUILDS_TBL_NAME, SQL_GUILDS_TABLE);
         createTable(GUILD_MEMBERS_TBL_NAME, SQL_GUILD_MEMBERS_TABLE);
         createTable(QUESTS_TBL_NAME, SQL_QUESTS_TABLE);
+
+        // TODO: this is not the prefered way, but currently the complete
+        // generation and maintenance of the database is a little dirty so
+        // keep this as is until there is a complete cleaner solution
+        const std::string idxName("tmw_accounts_username");
+        const std::string colName("username");
+        createIndex(idxName, ACCOUNTS_TBL_NAME, colName);
     }
     catch (const DbConnectionFailure& e) {
         LOG_ERROR("(DALStorage::open #1) Unable to connect to the database: "
@@ -184,6 +191,9 @@ Account *DALStorage::getAccountBySQL(std::string const &query)
             return account;
         }
         account->setLevel(level);
+        account->setRegistrationDate(toUint(accountInfo(0, 6)));
+        account->setLastLogin(toUint(accountInfo(0, 7)));
+
 
         // load the characters associated with the account.
         std::ostringstream sql;
@@ -659,6 +669,47 @@ DALStorage::createTable(const std::string& tblName,
     }
 }
 
+/**
+ * Create a index on the table
+ */
+void
+DALStorage::createIndex(const std::string& indxName,
+            const std::string& tblName,
+            const std::string& columnName )
+{
+#if defined (MYSQL_SUPPORT)
+#error MYSQL SUPPORT not complete implemented yet!
+
+#elif defined (SQLITE_SUPPORT)
+    std::ostringstream mSQL;
+    mSQL << "CREATE INDEX " << indxName << " ON " << tblName;
+    mSQL << " ( " << columnName << " );";
+
+    std::ostringstream mExists;
+    mExists << "index " << indxName << " already exists";
+#elif defined (POSTGRESQL_SUPPORT)
+
+#error POSTGRESQL SUPPORT not complete implemented yet!
+#endif
+
+    try {
+        mDb->execSql(mSQL.str());
+    }
+    catch (const dal::DbSqlQueryExecFailure& e)
+    {
+        const std::string msg(e.what());
+        if(msg == mExists.str())
+        {
+            LOG_DEBUG(mExists.str());
+        }
+        else
+        {
+            throw;
+        }
+    }
+} // end of createIndex
+
+
 
 /**
  * Add an account to the database.
@@ -672,15 +723,18 @@ void DALStorage::addAccount(Account *account)
     // TODO: we should start a transaction here so that in case of problem
     // the lost of data would be minimized.
 
+
     // insert the account.
     std::ostringstream sql1;
     sql1 << "insert into " << ACCOUNTS_TBL_NAME
-         << " (username, password, email, level, banned)"
+         << " (username, password, email, level, banned, registration, lastlogin)"
          << " values (\""
          << account->getName() << "\", \""
          << account->getPassword() << "\", \""
          << account->getEmail() << "\", "
-         << account->getLevel() << ", 0);";
+         << account->getLevel() << ", 0, "
+         << account->getRegistrationDate() << ", "
+         << account->getLastLogin() << ");";
     mDb->execSql(sql1.str());
 
     // get the account id.
@@ -708,10 +762,11 @@ void DALStorage::flush(Account *account)
     // update the account.
     std::ostringstream sqlUpdateAccountTable;
     sqlUpdateAccountTable << "update " << ACCOUNTS_TBL_NAME
-         << " set username = \"" << account->getName() << "\", "
-         << "password = \"" << account->getPassword() << "\", "
-         << "email = \"" << account->getEmail() << "\", "
-         << "level = '" << account->getLevel() << "' "
+         << " set username = '" << account->getName() << "', "
+         << "password = '" << account->getPassword() << "', "
+         << "email = '" << account->getEmail() << "', "
+         << "level = '" << account->getLevel() << "', "
+         << "lastlogin = '" << account->getLastLogin() << "' "
          << "where id = '" << account->getID() << "';";
     mDb->execSql(sqlUpdateAccountTable.str());
 
@@ -868,6 +923,18 @@ void DALStorage::delAccount(Account *account)
 }
 
 /**
+ * Update the date and time of the last login.
+ */
+void DALStorage::updateLastLogin(const Account *account)
+{
+    std::ostringstream sql;
+    sql << "UPDATE " << ACCOUNTS_TBL_NAME
+        << "   SET lastlogin = '" << account->getLastLogin() << "'"
+        << " WHERE id = '" << account->getID() << "';";
+    mDb->execSql(sql.str());
+}
+
+/**
  * Add a guild
  */
 void DALStorage::addGuild(Guild* guild)
diff --git a/src/account-server/dalstorage.hpp b/src/account-server/dalstorage.hpp
index af3546f2..7a8208ff 100644
--- a/src/account-server/dalstorage.hpp
+++ b/src/account-server/dalstorage.hpp
@@ -109,7 +109,6 @@ class DALStorage
         void
         addAccount(Account *account);
 
-
         /**
          * Delete an account.
          *
@@ -118,6 +117,13 @@ class DALStorage
         void delAccount(Account *account);
 
         /**
+         * Update the date and time of the last login.
+         *
+         * @param account the account that recently logged in.
+         */
+        void updateLastLogin(const Account *account);
+
+        /**
          * Sets a ban on an account (hence on all its characters).
          *
          * @param id character identifier.
@@ -246,6 +252,20 @@ class DALStorage
 
 
         /**
+         * Create an index on the specified column.
+         *
+         * @param indxName the name of the index.
+         * @param tblName the name of the table.
+         * @param columnName the name of the columns
+         *
+         * @exception dal::DbSqlQueryExecFailure.
+         */
+        void
+        createIndex(const std::string& indxName,
+                    const std::string& tblName,
+                    const std::string& columnName );
+
+        /**
          * Gets an account by using a SQL query string.
          *
          * @param query the query for the account
diff --git a/src/account-server/dalstoragesql.hpp b/src/account-server/dalstoragesql.hpp
index e485f6f9..311546ec 100644
--- a/src/account-server/dalstoragesql.hpp
+++ b/src/account-server/dalstoragesql.hpp
@@ -67,7 +67,8 @@
  */
 static char const *ACCOUNTS_TBL_NAME = "tmw_accounts";
 static char const *SQL_ACCOUNTS_TABLE =
-    "CREATE TABLE tmw_accounts ("
+    "CREATE TABLE tmw_accounts \n "
+    "( \n"
 #if defined (MYSQL_SUPPORT)
         "id       INTEGER     PRIMARY KEY AUTO_INCREMENT,"
         "username VARCHAR(32) NOT NULL UNIQUE,"
@@ -79,12 +80,14 @@ static char const *SQL_ACCOUNTS_TABLE =
         "INDEX (id)"
 #error "Incorrect definition. Please fix the types."
 #elif defined (SQLITE_SUPPORT)
-        "id       INTEGER     PRIMARY KEY,"
-        "username TEXT        NOT NULL UNIQUE,"
-        "password TEXT        NOT NULL,"
-        "email    TEXT        NOT NULL,"
-        "level    INTEGER     NOT NULL,"
-        "banned   INTEGER     NOT NULL"
+        "id             INTEGER     PRIMARY KEY, \n"
+        "username       TEXT        NOT NULL UNIQUE, \n"
+        "password       TEXT        NOT NULL, \n"
+        "email          TEXT        NOT NULL, \n"
+        "level          INTEGER     NOT NULL, \n"
+        "banned         INTEGER     NOT NULL, \n"
+        "registration   INTEGER     NOT NULL, \n"
+        "lastlogin      INTEGER     NOT NULL  \n"
 //        "activation TEXT"
 #elif defined (POSTGRESQL_SUPPORT)
         "id       SERIAL      PRIMARY KEY,"