From 213af0fbde3f198ad1ab2143d32fc6798b7f8f50 Mon Sep 17 00:00:00 2001 From: Thorbjørn Lindeijer Date: Sun, 6 Dec 2009 21:19:39 +0100 Subject: Renamed DALStorage to Storage The only reason it was a DALStorage was because it used to implement the Storage interface, but that interface got removed a long time ago. --- src/Makefile.am | 6 +- src/account-server/accounthandler.cpp | 2 +- src/account-server/dalstorage.cpp | 1997 ------------------------------- src/account-server/dalstorage.hpp | 144 --- src/account-server/dalstoragesql.hpp | 148 --- src/account-server/main-account.cpp | 6 +- src/account-server/serverhandler.cpp | 2 +- src/account-server/storage.cpp | 1998 ++++++++++++++++++++++++++++++++ src/account-server/storage.hpp | 144 +++ src/account-server/storagesql.hpp | 148 +++ src/chat-server/chatchannelmanager.cpp | 2 +- src/chat-server/chathandler.cpp | 2 +- src/chat-server/guildhandler.cpp | 2 +- src/chat-server/guildmanager.cpp | 2 +- src/chat-server/partyhandler.cpp | 2 +- 15 files changed, 2303 insertions(+), 2302 deletions(-) delete mode 100644 src/account-server/dalstorage.cpp delete mode 100644 src/account-server/dalstorage.hpp delete mode 100644 src/account-server/dalstoragesql.hpp create mode 100644 src/account-server/storage.cpp create mode 100644 src/account-server/storage.hpp create mode 100644 src/account-server/storagesql.hpp (limited to 'src') diff --git a/src/Makefile.am b/src/Makefile.am index 09635536..0be4389d 100644 --- a/src/Makefile.am +++ b/src/Makefile.am @@ -15,11 +15,11 @@ manaserv_account_SOURCES = \ account-server/accounthandler.cpp \ account-server/character.hpp \ account-server/character.cpp \ - account-server/dalstorage.hpp \ - account-server/dalstorage.cpp \ - account-server/dalstoragesql.hpp \ account-server/serverhandler.hpp \ account-server/serverhandler.cpp \ + account-server/storage.hpp \ + account-server/storage.cpp \ + account-server/storagesql.hpp \ chat-server/chathandler.hpp \ chat-server/chathandler.cpp \ chat-server/chatclient.hpp \ diff --git a/src/account-server/accounthandler.cpp b/src/account-server/accounthandler.cpp index 3793b31f..5f13af20 100644 --- a/src/account-server/accounthandler.cpp +++ b/src/account-server/accounthandler.cpp @@ -25,7 +25,7 @@ #include "account-server/account.hpp" #include "account-server/accountclient.hpp" #include "account-server/character.hpp" -#include "account-server/dalstorage.hpp" +#include "account-server/storage.hpp" #include "account-server/serverhandler.hpp" #include "chat-server/chathandler.hpp" #include "common/configuration.hpp" diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp deleted file mode 100644 index a22409d1..00000000 --- a/src/account-server/dalstorage.cpp +++ /dev/null @@ -1,1997 +0,0 @@ -/* - * The Mana Server - * Copyright (C) 2004 The Mana World Development Team - * - * This file is part of The Mana Server. - * - * The Mana Server is free software; you can redistribute it and/or modify - * it under the terms of the GNU General Public License as published by - * the Free Software Foundation; either version 2 of the License, or - * any later version. - * - * The Mana Server is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU General Public License - * along with The Mana Server. If not, see . - */ - -#include -#include - -#include "account-server/dalstorage.hpp" - -#include "point.h" -#include "account-server/account.hpp" -#include "account-server/dalstoragesql.hpp" -#include "chat-server/chatchannel.hpp" -#include "chat-server/guild.hpp" -#include "chat-server/post.hpp" -#include "common/configuration.hpp" -#include "dal/dalexcept.h" -#include "dal/dataproviderfactory.h" -#include "utils/functors.h" -#include "utils/logger.h" -#include "utils/xml.hpp" - -// TODO: make data/items.xml a constant or read it from config file -#define DEFAULT_ITEM_FILE "data/items.xml" - -// defines the supported db version -#define DB_VERSION_PARAMETER "database_version" -#define SUPPORTED_DB_VERSION "7" - - -/** - * Constructor. - */ -DALStorage::DALStorage() - : mDb(dal::DataProviderFactory::createDataProvider()), - mItemDbVersion(0) -{ -} - -/** - * Destructor. - */ -DALStorage::~DALStorage() -{ - if (mDb->isConnected()) - close(); - - delete mDb; -} - -/** - * Connect to the database and initialize it if necessary. - */ -void DALStorage::open() -{ - // Do nothing if already connected. - if (mDb->isConnected()) - return; - - using namespace dal; - - try { - // open a connection to the database. - mDb->connect(); - - // check database version here - std::string dbversion = getWorldStateVar(DB_VERSION_PARAMETER); - if (dbversion != SUPPORTED_DB_VERSION) - { - std::ostringstream errmsg; - errmsg << "Database version is not supported. " << - "Needed version: '" << SUPPORTED_DB_VERSION << - "', current version: '" << dbversion << "'"; - throw errmsg.str(); - } - - // 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; - errmsg << "(DALStorage::open #1) Unable to connect to the database: " - << e.what(); - throw errmsg.str(); - } -} - -/** - * Disconnect from the database. - */ -void DALStorage::close() -{ - mDb->disconnect(); -} - -/** - * Gets an account from a prepared SQL statement - * - * @return the account found - */ -Account *DALStorage::getAccountBySQL() -{ - try { - const dal::RecordSet &accountInfo = mDb->processSql(); - - // if the account is not even in the database then - // we have no choice but to return nothing. - if (accountInfo.isEmpty()) - { - return NULL; - } - - // specialize the string_to functor to convert - // a string to an unsigned int. - string_to< unsigned > toUint; - unsigned id = toUint(accountInfo(0, 0)); - - // create an Account instance - // and initialize it with information about the user. - Account *account = new Account(id); - account->setName(accountInfo(0, 1)); - account->setPassword(accountInfo(0, 2)); - account->setEmail(accountInfo(0, 3)); - account->setRegistrationDate(toUint(accountInfo(0, 6))); - account->setLastLogin(toUint(accountInfo(0, 7))); - - int level = toUint(accountInfo(0, 4)); - // Check if the user is permanently banned, or temporarily banned. - if (level == AL_BANNED - || time(NULL) <= (int) toUint(accountInfo(0, 5))) - { - account->setLevel(AL_BANNED); - // It is, so skip character loading. - return account; - } - account->setLevel(level); - - // load the characters associated with the account. - std::ostringstream sql; - sql << "select id from " << CHARACTERS_TBL_NAME << " where user_id = '" - << id << "';"; - const dal::RecordSet &charInfo = mDb->execSql(sql.str()); - - if (!charInfo.isEmpty()) - { - int size = charInfo.rows(); - Characters characters; - - LOG_DEBUG("Account "<< id << " has " << size << " character(s) in database."); - - // Two steps: it seems like multiple requests cannot be alive at the same time. - std::vector< unsigned > characterIDs; - for (int k = 0; k < size; ++k) - { - characterIDs.push_back(toUint(charInfo(k, 0))); - } - - for (int k = 0; k < size; ++k) - { - if (Character *ptr = getCharacter(characterIDs[k], account)) - { - characters.push_back(ptr); - } - else - { - LOG_ERROR("Failed to get character " << characterIDs[k] << " for account " << id << '.'); - } - } - - account->setCharacters(characters); - } - - return account; - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("DALStorage::getAccountBySQL: " << e.what()); - return NULL; // TODO: Throw exception here - } -} - -/** - * Get an account by user name. - * - * @param userName the owner of the account. - * @return the account associated to the user name. - */ -Account *DALStorage::getAccount(const std::string &userName) -{ - std::ostringstream sql; - sql << "SELECT * FROM " << ACCOUNTS_TBL_NAME << " WHERE username = ?"; - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, userName); - } - return getAccountBySQL(); -} - -/** - * Get an account by ID. - * - * @param accountID the ID of the account. - * - * @return the account associated with the ID. - */ -Account *DALStorage::getAccount(int accountID) -{ - std::ostringstream sql; - sql << "SELECT * FROM " << ACCOUNTS_TBL_NAME << " WHERE id = ?"; - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, accountID); - } - return getAccountBySQL(); -} - -/** - * Gets a character from a prepared SQL statement - * - * @param owner the account the character is in. - * - * @return the character found by the query. - */ -Character *DALStorage::getCharacterBySQL(Account *owner) -{ - Character *character; - - // specialize the string_to functor to convert - // a string to an unsigned int. - string_to< unsigned > toUint; - - try { - const dal::RecordSet &charInfo = mDb->processSql(); - - // if the character is not even in the database then - // we have no choice but to return nothing. - if (charInfo.isEmpty()) - { - return NULL; - } - - // specialize the string_to functor to convert - // a string to an unsigned short. - string_to< unsigned short > toUshort; - - character = new Character(charInfo(0, 2), toUint(charInfo(0, 0))); - character->setGender(toUshort(charInfo(0, 3))); - character->setHairStyle(toUshort(charInfo(0, 4))); - character->setHairColor(toUshort(charInfo(0, 5))); - character->setLevel(toUshort(charInfo(0, 6))); - character->setCharacterPoints(toUshort(charInfo(0, 7))); - character->setCorrectionPoints(toUshort(charInfo(0, 8))); - character->getPossessions().money = toUint(charInfo(0, 9)); - Point pos(toUshort(charInfo(0, 10)), toUshort(charInfo(0, 11))); - character->setPosition(pos); - for (int i = 0; i < CHAR_ATTR_NB; ++i) - { - character->setAttribute(CHAR_ATTR_BEGIN + i, - toUshort(charInfo(0, 13 + i))); - } - - int mapId = toUint(charInfo(0, 12)); - if (mapId > 0) - { - character->setMapId(mapId); - } - else - { - // Set character to default map and one of the default location - // Default map is to be 1, as not found return value will be 0. - character->setMapId(Configuration::getValue("defaultMap", 1)); - } - - /* Fill the account-related fields. Last step, as it may require a new - SQL query. */ - if (owner) - { - character->setAccount(owner); - } - else - { - int id = toUint(charInfo(0, 1)); - character->setAccountID(id); - std::ostringstream s; - s << "select level from " << ACCOUNTS_TBL_NAME - << " where id = '" << id << "';"; - const dal::RecordSet &levelInfo = mDb->execSql(s.str()); - character->setAccountLevel(toUint(levelInfo(0, 0)), true); - } - - // load the skills of the char from CHAR_SKILLS_TBL_NAME - std::ostringstream s; - s << "SELECT skill_id, skill_exp " - << "FROM " << CHAR_SKILLS_TBL_NAME << " " - << "WHERE char_id = " << character->getDatabaseID(); - - const dal::RecordSet &skillInfo = mDb->execSql(s.str()); - if (!skillInfo.isEmpty()) - { - const unsigned int nRows = skillInfo.rows(); - for (unsigned int row = 0; row < nRows; row++) - { - character->setExperience( - toUint(skillInfo(row, 0)), // skillid - toUint(skillInfo(row, 1))); // experience - } - } - s.clear(); - s.str(""); - // Load the status effect - s << "select status_id, status_time FROM " << CHAR_STATUS_EFFECTS_TBL_NAME - << " WHERE char_id = " << character->getDatabaseID(); - const dal::RecordSet &statusInfo = mDb->execSql(s.str()); - if (!statusInfo.isEmpty()) - { - const unsigned int nRows = statusInfo.rows(); - for (unsigned int row = 0; row < nRows; row++) - { - character->applyStatusEffect( - toUint(statusInfo(row, 0)), // Statusid - toUint(statusInfo(row, 1))); // Time - } - } - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::getCharacter #1) SQL query failure: " << e.what()); - return NULL; - } - - try - { - std::ostringstream sql; - sql << " select * from " << INVENTORIES_TBL_NAME << " where owner_id = '" - << character->getDatabaseID() << "' order by slot asc;"; - - const dal::RecordSet &itemInfo = mDb->execSql(sql.str()); - if (!itemInfo.isEmpty()) - { - Possessions &poss = character->getPossessions(); - unsigned nextSlot = 0; - - for (int k = 0, size = itemInfo.rows(); k < size; ++k) - { - unsigned slot = toUint(itemInfo(k, 2)); - if (slot < EQUIPMENT_SLOTS) - { - poss.equipment[slot] = toUint(itemInfo(k, 3)); - } - else - { - slot -= 32; - if (slot >= INVENTORY_SLOTS || slot < nextSlot) - { - LOG_ERROR("(DALStorage::getCharacter #2) Corrupted inventory."); - break; - } - InventoryItem item; - if (slot != nextSlot) - { - item.itemId = 0; - item.amount = slot - nextSlot; - poss.inventory.push_back(item); - } - item.itemId = toUint(itemInfo(k, 3)); - item.amount = toUint(itemInfo(k, 4)); - poss.inventory.push_back(item); - nextSlot = slot + 1; - } - } - } - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::getCharacter #2) SQL query failure: " << e.what()); - return NULL; - } - - return character; -} - -/** - * Gets a character by database ID. - * - * @param id the ID of the character. - * @param owner the account the character is in. - * - * @return the character associated to the ID. - */ -Character *DALStorage::getCharacter(int id, Account *owner) -{ - std::ostringstream sql; - sql << "SELECT * FROM " << CHARACTERS_TBL_NAME << " WHERE id = ?"; - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, id); - } - return getCharacterBySQL(owner); -} - -/** - * Gets a character by character name. - * - * @param name of the character - * - * @return the character associated to the name - */ -Character *DALStorage::getCharacter(const std::string &name) -{ - std::ostringstream sql; - sql << "SELECT * FROM " << CHARACTERS_TBL_NAME << " WHERE name = ?"; - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, name); - } - return getCharacterBySQL(NULL); -} - -/** - * Tells if the user name already exists. - * @return true if the user name exists. - */ -bool DALStorage::doesUserNameExist(const std::string &name) -{ - try { - std::ostringstream sql; - sql << "SELECT COUNT(username) FROM " << ACCOUNTS_TBL_NAME - << " WHERE username = ?"; - - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, name); - } - const dal::RecordSet &accountInfo = mDb->processSql(); - - std::istringstream ssStream(accountInfo(0, 0)); - unsigned int iReturn = 1; - ssStream >> iReturn; - return iReturn != 0; - } catch (const std::exception &e) { - // TODO: throw an exception. - LOG_ERROR("(DALStorage::doesUserNameExist) SQL query failure: " << e.what()); - } - - return true; -} - -/** - * Tells if the email address already exists. - * @return true if the email address exists. - */ -bool DALStorage::doesEmailAddressExist(const std::string &email) -{ - try { - std::ostringstream sql; - sql << "SELECT COUNT(email) FROM " << ACCOUNTS_TBL_NAME - << " WHERE UPPER(email) = UPPER(?)"; - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, email); - } - const dal::RecordSet &accountInfo = mDb->processSql(); - - std::istringstream ssStream(accountInfo(0, 0)); - unsigned int iReturn = 1; - ssStream >> iReturn; - return iReturn != 0; - } catch (const std::exception &e) { - // TODO: throw an exception. - LOG_ERROR("(DALStorage::doesEmailAddressExist) SQL query failure: " << e.what()); - } - - return true; -} - -/** - * Tells if the character's name already exists. - * @return true if character's name exists. - */ -bool DALStorage::doesCharacterNameExist(const std::string& name) -{ - try { - std::ostringstream sql; - sql << "SELECT COUNT(name) FROM " << CHARACTERS_TBL_NAME << " WHERE name = ?"; - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, name); - } - const dal::RecordSet &accountInfo = mDb->processSql(); - - std::istringstream ssStream(accountInfo(0, 0)); - int iReturn = 1; - ssStream >> iReturn; - return iReturn != 0; - } catch (const std::exception &e) { - // TODO: throw an exception. - LOG_ERROR("(DALStorage::doesCharacterNameExist) SQL query failure: " - << e.what()); - } - - return true; -} - -/** - * Updates the data for a single character, does not update the owning account - * or the characters name. Primary usage should be storing characterdata - * received from a game server. - * - * @param ptr Character to store values in the database. - * @param startTransaction set to false if this method is called as - * nested transaction. - * @return true on success - */ -bool DALStorage::updateCharacter(Character *character, - bool startTransaction) -{ - // Update the database Character data (see CharacterData for details) - if (startTransaction) - { - mDb->beginTransaction(); - } - try - { - std::ostringstream sqlUpdateCharacterInfo; - sqlUpdateCharacterInfo - << "update " << CHARACTERS_TBL_NAME << " " - << "set " - << "gender = '" << character->getGender() << "', " - << "hair_style = '" << character->getHairStyle() << "', " - << "hair_color = '" << character->getHairColor() << "', " - << "level = '" << character->getLevel() << "', " - << "char_pts = '" << character->getCharacterPoints() << "', " - << "correct_pts = '"<< character->getCorrectionPoints() << "', " - << "money = '" << character->getPossessions().money << "', " - << "x = '" << character->getPosition().x << "', " - << "y = '" << character->getPosition().y << "', " - << "map_id = '" << character->getMapId() << "', " - << "str = '" << character->getAttribute(CHAR_ATTR_STRENGTH) << "', " - << "agi = '" << character->getAttribute(CHAR_ATTR_AGILITY) << "', " - << "dex = '" << character->getAttribute(CHAR_ATTR_DEXTERITY) << "', " - << "vit = '" << character->getAttribute(CHAR_ATTR_VITALITY) << "', " -#if defined(MYSQL_SUPPORT) || defined(POSTGRESQL_SUPPORT) - << "`int` = '" -#else - << "int = '" -#endif - << character->getAttribute(CHAR_ATTR_INTELLIGENCE) << "', " - << "will = '" << character->getAttribute(CHAR_ATTR_WILLPOWER) << "' " - << "where id = '" << character->getDatabaseID() << "';"; - - mDb->execSql(sqlUpdateCharacterInfo.str()); - } - catch (const dal::DbSqlQueryExecFailure& e) - { - // TODO: throw an exception. - if (startTransaction) - { - mDb->rollbackTransaction(); - } - LOG_ERROR("(DALStorage::updateCharacter #1) SQL query failure: " << e.what()); - return false; - } - - /** - * Character's skills - */ - try - { - std::map::const_iterator skill_it; - for (skill_it = character->getSkillBegin(); - skill_it != character->getSkillEnd(); skill_it++) - { - updateExperience(character->getDatabaseID(), skill_it->first, skill_it->second); - } - } - catch (const dal::DbSqlQueryExecFailure& e) - { - // TODO: throw an exception. - if (startTransaction) - { - mDb->rollbackTransaction(); - } - LOG_ERROR("(DALStorage::updateCharacter #2) SQL query failure: " << e.what()); - return false; - } - - - /** - * Character's inventory - */ - - // Delete the old inventory first - try - { - std::ostringstream sqlDeleteCharacterInventory; - sqlDeleteCharacterInventory - << "delete from " << INVENTORIES_TBL_NAME - << " where owner_id = '" << character->getDatabaseID() << "';"; - mDb->execSql(sqlDeleteCharacterInventory.str()); - } - catch (const dal::DbSqlQueryExecFailure& e) - { - // TODO: throw an exception. - if (startTransaction) - { - mDb->rollbackTransaction(); - } - LOG_ERROR("(DALStorage::updateCharacter #3) SQL query failure: " << e.what()); - return false; - } - - // Insert the new inventory data - try - { - std::ostringstream sql; - - sql << "insert into " << INVENTORIES_TBL_NAME - << " (owner_id, slot, class_id, amount) values (" - << character->getDatabaseID() << ", "; - std::string base = sql.str(); - - const Possessions &poss = character->getPossessions(); - - for (int j = 0; j < EQUIPMENT_SLOTS; ++j) - { - int v = poss.equipment[j]; - if (!v) continue; - sql.str(std::string()); - sql << base << j << ", " << v << ", 1);"; - mDb->execSql(sql.str()); - } - - int slot = 32; - for (std::vector< InventoryItem >::const_iterator j = poss.inventory.begin(), - j_end = poss.inventory.end(); j != j_end; ++j) - { - int v = j->itemId; - if (!v) - { - slot += j->amount; - continue; - } - sql.str(std::string()); - sql << base << slot << ", " << v << ", " << unsigned(j->amount) << ");"; - mDb->execSql(sql.str()); - ++slot; - } - - } - catch (const dal::DbSqlQueryExecFailure& e) - { - // TODO: throw an exception. - if (startTransaction) - { - mDb->rollbackTransaction(); - } - LOG_ERROR("(DALStorage::updateCharacter #4) SQL query failure: " << e.what()); - return false; - } - - /** - * Update char status effects - */ - try - { - // Delete the old status effects first - std::ostringstream sql; - - sql << "delete from " << CHAR_STATUS_EFFECTS_TBL_NAME - << " where char_id = '" << character->getDatabaseID() << "';"; - - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure& e) - { - // TODO: throw an exception. - if (startTransaction) - { - mDb->rollbackTransaction(); - } - LOG_ERROR("(DALStorage::updateCharacter #5) SQL query failure: " << e.what()); - return false; - } - try - { - std::map::const_iterator status_it; - for (status_it = character->getStatusEffectBegin(); - status_it != character->getStatusEffectEnd(); status_it++) - { - insertStatusEffect(character->getDatabaseID(), status_it->first, status_it->second); - } - } - catch (const dal::DbSqlQueryExecFailure& e) - { - // TODO: throw an exception - if (startTransaction) - { - mDb->rollbackTransaction(); - } - LOG_ERROR("(DALStorage::updateCharacter #6) SQL query failure: " << e.what()); - return false; - } - if (startTransaction) - { - mDb->commitTransaction(); - } - return true; -} - -/** - * Save changes of a skill to the database permanently. - * - * @param character Character thats skill has changed. - * @param skill_id Identifier of the changed skill. - * - * @exception dbl::DbSqlQueryExecFailure. - * @deprecated Use DALStorage::updateExperience instead!!! -*/ -void DALStorage::flushSkill(const Character * character, int skill_id) -{ - updateExperience(character->getDatabaseID(), skill_id, - character->getExperience(skill_id)); -} - -/** - * Add an account to the database. - * @param account the new account. - */ -void DALStorage::addAccount(Account *account) -{ - assert(account->getCharacters().size() == 0); - - using namespace dal; - - mDb->beginTransaction(); - try - { - // insert the account - std::ostringstream sql; - sql << "insert into " << ACCOUNTS_TBL_NAME - << " (username, password, email, level, banned, registration, lastlogin)" - << " VALUES (?, ?, ?, " - << account->getLevel() << ", 0, " - << account->getRegistrationDate() << ", " - << account->getLastLogin() << ");"; - - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, account->getName()); - mDb->bindValue(2, account->getPassword()); - mDb->bindValue(3, account->getEmail()); - } - - mDb->processSql(); - account->setID(mDb->getLastId()); - - mDb->commitTransaction(); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("Error in DALStorage::addAccount: " << e.what()); - mDb->rollbackTransaction(); - } -} - -/** - * Update an account from the database. - */ -void DALStorage::flush(Account *account) -{ - assert(account->getID() >= 0); - - using namespace dal; - - mDb->beginTransaction(); - try - { - // 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) - { - if ((*it)->getDatabaseID() >= 0) - { - /* 2nd. parameter false means: don't start a transaction in - the updateCharacter method, cause we did this already a few - lines above */ - updateCharacter(*it, false); - } - else - { - 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 ) 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) << " " - << ");"; - - mDb->execSql(sqlInsertCharactersTable.str()); - - // Update the character ID. - (*it)->setDatabaseID(mDb->getLastId()); - - // update the characters skill - std::map::const_iterator skill_it; - for (skill_it = (*it)->getSkillBegin(); - skill_it != (*it)->getSkillEnd(); skill_it++) - { - updateExperience((*it)->getDatabaseID(), skill_it->first, skill_it->second); - } - } - } // - - // 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 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 - { - 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); - } - } - - mDb->commitTransaction(); - } - catch (const std::exception &e) - { - LOG_ERROR("ERROR in DALStorage::flush: " << e.what()); - mDb->rollbackTransaction(); - } -} - -/** - * Delete an account and its associated data from the database. - * - * @param account the account to delete. - */ -void DALStorage::delAccount(Account *account) -{ - account->setCharacters(Characters()); - flush(account); - - // delete the account. - std::ostringstream sql; - sql << "delete from " << ACCOUNTS_TBL_NAME - << " where id = '" << account->getID() << "';"; - mDb->execSql(sql.str()); -} - -/** - * Update the date and time of the last login. - * - * @param account the account that recently logged in. - */ -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()); -} - -/** - * Write a modification message about Character points to the database. - * - * @param CharId ID of the character - * @param CharPoints Number of character points left for the character - * @param CorrPoints Number of correction points left for the character - * @param AttribId ID of the modified attribute - * @param AttribValue New value of the modified attribute - */ -void DALStorage::updateCharacterPoints(int charId, - int charPoints, int corrPoints, - int attribId, int attribValue) -{ - std::ostringstream sql; - sql << "UPDATE " << CHARACTERS_TBL_NAME - << " SET char_pts = " << charPoints << ", " - << " correct_pts = " << corrPoints << ", "; - - switch (attribId) - { - case CHAR_ATTR_STRENGTH: sql << "str = "; break; - case CHAR_ATTR_AGILITY: sql << "agi = "; break; - case CHAR_ATTR_DEXTERITY: sql << "dex = "; break; - case CHAR_ATTR_VITALITY: sql << "vit = "; break; - case CHAR_ATTR_INTELLIGENCE: sql << "int = "; break; - case CHAR_ATTR_WILLPOWER: sql << "will = "; break; - } - sql << attribValue - << " WHERE id = " << charId; - - mDb->execSql(sql.str()); -} - -/** - * Write a modification message about character skills to the database. - * @param CharId ID of the character - * @param SkillId ID of the skill - * @param SkillValue new skill points - */ -void DALStorage::updateExperience(int charId, int skillId, int skillValue) -{ - try - { - // if experience has decreased to 0 we don't store it anymore, - // its the default - if (skillValue == 0) - { - std::ostringstream sql; - sql << "DELETE FROM " << CHAR_SKILLS_TBL_NAME - << " WHERE char_id = " << charId - << " AND skill_id = " << skillId; - mDb->execSql(sql.str()); - return; - } - - // try to update the skill - std::ostringstream sql; - sql << "UPDATE " << CHAR_SKILLS_TBL_NAME - << " SET skill_exp = " << skillValue - << " WHERE char_id = " << charId - << " AND skill_id = " << skillId; - mDb->execSql(sql.str()); - - // check if the update has modified a row - if (mDb->getModifiedRows() > 0) - { - return; - } - - sql.clear(); - sql.str(""); - sql << "INSERT INTO " << CHAR_SKILLS_TBL_NAME << " " - << "(char_id, skill_id, skill_exp) VALUES ( " - << charId << ", " - << skillId << ", " - << skillValue << ")"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("DALStorage::updateExperience: " << e.what()); - throw; - } -} - -/** - * Inserts a record about a status effect into the database - * @param charId ID of the character in the database - * @param statusId ID of the status effect - * @param time Time left on the status effect - */ -void DALStorage::insertStatusEffect(int charId, int statusId, int time) -{ - try - { - std::ostringstream sql; - - sql << "insert into " << CHAR_STATUS_EFFECTS_TBL_NAME - << " (char_id, status_id, status_time) VALUES ( " - << charId << ", " - << statusId << ", " - << time << ")"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("DALStorage::insertStatusEffect: " << e.what()); - throw; - } -} - - -/** - * Add a new guild. - */ -void DALStorage::addGuild(Guild *guild) -{ - std::ostringstream insertSql; - insertSql << "insert into " << GUILDS_TBL_NAME - << " (name) VALUES (?)"; - if (mDb->prepareSql(insertSql.str())) - { - mDb->bindValue(1, guild->getName()); - } - //mDb->execSql(insertSql.str()); - mDb->processSql(); - - std::ostringstream selectSql; - selectSql << "SELECT id FROM " << GUILDS_TBL_NAME - << " WHERE name = ?"; - - if (mDb->prepareSql(selectSql.str())) - { - mDb->bindValue(1, guild->getName()); - } - //const dal::RecordSet& guildInfo = mDb->execSql(selectSql.str()); - const dal::RecordSet& guildInfo = mDb->processSql(); - - string_to toUint; - unsigned id = toUint(guildInfo(0, 0)); - guild->setId(id); -} - -/** - * Delete a guild. - */ -void DALStorage::removeGuild(Guild *guild) -{ - std::ostringstream sql; - sql << "delete from " << GUILDS_TBL_NAME - << " where id = '" - << guild->getId() << "';"; - mDb->execSql(sql.str()); -} - -/** - * Add member to guild. - */ -void DALStorage::addGuildMember(int guildId, int memberId) -{ - std::ostringstream sql; - - try - { - sql << "insert into " << GUILD_MEMBERS_TBL_NAME - << " (guild_id, member_id, rights)" - << " values (" - << guildId << ", \"" - << memberId << "\", " - << 0 << ");"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure& e) { - // TODO: throw an exception. - LOG_ERROR("SQL query failure: " << e.what()); - } -} - -/** - * Remove member from guild. - */ -void DALStorage::removeGuildMember(int guildId, int memberId) -{ - std::ostringstream sql; - - try - { - sql << "delete from " << GUILD_MEMBERS_TBL_NAME - << " where member_id = \"" - << memberId << "\" and guild_id = '" - << guildId << "';"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure& e) - { - // TODO: throw an exception. - LOG_ERROR("SQL query failure: " << e.what()); - } -} - -/** - * Save guild member rights. - */ -void DALStorage::setMemberRights(int guildId, int memberId, int rights) -{ - std::ostringstream sql; - - try - { - sql << "update " << GUILD_MEMBERS_TBL_NAME - << " set rights = '" << rights << "'" - << " where member_id = \"" - << memberId << "\";"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure& e) - { - // TODO: throw an exception. - LOG_ERROR("SQL query failure: " << e.what()); - } -} - -/** - * Get the list of guilds. - * @return a list of guilds - */ -std::list DALStorage::getGuildList() -{ - std::list guilds; - std::stringstream sql; - string_to toShort; - - /** - * Get the guilds stored in the db. - */ - - try - { - sql << "select id, name from " << GUILDS_TBL_NAME << ";"; - const dal::RecordSet& guildInfo = mDb->execSql(sql.str()); - - // check that at least 1 guild was returned - if (guildInfo.isEmpty()) - { - return guilds; - } - - // loop through every row in the table and assign it to a guild - for ( unsigned int i = 0; i < guildInfo.rows(); ++i) - { - Guild* guild = new Guild(guildInfo(i,1)); - guild->setId(toShort(guildInfo(i,0))); - guilds.push_back(guild); - } - string_to< unsigned > toUint; - - /** - * Add the members to the guilds. - */ - for (std::list::iterator itr = guilds.begin(); - itr != guilds.end(); - ++itr) - { - std::ostringstream memberSql; - memberSql << "select member_id, rights from " << GUILD_MEMBERS_TBL_NAME - << " where guild_id = '" << (*itr)->getId() << "';"; - const dal::RecordSet& memberInfo = mDb->execSql(memberSql.str()); - - std::list > members; - for (unsigned int j = 0; j < memberInfo.rows(); ++j) - { - members.push_back(std::pair(toUint(memberInfo(j, 0)), toUint(memberInfo(j, 1)))); - } - - for (std::list >::const_iterator i = members.begin(); - i != members.end(); - ++i) - { - Character *character = getCharacter((*i).first, NULL); - if (character) - { - character->addGuild((*itr)->getName()); - (*itr)->addMember(character->getDatabaseID(), (*i).second); - } - } - } - } - catch (const dal::DbSqlQueryExecFailure& e) { - // TODO: throw an exception. - LOG_ERROR("SQL query failure: " << e.what()); - } - - return guilds; -} - -/** - * Gets the value of a quest variable. - */ -std::string DALStorage::getQuestVar(int id, const std::string &name) -{ - try - { - std::ostringstream query; - query << "select value from " << QUESTS_TBL_NAME - << " WHERE owner_id = ? AND name = ?"; - if (mDb->prepareSql(query.str())) - { - mDb->bindValue(1, id); - mDb->bindValue(2, name); - } - const dal::RecordSet &info = mDb->processSql(); - - if (!info.isEmpty()) return info(0, 0); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::getQuestVar) SQL query failure: " << e.what()); - } - - return std::string(); -} - -/** - * Gets the string value of a map specific world state variable. - * - * @param name Name of the requested world-state variable. - * @param map_id Id of the specific map. - */ -std::string DALStorage::getWorldStateVar(const std::string &name, int map_id) -{ - try - { - std::ostringstream query; - query << "SELECT value " - << " FROM " << WORLD_STATES_TBL_NAME - << " WHERE state_name = '" << name << "'"; - - // add map filter if map_id is given - if (map_id >= 0) - { - query << " AND map_id = '" << map_id << "'"; - } - - query << ";"; - const dal::RecordSet &info = mDb->execSql(query.str()); - - if (!info.isEmpty()) return info(0, 0); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::getWorldStateVar) SQL query failure: " << e.what()); - } - - return std::string(); -} - -/** - * Sets the value of a world state variable. - * - * @param name Name of the world-state vairable. - * @param value New value of the world-state variable. - */ -void DALStorage::setWorldStateVar(const std::string &name, const std::string &value) -{ - return setWorldStateVar(name, -1, value); -} - -/** - * Sets the value of a world state variable of a specific map. - * - * @param name Name of the world-state vairable. - * @param mapId ID of the specific map - * @param value New value of the world-state variable. - */ -void DALStorage::setWorldStateVar(const std::string &name, - int mapId, - const std::string &value) -{ - try - { - // 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 (mapId >= 0) - { - deleteStateVar << " AND map_id = '" << mapId << "'"; - } - 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 (mapId >= 0) - { - updateStateVar << " AND map_id = '" << mapId << "'"; - } - updateStateVar << ";"; - mDb->execSql(updateStateVar.str()); - - // if we updated a row, were finished here - if (mDb->getModifiedRows() >= 1) - { - return; - } - - // 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 (mapId >= 0) - { - insertStateVar << "'" << mapId << "', "; - } - else - { - insertStateVar << "NULL , "; - } - insertStateVar << "'" << value << "', " - << "'" << time(NULL) << "');"; - mDb->execSql(insertStateVar.str()); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::setWorldStateVar) SQL query failure: " << e.what()); - } -} - -/** - * Sets the value of a quest variable. - */ -void DALStorage::setQuestVar(int id, const std::string &name, - const std::string &value) -{ - try - { - std::ostringstream query1; - query1 << "delete from " << QUESTS_TBL_NAME - << " where owner_id = '" << id << "' and name = '" - << name << "';"; - mDb->execSql(query1.str()); - - if (value.empty()) return; - - std::ostringstream query2; - query2 << "insert into " << QUESTS_TBL_NAME - << " (owner_id, name, value) values ('" - << id << "', '" << name << "', '" << value << "');"; - mDb->execSql(query2.str()); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::setQuestVar) SQL query failure: " << e.what()); - } -} - -/** - * Sets a ban on an account (hence on all its characters). - * - * @param id character identifier. - * @param duration duration in minutes. - */ -void DALStorage::banCharacter(int id, int duration) -{ - try - { - std::ostringstream query; - query << "select user_id from " << CHARACTERS_TBL_NAME - << " where id = '" << id << "';"; - const dal::RecordSet &info = mDb->execSql(query.str()); - if (info.isEmpty()) - { - LOG_ERROR("Tried to ban an unknown user."); - return; - } - - std::ostringstream sql; - sql << "update " << ACCOUNTS_TBL_NAME - << " set level = '" << AL_BANNED << "', banned = '" - << time(NULL) + duration * 60 - << "' where id = '" << info(0, 0) << "';"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::banAccount) SQL query failure: " << e.what()); - } -} - -/** - * Delete a character in the database. - * - * @param charId character identifier. - * @param startTransaction indicates wheter the function should run in - * its own transaction or is called inline of another transaction - */ -void DALStorage::delCharacter(int charId, bool startTransaction = true) const -{ - if (startTransaction) - mDb->beginTransaction(); - try - { - std::ostringstream sql; - - // delete the inventory of the character - sql << "DELETE FROM " << INVENTORIES_TBL_NAME - << " WHERE owner_id = '" << charId << "';"; - mDb->execSql(sql.str()); - - // delete the skills of the character - sql.clear(); - sql.str(""); - sql << "DELETE FROM " << CHAR_SKILLS_TBL_NAME - << " WHERE char_id = '" << charId << "';"; - mDb->execSql(sql.str()); - - // delete from the quests table - sql.clear(); - sql.str(""); - sql << "DELETE FROM " << QUESTS_TBL_NAME - << " WHERE owner_id = '" << charId << "';"; - mDb->execSql(sql.str()); - - // delete from the guilds table - sql.clear(); - sql.str(""); - sql << "DELETE FROM " << GUILD_MEMBERS_TBL_NAME - << " WHERE member_id = '" << charId << "';"; - mDb->execSql(sql.str()); - - // delete auctions of the character - sql.clear(); - sql.str(""); - sql << "DELETE FROM " << AUCTION_TBL_NAME - << " WHERE char_id = '" << charId << "';"; - mDb->execSql(sql.str()); - - // delete bids made on auctions made by the character - sql.clear(); - sql.str(""); - sql << "DELETE FROM " << AUCTION_BIDS_TBL_NAME - << " WHERE char_id = '" << charId << "';"; - mDb->execSql(sql.str()); - - // now delete the character itself. - sql.clear(); - sql.str(""); - sql << "DELETE FROM " << CHARACTERS_TBL_NAME - << " WHERE id = '" << charId << "';"; - mDb->execSql(sql.str()); - - if (startTransaction) - mDb->commitTransaction(); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - if (startTransaction) - mDb->rollbackTransaction(); - LOG_ERROR("(DALStorage::delCharacter) SQL query failure: " << e.what()); - } -} - -/** - * Delete a character in the database. The object itself is not touched - * by this function! - * - * @param character character object. - * @param startTransaction indicates wheter the function should run in - * its own transaction or is called inline of another transaction - */ -void DALStorage::delCharacter(Character *character, - bool startTransaction = true) const -{ - delCharacter(character->getDatabaseID(), startTransaction); -} - -/** - * Removes expired bans from accounts - */ -void DALStorage::checkBannedAccounts() -{ - try - { - // update expired bans - std::ostringstream sql; - sql << "update " << ACCOUNTS_TBL_NAME - << " set level = " << AL_PLAYER << ", banned = 0" - << " where level = " << AL_BANNED - << " AND banned <= " << time(NULL) << ";"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::checkBannedAccounts) SQL query failure: " << e.what()); - } -} - -/** - * Set the level on an account. - * - * @param id The id of the account - * @param level The level to set for the account - */ -void DALStorage::setAccountLevel(int id, int level) -{ - try - { - std::ostringstream sql; - sql << "update " << ACCOUNTS_TBL_NAME - << " set level = " << level - << " where id = " << id << ";"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::setAccountLevel) SQL query failure: " << e.what()); - } -} - -/** - * Set the level on a character. - * - * @param id The id of the character - * @param level The level to set for the character - */ -void DALStorage::setPlayerLevel(int id, int level) -{ - try - { - std::ostringstream sql; - sql << "update " << CHARACTERS_TBL_NAME - << " set level = " << level - << " where id = " << id << ";"; - mDb->execSql(sql.str()); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::setPlayerLevel) SQL query failure: " << e.what()); - } -} - -/** - * Store letter. - * - * @param letter The letter to store - */ -void DALStorage::storeLetter(Letter *letter) -{ - std::ostringstream sql; - if (letter->getId() == 0) - { - // the letter was never saved before - sql << "INSERT INTO " << POST_TBL_NAME << " VALUES ( " - << "NULL, " - << letter->getSender()->getDatabaseID() << ", " - << letter->getReceiver()->getDatabaseID() << ", " - << letter->getExpiry() << ", " - << time(NULL) << ", " - << "?)"; - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, letter->getContents()); - } - - mDb->processSql(); - letter->setId(mDb->getLastId()); - - // TODO: store attachments in the database - - return; - } - else - { - // the letter has a unique id, update the record in the db - sql << "UPDATE " << POST_TBL_NAME - << " SET sender_id = '" << letter->getSender()->getDatabaseID() << "', " - << " receiver_id = '" << letter->getReceiver()->getDatabaseID() << "', " - << " letter_type = '" << letter->getType() << "', " - << " expiration_date = '" << letter->getExpiry() << "', " - << " sending_date = '" << time(NULL) << "', " - << " letter_text = ? " - << " WHERE letter_id = '" << letter->getId() << "'"; - - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, letter->getContents()); - } - mDb->processSql(); - - if (mDb->getModifiedRows() == 0) - { - // this should never happen... - LOG_ERROR("(DALStorage::storePost) trying to update nonexistant letter"); - throw "(DALStorage::storePost) trying to update nonexistant letter"; - } - - // TODO: update attachments in the database - } -} - -/** - * Retrieve post - * - * @param playerId The id of the character requesting his post - */ -Post *DALStorage::getStoredPost(int playerId) -{ - Post *p = new Post(); - // specialize the string_to functor to convert - // a string to an unsigned int. - string_to< unsigned > toUint; - - std::ostringstream sql; - sql << "SELECT * FROM " << POST_TBL_NAME - << " WHERE receiver_id = " << playerId; - - const dal::RecordSet &post = mDb->execSql(sql.str()); - - if (post.isEmpty()) - { - // there is no post waiting for the character - return p; - } - - for (unsigned int i = 0; i < post.rows(); i++ ) - { - // load sender and receiver - Character *sender = getCharacter(toUint(post(i, 1)), NULL); - Character *receiver = getCharacter(toUint(post(i, 2)), NULL); - - Letter *letter = new Letter(toUint( post(0,3) ), sender, receiver); - - letter->setId( toUint(post(0, 0)) ); - letter->setExpiry( toUint(post(0, 4)) ); - letter->addText( post(0, 6) ); - - // TODO: load attachments per letter from POST_ATTACHMENTS_TBL_NAME - // needs redesign of struct ItemInventroy - - p->addLetter(letter); - } - - return p; -} - -/** - * Delete a letter from the database. - * @param letter The letter to delete. - */ -void DALStorage::deletePost(Letter *letter) -{ - mDb->beginTransaction(); - - try - { - std::ostringstream sql; - - // first delete all attachments of the letter - // this could leave "dead" items in the item_instances table - sql << "DELETE FROM " << POST_ATTACHMENTS_TBL_NAME - << " WHERE letter_id = " << letter->getId(); - mDb->execSql(sql.str()); - - // delete the letter itself - sql.clear(); - sql.str(""); - sql << "DELETE FROM " << POST_TBL_NAME - << " WHERE letter_id = " << letter->getId(); - mDb->execSql(sql.str()); - - mDb->commitTransaction(); - letter->setId(0); - } - catch(const dal::DbSqlQueryExecFailure &e) - { - mDb->rollbackTransaction(); - LOG_ERROR("(DALStorage::deletePost) SQL query failure: " << e.what()); - } -} - -/** - * Synchronizes the base data in the connected SQL database with the xml - * files like items.xml. - * This method is called once after initialization of DALStorage. - * Probably this function should be called if a gm requests an online - * reload of the xml files to load new items or monsters without server - * restart. - */ -void DALStorage::syncDatabase() -{ - xmlDocPtr doc = xmlReadFile(DEFAULT_ITEM_FILE, NULL, 0); - if (!doc) - { - LOG_ERROR("Item Manager: Error while parsing item database (items.xml)!"); - return; - } - - xmlNodePtr node = xmlDocGetRootElement(doc); - if (!node || !xmlStrEqual(node->name, BAD_CAST "items")) - { - LOG_ERROR("Item Manager:(items.xml) is not a valid database file!"); - xmlFreeDoc(doc); - return; - } - - mDb->beginTransaction(); - int itmCount = 0; - for (node = node->xmlChildrenNode; node != NULL; node = node->next) - { - // Try to load the version of the item database. The version is defined - // as subversion tag embedded as XML attribute. So every modification - // to the items.xml file will increase the revision automatically. - if (xmlStrEqual(node->name, BAD_CAST "version")) - { - std::string revision = XML::getProperty(node, "revision", std::string()); - mItemDbVersion = atoi(revision.c_str()); - LOG_INFO("Loading item database version " << mItemDbVersion); - } - - if (!xmlStrEqual(node->name, BAD_CAST "item")) - { - continue; - } - - if (xmlStrEqual(node->name, BAD_CAST "item")) - { - int id = XML::getProperty(node, "id", 0); - if (id < 500) - { - continue; - } - - int weight = XML::getProperty(node, "weight", 0); - std::string type = XML::getProperty(node, "type", ""); - std::string name = XML::getProperty(node, "name", ""); - std::string desc = XML::getProperty(node, "description", ""); - std::string eff = XML::getProperty(node, "effect", ""); - std::string image = XML::getProperty(node, "image", ""); - std::string dye(""); - - // split image name and dye string - size_t pipe = image.find("|"); - if (pipe != std::string::npos) - { - dye = image.substr(pipe + 1); - image = image.substr(0, pipe); - } - - try - { - std::ostringstream sql; - sql << "UPDATE " << ITEMS_TBL_NAME - << " SET name = ?, " - << " description = ?, " - << " image = '" << image << "', " - << " weight = " << weight << ", " - << " itemtype = '" << type << "', " - << " effect = ?, " - << " dyestring = '" << dye << "' " - << " WHERE id = " << id; - - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, name); - mDb->bindValue(2, desc); - mDb->bindValue(3, eff); - } - mDb->processSql(); - if (mDb->getModifiedRows() == 0) - { - sql.clear(); - sql.str(""); - sql << "INSERT INTO " << ITEMS_TBL_NAME - << " VALUES ( " << id << ", ?, ?, '" - << image << "', " << weight << ", '" - << type << "', ?, '" << dye << "' )"; - //mDb->execSql(sql.str()); - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, name); - mDb->bindValue(2, desc); - mDb->bindValue(3, eff); - } - mDb->processSql(); - } - itmCount++; - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::SyncDatabase) SQL query failure: " << e.what()); - } - } - } - - mDb->commitTransaction(); - xmlFreeDoc(doc); -} - -/** - * 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 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 (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::setOnlineStatus) SQL query failure: " << e.what()); - } -} - -/** - * Store a transaction. - */ -void DALStorage::addTransaction(const Transaction &trans) -{ - try - { - std::stringstream sql; - sql << "INSERT INTO " << TRANSACTION_TBL_NAME - << " VALUES (NULL, " << trans.mCharacterId << ", " - << trans.mAction << ", " - << "?, " - << time(NULL) << ")"; - if (mDb->prepareSql(sql.str())) - { - mDb->bindValue(1, trans.mMessage); - } - mDb->processSql(); - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::addTransaction) SQL query failure: " << e.what()); - } -} - -/** - * Retrieve the last \num transactions that were stored. - */ -std::vector DALStorage::getTransactions(unsigned int num) -{ - std::vector transactions; - string_to toUint; - - try - { - std::stringstream sql; - sql << "SELECT * FROM " << TRANSACTION_TBL_NAME; - const dal::RecordSet &rec = mDb->execSql(sql.str()); - - int size = rec.rows(); - int start = size - num; - // Get the last records and store them in transactions - for (int i = start; i < size; ++i) - { - Transaction trans; - trans.mCharacterId = toUint(rec(i, 1)); - trans.mAction = toUint(rec(i, 2)); - trans.mMessage = rec(i, 3); - transactions.push_back(trans); - } - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::getTransactions) SQL query failure: " << e.what()); - } - - return transactions; -} - -/** - * Retrieve all transactions since the given \a date. - */ -std::vector DALStorage::getTransactions(time_t date) -{ - std::vector transactions; - string_to toUint; - - try - { - std::stringstream sql; - sql << "SELECT * FROM " << TRANSACTION_TBL_NAME << " WHERE time > " - << date; - const dal::RecordSet &rec = mDb->execSql(sql.str()); - - for (unsigned int i = 0; i < rec.rows(); ++i) - { - Transaction trans; - trans.mCharacterId = toUint(rec(i, 1)); - trans.mAction = toUint(rec(i, 2)); - trans.mMessage = rec(i, 3); - transactions.push_back(trans); - } - } - catch (const dal::DbSqlQueryExecFailure &e) - { - LOG_ERROR("(DALStorage::getTransactions) SQL query failure: " << e.what()); - } - - return transactions; -} diff --git a/src/account-server/dalstorage.hpp b/src/account-server/dalstorage.hpp deleted file mode 100644 index cb7be468..00000000 --- a/src/account-server/dalstorage.hpp +++ /dev/null @@ -1,144 +0,0 @@ -/* - * The Mana Server - * Copyright (C) 2004 The Mana World Development Team - * - * This file is part of The Mana Server. - * - * The Mana Server is free software; you can redistribute it and/or modify - * it under the terms of the GNU General Public License as published by - * the Free Software Foundation; either version 2 of the License, or - * any later version. - * - * The Mana Server is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU General Public License - * along with The Mana Server. If not, see . - */ - -#ifndef DALSTORAGE_H -#define DALSTORAGE_H - -#include -#include -#include - -#include "dal/dataprovider.h" - -#include "common/transaction.hpp" - -class Account; -class Character; -class ChatChannel; -class Guild; -class Letter; -class Post; - -/** - * The high level interface to the database. Through the storage you can access - * all accounts, characters, guilds, worlds states, transactions, etc. - */ -class DALStorage -{ - public: - DALStorage(); - ~DALStorage(); - - void open(); - void close(); - - Account *getAccount(const std::string &userName); - Account *getAccount(int accountID); - - Character *getCharacter(int id, Account *owner); - Character *getCharacter(const std::string &name); - - void addAccount(Account *account); - void delAccount(Account *account); - - void updateLastLogin(const Account *account); - - void updateCharacterPoints(int charId, - int charPoints, int corrPoints, - int attribId, int attribValue); - - void updateExperience(int charId, int skillId, int skillValue); - - void insertStatusEffect(int charId, int statusId, int time); - - void banCharacter(int id, int duration); - - void delCharacter(int charId, bool startTransaction) const; - void delCharacter(Character *character, bool startTransaction) const; - - void checkBannedAccounts(); - - bool doesUserNameExist(const std::string &name); - bool doesEmailAddressExist(const std::string &email); - bool doesCharacterNameExist(const std::string &name); - - bool updateCharacter(Character *ptr, - bool startTransaction = true); - - void flushSkill(const Character *character, int skill_id); - - void addGuild(Guild *guild); - void removeGuild(Guild *guild); - - void addGuildMember(int guild_id, int memberId); - void removeGuildMember(int guildId, int memberId); - - void setMemberRights(int guildId, int memberId, int rights); - - std::list getGuildList(); - - void flush(Account *); - - std::string getQuestVar(int id, const std::string &); - void setQuestVar(int id, const std::string &, const std::string &); - - std::string getWorldStateVar(const std::string &name, int map_id = -1); - void setWorldStateVar(const std::string &name, - const std::string &value); - void setWorldStateVar(const std::string &name, int mapId, - const std::string &value); - - void setAccountLevel(int id, int level); - void setPlayerLevel(int id, int level); - - void storeLetter(Letter *letter); - Post *getStoredPost(int playerId); - void deletePost(Letter* letter); - - /** - * Returns the version of the local item database. - */ - unsigned int getItemDatabaseVersion() const - { return mItemDbVersion; } - - void setOnlineStatus(int charId, bool online); - - void addTransaction(const Transaction &trans); - - std::vector getTransactions(unsigned int num); - std::vector getTransactions(time_t date); - - private: - // Prevent copying - DALStorage(const DALStorage &rhs); - DALStorage &operator=(const DALStorage &rhs); - - Account *getAccountBySQL(); - Character *getCharacterBySQL(Account *owner); - - void syncDatabase(); - - dal::DataProvider *mDb; /**< the data provider */ - unsigned int mItemDbVersion; /**< Version of the item database. */ -}; - -extern DALStorage *storage; - -#endif // DALSTORAGE_H diff --git a/src/account-server/dalstoragesql.hpp b/src/account-server/dalstoragesql.hpp deleted file mode 100644 index bfdbc5a5..00000000 --- a/src/account-server/dalstoragesql.hpp +++ /dev/null @@ -1,148 +0,0 @@ -/* - * The Mana Server - * Copyright (C) 2008 The Mana World Development Team - * - * This file is part of The Mana Server. - * - * The Mana Server is free software; you can redistribute it and/or modify - * it under the terms of the GNU General Public License as published by - * the Free Software Foundation; either version 2 of the License, or - * any later version. - * - * The Mana Server is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU General Public License - * along with The Mana Server. If not, see . - */ - -#ifndef _MANASERV_DALSTORAGE_SQL_H -#define _MANASERV_DALSTORAGE_SQL_H - -#ifdef HAVE_CONFIG_H -#include "config.h" -#endif - -#if !defined (MYSQL_SUPPORT) && !defined (SQLITE_SUPPORT) && \ - !defined (POSTGRESQL_SUPPORT) -#error "(dalstorage.hpp) no database backend defined" -#endif - -// TODO: Fix problem with PostgreSQL null primary key's. - -/** - * MySQL specificities: - * - TINYINT is an integer (1 byte) type defined as an extension to - * the SQL standard. - * - all integer types can have an optional (non-standard) attribute - * UNSIGNED (http://dev.mysql.com/doc/mysql/en/numeric-types.html) - * - * SQLite3 specificities: - * - any column (but only one for each table) with the exact type of - * 'INTEGER PRIMARY KEY' is taken as auto-increment. - * - the supported data types are: NULL, INTEGER, REAL, TEXT and BLOB - * (http://www.sqlite.org/datatype3.html) - * - the size of TEXT cannot be set, it is just ignored by the engine. - * - IMPORTANT: foreign key constraints are not yet supported - * (http://www.sqlite.org/omitted.html). Included in case of future - * support. - * - * Notes: - * - the SQL queries will take advantage of the most appropriate data - * types supported by a particular database engine in order to - * optimize the server database size. - */ - - -/** - * TABLE: mana_accounts. - */ -static const char *ACCOUNTS_TBL_NAME = "mana_accounts"; - -/** - * TABLE: mana_characters. - * - gender is 0 for male, 1 for female. - */ -static const char *CHARACTERS_TBL_NAME = "mana_characters"; - -/** - * TABLE: mana_char_skills. - */ -static const char *CHAR_SKILLS_TBL_NAME = "mana_char_skills"; - -/** - * TABLE: mana_char_status_effects. - */ -static const char *CHAR_STATUS_EFFECTS_TBL_NAME = "mana_char_status_effects"; - -/** - * TABLE: mana_inventories. - */ -static const char *INVENTORIES_TBL_NAME = "mana_inventories"; - -/** - * TABLE: mana_items. - */ -static const char *ITEMS_TBL_NAME = "mana_items"; - -/** - * TABLE: mana_guilds. - * Store player guilds - */ -static const char *GUILDS_TBL_NAME = "mana_guilds"; - -/** - * TABLE: mana_guild_members. - * Store guild members - */ -static const char *GUILD_MEMBERS_TBL_NAME = "mana_guild_members"; - -/** - * TABLE: mana_quests. - */ -static const char *QUESTS_TBL_NAME = "mana_quests"; - -/** - * TABLE: mana_world_states - */ -static const char *WORLD_STATES_TBL_NAME = "mana_world_states"; - -/** - * TABLE: mana_post - * Store letters sent by characters - */ -static const char *POST_TBL_NAME = "mana_post"; - -/** - * TABLE: mana_post_attachments - * Store attachments per letter. - */ -static const char *POST_ATTACHMENTS_TBL_NAME = "mana_post_attachments"; - -/** - * TABLE: mana_auctions - * Store items auctions. - */ -static const char *AUCTION_TBL_NAME = "mana_auctions"; - -/** - * TABLE: mana_auction_bids - * Store bids on auctions. - */ -static const char *AUCTION_BIDS_TBL_NAME = "mana_auction_bids"; - -/** - * TABLE: mana_online_list - * List currently online users. - */ -static const char *ONLINE_USERS_TBL_NAME = "mana_online_list"; - -/** - * TABLE: mana_transactions - * Stores all transactions - */ -static const char *TRANSACTION_TBL_NAME = "mana_transactions"; - -#endif // _MANASERV_DALSTORAGE_SQL_H diff --git a/src/account-server/main-account.cpp b/src/account-server/main-account.cpp index 345737d0..e7e08755 100644 --- a/src/account-server/main-account.cpp +++ b/src/account-server/main-account.cpp @@ -32,7 +32,7 @@ #include "account-server/accounthandler.hpp" #include "account-server/serverhandler.hpp" -#include "account-server/dalstorage.hpp" +#include "account-server/storage.hpp" #include "chat-server/chatchannelmanager.hpp" #include "chat-server/chathandler.hpp" #include "chat-server/guildmanager.hpp" @@ -58,7 +58,7 @@ static bool running = true; /**< Determines if server keeps running */ utils::StringFilter *stringFilter; /**< Slang's Filter */ /** Database handler. */ -DALStorage *storage; +Storage *storage; /** Communications (chat) message handler */ ChatHandler *chatHandler; @@ -156,7 +156,7 @@ static void initialize() // Open database try { - storage = new DALStorage; + storage = new Storage; storage->open(); } catch (std::string &error) { LOG_FATAL("Error opening the database: " << error); diff --git a/src/account-server/serverhandler.cpp b/src/account-server/serverhandler.cpp index eac9415d..1eb2730c 100644 --- a/src/account-server/serverhandler.cpp +++ b/src/account-server/serverhandler.cpp @@ -27,7 +27,7 @@ #include "account-server/accountclient.hpp" #include "account-server/accounthandler.hpp" #include "account-server/character.hpp" -#include "account-server/dalstorage.hpp" +#include "account-server/storage.hpp" #include "chat-server/post.hpp" #include "common/transaction.hpp" #include "common/configuration.hpp" diff --git a/src/account-server/storage.cpp b/src/account-server/storage.cpp new file mode 100644 index 00000000..2964109f --- /dev/null +++ b/src/account-server/storage.cpp @@ -0,0 +1,1998 @@ +/* + * The Mana Server + * Copyright (C) 2004 The Mana World Development Team + * + * This file is part of The Mana Server. + * + * The Mana Server is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * any later version. + * + * The Mana Server is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with The Mana Server. If not, see . + */ + +#include +#include + +#include "account-server/storage.hpp" + +#include "point.h" +#include "account-server/account.hpp" +#include "account-server/storagesql.hpp" +#include "chat-server/chatchannel.hpp" +#include "chat-server/guild.hpp" +#include "chat-server/post.hpp" +#include "common/configuration.hpp" +#include "dal/dalexcept.h" +#include "dal/dataproviderfactory.h" +#include "utils/functors.h" +#include "utils/logger.h" +#include "utils/xml.hpp" + +// TODO: make data/items.xml a constant or read it from config file +#define DEFAULT_ITEM_FILE "data/items.xml" + +// defines the supported db version +#define DB_VERSION_PARAMETER "database_version" +#define SUPPORTED_DB_VERSION "7" + + +/** + * Constructor. + */ +Storage::Storage() + : mDb(dal::DataProviderFactory::createDataProvider()), + mItemDbVersion(0) +{ +} + +/** + * Destructor. + */ +Storage::~Storage() +{ + if (mDb->isConnected()) + close(); + + delete mDb; +} + +/** + * Connect to the database and initialize it if necessary. + */ +void Storage::open() +{ + // Do nothing if already connected. + if (mDb->isConnected()) + return; + + using namespace dal; + + try { + // open a connection to the database. + mDb->connect(); + + // check database version here + std::string dbversion = getWorldStateVar(DB_VERSION_PARAMETER); + if (dbversion != SUPPORTED_DB_VERSION) + { + std::ostringstream errmsg; + errmsg << "Database version is not supported. " << + "Needed version: '" << SUPPORTED_DB_VERSION << + "', current version: '" << dbversion << "'"; + throw errmsg.str(); + } + + // 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; + errmsg << "(DALStorage::open #1) Unable to connect to the database: " + << e.what(); + throw errmsg.str(); + } +} + +/** + * Disconnect from the database. + */ +void Storage::close() +{ + mDb->disconnect(); +} + +/** + * Gets an account from a prepared SQL statement + * + * @return the account found + */ +Account *Storage::getAccountBySQL() +{ + try { + const dal::RecordSet &accountInfo = mDb->processSql(); + + // if the account is not even in the database then + // we have no choice but to return nothing. + if (accountInfo.isEmpty()) + { + return NULL; + } + + // specialize the string_to functor to convert + // a string to an unsigned int. + string_to< unsigned > toUint; + unsigned id = toUint(accountInfo(0, 0)); + + // create an Account instance + // and initialize it with information about the user. + Account *account = new Account(id); + account->setName(accountInfo(0, 1)); + account->setPassword(accountInfo(0, 2)); + account->setEmail(accountInfo(0, 3)); + account->setRegistrationDate(toUint(accountInfo(0, 6))); + account->setLastLogin(toUint(accountInfo(0, 7))); + + int level = toUint(accountInfo(0, 4)); + // Check if the user is permanently banned, or temporarily banned. + if (level == AL_BANNED + || time(NULL) <= (int) toUint(accountInfo(0, 5))) + { + account->setLevel(AL_BANNED); + // It is, so skip character loading. + return account; + } + account->setLevel(level); + + // load the characters associated with the account. + std::ostringstream sql; + sql << "select id from " << CHARACTERS_TBL_NAME << " where user_id = '" + << id << "';"; + const dal::RecordSet &charInfo = mDb->execSql(sql.str()); + + if (!charInfo.isEmpty()) + { + int size = charInfo.rows(); + Characters characters; + + LOG_DEBUG("Account "<< id << " has " << size << " character(s) in database."); + + // Two steps: it seems like multiple requests cannot be alive at the same time. + std::vector< unsigned > characterIDs; + for (int k = 0; k < size; ++k) + { + characterIDs.push_back(toUint(charInfo(k, 0))); + } + + for (int k = 0; k < size; ++k) + { + if (Character *ptr = getCharacter(characterIDs[k], account)) + { + characters.push_back(ptr); + } + else + { + LOG_ERROR("Failed to get character " << characterIDs[k] << " for account " << id << '.'); + } + } + + account->setCharacters(characters); + } + + return account; + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("DALStorage::getAccountBySQL: " << e.what()); + return NULL; // TODO: Throw exception here + } +} + +/** + * Get an account by user name. + * + * @param userName the owner of the account. + * @return the account associated to the user name. + */ +Account *Storage::getAccount(const std::string &userName) +{ + std::ostringstream sql; + sql << "SELECT * FROM " << ACCOUNTS_TBL_NAME << " WHERE username = ?"; + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, userName); + } + return getAccountBySQL(); +} + +/** + * Get an account by ID. + * + * @param accountID the ID of the account. + * + * @return the account associated with the ID. + */ +Account *Storage::getAccount(int accountID) +{ + std::ostringstream sql; + sql << "SELECT * FROM " << ACCOUNTS_TBL_NAME << " WHERE id = ?"; + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, accountID); + } + return getAccountBySQL(); +} + +/** + * Gets a character from a prepared SQL statement + * + * @param owner the account the character is in. + * + * @return the character found by the query. + */ +Character *Storage::getCharacterBySQL(Account *owner) +{ + Character *character; + + // specialize the string_to functor to convert + // a string to an unsigned int. + string_to< unsigned > toUint; + + try { + const dal::RecordSet &charInfo = mDb->processSql(); + + // if the character is not even in the database then + // we have no choice but to return nothing. + if (charInfo.isEmpty()) + { + return NULL; + } + + // specialize the string_to functor to convert + // a string to an unsigned short. + string_to< unsigned short > toUshort; + + character = new Character(charInfo(0, 2), toUint(charInfo(0, 0))); + character->setGender(toUshort(charInfo(0, 3))); + character->setHairStyle(toUshort(charInfo(0, 4))); + character->setHairColor(toUshort(charInfo(0, 5))); + character->setLevel(toUshort(charInfo(0, 6))); + character->setCharacterPoints(toUshort(charInfo(0, 7))); + character->setCorrectionPoints(toUshort(charInfo(0, 8))); + character->getPossessions().money = toUint(charInfo(0, 9)); + Point pos(toUshort(charInfo(0, 10)), toUshort(charInfo(0, 11))); + character->setPosition(pos); + for (int i = 0; i < CHAR_ATTR_NB; ++i) + { + character->setAttribute(CHAR_ATTR_BEGIN + i, + toUshort(charInfo(0, 13 + i))); + } + + int mapId = toUint(charInfo(0, 12)); + if (mapId > 0) + { + character->setMapId(mapId); + } + else + { + // Set character to default map and one of the default location + // Default map is to be 1, as not found return value will be 0. + character->setMapId(Configuration::getValue("defaultMap", 1)); + } + + /* Fill the account-related fields. Last step, as it may require a new + SQL query. */ + if (owner) + { + character->setAccount(owner); + } + else + { + int id = toUint(charInfo(0, 1)); + character->setAccountID(id); + std::ostringstream s; + s << "select level from " << ACCOUNTS_TBL_NAME + << " where id = '" << id << "';"; + const dal::RecordSet &levelInfo = mDb->execSql(s.str()); + character->setAccountLevel(toUint(levelInfo(0, 0)), true); + } + + // load the skills of the char from CHAR_SKILLS_TBL_NAME + std::ostringstream s; + s << "SELECT skill_id, skill_exp " + << "FROM " << CHAR_SKILLS_TBL_NAME << " " + << "WHERE char_id = " << character->getDatabaseID(); + + const dal::RecordSet &skillInfo = mDb->execSql(s.str()); + if (!skillInfo.isEmpty()) + { + const unsigned int nRows = skillInfo.rows(); + for (unsigned int row = 0; row < nRows; row++) + { + character->setExperience( + toUint(skillInfo(row, 0)), // skillid + toUint(skillInfo(row, 1))); // experience + } + } + s.clear(); + s.str(""); + // Load the status effect + s << "select status_id, status_time FROM " << CHAR_STATUS_EFFECTS_TBL_NAME + << " WHERE char_id = " << character->getDatabaseID(); + const dal::RecordSet &statusInfo = mDb->execSql(s.str()); + if (!statusInfo.isEmpty()) + { + const unsigned int nRows = statusInfo.rows(); + for (unsigned int row = 0; row < nRows; row++) + { + character->applyStatusEffect( + toUint(statusInfo(row, 0)), // Statusid + toUint(statusInfo(row, 1))); // Time + } + } + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::getCharacter #1) SQL query failure: " << e.what()); + return NULL; + } + + try + { + std::ostringstream sql; + sql << " select * from " << INVENTORIES_TBL_NAME << " where owner_id = '" + << character->getDatabaseID() << "' order by slot asc;"; + + const dal::RecordSet &itemInfo = mDb->execSql(sql.str()); + if (!itemInfo.isEmpty()) + { + Possessions &poss = character->getPossessions(); + unsigned nextSlot = 0; + + for (int k = 0, size = itemInfo.rows(); k < size; ++k) + { + unsigned slot = toUint(itemInfo(k, 2)); + if (slot < EQUIPMENT_SLOTS) + { + poss.equipment[slot] = toUint(itemInfo(k, 3)); + } + else + { + slot -= 32; + if (slot >= INVENTORY_SLOTS || slot < nextSlot) + { + LOG_ERROR("(DALStorage::getCharacter #2) Corrupted inventory."); + break; + } + InventoryItem item; + if (slot != nextSlot) + { + item.itemId = 0; + item.amount = slot - nextSlot; + poss.inventory.push_back(item); + } + item.itemId = toUint(itemInfo(k, 3)); + item.amount = toUint(itemInfo(k, 4)); + poss.inventory.push_back(item); + nextSlot = slot + 1; + } + } + } + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::getCharacter #2) SQL query failure: " << e.what()); + return NULL; + } + + return character; +} + +/** + * Gets a character by database ID. + * + * @param id the ID of the character. + * @param owner the account the character is in. + * + * @return the character associated to the ID. + */ +Character *Storage::getCharacter(int id, Account *owner) +{ + std::ostringstream sql; + sql << "SELECT * FROM " << CHARACTERS_TBL_NAME << " WHERE id = ?"; + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, id); + } + return getCharacterBySQL(owner); +} + +/** + * Gets a character by character name. + * + * @param name of the character + * + * @return the character associated to the name + */ +Character *Storage::getCharacter(const std::string &name) +{ + std::ostringstream sql; + sql << "SELECT * FROM " << CHARACTERS_TBL_NAME << " WHERE name = ?"; + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, name); + } + return getCharacterBySQL(NULL); +} + +/** + * Tells if the user name already exists. + * @return true if the user name exists. + */ +bool Storage::doesUserNameExist(const std::string &name) +{ + try { + std::ostringstream sql; + sql << "SELECT COUNT(username) FROM " << ACCOUNTS_TBL_NAME + << " WHERE username = ?"; + + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, name); + } + const dal::RecordSet &accountInfo = mDb->processSql(); + + std::istringstream ssStream(accountInfo(0, 0)); + unsigned int iReturn = 1; + ssStream >> iReturn; + return iReturn != 0; + } catch (const std::exception &e) { + // TODO: throw an exception. + LOG_ERROR("(DALStorage::doesUserNameExist) SQL query failure: " << e.what()); + } + + return true; +} + +/** + * Tells if the email address already exists. + * @return true if the email address exists. + */ +bool Storage::doesEmailAddressExist(const std::string &email) +{ + try { + std::ostringstream sql; + sql << "SELECT COUNT(email) FROM " << ACCOUNTS_TBL_NAME + << " WHERE UPPER(email) = UPPER(?)"; + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, email); + } + const dal::RecordSet &accountInfo = mDb->processSql(); + + std::istringstream ssStream(accountInfo(0, 0)); + unsigned int iReturn = 1; + ssStream >> iReturn; + return iReturn != 0; + } catch (const std::exception &e) { + // TODO: throw an exception. + LOG_ERROR("(DALStorage::doesEmailAddressExist) SQL query failure: " << e.what()); + } + + return true; +} + +/** + * Tells if the character's name already exists. + * @return true if character's name exists. + */ +bool Storage::doesCharacterNameExist(const std::string& name) +{ + try { + std::ostringstream sql; + sql << "SELECT COUNT(name) FROM " << CHARACTERS_TBL_NAME << " WHERE name = ?"; + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, name); + } + const dal::RecordSet &accountInfo = mDb->processSql(); + + std::istringstream ssStream(accountInfo(0, 0)); + int iReturn = 1; + ssStream >> iReturn; + return iReturn != 0; + } catch (const std::exception &e) { + // TODO: throw an exception. + LOG_ERROR("(DALStorage::doesCharacterNameExist) SQL query failure: " + << e.what()); + } + + return true; +} + +/** + * Updates the data for a single character, does not update the owning account + * or the characters name. Primary usage should be storing characterdata + * received from a game server. + * + * @param ptr Character to store values in the database. + * @param startTransaction set to false if this method is called as + * nested transaction. + * @return true on success + */ +bool Storage::updateCharacter(Character *character, + bool startTransaction) +{ + // Update the database Character data (see CharacterData for details) + if (startTransaction) + { + mDb->beginTransaction(); + } + try + { + std::ostringstream sqlUpdateCharacterInfo; + sqlUpdateCharacterInfo + << "update " << CHARACTERS_TBL_NAME << " " + << "set " + << "gender = '" << character->getGender() << "', " + << "hair_style = '" << character->getHairStyle() << "', " + << "hair_color = '" << character->getHairColor() << "', " + << "level = '" << character->getLevel() << "', " + << "char_pts = '" << character->getCharacterPoints() << "', " + << "correct_pts = '"<< character->getCorrectionPoints() << "', " + << "money = '" << character->getPossessions().money << "', " + << "x = '" << character->getPosition().x << "', " + << "y = '" << character->getPosition().y << "', " + << "map_id = '" << character->getMapId() << "', " + << "str = '" << character->getAttribute(CHAR_ATTR_STRENGTH) << "', " + << "agi = '" << character->getAttribute(CHAR_ATTR_AGILITY) << "', " + << "dex = '" << character->getAttribute(CHAR_ATTR_DEXTERITY) << "', " + << "vit = '" << character->getAttribute(CHAR_ATTR_VITALITY) << "', " +#if defined(MYSQL_SUPPORT) || defined(POSTGRESQL_SUPPORT) + << "`int` = '" +#else + << "int = '" +#endif + << character->getAttribute(CHAR_ATTR_INTELLIGENCE) << "', " + << "will = '" << character->getAttribute(CHAR_ATTR_WILLPOWER) << "' " + << "where id = '" << character->getDatabaseID() << "';"; + + mDb->execSql(sqlUpdateCharacterInfo.str()); + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception. + if (startTransaction) + { + mDb->rollbackTransaction(); + } + LOG_ERROR("(DALStorage::updateCharacter #1) SQL query failure: " << e.what()); + return false; + } + + /** + * Character's skills + */ + try + { + std::map::const_iterator skill_it; + for (skill_it = character->getSkillBegin(); + skill_it != character->getSkillEnd(); skill_it++) + { + updateExperience(character->getDatabaseID(), skill_it->first, skill_it->second); + } + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception. + if (startTransaction) + { + mDb->rollbackTransaction(); + } + LOG_ERROR("(DALStorage::updateCharacter #2) SQL query failure: " << e.what()); + return false; + } + + + /** + * Character's inventory + */ + + // Delete the old inventory first + try + { + std::ostringstream sqlDeleteCharacterInventory; + sqlDeleteCharacterInventory + << "delete from " << INVENTORIES_TBL_NAME + << " where owner_id = '" << character->getDatabaseID() << "';"; + mDb->execSql(sqlDeleteCharacterInventory.str()); + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception. + if (startTransaction) + { + mDb->rollbackTransaction(); + } + LOG_ERROR("(DALStorage::updateCharacter #3) SQL query failure: " << e.what()); + return false; + } + + // Insert the new inventory data + try + { + std::ostringstream sql; + + sql << "insert into " << INVENTORIES_TBL_NAME + << " (owner_id, slot, class_id, amount) values (" + << character->getDatabaseID() << ", "; + std::string base = sql.str(); + + const Possessions &poss = character->getPossessions(); + + for (int j = 0; j < EQUIPMENT_SLOTS; ++j) + { + int v = poss.equipment[j]; + if (!v) continue; + sql.str(std::string()); + sql << base << j << ", " << v << ", 1);"; + mDb->execSql(sql.str()); + } + + int slot = 32; + for (std::vector< InventoryItem >::const_iterator j = poss.inventory.begin(), + j_end = poss.inventory.end(); j != j_end; ++j) + { + int v = j->itemId; + if (!v) + { + slot += j->amount; + continue; + } + sql.str(std::string()); + sql << base << slot << ", " << v << ", " << unsigned(j->amount) << ");"; + mDb->execSql(sql.str()); + ++slot; + } + + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception. + if (startTransaction) + { + mDb->rollbackTransaction(); + } + LOG_ERROR("(DALStorage::updateCharacter #4) SQL query failure: " << e.what()); + return false; + } + + /** + * Update char status effects + */ + try + { + // Delete the old status effects first + std::ostringstream sql; + + sql << "delete from " << CHAR_STATUS_EFFECTS_TBL_NAME + << " where char_id = '" << character->getDatabaseID() << "';"; + + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception. + if (startTransaction) + { + mDb->rollbackTransaction(); + } + LOG_ERROR("(DALStorage::updateCharacter #5) SQL query failure: " << e.what()); + return false; + } + try + { + std::map::const_iterator status_it; + for (status_it = character->getStatusEffectBegin(); + status_it != character->getStatusEffectEnd(); status_it++) + { + insertStatusEffect(character->getDatabaseID(), status_it->first, status_it->second); + } + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception + if (startTransaction) + { + mDb->rollbackTransaction(); + } + LOG_ERROR("(DALStorage::updateCharacter #6) SQL query failure: " << e.what()); + return false; + } + if (startTransaction) + { + mDb->commitTransaction(); + } + return true; +} + +/** + * Save changes of a skill to the database permanently. + * + * @param character Character thats skill has changed. + * @param skill_id Identifier of the changed skill. + * + * @exception dbl::DbSqlQueryExecFailure. + * @deprecated Use DALStorage::updateExperience instead!!! +*/ +void Storage::flushSkill(const Character *character, int skill_id) +{ + updateExperience(character->getDatabaseID(), skill_id, + character->getExperience(skill_id)); +} + +/** + * Add an account to the database. + * @param account the new account. + */ +void Storage::addAccount(Account *account) +{ + assert(account->getCharacters().size() == 0); + + using namespace dal; + + mDb->beginTransaction(); + try + { + // insert the account + std::ostringstream sql; + sql << "insert into " << ACCOUNTS_TBL_NAME + << " (username, password, email, level, banned, registration, lastlogin)" + << " VALUES (?, ?, ?, " + << account->getLevel() << ", 0, " + << account->getRegistrationDate() << ", " + << account->getLastLogin() << ");"; + + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, account->getName()); + mDb->bindValue(2, account->getPassword()); + mDb->bindValue(3, account->getEmail()); + } + + mDb->processSql(); + account->setID(mDb->getLastId()); + + mDb->commitTransaction(); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("Error in DALStorage::addAccount: " << e.what()); + mDb->rollbackTransaction(); + } +} + +/** + * Update an account from the database. + */ +void Storage::flush(Account *account) +{ + assert(account->getID() >= 0); + + using namespace dal; + + mDb->beginTransaction(); + try + { + // 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) + { + if ((*it)->getDatabaseID() >= 0) + { + /* 2nd. parameter false means: don't start a transaction in + the updateCharacter method, cause we did this already a few + lines above */ + updateCharacter(*it, false); + } + else + { + 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 ) 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) << " " + << ");"; + + mDb->execSql(sqlInsertCharactersTable.str()); + + // Update the character ID. + (*it)->setDatabaseID(mDb->getLastId()); + + // update the characters skill + std::map::const_iterator skill_it; + for (skill_it = (*it)->getSkillBegin(); + skill_it != (*it)->getSkillEnd(); skill_it++) + { + updateExperience((*it)->getDatabaseID(), skill_it->first, skill_it->second); + } + } + } // + + // 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 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 + { + 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); + } + } + + mDb->commitTransaction(); + } + catch (const std::exception &e) + { + LOG_ERROR("ERROR in DALStorage::flush: " << e.what()); + mDb->rollbackTransaction(); + } +} + +/** + * Delete an account and its associated data from the database. + * + * @param account the account to delete. + */ +void Storage::delAccount(Account *account) +{ + account->setCharacters(Characters()); + flush(account); + + // delete the account. + std::ostringstream sql; + sql << "delete from " << ACCOUNTS_TBL_NAME + << " where id = '" << account->getID() << "';"; + mDb->execSql(sql.str()); +} + +/** + * Update the date and time of the last login. + * + * @param account the account that recently logged in. + */ +void Storage::updateLastLogin(const Account *account) +{ + std::ostringstream sql; + sql << "UPDATE " << ACCOUNTS_TBL_NAME + << " SET lastlogin = '" << account->getLastLogin() << "'" + << " WHERE id = '" << account->getID() << "';"; + mDb->execSql(sql.str()); +} + +/** + * Write a modification message about Character points to the database. + * + * @param CharId ID of the character + * @param CharPoints Number of character points left for the character + * @param CorrPoints Number of correction points left for the character + * @param AttribId ID of the modified attribute + * @param AttribValue New value of the modified attribute + */ +void Storage::updateCharacterPoints(int charId, + int charPoints, int corrPoints, + int attribId, int attribValue) +{ + std::ostringstream sql; + sql << "UPDATE " << CHARACTERS_TBL_NAME + << " SET char_pts = " << charPoints << ", " + << " correct_pts = " << corrPoints << ", "; + + switch (attribId) + { + case CHAR_ATTR_STRENGTH: sql << "str = "; break; + case CHAR_ATTR_AGILITY: sql << "agi = "; break; + case CHAR_ATTR_DEXTERITY: sql << "dex = "; break; + case CHAR_ATTR_VITALITY: sql << "vit = "; break; + case CHAR_ATTR_INTELLIGENCE: sql << "int = "; break; + case CHAR_ATTR_WILLPOWER: sql << "will = "; break; + } + sql << attribValue + << " WHERE id = " << charId; + + mDb->execSql(sql.str()); +} + +/** + * Write a modification message about character skills to the database. + * @param CharId ID of the character + * @param SkillId ID of the skill + * @param SkillValue new skill points + */ +void Storage::updateExperience(int charId, int skillId, int skillValue) +{ + try + { + // if experience has decreased to 0 we don't store it anymore, + // its the default + if (skillValue == 0) + { + std::ostringstream sql; + sql << "DELETE FROM " << CHAR_SKILLS_TBL_NAME + << " WHERE char_id = " << charId + << " AND skill_id = " << skillId; + mDb->execSql(sql.str()); + return; + } + + // try to update the skill + std::ostringstream sql; + sql << "UPDATE " << CHAR_SKILLS_TBL_NAME + << " SET skill_exp = " << skillValue + << " WHERE char_id = " << charId + << " AND skill_id = " << skillId; + mDb->execSql(sql.str()); + + // check if the update has modified a row + if (mDb->getModifiedRows() > 0) + { + return; + } + + sql.clear(); + sql.str(""); + sql << "INSERT INTO " << CHAR_SKILLS_TBL_NAME << " " + << "(char_id, skill_id, skill_exp) VALUES ( " + << charId << ", " + << skillId << ", " + << skillValue << ")"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("DALStorage::updateExperience: " << e.what()); + throw; + } +} + +/** + * Inserts a record about a status effect into the database + * @param charId ID of the character in the database + * @param statusId ID of the status effect + * @param time Time left on the status effect + */ +void Storage::insertStatusEffect(int charId, int statusId, int time) +{ + try + { + std::ostringstream sql; + + sql << "insert into " << CHAR_STATUS_EFFECTS_TBL_NAME + << " (char_id, status_id, status_time) VALUES ( " + << charId << ", " + << statusId << ", " + << time << ")"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("DALStorage::insertStatusEffect: " << e.what()); + throw; + } +} + + +/** + * Add a new guild. + */ +void Storage::addGuild(Guild *guild) +{ + std::ostringstream insertSql; + insertSql << "insert into " << GUILDS_TBL_NAME + << " (name) VALUES (?)"; + if (mDb->prepareSql(insertSql.str())) + { + mDb->bindValue(1, guild->getName()); + } + //mDb->execSql(insertSql.str()); + mDb->processSql(); + + std::ostringstream selectSql; + selectSql << "SELECT id FROM " << GUILDS_TBL_NAME + << " WHERE name = ?"; + + if (mDb->prepareSql(selectSql.str())) + { + mDb->bindValue(1, guild->getName()); + } + //const dal::RecordSet& guildInfo = mDb->execSql(selectSql.str()); + const dal::RecordSet& guildInfo = mDb->processSql(); + + string_to toUint; + unsigned id = toUint(guildInfo(0, 0)); + guild->setId(id); +} + +/** + * Delete a guild. + */ +void Storage::removeGuild(Guild *guild) +{ + std::ostringstream sql; + sql << "delete from " << GUILDS_TBL_NAME + << " where id = '" + << guild->getId() << "';"; + mDb->execSql(sql.str()); +} + +/** + * Add member to guild. + */ +void Storage::addGuildMember(int guildId, int memberId) +{ + std::ostringstream sql; + + try + { + sql << "insert into " << GUILD_MEMBERS_TBL_NAME + << " (guild_id, member_id, rights)" + << " values (" + << guildId << ", \"" + << memberId << "\", " + << 0 << ");"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure& e) { + // TODO: throw an exception. + LOG_ERROR("SQL query failure: " << e.what()); + } +} + +/** + * Remove member from guild. + */ +void Storage::removeGuildMember(int guildId, int memberId) +{ + std::ostringstream sql; + + try + { + sql << "delete from " << GUILD_MEMBERS_TBL_NAME + << " where member_id = \"" + << memberId << "\" and guild_id = '" + << guildId << "';"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception. + LOG_ERROR("SQL query failure: " << e.what()); + } +} + +/** + * Save guild member rights. + */ +void Storage::setMemberRights(int guildId, int memberId, int rights) +{ + std::ostringstream sql; + + try + { + sql << "update " << GUILD_MEMBERS_TBL_NAME + << " set rights = '" << rights << "'" + << " where member_id = \"" + << memberId << "\";"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception. + LOG_ERROR("SQL query failure: " << e.what()); + } +} + +/** + * Get the list of guilds. + * @return a list of guilds + */ +std::list Storage::getGuildList() +{ + std::list guilds; + std::stringstream sql; + string_to toShort; + + /** + * Get the guilds stored in the db. + */ + + try + { + sql << "select id, name from " << GUILDS_TBL_NAME << ";"; + const dal::RecordSet& guildInfo = mDb->execSql(sql.str()); + + // check that at least 1 guild was returned + if (guildInfo.isEmpty()) + { + return guilds; + } + + // loop through every row in the table and assign it to a guild + for ( unsigned int i = 0; i < guildInfo.rows(); ++i) + { + Guild* guild = new Guild(guildInfo(i,1)); + guild->setId(toShort(guildInfo(i,0))); + guilds.push_back(guild); + } + string_to< unsigned > toUint; + + /** + * Add the members to the guilds. + */ + for (std::list::iterator itr = guilds.begin(); + itr != guilds.end(); + ++itr) + { + std::ostringstream memberSql; + memberSql << "select member_id, rights from " << GUILD_MEMBERS_TBL_NAME + << " where guild_id = '" << (*itr)->getId() << "';"; + const dal::RecordSet& memberInfo = mDb->execSql(memberSql.str()); + + std::list > members; + for (unsigned int j = 0; j < memberInfo.rows(); ++j) + { + members.push_back(std::pair(toUint(memberInfo(j, 0)), toUint(memberInfo(j, 1)))); + } + + for (std::list >::const_iterator i = members.begin(); + i != members.end(); + ++i) + { + Character *character = getCharacter((*i).first, NULL); + if (character) + { + character->addGuild((*itr)->getName()); + (*itr)->addMember(character->getDatabaseID(), (*i).second); + } + } + } + } + catch (const dal::DbSqlQueryExecFailure& e) { + // TODO: throw an exception. + LOG_ERROR("SQL query failure: " << e.what()); + } + + return guilds; +} + +/** + * Gets the value of a quest variable. + */ +std::string Storage::getQuestVar(int id, const std::string &name) +{ + try + { + std::ostringstream query; + query << "select value from " << QUESTS_TBL_NAME + << " WHERE owner_id = ? AND name = ?"; + if (mDb->prepareSql(query.str())) + { + mDb->bindValue(1, id); + mDb->bindValue(2, name); + } + const dal::RecordSet &info = mDb->processSql(); + + if (!info.isEmpty()) return info(0, 0); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::getQuestVar) SQL query failure: " << e.what()); + } + + return std::string(); +} + +/** + * Gets the string value of a map specific world state variable. + * + * @param name Name of the requested world-state variable. + * @param map_id Id of the specific map. + */ +std::string Storage::getWorldStateVar(const std::string &name, int map_id) +{ + try + { + std::ostringstream query; + query << "SELECT value " + << " FROM " << WORLD_STATES_TBL_NAME + << " WHERE state_name = '" << name << "'"; + + // add map filter if map_id is given + if (map_id >= 0) + { + query << " AND map_id = '" << map_id << "'"; + } + + query << ";"; + const dal::RecordSet &info = mDb->execSql(query.str()); + + if (!info.isEmpty()) return info(0, 0); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::getWorldStateVar) SQL query failure: " << e.what()); + } + + return std::string(); +} + +/** + * Sets the value of a world state variable. + * + * @param name Name of the world-state vairable. + * @param value New value of the world-state variable. + */ +void Storage::setWorldStateVar(const std::string &name, + const std::string &value) +{ + return setWorldStateVar(name, -1, value); +} + +/** + * Sets the value of a world state variable of a specific map. + * + * @param name Name of the world-state vairable. + * @param mapId ID of the specific map + * @param value New value of the world-state variable. + */ +void Storage::setWorldStateVar(const std::string &name, + int mapId, + const std::string &value) +{ + try + { + // 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 (mapId >= 0) + { + deleteStateVar << " AND map_id = '" << mapId << "'"; + } + 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 (mapId >= 0) + { + updateStateVar << " AND map_id = '" << mapId << "'"; + } + updateStateVar << ";"; + mDb->execSql(updateStateVar.str()); + + // if we updated a row, were finished here + if (mDb->getModifiedRows() >= 1) + { + return; + } + + // 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 (mapId >= 0) + { + insertStateVar << "'" << mapId << "', "; + } + else + { + insertStateVar << "NULL , "; + } + insertStateVar << "'" << value << "', " + << "'" << time(NULL) << "');"; + mDb->execSql(insertStateVar.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::setWorldStateVar) SQL query failure: " << e.what()); + } +} + +/** + * Sets the value of a quest variable. + */ +void Storage::setQuestVar(int id, const std::string &name, + const std::string &value) +{ + try + { + std::ostringstream query1; + query1 << "delete from " << QUESTS_TBL_NAME + << " where owner_id = '" << id << "' and name = '" + << name << "';"; + mDb->execSql(query1.str()); + + if (value.empty()) return; + + std::ostringstream query2; + query2 << "insert into " << QUESTS_TBL_NAME + << " (owner_id, name, value) values ('" + << id << "', '" << name << "', '" << value << "');"; + mDb->execSql(query2.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::setQuestVar) SQL query failure: " << e.what()); + } +} + +/** + * Sets a ban on an account (hence on all its characters). + * + * @param id character identifier. + * @param duration duration in minutes. + */ +void Storage::banCharacter(int id, int duration) +{ + try + { + std::ostringstream query; + query << "select user_id from " << CHARACTERS_TBL_NAME + << " where id = '" << id << "';"; + const dal::RecordSet &info = mDb->execSql(query.str()); + if (info.isEmpty()) + { + LOG_ERROR("Tried to ban an unknown user."); + return; + } + + std::ostringstream sql; + sql << "update " << ACCOUNTS_TBL_NAME + << " set level = '" << AL_BANNED << "', banned = '" + << time(NULL) + duration * 60 + << "' where id = '" << info(0, 0) << "';"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::banAccount) SQL query failure: " << e.what()); + } +} + +/** + * Delete a character in the database. + * + * @param charId character identifier. + * @param startTransaction indicates wheter the function should run in + * its own transaction or is called inline of another transaction + */ +void Storage::delCharacter(int charId, bool startTransaction = true) const +{ + if (startTransaction) + mDb->beginTransaction(); + try + { + std::ostringstream sql; + + // delete the inventory of the character + sql << "DELETE FROM " << INVENTORIES_TBL_NAME + << " WHERE owner_id = '" << charId << "';"; + mDb->execSql(sql.str()); + + // delete the skills of the character + sql.clear(); + sql.str(""); + sql << "DELETE FROM " << CHAR_SKILLS_TBL_NAME + << " WHERE char_id = '" << charId << "';"; + mDb->execSql(sql.str()); + + // delete from the quests table + sql.clear(); + sql.str(""); + sql << "DELETE FROM " << QUESTS_TBL_NAME + << " WHERE owner_id = '" << charId << "';"; + mDb->execSql(sql.str()); + + // delete from the guilds table + sql.clear(); + sql.str(""); + sql << "DELETE FROM " << GUILD_MEMBERS_TBL_NAME + << " WHERE member_id = '" << charId << "';"; + mDb->execSql(sql.str()); + + // delete auctions of the character + sql.clear(); + sql.str(""); + sql << "DELETE FROM " << AUCTION_TBL_NAME + << " WHERE char_id = '" << charId << "';"; + mDb->execSql(sql.str()); + + // delete bids made on auctions made by the character + sql.clear(); + sql.str(""); + sql << "DELETE FROM " << AUCTION_BIDS_TBL_NAME + << " WHERE char_id = '" << charId << "';"; + mDb->execSql(sql.str()); + + // now delete the character itself. + sql.clear(); + sql.str(""); + sql << "DELETE FROM " << CHARACTERS_TBL_NAME + << " WHERE id = '" << charId << "';"; + mDb->execSql(sql.str()); + + if (startTransaction) + mDb->commitTransaction(); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + if (startTransaction) + mDb->rollbackTransaction(); + LOG_ERROR("(DALStorage::delCharacter) SQL query failure: " << e.what()); + } +} + +/** + * Delete a character in the database. The object itself is not touched + * by this function! + * + * @param character character object. + * @param startTransaction indicates wheter the function should run in + * its own transaction or is called inline of another transaction + */ +void Storage::delCharacter(Character *character, + bool startTransaction = true) const +{ + delCharacter(character->getDatabaseID(), startTransaction); +} + +/** + * Removes expired bans from accounts + */ +void Storage::checkBannedAccounts() +{ + try + { + // update expired bans + std::ostringstream sql; + sql << "update " << ACCOUNTS_TBL_NAME + << " set level = " << AL_PLAYER << ", banned = 0" + << " where level = " << AL_BANNED + << " AND banned <= " << time(NULL) << ";"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::checkBannedAccounts) SQL query failure: " << e.what()); + } +} + +/** + * Set the level on an account. + * + * @param id The id of the account + * @param level The level to set for the account + */ +void Storage::setAccountLevel(int id, int level) +{ + try + { + std::ostringstream sql; + sql << "update " << ACCOUNTS_TBL_NAME + << " set level = " << level + << " where id = " << id << ";"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::setAccountLevel) SQL query failure: " << e.what()); + } +} + +/** + * Set the level on a character. + * + * @param id The id of the character + * @param level The level to set for the character + */ +void Storage::setPlayerLevel(int id, int level) +{ + try + { + std::ostringstream sql; + sql << "update " << CHARACTERS_TBL_NAME + << " set level = " << level + << " where id = " << id << ";"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::setPlayerLevel) SQL query failure: " << e.what()); + } +} + +/** + * Store letter. + * + * @param letter The letter to store + */ +void Storage::storeLetter(Letter *letter) +{ + std::ostringstream sql; + if (letter->getId() == 0) + { + // the letter was never saved before + sql << "INSERT INTO " << POST_TBL_NAME << " VALUES ( " + << "NULL, " + << letter->getSender()->getDatabaseID() << ", " + << letter->getReceiver()->getDatabaseID() << ", " + << letter->getExpiry() << ", " + << time(NULL) << ", " + << "?)"; + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, letter->getContents()); + } + + mDb->processSql(); + letter->setId(mDb->getLastId()); + + // TODO: store attachments in the database + + return; + } + else + { + // the letter has a unique id, update the record in the db + sql << "UPDATE " << POST_TBL_NAME + << " SET sender_id = '" << letter->getSender()->getDatabaseID() << "', " + << " receiver_id = '" << letter->getReceiver()->getDatabaseID() << "', " + << " letter_type = '" << letter->getType() << "', " + << " expiration_date = '" << letter->getExpiry() << "', " + << " sending_date = '" << time(NULL) << "', " + << " letter_text = ? " + << " WHERE letter_id = '" << letter->getId() << "'"; + + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, letter->getContents()); + } + mDb->processSql(); + + if (mDb->getModifiedRows() == 0) + { + // this should never happen... + LOG_ERROR("(DALStorage::storePost) trying to update nonexistant letter"); + throw "(DALStorage::storePost) trying to update nonexistant letter"; + } + + // TODO: update attachments in the database + } +} + +/** + * Retrieve post + * + * @param playerId The id of the character requesting his post + */ +Post *Storage::getStoredPost(int playerId) +{ + Post *p = new Post(); + // specialize the string_to functor to convert + // a string to an unsigned int. + string_to< unsigned > toUint; + + std::ostringstream sql; + sql << "SELECT * FROM " << POST_TBL_NAME + << " WHERE receiver_id = " << playerId; + + const dal::RecordSet &post = mDb->execSql(sql.str()); + + if (post.isEmpty()) + { + // there is no post waiting for the character + return p; + } + + for (unsigned int i = 0; i < post.rows(); i++ ) + { + // load sender and receiver + Character *sender = getCharacter(toUint(post(i, 1)), NULL); + Character *receiver = getCharacter(toUint(post(i, 2)), NULL); + + Letter *letter = new Letter(toUint( post(0,3) ), sender, receiver); + + letter->setId( toUint(post(0, 0)) ); + letter->setExpiry( toUint(post(0, 4)) ); + letter->addText( post(0, 6) ); + + // TODO: load attachments per letter from POST_ATTACHMENTS_TBL_NAME + // needs redesign of struct ItemInventroy + + p->addLetter(letter); + } + + return p; +} + +/** + * Delete a letter from the database. + * @param letter The letter to delete. + */ +void Storage::deletePost(Letter *letter) +{ + mDb->beginTransaction(); + + try + { + std::ostringstream sql; + + // first delete all attachments of the letter + // this could leave "dead" items in the item_instances table + sql << "DELETE FROM " << POST_ATTACHMENTS_TBL_NAME + << " WHERE letter_id = " << letter->getId(); + mDb->execSql(sql.str()); + + // delete the letter itself + sql.clear(); + sql.str(""); + sql << "DELETE FROM " << POST_TBL_NAME + << " WHERE letter_id = " << letter->getId(); + mDb->execSql(sql.str()); + + mDb->commitTransaction(); + letter->setId(0); + } + catch(const dal::DbSqlQueryExecFailure &e) + { + mDb->rollbackTransaction(); + LOG_ERROR("(DALStorage::deletePost) SQL query failure: " << e.what()); + } +} + +/** + * Synchronizes the base data in the connected SQL database with the xml + * files like items.xml. + * This method is called once after initialization of DALStorage. + * Probably this function should be called if a gm requests an online + * reload of the xml files to load new items or monsters without server + * restart. + */ +void Storage::syncDatabase() +{ + xmlDocPtr doc = xmlReadFile(DEFAULT_ITEM_FILE, NULL, 0); + if (!doc) + { + LOG_ERROR("Item Manager: Error while parsing item database (items.xml)!"); + return; + } + + xmlNodePtr node = xmlDocGetRootElement(doc); + if (!node || !xmlStrEqual(node->name, BAD_CAST "items")) + { + LOG_ERROR("Item Manager:(items.xml) is not a valid database file!"); + xmlFreeDoc(doc); + return; + } + + mDb->beginTransaction(); + int itmCount = 0; + for (node = node->xmlChildrenNode; node != NULL; node = node->next) + { + // Try to load the version of the item database. The version is defined + // as subversion tag embedded as XML attribute. So every modification + // to the items.xml file will increase the revision automatically. + if (xmlStrEqual(node->name, BAD_CAST "version")) + { + std::string revision = XML::getProperty(node, "revision", std::string()); + mItemDbVersion = atoi(revision.c_str()); + LOG_INFO("Loading item database version " << mItemDbVersion); + } + + if (!xmlStrEqual(node->name, BAD_CAST "item")) + { + continue; + } + + if (xmlStrEqual(node->name, BAD_CAST "item")) + { + int id = XML::getProperty(node, "id", 0); + if (id < 500) + { + continue; + } + + int weight = XML::getProperty(node, "weight", 0); + std::string type = XML::getProperty(node, "type", ""); + std::string name = XML::getProperty(node, "name", ""); + std::string desc = XML::getProperty(node, "description", ""); + std::string eff = XML::getProperty(node, "effect", ""); + std::string image = XML::getProperty(node, "image", ""); + std::string dye(""); + + // split image name and dye string + size_t pipe = image.find("|"); + if (pipe != std::string::npos) + { + dye = image.substr(pipe + 1); + image = image.substr(0, pipe); + } + + try + { + std::ostringstream sql; + sql << "UPDATE " << ITEMS_TBL_NAME + << " SET name = ?, " + << " description = ?, " + << " image = '" << image << "', " + << " weight = " << weight << ", " + << " itemtype = '" << type << "', " + << " effect = ?, " + << " dyestring = '" << dye << "' " + << " WHERE id = " << id; + + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, name); + mDb->bindValue(2, desc); + mDb->bindValue(3, eff); + } + mDb->processSql(); + if (mDb->getModifiedRows() == 0) + { + sql.clear(); + sql.str(""); + sql << "INSERT INTO " << ITEMS_TBL_NAME + << " VALUES ( " << id << ", ?, ?, '" + << image << "', " << weight << ", '" + << type << "', ?, '" << dye << "' )"; + //mDb->execSql(sql.str()); + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, name); + mDb->bindValue(2, desc); + mDb->bindValue(3, eff); + } + mDb->processSql(); + } + itmCount++; + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::SyncDatabase) SQL query failure: " << e.what()); + } + } + } + + mDb->commitTransaction(); + xmlFreeDoc(doc); +} + +/** + * 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 Storage::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 (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::setOnlineStatus) SQL query failure: " << e.what()); + } +} + +/** + * Store a transaction. + */ +void Storage::addTransaction(const Transaction &trans) +{ + try + { + std::stringstream sql; + sql << "INSERT INTO " << TRANSACTION_TBL_NAME + << " VALUES (NULL, " << trans.mCharacterId << ", " + << trans.mAction << ", " + << "?, " + << time(NULL) << ")"; + if (mDb->prepareSql(sql.str())) + { + mDb->bindValue(1, trans.mMessage); + } + mDb->processSql(); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::addTransaction) SQL query failure: " << e.what()); + } +} + +/** + * Retrieve the last \num transactions that were stored. + */ +std::vector Storage::getTransactions(unsigned int num) +{ + std::vector transactions; + string_to toUint; + + try + { + std::stringstream sql; + sql << "SELECT * FROM " << TRANSACTION_TBL_NAME; + const dal::RecordSet &rec = mDb->execSql(sql.str()); + + int size = rec.rows(); + int start = size - num; + // Get the last records and store them in transactions + for (int i = start; i < size; ++i) + { + Transaction trans; + trans.mCharacterId = toUint(rec(i, 1)); + trans.mAction = toUint(rec(i, 2)); + trans.mMessage = rec(i, 3); + transactions.push_back(trans); + } + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::getTransactions) SQL query failure: " << e.what()); + } + + return transactions; +} + +/** + * Retrieve all transactions since the given \a date. + */ +std::vector Storage::getTransactions(time_t date) +{ + std::vector transactions; + string_to toUint; + + try + { + std::stringstream sql; + sql << "SELECT * FROM " << TRANSACTION_TBL_NAME << " WHERE time > " + << date; + const dal::RecordSet &rec = mDb->execSql(sql.str()); + + for (unsigned int i = 0; i < rec.rows(); ++i) + { + Transaction trans; + trans.mCharacterId = toUint(rec(i, 1)); + trans.mAction = toUint(rec(i, 2)); + trans.mMessage = rec(i, 3); + transactions.push_back(trans); + } + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("(DALStorage::getTransactions) SQL query failure: " << e.what()); + } + + return transactions; +} diff --git a/src/account-server/storage.hpp b/src/account-server/storage.hpp new file mode 100644 index 00000000..7c725ca2 --- /dev/null +++ b/src/account-server/storage.hpp @@ -0,0 +1,144 @@ +/* + * The Mana Server + * Copyright (C) 2004 The Mana World Development Team + * + * This file is part of The Mana Server. + * + * The Mana Server is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * any later version. + * + * The Mana Server is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with The Mana Server. If not, see . + */ + +#ifndef STORAGE_H +#define STORAGE_H + +#include +#include +#include + +#include "dal/dataprovider.h" + +#include "common/transaction.hpp" + +class Account; +class Character; +class ChatChannel; +class Guild; +class Letter; +class Post; + +/** + * The high level interface to the database. Through the storage you can access + * all accounts, characters, guilds, worlds states, transactions, etc. + */ +class Storage +{ + public: + Storage(); + ~Storage(); + + void open(); + void close(); + + Account *getAccount(const std::string &userName); + Account *getAccount(int accountID); + + Character *getCharacter(int id, Account *owner); + Character *getCharacter(const std::string &name); + + void addAccount(Account *account); + void delAccount(Account *account); + + void updateLastLogin(const Account *account); + + void updateCharacterPoints(int charId, + int charPoints, int corrPoints, + int attribId, int attribValue); + + void updateExperience(int charId, int skillId, int skillValue); + + void insertStatusEffect(int charId, int statusId, int time); + + void banCharacter(int id, int duration); + + void delCharacter(int charId, bool startTransaction) const; + void delCharacter(Character *character, bool startTransaction) const; + + void checkBannedAccounts(); + + bool doesUserNameExist(const std::string &name); + bool doesEmailAddressExist(const std::string &email); + bool doesCharacterNameExist(const std::string &name); + + bool updateCharacter(Character *ptr, + bool startTransaction = true); + + void flushSkill(const Character *character, int skill_id); + + void addGuild(Guild *guild); + void removeGuild(Guild *guild); + + void addGuildMember(int guild_id, int memberId); + void removeGuildMember(int guildId, int memberId); + + void setMemberRights(int guildId, int memberId, int rights); + + std::list getGuildList(); + + void flush(Account *); + + std::string getQuestVar(int id, const std::string &); + void setQuestVar(int id, const std::string &, const std::string &); + + std::string getWorldStateVar(const std::string &name, int map_id = -1); + void setWorldStateVar(const std::string &name, + const std::string &value); + void setWorldStateVar(const std::string &name, int mapId, + const std::string &value); + + void setAccountLevel(int id, int level); + void setPlayerLevel(int id, int level); + + void storeLetter(Letter *letter); + Post *getStoredPost(int playerId); + void deletePost(Letter* letter); + + /** + * Returns the version of the local item database. + */ + unsigned int getItemDatabaseVersion() const + { return mItemDbVersion; } + + void setOnlineStatus(int charId, bool online); + + void addTransaction(const Transaction &trans); + + std::vector getTransactions(unsigned int num); + std::vector getTransactions(time_t date); + + private: + // Prevent copying + Storage(const Storage &rhs); + Storage &operator=(const Storage &rhs); + + Account *getAccountBySQL(); + Character *getCharacterBySQL(Account *owner); + + void syncDatabase(); + + dal::DataProvider *mDb; /**< the data provider */ + unsigned int mItemDbVersion; /**< Version of the item database. */ +}; + +extern Storage *storage; + +#endif // STORAGE_H diff --git a/src/account-server/storagesql.hpp b/src/account-server/storagesql.hpp new file mode 100644 index 00000000..ac4c2c6e --- /dev/null +++ b/src/account-server/storagesql.hpp @@ -0,0 +1,148 @@ +/* + * The Mana Server + * Copyright (C) 2008 The Mana World Development Team + * + * This file is part of The Mana Server. + * + * The Mana Server is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * any later version. + * + * The Mana Server is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with The Mana Server. If not, see . + */ + +#ifndef _MANASERV_DALSTORAGE_SQL_H +#define _MANASERV_DALSTORAGE_SQL_H + +#ifdef HAVE_CONFIG_H +#include "config.h" +#endif + +#if !defined (MYSQL_SUPPORT) && !defined (SQLITE_SUPPORT) && \ + !defined (POSTGRESQL_SUPPORT) +#error "(storage.hpp) no database backend defined" +#endif + +// TODO: Fix problem with PostgreSQL null primary key's. + +/** + * MySQL specificities: + * - TINYINT is an integer (1 byte) type defined as an extension to + * the SQL standard. + * - all integer types can have an optional (non-standard) attribute + * UNSIGNED (http://dev.mysql.com/doc/mysql/en/numeric-types.html) + * + * SQLite3 specificities: + * - any column (but only one for each table) with the exact type of + * 'INTEGER PRIMARY KEY' is taken as auto-increment. + * - the supported data types are: NULL, INTEGER, REAL, TEXT and BLOB + * (http://www.sqlite.org/datatype3.html) + * - the size of TEXT cannot be set, it is just ignored by the engine. + * - IMPORTANT: foreign key constraints are not yet supported + * (http://www.sqlite.org/omitted.html). Included in case of future + * support. + * + * Notes: + * - the SQL queries will take advantage of the most appropriate data + * types supported by a particular database engine in order to + * optimize the server database size. + */ + + +/** + * TABLE: mana_accounts. + */ +static const char *ACCOUNTS_TBL_NAME = "mana_accounts"; + +/** + * TABLE: mana_characters. + * - gender is 0 for male, 1 for female. + */ +static const char *CHARACTERS_TBL_NAME = "mana_characters"; + +/** + * TABLE: mana_char_skills. + */ +static const char *CHAR_SKILLS_TBL_NAME = "mana_char_skills"; + +/** + * TABLE: mana_char_status_effects. + */ +static const char *CHAR_STATUS_EFFECTS_TBL_NAME = "mana_char_status_effects"; + +/** + * TABLE: mana_inventories. + */ +static const char *INVENTORIES_TBL_NAME = "mana_inventories"; + +/** + * TABLE: mana_items. + */ +static const char *ITEMS_TBL_NAME = "mana_items"; + +/** + * TABLE: mana_guilds. + * Store player guilds + */ +static const char *GUILDS_TBL_NAME = "mana_guilds"; + +/** + * TABLE: mana_guild_members. + * Store guild members + */ +static const char *GUILD_MEMBERS_TBL_NAME = "mana_guild_members"; + +/** + * TABLE: mana_quests. + */ +static const char *QUESTS_TBL_NAME = "mana_quests"; + +/** + * TABLE: mana_world_states + */ +static const char *WORLD_STATES_TBL_NAME = "mana_world_states"; + +/** + * TABLE: mana_post + * Store letters sent by characters + */ +static const char *POST_TBL_NAME = "mana_post"; + +/** + * TABLE: mana_post_attachments + * Store attachments per letter. + */ +static const char *POST_ATTACHMENTS_TBL_NAME = "mana_post_attachments"; + +/** + * TABLE: mana_auctions + * Store items auctions. + */ +static const char *AUCTION_TBL_NAME = "mana_auctions"; + +/** + * TABLE: mana_auction_bids + * Store bids on auctions. + */ +static const char *AUCTION_BIDS_TBL_NAME = "mana_auction_bids"; + +/** + * TABLE: mana_online_list + * List currently online users. + */ +static const char *ONLINE_USERS_TBL_NAME = "mana_online_list"; + +/** + * TABLE: mana_transactions + * Stores all transactions + */ +static const char *TRANSACTION_TBL_NAME = "mana_transactions"; + +#endif // _MANASERV_DALSTORAGE_SQL_H diff --git a/src/chat-server/chatchannelmanager.cpp b/src/chat-server/chatchannelmanager.cpp index f708c44c..87d68467 100644 --- a/src/chat-server/chatchannelmanager.cpp +++ b/src/chat-server/chatchannelmanager.cpp @@ -23,7 +23,7 @@ #include "chat-server/chatchannelmanager.hpp" #include "protocol.h" -#include "account-server/dalstorage.hpp" +#include "account-server/storage.hpp" #include "chat-server/chatclient.hpp" #include "chat-server/chathandler.hpp" #include "chat-server/guildmanager.hpp" diff --git a/src/chat-server/chathandler.cpp b/src/chat-server/chathandler.cpp index b2183a63..0069eb91 100644 --- a/src/chat-server/chathandler.cpp +++ b/src/chat-server/chathandler.cpp @@ -25,7 +25,7 @@ #include "protocol.h" #include "account-server/character.hpp" -#include "account-server/dalstorage.hpp" +#include "account-server/storage.hpp" #include "chat-server/guildmanager.hpp" #include "chat-server/chatchannelmanager.hpp" #include "chat-server/chatclient.hpp" diff --git a/src/chat-server/guildhandler.cpp b/src/chat-server/guildhandler.cpp index e7ec4c68..3fcc9264 100644 --- a/src/chat-server/guildhandler.cpp +++ b/src/chat-server/guildhandler.cpp @@ -26,7 +26,7 @@ #include "guildmanager.hpp" #include "account-server/character.hpp" -#include "account-server/dalstorage.hpp" +#include "account-server/storage.hpp" #include "net/messagein.hpp" #include "net/messageout.hpp" diff --git a/src/chat-server/guildmanager.cpp b/src/chat-server/guildmanager.cpp index 2d3bc691..431a7ce0 100644 --- a/src/chat-server/guildmanager.cpp +++ b/src/chat-server/guildmanager.cpp @@ -22,7 +22,7 @@ #include "guild.hpp" #include "protocol.h" #include "defines.h" -#include "account-server/dalstorage.hpp" +#include "account-server/storage.hpp" #include "chat-server/chatclient.hpp" #include "chat-server/chathandler.hpp" diff --git a/src/chat-server/partyhandler.cpp b/src/chat-server/partyhandler.cpp index e0f884fb..33dddd20 100644 --- a/src/chat-server/partyhandler.cpp +++ b/src/chat-server/partyhandler.cpp @@ -22,7 +22,7 @@ #include "chatclient.hpp" #include "party.hpp" -#include "account-server/dalstorage.hpp" +#include "account-server/storage.hpp" #include "account-server/serverhandler.hpp" #include "net/messagein.hpp" -- cgit v1.2.3-70-g09d2