Added table tmw_item_instances and tmw_item_attributes for mySQL and SQLite as preparation for unique item attributes. Fixed some formatting issues in mySQL Script.

3 files changed, 96 insertions, 48 deletions

diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
index beb98536..ba0cb251 100644
--- a/src/sql/mysql/createTables.sql
+++ b/src/sql/mysql/createTables.sql
@@ -15,7 +15,7 @@ CREATE TABLE IF NOT EXISTS `tmw_accounts` (
     PRIMARY KEY  (`id`),
     UNIQUE KEY `username` (`username`),
     UNIQUE KEY `email` (`email`)
-) ENGINE=InnoDB 
+) ENGINE=InnoDB
 DEFAULT CHARSET=utf8
 AUTO_INCREMENT=1 ;
 
@@ -50,17 +50,16 @@ CREATE TABLE IF NOT EXISTS `tmw_characters` (
     PRIMARY KEY (`id`),
     UNIQUE KEY `name` (`name`),
     KEY `user_id` (`user_id`),
-    FOREIGN KEY (`user_id`) 
+    FOREIGN KEY (`user_id`)
     	REFERENCES `tmw_accounts` (`id`)
-    	ON DELETE CASCADE 
+    	ON DELETE CASCADE
 ) ENGINE=InnoDB
-DEFAULT CHARSET=utf8 
+DEFAULT CHARSET=utf8
 AUTO_INCREMENT=1 ;
 
 --
--- table: `tmw_inventories`
+-- table: `tmw_char_skills`
 --
