3.8.0_to_4.0.0_mssql.sql

-- Adding new fields, creating/updating indices and foreign keys.

ALTER TABLE [COMPONENT] ADD
    [AUTHOR] VARCHAR(255),
    [BLAKE2B_256] VARCHAR(64),
    [BLAKE2B_384] VARCHAR(96),
    [BLAKE2B_512] VARCHAR(128),
    [BLAKE3] VARCHAR(255),
    [PROJECT_ID] BIGINT NOT NULL DEFAULT(0),
    [PUBLISHER] VARCHAR(255),
    [PURLCOORDINATES] VARCHAR(255),
    [SHA_384] VARCHAR(96),
    [SHA3_384] VARCHAR(96),
    [SWIDTAGID] VARCHAR(255);
GO

ALTER TABLE [COMPONENT] DROP CONSTRAINT
    [COMPONENT_FK1],
    [COMPONENT_FK2];
GO

DROP INDEX [COMPONENT_N49] ON [COMPONENT];
DROP INDEX [COMPONENT_N50] ON [COMPONENT];
GO

CREATE INDEX [COMPONENT_BLAKE2B_256_IDX] ON [COMPONENT] ([BLAKE2B_256]);
CREATE INDEX [COMPONENT_BLAKE2B_384_IDX] ON [COMPONENT] ([BLAKE2B_384]);
CREATE INDEX [COMPONENT_BLAKE2B_512_IDX] ON [COMPONENT] ([BLAKE2B_512]);
CREATE INDEX [COMPONENT_BLAKE3_IDX] ON [COMPONENT] ([BLAKE3]);
CREATE INDEX [COMPONENT_CPE_IDX] ON [COMPONENT] ([CPE]);
CREATE INDEX [COMPONENT_N49] ON [COMPONENT] ([PROJECT_ID]);
CREATE INDEX [COMPONENT_N50] ON [COMPONENT] ([PARENT_COMPONENT_ID]);
CREATE INDEX [COMPONENT_N51] ON [COMPONENT] ([LICENSE_ID]);
CREATE INDEX [COMPONENT_PURL_IDX] ON [COMPONENT] ([PURL]);
CREATE INDEX [COMPONENT_PURL_COORDINATES_IDX] ON [COMPONENT] ([PURLCOORDINATES]);
CREATE INDEX [COMPONENT_SHA384_IDX] ON [COMPONENT] ([SHA_384]);
CREATE INDEX [COMPONENT_SHA3_384_IDX] ON [COMPONENT] ([SHA3_384]);
CREATE INDEX [COMPONENT_SWID_TAGID_IDX] ON [COMPONENT] ([SWIDTAGID]);
GO

ALTER TABLE [COMPONENTANALYSISCACHE] ADD
    [RESULT] NVARCHAR(MAX);
GO

ALTER TABLE [DEPENDENCYMETRICS] ADD
    [POLICYVIOLATIONS_AUDITED] INT,
    [POLICYVIOLATIONS_FAIL] INT,
    [POLICYVIOLATIONS_INFO] INT,
    [POLICYVIOLATIONS_LICENSE_AUDITED] INT,
    [POLICYVIOLATIONS_LICENSE_TOTAL] INT,
    [POLICYVIOLATIONS_LICENSE_UNAUDITED] INT,
    [POLICYVIOLATIONS_OPERATIONAL_AUDITED] INT,
    [POLICYVIOLATIONS_OPERATIONAL_TOTAL] INT,
    [POLICYVIOLATIONS_OPERATIONAL_UNAUDITED] INT,
    [POLICYVIOLATIONS_SECURITY_AUDITED] INT,
    [POLICYVIOLATIONS_SECURITY_TOTAL] INT,
    [POLICYVIOLATIONS_SECURITY_UNAUDITED] INT,
    [POLICYVIOLATIONS_TOTAL] INT,
    [POLICYVIOLATIONS_UNAUDITED] INT,
    [POLICYVIOLATIONS_WARN] INT;
GO

UPDATE [DEPENDENCYMETRICS]
SET [POLICYVIOLATIONS_AUDITED] = 0,
    [POLICYVIOLATIONS_FAIL] = 0,
    [POLICYVIOLATIONS_INFO] = 0,
    [POLICYVIOLATIONS_LICENSE_AUDITED] = 0,
    [POLICYVIOLATIONS_LICENSE_TOTAL] = 0,
    [POLICYVIOLATIONS_LICENSE_UNAUDITED] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_AUDITED] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_TOTAL] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_UNAUDITED] = 0,
    [POLICYVIOLATIONS_SECURITY_AUDITED] = 0,
    [POLICYVIOLATIONS_SECURITY_TOTAL] = 0,
    [POLICYVIOLATIONS_SECURITY_UNAUDITED] = 0,
    [POLICYVIOLATIONS_TOTAL] = 0,
    [POLICYVIOLATIONS_UNAUDITED] = 0,
    [POLICYVIOLATIONS_WARN] = 0;
GO

