/****** Object: Schema [mig_config] Script Date: 2020-09-30 20:37:36 ******/
CREATE SCHEMA [mig_config]
GO
/****** Object: Schema [mig_director] Script Date: 2020-09-30 20:37:36 ******/
CREATE SCHEMA [mig_director]
GO
/****** Object: Schema [mig_profile] Script Date: 2020-09-30 20:37:36 ******/
CREATE SCHEMA [mig_profile]
GO
/****** Object: Schema [mig_public] Script Date: 2020-09-30 20:37:36 ******/
CREATE SCHEMA [mig_public]
GO
/****** Object: Schema [mig_schema] Script Date: 2020-09-30 20:37:36 ******/
CREATE SCHEMA [mig_schema]
GO
/****** Object: Schema [mig_tracking] Script Date: 2020-09-30 20:37:36 ******/
CREATE SCHEMA [mig_tracking]
GO
/****** Object: UserDefinedFunction [mig_config].[ParseCS] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [mig_config].[ParseCS]
(
@cSFormat varchar(max)
, @serverName varchar(128)
, @dbName varchar(128)
)
returns varchar(max)
as
begin
if @csFormat is null
begin
select @csFormat = [ParameterValue]
from [mig_config].[MasterParameter]
where [Parametername] = 'CSFormat'
end
return replace(replace(@cSFormat, '@server', @serverName), '@database', @dbName)
end
GO
/****** Object: UserDefinedFunction [mig_config].[BuildDataSource] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [mig_config].[BuildDataSource]
(
@qualifiedName nvarchar(256) = null
, @machineName nvarchar(128) = null
, @instanceName nvarchar(128) = null
, @port int = null
)
returns nvarchar(max)
with SchemaBinding
as
begin
return isnull(@qualifiedName, @machineName) + isnull('\' + @instanceName, '') + isnull(',' + convert(varchar(5), @port), '')
end
GO
/****** Object: Table [mig_director].[Server] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[Server](
[ServerId] [int] IDENTITY(1,1) NOT NULL,
[MachineName] [varchar](50) NOT NULL,
[QualifiedName] [varchar](50) NULL,
[IP] [varchar](50) NOT NULL,
[InstanceName] [varchar](50) NULL,
[Port] [int] NULL,
[DataSource] AS ([mig_config].[BuildDataSource]([QualifiedName],[MachineName],[InstanceName],[port])),
[Description] [varchar](50) NOT NULL,
[DefaultTrackPath] [varchar](max) NULL,
[ServiceCheckInTime] [datetime] NULL,
CONSTRAINT [PK_Server] PRIMARY KEY CLUSTERED
(
[ServerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_director].[Project] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[Project](
[ProjectID] UNIQUEIDENTIFIER NOT NULL,
[ProjectMode] INT CONSTRAINT [DF_Project_ProjectMode] DEFAULT ((1)) NULL,
[ProjectState] INT CONSTRAINT [DF_Project_ProjectState] DEFAULT ((0)) NOT NULL,
[ProjectDB_ServerId] INT NULL,
[ProjectDB_Name] VARCHAR (128) NULL,
[ProjectDB_CSFormat] VARCHAR (MAX) NULL,
[SourceEngineID] UNIQUEIDENTIFIER NULL,
[TargetEngineID] UNIQUEIDENTIFIER NULL,
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_director].[Track] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[Track](
[TrackID] [uniqueidentifier] NOT NULL,
[ServerId] [int] NOT NULL,
[NumberOnServer] [int] NOT NULL,
[MigrationDB_ServerId] [int] NULL,
[MigrationDB_Name] [varchar](128) NULL,
[MigrationDB_CSFormat] [varchar](max) NULL,
[StagingDB_ServerId] [int] NULL,
[StagingDB_Name] [varchar](128) NOT NULL,
[StagingDB_CSFormat] [varchar](max) NULL,
[ActiveTrackUsage] [uniqueidentifier] NULL,
[ArchiveToTrack] [uniqueidentifier] NULL,
[IsArchive] [bit] NOT NULL,
CONSTRAINT [PK_Track] PRIMARY KEY CLUSTERED
(
[TrackID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_director].[TrackUsage] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[TrackUsage](
[TrackUsageID] [uniqueidentifier] NOT NULL,
[ProjectID] [uniqueidentifier] NOT NULL,
[Description] [varchar](50) NOT NULL,
[Publish] [bit] NOT NULL,
[ExpireDateOnWeb] [date] NULL,
CONSTRAINT [PK_TrackUsage] PRIMARY KEY CLUSTERED
(
[TrackUsageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: View [mig_tracking].[ProjectTrack] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [mig_tracking].[ProjectTrack]
AS
select
C.[TrackID]
, B.[ProjectID]
, B.[Description] as [TrackName]
, mig_config.ParseCS(
C.[MigrationDB_CSFormat]
, isnull(F.[DataSource], D.[DataSource])
, C.[MigrationDB_Name]
) as [MigrationCS]
, mig_config.ParseCS(
E.[ProjectDB_CSFormat]
, isnull(G.[DataSource], D.[DataSource])
, E.[ProjectDB_Name]
) as [ProjectCS]
, D.[ServerId]
, C.[NumberOnServer]
from
[mig_director].[Project] as A
inner join [mig_director].[TrackUsage] as B on B.[ProjectID] = A.[ProjectID]
inner join [mig_director].[Track] as C on B.[TrackUsageID] = C.[ActiveTrackUsage]
inner join [mig_director].[Server] as D on C.[ServerId] = D.[ServerId]
inner join [mig_director].[Project] as E on E.[ProjectID] = B.[ProjectID]
left outer join [mig_director].[Server] as F on C.[MigrationDB_ServerId] = F.[ServerId]
left outer join [mig_director].[Server] as G on E.[ProjectDB_ServerId] = G.[ServerId]
where
B.[Publish] = 1
and (B.[ExpireDateOnWeb] > GetDate() or B.[ExpireDateOnWeb] is null)
GO
/****** Object: View [mig_tracking].[Project] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [mig_tracking].[Project]
AS
select
A.[ProjectID]
, A.[ProjectMode]
, A.[ProjectState]
, mig_config.[ParseCS](
A.[ProjectDB_CSFormat]
, isnull(B.[DataSource], lower(cast(serverproperty('MachineName') as nvarchar(50))))
, A.[ProjectDB_Name]) as [ConnectionString]
from
[mig_director].[Project] as A
left outer join [mig_director].[Server] as B
on
A.[ProjectDB_ServerId] = B.[ServerId]
GO
/****** Object: Table [mig_config].[AdditionalSetting] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_config].[AdditionalSetting](
[Name] [varchar](256) NOT NULL,
[Type] [tinyint] NOT NULL,
[Value] [varchar](max) NOT NULL,
CONSTRAINT [PK_AdditionalSetting] PRIMARY KEY CLUSTERED
(
[Name] ASC,
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_config].[EventReceiver] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_config].[EventReceiver](
[ReceiverCode] [int] NOT NULL,
[StringValue] [nvarchar](25) NOT NULL,
[DefaultWorkLevel] [int] NOT NULL,
CONSTRAINT [PK_ReceiverEnum] PRIMARY KEY CLUSTERED
(
[ReceiverCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [mig_config].[MasterParameter] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_config].[MasterParameter](
[ParameterName] [varchar](50) NOT NULL,
[ParameterValue] [varchar](max) NOT NULL,
CONSTRAINT [PK_MasterParameter] PRIMARY KEY CLUSTERED
(
[ParameterName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_director].[Concurrency] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[Concurrency](
[Tag] [varchar](256) NOT NULL,
[Dynamic] [bit] NOT NULL,
[Limit] [smallint] NOT NULL,
CONSTRAINT [PK_Concurrency] PRIMARY KEY CLUSTERED
(
[Tag] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [mig_director].[DirectorOption] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[DirectorOption](
[OptionName] [varchar](50) NOT NULL,
[OptionArea] [varchar](50) NOT NULL,
[OptionValue] [varchar](max) NOT NULL,
[OptionType] [varchar](10) NOT NULL,
[CanOverride] [bit] NOT NULL,
[Description] [varchar](max) NOT NULL,
CONSTRAINT [PK_Options] PRIMARY KEY CLUSTERED
(
[OptionName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_director].[Engine] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[Engine](
[EngineID] [uniqueidentifier] NOT NULL,
[EngineCode] [tinyint] NOT NULL,
[EngineName] [varchar](50) NOT NULL,
[LoadLibrary] [varchar](50) NOT NULL,
[ZipFile] [varbinary](max) NULL,
[DeployedAt] DATETIME2 NULL,
[DeployedBy] NVARCHAR(256) NULL,
CONSTRAINT [PK_Engine] PRIMARY KEY CLUSTERED
(
[EngineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [mig_director].[Runtime] (
[RuntimeID] UNIQUEIDENTIFIER NOT NULL,
[Version] VARCHAR (50) NOT NULL,
[ZipFile] VARBINARY (MAX) NOT NULL,
[IsActive] BIT CONSTRAINT [DF_Runtime_IsActive] DEFAULT 0 NOT NULL,
[DeployedAt] DATETIME2 NOT NULL,
[DeployedBy] NVARCHAR(256) NOT NULL,
CONSTRAINT [PK_RuntimeID] PRIMARY KEY CLUSTERED ([RuntimeID] ASC)
);
CREATE UNIQUE NONCLUSTERED INDEX [IX_Runtime_Version] ON [mig_director].[Runtime]([Version] DESC);
ALTER TABLE [mig_director].[Runtime] ADD CONSTRAINT [DF_Runtime_RuntimeID] DEFAULT (newid()) FOR [RuntimeID];
GO
/****** Object: Table [mig_director].[Folder] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[Folder](
[FolderId] [varchar](25) NOT NULL,
[Label] [varchar](100) NOT NULL,
[Seq] [int] NOT NULL,
[DefaultPath] [varchar](max) NOT NULL,
[Hint] [varchar](max) NOT NULL,
CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
(
[FolderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_director].[JobType] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[JobType](
[JobTypeId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[ClassNamespace] [varchar](256) NOT NULL,
[ClassName] [varchar](256) NOT NULL,
[CanCancel] [bit] NOT NULL,
[InitialBatchSize] [int] NULL,
[RunOutProcess] [bit] NOT NULL,
[HardCancel] [bit] NOT NULL,
[Priority] [tinyint] NOT NULL,
CONSTRAINT [PK_JobType] PRIMARY KEY CLUSTERED
(
[JobTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [mig_director].[TrackFolder] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[TrackFolder](
[TrackID] [uniqueidentifier] NOT NULL,
[FolderId] [varchar](25) NOT NULL,
[Path] [varchar](max) NOT NULL,
CONSTRAINT [PK_TrackFolder] PRIMARY KEY CLUSTERED
(
[TrackID] ASC,
[FolderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_director].[TrackProcess] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[TrackProcess](
[TrackID] [uniqueidentifier] NOT NULL,
[ProcessId] [int] NOT NULL,
[State] [int] NOT NULL,
[StartRequested] [varchar](50) NULL,
[StopRequested] [varchar](50) NULL,
[Error] [varchar](max) NULL,
[ProcessCheckIn] [datetime2](7) NULL,
CONSTRAINT [PK_TrackProcess] PRIMARY KEY CLUSTERED
(
[TrackID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_director].[TrackUserProfile] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_director].[TrackUserProfile](
[TrackID] [uniqueidentifier] NOT NULL,
[UserId] [varchar](50) NOT NULL,
[Profile] [varchar](max) NOT NULL,
[CreationDate] [datetime2](7) NOT NULL,
[ModificationDate] [datetime2](7) NULL,
CONSTRAINT [PK_TrackUserProfile] PRIMARY KEY CLUSTERED
(
[TrackID] ASC,
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [mig_profile].[UserProfile] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_profile].[UserProfile](
[UserId] [varchar](50) NOT NULL,
[Profile] [varchar](max) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[ModificationDate] [datetime] NULL,
CONSTRAINT [PK_UserProfile] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_Engine_NameAndType] Script Date: 2020-09-30 20:37:36 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Engine_NameAndType] ON [mig_director].[Engine]
(
[EngineName] ASC,
[EngineCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_Project_ProjectState] Script Date: 2020-09-30 20:37:36 ******/
CREATE NONCLUSTERED INDEX [IX_Project_ProjectState] ON [mig_director].[Project]
(
[ProjectState] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_Server] Script Date: 2020-09-30 20:37:36 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Server] ON [mig_director].[Server]
(
[MachineName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_Track_UniqueNumber] Script Date: 2020-09-30 20:37:36 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Track_UniqueNumber] ON [mig_director].[Track]
(
[ServerId] ASC,
[NumberOnServer] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_TrackUsage] Script Date: 2020-09-30 20:37:36 ******/
CREATE NONCLUSTERED INDEX [IX_TrackUsage] ON [mig_director].[TrackUsage]
(
[ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [mig_director].[DirectorOption] ADD CONSTRAINT [DF_Options_CanOverride] DEFAULT ((1)) FOR [CanOverride]
GO
ALTER TABLE [mig_director].[DirectorOption] ADD CONSTRAINT [DF_Options_Description] DEFAULT ('') FOR [Description]
GO
ALTER TABLE [mig_director].[Track] ADD CONSTRAINT [DF_Track_TrackID] DEFAULT (newid()) FOR [TrackID]
GO
ALTER TABLE [mig_director].[Track] ADD CONSTRAINT [DF_Track_Frozen] DEFAULT ((0)) FOR [IsArchive]
GO
ALTER TABLE [mig_director].[TrackProcess] ADD CONSTRAINT [DF_TrackProcess_ProcessId] DEFAULT ((-1)) FOR [ProcessId]
GO
ALTER TABLE [mig_director].[TrackProcess] ADD CONSTRAINT [DF_TrackProcess_ProcessState] DEFAULT ((0)) FOR [State]
GO
ALTER TABLE [mig_director].[TrackUsage] ADD CONSTRAINT [DF_TrackUsage_TrackUsageID] DEFAULT (newid()) FOR [TrackUsageID]
GO
ALTER TABLE [mig_director].[TrackUsage] ADD CONSTRAINT [DF_TrackUsage_AvailableForDirector] DEFAULT ((1)) FOR [Publish]
GO
ALTER TABLE [mig_director].[TrackUserProfile] ADD CONSTRAINT [DF_TrackUserProfile_CreationDate] DEFAULT (sysdatetime()) FOR [CreationDate]
GO
ALTER TABLE [mig_profile].[UserProfile] ADD CONSTRAINT [DF_UserProfile_Profile] DEFAULT ('') FOR [Profile]
GO
ALTER TABLE [mig_profile].[UserProfile] ADD CONSTRAINT [DF_UserProfile_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
/****** Object: StoredProcedure [mig_config].[AdditionalSetting_Delete] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_config].[AdditionalSetting_Delete]
@name varchar(256)
, @type tinyint
as
delete [mig_config].[AdditionalSetting]
where
[Name] = @name
and [Type] = @type
GO
/****** Object: StoredProcedure [mig_config].[AdditionalSetting_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_config].[AdditionalSetting_List]
as
select
[Name]
, [Type]
, [Value]
from
[mig_config].[AdditionalSetting]
GO
/****** Object: StoredProcedure [mig_config].[AdditionalSetting_Set] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_config].[AdditionalSetting_Set]
@name varchar(256)
, @type tinyint
, @value varchar(max)
as
update [mig_config].[AdditionalSetting]
set
[Value] = @value
where
[Name] = @name
and [Type] = @type
if 0 = @@rowCount
begin
insert [mig_config].[AdditionalSetting] (
[Name]
, [Type]
, [Value]
) values (
@name
, @type
, @value
)
end
GO
/****** Object: StoredProcedure [mig_config].[Config_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_config].[Config_List]
as
exec [mig_config].[Parameter_List]
exec [mig_config].[AdditionalSetting_List]
GO
/****** Object: StoredProcedure [mig_config].[GetEventReceiver] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_config].[GetEventReceiver]
AS
select
ReceiverCode
, StringValue as ReceiverLabel
, DefaultWorkLevel as WorkLevel
from mig_config.EventReceiver
order by ReceiverCode
GO
/****** Object: StoredProcedure [mig_config].[Parameter_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_config].[Parameter_List]
as
select
[ParameterName]
, [ParameterValue]
from
[mig_config].[MasterParameter]
GO
/****** Object: StoredProcedure [mig_config].[Parameter_Set] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_config].[Parameter_Set]
@parameterName varchar(50)
, @parameterValue varchar(max)
as
update [mig_config].[MasterParameter]
set
[ParameterValue] = @parameterValue
where
[ParameterName] = @parameterName
GO
/****** Object: StoredProcedure [mig_director].[_BuildConnectionString] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[_BuildConnectionString]
@serverId int
, @csFormat varchar(max) = null
, @dbName varchar(max)
as
declare @dataSource varchar(max) = (select [DataSource] from [mig_director].[Server] where [ServerId] = @serverId)
select mig_config.ParseCS(@csFormat, @dataSource, @dbName)
GO
/****** Object: StoredProcedure [mig_director].[Concurrency_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_director].[Concurrency_List](
@dynamic bit = null
)
as
select
[Tag]
, [Limit]
, [Dynamic]
from
[mig_director].[Concurrency]
where
@dynamic is null or [Dynamic] = @dynamic
GO
/****** Object: StoredProcedure [mig_director].[Concurrency_Set] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [mig_director].[Concurrency_Set](
@tag varchar(50)
, @limit smallint
)
as
update [mig_director].[Concurrency]
set
[Limit] = @limit
where
[Tag] = @tag
GO
create procedure [mig_director].[Deploy_ListInstances]
@engineID uniqueidentifier
as
select
[EngineCode]
, [EngineName]
, [LoadLibrary]
from
[mig_director].[Engine]
where [EngineID] = @engineID
select
[Name]
, [Type]
, [Value]
from
[mig_config].[AdditionalSetting]
select
D.[TrackID]
, E.[MachineName] as [Server]
, D.[NumberOnServer] as [Number]
, C.[Description] as [Usage]
, B.[ProjectID] as [ProjectID]
, isnull(F.[State], 0) as [TrackState]
from
[mig_director].[Engine] as A
inner join [mig_director].[Project] as B
on
B.[ProjectState] = 1
and ((A.[EngineCode] = 0 and A.[EngineID] = B.[SourceEngineID]) or (A.[EngineCode] = 1 and A.[EngineID] = B.[TargetEngineID]))
inner join [mig_director].[TrackUsage] as C
on
C.[ProjectID] = B.[ProjectID]
and C.[Publish] = 1
inner join [mig_director].[Track] as D
on
D.[ActiveTrackUsage] = C.[TrackUsageID]
and D.[IsArchive] = 0
inner join [mig_director].[Server] as E
on
E.[ServerId] = D.[ServerId]
left outer join [mig_director].[TrackProcess] as F
on
F.[TrackID] = D.[TrackID]
where A.[EngineID] = @engineID
go
CREATE PROCEDURE [mig_director].[Engine_GetZipFile]
@projectID uniqueidentifier = null
, @engineID uniqueidentifier = null
, @engineType tinyint = 0 -- 0: SourceEngine, 1: TargetEngine
, @zipFile varbinary(max) output
, @deployedBy varchar(50) output
, @deployedAt datetime2(7) output
as
begin
if @engineID is null
begin
select
@engineID = case @engineType
when 0 then [SourceEngineID]
else [TargetEngineID]
end
from
[mig_director].[Project]
where
[ProjectID] = @ProjectId
end
select
@zipFile = [ZipFile]
, @deployedBy = [DeployedBy]
, @deployedAt = [DeployedAt]
from
[mig_director].[Engine]
where
[EngineID] = @engineID
end
GO
CREATE PROCEDURE [mig_director].[Engine_UpdateFiles]
@engineID uniqueidentifier
, @zipFile varbinary(max)
, @user nvarchar(256)
as
begin
update [mig_director].[Engine]
set
[ZipFile] = @zipFile
, [DeployedBy] = @user
, [DeployedAt] = sysdatetime()
where
EngineID = @engineID
end
go
/****** Object: StoredProcedure [mig_director].[Engine_Delete] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Engine_Delete]
@engineID uniqueidentifier
as
declare @usage table([ProjectID] uniqueidentifier)
insert @usage
select
[ProjectID]
from
[mig_director].[Project]
where
[SourceEngineID] = @engineID or [TargetEngineID] = @engineID
if not exists(select top(1) 1 from @usage)
begin
delete [mig_director].[Engine]
where
[EngineID] = @engineID
end
select * from @usage order by 1
GO
/****** Object: StoredProcedure [mig_director].[Engine_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Engine_List]
as
select
[EngineID]
, [EngineName]
, [EngineCode]
, [LoadLibrary]
, [DeployedAt]
, [DeployedBy]
from
[mig_director].[Engine]
order by
[EngineName]
, [EngineCode]
GO
/****** Object: StoredProcedure [mig_director].[Engine_Modify_CreateOrUpdate] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Engine_Modify_CreateOrUpdate]
@engineID uniqueidentifier
, @engineName varchar(50)
, @engineCode tinyint
, @loadLibrary varchar(50)
as
merge into [mig_director].[Engine] as tgt
using (select
@engineID as [EngineID]
, @engineName as [EngineName]
, @engineCode as [EngineCode]
, @loadLibrary as [LoadLibrary]
) as src
on (tgt.[EngineID] = src.EngineID)
when matched then
update set
tgt.[EngineName] = src.EngineName
, tgt.[EngineCode] = src.EngineCode
, tgt.[LoadLibrary] = src.LoadLibrary
when not matched then
insert (
[EngineID]
, [EngineName]
, [EngineCode]
, [LoadLibrary]
) values (
src.[EngineID]
, src.[EngineName]
, src.[EngineCode]
, src.[LoadLibrary]
);
GO
CREATE PROCEDURE [mig_director].[Runtime_AddOrUpdate]
@runtimeID uniqueidentifier
, @version varchar(50)
, @zipFile varbinary(max)
, @user nvarchar(256)
as
begin
merge into [mig_director].[Runtime] as Tgt
using (select @runtimeID, @version, @zipFile) as Src ([RuntimeID], [Version], [ZipFile])
on Tgt.[Version] = Src.[Version]
when matched then
update
set
Tgt.[ZipFile] = Src.[ZipFile]
, Tgt.[DeployedBy] = @user
, Tgt.[DeployedAt] = sysdatetime()
when not matched then
insert (
[RuntimeID]
, [Version]
, [ZipFile]
, [DeployedBy]
, [DeployedAt]
)
values (
Src.[RuntimeID]
, Src.[Version]
, Src.[ZipFile]
, @user
, sysdatetime()
);
-- Mark active, if this is the first upload
if 1 = (select count(*) from [mig_director].[Runtime])
begin
update [mig_director].[Runtime]
set
[IsActive] = 1
where
[RuntimeID] = @runtimeID
end
end
GO
CREATE PROCEDURE [mig_director].[Runtime_Delete]
@runtimeID uniqueidentifier
as
declare @deleted table([IsActive] bit)
delete [mig_director].[Runtime]
output deleted.[IsActive] into @deleted
where
[RuntimeID] = @runtimeID
declare @wasActive bit = (select top(1) [IsActive] from @deleted)
if @wasActive = 1
begin
update [mig_director].[Runtime]
set
[IsActive] = 1
where
[Version] = (select max([Version]) from [mig_director].[Runtime])
end
GO
CREATE PROCEDURE [mig_director].[Runtime_GetZipFile]
@runtimeID uniqueidentifier = null
as
select
[ZipFile]
from
[mig_director].[Runtime]
where
(@runtimeID is not null and [RuntimeID] = @runtimeID)
or (@runtimeID is null and [IsActive] = 1)
GO
CREATE PROCEDURE [mig_director].[Runtime_List]
as
select
[RuntimeID]
, [Version]
, [IsActive]
, [DeployedAt]
, [DeployedBy]
from
[mig_director].[Runtime]
order by
[Version] desc
GO
CREATE PROCEDURE [mig_director].[Runtime_SetActive]
@runtimeID uniqueidentifier
as
set nocount on;
-- Clear current active Runtime
update [mig_director].[Runtime]
set
[IsActive] = case [RuntimeID] when @runtimeID then 1 else 0 end
GO
/****** Object: StoredProcedure [mig_director].[GetProjectCS] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[GetProjectCS]
@projectID uniqueidentifier
, @projectCS nvarchar(256) output
as
select
@projectCS = [ConnectionString]
from
[mig_tracking].[Project]
where
[ProjectID] = @projectID
GO
/****** Object: StoredProcedure [mig_director].[JobType_Get] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[JobType_Get] (
@jobTypeId int
, @name varchar(50) output
, @classNamespace varchar(256) output
, @className varchar(256) output
, @canCancel bit output
, @initialBatchSize int output
, @runOutProcess bit output
, @hardCancel bit output
, @priority tinyint output
)
AS
select
@name = [Name]
, @classNamespace = [ClassNamespace]
, @className = [ClassName]
, @canCancel = [CanCancel]
, @initialBatchSize = [InitialBatchSize]
, @runOutProcess = [RunOutProcess]
, @hardCancel = [HardCancel]
, @priority = [Priority]
from
[mig_director].[JobType]
where
[JobTypeId] = @jobTypeId
GO
/****** Object: StoredProcedure [mig_director].[MasterPulse] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[MasterPulse]
AS
declare @projects table(
[ProjectID] uniqueidentifier
, [ProjectMode] tinyint
)
insert into @projects
select
[ProjectID]
, [ProjectMode]
from
[mig_director].[Project]
where
[ProjectState] = 1 -- Active only
select * from @projects
declare @tracks table(
[TrackID] uniqueidentifier
, [ServerId] int
, [NumberOnServer] int
, [ProcessState] int
, [ProcessId] int
, [ProcessError] nvarchar(max)
, [ProjectID] uniqueidentifier
, [Description] nvarchar(50)
)
insert into @tracks
select
A.[TrackID]
, A.[ServerId]
, A.[NumberOnServer]
, isnull(C.[State], 0) as [ProcessState]
, isnull(C.[ProcessId], 0) as [ProcessId]
, isnull(C.[Error], '') as [ProcessError]
, B.[ProjectId]
, B.[Description]
from
[mig_director].[Track] as A
inner join [mig_director].[TrackUsage] as B
on
A.[ActiveTrackUsage] = B.[TrackUsageID]
left outer join [mig_director].[TrackProcess] as C
on
A.[TrackId] = C.[TrackID]
where
B.[ProjectID] in (select ProjectID from @projects)
and B.[Publish] = 1
and A.[IsArchive] = 0
order by
A.[ServerId]
, A.[NumberOnServer]
, B.[Description]
select * from @tracks
select
[ServerId]
, [MachineName]
, [Description]
, 0 as [ProcessState]
, convert(bigint, datediff(ss, isnull([ServiceCheckInTime], '2000-01-01'), getdate())) as [SecondsSinceCheckIn]
from
[mig_director].[Server]
select
[ParameterName]
, [ParameterValue]
from
[mig_config].[MasterParameter]
where
[ParameterName] <> 'CSFormat'
GO
/****** Object: StoredProcedure [mig_director].[Option_Get] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Option_Get](
@optionName nvarchar(50)
)
AS
select
[OptionValue]
from
[mig_director].[DirectorOption]
where
[OptionName] = @optionName
GO
/****** Object: StoredProcedure [mig_director].[Option_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Option_List]
AS
select distinct
[OptionArea]
from [mig_director].[DirectorOption]
order by
[OptionArea]
select
[OptionName]
, [OptionArea]
, [OptionValue]
, [OptionType]
, [CanOverride]
, [Description]
from [mig_director].[DirectorOption]
order by
[OptionArea]
, [OptionName]
GO
/****** Object: StoredProcedure [mig_director].[Option_Set] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Option_Set]
(
@optionName varchar(50),
@optionValue varchar(max)
)
AS
update [mig_director].[DirectorOption]
set
[OptionValue] = @optionValue
where
[OptionName] = @optionName
GO
/****** Object: StoredProcedure [mig_director].[Project_Delete] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Project_Delete]
@projectID uniqueidentifier
as
declare @usage table([Usage] varchar(max))
insert @usage
select
'Track: ' + D.[Description] + ' - ' + cast(C.[NumberOnServer] as varchar(10))
from
[mig_director].[Project] as A
inner join [mig_director].[TrackUsage] as B on B.[ProjectID] = A.[ProjectID]
inner join [mig_director].[Track] as C on C.[ActiveTrackUsage] = B.[TrackUsageID]
inner join [mig_director].[Server] as D on D.[ServerId] = C.[ServerId]
where
A.[ProjectID] = @projectID
if not exists(select top(1) 1 from @usage)
begin
delete [mig_director].[Project]
where
[ProjectID] = @projectID
end
select * from @usage order by 1
GO
/****** Object: StoredProcedure [mig_director].[Project_GetSourceEngineLibrary] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Project_GetSourceEngineLibrary]
@projectID uniqueidentifier
as
select
B.[LoadLibrary]
from
[mig_director].[Project] as A
left outer join [mig_director].[Engine] as B
on
A.[SourceEngineID] = B.[EngineID]
where
A.[ProjectID] = @projectID
GO
/****** Object: StoredProcedure [mig_director].[Project_GetTargetEngineLibrary] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Project_GetTargetEngineLibrary]
@projectID uniqueidentifier
as
select
B.[LoadLibrary]
from
[mig_director].[Project] as A
left outer join [mig_director].[Engine] as B
on
A.[TargetEngineID] = B.[EngineID]
where
A.[ProjectID] = @projectID
GO
/****** Object: StoredProcedure [mig_director].[Project_Modify_CreateOrUpdate] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Project_Modify_CreateOrUpdate]
@projectID uniqueidentifier
, @projectMode int
, @projectState int
, @projectDB_ServerId int
, @projectDB_Name varchar(50)
, @projectDB_CSFormat varchar(max) = null
, @sourceEngineID uniqueidentifier = null
, @targetEngineID uniqueidentifier = null
as
update [mig_director].[Project]
set
[ProjectMode] = @projectMode
, [ProjectState] = @projectState
, [ProjectDB_ServerId] = @projectDB_ServerId
, [ProjectDB_Name] = @projectDB_Name
, [ProjectDB_CSFormat] = @projectDB_CSFormat
, [SourceEngineID] = isnull(@sourceEngineID, [SourceEngineID])
, [TargetEngineID] = isnull(@targetEngineID, [TargetEngineID])
where
[ProjectID] = @projectID
if 0 = @@rowCount
begin
insert [mig_director].[Project] (
[ProjectID]
, [ProjectMode]
, [ProjectState]
, [ProjectDB_ServerId]
, [ProjectDB_Name]
, [ProjectDB_CSFormat]
, [SourceEngineID]
, [TargetEngineID]
) values (
@projectID
, @projectMode
, @projectState
, @projectDB_ServerId
, @projectDB_Name
, @projectDB_CSFormat
, @sourceEngineID
, @targetEngineID
)
select cast(1 as bit) -- Created
end
else
begin
select cast(0 as bit) -- Updated
end
GO
/****** Object: StoredProcedure [mig_director].[Project_Modify_Read] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Project_Modify_Read]
@projectID uniqueidentifier = null
as
select
[ProjectID]
, [ProjectMode]
, [ProjectState]
, [ProjectDB_ServerId]
, [ProjectDB_Name]
, [ProjectDB_CSFormat]
, [SourceEngineID]
, [TargetEngineID]
from
[mig_director].[Project]
where
[ProjectID] = @projectID
select
[ServerId]
, [MachineName]
, [Description]
from
[mig_director].[Server]
select
[EngineID]
, [EngineCode]
, [EngineName]
from
[mig_director].[Engine]
GO
/****** Object: StoredProcedure [mig_director].[Server_Create] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Server_Create]
@machineName varchar(50)
, @description varchar(50)
, @qualifiedName varchar(50) = null
, @instanceName varchar(50) = null
, @port int = null
, @defaultTrackPath varchar(max) = null
as
declare @id table([ServerId] int)
insert [mig_director].[Server] (
[MachineName]
, [IP]
, [Description]
, [QualifiedName]
, [InstanceName]
, [Port]
, [DefaultTrackPath]
)
output inserted.[ServerId] into @id
values (
@machineName
, ''
, @description
, @qualifiedName
, @instanceName
, @port
, @defaultTrackPath
)
select top(1) [ServerId] from @id
GO
/****** Object: StoredProcedure [mig_director].[Server_Delete] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Server_Delete]
@serverId int
as
declare @usage table([Usage] varchar(max))
insert @usage
select
'Track: ' + A.[Description] + ' - ' + cast(B.[NumberOnServer] as varchar(10))
from
[mig_director].[Server] as A
inner join [mig_director].[Track] as B on A.[ServerId] = B.[ServerId]
where
A.[ServerId] = @serverId
if not exists(select top(1) 1 from @usage)
begin
delete [mig_director].[Server]
where
[ServerId] = @serverId
end
select * from @usage order by 1
GO
/****** Object: StoredProcedure [mig_director].[Server_GetDataSource] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Server_GetDataSource]
@machineName varchar(50) = null
, @qualifiedName varchar(50) = null
, @instanceName varchar(50) = null
, @port int = null
as
select [mig_config].[BuildDataSource](@qualifiedName, @machineName, @instanceName, @port)
GO
/****** Object: StoredProcedure [mig_director].[Server_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Server_List]
as
select
[ServerId]
, [MachineName]
, [IP]
, [QualifiedName]
, [InstanceName]
, [Port]
, [Description]
, [DataSource]
, [DefaultTrackPath]
from
[mig_director].[Server]
order by
[Description]
GO
/****** Object: StoredProcedure [mig_director].[Server_Modify] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Server_Modify]
@serverId int
, @machineName varchar(50)
, @description varchar(50)
, @qualifiedName varchar(50) = null
, @instanceName varchar(50) = null
, @port int = null
, @defaultTrackPath varchar(max) = null
as
update [mig_director].[Server]
set
[MachineName] = @machineName
, [QualifiedName] = @qualifiedName
, [InstanceName] = @instanceName
, [Port] = @port
, [Description] = @description
, [DefaultTrackPath] = @defaultTrackPath
where
[ServerId] = @serverId
GO
/****** Object: StoredProcedure [mig_director].[ServiceCheckIn] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[ServiceCheckIn]
(
@machineName nvarchar(50)
)
AS
update mig_director.Server
set ServiceCheckInTime = GetDate()
where
MachineName = @machineName
GO
/****** Object: StoredProcedure [mig_director].[Track_Delete] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Track_Delete]
@trackID uniqueidentifier
as
declare @usage table([Type] varchar(50), [Usage] varchar(50), [ProjectID] uniqueidentifier)
insert @usage
select
'Project'
, B.[Description]
, C.[ProjectID]
from
[mig_director].[Track] as A
inner join [mig_director].[TrackUsage] as B
on
A.[ActiveTrackUsage] = B.[TrackUsageID]
inner join [mig_director].[Project] as C
on
B.[ProjectID] = C.[ProjectID]
where
A.[TrackID] = @trackID
insert @usage
select
'Archiving'
, isnull(B.[Description], B.[MachineName]) + ' - Track ' + cast(A.[NumberOnServer] as varchar(5))
, null
from
[mig_director].[Track] as A
inner join [mig_director].[Server] as B
on
A.[ServerId] = B.[ServerId]
where
A.[ArchiveToTrack] = @trackID
if not exists(select top(1) 1 from @usage)
begin
begin transaction
delete [mig_director].[TrackProcess]
where
[TrackID] = @trackID
delete [mig_director].[TrackFolder]
where
[TrackID] = @trackID
delete [mig_director].[Track]
where
[TrackID] = @trackID
commit
end
select * from @usage order by 1
GO
/****** Object: StoredProcedure [mig_director].[Track_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[Track_List]
as
select
A.[TrackID]
, isnull(B.[Description], B.[MachineName]) as [Server]
, A.[NumberOnServer]
, isnull(C.[Description], C.[MachineName]) as [MigrationDB_Server]
, A.[MigrationDB_Name]
, isnull(D.[Description], D.[MachineName]) as [StagingDB_Server]
, A.[StagingDB_Name]
, cast(case
when isnull(E.[Publish], 0) = 0 then 0
else 1
end as bit) as [Publish]
, E.[ExpireDateOnWeb]
, A.[IsArchive]
, isnull(H.[Description], H.[MachineName]) + ' - Track ' + cast(G.[NumberOnServer] as varchar(5)) as [ArchivingTo]
from
[mig_director].[Track] as A
inner join [mig_director].[Server] as B
on
A.[ServerId] = B.[ServerId]
left outer join [mig_director].[Server] as C
on
A.[MigrationDB_ServerId] = C.[ServerId]
left outer join [mig_director].[Server] as D
on
A.[StagingDB_ServerId] = D.[ServerId]
left outer join [mig_director].[TrackUsage] as E
on
A.[ActiveTrackUsage] = E.[TrackUsageID]
left outer join [mig_director].[Project] as F
on
E.[ProjectID] = F.[ProjectID]
left outer join [mig_director].[Track] as G
on
A.[ArchiveToTrack] = G.[TrackID]
left outer join [mig_director].[Server] as H
on
H.[ServerId] = G.[ServerId]
order by
isnull(B.[Description], B.[MachineName])
, A.[NumberOnServer]
GO
/****** Object: StoredProcedure [mig_director].[Track_Modify_CreateOrUpdate] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Track_Modify_CreateOrUpdate]
@trackID uniqueidentifier
, @serverId int
, @numberOnServer int
, @isArchive bit
, @migrationDB_ServerId int = null
, @migrationDB_Name varchar(50)
, @migrationDB_CSFormat varchar(1024) = null
, @stagingDB_ServerId int = null
, @stagingDB_Name varchar(50)
, @stagingDB_CSFormat varchar(1024) = null
, @folderXml xml = null -- This is the path 1This is the path 2
as
set quoted_identifier on
begin transaction
update [mig_director].[Track]
set
[ServerId] = @serverId
, [NumberOnServer] = @numberOnServer
, [IsArchive] = @isArchive
, [MigrationDB_ServerId] = @migrationDB_ServerId
, [MigrationDB_Name] = @migrationDB_Name
, [MigrationDB_CSFormat] = @migrationDB_CSFormat
, [StagingDB_ServerId] = @stagingDB_ServerId
, [StagingDB_Name] = @stagingDB_Name
, [StagingDB_CSFormat] = @stagingDB_CSFormat
where
[TrackID] = @trackID
if 0 = @@rowCount
begin
insert [mig_director].[Track] (
[TrackID]
, [ServerId]
, [NumberOnServer]
, [IsArchive]
, [MigrationDB_ServerId]
, [MigrationDB_Name]
, [MigrationDB_CSFormat]
, [StagingDB_ServerId]
, [StagingDB_Name]
, [StagingDB_CSFormat]
) values (
@trackID
, @serverId
, @numberOnServer
, @isArchive
, @migrationDB_ServerId
, @migrationDB_Name
, @migrationDB_CSFormat
, @stagingDB_ServerId
, @stagingDB_Name
, @stagingDB_CSFormat
)
end
if @folderXml is not null
begin
delete [mig_director].[TrackFolder]
where [TrackID] = @trackID;
insert [mig_director].[TrackFolder]
select
@trackID
, [Paths].[ref].value('@id', 'varchar(50)') as [FolderId]
, [Paths].[ref].value('.[1]', 'varchar(max)') as [Path]
from
@folderXml.nodes('/*/*') [Paths](ref)
end
commit
GO
/****** Object: StoredProcedure [mig_director].[Track_Modify_Read] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Track_Modify_Read]
@trackID uniqueidentifier = null
as
select
A.[TrackID]
, A.[ServerId]
, A.[NumberOnServer]
, A.[IsArchive]
, isnull(A.[MigrationDB_ServerId], -1) as [MigrationDB_ServerId]
, A.[MigrationDB_Name]
, A.[MigrationDB_CSFormat]
, isnull(A.[StagingDB_ServerId], -1) as [StagingDB_ServerId]
, A.[StagingDB_Name]
, A.[StagingDB_CSFormat]
, isnull(C.[Description], C.[MachineName]) + ' - Track ' + cast(B.[NumberOnServer] as varchar(5)) as [ArchiveFor]
, isnull(E.[Description], E.[MachineName]) + ' - Track ' + cast(D.[NumberOnServer] as varchar(5)) as [ArchiveTo]
from
[mig_director].[Track] as A
left outer join [mig_director].[Track] as B on A.[TrackID] = B.[ArchiveToTrack]
left outer join [mig_director].[Server] as C on C.[ServerId] = B.[ServerId]
left outer join [mig_director].[Track] as D on A.[ArchiveToTrack] = D.[TrackID]
left outer join [mig_director].[Server] as E on E.[ServerId] = D.[ServerId]
where
A.[TrackID] = @trackID
select
A.[FolderId]
, isnull(B.[Path], '') as [Path]
, A.[Label]
, A.[Hint]
, A.[DefaultPath]
from
[mig_director].[Folder] as A
left outer join [mig_director].[TrackFolder] as B on A.[FolderId] = B.[FolderId] and B.[TrackID] = @trackID
order by
A.[Seq]
select
[ServerId]
, [MachineName]
, [Description]
, [DefaultTrackPath]
from
[mig_director].[Server]
GO
/****** Object: StoredProcedure [mig_director].[Track_PathList] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Track_PathList]
@trackID uniqueidentifier
as
select
A.[FolderId]
, isnull(B.[Path], '') as [Path]
from
[mig_director].[Folder] as A
left outer join [mig_director].[TrackFolder] as B on A.[FolderId] = B.[FolderId] and B.[TrackID] = @trackID
GO
/****** Object: StoredProcedure [mig_director].[TrackConfigurationList] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackConfigurationList]
AS
select
A.[TrackID]
, A.[NumberOnServer]
, mig_config.ParseCS(
A.[MigrationDB_CSFormat]
, isnull(DirSrv.[DataSource], Srv.[DataSource])
, A.[MigrationDB_Name]
)
, mig_config.ParseCS(
A.[StagingDB_CSFormat]
, isnull(GenSrv.[DataSource], Srv.[DataSource])
, A.[StagingDB_Name]
)
, mig_config.ParseCS(
Prj.[ProjectDB_CSFormat]
, isnull(PrjSrv.[DataSource], Srv.[DataSource])
, Prj.[ProjectDB_Name]
)
, Prj.[ProjectMode]
, Usg.[Description]
, Prj.[ProjectID]
from
[mig_director].Track as A
inner join [mig_director].[TrackUsage] as Usg
on
Usg.[TrackUsageID] = A.[ActiveTrackUsage]
inner join [mig_director].[Project] as Prj
on
Prj.[ProjectID] = Usg.[ProjectID]
inner join [mig_director].[Server] as Srv
on
Srv.[ServerId] = A.[ServerId]
left outer join [mig_director].[Server] as DirSrv
on
A.[MigrationDB_ServerId] = DirSrv.[ServerId]
left outer join [mig_director].[Server] as GenSrv
on
A.[StagingDB_ServerId] = GenSrv.[ServerId]
left outer join [mig_director].[Server] as PrjSrv
on
Prj.[ProjectDB_ServerId] = PrjSrv.[ServerId]
where
Prj.[ProjectState] = 1 -- Active
GO
/****** Object: StoredProcedure [mig_director].[TrackLink_Modify_CreateOrUpdate] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[TrackLink_Modify_CreateOrUpdate]
@trackID uniqueidentifier
, @archiveToID uniqueidentifier = null
as
update A
set
[ArchiveToTrack] = @archiveToID
from
[mig_director].[Track] as A
left outer join [mig_director].[Track] as B on B.[ArchiveToTrack] = @archiveToID and B.[TrackID] <> A.[TrackID]
where
A.[TrackID] = @trackID
and B.[TrackID] is null
GO
/****** Object: StoredProcedure [mig_director].[TrackLink_Modify_Read] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[TrackLink_Modify_Read]
@trackID uniqueidentifier
as
declare @archiveToID uniqueidentifier
declare @projectID uniqueidentifier
select
@archiveToID = A.[ArchiveToTrack]
, @projectID = B.[ProjectID]
from
[mig_director].[Track] as A
inner join [mig_director].[TrackUsage] as B on A.[ActiveTrackUsage] = B.[TrackUsageID]
where
A.[TrackID] = @trackID
select @archiveToID as [ArchiveToID]
select
A.[TrackID]
, isnull(B.[Description], B.[MachineName]) + ' - Track ' + cast(A.[NumberOnServer] as varchar(5)) as [TrackLabel]
from
[mig_director].[Track] as A
inner join [mig_director].[Server] as B on A.[ServerId] = B.[ServerId]
inner join [mig_director].[TrackUsage] as C on A.[ActiveTrackUsage] = C.[TrackUsageID]
where
A.[IsArchive] = 1
and C.[ProjectID] = @projectID
and A.[TrackID] <> @trackID
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessCheckIn] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessCheckIn]
(
@trackID uniqueidentifier,
@debugging bit = 0
)
AS
declare @stopRequested nvarchar(50)
select
@stopRequested = StopRequested
from mig_director.TrackProcess
where TrackID = @trackID
if @stopRequested is null
begin
declare @state int
if 0 = @debugging
set @state = 1
else
set @state = 2
update mig_director.TrackProcess
set
State = @state
, ProcessCheckIn = sysdatetime()
where TrackID = @trackID
end
select @stopRequested
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessClearAll] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessClearAll] (
@machineName nvarchar(50)
)
AS
delete from mig_director.TrackProcess
from
mig_director.TrackProcess as A
inner join mig_director.Track as B
on B.TrackID = A.TrackID
inner join mig_director.[Server] as C
on B.ServerId = C.ServerId
where
C.MachineName = @machineName
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessConsumeStartRequests] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessConsumeStartRequests] (
@machineName nvarchar(50)
) AS
declare @requests table(
[TrackID] uniqueidentifier
, [RequestingUser] varchar(50)
)
update [mig_director].[TrackProcess]
set
[StartRequested] = null
output deleted.[TrackID], deleted.[StartRequested] into @requests
from
[mig_director].[TrackProcess] as A
inner join [mig_director].[Track] as B on A.[TrackID] = B.[TrackID]
inner join [mig_director].[Server] as C on B.[ServerId] = C.[ServerId]
where
A.[StartRequested] is not null
and C.[MachineName] = @machineName
select
A.[TrackID]
, B.[NumberOnServer]
, A.[RequestingUser]
from
@requests as A
inner join [mig_director].[Track] as B on A.[TrackID] = B.[TrackID]
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessRequestStart] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessRequestStart]
(
@trackID uniqueidentifier,
@userId nvarchar(50)
)
AS
delete from mig_director.TrackProcess
where TrackID = @trackID
insert into mig_director.TrackProcess (
TrackID
, StartRequested
) values (
@trackID
, @userId
)
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessRequestStop] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessRequestStop]
(
@trackID uniqueidentifier
, @userId nvarchar(50)
)
AS
update mig_director.TrackProcess
set
StopRequested = @userId
where TrackID = @trackID
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessRequestStopAll] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessRequestStopAll] (
@userId nvarchar(50)
, @machineName nvarchar(50)
) AS
update mig_director.TrackProcess
set
StopRequested = @userId
from
mig_director.TrackProcess as A
inner join mig_director.Track as B
on B.TrackID = A.TrackID
inner join mig_director.[Server] as C
on B.ServerId = C.ServerId
where
C.MachineName = @machineName
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessSetError] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessSetError]
(
@trackID uniqueidentifier,
@error nvarchar(max)
)
AS
update mig_director.TrackProcess
set
State = 0,
Error = @error,
StartRequested = null
where TrackID = @trackID
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessSetProcessId] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[TrackProcessSetProcessId]
(
@trackID uniqueidentifier,
@pId int
)
AS
update mig_director.TrackProcess
set
ProcessId = @pId
where TrackID = @trackID
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessSetRunning] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessSetRunning]
(
@trackID uniqueidentifier
, @running bit
)
AS
if 1 = @running
update mig_director.TrackProcess
set
State = 1
where TrackID = @trackID
else
update mig_director.TrackProcess
set
State = 0
, ProcessId = 0
, StopRequested = null
where TrackID = @trackID
GO
/****** Object: StoredProcedure [mig_director].[TrackProcessState] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[TrackProcessState]
@trackID uniqueidentifier
, @state int out
, @lastCheckIn datetime2(7) out
, @error varchar(max) out
AS
set @state = 0
set @lastCheckIn = '0001-01-01'
select
@state = [State]
, @lastCheckIn = [ProcessCheckin]
, @error = [Error]
from
[mig_director].[TrackProcess]
where
[TrackID] = @trackID
GO
/****** Object: StoredProcedure [mig_director].[TrackUsage_Modify_CreateOrUpdate] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[TrackUsage_Modify_CreateOrUpdate]
@trackID uniqueidentifier
, @trackUsageID uniqueidentifier = null
, @projectID uniqueidentifier = null
, @usage varchar(50) = null
, @publish bit = null
, @expireDateOnWeb datetime = null
as
begin transaction
if @trackUsageID is null
begin
declare @trackUsage table([TrackUsageID] uniqueidentifier)
if @projectID is null
begin
-- Set Unused
update [mig_director].[Track]
set
[ActiveTrackUsage] = null
output deleted.[ActiveTrackUsage] into @trackUsage
where
[TrackID] = @trackID
end
else
begin
-- Was Unused - New TrackUsage
insert [mig_director].[TrackUsage] (
[ProjectID]
, [Description]
, [Publish]
, [ExpireDateOnWeb]
)
output inserted.[TrackUsageID] into @trackUsage
values (
@projectID
, @usage
, @publish
, @expireDateOnWeb
)
-- Get the new TrackUsageID
declare @newUsageID uniqueidentifier = (select top(1) [TrackUsageID] from @trackUsage)
-- and clear the temp table, so there is not 2 rows in there for the following delete
delete @trackUsage
update [mig_director].[Track]
set
[ActiveTrackUsage] = @newUsageID
, [ArchiveToTrack] = null
output deleted.[ActiveTrackUsage] into @trackUsage
where
[TrackID] = @trackID
end
-- Clear any previous TrackUsage
delete [mig_director].[TrackUsage]
from
[mig_director].[TrackUsage] as A
inner join @trackUsage as B on A.[TrackUsageId] = B.[TrackUsageID]
end
else
begin
-- Update existing TrackUsage (change of ProjectID is only allowed if the track is not linked as an Archive to another track)
update [mig_director].[TrackUsage]
set
[ProjectID] = @projectID
, [Description] = @usage
, [Publish] = @publish
, [ExpireDateOnWeb] = @expireDateOnWeb
where
[TrackUsageID] = @trackUsageID
and (
[ProjectID] = @projectID
or
not exists (
select 1
from [mig_director].[Track]
where
[ArchiveToTrack] = @trackID
)
)
end
commit
GO
/****** Object: StoredProcedure [mig_director].[TrackUsage_Modify_Read] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[TrackUsage_Modify_Read]
@trackID uniqueidentifier
as
select
A.[TrackID]
, A.[IsArchive]
, B.[TrackUsageID]
, B.[ProjectID]
, B.[Description]
, B.[Publish]
, B.[ExpireDateOnWeb]
, isnull(D.[Description], D.[MachineName]) + ' - Track ' + cast(C.[NumberOnServer] as varchar(5)) as [ArchiveTo]
, isnull(F.[Description], F.[MachineName]) + ' - Track ' + cast(E.[NumberOnServer] as varchar(5)) as [ArchiveFor]
from
[mig_director].[Track] as A
left outer join [mig_director].[TrackUsage] as B
on
A.[ActiveTrackUsage] = B.[TrackUsageID]
left outer join [mig_director].[Track] as C
on
A.[ArchiveToTrack] = C.[TrackID]
left outer join [mig_director].[Server] as D
on
D.[ServerId] = C.[ServerId]
left outer join [mig_director].[Track] as E
on
A.[TrackID] = E.[ArchiveToTrack]
left outer join [mig_director].[Server] as F
on
F.[ServerId] = E.[ServerId]
where
A.[TrackID] = @trackID
select
[ProjectID]
from
[mig_director].[Project]
GO
/****** Object: StoredProcedure [mig_director].[TrackUserProfile_Get] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[TrackUserProfile_Get]
@trackID uniqueidentifier
, @userId varchar(50)
as
select
[Profile]
from
[mig_director].[TrackUserProfile]
where
[TrackID] = @trackID
and [UserId] = @userId
GO
/****** Object: StoredProcedure [mig_director].[TrackUserProfile_Set] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [mig_director].[TrackUserProfile_Set]
@trackID uniqueidentifier
, @userId varchar(50)
, @profile varchar(max)
as
update [mig_director].[TrackUserProfile]
set
[Profile] = @profile
, [ModificationDate] = sysdatetime()
where
[TrackID] = @trackID
and [UserId] = @userId
if 0 = @@rowCount
begin
insert [mig_director].[TrackUserProfile] (
[TrackID]
, [UserId]
, [Profile]
) values (
@trackID
, @userId
, @profile
)
end
GO
/****** Object: StoredProcedure [mig_public].[Track_List] Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_public].[Track_List]
AS
select
ProjectID
, 'Not available' as ProjectName
, TrackID
, TrackName
from [mig_tracking].[ProjectTrack]
order by ProjectID, TrackID
GO
CREATE TABLE [mig_director].[Secret] (
[Id] INT IDENTITY(1,1) NOT NULL,
[Name] VARCHAR(250) NOT NULL,
[Value] VARCHAR(MAX) NOT NULL,
CONSTRAINT [PK_Secret] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE UNIQUE INDEX [IX_Secret_Name] ON [mig_director].[Secret] ([Name])
GO
create procedure [mig_director].[Secret_List]
as
select
[Id]
, [Name]
, [Value]
from
[mig_director].[Secret]
order by
[Name]
GO
create procedure [mig_director].[Secret_GetByName]
@name varchar(250)
, @id int output
, @value varchar(max) output
as
select
@id = [Id]
, @value = [Value]
from
[mig_director].[Secret]
where
[Name] = @name
GO
create procedure [mig_director].[Secret_Get]
@id int
, @name varchar(250) output
, @value varchar(max) output
as
select
@name = [Name]
, @value = [Value]
from
[mig_director].[Secret]
where
[Id] = @id
GO
create procedure [mig_director].[Secret_Delete]
@id int
as
delete [mig_director].[Secret]
where
[Id] = @id
GO
create procedure [mig_director].[Secret_Create]
@name varchar(250)
, @value varchar(max)
as
insert [mig_director].[Secret] (
[Name]
, [Value]
)
values (
@name
, @value
)
GO
create procedure [mig_director].[Secret_Update]
@id int
, @name varchar(250)
, @value varchar(max)
as
update [mig_director].[Secret]
set
[Name] = @name
, [Value] = @value
where
[Id] = @id
GO
INSERT [mig_config].[EventReceiver] ([ReceiverCode], [StringValue], [DefaultWorkLevel]) VALUES (0, N'Source', 1)
GO
INSERT [mig_config].[EventReceiver] ([ReceiverCode], [StringValue], [DefaultWorkLevel]) VALUES (1, N'Migration', 1)
GO
INSERT [mig_config].[EventReceiver] ([ReceiverCode], [StringValue], [DefaultWorkLevel]) VALUES (2, N'Target', 0)
GO
INSERT [mig_config].[MasterParameter] ([ParameterName], [ParameterValue]) VALUES (N'CSFormat', N'Data Source=@server;Initial Catalog=@database;Integrated Security=True;TrustServerCertificate=True;Max Pool Size=500;MultipleActiveResultSets=True')
GO
INSERT [mig_config].[MasterParameter] ([ParameterName], [ParameterValue]) VALUES (N'PulseInterval', N'2500')
GO
INSERT [mig_config].[MasterParameter] ([ParameterName], [ParameterValue]) VALUES (N'SchemaServiceUrl', N'http://localhost/Tracker/SchemaService.asmx')
GO
INSERT [mig_config].[MasterParameter] ([ParameterName], [ParameterValue]) VALUES (N'ServerDescriptionInTrackWindow', N'True')
GO
INSERT [mig_director].[Concurrency] ([Tag], [Dynamic], [Limit]) VALUES (N'Source.Export', 1, 20)
GO
INSERT [mig_director].[Concurrency] ([Tag], [Dynamic], [Limit]) VALUES (N'Source.Insert', 1, 20)
GO
INSERT [mig_director].[Concurrency] ([Tag], [Dynamic], [Limit]) VALUES (N'Target.Import', 1, 10)
GO
INSERT [mig_director].[Concurrency] ([Tag], [Dynamic], [Limit]) VALUES (N'Target.Insert', 1, 10)
GO
INSERT [mig_director].[Concurrency] ([Tag], [Dynamic], [Limit]) VALUES (N'Target.Read', 1, 10)
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.BcpPacketSize', N'Director', N'4096', N'int', 1, N'The network packet size to use for BCP utility unload. Valid range is 4096 to 65535')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.BcpUnloadConcurrency', N'Director', N'10', N'int', 1, N'The maximum number of concurrent BCP unloads in one Unload job')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.DefaultBatchSize', N'Director', N'1000', N'int', 1, N'The default batch size for new tasks')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.GlobalCounter.BlockSize', N'Director', N'2500', N'int', 1, N'The interval to reserve for global counters')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.InitialMaxConcurrency', N'Director', N'20', N'int', 1, N'The maximum number of concurrent jobs on initial track start')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.OutProcessEnabled', N'Director', N'true', N'bool', 1, N'Enable out process job execution')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.RunTrackingTimeout', N'Director', N'1800', N'int', 1, N'Sql timeout in seconds for reload Tracking Web ')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.SaveExportXmlForRejects', N'Director', N'true', N'bool', 1, N'Save Export Xml for rejected root items')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.ValusetsetLoadConcurrency', N'Director', N'10', N'int', 1, N'The maximum number of Valuesets to load in parallel')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.Verbosity', N'Director', N'true', N'bool', 1, N'Emit events with impact Information. Uncheck to reduce the number of events generated')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.XmlEncoding', N'Director', N'UTF-8', N'string', 1, N'The Encoding used in the track (UTF-16 is not supported)')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.PartitionedUnload', N'Director', N'No', N'string', 1, N'Partitioned unload: No, All (always create file for all partitions), NotEmpty (do not create file for partitions with no items)')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.ZipUnloadFiles', N'Director', N'false', N'bool', 1, N'Zip unloaded files')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.UseNChar', N'Director', N'false', N'bool', 1, N'Use NVarChar data type for character data')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Director.SaveXmlVersions', N'Director', N'true', N'bool', 1, N'Keep item history')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.ExportTimeOut', N'Export', N'1800', N'string', 1, N'Sql timout in seconds for exporting items')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.SaveCommandFile', N'Export', N'false', N'bool', 1, N'Controls wether bulk load scripts and format files are kept when loading tables')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.TableLoadBatchSize', N'Export', N'10000', N'int', 1, N'Commit frequency for bulk load of exported data')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.TableLoadMaxErrors', N'Export', N'10', N'int', 1, N'Number of errors to accept before stopping bulk load of export table')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.TableLoadTimeOut', N'Export', N'1800', N'int', 1, N'Sql timout in seconds for export bulk load')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.ThrowOnUnknownPartitionValue', N'Export', N'true', N'bool', 1, N'Fault export if unable to determine PartitionValue (avoid PartitionValue = Unknown)')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.ViewLoadTimeOut', N'Export', N'7200', N'int', 1, N'Sql timout in seconds for export load af Views')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.ViewMaxParallelLoad', N'Export', N'20', N'int', 1, N'The maximum number of Views to load in parallel')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Export.FuzzyLoad', N'Export', N'true', N'bool', 1, N'Fuzzy load of source files (enables free column order and allows the omission of char columns and nullable columns)')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Garbage.Aging', N'Garbage Collector', N'15', N'int', 1, N'The age (in minutes) obsolete items must reach before they are collected')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Garbage.BatchSize', N'Garbage Collector', N'250000', N'int', 1, N'The maximum number of rows to delete in 1 batch (greater = faster, but less responsive to disable request)')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Garbage.DeadlockRetries', N'Garbage Collector', N'15', N'int', 1, N'The maximum number of deadlock retries for 1 commit')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Garbage.Interval', N'Garbage Collector', N'300000', N'int', 1, N'Milliseconds between collections')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Garbage.LogsToKeep', N'Garbage Collector', N'100', N'int', 1, N'The number of Collection logs to keep (logs with errors are always kept)')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Garbage.MaxDeleteConcurrency', N'Garbage Collector', N'3', N'int', 1, N'The maximum number of Collection threads to run concurrently')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Index.IndexBuildDOP', N'Index', N'10', N'int', 1, N'The maximum number of index rebuilds to execute in parallel')
GO
INSERT [mig_director].[DirectorOption] ([OptionName], [OptionArea], [OptionValue], [OptionType], [CanOverride], [Description]) VALUES (N'Index.MaxOptimizeIterations', N'Index', N'3', N'int', 1, N'The maximum number of optimize iterations per query')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'ApplicationFolder', N'Runtime', 10, N'Runtime', N'Runtime libraries of the Track')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'Archiving', N'Archiving', 100, N'Archiving', N'Destination for database backup files in connection with Archiving')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'SourceFiles', N'Source files', 30, N'Files\SourceData', N'Files received from Source System to be loaded to Staging')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'Extensions', N'Extensions', 110, N'Extensions', N'Working folder for installation specific Extensions')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'FormatFiles', N'Bulk load formatFiles', 70, N'Work\SourceData\FormatFiles', N'Generated Format files for Sql Server Bulk Load')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'LoadFiles', N'Reformatted files to bulk load', 60, N'Work\SourceData\FilesToLoad', N'Reformatted Source Files to input to Sql Server Bulk Load')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'SourceDuplicates', N'Source duplicates', 90, N'Work\SourceData\Duplicates', N'Working folder for detection of Source duplicates')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'Temp', N'Temp', 50, N'Work\Temp', N'Temporary working data')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'UnloadedData', N'Result', 40, N'Files\Result', N'Unloaded migration results')
GO
INSERT [mig_director].[Folder] ([FolderId], [Label], [Seq], [DefaultPath], [Hint]) VALUES (N'Valuesets', N'Valuesets', 20, N'Files\Valuesets', N'Input files to load into Valuesets')
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (1, N'AuditResult', N'MigFx.Director.Server.Job.Audit.Result', N'AuditResultJob', 0, 10000, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (2, N'ItemSet', N'MigFx.Director.Server.Job.ItemSet', N'ItemSetJob', 0, NULL, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (3, N'Import', N'MigFx.Director.Server.Job.Import', N'ImportJob', 1, 100, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (4, N'Index', N'MigFx.Director.Server.Job.Index', N'IndexJob', 1, NULL, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (5, N'Valueset', N'MigFx.Director.Server.Job.DataServices', N'UpdateValuesetsJob', 1, NULL, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (7, N'Maintenance', N'MigFx.Director.Server.Job.Maintenance', N'MaintenanceJob', 0, NULL, 0, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (8, N'Setup', N'MigFx.Director.Server.Job.Setup', N'SetupJob', 1, 25000, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (10, N'Export', N'MigFx.Director.Server.Job.Export', N'ExportJobNormal', 1, 100, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (11, N'Export, fasttrack', N'MigFx.Director.Server.Job.Export', N'ExportJobFastTrack', 1, 1000, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (12, N'Extension', N'MigFx.Director.Server.Job.Extension', N'ExtensionJob', 0, NULL, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (15, N'View', N'MigFx.Director.Server.Job.Export', N'SourceViewJob', 1, NULL, 1, 1, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (18, N'Table', N'MigFx.Director.Server.Job.Export', N'SourceTableJob', 1, 100000, 1, 1, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (19, N'File service', N'MigFx.Director.Server.Job.FileServices', N'FileServicesJob', 0, NULL, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (20, N'SynchronizeEntity', N'MigFx.Director.Server.Job.Export', N'SynchronizeJob', 0, NULL, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (21, N'Unload', N'MigFx.Director.Server.Job.Import', N'UnloadJob', 1, 5000, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (22, N'Unload', N'MigFx.Director.Server.Job.Export', N'UnloadJob', 1, 5000, 1, 0, 1)
GO
INSERT [mig_director].[JobType] ([JobTypeId], [Name], [ClassNamespace], [ClassName], [CanCancel], [InitialBatchSize], [RunOutProcess], [HardCancel], [Priority]) VALUES (23, N'Unload with Dependencies', N'MigFx.Director.Server.Job.Import.DependencyUnload', N'DependencyUnloadJob', 1, 5000, 1, 0, 1)
GO