Share [SHARE] How to track item owners

[GrisoM]

Some admins will found this intereseting, since i got tired of txt log searching... i decided to mod db SPs to store owner changes into db... this will probably estress SQL a bit and db size will increase a lot, but if you have a good SQL server you don't need to care about that.

 

First we need to create a new table, in that table we will store all item parameters and 1 new field, that will be the exact date item change owner.

 

This will only track single items (consume_type_normal), because trying to track other kind of items is a bit hard (if not almost imposible) due to item_id changes.

 

Well.. here we go... first step: Creating table.

Code:

 

USE [lin2world]

GO

/****** Object:  Table [dbo].[user_item_log]    Script Date: 08/06/2008 20:52:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[user_item_log](

[item_id] [int] NOT NULL,

[char_id] [int] NOT NULL,

[item_type] [int] NOT NULL,

[amount] [int] NOT NULL,

[enchant] [int] NOT NULL,

[eroded] [int] NOT NULL,

[bless] [tinyint] NOT NULL,

[ident] [int] NOT NULL,

[wished] [tinyint] NOT NULL,

[warehouse] [int] NOT NULL,

[date] [datetime] NOT NULL CONSTRAINT [DF_Table_1_create_date]  DEFAULT (getdate())

) ON [PRIMARY]

 

-----------------------------------------------------------------------------------------------

Second step: Modifying lin_UpdateUserItem Store Procedure.

 

Code:

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

 

/****** Object:  Stored Procedure dbo.lin_UpdateUserItem    Script Date: 2003-09-20 ?? 11:51:57 ******/

/********************************************

lin_UpdateUserItem

 

INPUT

@char_id INT,

@item_type INT,

@amount INT,

@enchant INT,

@eroded INT,

@bless INT,

@ident INT,

@wished INT,

@warehouse INT,

@item_id INT

OUTPUT

return

made by

carrot

date

2002-06-09

********************************************/

ALTER PROCEDURE [dbo].[lin_UpdateUserItem]

(

@char_id INT,

@item_type INT,

@amount INT,

@enchant INT,

@eroded INT,

@bless INT,

@ident INT,

@wished INT,

@warehouse INT,

@item_id INT

)

AS

SET NOCOUNT ON

 

-- START FidoW addon item tracker

DECLARE @isOK INT

SELECT @isOK = id FROM itemdata WHERE isQuest = 0 AND consumetype = 'consume_type_normal' AND id = @item_type

 

IF (@isOK IS NOT NULL)

BEGIN

DECLARE @old_char_id int

SELECT @old_char_id = char_id FROM user_item WHERE item_id = @item_id

IF (@old_char_id <> @char_id)

BEGIN

INSERT INTO user_item_log (item_id,char_id,item_type,amount,enchant,eroded,bless,ident,wished,warehouse,date)

VALUES (@item_id,@char_id,@item_type,@amount,@enchant,@eroded,@bless,@ident,@wished,@warehouse,GETDATE())

END

END

-- END FidoW addon item tracker

 

UPDATE user_item  set char_id=@char_id, item_type=@item_type, amount=@amount, enchant=@enchant, eroded=@eroded, bless=@bless, ident=@ident, wished=@wished, warehouse=@warehouse WHERE item_id=@item_id

 

---------------------------------------------------------------------------------------------------

Step three: Update lin_CreateItem Store Proceduce.

 

Code:

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

 

 

/****** Object:  Stored Procedure dbo.lin_CreateItem    Script Date: 2003-09-20 오전 11:51:57 ******/

/********************************************

lin_CreateItem

create item sp

INPUT

@char_id INT,

@item_type INT,

@amount INT,

@enchant INT,

@eroded INT,

@bless TINYINT,

@ident TINYINT,

@ready TINYINT,

@wished TINYINT,

@warehouse INT

OUTPUT

Item_ID, @@IDENTITY

return

made by

carrot

date

2002-01-31

********************************************/

ALTER PROCEDURE [dbo].[lin_CreateItem]

(

@char_id INT,

@item_type INT,

@amount INT,

@enchant INT,

@eroded INT,

@bless TINYINT,

@ident TINYINT,

@wished TINYINT,

@warehouse INT

)

AS

SET NOCOUNT ON

 

insert into user_item

(char_id , item_type , amount , enchant , eroded , bless , ident , wished , warehouse)

values

(@char_id, @item_type , @amount , @enchant , @eroded , @bless , @ident , @wished , @warehouse)

 

SELECT @@IDENTITY

 

-- START FidoW addon item tracker

DECLARE @isOK INT

SELECT @isOK = id FROM itemdata WHERE isQuest = 0 AND consumetype = 'consume_type_normal' AND id = @item_type

 

IF (@isOK IS NOT NULL)

BEGIN

DECLARE @item_id INT

SELECT TOP 1 @item_id = item_id FROM user_item

WHERE char_id = @char_id AND item_type =  @item_type AND amount = @amount AND enchant = @enchant AND eroded = @eroded AND bless = @bless AND ident = @ident AND wished = @wished AND warehouse = @warehouse

ORDER BY item_id DESC

 

INSERT INTO user_item_log (item_id,char_id,item_type,amount,enchant,eroded,bless,ident,wished,warehouse,date)

VALUES (@item_id,@char_id,@item_type,@amount,@enchant,@eroded,@bless,@ident,@wished,@warehouse,GETDATE())

END

-- END FidoW addon item tracker

 

--------------------------------------------------------------------------------------------------

 

This will NOT WORK if you have itemdata table empty or not updated (use CacheD and CachedScript to update that table)

 

 

How to use ? Just execute both querys into sql and its done, and it will start storing it since now.

 

What can we do with this ? We can track all items owner changes, in case some users log into other user account and steal all the items, we can track it easy with our new table, and more important, we dont need to look at logs anymore.

 

I hope someone will find this usefull.

 

 

If we want to initialize user_item_log table with all items already stored.... we can run the next query:

 

Code:

 

INSERT INTO user_item_log

SELECT item_id,char_id,item_type,amount,enchant,eroded,bless,ident,wished,warehouse,'2001-01-01 00:00:00'

FROM user_item

WHERE item_id NOT IN (SELECT DISTINCT item_id FROM user_item_log)

AND item_type IN (SELECT id FROM itemdata WHERE isQuest = 0 AND consumetype = 'consume_type_normal')

 

Of course we dont get the real date, but now we have stored the current owner waiting until the next change.

 

--------------------------------------------------------------------------------------------------

I use search and didnt found anything like this guide

 

Credits for this awesom guide goes to FidoW

 

Hope u like  bb

[fdLP.]

this is l2off ^_^

[GrisoM]

this is l2off ^_^

i have request some one to move it..  lmao xD

ty

[Theonegandalf]

reading the first line of the code i see it was for l2 off  :P

 

USE [lin2world]

 

From only this ;D

[GrisoM]

reading the first line of the code i see it was for l2 off  :P

 

 

From only this ;D

 

that is the most wierd i found this in a Lineage II Java Develop section haha xD my bad again ( i also give it a fast read to the text =P)

 

any way tell me your opinion :/ w00t!

 

[EDIT] ty k4rma for moving it ^^

[loverplus_kr]

wow

[CriticalError]

awesome guide i get to mi vault thanks a lot man

[Syndrome69]

awesome guide gz

[Predator321]

thnx for share