[Guide] Simple SQL query to check Max Plus of items during login/logout session

[witchymoo]

Hello,

Here I'll share simple SQL query to check and update "Max Plus" (with and without Adv. Elixir) of items in 2 different methods: (doesn't matter which, both has same effect)

Method 1: Real time after fusing
Method 2: On login/logout session

Why login/logout session? well as far as I tried, changing item plus from database trigger or [_AddLogItem] query without refreshing the client will cause misinformation between client and server. And might cause the client dc or crash (no idea which), because of wrong information send to/received from the server. (CMIIW)

For example, someone did +13 and you force real time query back to +12. The database already have "+12", but the client still reads "+13"... then the player fused again and succeeded the 2nd time (this time client reads +14 while server reads +12), and they start hunting/hitting/killing whatever without teleporting... not sure what will happen :P

(Update: as its.soul confirmed, nothing will happen, so I added a "real time" max plus check during equipment fusing, even though the client has to teleport to refresh the visual side)

Update notes:

1. This will work and might be useful if you run your server without additional 3rd party filter. But you won't need this if using filter such as K-Guard, SUPERMAN, Galullians or others, since those already have a feature for plus limit.
2. If you use Method 1, you won't need to use Method 2 because Method 1 already update the "ItemID" itself. So it won't matter where the player put the item after fusing

Now the boring part: (Feel free to develop the query as you see fit)

Method 1. "Real time" check and update right after fusing. (Client needs to teleport to refresh visual side)

Procedure: [SRO_VT_LOG].[dbo].[_AddLogItem]

 Spoiler

/* Item upgrading */
IF (@Operation = 90 OR @Operation = 160)
BEGIN
	/* Start: Max Plus */
	DECLARE @strDescStrip VARCHAR(128)
	DECLARE @ItemID INT

	DECLARE @MaxOptLevel TINYINT
	SET @MaxOptLevel = 12

	/* Get current fused plus */
	SET @len_desc1 = CHARINDEX (']', @strDesc)
	IF (@len_desc1 = 9)
	BEGIN
		SET @strDesc = SUBSTRING(@strDesc, 0, 9)
		SET @strDescStrip = SUBSTRING(@strDesc, 8, 1)
	END
	ELSE IF (@len_desc1 = 10)
	BEGIN
		SET @strDesc = SUBSTRING(@strDesc, 0, 10)
		SET @strDescStrip = SUBSTRING(@strDesc, 8, 2)
	END
	/* Check if OptLevel above @MaxOptLevel */
	IF (@strDescStrip >= @MaxOptLevel)
	BEGIN
		/* Get ItemID */
		SELECT @ItemID = ID64 from [SRO_VT_SHARD].[dbo].[_Items] WHERE Serial64 = @ItemSerial
		/* Update items if has more than @MaxOptLevel without Adv elixir*/
		UPDATE [SRO_VT_SHARD].[dbo].[_Items] SET OptLevel = @MaxOptLevel WHERE OptLevel > @MaxOptLevel AND Serial64 = @ItemSerial
		/* Delete Adv elixir effect for item if has more than @MaxOptLevel with Adv elixir */
		DELETE FROM [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] WHERE nItemDBID IN (
			SELECT a.ID64 FROM [SRO_VT_SHARD].[dbo].[_Items] a WITH (NOLOCK)
			INNER JOIN [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] b ON a.ID64 = b.nItemDBID
			WHERE a.ID64 = @ItemID AND (a.OptLevel + b.nOptValue) > @MaxOptLevel
			AND b.bOptType = 2
		)
	END
	RETURN -1
	/* End: Max Plus */
END

Method 2. Check and update during login/logout session for Inventory, Storage, and Guild Storage

Procedure: [SRO_VT_LOG].[dbo].[_AddLogChar]

 Spoiler

