From e56fc5a6aa170161d32e171cf3b499a691924bd2 Mon Sep 17 00:00:00 2001 From: "antoine.vinot" Date: Mon, 2 Sep 2024 11:10:31 +0200 Subject: [PATCH] SONAR-22914 Add CVEs DB migration and DAOs --- .../java/org/sonar/db/version/SqTables.java | 3 + .../org/sonar/db/dependency/CveCweDaoIT.java | 73 +++++++++++++++ .../org/sonar/db/dependency/CveDaoIT.java | 92 +++++++++++++++++++ .../db/dependency/IssuesDependencyDaoIT.java | 53 +++++++++++ .../src/main/java/org/sonar/db/DaoModule.java | 8 +- .../src/main/java/org/sonar/db/DbClient.java | 24 ++++- .../src/main/java/org/sonar/db/MyBatis.java | 18 +++- .../org/sonar/db/dependency/CveCweDao.java | 39 ++++++++ .../org/sonar/db/dependency/CveCweDto.java | 23 +++++ .../org/sonar/db/dependency/CveCweMapper.java | 28 ++++++ .../java/org/sonar/db/dependency/CveDao.java | 39 ++++++++ .../java/org/sonar/db/dependency/CveDto.java | 35 +++++++ .../org/sonar/db/dependency/CveMapper.java | 26 ++++++ .../db/dependency/IssuesDependencyDao.java | 34 +++++++ .../db/dependency/IssuesDependencyDto.java | 23 +++++ .../db/dependency/IssuesDependencyMapper.java | 24 +++++ .../org/sonar/db/dependency/package-info.java | 23 +++++ .../org/sonar/db/dependency/CveCweMapper.xml | 21 +++++ .../org/sonar/db/dependency/CveMapper.xml | 52 +++++++++++ .../db/dependency/IssuesDependencyMapper.xml | 15 +++ server/sonar-db-dao/src/schema/schema-sq.ddl | 26 ++++++ .../version/v107/CreateCveCweTableIT.java | 63 +++++++++++++ .../version/v107/CreateCvesTableIT.java | 74 +++++++++++++++ .../v107/CreateIssuesDependencyTableIT.java | 62 +++++++++++++ .../version/v107/CreateCveCweTable.java | 50 ++++++++++ .../version/v107/CreateCvesTable.java | 71 ++++++++++++++ .../v107/CreateIssuesDependencyTable.java | 45 +++++++++ .../migration/version/v107/DbVersion107.java | 6 +- 28 files changed, 1043 insertions(+), 7 deletions(-) create mode 100644 server/sonar-db-dao/src/it/java/org/sonar/db/dependency/CveCweDaoIT.java create mode 100644 server/sonar-db-dao/src/it/java/org/sonar/db/dependency/CveDaoIT.java create mode 100644 server/sonar-db-dao/src/it/java/org/sonar/db/dependency/IssuesDependencyDaoIT.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweDao.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweDto.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweMapper.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveDao.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveDto.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveMapper.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyDao.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyDto.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyMapper.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/dependency/package-info.java create mode 100644 server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/CveCweMapper.xml create mode 100644 server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/CveMapper.xml create mode 100644 server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/IssuesDependencyMapper.xml create mode 100644 server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateCveCweTableIT.java create mode 100644 server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateCvesTableIT.java create mode 100644 server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateIssuesDependencyTableIT.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateCveCweTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateCvesTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateIssuesDependencyTable.java diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java b/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java index bc3d0ab98a0..d49418ecf62 100644 --- a/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java +++ b/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java @@ -43,6 +43,8 @@ public final class SqTables { "ce_task_input", "ce_task_message", "ce_scanner_context", + "cves", + "cve_cwe", "components", "default_qprofiles", "deprecated_rule_keys", @@ -60,6 +62,7 @@ public final class SqTables { "internal_component_props", "internal_properties", "issues", + "issues_dependency", "issues_fixed", "issues_impacts", "issue_changes", diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/CveCweDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/CveCweDaoIT.java new file mode 100644 index 00000000000..7b9587201f5 --- /dev/null +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/CveCweDaoIT.java @@ -0,0 +1,73 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +import java.util.List; +import java.util.Map; +import java.util.Set; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.RegisterExtension; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +import static org.assertj.core.api.Assertions.assertThat; + +class CveCweDaoIT { + + @RegisterExtension + private final DbTester db = DbTester.create(System2.INSTANCE); + + private final CveCweDao cveCweDao = db.getDbClient().cveCweDao(); + + @Test + void insert_shouldPersistCveCwe() { + var cveCweDto = new CveCweDto("CVE_UUID", "CWE-123"); + + cveCweDao.insert(db.getSession(), cveCweDto); + + List> result = db.select(db.getSession(), "select * from cve_cwe"); + assertThat(result).hasSize(1); + assertThat(result.get(0)).containsExactlyInAnyOrderEntriesOf( + Map.of( + "cve_uuid", cveCweDto.cveUuid(), + "cwe", cveCweDto.cwe()) + ); + } + + @Test + void selectByCveUuid_shouldReturnCwesAttachedToCve() { + String cveUuid = "CVE_UUID"; + cveCweDao.insert(db.getSession(), new CveCweDto(cveUuid, "CWE-123")); + cveCweDao.insert(db.getSession(), new CveCweDto(cveUuid, "CWE-456")); + cveCweDao.insert(db.getSession(), new CveCweDto("ANOTHER_CVE_UUID", "CWE-789")); + + Set result = cveCweDao.selectByCveUuid(db.getSession(), cveUuid); + + assertThat(result).containsExactlyInAnyOrder("CWE-123", "CWE-456"); + } + + @Test + void selectByCveUuid_shouldReturnEmpty_whenNoCweAttachedToCve() { + Set result = cveCweDao.selectByCveUuid(db.getSession(), "some_uuid"); + + assertThat(result).isEmpty(); + } + +} diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/CveDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/CveDaoIT.java new file mode 100644 index 00000000000..00c04e9df21 --- /dev/null +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/CveDaoIT.java @@ -0,0 +1,92 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +import java.util.List; +import java.util.Map; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.RegisterExtension; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.fail; + +class CveDaoIT { + + @RegisterExtension + private final DbTester db = DbTester.create(System2.INSTANCE); + + private final CveDao cveDao = db.getDbClient().cveDao(); + + @Test + void insert_shouldPersistCve() { + var cveDto = new CveDto("CVE_UUID", + "CVE-2021-12345", + "Some CVE description", + 7.5, + 0.00109, + 0.447540000, + System.currentTimeMillis() - 2_000, + System.currentTimeMillis() - 1_500, + System.currentTimeMillis() - 1_000, + System.currentTimeMillis() - 500); + + cveDao.insert(db.getSession(), cveDto); + + List> result = db.select(db.getSession(), "select * from cves"); + assertThat(result).hasSize(1); + assertThat(result.get(0)).containsExactlyInAnyOrderEntriesOf( + Map.of( + "uuid", cveDto.uuid(), + "id", cveDto.id(), + "description", cveDto.description(), + "cvss_score", cveDto.cvssScore(), + "epss_score", cveDto.epssScore(), + "epss_percentile", cveDto.epssPercentile(), + "published_at", cveDto.publishedAt(), + "last_modified_at", cveDto.lastModifiedAt(), + "created_at", cveDto.createdAt(), + "updated_at", cveDto.updatedAt()) + ); + } + + @Test + void selectById_shouldReturnCve() { + String cveId = "CVE-2021-12345"; + var cveDto = new CveDto("CVE_UUID", + cveId, + "Some CVE description", + 7.5, + 0.00109, + 0.447540000, + System.currentTimeMillis() - 2_000, + System.currentTimeMillis() - 1_500, + System.currentTimeMillis() - 1_000, + System.currentTimeMillis() - 500); + cveDao.insert(db.getSession(), cveDto); + + CveDto result = cveDao.selectById(db.getSession(), cveId) + .orElseGet(() -> fail("Cve not found")); + + assertThat(result).usingRecursiveComparison().isEqualTo(cveDto); + } + +} diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/IssuesDependencyDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/IssuesDependencyDaoIT.java new file mode 100644 index 00000000000..9117091308e --- /dev/null +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/dependency/IssuesDependencyDaoIT.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +import java.util.List; +import java.util.Map; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.RegisterExtension; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +import static org.assertj.core.api.Assertions.assertThat; + +class IssuesDependencyDaoIT { + + @RegisterExtension + private final DbTester db = DbTester.create(System2.INSTANCE); + + private final IssuesDependencyDao issuesDependencyDao = db.getDbClient().issuesDependencyDao(); + + @Test + void insert_shouldPersistIssuesDependency() { + var issuesDependencyDto = new IssuesDependencyDto("ISSUE_UUID", "CVE_UUID"); + + issuesDependencyDao.insert(db.getSession(), issuesDependencyDto); + + List> result = db.select(db.getSession(), "select * from issues_dependency"); + assertThat(result).hasSize(1); + assertThat(result.get(0)).containsExactlyInAnyOrderEntriesOf( + Map.of( + "issue_uuid", issuesDependencyDto.issueUuid(), + "cve_uuid", issuesDependencyDto.cveUuid()) + ); + + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java b/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java index fb0ac071f4b..f11922bff2a 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java @@ -38,6 +38,9 @@ import org.sonar.db.component.ComponentDao; import org.sonar.db.component.ComponentKeyUpdaterDao; import org.sonar.db.component.ProjectLinkDao; import org.sonar.db.component.SnapshotDao; +import org.sonar.db.dependency.CveCweDao; +import org.sonar.db.dependency.CveDao; +import org.sonar.db.dependency.IssuesDependencyDao; import org.sonar.db.duplication.DuplicationDao; import org.sonar.db.entity.EntityDao; import org.sonar.db.es.EsQueueDao; @@ -65,8 +68,8 @@ import org.sonar.db.project.ProjectExportDao; import org.sonar.db.property.InternalComponentPropertiesDao; import org.sonar.db.property.InternalPropertiesDao; import org.sonar.db.property.PropertiesDao; -import org.sonar.db.provisioning.GithubOrganizationGroupDao; import org.sonar.db.provisioning.DevOpsPermissionsMappingDao; +import org.sonar.db.provisioning.GithubOrganizationGroupDao; import org.sonar.db.purge.PurgeDao; import org.sonar.db.pushevent.PushEventDao; import org.sonar.db.qualitygate.ProjectQgateAssociationDao; @@ -126,6 +129,8 @@ public class DaoModule extends Module { CeTaskMessageDao.class, ComponentDao.class, ComponentKeyUpdaterDao.class, + CveDao.class, + CveCweDao.class, DefaultQProfileDao.class, DevOpsPermissionsMappingDao.class, DuplicationDao.class, @@ -147,6 +152,7 @@ public class DaoModule extends Module { IssueChangeDao.class, IssueDao.class, IssueFixedDao.class, + IssuesDependencyDao.class, LiveMeasureDao.class, ProjectMeasureDao.class, MetricDao.class, diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java b/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java index 598d3663e1c..d04aff1df52 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java @@ -38,6 +38,9 @@ import org.sonar.db.component.ComponentDao; import org.sonar.db.component.ComponentKeyUpdaterDao; import org.sonar.db.component.ProjectLinkDao; import org.sonar.db.component.SnapshotDao; +import org.sonar.db.dependency.CveCweDao; +import org.sonar.db.dependency.CveDao; +import org.sonar.db.dependency.IssuesDependencyDao; import org.sonar.db.duplication.DuplicationDao; import org.sonar.db.entity.EntityDao; import org.sonar.db.es.EsQueueDao; @@ -65,8 +68,8 @@ import org.sonar.db.project.ProjectExportDao; import org.sonar.db.property.InternalComponentPropertiesDao; import org.sonar.db.property.InternalPropertiesDao; import org.sonar.db.property.PropertiesDao; -import org.sonar.db.provisioning.GithubOrganizationGroupDao; import org.sonar.db.provisioning.DevOpsPermissionsMappingDao; +import org.sonar.db.provisioning.GithubOrganizationGroupDao; import org.sonar.db.purge.PurgeDao; import org.sonar.db.pushevent.PushEventDao; import org.sonar.db.qualitygate.ProjectQgateAssociationDao; @@ -188,7 +191,6 @@ public class DbClient { private final ScimGroupDao scimGroupDao; private final EntityDao entityDao; private final AnticipatedTransitionDao anticipatedTransitionDao; - private final ReportScheduleDao reportScheduleDao; private final ReportSubscriptionDao reportSubscriptionDao; private final GithubOrganizationGroupDao githubOrganizationGroupDao; @@ -197,6 +199,9 @@ public class DbClient { private final ProjectExportDao projectExportDao; private final IssueFixedDao issueFixedDao; private final TelemetryMetricsSentDao telemetryMetricsSentDao; + private final CveDao cveDao; + private final CveCweDao cveCweDao; + private final IssuesDependencyDao issuesDependencyDao; public DbClient(Database database, MyBatis myBatis, DBSessions dbSessions, Dao... daos) { this.database = database; @@ -291,6 +296,9 @@ public class DbClient { projectExportDao = getDao(map, ProjectExportDao.class); issueFixedDao = getDao(map, IssueFixedDao.class); telemetryMetricsSentDao = getDao(map, TelemetryMetricsSentDao.class); + cveDao = getDao(map, CveDao.class); + cveCweDao = getDao(map, CveCweDao.class); + issuesDependencyDao = getDao(map, IssuesDependencyDao.class); } public DbSession openSession(boolean batch) { @@ -646,4 +654,16 @@ public class DbClient { public ProjectExportDao projectExportDao() { return projectExportDao; } + + public CveDao cveDao() { + return cveDao; + } + + public CveCweDao cveCweDao() { + return cveCweDao; + } + + public IssuesDependencyDao issuesDependencyDao() { + return issuesDependencyDao; + } } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java index 543c465f450..4191942d8d0 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java @@ -63,6 +63,12 @@ import org.sonar.db.component.SnapshotDto; import org.sonar.db.component.SnapshotMapper; import org.sonar.db.component.UuidWithBranchUuidDto; import org.sonar.db.component.ViewsSnapshotDto; +import org.sonar.db.dependency.CveCweDto; +import org.sonar.db.dependency.CveCweMapper; +import org.sonar.db.dependency.CveDto; +import org.sonar.db.dependency.CveMapper; +import org.sonar.db.dependency.IssuesDependencyDto; +import org.sonar.db.dependency.IssuesDependencyMapper; import org.sonar.db.duplication.DuplicationMapper; import org.sonar.db.duplication.DuplicationUnitDto; import org.sonar.db.entity.EntityDto; @@ -83,9 +89,9 @@ import org.sonar.db.issue.NewCodeReferenceIssueDto; import org.sonar.db.issue.PrIssueDto; import org.sonar.db.measure.LargestBranchNclocDto; import org.sonar.db.measure.LiveMeasureMapper; +import org.sonar.db.measure.ProjectLocDistributionDto; import org.sonar.db.measure.ProjectMeasureDto; import org.sonar.db.measure.ProjectMeasureMapper; -import org.sonar.db.measure.ProjectLocDistributionDto; import org.sonar.db.metric.MetricMapper; import org.sonar.db.newcodeperiod.NewCodePeriodMapper; import org.sonar.db.notification.NotificationQueueDto; @@ -119,10 +125,10 @@ import org.sonar.db.property.InternalPropertiesMapper; import org.sonar.db.property.InternalPropertyDto; import org.sonar.db.property.PropertiesMapper; import org.sonar.db.property.ScrapPropertyDto; -import org.sonar.db.provisioning.GithubOrganizationGroupDto; -import org.sonar.db.provisioning.GithubOrganizationGroupMapper; import org.sonar.db.provisioning.DevOpsPermissionsMappingDto; import org.sonar.db.provisioning.DevOpsPermissionsMappingMapper; +import org.sonar.db.provisioning.GithubOrganizationGroupDto; +import org.sonar.db.provisioning.GithubOrganizationGroupMapper; import org.sonar.db.purge.PurgeMapper; import org.sonar.db.purge.PurgeableAnalysisDto; import org.sonar.db.pushevent.PushEventDto; @@ -208,6 +214,8 @@ public class MyBatis { confBuilder.loadAlias("AnticipatedTransition", AnticipatedTransitionDto.class); confBuilder.loadAlias("CeTaskCharacteristic", CeTaskCharacteristicDto.class); confBuilder.loadAlias("Component", ComponentDto.class); + confBuilder.loadAlias("Cve", CveDto.class); + confBuilder.loadAlias("CveCwe", CveCweDto.class); confBuilder.loadAlias("DevOpsPermissionsMapping", DevOpsPermissionsMappingDto.class); confBuilder.loadAlias("DuplicationUnit", DuplicationUnitDto.class); confBuilder.loadAlias("Entity", EntityDto.class); @@ -225,6 +233,7 @@ public class MyBatis { confBuilder.loadAlias("KeyLongValue", KeyLongValue.class); confBuilder.loadAlias("Impact", ImpactDto.class); confBuilder.loadAlias("Issue", IssueDto.class); + confBuilder.loadAlias("IssueDependency", IssuesDependencyDto.class); confBuilder.loadAlias("NewCodeReferenceIssue", NewCodeReferenceIssueDto.class); confBuilder.loadAlias("ProjectMeasure", ProjectMeasureDto.class); confBuilder.loadAlias("LargestBranchNclocDto", LargestBranchNclocDto.class); @@ -284,6 +293,8 @@ public class MyBatis { CeTaskMessageMapper.class, ComponentKeyUpdaterMapper.class, ComponentMapper.class, + CveMapper.class, + CveCweMapper.class, LiveMeasureMapper.class, DefaultQProfileMapper.class, DuplicationMapper.class, @@ -304,6 +315,7 @@ public class MyBatis { IssueChangeMapper.class, IssueMapper.class, IssueFixedMapper.class, + IssuesDependencyMapper.class, ProjectMeasureMapper.class, MetricMapper.class, NewCodePeriodMapper.class, diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweDao.java new file mode 100644 index 00000000000..c974ae08e9f --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweDao.java @@ -0,0 +1,39 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +import java.util.Set; +import org.sonar.db.Dao; +import org.sonar.db.DbSession; + +public class CveCweDao implements Dao { + + public void insert(DbSession session, CveCweDto cveCweDto) { + mapper(session).insert(cveCweDto); + } + + private static CveCweMapper mapper(DbSession session) { + return session.getMapper(CveCweMapper.class); + } + + public Set selectByCveUuid(DbSession dbSession, String cveUuid) { + return mapper(dbSession).selectByCveUuid(cveUuid); + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweDto.java new file mode 100644 index 00000000000..00ab0897606 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweDto.java @@ -0,0 +1,23 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +public record CveCweDto(String cveUuid, String cwe) { +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweMapper.java new file mode 100644 index 00000000000..612eb7ba67f --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveCweMapper.java @@ -0,0 +1,28 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +import java.util.Set; + +public interface CveCweMapper { + void insert(CveCweDto cveCweDto); + + Set selectByCveUuid(String cveUuid); +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveDao.java new file mode 100644 index 00000000000..640ed6eb173 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveDao.java @@ -0,0 +1,39 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +import java.util.Optional; +import org.sonar.db.Dao; +import org.sonar.db.DbSession; + +public class CveDao implements Dao { + + public void insert(DbSession dbSession, CveDto cveDto) { + mapper(dbSession).insert(cveDto); + } + + public Optional selectById(DbSession dbSession, String id) { + return Optional.ofNullable(mapper(dbSession).selectById(id)); + } + + private static CveMapper mapper(DbSession dbSession) { + return dbSession.getMapper(CveMapper.class); + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveDto.java new file mode 100644 index 00000000000..53278c024c4 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveDto.java @@ -0,0 +1,35 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +public record CveDto( + String uuid, + String id, + String description, + double cvssScore, + double epssScore, + double epssPercentile, + Long publishedAt, + Long lastModifiedAt, + Long createdAt, + Long updatedAt +) { + +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveMapper.java new file mode 100644 index 00000000000..1cf344709ce --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/CveMapper.java @@ -0,0 +1,26 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +public interface CveMapper { + void insert(CveDto cveDto); + + CveDto selectById(String id); +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyDao.java new file mode 100644 index 00000000000..7fd6d79bf76 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyDao.java @@ -0,0 +1,34 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +import org.sonar.db.Dao; +import org.sonar.db.DbSession; + +public class IssuesDependencyDao implements Dao { + + public void insert(DbSession session, IssuesDependencyDto issuesDependencyDto) { + mapper(session).insert(issuesDependencyDto); + } + + private static IssuesDependencyMapper mapper(DbSession session) { + return session.getMapper(IssuesDependencyMapper.class); + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyDto.java new file mode 100644 index 00000000000..5c43f55f4e9 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyDto.java @@ -0,0 +1,23 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +public record IssuesDependencyDto(String issueUuid, String cveUuid) { +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyMapper.java new file mode 100644 index 00000000000..1a14f05eec5 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/IssuesDependencyMapper.java @@ -0,0 +1,24 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.db.dependency; + +public interface IssuesDependencyMapper { + void insert(IssuesDependencyDto issuesDependencyDto); +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/package-info.java b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/package-info.java new file mode 100644 index 00000000000..d9066c96c2f --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/dependency/package-info.java @@ -0,0 +1,23 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +@ParametersAreNonnullByDefault +package org.sonar.db.dependency; + +import javax.annotation.ParametersAreNonnullByDefault; diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/CveCweMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/CveCweMapper.xml new file mode 100644 index 00000000000..5002efd6e28 --- /dev/null +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/CveCweMapper.xml @@ -0,0 +1,21 @@ + + + + + + insert into cve_cwe ( + cve_uuid, + cwe + ) values ( + #{cveUuid, jdbcType=VARCHAR}, + #{cwe, jdbcType=VARCHAR} + ) + + + + + diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/CveMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/CveMapper.xml new file mode 100644 index 00000000000..22833f96b4f --- /dev/null +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/CveMapper.xml @@ -0,0 +1,52 @@ + + + + + + c.uuid as uuid, + c.id as id, + c.description as description, + c.cvss_score as cvssScore, + c.epss_score as epssScore, + c.epss_percentile as epssPercentile, + c.published_at as publishedAt, + c.last_modified_at as lastModifiedAt, + c.created_at as createdAt, + c.updated_at as updatedAt + + + + insert into cves ( + uuid, + id, + description, + cvss_score, + epss_score, + epss_percentile, + published_at, + last_modified_at, + created_at, + updated_at + ) values ( + #{uuid, jdbcType=VARCHAR}, + #{id, jdbcType=VARCHAR}, + #{description, jdbcType=VARCHAR}, + #{cvssScore, jdbcType=DOUBLE}, + #{epssScore, jdbcType=DOUBLE}, + #{epssPercentile, jdbcType=DOUBLE}, + #{publishedAt, jdbcType=BIGINT}, + #{lastModifiedAt, jdbcType=BIGINT}, + #{createdAt, jdbcType=BIGINT}, + #{updatedAt, jdbcType=BIGINT} + ) + + + + + diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/IssuesDependencyMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/IssuesDependencyMapper.xml new file mode 100644 index 00000000000..d6cb24539f3 --- /dev/null +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/dependency/IssuesDependencyMapper.xml @@ -0,0 +1,15 @@ + + + + + + insert into issues_dependency ( + issue_uuid, + cve_uuid + ) values ( + #{issueUuid, jdbcType=VARCHAR}, + #{cveUuid, jdbcType=VARCHAR} + ) + + + diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index 3378740caa0..e37c2c8ade4 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -249,6 +249,26 @@ CREATE UNIQUE NULLS NOT DISTINCT INDEX "COMPONENTS_UUID" ON "COMPONENTS"("UUID" CREATE INDEX "COMPONENTS_BRANCH_UUID" ON "COMPONENTS"("BRANCH_UUID" NULLS FIRST); CREATE UNIQUE NULLS NOT DISTINCT INDEX "COMPONENTS_KEE_BRANCH_UUID" ON "COMPONENTS"("KEE" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); +CREATE TABLE "CVE_CWE"( + "CVE_UUID" CHARACTER VARYING(40) NOT NULL, + "CWE" CHARACTER VARYING(50) NOT NULL +); +ALTER TABLE "CVE_CWE" ADD CONSTRAINT "PK_CVE_CWE" PRIMARY KEY("CVE_UUID", "CWE"); + +CREATE TABLE "CVES"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "ID" CHARACTER VARYING(50) NOT NULL, + "DESCRIPTION" CHARACTER VARYING(4000) NOT NULL, + "CVSS_SCORE" DOUBLE PRECISION, + "EPSS_SCORE" DOUBLE PRECISION, + "EPSS_PERCENTILE" DOUBLE PRECISION, + "PUBLISHED_AT" BIGINT NOT NULL, + "LAST_MODIFIED_AT" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "CVES" ADD CONSTRAINT "PK_CVES" PRIMARY KEY("UUID"); + CREATE TABLE "DEFAULT_QPROFILES"( "LANGUAGE" CHARACTER VARYING(20) NOT NULL, "QPROFILE_UUID" CHARACTER VARYING(255) NOT NULL, @@ -476,6 +496,12 @@ CREATE INDEX "ISSUES_RESOLUTION" ON "ISSUES"("RESOLUTION" NULLS FIRST); CREATE INDEX "ISSUES_UPDATED_AT" ON "ISSUES"("UPDATED_AT" NULLS FIRST); CREATE INDEX "ISSUES_RULE_UUID" ON "ISSUES"("RULE_UUID" NULLS FIRST); +CREATE TABLE "ISSUES_DEPENDENCY"( + "ISSUE_UUID" CHARACTER VARYING(40) NOT NULL, + "CVE_UUID" CHARACTER VARYING(40) NOT NULL +); +ALTER TABLE "ISSUES_DEPENDENCY" ADD CONSTRAINT "PK_ISSUES_DEPENDENCY" PRIMARY KEY("ISSUE_UUID"); + CREATE TABLE "ISSUES_FIXED"( "PULL_REQUEST_UUID" CHARACTER VARYING(40) NOT NULL, "ISSUE_KEY" CHARACTER VARYING(50) NOT NULL diff --git a/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateCveCweTableIT.java b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateCveCweTableIT.java new file mode 100644 index 00000000000..73dafb0b9bc --- /dev/null +++ b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateCveCweTableIT.java @@ -0,0 +1,63 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v107; + +import java.sql.SQLException; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.RegisterExtension; +import org.sonar.db.MigrationDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static java.sql.Types.VARCHAR; +import static org.sonar.db.MigrationDbTester.createForMigrationStep; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.DESCRIPTION_SECTION_KEY_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; + +class CreateCveCweTableIT { + + private static final String TABLE_NAME = "cve_cwe"; + + @RegisterExtension + public final MigrationDbTester db = createForMigrationStep(CreateCveCweTable.class); + + private final DdlChange createCveCweTable = new CreateCveCweTable(db.database()); + + @Test + void execute_shouldCreateTable() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + createCveCweTable.execute(); + + db.assertTableExists(TABLE_NAME); + db.assertColumnDefinition(TABLE_NAME, "cve_uuid", VARCHAR, UUID_SIZE, false); + db.assertColumnDefinition(TABLE_NAME, "cwe", VARCHAR, DESCRIPTION_SECTION_KEY_SIZE, false); + db.assertPrimaryKey(TABLE_NAME, "pk_cve_cwe", "cve_uuid", "cwe"); + } + + @Test + void execute_shouldBeReentrant() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + createCveCweTable.execute(); + + createCveCweTable.execute(); + + db.assertTableExists(TABLE_NAME); + } +} diff --git a/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateCvesTableIT.java b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateCvesTableIT.java new file mode 100644 index 00000000000..6586f90d114 --- /dev/null +++ b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateCvesTableIT.java @@ -0,0 +1,74 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v107; + +import java.sql.SQLException; +import java.sql.Types; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.RegisterExtension; +import org.sonar.db.MigrationDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static java.sql.Types.BIGINT; +import static java.sql.Types.VARCHAR; +import static org.sonar.db.MigrationDbTester.createForMigrationStep; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.DESCRIPTION_SECTION_KEY_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.MAX_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; + +class CreateCvesTableIT { + + private static final String TABLE_NAME = "cves"; + + @RegisterExtension + public final MigrationDbTester db = createForMigrationStep(CreateCvesTable.class); + + private final DdlChange createCvesTable = new CreateCvesTable(db.database()); + + @Test + void execute_shouldCreateTable() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + createCvesTable.execute(); + + db.assertTableExists(TABLE_NAME); + db.assertColumnDefinition(TABLE_NAME, "uuid", VARCHAR, UUID_SIZE, false); + db.assertColumnDefinition(TABLE_NAME, "id", VARCHAR, DESCRIPTION_SECTION_KEY_SIZE, false); + db.assertColumnDefinition(TABLE_NAME, "description", VARCHAR, MAX_SIZE, false); + db.assertColumnDefinition(TABLE_NAME, "cvss_score", Types.DOUBLE, null, true); + db.assertColumnDefinition(TABLE_NAME, "epss_score", Types.DOUBLE, null, true); + db.assertColumnDefinition(TABLE_NAME, "epss_percentile", Types.DOUBLE, null, true); + db.assertColumnDefinition(TABLE_NAME, "published_at", BIGINT, null, false); + db.assertColumnDefinition(TABLE_NAME, "last_modified_at", BIGINT, null, false); + db.assertColumnDefinition(TABLE_NAME, "created_at", BIGINT, null, false); + db.assertColumnDefinition(TABLE_NAME, "updated_at", BIGINT, null, false); + db.assertPrimaryKey(TABLE_NAME, "pk_cves", "uuid"); + } + + @Test + void execute_shouldBeReentrant() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + createCvesTable.execute(); + + createCvesTable.execute(); + + db.assertTableExists(TABLE_NAME); + } +} diff --git a/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateIssuesDependencyTableIT.java b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateIssuesDependencyTableIT.java new file mode 100644 index 00000000000..21c32f8a967 --- /dev/null +++ b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v107/CreateIssuesDependencyTableIT.java @@ -0,0 +1,62 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v107; + +import java.sql.SQLException; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.RegisterExtension; +import org.sonar.db.MigrationDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static java.sql.Types.VARCHAR; +import static org.sonar.db.MigrationDbTester.createForMigrationStep; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; + +class CreateIssuesDependencyTableIT { + + private static final String TABLE_NAME = "issues_dependency"; + + @RegisterExtension + public final MigrationDbTester db = createForMigrationStep(CreateIssuesDependencyTable.class); + + private final DdlChange createIssuesDependencyTable = new CreateIssuesDependencyTable(db.database()); + + @Test + void execute_shouldCreateTable() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + createIssuesDependencyTable.execute(); + + db.assertTableExists(TABLE_NAME); + db.assertColumnDefinition(TABLE_NAME, "issue_uuid", VARCHAR, UUID_SIZE, false); + db.assertColumnDefinition(TABLE_NAME, "cve_uuid", VARCHAR, UUID_SIZE, false); + db.assertPrimaryKey(TABLE_NAME, "pk_issues_dependency", "issue_uuid"); + } + + @Test + void execute_shouldBeReentrant() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + createIssuesDependencyTable.execute(); + + createIssuesDependencyTable.execute(); + + db.assertTableExists(TABLE_NAME); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateCveCweTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateCveCweTable.java new file mode 100644 index 00000000000..125c36819ed --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateCveCweTable.java @@ -0,0 +1,50 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v107; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +import org.sonar.server.platform.db.migration.sql.CreateTableBuilder; +import org.sonar.server.platform.db.migration.step.CreateTableChange; + +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.DESCRIPTION_SECTION_KEY_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class CreateCveCweTable extends CreateTableChange { + + private static final String TABLE_NAME = "cve_cwe"; + + public static final VarcharColumnDef CVE_UUID_COLUMN = newVarcharColumnDefBuilder().setColumnName("cve_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + public static final VarcharColumnDef CWE_COLUMN = newVarcharColumnDefBuilder().setColumnName("cwe").setIsNullable(false).setLimit(DESCRIPTION_SECTION_KEY_SIZE).build(); + + protected CreateCveCweTable(Database db) { + super(db, TABLE_NAME); + } + + @Override + public void execute(Context context, String tableName) throws SQLException { + context.execute(new CreateTableBuilder(getDialect(), tableName) + .addPkColumn(CVE_UUID_COLUMN) + .addPkColumn(CWE_COLUMN) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateCvesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateCvesTable.java new file mode 100644 index 00000000000..950e136ea40 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateCvesTable.java @@ -0,0 +1,71 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v107; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.def.BigIntegerColumnDef; +import org.sonar.server.platform.db.migration.def.DecimalColumnDef; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +import org.sonar.server.platform.db.migration.sql.CreateTableBuilder; +import org.sonar.server.platform.db.migration.step.CreateTableChange; + +import static org.sonar.server.platform.db.migration.def.BigIntegerColumnDef.newBigIntegerColumnDefBuilder; +import static org.sonar.server.platform.db.migration.def.DecimalColumnDef.newDecimalColumnDefBuilder; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.DESCRIPTION_SECTION_KEY_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.MAX_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class CreateCvesTable extends CreateTableChange { + + private static final String TABLE_NAME = "cves"; + + private static final VarcharColumnDef UUID_COLUMN = newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + private static final VarcharColumnDef ID_COLUMN = newVarcharColumnDefBuilder().setColumnName("id").setIsNullable(false).setLimit(DESCRIPTION_SECTION_KEY_SIZE).build(); + private static final VarcharColumnDef DESCRIPTION_COLUMN = newVarcharColumnDefBuilder().setColumnName("description").setIsNullable(false).setLimit(MAX_SIZE).build(); + public static final BigIntegerColumnDef UPDATED_AT_COLUMN = newBigIntegerColumnDefBuilder().setColumnName("updated_at").setIsNullable(false).build(); + public static final BigIntegerColumnDef CREATED_AT_COLUMN = newBigIntegerColumnDefBuilder().setColumnName("created_at").setIsNullable(false).build(); + public static final BigIntegerColumnDef LAST_MODIFIED_COLUMN = newBigIntegerColumnDefBuilder().setColumnName("last_modified_at").setIsNullable(false).build(); + public static final BigIntegerColumnDef PUBLISHED_COLUMN = newBigIntegerColumnDefBuilder().setColumnName("published_at").setIsNullable(false).build(); + public static final DecimalColumnDef CVSS_SCORE_COLUMN = newDecimalColumnDefBuilder().setColumnName("cvss_score").setIsNullable(true).build(); + public static final DecimalColumnDef EPSS_SCORE_COLUMN = newDecimalColumnDefBuilder().setColumnName("epss_score").setIsNullable(true).build(); + public static final DecimalColumnDef EPSS_PERCENTILE_COLUMN = newDecimalColumnDefBuilder().setColumnName("epss_percentile").setIsNullable(true).build(); + + protected CreateCvesTable(Database db) { + super(db, TABLE_NAME); + } + + @Override + public void execute(Context context, String tableName) throws SQLException { + context.execute(new CreateTableBuilder(getDialect(), tableName) + .addPkColumn(UUID_COLUMN) + .addColumn(ID_COLUMN) + .addColumn(DESCRIPTION_COLUMN) + .addColumn(CVSS_SCORE_COLUMN) + .addColumn(EPSS_SCORE_COLUMN) + .addColumn(EPSS_PERCENTILE_COLUMN) + .addColumn(PUBLISHED_COLUMN) + .addColumn(LAST_MODIFIED_COLUMN) + .addColumn(CREATED_AT_COLUMN) + .addColumn(UPDATED_AT_COLUMN) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateIssuesDependencyTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateIssuesDependencyTable.java new file mode 100644 index 00000000000..80a012cf3b5 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/CreateIssuesDependencyTable.java @@ -0,0 +1,45 @@ +/* + * SonarQube + * Copyright (C) 2009-2024 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v107; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.CreateTableBuilder; +import org.sonar.server.platform.db.migration.step.CreateTableChange; + +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class CreateIssuesDependencyTable extends CreateTableChange { + + private static final String TABLE_NAME = "issues_dependency"; + + protected CreateIssuesDependencyTable(Database db) { + super(db, TABLE_NAME); + } + + @Override + public void execute(Context context, String tableName) throws SQLException { + context.execute(new CreateTableBuilder(getDialect(), tableName) + .addPkColumn(newVarcharColumnDefBuilder().setColumnName("issue_uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("cve_uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/DbVersion107.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/DbVersion107.java index 36c626b67a3..2ca360a3244 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/DbVersion107.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v107/DbVersion107.java @@ -52,7 +52,11 @@ public class DbVersion107 implements DbVersion { .add(10_7_009, "Drop index 'uniq_github_perm_mappings' in the 'devops_perms_mapping' table", DropIndexUniqGithubPermsMappingInDevopsPermsMappingTable.class) .add(10_7_010, "Create uniq index on 'devops_perms_mapping' table for columns 'devops_platform_role', 'sonarqube_permission' and 'devops_platform'", CreateUniqueIndexOnDevopsPermsMappingTable.class) - .add(10_7_011, "Add default permissions for GitLab in 'devops_perms_mapping'", PopulateGitlabDevOpsPermissionsMapping.class); + .add(10_7_011, "Add default permissions for GitLab in 'devops_perms_mapping'", PopulateGitlabDevOpsPermissionsMapping.class) + .add(10_7_012, "Create 'cves' table", CreateCvesTable.class) + .add(10_7_013, "Create 'cve_cwe' table", CreateCveCweTable.class) + .add(10_7_014, "Create 'issues_dependency' table", CreateIssuesDependencyTable.class) + ; } } -- 2.39.5