PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Release 2.5 start'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Increase length on Schema, Table, View and Valueset names'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO
PRINT N'Alter table [mig_director].[Valueset]...';
GO
IF EXISTS (
    SELECT 1
    FROM sys.default_constraints
    WHERE parent_object_id = OBJECT_ID('[mig_director].[Valueset]')
      AND name = 'Valueset_CreationDate'
)
BEGIN
    ALTER TABLE [mig_director].[Valueset] DROP CONSTRAINT [Valueset_CreationDate];
END
GO
IF EXISTS (
    SELECT 1
    FROM sys.default_constraints
    WHERE parent_object_id = OBJECT_ID('[mig_director].[Valueset]')
      AND name = 'DF_Valueset_CreationDate'
)
BEGIN
    ALTER TABLE [mig_director].[Valueset] DROP CONSTRAINT [DF_Valueset_CreationDate];
END
GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [mig_director].[tmp_ms_xx_Valueset] (
    [ItemID]            UNIQUEIDENTIFIER NOT NULL,
    [FullName]          AS               (([SchemaName] + '.') + [Name]),
    [Name]              NVARCHAR (128)   NOT NULL,
    [SchemaName]        VARCHAR (128)     NOT NULL,
    [ProjectID]         UNIQUEIDENTIFIER NOT NULL,
    [ProjectType]       VARCHAR (50)     NOT NULL,
    [ValuesetType]      TINYINT          NOT NULL,
    [ProviderID]        UNIQUEIDENTIFIER NULL,
    [DataServicesUsage] XML              NULL,
    [RowCount]          INT              NULL,
    [CreateTableSql]    VARCHAR (MAX)    NOT NULL,
    [Columns]           XML              NOT NULL,
    [Present]           BIT              NOT NULL,
    [LastUpdateDate]    DATETIME2 (7)    NULL,
    [LastUpdateBy]      VARCHAR (50)     NULL,
    [CreationDate]      DATETIME2 (7)    CONSTRAINT [DF_Valueset_CreationDate] DEFAULT (sysdatetime()) NOT NULL,
    [CreatedBy]         VARCHAR (50)     NOT NULL,
    [ModificationDate]  DATETIME2 (7)    NULL,
    [ModifiedBy]        VARCHAR (50)     NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_Valueset1] PRIMARY KEY CLUSTERED ([ProjectID] ASC, [ItemID] ASC) WITH (FILLFACTOR = 70, PAD_INDEX = ON)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [mig_director].[Valueset])
    BEGIN
        INSERT INTO [mig_director].[tmp_ms_xx_Valueset] ([ProjectID], [ItemID], [Name], [SchemaName], [ProjectType], [ValuesetType], [ProviderID], [DataServicesUsage], [RowCount], [CreateTableSql], [Columns], [Present], [LastUpdateDate], [LastUpdateBy], [CreationDate], [CreatedBy], [ModificationDate], [ModifiedBy])
        SELECT   [ProjectID],
                 [ItemID],
                 [Name],
                 [SchemaName],
                 [ProjectType],
                 [ValuesetType],
                 [ProviderID],
                 [DataServicesUsage],
                 [RowCount],
                 [CreateTableSql],
                 [Columns],
                 [Present],
                 [LastUpdateDate],
                 [LastUpdateBy],
                 [CreationDate],
                 [CreatedBy],
                 [ModificationDate],
                 [ModifiedBy]
        FROM     [mig_director].[Valueset]
        ORDER BY [ProjectID] ASC, [ItemID] ASC;
    END

DROP TABLE [mig_director].[Valueset];
EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_Valueset]', N'Valueset';
EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_constraint_PK_Valueset1]', N'PK_Valueset', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Starting rebuilding table [mig_director].[ExportSourceView_Baseline]...';
GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

ALTER TABLE [mig_director].[ExportSourceView_Baseline] DROP CONSTRAINT [PK_ExportSourceView_Baseline];

CREATE TABLE [mig_director].[tmp_ms_xx_ExportSourceView_Baseline] (
    [FullName]         AS            (([ViewSchema] + '.') + [ViewName]) PERSISTED NOT NULL,
    [ViewSchema]       VARCHAR (128) NOT NULL,
    [ViewName]         VARCHAR (128) NOT NULL,
    [BaselineCount]    INT           NOT NULL,
    [ModificationDate] DATETIME2 (7) NULL,
    [CreationDate]     DATETIME2 (7) NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_ExportSourceView_Baseline1] PRIMARY KEY CLUSTERED ([FullName] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [mig_director].[ExportSourceView_Baseline])
    BEGIN
        INSERT INTO [mig_director].[tmp_ms_xx_ExportSourceView_Baseline] ([ViewSchema], [ViewName], [BaselineCount], [ModificationDate], [CreationDate])
        SELECT [ViewSchema],
               [ViewName],
               [BaselineCount],
               [ModificationDate],
               [CreationDate]
        FROM   [mig_director].[ExportSourceView_Baseline];
    END

DROP TABLE [mig_director].[ExportSourceView_Baseline];

EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_ExportSourceView_Baseline]', N'ExportSourceView_Baseline';

EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_constraint_PK_ExportSourceView_Baseline1]', N'PK_ExportSourceView_Baseline', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Starting rebuilding table [mig_director].[ExportSourceTable_Baseline]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

ALTER TABLE [mig_director].[ExportSourceTable_Baseline] DROP CONSTRAINT [PK_ExportSourceTable_Baseline];

CREATE TABLE [mig_director].[tmp_ms_xx_ExportSourceTable_Baseline] (
    [FullName]         AS            (([TableSchema] + '.') + [TableName]) PERSISTED NOT NULL,
    [TableSchema]      VARCHAR (128) NOT NULL,
    [TableName]        VARCHAR (128) NOT NULL,
    [BaselineCount]    INT           NOT NULL,
    [ModificationDate] DATETIME2 (7) NULL,
    [CreationDate]     DATETIME2 (7) NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_ExportSourceTable_Baseline1] PRIMARY KEY CLUSTERED ([FullName] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [mig_director].[ExportSourceTable_Baseline])
    BEGIN
        INSERT INTO [mig_director].[tmp_ms_xx_ExportSourceTable_Baseline] ([TableSchema], [TableName], [BaselineCount], [ModificationDate], [CreationDate])
        SELECT [TableSchema],
               [TableName],
               [BaselineCount],
               [ModificationDate],
               [CreationDate]
        FROM   [mig_director].[ExportSourceTable_Baseline];
    END

DROP TABLE [mig_director].[ExportSourceTable_Baseline];

EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_ExportSourceTable_Baseline]', N'ExportSourceTable_Baseline';

EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_constraint_PK_ExportSourceTable_Baseline1]', N'PK_ExportSourceTable_Baseline', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Alter table [mig_director].[ExportSourceView]...';
DROP INDEX IF EXISTS [IX_ExportSourceView] ON [mig_director].[ExportSourceView];
GO
IF EXISTS (
    SELECT 1
    FROM sys.default_constraints
    WHERE parent_object_id = OBJECT_ID('[mig_director].[ExportSourceView]')
      AND name = 'DF_ExportSourceView_ForceInScope'
)
BEGIN
    ALTER TABLE [mig_director].[ExportSourceView] DROP CONSTRAINT [DF_ExportSourceView_ForceInScope];
END
GO
IF EXISTS (
    SELECT 1
    FROM sys.default_constraints
    WHERE parent_object_id = OBJECT_ID('[mig_director].[ExportSourceView]')
      AND name = 'DF_ExportSourceView_CreationDate'
)
BEGIN
    ALTER TABLE [mig_director].[ExportSourceView] DROP CONSTRAINT [DF_ExportSourceView_CreationDate];
