194 lines
9.7 KiB
Transact-SQL
194 lines
9.7 KiB
Transact-SQL
/*
|
||
ESB Certificate Manager – vollständiges Datenbankschema v2
|
||
Idempotent; kann auf einem leeren Schema oder nach 001_CreateSchema.sql ausgeführt werden.
|
||
|
||
Tabellen:
|
||
dbo.SonicConnection – Sonic-ESB-Management-Instanzen
|
||
dbo.DeploymentTargets – Deployment-Ziele mit allen Konfigurationsfeldern
|
||
dbo.DeploymentRuns – Ein Eintrag pro Deployment-Lauf
|
||
dbo.DeploymentTargetResults – Detailergebnis pro Ziel und Lauf
|
||
|
||
Always Encrypted (optional):
|
||
Zur Nutzung von Always Encrypted auf der Spalte PasswordHash in SonicConnection
|
||
die Blöcke unterhalb des Kommentars "-- ALWAYS ENCRYPTED" auskommentieren
|
||
und den Schlüsselnamen anpassen.
|
||
*/
|
||
|
||
-- ============================================================
|
||
-- SonicConnection
|
||
-- ============================================================
|
||
IF OBJECT_ID(N'dbo.SonicConnection', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE dbo.SonicConnection
|
||
(
|
||
Id INT NOT NULL IDENTITY(1, 1),
|
||
Name NVARCHAR(128) NOT NULL, -- Eindeutiger Bezeichner, referenziert von DeploymentTargets
|
||
DomainName NVARCHAR(128) NOT NULL, -- Sonic-Domain, z.B. "proalpha-test"
|
||
ConnectionUrl NVARCHAR(512) NOT NULL, -- Sonic-Broker-URL, z.B. "tcp://dekun-painwbdet:13070"
|
||
ManagementHttpPort INT NOT NULL CONSTRAINT DF_SonicConnection_HttpPort DEFAULT (8080),
|
||
ApiBasePath NVARCHAR(128) NOT NULL CONSTRAINT DF_SonicConnection_ApiBase DEFAULT (N'/api/v1'),
|
||
Username NVARCHAR(128) NOT NULL,
|
||
PasswordHash NVARCHAR(512) NOT NULL CONSTRAINT DF_SonicConnection_PwdHash DEFAULT (N''),
|
||
-- ALWAYS ENCRYPTED: PasswordEncrypted NVARCHAR(512) ENCRYPTED WITH (
|
||
-- COLUMN_ENCRYPTION_KEY = CEK_SonicPwd,
|
||
-- ENCRYPTION_TYPE = DETERMINISTIC,
|
||
-- ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
|
||
-- ) NULL,
|
||
TimeoutSeconds INT NOT NULL CONSTRAINT DF_SonicConnection_Timeout DEFAULT (30),
|
||
PostRestartDelaySecs INT NOT NULL CONSTRAINT DF_SonicConnection_Delay DEFAULT (15),
|
||
IsActive BIT NOT NULL CONSTRAINT DF_SonicConnection_Active DEFAULT (1),
|
||
CONSTRAINT PK_SonicConnection PRIMARY KEY CLUSTERED (Id),
|
||
CONSTRAINT UQ_SonicConnection_Name UNIQUE (Name)
|
||
);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
-- ConnectionUrl-Spalte nachrüsten (Migration von ManagementUrl)
|
||
IF NOT EXISTS (SELECT 1 FROM sys.columns
|
||
WHERE object_id = OBJECT_ID(N'dbo.SonicConnection') AND name = N'ConnectionUrl')
|
||
BEGIN
|
||
ALTER TABLE dbo.SonicConnection ADD ConnectionUrl NVARCHAR(512) NOT NULL
|
||
CONSTRAINT DF_SonicConnection_ConnUrl DEFAULT (N'');
|
||
END
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.columns
|
||
WHERE object_id = OBJECT_ID(N'dbo.SonicConnection') AND name = N'ManagementHttpPort')
|
||
BEGIN
|
||
ALTER TABLE dbo.SonicConnection ADD ManagementHttpPort INT NOT NULL
|
||
CONSTRAINT DF_SonicConnection_HttpPort DEFAULT (8080);
|
||
END
|
||
END
|
||
GO
|
||
|
||
-- ============================================================
|
||
-- DeploymentTargets
|
||
-- ============================================================
|
||
IF OBJECT_ID(N'dbo.DeploymentTargets', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE dbo.DeploymentTargets
|
||
(
|
||
Id INT NOT NULL IDENTITY(1, 1),
|
||
Name NVARCHAR(100) NOT NULL,
|
||
Environment NVARCHAR(64) NOT NULL CONSTRAINT DF_DT_Env DEFAULT (N''),
|
||
IsActive BIT NOT NULL CONSTRAINT DF_DT_IsActive DEFAULT (1),
|
||
|
||
-- Zertifikat-Ablage
|
||
CertificateTargetPath NVARCHAR(500) NOT NULL,
|
||
CertificateFileName NVARCHAR(260) NOT NULL CONSTRAINT DF_DT_CertFile DEFAULT (N''),
|
||
|
||
-- Neustart-Konfiguration
|
||
RestartType NVARCHAR(30) NOT NULL CONSTRAINT DF_DT_RestartType DEFAULT (N'None'),
|
||
RestartHost NVARCHAR(255) NULL,
|
||
RestartCommand NVARCHAR(2000) NULL,
|
||
RestartArguments NVARCHAR(1024) NOT NULL CONSTRAINT DF_DT_RestartArgs DEFAULT (N''),
|
||
RestartTimeoutSeconds INT NOT NULL CONSTRAINT DF_DT_RestartTimeout DEFAULT (60),
|
||
|
||
-- Sonic ESB
|
||
SonicConnectionName NVARCHAR(128) NOT NULL CONSTRAINT DF_DT_SonicConn DEFAULT (N''),
|
||
ContainerName NVARCHAR(128) NOT NULL CONSTRAINT DF_DT_Container DEFAULT (N''),
|
||
XapiSourcePath NVARCHAR(512) NOT NULL CONSTRAINT DF_DT_XapiSrc DEFAULT (N''),
|
||
|
||
-- TLS-Probe
|
||
TlsHost NVARCHAR(255) NOT NULL CONSTRAINT DF_DT_TlsHost DEFAULT (N''),
|
||
TlsPort INT NOT NULL CONSTRAINT DF_DT_TlsPort DEFAULT (443),
|
||
TlsServerName NVARCHAR(255) NOT NULL CONSTRAINT DF_DT_TlsServerName DEFAULT (N''),
|
||
ExpectedFingerprint NVARCHAR(128) NULL,
|
||
|
||
SortOrder INT NOT NULL CONSTRAINT DF_DT_SortOrder DEFAULT (0),
|
||
|
||
CONSTRAINT PK_DeploymentTargets PRIMARY KEY CLUSTERED (Id),
|
||
CONSTRAINT CK_DeploymentTargets_RestartType CHECK (
|
||
RestartType IN (N'None', N'Command', N'SonicContainer', N'SonicContainerWithXapi')
|
||
)
|
||
);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
-- Neue Spalten idempotent nachrüsten
|
||
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.DeploymentTargets') AND name = N'TlsServerName')
|
||
ALTER TABLE dbo.DeploymentTargets ADD TlsServerName NVARCHAR(255) NOT NULL CONSTRAINT DF_DT_TlsServerName DEFAULT (N'');
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.DeploymentTargets') AND name = N'ExpectedFingerprint')
|
||
ALTER TABLE dbo.DeploymentTargets ADD ExpectedFingerprint NVARCHAR(128) NULL;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.DeploymentTargets') AND name = N'SonicConnectionName')
|
||
ALTER TABLE dbo.DeploymentTargets ADD SonicConnectionName NVARCHAR(128) NOT NULL CONSTRAINT DF_DT_SonicConn DEFAULT (N'');
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.DeploymentTargets') AND name = N'ContainerName')
|
||
ALTER TABLE dbo.DeploymentTargets ADD ContainerName NVARCHAR(128) NOT NULL CONSTRAINT DF_DT_Container DEFAULT (N'');
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.DeploymentTargets') AND name = N'XapiSourcePath')
|
||
ALTER TABLE dbo.DeploymentTargets ADD XapiSourcePath NVARCHAR(512) NOT NULL CONSTRAINT DF_DT_XapiSrc DEFAULT (N'');
|
||
END
|
||
GO
|
||
|
||
-- ============================================================
|
||
-- DeploymentRuns (ein Datensatz pro Deployment-Lauf)
|
||
-- ============================================================
|
||
IF OBJECT_ID(N'dbo.DeploymentRuns', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE dbo.DeploymentRuns
|
||
(
|
||
Id UNIQUEIDENTIFIER NOT NULL,
|
||
StartedAtUtc DATETIME2(3) NOT NULL,
|
||
FinishedAtUtc DATETIME2(3) NULL,
|
||
SourceFile NVARCHAR(500) NOT NULL,
|
||
SourceFingerprint NVARCHAR(128) NOT NULL,
|
||
StartedBy NVARCHAR(255) NOT NULL,
|
||
MachineName NVARCHAR(255) NOT NULL CONSTRAINT DF_DR_Machine DEFAULT (N''),
|
||
OverallStatus NVARCHAR(30) NOT NULL, -- 'Running' | 'Success' | 'PartialFailure' | 'Failure'
|
||
ErrorMessage NVARCHAR(MAX) NULL,
|
||
CONSTRAINT PK_DeploymentRuns PRIMARY KEY CLUSTERED (Id)
|
||
);
|
||
END
|
||
GO
|
||
|
||
-- ============================================================
|
||
-- DeploymentTargetResults (ein Datensatz pro Ziel und Lauf)
|
||
-- ============================================================
|
||
IF OBJECT_ID(N'dbo.DeploymentTargetResults', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE dbo.DeploymentTargetResults
|
||
(
|
||
Id INT NOT NULL IDENTITY(1, 1),
|
||
DeploymentRunId UNIQUEIDENTIFIER NOT NULL,
|
||
TargetId INT NOT NULL,
|
||
TargetName NVARCHAR(128) NOT NULL,
|
||
StartedAtUtc DATETIME2(3) NOT NULL,
|
||
FinishedAtUtc DATETIME2(3) NULL,
|
||
CopySucceeded BIT NOT NULL CONSTRAINT DF_DTR_Copy DEFAULT (0),
|
||
RestartSucceeded BIT NOT NULL CONSTRAINT DF_DTR_Restart DEFAULT (0),
|
||
TlsSucceeded BIT NOT NULL CONSTRAINT DF_DTR_Tls DEFAULT (0),
|
||
ObservedFingerprint NVARCHAR(128) NULL,
|
||
Status NVARCHAR(30) NOT NULL,
|
||
ErrorMessage NVARCHAR(MAX) NULL,
|
||
CONSTRAINT PK_DeploymentTargetResults PRIMARY KEY CLUSTERED (Id),
|
||
CONSTRAINT FK_DTR_Run FOREIGN KEY (DeploymentRunId) REFERENCES dbo.DeploymentRuns (Id)
|
||
);
|
||
|
||
CREATE NONCLUSTERED INDEX IX_DTR_RunId ON dbo.DeploymentTargetResults (DeploymentRunId);
|
||
END
|
||
GO
|
||
|
||
-- ============================================================
|
||
-- Beispieldaten
|
||
-- ============================================================
|
||
/*
|
||
INSERT INTO dbo.SonicConnection (Name, DomainName, ConnectionUrl, Username, PasswordHash)
|
||
VALUES (N'DE-Test', N'proalpha-test', N'tcp://dekun-painwbdet:13070', N'Administrator', N'<encrypted>');
|
||
|
||
INSERT INTO dbo.DeploymentTargets
|
||
(Name, Environment, IsActive, CertificateTargetPath, CertificateFileName,
|
||
RestartType, SonicConnectionName, ContainerName,
|
||
TlsHost, TlsPort, TlsServerName, SortOrder)
|
||
VALUES
|
||
(N'DE-Test Container A', N'TEST', 1,
|
||
N'\\dekun-painwbdet\sonic\certs', N'server.pfx',
|
||
N'SonicContainer', N'DE-Test', N'sonic-container-a',
|
||
N'dekun-painwbdet', 443, N'esb-test.firma.local', 10),
|
||
(N'DE-Test Container B (XApi)', N'TEST', 1,
|
||
N'\\dekun-painwbdet\sonic\certs', N'server.pfx',
|
||
N'SonicContainerWithXapi', N'DE-Test', N'sonic-container-b',
|
||
N'dekun-painwbdet', 443, N'esb-test.firma.local', 20);
|
||
*/
|