From 3ebbd70a22802524f2fefe133cb51c3785f655ee Mon Sep 17 00:00:00 2001 From: Pierre Date: Wed, 14 Apr 2021 17:27:46 +0200 Subject: [PATCH] SONAR-14699 add index on components.main_branch_project_uuid --- server/sonar-db-dao/src/schema/schema-sq.ddl | 1 + ...ranchProjectUuidIndexToComponentTable.java | 62 +++++++++++++++++++ .../db/migration/version/v89/DbVersion89.java | 3 +- ...hProjectUuidIndexToComponentTableTest.java | 52 ++++++++++++++++ .../schema.sql | 40 ++++++++++++ 5 files changed, 157 insertions(+), 1 deletion(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTableTest/schema.sql diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index 3430f5a74c4..8e997a27dbb 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -226,6 +226,7 @@ CREATE INDEX "PROJECTS_PROJECT_UUID" ON "COMPONENTS"("PROJECT_UUID"); CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER"); CREATE INDEX "PROJECTS_ROOT_UUID" ON "COMPONENTS"("ROOT_UUID"); CREATE INDEX "PROJECTS_UUID" ON "COMPONENTS"("UUID"); +CREATE INDEX "IDX_MAIN_BRANCH_PRJ_UUID" ON "COMPONENTS"("MAIN_BRANCH_PROJECT_UUID"); CREATE TABLE "DEFAULT_QPROFILES"( "LANGUAGE" VARCHAR(20) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTable.java new file mode 100644 index 00000000000..69f5bda74fe --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTable.java @@ -0,0 +1,62 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v89; + +import java.sql.Connection; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +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.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class AddMainBranchProjectUuidIndexToComponentTable extends DdlChange { + + private static final String TABLE_NAME = "components"; + private static final String INDEX_NAME = "idx_main_branch_prj_uuid"; + + public AddMainBranchProjectUuidIndexToComponentTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + if (!indexExists()) { + context.execute(new CreateIndexBuilder() + .setUnique(false) + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("main_branch_project_uuid") + .setIsNullable(false) + .setLimit(50) + .build()) + .build()); + } + } + + private boolean indexExists() throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + return DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection); + } + } + +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/DbVersion89.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/DbVersion89.java index 0c9ff6e637a..9637872efee 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/DbVersion89.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/DbVersion89.java @@ -28,6 +28,7 @@ public class DbVersion89 implements DbVersion { public void addSteps(MigrationStepRegistry registry) { registry .add(4400, "Add indices on columns 'type' and 'value' to 'new_code_periods' table", AddIndicesToNewCodePeriodTable.class) - .add(4401, "Drop local webhooks", DropLocalWebhooks.class); + .add(4401, "Drop local webhooks", DropLocalWebhooks.class) + .add(4402, "Add Index on column 'main_branch_project_uuid' to 'components' table", AddMainBranchProjectUuidIndexToComponentTable.class); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTableTest.java new file mode 100644 index 00000000000..fe8c6d680e6 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTableTest.java @@ -0,0 +1,52 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v89; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.MigrationStep; + +public class AddMainBranchProjectUuidIndexToComponentTableTest { + private static final String TABLE_NAME = "components"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(AddMainBranchProjectUuidIndexToComponentTableTest.class, "schema.sql"); + + private final MigrationStep underTest = new AddMainBranchProjectUuidIndexToComponentTable(db.database()); + + @Test + public void execute() throws SQLException { + underTest.execute(); + + db.assertIndex(TABLE_NAME, "idx_main_branch_prj_uuid", "main_branch_project_uuid"); + } + + @Test + public void migration_is_re_entrant() throws SQLException { + underTest.execute(); + + // re-entrant + underTest.execute(); + + db.assertIndex(TABLE_NAME, "idx_main_branch_prj_uuid", "main_branch_project_uuid"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTableTest/schema.sql new file mode 100644 index 00000000000..adc6b495cec --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v89/AddMainBranchProjectUuidIndexToComponentTableTest/schema.sql @@ -0,0 +1,40 @@ +CREATE TABLE "COMPONENTS"( + "UUID" VARCHAR(50) NOT NULL, + "KEE" VARCHAR(400), + "DEPRECATED_KEE" VARCHAR(400), + "NAME" VARCHAR(2000), + "LONG_NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "ENABLED" BOOLEAN DEFAULT TRUE NOT NULL, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "PRIVATE" BOOLEAN NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "LANGUAGE" VARCHAR(20), + "COPY_COMPONENT_UUID" VARCHAR(50), + "PATH" VARCHAR(2000), + "UUID_PATH" VARCHAR(1500) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(1500), + "MAIN_BRANCH_PROJECT_UUID" VARCHAR(50), + "B_CHANGED" BOOLEAN, + "B_NAME" VARCHAR(500), + "B_LONG_NAME" VARCHAR(500), + "B_DESCRIPTION" VARCHAR(2000), + "B_ENABLED" BOOLEAN, + "B_QUALIFIER" VARCHAR(10), + "B_LANGUAGE" VARCHAR(20), + "B_COPY_COMPONENT_UUID" VARCHAR(50), + "B_PATH" VARCHAR(2000), + "B_UUID_PATH" VARCHAR(1500), + "B_MODULE_UUID" VARCHAR(50), + "B_MODULE_UUID_PATH" VARCHAR(1500), + "CREATED_AT" TIMESTAMP +); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "COMPONENTS"("KEE"); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "COMPONENTS"("MODULE_UUID"); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "COMPONENTS"("PROJECT_UUID"); +CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER"); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "COMPONENTS"("ROOT_UUID"); +CREATE INDEX "PROJECTS_UUID" ON "COMPONENTS"("UUID"); -- 2.39.5