CREATE TABLE [FINDINGATTRIBUTION] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [ALT_ID] VARCHAR(255),
    [ANALYZERIDENTITY] VARCHAR(255) NOT NULL,
    [ATTRIBUTED_ON] DATETIME2(7) NOT NULL DEFAULT GETDATE(),
    [COMPONENT_ID] BIGINT NOT NULL,
    [PROJECT_ID] BIGINT NOT NULL,
    [REFERENCE_URL] VARCHAR(255),
    [UUID] VARCHAR(36) NOT NULL,
    [VULNERABILITY_ID] BIGINT NOT NULL,
    CONSTRAINT [FINDINGATTRIBUTION_UUID_IDX] UNIQUE ([UUID]),
    CONSTRAINT [FINDINGATTRIBUTION_FK2] FOREIGN KEY ([PROJECT_ID]) REFERENCES [PROJECT] ([ID]),
    CONSTRAINT [FINDINGATTRIBUTION_FK3] FOREIGN KEY ([VULNERABILITY_ID]) REFERENCES [VULNERABILITY] ([ID])
);
GO

CREATE INDEX [FINDINGATTRIBUTION_N50] ON [FINDINGATTRIBUTION] ([VULNERABILITY_ID]);
CREATE INDEX [FINDINGATTRIBUTION_N51] ON [FINDINGATTRIBUTION] ([COMPONENT_ID]);
CREATE INDEX [FINDINGATTRIBUTION_COMPOUND_IDX] ON [FINDINGATTRIBUTION] ([COMPONENT_ID],[VULNERABILITY_ID]);
CREATE INDEX [FINDINGATTRIBUTION_N49] ON [FINDINGATTRIBUTION] ([PROJECT_ID]);
GO

CREATE TABLE [LICENSEGROUP] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [NAME] VARCHAR(255) NOT NULL,
    [RISKWEIGHT] INT NOT NULL,
    [UUID] VARCHAR(36) NOT NULL,
    CONSTRAINT [LICENSEGROUP_UUID_IDX] UNIQUE ([UUID])
);
GO

CREATE INDEX [LICENSEGROUP_NAME_IDX] ON [LICENSEGROUP] ([NAME]);
GO

CREATE TABLE [LICENSEGROUP_LICENSE] (
    [LICENSEGROUP_ID] BIGINT NOT NULL,
    [LICENSE_ID] BIGINT NOT NULL,
    CONSTRAINT [LICENSEGROUP_LICENSE_FK1] FOREIGN KEY ([LICENSEGROUP_ID]) REFERENCES LICENSEGROUP ([ID]),
    CONSTRAINT [LICENSEGROUP_LICENSE_FK2] FOREIGN KEY ([LICENSE_ID]) REFERENCES LICENSE ([ID])
);
GO

CREATE INDEX [LICENSEGROUP_LICENSE_N49] ON [LICENSEGROUP_LICENSE] ([LICENSE_ID]);
CREATE INDEX [LICENSEGROUP_LICENSE_N50] ON [LICENSEGROUP_LICENSE] ([LICENSEGROUP_ID]);
GO

CREATE TABLE [OIDCGROUP] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [NAME] VARCHAR(1024) NOT NULL,
    [UUID] VARCHAR(36) NOT NULL,
    CONSTRAINT [OIDCGROUP_UUID_IDX] UNIQUE ([UUID])
);
GO

CREATE TABLE [MAPPEDOIDCGROUP] (
    [ID] BIGINT NOT NULL IDENTITY,
    [GROUP_ID] BIGINT NOT NULL,
    [TEAM_ID] BIGINT NOT NULL,
    [UUID] VARCHAR(36) NOT NULL,
    PRIMARY KEY ([ID]),
    CONSTRAINT [MAPPEDOIDCGROUP_UUID_IDX] UNIQUE ([UUID]),
    CONSTRAINT [MAPPEDOIDCGROUP_U1] UNIQUE ([TEAM_ID],[GROUP_ID]),
    CONSTRAINT [MAPPEDOIDCGROUP_FK1] FOREIGN KEY ([GROUP_ID]) REFERENCES [OIDCGROUP] ([ID]),
    CONSTRAINT [MAPPEDOIDCGROUP_FK2] FOREIGN KEY ([TEAM_ID]) REFERENCES [TEAM] ([ID])
);
GO

CREATE INDEX [MAPPEDOIDCGROUP_N50] ON [MAPPEDOIDCGROUP] ([TEAM_ID]);
CREATE INDEX [MAPPEDOIDCGROUP_N49] ON [MAPPEDOIDCGROUP] ([GROUP_ID]);
GO

CREATE TABLE [OIDCUSER] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [SUBJECT_IDENTIFIER] VARCHAR(255),
    [USERNAME] VARCHAR(255) NOT NULL,
    CONSTRAINT [OIDCUSER_USERNAME_IDX] UNIQUE ([USERNAME])
);
GO

CREATE TABLE [OIDCUSERS_PERMISSIONS] (
    [PERMISSION_ID] BIGINT NOT NULL,
    [OIDCUSER_ID] BIGINT NOT NULL,
    CONSTRAINT [OIDCUSERS_PERMISSIONS_FK1] FOREIGN KEY ([PERMISSION_ID]) REFERENCES [PERMISSION] ([ID]),
    CONSTRAINT [OIDCUSERS_PERMISSIONS_FK2] FOREIGN KEY ([OIDCUSER_ID]) REFERENCES [OIDCUSER] ([ID])
);
GO

CREATE INDEX [OIDCUSERS_PERMISSIONS_N49] ON [OIDCUSERS_PERMISSIONS] ([PERMISSION_ID]);
CREATE INDEX [OIDCUSERS_PERMISSIONS_N50] ON [OIDCUSERS_PERMISSIONS] ([OIDCUSER_ID]);
GO

CREATE TABLE [OIDCUSERS_TEAMS] (
    [OIDCUSERS_ID] BIGINT NOT NULL,
    [TEAM_ID] BIGINT NOT NULL,
    CONSTRAINT [OIDCUSERS_TEAMS_FK1] FOREIGN KEY ([OIDCUSERS_ID]) REFERENCES [OIDCUSER] ([ID]),
    CONSTRAINT [OIDCUSERS_TEAMS_FK2] FOREIGN KEY ([TEAM_ID]) REFERENCES [TEAM] ([ID])
);
GO

CREATE INDEX [OIDCUSERS_TEAMS_N49] ON [OIDCUSERS_TEAMS] ([OIDCUSERS_ID]);
CREATE INDEX [OIDCUSERS_TEAMS_N50] ON [OIDCUSERS_TEAMS] ([TEAM_ID]);
GO

CREATE TABLE [POLICY] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [NAME] VARCHAR(255) NOT NULL,
    [OPERATOR] VARCHAR(255) NOT NULL,
    [UUID] VARCHAR(36) NOT NULL,
    [VIOLATIONSTATE] VARCHAR(255) NOT NULL,
    CONSTRAINT [POLICY_UUID_IDX] UNIQUE ([UUID])
);
GO

CREATE INDEX [POLICY_NAME_IDX] ON [POLICY] ([NAME]);
GO

CREATE TABLE [POLICYCONDITION] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [OPERATOR] VARCHAR(255) NOT NULL,
    [POLICY_ID] BIGINT NOT NULL,
    [SUBJECT] VARCHAR(255) NOT NULL,
    [UUID] VARCHAR(36) NOT NULL,
    [VALUE] VARCHAR(255) NOT NULL,
    CONSTRAINT [POLICYCONDITION_UUID_IDX] UNIQUE ([UUID]),
    CONSTRAINT [POLICYCONDITION_FK1] FOREIGN KEY ([POLICY_ID]) REFERENCES [POLICY] ([ID])
);
GO

CREATE INDEX [POLICYCONDITION_N49] ON [POLICYCONDITION] ([POLICY_ID]);
GO

CREATE TABLE [POLICYVIOLATION] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [COMPONENT_ID] BIGINT NOT NULL,
    [POLICYCONDITION_ID] BIGINT NOT NULL,
    [PROJECT_ID] BIGINT NOT NULL,
    [TEXT] VARCHAR(255),
    [TIMESTAMP] DATETIME2(7) NOT NULL DEFAULT GETDATE(),
    [TYPE] VARCHAR(255) NOT NULL,
    [UUID] VARCHAR(36) NOT NULL,
    CONSTRAINT [POLICYVIOLATION_UUID_IDX] UNIQUE ([UUID]),
    CONSTRAINT [POLICYVIOLATION_FK2] FOREIGN KEY ([POLICYCONDITION_ID]) REFERENCES [POLICYCONDITION] ([ID]),
    CONSTRAINT [POLICYVIOLATION_FK3] FOREIGN KEY ([PROJECT_ID]) REFERENCES [PROJECT] ([ID])
);
GO

CREATE INDEX [POLICYVIOLATION_PROJECT_IDX] ON [POLICYVIOLATION] ([PROJECT_ID]);
CREATE INDEX [POLICYVIOLATION_N49] ON [POLICYVIOLATION] ([POLICYCONDITION_ID]);
CREATE INDEX [POLICYVIOLATION_COMPONENT_IDX] ON [POLICYVIOLATION] ([COMPONENT_ID]);
GO

CREATE TABLE [POLICY_PROJECTS] (
    [POLICY_ID] BIGINT NOT NULL,
    [PROJECT_ID] BIGINT DEFAULT NULL,
    CONSTRAINT [POLICY_PROJECTS_FK1] FOREIGN KEY ([POLICY_ID]) REFERENCES [POLICY] ([ID]),
    CONSTRAINT [POLICY_PROJECTS_FK2] FOREIGN KEY ([PROJECT_ID]) REFERENCES [PROJECT] ([ID])
);
GO

CREATE INDEX [POLICY_PROJECTS_N49] ON [POLICY_PROJECTS] ([PROJECT_ID]);
CREATE INDEX [POLICY_PROJECTS_N50] ON [POLICY_PROJECTS] ([POLICY_ID]);
GO

ALTER TABLE [PORTFOLIOMETRICS] ADD
    [POLICYVIOLATIONS_AUDITED] INT,
    [POLICYVIOLATIONS_FAIL] INT,
    [POLICYVIOLATIONS_INFO] INT,
    [POLICYVIOLATIONS_LICENSE_AUDITED] INT,
    [POLICYVIOLATIONS_LICENSE_TOTAL] INT,
    [POLICYVIOLATIONS_LICENSE_UNAUDITED] INT,
    [POLICYVIOLATIONS_OPERATIONAL_AUDITED] INT,
    [POLICYVIOLATIONS_OPERATIONAL_TOTAL] INT,
    [POLICYVIOLATIONS_OPERATIONAL_UNAUDITED] INT,
    [POLICYVIOLATIONS_SECURITY_AUDITED] INT,
    [POLICYVIOLATIONS_SECURITY_TOTAL] INT,
    [POLICYVIOLATIONS_SECURITY_UNAUDITED] INT,
    [POLICYVIOLATIONS_TOTAL] INT,
    [POLICYVIOLATIONS_UNAUDITED] INT,
    [POLICYVIOLATIONS_WARN] INT;
