SQRP-249 add ScaIssueReleaseDetailsDto and mapper

This is used to query SCA issues for a single analysis
with all the necessary tables joined to it to get the
full issue context.

SQRP-301 rename DbTester.getIssuesWithScaDbTester to getScaIssuesReleasesDetailsDbTester

SQRP-301 rename DbClient.issuesWithScaDao to scaIssuesReleasesDao

SQRP-301 add better doc comment to ScaIssueReleaseDetailsDto on meaning of identity fields

in sca_issues_releases dbtester, fix to work after separating new/insert

SQRP-296 port ScaIssueReleaseDetails to query cve_ids not title

10 files changed, 367 insertions, 0 deletions

diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDaoIT.java
new file mode 100644
index 00000000000..067424aa818
--- /dev/null
+++ b/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDaoIT.java
@@ -0,0 +1,90 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.sca;
+
+import java.math.BigDecimal;
+import java.util.List;
+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 org.sonar.db.Pagination;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+class ScaIssuesReleasesDetailsDaoIT {
+
+  @RegisterExtension
+  private final DbTester db = DbTester.create(System2.INSTANCE);
+
+  private final ScaIssuesReleasesDetailsDao scaIssuesReleasesDetailsDao = db.getDbClient().scaIssuesReleasesDetailsDao();
+
+  @Test
+  void selectByBranchUuid_shouldReturnIssues() {
+    var projectData = db.components().insertPrivateProject();
+    var componentDto = projectData.getMainBranchComponent();
+    var issue1 = db.getScaIssuesReleasesDetailsDbTester().insertIssue(ScaIssueType.VULNERABILITY, "1", componentDto.uuid(), projectData.projectUuid());
+    var issue2 = db.getScaIssuesReleasesDetailsDbTester().insertIssue(ScaIssueType.PROHIBITED_LICENSE, "2", componentDto.uuid(), projectData.projectUuid());
+
+    var foundPage = scaIssuesReleasesDetailsDao.selectByBranchUuid(db.getSession(), componentDto.branchUuid(), Pagination.forPage(1).andSize(1));
+
+    ScaIssueReleaseDetailsDto expected1 = new ScaIssueReleaseDetailsDto(
+      issue1.scaIssueReleaseUuid(),
+      issue1.severity(),
+      issue1.scaIssueUuid(),
+      issue1.scaReleaseUuid(),
+      ScaIssueType.VULNERABILITY,
+      "fakePackageUrl1",
+      "fakeVulnerabilityId1",
+      ScaIssueDto.NULL_VALUE,
+      ScaSeverity.INFO,
+      List.of("cwe1"),
+      new BigDecimal("7.1"));
+    ScaIssueReleaseDetailsDto expected2 = new ScaIssueReleaseDetailsDto(
+      issue2.scaIssueReleaseUuid(),
+      issue2.severity(),
+      issue2.scaIssueUuid(),
+      issue2.scaReleaseUuid(),
+      ScaIssueType.PROHIBITED_LICENSE,
+      ScaIssueDto.NULL_VALUE,
+      ScaIssueDto.NULL_VALUE,
+      "0BSD",
+      null,
+      null,
+      null);
+
+    assertThat(foundPage).hasSize(1).isSubsetOf(expected1, expected2);
+    var foundAllIssues = scaIssuesReleasesDetailsDao.selectByBranchUuid(db.getSession(), componentDto.branchUuid(), Pagination.forPage(1).andSize(10));
+    assertThat(foundAllIssues).hasSize(2).containsExactlyInAnyOrder(expected1, expected2);
+  }
+
+  @Test
+  void countByBranchUuid_shouldCountIssues() {
+    var componentDto = db.components().insertPrivateProject().getMainBranchComponent();
+    db.getScaIssuesReleasesDetailsDbTester().insertVulnerabilityIssue("1", componentDto.uuid());
+    db.getScaIssuesReleasesDetailsDbTester().insertVulnerabilityIssue("2", componentDto.uuid());
+    db.getScaIssuesReleasesDetailsDbTester().insertVulnerabilityIssue("3", componentDto.uuid());
+
+    var count1 = scaIssuesReleasesDetailsDao.countByBranchUuid(db.getSession(), componentDto.branchUuid());
+    assertThat(count1).isEqualTo(3);
+
+    assertThat(scaIssuesReleasesDetailsDao.countByBranchUuid(db.getSession(), "bogus-branch-uuid")).isZero();
+  }
+}
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 552520ed4ee..25669837f79 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
@@ -91,6 +91,7 @@ import org.sonar.db.rule.RuleRepositoryDao;
 import org.sonar.db.sca.ScaDependenciesDao;
 import org.sonar.db.sca.ScaIssuesDao;
 import org.sonar.db.sca.ScaIssuesReleasesDao;
