* Added installation scripts to set up database schemas for mysql, sqlite and postgresql. The create table statements have been completely removed out from the c++ source into separate, provider specific sql files. Accountserver will no longer create a sqlite file if none present.

* Added database specific config parameters to configure each provider independent.
* Simplified the connect routine of DALStorage class since every dataprovider is now responsible to retrieve its own parameters.
* Extended abstract dataprovider to support transactions, functionally implemented for SQLite and mySQL. 
* Added methods to retrieve last inserted auto-increment value and the number of modified rows by the last statement.
* Rewrite of DALStorage class to be a little more transactional.
* Fixed a bug when deleting a character. Old function left data in quests table and guilds table. 
* Doxygen now also includes non-documented functions and provides a dictionary for all classes

5 files changed, 518 insertions, 41 deletions

diff --git a/src/dal/dataprovider.h b/src/dal/dataprovider.h
index 07d29b4..2c0a9de 100644
--- a/src/dal/dataprovider.h
+++ b/src/dal/dataprovider.h
@@ -26,6 +26,7 @@
 
 
 #include <string>
+#include <stdexcept>
 
 #include "recordset.h"
 
@@ -96,16 +97,12 @@ class DataProvider
         /**
          * Create a connection to the database.
          *
-         * @param dbName the database name.
-         * @param userName the user name.
-         * @param password the user password.
+         * Each dataprovider is responsible to have default values and load
+         * necessary options from the config file.
          *
          * @exception DbConnectionFailure if unsuccessful connection.
          */
-        virtual void
-        connect(const std::string& dbName,
-                const std::string& userName,
-                const std::string& password) = 0;
+        virtual void connect(void) = 0;
 
 
         /**
@@ -138,7 +135,50 @@ class DataProvider
         std::string
         getDbName(void);
 
+        /**
+         * Starts a transaction.
+         *
+         * @exception std::runtime_error if a transaction is still open
+         */
+        virtual void
+        beginTransaction(void)
+            throw (std::runtime_error) = 0;
+
+        /**
+         * Commits a transaction.
+         *
+         * @exception std::runtime_error if no connection is currently open.
+         */
+        virtual void
+        commitTransaction(void)
+            throw (std::runtime_error) = 0;
+
+        /**
+         * Rollback a transaction.
+         *
+         * @exception std::runtime_error if no connection is currently open.
+         */
+        virtual void
+        rollbackTransaction(void)
+            throw (std::runtime_error) = 0;
 
+        /**
+         * Returns the number of changed rows by the last executed SQL
+         * statement.
+         *
+         * @return Number of rows that have changed.
+         */
+        virtual const unsigned int
+        getModifiedRows(void) const = 0;
+
+        /**
+         * Returns the last inserted value of an autoincrement column after an
+         * INSERT statement.
+         *
+         * @return last autoincrement value.
+         */
+        virtual const unsigned int
+        getLastId(void) const = 0;
 
     protected:
         std::string mDbName;  /**< the database name */
