diff options
| -rw-r--r-- | ChangeLog | 30 | ||||
| -rw-r--r-- | Doxyfile | 10 | ||||
| -rw-r--r-- | docs/tmwserv.xml | 51 | ||||
| -rw-r--r-- | src/account-server/dalstorage.cpp | 582 | ||||
| -rw-r--r-- | src/account-server/dalstorage.hpp | 47 | ||||
| -rw-r--r-- | src/account-server/dalstoragesql.hpp | 253 | ||||
| -rw-r--r-- | src/dal/dataprovider.h | 54 | ||||
| -rw-r--r-- | src/dal/mysqldataprovider.cpp | 154 | ||||
| -rw-r--r-- | src/dal/mysqldataprovider.h | 94 | ||||
| -rw-r--r-- | src/dal/sqlitedataprovider.cpp | 179 | ||||
| -rw-r--r-- | src/dal/sqlitedataprovider.h | 78 | ||||
| -rw-r--r-- | src/sql/mysql/createDatabase.sql | 34 | ||||
| -rw-r--r-- | src/sql/mysql/createTables.sql | 153 | ||||
| -rw-r--r-- | src/sql/postgresql/createTables.sql | 102 | ||||
| -rw-r--r-- | src/sql/sqlite/createTables.sql | 102 | ||||
| -rw-r--r-- | src/sql/sqlite/tmw.db | bin | 0 -> 19456 bytes |
16 files changed, 1262 insertions, 661 deletions
@@ -1,3 +1,24 @@ +2008-09-17 Andreas Habel <mail@exceptionfault.de> + + * src/sql/*, src/account-server/dalstoragesql.hpp: added installation + scripts to set up database schemas for mysql, sqlite and postgresql. + The create table statements have been completely removed out from the c++ + source into separate, provider specific sql files. + * docs/tmwserv.xml: added database specific parameters to configure + each provider, that is used as database backend independent + * Doxyfile: doxygen now also includes non-documented functions and provides + a dictionary for all classes + * dal/dataprovider.h, dal/sqlitedataprovider.h, dal/mysqldataprovider.h, + dal/mysqldataprovider.cpp, dal/sqlitedataprovider.cpp: + Extended abstract dataprovider to support transactions, implemented for + SQLite and mySQL. Added methods to retrieve last inserted + auto-increment value and the number of modified rows in the last statement. + * account-server/dalstorage.cpp: Tiny rewrite to be a little more + transactional and use advanced database functionality. Fixed a bug when + deleting a character that left data in quests table and guilds table. + Simplified the connect routine of DALStorage class since every dataprovider + is now responsible to retrieve its parameters for itself. + 2008-09-15 Andreas Habel <mail@exceptionfault.de> * Doxyfile: added doxygen file, docu will be generated under docs/api/html @@ -11,11 +32,10 @@ * src/account-server/dalstorage.cpp, src/account-server/account.cpp, src/account-server/account.hpp, src/account-server/accounthandler.cpp, - src/account-server/dalstorage.hpp, - src/account-server/dalstoragesql.hpp: Extended tmw_accounts table with - columns for lastlogin and registration date. Modified account-server - to fill the new columns on registration and login. Recreation of - database needed! + src/account-server/dalstorage.hpp, src/account-server/dalstoragesql.hpp: + Extended tmw_accounts table with columns for lastlogin and registration + date. Modified account-server to fill the new columns on registration and + login. Recreation of database needed! * src/account-server/dalstorage.cpp, src/account-server/dalstoragesql.hpp: Added createIndex function to create indexes on tables. @@ -24,7 +24,7 @@ ABBREVIATE_BRIEF = "The $name class" \ the ALWAYS_DETAILED_SEC = NO INLINE_INHERITED_MEMB = NO -FULL_PATH_NAMES = YES +FULL_PATH_NAMES = NO STRIP_FROM_PATH = C:/Programme/doxygen/bin/ STRIP_FROM_INC_PATH = SHORT_NAMES = NO @@ -50,14 +50,14 @@ TYPEDEF_HIDES_STRUCT = NO #--------------------------------------------------------------------------- # Build related configuration options #--------------------------------------------------------------------------- -EXTRACT_ALL = NO +EXTRACT_ALL = YES EXTRACT_PRIVATE = NO EXTRACT_STATIC = NO EXTRACT_LOCAL_CLASSES = YES EXTRACT_LOCAL_METHODS = NO EXTRACT_ANON_NSPACES = NO -HIDE_UNDOC_MEMBERS = YES -HIDE_UNDOC_CLASSES = YES +HIDE_UNDOC_MEMBERS = NO +HIDE_UNDOC_CLASSES = NO HIDE_FRIEND_COMPOUNDS = NO HIDE_IN_BODY_DOCS = NO INTERNAL_DOCS = NO @@ -152,7 +152,7 @@ VERBATIM_HEADERS = NO #--------------------------------------------------------------------------- # configuration options related to the alphabetical class index #--------------------------------------------------------------------------- -ALPHABETICAL_INDEX = NO +ALPHABETICAL_INDEX = YES COLS_IN_ALPHA_INDEX = 5 IGNORE_PREFIX = #--------------------------------------------------------------------------- diff --git a/docs/tmwserv.xml b/docs/tmwserv.xml index a16b063..1f20d32 100644 --- a/docs/tmwserv.xml +++ b/docs/tmwserv.xml @@ -1,12 +1,51 @@ <?xml version="1.0"?> <!-- An example configuration file for ~/.tmwserv.xml --> <configuration> - <!-- - Database configuration (unused by sqlite3). - --> - <option name="dbhost" value=""/> - <option name="dbpass" value=""/> - <option name="dbuser" value=""/> + +<!-- Database configuration *************************************************** + Uncomment one of the following parts according to the database backend you + would like to use. +--> + +<!-- + SQLite specific configuration. + + sqlite_database: name and path to the sqlite database file + optional, default="tmw.db" +--> +<!-- <option name="sqlite_database" value="tmw.db"/> --> + + +<!-- + mySQL specific configuration. + + mysql_hostname: ip or hostname of the database server + optional, default="localhost" + mysql_port: the port where the mysql server listens to + optional, default=3306 + mysql_database: name of the installed database + optional, default="tmw" + mysql_username: name of the user to connect to the database server + optional, default="tmw" + mysql_password: password to use whith the mysql_username + optional, default="tmw" +--> +<!-- +<option name="mysql_hostname" value="localhost"/> +<option name="mysql_port" value="3306"/> +<option name="mysql_database" value="tmw"/> +<option name="mysql_username" value="tmw"/> +<option name="mysql_password" value="tmw"/> +--> + + +<!-- + PostgreSQL specific configuration. + + TODO! +--> + +<!-- end of database configuration *************************************** --> <!-- New player starting location. The map should be defined in data/maps.xml. diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp index 601c0bb..d8040b8 100644 --- a/src/account-server/dalstorage.cpp +++ b/src/account-server/dalstorage.cpp @@ -59,94 +59,34 @@ DALStorage::~DALStorage() /** * Connect to the database and initialize it if necessary. + * + * TODO: <b>Exceptionfault:</b> after connecting to the database, we have to + * verify if the version matches a supported version. Maybe implement a + * "version table" to check after connect. Raise an error with verbose + * informations about the discrepancy between the versions. + * */ void DALStorage::open() { // Do nothing if already connected. - if (mDb->isConnected()) { + if (mDb->isConnected()) + { return; } using namespace dal; - static bool dbFileShown = false; - std::string dbFile = "tmw"; try { // open a connection to the database. -#if defined (MYSQL_SUPPORT) || defined (POSTGRESQL_SUPPORT) - mDb->connect(getName(), getUser(), getPassword()); - if (!dbFileShown) - { - LOG_INFO("Using " << dbFile << " as Database Name."); - dbFileShown = true; - } -#elif defined (SQLITE_SUPPORT) - // create the database file name. - dbFile += ".db"; - mDb->connect(dbFile, "", ""); - if (!dbFileShown) - { - LOG_INFO("SQLite uses ./" << dbFile << " as DB."); - dbFileShown = true; - } -#endif + mDb->connect(); + + //TODO: check database version here - // ensure that the required tables are created. - // - // strategy1: find a way to obtain the list of tables from the - // underlying database and create the tables that are - // missing. - // - // strategy2: try to create the tables and check the exceptions - // thrown. - // - // comments: - // - strategy1 is easy to achieve if we are using MysQL as - // executing the request "show tables;" returns the list of - // tables. However, there is not such a query for SQLite3. - // When using SQLite3 from the interactive shell or the - // command line, the command ".tables" returns the list of - // tables but sqlite3_exec() does not validate this statement - // and fails. - // The cost of this strategy is: - // (num. tables to create + 1) queries at most and - // 1 at minimum. - // - // - strategy2 will work with probably most databases. - // The cost of this strategy is: - // (num. tables to create) queries. - - // we will stick with strategy2 for the moment as we are focusing - // on SQLite. - - // FIXME: The tables should be checked/created at startup in order to - // avoid a DbSqlQueryExecFailure assert on sqlite while registering. - // Also, this would initialize connection to the database earlier in - // memory. - - createTable(ACCOUNTS_TBL_NAME, SQL_ACCOUNTS_TABLE); - createTable(CHARACTERS_TBL_NAME, SQL_CHARACTERS_TABLE); - createTable(INVENTORIES_TBL_NAME, SQL_INVENTORIES_TABLE); - createTable(GUILDS_TBL_NAME, SQL_GUILDS_TABLE); - createTable(GUILD_MEMBERS_TBL_NAME, SQL_GUILD_MEMBERS_TABLE); - createTable(QUESTS_TBL_NAME, SQL_QUESTS_TABLE); - createTable(WORLD_STATES_TBL_NAME, SQL_WORLD_STATES_TABLE); - - - // TODO: this is not the prefered way, but currently the complete - // generation and maintenance of the database is a little dirty so - // keep this as is until there is a complete cleaner solution - const std::string idxName("tmw_accounts_username"); - const std::string colName("username"); - createIndex(idxName, ACCOUNTS_TBL_NAME, colName); } catch (const DbConnectionFailure& e) { LOG_ERROR("(DALStorage::open #1) Unable to connect to the database: " << e.what()); } - catch (const DbSqlQueryExecFailure& e) { - LOG_ERROR("(DALStorage::open #2) SQL query failure: " << e.what()); - } } @@ -636,84 +576,6 @@ bool DALStorage::updateCharacter(Character *character) /** - * Create the specified table. - */ -void -DALStorage::createTable(const std::string& tblName, - const std::string& sql) -{ - try { - mDb->execSql(sql); - } - catch (const dal::DbSqlQueryExecFailure& e) { - // error message to check against. -#if defined (MYSQL_SUPPORT) - std::string alreadyExists("Table '"); - alreadyExists += tblName; - alreadyExists += "' already exists"; -#elif defined (POSTGRESQL_SUPPORT) - std::string alreadyExists("table "); - alreadyExists += tblName; - alreadyExists += " already exists"; -#else // SQLITE_SUPPORT - std::string alreadyExists("table "); - alreadyExists += tblName; - alreadyExists += " already exists"; -#endif - - const std::string msg(e.what()); - - // oops, another problem occurred. - if (msg != alreadyExists) { - // rethrow to let other error handlers manage the problem. - throw; - } - } -} - -/** - * Create a index on the table - */ -void -DALStorage::createIndex(const std::string& indxName, - const std::string& tblName, - const std::string& columnName ) -{ -#if defined (MYSQL_SUPPORT) -#error MYSQL SUPPORT not complete implemented yet! - -#elif defined (SQLITE_SUPPORT) - std::ostringstream mSQL; - mSQL << "CREATE INDEX " << indxName << " ON " << tblName; - mSQL << " ( " << columnName << " );"; - - std::ostringstream mExists; - mExists << "index " << indxName << " already exists"; -#elif defined (POSTGRESQL_SUPPORT) - -#error POSTGRESQL SUPPORT not complete implemented yet! -#endif - - try { - mDb->execSql(mSQL.str()); - } - catch (const dal::DbSqlQueryExecFailure& e) - { - const std::string msg(e.what()); - if(msg == mExists.str()) - { - LOG_DEBUG(mExists.str()); - } - else - { - throw; - } - } -} // end of createIndex - - - -/** * Add an account to the database. */ void DALStorage::addAccount(Account *account) @@ -722,31 +584,44 @@ void DALStorage::addAccount(Account *account) using namespace dal; - // TODO: we should start a transaction here so that in case of problem - // the lost of data would be minimized. - - - // insert the account. - std::ostringstream sql1; - sql1 << "insert into " << ACCOUNTS_TBL_NAME - << " (username, password, email, level, banned, registration, lastlogin)" - << " values (\"" - << account->getName() << "\", \"" - << account->getPassword() << "\", \"" - << account->getEmail() << "\", " - << account->getLevel() << ", 0, " - << account->getRegistrationDate() << ", " - << account->getLastLogin() << ");"; - mDb->execSql(sql1.str()); - - // get the account id. - std::ostringstream sql2; - sql2 << "select id from " << ACCOUNTS_TBL_NAME - << " where username = \"" << account->getName() << "\";"; - const RecordSet& accountInfo = mDb->execSql(sql2.str()); - string_to<unsigned int> toUint; - unsigned id = toUint(accountInfo(0, 0)); - account->setID(id); + mDb->beginTransaction(); + try + { + // insert the account. + std::ostringstream sql1; + sql1 << "insert into " << ACCOUNTS_TBL_NAME + << " (username, password, email, level, banned, registration, lastlogin)" + << " values (\"" + << account->getName() << "\", \"" + << account->getPassword() << "\", \"" + << account->getEmail() << "\", " + << account->getLevel() << ", 0, " + << account->getRegistrationDate() << ", " + << account->getLastLogin() << ");"; + mDb->execSql(sql1.str()); + + // get the account id. + /* + Exceptionfault: no longer needed as our databases provides a function to + get the last inserted auto-increment value + + std::ostringstream sql2; + sql2 << "select id from " << ACCOUNTS_TBL_NAME + << " where username = \"" << account->getName() << "\";"; + const RecordSet& accountInfo = mDb->execSql(sql2.str()); + string_to<unsigned int> toUint; + unsigned id = toUint(accountInfo(0, 0)); + account->setID(id); + */ + account->setID(mDb->getLastId()); + + mDb->commitTransaction(); + } + catch (const dal::DbSqlQueryExecFailure &e) + { + LOG_ERROR("Error in DALStorage::addAccount: " << e.what()); + mDb->rollbackTransaction(); + } } /** @@ -758,153 +633,135 @@ void DALStorage::flush(Account *account) using namespace dal; - // TODO: we should start a transaction here so that in case of problem - // the loss of data would be minimized. - - // update the account. - std::ostringstream sqlUpdateAccountTable; - sqlUpdateAccountTable << "update " << ACCOUNTS_TBL_NAME - << " set username = '" << account->getName() << "', " - << "password = '" << account->getPassword() << "', " - << "email = '" << account->getEmail() << "', " - << "level = '" << account->getLevel() << "', " - << "lastlogin = '" << account->getLastLogin() << "' " - << "where id = '" << account->getID() << "';"; - mDb->execSql(sqlUpdateAccountTable.str()); - - // get the list of characters that belong to this account. - Characters &characters = account->getCharacters(); - - // insert or update the characters. - for (Characters::const_iterator it = characters.begin(), - it_end = characters.end(); it != it_end; ++it) + mDb->beginTransaction(); + try { - if ((*it)->getDatabaseID() >= 0) - { - updateCharacter(*it); - } - else + + // update the account. + std::ostringstream sqlUpdateAccountTable; + sqlUpdateAccountTable + << "update " << ACCOUNTS_TBL_NAME + << " set username = '" << account->getName() << "', " + << "password = '" << account->getPassword() << "', " + << "email = '" << account->getEmail() << "', " + << "level = '" << account->getLevel() << "', " + << "lastlogin = '" << account->getLastLogin() << "' " + << "where id = '" << account->getID() << "';"; + mDb->execSql(sqlUpdateAccountTable.str()); + + // get the list of characters that belong to this account. + Characters &characters = account->getCharacters(); + + // insert or update the characters. + for (Characters::const_iterator it = characters.begin(), + it_end = characters.end(); it != it_end; ++it) { - std::ostringstream sqlInsertCharactersTable; - // insert the character - // This assumes that the characters name has been checked for - // uniqueness - sqlInsertCharactersTable - << "insert into " << CHARACTERS_TBL_NAME - << " (user_id, name, gender, hair_style, hair_color, level, char_pts, correct_pts, money," - << " x, y, map_id, str, agi, dex, vit, int, will, unarmed_exp, knife_exp, sword_exp, polearm_exp," - << " staff_exp, whip_exp, bow_exp, shoot_exp, mace_exp, axe_exp, thrown_exp) values (" - << account->getID() << ", \"" - << (*it)->getName() << "\", " - << (*it)->getGender() << ", " - << (int)(*it)->getHairStyle() << ", " - << (int)(*it)->getHairColor() << ", " - << (int)(*it)->getLevel() << ", " - << (int)(*it)->getCharacterPoints() << ", " - << (int)(*it)->getCorrectionPoints() << ", " - << (*it)->getPossessions().money << ", " - << (*it)->getPosition().x << ", " - << (*it)->getPosition().y << ", " - << (*it)->getMapId() << ", " - << (*it)->getAttribute(CHAR_ATTR_STRENGTH) << ", " - << (*it)->getAttribute(CHAR_ATTR_AGILITY) << ", " - << (*it)->getAttribute(CHAR_ATTR_DEXTERITY) << ", " - << (*it)->getAttribute(CHAR_ATTR_VITALITY) << ", " - << (*it)->getAttribute(CHAR_ATTR_INTELLIGENCE) << ", " - << (*it)->getAttribute(CHAR_ATTR_WILLPOWER) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_NONE - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_KNIFE - CHAR_SKILL_BEGIN) << "," - << (*it)->getExperience(CHAR_SKILL_WEAPON_SWORD - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_POLEARM - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_STAFF - CHAR_SKILL_BEGIN) << "," - << (*it)->getExperience(CHAR_SKILL_WEAPON_WHIP - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_BOW - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_SHOOTING - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_MACE - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_AXE - CHAR_SKILL_BEGIN) << ", " - << (*it)->getExperience(CHAR_SKILL_WEAPON_THROWN - CHAR_SKILL_BEGIN) - << ");"; - - mDb->execSql(sqlInsertCharactersTable.str()); - - // Update the character ID. - std::ostringstream sqlSelectIdCharactersTable; - sqlSelectIdCharactersTable - << "select id from " << CHARACTERS_TBL_NAME - << " where name = \"" << (*it)->getName() << "\";"; - RecordSet const &charInfo = - mDb->execSql(sqlSelectIdCharactersTable.str()); - - if (!charInfo.isEmpty()) { - string_to<unsigned int> toUint; - (*it)->setDatabaseID(toUint(charInfo(0, 0))); + if ((*it)->getDatabaseID() >= 0) + { + updateCharacter(*it); } else { - // TODO: The character's name is not unique, or some other - // error has occured + std::ostringstream sqlInsertCharactersTable; + // insert the character + // This assumes that the characters name has been checked for + // uniqueness + sqlInsertCharactersTable + << "insert into " << CHARACTERS_TBL_NAME + << " (user_id, name, gender, hair_style, hair_color, level, char_pts, correct_pts, money," + << " x, y, map_id, str, agi, dex, vit, " +#if defined(MYSQL_SUPPORT) || defined(POSTGRESQL_SUPPORT) + << "`int`, " +#else + << "int, " +#endif + << "will, unarmed_exp, knife_exp, sword_exp, polearm_exp," + << " staff_exp, whip_exp, bow_exp, shoot_exp, mace_exp, axe_exp, thrown_exp) values (" + << account->getID() << ", \"" + << (*it)->getName() << "\", " + << (*it)->getGender() << ", " + << (int)(*it)->getHairStyle() << ", " + << (int)(*it)->getHairColor() << ", " + << (int)(*it)->getLevel() << ", " + << (int)(*it)->getCharacterPoints() << ", " + << (int)(*it)->getCorrectionPoints() << ", " + << (*it)->getPossessions().money << ", " + << (*it)->getPosition().x << ", " + << (*it)->getPosition().y << ", " + << (*it)->getMapId() << ", " + << (*it)->getAttribute(CHAR_ATTR_STRENGTH) << ", " + << (*it)->getAttribute(CHAR_ATTR_AGILITY) << ", " + << (*it)->getAttribute(CHAR_ATTR_DEXTERITY) << ", " + << (*it)->getAttribute(CHAR_ATTR_VITALITY) << ", " + << (*it)->getAttribute(CHAR_ATTR_INTELLIGENCE) << ", " + << (*it)->getAttribute(CHAR_ATTR_WILLPOWER) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_NONE - CHAR_SKILL_BEGIN) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_KNIFE - CHAR_SKILL_BEGIN) << "," + << (*it)->getExperience(CHAR_SKILL_WEAPON_SWORD - CHAR_SKILL_BEGIN) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_POLEARM - CHAR_SKILL_BEGIN) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_STAFF - CHAR_SKILL_BEGIN) << "," + << (*it)->getExperience(CHAR_SKILL_WEAPON_WHIP - CHAR_SKILL_BEGIN) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_BOW - CHAR_SKILL_BEGIN) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_SHOOTING - CHAR_SKILL_BEGIN) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_MACE - CHAR_SKILL_BEGIN) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_AXE - CHAR_SKILL_BEGIN) << ", " + << (*it)->getExperience(CHAR_SKILL_WEAPON_THROWN - CHAR_SKILL_BEGIN) + << ");"; + + mDb->execSql(sqlInsertCharactersTable.str()); + + // Update the character ID. + (*it)->setDatabaseID(mDb->getLastId()); } - } - } + } // - // Existing characters in memory have been inserted or updated in database. - // Now, let's remove those who are no more in memory from database. + // Existing characters in memory have been inserted or updated in database. + // Now, let's remove those who are no more in memory from database. - // specialize the string_to functor to convert - // a string to an unsigned int. - string_to<unsigned short> toUint; - - std::ostringstream sqlSelectNameIdCharactersTable; - sqlSelectNameIdCharactersTable - << "select name, id from " << CHARACTERS_TBL_NAME - << " where user_id = '" << account->getID() << "';"; - const RecordSet& charInMemInfo = - mDb->execSql(sqlSelectNameIdCharactersTable.str()); - - // We compare chars from memory and those existing in db, - // And delete those not in mem but existing in db. - bool charFound; - for (unsigned int i = 0; i < charInMemInfo.rows(); ++i) // in database - { - charFound = false; - for (Characters::const_iterator it = characters.begin(), - it_end = characters.end(); it != it_end; ++it) // In memory + // specialize the string_to functor to convert + // a string to an unsigned int. + string_to<unsigned short> toUint; + + std::ostringstream sqlSelectNameIdCharactersTable; + sqlSelectNameIdCharactersTable + << "select name, id from " << CHARACTERS_TBL_NAME + << " where user_id = '" << account->getID() << "';"; + const RecordSet& charInMemInfo = + mDb->execSql(sqlSelectNameIdCharactersTable.str()); + + // We compare chars from memory and those existing in db, + // And delete those not in mem but existing in db. + bool charFound; + for (unsigned int i = 0; i < charInMemInfo.rows(); ++i) // in database { - if (charInMemInfo(i, 0) == (*it)->getName()) + charFound = false; + for (Characters::const_iterator it = characters.begin(), + it_end = characters.end(); it != it_end; ++it) // In memory { - charFound = true; - break; + if (charInMemInfo(i, 0) == (*it)->getName()) + { + charFound = true; + break; + } + } + if (!charFound) + { + // The char is db but not in memory, + // It will be removed from database. + // We store the id of the char to delete + // Because as deleted, the RecordSet is also emptied + // That creates an error. + unsigned int charId = toUint(charInMemInfo(i, 1)); + delCharacter(charId, false); } } - if (!charFound) - { - // The char is db but not in memory, - // It will be removed from database. - // We store the id of the char to delete - // Because as deleted, the RecordSet is also emptied - // That creates an error. - unsigned int charId = toUint(charInMemInfo(i, 1)); - - // delete the inventory. - std::ostringstream sqlDeleteInventoryTable; - sqlDeleteInventoryTable - << "delete from " - << INVENTORIES_TBL_NAME - << " where owner_id = '" - << charId - << "';"; - mDb->execSql(sqlDeleteInventoryTable.str()); - - // now delete the character. - std::ostringstream sqlDeleteCharactersTable; - sqlDeleteCharactersTable - << "delete from " - << CHARACTERS_TBL_NAME - << " where id = '" - << charId - << "';"; - mDb->execSql(sqlDeleteCharactersTable.str()); - } + + mDb->commitTransaction(); + } + catch (const std::exception &e) + { + LOG_ERROR("ERROR in DALStorage::flush: " << e.what()); + mDb->rollbackTransaction(); } } @@ -1168,33 +1025,57 @@ void DALStorage::setWorldStateVar(std::string const &name, { try { - std::ostringstream query1; - query1 << "DELETE FROM " << WORLD_STATES_TBL_NAME - << " WHERE state_name = '" << name << "'"; + // set the value to empty means: delete the variable + if (value.empty()) + { + std::ostringstream deleteStateVar; + deleteStateVar << "DELETE FROM " << WORLD_STATES_TBL_NAME + << " WHERE state_name = '" << name << "'"; + if (map_id >= 0) + { + deleteStateVar << " AND map_id = '" << map_id << "'"; + } + deleteStateVar << ";"; + mDb->execSql(deleteStateVar.str()); + return; + } + + // try to update the variable in the database + std::ostringstream updateStateVar; + updateStateVar << "UPDATE " << WORLD_STATES_TBL_NAME + << " SET value = '" << value << "', " + << " moddate = '" << time(NULL) << "' " + << " WHERE state_name = '" << name << "'"; + if (map_id >= 0) { - query1 << " AND map_id = '" << map_id << "'"; + updateStateVar << " AND map_id = '" << map_id << "'"; } - query1 << ";"; - mDb->execSql(query1.str()); + updateStateVar << ";"; + mDb->execSql(updateStateVar.str()); - if (value.empty()) return; + // if we updated a row, were finished here + if (mDb->getModifiedRows() >= 1) + { + return; + } - std::ostringstream query2; - query2 << "INSERT INTO " << WORLD_STATES_TBL_NAME - << " (state_name, map_id, value , moddate) VALUES (" - << "'" << name << "', "; + // otherwise we have to add the new variable + std::ostringstream insertStateVar; + insertStateVar << "INSERT INTO " << WORLD_STATES_TBL_NAME + << " (state_name, map_id, value , moddate) VALUES (" + << "'" << name << "', "; if (map_id >= 0) { - query2 << "'" << map_id << "', "; + insertStateVar << "'" << map_id << "', "; } else { - query2 << "NULL , "; + insertStateVar << "NULL , "; } - query2 << "'" << value << "', " - << "'" << time(NULL) << "');"; - mDb->execSql(query2.str()); + insertStateVar << "'" << value << "', " + << "'" << time(NULL) << "');"; + mDb->execSql(insertStateVar.str()); } catch (dal::DbSqlQueryExecFailure const &e) { @@ -1254,6 +1135,57 @@ void DALStorage::banCharacter(int id, int duration) } } +void DALStorage::delCharacter(int charId, bool startTransaction = true) const +{ + if (startTransaction) + mDb->beginTransaction(); + try + { + // delete the inventory of the character + std::ostringstream sqlDeleteInventoryTable; + sqlDeleteInventoryTable + << "DELETE FROM " << INVENTORIES_TBL_NAME + << " WHERE owner_id = '" << charId << "';"; + mDb->execSql(sqlDeleteInventoryTable.str()); + + // delete from the quests table + std::ostringstream sqlDeleteQuestsTable; + sqlDeleteQuestsTable + << "DELETE FROM " << QUESTS_TBL_NAME + << " WHERE owner_id = '" << charId << "';"; + mDb->execSql(sqlDeleteQuestsTable.str()); + + // delete from the guilds table + std::ostringstream sqlDeleteGuildsTable; + sqlDeleteGuildsTable + << "DELETE FROM " << GUILD_MEMBERS_TBL_NAME + << " WHERE member_id = '" << charId << "';"; + mDb->execSql(sqlDeleteGuildsTable.str()); + + // now delete the character itself. + std::ostringstream sqlDeleteCharactersTable; + sqlDeleteCharactersTable + << "DELETE FROM " << CHARACTERS_TBL_NAME + << " WHERE id = '" << charId << "';"; + mDb->execSql(sqlDeleteCharactersTable.str()); + + if (startTransaction) + mDb->commitTransaction(); + } + catch (dal::DbSqlQueryExecFailure const &e) + { + if (startTransaction) + mDb->rollbackTransaction(); + LOG_ERROR("(DALStorage::delCharacter) SQL query failure: " << e.what()); + } +} + +void DALStorage::delCharacter(Character *character, + bool startTransaction = true) const +{ + delCharacter(character->getDatabaseID(), startTransaction); +} + void DALStorage::checkBannedAccounts() { try diff --git a/src/account-server/dalstorage.hpp b/src/account-server/dalstorage.hpp index 099ec14..1085a3a 100644 --- a/src/account-server/dalstorage.hpp +++ b/src/account-server/dalstorage.hpp @@ -132,6 +132,25 @@ class DALStorage void banCharacter(int id, int duration); /** + * 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 delCharacter(int charId, bool startTransaction) const; + + /** + * Delete a character in the database. The object itself i 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 delCharacter(Character *character, bool startTransaction) const; + + /** * Removes expired bans from accounts */ void checkBannedAccounts(); @@ -269,34 +288,6 @@ class DALStorage DALStorage& operator=(const DALStorage& rhs); - - /** - * Create the specified table. - * - * @param tblName the table name. - * @param sql the SQL query to execute. - * - * @exception dal::DbSqlQueryExecFailure. - */ - void - createTable(const std::string& tblName, - const std::string& sql); - - - /** - * Create an index on the specified column. - * - * @param indxName the name of the index. - * @param tblName the name of the table. - * @param columnName the name of the columns - * - * @exception dal::DbSqlQueryExecFailure. - */ - void - createIndex(const std::string& indxName, - const std::string& tblName, - const std::string& columnName ); - /** * Gets an account by using a SQL query string. * diff --git a/src/account-server/dalstoragesql.hpp b/src/account-server/dalstoragesql.hpp index b773770..0165d1b 100644 --- a/src/account-server/dalstoragesql.hpp +++ b/src/account-server/dalstoragesql.hpp @@ -1,6 +1,6 @@ /* * The Mana World Server - * Copyright 2004 The Mana World Development Team + * Copyright 2008 The Mana World Development Team * * This file is part of The Mana World. * @@ -66,288 +66,39 @@ * TABLE: tmw_accounts. */ static char const *ACCOUNTS_TBL_NAME = "tmw_accounts"; -static char const *SQL_ACCOUNTS_TABLE = - "CREATE TABLE tmw_accounts \n " - "( \n" -#if defined (MYSQL_SUPPORT) - "id INTEGER PRIMARY KEY AUTO_INCREMENT," - "username VARCHAR(32) NOT NULL UNIQUE," - "password VARCHAR(32) NOT NULL," - "email VARCHAR(64) NOT NULL," - "level TINYINT UNSIGNED NOT NULL," - "banned TINYINT UNSIGNED NOT NULL," - "registration INTEGER NOT NULL," - "lastlogin INTEGER NOT NULL," -// "activation VARCHAR(32)," - "INDEX (id)" -#error "Incorrect definition. Please fix the types." -#elif defined (SQLITE_SUPPORT) - "id INTEGER PRIMARY KEY, \n" - "username TEXT NOT NULL UNIQUE, \n" - "password TEXT NOT NULL, \n" - "email TEXT NOT NULL, \n" - "level INTEGER NOT NULL, \n" - "banned INTEGER NOT NULL, \n" - "registration INTEGER NOT NULL, \n" - "lastlogin INTEGER NOT NULL \n" -// "activation TEXT" -#elif defined (POSTGRESQL_SUPPORT) - "id SERIAL PRIMARY KEY," - "username TEXT NOT NULL UNIQUE," - "password TEXT NOT NULL," - "email TEXT NOT NULL," - "level SMALLINT NOT NULL," - "banned SMALLINT NOT NULL," - "registration INTEGER NOT NULL," - "lastlogin INTEGER NOT NULL" -// "activation TEXT" -#endif - ");"; - /** * TABLE: tmw_characters. * - gender is 0 for male, 1 for female. */ static char const *CHARACTERS_TBL_NAME = "tmw_characters"; -static char const *SQL_CHARACTERS_TABLE = - "CREATE TABLE tmw_characters (" -#if defined (MYSQL_SUPPORT) - "id INTEGER PRIMARY KEY AUTO_INCREMENT," - "user_id INTEGER UNSIGNED NOT NULL," - "name VARCHAR(32) NOT NULL UNIQUE," - // general information about the character - "gender TINYINT UNSIGNED NOT NULL," - "hair_style TINYINT UNSIGNED NOT NULL," - "hair_color TINYINT UNSIGNED NOT NULL," - "level INTEGER UNSIGNED NOT NULL," - "char_pts INTEGER UNSIGNED NOT NULL," - "correct_pts INTEGER UNSIGNED NOT NULL," - "money INTEGER UNSIGNED NOT NULL," - // location on the map - "x SMALLINT UNSIGNED NOT NULL," - "y SMALLINT UNSIGNED NOT NULL," - "map_id TINYINT NOT NULL," - // attributes - "str SMALLINT UNSIGNED NOT NULL," - "agi SMALLINT UNSIGNED NOT NULL," - "dex SMALLINT UNSIGNED NOT NULL," - "vit SMALLINT UNSIGNED NOT NULL," - // note: int must be backquoted as it's a MySQL keyword - "`int` SMALLINT UNSIGNED NOT NULL," - "will SMALLINT UNSIGNED NOT NULL," - //skill experience - "unarmedExp INTEGER UNSIGNED NOT NULL," - "knife_exp INTEGER UNSIGNED NOT NULL," - "sword_exp INTEGER UNSIGNED NOT NULL," - "polearm_exp INTEGER UNSIGNED NOT NULL," - "staff_exp INTEGER UNSIGNED NOT NULL," - "whip_exp INTEGER UNSIGNED NOT NULL," - "bow_exp INTEGER UNSIGNED NOT NULL," - "shoot_exp INTEGER UNSIGNED NOT NULL," - "mace_exp INTEGER UNSIGNED NOT NULL," - "axe_exp INTEGER UNSIGNED NOT NULL," - "thrown_exp INTEGER UNSIGNED NOT NULL," - "FOREIGN KEY (user_id) REFERENCES tmw_accounts(id)," - "FOREIGN KEY (map_id) REFERENCES tmw_maps(id)," - "INDEX (id)" -#elif defined (SQLITE_SUPPORT) - "id INTEGER PRIMARY KEY," - "user_id INTEGER NOT NULL," - "name TEXT NOT NULL UNIQUE," - // general information about the character - "gender INTEGER NOT NULL," - "hair_style INTEGER NOT NULL," - "hair_color INTEGER NOT NULL," - "level INTEGER NOT NULL," - "char_pts INTEGER NOT NULL," - "correct_pts INTEGER NOT NULL," - "money INTEGER NOT NULL," - // location on the map - "x INTEGER NOT NULL," - "y INTEGER NOT NULL," - "map_id INTEGER NOT NULL," - // attributes - "str INTEGER NOT NULL," - "agi INTEGER NOT NULL," - "dex INTEGER NOT NULL," - "vit INTEGER NOT NULL," - "int INTEGER NOT NULL," - "will INTEGER NOT NULL," - //skill experience - "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)," - "FOREIGN KEY (map_id) REFERENCES tmw_maps(id)" -#elif defined (POSTGRESQL_SUPPORT) - "id SERIAL PRIMARY KEY," - "user_id INTEGER NOT NULL," - "name TEXT NOT NULL UNIQUE," - // general information about the character - "gender SMALLINT NOT NULL," - "hair_style SMALLINT NOT NULL," - "hair_color INTEGER NOT NULL," - "level INTEGER NOT NULL," - "char_pts INTEGER NOT NULL," - "correct_pts INTEGER NOT NULL," - "money INTEGER NOT NULL," - // location on the map - "x SMALLINT NOT NULL," - "y SMALLINT NOT NULL," - "map_id SMALLINT NOT NULL," - // attributes - "str SMALLINT NOT NULL," - "agi SMALLINT NOT NULL," - "dex SMALLINT NOT NULL," - "vit SMALLINT NOT NULL," - "int SMALLINT NOT NULL," - "will SMALLINT NOT NULL," - //skill experience - "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)," - "FOREIGN KEY (map_id) REFERENCES tmw_maps(id)" -#endif - ");"; - /** * TABLE: tmw_inventories. */ static char const *INVENTORIES_TBL_NAME("tmw_inventories"); -static char const *SQL_INVENTORIES_TABLE = - "CREATE TABLE tmw_inventories (" -#if defined (MYSQL_SUPPORT) - "id INTEGER PRIMARY KEY AUTO_INCREMENT," - "owner_id INTEGER NOT NULL," - "slot SMALLINT NOT NULL," - "class_id INTEGER NOT NULL," - "amount SMALLINT NOT NULL," - "FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)" - "INDEX (id)" -#elif defined (SQLITE_SUPPORT) - "id INTEGER PRIMARY KEY," - "owner_id INTEGER NOT NULL," - "slot INTEGER NOT NULL," - "class_id INTEGER NOT NULL," - "amount INTEGER NOT NULL," - "FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)" -#elif defined (POSTGRESQL_SUPPORT) - "id SERIAL PRIMARY KEY," - "owner_id INTEGER NOT NULL," - "slot SMALLINT NOT NULL," - "class_id INTEGER NOT NULL," - "amount SMALLINT NOT NULL," - "FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)" -#endif - ");"; /** * TABLE: tmw_guilds. * Store player guilds */ static char const *GUILDS_TBL_NAME = "tmw_guilds"; -static char const *SQL_GUILDS_TABLE = - "CREATE TABLE tmw_guilds (" -#if defined (MYSQL_SUPPORT) - "id INTEGER PRIMARY KEY AUTO_INCREMENT," - "name VARCHAR(32) NOT NULL UNIQUE" -#elif defined (SQLITE_SUPPORT) - "id INTEGER PRIMARY KEY," - "name TEXT NOT NULL UNIQUE" -#elif defined (POSTGRESQL_SUPPORT) - "id SERIAL PRIMARY KEY," - "name TEXT NOT NULL UNIQUE" -#endif - ");"; /** * TABLE: tmw_guild_members. * Store guild members */ static char const *GUILD_MEMBERS_TBL_NAME = "tmw_guild_members"; -static char const *SQL_GUILD_MEMBERS_TABLE = - "CREATE TABLE tmw_guild_members (" -#if defined (MYSQL_SUPPORT) - "guild_id INTEGER NOT NULL," - "member_id INTEGER NOT NULL," - "rights INTEGER NOT NULL," - "FOREIGN KEY (guild_id) REFERENCES tmw_guilds(id)," - "FOREIGN KEY (member_id) REFERENCES tmw_characters(id)" -#elif defined (SQLITE_SUPPORT) - "guild_id INTEGER NOT NULL," - "member_id INTEGER NOT NULL," - "rights INTEGER NOT NULL," - "FOREIGN KEY (guild_id) REFERENCES tmw_guilds(id)," - "FOREIGN KEY (member_id) REFERENCES tmw_characters(id)" -#elif defined (POSTGRESQL_SUPPORT) - "guild_id INTEGER NOT NULL," - "member_id INTEGER NOT NULL," - "rights INTEGER NOT NULL," - "FOREIGN KEY (guild_id) REFERENCES tmw_guilds(id)," - "FOREIGN KEY (member_id) REFERENCES tmw_characters(id)" -#endif - ");"; /** * TABLE: tmw_quests. */ static char const *QUESTS_TBL_NAME = "tmw_quests"; -static char const *SQL_QUESTS_TABLE = - "CREATE TABLE tmw_quests (" -#if defined (MYSQL_SUPPORT) -#error "Missing definition. Please fill the blanks." -#elif defined (SQLITE_SUPPORT) - "owner_id INTEGER NOT NULL," - "name TEXT NOT NULL," - "value TEXT NOT NULL," - "FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)" -#elif defined (POSTGRESQL_SUPPORT) - "owner_id INTEGER NOT NULL," - "name TEXT NOT NULL," - "value TEXT NOT NULL," - "FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)" -#endif - ");"; /** * TABLE: tmw_world_states */ static char const *WORLD_STATES_TBL_NAME = "tmw_world_states"; -static char const *SQL_WORLD_STATES_TABLE = - "CREATE TABLE tmw_world_states \n" - "( \n" -#if defined (MYSQL_SUPPORT) - "state_name VARCHAR(100) NOT NULL, \n" - "map_id SMALLINT UNSIGNED NULL, \n" - "value VARCHAR(255) NULL, \n" - "moddate INT UNSIGNED NOT NULL, \n" - "PRIMARY KEY(state_name) \n" -#elif defined (SQLITE_SUPPORT) - "state_name TEXT PRIMARY KEY, \n" - "map_id INTEGER NULL, \n" - "value TEXT NULL, \n" - "moddate INTEGER NOT NULL \n" -#elif defined (POSTGRESQL_SUPPORT) -#endif - ");"; + #endif // _TMWSERV_DALSTORAGE_SQL_H_ diff --git a/src/dal/dataprovider.h b/src/dal/dataprovider.h index 07d29b4..2c0a9de 100644 --- a/src/dal/dataprovider.h +++ b/src/dal/dataprovider.h @@ -26,6 +26,7 @@ #include <string> +#include <stdexcept> #include "recordset.h" @@ -96,16 +97,12 @@ class DataProvider /** * Create a connection to the database. * - * @param dbName the database name. - * @param userName the user name. - * @param password the user password. + * Each dataprovider is responsible to have default values and load + * necessary options from the config file. * * @exception DbConnectionFailure if unsuccessful connection. */ - virtual void - connect(const std::string& dbName, - const std::string& userName, - const std::string& password) = 0; + virtual void connect(void) = 0; /** @@ -138,7 +135,50 @@ class DataProvider std::string getDbName(void); + /** + * Starts a transaction. + * + * @exception std::runtime_error if a transaction is still open + */ + virtual void + beginTransaction(void) + throw (std::runtime_error) = 0; + + /** + * Commits a transaction. + * + * @exception std::runtime_error if no connection is currently open. + */ + virtual void + commitTransaction(void) + throw (std::runtime_error) = 0; + + /** + * Rollback a transaction. + * + * @exception std::runtime_error if no connection is currently open. + */ + virtual void + rollbackTransaction(void) + throw (std::runtime_error) = 0; + /** + * Returns the number of changed rows by the last executed SQL + * statement. + * + * @return Number of rows that have changed. + */ + virtual const unsigned int + getModifiedRows(void) const = 0; + + /** + * Returns the last inserted value of an autoincrement column after an + * INSERT statement. + * + * @return last autoincrement value. + */ + virtual const unsigned int + getLastId(void) const = 0; protected: std::string mDbName; /**< the database name */ diff --git a/src/dal/mysqldataprovider.cpp b/src/dal/mysqldataprovider.cpp index 85084dc..f6e422f 100644 --- a/src/dal/mysqldataprovider.cpp +++ b/src/dal/mysqldataprovider.cpp @@ -28,6 +28,18 @@ namespace dal { +const std::string MySqlDataProvider::CFGPARAM_MYSQL_HOST ="mysql_hostname"; +const std::string MySqlDataProvider::CFGPARAM_MYSQL_PORT ="mysql_port"; +const std::string MySqlDataProvider::CFGPARAM_MYSQL_DB ="mysql_database"; +const std::string MySqlDataProvider::CFGPARAM_MYSQL_USER ="mysql_username"; +const std::string MySqlDataProvider::CFGPARAM_MYSQL_PWD ="mysql_password"; + +const std::string MySqlDataProvider::CFGPARAM_MYSQL_HOST_DEF = "localhost"; +const unsigned int MySqlDataProvider::CFGPARAM_MYSQL_PORT_DEF = 3306; +const std::string MySqlDataProvider::CFGPARAM_MYSQL_DB_DEF = "tmw"; +const std::string MySqlDataProvider::CFGPARAM_MYSQL_USER_DEF = "tmw"; +const std::string MySqlDataProvider::CFGPARAM_MYSQL_PWD_DEF = "tmw"; + /** * Constructor. */ @@ -71,14 +83,24 @@ MySqlDataProvider::getDbBackend(void) const * Create a connection to the database. */ void -MySqlDataProvider::connect(const std::string& dbName, - const std::string& userName, - const std::string& password) +MySqlDataProvider::connect() { if (mIsConnected) { return; } + // retrieve configuration from config file + const std::string hostname + = Configuration::getValue(CFGPARAM_MYSQL_HOST, CFGPARAM_MYSQL_HOST_DEF); + const std::string dbName + = Configuration::getValue(CFGPARAM_MYSQL_DB, CFGPARAM_MYSQL_DB_DEF); + const std::string username + = Configuration::getValue(CFGPARAM_MYSQL_USER, CFGPARAM_MYSQL_USER_DEF); + const std::string password + = Configuration::getValue(CFGPARAM_MYSQL_PWD, CFGPARAM_MYSQL_PWD_DEF); + const unsigned int tcpPort + = Configuration::getValue(CFGPARAM_MYSQL_PORT, CFGPARAM_MYSQL_PORT_DEF); + // allocate and initialize a new MySQL object suitable // for mysql_real_connect(). mDb = mysql_init(NULL); @@ -88,17 +110,19 @@ MySqlDataProvider::connect(const std::string& dbName, "unable to initialize the MySQL library: no memory"); } - // insert connection options here. + LOG_INFO("Trying to connect with mySQL database server '" + << hostname << ":" << tcpPort << "' using '" << username + << "' as user, and '" << dbName << "' as database."); // actually establish the connection. - if (!mysql_real_connect(mDb, // handle to the connection - NULL, // localhost - userName.c_str(), // user name - password.c_str(), // user password - dbName.c_str(), // database name - 0, // use default TCP port - NULL, // use defaut socket - 0)) // client flags + if (!mysql_real_connect(mDb, // handle to the connection + hostname.c_str(), // hostname + username.c_str(), // username + password.c_str(), // password + dbName.c_str(), // database name + tcpPort, // tcp port + NULL, // socket, currently not used + 0)) // client flags { std::string msg(mysql_error(mDb)); mysql_close(mDb); @@ -110,6 +134,7 @@ MySqlDataProvider::connect(const std::string& dbName, mDbName = dbName; mIsConnected = true; + LOG_INFO("Connection to mySQL was sucessfull."); } @@ -124,6 +149,8 @@ MySqlDataProvider::execSql(const std::string& sql, throw std::runtime_error("not connected to database"); } + LOG_DEBUG("Performing SQL query: "<<sql); + // do something only if the query is different from the previous // or if the cache must be refreshed // otherwise just return the recordset from cache. @@ -194,5 +221,108 @@ MySqlDataProvider::disconnect(void) mIsConnected = false; } +void +MySqlDataProvider::beginTransaction(void) + throw (std::runtime_error) +{ + if (!mIsConnected) + { + const std::string error = "Trying to begin a transaction while not " + "connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + mysql_autocommit(mDb, AUTOCOMMIT_OFF); + execSql("BEGIN"); + LOG_DEBUG("SQL: started transaction"); +} + +void +MySqlDataProvider::commitTransaction(void) + throw (std::runtime_error) +{ + if (!mIsConnected) + { + const std::string error = "Trying to commit a transaction while not " + "connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + if (mysql_commit(mDb) != 0) + { + LOG_ERROR("MySqlDataProvider::commitTransaction: " << mysql_error(mDb)); + throw DbSqlQueryExecFailure(mysql_error(mDb)); + } + mysql_autocommit(mDb, AUTOCOMMIT_ON); + LOG_DEBUG("SQL: commited transaction"); +} + +void +MySqlDataProvider::rollbackTransaction(void) + throw (std::runtime_error) +{ + if (!mIsConnected) + { + const std::string error = "Trying to rollback a transaction while not " + "connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + if (mysql_rollback(mDb) != 0) + { + LOG_ERROR("MySqlDataProvider::rollbackTransaction: " << mysql_error(mDb)); + throw DbSqlQueryExecFailure(mysql_error(mDb)); + } + mysql_autocommit(mDb, AUTOCOMMIT_ON); + LOG_DEBUG("SQL: transaction rolled back"); +} + +const unsigned int +MySqlDataProvider::getModifiedRows(void) const +{ + if (!mIsConnected) + { + const std::string error = "Trying to getModifiedRows while not " + "connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + // FIXME: not sure if this is correct to bring 64bit int into int? + const my_ulonglong affected = mysql_affected_rows(mDb); + + if (affected > INT_MAX) + throw std::runtime_error("MySqlDataProvider::getLastId exceeded INT_MAX"); + + if (affected == (my_ulonglong)-1) + { + LOG_ERROR("MySqlDataProvider::getModifiedRows: " << mysql_error(mDb)); + throw DbSqlQueryExecFailure(mysql_error(mDb)); + } + + return (unsigned int)affected; +} + +const unsigned int +MySqlDataProvider::getLastId(void) const +{ + if (!mIsConnected) + { + const std::string error = "not connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + // FIXME: not sure if this is correct to bring 64bit int into int? + const my_ulonglong lastId = mysql_insert_id(mDb); + if (lastId > UINT_MAX) + throw std::runtime_error("MySqlDataProvider::getLastId exceeded INT_MAX"); + + return (unsigned int)lastId; +} + } // namespace dal diff --git a/src/dal/mysqldataprovider.h b/src/dal/mysqldataprovider.h index f246603..08c56dd 100644 --- a/src/dal/mysqldataprovider.h +++ b/src/dal/mysqldataprovider.h @@ -26,10 +26,15 @@ #include <iosfwd> - +// added to compile under windows +#ifdef WIN32 +#include <winsock2.h> +#endif #include <mysql/mysql.h> #include "dataprovider.h" +#include "common/configuration.hpp" +#include "utils/logger.h" namespace dal { @@ -41,6 +46,16 @@ namespace dal class MySqlDataProvider: public DataProvider { public: + + /** + * Replacement for mysql my_bool datatype used in mysql_autocommit() + * function. + */ + enum { + AUTOCOMMIT_OFF = 0, + AUTOCOMMIT_ON = 1 + }; + /** * Constructor. */ @@ -68,16 +83,9 @@ class MySqlDataProvider: public DataProvider /** * Create a connection to the database. * - * @param dbName the database name. - * @param userName the user name. - * @param password the user password. - * * @exception DbConnectionFailure if unsuccessful connection. */ - void - connect(const std::string& dbName, - const std::string& userName, - const std::string& password); + void connect(); /** @@ -104,8 +112,76 @@ class MySqlDataProvider: public DataProvider void disconnect(void); + /** + * Starts a transaction. + * + * @exception std::runtime_error if a transaction is still open + */ + void + beginTransaction(void) + throw (std::runtime_error); + + /** + * Commits a transaction. + * + * @exception std::runtime_error if no connection is currently open. + */ + void + commitTransaction(void) + throw (std::runtime_error); + + /** + * Rollback a transaction. + * + * @exception std::runtime_error if no connection is currently open. + */ + void + rollbackTransaction(void) + throw (std::runtime_error); + + /** + * Returns the number of changed rows by the last executed SQL + * statement. + * + * @return Number of rows that have changed. + */ + const unsigned int + getModifiedRows(void) const; + + /** + * Returns the last inserted value of an autoincrement column after an + * INSERT statement. + * + * @return last autoincrement value. + */ + const unsigned int + getLastId(void) const; private: + + /** defines the name of the hostname config parameter */ + static const std::string CFGPARAM_MYSQL_HOST; + /** defines the name of the server port config parameter */ + static const std::string CFGPARAM_MYSQL_PORT; + /** defines the name of the database config parameter */ + static const std::string CFGPARAM_MYSQL_DB; + /** defines the name of the username config parameter */ + static const std::string CFGPARAM_MYSQL_USER; + /** defines the name of the password config parameter */ + static const std::string CFGPARAM_MYSQL_PWD; + + /** defines the default value of the CFGPARAM_MYSQL_HOST parameter */ + static const std::string CFGPARAM_MYSQL_HOST_DEF; + /** defines the default value of the CFGPARAM_MYSQL_PORT parameter */ + static const unsigned int CFGPARAM_MYSQL_PORT_DEF; + /** defines the default value of the CFGPARAM_MYSQL_DB parameter */ + static const std::string CFGPARAM_MYSQL_DB_DEF; + /** defines the default value of the CFGPARAM_MYSQL_USER parameter */ + static const std::string CFGPARAM_MYSQL_USER_DEF; + /** defines the default value of the CFGPARAM_MYSQL_PWD parameter */ + static const std::string CFGPARAM_MYSQL_PWD_DEF; + + MYSQL* mDb; /**< the handle to the database connection */ }; diff --git a/src/dal/sqlitedataprovider.cpp b/src/dal/sqlitedataprovider.cpp index b126c19..fb539ec 100644 --- a/src/dal/sqlitedataprovider.cpp +++ b/src/dal/sqlitedataprovider.cpp @@ -32,6 +32,10 @@ namespace dal { +const std::string SqLiteDataProvider::CFGPARAM_SQLITE_DB = "sqlite_database"; +const std::string SqLiteDataProvider::CFGPARAM_SQLITE_DB_DEF = "tmw.db"; + + /** * Constructor. */ @@ -78,10 +82,15 @@ SqLiteDataProvider::getDbBackend(void) const * Create a connection to the database. */ void -SqLiteDataProvider::connect(const std::string& dbName, - const std::string& userName, - const std::string& password) +SqLiteDataProvider::connect() { + // get configuration parameter for sqlite + const std::string dbName + = Configuration::getValue(CFGPARAM_SQLITE_DB, CFGPARAM_SQLITE_DB_DEF); + + LOG_INFO("Trying to connect with SQLite database file '" + << dbName << "'"); + // sqlite3_open creates the database file if it does not exist // as a side-effect. if (sqlite3_open(dbName.c_str(), &mDb) != SQLITE_OK) { @@ -104,6 +113,7 @@ SqLiteDataProvider::connect(const std::string& dbName, mDbName = dbName; mIsConnected = true; + LOG_INFO("Connection to database sucessfull."); } @@ -118,7 +128,7 @@ SqLiteDataProvider::execSql(const std::string& sql, throw std::runtime_error("not connected to database"); } - LOG_DEBUG("Performing SQL querry: "<<sql); + LOG_DEBUG("Performing SQL query: "<<sql); // do something only if the query is different from the previous // or if the cache must be refreshed @@ -198,5 +208,166 @@ SqLiteDataProvider::disconnect(void) mIsConnected = false; } +void +SqLiteDataProvider::beginTransaction(void) + throw (std::runtime_error) +{ + if (!mIsConnected) + { + const std::string error = "Trying to begin a transaction while not " + "connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + if (inTransaction()) + { + const std::string error = "Trying to begin a transaction while anoter " + "one is still open!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + // trying to open a transaction + try + { + execSql("BEGIN TRANSACTION;"); + LOG_DEBUG("SQL: started transaction"); + } + catch (const DbSqlQueryExecFailure &e) + { + std::ostringstream error; + error << "SQL ERROR while trying to start a transaction: " << e.what(); + LOG_ERROR(error); + throw std::runtime_error(error.str()); + } +} + +void +SqLiteDataProvider::commitTransaction(void) + throw (std::runtime_error) +{ + if (!mIsConnected) + { + const std::string error = "Trying to commit a transaction while not " + "connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + if (!inTransaction()) + { + const std::string error = "Trying to commit a transaction while no " + "one is open!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + // trying to commit a transaction + try + { + execSql("COMMIT TRANSACTION;"); + LOG_DEBUG("SQL: commited transaction"); + } + catch (const DbSqlQueryExecFailure &e) + { + std::ostringstream error; + error << "SQL ERROR while trying to commit a transaction: " << e.what(); + LOG_ERROR(error); + throw std::runtime_error(error.str()); + } +} + +void +SqLiteDataProvider::rollbackTransaction(void) + throw (std::runtime_error) +{ + if (!mIsConnected) + { + const std::string error = "Trying to rollback a transaction while not " + "connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + if (!inTransaction()) + { + const std::string error = "Trying to rollback a transaction while no " + "one is open!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + // trying to rollback a transaction + try + { + execSql("ROLLBACK TRANSACTION;"); + LOG_DEBUG("SQL: transaction rolled back"); + } + catch (const DbSqlQueryExecFailure &e) + { + std::ostringstream error; + error << "SQL ERROR while trying to rollback a transaction: " << e.what(); + LOG_ERROR(error); + throw std::runtime_error(error.str()); + } +} + +const unsigned int +SqLiteDataProvider::getModifiedRows(void) const +{ + if (!mIsConnected) + { + const std::string error = "Trying to getModifiedRows while not " + "connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + return (unsigned int)sqlite3_changes(mDb); +} + +const bool +SqLiteDataProvider::inTransaction(void) const +{ + if (!mIsConnected) + { + const std::string error = "not connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + // The sqlite3_get_autocommit() interface returns non-zero or zero if the + // given database connection is or is not in autocommit mode, respectively. + // Autocommit mode is on by default. Autocommit mode is disabled by a BEGIN + // statement. Autocommit mode is re-enabled by a COMMIT or ROLLBACK. + const int ret = sqlite3_get_autocommit(mDb); + if (ret == 0) + { + return true; + } + else + { + return false; + } +} + +const unsigned int +SqLiteDataProvider::getLastId(void) const +{ + if (!mIsConnected) + { + const std::string error = "not connected to the database!"; + LOG_ERROR(error); + throw std::runtime_error(error); + } + + // FIXME: not sure if this is correct to bring 64bit int into int? + const sqlite3_int64 lastId = sqlite3_last_insert_rowid(mDb); + if (lastId > UINT_MAX) + throw std::runtime_error("SqLiteDataProvider::getLastId exceeded INT_MAX"); + + return (unsigned int)lastId; +} } // namespace dal diff --git a/src/dal/sqlitedataprovider.h b/src/dal/sqlitedataprovider.h index b791025..ea85d02 100644 --- a/src/dal/sqlitedataprovider.h +++ b/src/dal/sqlitedataprovider.h @@ -25,13 +25,21 @@ #include <iosfwd> #include <sqlite3.h> +#include "common/configuration.hpp" + + +// sqlite3_int64 is the preferred new datatype for 64-bit int values. +// see: http://www.sqlite.org/capi3ref.html#sqlite3_int64 +#ifndef sqlite3_int64 +typedef sqlite_int64 sqlite3_int64; +#endif + #include "dataprovider.h" namespace dal { - /** * A SQLite Data Provider. */ @@ -65,16 +73,9 @@ class SqLiteDataProvider: public DataProvider /** * Create a connection to the database. * - * @param dbName the database name. - * @param userName the user name. - * @param password the user password. - * * @exception DbConnectionFailure if unsuccessful connection. */ - void - connect(const std::string& dbName, - const std::string& userName, - const std::string& password); + void connect(); /** @@ -101,8 +102,67 @@ class SqLiteDataProvider: public DataProvider void disconnect(void); + /** + * Starts a transaction. + * + * @exception std::runtime_error if a transaction is still open + */ + void + beginTransaction(void) + throw (std::runtime_error); + + /** + * Commits a transaction. + * + * @exception std::runtime_error if no connection is currently open. + */ + void + commitTransaction(void) + throw (std::runtime_error); + + /** + * Rollback a transaction. + * + * @exception std::runtime_error if no connection is currently open. + */ + void + rollbackTransaction(void) + throw (std::runtime_error); + + /** + * Returns the number of changed rows by the last executed SQL + * statement. + * + * @return Number of rows that have changed. + */ + const unsigned int + getModifiedRows(void) const; + + /** + * Returns the last inserted value of an autoincrement column after an + * INSERT statement. + * + * @return last autoincrement value. + */ + const unsigned int + getLastId(void) const; private: + + /** defines the name of the database config parameter */ + static const std::string CFGPARAM_SQLITE_DB; + /** defines the default value of the CFGPARAM_SQLITE_DB parameter */ + static const std::string CFGPARAM_SQLITE_DB_DEF; + + /** + * Returns wheter the connection has a open transaction or is in auto- + * commit mode. + * + * @return true, if a transaction is open. + */ + const bool + inTransaction(void) const; + sqlite3* mDb; /**< the handle to the database connection */ }; diff --git a/src/sql/mysql/createDatabase.sql b/src/sql/mysql/createDatabase.sql new file mode 100644 index 0000000..c40aa24 --- /dev/null +++ b/src/sql/mysql/createDatabase.sql @@ -0,0 +1,34 @@ +/* + * The Mana World Server + * Copyright 2008 The Mana World Development Team + * + * This file is part of The Mana World. + * + * The Mana World 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 World 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 World; if not, write to the Free Software Foundation, Inc., + * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + * + * $Id$ + */ + +CREATE USER 'tmw'@'%' IDENTIFIED BY 'testtest'; +CREATE USER 'tmw'@'localhost' IDENTIFIED BY 'testtest'; + +GRANT USAGE ON * . * TO 'tmw'@'%' IDENTIFIED BY 'testtest' + WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; +GRANT USAGE ON * . * TO 'tmw'@'localhost' IDENTIFIED BY 'testtest' + WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; + +CREATE DATABASE IF NOT EXISTS `tmw` ; + +GRANT ALL PRIVILEGES ON `tmw` . * TO 'tmw'@'%'; +GRANT ALL PRIVILEGES ON `tmw` . * TO 'tmw'@'localhost'; diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql new file mode 100644 index 0000000..3bcf665 --- /dev/null +++ b/src/sql/mysql/createTables.sql @@ -0,0 +1,153 @@ +-- +-- table: `tmw_accounts` +-- + +CREATE TABLE IF NOT EXISTS `tmw_accounts` ( + `id` int(10) unsigned NOT NULL auto_increment, + `username` varchar(64) NOT NULL, + `password` varchar(64) NOT NULL, + `email` varchar(32) NOT NULL, + `level` tinyint(3) unsigned NOT NULL, + `banned` int(10) unsigned NOT NULL, + `registration` int(10) unsigned NOT NULL, + `lastlogin` int(10) unsigned NOT NULL, + -- + PRIMARY KEY (`id`), + UNIQUE KEY `username` (`username`), + UNIQUE KEY `email` (`email`) +) ENGINE=InnoDB +DEFAULT CHARSET=utf8_general_ci +AUTO_INCREMENT=1 ; + +-- +-- table: `tmw_characters` +-- + +CREATE TABLE IF NOT EXISTS `tmw_characters` ( + `id` int(10) unsigned NOT NULL auto_increment, + `user_id` int(10) unsigned NOT NULL, + `name` varchar(32) NOT NULL, + -- + `gender` tinyint(3) unsigned NOT NULL, + `hair_style` tinyint(3) unsigned NOT NULL, + `hair_color` tinyint(3) unsigned NOT NULL, + `level` tinyint(3) unsigned NOT NULL, + `char_pts` smallint(5) unsigned NOT NULL, + `correct_pts` smallint(5) unsigned NOT NULL, + `money` int(10) unsigned NOT NULL, + -- location on the map + `x` smallint(5) unsigned NOT NULL, + `y` smallint(5) unsigned NOT NULL, + `map_id` tinyint(3) unsigned NOT NULL, + -- attributes + `str` smallint(5) unsigned NOT NULL, + `agi` smallint(5) unsigned NOT NULL, + `dex` smallint(5) unsigned NOT NULL, + `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`), + KEY `user_id` (`user_id`), + FOREIGN KEY (`user_id`) + REFERENCES `tmw_accounts` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8_general_ci +AUTO_INCREMENT=1 ; + +-- +-- table: `tmw_inventories` +-- + +CREATE TABLE IF NOT EXISTS `tmw_inventories` ( + `id` int(10) unsigned NOT NULL auto_increment, + `owner_id` int(10) unsigned NOT NULL, + `slot` tinyint(3) unsigned NOT NULL, + `class_id` int(10) unsigned NOT NULL, + `amount` tinyint(3) unsigned NOT NULL, + -- + PRIMARY KEY (`id`), + UNIQUE KEY `owner_id` (`owner_id`, `slot`), + FOREIGN KEY (`owner_id`) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8_general_ci +AUTO_INCREMENT=1 ; + +-- +-- table: `tmw_world_states` +-- + +CREATE TABLE IF NOT EXISTS `tmw_world_states` ( + `state_name` varchar(100) NOT NULL, + `map_id` int(10) unsigned default NULL, + `value` varchar(255) NOT NULL, + `moddate` int(10) unsigned NOT NULL, + -- + KEY `state_name` (`state_name`) +) ENGINE=InnoDB +DEFAULT CHARSET=utf8_general_ci; + +-- +-- table: `tmw_guilds` +-- + +CREATE TABLE IF NOT EXISTS `tmw_guilds` ( + `id` int(10) unsigned NOT NULL auto_increment, + `name` varchar(35) NOT NULL, + -- + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB +DEFAULT CHARSET=utf8_general_ci +AUTO_INCREMENT=1 ; + +-- +-- table: `tmw_guild_members` +-- + +CREATE TABLE IF NOT EXISTS `tmw_guild_members` ( + `guild_id` int(10) unsigned NOT NULL, + `member_id` int(10) unsigned NOT NULL, + `rights` int(10) unsigned NOT NULL, + -- + PRIMARY KEY (`guild_id`, `member_id`), + FOREIGN KEY (`guild_id`) + REFERENCES `tmw_guilds` (`id`) + ON DELETE CASCADE, + FOREIGN KEY (`member_id`) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8_general_ci; + +-- +-- table: `tmw_quests` +-- + +CREATE TABLE IF NOT EXISTS `tmw_quests` ( + `owner_id` int(10) unsigned NOT NULL, + `name` varchar(100) NOT NULL, + `value` varchar(200) NOT NULL, + -- + PRIMARY KEY (`owner_id`, `name`), + FOREIGN KEY (`owner_id`) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8_general_ci; diff --git a/src/sql/postgresql/createTables.sql b/src/sql/postgresql/createTables.sql new file mode 100644 index 0000000..0d95f38 --- /dev/null +++ b/src/sql/postgresql/createTables.sql @@ -0,0 +1,102 @@ +CREATE TABLE tmw_accounts +( + id SERIAL PRIMARY KEY, + username TEXT NOT NULL UNIQUE, + password TEXT NOT NULL, + email TEXT NOT NULL, + level SMALLINT NOT NULL, + banned SMALLINT NOT NULL, + registration INTEGER NOT NULL, + lastlogin INTEGER NOT NULL +); + +CREATE INDEX tmw_accounts_username ON tmw_accounts ( username ); + + +CREATE TABLE tmw_characters +( + id SERIAL PRIMARY KEY, + user_id INTEGER NOT NULL, + name TEXT NOT NULL UNIQUE, + gender SMALLINT NOT NULL, + hair_style SMALLINT NOT NULL, + hair_color INTEGER NOT NULL, + level INTEGER NOT NULL, + char_pts INTEGER NOT NULL, + correct_pts INTEGER NOT NULL, + money INTEGER NOT NULL, + x SMALLINT NOT NULL, + y SMALLINT NOT NULL, + map_id SMALLINT NOT NULL, + str SMALLINT NOT NULL, + agi SMALLINT NOT NULL, + dex SMALLINT NOT NULL, + vit SMALLINT NOT NULL, + int SMALLINT NOT NULL, + will SMALLINT 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 TABLE tmw_inventories +( + id SERIAL PRIMARY KEY, + owner_id INTEGER NOT NULL, + slot SMALLINT NOT NULL, + class_id INTEGER NOT NULL, + amount SMALLINT NOT NULL, + -- + FOREIGN KEY (owner_id) REFERENCES tmw_characters(id) +); + +CREATE TABLE tmw_guilds +( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE +); + +CREATE TABLE tmw_guild_members +( + guild_id INTEGER NOT NULL, + member_id INTEGER NOT NULL, + rights INTEGER NOT NULL, + -- + FOREIGN KEY (guild_id) REFERENCES tmw_guilds(id), + FOREIGN KEY (member_id) REFERENCES tmw_characters(id) +); + +CREATE INDEX tmw_guild_members_g ON tmw_guild_members ( guild_id ); +CREATE INDEX tmw_guild_members_m ON tmw_guild_members ( member_id ); + +CREATE TABLE tmw_quests +( + owner_id INTEGER NOT NULL, + name TEXT NOT NULL, + value TEXT NOT NULL, + -- + FOREIGN KEY (owner_id) REFERENCES tmw_characters(id) +); + +CREATE TABLE tmw_world_states +( + state_name TEXT PRIMARY KEY, + map_id INTEGER NULL, + value TEXT NULL, + moddate INTEGER NOT NULL +); + +INSERT INTO "tmw_world_states" VALUES('accountserver_startup',NULL,NULL,1221633910); +INSERT INTO "tmw_world_states" VALUES('accountserver_version',NULL,NULL,1221633910); + + diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql new file mode 100644 index 0000000..4ff336b --- /dev/null +++ b/src/sql/sqlite/createTables.sql @@ -0,0 +1,102 @@ +CREATE TABLE tmw_accounts +( + id INTEGER PRIMARY KEY, + username TEXT NOT NULL UNIQUE, + password TEXT NOT NULL, + email TEXT NOT NULL, + level INTEGER NOT NULL, + banned INTEGER NOT NULL, + registration INTEGER NOT NULL, + lastlogin INTEGER NOT NULL +); + +CREATE INDEX tmw_accounts_username ON tmw_accounts ( username ); + + +CREATE TABLE tmw_characters +( + id INTEGER PRIMARY KEY, + user_id INTEGER NOT NULL, + name TEXT NOT NULL UNIQUE, + gender INTEGER NOT NULL, + hair_style INTEGER NOT NULL, + hair_color INTEGER NOT NULL, + level INTEGER NOT NULL, + char_pts INTEGER NOT NULL, + correct_pts INTEGER NOT NULL, + money INTEGER NOT NULL, + x INTEGER NOT NULL, + y INTEGER NOT NULL, + map_id INTEGER NOT NULL, + str INTEGER NOT NULL, + agi INTEGER NOT NULL, + dex INTEGER NOT NULL, + 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 TABLE tmw_inventories +( + id INTEGER PRIMARY KEY, + owner_id INTEGER NOT NULL, + slot INTEGER NOT NULL, + class_id INTEGER NOT NULL, + amount INTEGER NOT NULL, + -- + FOREIGN KEY (owner_id) REFERENCES tmw_characters(id) +); + +CREATE TABLE tmw_guilds +( + id INTEGER PRIMARY KEY, + name TEXT NOT NULL UNIQUE +); + +CREATE TABLE tmw_guild_members +( + guild_id INTEGER NOT NULL, + member_id INTEGER NOT NULL, + rights INTEGER NOT NULL, + -- + FOREIGN KEY (guild_id) REFERENCES tmw_guilds(id), + FOREIGN KEY (member_id) REFERENCES tmw_characters(id) +); + +CREATE INDEX tmw_guild_members_g ON tmw_guild_members ( guild_id ); +CREATE INDEX tmw_guild_members_m ON tmw_guild_members ( member_id ); + +CREATE TABLE tmw_quests +( + owner_id INTEGER NOT NULL, + name TEXT NOT NULL, + value TEXT NOT NULL, + -- + FOREIGN KEY (owner_id) REFERENCES tmw_characters(id) +); + +CREATE TABLE tmw_world_states +( + state_name TEXT PRIMARY KEY, + map_id INTEGER NULL, + value TEXT NULL, + moddate INTEGER NOT NULL +); + +INSERT INTO "tmw_world_states" VALUES('accountserver_startup',NULL,NULL,1221633910); +INSERT INTO "tmw_world_states" VALUES('accountserver_version',NULL,NULL,1221633910); + + diff --git a/src/sql/sqlite/tmw.db b/src/sql/sqlite/tmw.db Binary files differnew file mode 100644 index 0000000..d212f5a --- /dev/null +++ b/src/sql/sqlite/tmw.db |