-- 625


--------------------------------------------------------------------------------
--
-- Common Utilities
--
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--
-- Upgrade_DbDeleteFunction
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_DbDeleteFunction]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Upgrade_DbDeleteFunction]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Upgrade_DbDeleteFunction]
GO

	CREATE PROCEDURE [dbo].[Upgrade_DbDeleteFunction]
		@function_name nvarchar(max)
	AS
	BEGIN
		IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@function_name) AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
		BEGIN
			DECLARE @command nvarchar(max)
			SET @command = 'DROP FUNCTION ' + @function_name
			EXEC (@command)
		END
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_TransformCppText
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_TransformCppText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_TransformCppText]'
GO

	CREATE FUNCTION [dbo].[Upgrade_TransformCppText] (
		@text nvarchar(max))
	RETURNS nvarchar(max)
	AS
	BEGIN
		SET @text = replace(@text, '\t', CHAR(9))
		SET @text = replace(@text, '\n', CHAR(10))
		SET @text = replace(@text, '\r', CHAR(13))

		RETURN @text
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatString
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatString] (
		@format nvarchar(255),
		@arg0 sql_variant)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @result nvarchar(255)
		SET @format = dbo.Upgrade_TransformCppText(@format)
		EXEC xp_sprintf @result OUTPUT, @format, @arg0
		RETURN @result
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatXmlText
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatXmlText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatXmlText]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatXmlText] (
		@text nvarchar(max),
		@count int,
		@prefix nvarchar(255) = '\t')
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @str nvarchar(255)
		SET @str = replicate(@prefix, @count)	

		SET @text = replace(@text, '><', '>\r\n' + @str + '<')

		IF (len(@text) != 0)
		BEGIN
			SET @text = @str + @text
		END

		SET @text = dbo.Upgrade_TransformCppText(@text)
		RETURN @text
	END
GO


--------------------------------------------------------------------------------
--
-- Upgrade_FormatXmlDocument
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_FormatXmlDocument]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatXmlDocument]'
GO

	CREATE FUNCTION [dbo].[Upgrade_FormatXmlDocument] (
		@document xml,
		@count int,
		@prefix nvarchar(255) = '\t')
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @text nvarchar(max)
		SET @text = cast(@document as nvarchar(max))
		RETURN dbo.Upgrade_FormatXmlText(@text, @count, @prefix)
	END
GO


--------------------------------------------------------------------------------
--
-- Re-IP
--
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--
-- Upgrade_IsReplicationRuleIpAddressUpdated
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_IsReplicationRuleIpAddressUpdated]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_IsReplicationRuleIpAddressUpdated]'
GO

	CREATE FUNCTION [dbo].[Upgrade_IsReplicationRuleIpAddressUpdated] (
		@address nvarchar(255))
	RETURNS bit
	AS
	BEGIN
		-- Empty address
		IF len(@address) = 0
		BEGIN
			RETURN 'TRUE'
		END

		-- Address is already updated
		IF patindex('%*%', @address collate SQL_Latin1_General_CP1_CI_AS) <> 0
		BEGIN
			RETURN 'TRUE'
		END

		RETURN 'FALSE'
	END
	GO


--------------------------------------------------------------------------------
--
-- Upgrade_GetReplicationRuleData
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_GetReplicationRuleData]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetReplicationRuleData]'
GO

	CREATE FUNCTION [dbo].[Upgrade_GetReplicationRuleData] (
		@address nvarchar(255))
	RETURNS @result table
	(
		[position] int,
		[octet] int,
		[pattern] nvarchar(255),
		[part] nvarchar(255)
	)
	AS
	BEGIN
		DECLARE @table as table ([octet] int, [pattern] nvarchar(255), [part] nvarchar(255))

		INSERT INTO @table (octet, pattern, part)
			SELECT 1, '0.0.0.0', '*.*.*.*'
			UNION ALL
			SELECT 2, '%.0.0.0', '.*.*.*'
			UNION ALL
			SELECT 3, '%.0.0', '.*.*'
			UNION ALL
			SELECT 4, '%.0', '.*'

		INSERT INTO
			@result
		SELECT
			*
		FROM
		(
			SELECT
				patindex(pattern, @address collate SQL_Latin1_General_CP1_CI_AS) as position,
				octet,
				pattern,
				part
			FROM
				@table
		)
		AS
			temp

		-- |  2 | 0 | '0.0.0.0' | '*.*.*.*'
		-- |  8 | 1 | '%.0.0.0' | '.*.*.*'
		-- | 12 | 2 | '%.0.0'   | '.*.*'
		-- | 13 | 3 | '%.0'     | '.*'

		RETURN
	END
	GO


--------------------------------------------------------------------------------
--
-- Upgrade_UpdateReplicationRule
--
--------------------------------------------------------------------------------
PRINT 'Creating [dbo].[Upgrade_UpdateReplicationRule]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateReplicationRule]'
GO

	CREATE FUNCTION [dbo].[Upgrade_UpdateReplicationRule] (
		@xml_rule xml)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @text nvarchar(max)
		SET @text = cast(@xml_rule as nvarchar(max))

		DECLARE @source_address nvarchar(255)
		DECLARE @target_address nvarchar(255)

		SET @source_address = (SELECT @xml_rule.value('(/Rule/Source/@Address)[1]', 'nvarchar(max)'))
		SET @target_address = (SELECT @xml_rule.value('(/Rule/Target/@Address)[1]', 'nvarchar(max)'))
		
		IF [dbo].[Upgrade_IsReplicationRuleIpAddressUpdated](@source_address) = 'FALSE' AND [dbo].[Upgrade_IsReplicationRuleIpAddressUpdated](@target_address) = 'FALSE'
		BEGIN
			DECLARE @source_rule nvarchar(255)
			DECLARE @target_rule nvarchar(255)

			-- '127.0.0.0' -> '127.*.*.*'
			SELECT TOP 1
				@source_rule = substring(@source_address, 0, src.position) + src.part,
				@target_rule = substring(@target_address, 0, trg.position) + trg.part			
			FROM
				[dbo].[Upgrade_GetReplicationRuleData](@source_address) AS src
			CROSS APPLY
				[dbo].[Upgrade_GetReplicationRuleData](@target_address) AS trg
			WHERE
				src.octet = trg.octet
			AND
				src.position <> 0 AND trg.position <> 0

			IF @@rowcount <> 0
			BEGIN
				SET @text = replace(@text, '<Source Address="' + @source_address, '<Source Address="' + @source_rule)
				SET @text = replace(@text, '<Target Address="' + @target_address, '<Target Address="' + @target_rule)
			END
		END

		RETURN @text
	END
	GO


--------------------------------------------------------------------------------
--
-- Begin Upgrade
--
--------------------------------------------------------------------------------
SET NOCOUNT ON

DECLARE @db_version int
SET @db_version = (SELECT max([current_version]) FROM [dbo].[Version])

PRINT ''
PRINT 'Upgrading database...'
PRINT dbo.Upgrade_FormatString('\t[Time Stamp]="%s"', convert(varchar, getdate(), 113))
PRINT dbo.Upgrade_TransformCppText('\t[Current Version]="6.X"')
PRINT dbo.Upgrade_TransformCppText('\t[Next Version]="6.5"')
PRINT dbo.Upgrade_FormatString('\t[Database Version]="%s"', @db_version)
PRINT dbo.Upgrade_FormatString('\t[Server Name]="%s"', @@servername)
PRINT dbo.Upgrade_FormatString('\t[Process ID]="%s"', @@spid)
PRINT dbo.Upgrade_FormatString('\t[User Name]="%s"', SYSTEM_USER)
PRINT ''

GO


