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

2 files changed, 94 insertions, 2 deletions

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 );