• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

GlobalEvent Anti-Dupe - Delete all items with duplicated serial from your database.

Hello want to know where I can add that command on my item I want to have the serial.
doItemSetAttribute (uid, "serial", generateSerial ())

I thank
 
Post your shop system here (The script that gives the item)

Or edit your shop system with something like this
Lua:
	local new_item = doCreateItemEx(itemtogive_id, itemtogive_count)
	doItemSetAttribute(new_item, "serial", generateSerial())
	doPlayerAddItemEx(cid, new_item)
 
Code:
-- ### CONFIG ###
-- message send to player by script "type" (types you can check in "global.lua")
SHOP_MSG_TYPE = 19
-- time (in seconds) between connections to SQL database by shop script
SQL_interval = 5
-- ### END OF CONFIG ###
function onThink(interval, lastExecution)
local result_plr = db.getResult("SELECT * FROM z_ots_comunication WHERE `type` = 'login';")
if(result_plr:getID() ~= -1) then
while(true) do
id = tonumber(result_plr:getDataInt("id"))
action = tostring(result_plr:getDataString("action"))
delete = tonumber(result_plr:getDataInt("delete_it"))
cid = getCreatureByName(tostring(result_plr:getDataString("name")))
if isPlayer(cid) == TRUE then
local itemtogive_id = tonumber(result_plr:getDataInt("param1"))
local itemtogive_count = tonumber(result_plr:getDataInt("param2"))
local container_id = tonumber(result_plr:getDataInt("param3"))
local container_count = tonumber(result_plr:getDataInt("param4"))
local add_item_type = tostring(result_plr:getDataString("param5"))
local add_item_name = tostring(result_plr:getDataString("param6"))
local received_item = 0
local full_weight = 0
if add_item_type == 'container' then
container_weight = getItemWeightById(container_id, 1)
if isItemRune(itemtogive_id) == TRUE then
items_weight = container_count * getItemWeightById(itemtogive_id, 1)
else
items_weight = container_count * getItemWeightById(itemtogive_id, itemtogive_count)
end
full_weight = items_weight + container_weight
else
full_weight = getItemWeightById(itemtogive_id, itemtogive_count)
if isItemRune(itemtogive_id) == TRUE then
full_weight = getItemWeightById(itemtogive_id, 1)
else
full_weight = getItemWeightById(itemtogive_id, itemtogive_count)
end
end
local free_cap = getPlayerFreeCap(cid)
if full_weight <= free_cap then
if add_item_type == 'container' then
local new_container = doCreateItemEx(container_id, 1)
local iter = 0
while iter ~= container_count do
doAddContainerItem(new_container, itemtogive_id, itemtogive_count)
iter = iter + 1
end
received_item = doPlayerAddItemEx(cid, new_container)
else
local new_item = doCreateItemEx(itemtogive_id, itemtogive_count)
received_item = doPlayerAddItemEx(cid, new_item)
end
if received_item == RETURNVALUE_NOERROR then
doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, 'You received '.. add_item_name ..' from Thormenta Shop.')
db.executeQuery("DELETE FROM `z_ots_comunication` WHERE `id` = " .. id .. ";")
db.executeQuery("UPDATE `z_shop_history_item` SET `trans_state`='realized', `trans_real`=" .. os.time() .. " WHERE id = " .. id .. ";")
else
doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, ''.. add_item_name ..' from Thormenta Shop is waiting for you. Please make place for this item in your backpack/hands and wait about '.. SQL_interval ..' seconds to get it.')
end
else
doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, ''.. add_item_name ..' from Thormenta Shop is waiting for you. It weight is '.. full_weight ..' oz., you have only '.. free_cap ..' oz. free capacity. Put some items in depot and wait about '.. SQL_interval ..' seconds to get it.')
end
end
if not(result_plr:next()) then
break
end
end
result_plr:free()
end
return TRUE
end
 
