SCA-124 add a filter for "direct" on sca/issues-releases endpoint (#13184)

4 files changed, 52 insertions, 4 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
index 1ef369a11c2..087254cb125 100644
--- 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
@@ -379,6 +379,23 @@ class ScaIssuesReleasesDetailsDaoIT {
   }
 
   @Test
+  void withQueryFilteredByDirect_shouldReturnExpectedItems() {
+    QueryTestData testData = createQueryTestData();
+    var expectedDirect = testData.directIssues();
+    var expectedTransitive = testData.transitiveIssues();
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setDirect(true),
+      expectedDirect,
+      "Only direct issues should be returned");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setDirect(false),
+      expectedTransitive,
+      "Only the transitive issues should be returned");
+  }
+
+  @Test
   void withQueryMultipleFiltersNonDefaultSort_shouldReturnExpectedItems() {
     QueryTestData testData = createQueryTestData();
     var expectedPackageManagerMaven = testData.expectedIssuesWithPackageManager(PackageManager.MAVEN);
@@ -475,8 +492,23 @@ class ScaIssuesReleasesDetailsDaoIT {
       scaReleaseDto -> scaReleaseDto.toBuilder().setNewInPullRequest(true).build(),
       scaIssueReleaseDto -> scaIssueReleaseDto.toBuilder().setSeverity(ScaSeverity.INFO).build());
 
+    // issue 1 weirdly has no dependency, issues 2-3 are direct, issues 4-6 are transitive
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), issue2.scaReleaseUuid(), "2", true);
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), issue3.scaReleaseUuid(), "3", true);
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), issue4.scaReleaseUuid(), "4", false);
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), issue5.scaReleaseUuid(), "5", false);
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), issue6.scaReleaseUuid(), "6", false);
+
+    // make issue3 and issue4 BOTH direct and transitive
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), issue3.scaReleaseUuid(), "7", false);
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), issue4.scaReleaseUuid(), "8", true);
+
+    var directIssues = List.of(issue2, issue3, issue4).stream().sorted(identityComparator()).toList();
+    var transitiveIssues = List.of(issue3, issue4, issue5, issue6).stream().sorted(identityComparator()).toList();
+
     return new QueryTestData(projectData, componentDto,
-      List.of(issue1, issue2, issue3, issue4, issue5, issue6));
+      List.of(issue1, issue2, issue3, issue4, issue5, issue6),
+      directIssues, transitiveIssues);
   }
 
   @Test
@@ -514,7 +546,9 @@ class ScaIssuesReleasesDetailsDaoIT {
 
   private record QueryTestData(ProjectData projectData,
     ComponentDto componentDto,
-    List<ScaIssueReleaseDetailsDto> expectedIssues) {
+    List<ScaIssueReleaseDetailsDto> expectedIssues,
+    List<ScaIssueReleaseDetailsDto> directIssues,
+    List<ScaIssueReleaseDetailsDto> transitiveIssues) {
     private static Comparator<ScaIssueReleaseDetailsDto> cvssScoreComparator() {
       return Comparator.comparing(ScaIssueReleaseDetailsDto::cvssScore,
         // we treat null cvss as a score of 0.0
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQuery.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQuery.java
index ba990981b4b..453ea36b8f2 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQuery.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQuery.java
@@ -33,6 +33,7 @@ import static org.sonar.db.WildcardPosition.BEFORE_AND_AFTER;
 public record ScaIssuesReleasesDetailsQuery(
   String branchUuid,
   Sort sort,
+  @Nullable Boolean direct,
   @Nullable String vulnerabilityIdSubstring,
   @Nullable String packageNameSubstring,
   @Nullable Boolean newInPullRequest,
@@ -69,6 +70,7 @@ public record ScaIssuesReleasesDetailsQuery(
     return new Builder()
       .setBranchUuid(branchUuid)
       .setSort(sort)
+      .setDirect(direct)
       .setVulnerabilityIdSubstring(vulnerabilityIdSubstring)
       .setPackageNameSubstring(packageNameSubstring)
       .setNewInPullRequest(newInPullRequest)
@@ -112,6 +114,7 @@ public record ScaIssuesReleasesDetailsQuery(
   public static class Builder {
     private String branchUuid;
     private Sort sort;
+    private Boolean direct;
     private String vulnerabilityIdSubstring;
     private String packageNameSubstring;
     private Boolean newInPullRequest;
@@ -129,6 +132,11 @@ public record ScaIssuesReleasesDetailsQuery(
       return this;
     }
 
+    public Builder setDirect(@Nullable Boolean direct) {
+      this.direct = direct;
+      return this;
+    }
+
     public Builder setVulnerabilityIdSubstring(@Nullable String vulnerabilityIdSubstring) {
       this.vulnerabilityIdSubstring = vulnerabilityIdSubstring;
       return this;
@@ -160,7 +168,8 @@ public record ScaIssuesReleasesDetailsQuery(
     }
 
     public ScaIssuesReleasesDetailsQuery build() {
-      return new ScaIssuesReleasesDetailsQuery(branchUuid, sort, vulnerabilityIdSubstring, packageNameSubstring, newInPullRequest, types, severities, packageManagers);
+      return new ScaIssuesReleasesDetailsQuery(branchUuid, sort, direct, vulnerabilityIdSubstring, packageNameSubstring,
+        newInPullRequest, types, severities, packageManagers);
     }
   }
 }
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
index 50fc85e9db4..5845ce3990a 100644
--- 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
@@ -92,6 +92,11 @@
   <sql id="sqlSelectByQueryWhereClause">
     <where>
       sr.component_uuid = #{query.branchUuid,jdbcType=VARCHAR}
+      <if test="query.direct != null">
+        <!-- we only want each sca_releases row once, so this isn't a join. Note that each release
+             can be BOTH direct and !direct if it has multiple dependencies. -->
+        AND exists (select 1 from sca_dependencies sd where sd.sca_release_uuid = sr.uuid and sd.direct = #{query.direct,jdbcType=BOOLEAN})
+      </if>
       <if test="query.vulnerabilityIdSubstring != null">
           <!-- this screens out non-vulnerability-having issue types even if the search is for empty string -->
         AND si.vulnerability_id != '${@org.sonar.db.sca.ScaIssueDto@NULL_VALUE}'
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQueryTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQueryTest.java
index 6518a619220..49f35065925 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQueryTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQueryTest.java
@@ -30,7 +30,7 @@ class ScaIssuesReleasesDetailsQueryTest {
   @Test
   void test_toBuilder_build_shouldRoundTrip() {
     var query = new ScaIssuesReleasesDetailsQuery("branchUuid", ScaIssuesReleasesDetailsQuery.Sort.IDENTITY_ASC,
-      "vulnerabilityIdSubstring", "packageNameSubstring", true,
+      true, "vulnerabilityIdSubstring", "packageNameSubstring", true,
       List.of(ScaIssueType.VULNERABILITY), List.of(ScaSeverity.BLOCKER), List.of(PackageManager.NPM));
     AssertionsForClassTypes.assertThat(query.toBuilder().build()).isEqualTo(query);
   }