diff options
author | Andreas Habel <mail@exceptionfault.de> | 2008-09-17 11:32:45 +0000 |
---|---|---|
committer | Andreas Habel <mail@exceptionfault.de> | 2008-09-17 11:32:45 +0000 |
commit | a2af298fd993a129b657671a41f20e3975baf0ef (patch) | |
tree | 9e99436db881465af9738a6637ece7ef6b05fe5f /src/dal | |
parent | fb677eeec95d583b8b1928a907c815c95f8c4594 (diff) | |
download | manaserv-a2af298fd993a129b657671a41f20e3975baf0ef.tar.gz manaserv-a2af298fd993a129b657671a41f20e3975baf0ef.tar.xz manaserv-a2af298fd993a129b657671a41f20e3975baf0ef.zip |
* 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
Diffstat (limited to 'src/dal')
-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 |
5 files changed, 518 insertions, 41 deletions
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 */ }; |