• TibiaFace

    Tibiaface | Una comunidad Open Tibia donde encontras : mapas, scripts, Otserver, npc y amigos etc ...

    .
    demo menumenu

    Afiliados



    Votar:

    [Sistema] Anti-Dupe - Delete all items con duplicated serial de tu database.

    Compartir:

    Ver el tema anterior Ver el tema siguiente Ir abajo  Mensaje (Página 1 de 1.)

    [Admin] God Maya

    avatar
    Administrador
    Administrador
    Hola usuarios de tibiaface


    este sistema verifica si un item ha sido duplicado y lo eliminara

    lib/functions.lua:

    Código:
    function generateSerial()
        local strings = "AaBbCcDdEeFfGgHhIiJjKkLlMmOoPpQqRrSsTtUuVvWwXxYyZz"
        local newSerialStr, newSerialInt = "", 0
        local newSerial = ""
        local query, query2, query3
        repeat
            for k = 1, math.random(1, 10) do
                local l = math.random(1, string.len(strings))
                newSerialStr = newSerialStr .. string.sub(strings, l, l)
            end
            newSerialInt = math.random(999999)
            newSerial = newSerialStr .. "-" .. newSerialInt
            query = db.getResult("select * from player_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
            query2 = db.getResult("select * from player_depotitems where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
            query3 = db.getResult("select * from tile_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
        until query:getID() == -1 and query2:getID() == -1 and query3:getID() == -1
        return "!" .. newSerial
    end

    Ahora, cada vez que desee establecer una serial única para un artículo, simplemente use:

    Código:
    doItemSetAttribute(uid, "serial", generateSerial())

    globalevents/scripts copie cualquier archivo y renombre por trackitems.lua y pegue esto dentro

    Código:
    tablesToCheck = {"player_items", "player_depotitems", "tile_items", {"player_items", {"player_depotitems", "tile_items"}}, {"player_depotitems", "tile_items"}}
     
    function onStartup()
        local text, final = "", ""
        local filex = "data/logs/duplicated.txt"
        local f = io.open(filex, "a+")
        local count = 0
        for i = 1, table.maxn(tablesToCheck) do
            if type(tablesToCheck[i]) == "string" then
                local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 1)")
                if query:getID() ~= -1 then
                    while(true) do
                        local delete = db.executeQuery("delete from " .. tablesToCheck[i] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " and player_id = " .. query:getDataInt("player_id") .. ";")
                        text = "[!] -> Deleting items with duplicated serial from '" .. tablesToCheck[i] .. "': [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
                        count = (delete and count + 1 or count)
                        final = final .. (final ~= "" and "\n" or "") .. text
                        print(text)
                        if not query:next() then break end
                    end
                end
            else
                if type(tablesToCheck[i][2]) == "string" then
                    local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][2] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
                    if query:getID() ~= -1 then
                        while(true) do
                            local query_ = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][2] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][1] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
                            local delete = db.executeQuery("delete from " .. tablesToCheck[i][1] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
                            count = (delete and count + 1 or count)
                            local delete2 = db.executeQuery("delete from " .. tablesToCheck[i][2] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
                            count = (delete2 and count + 1 or count)
                            text = "[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][1] .. "' [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!") ..
                            "\n[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][2] .. "' [Player: " .. getPlayerNameByGUID(query_:getDataInt("player_id")) .. ", Item: " .. query_:getDataInt("itemtype") .. ", Count: " .. query_:getDataInt("count") .. ", Serial: " .. query_:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
                            final = final .. (final ~= "" and "\n" or "") .. text
                            print(text)
                            if not query:next() then break end
                        end
                    end
                else
                    for j = 1, #tablesToCheck[i][2] do
                        local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][2][j] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
                        if query:getID() ~= -1 then
                            while(true) do
                                local query_ = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][2][j] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][1] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
                                local delete = db.executeQuery("delete from " .. tablesToCheck[i][1] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
                                count = (delete and count + 1 or count)
                                local delete2 = db.executeQuery("delete from " .. tablesToCheck[i][2][j] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
                                count = (delete2 and count + 1 or count)
                                text = "[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][1] .. "' [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!") ..
                                "\n[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][2][j] .. "' [Player: " .. getPlayerNameByGUID(query_:getDataInt("player_id")) .. ", Item: " .. query_:getDataInt("itemtype") .. ", Count: " .. query_:getDataInt("count") .. ", Serial: " .. query_:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
                                final = final .. (final ~= "" and "\n" or "") .. text
                                print(text)
                                if not query:next() then break end
                            end
                        end
                    end
                end
            end
        end
        if f ~= nil then
            f:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] >> [Anti-Dupe] " .. count .. " duplicated items have been deleted from the database.\n" .. (final == "" and "[!] -> No duplicated item was found in the database" or final) .. "\n\n")
            f:close()
        else
            print("[!] -> [Anti-Dupe] Cannot save info to file!")
        end
        return true
    end
     

    en data/globalevents/gobaleEvents.xml

    Código:
       <globalevent name="trackitems" type="start" event="script" value="trackitems.lua"/>

    También puedes ver elementos con serial de phpMyAdmin con este comando:

    Código:
    SELECT * , SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 ) AS  'serial'
    FROM player_items
    WHERE CONVERT( attributes
    USING latin1 ) LIKE  '%serial%'



    Y puede ver elementos con comandos serial duplicadas con los siguientes comandos:

    Código:
    --Use this command to see duplicated items on players inventory
    SELECT * , SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 ) AS  'duplicated serials'
    FROM player_items
    WHERE SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    IN (
     
    SELECT SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    FROM player_items
    WHERE CONVERT( attributes
    USING latin1 ) LIKE  '%serial%'
    GROUP BY SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    HAVING COUNT( * ) >1
    )
     
    --Use this command to see duplicated items on players inventory or depot items
    SELECT * , SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 ) AS 'duplicated serials'
    FROM player_items
    WHERE SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    IN (
     
    SELECT SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    FROM player_depotitems
    WHERE CONVERT( attributes
    USING latin1 ) LIKE '%serial%'
    GROUP BY SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    HAVING COUNT( * ) >1
    )
     
    --Use this command to see duplicated items on players depots
    SELECT * , SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 ) AS 'duplicated serials'
    FROM player_depotitems
    WHERE SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    IN (
     
    SELECT SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    FROM player_depotitems
    WHERE CONVERT( attributes
    USING latin1 ) LIKE '%serial%'
    GROUP BY SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    HAVING COUNT( * ) >1
    )
     
    --Use this command to see duplicated items on player_items and player_depotitems (Example of usage)
    --Use this command to see duplicated items on players depots
    SELECT * , SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 ) AS 'duplicated serials'
    FROM player_items
    WHERE SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    IN (
     
    SELECT SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    FROM player_depotitems
    WHERE CONVERT( attributes
    USING latin1 ) LIKE '%serial%'
    GROUP BY SUBSTRING( CONVERT( attributes
    USING latin1 )
    FROM 18 )
    HAVING COUNT( * ) >0
     


    crear data/logs/duplicated.txt

    en data del servidor crean una carpeta llamada logs y dentro de ella un archivo llamado duplicated.txt



    Ver perfil de usuario http://www.tibiaface.com

    Ver el tema anterior Ver el tema siguiente Volver arriba  Mensaje (Página 1 de 1.)

    Permisos de este foro:
    No puedes responder a temas en este foro.

     

    BienvenidosTibiaFace es una comunidad de Open Tibia. Para participar debes estar registrado (click para Regístrate).