SQRP-242 Add releases endpoints

6 files changed, 123 insertions, 2 deletions

diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaDependenciesDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaDependenciesDaoIT.java
index 1d41394dd54..c6506ef1045 100644
--- a/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaDependenciesDaoIT.java
+++ b/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaDependenciesDaoIT.java
@@ -87,6 +87,22 @@ class ScaDependenciesDaoIT {
   }
 
   @Test
+  void selectByReleaseUuids_shouldReturnScaDependencies() {
+    ComponentDto componentDto = prepareComponentDto("1");
+    ScaDependencyDto scaDependencyDto1a = db.getScaDependenciesDbTester().insertScaDependencyWithRelease(componentDto.uuid(), "1a", true, PackageManager.MAVEN, "foo.bar1");
+    // same release, different dependency
+    ScaDependencyDto scaDependencyDto1b = db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), scaDependencyDto1a.scaReleaseUuid(), "1b", false);
+    ScaDependencyDto scaDependencyDto2 = db.getScaDependenciesDbTester().insertScaDependencyWithRelease(componentDto.uuid(), "2", true, PackageManager.MAVEN, "foo.bar2");
+    ScaDependencyDto scaDependencyDto3 = db.getScaDependenciesDbTester().insertScaDependencyWithRelease(componentDto.uuid(), "3", true, PackageManager.MAVEN, "foo.bar3");
+
+    List<ScaDependencyDto> results = scaDependenciesDao.selectByReleaseUuids(db.getSession(), List.of(scaDependencyDto1a.scaReleaseUuid(), scaDependencyDto2.scaReleaseUuid()));
+
+    assertThat(results)
+      .containsExactlyInAnyOrder(scaDependencyDto1a, scaDependencyDto1b, scaDependencyDto2)
+      .doesNotContain(scaDependencyDto3);
+  }
+
+  @Test
   void selectByQuery_shouldReturnScaDependencies_whenQueryByBranchUuid() {
     ComponentDto componentDto = prepareComponentDto("1");
     ScaDependencyDto scaDependencyDto1 = db.getScaDependenciesDbTester().insertScaDependencyWithRelease(componentDto.uuid(), "1", true, PackageManager.MAVEN, "foo.bar");
diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaReleasesDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaReleasesDaoIT.java
index 0a879afa0c3..1acf0ef735e 100644
--- a/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaReleasesDaoIT.java
+++ b/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaReleasesDaoIT.java
@@ -26,6 +26,7 @@ import java.util.Map;
 import java.util.Set;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.extension.RegisterExtension;
+import org.slf4j.LoggerFactory;
 import org.sonar.api.utils.System2;
 import org.sonar.db.DbTester;
 import org.sonar.db.Pagination;
@@ -147,6 +148,21 @@ class ScaReleasesDaoIT {
   void selectByQuery_shouldPartiallyMatchPackageName_whenQueriedByText() {
     ComponentDto componentDto = prepareComponentDto("1");
     ScaReleaseDto scaReleaseDto1 = db.getScaReleasesDbTester().insertScaRelease(componentDto.uuid(), "1", PackageManager.MAVEN, "foo.bar");
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), scaReleaseDto1, "1", true);
+    db.getScaDependenciesDbTester().insertScaDependency(componentDto.uuid(), scaReleaseDto1, "2", false);
+    var log = LoggerFactory.getLogger("");
+    List<Map<String, Object>> temp = db.select(db.getSession(), "select * from sca_releases");
+    log.warn("sca_releases: {}", temp.stream().count());
+    for (Map<String, Object> map : temp) {
+      log.warn(map.toString());
+    }
+    temp = db.select(db.getSession(), "select * from sca_dependencies");
+    log.warn("sca_dependencies: {}", temp.stream().count());
+    for (Map<String, Object> map : temp) {
+      log.warn(map.toString());
+    }
+
+
     @SuppressWarnings("unused")
     ScaReleaseDto scaReleaseDto2 = db.getScaReleasesDbTester().insertScaRelease(componentDto.uuid(), "2", PackageManager.MAVEN, "bar.mee");
     ScaReleaseDto scaReleaseDto3 = db.getScaReleasesDbTester().insertScaRelease(componentDto.uuid(), "3", PackageManager.MAVEN, "foo.bar.me");
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependenciesDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependenciesDao.java
index a2942a438d5..166a8e0f2aa 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependenciesDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependenciesDao.java
@@ -19,6 +19,7 @@
  */
 package org.sonar.db.sca;
 
+import java.util.Collection;
 import java.util.List;
 import java.util.Optional;
 import org.sonar.db.Dao;
@@ -61,4 +62,8 @@ public class ScaDependenciesDao implements Dao {
   public void update(DbSession session, ScaDependencyDto scaDependencyDto) {
     mapper(session).update(scaDependencyDto);
   }
+
+  public List<ScaDependencyDto> selectByReleaseUuids(DbSession dbSession, Collection<String> releaseUuids) {
+    return mapper(dbSession).selectByReleaseUuids(releaseUuids);
+  }
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependenciesMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependenciesMapper.java
index 16cdc145978..9113c76a906 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependenciesMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependenciesMapper.java
@@ -19,6 +19,7 @@
  */
 package org.sonar.db.sca;
 
+import java.util.Collection;
 import java.util.List;
 import org.apache.ibatis.annotations.Param;
 import org.sonar.db.Pagination;
@@ -34,6 +35,8 @@ public interface ScaDependenciesMapper {
 
   List<ScaDependencyDto> selectByQuery(@Param("query") ScaDependenciesQuery query, @Param("pagination") Pagination pagination);
 
+  List<ScaDependencyDto> selectByReleaseUuids(Collection<String> releaseUuids);
+
   void update(ScaDependencyDto dto);
 
   int countByQuery(@Param("query") ScaDependenciesQuery query);
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaReleaseDependenciesDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaReleaseDependenciesDto.java
new file mode 100644
index 00000000000..2340352646f
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaReleaseDependenciesDto.java
@@ -0,0 +1,67 @@
+/*
+ * 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;
+
+/**
+ * This DTO represents the join of sca_releases and sca_dependencies, and is "read only"
+ * (it cannot be inserted, it would only be a query result).
+ *
+ * @param uuid              Primary key
+ * @param componentUuid     the component the release is associated with
+ * @param packageUrl        package URL following the PURL specification
+ * @param packageManager    package manager e.g. PYPI
+ * @param packageName       package name e.g. "urllib3"
+ * @param version           package version e.g. "1.25.6"
+ * @param licenseExpression an SPDX license expression (NOT a single license, can have parens/AND/OR)
+ * @param known             is this package and version known to Sonar (if not it be internal, could be malicious, could be from a weird repo)
+ * @param createdAt         timestamp it was created
+ * @param updatedAt         timestamp it was last updated
+ */
+public record ScaReleaseDependenciesDto(
+  String uuid,
+  String componentUuid,
+  String packageUrl,
+  PackageManager packageManager,
+  String packageName,
+  String version,
+  String licenseExpression,
+  boolean known,
+  long createdAt,
+  long updatedAt,
+  List<ScaDependencyDto> dependencies) {
+
+  public ScaReleaseDependenciesDto(ScaReleaseDto release, List<ScaDependencyDto> dependencies) {
+    this(
+      release.uuid(),
+      release.componentUuid(),
+      release.packageUrl(),
+      release.packageManager(),
+      release.packageName(),
+      release.version(),
+      release.licenseExpression(),
+      release.known(),
+      release.createdAt(),
+      release.updatedAt(),
+      dependencies
+    );
+  }
+}
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaDependenciesMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaDependenciesMapper.xml
index 4bb319ea6af..631c269f86e 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaDependenciesMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaDependenciesMapper.xml
@@ -45,6 +45,20 @@
     where sd.uuid = #{uuid,jdbcType=VARCHAR}
   </select>
 
+  <select id="selectByReleaseUuids" parameterType="string" resultType="org.sonar.db.sca.ScaDependencyDto">
+    select <include refid="scaDependenciesColumns"/>
+    from sca_dependencies sd
+    <if test="arg0.isEmpty()">
+      where 1=0
+    </if>
+    <if test="!arg0.isEmpty()">
+      where sd.sca_release_uuid in
+      <foreach collection="collection" item="sca_release_uuid" open="(" close=")" separator=",">
+        #{sca_release_uuid, jdbcType=VARCHAR}
+      </foreach>
+    </if>
+  </select>
+
   <select id="selectByBranchUuid" parameterType="string" resultType="org.sonar.db.sca.ScaDependencyDto">
     select <include refid="scaDependenciesColumns"/>
     from sca_dependencies sd
@@ -55,14 +69,14 @@
 
   <select id="selectByQuery" parameterType="map" resultType="org.sonar.db.sca.ScaDependencyDto">
     select <include refid="scaDependenciesColumns"/>
-    <include refid="sqlSelectByQuery" />
+    <include refid="sqlSelectByQuery"/>
     ORDER BY sd.uuid ASC
     <include refid="org.sonar.db.common.Common.pagination"/>
   </select>
 
   <select id="countByQuery" resultType="int">
     select count(sd.uuid)
-    <include refid="sqlSelectByQuery" />
+    <include refid="sqlSelectByQuery"/>
   </select>
 
   <sql id="sqlSelectByQuery">