SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--------------------------------------------------------------------------------
-- Tape.report_directory_changes
PRINT N'Creating [dbo].[Tape.report_directory_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_directory_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_directory_changes]
GO

	CREATE VIEW [dbo].[Tape.report_directory_changes]
AS

WITH temp_table AS (
	SELECT  
		dir.id,
		dir.name,
		dir.[host_id],
		dir.parent_id,
		ISNULL(dver.creation_time, GETDATE()) as creation_time,
		ISNULL(dver.last_write_time, GETDATE()) as last_write_time,
		(SELECT COUNT(directory_id) FROM [Tape.directory_versions] WHERE directory_id = dir.id) as count_points,
		dver.backup_set_id,
		bset.media_family_id,
		ROW_NUMBER() OVER (PARTITION BY dir.id ORDER BY dir.id DESC) as RN,
		ISNULL(bset.usn, 0) as usn
	FROM [Tape.directories] dir
	LEFT JOIN [Tape.directory_versions] dver ON dver.directory_id = dir.id
	LEFT JOIN [Tape.backup_sets] bset ON bset.id = dver.backup_set_id
	GROUP BY dir.id,dir.name,dir.[host_id],dir.parent_id, creation_time, last_write_time,dver.backup_set_id,bset.media_family_id, usn
	)
SELECT 
	dirs.id,
	ISNULL(dirs.name, '/') name,
	dirs.media_family_id,
	dirs.[host_id],
	dirs.creation_time,
	dirs.last_write_time,
	dirs.backup_set_id,
	dirs.parent_id,
	dirs.count_points,
	dirs.usn
FROM temp_table as dirs
WHERE dirs.RN = 1
	
GO

--------------------------------------------------------------------------------
-- [Tape.report_file_changes]
PRINT N'Creating [dbo].[Tape.report_file_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_file_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_file_changes]
GO

	CREATE VIEW [dbo].[Tape.report_file_changes]
AS

WITH temp_table AS (SELECT  fil.id,
		fil.directory_id,
		fil.name,
		fver.creation_time,
		fver.last_write_time,
		fver.backup_set_id,
		fver.size,
		(SELECT COUNT([file_id]) FROM [Tape.file_versions] WHERE [file_id] = fil.id) as count_points,
		bset.media_family_id,
		ROW_NUMBER() OVER (PARTITION BY fil.id ORDER BY fil.id DESC) as RN,
		bset.usn
 FROM [Tape.file_versions] fver
 INNER JOIN [Tape.backup_sets] bset ON bset.id = fver.backup_set_id
 INNER JOIN [Tape.files] fil ON fil.id = fver.[file_id]
 GROUP BY fil.id,fil.directory_id,fil.name, creation_time, last_write_time, fver.backup_set_id, fver.size, bset.media_family_id, bset.usn)
SELECT files.id,
 files.name,
 files.media_family_id,
 files.creation_time,
 files.last_write_time,
 files.backup_set_id,
 files.directory_id,
 files.size,
 files.count_points,
 files.usn
FROM temp_table as files
WHERE files.RN =1

	
GO

--------------------------------------------------------------------------------
-- [Tape.report_job_set_changes]
PRINT N'Creating [dbo].[Tape.report_job_set_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_job_set_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_job_set_changes]
GO

	CREATE VIEW [dbo].[Tape.report_job_set_changes]
AS

SELECT  bsets.id,
		bsets.name,
		bsets.write_time,
		bsets.media_family_id,
		bsets.usn
 FROM [Tape.backup_sets] bsets

	
GO

--------------------------------------------------------------------------------
-- [Tape.report_media_set_changes]
PRINT N'Creating [dbo].[Tape.report_media_set_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_media_set_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_media_set_changes]
GO

	CREATE VIEW [dbo].[Tape.report_media_set_changes]
AS

WITH temp_table AS (SELECT bsets.media_family_id,
	    bsets.id,
	    bsets.name,
		MAX(bsets.write_time) as write_time,
		tmed.media_pool_id,
		ROW_NUMBER() OVER (PARTITION BY bsets.media_family_id ORDER BY bsets.write_time DESC) as RN,
		MAX(bsets.usn) as usn
 FROM [Tape.backup_sets] bsets 
 INNER JOIN [Tape.tape_mediums] tmed ON tmed.media_family_id = bsets.media_family_id 
 WHERE tmed.cleaner = 0
 GROUP BY bsets.media_family_id, write_time, bsets.id, tmed.media_pool_id, bsets.name)
SELECT media_sets.id,
 media_sets.name,
 media_sets.media_family_id,
 media_sets.write_time,
 media_sets.media_pool_id,
 media_sets.usn
FROM temp_table as media_sets
WHERE media_sets.RN =1

	
GO

--------------------------------------------------------------------------------
-- [Tape.report_server_changes]
PRINT N'Creating [dbo].[Tape.report_server_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_server_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_server_changes]
GO

	CREATE VIEW [dbo].[Tape.report_server_changes]
AS

SELECT DISTINCT(host.id), host.name FROM [Tape.hosts] host 
	INNER JOIN [Tape.directories] dir ON host.id = dir.[host_id]

	
GO

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

CREATE PROCEDURE [dbo].[Tape.report_directory_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;

	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.directories'
END
GO

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

CREATE PROCEDURE [dbo].[Tape.report_media_set_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.backup_sets'
END
GO

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

CREATE PROCEDURE [dbo].[Tape.report_file_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;

	SELECT uid, usn	FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.files'
END
GO

--------------------------------------------------------------------------------
-- Tape.report_filesysitems
PRINT N'Creating [dbo].[Tape.report_filesysitems]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_filesysitems]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_filesysitems]
GO

CREATE VIEW [dbo].[Tape.report_filesysitems]
AS
SELECT DISTINCT dir.id,
		ISNULL(dir.name, '/') name,
		dir.[host_id],
		dir.parent_id,
		dver.last_write_time,
		dver.creation_time,
		0 as size,
		(SELECT COUNT(directory_id) FROM [Tape.directory_versions] WHERE directory_id = dir.id) as count_points,
		0 as [type]
FROM [Tape.directories] dir
LEFT JOIN [Tape.directory_versions] dver ON dir.id = dver.directory_id AND dver.last_write_time = (SELECT MAX(last_write_time) FROM [Tape.directory_versions] WHERE directory_id = dir.id)
UNION ALL
SELECT DISTINCT fil.id,
		fil.name,
		'00000000-0000-0000-0000-000000000000' as [host_id],
		fil.directory_id as parent_id,
		fver.last_write_time,
		fver.creation_time,
		fver.size,
		(SELECT COUNT([file_id]) FROM [Tape.file_versions] WHERE [file_id] = fil.id) as count_points,
		1 as [type]
 FROM [Tape.files] fil
 INNER JOIN [Tape.file_versions] fver ON fil.id = fver.[file_id] AND fver.last_write_time = (SELECT MAX(last_write_time) FROM [Tape.file_versions] WHERE [file_id] = fil.id)
GO

--------------------------------------------------------------------------------
-- Tape.insert_as_first
PRINT N'Creating [dbo].[Tape.insert_as_first]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.insert_as_first]') AND type in (N'FN'))
	DROP FUNCTION [dbo].[Tape.insert_as_first]
GO
CREATE FUNCTION [dbo].[Tape.insert_as_first] (@x1 XML, @x2 XML)
RETURNS XML
AS
BEGIN
 
      -- insert into null value is impossible
      IF (@x1 is null)
            RETURN null
 
    -- if the value to insert is null or if @x1 contains no element we do nothing
      IF ((@x1.value('count(/*)','int') = 0) OR (@x2 is null))
            RETURN @x1
 
      -- if there is no element to insert we do nothing
      IF (@x2.value('count(/*)','int') = 0)
            RETURN @x1
 
      -- if any one of the two instances is not a valid document (more than one root element)
      -- we do nothing
      IF ((@x1.value('count(/*)','int') > 1) OR (@x2.value('count(/*)','int') > 1))
            RETURN @x1 
 
 
      DECLARE @x XML
      SET @x = CONVERT(XML, (CONVERT(nvarchar(MAX), @x1) + CONVERT(nvarchar(MAX), @x2)))
      SET @x.modify('insert /*[2] as first into /*[1]')
      SET @x.modify('delete /*[2]')
 
      RETURN @x
 
END
GO

--------------------------------------------------------------------------------
-- Tape.get_device_by_serial_number
PRINT N'Creating [dbo].[Tape.get_device_by_serial_number]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_device_by_serial_number]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_device_by_serial_number]
GO
CREATE PROCEDURE [dbo].[Tape.get_device_by_serial_number]
	@serial_number nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.devices] WHERE serial_number = @serial_number
END
GO


--------------------------------------------------------------------------------
-- Tape.get_device
PRINT N'Creating [dbo].[Tape.get_device]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_device]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_device]
GO
CREATE PROCEDURE [dbo].[Tape.get_device]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.devices] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_directory_full_name
PRINT N'Creating [dbo].[Tape.get_directory_full_name]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_directory_full_name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_directory_full_name]
GO
CREATE PROCEDURE [dbo].[Tape.get_directory_full_name]
	@id bigint
AS
BEGIN
	SET NOCOUNT ON;

	WITH directory_full_name(i, id, name)
	AS 
	(
		SELECT 0, parent_id, CAST(name AS nvarchar(255))
		FROM [Tape.directories]
		WHERE id = @id
		UNION ALL
		SELECT directory_full_name.i + 1, [Tape.directories].parent_id, [Tape.directories].name
		FROM [Tape.directories]
		INNER JOIN directory_full_name ON directory_full_name.id = [Tape.directories].id
	)
	
	SELECT name, 1 l, i FROM directory_full_name
	UNION SELECT h.name, 0, 0 FROM [Tape.hosts] h INNER JOIN [Tape.directories] d ON h.id = d.host_id WHERE d.id = @id
	ORDER BY l ASC, i DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_directory_by_host_id_and_name
PRINT N'Creating [dbo].[Tape.get_directory_by_host_id_and_name]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_directory_by_host_id_and_name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_directory_by_host_id_and_name]
GO
CREATE PROCEDURE [dbo].[Tape.get_directory_by_host_id_and_name]
	@host_id uniqueidentifier
	,@name nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	IF @name IS NULL
		SELECT * FROM [Tape.directories] WHERE [host_id] = @host_id AND parent_id IS NULL AND name IS NULL
	ELSE
		SELECT * FROM [Tape.directories] WHERE [host_id] = @host_id AND parent_id IS NULL AND name = @name
END
GO

--------------------------------------------------------------------------------
-- Tape.get_directory_by_parent_id_and_name
PRINT N'Creating [dbo].[Tape.get_directory_by_parent_id_and_name]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_directory_by_parent_id_and_name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_directory_by_parent_id_and_name]
GO
CREATE PROCEDURE [dbo].[Tape.get_directory_by_parent_id_and_name]
	@parent_id bigint
	,@name nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
--	IF @parent_id IS NULL
--		SELECT * FROM [Tape.directories] WHERE parent_id IS NULL AND name = @name
--	ELSE
	
	SELECT * FROM [Tape.directories] WHERE parent_id = @parent_id AND name = @name
END
GO


--------------------------------------------------------------------------------
-- Tape.get_directory
PRINT N'Creating [dbo].[Tape.get_directory]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_directory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_directory]
GO
CREATE PROCEDURE [dbo].[Tape.get_directory]
	@id bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.directories] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_directories
PRINT N'Creating [dbo].[Tape.get_directories]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_directories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_directories]
GO
CREATE PROCEDURE [dbo].[Tape.get_directories]
	@ids xml
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @hdocument int
	EXEC sp_xml_preparedocument @hdocument OUTPUT, @ids

	SELECT * FROM [Tape.directories] WHERE id IN (SELECT * FROM OPENXML(@hdocument, N'/ROOT/id', 1) WITH (id bigint))
	
	EXEC sp_xml_removedocument @hdocument
END
GO


--------------------------------------------------------------------------------
-- Tape.get_host
PRINT N'Creating [dbo].[Tape.get_host]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_host]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_host]
GO
CREATE PROCEDURE [dbo].[Tape.get_host]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.hosts] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_highest_committed_usn
PRINT N'Creating [dbo].[Tape.get_highest_committed_usn]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_highest_committed_usn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_highest_committed_usn]
GO
CREATE PROCEDURE [dbo].[Tape.get_highest_committed_usn]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT usn FROM [ReplicationInfo]
END
GO


--------------------------------------------------------------------------------
-- Tape.report_backup_sets_deletes
PRINT N'Creating [dbo].[Tape.report_backup_sets_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_backup_sets_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_backup_sets_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_backup_sets_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.backup_sets'
END
GO


--------------------------------------------------------------------------------
-- Tape.next_highest_committed_usn
PRINT N'Creating [dbo].[Tape.next_highest_committed_usn]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.next_highest_committed_usn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.next_highest_committed_usn]
GO
CREATE PROCEDURE [dbo].[Tape.next_highest_committed_usn]
	@highest_committed_usn bigint OUT
AS
BEGIN
	BEGIN TRANSACTION
	
	UPDATE [ReplicationInfo] SET usn = usn + 1
	SELECT @highest_committed_usn = usn FROM [ReplicationInfo]
	
	COMMIT TRANSACTION
END
GO


--------------------------------------------------------------------------------
-- Tape.report_backups_deletes
PRINT N'Creating [dbo].[Tape.report_backups_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_backups_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_backups_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_backups_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.jobs'
END
GO


--------------------------------------------------------------------------------
-- Tape.report_media_deletes
PRINT N'Creating [dbo].[Tape.report_media_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_media_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_media_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_media_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.tape_mediums'
END
GO


--------------------------------------------------------------------------------
-- Tape.report_media_pool_deletes
PRINT N'Creating [dbo].[Tape.report_media_pool_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_media_pool_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_media_pool_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_media_pool_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.tape_mediums'
END
GO


--------------------------------------------------------------------------------
-- Tape.report_libraries_deletes
PRINT N'Creating [dbo].[Tape.report_libraries_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_libraries_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_libraries_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_libraries_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.libraries'
END
GO


--------------------------------------------------------------------------------
-- Tape.report_jobs_deletes
PRINT N'Creating [dbo].[Tape.report_jobs_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_jobs_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_jobs_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_jobs_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.jobs'
END
GO


--------------------------------------------------------------------------------
-- Tape.report_media_pools_deletes
PRINT N'Creating [dbo].[Tape.report_media_pools_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_media_pools_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_media_pools_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_media_pools_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.media_pools'
END
GO


--------------------------------------------------------------------------------
-- Tape.report_sessions_deletes
PRINT N'Creating [dbo].[Tape.report_sessions_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_sessions_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_sessions_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_sessions_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.sessions'
END
GO


--------------------------------------------------------------------------------
-- Tape.report_tape_drives_deletes
PRINT N'Creating [dbo].[Tape.report_tape_drives_deletes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_tape_drives_deletes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.report_tape_drives_deletes]
GO
CREATE PROCEDURE [dbo].[Tape.report_tape_drives_deletes]
	@row_usn bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT uid, usn FROM [TombStones] WHERE usn > @row_usn AND table_name = 'Tape.tape_drives'
END
GO


--------------------------------------------------------------------------------
-- Tape.add_host
PRINT N'Creating [dbo].[Tape.add_host]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_host]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_host]
GO
CREATE PROCEDURE [dbo].[Tape.add_host]
	@name nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @id AS UNIQUEIDENTIFIER
	SET @id = NEWID()
	
	INSERT INTO [Tape.hosts] (
		id
		,name
		)
	VALUES (
		@id
		,@name
		)
		
	SELECT @id
END
GO


--------------------------------------------------------------------------------
-- Tape.add_directory
PRINT N'Creating [dbo].[Tape.add_directory]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_directory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_directory]
GO
CREATE PROCEDURE [dbo].[Tape.add_directory]
	@host_id uniqueidentifier
	,@parent_id bigint
	,@name nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.directories] (
		[host_id]
		,parent_id
		,name
	)
	VALUES (
		@host_id
		,@parent_id
		,@name
	)
		
	SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS [SCOPE_IDENTITY]
END
GO


--------------------------------------------------------------------------------
-- Tape.add_device
PRINT N'Creating [dbo].[Tape.add_device]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Tape.add_device]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Tape.add_device]
GO
CREATE PROCEDURE [Tape.add_device]
	@id uniqueidentifier
	,@scsi_port int
	,@scsi_bus int
	,@scsi_target_id int
	,@scsi_logical_unit_id int
	,@type tinyint
	,@name nvarchar(MAX)
	,@serial_number nvarchar(32)
	,@state tinyint
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [Tape.devices] (
		id
		,scsi_port
		,scsi_bus
		,scsi_target_id
		,scsi_logical_unit_id
		,[type]
		,name
		,serial_number
		,[state]
		)
	VALUES (
		@id
		,@scsi_port
		,@scsi_bus
		,@scsi_target_id
		,@scsi_logical_unit_id
		,@type
		,@name
		,@serial_number
		,@state
		)
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_directories_by_parent_id
PRINT N'Creating [dbo].[Tape.get_all_directories_by_parent_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_directories_by_parent_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_directories_by_parent_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_directories_by_parent_id]
	@parent_id bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.directories] WHERE parent_id = @parent_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_devices
