--------------------------------------------------------------------------------
-- MaxValue
PRINT N'Creating [dbo].[MaxValue]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaxValue]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[MaxValue]
GO

	CREATE FUNCTION [dbo].[MaxValue]
		(@x1 bigint, @x2 bigint)
	RETURNS bigint
	AS
	BEGIN
		  IF (@x1 > @x2 )
				RETURN @x1;
		  IF (@x1 < @x2 )
				RETURN @x2;
		  RETURN @x1;
	END

GO

--------------------------------------------------------------------------------
-- LinkedJobsString
PRINT N'Creating [dbo].[LinkedJobsString]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LinkedJobsString]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[LinkedJobsString]
GO

	CREATE FUNCTION [dbo].[LinkedJobsString] (
		@job_id uniqueidentifier )
	RETURNS nvarchar(max)
	AS
	BEGIN

		DECLARE @Result nvarchar(max)
		
		SELECT TOP(1)
					@Result = LEFT(o.list, LEN(o.list)-1) 
				FROM 
					[dbo].[LinkedJobs] c 
				CROSS APPLY 
				( 
				SELECT [name] + ', ' AS [text()] 
				FROM [dbo].[LinkedJobs] LEFT JOIN [dbo].[BJobs] ON [dbo].[LinkedJobs].[linked_job_id] = [dbo].[BJobs].[id]
				WHERE [dbo].[LinkedJobs].[job_id] = @job_id
				FOR XML PATH ('') 
				) o (list)
		RETURN @Result
	END

GO

--------------------------------------------------------------------------------
-- MaxValue3
PRINT N'Creating [dbo].[MaxValue3]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaxValue3]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[MaxValue3]
GO

	CREATE FUNCTION [dbo].[MaxValue3]
		(@x1 bigint, @x2 bigint, @x3 bigint)
	RETURNS bigint
	AS
	BEGIN
		DECLARE @result bigint
		SELECT @result = 0
		IF (@x1 > @result)
			SELECT @result = @x1;
		IF (@x2 > @result)
			SELECT @result = @x2;
		IF (@x3 > @result)
			SELECT @result = @x3;
		RETURN @result;
	END

GO

--------------------------------------------------------------------------------
-- MaxValue4
PRINT N'Creating [dbo].[MaxValue4]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaxValue4]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[MaxValue4]
GO

	CREATE FUNCTION [dbo].[MaxValue4]
		(@x1 bigint, @x2 bigint, @x3 bigint, @x4 bigint)
	RETURNS bigint
	AS
	BEGIN
		DECLARE @result bigint
		SELECT @result = 0
		IF (@x1 > @result)
			SELECT @result = @x1;
		IF (@x2 > @result)
			SELECT @result = @x2;
		IF (@x3 > @result)
			SELECT @result = @x3;
		IF (@x4 > @result)
			SELECT @result = @x4;
		RETURN @result;
	END

GO

--------------------------------------------------------------------------------
-- GetOibsMaxUsn
PRINT N'Creating [dbo].[GetOibsMaxUsn]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOibsMaxUsn]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[GetOibsMaxUsn]
GO

	CREATE FUNCTION [dbo].[GetOibsMaxUsn]
		(@backup_id uniqueidentifier)
	RETURNS bigint
	AS
	BEGIN
		DECLARE @result bigint
		SET @result = 0
		SELECT @result = MAX([dbo].[Backup.Model.RestoreJobSessions].[usn])
		FROM [dbo].[Backup.Model.RestoreJobSessions] LEFT JOIN [dbo].[Backup.Model.JobSessions]
				ON [dbo].[Backup.Model.RestoreJobSessions].[id] = [dbo].[Backup.Model.JobSessions].[id]
				WHERE [dbo].[Backup.Model.RestoreJobSessions].[oib_id] in 
					(SELECT [dbo].[Backup.Model.OIBs].[id] 
					 FROM [dbo].[Backup.Model.Points] LEFT JOIN [dbo].[Backup.Model.OIBs] on [dbo].[Backup.Model.Points].id = [dbo].[Backup.Model.OIBs].point_id
					 WHERE [dbo].[Backup.Model.Points].backup_id = @backup_id )

		if (@result IS NULL)
		BEGIN
				SELECT @result = MAX(sess.[usn])
					FROM [Backup.Model.JobSessions] sess
						INNER JOIN [Backup.Model.Backups] bk ON sess.job_id = bk.job_id
				WHERE bk.id = @backup_id AND sess.job_id <> '00000000-0000-0000-0000-000000000000' 
		END
		RETURN @result;
	END

