XfKPA2FK

1###############################
2# RUN THE FOLLOWING ONCE ONLY #
3###############################
4
5ALTER TABLE characters.item_instance ADD COLUMN cloned_item_id INT NULL;
6ALTER TABLE characters.item_loot ADD COLUMN cloned_item_id INT NULL;
7
8##########################################
9# DELETE THESE LINES AND THE LINES ABOVE #
10##########################################
11
12########################
13# EDIT THESE VARIABLES #
14########################
15
16SET @existing_char_name = 'Dw';
17SET @cloned_char_name = 'Dwclone';
18SET @existing_account_name = 'admin';
19SET @cloned_account_name = 'admin';
20
21###############################
22# DO NOT EDIT BELOW THIS LINE #
23###############################
24
25START TRANSACTION;
26
27SET @existing_char_guid = (
28	SELECT guid FROM characters.characters WHERE name = @existing_char_name
29);
30
31SET @existing_account_id = (
32	SELECT id FROM realmd.account WHERE username = @existing_account_name
33);
34SET @cloned_account_id = (
35	SELECT id FROM realmd.account WHERE username = @cloned_account_name
36);
37
38# Character Setup
39
40DROP TEMPORARY TABLE IF EXISTS characters.tmp;
41CREATE TEMPORARY TABLE characters.tmp
42	SELECT * FROM characters.characters WHERE name = @existing_char_name;
43ALTER TABLE characters.tmp DROP COLUMN guid;
44UPDATE characters.tmp SET name = @cloned_char_name, account = @cloned_account_id;
45INSERT INTO characters.characters
46	SELECT (
47		SELECT max(guid) + 1 as guid
48        FROM characters.characters
49	) as guid, characters.tmp.* FROM characters.tmp;
50DROP TEMPORARY TABLE characters.tmp;
51
52SET @cloned_char_guid = (
53	SELECT guid FROM characters.characters WHERE name = @cloned_char_name
54);
55
56# Item Setup
57ALTER TABLE characters.item_instance
58	MODIFY COLUMN guid INT UNIQUE
59    auto_increment;
60DROP TEMPORARY TABLE IF EXISTS characters.tmp;
61CREATE TEMPORARY TABLE characters.tmp
62	SELECT * FROM characters.item_instance
63    WHERE owner_guid = @existing_char_guid;
64UPDATE characters.tmp SET owner_guid = @cloned_char_guid;
65UPDATE characters.tmp SET cloned_item_id = guid;
66ALTER TABLE characters.tmp DROP guid;
67INSERT INTO characters.item_instance
68	SELECT 0, characters.tmp.* FROM characters.tmp;
69DROP TEMPORARY TABLE characters.tmp;
70ALTER TABLE characters.item_instance
71	MODIFY COLUMN guid INT UNIQUE;
72
73# Item Setup
74ALTER TABLE characters.item_loot
75	MODIFY COLUMN guid INT UNIQUE
76    auto_increment;
77DROP TEMPORARY TABLE IF EXISTS characters.tmp;
78CREATE TEMPORARY TABLE characters.tmp
79	SELECT * FROM characters.item_loot
80    WHERE owner_guid = @existing_char_guid;
81UPDATE characters.tmp SET owner_guid = @cloned_char_guid;
82UPDATE characters.tmp SET cloned_item_id = guid;
83ALTER TABLE characters.tmp DROP guid;
84INSERT INTO characters.item_loot
85	SELECT 0, characters.tmp.* FROM characters.tmp;
86DROP TEMPORARY TABLE characters.tmp;
87ALTER TABLE characters.item_instance
88	MODIFY COLUMN guid INT UNIQUE;
89
90# Action Setup
91DROP TEMPORARY TABLE IF EXISTS characters.tmp;
92CREATE TEMPORARY TABLE characters.tmp
93	SELECT * FROM characters.character_action
94    WHERE guid = @existing_char_guid;
95UPDATE characters.tmp SET guid = @cloned_char_guid;
96INSERT INTO characters.character_action SELECT * FROM characters.tmp;
97DROP TEMPORARY TABLE characters.tmp;
98
99# Aura Setup
100DROP TEMPORARY TABLE IF EXISTS characters.tmp;
101CREATE TEMPORARY TABLE characters.tmp
102	SELECT * FROM characters.character_aura WHERE guid = @existing_char_guid;
103UPDATE characters.tmp SET guid = @cloned_char_guid;
104INSERT INTO characters.character_aura SELECT * FROM characters.tmp;
105DROP TEMPORARY TABLE characters.tmp;
106
107# BG Data Setup
108DROP TEMPORARY TABLE IF EXISTS characters.tmp;
109CREATE TEMPORARY TABLE characters.tmp
110	SELECT * FROM characters.character_battleground_data
111    WHERE guid = @existing_char_guid;
112UPDATE characters.tmp SET guid = @cloned_char_guid;
113INSERT INTO characters.character_battleground_data
114	SELECT * FROM characters.tmp;
115DROP TEMPORARY TABLE characters.tmp;
116
117# BG Queue Setup
118DROP TEMPORARY TABLE IF EXISTS characters.tmp;
119CREATE TEMPORARY TABLE characters.tmp
120	SELECT * FROM characters.character_bgqueue
121    WHERE PlayerGUID = @existing_char_guid;
122UPDATE characters.tmp SET PlayerGUID = @cloned_char_guid;
123INSERT INTO characters.character_bgqueue
124	SELECT * FROM characters.tmp;
125DROP TEMPORARY TABLE characters.tmp;
126
127# Deleted Items Setup
128DROP TEMPORARY TABLE IF EXISTS characters.tmp;
129CREATE TEMPORARY TABLE characters.tmp
130	SELECT * FROM characters.character_deleted_items
131    WHERE player_guid = @existing_char_guid;
132UPDATE characters.tmp SET player_guid = @cloned_char_guid;
133INSERT INTO characters.character_deleted_items
134	SELECT * FROM characters.tmp;
135DROP TEMPORARY TABLE characters.tmp;
136
137# Forgotten Skills Setup
138DROP TEMPORARY TABLE IF EXISTS characters.tmp;
139CREATE TEMPORARY TABLE characters.tmp
140	SELECT * FROM characters.character_forgotten_skills
141    WHERE guid = @existing_char_guid;
142UPDATE characters.tmp SET guid = @cloned_char_guid;
143INSERT INTO characters.character_forgotten_skills
144	SELECT * FROM characters.tmp;
145DROP TEMPORARY TABLE characters.tmp;
146
147# Character Gifts
148DROP TEMPORARY TABLE IF EXISTS characters.tmp;
149CREATE TEMPORARY TABLE characters.tmp
150	SELECT * FROM characters.character_gifts
151    WHERE guid = @existing_char_guid;
152UPDATE characters.tmp SET guid = @cloned_char_guid;
153INSERT INTO characters.character_gifts
154	SELECT * FROM characters.tmp;
155DROP TEMPORARY TABLE characters.tmp;
156
157# Homebind Gifts
158DROP TEMPORARY TABLE IF EXISTS characters.tmp;
159CREATE TEMPORARY TABLE characters.tmp
160	SELECT * FROM characters.character_homebind
161    WHERE guid = @existing_char_guid;
162UPDATE characters.tmp SET guid = @cloned_char_guid;
163INSERT INTO characters.character_homebind
164	SELECT * FROM characters.tmp;
165DROP TEMPORARY TABLE characters.tmp;
166
167# Honor CP
168DROP TEMPORARY TABLE IF EXISTS characters.tmp;
169CREATE TEMPORARY TABLE characters.tmp
170	SELECT * FROM characters.character_honor_cp
171    WHERE guid = @existing_char_guid;
172UPDATE characters.tmp SET guid = @cloned_char_guid;
173INSERT INTO characters.character_honor_cp
174	SELECT * FROM characters.tmp;
175DROP TEMPORARY TABLE characters.tmp;
176
177# Instance
178DROP TEMPORARY TABLE IF EXISTS characters.tmp;
179CREATE TEMPORARY TABLE characters.tmp
180	SELECT * FROM characters.character_instance
181    WHERE guid = @existing_char_guid;
182UPDATE characters.tmp SET guid = @cloned_char_guid;
183INSERT INTO characters.character_instance
184	SELECT * FROM characters.tmp;
185DROP TEMPORARY TABLE characters.tmp;
186
187# Inventory - COME BACK
188DROP TEMPORARY TABLE IF EXISTS characters.tmp;
189CREATE TEMPORARY TABLE characters.tmp
190	SELECT * FROM characters.character_inventory
191    WHERE guid = @existing_char_guid;
192UPDATE characters.tmp SET guid = @cloned_char_guid;
193UPDATE characters.tmp SET item = (
194	SELECT guid FROM characters.item_instance WHERE cloned_item_id = item
195);
196INSERT INTO characters.character_inventory
197	SELECT * FROM characters.tmp;
198DROP TEMPORARY TABLE characters.tmp;
199
200# Pet
201DROP TEMPORARY TABLE IF EXISTS characters.tmp;
202CREATE TEMPORARY TABLE characters.tmp
203	SELECT * FROM characters.character_pet
204    WHERE owner = @existing_char_guid;
205UPDATE characters.tmp SET owner = @cloned_char_guid;
206INSERT INTO characters.character_pet
207	SELECT * FROM characters.tmp;
208DROP TEMPORARY TABLE characters.tmp;
209
210# Quest Status
211DROP TEMPORARY TABLE IF EXISTS characters.tmp;
212CREATE TEMPORARY TABLE characters.tmp
213	SELECT * FROM characters.character_queststatus
214    WHERE guid = @existing_char_guid;
215UPDATE characters.tmp SET guid = @cloned_char_guid;
216INSERT INTO characters.character_queststatus
217	SELECT * FROM characters.tmp;
218DROP TEMPORARY TABLE characters.tmp;
219
220# Rep
221DROP TEMPORARY TABLE IF EXISTS characters.tmp;
222CREATE TEMPORARY TABLE characters.tmp
223	SELECT * FROM characters.character_reputation
224    WHERE guid = @existing_char_guid;
225UPDATE characters.tmp SET guid = @cloned_char_guid;
226INSERT INTO characters.character_reputation
227	SELECT * FROM characters.tmp;
228DROP TEMPORARY TABLE characters.tmp;
229
230# Skills
231DROP TEMPORARY TABLE IF EXISTS characters.tmp;
232CREATE TEMPORARY TABLE characters.tmp
233	SELECT * FROM characters.character_skills
234    WHERE guid = @existing_char_guid;
235UPDATE characters.tmp SET guid = @cloned_char_guid;
236INSERT INTO characters.character_skills
237	SELECT * FROM characters.tmp;
238DROP TEMPORARY TABLE characters.tmp;
239
240# Social
241DROP TEMPORARY TABLE IF EXISTS characters.tmp;
242CREATE TEMPORARY TABLE characters.tmp
243	SELECT * FROM characters.character_social
244    WHERE guid = @existing_char_guid;
245UPDATE characters.tmp SET guid = @cloned_char_guid;
246INSERT INTO characters.character_social
247	SELECT * FROM characters.tmp;
248DROP TEMPORARY TABLE characters.tmp;
249
250#Spell
251DROP TEMPORARY TABLE IF EXISTS characters.tmp;
252CREATE TEMPORARY TABLE characters.tmp
253	SELECT * FROM characters.character_spell
254    WHERE guid = @existing_char_guid;
255UPDATE characters.tmp SET guid = @cloned_char_guid;
256INSERT INTO characters.character_spell
257	SELECT * FROM characters.tmp;
258DROP TEMPORARY TABLE characters.tmp;
259
260#Spell Cooldown
261DROP TEMPORARY TABLE IF EXISTS characters.tmp;
262CREATE TEMPORARY TABLE characters.tmp
263	SELECT * FROM characters.character_spell_cooldown
264    WHERE guid = @existing_char_guid;
265UPDATE characters.tmp SET guid = @cloned_char_guid;
266INSERT INTO characters.character_spell_cooldown
267	SELECT * FROM characters.tmp;
268DROP TEMPORARY TABLE characters.tmp;
269
270# Stats
271DROP TEMPORARY TABLE IF EXISTS characters.tmp;
272CREATE TEMPORARY TABLE characters.tmp
273	SELECT * FROM characters.character_stats
274    WHERE guid = @existing_char_guid;
275UPDATE characters.tmp SET guid = @cloned_char_guid;
276INSERT INTO characters.character_stats
277	SELECT * FROM characters.tmp;
278DROP TEMPORARY TABLE characters.tmp;
279
280# Ticket
281DROP TEMPORARY TABLE IF EXISTS characters.tmp;
282CREATE TEMPORARY TABLE characters.tmp
283	SELECT * FROM characters.character_ticket
284    WHERE guid = @existing_char_guid;
285UPDATE characters.tmp SET guid = @cloned_char_guid;
286INSERT INTO characters.character_ticket
287	SELECT * FROM characters.tmp;
288DROP TEMPORARY TABLE characters.tmp;
289
290COMMIT;