From 9e90ec77057147e8ee53837b6b282a66795cce79 Mon Sep 17 00:00:00 2001 From: Teryk Bellahsene Date: Wed, 27 Jul 2016 11:05:57 +0200 Subject: [PATCH] SONAR-7917 Delete project dashboard data in DB --- .../migrate/1300_delete_project_dashboards.rb | 29 ++++ .../org/sonar/db/version/DatabaseVersion.java | 2 +- .../sonar/db/version/MigrationStepModule.java | 6 +- .../version/v61/DeleteProjectDashboards.java | 75 ++++++++++ .../sonar/db/version/v61/package-info.java | 24 +++ .../org/sonar/db/version/rows-h2.sql | 2 + .../db/version/MigrationStepModuleTest.java | 2 +- .../v61/DeleteProjectDashboardsTest.java | 141 ++++++++++++++++++ .../DeleteProjectDashboardsTest/schema.sql | 39 +++++ 9 files changed, 317 insertions(+), 3 deletions(-) create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1300_delete_project_dashboards.rb create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v61/DeleteProjectDashboards.java create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v61/package-info.java create mode 100644 sonar-db/src/test/java/org/sonar/db/version/v61/DeleteProjectDashboardsTest.java create mode 100644 sonar-db/src/test/resources/org/sonar/db/version/v61/DeleteProjectDashboardsTest/schema.sql diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1300_delete_project_dashboards.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1300_delete_project_dashboards.rb new file mode 100644 index 00000000000..dc61e11e5d5 --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1300_delete_project_dashboards.rb @@ -0,0 +1,29 @@ +# +# SonarQube, open source software quality management tool. +# Copyright (C) 2008-2014 SonarSource +# mailto:contact AT sonarsource DOT com +# +# SonarQube 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. +# +# SonarQube 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. +# + +# +# SonarQube 6.1 +# +class DeleteProjectDashboards < ActiveRecord::Migration + + def self.up + execute_java_migration('org.sonar.db.version.v61.DeleteProjectDashboards') + end +end 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 e8b30543628..ec8a77cb0f6 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 @@ -30,7 +30,7 @@ import org.sonar.db.MyBatis; public class DatabaseVersion { - public static final int LAST_VERSION = 1_277; + public static final int LAST_VERSION = 1_300; /** * 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 2e3b5f8bfcb..9358ea7df44 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 @@ -144,6 +144,7 @@ import org.sonar.db.version.v60.PopulateUuidColumnsOfProjects; import org.sonar.db.version.v60.PopulateUuidColumnsOfResourceIndex; import org.sonar.db.version.v60.PopulateUuidPathColumnOnProjects; import org.sonar.db.version.v60.RemoveUsersPasswordWhenNotLocal; +import org.sonar.db.version.v61.DeleteProjectDashboards; public class MigrationStepModule extends Module { @Override @@ -304,7 +305,10 @@ public class MigrationStepModule extends Module { DropTreesOfSnapshots.class, DropTreeColumnsFromSnapshots.class, DropSnapshotIdColumnFromMeasures.class, - AddBColumnsToProjects.class + AddBColumnsToProjects.class, + + // 6.1 + DeleteProjectDashboards.class ); } } diff --git a/sonar-db/src/main/java/org/sonar/db/version/v61/DeleteProjectDashboards.java b/sonar-db/src/main/java/org/sonar/db/version/v61/DeleteProjectDashboards.java new file mode 100644 index 00000000000..3a8398ebf70 --- /dev/null +++ b/sonar-db/src/main/java/org/sonar/db/version/v61/DeleteProjectDashboards.java @@ -0,0 +1,75 @@ +/* + * 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.v61; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.version.BaseDataChange; +import org.sonar.db.version.MassUpdate; + +public class DeleteProjectDashboards extends BaseDataChange { + + public DeleteProjectDashboards(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + deleteWidgetProperties(context); + deleteDashboardsAndWidgets(context); + } + + private static void deleteWidgetProperties(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("SELECT w.id " + + "FROM widgets w " + + " INNER JOIN dashboards d on w.dashboard_id=d.id " + + "WHERE d.is_global=?") + .setBoolean(1, false); + massUpdate.update("DELETE from widget_properties WHERE widget_id=?"); + massUpdate.rowPluralName("delete widget properties of project dashboards"); + massUpdate.execute((row, update) -> { + update.setLong(1, row.getLong(1)); + return true; + }); + } + + private static void deleteDashboardsAndWidgets(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("SELECT id FROM dashboards d WHERE d.is_global=?").setBoolean(1, false); + massUpdate.update("DELETE from widgets WHERE dashboard_id=?"); + massUpdate.update("DELETE from active_dashboards WHERE dashboard_id=?"); + massUpdate.update("DELETE from dashboards WHERE id=?"); + massUpdate.rowPluralName("delete project dashboards"); + massUpdate.execute((row, update, updateIndex) -> { + long dashboardId = row.getLong(1); + switch (updateIndex) { + case 0: + case 1: + case 2: + update.setLong(1, dashboardId); + return true; + default: + throw new IllegalArgumentException("Unsupported update index " + updateIndex); + } + }); + } +} diff --git a/sonar-db/src/main/java/org/sonar/db/version/v61/package-info.java b/sonar-db/src/main/java/org/sonar/db/version/v61/package-info.java new file mode 100644 index 00000000000..bcfd847df32 --- /dev/null +++ b/sonar-db/src/main/java/org/sonar/db/version/v61/package-info.java @@ -0,0 +1,24 @@ +/* + * 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. + */ + +@ParametersAreNonnullByDefault +package org.sonar.db.version.v61; + +import javax.annotation.ParametersAreNonnullByDefault; 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 ea257a66f8b..1e2b030ff55 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 @@ -486,5 +486,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1275'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1276'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1277'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1300'); + INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '1418215735482', '1418215735482'); 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 579506d38b1..c5fbc32539b 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(126); + assertThat(container.size()).isEqualTo(127); } } diff --git a/sonar-db/src/test/java/org/sonar/db/version/v61/DeleteProjectDashboardsTest.java b/sonar-db/src/test/java/org/sonar/db/version/v61/DeleteProjectDashboardsTest.java new file mode 100644 index 00000000000..705fd7f2774 --- /dev/null +++ b/sonar-db/src/test/java/org/sonar/db/version/v61/DeleteProjectDashboardsTest.java @@ -0,0 +1,141 @@ +/* + * 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.v61; + +import java.sql.SQLException; +import java.util.Arrays; +import java.util.List; +import java.util.stream.Collectors; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +import static java.lang.String.valueOf; +import static org.assertj.core.api.Assertions.assertThat; + +public class DeleteProjectDashboardsTest { + @Rule + public DbTester db = DbTester.createForSchema(System2.INSTANCE, DeleteProjectDashboardsTest.class, "schema.sql"); + + private DeleteProjectDashboards underTest = new DeleteProjectDashboards(db.database()); + + @Test + public void no_effect_on_empty_tables() throws SQLException { + underTest.execute(); + + assertThat(db.countRowsOfTable("dashboards")).isEqualTo(0); + } + + @Test + public void delete_project_dashboard_data() throws SQLException { + insertGlobalDashboards(1L, 10L, 11L); + insertProjectDashboards(2L, 20L, 21L); + insertActiveDashboards(1L, 10L, 11L, 12L); + insertActiveDashboards(2L, 20L, 21L, 22L); + insertWidgets(1L, 100L, 101L, 102L); + insertWidgets(2L, 200L, 201L, 202L); + insertWidgetProperties(100L, 1001L, 1002L, 1003L); + insertWidgetProperties(202L, 2021L, 2022L, 2023L); + + underTest.execute(); + + assertIdsOfDashboardsAre(1L, 10L, 11L); + assertIdsOfActiveDashboardsAre(10L, 11L, 12L); + assertIdsOfWidgetsAre(100L, 101L, 102L); + assertIdsOfWidgetPropertiesAre(1001L, 1002L, 1003L); + } + + @Test + public void is_reentrant() throws SQLException { + insertGlobalDashboards(10L, 11L, 12L); + insertProjectDashboards(20L, 21L, 22L); + underTest.execute(); + assertIdsOfDashboardsAre(10L, 11L, 12L); + + underTest.execute(); + assertIdsOfDashboardsAre(10L, 11L, 12L); + } + + private void insertProjectDashboards(long... ids) { + Arrays.stream(ids).forEach(id -> insertDashboard(id, false)); + } + + private void insertGlobalDashboards(long... ids) { + Arrays.stream(ids).forEach(id -> insertDashboard(id, true)); + } + + private void insertDashboard(long id, boolean isGlobal) { + db.executeInsert( + "dashboards", + "ID", valueOf(id), + "IS_GLOBAL", valueOf(isGlobal)); + } + + private void insertActiveDashboards(long dashboardId, long... ids) { + Arrays.stream(ids).forEach( + id -> db.executeInsert( + "active_dashboards", + "ID", valueOf(id), + "DASHBOARD_ID", valueOf(dashboardId))); + } + + private void insertWidgets(long dashboardId, long... ids) { + Arrays.stream(ids).forEach( + id -> db.executeInsert( + "widgets", + "ID", valueOf(id), + "WIDGET_KEY", valueOf(id), + "DASHBOARD_ID", valueOf(dashboardId))); + } + + private void insertWidgetProperties(long widgetId, long... ids) { + Arrays.stream(ids).forEach( + id -> db.executeInsert( + "widget_properties", + "ID", valueOf(id), + "WIDGET_ID", valueOf(widgetId))); + } + + private void assertIdsOfDashboardsAre(Long... ids) { + List idsInDb = db.select("select ID from dashboards").stream().map(map -> (Long) map.get("ID")).collect(Collectors.toList()); + + assertThat(idsInDb).containsOnly(ids); + } + + private void assertIdsOfActiveDashboardsAre(Long... ids) { + List idsInDb = db.select("select ID from active_dashboards").stream().map(map -> (Long) map.get("ID")).collect(Collectors.toList()); + + assertThat(idsInDb).containsOnly(ids); + } + + private void assertIdsOfWidgetsAre(Long... ids) { + List idsInDb = db.select("select ID from widgets").stream().map(map -> (Long) map.get("ID")).collect(Collectors.toList()); + + assertThat(idsInDb).containsOnly(ids); + } + + private void assertIdsOfWidgetPropertiesAre(Long... ids) { + List idsInDb = db.select("select ID from widget_properties").stream().map(map -> (Long) map.get("ID")).collect(Collectors.toList()); + + assertThat(idsInDb).containsOnly(ids); + } +} diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v61/DeleteProjectDashboardsTest/schema.sql b/sonar-db/src/test/resources/org/sonar/db/version/v61/DeleteProjectDashboardsTest/schema.sql new file mode 100644 index 00000000000..1030e93bff0 --- /dev/null +++ b/sonar-db/src/test/resources/org/sonar/db/version/v61/DeleteProjectDashboardsTest/schema.sql @@ -0,0 +1,39 @@ +CREATE TABLE "DASHBOARDS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_ID" INTEGER, + "NAME" VARCHAR(256), + "DESCRIPTION" VARCHAR(1000), + "COLUMN_LAYOUT" VARCHAR(20), + "SHARED" BOOLEAN, + "IS_GLOBAL" BOOLEAN, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "ACTIVE_DASHBOARDS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "DASHBOARD_ID" INTEGER NOT NULL, + "USER_ID" INTEGER, + "ORDER_INDEX" INTEGER +); + +CREATE TABLE "WIDGETS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "DASHBOARD_ID" INTEGER NOT NULL, + "WIDGET_KEY" VARCHAR(256) NOT NULL, + "NAME" VARCHAR(256), + "DESCRIPTION" VARCHAR(1000), + "COLUMN_INDEX" INTEGER, + "ROW_INDEX" INTEGER, + "CONFIGURED" BOOLEAN, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP, + "RESOURCE_ID" INTEGER +); + +CREATE TABLE "WIDGET_PROPERTIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "WIDGET_ID" INTEGER NOT NULL, + "KEE" VARCHAR(100), + "TEXT_VALUE" VARCHAR(4000) +); -- 2.39.5