iCuJP4pL

1-- AUTH DATABASE
2USE convert_tc_auth;
3-- table name: columns left to default
4
5-- account: email, online, os, recruiter
6DELETE FROM account;
7INSERT INTO account (id, username, sha_pass_hash, sessionkey, v, s, joindate, last_ip, failed_logins, locked, last_login, expansion, mutetime, locale)
8SELECT               id, username, sha_pass_hash, sessionkey, v, s, joindate, last_ip, failed_logins, locked, last_login, expansion, mutetime, locale FROM convert_mg_auth.account;
9
10-- account_access: RealmID
11DELETE FROM account_access;
12INSERT INTO account_access (id, gmlevel) 
13SELECT                      id, gmlevel FROM convert_mg_auth.account;
14
15-- account_banned
16DELETE FROM account_banned;
17INSERT INTO account_banned SELECT * FROM convert_mg_auth.account_banned;
18
19-- ip_banned
20DELETE FROM ip_banned;
21INSERT INTO ip_banned SELECT * FROM convert_mg_auth.ip_banned;
22
23-- logs
24
25-- realmcharacters
26DELETE FROM realmcharacters;
27INSERT INTO realmcharacters SELECT * FROM convert_mg_auth.realmcharacters;
28
29-- realmlist
30DELETE FROM realmlist;
31INSERT INTO realmlist SELECT * FROM convert_mg_auth.realmlist;
32
33USE convert_tc_characters;
34
35-- account_data
36DELETE FROM account_data;
37DELETE FROM account_data;
38INSERT INTO account_data SELECT * FROM convert_mg_characters.account_data;
39
40-- account_instance_times
41
42-- account_tutorial
43DELETE FROM account_tutorial;
44INSERT INTO account_tutorial SELECT * FROM convert_mg_characters.character_tutorial;
45
46-- addons
47
48-- auctionhouse
49DELETE FROM auctionhouse;
50INSERT INTO auctionhouse (id, auctioneerguid, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit)
51SELECT                    id, houseid,        itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit FROM convert_mg_characters.auction;
52
53-- bugreport
54-- corpse
55-- creature_respawn
56-- game_event_condition_save
57-- game_event_save
58-- gameobject_respawn
59-- gm_subsurveys
60-- gm_surveys
61-- gm_tickets
62-- group_instance
63-- group_member
64-- groups
65
66-- guild
67DELETE FROM guild;
68INSERT INTO guild SELECT * FROM convert_mg_characters.guild;
69
70-- guild_bank_eventlog
71DELETE FROM guild_bank_eventlog;
72INSERT INTO guild_bank_eventlog SELECT * FROM convert_mg_characters.guild_bank_eventlog;
73
74-- guild_bank_item
75DELETE FROM guild_bank_item;
76INSERT INTO guild_bank_item SELECT guildid, TabId, SlotId, item_guid FROM convert_mg_characters.guild_bank_item;
77
78-- guild_bank_right
79DELETE FROM guild_bank_right;
80INSERT INTO guild_bank_right SELECT * FROM convert_mg_characters.guild_bank_right;
81
82-- guild_bank_tab
83DELETE FROM guild_bank_tab;
84INSERT INTO guild_bank_tab SELECT * FROM convert_mg_characters.guild_bank_tab;
85
86-- guild_eventlog
87DELETE FROM guild_eventlog;
88INSERT INTO guild_eventlog SELECT * FROM convert_mg_characters.guild_eventlog;
89
90-- guild_member
91DELETE FROM guild_member;
92INSERT INTO guild_member SELECT * FROM convert_mg_characters.guild_member;
93
94-- guild_rank
95DELETE FROM guild_rank;
96INSERT INTO guild_rank SELECT * FROM convert_mg_characters.guild_rank;
97
98-- channels
99
100-- character_account_data
101DELETE FROM character_account_data;
102INSERT INTO character_account_data SELECT * FROM convert_mg_characters.character_account_data;
103
104-- character_action
105DELETE FROM character_action;
106INSERT INTO character_action SELECT * FROM convert_mg_characters.character_action;
107
108-- character_achievement
109DELETE FROM character_achievement;
110INSERT INTO character_achievement SELECT * FROM convert_mg_characters.character_achievement;
111
112-- character_achievement_progress
113DELETE FROM character_achievement_progress;
114INSERT INTO character_achievement_progress SELECT * FROM convert_mg_characters.character_achievement_progress;
115
116-- character_arena_stats
117-- character_aura
118-- character_banned
119
120-- character_battleground_data
121DELETE FROM character_battleground_data;
122INSERT INTO character_battleground_data SELECT * FROM convert_mg_characters.character_battleground_data;
123
124-- character_battleground_random
125DELETE FROM character_battleground_random;
126INSERT INTO character_battleground_random SELECT * FROM convert_mg_characters.character_battleground_random;
127
128-- character_declinedname
129DELETE FROM character_declinedname;
130INSERT INTO character_declinedname SELECT * FROM convert_mg_characters.character_declinedname;
131
132-- character_equipmentsets
133DELETE FROM character_equipmentsets;
134INSERT INTO character_equipmentsets SELECT * FROM convert_mg_characters.character_equipmentsets;
135
136-- character_gifts
137DELETE FROM character_gifts;
138INSERT INTO character_gifts SELECT * FROM convert_mg_characters.character_gifts;
139
140-- character_glyphs
141DELETE FROM character_glyphs;
142INSERT INTO character_glyphs (guid,spec) SELECT DISTINCT guid,spec FROM convert_mg_characters.character_glyphs;
143UPDATE character_glyphs a SET glyph1 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '0');
144UPDATE character_glyphs a SET glyph2 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '1');
145UPDATE character_glyphs a SET glyph3 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '2');
146UPDATE character_glyphs a SET glyph4 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '3');
147UPDATE character_glyphs a SET glyph5 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '4');
148UPDATE character_glyphs a SET glyph6 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '5');
149
150-- character_homebind
151DELETE FROM character_homebind;
152INSERT INTO character_homebind SELECT * FROM convert_mg_characters.character_homebind;
153
154-- character_instance
155
156-- character_inventory
157DELETE FROM character_inventory;
158INSERT INTO character_inventory SELECT guid, bag, slot, item FROM convert_mg_characters.character_inventory;
159
160-- character_pet
161DELETE FROM character_pet;
162INSERT INTO character_pet SELECT * FROM convert_mg_characters.character_pet;
163
164-- character_pet_declinedname
165DELETE FROM character_pet_declinedname;
166INSERT INTO character_pet_declinedname SELECT * FROM convert_mg_characters.character_pet_declinedname;
167
168-- character_queststatus
169DELETE FROM character_queststatus;
170INSERT INTO character_queststatus 
171SELECT guid, quest, status, explored, timer, mobcount1, mobcount2, mobcount3, mobcount4, itemcount1, itemcount2, itemcount3, itemcount4, 1 FROM convert_mg_characters.character_queststatus;
172
173-- character_queststatus_daily
174DELETE FROM character_queststatus_daily;
175INSERT INTO character_queststatus_daily (guid, quest) SELECT * FROM convert_mg_characters.character_queststatus_daily;
176
177-- character_queststatus_rewarded
178DELETE FROM character_queststatus_rewarded;
179INSERT INTO character_queststatus_rewarded SELECT c.guid, c.quest FROM convert_mg_characters.character_queststatus c WHERE c.status = 1;
180
181-- character_queststatus_seasonal
182-- character_queststatus_weekly
183
184-- character_reputation
185DELETE FROM character_reputation;
186INSERT INTO character_reputation SELECT * FROM convert_mg_characters.character_reputation;
187
188-- character_skills
189DELETE FROM character_skills;
190INSERT INTO character_skills SELECT * FROM convert_mg_characters.character_skills;
191
192-- character_social
193DELETE FROM character_social;
194INSERT INTO character_social SELECT * FROM convert_mg_characters.character_social;
195
196-- character_spell
197DELETE FROM character_spell;
198INSERT INTO character_spell SELECT * FROM convert_mg_characters.character_spell;
199
200-- character_spell_cooldown
201DELETE FROM character_spell_cooldown;
202INSERT INTO character_spell_cooldown SELECT * FROM convert_mg_characters.character_spell_cooldown;
203
204-- character_stats
205-- character_talent
206
207-- characters
208DELETE FROM characters;
209INSERT INTO characters (guid, account, name, race, class, gender, level, xp, money, playerBytes, playerBytes2, playerFlags, position_x, position_y, position_z, map, instance_id, instance_mode_mask, orientation, taximask, online, cinematic, totaltime, leveltime, logout_time, is_logout_resting, rest_bonus, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login, 
210zone, death_expire_time, taxi_path, arenaPoints, totalHonorPoints, todayHonorPoints, yesterdayHonorPoints, totalKills, todayKills, yesterdayKills, chosenTitle, knownCurrencies, watchedFaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, latency, speccount, activespec, exploredZones, equipmentCache, ammoId, knownTitles, actionBars, grantableLevels, deleteInfos_Account, deleteInfos_Name, deleteDate)
211SELECT                  guid, account, name, race, class, gender, level, xp, money, playerBytes, playerBytes2, playerFlags, position_x, position_y, position_z, map, 0,           dungeon_difficulty, orientation, taximask, online, cinematic, totaltime, leveltime, logout_time, is_logout_resting, rest_bonus, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login,
212zone, death_expire_time, taxi_path, arenaPoints, totalHonorPoints, todayHonorPoints, yesterdayHonorPoints, totalKills, todayKills, yesterdayKills, chosenTitle, knownCurrencies, watchedFaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, 0, speccount, activespec, exploredZones, equipmentCache, ammoId, knownTitles, actionBars, grantableLevels, deleteInfos_Account, deleteInfos_Name, deleteDate FROM convert_mg_characters.characters;
213
214-- instance
215-- instance_reset
216
217-- item_instance
218-- DROP Trinity table and use mangos one
219DROP TABLE IF EXISTS `item_instance`;
220CREATE TABLE `item_instance` (
221  `guid` int(11) unsigned NOT NULL default '0',
222  `owner_guid` int(11) unsigned NOT NULL default '0',
223  `data` longtext,
224  `text` longtext,
225  PRIMARY KEY  (`guid`),
226  KEY `idx_owner_guid` (`owner_guid`)
227) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Item System';
228
229/* Copy mangos data */
230INSERT INTO item_instance SELECT * FROM convert_mg_characters.item_instance;
231
232/* Execute 07893_characters_item_instance.sql */
233UPDATE item_instance SET data = REPLACE(data,'  ',' ');
234UPDATE item_instance SET data = CONCAT(TRIM(data),' ');
235
236UPDATE `item_instance` SET `data` = CONCAT(
237SUBSTRING_INDEX(`data`, ' ', 59 + 1), ' ',
238SUBSTRING_INDEX(`data`, ' ', -3 -1), '0 ')
239WHERE length(SUBSTRING_INDEX(data, ' ', 64)) < length(data) and length(SUBSTRING_INDEX(data, ' ', 64+1)) >= length(data);
240
241UPDATE item_instance SET data = REPLACE(data,'  ',' ');
242UPDATE item_instance SET data = CONCAT(TRIM(data),' ');
243
244/* Don't Execute 07904_characters_item_instance.sql coz mangos already has this field */
245
246/* Execute 09090_characters_item_instance.sql */
247-- Add new fields
248ALTER TABLE `item_instance`
249ADD `creatorGuid` int(10) unsigned NOT NULL default '0' AFTER `owner_guid`,
250ADD `giftCreatorGuid` int(10) unsigned NOT NULL default '0' AFTER `creatorGuid`,
251ADD `count` int(10) unsigned NOT NULL default '1' AFTER `giftCreatorGuid`,
252ADD `duration` int(10) unsigned NOT NULL AFTER `count`,
253ADD `charges` text NOT NULL AFTER `duration`,
254ADD `flags` int(10) unsigned NOT NULL default '0' AFTER `charges`,
255ADD `enchantments` text NOT NULL AFTER `flags`,
256ADD `randomPropertyId` bigint(20) NOT NULL default '0' AFTER `enchantments`,
257ADD `durability` int(10) unsigned NOT NULL default '0' AFTER `randomPropertyId`,
258ADD `playedTime` int(10) unsigned NOT NULL default '0' AFTER `durability`;
259
260-- Temporarily change delimiter to prevent SQL syntax errors
261DELIMITER ||
262
263-- Function to convert ints from unsigned to signed
264DROP FUNCTION IF EXISTS `uint32toint32`||
265CREATE FUNCTION `uint32toint32`(input INT(10) UNSIGNED) RETURNS BIGINT(20) SIGNED DETERMINISTIC
266BEGIN
267  RETURN input;
268END||
269
270-- Restore original delimiter
271DELIMITER ;
272
273-- Move data to new fields
274UPDATE `item_instance` SET
275`creatorGuid` = SUBSTRING(`data`,
276length(SUBSTRING_INDEX(`data`,' ',10))+2,
277length(SUBSTRING_INDEX(`data`,' ',10+1))-length(SUBSTRING_INDEX(data,' ',10))-1),
278
279`giftCreatorGuid` = SUBSTRING(`data`,
280length(SUBSTRING_INDEX(`data`,' ',12))+2,
281length(SUBSTRING_INDEX(`data`,' ',12+1))-length(SUBSTRING_INDEX(data,' ',12))-1),
282
283`count` = SUBSTRING(`data`,
284length(SUBSTRING_INDEX(`data`,' ',14))+2,
285length(SUBSTRING_INDEX(`data`,' ',14+1))-length(SUBSTRING_INDEX(data,' ',14))-1),
286
287`duration` = SUBSTRING(`data`,
288length(SUBSTRING_INDEX(`data`,' ',15))+2,
289length(SUBSTRING_INDEX(`data`,' ',15+1))-length(SUBSTRING_INDEX(data,' ',15))-1),
290
291`charges` = CONCAT_WS(' ',
292uint32toint32(SUBSTRING(`data`,
293length(SUBSTRING_INDEX(`data`,' ',16))+2,
294length(SUBSTRING_INDEX(`data`,' ',16+1))-length(SUBSTRING_INDEX(data,' ',16))-1)),
295uint32toint32(SUBSTRING(`data`,
296length(SUBSTRING_INDEX(`data`,' ',17))+2,
297length(SUBSTRING_INDEX(`data`,' ',17+1))-length(SUBSTRING_INDEX(data,' ',17))-1)),
298uint32toint32(SUBSTRING(`data`,
299length(SUBSTRING_INDEX(`data`,' ',18))+2,
300length(SUBSTRING_INDEX(`data`,' ',18+1))-length(SUBSTRING_INDEX(data,' ',18))-1)),
301uint32toint32(SUBSTRING(`data`,
302length(SUBSTRING_INDEX(`data`,' ',19))+2,
303length(SUBSTRING_INDEX(`data`,' ',19+1))-length(SUBSTRING_INDEX(data,' ',19))-1)),
304uint32toint32(SUBSTRING(`data`,
305length(SUBSTRING_INDEX(`data`,' ',20))+2,
306length(SUBSTRING_INDEX(`data`,' ',20+1))-length(SUBSTRING_INDEX(data,' ',20))-1))),
307
308`flags` = SUBSTRING(`data`,
309length(SUBSTRING_INDEX(`data`,' ',21))+2,
310length(SUBSTRING_INDEX(`data`,' ',21+1))-length(SUBSTRING_INDEX(data,' ',21))-1),
311
312`enchantments` = SUBSTRING(`data`,
313length(SUBSTRING_INDEX(`data`,' ',22))+2,
314length(SUBSTRING_INDEX(`data`,' ',57+1))-length(SUBSTRING_INDEX(data,' ',22))-1),
315
316`randomPropertyId` = uint32toint32(SUBSTRING(`data`,
317length(SUBSTRING_INDEX(`data`,' ',59))+2,
318length(SUBSTRING_INDEX(`data`,' ',59+1))-length(SUBSTRING_INDEX(data,' ',59))-1)),
319
320`durability` = SUBSTRING(`data`,
321length(SUBSTRING_INDEX(`data`,' ',60))+2,
322length(SUBSTRING_INDEX(`data`,' ',60+1))-length(SUBSTRING_INDEX(data,' ',60))-1),
323
324`playedTime` = SUBSTRING(`data`,
325length(SUBSTRING_INDEX(`data`,' ',62))+2,
326length(SUBSTRING_INDEX(`data`,' ',62+1))-length(SUBSTRING_INDEX(data,' ',62))-1);
327
328-- Drop function
329DROP FUNCTION IF EXISTS `uint32toint32`;
330
331-- Fix heroic item flag
332UPDATE `item_instance` SET `flags`=`flags`&~0x8 WHERE
333SUBSTRING(`data`,
334length(SUBSTRING_INDEX(`data`,' ',3))+2,
335length(SUBSTRING_INDEX(`data`,' ',3+1))-length(SUBSTRING_INDEX(data,' ',3))-1)
336NOT IN (5043,5044,17302,17305,17308,21831);
337
338-- Drop old field
339ALTER TABLE `item_instance` DROP `data`;
340
341/* Execute 09160_characters_item_instance.sql */
342SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
343
344UPDATE `item_instance` SET `flags` = (`flags` & @allowedFlags);
345
346-- item_refund_instance
347
348-- item_soulbound_trade_data
349DELETE FROM item_soulbound_trade_data;
350INSERT INTO item_soulbound_trade_data SELECT * FROM convert_mg_characters.item_soulbound_trade_data;
351
352-- lag_reports
353-- lfg_data
354
355-- mail
356DELETE FROM mail;
357INSERT INTO mail SELECT * FROM convert_mg_characters.mail;
358
359-- mail_items
360DELETE FROM mail_items;
361INSERT INTO mail_items SELECT mail_id, item_guid, receiver FROM convert_mg_characters.mail_items;
362
363-- pet_aura
364
365-- pet_spell
366DELETE FROM pet_spell;
367INSERT INTO pet_spell SELECT guid, spell, active FROM convert_mg_characters.pet_spell;
368
369-- pet_spell_cooldown
370
371-- petition
372DELETE FROM petition;
373INSERT INTO petition SELECT * FROM convert_mg_characters.petition;
374
375-- petition_sign
376DELETE FROM petition_sign;
377INSERT INTO petition_sign SELECT * FROM convert_mg_characters.petition_sign;
378
379-- pool_quest_save
380-- reserved_name
381-- warden_action
382-- worldstates