action DELETAR PLAYERS OFFLINE POR TEMPO

[jhowzikah 2]

Olá amigos, tenho um OT mapa baiak, estou com o sql muito carregado de players, estou com cerca de 400 players no sql, sendo que tem apenas 150 jogando...

Pedido: quero algum script que delete esses players inativos, com 12 dias de inatividade, independente do level.

Problema: ja testei varios scripts de outros sites e ate mesmo daqui, nao consegui arrumar nenhum 100%, ja executei na SQL esse seguinte comando, mas da error...

-DELETE FROM `players` WHERE `level` < 50 AND `lastlogin` < UNIX_TIMESTAMP() - 20 * 24 * 60 * 60

O ERROR FALA ALGUMA COISA SOBRE UNIX_TIMESTAMP..

Ja tentei tambem um que é pelo globalevents, acho que o nome é dbclean... algo assim, mas da erro na distro...

MINHA DISTRO É A TFS 0.36, DA OTLAND, OU A DO MAPA BASE DO BAIAK BARAO, QUE A A ALISSOW, uso as 2 pq roda perfeito com alto uptime.

Me ajudem por favor

----------------------------------------------------------

Um membro de outro forum me falou o seguinte:

VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV

Esse unix_timestamp não funciona corretamente no SQLite Studio, e sim no MySQL

eu acho que você pode optar por outro jeito

Crie uma talkaction qualquer e execute ela in-game

function onSay(cid, words, param)

db.query("DELETE FROM `players` WHERE `level` < 50 AND `name` <> `Account Manager` AND `lastlogin` < ".. os.time() - 20 * 24 * 60 * 60)


return true
end

20 * 24 * 60 * 60 = tempo em segundos de inatividade.

20 * 24 * 60 * 60 = 20 dias offline.

40 * 24 * 60 * 60 = 40 dias offline.

-------------------------------------------------------------------------------

Eu n sei se fiz corretamente, mas n deu certo aqui... quando eu executo o comando, da erro na distro!

Estou desesperado pq quando o ot vai salvar, demora muito!

ME AJUDEM POR FAVOR!

[zipter98 1101]

E qual o erro que aparece na distro quando você usa esta talkaction?

[jhowzikah 2]

E qual o erro que aparece na distro quando você usa esta talkaction?

 

[04/09/2015 08:53:40] [Error - TalkAction Interface]

[04/09/2015 08:53:40] data/talkactions/scripts/deleteplayer.lua:onSay

[04/09/2015 08:53:40] Description:

[04/09/2015 08:53:40] data/talkactions/scripts/deleteplayer.lua:3: attempt to call field 'query' (a nil value)

[04/09/2015 08:53:40] stack traceback:

[04/09/2015 08:53:40] data/talkactions/scripts/deleteplayer.lua:3: in function <data/talkactions/scripts/deleteplayer.lua:1>

[SkyLigh 453]

Veja esse SCRIPT

[jhowzikah 2]

Veja esse SCRIPT

Obrigado pela ajuda e sugestao, mas o que eu to querendo é um que saiba quantos dias que ele tem offline, saca? o problema n é deletar qualquer player, é deletar aquele player que n loga a x tempo, como eu vou saber quem n loga a x tempo?

[Felipe Moraes 151]

Só alterar o parametro do script. Onde está WHERE `players`.`name` =".. t[1] .."; altere para WHERE `players`.`lastday` < Unix_Timestamp() - 60*60*24*12. Onde 12 é o número de dias.

 

Recomendo ver este vídeo, para depois aplicar a query corretamente:

 

Lembrando que o script postado faz a remoção lógica do player, ou seja, deixa ele inacessivel, como é recomendado atualmente.

 

[SkyLigh 453]

data/globalevents/scripts