diff --git a/src/dal/mysqldataprovider.cpp b/src/dal/mysqldataprovider.cpp
index 85084dc..f6e422f 100644
--- a/src/dal/mysqldataprovider.cpp
+++ b/src/dal/mysqldataprovider.cpp
@@ -28,6 +28,18 @@ namespace dal
 {
 
 
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_HOST ="mysql_hostname";
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_PORT ="mysql_port";
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_DB   ="mysql_database";
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_USER ="mysql_username";
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_PWD  ="mysql_password";
+
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_HOST_DEF = "localhost";
+const unsigned int MySqlDataProvider::CFGPARAM_MYSQL_PORT_DEF = 3306;
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_DB_DEF   = "tmw";
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_USER_DEF = "tmw";
+const std::string  MySqlDataProvider::CFGPARAM_MYSQL_PWD_DEF  = "tmw";
+
 /**
  * Constructor.
  */
@@ -71,14 +83,24 @@ MySqlDataProvider::getDbBackend(void) const
  * Create a connection to the database.
  */
 void
-MySqlDataProvider::connect(const std::string& dbName,
-                           const std::string& userName,
-                           const std::string& password)
+MySqlDataProvider::connect()
 {
     if (mIsConnected) {
         return;
     }
 
+    // retrieve configuration from config file
+    const std::string hostname
+        = Configuration::getValue(CFGPARAM_MYSQL_HOST, CFGPARAM_MYSQL_HOST_DEF);
+    const std::string dbName
+        = Configuration::getValue(CFGPARAM_MYSQL_DB, CFGPARAM_MYSQL_DB_DEF);
+    const std::string username
+        = Configuration::getValue(CFGPARAM_MYSQL_USER, CFGPARAM_MYSQL_USER_DEF);
+    const std::string password
+        = Configuration::getValue(CFGPARAM_MYSQL_PWD, CFGPARAM_MYSQL_PWD_DEF);
+    const unsigned int tcpPort
+        = Configuration::getValue(CFGPARAM_MYSQL_PORT, CFGPARAM_MYSQL_PORT_DEF);
+
     // allocate and initialize a new MySQL object suitable
     // for mysql_real_connect().
     mDb = mysql_init(NULL);
@@ -88,17 +110,19 @@ MySqlDataProvider::connect(const std::string& dbName,
             "unable to initialize the MySQL library: no memory");
     }
 
-    // insert connection options here.
+    LOG_INFO("Trying to connect with mySQL database server '"
+        << hostname << ":" << tcpPort << "' using '" << username
+        << "' as user, and '" << dbName << "' as database.");
 
     // actually establish the connection.
-    if (!mysql_real_connect(mDb,              // handle to the connection
-                            NULL,             // localhost
-                            userName.c_str(), // user name
-                            password.c_str(), // user password
-                            dbName.c_str(),   // database name
-                            0,                // use default TCP port
-                            NULL,             // use defaut socket
-                            0))               // client flags
+    if (!mysql_real_connect(mDb,               // handle to the connection
+                            hostname.c_str(),  // hostname
+                            username.c_str(),  // username
+                            password.c_str(),  // password
+                            dbName.c_str(),    // database name
+                            tcpPort,           // tcp port
+                            NULL,              // socket, currently not used
+                            0))                // client flags
     {
         std::string msg(mysql_error(mDb));
         mysql_close(mDb);
@@ -110,6 +134,7 @@ MySqlDataProvider::connect(const std::string& dbName,
     mDbName = dbName;
 
     mIsConnected = true;
+    LOG_INFO("Connection to mySQL was sucessfull.");
 }
 
 
@@ -124,6 +149,8 @@ MySqlDataProvider::execSql(const std::string& sql,
         throw std::runtime_error("not connected to database");
     }
 
+    LOG_DEBUG("Performing SQL query: "<<sql);
+
     // do something only if the query is different from the previous
     // or if the cache must be refreshed
     // otherwise just return the recordset from cache.
@@ -194,5 +221,108 @@ MySqlDataProvider::disconnect(void)
     mIsConnected = false;
 }
 
+void
+MySqlDataProvider::beginTransaction(void)
+    throw (std::runtime_error)
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "Trying to begin a transaction while not "
+            "connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    mysql_autocommit(mDb, AUTOCOMMIT_OFF);
+    execSql("BEGIN");
+    LOG_DEBUG("SQL: started transaction");
+}
+
+void
+MySqlDataProvider::commitTransaction(void)
+    throw (std::runtime_error)
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "Trying to commit a transaction while not "
+            "connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    if (mysql_commit(mDb) != 0)
+    {
+        LOG_ERROR("MySqlDataProvider::commitTransaction: " << mysql_error(mDb));
+        throw DbSqlQueryExecFailure(mysql_error(mDb));
+    }
+    mysql_autocommit(mDb, AUTOCOMMIT_ON);
+    LOG_DEBUG("SQL: commited transaction");
+}
+
+void
+MySqlDataProvider::rollbackTransaction(void)
+    throw (std::runtime_error)
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "Trying to rollback a transaction while not "
+            "connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    if (mysql_rollback(mDb) != 0)
+    {
+        LOG_ERROR("MySqlDataProvider::rollbackTransaction: " << mysql_error(mDb));
+        throw DbSqlQueryExecFailure(mysql_error(mDb));
+    }
+    mysql_autocommit(mDb, AUTOCOMMIT_ON);
+    LOG_DEBUG("SQL: transaction rolled back");
+}
+
+const unsigned int
+MySqlDataProvider::getModifiedRows(void) const
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "Trying to getModifiedRows while not "
+            "connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    // FIXME: not sure if this is correct to bring 64bit int into int?
+    const my_ulonglong affected = mysql_affected_rows(mDb);
+
+    if (affected > INT_MAX)
+        throw std::runtime_error("MySqlDataProvider::getLastId exceeded INT_MAX");
+
+    if (affected == (my_ulonglong)-1)
+    {
+        LOG_ERROR("MySqlDataProvider::getModifiedRows: " << mysql_error(mDb));
+        throw DbSqlQueryExecFailure(mysql_error(mDb));
+    }
+
+    return (unsigned int)affected;
+}
+
+const unsigned int
+MySqlDataProvider::getLastId(void) const
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "not connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    // FIXME: not sure if this is correct to bring 64bit int into int?
+    const my_ulonglong lastId = mysql_insert_id(mDb);
+    if (lastId > UINT_MAX)
+        throw std::runtime_error("MySqlDataProvider::getLastId exceeded INT_MAX");
+
+    return (unsigned int)lastId;
+}
+
 
 } // namespace dal
