e2c4kkR8

1-- Preparando todo
2DROP TABLE beta_characters.item_instance;
3DROP TABLE beta_characters.auctionhouse;
4DROP TABLE beta_characters.character_inventory;
5DROP TABLE beta_characters.guild_bank_item;
6DROP TABLE beta_characters.mail_items;
7
8CREATE TABLE beta_characters.item_instance SELECT * FROM characters.item_instance;
9CREATE TABLE beta_characters.auctionhouse SELECT * FROM characters.auctionhouse;
10CREATE TABLE beta_characters.character_inventory SELECT * FROM characters.character_inventory;
11CREATE TABLE beta_characters.guild_bank_item SELECT * FROM characters.guild_bank_item;
12CREATE TABLE beta_characters.mail_items SELECT * FROM characters.mail_items;
13
14
15-- Transformando
16
17ALTER TABLE beta_characters.`item_instance` ADD COLUMN `text` LONGTEXT AFTER `data`;
18
19
20UPDATE beta_characters.item_instance SET DATA = REPLACE(DATA,'  ',' ');
21UPDATE beta_characters.item_instance SET DATA = CONCAT(TRIM(DATA),' ');
22
23UPDATE beta_characters.`item_instance` SET `data` = CONCAT(
24    SUBSTRING_INDEX(`data`, ' ', 59 + 1), ' ',
25    SUBSTRING_INDEX(`data`, ' ', -3 -1), '0 ')
26WHERE LENGTH(SUBSTRING_INDEX(DATA, ' ', 64)) < LENGTH(DATA) AND LENGTH(SUBSTRING_INDEX(DATA, ' ', 64+1)) >= LENGTH(DATA);
27
28UPDATE beta_characters.item_instance SET DATA = REPLACE(DATA,'  ',' ');
29UPDATE beta_characters.item_instance SET DATA = CONCAT(TRIM(DATA),' ');
30
31
32
33-- Add new fields
34ALTER TABLE beta_characters.`item_instance`
35 ADD `creatorGuid` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `owner_guid`,
36 ADD `giftCreatorGuid` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `creatorGuid`,
37 ADD `count` INT(10) UNSIGNED NOT NULL DEFAULT '1' AFTER `giftCreatorGuid`,
38 ADD `duration` INT(10) UNSIGNED NOT NULL AFTER `count`,
39 ADD `charges` TEXT NOT NULL AFTER `duration`,
40 ADD `flags` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `charges`,
41 ADD `enchantments` TEXT NOT NULL AFTER `flags`,
42 ADD `randomPropertyId` INT(11) NOT NULL DEFAULT '0' AFTER `enchantments`,
43 ADD `durability` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `randomPropertyId`,
44 ADD `playedTime` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `durability`;
45
46-- Temporarily change delimiter to prevent SQL syntax errors
47DELIMITER ||
48
49-- Function to convert ints from unsigned to signed
50DROP FUNCTION IF EXISTS `uint32toint32`||
51CREATE FUNCTION `uint32toint32`(input INT(10) UNSIGNED) RETURNS INT(11) SIGNED DETERMINISTIC
52BEGIN
53  RETURN input;
54END||
55
56-- Restore original delimiter
57DELIMITER ;
58
59-- Move data to new fields
60UPDATE beta_characters.`item_instance` SET
61`creatorGuid` = SUBSTRING(`data`,
62LENGTH(SUBSTRING_INDEX(`data`,' ',10))+2,
63LENGTH(SUBSTRING_INDEX(`data`,' ',10+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',10))-1),
64
65`giftCreatorGuid` = SUBSTRING(`data`,
66LENGTH(SUBSTRING_INDEX(`data`,' ',12))+2,
67LENGTH(SUBSTRING_INDEX(`data`,' ',12+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',12))-1),
68
69`count` = SUBSTRING(`data`,
70LENGTH(SUBSTRING_INDEX(`data`,' ',14))+2,
71LENGTH(SUBSTRING_INDEX(`data`,' ',14+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',14))-1),
72
73`duration` = SUBSTRING(`data`,
74LENGTH(SUBSTRING_INDEX(`data`,' ',15))+2,
75LENGTH(SUBSTRING_INDEX(`data`,' ',15+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',15))-1),
76
77`charges` = CONCAT_WS(' ',
78uint32toint32(SUBSTRING(`data`,
79LENGTH(SUBSTRING_INDEX(`data`,' ',16))+2,
80LENGTH(SUBSTRING_INDEX(`data`,' ',16+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',16))-1)),
81uint32toint32(SUBSTRING(`data`,
82LENGTH(SUBSTRING_INDEX(`data`,' ',17))+2,
83LENGTH(SUBSTRING_INDEX(`data`,' ',17+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',17))-1)),
84uint32toint32(SUBSTRING(`data`,
85LENGTH(SUBSTRING_INDEX(`data`,' ',18))+2,
86LENGTH(SUBSTRING_INDEX(`data`,' ',18+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',18))-1)),
87uint32toint32(SUBSTRING(`data`,
88LENGTH(SUBSTRING_INDEX(`data`,' ',19))+2,
89LENGTH(SUBSTRING_INDEX(`data`,' ',19+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',19))-1)),
90uint32toint32(SUBSTRING(`data`,
91LENGTH(SUBSTRING_INDEX(`data`,' ',20))+2,
92LENGTH(SUBSTRING_INDEX(`data`,' ',20+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',20))-1))),
93
94`flags` = SUBSTRING(`data`,
95LENGTH(SUBSTRING_INDEX(`data`,' ',21))+2,
96LENGTH(SUBSTRING_INDEX(`data`,' ',21+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',21))-1),
97
98`enchantments` = SUBSTRING(`data`,
99LENGTH(SUBSTRING_INDEX(`data`,' ',22))+2,
100LENGTH(SUBSTRING_INDEX(`data`,' ',57+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',22))-1),
101
102`randomPropertyId` = uint32toint32(SUBSTRING(`data`,
103LENGTH(SUBSTRING_INDEX(`data`,' ',59))+2,
104LENGTH(SUBSTRING_INDEX(`data`,' ',59+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',59))-1)),
105
106`durability` = SUBSTRING(`data`,
107LENGTH(SUBSTRING_INDEX(`data`,' ',60))+2,
108LENGTH(SUBSTRING_INDEX(`data`,' ',60+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',60))-1),
109
110`playedTime` = SUBSTRING(`data`,
111LENGTH(SUBSTRING_INDEX(`data`,' ',62))+2,
112LENGTH(SUBSTRING_INDEX(`data`,' ',62+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',62))-1);
113
114-- Drop function
115DROP FUNCTION IF EXISTS `uint32toint32`;
116
117-- Fix heroic item flag
118UPDATE beta_characters.`item_instance` SET `flags`=`flags`&~0x8 WHERE
119SUBSTRING(`data`,
120LENGTH(SUBSTRING_INDEX(`data`,' ',3))+2,
121LENGTH(SUBSTRING_INDEX(`data`,' ',3+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',3))-1)
122NOT IN (5043,5044,17302,17305,17308,21831);
123
124-- Drop old field
125ALTER TABLE beta_characters.`item_instance` DROP `data`;
126
127
128SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
129
130UPDATE beta_characters.`item_instance` SET `flags` = (`flags` & @allowedFlags);
131
132
133
134ALTER TABLE beta_characters.`item_instance`
135ROW_FORMAT=DEFAULT,
136CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
137CHANGE `owner_guid` `owner_guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL,
138CHANGE `charges` `charges` TINYTEXT,
139CHANGE `duration` `duration` INT(10) DEFAULT '0' NOT NULL,
140CHANGE `flags` `flags` MEDIUMINT(8) UNSIGNED DEFAULT '0' NOT NULL,
141CHANGE `randomPropertyId` `randomPropertyId` SMALLINT(5) DEFAULT '0' NOT NULL,
142CHANGE `durability` `durability` SMALLINT(5) UNSIGNED DEFAULT '0' NOT NULL,
143CHANGE `text` `text` TEXT NULL;
144
145
146-- Add new column to item_instance table
147ALTER TABLE beta_characters.`item_instance` ADD COLUMN `itemEntry` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' AFTER `guid`;
148
149-- Set values for new column from corresponding columns in other tables
150
151UPDATE beta_characters.item_instance ii, beta_characters.auctionhouse ah
152SET ii.itemEntry = ah.item_template
153WHERE ii.guid = ah.itemguid;
154
155UPDATE beta_characters.item_instance ii, beta_characters.character_inventory ci
156SET ii.itemEntry = ci.item_template
157WHERE ii.guid = ci.item;
158
159UPDATE beta_characters.item_instance ii, beta_characters.guild_bank_item gbi
160SET ii.itemEntry = gbi.item_entry
161WHERE ii.guid = gbi.item_guid;
162
163UPDATE beta_characters.item_instance ii, beta_characters.mail_items mi
164SET ii.itemEntry = mi.item_template
165WHERE ii.guid = mi.item_guid;
166
167-- Remove unnecessary columns
168ALTER TABLE beta_characters.`auctionhouse` DROP COLUMN `item_template`;
169ALTER TABLE beta_characters.`character_inventory` DROP COLUMN `item_template`;
170ALTER TABLE beta_characters.`guild_bank_item` DROP COLUMN `item_entry`;
171ALTER TABLE beta_characters.`mail_items` DROP COLUMN `item_template`;