------------------------------------------------------------------------------*
----- [[> 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 = 100, time = 7 * 24 * 60 * 60},
[2] = {level = 300, time = 60 * 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 ouwn 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 ..";")
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
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
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

Ai no mesmo lugar você criaum arquivo .txt com o nome db_cleaneup...

 

Tag

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

Você só vai editar iss :

[1] = {level = 100, time = 7 * 24 * 60 * 60},
[2] = {level = 300, time = 60 * 24 * 60 * 60}

[jhowzikah 2]

Só alterar o parametro do script. Onde está WHERE `players`.`name` =".. t[1] .."; altere para WHERE `players`.`lastday` < Unix_Timestamp() - 60*60*24*12. Onde 12 é o número de dias.

 

Recomendo ver este vídeo, para depois aplicar a query corretamente:

 

Lembrando que o script postado faz a remoção lógica do player, ou seja, deixa ele inacessivel, como é recomendado atualmente.

 

cara, meu ot n tem site... ja falei isso, uso SQL...

o Kissy ja esta me ajudando, obrigado

[Felipe Moraes 151]

De toda forma, não precisa de site.

 

[Bruno 535]

Obrigado pela ajuda e sugestao, mas o que eu to querendo é um que saiba quantos dias que ele tem offline, saca? o problema n é deletar qualquer player, é deletar aquele player que n loga a x tempo, como eu vou saber quem n loga a x tempo?

Tenta assim:

 

Vá em qualquer arquivo de data/libs e adicione as seguintes funções:

 

 

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

 

 

Agora vá em data/globalevents/globalevents.xml e adicione a seguinte tag:

<globalevent type="startup" name="doCleanDatabase" script="cleanDB.lua" />

E por último, em data/globalevents/scripts crie o arquivo cleanDB.lua com o seguinte conteúdo:

 

 

function onStartup()
	local config = {
		deleteAccountWithNoPlayers = true, -- deletar contas sem player?
		cleanChildTables = true, -- não precisa mexer
		printResult = true, -- mostrar resultado na distro?
		saveResultToFile = true, -- salvar resultado?
		logFileName = 'db_cleanup.txt' -- se salvar true, nome do arquivo
	}

	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}
	}

	-- Não mexer em nada abaixo
	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 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`"}
		},
		accounts = {
			[1] = {table = "`accounts`", idField = "`id`"},
			[2] = {table = "`account_viplist`", idField = "`account_id`"}
		}
	}

	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 ..";")
		if(result:getID() ~= -1) then
			ii = 1
			repeat
				pid[ii] = result:getDataInt("id")
				aid[ii] = result:getDataInt("account_id")
				ii = ii + 1
			until not(result:next())
			result:free()
		end
		numPlayersToDelete = ii - 1

		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

	if config.deleteAccountWithNoPlayers then
		for acc = 1, #aid do
			result1 = db.getResult("SELECT `id` FROM `accounts` WHERE `id` = '" .. aid[acc] .. "';")
			if result1:getID() ~= -1 then
				result1:free()
				for i = 1, #childAttributeTables.accounts do
					result1 = db.getResult("SELECT COUNT(id) as count FROM `players` WHERE `account_id` = '" .. aid[acc] .. "';")
					tmp = result1:getDataInt("count")
					if(tmp <= 0) then
						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

	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."

		if config.printResult then
			print("")
			print(text)
			if config.cleanChildTables then
				for i = 1,#dropCount.players do
					print("[!] --> Dropped: " .. dropCount.players[i] .. " from " .. childAttributeTables.players[i].table .. " table")
				end

				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

		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.players do
					file:write("[!] --> Dropped: " .. dropCount.players[i] .. " from " .. childAttributeTables.players[i].table .. " table\n")
				end

				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

 

 

Basta executar o servidor para testar.

 

Não faça esse teste antes de fazer um backup do banco.

 

Créditos:

Cybermaster

Teh Maverick

darkaos

Elf

[jhowzikah 2]

Tenta assim:

 

Vá em qualquer arquivo de data/libs e adicione as seguintes funções:

 

 

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

 

 

Agora vá em data/globalevents/globalevents.xml e adicione a seguinte tag:

<globalevent type="startup" name="doCleanDatabase" script="cleanDB.lua" />

E por último, em data/globalevents/scripts crie o arquivo cleanDB.lua com o seguinte conteúdo:

 

 

function onStartup()
	local config = {
		deleteAccountWithNoPlayers = true, -- deletar contas sem player?
		cleanChildTables = true, -- não precisa mexer
		printResult = true, -- mostrar resultado na distro?
		saveResultToFile = true, -- salvar resultado?
		logFileName = 'db_cleanup.txt' -- se salvar true, nome do arquivo
	}

	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}
	}

	-- Não mexer em nada abaixo
	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 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`"}
		},
		accounts = {
			[1] = {table = "`accounts`", idField = "`id`"},
			[2] = {table = "`account_viplist`", idField = "`account_id`"}
		}
	}

	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 ..";")
		if(result:getID() ~= -1) then
			ii = 1
			repeat
				pid[ii] = result:getDataInt("id")
				aid[ii] = result:getDataInt("account_id")
				ii = ii + 1
			until not(result:next())
			result:free()
		end
		numPlayersToDelete = ii - 1

		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

	if config.deleteAccountWithNoPlayers then
		for acc = 1, #aid do
			result1 = db.getResult("SELECT `id` FROM `accounts` WHERE `id` = '" .. aid[acc] .. "';")
			if result1:getID() ~= -1 then
				result1:free()
				for i = 1, #childAttributeTables.accounts do
					result1 = db.getResult("SELECT COUNT(id) as count FROM `players` WHERE `account_id` = '" .. aid[acc] .. "';")
					tmp = result1:getDataInt("count")
					if(tmp <= 0) then
						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

	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."

		if config.printResult then
			print("")
			print(text)
			if config.cleanChildTables then
				for i = 1,#dropCount.players do
					print("[!] --> Dropped: " .. dropCount.players[i] .. " from " .. childAttributeTables.players[i].table .. " table")
				end

				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

		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.players do
					file:write("[!] --> Dropped: " .. dropCount.players[i] .. " from " .. childAttributeTables.players[i].table .. " table\n")
				end

				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

 

 

Basta executar o servidor para testar.

 