diff --git a/src/dal/mysqldataprovider.h b/src/dal/mysqldataprovider.h
index f246603..08c56dd 100644
--- a/src/dal/mysqldataprovider.h
+++ b/src/dal/mysqldataprovider.h
@@ -26,10 +26,15 @@
 
 
 #include <iosfwd>
-
+// added to compile under windows
+#ifdef WIN32
+#include <winsock2.h>
+#endif
 #include <mysql/mysql.h>
 
 #include "dataprovider.h"
+#include "common/configuration.hpp"
+#include "utils/logger.h"
 
 namespace dal
 {
@@ -41,6 +46,16 @@ namespace dal
 class MySqlDataProvider: public DataProvider
 {
     public:
+
+        /**
+         * Replacement for mysql my_bool datatype used in mysql_autocommit()
+         * function.
+         */
+        enum {
+            AUTOCOMMIT_OFF = 0,
+            AUTOCOMMIT_ON = 1
+        };
+
         /**
          * Constructor.
          */
@@ -68,16 +83,9 @@ class MySqlDataProvider: public DataProvider
         /**
          * Create a connection to the database.
          *
-         * @param dbName the database name.
-         * @param userName the user name.
-         * @param password the user password.
-         *
          * @exception DbConnectionFailure if unsuccessful connection.
          */
-        void
-        connect(const std::string& dbName,
-                const std::string& userName,
-                const std::string& password);
+        void connect();
 
 
         /**
@@ -104,8 +112,76 @@ class MySqlDataProvider: public DataProvider
         void
         disconnect(void);
 
+        /**
+         * Starts a transaction.
+         *
+         * @exception std::runtime_error if a transaction is still open
+         */
+        void
+        beginTransaction(void)
+            throw (std::runtime_error);
+
+        /**
+         * Commits a transaction.
+         *
+         * @exception std::runtime_error if no connection is currently open.
+         */
+        void
+        commitTransaction(void)
+            throw (std::runtime_error);
+
+        /**
+         * Rollback a transaction.
+         *
+         * @exception std::runtime_error if no connection is currently open.
+         */
+        void
+        rollbackTransaction(void)
+            throw (std::runtime_error);
+
+        /**
+         * Returns the number of changed rows by the last executed SQL
+         * statement.
+         *
+         * @return Number of rows that have changed.
+         */
+        const unsigned int
+        getModifiedRows(void) const;
+
+        /**
+         * Returns the last inserted value of an autoincrement column after an
+         * INSERT statement.
+         *
+         * @return last autoincrement value.
+         */
+        const unsigned int
+        getLastId(void) const;
 
     private:
+
+        /** defines the name of the hostname config parameter */
+        static const std::string CFGPARAM_MYSQL_HOST;
+        /** defines the name of the server port config parameter */
+        static const std::string CFGPARAM_MYSQL_PORT;
+        /** defines the name of the database config parameter */
+        static const std::string CFGPARAM_MYSQL_DB;
+        /** defines the name of the username config parameter */
+        static const std::string CFGPARAM_MYSQL_USER;
+        /** defines the name of the password config parameter */
+        static const std::string CFGPARAM_MYSQL_PWD;
+
+        /** defines the default value of the CFGPARAM_MYSQL_HOST parameter */
+        static const std::string CFGPARAM_MYSQL_HOST_DEF;
+        /** defines the default value of the CFGPARAM_MYSQL_PORT parameter */
+        static const unsigned int CFGPARAM_MYSQL_PORT_DEF;
+        /** defines the default value of the CFGPARAM_MYSQL_DB parameter */
+        static const std::string CFGPARAM_MYSQL_DB_DEF;
+        /** defines the default value of the CFGPARAM_MYSQL_USER parameter */
+        static const std::string CFGPARAM_MYSQL_USER_DEF;
+        /** defines the default value of the CFGPARAM_MYSQL_PWD parameter */
+        static const std::string CFGPARAM_MYSQL_PWD_DEF;
+
+
         MYSQL* mDb; /**< the handle to the database connection */
 };
 
diff --git a/src/dal/sqlitedataprovider.cpp b/src/dal/sqlitedataprovider.cpp
index b126c19..fb539ec 100644
--- a/src/dal/sqlitedataprovider.cpp
+++ b/src/dal/sqlitedataprovider.cpp
@@ -32,6 +32,10 @@ namespace dal
 {
 
 
+const std::string SqLiteDataProvider::CFGPARAM_SQLITE_DB     = "sqlite_database";
+const std::string SqLiteDataProvider::CFGPARAM_SQLITE_DB_DEF = "tmw.db";
+
+
 /**
  * Constructor.
  */
@@ -78,10 +82,15 @@ SqLiteDataProvider::getDbBackend(void) const
  * Create a connection to the database.
  */
 void
-SqLiteDataProvider::connect(const std::string& dbName,
-                            const std::string& userName,
-                            const std::string& password)
+SqLiteDataProvider::connect()
 {
+    // get configuration parameter for sqlite
+    const std::string dbName
+        = Configuration::getValue(CFGPARAM_SQLITE_DB, CFGPARAM_SQLITE_DB_DEF);
+
+    LOG_INFO("Trying to connect with SQLite database file '"
+        << dbName << "'");
+
     // sqlite3_open creates the database file if it does not exist
     // as a side-effect.
     if (sqlite3_open(dbName.c_str(), &mDb) != SQLITE_OK) {
@@ -104,6 +113,7 @@ SqLiteDataProvider::connect(const std::string& dbName,
     mDbName = dbName;
 
     mIsConnected = true;
+    LOG_INFO("Connection to database sucessfull.");
 }
 
 
@@ -118,7 +128,7 @@ SqLiteDataProvider::execSql(const std::string& sql,
         throw std::runtime_error("not connected to database");
     }
 
-    LOG_DEBUG("Performing SQL querry: "<<sql);
+    LOG_DEBUG("Performing SQL query: "<<sql);
 
     // do something only if the query is different from the previous
     // or if the cache must be refreshed
@@ -198,5 +208,166 @@ SqLiteDataProvider::disconnect(void)
     mIsConnected = false;
 }
 
+void
+SqLiteDataProvider::beginTransaction(void)
+    throw (std::runtime_error)
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "Trying to begin a transaction while not "
+            "connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    if (inTransaction())
+    {
+        const std::string error = "Trying to begin a transaction while anoter "
+            "one is still open!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    // trying to open a transaction
+    try
+    {
+        execSql("BEGIN TRANSACTION;");
+        LOG_DEBUG("SQL: started transaction");
+    }
+    catch (const DbSqlQueryExecFailure &e)
+    {
+        std::ostringstream error;
+        error << "SQL ERROR while trying to start a transaction: " << e.what();
+        LOG_ERROR(error);
+        throw std::runtime_error(error.str());
+    }
+}
+
+void
+SqLiteDataProvider::commitTransaction(void)
+    throw (std::runtime_error)
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "Trying to commit a transaction while not "
+            "connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    if (!inTransaction())
+    {
+        const std::string error = "Trying to commit a transaction while no "
+            "one is open!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    // trying to commit a transaction
+    try
+    {
+        execSql("COMMIT TRANSACTION;");
+        LOG_DEBUG("SQL: commited transaction");
+    }
+    catch (const DbSqlQueryExecFailure &e)
+    {
+        std::ostringstream error;
+        error << "SQL ERROR while trying to commit a transaction: " << e.what();
+        LOG_ERROR(error);
+        throw std::runtime_error(error.str());
+    }
+}
+
+void
+SqLiteDataProvider::rollbackTransaction(void)
+    throw (std::runtime_error)
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "Trying to rollback a transaction while not "
+            "connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    if (!inTransaction())
+    {
+        const std::string error = "Trying to rollback a transaction while no "
+            "one is open!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    // trying to rollback a transaction
+    try
+    {
+        execSql("ROLLBACK TRANSACTION;");
+        LOG_DEBUG("SQL: transaction rolled back");
+    }
+    catch (const DbSqlQueryExecFailure &e)
+    {
+        std::ostringstream error;
+        error << "SQL ERROR while trying to rollback a transaction: " << e.what();
+        LOG_ERROR(error);
+        throw std::runtime_error(error.str());
+    }
+}
+
+const unsigned int
+SqLiteDataProvider::getModifiedRows(void) const
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "Trying to getModifiedRows while not "
+            "connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    return (unsigned int)sqlite3_changes(mDb);
+}
+
+const bool
+SqLiteDataProvider::inTransaction(void) const
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "not connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    // The sqlite3_get_autocommit() interface returns non-zero or zero if the
+    // given database connection is or is not in autocommit mode, respectively.
+    // Autocommit mode is on by default. Autocommit mode is disabled by a BEGIN
+    // statement. Autocommit mode is re-enabled by a COMMIT or ROLLBACK.
+    const int ret = sqlite3_get_autocommit(mDb);
+    if (ret == 0)
+    {
+        return true;
+    }
+    else
+    {
+        return false;
+    }
+}
+
+const unsigned int
+SqLiteDataProvider::getLastId(void) const
+{
+    if (!mIsConnected)
+    {
+        const std::string error = "not connected to the database!";
+        LOG_ERROR(error);
+        throw std::runtime_error(error);
+    }
+
+    // FIXME: not sure if this is correct to bring 64bit int into int?
+    const sqlite3_int64 lastId = sqlite3_last_insert_rowid(mDb);
+    if (lastId > UINT_MAX)
+        throw std::runtime_error("SqLiteDataProvider::getLastId exceeded INT_MAX");
+
+    return (unsigned int)lastId;
+}
 
 } // namespace dal
