Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spRegularUpdateConnLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spRegularUpdateConnLog]
AS
SET NOCOUNT ON
DECLARE @Year char(4)
DECLARE @Mon char(2)
DECLARE @OldDate smalldatetime
DECLARE @OldTableName varchar(128)
SET @OldDate = DATEADD( mm, -1, GETDATE() )
SET @Year = DATEPART(yy, @OldDate)
SET @Mon = DATEPART(mm, @OldDate)
SET @OldTableName = ''ConnLog_'' + @Year +
CASE WHEN @Mon < 10 THEN ''0'' + CAST(@Mon AS char(1))
ELSE CAST(@Mon AS char(2)) END
EXEC sp_rename ''ConnLog'', @OldTableName
CREATE TABLE ConnLog(
id int IDENTITY
, AID int NOT NULL
, Time smalldatetime
, IPPart1 tinyint NOT NULL
, IPPart2 tinyint NOT NULL
, IPPart3 tinyint NOT NULL
, IPPart4 tinyint NOT NULL
, CountryCode3 char(3) )
CREATE NONCLUSTERED INDEX IX_ConnLog_AID
ON ConnLog( AID )
CREATE NONCLUSTERED INDEX IX_ConnLog_Time
ON ConnLog( Time )
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetCharClan]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetCharClan]
@CID int
AS
SET NOCOUNT ON
SELECT cl.CLID AS CLID, cl.Name AS ClanName FROM ClanMember cm(nolock), Clan cl(nolock) WHERE cm.cid=@CID AND cm.CLID=cl.CLID
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanList]
@Page INT,
@Backward INT = 0
AS
SET NOCOUNT ON
BEGIN
DECLARE @PageHead INT
DECLARE @RowCount INT
IF @Backward = 0
BEGIN
SELECT @RowCount = ((@Page -1) * 15 + 1)
SET ROWCOUNT @RowCount
SELECT @PageHead = CLID FROM Clan(NOLOCK) WHERE DeleteFlag=0 ORDER BY CLID DESC
SET ROWCOUNT 15
SELECT cl.CLID AS CLID, cl.Name as ClanName, c.Name AS Master, cl.RegDate AS RegDate, cl.EmblemUrl AS EmblemUrl, cl.Point AS Point
FROM Clan cl(NOLOCK), Character c(nolock)
WHERE cl.MasterCID=c.CID AND cl.DeleteFlag=0 AND cl.CLID<@PageHead
ORDER BY cl.CLID DESC
END
ELSE
BEGIN -- 역순
SELECT @RowCount = ((@Page -1) * 15 + 1)
SET ROWCOUNT @RowCount
SELECT @PageHead = CLID FROM Clan(NOLOCK) WHERE DeleteFlag=0 ORDER BY CLID
SET ROWCOUNT 15
SELECT CLID, ClanName, Master, RegDate, EmblemUrl, Point
FROM
(
SELECT TOP 15 cl.CLID AS CLID, cl.Name as ClanName, c.Name AS Master, cl.RegDate AS RegDate, cl.EmblemUrl AS EmblemUrl, cl.Point AS Point
FROM Clan cl(NOLOCK), Character c(nolock)
WHERE cl.MasterCID=c.CID AND cl.DeleteFlag=0 AND cl.CLID>=@PageHead ORDER BY cl.CLID
) AS t
ORDER BY CLID DESC
END
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanListSearchByName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanListSearchByName]
@Name VARCHAR(24)
AS
SET NOCOUNT ON
BEGIN
SELECT TOP 20 cl.CLID AS CLID, cl.Name as ClanName, c.Name AS Master, cl.RegDate AS RegDate, cl.EmblemUrl AS EmblemUrl, cl.Point AS Point
FROM Clan cl(NOLOCK), Character c(NOLOCK)
WHERE cl.MasterCID=c.CID AND c.DeleteFlag=0 AND cl.Name=@Name
ORDER BY cl.CLID
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanMember]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanMember]
@CLID int
AS
SET NOCOUNT ON
SELECT cm.clid AS CLID, cm.Grade AS ClanGrade, c.cid AS CID, c.name AS CharName
FROM ClanMember cm(nolock), Character c(nolock)
WHERE CLID=@CLID AND cm.cid=c.cid
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanRankingHistory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanRankingHistory]
@Year INT,
@Month INT,
@Page INT,
@Backward INT = 0
AS
SET NOCOUNT ON
BEGIN
/* 한페이지에 20개씩 보여준다 (속도를위해 갯수 고정) */
DECLARE @RowCount INT
DECLARE @PageHead INT
IF @Backward = 0
BEGIN
SELECT @RowCount = ((@Page -1) * 20 + 1)
SELECT TOP 20 Ranking, ClanName as ClanName, Point, Wins, Losses, CLID FROM ClanHonorRanking(NOLOCK)
WHERE Year=@Year AND Month=@Month AND Ranking>0 AND Ranking >= @RowCount ORDER BY Ranking
END
ELSE
BEGIN
SELECT @RowCount = ((@Page -1) * 20 + 1)
SET ROWCOUNT @RowCount
SELECT @PageHead = Ranking FROM Clan(NOLOCK) WHERE DeleteFlag=0 ORDER BY Ranking DESC
SET ROWCOUNT 20
SELECT Ranking, RankIncrease=0, ClanName, Point, Wins, Losses, CLID, EmblemUrl=NULL FROM
(
SELECT TOP 20 Ranking, ClanName, Point, Wins, Losses, CLID FROM ClanHonorRanking(NOLOCK)
WHERE Year=@Year AND Month=@Month AND Ranking>0 AND Ranking <= @PageHead ORDER BY Ranking DESC
) AS t ORDER BY Ranking
END
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanRankingMaxPage]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanRankingMaxPage]
AS
SET NOCOUNT ON
BEGIN
DECLARE @MaxPage INT
SELECT TOP 1 @MaxPage = Ranking / 20 + 1 FROM Clan(NOLOCK) WHERE DeleteFlag=0 AND Ranking>0 ORDER BY Ranking DESC
-- SELECT @MaxPage
RETURN @MaxPage
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanRankingSearchByRanking]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanRankingSearchByRanking]
@Ranking INT
AS
SET NOCOUNT ON
BEGIN
SELECT TOP 20 Ranking, RankIncrease, Name as ClanName, Point, Wins, Losses, CLID, EmblemUrl FROM Clan(NOLOCK)
WHERE DeleteFlag=0 AND Ranking>0 AND Ranking=@Ranking ORDER BY Ranking
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConnLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConnLog](
[id] [int] IDENTITY(1,1) NOT NULL,
[AID] [int] NOT NULL,
[Time] [smalldatetime] NULL,
[IPPart1] [tinyint] NOT NULL,
[IPPart2] [tinyint] NOT NULL,
[IPPart3] [tinyint] NOT NULL,
[IPPart4] [tinyint] NOT NULL,
[CountryCode3] [char](3) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteAccountLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DeleteAccountLog](
[id] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](24) NOT NULL,
[AID] [int] NOT NULL,
[RegDate] [smalldatetime] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServerLogStorage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ServerLogStorage](
[id] [int] IDENTITY(1,1) NOT NULL,
[ServerID] [smallint] NOT NULL,
[PlayerCount] [int] NOT NULL,
[GameCount] [int] NOT NULL,
[BlockCount] [int] NOT NULL,
[NonBlockCount] [int] NOT NULL,
[Time] [smalldatetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
Code:
USE [GunZDB]
GO
/****** Object: StoredProcedure [dbo].[spInsertConnLog] Script Date: 05/20/2009 23:46:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[spInsertConnLog]
@AID int
, @IPPart1 tinyint
, @IPPart2 tinyint
, @IPPart3 tinyint
, @IPPart4 tinyint
, @CountryCode3 char(3)
AS
SET NOCOUNT ON
INSERT INTO ConnLog( AID, Time, IPPart1, IPPart2, IPPart3, IPPart4, CountryCode3)
VALUES (@AID, GETDATE(), @IPPart1, @IPPart2, @IPPart3, @IPPart4, @CountryCode3)
I've helped a lot of people with the same problem before. I guess I'll post a new scripted Database.
Reply With Quote