/* Login/Logout sequence (4 = Login, 6 = Logout) */
IF (@EventID = 4 OR @EventID = 6)
BEGIN
	/*
	 * Begin: Item Plus check
	 */
	DECLARE @UserJID INT, @GuildID INT, @MaxOptLevel TINYINT
	SET @MaxOptLevel = 12	-- Set Max Plus value (change this)
	/*
	 * Get JID and GuildID info
	 */
	SELECT @UserJID = a.UserJID, @GuildID = b.GuildID FROM [SRO_VT_SHARD].[dbo].[_User] a WITH (NOLOCK)
		INNER JOIN [SRO_VT_SHARD].[dbo].[_Char] b ON a.CharID = b.CharID WHERE a.CharID = @CharID
	/*
	 * 1. INVENTORY: Update items in inventory if has more than +12 without Adv elixir
	 */
	UPDATE [SRO_VT_SHARD].[dbo].[_Items] SET OptLevel = @MaxOptLevel WHERE OptLevel > @MaxOptLevel AND ID64 IN (
		SELECT ItemID FROM [SRO_VT_SHARD].[dbo].[_Inventory] WITH (NOLOCK)
		WHERE CharID = @CharID AND ItemID > 0
	)
	/*
	 * 2. INVENTORY: Remove Adv elixir effect for items in inventory if has more than +12 with Adv elixir
	 */
	DELETE FROM [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] WHERE nItemDBID IN (
		SELECT a.ItemID FROM [SRO_VT_SHARD].[dbo].[_Inventory] a WITH (NOLOCK) 
		INNER JOIN [SRO_VT_SHARD].[dbo].[_Items] b ON a.ItemID = b.ID64 
		INNER JOIN [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] c ON a.ItemID = c.nItemDBID
		WHERE a.CharID = @CharID AND a.ItemID > 0 AND (b.OptLevel + c.nOptValue) > @MaxOptLevel
		AND c.bOptType = 2
	)
	/*
	 * 3. PERSONAL STORAGE: Update items in personal storage if has more than +12 without Adv elixir
	 */
	UPDATE [SRO_VT_SHARD].[dbo].[_Items] SET OptLevel = @MaxOptLevel WHERE OptLevel > @MaxOptLevel AND ID64 IN (
		SELECT ItemID FROM [SRO_VT_SHARD].[dbo].[_Chest] WITH (NOLOCK)
		WHERE UserJID = @UserJID AND ItemID > 0
	)
	/*
	 * 4. PERSONAL STORAGE: Remove Adv elixir effect for items in personal storage if has more than +12 with Adv elixir
	 */
	DELETE FROM [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] WHERE nItemDBID IN (
		SELECT a.ItemID FROM [SRO_VT_SHARD].[dbo].[_Chest] a WITH (NOLOCK) 
		INNER JOIN [SRO_VT_SHARD].[dbo].[_Items] b ON a.ItemID = b.ID64 
		INNER JOIN [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] c ON a.ItemID = c.nItemDBID
		WHERE a.UserJID = @UserJID AND a.ItemID > 0 AND (b.OptLevel + c.nOptValue) > @MaxOptLevel
		AND c.bOptType = 2
	)
	/*
	 * 5. GUILD: If character is guild member, check Item Plus in Guild Inventory
	 */
	IF (@GuildID <> 0)
	BEGIN
		/*
		 * 5.1. Update items in guild storage if has more than +12 without Adv elixir
		 */
		UPDATE [SRO_VT_SHARD].[dbo].[_Items] SET OptLevel = @MaxOptLevel WHERE OptLevel > @MaxOptLevel AND ID64 IN (
			SELECT ItemID FROM [SRO_VT_SHARD].[dbo].[_GuildChest] WITH (NOLOCK)
			WHERE GuildID = @GuildID AND ItemID > 0
		)
		/*
		 * 5.2. Remove Adv elixir effect for items in guild storage if has more than +12 with Adv elixir
		 */
		DELETE FROM [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] WHERE nItemDBID IN (
			SELECT a.ItemID FROM [SRO_VT_SHARD].[dbo].[_GuildChest] a WITH (NOLOCK) 
			INNER JOIN [SRO_VT_SHARD].[dbo].[_Items] b ON a.ItemID = b.ID64 
			INNER JOIN [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] c ON a.ItemID = c.nItemDBID
			WHERE a.GuildID = @GuildID AND a.ItemID > 0 AND (b.OptLevel + c.nOptValue) > @MaxOptLevel
			AND c.bOptType = 2
		)
	END
	/*
	 * END: Item Plus check
	 */
END

To test, fuse some items with or without Adv Elixir to more than your Max Plus value, then teleport (to test method 1), or put those items in Inventory, Storage, and Guild Storage, then logout/login again (to test method 2). see if it works.

good luck and have fun

[Eslam Galull]

good release

about :

then the player fused again and succeeded the 2nd time (this time client reads +14 while server reads +12), and they start hunting/hitting/killing whatever without teleporting... not sure what will happen :P

nothing will happen cuz clinet side is just a visual side..

[Xutan*]

there is a much better way,
you could simply filter it, so when you are trying to pass the limit, nothing will happen and you will get a notice informing you that you can't fuse this item anymore.

[witchymoo]

like reverting back to max plus when fusing?... if no problem with the client, then it'll do, coz i have no idea how to limit it on client side, so whenever succeeded the client will still show the latest value right?

[Xutan*]

Originally Posted by witchymoo

like reverting back to max plus when fusing?... if no problem with the client, then it'll do, coz i have no idea how to limit it on client side, so whenever succeeded the client will still show the latest value right?

It will not fail or success, plus will not be changed in both sides db and client.

[witchymoo]

Post updated, added another method to check and update real time on database side after fusing.

Originally Posted by Xutan*

It will not fail or success, plus will not be changed in both sides db and client.

About this, How do I filter it to follow your explanation?

thank you

[Xutan*]

Originally Posted by witchymoo

Post updated, added another method to check and update real time on database side after fusing.



About this, How do I filter it to follow your explanation?

thank you

using a packet filter.
P.S. : that kguard which was released includes this feature. you can check the source and learn how to do it.

[witchymoo]

Originally Posted by Xutan*

using a packet filter.
P.S. : that kguard which was released includes this feature. you can check the source and learn how to do it.

Ah alright, a 3rd party filter ... I was wondering if we can do that without additional addon/filter.

[Xutan*]

Originally Posted by witchymoo

Ah alright, a 3rd party filter ... I was wondering if we can do that without additional addon/filter.

your AddLogChar way is the only way to do it without any additional addons. however, it's kinda bad. players will use items with high plus till they get a disconnect.

there is something easier, but requires a bot.
you can do the AddLogItem's way and send "/totown" to the char which passed the limit. so the visual of the item plus will get updated.

[Eslam Galull]

as Xutan said : there is noway except addlogchar

using a 3rd part program is more useful adding more features ..

[Overlimit Developer]

tested , good one

[Justin1337*]

Great release witchy, but I have to agree a packet filter would work better. Still a nice idea though!