From 0500f966a3ea0763d87ae4138210ada78dd4f33c Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Fri, 19 Jul 2019 16:26:41 +0200 Subject: [PATCH] SONAR-8520 index on DEPRECATED_RULE_KEYS.RULE_ID must not be unique this is an old error which slipped through the radar because H2 schema was correct --- .../db/migration/version/v80/DbVersion80.java | 3 +- ...eprecatedRuleKeysRuleIdIndexNonUnique.java | 54 +++++++++++++++++++ .../version/v80/DbVersion80Test.java | 2 +- ...catedRuleKeysRuleIdIndexNonUniqueTest.java | 42 +++++++++++++++ .../deprecated_rule_keys.sql | 11 ++++ 5 files changed, 110 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUnique.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest/deprecated_rule_keys.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v80/DbVersion80.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v80/DbVersion80.java index f3f0a818d1e..a11fd4e4156 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v80/DbVersion80.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v80/DbVersion80.java @@ -27,6 +27,7 @@ public class DbVersion80 implements DbVersion { public void addSteps(MigrationStepRegistry registry) { registry .add(3000, "Set Organizations#guarded column nullable", MakeOrganizationsGuardedNullable.class) - .add(3001, "Create ProjectQualityGates table", CreateProjectQualityGatesTable.class); + .add(3001, "Create ProjectQualityGates table", CreateProjectQualityGatesTable.class) + .add(3002, "Make index on DEPRECATED_RULE_KEYS.RULE_ID non unique", MakeDeprecatedRuleKeysRuleIdIndexNonUnique.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUnique.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUnique.java new file mode 100644 index 00000000000..192af3772f2 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUnique.java @@ -0,0 +1,54 @@ +/* + * SonarQube + * Copyright (C) 2009-2019 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.v80; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.def.IntegerColumnDef; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.sql.DropIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class MakeDeprecatedRuleKeysRuleIdIndexNonUnique extends DdlChange { + private static final String TABLE_NAME = "deprecated_rule_keys"; + private static final IntegerColumnDef RULE_ID_COLUMN = IntegerColumnDef.newIntegerColumnDefBuilder() + .setColumnName("rule_id") + .setIsNullable(false) + .build(); + + public MakeDeprecatedRuleKeysRuleIdIndexNonUnique(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new DropIndexBuilder(getDialect()) + .setTable(TABLE_NAME) + .setName("rule_id_deprecated_rule_keys") + .build()); + + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName("rule_id_deprecated_rule_keys") + .setUnique(false) + .addColumn(RULE_ID_COLUMN) + .build()); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v80/DbVersion80Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v80/DbVersion80Test.java index 3552b825d63..cefa4368acf 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v80/DbVersion80Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v80/DbVersion80Test.java @@ -35,7 +35,7 @@ public class DbVersion80Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 2); + verifyMigrationCount(underTest, 3); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest.java new file mode 100644 index 00000000000..13a780659c8 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest.java @@ -0,0 +1,42 @@ +/* + * SonarQube + * Copyright (C) 2009-2019 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.v80; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest { + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest.class, "deprecated_rule_keys.sql"); + + private MakeDeprecatedRuleKeysRuleIdIndexNonUnique underTest = new MakeDeprecatedRuleKeysRuleIdIndexNonUnique(db.database()); + + @Test + public void execute_makes_index_non_unique() throws SQLException { + db.assertUniqueIndex("deprecated_rule_keys", "rule_id_deprecated_rule_keys", "rule_id"); + + underTest.execute(); + + db.assertIndex("deprecated_rule_keys", "rule_id_deprecated_rule_keys", "rule_id"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest/deprecated_rule_keys.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest/deprecated_rule_keys.sql new file mode 100644 index 00000000000..b46329a103b --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v80/MakeDeprecatedRuleKeysRuleIdIndexNonUniqueTest/deprecated_rule_keys.sql @@ -0,0 +1,11 @@ +CREATE TABLE "DEPRECATED_RULE_KEYS" ( + "UUID" VARCHAR(40) NOT NULL, + "RULE_ID" INTEGER NOT NULL, + "OLD_REPOSITORY_KEY" VARCHAR(200) NOT NULL, + "OLD_RULE_KEY" VARCHAR(255) NOT NULL, + "CREATED_AT" BIGINT, + + CONSTRAINT "PK_DEPRECATED_RULE_KEYS" PRIMARY KEY ("UUID") +); +CREATE UNIQUE INDEX "UNIQ_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS" ("OLD_REPOSITORY_KEY", "OLD_RULE_KEY"); +CREATE UNIQUE INDEX "RULE_ID_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS" ("RULE_ID"); -- 2.39.5