SQRP-252 Purge SCA tables when a branch is deleted

5 files changed, 75 insertions, 0 deletions

diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeDaoIT.java
index f93eaafd3aa..a01c48130d9 100644
--- a/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeDaoIT.java
+++ b/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeDaoIT.java
@@ -89,6 +89,10 @@ import org.sonar.db.property.PropertyDto;
 import org.sonar.db.report.ReportScheduleDto;
 import org.sonar.db.report.ReportSubscriptionDto;
 import org.sonar.db.rule.RuleDto;
+import org.sonar.db.sca.ScaDependenciesDbTester;
+import org.sonar.db.sca.ScaIssueReleaseDto;
+import org.sonar.db.sca.ScaReleasesDbTester;
+import org.sonar.db.sca.ScaSeverity;
 import org.sonar.db.source.FileSourceDto;
 import org.sonar.db.user.UserDismissedMessageDto;
 import org.sonar.db.user.UserDto;
@@ -1960,6 +1964,40 @@ oldCreationDate));
 
   }
 
+  @Test
+  void deleteBranch_purgesScaActivity() {
+    ProjectDto project = db.components().insertPublicProject().getProjectDto();
+    BranchDto branch1 = db.components().insertProjectBranch(project);
+    BranchDto branch2 = db.components().insertProjectBranch(project);
+
+    ScaReleasesDbTester scaReleasesDbTester = new ScaReleasesDbTester(db);
+    var release1 = scaReleasesDbTester.insertScaRelease(branch1.getUuid(), "1");
+    var release2 = scaReleasesDbTester.insertScaRelease(branch2.getUuid(), "2");
+
+    ScaDependenciesDbTester scaDependenciesDbTester = new ScaDependenciesDbTester(db);
+    scaDependenciesDbTester.insertScaDependency(branch1.getUuid(), release1.uuid(), "1",false);
+    scaDependenciesDbTester.insertScaDependency(branch2.getUuid(), release2.uuid(), "2", false);
+
+    ScaIssueReleaseDto issueRelease1 = new ScaIssueReleaseDto.Builder().setUuid("foo1").setScaIssueUuid("baz").setSeverity(ScaSeverity.LOW).setScaReleaseUuid(release1.uuid()).build();
+    ScaIssueReleaseDto issueRelease2 = new ScaIssueReleaseDto.Builder().setUuid("foo2").setScaIssueUuid("baz").setSeverity(ScaSeverity.LOW).setScaReleaseUuid(release2.uuid()).build();
+    dbClient.scaIssuesReleasesDao().insert(dbSession, issueRelease1);
+    dbClient.scaIssuesReleasesDao().insert(dbSession, issueRelease2);
+
+    assertThat(dbClient.scaReleasesDao().selectByBranchUuid(dbSession, branch1.getUuid())).isNotEmpty();
+    assertThat(dbClient.scaDependenciesDao().selectByBranchUuid(dbSession, branch1.getUuid())).isNotEmpty();
+    assertThat(dbClient.scaIssuesReleasesDao().selectByBranchUuid(dbSession, branch1.getUuid())).isNotEmpty();
+
+    underTest.deleteBranch(dbSession, branch1.getUuid());
+
+    assertThat(dbClient.scaReleasesDao().selectByBranchUuid(dbSession, branch1.getUuid())).isEmpty();
+    assertThat(dbClient.scaDependenciesDao().selectByBranchUuid(dbSession, branch1.getUuid())).isEmpty();
+    assertThat(dbClient.scaIssuesReleasesDao().selectByBranchUuid(dbSession, branch1.getUuid())).isEmpty();
+
+    assertThat(dbClient.scaReleasesDao().selectByBranchUuid(dbSession, branch2.getUuid())).isNotEmpty();
+    assertThat(dbClient.scaDependenciesDao().selectByBranchUuid(dbSession, branch2.getUuid())).isNotEmpty();
+    assertThat(dbClient.scaIssuesReleasesDao().selectByBranchUuid(dbSession, branch2.getUuid())).isNotEmpty();
+  }
+
   private AnticipatedTransitionDto getAnticipatedTransitionsDto(String uuid, String projectUuid, Date creationDate) {
     return new AnticipatedTransitionDto(uuid, projectUuid, "userUuid", "transition", null, null, null, null, "rule:key", "filepath",
       creationDate.getTime());
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java
index 9847cbc75a3..230d9aff010 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java
@@ -523,4 +523,23 @@ class PurgeCommands {
     session.commit();
     profiler.stop();
   }
+
+  public void deleteScaActivity(String componentUuid) {
+    profiler.start("deleteScaDependencies (sca_dependencies)");
+    purgeMapper.deleteScaDependenciesByComponentUuid(componentUuid);
+    session.commit();
+    profiler.stop();
+
+    profiler.start("deleteScaIssuesReleases (sca_issues_releases)");
+    purgeMapper.deleteScaIssuesReleasesByComponentUuid(componentUuid);
+    session.commit();
+    profiler.stop();
+
+    // sca_releases MUST be deleted last because dependencies and
+    // issues_releases only join to the component through sca_releases
+    profiler.start("deleteScaReleases (sca_releases)");
+    purgeMapper.deleteScaReleasesByComponentUuid(componentUuid);
+    session.commit();
+    profiler.stop();
+  }
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java
index 0d0e13b89ab..ce5e0cf5e70 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java
@@ -280,6 +280,7 @@ public class PurgeDao implements Dao {
     commands.deleteReportSchedules(branchUuid);
     commands.deleteReportSubscriptions(branchUuid);
     commands.deleteIssuesFixed(branchUuid);
+    commands.deleteScaActivity(branchUuid);
   }
 
   private static void deleteProject(String projectUuid, PurgeMapper mapper, PurgeCommands commands) {
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java
index c7a312c905a..5ca08a12d7a 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java
@@ -194,4 +194,10 @@ public interface PurgeMapper {
   void deleteAnticipatedTransitionsByProjectUuidAndCreationDate(@Param("projectUuid") String projectUuid, @Param("createdAtBefore") Long createdAtBefore);
 
   void deleteIssuesFixedByBranchUuid(@Param("branchUuid") String branchUuid);
+
+  void deleteScaDependenciesByComponentUuid(@Param("componentUuid") String componentUuid);
+
+  void deleteScaIssuesReleasesByComponentUuid(@Param("componentUuid") String componentUuid);
+
+  void deleteScaReleasesByComponentUuid(@Param("componentUuid") String componentUuid);
 }
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml
index 3700a8df647..4a64f3cdeab 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml
@@ -669,4 +669,15 @@
   <delete id="deleteIssuesFixedByBranchUuid">
     delete from issues_fixed where pull_request_uuid = #{branchUuid,jdbcType=VARCHAR}
   </delete>
+
+
+  <delete id="deleteScaDependenciesByComponentUuid">
+    delete from sca_dependencies where sca_release_uuid in (select uuid from sca_releases where component_uuid = #{componentUuid,jdbcType=VARCHAR})
+  </delete>
+  <delete id="deleteScaIssuesReleasesByComponentUuid">
+    delete from sca_issues_releases where sca_release_uuid in (select uuid from sca_releases where component_uuid = #{componentUuid,jdbcType=VARCHAR})
+  </delete>
+  <delete id="deleteScaReleasesByComponentUuid">
+    delete from sca_releases where component_uuid = #{componentUuid,jdbcType=VARCHAR}
+  </delete>
 </mapper>