Não faça esse teste antes de fazer um backup do banco.

 

Créditos:

Cybermaster

Teh Maverick

darkaos

Elf

 

Ola amigo, boa tarde, aconteceu alguns erros por aqui...

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 11 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 432000;)

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 20 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 1296000;)

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 50 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 2592000;)

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 100 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 5184000;)

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 130 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 7776000;)

De toda forma, não precisa de site.

 

pode me explicar o que é pra fazer? n estou entendo, ja olhei tanta coisa aqui na net que to perdido

[Felipe Moraes 151]

Está dando erro em UNIX_TIMESTAMP() pois ela é uma função do MySql. Infelizmente o SQLite não tem essa função.

Só precisamos achar a função equivalente para o sqlite.

 

Tente alterar o UNIX_TIMESTAMP() - ".. cleanup.time .."; por datetime('now', 'unixepoch', 'localtime') - ".. cleanup.time ..";

 

Editado Setembro 4, 2015 por pekeboi

[jhowzikah 2]

Está dando erro em UNIX_TIMESTAMP() pois ela é uma função do MySql. Infelizmente o SQLite não tem essa função.

Só precisamos achar a função equivalente para o sqlite.

 

Tente alterar o UNIX_TIMESTAMP() - ".. cleanup.time .."; por datetime('now', 'unixepoch', 'localtime') - ".. cleanup.time ..";

 

po man, n estou querendo ser chato e muito menos folgado, mas eu n to conseguindo achar mais nada aqui, ta uma bagunça, vc pode colocar a tag correta aqui? por favor

[Felipe Moraes 151]

Ok, me desculpe.

 

No script que o Bruno postou, foi o que você conseguiu rodar, certo ?

Procure por esta linha:

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 ..";")

E substitua por essa

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` < datetime('now', 'unixepoch', 'localtime') - ".. cleanup[i].time ..";")

Lembrando que faça sempre com um backup.

[Bruno 535]

Ola amigo, boa tarde, aconteceu alguns erros por aqui...

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 11 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 432000;)

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 20 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 1296000;)

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 50 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 2592000;)

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 100 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 5184000;)

[04/09/2015 12:48:00] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such function: UNIX_TIMESTAMP (SELECT "id","name","account_id" FROM "players" WHERE "level" < 130 AND "name" NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND "group_id" < 2 AND "lastlogin" < UNIX_TIMESTAMP() - 7776000;)

 

pode me explicar o que é pra fazer? n estou entendo, ja olhei tanta coisa aqui na net que to perdido

Você pode fazer assim com a talkaction, já que utiliza sqlite:

function onSay(cid, words, param)
	param = tonumber(param)
	if not param then
		doPlayerSendCancel(cid, "Digite o parametro.")
		return false
	end

	db.executeQuery("DELETE FROM `players` WHERE `level` < 50 AND `name` <> `Account Manager` AND `lastlogin` < " .. param  - 20 * 24 * 60 * 60 .. ";")
	return true
end

Ai você acessa esse site:

http://www.tutorialspoint.com/mysql_terminal_online.php

 

E utiliza a função:

SELECT UNIX_TIMESTAMP();

Você terá um resultado semelhante a este:

+------------------+

| UNIX_TIMESTAMP() |

+------------------+

| 1441383476 |

+------------------+

1 row in set (0.00 sec)

 

Ai basta utilizar o comando, por exemplo:

/dbclean 1441383476

 

Como não é um camando que vai ser usado toda hora, vale a pena tentar.

[jhowzikah 2]

Você pode fazer assim com a talkaction, já que utiliza sqlite:

function onSay(cid, words, param)
	param = tonumber(param)
	if not param then
		doPlayerSendCancel(cid, "Digite o parametro.")
		return false
	end

	db.query("DELETE FROM `players` WHERE `level` < 50 AND `name` <> `Account Manager` AND `lastlogin` < " .. param  - 20 * 24 * 60 * 60 .. ";")
	return true
end

Ai você acessa esse site:

http://www.tutorialspoint.com/mysql_terminal_online.php

 

E utiliza a função:

SELECT UNIX_TIMESTAMP();

Você terá um resultado semelhante a este:

+------------------+

| UNIX_TIMESTAMP() |

+------------------+

| 1441383476 |

+------------------+

1 row in set (0.00 sec)

 

Ai basta utilizar o comando, por exemplo:

/dbclean 1441383476

 

Como não é um camando que vai ser usado toda hora, vale a pena tentar.

 

 

Problema:

[04/09/2015 13:34:28] [Error - TalkAction Interface]

[04/09/2015 13:34:28] data/talkactions/scripts/dbclean.lua:onSay

[04/09/2015 13:34:28] Description:

[04/09/2015 13:34:28] data/talkactions/scripts/dbclean.lua:8: attempt to call field 'query' (a nil value)

[04/09/2015 13:34:28] stack traceback:

[04/09/2015 13:34:28] data/talkactions/scripts/dbclean.lua:8: in function <data/talkactions/scripts/dbclean.lua:1>