/****** 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,
	[ProjectName] [varchar](50) NOT NULL,
	[ProjectMode] [int] NULL,
	[ProjectState] [int] NOT NULL,
	[ProjectDB_ServerId] [int] NULL,
	[ProjectDB_Name] [varchar](128) NULL,
	[ProjectDB_CSFormat] [varchar](max) NULL,
	[DefaultLanguage] [char](2) NOT 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 top 100000
   C.[TrackID]
  ,B.[ProjectID]
  ,E.[ProjectName]
  ,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)  
order by 
  E.[ProjectName]
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.[ProjectName]
  , mig_config.[ParseCS](
		A.[ProjectDB_CSFormat]
	  , isnull(B.[DataSource], lower(convert(nvarchar(50), serverproperty('MachineName'))))
	  , A.[ProjectDB_Name]) as [ConnectionString]
	, A.[DefaultLanguage]
	from 
		[mig_director].[Project] as A
		left outer join [mig_director].[Server] as B on  A.[ProjectDB_ServerId] = B.[ServerId]
	where
		A.[ProjectState] in (1, 2) -- Active, Archived 
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].[Language]    Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_profile].[Language](
	[IsoCode639] [char](2) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Supported] [bit] NOT NULL,
 CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED 
(
	[IsoCode639] 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_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].[Project] ADD  CONSTRAINT [DF_Project_ProjectID]  DEFAULT (newid()) FOR [ProjectID]
GO
ALTER TABLE [mig_director].[Project] ADD  CONSTRAINT [DF_Project_ProjectMode]  DEFAULT ((1)) FOR [ProjectMode]
GO
ALTER TABLE [mig_director].[Project] ADD  CONSTRAINT [DF_Project_ProjectState]  DEFAULT ((0)) FOR [ProjectState]
GO
ALTER TABLE [mig_director].[Project] ADD  CONSTRAINT [DF_Project_Language]  DEFAULT ('EN') FOR [DefaultLanguage]
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].[Language] ADD  CONSTRAINT [DF_Language_Supported]  DEFAULT ((0)) FOR [Supported]
GO
ALTER TABLE [mig_profile].[UserProfile] ADD  CONSTRAINT [DF_UserProfile_Profile]  DEFAULT ('<profile />') 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
/****** Object:  StoredProcedure [mig_director].[DefaultLanguage_Get]    Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_director].[DefaultLanguage_Get] (
	@projectID uniqueidentifier
, @isoCode	char(2) = 'EN' output
) as
	
	select @isoCode = DefaultLanguage 
	from mig_director.Project
	where ProjectID = @projectID
GO
/****** Object:  StoredProcedure [mig_director].[DefaultLanguage_Set]    Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_director].[DefaultLanguage_Set] (
	@projectID uniqueidentifier
, @isoCode	char(2)
) as
	
	update mig_director.Project
	set DefaultLanguage = @isoCode
	where ProjectID = @projectID
GO
/****** Object:  StoredProcedure [mig_director].[Deploy_ListInstances]    Script Date: 2021-05-09 ******/
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([Usage] varchar(max))

  insert @usage
  select
	'Project: ' + A.[ProjectName] 
  from
	[mig_director].[Project] as A
  where
	  A.[SourceEngineID] = @engineID or A.[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
  , [ProjectName] nvarchar(50)
  , [ProjectMode] tinyint
  )
  
  insert into @projects
  select 
	[ProjectID]
  , [ProjectName]
  , [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_List]    Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [mig_director].[Project_List] 