--------------------------------------------------------------------------------
--
-- Replication Job
--
--------------------------------------------------------------------------------
PRINT 'Upgrading "Replication Jobs"...'
PRINT dbo.Upgrade_FormatString('\t[Start Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''

DECLARE @job_count int
SET @job_count = 0


DECLARE replication_jobs_cursor CURSOR FOR
SELECT
	[id],
	[name],
	[options]
FROM
	[dbo].[BJobs]
WHERE
	[type] = 1

OPEN replication_jobs_cursor

DECLARE @job_id uniqueidentifier
DECLARE @job_name nvarchar(255)
DECLARE @job_options xml

FETCH NEXT FROM
	replication_jobs_cursor
INTO
	@job_id,
	@job_name,
	@job_options

WHILE @@fetch_status = 0
BEGIN                                                             
	BEGIN TRANSACTION t_replication_jobs
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	IF @job_options IS NOT NULL
	BEGIN
		PRINT dbo.Upgrade_TransformCppText('BEGIN ITERATION: REPLICATION JOB')
		PRINT dbo.Upgrade_TransformCppText('\tLoading "Replication Job":')
		PRINT dbo.Upgrade_FormatString('\t\t[Id]="%s"', @job_id)
		PRINT dbo.Upgrade_FormatString('\t\t[Name]="%s"', @job_name)
		PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
		PRINT dbo.Upgrade_FormatXmlDocument(@job_options, 3, DEFAULT)

		-- Replication Jobs
		DECLARE @xml_rules table([rule] xml)
		DELETE FROM @xml_rules

		INSERT INTO
			@xml_rules
		SELECT
			T.c.query('.')
		FROM
			@job_options.nodes('/JobOptionsRoot/ReIPRules/Rule') T(c)

		-- Prepare
		SET	@job_options.modify('delete /JobOptionsRoot/ReIPRules')
		SET @job_options.modify('insert <ReIPRules>DB71332CC5AA4C19867BB8D29868303F</ReIPRules> as last into (/JobOptionsRoot)[1]')

		-- Process
		DECLARE @options nvarchar(max)
		DECLARE @rules nvarchar(max)

		SET @options = cast(@job_options as nvarchar(max))
		SET @rules = ''

		SELECT @rules = coalesce(@rules, '') + [dbo].[Upgrade_UpdateReplicationRule]([rule]) FROM @xml_rules
		SET @options = replace(@options, 'DB71332CC5AA4C19867BB8D29868303F', @rules)

		PRINT dbo.Upgrade_TransformCppText('\tUpdating "Replication Job":')
		PRINT dbo.Upgrade_FormatString('\t\t[ID]="%s"', @job_id)
		PRINT dbo.Upgrade_TransformCppText('\t\t[Options]=')
		PRINT dbo.Upgrade_FormatXmlText(@options, 3, DEFAULT)

		UPDATE
			[dbo].[BJobs]
		SET
			[options] = @options
		WHERE CURRENT OF
			replication_jobs_cursor

		----------------------------------------------------------------------------

		PRINT dbo.Upgrade_TransformCppText('END ITERATION: REPLICATION JOB')
	END

NextReplicationJobIteration:
	COMMIT TRANSACTION t_replication_jobs

	SET @job_count = @job_count + 1

	FETCH NEXT FROM
		replication_jobs_cursor
	INTO
		@job_id,
		@job_name,
		@job_options
END

CLOSE replication_jobs_cursor
DEALLOCATE replication_jobs_cursor

PRINT dbo.Upgrade_FormatString('\t[Job Count]="%s"', @job_count)
PRINT dbo.Upgrade_FormatString('\t[Total Read]="%s"', @@total_read)
PRINT dbo.Upgrade_FormatString('\t[Total Write]="%s"', @@total_write)
PRINT dbo.Upgrade_FormatString('\t[Total Errors]="%s"', @@total_errors)
PRINT dbo.Upgrade_FormatString('\t[End Date]="%s"', convert(varchar, getdate(), 113))
PRINT ''
GO


PRINT 'Deleting [dbo].[Upgrade_TransformCppText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_TransformCppText]'

PRINT 'Deleting [dbo].[Upgrade_FormatString]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatString]'

PRINT 'Deleting [dbo].[Upgrade_FormatXmlText]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatXmlText]'

PRINT 'Deleting [dbo].[Upgrade_FormatXmlDocument]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_FormatXmlDocument]'

PRINT 'Deleting [dbo].[Upgrade_IsReplicationRuleIpAddressUpdated]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_IsReplicationRuleIpAddressUpdated]'

PRINT 'Deleting [dbo].[Upgrade_GetReplicationRuleData]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_GetReplicationRuleData]'

PRINT 'Deleting [dbo].[Upgrade_UpdateReplicationRule]'
EXEC [dbo].[Upgrade_DbDeleteFunction] '[dbo].[Upgrade_UpdateReplicationRule]'

PRINT 'Deleting [dbo].[Upgrade_DbDeleteFunction]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Upgrade_DbDeleteFunction]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Upgrade_DbDeleteFunction]
GO


--
--  625 -> 626
--
IF EXISTS (SELECT * FROM [dbo].[Version] WHERE current_version = 625)
BEGIN
	IF @@Error = 0 BEGIN UPDATE [dbo].[Version] SET current_version = 626; END
END
GO