END
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [mig_director].[tmp_ms_xx_ExportSourceView] (
    [ItemID]             UNIQUEIDENTIFIER NOT NULL,
    [FullName]           AS               (([ViewSchema] + '.') + [ViewName]) PERSISTED NOT NULL,
    [ViewSchema]         VARCHAR (128)     NOT NULL,
    [ViewName]           NVARCHAR (128)   NOT NULL,
    [ColumnsXml]         XML              NOT NULL,
    [IsPresent]          BIT              NOT NULL,
    [LoadedDate]         DATETIME2 (7)    NULL,
    [LoadedElapsedTicks] BIGINT           NULL,
    [LoadedCount]        INT              NULL,
    [ForceInScope]       BIT              CONSTRAINT [DF_ExportSourceView_ForceInScope] DEFAULT ((0)) NOT NULL,
    [TestableQuery]      NVARCHAR (MAX)   NOT NULL,
    [AcceptedQueryPlan]  NVARCHAR (MAX)   NULL,
    [OptimizeIterations] INT              NULL,
    [OptimizeDate]       DATETIME2 (7)    NULL,
    [CreationDate]       DATETIME2 (7)    CONSTRAINT [DF_ExportSourceView_CreationDate] DEFAULT (sysdatetime()) NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_ExportSourceView1] PRIMARY KEY CLUSTERED ([ItemID] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [mig_director].[ExportSourceView])
    BEGIN
        INSERT INTO [mig_director].[tmp_ms_xx_ExportSourceView] ([ItemID], [ViewSchema], [ViewName], [ColumnsXml], [IsPresent], [LoadedDate], [LoadedElapsedTicks], [LoadedCount], [ForceInScope], [TestableQuery], [AcceptedQueryPlan], [OptimizeIterations], [OptimizeDate], [CreationDate])
        SELECT   [ItemID],
                 [ViewSchema],
                 [ViewName],
                 [ColumnsXml],
                 [IsPresent],
                 [LoadedDate],
                 [LoadedElapsedTicks],
                 [LoadedCount],
                 [ForceInScope],
                 [TestableQuery],
                 [AcceptedQueryPlan],
                 [OptimizeIterations],
                 [OptimizeDate],
                 [CreationDate]
        FROM     [mig_director].[ExportSourceView]
        ORDER BY [ItemID] ASC;
    END
DROP TABLE [mig_director].[ExportSourceView];
EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_ExportSourceView]', N'ExportSourceView';
EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_constraint_PK_ExportSourceView1]', N'PK_ExportSourceView', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
CREATE NONCLUSTERED INDEX [IX_ExportSourceView] ON [mig_director].[ExportSourceView]([FullName] ASC);
GO
PRINT N'Altering Procedure [mig_director].[Valueset_InsertOrUpdate]...';
GO
ALTER procedure [mig_director].[Valueset_InsertOrUpdate] (
	@itemID uniqueidentifier	
,   @name varchar(128)	
,   @schema varchar(128)	
,   @projectID uniqueidentifier	
,   @projectType varchar(50)	
,   @valuesetType tinyint	
,   @providerID uniqueidentifier = null	
,   @dataServicesUsage XML = null
,   @createTableSql varchar(max)
,   @columns xml
,   @user varchar(50)	
)
as
	declare @updated table([CreateTableSql] varchar(max) collate Latin1_General_CS_AS) -- Collate to ensure case-sensitive comparison

	-- First on ItemID
	update [mig_director].[Valueset]
	set
		[ProjectType] = @projectType
	,	[SchemaName] = @schema 
	,	[Name] = @name 
	,	[ValuesetType] = @valuesetType
	,	[ProviderID] = @providerID 
	,	[DataServicesUsage] = @dataServicesUsage
	,	[CreateTableSql] = @createTableSql
	,	[Columns] = @columns
	,	[RowCount] = case
			when [ValuesetType] = @valuesetType then [RowCount]
			else null
		end
	,	[Present] = 1
	,	[ModificationDate] = sysdatetime()
	,	[ModifiedBy] = @user
	output deleted.[CreateTableSql] into @updated
	where
		[ProjectID] = @projectID
	and	[ItemID] = @itemID
		
	if 0 = @@rowCount
	begin	
		-- Then on Name
		update [mig_director].[Valueset]
		set
			[ItemID] = @itemID
		,	[ProjectType] = @projectType
		,	[SchemaName] = @schema -- To handle case only difference
		,	[Name] = @name -- To handle case only difference
		,	[ValuesetType] = @valuesetType
		,	[ProviderID] = @providerID 
		,	[DataServicesUsage] = @dataServicesUsage
		,	[CreateTableSql] = @createTableSql
		,	[Columns] = @columns
		,	[RowCount] = case
				when [ValuesetType] = @valuesetType then [RowCount]
				else null
			end
		,	[Present] = 1
		,	[ModificationDate] = sysdatetime()
		,	[ModifiedBy] = @user
		output deleted.[CreateTableSql] into @updated
		where
			[ProjectID] = @projectID
		and [Name] = @name
		and [SchemaName] = @schema		

		if 0 = @@rowCount
		begin	
			-- Only now insert
			insert [mig_director].[Valueset] (
				[ItemID]
			,	[Name]
			,	[SchemaName]
			,	[ProjectID]
			,	[ProjectType]
			,	[ValuesetType]
			,	[ProviderID]
			,	[DataServicesUsage]
			,	[CreateTableSql]
			,	[Columns]
			,	[Present]
			,	[CreationDate]
			,	[CreatedBy]
			) values (
				@itemID	
			,	@name	
			,	@schema
			,	@projectID
			,	@projectType 
			,	@valuesetType 
			,	@providerID
			,	@dataServicesUsage
			,	@createTableSql	
			,	@columns
			,	1
			,	sysdatetime()
			,	@user	
			)
		end
	end

	-- Return true if create sql has changed / is new
	select cast(
		case 
			when (select top(1) [CreateTableSql] from @updated) = @createTableSql then 0
			else 1
		end
	as bit)
GO
PRINT N'Alter procedure [mig_director].[Export_SourceView_InsertOrUpdate]...';
GO
ALTER PROCEDURE [mig_director].[Export_SourceView_InsertOrUpdate] 
	@itemID uniqueidentifier
,	@viewSchema varchar(128)
,	@viewName varchar(128)
,	@columnsXml xml
,	@testableQuery nvarchar(max)
,	@wasCreated bit
AS
	declare @queryChanged bit
	declare @old table([TestableQuery] nvarchar(max) collate Latin1_General_CS_AS) -- Collate to ensure case-sensitive comparison
	
	update [mig_director].[ExportSourceView]
	set
		[ViewSchema] = @viewSchema
	,	[ViewName] = @viewName
	,	[ColumnsXml] = @columnsXml
	,	[LoadedCount] = case @wasCreated when 1 then null else [LoadedCount] end
	,	[LoadedElapsedTicks] = case @wasCreated when 1 then null else [LoadedElapsedTicks] end
	,	[LoadedDate] = case @wasCreated when 1 then null else [LoadedDate] end
	,	[IsPresent] = 1
	,	[TestableQuery] = @testableQuery
	,	[AcceptedQueryPlan] = case when [TestableQuery] = @testableQuery then [AcceptedQueryPlan] else null end 
	,	[OptimizeIterations] = case when [TestableQuery] = @testableQuery then [OptimizeIterations] else null end 
	,	[OptimizeDate] = case when [TestableQuery] = @testableQuery then [OptimizeDate] else null end 
	output deleted.[TestableQuery] into @old
	where
		[ItemID] = @itemID
	
	if 0 = @@rowCount
	begin
		insert [mig_director].[ExportSourceView] (
			[ItemID]
		,	[ViewSchema]
		,	[ViewName]
		,	[ColumnsXml]
		,	[TestableQuery]
		,	[IsPresent]
		) values (
			@itemID
		,	@viewSchema
		,	@viewName
		,	@columnsXml
		,	@testableQuery
		,	1
		)

		set @queryChanged = 1
	end
	else
	begin
		select @queryChanged = case when [TestableQuery] = @testableQuery then 0 else 1 end from @old
	end

	select @queryChanged
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Clean up after Formatted Files'
PRINT N'----------------------------------------------------------------------------------------------------------------------';
GO
PRINT N'Updating extension usages...';
update [mig_director].[ExtensionUsage]
set
	[ParameterValues] = case [ExtensionClassName]
		when 'MigFx.Director.Server.Job.Export.SourceTableJob+Db2Reformatter' then '<values><value name="Separator" label="Separator" value=";"/><value name="QuoteChar" label="Quote Char" value="#"/><value name="ColumnHeaders" label="Column headers" value="False"/><value name="InputEncoding" label="Input encoding" value=""/></values>'
		else [ParameterValues]
	end
