SONAR-13747 Drop unused DB columns related to periods

7 files changed, 303 insertions, 0 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java
index cb31b354822..c3891c0face 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java
@@ -29,6 +29,9 @@ public class DbVersion85 implements DbVersion {
       .add(4000, "Delete 'project_alm_settings' orphans", DeleteProjectAlmSettingsOrphans.class)
       .add(4001, "Drop 'period', 'value_warning' columns from 'quality_gates_conditions' table", DropPeriodAndValueWarningColumnsFromQualityGateConditionsTable.class)
       .add(4001, "Drop 'project_alm_bindings' table", DropProjectAlmBindings.class)
+      .add(4002, "Drop unused variation values columns in 'project_measures' table", DropUnusedVariationsInProjectMeasures.class)
+      .add(4003, "Drop unused periods in 'snapshots' table", DropUnusedPeriodsInSnapshots.class)
+
     ;
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshots.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshots.java
new file mode 100644
index 00000000000..95c42b7acfd
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshots.java
@@ -0,0 +1,39 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 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.v85;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.sql.DropColumnsBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+public class DropUnusedPeriodsInSnapshots extends DdlChange {
+  private static final String TABLE_NAME = "snapshots";
+
+  public DropUnusedPeriodsInSnapshots(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    context.execute(new DropColumnsBuilder(getDialect(), TABLE_NAME, "period2_mode", "period2_param", "period2_date",
+      "period3_mode", "period3_param", "period3_date", "period4_mode", "period4_param", "period4_date", "period5_mode", "period5_param", "period5_date").build());
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasures.java
new file mode 100644
index 00000000000..c3158ade2cb
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasures.java
@@ -0,0 +1,38 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 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.v85;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.sql.DropColumnsBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+public class DropUnusedVariationsInProjectMeasures extends DdlChange {
+  private static final String TABLE_NAME = "project_measures";
+
+  public DropUnusedVariationsInProjectMeasures(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    context.execute(new DropColumnsBuilder(getDialect(), TABLE_NAME, "variation_value_2", "variation_value_3", "variation_value_4", "variation_value_5").build());
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshotsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshotsTest.java
new file mode 100644
index 00000000000..f1b7cb7c614
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshotsTest.java
@@ -0,0 +1,102 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 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.v85;
+
+import java.sql.SQLException;
+import java.sql.Types;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.step.MigrationStep;
+
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.entry;
+
+public class DropUnusedPeriodsInSnapshotsTest {
+  private static final String TABLE_NAME = "snapshots";
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(DropUnusedPeriodsInSnapshotsTest.class, "schema.sql");
+
+  private MigrationStep underTest = new DropUnusedPeriodsInSnapshots(db.database());
+
+  @Test
+  public void drops_table() throws SQLException {
+    insertData();
+    db.assertColumnDefinition(TABLE_NAME, "period2_mode", Types.VARCHAR, 100, true);
+    db.assertColumnDefinition(TABLE_NAME, "period3_mode", Types.VARCHAR, 100, true);
+    db.assertColumnDefinition(TABLE_NAME, "period4_mode", Types.VARCHAR, 100, true);
+    db.assertColumnDefinition(TABLE_NAME, "period5_mode", Types.VARCHAR, 100, true);
+
+    db.assertColumnDefinition(TABLE_NAME, "period2_param", Types.VARCHAR, 100, true);
+    db.assertColumnDefinition(TABLE_NAME, "period3_param", Types.VARCHAR, 100, true);
+    db.assertColumnDefinition(TABLE_NAME, "period4_param", Types.VARCHAR, 100, true);
+    db.assertColumnDefinition(TABLE_NAME, "period5_param", Types.VARCHAR, 100, true);
+
+    db.assertColumnDefinition(TABLE_NAME, "period2_date", Types.BIGINT, null, true);
+    db.assertColumnDefinition(TABLE_NAME, "period3_date", Types.BIGINT, null, true);
+    db.assertColumnDefinition(TABLE_NAME, "period4_date", Types.BIGINT, null, true);
+    db.assertColumnDefinition(TABLE_NAME, "period5_date", Types.BIGINT, null, true);
+
+    underTest.execute();
+    db.assertColumnDoesNotExist(TABLE_NAME, "period2_mode");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period3_mode");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period4_mode");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period5_mode");
+
+    db.assertColumnDoesNotExist(TABLE_NAME, "period2_param");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period3_param");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period4_param");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period5_param");
+
+    db.assertColumnDoesNotExist(TABLE_NAME, "period2_date");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period3_date");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period4_date");
+    db.assertColumnDoesNotExist(TABLE_NAME, "period5_date");
+
+    assertThat(db.selectFirst("select * from snapshots")).contains(entry("PERIOD1_MODE", "m1"));
+
+  }
+
+  private void insertData() {
+    db.executeInsert(TABLE_NAME,
+      "uuid", "uuid1",
+      "component_uuid", "component1",
+
+      "period1_mode", "m1",
+      "period2_mode", "m2",
+      "period3_mode", "m3",
+      "period4_mode", "m4",
+      "period5_mode", "m5",
+
+      "period1_param", "p1",
+      "period2_param", "p2",
+      "period3_param", "p3",
+      "period4_param", "p4",
+      "period5_param", "p5",
+
+      "period1_date", 1,
+      "period2_date", 2,
+      "period3_date", 3,
+      "period4_date", 4,
+      "period5_date", 5
+    );
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasuresTest.java
new file mode 100644
index 00000000000..07e8a541d11
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasuresTest.java
@@ -0,0 +1,70 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 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.v85;
+
+import java.sql.SQLException;
+import java.sql.Types;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.step.MigrationStep;
+
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.entry;
+
+public class DropUnusedVariationsInProjectMeasuresTest {
+  private static final String TABLE_NAME = "project_measures";
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(DropUnusedVariationsInProjectMeasuresTest.class, "schema.sql");
+
+  private MigrationStep underTest = new DropUnusedVariationsInProjectMeasures(db.database());
+
+  @Test
+  public void drops_table() throws SQLException {
+    insertData();
+    db.assertColumnDefinition(TABLE_NAME, "variation_value_2", Types.DOUBLE, null, true);
+    db.assertColumnDefinition(TABLE_NAME, "variation_value_3", Types.DOUBLE, null, true);
+    db.assertColumnDefinition(TABLE_NAME, "variation_value_4", Types.DOUBLE, null, true);
+    db.assertColumnDefinition(TABLE_NAME, "variation_value_5", Types.DOUBLE, null, true);
+
+    underTest.execute();
+    db.assertColumnDoesNotExist(TABLE_NAME, "variation_value_2");
+    db.assertColumnDoesNotExist(TABLE_NAME, "variation_value_3");
+    db.assertColumnDoesNotExist(TABLE_NAME, "variation_value_4");
+    db.assertColumnDoesNotExist(TABLE_NAME, "variation_value_5");
+    assertThat(db.selectFirst("select * from project_measures")).contains(entry("VARIATION_VALUE_1", 1.0));
+
+  }
+
+  private void insertData() {
+    db.executeInsert(TABLE_NAME,
+      "uuid", "uuid1",
+      "analysis_uuid", "analysis1",
+      "component_uuid", "component1",
+      "metric_uuid", "metric1",
+      "variation_value_1", 1.0,
+      "variation_value_2", 2.0,
+      "variation_value_3", 3.0,
+      "variation_value_4", 4.0,
+      "variation_value_5", 5.0
+    );
+  }
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshotsTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshotsTest/schema.sql
new file mode 100644
index 00000000000..186c3b6bd6a
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedPeriodsInSnapshotsTest/schema.sql
@@ -0,0 +1,30 @@
+CREATE TABLE "SNAPSHOTS"(
+    "UUID" VARCHAR(50) NOT NULL,
+    "COMPONENT_UUID" VARCHAR(50) NOT NULL,
+    "STATUS" VARCHAR(4) DEFAULT 'U' NOT NULL,
+    "ISLAST" BOOLEAN DEFAULT FALSE NOT NULL,
+    "VERSION" VARCHAR(500),
+    "PURGE_STATUS" INTEGER,
+    "BUILD_STRING" VARCHAR(100),
+    "REVISION" VARCHAR(100),
+    "BUILD_DATE" BIGINT,
+    "PERIOD1_MODE" VARCHAR(100),
+    "PERIOD1_PARAM" VARCHAR(100),
+    "PERIOD2_MODE" VARCHAR(100),
+    "PERIOD2_PARAM" VARCHAR(100),
+    "PERIOD3_MODE" VARCHAR(100),
+    "PERIOD3_PARAM" VARCHAR(100),
+    "PERIOD4_MODE" VARCHAR(100),
+    "PERIOD4_PARAM" VARCHAR(100),
+    "PERIOD5_MODE" VARCHAR(100),
+    "PERIOD5_PARAM" VARCHAR(100),
+    "PERIOD1_DATE" BIGINT,
+    "PERIOD2_DATE" BIGINT,
+    "PERIOD3_DATE" BIGINT,
+    "PERIOD4_DATE" BIGINT,
+    "PERIOD5_DATE" BIGINT,
+    "CREATED_AT" BIGINT
+);
+ALTER TABLE "SNAPSHOTS" ADD CONSTRAINT "PK_SNAPSHOTS" PRIMARY KEY("UUID");
+CREATE UNIQUE INDEX "ANALYSES_UUID" ON "SNAPSHOTS"("UUID");
+CREATE INDEX "SNAPSHOT_COMPONENT" ON "SNAPSHOTS"("COMPONENT_UUID");
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasuresTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasuresTest/schema.sql
new file mode 100644
index 00000000000..7f14e31a89f
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedVariationsInProjectMeasuresTest/schema.sql
@@ -0,0 +1,21 @@
+CREATE TABLE "PROJECT_MEASURES"(
+    "VALUE" DOUBLE,
+    "ANALYSIS_UUID" VARCHAR(50) NOT NULL,
+    "COMPONENT_UUID" VARCHAR(50) NOT NULL,
+    "TEXT_VALUE" VARCHAR(4000),
+    "ALERT_STATUS" VARCHAR(5),
+    "ALERT_TEXT" VARCHAR(4000),
+    "DESCRIPTION" VARCHAR(4000),
+    "PERSON_ID" INTEGER,
+    "VARIATION_VALUE_1" DOUBLE,
+    "VARIATION_VALUE_2" DOUBLE,
+    "VARIATION_VALUE_3" DOUBLE,
+    "VARIATION_VALUE_4" DOUBLE,
+    "VARIATION_VALUE_5" DOUBLE,
+    "MEASURE_DATA" BLOB,
+    "UUID" VARCHAR(40) NOT NULL,
+    "METRIC_UUID" VARCHAR(40) NOT NULL
+);
+ALTER TABLE "PROJECT_MEASURES" ADD CONSTRAINT "PK_PROJECT_MEASURES" PRIMARY KEY("UUID");
+CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES"("COMPONENT_UUID");
+CREATE INDEX "MEASURES_ANALYSIS_METRIC" ON "PROJECT_MEASURES"("ANALYSIS_UUID", "METRIC_UUID");