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;