Skip to content

Instantly share code, notes, and snippets.

@karlbunch

karlbunch/characters_335a_to_434_convert_currency.sql

Created March 21, 2017 11:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save karlbunch/fd812ac9d3ea9a19127cdf78967b9bdc to your computer and use it in GitHub Desktop.
Save karlbunch/fd812ac9d3ea9a19127cdf78967b9bdc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
characters_335a_to_434_convert_currency.sql
-- Convert character tokens/currencies from 3.x to 4.x
-- http://web.archive.org/web/20100918101232/http://forums.worldofwarcraft.com/thread.html?topicId=26850245012&sid=1&pageNo=10
SET @HONOR_RATE = 0.024;
SET @ARENA_RATE = 0.85;
SET @HONOR_HARDCAP = 4000;
SET @HONOR_TO_MONEY = 35;
SET @JUSTICE_HARDCAP = 4000;
SET @JUSTICE_TO_MONEY = 20000;
SET @MAX_MONEY_AMOUNT = 9999999999;
DROP TABLE IF EXISTS temp_token_to_money
;
CREATE TEMPORARY TABLE temp_token_to_money (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, moneyRate INT(10) UNSIGNED)
;
INSERT INTO temp_token_to_money VALUES
(40752, 55000), -- Emblem of Heroism
(40735, 55000), -- Emblem of Valor
(45624, 55000), -- Emblem of Conquest
(29434, 18330) -- Badge of Justice
;
DROP TABLE IF EXISTS temp_token_to_honor
;
CREATE TEMPORARY TABLE temp_token_to_honor (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, honorRate FLOAT(6))
;
INSERT INTO temp_token_to_honor VALUES
(20558, 2.976), -- Warsong Gulch Mark of Honor
(20559, 2.976), -- Arathi Basin Mark of Honor
(20560, 2.976), -- Alterac Valley Mark of Honor
(29024, 2.976), -- Eye of the Storm Mark of Honor
(42425, 2.976), -- Strand of the Ancients Mark of Honor
(43589, 19.08), -- Wintergrasp Mark of Honor
(43228, 1.600), -- Stone Keeper's Shard
(37836, 3.000), -- Venture Coin
(28558, 1.000) -- Spirit Shard
;
CREATE TEMPORARY TABLE temp_token_to_justice (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, justiceRate FLOAT(6))
;
INSERT INTO temp_token_to_justice VALUES
(47241, 2.75), -- Emblem of Triumph
(49426, 2.75) -- Emblem of Frost
;
DROP TABLE IF EXISTS temp_all_tokens
;
CREATE TEMPORARY TABLE temp_all_tokens (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY)
SELECT t1.itemEntry FROM temp_token_to_money t1
UNION ALL
SELECT t2.itemEntry FROM temp_token_to_honor t2
UNION ALL
SELECT t3.itemEntry FROM temp_token_to_justice t3
;
DROP TABLE IF EXISTS temp_currency_tokens
;
CREATE TEMPORARY TABLE temp_currency_tokens (
`item_guid` int(10) unsigned NOT NULL DEFAULT '0',
`itemEntry` mediumint(8) unsigned NOT NULL DEFAULT '0',
`item_count` int(10) unsigned NOT NULL DEFAULT '1',
`owner_guid` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`item_guid`),
KEY `idx_owner_guid` (`owner_guid`),
KEY `idx_itemEntry` (`itemEntry`)
)
;
INSERT INTO temp_currency_tokens
SELECT ii.`guid`, ii.`itemEntry`, ii.`count`, ii.`owner_guid`
FROM item_instance ii, temp_all_tokens r
WHERE r.itemEntry = ii.itemEntry
;
DROP TABLE IF EXISTS temp_characters_updates
;
CREATE TEMPORARY TABLE temp_characters_updates (guid INT(10) UNSIGNED NOT NULL PRIMARY KEY, moneyDelta INT(10) UNSIGNED, newHonor INT(10) UNSIGNED, newJustice INT(10) UNSIGNED)
SELECT guid, money AS moneyDelta, 0 AS newHonor, 0 AS newJustice
FROM characters
;
UPDATE temp_characters_updates u
SET u.moneyDelta = (SELECT IFNULL(SUM(r.moneyRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_money r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
;
UPDATE temp_characters_updates u
SET u.newHonor = (SELECT IFNULL(SUM(r.honorRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_honor r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
;
UPDATE temp_characters_updates u
SET u.newJustice = (SELECT IFNULL(SUM(r.justiceRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_justice r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
;
UPDATE temp_characters_updates u, characters c
SET u.newHonor = u.newHonor + IFNULL(c.totalHonorPoints * @HONOR_RATE, 0) + IFNULL(c.arenaPoints * @ARENA_RATE, 0)
WHERE c.guid = u.guid
;
UPDATE temp_characters_updates u
SET u.moneyDelta = u.moneyDelta + (u.newHonor - @HONOR_HARDCAP) * @HONOR_TO_MONEY, u.newHonor = @HONOR_HARDCAP
WHERE u.newHonor > @HONOR_HARDCAP
;
UPDATE temp_characters_updates u
SET u.moneyDelta = u.moneyDelta + (u.newJustice - @JUSTICE_HARDCAP) * @JUSTICE_TO_MONEY, u.newJustice = @JUSTICE_HARDCAP
WHERE u.newJustice > @JUSTICE_HARDCAP
;
SELECT c.guid, c.name, c.totalHonorPoints, c.arenaPoints, c.money AS `currentMoney`, '=>', (c.money + cu.moneyDelta) AS `newMoney`, cu.moneyDelta, cu.newHonor, cu.newJustice
FROM characters c, temp_characters_updates cu
WHERE c.guid = cu.guid
ORDER BY 7 DESC
LIMIT 20
;
Raw
run.out
time mysql aokromes -ve 'source characters_335a_to_434_convert_currency.sql'
--------------
SET @HONOR_RATE = 0.024
--------------
--------------
SET @ARENA_RATE = 0.85
--------------
--------------
SET @HONOR_HARDCAP = 4000
--------------
--------------
SET @HONOR_TO_MONEY = 35
--------------
--------------
SET @JUSTICE_HARDCAP = 4000
--------------
--------------
SET @JUSTICE_TO_MONEY = 20000
--------------
--------------
SET @MAX_MONEY_AMOUNT = 9999999999
--------------
--------------
DROP TABLE IF EXISTS temp_token_to_money
--------------
--------------
CREATE TEMPORARY TABLE temp_token_to_money (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, moneyRate INT(10) UNSIGNED)
--------------
--------------
INSERT INTO temp_token_to_money VALUES
(40752, 55000),
(40735, 55000),
(45624, 55000),
(29434, 18330)
--------------
--------------
DROP TABLE IF EXISTS temp_token_to_honor
--------------
--------------
CREATE TEMPORARY TABLE temp_token_to_honor (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, honorRate FLOAT(6))
--------------
--------------
INSERT INTO temp_token_to_honor VALUES
(20558, 2.976),
(20559, 2.976),
(20560, 2.976),
(29024, 2.976),
(42425, 2.976),
(43589, 19.08),
(43228, 1.600),
(37836, 3.000),
(28558, 1.000)
--------------
--------------
CREATE TEMPORARY TABLE temp_token_to_justice (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, justiceRate FLOAT(6))
--------------
--------------
INSERT INTO temp_token_to_justice VALUES
(47241, 2.75),
(49426, 2.75)
--------------
--------------
DROP TABLE IF EXISTS temp_all_tokens
--------------
--------------
CREATE TEMPORARY TABLE temp_all_tokens (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY)
SELECT t1.itemEntry FROM temp_token_to_money t1
UNION ALL
SELECT t2.itemEntry FROM temp_token_to_honor t2
UNION ALL
SELECT t3.itemEntry FROM temp_token_to_justice t3
--------------
--------------
DROP TABLE IF EXISTS temp_currency_tokens
--------------
--------------
CREATE TEMPORARY TABLE temp_currency_tokens (
`item_guid` int(10) unsigned NOT NULL DEFAULT '0',
`itemEntry` mediumint(8) unsigned NOT NULL DEFAULT '0',
`item_count` int(10) unsigned NOT NULL DEFAULT '1',
`owner_guid` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`item_guid`),
KEY `idx_owner_guid` (`owner_guid`),
KEY `idx_itemEntry` (`itemEntry`)
)
--------------
--------------
INSERT INTO temp_currency_tokens
SELECT ii.`guid`, ii.`itemEntry`, ii.`count`, ii.`owner_guid`
FROM item_instance ii, temp_all_tokens r
WHERE r.itemEntry = ii.itemEntry
--------------
--------------
DROP TABLE IF EXISTS temp_characters_updates
--------------
--------------
CREATE TEMPORARY TABLE temp_characters_updates (guid INT(10) UNSIGNED NOT NULL PRIMARY KEY, moneyDelta INT(10) UNSIGNED, newHonor INT(10) UNSIGNED, newJustice INT(10) UNSIGNED)
SELECT guid, money AS moneyDelta, 0 AS newHonor, 0 AS newJustice
FROM characters
--------------
--------------
UPDATE temp_characters_updates u
SET u.moneyDelta = (SELECT IFNULL(SUM(r.moneyRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_money r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
--------------
--------------
UPDATE temp_characters_updates u
SET u.newHonor = (SELECT IFNULL(SUM(r.honorRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_honor r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
--------------
--------------
UPDATE temp_characters_updates u
SET u.newJustice = (SELECT IFNULL(SUM(r.justiceRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_justice r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
--------------
--------------
UPDATE temp_characters_updates u, characters c
SET u.newHonor = u.newHonor + IFNULL(c.totalHonorPoints * @HONOR_RATE, 0) + IFNULL(c.arenaPoints * @ARENA_RATE, 0)
WHERE c.guid = u.guid
--------------
--------------
UPDATE temp_characters_updates u
SET u.moneyDelta = u.moneyDelta + (u.newHonor - @HONOR_HARDCAP) * @HONOR_TO_MONEY, u.newHonor = @HONOR_HARDCAP
WHERE u.newHonor > @HONOR_HARDCAP
--------------
--------------
UPDATE temp_characters_updates u
SET u.moneyDelta = u.moneyDelta + (u.newJustice - @JUSTICE_HARDCAP) * @JUSTICE_TO_MONEY, u.newJustice = @JUSTICE_HARDCAP
WHERE u.newJustice > @JUSTICE_HARDCAP
--------------
--------------
SELECT c.guid, c.name, c.totalHonorPoints, c.arenaPoints, c.money AS `currentMoney`, '=>', (c.money + cu.moneyDelta) AS `newMoney`, cu.moneyDelta, cu.newHonor, cu.newJustice
FROM characters c, temp_characters_updates cu
WHERE c.guid = cu.guid
ORDER BY 7 DESC
LIMIT 20
--------------
+--------+--------------+------------------+-------------+--------------+----+------------+------------+----------+------------+
| guid | name | totalHonorPoints | arenaPoints | currentMoney | => | newMoney | moneyDelta | newHonor | newJustice |
+--------+--------------+------------------+-------------+--------------+----+------------+------------+----------+------------+
| 2163 | Vanyavanae | 13580 | 3035 | 2086196082 | => | 2509257117 | 423061035 | 4000 | 4000 |
| 63361 | Daren | 14403 | 4552 | 2125803509 | => | 2352112699 | 226309190 | 4000 | 4000 |
| 6633 | Averia | 8292 | 2067 | 2122661076 | => | 2325579481 | 202918405 | 4000 | 4000 |
| 60451 | Cabritilla | 8979 | 1680 | 2081793708 | => | 2287749193 | 205955485 | 4000 | 4000 |
| 3790 | Ladetos | 18912 | 286 | 2118206923 | => | 2258063908 | 139856985 | 4000 | 4000 |
| 10890 | Oilaskoa | 14415 | 561 | 2070956244 | => | 2254850869 | 183894625 | 4000 | 4000 |
| 3010 | Egotor | 27350 | 369 | 2115854836 | => | 2252610786 | 136755950 | 4000 | 4000 |
| 31892 | Isleromanolt | 14252 | 1992 | 2128068233 | => | 2226856653 | 98788420 | 4000 | 4000 |
| 3855 | Velvet | 13418 | 0 | 2117439676 | => | 2210689061 | 93249385 | 4000 | 4000 |
| 99595 | Ckyra | 73553 | 1305 | 2106768391 | => | 2209485076 | 102716685 | 4000 | 4000 |
| 2774 | Antar | 7770 | 1992 | 2109853453 | => | 2209456988 | 99603535 | 4000 | 4000 |
| 140750 | Hosher | 859 | 936 | 2094788249 | => | 2138934299 | 44146050 | 4000 | 4000 |
| 102341 | Arrowsong | 71668 | 10000 | 2112042920 | => | 2118542960 | 6500040 | 4000 | 817 |
| 105310 | Melaniec | 15962 | 425 | 2080165210 | => | 2082182075 | 2016865 | 4000 | 170 |
| 10923 | Nelo | 73213 | 3994 | 2047022587 | => | 2063452797 | 16430210 | 4000 | 4000 |
| 63485 | Mgorcuatro | 47204 | 1211 | 2026596969 | => | 2034753819 | 8156850 | 2587 | 2360 |
| 61499 | Revan | 7968 | 2133 | 1976556027 | => | 1980937912 | 4381885 | 4000 | 767 |
| 124213 | Fantakill | 75000 | 1255 | 1833917906 | => | 1845043026 | 11125120 | 4000 | 3476 |
| 89761 | Ekatombe | 2268 | 0 | 1795982372 | => | 1795982372 | 0 | 54 | 201 |
| 77655 | Juno | 11213 | 3166 | 1674302864 | => | 1674669464 | 366600 | 3045 | 66 |
+--------+--------------+------------------+-------------+--------------+----+------------+------------+----------+------------+
real 0m13.240s
user 0m0.013s
sys 0m0.006s
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment