SONAR-7180 script to delete properties sonar.core.projectsdashboard.*

7 files changed, 225 insertions, 3 deletions

diff --git a/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java b/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java
index 3a7ac8cbc38..4593d40db8c 100644
--- a/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java
+++ b/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java
@@ -29,7 +29,7 @@ import org.sonar.db.MyBatis;
 
 public class DatabaseVersion {
 
-  public static final int LAST_VERSION = 1008;
+  public static final int LAST_VERSION = 1009;
 
   /**
    * The minimum supported version which can be upgraded. Lower
diff --git a/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java b/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java
index c384fcba2fa..d4f25e25adc 100644
--- a/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java
+++ b/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java
@@ -62,6 +62,7 @@ import org.sonar.db.version.v52.RemoveSnapshotLibraries;
 import org.sonar.db.version.v53.FixMsSqlCollation;
 import org.sonar.db.version.v53.UpdateCustomDashboardInLoadedTemplates;
 import org.sonar.db.version.v54.InsertGateAdminPermissionForEachProfileAdmin;
+import org.sonar.db.version.v54.RemoveComponentPageProperties;
 
 public class MigrationStepModule extends Module {
   @Override
@@ -119,6 +120,7 @@ public class MigrationStepModule extends Module {
       UpdateCustomDashboardInLoadedTemplates.class,
 
       // 5.4
-      InsertGateAdminPermissionForEachProfileAdmin.class);
+      InsertGateAdminPermissionForEachProfileAdmin.class,
+      RemoveComponentPageProperties.class);
   }
 }
diff --git a/sonar-db/src/main/java/org/sonar/db/version/v54/RemoveComponentPageProperties.java b/sonar-db/src/main/java/org/sonar/db/version/v54/RemoveComponentPageProperties.java
new file mode 100644
index 00000000000..f1c9b4f53d3
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/version/v54/RemoveComponentPageProperties.java
@@ -0,0 +1,61 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.version.v54;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.db.version.BaseDataChange;
+import org.sonar.db.version.MassUpdate;
+import org.sonar.db.version.Select;
+import org.sonar.db.version.SqlStatement;
+
+/**
+ * Remove properties use to store customisation of dropped component page:
+ * <ul>
+ *   <li>sonar.core.projectsdashboard.columns</li>
+ *   <li>sonar.core.projectsdashboard.defaultSortedColumn</li>
+ *   <li>any other property starting with "sonar.core.projectsdashboard."</li>
+ * </ul>
+ */
+public class RemoveComponentPageProperties extends BaseDataChange {
+
+  public RemoveComponentPageProperties(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    MassUpdate update = context.prepareMassUpdate().rowPluralName("properties for deleted component page");
+    update.select("SELECT p.id FROM properties p WHERE p.prop_key like 'sonar.core.projectsdashboard.%'");
+    update.update("DELETE FROM properties WHERE id=?");
+    update.execute(MigrationHandler.INSTANCE);
+  }
+
+  private enum MigrationHandler implements MassUpdate.Handler {
+    INSTANCE;
+
+    @Override
+    public boolean handle(Select.Row row, SqlStatement update) throws SQLException {
+      update.setLong(1, row.getLong(1));
+      return true;
+    }
+  }
+
+}
diff --git a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql
index 5c3f1dd41b5..775d65c4fe3 100644
--- a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql
+++ b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql
@@ -370,6 +370,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1005');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1006');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1007');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1008');
+INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1009');
 
 INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT, REMEMBER_TOKEN, REMEMBER_TOKEN_EXPIRES_AT) VALUES (1, 'admin', 'Administrator', '', 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '1418215735482', '1418215735482', null, null);
 ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2;
diff --git a/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java b/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java
index 47262dffd83..4d8c997e9b4 100644
--- a/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java
@@ -29,6 +29,6 @@ public class MigrationStepModuleTest {
   public void verify_count_of_added_MigrationStep_types() {
     ComponentContainer container = new ComponentContainer();
     new MigrationStepModule().configure(container);
-    assertThat(container.size()).isEqualTo(44);
+    assertThat(container.size()).isEqualTo(45);
   }
 }
