[RELEASE]Custom GM commands

[zargon05]

Here is a way to have custom GM commands in shaiya directly from the in-game chat. To get you started on making more I have included the ban function.
First off, We need this a SQL function which I found on the internet
You will need to execute this only once.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Split]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[Split]

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
    @value varchar(8000),
    @bcontinue bit,
    @iStrike smallint,
    @iDelimlength tinyint

IF @sDelim = 'Space'
    BEGIN
    SET @sDelim = ' '
    END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
    BEGIN
    WHILE @bcontinue = 1
        BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
        IF CHARINDEX(@sDelim, @sText)>0
            BEGIN
            SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            
--Trim the element and its delimiter from the front of the string.
            --Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
            SET @idx = @idx + 1
            SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
        
            END
        ELSE
            BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            --Exit the WHILE loop.
SET @bcontinue = 0
            END
        END
    END
ELSE
    BEGIN
    WHILE @bcontinue=1
        BEGIN
        --If the delimiter is an empty string, check for remaining text
        --instead of a delimiter. Insert the first character into the
        --retArray table. Trim the character from the front of the string.
--Increment the index and loop.
        IF DATALENGTH(@sText)>1
            BEGIN
            SET @value = SUBSTRING(@sText,1,1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            SET @idx = @idx+1
            SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
            
            END
        ELSE
            BEGIN
            --One character remains.
            --Insert the character, and exit the WHILE loop.
            INSERT @retArray (idx, value)
            VALUES (@idx, @sText)
            SET @bcontinue = 0    
            END
    END

END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The above is not my code, I just took to from the internet.

Now onto the chatlog trigger

USE [PS_Chatlog]
GO

/****** Object:  Trigger [dbo].[Commands]    Script Date: 02/12/2011 20:46:10  v0.2******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Commands]
ON [PS_Chatlog].[dbo].[ChatLog]
FOR INSERT
AS

DECLARE
@ChatData varchar(50),
@UserUID varchar(10),
@cmd varchar(10),
@auth smallint


SELECT @ChatData=ChatData,@UserUID=UserUID FROM inserted
SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID

IF (@auth = 16)
	BEGIN
        SELECT * INTO #tmptable FROM dbo.split(@ChatData,' ')
        SELECT @cmd=value FROM #tmptable where idx=0
	IF (@cmd='\ban')
	BEGIN	
		UPDATE um SET status=-5 FROM PS_UserData.dbo.users_mASter AS um 
		INNER JOIN PS_GameData.dbo.Chars AS c 
		ON um.UserUID=c.UserUID
		INNER JOIN #tmptable AS tt on 
		c.CharName=tt.value
	END
END

DROP TABLE #tmptable
GO

Edited usp_Insert_Chat_Log_E

USE [PS_Chatlog]
GO
/****** Object:  StoredProcedure [dbo].[usp_Insert_Chat_Log_E]    Script Date: 02/14/2011 19:52:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Stored Procedure dbo.usp_Insert_Chat_Log_E    Script Date: 2008-6-7 18:30:55 ******/
ALTER     Proc [dbo].[usp_Insert_Chat_Log_E]

/* 
Created by humanws, 2005-10-14
채팅 로그 남기기
 */

@UserUID int,
@CharID int,
@ChatType tinyint,		-- 일반1, 귓말2, 길드3, 파티4, 거래5
@TargetName varchar(30),
@ChatData varchar(128),
@MapID smallint,
@ChatTime datetime

AS

DECLARE @Sql nvarchar(4000)
DECLARE @yyyy varchar(4)
DECLARE @mm varchar(2)
DECLARE @dd varchar(2)

DECLARE @cmd varchar(10)
DECLARE @auth smallint

SET @yyyy = DATEPART(yyyy, @ChatTime)
SET @mm = DATEPART(mm, @ChatTime)
SET @dd = DATEPART(dd, @ChatTime)


IF( LEN(@mm) = 1 )
BEGIN
	SET @mm = '0' + @mm
END

IF( LEN(@dd) = 1 )
BEGIN
	SET @dd = '0' + @dd
END

SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID
IF (@auth = 16)
	BEGIN
	SELECT * INTO #tmptable FROM dbo.split(@ChatData,' ')
	SELECT @cmd=value FROM #tmptable where idx=0
	IF (@cmd='\ban')
	BEGIN	
		UPDATE um SET status=-5 FROM PS_UserData.dbo.users_mASter AS um 
		INNER JOIN PS_GameData.dbo.Chars AS c 
		ON um.UserUID=c.UserUID
		INNER JOIN #tmptable AS tt on 
		c.CharName=tt.value
	END
	DROP TABLE #tmptable
END

SET @Sql = N'
INSERT INTO PS_ChatLog.dbo.ChatLog
(UserUID, CharID, ChatType, TargetName, ChatData, MapID, ChatTime)
VALUES(@UserUID, @CharID, @ChatType, @TargetName, @ChatData, @MapID, @ChatTime)'

EXEC sp_executesql @Sql, 
N'@UserUID int, @CharID int, @ChatType tinyint, @TargetName varchar(30), @ChatData varchar(128),@MapID smallint, @ChatTime datetime',
@UserUID, @CharID, @ChatType, @TargetName, @ChatData, @MapID, @ChatTime

Once you have executed both the above codes, you should have a working ban function. To ban someone simply get on a GM account and type in chat "\ban <charname>" and it will be done .

[[Dev]Ansem]

"IF (@auth = 16)"

= only gm acc with full lvl 16 can use that command?

[zargon05]

Yes

[Bаne]

TY mate you are a real life saver.

[[GM]Recover]

thanks m8 will try it soon (on my mac atm got it from school)

[abrasive]

Since this is running every time chat is persisted to the database (basically every time someone says something, which is a LOT) it has the potential to cause performance issues.

It looks like the trigger is running a minimum of 5 queries, plus whatever is going on in the split stored procedure, PLUS creating and destroying a temp table. That sounds a bit heavy to me.

You can save the database a LOT of work by switching the order of operations a little bit in the trigger, like so:

-- snip --

SELECT @UserUID=UserUID FROM inserted
SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID

IF (@auth = 16)
	SELECT @ChatData=ChatData from inserted
	SELECT * INTO #tmptable FROM dbo.split(@ChatData,' ')
	SELECT @cmd=value FROM #tmptable where idx=0
	BEGIN
	IF (@cmd='\ban')
	BEGIN	
		UPDATE um SET status=-5 FROM PS_UserData.dbo.Users_Master AS um 
		INNER JOIN PS_GameData.dbo.Chars AS c 
		ON um.UserUID=c.UserUID
		INNER JOIN #tmptable AS tt on 
		c.CharName=tt.value
	END
	DROP TABLE #tmptable
END

Now you're only doing two queries, and not bothering with any of the real heavy stuff unless the users has a status of 16.

PS: I didn't test this, but you probably get the idea

[cosong]

Mine is doesn't work, both querty execute fine, any got it work?

[cosong]

if you type /ban <name>, i think you got DC...but mine is \ban doesn't work too, idk why^(

[cosong]

no put any info...what you mean about that? I execute both scripts only and restart server. Can you give more info?

[[GM]Father]

Originally Posted by abrasive

Since this is running every time chat is persisted to the database (basically every time someone says something, which is a LOT) it has the potential to cause performance issues.

It looks like the trigger is running a minimum of 5 queries, plus whatever is going on in the split stored procedure, PLUS creating and destroying a temp table. That sounds a bit heavy to me.

You can save the database a LOT of work by switching the order of operations a little bit in the trigger, like so:

-- snip --

SELECT @UserUID=UserUID FROM inserted
SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID

IF (@auth = 16)
	SELECT @ChatData=ChatData from inserted
	SELECT * INTO #tmptable FROM dbo.split(@ChatData,' ')
	SELECT @cmd=value FROM #tmptable where idx=0
	BEGIN
	IF (@cmd='\ban')
	BEGIN	
		UPDATE um SET status=-5 FROM PS_UserData.dbo.Users_Master AS um 
		INNER JOIN PS_GameData.dbo.Chars AS c 
		ON um.UserUID=c.UserUID
		INNER JOIN #tmptable AS tt on 
		c.CharName=tt.value
	END
	DROP TABLE #tmptable
END

Now you're only doing two queries, and not bothering with any of the real heavy stuff unless the users has a status of 16.

PS: I didn't test this, but you probably get the idea

Warning:
If you do not fix this script as Abrasive suggested you are opening yourself up to a simple in-game DDOS attack.

A simple script that repeatedly sends a message will bring your server to it's knees.

[zargon05]

The first script is to create a function called Split which takes in a start data and a delimiter as input and return a table with an "array" of string like for example:
Input = "donut,cheese,cake,ice-cream" and u set the delimiter as "," then it will return a table which roughly looks like

Idx Value
---------
0 donut
1 cheese
2 cake
3 ice-cream

So the each string can be individually accessed.
I updated to script to v0.2 to improve performace as per abrasive's script

[lilprohacker]

I did a very similar thing, but I just changed the stored proc "[dbo].[usp_Insert_Chat_Log_E]". I figured, it's best to stay away from triggers if a stored proc is already being used.

Also a few changes I would do. I'd remove the use of the function Split and replace it with a simple substring.

SELECT @ChatData=ChatData,@UserUID=UserUID FROM inserted
SELECT @auth=Status FROM PS_UserData.dbo.Users_Master WHERE @UserUID=UserUID

IF (@auth = 16)
	BEGIN
		DECLARE @CharName varchar(15)
        SELECT @cmd= SUBSTRING(@ChatData,0, CHARINDEX(' ',@ChatData))
		SELECT @CharName = SUBSTRING(@ChatData, len(@cmd)+2, len(@ChatData))

		IF (@cmd='\ban')
		BEGIN	
			UPDATE um SET status=-5 FROM PS_UserData.dbo.users_mASter AS um 
			INNER JOIN PS_GameData.dbo.Chars AS c 
			ON um.UserUID=c.UserUID
			Where c.CharName = @CharName
		END
	END
GO

Now I haven't tested this but you get the idea.

[zargon05]

I initially used substring but I wanted to make a more flexible code so it can be easily expanded to accommodate more commands while keeping the basic layout. For those who want to directly use the gamelog sp, i'll post a script for that also.

Originally Posted by castor4878

I imagine that the "/" commands are handled before landing in Gamelogs.
The point was what happens to the unrecognized commands?

All the commands starting with / are first check by the client side game.exe to verify if such a command exist after returning true is only when it passes it onto the server.

[lilprohacker]

The substring code I posted lets you use any command you desire followed by an argument. so you could make /ban /getIP /givepoints /whateva..... and so on.

[[Dev]Ansem]

any updates on this? I want to buy some triggers ;O