PRINT N'Creating [dbo].[Tape.get_all_devices]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_devices]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_devices]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_devices]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.devices]
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_device
PRINT N'Creating [dbo].[Tape.delete_device]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_device]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_device]
GO
CREATE PROCEDURE [dbo].[Tape.delete_device]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.devices] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.unlock_device
PRINT N'Creating [dbo].[Tape.unlock_device]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.unlock_device]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.unlock_device]
GO
CREATE PROCEDURE [dbo].[Tape.unlock_device]
	@device_id uniqueidentifier 
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.devices] SET lock_id = NULL WHERE id = @device_id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_all_devices_state
PRINT N'Creating [dbo].[Tape.update_all_devices_state]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_all_devices_state]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_all_devices_state]
GO
CREATE PROCEDURE [dbo].[Tape.update_all_devices_state]
	@state tinyint
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.devices] SET [state] = @state
END
GO


--------------------------------------------------------------------------------
-- Tape.update_device
PRINT N'Creating [dbo].[Tape.update_device]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_device]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_device]
GO
CREATE PROCEDURE [dbo].[Tape.update_device]
	@id uniqueidentifier
	,@scsi_port int
	,@scsi_bus int
	,@scsi_target_id int
	,@scsi_logical_unit_id int
	,@type tinyint
	,@name nvarchar(MAX)
	,@serial_number nvarchar(32)
	,@state tinyint
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.devices] SET
		scsi_port = @scsi_port
		,scsi_bus = @scsi_bus
		,scsi_target_id = @scsi_target_id
		,scsi_logical_unit_id = @scsi_logical_unit_id
		,[type] = @type
		,name = @name
		,serial_number = @serial_number
		,[state] = @state
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_changer
PRINT N'Creating [dbo].[Tape.update_changer]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_changer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_changer]
GO
CREATE PROCEDURE [dbo].[Tape.update_changer]
	@device_id uniqueidentifier
	,@name nvarchar(MAX)
	,@vendor_id nvarchar(8)
	,@product_id nvarchar(16)
	,@revision nvarchar(4)
	,@first_drive_number int
	,@drive_count int
	,@first_slot_number int
	,@slot_count int
	,@first_ieport_number int
	,@ieport_count int
	,@first_cleaner_slot_number int
	,@cleaner_slot_count int
	,@magazine_size int
	,@features0 bigint
	,@features1 bigint
	,@move_from_slot tinyint
	,@move_from_ieport tinyint
	,@move_from_drive tinyint
	,@exchange_from_slot tinyint
	,@exchange_from_ieport tinyint
	,@exchange_from_drive tinyint
	,@lock_unlock_capabilities tinyint
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.changers] SET
		vendor_id = @vendor_id
		,name = @name
		,product_id = @product_id
		,revision = @revision
		,first_drive_number = @first_drive_number
		,drive_count = @drive_count
		,first_slot_number = @first_slot_number
		,slot_count = @slot_count
		,first_ieport_number = @first_ieport_number
		,ieport_count = @ieport_count
		,first_cleaner_slot_number = @first_cleaner_slot_number
		,cleaner_slot_count = @cleaner_slot_count
		,magazine_size = @magazine_size
		,features0 = @features0
		,features1 = @features1
		,move_from_slot = @move_from_slot
		,move_from_ieport = @move_from_ieport
		,move_from_drive = @move_from_drive
		,exchange_from_slot = @exchange_from_slot
		,exchange_from_ieport = @exchange_from_ieport
		,exchange_from_drive = @exchange_from_drive
		,lock_unlock_capabilities = @lock_unlock_capabilities
	WHERE
		device_id = @device_id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_changer
PRINT N'Creating [dbo].[Tape.delete_changer]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_changer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_changer]
GO
CREATE PROCEDURE [dbo].[Tape.delete_changer]
	@device_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.changers] WHERE device_id = @device_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_files_by_directory_id
PRINT N'Creating [dbo].[Tape.get_all_files_by_directory_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_files_by_directory_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_files_by_directory_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_files_by_directory_id]
	@directory_id bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.files] WHERE directory_id = @directory_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_changers
PRINT N'Creating [dbo].[Tape.get_all_changers]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_changers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_changers]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_changers]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.changers]
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_file
PRINT N'Creating [dbo].[Tape.delete_file]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_file]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_file]
GO
CREATE PROCEDURE [dbo].[Tape.delete_file]
	@id bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.files] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.add_changer
PRINT N'Creating [dbo].[Tape.add_changer]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Tape.add_changer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Tape.add_changer]
GO
CREATE PROCEDURE [dbo].[Tape.add_changer]
	@device_id uniqueidentifier
	,@name nvarchar(MAX)
	,@vendor_id nvarchar(8)
	,@product_id nvarchar(16)
	,@revision nvarchar(4)
	,@first_drive_number int
	,@drive_count int
	,@first_slot_number int
	,@slot_count int
	,@first_ieport_number int
	,@ieport_count int
	,@first_cleaner_slot_number int
	,@cleaner_slot_count int
	,@magazine_size int
	,@features0 bigint
	,@features1 bigint
	,@move_from_slot tinyint
	,@move_from_ieport tinyint
	,@move_from_drive tinyint
	,@exchange_from_slot tinyint
	,@exchange_from_ieport tinyint
	,@exchange_from_drive tinyint
	,@lock_unlock_capabilities tinyint
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.changers] (
		device_id
		,name
		,vendor_id
		,product_id
		,revision
		,first_drive_number
		,drive_count
		,first_slot_number
		,slot_count
		,first_ieport_number
		,ieport_count
		,first_cleaner_slot_number
		,cleaner_slot_count
		,magazine_size
		,features0
		,features1
		,move_from_slot
		,move_from_ieport
		,move_from_drive
		,exchange_from_slot
		,exchange_from_ieport
		,exchange_from_drive
		,lock_unlock_capabilities
		)
	VALUES (
		@device_id
		,@name
		,@vendor_id
		,@product_id
		,@revision
		,@first_drive_number
		,@drive_count
		,@first_slot_number
		,@slot_count
		,@first_ieport_number
		,@ieport_count
		,@first_cleaner_slot_number
		,@cleaner_slot_count
		,@magazine_size
		,@features0
		,@features1
		,@move_from_slot
		,@move_from_ieport
		,@move_from_drive
		,@exchange_from_slot
		,@exchange_from_ieport
		,@exchange_from_drive
		,@lock_unlock_capabilities
		)
END
GO


--------------------------------------------------------------------------------
-- Tape.add_file
PRINT N'Creating [dbo].[Tape.add_file]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_file]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_file]
GO
CREATE PROCEDURE [dbo].[Tape.add_file]
	@directory_id BIGINT
	,@name NVARCHAR(255)
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.files] (directory_id ,name) VALUES (@directory_id, @name)
		
	SELECT * FROM [Tape.files] WHERE id = CAST(SCOPE_IDENTITY() AS BIGINT)
END
GO


--------------------------------------------------------------------------------
-- Tape.search_files
PRINT N'Creating [dbo].[Tape.search_files]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.search_files]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.search_files]
GO
CREATE PROCEDURE [dbo].[Tape.search_files]
	@pattern nvarchar(800)
	,@parent_ids xml
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @hdocument int
	EXEC sp_xml_preparedocument @hdocument OUTPUT, @parent_ids

	DECLARE @ids_count int	
	SELECT @ids_count = COUNT(*) FROM OPENXML(@hdocument, N'/ROOT/id', 1) WITH (id bigint)
	
	IF @ids_count > 0
		SELECT id FROM [Tape.files] WHERE name LIKE @pattern ESCAPE '\' AND directory_id IN (SELECT id FROM OPENXML(@hdocument, N'/ROOT/id', 1) WITH (id bigint))
	ELSE
		SELECT id FROM [Tape.files] WHERE name LIKE @pattern ESCAPE '\'

	EXEC sp_xml_removedocument @hdocument
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_by_directory_and_name
PRINT N'Creating [dbo].[Tape.get_file_by_directory_and_name]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_by_directory_and_name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_by_directory_and_name]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_by_directory_and_name]
	@directory_id bigint
	,@name nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		* 
	FROM 
		[Tape.files]
	WHERE
		directory_id = @directory_id AND
		name = @name
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file
PRINT N'Creating [dbo].[Tape.get_file]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file]
GO
CREATE PROCEDURE [dbo].[Tape.get_file]
	@id bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.files] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_files
PRINT N'Creating [dbo].[Tape.get_files]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_files]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_files]
GO
CREATE PROCEDURE [dbo].[Tape.get_files]
	@ids xml
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @hdocument int
	EXEC sp_xml_preparedocument @hdocument OUTPUT, @ids
	
	SELECT * FROM [Tape.files] WHERE id IN (SELECT id FROM OPENXML(@hdocument, N'/ROOT/id', 1) WITH (id bigint))

	EXEC sp_xml_removedocument @hdocument
END
GO


--------------------------------------------------------------------------------
-- Tape.get_changer
PRINT N'Creating [dbo].[Tape.get_changer]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_changer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_changer]
GO
CREATE PROCEDURE [dbo].[Tape.get_changer]
	@device_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.changers] WHERE device_id = @device_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_libraries
PRINT N'Creating [dbo].[Tape.get_all_libraries]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_libraries]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_libraries]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_libraries]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.libraries]
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_jobs
PRINT N'Creating [dbo].[Tape.get_all_jobs]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_jobs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_jobs]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_jobs]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.jobs] WHERE [Tape.jobs].[Type] in (24, 25, 26, 27, 28, 29, 32, 33, 34, 35, 36) 
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_tape_mediums
PRINT N'Creating [dbo].[Tape.get_all_tape_mediums]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_tape_mediums]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_tape_mediums]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_tape_mediums]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.tape_mediums]
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_tape_drives
PRINT N'Creating [dbo].[Tape.get_all_tape_drives]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_tape_drives]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_tape_drives]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_tape_drives]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.tape_drives]
END
GO


--------------------------------------------------------------------------------
-- Tape.get_library
PRINT N'Creating [dbo].[Tape.get_library]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_library]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_library]
GO
CREATE PROCEDURE [dbo].[Tape.get_library]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.libraries] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_libraries_by_type
PRINT N'Creating [dbo].[Tape.get_libraries_by_type]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_libraries_by_type]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_libraries_by_type]
GO
CREATE PROCEDURE [dbo].[Tape.get_libraries_by_type]
	@type tinyint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.libraries] WHERE [type] = @type
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_mediums_by_media_pool_id
PRINT N'Creating [dbo].[Tape.get_tape_mediums_by_media_pool_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_mediums_by_media_pool_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_mediums_by_media_pool_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_mediums_by_media_pool_id]
	@media_pool_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		* 
	FROM 
		[Tape.tape_mediums] 
	WHERE 
		media_pool_id = @media_pool_id 
	ORDER BY 
		media_family_id, media_sequence_number, barcode
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_medium_to_overwrite
PRINT N'Creating [dbo].[Tape.get_tape_medium_to_overwrite]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_medium_to_overwrite]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_medium_to_overwrite]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_medium_to_overwrite]
	@media_pool_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1 
		*
	FROM 
		[Tape.tape_mediums]
	WHERE 
		media_pool_id = @media_pool_id AND
		location_type IN (0, 1) AND
		is_write_protected = 0 AND
		is_full <> 0
	ORDER BY 
		last_write_time ASC, 
		barcode ASC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_medium_to_append
PRINT N'Creating [dbo].[Tape.get_tape_medium_to_append]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_medium_to_append]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_medium_to_append]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_medium_to_append]
	@media_pool_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1 
		*
	FROM 
		[Tape.tape_mediums]
	WHERE 
		media_pool_id = @media_pool_id AND
		location_type IN (0, 1) AND
		is_write_protected = 0 AND
		is_full = 0
	ORDER BY 
		last_write_time DESC,
		barcode ASC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_medium_by_location
PRINT N'Creating [dbo].[Tape.get_tape_medium_by_location]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_medium_by_location]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_medium_by_location]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_medium_by_location]
	@location_type tinyint
	,@location_library_id uniqueidentifier
	,@location_address int
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT
		*
	FROM
		[Tape.tape_mediums]
	WHERE
		location_type = @location_type AND
		location_library_id = @location_library_id AND
		location_address = @location_address
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_medium_by_barcode
PRINT N'Creating [dbo].[Tape.get_tape_medium_by_barcode]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_medium_by_barcode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_medium_by_barcode]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_medium_by_barcode]
	@barcode nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.tape_mediums] WHERE barcode = @barcode
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_medium_by_family_id_and_sequence_number
PRINT N'Creating [dbo].[Tape.get_tape_medium_by_family_id_and_sequence_number]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_medium_by_family_id_and_sequence_number]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_medium_by_family_id_and_sequence_number]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_medium_by_family_id_and_sequence_number]
	@media_family_id int
	,@media_sequence_number int
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.tape_mediums] WHERE media_family_id = @media_family_id AND media_sequence_number = @media_sequence_number
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_medium
PRINT N'Creating [dbo].[Tape.get_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_medium]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_medium]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.tape_mediums] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_drive
PRINT N'Creating [dbo].[Tape.get_tape_drive]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_drive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_drive]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_drive]
	@device_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.tape_drives] WHERE device_id = @device_id
END
GO


--------------------------------------------------------------------------------
-- Tape.lock_tape_medium
PRINT N'Creating [dbo].[Tape.lock_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.lock_tape_medium]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.lock_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.lock_tape_medium]
	@tape_medium_id uniqueidentifier, 
	@lock_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET lock_id = @lock_id WHERE id = @tape_medium_id AND lock_id IS NULL
	
	SELECT @@ROWCOUNT
END
GO


--------------------------------------------------------------------------------
-- Tape.add_library
PRINT N'Creating [dbo].[Tape.add_library]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_library]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_library]
GO
CREATE PROCEDURE [dbo].[Tape.add_library]
	@id uniqueidentifier
	,@type tinyint
	,@enabled bit
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.libraries] (
		id
		,[type]
		,[enabled]
		)
	VALUES (
		@id
		,@type
		,@enabled
		)
		
END
GO


--------------------------------------------------------------------------------
-- Tape.add_job
PRINT N'Creating [dbo].[Tape.add_job]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_job]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_job]
GO
CREATE PROCEDURE [dbo].[Tape.add_job]
	@name nvarchar(MAX)
	,@description nvarchar(MAX)
	,@type tinyint
	,@full_mediapool_id uniqueidentifier
	,@incremental_mediapool_id uniqueidentifier
	,@options xml
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @id AS UNIQUEIDENTIFIER
	SET @id = NEWID()
	INSERT INTO [Tape.jobs] (
		id
		,name
		,[description]
		,[type]
		,full_mediapool_id
		,incremental_mediapool_id
		,options
		)
	VALUES (
		@id
		,@name
		,@description
		,@type
		,@full_mediapool_id
		,@incremental_mediapool_id
		,@options
		)
	SELECT @id
END
GO


--------------------------------------------------------------------------------
-- Tape.add_tape_medium
PRINT N'Creating [dbo].[Tape.add_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_tape_medium]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.add_tape_medium]
	@id uniqueidentifier
	,@barcode nvarchar(MAX)
	,@name nvarchar(MAX)
	,@description nvarchar(MAX)
	,@media_family_id int
	,@media_sequence_number int
	,@media_time datetime
	,@continuation bit
	,@location_type tinyint
	,@location_library_id uniqueidentifier
	,@location_address int
	,@original_slot int
	,@media_pool_id uniqueidentifier
	,@capacity bigint
	,@remaining bigint
	,@block_size int
	,@partition_count int
	,@is_write_protected bit
	,@is_full bit
	,@media_id uniqueidentifier
	,@media_label nvarchar(MAX)
	,@format_logical_address bigint
	,@retirement_reason nvarchar(MAX)
	,@retired bit
	,@cleaner bit
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.tape_mediums] (
		id
		,barcode
		,name
		,description
		,media_family_id
		,media_sequence_number
		,media_time
		,continuation
		,location_type
		,location_library_id
		,location_address
		,original_slot
		,media_pool_id
		,capacity
		,remaining
		,block_size
		,partition_count
		,is_write_protected
		,is_full
		,media_id
		,media_label
		,format_logical_address
		,retention_reason
		,retired
		,cleaner
		)
	VALUES (
		@id
		,@barcode
		,@name
		,@description
		,@media_family_id
		,@media_sequence_number
		,@media_time
		,@continuation
		,@location_type
		,@location_library_id
		,@location_address
		,@original_slot
		,@media_pool_id
		,@capacity
		,@remaining
		,@block_size
		,@partition_count
		,@is_write_protected
		,@is_full
		,@media_id
		,@media_label
		,@format_logical_address
		,@retirement_reason
		,@retired
		,@cleaner
		)
END
GO


--------------------------------------------------------------------------------
-- Tape.add_tape_drive
PRINT N'Creating [dbo].[Tape.add_tape_drive]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_tape_drive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_tape_drive]
GO
CREATE PROCEDURE [dbo].[Tape.add_tape_drive]
	@device_id uniqueidentifier
	,@address int
	,@name nvarchar(max)
	,@model nvarchar(max)
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [Tape.tape_drives] (
		device_id
		,[address]
		,[name]
		,[model]
		)
	VALUES (
		@device_id
		,@address
		,@name
		,@model
		)
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_library
PRINT N'Creating [dbo].[Tape.delete_library]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_library]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_library]
GO
CREATE PROCEDURE [dbo].[Tape.delete_library]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.libraries] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_job
PRINT N'Creating [dbo].[Tape.delete_job]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_job]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_job]
GO
CREATE PROCEDURE [dbo].[Tape.delete_job]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.jobs] WHERE id = @id
	DELETE FROM [BJobs] WHERE [id] = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_tape_medium
PRINT N'Creating [dbo].[Tape.delete_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_tape_medium]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.delete_tape_medium]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.tape_mediums] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_tape_drive
PRINT N'Creating [dbo].[Tape.delete_tape_drive]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_tape_drive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_tape_drive]
GO
CREATE PROCEDURE [dbo].[Tape.delete_tape_drive]
	@device_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.tape_drives] WHERE device_id = @device_id
END
GO


--------------------------------------------------------------------------------
-- Tape.unlock_tape_medium
PRINT N'Creating [dbo].[Tape.unlock_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.unlock_tape_medium]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.unlock_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.unlock_tape_medium]
	@tape_medium_id uniqueidentifier 
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET lock_id = NULL WHERE id = @tape_medium_id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_library
PRINT N'Creating [dbo].[Tape.update_library]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_library]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_library]
GO
CREATE PROCEDURE [dbo].[Tape.update_library]
	@id uniqueidentifier
	,@type tinyint
	,@enabled bit
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.libraries] SET
		[type] = @type
		,[enabled] = @enabled
	WHERE 
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_job
PRINT N'Creating [dbo].[Tape.update_job]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_job]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_job]
GO
CREATE PROCEDURE [dbo].[Tape.update_job]
	@id uniqueidentifier
	,@name nvarchar(MAX)
	,@description nvarchar(MAX)
	,@type tinyint
	,@full_mediapool_id uniqueidentifier
	,@incremental_mediapool_id uniqueidentifier
	,@schedule_enabled bit
	,@options xml
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.jobs] SET
		name = @name
		,[description] = @description
		,[type] = @type
		,full_mediapool_id = @full_mediapool_id
		,incremental_mediapool_id = @incremental_mediapool_id
		,options= @options
		,schedule_enabled = @schedule_enabled
	WHERE
		id = @id

	UPDATE [BJobs] SET
		name = @name
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_job
PRINT N'Creating [dbo].[Tape.get_job]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_job]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_job]
GO
CREATE PROCEDURE [dbo].[Tape.get_job]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.jobs] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium
PRINT N'Creating [dbo].[Tape.update_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium]
	@id uniqueidentifier
	,@barcode nvarchar(MAX)
	,@name nvarchar(MAX)
	,@description nvarchar(MAX)
	,@media_family_id int
	,@media_sequence_number int
	,@media_time datetime
	,@continuation bit
	,@location_type tinyint
	,@location_library_id uniqueidentifier
	,@location_address int
	,@original_slot int
	,@media_pool_id uniqueidentifier
	,@capacity bigint
	,@remaining bigint
	,@block_size int
	,@partition_count int
	,@is_write_protected bit
	,@is_full bit
	,@media_id uniqueidentifier
	,@media_label nvarchar(MAX)
	,@format_logical_address bigint
	,@retirement_reason nvarchar(MAX)
	,@retired bit
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		barcode = @barcode
		,name = @name
		,description = @description
		,media_family_id = @media_family_id
		,media_sequence_number = @media_sequence_number
		,media_time = @media_time
		,continuation = @continuation
		,location_type = @location_type
		,location_library_id = @location_library_id
		,location_address = @location_address
		,original_slot = @original_slot
		,media_pool_id = @media_pool_id
		,capacity = @capacity
		,remaining = @remaining
		,block_size = @block_size
		,partition_count = @partition_count
		,is_write_protected = @is_write_protected
		,is_full = @is_full
		,media_id = @media_id
		,media_label = @media_label
		,format_logical_address = @format_logical_address
		,retention_reason = @retirement_reason
		,retired = @retired
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_location
PRINT N'Creating [dbo].[Tape.update_tape_medium_location]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_location]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_location]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_location]
	@id uniqueidentifier
	,@location_type tinyint
	,@location_library_id uniqueidentifier
	,@location_address int
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		location_type = @location_type
		,location_library_id = @location_library_id
		,location_address = @location_address
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_original_slot
PRINT N'Creating [dbo].[Tape.update_tape_medium_original_slot]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_original_slot]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_original_slot]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_original_slot]
	@id uniqueidentifier
	,@original_slot int
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		original_slot = @original_slot
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_barcode
PRINT N'Creating [dbo].[Tape.update_tape_medium_barcode]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_barcode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_barcode]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_barcode]
	@id uniqueidentifier
	,@barcode nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		barcode = @barcode
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_cleaner
PRINT N'Creating [dbo].[Tape.update_tape_medium_cleaner]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_cleaner]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_cleaner]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_cleaner]
	@id uniqueidentifier
	,@cleaner bit
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		cleaner = @cleaner
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_name
PRINT N'Creating [dbo].[Tape.update_tape_medium_name]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_name]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_name]
	@id uniqueidentifier
	,@name nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		name = @name
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_description
PRINT N'Creating [dbo].[Tape.update_tape_medium_description]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_description]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_description]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_description]
	@id uniqueidentifier
	,@description nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		description = @description
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_media_family_id
PRINT N'Creating [dbo].[Tape.update_tape_medium_media_family_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_media_family_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_media_family_id]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_media_family_id]
	@id uniqueidentifier
	,@media_family_id int
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		media_family_id = @media_family_id
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_is_full
PRINT N'Creating [dbo].[Tape.update_tape_medium_is_full]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_is_full]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_is_full]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_is_full]
	@id uniqueidentifier
	,@is_full bit
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		is_full = @is_full
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_retired
PRINT N'Creating [dbo].[Tape.update_tape_medium_retired]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_retired]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_retired]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_retired]
	@id uniqueidentifier
	,@retirement_reason nvarchar(MAX)
	,@retired bit
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		retention_reason = @retirement_reason
		,retired = @retired
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_media_pool_id
PRINT N'Creating [dbo].[Tape.update_tape_medium_media_pool_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_media_pool_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_media_pool_id]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_media_pool_id]
	@id uniqueidentifier
	,@media_pool_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		media_pool_id = @media_pool_id
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_media_info
PRINT N'Creating [dbo].[Tape.update_tape_medium_media_info]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_media_info]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_media_info]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_media_info]
	@id uniqueidentifier
	,@media_family_id int
	,@media_sequence_number int
	,@media_time datetime
	,@continuation bit
	,@media_id uniqueidentifier
	,@media_label nvarchar(MAX)
	,@format_logical_address bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		media_family_id = @media_family_id
		,media_sequence_number = @media_sequence_number
		,media_time = @media_time
		,continuation = @continuation
		,media_id = @media_id
		,media_label = @media_label
		,format_logical_address = @format_logical_address
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_last_write_time
PRINT N'Creating [dbo].[Tape.update_tape_medium_last_write_time]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_last_write_time]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_last_write_time]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_last_write_time]
	@id uniqueidentifier
	,@last_write_time datetime
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		last_write_time = @last_write_time
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_remaining
PRINT N'Creating [dbo].[Tape.update_tape_medium_remaining]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_remaining]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_remaining]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_remaining]
	@id uniqueidentifier
	,@remaining bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		remaining = @remaining
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_medium_info
PRINT N'Creating [dbo].[Tape.update_tape_medium_info]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_medium_info]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_medium_info]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_medium_info]
	@id uniqueidentifier
	,@capacity bigint
	,@remaining bigint
	,@block_size int
	,@partition_count int
	,@is_write_protected bit
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET
		capacity = @capacity
		,remaining = @remaining
		,block_size = @block_size
		,partition_count = @partition_count
		,is_write_protected = @is_write_protected
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_tape_drive
PRINT N'Creating [dbo].[Tape.update_tape_drive]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_tape_drive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_tape_drive]
GO
CREATE PROCEDURE [dbo].[Tape.update_tape_drive]
	@device_id uniqueidentifier
	,@address int
	,@enabled bit
	,@name nvarchar(max)
	,@model nvarchar(max)
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [Tape.tape_drives] SET
		[address] = @address
		,[enabled] = @enabled
		,[name] = @name
		,[model] = @model
	WHERE
		device_id = @device_id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_session_progress
PRINT N'Creating [dbo].[Tape.update_session_progress]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_session_progress]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_session_progress]
GO
CREATE PROCEDURE [dbo].[Tape.update_session_progress]
	@id uniqueidentifier
	,@progress int
	,@total_objects int
	,@processed_objects int
	,@total_size bigint
	,@processed_size bigint
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @usn bigint

	exec [dbo].[IncrementUsn] @usn OUTPUT;

	UPDATE [Backup.Model.BackupJobSessions] SET
		total_objects = @total_objects
		,processed_objects = @processed_objects
		,total_size = @total_size
		,total_used_size = @total_size
		,processed_size = @processed_size
		,processed_used_size = @processed_size
		,read_size = @processed_size
		,stored_size = @processed_size
		,usn = @usn
	WHERE
		id = @id

	exec [dbo].[IncrementUsn] @usn OUTPUT;

	UPDATE [Backup.Model.JobSessions] SET
		progress = @progress
		,usn = @usn
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_session
PRINT N'Creating [dbo].[Tape.update_session]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_session]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_session]
GO
CREATE PROCEDURE [dbo].[Tape.update_session]
	@id uniqueidentifier
	,@job_id uniqueidentifier
	,@job_name nvarchar(MAX)
	,@job_type int
	,@creation_time datetime
	,@end_time datetime
	,@result int
	,@state int
AS
BEGIN
	SET NOCOUNT ON;
		
	UPDATE [Backup.Model.JobSessions] SET
		job_id = @job_id
		,job_name = @job_name
		,job_type = @job_type
		,creation_time = @creation_time
		,end_time = @end_time
		,result = @result
		,[state] = @state
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_media_pool
PRINT N'Creating [dbo].[Tape.update_media_pool]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_media_pool]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_media_pool]
GO
CREATE PROCEDURE [dbo].[Tape.update_media_pool]
	@id uniqueidentifier
	,@name nvarchar(MAX)
	,@description nvarchar(MAX)
	,@type tinyint
	,@options xml
	,@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.media_pools] SET
		name = @name
		,[description] = @description
		,[type] = @type
		,options = @options
		,library_id = @library_id
	WHERE 
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.update_backup
PRINT N'Creating [dbo].[Tape.update_backup]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_backup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.update_backup]
GO
CREATE PROCEDURE [dbo].[Tape.update_backup]
	@id uniqueidentifier
	,@job_id uniqueidentifier
	,@name nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.backups] SET
		job_id = @job_id,
		name = @name
	WHERE
		id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.unlock_media_pool
PRINT N'Creating [dbo].[Tape.unlock_media_pool]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.unlock_media_pool]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.unlock_media_pool]
GO
CREATE PROCEDURE [dbo].[Tape.unlock_media_pool]
	@media_pool_id uniqueidentifier 
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.media_pools] SET lock_id = NULL WHERE id = @media_pool_id
END
GO


--------------------------------------------------------------------------------
-- Tape.unlock_all
PRINT N'Creating [dbo].[Tape.unlock_all]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.unlock_all]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.unlock_all]
GO
CREATE PROCEDURE [dbo].[Tape.unlock_all]
	@lock_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.devices] SET lock_id = NULL WHERE lock_id = @lock_id
	UPDATE [Tape.media_pools] SET lock_id = NULL WHERE lock_id = @lock_id
	UPDATE [Tape.tape_mediums] SET lock_id = NULL WHERE lock_id = @lock_id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_session
PRINT N'Creating [dbo].[Tape.delete_session]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_session]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_session]
GO
CREATE PROCEDURE [dbo].[Tape.delete_session]
	@id uniqueidentifier	
AS
BEGIN
	SET NOCOUNT ON;
	
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_media_pool
PRINT N'Creating [dbo].[Tape.delete_media_pool]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_media_pool]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_media_pool]
GO
CREATE PROCEDURE [dbo].[Tape.delete_media_pool]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.media_pools] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_library_devices
PRINT N'Creating [dbo].[Tape.delete_library_devices]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_library_devices]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_library_devices]
GO
CREATE PROCEDURE [dbo].[Tape.delete_library_devices]
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.library_devices] WHERE library_id = @library_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_changers_by_library_id
PRINT N'Creating [dbo].[Tape.get_all_changers_by_library_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_changers_by_library_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_changers_by_library_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_changers_by_library_id]
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		[Tape.changers].* 
	FROM 
		[Tape.changers] 
		INNER JOIN [Tape.library_devices] ON [Tape.changers].device_id = [Tape.library_devices].device_id
	WHERE
		[Tape.library_devices].library_id = @library_id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_backup
PRINT N'Creating [dbo].[Tape.delete_backup]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_backup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_backup]
GO
CREATE PROCEDURE [dbo].[Tape.delete_backup]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.backups] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.cleanup_orphan_locks
PRINT N'Creating [dbo].[Tape.cleanup_orphan_locks]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.cleanup_orphan_locks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.cleanup_orphan_locks]
GO
CREATE PROCEDURE [dbo].[Tape.cleanup_orphan_locks]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT id INTO #session_ids FROM [Backup.Model.JobSessions] WHERE state <> -1 AND [Backup.Model.JobSessions].job_type IN (24, 25, 26, 27, 28, 29, 32, 33, 34, 35, 36)
	CREATE CLUSTERED INDEX PK_id ON #session_ids (id)
	
	UPDATE [Tape.devices] SET lock_id = NULL WHERE lock_id IS NOT NULL AND lock_id NOT IN (SELECT id FROM #session_ids)
	UPDATE [Tape.media_pools] SET lock_id = NULL WHERE lock_id IS NOT NULL AND lock_id NOT IN (SELECT id FROM #session_ids)
	UPDATE [Tape.tape_mediums] SET lock_id = NULL WHERE lock_id IS NOT NULL AND lock_id NOT IN (SELECT id FROM #session_ids)
END
GO


--------------------------------------------------------------------------------
-- Tape.empty_media_pool
PRINT N'Creating [dbo].[Tape.empty_media_pool]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.empty_media_pool]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.empty_media_pool]
GO
CREATE PROCEDURE [dbo].[Tape.empty_media_pool]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.tape_mediums] SET media_pool_id = (SELECT id FROM [Tape.media_pools] WHERE [type] = 1 AND library_id = (SELECT library_id FROM [Tape.media_pools] WHERE id = @id)) WHERE media_pool_id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.add_session
PRINT N'Creating [dbo].[Tape.add_session]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_session]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_session]
GO
CREATE PROCEDURE [dbo].[Tape.add_session]
	@job_id uniqueidentifier
	,@job_name nvarchar(MAX)
	,@job_type int
	,@creation_time datetime
	,@end_time datetime
	,@result int
	,@state int
	,@control int
	,@is_startfull bit
	,@transport_type int
	,@job_spec xml
	,@run_manually bit
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @id AS UNIQUEIDENTIFIER
	SET @id = NEWID()

	INSERT INTO [Backup.Model.JobSessions] (
		id
		,job_id
		,job_name
		,job_type
		,creation_time
		,end_time
		,result
		,[state]
		,[control]
		,[operation]
		,[job_spec]
		,run_manually
		)
	VALUES (
		@id
		,@job_id
		,@job_name
		,@job_type
		,@creation_time
		,@end_time
		,@result
		,@state
		,@control
		,''
		,@job_spec
		,@run_manually
		)

	INSERT INTO [Backup.Model.BackupJobSessions] (
		id
		,is_startfull
		,transport_type)
	VALUES (
		@id
		,@is_startfull
		,@transport_type)
		
	SELECT @id
END
GO


--------------------------------------------------------------------------------
-- Tape.add_media_pool
PRINT N'Creating [dbo].[Tape.add_media_pool]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_media_pool]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_media_pool]
GO
CREATE PROCEDURE [dbo].[Tape.add_media_pool]
	@name nvarchar(MAX)
	,@description  nvarchar(MAX)
	,@type tinyint
	,@options xml
	,@library_id uniqueidentifier	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @id AS UNIQUEIDENTIFIER
	SET @id = NEWID()
	
	INSERT INTO [Tape.media_pools] (
		id
		,name
		,[description]
		,[type]
		,options
		,library_id
	) VALUES (
		@id
		,@name
		,@description
		,@type
		,@options
		,@library_id
	)
		
	SELECT @id
END
GO


--------------------------------------------------------------------------------
-- Tape.add_library_device
PRINT N'Creating [dbo].[Tape.add_library_device]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_library_device]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_library_device]
GO
CREATE PROCEDURE [dbo].[Tape.add_library_device]
	@library_id uniqueidentifier
	,@device_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.library_devices] (
		library_id
		,device_id)
	VALUES (
		@library_id
		,@device_id
		)
END
GO


--------------------------------------------------------------------------------
-- Tape.add_backup
PRINT N'Creating [dbo].[Tape.add_backup]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_backup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_backup]
GO
CREATE PROCEDURE [dbo].[Tape.add_backup]
	@job_id uniqueidentifier
	,@name nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @id AS UNIQUEIDENTIFIER
	SET @id = NEWID()
	
	INSERT INTO [Tape.backups] (
		id
		,job_id
		,name
		)
	VALUES (
		@id
		,@job_id
		,@name
		)
		
	SELECT @id
END
GO


--------------------------------------------------------------------------------
-- Tape.lock_tape_drive_device
PRINT N'Creating [dbo].[Tape.lock_tape_drive_device]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.lock_tape_drive_device]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.lock_tape_drive_device]
GO
CREATE PROCEDURE [dbo].[Tape.lock_tape_drive_device]
	@device_ids xml
	,@lock_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @temp_lock_id uniqueidentifier
	SET @temp_lock_id = NEWID()
	DECLARE @result uniqueidentifier
	DECLARE @hdocument int
	EXEC sp_xml_preparedocument @hdocument OUTPUT, @device_ids
	DECLARE @tmp TABLE(num int PRIMARY KEY IDENTITY, id uniqueidentifier)
	INSERT INTO @tmp SELECT * FROM OPENXML(@hdocument, N'/ROOT/id', 1)  WITH (id uniqueidentifier)
	UPDATE 
		[Tape.devices] 
	SET 
		lock_id = @temp_lock_id
	WHERE 
		id = 
			(
				SELECT TOP 1	
					t.id
				FROM 
					@tmp t
					INNER JOIN [Tape.devices] WITH (TABLOCKX) ON t.id = [Tape.devices].id 
					INNER JOIN [Tape.tape_drives] ON [Tape.tape_drives].device_id = [Tape.devices].id 
					
				WHERE
					[Tape.devices].lock_id IS NULL AND
					[Tape.tape_drives].[enabled] = 1
			)
	SELECT @result = id FROM [Tape.devices] WHERE lock_id = @temp_lock_id
	UPDATE [Tape.devices] SET lock_id = @lock_id WHERE lock_id = @temp_lock_id
	SELECT @result
END
GO


--------------------------------------------------------------------------------
-- Tape.lock_media_pool
PRINT N'Creating [dbo].[Tape.lock_media_pool]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.lock_media_pool]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.lock_media_pool]
GO
CREATE PROCEDURE [dbo].[Tape.lock_media_pool]
	@media_pool_id uniqueidentifier, 
	@lock_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE [Tape.media_pools] SET lock_id = @lock_id WHERE id = @media_pool_id AND lock_id IS NULL
	
	SELECT @@ROWCOUNT
END
GO


--------------------------------------------------------------------------------
-- Tape.library_names
PRINT N'Creating [dbo].[Tape.library_names]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.library_names]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.library_names]
GO
CREATE VIEW dbo.[Tape.library_names]
AS
SELECT     [Tape.libraries].id, (CASE WHEN [Tape.changers].name IS NULL OR LTRIM(RTRIM([Tape.changers].name)) = ''
                      THEN [Tape.changers].vendor_id + ' ' + [Tape.changers].product_id + ' ' + [Tape.changers].revision ELSE [Tape.changers].name END) AS name
FROM         [Tape.libraries] INNER JOIN
                      [Tape.library_devices] ON [Tape.libraries].id = [Tape.library_devices].library_id INNER JOIN
                      [Tape.changers] ON [Tape.library_devices].device_id = [Tape.changers].device_id
WHERE     [Tape.libraries].type = 0
UNION
SELECT     [Tape.libraries].id, [Tape.devices].name
FROM         [Tape.libraries] INNER JOIN
                      [Tape.library_devices] ON [Tape.libraries].id = [Tape.library_devices].library_id INNER JOIN
                      [Tape.devices] ON [Tape.library_devices].device_id = [Tape.devices].id
WHERE     [Tape.libraries].type = 1
GO


--------------------------------------------------------------------------------
-- Tape.get_media_pool
PRINT N'Creating [dbo].[Tape.get_media_pool]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_media_pool]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_media_pool]
GO
CREATE PROCEDURE [dbo].[Tape.get_media_pool]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.media_pools] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_locking_sessions
PRINT N'Creating [dbo].[Tape.get_locking_sessions]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_locking_sessions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_locking_sessions]
GO
CREATE PROCEDURE [dbo].[Tape.get_locking_sessions]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT lock_id INTO #lock_ids FROM [Tape.devices]
	UNION
	SELECT lock_id FROM [Tape.media_pools]
	UNION
	SELECT lock_id FROM [Tape.tape_mediums]
	
	CREATE CLUSTERED INDEX PK_lock_id ON #lock_ids (lock_id)
	
	SELECT * FROM [Backup.Model.BackupJobSessions] AS s
	INNER JOIN [Backup.Model.JobSessions] AS bs 
	ON s.id = bs.id
	WHERE s.id IN (SELECT * FROM #lock_ids)
END
GO


--------------------------------------------------------------------------------
-- Tape.get_library_devices
PRINT N'Creating [dbo].[Tape.get_library_devices]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_library_devices]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_library_devices]
GO
CREATE PROCEDURE [dbo].[Tape.get_library_devices]
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.library_devices] WHERE library_id = @library_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_library_device_by_device_id
PRINT N'Creating [dbo].[Tape.get_library_device_by_device_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_library_device_by_device_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_library_device_by_device_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_library_device_by_device_id]
	@device_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.library_devices] WHERE device_id = @device_id
END
GO


--------------------------------------------------------------------------------
-- Tape.report_backups_changes
PRINT N'Creating [dbo].[Tape.report_backups_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_backups_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_backups_changes]
GO
CREATE VIEW dbo.[Tape.report_backups_changes]
AS
SELECT     id, job_id, name, usn
FROM         dbo.[Tape.backups]
GO


--------------------------------------------------------------------------------
-- Tape.report_sessions_changes
PRINT N'Creating [dbo].[Tape.report_sessions_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_sessions_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_sessions_changes]
GO
CREATE VIEW dbo.[Tape.report_sessions_changes]
AS
SELECT        s.id, (CASE WHEN s.[usn] > bs.[usn] THEN s.[usn] ELSE bs.[usn] END) AS usn, bs.job_name, bs.job_type, bs.state, bs.creation_time AS start_time, bs.end_time, 
                         bs.result, bs.progress, s.total_objects, s.processed_objects, s.total_size, s.processed_size, s.is_startfull, s.transport_type
FROM            dbo.[Backup.Model.BackupJobSessions] AS s INNER JOIN
                         dbo.[Backup.Model.JobSessions] AS bs ON s.id = bs.id
GO


--------------------------------------------------------------------------------
-- Tape.report_media_pools_changes
PRINT N'Creating [dbo].[Tape.report_media_pools_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_media_pools_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_media_pools_changes]
GO
CREATE VIEW dbo.[Tape.report_media_pools_changes]
AS
SELECT     m.id, (CASE WHEN ISNULL(m.usn, 0) > ISNULL(MAX(tu.usn), 0) THEN ISNULL(m.usn, 0) ELSE ISNULL(MAX(tu.usn), 0) END) AS usn, m.name, m.type, SUM(t.capacity) 
                      AS capacity, SUM(t.remaining) AS remaining, COUNT(t.id) AS tapes, m.library_id
FROM         
	dbo.[Tape.media_pools] AS m 
	LEFT OUTER JOIN dbo.[Tape.tape_mediums] AS t ON m.id = t.media_pool_id 
	CROSS JOIN (SELECT MAX(usn) AS usn FROM dbo.[Tape.tape_mediums]) AS tu
WHERE ISNULL(t.cleaner, 0) = 0
GROUP BY m.id, m.usn, m.name, m.type, m.library_id
GO


--------------------------------------------------------------------------------
-- Tape.report_tape_drives_changes
PRINT N'Creating [dbo].[Tape.report_tape_drives_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_tape_drives_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_tape_drives_changes]
GO
CREATE VIEW [dbo].[Tape.report_tape_drives_changes]
AS
SELECT        td.device_id AS id, ld1.library_id, td.usn, td.enabled, d1.state, td.name, tm.barcode AS media_barcode, tm.id AS media_id, tm.name AS media_name, ld2.device_id, 
                         CASE WHEN c.first_drive_number IS NULL THEN 'Drive 1 (' + d1.name + ')' ELSE 'Drive ' + CAST(td.address + c.first_drive_number AS NVARCHAR) 
                         + ' (' + d1.name + ')' END AS device, dbo.[Tape.media_families].name AS media_set_name
FROM            dbo.[Tape.media_families] RIGHT OUTER JOIN
                         dbo.[Tape.tape_mediums] AS tm ON dbo.[Tape.media_families].id = tm.media_family_id RIGHT OUTER JOIN
                         dbo.[Tape.devices] AS d2 INNER JOIN
                         dbo.[Tape.library_devices] AS ld2 ON d2.id = ld2.device_id INNER JOIN
                         dbo.[Tape.changers] AS c ON d2.id = c.device_id RIGHT OUTER JOIN
                         dbo.[Tape.tape_drives] AS td INNER JOIN
                         dbo.[Tape.library_devices] AS ld1 ON td.device_id = ld1.device_id INNER JOIN
                         dbo.[Tape.devices] AS d1 ON td.device_id = d1.id AND ld1.device_id = d1.id ON d2.type = 8 AND ld2.library_id = ld1.library_id ON tm.location_type = 0 AND 
                         tm.location_address = td.address AND tm.location_library_id = ld1.library_id
GO


--------------------------------------------------------------------------------
-- Tape.report_tape_drive_changes
PRINT N'Creating [dbo].[Tape.report_tape_drive_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_tape_drive_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_tape_drive_changes]
GO
CREATE VIEW [dbo].[Tape.report_tape_drive_changes]
AS
SELECT        dbo.[Tape.tape_mediums].id, dbo.[Tape.tape_mediums].usn, dbo.[Tape.tape_mediums].name, dbo.[Tape.tape_mediums].barcode, dbo.[Tape.tape_drives].device_id, 
                         dbo.[Tape.media_families].name AS media_set_name
FROM            dbo.[Tape.tape_mediums] INNER JOIN
                         dbo.[Tape.library_devices] ON dbo.[Tape.tape_mediums].location_library_id = dbo.[Tape.library_devices].library_id INNER JOIN
                         dbo.[Tape.tape_drives] ON dbo.[Tape.library_devices].device_id = dbo.[Tape.tape_drives].device_id AND 
                         (dbo.[Tape.tape_mediums].location_address = dbo.[Tape.tape_drives].address OR
                         dbo.[Tape.tape_drives].address IS NULL) LEFT OUTER JOIN
                         dbo.[Tape.media_families] ON dbo.[Tape.tape_mediums].media_family_id = dbo.[Tape.media_families].id
WHERE        (dbo.[Tape.tape_mediums].location_type = 0)
GO


--------------------------------------------------------------------------------
-- Tape.report_jobs_changes
PRINT N'Creating [dbo].[Tape.report_jobs_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_jobs_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_jobs_changes]
GO
CREATE VIEW dbo.[Tape.report_jobs_changes]
AS
SELECT     j.id, (CASE WHEN ISNULL(bs.usn, j.usn) > j.usn THEN ISNULL(bs.usn, j.usn) ELSE j.usn END) AS usn, j.name, ISNULL(bs.state, -1) AS state, 
                      ISNULL(bs.result, -1) AS result, j.type, j.schedule_enabled
FROM         dbo.[Tape.jobs] AS j 
					LEFT OUTER JOIN [Backup.Model.JobSessions] AS bs ON j.id = bs.job_id
WHERE     (bs.id IS NULL OR
                      bs.id =
                          (SELECT     TOP (1) id
                            FROM          dbo.[Backup.Model.JobSessions]
                            WHERE      (job_id = j.id)
                            ORDER BY creation_time DESC)) AND (j.type IN (24, 25, 26, 27, 28, 29, 32, 33, 34, 35, 36))
GO


--------------------------------------------------------------------------------
-- Tape.get_session
PRINT N'Creating [dbo].[Tape.get_session]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_session]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_session]
GO
CREATE PROCEDURE [dbo].[Tape.get_session]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Backup.Model.BackupJobSessions] AS s
	INNER JOIN [Backup.Model.JobSessions] AS bs
	ON s.id = bs.id
	WHERE s.id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_devices_by_library_id
PRINT N'Creating [dbo].[Tape.get_devices_by_library_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_devices_by_library_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_devices_by_library_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_devices_by_library_id]
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		[Tape.devices].* 
	FROM 
		[Tape.devices] 
		INNER JOIN [Tape.library_devices] ON [Tape.devices].id = [Tape.library_devices].device_id
	WHERE 
		[Tape.library_devices].library_id = @library_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_last_session_by_job
PRINT N'Creating [dbo].[Tape.get_last_session_by_job]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_last_session_by_job]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_last_session_by_job]
GO
CREATE PROCEDURE [dbo].[Tape.get_last_session_by_job]
	@job_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1 *  
	FROM [Backup.Model.BackupJobSessions] AS s
	INNER JOIN [Backup.Model.JobSessions] AS bs ON bs.id = s.id 
	WHERE bs.job_id = @job_id 
	ORDER BY bs.creation_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_sessions
PRINT N'Creating [dbo].[Tape.get_all_sessions]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_sessions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_sessions]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_sessions]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Backup.Model.BackupJobSessions] AS s
	INNER JOIN [Backup.Model.JobSessions] AS bs
	ON s.id = bs.id
	WHERE bs.job_type in (24, 25, 26, 27, 28, 29, 32, 33, 34, 35, 36)
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_media_pools_by_type
PRINT N'Creating [dbo].[Tape.get_all_media_pools_by_type]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_media_pools_by_type]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_media_pools_by_type]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_media_pools_by_type]
	@type tinyint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.media_pools] WHERE [type] = @type
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_media_pools_by_type_and_library_id
PRINT N'Creating [dbo].[Tape.get_all_media_pools_by_type_and_library_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_media_pools_by_type_and_library_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_media_pools_by_type_and_library_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_media_pools_by_type_and_library_id]
	@type tinyint,
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.media_pools] WHERE [type] = @type AND [library_id] = @library_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_media_pools
PRINT N'Creating [dbo].[Tape.get_all_media_pools]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_media_pools]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_media_pools]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_media_pools]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.media_pools]
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_library_devices
PRINT N'Creating [dbo].[Tape.get_all_library_devices]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_library_devices]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_library_devices]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_library_devices]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.library_devices]
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_tape_drives_by_library_id
PRINT N'Creating [dbo].[Tape.get_all_tape_drives_by_library_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_tape_drives_by_library_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_tape_drives_by_library_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_tape_drives_by_library_id]
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		[Tape.tape_drives].* 
	FROM 
		[Tape.tape_drives] 
		INNER JOIN [Tape.library_devices] ON [Tape.tape_drives].device_id = [Tape.library_devices].device_id
	WHERE
		[Tape.library_devices].library_id = @library_id
	ORDER BY
		[address] ASC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup_by_job_id
PRINT N'Creating [dbo].[Tape.get_backup_by_job_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup_by_job_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup_by_job_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup_by_job_id]
	@job_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.backups] WHERE job_id = @job_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup_all
PRINT N'Creating [dbo].[Tape.get_backup_all]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup_all]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup_all]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup_all]
	
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.backups]
END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup
PRINT N'Creating [dbo].[Tape.get_backup]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.backups] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup_sets_by_backup_id
PRINT N'Creating [dbo].[Tape.get_backup_sets_by_backup_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup_sets_by_backup_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup_sets_by_backup_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup_sets_by_backup_id]
	@backup_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.backup_sets] WHERE backup_id = @backup_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup_set
PRINT N'Creating [dbo].[Tape.get_backup_set]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup_set]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup_set]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup_set]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.backup_sets] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup_sets_by_media_family_id
PRINT N'Creating [dbo].[Tape.get_backup_sets_by_media_family_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup_sets_by_media_family_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup_sets_by_media_family_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup_sets_by_media_family_id]
	@media_family_id int
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.backup_sets] WHERE media_family_id = @media_family_id ORDER BY number
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tapes_by_backup_id
PRINT N'Creating [dbo].[Tape.get_tapes_by_backup_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tapes_by_backup_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tapes_by_backup_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_tapes_by_backup_id]
	@backup_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * 
	FROM [Tape.tape_mediums] 
	WHERE media_family_id in (SELECT [media_family_id] FROM [Tape.backup_sets] WHERE backup_id = @backup_id) 
	ORDER BY media_time
END
GO


--------------------------------------------------------------------------------
-- Tape.report_media_pool_changes
PRINT N'Creating [dbo].[Tape.report_media_pool_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_media_pool_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_media_pool_changes]
GO
CREATE VIEW dbo.[Tape.report_media_pool_changes]
AS
SELECT        dbo.[Tape.tape_mediums].id, dbo.[Tape.tape_mediums].usn, dbo.[Tape.tape_mediums].barcode AS name, dbo.[Tape.media_pools].id AS media_pool_id, 
                         dbo.[Tape.library_names].name AS library, dbo.[Tape.tape_mediums].name AS media_set
FROM            dbo.[Tape.tape_mediums] LEFT OUTER JOIN
                         dbo.[Tape.library_names] ON dbo.[Tape.tape_mediums].location_library_id = dbo.[Tape.library_names].id LEFT OUTER JOIN
                         dbo.[Tape.media_pools] ON dbo.[Tape.tape_mediums].media_pool_id = dbo.[Tape.media_pools].id
WHERE dbo.[Tape.tape_mediums].cleaner = 0
GO


