Added tables and statements to store and retrieve letters. Attachments not functional as long as items not stored as individual items.

9 files changed, 258 insertions, 10 deletions

diff --git a/ChangeLog b/ChangeLog
index 1228675d..4df81430 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,13 @@
+2008-10-17  Andreas Habel  <mail@exceptionfault.de>
+
+	* src/sql/sqlite/createTables.sql, src/sql/mysql/createTables.sql,
+	account-server/dalstorage.cpp, account-server/dalstorage.hpp, 
+	account-server/dalstoragesql.hpp, chat-server/post.cpp, 
+	chat-server/post.hpp: Added tables and statements to store and retrieve 
+	letters. Attachments not functional as long as items not stored as 
+	individual items.
+	* docs/database_specification.png: Added database specification chart
+
 2008-11-02  Philipp Sehmisch  <tmw@crushnet.org>
 
 	* data/scripts/libs/libtmw.lua, data/scripts/libs/libtmw-constants.lua:
diff --git a/docs/database_specification.png b/docs/database_specification.png
new file mode 100644
index 00000000..4fde41d1
--- /dev/null
+++ b/docs/database_specification.png
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 );