Character Purge Tool?

SqlDev01 · #4 08-16-2011, 01:13 PM

The following code should insert into a table those that match the requirements. This does not delete them from the tables. Will leave that exercise to another user.

Code:

/* 
General Script Information:
	The script will attempt to find users to delete in several different levels.
	1.  By last account / char login date (the maximum of the two).
	2.  By level.
	3.  By platinum amount.
	
Instructions:
	1.  Tweak the filter criteria as you see fit.  See the lines
		below as the filter criteria is described.  You can / should
		play around with this until you decide how you want to filter this.
	2.  Run the entirety of the -uncommented- script.
	3.  You will see a list of the characters that are going to be removed.
	
	-- NOTE:  This is not added to the script yet.
	-- 3.  If you are OK with this change, highlight and run
	-- the entirety of the commented out section (the actual delets).
*/

-- drop table characterDeletionFilterCriteria
CREATE TEMPORARY TABLE IF NOT EXISTS characterDeletionFilterCriteria(
	FROM_DATE DATETIME -- Earliest date, inclusive, that this filter row should apply to.
	, TO_DATE DATETIME -- Latest date, exclusive, that this filter row should apply to.
	, MAXIMUM_CHARACTER_LEVEL INT -- Inclusive Highest level character this filter should apply to.
	, MAXIMUM_LIQUID_PLAT INT	-- Highest amount of liquid plat this filter should apply to.
);
DELETE FROM characterDeletionFilterCriteria;

-- Insert deletion filters.
INSERT INTO characterDeletionFilterCriteria
-- Very lightweight restriction while tesitng... not logged in the last 1 year and their char is <= 55 and they have less than 50k plat.
	SELECT DATE_ADD(SYSDATE(), INTERVAL -10 YEAR), DATE_ADD(SYSDATE(), INTERVAL -6 MONTH), 55, 50000

-- If they have not logged into the account or the character in 3 years and have less than a million on them get rid of them.
-- SELECT DATE_ADD(SYSDATE(), INTERVAL -10 YEAR), DATE_ADD(SYSDATE(), INTERVAL -3 YEAR), 9999, 1000000

-- If they have last logged in between 3 years and 2 years and are not at least level 70 and have less then 500k on them get rid of them.
-- UNION SELECT DATE_ADD(SYSDATE(), INTERVAL -3 YEAR), DATE_ADD(SYSDATE(), INTERVAL -2 YEAR), 70, 500000

-- If they have last logged in between 2 years and 1 year ago but are not at least level 55 and have less than 200k on them get rid of them.
-- UNION SELECT DATE_ADD(SYSDATE(), INTERVAL -2 YEAR), DATE_ADD(SYSDATE(), INTERVAL -1 YEAR), 55, 200000

-- If they have last logged in in the last year, leave them be.

;

-- Find users that at least appear to fit into the broad filter categories.
-- Temp table used to speed up subsequent queries as opposed to trying to join
--	every possible table needed right now.
CREATE TEMPORARY TABLE IF NOT EXISTS CharactersToConsiderForDeletionPendingOtherFilterCriteria(
	CHARACTER_ID INT
	, CHARACTER_NAME VARCHAR(64)
	, LastAccountAccessDate DATETIME
	, MinimumPlatAmountCharacterNeedsToAvoidDeletion INT
);
DELETE FROM CharactersToConsiderForDeletionPendingOtherFilterCriteria; 

INSERT INTO CharactersToConsiderForDeletionPendingOtherFilterCriteria
SELECT
outerchar.ID
, outerChar.NAME
, accountAccessDates.LastAccessDate
, MIN(filter.MAXIMUM_LIQUID_PLAT)
FROM
	(
		SELECT 
			c.ID AS CHARACTER_ID
			, CASE 
				WHEN aip.lastused IS NULL THEN FROM_UNIXTIME(c.timelaston)
				WHEN FROM_UNIXTIME(c.timelaston) > aip.lastused THEN FROM_UNIXTIME(c.timelaston)
				ELSE aip.lastused
			END AS LastAccessDate

		FROM
			character_ c
			INNER JOIN (SELECT accid, MAX(lastused) AS lastused FROM account_ip GROUP BY accid) aip
				ON c.account_id = aip.accid
	) accountAccessDates
	INNER JOIN character_ outerchar
		ON outerchar.id = accountAccessDates.CHARACTER_ID
	INNER JOIN characterDeletionFilterCriteria filter
		ON accountAccessDates.LastAccessDate >= filter.FROM_DATE AND accountAccessDates.LastAccessdate < filter.TO_DATE
		AND outerchar.LEVEL <= filter.MAXIMUM_CHARACTER_LEVEL
GROUP BY outerchar.ID, outerChar.NAME, accountAccessDates.LastAccessDate
;

CREATE TEMPORARY TABLE IF NOT EXISTS CharactersToBeDeleted(
	CHARACTER_ID INT
	, CHARACTER_NAME VARCHAR(64)
	, LEVEL INT
	, LastAccountAccessDate DATETIME
	, CurrentLiquidPlatinumAmount INT
);
DELETE FROM CharactersToBeDeleted; 

INSERT INTO CharactersToBeDeleted
SELECT 
	IndividualCharacterSlots.CHARACTER_ID
	, IndividualCharacterSlots.CHARACTER_NAME
	, IndividualCharacterSlots.LEVEL
	, IndividualCharacterSlots.LastAccountAccessDate
	, 
		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
			filter.CHARACTER_ID
			, filter.CHARACTER_NAME
			, filter.LastAccountAccessDate
			, filter.MinimumPlatAmountCharacterNeedsToAvoidDeletion
			, c.LEVEL
			
			/* 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_ c
			INNER JOIN CharactersToConsiderForDeletionPendingOtherFilterCriteria filter
				ON c.id = filter.character_id
	) IndividualCharacterSlots 
	WHERE MinimumPlatAmountCharacterNeedsToAvoidDeletion > (
			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
		);


-- select count(*) as NumberOfCharactersToBePurged from CharactersToBeDeleted;
SELECT 
	* 
FROM 
	CharactersToBeDeleted 
ORDER BY 
	CharactersToBeDeleted.LEVEL DESC
	, CurrentLiquidPlatinumAmount DESC 
LIMIT 1000;