From 964adbc73f14ac04385ac7037a1fffc4b819e732 Mon Sep 17 00:00:00 2001 From: Fabrice Bellingard Date: Tue, 17 May 2011 11:02:32 +0200 Subject: [PATCH] SONAR-2347 Close a review when its corresponding violation is fixed Update the DB Cleaner plugin to delete reviews and review comments related to deleted projects. --- .../plugins/dbcleaner/DbCleanerPlugin.java | 35 ++++++---- .../dbcleaner/purges/PurgeOrphanReviews.java | 66 +++++++++++++++++++ .../purges/PurgeOrphanReviewsTest.java | 46 +++++++++++++ .../purgeOrphanReviews-result.xml | 43 ++++++++++++ .../purgeOrphanReviews.xml | 39 +++++++++++ .../org/sonar/test/persistence/sonar-test.ddl | 1 + 6 files changed, 218 insertions(+), 12 deletions(-) create mode 100644 plugins/sonar-dbcleaner-plugin/src/main/java/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviews.java create mode 100644 plugins/sonar-dbcleaner-plugin/src/test/java/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest.java create mode 100644 plugins/sonar-dbcleaner-plugin/src/test/resources/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest/purgeOrphanReviews-result.xml create mode 100644 plugins/sonar-dbcleaner-plugin/src/test/resources/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest/purgeOrphanReviews.xml diff --git a/plugins/sonar-dbcleaner-plugin/src/main/java/org/sonar/plugins/dbcleaner/DbCleanerPlugin.java b/plugins/sonar-dbcleaner-plugin/src/main/java/org/sonar/plugins/dbcleaner/DbCleanerPlugin.java index f38a6c39b20..a02d8d0a308 100644 --- a/plugins/sonar-dbcleaner-plugin/src/main/java/org/sonar/plugins/dbcleaner/DbCleanerPlugin.java +++ b/plugins/sonar-dbcleaner-plugin/src/main/java/org/sonar/plugins/dbcleaner/DbCleanerPlugin.java @@ -19,30 +19,41 @@ */ package org.sonar.plugins.dbcleaner; +import java.util.Arrays; +import java.util.List; + import org.sonar.api.Properties; import org.sonar.api.Property; import org.sonar.api.SonarPlugin; import org.sonar.plugins.dbcleaner.api.DbCleanerConstants; import org.sonar.plugins.dbcleaner.period.DefaultPeriodCleaner; import org.sonar.plugins.dbcleaner.period.PeriodPurge; -import org.sonar.plugins.dbcleaner.purges.*; +import org.sonar.plugins.dbcleaner.purges.PurgeDeletedResources; +import org.sonar.plugins.dbcleaner.purges.PurgeDependencies; +import org.sonar.plugins.dbcleaner.purges.PurgeDeprecatedLast; +import org.sonar.plugins.dbcleaner.purges.PurgeDisabledResources; +import org.sonar.plugins.dbcleaner.purges.PurgeEntities; +import org.sonar.plugins.dbcleaner.purges.PurgeEventOrphans; +import org.sonar.plugins.dbcleaner.purges.PurgeOrphanResources; +import org.sonar.plugins.dbcleaner.purges.PurgeOrphanReviews; +import org.sonar.plugins.dbcleaner.purges.PurgePropertyOrphans; +import org.sonar.plugins.dbcleaner.purges.PurgeResourceRoles; +import org.sonar.plugins.dbcleaner.purges.PurgeRuleMeasures; +import org.sonar.plugins.dbcleaner.purges.PurgeUnprocessed; import org.sonar.plugins.dbcleaner.runner.PurgeRunner; -import java.util.Arrays; -import java.util.List; - @Properties({ - @Property(key = DbCleanerConstants.MONTHS_BEFORE_KEEPING_ONLY_ONE_SNAPSHOT_BY_WEEK, - defaultValue = DbCleanerConstants.ONE_MONTH, name = "Number of months before starting to keep only one snapshot by week", + @Property(key = DbCleanerConstants.MONTHS_BEFORE_KEEPING_ONLY_ONE_SNAPSHOT_BY_WEEK, defaultValue = DbCleanerConstants.ONE_MONTH, + name = "Number of months before starting to keep only one snapshot by week", description = "After this number of months, if there are several snapshots during the same week, " + "the DbCleaner keeps the first one and fully delete the other ones.", global = true, project = true), - @Property(key = DbCleanerConstants.MONTHS_BEFORE_KEEPING_ONLY_ONE_SNAPSHOT_BY_MONTH, - defaultValue = DbCleanerConstants.ONE_YEAR, name = "Number of months before starting to keep only one snapshot by month", + @Property(key = DbCleanerConstants.MONTHS_BEFORE_KEEPING_ONLY_ONE_SNAPSHOT_BY_MONTH, defaultValue = DbCleanerConstants.ONE_YEAR, + name = "Number of months before starting to keep only one snapshot by month", description = "After this number of months, if there are several snapshots during the same month, " + "the DbCleaner keeps the first one and fully delete the other ones.", global = true, project = true), @Property(key = DbCleanerConstants.MONTHS_BEFORE_DELETING_ALL_SNAPSHOTS, defaultValue = DbCleanerConstants.FIVE_YEARS, name = "Number of months before starting to delete all remaining snapshots", - description = "After this number of months, all snapshots are fully deleted.", global = true, project = true)}) + description = "After this number of months, all snapshots are fully deleted.", global = true, project = true) }) public final class DbCleanerPlugin extends SonarPlugin { public List getExtensions() { @@ -51,9 +62,9 @@ public final class DbCleanerPlugin extends SonarPlugin { DefaultPeriodCleaner.class, // purges - PurgeOrphanResources.class, PurgeEntities.class, PurgeRuleMeasures.class, PurgeUnprocessed.class, - PurgeDeletedResources.class, PurgeDeprecatedLast.class, PurgeDisabledResources.class, - PurgeResourceRoles.class, PurgeEventOrphans.class, PurgePropertyOrphans.class, PeriodPurge.class, PurgeDependencies.class, + PurgeOrphanResources.class, PurgeEntities.class, PurgeRuleMeasures.class, PurgeUnprocessed.class, PurgeDeletedResources.class, + PurgeDeprecatedLast.class, PurgeDisabledResources.class, PurgeResourceRoles.class, PurgeEventOrphans.class, + PurgePropertyOrphans.class, PeriodPurge.class, PurgeDependencies.class, PurgeOrphanReviews.class, // post-job PurgeRunner.class); diff --git a/plugins/sonar-dbcleaner-plugin/src/main/java/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviews.java b/plugins/sonar-dbcleaner-plugin/src/main/java/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviews.java new file mode 100644 index 00000000000..8e52416b767 --- /dev/null +++ b/plugins/sonar-dbcleaner-plugin/src/main/java/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviews.java @@ -0,0 +1,66 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2011 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.plugins.dbcleaner.purges; + +import javax.persistence.Query; + +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; +import org.sonar.api.database.DatabaseSession; +import org.sonar.plugins.dbcleaner.api.Purge; +import org.sonar.plugins.dbcleaner.api.PurgeContext; + +/** + * Purge Review that are attached to projects that have been deleted. + * + * @since 2.8 + */ +public final class PurgeOrphanReviews extends Purge { + + private static final Logger LOG = LoggerFactory.getLogger(PurgeOrphanReviews.class); + + public PurgeOrphanReviews(DatabaseSession session) { + super(session); + } + + public void purge(PurgeContext context) { + DatabaseSession session = getSession(); + + // delete reviews + Query query = session.createNativeQuery(getDeleteReviewsSqlRequest()); + int rowDeleted = query.executeUpdate(); + LOG.debug("- {} reviews deleted.", rowDeleted); + + // and delete review comments + query = session.createNativeQuery(getDeleteReviewCommentsSqlRequest()); + rowDeleted = query.executeUpdate(); + LOG.debug("- {} review comments deleted.", rowDeleted); + + session.commit(); + } + + protected String getDeleteReviewsSqlRequest() { + return "DELETE FROM reviews WHERE project_id not in (SELECT id FROM projects WHERE scope = 'PRJ' and qualifier = 'TRK')"; + } + + protected String getDeleteReviewCommentsSqlRequest() { + return "DELETE FROM review_comments WHERE review_id not in (SELECT id FROM reviews)"; + } +} diff --git a/plugins/sonar-dbcleaner-plugin/src/test/java/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest.java b/plugins/sonar-dbcleaner-plugin/src/test/java/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest.java new file mode 100644 index 00000000000..de1632ac757 --- /dev/null +++ b/plugins/sonar-dbcleaner-plugin/src/test/java/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest.java @@ -0,0 +1,46 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2011 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.plugins.dbcleaner.purges; + +import static org.hamcrest.Matchers.is; +import static org.junit.Assert.assertThat; + +import java.sql.Statement; + +import org.junit.Test; +import org.sonar.test.persistence.DatabaseTestCase; + +public class PurgeOrphanReviewsTest extends DatabaseTestCase { + + @Test + public void shouldCloseReviewWithoutCorrespondingViolation() throws Exception { + setupData("purgeOrphanReviews"); + + Statement stmt = getConnection().createStatement(); + int count = stmt.executeUpdate(new PurgeOrphanReviews(null).getDeleteReviewsSqlRequest()); + assertThat(count, is(1)); + + count = stmt.executeUpdate(new PurgeOrphanReviews(null).getDeleteReviewCommentsSqlRequest()); + assertThat(count, is(1)); + + assertTables("purgeOrphanReviews", "reviews"); + } + +} diff --git a/plugins/sonar-dbcleaner-plugin/src/test/resources/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest/purgeOrphanReviews-result.xml b/plugins/sonar-dbcleaner-plugin/src/test/resources/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest/purgeOrphanReviews-result.xml new file mode 100644 index 00000000000..7680bdd39c0 --- /dev/null +++ b/plugins/sonar-dbcleaner-plugin/src/test/resources/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest/purgeOrphanReviews-result.xml @@ -0,0 +1,43 @@ + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/plugins/sonar-dbcleaner-plugin/src/test/resources/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest/purgeOrphanReviews.xml b/plugins/sonar-dbcleaner-plugin/src/test/resources/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest/purgeOrphanReviews.xml new file mode 100644 index 00000000000..01da1669b40 --- /dev/null +++ b/plugins/sonar-dbcleaner-plugin/src/test/resources/org/sonar/plugins/dbcleaner/purges/PurgeOrphanReviewsTest/purgeOrphanReviews.xml @@ -0,0 +1,39 @@ + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl b/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl index c5125179cbf..54f49a0953b 100644 --- a/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl +++ b/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl @@ -492,6 +492,7 @@ CREATE TABLE REVIEWS ( STATUS VARCHAR(10), SEVERITY VARCHAR(10), RULE_FAILURE_PERMANENT_ID INTEGER, + PROJECT_ID INTEGER, RESOURCE_ID INTEGER, RESOURCE_LINE INTEGER, primary key (id) -- 2.39.5