diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/dal/mysqldataprovider.cpp | 140 | ||||
| -rw-r--r-- | src/dal/mysqldataprovider.h | 28 | ||||
| -rw-r--r-- | src/sql/mysql/createTables.sql | 82 |
3 files changed, 230 insertions, 20 deletions
diff --git a/src/dal/mysqldataprovider.cpp b/src/dal/mysqldataprovider.cpp index 811e3c06..3eda9eaa 100644 --- a/src/dal/mysqldataprovider.cpp +++ b/src/dal/mysqldataprovider.cpp @@ -127,6 +127,8 @@ void MySqlDataProvider::connect() // Save the Db Name. mDbName = dbName; + mStmt = mysql_stmt_init(mDb); + mIsConnected = true; LOG_INFO("Connection to mySQL was sucessfull."); } @@ -143,7 +145,7 @@ MySqlDataProvider::execSql(const std::string& sql, throw std::runtime_error("not connected to database"); } - LOG_DEBUG("Performing SQL query: "<<sql); + LOG_DEBUG("MySqlDataProvider::execSql Performing SQL query: "<<sql); // do something only if the query is different from the previous // or if the cache must be refreshed @@ -207,6 +209,8 @@ void MySqlDataProvider::disconnect() // handle allocated by mysql_init(). mysql_close(mDb); + mysql_stmt_close(mStmt); + // deinitialize the MySQL client library. mysql_library_end(); @@ -312,5 +316,139 @@ unsigned MySqlDataProvider::getLastId() const return (unsigned) lastId; } +bool MySqlDataProvider::prepareSql(const std::string &sql) +{ + if (!mIsConnected) + return false; + + LOG_DEBUG("MySqlDataProvider::prepareSql Preparing SQL statement: "<<sql); + + mBind.clear(); + + if (mysql_stmt_prepare(mStmt, sql.c_str(), sql.size()) != 0) + { + return false; + } + + return true; +} + +const RecordSet &MySqlDataProvider::processSql() +{ + MYSQL_BIND* paramsBind; + unsigned int i; + + if (!mIsConnected) { + throw std::runtime_error("not connected to database"); + } + + paramsBind = new MYSQL_BIND[mBind.size()]; + for (i = 0; i < mBind.size(); ++i) { + paramsBind[i].buffer_type = mBind[i]->buffer_type; + paramsBind[i].buffer = mBind[i]->buffer; + paramsBind[i].buffer_length = mBind[i]->buffer_length; + paramsBind[i].is_null = 0; + paramsBind[i].length = mBind[i]->length; + } + + if (mysql_stmt_bind_param(mStmt, paramsBind)) + { + LOG_ERROR("MySqlDataProvider::processSql Bind params failed: " << mysql_stmt_error(mStmt)); + } + + if (mysql_stmt_field_count(mStmt) > 0) { + mRecordSet.clear(); + MYSQL_BIND* resultBind; + MYSQL_RES* res; + + if (mysql_stmt_execute(mStmt)) + { + LOG_ERROR("MySqlDataProvider::processSql Execute failed: " << mysql_stmt_error(mStmt)); + } + + res = mysql_stmt_result_metadata(mStmt); + + // set the field names. + unsigned int nFields = mysql_num_fields(res); + MYSQL_FIELD* fields = mysql_fetch_fields(res); + Row fieldNames; + + resultBind = new MYSQL_BIND[mysql_num_fields(res)]; + + for (i = 0; i < mysql_num_fields(res); ++i) { + resultBind[i].buffer_type = MYSQL_TYPE_STRING; + resultBind[i].buffer = (void*) new char[255]; + resultBind[i].buffer_length = 255; + resultBind[i].is_null = new my_bool; + resultBind[i].length = new unsigned long; + resultBind[i].error = new my_bool; + } + + if (mysql_stmt_bind_result(mStmt, resultBind)) + { + LOG_ERROR("MySqlDataProvider::processSql Bind result failed: " << mysql_stmt_error(mStmt)); + } + + for (i = 0; i < nFields; ++i) { + fieldNames.push_back(fields[i].name); + } + mRecordSet.setColumnHeaders(fieldNames); + + // store the result of the query. + if (mysql_stmt_store_result(mStmt)) { + throw DbSqlQueryExecFailure(mysql_stmt_error(mStmt)); + } + + // populate the RecordSet. + while (!mysql_stmt_fetch(mStmt)) { + Row r; + + for (unsigned int i = 0; i < nFields; ++i) { + r.push_back(static_cast<char *>(resultBind[i].buffer)); + } + + mRecordSet.add(r); + } + + delete[] resultBind; + } + else + { + if (mysql_stmt_execute(mStmt)) + { + LOG_ERROR("MySqlDataProvider::processSql Execute failed: " << mysql_stmt_error(mStmt)); + } + } + + // free memory + delete[] paramsBind; + mysql_stmt_free_result(mStmt); + + return mRecordSet; +} + +void MySqlDataProvider::bindValue(int place, const std::string &value) +{ + unsigned long* size = new unsigned long; + *size = value.size(); + MYSQL_BIND* bind = new MYSQL_BIND; + bind->buffer_type= MYSQL_TYPE_STRING; + bind->buffer= (void*) value.c_str(); + bind->buffer_length= value.size(); + bind->length = size; + + //FIXME : Isn't taking care of the place param + mBind.push_back(bind); +} + +void MySqlDataProvider::bindValue(int place, int value) +{ + MYSQL_BIND* bind = new MYSQL_BIND; + bind->buffer_type= MYSQL_TYPE_LONG; + bind->buffer= &value; + + //FIXME : Isn't taking care of the place param + mBind.push_back(bind); +} } // namespace dal diff --git a/src/dal/mysqldataprovider.h b/src/dal/mysqldataprovider.h index 3ba87cd0..8efcc0dd 100644 --- a/src/dal/mysqldataprovider.h +++ b/src/dal/mysqldataprovider.h @@ -144,6 +144,32 @@ class MySqlDataProvider: public DataProvider */ unsigned getLastId() const; + /** + * Prepare SQL statement + */ + bool prepareSql(const std::string &sql); + + /** + * Process SQL statement + * SQL statement needs to be prepared and parameters binded before + * calling this function + */ + const RecordSet& processSql() ; + + /** + * Bind Value (String) + * @param place - which parameter to bind to + * @param value - the string to bind + */ + void bindValue(int place, const std::string &value); + + /** + * Bind Value (Integer) + * @param place - which parameter to bind to + * @param value - the integer to bind + */ + void bindValue(int place, int value); + private: /** defines the name of the hostname config parameter */ static const std::string CFGPARAM_MYSQL_HOST; @@ -169,6 +195,8 @@ class MySqlDataProvider: public DataProvider MYSQL *mDb; /**< the handle to the database connection */ + MYSQL_STMT *mStmt; /**< the prepared statement to process */ + std::vector<MYSQL_BIND*> mBind; }; diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql index 98fbbcca..0b75710e 100644 --- a/src/sql/mysql/createTables.sql +++ b/src/sql/mysql/createTables.sql @@ -11,6 +11,8 @@ CREATE TABLE IF NOT EXISTS `mana_accounts` ( `banned` int(10) unsigned NOT NULL, `registration` int(10) unsigned NOT NULL, `lastlogin` int(10) unsigned NOT NULL, + `authorization` text NULL, + `expiration` int(10) NULL, -- PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), @@ -84,7 +86,7 @@ CREATE TABLE IF NOT EXISTS `mana_char_status_effects` ( FOREIGN KEY (`char_id`) REFERENCES `mana_characters` (`id`) ON DELETE CASCADE -) ENGING=InnoDB +) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -161,24 +163,6 @@ CREATE TABLE IF NOT EXISTS `mana_inventories` ( DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; --- --- table: `mana_world_states` --- -CREATE TABLE IF NOT EXISTS `mana_world_states` ( - `state_name` varchar(100) NOT NULL, - `map_id` int(10) unsigned default NULL, - `value` varchar(255) default NULL, - `moddate` int(10) unsigned NOT NULL, - -- - KEY `state_name` (`state_name`) -) ENGINE=InnoDB -DEFAULT CHARSET=utf8; - -INSERT INTO mana_world_states VALUES('accountserver_startup',NULL,NULL,UNIX_TIMESTAMP()); -INSERT INTO mana_world_states VALUES('accountserver_version',NULL,NULL,UNIX_TIMESTAMP()); -INSERT INTO mana_world_states VALUES('database_version', NULL,'3', UNIX_TIMESTAMP()); - --- -- table: `mana_guilds` -- CREATE TABLE IF NOT EXISTS `mana_guilds` ( @@ -225,6 +209,15 @@ CREATE TABLE IF NOT EXISTS `mana_quests` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE IF NOT EXISTS mana_world_states +( + state_name varchar(100)NOT NULL, + map_id INTEGER NULL, + value TEXT NULL, + moddate INTEGER NOT NULL, + PRIMARY KEY (`state_name`) +); + -- -- table: `mana_auctions` -- @@ -328,6 +321,16 @@ AUTO_INCREMENT=1 ; -- table: `mana_online_list` -- +CREATE TABLE IF NOT EXISTS mana_transaction_codes +( + id int(10) unsigned NOT NULL auto_increment, + description text NOT NULL, + category text NOT NULL, + + PRIMARY KEY (id) +); + + CREATE TABLE IF NOT EXISTS `mana_online_list` ( `char_id` int(10) unsigned NOT NULL, `login_date` int(10) NOT NULL, @@ -363,3 +366,44 @@ CREATE TABLE IF NOT EXISTS `mana_transactions` ( PRIMARY KEY (`id`) ) AUTO_INCREMENT=0 ; + +-- initial world states and database version + +INSERT INTO mana_world_states VALUES('accountserver_startup',NULL,NULL, NOW()); +INSERT INTO mana_world_states VALUES('accountserver_version',NULL,NULL, NOW()); +INSERT INTO mana_world_states VALUES('database_version', NULL,'7', NOW()); + +-- all known transaction codes + +INSERT INTO mana_transaction_codes VALUES ( 1, 'Character created', 'Character' ); +INSERT INTO mana_transaction_codes VALUES ( 2, 'Character selected', 'Character' ); +INSERT INTO mana_transaction_codes VALUES ( 3, 'Character deleted', 'Character' ); +INSERT INTO mana_transaction_codes VALUES ( 4, 'Public message sent', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 5, 'Public message annouced', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 6, 'Private message sent', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 7, 'Channel joined', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 8, 'Channel kicked', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 9, 'Channel MODE', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 10, 'Channel QUIT', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 11, 'Channel LIST', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 12, 'Channel USERLIST', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 13, 'Channel TOPIC', 'Chat' ); +INSERT INTO mana_transaction_codes VALUES ( 14, 'Command BAN', 'Commands' ); +INSERT INTO mana_transaction_codes VALUES ( 15, 'Command DROP', 'Commands' ); +INSERT INTO mana_transaction_codes VALUES ( 16, 'Command ITEM', 'Commands' ); +INSERT INTO mana_transaction_codes VALUES ( 17, 'Command MONEY', 'Commands' ); +INSERT INTO mana_transaction_codes VALUES ( 18, 'Command SETGROUP', 'Commands' ); +INSERT INTO mana_transaction_codes VALUES ( 19, 'Command SPAWN', 'Commands' ); +INSERT INTO mana_transaction_codes VALUES ( 20, 'Command WARP', 'Commands' ); +INSERT INTO mana_transaction_codes VALUES ( 21, 'Item picked up', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 22, 'Item used', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 23, 'Item dropped', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 24, 'Item moved', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 25, 'Target attacked', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 26, 'ACTION Changed', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 27, 'Trade requested', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 28, 'Trade ended', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 29, 'Trade money', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 30, 'Trade items', 'Actions' ); +INSERT INTO mana_transaction_codes VALUES ( 31, 'Attribute increased', 'Character' ); +INSERT INTO mana_transaction_codes VALUES ( 32, 'Attribute decreased', 'Character' );
\ No newline at end of file |