MhEXwCmi

1DROP TABLE IF EXISTS dst_db.objectIdx;
2CREATE TABLE dst_db.objectIdx (
3    object_id int NOT NULL,
4    PRIMARY KEY (object_id)
5);
6
7drop table if exists src_db.`acc_conv`;
8create table src_db.`acc_conv` (
9  `src_login` VARCHAR(32) NOT NULL,
10  `dst_login` VARCHAR(32) NOT NULL,
11  primary key(`src_login`),
12  unique key(`dst_login`)
13) engine=InnoDB default charset=utf8;
14
15
16
17INSERT INTO dst_db.objectIdx (object_id)
18SELECT obj_id from dst_db.characters;
19
20INSERT INTO dst_db.objectIdx (object_id)
21SELECT item_id from dst_db.items;
22
23INSERT INTO dst_db.objectIdx (object_id)
24SELECT clan_id from dst_db.clan_data;
25
26INSERT INTO dst_db.objectIdx (object_id)
27SELECT ally_id from dst_db.ally_data;
28
29INSERT INTO dst_db.objectIdx (object_id)
30SELECT objId from dst_db.pets;
31
32-- make table with current objectId's situation from imported database
33DROP TABLE IF EXISTS src_db.objectIdx;
34CREATE TABLE src_db.objectIdx (
35shortIdx int NOT NULL auto_increment,
36object_id int NOT NULL,
37PRIMARY KEY  (shortIdx),
38KEY `object_id` (`object_id`)
39);
40
41
42INSERT INTO src_db.objectIdx (object_id)
43SELECT obj_id FROM src_db.characters;
44INSERT INTO src_db.objectIdx (object_id)
45SELECT item_id FROM src_db.items;
46INSERT INTO src_db.objectIdx (object_id)
47SELECT clan_id FROM src_db.clan_data;
48INSERT INTO src_db.objectIdx (object_id)
49SELECT ally_id FROM src_db.ally_data;
50INSERT INTO src_db.objectIdx (object_id)
51SELECT objId FROM src_db.pets;
52
53-- make table with oldId/newId for imported database
54DROP TABLE IF EXISTS src_db.exportIdx;
55CREATE TABLE src_db.exportIdx (
56old_id int NOT NULL,
57new_id int NOT NULL,
58PRIMARY KEY  (old_id),
59KEY `new_id` (`new_id`)
60);
61INSERT INTO src_db.exportIdx
62SELECT object_id,shortIdx+(SELECT max(object_id) FROM dst_db.objectIdx)
63from src_db.objectIdx;
64
65-- okeys, lets do change old_id on new_id
66-- main ids change
67UPDATE src_db.characters a,src_db.exportIdx b 
68SET a.obj_id = b.new_id where a.obj_id = b.old_id; 
69
70UPDATE src_db.items a,src_db.exportIdx b
71SET a.item_id = b.new_id where a.item_id = b.old_id;
72
73UPDATE src_db.clan_data a,src_db.exportIdx b
74SET a.clan_id = b.new_id where a.clan_id = b.old_id;
75
76UPDATE src_db.ally_data a,src_db.exportIdx b
77SET a.ally_id = b.new_id where a.ally_id = b.old_id;
78
79UPDATE src_db.pets a,src_db.exportIdx b
80SET a.objId = b.new_id where a.objId = b.old_id;
81-- secondary ids change
82UPDATE src_db.items a,src_db.exportIdx b
83SET a.owner_id = b.new_id
84WHERE a.owner_id= b.old_id;
85
86UPDATE src_db.items_duration a,src_db.exportIdx b
87SET a.item_id = b.new_id
88WHERE a.item_id= b.old_id;
89
90UPDATE src_db.items_options a,src_db.exportIdx b
91SET a.item_id = b.new_id
92WHERE a.item_id= b.old_id;
93
94UPDATE src_db.items_period a,src_db.exportIdx b
95SET a.item_id = b.new_id
96WHERE a.item_id= b.old_id;
97
98UPDATE src_db.items_variation a,src_db.exportIdx b
99SET a.item_id = b.new_id
100WHERE a.item_id= b.old_id;
101
102UPDATE src_db.character_quests a,src_db.exportIdx b
103SET a.char_id = b.new_id
104WHERE a.char_id = b.old_id;
105
106UPDATE src_db.character_friends a,src_db.exportIdx b
107SET a.char_id = b.new_id
108WHERE a.char_id = b.old_id;
109
110UPDATE src_db.character_hennas a,src_db.exportIdx b
111SET a.char_obj_id = b.new_id
112WHERE a.char_obj_id = b.old_id;
113
114UPDATE src_db.character_recipebook a,src_db.exportIdx b
115SET a.char_id = b.new_id
116WHERE a.char_id = b.old_id;
117
118UPDATE src_db.character_shortcuts a,src_db.exportIdx b
119SET a.object_id = b.new_id
120WHERE a.object_id = b.old_id;
121
122UPDATE src_db.character_shortcuts a,src_db.exportIdx b
123SET a.shortcut_id = b.new_id
124WHERE a.shortcut_id = b.old_id AND type = 1;
125
126UPDATE src_db.character_macroses a,src_db.exportIdx b
127SET a.char_obj_id = b.new_id
128WHERE a.char_obj_id = b.old_id;
129
130UPDATE src_db.character_skills a,src_db.exportIdx b
131SET a.char_obj_id = b.new_id
132WHERE a.char_obj_id = b.old_id;
133
134UPDATE src_db.character_skills_save a,src_db.exportIdx b
135SET a.char_obj_id = b.new_id
136WHERE a.char_obj_id = b.old_id;
137
138UPDATE src_db.character_subclasses a,src_db.exportIdx b
139SET a.char_obj_id = b.new_id
140WHERE a.char_obj_id = b.old_id;
141
142UPDATE src_db.character_variables a,src_db.exportIdx b
143SET a.obj_id = b.new_id
144WHERE a.obj_id = b.old_id;
145
146UPDATE src_db.characters a,src_db.exportIdx b
147SET a.clanid = b.new_id
148WHERE a.clanid = b.old_id;
149
150UPDATE src_db.siege_clans a,src_db.exportIdx b
151SET a.clan_id = b.new_id
152WHERE a.clan_id = b.old_id;
153
154UPDATE src_db.clan_data a,src_db.exportIdx b
155SET a.ally_id = b.new_id
156WHERE a.ally_id = b.old_id;
157
158UPDATE src_db.clan_subpledges a,src_db.exportIdx b
159SET a.clan_id = b.new_id
160WHERE a.clan_id = b.old_id;
161
162UPDATE src_db.clan_privs a,src_db.exportIdx b
163SET a.clan_id = b.new_id
164WHERE a.clan_id = b.old_id;
165
166UPDATE src_db.clan_skills a,src_db.exportIdx b
167SET a.clan_id = b.new_id
168WHERE a.clan_id = b.old_id;
169
170UPDATE src_db.pets a,src_db.exportIdx b
171SET a.item_obj_id = b.new_id
172WHERE a.item_obj_id = b.old_id;
173
174insert into src_db.acc_conv (src_login, dst_login) select src_db.characters.account_name, src_db.characters.account_name from src_db.characters
175group by src_db.characters.account_name
176;
177
178delimiter $$
179drop FUNCTION if exists `lip_ex_TrunLoginWithPfx` $$
180CREATE DEFINER = CURRENT_USER FUNCTION `lip_ex_TrunLoginWithPfx` (`sSrcLogin` varchar(32)) RETURNS varchar(32)
181  NOT DETERMINISTIC
182  SQL SECURITY DEFINER
183entry: BEGIN
184  declare iIdx int default 0;
185  declare iCnt int default 0;
186	declare sLogin varchar(32) default 0;
187  set sLogin = sSrcLogin;
188
189  REPEAT
190    set iIdx = iIdx + 1;
191    if CHAR_LENGTH(sSrcLogin) < 11 THEN
192      select trim(concat(sSrcLogin, '_', iIdx)) into sLogin;
193    else
194      select trim(concat(LEFT(sSrcLogin, char_length(sSrcLogin) - 3), '_', iIdx)) into sLogin;
195    end if;
196    select count(dst_login) into iCnt from acc_conv where acc_conv.dst_login = sLogin;
197  UNTIL (iCnt < 1) END REPEAT;
198
199	RETURN sLogin;
200END $$
201delimiter ;
202
203update src_db.acc_conv set src_db.acc_conv.dst_login = lip_ex_TrunLoginWithPfx(src_db.acc_conv.src_login);
204
205SELECT '-- check login (in characters) ---';
206UPDATE src_db.characters, src_db.acc_conv
207SET src_db.characters.account_name = src_db.acc_conv.dst_login WHERE src_db.characters.account_name = src_db.acc_conv.src_login;
208
209
210
211SELECT '-- check char_name ---';
212UPDATE src_db.characters
213SET char_name = CONCAT(char_name,'XL')
214WHERE char_name in (SELECT char_name FROM dst_db.characters);
215
216SELECT '-- check clan_name ---';
217UPDATE src_db.clan_subpledges
218SET name = CONCAT(name,'XL')
219WHERE name in (SELECT name FROM dst_db.clan_subpledges);
220
221SELECT '-- check ally_name ---';
222UPDATE src_db.ally_data
223SET ally_name = CONCAT(ally_name,'XL')
224WHERE ally_name in (SELECT ally_name FROM dst_db.ally_data);
225
226-- now may start merge
227-- main tables
228SELECT 'Start merge process.';
229-- INSERT INTO dst_db.accounts SELECT * FROM src_db.accounts;
230
231insert into dst_db.accounts(login, `password`, email) 
232select src_db.acc_conv.dst_login, x.password, x.email
233from src_db.acc_conv, src_db.accounts x where x.login = src_db.acc_conv.src_login;
234
235SELECT 'accounts ok.';
236INSERT INTO dst_db.characters SELECT * FROM src_db.characters; 
237SELECT 'characters ok.';
238INSERT INTO dst_db.items SELECT * FROM src_db.items; 
239SELECT 'items ok.';
240INSERT INTO dst_db.clan_data SELECT * FROM src_db.clan_data; 
241SELECT 'clan_data ok.';
242INSERT INTO dst_db.ally_data SELECT * FROM src_db.ally_data; 
243SELECT 'ally_data ok.';
244INSERT INTO dst_db.pets SELECT * FROM src_db.pets; 
245SELECT 'pets ok.';
246-- secondary tables
247INSERT INTO dst_db.character_quests SELECT * FROM src_db.character_quests;
248SELECT 'character_quests ok.';
249INSERT INTO dst_db.character_friends SELECT * FROM src_db.character_friends;
250SELECT 'character_friends ok.';
251INSERT INTO dst_db.character_hennas SELECT * FROM src_db.character_hennas;
252SELECT 'character_hennas ok.';
253INSERT INTO dst_db.character_recipebook SELECT * FROM src_db.character_recipebook;
254SELECT 'character_recipebook ok.';
255INSERT INTO dst_db.character_shortcuts SELECT * FROM src_db.character_shortcuts;
256SELECT 'character_shortcuts ok.';
257INSERT INTO dst_db.character_macroses SELECT * FROM src_db.character_macroses;
258SELECT 'character_macroses ok.';
259INSERT INTO dst_db.character_skills SELECT * FROM src_db.character_skills;
260SELECT 'character_skills ok.';
261INSERT INTO dst_db.character_skills_save SELECT * FROM src_db.character_skills_save;
262SELECT 'character_skills_save ok.';
263INSERT INTO dst_db.character_subclasses SELECT * FROM src_db.character_subclasses;
264SELECT 'character_subclasses ok.';
265INSERT INTO dst_db.character_variables SELECT * FROM src_db.character_variables;
266SELECT 'character_variables ok.';
267INSERT INTO dst_db.items_duration SELECT * FROM src_db.items_duration;
268SELECT 'items_duration ok.';
269INSERT INTO dst_db.items_options SELECT * FROM src_db.items_options;
270SELECT 'items_options ok.';
271INSERT INTO dst_db.items_period SELECT * FROM src_db.items_period;
272SELECT 'items_period ok.';
273INSERT INTO dst_db.items_variation SELECT * FROM src_db.items_variation;
274SELECT 'items_variation ok.';
275INSERT INTO dst_db.siege_clans SELECT * FROM src_db.siege_clans;
276SELECT 'siege_clans ok.';
277INSERT INTO dst_db.clan_subpledges SELECT * FROM src_db.clan_subpledges; 
278SELECT 'clan_subpledges ok.';
279INSERT INTO dst_db.clan_privs SELECT * FROM src_db.clan_privs; 
280SELECT 'clan_privs ok.';
281INSERT INTO dst_db.clan_skills SELECT * FROM src_db.clan_skills; 
282SELECT 'clan_skills ok.';
283drop FUNCTION if exists `lip_ex_TrunLoginWithPfx`;