-
 CREATE TABLE IF NOT EXISTS `tmw_char_skills` (
     `char_id`      int(10)      unsigned NOT NULL,
     `skill_id`     smallint(5)  unsigned NOT NULL,
@@ -89,13 +88,45 @@ CREATE TABLE IF NOT EXISTS `tmw_items` (
     PRIMARY KEY (`id`),
     KEY `itemtype` (`itemtype`)
 ) ENGINE=InnoDB
-DEFAULT CHARSET=utf8 
+DEFAULT CHARSET=utf8
 AUTO_INCREMENT=1 ;
 
 --
--- table: `tmw_inventories`
+-- table: `tmw_item_instances`
+--
+CREATE TABLE IF NOT EXISTS `tmw_item_instances` (
+    `item_id`      int(10)      unsigned NOT NULL auto_increment,
+    `itemclass_id` int(10)      unsigned NOT NULL,
+    `amount`       tinyint(3)   unsigned NOT NULL,
+    --
+    PRIMARY KEY (`item_id`),
+    FOREIGN KEY (`itemclass_id`)
+        REFERENCES `tmw_items` (`id`)
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8
+AUTO_INCREMENT=1 ;
+
+--
+-- table: `tmw_item_attributes`
 --
+CREATE TABLE IF NOT EXISTS `tmw_item_attributes` (
+    `attribute_id`    int(10)      unsigned NOT NULL auto_increment,
+    `item_id`         int(10)      unsigned NOT NULL,
+    `attribute_class` tinyint(3)   unsigned NOT NULL,
+    `attribute_value` varchar(500)              NULL,
+    --
+    PRIMARY KEY (`attribute_id`),
+    FOREIGN KEY (`item_id`)
+        REFERENCES `tmw_item_instances` (`item_id`)
+        ON DELETE CASCADE
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8
+AUTO_INCREMENT=1 ;
 
+--
+-- table: `tmw_inventories`
+-- todo: remove class_id and amount and reference on tmw_item_instances
+--
 CREATE TABLE IF NOT EXISTS `tmw_inventories` (
     `id`           int(10)      unsigned NOT NULL auto_increment,
     `owner_id`     int(10)      unsigned NOT NULL,
@@ -107,15 +138,14 @@ CREATE TABLE IF NOT EXISTS `tmw_inventories` (
     UNIQUE KEY `owner_id` (`owner_id`, `slot`),
     FOREIGN KEY (`owner_id`)
     	REFERENCES `tmw_characters` (`id`)
-    	ON DELETE CASCADE 
+    	ON DELETE CASCADE
 ) ENGINE=InnoDB
-DEFAULT CHARSET=utf8 
+DEFAULT CHARSET=utf8
 AUTO_INCREMENT=1 ;
 
 --
 -- table: `tmw_world_states`
 --
-
 CREATE TABLE IF NOT EXISTS `tmw_world_states` (
     `state_name`   varchar(100)          NOT NULL,
     `map_id`       int(10)      unsigned default NULL,
@@ -129,7 +159,6 @@ DEFAULT CHARSET=utf8;
 --
 -- table: `tmw_guilds`
 --
-
 CREATE TABLE IF NOT EXISTS `tmw_guilds` (
     `id`           int(10)      unsigned NOT NULL auto_increment,
     `name`         varchar(35)           NOT NULL,
@@ -143,7 +172,6 @@ AUTO_INCREMENT=1 ;
 --
 -- table: `tmw_guild_members`
 --
-
 CREATE TABLE IF NOT EXISTS `tmw_guild_members` (
 	`guild_id`     int(10)      unsigned NOT NULL,
 	`member_id`    int(10)      unsigned NOT NULL,
@@ -241,13 +269,11 @@ CREATE TABLE IF NOT EXISTS `tmw_post` (
 	INDEX `fk_letter_sender` (`sender_id` ASC) ,
 	INDEX `fk_letter_receiver` (`receiver_id` ASC) ,
 	--
-	CONSTRAINT `fk_letter_sender`
-		FOREIGN KEY (`sender_id` )
+	FOREIGN KEY (`sender_id` )
 		REFERENCES `tmw_characters` (`id`)
 		ON DELETE CASCADE,
-	CONSTRAINT `fk_letter_receiver`
-		FOREIGN KEY (`receiver_id` )
-		REFERENCES `tmw_characters` (`id` )
+    FOREIGN KEY (`receiver_id` )
+		REFERENCES `tmw_characters` (`id`)
 		ON DELETE CASCADE
 ) ENGINE = InnoDB
 DEFAULT CHARSET=utf8
@@ -256,7 +282,6 @@ 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,
@@ -266,13 +291,11 @@ CREATE TABLE IF NOT EXISTS `tmw_post_attachments` (
 	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` )
+	FOREIGN KEY (`letter_id` )
+		REFERENCES `tmw_post` (`letter_id`)
 		ON DELETE CASCADE,
-	CONSTRAINT `fk_attachment_item`
-		FOREIGN KEY (`item_id` )
-		REFERENCES `mydb`.`tmw_item_instances` (`item_id` )
+	FOREIGN KEY (`item_id` )
+		REFERENCES `tmw_item_instances` (`item_id`)
 		ON DELETE RESTRICT
 ) ENGINE = InnoDB
 DEFAULT CHARSET=utf8
diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql
index d75dc5fe..253750a8 100644
--- a/src/sql/sqlite/createTables.sql
+++ b/src/sql/sqlite/createTables.sql
@@ -1,20 +1,20 @@
-CREATE TABLE tmw_accounts 
-( 
-   id           INTEGER     PRIMARY KEY, 
-   username     TEXT        NOT NULL UNIQUE, 
-   password     TEXT        NOT NULL, 
-   email        TEXT        NOT NULL, 
-   level        INTEGER     NOT NULL, 
-   banned       INTEGER     NOT NULL, 
-   registration INTEGER     NOT NULL, 
-   lastlogin    INTEGER     NOT NULL  
+CREATE TABLE tmw_accounts
+(
+   id           INTEGER     PRIMARY KEY,
+   username     TEXT        NOT NULL UNIQUE,
+   password     TEXT        NOT NULL,
+   email        TEXT        NOT NULL,
+   level        INTEGER     NOT NULL,
+   banned       INTEGER     NOT NULL,
+   registration INTEGER     NOT NULL,
+   lastlogin    INTEGER     NOT NULL
 );
 
 CREATE UNIQUE INDEX tmw_accounts_username ON tmw_accounts ( username );
 CREATE UNIQUE INDEX tmw_accounts_email    ON tmw_accounts ( email );
 
 
-CREATE TABLE tmw_characters 
+CREATE TABLE tmw_characters
 (
    id           INTEGER     PRIMARY KEY,
    user_id      INTEGER     NOT NULL,
@@ -42,7 +42,7 @@ CREATE TABLE tmw_characters
 CREATE INDEX tmw_characters_user ON tmw_characters ( user_id );
 CREATE UNIQUE INDEX tmw_characters_name ON tmw_characters ( name );
 
-CREATE TABLE tmw_char_skills 
+CREATE TABLE tmw_char_skills
 (
     char_id     INTEGER     NOT NULL,
     skill_id    INTEGER     NOT NULL,
@@ -53,7 +53,7 @@ CREATE TABLE tmw_char_skills
 
 CREATE INDEX tmw_char_skills_char ON tmw_char_skills ( char_id );
 
-CREATE TABLE tmw_items 
+CREATE TABLE tmw_items
 (
     id           INTEGER    PRIMARY KEY,
     name         TEXT       NOT NULL,
@@ -67,7 +67,31 @@ CREATE TABLE tmw_items
 
 CREATE INDEX tmw_items_type ON tmw_items (itemtype);
 
-CREATE TABLE tmw_inventories 
+CREATE TABLE tmw_item_instances
+(
+    item_id       INTEGER    PRIMARY KEY,
+    itemclass_id  INTEGER    NOT NULL,
+    amount        INTEGER    NOT NULL,
+    --
+    FOREIGN KEY (itemclass_id) REFERENCES tmw_items(id)
+);
+
+CREATE INDEX tmw_item_instances_typ ON tmw_item_instances ( itemclass_id );
+
+CREATE TABLE tmw_item_attributes
+(
+    attribute_id    INTEGER    PRIMARY KEY,
+    item_id         INTEGER    NOT NULL,
+    attribute_class INTEGER    NOT NULL,
+    attribute_value TEXT,
+    --
+    FOREIGN KEY (item_id) REFERENCES tmw_item_instances(item_id)
+);
+
+CREATE INDEX tmw_item_attributes_item ON tmw_item_attributes ( item_id );
+
+-- todo: remove class_id and amount and reference on tmw_item_instances
+CREATE TABLE tmw_inventories
 (
    id           INTEGER     PRIMARY KEY,
    owner_id     INTEGER     NOT NULL,
@@ -80,13 +104,13 @@ CREATE TABLE tmw_inventories
 
 CREATE INDEX tmw_inventories_owner ON tmw_inventories ( owner_id );
 
-CREATE TABLE tmw_guilds 
+CREATE TABLE tmw_guilds
 (
    id           INTEGER     PRIMARY KEY,
    name 		TEXT        NOT NULL UNIQUE
 );
 
-CREATE TABLE tmw_guild_members 
+CREATE TABLE tmw_guild_members
 (
    guild_id     INTEGER     NOT NULL,
    member_id    INTEGER     NOT NULL,
@@ -99,7 +123,7 @@ CREATE TABLE tmw_guild_members
 CREATE INDEX tmw_guild_members_g ON tmw_guild_members ( guild_id );
 CREATE INDEX tmw_guild_members_m ON tmw_guild_members ( member_id );
 
-CREATE TABLE tmw_quests 
+CREATE TABLE tmw_quests
 (
    owner_id     INTEGER     NOT NULL,
    name         TEXT        NOT NULL,
@@ -108,12 +132,12 @@ CREATE TABLE tmw_quests
    FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)
 );
 
-CREATE TABLE tmw_world_states 
-( 
-   state_name   TEXT        PRIMARY KEY, 
-   map_id       INTEGER     NULL, 
-   value        TEXT        NULL, 
-   moddate      INTEGER     NOT NULL 
+CREATE TABLE tmw_world_states
+(
+   state_name   TEXT        PRIMARY KEY,
+   map_id       INTEGER     NULL,
+   value        TEXT        NULL,
+   moddate      INTEGER     NOT NULL
 );
 
 INSERT INTO "tmw_world_states" VALUES('accountserver_startup',NULL,NULL,1221633910);
@@ -189,3 +213,4 @@ CREATE TABLE tmw_post_attachments
 
 CREATE INDEX tmw_post_attachments_ltr ON tmw_post_attachments ( letter_id );
 CREATE INDEX tmw_post_attachments_itm ON tmw_post_attachments ( item_id );
+
diff --git a/src/sql/sqlite/tmw.db b/src/sql/sqlite/tmw.db
index 020e2296..93ae76d5 100644
--- a/src/sql/sqlite/tmw.db
+++ b/src/sql/sqlite/tmw.db