* 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

1 files changed, 257 insertions, 325 deletions

diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp
index 601c0bb6..d8040b8e 100644
--- a/src/account-server/dalstorage.cpp
+++ b/src/account-server/dalstorage.cpp
@@ -59,94 +59,34 @@ DALStorage::~DALStorage()
 
 /**
  * Connect to the database and initialize it if necessary.
+ *
+ * TODO: <b>Exceptionfault:</b> after connecting to the database, we have to
+ *       verify if the version matches a supported version. Maybe implement a
+ *       "version table" to check after connect. Raise an error with verbose
+ *       informations about the discrepancy between the versions.
+ *
  */
 void DALStorage::open()
 {
     // Do nothing if already connected.
-    if (mDb->isConnected()) {
+    if (mDb->isConnected())
+    {
         return;
     }
 
     using namespace dal;
 
-    static bool dbFileShown = false;
-    std::string dbFile = "tmw";
     try {
         // open a connection to the database.
-#if defined (MYSQL_SUPPORT) || defined (POSTGRESQL_SUPPORT)
-        mDb->connect(getName(), getUser(), getPassword());
-        if (!dbFileShown)
-        {
-            LOG_INFO("Using " << dbFile << " as Database Name.");
-            dbFileShown = true;
-        }
-#elif defined (SQLITE_SUPPORT)
-        // create the database file name.
-        dbFile += ".db";
-        mDb->connect(dbFile, "", "");
-        if (!dbFileShown)
-        {
-            LOG_INFO("SQLite uses ./" << dbFile << " as DB.");
-            dbFileShown = true;
-        }
-#endif
+        mDb->connect();
+
+        //TODO: check database version here
 
-        // ensure that the required tables are created.
-        //
-        // strategy1: find a way to obtain the list of tables from the
-        //            underlying database and create the tables that are
-        //            missing.
-        //
-        // strategy2: try to create the tables and check the exceptions
-        //            thrown.
-        //
-        // comments:
-        //     - strategy1 is easy to achieve if we are using MysQL as
-        //       executing the request "show tables;" returns the list of
-        //       tables. However, there is not such a query for SQLite3.
-        //       When using SQLite3 from the interactive shell or the
-        //       command line, the command ".tables" returns the list of
-        //       tables but sqlite3_exec() does not validate this statement
-        //       and fails.
-        //       The cost of this strategy is:
-        //           (num. tables to create + 1) queries at most and
-        //           1 at minimum.
-        //
-        //     - strategy2 will work with probably most databases.
-        //       The cost of this strategy is:
-        //           (num. tables to create) queries.
-
-        // we will stick with strategy2 for the moment as we are focusing
-        // on SQLite.
-
-        // FIXME: The tables should be checked/created at startup in order to
-        // avoid a DbSqlQueryExecFailure assert on sqlite while registering.
-        // Also, this would initialize connection to the database earlier in
-        // memory.
-
-        createTable(ACCOUNTS_TBL_NAME, SQL_ACCOUNTS_TABLE);
-        createTable(CHARACTERS_TBL_NAME, SQL_CHARACTERS_TABLE);
-        createTable(INVENTORIES_TBL_NAME, SQL_INVENTORIES_TABLE);
-        createTable(GUILDS_TBL_NAME, SQL_GUILDS_TABLE);
-        createTable(GUILD_MEMBERS_TBL_NAME, SQL_GUILD_MEMBERS_TABLE);
-        createTable(QUESTS_TBL_NAME, SQL_QUESTS_TABLE);
-        createTable(WORLD_STATES_TBL_NAME, SQL_WORLD_STATES_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: "
                      << e.what());
     }
-    catch (const DbSqlQueryExecFailure& e) {
-        LOG_ERROR("(DALStorage::open #2) SQL query failure: " << e.what());
-    }
 }
 
 
@@ -636,84 +576,6 @@ bool DALStorage::updateCharacter(Character *character)
 
 
 /**
- * Create the specified table.
- */
-void
-DALStorage::createTable(const std::string& tblName,
-                        const std::string& sql)
-{
-    try {
-        mDb->execSql(sql);
-    }
-    catch (const dal::DbSqlQueryExecFailure& e) {
-        // error message to check against.
-#if defined (MYSQL_SUPPORT)
-        std::string alreadyExists("Table '");
-        alreadyExists += tblName;
-        alreadyExists += "' already exists";
-#elif defined (POSTGRESQL_SUPPORT)
-        std::string alreadyExists("table ");
-        alreadyExists += tblName;
-        alreadyExists += " already exists";
-#else // SQLITE_SUPPORT
-        std::string alreadyExists("table ");
-        alreadyExists += tblName;
-        alreadyExists += " already exists";
-#endif
-
-        const std::string msg(e.what());
-
-        // oops, another problem occurred.
-        if (msg != alreadyExists) {
-            // rethrow to let other error handlers manage the problem.
-            throw;
-        }
-    }
-}
-
-/**
- * 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.
  */
 void DALStorage::addAccount(Account *account)
@@ -722,31 +584,44 @@ void DALStorage::addAccount(Account *account)
 
     using namespace dal;
 
-    // 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, registration, lastlogin)"
-         << " values (\""
-         << account->getName() << "\", \""
-         << account->getPassword() << "\", \""
-         << account->getEmail() << "\", "
-         << account->getLevel() << ", 0, "
-         << account->getRegistrationDate() << ", "
-         << account->getLastLogin() << ");";
-    mDb->execSql(sql1.str());
-
-    // get the account id.
-    std::ostringstream sql2;
-    sql2 << "select id from " << ACCOUNTS_TBL_NAME
-         << " where username = \"" << account->getName() << "\";";
-    const RecordSet& accountInfo = mDb->execSql(sql2.str());
-    string_to<unsigned int> toUint;
-    unsigned id = toUint(accountInfo(0, 0));
-    account->setID(id);
+    mDb->beginTransaction();
+    try
+    {
+        // insert the account.
+        std::ostringstream sql1;
+        sql1 << "insert into " << ACCOUNTS_TBL_NAME
+             << " (username, password, email, level, banned, registration, lastlogin)"
+             << " values (\""
+             << account->getName() << "\", \""
+             << account->getPassword() << "\", \""
+             << account->getEmail() << "\", "
+             << account->getLevel() << ", 0, "
+             << account->getRegistrationDate() << ", "
+             << account->getLastLogin() << ");";
+        mDb->execSql(sql1.str());
+
+        // get the account id.
+        /*
+        Exceptionfault: no longer needed as our databases provides a function to
+                        get the last inserted auto-increment value
+
+        std::ostringstream sql2;
+        sql2 << "select id from " << ACCOUNTS_TBL_NAME
+             << " where username = \"" << account->getName() << "\";";
+        const RecordSet& accountInfo = mDb->execSql(sql2.str());
+        string_to<unsigned int> toUint;
+        unsigned id = toUint(accountInfo(0, 0));
+        account->setID(id);
+        */
+        account->setID(mDb->getLastId());
+
+        mDb->commitTransaction();
+    }
+    catch (const dal::DbSqlQueryExecFailure &e)
+    {
+        LOG_ERROR("Error in DALStorage::addAccount: " << e.what());
+        mDb->rollbackTransaction();
+    }
 }
 
 /**
@@ -758,153 +633,135 @@ void DALStorage::flush(Account *account)
 
     using namespace dal;
 
-    // TODO: we should start a transaction here so that in case of problem
-    // the loss of data would be minimized.
-
-    // update the account.
-    std::ostringstream sqlUpdateAccountTable;
-    sqlUpdateAccountTable << "update " << ACCOUNTS_TBL_NAME
-         << " set username = '" << account->getName() << "', "
-         << "password = '" << account->getPassword() << "', "
-         << "email = '" << account->getEmail() << "', "
-         << "level = '" << account->getLevel() << "', "
-         << "lastlogin = '" << account->getLastLogin() << "' "
-         << "where id = '" << account->getID() << "';";
-    mDb->execSql(sqlUpdateAccountTable.str());
-
-    // get the list of characters that belong to this account.
-    Characters &characters = account->getCharacters();
-
-    // insert or update the characters.
-    for (Characters::const_iterator it = characters.begin(),
-         it_end = characters.end(); it != it_end; ++it)
+    mDb->beginTransaction();
+    try
     {
-        if ((*it)->getDatabaseID() >= 0)
-        {
-            updateCharacter(*it);
-        }
-        else
+
+        // update the account.
+        std::ostringstream sqlUpdateAccountTable;
+        sqlUpdateAccountTable
+             << "update " << ACCOUNTS_TBL_NAME
+             << " set username = '" << account->getName() << "', "
+             << "password = '" << account->getPassword() << "', "
+             << "email = '" << account->getEmail() << "', "
+             << "level = '" << account->getLevel() << "', "
+             << "lastlogin = '" << account->getLastLogin() << "' "
+             << "where id = '" << account->getID() << "';";
+        mDb->execSql(sqlUpdateAccountTable.str());
+
+        // get the list of characters that belong to this account.
+        Characters &characters = account->getCharacters();
+
+        // insert or update the characters.
+        for (Characters::const_iterator it = characters.begin(),
+             it_end = characters.end(); it != it_end; ++it)
         {
-            std::ostringstream sqlInsertCharactersTable;
-            // insert the character
-            // This assumes that the characters name has been checked for
-            // uniqueness
-            sqlInsertCharactersTable
-                 << "insert into " << CHARACTERS_TBL_NAME
-                 << " (user_id, name, gender, hair_style, hair_color, level, char_pts, correct_pts, money,"
-                 << " x, y, map_id, str, agi, dex, vit, int, will, unarmed_exp, knife_exp, sword_exp, polearm_exp,"
-                 << " staff_exp, whip_exp, bow_exp, shoot_exp, mace_exp, axe_exp, thrown_exp) values ("
-                 << account->getID() << ", \""
-                 << (*it)->getName() << "\", "
-                 << (*it)->getGender() << ", "
-                 << (int)(*it)->getHairStyle() << ", "
-                 << (int)(*it)->getHairColor() << ", "
-                 << (int)(*it)->getLevel() << ", "
-                 << (int)(*it)->getCharacterPoints() << ", "
-                 << (int)(*it)->getCorrectionPoints() << ", "
-                 << (*it)->getPossessions().money << ", "
-                 << (*it)->getPosition().x << ", "
-                 << (*it)->getPosition().y << ", "
-                 << (*it)->getMapId() << ", "
-                 << (*it)->getAttribute(CHAR_ATTR_STRENGTH) << ", "
-                 << (*it)->getAttribute(CHAR_ATTR_AGILITY) << ", "
-                 << (*it)->getAttribute(CHAR_ATTR_DEXTERITY) << ", "
-                 << (*it)->getAttribute(CHAR_ATTR_VITALITY) << ", "
-                 << (*it)->getAttribute(CHAR_ATTR_INTELLIGENCE) << ", "
-                 << (*it)->getAttribute(CHAR_ATTR_WILLPOWER) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_NONE - CHAR_SKILL_BEGIN) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_KNIFE - CHAR_SKILL_BEGIN) << ","
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_SWORD - CHAR_SKILL_BEGIN) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_POLEARM - CHAR_SKILL_BEGIN) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_STAFF - CHAR_SKILL_BEGIN) << ","
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_WHIP - CHAR_SKILL_BEGIN) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_BOW - CHAR_SKILL_BEGIN) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_SHOOTING - CHAR_SKILL_BEGIN) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_MACE - CHAR_SKILL_BEGIN) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_AXE - CHAR_SKILL_BEGIN) << ", "
-                 << (*it)->getExperience(CHAR_SKILL_WEAPON_THROWN - CHAR_SKILL_BEGIN)
-                 << ");";
-
-            mDb->execSql(sqlInsertCharactersTable.str());
-
-            // Update the character ID.
-            std::ostringstream sqlSelectIdCharactersTable;
-            sqlSelectIdCharactersTable
-                 << "select id from " << CHARACTERS_TBL_NAME
-                 << " where name = \"" << (*it)->getName() << "\";";
-            RecordSet const &charInfo =
-                mDb->execSql(sqlSelectIdCharactersTable.str());
-
-            if (!charInfo.isEmpty()) {
-                string_to<unsigned int> toUint;
-                (*it)->setDatabaseID(toUint(charInfo(0, 0)));
+            if ((*it)->getDatabaseID() >= 0)
+            {
+                updateCharacter(*it);
             }
             else
             {
-                // TODO: The character's name is not unique, or some other
-                // error has occured
+                std::ostringstream sqlInsertCharactersTable;
+                // insert the character
+                // This assumes that the characters name has been checked for
+                // uniqueness
+                sqlInsertCharactersTable
+                     << "insert into " << CHARACTERS_TBL_NAME
+                     << " (user_id, name, gender, hair_style, hair_color, level, char_pts, correct_pts, money,"
+                     << " x, y, map_id, str, agi, dex, vit, "
+#if defined(MYSQL_SUPPORT) || defined(POSTGRESQL_SUPPORT)
+            << "`int`, "
+#else
+            << "int, "
+#endif
+                     << "will, unarmed_exp, knife_exp, sword_exp, polearm_exp,"
+                     << " staff_exp, whip_exp, bow_exp, shoot_exp, mace_exp, axe_exp, thrown_exp) values ("
+                     << account->getID() << ", \""
+                     << (*it)->getName() << "\", "
+                     << (*it)->getGender() << ", "
+                     << (int)(*it)->getHairStyle() << ", "
+                     << (int)(*it)->getHairColor() << ", "
+                     << (int)(*it)->getLevel() << ", "
+                     << (int)(*it)->getCharacterPoints() << ", "
+                     << (int)(*it)->getCorrectionPoints() << ", "
+                     << (*it)->getPossessions().money << ", "
+                     << (*it)->getPosition().x << ", "
+                     << (*it)->getPosition().y << ", "
+                     << (*it)->getMapId() << ", "
+                     << (*it)->getAttribute(CHAR_ATTR_STRENGTH) << ", "
+                     << (*it)->getAttribute(CHAR_ATTR_AGILITY) << ", "
+                     << (*it)->getAttribute(CHAR_ATTR_DEXTERITY) << ", "
+                     << (*it)->getAttribute(CHAR_ATTR_VITALITY) << ", "
+                     << (*it)->getAttribute(CHAR_ATTR_INTELLIGENCE) << ", "
+                     << (*it)->getAttribute(CHAR_ATTR_WILLPOWER) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_NONE - CHAR_SKILL_BEGIN) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_KNIFE - CHAR_SKILL_BEGIN) << ","
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_SWORD - CHAR_SKILL_BEGIN) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_POLEARM - CHAR_SKILL_BEGIN) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_STAFF - CHAR_SKILL_BEGIN) << ","
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_WHIP - CHAR_SKILL_BEGIN) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_BOW - CHAR_SKILL_BEGIN) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_SHOOTING - CHAR_SKILL_BEGIN) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_MACE - CHAR_SKILL_BEGIN) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_AXE - CHAR_SKILL_BEGIN) << ", "
+                     << (*it)->getExperience(CHAR_SKILL_WEAPON_THROWN - CHAR_SKILL_BEGIN)
+                     << ");";
+
+                mDb->execSql(sqlInsertCharactersTable.str());
+
+                // Update the character ID.
+                (*it)->setDatabaseID(mDb->getLastId());
             }
-        }
-    }
+        } //
 
-    // Existing characters in memory have been inserted or updated in database.
-    // Now, let's remove those who are no more in memory from database.
+        // Existing characters in memory have been inserted or updated in database.
+        // Now, let's remove those who are no more in memory from database.
 
-    // specialize the string_to functor to convert
-    // a string to an unsigned int.
-    string_to<unsigned short> toUint;
-
-    std::ostringstream sqlSelectNameIdCharactersTable;
-    sqlSelectNameIdCharactersTable
-         << "select name, id from " << CHARACTERS_TBL_NAME
-         << " where user_id = '" << account->getID() << "';";
-    const RecordSet& charInMemInfo =
-        mDb->execSql(sqlSelectNameIdCharactersTable.str());
-
-    // We compare chars from memory and those existing in db,
-    // And delete those not in mem but existing in db.
-    bool charFound;
-    for (unsigned int i = 0; i < charInMemInfo.rows(); ++i) // in database
-    {
-        charFound = false;
-        for (Characters::const_iterator it = characters.begin(),
-             it_end = characters.end(); it != it_end; ++it) // In memory
+        // specialize the string_to functor to convert
+        // a string to an unsigned int.
+        string_to<unsigned short> toUint;
+
+        std::ostringstream sqlSelectNameIdCharactersTable;
+        sqlSelectNameIdCharactersTable
+             << "select name, id from " << CHARACTERS_TBL_NAME
+             << " where user_id = '" << account->getID() << "';";
+        const RecordSet& charInMemInfo =
+            mDb->execSql(sqlSelectNameIdCharactersTable.str());
+
+        // We compare chars from memory and those existing in db,
+        // And delete those not in mem but existing in db.
+        bool charFound;
+        for (unsigned int i = 0; i < charInMemInfo.rows(); ++i) // in database
         {
-            if (charInMemInfo(i, 0) == (*it)->getName())
+            charFound = false;
+            for (Characters::const_iterator it = characters.begin(),
+                 it_end = characters.end(); it != it_end; ++it) // In memory
             {
-                charFound = true;
-                break;
+                if (charInMemInfo(i, 0) == (*it)->getName())
+                {
+                    charFound = true;
+                    break;
+                }
+            }
+            if (!charFound)
+            {
+                // The char is db but not in memory,
+                // It will be removed from database.
+                // We store the id of the char to delete
+                // Because as deleted, the RecordSet is also emptied
+                // That creates an error.
+                unsigned int charId = toUint(charInMemInfo(i, 1));
+                delCharacter(charId, false);
             }
         }
-        if (!charFound)
-        {
-            // The char is db but not in memory,
-            // It will be removed from database.
-            // We store the id of the char to delete
-            // Because as deleted, the RecordSet is also emptied
-            // That creates an error.
-            unsigned int charId = toUint(charInMemInfo(i, 1));
-
-                // delete the inventory.
-                std::ostringstream sqlDeleteInventoryTable;
-                sqlDeleteInventoryTable
-                    << "delete from "
-                    << INVENTORIES_TBL_NAME
-                    << " where owner_id = '"
-                    << charId
-                    << "';";
-                mDb->execSql(sqlDeleteInventoryTable.str());
-
-                // now delete the character.
-                std::ostringstream sqlDeleteCharactersTable;
-                sqlDeleteCharactersTable
-                    << "delete from "
-                    << CHARACTERS_TBL_NAME
-                    << " where id = '"
-                    << charId
-                    << "';";
-                mDb->execSql(sqlDeleteCharactersTable.str());
-        }
+
+        mDb->commitTransaction();
+    }
+    catch (const std::exception &e)
+    {
+        LOG_ERROR("ERROR in DALStorage::flush: " << e.what());
+        mDb->rollbackTransaction();
     }
 }
 
@@ -1168,33 +1025,57 @@ void DALStorage::setWorldStateVar(std::string const &name,
 {
     try
     {
-        std::ostringstream query1;
-        query1 << "DELETE FROM " << WORLD_STATES_TBL_NAME
-               << " WHERE state_name = '" << name << "'";
+        // set the value to empty means: delete the variable
+        if (value.empty())
+        {
+            std::ostringstream deleteStateVar;
+            deleteStateVar << "DELETE FROM " << WORLD_STATES_TBL_NAME
+                           << " WHERE state_name = '" << name << "'";
+            if (map_id >= 0)
+            {
+                deleteStateVar << " AND map_id = '" << map_id << "'";
+            }
+            deleteStateVar << ";";
+            mDb->execSql(deleteStateVar.str());
+            return;
+        }
+
+        // try to update the variable in the database
+        std::ostringstream updateStateVar;
+        updateStateVar << "UPDATE " << WORLD_STATES_TBL_NAME
+                       << "   SET value = '" << value << "', "
+                       << "       moddate = '" << time(NULL) << "' "
+                       << " WHERE state_name = '" << name << "'";
+
         if (map_id >= 0)
         {
-            query1 << "   AND map_id = '" << map_id << "'";
+            updateStateVar << "   AND map_id = '" << map_id << "'";
         }
-        query1 << ";";
-        mDb->execSql(query1.str());
+        updateStateVar << ";";
+        mDb->execSql(updateStateVar.str());
 
-        if (value.empty()) return;
+        // if we updated a row, were finished here
+        if (mDb->getModifiedRows() >= 1)
+        {
+            return;
+        }
 
-        std::ostringstream query2;
-        query2 << "INSERT INTO " << WORLD_STATES_TBL_NAME
-               << " (state_name, map_id, value , moddate) VALUES ("
-               << "'" << name << "', ";
+        // otherwise we have to add the new variable
+        std::ostringstream insertStateVar;
+        insertStateVar << "INSERT INTO " << WORLD_STATES_TBL_NAME
+                       << " (state_name, map_id, value , moddate) VALUES ("
+                       << "'" << name << "', ";
         if (map_id >= 0)
         {
-            query2 << "'" << map_id << "', ";
+            insertStateVar << "'" << map_id << "', ";
         }
         else
         {
-            query2 << "NULL , ";
+            insertStateVar << "NULL , ";
         }
-        query2 << "'" << value << "', "
-               << "'" << time(NULL) << "');";
-        mDb->execSql(query2.str());
+        insertStateVar << "'" << value << "', "
+                       << "'" << time(NULL) << "');";
+        mDb->execSql(insertStateVar.str());
     }
     catch (dal::DbSqlQueryExecFailure const &e)
     {
@@ -1254,6 +1135,57 @@ void DALStorage::banCharacter(int id, int duration)
     }
 }
 
+void DALStorage::delCharacter(int charId, bool startTransaction = true) const
+{
+    if (startTransaction)
+        mDb->beginTransaction();
+    try
+    {
+        // delete the inventory of the character
+        std::ostringstream sqlDeleteInventoryTable;
+        sqlDeleteInventoryTable
+            << "DELETE FROM " << INVENTORIES_TBL_NAME
+            << " WHERE owner_id = '" << charId << "';";
+        mDb->execSql(sqlDeleteInventoryTable.str());
+
+        // delete from the quests table
+        std::ostringstream sqlDeleteQuestsTable;
+        sqlDeleteQuestsTable
+            << "DELETE FROM " << QUESTS_TBL_NAME
+            << " WHERE owner_id = '" << charId << "';";
+        mDb->execSql(sqlDeleteQuestsTable.str());
+
+        // delete from the guilds table
+        std::ostringstream sqlDeleteGuildsTable;
+        sqlDeleteGuildsTable
+            << "DELETE FROM " << GUILD_MEMBERS_TBL_NAME
+            << " WHERE member_id = '" << charId << "';";
+        mDb->execSql(sqlDeleteGuildsTable.str());
+
+        // now delete the character itself.
+        std::ostringstream sqlDeleteCharactersTable;
+        sqlDeleteCharactersTable
+            << "DELETE FROM " << CHARACTERS_TBL_NAME
+            << " WHERE id = '" << charId << "';";
+        mDb->execSql(sqlDeleteCharactersTable.str());
+
+        if (startTransaction)
+            mDb->commitTransaction();
+    }
+    catch (dal::DbSqlQueryExecFailure const &e)
+    {
+        if (startTransaction)
+            mDb->rollbackTransaction();
+        LOG_ERROR("(DALStorage::delCharacter) SQL query failure: " << e.what());
+    }
+}
+
+void DALStorage::delCharacter(Character *character,
+                              bool startTransaction = true) const
+{
+    delCharacter(character->getDatabaseID(), startTransaction);
+}
+
 void DALStorage::checkBannedAccounts()
 {
     try