uZGbVvWF

1-- Query let convert characters DB from format
2-- MaNGOS Zero characters DB `required_z1597_s1099_02_characters_pet_aura` to
3-- MaNGOS One characters DB `required_s1099_11299_02_characters_pet_aura`.
4
5-- Expected that in case Mangos Zero characters DB changes it will updated for more up-to-date versions.
6-- For targeted MaNGOS Master characters DB you can after convertion apply MaNGOS SQL updates in normal
7-- way for update characters DB to more recent revision.
8
9-- Note: ALWAYS DO BACKUP before use it. You will CAN NOT easy restore original DB state after tool use.
10
11ALTER TABLE character_db_version CHANGE COLUMN `required_z1597_s1099_02_characters_pet_aura` `required_s1099_11299_02_characters_pet_aura` bit;
12
13
14-- MODIFID TABLES
15
16ALTER TABLE characters
17  ADD COLUMN `dungeon_difficulty` tinyint(1) unsigned NOT NULL DEFAULT '0' AFTER `map`,
18  ADD COLUMN `arenaPoints` int(10) UNSIGNED NOT NULL default '0' AFTER `taxi_path`,
19  ADD COLUMN `totalHonorPoints` int(10) UNSIGNED NOT NULL default '0' AFTER `arenaPoints`,
20  ADD COLUMN `todayHonorPoints` int(10) UNSIGNED NOT NULL default '0' AFTER `totalHonorPoints`,
21  ADD COLUMN `yesterdayHonorPoints` int(10) UNSIGNED NOT NULL default '0' AFTER `todayHonorPoints`,
22  ADD COLUMN `totalKills` int(10) UNSIGNED NOT NULL default '0' AFTER `yesterdayHonorPoints`,
23  ADD COLUMN `todayKills` smallint(5) UNSIGNED NOT NULL default '0' AFTER `totalKills`,
24  ADD COLUMN `yesterdayKills` smallint(5) UNSIGNED NOT NULL default '0' AFTER `todayKills`,
25  ADD COLUMN `chosenTitle` int(10) UNSIGNED NOT NULL default '0' AFTER `yesterdayKills`,
26  DROP COLUMN `honor_highest_rank`,
27  DROP COLUMN `honor_standing`,
28  DROP COLUMN `stored_honor_rating`,
29  DROP COLUMN `stored_dishonorable_kills`,
30  DROP COLUMN `stored_honorable_kills`,
31  ADD COLUMN `knownTitles` longtext AFTER `ammoId`;
32
33UPDATE characters
34  SET at_login = at_login | 4; -- reset talents at login
35
36UPDATE characters              --  avoid client crashes at not existed item models
37  SET equipmentCache = '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ';
38
39UPDATE characters
40  SET knownTitles = '0 0 ';
41
42ALTER TABLE character_social
43  ADD COLUMN `note` varchar(48) NOT NULL DEFAULT '' COMMENT 'Friend Note' AFTER `flags`;
44
45
46ALTER TABLE character_stats
47  ADD COLUMN `spellCritPct` float UNSIGNED NOT NULL default '0' AFTER `rangedCritPct`,
48  ADD COLUMN `spellPower` int(10) UNSIGNED NOT NULL default '0' AFTER `rangedAttackPower`;
49
50ALTER TABLE groups
51  ADD COLUMN `difficulty` tinyint(3) unsigned NOT NULL default '0' AFTER isRaid;
52
53ALTER TABLE guild
54  ADD COLUMN `BankMoney` bigint(20) NOT NULL default '0' AFTER `createdate`;
55
56ALTER TABLE guild_member
57  ADD COLUMN `BankResetTimeMoney` int(11) unsigned NOT NULL default '0' AFTER `offnote`,
58  ADD COLUMN `BankRemMoney` int(11) unsigned NOT NULL default '0' AFTER `BankResetTimeMoney`,
59  ADD COLUMN `BankResetTimeTab0` int(11) unsigned NOT NULL default '0' AFTER `BankRemMoney`,
60  ADD COLUMN `BankRemSlotsTab0` int(11) unsigned NOT NULL default '0' AFTER `BankResetTimeTab0`,
61  ADD COLUMN `BankResetTimeTab1` int(11) unsigned NOT NULL default '0' AFTER `BankRemSlotsTab0`,
62  ADD COLUMN `BankRemSlotsTab1` int(11) unsigned NOT NULL default '0' AFTER `BankResetTimeTab1`,
63  ADD COLUMN `BankResetTimeTab2` int(11) unsigned NOT NULL default '0' AFTER `BankRemSlotsTab1`,
64  ADD COLUMN `BankRemSlotsTab2` int(11) unsigned NOT NULL default '0' AFTER `BankResetTimeTab2`,
65  ADD COLUMN `BankResetTimeTab3` int(11) unsigned NOT NULL default '0' AFTER `BankRemSlotsTab2`,
66  ADD COLUMN `BankRemSlotsTab3` int(11) unsigned NOT NULL default '0' AFTER `BankResetTimeTab3`,
67  ADD COLUMN `BankResetTimeTab4` int(11) unsigned NOT NULL default '0' AFTER `BankRemSlotsTab3`,
68  ADD COLUMN `BankRemSlotsTab4` int(11) unsigned NOT NULL default '0' AFTER `BankResetTimeTab4`,
69  ADD COLUMN `BankResetTimeTab5` int(11) unsigned NOT NULL default '0' AFTER `BankRemSlotsTab4`,
70  ADD COLUMN `BankRemSlotsTab5` int(11) unsigned NOT NULL default '0' AFTER `BankResetTimeTab5`;
71
72ALTER TABLE guild_rank
73  ADD COLUMN `BankMoneyPerDay` int(11) unsigned NOT NULL default '0' AFTER `rights`;
74
75ALTER TABLE instance
76  ADD COLUMN `difficulty` tinyint(1) unsigned NOT NULL default '0' AFTER `resettime`;
77
78ALTER TABLE item_loot
79  ADD COLUMN `suffix` int(11) unsigned NOT NULL default '0' AFTER `amount`;
80
81ALTER TABLE petition
82  ADD COLUMN `type` int(10) unsigned NOT NULL default '0' AFTER `name`,
83  DROP PRIMARY KEY,
84  ADD PRIMARY KEY (`ownerguid`,`type`);
85
86UPDATE petition
87  SET type = 9;  -- guild petition
88
89ALTER TABLE petition_sign
90  ADD COLUMN `type` int(10) unsigned NOT NULL default '0' AFTER `player_account`;
91
92UPDATE petition_sign
93  SET type = 9;  -- guild petition
94
95ALTER TABLE saved_variables
96  ADD COLUMN `NextArenaPointDistributionTime` bigint(40) UNSIGNED NOT NULL DEFAULT '0' AFTER `NextMaintenanceDate`,
97  ADD COLUMN `NextDailyQuestResetTime` bigint(40) unsigned NOT NULL default '0' AFTER `NextArenaPointDistributionTime`;
98
99
100DELETE FROM `character_spell_cooldown`;
101DELETE FROM `pet_spell_cooldown`;
102DELETE FROM `character_tutorial`;
103DELETE FROM `character_aura`;
104DELETE FROM `pet_aura`;
105
106
107-- FIELD VALUES CONVERT
108
109-- 0.6 -> 0.7 none
110
111-- 0.7 -> 0.8
112
113DELETE FROM `character_spell` WHERE `spell` = '23301';
114
115-- 0.8 -> 0.9 none
116-- 0.9 -> 0.10 none
117-- 0.10 -> 0.11 none
118
119UPDATE item_instance SET data= CONCAT(
120  SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',22),' ',-22),' 0 0 0 0 0 0 0 0 0 0 0 0 ',
121  SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',48),' ',-48+22))
122WHERE SUBSTRING_INDEX(data,' ',48) = data AND SUBSTRING_INDEX(data,' ',48-1) <> data;
123
124UPDATE item_instance SET data= CONCAT(
125  SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',22),' ',-22),' 0 0 0 0 0 0 0 0 0 0 0 0 ',
126  SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',106),' ',-106+22),' 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ')
127WHERE SUBSTRING_INDEX(data,' ',106) = data AND SUBSTRING_INDEX(data,' ',106-1) <> data;
128
129-- NEW TABLES with non critical info drop
130
131DROP TABLE IF EXISTS `arena_team`;
132CREATE TABLE `arena_team` (
133  `arenateamid` int(10) unsigned NOT NULL default '0',
134  `name` char(255) NOT NULL,
135  `captainguid` int(10) unsigned NOT NULL default '0',
136  `type` tinyint(3) unsigned NOT NULL default '0',
137  `BackgroundColor` int(10) unsigned NOT NULL default '0',
138  `EmblemStyle` int(10) unsigned NOT NULL default '0',
139  `EmblemColor` int(10) unsigned NOT NULL default '0',
140  `BorderStyle` int(10) unsigned NOT NULL default '0',
141  `BorderColor` int(10) unsigned NOT NULL default '0',
142  PRIMARY KEY  (`arenateamid`)
143) ENGINE=InnoDB DEFAULT CHARSET=utf8;
144
145DROP TABLE IF EXISTS `arena_team_member`;
146CREATE TABLE `arena_team_member` (
147  `arenateamid` int(10) unsigned NOT NULL default '0',
148  `guid` int(10) unsigned NOT NULL default '0',
149  `played_week` int(10) unsigned NOT NULL default '0',
150  `wons_week` int(10) unsigned NOT NULL default '0',
151  `played_season` int(10) unsigned NOT NULL default '0',
152  `wons_season` int(10) unsigned NOT NULL default '0',
153  `personal_rating` int(10) UNSIGNED NOT NULL DEFAULT '0',
154  PRIMARY KEY  (`arenateamid`,`guid`)
155) ENGINE=InnoDB DEFAULT CHARSET=utf8;
156
157DROP TABLE IF EXISTS `arena_team_stats`;
158CREATE TABLE `arena_team_stats` (
159  `arenateamid` int(10) unsigned NOT NULL default '0',
160  `rating` int(10) unsigned NOT NULL default '0',
161  `games_week` int(10) unsigned NOT NULL default '0',
162  `wins_week` int(10) unsigned NOT NULL default '0',
163  `games_season` int(10) unsigned NOT NULL default '0',
164  `wins_season` int(10) unsigned NOT NULL default '0',
165  `rank` int(10) unsigned NOT NULL default '0',
166  PRIMARY KEY  (`arenateamid`)
167) ENGINE=InnoDB DEFAULT CHARSET=utf8;
168
169DROP TABLE IF EXISTS `character_declinedname`;
170CREATE TABLE `character_declinedname` (
171  `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
172  `genitive` varchar(15) NOT NULL default '',
173  `dative` varchar(15) NOT NULL default '',
174  `accusative` varchar(15) NOT NULL default '',
175  `instrumental` varchar(15) NOT NULL default '',
176  `prepositional` varchar(15) NOT NULL default '',
177  PRIMARY KEY  (`guid`)
178) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
179
180
181DROP TABLE IF EXISTS `character_pet_declinedname`;
182CREATE TABLE `character_pet_declinedname` (
183  `id` int(11) unsigned NOT NULL default '0',
184  `owner` int(11) unsigned NOT NULL default '0',
185  `genitive` varchar(12) NOT NULL default '',
186  `dative` varchar(12) NOT NULL default '',
187  `accusative` varchar(12) NOT NULL default '',
188  `instrumental` varchar(12) NOT NULL default '',
189  `prepositional` varchar(12) NOT NULL default '',
190  PRIMARY KEY  (`id`),
191  KEY owner_key (`owner`)
192) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
193
194DROP TABLE IF EXISTS `character_queststatus_daily`;
195CREATE TABLE `character_queststatus_daily` (
196  `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
197  `quest` int(11) unsigned NOT NULL default '0' COMMENT 'Quest Identifier',
198  PRIMARY KEY  (`guid`,`quest`),
199  KEY `idx_guid` (`guid`)
200) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System';
201
202
203DROP TABLE IF EXISTS `guild_bank_eventlog`;
204CREATE TABLE `guild_bank_eventlog` (
205  `guildid` int(11) unsigned NOT NULL default '0' COMMENT 'Guild Identificator',
206  `LogGuid` int(11) unsigned NOT NULL default '0' COMMENT 'Log record identificator - auxiliary column',
207  `TabId` tinyint(3) unsigned NOT NULL default '0' COMMENT 'Guild bank TabId',
208  `EventType` tinyint(3) unsigned NOT NULL default '0' COMMENT 'Event type',
209  `PlayerGuid` int(11) unsigned NOT NULL default '0',
210  `ItemOrMoney` int(11) unsigned NOT NULL default '0',
211  `ItemStackCount` tinyint(3) unsigned NOT NULL default '0',
212  `DestTabId` tinyint(1) unsigned NOT NULL default '0' COMMENT 'Destination Tab Id',
213  `TimeStamp` bigint(20) unsigned NOT NULL default '0' COMMENT 'Event UNIX time',
214  PRIMARY KEY  (`guildid`,`LogGuid`,`TabId`),
215  KEY `guildid_key` (`guildid`),
216  INDEX `Idx_PlayerGuid`(`PlayerGuid`),
217  INDEX `Idx_LogGuid`(`LogGuid`)
218) ENGINE=InnoDB DEFAULT CHARSET=utf8;
219
220DROP TABLE IF EXISTS `guild_bank_item`;
221CREATE TABLE `guild_bank_item` (
222  `guildid` int(11) unsigned NOT NULL default '0',
223  `TabId` tinyint(1) unsigned NOT NULL default '0',
224  `SlotId` tinyint(3) unsigned NOT NULL default '0',
225  `item_guid` int(11) unsigned NOT NULL default '0',
226  `item_entry` int(11) unsigned NOT NULL default '0',
227  PRIMARY KEY  (`guildid`,`tabid`,`slotid`),
228  KEY `guildid_key` (`guildid`),
229  INDEX `Idx_item_guid`(`item_guid`)
230) ENGINE=InnoDB DEFAULT CHARSET=utf8;
231
232DROP TABLE IF EXISTS `guild_bank_right`;
233CREATE TABLE `guild_bank_right` (
234  `guildid` int(11) unsigned NOT NULL default '0',
235  `TabId` tinyint(1) unsigned NOT NULL default '0',
236  `rid` int(11) unsigned NOT NULL default '0',
237  `gbright` tinyint(3) unsigned NOT NULL default '0',
238  `SlotPerDay` int(11) unsigned NOT NULL default '0',
239  PRIMARY KEY  (`guildid`,`TabId`,`rid`),
240  KEY `guildid_key` (`guildid`)
241) ENGINE=InnoDB DEFAULT CHARSET=utf8;
242
243DROP TABLE IF EXISTS `guild_bank_tab`;
244CREATE TABLE `guild_bank_tab` (
245  `guildid` int(11) unsigned NOT NULL default '0',
246  `TabId` tinyint(1) unsigned NOT NULL default '0',
247  `TabName` varchar(100) NOT NULL default '',
248  `TabIcon` varchar(100) NOT NULL default '',
249  `TabText` text,
250  PRIMARY KEY  (`guildid`,`TabId`),
251  KEY `guildid_key` (`guildid`)
252) ENGINE=InnoDB DEFAULT CHARSET=utf8;
253
254-- REMOVED TABLES with non critical info drop
255
256DROP TABLE IF EXISTS `character_honor_cp`;
257
258ALTER TABLE character_db_version CHANGE COLUMN required_s1099_11299_02_characters_pet_aura required_s1342_11704_01_characters_auction bit;
259
260ALTER TABLE `auction`
261  DROP KEY `item_guid`,
262  ADD COLUMN `item_count` int(11) unsigned NOT NULL default '0' AFTER `item_template`,
263  ADD COLUMN `item_randompropertyid` int(11) NOT NULL default '0' AFTER `item_count`;
264
265UPDATE auction, item_instance
266  SET auction.item_count = SUBSTRING_INDEX(SUBSTRING_INDEX(item_instance.data, ' ', 14 + 1), ' ', -1)
267  WHERE auction.itemguid = item_instance.guid;
268
269ALTER TABLE character_db_version CHANGE COLUMN required_s1342_11704_01_characters_auction required_s1350_11716_01_characters_auction bit;
270
271ALTER TABLE `auction`
272  CHANGE COLUMN `time` `time` bigint(40) unsigned NOT NULL default '0';
273
274ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_01_characters_auction required_s1350_11716_02_characters_characters bit;
275
276ALTER TABLE `characters`
277  CHANGE COLUMN `deleteDate` `deleteDate` bigint(20) unsigned default NULL;
278
279ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_02_characters_characters required_s1350_11716_03_characters_creature_respawn bit;
280
281ALTER TABLE `creature_respawn`
282  CHANGE COLUMN `respawntime` `respawntime` bigint(20) unsigned NOT NULL default '0';
283
284ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_03_characters_creature_respawn required_s1350_11716_04_characters_gameobject_respawn bit;
285
286ALTER TABLE `gameobject_respawn`
287  CHANGE COLUMN `respawntime` `respawntime` bigint(20) unsigned NOT NULL default '0';
288
289ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_04_characters_gameobject_respawn required_s1350_11716_05_characters_guild bit;
290
291ALTER TABLE `guild`
292  CHANGE COLUMN `createdate` `createdate` bigint(20) unsigned NOT NULL default '0',
293  CHANGE COLUMN `BankMoney` `BankMoney` bigint(20) unsigned NOT NULL default '0';
294
295ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_05_characters_guild required_s1350_11716_06_characters_guild_eventlog bit;
296
297ALTER TABLE `guild_eventlog`
298  CHANGE COLUMN `guildid` `guildid` int(11) unsigned NOT NULL COMMENT 'Guild Identificator',
299  CHANGE COLUMN `LogGuid` `LogGuid` int(11) unsigned NOT NULL COMMENT 'Log record identificator - auxiliary column',
300  CHANGE COLUMN `EventType` `EventType` tinyint(1) unsigned NOT NULL COMMENT 'Event type',
301  CHANGE COLUMN `PlayerGuid1` `PlayerGuid1` int(11) unsigned NOT NULL COMMENT 'Player 1',
302  CHANGE COLUMN `PlayerGuid2` `PlayerGuid2` int(11) unsigned NOT NULL COMMENT 'Player 2',
303  CHANGE COLUMN `NewRank` `NewRank` tinyint(2) unsigned NOT NULL COMMENT 'New rank(in case promotion/demotion)',
304  CHANGE COLUMN `TimeStamp` `TimeStamp` bigint(20) unsigned NOT NULL COMMENT 'Event UNIX time';
305
306ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_06_characters_guild_eventlog required_s1350_11716_07_characters_instance bit;
307
308ALTER TABLE `instance`
309  CHANGE COLUMN `resettime` `resettime` bigint(40) unsigned NOT NULL default '0';
310
311ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_07_characters_instance required_s1350_11716_08_characters_instance_reset bit;
312
313ALTER TABLE `instance_reset`
314  CHANGE COLUMN `resettime` `resettime` bigint(40) unsigned NOT NULL default '0';
315
316ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_08_characters_instance_reset required_s1350_11716_09_characters_mail bit;
317
318ALTER TABLE `mail`
319  CHANGE COLUMN `expire_time` `expire_time` bigint(40) unsigned NOT NULL default '0',
320  CHANGE COLUMN `deliver_time` `deliver_time` bigint(40) unsigned NOT NULL default '0';
321
322ALTER TABLE character_db_version CHANGE COLUMN required_s1350_11716_09_characters_mail required_s1699_xxxxx_01_characters_characters bit;
323ALTER TABLE `characters` ADD COLUMN `grantableLevels` tinyint(3) unsigned NOT NULL default '0' AFTER `actionBars`;