GO

--------------------------------------------------------------------------------
-- MaxValue5
PRINT N'Creating [dbo].[MaxValue5]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaxValue5]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[MaxValue5]
GO

	CREATE FUNCTION [dbo].[MaxValue5]
		(@x1 bigint, @x2 bigint, @x3 bigint, @x4 bigint, @x5 bigint)
	RETURNS bigint
	AS
	BEGIN
		DECLARE @result bigint
		SELECT @result = 0
		IF (@x1 > @result)
			SELECT @result = @x1;
		IF (@x2 > @result)
			SELECT @result = @x2;
		IF (@x3 > @result)
			SELECT @result = @x3;
		IF (@x4 > @result)
			SELECT @result = @x4;
		IF (@x5 > @result)
			SELECT @result = @x5;		
		RETURN @result;
	END

GO

--------------------------------------------------------------------------------
-- MaxValue6
PRINT N'Creating [dbo].[MaxValue6]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaxValue6]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[MaxValue6]
GO

CREATE FUNCTION [dbo].[MaxValue6] (
	 @val1 bigint
	,@val2 bigint
	,@val3 bigint
	,@val4 bigint
	,@val5 bigint
	,@val6 bigint
	)
RETURNS bigint
AS
BEGIN
		DECLARE @result bigint
		SELECT @result = 0
		IF (@val1 > @result)
			SELECT @result = @val1;
		IF (@val2 > @result)
			SELECT @result = @val2;
		IF (@val3 > @result)
			SELECT @result = @val3;
		IF (@val4 > @result)
			SELECT @result = @val4;
		IF (@val5 > @result)
			SELECT @result = @val5;
		IF (@val6 > @result)
			SELECT @result = @val6;
		RETURN @result;
END
GO

--------------------------------------------------------------------------------
-- Backup.Model.FnGetOibsChain
PRINT N'Creating [dbo].[Backup.Model.FnGetOibsChain]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Backup.Model.FnGetOibsChain]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[Backup.Model.FnGetOibsChain]
GO

	CREATE FUNCTION [dbo].[Backup.Model.FnGetOibsChain]
		( @id uniqueidentifier )
	RETURNS 
	@Result TABLE 
	(
		id UNIQUEIDENTIFIER, 
		rn int
	)
	AS
	BEGIN

		DECLARE @rn INT
		SET @rn = 1

		DECLARE @oib_chain TABLE (id UNIQUEIDENTIFIER, pid UNIQUEIDENTIFIER, rn int)
		INSERT @oib_chain(id, pid, rn)
		SELECT link_id, id, @rn from [dbo].[Backup.Model.OIBs] where id = @id

		WHILE @@RowCount <> 0
		BEGIN
			set @rn = @rn + 1
			INSERT @oib_chain(id, pid, rn)
			select 
				oib.link_id, oib.id, @rn 
			from 
				[dbo].[Backup.Model.OIBs] oib, 
				@oib_chain bc
			where
				oib.id = bc.id and 
				bc.id != bc.pid and
				bc.rn = @rn - 1
		END

		INSERT @Result(id, rn)
		select pid, rn from @oib_chain
			
		RETURN 
	END

GO

--------------------------------------------------------------------------------
-- GetFileNameFromAgentPath
PRINT N'Creating [dbo].[GetFileNameFromAgentPath]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileNameFromAgentPath]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[GetFileNameFromAgentPath]
GO

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

	CREATE PROCEDURE [dbo].[GetFileNameFromAgentPath]
		@filepath nvarchar(1000),
		@filename nvarchar(1000) OUTPUT
	AS
	BEGIN
		SET NOCOUNT ON;	
		BEGIN TRY
			declare @filepath_xml xml
			set @filepath_xml = @filepath
			select @filename = @filepath_xml.query('/FullName').value('.','nvarchar(1000)')
		END TRY
		BEGIN CATCH
			
		END CATCH;

		if @filename is null or @filename = ''
			set @filename = @filepath
	END
	
GO

--------------------------------------------------------------------------------
-- fn.IsFoldersContainHost
PRINT N'Creating [dbo].[fn.IsFoldersContainHost]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsFoldersContainHost]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.IsFoldersContainHost]
GO

	CREATE FUNCTION [dbo].[fn.IsFoldersContainHost]
		(@row_host_id uniqueidentifier)
	RETURNS BIT
	AS
	BEGIN

	DECLARE @hifsCount int

	SELECT 
		@hifsCount = COUNT(*) 
	FROM [dbo].[Folder_Host] 
	WHERE [host_id] = @row_host_id
	
	DECLARE @res BIT
	IF (@hifsCount > 0)
		SET @res = 1
	ELSE
		SET @res = 0
		
		RETURN @res
	END	

GO

--------------------------------------------------------------------------------
-- GetOibState
PRINT N'Creating [dbo].[GetOibState]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOibState]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[GetOibState]
GO

	CREATE FUNCTION [dbo].[GetOibState]
		(@oib_id uniqueidentifier)
	RETURNS bit
	AS
	BEGIN
		  DECLARE @result bit
	      
		  SELECT @result = COUNT([dbo].[Backup.Model.RestoreJobSessions].[id])
			FROM [dbo].[Backup.Model.RestoreJobSessions] LEFT JOIN [dbo].[Backup.Model.JobSessions]
			ON [dbo].[Backup.Model.RestoreJobSessions].[id] = [dbo].[Backup.Model.JobSessions].[id]
			WHERE [state] > 0 AND [dbo].[Backup.Model.RestoreJobSessions].[oib_id] = @oib_id

		  RETURN @result;
	END

GO

-------------------------------------------------------------------------------- 
-- fn.IsHostUpgradeRequired
PRINT N'Creating [dbo].[fn.IsHostUpgradeRequired]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsHostUpgradeRequired]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.IsHostUpgradeRequired]
GO

	CREATE FUNCTION [dbo].[fn.IsHostUpgradeRequired]
		(@hostId uniqueidentifier)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @outOfDateCompsCount int
		SELECT 
			@outOfDateCompsCount = COUNT(*)
		FROM
			[dbo].[HostComponents] c,
			[dbo].[Hosts] h
		WHERE
			c.[is_up_to_date] = 0 AND
			c.[physical_host_id] = h.[physical_host_id] AND
			h.[id] = @hostId

		DECLARE @res BIT
		IF (@outOfDateCompsCount > 0)
			SET @res = 1
		ELSE
			SET @res = 0
		RETURN @res
	END

GO

-------------------------------------------------------------------------------- 
-- fn.IsPhysicalHostUpgradeRequired
PRINT N'Creating [dbo].[fn.IsPhysicalHostUpgradeRequired]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsPhysicalHostUpgradeRequired]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.IsPhysicalHostUpgradeRequired]
GO

	CREATE FUNCTION [dbo].[fn.IsPhysicalHostUpgradeRequired]
		(@hostId uniqueidentifier)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @outOfDateCompsCount int
		SELECT 
			@outOfDateCompsCount = COUNT(*)
		FROM
			[dbo].[HostComponents] c
		WHERE
			c.[is_up_to_date] = 0 AND
			c.[physical_host_id] = @hostId

		DECLARE @res BIT
		IF (@outOfDateCompsCount > 0)
			SET @res = 1
		ELSE
			SET @res = 0
		RETURN @res
	END

GO

-------------------------------------------------------------------------------- 
-- fn.GetMaxRepositoryUsn
PRINT N'Creating [dbo].[fn.GetMaxRepositoryUsn]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.GetMaxRepositoryUsn]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.GetMaxRepositoryUsn]
GO

	CREATE FUNCTION [dbo].[fn.GetMaxRepositoryUsn]
		(@repositoryId uniqueidentifier)
	RETURNS BIGINT
	AS
	BEGIN
		DECLARE @repositoryUsn BIGINT
		DECLARE @hostId uniqueidentifier
		DECLARE @hostUsn BIGINT
		DECLARE @mountHostId uniqueidentifier
		DECLARE @mountHostUsn BIGINT
		DECLARE @result BIGINT
		
		SET @repositoryUsn = (SELECT TOP 1 [dbo].[BackupRepositories].[usn] FROM [dbo].[BackupRepositories] WHERE [dbo].[BackupRepositories].[id] = @repositoryId)
		
		SET @hostId = (SELECT TOP 1 [dbo].[BackupRepositories].[host_id] FROM [dbo].[BackupRepositories] WHERE [dbo].[BackupRepositories].[id] = @repositoryId)
		SET @hostUsn = [dbo].[fn.GetMaxHostUsn](@hostId)
		
		SET @mountHostId = (SELECT TOP 1 [dbo].[BackupRepositories].[mount_host_id] FROM [dbo].[BackupRepositories] WHERE [dbo].[BackupRepositories].[id] = @repositoryId)
		SET @mountHostUsn = [dbo].[fn.GetMaxHostUsn](@mountHostId)
		
		SET @result = [dbo].[MaxValue3](@hostUsn, @mountHostUsn, @repositoryUsn)
			
		RETURN  @result
	END

GO

--------------------------------------------------------------------------------
-- MinValue
PRINT N'Creating [dbo].[MinValue]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MinValue]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[MinValue]
GO

	CREATE FUNCTION [dbo].[MinValue]
		(@x1 int, @x2 int)
	RETURNS int
	AS
	BEGIN
		  IF (@x1 > @x2 )
				RETURN @x2;
		  IF (@x1 < @x2 )
				RETURN @x1;
		  RETURN @x1;
	END

GO

--------------------------------------------------------------------------------
-- Split
PRINT N'Creating [dbo].[Split]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Split]') AND type in (N'TF'))
	DROP FUNCTION [dbo].[Split]
GO

	CREATE FUNCTION [dbo].[Split]
		(@text nvarchar(max), @delimiter char(1) = ' ')
	RETURNS @Strings TABLE(position int IDENTITY PRIMARY KEY, value int)
	AS 
	BEGIN
		DECLARE @INDEX int    
		SET @INDEX = -1
		WHILE (LEN(@text) > 0)
		BEGIN        
			SET @INDEX = CHARINDEX(@delimiter , @text)        
			IF (@INDEX = 0) AND (LEN(@text) > 0)
			BEGIN   
				INSERT INTO @Strings VALUES (@text)   
				BREAK
			END        
			IF (@INDEX > 1)
			BEGIN   
				INSERT INTO @Strings VALUES (LEFT(@text, @INDEX - 1))   
				SET @text = RIGHT(@text, (LEN(@text) - @INDEX))
			END
			ELSE   
				SET @text = RIGHT(@text, (LEN(@text) - @INDEX))
		END
		RETURN
	END

GO

--------------------------------------------------------------------------------
-- CompareVersions
PRINT N'Creating [dbo].[CompareVersions]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompareVersions]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[CompareVersions]
GO

	CREATE FUNCTION [dbo].[CompareVersions]
		(@ver1 nvarchar(max), @ver2 nvarchar(max))
	RETURNS int
	AS
	BEGIN	
		DECLARE @ver1_table TABLE (part_number int, part_value int)
		INSERT INTO @ver1_table SELECT * FROM [dbo].[Split] (@ver1, '.')
		DECLARE @ver1_parts_count int
		SELECT @ver1_parts_count = COUNT(*) FROM @ver1_table
		
		DECLARE @ver2_table TABLE (part_number int, part_value int)
		INSERT INTO @ver2_table SELECT * FROM [dbo].[Split] (@ver2, '.')
		DECLARE @ver2_parts_count int
		SELECT @ver2_parts_count = COUNT(*) FROM @ver2_table
		
		DECLARE @min_parts_count int
		SELECT @min_parts_count = [dbo].[MaxValue](@ver1_parts_count, @ver2_parts_count)
		DECLARE @i int
		SET @i = 1
		WHILE (@i<=@min_parts_count)
		BEGIN
			DECLARE @ver1_part_value int
			DECLARE @ver2_part_value int

			SELECT @ver1_part_value = ISNULL([@ver1_table].part_value, 0) FROM @ver1_table WHERE [@ver1_table].part_number = @i
			SELECT @ver2_part_value = ISNULL([@ver2_table].part_value, 0) FROM @ver2_table WHERE [@ver2_table].part_number = @i

			IF (@ver1_part_value < @ver2_part_value)
				RETURN -1
			IF (@ver1_part_value > @ver2_part_value)
				RETURN 1
			SET @i = @i + 1
		END
		RETURN 0
	END

GO

--------------------------------------------------------------------------------
-- MaxBackupUsn
PRINT N'DROP [dbo].[MaxBackupUsn]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaxBackupUsn]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[MaxBackupUsn]
GO

--------------------------------------------------------------------------------
-- fn.IsImportedHost
PRINT N'Creating [dbo].[fn.IsImportedHost]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsImportedHost]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.IsImportedHost]
GO

	CREATE FUNCTION [dbo].[fn.IsImportedHost]
		(@host_id uniqueidentifier)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @local_host_id uniqueidentifier
		SET @local_host_id = '6745a759-2205-4cd2-b172-8ec8f7e60ef8'
		IF (@host_id <> @local_host_id)
		BEGIN
			DECLARE @count int
			SELECT 
				@count = COUNT(*) 
			FROM 
				[Hosts]
			WHERE 
				[id] = @host_id AND 
				[parent_id] = '00000000-0000-0000-0000-000000000000' AND 
				[dbo].[fn.IsFoldersContainHost]([id]) = 'False'
			DECLARE @res BIT
			IF (@count > 0)
				SET @res = 1
			ELSE
				SET @res = 0
		END
		ELSE
			SET @res = 0
		
		RETURN @res
	END	

GO

PRINT N'Creating [dbo].[fn.IsRootHost]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsRootHost]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.IsRootHost]
GO

	CREATE FUNCTION [dbo].[fn.IsRootHost]
		(@host_id uniqueidentifier)
	RETURNS BIT
	AS	
	BEGIN

		DECLARE @local_host_id uniqueidentifier
		SET @local_host_id = '6745a759-2205-4cd2-b172-8ec8f7e60ef8'

		DECLARE @unknown_vi_host_id uniqueidentifier
		SET @unknown_vi_host_id = 'D7B57228-436F-4241-A128-04FA5FCFA032'
		
		DECLARE @res BIT
		IF (@host_id = @local_host_id)
			SET @res = 1
		ELSE IF (@host_id = @unknown_vi_host_id)
			SET @res = 0
		ELSE
		BEGIN
			DECLARE @count int
			SELECT 
				@count = COUNT(*) 
			FROM 
				[Hosts] 
			WHERE 
				[id] = @host_id AND 
				([parent_id] = '00000000-0000-0000-0000-000000000000') AND ([dbo].[fn.IsImportedHost]([id]) = 'False')
			IF (@count > 0)
				SET @res = 1
			ELSE
				SET @res = 0
		END		
		
		RETURN @res
	END		
GO

select * from [dbo].[Hosts]
go


PRINT N'Creating [dbo].[fn.VisibleHosts]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.VisibleHosts]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.VisibleHosts]
GO
CREATE FUNCTION [dbo].[fn.VisibleHosts]()
	RETURNS 
		@hosts TABLE
		( 
		   [id] uniqueidentifier
		  ,[type] int
		  ,[name] nvarchar(255)
		  ,[ip] nvarchar(50)
		  ,[parent_id] uniqueidentifier
		  ,[info] nvarchar(255)
		  ,[description] nvarchar(1024)
		  ,[protocol] int
		  ,[reference] nvarchar(1024)
		  ,[api_version] int
		  ,[usn] bigint
		  ,[win_creds] xml
		  ,[bios_uuid] nvarchar(255)
		  ,[options] xml
		  ,[physical_host_id] uniqueidentifier
		  ,[credentials_id] uniqueidentifier
		  ,[runtime_info] xml
		  ,[is_unavailable] bit
		  ,[dns_name] nvarchar(255)
		  ,[host_instance_id] nvarchar(255)
		  ,[host_unique_id] nvarchar(255)
		  ,rec_level int
		)
	AS	
	BEGIN
		
		WITH VisibleHostsCTE AS 
		(
			SELECT
				[dbo].[Hosts].[id],
				[dbo].[Hosts].[type],
				[dbo].[Hosts].[name],
				[dbo].[Hosts].[ip],
				[dbo].[Hosts].[parent_id],
				[dbo].[Hosts].[info],
				[dbo].[Hosts].[description],
				[dbo].[Hosts].[protocol],
				[dbo].[Hosts].[reference],
				[dbo].[Hosts].[api_version],
				[dbo].[Hosts].[usn],
				[dbo].[Hosts].[win_creds],
				[dbo].[Hosts].[bios_uuid],
				[dbo].[Hosts].[options],
				[dbo].[Hosts].[physical_host_id],
				[dbo].[Hosts].[credentials_id],
				[dbo].[Hosts].[runtime_info],
				[dbo].[Hosts].[is_unavailable],
				[dbo].[Hosts].[dns_name],
				[dbo].[Hosts].[host_instance_id],
				[dbo].[Hosts].[host_unique_id],
				0 AS rec_level
			FROM
				[dbo].[Hosts]
			WHERE
				[dbo].[fn.IsRootHost]([id]) = 'True'
		    
			UNION ALL

			SELECT
				[hosts].[id],
				[hosts].[type],
				[hosts].[name],
				[hosts].[ip],
				[hosts].[parent_id],
				[hosts].[info],
				[hosts].[description],
				[hosts].[protocol],
				[hosts].[reference],
				[hosts].[api_version],
				[hosts].[usn],
				[hosts].[win_creds],
				[hosts].[bios_uuid],
				[hosts].[options],
				[hosts].[physical_host_id],
				[hosts].[credentials_id],
				[hosts].[runtime_info],
				[hosts].[is_unavailable],
				[hosts].[dns_name],
				[hosts].[host_instance_id],
				[hosts].[host_unique_id],
				rec_level + 1
			FROM
				[dbo].[Hosts] hosts
			INNER JOIN
				VisibleHostsCTE vhosts ON hosts.[parent_id] = vhosts.[id] 
		)
		INSERT @hosts
		SELECT * FROM VisibleHostsCTE
		
		RETURN
	END
GO

