· 8 years ago · Jul 05, 2018, 03:42 PM
1DELETE FROM creature WHERE id NOT IN (SELECT entry FROM creature_template);
2DELETE FROM creature_addon WHERE guid NOT IN (SELECT guid FROM creature);
3DELETE FROM pool_creature WHERE guid NOT IN(SELECT guid FROM creature);
4DELETE FROM game_event_creature WHERE guid NOT IN (SELECT guid FROM creature);
5
6DROP TABLE IF EXISTS `creature_temp`;
7CREATE TABLE IF NOT EXISTS `creature_temp` (
8 `guid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Global Unique Identifier',
9 `id` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'Creature Identifier',
10 `map` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Map Identifier',
11 `spawnMask` tinyint(3) unsigned NOT NULL DEFAULT '1',
12 `phaseMask` smallint(5) unsigned NOT NULL DEFAULT '1',
13 `modelid` mediumint(8) unsigned NOT NULL DEFAULT '0',
14 `equipment_id` mediumint(9) NOT NULL DEFAULT '0',
15 `position_x` float NOT NULL DEFAULT '0',
16 `position_y` float NOT NULL DEFAULT '0',
17 `position_z` float NOT NULL DEFAULT '0',
18 `orientation` float NOT NULL DEFAULT '0',
19 `spawntimesecs` int(10) unsigned NOT NULL DEFAULT '120',
20 `spawndist` float NOT NULL DEFAULT '0',
21 `currentwaypoint` mediumint(8) unsigned NOT NULL DEFAULT '0',
22 `curhealth` int(10) unsigned NOT NULL DEFAULT '1',
23 `curmana` int(10) unsigned NOT NULL DEFAULT '0',
24 `MovementType` tinyint(3) unsigned NOT NULL DEFAULT '0',
25 `npcflag` int(10) unsigned NOT NULL DEFAULT '0',
26 `unit_flags` int(10) unsigned NOT NULL DEFAULT '0',
27 `dynamicflags` int(10) unsigned NOT NULL DEFAULT '0',
28 `old_guid` int(10) unsigned NOT NULL DEFAULT '0',
29 PRIMARY KEY (`guid`),
30 KEY `idx_map` (`map`),
31 KEY `idx_id` (`id`),
32 KEY `idx_oldguid_tmp` (`old_guid`)
33) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Creature System' AUTO_INCREMENT=1;
34
35ALTER TABLE `creature_addon` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
36ALTER TABLE `creature_formations` ADD COLUMN `new_guid_leader` INT(10) UNSIGNED NOT NULL;
37ALTER TABLE `creature_formations` ADD COLUMN `new_guid_member` INT(10) UNSIGNED NOT NULL;
38ALTER TABLE `game_event_creature` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
39ALTER TABLE `pool_creature` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
40ALTER TABLE `game_event_model_equip` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
41ALTER TABLE `game_event_npc_vendor` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
42ALTER TABLE `game_event_npcflag` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
43ALTER TABLE `smart_scripts` ADD COLUMN `new_guid` INT(10) NOT NULL;
44
45INSERT INTO `creature_temp`
46 (
47 `id`, `map`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`,
48 `position_y`, `position_z`, `orientation`, `spawntimesecs`, `spawndist`,
49 `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`,
50 `unit_flags`, `dynamicflags`, `old_guid`
51 ) SELECT
52 `id`, `map`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`,
53 `position_y`, `position_z`, `orientation`, `spawntimesecs`, `spawndist`,
54 `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`,
55 `unit_flags`, `dynamicflags`, `guid`
56 FROM `creature` ORDER BY `id` ASC;
57
58UPDATE game_event_npc_vendor p
59 INNER JOIN creature_temp pp
60 ON p.guid = pp.old_guid
61 SET p.new_guid = pp.guid;
62UPDATE game_event_npcflag p
63 INNER JOIN creature_temp pp
64 ON p.guid = pp.old_guid
65 SET p.new_guid = pp.guid;
66UPDATE game_event_model_equip p
67 INNER JOIN creature_temp pp
68 ON p.guid = pp.old_guid
69 SET p.new_guid = pp.guid;
70UPDATE creature_addon p
71 INNER JOIN creature_temp pp
72 ON p.guid = pp.old_guid
73 SET p.new_guid = pp.guid;
74UPDATE creature_formations p
75 INNER JOIN creature_temp pp
76 ON p.leaderGUID = pp.old_guid
77 SET p.new_guid_leader = pp.guid;
78UPDATE creature_formations p
79 INNER JOIN creature_temp pp
80 ON p.memberGUID = pp.old_guid
81 SET p.new_guid_member = pp.guid;
82UPDATE game_event_creature p
83 INNER JOIN creature_temp pp
84 ON p.guid = pp.old_guid
85 SET p.new_guid = pp.guid;
86UPDATE pool_creature p
87 INNER JOIN creature_temp pp
88 ON p.guid = pp.old_guid
89 SET p.new_guid = pp.guid;
90UPDATE smart_scripts p
91 INNER JOIN creature_temp pp
92 ON p.entryorguid = pp.old_guid
93 SET p.new_guid = -pp.guid
94 WHERE
95 entryorguid < 0 AND
96 source_type = 0;
97
98DROP TABLE `creature`;
99ALTER TABLE `creature_temp` DROP COLUMN `old_guid`;
100RENAME TABLE `creature_temp` TO `creature`;
101
102ALTER TABLE game_event_npc_vendor DISABLE KEYS;
103UPDATE `game_event_npc_vendor` SET `guid`=`new_guid`;
104ALTER TABLE `game_event_npc_vendor` DROP COLUMN `new_guid`;
105ALTER TABLE game_event_npc_vendor ENABLE KEYS;
106
107ALTER TABLE game_event_npcflag DROP PRIMARY KEY;
108UPDATE `game_event_npcflag` SET `guid`=`new_guid`;
109ALTER TABLE `game_event_npcflag` DROP COLUMN `new_guid`;
110ALTER TABLE game_event_npcflag ADD PRIMARY KEY(`guid`);
111
112ALTER TABLE game_event_model_equip DISABLE KEYS;
113UPDATE `game_event_model_equip` SET `guid`=`new_guid`;
114ALTER TABLE `game_event_model_equip` DROP COLUMN `new_guid`;
115ALTER TABLE game_event_model_equip ENABLE KEYS;
116
117ALTER TABLE `creature_addon` DROP PRIMARY KEY;
118UPDATE `creature_addon` SET `guid`=`new_guid`;
119ALTER TABLE `creature_addon` DROP COLUMN `new_guid`;
120ALTER TABLE `creature_addon` ADD PRIMARY KEY(`guid`);
121
122ALTER TABLE creature_formations DROP PRIMARY KEY;
123UPDATE `creature_formations` SET leaderGUID = new_guid_leader;
124ALTER TABLE `creature_formations` DROP COLUMN `new_guid_leader`;
125UPDATE `creature_formations` SET memberGUID = new_guid_member;
126ALTER TABLE `creature_formations` DROP COLUMN `new_guid_member`;
127ALTER TABLE creature_formations ADD PRIMARY KEY(`memberGUID`);
128
129ALTER TABLE `game_event_creature` DROP PRIMARY KEY;
130UPDATE `game_event_creature` SET guid = new_guid;
131ALTER TABLE `game_event_creature` DROP COLUMN `new_guid`;
132ALTER TABLE `game_event_creature` ADD PRIMARY KEY(`guid`, `eventEntry`);
133
134ALTER TABLE `pool_creature` DROP PRIMARY KEY;
135UPDATE `pool_creature` SET guid = new_guid;
136ALTER TABLE `pool_creature` DROP COLUMN `new_guid`;
137ALTER TABLE `pool_creature` ADD PRIMARY KEY(`guid`);
138
139ALTER TABLE `smart_scripts` DROP PRIMARY KEY;
140UPDATE `smart_scripts` SET `entryorguid` = `new_guid`
141 WHERE entryorguid < 0 AND new_guid < 0 AND source_type = 0;
142ALTER TABLE `smart_scripts` DROP COLUMN `new_guid`;
143ALTER TABLE `smart_scripts` ADD PRIMARY KEY (`entryorguid`,`source_type`,`id`,`link`);