Here is the second version of my Anti-Dupe system that you can see here http://otland.net/f81/basic-anti-dupe-system-131898/
This new script brings a better configuration, now it checks the tables that you want and some optimizations made.
//TODO
First of all, you need to paste this function at your lib/functions.lua:
Now, everytime that do you want to set an unique serial to an item, just use:
This is the script that will delete items with duplicated serial when serer starts:
Create a file of globalevents/scripts called trackitems.lua and paste:
Also you can see items with serial from phpMyAdmin with this command:
And you can see items with duplicated serials with the following commands:
Note that you can change the table 'player_items' to 'player_depotitems' to get info about items on depot
Please, comment with constructive criticism !
Rep++ is appreciated!
This new script brings a better configuration, now it checks the tables that you want and some optimizations made.
//TODO
- You can configure the tables that will be checked.
- It saves the info on a log file, declared by yourself.
- When a duplicated item is deleted, scripts prints it on console, showing the player who has the item, item-id, count and serial of the item.
- With the function 'generateSerial()' you can set an unique serial to every item.
First of all, you need to paste this function at your lib/functions.lua:
Lua:
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
Now, everytime that do you want to set an unique serial to an item, just use:
Lua:
doItemSetAttribute(uid, "serial", generateSerial())
This is the script that will delete items with duplicated serial when serer starts:
Create a file of globalevents/scripts called trackitems.lua and paste:
Lua:
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
XML:
<globalevent name="trackitems" type="start" event="script" value="trackitems.lua"/>
SQL:
SELECT * , SUBSTRING( CONVERT( attributes
USING latin1 )
FROM 18 ) AS 'serial'
FROM player_items
WHERE CONVERT( attributes
USING latin1 ) LIKE '%serial%'
And you can see items with duplicated serials with the following commands:
SQL:
--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
Note that you can change the table 'player_items' to 'player_depotitems' to get info about items on depot
Please, comment with constructive criticism !
Rep++ is appreciated!
Last edited: