From bf90aef80fb828f8c9253647b98a49d5dded0a3a Mon Sep 17 00:00:00 2001 From: Andreas Habel Date: Mon, 3 Nov 2008 09:57:00 +0000 Subject: Added tables and statements to store and retrieve letters. Attachments not functional as long as items not stored as individual items. --- src/account-server/dalstorage.cpp | 111 ++++++++++++++++++++++++++++++++++- src/account-server/dalstorage.hpp | 8 ++- src/account-server/dalstoragesql.hpp | 12 ++++ src/chat-server/post.cpp | 4 +- src/chat-server/post.hpp | 27 ++++++++- src/sql/mysql/createTables.sql | 56 +++++++++++++++++- src/sql/sqlite/createTables.sql | 40 ++++++++++++- 7 files changed, 248 insertions(+), 10 deletions(-) (limited to 'src') diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp index 35c8cd75..5d3f9577 100644 --- a/src/account-server/dalstorage.cpp +++ b/src/account-server/dalstorage.cpp @@ -1327,11 +1327,116 @@ void DALStorage::setPlayerLevel(int id, int level) void DALStorage::storeLetter(Letter *letter) { - // TODO: Store letter for user + std::ostringstream sql; + if (letter->getId() == 0) + { + // the letter was never saved before + sql << "INSERT INTO " << POST_TBL_NAME << " VALUES ( " + << "NULL, " + << letter->getSender()->getDatabaseID() << ", " + << letter->getReceiver()->getDatabaseID() << ", " + << letter->getExpiry() << ", " + << time(NULL) << ", " + << "'" << letter->getContents() << "' )"; + + mDb->execSql(sql.str()); + letter->setId(mDb->getLastId()); + + // TODO: store attachments in the database + + return; + } + else + { + // the letter has a unique id, update the record in the db + sql << "UPDATE " << POST_TBL_NAME + << " SET sender_id = '" << letter->getSender()->getDatabaseID() << "', " + << " receiver_id = '" << letter->getReceiver()->getDatabaseID() << "', " + << " letter_type = '" << letter->getType() << "', " + << " expiration_date = '" << letter->getExpiry() << "', " + << " sending_date = '" << time(NULL) << "', " + << " letter_text = '" << letter->getContents() << "' " + << " WHERE letter_id = '" << letter->getId() << "'"; + + mDb->execSql(sql.str()); + + if (mDb->getModifiedRows() == 0) + { + // this should never happen... + LOG_ERROR("(DALStorage::storePost) trying to update nonexsistant letter"); + throw "(DALStorage::storePost) trying to update nonexsistant letter"; + } + + // TODO: update attachments in the database + } } Post* DALStorage::getStoredPost(int playerId) { - // TODO: Get post for user - return 0; + Post* p = new Post(); + // specialize the string_to functor to convert + // a string to an unsigned int. + string_to< unsigned > toUint; + + std::ostringstream sql; + sql << "SELECT * FROM " << POST_TBL_NAME + << " WHERE receiver_id = " << playerId; + + dal::RecordSet const &post = mDb->execSql(sql.str()); + + if (post.isEmpty()) + { + // there is no post waiting for the character + return p; + } + + for (unsigned int i = 0; i < post.rows(); i++ ) + { + // load sender and receiver + Character *sender = getCharacter(toUint(post(i, 1)), NULL); + Character *receiver = getCharacter(toUint(post(i, 2)), NULL); + + Letter *letter = new Letter(toUint( post(0,3) ), sender, receiver); + + letter->setId( toUint(post(0, 0)) ); + letter->setExpiry( toUint(post(0, 4)) ); + letter->addText( post(0, 6) ); + + // TODO: load attachments per letter from POST_ATTACHMENTS_TBL_NAME + // needs redesign of struct ItemInventroy + + p->addLetter(letter); + } + + return p; +} + +void DALStorage::deletePost(Letter* letter) +{ + mDb->beginTransaction(); + + try + { + std::ostringstream sql; + + // first delete all attachments of the letter + // this could leave "dead" items in the item_instances table + sql << "DELETE FROM " << POST_ATTACHMENTS_TBL_NAME + << " WHERE letter_id = " << letter->getId(); + mDb->execSql(sql.str()); + + // delete the letter itself + sql.str(""); + sql << "DELETE FROM " << POST_TBL_NAME + << " WHERE letter_id = " << letter->getId(); + mDb->execSql(sql.str()); + + mDb->commitTransaction(); + letter->setId(0); + } + catch(dal::DbSqlQueryExecFailure const &e) + { + mDb->rollbackTransaction(); + LOG_ERROR("(DALStorage::deletePost) SQL query failure: " << e.what()); + } } diff --git a/src/account-server/dalstorage.hpp b/src/account-server/dalstorage.hpp index 817f2ef4..253c025a 100644 --- a/src/account-server/dalstorage.hpp +++ b/src/account-server/dalstorage.hpp @@ -312,10 +312,16 @@ class DALStorage /** * Retrieve post * - * @param playerId The id of the player requesting his post + * @param playerId The id of the character requesting his post */ Post* getStoredPost(int playerId); + /** + * Delete a letter from the database. + * @param letter The letter to delete. + */ + void deletePost(Letter* letter); + /** * Add item to auction * diff --git a/src/account-server/dalstoragesql.hpp b/src/account-server/dalstoragesql.hpp index 30b5b57e..7c4f6df5 100644 --- a/src/account-server/dalstoragesql.hpp +++ b/src/account-server/dalstoragesql.hpp @@ -105,6 +105,18 @@ static char const *QUESTS_TBL_NAME = "tmw_quests"; */ static char const *WORLD_STATES_TBL_NAME = "tmw_world_states"; +/** + * TABLE: tmw_post + * Store letters sent by characters + */ +static char const *POST_TBL_NAME = "tmw_post"; + +/** + * TABLE: tmw_post_attachments + * Store attachments per letter. + */ +static char const *POST_ATTACHMENTS_TBL_NAME = "tmw_post_attachments"; + /** * TABLE: tmw_auctions * Store items auctions. diff --git a/src/chat-server/post.cpp b/src/chat-server/post.cpp index 2d3c12e3..904ff673 100644 --- a/src/chat-server/post.cpp +++ b/src/chat-server/post.cpp @@ -26,8 +26,8 @@ #include "../account-server/character.hpp" #include "../defines.h" -Letter::Letter(int type, Character *sender, Character *receiver) - : mType(type), mSender(sender), mReceiver(receiver) +Letter::Letter(unsigned int type, Character *sender, Character *receiver) + : mId(0), mType(type), mSender(sender), mReceiver(receiver) { } diff --git a/src/chat-server/post.hpp b/src/chat-server/post.hpp index 25aefb50..f2ec6b7f 100644 --- a/src/chat-server/post.hpp +++ b/src/chat-server/post.hpp @@ -37,17 +37,39 @@ class Letter { public: /** - * Constructor + * Constructor. + * + * Before the letter is stored in the database, the unique Id of the letter + * is 0. * @param type Type of Letter - unused * @param sender Pointer to character that sent the letter * @param receiver Pointer to character that will receive the letter */ - Letter(int type, Character *sender, Character *receiver); + Letter(unsigned int type, Character *sender, Character *receiver); /** * Destructor */ ~Letter(); + + /** + * Gets the unique Id of the letter. + */ + unsigned long getId() const + { return mId; } + + /** + * Sets the unique Id of the letter used as primary key in the database. + * @param Id Unique id of the letter. + */ + void setId(unsigned long Id) + { mId = Id; } + + /** + * Gets the type of the letter. (unused) + */ + unsigned int getType(void) const + { return mType; } /** * Set the expiry @@ -97,6 +119,7 @@ public: std::vector getAttachments(); private: + unsigned int mId; unsigned int mType; unsigned long mExpiry; std::string mContents; diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql index a5172245..beb98536 100644 --- a/src/sql/mysql/createTables.sql +++ b/src/sql/mysql/createTables.sql @@ -158,7 +158,7 @@ CREATE TABLE IF NOT EXISTS `tmw_guild_members` ( ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; - + -- -- table: `tmw_quests` -- @@ -224,3 +224,57 @@ CREATE TABLE IF NOT EXISTS `tmw_auction_bids` ( DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; +-- +-- table: `tmw_post` +-- + +CREATE TABLE IF NOT EXISTS `tmw_post` ( + `letter_id` int(10) unsigned NOT NULL auto_increment, + `sender_id` int(10) unsigned NOT NULL, + `receiver_id` int(10) unsigned NOT NULL, + `letter_type` int(5) unsigned NOT NULL, + `expiration_date` int(10) unsigned NOT NULL, + `sending_date` int(10) unsigned NOT NULL, + `letter_text` TEXT NULL, + -- + PRIMARY KEY (`letter_id`), + INDEX `fk_letter_sender` (`sender_id` ASC) , + INDEX `fk_letter_receiver` (`receiver_id` ASC) , + -- + CONSTRAINT `fk_letter_sender` + FOREIGN KEY (`sender_id` ) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE, + CONSTRAINT `fk_letter_receiver` + FOREIGN KEY (`receiver_id` ) + REFERENCES `tmw_characters` (`id` ) + ON DELETE CASCADE +) ENGINE = InnoDB +DEFAULT CHARSET=utf8 +AUTO_INCREMENT=1 ; + +-- +-- table: `tmw_post_attachements` +-- + +CREATE TABLE IF NOT EXISTS `tmw_post_attachments` ( + `attachment_id` int(10) unsigned NOT NULL auto_increment, + `letter_id` int(10) unsigned NOT NULL, + `item_id` int(10) unsigned NOT NULL, + -- + PRIMARY KEY (`attachment_id`) , + INDEX `fk_attachment_letter` (`letter_id` ASC) , + INDEX `fk_attachment_item` (`item_id` ASC), + -- + CONSTRAINT `fk_attachment_letter` + FOREIGN KEY (`letter_id` ) + REFERENCES `mydb`.`tmw_post` (`letter_id` ) + ON DELETE CASCADE, + CONSTRAINT `fk_attachment_item` + FOREIGN KEY (`item_id` ) + REFERENCES `mydb`.`tmw_item_instances` (`item_id` ) + ON DELETE RESTRICT +) ENGINE = InnoDB +DEFAULT CHARSET=utf8 +AUTO_INCREMENT=1 ; + diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql index 8a3e0479..d75dc5fe 100644 --- a/src/sql/sqlite/createTables.sql +++ b/src/sql/sqlite/createTables.sql @@ -10,7 +10,8 @@ CREATE TABLE tmw_accounts lastlogin INTEGER NOT NULL ); -CREATE INDEX tmw_accounts_username ON tmw_accounts ( username ); +CREATE UNIQUE INDEX tmw_accounts_username ON tmw_accounts ( username ); +CREATE UNIQUE INDEX tmw_accounts_email ON tmw_accounts ( email ); CREATE TABLE tmw_characters @@ -77,6 +78,8 @@ CREATE TABLE tmw_inventories FOREIGN KEY (owner_id) REFERENCES tmw_characters(id) ); +CREATE INDEX tmw_inventories_owner ON tmw_inventories ( owner_id ); + CREATE TABLE tmw_guilds ( id INTEGER PRIMARY KEY, @@ -151,3 +154,38 @@ CREATE TABLE tmw_auction_bids CREATE INDEX tmw_auction_bids_auction ON tmw_auction_bids ( auction_id ); CREATE INDEX tmw_auction_bids_owner ON tmw_auction_bids ( char_id ); + + +CREATE TABLE tmw_post +( + letter_id INTEGER PRIMARY KEY, + sender_id INTEGER NOT NULL, + receiver_id INTEGER NOT NULL, + letter_type INTEGER NOT NULL, + expiration_date INTEGER NOT NULL, + sending_date INTEGER NOT NULL, + letter_text TEXT NULL, + -- + FOREIGN KEY (sender_id) REFERENCES tmw_characters(id), + FOREIGN KEY (receiver_id) REFERENCES tmw_characters(id) +); + +CREATE INDEX tmw_post_sender ON tmw_post ( sender_id ); +CREATE INDEX tmw_post_receiver ON tmw_post ( receiver_id ); + +-- +-- table: `tmw_post_attachements` +-- + +CREATE TABLE tmw_post_attachments +( + attachment_id INTEGER PRIMARY KEY, + letter_id INTEGER NOT NULL, + item_id INTEGER NOT NULL, + -- + FOREIGN KEY (letter_id) REFERENCES tmw_post(letter_id), + FOREIGN KEY (item_id) REFERENCES tmw_item_instances(item_id) +); + +CREATE INDEX tmw_post_attachments_ltr ON tmw_post_attachments ( letter_id ); +CREATE INDEX tmw_post_attachments_itm ON tmw_post_attachments ( item_id ); -- cgit v1.2.3-70-g09d2