+import org.sonar.db.sca.ScaIssuesReleasesDetailsDao;
 import org.sonar.db.sca.ScaReleasesDao;
 import org.sonar.db.sca.ScaVulnerabilityIssuesDao;
 import org.sonar.db.scannercache.ScannerAnalysisCacheDao;
@@ -154,6 +155,7 @@ public class DaoModule extends Module {
     IssueChangeDao.class,
     IssueDao.class,
     IssueFixedDao.class,
+    ScaIssuesReleasesDetailsDao.class,
     MeasureDao.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 120a04adc3c..9693ee8bb5e 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
@@ -91,6 +91,7 @@ import org.sonar.db.rule.RuleRepositoryDao;
 import org.sonar.db.sca.ScaDependenciesDao;
 import org.sonar.db.sca.ScaIssuesDao;
 import org.sonar.db.sca.ScaIssuesReleasesDao;
+import org.sonar.db.sca.ScaIssuesReleasesDetailsDao;
 import org.sonar.db.sca.ScaReleasesDao;
 import org.sonar.db.sca.ScaVulnerabilityIssuesDao;
 import org.sonar.db.scannercache.ScannerAnalysisCacheDao;
@@ -210,6 +211,7 @@ public class DbClient {
   private final ScaIssuesDao scaIssuesDao;
   private final ScaIssuesReleasesDao scaIssuesReleasesDao;
   private final ScaVulnerabilityIssuesDao scaVulnerabilityIssuesDao;
+  private final ScaIssuesReleasesDetailsDao scaIssuesReleasesDetailsDao;
 
   public DbClient(Database database, MyBatis myBatis, DBSessions dbSessions, Dao... daos) {
     this.database = database;
@@ -311,6 +313,7 @@ public class DbClient {
     scaIssuesDao = getDao(map, ScaIssuesDao.class);
     scaIssuesReleasesDao = getDao(map, ScaIssuesReleasesDao.class);
     scaVulnerabilityIssuesDao = getDao(map, ScaVulnerabilityIssuesDao.class);
+    scaIssuesReleasesDetailsDao = getDao(map, ScaIssuesReleasesDetailsDao.class);
   }
 
   public DbSession openSession(boolean batch) {
@@ -694,4 +697,8 @@ public class DbClient {
   public ScaVulnerabilityIssuesDao scaVulnerabilityIssuesDao() {
     return scaVulnerabilityIssuesDao;
   }
+
+  public ScaIssuesReleasesDetailsDao scaIssuesReleasesDetailsDao() {
+    return scaIssuesReleasesDetailsDao;
+  }
 }
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 2de44b8eb92..85ad8b70ebd 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
@@ -154,6 +154,7 @@ import org.sonar.db.rule.RuleRepositoryMapper;
 import org.sonar.db.sca.ScaDependenciesMapper;
 import org.sonar.db.sca.ScaDependencyDto;
 import org.sonar.db.sca.ScaIssuesMapper;
+import org.sonar.db.sca.ScaIssuesReleasesDetailsMapper;
 import org.sonar.db.sca.ScaIssuesReleasesMapper;
 import org.sonar.db.sca.ScaReleasesMapper;
 import org.sonar.db.sca.ScaVulnerabilityIssuesMapper;
@@ -310,6 +311,7 @@ public class MyBatis {
       IssueChangeMapper.class,
       IssueMapper.class,
       IssueFixedMapper.class,
+      ScaIssuesReleasesDetailsMapper.class,
       MeasureMapper.class,
       ProjectMeasureMapper.class,
       MetricMapper.class,
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDetailsDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDetailsDto.java
new file mode 100644
index 00000000000..d2dd1674c03
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDetailsDto.java
@@ -0,0 +1,54 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.sca;
+
+import java.math.BigDecimal;
+import java.util.List;
+import javax.annotation.Nullable;
+
+/**
+ * <p>A "read-only" DTO used to query the join of sca_issues_releases, sca_issues, and sca_*_issues.
+ * This is used to return all the details shown in a list of issues in the UX.
+ * This DTO and its mapper are an optimization, to do more work in SQL and
+ * avoid "joining in Java."
+ * </p>
+ * <p>
+ *   The packageUrl parameter in particular is tricky; it's the identity packageUrl from ScaIssueDto,
+ *   and can be set to {@link ScaIssueDto#NULL_VALUE} if the issue is not a vulnerability. What you
+ *   likely want in many cases instead would be the package URLs from the individual releases,
+ *   those are the affected release URLs.
+ * </p>
+ * <p>
+ *   Similarily, vulnerabiiltyId and spdxLicenseId can have {@link ScaIssueDto#NULL_VALUE} if they
+ *   are inapplicable to the issue type at hand.
+ * </p>
+ */
+public record ScaIssueReleaseDetailsDto(String scaIssueReleaseUuid,
+  ScaSeverity severity,
+  String scaIssueUuid,
+  String scaReleaseUuid,
+  ScaIssueType scaIssueType,
+  String packageUrl,
+  String vulnerabilityId,
+  String spdxLicenseId,
+  @Nullable ScaSeverity vulnerabilityBaseSeverity,
+  @Nullable List<String> cweIds,
+  @Nullable BigDecimal cvssScore) implements ScaIssueIdentity {
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDao.java
new file mode 100644
index 00000000000..77be2a81ea5
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDao.java
@@ -0,0 +1,46 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.sca;
+
+import java.util.List;
+import org.sonar.db.Dao;
+import org.sonar.db.DbSession;
+import org.sonar.db.Pagination;
+
+public class ScaIssuesReleasesDetailsDao implements Dao {
+
+  private static ScaIssuesReleasesDetailsMapper mapper(DbSession session) {
+    return session.getMapper(ScaIssuesReleasesDetailsMapper.class);
+  }
+
+  /**
+   * Retrieves all issues with a specific branch UUID, no other filtering is done by this method.
+   */
+  public List<ScaIssueReleaseDetailsDto> selectByBranchUuid(DbSession dbSession, String branchUuid, Pagination pagination) {
+    return mapper(dbSession).selectByBranchUuid(branchUuid, pagination);
+  }
+
+  /**
+   * Counts all issues with a specific branch UUID, no other filtering is done by this method.
+   */
+  public int countByBranchUuid(DbSession dbSession, String branchUuid) {
+    return mapper(dbSession).countByBranchUuid(branchUuid);
+  }
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.java
new file mode 100644
index 00000000000..02bea84eb9c
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.java
@@ -0,0 +1,30 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.sca;
+
+import java.util.List;
+import org.apache.ibatis.annotations.Param;
+import org.sonar.db.Pagination;
+
+public interface ScaIssuesReleasesDetailsMapper {
+  List<ScaIssueReleaseDetailsDto> selectByBranchUuid(@Param("branchUuid") String branchUuid, @Param("pagination") Pagination pagination);
+
+  int countByBranchUuid(String branchUuid);
+}
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.xml
new file mode 100644
index 00000000000..80218190ced
--- /dev/null
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.xml
@@ -0,0 +1,57 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">
+<mapper namespace="org.sonar.db.sca.ScaIssuesReleasesDetailsMapper">
+  <!-- we're using a resultMap instead of the usual resultType approach in order to provide
+       a typeHandler for the cwe_ids column -->
+  <resultMap id="scaIssueReleaseDetailsResultMap" type="org.sonar.db.sca.ScaIssueReleaseDetailsDto">
+    <constructor>
+      <idArg column="uuid" javaType="String"/>
+      <arg column="severity" javaType="org.sonar.db.sca.ScaSeverity" jdbcType="VARCHAR"/>
+      <arg column="sca_issue_uuid" javaType="String"/>
+      <arg column="sca_release_uuid" javaType="String"/>
+      <arg column="sca_issue_type" javaType="org.sonar.db.sca.ScaIssueType" jdbcType="VARCHAR"/>
+      <arg column="package_url" javaType="String"/>
+      <arg column="vulnerability_id" javaType="String"/>
+      <arg column="spdx_license_id" javaType="String"/>
+      <arg column="base_severity" javaType="org.sonar.db.sca.ScaSeverity" jdbcType="VARCHAR"/>
+      <arg column="cwe_ids" typeHandler="org.sonar.db.sca.ListOfStringsTypeHandler" jdbcType="VARCHAR"
+           javaType="java.util.List"/>
+      <arg column="cvss_score" javaType="java.math.BigDecimal"/>
+    </constructor>
+  </resultMap>
+
+  <sql id="issuesWithScaColumns">
+    sir.uuid,
+    sir.severity,
+    sir.sca_issue_uuid,
+    sir.sca_release_uuid,
+    si.sca_issue_type,
+    si.package_url,
+    si.vulnerability_id,
+    si.spdx_license_id,
+    svi.base_severity,
+    svi.cwe_ids,
+    svi.cvss_score
+  </sql>
+
+  <sql id="sqlSelectByBranchUuid">
+    from sca_issues_releases sir
+    inner join sca_issues si on sir.sca_issue_uuid = si.uuid
+    inner join sca_releases sr on sir.sca_release_uuid = sr.uuid
+    inner join components c on sr.component_uuid = c.uuid
+    left join sca_vulnerability_issues svi on sir.sca_issue_uuid = svi.uuid
+    where c.branch_uuid = #{branchUuid,jdbcType=VARCHAR}
+  </sql>
+
+  <select id="selectByBranchUuid" parameterType="map" resultMap="scaIssueReleaseDetailsResultMap">
+    select <include refid="issuesWithScaColumns"/>
+    <include refid="sqlSelectByBranchUuid"/>
+    ORDER BY sir.sca_issue_uuid ASC
+    <include refid="org.sonar.db.common.Common.pagination"/>
+  </select>
+
+  <select id="countByBranchUuid" parameterType="string" resultType="int">
+    select count(sir.uuid)
+    <include refid="sqlSelectByBranchUuid"/>
+  </select>
+</mapper>
diff --git a/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/DbTester.java b/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/DbTester.java
index c4d59854ab8..51049573958 100644
--- a/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/DbTester.java
+++ b/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/DbTester.java
@@ -58,6 +58,7 @@ import org.sonar.db.qualityprofile.QualityProfileDbTester;
 import org.sonar.db.rule.RuleDbTester;
 import org.sonar.db.sca.ScaDependenciesDbTester;
 import org.sonar.db.sca.ScaIssuesDbTester;
+import org.sonar.db.sca.ScaIssuesReleasesDetailsDbTester;
 import org.sonar.db.sca.ScaReleasesDbTester;
 import org.sonar.db.source.FileSourceTester;
 import org.sonar.db.user.UserDbTester;
@@ -101,6 +102,7 @@ public class DbTester extends AbstractDbTester<TestDbImpl> implements BeforeEach
   private final AnticipatedTransitionDbTester anticipatedTransitionDbTester;
   private final ScaDependenciesDbTester scaDependenciesDbTester;
   private final ScaIssuesDbTester scaIssuesDbTester;
+  private final ScaIssuesReleasesDetailsDbTester scaIssuesReleasesDetailsDbTester;
   private final ScaReleasesDbTester scaReleasesDbTester;
 
   private DbTester(UuidFactory uuidFactory, System2 system2, @Nullable String schemaPath, AuditPersister auditPersister, MyBatisConfExtension... confExtensions) {
@@ -136,6 +138,7 @@ public class DbTester extends AbstractDbTester<TestDbImpl> implements BeforeEach
     this.anticipatedTransitionDbTester = new AnticipatedTransitionDbTester(this);
     this.scaDependenciesDbTester = new ScaDependenciesDbTester(this);
     this.scaIssuesDbTester = new ScaIssuesDbTester(this);
+    this.scaIssuesReleasesDetailsDbTester = new ScaIssuesReleasesDetailsDbTester(this);
     this.scaReleasesDbTester = new ScaReleasesDbTester(this);
   }
 
@@ -292,6 +295,10 @@ public class DbTester extends AbstractDbTester<TestDbImpl> implements BeforeEach
     return scaIssuesDbTester;
   }
 
+  public ScaIssuesReleasesDetailsDbTester getScaIssuesReleasesDetailsDbTester() {
+    return scaIssuesReleasesDetailsDbTester;
+  }
+
   public ScaReleasesDbTester getScaReleasesDbTester() {
     return scaReleasesDbTester;
   }
diff --git a/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDbTester.java b/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDbTester.java
new file mode 100644
index 00000000000..56f527bc6be
--- /dev/null
+++ b/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDbTester.java
@@ -0,0 +1,72 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.sca;
+
+import java.util.Optional;
+import javax.annotation.Nullable;
+import org.sonar.db.DbClient;
+import org.sonar.db.DbTester;
+
+public class ScaIssuesReleasesDetailsDbTester {
+  private final DbTester db;
+  private final DbClient dbClient;
+
+  public ScaIssuesReleasesDetailsDbTester(DbTester db) {
+    this.db = db;
+    this.dbClient = db.getDbClient();
+  }
+
+  public ScaIssueReleaseDetailsDto fromDtos(@Nullable String projectUuid, ScaIssueReleaseDto scaIssueReleaseDto, ScaIssueDto scaIssueDto,
+    Optional<ScaVulnerabilityIssueDto> scaVulnerabilityIssueDto) {
+    // this should emulate what the mapper does when joining these tables
+    return new ScaIssueReleaseDetailsDto(scaIssueReleaseDto.uuid(), ScaSeverity.INFO,
+      scaIssueReleaseDto.scaIssueUuid(), scaIssueReleaseDto.scaReleaseUuid(), scaIssueDto.scaIssueType(),
+      scaIssueDto.packageUrl(), scaIssueDto.vulnerabilityId(), scaIssueDto.spdxLicenseId(),
+      scaVulnerabilityIssueDto.map(ScaVulnerabilityIssueDto::baseSeverity).orElse(null),
+      scaVulnerabilityIssueDto.map(ScaVulnerabilityIssueDto::cweIds).orElse(null),
+      scaVulnerabilityIssueDto.map(ScaVulnerabilityIssueDto::cvssScore).orElse(null));
+  }
+
+  private ScaIssueReleaseDetailsDto insertIssue(ScaIssueDto scaIssue, String suffix, String componentUuid, @Nullable String projectUuid) {
+    // insertScaRelease has suffix and componentUuid swapped vs. our own method...
+    var scaRelease = db.getScaReleasesDbTester().insertScaRelease(componentUuid, suffix);
+    var scaIssueRelease = new ScaIssueReleaseDto("sca-issue-release-uuid-" + suffix, scaIssue, scaRelease, ScaSeverity.INFO, 1L, 2L);
+    dbClient.scaIssuesReleasesDao().insert(db.getSession(), scaIssueRelease);
+    return fromDtos(projectUuid, scaIssueRelease, scaIssue, Optional.empty());
+  }
+
+  public ScaIssueReleaseDetailsDto insertVulnerabilityIssue(String suffix, String componentUuid) {
+    var scaIssue = db.getScaIssuesDbTester().insertVulnerabilityIssue(suffix).getKey();
+    return insertIssue(scaIssue, suffix, componentUuid, null);
+  }
+
+  public ScaIssueReleaseDetailsDto insertProhibitedLicenseIssue(String suffix, String componentUuid) {
+    var scaIssue = db.getScaIssuesDbTester().insertProhibitedLicenseIssue(suffix);
+    return insertIssue(scaIssue, suffix, componentUuid, null);
+  }
+
+  public ScaIssueReleaseDetailsDto insertIssue(ScaIssueType scaIssueType, String suffix, String componentUuid, @Nullable String projectUuid) {
+    var scaIssue = switch (scaIssueType) {
+      case VULNERABILITY -> db.getScaIssuesDbTester().insertVulnerabilityIssue(suffix).getKey();
+      case PROHIBITED_LICENSE -> db.getScaIssuesDbTester().insertProhibitedLicenseIssue(suffix);
+    };
+    return insertIssue(scaIssue, suffix, componentUuid, projectUuid);
+  }
+}