123123/ZA.CoreService.ESBCertificateManager/Sql/001_CreateSchema.sql

/*
  ESB Certificate Manager – Zielkonfiguration (SQL Server)
  Offline vorbereitet; zur späteren Nutzung auf dem Ziel-SQL-Server ausführen.
*/

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 DeploymentTarget
        DomainName            NVARCHAR(128)  NOT NULL,  -- Sonic-Domain-Name, z.B. "proalpha-test"
        ManagementUrl         NVARCHAR(512)  NOT NULL,  -- HTTP-URL der Management Console, z.B. "http://host:8080"
        ApiBasePath           NVARCHAR(128)  NOT NULL CONSTRAINT DF_SonicConnection_ApiBasePath DEFAULT (N'/api/v1'),
        Username              NVARCHAR(128)  NOT NULL,
        -- Passwort wird in der Anwendung verschlüsselt gespeichert; hier nur als Verweis
        PasswordHash          NVARCHAR(512)  NOT NULL CONSTRAINT DF_SonicConnection_PwdHash DEFAULT (N''),
        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_IsActive DEFAULT (1),
        CONSTRAINT PK_SonicConnection PRIMARY KEY CLUSTERED (Id),
        CONSTRAINT UQ_SonicConnection_Name UNIQUE (Name)
    );
END
GO

