Advanced Auto-DB Clean

Discussion in 'GlobalEvents, Spells & CreatureEvents' started by Doggynub, Nov 9, 2010.

  1. Doggynub

    Doggynub LUA / C++

    Joined:
    Sep 28, 2008
    Messages:
    2,541
    Likes Received:
    175
    Best Answers:
    0
    Advanced Auto-DB Clean:

    * Features :
    1- Ability to Delete accounts on a time of inactive (deleting acc empty of players).
    2- Ability to delete inactive players.
    3- Ability to delete all players data when they are deleted.
    4- prevent to delte players who havnt logged yet, or their lastlog not yet created.
    5- prevent of deleting of Toutor's +, and acc manager and samples.
    6- Ability to write logs in external file to show character deleted with names and the account deleted ,time indicated.
    7- Ability to write on console how much player and acc deleted on this startup.

    *When it works :
    This will work on server start, it will delete players first who are inactive, if all players from an account was deleted then after that the clean account empty of players will start working till it clean db from them.


    * Now lets install it :
    Goto globalevent --> globalevent.xml :


    Code (XML):
    1. <globalevent name="db" type="startup" event="script" value="db.lua"/>
    Now go into globalEvent--> scripts --> make new file "db.lua" paste


    Code (Lua):
    1. --[[ Advanced Auto-DB Clean By >>Doggynub<< ]]--
    2. --[[ tiny little part from : Automated Database Cleanup 1.1 //By Cybermaster ]]--
    3. -- [[ credits for a better loop through database get result to :SpiderOt ]] --
    4.  
    5. local c = {
    6.                     accounts = true ,              -- delete accounts that doenst contain players(same as deleting acc after the time down, as on deleting players that been inactive account will be emty so account will be deleted)
    7.  
    8.                     players = true,             -- delete players who are inactive?(this must be enabled)
    9.  
    10.                     time = 45,                     -- in days
    11.  
    12.                     remove_all = true,             -- remove all players data from data baase whe he is deleted?  
    13.  
    14.                     log_File = true,                -- make a log file state amount of player cleaned in every clean with their names?
    15.  
    16.                     print_console = true -- print amount of player and accounts deleted on each start up
    17.  
    18.                 }
    19.  
    20.  
    21.  
    22. local players = {}
    23. local names = {}
    24. function cleanPlayers()
    25. local Info = db.getResult("SELECT players.id AS id , players.name AS name FROM players WHERE  players.name NOT IN ('Account Manager','Druid Sample','Knight Sample','Paladin Sample','Sorcerer Sample') AND group_id < 2 AND lastlogin > 0 AND lastlogin < UNIX_TIMESTAMP() - '".. (c.time*24*60*60) .."' ;")
    26.  
    27.         if (Info:getID() ~= -1) then
    28.             while true do
    29.             table.insert(players, Info:getDataInt("id"))
    30.             table.insert(names, Info:getDataString("name"))
    31.             if not Info:next() then
    32.                 break
    33.             end
    34.         end
    35.         Info:free()
    36.         end  
    37.         if #players > 0 then
    38.             db.executeQuery("DELETE FROM players WHERE players.id IN ("..table.concat(players,",")..") ;")
    39.             if c.remove_all == true then
    40.                 db.executeQuery("DELETE FROM player_skills WHERE player_id IN ("..table.concat(players,",")..") ;")
    41.                 db.executeQuery("DELETE FROM player_items WHERE player_id IN ("..table.concat(players,",")..") ;")
    42.                 db.executeQuery("DELETE FROM  player_depotitems WHERE player_id IN ("..table.concat(players,",")..") ;")
    43.                 db.executeQuery("DELETE FROM  player_deaths WHERE player_id IN ("..table.concat(players,",")..") ;")
    44.                 db.executeQuery("DELETE FROM  player_storage WHERE  player_id IN ("..table.concat(players,",")..") ;")
    45.                 db.executeQuery("DELETE FROM  player_killers WHERE  player_id IN ("..table.concat(players,",")..") ;")
    46.                 db.executeQuery("DELETE FROM  player_namelocks WHERE  player_id IN ("..table.concat(players,",")..") ;")
    47.                 db.executeQuery("DELETE FROM  player_spells WHERE  player_id IN ("..table.concat(players,",")..") ;")
    48.                 db.executeQuery("DELETE FROM  player_viplist WHERE  player_id IN ("..table.concat(players,",")..") ;")
    49.             end
    50.             if c.print_console == true then
    51.                 print(">> [DB] : "..#players.." players have been removed for not loggin for long time.")
    52.             end
    53.             if c.log_File == true then
    54.                 doWriteLogFile("data/logs/Database clean.txt"," : "..#players.." players cleaned --> (".. table.concat(names,", ")..") \n\n ")
    55.             end
    56.         end
    57.  
    58.  
    59.  
    60. end
    61. local accounts = {}
    62. function cleanDBAcc()
    63.     function getDB()
    64.         local result = db.getResult("SELECT COUNT(`id`) as `count` FROM `accounts`;")
    65.         local tmp = result:getDataInt("count")
    66.         result:free()
    67.         return tmp
    68.     end
    69.     local before = getDB()
    70.     local Info = db.getResult("SELECT accounts.id AS `id` FROM accounts INNER JOIN players ON accounts.id = players.account_id ;")
    71.  
    72.         if (Info:getID() ~= -1) then
    73.             while true do
    74.             table.insert(accounts, Info:getDataInt("id"))
    75.             if not Info:next() then
    76.                 break
    77.             end
    78.         end
    79.         Info:free()
    80.         end  
    81.         db.executeQuery("DELETE FROM accounts WHERE accounts.id NOT IN("..table.concat(accounts,",")..");")
    82.         local new = before - getDB()
    83.         if c.print_console == true then
    84.             if new > 0 then
    85.                 if c.print_console == true then
    86.                     print("\n>>>>[DB] : "..new.." Accounts were deleted\n")
    87.                 end
    88.                 if c.log_File == true then
    89.                     doWriteLogFile("data/logs/Database clean.txt"," : "..new.." accounts cleaned \n\n ")
    90.                 end
    91.             end
    92.         end
    93.     return true
    94. end  
    95. function onStartup()
    96.     cleanPlayers()
    97.     if c.accounts == true then
    98.         cleanDBAcc()
    99.     end
    100.     return true
    101. end
    102.  
    P.S:
    Please make sure to make a backup of db before using , just incase somehtng happened.

    Regards,
    Doggynub
     
    Last edited: Nov 10, 2010
    Tatuy1, imkingran, Venn and 1 other person like this.
  2. GuHB

    GuHB Member

    Joined:
    Dec 14, 2009
    Messages:
    630
    Likes Received:
    9
    Best Answers:
    0
    I'v dont tested it, but, nice work. Its veery usefully.

    Thanks!
     
  3. RoHaN-OTs

    RoHaN-OTs RoHaN-OTs.com

    Joined:
    Jul 28, 2010
    Messages:
    1,590
    Likes Received:
    79
    Best Answers:
    0
    nice 1 :D
     
  4. Winnerandy

    Winnerandy Experienced Web Design'er

    Joined:
    Oct 23, 2008
    Messages:
    2,252
    Likes Received:
    51
    Best Answers:
    0
    Very nice :D
     
  5. Doggynub

    Doggynub LUA / C++

    Joined:
    Sep 28, 2008
    Messages:
    2,541
    Likes Received:
    175
    Best Answers:
    0
    Thanks :p
     
  6. Tibiamakers

    Tibiamakers yourolist.com

    Joined:
    May 24, 2010
    Messages:
    1,377
    Likes Received:
    95
    Best Answers:
    0
  7. Elexonic

    Elexonic Well-Known Member

    Joined:
    Jun 18, 2008
    Messages:
    1,920
    Likes Received:
    49
    Best Answers:
    0
    yeah Nice!
     
  8. cbrm

    cbrm Just another mod Staff Member Global Moderator

    Joined:
    Jan 6, 2009
    Messages:
    6,527
    Likes Received:
    806
    Best Answers:
    0
    Much complete :)
     
  9. Doggynub

    Doggynub LUA / C++

    Joined:
    Sep 28, 2008
    Messages:
    2,541
    Likes Received:
    175
    Best Answers:
    0
    Bumpageeee :D
     
  10. Donio

    Donio Banned User

    Joined:
    Jun 24, 2008
    Messages:
    4,004
    Likes Received:
    14
    Best Answers:
    0
    any1 tested it yet? :p
     
  11. Doggynub

    Doggynub LUA / C++

    Joined:
    Sep 28, 2008
    Messages:
    2,541
    Likes Received:
    175
    Best Answers:
    0
    It is working :p, but as i said just in case somthng happens make a backup of your db in the first time u use it.
     
  12. KnightmareZ

    KnightmareZ Member

    Joined:
    Feb 3, 2008
    Messages:
    607
    Likes Received:
    4
    Best Answers:
    0
    Awesome ; )!

    Thank you
     
  13. kito2

    kito2 mtibia-global.com Just a noob

    Joined:
    Mar 9, 2009
    Messages:
    3,358
    Likes Received:
    90
    Best Answers:
    1
    lol xD
     
  14. TKO

    TKO Syphero Owner!

    Joined:
    Mar 10, 2008
    Messages:
    2,251
    Likes Received:
    26
    Best Answers:
    0
    do this work for the 0.4 dev?
     
  15. Doggynub

    Doggynub LUA / C++

    Joined:
    Sep 28, 2008
    Messages:
    2,541
    Likes Received:
    175
    Best Answers:
    0
  16. TKO

    TKO Syphero Owner!

    Joined:
    Mar 10, 2008
    Messages:
    2,251
    Likes Received:
    26
    Best Answers:
    0
    awesome i will try it:D
     
  17. TKO

    TKO Syphero Owner!

    Joined:
    Mar 10, 2008
    Messages:
    2,251
    Likes Received:
    26
    Best Answers:
    0
    works awesome thanks dude:D
     
  18. Venn

    Venn Member

    Joined:
    Aug 16, 2009
    Messages:
    547
    Likes Received:
    19
    Best Answers:
    0
    it clean also samples ?

    @miss read ;P i see not thx
     
  19. Santi

    Santi Theres no way im stopping

    Joined:
    Aug 29, 2010
    Messages:
    1,975
    Likes Received:
    151
    Best Answers:
    0
    Good work as always.
     
  20. hesham15

    hesham15 New Member

    Joined:
    Feb 11, 2011
    Messages:
    94
    Likes Received:
    1
    Best Answers:
    0
    nice work :)
     

Share This Page

Loading...