Skip to content

Instantly share code, notes, and snippets.

@karlbunch

karlbunch/characters_335a_to_434_convert_currency.sql

Created March 22, 2017 12:02
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/d345d6d0ba55cefa12412f2648e64874 to your computer and use it in GitHub Desktop.
Save karlbunch/d345d6d0ba55cefa12412f2648e64874 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
characters_335a_to_434_convert_currency.sql
-- BEGIN CURRENCY CONVERSION
-- 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;
SET @CURRENCY_TYPE_HONOR_POINTS = 392;
SET @CURRENCY_TYPE_JUSTICE_POINTS = 395;
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
;
DROP TABLE IF EXISTS temp_token_to_justice
;
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 + u.moneyDelta) AS `newMoney`, u.moneyDelta, u.newHonor, u.newJustice
FROM characters c, temp_characters_updates u
WHERE c.guid = u.guid
ORDER BY 7 DESC
LIMIT 20
;
DROP TABLE IF EXISTS `character_currency`
;
CREATE TABLE `character_currency` (
`guid` int(10) unsigned NOT NULL,
`currency` smallint(5) unsigned NOT NULL,
`total_count` int(10) unsigned NOT NULL,
`week_count` int(10) unsigned NOT NULL,
PRIMARY KEY (`guid`,`currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
START TRANSACTION
;
INSERT INTO `character_currency`
SELECT u.guid, @CURRENCY_TYPE_HONOR_POINTS, u.newHonor, 0
FROM temp_characters_updates u
WHERE u.newHonor > 0
;
INSERT INTO `character_currency`
SELECT u.guid, @CURRENCY_TYPE_JUSTICE_POINTS, u.newJustice, 0
FROM temp_characters_updates u
WHERE u.newHonor > 0
;
UPDATE characters c, temp_characters_updates u
SET c.money = c.money + u.moneyDelta
WHERE c.guid = u.guid
;
DELETE FROM character_inventory
WHERE item IN (SELECT item_guid FROM temp_currency_tokens)
;
DELETE FROM item_instance
WHERE guid IN (SELECT item_guid FROM temp_currency_tokens)
;
COMMIT
;
DROP TABLE temp_token_to_money
;
DROP TABLE temp_token_to_honor
;
DROP TABLE temp_token_to_justice
;
DROP TABLE temp_all_tokens
;
DROP TABLE temp_currency_tokens
;
DROP TABLE temp_characters_updates
;
-- END CURRENCY CONVERSION
Raw
run.out
2:[KARL]:base> 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
--------------
--------------
SET @CURRENCY_TYPE_HONOR_POINTS = 392
--------------
--------------
SET @CURRENCY_TYPE_JUSTICE_POINTS = 395
--------------
--------------
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)
--------------
--------------
DROP TABLE IF EXISTS temp_token_to_justice
--------------
--------------
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 + u.moneyDelta) AS `newMoney`, u.moneyDelta, u.newHonor, u.newJustice
FROM characters c, temp_characters_updates u
WHERE c.guid = u.guid
ORDER BY 7 DESC
LIMIT 20
--------------
+--------+--------------+------------------+-------------+--------------+----+------------+------------+----------+------------+
| guid | name | totalHonorPoints | arenaPoints | currentMoney | => | newMoney | moneyDelta | newHonor | newJustice |
+--------+--------------+------------------+-------------+--------------+----+------------+------------+----------+------------+
| 2163 | Vanyavanae | 13580 | 3035 | 2509257117 | => | 2932318152 | 423061035 | 4000 | 4000 |
| 63361 | Daren | 14403 | 4552 | 2352112699 | => | 2578421889 | 226309190 | 4000 | 4000 |
| 6633 | Averia | 8292 | 2067 | 2325579481 | => | 2528497886 | 202918405 | 4000 | 4000 |
| 60451 | Cabritilla | 8979 | 1680 | 2287749193 | => | 2493704678 | 205955485 | 4000 | 4000 |
| 10890 | Oilaskoa | 14415 | 561 | 2254850869 | => | 2438745494 | 183894625 | 4000 | 4000 |
| 3790 | Ladetos | 18912 | 286 | 2258063908 | => | 2397920893 | 139856985 | 4000 | 4000 |
| 3010 | Egotor | 27350 | 369 | 2252610786 | => | 2389366736 | 136755950 | 4000 | 4000 |
| 31892 | Isleromanolt | 14252 | 1992 | 2226856653 | => | 2325645073 | 98788420 | 4000 | 4000 |
| 99595 | Ckyra | 73553 | 1305 | 2209485076 | => | 2312201761 | 102716685 | 4000 | 4000 |
| 2774 | Antar | 7770 | 1992 | 2209456988 | => | 2309060523 | 99603535 | 4000 | 4000 |
| 3855 | Velvet | 13418 | 0 | 2210689061 | => | 2303938446 | 93249385 | 4000 | 4000 |
| 140750 | Hosher | 859 | 936 | 2138934299 | => | 2183080349 | 44146050 | 4000 | 4000 |
| 102341 | Arrowsong | 71668 | 10000 | 2118542960 | => | 2125043000 | 6500040 | 4000 | 817 |
| 105310 | Melaniec | 15962 | 425 | 2082182075 | => | 2084198940 | 2016865 | 4000 | 170 |
| 10923 | Nelo | 73213 | 3994 | 2063452797 | => | 2079883007 | 16430210 | 4000 | 4000 |
| 63485 | Mgorcuatro | 47204 | 1211 | 2034753819 | => | 2042910669 | 8156850 | 2587 | 2360 |
| 61499 | Revan | 7968 | 2133 | 1980937912 | => | 1985319797 | 4381885 | 4000 | 767 |
| 124213 | Fantakill | 75000 | 1255 | 1845043026 | => | 1856168146 | 11125120 | 4000 | 3476 |
| 89761 | Ekatombe | 2268 | 0 | 1795982372 | => | 1795982372 | 0 | 54 | 201 |
| 119567 | Juarezknight | 75000 | 5608 | 1571152112 | => | 1692207382 | 121055270 | 4000 | 4000 |
+--------+--------------+------------------+-------------+--------------+----+------------+------------+----------+------------+
--------------
DROP TABLE IF EXISTS `character_currency`
--------------
--------------
CREATE TABLE `character_currency` (
`guid` int(10) unsigned NOT NULL,
`currency` smallint(5) unsigned NOT NULL,
`total_count` int(10) unsigned NOT NULL,
`week_count` int(10) unsigned NOT NULL,
PRIMARY KEY (`guid`,`currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------
--------------
START TRANSACTION
--------------
--------------
INSERT INTO `character_currency`
SELECT u.guid, @CURRENCY_TYPE_HONOR_POINTS, u.newHonor, 0
FROM temp_characters_updates u
WHERE u.newHonor > 0
--------------
--------------
INSERT INTO `character_currency`
SELECT u.guid, @CURRENCY_TYPE_JUSTICE_POINTS, u.newJustice, 0
FROM temp_characters_updates u
WHERE u.newHonor > 0
--------------
--------------
UPDATE characters c, temp_characters_updates u
SET c.money = c.money + u.moneyDelta
WHERE c.guid = u.guid
--------------
--------------
DELETE FROM character_inventory
WHERE item IN (SELECT item_guid FROM temp_currency_tokens)
--------------
--------------
DELETE FROM item_instance
WHERE guid IN (SELECT item_guid FROM temp_currency_tokens)
--------------
--------------
COMMIT
--------------
--------------
DROP TABLE temp_token_to_money
--------------
--------------
DROP TABLE temp_token_to_honor
--------------
--------------
DROP TABLE temp_token_to_justice
--------------
--------------
DROP TABLE temp_all_tokens
--------------
--------------
DROP TABLE temp_currency_tokens
--------------
--------------
DROP TABLE temp_characters_updates
--------------
real 0m50.053s
user 0m0.014s
sys 0m0.006s
2:[KARL]:base> exit
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment