From b39b8fdd0dd219b78cced60d746f2eb0d0cacdf7 Mon Sep 17 00:00:00 2001 From: azazel Date: Wed, 15 Dec 2010 14:08:12 +0600 Subject: 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 --- sql/updates/10654_characters_item_instance.sql | 28 ++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) create mode 100644 sql/updates/10654_characters_item_instance.sql (limited to 'sql/updates') 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; -- cgit v1.2.3