diff --git a/src/dal/sqlitedataprovider.h b/src/dal/sqlitedataprovider.h
index b791025..ea85d02 100644
--- a/src/dal/sqlitedataprovider.h
+++ b/src/dal/sqlitedataprovider.h
@@ -25,13 +25,21 @@
 
 #include <iosfwd>
 #include <sqlite3.h>
+#include "common/configuration.hpp"
+
+
+// sqlite3_int64 is the preferred new datatype for 64-bit int values.
+// see: http://www.sqlite.org/capi3ref.html#sqlite3_int64
+#ifndef sqlite3_int64
+typedef sqlite_int64 sqlite3_int64;
+#endif
+
 
 #include "dataprovider.h"
 
 namespace dal
 {
 
-
 /**
  * A SQLite Data Provider.
  */
@@ -65,16 +73,9 @@ class SqLiteDataProvider: public DataProvider
         /**
          * Create a connection to the database.
          *
-         * @param dbName the database name.
-         * @param userName the user name.
-         * @param password the user password.
-         *
          * @exception DbConnectionFailure if unsuccessful connection.
          */
-        void
-        connect(const std::string& dbName,
-                const std::string& userName,
-                const std::string& password);
+        void connect();
 
 
         /**
@@ -101,8 +102,67 @@ class SqLiteDataProvider: public DataProvider
         void
         disconnect(void);
 
+        /**
+         * Starts a transaction.
+         *
+         * @exception std::runtime_error if a transaction is still open
+         */
+        void
+        beginTransaction(void)
+            throw (std::runtime_error);
+
+        /**
+         * Commits a transaction.
+         *
+         * @exception std::runtime_error if no connection is currently open.
+         */
+        void
+        commitTransaction(void)
+            throw (std::runtime_error);
+
+        /**
+         * Rollback a transaction.
+         *
+         * @exception std::runtime_error if no connection is currently open.
+         */
+        void
+        rollbackTransaction(void)
+            throw (std::runtime_error);
+
+        /**
+         * Returns the number of changed rows by the last executed SQL
+         * statement.
+         *
+         * @return Number of rows that have changed.
+         */
+        const unsigned int
+        getModifiedRows(void) const;
+
+        /**
+         * Returns the last inserted value of an autoincrement column after an
+         * INSERT statement.
+         *
+         * @return last autoincrement value.
+         */
+        const unsigned int
+        getLastId(void) const;
 
     private:
+
+        /** defines the name of the database config parameter */
+        static const std::string CFGPARAM_SQLITE_DB;
+        /** defines the default value of the CFGPARAM_SQLITE_DB parameter */
+        static const std::string CFGPARAM_SQLITE_DB_DEF;
+
+        /**
+         * Returns wheter the connection has a open transaction or is in auto-
+         * commit mode.
+         *
+         * @return true, if a transaction is open.
+         */
+        const bool
+        inTransaction(void) const;
+
         sqlite3* mDb; /**< the handle to the database connection */
 };