From 357067b1cb44bcb96e1f8c29d54a4f2da536d921 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Wed, 3 Oct 2018 17:44:25 +0200 Subject: [PATCH] SONAR-10642 fix ce table data of branch/pr not purged --- .../org/sonar/db/purge/PurgeCommands.java | 20 +- .../java/org/sonar/db/purge/PurgeMapper.java | 20 +- .../org/sonar/db/purge/PurgeMapper.xml | 102 ++++- .../java/org/sonar/db/purge/PurgeDaoTest.java | 404 ++++++++++-------- .../db/migration/step/DataChange.java | 5 + .../v74/CleanOrphanRowsInCeTables.java | 66 +++ .../db/migration/version/v74/DbVersion74.java | 1 + .../v74/CleanOrphanRowsInCeTablesTest.java | 193 +++++++++ .../version/v74/DbVersion74Test.java | 2 +- .../ce_tables.sql | 83 ++++ 10 files changed, 665 insertions(+), 231 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTables.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTablesTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTablesTest/ce_tables.sql 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 38191d4be35..0b716f46ce8 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 @@ -248,46 +248,46 @@ class PurgeCommands { void deleteCeActivity(String rootUuid) { profiler.start("deleteCeActivity (ce_scanner_context)"); - purgeMapper.deleteCeScannerContextOfCeActivityByProjectUuid(rootUuid); + purgeMapper.deleteCeScannerContextOfCeActivityByRootUuid(rootUuid); session.commit(); profiler.stop(); profiler.start("deleteCeActivity (ce_task_characteristics)"); - purgeMapper.deleteCeTaskCharacteristicsOfCeActivityByProjectUuid(rootUuid); + purgeMapper.deleteCeTaskCharacteristicsOfCeActivityByRootUuid(rootUuid); session.commit(); profiler.stop(); profiler.start("deleteCeActivity (ce_task_input)"); - purgeMapper.deleteCeTaskInputOfCeActivityByProjectUuid(rootUuid); + purgeMapper.deleteCeTaskInputOfCeActivityByRootUuid(rootUuid); session.commit(); profiler.stop(); profiler.start("deleteCeActivity (ce_task_message)"); - purgeMapper.deleteCeTaskMessageOfCeActivityByProjectUuid(rootUuid); + purgeMapper.deleteCeTaskMessageOfCeActivityByRootUuid(rootUuid); session.commit(); profiler.stop(); profiler.start("deleteCeActivity (ce_activity)"); - purgeMapper.deleteCeActivityByProjectUuid(rootUuid); + purgeMapper.deleteCeActivityByRootUuid(rootUuid); session.commit(); profiler.stop(); } void deleteCeQueue(String rootUuid) { profiler.start("deleteCeQueue (ce_scanner_context)"); - purgeMapper.deleteCeScannerContextOfCeQueueByProjectUuid(rootUuid); + purgeMapper.deleteCeScannerContextOfCeQueueByRootUuid(rootUuid); session.commit(); profiler.stop(); profiler.start("deleteCeQueue (ce_task_characteristics)"); - purgeMapper.deleteCeTaskCharacteristicsOfCeQueueByProjectUuid(rootUuid); + purgeMapper.deleteCeTaskCharacteristicsOfCeQueueByRootUuid(rootUuid); session.commit(); profiler.stop(); profiler.start("deleteCeQueue (ce_task_input)"); - purgeMapper.deleteCeTaskInputOfCeQueueByProjectUuid(rootUuid); + purgeMapper.deleteCeTaskInputOfCeQueueByRootUuid(rootUuid); session.commit(); profiler.stop(); profiler.start("deleteCeQueue (ce_task_message)"); - purgeMapper.deleteCeTaskMessageOfCeQueueByProjectUuid(rootUuid); + purgeMapper.deleteCeTaskMessageOfCeQueueByRootUuid(rootUuid); session.commit(); profiler.stop(); profiler.start("deleteCeQueue (ce_queue)"); - purgeMapper.deleteCeQueueByProjectUuid(rootUuid); + purgeMapper.deleteCeQueueByRootUuid(rootUuid); session.commit(); profiler.stop(); } 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 af898c1a076..8aa4a17354a 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 @@ -90,25 +90,25 @@ public interface PurgeMapper { void deleteFileSourcesByFileUuid(@Param("fileUuids") List fileUuids); - void deleteCeTaskCharacteristicsOfCeActivityByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeTaskCharacteristicsOfCeActivityByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeTaskInputOfCeActivityByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeTaskInputOfCeActivityByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeTaskMessageOfCeActivityByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeScannerContextOfCeActivityByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeScannerContextOfCeActivityByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeTaskMessageOfCeActivityByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeActivityByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeActivityByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeScannerContextOfCeQueueByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeScannerContextOfCeQueueByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeTaskCharacteristicsOfCeQueueByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeTaskCharacteristicsOfCeQueueByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeTaskInputOfCeQueueByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeTaskInputOfCeQueueByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeTaskMessageOfCeQueueByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeTaskMessageOfCeQueueByRootUuid(@Param("rootUuid") String rootUuid); - void deleteCeQueueByProjectUuid(@Param("projectUuid") String projectUuid); + void deleteCeQueueByRootUuid(@Param("rootUuid") String rootUuid); void deleteWebhooksByProjectUuid(@Param("projectUuid") String projectUuid); 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 8a63d01490f..74543f9b82e 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 @@ -312,60 +312,122 @@ - + delete from ce_scanner_context where - task_uuid in (select uuid from ce_activity where main_component_uuid=#{projectUuid,jdbcType=VARCHAR}) + task_uuid in ( + select + uuid + from ce_activity + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} + ) - + delete from ce_task_characteristics where - task_uuid in (select uuid from ce_activity where main_component_uuid=#{projectUuid,jdbcType=VARCHAR}) + task_uuid in ( + select + uuid + from ce_activity + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} + ) - + delete from ce_task_input where - task_uuid in (select uuid from ce_activity where main_component_uuid=#{projectUuid,jdbcType=VARCHAR}) + task_uuid in ( + select + uuid + from ce_activity + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} + ) - + delete from ce_task_message where - task_uuid in (select uuid from ce_activity where main_component_uuid=#{projectUuid,jdbcType=VARCHAR}) + task_uuid in ( + select + uuid + from ce_activity + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} + ) - - delete from ce_activity where main_component_uuid=#{projectUuid,jdbcType=VARCHAR} + + delete from ce_activity + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} - + delete from ce_scanner_context where - task_uuid in (select uuid from ce_queue where main_component_uuid=#{projectUuid,jdbcType=VARCHAR}) + task_uuid in ( + select + uuid + from ce_queue + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} + ) - + delete from ce_task_characteristics where - task_uuid in (select uuid from ce_queue where main_component_uuid=#{projectUuid,jdbcType=VARCHAR}) + task_uuid in ( + select + uuid + from ce_queue + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} + ) - + delete from ce_task_input where - task_uuid in (select uuid from ce_queue where main_component_uuid=#{projectUuid,jdbcType=VARCHAR}) + task_uuid in ( + select + uuid + from ce_queue + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} + ) - + delete from ce_task_message where - task_uuid in (select uuid from ce_queue where main_component_uuid=#{projectUuid,jdbcType=VARCHAR}) + task_uuid in ( + select + uuid + from ce_queue + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} + ) - - delete from ce_queue where main_component_uuid=#{projectUuid,jdbcType=VARCHAR} + + delete from ce_queue + where + component_uuid=#{rootUuid,jdbcType=VARCHAR} + or main_component_uuid=#{rootUuid,jdbcType=VARCHAR} diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeDaoTest.java index f62e6260a8f..59ddc5e0f48 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeDaoTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeDaoTest.java @@ -74,6 +74,7 @@ import static org.mockito.Mockito.mock; import static org.mockito.Mockito.verifyZeroInteractions; import static org.mockito.Mockito.when; import static org.sonar.db.ce.CeTaskTypes.REPORT; +import static org.sonar.db.component.ComponentTesting.newBranchDto; import static org.sonar.db.component.ComponentTesting.newDirectory; import static org.sonar.db.component.ComponentTesting.newFileDto; import static org.sonar.db.component.ComponentTesting.newModuleDto; @@ -138,7 +139,7 @@ public class PurgeDaoTest { underTest.purge(dbSession, newConfigurationWith30Days(system2, project.uuid()), PurgeListener.EMPTY, new PurgeProfiler()); dbSession.commit(); - assertThat(getUuidsInTableProjects()).containsOnly(project.uuid(), longBranch.uuid(), recentShortBranch.uuid()); + assertThat(uuidsIn("projects")).containsOnly(project.uuid(), longBranch.uuid(), recentShortBranch.uuid()); } @Test @@ -164,7 +165,7 @@ public class PurgeDaoTest { underTest.purge(dbSession, newConfigurationWith30Days(system2, project.uuid()), PurgeListener.EMPTY, new PurgeProfiler()); dbSession.commit(); - assertThat(getUuidsInTableProjects()).containsOnly(project.uuid(), longBranch.uuid(), recentPullRequest.uuid()); + assertThat(uuidsIn("projects")).containsOnly(project.uuid(), longBranch.uuid(), recentPullRequest.uuid()); } @Test @@ -297,50 +298,6 @@ public class PurgeDaoTest { .containsExactlyInAnyOrder(webhookDeliveryNotDeleted.getUuid()); } - @Test - public void delete_branch_and_associated_data() { - ComponentDto project = db.components().insertMainBranch(); - ComponentDto branch = db.components().insertProjectBranch(project); - - CeQueueDto projectCeQueue = insertCeQueue(project); - CeActivityDto projectCeActivity = insertCeActivity(project); - CeQueueDto branchCeQueue = insertCeQueue(branch); - CeActivityDto branchCeActivity = insertCeActivity(branch); - Stream.of(projectCeQueue.getUuid(), projectCeActivity.getUuid(), branchCeQueue.getUuid(), branchCeActivity.getUuid()) - .forEach(taskUuid -> { - insertCeScannerContext(taskUuid); - insertCeTaskCharacteristics(taskUuid, 2); - insertCeTaskMessages(taskUuid, 3); - insertCeTaskInput(taskUuid); - }); - - underTest.deleteBranch(dbSession, branch.uuid()); - dbSession.commit(); - - assertThat(uuidsOfTable("projects")).containsOnly(project.uuid()); - assertThat(uuidsOfTable("project_branches")).containsOnly(project.uuid()); - // deleteBranch is bugged and does not delete from ce_* tables (see SONAR-10642) - assertThat(uuidsOfTable("ce_queue")) - .containsOnly(projectCeQueue.getUuid(), branchCeQueue.getUuid()) - .hasSize(2); - assertThat(uuidsOfTable("ce_activity")) - .containsOnly(projectCeActivity.getUuid(), branchCeActivity.getUuid()) - .hasSize(2); - String[] allTaskUuids = {projectCeQueue.getUuid(), projectCeActivity.getUuid(), branchCeQueue.getUuid(), branchCeActivity.getUuid()}; - assertThat(taskUuidsOfTable("ce_scanner_context")) - .containsOnly(allTaskUuids) - .hasSize(4); - assertThat(taskUuidsOfTable("ce_task_characteristics")) - .containsOnly(allTaskUuids) - .hasSize(8); - assertThat(taskUuidsOfTable("ce_task_message")) - .containsOnly(allTaskUuids) - .hasSize(12); - assertThat(taskUuidsOfTable("ce_task_input")) - .containsOnly(allTaskUuids) - .hasSize(4); - } - private Stream taskUuidsOfTable(String tableName) { return db.select("select task_uuid as \"TASK_UUID\" from " + tableName) .stream() @@ -374,77 +331,130 @@ public class PurgeDaoTest { @Test public void delete_row_in_ce_task_input_referring_to_a_row_in_ce_activity_when_deleting_project() { - ComponentDto projectToBeDeleted = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - ComponentDto anotherLivingProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - dbClient.componentDao().insert(dbSession, projectToBeDeleted, anotherLivingProject); - - // Insert 2 rows in CE_ACTIVITY : one for the project that will be deleted, and one on another project - CeActivityDto toBeDeleted = insertCeActivity(projectToBeDeleted); - insertCeTaskInput(toBeDeleted.getUuid()); - CeActivityDto toNotDelete = insertCeActivity(anotherLivingProject); - insertCeTaskInput(toNotDelete.getUuid()); + ComponentDto project = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + ComponentDto branch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherBranch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + dbClient.componentDao().insert(dbSession, project, branch, anotherBranch, anotherProject); + + CeActivityDto projectTask = insertCeActivity(project); + insertCeTaskInput(projectTask.getUuid()); + CeActivityDto branchTask = insertCeActivity(branch); + insertCeTaskInput(branchTask.getUuid()); + CeActivityDto anotherBranchTask = insertCeActivity(anotherBranch); + insertCeTaskInput(anotherBranchTask.getUuid()); + CeActivityDto anotherProjectTask = insertCeActivity(anotherProject); + insertCeTaskInput(anotherProjectTask.getUuid()); insertCeTaskInput("non existing task"); dbSession.commit(); - underTest.deleteProject(dbSession, projectToBeDeleted.uuid()); + underTest.deleteProject(dbSession, branch.uuid()); dbSession.commit(); - assertThat(db.select("select uuid as \"UUID\" from ce_activity")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid()); - assertThat(db.select("select task_uuid as \"UUID\" from ce_task_input")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid(), "non existing task"); + assertThat(uuidsIn("ce_activity")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_input")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid(), "non existing task"); + + underTest.deleteProject(dbSession, project.uuid()); + dbSession.commit(); + + assertThat(uuidsIn("ce_activity")).containsOnly(anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_input")).containsOnly(anotherProjectTask.getUuid(), "non existing task"); } @Test public void delete_row_in_ce_scanner_context_referring_to_a_row_in_ce_activity_when_deleting_project() { - ComponentDto projectToBeDeleted = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - ComponentDto anotherLivingProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - dbClient.componentDao().insert(dbSession, projectToBeDeleted, anotherLivingProject); - - // Insert 2 rows in CE_ACTIVITY : one for the project that will be deleted, and one on another project - CeActivityDto toBeDeleted = insertCeActivity(projectToBeDeleted); - insertCeScannerContext(toBeDeleted.getUuid()); - CeActivityDto toNotDelete = insertCeActivity(anotherLivingProject); - insertCeScannerContext(toNotDelete.getUuid()); + ComponentDto project = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + ComponentDto branch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherBranch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + dbClient.componentDao().insert(dbSession, project, branch, anotherBranch, anotherProject); + + CeActivityDto projectTask = insertCeActivity(project); + insertCeScannerContext(projectTask.getUuid()); + CeActivityDto branchTask = insertCeActivity(branch); + insertCeScannerContext(branchTask.getUuid()); + CeActivityDto anotherBranchTask = insertCeActivity(anotherBranch); + insertCeScannerContext(anotherBranchTask.getUuid()); + CeActivityDto anotherProjectTask = insertCeActivity(anotherProject); + insertCeScannerContext(anotherProjectTask.getUuid()); insertCeScannerContext("non existing task"); dbSession.commit(); - underTest.deleteProject(dbSession, projectToBeDeleted.uuid()); + underTest.deleteProject(dbSession, branch.uuid()); + dbSession.commit(); + + assertThat(uuidsIn("ce_activity")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_scanner_context")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid(), "non existing task"); + + underTest.deleteProject(dbSession, project.uuid()); dbSession.commit(); - assertThat(db.select("select uuid as \"UUID\" from ce_activity")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid()); - assertThat(db.select("select task_uuid as \"UUID\" from ce_scanner_context")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid(), "non existing task"); + assertThat(uuidsIn("ce_activity")).containsOnly(anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_scanner_context")).containsOnly(anotherProjectTask.getUuid(), "non existing task"); } @Test public void delete_row_in_ce_task_characteristics_referring_to_a_row_in_ce_activity_when_deleting_project() { - ComponentDto projectToBeDeleted = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - ComponentDto anotherLivingProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - dbClient.componentDao().insert(dbSession, projectToBeDeleted, anotherLivingProject); - - // Insert 2 rows in CE_ACTIVITY : one for the project that will be deleted, and one on another project - CeActivityDto toBeDeleted = insertCeActivity(projectToBeDeleted); - insertCeTaskCharacteristics(toBeDeleted.getUuid(), 3); - CeActivityDto toNotDelete = insertCeActivity(anotherLivingProject); - insertCeTaskCharacteristics(toNotDelete.getUuid(), 2); + ComponentDto project = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + ComponentDto branch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherBranch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + dbClient.componentDao().insert(dbSession, project, branch, anotherBranch, anotherProject); + + CeActivityDto projectTask = insertCeActivity(project); + insertCeTaskCharacteristics(projectTask.getUuid(), 3); + CeActivityDto branchTask = insertCeActivity(branch); + insertCeTaskCharacteristics(branchTask.getUuid(), 2); + CeActivityDto anotherBranchTask = insertCeActivity(anotherBranch); + insertCeTaskCharacteristics(anotherBranchTask.getUuid(), 6); + CeActivityDto anotherProjectTask = insertCeActivity(anotherProject); + insertCeTaskCharacteristics(anotherProjectTask.getUuid(), 2); insertCeTaskCharacteristics("non existing task", 5); dbSession.commit(); - underTest.deleteProject(dbSession, projectToBeDeleted.uuid()); + underTest.deleteProject(dbSession, branch.uuid()); dbSession.commit(); - assertThat(db.select("select uuid as \"UUID\" from ce_activity")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid()); - assertThat(db.select("select task_uuid as \"UUID\" from ce_task_characteristics")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid(), "non existing task"); + assertThat(uuidsIn("ce_activity")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_characteristics")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid(), "non existing task"); + + underTest.deleteProject(dbSession, project.uuid()); + dbSession.commit(); + + assertThat(uuidsIn("ce_activity")).containsOnly(anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_characteristics")).containsOnly(anotherProjectTask.getUuid(), "non existing task"); + } + + @Test + public void delete_row_in_ce_task_message_referring_to_a_row_in_ce_activity_when_deleting_project() { + ComponentDto project = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + ComponentDto branch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherBranch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + dbClient.componentDao().insert(dbSession, project, branch, anotherBranch, anotherProject); + + CeActivityDto projectTask = insertCeActivity(project); + insertCeTaskMessages(projectTask.getUuid(), 3); + CeActivityDto branchTask = insertCeActivity(branch); + insertCeTaskMessages(branchTask.getUuid(), 2); + CeActivityDto anotherBranchTask = insertCeActivity(anotherBranch); + insertCeTaskMessages(anotherBranchTask.getUuid(), 6); + CeActivityDto anotherProjectTask = insertCeActivity(anotherProject); + insertCeTaskMessages(anotherProjectTask.getUuid(), 2); + insertCeTaskMessages("non existing task", 5); + dbSession.commit(); + + underTest.deleteProject(dbSession, branch.uuid()); + dbSession.commit(); + + assertThat(uuidsIn("ce_activity")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_message")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid(), "non existing task"); + + underTest.deleteProject(dbSession, project.uuid()); + dbSession.commit(); + + assertThat(uuidsIn("ce_activity")).containsOnly(anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_message")).containsOnly(anotherProjectTask.getUuid(), "non existing task"); } @Test @@ -468,127 +478,133 @@ public class PurgeDaoTest { @Test public void delete_row_in_ce_task_input_referring_to_a_row_in_ce_queue_when_deleting_project() { - ComponentDto projectToBeDeleted = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - ComponentDto anotherLivingProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - dbClient.componentDao().insert(dbSession, projectToBeDeleted, anotherLivingProject); - - // Insert 2 rows in CE_ACTIVITY : one for the project that will be deleted, and one on another project - CeQueueDto toBeDeleted = insertCeQueue(projectToBeDeleted); - insertCeTaskInput(toBeDeleted.getUuid()); - CeQueueDto toNotDelete = insertCeQueue(anotherLivingProject); - insertCeTaskInput(toNotDelete.getUuid()); + ComponentDto project = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + ComponentDto branch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherBranch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + dbClient.componentDao().insert(dbSession, project, branch, anotherBranch, anotherProject); + + CeQueueDto projectTask = insertCeQueue(project); + insertCeTaskInput(projectTask.getUuid()); + CeQueueDto branchTask = insertCeQueue(branch); + insertCeTaskInput(branchTask.getUuid()); + CeQueueDto anotherBranchTask = insertCeQueue(anotherBranch); + insertCeTaskInput(anotherBranchTask.getUuid()); + CeQueueDto anotherProjectTask = insertCeQueue(anotherProject); + insertCeTaskInput(anotherProjectTask.getUuid()); insertCeTaskInput("non existing task"); dbSession.commit(); - underTest.deleteProject(dbSession, projectToBeDeleted.uuid()); + underTest.deleteProject(dbSession, branch.uuid()); + dbSession.commit(); + + assertThat(uuidsIn("ce_queue")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_input")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid(), "non existing task"); + + underTest.deleteProject(dbSession, project.uuid()); dbSession.commit(); - assertThat(db.select("select uuid as \"UUID\" from ce_queue")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid()); - assertThat(db.select("select task_uuid as \"UUID\" from ce_task_input")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid(), "non existing task"); + assertThat(uuidsIn("ce_queue")).containsOnly(anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_input")).containsOnly(anotherProjectTask.getUuid(), "non existing task"); } @Test public void delete_row_in_ce_scanner_context_referring_to_a_row_in_ce_queue_when_deleting_project() { - ComponentDto projectToBeDeleted = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - ComponentDto anotherLivingProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - dbClient.componentDao().insert(dbSession, projectToBeDeleted, anotherLivingProject); - - // Insert 2 rows in CE_ACTIVITY : one for the project that will be deleted, and one on another project - CeQueueDto toBeDeleted = insertCeQueue(projectToBeDeleted); - insertCeScannerContext(toBeDeleted.getUuid()); - CeQueueDto toNotDelete = insertCeQueue(anotherLivingProject); - insertCeScannerContext(toNotDelete.getUuid()); + ComponentDto project = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + ComponentDto branch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherBranch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + dbClient.componentDao().insert(dbSession, project, branch, anotherBranch, anotherProject); + + CeQueueDto projectTask = insertCeQueue(project); + insertCeScannerContext(projectTask.getUuid()); + CeQueueDto branchTask = insertCeQueue(branch); + insertCeScannerContext(branchTask.getUuid()); + CeQueueDto anotherBranchTask = insertCeQueue(anotherBranch); + insertCeScannerContext(anotherBranchTask.getUuid()); + CeQueueDto anotherProjectTask = insertCeQueue(anotherProject); + insertCeScannerContext(anotherProjectTask.getUuid()); insertCeScannerContext("non existing task"); dbSession.commit(); - underTest.deleteProject(dbSession, projectToBeDeleted.uuid()); + underTest.deleteProject(dbSession, branch.uuid()); dbSession.commit(); - assertThat(db.select("select uuid as \"UUID\" from ce_queue")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid()); - assertThat(db.select("select task_uuid as \"UUID\" from ce_scanner_context")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid(), "non existing task"); + assertThat(uuidsIn("ce_queue")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_scanner_context")) + .containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid(), "non existing task"); + + underTest.deleteProject(dbSession, project.uuid()); + dbSession.commit(); + + assertThat(uuidsIn("ce_queue")).containsOnly(anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_scanner_context")).containsOnly(anotherProjectTask.getUuid(), "non existing task"); } @Test public void delete_row_in_ce_task_characteristics_referring_to_a_row_in_ce_queue_when_deleting_project() { - ComponentDto projectToBeDeleted = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - ComponentDto anotherLivingProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - dbClient.componentDao().insert(dbSession, projectToBeDeleted, anotherLivingProject); - - // Insert 2 rows in CE_ACTIVITY : one for the project that will be deleted, and one on another project - CeQueueDto toBeDeleted = insertCeQueue(projectToBeDeleted); - insertCeTaskCharacteristics(toBeDeleted.getUuid(), 3); - CeQueueDto toNotDelete = insertCeQueue(anotherLivingProject); - insertCeTaskCharacteristics(toNotDelete.getUuid(), 2); + ComponentDto project = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + ComponentDto branch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherBranch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + dbClient.componentDao().insert(dbSession, project, branch, anotherBranch, anotherProject); + + CeQueueDto projectTask = insertCeQueue(project); + insertCeTaskCharacteristics(projectTask.getUuid(), 3); + CeQueueDto branchTask = insertCeQueue(branch); + insertCeTaskCharacteristics(branchTask.getUuid(), 1); + CeQueueDto anotherBranchTask = insertCeQueue(anotherBranch); + insertCeTaskCharacteristics(anotherBranchTask.getUuid(), 5); + CeQueueDto anotherProjectTask = insertCeQueue(anotherProject); + insertCeTaskCharacteristics(anotherProjectTask.getUuid(), 2); insertCeTaskCharacteristics("non existing task", 5); dbSession.commit(); - underTest.deleteProject(dbSession, projectToBeDeleted.uuid()); + underTest.deleteProject(dbSession, branch.uuid()); + dbSession.commit(); + + assertThat(uuidsIn("ce_queue")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_characteristics")) + .containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid(), "non existing task"); + + underTest.deleteProject(dbSession, project.uuid()); dbSession.commit(); - assertThat(db.select("select uuid as \"UUID\" from ce_queue")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid()); - assertThat(db.select("select task_uuid as \"UUID\" from ce_task_characteristics")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid(), "non existing task"); + assertThat(uuidsIn("ce_queue")).containsOnly(anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_characteristics")).containsOnly(anotherProjectTask.getUuid(), "non existing task"); } @Test public void delete_row_in_ce_task_message_referring_to_a_row_in_ce_queue_when_deleting_project() { - ComponentDto projectToBeDeleted = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - ComponentDto anotherLivingProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); - dbClient.componentDao().insert(dbSession, projectToBeDeleted, anotherLivingProject); - - // Insert 2 rows in CE_ACTIVITY : one for the project that will be deleted, and one on another project - CeQueueDto toBeDeleted = insertCeQueue(projectToBeDeleted); - insertCeTaskMessages(toBeDeleted.getUuid(), 3); - CeQueueDto toNotDelete = insertCeQueue(anotherLivingProject); - insertCeTaskMessages(toNotDelete.getUuid(), 2); + ComponentDto project = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + ComponentDto branch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherBranch = ComponentTesting.newProjectBranch(project, newBranchDto(project)); + ComponentDto anotherProject = ComponentTesting.newPrivateProjectDto(db.getDefaultOrganization()); + dbClient.componentDao().insert(dbSession, project, branch, anotherBranch, anotherProject); + + CeQueueDto projectTask = insertCeQueue(project); + insertCeTaskMessages(projectTask.getUuid(), 3); + CeQueueDto branchTask = insertCeQueue(branch); + insertCeTaskMessages(branchTask.getUuid(), 1); + CeQueueDto anotherBranchTask = insertCeQueue(anotherBranch); + insertCeTaskMessages(anotherBranchTask.getUuid(), 5); + CeQueueDto anotherProjectTask = insertCeQueue(anotherProject); + insertCeTaskMessages(anotherProjectTask.getUuid(), 2); insertCeTaskMessages("non existing task", 5); dbSession.commit(); - underTest.deleteProject(dbSession, projectToBeDeleted.uuid()); + underTest.deleteProject(dbSession, branch.uuid()); dbSession.commit(); - assertThat(db.select("select uuid as \"UUID\" from ce_queue")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid()); - assertThat(db.select("select task_uuid as \"TASK_UUID\" from ce_task_message")) - .extracting(row -> (String) row.get("TASK_UUID")) - .containsOnly(toNotDelete.getUuid(), "non existing task"); - } + assertThat(uuidsIn("ce_queue")).containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_message")) + .containsOnly(projectTask.getUuid(), anotherBranchTask.getUuid(), anotherProjectTask.getUuid(), "non existing task"); - @Test - public void delete_row_in_ce_task_message_referring_to_a_row_in_ce_activity_when_deleting_project() { - ComponentDto projectToBeDeleted = ComponentTesting.newPublicProjectDto(db.getDefaultOrganization()); - ComponentDto anotherLivingProject = ComponentTesting.newPublicProjectDto(db.getDefaultOrganization()); - dbClient.componentDao().insert(dbSession, projectToBeDeleted, anotherLivingProject); - - // Insert 2 rows in CE_ACTIVITY : one for the project that will be deleted, and one on another project - CeActivityDto toBeDeleted = insertCeActivity(projectToBeDeleted); - insertCeTaskMessages(toBeDeleted.getUuid(), 3); - CeActivityDto toNotDelete = insertCeActivity(anotherLivingProject); - insertCeTaskMessages(toNotDelete.getUuid(), 2); - insertCeTaskMessages("non existing task", 5); - dbSession.commit(); - - underTest.deleteProject(dbSession, projectToBeDeleted.uuid()); + underTest.deleteProject(dbSession, project.uuid()); dbSession.commit(); - assertThat(db.select("select uuid as \"UUID\" from ce_activity")) - .extracting(row -> (String) row.get("UUID")) - .containsOnly(toNotDelete.getUuid()); - assertThat(db.select("select task_uuid as \"TASK_UUID\" from ce_task_message")) - .extracting(row -> (String) row.get("TASK_UUID")) - .containsOnly(toNotDelete.getUuid(), "non existing task"); + assertThat(uuidsIn("ce_queue")).containsOnly(anotherProjectTask.getUuid()); + assertThat(taskUuidsIn("ce_task_message")).containsOnly(anotherProjectTask.getUuid(), "non existing task"); } private ComponentDto insertProjectWithBranchAndRelatedData() { @@ -829,7 +845,7 @@ public class PurgeDaoTest { underTest.deleteNonRootComponentsInView(dbSession, components); - assertThat(getUuidsInTableProjects()) + assertThat(uuidsIn("projects")) .containsOnly(project.uuid()); } @@ -855,14 +871,10 @@ public class PurgeDaoTest { underTest.deleteNonRootComponentsInView(dbSession, components); - assertThat(getUuidsInTableProjects()) + assertThat(uuidsIn("projects")) .containsOnly(view.uuid(), projects[0].uuid(), projects[1].uuid(), projects[2].uuid()); } - private Stream getUuidsInTableProjects() { - return db.select("select uuid as \"UUID\" from projects").stream().map(row -> (String) row.get("UUID")); - } - @Test public void deleteNonRootComponents_deletes_only_specified_non_root_components_of_a_project_from_table_PROJECTS() { ComponentDto project = new Random().nextBoolean() ? db.components().insertPublicProject() : db.components().insertPrivateProject(); @@ -875,11 +887,11 @@ public class PurgeDaoTest { ComponentDto file3 = db.components().insertComponent(newFileDto(project)); underTest.deleteNonRootComponentsInView(dbSession, singletonList(file3)); - assertThat(getUuidsInTableProjects()) + assertThat(uuidsIn("projects")) .containsOnly(project.uuid(), module1.uuid(), module2.uuid(), dir1.uuid(), dir2.uuid(), file1.uuid(), file2.uuid()); underTest.deleteNonRootComponentsInView(dbSession, asList(module1, dir2, file1)); - assertThat(getUuidsInTableProjects()) + assertThat(uuidsIn("projects")) .containsOnly(project.uuid(), module2.uuid(), dir1.uuid(), file2.uuid()); } @@ -899,12 +911,12 @@ public class PurgeDaoTest { ComponentDto pc3 = db.components().insertComponent(newProjectCopy("c", projects[2], subview2)); underTest.deleteNonRootComponentsInView(dbSession, singletonList(pc3)); - assertThat(getUuidsInTableProjects()) + assertThat(uuidsIn("projects")) .containsOnly(view.uuid(), projects[0].uuid(), projects[1].uuid(), projects[2].uuid(), subview1.uuid(), subview2.uuid(), pc1.uuid(), pc2.uuid()); underTest.deleteNonRootComponentsInView(dbSession, asList(subview1, pc2)); - assertThat(getUuidsInTableProjects()) + assertThat(uuidsIn("projects")) .containsOnly(view.uuid(), projects[0].uuid(), projects[1].uuid(), projects[2].uuid(), subview2.uuid(), pc1.uuid()); } @@ -1079,6 +1091,18 @@ public class PurgeDaoTest { .orElse(null); } + private Stream uuidsIn(String tableName) { + return db.select("select uuid as \"UUID\" from " + tableName) + .stream() + .map(row -> (String) row.get("UUID")); + } + + private Stream taskUuidsIn(String tableName) { + return db.select("select task_uuid as \"UUID\" from " + tableName) + .stream() + .map(row -> (String) row.get("UUID")); + } + private static PurgeConfiguration newConfigurationWith30Days() { return new PurgeConfiguration(new IdUuidPair(THE_PROJECT_ID, THE_PROJECT_UUID), emptyList(), 30, Optional.of(30), System2.INSTANCE, Collections.emptyList()); } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DataChange.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DataChange.java index f4d3ef1ba22..0aead2d9587 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DataChange.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DataChange.java @@ -21,6 +21,7 @@ package org.sonar.server.platform.db.migration.step; import java.sql.Connection; import java.sql.SQLException; +import org.sonar.api.config.Configuration; import org.sonar.db.Database; import org.sonar.db.dialect.Dialect; @@ -62,6 +63,10 @@ public abstract class DataChange implements MigrationStep { return res; } + protected static boolean isSonarCloud(Configuration configuration) { + return configuration.getBoolean("sonar.sonarcloud.enabled").orElse(false); + } + public static class Context { private final Database db; private final Connection readConnection; diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTables.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTables.java new file mode 100644 index 00000000000..161bcde30d7 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTables.java @@ -0,0 +1,66 @@ +/* + * SonarQube + * Copyright (C) 2009-2018 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.server.platform.db.migration.version.v74; + +import java.sql.SQLException; +import org.sonar.api.config.Configuration; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.SupportsBlueGreen; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +@SupportsBlueGreen +public class CleanOrphanRowsInCeTables extends DataChange { + private final Configuration configuration; + + public CleanOrphanRowsInCeTables(Database db, Configuration configuration1) { + super(db); + this.configuration = configuration1; + } + + @Override + protected void execute(Context context) throws SQLException { + if (isSonarCloud(configuration)) { + return; + } + + // clean orphans on ce_activity + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select" + + " cea.uuid" + + " from ce_activity cea" + + " where" + + " cea.component_uuid is not null" + + " and cea.main_component_uuid is not null" + + " and cea.component_uuid = cea.main_component_uuid" + + " and exists (select 1 from ce_task_characteristics ctc where ctc.task_uuid = cea.uuid)"); + massUpdate.update("delete from ce_task_input where task_uuid = ?"); + massUpdate.update("delete from ce_scanner_context where task_uuid = ?"); + massUpdate.update("delete from ce_task_characteristics where task_uuid = ?"); + massUpdate.update("delete from ce_activity where uuid = ?"); + massUpdate.rowPluralName("orphans of deleted branch/pr"); + massUpdate.execute((row, update, updateIndex) -> { + String taskUuid = row.getString(1); + update.setString(1, taskUuid); + return true; + }); + } + +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74.java index 5fea0de10c9..45186436a14 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74.java @@ -43,6 +43,7 @@ public class DbVersion74 implements DbVersion { .add(2320, "Finalize CE_ACTIVITY.MAIN_LAST_KEY 3/3", FinalizeMainLastKeyColumnsToCeActivity.class) .add(2321, "Increase organization key and name length", IncreaseOrganizationsKeeAndNameLength.class) .add(2322, "Create table CE_TASK_MESSAGE", CreateCeTaskMessage.class) + .add(2323, "Clean orphans from deleted branches and PRs in CE_* tables", CleanOrphanRowsInCeTables.class) ; } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTablesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTablesTest.java new file mode 100644 index 00000000000..01487522f28 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTablesTest.java @@ -0,0 +1,193 @@ +/* + * SonarQube + * Copyright (C) 2009-2018 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.server.platform.db.migration.version.v74; + +import java.sql.SQLException; +import java.util.Random; +import java.util.stream.IntStream; +import java.util.stream.Stream; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.junit.rules.ExpectedException; +import org.sonar.api.config.internal.MapSettings; +import org.sonar.core.util.UuidFactoryFast; +import org.sonar.db.CoreDbTester; + +import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic; +import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric; +import static org.assertj.core.api.Assertions.assertThat; + +public class CleanOrphanRowsInCeTablesTest { + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CleanOrphanRowsInCeTablesTest.class, "ce_tables.sql"); + @Rule + public ExpectedException expectedException = ExpectedException.none(); + + private Random random = new Random(); + private MapSettings settings = new MapSettings(); + private CleanOrphanRowsInCeTables underTest = new CleanOrphanRowsInCeTables(db.database(), settings.asConfig()); + + @Test + public void execute_has_no_effect_on_empty_tables() throws SQLException { + underTest.execute(); + } + + @Test + public void execute_deletes_rows_of_ce_activity_and_child_tables_which_have_main_component_equals_component_and_at_least_one_characteristic() throws SQLException { + String projectUuid = randomAlphabetic(10); + String branchUuid = randomAlphanumeric(11); + String mainBranchTask = insertCeActivity(projectUuid, projectUuid, 0); + String existingBranchTask = insertCeActivity(projectUuid, branchUuid, 1 + new Random().nextInt(10)); + String nonExistingBranchTask = insertCeActivity(projectUuid, projectUuid, 1 + new Random().nextInt(10)); + String missingTask = "missing_task"; + Stream.of(mainBranchTask, existingBranchTask, nonExistingBranchTask, missingTask) + .forEach(taskUuid -> { + insertCeTaskInput(taskUuid); + insertCeScannerContext(taskUuid); + }); + insertCeTaskCharacteristics(missingTask); + + underTest.execute(); + + assertThat(uuidsOf("ce_activity")).containsOnly(mainBranchTask, existingBranchTask); + assertThat(taskUuidsOf("ce_task_input")).containsOnly(mainBranchTask, existingBranchTask, missingTask); + assertThat(taskUuidsOf("ce_scanner_context")).containsOnly(mainBranchTask, existingBranchTask, missingTask); + assertThat(taskUuidsOf("ce_task_characteristics")).containsOnly(existingBranchTask, missingTask); + } + + @Test + public void execute_has_no_effect_on_SonarCloud() throws SQLException { + String projectUuid = randomAlphabetic(10); + String branchUuid = randomAlphanumeric(11); + String mainBranchTask = insertCeActivity(projectUuid, projectUuid, 0); + String existingBranchTask = insertCeActivity(projectUuid, branchUuid, 1 + new Random().nextInt(10)); + String nonExistingBranchTask = insertCeActivity(projectUuid, projectUuid, 1 + new Random().nextInt(10)); + String missingTask = "missing_task"; + Stream.of(mainBranchTask, existingBranchTask, nonExistingBranchTask, missingTask) + .forEach(taskUuid -> { + insertCeTaskInput(taskUuid); + insertCeScannerContext(taskUuid); + }); + insertCeTaskCharacteristics(missingTask); + settings.setProperty("sonar.sonarcloud.enabled", true); + + underTest.execute(); + + assertThat(uuidsOf("ce_activity")).containsOnly(mainBranchTask, existingBranchTask, nonExistingBranchTask); + assertThat(taskUuidsOf("ce_task_input")).containsOnly(mainBranchTask, existingBranchTask, nonExistingBranchTask, missingTask); + assertThat(taskUuidsOf("ce_scanner_context")).containsOnly(mainBranchTask, existingBranchTask, nonExistingBranchTask, missingTask); + assertThat(taskUuidsOf("ce_task_characteristics")).containsOnly(existingBranchTask, nonExistingBranchTask, missingTask); + } + + @Test + public void execute_is_reentrant() throws SQLException { + String projectUuid = randomAlphabetic(10); + String branchUuid = randomAlphanumeric(11); + String mainBranchTask = insertCeActivity(projectUuid, projectUuid, 0); + String existingBranchTask = insertCeActivity(projectUuid, branchUuid, 1 + new Random().nextInt(10)); + String nonExistingBranchTask = insertCeActivity(projectUuid, projectUuid, 1 + new Random().nextInt(10)); + Stream.of(mainBranchTask, existingBranchTask, nonExistingBranchTask) + .forEach(taskUuid -> { + insertCeTaskInput(taskUuid); + insertCeScannerContext(taskUuid); + }); + underTest.execute(); + + underTest.execute(); + } + + private Stream taskUuidsOf(String tableName) { + return db.select("select distinct task_uuid as \"TASK_UUID\" from " + tableName) + .stream() + .map(r -> (String) r.get("TASK_UUID")); + } + + private Stream uuidsOf(String tableName) { + return db.select("select uuid as \"UUID\" from " + tableName) + .stream() + .map(r -> (String) r.get("UUID")); + } + + private String insertCeActivity(@Nullable String componentUuid, @Nullable String mainComponentUuid, int numberOfCharacteristics) { + String uuid = UuidFactoryFast.getInstance().create(); + db.executeInsert( + "ce_activity", + "UUID", uuid, + "COMPONENT_UUID", componentUuid, + "MAIN_COMPONENT_UUID", mainComponentUuid, + "TASK_TYPE", randomAlphanumeric(10), + "STATUS", randomAlphanumeric(10), + "IS_LAST", random.nextBoolean(), + "IS_LAST_KEY", randomAlphanumeric(15), + "EXECUTION_COUNT", random.nextInt(99), + "SUBMITTED_AT", random.nextInt(95654354), + "CREATED_AT", random.nextInt(95654354), + "UPDATED_AT", random.nextInt(95654354)); + + IntStream.range(0, numberOfCharacteristics) + .forEach(i -> insertCeTaskCharacteristic(uuid, i)); + + return uuid; + } + + private void insertCeTaskInput(String taskUuid) { + db.executeInsert( + "ce_task_input", + "TASK_UUID", taskUuid, + "INPUT_DATA", randomAlphanumeric(123).getBytes(), + "CREATED_AT", random.nextInt(95654354), + "UPDATED_AT", random.nextInt(95654354)); + } + + private void insertCeScannerContext(String taskUuid) { + db.executeInsert( + "ce_scanner_context", + "TASK_UUID", taskUuid, + "CONTEXT_DATA", randomAlphanumeric(123).getBytes(), + "CREATED_AT", random.nextInt(95654354), + "UPDATED_AT", random.nextInt(95654354)); + } + + private void insertCeTaskCharacteristics(String taskUuid) { + for (int i = 0; i < 1 + random.nextInt(3); i++) { + insertCeTaskCharacteristic(taskUuid, i); + } + } + + private void insertCeTaskCharacteristic(String taskUuid, int i) { + String uuid = UuidFactoryFast.getInstance().create(); + db.executeInsert( + "ce_task_characteristics", + "UUID", uuid, + "TASK_UUID", taskUuid, + "KEE", "kee_" + uuid + i, + "TEXT_VALUE", randomAlphanumeric(18)); + } + + @Test + public void migration_is_not_reentrant() throws SQLException { + // FIXME + underTest.execute(); + + underTest.execute(); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74Test.java index db9845202a5..b67fedaffea 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74Test.java @@ -35,6 +35,6 @@ public class DbVersion74Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 16); + verifyMigrationCount(underTest, 17); } } diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTablesTest/ce_tables.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTablesTest/ce_tables.sql new file mode 100644 index 00000000000..8856d15ca6b --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v74/CleanOrphanRowsInCeTablesTest/ce_tables.sql @@ -0,0 +1,83 @@ +CREATE TABLE "CE_QUEUE" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(40) NOT NULL, + "TASK_TYPE" VARCHAR(15) NOT NULL, + "COMPONENT_UUID" VARCHAR(40) NULL, + "MAIN_COMPONENT_UUID" VARCHAR(40) NULL, + "STATUS" VARCHAR(15) NOT NULL, + "SUBMITTER_UUID" VARCHAR(255) NULL, + "WORKER_UUID" VARCHAR(40) NULL, + "EXECUTION_COUNT" INTEGER NOT NULL, + "STARTED_AT" BIGINT NULL, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +CREATE UNIQUE INDEX "CE_QUEUE_UUID" ON "CE_QUEUE" ("UUID"); +CREATE INDEX "CE_QUEUE_COMPONENT" ON "CE_QUEUE" ("COMPONENT_UUID"); +CREATE INDEX "CE_QUEUE_MAIN_COMPONENT" ON "CE_QUEUE" ("MAIN_COMPONENT_UUID"); +CREATE INDEX "CE_QUEUE_STATUS" ON "CE_QUEUE" ("STATUS"); + + +CREATE TABLE "CE_ACTIVITY" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(40) NOT NULL, + "TASK_TYPE" VARCHAR(15) NOT NULL, + "COMPONENT_UUID" VARCHAR(40) NULL, + "MAIN_COMPONENT_UUID" VARCHAR(40) NULL, + "ANALYSIS_UUID" VARCHAR(50) NULL, + "STATUS" VARCHAR(15) NOT NULL, + "IS_LAST" BOOLEAN, + "IS_LAST_KEY" VARCHAR(55), + "MAIN_IS_LAST" BOOLEAN, + "MAIN_IS_LAST_KEY" VARCHAR(55), + "SUBMITTER_UUID" VARCHAR(255) NULL, + "WORKER_UUID" VARCHAR(40) NULL, + "EXECUTION_COUNT" INTEGER NOT NULL, + "SUBMITTED_AT" BIGINT NOT NULL, + "STARTED_AT" BIGINT NULL, + "EXECUTED_AT" BIGINT NULL, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "EXECUTION_TIME_MS" BIGINT NULL, + "ERROR_MESSAGE" VARCHAR(1000), + "ERROR_STACKTRACE" CLOB, + "ERROR_TYPE" VARCHAR(20) +); +CREATE UNIQUE INDEX "CE_ACTIVITY_UUID" ON "CE_ACTIVITY" ("UUID"); +CREATE INDEX "CE_ACTIVITY_COMPONENT" ON "CE_ACTIVITY" ("COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_MAIN_COMPONENT" ON "CE_ACTIVITY" ("COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_ISLAST_KEY" ON "CE_ACTIVITY" ("IS_LAST_KEY"); +CREATE INDEX "CE_ACTIVITY_ISLAST" ON "CE_ACTIVITY" ("IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST_KEY" ON "CE_ACTIVITY" ("MAIN_IS_LAST_KEY"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST" ON "CE_ACTIVITY" ("MAIN_IS_LAST", "STATUS"); + + +CREATE TABLE "CE_TASK_CHARACTERISTICS" ( + "UUID" VARCHAR(40) NOT NULL, + "TASK_UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(50) NOT NULL, + "TEXT_VALUE" VARCHAR(4000), + + CONSTRAINT "PK_CE_TASK_CHARACTERISTICS" PRIMARY KEY ("UUID") +); +CREATE INDEX "CE_TASK_CHARACTERISTICS_TASK_UUID" ON "CE_TASK_CHARACTERISTICS" ("TASK_UUID"); + + +CREATE TABLE "CE_TASK_INPUT" ( + "TASK_UUID" VARCHAR(40) NOT NULL, + "INPUT_DATA" BLOB, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_CE_TASK_INPUT" PRIMARY KEY ("TASK_UUID") +); + + +CREATE TABLE "CE_SCANNER_CONTEXT" ( + "TASK_UUID" VARCHAR(40) NOT NULL, + "CONTEXT_DATA" BLOB NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_CE_SCANNER_CONTEXT" PRIMARY KEY ("TASK_UUID") +); -- 2.39.5