--------------------------------------------------------------------------------
-- Tape.report_media_changes
PRINT N'Creating [dbo].[Tape.report_media_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_media_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_media_changes]
GO
CREATE VIEW [dbo].[Tape.report_media_changes]
AS
SELECT        dbo.[Tape.tape_mediums].id, 
				dbo.[Tape.tape_mediums].usn, 
				dbo.[Tape.tape_mediums].name, 
				dbo.[Tape.tape_mediums].barcode, 
                dbo.[Tape.library_names].name AS library,
                dbo.[Tape.media_pools].name AS media_pool,
                dbo.[Tape.tape_mediums].media_pool_id, 
                dbo.[Tape.tape_mediums].location_library_id,
                dbo.[Tape.tape_mediums].location_type,
                dbo.[Tape.tape_mediums].location_address + ISNULL
                             ((SELECT        CASE dbo.[Tape.tape_mediums].location_type WHEN 0 THEN c.first_drive_number WHEN 1 THEN c.first_slot_number ELSE 1 END AS Expr1
                                 FROM            dbo.[Tape.library_devices] AS ld INNER JOIN
                                                          dbo.[Tape.changers] AS c ON ld.device_id = c.device_id
                                 WHERE        (dbo.[Tape.tape_mediums].location_library_id = ld.library_id)), 1) AS location_number, 
                dbo.[Tape.tape_mediums].capacity, 
                dbo.[Tape.tape_mediums].remaining, 
                dbo.[Tape.tape_mediums].retention_reason, 
                dbo.[Tape.tape_mediums].description, 
                dbo.[Tape.tape_mediums].lock_id, 
                dbo.[Tape.media_families].name AS media_set_name,
                dbo.[Tape.tape_mediums].media_sequence_number
FROM            dbo.[Tape.media_pools] RIGHT OUTER JOIN
                         dbo.[Tape.tape_mediums] LEFT OUTER JOIN
                         dbo.[Tape.media_families] ON dbo.[Tape.tape_mediums].media_family_id = dbo.[Tape.media_families].id ON 
                         dbo.[Tape.media_pools].id = dbo.[Tape.tape_mediums].media_pool_id LEFT OUTER JOIN
                         dbo.[Tape.library_names] ON dbo.[Tape.tape_mediums].location_library_id = dbo.[Tape.library_names].id
WHERE        (dbo.[Tape.tape_mediums].media_pool_id <> '00000000-0000-0000-0000-000000000000') AND (dbo.[Tape.tape_mediums].cleaner = 0)
GO


--------------------------------------------------------------------------------
-- Tape.report_libraries_changes
PRINT N'Creating [dbo].[Tape.report_libraries_changes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.report_libraries_changes]') AND type in (N'V'))
	DROP VIEW [dbo].[Tape.report_libraries_changes]
GO
CREATE VIEW [dbo].[Tape.report_libraries_changes]
AS
SELECT      [Tape.libraries].id, 
			[Tape.library_names].name name, 
			[Tape.libraries].usn, 
			[Tape.libraries].type, 
			NULL AS device_id, 
            [Tape.changers].drive_cleaning_required AS drive_cleaning_required,
            '' as model,
            [Tape.libraries].enabled,
            [Tape.devices].[state] 
FROM            [Tape.libraries] INNER JOIN
                         [Tape.library_names] ON [Tape.libraries].id = [Tape.library_names].id INNER JOIN
                         [Tape.library_devices] ON [Tape.libraries].id = [Tape.library_devices].library_id INNER JOIN
                         [Tape.changers] ON [Tape.library_devices].device_id = [Tape.changers].device_id INNER JOIN
                         [Tape.devices] ON [Tape.library_devices].device_id = [Tape.devices].id
WHERE        [Tape.libraries].type = 0
UNION
SELECT      [Tape.libraries].id, 
			[Tape.library_names].name, 
			[Tape.libraries].usn, 
			[Tape.libraries].type, 
			[Tape.tape_drives].device_id, 
			0, 
			[Tape.tape_drives].model,
			[Tape.libraries].enabled, 
			[Tape.devices].[state] 
FROM            [Tape.libraries] INNER JOIN
                         [Tape.library_names] ON [Tape.libraries].id = [Tape.library_names].id INNER JOIN
                         [Tape.library_devices] ON [Tape.libraries].id = [Tape.library_devices].library_id INNER JOIN
                         [Tape.tape_drives] ON [Tape.library_devices].device_id = [Tape.tape_drives].device_id INNER JOIN
                         [Tape.devices] ON [Tape.library_devices].device_id = [Tape.devices].id
WHERE        [Tape.libraries].type = 1
GO


--------------------------------------------------------------------------------
-- Tape.add_backup_set
PRINT N'Creating [dbo].[Tape.add_backup_set]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Tape.add_backup_set]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Tape.add_backup_set]
GO
CREATE PROCEDURE [dbo].[Tape.add_backup_set]
	@media_family_id int
	,@number int
	,@write_time datetime
	,@name nvarchar(MAX)
	,@backup_id uniqueidentifier
	,@physical_block_address bigint
	,@pending bit
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @id AS UNIQUEIDENTIFIER
	SET @id = NEWID()

	DECLARE @usn bigint
	EXEC [dbo].[IncrementUsn] @usn OUTPUT

	INSERT INTO [Tape.backup_sets] (
		id
		,media_family_id
		,number
		,write_time
		,name
		,backup_id
		,physical_block_address
		,pending
		,usn
	)
	VALUES (
		@id
		,@media_family_id
		,@number
		,@write_time
		,@name
		,@backup_id
		,@physical_block_address
		,@pending
		,@usn
	)
		
	SELECT @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_files_by_directory_id_and_backup_set_id
PRINT N'Creating [dbo].[Tape.get_all_files_by_directory_id_and_backup_set_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_files_by_directory_id_and_backup_set_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_files_by_directory_id_and_backup_set_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_files_by_directory_id_and_backup_set_id]
	@directory_id bigint
	,@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT DISTINCT
		[Tape.files].* 
	FROM 
		[Tape.files] 
		INNER JOIN [Tape.file_versions] ON [Tape.files].id = [Tape.file_versions].[file_id] 
	WHERE 
		[Tape.files].directory_id = @directory_id AND
		[Tape.file_versions].backup_set_id = @backup_set_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_files_by_directory_id_and_backup_id
PRINT N'Creating [dbo].[Tape.get_all_files_by_directory_id_and_backup_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_files_by_directory_id_and_backup_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_files_by_directory_id_and_backup_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_files_by_directory_id_and_backup_id]
	@directory_id bigint
	,@backup_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT DISTINCT
		[Tape.files].* 
	FROM 
		[Tape.files] 
		INNER JOIN [Tape.file_versions] ON [Tape.files].id = [Tape.file_versions].[file_id] 
		INNER JOIN [Tape.backup_sets] ON [Tape.file_versions].backup_set_id = [Tape.backup_sets].id
	WHERE 
		[Tape.files].directory_id = @directory_id AND
		[Tape.backup_sets].backup_id = @backup_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_files_by_backup_id
PRINT N'Creating [dbo].[Tape.get_all_files_by_backup_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_files_by_backup_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_files_by_backup_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_files_by_backup_id]
	@backup_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT DISTINCT
		[Tape.files].* 
	FROM 
		[Tape.files] 
		INNER JOIN [Tape.file_versions] ON [Tape.files].id = [Tape.file_versions].[file_id] 
		INNER JOIN [Tape.backup_sets] ON [Tape.file_versions].backup_set_id = [Tape.backup_sets].id
	WHERE 
		[Tape.backup_sets].backup_id = @backup_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_directories_by_parent_id_and_tape_medium_id
PRINT N'Creating [dbo].[Tape.get_all_directories_by_parent_id_and_tape_medium_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_directories_by_parent_id_and_tape_medium_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_directories_by_parent_id_and_tape_medium_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_directories_by_parent_id_and_tape_medium_id]
	@parent_id bigint
	,@tape_medium_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT DISTINCT
		d.* 
	FROM 
		[Tape.directories] d
		INNER JOIN [Tape.directory_versions] dv ON d.id = dv.directory_id
		INNER JOIN [Tape.backup_sets] bs ON dv.backup_set_id = bs.id
		INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND dv.media_sequence_number = tm.media_sequence_number
	WHERE 
		d.parent_id = @parent_id AND
		tm.id = @tape_medium_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_directories_by_parent_id_and_backup_set_id
PRINT N'Creating [dbo].[Tape.get_all_directories_by_parent_id_and_backup_set_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_directories_by_parent_id_and_backup_set_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_directories_by_parent_id_and_backup_set_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_directories_by_parent_id_and_backup_set_id]
	@parent_id bigint
	,@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT DISTINCT
		[Tape.directories].* 
	FROM 
		[Tape.directories] 
		INNER JOIN [Tape.directory_versions] ON [Tape.directories].id = [Tape.directory_versions].directory_id
	WHERE 
		[Tape.directories].parent_id = @parent_id AND
		[Tape.directory_versions].backup_set_id = @backup_set_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_directories_by_parent_id_and_backup_id
PRINT N'Creating [dbo].[Tape.get_all_directories_by_parent_id_and_backup_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_directories_by_parent_id_and_backup_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_directories_by_parent_id_and_backup_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_directories_by_parent_id_and_backup_id]
	@parent_id bigint
	,@backup_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT DISTINCT
		[Tape.directories].* 
	FROM 
		[Tape.directories] 
		INNER JOIN [Tape.directory_versions] ON [Tape.directories].id = [Tape.directory_versions].directory_id
		INNER JOIN [Tape.backup_sets] ON [Tape.directory_versions].backup_set_id = [Tape.backup_sets].id
	WHERE 
		[Tape.directories].parent_id = @parent_id AND
		[Tape.backup_sets].backup_id = @backup_id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_file_version
PRINT N'Creating [dbo].[Tape.delete_file_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_file_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_file_version]
GO
CREATE PROCEDURE [dbo].[Tape.delete_file_version]
	@file_id bigint
	,@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DELETE FROM [Tape.file_versions] WHERE [file_id] = @file_id AND backup_set_id = @backup_set_id
END
GO


--------------------------------------------------------------------------------
-- Tape.add_directory_version
PRINT N'Creating [dbo].[Tape.add_directory_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Tape.add_directory_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Tape.add_directory_version]
GO
CREATE PROCEDURE [dbo].[Tape.add_directory_version]
	@directory_id bigint
	,@backup_set_id uniqueidentifier
	,@creation_time datetime
	,@last_write_time datetime
	,@attributes int
	,@format_logical_address bigint
	,@media_sequence_number int
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.directory_versions] (
		directory_id
		,backup_set_id
		,creation_time
		,last_write_time
		,attributes
		,format_logical_address
		,media_sequence_number
	)
	VALUES (
		@directory_id
		,@backup_set_id
		,@creation_time
		,@last_write_time
		,@attributes
		,@format_logical_address
		,@media_sequence_number
	)
END
GO


--------------------------------------------------------------------------------
-- Tape.add_file_version
PRINT N'Creating [dbo].[Tape.add_file_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_file_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_file_version]
GO
CREATE PROCEDURE [dbo].[Tape.add_file_version]
	@file_id bigint
	,@backup_set_id uniqueidentifier
	,@creation_time datetime
	,@last_write_time datetime
	,@size bigint
	,@attributes int
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.file_versions] (
		[file_id]
		,backup_set_id
		,creation_time
		,last_write_time
		,size
		,attributes
	)
	VALUES (
		@file_id
		,@backup_set_id
		,@creation_time
		,@last_write_time
		,@size
		,@attributes
	)
END
GO


--------------------------------------------------------------------------------
-- Tape.search_directories
PRINT N'Creating [dbo].[Tape.search_directories]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.search_directories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.search_directories]
GO
CREATE PROCEDURE [dbo].[Tape.search_directories]
	@pattern nvarchar(800)
	,@parent_ids xml
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @hdocument int
	EXEC sp_xml_preparedocument @hdocument OUTPUT, @parent_ids

	DECLARE @ids_count int	
	SELECT @ids_count = COUNT(*) FROM OPENXML(@hdocument, N'/ROOT/id', 1) WITH (id bigint)
	
	IF @ids_count > 0
		SELECT DISTINCT id 
		FROM [Tape.directories]
		INNER JOIN [Tape.directory_versions] ON [Tape.directories].id = [Tape.directory_versions].directory_id 
		WHERE name LIKE @pattern ESCAPE '\' AND parent_id IN (SELECT id FROM OPENXML(@hdocument, N'/ROOT/id', 1) WITH (id bigint))
	ELSE
		SELECT DISTINCT id 
		FROM [Tape.directories]
		INNER JOIN [Tape.directory_versions] ON [Tape.directories].id = [Tape.directory_versions].directory_id 
		WHERE name LIKE @pattern ESCAPE '\'

	EXEC sp_xml_removedocument @hdocument
END
GO


--------------------------------------------------------------------------------
-- Tape.get_directory_version
PRINT N'Creating [dbo].[Tape.get_directory_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_directory_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_directory_version]
GO
CREATE PROCEDURE [dbo].[Tape.get_directory_version]
	@directory_id bigint
	,@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		* 
	FROM 
		[Tape.directory_versions] 
	WHERE 
		directory_id = @directory_id AND
		backup_set_id = @backup_set_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_nearest_file_version
PRINT N'Creating [dbo].[Tape.get_nearest_file_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_nearest_file_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_nearest_file_version]
GO
CREATE PROCEDURE [dbo].[Tape.get_nearest_file_version]
	@file_id bigint,
	@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1
		[Tape.file_versions].*
	FROM
		[Tape.file_versions]
	WHERE 
		[Tape.file_versions].[file_id] = @file_id AND
		[Tape.file_versions].backup_set_id IN (SELECT id FROM [Tape.backup_sets] WHERE write_time <= (SELECT write_time FROM [Tape.backup_sets] WHERE id = @backup_set_id))
	ORDER BY 
		[Tape.file_versions].last_write_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_nearest_directory_version
PRINT N'Creating [dbo].[Tape.get_nearest_directory_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_nearest_directory_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_nearest_directory_version]
GO
CREATE PROCEDURE [dbo].[Tape.get_nearest_directory_version]
	@directory_id bigint,
	@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1
		[Tape.directory_versions].*
	FROM
		[Tape.directory_versions]
	WHERE 
		[Tape.directory_versions].directory_id = @directory_id AND
		[Tape.directory_versions].backup_set_id IN (SELECT id FROM [Tape.backup_sets] WHERE write_time <= (SELECT write_time FROM [Tape.backup_sets] WHERE id = @backup_set_id))
	ORDER BY 
		[Tape.directory_versions].last_write_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup_sets_by_file_id
PRINT N'Creating [dbo].[Tape.get_backup_sets_by_file_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup_sets_by_file_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup_sets_by_file_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup_sets_by_file_id]
	@file_id bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		[Tape.backup_sets].* 
	FROM 
		[Tape.backup_sets] INNER JOIN 
		[Tape.file_versions] ON [Tape.backup_sets].id = [Tape.file_versions].backup_set_id
	WHERE 
		[Tape.file_versions].[file_id] = @file_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup_sets_by_directory_id
PRINT N'Creating [dbo].[Tape.get_backup_sets_by_directory_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup_sets_by_directory_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup_sets_by_directory_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup_sets_by_directory_id]
	@directory_id bigint
AS
BEGIN
	SET NOCOUNT ON;
    
	WITH subdirectories(id) 
	AS 
	(
		SELECT id
		FROM [Tape.directories]
		WHERE id = @directory_id
		UNION ALL
		SELECT [Tape.directories].id FROM [Tape.directories] INNER JOIN subdirectories ON [Tape.directories].parent_id = subdirectories.id
	)
	
	SELECT DISTINCT 
		[Tape.backup_sets].* 
	FROM 
		subdirectories 
--		INNER JOIN [Tape.files] ON subdirectories.id = [Tape.files].directory_id 
--		INNER JOIN [Tape.file_versions] ON [Tape.files].id = [Tape.file_versions].[file_id]
--		INNER JOIN [Tape.backup_sets] ON [Tape.file_versions].backup_set_id = [Tape.backup_sets].id
		INNER JOIN [Tape.directory_versions] ON subdirectories.id = [Tape.directory_versions].directory_id 
		INNER JOIN [Tape.backup_sets] ON [Tape.directory_versions].backup_set_id = [Tape.backup_sets].id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_last_file_version_by_backup
PRINT N'Creating [dbo].[Tape.get_last_file_version_by_backup]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_last_file_version_by_backup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_last_file_version_by_backup]
GO
CREATE PROCEDURE [dbo].[Tape.get_last_file_version_by_backup]
	@file_id bigint
	,@backup_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1 
		[Tape.file_versions].* 
	FROM 
		[Tape.file_versions] 
		INNER JOIN [Tape.backup_sets] ON [Tape.file_versions].backup_set_id = [Tape.backup_sets].id
	WHERE
		[Tape.file_versions].[file_id] = @file_id AND
		[Tape.backup_sets].backup_id = @backup_id
	ORDER BY
		[Tape.backup_sets].write_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_last_file_version
PRINT N'Creating [dbo].[Tape.get_last_file_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_last_file_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_last_file_version]
GO
CREATE PROCEDURE [dbo].[Tape.get_last_file_version]
	@file_id bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1 * 
	FROM 
		[Tape.file_versions] 
		INNER JOIN [Tape.backup_sets] ON [Tape.file_versions].backup_set_id = [Tape.backup_sets].id
	WHERE
		[Tape.file_versions].[file_id] = @file_id
	ORDER BY
		[Tape.backup_sets].write_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_last_directory_version_by_backup
PRINT N'Creating [dbo].[Tape.get_last_directory_version_by_backup]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_last_directory_version_by_backup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_last_directory_version_by_backup]
GO
CREATE PROCEDURE [dbo].[Tape.get_last_directory_version_by_backup]
	@directory_id bigint
	,@backup_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1 
		[Tape.directory_versions].* 
	FROM 
		[Tape.directory_versions] 
		INNER JOIN [Tape.backup_sets] ON [Tape.directory_versions].backup_set_id = [Tape.backup_sets].id
	WHERE
		[Tape.directory_versions].directory_id = @directory_id AND 
		[Tape.backup_sets].backup_id = @backup_id
	ORDER BY
		[Tape.backup_sets].write_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_last_directory_version
