GlobalEvent 2.0 Database Cleaner - Inactive Players + The Players Child Data (skills, items, etc)

BeniS · Apr 25, 2011

Hey All!

This script is based off CyberM's http://otland.net/f82/automated-database-cleanup-1-1-solution-inactive-players-60128/ database cleaner, much credit to him for the base structure of this script.

I found that deleting just the character from the database wasn't enough for me! haha so I went ahead and revamped it so when it removes a character from the database it removes ALL other types of data that the `players` table has set in other tables and doesn't need, for example `player_skills` or `player_depotitems, these tables become clustered full of useless data when a character is removed. (If you have set the triggers in the mysql schema then this won't be that useful to you, but I personally like doing it like this all at once on startup).

The tables you search through when deleting inactive players is fully customizable. If you don't want it to remove from a table I have preset in the script just simply remove it from the list (be sure not to remove the `players` table from the array list.) or ofcourse vice-versa, if you want to add another table that I haven't added then you can do so easily.

You can also choose whether or not you want to delete from childTables in the config, along with a few other options.

Special thanks to darkhaos for adding removals of empty accounts to the script! Saves me the time, thank you.
The script will remove empty accounts from the database. (configurable) thanks to darkhaos.

Also note that it does NOT remove sample characters or the Account Manager, I have tested this script.

Example Config:

Lua:

	local childAttributeTables = {
		players = {
			[1] = {table = "`player_viplist`", idField = "`player_id`"},
			[2] = {table = "`player_storage`", idField = "`player_id`"},
			[3] = {table = "`player_spells`", idField = "`player_id`"},
			[4] = {table = "`player_skills`", idField = "`player_id`"},
			[5] = {table = "`player_namelocks`", idField = "`player_id`"},
			[6] = {table = "`player_items`", idField = "`player_id`"},
			[7] = {table = "`player_depotitems`", idField = "`player_id`"},
			[8] = {table = "`houses`", idField = "`owner`"},
			[9] = {table = "`house_auctions`", idField = "`player_id`"},
			[10] = {table = "`players`", idField = "`id`"} -- Keep this as the last item in the array
			--Note: `houses` and `bans` are in the DB triggers for TFS so don't worry about them.
			--Also I did not want to put killers, or deaths on here because that is historic data,
			--do so at your ouwn risk.
		},
		accounts = {
			[1] = {table = "`accounts`", idField = "`id`"},
			[2] = {table = "`account_viplist`", idField = "`account_id`"}
		}
	}

This script will activate the same as CyberM's v1.1 through the globalevents onStartUp method.

Logs the amounts removed for each table in the database, and prints it to the screen with the counts of each tables deletes.

Example Log File:

Code:

[26 April 2011 13:25:41 ] >> [DBCLEANUP] 3 inactive players and 2 empty accounts have been deleted from the database.
[!] --> Dropped: 0 from `player_viplist` table
[!] --> Dropped: 0 from `player_storage` table
[!] --> Dropped: 0 from `player_spells` table
[!] --> Dropped: 0 from `player_skills` table
[!] --> Dropped: 0 from `player_namelocks` table
[!] --> Dropped: 0 from `player_items` table
[!] --> Dropped: 0 from `player_depotitems` table
[!] --> Dropped: 3 from `players` table
[!] --> Dropped: 2 from `accounts` table
[!] --> Dropped: 0 from `account_viplist` table

[26 April 2011 13:26:40 ] >> [DBCLEANUP] 73 inactive players and 36 empty accounts have been deleted from the database.
[!] --> Dropped: 0 from `player_viplist` table
[!] --> Dropped: 38 from `player_storage` table
[!] --> Dropped: 0 from `player_spells` table
[!] --> Dropped: 7 from `player_skills` table
[!] --> Dropped: 0 from `player_namelocks` table
[!] --> Dropped: 8 from `player_items` table
[!] --> Dropped: 1 from `player_depotitems` table
[!] --> Dropped: 73 from `players` table
[!] --> Dropped: 36 from `accounts` table
[!] --> Dropped: 2 from `account_viplist` table

[26 April 2011 13:55:22 ] >> [DBCLEANUP] 1143 inactive players and 784 empty accounts have been deleted from the database.
[!] --> Dropped: 0 from `player_viplist` table
[!] --> Dropped: 860 from `player_storage` table
[!] --> Dropped: 0 from `player_spells` table
[!] --> Dropped: 991 from `player_skills` table
[!] --> Dropped: 0 from `player_namelocks` table
[!] --> Dropped: 389 from `player_items` table
[!] --> Dropped: 627 from `player_depotitems` table
[!] --> Dropped: 1143 from `players` table
[!] --> Dropped: 784 from `accounts` table
[!] --> Dropped: 153 from `account_viplist` table

I caution you all before using this script to back up your database AND first test it on a local server before using it on your dedicated server. I can not guarantee it will work the same for every core server. This has been tested on the 0.4 revs not 0.3.6pl* versions.

Here is the full script:

Add to globalevents.xml:

Lua:

<globalevent name="dbcleaner" type="startup" event="script" value="dbcleaner.lua"/>

Add to globalevents/scripts/dbcleaner.lua:

Lua:

------------------------------------------------------------------------------*
----- [[> Automated Database Cleanup 1.1 Structure //By Cybermaster <]] ------|
-------------- [[> System 2.0 Revamped by Teh Maverick <3 <]] ----------------|
------------- [[>  Removal of empty accounts by darkaos :) <]] ---------------|
--------------- [[> Function getDBPlayersCount() by Elf <]] ------------------|
------------------------------------------------------------------------------|
------------------------------------------------------------------------------|
--- ~!READ THIS!~ ------------------------------------------------------------|
--- Be sure to back up your database and test this on your server first, -----|
--- I(Teh Maverick) cannot guarantee it will work the same for every core. ---|
--- It is very easy to test, with the log file and values that are printed ---|
-----------------------------------Enjoy!-------------------------------------|
------------------------------------------------------------------------------*
 
function countRowsWhereInTable(table, field, condition)
    local result = db.getResult("SELECT COUNT(" .. field .. ") as count FROM " .. table .. " WHERE " .. field .. " = '" .. condition .. "';")
    local tmp = result:getDataInt("count")
    result:free()
    return tmp
end
 
function getDBPlayersCount()
    local result = db.getResult("SELECT COUNT(id) as count FROM `players`;")
    local tmp = result:getDataInt("count")
    result:free()
    return tmp
end
 
function getDBAccountsCount()
    local result = db.getResult("SELECT COUNT(id) as count FROM `accounts`;")
    local tmp = result:getDataInt("count")
    result:free()
    return tmp
end
 
function onStartup()
	local DB_BEFORE = {players = getDBPlayersCount(), accounts = getDBAccountsCount()}
	local result,result1, ii, numPlayersToDelete, numAccountsDeleted, tmp = 0, 0, 0, 0, 0
	local pid, aid = {}, {}
	local dropCount = {players={},accounts={}}
 
	local config = {
		deleteAccountWithNoPlayers = true,
		cleanChildTables = true,
		printResult = true,
		saveResultToFile = true,
		logFileName = 'db_cleanup.txt'
	}
 
    --In each table, players with below specified level, and days of inactivity will be deleted from db on server startup
	local cleanup = {
		[1] = {level = 11, time = 5 * 24 * 60 * 60},
		[2] = {level = 20, time = 15 * 24 * 60 * 60},
		[3] = {level = 50, time = 30 * 24 * 60 * 60},
		[4] = {level = 100, time = 60 * 24 * 60 * 60},
		[5] = {level = 130, time = 90 * 24 * 60 * 60}
	}
 
	local childAttributeTables = {
		players = {
			[1] = {table = "`player_viplist`", idField = "`player_id`"},
			[2] = {table = "`player_storage`", idField = "`player_id`"},
			[3] = {table = "`player_spells`", idField = "`player_id`"},
			[4] = {table = "`player_skills`", idField = "`player_id`"},
			[5] = {table = "`player_namelocks`", idField = "`player_id`"},
			[6] = {table = "`player_items`", idField = "`player_id`"},
			[7] = {table = "`player_depotitems`", idField = "`player_id`"},
			[8] = {table = "`houses`", idField = "`owner`"},
			[9] = {table = "`house_auctions`", idField = "`player_id`"},
			[10] = {table = "`players`", idField = "`id`"} -- Keep this as the last item in the array
			--Note: `houses` and `bans` are in the DB triggers for TFS so don't worry about them.
			--Also I did not want to put killers, or deaths on here because that is historic data,
			--do so at your own risk.
		},
		accounts = {
			[1] = {table = "`accounts`", idField = "`id`"},
			[2] = {table = "`account_viplist`", idField = "`account_id`"}
		}
	}
	
	--Clean up all the players and player data
	for i = 1, #cleanup do
		result = db.getResult("SELECT `id`,`name`,`account_id` FROM `players` WHERE `level` < ".. cleanup[i].level .." AND `name` NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND `group_id` < 2 AND `lastlogin` < UNIX_TIMESTAMP() - ".. cleanup[i].time .." AND `lastlogin` > 0;")
		if(result:getID() ~= -1) then
			ii = 1
			repeat
				pid[ii] = result:getDataInt("id") -- list the players id into an array
				aid[ii] = result:getDataInt("account_id") -- list the account id of each player being removed into an array
				ii = ii + 1
			until not(result:next())
			result:free()
		end
		numPlayersToDelete = ii - 1
 
		--Drop players and their child table attribute data such as skills, items, etc.
		for j = 1, numPlayersToDelete do
			
			if(config.cleanChildTables) then
				for k = 1, #childAttributeTables.players do
					if childAttributeTables.players[k].table == "houses" then
						house = getHouseByPlayerGUID(pid[j])
						if house ~= 0 or house ~= nil then
							doCleanHouse(house)
							doUpdateHouseAuctions()
						end
					else
						dropCount.players[k] = ((dropCount.players[k] or 0) + countRowsWhereInTable(childAttributeTables.players[k].table, childAttributeTables.players[k].idField, pid[j]))
						db.executeQuery("DELETE FROM " .. childAttributeTables.players[k].table .. " WHERE " .. childAttributeTables.players[k].idField .. " = '" .. pid[j] .. "';")
					end
				end
			else
				db.executeQuery("DELETE FROM `players` WHERE `id` = '" .. pid[j] .. "';")
			end
		end
    end
	
	--Drop all the accounts that have 0 players linked to them (at the moment its only checking from the list of players removed)
	if config.deleteAccountWithNoPlayers then
		--This part was scripted by Darkhaos, modified/fixed by Teh Maverick --[[
		for acc = 1, #aid do
			result1 = db.getResult("SELECT `id` FROM `accounts` WHERE `id` = '" .. aid[acc] .. "';")
			if result1:getID() ~= -1 then -- check to make sure the account exists
				result1:free()
				for i = 1, #childAttributeTables.accounts do
					--Make sure there are no other players on the account
					result1 = db.getResult("SELECT COUNT(id) as count FROM `players` WHERE `account_id` = '" .. aid[acc] .. "';")
					tmp = result1:getDataInt("count")
					if(tmp <= 0) then
						--Remove accounts
						dropCount.accounts[i] = ((dropCount.accounts[i] or 0) + countRowsWhereInTable(childAttributeTables.accounts[i].table, childAttributeTables.accounts[i].idField, aid[acc]))
						db.executeQuery("DELETE FROM " .. childAttributeTables.accounts[i].table .. " WHERE " .. childAttributeTables.accounts[i].idField .. " = '" .. aid[acc] .. "';")
					end
				end
			end
		end
	end
	--]]
 
	--Print and Save results (configurable)
	local DB_NOW = {players = DB_BEFORE.players - getDBPlayersCount(), accounts = DB_BEFORE.accounts - getDBAccountsCount()}
	if DB_NOW.players > 0 or DB_NOW.accounts > 0 then
		local text = ">> [DBCLEANUP] " .. DB_NOW.players .. " inactive players" .. (config.deleteAccountWithNoPlayers and " and " .. DB_NOW.accounts .. " empty accounts" or "") .. " have been deleted from the database."
 
		--Write to console
		if config.printResult then
			print("")
			print(text)
			if config.cleanChildTables then
				--Write player info
				for i = 1,#dropCount.players do
					print("[!] --> Dropped: " .. dropCount.players[i] .. " from " .. childAttributeTables.players[i].table .. " table")
				end
				--Write account info
				if config.deleteAccountWithNoPlayers then
					for i = 1,#dropCount.accounts do
						print("[!] --> Dropped: " .. dropCount.accounts[i] .. " from " .. childAttributeTables.accounts[i].table .. " table")
					end
				end
				print("")
			end
		end
 
		--Write to file
		if config.saveResultToFile then
 
			local file = io.open("data/logs/"..config.logFileName, "a")
			file:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] " .. text .. "\n")
 
			if config.cleanChildTables then
				--Write player info
				for i = 1, #dropCount.players do
					file:write("[!] --> Dropped: " .. dropCount.players[i] .. " from " .. childAttributeTables.players[i].table .. " table\n")
				end
				--Write account info
				if config.deleteAccountWithNoPlayers then
					for i = 1, #dropCount.accounts do
						file:write("[!] --> Dropped: " .. dropCount.accounts[i] .. " from " .. childAttributeTables.accounts[i].table .. " table\n")
					end
				end
				file:write("\n")
			end
			file:close()
		end
	end
	return true
end

DONE!

So anyway! Enjoy nd hope it is useful to many people

Be sure to comment and thank CyberM just as much as me

(check his one out if you are looking for one that is a little less complicated to noobs haha). Be sure to report bugs!

I find it useful and hopefully the OT community too

Peace!
Mav

agomez · Apr 25, 2011

really nice, but how i can edit this script dont delete acc manager,and samples character?

and itwould be better if you can edit or make another to delete accounts

Kudzu · Apr 25, 2011

thanks

rep ofc

Cykotitan · Apr 25, 2011

AND `id` > 6 AND `group_id` < 2

instead of `id` > 6 use NOT IN ('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample')
and `group_id` = 1 instead of < 2

also, a few queries for cleaning old deaths/kills (use if you have poor Top Guilds/Fraggers query performance)

SQL:

DELETE FROM `player_deaths` WHERE `date` < UNIX_TIMESTAMP() - 7*86400;

DELETE FROM `killers` WHERE `death_id` NOT IN (SELECT `id` FROM `player_deaths` WHERE `player_deaths`.`id`=`killers`.`death_id`);

DELETE FROM `environment_killers` WHERE `kill_id` NOT IN (SELECT `id` FROM `killers` WHERE `killers`.`id`=`environment_killers`.`kill_id`);

DELETE FROM `player_killers` WHERE `kill_id` NOT IN (SELECT `id` FROM `killers` WHERE `killers`.`id`=`player_killers`.`kill_id`);

agomez · Apr 25, 2011

will be like this?

PHP:

result = db.getResult("SELECT `id` FROM `players` WHERE `level` < ".. cleanup[i].level .." AND 'Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample' AND `group_id` = 1 AND `lastlogin` < UNIX_TIMESTAMP() - ".. cleanup[i].time ..";")
		if(result:getID() ~= -1) then
			ii = 1
			repeat

BeniS · Apr 26, 2011

Cykotitan said:
instead of `id` > 6 use IN ('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample')
and `group_id` = 1 instead of < 2

also, a few queries for cleaning old deaths/kills (use if you have poor Top Guilds/Fraggers query performance)

SQL:

DELETE FROM `player_deaths` WHERE `date` < UNIX_TIMESTAMP() - 7*86400; DELETE FROM `killers` WHERE `death_id` NOT IN (SELECT `id` FROM `player_deaths` WHERE `player_deaths`.`id`=`killers`.`death_id`); DELETE FROM `environment_killers` WHERE `kill_id` NOT IN (SELECT `id` FROM `killers` WHERE `killers`.`id`=`environment_killers`.`kill_id`); DELETE FROM `player_killers` WHERE `kill_id` NOT IN (SELECT `id` FROM `killers` WHERE `killers`.`id`=`player_killers`.`kill_id`);

If you read my comment in the code, you will see my reason for not removing from the player deaths and kills.

--Also I did not want to put killers, or deaths on here because that is historic data,

agomez said:

will be like this?

PHP:

result = db.getResult("SELECT `id` FROM `players` WHERE `level` < ".. cleanup[i].level .." AND 'Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample' AND `group_id` = 1 AND `lastlogin` < UNIX_TIMESTAMP() - ".. cleanup[i].time ..";")
		if(result:getID() ~= -1) then
			ii = 1
			repeat

No you use the NOT IN clause. I'll update it later

it still works fine like that though. The statement was taken from CyberM's 1.1 version, and I didn't bother editing it.

agomez said:
really nice, but how i can edit this script dont delete acc manager,and samples character?
and itwould be better if you can edit or make another to delete accounts

Theoretically it shouldn't delete them, but get my latest version I just updated now.

Updated main post: to properly avoid removing Samples and Account Manager.

Kippetjee · Apr 26, 2011

Have Rooksamples protected??

BeniS · Apr 26, 2011

skillboy said:
Have Rooksamples protected??

Yes, I tested it.

Doggynub · Apr 26, 2011

Humm,Advanced auto db clean

BeniS · Apr 26, 2011

Doggynub said:
Humm,Advanced auto db clean

haha never saw this one

mines more customizable though \../ I should consider adding the account removing too, but it's not really something any 'company' should do

Darkhaos · Apr 26, 2011

Teh Maverick said:
haha never saw this one mines more customizable \../ though I should consider adding the account moving too

I was editing your script to do that, after deleting the players, script will check accounts where players has been deleted, script will check if the account has no players and will delete it and account_viplist

But i'll post it here to don't stole credits to you

BeniS · Apr 26, 2011

darkhaos said:
I was editing your script to do that, after deleting the players, script will check accounts where players has been deleted, script will check if the account has no players and will delete it and account_viplist

But i'll post it here to don't stole credits to you

haha sweet bro, saves me some time

Though I personally wouldn't remove someones account from the database, ever. But meh we can make it customizable.

Darkhaos · Apr 26, 2011

Teh Maverick said:
haha sweet bro, saves me some time Though I personally wouldn't remove someones account from the database, ever. But meh we can make it customizable.

Is a nice feature because if an account doesn't have character.. is trash

Doggynub · Apr 26, 2011

True, same idea i got when i did it

Cykotitan · Apr 26, 2011

Teh Maverick said:
If you read my comment in the code, you will see my reason for not removing from the player deaths and kills.

Yes, but on servers which have several deaths per minute (that means even more space for storing killers), it becomes inefficient to store deaths/killers for up to 30 days. Uhm, do they even remove automatically?!

BeniS · Apr 26, 2011

Cykotitan said:
Yes, but on servers which have several deaths per minute (that means even more space for storing killers), it becomes inefficient to store deaths/killers for up to 30 days. Uhm, do they even remove automatically?!

haha that's true. Not sure what happens to them after they are past the date, but the data should be kept as record I guess.

Darkhaos · Apr 26, 2011

Lua:

------------------------------------------------------------------------------*
----- [[> Automated Database Cleanup 1.1 Structure //By Cybermaster <]] ------|
-------------- [[> System 2.0 Revamped by Teh Maverick <3 <]] ----------------|
--------------- [[> Function getDBPlayersCount() by Elf <]] ------------------|
------------------------------------------------------------------------------|
------------------------------------------------------------------------------|
--- ~!READ THIS!~ ------------------------------------------------------------|
--- Be sure to back up your database and test this on your server first, -----|
--- I(Teh Maverick) cannot guarantee it will work the same for every core. ---|
--- It is very easy to test, with the log file and values that are printed ---|
-----------------------------------Enjoy!-------------------------------------|
------------------------------------------------------------------------------*
 
function countRowsWhereInTable(table, field, condition)
    local result = db.getResult("SELECT COUNT(" .. field .. ") as count FROM " .. table .. " WHERE " .. field .. " = '" .. condition .. "';")
    local tmp = result:getDataInt("count")
    result:free()
    return tmp
end
 
function getDBPlayersCount()
    local result = db.getResult("SELECT COUNT(id) as count FROM players;")
    local tmp = result:getDataInt("count")
    result:free()
    return tmp
end

function getDBAccountsCount()
    local result = db.getResult("SELECT COUNT(id) as count FROM accounts;")
    local tmp = result:getDataInt("count")
    result:free()
    return tmp
end
 
function onStartup()
	local DB_BEFORE = {players = getDBPlayersCount(), accounts = getDBAccountsCount()}
	local result, ii, numPlayersToDelete, numAccountsDeleted = 0, 0, 0, 0
	local pid, dropCount, accountDropCount = {}, {}, {}
 
	local config = {
		deleteAccountWithNoPlayers = true,
		cleanChildTables = true,
		printResult = true,
		saveResultToFile = true,
		logFileName = 'db_cleanup.txt'
	}
 
    --In each table, players with below specified level, and days of inactivity will be deleted from db on server startup
	local cleanup = {
		[1] = {level = 11, time = 5 * 24 * 60 * 60},
		[2] = {level = 20, time = 15 * 24 * 60 * 60},
		[3] = {level = 50, time = 30 * 24 * 60 * 60},
		[4] = {level = 100, time = 60 * 24 * 60 * 60},
		[5] = {level = 150, time = 90 * 24 * 60 * 60}
	}
 
	local childAttributeTables = {
		[1] = {table = "`player_viplist`", idField = "`player_id`"},
		[2] = {table = "`player_storage`", idField = "`player_id`"},
		[3] = {table = "`player_spells`", idField = "`player_id`"},
		[4] = {table = "`player_skills`", idField = "`player_id`"},
		[5] = {table = "`player_namelocks`", idField = "`player_id`"},
		[6] = {table = "`player_items`", idField = "`player_id`"},
		[7] = {table = "`player_depotitems`", idField = "`player_id`"},
		[8] = {table = "`players`", idField = "`id`"},
		[9] = {table = "`accounts`", idField = "`id`"}, 
		[10] = {table = "`account_viplist`", idField = "`account_id`"} -- Keep this as the last item in the array
		--Note: `houses` and `bans` are in the DB triggers for TFS so don't worry about them.
		--Also I did not want to put killers, or deaths on here because that is historic data,
		--do so at your ouwn risk.
	}
 
	for i = 1, #cleanup do
		result = db.getResult("SELECT `id`,`name` FROM `players` WHERE `level` < ".. cleanup[i].level .." AND `name` NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND `group_id` < 2 AND `lastlogin` < UNIX_TIMESTAMP() - ".. cleanup[i].time ..";")
		if(result:getID() ~= -1) then
			ii = 1
			repeat
				pid[ii] = result:getDataInt("id") -- list the players id into an array
				ii = ii + 1
			until not(result:next())
			result:free()
		end
		numPlayersToDelete = ii - 1
 
		--Drop players and their child table attribute data such as skills, items, etc.
		local quer
		for j = 1, numPlayersToDelete do
 
			quer = db.getResult("select account_id from players where id = " .. pid[j] .. ";")
			if quer:getID() ~= -1 then
				accountDropCount[j] = quer:getDataInt("account_id")
			end
			if(config.cleanChildTables == true) then
				for k = 1, #childAttributeTables do
					dropCount[k] = ((dropCount[k] or 0) + countRowsWhereInTable(childAttributeTables[k].table, childAttributeTables[k].idField, pid[j]))
					--print(dropCount[k])
					db.executeQuery("DELETE FROM " .. childAttributeTables[k].table .. " WHERE " .. childAttributeTables[k].idField .. " = '" .. pid[j] .. "';")
				end
			else
				db.executeQuery("DELETE FROM players WHERE id = '" .. pid[j] .. "';")
			end

			--This part was scripted by Darkhaos --[[
			if deleteAccountWithNoPlayer then
				for acc = 1, #accountDropCount do
					quer = db.getResult("select count(id) from players where account_id = " .. accountDropCount[acc] .. ";")
					if quer:getID() ~= -1 then
						for i = #childAttributeTables -1, #childAttributeTables do
							db.executeQuery("delete from " .. childAttributeTables[i].table .. " where " .. childAttributeTables[i].idField .. " = " .. accountDropCount[acc] .. ";")
							dropCount[i] = dropCount[i] + 1
						end
					end
				end
			end
			--]]				
		end
    end
 
	--Print and Save results (configurable)
	local DB_NOW = {players = DB_BEFORE.players - getDBPlayersCount(), accounts = DB_BEFORE.accounts - getDBAccountsCount()}
	if DB_NOW.players > 0 or DB_NOW.accounts > 0 then
		local text = ">> [DBCLEANUP] " .. DB_NOW.players .. " inactive players and " .. DB_NOW.accounts .. " have been deleted from the database."
 
		--Write to console
		if config.printResult then
			print(text)
			if config.cleanChildTables then
				for i = 1,#dropCount do
					print("[!] --> Dropped: " .. dropCount[i] .. " from " .. childAttributeTables[i].table .. " table")
					if(i == #dropCount) then
						print("")
					end
				end
			end
		end
 
		--Write to file
		if config.saveResultToFile then
 
			local file = io.open("data/logs/"..config.logFileName, "a")
			file:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] " .. text .. "\n")
 
			if config.cleanChildTables then
				for i = 1, #dropCount do
					file:write("[!] --> Dropped: " .. dropCount[i] .. " from " .. childAttributeTables[i].table .. " table\n")
					if i == #dropCount then
						file:write("\n")
					end
				end
			end
			file:close()
		end
	end
	return true
end

Here's

BeniS · Apr 26, 2011

darkhaos said:
Here's

Sweet, tested it? I'll add it after I've tested it

Jano · Apr 26, 2011

trigger is faster

BeniS · Apr 26, 2011

Jano said:
trigger is faster

Yea, but actually it can be worse for the performance of your database (probably not for the removing of players, seeing as you don't remove players often), but if you use a trigger its removing the values in realtime when its deleted, so if you have something removed from the database frequently while the server is running then the trigger can be worse in certain cases, in terms of performance.

I personally like to have control over conditioning what I remove, this script can help with that. Besides, performance of startups aren't exactly an issue.

EDIT: I've found out the only real issue with doing it this way is if the server crashes part-way through your clean, it can be bad. I can fix the script to drop it all in one controlled query at the end of the script sometime.

GlobalEvent 2.0 Database Cleaner - Inactive Players + The Players Child Data (skills, items, etc)

Advanced OT User

Member

Active Member

Experienced G'

Member

Advanced OT User

Member

Advanced OT User

LUA / C++

Advanced OT User

(:

Advanced OT User

(:

LUA / C++

Experienced G'

Advanced OT User

(:

Advanced OT User

oturhaN

Advanced OT User

Similar threads