Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Tools
Reload this Page General EQEmu SQL Utility Scripts (plat exploit finder, etc)

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

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #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
 

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Threaded Mode Threaded Mode

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 11:51 PM.

EQEmulator - Archive - Top

 

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 - 2025, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3