PRINT N'Creating [dbo].[Tape.get_last_directory_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_last_directory_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_last_directory_version]
GO
CREATE PROCEDURE [dbo].[Tape.get_last_directory_version]
	@directory_id bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP 1 * 
	FROM 
		[Tape.directory_versions] 
		INNER JOIN [Tape.backup_sets] ON [Tape.directory_versions].backup_set_id = [Tape.backup_sets].id
	WHERE
		[Tape.directory_versions].directory_id = @directory_id
	ORDER BY
		[Tape.backup_sets].write_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_versions_by_backup_id
PRINT N'Creating [dbo].[Tape.get_file_versions_by_backup_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_versions_by_backup_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_versions_by_backup_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_versions_by_backup_id]
	@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		* 
	FROM 
		[Tape.file_versions] 
	WHERE
		backup_set_id = @backup_set_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_version
PRINT N'Creating [dbo].[Tape.get_file_version]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_version]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_version]
	@file_id bigint
	,@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		* 
	FROM 
		[Tape.file_versions] 
	WHERE 
		[file_id] = @file_id AND
		backup_set_id = @backup_set_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_parts_by_tape_medium_id
PRINT N'Creating [dbo].[Tape.get_file_parts_by_tape_medium_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_parts_by_tape_medium_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_parts_by_tape_medium_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_parts_by_tape_medium_id]
	@tape_medium_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		* 
	FROM
		[Tape.file_parts] fp
		INNER JOIN [Tape.backup_sets] bs ON fp.backup_set_id = bs.id
		INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND fp.media_sequence_number = tm.media_sequence_number
	WHERE
		tm.id = @tape_medium_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_parts_by_backup_set_id
PRINT N'Creating [dbo].[Tape.get_file_parts_by_backup_set_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_parts_by_backup_set_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_parts_by_backup_set_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_parts_by_backup_set_id]
	@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT
		*
	FROM
		[Tape.file_parts]
	WHERE
		[Tape.file_parts].backup_set_id = @backup_set_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_parts
PRINT N'Creating [dbo].[Tape.get_file_parts]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_parts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_parts]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_parts]
	@file_id bigint 
	,@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT
		[Tape.file_parts].*
	FROM
		[Tape.file_parts]
	WHERE
		[Tape.file_parts].file_id = @file_id AND
		[Tape.file_parts].backup_set_id = @backup_set_id
	ORDER BY
		[Tape.file_parts].media_sequence_number ASC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_part
PRINT N'Creating [dbo].[Tape.get_file_part]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_part]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_part]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_part]
	@file_id bigint
	,@backup_set_id uniqueidentifier
	,@media_sequence_number int
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		* 
	FROM
		[Tape.file_parts]
	WHERE
		file_id = @file_id AND
		backup_set_id = @backup_set_id AND
		media_sequence_number = @media_sequence_number
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_files_by_directory_id_and_tape_medium_id
PRINT N'Creating [dbo].[Tape.get_all_files_by_directory_id_and_tape_medium_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_files_by_directory_id_and_tape_medium_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_files_by_directory_id_and_tape_medium_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_files_by_directory_id_and_tape_medium_id]
	@directory_id bigint
	,@tape_medium_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT DISTINCT
		f.* 
	FROM 
		[Tape.files] f 
		INNER JOIN [Tape.file_versions] fv ON f.id = fv.file_id
		INNER JOIN [Tape.file_parts] fp ON fv.file_id = fp.file_id AND fv.backup_set_id = fv.backup_set_id
		INNER JOIN [Tape.backup_sets] bs ON fp.backup_set_id = bs.id
		INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND fp.media_sequence_number = tm.media_sequence_number
	WHERE 
		f.directory_id = @directory_id AND
		tm.id = @tape_medium_id
END
GO


