FbdJP6pY

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