mysqldump --single-transaction --routines --triggers --max_allowed_packet=2048M -u root -p<Password> db_ssc_2222 > 20240801_ssc_backup.sql
2. SSC 설치 진행
Database 항목에서 DB Connection 테스트 후 우측하단 스크립트 다운로드에서 ssc-migration.sql 다운로드
(하단 그림 참고)
3. ssc-migration.sql 스크립트로 DB 마이그레이션 진행
mysql -u root -p<Password> db_ssc_2222 < ssc-migration.sql
만일 오류 발생 시 아래의 스크립트를 실행하여 원상 복구
DROP DATABASE IF EXISTS db_ssc_2222;
CREATE DATABASE db_ssc_2222 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;
mysql -u root -p<Password> db_ssc_2222 < 20240705_103707_ssc_db.sql
4. SSC 설치 진행
SEED도 정상적으로 업로드 한다.
(하단 그림 참고)
[샘플] ssc-migration.sql
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: dbMaster.xml
-- Ran at: 24. 7. 5. 오후 12:09
-- Against: root@jdbc:mariadb://10.10.10.149/db_ssc_2222
-- Liquibase version: 4.10.0
-- *********************************************************************
-- Create Database Lock Table
CREATE TABLE db_ssc_2222.DATABASECHANGELOGLOCK (ID INT NOT NULL, `LOCKED` TINYINT(1) NOT NULL, LOCKGRANTED datetime NULL, LOCKEDBY VARCHAR(255) NULL, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));
-- Initialize Database Lock Table
DELETE FROM db_ssc_2222.DATABASECHANGELOGLOCK;
INSERT INTO db_ssc_2222.DATABASECHANGELOGLOCK (ID, `LOCKED`) VALUES (1, 0);
-- Lock Database
UPDATE db_ssc_2222.DATABASECHANGELOGLOCK SET `LOCKED` = 1, LOCKEDBY = 'LENOVO-E430 (10.10.10.127)', LOCKGRANTED = NOW() WHERE ID = 1 AND `LOCKED` = 0;
-- Changeset dbF360_Init.xml::f360_init_mysql_1::hp
SET collation_connection = @@collation_database;
UPDATE db_ssc_2222.DATABASECHANGELOG SET COMMENTS = '', CONTEXTS = NULL, DATEEXECUTED = NOW(), DEPLOYMENT_ID = '0148965608', EXECTYPE = 'RERAN', LABELS = NULL, MD5SUM = '8:c0200fd5942e9c97af8ac5709e56d3cb', ORDEREXECUTED = 808 WHERE ID = 'f360_init_mysql_1' AND AUTHOR = 'hp' AND FILENAME = 'dbF360_Init.xml';
-- Changeset dbF360_23.1.xml::f360_23.1_1::fortify
-- Add indices to support eventType and userName filtering
CREATE INDEX EVENTLOGENTRY_TYPE_IND ON db_ssc_2222.eventlogentry(eventType);
CREATE INDEX EVENTLOGENTRY_NAME_TYPE_IND ON db_ssc_2222.eventlogentry(userName, eventType);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_1', 'fortify', 'dbF360_23.1.xml', NOW(), 809, '8:f0cd5b243e24b12b34472ea39b4fdc65', 'createIndex indexName=EVENTLOGENTRY_TYPE_IND, tableName=eventlogentry; createIndex indexName=EVENTLOGENTRY_NAME_TYPE_IND, tableName=eventlogentry', 'Add indices to support eventType and userName filtering', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.1.xml::f360_23.1_2::fortify
-- Adding startDate and finishDate columns to savedreport table
ALTER TABLE db_ssc_2222.savedreport ADD startDate datetime(3) NULL, ADD finishDate datetime(3) NULL;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_2', 'fortify', 'dbF360_23.1.xml', NOW(), 810, '8:4774f0387df72c051dbcfdf79c97525d', 'addColumn tableName=savedreport', 'Adding startDate and finishDate columns to savedreport table', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.1.xml::f360_23.1_3::fortify
-- Adding table as an extension to the issue table because the issue table is too large.
-- We can use this table to append any columns we would like to attach to issues.
-- issue_id maps to issue.id. projectVersion_id, issueInstanceId and engineType also map to corresponding columns in the issue table.
-- reintroducedDate is the date an issue is reintroduced in an app version, similar to foundDate and removedDate.
CREATE TABLE db_ssc_2222.issue_extension (issue_id INT NOT NULL, projectVersion_id BIGINT NOT NULL, issueInstanceId VARCHAR(80) NOT NULL, engineType VARCHAR(20) NOT NULL, reintroducedDate BIGINT NULL, CONSTRAINT PK_ISSUE_EXTENSION PRIMARY KEY (issue_id));
CREATE INDEX ISSUE_EXTENSION_PV_IID_ENGINE_IDX ON db_ssc_2222.issue_extension(projectVersion_id, issueInstanceId, engineType);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_3', 'fortify', 'dbF360_23.1.xml', NOW(), 811, '8:760b177832a55b791e0796352399cebe', 'createTable tableName=issue_extension; createIndex indexName=ISSUE_EXTENSION_PV_IID_ENGINE_IDX, tableName=issue_extension', 'Adding table as an extension to the issue table because the issue table is too large.
We can use this table to append any columns we would like to attach to issues.
issue_id maps to issue.id. projectVersion_id, issueInstan...', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.1.xml::f360_23.1_4::fortify
-- add start and finish dates to data exports
ALTER TABLE db_ssc_2222.dataexport ADD startDate datetime(3) NULL;
ALTER TABLE db_ssc_2222.dataexport ADD finishDate datetime(3) NULL;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_4', 'fortify', 'dbF360_23.1.xml', NOW(), 812, '8:b707b1e845027df177bf9cb3cee8a7eb', 'addColumn tableName=dataexport; addColumn tableName=dataexport', 'add start and finish dates to data exports', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.1.xml::f360_23.1_5::fortify
-- add new table for issues delta exports
CREATE TABLE db_ssc_2222.issuesdeltaexport (id BIGINT AUTO_INCREMENT NOT NULL, fileName VARCHAR(255) NOT NULL, note VARCHAR(255) NULL, requestDate datetime(3) NOT NULL, startDate datetime(3) NULL, finishDate datetime(3) NULL, userName VARCHAR(255) NOT NULL, status VARCHAR(30) NOT NULL, messages MEDIUMTEXT NULL, documentInfo_id INT NULL, projectVersionIds MEDIUMTEXT NULL, deltaSinceDate datetime(3) NULL, CONSTRAINT PK_ISSUESDELTAEXPORT PRIMARY KEY (id));
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_5', 'fortify', 'dbF360_23.1.xml', NOW(), 813, '8:f532a7c3961d83d175526aa421a308c3', 'createTable tableName=issuesdeltaexport', 'add new table for issues delta exports', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360Mysql_23.1_datetime.xml::f360Mysql_23.1_datetime::fortify
-- Enhance to millisecond precision for datetime columns added between 20.2 and 22.2 which were incorrectly created as datetime instead of datetime(3) in scripts generated prior to ssc v23.1
ALTER TABLE fortifygroup MODIFY created datetime(3) NOT NULL, MODIFY lastModified datetime(3) NOT NULL;
ALTER TABLE fortifyuser MODIFY created datetime(3) NOT NULL, MODIFY lastModified datetime(3) NOT NULL;
ALTER TABLE jobqueue MODIFY createTime datetime(3) NOT NULL;
ALTER TABLE ldapcache MODIFY uploadDate datetime(3) NOT NULL;
ALTER TABLE scan_issue_correlation MODIFY target_scan_time datetime(3);
ALTER TABLE webhook MODIFY creationDate datetime(3) NOT NULL;
ALTER TABLE webhookhistory MODIFY createdAt datetime(3) NOT NULL;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360Mysql_23.1_datetime', 'fortify', 'dbF360Mysql_23.1_datetime.xml', NOW(), 814, '8:efc7aa749c470eae51e25472fca50bec', 'sql', 'Enhance to millisecond precision for datetime columns added between 20.2 and 22.2 which were incorrectly created as datetime instead of datetime(3) in scripts generated prior to ssc v23.1', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_1::fortify
-- Remove orphan records in documentinfo and datablob
DELETE FROM db_ssc_2222.documentinfo WHERE NOT EXISTS( SELECT 1 FROM
(SELECT artifact.documentInfo_id
FROM artifact
UNION
SELECT au.documentInfo_id
FROM auditattachment au
UNION
SELECT de.documentInfo_id
FROM dataexport de
UNION
SELECT df.templateInfo_id
FROM documentdef df
UNION
SELECT dsp.fileValueDocumentInfo_id
FROM dynamicscanparameter dsp
UNION
SELECT ide.documentInfo_id
FROM issuesdeltaexport ide
UNION
SELECT pm.documentInfo_id
FROM pluginmetadata pm
UNION
SELECT pt.documentInfo_id
FROM projecttemplate pt
UNION
SELECT rd.templateDoc_id
FROM reportdefinition rd
UNION
SELECT rl.fileDoc_id FROM
reportlibrary rl
UNION
SELECT rp.documentInfo_id
FROM rulepack rp UNION
SELECT sr.reportOutputDoc_id
FROM savedreport sr) dids
WHERE dids.documentInfo_id = documentinfo.id);
DELETE FROM db_ssc_2222.datablob WHERE NOT EXISTS( SELECT 1 FROM
(SELECT documentinfo.fileBlob_id
FROM documentinfo
UNION
SELECT snapshot.auditBlob_id
FROM snapshot
UNION
SELECT ldapcache.blobId
FROM ldapcache
UNION
SELECT projectversion.currentFprBlob_id
FROM projectversion) blobs
WHERE datablob.id = blobs.fileBlob_id);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_1', 'fortify', 'dbF360_23.2.xml', NOW(), 815, '8:906f6f63924a29f20ac33d86e038d409', 'delete tableName=documentinfo; delete tableName=datablob', 'Remove orphan records in documentinfo and datablob', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_2::fortify
-- Drop index and re-create it as non-unique. It may have previously been created as unique.
DROP INDEX ISSUE_EXTENSION_PV_IID_ENGINE_IDX ON db_ssc_2222.issue_extension;
CREATE INDEX ISSUE_EXTENSION_PV_IID_ENGINE_IDX ON db_ssc_2222.issue_extension(projectVersion_id, issueInstanceId, engineType);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_2', 'fortify', 'dbF360_23.2.xml', NOW(), 816, '8:26fa343f49f07cdcf5f02b38f2735cae', 'dropIndex indexName=ISSUE_EXTENSION_PV_IID_ENGINE_IDX, tableName=issue_extension; createIndex indexName=ISSUE_EXTENSION_PV_IID_ENGINE_IDX, tableName=issue_extension', 'Drop index and re-create it as non-unique. It may have previously been created as unique.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_4::fortify
-- Update documentType for project template documents
UPDATE documentinfo
SET documentType = 24
WHERE EXISTS(SELECT 1 from projecttemplate
WHERE projecttemplate.documentInfo_id = documentinfo.id);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_4', 'fortify', 'dbF360_23.2.xml', NOW(), 817, '8:e4a40d317e31c02783c287f758348e01', 'sql', 'Update documentType for project template documents', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_5::fortify
-- Update documentType for report documents
UPDATE documentinfo
SET documentType = 4
WHERE EXISTS(SELECT 1
FROM savedreport
WHERE savedreport.reportOutputDoc_id = documentinfo.id
AND savedreport.format = 'PDF');
UPDATE documentinfo
SET documentType = 5
WHERE EXISTS(SELECT 1
FROM savedreport
WHERE savedreport.reportOutputDoc_id = documentinfo.id
AND savedreport.format = 'DOC');
UPDATE documentinfo
SET documentType = 23
WHERE EXISTS(SELECT 1
FROM savedreport
WHERE savedreport.reportOutputDoc_id = documentinfo.id
AND savedreport.format = 'XLS');
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_5', 'fortify', 'dbF360_23.2.xml', NOW(), 818, '8:48e01f5bdece489d3a2f0a47434de339', 'sql; sql; sql', 'Update documentType for report documents', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_6::fortify
-- Add auditAssistantTrainingCustomTagGuid to ProjectVersion to identify which custom tag to use for Audit Assistant training.
ALTER TABLE db_ssc_2222.projectversion ADD auditAssistantTrainingCustomTagGuid VARCHAR(255) NULL;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_6', 'fortify', 'dbF360_23.2.xml', NOW(), 819, '8:b33361c7395796eed2ed0119eb7ee678', 'addColumn tableName=projectversion', 'Add auditAssistantTrainingCustomTagGuid to ProjectVersion to identify which custom tag to use for Audit Assistant training.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_7::fortify
-- Add column to attrlookup for defining Audit Assistant Training Label
ALTER TABLE db_ssc_2222.attrlookup ADD auditAssistantTrainingLabel VARCHAR(255) DEFAULT 'SKIP_FOR_TRAINING' NOT NULL;
ALTER TABLE db_ssc_2222.attrlookup ALTER auditAssistantTrainingLabel DROP DEFAULT;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_7', 'fortify', 'dbF360_23.2.xml', NOW(), 820, '8:3ffe7546ef51773632dcc9bc884d738f', 'addColumn tableName=attrlookup; dropDefaultValue columnName=auditAssistantTrainingLabel, tableName=attrlookup', 'Add column to attrlookup for defining Audit Assistant Training Label', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_8::fortify
-- Add table for SBOM storage. Links SBOM file to project version by engineType.
-- Link SBOM Document to a scan and some SBOM info
CREATE TABLE db_ssc_2222.sbom (id BIGINT AUTO_INCREMENT NOT NULL, projectVersion_id BIGINT NOT NULL, engineType VARCHAR(20) NOT NULL, documentInfo_id BIGINT NOT NULL, format VARCHAR(255) NOT NULL, serialization VARCHAR(255) NOT NULL, CONSTRAINT PK_SBOM PRIMARY KEY (id));
ALTER TABLE db_ssc_2222.scan ADD sbomDocumentInfo_id BIGINT NULL, ADD sbomFormat VARCHAR(255) NULL, ADD sbomSerialization VARCHAR(255) NULL;
ALTER TABLE db_ssc_2222.attrlookup ALTER auditAssistantTrainingLabel DROP DEFAULT;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_8', 'fortify', 'dbF360_23.2.xml', NOW(), 821, '8:5c82436f8b1aeb884e671de1b3a919ff', 'createTable tableName=sbom; addColumn tableName=scan; dropDefaultValue columnName=auditAssistantTrainingLabel, tableName=attrlookup', 'Add table for SBOM storage. Links SBOM file to project version by engineType.
Link SBOM Document to a scan and some SBOM info', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_9::fortify
-- Add index for sbom table
CREATE UNIQUE INDEX SBOM_PV_ENGINE_IDX ON db_ssc_2222.sbom(projectVersion_id, engineType);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_9', 'fortify', 'dbF360_23.2.xml', NOW(), 822, '8:9eda2793fe19d138ca6f78ec941b7ec4', 'createIndex indexName=SBOM_PV_ENGINE_IDX, tableName=sbom', 'Add index for sbom table', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_11::fortify
-- Disable Audit Assistant in order to allow users to gracefully transition to Audit Assistant G2
UPDATE db_ssc_2222.configproperty SET propertyValue = 'false' WHERE propertyName='auditassistant.enabled';
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_11', 'fortify', 'dbF360_23.2.xml', NOW(), 823, '8:4aab4e7b2f0ff0c23d4dde095fc44d78', 'update tableName=configproperty', 'Disable Audit Assistant in order to allow users to gracefully transition to Audit Assistant G2', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_12_mysql_oracle::fortify
-- Add indexes on variablehistory and measurementhistory to make project version state queries more efficient
CREATE INDEX VARIABLEHISTORY_SNAPID_VARID_VARVALUE_IDX ON db_ssc_2222.variablehistory(snapshot_id, variable_id, variableValue);
CREATE INDEX MEASUREMENTHISTORY_SNAPID_MSRID_MSRVALUE_IDX ON db_ssc_2222.measurementhistory(snapshot_id, measurement_id, measurementValue);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_12_mysql_oracle', 'fortify', 'dbF360_23.2.xml', NOW(), 824, '8:3d23939b9a9500d05ed0649d42d3be48', 'createIndex indexName=VARIABLEHISTORY_SNAPID_VARID_VARVALUE_IDX, tableName=variablehistory; createIndex indexName=MEASUREMENTHISTORY_SNAPID_MSRID_MSRVALUE_IDX, tableName=measurementhistory', 'Add indexes on variablehistory and measurementhistory to make project version state queries more efficient', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_13::fortify
-- Add index for jobqueue table
CREATE INDEX JOBQUEUE_PVID_JOBCLASS_STATE_IDX ON db_ssc_2222.jobqueue(projectVersion_id, jobClassName, state);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_13', 'fortify', 'dbF360_23.2.xml', NOW(), 825, '8:3e9fb1c313ab1088bb75d3827a95a565', 'createIndex indexName=JOBQUEUE_PVID_JOBCLASS_STATE_IDX, tableName=jobqueue', 'Add index for jobqueue table', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_14_mysql::fortify
-- Add an index on a scan_issue table to improve performance during FPRs deletions.
DROP INDEX SCAN_PV_IDX ON db_ssc_2222.scan_issue;
CREATE INDEX SCANISSUE_PVID_SCANID_ISSUEID_IDX ON db_ssc_2222.scan_issue(projectVersion_id, scan_id, issue_id);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_14_mysql', 'fortify', 'dbF360_23.2.xml', NOW(), 826, '8:8c1fda40198f2224d866c90f5174b1cc', 'dropIndex indexName=SCAN_PV_IDX, tableName=scan_issue; createIndex indexName=SCANISSUE_PVID_SCANID_ISSUEID_IDX, tableName=scan_issue', 'Add an index on a scan_issue table to improve performance during FPRs deletions.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_23.2.xml::f360_23.2_15_mssql_mysql::fortify
-- Allow storing longer tokens for bugtracker state management.
ALTER TABLE db_ssc_2222.bugstatemgmtconfig MODIFY password VARCHAR(4000);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_15_mssql_mysql', 'fortify', 'dbF360_23.2.xml', NOW(), 827, '8:5f12a2a3156ccdbeaf832b4c5608549f', 'modifyDataType columnName=password, tableName=bugstatemgmtconfig', 'Allow storing longer tokens for bugtracker state management.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360Mysql_23.2_scan_issue_id.xml::f360Mysql_23.2_scan_issue_id::fortify
-- Update scan_issue ID from INT to BIGINT
ALTER TABLE scan_issue
MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360Mysql_23.2_scan_issue_id', 'fortify', 'dbF360Mysql_23.2_scan_issue_id.xml', NOW(), 828, '8:618a78a0fa7dd54837d58336474506b8', 'sql', 'Update scan_issue ID from INT to BIGINT', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_1::fortify
-- Adding new column uiTheme to persist user preferred UI theme.
ALTER TABLE db_ssc_2222.userpreference ADD uiTheme VARCHAR(100) NULL;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_1', 'fortify', 'dbF360_24.2.xml', NOW(), 829, '8:e54ec3f9f40e1ff0880ab4e79ad9630d', 'addColumn tableName=userpreference', 'Adding new column uiTheme to persist user preferred UI theme.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_2::fortify
-- Remove invalid tokens referencing removed token definitions.
DELETE FROM db_ssc_2222.agentcredential WHERE type IN ('AuditToken', 'ScanCentralOneTimeJobToken', 'WIESystemToken', 'WIEUserToken');
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_2', 'fortify', 'dbF360_24.2.xml', NOW(), 830, '8:426c762d31dcce0cf63e1e050db6dbd2', 'delete tableName=agentcredential', 'Remove invalid tokens referencing removed token definitions.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_3::fortify
-- Remove unused requirement documents and associated file blobs
DELETE FROM db_ssc_2222.datablob WHERE EXISTS (SELECT 1 FROM documentdef dd
INNER JOIN documentinfo di ON dd.templateInfo_id = di.id
WHERE di.fileBlob_id = datablob.id);
DELETE FROM db_ssc_2222.documentinfo WHERE EXISTS (SELECT 1 FROM documentdef dd WHERE documentinfo.id = dd.templateInfo_id);
DROP TABLE db_ssc_2222.documentdef;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_3', 'fortify', 'dbF360_24.2.xml', NOW(), 831, '8:4e89b666da17655f266764d88a6fbfd6', 'delete tableName=datablob; delete tableName=documentinfo; dropTable tableName=documentdef', 'Remove unused requirement documents and associated file blobs', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_4::fortify
-- Remove unused governance tables
DROP TABLE db_ssc_2222.rtassignment;
DROP TABLE db_ssc_2222.activity_persona;
DROP TABLE db_ssc_2222.documentactivity;
DROP TABLE db_ssc_2222.requirement_activity;
DROP TABLE db_ssc_2222.timelapseactivity;
DROP TABLE db_ssc_2222.projectstateactivity;
DROP TABLE db_ssc_2222.activity;
DROP TABLE db_ssc_2222.requirement_persona;
DROP TABLE db_ssc_2222.requirement;
DROP TABLE db_ssc_2222.projectpersonaassignment;
DROP TABLE db_ssc_2222.requirementtemplate_persona;
DROP TABLE db_ssc_2222.metadatarule;
DROP TABLE db_ssc_2222.requirementtemplate;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_4', 'fortify', 'dbF360_24.2.xml', NOW(), 832, '8:4f6e381b235cd2ad790e5d14f2bd1fed', 'dropTable tableName=rtassignment; dropTable tableName=activity_persona; dropTable tableName=documentactivity; dropTable tableName=requirement_activity; dropTable tableName=timelapseactivity; dropTable tableName=projectstateactivity; dropTable tabl...', 'Remove unused governance tables', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_5::fortify
-- Remove unused governance alerts
DELETE FROM db_ssc_2222.alert WHERE monitoredEntityType IN ('RT_INSTANCE', 'ACTIVITY_INSTANCE', 'REQ_INSTANCE');
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_5', 'fortify', 'dbF360_24.2.xml', NOW(), 833, '8:b7b4363a94cb5f6bfe943796db53e733', 'delete tableName=alert', 'Remove unused governance alerts', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_6::fortify
-- Adding table storedobjectinfo to persist information relevant to object storage.
CREATE TABLE db_ssc_2222.storedobjectinfo (objectKey VARCHAR(255) NOT NULL, creationDate datetime(3) NOT NULL, checksum VARCHAR(255) NULL, deletionRequestDate datetime(3) NULL, CONSTRAINT PK_STOREDOBJECTINFO PRIMARY KEY (objectKey));
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_6', 'fortify', 'dbF360_24.2.xml', NOW(), 834, '8:516551ac7c3fc4c713d8187560def4db', 'createTable tableName=storedobjectinfo', 'Adding table storedobjectinfo to persist information relevant to object storage.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_6_part2::fortify
-- Add storedObjectKey column to documentInfo
ALTER TABLE db_ssc_2222.documentinfo ADD storedObjectKey VARCHAR(255) NULL;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_6_part2', 'fortify', 'dbF360_24.2.xml', NOW(), 835, '8:00404b2670ae61c9580e4ceb03ecf933', 'addColumn tableName=documentinfo', 'Add storedObjectKey column to documentInfo', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_7::fortify
-- Add a new table to track failed Kafka events by project version
CREATE TABLE db_ssc_2222.failedkafkaevent (projectVersion_id BIGINT NOT NULL, earliestAuditTime BIGINT NOT NULL, CONSTRAINT PK_FAILEDKAFKAEVENT PRIMARY KEY (projectVersion_id));
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_7', 'fortify', 'dbF360_24.2.xml', NOW(), 836, '8:3a1dd5ac8d8d7a06557f4806e94565a5', 'createTable tableName=failedkafkaevent', 'Add a new table to track failed Kafka events by project version', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_8::fortify
-- Set issue status as Under Review when an issue has an audit history but its status is still set as Unreviewed.
-- Ignore issues where primary tag has been set (audited) and audithistory changes where guid is user assignment and suppressed.
UPDATE issue i
JOIN audithistory ah ON i.id = ah.issue_id AND i.issueStatus = 'Unreviewed' AND i.audited = 'N'
AND ah.attrGuid NOT IN ('userAssignment', '22222222-2222-2222-2222-222222222222')
SET i.issueStatus = 'Under Review';
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_8', 'fortify', 'dbF360_24.2.xml', NOW(), 837, '8:a0484b467f565fa943899dad2587f99d', 'sql; sql; sql', 'Set issue status as Under Review when an issue has an audit history but its status is still set as Unreviewed.
Ignore issues where primary tag has been set (audited) and audithistory changes where guid is user assignment and suppressed.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_9::fortify
-- Add table for data retention (cleanup) policies and add policy-related columns for projectVersion table.
CREATE TABLE db_ssc_2222.dataretentionpolicy (id BIGINT AUTO_INCREMENT NOT NULL, guid VARCHAR(255) NOT NULL, objectVersion INT NOT NULL, name VARCHAR(255) NOT NULL, `description` VARCHAR(2000) NOT NULL, systemOwned VARCHAR(1) DEFAULT 'N' NOT NULL, minArtifacts INT NOT NULL, maxArtifacts INT NOT NULL, minArtifactAgeInDays INT NOT NULL, maxArtifactAgeInDays INT NOT NULL, CONSTRAINT PK_DR_POLICY PRIMARY KEY (id), CONSTRAINT UQ_DR_POLICY_GUID UNIQUE (guid), CONSTRAINT UQ_DR_POLICY_NAME UNIQUE (name));
ALTER TABLE db_ssc_2222.projectversion ADD dataRetentionPolicyOverride VARCHAR(1) DEFAULT 'N' NOT NULL, ADD dataRetentionPolicyGuid VARCHAR(255) NULL;
CREATE INDEX PV_DATA_RETENTION_IDX ON db_ssc_2222.projectversion(dataRetentionPolicyOverride, dataRetentionPolicyGuid);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_9', 'fortify', 'dbF360_24.2.xml', NOW(), 838, '8:1d101f59feb56abea7653f4de1a93ef6', 'createTable tableName=dataretentionpolicy; addColumn tableName=projectversion; createIndex indexName=PV_DATA_RETENTION_IDX, tableName=projectversion', 'Add table for data retention (cleanup) policies and add policy-related columns for projectVersion table.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_10::fortify
-- In 24.2, admins cannot create new policies but can edit and/or enable the system-owned default policy. Pre-create the system-owned default policy entity with benign properties.
INSERT INTO db_ssc_2222.dataretentionpolicy (guid, objectVersion, name, `description`, systemOwned, minArtifacts, maxArtifacts, minArtifactAgeInDays, maxArtifactAgeInDays) VALUES ('__DATA_RETENTION_SYSTEM_DEFAULT_POLICY__', 1, 'Default data retention policy', 'This system-owned policy is used for an application when the feature is enabled and the application has not opted-out of the default policy.', 'Y', 1000, 1000, 3650, 3650);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_10', 'fortify', 'dbF360_24.2.xml', NOW(), 839, '8:77cd5d81f011a8bb00198409b4426e49', 'insert tableName=dataretentionpolicy; insert tableName=dataretentionpolicy', 'In 24.2, admins cannot create new policies but can edit and/or enable the system-owned default policy. Pre-create the system-owned default policy entity with benign properties.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_11::fortify
-- Remove orphaned records in auditcomment
DELETE FROM db_ssc_2222.auditcomment WHERE NOT EXISTS (SELECT 1 FROM issue i WHERE i.id = auditcomment.issue_id);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_11', 'fortify', 'dbF360_24.2.xml', NOW(), 840, '8:b51b666d6d2750e155ed01e122ecc726', 'delete tableName=auditcomment', 'Remove orphaned records in auditcomment', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_12::fortify
-- Delete orphaned documentinfo and datablob rows that correspond to associatedDocIds of artifacts belonging to deleted PVs
DELETE di, db FROM documentinfo di
JOIN datablob db ON di.fileBlob_id = db.id
LEFT JOIN artifact a ON a.associatedDocInfo_id = di.id
WHERE a.id IS NULL AND di.documentType=16;
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_12', 'fortify', 'dbF360_24.2.xml', NOW(), 841, '8:2d4ee5abdfaed1ac0e860b301ca50472', 'sql; sql; sql', 'Delete orphaned documentinfo and datablob rows that correspond to associatedDocIds of artifacts belonging to deleted PVs', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset dbF360_24.2.xml::f360_24.2_13::fortify
-- Remove orphaned records in applicationentity and related tables
DELETE FROM db_ssc_2222.projectversion_alert WHERE projectVersion_id IN (SELECT id FROM applicationentity
WHERE NOT EXISTS (SELECT 1 FROM projectversion
WHERE applicationentity.id = projectversion.id));
DELETE FROM db_ssc_2222.alerthistory WHERE projectVersion_id IN (SELECT id FROM applicationentity
WHERE NOT EXISTS (SELECT 1 FROM projectversion
WHERE applicationentity.id = projectversion.id));
DELETE FROM db_ssc_2222.metavalue WHERE projectVersion_id IN (SELECT id FROM applicationentity
WHERE NOT EXISTS (SELECT 1 FROM projectversion
WHERE applicationentity.id = projectversion.id));
DELETE FROM db_ssc_2222.report_applicationentity WHERE applicationEntity_id IN (SELECT id FROM applicationentity
WHERE NOT EXISTS (SELECT 1 FROM projectversion
WHERE applicationentity.id = projectversion.id));
DELETE FROM db_ssc_2222.runtimeapplication;
DELETE FROM db_ssc_2222.applicationentity WHERE NOT EXISTS (SELECT 1 FROM projectversion
WHERE applicationentity.id = projectversion.id);
INSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_13', 'fortify', 'dbF360_24.2.xml', NOW(), 842, '8:98d04489fa98f402050b6c32439a1afb', 'delete tableName=projectversion_alert; delete tableName=alerthistory; delete tableName=metavalue; delete tableName=report_applicationentity; delete tableName=runtimeapplication; delete tableName=applicationentity', 'Remove orphaned records in applicationentity and related tables', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');
-- Changeset procs/dbF360_updateExistingWithLatest.xml::updateExistingWithLatest_mysql::hp_main
DROP PROCEDURE IF EXISTS updateExistingWithLatest;
DELIMITER //
CREATE PROCEDURE updateExistingWithLatest (p_scan_id INT,
p_projectVersion_id INT,
p_foundDate BIGINT,
p_folder_id INT,
p_customPriorityEnabled INT
)
BEGIN
INSERT INTO issue_extension(issue_id, projectVersion_id, issueInstanceId, engineType, reintroducedDate)
SELECT i.id, p_projectVersion_id, i.issueInstanceId, i.engineType, p_foundDate
FROM issue i INNER JOIN scan_issue si ON i.engineType = si.engineType AND i.issueInstanceId = si.issueInstanceId AND i.projectVersion_id = si.projectVersion_id
WHERE si.scan_id = p_scan_id AND si.projectVersion_id = p_projectVersion_id AND i.scanStatus = 'REMOVED'
ON DUPLICATE KEY UPDATE issue_extension.reintroducedDate = p_foundDate;
insert into issue (lastScan_Id, scanStatus, issueInstanceId, projectVersion_Id, engineType
, foundDate, shortFileName, fileName, severity, confidence, kingdom
, issueType, issueSubtype, analyzer, lineNumber, taintFlag, packageName
, functionName, className, issueAbstract, issueRecommendation, friority
, replaceStore, ruleGuid, findingGuid, snippetId, contextId, category
, url, source, sourceContext, sink, sinkContext, sourceFile
, audienceSet, remediationConstant, likelihood, probability, impact
, accuracy, rtaCovered, requestIdentifier, requestHeader, requestParameter
, requestBody, requestMethod, cookie, httpVersion, attackPayload, attackType
, attackTriggerDefinition , response, triggerDefinition, triggerString
, triggerDisplayText, secondaryRequest, sourceLine, mappedCategory
, vulnerableParameter, reproStepDefinition, stackTrace, stackTraceTriggerDisplayText
, manual, minVirtualCallConfidence, hidden, folder_Id, objectVersion)
select scan_Id, 'NEW', issueInstanceId, projectVersion_Id, engineType, p_foundDate
, shortFileName, fileName, severity, confidence, kingdom, issueType, issueSubtype
, analyzer, lineNumber, taintFlag, packageName, functionName, className, issueAbstract
, issueRecommendation, friority, replaceStore, ruleGuid, findingGuid, snippetId, contextId
, category, url, source, sourceContext, sink, sinkContext, sourceFile, audienceSet
, remediationConstant, likelihood, probability, impact, accuracy, rtaCovered
, requestIdentifier, requestHeader, requestParameter, requestBody, requestMethod, cookie
, httpVersion, attackPayload, attackType, attackTriggerDefinition, response
, triggerDefinition, triggerString, triggerDisplayText, secondaryRequest, sourceLine
, mappedCategory, vulnerableParameter, reproStepDefinition, stackTrace
, stackTraceTriggerDisplayText, manual, minVirtualCallConfidence, 'N', p_folder_id, 0 from scan_issue si where si.projectVersion_id = p_projectVersion_id and si.scan_id = p_scan_id
on duplicate key update issue.lastScan_Id= si.scan_id
, issue.shortFileName=si.shortFileName, issue.fileName=si.fileName, issue.severity=si.severity, issue.confidence=si.confidence, issue.kingdom=si.kingdom, issue.issueType=si.issueType
, issue.issueSubtype=si.issueSubtype, issue.analyzer=si.analyzer, issue.engineType=si.engineType, issue.lineNumber=si.lineNumber, issue.taintFlag=si.taintFlag, issue.packageName=si.packageName
, issue.functionName=si.functionName, issue.className=si.className, issue.issueAbstract=si.issueAbstract, issue.issueRecommendation=si.issueRecommendation
, issue.friority = (case when p_customPriorityEnabled = 0 or issue.enginePriority is null then si.friority else issue.friority end)
, issue.enginePriority = (case when p_customPriorityEnabled = 0 or issue.enginePriority is null then null else si.friority end)
, issue.replaceStore=si.replaceStore
, issue.ruleGuid=si.ruleGuid, issue.snippetId=si.snippetId, issue.contextId=si.contextId, issue.category=si.category, issue.url=si.url, issue.source=si.source, issue.sourceContext=si.sourceContext, issue.sink=si.sink
, issue.sinkContext=si.sinkContext, issue.sourceFile=si.sourceFile, issue.audienceSet=si.audienceSet
, issue.scanStatus = (CASE WHEN issue.scanStatus='REMOVED' THEN 'REINTRODUCED' ELSE 'UPDATED' END)
, issue.findingGuid = si.findingGuid, issue.remediationConstant=si.remediationConstant,issue.likelihood=si.likelihood,issue.impact=si.impact, issue.accuracy=si.accuracy
, issue.rtaCovered=si.rtaCovered,issue.probability=si.probability
, issue.requestIdentifier=si.requestIdentifier, issue.requestHeader=si.requestHeader, issue.requestParameter=si.requestParameter, issue.requestBody=si.requestBody, issue.requestMethod=si.requestMethod
, issue.cookie=si.cookie, issue.httpVersion=si.httpVersion, issue.attackPayload=si.attackPayload, issue.attackType=si.attackType, issue.response=si.response, issue.triggerDefinition=si.triggerDefinition, issue.triggerString=si.triggerString
, issue.triggerDisplayText=si.triggerDisplayText, issue.secondaryRequest=si.secondaryRequest, issue.sourceLine=si.sourceLine, issue.mappedCategory=si.mappedCategory
, issue.minVirtualCallConfidence=si.minVirtualCallConfidence;
insert into issue_ca (issue_id, projectVersion_id, issueInstanceId, engineType, dataVersion, metadataPluginId,
integerValue01, integerValue02, integerValue03, decimalValue01, decimalValue02, decimalValue03, decimalValue04, decimalValue05,
dateValue01, dateValue02, dateValue03, dateValue04, dateValue05,
textValue01, textValue02, textValue03, textValue04, textValue05, textValue06, textValue07, textValue08, textValue09, textValue10, textValue11, textValue12, textValue13, textValue14, textValue15, textValue16,
clobValue01, clobValue02, clobValue03, clobValue04, clobValue05, clobValue06)
select i.id, sica.projectVersion_id, sica.issueInstanceId, sica.engineType, sica.dataVersion, sica.metadataPluginId,
sica.integerValue01, sica.integerValue02, sica.integerValue03, sica.decimalValue01, sica.decimalValue02, sica.decimalValue03, sica.decimalValue04, sica.decimalValue05,
sica.dateValue01, sica.dateValue02, sica.dateValue03, sica.dateValue04, sica.dateValue05,
sica.textValue01, sica.textValue02, sica.textValue03, sica.textValue04, sica.textValue05, sica.textValue06, sica.textValue07, sica.textValue08, sica.textValue09, sica.textValue10, sica.textValue11, sica.textValue12, sica.textValue13, sica.textValue14, sica.textValue15, sica.textValue16,
sica.clobValue01, sica.clobValue02, sica.clobValue03, sica.clobValue04, sica.clobValue05, sica.clobValue06
from scan_issue_ca sica
join issue i ON i.engineType=sica.engineType AND i.issueInstanceId=sica.issueInstanceId where sica.scan_id = p_scan_id AND sica.projectVersion_id = p_projectVersion_id AND i.projectVersion_id=p_projectVersion_id
on duplicate key update issue_ca.issueInstanceId=sica.issueInstanceId, issue_ca.engineType=sica.engineType, issue_ca.dataVersion=sica.dataVersion,
issue_ca.integerValue01=sica.integerValue01, issue_ca.integerValue02=sica.integerValue02, issue_ca.integerValue03=sica.integerValue03, issue_ca.decimalValue01=sica.decimalValue01, issue_ca.decimalValue02=sica.decimalValue02, issue_ca.decimalValue03=sica.decimalValue03, issue_ca.decimalValue04=sica.decimalValue04, issue_ca.decimalValue05=sica.decimalValue05,
issue_ca.dateValue01=sica.dateValue01, issue_ca.dateValue02=sica.dateValue02, issue_ca.dateValue03=sica.dateValue03, issue_ca.dateValue04=sica.dateValue04, issue_ca.dateValue05=sica.dateValue05,
issue_ca.textValue01=sica.textValue01, issue_ca.textValue02=sica.textValue02, issue_ca.textValue03=sica.textValue03, issue_ca.textValue04=sica.textValue04, issue_ca.textValue05=sica.textValue05, issue_ca.textValue06=sica.textValue06, issue_ca.textValue07=sica.textValue07, issue_ca.textValue08=sica.textValue08, issue_ca.textValue09=sica.textValue09, issue_ca.textValue10=sica.textValue10,
issue_ca.textValue11=sica.textValue11, issue_ca.textValue12=sica.textValue12, issue_ca.textValue13=sica.textValue13, issue_ca.textValue14=sica.textValue14, issue_ca.textValue15=sica.textValue15, issue_ca.textValue16=sica.textValue16,
issue_ca.clobValue01=sica.clobValue01, issue_ca.clobValue02=sica.clobValue02, issue_ca.clobValue03=sica.clobValue03, issue_ca.clobValue04=sica.clobValue04, issue_ca.clobValue05=sica.clobValue05, issue_ca.clobValue06=sica.clobValue06;
END//
DELIMITER ;
UPDATE db_ssc_2222.DATABASECHANGELOG SET COMMENTS = '', CONTEXTS = NULL, DATEEXECUTED = NOW(), DEPLOYMENT_ID = '0148965608', EXECTYPE = 'RERAN', LABELS = NULL, MD5SUM = '8:eb053150f34f5adc61b0b16f91762166', ORDEREXECUTED = 843 WHERE ID = 'updateExistingWithLatest_mysql' AND AUTHOR = 'hp_main' AND FILENAME = 'procs/dbF360_updateExistingWithLatest.xml';
-- Release Database Lock
UPDATE db_ssc_2222.DATABASECHANGELOGLOCK SET `LOCKED` = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;