2249 lines
105 KiB
Transact-SQL
2249 lines
105 KiB
Transact-SQL
IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
|
|
BEGIN
|
|
CREATE TABLE [__EFMigrationsHistory] (
|
|
[MigrationId] nvarchar(150) NOT NULL,
|
|
[ProductVersion] nvarchar(32) NOT NULL,
|
|
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
|
|
);
|
|
END;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Domains] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[FQDN] nvarchar(max) NOT NULL,
|
|
[NetBIOS] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_Domains] PRIMARY KEY ([Id])
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Environments] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[CloudEnabled] bit NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_Environments] PRIMARY KEY ([Id])
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [OptionCategories] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[ParentCategoryName] nvarchar(max) NOT NULL,
|
|
[showOrder] int NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_OptionCategories] PRIMARY KEY ([Id])
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Runbooks] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[Description] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_Runbooks] PRIMARY KEY ([Id])
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Services] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[Description] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_Services] PRIMARY KEY ([Id])
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [VirtualMachines] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[DomainID] uniqueidentifier NOT NULL,
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_VirtualMachines] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_VirtualMachines_Domains_DomainID] FOREIGN KEY ([DomainID]) REFERENCES [Domains] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [EnvironmentDomains] (
|
|
[EnvironmentId] uniqueidentifier NOT NULL,
|
|
[DomainId] uniqueidentifier NOT NULL,
|
|
[Modified] datetime2 NOT NULL,
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL,
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_EnvironmentDomains] PRIMARY KEY ([EnvironmentId], [DomainId]),
|
|
CONSTRAINT [FK_EnvironmentDomains_Domains_DomainId] FOREIGN KEY ([DomainId]) REFERENCES [Domains] ([Id]) ON DELETE CASCADE,
|
|
CONSTRAINT [FK_EnvironmentDomains_Environments_EnvironmentId] FOREIGN KEY ([EnvironmentId]) REFERENCES [Environments] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Options] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[OptionType] nvarchar(max) NOT NULL,
|
|
[OptionValue] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
[OptionCategoryId] uniqueidentifier NOT NULL,
|
|
CONSTRAINT [PK_Options] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_Options_OptionCategories_OptionCategoryId] FOREIGN KEY ([OptionCategoryId]) REFERENCES [OptionCategories] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Events] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[RunbookId] uniqueidentifier NOT NULL,
|
|
[Class] nvarchar(max) NOT NULL,
|
|
[Method] nvarchar(max) NOT NULL,
|
|
[RestEndpointMethod] nvarchar(max) NOT NULL,
|
|
[RestEndpointOperation] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_Events] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_Events_Runbooks_RunbookId] FOREIGN KEY ([RunbookId]) REFERENCES [Runbooks] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [TemplateCategories] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[ServiceId] uniqueidentifier NOT NULL,
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[ParentCategoryName] nvarchar(max) NOT NULL,
|
|
[showOrder] int NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_TemplateCategories] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_TemplateCategories_Services_ServiceId] FOREIGN KEY ([ServiceId]) REFERENCES [Services] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Templates] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[CloudTemplate] bit NOT NULL,
|
|
[Version] nvarchar(max) NOT NULL,
|
|
[Description] nvarchar(max) NOT NULL,
|
|
[JSONData] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
[TemplateCategoryId] uniqueidentifier NOT NULL,
|
|
CONSTRAINT [PK_Templates] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_Templates_TemplateCategories_TemplateCategoryId] FOREIGN KEY ([TemplateCategoryId]) REFERENCES [TemplateCategories] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [DeploymentGroups] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[TemplateId] uniqueidentifier NOT NULL,
|
|
[Status] nvarchar(max) NOT NULL DEFAULT ('New'),
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_DeploymentGroups] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_DeploymentGroups_Templates_TemplateId] FOREIGN KEY ([TemplateId]) REFERENCES [Templates] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [TemplateOptions] (
|
|
[TemplateId] uniqueidentifier NOT NULL,
|
|
[OptionId] uniqueidentifier NOT NULL,
|
|
[Value] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL,
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL,
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_TemplateOptions] PRIMARY KEY ([OptionId], [TemplateId]),
|
|
CONSTRAINT [FK_TemplateOptions_Options_OptionId] FOREIGN KEY ([OptionId]) REFERENCES [Options] ([Id]) ON DELETE CASCADE,
|
|
CONSTRAINT [FK_TemplateOptions_Templates_TemplateId] FOREIGN KEY ([TemplateId]) REFERENCES [Templates] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Deployments] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[DeploymentGroupId] uniqueidentifier NOT NULL,
|
|
[VirtualMachineId] uniqueidentifier NOT NULL,
|
|
[Status] nvarchar(max) NOT NULL,
|
|
[JSONData] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_Deployments] PRIMARY KEY ([VirtualMachineId], [DeploymentGroupId]),
|
|
CONSTRAINT [AK_Deployments_Id] UNIQUE ([Id]),
|
|
CONSTRAINT [FK_Deployments_DeploymentGroups_DeploymentGroupId] FOREIGN KEY ([DeploymentGroupId]) REFERENCES [DeploymentGroups] ([Id]) ON DELETE CASCADE,
|
|
CONSTRAINT [FK_Deployments_VirtualMachines_VirtualMachineId] FOREIGN KEY ([VirtualMachineId]) REFERENCES [VirtualMachines] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [Jobs] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[DeploymentId] uniqueidentifier NOT NULL,
|
|
[RunbookId] uniqueidentifier NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_Jobs] PRIMARY KEY ([RunbookId], [DeploymentId]),
|
|
CONSTRAINT [FK_Jobs_Deployments_DeploymentId] FOREIGN KEY ([DeploymentId]) REFERENCES [Deployments] ([Id]) ON DELETE CASCADE,
|
|
CONSTRAINT [FK_Jobs_Runbooks_RunbookId] FOREIGN KEY ([RunbookId]) REFERENCES [Runbooks] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_DeploymentGroups_TemplateId] ON [DeploymentGroups] ([TemplateId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_Deployments_DeploymentGroupId] ON [Deployments] ([DeploymentGroupId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_EnvironmentDomains_DomainId] ON [EnvironmentDomains] ([DomainId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_Events_RunbookId] ON [Events] ([RunbookId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_Jobs_DeploymentId] ON [Jobs] ([DeploymentId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_Options_OptionCategoryId] ON [Options] ([OptionCategoryId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_TemplateCategories_ServiceId] ON [TemplateCategories] ([ServiceId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_TemplateOptions_TemplateId] ON [TemplateOptions] ([TemplateId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_Templates_TemplateCategoryId] ON [Templates] ([TemplateCategoryId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_VirtualMachines_DomainID] ON [VirtualMachines] ([DomainID]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20231020093825_Initial'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20231020093825_Initial', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260514210821_AddQueueJobs'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260514210821_AddQueueJobs', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260514210842_AddQueueJobModelSnapshotFix'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260514210842_AddQueueJobModelSnapshotFix', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [VirtualMachines] ADD [ExternalId] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [VirtualMachines] ADD [MetadataJson] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [VirtualMachines] ADD [ProviderType] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [VirtualMachines] ADD [TargetType] nvarchar(max) NOT NULL DEFAULT N'';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Environments] ADD [EnvironmentType] nvarchar(max) NOT NULL DEFAULT N'';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Environments] ADD [MetadataJson] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Environments] ADD [ProviderType] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Environments] ADD [SubscriptionId] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Environments] ADD [TenantId] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195005_AddEnvironmentAndTargetProviderModel'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260515195005_AddEnvironmentAndTargetProviderModel', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195742_RemoveCloudEnabledFromEnvironment'
|
|
)
|
|
BEGIN
|
|
DECLARE @var nvarchar(max);
|
|
SELECT @var = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'CloudEnabled');
|
|
IF @var IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var + ';');
|
|
ALTER TABLE [Environments] DROP COLUMN [CloudEnabled];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195742_RemoveCloudEnabledFromEnvironment'
|
|
)
|
|
BEGIN
|
|
DECLARE @var1 nvarchar(max);
|
|
SELECT @var1 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'TenantId');
|
|
IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var1 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [TenantId] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195742_RemoveCloudEnabledFromEnvironment'
|
|
)
|
|
BEGIN
|
|
DECLARE @var2 nvarchar(max);
|
|
SELECT @var2 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'SubscriptionId');
|
|
IF @var2 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var2 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [SubscriptionId] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195742_RemoveCloudEnabledFromEnvironment'
|
|
)
|
|
BEGIN
|
|
DECLARE @var3 nvarchar(max);
|
|
SELECT @var3 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'ProviderType');
|
|
IF @var3 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var3 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [ProviderType] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195742_RemoveCloudEnabledFromEnvironment'
|
|
)
|
|
BEGIN
|
|
DECLARE @var4 nvarchar(max);
|
|
SELECT @var4 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'MetadataJson');
|
|
IF @var4 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var4 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [MetadataJson] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195742_RemoveCloudEnabledFromEnvironment'
|
|
)
|
|
BEGIN
|
|
DECLARE @var5 nvarchar(max);
|
|
SELECT @var5 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'EnvironmentType');
|
|
IF @var5 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var5 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [EnvironmentType] nvarchar(max) NOT NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515195742_RemoveCloudEnabledFromEnvironment'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260515195742_RemoveCloudEnabledFromEnvironment', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515204426_RemoveVirtualMachineTargetAndProvider'
|
|
)
|
|
BEGIN
|
|
DECLARE @var6 nvarchar(max);
|
|
SELECT @var6 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[VirtualMachines]') AND [c].[name] = N'ProviderType');
|
|
IF @var6 IS NOT NULL EXEC(N'ALTER TABLE [VirtualMachines] DROP CONSTRAINT ' + @var6 + ';');
|
|
ALTER TABLE [VirtualMachines] DROP COLUMN [ProviderType];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515204426_RemoveVirtualMachineTargetAndProvider'
|
|
)
|
|
BEGIN
|
|
DECLARE @var7 nvarchar(max);
|
|
SELECT @var7 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[VirtualMachines]') AND [c].[name] = N'TargetType');
|
|
IF @var7 IS NOT NULL EXEC(N'ALTER TABLE [VirtualMachines] DROP CONSTRAINT ' + @var7 + ';');
|
|
ALTER TABLE [VirtualMachines] DROP COLUMN [TargetType];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515204426_RemoveVirtualMachineTargetAndProvider'
|
|
)
|
|
BEGIN
|
|
DECLARE @var8 nvarchar(max);
|
|
SELECT @var8 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[VirtualMachines]') AND [c].[name] = N'MetadataJson');
|
|
IF @var8 IS NOT NULL EXEC(N'ALTER TABLE [VirtualMachines] DROP CONSTRAINT ' + @var8 + ';');
|
|
ALTER TABLE [VirtualMachines] ALTER COLUMN [MetadataJson] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515204426_RemoveVirtualMachineTargetAndProvider'
|
|
)
|
|
BEGIN
|
|
DECLARE @var9 nvarchar(max);
|
|
SELECT @var9 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[VirtualMachines]') AND [c].[name] = N'ExternalId');
|
|
IF @var9 IS NOT NULL EXEC(N'ALTER TABLE [VirtualMachines] DROP CONSTRAINT ' + @var9 + ';');
|
|
ALTER TABLE [VirtualMachines] ALTER COLUMN [ExternalId] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515204426_RemoveVirtualMachineTargetAndProvider'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260515204426_RemoveVirtualMachineTargetAndProvider', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515205512_MakeVirtualMachineDomainOptionalAndAddLinkUnlinkFlow'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [VirtualMachines] DROP CONSTRAINT [FK_VirtualMachines_Domains_DomainID];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515205512_MakeVirtualMachineDomainOptionalAndAddLinkUnlinkFlow'
|
|
)
|
|
BEGIN
|
|
DECLARE @var10 nvarchar(max);
|
|
SELECT @var10 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[VirtualMachines]') AND [c].[name] = N'DomainID');
|
|
IF @var10 IS NOT NULL EXEC(N'ALTER TABLE [VirtualMachines] DROP CONSTRAINT ' + @var10 + ';');
|
|
ALTER TABLE [VirtualMachines] ALTER COLUMN [DomainID] uniqueidentifier NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515205512_MakeVirtualMachineDomainOptionalAndAddLinkUnlinkFlow'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [VirtualMachines] ADD CONSTRAINT [FK_VirtualMachines_Domains_DomainID] FOREIGN KEY ([DomainID]) REFERENCES [Domains] ([Id]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515205512_MakeVirtualMachineDomainOptionalAndAddLinkUnlinkFlow'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260515205512_MakeVirtualMachineDomainOptionalAndAddLinkUnlinkFlow', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515215206_AddServiceCloudFlagAndRoleDefinitionsV2'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Services] ADD [IsCloudService] bit NOT NULL DEFAULT CAST(0 AS bit);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515215206_AddServiceCloudFlagAndRoleDefinitionsV2'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [ServiceRoleDefinitions] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[ServiceId] uniqueidentifier NOT NULL,
|
|
[Key] nvarchar(max) NOT NULL,
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[Description] nvarchar(max) NULL,
|
|
[MinCount] int NOT NULL,
|
|
[MaxCount] int NOT NULL,
|
|
[DefaultStageOrder] int NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_ServiceRoleDefinitions] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_ServiceRoleDefinitions_Services_ServiceId] FOREIGN KEY ([ServiceId]) REFERENCES [Services] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515215206_AddServiceCloudFlagAndRoleDefinitionsV2'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_ServiceRoleDefinitions_ServiceId] ON [ServiceRoleDefinitions] ([ServiceId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515215206_AddServiceCloudFlagAndRoleDefinitionsV2'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260515215206_AddServiceCloudFlagAndRoleDefinitionsV2', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515221746_RemoveRoleDefinitionSizingFields'
|
|
)
|
|
BEGIN
|
|
DECLARE @var11 nvarchar(max);
|
|
SELECT @var11 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[ServiceRoleDefinitions]') AND [c].[name] = N'DefaultStageOrder');
|
|
IF @var11 IS NOT NULL EXEC(N'ALTER TABLE [ServiceRoleDefinitions] DROP CONSTRAINT ' + @var11 + ';');
|
|
ALTER TABLE [ServiceRoleDefinitions] DROP COLUMN [DefaultStageOrder];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515221746_RemoveRoleDefinitionSizingFields'
|
|
)
|
|
BEGIN
|
|
DECLARE @var12 nvarchar(max);
|
|
SELECT @var12 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[ServiceRoleDefinitions]') AND [c].[name] = N'MaxCount');
|
|
IF @var12 IS NOT NULL EXEC(N'ALTER TABLE [ServiceRoleDefinitions] DROP CONSTRAINT ' + @var12 + ';');
|
|
ALTER TABLE [ServiceRoleDefinitions] DROP COLUMN [MaxCount];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515221746_RemoveRoleDefinitionSizingFields'
|
|
)
|
|
BEGIN
|
|
DECLARE @var13 nvarchar(max);
|
|
SELECT @var13 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[ServiceRoleDefinitions]') AND [c].[name] = N'MinCount');
|
|
IF @var13 IS NOT NULL EXEC(N'ALTER TABLE [ServiceRoleDefinitions] DROP CONSTRAINT ' + @var13 + ';');
|
|
ALTER TABLE [ServiceRoleDefinitions] DROP COLUMN [MinCount];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260515221746_RemoveRoleDefinitionSizingFields'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260515221746_RemoveRoleDefinitionSizingFields', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516113515_RemoveTemplateCategoryLegacyFields'
|
|
)
|
|
BEGIN
|
|
DECLARE @var14 nvarchar(max);
|
|
SELECT @var14 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[TemplateCategories]') AND [c].[name] = N'ParentCategoryName');
|
|
IF @var14 IS NOT NULL EXEC(N'ALTER TABLE [TemplateCategories] DROP CONSTRAINT ' + @var14 + ';');
|
|
ALTER TABLE [TemplateCategories] DROP COLUMN [ParentCategoryName];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516113515_RemoveTemplateCategoryLegacyFields'
|
|
)
|
|
BEGIN
|
|
DECLARE @var15 nvarchar(max);
|
|
SELECT @var15 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[TemplateCategories]') AND [c].[name] = N'showOrder');
|
|
IF @var15 IS NOT NULL EXEC(N'ALTER TABLE [TemplateCategories] DROP CONSTRAINT ' + @var15 + ';');
|
|
ALTER TABLE [TemplateCategories] DROP COLUMN [showOrder];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516113515_RemoveTemplateCategoryLegacyFields'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260516113515_RemoveTemplateCategoryLegacyFields', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516114627_AddTemplateCategoryMetadataFields'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [TemplateCategories] ADD [Color] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516114627_AddTemplateCategoryMetadataFields'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [TemplateCategories] ADD [Description] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516114627_AddTemplateCategoryMetadataFields'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [TemplateCategories] ADD [IconKey] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516114627_AddTemplateCategoryMetadataFields'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [TemplateCategories] ADD [IsActive] bit NOT NULL DEFAULT CAST(1 AS bit);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516114627_AddTemplateCategoryMetadataFields'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260516114627_AddTemplateCategoryMetadataFields', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516115934_MoveIconKeyFromTemplateCategoryToService'
|
|
)
|
|
BEGIN
|
|
DECLARE @var16 nvarchar(max);
|
|
SELECT @var16 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[TemplateCategories]') AND [c].[name] = N'IconKey');
|
|
IF @var16 IS NOT NULL EXEC(N'ALTER TABLE [TemplateCategories] DROP CONSTRAINT ' + @var16 + ';');
|
|
ALTER TABLE [TemplateCategories] DROP COLUMN [IconKey];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516115934_MoveIconKeyFromTemplateCategoryToService'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Services] ADD [IconKey] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516115934_MoveIconKeyFromTemplateCategoryToService'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260516115934_MoveIconKeyFromTemplateCategoryToService', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516121222_RemoveTemplateCloudFlag'
|
|
)
|
|
BEGIN
|
|
DECLARE @var17 nvarchar(max);
|
|
SELECT @var17 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Templates]') AND [c].[name] = N'CloudTemplate');
|
|
IF @var17 IS NOT NULL EXEC(N'ALTER TABLE [Templates] DROP CONSTRAINT ' + @var17 + ';');
|
|
ALTER TABLE [Templates] DROP COLUMN [CloudTemplate];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516121222_RemoveTemplateCloudFlag'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260516121222_RemoveTemplateCloudFlag', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Templates] ADD [DeploymentRuleId] uniqueidentifier NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [QueueJobs] ADD [MetadataJson] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [QueueJobs] ADD [RuleSnapshotJson] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [DeploymentRules] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[Description] nvarchar(max) NULL,
|
|
[IsActive] bit NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_DeploymentRules] PRIMARY KEY ([Id])
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [QueueJobSteps] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[QueueJobId] uniqueidentifier NOT NULL,
|
|
[DependsOnQueueJobStepId] uniqueidentifier NULL,
|
|
[SortOrder] int NOT NULL,
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[StepType] nvarchar(max) NOT NULL,
|
|
[Status] nvarchar(max) NOT NULL,
|
|
[MetadataJson] nvarchar(max) NULL,
|
|
[ApprovedAt] datetime2 NULL,
|
|
[ApprovedBy] nvarchar(max) NULL,
|
|
[ApprovalComment] nvarchar(max) NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_QueueJobSteps] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_QueueJobSteps_QueueJobSteps_DependsOnQueueJobStepId] FOREIGN KEY ([DependsOnQueueJobStepId]) REFERENCES [QueueJobSteps] ([Id]) ON DELETE NO ACTION,
|
|
CONSTRAINT [FK_QueueJobSteps_QueueJobs_QueueJobId] FOREIGN KEY ([QueueJobId]) REFERENCES [QueueJobs] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [DeploymentRuleSteps] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[DeploymentRuleId] uniqueidentifier NOT NULL,
|
|
[SortOrder] int NOT NULL,
|
|
[Name] nvarchar(max) NOT NULL,
|
|
[StepType] nvarchar(max) NOT NULL,
|
|
[RequiresApproval] bit NOT NULL,
|
|
[MetadataJson] nvarchar(max) NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_DeploymentRuleSteps] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_DeploymentRuleSteps_DeploymentRules_DeploymentRuleId] FOREIGN KEY ([DeploymentRuleId]) REFERENCES [DeploymentRules] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_Templates_DeploymentRuleId] ON [Templates] ([DeploymentRuleId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_DeploymentRuleSteps_DeploymentRuleId] ON [DeploymentRuleSteps] ([DeploymentRuleId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_QueueJobSteps_DependsOnQueueJobStepId] ON [QueueJobSteps] ([DependsOnQueueJobStepId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_QueueJobSteps_QueueJobId] ON [QueueJobSteps] ([QueueJobId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Templates] ADD CONSTRAINT [FK_Templates_DeploymentRules_DeploymentRuleId] FOREIGN KEY ([DeploymentRuleId]) REFERENCES [DeploymentRules] ([Id]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516140554_AddDeploymentRulesAndQueueJobSteps'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260516140554_AddDeploymentRulesAndQueueJobSteps', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentGroups] DROP CONSTRAINT [FK_DeploymentGroups_Templates_TemplateId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Deployments] DROP CONSTRAINT [FK_Deployments_DeploymentGroups_DeploymentGroupId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Deployments] DROP CONSTRAINT [FK_Deployments_VirtualMachines_VirtualMachineId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Jobs] DROP CONSTRAINT [FK_Jobs_Deployments_DeploymentId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [QueueJobSteps] DROP CONSTRAINT [FK_QueueJobSteps_QueueJobSteps_DependsOnQueueJobStepId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [QueueJobSteps] DROP CONSTRAINT [FK_QueueJobSteps_QueueJobs_QueueJobId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [QueueJobTargets] DROP CONSTRAINT [FK_QueueJobTargets_QueueJobs_QueueJobId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [QueueJobTargets] DROP CONSTRAINT [PK_QueueJobTargets];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [QueueJobSteps] DROP CONSTRAINT [PK_QueueJobSteps];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [QueueJobs] DROP CONSTRAINT [PK_QueueJobs];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Deployments] DROP CONSTRAINT [AK_Deployments_Id];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Deployments] DROP CONSTRAINT [PK_Deployments];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentGroups] DROP CONSTRAINT [PK_DeploymentGroups];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[QueueJobTargets]', N'DeploymentJobTargets', 'OBJECT';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[QueueJobSteps]', N'DeploymentJobSteps', 'OBJECT';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[QueueJobs]', N'DeploymentJobs', 'OBJECT';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[Deployments]', N'DeploymentExecutions', 'OBJECT';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentGroups]', N'DeploymentBatches', 'OBJECT';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobTargets].[IX_QueueJobTargets_QueueJobId]', N'IX_DeploymentJobTargets_QueueJobId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobSteps].[IX_QueueJobSteps_QueueJobId]', N'IX_DeploymentJobSteps_QueueJobId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobSteps].[IX_QueueJobSteps_DependsOnQueueJobStepId]', N'IX_DeploymentJobSteps_DependsOnQueueJobStepId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentExecutions].[IX_Deployments_DeploymentGroupId]', N'IX_DeploymentExecutions_DeploymentGroupId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentBatches].[IX_DeploymentGroups_TemplateId]', N'IX_DeploymentBatches_TemplateId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobTargets] ADD CONSTRAINT [PK_DeploymentJobTargets] PRIMARY KEY ([Id]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobSteps] ADD CONSTRAINT [PK_DeploymentJobSteps] PRIMARY KEY ([Id]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobs] ADD CONSTRAINT [PK_DeploymentJobs] PRIMARY KEY ([Id]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentExecutions] ADD CONSTRAINT [AK_DeploymentExecutions_Id] UNIQUE ([Id]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentExecutions] ADD CONSTRAINT [PK_DeploymentExecutions] PRIMARY KEY ([VirtualMachineId], [DeploymentGroupId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentBatches] ADD CONSTRAINT [PK_DeploymentBatches] PRIMARY KEY ([Id]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentBatches] ADD CONSTRAINT [FK_DeploymentBatches_Templates_TemplateId] FOREIGN KEY ([TemplateId]) REFERENCES [Templates] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentExecutions] ADD CONSTRAINT [FK_DeploymentExecutions_DeploymentBatches_DeploymentGroupId] FOREIGN KEY ([DeploymentGroupId]) REFERENCES [DeploymentBatches] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentExecutions] ADD CONSTRAINT [FK_DeploymentExecutions_VirtualMachines_VirtualMachineId] FOREIGN KEY ([VirtualMachineId]) REFERENCES [VirtualMachines] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobSteps] ADD CONSTRAINT [FK_DeploymentJobSteps_DeploymentJobSteps_DependsOnQueueJobStepId] FOREIGN KEY ([DependsOnQueueJobStepId]) REFERENCES [DeploymentJobSteps] ([Id]) ON DELETE NO ACTION;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobSteps] ADD CONSTRAINT [FK_DeploymentJobSteps_DeploymentJobs_QueueJobId] FOREIGN KEY ([QueueJobId]) REFERENCES [DeploymentJobs] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobTargets] ADD CONSTRAINT [FK_DeploymentJobTargets_DeploymentJobs_QueueJobId] FOREIGN KEY ([QueueJobId]) REFERENCES [DeploymentJobs] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Jobs] ADD CONSTRAINT [FK_Jobs_DeploymentExecutions_DeploymentId] FOREIGN KEY ([DeploymentId]) REFERENCES [DeploymentExecutions] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141653_RenameDeploymentAndJobTables'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260516141653_RenameDeploymentAndJobTables', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentExecutions] DROP CONSTRAINT [FK_DeploymentExecutions_DeploymentBatches_DeploymentGroupId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobSteps] DROP CONSTRAINT [FK_DeploymentJobSteps_DeploymentJobSteps_DependsOnQueueJobStepId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobSteps] DROP CONSTRAINT [FK_DeploymentJobSteps_DeploymentJobs_QueueJobId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobTargets] DROP CONSTRAINT [FK_DeploymentJobTargets_DeploymentJobs_QueueJobId];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobTargets].[QueueJobId]', N'DeploymentJobId', 'COLUMN';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobTargets].[DeploymentGroupId]', N'DeploymentBatchId', 'COLUMN';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobTargets].[IX_DeploymentJobTargets_QueueJobId]', N'IX_DeploymentJobTargets_DeploymentJobId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobSteps].[QueueJobId]', N'DeploymentJobId', 'COLUMN';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobSteps].[DependsOnQueueJobStepId]', N'DependsOnDeploymentJobStepId', 'COLUMN';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobSteps].[IX_DeploymentJobSteps_QueueJobId]', N'IX_DeploymentJobSteps_DeploymentJobId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentJobSteps].[IX_DeploymentJobSteps_DependsOnQueueJobStepId]', N'IX_DeploymentJobSteps_DependsOnDeploymentJobStepId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentExecutions].[DeploymentGroupId]', N'DeploymentBatchId', 'COLUMN';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
EXEC sp_rename N'[DeploymentExecutions].[IX_DeploymentExecutions_DeploymentGroupId]', N'IX_DeploymentExecutions_DeploymentBatchId', 'INDEX';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentExecutions] ADD CONSTRAINT [FK_DeploymentExecutions_DeploymentBatches_DeploymentBatchId] FOREIGN KEY ([DeploymentBatchId]) REFERENCES [DeploymentBatches] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobSteps] ADD CONSTRAINT [FK_DeploymentJobSteps_DeploymentJobSteps_DependsOnDeploymentJobStepId] FOREIGN KEY ([DependsOnDeploymentJobStepId]) REFERENCES [DeploymentJobSteps] ([Id]) ON DELETE NO ACTION;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobSteps] ADD CONSTRAINT [FK_DeploymentJobSteps_DeploymentJobs_DeploymentJobId] FOREIGN KEY ([DeploymentJobId]) REFERENCES [DeploymentJobs] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentJobTargets] ADD CONSTRAINT [FK_DeploymentJobTargets_DeploymentJobs_DeploymentJobId] FOREIGN KEY ([DeploymentJobId]) REFERENCES [DeploymentJobs] ([Id]) ON DELETE CASCADE;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260516141719_RenameDeploymentAndJobColumns'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260516141719_RenameDeploymentAndJobColumns', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517193537_SyncEnvironmentStageAndHostingType'
|
|
)
|
|
BEGIN
|
|
DECLARE @var18 nvarchar(max);
|
|
SELECT @var18 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'TenantId');
|
|
IF @var18 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var18 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [TenantId] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517193537_SyncEnvironmentStageAndHostingType'
|
|
)
|
|
BEGIN
|
|
DECLARE @var19 nvarchar(max);
|
|
SELECT @var19 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'SubscriptionId');
|
|
IF @var19 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var19 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [SubscriptionId] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517193537_SyncEnvironmentStageAndHostingType'
|
|
)
|
|
BEGIN
|
|
DECLARE @var20 nvarchar(max);
|
|
SELECT @var20 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'ProviderType');
|
|
IF @var20 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var20 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [ProviderType] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517193537_SyncEnvironmentStageAndHostingType'
|
|
)
|
|
BEGIN
|
|
DECLARE @var21 nvarchar(max);
|
|
SELECT @var21 = QUOTENAME([d].[name])
|
|
FROM [sys].[default_constraints] [d]
|
|
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
|
|
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Environments]') AND [c].[name] = N'MetadataJson');
|
|
IF @var21 IS NOT NULL EXEC(N'ALTER TABLE [Environments] DROP CONSTRAINT ' + @var21 + ';');
|
|
ALTER TABLE [Environments] ALTER COLUMN [MetadataJson] nvarchar(max) NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517193537_SyncEnvironmentStageAndHostingType'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [Environments] ADD [HostingType] nvarchar(max) NOT NULL DEFAULT N'OnPrem';
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517193537_SyncEnvironmentStageAndHostingType'
|
|
)
|
|
BEGIN
|
|
|
|
UPDATE [Environments]
|
|
SET [HostingType] = CASE
|
|
WHEN [EnvironmentType] IN ('OnPrem','AzureTenant','M365Tenant') THEN [EnvironmentType]
|
|
ELSE 'OnPrem'
|
|
END
|
|
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517193537_SyncEnvironmentStageAndHostingType'
|
|
)
|
|
BEGIN
|
|
|
|
UPDATE [Environments]
|
|
SET [EnvironmentType] = CASE
|
|
WHEN [EnvironmentType] IN ('Development','Test','QA','Production') THEN [EnvironmentType]
|
|
ELSE 'Test'
|
|
END
|
|
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517193537_SyncEnvironmentStageAndHostingType'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260517193537_SyncEnvironmentStageAndHostingType', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260517205713_LinkDeploymentBatchToDeploymentRule'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260517205713_LinkDeploymentBatchToDeploymentRule', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707195927_AddTemplateVersions'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentBatches] ADD [DeploymentRuleId] uniqueidentifier NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707195927_AddTemplateVersions'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [TemplateVersions] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[TemplateId] uniqueidentifier NOT NULL,
|
|
[Version] nvarchar(64) NOT NULL,
|
|
[JsonData] nvarchar(max) NOT NULL,
|
|
[JsonHash] nvarchar(64) NOT NULL,
|
|
[SchemaVersion] nvarchar(32) NOT NULL,
|
|
[IsPublished] bit NOT NULL,
|
|
[PublishedAt] datetime2 NULL,
|
|
[PublishedBy] nvarchar(max) NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_TemplateVersions] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [CK_TemplateVersions_JsonData_IsJson] CHECK (ISJSON([JsonData]) = 1),
|
|
CONSTRAINT [FK_TemplateVersions_Templates_TemplateId] FOREIGN KEY ([TemplateId]) REFERENCES [Templates] ([Id]) ON DELETE CASCADE
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707195927_AddTemplateVersions'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [TemplateVersions]
|
|
([Id], [TemplateId], [Version], [JsonData], [JsonHash], [SchemaVersion], [IsPublished], [PublishedAt], [PublishedBy], [Modified], [ModifiedBy], [Created], [CreatedBy])
|
|
SELECT
|
|
NEWID(),
|
|
[Id],
|
|
CASE
|
|
WHEN [Version] IS NULL OR LTRIM(RTRIM([Version])) = '' THEN '1.0.0'
|
|
ELSE [Version]
|
|
END,
|
|
CASE
|
|
WHEN ISJSON([JSONData]) = 1 THEN [JSONData]
|
|
ELSE '{}'
|
|
END,
|
|
CONVERT(varchar(64), HASHBYTES('SHA2_256', CONVERT(varbinary(max), CASE WHEN ISJSON([JSONData]) = 1 THEN [JSONData] ELSE '{}' END)), 2),
|
|
'1.0',
|
|
1,
|
|
SYSUTCDATETIME(),
|
|
[CreatedBy],
|
|
[Modified],
|
|
[ModifiedBy],
|
|
[Created],
|
|
[CreatedBy]
|
|
FROM [Templates]
|
|
WHERE NOT EXISTS (
|
|
SELECT 1
|
|
FROM [TemplateVersions]
|
|
WHERE [TemplateVersions].[TemplateId] = [Templates].[Id]
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707195927_AddTemplateVersions'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_DeploymentBatches_DeploymentRuleId] ON [DeploymentBatches] ([DeploymentRuleId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707195927_AddTemplateVersions'
|
|
)
|
|
BEGIN
|
|
EXEC(N'CREATE UNIQUE INDEX [IX_TemplateVersions_TemplateId] ON [TemplateVersions] ([TemplateId]) WHERE [IsPublished] = 1');
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707195927_AddTemplateVersions'
|
|
)
|
|
BEGIN
|
|
CREATE UNIQUE INDEX [IX_TemplateVersions_TemplateId_Version] ON [TemplateVersions] ([TemplateId], [Version]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707195927_AddTemplateVersions'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentBatches] ADD CONSTRAINT [FK_DeploymentBatches_DeploymentRules_DeploymentRuleId] FOREIGN KEY ([DeploymentRuleId]) REFERENCES [DeploymentRules] ([Id]) ON DELETE SET NULL;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707195927_AddTemplateVersions'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260707195927_AddTemplateVersions', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
DELETE FROM [DeploymentJobTargets];
|
|
DELETE FROM [DeploymentJobSteps];
|
|
DELETE FROM [DeploymentJobs];
|
|
DELETE FROM [Jobs];
|
|
DELETE FROM [Events];
|
|
DELETE FROM [DeploymentExecutions];
|
|
DELETE FROM [DeploymentBatches];
|
|
DELETE FROM [TemplateVersions];
|
|
DELETE FROM [TemplateOptions];
|
|
DELETE FROM [Templates];
|
|
DELETE FROM [DeploymentRuleSteps];
|
|
DELETE FROM [DeploymentRules];
|
|
DELETE FROM [ServiceRoleDefinitions];
|
|
DELETE FROM [TemplateCategories];
|
|
DELETE FROM [Options];
|
|
DELETE FROM [OptionCategories];
|
|
DELETE FROM [Services];
|
|
DELETE FROM [VirtualMachines];
|
|
DELETE FROM [EnvironmentDomains];
|
|
DELETE FROM [Domains];
|
|
DELETE FROM [Environments];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'Description', N'IsActive', N'Modified', N'ModifiedBy', N'Name') AND [object_id] = OBJECT_ID(N'[DeploymentRules]'))
|
|
SET IDENTITY_INSERT [DeploymentRules] ON;
|
|
EXEC(N'INSERT INTO [DeploymentRules] ([Id], [Created], [CreatedBy], [Description], [IsActive], [Modified], [ModifiedBy], [Name])
|
|
VALUES (''60000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Validate input, wait for approval, then provision targets.'', CAST(1 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Standard Provisioning'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'Description', N'IsActive', N'Modified', N'ModifiedBy', N'Name') AND [object_id] = OBJECT_ID(N'[DeploymentRules]'))
|
|
SET IDENTITY_INSERT [DeploymentRules] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'FQDN', N'Modified', N'ModifiedBy', N'Name', N'NetBIOS') AND [object_id] = OBJECT_ID(N'[Domains]'))
|
|
SET IDENTITY_INSERT [Domains] ON;
|
|
EXEC(N'INSERT INTO [Domains] ([Id], [Created], [CreatedBy], [FQDN], [Modified], [ModifiedBy], [Name], [NetBIOS])
|
|
VALUES (''20000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''corp.contoso.com'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Central Management'', N''CONTOSO''),
|
|
(''20000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''resource.contoso.com'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Resource Domain'', N''RESOURCE'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'FQDN', N'Modified', N'ModifiedBy', N'Name', N'NetBIOS') AND [object_id] = OBJECT_ID(N'[Domains]'))
|
|
SET IDENTITY_INSERT [Domains] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'EnvironmentType', N'HostingType', N'MetadataJson', N'Modified', N'ModifiedBy', N'Name', N'ProviderType', N'SubscriptionId', N'TenantId') AND [object_id] = OBJECT_ID(N'[Environments]'))
|
|
SET IDENTITY_INSERT [Environments] ON;
|
|
EXEC(N'INSERT INTO [Environments] ([Id], [Created], [CreatedBy], [EnvironmentType], [HostingType], [MetadataJson], [Modified], [ModifiedBy], [Name], [ProviderType], [SubscriptionId], [TenantId])
|
|
VALUES (''10000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Test'', N''OnPrem'', N''{"location":"Datacenter A"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Contoso Test'', NULL, NULL, NULL),
|
|
(''10000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Production'', N''OnPrem'', N''{"location":"Datacenter A"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Contoso Production'', NULL, NULL, NULL),
|
|
(''10000000-0000-0000-0000-000000000003'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Production'', N''M365Tenant'', N''{"tenant":"contoso.onmicrosoft.com"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Contoso M365'', N''Microsoft365'', NULL, N''11111111-1111-1111-1111-111111111111'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'EnvironmentType', N'HostingType', N'MetadataJson', N'Modified', N'ModifiedBy', N'Name', N'ProviderType', N'SubscriptionId', N'TenantId') AND [object_id] = OBJECT_ID(N'[Environments]'))
|
|
SET IDENTITY_INSERT [Environments] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'Description', N'IconKey', N'IsCloudService', N'Modified', N'ModifiedBy', N'Name') AND [object_id] = OBJECT_ID(N'[Services]'))
|
|
SET IDENTITY_INSERT [Services] ON;
|
|
EXEC(N'INSERT INTO [Services] ([Id], [Created], [CreatedBy], [Description], [IconKey], [IsCloudService], [Modified], [ModifiedBy], [Name])
|
|
VALUES (''40000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''On-premises directory and identity service.'', N''network'', CAST(0 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Active Directory''),
|
|
(''40000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Database platform for application workloads.'', N''database'', CAST(0 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''SQL Server''),
|
|
(''40000000-0000-0000-0000-000000000003'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Collaboration platform for on-premises workloads.'', N''sharepoint'', CAST(0 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''SharePoint Server''),
|
|
(''40000000-0000-0000-0000-000000000004'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Cloud collaboration workload in Microsoft 365.'', N''messages-square'', CAST(1 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Microsoft Teams'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'Description', N'IconKey', N'IsCloudService', N'Modified', N'ModifiedBy', N'Name') AND [object_id] = OBJECT_ID(N'[Services]'))
|
|
SET IDENTITY_INSERT [Services] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentRuleId', N'MetadataJson', N'Modified', N'ModifiedBy', N'Name', N'RequiresApproval', N'SortOrder', N'StepType') AND [object_id] = OBJECT_ID(N'[DeploymentRuleSteps]'))
|
|
SET IDENTITY_INSERT [DeploymentRuleSteps] ON;
|
|
EXEC(N'INSERT INTO [DeploymentRuleSteps] ([Id], [Created], [CreatedBy], [DeploymentRuleId], [MetadataJson], [Modified], [ModifiedBy], [Name], [RequiresApproval], [SortOrder], [StepType])
|
|
VALUES (''60000000-0000-0000-0000-000000000101'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''60000000-0000-0000-0000-000000000001'', N''{"action":"validate"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Validate configuration'', CAST(0 AS bit), 10, N''Provision''),
|
|
(''60000000-0000-0000-0000-000000000102'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''60000000-0000-0000-0000-000000000001'', N''{"approverGroup":"Platform Owners"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Approve deployment'', CAST(1 AS bit), 20, N''Approval''),
|
|
(''60000000-0000-0000-0000-000000000103'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''60000000-0000-0000-0000-000000000001'', N''{"action":"deploy"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Provision workload'', CAST(0 AS bit), 30, N''Provision'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentRuleId', N'MetadataJson', N'Modified', N'ModifiedBy', N'Name', N'RequiresApproval', N'SortOrder', N'StepType') AND [object_id] = OBJECT_ID(N'[DeploymentRuleSteps]'))
|
|
SET IDENTITY_INSERT [DeploymentRuleSteps] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'DomainId', N'EnvironmentId', N'Created', N'CreatedBy', N'Modified', N'ModifiedBy') AND [object_id] = OBJECT_ID(N'[EnvironmentDomains]'))
|
|
SET IDENTITY_INSERT [EnvironmentDomains] ON;
|
|
EXEC(N'INSERT INTO [EnvironmentDomains] ([DomainId], [EnvironmentId], [Created], [CreatedBy], [Modified], [ModifiedBy])
|
|
VALUES (''20000000-0000-0000-0000-000000000001'', ''10000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed''),
|
|
(''20000000-0000-0000-0000-000000000001'', ''10000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed''),
|
|
(''20000000-0000-0000-0000-000000000002'', ''10000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'DomainId', N'EnvironmentId', N'Created', N'CreatedBy', N'Modified', N'ModifiedBy') AND [object_id] = OBJECT_ID(N'[EnvironmentDomains]'))
|
|
SET IDENTITY_INSERT [EnvironmentDomains] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'Description', N'Key', N'Modified', N'ModifiedBy', N'Name', N'ServiceId') AND [object_id] = OBJECT_ID(N'[ServiceRoleDefinitions]'))
|
|
SET IDENTITY_INSERT [ServiceRoleDefinitions] ON;
|
|
EXEC(N'INSERT INTO [ServiceRoleDefinitions] ([Id], [Created], [CreatedBy], [Description], [Key], [Modified], [ModifiedBy], [Name], [ServiceId])
|
|
VALUES (''41000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''SharePoint farm role.'', N''Farm'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Farm'', ''40000000-0000-0000-0000-000000000003''),
|
|
(''41000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''SharePoint web front-end role.'', N''WebFrontEnd'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Web Front End'', ''40000000-0000-0000-0000-000000000003''),
|
|
(''41000000-0000-0000-0000-000000000003'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''SharePoint service application role.'', N''Application'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Application'', ''40000000-0000-0000-0000-000000000003''),
|
|
(''41000000-0000-0000-0000-000000000004'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''SQL Server database role.'', N''Database'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Database'', ''40000000-0000-0000-0000-000000000002'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'Description', N'Key', N'Modified', N'ModifiedBy', N'Name', N'ServiceId') AND [object_id] = OBJECT_ID(N'[ServiceRoleDefinitions]'))
|
|
SET IDENTITY_INSERT [ServiceRoleDefinitions] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Color', N'Created', N'CreatedBy', N'Description', N'IsActive', N'Modified', N'ModifiedBy', N'Name', N'ServiceId') AND [object_id] = OBJECT_ID(N'[TemplateCategories]'))
|
|
SET IDENTITY_INSERT [TemplateCategories] ON;
|
|
EXEC(N'INSERT INTO [TemplateCategories] ([Id], [Color], [Created], [CreatedBy], [Description], [IsActive], [Modified], [ModifiedBy], [Name], [ServiceId])
|
|
VALUES (''50000000-0000-0000-0000-000000000001'', N''#2563EB'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Templates for domain controller and domain configuration.'', CAST(1 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Domain Services'', ''40000000-0000-0000-0000-000000000001''),
|
|
(''50000000-0000-0000-0000-000000000002'', N''#16A34A'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Templates for SQL Server workloads.'', CAST(1 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Database Platform'', ''40000000-0000-0000-0000-000000000002''),
|
|
(''50000000-0000-0000-0000-000000000003'', N''#0F766E'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Templates for SharePoint Server farms.'', CAST(1 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Collaboration Farm'', ''40000000-0000-0000-0000-000000000003''),
|
|
(''50000000-0000-0000-0000-000000000004'', N''#7C3AED'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Templates for Teams policy configuration.'', CAST(1 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Teams Policies'', ''40000000-0000-0000-0000-000000000004'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Color', N'Created', N'CreatedBy', N'Description', N'IsActive', N'Modified', N'ModifiedBy', N'Name', N'ServiceId') AND [object_id] = OBJECT_ID(N'[TemplateCategories]'))
|
|
SET IDENTITY_INSERT [TemplateCategories] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DomainID', N'ExternalId', N'MetadataJson', N'Modified', N'ModifiedBy', N'Name') AND [object_id] = OBJECT_ID(N'[VirtualMachines]'))
|
|
SET IDENTITY_INSERT [VirtualMachines] ON;
|
|
EXEC(N'INSERT INTO [VirtualMachines] ([Id], [Created], [CreatedBy], [DomainID], [ExternalId], [MetadataJson], [Modified], [ModifiedBy], [Name])
|
|
VALUES (''30000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''20000000-0000-0000-0000-000000000001'', NULL, N''{"role":"DomainController","environment":"Test"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''CT-DC-01''),
|
|
(''30000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''20000000-0000-0000-0000-000000000001'', NULL, N''{"role":"DomainController","environment":"Production"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''CT-DC-02''),
|
|
(''30000000-0000-0000-0000-000000000003'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''20000000-0000-0000-0000-000000000001'', NULL, N''{"role":"SqlServer","environment":"Test"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''CT-SQL-01''),
|
|
(''30000000-0000-0000-0000-000000000004'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''20000000-0000-0000-0000-000000000001'', NULL, N''{"role":"SharePoint","environment":"Test"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''CT-SHP-01''),
|
|
(''30000000-0000-0000-0000-000000000005'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''20000000-0000-0000-0000-000000000001'', NULL, N''{"role":"SharePoint","environment":"Test"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''CT-SHP-02'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DomainID', N'ExternalId', N'MetadataJson', N'Modified', N'ModifiedBy', N'Name') AND [object_id] = OBJECT_ID(N'[VirtualMachines]'))
|
|
SET IDENTITY_INSERT [VirtualMachines] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentRuleId', N'Description', N'JSONData', N'Modified', N'ModifiedBy', N'Name', N'TemplateCategoryId', N'Version') AND [object_id] = OBJECT_ID(N'[Templates]'))
|
|
SET IDENTITY_INSERT [Templates] ON;
|
|
EXEC(N'INSERT INTO [Templates] ([Id], [Created], [CreatedBy], [DeploymentRuleId], [Description], [JSONData], [Modified], [ModifiedBy], [Name], [TemplateCategoryId], [Version])
|
|
VALUES (''70000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''60000000-0000-0000-0000-000000000001'', N''Creates a reusable Active Directory domain baseline.'', CONCAT(CAST(N''{'' AS nvarchar(max)), nchar(10), N'' "schemaVersion": "1.0",'', nchar(10), N'' "templateType": "Service",'', nchar(10), N'' "parameters": {'', nchar(10), N'' "DomainName": { "type": "string", "defaultValue": "corp.contoso.com" },'', nchar(10), N'' "NetBIOSName": { "type": "string", "defaultValue": "CONTOSO" }'', nchar(10), N'' },'', nchar(10), N'' "variables": {'', nchar(10), N'' "DefaultSiteName": "[concat(parameters(''''DomainName''''), ''''-DefaultSite'''')]"'', nchar(10), N'' },'', nchar(10), N'' "resources": {'', nchar(10), N'' "AllNodes": ['', nchar(10), N'' { "NodeName": "*", "PSDscAllowPlainTextPassword": true }'', nchar(10), N'' ],'', nchar(10), N'' "NonNodeData": {'', nchar(10), N'' "Services": {'', nchar(10), N'' "ActiveDirectory": {'', nchar(10), N'' "DomainName": "[parameters(''''DomainName'''')]",'', nchar(10), N'' "NetBIOSName": "[parameters(''''NetBIOSName'''')]"'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N''}''), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Contoso Active Directory Domain'', ''50000000-0000-0000-0000-000000000001'', N''1.0.0''),
|
|
(''70000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''60000000-0000-0000-0000-000000000001'', N''Creates a SQL Server baseline for application workloads.'', CONCAT(CAST(N''{'' AS nvarchar(max)), nchar(10), N'' "schemaVersion": "1.0",'', nchar(10), N'' "templateType": "Service",'', nchar(10), N'' "parameters": {'', nchar(10), N'' "InstanceName": { "type": "string", "defaultValue": "MSSQLSERVER" },'', nchar(10), N'' "DatabasePrefix": { "type": "string", "defaultValue": "Contoso" }'', nchar(10), N'' },'', nchar(10), N'' "variables": {'', nchar(10), N'' "ConfigDatabase": "[concat(parameters(''''DatabasePrefix''''), ''''_Config'''')]"'', nchar(10), N'' },'', nchar(10), N'' "resources": {'', nchar(10), N'' "NonNodeData": {'', nchar(10), N'' "Services": {'', nchar(10), N'' "SqlServer": {'', nchar(10), N'' "InstanceName": "[parameters(''''InstanceName'''')]",'', nchar(10), N'' "ConfigDatabase": "[variables(''''ConfigDatabase'''')]"'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N''}''), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Contoso SQL Server'', ''50000000-0000-0000-0000-000000000002'', N''1.0.0''),
|
|
(''70000000-0000-0000-0000-000000000003'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''60000000-0000-0000-0000-000000000001'', N''Creates a SharePoint Server farm baseline.'', CONCAT(CAST(N''{'' AS nvarchar(max)), nchar(10), N'' "schemaVersion": "1.0",'', nchar(10), N'' "templateType": "Service",'', nchar(10), N'' "parameters": {'', nchar(10), N'' "DatabasePrefix": { "type": "string", "defaultValue": "SharePoint_Contoso_Test" },'', nchar(10), N'' "FarmAccount": { "type": "credential", "metadata": { "description": "Farm account resolved by the credential provider." } }'', nchar(10), N'' },'', nchar(10), N'' "variables": {'', nchar(10), N'' "ConfigDbName": "[concat(parameters(''''DatabasePrefix''''), ''''_Farm_Config'''')]",'', nchar(10), N'' "AdminContentDbName": "[concat(parameters(''''DatabasePrefix''''), ''''_AdminContent'''')]"'', nchar(10), N'' },'', nchar(10), N'' "resources": {'', nchar(10), N'' "NonNodeData": {'', nchar(10), N'' "Services": {'', nchar(10), N'' "SharePoint": {'', nchar(10), N'' "Farm": {'', nchar(10), N'' "ConfigDatabase": "[variables(''''ConfigDbName'''')]",'', nchar(10), N'' "AdminContentDatabase": "[variables(''''AdminContentDbName'''')]",'', nchar(10), N'' "ManagedAccounts": {'', nchar(10), N'' "FarmAccount": "[parameters(''''FarmAccount'''')]"'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N''}''), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Contoso SharePoint Server'', ''50000000-0000-0000-0000-000000000003'', N''1.0.0''),
|
|
(''70000000-0000-0000-0000-000000000004'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''60000000-0000-0000-0000-000000000001'', N''Creates a Microsoft Teams policy baseline.'', CONCAT(CAST(N''{'' AS nvarchar(max)), nchar(10), N'' "schemaVersion": "1.0",'', nchar(10), N'' "templateType": "Service",'', nchar(10), N'' "parameters": {'', nchar(10), N'' "CallingPolicyName": { "type": "string", "defaultValue": "Contoso-Standard-Calling" },'', nchar(10), N'' "MeetingPolicyName": { "type": "string", "defaultValue": "Contoso-Standard-Meetings" }'', nchar(10), N'' },'', nchar(10), N'' "variables": {},'', nchar(10), N'' "resources": {'', nchar(10), N'' "NonNodeData": {'', nchar(10), N'' "Services": {'', nchar(10), N'' "Teams": {'', nchar(10), N'' "CallingPolicy": "[parameters(''''CallingPolicyName'''')]",'', nchar(10), N'' "MeetingPolicy": "[parameters(''''MeetingPolicyName'''')]"'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N''}''), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Contoso Teams Policies'', ''50000000-0000-0000-0000-000000000004'', N''1.0.0'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentRuleId', N'Description', N'JSONData', N'Modified', N'ModifiedBy', N'Name', N'TemplateCategoryId', N'Version') AND [object_id] = OBJECT_ID(N'[Templates]'))
|
|
SET IDENTITY_INSERT [Templates] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentRuleId', N'Modified', N'ModifiedBy', N'Status', N'TemplateId') AND [object_id] = OBJECT_ID(N'[DeploymentBatches]'))
|
|
SET IDENTITY_INSERT [DeploymentBatches] ON;
|
|
EXEC(N'INSERT INTO [DeploymentBatches] ([Id], [Created], [CreatedBy], [DeploymentRuleId], [Modified], [ModifiedBy], [Status], [TemplateId])
|
|
VALUES (''80000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''60000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Pending'', ''70000000-0000-0000-0000-000000000003'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentRuleId', N'Modified', N'ModifiedBy', N'Status', N'TemplateId') AND [object_id] = OBJECT_ID(N'[DeploymentBatches]'))
|
|
SET IDENTITY_INSERT [DeploymentBatches] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'IsPublished', N'JsonData', N'JsonHash', N'Modified', N'ModifiedBy', N'PublishedAt', N'PublishedBy', N'SchemaVersion', N'TemplateId', N'Version') AND [object_id] = OBJECT_ID(N'[TemplateVersions]'))
|
|
SET IDENTITY_INSERT [TemplateVersions] ON;
|
|
EXEC(N'INSERT INTO [TemplateVersions] ([Id], [Created], [CreatedBy], [IsPublished], [JsonData], [JsonHash], [Modified], [ModifiedBy], [PublishedAt], [PublishedBy], [SchemaVersion], [TemplateId], [Version])
|
|
VALUES (''71000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', CAST(1 AS bit), CONCAT(CAST(N''{'' AS nvarchar(max)), nchar(10), N'' "schemaVersion": "1.0",'', nchar(10), N'' "templateType": "Service",'', nchar(10), N'' "parameters": {'', nchar(10), N'' "DomainName": { "type": "string", "defaultValue": "corp.contoso.com" },'', nchar(10), N'' "NetBIOSName": { "type": "string", "defaultValue": "CONTOSO" }'', nchar(10), N'' },'', nchar(10), N'' "variables": {'', nchar(10), N'' "DefaultSiteName": "[concat(parameters(''''DomainName''''), ''''-DefaultSite'''')]"'', nchar(10), N'' },'', nchar(10), N'' "resources": {'', nchar(10), N'' "AllNodes": ['', nchar(10), N'' { "NodeName": "*", "PSDscAllowPlainTextPassword": true }'', nchar(10), N'' ],'', nchar(10), N'' "NonNodeData": {'', nchar(10), N'' "Services": {'', nchar(10), N'' "ActiveDirectory": {'', nchar(10), N'' "DomainName": "[parameters(''''DomainName'''')]",'', nchar(10), N'' "NetBIOSName": "[parameters(''''NetBIOSName'''')]"'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N''}''), N''C05CBEF91131C75DE53B9609D9C2709D680AFC9559EE8D998318A132490F79A7'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''1.0'', ''70000000-0000-0000-0000-000000000001'', N''1.0.0''),
|
|
(''71000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', CAST(1 AS bit), CONCAT(CAST(N''{'' AS nvarchar(max)), nchar(10), N'' "schemaVersion": "1.0",'', nchar(10), N'' "templateType": "Service",'', nchar(10), N'' "parameters": {'', nchar(10), N'' "InstanceName": { "type": "string", "defaultValue": "MSSQLSERVER" },'', nchar(10), N'' "DatabasePrefix": { "type": "string", "defaultValue": "Contoso" }'', nchar(10), N'' },'', nchar(10), N'' "variables": {'', nchar(10), N'' "ConfigDatabase": "[concat(parameters(''''DatabasePrefix''''), ''''_Config'''')]"'', nchar(10), N'' },'', nchar(10), N'' "resources": {'', nchar(10), N'' "NonNodeData": {'', nchar(10), N'' "Services": {'', nchar(10), N'' "SqlServer": {'', nchar(10), N'' "InstanceName": "[parameters(''''InstanceName'''')]",'', nchar(10), N'' "ConfigDatabase": "[variables(''''ConfigDatabase'''')]"'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N''}''), N''F376737E82CB6B729F7B9960CB9988897C16D90A1C454ED2F594B28EB86ECA11'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''1.0'', ''70000000-0000-0000-0000-000000000002'', N''1.0.0''),
|
|
(''71000000-0000-0000-0000-000000000003'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', CAST(1 AS bit), CONCAT(CAST(N''{'' AS nvarchar(max)), nchar(10), N'' "schemaVersion": "1.0",'', nchar(10), N'' "templateType": "Service",'', nchar(10), N'' "parameters": {'', nchar(10), N'' "DatabasePrefix": { "type": "string", "defaultValue": "SharePoint_Contoso_Test" },'', nchar(10), N'' "FarmAccount": { "type": "credential", "metadata": { "description": "Farm account resolved by the credential provider." } }'', nchar(10), N'' },'', nchar(10), N'' "variables": {'', nchar(10), N'' "ConfigDbName": "[concat(parameters(''''DatabasePrefix''''), ''''_Farm_Config'''')]",'', nchar(10), N'' "AdminContentDbName": "[concat(parameters(''''DatabasePrefix''''), ''''_AdminContent'''')]"'', nchar(10), N'' },'', nchar(10), N'' "resources": {'', nchar(10), N'' "NonNodeData": {'', nchar(10), N'' "Services": {'', nchar(10), N'' "SharePoint": {'', nchar(10), N'' "Farm": {'', nchar(10), N'' "ConfigDatabase": "[variables(''''ConfigDbName'''')]",'', nchar(10), N'' "AdminContentDatabase": "[variables(''''AdminContentDbName'''')]",'', nchar(10), N'' "ManagedAccounts": {'', nchar(10), N'' "FarmAccount": "[parameters(''''FarmAccount'''')]"'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N''}''), N''FD5F0FD3C13538FAC70646972DC364DFCEAAF85BBE91B0F50FDFF9FD3A3748B4'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''1.0'', ''70000000-0000-0000-0000-000000000003'', N''1.0.0''),
|
|
(''71000000-0000-0000-0000-000000000004'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', CAST(1 AS bit), CONCAT(CAST(N''{'' AS nvarchar(max)), nchar(10), N'' "schemaVersion": "1.0",'', nchar(10), N'' "templateType": "Service",'', nchar(10), N'' "parameters": {'', nchar(10), N'' "CallingPolicyName": { "type": "string", "defaultValue": "Contoso-Standard-Calling" },'', nchar(10), N'' "MeetingPolicyName": { "type": "string", "defaultValue": "Contoso-Standard-Meetings" }'', nchar(10), N'' },'', nchar(10), N'' "variables": {},'', nchar(10), N'' "resources": {'', nchar(10), N'' "NonNodeData": {'', nchar(10), N'' "Services": {'', nchar(10), N'' "Teams": {'', nchar(10), N'' "CallingPolicy": "[parameters(''''CallingPolicyName'''')]",'', nchar(10), N'' "MeetingPolicy": "[parameters(''''MeetingPolicyName'''')]"'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N'' }'', nchar(10), N''}''), N''32A902FE1A871D4CE2C877EBC5542F7562856532F09BA7F53597B21270586241'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''1.0'', ''70000000-0000-0000-0000-000000000004'', N''1.0.0'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'IsPublished', N'JsonData', N'JsonHash', N'Modified', N'ModifiedBy', N'PublishedAt', N'PublishedBy', N'SchemaVersion', N'TemplateId', N'Version') AND [object_id] = OBJECT_ID(N'[TemplateVersions]'))
|
|
SET IDENTITY_INSERT [TemplateVersions] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'DeploymentBatchId', N'VirtualMachineId', N'Created', N'CreatedBy', N'Id', N'JSONData', N'Modified', N'ModifiedBy', N'Status') AND [object_id] = OBJECT_ID(N'[DeploymentExecutions]'))
|
|
SET IDENTITY_INSERT [DeploymentExecutions] ON;
|
|
EXEC(N'INSERT INTO [DeploymentExecutions] ([DeploymentBatchId], [VirtualMachineId], [Created], [CreatedBy], [Id], [JSONData], [Modified], [ModifiedBy], [Status])
|
|
VALUES (''80000000-0000-0000-0000-000000000001'', ''30000000-0000-0000-0000-000000000004'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''81000000-0000-0000-0000-000000000001'', N''{"role":"WebFrontEnd"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Pending''),
|
|
(''80000000-0000-0000-0000-000000000001'', ''30000000-0000-0000-0000-000000000005'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''81000000-0000-0000-0000-000000000002'', N''{"role":"Application"}'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''Pending'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'DeploymentBatchId', N'VirtualMachineId', N'Created', N'CreatedBy', N'Id', N'JSONData', N'Modified', N'ModifiedBy', N'Status') AND [object_id] = OBJECT_ID(N'[DeploymentExecutions]'))
|
|
SET IDENTITY_INSERT [DeploymentExecutions] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707203222_ResetAndSeedDemoData'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260707203222_ResetAndSeedDemoData', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707204104_RemoveSmaRunbooks'
|
|
)
|
|
BEGIN
|
|
DROP TABLE [Events];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707204104_RemoveSmaRunbooks'
|
|
)
|
|
BEGIN
|
|
DROP TABLE [Jobs];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707204104_RemoveSmaRunbooks'
|
|
)
|
|
BEGIN
|
|
DROP TABLE [Runbooks];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707204104_RemoveSmaRunbooks'
|
|
)
|
|
BEGIN
|
|
ALTER TABLE [DeploymentExecutions] DROP CONSTRAINT [AK_DeploymentExecutions_Id];
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707204104_RemoveSmaRunbooks'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260707204104_RemoveSmaRunbooks', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [DeploymentTargetAssignments] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[DeploymentGroupId] uniqueidentifier NOT NULL,
|
|
[VirtualMachineId] uniqueidentifier NOT NULL,
|
|
[RoleKey] nvarchar(450) NOT NULL,
|
|
[SortOrder] int NOT NULL,
|
|
[NodeDataJson] nvarchar(max) NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_DeploymentTargetAssignments] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [CK_DeploymentTargetAssignments_NodeDataJson_IsJson] CHECK (ISJSON([NodeDataJson]) = 1),
|
|
CONSTRAINT [FK_DeploymentTargetAssignments_DeploymentBatches_DeploymentGroupId] FOREIGN KEY ([DeploymentGroupId]) REFERENCES [DeploymentBatches] ([Id]) ON DELETE CASCADE,
|
|
CONSTRAINT [FK_DeploymentTargetAssignments_VirtualMachines_VirtualMachineId] FOREIGN KEY ([VirtualMachineId]) REFERENCES [VirtualMachines] ([Id]) ON DELETE NO ACTION
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [DeploymentTemplateSelections] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[DeploymentGroupId] uniqueidentifier NOT NULL,
|
|
[TemplateVersionId] uniqueidentifier NOT NULL,
|
|
[TemplateRole] nvarchar(max) NOT NULL,
|
|
[SortOrder] int NOT NULL,
|
|
[Alias] nvarchar(max) NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_DeploymentTemplateSelections] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [FK_DeploymentTemplateSelections_DeploymentBatches_DeploymentGroupId] FOREIGN KEY ([DeploymentGroupId]) REFERENCES [DeploymentBatches] ([Id]) ON DELETE CASCADE,
|
|
CONSTRAINT [FK_DeploymentTemplateSelections_TemplateVersions_TemplateVersionId] FOREIGN KEY ([TemplateVersionId]) REFERENCES [TemplateVersions] ([Id]) ON DELETE NO ACTION
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
CREATE TABLE [DeploymentParameterValues] (
|
|
[Id] uniqueidentifier NOT NULL DEFAULT (NEWID()),
|
|
[DeploymentGroupId] uniqueidentifier NOT NULL,
|
|
[DeploymentTemplateSelectionId] uniqueidentifier NULL,
|
|
[Name] nvarchar(450) NOT NULL,
|
|
[ValueJson] nvarchar(max) NOT NULL,
|
|
[IsSecretReference] bit NOT NULL,
|
|
[IsOverride] bit NOT NULL,
|
|
[Modified] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[ModifiedBy] nvarchar(max) NOT NULL,
|
|
[Created] datetime2 NOT NULL DEFAULT (GETDATE()),
|
|
[CreatedBy] nvarchar(max) NOT NULL,
|
|
CONSTRAINT [PK_DeploymentParameterValues] PRIMARY KEY ([Id]),
|
|
CONSTRAINT [CK_DeploymentParameterValues_ValueJson_IsJson] CHECK (ISJSON([ValueJson]) = 1),
|
|
CONSTRAINT [FK_DeploymentParameterValues_DeploymentBatches_DeploymentGroupId] FOREIGN KEY ([DeploymentGroupId]) REFERENCES [DeploymentBatches] ([Id]) ON DELETE CASCADE,
|
|
CONSTRAINT [FK_DeploymentParameterValues_DeploymentTemplateSelections_DeploymentTemplateSelectionId] FOREIGN KEY ([DeploymentTemplateSelectionId]) REFERENCES [DeploymentTemplateSelections] ([Id])
|
|
);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentGroupId', N'Modified', N'ModifiedBy', N'NodeDataJson', N'RoleKey', N'SortOrder', N'VirtualMachineId') AND [object_id] = OBJECT_ID(N'[DeploymentTargetAssignments]'))
|
|
SET IDENTITY_INSERT [DeploymentTargetAssignments] ON;
|
|
EXEC(N'INSERT INTO [DeploymentTargetAssignments] ([Id], [Created], [CreatedBy], [DeploymentGroupId], [Modified], [ModifiedBy], [NodeDataJson], [RoleKey], [SortOrder], [VirtualMachineId])
|
|
VALUES (''84000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''80000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''{"nodeName":"CT-SHP-01"}'', N''WebFrontEnd'', 10, ''30000000-0000-0000-0000-000000000004''),
|
|
(''84000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''80000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''{"nodeName":"CT-SHP-02"}'', N''Application'', 20, ''30000000-0000-0000-0000-000000000005'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentGroupId', N'Modified', N'ModifiedBy', N'NodeDataJson', N'RoleKey', N'SortOrder', N'VirtualMachineId') AND [object_id] = OBJECT_ID(N'[DeploymentTargetAssignments]'))
|
|
SET IDENTITY_INSERT [DeploymentTargetAssignments] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Alias', N'Created', N'CreatedBy', N'DeploymentGroupId', N'Modified', N'ModifiedBy', N'SortOrder', N'TemplateRole', N'TemplateVersionId') AND [object_id] = OBJECT_ID(N'[DeploymentTemplateSelections]'))
|
|
SET IDENTITY_INSERT [DeploymentTemplateSelections] ON;
|
|
EXEC(N'INSERT INTO [DeploymentTemplateSelections] ([Id], [Alias], [Created], [CreatedBy], [DeploymentGroupId], [Modified], [ModifiedBy], [SortOrder], [TemplateRole], [TemplateVersionId])
|
|
VALUES (''82000000-0000-0000-0000-000000000001'', N''SharePoint'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''80000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', 10, N''Service'', ''71000000-0000-0000-0000-000000000003'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Alias', N'Created', N'CreatedBy', N'DeploymentGroupId', N'Modified', N'ModifiedBy', N'SortOrder', N'TemplateRole', N'TemplateVersionId') AND [object_id] = OBJECT_ID(N'[DeploymentTemplateSelections]'))
|
|
SET IDENTITY_INSERT [DeploymentTemplateSelections] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentGroupId', N'DeploymentTemplateSelectionId', N'IsOverride', N'IsSecretReference', N'Modified', N'ModifiedBy', N'Name', N'ValueJson') AND [object_id] = OBJECT_ID(N'[DeploymentParameterValues]'))
|
|
SET IDENTITY_INSERT [DeploymentParameterValues] ON;
|
|
EXEC(N'INSERT INTO [DeploymentParameterValues] ([Id], [Created], [CreatedBy], [DeploymentGroupId], [DeploymentTemplateSelectionId], [IsOverride], [IsSecretReference], [Modified], [ModifiedBy], [Name], [ValueJson])
|
|
VALUES (''83000000-0000-0000-0000-000000000001'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''80000000-0000-0000-0000-000000000001'', ''82000000-0000-0000-0000-000000000001'', CAST(1 AS bit), CAST(0 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''DatabasePrefix'', N''{"value":"SharePoint_Contoso_Test"}''),
|
|
(''83000000-0000-0000-0000-000000000002'', ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', ''80000000-0000-0000-0000-000000000001'', ''82000000-0000-0000-0000-000000000001'', CAST(1 AS bit), CAST(1 AS bit), ''2026-07-07T00:00:00.0000000Z'', N''DemoSeed'', N''FarmAccount'', N''{"provider":"SecretManagement","vault":"ContosoDemo","name":"Windows/SharePoint/FarmAccount"}'')');
|
|
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Created', N'CreatedBy', N'DeploymentGroupId', N'DeploymentTemplateSelectionId', N'IsOverride', N'IsSecretReference', N'Modified', N'ModifiedBy', N'Name', N'ValueJson') AND [object_id] = OBJECT_ID(N'[DeploymentParameterValues]'))
|
|
SET IDENTITY_INSERT [DeploymentParameterValues] OFF;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
EXEC(N'CREATE UNIQUE INDEX [IX_DeploymentParameterValues_DeploymentGroupId_DeploymentTemplateSelectionId_Name] ON [DeploymentParameterValues] ([DeploymentGroupId], [DeploymentTemplateSelectionId], [Name]) WHERE [DeploymentTemplateSelectionId] IS NOT NULL');
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
EXEC(N'CREATE UNIQUE INDEX [IX_DeploymentParameterValues_DeploymentGroupId_Name] ON [DeploymentParameterValues] ([DeploymentGroupId], [Name]) WHERE [DeploymentTemplateSelectionId] IS NULL');
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_DeploymentParameterValues_DeploymentTemplateSelectionId] ON [DeploymentParameterValues] ([DeploymentTemplateSelectionId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
CREATE UNIQUE INDEX [IX_DeploymentTargetAssignments_DeploymentGroupId_VirtualMachineId_RoleKey] ON [DeploymentTargetAssignments] ([DeploymentGroupId], [VirtualMachineId], [RoleKey]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_DeploymentTargetAssignments_VirtualMachineId] ON [DeploymentTargetAssignments] ([VirtualMachineId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
CREATE UNIQUE INDEX [IX_DeploymentTemplateSelections_DeploymentGroupId_SortOrder] ON [DeploymentTemplateSelections] ([DeploymentGroupId], [SortOrder]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
CREATE INDEX [IX_DeploymentTemplateSelections_TemplateVersionId] ON [DeploymentTemplateSelections] ([TemplateVersionId]);
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT * FROM [__EFMigrationsHistory]
|
|
WHERE [MigrationId] = N'20260707210631_AddDeploymentComposition'
|
|
)
|
|
BEGIN
|
|
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
|
|
VALUES (N'20260707210631_AddDeploymentComposition', N'10.0.8');
|
|
END;
|
|
|
|
COMMIT;
|
|
GO
|
|
|