USE [Cosmos_Logs]
GO
/****** Object:  Table [dbo].[TriggerTable]    Script Date: 10/24/2008 08:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TriggerTable](
	[dummy] [int] NULL,
	[id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_TriggerTable] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LoggedTables]    Script Date: 10/24/2008 08:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LoggedTables](
	[DBName] [sysname] NOT NULL,
	[TableName] [sysname] NOT NULL,
	[InsertDate] [datetime] NULL,
	[InsertedUser] [sysname] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LoggedTableHistory]    Script Date: 10/24/2008 08:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LoggedTableHistory](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DBName] [sysname] NOT NULL,
	[TableName] [sysname] NOT NULL,
	[StartedDate] [datetime] NULL,
	[StartedByUser] [sysname] NOT NULL,
	[StoppedDate] [datetime] NULL,
	[StoppedByUser] [sysname] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LogInfo]    Script Date: 10/24/2008 08:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LogInfo](
	[Last_Log_Id] [bigint] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Logs]    Script Date: 10/24/2008 08:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Logs](
	[LOG_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[DBName] [sysname] NULL,
	[TableName] [sysname] NOT NULL,
	[inserted] [xml] NULL,
	[deleted] [xml] NULL,
	[OperationDate] [datetime] NULL,
	[InsertDate] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LogDetail]    Script Date: 10/24/2008 08:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LogDetail](
	[LOG_DETAIL_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[LOG_ID] [bigint] NULL,
	[ROW_ID] [bigint] NULL,
	[COLUMN_NAME] [sysname] NOT NULL,
	[DATA_TYPE] [sysname] NOT NULL,
	[INSERTED_VALUE] [nvarchar](max) NULL,
	[DELETED_VALUE] [nvarchar](max) NULL,
	[IsPK] [bit] NULL
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[SP_Create_Job]    Script Date: 10/24/2008 08:21:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_Create_Job]
AS

DECLARE @DBID INT;
SET @DBID = DB_ID();

DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();

RAISERROR (
	N'Please reference to contents of this SP to create COSMOS_LOG_PARSER job!',
    17, -- Severity
    1 -- State
); 


/*
USE [msdb]
GO
--- Object:  Job [COSMOS_LOG_PARSER]    Script Date: 01/11/2008 15:39:58 ---
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
--- Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/11/2008 15:39:58 ---
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'COSMOS_LOG_PARSER', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Log Parser for Cosmos databases tables logs', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'cosmossa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--- Object:  Step [Run SP_LogParser]    Script Date: 01/11/2008 15:39:59 ---
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SP_LogParser', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC SP_LogParser', 
		@database_name=N'Cosmos_Logs', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'CosmosLogParserScheduleForEveryFiveMinutes', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20080111, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
*/
GO
/****** Object:  StoredProcedure [dbo].[SP_LogParser]    Script Date: 10/24/2008 08:21:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_LogParser]

AS

SET NOCOUNT ON

declare @logid bigint, @processed bit, @lastlogid bigint
select @processed = 0, @logid = (Last_Log_Id + 1) from LogInfo
select @lastlogid = LOG_ID from logs (nolock)

DECLARE @dbname sysname, @table sysname, @deleted xml, @inserted xml, @operation varchar(10), @i_count int, @d_count int, @count int, @i int

DECLARE @COLUMN_NAME sysname, @DATA_TYPE sysname, @Id int
DECLARE @xq_del nvarchar(max), @xq_ins nvarchar(max), @xq nvarchar(max)
DECLARE @CN sysname, @InsertedValue nvarchar(max), @DeletedValue nvarchar(max)

create table #pk_table (
	TABLE_NAME sysname,
	COLUMN_NAME sysname, 
	DATA_TYPE sysname,
	COLUMN_ID int,
	IsPK bit
)

WHILE (@logid <= @lastlogid)
BEGIN
---- LOOP FOR EACH LOG ----------------------------------------------------

select @dbname = DBName, @table = TableName, @deleted = deleted, @inserted = inserted from logs where log_id = @logid;

select @i_count = (select count(del.value('.','nvarchar(max)')) from @deleted.nodes('/deleted') T(del)) 
select @d_count = (select count(ins.value('.','nvarchar(max)')) from @inserted.nodes('/inserted') T(ins)) 
select @i = 1, @count = case when @i_count = 0 then @d_count else @i_count end

IF (@inserted IS NOT NULL) AND (@deleted IS NOT NULL)
	SET @operation = N'UPDATE'
ELSE IF (@inserted IS NOT NULL)
	SET @operation = N'INSERT'
ELSE IF (@deleted IS NOT NULL)
	SET @operation = N'DELETE'

-----------------------------------------
declare @Columns_Query nvarchar(max)

select @Columns_Query = N'
insert into #pk_table (TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID)
select
	c.TABLE_NAME,
	c.COLUMN_NAME,
	c.DATA_TYPE,
	COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME), c.COLUMN_NAME, ''ColumnID'')
FROM ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = ''' + @table + ''''

EXEC sp_executesql @Columns_Query
-----------------------------------------

--------------- PRIMARYKEY --------------
declare @PK_Query nvarchar(max)

select @PK_Query = N'
update #pk_table
set IsPK = 1
where COLUMN_NAME in (
select
	c.name
from ' + @dbname + '.sys.indexes i (nolock)
inner join ' + @dbname + '.sys.index_columns ic (nolock) on ic.object_id = i.object_id and ic.index_id = i.index_id
inner join ' + @dbname + '.sys.columns c (nolock) on c.object_id = ic.object_id and c.column_id = ic.column_id
inner join ' + @dbname + '.sys.types t (nolock) on t.system_type_id = c.system_type_id
where 
	i.object_id = Object_ID(''' + @dbname + '.dbo.' + @table + ''') 
	and i.is_primary_key = 1
)'

EXEC sp_executesql @PK_Query
---------------------------------


while (@i <= @count)
begin
	------------------------------------------
	DECLARE Columns_cursor CURSOR FAST_FORWARD FOR 
		with cte (RowNum, COLUMN_NAME, DATA_TYPE, xq_del, xq_ins, xq)
		as
		(
			select
				ROW_NUMBER() OVER ( ORDER BY COLUMNPROPERTY(OBJECT_ID(@table), c.COLUMN_NAME, 'ColumnID') ) AS RowNum,
				c.COLUMN_NAME,
				c.DATA_TYPE,
				'select @DeletedValue = @deleted.value(''(/deleted[' + CAST(@i as varchar(10)) + ']/' + c.COLUMN_NAME + ')[1]'', ''nvarchar(max)'')' xq_del,
				'select @InsertedValue = @inserted.value(''(/inserted[' + CAST(@i as varchar(10)) + ']/' + c.COLUMN_NAME + ')[1]'', ''nvarchar(max)'')' xq_ins,
				'select @CN = ''' + c.COLUMN_NAME + ''' where @inserted.value(''(/inserted[' + CAST(@i as varchar(10)) + ']/' + c.COLUMN_NAME + ')[1]'', ''nvarchar(max)'') <> @deleted.value(''(/deleted[' + CAST(@i as varchar(10)) + ']/' + c.COLUMN_NAME + ')[1]'', ''nvarchar(max)'')' xq
			from logs
			inner join #pk_table c ON c.TABLE_NAME = logs.TableName
			where logs.log_id = @logid
		)
		select COLUMN_NAME, DATA_TYPE, RowNum, xq_del, xq_ins, xq from cte order by RowNum

	OPEN Columns_cursor 

	FETCH NEXT FROM Columns_cursor INTO @COLUMN_NAME, @DATA_TYPE, @Id, @xq_del, @xq_ins, @xq

	WHILE @@FETCH_STATUS = 0
	BEGIN
		------------------------

			SELECT @CN = NULL, @InsertedValue = NULL, @DeletedValue = NULL, @processed = 1
			exec sp_executesql @xq, N'@deleted xml, @inserted xml, @CN sysname OUT', @deleted = @deleted, @inserted = @inserted, @CN = @CN OUT;
			exec sp_executesql @xq_del, N'@deleted xml, @DeletedValue nvarchar(max) OUT', @deleted = @deleted, @DeletedValue = @DeletedValue OUT;
			exec sp_executesql @xq_ins, N'@inserted xml, @InsertedValue nvarchar(max) OUT', @inserted = @inserted, @InsertedValue = @InsertedValue OUT;

			INSERT INTO LogDetail (
				LOG_ID,
				ROW_ID,
				COLUMN_NAME,
				DATA_TYPE,
				INSERTED_VALUE,
				DELETED_VALUE,
				IsPK
			)
			SELECT 
				@logid logid, 
				@i,
				@COLUMN_NAME COLUMN_NAME, 
				@DATA_TYPE DATA_TYPE, 
				@InsertedValue Inserted, 
				@DeletedValue Deleted,
				CASE WHEN EXISTS(select * from #pk_table where COLUMN_NAME = @COLUMN_NAME AND IsPK = 1) THEN 1 ELSE NULL END
			WHERE 
				(@COLUMN_NAME IN (select COLUMN_NAME from #pk_table where IsPK = 1)) OR
				(@operation = N'INSERT' AND @InsertedValue IS NOT NULL) OR 
				(@operation = N'DELETE' AND @DeletedValue IS NOT NULL) OR 
				(
					(@operation = N'UPDATE') AND 
					(
						(
							COALESCE(@InsertedValue, @DeletedValue) IS NOT NULL AND 
							(@InsertedValue <> @DeletedValue)
						) OR
						(
							(@InsertedValue IS NOT NULL AND @DeletedValue IS NULL) OR 
							(@DeletedValue IS NOT NULL AND @InsertedValue IS NULL)
						)
					)
				)

		FETCH NEXT FROM Columns_cursor INTO @COLUMN_NAME, @DATA_TYPE, @Id, @xq_del, @xq_ins, @xq
		------------------------
	END

	CLOSE Columns_cursor 
	DEALLOCATE Columns_cursor 

	------------------------------------------
	set @i = @i + 1
end

delete from #pk_table -- PrimaryKey table

if @processed = 1
	UPDATE LogInfo SET Last_Log_Id = @logid

---- (END OF) LOOP FOR EACH LOG -----------------------------------------------
	set @logid = @logid + 1
END

drop table #pk_table -- PrimaryKey table

SET NOCOUNT OFF
GO
/****** Object:  StoredProcedure [dbo].[SP_InitialValues]    Script Date: 10/24/2008 08:21:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_InitialValues]
AS

if not exists(select * from LogInfo)
	insert into LogInfo select 0
GO
/****** Object:  Default [DF_LoggedTables_InsertDate]    Script Date: 10/24/2008 08:20:12 ******/
ALTER TABLE [dbo].[LoggedTables] ADD  CONSTRAINT [DF_LoggedTables_InsertDate]  DEFAULT (getdate()) FOR [InsertDate]
GO
/****** Object:  Default [DF_LoggedTables_InsertedUser]    Script Date: 10/24/2008 08:20:12 ******/
ALTER TABLE [dbo].[LoggedTables] ADD  CONSTRAINT [DF_LoggedTables_InsertedUser]  DEFAULT (suser_sname()) FOR [InsertedUser]
GO
/****** Object:  Default [DF__logs__InsertDate__0BC6C43E]    Script Date: 10/24/2008 08:20:12 ******/
ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF__logs__InsertDate__0BC6C43E]  DEFAULT (getdate()) FOR [InsertDate]
GO

EXEC [dbo].[SP_InitialValues]
GO