IF OBJECT_ID(N'dbo.DeploymentTarget', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.DeploymentTarget
    (
        Id                    INT            NOT NULL IDENTITY(1, 1),
        Name                  NVARCHAR(128)  NOT NULL,
        Environment           NVARCHAR(64)   NOT NULL,
        IsActive              BIT            NOT NULL CONSTRAINT DF_DeploymentTarget_IsActive DEFAULT (1),
        TargetDirectory       NVARCHAR(512)  NOT NULL,
        CertificateFileName   NVARCHAR(260)  NOT NULL,
        ContainerName         NVARCHAR(128)  NOT NULL CONSTRAINT DF_DeploymentTarget_Container DEFAULT (N''),
        RestartType           NVARCHAR(32)   NOT NULL CONSTRAINT DF_DeploymentTarget_RestartType DEFAULT (N'None'),
        RestartCommand        NVARCHAR(512)  NOT NULL CONSTRAINT DF_DeploymentTarget_RestartCmd DEFAULT (N''),
        RestartArguments      NVARCHAR(1024) NOT NULL CONSTRAINT DF_DeploymentTarget_RestartArgs DEFAULT (N''),
        RestartTimeoutSeconds INT            NOT NULL CONSTRAINT DF_DeploymentTarget_RestartTimeout DEFAULT (60),
        -- Sonic ESB Management
        SonicConnectionName   NVARCHAR(128)  NOT NULL CONSTRAINT DF_DeploymentTarget_SonicConn DEFAULT (N''),
        XapiSourcePath        NVARCHAR(512)  NOT NULL CONSTRAINT DF_DeploymentTarget_XapiSrc DEFAULT (N''),
        -- TLS-Probe
        TlsHost               NVARCHAR(255)  NOT NULL CONSTRAINT DF_DeploymentTarget_TlsHost DEFAULT (N''),
        TlsPort               INT            NULL,
        SortOrder             INT            NOT NULL CONSTRAINT DF_DeploymentTarget_SortOrder DEFAULT (0),
        CONSTRAINT PK_DeploymentTarget PRIMARY KEY CLUSTERED (Id),
        CONSTRAINT CK_DeploymentTarget_RestartType CHECK (
            RestartType IN (N'None', N'Command', N'SonicContainer', N'SonicContainerWithXapi')
        )
    );
END
ELSE
BEGIN
    -- Neue Spalten zu bestehender Tabelle hinzufügen (idempotent)
    IF NOT EXISTS (SELECT 1 FROM sys.columns
                   WHERE object_id = OBJECT_ID(N'dbo.DeploymentTarget')
                     AND name = N'SonicConnectionName')
    BEGIN
        ALTER TABLE dbo.DeploymentTarget
            ADD SonicConnectionName NVARCHAR(128) NOT NULL
                CONSTRAINT DF_DeploymentTarget_SonicConn DEFAULT (N'');
    END

    IF NOT EXISTS (SELECT 1 FROM sys.columns
                   WHERE object_id = OBJECT_ID(N'dbo.DeploymentTarget')
                     AND name = N'XapiSourcePath')
    BEGIN
        ALTER TABLE dbo.DeploymentTarget
            ADD XapiSourcePath NVARCHAR(512) NOT NULL
                CONSTRAINT DF_DeploymentTarget_XapiSrc DEFAULT (N'');
    END

    -- CHECK-Constraint um neue RestartType-Werte erweitern
    IF EXISTS (SELECT 1 FROM sys.check_constraints
               WHERE parent_object_id = OBJECT_ID(N'dbo.DeploymentTarget')
                 AND name = N'CK_DeploymentTarget_RestartType')
    BEGIN
        ALTER TABLE dbo.DeploymentTarget DROP CONSTRAINT CK_DeploymentTarget_RestartType;
        ALTER TABLE dbo.DeploymentTarget ADD CONSTRAINT CK_DeploymentTarget_RestartType
            CHECK (RestartType IN (N'None', N'Command', N'SonicContainer', N'SonicContainerWithXapi'));
    END
END
GO

IF OBJECT_ID(N'dbo.DeploymentRunHistory', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.DeploymentRunHistory
    (
        Id             BIGINT         NOT NULL IDENTITY(1, 1),
        StartedAtUtc   DATETIME2(3)   NOT NULL,
        FinishedAtUtc  DATETIME2(3)   NULL,
        UserName       NVARCHAR(128)  NOT NULL,
        MachineName    NVARCHAR(128)  NOT NULL CONSTRAINT DF_DeploymentRunHistory_Machine DEFAULT (N''),
        OverallSuccess BIT            NULL,
        Summary        NVARCHAR(2000) NULL,
        CONSTRAINT PK_DeploymentRunHistory PRIMARY KEY CLUSTERED (Id)
    );
END
ELSE
BEGIN
    IF NOT EXISTS (SELECT 1 FROM sys.columns
                   WHERE object_id = OBJECT_ID(N'dbo.DeploymentRunHistory')
                     AND name = N'MachineName')
    BEGIN
        ALTER TABLE dbo.DeploymentRunHistory
            ADD MachineName NVARCHAR(128) NOT NULL
                CONSTRAINT DF_DeploymentRunHistory_Machine DEFAULT (N'');
    END
END
GO

IF OBJECT_ID(N'dbo.DeploymentRunDetail', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.DeploymentRunDetail
    (
        Id         BIGINT         NOT NULL IDENTITY(1, 1),
        RunId      BIGINT         NOT NULL,
        TargetId   INT            NOT NULL,
        TargetName NVARCHAR(128)  NOT NULL,
        Success    BIT            NOT NULL,
        StatusText NVARCHAR(256)  NOT NULL,
        Detail     NVARCHAR(2000) NULL,
        Steps      NVARCHAR(MAX)  NULL,
        CONSTRAINT PK_DeploymentRunDetail PRIMARY KEY CLUSTERED (Id),
        CONSTRAINT FK_DeploymentRunDetail_Run
            FOREIGN KEY (RunId) REFERENCES dbo.DeploymentRunHistory (Id)
    );
END
GO

/*
Beispiel-Insert:

INSERT INTO dbo.SonicConnection (Name, DomainName, ManagementUrl, Username, PasswordHash)
VALUES (N'DE-Test', N'proalpha-test', N'http://dekun-painwbdet:8080', N'Administrator', N'<encrypted>');

INSERT INTO dbo.DeploymentTarget
(
    Name, Environment, IsActive, TargetDirectory, CertificateFileName,
    ContainerName, RestartType, SonicConnectionName,
    TlsHost, TlsPort, SortOrder
)
VALUES
(
    N'DE-Test Container A', N'TEST', 1, N'\\share\esb\certs\a', N'esb-cert.cer',
    N'sonic-container-a', N'SonicContainer', N'DE-Test',
    N'dekun-painwbdet', 13070, 10
),
(
    N'DE-Test Container B (XApi)', N'TEST', 1, N'\\share\esb\certs\b', N'esb-cert.cer',
    N'sonic-container-b', N'SonicContainerWithXapi', N'DE-Test',
    N'dekun-painwbdet', 13070, 20
);
*/