GO

ALTER TABLE [PORTFOLIOMETRICS] DROP COLUMN
    [DEPENDENCIES],
    [VULNERABLEDEPENDENCIES];
GO

UPDATE [PORTFOLIOMETRICS]
SET [POLICYVIOLATIONS_AUDITED] = 0,
    [POLICYVIOLATIONS_FAIL] = 0,
    [POLICYVIOLATIONS_INFO] = 0,
    [POLICYVIOLATIONS_LICENSE_AUDITED] = 0,
    [POLICYVIOLATIONS_LICENSE_TOTAL] = 0,
    [POLICYVIOLATIONS_LICENSE_UNAUDITED] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_AUDITED] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_TOTAL] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_UNAUDITED] = 0,
    [POLICYVIOLATIONS_SECURITY_AUDITED] = 0,
    [POLICYVIOLATIONS_SECURITY_TOTAL] = 0,
    [POLICYVIOLATIONS_SECURITY_UNAUDITED] = 0,
    [POLICYVIOLATIONS_TOTAL] = 0,
    [POLICYVIOLATIONS_UNAUDITED] = 0,
    [POLICYVIOLATIONS_WARN] = 0;
GO

ALTER TABLE [PROJECT] ADD
    [AUTHOR] VARCHAR(255),
    [CLASSIFIER] VARCHAR(255),
    [CPE] VARCHAR(255),
    [GROUP] VARCHAR(255),
    [PUBLISHER] VARCHAR(255),
    [SWIDTAGID] VARCHAR(255);
GO

CREATE INDEX [PROJECT_CLASSIFIER_IDX] ON [PROJECT] ([CLASSIFIER]);
CREATE INDEX [PROJECT_CPE_IDX] ON [PROJECT] ([CPE]);
CREATE INDEX [PROJECT_GROUP_IDX] ON [PROJECT] ([GROUP]);
CREATE INDEX [PROJECT_PURL_IDX] ON [PROJECT] ([PURL]);
CREATE INDEX [PROJECT_SWID_TAGID_IDX] ON [PROJECT] ([SWIDTAGID]);
GO

ALTER TABLE [PROJECTMETRICS] ADD
    [POLICYVIOLATIONS_AUDITED] INT,
    [POLICYVIOLATIONS_FAIL] INT,
    [POLICYVIOLATIONS_INFO] INT,
    [POLICYVIOLATIONS_LICENSE_AUDITED] INT,
    [POLICYVIOLATIONS_LICENSE_TOTAL] INT,
    [POLICYVIOLATIONS_LICENSE_UNAUDITED] INT,
    [POLICYVIOLATIONS_OPERATIONAL_AUDITED] INT,
    [POLICYVIOLATIONS_OPERATIONAL_TOTAL] INT,
    [POLICYVIOLATIONS_OPERATIONAL_UNAUDITED] INT,
    [POLICYVIOLATIONS_SECURITY_AUDITED] INT,
    [POLICYVIOLATIONS_SECURITY_TOTAL] INT,
    [POLICYVIOLATIONS_SECURITY_UNAUDITED] INT,
    [POLICYVIOLATIONS_TOTAL] INT,
    [POLICYVIOLATIONS_UNAUDITED] INT,
    [POLICYVIOLATIONS_WARN] INT;
GO

UPDATE [PROJECTMETRICS]
SET [POLICYVIOLATIONS_AUDITED] = 0,
    [POLICYVIOLATIONS_FAIL] = 0,
    [POLICYVIOLATIONS_INFO] = 0,
    [POLICYVIOLATIONS_LICENSE_AUDITED] = 0,
    [POLICYVIOLATIONS_LICENSE_TOTAL] = 0,
    [POLICYVIOLATIONS_LICENSE_UNAUDITED] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_AUDITED] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_TOTAL] = 0,
    [POLICYVIOLATIONS_OPERATIONAL_UNAUDITED] = 0,
    [POLICYVIOLATIONS_SECURITY_AUDITED] = 0,
    [POLICYVIOLATIONS_SECURITY_TOTAL] = 0,
    [POLICYVIOLATIONS_SECURITY_UNAUDITED] = 0,
    [POLICYVIOLATIONS_TOTAL] = 0,
    [POLICYVIOLATIONS_UNAUDITED] = 0,
    [POLICYVIOLATIONS_WARN] = 0;
GO

DECLARE @DropScansComponentsForeignKey NVARCHAR(100) = N'
ALTER TABLE [SCANS_COMPONENTS] DROP CONSTRAINT
    [SCANS_COMPONENTS_FK2];
';
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SCANS_COMPONENTS')
BEGIN
    EXEC(@DropScansComponentsForeignKey);
END
GO

