GlobalEvent Automated Database Cleanup 1.1, the solution for inactive players

[hodleo]

Well people, this time I give you this very useful script which cleans up your database from inactive players who have a long time offline. This works like the Nicaw AAC's.

But you must remember that the script will delete players in db with id > 6 so your Acc Manager and Player Samples have to be in 0 - 6 id row. MAKE A BACKUP OF YOUR DB before testing this script, although it will work fine.

What does this do? It deletes inactive players on server startup below specified level and time. It won't delete Account Manager neither player samples. nor players with group_id above 1.

Improvements and suggestions to my script are very welcomed. I credit Elf for the getDBPlayersCount() function.

Players below level with days offline will be deleted:
LEVEL_______DAYS OFFLINE
11 ----------------------5
20 ---------------------15
50 ---------------------30
100 --------------------60
150 --------------------90

globalevents.xml
Lua Code:

<globalevent name="dbclean" type="start" event="script" value="dbclean.lua"/>​

globalevents/scripts/dbclean.lua

-- [[> Automated Database Cleanup 1.1 //By Cybermaster <]] --
-- [[> Function getDBPlayersCount() by Elf <]] --

function getDBPlayersCount()
    local result = db.getResult("SELECT COUNT(`id`) as `count` FROM `players`;")
    local tmp = result:getDataInt("count")
    result:free()
    return tmp
end

function onStartup()
    local DB_BEFORE = getDBPlayersCount()

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

    for i = 1, #cleanup do
        db.executeQuery("DELETE FROM `players` WHERE `level` < ".. cleanup[i].level .." AND `id` > 6 AND `group_id` < 2 AND `lastlogin` < UNIX_TIMESTAMP() - ".. cleanup[i].time ..";")
    end
        
    local DB_NOW = DB_BEFORE - getDBPlayersCount()
    if DB_NOW > 0 then
        local text = ">> [DBCLEANUP] "..DB_NOW.." inactive players have been deleted from database."
        print("" .. text .. "")
        local file = io.open("data/logs/db_cleanup.txt", "a")
        file:write("\n[".. os.date("%d %B %Y %X ", os.time()) .."] "..text.."")
        file:close()
    end
return true
end

 

[Cykotitan]

    for i = [B][COLOR="Red"]1[/COLOR][/B], #cleanup do

It won't loop through [0] (var with index 0)

 

[hodleo]

oh yeah, my noob(my bad)
is it correct like now?

 

[Cykotitan]

"ok"

 

[HeberPcL]

Hey, nice!

=p

 

[hodleo]

thanks :]
- >

 

[Technic]

Nice, but i prefer cron

 

[hodleo]

what is cron?

 

[chojrak]

cron (Unix) – Wikipedia, wolna encyklopedia

 

[hodleo]

wha's the difference then

 

[chojrak]

You can setup cron with MySQL query instead of stupid script xP

 

[hodleo]

yeah, whatvr

 

[Syntax]

its neither stupid nor useless, its actually nice since most people in the community are familiar with Lua Scripting, that way they can edit and modify it how they please, plus run and add it to their server with ease

 

[elf]

First of all- why do you DELETE them? Second: getDBCountblabla - DB_BEFORE? That will give a negative result, lol. DB_BEFORE - getBLABLACount.

 

[hodleo]

First of all- why do you DELETE them? Second: getDBCountblabla - DB_BEFORE? That will give a negative result, lol. DB_BEFORE - getBLABLACount.

1. inactive players, and db maintenance
2. yeah, again my bad


there! the script has been updated
now i added a log where deletions are registered

 

[Guitar Freak]

its neither stupid nor useless, its actually nice since most people in the community are familiar with Lua Scripting, that way they can edit and modify it how they please, plus run and add it to their server with ease

+1 And not everyone uses nix here (regardless if they should or not).

Seriously, I dont know when it f*cking happened, but this forum is starting to get VERY annoying when it comes to releasing stuff.

Almost 90% of the time I see people releasing their work for free here, all I see on the replies is noobs "too full of themselves" bashing the releaser with mean comments, flames and sh*t like that, instead of atleast wasting their typing time on making a constructive criticism for him or if you're not going to do that neither thank him, just dont f*cking post at all.

He's giving his own work without charging anything in return or claiming he's the supergod of super uber scripting, so whats up with the blind flaming rage? Damn.

Sorry for the faulty language, but this pisses me off big time.

+Rep to you Cyber.

 

[hodleo]

thanks, gfreak

 

[kito2]

Pretty cool!
THIS ROX LOVE YOU AND REPPED!

What happens on level 150+, it is 90 days?

 

[Sync]

I. Personally like the script and the idea but the fact that ur doing it basically for rep and basically pushon kids to rep you just ruins it for me.

 

[kito2]

And what you care if he wants rep?