LGram16
dd97ddec92
Move git root from Client/ to src/ to track all source code: - Client: Game client source (moved to Client/Client/) - Server: Game server source - GameTools: Development tools - CryptoSource: Encryption utilities - database: Database scripts - Script: Game scripts - rylCoder_16.02.2008_src: Legacy coder tools - GMFont, Game: Additional resources 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
434 lines
11 KiB
Transact-SQL
434 lines
11 KiB
Transact-SQL
-- 해외쪽 운영DB 버전 관리가 지금까지 제대로 되지 않아 운영계정을 제외하곤 최신으로 모두 수정!
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMS_DoingRestraintChar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
|
drop procedure [dbo].[CMS_DoingRestraintChar]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USPCheckRestraintChar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
|
drop procedure [dbo].[USPCheckRestraintChar]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USPDeleteRestraintChar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
|
drop procedure [dbo].[USPDeleteRestraintChar]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblChatBanList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
|
|
drop table [dbo].[TblChatBanList]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblGMLogMsg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
|
|
drop table [dbo].[TblGMLogMsg]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblGMLogMsg_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
|
|
drop table [dbo].[TblGMLogMsg_Log]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblInterestedUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
|
|
drop table [dbo].[TblInterestedUser]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblRestraintChar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
|
|
drop table [dbo].[TblRestraintChar]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblDetailLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
|
|
drop table [dbo].[TblDetailLog]
|
|
GO
|
|
|
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
|
|
drop table [dbo].[TblLog]
|
|
GO
|
|
|
|
|
|
CREATE TABLE [dbo].[TblChatBanList] (
|
|
[CID] [int] NULL ,
|
|
[ServerGroupID] [tinyint] NULL ,
|
|
[RemainTime] [int] NULL ,
|
|
[AdminCID] [int] NULL ,
|
|
[StartTime] [smalldatetime] NULL ,
|
|
[EndTime] [smalldatetime] NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[TblGMLogMsg] (
|
|
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
|
|
[UID] [int] NULL ,
|
|
[CID] [int] NULL ,
|
|
[ServerGroupID] [tinyint] NOT NULL ,
|
|
[EditDate] [smalldatetime] NOT NULL ,
|
|
[LogMsg] [varchar] (200) NULL ,
|
|
[AdminID] [varchar] (20) NOT NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[TblGMLogMsg_Log] (
|
|
[LogID] [int] NOT NULL ,
|
|
[UID] [int] NULL ,
|
|
[CID] [int] NULL ,
|
|
[ServerGroupID] [tinyint] NOT NULL ,
|
|
[EditDate] [smalldatetime] NOT NULL ,
|
|
[LogMsg] [varchar] (200) NULL ,
|
|
[AdminID] [varchar] (20) NOT NULL ,
|
|
[InsertTime] [smalldatetime] NOT NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[TblInterestedUser] (
|
|
[strAccount] [varchar] (20) NOT NULL ,
|
|
[strReason] [varchar] (260) NULL ,
|
|
[intUID] [int] NOT NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[TblRestraintChar] (
|
|
[tinyServerID] [tinyint] NOT NULL ,
|
|
[UID] [int] NOT NULL ,
|
|
[strClientID] [varchar] (20) NOT NULL ,
|
|
[strCharName] [varchar] (20) NOT NULL ,
|
|
[tinyType] [tinyint] NOT NULL ,
|
|
[tinyKind] [tinyint] NOT NULL ,
|
|
[tinyBlocked] [tinyint] NOT NULL ,
|
|
[dateStartTime] [smalldatetime] NOT NULL ,
|
|
[dateEndTime] [smalldatetime] NOT NULL ,
|
|
[intEndTime] [int] NOT NULL ,
|
|
[strAdminID] [varchar] (20) NULL ,
|
|
[strDescription] [varchar] (30) NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[TblDetailLog] (
|
|
[DetailLogID] [int] IDENTITY (1, 1) NOT NULL ,
|
|
[LogID] [int] NOT NULL ,
|
|
[UpdateDate] [smalldatetime] NOT NULL ,
|
|
[UpdateType] [int] NULL ,
|
|
[Before] [varchar] (200) NULL ,
|
|
[After] [varchar] (200) NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[TblLog] (
|
|
[UID] [int] NOT NULL ,
|
|
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
|
|
[Login] [smalldatetime] NOT NULL ,
|
|
[Logout] [smalldatetime] NULL ,
|
|
[UseIP] [varchar] (16) NOT NULL ,
|
|
[UseMsg] [varchar] (100) NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblGMLogMsg] WITH NOCHECK ADD
|
|
CONSTRAINT [PK_TblGMLogMsg] PRIMARY KEY CLUSTERED
|
|
(
|
|
[LogID]
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblGMLogMsg_Log] WITH NOCHECK ADD
|
|
CONSTRAINT [PK_TblGMLogMsg_Log] PRIMARY KEY CLUSTERED
|
|
(
|
|
[LogID]
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblDetailLog] WITH NOCHECK ADD
|
|
CONSTRAINT [PK_TblDetailLog] PRIMARY KEY CLUSTERED
|
|
(
|
|
[DetailLogID]
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblLog] WITH NOCHECK ADD
|
|
CONSTRAINT [PK_TblLog] PRIMARY KEY CLUSTERED
|
|
(
|
|
[LogID]
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
CREATE INDEX [idx_TblChatBanList_cid] ON [dbo].[TblChatBanList]([CID]) ON [PRIMARY]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblGMLogMsg] ADD
|
|
CONSTRAINT [DF_TblGMLogMsg_UID] DEFAULT (0) FOR [UID],
|
|
CONSTRAINT [DF_TblGMLogMsg_CID] DEFAULT (0) FOR [CID],
|
|
CONSTRAINT [DF_TblGMLogMsg_EditDate] DEFAULT (getdate()) FOR [EditDate]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblGMLogMsg_Log] ADD
|
|
CONSTRAINT [DF_TblGMLogMsg_Log_UID] DEFAULT (0) FOR [UID],
|
|
CONSTRAINT [DF_TblGMLogMsg_Log_CID] DEFAULT (0) FOR [CID],
|
|
CONSTRAINT [DF_TblGMLogMsg_Log_EditDate] DEFAULT (getdate()) FOR [EditDate],
|
|
CONSTRAINT [DF_TblGMLogMsg_Log_InsertTime] DEFAULT (getdate()) FOR [InsertTime]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblRestraintChar] ADD
|
|
CONSTRAINT [DF__TblRestra__strCh__11158940] DEFAULT ('ACCOUNT') FOR [strCharName],
|
|
CONSTRAINT [DF__TblRestra__dateS__1209AD79] DEFAULT (getdate()) FOR [dateStartTime],
|
|
CONSTRAINT [DF__TblRestra__intEn__12FDD1B2] DEFAULT (0) FOR [intEndTime]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblDetailLog] ADD
|
|
CONSTRAINT [DF_TblDetailLog_UpdateDate] DEFAULT (getdate()) FOR [UpdateDate]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TblLog] ADD
|
|
CONSTRAINT [DF_TblLog_Login] DEFAULT (getdate()) FOR [Login]
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
-- 블럭 하기
|
|
CREATE PROC CMS_DoingRestraintChar @ServerID TINYINT,@UID INT, @strClientID VARCHAR(20), @strCharName varchar(16),@Type tinyint,@Kind tinyint,@EndDate varchar(10),
|
|
@AdminID VARCHAR(20),@Description VARCHAR(50)
|
|
AS
|
|
SET NOCOUNT ON
|
|
|
|
DECLARE @EndDateTime INT
|
|
|
|
IF @strCharName IS NULL
|
|
BEGIN
|
|
SET @strCharName = 'Unknown'
|
|
END
|
|
|
|
SET @EndDateTime = cast(@EndDate as INT)
|
|
|
|
INSERT INTO TblRestraintChar (tinyServerID,UID,strClientID,strCharName,tinyType,tinyKind,tinyBlocked,dateEndTime,strAdminID,strDescription)
|
|
VALUES (@ServerID,@UID,@strClientid,@strCharName,@Type,@Kind,1,GETDATE() + @EndDateTime,@AdminID,@Description)
|
|
|
|
SELECT @@ROWCOUNT
|
|
|
|
GO
|
|
SET QUOTED_IDENTIFIER OFF
|
|
GO
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
|
|
-- 블럭 체크
|
|
CREATE Proc USPCheckRestraintChar @ServerID Tinyint,@UID INT
|
|
AS
|
|
|
|
SET NOCOUNT ON
|
|
|
|
DECLARE @tinyType TINYINT
|
|
SET @tinyType = 0
|
|
|
|
DECLARE @tinyKind TINYINT -- 1:영구,0:임시
|
|
DECLARE @EndTime INT
|
|
DECLARE @dateEndTime SMALLDATETIME
|
|
DECLARE @intEndTime INT
|
|
|
|
DECLARE @RETURNCHAR1 VARCHAR(20)
|
|
DECLARE @RETURNCHAR2 VARCHAR(20)
|
|
DECLARE @RETURNCHAR3 VARCHAR(20)
|
|
DECLARE @RETURNCHAR4 VARCHAR(20)
|
|
DECLARE @RETURNCHAR5 VARCHAR(20)
|
|
|
|
DECLARE @strCharName VARCHAR(20)
|
|
DECLARE @TIME INT
|
|
|
|
SELECT @tinyType=tinyType,@tinyKind = tinykind,@EndTime =DATEDIFF(MINUTE,GETDATE(),dateEndTime)
|
|
FROM TblRestraintChar
|
|
WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1
|
|
ORDER BY tinyType
|
|
|
|
IF @tinyType = 0 -- 블럭안함
|
|
BEGIN
|
|
SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
|
|
RETURN
|
|
END
|
|
ELSE IF @tinyType = 1 -- 계정블럭
|
|
BEGIN
|
|
|
|
-- 기간여부체크
|
|
IF (@EndTime <= 0 and @tinyKind = 0) --블럭해제된 상태임
|
|
BEGIN
|
|
UPDATE TblRestraintChar
|
|
SET tinyBlocked = 0
|
|
WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1
|
|
|
|
SET @tinyType = 0
|
|
|
|
SELECT @tinyType=tinyType,@tinyKind = tinykind,@EndTime =DATEDIFF(MINUTE,GETDATE(),dateEndTime)
|
|
FROM TblRestraintChar
|
|
WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1
|
|
ORDER BY tinyType
|
|
|
|
IF @tinyType = 2 --캐릭터블럭
|
|
BEGIN
|
|
SET @strCharName = NULL
|
|
SET @EndTime = 0
|
|
SET @TIME = 1
|
|
|
|
DECLARE Restraint_Cursor_A CURSOR FOR
|
|
SELECT strCharName,DATEDIFF(MINUTE,GETDATE(),dateEndTime),tinyKind
|
|
FROM TblRestraintChar
|
|
WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1
|
|
|
|
OPEN Restraint_Cursor_A FETCH NEXT FROM Restraint_Cursor_A INTO @strCharName,@EndTime,@tinyKind
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
IF ((@EndTime > 0) or (@tinyKind = 1))
|
|
BEGIN
|
|
|
|
IF @TIME = 1
|
|
SET @RETURNCHAR1 = @strCharName
|
|
ELSE IF @TIME = 2
|
|
SET @RETURNCHAR2 = @strCharName
|
|
ELSE IF @TIME = 3
|
|
SET @RETURNCHAR3 = @strCharName
|
|
ELSE IF @TIME = 4
|
|
SET @RETURNCHAR4 = @strCharName
|
|
ELSE IF @TIME = 5
|
|
SET @RETURNCHAR5 = @strCharName
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
UPDATE TblRestraintChar
|
|
SET tinyBlocked = 0
|
|
WHERE tinyServerID=@ServerID AND UID = @UID AND strCharName = @strCharName AND tinyBlocked = 1
|
|
END
|
|
|
|
|
|
SET @TIME = @TIME + 1
|
|
|
|
FETCH NEXT FROM Restraint_Cursor_A into @strCharName,@EndTime,@tinyKind
|
|
|
|
END
|
|
|
|
CLOSE Restraint_Cursor_A
|
|
DEALLOCATE Restraint_Cursor_A
|
|
|
|
IF ((@RETURNCHAR1 IS NULL) AND (@RETURNCHAR2 IS NULL) AND (@RETURNCHAR3 IS NULL) and (@RETURNCHAR4 IS NULL) and (@RETURNCHAR5 IS NULL))
|
|
BEGIN
|
|
SET @tinyType = 0
|
|
END
|
|
|
|
SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
|
|
RETURN
|
|
END
|
|
|
|
SET @tinyType = 0
|
|
SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
|
|
RETURN
|
|
END
|
|
END
|
|
ELSE IF @tinyType = 2 --캐릭터블럭
|
|
BEGIN
|
|
SET @strCharName = NULL
|
|
SET @EndTime = 0
|
|
SET @TIME = 1
|
|
|
|
DECLARE Restraint_Cursor CURSOR FOR
|
|
SELECT strCharName,DATEDIFF(MINUTE,GETDATE(),dateEndTime),tinyKind
|
|
FROM TblRestraintChar
|
|
WHERE tinyServerID=@ServerID AND UID = @UID AND tinyBlocked = 1
|
|
|
|
|
|
OPEN Restraint_Cursor FETCH NEXT FROM Restraint_Cursor INTO @strCharName,@EndTime,@tinyKind
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
IF ((@EndTime > 0) or (@tinyKind = 1))
|
|
BEGIN
|
|
|
|
IF @TIME = 1
|
|
SET @RETURNCHAR1 = @strCharName
|
|
ELSE IF @TIME = 2
|
|
SET @RETURNCHAR2 = @strCharName
|
|
ELSE IF @TIME = 3
|
|
SET @RETURNCHAR3 = @strCharName
|
|
ELSE IF @TIME = 4
|
|
SET @RETURNCHAR4 = @strCharName
|
|
ELSE IF @TIME = 5
|
|
SET @RETURNCHAR5 = @strCharName
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
UPDATE TblRestraintChar
|
|
SET tinyBlocked = 0
|
|
WHERE tinyServerID=@ServerID AND UID = @UID AND strCharName = @strCharName AND tinyBlocked = 1
|
|
END
|
|
|
|
|
|
SET @TIME = @TIME + 1
|
|
|
|
FETCH NEXT FROM Restraint_Cursor into @strCharName,@EndTime,@tinyKind
|
|
|
|
END
|
|
|
|
CLOSE Restraint_Cursor
|
|
DEALLOCATE Restraint_Cursor
|
|
|
|
IF ((@RETURNCHAR1 IS NULL) AND (@RETURNCHAR2 IS NULL) AND (@RETURNCHAR3 IS NULL) and (@RETURNCHAR4 IS NULL) and (@RETURNCHAR5 IS NULL))
|
|
BEGIN
|
|
SET @tinyType = 0
|
|
END
|
|
|
|
SELECT @tinyType,@RETURNCHAR1,@RETURNCHAR2,@RETURNCHAR3,@RETURNCHAR4,@RETURNCHAR5
|
|
RETURN
|
|
END
|
|
|
|
|
|
|
|
GO
|
|
SET QUOTED_IDENTIFIER OFF
|
|
GO
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
|
|
|
|
CREATE PROC USPDeleteRestraintChar @SeverID tinyint,@UID INT,@Charname varchar(16)
|
|
AS
|
|
|
|
|
|
SET NOCOUNT ON
|
|
|
|
IF EXISTS (SELECT UID FROM TblRestraintChar WHERE tinyServerID=@SeverID AND UID = @UID AND strCharName = @Charname)
|
|
BEGIN
|
|
DELETE TblRestraintChar
|
|
WHERE tinyServerID=@SeverID AND UID = @UID AND strCharName = @Charname
|
|
|
|
SELECT @@ROWCOUNT
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT 0
|
|
END
|
|
|
|
|
|
GO
|
|
SET QUOTED_IDENTIFIER OFF
|
|
GO
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|