,	[ExtensionClassName] = case [ExtensionClassName]
		when 'MigFx.Director.Server.Job.Export.SourceTableJob+CsvReformatter' then 'MigFx.Director.Server.Job.Export.BulkProviders.BulkDataSimpleCsvProvider'
		when 'MigFx.Director.Server.Job.Export.SourceTableJob+CsvQuotedTextReformatter' then 'MigFx.Director.Server.Job.Export.BulkProviders.BulkDataRfc4180CsvProvider'
		when 'MigFx.Director.Server.Job.Export.SourceTableJob+ExcelReformatter' then 'MigFx.Director.Server.Job.Export.BulkProviders.BulkDataExcelProvider'
		when 'MigFx.Director.Server.Job.Export.SourceTableJob+Db2Reformatter' then 'MigFx.Director.Server.Job.Export.BulkProviders.BulkDataRfc4180CsvProvider'
		else [ExtensionClassName]
	end
where
	[ExtensionType] = 1
GO
PRINT N'Dropping Procedure [mig_director].[Export_FormattedFile_*] procedures...';
GO
DROP PROCEDURE IF EXISTS [mig_director].[Export_FormattedFile_List];
DROP PROCEDURE IF EXISTS [mig_director].[Export_FormattedFile_SetLoaded];
DROP PROCEDURE IF EXISTS [mig_director].[Export_FormattedFile_SetPresent];
DROP PROCEDURE IF EXISTS [mig_director].[Export_FormattedFile_SetTruncated];
DROP PROCEDURE IF EXISTS [mig_director].[Export_SourceFile_SetReformat];
DROP PROCEDURE IF EXISTS [mig_director].[Export_SourceTable_SetReformat];
GO
PRINT N'Starting rebuilding tables [mig_director].[ExportSourceTable] and [mig_director].[ExportSourceFile]...';
GO
if exists (select 1 from sys.tables where schema_name(schema_id) = 'mig_director' and name = 'ExportFormattedFile')
begin
	BEGIN TRANSACTION;

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

	SET XACT_ABORT ON;

	-- Rebuild [mig_director].[ExportSourceTable] 
	ALTER TABLE [mig_director].[ExportSourceTable] DROP CONSTRAINT [DF_ExportSourceTable_ForceInScope];
	ALTER TABLE [mig_director].[ExportSourceTable] DROP CONSTRAINT [DF_ExportSourceTable_CreationDate];

	CREATE TABLE [mig_director].[tmp_ms_xx_ExportSourceTable] (
		[ItemID]            UNIQUEIDENTIFIER NOT NULL,
		[FullName]          AS               (([TableSchema] + '.') + [TableName]) PERSISTED NOT NULL,
		[TableSchema]       VARCHAR (128)     NOT NULL,
		[TableName]         VARCHAR (128)    NOT NULL,
		[ColumnsXml]        XML              NOT NULL,
		[LoaderExtensionID] UNIQUEIDENTIFIER NULL,
		[LoadedDate]        DATETIME2 (7)    NULL,
		[LoadedCount]       INT              NULL,
		[IsPresent]         BIT              NOT NULL,
		[ForceInScope]      BIT              CONSTRAINT [DF_ExportSourceTable_ForceInScope] DEFAULT ((0)) NOT NULL,
		[CreationDate]      DATETIME2 (7)    CONSTRAINT [DF_ExportSourceTable_CreationDate] DEFAULT (sysdatetime()) NOT NULL,
		CONSTRAINT [tmp_ms_xx_constraint_PK_ExportSourceTable1] PRIMARY KEY CLUSTERED ([ItemID] ASC)
	);

	IF EXISTS (SELECT TOP 1 1 
			   FROM   [mig_director].[ExportSourceTable])
		BEGIN
			with 
				allFmt as (
					select 
						A.[SourceTableFullName]
					,	B.[LoadedDate]
					from	
						[mig_director].[ExportSourceFile] as A
						inner join [mig_director].[ExportFormattedFile] as B
						on
							A.[FileName] = B.[FileName]
				)
			,	newest as (
				select
						A.[SourceTableFullName]
					,	A.[LoadedDate]
				from
					allFmt as A
				where
					A.[LoadedDate] = (
						select MAX(B.[LoadedDate])
						from
							allFmt as B
						where
							A.[SourceTableFullName] = B.[SourceTableFullName]
					)
				)
			insert [mig_director].[tmp_ms_xx_ExportSourceTable] ([ItemID], [TableSchema], [TableName], [ColumnsXml], [LoaderExtensionID], [LoadedDate], [LoadedCount], [IsPresent], [ForceInScope], [CreationDate])
			select
				A.[ItemID]
			,	A.[TableSchema]
			,	A.[TableName]
			,	A.[ColumnsXml]
			,	A.[ReformatExtensionID]
			,	B.[LoadedDate]
			,	A.[LoadedCount]
			,	A.[IsPresent]
			,	A.[ForceInScope]
			,	A.[CreationDate]
			from
				[mig_director].[ExportSourceTable] as A
				left outer join newest as B
				on
					A.[FullName] = B.[SourceTableFullName]
			order by
				A.[ItemID]
		END

	DROP TABLE [mig_director].[ExportSourceTable];

	EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_ExportSourceTable]', N'ExportSourceTable';

	EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_constraint_PK_ExportSourceTable1]', N'PK_ExportSourceTable', N'OBJECT';

	CREATE NONCLUSTERED INDEX [IX_ExportSourceTable] ON [mig_director].[ExportSourceTable]([FullName] ASC);

-- Rebuild [mig_director].[ExportSourceFile] 
	ALTER TABLE [mig_director].[ExportSourceFile] DROP CONSTRAINT [DF_ExportSourceFile_CreationDate];

	CREATE TABLE [mig_director].[tmp_ms_xx_ExportSourceFile] (
		[FileName]            VARCHAR (512)    NOT NULL,
		[FullName]            VARCHAR (1024)   NOT NULL,
		[FileDate]            DATETIME2 (7)    NULL,
		[SourceTableFullName] VARCHAR (512)    NOT NULL,
		[LoaderExtensionID]   UNIQUEIDENTIFIER NULL,
		[DateOfLoadedFile]    DATETIME2 (7)    NULL,
		[LoadedDate]          DATETIME2 (7)    NULL,
		[LoadedCount]         INT              CONSTRAINT [DF_ExportSourceFile_LoadedCount] DEFAULT ((0)) NOT NULL,
		[CreationDate]        DATETIME2 (7)    CONSTRAINT [DF_ExportSourceFile_CreationDate] DEFAULT (sysdatetime()) NOT NULL,
		CONSTRAINT [tmp_ms_xx_constraint_PK_ExportSourceFile1] PRIMARY KEY CLUSTERED ([FileName] ASC)
	);

	IF EXISTS (SELECT TOP 1 1 
			   FROM   [mig_director].[ExportSourceFile])
		BEGIN
			INSERT INTO [mig_director].[tmp_ms_xx_ExportSourceFile] (
				[FileName], 
				[FullName], 
				[FileDate], 
				[SourceTableFullName], 
				[LoaderExtensionID],
				[DateOfLoadedFile], 
				[LoadedDate], 
				[LoadedCount], 
				[CreationDate]
				)
			SELECT   A.[FileName],
					 A.[FullName],
					 A.[FileDate],
					 A.[SourceTableFullName],
					 A.[ReformatExtensionID],
					 B.[DateOfLoadedFile],
					 B.[LoadedDate],
					 isnull(B.[LoadedCount], 0),
					 A.[CreationDate]
			FROM     
				[mig_director].[ExportSourceFile] as A
				left outer join [mig_director].[ExportFormattedFile] as B
				on
					A.[FileName] = B.[FileName]

			ORDER BY A.[FileName] ASC;
		END

	DROP TABLE IF EXISTS [mig_director].[ExportSourceFile];

	EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_ExportSourceFile]', N'ExportSourceFile';

	EXECUTE sp_rename N'[mig_director].[tmp_ms_xx_constraint_PK_ExportSourceFile1]', N'PK_ExportSourceFile', N'OBJECT';

	-- Drop [mig_director].[ExportFormattedFile] 
	DROP TABLE [mig_director].[ExportFormattedFile];

	COMMIT TRANSACTION;

	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
