SQRP-292 Add sca_issues_releases

SQRP-293 add sca_issues_releases.severity column

SQRP-292 Update sca_issues_releases Dao to support persisting

SQRP-300 Add sca_issues_releases.severity_sort_key

6 files changed, 195 insertions, 0 deletions

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 49397cbcd1d..552520ed4ee 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
@@ -90,6 +90,7 @@ import org.sonar.db.rule.RuleDao;
 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.ScaReleasesDao;
 import org.sonar.db.sca.ScaVulnerabilityIssuesDao;
 import org.sonar.db.scannercache.ScannerAnalysisCacheDao;
@@ -190,6 +191,7 @@ public class DaoModule extends Module {
     SamlMessageIdDao.class,
     ScaDependenciesDao.class,
     ScaIssuesDao.class,
+    ScaIssuesReleasesDao.class,
     ScaReleasesDao.class,
     ScaVulnerabilityIssuesDao.class,
     ScannerAnalysisCacheDao.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 e3ea8c2aa7e..120a04adc3c 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
@@ -90,6 +90,7 @@ import org.sonar.db.rule.RuleDao;
 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.ScaReleasesDao;
 import org.sonar.db.sca.ScaVulnerabilityIssuesDao;
 import org.sonar.db.scannercache.ScannerAnalysisCacheDao;
@@ -207,6 +208,7 @@ public class DbClient {
   private final ScaReleasesDao scaReleasesDao;
   private final ScaDependenciesDao scaDependenciesDao;
   private final ScaIssuesDao scaIssuesDao;
+  private final ScaIssuesReleasesDao scaIssuesReleasesDao;
   private final ScaVulnerabilityIssuesDao scaVulnerabilityIssuesDao;
 
   public DbClient(Database database, MyBatis myBatis, DBSessions dbSessions, Dao... daos) {
@@ -307,6 +309,7 @@ public class DbClient {
     scaReleasesDao = getDao(map, ScaReleasesDao.class);
     scaDependenciesDao = getDao(map, ScaDependenciesDao.class);
     scaIssuesDao = getDao(map, ScaIssuesDao.class);
+    scaIssuesReleasesDao = getDao(map, ScaIssuesReleasesDao.class);
     scaVulnerabilityIssuesDao = getDao(map, ScaVulnerabilityIssuesDao.class);
   }
 
@@ -684,6 +687,10 @@ public class DbClient {
     return scaIssuesDao;
   }
 
+  public ScaIssuesReleasesDao scaIssuesReleasesDao() {
+    return scaIssuesReleasesDao;
+  }
+
   public ScaVulnerabilityIssuesDao scaVulnerabilityIssuesDao() {
     return scaVulnerabilityIssuesDao;
   }
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 4c668866151..2de44b8eb92 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.ScaIssuesReleasesMapper;
 import org.sonar.db.sca.ScaReleasesMapper;
 import org.sonar.db.sca.ScaVulnerabilityIssuesMapper;
 import org.sonar.db.scannercache.ScannerAnalysisCacheMapper;
@@ -347,6 +348,7 @@ public class MyBatis {
       SamlMessageIdMapper.class,
       ScaDependenciesMapper.class,
       ScaIssuesMapper.class,
+      ScaIssuesReleasesMapper.class,
       ScaReleasesMapper.class,
       ScaVulnerabilityIssuesMapper.class,
       ScannerAnalysisCacheMapper.class,
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDto.java
new file mode 100644
index 00000000000..98b1616a690
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDto.java
@@ -0,0 +1,105 @@
+/*
+ * 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;
+
+/**
+ * Represents a many-to-many join between Software Composition Analysis (SCA) issue and a SCA release.
+ *
+ * @param uuid             primary key
+ * @param scaIssueUuid               the UUID of the SCA issue
+ * @param scaReleaseUuid             the UUID of the SCA release
+ * @param severity                   the severity of the issue
+ * @param createdAt                  timestamp of creation
+ * @param updatedAt                  timestamp of most recent update
+ */
+public record ScaIssueReleaseDto(
+  String uuid,
+  String scaIssueUuid,
+  String scaReleaseUuid,
+  ScaSeverity severity,
+  long createdAt,
+  long updatedAt) {
+
+  /**
+   * This constructor makes it a little harder to get the issue and release uuids backward,
+   * if you have the DTOs around to use it.
+   */
+  public ScaIssueReleaseDto(String uuid, ScaIssueDto scaIssueDto, ScaReleaseDto scaReleaseDto, ScaSeverity severity, long createdAt, long updatedAt) {
+    this(uuid, scaIssueDto.uuid(), scaReleaseDto.uuid(), severity, createdAt, updatedAt);
+  }
+
+  public int severitySortKey() {
+    return severity.databaseSortKey();
+  }
+
+  public Builder toBuilder() {
+    return new Builder()
+      .setUuid(this.uuid)
+      .setScaIssueUuid(this.scaIssueUuid)
+      .setScaReleaseUuid(this.scaReleaseUuid)
+      .setSeverity(this.severity)
+      .setCreatedAt(this.createdAt)
+      .setUpdatedAt(this.updatedAt);
+  }
+
+  public static class Builder {
+    private String uuid;
+    private String scaIssueUuid;
+    private String scaReleaseUuid;
+    private ScaSeverity severity;
+    private long createdAt;
+    private long updatedAt;
+
+    public Builder setUuid(String uuid) {
+      this.uuid = uuid;
+      return this;
+    }
+
+    public Builder setScaIssueUuid(String scaIssueUuid) {
+      this.scaIssueUuid = scaIssueUuid;
+      return this;
+    }
+
+    public Builder setScaReleaseUuid(String scaReleaseUuid) {
+      this.scaReleaseUuid = scaReleaseUuid;
+      return this;
+    }
+
+    public Builder setSeverity(ScaSeverity severity) {
+      this.severity = severity;
+      return this;
+    }
+
+    public Builder setCreatedAt(long createdAt) {
+      this.createdAt = createdAt;
+      return this;
+    }
+
+    public Builder setUpdatedAt(long updatedAt) {
+      this.updatedAt = updatedAt;
+      return this;
+    }
+
+    public ScaIssueReleaseDto build() {
+      return new ScaIssueReleaseDto(
+        uuid, scaIssueUuid, scaReleaseUuid, severity, createdAt, updatedAt);
+    }
+  }
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDao.java
new file mode 100644
index 00000000000..710deb12d2c
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDao.java
@@ -0,0 +1,47 @@
+/*
+ * 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;
+
+public class ScaIssuesReleasesDao implements Dao {
+
+  private static ScaIssuesReleasesMapper mapper(DbSession session) {
+    return session.getMapper(ScaIssuesReleasesMapper.class);
+  }
+
+  public void insert(DbSession session, ScaIssueReleaseDto scaIssueReleaseDto) {
+    mapper(session).insert(scaIssueReleaseDto);
+  }
+
+  public void update(DbSession session, ScaIssueReleaseDto scaIssueReleaseDto) {
+    mapper(session).update(scaIssueReleaseDto);
+  }
+
+  public void deleteByUuid(DbSession session, String uuid) {
+    mapper(session).deleteByUuid(uuid);
+  }
+
+  public List<ScaIssueReleaseDto> selectByBranchUuid(DbSession dbSession, String branchUuid) {
+    return mapper(dbSession).selectByBranchUuid(branchUuid);
+  }
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesMapper.java
new file mode 100644
index 00000000000..58c40fd1435
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesMapper.java
@@ -0,0 +1,32 @@
+/*
+ * 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;
+
+public interface ScaIssuesReleasesMapper {
+  void insert(ScaIssueReleaseDto dto);
+
+  void update(ScaIssueReleaseDto dto);
+
+  void deleteByUuid(String uuid);
+
+  List<ScaIssueReleaseDto> selectByBranchUuid(String branchUuid);
+}