From 34f18002b369c8b35f4331347fc5529c9cac4f28 Mon Sep 17 00:00:00 2001 From: Simon Brandhof Date: Fri, 12 May 2017 15:04:20 +0200 Subject: [PATCH] SONAR-9197 add index to active_rule_parameters in order to support big volume when organizations are enabled --- .../org/sonar/db/version/schema-h2.ddl | 1 + .../v64/AddIndexOnActiveRuleParameters.java | 43 +++++++++++++++ .../db/migration/version/v64/DbVersion64.java | 3 +- .../AddIndexOnActiveRuleParametersTest.java | 55 +++++++++++++++++++ .../version/v64/DbVersion64Test.java | 2 +- .../active_rule_parameters_without_index.sql | 7 +++ 6 files changed, 109 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParameters.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParametersTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParametersTest/active_rule_parameters_without_index.sql diff --git a/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl b/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl index 321ff0a0501..4c14adca425 100644 --- a/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl +++ b/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl @@ -370,6 +370,7 @@ CREATE TABLE "ACTIVE_RULE_PARAMETERS" ( "RULES_PARAMETER_KEY" VARCHAR(128), "VALUE" VARCHAR(4000) ); +CREATE INDEX "IX_ARP_ON_ACTIVE_RULE_ID" ON "ACTIVE_RULE_PARAMETERS" ("ACTIVE_RULE_ID"); CREATE TABLE "USERS" ( diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParameters.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParameters.java new file mode 100644 index 00000000000..26e29ec8960 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParameters.java @@ -0,0 +1,43 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v64; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.IntegerColumnDef.newIntegerColumnDefBuilder; + +public class AddIndexOnActiveRuleParameters extends DdlChange { + public AddIndexOnActiveRuleParameters(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute( + new CreateIndexBuilder(getDialect()) + .setTable("active_rule_parameters") + .setName("ix_arp_on_active_rule_id") + .addColumn(newIntegerColumnDefBuilder().setColumnName("active_rule_id").setIsNullable(false).build()) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java index a20c27cfee5..58a8eed8f77 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java @@ -71,6 +71,7 @@ public class DbVersion64 implements DbVersion { .add(1640, "Make column ORGANIZATIONS.NEW_PROJECT_PRIVATE not nullable", MakeColumnNewProjectPrivateNotNullable.class) .add(1641, "Make components private based on permissions", MakeComponentsPrivateBasedOnPermissions.class) .add(1642, "Support private project in default permission template", SupportPrivateProjectInDefaultPermissionTemplate.class) - .add(1643, "Drop user and codeviewer perms to AnyOne in permission templates", SupportProjectVisibilityInTemplates.class); + .add(1643, "Drop user and codeviewer perms to AnyOne in permission templates", SupportProjectVisibilityInTemplates.class) + .add(1644, "Add index on active_rule_parameters.active_rule_id", AddIndexOnActiveRuleParameters.class); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParametersTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParametersTest.java new file mode 100644 index 00000000000..ec170f3cc3b --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParametersTest.java @@ -0,0 +1,55 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v64; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.junit.rules.ExpectedException; +import org.sonar.db.CoreDbTester; + +public class AddIndexOnActiveRuleParametersTest { + + private static final String TABLE = "active_rule_parameters"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(AddIndexOnActiveRuleParametersTest.class, "active_rule_parameters_without_index.sql"); + @Rule + public ExpectedException expectedException = ExpectedException.none(); + + private AddIndexOnActiveRuleParameters underTest = new AddIndexOnActiveRuleParameters(db.database()); + + @Test + public void execute_adds_index() throws SQLException { + underTest.execute(); + + db.assertIndex(TABLE, "ix_arp_on_active_rule_id", "active_rule_id"); + } + + @Test + public void execute_is_not_reentrant() throws SQLException { + underTest.execute(); + + expectedException.expect(IllegalStateException.class); + expectedException.expectMessage("Fail to execute"); + + underTest.execute(); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java index f712f01a983..f42f7f26746 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java @@ -35,6 +35,6 @@ public class DbVersion64Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 44); + verifyMigrationCount(underTest, 45); } } diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParametersTest/active_rule_parameters_without_index.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParametersTest/active_rule_parameters_without_index.sql new file mode 100644 index 00000000000..dd1904476f1 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/AddIndexOnActiveRuleParametersTest/active_rule_parameters_without_index.sql @@ -0,0 +1,7 @@ +CREATE TABLE "ACTIVE_RULE_PARAMETERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ACTIVE_RULE_ID" INTEGER NOT NULL, + "RULES_PARAMETER_ID" INTEGER NOT NULL, + "RULES_PARAMETER_KEY" VARCHAR(128), + "VALUE" VARCHAR(4000) +); -- 2.39.5