CREATE TABLE [VIOLATIONANALYSIS] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [STATE] VARCHAR(255) NOT NULL,
    [COMPONENT_ID] BIGINT DEFAULT NULL,
    [POLICYVIOLATION_ID] BIGINT NOT NULL,
    [PROJECT_ID] BIGINT DEFAULT NULL,
    [SUPPRESSED] BINARY(1) NOT NULL,
    CONSTRAINT [VIOLATIONANALYSIS_COMPOSITE_IDX] UNIQUE ([PROJECT_ID],[COMPONENT_ID],[POLICYVIOLATION_ID]),
    CONSTRAINT [VIOLATIONANALYSIS_FK2] FOREIGN KEY ([POLICYVIOLATION_ID]) REFERENCES [POLICYVIOLATION] ([ID]),
    CONSTRAINT [VIOLATIONANALYSIS_FK3] FOREIGN KEY ([PROJECT_ID]) REFERENCES [PROJECT] ([ID])
);
GO

CREATE INDEX [VIOLATIONANALYSIS_N49] ON [VIOLATIONANALYSIS] ([COMPONENT_ID]);
CREATE INDEX [VIOLATIONANALYSIS_N51] ON [VIOLATIONANALYSIS] ([POLICYVIOLATION_ID]);
CREATE INDEX [VIOLATIONANALYSIS_N50] ON [VIOLATIONANALYSIS] ([PROJECT_ID]);
GO

CREATE TABLE [VIOLATIONANALYSISCOMMENT] (
    [ID] BIGINT NOT NULL IDENTITY PRIMARY KEY,
    [COMMENT] VARCHAR(max) NOT NULL,
    [COMMENTER] VARCHAR(255),
    [TIMESTAMP] DATETIME2(7) NOT NULL DEFAULT GETDATE(),
    [VIOLATIONANALYSIS_ID] BIGINT NOT NULL,
    CONSTRAINT [VIOLATIONANALYSISCOMMENT_FK1] FOREIGN KEY ([VIOLATIONANALYSIS_ID]) REFERENCES [VIOLATIONANALYSIS] ([ID])
);
GO

CREATE INDEX [VIOLATIONANALYSISCOMMENT_N49] ON [VIOLATIONANALYSISCOMMENT] ([VIOLATIONANALYSIS_ID]);
GO

ALTER TABLE [VULNERABILITY] ADD
    [FRIENDLYVULNID] VARCHAR(255);
GO

DROP TABLE [BOMS_COMPONENTS];
GO

DROP TABLE [COMPONENTMETRICS];
GO

DROP TABLE [CPEREFERENCE];
GO

-- Find dangling components that no project depends on anymore and remove them