--------------------------------------------------------------------------------
-- Tape.cleanup_incomplete_oibs
PRINT N'Creating [dbo].[Tape.cleanup_incomplete_oibs]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.cleanup_incomplete_oibs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.cleanup_incomplete_oibs]
GO
CREATE PROCEDURE [dbo].[Tape.cleanup_incomplete_oibs] 
	@tape_medium_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	CREATE TABLE #oibs 
	(
		id uniqueidentifier, 
		link_id uniqueidentifier, 
		parent_id uniqueidentifier
	)

	INSERT INTO #oibs
	SELECT o.id, o.link_id, o.parent_id
	FROM [Tape.tape_mediums] tm
		INNER JOIN [Tape.backup_sets] bs ON tm.media_family_id = bs.media_family_id
		INNER JOIN [Tape.file_parts] fp ON tm.media_sequence_number = fp.media_sequence_number AND bs.id = fp.backup_set_id
		INNER JOIN [Tape.storages] s ON bs.id = s.backup_set_id AND fp.file_id = s.file_id
		INNER JOIN [Backup.Model.OIBs] o ON s.tape_storage_id = o.storage_id
	WHERE 
		tm.id = @tape_medium_id 

	WHILE @@RowCount > 0
	BEGIN 
		INSERT INTO #oibs
		SELECT id, link_id, parent_id
		FROM [Backup.Model.OIBs]
		WHERE 
			id NOT IN (SELECT id FROM #oibs) AND 
			(
				link_id IN (SELECT id FROM #oibs) OR 
				parent_id IN (SELECT id FROM #oibs) 
--				OR 
--				id IN (SELECT link_id FROM #oibs) OR 
--				id IN (SELECT parent_id FROM #oibs)
			)
	END

	DELETE FROM [Backup.Model.OIBs] WHERE id IN (SELECT id FROM #oibs)

	DECLARE @id uniqueidentifier, @usn bigint
	DECLARE del CURSOR FOR SELECT id FROM #oibs
	OPEN del

	FETCH NEXT FROM del INTO @id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC [dbo].[IncrementUsn] @usn OUTPUT
		EXEC [dbo].[InsertTombStone] 'Backup.Model.OIBs', @id, @usn

		FETCH NEXT FROM del INTO @id
	END

	CLOSE del
	DEALLOCATE del
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_tape_medium_from_catalogue
PRINT N'Creating [dbo].[Tape.delete_tape_medium_from_catalogue]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_tape_medium_from_catalogue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_tape_medium_from_catalogue]
GO
CREATE PROCEDURE [dbo].[Tape.delete_tape_medium_from_catalogue]
	@tape_medium_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	--exec [Tape.cleanup_incomplete_oibs] @tape_medium_id = @tape_medium_id
	
	DECLARE @id uniqueidentifier, @usn bigint
	
	-- clear directory versions
	DELETE dv
	FROM [Tape.directory_versions] dv 
	INNER JOIN [Tape.backup_sets] bs ON dv.backup_set_id = bs.id
	INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND dv.media_sequence_number = tm.media_sequence_number
	WHERE tm.id = @tape_medium_id
	
	-- build deleted file versions table
	SELECT DISTINCT fp.file_id, bs.id backup_set_id
	INTO #parts
	FROM [Tape.file_parts] fp
	INNER JOIN [Tape.backup_sets] bs ON fp.backup_set_id = bs.id
	INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND fp.media_sequence_number = tm.media_sequence_number
	WHERE tm.id = @tape_medium_id
	
	CREATE INDEX [file_id_backup_set_id] ON #parts (file_id, backup_set_id)
	
	-- clear oibs
	UPDATE co SET co.link_id = NULL
	FROM 
		[Backup.Model.OIBs] po
		INNER JOIN [Backup.Model.OIBs] co ON po.id = co.link_id
		INNER JOIN [Tape.storages] ts ON po.storage_id = ts.tape_storage_id 
		INNER JOIN #parts p ON ts.file_id = p.file_id AND ts.backup_set_id = p.backup_set_id

	SELECT o.id AS id, o.usn AS usn
	INTO #oibIds
	FROM
		[Backup.Model.OIBs] o 
		INNER JOIN [Tape.storages] ts ON o.storage_id = ts.tape_storage_id 
		INNER JOIN #parts p ON ts.file_id = p.file_id AND ts.backup_set_id = p.backup_set_id

	DELETE FROM [Backup.Model.OIBs] WHERE id IN (SELECT id FROM #oibIds)

	DECLARE del CURSOR FOR SELECT id FROM #oibIds
	OPEN del

	FETCH NEXT FROM del INTO @id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC [dbo].[IncrementUsn] @usn OUTPUT
			EXEC [dbo].[InsertTombStone] 'Backup.Model.OIBs', @id, @usn
		END TRY
		BEGIN CATCH END CATCH
		FETCH NEXT FROM del INTO @id
	END

	CLOSE del
	DEALLOCATE del

	-- clear storages
	SELECT s.id AS id, s.usn AS usn
	INTO #storageIds
	FROM 
		[Backup.Model.Storages] s 
		INNER JOIN [Tape.storages] ts ON s.id = ts.tape_storage_id 
		INNER JOIN #parts p ON ts.file_id = p.file_id AND ts.backup_set_id = p.backup_set_id

	DELETE FROM [Backup.Model.Storages] WHERE id IN (SELECT id FROM #storageIds)

	DECLARE del CURSOR FOR SELECT id FROM #storageIds
	OPEN del

	FETCH NEXT FROM del INTO @id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC [dbo].[IncrementUsn] @usn OUTPUT
			EXEC [dbo].[InsertTombStone] 'Backup.Model.Storages', @id, @usn
		END TRY
		BEGIN CATCH END CATCH
		FETCH NEXT FROM del INTO @id
	END

	CLOSE del
	DEALLOCATE del

	-- clear points
	UPDATE cp SET cp.link_id = NULL
	FROM 
		[Backup.Model.Points] pp
		INNER JOIN [Backup.Model.Points] cp ON pp.id = cp.link_id
		INNER JOIN [Backup.Model.Backups] b ON pp.backup_id = b.id
	WHERE 
		b.target_type = 4 AND
		pp.id NOT IN 
		(
			SELECT DISTINCT point_id FROM [Backup.Model.OIBs]
		)

	SELECT p.id AS id, p.usn AS usn
	INTO #pointIds
	FROM 
		[Backup.Model.Points] p
		INNER JOIN [Backup.Model.Backups] b ON p.backup_id = b.id
	WHERE 
		b.target_type = 4 AND
		p.id NOT IN 
		(
			SELECT DISTINCT point_id FROM [Backup.Model.OIBs]
		)

	DELETE FROM [Backup.Model.Points] WHERE id IN (SELECT id FROM #pointIds)

	DECLARE del CURSOR FOR SELECT id FROM #pointIds
	OPEN del

	FETCH NEXT FROM del INTO @id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC [dbo].[IncrementUsn] @usn OUTPUT
			EXEC [dbo].[InsertTombStone] 'Backup.Model.Points', @id, @usn
		END TRY
		BEGIN CATCH END CATCH
		FETCH NEXT FROM del INTO @id
	END

	CLOSE del
	DEALLOCATE del
	
	-- clear backups
	SELECT id AS id, usn AS usn
	INTO #backupIds
	FROM [Backup.Model.Backups]
	WHERE
		target_type = 4 AND 
		id NOT IN 
		(
			SELECT DISTINCT backup_id FROM [Backup.Model.Points]
			UNION SELECT backup_id FROM [Backup.Model.Storages]
		)

	DELETE FROM [Backup.Model.Backups] WHERE id IN (SELECT id FROM #backupIds)

	DECLARE del CURSOR FOR SELECT id FROM #backupIds
	OPEN del

	FETCH NEXT FROM del INTO @id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC [dbo].[IncrementUsn] @usn OUTPUT
			EXEC [dbo].[InsertTombStone] 'Backup.Model.Backups', @id, @usn
		END TRY
		BEGIN CATCH END CATCH
		FETCH NEXT FROM del INTO @id
	END

	CLOSE del
	DEALLOCATE del

	-- clear file versions
	DELETE [Tape.file_versions] 
	FROM 
		[Tape.file_versions] fv
		INNER JOIN #parts p ON fv.file_id = p.file_id AND fv.backup_set_id = p.backup_set_id

	-- clear mapping
	DELETE FROM [Tape.vm_objects]
	WHERE 
		tape_object_id NOT IN 
		(
			SELECT DISTINCT id FROM [Backup.Model.OIBs]
			UNION SELECT id FROM [Backup.Model.Storages]
			UNION SELECT id FROM [Backup.Model.Points]
			UNION SELECT id FROM [Backup.Model.Backups]
		)
	
	-- clear files
	DELETE FROM [Tape.files] 
	WHERE 
		id NOT IN 
		(
			SELECT DISTINCT [file_id] FROM [Tape.file_versions]
		)
	
	-- clear directories
	WHILE 1=1
	BEGIN
		DELETE FROM [Tape.directories]
		WHERE 
			NOT EXISTS (SELECT TOP(1) * FROM [Tape.directories] c WHERE [Tape.directories].id = c.parent_id) AND
			NOT EXISTS (SELECT TOP(1) * FROM [Tape.directory_versions] v WHERE [Tape.directories].id = v.directory_id) AND
			NOT EXISTS (SELECT TOP(1) * FROM [Tape.files] f WHERE [Tape.directories].id = f.directory_id)

		IF (@@ROWCOUNT = 0) BREAK
	END
	
	-- clear backup sets
	DELETE FROM [Tape.backup_sets] 
	WHERE
		pending = 0 AND
		id NOT IN 
		(
			SELECT DISTINCT backup_set_id FROM [Tape.file_versions]
			UNION SELECT backup_set_id FROM [Tape.directory_versions]
		)

	-- clear tapes
	UPDATE 
		[Tape.tape_mediums]
	SET 
		media_family_id = NULL, 
		media_sequence_number = NULL, 
		media_time = NULL,
		continuation = 0, 
		is_full = 0, 
		last_write_time = NULL,
		-- media_id = NULL, //Commented by AT - we need media id to be persistent for standalone drives support
		media_label = NULL,
		format_logical_address = 0,
		remaining = capacity,
		retired = 0
	WHERE 
		id = @tape_medium_id

END
GO


--------------------------------------------------------------------------------
-- Tape.add_file_part
PRINT N'Creating [dbo].[Tape.add_file_part]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_file_part]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_file_part]
GO
CREATE PROCEDURE [dbo].[Tape.add_file_part]
	@file_id bigint
	,@backup_set_id uniqueidentifier
	,@format_logical_address bigint
	,@media_sequence_number int
	,@incomplete bit
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [Tape.file_parts] (
		[file_id]
		,backup_set_id
		,format_logical_address
		,media_sequence_number
		,incomplete
	) VALUES (
		@file_id
		,@backup_set_id
		,@format_logical_address
		,@media_sequence_number
		,@incomplete
	)
END
GO


--------------------------------------------------------------------------------
-- Tape.get_host_by_name
PRINT N'Creating [dbo].[Tape.get_host_by_name]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_host_by_name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_host_by_name]
GO
CREATE PROCEDURE [dbo].[Tape.get_host_by_name]
	@name nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.hosts] WHERE name = @name
END
GO


--------------------------------------------------------------------------------
-- Tape.get_volumes
PRINT N'Creating [dbo].[Tape.get_volumes]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_volumes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_volumes]
GO
CREATE PROCEDURE [dbo].[Tape.get_volumes]
	@host_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.directories] WHERE [host_id] = @host_id AND parent_id IS NULL
END
GO


--------------------------------------------------------------------------------
-- Tape.get_all_hosts
PRINT N'Creating [dbo].[Tape.get_all_hosts]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_all_hosts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_all_hosts]
GO
CREATE PROCEDURE [dbo].[Tape.get_all_hosts]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.hosts]
END
GO

--------------------------------------------------------------------------------
-- Tape.[Tape.get_not_empty_hosts]
PRINT N'Creating [dbo].[Tape.get_not_empty_hosts]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_not_empty_hosts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_not_empty_hosts]
GO
CREATE PROCEDURE [dbo].[Tape.get_not_empty_hosts]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT DISTINCT(host.id),host.name FROM [Tape.hosts] host
	INNER JOIN [Tape.directories] dir ON dir.[host_id] = host.[id]
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_medium_by_media_id
PRINT N'Creating [dbo].[Tape.get_tape_medium_by_media_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_medium_by_media_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_medium_by_media_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_medium_by_media_id]
	@library_id uniqueidentifier,
	@media_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.tape_mediums] WHERE media_id = @media_id AND location_library_id = @library_id
END
GO


--------------------------------------------------------------------------------
-- Tape.add_storage
PRINT N'Creating [dbo].[Tape.add_storage]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_storage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_storage]
GO
CREATE PROCEDURE [dbo].[Tape.add_storage]
	@tape_storage_id uniqueidentifier
	,@file_id bigint
	,@backup_set_id uniqueidentifier
	,@media_family_id int
	,@session_number int
	,@format_logical_address bigint
	,@location xml
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO [Tape.storages] (
		tape_storage_id
		,file_id
		,backup_set_id
		,media_family_id
		,session_number
		,format_logical_address
		,location
		)
	VALUES (
		@tape_storage_id
		,@file_id
		,@backup_set_id
		,@media_family_id
		,@session_number
		,@format_logical_address
		,@location
		)
END
GO


--------------------------------------------------------------------------------
-- Tape.get_storage
PRINT N'Creating [dbo].[Tape.get_storage]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_storage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_storage]
GO
CREATE PROCEDURE [dbo].[Tape.get_storage]
	@tape_storage_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM [Tape.storages] WHERE tape_storage_id = @tape_storage_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_first_tape_medium
PRINT N'Creating [dbo].[Tape.get_first_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_first_tape_medium]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_first_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.get_first_tape_medium]
	@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * 
	FROM [Tape.tape_mediums] 
	WHERE id = 
		(
		SELECT TOP 1 tm.id 
		FROM [Tape.file_parts] fp
		INNER JOIN [Tape.backup_sets] bs ON fp.backup_set_id = bs.id
		INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND fp.media_sequence_number = tm.media_sequence_number
		WHERE bs.id = @backup_set_id 
		ORDER BY tm.format_logical_address
		)
END
GO


--------------------------------------------------------------------------------
-- Tape.add_vm_object
PRINT N'Creating [dbo].[Tape.add_vm_object]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_vm_object]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.add_vm_object]
GO
CREATE PROCEDURE [dbo].[Tape.add_vm_object]
	@vm_object_id uniqueidentifier
	,@tape_object_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [Tape.vm_objects] (
		vm_object_id
		,tape_object_id
		)
	VALUES (
		@vm_object_id
		,@tape_object_id
		)
END
GO


--------------------------------------------------------------------------------
-- Tape.get_vm_object_ids
PRINT N'Creating [dbo].[Tape.get_vm_object_ids]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_vm_object_ids]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_vm_object_ids]
GO
CREATE PROCEDURE [dbo].[Tape.get_vm_object_ids]
	@tape_object_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM [Tape.vm_objects] WHERE tape_object_id = @tape_object_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_object_ids
PRINT N'Creating [dbo].[Tape.get_tape_object_ids]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_object_ids]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_object_ids]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_object_ids]
	@vm_object_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM [Tape.vm_objects] WHERE vm_object_id = @vm_object_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_part_by_address
PRINT N'Creating [dbo].[Tape.get_file_part_by_address]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_part_by_address]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_part_by_address]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_part_by_address]
	@media_family_id int
	,@backup_set_number int
	,@format_logical_address bigint
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		[Tape.file_parts].* 
	FROM 
		[Tape.file_parts]
		INNER JOIN [Tape.backup_sets] on [Tape.file_parts].backup_set_id = [Tape.backup_sets].id 
	WHERE 
		[Tape.backup_sets].media_family_id = @media_family_id AND 
		[Tape.backup_sets].number = @backup_set_number AND
		[Tape.file_parts].format_logical_address = @format_logical_address
END
GO


--------------------------------------------------------------------------------
-- Tape.resolve_new_storages
PRINT N'Creating [dbo].[Tape.resolve_new_storages]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.resolve_new_storages]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.resolve_new_storages]
GO
CREATE PROCEDURE [dbo].[Tape.resolve_new_storages]
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE 
		[Tape.storages]
	SET 
		file_id = [Tape.file_parts].file_id, 
		backup_set_id = [Tape.file_parts].backup_set_id
	FROM
		[Tape.storages]
		INNER JOIN [Tape.backup_sets] ON [Tape.storages].media_family_id = [Tape.backup_sets].media_family_id AND [Tape.storages].session_number = [Tape.backup_sets].number
		INNER JOIN [Tape.file_parts] ON [Tape.storages].format_logical_address = [Tape.file_parts].format_logical_address AND [Tape.backup_sets].id = [Tape.file_parts].backup_set_id
	WHERE
		[Tape.storages].file_id IS NULL
END
GO


--------------------------------------------------------------------------------
-- Tape.get_tape_mediums_by_media_family_id
PRINT N'Creating [dbo].[Tape.get_tape_mediums_by_media_family_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_mediums_by_media_family_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_tape_mediums_by_media_family_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_mediums_by_media_family_id]
	@media_family_id int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM [Tape.tape_mediums] WHERE media_family_id = @media_family_id
END
GO


--------------------------------------------------------------------------------
-- Tape.delete_orphan_vm_objects
PRINT N'Creating [dbo].[Tape.delete_orphan_vm_objects]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.delete_orphan_vm_objects]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.delete_orphan_vm_objects]
GO
CREATE PROCEDURE [dbo].[Tape.delete_orphan_vm_objects]
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM [Tape.backups]
	WHERE 
	id NOT IN 
	(
		SELECT id FROM [Backup.Model.Backups]
		UNION
		SELECT '437913A2-1D8E-4d74-BB0D-4FDEC6771A6C'
	)

	DELETE FROM [Tape.vm_objects]
	WHERE tape_object_id NOT IN 
	(
		SELECT id FROM [Backup.Model.Backups]
		UNION SELECT id FROM [Backup.Model.Storages]
		UNION SELECT id FROM [Backup.Model.Points]
		UNION SELECT id FROM [Backup.Model.OIBs]
	)

	DELETE [Tape.file_versions]
	FROM 
		[Tape.file_versions] v
	WHERE NOT EXISTS (SELECT * FROM [Tape.storages] s WHERE v.file_id = s.file_id AND v.backup_set_id = s.backup_set_id)

	DELETE FROM [Tape.storages]
	WHERE tape_storage_id NOT IN 
	(
		SELECT tape_object_id FROM [Tape.vm_objects]
	)
END
GO


--------------------------------------------------------------------------------
-- Tape.report_directory_changes
PRINT N'Creating [dbo].[Tape.get_last_directory_version_by_id_and_media_family_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_last_directory_version_by_id_and_media_family_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_last_directory_version_by_id_and_media_family_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_last_directory_version_by_id_and_media_family_id]
	@directory_id bigint
	,@media_family_id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
		TOP(1) d.*
	FROM 
		[Tape.directory_versions] AS d
		INNER JOIN [Tape.backup_sets] AS b ON d.backup_set_id = b.id
	WHERE 
		d.directory_id = @directory_id AND 
		b.media_family_id = @media_family_id
	ORDER BY 
		b.write_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_last_file_version_by_id_and_media_family_id
PRINT N'Creating [dbo].[Tape.get_last_file_version_by_id_and_media_family_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_last_file_version_by_id_and_media_family_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_last_file_version_by_id_and_media_family_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_last_file_version_by_id_and_media_family_id]
	@file_id bigint
	,@media_family_id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
		TOP(1) f.*
	FROM 
		[Tape.file_versions] AS f
		INNER JOIN [Tape.backup_sets] AS b ON f.backup_set_id = b.id
	WHERE 
		f.file_id = @file_id AND 
		b.media_family_id = @media_family_id
	ORDER BY 
		b.write_time DESC
END
GO


--------------------------------------------------------------------------------
-- Tape.get_directory_by_host_id_and_path
PRINT N'Creating [dbo].[Tape.get_directory_by_host_id_and_path]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_directory_by_host_id_and_path]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_directory_by_host_id_and_path]
GO
CREATE PROCEDURE [dbo].[Tape.get_directory_by_host_id_and_path]
	@host_id uniqueidentifier
	,@root nvarchar(max)
	,@parts xml
	,@add bit
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @id BIGINT
	DECLARE @parent_id BIGINT
	DECLARE @part NVARCHAR(MAX)
	DECLARE @hdocument INT

	IF @root IS NULL
		SELECT @id = id FROM [Tape.directories] WHERE [host_id] = @host_id AND parent_id IS NULL AND name IS NULL
	ELSE
		SELECT @id = id FROM [Tape.directories] WHERE [host_id] = @host_id AND parent_id IS NULL AND name = @root

	IF @id IS NULL
	BEGIN
		INSERT INTO [Tape.directories] ([host_id], parent_id, name) VALUES (@host_id, @parent_id, @root)
		SELECT @id = CAST(SCOPE_IDENTITY() AS BIGINT)
	END

	EXEC sp_xml_preparedocument @hdocument OUTPUT, @parts

	DECLARE part_cursor CURSOR FOR SELECT * FROM OPENXML(@hdocument, N'/ROOT/n', 0) WITH (n NVARCHAR(MAX))
	OPEN part_cursor

	FETCH NEXT FROM part_cursor INTO @part
	WHILE (@@FETCH_STATUS = 0 AND @id IS NOT NULL)
	BEGIN
		SET @parent_id = @id
		SET @id = NULL

		SELECT @id = id FROM [Tape.directories] WHERE parent_id = @parent_id AND name = @part

		if @id IS NULL AND @add <> 0
		BEGIN
			INSERT INTO [Tape.directories] ([host_id], parent_id ,name) VALUES (@host_id, @parent_id, @part)
		
			SELECT @id = CAST(SCOPE_IDENTITY() AS BIGINT)
		END

		FETCH NEXT FROM part_cursor INTO @part
	END

	CLOSE part_cursor
	DEALLOCATE part_cursor

	EXEC sp_xml_removedocument @hdocument

	IF @id IS NOT NULL SELECT * FROM [Tape.directories] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_file_versions_by_file_id
PRINT N'Creating [dbo].[Tape.get_file_versions_by_file_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_file_versions_by_file_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_file_versions_by_file_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_file_versions_by_file_id]
	@file_id bigint
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM [Tape.file_versions] WHERE file_id = @file_id
END
GO


--------------------------------------------------------------------------------
-- Tape.get_directory_versions_by_directory_id
PRINT N'Creating [dbo].[Tape.get_directory_versions_by_directory_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_directory_versions_by_directory_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_directory_versions_by_directory_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_directory_versions_by_directory_id]
	@directory_id bigint
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM [Tape.directory_versions] WHERE directory_id = @directory_id
END
GO

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

	CREATE PROCEDURE [dbo].[Tape.report_search_filesysitems]
		@host_id uniqueidentifier,
		@parent_id bigint,
		@like_str varchar(256)
	AS
	BEGIN
		SET NOCOUNT ON;
		WITH    dirtree (id, parent_id, dirname, level, dirpath) AS 
				(
				SELECT  [id], [parent_id], [name], 1, CAST([name] AS NVARCHAR(1024))
				FROM    [dbo].[Tape.directories]
				WHERE   ((@parent_id IS NOT NULL AND [parent_id] = @parent_id) OR ([parent_id] IS NULL AND @parent_id IS NULL)) AND [host_id] = @host_id
				UNION ALL
				SELECT  child.[id], child.[parent_id], child.[name], level + 1, CAST((dirpath + '/' +child.[name]) AS NVARCHAR(1024)) 
				FROM    [dbo].[Tape.directories] as child
				INNER JOIN dirtree ON child.[parent_id] = dirtree.id
				)

		SELECT f.id, f.name, fver.creation_time, fver.last_write_time, fver.size, d.id as dir_id, dirpath, 0 as type FROM dirtree d 
		left join  [dbo].[Tape.files] f on f.directory_id = d.id
		INNER JOIN [Tape.file_versions] fver ON f.id = fver.[file_id] AND fver.last_write_time = (SELECT MAX(last_write_time) FROM [Tape.file_versions] WHERE [file_id] = f.id)
		WHERE f.name like @like_str
		GROUP BY f.id, f.name, fver.creation_time, fver.last_write_time, fver.size, d.id, dirpath
	END
	
GO

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

	CREATE PROCEDURE [dbo].[Tape.report_search_filesysitems_everywhere]
		@like_str varchar(256)
	AS
	BEGIN
		SET NOCOUNT ON;
		WITH    dirtree (id, parent_id, dirname, level, dirpath) AS 
				(
				SELECT  d.[id], [parent_id], d.[name], 1, CAST(('file:///' + h.name + '/' + d.name) AS NVARCHAR(1024))
				FROM    [Tape.hosts] h INNER JOIN [Tape.directories] d ON h.id = d.host_id 
				WHERE   [parent_id] IS NULL 
				UNION ALL
				SELECT  child.[id], child.[parent_id], child.[name], level + 1, CAST((dirpath + '/' +child.[name]) AS NVARCHAR(1024)) 
				FROM    [dbo].[Tape.directories] as child
				INNER JOIN dirtree ON child.[parent_id] = dirtree.id
				)

		SELECT f.id, f.name, fver.creation_time, fver.last_write_time, fver.size, d.id as dir_id, dirpath, 0 as type FROM dirtree d 
		left join  [dbo].[Tape.files] f on f.directory_id = d.id
		INNER JOIN [Tape.file_versions] fver ON f.id = fver.[file_id] AND fver.last_write_time = (SELECT MAX(last_write_time) FROM [Tape.file_versions] WHERE [file_id] = f.id)
		WHERE f.name like @like_str
		GROUP BY f.id, f.name, fver.creation_time, fver.last_write_time, fver.size, d.id, dirpath
	END
	
GO

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

CREATE PROCEDURE [dbo].[Tape.get_nearest_file_versions]
	@parent_id bigint
	,@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT r.* FROM [Tape.file_versions] r INNER JOIN
	(SELECT 
		fv.file_id, MAX(last_write_time) last_write_time
	FROM 
		[Tape.files] f
		INNER JOIN [Tape.file_versions] fv ON f.id = fv.file_id 
	WHERE 
		f.directory_id = @parent_id AND
		fv.backup_set_id IN (SELECT id FROM [Tape.backup_sets] WHERE write_time <= (SELECT write_time FROM [Tape.backup_sets] WHERE id = @backup_set_id))
	GROUP BY fv.file_id) l ON r.file_id = l.file_id AND r.last_write_time = l.last_write_time
END
GO

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

CREATE PROCEDURE [dbo].[Tape.get_nearest_directory_versions]
	@parent_id bigint
	,@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT r.* FROM [Tape.directory_versions] r INNER JOIN
	(SELECT 
		dv.directory_id, MAX(last_write_time) last_write_time
	FROM 
		[Tape.directories] d
		INNER JOIN [Tape.directory_versions] dv ON d.id = dv.directory_id 
	WHERE 
		d.parent_id = @parent_id AND
		dv.backup_set_id IN (SELECT id FROM [Tape.backup_sets] WHERE write_time <= (SELECT write_time FROM [Tape.backup_sets] WHERE id = @backup_set_id))
	GROUP BY dv.directory_id) l ON r.directory_id = l.directory_id AND r.last_write_time = l.last_write_time
END
GO


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

CREATE PROCEDURE [dbo].[Tape.get_jobs_by_media_pool_id]
	@media_pool_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
		[Tape.jobs].* 
	FROM 
		[Tape.jobs] 
	WHERE full_mediapool_id = @media_pool_id OR incremental_mediapool_id = @media_pool_id
END
GO


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

CREATE PROCEDURE [dbo].[Tape.get_all_backup_sets_for_directory]
	@directory_id BIGINT
AS
BEGIN
	SET NOCOUNT ON;

	CREATE TABLE #backup_set_ids (id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED);

	WITH directories(id)
	AS
	(
		SELECT id
		FROM [Tape.directories]
		WHERE id = @directory_id

		UNION ALL

		SELECT [Tape.directories].id
		FROM [Tape.directories] INNER JOIN directories ON [Tape.directories].parent_id = directories.id
	)

	SELECT DISTINCT bs.*
	FROM 
		directories d 
		INNER JOIN [Tape.directory_versions] dv ON d.id = dv.directory_id
		INNER JOIN [Tape.backup_sets] bs ON bs.id = dv.backup_set_id

	UNION 

	SELECT DISTINCT bs.*
	FROM 
		directories d 
		INNER JOIN [Tape.files] f ON d.id = f.directory_id
		INNER JOIN [Tape.file_versions] fv ON f.id = fv.file_id
		INNER JOIN [Tape.backup_sets] bs ON bs.id = fv.backup_set_id
END
GO


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

CREATE PROCEDURE [dbo].[Tape.delete_library_device]
	@library_id uniqueidentifier
	,@device_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM [Tape.library_devices] WHERE library_id = @library_id AND device_id = @device_id
END
GO


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

CREATE PROCEDURE [dbo].[Tape.get_tape_mediums_by_library_id]
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM [Tape.tape_mediums] WHERE location_library_id = @library_id
END
GO


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

CREATE PROCEDURE [dbo].[Tape.get_new_tape_mediums_by_library_id]
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM [Tape.tape_mediums] WHERE location_library_id = @library_id AND media_pool_id = '00000000-0000-0000-0000-000000000000'
END
GO

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

CREATE PROCEDURE [dbo].[Tape.add_restore_session]
	@id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @usn bigint
	EXEC [dbo].[IncrementUsn] @usn OUTPUT

	INSERT INTO [Backup.Model.RestoreJobSessions] (
		id
		,action
		,usn
		,options
		,initiator_sid 
		,initiator_name
		,reason
		,files_log_xml
		,platform
		,multi_restore_id
		,restore_type
		,oib_id
		,state_xml
		,is_internal
		,oib_display_name
		,oib_creation_time
		)
	VALUES (
		@id
		,0
		,@usn
		,''
		,'00000000-0000-0000-0000-000000000000'
		,''
		,''
		,''
		,5
		,'00000000-0000-0000-0000-000000000000'
		,0
		,'00000000-0000-0000-0000-000000000000'
		,'<StateXml/>'
		,0
		,''
		,'1900-01-01'
		)
END
GO

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

CREATE PROCEDURE [dbo].[Tape.capture_tape_medium] 
	@source_media_pool_id uniqueidentifier
	,@target_media_pool_id uniqueidentifier
	,@tape_drive_address int
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @id uniqueidentifier
	SET @id = NEWID()

	UPDATE [Tape.tape_mediums]
	SET media_pool_id = @target_media_pool_id, lock_id = @id
	WHERE id = 
		(
		SELECT TOP 1 id 
		FROM [Tape.tape_mediums] WITH (TABLOCKX) 
		WHERE media_pool_id = @source_media_pool_id AND cleaner = 0 AND location_type IN (0, 1) AND lock_id IS NULL 
		ORDER BY 
			CASE WHEN location_type = 0 AND location_address = @tape_drive_address THEN 0 ELSE 1 END, 
			CASE WHEN barcode IS NULL THEN 1 ELSE 0 END, 
			barcode
		)

	SELECT @id = id FROM [Tape.tape_mediums] WHERE lock_id = @id
	UPDATE [Tape.tape_mediums] SET lock_id = NULL WHERE id = @id

	SELECT * FROM [Tape.tape_mediums] WHERE id = @id
END
GO

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

CREATE PROCEDURE [dbo].[Tape.get_storages_by_oib]
	@oib_id uniqueidentifier 
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @group_id uniqueidentifier
	DECLARE @num numeric

	SELECT @group_id = group_id, @num = num
	FROM [Backup.Model.Points] 
	WHERE id = (
		SELECT point_id 
		FROM [Backup.Model.OIBs] 
		WHERE id = @oib_id)

	SELECT * 
	FROM [Tape.storages] 
	WHERE tape_storage_id in (
		SELECT DISTINCT storage_id 
		FROM [Backup.Model.Oibs] 
		WHERE point_id in (
			SELECT id 
			FROM [Backup.Model.Points] 
			WHERE group_id = @group_id AND num <= @num))
END
GO


--------------------------------------------------------------------------------
-- [dbo].[Tape.get_tape_mediums_by_file_id_backup_set_id]
PRINT N'Creating [dbo].[Tape.get_tape_mediums_by_file_id_backup_set_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_mediums_by_file_id_backup_set_id]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.get_tape_mediums_by_file_id_backup_set_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_mediums_by_file_id_backup_set_id]
	@file_id bigint,
	@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT tm.* 
	FROM [Tape.file_parts] fp 
	INNER JOIN [Tape.backup_sets] bs ON fp.backup_set_id = bs.id
	INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND fp.media_sequence_number = tm.media_sequence_number
	WHERE fp.file_id = @file_id AND bs.id = @backup_set_id
END

GO

--------------------------------------------------------------------------------
-- [dbo].[Tape.get_tape_mediums_by_directory_id_backup_set_id]
PRINT N'Creating [dbo].[Tape.get_tape_mediums_by_directory_id_backup_set_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_mediums_by_directory_id_backup_set_id]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.get_tape_mediums_by_directory_id_backup_set_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_mediums_by_directory_id_backup_set_id]
	@directory_id bigint,
	@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT tm.* 
	FROM [Tape.directory_versions] dv
	INNER JOIN [Tape.backup_sets] bs ON dv.backup_set_id = bs.id
	INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND dv.media_sequence_number = tm.media_sequence_number
	WHERE dv.directory_id = @directory_id AND bs.id = @backup_set_id
END

GO

--------------------------------------------------------------------------------
-- [dbo].[Tape.get_tape_mediums]
PRINT N'Creating [dbo].[Tape.get_tape_mediums]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_mediums]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.get_tape_mediums]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_mediums]
	@ids XML
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @hdocument INT
	EXEC sp_xml_preparedocument @hdocument OUTPUT, @ids

	SELECT * FROM [Tape.tape_mediums] WHERE id IN (SELECT * FROM OPENXML(@hdocument, N'/ROOT/id', 1) WITH (id UNIQUEIDENTIFIER))

	EXEC sp_xml_removedocument @hdocument
END
GO

--------------------------------------------------------------------------------
-- [dbo].[Tape.update_backup_set_pending]
PRINT N'Creating [dbo].[Tape.update_backup_set_pending]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_backup_set_pending]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.update_backup_set_pending]
GO
CREATE PROCEDURE [dbo].[Tape.update_backup_set_pending]
	@id uniqueidentifier
	,@pending bit
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [dbo].[Tape.backup_sets] SET [pending] = @pending WHERE [id] = @id
END
GO
--------------------------------------------------------------------------------
-- [dbo].[Tape.get_hosts_on_tape_medium]
PRINT N'Creating [dbo].[Tape.get_hosts_on_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_hosts_on_tape_medium]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.get_hosts_on_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.get_hosts_on_tape_medium]
	@medium_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT DISTINCT(h.id), h.name 
	FROM [Tape.hosts] h
	INNER JOIN [Tape.directories] d ON d.[host_id] = h.[id]
	LEFT OUTER JOIN [Tape.directory_versions] dv ON dv.directory_id = d.id
	LEFT OUTER JOIN [Tape.files] f ON f.directory_id = d.id
	LEFT OUTER JOIN [Tape.file_parts] fp ON fp.file_id = f.id 
	LEFT OUTER JOIN [Tape.backup_sets] dbs ON dv.backup_set_id = dbs.id 
	LEFT OUTER JOIN [Tape.tape_mediums] dtm ON dbs.media_family_id = dtm.media_family_id AND dv.media_sequence_number = dtm.media_sequence_number
	LEFT OUTER JOIN [Tape.backup_sets] fbs ON fp.backup_set_id = fbs.id 
	LEFT OUTER JOIN [Tape.tape_mediums] ftm ON fbs.media_family_id = ftm.media_family_id AND fp.media_sequence_number = ftm.media_sequence_number
	WHERE dtm.id = @medium_id OR ftm.id = @medium_id
END
GO

--------------------------------------------------------------------------------
-- [dbo].[Tape.are_file_system_objects_in_between]
PRINT N'Creating [dbo].[Tape.are_file_system_objects_in_between]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.are_file_system_objects_in_between]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.are_file_system_objects_in_between]
GO
CREATE PROCEDURE [dbo].[Tape.are_file_system_objects_in_between]
	@tape_medium_id uniqueidentifier
	,@backup_set_id uniqueidentifier
	,@min bigint
	,@max bigint
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @count bigint

	SELECT @count = COUNT(*)
	FROM [Tape.directory_versions] dv
	INNER JOIN [Tape.backup_sets] bs ON dv.backup_set_id = bs.id
	INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND dv.media_sequence_number = tm.media_sequence_number
	WHERE 
		tm.id = @tape_medium_id AND
		bs.id = @backup_set_id AND
		dv.format_logical_address > @min AND
		dv.format_logical_address < @max

	IF (@count = 0)
	BEGIN
		SELECT @count = COUNT(*)
		FROM [Tape.file_parts] fp
		INNER JOIN [Tape.backup_sets] bs ON fp.backup_set_id = bs.id
		INNER JOIN [Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND fp.media_sequence_number = tm.media_sequence_number
		WHERE 
			tm.id = @tape_medium_id AND
			bs.id = @backup_set_id AND
			fp.format_logical_address > @min AND
			fp.format_logical_address < @max
	END

	SELECT CASE WHEN @count = 0 THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END AS result
END
GO

--------------------------------------------------------------------------------
-- [dbo].[Tape.add_media_family]
PRINT N'Creating [dbo].[Tape.add_media_family]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.add_media_family]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.add_media_family]
GO
CREATE PROCEDURE [Tape.add_media_family]
	@id int
	,@name nvarchar(MAX)
	,@format_logical_address_size int
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [Tape.media_families] (
		id
		,name
		,format_logical_address_size
	)
	VALUES (
		@id
		,@name
		,@format_logical_address_size
	)
END
GO

--------------------------------------------------------------------------------
-- [dbo].[Tape.get_media_family]
PRINT N'Creating [dbo].[Tape.get_media_family]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_media_family]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.get_media_family]
GO
CREATE PROCEDURE [Tape.get_media_family]
	@id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM [Tape.media_families] WHERE id = @id
END
GO


--------------------------------------------------------------------------------
-- [dbo].[Tape.get_minimum_media_sequence_number]
PRINT N'Creating [dbo].[Tape.get_minimum_media_sequence_number]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_minimum_media_sequence_number]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.get_minimum_media_sequence_number]
GO
CREATE PROCEDURE [Tape.get_minimum_media_sequence_number]
	@backup_set_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT MIN(media_sequence_number) media_sequence_number
	FROM 
	(
		SELECT DISTINCT media_sequence_number
		FROM [Tape.directory_versions]
		WHERE backup_set_id = @backup_set_id
		UNION SELECT media_sequence_number
		FROM [Tape.file_parts]
		WHERE backup_set_id = @backup_set_id
	) m
END
GO

--------------------------------------------------------------------------------
-- [dbo].[Tape.get_tape_drive_by_library_id_and_address]
PRINT N'Creating [dbo].[Tape.get_tape_drive_by_library_id_and_address]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_tape_drive_by_library_id_and_address]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.get_tape_drive_by_library_id_and_address]
GO
CREATE PROCEDURE [dbo].[Tape.get_tape_drive_by_library_id_and_address]
	@library_id uniqueidentifier
	,@address int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
		[Tape.tape_drives].* 
	FROM 
		[Tape.tape_drives] 
		INNER JOIN [Tape.library_devices] ON [Tape.tape_drives].device_id = [Tape.library_devices].device_id
	WHERE
		[Tape.library_devices].library_id = @library_id AND
		[address] = @address
END
GO


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

create proc [dbo].[Tape.GetBackupSetsByDirIdIgnoreSubdirs]
	@directory_id bigint
as
begin
	set nocount on;
	select * from [Tape.backup_sets] bs 
	join 
	(
		select distinct dv.backup_set_id 
		from [Tape.directories] d 
		join [Tape.directory_versions] dv 
		on d.id = dv.directory_id
		where d.id = @directory_id  
	) 
	ids on bs.id = ids.backup_set_id
end
go


--------------------------------------------------------------------------------
-- [dbo].[Tape.update_file_part_incomplete]
PRINT N'Creating [dbo].[Tape.update_file_part_incomplete]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.update_file_part_incomplete]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.update_file_part_incomplete]
GO
CREATE PROCEDURE [Tape.update_file_part_incomplete]
	@file_id bigint
	,@backup_set_id uniqueidentifier
	,@media_sequence_number int
	,@incomplete bit
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [Tape.file_parts] 
	SET incomplete = @incomplete 
	WHERE 
		file_id = @file_id AND 
		backup_set_id = @backup_set_id AND
		media_sequence_number = @media_sequence_number
END
GO

-- [dbo].[Tape.GetLibraryState]
PRINT N'Creating [dbo].[Tape.GetLibraryState]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.GetLibraryState]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.GetLibraryState]
GO
create proc [dbo].[Tape.GetLibraryState]
	@lib_id uniqueidentifier
as
begin
	set nocount on;
	
	select top(1) devs.state 
    from [Tape.libraries] libs 
	     join
         [Tape.library_devices] lib_devs on libs.id = lib_devs.library_id 
		 join
         [Tape.devices] devs on lib_devs.device_id = devs.id
    where libs.id = @lib_id
end
go

-- [dbo].[Tape.GetOnlineLibraries]
PRINT N'Creating [dbo].[Tape.GetOnlineLibraries]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.GetOnlineLibraries]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.GetOnlineLibraries]
GO
create proc [dbo].[Tape.GetOnlineLibraries]
as
begin
	set nocount on;
	select 
	     * 
	from 
		 [Tape.libraries] libs
	     join
		 (
			 select distinct libs.id 
		  	 from [Tape.libraries] libs 
			 join [Tape.library_devices] lib_devs on libs.id = lib_devs.library_id 
			 join [Tape.devices] devs on lib_devs.device_id = devs.id
			 where devs.state = 0
		 ) l on libs.id = l.id

end

go

-- [dbo].[Tape.GetOnlineLibraries]
PRINT N'Creating [dbo].[Tape.GetUserMediaPoolsDescriptions]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.GetUserMediaPoolsDescriptions]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.GetUserMediaPoolsDescriptions]
go
create proc [dbo].[Tape.GetUserMediaPoolsDescriptions]
as
begin
	set nocount on;
	select 
		 mp.id
		,mp.name
		,l.lib_name
	from
		[Tape.media_pools] mp
	join
	(
		select distinct 
			l.id [lib_id], c.vendor_id + ' ' + c.product_id + ' ' + c.revision as [lib_name]
		from 
			[Tape.changers] c
		join
			[Tape.libraries] l
		join
			[Tape.devices] d 
		join 
			[Tape.library_devices] ld 
		on 
			d.id = ld.device_id
		on 
			l.id = ld.library_id
		on
			c.device_id = d.id
		where l.type = 0

		union 

		select distinct 
			lib.id, dr.model
		from 
			[Tape.tape_drives] dr
		join 
			[Tape.devices] dev
		join 
			[Tape.library_devices] ld
		join
			[Tape.libraries] lib
		on 
			ld.library_id = lib.id
		on
			ld.device_id = dev.id 
		on 
			dr.device_id = dev.id
		where lib.type = 1
	) l 
	on 
		mp.library_id = l.lib_id 
	where mp.type = 4
end
go


--------------------------------------------------------------------------------
-- [dbo].[Tape.remove_library_from_infrastructure]
PRINT N'Creating [dbo].[Tape.remove_library_from_infrastructure]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.remove_library_from_infrastructure]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Tape.remove_library_from_infrastructure]
GO
CREATE PROCEDURE [dbo].[Tape.remove_library_from_infrastructure]
	@library_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @id uniqueidentifier

	DECLARE tape_mediums_cursor CURSOR FOR SELECT id FROM [Tape.tape_mediums] WHERE location_library_id = @library_id
	OPEN tape_mediums_cursor

    FETCH NEXT FROM tape_mediums_cursor INTO @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC [Tape.delete_tape_medium_from_catalogue] @tape_medium_id = @id

        FETCH NEXT FROM tape_mediums_cursor INTO @id
    END
	
	CLOSE tape_mediums_cursor
	DEALLOCATE tape_mediums_cursor

	DELETE FROM [Tape.tape_mediums] WHERE location_library_id = @library_id
	DELETE FROM [Tape.media_pools] WHERE library_id = @library_id

	DELETE t FROM [Tape.tape_drives] t INNER JOIN [Tape.library_devices] l ON t.device_id = l.device_id WHERE l.library_id = @library_id
	DELETE c FROM [Tape.changers] c INNER JOIN [Tape.library_devices] l ON c.device_id = l.device_id WHERE l.library_id = @library_id
	DELETE d FROM [Tape.devices] d INNER JOIN [Tape.library_devices] l ON d.id = l.device_id WHERE l.library_id = @library_id
	DELETE FROM [Tape.library_devices] WHERE library_id = @library_id
	DELETE FROM [Tape.libraries] WHERE id = @library_id
END
GO

--------------------------------------------------------------------------------
-- Tape.get_oib_ids_by_tape_medium
PRINT N'Creating [dbo].[Tape.get_oib_ids_by_tape_medium]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_oib_ids_by_tape_medium]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_oib_ids_by_tape_medium]
GO
CREATE PROCEDURE [dbo].[Tape.get_oib_ids_by_tape_medium]
	@tape_medium_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @id uniqueidentifier
	
	-- build file versions table
	SELECT DISTINCT fp.file_id, bs.id backup_set_id
	INTO #parts
	FROM dbo.[Tape.file_parts] fp
	INNER JOIN dbo.[Tape.backup_sets] bs ON fp.backup_set_id = bs.id
	INNER JOIN dbo.[Tape.tape_mediums] tm ON bs.media_family_id = tm.media_family_id AND fp.media_sequence_number = tm.media_sequence_number
	WHERE tm.id = @tape_medium_id
	
	CREATE INDEX [file_id_backup_set_id] ON #parts (file_id, backup_set_id)
	
	-- get oibs
	SELECT o.id AS id
	INTO #oibIds
	FROM
		dbo.[Backup.Model.OIBs] o 
		INNER JOIN dbo.[Tape.storages] ts ON o.storage_id = ts.tape_storage_id 
		INNER JOIN #parts p ON ts.file_id = p.file_id AND ts.backup_set_id = p.backup_set_id

	SELECT * FROM #oibIds

END
GO


--------------------------------------------------------------------------------
-- Tape.get_backup_set_by_tape_medium_id
PRINT N'Creating [dbo].[Tape.get_backup_set_by_tape_medium_id]'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tape.get_backup_set_by_tape_medium_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Tape.get_backup_set_by_tape_medium_id]
GO
CREATE PROCEDURE [dbo].[Tape.get_backup_set_by_tape_medium_id]
	@tape_medium_id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @media_family_id int
	DECLARE @media_sequence_number int

	SELECT @media_family_id = media_family_id, @media_sequence_number = media_sequence_number
	FROM [Tape.tape_mediums] 
	WHERE [Tape.tape_mediums].id = @tape_medium_id

	SELECT DISTINCT *
	INTO #backup_sets_ids
	FROM [Tape.backup_sets]
	WHERE media_family_id = @media_family_id

	CREATE CLUSTERED INDEX [id] ON #backup_sets_ids (id)

	SELECT DISTINCT bsf.* 
	FROM [Tape.file_parts] fp INNER JOIN #backup_sets_ids bsf ON fp.backup_set_id = bsf.id
	WHERE fp.media_sequence_number = @media_sequence_number
	UNION
	SELECT DISTINCT bsd.*
	FROM [Tape.directory_versions] dv INNER JOIN #backup_sets_ids bsd ON dv.backup_set_id = bsd.id
	WHERE dv.media_sequence_number = @media_sequence_number
END
GO