end
GO
PRINT N'Altering Procedure [mig_director].[Export_SourceFile_SetPresent]...';
GO
ALTER PROCEDURE [mig_director].[Export_SourceFile_SetPresent] 
( 
    @fileName varchar(512),
    @fullName varchar(1024),
    @sourceTableFullName varchar(512),
    @fileDate datetime2(7) = null
)
AS
    if @fileDate is null
    begin
        delete [mig_director].[ExportSourceFile] 
        where 
            [FileName] = @fileName
        and [FileName] not like '%.ExtensionLoad'
        and [LoaderExtensionID] is null
    end	
    else
    begin
        update [mig_director].[ExportSourceFile]
        set
            [FileDate] = @fileDate
        ,   [SourceTableFullName] = @sourceTableFullName
        ,   [FullName] = @fullName
        where
            [FileName] = @fileName

        if 0 = @@rowcount
        begin
            insert into [mig_director].[ExportSourceFile] (
                [FileName]
            ,	[FullName]
            ,   [SourceTableFullName]
            ,   [FileDate]
            ) values (
                @fileName
            ,	@fullName
            ,   @sourceTableFullName
            ,   @fileDate
            )
        end
    end
GO
PRINT N'Altering Procedure [mig_director].[Export_SourceTable_InsertOrUpdate]...';
GO
ALTER PROCEDURE [mig_director].[Export_SourceTable_InsertOrUpdate] 
	@itemID uniqueidentifier
,	@tableSchema varchar(128)
,	@tableName varchar(128)
,	@columnsXml xml
,	@wasCreated bit
AS
	update [mig_director].[ExportSourceTable]
	set
		[TableSchema] = @tableSchema
	,	[ColumnsXml] = @columnsXml
	,	[LoadedCount] = case @wasCreated 
			when 1 then null 
			else [LoadedCount] 
		end
	,	[IsPresent] = 1
	,	[ItemID] = @itemID
	where
		[ItemID] = @itemID
		
	if 0 = @@rowCount
	begin
		declare @loaderExtensionID uniqueidentifier

		select top(1)
			@loaderExtensionID = [ID]
		from	
			[mig_director].[ExtensionUsage]
		where
			[ExtensionType] = 1 -- 1: Loader
		and [IsDefault] = 1

		insert [mig_director].[ExportSourceTable] (
			[ItemID]
		,	[TableSchema]
		,	[TableName]
		,	[ColumnsXml]
		,	[LoaderExtensionID]
		,	[LoadedCount]
		,	[IsPresent]
		) values (
			@itemID
		,	@tableSchema
		,	@tableName
		,	@columnsXml
		,	@loaderExtensionID
		,	null
		,	1
		)
	end
GO
PRINT N'Altering Procedure [mig_director].[Export_SourceTable_SetLoadedExtension]...';
GO
ALTER procedure [mig_director].[Export_SourceTable_SetLoadedExtension]
    @tableSchema varchar(128)
,   @tableName varchar(256)
,   @loadedCount int
as
    declare @date1 datetime2(7) = sysdatetime()
    declare @date2 datetime2(7) = dateadd(second, 1, @date1)

    declare @fileName varchar(512) = @tableSchema + '\' + @tableName + '.ExtensionLoad'
    
    update [mig_director].[ExportSourceTable] 
    set 
        [LoadedCount] = @loadedCount 
    where 
        [TableSchema] = @tableSchema
    and [TableName] = @tableName

    delete [mig_director].[ExportSourceFile] 
    where 
        [FileName] = @fileName

    insert into [mig_director].[ExportSourceFile] (
        [FileName]
    ,   [FullName]
    ,   [FileDate]
    ,   [DateOfLoadedFile]
    ,   [LoadedDate]
    ,   [LoadedCount]
    ) values (
        @fileName
    ,   @fileName
    ,   @date2
    ,   @date2
    ,   @date2
    ,   @loadedCount
    );
GO
PRINT N'Altering Procedure [mig_director].[Export_SourceTable_StatusList]...';
GO
ALTER PROCEDURE [mig_director].[Export_SourceTable_StatusList] 
	@fullName nvarchar(512) = null
as
	set NoCount off -- Used to return the number of affected records

	-- Get dependencies to ExportEntities in order to filter out tables used by entities not in scope
	select
		B.[FullName]
	,	A.[EntityID]
	from 
		[mig_director].[ExportEntitySourceDependency] as A
		inner join [mig_director].[ExportSourceTable] as B
		on 
			A.[SourceID] = B.[ItemID]
	where
		B.[IsPresent] = 1 
	and	A.SourceType = 'Table'

	declare @tables table(
		[ItemID] uniqueidentifier
	,	[FullName] varchar(512) -- Extra to allow for the dot
	,	[TableSchema] varchar(128)
	,	[TableName] varchar(128)
	,	[LoaderExtensionID] uniqueidentifier
	,	[BaselineCount] int null
	,	[LoadedDate] datetime2(7) null
	,	[LoadedCount] int null
	,	[ForceInScope] bit
	,	primary key ([FullName])
	)
	
	declare @guidEmpty uniqueidentifier = '00000000-0000-0000-0000-000000000000'

	insert into @tables
	select 
		A.[ItemID]
	,	A.[FullName]
	,	A.[TableSchema]
	,	A.[TableName]
	,	isnull(A.[LoaderExtensionID], @guidEmpty) 
	,	B.[BaselineCount]
	,	A.[LoadedDate]
	,	A.[LoadedCount]
	,	A.[ForceInScope]
	from
		[mig_director].[ExportSourceTable] as A
		left outer join [mig_director].[ExportSourceTable_Baseline] as B
		on 
			A.[FullName] = B.[FullName]
	where
		(@fullName is null or @fullName = A.[FullName])
	and A.[IsPresent] = 1 

	select
		[ItemID]
	,	[FullName]
	,	[TableSchema]
	,	[TableName]
	,	[LoaderExtensionID]
	,	0 as [FileCount]
	,	convert(datetime, null) as [FileDate]
	,	convert(datetime, null) as [LoadedFileDate]
	,	[LoadedDate]
	,	[LoadedCount]
	,	[BaselineCount]
	,	convert(bit, 1) as [Ready]
	,	[ForceInScope]
	,	convert(bit, 0) as [InScope]
	from @tables

	select
		B.FullName as [FullName_Received]
	,	B.[FileName]
	,	A.[TableName] as[SourceTableName] 
	,	A.[FullName] as [SourceTableFullName]
	,	isnull(B.[LoaderExtensionID], @guidEmpty) as [LoaderExtensionID]
	,	B.[FileDate] as [FileDate]
	,	B.[DateOfLoadedFile]
	,	B.[LoadedDate]
	,	B.[LoadedCount]
	,	1 as [Ready]
	from 
		@tables as A
		inner join [mig_director].[ExportSourceFile] as B 
		on  
			lower(A.[FullName]) = lower(B.[SourceTableFullName]) 
		and	B.[FileDate] is not null

	select 
		[ID]
	,	[Name]
	,	[IsDefault]
	,	[ExtensionClassName]
	,	cast(0 as bit) as [IsFileLoader]
	from
		[mig_director].[ExtensionUsage]
	where
		[ExtensionType] = 1 -- 1: Loader
GO
PRINT N'Altering Procedure [mig_director].[Export_SourceTable_SetLoaded]...';
GO
ALTER PROCEDURE [mig_director].[Export_SourceTable_SetLoaded] 
	@fullName varchar(512)
,	@loadedDate datetime2(7) = null
,	@loadedCount int
as
	update [mig_director].[ExportSourceTable]
	set
	 	[LoadedCount] = @loadedCount
	,   [LoadedDate] = @loadedDate
	where
		[FullName] = @fullName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceFile_SetLoaded]...';