as
  select
	A.[ProjectID]
  , A.[ProjectName]
  , A.[ProjectMode]
  , A.[ProjectState]
  , src.[EngineName] as [SourceEngine]
  , tgt.[EngineName] as [TargetEngine]
  from
	[mig_director].[Project] as A
	left outer join [mig_director].[Engine] as src on A.[SourceEngineID] = src.[EngineID]
	left outer join [mig_director].[Engine] as tgt on A.[TargetEngineID] = tgt.[EngineID]
  order by 
	A.[ProjectName]
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 
, @projectName varchar(50)
, @projectMode int
, @projectState int
, @projectDB_ServerId int
, @projectDB_Name varchar(50)
, @projectDB_CSFormat varchar(max) = null
, @defaultLanguage char(2) 
, @sourceEngineID uniqueidentifier = null
, @targetEngineID uniqueidentifier = null
as
  update [mig_director].[Project]
  set
	[ProjectName] = @projectName
  , [ProjectMode] = @projectMode
  , [ProjectState] = @projectState
  , [ProjectDB_ServerId] = @projectDB_ServerId
  , [ProjectDB_Name] = @projectDB_Name
  , [ProjectDB_CSFormat] = @projectDB_CSFormat
  , [DefaultLanguage] = @defaultLanguage
  , [SourceEngineID] = @sourceEngineID
  , [TargetEngineID] = @targetEngineID
  where
	[ProjectID] = @projectID  

  if 0 = @@rowCount
  begin
	insert [mig_director].[Project] (
	  [ProjectID] 
	, [ProjectName] 
	, [ProjectMode] 
	, [ProjectState] 
	, [ProjectDB_ServerId] 
	, [ProjectDB_Name] 
	, [ProjectDB_CSFormat] 
	, [DefaultLanguage] 
	, [SourceEngineID]
	, [TargetEngineID]
	) values (
	  @projectID
	, @projectName
	, @projectMode
	, @projectState
	, @projectDB_ServerId
	, @projectDB_Name
	, @projectDB_CSFormat
	, @defaultLanguage
	, @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]
  , [ProjectName]
  , [ProjectMode]
  , [ProjectState]
  , [DefaultLanguage]
  , [ProjectDB_ServerId]
  , [ProjectDB_Name]
  , [ProjectDB_CSFormat]
  , [SourceEngineID]
  , [TargetEngineID]
  from
	[mig_director].[Project]
  where 
	[ProjectID] = @projectID

  select
	[ServerId]
  , [MachineName]
  , [Description] 
  from
	[mig_director].[Server]

  select
	[IsoCode639]
  , [Name]
  from 
	[mig_profile].[Language]
  where
	[Supported] = 1

  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([Usage] varchar(max))

  insert @usage
  select
	'Project: ' + C.[ProjectName] + ' - ' + B.[Description]
  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)) 
  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] 
  , case
	  when F.[ProjectID] is null then null
	  else F.[ProjectName] + ' - ' + E.[Description]
	end as [Usage]
  , 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 -- <paths><path id="id1">This is the path 1</path><path id="id2">This is the path 2</path></paths>
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]
	,	Prj.[ProjectName] + ' - ' + Usg.[Description] 
	,	Prj.[ProjectID]
	,	Prj.[DefaultLanguage]
	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]
  , [ProjectName]
  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_profile].[GetLanguages]    Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [mig_profile].[GetLanguages] 
AS

  select
	  IsoCode639
	, [Name]
	, [Supported]
  from mig_profile.Language
  order by [Name]
GO
/****** Object:  StoredProcedure [mig_profile].[SetLanguageSupported]    Script Date: 2020-09-30 20:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [mig_profile].[SetLanguageSupported] (
	@IsoCode char(2)
, @supported bit
) AS

	update mig_profile.Language
	set [Supported] = @supported
  where IsoCode639 = @isoCode
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
	, 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.CompressXml', N'Director', N'true', N'bool', 1, N'Compress migration xml')
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'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.RejectOnXmlValidationError', N'Export', N'true', N'bool', 1, N'Xml validation error in Export results in Disposition Full Discard (if false: Disposition = Retain")')
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
INSERT [mig_profile].[Language] ([IsoCode639], [Name], [Supported]) VALUES (N'DA', N'Danish', 1)
GO
INSERT [mig_profile].[Language] ([IsoCode639], [Name], [Supported]) VALUES (N'EN', N'English', 1)
GO
INSERT [mig_profile].[Language] ([IsoCode639], [Name], [Supported]) VALUES (N'FI', N'Finnish', 0)
GO
INSERT [mig_profile].[Language] ([IsoCode639], [Name], [Supported]) VALUES (N'FO', N'Faroese', 0)
GO
INSERT [mig_profile].[Language] ([IsoCode639], [Name], [Supported]) VALUES (N'IS', N'Icelandic', 0)
GO
INSERT [mig_profile].[Language] ([IsoCode639], [Name], [Supported]) VALUES (N'NO', N'Norwegian', 0)
GO
INSERT [mig_profile].[Language] ([IsoCode639], [Name], [Supported]) VALUES (N'SV', N'Swedish', 0)
GO