diff options
author | Andreas Habel <mail@exceptionfault.de> | 2008-11-03 09:57:00 +0000 |
---|---|---|
committer | Andreas Habel <mail@exceptionfault.de> | 2008-11-03 09:57:00 +0000 |
commit | bf90aef80fb828f8c9253647b98a49d5dded0a3a (patch) | |
tree | 7df6051b01633a5595fd727b96c17f1dde902701 /src | |
parent | de6575cd162c41e3ced8cff7c7d3e34041da0d56 (diff) | |
download | manaserv-bf90aef80fb828f8c9253647b98a49d5dded0a3a.tar.gz manaserv-bf90aef80fb828f8c9253647b98a49d5dded0a3a.tar.bz2 manaserv-bf90aef80fb828f8c9253647b98a49d5dded0a3a.tar.xz manaserv-bf90aef80fb828f8c9253647b98a49d5dded0a3a.zip |
Added tables and statements to store and retrieve letters. Attachments not functional as long as items not stored as individual items.
Diffstat (limited to 'src')
-rw-r--r-- | src/account-server/dalstorage.cpp | 111 | ||||
-rw-r--r-- | src/account-server/dalstorage.hpp | 8 | ||||
-rw-r--r-- | src/account-server/dalstoragesql.hpp | 12 | ||||
-rw-r--r-- | src/chat-server/post.cpp | 4 | ||||
-rw-r--r-- | src/chat-server/post.hpp | 27 | ||||
-rw-r--r-- | src/sql/mysql/createTables.sql | 56 | ||||
-rw-r--r-- | src/sql/sqlite/createTables.sql | 40 |
7 files changed, 248 insertions, 10 deletions
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,11 +312,17 @@ 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 * * @param itemId The id of the item for 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 @@ -106,6 +106,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<InventoryItem> 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 ); |