USE [LogDB_2800]
GO
/****** Object:  StoredProcedure [dbo].[spCreateBcpResult]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spCreateBcpResult]
	-- Add the parameters for the stored procedure here
	@logFileName	nvarchar(256),
	@movedFileName	nvarchar(256),
	@bcpLines	int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    insert into bcpResult( logFileName, movedFileName, bcpLines)
	values
	(@logFileName, @movedFileName, @bcpLines)
END
GO
/****** Object:  StoredProcedure [dbo].[spCreateBcpResult2]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCreateBcpResult2]
	-- Add the parameters for the stored procedure here
	@logFileName	nvarchar(256),
	@movedFileName	nvarchar(256),
	@bcpLines	int,
	@execTime	bigint

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='bcpResult2')
	create table bcpResult2 (
		bcpTime timestamp, 
		logFileName nvarchar(256), 
		movedFileName nvarchar(256), 
		bcpLines int,
		execTime bigint)		

    insert into bcpResult2( logFileName, movedFileName, bcpLines, execTime)
	values
	(@logFileName, @movedFileName, @bcpLines, @execTime)
END
GO
/****** Object:  StoredProcedure [dbo].[spCreateLogTableChat]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCreateLogTableChat]
	@logTableName	NVARCHAR(128)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sqlquery VARCHAR(1024);

	SET @sqlquery =
		'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''')  \
		begin \
		create table ' + @logTableName + '( \
			timeStamp datetime,\
			planetId int,\
			serverType int,\
			logType int,\
			intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int, intVal11 int, intVal12 int, intVal13 int, intVal14 int, intVal15 int,\
			int64Val1 bigint, int64Val2 bigint, int64Val3 bigint, int64Val4 bigint,\
			itemDbId bigint,\
			contId int, channelId int, posX int, posY int, posZ int, \
			stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128) , chat nvarchar(516)   )
			
		CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName +  '( \
		 logType, 	intVal1  ) \
			
		end ';

	EXEC (@sqlquery);



END
GO
/****** Object:  StoredProcedure [dbo].[spCreateLogTableChat_Old]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCreateLogTableChat_Old]
	@logTableName	NVARCHAR(128)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sqlquery VARCHAR(1024);

	SET @sqlquery =
		'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''')  \
		begin \
		create table ' + @logTableName + '( \
			timeStamp datetime,\
			planetId int,\
			serverType int,\
			logType int,\
			intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int,\
			doubleVal1 float, doubleVal2 float, doubleVal3 float, doubleVal4 float,\
			int64Val1 bigint, int64Val2 bigint,\
			itemDbId bigint,\
			contId int, channelId int, posX int, posY int, posZ int, \
			stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128) , chat nvarchar(516)   )
			
		CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName +  '( \
		 logType, 	intVal1  ) \
			
		end ';

	EXEC (@sqlquery);



END
GO
/****** Object:  StoredProcedure [dbo].[spCreateLogTableEx]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* 로그 테이블 만들기 프로시져 생성 */
CREATE PROCEDURE [dbo].[spCreateLogTableEx]
	@logTableName	NVARCHAR(128)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sqlquery VARCHAR(2048);

	SET @sqlquery =
		'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''')  \
		begin  \
		
		create table ' + @logTableName + '( \
			timeStamp datetime,\
			planetId int,\
			serverType int,\
			logType int,\
			intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int, intVal11 int, intVal12 int, intVal13 int, intVal14 int, intVal15 int,\
			int64Val1 bigint, int64Val2 bigint, int64Val3 bigint, int64Val4 bigint,\
			itemDbId bigint,\
			contId int, channelId int, posX int, posY int, posZ int, \
			stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128)) \
			
		CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + ' (timeStamp, stringVal1, logType) \
		CREATE NONCLUSTERED INDEX IX2_' + @logTableName + ' ON dbo.' + @logTableName + ' (itemDbId, timeStamp, logType) \
		
		end ';

	/*
		CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + ' (logType, intVal1, intVal2, intVal3 ) \
		CREATE NONCLUSTERED INDEX IX2_' + @logTableName + ' ON dbo.' + @logTableName + ' (stringVal1) \
		CREATE NONCLUSTERED INDEX IX3_' + @logTableName + ' ON dbo.' + @logTableName + ' (itemDbId) where (itemDbId <> 0) \
	*/

	EXEC (@sqlquery);
