From 8643251e97d9975814a28a348e76b4a3f8d38ec9 Mon Sep 17 00:00:00 2001 From: Wojtek Wajerowicz <115081248+wojciech-wajerowicz-sonarsource@users.noreply.github.com> Date: Tue, 9 May 2023 15:04:23 +0200 Subject: [PATCH] SONAR-19085 Add index on 'external_groups(external_identity_provider, external_id). --- server/sonar-db-dao/src/schema/schema-sq.ddl | 1 + .../v101/CreateExternalGroupsTable.java | 3 - ...nalIdAndIdentityOnExternalGroupsTable.java | 62 +++++++++++++++++++ .../migration/version/v101/DbVersion101.java | 3 +- ...dAndIdentityOnExternalGroupsTableTest.java | 57 +++++++++++++++++ .../schema.sql | 6 ++ 6 files changed, 128 insertions(+), 4 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest/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 197259b24e8..8ceb69f5c2f 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -317,6 +317,7 @@ CREATE TABLE "EXTERNAL_GROUPS"( "EXTERNAL_IDENTITY_PROVIDER" CHARACTER VARYING(100) NOT NULL ); ALTER TABLE "EXTERNAL_GROUPS" ADD CONSTRAINT "PK_EXTERNAL_GROUPS" PRIMARY KEY("GROUP_UUID"); +CREATE UNIQUE INDEX "UNIQ_EXT_GRP_EXT_ID_PROVIDER" ON "EXTERNAL_GROUPS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_GROUP_ID" NULLS FIRST); CREATE TABLE "FILE_SOURCES"( "UUID" CHARACTER VARYING(40) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateExternalGroupsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateExternalGroupsTable.java index efd816d6036..8a41ebcbd8b 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateExternalGroupsTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateExternalGroupsTable.java @@ -30,13 +30,10 @@ import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVar public class CreateExternalGroupsTable extends CreateTableChange { - @VisibleForTesting static final String TABLE_NAME = "external_groups"; @VisibleForTesting static final String GROUP_UUID_COLUMN_NAME = "group_uuid"; - @VisibleForTesting static final String EXTERNAL_GROUP_ID_COLUMN_NAME = "external_group_id"; - @VisibleForTesting static final String EXTERNAL_IDENTITY_PROVIDER_COLUMN_NAME = "external_identity_provider"; public CreateExternalGroupsTable(Database db) { diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.java new file mode 100644 index 00000000000..9ceda7fbcb2 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.java @@ -0,0 +1,62 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import com.google.common.annotations.VisibleForTesting; +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.version.v101.CreateExternalGroupsTable.EXTERNAL_GROUP_ID_COLUMN_NAME; +import static org.sonar.server.platform.db.migration.version.v101.CreateExternalGroupsTable.EXTERNAL_IDENTITY_PROVIDER_COLUMN_NAME; +import static org.sonar.server.platform.db.migration.version.v101.CreateExternalGroupsTable.TABLE_NAME; + +public class CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable extends DdlChange { + + @VisibleForTesting + static final String INDEX_NAME = "uniq_ext_grp_ext_id_provider"; + + public CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + createIndex(context, connection); + } + } + + private static void createIndex(Context context, Connection connection) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn(EXTERNAL_IDENTITY_PROVIDER_COLUMN_NAME) + .addColumn(EXTERNAL_GROUP_ID_COLUMN_NAME) + .setUnique(true) + .build()); + } + + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java index f92e293bc9e..684da4ea0b2 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java @@ -56,6 +56,7 @@ public class DbVersion101 implements DbVersion { .add(10_1_012, "Drop column 'project_key' in 'user_tokens", DropProjectKeyInUserTokens.class) .add(10_1_013, "Increase size of 'ce_queue.task_type' from 15 to 40 characters", IncreaseTaskTypeColumnSizeInCeQueue.class) .add(10_1_014, "Increase size of 'ce_activity.task_type' from 15 to 40 characters", IncreaseTaskTypeColumnSizeInCeActivity.class) - .add(10_1_015, "Add 'external_groups' table.", CreateExternalGroupsTable.class); + .add(10_1_015, "Add 'external_groups' table.", CreateExternalGroupsTable.class) + .add(10_1_016, "Add index on 'external_groups(external_identity_provider, external_id).", CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.class); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest.java new file mode 100644 index 00000000000..68d7e75ba56 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest.java @@ -0,0 +1,57 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.sonar.server.platform.db.migration.version.v101.CreateExternalGroupsTable.EXTERNAL_GROUP_ID_COLUMN_NAME; +import static org.sonar.server.platform.db.migration.version.v101.CreateExternalGroupsTable.EXTERNAL_IDENTITY_PROVIDER_COLUMN_NAME; +import static org.sonar.server.platform.db.migration.version.v101.CreateExternalGroupsTable.TABLE_NAME; +import static org.sonar.server.platform.db.migration.version.v101.CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.INDEX_NAME; + +public class CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest { + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest.class, "schema.sql"); + + private final CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable createIndexOnExternalIdAndIdentityOnExternalGroupsTable = new CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable( + db.database()); + + @Test + public void migration_should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + createIndexOnExternalIdAndIdentityOnExternalGroupsTable.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, EXTERNAL_IDENTITY_PROVIDER_COLUMN_NAME, EXTERNAL_GROUP_ID_COLUMN_NAME); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + createIndexOnExternalIdAndIdentityOnExternalGroupsTable.execute(); + createIndexOnExternalIdAndIdentityOnExternalGroupsTable.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, EXTERNAL_IDENTITY_PROVIDER_COLUMN_NAME, EXTERNAL_GROUP_ID_COLUMN_NAME); + } + +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest/schema.sql new file mode 100644 index 00000000000..c43b1449258 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTableTest/schema.sql @@ -0,0 +1,6 @@ +CREATE TABLE "EXTERNAL_GROUPS"( + "GROUP_UUID" CHARACTER VARYING(40) NOT NULL, + "EXTERNAL_GROUP_ID" CHARACTER VARYING(255) NOT NULL, + "EXTERNAL_IDENTITY_PROVIDER" CHARACTER VARYING(100) NOT NULL +); +ALTER TABLE "EXTERNAL_GROUPS" ADD CONSTRAINT "PK_EXTERNAL_GROUPS" PRIMARY KEY("GROUP_UUID"); -- 2.39.5