/******************************************************************/
/* NB: Please run setup script in SQLCMD mode */
/******************************************************************/
:setvar Utility "MigFx_Utility" -- Set this SQLCMD variable to the name of the MigFx_Utility database
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
GO
PRINT N'Creating Schema [mig_backup]...';
GO
CREATE SCHEMA [mig_backup]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_concurrency]...';
GO
CREATE SCHEMA [mig_concurrency]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_director]...';
GO
CREATE SCHEMA [mig_director]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_garbage]...';
GO
CREATE SCHEMA [mig_garbage]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_item]...';
GO
CREATE SCHEMA [mig_item]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_profile]...';
GO
CREATE SCHEMA [mig_profile]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_project]...';
GO
CREATE SCHEMA [mig_project]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_schema]...';
GO
CREATE SCHEMA [mig_schema]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_temp]...';
GO
CREATE SCHEMA [mig_temp]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_tracking]...';
GO
CREATE SCHEMA [mig_tracking]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_translation]...';
GO
CREATE SCHEMA [mig_translation]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_utility]...';
GO
CREATE SCHEMA [mig_utility]
AUTHORIZATION [dbo];
GO
PRINT N'Creating Schema [mig_valid]...';
GO
CREATE SCHEMA [mig_valid]
AUTHORIZATION [dbo];
GO
PRINT N'Creating User-Defined Table Type [mig_garbage].[InvalidateSet]...';
GO
CREATE TYPE [mig_garbage].[InvalidateSet] AS TABLE (
[ItemId] BIGINT NOT NULL,
[Step] TINYINT NULL,
[InvalidJobId] INT NULL,
[InvalidJobInstanceId] INT NULL,
[ValidJobId] INT NULL,
[ValidJobInstanceId] INT NULL);
GO
PRINT N'Creating User-Defined Table Type [mig_item].[ExportItemList]...';
GO
CREATE TYPE [mig_item].[ExportItemList] AS TABLE (
[ExportID] UNIQUEIDENTIFIER NOT NULL,
[BreakFields] VARCHAR (512) NOT NULL,
PRIMARY KEY CLUSTERED ([ExportID] ASC));
GO
PRINT N'Creating Partition Function [RelationshipRange]...';
GO
CREATE PARTITION FUNCTION [RelationshipRange](BIGINT)
AS RANGE
FOR VALUES (0);
GO
PRINT N'Creating Partition Scheme [RelationshipScheme]...';
GO
CREATE PARTITION SCHEME [RelationshipScheme]
AS PARTITION [RelationshipRange]
TO ([PRIMARY], [PRIMARY], [PRIMARY]);
GO
PRINT N'Creating Table [mig_concurrency].[Token]...';
GO
CREATE TABLE [mig_concurrency].[Token] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[TagId] TINYINT NOT NULL,
[Tag] VARCHAR (50) NOT NULL,
[Available] BIT NOT NULL,
[ProcessId] INT NULL,
[ProcessName] VARCHAR (50) NULL,
[Attempts] INT NULL,
CONSTRAINT [PK_Token] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_concurrency].[Token].[IX_Token]...';
GO
CREATE NONCLUSTERED INDEX [IX_Token]
ON [mig_concurrency].[Token]([TagId] ASC, [Available] ASC)
INCLUDE([ProcessId]);
GO
PRINT N'Creating Table [mig_director].[ExportEntitySourceDependency]...';
GO
CREATE TABLE [mig_director].[ExportEntitySourceDependency] (
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[SourceID] UNIQUEIDENTIFIER NOT NULL,
[SourceType] VARCHAR (50) NOT NULL,
[IsPresent] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportEntitySourceDependency] PRIMARY KEY CLUSTERED ([EntityID] ASC, [SourceID] ASC, [SourceType] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ExportEntity]...';
GO
CREATE TABLE [mig_director].[ExportEntity] (
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[EntityName] VARCHAR (256) NOT NULL,
[BusinessEntityID] UNIQUEIDENTIFIER NOT NULL,
[IsPresent] BIT NOT NULL,
[Ignored] BIT NOT NULL,
[LastSynchronizeTime] DATETIME2 (7) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportEntity_1] PRIMARY KEY CLUSTERED ([EntityID] ASC)
);
GO
PRINT N'Creating Table [mig_director].[DirectorOption]...';
GO
CREATE TABLE [mig_director].[DirectorOption] (
[OptionName] NVARCHAR (50) NOT NULL,
[OptionValue] NVARCHAR (MAX) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
[ModificationDate] DATETIME2 (7) NULL,
CONSTRAINT [PK_Options] PRIMARY KEY CLUSTERED ([OptionName] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ValuesetProvider]...';
GO
CREATE TABLE [mig_director].[ValuesetProvider] (
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[ProjectID] UNIQUEIDENTIFIER NOT NULL,
[ProjectType] VARCHAR (50) NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[Xml] XML NOT NULL,
[Present] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
[CreatedBy] VARCHAR (50) NOT NULL,
[ModificationDate] DATETIME2 (7) NULL,
[ModifiedBy] VARCHAR (50) NULL,
CONSTRAINT [PK_ValuesetProvider] PRIMARY KEY CLUSTERED ([ProjectID] ASC, [ItemID] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ValuesetBaseline]...';
GO
CREATE TABLE [mig_director].[ValuesetBaseline] (
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[ProjectID] UNIQUEIDENTIFIER NOT NULL,
[BaselineCount] INT CONSTRAINT [ValuesetBaseline_BaselineCount] DEFAULT ((0)) NOT NULL,
[CreationDate] DATETIME2 (2) CONSTRAINT [ValuesetBaseline_CreationDate] DEFAULT (sysdatetime()) NOT NULL,
CONSTRAINT [PK_ValuesetBaseline] PRIMARY KEY CLUSTERED ([ItemID] ASC, [ProjectID] ASC)
);
GO
PRINT N'Creating Table [mig_director].[Valueset]...';
GO
CREATE TABLE [mig_director].[Valueset] (
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[FullName] AS (([SchemaName] + '.') + [Name]),
[Name] VARCHAR (50) NOT NULL,
[SchemaName] VARCHAR (50) 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) NOT NULL,
[CreatedBy] VARCHAR (50) NOT NULL,
[ModificationDate] DATETIME2 (7) NULL,
[ModifiedBy] VARCHAR (50) NULL,
CONSTRAINT [PK_Valueset] PRIMARY KEY CLUSTERED ([ProjectID] ASC, [ItemID] ASC) WITH (FILLFACTOR = 70, PAD_INDEX = ON)
);
GO
PRINT N'Creating Table [mig_director].[Concurrency]...';
GO
CREATE TABLE [mig_director].[Concurrency] (
[Tag] VARCHAR (256) NOT NULL,
[Limit] SMALLINT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Concurrency] PRIMARY KEY CLUSTERED ([Tag] ASC)
);
GO
PRINT N'Creating Table [mig_director].[Command]...';
GO
CREATE TABLE [mig_director].[Command] (
[CommandId] BIGINT IDENTITY (1, 1) NOT NULL,
[CommandType] VARCHAR (50) NOT NULL,
[StartExecutionDate] DATETIME2 (7) NULL,
[EndExecutionDate] DATETIME2 (7) NULL,
[Elapsed] BIGINT NULL,
[Parameters] XML NOT NULL,
[SyncResult] VARCHAR (MAX) NULL,
[SyncError] BIT NULL,
[CreatedBy] VARCHAR (50) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Command] PRIMARY KEY CLUSTERED ([CommandId] ASC) WITH (FILLFACTOR = 70, PAD_INDEX = ON)
);
GO
PRINT N'Creating Table [mig_director].[RunParameter]...';
GO
CREATE TABLE [mig_director].[RunParameter] (
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[ProjectID] UNIQUEIDENTIFIER NOT NULL,
[ProjectType] VARCHAR (50) NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[Value] VARCHAR (MAX) NOT NULL,
[Description] VARCHAR (MAX) NOT NULL,
[DataTypeXml] XML NULL,
[Present] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
[CreatedBy] VARCHAR (50) NOT NULL,
[ModificationDate] DATETIME2 (7) NULL,
[ModifiedBy] VARCHAR (50) NULL,
CONSTRAINT [PK_RunParameter] PRIMARY KEY CLUSTERED ([ProjectID] ASC, [ItemID] ASC)
);
GO
PRINT N'Creating Table [mig_director].[JobInstanceLog]...';
GO
CREATE TABLE [mig_director].[JobInstanceLog] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[JobInstanceId] INT NOT NULL,
[LogType] TINYINT NOT NULL,
[Message] VARCHAR (MAX) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_JobLog] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_director].[JobInstanceLog].[IX_JobLog_JobInstanceId]...';
GO
CREATE NONCLUSTERED INDEX [IX_JobLog_JobInstanceId]
ON [mig_director].[JobInstanceLog]([JobInstanceId] DESC, [CreationDate] ASC, [Id] ASC);
GO
PRINT N'Creating Table [mig_director].[JobInstance]...';
GO
CREATE TABLE [mig_director].[JobInstance] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[JobId] INT NOT NULL,
[ClientID] UNIQUEIDENTIFIER NOT NULL,
[DebugWait] BIT NOT NULL,
[Priority] TINYINT NOT NULL,
[ExecutionState] TINYINT NOT NULL,
[Result] TINYINT NOT NULL,
[TimeStart] DATETIME2 (7) NULL,
[TimeEnd] DATETIME2 (7) NULL,
[ProcessId] INT NULL,
[ProcessName] VARCHAR (256) NULL,
[RuntimeVersion] VARCHAR (50) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
[CreatedBy] VARCHAR (50) NOT NULL,
CONSTRAINT [PK_JobInstance] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_director].[JobInstance].[IX_JobInstance_JobId]...';
GO
CREATE NONCLUSTERED INDEX [IX_JobInstance_JobId]
ON [mig_director].[JobInstance]([JobId] DESC);
GO
PRINT N'Creating Index [mig_director].[JobInstance].[IX_JobInstance_JobList]...';
GO
CREATE NONCLUSTERED INDEX [IX_JobInstance_JobList]
ON [mig_director].[JobInstance]([JobId] ASC, [Id] ASC)
INCLUDE([TimeStart], [TimeEnd], [ExecutionState]);
GO
PRINT N'Creating Index [mig_director].[JobInstance].[IX_JobInstance_ClientID]...';
GO
CREATE NONCLUSTERED INDEX [IX_JobInstance_ClientID]
ON [mig_director].[JobInstance]([ClientID] ASC)
INCLUDE([Result], [ExecutionState]);
GO
PRINT N'Creating Table [mig_director].[Job]...';
GO
CREATE TABLE [mig_director].[Job] (
[JobId] INT IDENTITY (1, 1) NOT NULL,
[JobTypeId] INT NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[ClassName] VARCHAR (256) NOT NULL,
[Header] VARCHAR (256) NULL,
[Parameters] XML NOT NULL,
[RestartArea] XML NULL,
[BatchSize] INT NOT NULL,
[CanCancel] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED ([JobId] ASC)
);
GO
PRINT N'Creating Index [mig_director].[Job].[IX_Job]...';
GO
CREATE NONCLUSTERED INDEX [IX_Job]
ON [mig_director].[Job]([JobId] ASC)
INCLUDE([Name], [Header], [CanCancel], [CreationDate]);
GO
PRINT N'Creating Table [mig_director].[ItemSet]...';
GO
CREATE TABLE [mig_director].[ItemSet] (
[SetId] INT IDENTITY (1, 1) NOT NULL,
[Type] VARCHAR (50) NOT NULL,
[Source] VARCHAR (MAX) NOT NULL,
[ItemCount] INT NULL,
[OrderDate] DATETIME2 (7) NOT NULL,
[ReadyDate] DATETIME2 (7) NULL,
CONSTRAINT [PK_ItemSet] PRIMARY KEY CLUSTERED ([SetId] ASC)
);
GO
PRINT N'Creating Table [mig_director].[GlobalCounter]...';
GO
CREATE TABLE [mig_director].[GlobalCounter] (
[CounterId] VARCHAR (50) NOT NULL,
[NextIntervalStart] BIGINT NOT NULL,
CONSTRAINT [PK_GlobalCounter] PRIMARY KEY CLUSTERED ([CounterId] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ExportSourceViewDependency]...';
GO
CREATE TABLE [mig_director].[ExportSourceViewDependency] (
[ViewID] UNIQUEIDENTIFIER NOT NULL,
[DependentOnID] UNIQUEIDENTIFIER NOT NULL,
[SourceType] VARCHAR (50) NOT NULL,
[IsPresent] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportSourceViewDependency] PRIMARY KEY CLUSTERED ([ViewID] ASC, [DependentOnID] ASC, [SourceType] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ExportSourceView_Baseline]...';
GO
CREATE TABLE [mig_director].[ExportSourceView_Baseline] (
[FullName] AS (([ViewSchema] + '.') + [ViewName]) PERSISTED NOT NULL,
[ViewSchema] VARCHAR (50) NOT NULL,
[ViewName] VARCHAR (50) NOT NULL,
[BaselineCount] INT NOT NULL,
[ModificationDate] DATETIME2 (7) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportSourceView_Baseline] PRIMARY KEY CLUSTERED ([FullName] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ExportSourceView]...';
GO
CREATE TABLE [mig_director].[ExportSourceView] (
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[FullName] AS (([ViewSchema] + '.') + [ViewName]) PERSISTED NOT NULL,
[ViewSchema] VARCHAR (50) NOT NULL,
[ViewName] VARCHAR (50) NOT NULL,
[IsPresent] BIT NOT NULL,
[LoadedDate] DATETIME2 (7) NULL,
[LoadedElapsedTicks] BIGINT NULL,
[LoadedCount] INT NULL,
[ForceInScope] BIT NOT NULL,
[TestableQuery] VARCHAR (MAX) NOT NULL,
[AcceptedQueryPlan] VARCHAR (MAX) NULL,
[OptimizeIterations] INT NULL,
[OptimizeDate] DATETIME2 (7) NULL,
[CreationDate] DATETIME2 (7) NULL,
CONSTRAINT [PK_ExportSourceView] PRIMARY KEY CLUSTERED ([ItemID] ASC)
);
GO
PRINT N'Creating Index [mig_director].[ExportSourceView].[IX_ExportSourceView]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportSourceView]
ON [mig_director].[ExportSourceView]([FullName] ASC);
GO
PRINT N'Creating Table [mig_director].[ExportSourceTable_Baseline]...';
GO
CREATE TABLE [mig_director].[ExportSourceTable_Baseline] (
[FullName] AS (([TableSchema] + '.') + [TableName]) PERSISTED NOT NULL,
[TableSchema] VARCHAR (50) NOT NULL,
[TableName] VARCHAR (50) NOT NULL,
[BaselineCount] INT NOT NULL,
[ModificationDate] DATETIME2 (7) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportSourceTable_Baseline] PRIMARY KEY CLUSTERED ([FullName] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ExportSourceTable]...';
GO
CREATE TABLE [mig_director].[ExportSourceTable] (
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[FullName] AS (([TableSchema] + '.') + [TableName]) PERSISTED NOT NULL,
[TableSchema] VARCHAR (50) NOT NULL,
[TableName] VARCHAR (128) NOT NULL,
[ReformatExtensionID] UNIQUEIDENTIFIER NULL,
[LoadedCount] INT NULL,
[IsPresent] BIT NOT NULL,
[ForceInScope] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportSourceTable] PRIMARY KEY CLUSTERED ([ItemID] ASC)
);
GO
PRINT N'Creating Index [mig_director].[ExportSourceTable].[IX_ExportSourceTable]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportSourceTable]
ON [mig_director].[ExportSourceTable]([FullName] ASC);
GO
PRINT N'Creating Table [mig_director].[ExportSourceFile]...';
GO
CREATE TABLE [mig_director].[ExportSourceFile] (
[FileName] VARCHAR (512) NOT NULL,
[FullName] VARCHAR (1024) NOT NULL,
[FileDate] DATETIME2 (7) NULL,
[SourceTableFullName] VARCHAR (512) NOT NULL,
[ReformatExtensionID] UNIQUEIDENTIFIER NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportSourceFile] PRIMARY KEY CLUSTERED ([FileName] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ExportFormattedFile]...';
GO
CREATE TABLE [mig_director].[ExportFormattedFile] (
[FileName] VARCHAR (512) NOT NULL,
[FullName] VARCHAR (1024) NOT NULL,
[FileDate] DATETIME2 (7) NULL,
[DateOfLoadedFile] DATETIME2 (7) NULL,
[LoadedDate] DATETIME2 (7) NULL,
[LoadedCount] INT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportFormattedFile] PRIMARY KEY CLUSTERED ([FileName] ASC)
);
GO
PRINT N'Creating Table [mig_director].[JobData]...';
GO
CREATE TABLE [mig_director].[JobData] (
[JobId] INT NOT NULL,
[Type] VARCHAR (50) NOT NULL,
[Content] VARCHAR (MAX) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_JobData] PRIMARY KEY CLUSTERED ([JobId] ASC, [Type] ASC)
);
GO
PRINT N'Creating Table [mig_director].[State]...';
GO
CREATE TABLE [mig_director].[State] (
[Name] VARCHAR (50) NOT NULL,
[Value] VARCHAR (MAX) NOT NULL,
CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED ([Name] ASC)
);
GO
PRINT N'Creating Table [mig_director].[JobInstanceRunParameter]...';
GO
CREATE TABLE [mig_director].[JobInstanceRunParameter] (
[JobInstanceId] INT NOT NULL,
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[Value] VARCHAR (MAX) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
PRIMARY KEY CLUSTERED ([JobInstanceId] ASC, [ItemID] ASC)
);
GO
PRINT N'Creating Index [mig_director].[JobInstanceRunParameter].[IX_JobInstance_1]...';
GO
CREATE NONCLUSTERED INDEX [IX_JobInstance_1]
ON [mig_director].[JobInstanceRunParameter]([JobInstanceId] ASC)
INCLUDE([ItemID], [Name], [Value]);
GO
PRINT N'Creating Table [mig_director].[BusinessEntity]...';
GO
CREATE TABLE [mig_director].[BusinessEntity] (
[BusinessEntityId] UNIQUEIDENTIFIER NOT NULL,
[BusinessEntityName] VARCHAR (128) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_BusinessEntity] PRIMARY KEY CLUSTERED ([BusinessEntityId] ASC)
);
GO
PRINT N'Creating Table [mig_director].[Engine]...';
GO
CREATE TABLE [mig_director].[Engine] (
[ProjectID] UNIQUEIDENTIFIER NOT NULL,
[ProjectType] VARCHAR (50) NOT NULL,
[DataServicesHash] CHAR (32) NOT NULL,
[Commit] BIGINT NOT NULL,
[CleanWorkspace] BIT NOT NULL,
CONSTRAINT [PK_Engine] PRIMARY KEY CLUSTERED ([ProjectID] ASC)
);
GO
PRINT N'Creating Table [mig_director].[ExtensionUsage]...';
GO
CREATE TABLE [mig_director].[ExtensionUsage] (
[ID] UNIQUEIDENTIFIER NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[IsDefault] BIT NOT NULL,
[ExtensionType] TINYINT NOT NULL,
[ExtensionClassName] VARCHAR (MAX) NOT NULL,
[ExtensionDisplayName] VARCHAR (MAX) NOT NULL,
[ParameterValues] XML NULL,
CONSTRAINT [PK_ExtensionUsage] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO
PRINT N'Creating Table [mig_garbage].[Log]...';
GO
CREATE TABLE [mig_garbage].[Log] (
[LogTime] DATETIME2 (7) NOT NULL,
[TableName] VARCHAR (50) NOT NULL,
[Statement] VARCHAR (MAX) NOT NULL,
[Deleted] INT NOT NULL,
[LastDeleteTime] DATETIME2 (7) NULL,
[Reported] INT NOT NULL,
[LastReportTime] DATETIME2 (7) NULL,
[StartTime] DATETIME2 (7) NULL,
[EndTime] DATETIME2 (7) NULL,
[ErrorMessage] VARCHAR (MAX) NULL,
CONSTRAINT [PK_LogDetail] PRIMARY KEY CLUSTERED ([LogTime] ASC, [TableName] ASC)
);
GO
PRINT N'Creating Index [mig_garbage].[Log].[IX_Report]...';
GO
CREATE NONCLUSTERED INDEX [IX_Report]
ON [mig_garbage].[Log]([LastReportTime] ASC, [LastDeleteTime] ASC);
GO
PRINT N'Creating Table [mig_item].[Garbage]...';
GO
CREATE TABLE [mig_item].[Garbage] (
[_Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[InvalidatedTime] DATETIME2 (7) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Garbage] PRIMARY KEY CLUSTERED ([_Id] ASC)
);
GO
PRINT N'Creating Index [mig_item].[Garbage].[UX_Garbage]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [UX_Garbage]
ON [mig_item].[Garbage]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Table [mig_item].[Active]...';
GO
CREATE TABLE [mig_item].[Active] (
[ItemId] BIGINT NOT NULL,
[Step] TINYINT NOT NULL,
[JobId] INT NULL,
[JobInstanceId] INT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Active] PRIMARY KEY CLUSTERED ([ItemId] ASC, [Step] ASC)
);
GO
PRINT N'Creating Index [mig_item].[Active].[IX_Active_1]...';
GO
CREATE NONCLUSTERED INDEX [IX_Active_1]
ON [mig_item].[Active]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC);
GO
PRINT N'Creating Index [mig_item].[Active].[IX_Active_2]...';
GO
CREATE NONCLUSTERED INDEX [IX_Active_2]
ON [mig_item].[Active]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Index [mig_item].[Active].[IX_Active_Unload]...';
GO
CREATE NONCLUSTERED INDEX [IX_Active_Unload]
ON [mig_item].[Active]([Step] ASC)
INCLUDE([ItemId], [JobId], [JobInstanceId]);
GO
PRINT N'Creating Table [mig_item].[Identity]...';
GO
CREATE TABLE [mig_item].[Identity] (
[EntityName] VARCHAR (64) NOT NULL,
[TargetEntityID] UNIQUEIDENTIFIER NOT NULL,
[TargetFieldID] UNIQUEIDENTIFIER NOT NULL,
[Xpath] VARCHAR (512) NOT NULL,
[KeyString] VARCHAR (1024) NOT NULL,
[NumberValue] BIGINT IDENTITY (1, 1) NOT NULL,
[StringValue] VARCHAR (MAX) NULL,
[GuidValue] UNIQUEIDENTIFIER NULL,
[CreationDate] DATETIME2 (7) NOT NULL
);
GO
PRINT N'Creating Index [mig_item].[Identity].[IX_Identity]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Identity]
ON [mig_item].[Identity]([KeyString] ASC, [Xpath] ASC, [TargetFieldID] ASC, [TargetEntityID] ASC, [EntityName] ASC)
INCLUDE([NumberValue], [StringValue], [GuidValue]) WITH (FILLFACTOR = 70, PAD_INDEX = ON);
GO
PRINT N'Creating Table [mig_item].[SourceXml]...';
GO
CREATE TABLE [mig_item].[SourceXml] (
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[XmlData] AS (CASE WHEN [_XmlUncompressed] IS NULL THEN CONVERT (VARCHAR (MAX), Decompress([_XmlCompressed])) ELSE [_XmlUncompressed] END),
[_XmlCompressed] VARBINARY (MAX) NULL,
[_XmlUncompressed] VARCHAR (MAX) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_SourceXml] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);
GO
PRINT N'Creating Index [mig_item].[SourceXml].[IX_SourceXml_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_SourceXml_Valid]
ON [mig_item].[SourceXml]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC);
GO
PRINT N'Creating Table [mig_item].[SavedString]...';
GO
CREATE TABLE [mig_item].[SavedString] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[KeyValue] VARCHAR (256) NOT NULL,
[StringValue] NVARCHAR (MAX) NOT NULL,
[CreationDate] DATETIME2 (2) NOT NULL,
CONSTRAINT [PK_SavedString] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_item].[SavedString].[IX_SavedString_KeyValue]...';
GO
CREATE NONCLUSTERED INDEX [IX_SavedString_KeyValue]
ON [mig_item].[SavedString]([KeyValue] ASC);
GO
PRINT N'Creating Index [mig_item].[SavedString].[IX_SavedString_ItemId]...';
GO
CREATE NONCLUSTERED INDEX [IX_SavedString_ItemId]
ON [mig_item].[SavedString]([ItemId] ASC);
GO
PRINT N'Creating Index [mig_item].[SavedString].[IX_SavedString_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_SavedString_Valid]
ON [mig_item].[SavedString]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Table [mig_item].[RelationshipValue]...';
GO
CREATE TABLE [mig_item].[RelationshipValue] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[ItemPath] VARCHAR (256) NOT NULL,
[ValueType] TINYINT NOT NULL,
[KeyValues] VARCHAR (512) NOT NULL,
[FieldId] UNIQUEIDENTIFIER NOT NULL,
[Value] VARCHAR (MAX) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_RelationshipValue] PRIMARY KEY CLUSTERED ([Id] ASC, [ItemId] ASC) WITH (FILLFACTOR = 80, PAD_INDEX = ON)
ON [RelationshipScheme] ([ItemId])
);
GO
PRINT N'Creating Index [mig_item].[RelationshipValue].[IX_RelationshipValue_GetValue]...';
GO
CREATE NONCLUSTERED INDEX [IX_RelationshipValue_GetValue]
ON [mig_item].[RelationshipValue]([FieldId] ASC, [ItemPath] ASC, [KeyValues] ASC)
INCLUDE([ItemId], [JobId], [JobInstanceId], [Step], [Value]) WITH (FILLFACTOR = 70, PAD_INDEX = ON);
GO
PRINT N'Creating Index [mig_item].[RelationshipValue].[IX_RelationshipValue_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_RelationshipValue_Valid]
ON [mig_item].[RelationshipValue]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Table [mig_item].[RelationshipBagPropertyValue]...';
GO
CREATE TABLE [mig_item].[RelationshipBagPropertyValue] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[ItemPath] VARCHAR (256) NOT NULL,
[KeyValues] VARCHAR (512) NOT NULL,
[Bag] VARCHAR (256) NOT NULL,
[Property] VARCHAR (256) NOT NULL,
[Value] XML NOT NULL,
[CreationDate] DATETIME2 (7) CONSTRAINT [DF_RelationshipBagPropertyValue_CreationDate] DEFAULT (sysdatetime()) NOT NULL,
CONSTRAINT [PK_RelationshipBagPropertyValue] PRIMARY KEY CLUSTERED ([Id] ASC, [ItemId] ASC) WITH (FILLFACTOR = 80, PAD_INDEX = ON)
ON [RelationshipScheme] ([ItemId])
);
GO
PRINT N'Creating Index [mig_item].[RelationshipBagPropertyValue].[X_RelationshipValue_GetValue]...';
GO
CREATE NONCLUSTERED INDEX [IX_RelationshipBagPropertyValue_GetValue]
ON [mig_item].[RelationshipBagPropertyValue]([Bag] ASC, [Property] ASC, [ItemPath] ASC, [KeyValues] ASC)
INCLUDE([ItemId], [JobId], [JobInstanceId], [Step], [Value]) WITH (FILLFACTOR = 70, PAD_INDEX = ON);
GO
PRINT N'Creating Index [mig_item].[RelationshipBagPropertyValue].[IX_RelationshipBagPropertyValue_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_RelationshipBagPropertyValue_Valid]
ON [mig_item].[RelationshipBagPropertyValue]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Table [mig_item].[RelationshipKey]...';
GO
CREATE TABLE [mig_item].[RelationshipKey] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[ItemPath] VARCHAR (256) NOT NULL,
[KeyValues] VARCHAR (512) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_RelationshipKey] PRIMARY KEY CLUSTERED ([Id] ASC, [ItemId] ASC) WITH (FILLFACTOR = 80, PAD_INDEX = ON)
ON [RelationshipScheme] ([ItemId])
);
GO
PRINT N'Creating Index [mig_item].[RelationshipKey].[IX_RelationshipKey_ForExists]...';
GO
CREATE NONCLUSTERED INDEX [IX_RelationshipKey_ForExists]
ON [mig_item].[RelationshipKey]([ItemPath] ASC, [KeyValues] ASC)
INCLUDE([ItemId], [JobId], [JobInstanceId], [Step]) WITH (FILLFACTOR = 70, PAD_INDEX = ON);
GO
PRINT N'Creating Index [mig_item].[RelationshipKey].[IX_RelationshipKey_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_RelationshipKey_Valid]
ON [mig_item].[RelationshipKey]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Table [mig_item].[Processed]...';
GO
CREATE TABLE [mig_item].[Processed] (
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[ElapsedTicks] BIGINT NOT NULL,
[Level] INT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Processed] PRIMARY KEY CLUSTERED ([ItemId] ASC, [JobId] ASC, [Step] ASC)
);
GO
PRINT N'Creating Index [mig_item].[Processed].[IX_Processed_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_Processed_Valid]
ON [mig_item].[Processed]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Table [mig_item].[MainKey]...';
GO
CREATE TABLE [mig_item].[MainKey] (
[KeyId] BIGINT IDENTITY (1, 1) NOT NULL,
[BusinessEntityName] VARCHAR (50) NOT NULL,
[KeyValue] VARCHAR (512) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_MainKey] PRIMARY KEY CLUSTERED ([KeyId] ASC)
);
GO
PRINT N'Creating Index [mig_item].[MainKey].[IX_MainKey]...';
GO
CREATE NONCLUSTERED INDEX [IX_MainKey]
ON [mig_item].[MainKey]([KeyId] ASC, [BusinessEntityName] ASC);
GO
PRINT N'Creating Index [mig_item].[MainKey].[UX_MainKey]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [UX_MainKey]
ON [mig_item].[MainKey]([KeyValue] ASC, [BusinessEntityName] ASC);
GO
PRINT N'Creating Index [mig_item].[MainKey].[UX_MainKey_Covering]...';
GO
CREATE NONCLUSTERED INDEX [UX_MainKey_Covering]
ON [mig_item].[MainKey]([KeyId] ASC)
INCLUDE([KeyValue], [BusinessEntityName]);
GO
PRINT N'Creating Table [mig_item].[ItemSet]...';
GO
CREATE TABLE [mig_item].[ItemSet] (
[_Id] BIGINT IDENTITY (1, 1) NOT NULL,
[SetId] INT NOT NULL,
[ItemId] BIGINT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Item_ItemSet] PRIMARY KEY CLUSTERED ([_Id] ASC)
);
GO
PRINT N'Creating Table [mig_item].[Item]...';
GO
CREATE TABLE [mig_item].[Item] (
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[KeyId] BIGINT NOT NULL,
[PartitionValue] VARCHAR (50) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Item_Item] PRIMARY KEY CLUSTERED ([ItemId] ASC, [JobId] ASC)
);
GO
PRINT N'Creating Index [mig_item].[Item].[UX_Item_ItemIdJobId_KeyId]...';
GO
CREATE NONCLUSTERED INDEX [UX_Item_ItemIdJobId_KeyId]
ON [mig_item].[Item]([ItemId] ASC, [JobId] ASC, [KeyId] ASC);
GO
PRINT N'Creating Index [mig_item].[Item].[IX_Item_KeyId]...';
GO
CREATE NONCLUSTERED INDEX [IX_Item_KeyId]
ON [mig_item].[Item]([KeyId] ASC);
GO
PRINT N'Creating Index [mig_item].[Item].[IX_Item_Cover]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Item_Cover]
ON [mig_item].[Item]([ItemId] ASC)
INCLUDE([KeyId], [PartitionValue]);
GO
PRINT N'Creating Table [mig_item].[Id]...';
GO
CREATE TABLE [mig_item].[Id] (
[ItemId] BIGINT IDENTITY (1, 1) NOT NULL,
[InvalidatedTime] DATETIME2 (7) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);
GO
PRINT N'Creating Index [mig_item].[Id].[IX_Id]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Id]
ON [mig_item].[Id]([ItemId] ASC)
INCLUDE([InvalidatedTime]);
GO
PRINT N'Creating Index [mig_item].[Id].[IX_Id_valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_Id_valid]
ON [mig_item].[Id]([InvalidatedTime] ASC)
INCLUDE([ItemId]);
GO
PRINT N'Creating Table [mig_item].[ExportXml]...';
GO
CREATE TABLE [mig_item].[ExportXml] (
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[XmlData] AS (CASE WHEN [_XmlUncompressed] IS NULL THEN CONVERT (VARCHAR (MAX), Decompress([_XmlCompressed])) ELSE [_XmlUncompressed] END),
[_XmlCompressed] VARBINARY (MAX) NULL,
[_XmlUncompressed] VARCHAR (MAX) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportXml] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);
GO
PRINT N'Creating Index [mig_item].[ExportXml].[IX_ExportXml_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportXml_Valid]
ON [mig_item].[ExportXml]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC);
GO
PRINT N'Creating Table [mig_item].[ExportKey]...';
GO
CREATE TABLE [mig_item].[ExportKey] (
[ExportID] UNIQUEIDENTIFIER NOT NULL,
[ItemId] BIGINT NOT NULL,
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[BreakFields] VARCHAR (512) NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportKey] PRIMARY KEY CLUSTERED ([ExportID] ASC)
);
GO
PRINT N'Creating Index [mig_item].[ExportKey].[IX_ExportKey_2]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportKey_2]
ON [mig_item].[ExportKey]([ItemId] ASC)
INCLUDE([EntityID]);
GO
PRINT N'Creating Index [mig_item].[ExportKey].[IX_ExportKey_Delete]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportKey_Delete]
ON [mig_item].[ExportKey]([EntityID] ASC, [JobId] ASC, [ExportID] ASC);
GO
PRINT N'Creating Index [mig_item].[ExportKey].[IX_ExportKey_1]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_ExportKey_1]
ON [mig_item].[ExportKey]([EntityID] ASC, [ItemId] ASC);
GO
PRINT N'Creating Index [mig_item].[ExportKey].[IX_ExportKey_3]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_ExportKey_3]
ON [mig_item].[ExportKey]([ExportID] ASC, [ItemId] ASC, [EntityID] ASC, [JobId] ASC);
GO
PRINT N'Creating Index [mig_item].[ExportKey].[IX_ExportKey_Delete_2]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportKey_Delete_2]
ON [mig_item].[ExportKey]([EntityID] ASC)
INCLUDE([ExportID], [ItemId], [JobId], [JobInstanceId], [CreationDate]);
GO
PRINT N'Creating Table [mig_item].[ExportItem]...';
GO
CREATE TABLE [mig_item].[ExportItem] (
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Result] TINYINT NOT NULL,
[SourceKey] VARCHAR (512) NOT NULL,
[Commit] BIGINT NOT NULL,
[CleanWorkspace] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ExportItem] PRIMARY KEY CLUSTERED ([ItemId] ASC, [JobId] ASC)
);
GO
PRINT N'Creating Index [mig_item].[ExportItem].[IX_ExportItem_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportItem_Valid]
ON [mig_item].[ExportItem]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC)
INCLUDE([Result]);
GO
PRINT N'Creating Index [mig_item].[ExportItem].[IX_ExportItem_Unload]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportItem_Unload]
ON [mig_item].[ExportItem]([Result] ASC)
INCLUDE([ItemId], [JobId], [JobInstanceId]);
GO
PRINT N'Creating Index [mig_item].[ExportItem].[IX_ExportItem_List]...';
GO
CREATE NONCLUSTERED INDEX [IX_ExportItem_List]
ON [mig_item].[ExportItem]([JobId] ASC, [Result] ASC)
INCLUDE([ItemId]);
GO
PRINT N'Creating Table [mig_item].[Dependency]...';
GO
CREATE TABLE [mig_item].[Dependency] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[DependentOnKeyId] BIGINT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Dependency] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_item].[Dependency].[UX_Dependency_1]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [UX_Dependency_1]
ON [mig_item].[Dependency]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC, [DependentOnKeyId] ASC);
GO
PRINT N'Creating Index [mig_item].[Dependency].[IX_Dependency_2]...';
GO
CREATE NONCLUSTERED INDEX [IX_Dependency_2]
ON [mig_item].[Dependency]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Index [mig_item].[Dependency].[UX_Dependency_DependentOn]...';
GO
CREATE NONCLUSTERED INDEX [UX_Dependency_DependentOn]
ON [mig_item].[Dependency]([DependentOnKeyId] ASC);
GO
PRINT N'Creating Table [mig_item].[TargetXml]...';
GO
CREATE TABLE [mig_item].[TargetXml] (
[ItemId] BIGINT NOT NULL,
[JobId] INT NULL,
[JobInstanceId] INT NULL,
[XmlData] AS (CASE WHEN [_XmlUncompressed] IS NULL THEN CONVERT (VARCHAR (MAX), Decompress([_XmlCompressed])) ELSE [_XmlUncompressed] END),
[_XmlCompressed] VARBINARY (MAX) NULL,
[_XmlUncompressed] VARCHAR (MAX) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_TargetXml] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);
GO
PRINT N'Creating Index [mig_item].[TargetXml].[IX_TargetXml_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_TargetXml_Valid]
ON [mig_item].[TargetXml]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC);
GO
PRINT N'Creating Table [mig_item].[ImportItem]...';
GO
CREATE TABLE [mig_item].[ImportItem] (
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Result] TINYINT NOT NULL,
[TargetKey] VARCHAR (512) NULL,
[Commit] BIGINT NOT NULL,
[CleanWorkspace] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_ImportItem] PRIMARY KEY CLUSTERED ([ItemId] ASC, [JobId] ASC)
);
GO
PRINT N'Creating Index [mig_item].[ImportItem].[IX_ImportItem_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_ImportItem_Valid]
ON [mig_item].[ImportItem]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC)
INCLUDE([Result]);
GO
PRINT N'Creating Index [mig_item].[ImportItem].[IX_ImportItem_Unload]...';
GO
CREATE NONCLUSTERED INDEX [IX_ImportItem_Unload]
ON [mig_item].[ImportItem]([Result] ASC)
INCLUDE([ItemId], [JobId], [JobInstanceId]);
GO
PRINT N'Creating Table [mig_item].[AuditValue]...';
GO
CREATE TABLE [mig_item].[AuditValue] (
[AuditValueID] UNIQUEIDENTIFIER NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[ParentAuditValueID] UNIQUEIDENTIFIER NULL,
[AmountDebit] DECIMAL (15, 2) NULL,
[AmountCredit] DECIMAL (15, 2) NULL,
[Count] INT NOT NULL,
[Success] BIT NOT NULL,
[InheritedSuccess] BIT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_AuditValue] PRIMARY KEY CLUSTERED ([AuditValueID] ASC)
);
GO
PRINT N'Creating Index [mig_item].[AuditValue].[IX_AuditValue_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_AuditValue_Valid]
ON [mig_item].[AuditValue]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Table [mig_item].[AuditSpecifier]...';
GO
CREATE TABLE [mig_item].[AuditSpecifier] (
[AuditSpecifierID] UNIQUEIDENTIFIER NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[AuditValueID] UNIQUEIDENTIFIER NOT NULL,
[AuditHeaderClassID] UNIQUEIDENTIFIER NOT NULL,
[AuditGroupClassID] UNIQUEIDENTIFIER NOT NULL,
[AuditSpecifierClassID] UNIQUEIDENTIFIER NOT NULL,
[AuditSpecifierValue] VARCHAR (256) NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_AuditSpecifier] PRIMARY KEY CLUSTERED ([AuditSpecifierID] ASC)
);
GO
PRINT N'Creating Index [mig_item].[AuditSpecifier].[IX_AuditSpecifier_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_AuditSpecifier_Valid]
ON [mig_item].[AuditSpecifier]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC);
GO
PRINT N'Creating Index [mig_item].[AuditSpecifier].[IX_AuditSpecifier_AuditValue]...';
GO
CREATE NONCLUSTERED INDEX [IX_AuditSpecifier_AuditValue]
ON [mig_item].[AuditSpecifier]([AuditValueID] ASC);
GO
PRINT N'Creating Table [mig_item].[FileKey]...';
GO
CREATE TABLE [mig_item].[FileKey] (
[Class] VARCHAR (50) NOT NULL,
[KeyValue] VARCHAR (512) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_FileKey] PRIMARY KEY CLUSTERED ([Class] ASC, [KeyValue] ASC)
);
GO
PRINT N'Creating Table [mig_item].[EventUser]...';
GO
CREATE TABLE [mig_item].[EventUser] (
[_RowId] BIGINT IDENTITY (1, 1) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[Engine] TINYINT NOT NULL,
[Disposition] TINYINT NOT NULL,
[Impact] TINYINT NOT NULL,
[Receiver] TINYINT NOT NULL,
[EventName] VARCHAR (50) NOT NULL,
[EventID] UNIQUEIDENTIFIER NOT NULL,
[EventTextID] UNIQUEIDENTIFIER NOT NULL,
[ParameterXml] VARCHAR (MAX) NOT NULL,
[ParameterHash] BINARY (20) NOT NULL,
[GroupHash] BINARY (20) NOT NULL,
[XPath] VARCHAR (512) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_EventUser] PRIMARY KEY CLUSTERED ([_RowId] ASC) WITH (FILLFACTOR = 70, PAD_INDEX = ON)
);
GO
PRINT N'Creating Index [mig_item].[EventUser].[IX_EventUser_EventList]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventUser_EventList]
ON [mig_item].[EventUser]([Step] ASC, [Disposition] ASC, [Impact] ASC, [ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Engine] ASC, [EventID] ASC, [EventName] ASC, [Receiver] ASC, [EventTextID] ASC);
GO
PRINT N'Creating Index [mig_item].[EventUser].[IX_EventUser_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventUser_Valid]
ON [mig_item].[EventUser]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC)
INCLUDE([_RowId], [Disposition], [Impact], [Receiver], [EventName], [EventID], [EventTextID], [ParameterXml], [XPath], [CreationDate]);
GO
PRINT N'Creating Index [mig_item].[EventUser].[IX_EventUser_Job]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventUser_Job]
ON [mig_item].[EventUser]([JobId] ASC)
INCLUDE([ItemId], [Step], [Disposition], [Impact], [EventName], [EventID], [EventTextID]);
GO
PRINT N'Creating Index [mig_item].[EventUser].[IX_EventUser_UpdateTracking]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventUser_UpdateTracking]
ON [mig_item].[EventUser]([ItemId] ASC)
INCLUDE([JobId], [JobInstanceId], [Step], [Engine], [Disposition], [Impact], [Receiver], [EventID], [ParameterXml], [ParameterHash]);
GO
PRINT N'Creating Table [mig_item].[EventSystem]...';
GO
CREATE TABLE [mig_item].[EventSystem] (
[_RowId] BIGINT IDENTITY (1, 1) NOT NULL,
[ItemId] BIGINT NOT NULL,
[JobId] INT NOT NULL,
[JobInstanceId] INT NOT NULL,
[Step] TINYINT NOT NULL,
[Engine] TINYINT NOT NULL,
[Disposition] TINYINT NOT NULL,
[Impact] TINYINT NOT NULL,
[Receiver] TINYINT NOT NULL,
[Message] VARCHAR (256) NOT NULL,
[ParameterXml] VARCHAR (MAX) NOT NULL,
[ParameterHash] BINARY (20) NOT NULL,
[GroupHash] BINARY (20) NOT NULL,
[XPath] VARCHAR (512) NULL,
[CreationDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_EventSystem] PRIMARY KEY CLUSTERED ([_RowId] ASC) WITH (FILLFACTOR = 70, PAD_INDEX = ON)
);
GO
PRINT N'Creating Index [mig_item].[EventSystem].[IX_EventSystem]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventSystem]
ON [mig_item].[EventSystem]([Step] ASC, [ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Disposition] ASC, [Impact] ASC, [Receiver] ASC, [Message] ASC);
GO
PRINT N'Creating Index [mig_item].[EventSystem].[IX_EventSystem_Valid]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventSystem_Valid]
ON [mig_item].[EventSystem]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Step] ASC)
INCLUDE([_RowId], [Disposition], [Impact], [Receiver], [Message], [ParameterXml], [XPath], [CreationDate]);
GO
PRINT N'Creating Index [mig_item].[EventSystem].[IX_EventSystem_Job]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventSystem_Job]
ON [mig_item].[EventSystem]([JobId] ASC)
INCLUDE([ItemId], [Step], [Disposition], [Impact], [Message]);
GO
PRINT N'Creating Index [mig_item].[EventSystem].[IX_EventSystem_EventList]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventSystem_EventList]
ON [mig_item].[EventSystem]([Step] ASC, [Disposition] ASC, [Impact] ASC, [ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC, [Engine] ASC, [Receiver] ASC, [Message] ASC);
GO
PRINT N'Creating Table [mig_profile].[Trace]...';
GO
CREATE TABLE [mig_profile].[Trace] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[TraceID] UNIQUEIDENTIFIER NOT NULL,
[ParentTraceID] UNIQUEIDENTIFIER NULL,
[JobInstanceId] INT NOT NULL,
[ItemId] BIGINT NOT NULL,
[FrameId] CHAR (32) NOT NULL,
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[TicksInclusive] BIGINT NOT NULL,
[TicksExclusive] BIGINT NOT NULL,
[Count] INT NOT NULL,
[ItemPath] VARCHAR (1024) NULL,
[CreationTime] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Profile_Trace] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_profile].[Trace].[IX_Profile_TraceID]...';
GO
CREATE NONCLUSTERED INDEX [IX_Profile_TraceID]
ON [mig_profile].[Trace]([TraceID] ASC);
GO
PRINT N'Creating Index [mig_profile].[Trace].[IX_Profile_Frame]...';
GO
CREATE NONCLUSTERED INDEX [IX_Profile_Frame]
ON [mig_profile].[Trace]([JobInstanceId] ASC, [ItemId] ASC, [FrameId] ASC);
GO
PRINT N'Creating Table [mig_profile].[Job]...';
GO
CREATE TABLE [mig_profile].[Job] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[JobInstanceId] INT NOT NULL,
[FrameId] CHAR (32) NOT NULL,
[TicksInclusive] BIGINT NOT NULL,
[TicksExclusive] BIGINT NOT NULL,
[Count] INT NOT NULL,
[CreationTime] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Profile_Job] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_profile].[Job].[IX_Profile_Job]...';
GO
CREATE NONCLUSTERED INDEX [IX_Profile_Job]
ON [mig_profile].[Job]([JobInstanceId] ASC);
GO
PRINT N'Creating Table [mig_profile].[Item]...';
GO
CREATE TABLE [mig_profile].[Item] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[JobInstanceId] INT NOT NULL,
[ItemId] BIGINT NOT NULL,
[FrameId] CHAR (32) NOT NULL,
[TicksInclusive] BIGINT NOT NULL,
[TicksExclusive] BIGINT NOT NULL,
[Count] INT NOT NULL,
[CreationTime] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Profile_Item] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_profile].[Item].[IX_Profile_Item]...';
GO
CREATE NONCLUSTERED INDEX [IX_Profile_Item]
ON [mig_profile].[Item]([JobInstanceId] ASC, [ItemId] ASC, [FrameId] ASC);
GO
PRINT N'Creating Table [mig_profile].[Frame]...';
GO
CREATE TABLE [mig_profile].[Frame] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[JobInstanceId] INT NOT NULL,
[FrameId] CHAR (32) NOT NULL,
[ParentFrameId] CHAR (32) NULL,
[Location] VARCHAR (MAX) NOT NULL,
[CodeLocationID] UNIQUEIDENTIFIER NOT NULL,
[ProfilingType] VARCHAR (50) NOT NULL,
[Scope] VARCHAR (256) NOT NULL,
[FullScope] VARCHAR (MAX) NOT NULL,
[CreationTime] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_Profile_Frame] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_profile].[Frame].[IX_Profile_Frame]...';
GO
CREATE NONCLUSTERED INDEX [IX_Profile_Frame]
ON [mig_profile].[Frame]([JobInstanceId] ASC, [FrameId] ASC);
GO
PRINT N'Creating Table [mig_tracking].[Log]...';
GO
CREATE TABLE [mig_tracking].[Log] (
[LogId] BIGINT IDENTITY (1, 1) NOT NULL,
[MessageID] UNIQUEIDENTIFIER NOT NULL,
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[PartitionValue] NVARCHAR (50) NULL,
[LogType] INT NOT NULL,
[LogTime] DATETIME2 (7) NOT NULL,
[LogUser] NVARCHAR (128) NOT NULL,
[Property] NVARCHAR (128) NOT NULL,
[Before] NVARCHAR (MAX) NULL,
[After] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED ([LogId] ASC)
);
GO
PRINT N'Creating Index [mig_tracking].[Log].[IX_Log_2]...';
GO
CREATE NONCLUSTERED INDEX [IX_Log_2]
ON [mig_tracking].[Log]([MessageID] ASC, [EntityID] ASC, [PartitionValue] ASC, [LogId] DESC);
GO
PRINT N'Creating Index [mig_tracking].[Log].[IX_Log_1]...';
GO
CREATE NONCLUSTERED INDEX [IX_Log_1]
ON [mig_tracking].[Log]([MessageID] ASC, [EntityID] ASC, [LogId] DESC);
GO
PRINT N'Creating Table [mig_tracking].[Parameter]...';
GO
CREATE TABLE [mig_tracking].[Parameter] (
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[ProjectType] VARCHAR (50) NULL,
[Name] VARCHAR (50) NULL,
[Value] VARCHAR (MAX) NULL,
[Description] VARCHAR (MAX) NULL
);
GO
PRINT N'Creating Table [mig_tracking].[PartitionEventCount]...';
GO
CREATE TABLE [mig_tracking].[PartitionEventCount] (
[MessageID] UNIQUEIDENTIFIER NOT NULL,
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[PartitionValue] VARCHAR (50) NOT NULL,
[Time] DATETIME2 (7) NOT NULL,
[InstanceCount] BIGINT NOT NULL,
[ItemCount] BIGINT NOT NULL,
CONSTRAINT [PK_PartitionEventCount] PRIMARY KEY CLUSTERED ([MessageID] ASC, [EntityID] ASC, [PartitionValue] ASC, [Time] ASC)
);
GO
PRINT N'Creating Table [mig_tracking].[RootItem]...';
GO
CREATE TABLE [mig_tracking].[RootItem] (
[ItemId] BIGINT NOT NULL,
[PartitionValue] VARCHAR (50) NOT NULL,
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[SourceKey] VARCHAR (512) NOT NULL,
[MigrationKey] VARCHAR (512) NOT NULL,
[TargetKey] VARCHAR (512) NULL,
[IsExported] BIT NOT NULL,
[IsImported] BIT NULL,
[ExportID] UNIQUEIDENTIFIER NOT NULL,
[HasSourceXml] BIT NULL,
[HasExportXml] BIT NULL,
[HasTargetXml] BIT NULL,
[SourceCommit] BIGINT NOT NULL,
[SourceCleanWorkspace] BIT NOT NULL,
[TargetCommit] BIGINT NOT NULL,
[TargetCleanWorkspace] BIT NOT NULL,
[ExportJobId] INT NOT NULL,
[ExportTime] DATETIME2 (7) NOT NULL,
[ImportJobId] INT NULL,
[ImportTime] DATETIME2 (7) NULL,
[RunDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_RootItem] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);
GO
PRINT N'Creating Index [mig_tracking].[RootItem].[IX_RootItem_TargetKey]...';
GO
CREATE NONCLUSTERED INDEX [IX_RootItem_TargetKey]
ON [mig_tracking].[RootItem]([EntityID] ASC, [TargetKey] ASC, [PartitionValue] ASC);
GO
PRINT N'Creating Index [mig_tracking].[RootItem].[IX_RootItem_MigrationKey]...';
GO
CREATE NONCLUSTERED INDEX [IX_RootItem_MigrationKey]
ON [mig_tracking].[RootItem]([EntityID] ASC, [MigrationKey] ASC, [PartitionValue] ASC);
GO
PRINT N'Creating Index [mig_tracking].[RootItem].[IX_RootItem_SourceKey]...';
GO
CREATE NONCLUSTERED INDEX [IX_RootItem_SourceKey]
ON [mig_tracking].[RootItem]([EntityID] ASC, [SourceKey] ASC, [PartitionValue] ASC);
GO
PRINT N'Creating Table [mig_tracking].[RootItemLink]...';
GO
CREATE TABLE [mig_tracking].[RootItemLink] (
[SourceItemId] BIGINT NOT NULL,
[TargetItemId] BIGINT NOT NULL,
[TargetEntityID] UNIQUEIDENTIFIER NOT NULL,
[RunDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_RootItemLink] PRIMARY KEY CLUSTERED ([SourceItemId] ASC, [TargetItemId] ASC)
);
GO
PRINT N'Creating Index [mig_tracking].[RootItemLink].[IX_RootItemLink_TargetEntityID]...';
GO
CREATE NONCLUSTERED INDEX [IX_RootItemLink_TargetEntityID]
ON [mig_tracking].[RootItemLink]([TargetEntityID] ASC);
GO
PRINT N'Creating Index [mig_tracking].[RootItemLink].[IX_RootItemLink_TargetItemId]...';
GO
CREATE NONCLUSTERED INDEX [IX_RootItemLink_TargetItemId]
ON [mig_tracking].[RootItemLink]([TargetItemId] ASC);
GO
PRINT N'Creating Table [mig_tracking].[Statistic]...';
GO
CREATE TABLE [mig_tracking].[Statistic] (
[PartitionValue] VARCHAR (50) NOT NULL,
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[Time] DATETIME2 (7) NOT NULL,
[Available] BIGINT NOT NULL,
[ExpDiscarded] BIGINT NOT NULL,
[Exported] BIGINT NOT NULL,
[ImpDiscarded] BIGINT NOT NULL,
[Imported] BIGINT NOT NULL,
CONSTRAINT [PK_Statistic] PRIMARY KEY CLUSTERED ([PartitionValue] ASC, [EntityID] ASC, [Time] ASC)
);
GO
PRINT N'Creating Table [mig_tracking].[Baseline]...';
GO
CREATE TABLE [mig_tracking].[Baseline] (
[BaselineId] INT IDENTITY (1, 1) NOT NULL,
[Title] NVARCHAR (128) NOT NULL,
[Time] DATETIME2 (7) NOT NULL,
[IsCurrent] BIT NOT NULL,
[Comment] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_Baseline] PRIMARY KEY CLUSTERED ([BaselineId] ASC)
);
GO
PRINT N'Creating Table [mig_tracking].[EventParameter]...';
GO
CREATE TABLE [mig_tracking].[EventParameter] (
[HashValue] BINARY (20) NOT NULL,
[GroupHash] BINARY (20) NOT NULL,
[Id] INT IDENTITY (1, 1) NOT NULL,
[ParameterXml] VARCHAR (MAX) NULL,
[RunDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_EventParameter] PRIMARY KEY CLUSTERED ([HashValue] ASC)
);
GO
PRINT N'Creating Index [mig_tracking].[EventParameter].[IX_EventParameter_Id]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventParameter_Id]
ON [mig_tracking].[EventParameter]([Id] ASC);
GO
PRINT N'Creating Index [mig_tracking].[EventParameter].[IX_EventParameter_GroupHash]...';
GO
CREATE NONCLUSTERED INDEX [IX_EventParameter_GroupHash]
ON [mig_tracking].[EventParameter]([GroupHash] ASC);
GO
PRINT N'Creating Table [mig_tracking].[RootEntityEvent]...';
GO
CREATE TABLE [mig_tracking].[RootEntityEvent] (
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[PartitionValue] VARCHAR (50) NOT NULL,
[MessageID] UNIQUEIDENTIFIER NOT NULL,
[ParameterHash] BINARY (20) NOT NULL,
[GroupHash] BINARY (20) NOT NULL,
[EventCount] BIGINT NOT NULL,
[ItemCount] BIGINT NOT NULL,
[RunDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_RootEntityEvent] PRIMARY KEY CLUSTERED ([EntityID] ASC, [PartitionValue] ASC, [MessageID] ASC, [ParameterHash] ASC)
);
GO
PRINT N'Creating Table [mig_tracking].[RootItemEvent]...';
GO
CREATE TABLE [mig_tracking].[RootItemEvent] (
[RowID] UNIQUEIDENTIFIER NOT NULL,
[MessageID] UNIQUEIDENTIFIER NOT NULL,
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[PartitionValue] VARCHAR (50) NOT NULL,
[ItemId] BIGINT NOT NULL,
[ParameterHash] BINARY (20) NOT NULL,
[GroupHash] BINARY (20) NOT NULL,
[EventCount] BIGINT NOT NULL,
[RunDate] DATETIME2 (7) NOT NULL,
CONSTRAINT [PK_RootItemEvent] PRIMARY KEY CLUSTERED ([RowID] ASC)
);
GO
PRINT N'Creating Index [mig_tracking].[RootItemEvent].[IX_RootItemEvent_ItemId]...';
GO
CREATE NONCLUSTERED INDEX [IX_RootItemEvent_ItemId]
ON [mig_tracking].[RootItemEvent]([ItemId] ASC);
GO
PRINT N'Creating Index [mig_tracking].[RootItemEvent].[IX_RootItemEvent]...';
GO
CREATE NONCLUSTERED INDEX [IX_RootItemEvent]
ON [mig_tracking].[RootItemEvent]([MessageID] ASC, [EntityID] ASC, [PartitionValue] ASC, [ItemId] ASC)
INCLUDE([ParameterHash]);
GO
PRINT N'Creating Index [mig_tracking].[RootItemEvent].[IX_RootItemEvent_EntityID]...';
GO
CREATE NONCLUSTERED INDEX [IX_RootItemEvent_EntityID]
ON [mig_tracking].[RootItemEvent]([EntityID] ASC);
GO
PRINT N'Creating Table [mig_translation].[TranslationValuesetRow]...';
GO
CREATE TABLE [mig_translation].[TranslationValuesetRow] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ValuesetID] UNIQUEIDENTIFIER NOT NULL,
[PartitionValue] NVARCHAR (50) NOT NULL,
[RowXml] XML NOT NULL,
[ValidationCode] INT NOT NULL,
[ValidationText] NVARCHAR (MAX) NULL,
[ModifiedAt] DATETIME NOT NULL,
[ModifiedBy] NVARCHAR (50) NOT NULL,
CONSTRAINT [PK_TranslationValuesetRow] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Creating Index [mig_translation].[TranslationValuesetRow].[IX_TranslationValuesetRow_ValusetID_Partitionvalue]...';
GO
CREATE NONCLUSTERED INDEX [IX_TranslationValuesetRow_ValusetID_Partitionvalue]
ON [mig_translation].[TranslationValuesetRow]([ValuesetID] ASC, [PartitionValue] ASC);
GO
PRINT N'Creating Table [mig_translation].[TranslationValueset]...';
GO
CREATE TABLE [mig_translation].[TranslationValueset] (
[ValuesetID] UNIQUEIDENTIFIER NOT NULL,
[Readonly] BIT NOT NULL,
[ValidatedAt] DATETIME2 (7) NULL,
CONSTRAINT [PK_TranslationValueset] PRIMARY KEY CLUSTERED ([ValuesetID] ASC)
);
GO
PRINT N'Creating Synonym [mig_tracking].[AutomaticState]...';
GO
CREATE SYNONYM [mig_tracking].[AutomaticState] FOR [$(Utility)].[mig_tracking].[AutomaticState];
GO
PRINT N'Creating Synonym [mig_tracking].[MessageState]...';
GO
CREATE SYNONYM [mig_tracking].[MessageState] FOR [$(Utility)].[mig_tracking].[vwMessageState];
GO
PRINT N'Creating Synonym [mig_tracking].[ufGetAutomaticState]...';
GO
CREATE SYNONYM [mig_tracking].[ufGetAutomaticState] FOR [$(Utility)].[mig_tracking].[ufGetAutomaticState];
GO
PRINT N'Creating Synonym [mig_tracking].[UserState]...';
GO
CREATE SYNONYM [mig_tracking].[UserState] FOR [$(Utility)].[mig_tracking].[UserState];
GO
PRINT N'Creating Synonym [mig_tracking].[UserStateUpdate]...';
GO
CREATE SYNONYM [mig_tracking].[UserStateUpdate] FOR [$(Utility)].[mig_tracking].[UserStateUpdate];
GO
PRINT N'Creating Synonym [mig_utility].[ParseDelimitedString]...';
GO
CREATE SYNONYM [mig_utility].[ParseDelimitedString] FOR [$(Utility)].[mig_utility].[ParseDelimitedString];
GO
PRINT N'Creating Synonym [mig_utility].[ParseGuids]...';
GO
CREATE SYNONYM [mig_utility].[ParseGuids] FOR [$(Utility)].[mig_utility].[ParseGuids];
GO
PRINT N'Creating Synonym [mig_utility].[ParseInfoList]...';
GO
CREATE SYNONYM [mig_utility].[ParseInfoList] FOR [$(Utility)].[mig_utility].[ParseInfoList];
GO
PRINT N'Creating Synonym [mig_utility].[ParseIntegers]...';
GO
CREATE SYNONYM [mig_utility].[ParseIntegers] FOR [$(Utility)].[mig_utility].[ParseIntegers];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[ExportEntitySourceDependency]...';
GO
ALTER TABLE [mig_director].[ExportEntitySourceDependency]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[ExportEntity]...';
GO
ALTER TABLE [mig_director].[ExportEntity]
ADD DEFAULT ((0)) FOR [Ignored];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[ExportEntity]...';
GO
ALTER TABLE [mig_director].[ExportEntity]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[DirectorOption]...';
GO
ALTER TABLE [mig_director].[DirectorOption]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[ValuesetProvider_CreationDate]...';
GO
ALTER TABLE [mig_director].[ValuesetProvider]
ADD CONSTRAINT [ValuesetProvider_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[ValuesetBaseline_BaselineCount]...';
GO
PRINT N'Creating Default Constraint [mig_director].[Valueset_CreationDate]...';
GO
ALTER TABLE [mig_director].[Valueset]
ADD CONSTRAINT [Valueset_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[Concurrency]...';
GO
ALTER TABLE [mig_director].[Concurrency]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_Command_CreationDate]...';
GO
ALTER TABLE [mig_director].[Command]
ADD CONSTRAINT [DF_Command_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[Runparameter_CreationDate]...';
GO
ALTER TABLE [mig_director].[RunParameter]
ADD CONSTRAINT [Runparameter_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[JobInstanceLog]...';
GO
ALTER TABLE [mig_director].[JobInstanceLog]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[JobInstance]...';
GO
ALTER TABLE [mig_director].[JobInstance]
ADD DEFAULT (newid()) FOR [ClientID];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[JobInstance]...';
GO
ALTER TABLE [mig_director].[JobInstance]
ADD DEFAULT ((0)) FOR [DebugWait];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[JobInstance]...';
GO
ALTER TABLE [mig_director].[JobInstance]
ADD DEFAULT ((0)) FOR [ExecutionState];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[JobInstance]...';
GO
ALTER TABLE [mig_director].[JobInstance]
ADD DEFAULT ((0)) FOR [Result];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[JobInstance]...';
GO
ALTER TABLE [mig_director].[JobInstance]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[Job]...';
GO
ALTER TABLE [mig_director].[Job]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[ItemSet]...';
GO
ALTER TABLE [mig_director].[ItemSet]
ADD DEFAULT (sysdatetime()) FOR [OrderDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[ExportSourceViewDependency]...';
GO
ALTER TABLE [mig_director].[ExportSourceViewDependency]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_ExportSourceView_ForceInScope]...';
GO
ALTER TABLE [mig_director].[ExportSourceView]
ADD CONSTRAINT [DF_ExportSourceView_ForceInScope] DEFAULT ((0)) FOR [ForceInScope];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_ExportSourceView_CreationDate]...';
GO
ALTER TABLE [mig_director].[ExportSourceView]
ADD CONSTRAINT [DF_ExportSourceView_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_ExportSourceTable_ForceInScope]...';
GO
ALTER TABLE [mig_director].[ExportSourceTable]
ADD CONSTRAINT [DF_ExportSourceTable_ForceInScope] DEFAULT ((0)) FOR [ForceInScope];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_ExportSourceTable_CreationDate]...';
GO
ALTER TABLE [mig_director].[ExportSourceTable]
ADD CONSTRAINT [DF_ExportSourceTable_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_ExportSourceFile_CreationDate]...';
GO
ALTER TABLE [mig_director].[ExportSourceFile]
ADD CONSTRAINT [DF_ExportSourceFile_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_ExportFormattedFile_LoadedCount]...';
GO
ALTER TABLE [mig_director].[ExportFormattedFile]
ADD CONSTRAINT [DF_ExportFormattedFile_LoadedCount] DEFAULT ((0)) FOR [LoadedCount];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_ExportFormattedFile_CreationDate]...';
GO
ALTER TABLE [mig_director].[ExportFormattedFile]
ADD CONSTRAINT [DF_ExportFormattedFile_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_director].[DF_JobData_CreationDate]...';
GO
ALTER TABLE [mig_director].[JobData]
ADD CONSTRAINT [DF_JobData_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_director].[BusinessEntity]...';
GO
ALTER TABLE [mig_director].[BusinessEntity]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_garbage].[Log]...';
GO
ALTER TABLE [mig_garbage].[Log]
ADD DEFAULT 0 FOR [Deleted];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_garbage].[Log]...';
GO
ALTER TABLE [mig_garbage].[Log]
ADD DEFAULT 0 FOR [Reported];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_Active_CreationDate]...';
GO
ALTER TABLE [mig_item].[Active]
ADD CONSTRAINT [DF_Active_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_Identity_CreationDate]...';
GO
ALTER TABLE [mig_item].[Identity]
ADD CONSTRAINT [DF_Identity_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_SourceXml_CreationDate]...';
GO
ALTER TABLE [mig_item].[SourceXml]
ADD CONSTRAINT [DF_SourceXml_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_SavedString_CreationDate]...';
GO
ALTER TABLE [mig_item].[SavedString]
ADD CONSTRAINT [DF_SavedString_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[RelationshipValue]...';
GO
ALTER TABLE [mig_item].[RelationshipValue]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[RelationshipKey]...';
GO
ALTER TABLE [mig_item].[RelationshipKey]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[Processed]...';
GO
ALTER TABLE [mig_item].[Processed]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[MainKey]...';
GO
ALTER TABLE [mig_item].[MainKey]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[ItemSet]...';
GO
ALTER TABLE [mig_item].[ItemSet]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[Item]...';
GO
ALTER TABLE [mig_item].[Item]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[Id]...';
GO
ALTER TABLE [mig_item].[Id]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_ExportXml_CreationDate]...';
GO
ALTER TABLE [mig_item].[ExportXml]
ADD CONSTRAINT [DF_ExportXml_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_ExportKey_CreationDate]...';
GO
ALTER TABLE [mig_item].[ExportKey]
ADD CONSTRAINT [DF_ExportKey_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_ExportItem_CreationDate]...';
GO
ALTER TABLE [mig_item].[ExportItem]
ADD CONSTRAINT [DF_ExportItem_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_Dependency_CreationDate]...';
GO
ALTER TABLE [mig_item].[Dependency]
ADD CONSTRAINT [DF_Dependency_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_TargetXml_CreationDate]...';
GO
ALTER TABLE [mig_item].[TargetXml]
ADD CONSTRAINT [DF_TargetXml_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_ImportItem_CreationDate]...';
GO
ALTER TABLE [mig_item].[ImportItem]
ADD CONSTRAINT [DF_ImportItem_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[AuditValue]...';
GO
ALTER TABLE [mig_item].[AuditValue]
ADD DEFAULT newsequentialid() FOR [AuditValueID];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[AuditValue]...';
GO
ALTER TABLE [mig_item].[AuditValue]
ADD DEFAULT (1) FOR [Count];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[AuditValue]...';
GO
ALTER TABLE [mig_item].[AuditValue]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[AuditSpecifier]...';
GO
ALTER TABLE [mig_item].[AuditSpecifier]
ADD DEFAULT newsequentialid() FOR [AuditSpecifierID];
GO
PRINT N'Creating Default Constraint unnamed constraint on [mig_item].[AuditSpecifier]...';
GO
ALTER TABLE [mig_item].[AuditSpecifier]
ADD DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_FileKey_CreationDate]...';
GO
ALTER TABLE [mig_item].[FileKey]
ADD CONSTRAINT [DF_FileKey_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_EventUser_CreationDate]...';
GO
ALTER TABLE [mig_item].[EventUser]
ADD CONSTRAINT [DF_EventUser_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_item].[DF_EventSystem_CreationDate]...';
GO
ALTER TABLE [mig_item].[EventSystem]
ADD CONSTRAINT [DF_EventSystem_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Default Constraint [mig_profile].[DF_Profile_Trace_CreationTime]...';
GO
ALTER TABLE [mig_profile].[Trace]
ADD CONSTRAINT [DF_Profile_Trace_CreationTime] DEFAULT (sysdatetime()) FOR [CreationTime];
GO
PRINT N'Creating Default Constraint [mig_profile].[DF_Profile_Job_CreationTime]...';
GO
ALTER TABLE [mig_profile].[Job]
ADD CONSTRAINT [DF_Profile_Job_CreationTime] DEFAULT (sysdatetime()) FOR [CreationTime];
GO
PRINT N'Creating Default Constraint [mig_profile].[DF_Profile_Item_CreationTime]...';
GO
ALTER TABLE [mig_profile].[Item]
ADD CONSTRAINT [DF_Profile_Item_CreationTime] DEFAULT (sysdatetime()) FOR [CreationTime];
GO
PRINT N'Creating Default Constraint [mig_profile].[DF_Profile_Frame_CreationTime]...';
GO
ALTER TABLE [mig_profile].[Frame]
ADD CONSTRAINT [DF_Profile_Frame_CreationTime] DEFAULT (sysdatetime()) FOR [CreationTime];
GO
PRINT N'Creating Default Constraint [mig_tracking].[DF_BaselineStatistic_CreationDate]...';
GO
PRINT N'Creating Default Constraint [mig_tracking].[DF_Baseline_IsCurrent]...';
GO
ALTER TABLE [mig_tracking].[Baseline]
ADD CONSTRAINT [DF_Baseline_IsCurrent] DEFAULT ((0)) FOR [IsCurrent];
GO
PRINT N'Creating Default Constraint [mig_translation].[DF_TranslationValuesetRow_ValidationCode]...';
GO
ALTER TABLE [mig_translation].[TranslationValuesetRow]
ADD CONSTRAINT [DF_TranslationValuesetRow_ValidationCode] DEFAULT 0 FOR [ValidationCode];
GO
PRINT N'Creating Default Constraint [mig_translation].[DF_TranslationValueset_Readonly]...';
GO
ALTER TABLE [mig_translation].[TranslationValueset]
ADD CONSTRAINT [DF_TranslationValueset_Readonly] DEFAULT 0 FOR [Readonly];
GO
PRINT N'Creating View [mig_tracking].[EventSystem]...';
GO
CREATE view [mig_tracking].[EventSystem]
as
select
A.*
, C.[PartitionValue]
from
[mig_item].[EventSystem] as A
inner join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and A.[JobId] = B.[JobId]
and A.[Step] = B.[Step]
and A.[JobInstanceId] = B.[JobInstanceId]
inner join [mig_item].[Item] as C
on A.[ItemId] = C.[ItemId] -- Do not include JobId and JobInstanceId in join. They will always reflect only the export job
inner join [mig_item].[Id] as D
on A.[ItemId] = D.[ItemId]
where
D.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_tracking].[EventUser]...';
GO
CREATE view [mig_tracking].[EventUser]
as
select
A.*
, C.[PartitionValue]
from
[mig_item].[EventUser] as A
inner join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and A.[JobId] = B.[JobId]
and A.[Step] = B.[Step]
and A.[JobInstanceId] = B.[JobInstanceId]
inner join [mig_item].[Item] as C
on A.[ItemId] = C.[ItemId] -- Do not include JobId and JobInstanceId in join. They will always reflect only export job
inner join [mig_item].[Id] as D
on A.[ItemId] = D.[ItemId]
where
D.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_tracking].[VwRootItem]...';
GO
create view [mig_tracking].[VwRootItem]
as
select
A.[ItemId]
, B.[PartitionValue]
, D.[EntityID]
, D.[BreakFields] as [SourceKey]
, C.[KeyValue] as [MigrationKey]
, F.[TargetKey]
, case E.Result
when 0 then 1
else 0 end as [IsExported]
, case
when F.Result is null then null
when F.Result = 2 then 1
else 0 end as [IsImported]
, D.[ExportID]
, cast(case when G.[ItemId] is null then 0 else 1 end as bit) as [HasSourceXml]
, cast(case when H.[ItemId] is null then 0 else 1 end as bit) as [HasExportXml]
, cast(case when I.[ItemId] is null then 0 else 1 end as bit) as [HasTargetXml]
, E.[Commit] as [SourceCommit]
, E.[CleanWorkSpace] as [SourceCleanWorkspace]
, isnull(F.[Commit], 0) as [TargetCommit]
, isnull(F.[CleanWorkSpace], 0) as [TargetCleanWorkspace]
, E.[JobId] as [ExportJobId]
, EE.[CreationDate] as [ExportTime]
, F.[JobId] as [ImportJobId]
, FF.[CreationDate] as [ImportTime]
from
[mig_item].[Id] as A with (NoLock)
inner join [mig_item].[Item] as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
inner join [mig_item].[MainKey] as C with (NoLock)
on
B.[KeyId] = C.[KeyId]
inner join [mig_item].[ExportKey] as D with (NoLock)
on
A.[ItemId] = D.[ItemId]
inner join [mig_item].[ExportItem] as E with (NoLock)
on
A.[ItemId] = E.[ItemId]
inner join [mig_director].[JobInstance] as EE with (NoLock)
on
E.[JobInstanceId] = EE.[Id]
left outer join [mig_item].[ImportItem] as F with (NoLock)
on
A.[ItemId] = F.[ItemId]
and F.[JobId] >= E.[JobId]
left outer join [mig_director].[JobInstance] as FF with (NoLock)
on
F.[JobInstanceId] = FF.[Id]
left outer join [mig_item].[SourceXml] as G with (NoLock)
on
A.[ItemId] = G.[ItemId]
and G.[XmlData] is not null
left outer join [mig_item].[ExportXml] as H with (NoLock)
on
A.[ItemId] = H.[ItemId]
and H.[XmlData] is not null
left outer join [mig_item].[TargetXml] as I with (NoLock)
on
A.[ItemId] = I.[ItemId]
and I.[XmlData] is not null
where
A.[InvalidatedTime] is null
and E.[Result] <> 1 -- 1: Dropped
GO
PRINT N'Creating View [mig_utility].[ItemKey]...';
GO
create view [mig_utility].[ItemKey]
as
select
A.[ItemId]
, A.[EntityID]
, D.[EntityName]
, E.[BusinessEntityName]
, C.[KeyValue]
, convert(xml, C.[KeyValue]) as [KeyValueXml]
from
[mig_item].[ExportKey] as A
inner join [mig_item].[Item] as B on A.[ItemId] = B.[ItemId]
inner join [mig_item].[MainKey] as C on B.[KeyId] = C.[KeyId]
inner join [mig_director].[ExportEntity] as D on A.[EntityID] = D.[EntityID]
inner join [mig_director].[BusinessEntity] as E on D.[BusinessEntityID] = E.[BusinessEntityID]
GO
PRINT N'Creating View [mig_valid].[RelationshipValue]...';
GO
CREATE view [mig_valid].[RelationshipValue]
as
-- All Relationships created by the initializer
-- have ItemId = 0.
select
A.*
from
[mig_item].[RelationshipValue] as A
left outer join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and A.[JobId] = B.[JobId]
and A.[Step] = B.[Step]
and A.[JobInstanceId] = B.[JobInstanceId]
where
A.[ItemId] = 0 -- Initializer
or B.[ItemId] is not null -- Other
GO
PRINT N'Creating View [mig_valid].[RelationshipPropertyValue]...';
GO
CREATE view [mig_valid].[RelationshipBagPropertyValue]
as
-- All Relationships created by the initializer
-- have ItemId = 0.
select
A.*
from
[mig_item].[RelationshipBagPropertyValue] as A
left outer join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and A.[JobId] = B.[JobId]
and A.[Step] = B.[Step]
and A.[JobInstanceId] = B.[JobInstanceId]
where
A.[ItemId] = 0 -- Initializer
or B.[ItemId] is not null -- Other
GO
PRINT N'Creating View [mig_valid].[RelationshipKey]...';
GO
CREATE view [mig_valid].[RelationshipKey]
as
-- All Relationships created by the initializer
-- have ItemId = 0.
select
A.*
from
[mig_item].[RelationshipKey] as A
left outer join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and A.[JobId] = B.[JobId]
and A.[Step] = B.[Step]
and A.[JobInstanceId] = B.[JobInstanceId]
where
A.[ItemId] = 0 -- Initializer
or B.[ItemId] is not null -- Other
GO
PRINT N'Creating View [mig_valid].[Item]...';
GO
CREATE view [mig_valid].[Item]
as
select
A.*
from
[mig_item].[Item] as A
inner join [mig_item].[Id] as C
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[ExportItem]...';
GO
CREATE view [mig_valid].[ExportItem]
as
select
A.*
from
[mig_item].[ExportItem] as A
inner join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and B.[Step] = 0 -- Export
and A.[JobId] = B.[JobId]
and A.[JobInstanceId] = B.[JobInstanceId]
inner join [mig_item].[Id] as C
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[EventUser]...';
GO
CREATE view [mig_valid].[EventUser]
as
select
A.*
from
[mig_item].[EventUser] as A
inner join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and A.[JobId] = B.[JobId]
and A.[Step] = B.[Step]
and A.[JobInstanceId] = B.[JobInstanceId]
inner join [mig_item].[Id] as C
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[EventSystem]...';
GO
CREATE view [mig_valid].[EventSystem]
as
select
A.*
from
[mig_item].[EventSystem] as A
inner join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and A.[JobId] = B.[JobId]
and A.[Step] = B.[Step]
and A.[JobInstanceId] = B.[JobInstanceId]
inner join [mig_item].[Id] as C
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[Dependency]...';
GO
CREATE view [mig_valid].[Dependency]
as
select
A.*
from
[mig_item].[Dependency] as A
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
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[ImportItem]...';
GO
CREATE view [mig_valid].[ImportItem]
as
select
A.*
from
[mig_item].[ImportItem] as A
inner join [mig_item].[Active] as B
on A.[ItemId] = B.[ItemId]
and B.[Step] = 1 -- Conversion
and A.[JobId] = B.[JobId]
and A.[JobInstanceId] = B.[JobInstanceId]
inner join [mig_item].[Id] as C
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[AuditValue]...';
GO
CREATE view [mig_valid].[AuditValue]
as
select
A.*
from
[mig_item].[AuditValue] as A
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
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[AuditSpecifier]...';
GO
CREATE view [mig_valid].[AuditSpecifier]
as
select
A.*
from
[mig_item].[AuditSpecifier] as A
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
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[SavedString]...';
GO
CREATE view [mig_valid].[SavedString]
as
select
A.*
from
[mig_item].[SavedString] as A
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
on A.[ItemId] = C.[ItemId]
where
C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[TargetXml]...';
GO
CREATE VIEW [mig_valid].[TargetXml] AS
select
A.[ItemId]
, B.[PartitionValue]
, A.[CreationDate]
, A.[XmlData]
from
[mig_item].[TargetXml] as A with (NoLock)
inner join [mig_valid].[Item] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
A.[XmlData] is not null
GO
PRINT N'Creating View [mig_valid].[ExportXml]...';
GO
CREATE VIEW [mig_valid].[ExportXml]
AS
select
A.[ItemId]
, B.[PartitionValue]
, A.[CreationDate]
, A.[XmlData]
from
[mig_item].[ExportXml] as A with (NoLock)
inner join [mig_valid].[Item] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
A.[XmlData] is not null
GO
PRINT N'Creating View [mig_valid].[SourceXml]...';
GO
CREATE VIEW [mig_valid].[SourceXml]
AS
select
A.[ItemId]
, B.[PartitionValue]
, A.[CreationDate]
, A.[XmlData]
from
[mig_item].[SourceXml] as A with (NoLock)
inner join [mig_valid].[Item] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
A.[XmlData] is not null
GO
PRINT N'Creating View [mig_tracking].[VwRootItemLink]...';
GO
CREATE view [mig_tracking].[VwRootItemLink]
as
select
D.[ItemId] AS [ParentItemId]
, A.[ItemId] as [ChildItemId]
, B.[EntityID] as [ChildEntityID]
from
[mig_valid].[Dependency] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
inner join [mig_item].[MainKey] as C with (NoLock)
on A.[DependentOnKeyId] = C.[KeyId]
inner join [mig_valid].[Item] as D with (NoLock)
on C.[KeyId] = D.[KeyId]
GO
PRINT N'Creating Function [mig_director].[TranslateImportResult]...';
GO
CREATE FUNCTION [mig_director].[TranslateImportResult](
@result tinyint
)
returns varchar(50)
AS
begin
return case @result
when 0 then 'None'
when 1 then 'Import Failure'
when 2 then 'Import Success'
end
end
GO
PRINT N'Creating Function [mig_director].[TranslateExecutionState]...';
GO
CREATE function [mig_director].[TranslateExecutionState](
@executionState int
)
returns varchar(50)
AS
begin
return case @executionState
when -1 then ''
when 0 then 'Pending'
when 1 then 'Running'
when 2 then 'Completed'
when 3 then 'Cancelled'
when 4 then 'Aborted'
end
end
GO
PRINT N'Creating Function [mig_director].[CalculateElapsedSeconds]...';
GO
CREATE FUNCTION [mig_director].[CalculateElapsedSeconds] (
@timeStart datetime2(7)
, @timeEnd datetime2(7)
, @executionState tinyint
, @now datetime2(7)
) returns int
as
begin
return
case
when @timeStart is null then null
when @timeEnd is null then
case
when @executionState = 1 then datediff(second, @timeStart, @now)-- Running
else null
end
else datediff(second, @timeStart, @timeEnd)
end
end
GO
PRINT N'Creating Function [mig_director].[DefaultSmartText]...';
GO
create FUNCTION [mig_director].[DefaultSmartText](
@xml xml
)
returns varchar(max)
with schemabinding
as
begin
return isnull(@xml.value('(*/text[@language = ''EN''])[1]', 'varchar(max)'), @xml.value('(*/text)[1]', 'varchar(max)'))
end
GO
PRINT N'Creating Function [mig_garbage].[BuildStatement]...';
GO
CREATE 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
------------------------------------------------------------------------------------------------------------------
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'Creating Table [mig_director].[SmartText]...';
GO
CREATE TABLE [mig_director].[SmartText] (
[ItemID] UNIQUEIDENTIFIER NOT NULL,
[Xml] XML NOT NULL,
[DefaultText] AS ([mig_director].[DefaultSmartText]([xml])) PERSISTED,
[CreationDate] DATETIME2 (7) NOT NULL,
[CreatedBy] VARCHAR (50) NOT NULL,
[ModificationDate] DATETIME2 (7) NULL,
[ModifiedBy] VARCHAR (50) NULL,
CONSTRAINT [PK_SmartText] PRIMARY KEY CLUSTERED ([ItemID] ASC)
);
GO
PRINT N'Creating Index [mig_director].[SmartText].[IX_SmartText_Xml]...';
GO
CREATE NONCLUSTERED INDEX [IX_SmartText_Xml]
ON [mig_director].[SmartText]([ItemID] ASC)
INCLUDE([Xml]);
GO
PRINT N'Creating Index [mig_director].[SmartText].[IX_SmartText_Default]...';
GO
CREATE NONCLUSTERED INDEX [IX_SmartText_Default]
ON [mig_director].[SmartText]([ItemID] ASC)
INCLUDE([DefaultText]);
GO
PRINT N'Creating Default Constraint [mig_director].[DF_SmartText_CreationDate]...';
GO
ALTER TABLE [mig_director].[SmartText]
ADD CONSTRAINT [DF_SmartText_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate];
GO
PRINT N'Creating Function [mig_director].[_Export_Statistics]...';
GO
CREATE function [mig_director].[_Export_Statistics]()
returns table
as
-- The function is used from the Director Entity list, where SplitValue is not needed.
-- This is why the join towards mig_valid.Item is an outer join
return (
with list as (
select
B.[PartitionValue]
, C.[EntityID]
, count(*) as [Count]
, sum(
case
when E.Result is null then 0
else 1
end
) as [Processed]
, sum(
case
when isnull(E.Result, 10) = 0 then 1
else 0
end
) as [Exported]
, sum(
case
when isnull(E.Result, 10) = 1 then 1
else 0
end
) as [Dropped]
, sum(
case
when isnull(E.Result, 10) = 2 then 1
else 0
end
) as [Discarded]
from
[mig_item].[Id] as A with (NoLock)
left outer join [mig_item].[Item] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
inner join [mig_item].[ExportKey] as C with (NoLock)
on A.[ItemId] = C.[ItemId]
left outer join [mig_valid].[Item] as D with (NoLock)
on A.[ItemId] = D.[ItemId]
left outer join [mig_valid].[ExportItem] as E with (NoLock)
on A.[ItemId] = E.[ItemId]
where
A.[InvalidatedTime] is null
group by
B.[PartitionValue]
, C.[EntityID]
)
select
C.[PartitionValue]
, A.[EntityID]
, 1 as [Ready]
, B.[BusinessEntityName]
, A.[EntityName]
, C.[Count]
, C.[Exported]
, C.[Dropped]
, C.[Discarded]
, C.[Count] - C.[Processed] as [Difference]
from
[mig_director].[ExportEntity] as A with (NoLock)
inner join [mig_director].[BusinessEntity] as B with (NoLock)
on A.[BusinessEntityID] = B.[BusinessEntityID]
left outer join list as C
on A.[EntityID] = C.[EntityID]
)
GO
PRINT N'Creating Function [mig_director].[_Import_Statistics]...';
GO
CREATE function [mig_director].[_Import_Statistics]()
returns table
as
return (
with list as (
select
B.[PartitionValue]
, C.[EntityID]
, count(*) as [Count]
, sum(
case
when E.Result is null then 0
else 1
end
) as [Processed]
, sum(
case
when isnull(E.Result, 10) = 2 then 1 -- 2:Success
else 0
end
) as [Imported]
, sum(
case
when isnull(E.Result, 10) < 2 then 1 -- 0:None, 1:ImportFailure
else 0
end
) as [Discarded]
from
[mig_item].[Id] as A with (NoLock)
inner join [mig_item].[Item] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
inner join [mig_item].[ExportKey] as C with (NoLock)
on A.[ItemId] = C.[ItemId]
inner join [mig_item].[ExportItem] as D with (NoLock)
on A.[ItemId] = D.[ItemId] and D.[Result] = 0 -- 0:Exported
left outer join [mig_item].[ImportItem] as E with (NoLock)
on A.[ItemId] = E.[ItemId] and E.[JobId] >= D.[JobId]
where
A.[InvalidatedTime] is null
group by
B.[PartitionValue]
, C.[EntityID]
)
select
C.[PartitionValue]
, A.[EntityID]
, B.[BusinessEntityName]
, A.[EntityName]
, C.[Count]
, C.[Imported]
, C.[Discarded]
, C.[Count] - C.[Processed] as [Difference]
from
[mig_director].[ExportEntity] as A with (NoLock)
inner join [mig_director].[BusinessEntity] as B with (NoLock)
on A.[BusinessEntityID] = B.[BusinessEntityID]
left outer join list as C
on A.[EntityID] = C.[EntityID]
where
A.[IsPresent] = 1 and A.[Ignored] = 0
)
GO
PRINT N'Creating Procedure [mig_concurrency].[TokenSummary]...';
GO
CREATE procedure [mig_concurrency].[TokenSummary]
as
select
[Tag]
, [ProcessName]
, count(*) as [Count]
, avg([Attempts]) as AverageAttempts
, max([Attempts]) as MaxAttempts
from
[mig_concurrency].[Token]
where
[ProcessId] is not null
group by
[Tag]
, [ProcessName]
GO
PRINT N'Creating Procedure [mig_concurrency].[TokenRelease]...';
GO
CREATE procedure [mig_concurrency].[TokenRelease]
@tokenId int
as
update [mig_concurrency].[Token] with (RowLock)
set
[ProcessId] = null
, [ProcessName] = null
, [Attempts] = null
where
[Id] = @tokenId
GO
PRINT N'Creating Procedure [mig_concurrency].[TokenRequest]...';
GO
CREATE procedure [mig_concurrency].[TokenRequest]
@tagId tinyint
, @pId int
, @pName varchar(50)
, @timeout int
as
declare @attempts int = 0
declare @success bit = 0
declare @id table([Id] int)
declare @endTime datetime2(7) = dateadd(ms, @timeout, sysdatetime())
while 0 = @success and (0 = @attempts or sysdatetime() < @endTime)
begin
set @attempts = @attempts + 1
update top(1) [mig_concurrency].[Token] with (RowLock)
set
[ProcessId] = @pId
, [ProcessName] = @pName
, [Attempts] = @attempts
output inserted.Id into @id
where
[TagId] = @tagId
and [Available] = 1
and [ProcessId] is null
if 0 = @@rowcount
begin
waitfor delay '00:00:00.2' -- Concurrency Token Idle
end
else
begin
set @success = 1
end
end
select top(1) [Id] from @id
GO
PRINT N'Creating Procedure [mig_concurrency].[TokenClearOrphans]...';
GO
CREATE procedure [mig_concurrency].[TokenClearOrphans]
@runningPIdList varchar(max)
as
declare @runningPIds table([PId] int)
insert @runningPIds select CONVERT(int, [Value]) from [mig_utility].[ParseDelimitedString](@runningPIdList, ',')
update [mig_concurrency].[Token] with (RowLock)
set
[ProcessId] = null
, [ProcessName] = null
, [Attempts] = null
from
[mig_concurrency].[Token] as A
left outer join @runningPIds as B
on A.[ProcessId] = B.[PId]
where
B.[PId] is null
GO
PRINT N'Creating Procedure [mig_concurrency].[TokenSetLimit]...';
GO
CREATE procedure [mig_concurrency].[TokenSetLimit]
@tagId tinyint
, @tagName varchar(50)
, @limit int
as
declare @diff int
declare @current int = (
select count(*)
from [mig_concurrency].[Token] with (NoLock)
where
[TagId] = @tagId
and [Available] = 1
)
if @current < @limit
begin
set @diff = @limit - @current
-- First make any existing, unavailable tokens available
update top(@diff) [mig_concurrency].[Token] with (RowLock)
set [Available] = 1
where
[TagId] = @tagId
and [Available] = 0
set @diff = @diff - @@rowcount
-- If not enough insert new tokens for remainder
while 0 < @diff
begin
insert [mig_concurrency].[Token] with (RowLock) (
TagId
, Tag
, [Available]
) values (
@tagId
, @tagName
, 1
)
set @diff = @diff - 1
end
end
else
begin
set @diff = @current - @limit
update top(@diff) [mig_concurrency].[Token] with (RowLock)
set [Available] = 0
where
[TagId] = @tagId
and [Available] = 1
end
GO
PRINT N'Creating Procedure [mig_concurrency].[TokenClearAll]...';
GO
CREATE procedure [mig_concurrency].[TokenClearAll]
as
truncate table [mig_concurrency].[Token]
GO
PRINT N'Creating Procedure [mig_director].[EntityNames_List]...';
GO
CREATE procedure [mig_director].[EntityNames_List]
as
select
A.[EntityID]
, A.[EntityName]
, A.[BusinessEntityID]
, B.[BusinessEntityName]
, A.[Ignored]
, A.[IsPresent]
, convert(bit, case
when exists (select top(1) 1 from [mig_item].[ExportKey] where [EntityID] = A.[EntityID]) then 1
else 0
end) as [HasItems]
from
[mig_director].[ExportEntity] as A
inner join [mig_director].[BusinessEntity] as B
on A.[BusinessEntityId] = B.[BusinessEntityId]
order by
A.[EntityName]
GO
PRINT N'Creating Procedure [mig_director].[ValuesetProvider_List]...';
GO
create procedure [mig_director].[ValuesetProvider_List]
as
select
[ItemID]
, [Xml]
from
[mig_director].[ValuesetProvider]
where
[Present] = 1
GO
PRINT N'Creating Procedure [mig_director].[ValuesetProvider_InsertOrUpdate]...';
GO
CREATE PROCEDURE [mig_director].[ValuesetProvider_InsertOrUpdate]
@projectID uniqueidentifier
, @projectType varchar(50)
, @itemID uniqueidentifier
, @name varchar(50)
, @xml xml
, @user varchar(50)
AS
update [mig_director].[ValuesetProvider]
set
[ProjectType] = @projectType
, [Name] = @name
, [Xml] = @xml
, [Present] = 1
, [ModificationDate] = sysdatetime()
, [ModifiedBy] = @user
where
[ItemID] = @itemID and [ProjectID] = @projectID
if 0 = @@rowcount
begin
insert into [mig_director].[ValuesetProvider] (
[ItemID]
, [ProjectID]
, [ProjectType]
, [Name]
, [Xml]
, [Present]
, [CreatedBy]
) values (
@itemID
, @projectID
, @projectType
, @name
, @xml
, 1
, @user
)
end
GO
PRINT N'Creating Procedure [mig_director].[Valueset_Update]...';
GO
CREATE PROCEDURE [mig_director].[Valueset_Update]
@projectID uniqueidentifier
, @itemID uniqueidentifier
, @rowCount int = null
, @user varchar(50) = null
AS
update [mig_director].[Valueset]
set
[LastUpdateDate] = case when @rowCount is null then null else sysdatetime() end
, [LastUpdateBy] = @user
, [RowCount] = @rowCount
where
[ProjectID] = @projectID
and [ItemID] = @itemID
GO
PRINT N'Creating Procedure [mig_director].[Valueset_SaveBaseline]...';
GO
CREATE procedure [mig_director].[Valueset_SaveBaseline]
@source bit = 0
as
declare @baselines table(
[ItemID] uniqueidentifier
, [ProjectID] uniqueidentifier
, [RowCount] bigint
)
insert @baselines
select
[ItemID]
, [ProjectID]
, [RowCount]
from
[mig_director].[Valueset]
where
(@source = 0 and [ProjectType] <> 'Source')
or (@source = 1 and [ProjectType] = 'Source')
delete from [mig_director].[ValuesetBaseline]
from
[mig_director].[ValuesetBaseline] as A
inner join @baselines as B
on
A.[ItemID] = B.[ItemID]
and A.[ProjectID] = B.[ProjectID]
insert [mig_director].[ValuesetBaseline] (
[ItemID]
, [ProjectID]
, [BaselineCount]
)
select
[ItemID]
, [ProjectID]
, [RowCount]
from @baselines
where
[RowCount] is not null
GO
PRINT N'Creating Procedure [mig_director].[Valueset_List]...';
GO
CREATE PROCEDURE [mig_director].[Valueset_List]
@projectTypes xml = null
as
;with
dc as (
select
A.[ItemID]
, A.[Name]
, A.[ProjectID]
, A.[ProjectType]
, A.[SchemaName]
, A.[ValuesetType]
, A.[LastUpdateDate]
, A.[LastUpdateBy]
, A.[RowCount] as [Count]
, C.[BaselineCount] as [Baseline]
from
[mig_director].[Valueset] as A
inner join [mig_director].[Engine] as B
on
A.[ProjectID] = B.[ProjectID]
left outer join [mig_director].[ValuesetBaseline] as C
on
A.[ItemID] = C.[ItemID]
where
A.[Present] = 1
)
, projectTypes as (
select
[Value] as [ProjectType]
from
[mig_utility].[ParseInfoList](@projectTypes)
)
select
A.*
from
dc as A
left outer join projectTypes as B
on A.[ProjectType] = B.[ProjectType]
where
(@projectTypes is null or B.[ProjectType] is not null)
order by
A.[Name]
GO
PRINT N'Creating Procedure [mig_director].[Valueset_InsertOrUpdate]...';
GO
CREATE procedure [mig_director].[Valueset_InsertOrUpdate] (
@itemID uniqueidentifier
, @name varchar(50)
, @schema varchar(50)
, @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'Creating Procedure [mig_director].[Valueset_Fetch]...';
GO
CREATE PROCEDURE [mig_director].[Valueset_Fetch]
@projectId uniqueidentifier
, @itemId uniqueidentifier
as
select
A.[ValuesetType]
, A.[ProviderID]
, A.[DataServicesUsage]
, A.[Columns]
, A.[SchemaName]
, A.[Name]
, B.[Xml]
from
[mig_director].[Valueset] as A
left outer join [mig_director].[ValuesetProvider] as B
on A.[ProjectID] = B.[ProjectID] and A.[ProviderID] = B.[ItemID]
where A.ItemId = @itemId and A.[ProjectID] = @projectId
GO
PRINT N'Creating Procedure [mig_director].[Import_Entity_StatusList]...';
GO
CREATE procedure [mig_director].[Import_Entity_StatusList]
as
select
[EntityID]
, [BusinessEntityName]
, [EntityName]
, sum([Count]) as [Exported]
, sum([Discarded]) as [Discarded]
, sum([Imported]) as [Imported]
, sum([Difference]) as [Difference]
from
[mig_director].[_Import_Statistics]()
group by
[EntityID]
, [BusinessEntityName]
, [EntityName]
order by
[BusinessEntityName]
, [EntityName]
GO
PRINT N'Creating Procedure [mig_director].[Concurrency_Set]...';
GO
CREATE procedure [mig_director].[Concurrency_Set](
@tag varchar(50)
, @limit smallint = null
)
as
if @limit is null
begin
delete from [mig_director].[Concurrency]
where
[Tag] = @tag
end
else
begin
update [mig_director].[Concurrency]
set
[Limit] = @limit
where
[Tag] = @tag
if 0 = @@rowCount
begin
insert [mig_director].[Concurrency] (
[Tag]
, [Limit]
) values (
@tag
, @limit
)
end
end
GO
PRINT N'Creating Procedure [mig_director].[Concurrency_List]...';
GO
CREATE procedure [mig_director].[Concurrency_List]
as
select
[Tag]
, [Limit]
from
[mig_director].[Concurrency]
GO
PRINT N'Creating Procedure [mig_director].[Command_MarkExecuted]...';
GO
CREATE PROCEDURE [mig_director].[Command_MarkExecuted]
@commandId bigint
, @elapsed bigint
, @syncResult varchar(max)
, @syncError bit
AS
update [mig_director].[Command]
set
[EndExecutionDate] = sysdatetime()
, [Elapsed] = @elapsed
, [SyncResult] = @syncResult
, [SyncError] = @syncError
where
[CommandId] = @commandId
GO
PRINT N'Creating Procedure [mig_director].[Command_Insert]...';
GO
CREATE PROCEDURE [mig_director].[Command_Insert] (
@commandType nvarchar(50)
, @parameters xml
, @user nvarchar(50)
)
AS
insert [mig_director].[Command] (
[CommandType]
, [Parameters]
, [CreatedBy]
) values (
@commandType
, @parameters
, @user
)
select convert(bigint, scope_identity())
GO
PRINT N'Creating Procedure [mig_director].[Command_GetResult]...';
GO
CREATE PROCEDURE [mig_director].[Command_GetResult]
@commandId bigint
, @executionDate datetime2(7) = null output
, @syncResult varchar(max) = null output
, @syncError bit = null output
AS
select
@executionDate = [EndExecutionDate]
, @syncResult = [SyncResult]
, @syncError = [SyncError]
from
[mig_director].[Command]
where
[CommandId] = @commandId
GO
PRINT N'Creating Procedure [mig_director].[Command_ConsumeForExecution]...';
GO
CREATE PROCEDURE [mig_director].[Command_ConsumeForExecution]
AS
begin transaction
-- Get the new commands
select
[CommandId]
, [CommandType]
, [Parameters]
, [CreatedBy]
into #commands
from
[mig_director].[Command] WITH (UPDLOCK, ROWLOCK, READPAST)
where
[StartExecutionDate] is null
order by
[CreationDate]
-- mark the new commads as processing (so we don't get them next time around)
-- Using temp table in order not to delete any new commands just inserted after the select
update [mig_director].[Command]
set [StartExecutionDate] = sysdatetime()
where [CommandId] in (select [CommandId] from #commands)
commit transaction
select * from #commands
GO
PRINT N'Creating Procedure [mig_director].[Command_CleanUp]...';
GO
create PROCEDURE [mig_director].[Command_CleanUp]
AS
-- cleanup (leave only 1000 newest and all unexecuted commands)
delete from [mig_director].[Command]
where
[EndExecutionDate] is not null
and [CommandId] < (
select
min([CommandId])
from (
select top 1000
[CommandId]
, 'c' as [Constant]
from
[mig_director].[Command]
order by
[CommandId] desc
) as T
group by
[Constant]
)
GO
PRINT N'Creating Procedure [mig_director].[Audit_GetItems]...';
GO
create procedure [mig_director].[Audit_GetItems]
as
select
A.[ItemId]
, C.[EntityID]
, A.[PartitionValue]
, D.[SourceKey]
, B.[KeyValue] as [ExportedKey]
, G.[TargetKey]
, E.[AuditValueID]
, E.[ParentAuditValueID]
, E.[AmountDebit]
, E.[AmountCredit]
, E.[Count]
, E.[Success]
, E.[InheritedSuccess]
, F.[AuditHeaderClassID]
, F.[AuditGroupClassID]
, F.[AuditSpecifierClassID]
, F.[AuditSpecifierID]
, F.[AuditSpecifierValue]
from
[mig_valid].[Item] as A with (NoLock)
inner join [mig_item].[MainKey] as B with (NoLock)
on A.[KeyId] = B.[KeyId]
inner join [mig_item].[ExportKey] as C with (NoLock)
on A.[ItemId] = C.[ItemId]
inner join [mig_valid].[ExportItem] as D with (NoLock)
on A.[ItemId] = D.[ItemId] and D.[Result] <> 1 -- Dropped
inner join [mig_valid].[AuditValue] as E with (NoLock)
on A.[ItemId] = E.[ItemId]
left outer join [mig_valid].[AuditSpecifier] as F with (NoLock)
on E.[AuditValueID] = F.[AuditValueID]
left outer join [mig_valid].[ImportItem] as G with (NoLock)
on A.[ItemId] = G.[ItemId]
order by
A.[ItemId]
, E.[ParentAuditValueID]
, E.[AuditValueID]
, F.[AuditHeaderClassID]
, F.[AuditGroupClassID]
, F.[AuditSpecifierClassID]
GO
PRINT N'Creating Procedure [mig_director].[Audit_GetHeaders]...';
GO
CREATE procedure [mig_director].[Audit_GetHeaders]
as
with [header] (
[AuditHeaderClassID]
) as (
select distinct
[AuditHeaderClassID]
from
[mig_valid].[AuditSpecifier]
)
select
[AuditHeaderClassID] as [HeaderClassID]
, 'Header ' + convert(nvarchar(10), row_number() over (order by [AuditHeaderClassID])) as [HeaderLabel]
from
[header]
;
with [group] (
[AuditHeaderClassID]
, [AuditGroupClassID]
) as (
select distinct
[AuditHeaderClassID]
, [AuditGroupClassID]
from
[mig_valid].[AuditSpecifier]
)
select
[AuditHeaderClassID] as [HeaderClassID]
, [AuditGroupClassID] as [GroupClassID]
, 'Group ' + convert(nvarchar(10), row_number() over (order by [AuditHeaderClassID], [AuditGroupClassID])) as [GroupLabel]
from
[group]
;
with [specifier] (
[AuditGroupClassID]
, [AuditSpecifierClassID]
) as (
select distinct
[AuditGroupClassID]
, [AuditSpecifierClassID]
from
[mig_valid].[AuditSpecifier]
)
select
[AuditGroupClassID] as [GroupClassID]
, [AuditSpecifierClassID] as [SpecifierClassID]
, 'Specifier ' + convert(nvarchar(10), row_number() over (order by [AuditGroupClassID], [AuditSpecifierClassID])) as [SpecifierLabel]
from
[specifier]
GO
PRINT N'Creating Procedure [mig_director].[Audit_EntityList]...';
GO
CREATE procedure [mig_director].[Audit_EntityList]
as
with specCount as (
select
A.[ItemId]
, count(*) as [Count]
from
[mig_valid].[AuditValue] as A with (NoLock)
left outer join [mig_valid].[AuditSpecifier] as B with (NoLock)
on A.[AuditValueID] = B.[AuditValueID]
group by
A.[ItemId]
)
select
E.[BusinessEntityName]
, C.[EntityID]
, D.[EntityName]
, sum(specCount.[Count])
from
specCount
inner join [mig_valid].[Item] as A with (NoLock)
on specCount.[ItemId] = A.[ItemId]
inner join [mig_item].[MainKey] as B with (NoLock)
on A.[KeyId] = B.[KeyId]
inner join [mig_item].[ExportKey] as C with (NoLock)
on A.[ItemId] = C.[ItemId]
inner join [mig_director].[ExportEntity] as D with (NoLock)
on C.[EntityID] = D.[EntityID]
inner join [mig_director].[BusinessEntity] as E with (NoLock)
on D.[BusinessEntityID] = E.[BusinessEntityID]
group by
E.[BusinessEntityName]
, C.[EntityID]
, D.[EntityName]
order by
E.[BusinessEntityName]
, D.[EntityName]
GO
PRINT N'Creating Procedure [mig_director].[_ItemSet_SetReady]...';
GO
create procedure [mig_director].[_ItemSet_SetReady](
@setId int
, @itemCount int
)
as
update [mig_director].[ItemSet]
set
[ReadyDate] = sysdatetime()
, [ItemCount] = @itemCount
where
[SetId] = @setId
GO
PRINT N'Creating Procedure [mig_director].[_ItemSet_Insert]...';
GO
create procedure [mig_director].[_ItemSet_Insert](
@type varchar(50)
, @source varchar(max)
, @setId int output
)
as
insert [mig_director].[ItemSet] (
[Type]
, [Source]
) values (
@type
, @source
)
set @setId = scope_identity()
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_MarkNotPresent]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_MarkNotPresent]
as
update [mig_director].[ExportSourceView]
set
[IsPresent] = 0
update [mig_director].[ExportSourceViewDependency]
set
[IsPresent] = 0
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_InsertOrUpdate]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_InsertOrUpdate]
@itemID uniqueidentifier
, @viewSchema varchar(50)
, @viewName varchar(50)
, @testableQuery varchar(max)
, @wasCreated bit
AS
declare @queryChanged bit
declare @old table([TestableQuery] varchar(max) collate Latin1_General_CS_AS) -- Collate to ensure case-sensitive comparison
update [mig_director].[ExportSourceView]
set
[ViewSchema] = @viewSchema
, [ViewName] = @viewName
, [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]
, [TestableQuery]
, [IsPresent]
) values (
@itemID
, @viewSchema
, @viewName
, @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'Creating Procedure [mig_director].[Export_SourceView_DependencyInsertOrUpdate]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_DependencyInsertOrUpdate]
(
@viewID uniqueidentifier
, @itemID uniqueidentifier
, @sourceType varchar(50)
)
as
update [mig_director].[ExportSourceViewDependency]
set
[IsPresent] = 1
where
[ViewID] = @viewID
and [DependentOnID] = @itemID
and [SourceType] = @sourceType
if 0 = @@rowCount
begin
insert [mig_director].[ExportSourceViewDependency] (
[ViewID]
, [DependentOnID]
, [SourceType]
, [IsPresent]
) values (
@viewID
, @itemID
, @sourceType
, 1
)
end
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_StatusList]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceTable_StatusList]
@fullName nvarchar(50) = 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 entitiesnot 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(100)
, [TableSchema] varchar(50)
, [TableName] varchar(50)
, [ReformatExtensionID] uniqueidentifier
, [BaselineCount] int 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.[ReformatExtensionID], @guidEmpty)
, B.[BaselineCount]
, 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]
, [ReformatExtensionID]
, 0 as [FileCount]
, convert(datetime, null) as [FileDate]
, convert(datetime, null) as [FormattedFileDate]
, convert(datetime, null) as [LoadedFileDate]
, convert(datetime, null) as [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(C.[FullName], '') as [FullName_Formatted]
, isnull(B.[ReformatExtensionID], @guidEmpty) as [ReformatExtensionID]
, B.[FileDate] as [FileDate]
, C.[FileDate] as [FormattedFileDate]
, C.[DateOfLoadedFile]
, C.[LoadedDate]
, isnull(C.[LoadedCount], 0) as [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
left outer join [mig_director].[ExportFormattedFile] as C
on B.[FileName] = C.[FileName]
and C.[FileDate] is not null
select
[ID]
, [Name]
, [IsDefault]
from
[mig_director].[ExtensionUsage]
where
[ExtensionType] = 1 -- 1: Reformatter
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_SetReformat]...';
GO
create PROCEDURE [mig_director].[Export_SourceTable_SetReformat]
@fullName nvarchar(50)
, @reformatExtensionID uniqueidentifier = null
as
update [mig_director].[ExportSourceTable]
set
[ReformatExtensionID] = @reformatExtensionID
where
[FullName] = @fullName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_SetLoaded]...';
GO
create PROCEDURE [mig_director].[Export_SourceTable_SetLoaded]
(
@fullName varchar(100)
, @loadedCount int
)
AS
update [mig_director].[ExportSourceTable]
set
[LoadedCount] = @loadedCount
where
[FullName] = @fullName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_SetBaseline]...';
GO
CREATE procedure [mig_director].[Export_SourceTable_SetBaseline]
as
declare @now datetime2(7) = sysdatetime()
delete from [mig_director].[ExportSourceTable_Baseline]
from
[mig_director].[ExportSourceTable_Baseline] as A
inner join [mig_director].[ExportSourceTable] as B
on
A.[FullName] = B.[FullName]
and (B.[LoadedCount] is null or B.[IsPresent] = 0)
update [mig_director].[ExportSourceTable_Baseline]
set
[BaselineCount] = B.[LoadedCount]
, [ModificationDate] = @now
from
[mig_director].[ExportSourceTable_Baseline] as A
inner join [mig_director].[ExportSourceTable] as B
on
A.[FullName] = B.[FullName]
insert [mig_director].[ExportSourceTable_Baseline] (
[TableSchema]
, [TableName]
, [BaselineCount]
, [CreationDate]
)
select
A.[TableSchema]
, A.[TableName]
, A.[LoadedCount]
, @now
from
[mig_director].[ExportSourceTable] as A
left outer join [mig_director].[ExportSourceTable_Baseline] as B
on
A.[FullName] = B.[FullName]
where
B.[FullName] is null
and A.[LoadedCount] is not null
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_MarkNotPresent]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceTable_MarkNotPresent]
as
update [mig_director].[ExportSourceTable]
set
[IsPresent] = 0
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_InsertOrUpdate]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceTable_InsertOrUpdate]
@itemID uniqueidentifier
, @tableSchema varchar(50)
, @tableName varchar(50)
, @wasCreated bit
AS
update [mig_director].[ExportSourceTable]
set
[TableSchema] = @tableSchema
, [LoadedCount] = case @wasCreated
when 1 then null
else [LoadedCount]
end
, [IsPresent] = 1
, [ItemID] = @itemID
where
[ItemID] = @itemID
if 0 = @@rowCount
begin
declare @reformatExtensionID uniqueidentifier
select top(1)
@reformatExtensionID = [ID]
from
[mig_director].[ExtensionUsage]
where
[ExtensionType] = 1 -- 1: Reformatter
and [IsDefault] = 1
insert [mig_director].[ExportSourceTable] (
[ItemID]
, [TableSchema]
, [TableName]
, [ReformatExtensionID]
, [LoadedCount]
, [IsPresent]
) values (
@itemID
, @tableSchema
, @tableName
, @reformatExtensionID
, null
, 1
)
end
if 1 = @wasCreated
begin
update [mig_director].[ExportFormattedFile]
set
[LoadedCount] = 0
, [LoadedDate] = null
, [DateOfLoadedFile] = null
from
[mig_director].[ExportFormattedFile] as A
inner join [mig_director].[ExportSourceFile] as B
on
A.[FileName] = B.[FileName]
and B.[SourceTableFullName] = @tableSchema + '.' + @tableName
end
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceFile_SetReformat]...';
GO
create PROCEDURE [mig_director].[Export_SourceFile_SetReformat]
@fileName varchar(100)
, @reformatExtensionID uniqueidentifier = null
as
update [mig_director].[ExportSourceFile]
set
[ReformatExtensionID] = @reformatExtensionID
where
[FileName] = @fileName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceFile_SetPresent]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceFile_SetPresent]
(
@fileName varchar(512),
@fullName varchar(1024),
@sourceTableFullName varchar(50),
@fileDate datetime2(7) = null
)
AS
if @fileDate is null
begin
delete [mig_director].[ExportSourceFile]
where
[FileName] = @fileName
and [FileName] not like '%.ExtensionLoad'
and [ReformatExtensionID] 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'Creating Procedure [mig_director].[Export_SourceFile_List]...';
GO
create PROCEDURE [mig_director].[Export_SourceFile_List]
AS
select
[FullName]
, [FileDate]
from
[mig_director].[ExportSourceFile]
GO
PRINT N'Creating Procedure [mig_director].[Export_JobCheckRestart]...';
GO
CREATE procedure [mig_director].[Export_JobCheckRestart]
@jobId int
as
select
C.[EntityName]
, sum(
case
when D.Result = 0 then 1
else 0
end
) as [Exported]
, sum(
case
when D.Result = 1 then 1
else 0
end
) as [Dropped]
, sum(
case
when D.Result = 2 then 1
else 0
end
) as [Discarded]
from
[mig_item].[Processed] as A
inner join [mig_item].[ExportKey] as B
on A.[ItemId] = B.[ItemId]
inner join [mig_director].[ExportEntity] as C
on B.[EntityID] = C.[EntityID]
inner join [mig_item].[ExportItem] as D with (NoLock)
on A.[ItemId] = D.[ItemId] and @jobId = D.[JobId]
where
A.[JobId] = @jobId
group by
C.[EntityName]
GO
PRINT N'Creating Procedure [mig_director].[Export_FormattedFile_SetTruncated]...';
GO
create PROCEDURE [mig_director].[Export_FormattedFile_SetTruncated]
(
@fullName varchar(100)
)
AS
update [mig_director].[ExportFormattedFile]
set
[LoadedCount] = 0
, [DateOfLoadedFile] = null
, [LoadedDate] = null
from
[mig_director].[ExportFormattedFile] as A
inner join [mig_director].[ExportSourceFile] as B
on A.[FileName] = B.[FileName]
and B.[SourceTableFullName] = @fullName
GO
PRINT N'Creating Procedure [mig_director].[Export_FormattedFile_SetPresent]...';
GO
CREATE PROCEDURE [mig_director].[Export_FormattedFile_SetPresent]
(
@fileName varchar(512),
@fullName varchar(1024),
@fileDate datetime2(7) = null
)
AS
if @fileDate is null
begin
delete [mig_director].[ExportFormattedFile]
where
[FileName] = @fileName
and [FileName] not like '%.ExtensionLoad'
and [LoadedDate] is null
end
else
begin
update [mig_director].[ExportFormattedFile]
set
[FullName] = @fullName
, [FileDate] = @fileDate
where
[FileName] = @fileName
if 0 = @@rowcount
begin
insert into [mig_director].[ExportFormattedFile] (
[FileName]
, [FullName]
, [FileDate]
) values (
@fileName
, @fullName
, @fileDate
)
end
end
GO
PRINT N'Creating Procedure [mig_director].[Export_FormattedFile_SetLoaded]...';
GO
CREATE PROCEDURE [mig_director].[Export_FormattedFile_SetLoaded]
(
@fileName varchar(512),
@loadedCount int
)
AS
update [mig_director].[ExportFormattedFile]
set
[LoadedCount] = @loadedCount
, [DateOfLoadedFile] = [FileDate]
, [LoadedDate] = sysdatetime()
where
[FileName] = @fileName
GO
PRINT N'Creating Procedure [mig_director].[Export_FormattedFile_List]...';
GO
CREATE PROCEDURE [mig_director].[Export_FormattedFile_List]
AS
select
[FullName]
, [FileDate]
from
[mig_director].[ExportFormattedFile]
GO
PRINT N'Creating Procedure [mig_director].[Export_Entity_StatusList]...';
GO
CREATE procedure [mig_director].[Export_Entity_StatusList]
AS
select
[EntityID]
, [Ready]
, [BusinessEntityName]
, [EntityName]
, sum([Count]) as [Source]
, sum([Dropped]) as [Dropped]
, sum([Discarded]) as [Discarded]
, sum([Exported]) as [Exported]
, sum([Difference]) as [Difference]
from
[mig_director].[_Export_Statistics]()
group by
[EntityID]
, [Ready]
, [BusinessEntityName]
, [EntityName]
order by
[BusinessEntityName]
, [EntityName]
;with
valuesets as (
select
A.*
from
[mig_director].[Valueset] as A
inner join [mig_director].[Engine] as B
on
A.[ProjectID] = B.[ProjectID]
)
select
A.[EntityID]
, A.[SourceID]
, coalesce(B.[FullName], C.[FullName], 'Vs.' + D.[Name]) as [SourceFullName]
, A.[SourceType]
, case A.[SourceType]
when 'View' then B.[LoadedDate]
when 'Table' then null
else D.[LastUpdateDate]
end as [LoadedDate]
, case A.[SourceType]
when 'View' then B.[LoadedCount]
when 'Table' then null
else D.[RowCount]
end as [LoadedCount]
from
[mig_director].[ExportEntitySourceDependency] as A
left outer join [mig_director].[ExportSourceView] as B
on
A.[SourceType] = 'View' and A.[SourceID] = B.[ItemID]
left outer join [mig_director].[ExportSourceTable] as C
on
A.[SourceType] = 'Table' and A.[SourceID] = C.[ItemID]
left outer join valuesets as D
on
A.[SourceType] = 'Valueset' and A.[SourceID] = D.[ItemID]
where
A.[IsPresent] = 1
GO
PRINT N'Creating Procedure [mig_director].[Export_Entity_SourceDependencyInsertOrUpdate]...';
GO
-- Batch submitted through debugger: SQLQuery2.sql|7|0|C:\Users\lars\AppData\Local\Temp\~vs983D.sql
CREATE PROCEDURE [mig_director].[Export_Entity_SourceDependencyInsertOrUpdate]
(
@entityID uniqueidentifier,
@itemID uniqueidentifier,
@sourceType varchar(50)
)
AS
update [mig_director].[ExportEntitySourceDependency]
set
[Ispresent] = 1
, [SourceType] = @sourceType
where
[EntityID] = @entityID
and [SourceID] = @itemID
if 0 = @@rowcount
begin
insert [mig_director].[ExportEntitySourceDependency] (
[EntityID]
, [SourceID]
, [SourceType]
, [IsPresent]
) values (
@entityID
, @itemID
, @sourceType
, 1
)
end
GO
PRINT N'Creating Procedure [mig_director].[Export_Entity_MarkNotPresent]...';
GO
CREATE PROCEDURE [mig_director].[Export_Entity_MarkNotPresent]
as
update [mig_director].[ExportEntity]
set
[IsPresent] = 0
update [mig_director].[ExportEntitySourceDependency]
set
[IsPresent] = 0
GO
PRINT N'Creating Procedure [mig_director].[Export_Entity_InsertOrUpdate]...';
GO
CREATE PROCEDURE [mig_director].[Export_Entity_InsertOrUpdate]
(
@entityID uniqueidentifier
, @entityName varchar(256)
, @businessEntityID uniqueidentifier
, @businessEntityName varchar(128)
)
AS
update [mig_director].[ExportEntity]
set
[EntityName] = @entityName
, [BusinessEntityID] = @businessEntityID
, [IsPresent] = 1
where
[EntityId] = @entityID
if 0 = @@rowcount
begin
insert [mig_director].[ExportEntity] (
[EntityID]
, [EntityName]
, [BusinessEntityID]
, [IsPresent]
) values (
@entityID
, @entityName
, @businessEntityID
, 1
)
end
update [mig_director].[BusinessEntity]
set
[BusinessEntityName] = @businessEntityName
where
[BusinessEntityId] = @businessEntityID
if 0 = @@rowcount
begin
insert [mig_director].[BusinessEntity] (
[BusinessEntityID]
, [BusinessEntityName]
) values (
@businessEntityID
, @businessEntityName
)
end
GO
PRINT N'Creating Procedure [mig_director].[Event_List]...';
GO
CREATE procedure [mig_director].[Event_List]
@export bit
, @dispositions varchar(max) = null
, @impacts varchar(max) = null
, @entities varchar(max) = null
as
declare @dispositionTable table([Disposition] int, primary key (Disposition))
declare @impactTable table([Impact] int, primary key (Impact))
declare @entitiesTable table([EntityID] uniqueidentifier primary key (EntityID))
if @dispositions is not null
begin
insert into @dispositionTable
select distinct convert(int, [Value]) as [Disposition] from [mig_utility].[ParseDelimitedString](@dispositions, ',')
end
if @impacts is not null
begin
insert into @impactTable
select distinct convert(int, [Value]) as [Impact] from [mig_utility].[ParseDelimitedString](@impacts, ',')
end
if @entities is not null
begin
insert into @entitiesTable
select distinct [Id] from [mig_utility].[ParseGuids](@entities, ',')
end
;with
systemEvents as (
select
C.[EntityID]
, A.[Step]
, A.[Engine]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[Message]
, count(*) as [Count]
, count(distinct A.[ItemId]) as [Items]
from
[mig_item].[EventSystem] as A with (NoLock)
inner join [mig_item].[Id] as B with (noLock)
on A.[ItemId] = B.[ItemId] and B.[InvalidatedTime] is null
inner join [mig_item].[ExportKey] as C with (NoLock)
on A.[ItemId] = C.[ItemId]
inner join [mig_item].[Active] as D with (NoLock)
on A.[ItemId] = D.[ItemId]
and A.[JobId] = D.[JobId]
and A.[JobInstanceId] = D.[JobInstanceId]
and A.[Step] = D.[Step]
where
((1 = @export and 0 = A.[Step]) or (0 = @export and 1 = A.[Step]))
and (@dispositions is null or A.[Disposition] in (select [Disposition] from @dispositionTable))
and (@impacts is null or A.[Impact] in (select [Impact] from @impactTable))
and (@entities is null or C.[EntityID] in (select [EntityID] from @entitiesTable))
group by
C.[EntityID]
, A.[Step]
, A.[Engine]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[Message]
)
, userEvents as (
select
C.[EntityID]
, A.[Step]
, A.[Engine]
, A.[EventID]
, A.[EventName]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[EventTextID]
, count(*) as [Count]
, count(distinct A.[ItemId]) as [Items]
from
[mig_item].[EventUser] as A with (NoLock)
inner join [mig_item].[Id] as B with (noLock)
on A.[ItemId] = B.[ItemId] and B.[InvalidatedTime] is null
inner join [mig_item].[ExportKey] as C with (NoLock)
on A.[ItemId] = C.[ItemId]
inner join [mig_item].[Active] as D with (NoLock)
on A.[ItemId] = D.[ItemId]
and A.[JobId] = D.[JobId]
and A.[JobInstanceId] = D.[JobInstanceId]
and A.[Step] = D.[Step]
where
((1 = @export and 0 = A.[Step]) or (0 = @export and 1 = A.[Step]))
and (@dispositions is null or A.[Disposition] in (select [Disposition] from @dispositionTable))
and (@impacts is null or A.[Impact] in (select [Impact] from @impactTable))
and (@entities is null or C.[EntityID] in (select [EntityID] from @entitiesTable))
group by
C.[EntityID]
, A.[Step]
, A.[Engine]
, A.[EventID]
, A.[EventName]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[EventTextID]
)
, allEvents as (
select
A.[EntityID]
, A.[Step]
, A.[Engine]
, A.[EventID]
, A.[EventName]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, isnull(substring(B.[DefaultText], 1, 2000), 'Text not found (' + lower(convert(varchar(50), A.[EventTextID])) + ')') as [Message]
, A.[Count]
, A.[Items]
from
userEvents as A
left outer join [mig_director].[SmartText] as B with (NoLock)
on A.[EventTextID] = B.[ItemID]
union all
select
[EntityID]
, [Step]
, [Engine]
, cast(cast(0 as binary) as uniqueidentifier) -- Guid.Empty
, '(System)'
, [Disposition]
, [Impact]
, [Receiver]
, [Message]
, [Count]
, [Items]
from
systemEvents
)
select
B.[EntityName]
, A.[Disposition]
, A.[Impact]
, A.[EntityID]
, A.[Step]
, A.[Engine]
, A.[EventID]
, A.[EventName]
, A.[Receiver]
, A.[Message]
, A.[Count]
, A.[Items]
from
allEvents as A
inner join [mig_director].[ExportEntity] as B with (NoLock)
on A.[EntityID] = B.[EntityID] and B.[IsPresent] = 1
order by
B.[EntityName]
, A.[Disposition]
, A.[Impact]
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_SetAllAborted]...';
GO
create PROCEDURE [mig_director].[JobInstance_SetAllAborted] (
@jobInstanceId int
)
as
update [mig_director].[JobInstance]
set
[ExecutionState] = 4
where
[ExecutionState] < 2
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_List]...';
GO
CREATE procedure [mig_director].[JobInstance_List]
@jobId int
as
declare @now datetime2(7) = sysdatetime()
select
A.[JobId]
, A.[Id] as [JobInstanceId]
, A.[ClientID]
, A.[ExecutionState] as [ExecutionStateNum]
, A.[Result] as [ResultNum]
, A.[TimeStart]
, A.[Priority] as [PriorityNum]
, A.[TimeEnd]
, [mig_director].[CalculateElapsedSeconds](A.[TimeStart], A.[TimeEnd], A.[ExecutionState], @now) as [ElapsedSeconds]
, A.[ProcessId]
, A.[ProcessName]
, A.[RuntimeVersion]
, A.[CreationDate]
, A.[CreatedBy]
, case
when (select top(1) 1 from [mig_profile].[Job] as B where A.[Id] = B.[JobInstanceId]) is null then ''
else 'Profile'
end as [Profile]
from
[mig_director].[JobInstance] as A
where
[JobId] = @jobId
order by
[Id] desc
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_LogEntryInsert]...';
GO
CREATE PROCEDURE [mig_director].[JobInstance_LogEntryInsert] (
@jobInstanceId int
, @logType tinyint
, @message varchar(max)
, @entryId bigint output
)
as
insert [mig_director].[JobInstanceLog] (
[JobInstanceId]
, [LogType]
, [Message]
) values (
@jobInstanceId
, @logType
, @message
)
set @entryId = cast(scope_identity() as bigint)
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_Insert]...';
GO
CREATE PROCEDURE [mig_director].[JobInstance_Insert] (
@jobId int
, @clientID uniqueidentifier
, @priority tinyint
, @user varchar(50)
)
as
insert [mig_director].[JobInstance] (
[JobId]
, [ClientID]
, [Priority]
, [CreatedBy]
) values (
@jobId
, @clientID
, @priority
, @user
)
select convert(int, scope_identity())
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_AbortAllActive]...';
GO
CREATE procedure [mig_director].[JobInstance_AbortAllActive]
as
declare @ids table([Id] int)
update [mig_director].[JobInstance]
set
[ExecutionState] = 4 -- Aborted
output inserted.Id into @ids
where
[ExecutionState] < 2 -- Pending and Running
insert [mig_director].[JobInstanceLog] (
[JobInstanceId]
, [LogType]
, [Message]
)
select
[Id]
, 2
, 'Aborted because Track stopped'
from @ids
GO
PRINT N'Creating Procedure [mig_director].[Job_SetCommit]...';
GO
CREATE PROCEDURE [mig_director].[Job_SetCommit] (
@jobId int
, @restartArea varchar(max)
)
as
update [mig_director].[Job]
set
[RestartArea] = @restartArea
where
[JobId] = @jobId
select
[BatchSize]
from
[mig_director].[Job]
where
[JobId] = @jobId
GO
PRINT N'Creating Procedure [mig_director].[Job_SetBatchSize]...';
GO
CREATE procedure [mig_director].[Job_SetBatchSize]
@jobId int
, @batchSize int
as
update [mig_director].[Job]
set
[BatchSize] = @batchSize
where
[JobId] = @jobId
GO
PRINT N'Creating Procedure [mig_director].[Job_Restart]...';
GO
CREATE PROCEDURE [mig_director].[Job_Restart] (
@jobId int
, @clientID uniqueidentifier
, @user varchar(50)
, @jobTypeId int output
)
as
select
@jobTypeId = [JobTypeId]
from
[mig_director].[Job]
where
[JobId] = @jobId
-- Create with priority from last instance
declare @priority tinyint = (
select top(1)
[Priority]
from
[mig_director].[JobInstance]
where
[JobId] = @jobId
order by
[Id] desc
)
exec [mig_director].[JobInstance_Insert] @jobId, @clientID, @priority, @user
GO
PRINT N'Creating Procedure [mig_director].[Job_List]...';
GO
CREATE procedure [mig_director].[Job_List]
as
declare @now datetime2(7) = sysdatetime();
with
S as (
select
[JobId]
, [TimeStart]
from
[mig_director].[JobInstance] as A
where
[Id] = (
select min([Id])
from [mig_director].[JobInstance] as B
where
A.[JobId] = B.[JobId]
)
)
, E as (
select
[JobId]
, [Id] as [lastInstanceId]
, [ExecutionState]
, [TimeEnd]
from
[mig_director].[JobInstance] as A
where
[Id] = (
select max([Id])
from [mig_director].[JobInstance] as B
where
A.[JobId] = B.[JobId]
)
)
, T as (
select
[JobId]
, sum([mig_director].[CalculateElapsedSeconds]([TimeStart], [TimeEnd], [ExecutionState], @now)) as [ElapsedSeconds]
from
[mig_director].[JobInstance]
group by
[JobId]
)
select
S.[JobId]
, S.[TimeStart]
, E.[TimeEnd]
, E.[LastInstanceId]
, T.[ElapsedSeconds]
into #instanceSummary
from
S
inner join E on S.[JobId] = E.[JobId]
inner join T on S.[JobId] = T.[JobId]
create index __IX_summary on #instanceSummary
(
[JobId] asc
)
select
A.[JobId]
, B.[LastInstanceId]
, A.[JobTypeId]
, A.[Name]
, A.[Header]
, A.[CanCancel] as [JobTypeCanCancel]
, isnull(cast(C.[ExecutionState] as int), -1) as [ExecutionStateNum]
, isnull(C.[Result], 0) as [ResultNum]
, C.[Priority] as [PriorityNum]
, A.[CreationDate] as [TimeSubmit]
, B.[TimeStart]
, case
when C.[ExecutionState] = 3 then null -- Cancelled
else B.[TimeEnd]
end as [TimeEnd]
, B.[ElapsedSeconds]
from
[mig_director].[Job] as A
left outer join #instanceSummary as B
on A.[JobId] = B.[JobId]
left outer join [mig_director].[JobInstance] as C
on C.[Id] = B.[LastInstanceId]
order by
A.[JobId] desc
GO
PRINT N'Creating Procedure [mig_director].[Job_InsertStartStop]...';
GO
CREATE PROCEDURE [mig_director].[Job_InsertStartStop] (
@start bit
, @user varchar(50)
)
as
insert [mig_director].[Job] (
[JobTypeId]
, [Name]
, [ClassName]
, [Header]
, [Parameters]
, [BatchSize]
, [CanCancel]
) values (
0
, case @start when 1 then 'Track Started' else 'Track Stopped' end
, ''
, @user
, ''
, 0
, 0
)
GO
PRINT N'Creating Procedure [mig_director].[Job_Get]...';
GO
CREATE procedure [mig_director].[Job_Get]
@jobId int
as
select
[JobId]
, [Parameters]
, [RestartArea]
, [BatchSize]
, [CanCancel]
from
[mig_director].[Job]
where
[JobId] = @jobId
GO
PRINT N'Creating Procedure [mig_director].[Job_CreateNew]...';
GO
CREATE PROCEDURE [mig_director].[Job_CreateNew] (
@jobTypeId int
, @clientID uniqueidentifier
, @name varchar(50)
, @className varchar(256)
, @canCancel bit
, @header varchar(256) = null
, @parameters xml
, @batchSize int
, @priority tinyint
, @user varchar(50)
, @jobId int output
)
as
insert [mig_director].[Job] (
[JobTypeId]
, [Name]
, [ClassName]
, [CanCancel]
, [Header]
, [Parameters]
, [BatchSize]
) values (
@jobTypeId
, @name
, @className
, @canCancel
, @header
, @parameters
, @batchSize
)
set @jobId = scope_identity()
exec [mig_director].[JobInstance_Insert] @jobId, @clientID, @priority, @user
GO
PRINT N'Creating Procedure [mig_director].[ItemSet_List]...';
GO
create procedure [mig_director].[ItemSet_List]
as
select
[SetId]
, [Type]
, [Source]
, [ItemCount]
, [OrderDate]
, convert(bit,
case
when [ReadyDate] is null then 0
else 1
end
) as [Ready]
from
[mig_director].[ItemSet]
order by
[SetId] desc
GO
PRINT N'Creating Procedure [mig_director].[ItemSet_FromSql]...';
GO
CREATE procedure [mig_director].[ItemSet_FromSql](
@sql varchar(max)
)
as
declare @setId int
exec [mig_director].[_ItemSet_Insert]
'Sql'
, @sql
, @setId output
declare @items table([ItemId] bigint)
insert @items ([ItemId])
execute(@sql)
insert [mig_item].[ItemSet] (
[SetId]
, [ItemId]
)
select
@setId
, [ItemId]
from @items
group by
[ItemId]
exec [mig_director].[_ItemSet_SetReady] @setId, @@rowCount
GO
PRINT N'Creating Procedure [mig_director].[ItemSet_FromEventUser]...';
GO
create procedure [mig_director].[ItemSet_FromEventUser](
@engine tinyint
, @engineText varchar(50)
, @entityID uniqueidentifier
, @eventID uniqueidentifier
, @message varchar(max)
)
as
declare @setId int
declare @type varchar(50) = @engineText + ' event'
exec [mig_director].[_ItemSet_Insert]
@type
, @message
, @setId output
;with items (
[ItemId]
) as (
select
A.[ItemId]
from
[mig_valid].[EventUser] as A
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
and B.[EntityID] = @entityID
where
A.[Engine] = @engine
and A.[EventID] = @eventID
group by
A.[ItemId]
)
insert [mig_item].[ItemSet] (
[SetId]
, [ItemId]
)
select
@setId
, [ItemId]
from items
group by
[ItemId]
exec [mig_director].[_ItemSet_SetReady] @setId, @@rowCount
GO
PRINT N'Creating Procedure [mig_director].[ItemSet_FromEventSystem]...';
GO
create procedure [mig_director].[ItemSet_FromEventSystem](
@engine tinyint
, @engineText varchar(50)
, @entityID uniqueidentifier
, @message varchar(max)
)
as
declare @setId int
declare @type varchar(50) = @engineText + ' event'
exec [mig_director].[_ItemSet_Insert]
@type
, @message
, @setId output
;with items (
[ItemId]
) as (
select
A.[ItemId]
from
[mig_valid].[EventSystem] as A
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
and B.[EntityID] = @entityID
where
A.[Engine] = @engine
and A.[Message] = @message
group by
A.[ItemId]
)
insert [mig_item].[ItemSet] (
[SetId]
, [ItemId]
)
select
@setId
, [ItemId]
from items
group by
[ItemId]
exec [mig_director].[_ItemSet_SetReady] @setId, @@rowCount
GO
PRINT N'Creating Procedure [mig_director].[ItemSet_Delete]...';
GO
create procedure [mig_director].[ItemSet_Delete](
@setId int
)
as
delete from [mig_director].[ItemSet]
where
[SetId] = @setId
delete from [mig_item].[ItemSet]
where
[SetId] = @setId
GO
PRINT N'Creating Procedure [mig_director].[GlobalCounter_GetIntervalStart]...';
GO
create procedure [mig_director].[GlobalCounter_GetIntervalStart] (
@counterId varchar(50)
, @intervalSize int
) as
declare @result table([Updated] bigint)
merge [mig_director].[GlobalCounter] with (holdlock) as tgt
using (select @counterId, @intervalSize) as src ([CounterId], [IntervalSize])
on (tgt.[CounterId] = src.[CounterId])
when matched then
update set [NextIntervalStart] = [NextIntervalStart] + src.[IntervalSize]
when not matched then
insert ([CounterId], [NextIntervalStart])
values (src.[CounterID], src.[IntervalSize])
output deleted.[NextIntervalStart] into @result;
select isnull((select [Updated] from @result), 0)
GO
PRINT N'Creating Procedure [mig_director].[GlobalCounter_List]...';
GO
create procedure [mig_director].[GlobalCounter_List]
as
select
[CounterId]
, [NextIntervalStart] as IntervalEnd
from [mig_director].[GlobalCounter] with (NoLock)
order by [CounterId]
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_StatusList]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_StatusList]
AS
-- Get dependencies to ExportEntities in order to filter out views used by entities not in scope
select
B.[FullName]
, A.[EntityID]
from
[mig_director].[ExportEntitySourceDependency] as A
inner join [mig_director].[ExportSourceView] as B
on
A.[SourceID] = B.[ItemID]
where
B.[IsPresent] = 1
and A.SourceType = 'View'
select
A.[ItemID]
, A.[FullName]
, A.[ViewSchema]
, A.[ViewName]
, 1 as [Ready]
, A.[LoadedDate]
, A.[LoadedElapsedTicks]
, A.[LoadedCount]
, B.[BaselineCount]
, [ForceInScope]
, convert(bit, 0) as [InScope]
, convert(bit, case when A.[AcceptedQueryPlan] is null then 0 else 1 end) as HasQueryPlan
, A.[OptimizeIterations]
from
[mig_director].[ExportSourceView] as A
left outer join [mig_director].[ExportSourceView_Baseline] as B
on
A.[FullName] = B.[FullName]
where
A.[IsPresent] = 1
order by
A.[FullName]
;with
valuesets as (
select
A.*
from
[mig_director].[Valueset] as A
inner join [mig_director].[Engine] as B
on
A.[ProjectID] = B.[ProjectID]
)
, dep as (
select
B.[FullName] as [ViewFullName]
, A.[DependentOnID] as [SourceID]
, coalesce(C.[FullName], D.[FullName], 'Vs.' + E.[Name]) as [DependentOnFullName]
, A.[SourceType]
, case A.[SourceType]
when 'View' then C.[LoadedDate]
when 'Table' then null
else E.[LastUpdateDate]
end as [LoadedDate]
, case A.[SourceType]
when 'View' then C.[LoadedCount]
when 'Table' then null
else E.[RowCount]
end as [LoadedCount]
from
[mig_director].[ExportSourceViewDependency] as A
inner join [mig_director].[ExportSourceView] as B
on
A.[ViewID] = B.[ItemID]
left outer join [mig_director].[ExportSourceView] as C
on
A.[DependentOnID] = C.[ItemID] and A.[SourceType] = 'View'
left outer join [mig_director].[ExportSourceTable] as D
on
A.[DependentOnID] = D.[ItemID] and A.[SourceType] = 'Table'
left outer join valuesets as E
on
A.[DependentOnID] = E.[ItemID] and A.[SourceType] = 'Valueset'
where
A.[IsPresent] = 1
)
select
*
from dep
order by
[DependentOnFullName]
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_SetLoaded]...';
GO
create PROCEDURE [mig_director].[Export_SourceView_SetLoaded]
(
@fullName varchar(100)
, @loadedCount int
, @elapsedTicks bigint
)
AS
update [mig_director].[ExportSourceView]
set
[LoadedCount] = @loadedCount
, [LoadedElapsedTicks] = @elapsedTicks
, [LoadedDate] = sysdatetime()
where
[FullName] = @fullName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_SetBaseline]...';
GO
CREATE procedure [mig_director].[Export_SourceView_SetBaseline]
as
declare @now datetime2(7) = sysdatetime()
delete from [mig_director].[ExportSourceView_Baseline]
from
[mig_director].[ExportSourceView_Baseline] as A
inner join [mig_director].[ExportSourceView] as B
on A.[FullName] = B.[FullName] and B.[LoadedCount] is null
update [mig_director].[ExportSourceView_Baseline]
set
[BaselineCount] = B.[LoadedCount]
, [ModificationDate] = @now
from
[mig_director].[ExportSourceView_Baseline] as A
inner join [mig_director].[ExportSourceView] as B
on A.[FullName] = B.[FullName]
insert [mig_director].[ExportSourceView_Baseline] (
[ViewSchema]
, [ViewName]
, [BaselineCount]
, [CreationDate]
)
select
A.[ViewSchema]
, A.[ViewName]
, A.[LoadedCount]
, @now
from
[mig_director].[ExportSourceView] as A
left outer join [mig_director].[ExportSourceView_Baseline] as B
on A.[FullName] = B.[FullName]
where
B.[FullName] is null and A.[LoadedCount] is not null
GO
PRINT N'Creating Procedure [mig_director].[Partition_List]...';
GO
create procedure [mig_director].[Partition_List]
as
select distinct
A.[PartitionValue]
from
[mig_valid].[Item] as A
inner join [mig_item].[ExportItem] as B
on A.[ItemId] = B.[ItemId] and B.[Result] <> 1 -- Dropped
GO
PRINT N'Creating Procedure [mig_director].[SmartText_InsertOrUpdate]...';
GO
CREATE PROCEDURE [mig_director].[SmartText_InsertOrUpdate]
(
@itemID uniqueidentifier,
@xml xml,
@user varchar(50)
)
AS
update [mig_director].[SmartText]
set
[Xml] = @xml
, [ModificationDate] = sysdatetime()
, [ModifiedBy] = @user
where
[ItemID] = @itemID
if 0 = @@rowcount
begin
insert into [mig_director].[SmartText] (
[ItemID]
, [Xml]
, [CreatedBy]
) values (
@itemID
, @xml
, @user
)
end
GO
PRINT N'Creating Procedure [mig_director].[RunParameter_SetValue]...';
GO
create PROCEDURE [mig_director].[RunParameter_SetValue](
@itemID uniqueidentifier
, @projectID uniqueidentifier
, @value varchar(max)
, @userName varchar(50)
)
AS
update [mig_director].[RunParameter]
set
[Value] = @value
, [ModificationDate] = sysdatetime()
, [ModifiedBy] = @userName
where
[ItemID] = @itemID and [ProjectID] = @projectID
GO
PRINT N'Creating Procedure [mig_director].[RunParameter_List]...';
GO
CREATE PROCEDURE [mig_director].[RunParameter_List](
@projectTypes xml = null
)
as
select
A.[ItemID]
, A.[ProjectID]
, A.[ProjectType]
, A.[Name]
, A.[Value]
, A.[Description]
, A.[DataTypeXml]
from
[mig_director].[RunParameter] as A
left outer join [mig_utility].[ParseInfoList](@projectTypes) as B
on A.[ProjectType] = B.[Value]
where
(@projectTypes is null or B.[Value] is not null) and A.[Present] = 1
order by
A.[Name]
GO
PRINT N'Creating Procedure [mig_director].[RunParameter_InsertOrUpdate]...';
GO
CREATE PROCEDURE [mig_director].[RunParameter_InsertOrUpdate](
@itemID uniqueidentifier
, @projectID uniqueidentifier
, @projectType varchar(50)
, @name varchar(50)
, @value varchar(max)
, @description varchar(max)
, @dataTypeXml xml
, @user varchar(50)
)
AS
update [mig_director].[RunParameter]
set
[ProjectID] = @projectID
, [ProjectType] = @projectType
, [Name] = @name
, [Value] = @value
, [Description] = @description
, [DataTypeXml] = @dataTypeXml
, [Present] = 1
, [ModificationDate] = sysdatetime()
, [ModifiedBy] = @user
where
[ItemId] = @itemId and [ProjectID] = @projectID
if @@rowCount = 0
begin
insert [mig_director].[RunParameter] (
[ItemID]
, [ProjectID]
, [ProjectType]
, [Name]
, [Value]
, [Description]
, [DataTypeXml]
, [Present]
, [CreationDate]
, [CreatedBy]
) values (
@itemID
, @projectID
, @projectType
, @name
, @value
, @description
, @dataTypeXml
, 1
, sysdatetime()
, @user
)
end
GO
PRINT N'Creating Procedure [mig_director].[Reject_List]...';
GO
CREATE procedure [mig_director].[Reject_List]
as
select
A.[ItemId]
, C.[EntityID]
, E.[BusinessEntityName]
, D.[EntityName]
, C.[BreakFields]
from
[mig_valid].[Item] as A with (NoLock)
inner join [mig_item].[MainKey] as B with (NoLock)
on A.[KeyId] = B.[KeyId]
inner join [mig_item].[ExportKey] as C with (NoLock)
on A.[ItemId] = C.[ItemId]
inner join [mig_director].[ExportEntity] as D with (NoLock)
on C.[EntityID] = D.[EntityID]
inner join [mig_director].[BusinessEntity] as E with (NoLock)
on D.[BusinessEntityID] = E.[BusinessEntityID]
left outer join [mig_valid].[ExportItem] as F with (NoLock)
on A.[ItemId] = F.[ItemId]
and F.[Result] = 2 -- 2: Rejected
left outer join [mig_valid].[ImportItem] as G with (NoLock)
on A.[ItemId] = G.[ItemId]
and G.[Result] < 2 -- 2: Success
where
F.[ItemId] is not null or G.[ItemId] is not null
GO
PRINT N'Creating Procedure [mig_director].[Option_Set]...';
GO
CREATE PROCEDURE [mig_director].[Option_Set]
(
@optionName varchar(50),
@optionValue varchar(max) = null
)
AS
if @optionValue is null -- Clear Override
begin
delete from [mig_director].[DirectorOption]
where
[OptionName] = @optionName
end
else
begin
update [mig_director].[DirectorOption]
set
[OptionValue] = @optionValue
, [ModificationDate] = sysdatetime()
where
[OptionName] = @optionName
if 0 = @@rowCount
begin
insert [mig_director].[DirectorOption] (
[OptionValue]
, [OptionName]
) values (
@optionValue
, @optionName
)
end
end
GO
PRINT N'Creating Procedure [mig_director].[Option_List]...';
GO
create PROCEDURE [mig_director].[Option_List]
as
select
[OptionName]
, [OptionValue]
from [mig_director].[DirectorOption]
GO
PRINT N'Creating Procedure [mig_director].[Option_Get]...';
GO
CREATE PROCEDURE [mig_director].[Option_Get] (
@optionName nvarchar(50)
)
AS
select
[OptionValue]
from
[mig_director].[DirectorOption]
where
[OptionName] = @optionName
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_SetStarted]...';
GO
CREATE PROCEDURE [mig_director].[JobInstance_SetStarted] (
@jobInstanceId int
, @processId int
, @processName varchar(256)
, @runtimeVersion varchar(50)
)
as
update [mig_director].[JobInstance]
set
[ExecutionState] = 1
, [TimeStart] = sysdatetime()
, [ProcessId] = @processId
, [ProcessName] = @processName
, [RuntimeVersion] = @runtimeVersion
where
[Id] = @jobInstanceId
select
B.[JobId]
, B.[ClassName]
, B.[Parameters]
, B.[RestartArea]
, B.[BatchSize]
, A.[CreatedBy]
from
[mig_director].[JobInstance] as A
inner join [mig_director].[Job] as B
on A.[JobId] = B.[JobId]
where
A.[Id] = @jobInstanceId
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_SetDebugWait]...';
GO
create PROCEDURE [mig_director].[JobInstance_SetDebugWait] (
@jobInstanceId int
, @waiting bit
)
as
update[mig_director].[JobInstance]
set
[DebugWait] = @waiting
where
[Id] = @jobInstanceId
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_SetCompleted]...';
GO
CREATE PROCEDURE [mig_director].[JobInstance_SetCompleted] (
@jobInstanceId int
, @result tinyint = 1
)
as
update [mig_director].[JobInstance]
set
[ExecutionState] = 2
, [Result] = @result
, [TimeEnd] = sysdatetime()
where
[Id] = @jobInstanceId
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_SetCancelled]...';
GO
CREATE PROCEDURE [mig_director].[JobInstance_SetCancelled] (
@jobInstanceId int
)
as
update [mig_director].[JobInstance]
set
[ExecutionState] = 3
, [TimeEnd] = sysdatetime()
where
[Id] = @jobInstanceId
and [ExecutionState] < 3 -- Only Pending and Running are updated
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_GetState]...';
GO
create procedure [mig_director].[JobInstance_GetState]
@clientID uniqueidentifier
, @executionState int out
, @result int out
as
select
@executionState = [ExecutionState]
, @result = [Result]
from
[mig_director].[JobInstance]
where
[ClientID] = @clientID
GO
PRINT N'Creating Procedure [mig_director].[Job_ListEvent]...';
GO
CREATE procedure [mig_director].[Job_ListEvent]
@jobId int
, @dispositions varchar(max) = null
, @impacts varchar(max) = null
, @entities varchar(max) = null
as
declare @dispositionTable table([Disposition] int, primary key (Disposition))
declare @impactTable table([Impact] int, primary key (Impact))
declare @entitiesTable table([EntityID] uniqueidentifier primary key (EntityID))
if @dispositions is not null
begin
insert into @dispositionTable
select distinct convert(int, [Value]) as [Disposition] from [mig_utility].[ParseDelimitedString](@dispositions, ',')
end
if @impacts is not null
begin
insert into @impactTable
select distinct convert(int, [Value]) as [Impact] from [mig_utility].[ParseDelimitedString](@impacts, ',')
end
if @entities is not null
begin
insert into @entitiesTable
select distinct [Id] from [mig_utility].[ParseGuids](@entities, ',')
end
;with
systemEvents as (
select
B.[EntityID]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[Message]
, count(*) as [Count]
, count(distinct A.[ItemId]) as [Items]
from
[mig_item].[EventSystem] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
A.[JobId] = @jobId
and (@dispositions is null or A.[Disposition] in (select [Disposition] from @dispositionTable))
and (@impacts is null or A.[Impact] in (select [Impact] from @impactTable))
and (@entities is null or B.[EntityID] in (select [EntityID] from @entitiesTable))
group by
B.[EntityID]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[Message]
)
, userEvents as (
select
B.[EntityID]
, A.[EventID]
, A.[EventName]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[EventTextID]
, count(*) as [Count]
, count(distinct A.[ItemId]) as [Items]
from
[mig_item].[EventUser] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
A.[JobId] = @jobId
and (@dispositions is null or A.[Disposition] in (select [Disposition] from @dispositionTable))
and (@impacts is null or A.[Impact] in (select [Impact] from @impactTable))
and (@entities is null or B.[EntityID] in (select [EntityID] from @entitiesTable))
group by
B.[EntityID]
, A.[EventID]
, A.[EventName]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[EventTextID]
)
, texts as (
select
[ItemID] as [TextID]
, substring([DefaultText], 1, 2000) as [Text]
from [mig_director].[SmartText] with (NoLock)
)
, list as (
select
[EntityID]
, null as [EventID]
, [Disposition]
, [Impact]
, [Receiver]
, '(System)' as [EventName]
, [Message]
, [Count]
, [Items]
from systemEvents
union all
select
[EntityID]
, [EventID]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[EventName]
, isnull(B.[Text], 'Text not found (' + lower(convert(varchar(50), A.[EventTextID])) + ')') as [Message]
, A.[Count]
, A.[Items]
from
userEvents as A
left outer join texts as B
on A.[EventTextID] = B.[TextID]
)
select
B.[EntityId]
, B.[EntityName]
, C.[BusinessEntityName]
, A.[EventID]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[EventName]
, A.[Message]
, A.[Count]
, A.[Items]
from
list as A
inner join [mig_director].[ExportEntity] as B
on A.[EntityID] = B.[EntityId]
inner join [mig_director].[BusinessEntity] as C
on B.[BusinessEntityID] = C.[BusinessEntityID]
order by
B.[EntityName]
, A.[EventName]
, A.[Message]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
GO
PRINT N'Creating Procedure [mig_director].[Job_ListEventParameter]...';
GO
CREATE procedure [mig_director].[Job_ListEventParameter]
@jobId int
, @limit int
, @entityID uniqueidentifier
, @message varchar(max)
, @eventID uniqueidentifier = null
, @disposition int
, @impact int
, @receiver int
as
with
itemCount as (
-- EITHER User Events part
select top(@limit)
A.[ParameterHash]
, A.[ItemId]
, 0 as [Type]
, count(*) as [Count]
from
[mig_item].[EventUser] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
where
@eventID is not null
and A.[JobId] = @jobId
and A.[EventID] = @eventID
and B.[EntityID] = @entityID
and A.[Disposition] = @disposition
and A.[Impact] = @impact
and A.[Receiver] = @receiver
group by
A.[ParameterHash]
, A.[ItemId]
union all
-- OR System Events part
select top(@limit)
A.[ParameterHash]
, A.[ItemId]
, 1 as [Type]
, count(*) as [Count]
from
[mig_item].[EventSystem] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
@eventID is null
and A.[JobId] = @jobId
and A.[Message] = @message
and B.[EntityID] = @entityID
and A.[Disposition] = @disposition
and A.[Impact] = @impact
and A.[Receiver] = @receiver
group by
A.[ParameterHash]
, A.[ItemId]
)
, hashCount as (
select
[ParameterHash]
, [Type]
, sum([Count]) as [Count]
, count(*) as [Items]
from
itemCount
group by
[ParameterHash]
, [Type]
)
select
[ParameterXml] = case A.[Type]
when 0 then (
select
top(1) [ParameterXml]
from [mig_item].[EventUser] as B with (NoLock)
where
B.[ParameterHash] = A.[ParameterHash]
and B.[JobId] = @jobId
and B.[EventID] = @eventID
)
else (
select
top(1) [ParameterXml]
from [mig_item].[EventSystem] as B with (NoLock)
where
B.[ParameterHash] = A.[ParameterHash]
and B.[JobId] = @jobId
and B.[Message] = @message
)
end
, A.[Count]
, A.[Items]
from
hashCount as A
GO
PRINT N'Creating Procedure [mig_director].[State_Get]...';
GO
create procedure [mig_director].[State_Get]
@name varchar(50)
as
select
Value
from
[mig_director].[State]
where
Name = @name
GO
PRINT N'Creating Procedure [mig_director].[State_Set]...';
GO
create procedure [mig_director].[State_Set]
@name varchar(50)
, @value varchar(max)
as
update [mig_director].[State]
set
Value = @value
where
Name = @name
if 0 = @@rowCount
begin
insert [mig_director].[State] values (@name, @value)
end
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_SetPriority]...';
GO
create PROCEDURE [mig_director].[JobInstance_SetPriority] (
@jobInstanceId int
, @priority tinyint
)
as
update [mig_director].[JobInstance]
set
[Priority] = @priority
where
[Id] = @jobInstanceId
GO
PRINT N'Creating Procedure [mig_director].[Setup_Initializer_Start]...';
GO
create procedure [mig_director].[Setup_Initializer_Start]
@jobId int
, @jobInstanceId int
as
declare @step tinyint = 1 -- Conversion
update [mig_item].[Active]
set
[JobId] = @jobId
, [JobInstanceId] = @jobInstanceId
where
[ItemId] = 0
and [Step] = @step
if 0 = @@rowCount
begin
insert [mig_item].[Active] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
) values (
0
, @jobId
, @jobInstanceId
, @step
)
end
GO
PRINT N'Creating Procedure [mig_director].[Import_JobCheckRestart]...';
GO
CREATE procedure [mig_director].[Import_JobCheckRestart]
@jobId int
as
select
D.[BusinessEntityName]
, isnull(A.[Level], 0)
, sum(
case
when E.Result = 2 then 1
else 0
end
) as [Converted]
, sum(
case
when E.Result < 2 then 1
else 0
end
) as [Discarded]
from
[mig_item].[Processed] as A
inner join [mig_item].[ExportKey] as B
on A.[ItemId] = B.[ItemId]
inner join [mig_director].[ExportEntity] as C
on B.[EntityID] = C.[EntityID]
inner join [mig_director].[BusinessEntity] as D
on C.[BusinessEntityID] = D.[BusinessEntityID]
inner join [mig_item].[ImportItem] as E with (NoLock)
on A.[ItemId] = E.[ItemId] and @jobId = E.[JobId]
where
A.[JobId] = @jobId
group by
D.[BusinessEntityName]
, isnull(A.[Level], 0)
order by
isnull(A.[Level], 0)
, D.[BusinessEntityName]
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_ListLog]...';
GO
CREATE procedure [mig_director].[JobInstance_ListLog]
@clientID uniqueidentifier
as
select
A.[JobInstanceId]
, A.[Id] as [JobInstanceLogId]
, A.[LogType] as [LogTypeNum]
, A.[Message]
, A.[CreationDate]
from
[mig_director].[JobInstanceLog] as A with (NoLock)
inner join [mig_director].[JobInstance] as B with (NoLock)
on A.[JobInstanceId] = B.[Id]
where
B.[ClientID] = @clientID
order by
A.[CreationDate], A.[Id]
select top(1)
A.[Id] as [JobInstanceId]
, A.[ExecutionState] as [ExecutionStateNum]
, A.[Result] as [ResultNum]
, A.[TimeEnd]
, [mig_director].[CalculateElapsedSeconds](A.[TimeStart], A.[TimeEnd], A.[ExecutionState], sysdatetime()) as [ElapsedSeconds]
, case
when (select top(1) 1 from [mig_profile].[Job] as B where A.[Id] = B.[JobInstanceId]) is null then ''
else 'Profile'
end as [Profile]
from
[mig_director].[JobInstance] as A with (NoLock)
where
[ClientID] = @clientID
select
[JobInstanceId]
, [ItemID]
, [Name]
, [Value]
from
[mig_director].[JobInstanceRunParameter] as A with (NoLock)
inner join [mig_director].[JobInstance] as B with (NoLock)
on A.[JobInstanceId] = B.[Id]
where
B.[ClientID] = @clientID
order by
[Name]
GO
PRINT N'Creating Procedure [mig_director].[Setup_ClearJobs]...';
GO
CREATE PROCEDURE [mig_director].[Setup_ClearJobs]
AS
SET NOCOUNT ON
truncate table [mig_director].[Job]
truncate table [mig_director].[JobInstance]
truncate table [mig_director].[JobInstanceLog]
truncate table [mig_director].[JobInstanceRunParameter]
truncate table [mig_director].[JobData]
truncate table [mig_director].[Command]
GO
PRINT N'Creating Procedure [mig_director].[Setup_ClearOther]...';
GO
create procedure [mig_director].[Setup_ClearOther]
@mode tinyint -- 0: ResetTrack, 1: ResetIterationExInititializer, 2: ResetIterationIncInitializer
, @jobId int
, @includeDc bit
as
-- Store Active row from Initializer
select *
into #initActive
from [mig_item].[Active]
where [ItemId] = 0
-- Always include these
truncate table [mig_director].[GlobalCounter]
truncate table [mig_director].[ItemSet]
truncate table [mig_garbage].[Log]
truncate table [mig_item].[AuditSpecifier]
truncate table [mig_item].[AuditValue]
truncate table [mig_item].[ImportItem]
truncate table [mig_item].[TargetXml]
truncate table [mig_item].[Dependency]
truncate table [mig_item].[EventSystem]
truncate table [mig_item].[EventUser]
truncate table [mig_item].[ExportItem]
truncate table [mig_item].[ExportXml]
truncate table [mig_item].[Garbage]
truncate table [mig_item].[Item]
truncate table [mig_item].[ItemSet]
truncate table [mig_item].[Processed]
truncate table [mig_item].[SavedString]
truncate table [mig_item].[SourceXml]
truncate table [mig_item].[Identity]
truncate table [mig_profile].[Item]
truncate table [mig_profile].[Frame]
truncate table [mig_profile].[Job]
truncate table [mig_profile].[Trace]
truncate table [mig_item].[Active]
if 0 = @mode -- 'Reset Track' - Just clear everything
begin
truncate table [mig_director].[Engine]
truncate table [mig_director].[Concurrency]
truncate table [mig_director].[Valueset]
truncate table [mig_director].[ValuesetBaseline]
truncate table [mig_director].[ValuesetProvider]
truncate table [mig_director].[DirectorOption]
truncate table [mig_director].[BusinessEntity]
truncate table [mig_director].[ExportEntity]
truncate table [mig_director].[ExportEntitySourceDependency]
truncate table [mig_director].[ExportSourceTable]
truncate table [mig_director].[ExportSourceTable_Baseline]
truncate table [mig_director].[ExportSourceView]
truncate table [mig_director].[ExportSourceView_Baseline]
truncate table [mig_director].[ExportSourceViewDependency]
truncate table [mig_director].[RunParameter]
truncate table [mig_director].[SmartText]
truncate table [mig_item].[MainKey]
truncate table [mig_item].[RelationshipKey]
truncate table [mig_item].[RelationshipValue]
truncate table [mig_item].[RelationshipBagPropertyValue]
truncate table [mig_item].[ExportKey]
truncate table [mig_item].[Id]
truncate table [mig_tracking].[Baseline]
truncate table [mig_tracking].[EventParameter]
truncate table [mig_tracking].[Log]
truncate table [mig_tracking].[Parameter]
truncate table [mig_tracking].[PartitionEventCount]
truncate table [mig_tracking].[RootEntityEvent]
truncate table [mig_tracking].[RootItem]
truncate table [mig_tracking].[RootItemEvent]
truncate table [mig_tracking].[RootItemLink]
truncate table [mig_tracking].[Statistic]
truncate table [mig_translation].[TranslationValueset]
truncate table [mig_translation].[TranslationValuesetRow]
end
else -- 'New Data' or 'Reset Iteration': More care must be taken
begin
if 1 = @includeDc
begin
truncate table [mig_director].[Valueset]
end
if 1 = @mode -- Reset iteration. The Initializer is not being run
begin
insert [mig_item].[Active]
select * from #initActive
-- Too expensive to start deleting relationship items with ItemId > 0.
-- Leave this to the GarbageCollector (because Active and Id tables are truncated,
-- whatever remains in the 2 Relationship* tables above ItemId = 0 is garbage)
end
else
begin
if 2 = @mode -- Reset iteration. The initializer being run
begin
truncate table [mig_item].[RelationshipKey]
truncate table [mig_item].[RelationshipValue]
truncate table [mig_item].[RelationshipBagPropertyValue]
end
end
end
GO
PRINT N'Creating Procedure [mig_director].[Export_Entity_SetIgnored]...';
GO
CREATE PROCEDURE [mig_director].[Export_Entity_SetIgnored]
(
@entityID uniqueidentifier
, @ignored bit
)
AS
update [mig_director].[ExportEntity]
set
[Ignored] = @ignored
where
[EntityId] = @entityID
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_LogEntryDelete]...';
GO
create PROCEDURE [mig_director].[JobInstance_LogEntryDelete] (
@entryId bigint
)
as
delete from [mig_director].[JobInstanceLog] where [Id] = @entryId
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_LogEntryUpdate]...';
GO
CREATE PROCEDURE [mig_director].[JobInstance_LogEntryUpdate] (
@entryId bigint
, @logType tinyint
, @message varchar(max) = null
, @moveToTail bit = 0
)
as
update [mig_director].[JobInstanceLog]
set
[Message] = isnull(@message, convert(varchar(8), getdate() - cast([CreationDate] as datetime), 108) + ' ' + [Message])
, [LogType] = @logType
, [CreationDate] = case @moveToTail when 0 then [CreationDate] else sysdatetime() end
where
[Id] = @entryId
and ([LogType] <> @logType or [Message] <> @message or 1 = @moveToTail)
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_RunParameterInsert]...';
GO
create PROCEDURE [mig_director].[JobInstance_RunParameterInsert] (
@jobInstanceId int
, @itemID uniqueidentifier
, @name varchar(50)
, @value varchar(max)
)
as
insert [mig_director].[JobInstanceRunParameter] (
[JobInstanceId]
, [ItemID]
, [Name]
, [Value]
, [CreationDate]
) values (
@jobInstanceId
, @itemID
, @name
, @value
, sysdatetime()
)
GO
PRINT N'Creating Procedure [mig_director].[Job_DataGet]...';
GO
create procedure [mig_director].[Job_DataGet]
@jobId int
, @type varchar(50)
as
select
[Content]
from [mig_director].[JobData]
where
[JobId] = @jobId
and [Type] = @type
GO
PRINT N'Creating Procedure [mig_director].[Job_DataClear]...';
GO
create procedure [mig_director].[Job_DataClear]
@jobId int
, @type varchar(50) = null
as
delete from [mig_director].[JobData]
where
[JobId] = @jobId
and (@type is null or [Type] = @type)
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_ListInvalidQuery]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_ListInvalidQuery]
AS
select
[ItemID]
from [mig_director].[ExportSourceView]
where
[OptimizeDate] is null and [IsPresent] = 1
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_GetQueryPlan]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_GetQueryPlan]
@itemID uniqueidentifier
AS
select
[AcceptedQueryPlan]
from
[mig_director].[ExportSourceView]
where
[ItemID] = @itemID
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_InvalidateQueryPlan]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_InvalidateQueryPlan]
@itemID uniqueidentifier = null
AS
update [mig_director].[ExportSourceView]
set
[AcceptedQueryPlan] = null
, [OptimizeIterations] = null
, [OptimizeDate] = null
where
@itemID is null or [ItemID] = @itemID
GO
PRINT N'Creating Procedure [mig_director].[Import_EarliestProcessingTime]...';
GO
CREATE PROCEDURE [mig_director].[Import_EarliestProcessingTime]
@entityIDs varchar(max)
, @minDate datetime2(7)
AS
with
entityIDs as (
select
convert(uniqueidentifier, Value) as [EntityID]
from
mig_utility.ParseDelimitedString(@entityIDs, ';')
)
, dates as (
select
isnull(G.[CreationDate], @minDate) as [ExecutionTime]
from
entityIDs as A
inner join [mig_item].[ExportKey] as B on A.[EntityID] = B.[EntityID]
inner join [mig_item].[ExportItem] as C on B.[ItemId] = C.[ItemId] and C.[Result] = 0
inner join [mig_item].[Active] as D on D.[ItemId] = C.[ItemId] and D.[JobId] = C.[JobId] and D.[JobInstanceId] = C.[JobInstanceId] and D.[Step] = 0
left outer join [mig_item].[Active] as E on E.[ItemId] = D.[ItemId] and E.[Step] = 1
left outer join [mig_item].[ImportItem] as F on F.[ItemId] = E.[ItemId] and F.[JobId] = E.[JobId] and F.[JobInstanceId] = E.[JobInstanceId]
left outer join [mig_director].[JobInstance] as G on G.[Id] = F.[JobInstanceId]
)
select min([ExecutionTime]) from dates
GO
PRINT N'Creating Procedure [mig_director].[Export_EarliestProcessingTime]...';
GO
CREATE PROCEDURE [mig_director].[Export_EarliestProcessingTime]
@entityIDs varchar(max)
, @viewIDs varchar(max)
, @minDate datetime2(7)
AS
declare @ids table([EntityID] uniqueidentifier)
declare @minExecTime datetime2(7)
declare @minSyncTime datetime2(7)
declare @minViewTime datetime2(7)
insert @ids
select
convert(uniqueidentifier, Value)
from
mig_utility.ParseDelimitedString(@entityIDs, ';');
with execDates as (
select
isnull(E.[CreationDate], @minDate) as [ExecutionTime]
from
@ids as A
inner join [mig_item].[ExportKey] as B on A.[EntityID] = B.[EntityID]
inner join [mig_item].[ExportItem] as C on B.[ItemId] = C.[ItemId] and C.[Result] = 0
inner join [mig_item].[Active] as D on D.[ItemId] = C.[ItemId] and D.[JobId] = C.[JobId] and D.[JobInstanceId] = C.[JobInstanceId] and D.[Step] = 0
left outer join [mig_director].[JobInstance] as E on E.[Id] = D.[JobInstanceId]
)
select @minExecTime = min([ExecutionTime]) from execDates;
with syncDates as (
select
isnull(B.[LastSynchronizeTime], @minDate) as [SynchronizeTime]
from
@ids as A
inner join [mig_director].[ExportEntity] as B on A.[EntityID] = B.[EntityID]
)
select @minSyncTime = min([SynchronizeTime]) from syncDates;
with
viewsInSccope as (
select
convert(uniqueidentifier, Value) as [ViewID]
from
mig_utility.ParseDelimitedString(@viewIDs, ';')
)
, viewDates as (
select
isnull(A.[LoadedDate], @minDate) as [LoadTime]
from
[mig_director].[ExportSourceView] as A
inner join viewsInSccope as B on A.[ItemID] = B.[ViewID]
)
select @minViewTime = min([LoadTime]) from viewDates
select min(t) from (values (@minExecTime), (@minSyncTime), (@minViewTime)) as times(t)
GO
PRINT N'Creating Procedure [mig_director].[ValuesetProvider_Initialize]...';
GO
create procedure [mig_director].[ValuesetProvider_Initialize]
@projectType varchar(50)
as
update [mig_director].[ValuesetProvider]
set
[Present] = 0
where
[ProjectType] = @projectType
GO
PRINT N'Creating Procedure [mig_director].[Valueset_Initialize]...';
GO
CREATE PROCEDURE [mig_director].[Valueset_Initialize]
@projectType varchar(50)
AS
update [mig_director].[Valueset]
set
[Present] = 0
where
[ProjectType] = @projectType
GO
PRINT N'Creating Procedure [mig_director].[RunParameter_Initialize]...';
GO
CREATE PROCEDURE [mig_director].[RunParameter_Initialize]
@projectType varchar(50)
as
update [mig_director].[RunParameter]
set
[Present] = 0
where [ProjectType] = @projectType
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_GetTestableQuery]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_GetTestableQuery]
@itemID uniqueidentifier
, @force bit = 0
AS
select
[TestableQuery]
from [mig_director].[ExportSourceView]
where
[ItemID] = @itemID
and (@force = 1 or [OptimizeDate] is null)
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_SaveQueryPlan]...';
GO
CREATE PROCEDURE [mig_director].[Export_SourceView_SaveQueryPlan]
@itemID uniqueidentifier
, @queryPlan varchar(max)
, @optimizeIterations int
AS
update [mig_director].[ExportSourceView]
set
[AcceptedQueryPlan] = @queryPlan
, [OptimizeIterations] = @optimizeIterations
, [OptimizeDate] = sysdatetime()
where
[ItemID] = @itemID
GO
PRINT N'Creating Procedure [mig_director].[DataServicesHash_Set]...';
GO
create procedure [mig_director].[DataServicesHash_Set]
@projectId uniqueidentifier
, @projectType varchar(50)
, @hashCode char(32)
as
update [mig_director].[Engine]
set
[ProjectType] = @projectType
, [DataServicesHash] = @hashCode
where
[ProjectID] = @projectId
GO
PRINT N'Creating Procedure [mig_director].[DataServicesHash_Get]...';
GO
create procedure [mig_director].[DataServicesHash_Get]
@projectId uniqueidentifier
as
select
[DataServicesHash]
from
[mig_director].[Engine]
where
[ProjectID] = @projectId
GO
PRINT N'Creating Procedure [mig_director].[Valueset_DefinitionList]...';
GO
CREATE PROCEDURE [mig_director].[Valueset_DefinitionList]
@projectID uniqueidentifier
as
select
[ItemID]
, [Name]
, [CreateTableSql]
, [ValuesetType]
from
[mig_director].[Valueset]
where
[ProjectID] = @projectID
and [Present] = 1
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_Sequence]...';
GO
CREATE procedure [mig_director].[Export_SourceView_Sequence]
@jobId int
, @viewIdList varchar(max)
, @includeDescendants bit
as
declare @itemIDs table([ItemID] uniqueidentifier)
insert @itemIDs
select
convert(uniqueidentifier, [Value]) as [ItemID]
from
[mig_utility].[ParseDelimitedString](@viewIdList, ';')
create table #dependencies (
[ItemID] uniqueidentifier
, [DependentOnID] uniqueidentifier null
)
-- Extract dependencies within selected views
insert #dependencies
select
A.[ItemID]
, B.[DependentOnID]
from
@itemIDs as A
inner join [mig_director].[ExportSourceViewDependency] as B
on A.[ItemID] = B.[ViewID] and B.[SourceType] = 'View'
inner join @itemIDs as C
on B.[DependentOnID] = C.[ItemID]
where
B.[IsPresent] = 1
-- Add views not dependent on any within the selected views
insert #dependencies
select
A.[ItemID]
, null
from
@itemIDs as A
left outer join #dependencies as B
on A.[ItemID] = B.[ItemID]
where
B.[ItemID] is null
create index __IX_Dependencies_1 on #dependencies (
[DependentOnID] asc
, [ItemId] asc
)
create table #levels (
[ItemID] uniqueidentifier
, [Level] int
)
if 1 = @includeDescendants
begin
-- Start recursive CTE
;with
cteValidDependencies as (
select
A.[ViewID]
, A.[DependentOnID]
from
[mig_director].[ExportSourceViewDependency] as A
inner join [mig_director].[ExportSourceView] as B
on A.[ViewID] = B.[ItemID]
where
A.[IsPresent] = 1
and B.[IsPresent] = 1
)
, cteDependency as (
-- Anchor is root views, ie views not dependent on any other within the specified views
select
A.[ItemID]
, A.[DependentOnID]
, 0 as [Level]
from
#dependencies as A
left outer join #dependencies as B
on A.[DependentOnID] = B.[ItemID]
where
B.[ItemID] is null
union all
-- Recursion
select
A.[ViewID]
, A.[DependentOnID]
, B.[Level] + 1
from
cteValidDependencies as A -- Get All descendant
inner join cteDependency as B
on A.[DependentOnID] = B.[ItemID]
)
insert #levels
select
[ItemID]
, [Level]
from
cteDependency
group by
[ItemID]
, [Level]
option (maxrecursion 50)
-- End recursive CTE
end
else
begin
-- Start recursive CTE
;with cteDependency (
[ItemID]
, [DependentOnID]
, [Level]
) as (
-- Anchor is root views, ie views not dependent on any other within the specified views
select
A.[ItemID]
, A.[DependentOnID]
, 0
from
#dependencies as A
left outer join #dependencies as B
on A.[DependentOnID] = B.[ItemID]
where
B.[ItemID] is null
union all
-- Recursion
select
A.[ItemID]
, A.[DependentOnID]
, B.[Level] + 1
from
#dependencies as A -- Only descendants within specified views
inner join cteDependency as B
on A.[DependentOnID] = B.[ItemID]
)
insert #levels
select
[ItemID]
, [Level]
from
cteDependency
group by
[ItemID]
, [Level]
option (maxrecursion 50)
-- End recursive CTE
end
declare @result table([ItemID] uniqueidentifier, [Level] int)
insert @result
select
[ItemID]
, max([Level]) as [Level]
from
#levels
group by
[ItemID]
if not exists (select 1 from [mig_director].[JobData] where [JobId] = @jobId and [Type] = 'LoadViews')
begin
declare @xml varchar(max) = (
select
A.[Level]
, B.[FullName] as [ViewName]
from
@result as A
inner join [mig_director].[ExportSourceView] as B
on A.[ItemID] = B.[ItemID]
order by
A.[Level]
, B.[FullName]
for xml path, root ('Views')
)
insert [mig_director].[JobData] values (@jobId, 'LoadViews', @xml, sysdatetime())
end
select * from @result order by [Level]
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceView_SetForceInScope]...';
GO
create PROCEDURE [mig_director].[Export_SourceView_SetForceInScope]
(
@fullName varchar(100)
, @forceInScope bit
)
AS
update [mig_director].[ExportSourceView]
set
[ForceInScope] = @forceInScope
where
[FullName] = @fullName
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_SetForceInScope]...';
GO
create PROCEDURE [mig_director].[Export_SourceTable_SetForceInScope]
(
@fullName varchar(100)
, @forceInScope bit
)
AS
update [mig_director].[ExportSourceTable]
set
[ForceInScope] = @forceInScope
where
[FullName] = @fullName
GO
PRINT N'Creating Procedure [mig_director].[Engine_Set]...';
GO
create procedure [mig_director].[Engine_Set]
@projectId uniqueidentifier
, @projectType varchar(50)
, @commit bigint
, @cleanWorkspace bit
as
delete [mig_director].[Engine]
where
[ProjectType] = @projectType
and [ProjectID] <> @projectId
update [mig_director].[Engine]
set
[Commit] = @commit
, [CleanWorkspace] = @cleanWorkspace
where
[ProjectID] = @projectId
if 0 = @@rowCount
begin
insert [mig_director].[Engine] (
[ProjectID]
, [ProjectType]
, [DataServicesHash]
, [Commit]
, [CleanWorkspace]
) values (
@projectId
, @projectType
, ''
, @commit
, @cleanWorkspace
)
end
GO
PRINT N'Creating Procedure [mig_director].[Export_SourceTable_SetLoadedExtension]...';
GO
CREATE procedure [mig_director].[Export_SourceTable_SetLoadedExtension]
@tableSchema varchar(50)
, @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].[ExportFormattedFile]
where
[FileName] = @fileName
insert into [mig_director].[ExportFormattedFile] (
[FileName]
, [FullName]
, [FileDate]
, [DateOfLoadedFile]
, [LoadedDate]
, [LoadedCount]
) values (
@fileName
, @fileName
, @date2
, @date2
, @date2
, @loadedCount
);
delete [mig_director].[ExportSourceFile]
where
[FileName] = @fileName
insert into [mig_director].[ExportSourceFile] (
[FileName]
, [FullName]
, [FileDate]
, [SourceTableFullName]
) values (
@fileName
, @fileName
, @date1
, @tableSchema + '.' + @tableName
);
GO
PRINT N'Creating Procedure [mig_director].[Export_Valueset_StatusList]...';
GO
CREATE PROCEDURE [mig_director].[Export_Valueset_StatusList]
AS
set NoCount off -- Used to return the number of affected records
select
A.[ItemID]
, cast(
case
when A.[LastUpdateDate] is null then 0
else 1
end as bit) as [Loaded]
from
[mig_director].[Valueset] as A
inner join [mig_director].[Engine] as B
on
A.[ProjectID] = B.[ProjectID]
where
A.[ProjectType] = 'Source'
and A.[Present] = 1
GO
PRINT N'Creating Procedure [mig_director].[ExtensionUsage_Add]...';
GO
CREATE procedure [mig_director].[ExtensionUsage_Add]
@name varchar(50)
, @extensionClassName varchar(max)
, @extensionDisplayName varchar(max)
, @extensionType tinyint
, @parameterValues xml
as
-- The first usage for the ExtensionType is set to default
declare @isDefault bit = 1
select top(1)
@isDefault = 0
from
[mig_director].[ExtensionUsage]
where
[ExtensionType] = @extensionType
insert [mig_director].[ExtensionUsage] (
[ID]
, [Name]
, [IsDefault]
, [ExtensionType]
, [ExtensionClassName]
, [ExtensionDisplayName]
, [ParameterValues]
) values (
newid()
, @name
, @isDefault
, @extensionType
, @extensionClassName
, @extensionDisplayName
, @parameterValues
)
GO
PRINT N'Creating Procedure [mig_director].[ExtensionUsage_GetByDefault]...';
GO
CREATE procedure [mig_director].[ExtensionUsage_GetByDefault]
@extensionType tinyint
, @extensionClassName varchar(max) output
, @extensionDisplayName varchar(max) output
, @parameterValues xml output
as
select
@extensionClassName = [ExtensionClassName]
, @extensionDisplayName = [ExtensionDisplayname]
, @parameterValues = [ParameterValues]
from
[mig_director].[ExtensionUsage]
where
[ExtensionType] = @extensionType
and [IsDefault] = 1
GO
PRINT N'Creating Procedure [mig_director].[ExtensionUsage_GetByID]...';
GO
CREATE procedure [mig_director].[ExtensionUsage_GetByID]
@usageID uniqueidentifier
, @extensionClassName varchar(max) output
, @extensionDisplayName varchar(max) output
, @parameterValues xml output
as
select
@extensionClassName = [ExtensionClassName]
, @extensionDisplayName = [ExtensionDisplayname]
, @parameterValues = [ParameterValues]
from
[mig_director].[ExtensionUsage]
where
[ID] = @usageID
GO
PRINT N'Creating Procedure [mig_director].[ExtensionUsage_SetDefault]...';
GO
CREATE procedure [mig_director].[ExtensionUsage_SetDefault]
@usageID uniqueidentifier
as
declare @extensionType tinyint = (select [ExtensionType] from [mig_director].[ExtensionUsage] where [ID] = @usageID)
update [mig_director].[ExtensionUsage]
set
[IsDefault] = case
when [ID] = @usageID then 1
else 0
end
where
[ExtensionType] = @extensionType
GO
PRINT N'Creating Procedure [mig_director].[ExtensionUsage_Delete]...';
GO
CREATE procedure [mig_director].[ExtensionUsage_Delete]
@usageID uniqueidentifier
as
declare @deleted table([ExtensionType] tinyint, [IsDefault] bit)
delete [mig_director].[ExtensionUsage]
output deleted.[ExtensionType], deleted.[IsDefault] into @deleted
where
[ID] = @usageID
-- If this was the default usage for the ExtensionType, then set another as Default
update top(1) [mig_director].[ExtensionUsage]
set
[IsDefault] = 1
from
[mig_director].[ExtensionUsage] as A
inner join @deleted as B
on
A.[ExtensionType] = B.[ExtensionType]
where
B.[IsDefault] = 1
GO
PRINT N'Creating Procedure [mig_director].[ExtensionUsage_Modify]...';
GO
CREATE procedure [mig_director].[ExtensionUsage_Modify]
@usageID uniqueidentifier
, @name varchar(50)
, @extensionClassName varchar(max)
, @extensionDisplayName varchar(max)
, @parameterValues xml
as
update [mig_director].[ExtensionUsage]
set
[Name] = @name
, [ExtensionClassName] = @extensionClassName
, [ExtensionDisplayName] = @extensionDisplayName
, [ParameterValues] = @parameterValues
where
[ID] = @usageID
GO
PRINT N'Creating Procedure [mig_director].[ExtensionUsage_List]...';
GO
create procedure [mig_director].[ExtensionUsage_List]
@extensionType tinyint = null
as
select
[ID]
, [Name]
, [IsDefault]
, [ExtensionType]
, [ExtensionClassName]
, [ExtensionDisplayName]
, [ParameterValues]
from
[mig_director].[ExtensionUsage]
where
@extensionType is null
or [ExtensionType] = @extensionType
order by
[ExtensionType]
, [Name]
GO
PRINT N'Creating Procedure [mig_director].[GetSchemaLevel]...';
GO
create procedure [mig_director].[GetSchemaLevel]
as
select 1;
GO
PRINT N'Creating Procedure [mig_garbage].[_Log_UpdateCount]...';
GO
CREATE procedure [mig_garbage].[_Log_UpdateCount]
@logTime dateTime2(7)
, @tableName varchar(50)
, @deletedCount int
, @statement varchar(max)
as
declare @now datetime2(7) = sysdatetime()
update [mig_garbage].[Log]
set
[Deleted] = [Deleted] + @deletedCount
, [LastDeleteTime] = @now
where
[LogTime] = @logTime
and [TableName] = @tableName
if 0 = @@rowcount
begin
insert [mig_garbage].[Log] (
[LogTime]
, [TableName]
, [Statement]
, [StartTime]
, [LastDeleteTime]
, [Deleted]
) values (
@logTime
, @tableName
, @statement
, @now
, @now
, @deletedCount
)
end
GO
PRINT N'Creating Procedure [mig_garbage].[Log_ListDetail]...';
GO
CREATE procedure [mig_garbage].[Log_ListDetail]
@logTime datetime2(7)
as
select
[LogTime]
, [TableName]
, 'sql' as [StatementMark]
, [Statement] as [StatementText]
, [Deleted]
, [StartTime]
, [EndTime]
, case
when [ErrorMessage] is null then ''
else 'err'
end as [ErrorMark]
, [ErrorMessage]
from
[mig_garbage].[Log]
where
[LogTime] = @logTime
order by
[TableName]
GO
PRINT N'Creating Procedure [mig_garbage].[Log_Cleanup]...';
GO
CREATE procedure [mig_garbage].[Log_Cleanup]
@logsToKeep int
as
declare @logs table([LogTime] datetime2(7))
-- Get all candidates (logs with out any errors)
insert @logs
select distinct
A.[LogTime]
from [mig_garbage].[Log] as A
where
not exists (select 1 from [mig_garbage].[Log] as B where B.[LogTime] = A.[LogTime] and B.[ErrorMessage] is not null)
if @@rowCount > @logsToKeep
begin
-- Get the LogTime of oldest log to keep
declare @oldest datetime2(2)
select @oldest = min([LogTime])
from (
select top(@logsToKeep)
[LogTime]
, 'c' as [Constant]
from @logs
order by
[LogTime] desc
) as T
-- Delete logs to keep from candidates
delete from @logs
where
[LogTime] >= @oldest
-- Finally delete all the remaining candidates from Log table
delete from [mig_garbage].[Log]
from
[mig_garbage].[Log] as A
inner join @logs as B on B.[LogTime] = A.[LogTime]
end
GO
PRINT N'Creating Procedure [mig_garbage].[Log_Finish]...';
GO
CREATE procedure [mig_garbage].[Log_Finish]
@logTime datetime2
, @tableName varchar(50)
, @errorMessage varchar(max) = null
as
declare @now datetime2(7) = sysdatetime()
update [mig_garbage].[Log]
set
[EndTime] = sysdatetime()
, [ErrorMessage] = @errorMessage
where
[LogTime] = @logTime
and [TableName] = @tableName
if 0 = @@rowcount and @errorMessage is not null
begin
insert [mig_garbage].[Log] (
[LogTime]
, [TableName]
, [Statement]
, [ErrorMessage]
, [StartTime]
, [LastDeleteTime]
, [Deleted]
) values (
@logTime
, @tableName
, 'missing'
, @errorMessage
, @now
, @now
, 0
)
end
GO
PRINT N'Creating Procedure [mig_garbage].[Log_List]...';
GO
CREATE procedure [mig_garbage].[Log_List]
as
;with
Lst ([LogTime] , [EndTime]) as (
select
A.[LogTime]
, A.[EndTime]
from [mig_garbage].[Log] as A
where
not exists (select 1 from [mig_garbage].[Log] as B where B.[LogTime] = A.[LogTime] and B.[Endtime] is null)
and A.[EndTime] = (
select top(1)
C.[EndTime]
from [mig_garbage].[Log] as C
where C.[LogTime] = A.[LogTime]
order by C.[EndTime] desc
)
)
, Err ([LogTime]) as (
select top(1)
A.[LogTime]
from [mig_garbage].[Log] as A
where
exists (select 1 from [mig_garbage].[Log] as B where B.[LogTime] = A.[LogTime] and B.[ErrorMessage] is not null)
)
, Hdr ([LogTime], [Deleted]) as (
select
[LogTime]
, sum([Deleted])
from
[mig_garbage].[Log]
group by
[LogTime]
)
select distinct
Hdr.[LogTime]
, Lst.[EndTime]
, case
when Err.[LogTime] is null then 0
else 1
end as HasError
, Hdr.[Deleted]
from
Hdr
left outer join Lst on Hdr.[LogTime] = Lst.[LogTime]
left outer join Err on Hdr.[LogTime] = Err.[LogTime]
where
Err.[LogTime] is not null
or Hdr.[Deleted] > 0
order by
Hdr.[LogTime] desc
GO
PRINT N'Creating Procedure [mig_garbage].[Rows_Delete]...';
GO
CREATE procedure [mig_garbage].[Rows_Delete]
@logTime dateTime2(7)
, @tableName varchar(50)
, @newest datetime2(7)
, @batchSize int
, @reportInterval int
as
-- Garbage collector must always be chosen as the deadlock victim
-- Transaction and Retry logic on deadlocks reside in the c# GarbageCollector
set deadlock_priority low
declare @selectSql nvarchar(max) = [mig_garbage].[BuildStatement](
'select top(' + convert(varchar(10), @batchSize) + ') 1'
, @tableName
)
declare @deleteSql nvarchar(max) = 'delete from [mig_item].[' + @tableName + '] where current of @cursor'
declare @statement varchar(max) = 'declare @newest datetime2(7) = ''' + convert(varchar(max), @newest) + '''' + char(13) + @selectSql
-- The print will send the statement to the Sql output (collected in JobInstanceLog in case of an error)
print @statement
declare @csrSql nvarchar(max) =
'set @cursor = cursor local for ' + @selectSql + char(13)
+ 'open @cursor' -- Important to open in the dynamic sql scope. It is at this point the @cursor vairable is assigned
declare @csr cursor
exec sp_executeSql @csrSql, N'@newest datetime2(7), @cursor cursor output', @newest = @newest, @cursor = @csr output
-- Loop thru cursor
declare @dummy tinyint
declare @batch int = 0
declare @total int = 0
fetch next from @csr into @dummy
while @@fetch_status = 0
begin
exec sp_executeSql @deleteSql, N'@cursor cursor', @cursor = @csr -- delete row
set @batch = @batch + 1
if @batch >= @reportInterval
begin
set @total = @total + @batch
exec [mig_garbage].[_Log_UpdateCount] @logTime, @tableName, @batch, @statement
set @batch = 0
end
fetch next from @csr into @dummy
end
close @csr
deallocate @csr
if 0 < @batch
begin
exec [mig_garbage].[_Log_UpdateCount] @logTime, @tableName, @batch, @statement
end
select @total
GO
PRINT N'Creating Procedure [mig_garbage].[Tables_List]...';
GO
CREATE procedure [mig_garbage].[Tables_List]
as
select
A.name
from
sys.tables as A
where
schema_name(A.schema_id) = 'mig_item'
and A.name not in ('ExportKey', 'FileKey') -- Are handled by synchronize
and exists (
select 1
from
sys.columns as B
where
A.object_id = B.object_id
and B.name = 'JobId'
)
union select 'Id'
GO
PRINT N'Creating Procedure [mig_garbage].[Log_Report]...';
GO
create procedure [mig_garbage].[Log_Report]
as
declare @counts table ([Deleted] int)
begin transaction
update [mig_garbage].[Log]
set
[Reported] = [Deleted]
, [LastReportTime] = sysdatetime()
output Deleted.[Deleted] - Deleted.[Reported] into @counts
where
([LastReportTime] is null or [LastReportTime] < [LastDeleteTime])
commit transaction
select isnull(sum([Deleted]), 0) from @counts
GO
PRINT N'Creating Procedure [mig_garbage].[_Log_InvalidateItems]...';
GO
CREATE procedure [mig_garbage].[_Log_InvalidateItems]
@wasInvalidated [mig_garbage].[InvalidateSet] readonly
as
declare @now datetime2(7) = sysdatetime()
-- Log the invalidated items
-- (update and insert for the (improbable) occasion
-- that and item is invalidated more that 1 time before
-- being garbage collected)
update [mig_item].[Garbage]
set
[InvalidatedTime] = @now
from
[mig_item].[Garbage] as A
inner join @wasInvalidated as B
on B.[ItemId] = A.[ItemId]
and B.[InvalidJobId] = A.[JobId]
and B.[InvalidJobInstanceId] = A.[JobInstanceId]
and B.[Step] = A.[Step]
insert [mig_item].[Garbage] with (RowLock)
select
A.[ItemId]
, A.[InvalidJobId]
, A.[InvalidJobInstanceId]
, A.[Step]
, @now
, @now
from
@wasInvalidated as A
left outer join [mig_item].[Garbage] as B
on A.[ItemId] = B.[ItemId]
and A.[InvalidJobId] = B.[JobId]
and A.[InvalidJobInstanceId] = B.[JobInstanceId]
and A.[Step] = B.[Step]
where
B.[ItemId] is null
GO
PRINT N'Creating Procedure [mig_item].[Audit_ValueSetSuccess]...';
GO
CREATE PROCEDURE [mig_item].[Audit_ValueSetSuccess]
(
@auditValueID uniqueidentifier
, @success bit
)
as
update [mig_valid].[AuditValue]
set
Success = @success
, InheritedSuccess = case when ParentAuditValueID is null then @success else InheritedSuccess end
where AuditValueID = @auditValueID
GO
PRINT N'Creating Procedure [mig_item].[Audit_InsertValue]...';
GO
create PROCEDURE [mig_item].[Audit_InsertValue]
(
@itemId bigint
, @jobId int
, @jobInstanceId int
, @step tinyint
, @parentAuditValueID uniqueidentifier = null
, @amountDebit decimal(15,2) = null
, @amountCredit decimal(15,2) = null
, @count int
, @success bit
, @inheritedSuccess bit
)
as
insert into AuditValue (
[Itemid]
, [JobId]
, [JobInstanceId]
, [Step]
, [ParentAuditValueID]
, [AmountDebit]
, [AmountCredit]
, [Count]
, [Success]
, [InheritedSuccess]
)
output inserted.AuditValueID
values (
@itemId
, @jobId
, @jobInstanceId
, @step
, @parentAuditValueID
, @amountDebit
, @amountCredit
, @count
, @success
, @inheritedSuccess
)
GO
PRINT N'Creating Procedure [mig_item].[Audit_InsertSpecifier]...';
GO
create PROCEDURE [mig_item].[Audit_InsertSpecifier]
(
@itemId bigint
, @jobId int
, @jobInstanceId int
, @step tinyint
, @auditValueID uniqueidentifier
, @auditHeaderClassID uniqueidentifier
, @auditGroupClassID uniqueidentifier
, @auditSpecifierClassID uniqueidentifier
, @auditSpecifierValue nvarchar(256)
)
as
insert into [mig_item].[AuditSpecifier] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
, [AuditValueID]
, [AuditHeaderClassID]
, [AuditGroupClassID]
, [AuditSpecifierClassID]
, [AuditSpecifierValue]
)
output inserted.AuditSpecifierID
values (
@itemId
, @jobId
, @jobinstanceId
, @step
, @auditValueID
, @auditHeaderClassID
, @auditGroupClassID
, @auditSpecifierClassID
, @auditSpecifierValue
)
GO
PRINT N'Creating Procedure [mig_item].[Audit_FindExistingRootValue]...';
GO
create PROCEDURE [mig_item].[Audit_FindExistingRootValue]
(
@itemId bigint
, @jobId int
, @step tinyint
)
as
select
[AuditValueID]
from
[mig_valid].[AuditValue]
where
[ItemId] = @itemId
and [JobId] <> @jobId
and [Step] < @step
and [ParentAuditValueID] is null
GO
PRINT N'Creating Procedure [mig_item].[_GetKeyId]...';
GO
CREATE procedure [mig_item].[_GetKeyId]
@businessEntityName varchar(50)
, @keyValue varchar(512)
, @maxDeadlockRetries int
, @keyId bigint output
as
select
@keyId = [KeyId]
from
[mig_item].[MainKey]
where
[BusinessEntityName] = @businessEntityName
and [KeyValue] = @keyValue
if 0 = @@rowCount
begin
declare @retryCount int = 0
declare @identity table([KeyId] bigint)
lblRetry:
-- Guarded insert in case another thread has inserted the key after the select above and the insert now
begin try
-- Try insert first
insert [mig_item].[mainKey] (
[BusinessEntityName]
, [KeyValue]
)
output inserted.[KeyId] into @identity
values (
@businessEntityName
, @keyValue
)
select @keyId = [KeyId] from @identity
end try
begin catch
-- Insert failed
declare @err int = @@error
if 2601 = @err
begin
-- 2601 Duplicate in unique index for InterfaceEntityName and KeyValue
select
@keyId = [KeyId]
from
[mig_item].[MainKey]
where
[BusinessEntityName] = @businessEntityName
and [KeyValue] = @keyValue
end
else if @err in (1205, 1222)
begin
-- 1205: Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
-- 1222: Lock request time out period exceeded.
set @retryCount = @retryCount + 1
if @retryCount > @maxDeadlockRetries
begin
goto lblError
end
else
begin
waitfor delay '00:00:00.1'
delete from @identity
goto lblRetry
end
end
else
begin
-- Other error
goto lblError
end
end catch
end
return
lblError:
declare @errMessage nvarchar(4000) = error_message()
declare @errSeverity int = error_severity()
declare @errState int = error_state()
raiserror(@errMessage, @errSeverity, @errState)
GO
PRINT N'Creating Procedure [mig_item].[Xml_GetSource]...';
GO
CREATE procedure [mig_item].[Xml_GetSource] (
@itemId bigint
)
as
select top(1)
[XmlData]
from
[mig_item].[SourceXml] with (NoLock)
where
[ItemId] = @itemId
GO
PRINT N'Creating Procedure [mig_item].[Xml_GetExport]...';
GO
CREATE procedure [mig_item].[Xml_GetExport] (
@itemId bigint
)
as
select top(1)
[XmlData]
from
[mig_item].[ExportXml] with (NoLock)
where
[ItemId] = @itemId
GO
PRINT N'Creating Procedure [mig_item].[Xml_GetTarget]...';
GO
CREATE procedure [mig_item].[Xml_GetTarget] (
@itemId bigint
)
as
select top(1)
[XmlData]
from
[mig_item].[TargetXml] with (NoLock)
where
[ItemId] = @itemId
GO
PRINT N'Creating Procedure [mig_item].[Identity_GetString]...';
GO
create PROCEDURE [mig_item].[Identity_GetString]
(
@entityName varchar(64),
@targetEntityID uniqueidentifier,
@targetFieldID uniqueidentifier,
@xPath varchar(512),
@keyString varchar(1024),
@value varchar(max) output
)
as
set NoCount off -- Used to return the number of affected records
declare @selectedValue varchar(max)
select
@selectedValue = [StringValue]
from
[mig_item].[Identity]
where
[EntityName] = @entityName
and [TargetEntityID] = @targetEntityID
and [TargetFieldID] = @targetFieldID
and [XPath] = @xPath
and [KeyString] = @keyString
if @@rowCount = 0
begin
insert into [mig_item].[Identity] (
[EntityName]
, [TargetEntityID]
, [TargetFieldID]
, [XPath]
, [KeyString]
, [StringValue]
) values (
@entityName
, @targetEntityID
, @targetFieldID
, @xPath
, @keyString
, @value
)
end
else if @selectedValue is null -- KeyValue existed, but the KeyString was not set
begin
update [mig_item].[Identity]
set
[StringValue] = @value
where
[EntityName] = @entityName
and [TargetEntityID] = @targetEntityID
and [TargetFieldID] = @targetFieldID
and [XPath] = @xPath
and [KeyString] = @keyString
end
else
begin
set @value = @selectedValue
end
GO
PRINT N'Creating Procedure [mig_item].[Identity_GetNumber]...';
GO
create PROCEDURE [mig_item].[Identity_GetNumber]
(
@entityName varchar(64),
@targetEntityID uniqueidentifier,
@targetFieldID uniqueidentifier,
@xPath varchar(512),
@keyString varchar(1024),
@value bigint = 0 output
)
as
select
@value = [NumberValue]
from
[mig_item].[Identity]
where
[EntityName] = @entityName
and [TargetEntityID] = @targetEntityID
and [TargetFieldID] = @targetFieldID
and [XPath] = @xPath
and [KeyString] = @keyString
if 0 = @@rowCount
begin
insert into [mig_item].[Identity] (
[EntityName]
, [TargetEntityID]
, [TargetFieldID]
, [XPath]
, [KeyString]
) values (
@entityName
, @targetEntityID
, @targetFieldID
, @xPath
, @keyString
)
set @value = SCOPE_IDENTITY()
end
GO
PRINT N'Creating Procedure [mig_item].[SavedString_Set]...';
GO
create PROCEDURE [mig_item].[SavedString_Set]
(
@itemId bigint
, @keyValue varchar(256)
, @stringValue nvarchar(max) = ''
, @jobId int
, @jobInstanceId int
)
AS
update [mig_item].[SavedString] with (RowLock)
set
[StringValue] = @stringValue
where
[ItemId] = @itemId
and [KeyValue] = @keyValue
and [Jobid] = @jobId
and [JobInstanceId] = @jobInstanceId
if 0 = @@rowCount
begin
insert into [mig_item].[SavedString] with (RowLock) (
[KeyValue]
, [ItemId]
, [StringValue]
, [JobId]
, [JobInstanceId]
, [Step]
) values (
@keyValue
, @itemId
, @stringValue
, @jobId
, @jobInstanceId
, 1 -- Conversion
)
end
GO
PRINT N'Creating Procedure [mig_item].[SavedString_Get]...';
GO
create PROCEDURE [mig_item].[SavedString_Get]
(
@keyValue varchar(256)
)
AS
select
[StringValue]
from
[mig_valid].[SavedString] with (NoLock)
where
[KeyValue] = @keyValue
GO
PRINT N'Creating Procedure [mig_item].[InsertProcessed]...';
GO
CREATE procedure [mig_item].[InsertProcessed](
@itemId bigint
, @jobId int
, @jobInstanceId int
, @step int
, @elapsedTicks bigint
, @level int = null
)
as
update [mig_item].[Processed]
set
[JobId] = @jobId
, [JobInstanceId] = @jobInstanceId
, [ElapsedTicks] = @elapsedTicks
, [Level] = @level
where
[ItemId] = @itemId
and [Step] = @step
if 0 = @@rowCount
begin
insert [mig_item].[Processed] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
, [ElapsedTicks]
, [Level]
) values (
@itemId
, @jobId
, @jobInstanceId
, @step
, @elapsedTicks
, @level
)
end
GO
PRINT N'Creating Procedure [mig_item].[Export_Dependency_Insert]...';
GO
CREATE procedure [mig_item].[Export_Dependency_Insert]
@itemId bigint
, @jobId int
, @jobInstanceId int
, @businessEntityName varchar(50)
, @keyValue varchar(512)
, @maxDeadlockRetries int
as
-- Get KeyId
declare @keyId bigint
exec [mig_item].[_GetKeyId] @businessEntityName, @keyValue, @maxDeadlockRetries, @keyId output
insert [mig_item].[Dependency] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
, [DependentOnKeyId]
) values (
@itemId
, @jobId
, @jobInstanceId
, 0 -- Export
, @keyId
)
GO
PRINT N'Creating Procedure [mig_item].[Import_RelationshipSaveValue]...';
GO
CREATE PROCEDURE [mig_item].[Import_RelationshipSaveValue]
@itemId bigint
, @jobId int
, @jobInstanceId int
, @itemPath varchar(512)
, @valueType tinyint
, @keyValues varchar(512)
, @fieldId uniqueidentifier
, @value varchar(max)
as
insert [mig_item].[RelationshipValue] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
, [ItemPath]
, [ValueType]
, [KeyValues]
, [FieldId]
, [Value]
) values (
@itemId
, @jobId
, @jobInstanceId
, 1 -- Import
, @itemPath
, @valueType
, @keyValues
, @fieldId
, @value
)
GO
PRINT N'Creating Procedure [mig_item].[Import_RelationshipSaveKey]...';
GO
CREATE PROCEDURE [mig_item].[Import_RelationshipSaveKey]
(
@itemId bigint
, @jobId int
, @jobInstanceId int
, @itemPath varchar(256)
, @keyValues varchar(512)
)
AS
insert into [mig_item].[RelationshipKey] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
, [ItemPath]
, [KeyValues]
) values (
@ItemId
, @JobId
, @jobInstanceId
, 1 -- Import
, @itemPath
, @keyValues
)
GO
PRINT N'Creating Procedure [mig_item].[Import_RelationshipGetValue]...';
GO
CREATE PROCEDURE [mig_item].[Import_RelationshipGetValue]
(
@itemPath varchar(256),
@valueType tinyint,
@keyValues varchar(512),
@fieldId uniqueidentifier,
@value varchar(max) output
)
AS
set @value = null
select top(1)
@value = [Value]
from
[mig_valid].[RelationshipValue] with (NoLock)
where
[ItemPath] = @itemPath
and [ValueType] = @valueType
and [FieldId] = @fieldId
and [KeyValues] = @keyValues
GO
PRINT N'Creating Procedure [mig_item].[Import_RelationshipExists]...';
GO
CREATE PROCEDURE [mig_item].[Import_RelationshipExists]
(
@itemPath varchar(256),
@keyValues varchar(512),
@response bit output
)
AS
set NoCount on
set @response = 0
select top(1)
@response = 1
from
[mig_valid].[RelationshipKey] with (NoLock)
where
[ItemPath] = @itemPath
and [KeyValues] = @keyValues
GO
PRINT N'Creating Procedure [mig_item].[mport_RelationshipSaveBagPropertyValue]...';
GO
CREATE PROCEDURE [mig_item].[Import_RelationshipSaveBagPropertyValue]
(
@itemId bigint
, @jobId int
, @jobInstanceId int
, @itemPath varchar(512)
, @bag varchar(256)
, @property varchar(256)
, @keyValues varchar(512)
, @value xml = null
)
AS
insert [mig_item].[RelationshipBagPropertyValue] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
, [ItemPath]
, [Bag]
, [Property]
, [KeyValues]
, [Value]
) values (
@itemId
, @jobId
, @jobInstanceId
, 1 -- Import
, @itemPath
, @bag
, @property
, @keyValues
, @value
)
GO
PRINT N'Creating Procedure [mig_item].[Import_RelationshipGetBagPropertyValue]'
GO
CREATE PROCEDURE [mig_item].[Import_RelationshipGetBagPropertyValue]
@itemPath varchar(256),
@bag varchar(256),
@property varchar(256),
@keyValues varchar(512),
@value xml output
AS
set @value = null
select top(1)
@value = [Value]
from
[mig_valid].[RelationshipBagPropertyValue] with (NoLock)
where
[ItemPath] = @itemPath
and [Bag] = @bag
and [Property] = @property
and [KeyValues] = @keyValues
GO
PRINT N'Creating Procedure [mig_item].[Import_Item_Insert]...';
GO
create procedure [mig_item].[Import_Item_Insert](
@itemId bigint
, @jobId int
, @jobInstanceId int
, @result tinyint
, @targetKey varchar(512) = null
, @targetXml varchar(max) = null
, @fastTrack bit
, @compress bit
)
as
declare @step tinyint = 1 -- Convert
-------------------------------------------
-- Insert or update Item for this iteration
-------------------------------------------
declare @commit bigint
declare @cleanWorkspace bit
select top(1)
@commit = [Commit]
, @cleanWorkspace = [CleanWorkspace]
from
[mig_director].[Engine]
where
[ProjectType] = 'Target'
update [mig_item].[ImportItem]
set
[JobId] = @jobId
, [JobInstanceId] = @jobInstanceId
, [Result] = @result
, [TargetKey] = @targetKey
, [Commit] = isnull(@commit, 0)
, [CleanWorkspace] = isnull(@cleanWorkspace, 0)
where
[ItemId] = @itemId
if 0 = @@rowcount
begin
insert [mig_item].[ImportItem] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Result]
, [TargetKey]
, [Commit]
, [CleanWorkspace]
) values (
@itemId
, @jobId
, @jobInstanceId
, @result
, @targetKey
, isnull(@commit, 0)
, isnull(@cleanWorkspace, 0)
)
end
if 0 = @@error and @targetXml is not null
begin
---------------------------------------------------
-- Insert or update TargetXml for this iteration
---------------------------------------------------
update [mig_item].[TargetXml]
set
[JobId] = @jobId
, [JobInstanceId] = @jobInstanceId
, [_XmlCompressed] = case @compress when 1 then compress(@targetXml) else null end
, [_XmlUncompressed] = case @compress when 0 then @targetXml else null end
, [CreationDate] = sysdatetime()
where
[ItemId] = @itemId
if 0 = @@rowcount
begin
insert [mig_item].[TargetXml] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [_XmlCompressed]
, [_XmlUncompressed]
) values (
@itemId
, @jobId
, @jobInstanceId
, case @compress when 1 then compress(@targetXml) else null end
, case @compress when 0 then @targetXml else null end
)
end
end
GO
PRINT N'Creating Procedure [mig_item].[Import_Unload]...';
GO
CREATE procedure [mig_item].[Import_Unload]
@limit bigint
, @entityID uniqueidentifier
, @xmlEncoding varchar(50)
, @key varchar(max) = null
as
with items 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)
where
A.[EntityID] = @entityID
)
select
T.[Xmldata]
from (
select 0 as [Sequence], '' as [Xmldata]
union all
select 1 as [Sequence], ''
union all
select top(@limit)
2, B.[Xmldata]
from
items as A
inner join [mig_item].[TargetXml] as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
where
B.XmlData is not null
union all
select 3, ''
) as T
order by [Sequence]
GO
PRINT N'Creating Procedure [mig_item].[Export_Unload]...';
GO
CREATE procedure [mig_item].[Export_Unload]
@limit bigint
, @entityID uniqueidentifier
, @xmlEncoding varchar(50)
as
with items as (
select A.[ItemId]
from
[mig_item].[ExportKey] as A with (NoLock)
inner join [mig_item].[ExportItem] as B with (NoLock)
on B.[ItemId] = A.[ItemId]
and B.[Result] = 0 -- 0: Exported
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] = 0 -- 0: Export
where
A.[EntityID] = @entityID
)
select
T.[Xmldata]
from (
select 0 as [Sequence], '' as [Xmldata]
union all
select 1 as [Sequence], ''
union all
select top(@limit)
2, B.[Xmldata]
from
items as A with (NoLock)
inner join [mig_item].[ExportXml] as B
on A.[ItemId] = B.[ItemId]
where
B.[XmlData] is not null
union all
select 3, ''
) as T
order by Sequence
GO
PRINT N'Creating Procedure [mig_item].[Import_RelationshipGetXml_Import]...';
GO
CREATE PROCEDURE [mig_item].[Import_RelationshipGetXml_Import]
@entityName varchar(50)
, @keyValue varchar(512)
as
select
C.[XmlData]
from
[mig_valid].[Item] as A with (NoLock)
inner join [mig_item].[MainKey] as B with (NoLock)
on
A.[KeyId] = B.[KeyId]
and B.[BusinessEntityName] = @entityName
and B.[KeyValue] = @keyValue
inner join [mig_item].[TargetXml] as C with (NoLock)
on
A.[ItemId] = C.[ItemId]
and C.[XmlData] is not null
GO
PRINT N'Creating Procedure [mig_item].[Export_GetEntityIDsForItemSet]...';
GO
CREATE procedure [mig_item].[Export_GetEntityIDsForItemSet]
@setId bigint
as
select
A.[EntityID]
from
[mig_item].[ExportKey] as A with (NoLock)
inner join [mig_item].[ItemSet] as B with (NoLock)
on A.[ItemId] = B.[ItemId] and B.[SetId] = @setId
group by
A.[EntityID]
GO
PRINT N'Creating Procedure [mig_item].[_Export_CountItems]...';
GO
create procedure [mig_item].[_Export_CountItems]
@jobId int
as
--This procedure expects the temporary table #itemsToExport
-- [ItemId]
if object_id('tempdb..#itemsToExport') is null
begin
raiserror('Expected temp table #itemsToExport does not exist', 16, 1)
-- Dummy code to avoid reference error on temp table #itemsToExport in Visual Studio
create table #itemsToExport (
[ExportID] uniqueidentifier
, [EntityID] uniqueidentifier
, [ItemId] bigint
, [BreakFields] varchar(512)
)
end
if not exists (select 1 from [mig_director].[JobData] where [JobId] = @jobId and [Type] = 'ExportCount')
begin
declare @xml varchar(max) = (
select
B.[EntityName]
, count(*) as [Count]
from
#itemsToExport as A
inner join [mig_director].[ExportEntity] as B
on A.[EntityID] = B.[EntityID]
group by
B.[EntityName]
order by
B.[EntityName]
for xml path, root ('Count')
)
insert [mig_director].[JobData] values (@jobId, 'ExportCount', @xml, sysdatetime())
end
select
*
from
#itemsToExport
order by
[ItemId]
GO
PRINT N'Creating Type [mig_item].[ItemIdList]...';
GO
create type [mig_item].[ItemIdList] as table (
[ItemId] bigint not null
, primary key ([ItemId])
)
GO
PRINT N'Creating Procedure [mig_item].[_Import_CalculateLevels]...';
GO
create procedure [mig_item].[_Import_CalculateLevels]
@jobId int
, @itemIds [mig_item].[ItemIdList] readonly
, @includeDescendants bit
, @includeAncestors bit
as
select
A.[ItemId]
, B.[KeyId]
, C.[DependentOnKeyId]
into #itemDependencies
from
[mig_item].[_Import_IncludeDependencies](@itemIds, @includeDescendants, @includeAncestors) as A
inner join [mig_item].[Item] as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
left outer join [mig_valid].[Dependency] as C with (NoLock)
on
A.[ItemId] = C.[ItemId]
and B.[KeyId] <> C.[DependentOnKeyId] -- Avoid self-referencing items
create index __IX_ItemDependencies_1 on #itemDependencies (
[KeyId] asc
)
create index __IX_ItemDependencies_2 on #itemDependencies (
[DependentOnKeyId] asc
)
-----------------------------------
-- Start recursive CTE
-----------------------------------
;with
levels as (
-- Anchor is root items, ie items not depending on any other
select
A.[ItemId]
, A.[KeyId]
, A.[DependentOnKeyId]
, 0 as [Level]
from
#itemDependencies as A
left outer join #itemDependencies as B
on
A.[DependentOnKeyId] = B.[KeyId]
where
B.[KeyId] is null
union all
-- Recursion
select
A.[ItemId]
, A.[KeyId]
, A.[DependentOnKeyId]
, B.[Level] + 1
from
#itemDependencies as A
inner join levels as B
on
A.[DependentOnKeyId] = B.[KeyId]
)
select
[ItemId]
, [Level]
, [KeyId]
, [DependentOnKeyId]
into #levels
from
levels
option (maxrecursion 50)
-----------------------------------
-- End recursive CTE
-----------------------------------
create index __IX_Levels on #levels (
[ItemId] asc
, [Level] desc
)
select
A.[ItemId]
, max(A.[Level]) as [Level]
, B.[EntityID]
into #result
from
#levels as A
inner join [mig_item].[ExportKey] as B
on
A.[ItemId] = B.[ItemId]
group by
A.[ItemId]
, B.[EntityID]
create index __IX_Result on #result (
[Level] asc
, [EntityID] desc
)
if not exists (select 1 from [mig_director].[JobData] where [JobId] = @jobId and [Type] = 'ImportCount')
begin
declare @xml varchar(max) = (
select
A.[Level]
, C.[BusinessEntityName] as [EntityName]
, count(*) as [Count]
from
#result as A
inner join [mig_director].[ExportEntity] as B
on
A.[EntityID] = B.[EntityID]
inner join [mig_director].[BusinessEntity] as C
on
B.[BusinessEntityID] = C.[BusinessEntityID]
group by
A.[Level]
, C.[BusinessEntityName]
order by
A.[Level]
, C.[BusinessEntityName]
for xml path, root ('Count')
)
insert [mig_director].[JobData] values (@jobId, 'ImportCount', @xml, sysdatetime())
end
select
A.[ItemId]
, A.[Level]
, A.[EntityID]
from
#result as A
left outer join [mig_item].[Processed] as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
and B.[JobId] = @jobId
where
B.[ItemId] is null
order by
A.[Level]
, A.[ItemId] % 10000
GO
PRINT N'Creating Procedure [mig_item].[_Import_Unload_DependenciesXml]...';
GO
CREATE procedure [mig_item].[_Import_Unload_DependenciesXml]
@itemIds [mig_item].[ItemIdList] readonly
, @includeDescendants bit
, @includeAncestors bit
, @xmlEncoding varchar(50)
as
select
T.[Xmldata]
from (
select 0 as [Sequence], '' as [Xmldata]
union all
select 1 as [Sequence], ''
union all
select
2, B.[Xmldata]
from
[mig_item].[_Import_IncludeDependencies](@itemIds, @includeDescendants, @includeAncestors) as A
inner join [mig_item].[TargetXml] as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
where
B.XmlData is not null
union all
select 3, ''
) as T
order by [Sequence]
GO
PRINT N'Creating Procedure [mig_item].[Import_Unload_DependenciesByEntityID]...';
GO
CREATE procedure [mig_item].[Import_Unload_DependenciesByEntityID]
@limit bigint
, @entityIDs varchar(max) -- Comma-separated Guids
, @includeDescendants bit
, @includeAncestors bit
, @xmlEncoding varchar(50)
, @key varchar(max) = null
as
declare @itemIds [mig_item].[ItemIdList]
declare @entityID uniqueidentifier
declare csrUser cursor for select distinct [Id] from [mig_utility].[ParseGuids](@entityIDs, ',')
open csrUser
fetch next from csrUser into @entityID
while @@fetch_status = 0
begin
insert @itemIds
select top(@limit)
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)
where
A.[EntityID] = @entityID
fetch next from csrUser into @entityID
end
close csrUser
deallocate csrUser
exec [mig_item].[_Import_Unload_DependenciesXml] @itemIds, @includeDescendants, @includeAncestors, @xmlEncoding
GO
PRINT N'Creating Procedure [mig_item].[Import_Unload_DependenciesByItemSet]...';
GO
CREATE procedure [mig_item].[Import_Unload_DependenciesByItemSet] (
@limit bigint
, @setIdlist varchar(max)
, @includeDescendants bit
, @includeAncestors bit
, @xmlEncoding varchar(50)
, @key varchar(max) = null
)
as
declare @setIds table([SetId] bigint, primary key ([SetId]))
insert @setIds select convert(bigint, [Value]) from [mig_utility].[ParseDelimitedString](@setIdlist, ',')
declare @itemIds [mig_item].[ItemIdList]
declare @setId bigint
declare csrUser cursor for select distinct convert(bigint, [Value]) from [mig_utility].[ParseDelimitedString](@setIdlist, ',')
open csrUser
fetch next from csrUser into @setId
while @@fetch_status = 0
begin
insert @itemIds
select top(@limit)
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)
where
A.[SetId] = @setId
fetch next from csrUser into @setId
end
close csrUser
deallocate csrUser
exec [mig_item].[_Import_Unload_DependenciesXml] @itemIds, @includeDescendants, @includeAncestors, @xmlEncoding
GO
PRINT N'Creating Procedure [mig_item].[Identity_GetGuid]...';
GO
create PROCEDURE [mig_item].[Identity_GetGuid]
(
@entityName varchar(64),
@targetEntityID uniqueidentifier,
@targetFieldID uniqueidentifier,
@xPath varchar(512),
@keyString varchar(1024),
@value uniqueidentifier output
)
as
set NoCount off -- Used to return the number of affected records
declare @selectedValue uniqueidentifier
select
@selectedValue = [GuidValue]
from
[mig_item].[Identity]
where
[EntityName] = @entityName
and [TargetEntityID] = @targetEntityID
and [TargetFieldID] = @targetFieldID
and [XPath] = @xPath
and [KeyString] = @keyString
if @@rowCount = 0
begin
set @value = newid()
insert into [mig_item].[Identity] (
[EntityName]
, [TargetEntityID]
, [TargetFieldID]
, [XPath]
, [KeyString]
, [GuidValue]
) values (
@entityName
, @targetEntityID
, @targetFieldID
, @xPath
, @keyString
, @value
)
end
else if @selectedValue is null -- KeyValue existed, but the KeyGuid was not set
begin
set @value = newid()
update [mig_item].[Identity]
set
[GuidValue] = @value
where
[EntityName] = @entityName
and [TargetEntityID] = @targetEntityID
and [TargetFieldID] = @targetFieldID
and [XPath] = @xPath
and [KeyString] = @keyString
end
else
begin
set @value = @selectedValue
end
GO
PRINT N'Creating Procedure [mig_item].[Import_RelationshipGetXml_Export]...';
GO
CREATE PROCEDURE [mig_item].[Import_RelationshipGetXml_Export]
@entityName varchar(50)
, @keyValue varchar(512)
as
select
C.[XmlData]
from
[mig_valid].[Item] as A with (NoLock)
inner join [mig_item].[MainKey] as B with (NoLock)
on
A.[KeyId] = B.[KeyId]
and B.[BusinessEntityName] = @entityName
and B.[KeyValue] = @keyValue
inner join [mig_item].[ExportXml] as C with (NoLock)
on
A.[ItemId] = C.[ItemId]
and C.[XmlData] is not null
GO
PRINT N'Creating Procedure [mig_item].[Event_InsertUser]...';
GO
CREATE procedure [mig_item].[Event_InsertUser](
@itemId bigint
, @jobId int
, @jobInstanceId int
, @step tinyint
, @engine tinyint
, @disposition tinyint
, @impact tinyint
, @receiver int
, @eventName varchar(50)
, @eventID uniqueidentifier
, @eventTextID uniqueidentifier
, @parameterXml varchar(max)
, @parameterHash binary(20)
, @groupHash binary(20)
, @xPath varchar(512) = null
)
as
insert [mig_item].[EventUser] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
, [Engine]
, [Disposition]
, [Impact]
, [Receiver]
, [EventName]
, [EventID]
, [EventTextID]
, [ParameterXml]
, [ParameterHash]
, [GroupHash]
, [XPath]
) values (
@itemId
, @jobId
, @jobInstanceId
, @step
, @engine
, @disposition
, @impact
, @receiver
, @eventName
, @eventID
, @eventTextID
, @parameterXml
, @parameterHash
, @groupHash
, @xPath
)
GO
PRINT N'Creating Procedure [mig_item].[Event_InsertSystem]...';
GO
CREATE procedure [mig_item].[Event_InsertSystem](
@itemId bigint
, @jobId int
, @jobInstanceId int
, @step tinyint
, @engine tinyint
, @disposition tinyint
, @impact tinyint
, @receiver tinyint
, @message varchar(256)
, @parameterXml varchar(max)
, @parameterHash binary(20)
, @groupHash binary(20)
, @xPath varchar(512) = null
)
as
insert [mig_item].[EventSystem] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Step]
, [Engine]
, [Disposition]
, [Impact]
, [Receiver]
, [Message]
, [ParameterXml]
, [ParameterHash]
, [GroupHash]
, [XPath]
) values (
@itemId
, @jobId
, @jobInstanceId
, @step
, @engine
, @disposition
, @impact
, @receiver
, @message
, @parameterXml
, @parameterHash
, @groupHash
, @xPath
)
GO
PRINT N'Creating Procedure [mig_profile].[JobInsert]...';
GO
CREATE procedure [mig_profile].[JobInsert] (
@jobInstanceId int
, @frameId char(32)
, @parentFrameId char(32) = null
, @location varchar(max)
, @codeLocationID uniqueidentifier
, @profilingType varchar(50)
, @scope varchar(256)
, @fullScope varchar(max)
, @ticksInclusive bigint
, @ticksExclusive bigint
, @count int
) as
insert into [mig_profile].[Frame] (
[JobInstanceId]
, [FrameId]
, [ParentFrameId]
, [Location]
, [CodeLocationID]
, [ProfilingType]
, [Scope]
, [FullScope]
) values (
@jobInstanceId
, @frameId
, @parentFrameId
, @location
, @codeLocationID
, @profilingType
, @scope
, @fullScope
)
insert into [mig_profile].[Job] (
[JobInstanceId]
, [FrameId]
, [TicksInclusive]
, [TicksExclusive]
, [Count]
) values (
@jobInstanceId
, @frameId
, @ticksInclusive
, @ticksExclusive
, @count
)
GO
PRINT N'Creating Procedure [mig_profile].[ItemInsert]...';
GO
CREATE procedure [mig_profile].[ItemInsert] (
@jobInstanceId int
, @itemId bigint
, @frameId char(32)
, @ticksInclusive bigint
, @ticksExclusive bigint
, @count int
) as
insert into [mig_profile].[Item] (
[JobInstanceId]
, [ItemId]
, [FrameId]
, [TicksInclusive]
, [TicksExclusive]
, [Count]
) values (
@jobInstanceId
, @itemId
, @frameId
, @ticksInclusive
, @ticksExclusive
, @count
)
GO
PRINT N'Creating Procedure [mig_profile].[TraceInsert]...';
GO
CREATE procedure [mig_profile].[TraceInsert] (
@traceID uniqueidentifier
, @parentTraceID uniqueidentifier = null
, @jobInstanceId bigint
, @itemId bigint
, @frameId char(32)
, @entityID uniqueidentifier
, @ticksInclusive bigint
, @ticksExclusive bigint
, @count int
, @itemPath varchar(1024) = null
) as
insert into [mig_profile].[Trace] (
[TraceID]
, [ParentTraceID]
, [FrameId]
, [JobInstanceId]
, [ItemId]
, [EntityID]
, [TicksInclusive]
, [TicksExclusive]
, [Count]
, [ItemPath]
) values (
@traceID
, @parentTraceID
, @frameId
, @jobInstanceId
, @itemId
, @entityID
, @ticksInclusive
, @ticksExclusive
, @count
, @itemPath
)
GO
PRINT N'Creating Procedure [mig_profile].[ListAll]...';
GO
CREATE PROCEDURE [mig_profile].[ListAll](
@jobInstanceId int
)
AS
declare @items bit = 0
select @items = 1 from [mig_profile].[Item] where [JobInstanceId] = @jobInstanceId
select @items as [HasItems]
select
A.[FrameId]
, isnull(A.[ParentFrameId], '') as [ParentFrameId]
, A.[ProfilingType]
, A.[Scope]
, A.[FullScope]
, B.[TicksInclusive]
, B.[TicksExclusive]
, B.[Count]
from
[mig_profile].[Frame] as A with (NoLock)
inner join [mig_profile].[Job] as B with (NoLock)
on A.[JobInstanceId] = B.[JobInstanceId]
and A.[FrameId] = B.[FrameId]
where
A.[JobInstanceId] = @jobInstanceId
GO
PRINT N'Creating Procedure [mig_tracking].[Actual_List]...';
GO
CREATE procedure [mig_tracking].[Actual_List]
as
begin
set nocount on
-----------------------------------------------------
-- List SmartTexts
-----------------------------------------------------
select
[ItemID]
, [Xml]
from [mig_director].[SmartText]
-----------------------------------------------------
-- User Events
-----------------------------------------------------
select distinct
A.[EventId]
, A.[EventTextId]
, A.[Engine]
, A.[Step]
, A.[Disposition]
, A.[Impact]
, A.[Receiver]
, A.[EventName]
, cast(case
when B.[ItemID] is null then 1
else 0
end as bit) as [TextMissing]
into #userMessage
from [mig_valid].[EventUser] as A
left outer join [mig_director].[SmartText] as B
on B.[ItemID] = A.[EventTextId]
select
[EventId]
, [EventTextId]
, [Engine]
, [Step]
, [Disposition]
, [Impact]
, [Receiver]
, [EventName]
from #userMessage
-----------------------------------------------------
-- System Events
-----------------------------------------------------
select distinct
[Engine]
, [Step]
, [Disposition]
, [Impact]
, [Receiver]
, [Message]
from [mig_valid].[EventSystem]
-----------------------------------------------------
-- List current PartitionValues
-----------------------------------------------------
select distinct
[PartitionValue]
from
[mig_valid].[Item]
-----------------------------------------------------
-- Report missing smarttexts for User Events
-----------------------------------------------------
select
[EventTextID]
, [EventName]
from #userMessage
where [TextMissing] = 1
end
GO
PRINT N'Creating Procedure [mig_tracking].[Baseline_Delete]...';
GO
CREATE procedure [mig_tracking].[Baseline_Delete]
@baselineId int
as
set nocount on;
delete [mig_tracking].[Baseline]
where
[BaselineId] = @baselineId
GO
PRINT N'Creating Procedure [mig_tracking].[Baseline_List]...';
GO
CREATE PROCEDURE [mig_tracking].[Baseline_List]
as
select
[BaselineId]
, [Title]
, [Time]
, [IsCurrent]
, [Comment]
from
[mig_tracking].[Baseline]
order by
[Time] desc
GO
PRINT N'Creating Procedure [mig_tracking].[Baseline_Modify]...';
GO
CREATE procedure [mig_tracking].[Baseline_Modify]
@baselineId int
, @time DateTime2
, @title nvarchar(128)
, @comment nvarchar(max) = null
as
update [mig_tracking].[Baseline]
set
[Title] = @title
, [Time] = @time
, [Comment] = @comment
where
[BaselineId] = @baselineId
GO
PRINT N'Creating Procedure [mig_tracking].[Baseline_Save]...';
GO
CREATE procedure [mig_tracking].[Baseline_Save]
@title nvarchar(128)
, @time DateTime2
, @comment nvarchar(max)
, @setCurrent bit
as
set nocount on;
declare @inserted table([BaselineId] int)
-- Create Baseline
insert [mig_tracking].[Baseline](
[Time]
, [Title]
, [Comment]
, [IsCurrent]
)
output inserted.[BaselineId] into @inserted
values (
@time
, @title
, @comment
, 0
);
if 1 = @setCurrent
begin
declare @baselineId int = (select top(1) [BaselineId] from @inserted)
exec [mig_tracking].[Baseline_SetCurrent] @baselineId
end
GO
PRINT N'Creating Procedure [mig_tracking].[Baseline_SetCurrent]...';
GO
CREATE procedure [mig_tracking].[Baseline_SetCurrent]
@baselineId int
as
set nocount on;
-- Clear current Baseline
update [mig_tracking].[Baseline]
set
[IsCurrent] = case [BaselineId] when @baselineId then 1 else 0 end
GO
PRINT N'Creating Procedure [mig_tracking].[Actual_Update]...';
GO
CREATE procedure [mig_tracking].[Actual_Update]
@systemMessages xml --
, @userMessages xml --
, @entitiesToProcess varchar(max) = null
, @runDate datetime2
, @user varchar(256)
, @jobInstanceId int
as
begin
set nocount on;
declare @start datetime = getdate()
declare @logId bigint
declare @text varchar(max) =
'------------------------------------------------------------------
Elapsed [mig_tracking].[Actual_Update]
------------------------------------------------------------------'
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 0, @text, @logId output
-- Table to hold EntityID to process
declare @entities table(EntityID uniqueidentifier, primary key (EntityID))
if @entitiesToProcess is not null
begin
insert into @entities
select distinct [Id] from [mig_utility].[ParseGuids](@entitiesToProcess, ',')
end
-- Table to hold known User Events from project database
declare @userEvents table([MessageID] uniqueidentifier, [Step] tinyint, [Engine] tinyint, [Disposition] int, [Impact] int, [Receiver] int, [EventID] uniqueidentifier);
with
msgXml as (
select T.c.query('.') as [Msg]
from @userMessages.nodes('/list/msg') T(c)
)
insert @userEvents
select
[Msg].value('(*/messageID)[1]', 'uniqueidentifier') as [MessageID]
, [Msg].value('(*/step)[1]', 'tinyint') as [Step]
, [Msg].value('(*/engine)[1]', 'tinyint') as [Engine]
, [Msg].value('(*/disposition)[1]', 'int') as [Disposition]
, [Msg].value('(*/impact)[1]', 'int') as [Impact]
, [Msg].value('(*/receiver)[1]', 'int') as [Receiver]
, [Msg].value('(*/eventID)[1]', 'uniqueidentifier') as [EventID]
from msgXml
-- Table to hold known System Events from project database
declare @systemEvents table([MessageID] uniqueidentifier, [Step] tinyint, [Engine] tinyint, [Disposition] int, [Impact] int, [Receiver] int, [MessageFmt] varchar(max));
with
msgXml as (
select T.c.query('.') as [Msg]
from @systemMessages.nodes('/list/msg') T(c)
)
insert @systemEvents
select
[Msg].value('(*/messageID)[1]', 'uniqueidentifier') as [MessageID]
, [Msg].value('(*/step)[1]', 'tinyint') as [Step]
, [Msg].value('(*/engine)[1]', 'tinyint') as [Engine]
, [Msg].value('(*/disposition)[1]', 'int') as [Disposition]
, [Msg].value('(*/impact)[1]', 'int') as [Impact]
, [Msg].value('(*/receiver)[1]', 'int') as [Receiver]
, [Msg].value('(*/messageFmt)[1]', 'varchar(max)') as [MessageFmt]
from msgXml
-----------------------------------------------------
-- RootItem
-----------------------------------------------------
if @entitiesToProcess is null
begin
if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_SourceKey')
drop index [IX_RootItem_SourceKey] on [mig_tracking].[RootItem]
if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_MigrationKey')
drop index [IX_RootItem_MigrationKey] on [mig_tracking].[RootItem]
if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_TargetKey')
drop index [IX_RootItem_TargetKey] on [mig_tracking].[RootItem]
truncate table [mig_tracking].[RootItem]
end
else
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootItem', @logId output
delete [mig_tracking].[RootItem]
from
[mig_tracking].[RootItem] as A
inner join @entities as B
on A.[EntityID] = B.[EntityID]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItem', @logId output
insert [mig_tracking].[RootItem]
select
[ItemId]
, [PartitionValue]
, [EntityID]
, [SourceKey]
, [MigrationKey]
, [TargetKey]
, [IsExported]
, [IsImported]
, [ExportID]
, [HasSourceXml]
, [HasExportXml]
, [HasTargetXml]
, [SourceCommit]
, [SourceCleanWorkspace]
, [TargetCommit]
, [TargetCleanWorkspace]
, [ExportJobId]
, [ExportTime]
, [ImportJobId]
, [ImportTime]
, @runDate
from
[mig_tracking].[VwRootItem]
where
(@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities))
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
if not exists (select * from sys.indexes WHERE object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_SourceKey')
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create SourceKey index on RootItem', @logId output
create nonclustered index [IX_RootItem_SourceKey] on [mig_tracking].[RootItem]
(
[EntityID] asc
, [SourceKey] asc
, [PartitionValue] asc
)
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
if not exists (select * from sys.indexes WHERE object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_MigrationKey')
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create MigrationKey index on RootItem', @logId output
create nonclustered index [IX_RootItem_MigrationKey] on [mig_tracking].[RootItem]
(
[EntityID] asc
, [MigrationKey] asc
, [PartitionValue] asc
)
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_TargetKey')
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create TargetKey index on RootItem', @logId output
create nonclustered index [IX_RootItem_TargetKey] on [mig_tracking].[RootItem]
(
[EntityID] asc
, [TargetKey] asc
, [PartitionValue] asc
)
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
-----------------------------------------------------
-- RootItemLink
-----------------------------------------------------
if @entitiesToProcess is null
begin
if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemLink]') and name = N'IX_RootItemLink_TargetItemId')
drop index [IX_RootItemLink_TargetItemId] on [mig_tracking].[RootItemLink]
if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemLink]') and name = N'IX_RootItemLink_TargetEntityID')
drop index [IX_RootItemLink_TargetEntityID] on [mig_tracking].[RootItemLink]
truncate table [mig_tracking].[RootItemLink]
end
else
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootItemLink', @logId output
delete [mig_tracking].[RootItemLink]
from
[mig_tracking].[RootItemLink] as A
inner join @entities as B
on A.[TargetEntityID] = B.[EntityID]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemLink', @logId output
insert [mig_tracking].[RootItemLink]
select
[ParentItemId]
, [ChildItemId]
, [ChildEntityID]
, @runDate
from
[mig_tracking].[VwRootItemLink]
where
(@entitiesToProcess is null or [ChildEntityID] in (select [EntityID] from @entities))
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
if not exists (select * from sys.indexes WHERE object_id = object_id(N'[mig_tracking].[RootItemLink]') and name = N'IX_RootItemLink_TargetItemId')
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create TargetItemId index on RootItemLink', @logId output
create nonclustered index [IX_RootItemLink_TargetItemId] on [mig_tracking].[RootItemLink]
(
[TargetItemId] asc
)
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
if not exists (select * from sys.indexes WHERE object_id = object_id(N'[mig_tracking].[RootItemLink]') and name = N'IX_RootItemLink_TargetEntityID')
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create TargetEntityID index on RootItemLink', @logId output
create nonclustered index [IX_RootItemLink_TargetEntityID] ON [mig_tracking].[RootItemLink]
(
[TargetEntityID] asc
)
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
-----------------------------------------------------
-- EventParameter
-----------------------------------------------------
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert EventParameter', @logId output
;with
usrEv as (select distinct [ParameterHash], [GroupHash], [ParameterXml] from [mig_valid].[EventUser])
, sysEv as (select distinct [ParameterHash], [GroupHash], [ParameterXml] from [mig_valid].[EventSystem])
, allEv as (
select * from usrEv
union
select * from sysEv
)
insert [mig_tracking].[EventParameter] (
[HashValue]
, [GroupHash]
, [ParameterXml]
, [RunDate]
)
select
A.[ParameterHash]
, A.[GroupHash]
, A.[ParameterXml]
, @runDate
from allEv as A
left outer join [mig_tracking].[EventParameter] as B
on B.[HashValue] = A.[ParameterHash]
where
B.[HashValue] is null
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
-----------------------------------------------------
-- RootItemEvent
-----------------------------------------------------
if @entitiesToProcess is null
begin
if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_EntityID')
drop index [IX_RootItemEvent_EntityID] on [mig_tracking].[RootItemEvent]
if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent')
drop index [IX_RootItemEvent] on [mig_tracking].[RootItemEvent]
if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_ItemId')
drop index [IX_RootItemEvent_ItemId] on [mig_tracking].[RootItemEvent]
truncate table [mig_tracking].[RootItemEvent]
end
else
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootItemEvent', @logId output
delete [mig_tracking].[RootItemEvent]
from
[mig_tracking].[RootItemEvent] as A
inner join @entities as B
on A.[EntityID] = B.[EntityID]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
-- User events
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemEvent (User)', @logId output
;with
[events] as (
select
B.[EntityID]
, C.[PartitionValue]
, A.[ItemId]
, A.[Step]
, A.[Engine]
, A.[Receiver]
, A.[Disposition]
, A.[Impact]
, A.[EventID]
, A.[ParameterHash]
, A.[GroupHash]
, A.[ParameterXml]
, count(*) AS [EventCount]
from
[mig_valid].[EventUser] as A
inner join [mig_item].[ExportKey] as B on A.[ItemId] = B.[ItemId]
inner join [mig_item].[Item] as C on A.[ItemId] = C.[ItemId]
where
(@entitiesToProcess is null or B.[EntityID] in (select [EntityID] from @entities))
group by
B.[EntityID]
, C.[PartitionValue]
, A.[ItemId]
, A.[Engine]
, A.[Step]
, A.[Receiver]
, A.[Disposition]
, A.[Impact]
, A.[EventID]
, A.[ParameterHash]
, A.[GroupHash]
, A.[ParameterXml]
)
insert [mig_tracking].[RootItemEvent] (
[RowID]
, [EntityID]
, [ItemId]
, [PartitionValue]
, [MessageID]
, [ParameterHash]
, [GroupHash]
, [EventCount]
, [RunDate])
select
newid() -- To detect problem with join
, A.[EntityID]
, A.[ItemId]
, A.[PartitionValue]
, B.[MessageID]
, A.[ParameterHash]
, A.[GroupHash]
, A.[EventCount]
, @runDate
from
[events] as A
inner join @userEvents as B
on
B.[Step] = A.[Step]
and B.[Engine] = A.[Engine]
and B.[Disposition] = A.[Disposition]
and B.[Impact] = A.[Impact]
and B.[Receiver] = A.[Receiver]
and B.[EventID] = A.[EventID]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
-- System events
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemEvent (System)', @logId output
;with
[events] as (
select
B.[EntityID]
, C.[PartitionValue]
, A.[ItemId]
, A.[Step]
, A.[Engine]
, A.[Receiver]
, A.[Disposition]
, A.[Impact]
, A.[Message]
, A.[ParameterHash]
, A.[GroupHash]
, A.[ParameterXml]
, count(*) AS [EventCount]
from
[mig_valid].[EventSystem] as A
inner join [mig_item].[ExportKey] as B on A.[ItemId] = B.[ItemId]
inner join [mig_item].[Item] as C on A.[ItemId] = C.[ItemId]
where
(@entitiesToProcess is null or B.[EntityID] in (select [EntityID] from @entities))
group by
B.[EntityID]
, C.[PartitionValue]
, A.[ItemId]
, A.[Engine]
, A.[Step]
, A.[Receiver]
, A.[Disposition]
, A.[Impact]
, A.[Message]
, A.[ParameterHash]
, A.[GroupHash]
, A.[ParameterXml]
)
insert [mig_tracking].[RootItemEvent] (
[RowID]
, [EntityID]
, [ItemId]
, [PartitionValue]
, [MessageID]
, [ParameterHash]
, [GroupHash]
, [EventCount]
, [RunDate])
select
newid() -- To detect problem with join
, A.[EntityID]
, A.[ItemId]
, A.[PartitionValue]
, B.[MessageID]
, A.[ParameterHash]
, A.[GroupHash]
, A.[EventCount]
, @runDate
from
[events] as A
inner join @systemEvents as B
on
B.[Step] = A.[Step]
and B.[Engine] = A.[Engine]
and B.[Disposition] = A.[Disposition]
and B.[Impact] = A.[Impact]
and B.[Receiver] = A.[Receiver]
and B.[MessageFmt] = A.[Message]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_EntityID')
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create EntityID index on RootItemEvent', @logId output
create nonclustered index [IX_RootItemEvent_EntityID] on [mig_tracking].[RootItemEvent]
(
[EntityID] asc
)
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent')
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create RootItemEvent index on RootItemEvent', @logId output
create nonclustered index [IX_RootItemEvent] on [mig_tracking].[RootItemEvent]
(
[MessageID] asc,
[EntityID] asc,
[PartitionValue] asc,
[ItemId] asc
) include (
[ParameterHash]
)
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_ItemId')
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create RootItemEvent_ItemId index on RootItemEvent', @logId output
create nonclustered index [IX_RootItemEvent_ItemId] on [mig_tracking].[RootItemEvent]
(
[ItemId] asc
)
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
-----------------------------------------------------
-- RootEntityEvent
-----------------------------------------------------
if @entitiesToProcess is null
begin
truncate table [mig_tracking].[RootEntityEvent];
end
else
begin
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootEntityEvent', @logId output
delete [mig_tracking].[RootEntityEvent]
from
[mig_tracking].[RootEntityEvent] as A
inner join @entities as B
on A.[EntityID] = B.[EntityID]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
end
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootEntityEvent', @logId output
insert [mig_tracking].[RootEntityEvent] (
[EntityID]
, [PartitionValue]
, [MessageID]
, [ParameterHash]
, [GroupHash]
, [EventCount]
, [ItemCount]
, [RunDate])
select
[EntityID]
, [PartitionValue]
, [MessageID]
, [ParameterHash]
, [GroupHash]
, sum([EventCount])
, count(distinct [ItemId])
, @runDate
from
[mig_tracking].[RootItemEvent]
where
(@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities))
group by
[EntityID]
, [PartitionValue]
, [MessageID]
, [ParameterHash]
, [GroupHash]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
-----------------------------------------------------
-- PartitionEventCount
-----------------------------------------------------
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'PartitionEventCount, update', @logId output
declare @addedCounts table(
[MessageID] uniqueidentifier not null
, [EntityID] uniqueidentifier not null
, [PartitionValue] nvarchar(50) not null
, [InstanceCount] int not null
, [ItemCount] int not null
)
declare @existingCounts table(
[MessageID] uniqueidentifier not null
, [EntityID] uniqueidentifier not null
, [PartitionValue] nvarchar(50) not null
, [InstanceCount] int not null
, [ItemCount] int not null
)
insert @existingCounts
select
A.[MessageID]
, A.[EntityID]
, A.[PartitionValue]
, A.[InstanceCount]
, A.[ItemCount]
from
[mig_tracking].[PartitionEventCount] as A
where
A.[Time] = (
select max([Time])
from [mig_tracking].[PartitionEventCount] as B
where
A.[MessageID] = B.[MessageID]
and A.[EntityID] = B.[EntityID]
and A.[PartitionValue] = B.[PartitionValue]
)
;with
[newCounts] as (
select
[MessageID]
, [EntityID]
, [PartitionValue]
, sum([EventCount]) as [InstanceCount]
, count(distinct [ItemId]) as [ItemCount]
from
[mig_tracking].[RootItemEvent]
where
(@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities))
group by
[MessageID]
, [EntityID]
, [PartitionValue]
)
insert [mig_tracking].[PartitionEventCount] (
[MessageID]
, [EntityID]
, [PartitionValue]
, [InstanceCount]
, [ItemCount]
, [Time]
)
output inserted.[MessageID], inserted.[EntityID], inserted.[PartitionValue], inserted.[InstanceCount], inserted.[ItemCount] into @addedCounts
select
coalesce(A.[MessageID], B.[MessageID])
, coalesce(A.[EntityID], B.[EntityID])
, coalesce(A.[PartitionValue], B.[PartitionValue])
, isnull(A.[InstanceCount], 0)
, isnull(A.[ItemCount], 0)
, @runDate
from
[newCounts] as A
full outer join @existingCounts as B
on
A.[MessageID] = B.[MessageID]
and A.[EntityID] = B.[EntityID]
and A.[PartitionValue] = B.[PartitionValue]
where
(@entitiesToProcess is null or coalesce(A.[EntityID], B.[EntityID]) in (select [EntityID] from @entities))
and (
isnull(A.[InstanceCount], 0) <> isnull(B.[InstanceCount], 0)
or isnull(A.[ItemCount], 0) <> isnull(B.[ItemCount], 0)
)
-- Log count changes
insert [mig_tracking].[Log]
select
A.[MessageID]
, A.[EntityID]
, A.[PartitionValue]
, case
when B.[InstanceCount] is null then 0 -- Added
else 1 -- Modified
end
, @runDate
, @user
, 'Instance Count (' + A.[PartitionValue] + ')'
, format(isnull(B.[InstanceCount], 0), 'N0')
, format(A.[InstanceCount], 'N0')
from
@addedCounts as A
left outer join @existingCounts as B
on
A.[MessageID] = B.[MessageID]
and A.[EntityID] = B.[EntityID]
and A.[PartitionValue] = B.[PartitionValue]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
-----------------------------------------------------
-- Statistics
-----------------------------------------------------
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Statistics', @logId output;
with
export as (
select *
from [mig_director].[_Export_Statistics]()
where
[PartitionValue] is not null
)
, import as (
select *
from [mig_director].[_Import_Statistics]()
where
[PartitionValue] is not null
)
, newStatistic as (
select
A.[PartitionValue]
, A.[EntityID]
, A.[Count] - A.[Dropped] as [Available]
, A.[Discarded] as [ExpDiscarded]
, A.[Exported]
, isnull(B.[Discarded], 0) as [ImpDiscarded]
, isnull(B.[Imported], 0) as [Imported]
, A.[Difference] + isnull(B.[Difference], 0) as [Check]
from
export as A
left outer join import as B
on A.[PartitionValue] = B.[PartitionValue]
and A.[EntityID] = B.[EntityID]
where
(@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities))
)
, curStatistic as (
select *
from
[mig_tracking].[Statistic] as A
where
A.[Time] = (
select max([Time])
from [mig_tracking].[Statistic]
where
[PartitionValue] = A.[PartitionValue]
and [EntityID] = A.[EntityID]
)
)
insert [mig_tracking].[Statistic] (
[PartitionValue]
, [EntityID]
, [Time]
, [Available]
, [ExpDiscarded]
, [Exported]
, [ImpDiscarded]
, [Imported]
)
select
A.[PartitionValue]
, A.[EntityID]
, @runDate
, A.[Available]
, A.[ExpDiscarded]
, A.[Exported]
, A.[ImpDiscarded]
, A.[Imported]
from
newStatistic as A
left outer join curStatistic as B
on
A.[PartitionValue] = B.[PartitionValue]
and A.[EntityID] = B.[EntityID]
where
B.[EntityID] is null
or B.[Available] <> A.[Available]
or B.[ExpDiscarded] <> A.[ExpDiscarded]
or B.[Exported] <> A.[Exported]
or B.[ImpDiscarded] <> A.[ImpDiscarded]
or B.[Imported] <> A.[Imported]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
-----------------------------------------------------
-- Parameters
-----------------------------------------------------
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Parameters', @logId output
truncate table [mig_tracking].[Parameter]
insert [mig_tracking].[Parameter]
select
[ItemID]
, [ProjectType]
, [Name]
, [Value]
, [Description]
from
[mig_director].[RunParameter]
where
[Present] = 1
and [Value] is not null
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
set @text =
'------------------------------------------------------------------
' + convert(varchar, getdate() - @start, 108) + ' Total
------------------------------------------------------------------'
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 0, @text, @logId output
-- Return the current non-zero partition counts for status updates in the project DB
select
A.[MessageID]
, A.[EntityID]
, A.[PartitionValue]
from
[mig_tracking].[PartitionEventCount] as A
where
A.[InstanceCount] > 0
and A.[Time] = (
select max(B.[Time])
from [mig_tracking].[PartitionEventCount] as B
where
A.[MessageID] = B.[MessageID]
and A.[EntityID] = B.[EntityID]
and A.[PartitionValue] = B.[PartitionValue]
)
end
GO
PRINT N'Creating Procedure [mig_translation].[ListValuesets]...';
GO
create procedure [mig_translation].[ListValuesets]
as
-- Returns an xml document containing all the Translation Valuesets as well as all SmartTexts used by Translation Valueset Display or Description
-- The document is consumed by the stored procedure [mig_translation].[TranslationValueset_Update] in the project database
-- Sample of returned xml (displayTextID and descriptionTextId are optional)
--
--
-- 7eef8e2d-0429-4786-9949-4f207d372ceb
-- TranslateBankUnits
-- 7eef8e2d-0429-4786-9949-4f207d372ceb
-- a5411722-071c-43ac-9963-2d4a6d6055c3
--
--
-- 12a1f01e-38b2-438c-882c-1f2f6b133bf8
-- TranslateSegments
--
--
-- BankUnits display name
--
--
-- BankUnits decription
--
--
declare @valueset table(
[id] uniqueidentifier
, [name] varchar(max)
, [displayTextID] uniqueidentifier
, [descriptionTextID] uniqueidentifier
)
insert @valueset
select
A.[ItemID] as [id]
, A.[Name] as [name]
, A.[Columns].value('(*/@displaySmartTextId)[1]', 'uniqueidentifier') as [displayTextID]
, A.[Columns].value('(*/@descriptionSmartTextId)[1]', 'uniqueidentifier') as [descriptionTextID]
from
[mig_director].[Valueset] as A
inner join [mig_director].[Engine] as B
on
A.[ProjectID] = B.[ProjectID]
where
A.[ValuesetType] = 2
and A.[Present] = 1
declare @texts varchar(max) = ''
select @texts = @texts + cast(A.[Xml] as varchar(max))
from
[mig_director].[SmartText] as A
inner join @valueset as B
on
A.[ItemID] = B.[displayTextID]
or A.[ItemID] = B.[descriptionTextID]
select cast((
'' +
cast((select * from @valueset for xml path('valueset')) as varchar(max)) +
@texts +
'') as xml)
GO
PRINT N'Creating Procedure [mig_director].[JobInstance_SetFaulted]...';
GO
CREATE PROCEDURE [mig_director].[JobInstance_SetFaulted] (
@jobInstanceId int
, @message varchar(max)
)
as
declare @entryId bigint
exec [mig_director].[JobInstance_SetCompleted] @jobInstanceId, 2
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 2, @message, @entryId output
GO
PRINT N'Creating Procedure [mig_garbage].[_InvalidateSet]...';
GO
CREATE procedure [mig_garbage].[_InvalidateSet]
@toInvalidate [mig_garbage].[InvalidateSet] readonly
as
declare @now datetime2(7) = sysdatetime()
declare @wasInvalidated [mig_garbage].[InvalidateSet]
-- Invalidate existing items from previous jobs
update [mig_item].[Active] with (RowLock)
set
[JobId] = B.[ValidJobId]
, [JobInstanceId] = B.[ValidJobInstanceId]
output deleted.ItemId, deleted.Step, deleted.JobId, deleted.JobInstanceId, B.[ValidJobId], B.[ValidJobInstanceId] into @wasInvalidated
from
[mig_item].[Active] as A
inner join @toInvalidate as B
on A.[ItemId] = B.[ItemId]
where
(B.[Step] is null or A.[Step] >= B.[Step])
and (B.[InvalidJobId] is null or A.[JobId] = B.[InvalidJobId])
and (B.[InvalidJobInstanceId] is null or A.[JobInstanceId] = B.[InvalidJobInstanceId])
-- Create new items
insert [mig_item].[Active] with (RowLock) (
[ItemId]
, [Step]
, [JobId]
, [JobInstanceId]
)
select
A.[ItemId]
, A.[Step]
, A.[ValidJobId]
, A.[ValidJobInstanceId]
from
@toInvalidate as A
left outer join @wasInvalidated as B
on A.[ItemId] = B.[ItemId]
and A.[InvalidJobId] = B.[InvalidJobId]
and A.[InvalidJobInstanceId] = B.[InvalidJobInstanceId]
left outer join [mig_item].[Active] as C
on A.[ItemId] = C.[ItemId]
and A.[Step] = C.[Step]
where
A.[Step] is not null
and A.[ValidJobId] is not null
and B.[ItemId] is null
and C.[ItemId] is null
exec [mig_garbage].[_Log_InvalidateItems] @wasInvalidated
GO
PRINT N'Creating Procedure [mig_garbage].[_InvalidateItem]...';
GO
CREATE procedure [mig_garbage].[_InvalidateItem]
@itemId bigint
, @step tinyint = null
, @validJobId int = null
, @validJobInstanceId int = null
as
declare @now datetime2(7) = sysdatetime()
declare @wasInvalidated [mig_garbage].[InvalidateSet]
-- Invalidate existing item from previous job
update [mig_item].[Active] with (RowLock)
set
[JobId] = @validJobId
, [JobInstanceId] = @validJobInstanceId
output deleted.ItemId, deleted.Step, deleted.JobId, deleted.JobInstanceId, @validJobId, @validJobInstanceId into @wasInvalidated
from
[mig_item].[Active]
where
[ItemId] = @itemId and [Step] >= @step
if 0 = @@rowCount
begin
-- Create new item
insert [mig_item].[Active] with (RowLock) (
[ItemId]
, [Step]
, [JobId]
, [JobInstanceId]
) values (
@itemId
, @step
, @validJobId
, @validJobInstanceId
)
end
exec [mig_garbage].[_Log_InvalidateItems] @wasInvalidated
GO
PRINT N'Creating Function [mig_item].[Import_IncludeDependencies]...';
GO
create function [mig_item].[_Import_IncludeDependencies](
@itemIds [mig_item].[ItemIdList] readonly
, @includeDescendants bit
, @includeAncestors bit
)
returns @ids table (
[ItemId] bigint
)
as
begin
with
------------------------------------------------------------------
-- A utility query to only inspect relevant dependencies
------------------------------------------------------------------
validDependencies as (
select
A.[ItemId]
, A.[KeyId]
, C.[DependentOnKeyId]
from
[mig_item].[Item] as A with (NoLock)
inner join [mig_item].[ExportItem] as B with (NoLock)
on
B.[ItemId] = A.[ItemId]
and B.[Result] = 0 -- 0:Exported
left outer join [mig_valid].[Dependency] as C
on
A.[ItemId] = C.[ItemId]
where
C.[Id] is null -- Root items
or A.[KeyId] <> C.[DependentOnKeyId] -- Avoid self-referencing items
)
------------------------------------------------------------------
-- Descendants (if @includeDescendants = 1)
------------------------------------------------------------------
, descendantSeed as (
select
B.[ItemId]
, B.[KeyId]
, B.[DependentOnKeyId]
from
@itemIds as A
inner join validDependencies as B with (NoLock)
on
A.[ItemId] = B.ItemId
where
@includeDescendants = 1
)
, descendants as (
select
A.[ItemId]
, A.[KeyId]
, cast(null as bigint) as [DependentOnKeyId]
from
descendantSeed as A
left outer join descendantSeed as B
on
A.[DependentOnKeyId] = B.[KeyId]
where
B.[KeyId] is null
union all
select
B.[ItemId]
, B.[KeyId]
, B.[DependentOnKeyId]
from
descendants as A
inner join validDependencies as B
on
A.[KeyId] = B.[DependentOnKeyId]
)
------------------------------------------------------------------
-- Ancestors (if @includeAncestors = 1)
------------------------------------------------------------------
, ancestors as (
select
C.[ItemId]
, C.[KeyId]
, B.[DependentOnKeyId]
from
@itemIds as A
inner join validDependencies as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
inner join [mig_item].[Item] as C with (NoLock)
on
B.[DependentOnKeyId] = C.[KeyId]
where
@includeAncestors = 1
union all
select
A.[ItemId]
, A.[KeyId]
, A.[DependentOnKeyId]
from
validDependencies as A
inner join ancestors as B
on
A.[KeyId] = B.[DependentOnKeyId]
)
------------------------------------------------------------------
-- Result
------------------------------------------------------------------
insert @ids
select [ItemId] from @itemIds
union
select [ItemId] from descendants
union
select [ItemId] from ancestors
return
end
GO
PRINT N'Creating Procedure [mig_item].[Import_GetByItemSet]...';
GO
CREATE procedure [mig_item].[Import_GetByItemSet] (
@setIdlist varchar(max)
, @includeDescendants bit
, @includeAncestors bit
, @limit bigint
, @jobId int
)
as
declare @setIds table([SetId] bigint, primary key ([SetId]))
insert @setIds select convert(bigint, [Value]) from [mig_utility].[ParseDelimitedString](@setIdlist, ',')
declare @itemIds [mig_item].[ItemIdList]
insert @itemIds
select top(@limit)
A.[ItemId]
from
[mig_valid].[Item] as A with (NoLock)
inner join [mig_item].[ItemSet] as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
inner join @setIds as C
on
B.[SetId] = C.[SetId]
inner join [mig_item].[ExportItem] as D
on
D.[ItemId] = A.[ItemId]
and D.[Result] = 0 -- 0:Exported
group by
A.[ItemId]
exec [mig_item].[_Import_CalculateLevels] @jobId, @itemIds, @includeDescendants, @includeAncestors
GO
PRINT N'Creating Procedure [mig_item].[Import_GetByEvent]...';
GO
CREATE procedure [mig_item].[Import_GetByEvent] (
@userEventlist varchar(max)
, @systemEventlist varchar(max)
, @includeDescendants bit
, @includeAncestors bit
, @limit bigint
, @jobId int
)
as
declare @delimiter varchar(10) = '<>##.##<>'
declare @count int = 0
declare @str varchar(max)
declare @xml xml
declare @entityID uniqueidentifier
declare @eventID uniqueidentifier
declare @message varchar(1024)
declare @engine tinyint
declare @itemIds [mig_item].[ItemIdList]
-- User events
declare csrUser cursor for select [Value] from [mig_utility].[ParseDelimitedString](@userEventlist, @delimiter)
open csrUser
fetch next from csrUser into @str
while @@fetch_status = 0
begin
set @xml = convert(xml, @str)
set @entityID = @xml.value('(*/EntityID)[1]', 'uniqueidentifier')
set @eventID = @xml.value('(*/EventID)[1]', 'uniqueidentifier')
set @engine = @xml.value('(*/Engine)[1]', 'tinyint')
insert @itemIds
select
A.[ItemId]
from
[mig_valid].[Item] as A with (NoLock)
inner join [mig_valid].[EventUser] as B
on
A.[ItemId] = B.[ItemId] and B.[Engine] = @engine and B.[EventID] = @eventID
inner join [mig_item].[ExportKey] as C with (NoLock)
on
A.[ItemId] = C.[ItemId] and C.[EntityID] = @entityID
left outer join @itemIds as D
on
A.[ItemId] = D.[ItemId]
where
D.[ItemId] is null
group by
A.[ItemId]
set @count = @count + @@rowcount
fetch next from csrUser into @str
end
close csrUser
deallocate csrUser
-- System events
declare csrSystem cursor for select [Value] from [mig_utility].[ParseDelimitedString](@userEventlist, @delimiter)
open csrSystem
fetch next from csrSystem into @str
while @@fetch_status = 0
begin
set @xml = convert(xml, @str)
set @entityID = @xml.value('(*/EntityID)[1]', 'uniqueidentifier')
set @message = @xml.value('(*/Message)[1]', 'varchar(1024)')
set @engine = @xml.value('(*/Engine)[1]', 'tinyint')
insert @itemIds
select
A.[ItemId]
from
[mig_valid].[Item] as A with (NoLock)
inner join [mig_valid].[EventSystem] as B
on
A.[ItemId] = B.[ItemId] and B.[Engine] = @engine and B.[Message] = @message
inner join [mig_item].[ExportKey] as C with (NoLock)
on
A.[ItemId] = C.[ItemId] and C.[EntityID] = @entityID
left outer join @itemIds as D
on
A.[ItemId] = D.[ItemId]
where
D.[ItemId] is null
group by
A.[ItemId]
set @count = @count + @@rowcount
fetch next from csrSystem into @str
end
close csrSystem
deallocate csrSystem
-- If too many, delete randomly in an effort to get some of all event types
if @count > @limit
begin
with del as (
select top(@count - @limit)
[ItemId]
from
@itemIds
order by
[ItemId] % 500
)
delete from @itemIds
from
@itemIds as A
inner join del as B
on
A.[ItemId] = B.[ItemId]
end
exec [mig_item].[_Import_CalculateLevels] @jobId, @itemIds, @includeDescendants, @includeAncestors
GO
PRINT N'Creating Procedure [mig_item].[Import_GetByEntityID]...';
GO
create procedure [mig_item].[Import_GetByEntityID] (
@entityIDlist varchar(max) = null
, @key varchar(512) = null
, @mode tinyint
, @includeDescendants bit
, @includeAncestors bit
, @limit bigint
, @jobId int
)
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 convert(uniqueidentifier, [Value]) from [mig_utility].[ParseDelimitedString](@entityIDlist, ',')
end
declare @itemIds [mig_item].[ItemIdList]
insert @itemIds
select top(@limit)
A.[ItemId]
from
[mig_valid].[Item] as A with (NoLock)
inner join [mig_item].[MainKey] as B with (NoLock)
on
A.[KeyId] = B.[KeyId]
and (@key is null or B.[KeyValue] like @key)
inner join [mig_item].[ExportItem] as C with (NoLock)
on
A.[ItemId] = C.[ItemId] and C.[Result] = 0 -- 0: Exported
inner join [mig_item].[ExportKey] as D with (NoLock)
on
A.[ItemId] = D.[ItemId]
inner join @entityIDs as E
on
D.[EntityID] = E.[ID]
left outer join [mig_valid].[ImportItem] as F with (NoLock)
on
A.[ItemId] = F.[ItemId]
where
(@mode = 0) -- All
or (@mode = 1 and F.[Result] < 2) -- Rejects, 2: Success
or (@mode = 2 and F.[Result] is null) -- Difference
exec [mig_item].[_Import_CalculateLevels] @jobId, @itemIds, @includeDescendants, @includeAncestors
GO
PRINT N'Creating Procedure [mig_item].[Export_Item_Insert]...';
GO
create procedure [mig_item].[Export_Item_Insert]
@itemId bigint
, @jobId int
, @jobInstanceId int
, @exportID uniqueidentifier
, @result tinyint
, @businessEntityName varchar(50)
, @partitionValue varchar(50)
, @keyValue varchar(512)
, @sourceKey varchar(512)
, @sourceXml varchar(max) = null
, @exportXml varchar(max) = null
, @maxDeadlockRetries int
, @compress bit
as
declare @step tinyint = 0 -- Export
-- Mark previous export for garbage collection
exec [mig_garbage].[_InvalidateItem] @itemId, @step, @jobId, @jobInstanceId
-- Get KeyId
declare @keyId bigint
declare @duplicate bit = 0
exec [mig_item].[_GetKeyId] @businessEntityName, @keyValue, @maxDeadlockRetries, @keyId output
-- Check for duplicate key
select
@duplicate = 1
from
[mig_valid].[Item]
where
[KeyId] = @keyId
and [ItemId] <> @itemId -- Avoid finding yourself !
if 1 = @duplicate
begin
-- Insert ExportID in @keyValue and get a new KeyId
declare @idTxt varchar(50) = lower(convert(varchar(50), @exportID))
declare @keyXml xml = convert(xml, @keyValue)
set @keyXml.modify('insert as last into (/*)[1]')
set @keyValue = convert(varchar(512), @keyXml)
exec [mig_item].[_GetKeyId] @businessEntityName, @keyValue, @maxDeadlockRetries, @keyId output
-- Override @result
set @result = 2 -- Rejected
end
if 0 = @@error
begin
-------------------------------------------------------------
-- Clear any existing Xml from import step for this item
-------------------------------------------------------------
update [mig_item].[TargetXml]
set
[_XmlCompressed] = null
, [_XmlUncompressed] = null
, [JobId] = null
, [JobInstanceId] = null
, [CreationDate] = sysdatetime()
where
[ItemId] = @itemId
-------------------------------------------
-- Insert or update Item for this iteration
-------------------------------------------
update [mig_item].[Item]
set
[JobId] = @jobId
, [JobInstanceId] = @jobInstanceId
, [KeyId] = @keyId
, [PartitionValue] = @partitionValue
where
[ItemId] = @itemId
if 0 = @@rowCount
begin
insert [mig_item].[Item] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [KeyId]
, [PartitionValue]
) values (
@itemId
, @jobId
, @jobInstanceId
, @keyId
, @partitionValue
)
end
if 0 = @@error
begin
-------------------------------------------------
-- Insert or update ExportItem for this iteration
-------------------------------------------------
declare @commit bigint
declare @cleanWorkspace bit
select top(1)
@commit = [Commit]
, @cleanWorkspace = [CleanWorkspace]
from
[mig_director].[Engine]
where
[ProjectType] = 'Source'
update [mig_item].[ExportItem]
set
[JobId] = @jobId
, [JobInstanceId] = @jobInstanceId
, [Result] = @result
, [SourceKey] = @sourceKey
, [Commit] = isnull(@commit, 0)
, [CleanWorkspace] = isnull(@cleanWorkspace, 0)
where
[ItemId] = @itemId
if 0 = @@rowcount
begin
insert [mig_item].[ExportItem] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [Result]
, [SourceKey]
, [Commit]
, [CleanWorkspace]
) values (
@itemId
, @jobId
, @jobInstanceId
, @result
, @sourceKey
, isnull(@commit, 0)
, isnull(@cleanWorkspace, 0)
)
end
if 0 = @@error and @sourceXml is not null
begin
-------------------------------------------------
-- Insert or update SourceXml for this iteration
-------------------------------------------------
update [mig_item].[SourceXml]
set
[JobId] = @jobId
, [JobInstanceId] = @jobInstanceId
, [_XmlCompressed] = case @compress when 1 then compress(@sourceXml) else null end
, [_XmlUncompressed] = case @compress when 0 then @sourceXml else null end
, [CreationDate] = sysdatetime()
where
[ItemId] = @itemId
if 0 = @@rowCount
begin
insert [mig_item].[SourceXml] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [_XmlCompressed]
, [_XmlUncompressed]
) values (
@itemId
, @jobId
, @jobInstanceId
, case @compress when 1 then compress(@sourceXml) else null end
, case @compress when 0 then @sourceXml else null end
)
end
if 0 = @@error and @exportXml is not null and @result <> 1 -- Dropped
begin
-------------------------------------------------
-- Insert or update ExportXml for this iteration
-------------------------------------------------
update [mig_item].[ExportXml]
set
[JobId] = @jobId
, [JobInstanceId] = @jobInstanceId
, [_XmlCompressed] = case @compress when 1 then compress(@exportXml) else null end
, [_XmlUncompressed] = case @compress when 0 then @exportXml else null end
, [CreationDate] = sysdatetime()
where
[ItemId] = @itemId
if 0 = @@rowCount
begin
insert [mig_item].[ExportXml] (
[ItemId]
, [JobId]
, [JobInstanceId]
, [_XmlCompressed]
, [_XmlUncompressed]
) values (
@itemId
, @jobId
, @jobInstanceId
, case @compress when 1 then compress(@exportXml) else null end
, case @compress when 0 then @exportXml else null end
)
end
end
end
end
end
select @duplicate
GO
PRINT N'Creating Procedure [mig_item].[Export_GetByItemSet]...';
GO
CREATE procedure [mig_item].[Export_GetByItemSet](
@setId bigint
, @limit bigint
, @jobId int
)
as
select top(@limit)
A.[ExportID]
, A.[EntityID]
, A.[ItemId]
, A.[BreakFields]
into #itemsToExport
from
[mig_item].[ExportKey] as A with (NoLock)
inner join [mig_item].[ItemSet] as B with (NoLock)
on A.[ItemId] = B.[ItemId] and B.[SetId] = @setId
left outer join [mig_item].[Processed] as Cur with (NoLock)
on A.[ItemId] = Cur.[ItemId] and Cur.[JobId] = @jobId
where
Cur.ItemId is null
exec [mig_item].[_Export_CountItems] @jobId
GO
PRINT N'Creating Procedure [mig_item].[Export_GetByEvent]...';
GO
CREATE procedure [mig_item].[Export_GetByEvent](
@userEventlist varchar(max)
, @systemEventlist varchar(max)
, @limit bigint
, @jobId int
)
as
set @limit = @limit - (select count(*) from [mig_item].[Processed] with (NoLock) where [JobId] = @jobId)
declare @delimiter varchar(10) = '<>##.##<>'
declare @count int = 0
declare @str varchar(max)
declare @xml xml
declare @entityID uniqueidentifier
declare @eventID uniqueidentifier
declare @message varchar(1024)
declare @engine tinyint
create table #itemsToExport (
[ExportID] uniqueidentifier
, [EntityID] uniqueidentifier
, [ItemId] bigint
, [BreakFields] varchar(512)
)
create unique nonclustered index __IX_itemsToConvert on #itemsToExport ([ItemId] asc)
-- User events
declare csrUser cursor for select [Value] from [mig_utility].[ParseDelimitedString](@userEventlist, @delimiter)
open csrUser
fetch next from csrUser into @str
while @@fetch_status = 0
begin
set @xml = convert(xml, @str)
set @entityID = @xml.value('(*/EntityID)[1]', 'uniqueidentifier')
set @eventID = @xml.value('(*/EventID)[1]', 'uniqueidentifier')
set @engine = @xml.value('(*/Engine)[1]', 'tinyint')
insert #itemsToExport
select
A.[ExportID]
, A.[EntityID]
, A.[ItemId]
, A.[BreakFields]
from
[mig_item].[ExportKey] as A with (NoLock)
inner join [mig_valid].[EventUser] as B
on A.[ItemId] = B.[ItemId] and B.[Engine] = @engine and B.[EventID] = @eventID
left outer join #itemsToExport as C
on A.[ItemId] = C.[ItemId]
left outer join [mig_item].[Processed] as D with (NoLock)
on A.[ItemId] = D.[ItemId] and D.[JobId] = @jobId
where
A.[EntityID] = @entityID and C.[ItemId] is null and D.[ItemId] is null
group by
A.[ExportID]
, A.[EntityID]
, A.[ItemId]
, A.[BreakFields]
set @count = @count + @@rowcount
fetch next from csrUser into @str
end
close csrUser
deallocate csrUser
-- System events
declare csrSystem cursor for select [Value] from [mig_utility].[ParseDelimitedString](@userEventlist, @delimiter)
open csrSystem
fetch next from csrSystem into @str
while @@fetch_status = 0
begin
set @xml = convert(xml, @str)
set @entityID = @xml.value('(*/EntityID)[1]', 'uniqueidentifier')
set @message = @xml.value('(*/Message)[1]', 'varchar(1024)')
set @engine = @xml.value('(*/Engine)[1]', 'tinyint')
insert #itemsToExport
select
A.[ExportID]
, A.[EntityID]
, A.[ItemId]
, A.[BreakFields]
from
[mig_item].[ExportKey] as A with (NoLock)
inner join [mig_valid].[EventSystem] as B
on A.[ItemId] = B.[ItemId] and B.[Engine] = @engine and B.[Message] = @message
left outer join #itemsToExport as C
on A.[ItemId] = C.[ItemId]
left outer join [mig_item].[Processed] as D with (NoLock)
on A.[ItemId] = D.[ItemId] and D.[JobId] = @jobId
where
A.[EntityID] = @entityID and C.[ItemId] is null and D.[ItemId] is null
group by
A.[ExportID]
, A.[EntityID]
, A.[ItemId]
, A.[BreakFields]
set @count = @count + @@rowcount
fetch next from csrSystem into @str
end
close csrSystem
deallocate csrSystem
-- If too many, delete randomly in an effort to get some of all event types
if @count > @limit
begin
with del as (
select top(@count - @limit)
[ItemId]
from
#itemsToExport
order by
[ItemId] % 500
)
delete from #itemsToExport
from
#itemsToExport as A
inner join del as B
on A.[ItemId] = B.[ItemId]
end
exec [mig_item].[_Export_CountItems] @jobId
GO
PRINT N'Creating Procedure [mig_item].[Export_GetByEntityID]...';
GO
CREATE procedure [mig_item].[Export_GetByEntityID](
@entityID uniqueidentifier
, @limit bigint
, @key varchar(512) = null
, @mode tinyint
, @jobId int
)
as
set @limit = @limit - (select count(*) from [mig_item].[Processed] with (NoLock) where [JobId] = @jobId)
select top(@limit)
A.[ExportID]
, A.[EntityID]
, A.[ItemId]
, A.[BreakFields]
into #itemsToExport
from
[mig_item].[ExportKey] as A with (NoLock)
left outer join [mig_item].[ExportItem] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
left outer join [mig_item].[Processed] as C with (NoLock)
on A.[ItemId] = C.[ItemId] and C.[JobId] = @jobId
where
(A.[EntityID] = @entityID)
and (@key is null or A.[BreakFields] like @key)
and (
(@mode = 0) -- All
or (@mode = 1 and B.[Result] = 2) -- Rejects, 2: Reject
or (@mode = 2 and B.[Result] is null) -- Difference
)
and (C.[ItemId] is null)
exec [mig_item].[_Export_CountItems] @jobId
GO
PRINT N'Creating Procedure [mig_item].[Import_Item_Start]...';
GO
CREATE PROCEDURE [mig_item].[Import_Item_Start]
(
@itemId bigint
, @jobId int
, @jobInstanceId int
)
AS
declare @step tinyint = 1 -- Import
exec [mig_garbage].[_InvalidateItem] @itemId, @step, @jobId, @jobInstanceId
GO
PRINT N'Creating Procedure [mig_garbage].[_SetItemSetObsolete]...';
GO
CREATE procedure [mig_garbage].[_SetItemSetObsolete] (
@itemSet [mig_garbage].[InvalidateSet] readonly
)
as
declare @time datetime2(7) = sysdatetime()
update [mig_item].[Id]
set
[InvalidatedTime] = @time
from
[mig_item].[Id] as A
inner join @itemSet as B
on A.[ItemId] = B.[ItemId]
exec [mig_garbage].[_InvalidateSet] @itemSet
GO
PRINT N'Creating Procedure [mig_item].[_Export_EntitySynchronizeItems]...';
GO
CREATE procedure [mig_item].[_Export_EntitySynchronizeItems]
@jobId int
, @jobInstanceId int
, @entityID uniqueidentifier
, @sync [mig_item].[ExportItemList] readonly
, @newCount int output
, @obsoleteCount int output
as
--------------------------------------------------------------------------------------------------------------
-- Process New items
--------------------------------------------------------------------------------------------------------------
declare @newId table([ItemId] bigint)
declare @newExportID table([ExportID] uniqueidentifier, [BreakFields] varchar(512))
declare @time datetime2(7) = sysdatetime()
-- Determine new items
insert @newExportID
select
A.[ExportID]
, A.[BreakFields]
from
@sync as A
left outer join [mig_item].[ExportKey] as B
on A.[ExportID] = B.[ExportID]
where
B.[BreakFields] is null
-- Generate ItemIds
insert [mig_item].[Id] with (RowLock) ([CreationDate])
output inserted.[ItemId] into @newId
select
@time
from @newExportID
-- Match ExportIDs and generated ItemIds and insert on ExportKey
;with
itemId ([RowId], [ItemId]) as (
select
row_number() over(order by [ItemId])
, [ItemId]
from @newId
)
, exportID ([RowId], [ExportId], [BreakFields]) as (
select
row_number() over(order by [ExportId])
, [ExportId]
, [BreakFields]
from @newExportID
)
insert [mig_item].[ExportKey] with (RowLock) (
ExportID
, ItemId
, EntityID
, BreakFields
, JobId
, JobInstanceId
)
select
B.[ExportID]
, A.[ItemId]
, @entityID
, B.[BreakFields]
, @jobId
, @jobInstanceId
from
itemId as A
inner join exportID as B on A.[RowId] = B.[RowId]
-- Return the number of new items
set @newCount = @@rowcount
--------------------------------------------------------------------------------------------------------------
-- Process Obsolete items
--------------------------------------------------------------------------------------------------------------
declare @obsoleteItems [mig_garbage].[InvalidateSet]
delete from [mig_item].[ExportKey] with (RowLock)
output Deleted.[ItemId] into @obsoleteItems([ItemId])
from
[mig_item].[ExportKey] as A
left outer join @sync as B
on A.[ExportID] = B.[ExportID]
where
A.[EntityID] = @entityID
and B.[ExportID] is null
-- Return the number of obsolete items
set @obsoleteCount = @@rowcount
-- Mark as garbage
exec [mig_garbage].[_SetItemSetObsolete] @obsoleteItems
GO
PRINT N'Creating Procedure [mig_item].[Export_EntitySynchronizeItems]...';
GO
CREATE procedure [mig_item].[Export_EntitySynchronizeItems]
@jobId int
, @jobInstanceId int
, @entityID uniqueidentifier
, @exportDb varchar(50)
, @totalCount int output
, @newCount int output
, @obsoleteCount int output
as
declare @now datetime2(7) = sysdatetime()
---------------------------------------------------------------------------------------------------------------
-- Dynamically call the Synchronize procedure in the Export DB and retrieve the sync results in table var @sync
---------------------------------------------------------------------------------------------------------------
declare @sync [mig_item].[ExportItemList]
declare @sql nvarchar(256) =
'exec ' + @exportDb + '.[mig_director].[EntityKeyList] ''' + convert(nvarchar(50), @entityID) + ''''
-- Execute the call
insert into @sync exec (@sql)
-- Return the total count
set @totalCount = @@rowcount
exec [mig_item].[_Export_EntitySynchronizeItems] @jobId, @jobInstanceId, @entityID, @sync, @newCount output, @obsoleteCount output
update [mig_director].[ExportEntity]
set
[LastSynchronizeTime] = @now
where
[EntityId] = @entityID
GO
PRINT N'Creating Procedure [mig_item].[Export_EntityClearAllItems]...';
GO
CREATE procedure [mig_item].[Export_EntityClearAllItems]
@jobId int
, @jobInstanceId int
, @entityID uniqueidentifier
as
declare @sync [mig_item].[ExportItemList] -- Empty synch list causes cleanup of all items for EntityID
declare @newCount int = 0
declare @obsoleteCount int = 0
exec [mig_item].[_Export_EntitySynchronizeItems] @jobId, @jobInstanceId, @entityID, @sync, @newCount output, @obsoleteCount output
select @obsoleteCount
GO
PRINT N'Update complete.';
GO