Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Tools

Development::Tools 3rd Party Tools for EQEMu (DB management tools, front ends, etc...)

Reply
 
Thread Tools Display Modes
  #1  
Old 12-18-2010, 03:38 PM
SqlDev01
Fire Beetle
 
Join Date: Dec 2010
Location: N/A
Posts: 5
Default 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;

;
Reply With Quote
  #2  
Old 05-15-2011, 10:48 PM
KLS
Administrator
 
Join Date: Sep 2006
Posts: 1,348
Default

You know no one ever replied to this but I was playing with it and these are really neat.
Reply With Quote
  #3  
Old 05-23-2011, 03:24 PM
So_1337
Dragon
 
Join Date: May 2006
Location: Cincinnati, OH
Posts: 689
Default

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.
Reply With Quote
  #4  
Old 05-23-2011, 10:47 PM
wolfwalkereci
Discordant
 
Join Date: Dec 2005
Posts: 435
Default

Yes it is some nice code. Shame the author created a new account to post it.
Reply With Quote
  #5  
Old 06-12-2011, 02:11 AM
SqlDev01
Fire Beetle
 
Join Date: Dec 2010
Location: N/A
Posts: 5
Default

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
Reply With Quote
  #6  
Old 06-12-2011, 02:39 AM
Burningsoul
Banned
 
Join Date: Oct 2009
Posts: 312
Default

SqlDev01 - Thank you for sharing your work, the bad-name renamer in particular is exceptional, and unexpected! Congrats!
Reply With Quote
  #7  
Old 06-13-2011, 11:11 PM
revloc02c's Avatar
revloc02c
Hill Giant
 
Join Date: Aug 2010
Location: UT
Posts: 215
Default

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.
Reply With Quote
  #8  
Old 02-02-2012, 05:59 AM
trevius's Avatar
trevius
Developer
 
Join Date: Aug 2006
Location: USA
Posts: 5,946
Default

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.
__________________
Trevazar/Trevius Owner of: Storm Haven
Everquest Emulator FAQ (Frequently Asked Questions) - Read It!

Last edited by trevius; 02-02-2012 at 10:41 PM..
Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 07:51 PM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3