Jump to content

Recommended Posts

Posted

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

Posted

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 ^^

  • 9 months later...
  • 2 years later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Posts

    • Hello Everyone  I hope you missed Old nostalgia BNB , is time to come back and be ready for the new journey  L2 BnB C3 Website: https://l2bnb.eu/ Discord: https://discord.gg/jaCJKYXgYZ Max Level 75 Max Grade (A) Safe:3 , Max:16 Anti-Botting. Auto learning skills , Auto Loot , Auto Create Account Subclass (NO) quest. Retail Buffs/DS Time ,Need Buffer Char (NO NPC Buffer) OfflineShop,OfflineCraft,ChangePass No GM Shop,No Global Gk,No Donate,Free Teleport only LVL 1 commands: .offline , .changepassword 99+% Retail 1+1 Window Per Pc BASIC FEATURES: Exp/SP: x 3 Adena: x3 Drop: х3 Spoil: x3 Support 24/7 GLOBAL COMMUNITY
    • 亲爱的朋友们,我们很高兴向您介绍我们的全新服务 —— KYC 实名认证,适用于任何平台!️ 我们为加密货币交易所、在线市场、社交网络、主机服务商、赌场及其他合法网站提供实名认证服务。认证可通过护照或驾驶证完成。不支持任何涉及非法活动的网站。 可用国家: 东欧:俄罗斯、乌克兰、白俄罗斯、乌兹别克斯坦、亚美尼亚、吉尔吉斯斯坦、哈萨克斯坦 — $30–33 欧盟(西欧,通常为拉脱维亚和爱沙尼亚) — $80–88 非洲:尼日利亚、肯尼亚 — $30–33 如果您需要注册并验证您的账户,总金额将额外收取 10% 手续费。 如需申请 KYC 认证或咨询其他问题,请通过以下方式联系我们: ➡ Telegram: https://t.me/socnet_support ➡ WhatsApp: https://wa.me/79051904467 ➡ Discord: socnet_support ➡ ✉ 邮箱: solomonbog@socnet.store SOCNET 商店有效链接: 数字商品商店(网站):进入 商店 Telegram 机器人:进入 – 通过 Telegram 消息应用便捷访问商店。 Telegram 星星购买机器人:进入 – 快速且优惠地购买 Telegram 星星。 SMM 面板:进入 – 推广您的社交媒体账户。 我们为您准备了最新的促销与特别优惠,用于购买我们的产品与服务: 1. 使用优惠码 OCTOBER2025(8% 折扣)即可在 9 月于我们的商店(网站或机器人)购物享受优惠!首次购买还可使用优惠码 SOCNET(15% 折扣)。 2. 获得 $1 商店余额或 10–20% 折扣 —— 只需在网站注册后按照以下格式留言:"SEND ME BONUS, MY USERNAME IS..." – 在我们的论坛帖中发布即可! 3. 首次试用 SMM 面板可获得 $1 奖励 —— 只需在网站(支持)提交标题为 “Get Trial Bonus” 的工单。 4. 每周在我们的 Telegram 频道及星星购买机器人中举行 Telegram 星星赠送活动! 新闻资讯: ➡ Telegram 频道: https://t.me/accsforyou_shop ➡ WhatsApp 频道: https://chat.whatsapp.com/K8rBy500nA73z27PxgaJUw?mode=ems_copy_t ➡ Discord 服务器: https://discord.gg/y9AStFFsrh
    • 亲爱的朋友们,我们很高兴向您介绍我们的全新服务 —— KYC 实名认证,适用于任何平台!️ 我们为加密货币交易所、在线市场、社交网络、主机服务商、赌场及其他合法网站提供实名认证服务。认证可通过护照或驾驶证完成。不支持任何涉及非法活动的网站。 可用国家: 东欧:俄罗斯、乌克兰、白俄罗斯、乌兹别克斯坦、亚美尼亚、吉尔吉斯斯坦、哈萨克斯坦 — $30–33 欧盟(西欧,通常为拉脱维亚和爱沙尼亚) — $80–88 非洲:尼日利亚、肯尼亚 — $30–33 如果您需要注册并验证您的账户,总金额将额外收取 10% 手续费。 如需申请 KYC 认证或咨询其他问题,请通过以下方式联系我们: ➡ Telegram: https://t.me/socnet_support ➡ WhatsApp: https://wa.me/79051904467 ➡ Discord: socnet_support ➡ ✉ 邮箱: solomonbog@socnet.store SOCNET 商店有效链接: 数字商品商店(网站):进入 商店 Telegram 机器人:进入 – 通过 Telegram 消息应用便捷访问商店。 Telegram 星星购买机器人:进入 – 快速且优惠地购买 Telegram 星星。 SMM 面板:进入 – 推广您的社交媒体账户。 我们为您准备了最新的促销与特别优惠,用于购买我们的产品与服务: 1. 使用优惠码 OCTOBER2025(8% 折扣)即可在 9 月于我们的商店(网站或机器人)购物享受优惠!首次购买还可使用优惠码 SOCNET(15% 折扣)。 2. 获得 $1 商店余额或 10–20% 折扣 —— 只需在网站注册后按照以下格式留言:"SEND ME BONUS, MY USERNAME IS..." – 在我们的论坛帖中发布即可! 3. 首次试用 SMM 面板可获得 $1 奖励 —— 只需在网站(支持)提交标题为 “Get Trial Bonus” 的工单。 4. 每周在我们的 Telegram 频道及星星购买机器人中举行 Telegram 星星赠送活动! 新闻资讯: ➡ Telegram 频道: https://t.me/accsforyou_shop ➡ WhatsApp 频道: https://chat.whatsapp.com/K8rBy500nA73z27PxgaJUw?mode=ems_copy_t ➡ Discord 服务器: https://discord.gg/y9AStFFsrh
    • 亲爱的朋友们,我们很高兴向您介绍我们的全新服务 —— KYC 实名认证,适用于任何平台!️ 我们为加密货币交易所、在线市场、社交网络、主机服务商、赌场及其他合法网站提供实名认证服务。认证可通过护照或驾驶证完成。不支持任何涉及非法活动的网站。 可用国家: 东欧:俄罗斯、乌克兰、白俄罗斯、乌兹别克斯坦、亚美尼亚、吉尔吉斯斯坦、哈萨克斯坦 — $30–33 欧盟(西欧,通常为拉脱维亚和爱沙尼亚) — $80–88 非洲:尼日利亚、肯尼亚 — $30–33 如果您需要注册并验证您的账户,总金额将额外收取 10% 手续费。 如需申请 KYC 认证或咨询其他问题,请通过以下方式联系我们: ➡ Telegram: https://t.me/socnet_support ➡ WhatsApp: https://wa.me/79051904467 ➡ Discord: socnet_support ➡ ✉ 邮箱: solomonbog@socnet.store SOCNET 商店有效链接: 数字商品商店(网站):进入 商店 Telegram 机器人:进入 – 通过 Telegram 消息应用便捷访问商店。 Telegram 星星购买机器人:进入 – 快速且优惠地购买 Telegram 星星。 SMM 面板:进入 – 推广您的社交媒体账户。 我们为您准备了最新的促销与特别优惠,用于购买我们的产品与服务: 1. 使用优惠码 OCTOBER2025(8% 折扣)即可在 9 月于我们的商店(网站或机器人)购物享受优惠!首次购买还可使用优惠码 SOCNET(15% 折扣)。 2. 获得 $1 商店余额或 10–20% 折扣 —— 只需在网站注册后按照以下格式留言:"SEND ME BONUS, MY USERNAME IS..." – 在我们的论坛帖中发布即可! 3. 首次试用 SMM 面板可获得 $1 奖励 —— 只需在网站(支持)提交标题为 “Get Trial Bonus” 的工单。 4. 每周在我们的 Telegram 频道及星星购买机器人中举行 Telegram 星星赠送活动! 新闻资讯: ➡ Telegram 频道: https://t.me/accsforyou_shop ➡ WhatsApp 频道: https://chat.whatsapp.com/K8rBy500nA73z27PxgaJUw?mode=ems_copy_t ➡ Discord 服务器: https://discord.gg/y9AStFFsrh
  • Topics

×
×
  • Create New...

AdBlock Extension Detected!

Our website is made possible by displaying online advertisements to our members.

Please disable AdBlock browser extension first, to be able to use our community.

I've Disabled AdBlock