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.

1 files changed, 48 insertions, 23 deletions

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