From a2af298fd993a129b657671a41f20e3975baf0ef Mon Sep 17 00:00:00 2001
From: Andreas Habel <mail@exceptionfault.de>
Date: Wed, 17 Sep 2008 11:32:45 +0000
Subject: * Added installation scripts to set up database schemas for mysql,
sqlite and postgresql. The create table statements have been completely
removed out from the c++ source into separate, provider specific sql files.
Accountserver will no longer create a sqlite file if none present. * Added
database specific config parameters to configure each provider independent. *
Simplified the connect routine of DALStorage class since every dataprovider
is now responsible to retrieve its own parameters. * Extended abstract
dataprovider to support transactions, functionally implemented for SQLite and
mySQL. * Added methods to retrieve last inserted auto-increment value and the
number of modified rows by the last statement. * Rewrite of DALStorage class
to be a little more transactional. * Fixed a bug when deleting a character.
Old function left data in quests table and guilds table. * Doxygen now also
includes non-documented functions and provides a dictionary for all classes
---
ChangeLog | 30 +-
Doxyfile | 10 +-
docs/tmwserv.xml | 51 ++-
src/account-server/dalstorage.cpp | 582 ++++++++++++++++-------------------
src/account-server/dalstorage.hpp | 47 ++-
src/account-server/dalstoragesql.hpp | 253 +--------------
src/dal/dataprovider.h | 54 +++-
src/dal/mysqldataprovider.cpp | 154 ++++++++-
src/dal/mysqldataprovider.h | 94 +++++-
src/dal/sqlitedataprovider.cpp | 179 ++++++++++-
src/dal/sqlitedataprovider.h | 78 ++++-
src/sql/mysql/createDatabase.sql | 34 ++
src/sql/mysql/createTables.sql | 153 +++++++++
src/sql/postgresql/createTables.sql | 102 ++++++
src/sql/sqlite/createTables.sql | 102 ++++++
src/sql/sqlite/tmw.db | Bin 0 -> 19456 bytes
16 files changed, 1262 insertions(+), 661 deletions(-)
create mode 100644 src/sql/mysql/createDatabase.sql
create mode 100644 src/sql/mysql/createTables.sql
create mode 100644 src/sql/postgresql/createTables.sql
create mode 100644 src/sql/sqlite/createTables.sql
create mode 100644 src/sql/sqlite/tmw.db
diff --git a/ChangeLog b/ChangeLog
index 468fbb1a..a72b9f91 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -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.
diff --git a/Doxyfile b/Doxyfile
index 61a6527a..5415e2c8 100644
--- a/Doxyfile
+++ b/Doxyfile
@@ -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 a16b063c..1f20d326 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 601c0bb6..d8040b8e 100644
--- a/src/account-server/dalstorage.cpp
+++ b/src/account-server/dalstorage.cpp
@@ -59,94 +59,34 @@ DALStorage::~DALStorage()
/**
* Connect to the database and initialize it if necessary.
+ *
+ * TODO: <b>Exceptionfault:</b> after connecting to the database, we have to
+ * verify if the version matches a supported version. Maybe implement a
+ * "version table" to check after connect. Raise an error with verbose
+ * informations about the discrepancy between the versions.
+ *
*/
void DALStorage::open()
{
// Do nothing if already connected.
- if (mDb->isConnected()) {
+ if (mDb->isConnected())
+ {
return;
}
using namespace dal;
- static bool dbFileShown = false;
- std::string dbFile = "tmw";
try {
// open a connection to the database.
-#if defined (MYSQL_SUPPORT) || defined (POSTGRESQL_SUPPORT)
- mDb->connect(getName(), getUser(), getPassword());
- if (!dbFileShown)
- {
- LOG_INFO("Using " << dbFile << " as Database Name.");
- dbFileShown = true;
- }
-#elif defined (SQLITE_SUPPORT)
- // create the database file name.
- dbFile += ".db";
- mDb->connect(dbFile, "", "");
- if (!dbFileShown)
- {
- LOG_INFO("SQLite uses ./" << dbFile << " as DB.");
- dbFileShown = true;
- }
-#endif
+ mDb->connect();
+
+ //TODO: check database version here
- // ensure that the required tables are created.
- //
- // strategy1: find a way to obtain the list of tables from the
- // underlying database and create the tables that are
- // missing.
- //
- // strategy2: try to create the tables and check the exceptions
- // thrown.
- //
- // comments:
- // - strategy1 is easy to achieve if we are using MysQL as
- // executing the request "show tables;" returns the list of
- // tables. However, there is not such a query for SQLite3.
- // When using SQLite3 from the interactive shell or the
- // command line, the command ".tables" returns the list of
- // tables but sqlite3_exec() does not validate this statement
- // and fails.
- // The cost of this strategy is:
- // (num. tables to create + 1) queries at most and
- // 1 at minimum.
- //
- // - strategy2 will work with probably most databases.
- // The cost of this strategy is:
- // (num. tables to create) queries.
-
- // we will stick with strategy2 for the moment as we are focusing
- // on SQLite.
-
- // FIXME: The tables should be checked/created at startup in order to
- // avoid a DbSqlQueryExecFailure assert on sqlite while registering.
- // Also, this would initialize connection to the database earlier in
- // memory.
-
- createTable(ACCOUNTS_TBL_NAME, SQL_ACCOUNTS_TABLE);
- createTable(CHARACTERS_TBL_NAME, SQL_CHARACTERS_TABLE);
- createTable(INVENTORIES_TBL_NAME, SQL_INVENTORIES_TABLE);
- createTable(GUILDS_TBL_NAME, SQL_GUILDS_TABLE);
- createTable(GUILD_MEMBERS_TBL_NAME, SQL_GUILD_MEMBERS_TABLE);
- createTable(QUESTS_TBL_NAME, SQL_QUESTS_TABLE);
- createTable(WORLD_STATES_TBL_NAME, SQL_WORLD_STATES_TABLE);
-
-
- // TODO: this is not the prefered way, but currently the complete
- // generation and maintenance of the database is a little dirty so
- // keep this as is until there is a complete cleaner solution
- const std::string idxName("tmw_accounts_username");
- const std::string colName("username");
- createIndex(idxName, ACCOUNTS_TBL_NAME, colName);
}
catch (const DbConnectionFailure& e) {
LOG_ERROR("(DALStorage::open #1) Unable to connect to the database: "
<< e.what());
}
- catch (const DbSqlQueryExecFailure& e) {
- LOG_ERROR("(DALStorage::open #2) SQL query failure: " << e.what());
- }
}
@@ -635,84 +575,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.
*/
@@ -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 099ec14a..1085a3ae 100644
--- a/src/account-server/dalstorage.hpp
+++ b/src/account-server/dalstorage.hpp
@@ -131,6 +131,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
*/
@@ -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 b773770f..0165d1bf 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 07d29b4e..2c0a9de4 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 85084dc8..f6e422fa 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 f2466033..08c56dda 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 b126c19a..fb539ece 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 b791025b..ea85d027 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 00000000..c40aa246
--- /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 00000000..3bcf665e
--- /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 00000000..0d95f384
--- /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 00000000..4ff336bb
--- /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
new file mode 100644
index 00000000..d212f5a5
Binary files /dev/null and b/src/sql/sqlite/tmw.db differ
--
cgit v1.2.3-70-g09d2