DB Schema/Characters: removed columns auctionhouse.item_template, character_inventory.item_template, guild_bank_item.item_entry and mail_items.item_template and created item_instance.itemEntry column instead of them.

As a side effect moved related queries to prepared statements.
As one more side effect fixed not shown and lost mail items in returned expired mail (patch by Quriq14).

--HG--
branch : trunk

2 files changed, 29 insertions, 4 deletions

diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql
index 3753593e694..11acfcc0ef7 100644
--- a/sql/base/characters_database.sql
+++ b/sql/base/characters_database.sql
@@ -184,7 +184,6 @@ CREATE TABLE `auctionhouse` (
   `id` int(11) unsigned NOT NULL default '0',
   `auctioneerguid` int(11) unsigned NOT NULL default '0',
   `itemguid` int(11) unsigned NOT NULL default '0',
-  `item_template` int(11) unsigned NOT NULL default '0' COMMENT 'Item Identifier',
   `itemowner` int(11) unsigned NOT NULL default '0',
   `buyoutprice` int(11) NOT NULL default '0',
   `time` bigint(40) NOT NULL default '0',
@@ -762,7 +761,6 @@ CREATE TABLE `character_inventory` (
   `bag` int(11) unsigned NOT NULL default '0',
   `slot` tinyint(3) unsigned NOT NULL default '0',
   `item` int(11) unsigned NOT NULL default '0' COMMENT 'Item Global Unique Identifier',
-  `item_template` int(11) unsigned NOT NULL default '0' COMMENT 'Item Identifier',
   PRIMARY KEY  (`item`),
   KEY `idx_guid` (`guid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Player System';
@@ -1557,7 +1555,6 @@ CREATE TABLE `guild_bank_item` (
   `TabId` tinyint(1) unsigned NOT NULL default '0',
   `SlotId` tinyint(3) unsigned NOT NULL default '0',
   `item_guid` int(11) unsigned NOT NULL default '0',
-  `item_entry` int(11) unsigned NOT NULL default '0',
   PRIMARY KEY  (`guildid`,`tabid`,`slotid`),
   KEY `guildid_key` (`guildid`),    
   INDEX `Idx_item_guid`(`item_guid`)
@@ -1790,6 +1787,7 @@ DROP TABLE IF EXISTS `item_instance`;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `item_instance` (
   `guid` int(11) unsigned NOT NULL default '0',
+  `itemEntry` mediumint(8) unsigned NOT NULL default '0',
   `owner_guid` int(11) unsigned NOT NULL default '0',
   `creatorGuid` int(10) unsigned NOT NULL default '0',
   `giftCreatorGuid` int(10) unsigned NOT NULL default '0',
@@ -1938,7 +1936,6 @@ DROP TABLE IF EXISTS `mail_items`;
 CREATE TABLE `mail_items` (
   `mail_id` int(11) NOT NULL default '0',
   `item_guid` int(11) NOT NULL default '0',
-  `item_template` int(11) NOT NULL default '0',
   `receiver` int(11) unsigned NOT NULL default '0' COMMENT 'Character Global Unique Identifier',
   PRIMARY KEY  (`mail_id`,`item_guid`),
   KEY `idx_receiver` (`receiver`)
diff --git a/sql/updates/10654_characters_item_instance.sql b/sql/updates/10654_characters_item_instance.sql
new file mode 100644
index 00000000000..297250201cd
--- /dev/null
+++ b/sql/updates/10654_characters_item_instance.sql
@@ -0,0 +1,28 @@
+-- Add new column to item_instance table
+ALTER TABLE `item_instance` ADD COLUMN `itemEntry` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `guid`;
+
+-- Set values for new column from corresponding columns in other tables
+UPDATE item_instance ii, auctionhouse ah
+SET ii.itemEntry = ah.item_template
+WHERE ii.guid = ah.itemguid;
+
+UPDATE item_instance ii, character_inventory ci
+SET ii.itemEntry = ci.item_template
+WHERE ii.guid = ci.item;
+
+UPDATE item_instance ii, guild_bank_item gbi
+SET ii.itemEntry = gbi.item_entry
+WHERE ii.guid = gbi.item_guid;
+
+UPDATE item_instance ii, mail_items mi
+SET ii.itemEntry = mi.item_template
+WHERE ii.guid = mi.item_guid;
+
+-- Remove unnecessary columns
+ALTER TABLE `auctionhouse` DROP COLUMN `item_template`;
+ALTER TABLE `character_inventory` DROP COLUMN `item_template`;
+ALTER TABLE `guild_bank_item` DROP COLUMN `item_entry`;
+ALTER TABLE `mail_items` DROP COLUMN `item_template`;
+
+-- Delete orphan records (use at your own risk)
+-- DELETE FROM item_instance WHERE itemEntry = 0;