GizzlerandCursor
58b7826162
Configure CertificateCheckUrl per container for curl-like TLS checks, classify Sonic permission errors, and extend setup wizard for container management. Co-authored-by: Cursor <cursoragent@cursor.com>
608 lines
19 KiB
Transact-SQL
608 lines
19 KiB
Transact-SQL
/*
|
||
DATABASE: [EsbZertifikatManager]
|
||
OBJECTNAME: Schema
|
||
ACTION: CREATE
|
||
IDX: 002
|
||
DATE: 2026-07-29
|
||
USER: CUR
|
||
COMMENT: 6 Kern-Tabellen + Always Encrypted (CMK/CEK).
|
||
Vorher 00_DropExisting + 01_CreateDatabase ausfuehren.
|
||
CMK-KEY_PATH = CurrentUser/My/<Thumbprint aus appsettings>.
|
||
CEK-ENCRYPTED_VALUE ist an dieses Zertifikat gebunden.
|
||
Bei neuem Cert: CEK in SSMS neu erzeugen und ENCRYPTED_VALUE ersetzen.
|
||
Connection String muss Column Encryption Setting=Enabled enthalten.
|
||
*/
|
||
USE [EsbZertifikatManager];
|
||
GO
|
||
|
||
SET ANSI_NULLS ON;
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER ON;
|
||
GO
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
/* Reste bereinigen: Kind-Tabellen -> AE-Tabellen -> CEK -> CMK */
|
||
/* -------------------------------------------------------------------------- */
|
||
|
||
IF OBJECT_ID(N'[dbo].[CertificateBackup]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[CertificateBackup];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[DeploymentRunStep]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[DeploymentRunStep];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[DeploymentRunContainer]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[DeploymentRunContainer];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[DeploymentRun]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[DeploymentRun];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[DeploymentAction]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[DeploymentAction];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[TlsEndpoint]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[TlsEndpoint];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[CertificateTarget]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[CertificateTarget];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[CertificateArtifact]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[CertificateArtifact];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[AppUser]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[AppUser];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[SonicCredential]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[SonicCredential];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[SonicContainer]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[SonicContainer];
|
||
END;
|
||
GO
|
||
|
||
/* SonicConnection kann AE-Spalten haben — vor CEK-Drop entfernen */
|
||
IF OBJECT_ID(N'[dbo].[SonicConnection]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[SonicConnection];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[EsbService]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[EsbService];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[Country]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[Country];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[Environment]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[Environment];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[Company]', N'U') IS NOT NULL
|
||
BEGIN
|
||
DROP TABLE [dbo].[Company];
|
||
END;
|
||
GO
|
||
|
||
IF EXISTS
|
||
(
|
||
SELECT 1
|
||
FROM [sys].[column_encryption_keys] AS cek
|
||
WHERE cek.[name] = N'CEK_ESB_ZertifikatManager'
|
||
)
|
||
BEGIN
|
||
DROP COLUMN ENCRYPTION KEY [CEK_ESB_ZertifikatManager];
|
||
END;
|
||
GO
|
||
|
||
IF EXISTS
|
||
(
|
||
SELECT 1
|
||
FROM [sys].[column_master_keys] AS cmk
|
||
WHERE cmk.[name] = N'CMK_ESB_ZertifikatManager'
|
||
)
|
||
BEGIN
|
||
DROP COLUMN MASTER KEY [CMK_ESB_ZertifikatManager];
|
||
END;
|
||
GO
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
/* Always Encrypted – neu anlegen */
|
||
/* -------------------------------------------------------------------------- */
|
||
|
||
CREATE COLUMN MASTER KEY [CMK_ESB_ZertifikatManager]
|
||
WITH
|
||
(
|
||
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
|
||
KEY_PATH = N'CurrentUser/My/95E906CCC9C5F227969877CF3BD46D2AD7168853'
|
||
);
|
||
GO
|
||
|
||
CREATE COLUMN ENCRYPTION KEY [CEK_ESB_ZertifikatManager]
|
||
WITH VALUES
|
||
(
|
||
COLUMN_MASTER_KEY = [CMK_ESB_ZertifikatManager],
|
||
ALGORITHM = 'RSA_OAEP',
|
||
ENCRYPTED_VALUE = 0x017000630075007200720065006E00740075007300650072002F006D0079002F003900350065003900300036006300630063003900630035006600320032003700390036003900380037003700630066003300620064003400360064003200610064003700310036003800380035003300000063875F8F9E6CA28F5C6AB80295C9526F00C6708A9E3D8EEF172189C88A445F21D08CA11BCA77B3460C8F109A0C30B3CF9E3E7007BC4EACE9563591014C6FDE01F15E72F6992C7862F1C22C7E69D00418B26E700F95112E870A17C4510891AE687B522F71A58E70DFAE211F6B233628FB17E87F2837726E4801CB45A2357CE57194239950B19930CE5C8B7AF9A0CFF86AFA52DF97D392C77DE694ECA336A0DBCB6E5157AF8713636D90D121BDA1CBC4909C3A1102BE9FE561321FED761A672D2E534A5C94391704C79D007804DE628961BB2A4744D88E975C8287459F2D7D42F643078CEFF8842DFE4C826CF4B47BE2A59DC65D495F39BE45AA583E2784DE039F
|
||
);
|
||
GO
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
/* Company */
|
||
/* -------------------------------------------------------------------------- */
|
||
|
||
IF OBJECT_ID(N'[dbo].[Company]', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE [dbo].[Company]
|
||
(
|
||
[CompanyId] [int] IDENTITY(1, 1) NOT NULL,
|
||
[CompanyCode] [nvarchar](30) NOT NULL,
|
||
[CompanyName] [nvarchar](200) NOT NULL,
|
||
[IsActive] [bit] NOT NULL,
|
||
[CreationDateTime] [datetime2](3) NOT NULL,
|
||
[CreatedBy] [nvarchar](128) NOT NULL,
|
||
[ModifiedDateTime] [datetime2](3) NULL,
|
||
[ModifiedBy] [nvarchar](128) NULL,
|
||
CONSTRAINT [pk_Company] PRIMARY KEY CLUSTERED ([CompanyId] ASC),
|
||
CONSTRAINT [uc_Company_CompanyCode] UNIQUE NONCLUSTERED ([CompanyCode] ASC)
|
||
);
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_Company_IsActive]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[Company]
|
||
ADD CONSTRAINT [dc_Company_IsActive]
|
||
DEFAULT ((1)) FOR [IsActive];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_Company_CreationDateTime]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[Company]
|
||
ADD CONSTRAINT [dc_Company_CreationDateTime]
|
||
DEFAULT (SYSUTCDATETIME()) FOR [CreationDateTime];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_Company_CreatedBy]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[Company]
|
||
ADD CONSTRAINT [dc_Company_CreatedBy]
|
||
DEFAULT (SUSER_SNAME()) FOR [CreatedBy];
|
||
END;
|
||
GO
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
/* Environment */
|
||
/* -------------------------------------------------------------------------- */
|
||
|
||
IF OBJECT_ID(N'[dbo].[Environment]', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE [dbo].[Environment]
|
||
(
|
||
[EnvironmentId] [int] IDENTITY(1, 1) NOT NULL,
|
||
[EnvironmentCode] [nvarchar](30) NOT NULL,
|
||
[EnvironmentName] [nvarchar](100) NOT NULL,
|
||
[SortOrder] [int] NOT NULL,
|
||
[IsActive] [bit] NOT NULL,
|
||
[CreationDateTime] [datetime2](3) NOT NULL,
|
||
[CreatedBy] [nvarchar](128) NOT NULL,
|
||
[ModifiedDateTime] [datetime2](3) NULL,
|
||
[ModifiedBy] [nvarchar](128) NULL,
|
||
CONSTRAINT [pk_Environment] PRIMARY KEY CLUSTERED ([EnvironmentId] ASC),
|
||
CONSTRAINT [uc_Environment_EnvironmentCode] UNIQUE NONCLUSTERED ([EnvironmentCode] ASC),
|
||
CONSTRAINT [uc_Environment_SortOrder] UNIQUE NONCLUSTERED ([SortOrder] ASC)
|
||
);
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_Environment_IsActive]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[Environment]
|
||
ADD CONSTRAINT [dc_Environment_IsActive]
|
||
DEFAULT ((1)) FOR [IsActive];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_Environment_CreationDateTime]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[Environment]
|
||
ADD CONSTRAINT [dc_Environment_CreationDateTime]
|
||
DEFAULT (SYSUTCDATETIME()) FOR [CreationDateTime];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_Environment_CreatedBy]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[Environment]
|
||
ADD CONSTRAINT [dc_Environment_CreatedBy]
|
||
DEFAULT (SUSER_SNAME()) FOR [CreatedBy];
|
||
END;
|
||
GO
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
/* SonicConnection */
|
||
/* -------------------------------------------------------------------------- */
|
||
|
||
IF OBJECT_ID(N'[dbo].[SonicConnection]', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE [dbo].[SonicConnection]
|
||
(
|
||
[SonicConnectionId] [int] IDENTITY(1, 1) NOT NULL,
|
||
[EnvironmentId] [int] NOT NULL,
|
||
[ConnectionName] [nvarchar](150) NOT NULL,
|
||
[ManagementHost] [nvarchar](255) NOT NULL,
|
||
[ManagementPort] [int] NOT NULL,
|
||
[DomainName] [nvarchar](150) NOT NULL,
|
||
[ConnectionProtocol] [nvarchar](20) NOT NULL,
|
||
[IsActive] [bit] NOT NULL,
|
||
[CreationDateTime] [datetime2](3) NOT NULL,
|
||
[CreatedBy] [nvarchar](128) NOT NULL,
|
||
[ModifiedDateTime] [datetime2](3) NULL,
|
||
[ModifiedBy] [nvarchar](128) NULL,
|
||
CONSTRAINT [pk_SonicConnection] PRIMARY KEY CLUSTERED ([SonicConnectionId] ASC),
|
||
CONSTRAINT [uc_SonicConnection_ConnectionName] UNIQUE NONCLUSTERED ([ConnectionName] ASC),
|
||
CONSTRAINT [fk_SonicConnection_EnvironmentId]
|
||
FOREIGN KEY ([EnvironmentId])
|
||
REFERENCES [dbo].[Environment] ([EnvironmentId]),
|
||
CONSTRAINT [cc_SonicConnection_ManagementPort]
|
||
CHECK ([ManagementPort] >= (1) AND [ManagementPort] <= (65535))
|
||
);
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicConnection_ConnectionProtocol]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicConnection]
|
||
ADD CONSTRAINT [dc_SonicConnection_ConnectionProtocol]
|
||
DEFAULT (N'tcp') FOR [ConnectionProtocol];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicConnection_IsActive]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicConnection]
|
||
ADD CONSTRAINT [dc_SonicConnection_IsActive]
|
||
DEFAULT ((1)) FOR [IsActive];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicConnection_CreationDateTime]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicConnection]
|
||
ADD CONSTRAINT [dc_SonicConnection_CreationDateTime]
|
||
DEFAULT (SYSUTCDATETIME()) FOR [CreationDateTime];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicConnection_CreatedBy]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicConnection]
|
||
ADD CONSTRAINT [dc_SonicConnection_CreatedBy]
|
||
DEFAULT (SUSER_SNAME()) FOR [CreatedBy];
|
||
END;
|
||
GO
|
||
|
||
IF NOT EXISTS
|
||
(
|
||
SELECT 1
|
||
FROM [sys].[indexes] AS i
|
||
WHERE i.[object_id] = OBJECT_ID(N'[dbo].[SonicConnection]')
|
||
AND i.[name] = N'idx_SonicConnection_EnvironmentActive'
|
||
)
|
||
BEGIN
|
||
CREATE NONCLUSTERED INDEX [idx_SonicConnection_EnvironmentActive]
|
||
ON [dbo].[SonicConnection]
|
||
(
|
||
[EnvironmentId] ASC,
|
||
[IsActive] ASC
|
||
);
|
||
END;
|
||
GO
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
/* SonicCredential (Always Encrypted) */
|
||
/* -------------------------------------------------------------------------- */
|
||
|
||
IF OBJECT_ID(N'[dbo].[SonicCredential]', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE [dbo].[SonicCredential]
|
||
(
|
||
[SonicCredentialId] [int] IDENTITY(1, 1) NOT NULL,
|
||
[SonicConnectionId] [int] NOT NULL,
|
||
[CredentialName] [nvarchar](150) NOT NULL,
|
||
[CredentialUserName] [nvarchar](256)
|
||
COLLATE Latin1_General_BIN2
|
||
ENCRYPTED WITH
|
||
(
|
||
COLUMN_ENCRYPTION_KEY = [CEK_ESB_ZertifikatManager],
|
||
ENCRYPTION_TYPE = Randomized,
|
||
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
|
||
) NULL,
|
||
[CredentialSecret] [nvarchar](512)
|
||
COLLATE Latin1_General_BIN2
|
||
ENCRYPTED WITH
|
||
(
|
||
COLUMN_ENCRYPTION_KEY = [CEK_ESB_ZertifikatManager],
|
||
ENCRYPTION_TYPE = Randomized,
|
||
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
|
||
) NULL,
|
||
[IsDefault] [bit] NOT NULL,
|
||
[IsActive] [bit] NOT NULL,
|
||
[CreationDateTime] [datetime2](3) NOT NULL,
|
||
[CreatedBy] [nvarchar](128) NOT NULL,
|
||
[ModifiedDateTime] [datetime2](3) NULL,
|
||
[ModifiedBy] [nvarchar](128) NULL,
|
||
CONSTRAINT [pk_SonicCredential] PRIMARY KEY CLUSTERED ([SonicCredentialId] ASC),
|
||
CONSTRAINT [uc_SonicCredential_CredentialName]
|
||
UNIQUE NONCLUSTERED ([SonicConnectionId] ASC, [CredentialName] ASC),
|
||
CONSTRAINT [fk_SonicCredential_SonicConnectionId]
|
||
FOREIGN KEY ([SonicConnectionId])
|
||
REFERENCES [dbo].[SonicConnection] ([SonicConnectionId])
|
||
);
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicCredential_IsDefault]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicCredential]
|
||
ADD CONSTRAINT [dc_SonicCredential_IsDefault]
|
||
DEFAULT ((0)) FOR [IsDefault];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicCredential_IsActive]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicCredential]
|
||
ADD CONSTRAINT [dc_SonicCredential_IsActive]
|
||
DEFAULT ((1)) FOR [IsActive];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicCredential_CreationDateTime]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicCredential]
|
||
ADD CONSTRAINT [dc_SonicCredential_CreationDateTime]
|
||
DEFAULT (SYSUTCDATETIME()) FOR [CreationDateTime];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicCredential_CreatedBy]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicCredential]
|
||
ADD CONSTRAINT [dc_SonicCredential_CreatedBy]
|
||
DEFAULT (SUSER_SNAME()) FOR [CreatedBy];
|
||
END;
|
||
GO
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
/* SonicContainer */
|
||
/* -------------------------------------------------------------------------- */
|
||
|
||
IF OBJECT_ID(N'[dbo].[SonicContainer]', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE [dbo].[SonicContainer]
|
||
(
|
||
[SonicContainerId] [int] IDENTITY(1, 1) NOT NULL,
|
||
[CompanyId] [int] NOT NULL,
|
||
[SonicConnectionId] [int] NOT NULL,
|
||
[ContainerName] [nvarchar](250) NOT NULL,
|
||
[ContainerDisplayName] [nvarchar](250) NULL,
|
||
[CertificateCheckUrl] [nvarchar](500) NULL,
|
||
[RestartTimeoutSeconds] [int] NOT NULL,
|
||
[IsActive] [bit] NOT NULL,
|
||
[CreationDateTime] [datetime2](3) NOT NULL,
|
||
[CreatedBy] [nvarchar](128) NOT NULL,
|
||
[ModifiedDateTime] [datetime2](3) NULL,
|
||
[ModifiedBy] [nvarchar](128) NULL,
|
||
CONSTRAINT [pk_SonicContainer] PRIMARY KEY CLUSTERED ([SonicContainerId] ASC),
|
||
CONSTRAINT [uc_SonicContainer_CompanyContainer]
|
||
UNIQUE NONCLUSTERED ([CompanyId] ASC, [ContainerName] ASC),
|
||
CONSTRAINT [fk_SonicContainer_CompanyId]
|
||
FOREIGN KEY ([CompanyId])
|
||
REFERENCES [dbo].[Company] ([CompanyId]),
|
||
CONSTRAINT [fk_SonicContainer_SonicConnectionId]
|
||
FOREIGN KEY ([SonicConnectionId])
|
||
REFERENCES [dbo].[SonicConnection] ([SonicConnectionId]),
|
||
CONSTRAINT [cc_SonicContainer_RestartTimeoutSeconds]
|
||
CHECK ([RestartTimeoutSeconds] >= (10) AND [RestartTimeoutSeconds] <= (3600))
|
||
);
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicContainer_RestartTimeoutSeconds]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicContainer]
|
||
ADD CONSTRAINT [dc_SonicContainer_RestartTimeoutSeconds]
|
||
DEFAULT ((180)) FOR [RestartTimeoutSeconds];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicContainer_IsActive]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicContainer]
|
||
ADD CONSTRAINT [dc_SonicContainer_IsActive]
|
||
DEFAULT ((1)) FOR [IsActive];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicContainer_CreationDateTime]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicContainer]
|
||
ADD CONSTRAINT [dc_SonicContainer_CreationDateTime]
|
||
DEFAULT (SYSUTCDATETIME()) FOR [CreationDateTime];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_SonicContainer_CreatedBy]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[SonicContainer]
|
||
ADD CONSTRAINT [dc_SonicContainer_CreatedBy]
|
||
DEFAULT (SUSER_SNAME()) FOR [CreatedBy];
|
||
END;
|
||
GO
|
||
|
||
IF NOT EXISTS
|
||
(
|
||
SELECT 1
|
||
FROM [sys].[indexes] AS i
|
||
WHERE i.[object_id] = OBJECT_ID(N'[dbo].[SonicContainer]')
|
||
AND i.[name] = N'idx_SonicContainer_ConnectionActive'
|
||
)
|
||
BEGIN
|
||
CREATE NONCLUSTERED INDEX [idx_SonicContainer_ConnectionActive]
|
||
ON [dbo].[SonicContainer]
|
||
(
|
||
[SonicConnectionId] ASC,
|
||
[IsActive] ASC
|
||
);
|
||
END;
|
||
GO
|
||
|
||
IF NOT EXISTS
|
||
(
|
||
SELECT 1
|
||
FROM [sys].[indexes] AS i
|
||
WHERE i.[object_id] = OBJECT_ID(N'[dbo].[SonicContainer]')
|
||
AND i.[name] = N'idx_SonicContainer_CompanyActive'
|
||
)
|
||
BEGIN
|
||
CREATE NONCLUSTERED INDEX [idx_SonicContainer_CompanyActive]
|
||
ON [dbo].[SonicContainer]
|
||
(
|
||
[CompanyId] ASC,
|
||
[IsActive] ASC
|
||
);
|
||
END;
|
||
GO
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
/* CertificateTarget */
|
||
/* -------------------------------------------------------------------------- */
|
||
|
||
IF OBJECT_ID(N'[dbo].[CertificateTarget]', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE [dbo].[CertificateTarget]
|
||
(
|
||
[CertificateTargetId] [int] IDENTITY(1, 1) NOT NULL,
|
||
[SonicContainerId] [int] NOT NULL,
|
||
[TargetDirectory] [nvarchar](500) NOT NULL,
|
||
[TargetFileName] [nvarchar](260) NOT NULL,
|
||
[BackupEnabled] [bit] NOT NULL,
|
||
[BackupDirectoryName] [nvarchar](100) NOT NULL,
|
||
[IsActive] [bit] NOT NULL,
|
||
[CreationDateTime] [datetime2](3) NOT NULL,
|
||
[CreatedBy] [nvarchar](128) NOT NULL,
|
||
[ModifiedDateTime] [datetime2](3) NULL,
|
||
[ModifiedBy] [nvarchar](128) NULL,
|
||
CONSTRAINT [pk_CertificateTarget] PRIMARY KEY CLUSTERED ([CertificateTargetId] ASC),
|
||
CONSTRAINT [uc_CertificateTarget_Destination]
|
||
UNIQUE NONCLUSTERED
|
||
(
|
||
[SonicContainerId] ASC,
|
||
[TargetDirectory] ASC,
|
||
[TargetFileName] ASC
|
||
),
|
||
CONSTRAINT [fk_CertificateTarget_SonicContainerId]
|
||
FOREIGN KEY ([SonicContainerId])
|
||
REFERENCES [dbo].[SonicContainer] ([SonicContainerId])
|
||
);
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_CertificateTarget_BackupEnabled]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[CertificateTarget]
|
||
ADD CONSTRAINT [dc_CertificateTarget_BackupEnabled]
|
||
DEFAULT ((1)) FOR [BackupEnabled];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_CertificateTarget_BackupDirectoryName]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[CertificateTarget]
|
||
ADD CONSTRAINT [dc_CertificateTarget_BackupDirectoryName]
|
||
DEFAULT (N'Backup') FOR [BackupDirectoryName];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_CertificateTarget_IsActive]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[CertificateTarget]
|
||
ADD CONSTRAINT [dc_CertificateTarget_IsActive]
|
||
DEFAULT ((1)) FOR [IsActive];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_CertificateTarget_CreationDateTime]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[CertificateTarget]
|
||
ADD CONSTRAINT [dc_CertificateTarget_CreationDateTime]
|
||
DEFAULT (SYSUTCDATETIME()) FOR [CreationDateTime];
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[dc_CertificateTarget_CreatedBy]', N'D') IS NULL
|
||
BEGIN
|
||
ALTER TABLE [dbo].[CertificateTarget]
|
||
ADD CONSTRAINT [dc_CertificateTarget_CreatedBy]
|
||
DEFAULT (SUSER_SNAME()) FOR [CreatedBy];
|
||
END;
|
||
GO
|
||
|
||
IF NOT EXISTS
|
||
(
|
||
SELECT 1
|
||
FROM [sys].[indexes] AS i
|
||
WHERE i.[object_id] = OBJECT_ID(N'[dbo].[CertificateTarget]')
|
||
AND i.[name] = N'idx_CertificateTarget_ContainerActive'
|
||
)
|
||
BEGIN
|
||
CREATE NONCLUSTERED INDEX [idx_CertificateTarget_ContainerActive]
|
||
ON [dbo].[CertificateTarget]
|
||
(
|
||
[SonicContainerId] ASC,
|
||
[IsActive] ASC
|
||
);
|
||
END;
|
||
GO
|