Lua:
-- ### CONFIG ###
-- message send to player by script "type" (types you can check in "global.lua")
SHOP_MSG_TYPE = 19
-- time (in seconds) between connections to SQL database by shop script
SQL_interval = 5
-- ### END OF CONFIG ###
function onThink(interval, lastExecution)
	local result_plr = db.getResult("SELECT * FROM z_ots_comunication WHERE `type` = 'login';")
	if(result_plr:getID() ~= -1) then
		while(true) do
			id = tonumber(result_plr:getDataInt("id"))
			action = tostring(result_plr:getDataString("action"))
			delete = tonumber(result_plr:getDataInt("delete_it"))
			cid = getCreatureByName(tostring(result_plr:getDataString("name")))
			if isPlayer(cid) == TRUE then
				local itemtogive_id = tonumber(result_plr:getDataInt("param1"))
				local itemtogive_count = tonumber(result_plr:getDataInt("param2"))
				local container_id = tonumber(result_plr:getDataInt("param3"))
				local container_count = tonumber(result_plr:getDataInt("param4"))
				local add_item_type = tostring(result_plr:getDataString("param5"))
				local add_item_name = tostring(result_plr:getDataString("param6"))
				local received_item = 0
				local full_weight = 0
				if add_item_type == 'container' then
					container_weight = getItemWeightById(container_id, 1)
					if isItemRune(itemtogive_id) == TRUE then
						items_weight = container_count * getItemWeightById(itemtogive_id, 1)
					else
						items_weight = container_count * getItemWeightById(itemtogive_id, itemtogive_count)
					end
					full_weight = items_weight + container_weight
				else
					full_weight = getItemWeightById(itemtogive_id, itemtogive_count)
					if isItemRune(itemtogive_id) == TRUE then
						full_weight = getItemWeightById(itemtogive_id, 1)
					else
						full_weight = getItemWeightById(itemtogive_id, itemtogive_count)
					end
				end
				local free_cap = getPlayerFreeCap(cid)
				if full_weight <= free_cap then
					if add_item_type == 'container' then
						local new_container = doCreateItemEx(container_id, 1)
						local iter = 0
						while iter ~= container_count do
							doAddContainerItem(new_container, itemtogive_id, itemtogive_count)
							iter = iter + 1
						end
						received_item = doPlayerAddItemEx(cid, new_container)
					else
						local new_item = doCreateItemEx(itemtogive_id, itemtogive_count)
						doItemSetAttribute(new_item, "serial", generateSerial())
						received_item = doPlayerAddItemEx(cid, new_item)
					end
					if received_item == RETURNVALUE_NOERROR then
						doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, 'You received '.. add_item_name ..' from Thormenta Shop.')
						db.executeQuery("DELETE FROM `z_ots_comunication` WHERE `id` = " .. id .. ";")
						db.executeQuery("UPDATE `z_shop_history_item` SET `trans_state`='realized', `trans_real`=" .. os.time() .. " WHERE id = " .. id .. ";")
					else
						doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, ''.. add_item_name ..' from Thormenta Shop is waiting for you. Please make place for this item in your backpack/hands and wait about '.. SQL_interval ..' seconds to get it.')
					end
				else
					doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, ''.. add_item_name ..' from Thormenta Shop is waiting for you. It weight is '.. full_weight ..' oz., you have only '.. free_cap ..' oz. free capacity. Put some items in depot and wait about '.. SQL_interval ..' seconds to get it.')
				end
			end
			if not(result_plr:next()) then
				break
			end
		end
		result_plr:free()
	end
	return TRUE
end

For now it gives serial to items that isn't in containers.
 
Thanks bro.. I just got a problem here..

No console errors... nothing.

I created a subtable (serial) at player_items. And when I buy the item no serial is generated ;/

Could you add me on MSN or some chat tool?
[email protected]

Thanks.
 
Thanks bro.. I just got a problem here..

No console errors... nothing.

I created a subtable (serial) at player_items. And when I buy the item no serial is generated ;/

Could you add me on MSN or some chat tool?
[email protected]

Thanks.

Serial is saved at items attributes, subtable 'attributes'
 
it wouldn't works with rings and others itens that already have atributes, because it's static, always search GROUP BY SUBSTRING( CONVERT( attributesUSING latin1 ) FROM 18 )
 
There's some way to it works with items that already have attributes like attack, defense, description, weight, etc?
 
Darkhaos But, to play the item in a home or on the ground he lost the serial?

I tested this and it happened, my doing something wrong or is it a bug?
 
[22:28:09.416] mysql_real_query(): SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' 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) - MYSQL ERROR: Lost connection to MySQL server during query (2013)

Was using for a while... Now I got this error... Any idea?
 
Made a few fixes to this:
  • Removes all duplicated items but 1 (instead of them all).
  • Fixed possible memory leaks with db connection (wasn't freeing queries).
  • Tidy a few things off, not going to create a new local inside the loops.
  • Some text format changes.

Dunno if Darkhaos minds, but here's a tweaked version for anyone that wants the above features.

Lua:
--[[ 
	Known issues: 
	-it wouldn't works with rings and others itens that already have atributes, 
	because it's static, always search GROUP BY SUBSTRING( CONVERT( attributesUSING latin1 ) FROM 18 )
	
	-This error can occur,
	[22:28:09.416] mysql_real_query(): SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 
	'track' 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) - MYSQL ERROR: Lost connection to MySQL server during query (2013)
]]

