kyB9yfr0

1-- EXPORT INSTANCE DATA HELPER
2
3SET @MAP := XXX;
4
5SET @CCOUNT := 0;
6SET @OCOUNT := 0;
7SET @PCOUNT := 0;
8
9DROP TABLE IF EXISTS tmp_creature_update;
10CREATE TABLE IF NOT EXISTS tmp_creature_update AS (
11SELECT 0 AS new_guid, guid AS original_guid, id
12FROM creature
13WHERE guid IN (SELECT guid FROM creature WHERE map=@MAP)
14);
15UPDATE tmp_creature_update SET new_guid = (@CCOUNT := @CCOUNT + 1) ORDER BY id ASC, original_guid ASC;
16
17DROP TABLE IF EXISTS tmp_gameobject_update;
18CREATE TABLE IF NOT EXISTS tmp_gameobject_update AS (
19SELECT 0 AS new_guid, guid AS original_guid, id
20FROM gameobject
21WHERE guid IN (SELECT guid FROM gameobject WHERE map=@MAP)
22);
23UPDATE tmp_gameobject_update SET new_guid = (@OCOUNT := @OCOUNT + 1) ORDER BY id ASC, original_guid ASC;
24
25DROP TABLE IF EXISTS tmp_pool_update;
26CREATE TABLE IF NOT EXISTS tmp_pool_update AS (
27SELECT 0 AS new_entry, entry AS original_entry
28FROM pool_template
29WHERE entry IN (SELECT pool_entry FROM pool_creature WHERE guid IN (SELECT guid FROM creature WHERE map=@MAP))
30OR entry IN (SELECT pool_entry FROM pool_creature_template WHERE id IN (SELECT id FROM creature WHERE map=@MAP))
31OR entry IN (SELECT pool_entry FROM pool_gameobject WHERE guid IN (SELECT guid FROM gameobject WHERE map=@MAP))
32OR entry IN (SELECT pool_entry FROM pool_gameobject_template WHERE id IN (SELECT id FROM gameobject WHERE map=@MAP))
33);
34UPDATE tmp_pool_update SET new_entry = (@PCOUNT := @PCOUNT + 1) ORDER BY original_entry ASC;
35
36--
37-- CREATURES
38--
39
40SELECT CONCAT('(@CGUID+',B.new_guid,', ',point,', ',position_x,', ',position_y,', ',position_z,', ',orientation,', ',waittime,', ',script_id,'),')
41FROM creature_movement A, tmp_creature_update B
42WHERE A.id = B.original_guid
43ORDER BY B.new_guid ASC, point ASC;
44
45SELECT CONCAT('(',entry,', ',pathId,', ',point,', ',position_x,', ',position_y,', ',position_z,', ',orientation,', ',waittime,', ',script_id,'),')
46FROM creature_movement_template
47WHERE entry IN (SELECT id FROM creature WHERE map=@MAP)
48ORDER BY entry ASC, pathId ASC, point ASC;
49
50SELECT CONCAT('(',id,', ',delay,', ',command,', ',datalong,', ',datalong2,', ',datalong3,', ',buddy_entry,', ',search_radius,', ',data_flags,', ',dataint,', ',dataint2,', ',dataint3,', ',dataint4,', ',x,', ',y,', ',z,', ',o,', ''',comments,'''),')
51FROM dbscripts_on_creature_movement
52WHERE id IN (SELECT script_id FROM creature_movement WHERE id IN (SELECT guid FROM creature WHERE map=@MAP))
53OR id IN (SELECT script_id FROM creature_movement_template WHERE entry IN (SELECT id FROM creature WHERE map=@MAP))
54ORDER BY id ASC, delay ASC;
55
56/*
57-- search for movement scripts which depend on object/creature guid
58SELECT * FROM dbscripts_on_creature_movement WHERE data_flags = data_flags|16 AND (search_radius IN (SELECT guid FROM creature WHERE map=@MAP) OR search_radius IN (SELECT guid FROM gameobject WHERE map=@MAP));
59
60SELECT id, delay, command, B.new_guid AS datalong, datalong2, datalong3, buddy_entry, search_radius, data_flags, dataint, dataint2, dataint3, dataint4, x, y, z, o, comments
61FROM dbscripts_on_event A, tmp_gameobject_update B
62WHERE command=9 AND A.datalong = B.original_guid;
63
64-- other dbscripts which may not be so obviously related at first...
65-- dbscripts_on_relay, dbscript_string, dbscript_random_templates, dbscripts_on_event, dbscripts_on_spell, dbscripts_on_gossip, dbscripts_on_quest_start, dbscripts_on_quest_end
66-- select * from dbscripts_on_*
67*/
68
69SELECT CONCAT('(@CGUID+',B.new_guid,', ',mount,', ',bytes1,', ',b2_0_sheath,', ',b2_1_flags,', ',emote,', ',moveflags,', ',COALESCE(auras,'NULL'),'), -- ', C.Name)
70FROM creature_addon A, tmp_creature_update B, creature_template C
71WHERE A.guid = B.original_guid AND B.id=C.entry
72ORDER BY B.new_guid ASC;
73
74SELECT CONCAT('(',A.entry,', ',mount,', ',bytes1,', ',b2_0_sheath,', ',b2_1_flags,', ',emote,', ',moveflags,', ',COALESCE(auras,'NULL'),'), -- ', B.Name)
75FROM creature_template_addon A, creature_template B
76WHERE A.entry IN (SELECT id FROM creature WHERE map=@MAP) AND A.entry=B.entry
77ORDER BY A.entry ASC;
78
79SELECT CONCAT('(@CGUID+',B.new_guid,', ',event,'), -- ', C.Name, ' (', D.description, ')')
80FROM game_event_creature A, tmp_creature_update B, creature_template C, game_event D
81WHERE A.guid = B.original_guid AND B.id=C.entry AND A.event=D.entry
82ORDER BY B.new_guid ASC;
83
84SELECT CONCAT('(@CGUID+',B.new_guid,', ',entry_id,', ',modelid,', ',equipment_id,', ',spell_start,', ',spell_end,', ',event,'), -- ', C.Name, ' (', D.description, ')')
85FROM game_event_creature_data A, tmp_creature_update B, creature_template C, game_event D
86WHERE A.guid = B.original_guid AND B.id=C.entry AND A.event=D.entry
87ORDER BY B.new_guid ASC;
88
89SELECT DISTINCT CONCAT('(@CGUID+',B_slave.new_guid,', @CGUID+',B_master.new_guid,', ',flag,'), -- ', B_slave_ct.Name, ' -> ', B_master_ct.Name)
90FROM creature_linking A
91LEFT JOIN tmp_creature_update B_master ON B_master.original_guid = A.master_guid
92LEFT JOIN tmp_creature_update B_slave ON B_slave.original_guid = A.guid
93LEFT JOIN creature_template B_master_ct ON B_master.id = B_master_ct.entry
94LEFT JOIN creature_template B_slave_ct ON B_slave.id = B_slave_ct.entry
95WHERE B_slave.new_guid IS NOT NULL AND B_master.new_guid
96ORDER BY B_slave.new_guid ASC, B_master.new_guid ASC;
97
98SELECT CONCAT('(',A.entry,', ',map,', ',master_entry,', ',flag,', ',search_range,'), -- ', B_slave.Name, ' -> ', B_master.Name)
99FROM creature_linking_template A
100LEFT JOIN creature_template B_slave ON A.entry=B_slave.entry
101LEFT JOIN creature_template B_master ON A.master_entry=B_master.entry
102WHERE map=@MAP
103ORDER BY A.entry ASC;
104
105SELECT CONCAT('(',id,', ',delay,', ',command,', ',datalong,', ',datalong2,', ',datalong3,', ',buddy_entry,', ',search_radius,', ',data_flags,', ',dataint,', ',dataint2,', ',dataint3,', ',dataint4,', ',x,', ',y,', ',z,', ',o,', ''',comments,'''),')
106FROM dbscripts_on_creature_death
107WHERE id IN (SELECT id FROM creature WHERE map=@MAP)
108ORDER BY id ASC, delay ASC;
109
110SELECT CONCAT('(@PGUID+',B.new_entry,', ',max_limit,', ''',description,'''),') 
111FROM pool_template A, tmp_pool_update B
112WHERE A.entry = B.original_entry
113AND (A.entry IN (SELECT pool_entry FROM pool_creature WHERE guid IN (SELECT guid FROM creature WHERE map=@MAP))
114OR A.entry IN (SELECT pool_entry FROM pool_creature_template WHERE id IN (SELECT id FROM creature WHERE map=@MAP)))
115ORDER BY B.new_entry ASC;
116
117SELECT CONCAT('(@CGUID+',B.new_guid,', @PGUID+',C.new_entry,', ',chance,', ''',description,'''),')
118FROM pool_creature A, tmp_creature_update B, tmp_pool_update C
119WHERE (A.guid = B.original_guid AND A.pool_entry = C.original_entry)
120ORDER BY C.new_entry ASC, B.new_guid ASC;
121
122SELECT CONCAT('(',id,', @PGUID+',B.new_entry,', ',chance,', ''',description,'''),')
123FROM pool_creature_template A, tmp_pool_update B
124WHERE A.pool_entry = B.original_entry
125AND id IN (SELECT id FROM creature WHERE map=@MAP)
126ORDER BY B.new_entry ASC;
127
128SELECT CONCAT('(@CGUID+',B.new_guid,', ',A.event1,', ',A.event2,'), -- ',C.description)
129FROM creature_battleground A, tmp_creature_update B, battleground_events C
130WHERE A.guid = B.original_guid AND A.event1 = C.event1 AND A.event2 = C.event2 AND C.map = @MAP
131ORDER BY B.new_guid ASC;
132
133SELECT CONCAT('(@CGUID+',B.new_guid,', ',A.id,', ',map,', ',position_x,', ',position_y,', ',position_z,', ',orientation,', ',spawntimesecsmin,', ',spawntimesecsmax,', ',spawndist,', ',currentwaypoint,', ',DeathState,', ',A.MovementType,'), -- ', C.Name)
134FROM creature A, tmp_creature_update B, creature_template C
135WHERE A.guid = B.original_guid AND A.id=C.entry
136ORDER BY B.new_guid;
137
138DROP TABLE IF EXISTS tmp_creature_update;
139
140--
141-- GAMEOBJECTS
142--
143
144SELECT CONCAT('(@PGUID+',B.new_entry,', ',max_limit,', ''',description,'''),')
145FROM pool_template A, tmp_pool_update B
146WHERE A.entry = B.original_entry 
147AND (A.entry IN (SELECT pool_entry FROM pool_gameobject WHERE guid IN (SELECT guid FROM gameobject WHERE map=@MAP))
148OR A.entry IN (SELECT pool_entry FROM pool_gameobject_template WHERE id IN (SELECT id FROM gameobject WHERE map=@MAP)))
149ORDER BY B.new_entry ASC;
150
151SELECT CONCAT('(@OGUID+',B.new_guid,', @PGUID+',C.new_entry,', ',chance,', ''',description,'''),')
152FROM pool_gameobject A, tmp_gameobject_update B, tmp_pool_update C
153WHERE A.pool_entry = C.original_entry AND A.guid = B.original_guid
154ORDER BY C.new_entry ASC, B.new_guid ASC;
155
156SELECT CONCAT('(',id,', @PGUID+',B.new_entry,', ',chance,', ''',description,'''),')
157FROM pool_gameobject_template A, tmp_pool_update B
158WHERE A.pool_entry = B.original_entry AND id IN (SELECT id FROM gameobject WHERE map=@MAP)
159ORDER BY B.new_entry ASC;
160
161SELECT CONCAT('(@OGUID+',B.new_guid,', ',delay,', ',command,', ',CASE WHEN A.command IN (11,12) THEN CONCAT('@OGUID+',COALESCE(C.new_guid,datalong)) ELSE datalong END,', ',datalong2,', ',datalong3,', ',buddy_entry,', ',search_radius,', ',data_flags,', ',dataint,', ',dataint2,', ',dataint3,', ',dataint4,', ',x,', ',y,', ',z,', ',o,', ''',comments,'''),')
162FROM dbscripts_on_go_use A
163LEFT JOIN tmp_gameobject_update B ON A.id = B.original_guid
164LEFT JOIN tmp_gameobject_update C ON A.command IN (11,12) AND A.datalong = C.original_guid
165WHERE A.id IN (SELECT guid FROM gameobject WHERE map=@MAP)
166ORDER BY A.id ASC, delay ASC;
167
168SELECT CONCAT('(',id,', ',delay,', ',command,', ',datalong,', ',datalong2,', ',datalong3,', ',buddy_entry,', ',search_radius,', ',data_flags,', ',dataint,', ',dataint2,', ',dataint3,', ',dataint4,', ',x,', ',y,', ',z,', ',o,', ''',comments,'''),')
169FROM dbscripts_on_go_template_use
170WHERE id IN (SELECT id FROM gameobject WHERE map=@MAP)
171ORDER BY id ASC, delay ASC;
172
173SELECT CONCAT('(@OGUID+',B.new_guid,', ',event,'), -- ', C.Name, ' (', D.description, ')')
174FROM game_event_gameobject A, tmp_gameobject_update B, gameobject_template C, game_event D
175WHERE A.guid = B.original_guid AND B.id=C.entry AND A.event=D.entry
176ORDER BY B.new_guid ASC;
177
178SELECT CONCAT('(@OGUID+',B.new_guid,', ',A.event1,', ',A.event2,'), -- ',C.description)
179FROM gameobject_battleground A, tmp_gameobject_update B, battleground_events C
180WHERE A.guid = B.original_guid AND A.event1 = C.event1 AND A.event2 = C.event2 AND C.map = @MAP
181ORDER BY B.new_guid ASC;
182
183SELECT CONCAT('(@OGUID+',B.new_guid,', ',A.id,', ',map,', ',position_x,', ',position_y,', ',position_z,', ',orientation,', ',rotation0,', ',rotation1,', ',rotation2,', ',rotation3,', ',spawntimesecsmin,', ',spawntimesecsmax,', ',animprogress,', ',state,'), -- ', C.Name)
184FROM gameobject A, tmp_gameobject_update B, gameobject_template C
185WHERE A.guid = B.original_guid AND B.id=C.entry
186ORDER BY B.new_guid ASC;
187
188DROP TABLE IF EXISTS tmp_gameobject_update;
189
190DROP TABLE IF EXISTS tmp_pool_update;