CREATE PROCEDURE cleanup_components
AS
BEGIN
    SELECT c.[ID]
    INTO #DanglingComponents
    FROM [COMPONENT] c
    LEFT JOIN [DEPENDENCY] d ON c.[ID] = d.[COMPONENT_ID]
    WHERE d.[COMPONENT_ID] IS NULL;

    DELETE ac
    FROM [ANALYSISCOMMENT] ac
    LEFT JOIN [ANALYSIS] a ON ac.[ANALYSIS_ID] = a.[ID]
    WHERE a.[COMPONENT_ID] IN (SELECT d.[ID] FROM #DanglingComponents d);

    DELETE FROM [ANALYSIS] WHERE [COMPONENT_ID] IN (SELECT d.[ID] FROM #DanglingComponents d);
    DELETE FROM [COMPONENTS_VULNERABILITIES] WHERE [COMPONENT_ID] IN (SELECT d.[ID] FROM #DanglingComponents d);
    DELETE FROM [DEPENDENCYMETRICS] WHERE [COMPONENT_ID] IN (SELECT d.[ID] FROM #DanglingComponents d);
    DELETE FROM [COMPONENT] WHERE [ID] IN (SELECT d.[ID] FROM #DanglingComponents d);

    -- SCANS_COMPONENTS may not exist (https://github.com/DependencyTrack/dependency-track/blob/master/docs/_posts/2019-12-16-v3.7.0.md)
    DECLARE @DeleteFromScansComponents NVARCHAR(100) = N'
    DELETE FROM [SCANS_COMPONENTS] WHERE [COMPONENT_ID] IN (SELECT d.[ID] FROM #DanglingComponents d);
    ';
    IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SCANS_COMPONENTS')
    BEGIN
        EXEC(@DeleteFromScansComponents);
    END

    DROP TABLE #DanglingComponents;
END
GO

EXEC cleanup_components;
GO

DROP PROCEDURE cleanup_components;
GO

-- Updating the COMPONENT table's rows to match the new structure.
-- This includes the multiplication of the components for each applicable project.

ALTER TABLE [ANALYSIS] DROP CONSTRAINT [ANALYSIS_FK1];
ALTER TABLE [COMPONENTS_VULNERABILITIES] DROP CONSTRAINT [COMPONENTS_VULNERABILITIES_FK1];
ALTER TABLE [DEPENDENCY] DROP CONSTRAINT [DEPENDENCY_FK1];
ALTER TABLE [DEPENDENCYMETRICS] DROP CONSTRAINT [DEPENDENCYMETRICS_FK1];
GO

CREATE TABLE [TMP_COMPONENT_MAPPING] (
    [ORIGINAL_COMPONENT_ID] BIGINT,
    [NEW_COMPONENT_ID] BIGINT,
    [PROJECT_ID] BIGINT
);
GO

CREATE TABLE [COMPONENT_40] (
    [ID] BIGINT IDENTITY NOT NULL PRIMARY KEY,
    [CLASSIFIER] VARCHAR(255),
    [COPYRIGHT] VARCHAR(1024),
    [CPE] VARCHAR(255),
    [DESCRIPTION] VARCHAR(1024),
    [EXTENSION] VARCHAR(255),
    [FILENAME] VARCHAR(255),
    [GROUP] VARCHAR(255),
    [INTERNAL] BIT,
    [LAST_RISKSCORE] DECIMAL(19, 0),
    [LICENSE] VARCHAR(255),
    [MD5] VARCHAR(32),
    [NAME] VARCHAR(255) NOT NULL,
    [PARENT_COMPONENT_ID] BIGINT,
    [PURL] VARCHAR(255),
    [LICENSE_ID] BIGINT,
    [SHA1] VARCHAR(40),
    [SHA_256] VARCHAR(64),
    [SHA3_256] VARCHAR(64),
    [SHA3_512] VARCHAR(128),
    [SHA_512] VARCHAR(128),
    [UUID] VARCHAR(36) NOT NULL,
    [VERSION] VARCHAR(255),
    [AUTHOR] VARCHAR(255),
    [BLAKE2B_256] VARCHAR(64),
    [BLAKE2B_384] VARCHAR(96),
    [BLAKE2B_512] VARCHAR(128),
    [BLAKE3] VARCHAR(255),
    [PROJECT_ID] BIGINT NOT NULL DEFAULT(0),
    [PUBLISHER] VARCHAR(255),
    [PURLCOORDINATES] VARCHAR(255),
    [SHA_384] VARCHAR(96),
    [SHA3_384] VARCHAR(96),
    [SWIDTAGID] VARCHAR(255)
);
GO

ALTER TABLE [COMPONENT_40] WITH CHECK ADD CHECK (([CLASSIFIER]='FILE' OR [CLASSIFIER]='DEVICE' OR [CLASSIFIER]='OPERATING_SYSTEM' OR [CLASSIFIER]='LIBRARY' OR [CLASSIFIER]='FRAMEWORK' OR [CLASSIFIER]='APPLICATION' OR [CLASSIFIER] IS NULL))
GO

CREATE TABLE [COMPONENTS_VULNERABILITIES_40] (
    [COMPONENT_ID] BIGINT NOT NULL,
    [VULNERABILITY_ID] BIGINT NOT NULL
);
GO

CREATE INDEX [COMPONENTS_VULNERABILITIES_N49] ON [COMPONENTS_VULNERABILITIES_40] ([COMPONENT_ID]);
CREATE INDEX [COMPONENTS_VULNERABILITIES_N50] ON [COMPONENTS_VULNERABILITIES_40] ([VULNERABILITY_ID]);
GO

CREATE PROCEDURE convert_components
AS
BEGIN
    DECLARE @v_original_id BIGINT;
    DECLARE @v_classifier VARCHAR(255);
    DECLARE @v_copyright VARCHAR(1024);
    DECLARE @v_cpe VARCHAR(255);
    DECLARE @v_description VARCHAR(1024);
    DECLARE @v_extension VARCHAR(255);
    DECLARE @v_filename VARCHAR(255);
    DECLARE @v_group VARCHAR(255);
    DECLARE @v_internal BIT;
    DECLARE @v_last_riskscore FLOAT(53);
    DECLARE @v_license VARCHAR(255);
    DECLARE @v_md5 VARCHAR(32);
    DECLARE @v_name VARCHAR(255);
    DECLARE @v_parent_component_id BIGINT;
    DECLARE @v_purl VARCHAR(255);
    DECLARE @v_license_id BIGINT;
    DECLARE @v_sha1 VARCHAR(40);
    DECLARE @v_sha_256 VARCHAR(64);
    DECLARE @v_sha3_256 VARCHAR(64);
    DECLARE @v_sha3_512 VARCHAR(128);
    DECLARE @v_sha_512 VARCHAR(128);
    DECLARE @v_uuid VARCHAR(36);
    DECLARE @v_version VARCHAR(255);
    DECLARE @v_project_id BIGINT;

    DECLARE @v_query_count BIGINT;
    DECLARE @v_new_id BIGINT;

    DECLARE component_cursor CURSOR FOR
        SELECT c.[ID], c.[CLASSIFIER], c.[COPYRIGHT], c.[CPE], c.[DESCRIPTION], c.[EXTENSION], c.[FILENAME],
               c.[GROUP], c.[INTERNAL], c.[LAST_RISKSCORE], c.[LICENSE], c.[MD5], c.[NAME],
               c.[PARENT_COMPONENT_ID], c.[PURL], c.[LICENSE_ID], c.[SHA1], c.[SHA_256],c.[SHA3_256],
               c.[SHA3_512], c.[SHA_512], c.[UUID], c.[VERSION], g.[PROJECT_ID]
        FROM (
            SELECT c.[ID], d.[PROJECT_ID]
            FROM [COMPONENT] c
                JOIN [DEPENDENCY] d ON c.[ID] = d.[COMPONENT_ID]
            GROUP BY c.[ID], d.[PROJECT_ID]
        ) g
        INNER JOIN [COMPONENT] c ON c.[ID] = g.[ID];

    DECLARE mapping_cursor CURSOR FOR
        SELECT [ORIGINAL_COMPONENT_ID], [NEW_COMPONENT_ID], [PROJECT_ID]
        FROM [TMP_COMPONENT_MAPPING]
        ORDER BY [ORIGINAL_COMPONENT_ID] DESC;

    OPEN component_cursor;

    FETCH NEXT FROM component_cursor INTO @v_original_id, @v_classifier, @v_copyright, @v_cpe, @v_description,
        @v_extension, @v_filename, @v_group, @v_internal, @v_last_riskscore, @v_license,
        @v_md5, @v_name, @v_parent_component_id, @v_purl, @v_license_id, @v_sha1, @v_sha_256,
        @v_sha3_256, @v_sha3_512, @v_sha_512, @v_uuid, @v_version, @v_project_id;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF @v_parent_component_id IS NOT NULL
        BEGIN
            SET @v_parent_component_id = (SELECT [NEW_COMPONENT_ID]
                FROM [TMP_COMPONENT_MAPPING]
                WHERE [ORIGINAL_COMPONENT_ID] = @v_parent_component_id
                    AND [PROJECT_ID] = @v_project_id);
        END;

        SELECT @v_query_count = COUNT(*) FROM [COMPONENT_40] WHERE [ID] = @v_original_id;

        IF @v_query_count = 0
        BEGIN
            SET IDENTITY_INSERT [COMPONENT_40] ON;

            INSERT INTO [COMPONENT_40] ([ID], [CLASSIFIER], [COPYRIGHT], [CPE], [DESCRIPTION], [EXTENSION], [FILENAME],
                                        [GROUP], [INTERNAL], [LAST_RISKSCORE], [LICENSE], [MD5], [NAME],
                                        [PARENT_COMPONENT_ID], [PURL], [LICENSE_ID], [SHA1], [SHA_256], [SHA3_256],
                                        [SHA3_512], [SHA_512], [UUID], [VERSION], [PROJECT_ID])
            VALUES (@v_original_id, @v_classifier, @v_copyright, @v_cpe, @v_description,
                    @v_extension, @v_filename, @v_group, @v_internal, @v_last_riskscore, @v_license,
                    @v_md5, @v_name, @v_parent_component_id, @v_purl, @v_license_id, @v_sha1, @v_sha_256,
                    @v_sha3_256, @v_sha3_512, @v_sha_512, @v_uuid, @v_version, @v_project_id);

            SET IDENTITY_INSERT [COMPONENT_40] OFF;

            SET @v_new_id = @v_original_id;
        END
        ELSE
        BEGIN
            INSERT INTO [COMPONENT_40] ([CLASSIFIER], [COPYRIGHT], [CPE], [DESCRIPTION], [EXTENSION], [FILENAME],
                                        [GROUP], [INTERNAL], [LAST_RISKSCORE], [LICENSE], [MD5], [NAME],
                                        [PARENT_COMPONENT_ID], [PURL], [LICENSE_ID], [SHA1], [SHA_256], [SHA3_256],
                                        [SHA3_512], [SHA_512], [UUID], [VERSION], [PROJECT_ID])
            VALUES (@v_classifier, @v_copyright, @v_cpe, @v_description,
                    @v_extension, @v_filename, @v_group, @v_internal, @v_last_riskscore, @v_license,
                    @v_md5, @v_name, @v_parent_component_id, @v_purl, @v_license_id, @v_sha1, @v_sha_256,
                    @v_sha3_256, @v_sha3_512, @v_sha_512, NEWID(), @v_version, @v_project_id);

            SELECT @v_new_id = SCOPE_IDENTITY();
        END;

        INSERT INTO [TMP_COMPONENT_MAPPING] VALUES (@v_original_id, @v_new_id, @v_project_id);

        FETCH NEXT FROM component_cursor INTO @v_original_id, @v_classifier, @v_copyright, @v_cpe, @v_description,
            @v_extension, @v_filename, @v_group, @v_internal, @v_last_riskscore, @v_license,
            @v_md5, @v_name, @v_parent_component_id, @v_purl, @v_license_id, @v_sha1, @v_sha_256,
            @v_sha3_256, @v_sha3_512, @v_sha_512, @v_uuid, @v_version, @v_project_id;

    END;

    CLOSE component_cursor;
    DEALLOCATE component_cursor;

    OPEN mapping_cursor;

    FETCH NEXT FROM mapping_cursor INTO @v_original_id, @v_new_id, @v_project_id;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        UPDATE [ANALYSIS]
        SET [COMPONENT_ID] = @v_new_id
        WHERE [COMPONENT_ID] = @v_original_id
            AND [PROJECT_ID] = @v_project_id;

        UPDATE [DEPENDENCYMETRICS]
        SET [COMPONENT_ID] = @v_new_id
        WHERE [COMPONENT_ID] = @v_original_id
          AND [PROJECT_ID] = @v_project_id;

        FETCH NEXT FROM mapping_cursor INTO @v_original_id, @v_new_id, @v_project_id;
    END;

    CLOSE mapping_cursor;
    DEALLOCATE mapping_cursor;

    INSERT INTO [COMPONENTS_VULNERABILITIES_40]
    SELECT tcm.[NEW_COMPONENT_ID], cv.[VULNERABILITY_ID]
    FROM [TMP_COMPONENT_MAPPING] tcm
    JOIN [COMPONENTS_VULNERABILITIES] cv ON tcm.[ORIGINAL_COMPONENT_ID] = cv.[COMPONENT_ID];
END;
GO

EXEC convert_components;
GO

DROP PROCEDURE convert_components;
GO

DROP TABLE [TMP_COMPONENT_MAPPING];
GO

DROP TABLE [COMPONENT];
GO

EXEC sp_rename N'COMPONENT_40', N'COMPONENT';
GO

ALTER TABLE [COMPONENT] ADD
    CONSTRAINT [COMPONENT_FK1] FOREIGN KEY ([PARENT_COMPONENT_ID]) REFERENCES [COMPONENT] ([ID]),
    CONSTRAINT [COMPONENT_FK2] FOREIGN KEY ([PROJECT_ID]) REFERENCES [PROJECT] ([ID]),
    CONSTRAINT [COMPONENT_FK3] FOREIGN KEY ([LICENSE_ID]) REFERENCES [LICENSE] ([ID]),
    CONSTRAINT [COMPONENT_UUID_IDX] UNIQUE ([UUID]);
GO

ALTER TABLE [ANALYSIS] ADD CONSTRAINT [ANALYSIS_FK1] FOREIGN KEY ([COMPONENT_ID]) REFERENCES [COMPONENT] ([ID]);
GO

DROP TABLE [COMPONENTS_VULNERABILITIES];
GO

EXEC sp_rename N'COMPONENTS_VULNERABILITIES_40', N'COMPONENTS_VULNERABILITIES';
GO

ALTER TABLE [COMPONENTS_VULNERABILITIES] ADD
    CONSTRAINT [COMPONENTS_VULNERABILITIES_FK1] FOREIGN KEY ([COMPONENT_ID]) REFERENCES [COMPONENT] ([ID]),
    CONSTRAINT [COMPONENTS_VULNERABILITIES_FK2] FOREIGN KEY ([VULNERABILITY_ID]) REFERENCES [VULNERABILITY] ([ID]);
GO

ALTER TABLE [DEPENDENCYMETRICS] ADD CONSTRAINT [DEPENDENCYMETRICS_FK1] FOREIGN KEY ([COMPONENT_ID]) REFERENCES [COMPONENT] ([ID]);
GO

ALTER TABLE [FINDINGATTRIBUTION] ADD CONSTRAINT [FINDINGATTRIBUTION_FK1] FOREIGN KEY ([COMPONENT_ID]) REFERENCES [COMPONENT] ([ID]);
GO

ALTER TABLE [POLICYVIOLATION] ADD CONSTRAINT [POLICYVIOLATION_FK1] FOREIGN KEY ([COMPONENT_ID]) REFERENCES [COMPONENT] ([ID]);
GO

-- SCANS_COMPONENTS may not exist (https://github.com/DependencyTrack/dependency-track/blob/master/docs/_posts/2019-12-16-v3.7.0.md)
DECLARE @AddScansComponentsForeighKey NVARCHAR(200) = N'
ALTER TABLE [SCANS_COMPONENTS] ADD CONSTRAINT [SCANS_COMPONENTS_FK2] FOREIGN KEY ([COMPONENT_ID]) REFERENCES [COMPONENT] ([ID]);
';
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SCANS_COMPONENTS')
BEGIN
    EXEC(@AddScansComponentsForeighKey);
END

GO

ALTER TABLE [VIOLATIONANALYSIS] ADD CONSTRAINT [VIOLATIONANALYSIS_FK1] FOREIGN KEY ([COMPONENT_ID]) REFERENCES [COMPONENT] ([ID]);
GO

DROP TABLE [DEPENDENCY];
GO

UPDATE [SCHEMAVERSION] SET [VERSION] = '4.0.0' WHERE [ID] = 1;
GO

-- Fill FINDINGATTRIBUTION table

INSERT INTO [FINDINGATTRIBUTION] ([ANALYZERIDENTITY], [COMPONENT_ID], [PROJECT_ID], [UUID], [VULNERABILITY_ID])
SELECT
    CASE
        WHEN v.[SOURCE] = 'INTERNAL' THEN 'INTERNAL_ANALYZER'
        WHEN v.[SOURCE] = 'NPM'      THEN 'NPM_AUDIT_ANALYZER'
        WHEN v.[SOURCE] = 'OSSINDEX' THEN 'OSSINDEX_ANALYZER'
        WHEN v.[SOURCE] = 'VULNDB'   THEN 'VULNDB_ANALYZER'
        ELSE 'NONE'
    END AS [ANALYZERIDENTITY],
    c.[ID] AS [COMPONENT_ID],
    c.[PROJECT_ID] AS [PROJECT_ID],
    NEWID() AS [UUID],
    v.[ID] AS [VULNERABILITY_ID]
FROM [COMPONENT] c
    INNER JOIN [COMPONENTS_VULNERABILITIES] cv ON c.[ID] = cv.[COMPONENT_ID]
    INNER JOIN [VULNERABILITY] v ON v.[ID] = cv.[VULNERABILITY_ID];