function onStartup()
	
	tablesToCheck = {"player_items", "player_depotitems", "tile_items", {"player_items", {"player_depotitems", "tile_items"}}, {"player_depotitems", "tile_items"}}
	local text, final = "", ""
	local filex = "data/logs/duplicated.txt"
	local f = io.open(filex, "a+")
	local delete, delete2, query, query_ = nil, nil, nil, nil
	local count, tmpCount = 0, 0
	
	for i = 1, table.maxn(tablesToCheck) do
		if type(tablesToCheck[i]) == "string" then
			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
				tmpCount = query:getRows()
				while(true) do
					delete = db.executeQuery("DELETE FROM " .. tablesToCheck[i] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
					count = (delete and count + 1 or count)
					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!")
					final = final .. (final ~= "" and "\n" or "") .. text
					tmpCount = tmpCount - 1
					if (not query:next() or tmpCount <= 1) then break end
				end
				query:free()
			end
		else
			if type(tablesToCheck[i][2]) == "string" then
				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
					tmpCount = query:getRows()
					while(true) do
						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)")
						delete = db.executeQuery("DELETE FROM" .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
						count = (delete and count + 1 or count)
						delete2 = db.executeQuery("DELETE FROM " .. tablesToCheck[i][2] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
						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
						tmpCount = tmpCount - 1
						if (query_:getID() ~= -1) then query_:free() end
						if (not query:next() or tmpCount <= 1) then break end
					end
					query:free()
				end
			else
				for j = 1, #tablesToCheck[i][2] do
					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
						tmpCount = query:getRows()
						while(true) do
							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)")
							delete = db.executeQuery("DELETE FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
							count = (delete and count + 1 or count)
							delete2 = db.executeQuery("DELETE FROM " .. tablesToCheck[i][2][j] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
							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
							tmpCount = tmpCount - 1
							if (query_:getID() ~= -1) then query_:free() end
							if (not query:next() or tmpCount <= 1) then break end
						end
						query:free()
					end
				end
			end
		end
	end
	if f ~= nil then
		print("["..os.date("%X", os.time())..".TMI] >> Item Tracker: " .. count .. " duplicated items have been deleted...")
		f:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] >> [Item Tracker] " .. 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("[["..os.date("%X", os.time())..".TMI] >> [Item Tracker] Cannot save info to file!")
	end
	return true
end

Tested in 0.3.7 revs
 
Last edited:
Made a few fixes to this:
  • Removes all duplicated items but 1 (instead of them all).
  • Fixed possible memory leaks with db connection (wasn't freeing queries).
  • Tidy a few things off, not going to create a new local inside the loops.
  • Some text format changes.

Dunno if Darkhaos minds, but here's a tweaked version for anyone that wants the above features.

Lua:
function onStartup()
	
	tablesToCheck = {"player_items", "player_depotitems", "tile_items", {"player_items", {"player_depotitems", "tile_items"}}, {"player_depotitems", "tile_items"}}
	local text, final = "", ""
	local filex = "data/logs/duplicated.txt"
	local f = io.open(filex, "a+")
	local delete, delete2, query, query_ = nil, nil, nil, nil
	local count, tmpCount = 0, 0
	
	for i = 1, table.maxn(tablesToCheck) do
		if type(tablesToCheck[i]) == "string" then
			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
				tmpCount = query:getRows()
				while(true) do
					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") .. " LIMIT 1;")
					count = (delete and count + 1 or count)
					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!")
					final = final .. (final ~= "" and "\n" or "") .. text
					tmpCount = tmpCount - 1
					if (not query:next() or tmpCount <= 1) then break end
				end
				query:free()
			end
		else
			if type(tablesToCheck[i][2]) == "string" then
				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
					tmpCount = query:getRows()
					while(true) do
						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)")
						delete = db.executeQuery("DELETE FROM" .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
						count = (delete and count + 1 or count)
						delete2 = db.executeQuery("DELETE FROM " .. tablesToCheck[i][2] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
						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
						tmpCount = tmpCount - 1
						if (query_:getID() ~= -1) then query_:free() end
						if (not query:next() or tmpCount <= 1) then break end
					end
					query:free()
				end
			else
				for j = 1, #tablesToCheck[i][2] do
					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
						tmpCount = query:getRows()
						while(true) do
							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)")
							delete = db.executeQuery("DELETE FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
							count = (delete and count + 1 or count)
							delete2 = db.executeQuery("DELETE FROM " .. tablesToCheck[i][2][j] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " LIMIT 1;")
							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
							tmpCount = tmpCount - 1
							if (query_:getID() ~= -1) then query_:free() end
							if (not query:next() or tmpCount <= 1) then break end
						end
						query:free()
					end
				end
			end
		end
	end
	if f ~= nil then
		print("["..os.date("%X", os.time())..".TMI] >> Item Tracker: " .. count .. " duplicated items have been deleted...")
		f:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] >> [Item Tracker] " .. 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("[["..os.date("%X", os.time())..".TMI] >> [Item Tracker] Cannot save info to file!")
	end
	return true
end

Tested in 0.3.7 revs

1. I made it removes all the items, because with this script you can't know which is the original and wich is the duplicated
2. Freeing queries wion't fix that (Maybe fix it for some users only).

Anyways thanks for these features that you added/modified.
 
Back
Top