GO
create or alter procedure [mig_director].[Export_SourceFile_SetLoaded] 
	@fileName varchar(512)
,	@loadedDate datetime2(7) = null
,	@loadedCount int
AS
	update [mig_director].[ExportSourceFile]
	set
		[LoadedCount] = @loadedCount
	,   [DateOfLoadedFile] = [FileDate]
	,   [LoadedDate] = @loadedDate
	where
		[FileName] = @fileName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceFile_SetLoader]...';
GO
create or alter procedure [mig_director].[Export_SourceFile_SetLoader] 
	@fileName varchar(512)
,	@loaderExtensionID uniqueidentifier = null
as

	update [mig_director].[ExportSourceFile]
	set
		[LoaderExtensionID] = @loaderExtensionID
	where
		[FileName] = @fileName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceFile_SetTruncated]...';
GO
create or alter procedure [mig_director].[Export_SourceFile_SetTruncated] 
	@fullName varchar(512)
as
	update [mig_director].[ExportSourceFile]
	set
		[LoadedCount] = 0
	,	[DateOfLoadedFile] = null
	,	[LoadedDate] = null
	where 
		[SourceTableFullName] = @fullName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_SetLoader]...';
GO
create or alter PROCEDURE [mig_director].[Export_SourceTable_SetLoader] 
	@fullName nvarchar(512)
,	@loaderExtensionID uniqueidentifier = null
as
	update [mig_director].[ExportSourceTable]
	set
		[LoaderExtensionID] = @loaderExtensionID
	where
		[FullName] = @fullName
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- New Unload implementations'
PRINT N'----------------------------------------------------------------------------------------------------------------------';
GO
PRINT N'Wrap any non-xml Audit items...';
GO
if exists (
	select 1
	from [mig_item].[Audit]
	where 
		convert(nvarchar(max), decompress([ResultCompressed])) not like '<%'
)
begin
    set xact_abort on;
    begin tran;

    print 'No rows wrapped yet - applying wrap to all rows';

    update [mig_item].[Audit]
    set 
		[ResultCompressed] = compress(
			N'<AuditResult><![CDATA['
			+ convert(nvarchar(max), decompress([ResultCompressed]))
			+ N']]></AuditResult>'
		)
	where 
		convert(nvarchar(max), decompress([ResultCompressed])) not like '<%'

    commit;
end
PRINT N'Alter procedure [mig_item].[Audit_Result]...';
GO
CREATE OR ALTER procedure [mig_item].[Audit_Result]
	@jobId int
,	@partitionCount int = 1
,	@partitionIndex int = null
as
	with
		valid as (
			select
				A.[ItemId]
			,	A.[Step]
			,	A.[ResultCompressed]
			from
				[mig_item].[Audit] as A with (NoLock) 

				inner join [mig_item].[Active] as B 
				on	
					A.[ItemId] = B.[ItemId]
				and	A.[JobId] = B.[JobId]
				and	A.[JobInstanceId] = B.[JobInstanceId]
				and	A.[Step] = B.[Step]

				inner join [mig_item].[Id] as C with (NoLock)
				on 
					A.[ItemId] = C.[ItemId]
			where
				C.[InvalidatedTime] is null
		)
	,	src as (
			select 
				[ItemId] 
			,	[ResultCompressed]
			from 
				valid
			where 
				[Step] = 0
		)
	,	tgt as (
			select 
				[ItemId] 
			,	[ResultCompressed]
			from 
				valid
			where 
				[Step] = 1
		)
	,	aud as (
			select
				coalesce(src.[ItemId], tgt.[ItemId]) as [ItemId]
			,	src.[ResultCompressed] as [SourceAudit]
			,	tgt.[ResultCompressed] as [TargetAudit]
			from
				src
				full outer join tgt
				on
					src.[ItemId] = tgt.[ItemId]
		)
	select 
		A.[ItemId] 
	,	B.[EntityID] 
	,	C.[PartitionValue]
	,	B.[BreakFields] as [sourceKey]
	,	D.[KeyValue] as [migrationKey]
	,	A.[SourceAudit] 
	,	A.[TargetAudit] 
	from
		aud as A

		inner join [mig_item].[Exportkey] as B with (nolock)
		on 
			A.[ItemId]= B.[ItemId]

		inner join [mig_item].[item] as C with (nolock)
		on 
			A.[ItemId] = C.[ItemId]

		inner join [mig_item].[Mainkey] as D with (nolock)
		on 
			C.[KeyId] = D.[KeyId]

		left outer join [mig_item].[Processed] as E -- No NoLock on the Processed table
		on
			E.[JobId] = @jobId
		and	E.[ItemId] = A.[ItemId]
	where
		E.[ItemId] is null
	and	(@partitionIndex is null or A.[ItemId] % @partitionCount = @partitionIndex)
GO
PRINT N'Restructure mig_item.Processed table...';
GO

if exists (select 1 from sys.columns where object_id = object_id(N'mig_item.Processed') and name = 'ElapsedTicks')
begin
    drop index if exists IX_Processed_Valid on mig_item.Processed;

    alter table mig_item.Processed drop constraint if exists PK_Processed;

    alter table mig_item.Processed add constraint PK_Processed primary key clustered (JobId, ItemId);

    alter table mig_item.Processed drop column if exists Step, ElapsedTicks;
