|
|
|
 |
|
|
 |
|
 |
 |
|
 |
 |
|
 |
|
| Development::Tools 3rd Party Tools for EQEMu (DB management tools, front ends, etc...) |
 |
|
 |
12-18-2010, 03:38 PM
|
|
Fire Beetle
|
|
Join Date: Dec 2010
Location: N/A
Posts: 5
|
|
General EQEmu SQL Utility Scripts (plat exploit finder, etc)
From chatting with a server admin, I found that there was no actual scripts to let you handle - from a server level - general data mining into what your users were doing. Using a particular tool that exists you could, of course, look up information about a specific account - but what about those accounts you are not paying attention to? What about those cases where a person may hold 60% of your server networth (created due to plat / tradeskill exploits) on a level 26 shadowknight that is logged out currently in Misty Thicket?
These sql scripts should allow you to get an eye into what is going on in the server as well as wipe out characters plat (and replace with a given amount) if you so choose.
You can get the scripts from http://www.mediafire.com/file/u62jq0...y SQL v1.7.zip but I am also including the individual files in code blocks here. We have had too many good utilities / scripts die out due to a file host removing old files.
These scripts have been tested out and should work without issue on your server. Be aware that the two scripts marked "HeavyWeight" are expensive queries that should be ran during off peak hours to guarantee low server impact. The update script should be read in its entirety before executing.
NOTE - these scripts are based off of the character platinum slot being found in offset 4720 in the character_ profile blob. I do not see this changing in the immediate future but, if it does, you can simply update the scripts and do a global find / replace for 4720 and replace it with the new platinum offset.
Improvements to be made: I will be writing a new script to handle automated suspending / renaming of characters found using names that are inappropriate for your server. I am currently testing a script that will be used to wipe out characters whose accounts have not bothered logging in in years and meet other criteria (data cleanup). It would be -nice- to move a lot of this logic into a view that will read the profile blob as it will make the scripts less complex. I am not planning on doing this in the next few days so if someone would like to take a stab at this, be my guest.
--
*********************************
* EQEMU SQL Script Explanations *
*********************************
1. PlatSearching_DetailedAnalysis_Offset4720_v1.7.sql - This will provide you with all the liquid platinum a player has access to. This includes plat on their cursor and their shared bank. This will also include a characters tradeskill level. You will also see the account id and account login to easy group players together if they are on the same account. Run this script all at once.
2. PlatSearching_SimplePlatByIPAddress_Offset4720_v1. 7.sql - This will provide you with all the platinum a player has access to throughout all their characters and accounts based on their ip address. If a person logged into five accounts on your server with ip address "255.255.255.255" and these accounts have a sum total of "250,000" plat, you will see a row for IP "255.255.255.255" and IP_ADDRESS_NET_WORTH_IN_PLAT "250000". Important note here - a users IP address can change over time. As such, you may see the same plat amount counted multiple times for different ip address - this is not a bug, this is by design. Example: If a player Bob has 10 accounts which each having about 1 million plat, and Bobs IP address changes every week - assuming Bob logs in at least once a week under a new ip address into all his accounts, you would see a total of 10 rows with different ip addresses listing ~10 million plat. This does not mean that you have that much unique money in the wild - please keep this in mind. It should be true, however, that for a given IP address the plat available is accurate - you simply cannot create new aggregations based on this data. Run this script all at once.
3. PlatSearching_SimplePlatAnalysisWithItemValues_Off set4720_v1.7.sql - This will provide you with all the platinum a player has access to including platinum tied up in tradeable items. This will only count the merchant price of tradeable items so if a user is hording some very lucrative tradeskill items, this may not be caught. This will help with users who do not like the idea of someone watching their plat totals and, instead, try to hide plat in stacks of blue diamonds or the like. It is important to note that items in the shared bank will affect the total plat shown here for all users that are on that account. This means that if you see 5 people with 2 million each, it could simply be that the user has 2 million plat in items in the shared bank accessible across 5 of their characters on that account. If you see a better way to handle this, please let me know. Run this script all at once. Note - this is a heavy weight query and may take several minutes (or longer) to run depending on your character count. It is adviseable to run this during off peak hours (or during maintenance) to guarantee that you will not impact players. Adding an appropriate index may help speed this up but this has not be done yet.
4. PlatUpdate_CharacterMoneyUpdateScript_Offset4720_v 1.7.sql - This will update a given users plat to the new plat amount that you put in. You should run the first section of the file to create the persisted table only one time and then remove it from your update script. Each section (except the first section and the last two sections in the file) should be ran one at a time. You simply put the character id of the user you wish to update into the file as well as their new platinum amount. The default script you see modifies character 2 to have a new plat total of 8,675,309. The section that actually modifies a users data is listed at the bottom surrounded by warnings. The section immediately prior to this will show you what their old plat was, what their new plat would be so you can check to make sure this is what you desire. If you wish to abort at this time, you can simply run the last section in the script to delete these records from your backup table. If you wish to revert, you will need to run the reversion section at the bottom of the script. To be frank, though, it is safer to simply update a user twice than it is to revert. Do -not- delete records from the backup table as this will be invaluable if you need to see how things were at a later point. VERY IMPORTANT NOTE - this needs to be ran either when the server is down for maintenance or when a user is logged out. Updating a user while they are logged in as unpredictable results. From testing, these results appear to be relegated to a user not being fully updated but this is not a guarantee. DO NOT RUN THIS SCRIPT ALL AT ONCE. READ.
5. TradeskillSearching_FindUsersPossiblyAbusingRecipe s_v1.7.sql - finds the users who have created a single recipe combine more than __ times. The script defaults this number to 200 but you can modify this yourself. You can also remove some noise recipes from this script by uncommenting the "and recipe.name not in ..." like and adding in the recipe name you wish to avoid seeing. This script will output the IP Address, AccountID, CharacterID, CharacterName, RecipeName, and the CntMade. NOTE - IP Address was provided in this script to allow you to perform some ad hoc data mining - this will, however, cause some characters to show up multiple times due to the fact that a characters ip address may have changed. If this is alarming, you can simply remove the join to the "account_ip" table and comment out the "aip.IP as IP_ADDRESS" line as well as removing the comma on the second line. Run this script all at once.
6. TradeskillSearching_FindUsersPossiblyAbusingRecipe s_ByIPAddress_v1.7.sql - same as 6 except we are aggregating based on the IP Address. This will show, based on a give ip address, who has performed the most combines for a single recipe. As does 6, this removes anyone who has not made enough recipe combines to matter (based on 200 by default). Also, you are still able to remove recipes from showing up if you have too much noise in your result set. Run this script all at once.
7. Utility_BadCharNameFinder_v1.7.sql - General SQL utility to see what users have names that you consider inappropriate. This will limit the search to accounts that have not been suspended or banned. You can modify the list it uses by updating the temporary table at the top of the page. Run this script all at once.
8. PlatSearching_EconomySize_v1.7.sql - Just gives you information on the entire size of your economy. Total liquid plat, Total locked in plat (sellable items), and Total Combined Character Net Worth. This now takes into account items in the shared bank being counted multiple times and will, now, account for these when viewing the total. Note - this is a heavy weight query and may take several minutes (or longer) to run depending on your character count. It is adviseable to run this during off peak hours (or during maintenance) to guarantee that you will not impact players. Adding an appropriate index may help speed this up but this has not be done yet.
Version 1.7 Files:
1 - LightWeight - PlatSearching_DetailedAnalysis_Offset4720_v1.7.sql
Code:
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
, SKILL_FISHING
, SKILL_MKPOISON
, SKILL_TINKERING
, SKILL_RESEARCH
, SKILL_ALCHEMY
, SKILL_BAKING
, SKILL_TAILORING
, SKILL_BLACKSMITHING
, SKILL_FLETCHING
, SKILL_BREWING
, SKILL_BEGGING
, SKILL_JEWELRY
, SKILL_POTTERY
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
/* Fishing = 55 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (55*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (55*4) + 1)) as SKILL_FISHING
/* Make Poison = 56 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (56*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (56*4) + 1)) as SKILL_MKPOISON
/* Tinkering = 57 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (57*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (57*4) + 1)) as SKILL_TINKERING
/* Research = 58 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (58*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (58*4) + 1)) as SKILL_RESEARCH
/* Alchemy = 59 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (59*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (59*4) + 1)) as SKILL_ALCHEMY
/* Baking = 60 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (60*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (60*4) + 1)) as SKILL_BAKING
/* Tailoring = 61 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (61*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (61*4) + 1)) as SKILL_TAILORING
/* BlackSmithing = 63 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (63*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (63*4) + 1)) as SKILL_BLACKSMITHING
/* Fletching = 64 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (64*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (64*4) + 1)) as SKILL_FLETCHING
/* Brewing = 65 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (65*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (65*4) + 1)) as SKILL_BREWING
/* Begging = 67*/
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (67*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (67*4) + 1)) as SKILL_BEGGING
/* Skills JC = 68*/
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (68*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (68*4) + 1)) as SKILL_JEWELRY
/* Pottery = 69 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (69*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (69*4) + 1)) as SKILL_POTTERY
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
order by CHARACTER_NET_WORTH_IN_PLAT DESC
LIMIT 100;
2 - LightWeight - PlatSearching_SimplePlatByIPAddress_Offset4720_v1. 7.sql
Code:
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT IP_ADDRESS, SUM(CHARACTER_NET_WORTH_IN_PLAT) AS IP_ADDRESS_NET_WORTH_IN_PLAT
FROM (
SELECT
accIp.IP AS IP_ADDRESS
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
INNER JOIN account_ip accIp
ON accIp.accid = IndividualCharacterSlots.ACCOUNT_ID
) Details
GROUP BY IP_ADDRESS
ORDER BY IP_ADDRESS_NET_WORTH_IN_PLAT DESC
LIMIT 100;
3 - HeavyWeight - PlatSearching_SimplePlatAnalysisWithItemValues_Off set4720_v1.7.sql
Code:
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Creation of temp tables... Doing a lot of work with temp tables
-- as the query optimizer is having issues with the complexity of the query (and the estimated)
-- rows returned. Putting into temp table to persist these concrete values.
-- In a perfect world, it would not be necessary and would add overhead - as it is, it should
-- speed this up considerably.
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS(
ACCOUNT_ID INT
, BagSlotIn2500 INT
, BagSlotIn2501 INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEMS(
ACCOUNT_ID INT
, ITEM_ID INT
, CNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT(
ACCOUNT_ID INT
, SHARED_BANK_ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT(
CHARACTER_ID INT
, ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_TOTAL_PURE_PLAT(
ACCOUNT_ID INT
, ACCOUNT_LOGIN VARCHAR(200)
, CHARACTER_ID INT
, CHARACTER_NAME VARCHAR(200)
, CHARACTER_NET_WORTH_IN_PURE_PLATINUM INT
);
-- Pivoting shared bank bank slot data.
INSERT INTO ACCOUNT_SHARED_BANK_BAG_SLOTS
SELECT account_id, MAX(BagSlotIn2500) AS BagSlotIn2500, MAX(BagSlotIn2501) AS BagSlotIn2501
FROM
(
SELECT
account_id
, CASE WHEN slotid = 2500 THEN IsBag ELSE NULL END AS BagSlotIn2500
, CASE WHEN slotid = 2501 THEN IsBag ELSE NULL END AS BagSlotIn2501
FROM
(
SELECT
acct.id AS account_id
, bagSlots.slotid
, CASE WHEN sb.acctid IS NULL OR i.bagslots < 1 THEN 0 ELSE 1 END AS IsBag
FROM
account acct
CROSS JOIN (SELECT 2500 AS slotid UNION SELECT 2501 ) bagSlots
LEFT JOIN sharedBank sb
ON sb.acctid = acct.id
AND sb.slotid = bagslots.slotid
LEFT JOIN items i
ON i.id = sb.itemid
) unPivotedData
) AS preparingForThePivot
GROUP BY account_id
;
INSERT INTO ACCOUNT_SHARED_BANK_ITEMS
SELECT
account_id, bankitem, SUM(bankitemcharges) AS totalCntItems
FROM
(
SELECT
bankBagSlots.account_id
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN itemid
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN itemid
WHEN slotid BETWEEN 2500 AND 2501 THEN itemid
ELSE NULL
END AS BankItem
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN charges
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN charges
WHEN slotid BETWEEN 2500 AND 2501 THEN charges
ELSE NULL
END AS BankItemCharges
FROM
ACCOUNT_SHARED_BANK_BAG_SLOTS bankBagSlots
INNER JOIN sharedbank sb
ON sb.acctid = bankBagSlots.account_id
) SharedBankItems
WHERE bankitem IS NOT NULL
GROUP BY account_id, bankitem;
INSERT INTO ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT
SELECT
bankItems.account_id
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN bankitems.cnt
ELSE 1
END) * 0.94)/1000
FROM
ACCOUNT_SHARED_BANK_ITEMS bankItems
INNER JOIN items i
ON i.id = bankitems.item_id
GROUP BY bankItems.account_id
;
INSERT INTO CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT
SELECT
groupedCharInv.CHARACTER_ID
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN groupedCharInv.cnt
ELSE 1
END) * 0.94)/1000
FROM
(
SELECT
inv.charid AS CHARACTER_ID
, inv.itemid AS ITEM_ID
, SUM(inv.charges) AS cnt
FROM
inventory inv
GROUP BY
inv.charid, inv.itemid
) groupedCharInv
INNER JOIN items i
ON i.id = groupedCharInv.ITEM_ID
GROUP BY groupedCharInv.CHARACTER_ID;
INSERT INTO CHARACTER_TOTAL_PURE_PLAT
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
;
-- Notes -
-- CHARACTER_TOTAL_PURE_PLAT has a guaranteed 1:1 for each character.
-- ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each account but will have at most 1.
-- CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each character but will have at most 1.
-- Final Aggregation
SELECT
charPurePlat.ACCOUNT_ID
, charPurePlat.ACCOUNT_LOGIN
, charPurePlat.CHARACTER_ID
, charPurePlat.CHARACTER_NAME
, COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_PLATINUM,0) AS CHARACTER_NET_WORTH_IN_PURE_PLATINUM
, COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0) AS ITEM_VALUE_IN_PLAT
, COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT, 0) AS SHARED_BANK_ITEM_VALUE_IN_PLAT
, COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_PLATINUM, 0)
+ COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0)
+ COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT, 0) AS TotalCombinedCharacterNetWorth
FROM
CHARACTER_TOTAL_PURE_PLAT charPurePlat
LEFT JOIN CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT charNonSharedItemPlat
ON charNonSharedItemPlat.character_ID = charPurePlat.character_id
LEFT JOIN ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT acctShared
ON acctShared.account_id = charPurePlat.account_id
ORDER BY 8 DESC
LIMIT 100
;
DROP TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEMS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_TOTAL_PURE_PLAT;
;
4 - LightWeight - PlatUpdate_CharacterMoneyUpdateScript_Offset4720_v 1.7.sql
NOTE - reformatted to not wrap on the screen. You may wish to download the zip file to get the correctly formatted text as it may be easier to understand.
Code:
/*
Create a "Permanent" backup table of any accounts that have their money wiped.
When you run the "Update Money" procedure, you will be taking -all- accounts
that are in this table where the character_id -AND- original_profile match
and you will be updating it to the new profile. This lets you simply add
new new rows to this table whenever you wish to grant a boon or wipe out
someones plat.
This table should probably not be dropped.
*/
/*
RUN ONCE, KEEP THIS TABLE!
*/
CREATE TABLE character_backup_pre_money_wipe_semi_permanent_table(
CHARACTER_ID INT
, ORIGINAL_PROFILE BLOB
, ORIGINAL_MONEY_VALUE INT
, NEW_PROFILE BLOB
, NEW_MONEY_VALUE INT
, DATE_ADDED DATETIME
, DATE_EXECUTED DATETIME
)
;
/*
Insert the character_ids and the money you wish this character to have.
Manual process currently - find the character_ids from another script
and you can insert the rows here.
*/
INSERT INTO character_backup_pre_money_wipe_semi_permanent_table(CHARACTER_ID, NEW_MONEY_VALUE, DATE_ADDED)
SELECT 2, 8675309, NOW()
;
/*
!!! DO NOT EDIT BELOW THIS LINE !!!
*/
/*
Lets get the original profile and note what their original money was.
NOTE - this simply updates our -backup- table.
*/
UPDATE character_backup_pre_money_wipe_semi_permanent_table, (
SELECT
preMoneyBackup.CHARACTER_ID AS ID
, PROFILE
, FLOOR(MONEY_NOTES.CHARACTER_NET_WORTH_IN_PLAT) AS CHARACTER_NET_WORTH_IN_PLAT
FROM
character_backup_pre_money_wipe_semi_permanent_table preMoneyBackup
INNER JOIN character_ c
ON c.id = preMoneyBackup.CHARACTER_ID
INNER JOIN (
SELECT ID
, FLOOR(PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000) AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) d
) MONEY_NOTES
ON c.ID = MONEY_NOTES.ID
) derivedTable
SET ORIGINAL_PROFILE = derivedTable.PROFILE, ORIGINAL_MONEY_VALUE = derivedTable.CHARACTER_NET_WORTH_IN_PLAT
WHERE
character_backup_pre_money_wipe_semi_permanent_table.CHARACTER_ID = derivedTable.ID
AND ORIGINAL_PROFILE IS NULL
AND DATE_EXECUTED IS NULL
;
/*
For those backup entries that are missing a new profile value, go ahead and create it.
NOTE - this simply updates our -backup- table.
*/
UPDATE
character_backup_pre_money_wipe_semi_permanent_table
, (
SELECT d.CHARACTER_ID, CONCAT(CHAR(CHAR_ONE), CHAR(CHAR_TWO), CHAR(CHAR_THREE), CHAR(CHAR_FOUR)) AS newPlat
FROM (
SELECT character_backup_pre_money_wipe_semi_permanent_table.CHARACTER_ID
,
NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) / (256*256)) * 256 * 256) -
FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) * 256 * 256))/256) * 256 AS CHAR_ONE
, FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) * 256 * 256))/256) AS CHAR_TWO
, FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) AS CHAR_THREE
, FLOOR(NEW_MONEY_VALUE/(256*256*256)) AS CHAR_FOUR
FROM
character_backup_pre_money_wipe_semi_permanent_table
WHERE
NEW_PROFILE IS NULL
AND character_backup_pre_money_wipe_semi_permanent_table.DATE_EXECUTED IS NULL
) d
) newCharacterMoneyValues
SET NEW_PROFILE = INSERT(
(SELECT ORIGINAL_PROFILE FROM (SELECT * FROM character_) AS X WHERE X.ID = character_backup_pre_money_wipe_semi_permanent_table.CHARACTER_ID)
,(4720+1)
,52
,RPAD(newPlat,52,CHAR(0))
)
WHERE
character_backup_pre_money_wipe_semi_permanent_table.CHARACTER_ID = newCharacterMoneyValues.CHARACTER_ID
AND
character_backup_pre_money_wipe_semi_permanent_table.NEW_PROFILE IS NULL
AND character_backup_pre_money_wipe_semi_permanent_table.DATE_EXECUTED IS NULL
;
/*
Lets go ahead and query and make sure that this change makes sense.
Look at the users old networth, the new networth (calculated based
off of the new profile so what you see here is what -will- be going
into the database.
*/
SELECT ID AS CHARACTER_ID
, DATE_ADDED AS DATE_THIS_WIPE_REQUEST_WAS_ADDED
, ORIGINAL_MONEY_VALUE AS ORIGINAL_CHARACTER_NETWORTH
, FLOOR(PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000) AS NEW_CHARACTER_NETWORTH
FROM
(
SELECT
ID
, DATE_ADDED
, ORIGINAL_MONEY_VALUE
/* Player Platinum! */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM
character_backup_pre_money_wipe_semi_permanent_table preWipeBackup
INNER JOIN character_ c
ON c.ID = preWipeBackup.CHARACTER_ID
AND preWipeBackup.ORIGINAL_PROFILE = c.PROFILE
AND preWipeBackup.DATE_EXECUTED IS NULL
) MONEY_NOTES
;
/*
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
This is the only part of the script that actually updates the character table.
It will update the character profile to be the new character profile we generated
for those accounts that match based on character id -and- that have the same
old profile that we put into the account. This is just another way to make sure
that we do not have unintended consequences (wiping out a profile based on
old data that is no longer valid)
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
*/
UPDATE character_, character_backup_pre_money_wipe_semi_permanent_table, account
SET
character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_table.NEW_PROFILE
, character_backup_pre_money_wipe_semi_permanent_table.DATE_EXECUTED = NOW()
, account.sharedplat = 0
WHERE
character_.ID = character_backup_pre_money_wipe_semi_permanent_table.CHARACTER_ID
AND character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_table.ORIGINAL_PROFILE
AND character_backup_pre_money_wipe_semi_permanent_table.DATE_EXECUTED IS NULL
and account.id = character_.account_id
;
/*
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT
You can roll back to previous value by using the ORIGINAL_PROFILE value
in the character_backup_pre_money_wipe_semi_permanent_table table.
The next part of the script will simply revert out -all- changes that
is in this wipeout table. If you wish to be more granular, you will
need to hand code a snippet that touches this table. Its a minor change.
If you wish to keep the users profile as it is and simply move them back to the older
money, it will require more consideration.
ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
*/
UPDATE character_, character_backup_pre_money_wipe_semi_permanent_table
SET
character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_table.ORIGINAL_PROFILE
, character_backup_pre_money_wipe_semi_permanent_table.DATE_EXECUTED = NULL
WHERE
character_.ID = character_backup_pre_money_wipe_semi_permanent_table.CHARACTER_ID
/*
You may need to remove the following condition if the user has logged into his account
- logging into account would be enough to change the profile blob.
*/
AND character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_table.NEW_PROFILE
;
/*
Once you roll back someones update, it may be wise to delete any rows from the backup table that have a null execution date
*/
DELETE FROM character_backup_pre_money_wipe_semi_permanent_table WHERE DATE_EXECUTED IS NULL;
;
5 - LightWeight - TradeskillSearching_FindUsersPossiblyAbusingRecipe s_v1.7.sql
Code:
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Filter out noise - people who did not make at least 200 of
-- an item are not interesting to me.
-- Optionally filter out recipes like HoH stones.
-- and recipe.name not in ('carved shadewood recurve bow (hemp)', 'fish fries', 'fake recipe name')
;
SELECT
aip.IP AS IP_ADDRESS
, acc.id AS ACCOUNT_ID
, acc.NAME AS ACCOUNT_LOGIN
, c.id AS CHARACTER_ID
, c.NAME AS CHARACTER_NAME
, recipe.NAME AS RECIPE_NAME
, characterCreatedRecipes.madecount AS CNT_MADE
FROM
char_recipe_list characterCreatedRecipes
INNER JOIN character_ c
ON c.id = characterCreatedRecipes.char_id
INNER JOIN tradeskill_recipe recipe
ON recipe.id = characterCreatedRecipes.recipe_id
INNER JOIN account acc
ON acc.id = c.account_id
INNER JOIN account_ip aip
ON aip.accid = c.account_id
WHERE
characterCreatedRecipes.madecount > 200
ORDER BY
characterCreatedRecipes.madecount DESC
6 - LightWeight - TradeskillSearching_FindUsersPossiblyAbusingRecipe s_ByIPAddress_v1.7.sql
Code:
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Filter out noise - people who did not make at least 200 of
-- an item are not interesting to me.
-- Optionally filter out noise recipes like HoH stones.
-- and recipe.name not in ('carved shadewood recurve bow (hemp)', 'fish fries', 'fake recipe name')
;
SELECT
IP_ADDRESS
, recipe.NAME AS RECIPE_NAME
, CNT_MADE
FROM(
SELECT
aip.IP AS IP_ADDRESS
, characterCreatedRecipes.recipe_id AS RECIPE_ID
, SUM(characterCreatedRecipes.madecount) AS CNT_MADE
FROM
char_recipe_list characterCreatedRecipes
INNER JOIN character_ c
ON c.id = characterCreatedRecipes.char_id
INNER JOIN account acc
ON acc.id = c.account_id
INNER JOIN account_ip aip
ON aip.accid = c.account_id
WHERE
characterCreatedRecipes.madecount > 200
GROUP BY aip.IP, characterCreatedRecipes.recipe_id
) IpCreatedRecipes
INNER JOIN tradeskill_recipe recipe
ON recipe.id = IpCreatedRecipes.RECIPE_ID
ORDER BY
3 DESC
7 - LightWeight - Utility_BadCharNameFinder_v1.7.sql
Code:
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TEMPORARY TABLE CHARACTER_NAME_FINDER(
WORD_IN_NAME VARCHAR(50)
);
INSERT INTO CHARACTER_NAME_FINDER
SELECT 'nipple'
UNION SELECT 'shit'
UNION SELECT 'fuck'
UNION SELECT 'cumming'
UNION SELECT 'poon'
UNION SELECT 'herpes'
UNION SELECT 'yourmom'
UNION SELECT 'your_mom'
UNION SELECT 'dick'
UNION SELECT 'toker'
UNION SELECT 'potbowl'
UNION SELECT 'anus'
UNION SELECT 'balls'
UNION SELECT 'bitch'
UNION SELECT 'nigg'
UNION SELECT 'abort'
UNION SELECT 'pussy'
UNION SELECT 'sex'
UNION SELECT 'suck'
UNION SELECT 'damn'
UNION SELECT 'ass'
UNION SELECT 'cunt'
UNION SELECT 'cock'
UNION SELECT 'penis'
UNION SELECT 'whore'
UNION SELECT 'piss'
;
/* DO NOT EDIT BELOW THIS LINE */
SELECT *
FROM (
SELECT
c.id AS CHARACTER_ID
, TRIM(REPLACE(CAST(SUBSTRING(PROFILE, (4+1), 64) AS CHAR(64)),'\0','')) AS FirstName
, TRIM(REPLACE(CAST(SUBSTRING(PROFILE, (68+1), 32) AS CHAR(64)),'\0','')) AS LastName
, LEVEL
FROM
character_ c
INNER JOIN account acc
ON acc.id = c.account_id
WHERE
acc.STATUS >= 0
) CharacterNames
INNER JOIN CHARACTER_NAME_FINDER filter
ON
LOCATE(WORD_IN_NAME, FirstName) > 0
OR
LOCATE(WORD_IN_NAME, LastName) > 0
ORDER BY CHARACTER_ID desc
LIMIT 100
;
DROP TABLE CHARACTER_NAME_FINDER;
8 - HeavyWeight - PlatSearching_EconomySize_v1.7.sql
Code:
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Creation of temp tables... Doing a lot of work with temp tables
-- as the query optimizer is having issues with the complexity of the query (and the estimated)
-- rows returned. Putting into temp table to persist these concrete values.
-- In a perfect world, it would not be necessary and would add overhead - as it is, it should
-- speed this up considerably.
CREATE TABLE ACCOUNT_CHARACTER_COUNT(
ACCOUNT_ID INT
, NUMBER_OF_CHARACTERS_ON_ACCOUNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS(
ACCOUNT_ID INT
, BagSlotIn2500 INT
, BagSlotIn2501 INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEMS(
ACCOUNT_ID INT
, ITEM_ID INT
, CNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT(
ACCOUNT_ID INT
, SHARED_BANK_ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT(
CHARACTER_ID INT
, ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_TOTAL_PURE_PLAT(
ACCOUNT_ID INT
, ACCOUNT_LOGIN VARCHAR(200)
, CHARACTER_ID INT
, CHARACTER_NAME VARCHAR(200)
, CHARACTER_NET_WORTH_IN_PURE_PLATINUM INT
);
INSERT INTO ACCOUNT_CHARACTER_COUNT
SELECT
acc.id
, COUNT(DISTINCT c.id)
FROM
account acc
LEFT JOIN character_ c
ON acc.id = c.account_id
GROUP BY acc.id;
-- Pivoting shared bank bank slot data.
INSERT INTO ACCOUNT_SHARED_BANK_BAG_SLOTS
SELECT account_id, MAX(BagSlotIn2500) AS BagSlotIn2500, MAX(BagSlotIn2501) AS BagSlotIn2501
FROM
(
SELECT
account_id
, CASE WHEN slotid = 2500 THEN IsBag ELSE NULL END AS BagSlotIn2500
, CASE WHEN slotid = 2501 THEN IsBag ELSE NULL END AS BagSlotIn2501
FROM
(
SELECT
acct.id AS account_id
, bagSlots.slotid
, CASE WHEN sb.acctid IS NULL OR i.bagslots < 1 THEN 0 ELSE 1 END AS IsBag
FROM
account acct
CROSS JOIN (SELECT 2500 AS slotid UNION SELECT 2501 ) bagSlots
LEFT JOIN sharedBank sb
ON sb.acctid = acct.id
AND sb.slotid = bagslots.slotid
LEFT JOIN items i
ON i.id = sb.itemid
) unPivotedData
) AS preparingForThePivot
GROUP BY account_id
;
INSERT INTO ACCOUNT_SHARED_BANK_ITEMS
SELECT
account_id, bankitem, SUM(bankitemcharges) AS totalCntItems
FROM
(
SELECT
bankBagSlots.account_id
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN itemid
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN itemid
WHEN slotid BETWEEN 2500 AND 2501 THEN itemid
ELSE NULL
END AS BankItem
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN charges
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN charges
WHEN slotid BETWEEN 2500 AND 2501 THEN charges
ELSE NULL
END AS BankItemCharges
FROM
ACCOUNT_SHARED_BANK_BAG_SLOTS bankBagSlots
INNER JOIN sharedbank sb
ON sb.acctid = bankBagSlots.account_id
) SharedBankItems
WHERE bankitem IS NOT NULL
GROUP BY account_id, bankitem;
INSERT INTO ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT
SELECT
bankItems.account_id
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN bankitems.cnt
ELSE 1
END) * 0.94)/1000
FROM
ACCOUNT_SHARED_BANK_ITEMS bankItems
INNER JOIN items i
ON i.id = bankitems.item_id
GROUP BY bankItems.account_id
;
INSERT INTO CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT
SELECT
groupedCharInv.CHARACTER_ID
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN groupedCharInv.cnt
ELSE 1
END) * 0.94)/1000
FROM
(
SELECT
inv.charid AS CHARACTER_ID
, inv.itemid AS ITEM_ID
, SUM(inv.charges) AS cnt
FROM
inventory inv
GROUP BY
inv.charid, inv.itemid
) groupedCharInv
INNER JOIN items i
ON i.id = groupedCharInv.ITEM_ID
GROUP BY groupedCharInv.CHARACTER_ID;
INSERT INTO CHARACTER_TOTAL_PURE_PLAT
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
;
-- Notes -
-- CHARACTER_TOTAL_PURE_PLAT has a guaranteed 1:1 for each character.
-- ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each account but will have at most 1.
-- CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each character but will have at most 1.
-- Final Aggregation
SELECT
SUM(CAST(COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_PLATINUM,0) AS UNSIGNED)) AS TOTAL_LIQUID_PLATINUM
, SUM(CAST(
COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT,0) / (
CASE
WHEN acctCharCount.ACCOUNT_ID IS NULL then 1
WHEN acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT = 0 then 1
ELSE acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT
END)
AS UNSIGNED)) AS TOTAL_PLATINUM_LOCKED_IN_SHARED_BANK
, SUM(CAST(COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT,0) AS UNSIGNED)) AS TOTAL_PLATINUM_LOCKED_IN_ITEMS
, SUM(CAST( COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_PLATINUM, 0)
+ COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0)
+ COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT,0) / (
CASE
WHEN acctCharCount.ACCOUNT_ID IS NULL then 1
WHEN acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT = 0 then 1
ELSE acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT
END) AS UNSIGNED)) AS TotalCombinedWorldNetWorth
FROM
CHARACTER_TOTAL_PURE_PLAT charPurePlat
LEFT JOIN CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT charNonSharedItemPlat
ON charNonSharedItemPlat.character_ID = charPurePlat.character_id
LEFT JOIN ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT acctShared
ON acctShared.account_id = charPurePlat.account_id
LEFT JOIN ACCOUNT_CHARACTER_COUNT acctCharCount
ON acctCharCount.ACCOUNT_ID = acctShared.account_id
;
DROP TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEMS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_TOTAL_PURE_PLAT;
DROP TABLE ACCOUNT_CHARACTER_COUNT;
;
|
 |