END
GO
/****** Object:  StoredProcedure [dbo].[spCreateLogTableEx_Old]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCreateLogTableEx_Old]
	@logTableName	NVARCHAR(128)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sqlquery VARCHAR(1024);

	SET @sqlquery =
		'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''')  \
		begin  \
		create table ' + @logTableName + '( \
			timeStamp datetime,\
			planetId int,\
			serverType int,\
			logType int,\
			intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int,\
			doubleVal1 float, doubleVal2 float, doubleVal3 float, doubleVal4 float,\
			int64Val1 bigint, int64Val2 bigint,\
			itemDbId bigint,\
			contId int, channelId int, posX int, posY int, posZ int, \
			stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128)) \
				
		CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName +  '( \
			logType, 	intVal1, intVal2, intVal3 ) 
		
		end ';


	EXEC (@sqlquery);


END
GO
/****** Object:  StoredProcedure [dbo].[spDeleteOldLogTable]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDeleteOldLogTable]
	@daysBefore	int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;



	DECLARE tables_cursor CURSOR
		FOR
			select name from sys.objects (nolock) 
			WHERE type='U' and create_date < dateadd(day, @daysBefore, GETUTCDATE())
			and ( name like 'GAMELOG%' or name like 'CHATLOG%' or name like 'AUDITLOG%' )
		
	OPEN tables_cursor
	DECLARE @tablename sysname
	FETCH NEXT FROM tables_cursor INTO @tablename
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		EXEC ( 'drop table ' + @tablename)
		-- EXEC ( 'select ''' + @tablename + ''' ')
		
		FETCH NEXT FROM tables_cursor INTO @tablename
	END
	DEALLOCATE tables_cursor
	
	

    
END
GO
/****** Object:  StoredProcedure [dbo].[spGetAssertLogs]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spGetAssertLogs]
as
begin
	set nocount on;
	
	declare @lastTime datetime;
	set @lastTime = (select fetchtime from AssertLogLastFetchedInfo);
	
	declare @curTime datetime;
	set @curTime = GETUTCDATE();
	
	delete from AssertLogLastFetchedInfo;
	
	insert into AssertLogLastFetchedInfo (fetchtime) values (@curTime);
	
	select timeStamp, planetId, serverType, stringVal1, stringVal2
	from AssertLog
	where timeStamp >= @lastTime and timeStamp < @curTime;
end
GO
/****** Object:  StoredProcedure [dbo].[spLogDirect]    Script Date: 7/19/2022 8:22:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spLogDirect]
	@timeStamp datetime,
	@planetId int,
	@serverType int,
	@logType int,
	@intVal1 int,
	@intVal2 int,
	@intVal3 int,
	@intVal4 int,
	@intVal5 int,
	@intVal6 int,
	@intVal7 int,
	@intVal8 int,
	@intVal9 int,
	@intVal10 int,
	@intVal11 int,
	@intVal12 int,
	@intVal13 int,
	@intVal14 int,
	@intVal15 int,
	@int64Val1 bigint,
	@int64Val2 bigint,
	@int64Val3 bigint,
	@int64Val4 bigint,
	@stringVal1 nvarchar(128),
	@stringVal2 nvarchar(128),
	@stringVal3 nvarchar(128),
	@stringVal4 nvarchar(128)
as
begin
	set nocount on;
	
	insert into AssertLog (
			timeStamp, planetId, serverType, logType,
			intVal1, intVal2, intVal3, intVal4, intVal5, intVal6, intVal7, intVal8, intVal9, intVal10, intVal11, intVal12, intVal13, intVal14, intVal15,
			int64Val1, int64Val2, int64Val3, int64Val4,
			stringVal1, stringVal2, stringVal3, stringVal4 )
	values (
			@timeStamp, @planetId, @serverType, @logType,
			@intVal1, @intVal2, @intVal3, @intVal4, @intVal5, @intVal6, @intVal7, @intVal8, @intVal9, @intVal10, @intVal11, @intVal12, @intVal13, @intVal14, @intVal15,			
			@int64Val1, @int64Val2, @int64Val3, @int64Val4,
			@stringVal1, @stringVal2, @stringVal3, @stringVal4 );
end
GO