diff --git a/sonar-db/src/test/java/org/sonar/db/version/v54/RemoveComponentPagePropertiesTest.java b/sonar-db/src/test/java/org/sonar/db/version/v54/RemoveComponentPagePropertiesTest.java
new file mode 100644
index 00000000000..5c0c2eec331
--- /dev/null
+++ b/sonar-db/src/test/java/org/sonar/db/version/v54/RemoveComponentPagePropertiesTest.java
@@ -0,0 +1,151 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.version.v54;
+
+import javax.annotation.Nullable;
+import org.junit.Before;
+import org.junit.ClassRule;
+import org.junit.Test;
+import org.sonar.api.utils.System2;
+import org.sonar.db.DbTester;
+
+import static java.lang.String.format;
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class RemoveComponentPagePropertiesTest {
+
+  private static final String TABLE_PROPERTIES = "properties";
+  private static final String EXPECTED_PREFIX = "sonar.core.projectsdashboard.";
+
+  @ClassRule
+  public static DbTester db = DbTester.createForSchema(System2.INSTANCE, RemoveComponentPagePropertiesTest.class, "schema.sql");
+
+  RemoveComponentPageProperties underTest;
+
+  @Before
+  public void setUp() {
+    db.executeUpdateSql("truncate table " + TABLE_PROPERTIES);
+
+    underTest = new RemoveComponentPageProperties(db.database());
+  }
+
+  @Test
+  public void migrate_empty_db() throws Exception {
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(0);
+  }
+
+  @Test
+  public void do_not_remove_property_sonar_core_projectsdashboard() throws Exception {
+    String missingLastDot = EXPECTED_PREFIX.substring(0, EXPECTED_PREFIX.length() - 2);
+    insertProperty(missingLastDot, null, null);
+
+    underTest.execute();
+
+    assertPropertiesContainsRow(missingLastDot, null, null);
+    assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(1);
+  }
+
+  @Test
+  public void remove_any_property_starting_with_sonar_core_projectsdashboard_and_a_dot() throws Exception {
+    insertProperty(EXPECTED_PREFIX, null, null);
+    insertProperty(EXPECTED_PREFIX + "toto", null, null);
+    insertProperty(EXPECTED_PREFIX + "chum", null, null);
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(0);
+  }
+
+  @Test
+  public void remove_any_property_starting_with_sonar_core_projectsdashboard_and_a_dot_for_resource_and_or_user() throws Exception {
+    String key = EXPECTED_PREFIX + "toto";
+    insertProperty(key, null, null);
+    insertProperty(key, 984, null);
+    insertProperty(key, null, 99);
+    insertProperty(key, 66, 77);
+
+    String otherKey = "other key";
+    insertProperty(otherKey, null, null);
+    insertProperty(otherKey, 984, null);
+    insertProperty(otherKey, null, 99);
+    insertProperty(otherKey, 66, 77);
+
+    underTest.execute();
+
+    assertPropertiesContainsRow(otherKey, null, null);
+    assertPropertiesContainsRow(otherKey, 984, null);
+    assertPropertiesContainsRow(otherKey, null, 99);
+    assertPropertiesContainsRow(otherKey, 66, 77);
+    assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(4);
+  }
+
+  @Test
+  public void do_not_remove_property_other_than_starting_with_sonar_core_projectsdashboard_and_a_dot() throws Exception {
+    insertProperty("toto.", null, null);
+    insertProperty("pouf", null, null);
+
+    underTest.execute();
+
+    assertPropertiesContainsRow("toto.", null, null);
+    assertPropertiesContainsRow("pouf", null, null);
+    assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(2);
+  }
+
+  private void assertPropertiesContainsRow(String key, @Nullable Integer resourceId, @Nullable Integer userId) {
+    assertThat(db.countSql(propertiesRowSql(key, resourceId, userId))).isEqualTo(1);
+  }
+
+  private static String propertiesRowSql(String key, @Nullable Integer resourceId, @Nullable Integer userId) {
+    return format(
+      "select count(*) from properties where prop_key='%s' and resource_id %s and user_id %s and text_value = '%s'",
+      key,
+      whereClauseOfInteger(resourceId),
+      whereClauseOfInteger(userId),
+      generatedValueOfProperty(key));
+  }
+
+  private static String whereClauseOfInteger(@Nullable Integer id) {
+    if (id == null) {
+      return "is null";
+    }
+    return "=" + id;
+  }
+
+  private void insertProperty(String key, @Nullable Integer resourceId, @Nullable Integer userId) {
+    db.executeUpdateSql(format(
+      "insert into properties (prop_key,resource_id,text_value,user_id) values ('%s',%s,'%s',%s)",
+      key, nullIntegerValue(resourceId), generatedValueOfProperty(key), nullIntegerValue(userId))
+      );
+  }
+
+  private static String generatedValueOfProperty(String key) {
+    return key + " value";
+  }
+
+  private static String nullIntegerValue(@Nullable Integer id) {
+    if (id == null) {
+      return "null";
+    }
+    return String.valueOf(id);
+  }
+
+}
diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v54/RemoveComponentPagePropertiesTest/schema.sql b/sonar-db/src/test/resources/org/sonar/db/version/v54/RemoveComponentPagePropertiesTest/schema.sql
new file mode 100644
index 00000000000..29f61f2f23b
--- /dev/null
+++ b/sonar-db/src/test/resources/org/sonar/db/version/v54/RemoveComponentPagePropertiesTest/schema.sql
@@ -0,0 +1,7 @@
+CREATE TABLE "PROPERTIES" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "PROP_KEY" VARCHAR(512),
+  "RESOURCE_ID" INTEGER,
+  "TEXT_VALUE" CLOB(2147483647),
+  "USER_ID" INTEGER
+);