|
 |
05-15-2011, 10:48 PM
|
|
Administrator
|
|
Join Date: Sep 2006
Posts: 1,348
|
|
You know no one ever replied to this but I was playing with it and these are really neat.
|
05-23-2011, 03:24 PM
|
|
Dragon
|
|
Join Date: May 2006
Location: Cincinnati, OH
Posts: 689
|
|
I was going through these a couple weeks ago after finding them in a search, but was hesitant to be the one to bump them after almost six months after the fact. Now that KLS did the dirty work...
These are really something. Though the server I run doesn't have much of an economy due to the small population, it really helped me see mistakes I'd made and where the players were taking advantage of those mistakes. There was definitely a big red flag raised when one of my players was sitting on 28k platinum at level 37.
Very elegantly written, nice work! Thanks for your contribution!
__________________
Yo mama so fat, if she moved past a black hole at high velocity it'd create a closed timelike curve.
|
05-23-2011, 10:47 PM
|
|
Discordant
|
|
Join Date: Dec 2005
Posts: 435
|
|
Yes it is some nice code. Shame the author created a new account to post it.
|
|
|
|
06-12-2011, 02:11 AM
|
|
Fire Beetle
|
|
Join Date: Dec 2010
Location: N/A
Posts: 5
|
|
Thanks for the kind words regarding the scripts.
Created a new account for this just because releasing these scripts under my usual account name would have created unnecessary drama. These scripts were created to help out a server admin that had several -billion- in duped plat sitting across many accounts. Plat was wiped, tears were shed, and people basically threw a fit. Best that this remain anonymous as I doubt those normalized in the plat wipe have become less pissed off after 6 months.
I had three more scripts I created after this post that I never got around to posting here.
Script 1 - "Reset tradeskill sell prices to one copper" - self explanatory. Even in the stock EQEmu database, there are a lot of exploitable tradeskill recipes. This is not hard to find if you query the database with this in mind. You can keep trying to find them all / fix them all but I dont know if I would spend that much time on it. I prefer a salt-the-earth approach to this sort of thing. This script will simply find all items that are the result of a tradeskill combine and set their sell price to zero. NOTE - this will also affect the sell price of DROPPED items that also can come from a tradeskill combine so keep this in mind. You are basically nerfing the ability to recoup any investment from any tradeskill items thus making the cost to up someones tradeskill exceptionally high. This has been tested on production servers to work exactly as described.
Script 2 - "Rename users with bad names" - This was just a script I created for kicks that found users with bad names and renamed them to an appropriate (and unique) fantasy character name. This has two parts - the first part that creates fantasy names and the second part that actually updates the project eq database to change the names. I tested these on my local server installation and it worked without issue but I would suggest validating this on a test database before running this in production as I have not had a production test run of this yet. This was probably the most pointless but also the most fun script I have had the pleasure in writing in quite some time.
As before, if anyone has any requests / suggestions / etc for sql queries / updates, please feel free to post them here. It is worth noting that you are welcome to distribute, modify, etc any of these scripts for any reason. Consider these available for redistribution under either the LGPL or BSD licenses (whichever you would prefer more).
MYSqlUpdate_ProjectEQDB_ResetTradeskillSellPricesT oOneCopper.sql
Code:
/* First a query to make sure the changes make sense... */
SELECT DISTINCT
i.NAME
, item_id
, PRICE AS OLD_PRICE
, 1 AS NEW_PRICE
FROM
tradeskill_recipe tr
INNER JOIN tradeskill_recipe_entries trEntries
ON tr.ID = trEntries.recipe_id
INNER JOIN items i
ON i.ID = trEntries.ITEM_ID
WHERE
(
successcount >= 1
OR
failcount >= 1
)
AND ISCONTAINER = 0
AND i.PRICE > 1
ORDER BY i.id ASC
LIMIT 100;
/* Backup any items that are about to be modified */
CREATE TABLE ItemBackup_BeforeTradeskillPriceChanges_20101205(
ITEM_ID INT
, OLD_PRICE INT
);
INSERT INTO ItemBackup_BeforeTradeskillPriceChanges_20101205(ITEM_ID, OLD_PRICE)
SELECT DISTINCT
item_id
, PRICE AS OLD_PRICE
FROM
tradeskill_recipe tr
INNER JOIN tradeskill_recipe_entries trEntries
ON tr.ID = trEntries.recipe_id
INNER JOIN items i
ON i.ID = trEntries.ITEM_ID
WHERE
(
successcount >= 1
OR
failcount >= 1
)
AND ISCONTAINER = 0
AND i.PRICE > 1;
/* Make changes - Force to use backup table to guarantee only changing items we have saved.*/
UPDATE ITEMS, ItemBackup_BeforeTradeskillPriceChanges_20101205
SET ITEMS.PRICE = 1
WHERE ITEMS.ID = ItemBackup_BeforeTradeskillPriceChanges_20101205.ITEM_ID;
/* Lets see if this did what it should have */
SELECT DISTINCT
i.NAME
, item_id
, PRICE AS OLD_PRICE
, 1 AS NEW_PRICE
FROM
tradeskill_recipe tr
INNER JOIN tradeskill_recipe_entries trEntries
ON tr.ID = trEntries.recipe_id
INNER JOIN items i
ON i.ID = trEntries.ITEM_ID
WHERE
(
successcount >= 1
OR
failcount >= 1
)
AND ISCONTAINER = 0
AND i.PRICE > 1
ORDER BY i.id ASC
LIMIT 100;
/* Lets make sure that not all prices were modified */
SELECT i.ID, i.NAME, i.PRICE
FROM
items i
WHERE
i.PRICE > 1
LIMIT 1000;
/* "Oh Crap, lets rollback! - You can use this portion to rollback to older prices." */
UPDATE ITEMS, ItemBackup_BeforeTradeskillPriceChanges_20101205
SET ITEMS.PRICE = ItemBackup_BeforeTradeskillPriceChanges_20101205.OLD_PRICE
WHERE ITEMS.ID = ItemBackup_BeforeTradeskillPriceChanges_20101205.ITEM_ID;
--- Fun Scripts ---
ModerateWeight - Utility_CreateRandomFantasyNameTable_v1.7.sql
Code:
/*
This script will create a table "Fantasy" names using the algorithm
found at http://www.dwheeler.com/totro.html. It will attempt
generate names based on alternating vowel + consonant based on
the consonant ordering. It will create names based on a minimum
of 4 syllables and a maximum of 10 syllables. If you wish to change
this you may need to copy / paste the update-the-temp-table section
a few more times to get the length desired.
Ordering is defined to be a bit vector having the following designation.
1 = Can be at ending
2 = Can be at beginning
3 = Can be in the middle
The PERMANENT table to be created is called "AutoGeneratedFantasyName".
This table will be truncated and repopulated with 20k fresh "random" names
each time this script is ran.
No change to ProjectEQ data is made by this script - it is simply a helper
for other scripts that actually do mutate ProjectEQ data.
*/
-- Permanent Table
CREATE TABLE IF NOT EXISTS AutoGeneratedFantasyName(
AutoGeneratedFantasyNameID INT
, RandomName VARCHAR(64)
);
CREATE TEMPORARY TABLE IF NOT EXISTS FantasyNameVowel(
Vowel VARCHAR(3)
);
CREATE TEMPORARY TABLE IF NOT EXISTS FantasyNameConsonant(
Consonant VARCHAR(3)
, Ordering INT
);
CREATE TEMPORARY TABLE IF NOT EXISTS FantasyNamesCreated(
RANDOM_BIT INT
, SYLLABLE_LENGTH INT
, CURRENT_LENGTH INT
, RANDOM_NAME VARCHAR(64)
);
DELETE FROM FantasyNameVowel;
DELETE FROM FantasyNameConsonant;
DELETE FROM FantasyNamesCreated;
DELETE FROM AutoGeneratedFantasyName;
-- 7 x AEIOU
INSERT INTO FantasyNameVowel VALUES('a');
INSERT INTO FantasyNameVowel VALUES('a');
INSERT INTO FantasyNameVowel VALUES('a');
INSERT INTO FantasyNameVowel VALUES('a');
INSERT INTO FantasyNameVowel VALUES('a');
INSERT INTO FantasyNameVowel VALUES('a');
INSERT INTO FantasyNameVowel VALUES('a');
INSERT INTO FantasyNameVowel VALUES('e');
INSERT INTO FantasyNameVowel VALUES('e');
INSERT INTO FantasyNameVowel VALUES('e');
INSERT INTO FantasyNameVowel VALUES('e');
INSERT INTO FantasyNameVowel VALUES('e');
INSERT INTO FantasyNameVowel VALUES('e');
INSERT INTO FantasyNameVowel VALUES('e');
INSERT INTO FantasyNameVowel VALUES('i');
INSERT INTO FantasyNameVowel VALUES('i');
INSERT INTO FantasyNameVowel VALUES('i');
INSERT INTO FantasyNameVowel VALUES('i');
INSERT INTO FantasyNameVowel VALUES('i');
INSERT INTO FantasyNameVowel VALUES('i');
INSERT INTO FantasyNameVowel VALUES('i');
INSERT INTO FantasyNameVowel VALUES('o');
INSERT INTO FantasyNameVowel VALUES('o');
INSERT INTO FantasyNameVowel VALUES('o');
INSERT INTO FantasyNameVowel VALUES('o');
INSERT INTO FantasyNameVowel VALUES('o');
INSERT INTO FantasyNameVowel VALUES('o');
INSERT INTO FantasyNameVowel VALUES('o');
INSERT INTO FantasyNameVowel VALUES('u');
INSERT INTO FantasyNameVowel VALUES('u');
INSERT INTO FantasyNameVowel VALUES('u');
INSERT INTO FantasyNameVowel VALUES('u');
INSERT INTO FantasyNameVowel VALUES('u');
INSERT INTO FantasyNameVowel VALUES('u');
INSERT INTO FantasyNameVowel VALUES('u');
-- Less common multivowels
INSERT INTO FantasyNameVowel VALUES('ae');
INSERT INTO FantasyNameVowel VALUES('ai');
INSERT INTO FantasyNameVowel VALUES('ao');
INSERT INTO FantasyNameVowel VALUES('au');
INSERT INTO FantasyNameVowel VALUES('aa');
INSERT INTO FantasyNameVowel VALUES('ea');
INSERT INTO FantasyNameVowel VALUES('eo');
INSERT INTO FantasyNameVowel VALUES('eu');
INSERT INTO FantasyNameVowel VALUES('ee');
INSERT INTO FantasyNameVowel VALUES('ia');
INSERT INTO FantasyNameVowel VALUES('io');
INSERT INTO FantasyNameVowel VALUES('iu');
INSERT INTO FantasyNameVowel VALUES('ii');
INSERT INTO FantasyNameVowel VALUES('oa');
INSERT INTO FantasyNameVowel VALUES('oe');
INSERT INTO FantasyNameVowel VALUES('oi');
INSERT INTO FantasyNameVowel VALUES('ou');
INSERT INTO FantasyNameVowel VALUES('oo');
INSERT INTO FantasyNameVowel VALUES('eau');
INSERT INTO FantasyNameVowel VALUES('y');
-- Consonants.
INSERT INTO FantasyNameConsonant VALUES('b',7);
INSERT INTO FantasyNameConsonant VALUES('c',7);
INSERT INTO FantasyNameConsonant VALUES('d',7);
INSERT INTO FantasyNameConsonant VALUES('f',7);
INSERT INTO FantasyNameConsonant VALUES('g',7);
INSERT INTO FantasyNameConsonant VALUES('h',7);
INSERT INTO FantasyNameConsonant VALUES('j',7);
INSERT INTO FantasyNameConsonant VALUES('k',7);
INSERT INTO FantasyNameConsonant VALUES('l',7);
INSERT INTO FantasyNameConsonant VALUES('m',7);
INSERT INTO FantasyNameConsonant VALUES('n',7);
INSERT INTO FantasyNameConsonant VALUES('p',7);
INSERT INTO FantasyNameConsonant VALUES('qu',6);
INSERT INTO FantasyNameConsonant VALUES('r',7);
INSERT INTO FantasyNameConsonant VALUES('s',7);
INSERT INTO FantasyNameConsonant VALUES('t',7);
INSERT INTO FantasyNameConsonant VALUES('v',7);
INSERT INTO FantasyNameConsonant VALUES('w',7);
INSERT INTO FantasyNameConsonant VALUES('x',7);
INSERT INTO FantasyNameConsonant VALUES('y',7);
INSERT INTO FantasyNameConsonant VALUES('z',7);
-- Blends
INSERT INTO FantasyNameConsonant VALUES('sc',7);
INSERT INTO FantasyNameConsonant VALUES('ch',7);
INSERT INTO FantasyNameConsonant VALUES('gh',7);
INSERT INTO FantasyNameConsonant VALUES('ph',7);
INSERT INTO FantasyNameConsonant VALUES('sh',7);
INSERT INTO FantasyNameConsonant VALUES('th',7);
INSERT INTO FantasyNameConsonant VALUES('wh',6);
INSERT INTO FantasyNameConsonant VALUES('ck',5);
INSERT INTO FantasyNameConsonant VALUES('nk',5);
INSERT INTO FantasyNameConsonant VALUES('rk',5);
INSERT INTO FantasyNameConsonant VALUES('sk',5);
INSERT INTO FantasyNameConsonant VALUES('cl',6);
INSERT INTO FantasyNameConsonant VALUES('fl',6);
INSERT INTO FantasyNameConsonant VALUES('fl',6);
INSERT INTO FantasyNameConsonant VALUES('kl',6);
INSERT INTO FantasyNameConsonant VALUES('ll',6);
INSERT INTO FantasyNameConsonant VALUES('pl',6);
INSERT INTO FantasyNameConsonant VALUES('sl',6);
INSERT INTO FantasyNameConsonant VALUES('br',6);
INSERT INTO FantasyNameConsonant VALUES('cr',6);
INSERT INTO FantasyNameConsonant VALUES('dr',6);
INSERT INTO FantasyNameConsonant VALUES('fr',6);
INSERT INTO FantasyNameConsonant VALUES('gr',6);
INSERT INTO FantasyNameConsonant VALUES('kr',6);
INSERT INTO FantasyNameConsonant VALUES('pr',6);
INSERT INTO FantasyNameConsonant VALUES('sr',6);
INSERT INTO FantasyNameConsonant VALUES('tr',6);
INSERT INTO FantasyNameConsonant VALUES('ss',5);
INSERT INTO FantasyNameConsonant VALUES('st',7);
INSERT INTO FantasyNameConsonant VALUES('str',6);
-- Repeat some entries to make them more common.
INSERT INTO FantasyNameConsonant VALUES('b',7);
INSERT INTO FantasyNameConsonant VALUES('c',7);
INSERT INTO FantasyNameConsonant VALUES('d',7);
INSERT INTO FantasyNameConsonant VALUES('f',7);
INSERT INTO FantasyNameConsonant VALUES('g',7);
INSERT INTO FantasyNameConsonant VALUES('h',7);
INSERT INTO FantasyNameConsonant VALUES('j',7);
INSERT INTO FantasyNameConsonant VALUES('k',7);
INSERT INTO FantasyNameConsonant VALUES('l',7);
INSERT INTO FantasyNameConsonant VALUES('m',7);
INSERT INTO FantasyNameConsonant VALUES('n',7);
INSERT INTO FantasyNameConsonant VALUES('p',7);
INSERT INTO FantasyNameConsonant VALUES('r',7);
INSERT INTO FantasyNameConsonant VALUES('s',7);
INSERT INTO FantasyNameConsonant VALUES('t',7);
INSERT INTO FantasyNameConsonant VALUES('v',7);
INSERT INTO FantasyNameConsonant VALUES('w',7);
INSERT INTO FantasyNameConsonant VALUES('b',7);
INSERT INTO FantasyNameConsonant VALUES('c',7);
INSERT INTO FantasyNameConsonant VALUES('d',7);
INSERT INTO FantasyNameConsonant VALUES('f',7);
INSERT INTO FantasyNameConsonant VALUES('g',7);
INSERT INTO FantasyNameConsonant VALUES('h',7);
INSERT INTO FantasyNameConsonant VALUES('j',7);
INSERT INTO FantasyNameConsonant VALUES('k',7);
INSERT INTO FantasyNameConsonant VALUES('l',7);
INSERT INTO FantasyNameConsonant VALUES('m',7);
INSERT INTO FantasyNameConsonant VALUES('n',7);
INSERT INTO FantasyNameConsonant VALUES('p',7);
INSERT INTO FantasyNameConsonant VALUES('t',7);
INSERT INTO FantasyNameConsonant VALUES('d',7);
INSERT INTO FantasyNameConsonant VALUES('y',7);
INSERT INTO FantasyNameConsonant VALUES('b',7);
INSERT INTO FantasyNameConsonant VALUES('e',7);
INSERT INTO FantasyNameConsonant VALUES('br',6);
INSERT INTO FantasyNameConsonant VALUES('dr',6);
INSERT INTO FantasyNameConsonant VALUES('fr',6);
INSERT INTO FantasyNameConsonant VALUES('gr',6);
INSERT INTO FantasyNameConsonant VALUES('kr',6);
-- Generate base random list for FantasyNamesCreated
INSERT INTO FantasyNamesCreated
SELECT
CASE WHEN RAND() < 0.50000009 THEN 1 ELSE 0 END
, FLOOR(RAND() * 10) % 7 + 4 -- At least 4 syllables, up to 10
, 0 -- curent syllable length = 0
, ''
FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a -- 10
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) b -- 100
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) c -- 1000
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) d -- 10,000
CROSS JOIN (SELECT 1 UNION SELECT 2) e -- 20,0000
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
UPDATE FantasyNamesCreated
SET
RANDOM_NAME = CONCAT(
RANDOM_NAME
, CASE
WHEN RANDOM_BIT = 1 THEN (
SELECT vowel FROM FantasyNameVowel
ORDER BY RAND()
LIMIT 1
)
ELSE (
SELECT consonant FROM FantasyNameConsonant
WHERE
1 = CASE
WHEN CURRENT_LENGTH = 0 THEN (ordering & 2) = 2
WHEN (CURRENT_LENGTH + 1 ) < SYLLABLE_LENGTH THEN (ordering & 4) = 4
ELSE (ordering & 1) = 1
END
ORDER BY RAND()
LIMIT 1
)
END
)
, RANDOM_BIT = CASE WHEN RANDOM_BIT = 0 THEN 1 ELSE 0 END
, CURRENT_LENGTH = CURRENT_LENGTH + 1
WHERE
CURRENT_LENGTH < SYLLABLE_LENGTH
;
-- Fix name casing.
UPDATE FantasyNamesCreated
SET RANDOM_NAME = CONCAT(UCASE(SUBSTRING(random_name, 1, 1)),SUBSTRING(random_name, 2) )
;
-- Get rid of any names that are already being used.
DELETE FROM FantasyNamesCreated
USING FantasyNamesCreated
LEFT JOIN character_
ON character_.NAME = FantasyNamesCreated.RANDOM_NAME
WHERE
character_.NAME IS NOT NULL
;
-- Insert into our final persisted table.
SET @ROWNUM = 0;
INSERT INTO AutoGeneratedFantasyName
SELECT (@ROWNUM := @ROWNUM + 1), d.NAME
FROM
(SELECT DISTINCT RANDOM_NAME AS NAME FROM FantasyNamesCreated) d
;
-- See what the final generated names are like.
SELECT *
FROM
AutoGeneratedFantasyName
LIMIT 50
;
10 - HeavyWeight - Utility_RenameUsersWtihBadNames_v1.7.sql
Code:
/*********************************************************************************
EQEmu - Naughty Name Renamer.
Synopsis: This file will find characters (first name and last name) with inappropriate
language and replace them. First names will be modified. Last names will be
wiped. Backup data stored in persisted table AutomatedCharacterRenames
that can be wiped / deleted at your leisure.
Assumptions:
It is assumed that you already have ran the script number 9
(9 - ModerateWeight - Create_random_fantasy_name_table.sql) to generate a
list of acceptable fantasy names. This table should be called `AutoGeneratedFantasyName`
and found in the projecteq database.
Instructions:
Edit the top portion of the file to add or remove words from the list of banned
character names. After that, you should run each section until you get
to the **UPDATE** sections. The last section before the UPDATE section
should show you the proposed list of modifications. At this point you can
remove individual changes from the list using a commented out portion of
the code and rerun the select statement. Once you are satisfied with the
changes, move to and run the first update statement. This will replace
the characters first name with the fantasy auto generated (if the bad word
appears in the first name) and will replace the last name with empty string
(wiping out their last name).
Optional:
For those characters whose names were updated, you can execute the final **UPDATE**
section and these accounts will be suspended for 1 week. This can only
be executed right after you have updated the users names.
IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT
Note - You will not be able to successfully edit a characters name
if this character is logged in. This will not error our or cause a
data abnormality - it simply will be auto-reverted the next time the
server syncs the profile blob to the database. It is suggested that you run
this during maintenance or only if you are willing to let some users
get away from you (if they are logged in) with the idea to run this script
again later.
If you plan on running this script every week, this is probably
not as important as one might imagine. (The user will get caught at some point).
IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT IMPORTANT
*********************************************************************************/
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TEMPORARY TABLE IF NOT EXISTS CHARACTER_NAME_FINDER(
WORD_IN_NAME VARCHAR(50)
);
DELETE FROM CHARACTER_NAME_FINDER;
INSERT INTO CHARACTER_NAME_FINDER
SELECT 'nipple'
UNION SELECT 'shit'
UNION SELECT 'fuck'
UNION SELECT 'cumming'
UNION SELECT 'poon'
UNION SELECT 'herpes'
UNION SELECT 'yourmom'
UNION SELECT 'your_mom'
UNION SELECT 'dick'
UNION SELECT 'toker'
UNION SELECT 'potbowl'
UNION SELECT 'anus'
UNION SELECT 'balls'
UNION SELECT 'bitch'
UNION SELECT 'nigg'
UNION SELECT 'abort'
UNION SELECT 'pussy'
UNION SELECT 'sex'
UNION SELECT 'suck'
UNION SELECT 'damn'
UNION SELECT 'ass'
UNION SELECT 'cunt'
UNION SELECT 'cock'
UNION SELECT 'penis'
UNION SELECT 'whore'
UNION SELECT 'piss'
;
/* DO NOT EDIT BELOW THIS LINE */
/*************************************************
** Fantasy Name Table Maintenance **
**************************************************/
-- Delete any names that exist in the character table.
DELETE FROM AutoGeneratedFantasyName
USING
AutoGeneratedFantasyName
LEFT JOIN character_
ON character_.NAME = AutoGeneratedFantasyName.RandomName
WHERE
character_.NAME IS NOT NULL
;
-- Renumber fantasy name table.
SET @ROWNUM = 0;
UPDATE AutoGeneratedFantasyName
SET AutoGeneratedFantasyNameId = (@ROWNUM := @ROWNUM + 1)
;
CREATE TABLE IF NOT EXISTS AutomatedCharacterRenames(
CHARACTER_ID INT
, ORIGINAL_FIRST_NAME VARCHAR(64)
, ORIGINAL_LAST_NAME VARCHAR(32)
, LEVEL INT
, WORD_IN_NAME VARCHAR(64)
, NEW_FIRST_NAME VARCHAR(64)
, NEW_LAST_NAME VARCHAR(32)
, INTERNAL_FANTASY_ID_TO_USE INT
, DATE_INSERTED DATETIME
, DATE_UPDATED DATETIME
);
-- Clear out any partial updates from the past.
DELETE FROM AutomatedCharacterRenames WHERE DATE_UPDATED IS NULL;
SET @ROWNUM = 0;
INSERT INTO AutomatedCharacterRenames
SELECT
contaminatedCharacters.CHARACTER_ID
, contaminatedCharacters.FirstName AS ORIGINAL_FIRST_NAME
, contaminatedCharacters.LastName AS ORIGINAL_LAST_NAME
, contaminatedCharacters.LEVEL
, contaminatedCharacters.WORD_IN_NAME
, CASE WHEN FIRST_NAME_CONTAMINATED = 1 THEN NULL ELSE FirstName END AS NEW_FIRST_NAME
, CASE WHEN LAST_NAME_CONTAMINATED = 1 THEN '' ELSE LastName END AS NEW_LAST_NAME
, CASE WHEN FIRST_NAME_CONTAMINATED = 1 THEN (@ROWNUM := @ROWNUM + 1) ELSE NULL END
, NOW()
, NULL
FROM
(
SELECT
CharacterNames.CHARACTER_ID
, CharacterNames.LEVEL
, CharacterNames.FirstName
, CharacterNames.LastName
, MAX(filter.WORD_IN_NAME) AS WORD_IN_NAME
, MAX(CASE WHEN LOCATE(WORD_IN_NAME, FirstName) > 0 THEN 1 ELSE 0 END) AS FIRST_NAME_CONTAMINATED
, MAX(CASE WHEN LOCATE(WORD_IN_NAME, LastName) > 0 THEN 1 ELSE 0 END) AS LAST_NAME_CONTAMINATED
FROM (
SELECT
c.id AS CHARACTER_ID
, TRIM(REPLACE(CAST(SUBSTRING(PROFILE, (4+1), 64) AS CHAR(64)),'\0','')) AS FirstName
, TRIM(REPLACE(CAST(SUBSTRING(PROFILE, (68+1), 32) AS CHAR(64)),'\0','')) AS LastName
, LEVEL
FROM
character_ c
INNER JOIN account acc
ON acc.id = c.account_id
WHERE
acc.STATUS >= 0
) CharacterNames
INNER JOIN CHARACTER_NAME_FINDER filter
ON
LOCATE(WORD_IN_NAME, FirstName) > 0
OR
LOCATE(WORD_IN_NAME, LastName) > 0
GROUP BY
CharacterNames.CHARACTER_ID
, CharacterNames.LEVEL
, CharacterNames.FirstName
, CharacterNames.LastName
) contaminatedCharacters
;
-- Get the final name changes that we will be making.
UPDATE AutomatedCharacterRenames, AutoGeneratedFantasyName
SET
AutomatedCharacterRenames.NEW_FIRST_NAME = AutoGeneratedFantasyName.RandomName
WHERE
AutomatedCharacterRenames.NEW_FIRST_NAME IS NULL
AND AutomatedCharacterRenames.INTERNAL_FANTASY_ID_TO_USE = AutoGeneratedFantasyName.AutoGeneratedFantasyNameID
;
-- Protect ourselves in case script was not ran right.
-- Delete any names from the update list if the first name is still null.
-- (to stop issues if people do not run the "reorder" script portion)
DELETE
FROM AutomatedCharacterRenames
WHERE
NEW_FIRST_NAME IS NULL;
-- See what you are going to be modifying.
SELECT
*
FROM
AutomatedCharacterRenames
WHERE
DATE_UPDATED IS NULL
ORDER
BY CHARACTER_ID ASC
;
-- Remove any changes that you do not wish to have
-- Update the character_id filter to point to the one you wish to remove
-- from this list. Once done, rerun the above sql
-- statement to see the remaining list.
DELETE FROM AutomatedCharacterRenames
WHERE
DATE_UPDATED IS NULL
AND CHARACTER_ID = -1
;
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- Update the names to the values we noted above
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
CREATE TEMPORARY TABLE IF NOT EXISTS UpdatesToMake(
CHARACTER_ID INT
, ACCOUNT_ID INT
, ORIGINAL_FIRST_NAME VARCHAR(64)
, NEW_FIRST_NAME VARCHAR(64)
, NEW_ENCODED_FIRST_NAME VARCHAR(64)
, NEW_ENCODED_LAST_NAME VARCHAR(64)
, NEW_PROFILE BLOB
);
DELETE FROM UpdatesToMake;
INSERT INTO UpdatesToMake
SELECT
acr.CHARACTER_ID
, c.account_id
, acr.ORIGINAL_FIRST_NAME
, acr.NEW_FIRST_NAME
, RPAD(acr.NEW_FIRST_NAME, 64, '\0')
, RPAD(acr.NEW_LAST_NAME, 32, '\0')
, INSERT(c.PROFILE, (4+1), 96, CONCAT(RPAD(acr.NEW_FIRST_NAME, 64, '\0'), RPAD(acr.NEW_LAST_NAME, 32, '\0')))
FROM
AutomatedCharacterRenames acr
INNER JOIN character_ c
ON c.id = acr.CHARACTER_ID
WHERE
DATE_UPDATED IS NULL ;
UPDATE AutomatedCharacterRenames
SET DATE_UPDATED = NOW()
WHERE DATE_UPDATED IS NULL;
-- Fix account character name.
UPDATE ACCOUNT, UpdatesToMake
SET ACCOUNT.CHARNAME = UpdatesToMake.NEW_FIRST_NAME
WHERE ACCOUNT.CHARNAME = UpdatesToMake.ORIGINAL_FIRST_NAME;
-- Fix petitions
UPDATE PETITIONS, UpdatesToMake
SET PETITIONS.CHARNAME = UpdatesToMake.NEW_FIRST_NAME
WHERE PETITIONS.CHARNAME = UpdatesToMake.ORIGINAL_FIRST_NAME;
-- Fix corpses
UPDATE PLAYER_CORPSES, UpdatesToMake
SET PLAYER_CORPSES.CHARNAME = UpdatesToMake.NEW_FIRST_NAME
WHERE PLAYER_CORPSES.CHARID = UpdatesToMake.CHARACTER_ID;
-- Fix characters.
UPDATE CHARACTER_, UpdatesToMake
SET
CHARACTER_.PROFILE = UpdatesToMake.NEW_PROFILE
, CHARACTER_.NAME = UpdatesToMake.NEW_FIRST_NAME
WHERE
CHARACTER_.ID = UpdatesToMake.CHARACTER_ID;
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- OPTIONAL: Suspend accounts that had their characters renamed.
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
-- UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
UPDATE ACCOUNT, UpdatesToMake
SET
ACCOUNT.SUSPENDEDUNTIL = ADDDATE(NOW(), INTERVAL 7 DAY)
WHERE
ACCOUNT.ID = UpdatesToMake.ACCOUNT_ID
|
|
|
|
06-12-2011, 02:39 AM
|
|
Discordant
|
|
Join Date: Oct 2009
Posts: 312
|
|
SqlDev01 - Thank you for sharing your work, the bad-name renamer in particular is exceptional, and unexpected! Congrats!
|
06-13-2011, 11:11 PM
|
 |