end
GO
PRINT N'Alter Function [mig_garbage].[BuildStatement]...';
GO
ALTER function [mig_garbage].[BuildStatement](
	@seed varchar(max)
,	@tableName varchar(50)
)
returns varchar(max)
as
	begin
	
		declare @stmnt varchar(max)
		
		------------------------------------------------------------------------------------------------------------------
		-- The Id table controls everything. If an Item is invalidated here it means it is no longer exported
		------------------------------------------------------------------------------------------------------------------
		if 'Id' = @tableName
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  where' + char(13)
			+ '    A.[InvalidatedTime] < @newest'
		end
		------------------------------------------------------------------------------------------------------------------
		-- These tables are all 'singletons' = only 1 row per item
		------------------------------------------------------------------------------------------------------------------
		else if @tableName in ('Item', 'Active', 'ExportItem', 'ImportItem', 'SourceXml', 'ExportXml', 'TargetXml')
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Id] as B' + char(13)
			+ '    on A.[ItemId] = B.[ItemId]' + char(13)
			+ '  where' + char(13)
			+ '  (' + char(13)
			+ '       B.[ItemId] is null' + char(13)				
			+ '    or B.[InvalidatedTime] < @newest' + char(13)
			+ '  )'

			if 'Active' = @tableName
			begin
				set @stmnt = @stmnt + char(13) + '  and A.[ItemId] > 0 -- Keep Active row from Initializer'
			end
		end
		------------------------------------------------------------------------------------------------------------------
		-- The garbage table is collected last
		------------------------------------------------------------------------------------------------------------------
		else if @tableName = 'Garbage'
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Active] as B' + char(13)
			+ '    on A.[ItemId] = B.[ItemId] and B.[JobId] = A.[JobId] and B.[JobInstanceId] = A.[JobInstanceId] and A.[Step] = B.[Step]' + char(13)
			+ '  where' + char(13)
			+ '      A.[InvalidatedTime] < @newest' + char(13)
			+ '  and B.[ItemId] is null'
		end
		------------------------------------------------------------------------------------------------------------------
		-- Special care in Relationhip tables to preserve relationships created by Initializer
		------------------------------------------------------------------------------------------------------------------
		else if @tableName in ('RelationshipKey', 'RelationshipValue', 'RelationshipBagPropertyValue')
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Garbage] as B' + char(13)
			+ '    on B.[ItemId] = A.[ItemId] and B.[JobId] = A.[JobId] and B.[JobInstanceId] = A.[JobInstanceId] and B.[Step] = A.[Step]' + char(13)
			+ '  left outer join [mig_item].[Id] as C' + char(13)
			+ '    on A.[ItemId] = C.[ItemId]' + char(13)
			+ '  left outer join [mig_item].[Active] as D' + char(13)
			+ '    on A.[ItemId] = D.[ItemId] and A.[JobId] = D.[JobId] and A.[JobInstanceId] = D.[JobInstanceId] and A.[Step] = D.[Step]' + char(13)
			+ '  where' + char(13) 
			+ '    -- Other relationships' + char(13)
			+ '    A.[ItemId] > 0' + char(13)
			+ '    and' + char(13)
			+ '    (' + char(13)
			+ '      B.[InvalidatedTime] < @newest' + char(13)
			+ '      or	(C.[ItemId] is null or C.[InvalidatedTime] < @newest)' + char(13)
			+ '    )' + char(13)
		end
		------------------------------------------------------------------------------------------------------------------
		-- The remaing tables contains real Garbage = More than 1 row per Item, recreated every time
		-- The Processed table does not have a Step column, so it is handled separately
		------------------------------------------------------------------------------------------------------------------
		else if @tableName = 'Processed'
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Garbage] as B' + char(13)
			+ '    on B.[ItemId] = A.[ItemId] and B.[JobId] = A.[JobId] and B.[JobInstanceId] = A.[JobInstanceId]' + char(13)
			+ '  left outer join [mig_item].[Id] as C' + char(13)
			+ '    on A.[ItemId] = C.[ItemId]' + char(13)
			+ '  left outer join [mig_item].[Active] as D' + char(13)
			+ '    on A.[ItemId] = D.[ItemId] and A.[JobId] = D.[JobId] and A.[JobInstanceId] = D.[JobInstanceId]' + char(13)
			+ '  where' + char(13) 
			+ '    B.[InvalidatedTime] < @newest' + char(13)
			+ '    or	(C.[ItemId] is null or C.[InvalidatedTime] < @newest)' 
		end
		------------------------------------------------------------------------------------------------------------------
		-- The remaing tables contains real Garbage = More than 1 row per Item, recreated every time
		------------------------------------------------------------------------------------------------------------------
		else 
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Garbage] as B' + char(13)
			+ '    on B.[ItemId] = A.[ItemId] and B.[JobId] = A.[JobId] and B.[JobInstanceId] = A.[JobInstanceId] and B.[Step] = A.[Step]' + char(13)
			+ '  left outer join [mig_item].[Id] as C' + char(13)
			+ '    on A.[ItemId] = C.[ItemId]' + char(13)
			+ '  left outer join [mig_item].[Active] as D' + char(13)
			+ '    on A.[ItemId] = D.[ItemId] and A.[JobId] = D.[JobId] and A.[JobInstanceId] = D.[JobInstanceId] and A.[Step] = D.[Step]' + char(13)
			+ '  where' + char(13) 
			+ '    B.[InvalidatedTime] < @newest' + char(13)
			+ '    or	(C.[ItemId] is null or C.[InvalidatedTime] < @newest)' 
		end
		
		return @seed + char(13)+ 'from' + char(13) + @stmnt
		
	end
GO
PRINT N'Alter Procedure [mig_item].[Export_Unload]...';
GO
CREATE OR ALTER procedure [mig_item].[Export_Unload]
	@jobId int
,	@partitionCount int = 1
,	@partitionIndex int = null
,	@entityIDlist varchar(max) = null
as
	declare @entityIDs table([ID] uniqueidentifier, primary key ([ID]))

	if @entityIDlist is null
	begin
		insert @entityIDs select [EntityID] from [mig_director].[ExportEntity] where [IsPresent] = 1
	end
	else
	begin
		insert @entityIDs select [ID] from [mig_utility].[ParseGuids](@entityIDlist, ',')
	end;
	
	with 
		valid as (
			select A.*
			from  
				[mig_item].[ExportXml] as A with (NoLock)
				inner join [mig_valid].[Item] as B with (NoLock)
				on 
					A.[ItemId] = B.[ItemId]
			where
				A.[Deleted] = 0
		)

	select 
		B.[EntityID]
	,	A.[ItemId]
	,	A.[XmlCompressed]
	from
		valid as A with (NoLock)
		
		inner join [mig_item].[ExportKey] as B with (NoLock)
		on  
			A.[ItemId] = B.[ItemId] 

		inner join [mig_item].[ExportItem] as C with (NoLock)
		on  
			C.[ItemId] = A.[ItemId] 
		and	C.[Result] = 0 -- 0: Exported

		inner join [mig_item].[Active] as D with (NoLock)
		on  
			D.[ItemId] = C.[ItemId]		
		and D.[JobId] = C.[JobId] 
		and D.[JobInstanceId] = C.[JobInstanceId]
		and D.[Step] = 0 -- 0: Export

		inner join @entityIDs as E
		on
			B.[EntityID] = E.[ID]

		left outer join [mig_item].[Processed] as F -- No NoLock on the Processed table
		on
			F.[JobId] = @jobId
		and	F.[ItemId] = A.[ItemId]
	where
		F.[ItemId] is null
	and	(@partitionIndex is null or A.[ItemId] % @partitionCount = @partitionIndex)
GO
PRINT N'Alter Procedure [mig_item].[Import_Unload_DependenciesByEntityID]...';
GO
ALTER procedure [mig_item].[Import_Unload_DependenciesByEntityID]
	@limit bigint
,	@jobId int
,	@list varchar(max) -- Comma-separated Guids
,	@includeDescendants bit
,	@includeAncestors bit
,	@key varchar(max) = null
as
	declare @entityIDs table([ID] uniqueidentifier, primary key ([ID]))

	insert @entityIDs select [ID] from [mig_utility].[ParseGuids](@list, ',')
	
	declare @itemIds [mig_item].[ItemIdList];

	with valid as (
		select 
			A.[ItemId]
		from
			[mig_item].[ExportKey] as A with (NoLock)

			inner join [mig_item].[ImportItem] as B with (NoLock)
			on  
				B.[ItemId] = A.[ItemId] 
			and B.[Result] = 2 -- 3: Success

			inner join [mig_item].[Active] as C with (NoLock)
			on  
				C.[ItemId] = B.[ItemId] 
			and C.[JobId] = B.[JobId] 
			and C.[JobInstanceId] = B.[JobInstanceId]
			and C.[Step] = 1 -- 1: Import

			inner join [mig_item].[Item] as D with (NoLock)
			on
				A.[ItemId] = D.[ItemId]

			inner join [mig_item].[MainKey] as E
			on
				D.[KeyId] = E.[KeyId]
			and	(@key is null or E.[KeyValue] like @key)

			inner join @entityIDs as F
			on
				A.[EntityID] = F.[ID]
	)
	insert @itemIds
	select top(@limit)
		A.[ItemId]
	from
		valid as A with (NoLock)

		left outer join [mig_item].[Processed] as B -- No NoLock on the Processed table
		on
			B.[JobId] = @jobId
		and	B.[ItemId] = A.[ItemId]
	where
		B.[ItemId] is null -- Restart: Not already processed by this job

	select
		B.[EntityID]
	,	C.[ItemId]  
	,	C.[XmlCompressed] 
	from 
		[mig_item].[_Import_IncludeDependencies](@itemIds, @includeDescendants, @includeAncestors) as A 

		inner join [mig_item].[ExportKey] as B with (NoLock)
		on
			A.[ItemId] = B.[ItemId]

		inner join [mig_item].[TargetXml] as C with (NoLock)
		on 
			A.[ItemId] = C.[ItemId]
