From 410f448669a2ed843ff0b412924c04c8fbe87458 Mon Sep 17 00:00:00 2001 From: Andreas Habel Date: Fri, 19 Sep 2008 12:50:53 +0000 Subject: * Doxygen now writes all warnings into a separate file * Added link to wiki documentation as comment into tmwserv.xml * Added table tmw_char_skills to store skills of a character and removed weapon skills from table tmw_characters. --- src/account-server/dalstorage.cpp | 152 ++++++++++++++++++++++++----------- src/account-server/dalstorage.hpp | 11 +++ src/account-server/dalstoragesql.hpp | 5 ++ src/sql/mysql/createTables.sql | 30 ++++--- src/sql/sqlite/createTables.sql | 25 +++--- src/sql/sqlite/tmw.db | Bin 19456 -> 24576 bytes 6 files changed, 151 insertions(+), 72 deletions(-) (limited to 'src') diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp index d8040b8e..d3d10aff 100644 --- a/src/account-server/dalstorage.cpp +++ b/src/account-server/dalstorage.cpp @@ -240,11 +240,6 @@ Character *DALStorage::getCharacterBySQL(std::string const &query, Account *owne character->setAttribute(CHAR_ATTR_BEGIN + i, toUshort(charInfo(0, 13 + i))); } - for (int i = 0; i < CHAR_SKILL_WEAPON_NB; ++i) - { - int exp = toUint(charInfo(0, 13 + CHAR_ATTR_NB + i)); - character->setExperience(i, exp); - } int mapId = toUint(charInfo(0, 12)); if (mapId > 0) @@ -275,6 +270,23 @@ Character *DALStorage::getCharacterBySQL(std::string const &query, Account *owne 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(); + + dal::RecordSet const &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 + } + } } catch (dal::DbSqlQueryExecFailure const &e) { @@ -327,6 +339,7 @@ Character *DALStorage::getCharacterBySQL(std::string const &query, Account *owne catch (dal::DbSqlQueryExecFailure const &e) { LOG_ERROR("(DALStorage::getCharacter #2) SQL query failure: " << e.what()); + return NULL; } return character; @@ -456,6 +469,7 @@ bool DALStorage::doesCharacterNameExist(const std::string& name) bool DALStorage::updateCharacter(Character *character) { // Update the database Character data (see CharacterData for details) + mDb->beginTransaction(); try { std::ostringstream sqlUpdateCharacterInfo; @@ -482,19 +496,7 @@ bool DALStorage::updateCharacter(Character *character) << "int = '" #endif << character->getAttribute(CHAR_ATTR_INTELLIGENCE) << "', " - << "will = '" << character->getAttribute(CHAR_ATTR_WILLPOWER) << "', " - << "unarmed_exp = '"<< character->getExperience(CHAR_SKILL_WEAPON_NONE - CHAR_SKILL_BEGIN) << "', " - << "knife_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_KNIFE - CHAR_SKILL_BEGIN) << "', " - << "sword_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_SWORD - CHAR_SKILL_BEGIN) << "', " - << "polearm_exp = '"<< character->getExperience(CHAR_SKILL_WEAPON_POLEARM - CHAR_SKILL_BEGIN) << "', " - << "staff_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_STAFF - CHAR_SKILL_BEGIN) << "', " - << "whip_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_WHIP - CHAR_SKILL_BEGIN) << "', " - << "bow_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_BOW - CHAR_SKILL_BEGIN) << "', " - << "shoot_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_SHOOTING - CHAR_SKILL_BEGIN) << "', " - << "mace_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_MACE - CHAR_SKILL_BEGIN) << "', " - << "axe_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_AXE - CHAR_SKILL_BEGIN) << "', " - << "thrown_exp = '" << character->getExperience(CHAR_SKILL_WEAPON_THROWN - CHAR_SKILL_BEGIN) << "' " - + << "will = '" << character->getAttribute(CHAR_ATTR_WILLPOWER) << "' " << "where id = '" << character->getDatabaseID() << "';"; mDb->execSql(sqlUpdateCharacterInfo.str()); @@ -502,10 +504,30 @@ bool DALStorage::updateCharacter(Character *character) catch (const dal::DbSqlQueryExecFailure& e) { // TODO: throw an exception. + mDb->rollbackTransaction(); LOG_ERROR("(DALStorage::updateCharacter #1) SQL query failure: " << e.what()); return false; } + /** + * Character's skills + */ + try + { + for (unsigned int skill_id = 0; skill_id < CHAR_SKILL_NB; skill_id++) + { + flushSkill(character, skill_id); + } + } + catch (const dal::DbSqlQueryExecFailure& e) + { + // TODO: throw an exception. + mDb->rollbackTransaction(); + LOG_ERROR("(DALStorage::updateCharacter #2) SQL query failure: " << e.what()); + return false; + } + + /** * Character's inventory */ @@ -522,7 +544,8 @@ bool DALStorage::updateCharacter(Character *character) catch (const dal::DbSqlQueryExecFailure& e) { // TODO: throw an exception. - LOG_ERROR("(DALStorage::updateCharacter #2) SQL query failure: " << e.what()); + mDb->rollbackTransaction(); + LOG_ERROR("(DALStorage::updateCharacter #3) SQL query failure: " << e.what()); return false; } @@ -567,13 +590,66 @@ bool DALStorage::updateCharacter(Character *character) catch (const dal::DbSqlQueryExecFailure& e) { // TODO: throw an exception. - LOG_ERROR("(DALStorage::updateCharacter #3) SQL query failure: " << e.what()); + mDb->rollbackTransaction(); + LOG_ERROR("(DALStorage::updateCharacter #4) SQL query failure: " << e.what()); return false; } + mDb->commitTransaction(); return true; } +/** + * Save changes of a skill to the database permanently. +*/ +void DALStorage::flushSkill(const Character* const character, + const int skill_id ) +{ + try + { + const unsigned int exp = character->getExperience(skill_id); + + // if experience has decreased to 0 we don't store is anymore, + // its the default + if (exp == 0) + { + std::ostringstream sql; + sql << "DELETE FROM " << CHAR_SKILLS_TBL_NAME << " " + << "WHERE char_id = '" << character->getDatabaseID() << "' " + << "AND skill_id = '" << skill_id << "'"; + mDb->execSql(sql.str()); + return; + } + + // try to update the skill + std::ostringstream sql; + sql << "UPDATE " << CHAR_SKILLS_TBL_NAME << " " + << "SET skill_exp = '" << exp << "' " + << "WHERE char_id = '" << character->getDatabaseID() << "' " + << "AND skill_id = '" << skill_id << "'"; + mDb->execSql(sql.str()); + + // check if the update has modified a row + if (mDb->getModifiedRows() > 0) + { + return; + } + + sql.clear(); + sql << "INSERT INTO " << CHAR_SKILLS_TBL_NAME << " " + << "(char_id, skill_id, skill_exp) VALUES " + << "'" << character->getDatabaseID() << "', " + << "'" << skill_id << "', " + << "'" << exp << "'"; + mDb->execSql(sql.str()); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("DALStorage::flushSkill: " << e.what()); + throw; + } +} + /** * Add an account to the database. @@ -599,20 +675,6 @@ void DALStorage::addAccount(Account *account) << account->getRegistrationDate() << ", " << account->getLastLogin() << ");"; mDb->execSql(sql1.str()); - - // get the account id. - /* - Exceptionfault: no longer needed as our databases provides a function to - get the last inserted auto-increment value - - std::ostringstream sql2; - sql2 << "select id from " << ACCOUNTS_TBL_NAME - << " where username = \"" << account->getName() << "\";"; - const RecordSet& accountInfo = mDb->execSql(sql2.str()); - string_to toUint; - unsigned id = toUint(accountInfo(0, 0)); - account->setID(id); - */ account->setID(mDb->getLastId()); mDb->commitTransaction(); @@ -675,8 +737,7 @@ void DALStorage::flush(Account *account) #else << "int, " #endif - << "will, unarmed_exp, knife_exp, sword_exp, polearm_exp," - << " staff_exp, whip_exp, bow_exp, shoot_exp, mace_exp, axe_exp, thrown_exp) values (" + << "will ) values (" << account->getID() << ", \"" << (*it)->getName() << "\", " << (*it)->getGender() << ", " @@ -694,24 +755,19 @@ void DALStorage::flush(Account *account) << (*it)->getAttribute(CHAR_ATTR_DEXTERITY) << ", " << (*it)->getAttribute(CHAR_ATTR_VITALITY) << ", " << (*it)->getAttribute(CHAR_ATTR_INTELLIGENCE) << ", " - << (*it)->getAttribute(CHAR_ATTR_WILLPOWER) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_NONE - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_KNIFE - CHAR_SKILL_BEGIN) << "," - << (*it)->getExperience(CHAR_SKILL_WEAPON_SWORD - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_POLEARM - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_STAFF - CHAR_SKILL_BEGIN) << "," - << (*it)->getExperience(CHAR_SKILL_WEAPON_WHIP - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_BOW - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_SHOOTING - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_MACE - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_AXE - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_THROWN - CHAR_SKILL_BEGIN) + << (*it)->getAttribute(CHAR_ATTR_WILLPOWER) << " " << ");"; mDb->execSql(sqlInsertCharactersTable.str()); // Update the character ID. (*it)->setDatabaseID(mDb->getLastId()); + + // update the characters skills + for (unsigned int skill_id = 0; skill_id < CHAR_SKILL_NB; skill_id++) + { + flushSkill((*it), skill_id); + } } } // diff --git a/src/account-server/dalstorage.hpp b/src/account-server/dalstorage.hpp index 1085a3ae..970f550b 100644 --- a/src/account-server/dalstorage.hpp +++ b/src/account-server/dalstorage.hpp @@ -183,6 +183,17 @@ class DALStorage bool updateCharacter(Character *ptr); + /** + * 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. + */ + void + flushSkill(const Character* const character, const int skill_id ); + /** * Add a new guild * diff --git a/src/account-server/dalstoragesql.hpp b/src/account-server/dalstoragesql.hpp index 0165d1bf..95c89c03 100644 --- a/src/account-server/dalstoragesql.hpp +++ b/src/account-server/dalstoragesql.hpp @@ -73,6 +73,11 @@ static char const *ACCOUNTS_TBL_NAME = "tmw_accounts"; */ static char const *CHARACTERS_TBL_NAME = "tmw_characters"; +/** + * TABLE: tmw_char_skills. + */ +static char const *CHAR_SKILLS_TBL_NAME = "tmw_char_skills"; + /** * TABLE: tmw_inventories. */ diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql index 2f164cca..4db80e82 100644 --- a/src/sql/mysql/createTables.sql +++ b/src/sql/mysql/createTables.sql @@ -46,18 +46,6 @@ CREATE TABLE IF NOT EXISTS `tmw_characters` ( `vit` smallint(5) unsigned NOT NULL, `int` smallint(5) unsigned NOT NULL, `will` smallint(5) unsigned NOT NULL, - -- skill experience - `unarmed_exp` smallint(5) unsigned NOT NULL, - `knife_exp` smallint(5) unsigned NOT NULL, - `sword_exp` smallint(5) unsigned NOT NULL, - `polearm_exp` smallint(5) unsigned NOT NULL, - `staff_exp` smallint(5) unsigned NOT NULL, - `whip_exp` smallint(5) unsigned NOT NULL, - `bow_exp` smallint(5) unsigned NOT NULL, - `shoot_exp` smallint(5) unsigned NOT NULL, - `mace_exp` smallint(5) unsigned NOT NULL, - `axe_exp` smallint(5) unsigned NOT NULL, - `thrown_exp` smallint(5) unsigned NOT NULL, -- PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), @@ -68,7 +56,23 @@ CREATE TABLE IF NOT EXISTS `tmw_characters` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; - + +-- +-- table: `tmw_inventories` +-- + +CREATE TABLE IF NOT EXISTS `tmw_char_skills` ( + `char_id` int(10) unsigned NOT NULL, + `skill_id` smallint(5) unsigned NOT NULL, + `skill_exp` smallint(5) unsigned NOT NULL, + -- + PRIMARY KEY (`char_id`, `skill_id`), + FOREIGN KEY (`char_id`) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8; + -- -- table: `tmw_inventories` -- diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql index 4ff336bb..ba7fa5e2 100644 --- a/src/sql/sqlite/createTables.sql +++ b/src/sql/sqlite/createTables.sql @@ -34,21 +34,24 @@ CREATE TABLE tmw_characters vit INTEGER NOT NULL, int INTEGER NOT NULL, will INTEGER NOT NULL, - unarmed_exp INTEGER NOT NULL, - knife_exp INTEGER NOT NULL, - sword_exp INTEGER NOT NULL, - polearm_exp INTEGER NOT NULL, - staff_exp INTEGER NOT NULL, - whip_exp INTEGER NOT NULL, - bow_exp INTEGER NOT NULL, - shoot_exp INTEGER NOT NULL, - mace_exp INTEGER NOT NULL, - axe_exp INTEGER NOT NULL, - thrown_exp INTEGER NOT NULL, -- FOREIGN KEY (user_id) REFERENCES tmw_accounts(id) ); +CREATE INDEX tmw_characters_user ON tmw_characters ( user_id ); +CREATE UNIQUE INDEX tmw_characters_name ON tmw_characters ( name ); + +CREATE TABLE tmw_char_skills +( + char_id INTEGER NOT NULL, + skill_id INTEGER NOT NULL, + skill_exp INTEGER NOT NULL, + -- + FOREIGN KEY (char_id) REFERENCES tmw_characters(id) +); + +CREATE INDEX tmw_char_skills_char ON tmw_char_skills ( char_id ); + CREATE TABLE tmw_inventories ( id INTEGER PRIMARY KEY, diff --git a/src/sql/sqlite/tmw.db b/src/sql/sqlite/tmw.db index d212f5a5..a9f72284 100644 Binary files a/src/sql/sqlite/tmw.db and b/src/sql/sqlite/tmw.db differ -- cgit v1.2.3-60-g2f50