GlobalEvent Automated Database Cleanup 1.1, the solution for inactive players

[kito2]

wat the shouldn't be like that
i guess i have to update this too <_<

It works pretty good, and keeps having my db pretty clean xD

i guess i should make it like that

Lert me help you:

function getLastLoginDiff(name) 
    if type(name) ~= "string" then 
        return name 
    end 
     
    local res = db.getResult("SELECT `lastlogin` FROM `players` WHERE `name` = '"..name.."';") 
    if res:getID() ~= -1 then 
        return os.time()-res:getDataInt('lastlogin') 
    end 
end 

local days = 7

function onStartup() 
    local guild = db.getResult("SELECT `id`, `ownerid` FROM `guilds`;") 
    if guild:getID() == -1 then 
        return true 
    end 
    repeat 
        local id, owner = guild:getDataInt("id"), guild:getDataInt("ownerid") 
        local diff = getLastLoginDiff(getPlayerNameByGUID(owner)) 
        if diff >= 7*24*60*60 then 
            db.executeQuery("DELETE FROM `guilds` WHERE `id` = "..id..";") 
        end 
    until not(guild:next()) 
    guild:free() 
    return true 
end

I think that you made that script once... It clean guilds for leader who hasn't logged in on a few days, but if leader have a name with ', it gives errors, maybe you could add this to this script.

 

[Collz]

This is very useful. I feel bad for those who come back a month later to find their character deleted

 

[kito2]

Hahaha, agreed with you xD

 

[Donio]

will this work with 0.3.6`?

 

[nightshade]

i'm using this, very usefull

 

[Najiwe23]

Nice, but anyway inactive players aint a real big problem. Databases don't consume much space.

 

[lizudus]

HOW I CAN DO THIS ERASE WITHOUT ERASE SAMPLES LIKE Rook Sample, Sorcerer Sample, Druid sample... etc??

 

[lizudus]

bu the post!

 

[lizudus]

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

i want this script BUT Ignore samples example, rook sample, sorcerer sample, knight sample, paladin sample, druid sample any help XD?

 

[hodleo]

I will open this thread again when I update the script.