API tools faq
paste
Guest User

Untitled

a guest
Jun 23rd, 2018
104
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.04 KB | None | 0 0
raw download clone embed print report
  1. --
  2. -- CONVERSIONE DB REALMD
  3. TRUNCATE TABLE trinity_realmd.account;
  4. INSERT IGNORE INTO trinity_realmd.account (id,username,sha_pass_hash,sessionkey,v,s,email,joindate,last_ip,failed_logins,locked,last_login,expansion,mutetime,locale) SELECT id,username,sha_pass_hash,sessionkey,v,s,email,joindate,last_ip,failed_logins,locked,last_login,expansion,mutetime,locale FROM tmprealmd.account;
  5. TRUNCATE TABLE trinity_realmd.realmcharacters;
  6. INSERT IGNORE INTO trinity_realmd.realmcharacters SELECT * FROM tmprealmd.realmcharacters;
  7. TRUNCATE TABLE trinity_realmd.account_access;
  8. INSERT IGNORE INTO trinity_realmd.account_access SELECT id,gmlevel,active_realm_id FROM tmprealmd.account;
  9.  
  10. --
  11. -- CONVERSIONE DB CHAR
  12. TRUNCATE TABLE account_data;
  13. INSERT IGNORE INTO account_data SELECT * FROM characters_335.account_data;
  14. TRUNCATE TABLE arena_team;
  15. INSERT IGNORE INTO arena_team (arenateamid, name, captainguid, type, rating, seasongames, seasonwins, weekgames, weekwins, rank, backgroundColor, emblemstyle, emblemcolor, borderstyle, bordercolor) SELECT a.arenateamid, a.name, a.captainguid, a.type, b.rating, b.games_season, b.wins_season, b.games_week, b.wins_week, b.rank, a.backgroundColor, a.emblemstyle, a.emblemcolor, a.borderstyle, a.bordercolor FROM characters_335.arena_team a, characters_335.arena_team_stats b WHERE a.arenateamid = b.arenateamid;
  16. TRUNCATE TABLE arena_team_member;
  17. INSERT IGNORE INTO arena_team_member (arenateamId, guid, weekgames, weekwins, seasongames, seasonwins, personalrating) SELECT arenateamid, guid, played_week, wons_week, played_season, wons_season, personal_rating FROM characters_335.arena_team_member;
  18. TRUNCATE TABLE auctionhouse;
  19. INSERT IGNORE INTO auctionhouse (id, auctioneerguid, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit) SELECT id, 8661, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit FROM characters_335.auction;
  20. TRUNCATE TABLE character_account_data;
  21. INSERT IGNORE INTO character_account_data SELECT * FROM characters_335.character_account_data;
  22. TRUNCATE TABLE character_achievement;
  23. INSERT IGNORE INTO character_achievement SELECT * FROM characters_335.character_achievement;
  24. TRUNCATE TABLE character_achievement_progress;
  25. INSERT IGNORE INTO character_achievement_progress SELECT * FROM characters_335.character_achievement_progress;
  26. TRUNCATE TABLE character_action;
  27. INSERT IGNORE INTO character_action SELECT * FROM characters_335.character_action;
  28. TRUNCATE TABLE character_battleground_data;
  29. INSERT IGNORE INTO character_battleground_data (guid, instanceid, team, joinx, joiny, joinz, joino, joinmapid, taxistart, taxiend, mountspell) SELECT guid, instance_id, team, join_x, join_y, join_z, join_o, join_map, taxi_start, taxi_end, mount_spell FROM characters_335.character_battleground_data;
  30. TRUNCATE TABLE character_battleground_random;
  31. INSERT IGNORE INTO character_battleground_random SELECT * FROM characters_335.character_battleground_random;
  32. TRUNCATE TABLE character_equipmentsets;
  33. INSERT IGNORE INTO character_equipmentsets (guid, setguid, setindex, name, iconname, item0, item1, item2, item3, item4, item5, item6, item7, item8, item9, item10, item11, item12, item13, item14, item15, item16, item17, item18) SELECT guid, setguid, setindex, name, iconname, item0, item1, item2, item3, item4, item5, item6, item7, item8, item9, item10, item11, item12, item13, item14, item15, item16, item17, item18 FROM characters_335.character_equipmentsets;
  34. TRUNCATE TABLE character_gifts;
  35. INSERT IGNORE INTO character_gifts SELECT * FROM characters_335.character_gifts;
  36. TRUNCATE TABLE character_glyphs;
  37. INSERT IGNORE INTO character_glyphs (guid, spec) SELECT guid, spec FROM characters_335.character_glyphs GROUP BY guid, spec;
  38. UPDATE character_glyphs a SET glyph1 = (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 0) WHERE EXISTS (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 0);
  39. UPDATE character_glyphs a SET glyph2 = (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 1) WHERE EXISTS (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 1);
  40. UPDATE character_glyphs a SET glyph3 = (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 2) WHERE EXISTS (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 2);
  41. UPDATE character_glyphs a SET glyph4 = (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 3) WHERE EXISTS (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 3);
  42. UPDATE character_glyphs a SET glyph5 = (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 4) WHERE EXISTS (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 4);
  43. UPDATE character_glyphs a SET glyph6 = (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 5) WHERE EXISTS (SELECT glyph FROM characters_335.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 5);
  44. TRUNCATE TABLE character_homebind;
  45. INSERT IGNORE INTO character_homebind SELECT * FROM characters_335.character_homebind;
  46. -- DIVIDIAMO CHARACTER_INVENTORY IN PIU QUERY POICHE E' TROPPO GRANDE
  47. ALTER TABLE character_inventory ENGINE=MyISAM;
  48. TRUNCATE TABLE character_inventory;
  49. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 0, 250000;
  50. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 250000, 500000;
  51. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 500000, 750000;
  52. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 750000, 1000000;
  53. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 1250000, 1500000;
  54. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 1500000, 1750000;
  55. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 1750000, 2000000;
  56. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 2250000, 2500000;
  57. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 2500000, 2750000;
  58. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 2750000, 3000000;
  59. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 3000000, 3250000;
  60. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 3250000, 3500000;
  61. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 3500000, 3750000;
  62. INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM characters_335.character_inventory LIMIT 3750000, 4000000;
  63. ALTER TABLE character_inventory ENGINE=InnoDB;
  64. TRUNCATE TABLE character_pet;
  65. INSERT IGNORE INTO character_pet (id, entry, owner, modelid, createdbyspell, pettype, level, exp, reactstate, name, renamed, slot, curhealth, curmana, curhappiness, savetime, abdata) SELECT id, entry, owner, modelid, createdbyspell, pettype, level, exp, reactstate, name, renamed, slot, curhealth, curmana, curhappiness, savetime, abdata FROM characters_335.character_pet;
  66. ALTER TABLE character_queststatus ENGINE=MyISAM;
  67. TRUNCATE TABLE character_queststatus;
  68. INSERT IGNORE INTO character_queststatus (guid, quest, status, explored, timer, mobcount1, mobcount2, mobcount3, mobcount4, itemcount1, itemcount2, itemcount3, itemcount4, playercount)
  69. SELECT guid, quest, status, explored, timer, mobcount1, mobcount2, mobcount3, mobcount4, itemcount1, itemcount2, itemcount3, itemcount4, 0 FROM characters_335.character_queststatus;
  70. ALTER TABLE character_queststatus ENGINE=InnoDB;
  71. TRUNCATE TABLE character_queststatus_daily;
  72. INSERT IGNORE INTO character_queststatus_daily (guid,quest) SELECT guid, quest FROM characters_335.character_queststatus_daily;
  73. TRUNCATE TABLE character_queststatus_weekly;
  74. INSERT IGNORE INTO character_queststatus_weekly SELECT * FROM characters_335.character_queststatus_weekly;
  75. TRUNCATE TABLE character_reputation;
  76. INSERT IGNORE INTO character_reputation SELECT * FROM characters_335.character_reputation;
  77. TRUNCATE TABLE character_skills;
  78. INSERT IGNORE INTO character_skills SELECT * FROM characters_335.character_skills;
  79. TRUNCATE TABLE character_social;
  80. INSERT IGNORE INTO character_social SELECT * FROM characters_335.character_social;
  81. TRUNCATE TABLE character_spell;
  82. INSERT IGNORE INTO character_spell SELECT * FROM characters_335.character_spell;
  83. TRUNCATE TABLE character_spell_cooldown;
  84. INSERT IGNORE INTO character_spell_cooldown SELECT * FROM characters_335.character_spell_cooldown;
  85. TRUNCATE TABLE character_stats;
  86. INSERT IGNORE INTO character_stats (guid, maxhealth, maxpower1, maxpower2, maxpower3, maxpower4, maxpower5, maxpower6, maxpower7, strength, agility, stamina, intellect, spirit, armor, resholy, resfire, resnature, resfrost, resshadow, resarcane, blockpct, dodgepct, parrypct, critpct, rangedcritpct, spellcritpct, attackpower, rangedattackpower, spellpower, resilience) SELECT guid, maxhealth, maxpower1, maxpower2, maxpower3, maxpower4, maxpower5, maxpower6, maxpower7, strength, agility, stamina, intellect, spirit, armor, resholy, resfire, resnature, resfrost, resshadow, resarcane, blockpct, dodgepct, parrypct, critpct, rangedcritpct, spellcritpct, attackpower, rangedattackpower, spellpower, resilience FROM characters_335.character_stats;
  87. TRUNCATE TABLE guild;
  88. INSERT IGNORE INTO guild SELECT * FROM characters_335.guild;
  89. TRUNCATE TABLE guild_bank_eventlog;
  90. INSERT IGNORE INTO guild_bank_eventlog SELECT * FROM characters_335.guild_bank_eventlog;
  91. TRUNCATE TABLE guild_bank_item;
  92. INSERT IGNORE INTO guild_bank_item (guildid, tabid, slotid, item_guid) SELECT guildid, tabid, slotid, item_guid FROM characters_335.guild_bank_item;
  93. TRUNCATE TABLE guild_bank_right;
  94. INSERT IGNORE INTO guild_bank_right SELECT * FROM characters_335.guild_bank_right;
  95. TRUNCATE TABLE guild_bank_tab;
  96. INSERT IGNORE INTO guild_bank_tab SELECT * FROM characters_335.guild_bank_tab;
  97. TRUNCATE TABLE guild_eventlog;
  98. INSERT IGNORE INTO guild_eventlog SELECT * FROM characters_335.guild_eventlog;
  99. TRUNCATE TABLE guild_member;
  100. INSERT IGNORE INTO guild_member SELECT * FROM characters_335.guild_member;
  101. TRUNCATE TABLE guild_rank;
  102. INSERT IGNORE INTO guild_rank SELECT * FROM characters_335.guild_rank;
  103. ALTER TABLE item_instance ENGINE=MyISAM;
  104. TRUNCATE TABLE item_instance;
  105. ALTER TABLE item_instance ADD COLUMN tmp longtext default '' AFTER text;
  106. INSERT IGNORE INTO item_instance (guid, owner_guid, text, tmp) SELECT guid, owner_guid, text, data FROM characters_335.item_instance;
  107. UPDATE item_instance ii, characters_335.auction ah SET ii.itemEntry = ah.item_template WHERE ii.guid = ah.itemguid;
  108. UPDATE item_instance ii, characters_335.character_inventory ci SET ii.itemEntry = ci.item_template WHERE ii.guid = ci.item;
  109. UPDATE item_instance ii, characters_335.guild_bank_item gbi SET ii.itemEntry = gbi.item_entry WHERE ii.guid = gbi.item_guid;
  110. UPDATE item_instance ii, characters_335.mail_items mi SET ii.itemEntry = mi.item_template WHERE ii.guid = mi.item_guid;
  111. DELIMITER ||
  112. DROP FUNCTION IF EXISTS uint32toint32||
  113. CREATE FUNCTION uint32toint32(input INT(10) UNSIGNED) RETURNS INT(11) SIGNED DETERMINISTIC
  114. BEGIN
  115. RETURN input;
  116. END||
  117. DELIMITER ;
  118. UPDATE item_instance ii SET creatorGuid = SUBSTRING(tmp, length(SUBSTRING_INDEX(tmp,' ',10))+2, length(SUBSTRING_INDEX(tmp,' ',10+1))-length(SUBSTRING_INDEX(tmp,' ',10))-1),
  119. giftCreatorGuid = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',12))+2,length(SUBSTRING_INDEX(tmp,' ',12+1))-length(SUBSTRING_INDEX(tmp,' ',12))-1),
  120. count = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',14))+2,length(SUBSTRING_INDEX(tmp,' ',14+1))-length(SUBSTRING_INDEX(tmp,' ',14))-1),
  121. duration = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',15))+2,length(SUBSTRING_INDEX(tmp,' ',15+1))-length(SUBSTRING_INDEX(tmp,' ',15))-1),
  122. charges = CONCAT_WS(' ',uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',16))+2,length(SUBSTRING_INDEX(tmp,' ',16+1))-length(SUBSTRING_INDEX(tmp,' ',16))-1)),uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',17))+2,length(SUBSTRING_INDEX(tmp,' ',17+1))-length(SUBSTRING_INDEX(tmp,' ',17))-1)),uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',18))+2,length(SUBSTRING_INDEX(tmp,' ',18+1))-length(SUBSTRING_INDEX(tmp,' ',18))-1)),uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',19))+2,length(SUBSTRING_INDEX(tmp,' ',19+1))-length(SUBSTRING_INDEX(tmp,' ',19))-1)),uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',20))+2,length(SUBSTRING_INDEX(tmp,' ',20+1))-length(SUBSTRING_INDEX(tmp,' ',20))-1))),
  123. flags = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',21))+2,length(SUBSTRING_INDEX(tmp,' ',21+1))-length(SUBSTRING_INDEX(tmp,' ',21))-1),
  124. enchantments = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',22))+2,length(SUBSTRING_INDEX(tmp,' ',57+1))-length(SUBSTRING_INDEX(tmp,' ',22))-1),
  125. randomPropertyId = uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',59))+2,length(SUBSTRING_INDEX(tmp,' ',59+1))-length(SUBSTRING_INDEX(tmp,' ',59))-1)),
  126. durability = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',60))+2,length(SUBSTRING_INDEX(tmp,' ',60+1))-length(SUBSTRING_INDEX(tmp,' ',60))-1),
  127. playedTime = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',62))+2,length(SUBSTRING_INDEX(tmp,' ',62+1))-length(SUBSTRING_INDEX(tmp,' ',62))-1);
  128. DROP FUNCTION IF EXISTS uint32toint32;
  129. UPDATE item_instance ii SET flags = flags &~0x8 WHERE
  130. SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',3))+2,length(SUBSTRING_INDEX(tmp,' ',3+1))-length(SUBSTRING_INDEX(tmp,' ',3))-1)NOT IN (5043,5044,17302,17305,17308,21831);
  131. ALTER TABLE item_instance DROP COLUMN tmp;
  132. SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
  133. UPDATE item_instance SET flags = (flags & @allowedFlags);
  134. ALTER TABLE item_instance ENGINE=InnoDB;
  135. TRUNCATE TABLE mail;
  136. INSERT IGNORE INTO mail SELECT * FROM characters_335.mail;
  137. TRUNCATE TABLE mail_items;
  138. INSERT IGNORE INTO mail_items (mail_id, item_guid, receiver) SELECT mail_id, item_guid, receiver FROM characters_335.mail_items;
  139. TRUNCATE TABLE pet_spell;
  140. INSERT IGNORE INTO pet_spell (guid, spell, active) SELECT guid, spell, active FROM characters_335.pet_spell GROUP BY guid, spell;
  141. TRUNCATE TABLE pet_spell_cooldown;
  142. INSERT IGNORE INTO pet_spell_cooldown SELECT * FROM characters_335.pet_spell_cooldown;
  143. TRUNCATE TABLE characters;
  144. INSERT IGNORE INTO characters (guid, account, name, race, class, gender, level, xp, money, playerbytes, playerbytes2, playerflags, position_x, position_y, position_z, map, instance_id, instance_mode_mask, orientation, taximask, online, cinematic, totaltime, leveltime, logout_time, is_logout_resting, rest_bonus, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login, zone, death_expire_time, taxi_path, arenapoints, totalhonorpoints, todayhonorpoints, yesterdayhonorpoints, totalkills, todaykills, yesterdaykills, chosentitle, knowncurrencies, watchedfaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, latency, speccount, activespec, exploredzones, equipmentcache, ammoid, knowntitles, actionbars, grantablelevels, deleteinfos_account, deleteinfos_name, deletedate) SELECT guid, account, name, race, class, gender, level, xp, money, playerbytes, playerbytes2, playerflags, position_x, position_y, position_z, map, 0, 0, orientation, taximask, online, cinematic, totaltime, leveltime, logout_time, is_logout_resting, rest_bonus, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login, zone, death_expire_time, taxi_path, arenapoints, totalhonorpoints, todayhonorpoints, yesterdayhonorpoints, totalkills, todaykills, yesterdaykills, chosentitle, knowncurrencies, watchedfaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, 0, speccount, activespec, exploredzones, equipmentcache, ammoid, knowntitles, actionbars, grantablelevels, deleteinfos_account, deleteinfos_name, deletedate FROM characters_335.characters;
  145. UPDATE characters SET at_login = at_login|4;
  146. --
  147.  
  148.  
  149. --
  150. -- INFINE AGGIORNIAMO IL REALMD CON IL NUMERO DEI PG PER OGNI ACCOUNT
  151. UPDATE trinity_realmd.realmcharacters a SET numchars = (SELECT COUNT(guid) FROM trinity_char.characters WHERE account = a.acctid);
  152. --
  153. -- FINE
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!