-------------------------------------------------------------------------------- 
-- fn.GetMaxHostUsn
PRINT N'Creating [dbo].[fn.GetMaxHostUsn]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.GetMaxHostUsn]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.GetMaxHostUsn]
GO

	CREATE FUNCTION [dbo].[fn.GetMaxHostUsn]
		(@hostId uniqueidentifier)
	RETURNS BIGINT
	AS
	BEGIN
		DECLARE @hostUsn BIGINT
		DECLARE @maxHostCompUsn BIGINT
		DECLARE @result BIGINT
		
		SELECT
			@maxHostCompUsn = MAX([dbo].[Hosts].[usn])
		FROM
			[dbo].[Hosts]
		WHERE
			[dbo].[Hosts].[id] = @hostId
			
		SELECT 
			@hostUsn = MAX([dbo].[HostComponents].[usn])
		FROM
			[dbo].[Hosts],
			[dbo].[HostComponents]
		WHERE
			[dbo].[Hosts].[id] = @hostId AND [dbo].[Hosts].[physical_host_id] = [dbo].[HostComponents].[physical_host_id]
			
		IF @maxHostCompUsn IS NULL OR @hostUsn > @maxHostCompUsn
			SET @result = @hostUsn
		ELSE
			SET @result = @maxHostCompUsn
			
		RETURN  @result
	END

GO

--------------------------------------------------------------------------------
-- fn.IsRootVisibleHost
PRINT N'Creating [dbo].[fn.IsRootVisibleHost]'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.IsRootVisibleHost]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn.IsRootVisibleHost]
GO

	CREATE FUNCTION [dbo].[fn.IsRootVisibleHost]
		(@host_id uniqueidentifier)
	RETURNS BIT
	AS	
	BEGIN

		DECLARE @local_host_id uniqueidentifier
		SET @local_host_id = '6745a759-2205-4cd2-b172-8ec8f7e60ef8'

		DECLARE @unknown_vi_host_id uniqueidentifier
		SET @unknown_vi_host_id = 'D7B57228-436F-4241-A128-04FA5FCFA032'
		
		DECLARE @vcType int
		SET @vcType = 1
		--    VC - VCD.   parent_id  .

		DECLARE @res BIT
		IF (@host_id = @local_host_id)
			SET @res = 1
		ELSE IF (@host_id = @unknown_vi_host_id)
			SET @res = 0
		ELSE
		BEGIN
			DECLARE @count int
			SELECT 
				@count = COUNT(*) 
			FROM 
				[Hosts] 
			WHERE 
				[id] = @host_id AND 
				([parent_id] = '00000000-0000-0000-0000-000000000000' OR [type] = @vcType) AND ([dbo].[fn.IsImportedHost]([id]) = 'False')			
			IF (@count > 0)
				SET @res = 1
			ELSE
				SET @res = 0
		END		
		
		RETURN @res
	END		

GO

---------------------------------------------------------------------------------
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.MakeObjectHash]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.MakeObjectHash]
GO
CREATE FUNCTION [dbo].[fn.Backup.MakeObjectHash]
(    
    @hierarchy_object_ref nvarchar(434),
	@hierarchy_root_instance_id nvarchar(255)
)
RETURNS varbinary(16) 
AS
BEGIN 

	declare @hash varbinary(16)
	set @hash = HASHBYTES('MD5', CONVERT(varchar(max), @hierarchy_object_ref) + CONVERT(varchar(max), @hierarchy_root_instance_id))	
    RETURN @hash
END
GO
-----------------------------------------------------
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.FromBase64]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.FromBase64]
GO
CREATE FUNCTION [dbo].[fn.Common.FromBase64]
(
	@str varchar(max)
)
RETURNS varbinary(max)
AS
BEGIN 

	declare @bin varbinary(max)
	SET @bin = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@str"))', 'VARBINARY(MAX)');		
    RETURN @bin
END
GO


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Common.ToBase64]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Common.ToBase64]
GO
CREATE FUNCTION [dbo].[fn.Common.ToBase64]
(
	@bin varbinary(max)
)
RETURNS varchar(max) 
AS
BEGIN 

	declare @str varchar(max)
	SET @str = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')
    RETURN @str
END
GO