GO
PRINT N'Alter Procedure [mig_item].[Import_Unload_DependenciesByItemSet]...';
GO
ALTER procedure [mig_item].[Import_Unload_DependenciesByItemSet] (
	@limit bigint
,	@jobId int
,	@list varchar(max) -- Comma-separated list of set ids
,	@includeDescendants bit
,	@includeAncestors bit
,	@key varchar(max) = null
)
as
	declare @setIds table([SetId] bigint, primary key ([SetId]))
	insert @setIds select convert(bigint, [Value]) from [mig_utility].[ParseDelimitedString](@list, ',')

	declare @itemIds [mig_item].[ItemIdList];

	with valid as (
		select 
			B.[ItemId]
		from
			[mig_item].[ItemSet] as A with (NoLock)

			inner join [mig_item].[ExportKey] as B with (NoLock)
			on
				A.[ItemId] = B.[ItemId]

			inner join [mig_item].[ImportItem] as C with (NoLock)
			on  
				C.[ItemId] = A.[ItemId] 
			and C.[Result] = 2 -- 3: Success

			inner join [mig_item].[Active] as D with (NoLock)
			on  
				D.[ItemId] = C.[ItemId] 
			and D.[JobId] = C.[JobId] 
			and D.[JobInstanceId] = C.[JobInstanceId]
			and D.[Step] = 1 -- 1: Import

			inner join [mig_item].[Item] as E with (NoLock)
			on
				A.[ItemId] = E.[ItemId]

			inner join [mig_item].[MainKey] as F
			on
				E.[KeyId] = F.[KeyId]
			and	(@key is null or F.[KeyValue] like @key)

			inner join @setIds as G
			on
				A.[SetId] = G.[SetId]
	)
	insert @itemIds
	select top (@limit)
		A.[ItemId]
	from
		valid as A with (NoLock)

		left outer join [mig_item].[Processed] as B -- No NoLock on the Processed table
		on
			B.[JobId] = @jobId
		and	B.[ItemId] = A.[ItemId]
	where
		B.[ItemId] is null -- Restart: Not already processed by this job

	select
		B.[EntityID]
	,	C.[ItemId]  
	,	C.[XmlCompressed] 
	from 
		[mig_item].[_Import_IncludeDependencies](@itemIds, @includeDescendants, @includeAncestors) as A 

		inner join [mig_item].[ExportKey] as B with (NoLock)
		on
			A.[ItemId] = B.[ItemId]

		inner join [mig_item].[TargetXml] as C with (NoLock)
		on 
			A.[ItemId] = C.[ItemId]
GO
PRINT N'Drop Procedure [mig_item].[_Import_Unload_DependenciesXml]...';
GO
DROP PROCEDURE IF EXISTS [mig_item].[_Import_Unload_DependenciesXml]
GO
PRINT N'Alter Procedure [mig_item].[Import_Unload]...';
GO
CREATE OR ALTER procedure [mig_item].[Import_Unload]
	@jobId int
,	@partitionCount int = 1
,	@partitionIndex int = null
,	@entityIDlist varchar(max) = null
,	@key varchar(max) = null
as
	declare @entityIDs table([ID] uniqueidentifier, primary key ([ID]))

	if @entityIDlist is null
	begin
		insert @entityIDs select [EntityID] from [mig_director].[ExportEntity] where [IsPresent] = 1
	end
	else
	begin
		insert @entityIDs select [ID] from [mig_utility].[ParseGuids](@entityIDlist, ',')
	end;
	
	with 
		valid as (
			select A.*
			from  
				[mig_item].[TargetXml] as A with (NoLock)
				inner join [mig_valid].[Item] as B with (NoLock)
				on 
					A.[ItemId] = B.[ItemId]
			where
				A.[Deleted] = 0
		)

	select 
		B.[EntityID]
	,	A.[ItemId]
	,	A.[XmlCompressed]
	from
		valid as A with (NoLock)
		
		inner join [mig_item].[ExportKey] as B with (NoLock)
		on  
			A.[ItemId] = B.[ItemId] 

		inner join [mig_item].[ImportItem] as C with (NoLock)
		on  
			C.[ItemId] = B.[ItemId] 
		and C.[Result] = 2 -- 3: Success

		inner join [mig_item].[Active] as D with (NoLock)
		on  
			D.[ItemId] = C.[ItemId]		
		and D.[JobId] = C.[JobId] 
		and D.[JobInstanceId] = C.[JobInstanceId]
		and D.[Step] = 1 -- 1: Import

		inner join [mig_item].[Item] as E with (NoLock)
		on
			B.[ItemId] = E.[ItemId]
				
		inner join [mig_item].[MainKey] as F with (NoLock)
		on
			E.[KeyId] = F.[KeyId]
		and	(@key is null or F.[KeyValue] like @key)

		inner join @entityIDs as G
		on
			B.[EntityID] = G.[ID]

		left outer join [mig_item].[Processed] as H -- No NoLock on the Processed table
		on
			H.[JobId] = @jobId
		and	H.[ItemId] = A.[ItemId]
	where
		H.[ItemId] is null -- Restart: Not already processed by this job
	and	(@partitionIndex is null or A.[ItemId] % @partitionCount = @partitionIndex)
GO
PRINT N'Alter Procedure [mig_item].[InsertProcessed]...';
GO
CREATE OR ALTER procedure [mig_item].[InsertProcessed]
	@itemId bigint
,	@jobId int
,	@jobInstanceId int
,	@level int = null
as
	insert [mig_item].[Processed] (
		[ItemId]
	,	[JobId]
	,	[JobInstanceId]
	,	[Level]
	,	[CreationDate]
	) values (
		@itemId 
	,	@jobId 
	,	@jobInstanceId
	,	@level
	,	sysdatetime()
	)
GO
PRINT N'Alter Procedure [mig_item].[InsertProcessedBatch]...';
GO
CREATE OR ALTER procedure [mig_item].[InsertProcessedBatch]
	@jobId int
,	@jobInstanceId int
,	@items [mig_item].[ItemIdList] readonly
as
	set nocount on;

	-- only insert those not already present
	insert into [mig_item].[Processed] (
		[ItemId]
	,	[JobId]
	,	[JobInstanceId]
	,	[CreationDate]
	)
	select
		A.[ItemId]
	,	@jobId
	,	@jobInstanceId
	,	sysdatetime()
	from 
		@items as A
		left join [mig_item].[Processed] as B
		on 
			B.[ItemId] = A.[ItemId]
		and	B.[JobId]  = @jobId
	where 
		B.[ItemId] is null
GO
PRINT N'Alter Procedure [mig_item].[Xml_InsertSource]...';
GO
CREATE OR ALTER procedure [mig_item].[Xml_InsertSource]
	@itemId bigint
,	@correlationID uniqueidentifier
,   @hash binary(32) = 0
,	@savingVersions bit
,   @xml varbinary(max) = null
as
	declare @xmlPrevious varbinary(max) 

	if 1 = @savingVersions
	begin 
		select 
			@xmlPrevious = [XmlCompressed] 
		from
			[mig_item].[SourceXml] with (NoLock)
		where
			[ItemId] = @ItemId
	end

	merge [mig_item].[SourceXml] as tgt
	using (
		select 
			@itemId as [ItemId]
		,	@correlationID as [CorrelationID]
		,	@hash as [Hash]
		,	@xml as [XmlCompressed]
		) as src
	on 
		tgt.[ItemId] = src.[ItemId]

	when matched then
		update set
			tgt.[CorrelationIDCreator] = @correlationID
		,	tgt.[CorrelationIDLast] = @correlationID
		,	tgt.[Hash] = src.[Hash]
		,	tgt.[Deleted] = 0
		,	tgt.[XmlCompressed] = src.[XmlCompressed]

	when not matched then
		insert (
			[ItemId]
		,	[CorrelationIDCreator]
		,	[CorrelationIDLast]
		,	[Hash]
		,	[Deleted]
		,	[XmlCompressed]
		) values (
			src.[ItemId]
		,	src.[CorrelationID]
		,	src.[CorrelationID]
		,	src.[Hash]
		,	0
		,	src.[XmlCompressed]
		);

	-- Return the previous compressed xml as a result set for efficient
	-- streamed decompressed in client
	select @xmlPrevious as [XmlPrevious]
GO
PRINT N'Alter Procedure [mig_item].[Xml_InsertExport]...';
GO
CREATE OR ALTER procedure [mig_item].[Xml_InsertExport]
	@itemId bigint