Hill Giant
|
|
Join Date: Aug 2010
Location: UT
Posts: 215
|
|
Wow, very nice. Thanks for sharing, several of these are invaluable.
I remember when that plat wipe went down. It was really fun to watch--drama was better than TV.
|
|
|
|
02-02-2012, 05:59 AM
|
 |
Developer
|
|
Join Date: Aug 2006
Location: USA
Posts: 5,946
|
|
I am no SQL guru like SqlDev01, and this can probably be written better and more efficiently, but I figured I would share it anyway.
In relation to the tradeskill exploit queries SqlDev01 posted above, these queries are designed to find tradeskill combines that may potentially be exploited. There are 2 queries. They are both the same in every way except the second one actually makes item price changes (see the UPDATE near the bottom of it).
This first query basically creates some temporary tables and totals up the costs for the components, failures and success results for each recipe. It then checks if the total of the components cost is less than the total of the success results cost. It will list all of the recipes it finds that might be an issue.
There are a lot of factors into tradeskill recipe pricing such as where the components come from (are they purchased, dropped, etc), what the sellrates are for each item in the combine and result, if the result is no drop, the actual amount you pay/receive for the components and results after prices are adjusted, combine results that are components for another combine, and probably extra stuff not mentioned here.
These scripts do not account for all of that, as it would require a lot more queries and logic to make sure all factors were considered. It does take account for results being no drop. The scripts also assume the sellrate for all items is set to 1.
I ran this script and found a much larger number of results than I expected to see. It was far to many to deal with them all on a case-by-case basis, which was my original intention. So, I made the second script that actually adjusts item prices.
Note that you would want to adjust the 2 variables at the top of the scripts to specify the range of recipe_ids that you want to run the query on. If you run it for all of them and have around 10k recipes, just be aware that it will take a LONG time to complete. The SUM portion where it actually totals up the costs for components, failures, and successes is what takes the longest. It took around 30 minutes to complete on my test server. Because of this, I would recommend only trying it when your server is not running, as it will possibly impact play performance and take longer to run.
This first query should be perfectly safe to run on any server. It doesn't change any existing data, but remember it takes a long time to complete and shouldn't be done while the server is running:
Code:
-- Query Script to find all Exploitable Tradeskill Combines where the cost to create is less than the value of the combine result.
--
-- NOTE: This script takes a LONG time to run. It ran for about 30 minutes on my server before completing.
--
-- Define the range of IDs you want to run the query for (smaller ranges will take less time)
SET @StartingRecipeID = 1;
SET @EndingRecipeID = 1000;
-- Create a temporary table that includes the prices for all items in the components, failures, and results
CREATE TEMPORARY TABLE temp_table (
id INT(11) DEFAULT 0 PRIMARY KEY,
recipe_id INT(11) DEFAULT 0,
combine_value INT(11) DEFAULT 0,
fail_value INT(11) DEFAULT 0,
result_value INT(11) DEFAULT 0
);
-- Insert all of the Entry IDs and Recipe IDs into the temp_table
INSERT INTO temp_table ( id, recipe_id ) ( SELECT id, recipe_id FROM tradeskill_recipe_entries );
-- Set the result_value for temp_table - Add 0 value if item is not tradeable
UPDATE tradeskill_recipe_entries, items, temp_table
SET temp_table.result_value = (
(IFNULL((SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id AND items.nodrop = 1), 0)) *
(SELECT tradeskill_recipe_entries.successcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id)/
IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
)
WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.successcount > 0;
-- Set the combine_value for temp_table
UPDATE tradeskill_recipe_entries, items, temp_table
SET temp_table.combine_value = (
(SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id) *
(SELECT tradeskill_recipe_entries.componentcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id) /
IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
)
WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.componentcount > 0;
-- Set the fail_value for temp_table
UPDATE tradeskill_recipe_entries, items, temp_table
SET temp_table.fail_value = (
(SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id) *
(SELECT tradeskill_recipe_entries.failcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id) /
IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
)
WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.failcount > 0;
-- Create a temporary table that includes the total prices for all items in the components and results for each recipe
CREATE TEMPORARY TABLE temp_table2 (
recipe_id INT(11) DEFAULT 0 PRIMARY KEY,
recipe_name VARCHAR(64) DEFAULT '',
combine_value INT(11) DEFAULT 0,
fail_value INT(11) DEFAULT 0,
result_value INT(11) DEFAULT 0
);
-- Insert all of the unique Recipe IDs into the temp_table2
REPLACE INTO temp_table2 ( recipe_id ) ( SELECT recipe_id FROM tradeskill_recipe_entries );
-- UPDATE the name of all recipes to the temp_table2
UPDATE temp_table2, tradeskill_recipe SET temp_table2.recipe_name =
(
SELECT tradeskill_recipe.name FROM tradeskill_recipe WHERE tradeskill_recipe.id = temp_table2.recipe_id LIMIT 1
)
WHERE tradeskill_recipe.id = temp_table2.recipe_id;
-- Test Query to view a sample output of temp_table
SELECT recipe_id, combine_value, fail_value, result_value FROM temp_table LIMIT 10;
-- Test Query to view a sample output of temp_table
SELECT recipe_id, recipe_name, combine_value, fail_value, result_value FROM temp_table2 LIMIT 10;
-- Uncomment the following Delete line to test changes in this query so it will run faster
DELETE FROM temp_table WHERE recipe_id <= @StartingRecipeID AND recipe_id > @EndingRecipeID;
-- UPDATE temp_table2 with the combine_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.combine_value = (
SELECT SUM(temp_table.combine_value)
FROM temp_table
WHERE temp_table.recipe_id = temp_table2.recipe_id
GROUP BY temp_table.recipe_id
);
-- UPDATE temp_table2 with the fail_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.fail_value = (
SELECT SUM(temp_table.fail_value)
FROM temp_table
WHERE temp_table.recipe_id = temp_table2.recipe_id
GROUP BY temp_table.recipe_id
);
-- UPDATE temp_table2 with the result_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.result_value = (
SELECT SUM(temp_table.result_value)
FROM temp_table
WHERE temp_table.recipe_id = temp_table2.recipe_id
GROUP BY temp_table.recipe_id
);
-- Main Query to find any recipe for which the result is worth more than the combined cost of the components.
SELECT recipe_id, recipe_name, combine_value, fail_value, result_value
FROM temp_table2 WHERE (combine_value - fail_value) < (result_value - fail_value);
-- Drop the temporary tables since they are no longer needed
DROP TEMPORARY TABLE temp_table;
DROP TEMPORARY TABLE temp_table2;
The second query below is the one that actually does the price changes. After running this, you may want to run the above query again just to see how many results it finds on the second pass. There are so many factors that it is most likely not going to resolve every possible recipe, but it should definitely lower the total output down to a more reasonable level where you can investigate them 1 by 1 as needed.
Be warned that this DOES change prices in your items table, so make sure to backup your database before running it.
I can adjust it to create a new table that will save backup prices and post another query to restore original prices if people really need it.
Code:
-- Query Script to find all Exploitable Tradeskill Combines where the cost to create is less than the value of the combine result.
--
-- NOTE: This script takes a LONG time to run. It ran for about 30 minutes on my server before completing.
--
-- Define the range of IDs you want to run the query for (smaller ranges will take less time)
SET @StartingRecipeID = 1;
SET @EndingRecipeID = 1000;
-- Create a temporary table that includes the prices for all items in the components, failures, and results
CREATE TEMPORARY TABLE temp_table (
id INT(11) DEFAULT 0 PRIMARY KEY,
recipe_id INT(11) DEFAULT 0,
combine_value INT(11) DEFAULT 0,
fail_value INT(11) DEFAULT 0,
result_value INT(11) DEFAULT 0
);
-- Insert all of the Entry IDs and Recipe IDs into the temp_table
INSERT INTO temp_table ( id, recipe_id ) ( SELECT id, recipe_id FROM tradeskill_recipe_entries );
-- Set the result_value for temp_table - Add 0 value if item is not tradeable
UPDATE tradeskill_recipe_entries, items, temp_table
SET temp_table.result_value = (
(IFNULL((SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id AND items.nodrop = 1), 0)) *
(SELECT tradeskill_recipe_entries.successcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id)/
IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
)
WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.successcount > 0;
-- Set the combine_value for temp_table
UPDATE tradeskill_recipe_entries, items, temp_table
SET temp_table.combine_value = (
(SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id) *
(SELECT tradeskill_recipe_entries.componentcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id) /
IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
)
WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.componentcount > 0;
-- Set the fail_value for temp_table
UPDATE tradeskill_recipe_entries, items, temp_table
SET temp_table.fail_value = (
(SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id) *
(SELECT tradeskill_recipe_entries.failcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id) /
IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
)
WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.failcount > 0;
-- Create a temporary table that includes the total prices for all items in the components and results for each recipe
CREATE TEMPORARY TABLE temp_table2 (
recipe_id INT(11) DEFAULT 0 PRIMARY KEY,
recipe_name VARCHAR(64) DEFAULT '',
combine_value INT(11) DEFAULT 0,
fail_value INT(11) DEFAULT 0,
result_value INT(11) DEFAULT 0
);
-- Insert all of the unique Recipe IDs into the temp_table2
REPLACE INTO temp_table2 ( recipe_id ) ( SELECT recipe_id FROM tradeskill_recipe_entries );
-- UPDATE the name of all recipes to the temp_table2
UPDATE temp_table2, tradeskill_recipe SET temp_table2.recipe_name =
(
SELECT tradeskill_recipe.name FROM tradeskill_recipe WHERE tradeskill_recipe.id = temp_table2.recipe_id LIMIT 1
)
WHERE tradeskill_recipe.id = temp_table2.recipe_id;
-- Test Query to view a sample output of temp_table
SELECT recipe_id, combine_value, fail_value, result_value FROM temp_table LIMIT 10;
-- Test Query to view a sample output of temp_table
SELECT recipe_id, recipe_name, combine_value, fail_value, result_value FROM temp_table2 LIMIT 10;
-- Uncomment the following Delete line to test changes in this query so it will run faster
DELETE FROM temp_table WHERE recipe_id <= @StartingRecipeID AND recipe_id > @EndingRecipeID;
-- UPDATE temp_table2 with the combine_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.combine_value = (
SELECT SUM(temp_table.combine_value)
FROM temp_table
WHERE temp_table.recipe_id = temp_table2.recipe_id
GROUP BY temp_table.recipe_id
);
-- UPDATE temp_table2 with the fail_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.fail_value = (
SELECT SUM(temp_table.fail_value)
FROM temp_table
WHERE temp_table.recipe_id = temp_table2.recipe_id
GROUP BY temp_table.recipe_id
);
-- UPDATE temp_table2 with the result_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.result_value = (
SELECT SUM(temp_table.result_value)
FROM temp_table
WHERE temp_table.recipe_id = temp_table2.recipe_id
GROUP BY temp_table.recipe_id
);
-- Main Query to find any recipe for which the result is worth more than the combined cost of the components.
SELECT recipe_id, recipe_name, combine_value, fail_value, result_value
FROM temp_table2 WHERE (combine_value - fail_value) < (result_value - fail_value);
UPDATE items, temp_table2, tradeskill_recipe_entries
SET items.price = ((temp_table2.combine_value - temp_table2.fail_value) / IF((items.itemtype = 21 AND items.maxcharges = tradeskill_recipe_entries.successcount), 1, tradeskill_recipe_entries.successcount))
WHERE temp_table2.recipe_id = tradeskill_recipe_entries.recipe_id
AND items.nodrop = 1
AND (temp_table2.combine_value - temp_table2.fail_value) < (temp_table2.result_value - temp_table2.fail_value)
AND items.price > ((temp_table2.combine_value - temp_table2.fail_value) / tradeskill_recipe_entries.successcount)
AND items.id = tradeskill_recipe_entries.item_id
AND tradeskill_recipe_entries.successcount > 0
AND ISNULL((SELECT tre_table.failcount FROM tradeskill_recipe_entries AS tre_table WHERE tre_table.failcount > 0 AND tre_table.item_id = items.id AND tre_table.recipe_id = temp_table2.recipe_id))
AND tradeskill_recipe_entries.recipe_id >= @StartingRecipeID
AND tradeskill_recipe_entries.recipe_id < @EndingRecipeID;
-- Drop the temporary tables since they are no longer needed
DROP TEMPORARY TABLE temp_table;
DROP TEMPORARY TABLE temp_table2;
If anyone has any issues running this or any suggestions, let me know and I will try to help out.
Last edited by trevius; 02-02-2012 at 10:41 PM..
|
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT -4. The time now is 02:49 AM.
|
|
 |
|
 |
|
|
|
 |
|
 |
|
 |
Linear Mode