--------------------------------------------------------------------------------
-- fn.GetSnapshotMaxUsn
PRINT N'Creating [dbo].[fn.GetSnapshotMaxUsn]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.GetSnapshotMaxUsn]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[fn.GetSnapshotMaxUsn]
GO

	CREATE FUNCTION [dbo].[fn.GetSnapshotMaxUsn]
		(@snapshot_id uniqueidentifier)
	RETURNS bigint
	AS
	BEGIN
		DECLARE @oibs_max_usn bigint
		DECLARE @snapshot_usn bigint
		DECLARE @result bigint
		
		SELECT 
			@oibs_max_usn = ISNULL(MAX(oibs.usn), 0)
		FROM 
			[dbo].[Backup.Model.SanSnapshotStorages] snapshotStorages, 
			[dbo].[Backup.Model.OIBs] oibs 
		where 
			snapshotStorages.[snapshot_id] = @snapshot_id AND 
			oibs.[storage_id] = snapshotStorages.[storage_id]
			
		SELECT
			@snapshot_usn = MAX(snapshots.Usn)
		FROM
			[dbo].[Backup.Model.SanSnapshots] snapshots
		WHERE
			snapshots.[id] = @snapshot_id
		
		SET @result = [dbo].[MaxValue](@oibs_max_usn, @snapshot_usn)
		
		RETURN @result;
	END
GO

--------------------------------------------------------------------------------
-- fn.GetSnapshotVmsCount
PRINT N'Creating [dbo].[fn.GetSnapshotVmsCount]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.GetSnapshotVmsCount]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[fn.GetSnapshotVmsCount]
GO

	CREATE FUNCTION [dbo].[fn.GetSnapshotVmsCount]
		(@snapshot_id uniqueidentifier)
	RETURNS bigint
	AS
	BEGIN
		DECLARE @result bigint
		
		SELECT 
			@result = COUNT(*) 		
		FROM 
			[dbo].[Backup.Model.SanSnapshotStorages] snapshotStorages, 
			[dbo].[Backup.Model.OIBs] oibs 
		where 
			snapshotStorages.[snapshot_id] = @snapshot_id AND 
			oibs.[storage_id] = snapshotStorages.[storage_id]
			
		RETURN @result;
	END
GO

--------------------------------------------------------------------------------
-- ParseIdList
PRINT N'Creating [dbo].[ParseIdList]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParseIdList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[ParseIdList]
GO

CREATE FUNCTION [dbo].[ParseIdList](@xmlTable xml)
	RETURNS @xml TABLE ([id] uniqueidentifier)
	AS	
	BEGIN
		WITH pXml AS 
		(
		SELECT dataset.rowset.value('id[1]', 'uniqueidentifier') as id
			FROM @xmlTable.nodes('/dataset/row') as dataset(rowset)
		)
		INSERT @xml SELECT * FROM pXml
			RETURN
	END
GO


--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaxValue2]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[MaxValue2]
GO
CREATE FUNCTION [dbo].[MaxValue2]
		(@x1 bigint, @x2 bigint)
	RETURNS bigint
	AS
	BEGIN
		DECLARE @result bigint
		SELECT @result = 0
		IF (@x1 > @result)
			SELECT @result = @x1;
		IF (@x2 > @result)
			SELECT @result = @x2;
		RETURN @result;
	END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IsValidChainOibs]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[IsValidChainOibs]
go
create function [dbo].[IsValidChainOibs]
		(@oib_id uniqueidentifier)
	returns bit
	as
	begin
	      declare @result bit;
		  with cteOibs (id, link_id, cnt) 
		  as
		  (
			select id, link_id, 0 from [Backup.Model.OIBs] where id = @oib_id
			union all
			select o.id, o.link_id, c.cnt + 1 from [Backup.Model.OIBs] o
			join cteOibs c on o.id = c.link_id 
		  )
		  select @result = case when (select count(*) from cteOibs c join [Backup.Model.OIBs] o on c.id = o.id where o.[type] = 0) > 0 then 1 else 0 end
		  OPTION (MAXRECURSION 0)
		  return @result;
	end
go