,	@correlationID uniqueidentifier
,   @hash binary(32) = 0
,	@savingVersions bit
,   @xml varbinary(max) = null
as
	declare @xmlPrevious varbinary(max) 

	if 1 = @savingVersions
	begin 
		select 
			@xmlPrevious = [XmlCompressed] 
		from
			[mig_item].[ExportXml] with (NoLock)
		where
			[ItemId] = @ItemId
	end

	merge [mig_item].[ExportXml] as tgt
	using (
		select 
			@itemId as [ItemId]
		,	@correlationID as [CorrelationID]
		,	@hash as [Hash]
		,	@xml as [XmlCompressed]
		) as src
	on 
		tgt.[ItemId] = src.[ItemId]

	when matched then
		update set
			tgt.[CorrelationIDCreator] = @correlationID
		,	tgt.[CorrelationIDLast] = @correlationID
		,	tgt.[Hash] = src.[Hash]
		,	tgt.[Deleted] = 0
		,	tgt.[XmlCompressed] = src.[XmlCompressed]

	when not matched then
		insert (
			[ItemId]
		,	[CorrelationIDCreator]
		,	[CorrelationIDLast]
		,	[Hash]
		,	[Deleted]
		,	[XmlCompressed]
		) values (
			src.[ItemId]
		,	src.[CorrelationID]
		,	src.[CorrelationID]
		,	src.[Hash]
		,	0
		,	src.[XmlCompressed]
		);

	-- Return the previous compressed xml as a result set for efficient
	-- streamed decompressed in client
	select @xmlPrevious as [XmlPrevious]
GO
PRINT N'Alter Procedure [mig_item].[Xml_InsertTarget]...';
GO
CREATE OR ALTER procedure [mig_item].[Xml_InsertTarget]
	@itemId bigint
,	@correlationID uniqueidentifier
,   @hash binary(32) = 0
,	@savingVersions bit
,   @xml varbinary(max) = null
as
	declare @xmlPrevious varbinary(max) 

	if 1 = @savingVersions
	begin 
		select 
			@xmlPrevious = [XmlCompressed] 
		from
			[mig_item].[TargetXml] with (NoLock)
		where
			[ItemId] = @ItemId
	end

	merge [mig_item].[TargetXml] as tgt
	using (
		select 
			@itemId as [ItemId]
		,	@correlationID as [CorrelationID]
		,	@hash as [Hash]
		,	@xml as [XmlCompressed]
		) as src
	on 
		tgt.[ItemId] = src.[ItemId]

	when matched then
		update set
			tgt.[CorrelationIDCreator] = @correlationID
		,	tgt.[CorrelationIDLast] = @correlationID
		,	tgt.[Hash] = src.[Hash]
		,	tgt.[Deleted] = 0
		,	tgt.[XmlCompressed] = src.[XmlCompressed]

	when not matched then
		insert (
			[ItemId]
		,	[CorrelationIDCreator]
		,	[CorrelationIDLast]
		,	[Hash]
		,	[Deleted]
		,	[XmlCompressed]
		) values (
			src.[ItemId]
		,	src.[CorrelationID]
		,	src.[CorrelationID]
		,	src.[Hash]
		,	0
		,	src.[XmlCompressed]
		);

	-- Return the previous compressed xml as a result set for efficient
	-- streamed decompressed in client
	select @xmlPrevious as [XmlPrevious]
GO
PRINT N'Alter Procedure [mig_item].[Xml_InsertExportEvent]...';
GO
CREATE OR ALTER procedure [mig_item].[Xml_InsertExportEvent]
	@itemId bigint
,	@correlationID uniqueidentifier
,   @hash binary(32) = 0
,	@savingVersions bit
,   @xml varbinary(max) = null
as
	declare @xmlPrevious varbinary(max) 

	if 1 = @savingVersions
	begin 
		select 
			@xmlPrevious = [XmlCompressed] 
		from
			[mig_item].[ExportEventXml] with (NoLock)
		where
			[ItemId] = @ItemId
	end

	merge [mig_item].[ExportEventXml] as tgt
	using (
		select 
			@itemId as [ItemId]
		,	@correlationID as [CorrelationID]
		,	@hash as [Hash]
		,	@xml as [XmlCompressed]
		) as src
	on 
		tgt.[ItemId] = src.[ItemId]

	when matched then
		update set
			tgt.[CorrelationIDCreator] = @correlationID
		,	tgt.[CorrelationIDLast] = @correlationID
		,	tgt.[Hash] = src.[Hash]
		,	tgt.[Deleted] = 0
		,	tgt.[XmlCompressed] = src.[XmlCompressed]

	when not matched then
		insert (
			[ItemId]
		,	[CorrelationIDCreator]
		,	[CorrelationIDLast]
		,	[Hash]
		,	[Deleted]
		,	[XmlCompressed]
		) values (
			src.[ItemId]
		,	src.[CorrelationID]
		,	src.[CorrelationID]
		,	src.[Hash]
		,	0
		,	src.[XmlCompressed]
		);

	-- Return the previous compressed xml as a result set for efficient
	-- streamed decompressed in client
	select @xmlPrevious as [XmlPrevious]
GO
PRINT N'Alter Procedure [mig_item].[Xml_InsertImportEvent]...';
GO
CREATE OR ALTER procedure [mig_item].[Xml_InsertImportEvent]
	@itemId bigint
,	@correlationID uniqueidentifier
,   @hash binary(32) = 0
,	@savingVersions bit
,   @xml varbinary(max) = null
as
	declare @xmlPrevious varbinary(max) 

	if 1 = @savingVersions
	begin 
		select 
			@xmlPrevious = [XmlCompressed] 
		from
			[mig_item].[ImportEventXml] with (NoLock)
		where
			[ItemId] = @ItemId
	end

	merge [mig_item].[ImportEventXml] as tgt
	using (
		select 
			@itemId as [ItemId]
		,	@correlationID as [CorrelationID]
		,	@hash as [Hash]
		,	@xml as [XmlCompressed]
		) as src
	on 
		tgt.[ItemId] = src.[ItemId]

	when matched then
		update set
			tgt.[CorrelationIDCreator] = @correlationID
		,	tgt.[CorrelationIDLast] = @correlationID
		,	tgt.[Hash] = src.[Hash]
		,	tgt.[Deleted] = 0
		,	tgt.[XmlCompressed] = src.[XmlCompressed]

	when not matched then
		insert (
			[ItemId]
		,	[CorrelationIDCreator]
		,	[CorrelationIDLast]
		,	[Hash]
		,	[Deleted]
		,	[XmlCompressed]
		) values (
			src.[ItemId]
		,	src.[CorrelationID]
		,	src.[CorrelationID]
		,	src.[Hash]
		,	0
		,	src.[XmlCompressed]
		);

	-- Return the previous compressed xml as a result set for efficient
	-- streamed decompressed in client
	select @xmlPrevious as [XmlPrevious]
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Clean up unnamed constraints'
PRINT N'----------------------------------------------------------------------------------------------------------------------';
GO
DECLARE @ConstraintName sysname;

SELECT @ConstraintName = dc.name
FROM sys.default_constraints dc
INNER JOIN sys.columns c 
    ON c.default_object_id = dc.object_id
WHERE 
    c.object_id = OBJECT_ID('[mig_item].[RelationshipKey]')
    AND c.name = 'CreationDate';

IF @ConstraintName IS NOT NULL AND @ConstraintName <> 'DF_RelationshipKey_CreationDate'
BEGIN
    EXEC('ALTER TABLE [mig_item].[RelationshipKey] DROP CONSTRAINT [' + @ConstraintName + ']');
	ALTER TABLE [mig_item].[RelationshipKey] ADD CONSTRAINT [DF_RelationshipKey_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
END

SELECT @ConstraintName = dc.name
FROM sys.default_constraints dc
INNER JOIN sys.columns c 
    ON c.default_object_id = dc.object_id
WHERE 
    c.object_id = OBJECT_ID('[mig_item].[RelationshipValue]')
    AND c.name = 'CreationDate';

IF @ConstraintName IS NOT NULL AND @ConstraintName <> 'DF_RelationshipValue_CreationDate'
BEGIN
    EXEC('ALTER TABLE [mig_item].[RelationshipValue] DROP CONSTRAINT [' + @ConstraintName + ']');
	ALTER TABLE [mig_item].[RelationshipValue] ADD CONSTRAINT [DF_RelationshipValue_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
END
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Release 2.5 end end'
PRINT N'----------------------------------------------------------------------------------------------------------------------';
GO
