From 253cc559df1e0b57b801afee2948d8950ee774bd Mon Sep 17 00:00:00 2001 From: Michal Duda Date: Wed, 21 Apr 2021 17:02:41 +0200 Subject: [PATCH] SONAR-14571 add index on WEBHOOK_UUID in WEBHOOK_DELIVERIES --- server/sonar-db-dao/src/schema/schema-sq.ddl | 1 + ...OnWebhookUuidInWebhookDeliveriesTable.java | 54 +++++++++++++++++++ .../db/migration/version/v89/DbVersion89.java | 3 +- ...bhookUuidInWebhookDeliveriesTableTest.java | 53 ++++++++++++++++++ .../schema.sql | 18 +++++++ 5 files changed, 128 insertions(+), 1 deletion(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTableTest/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 1ac0160e5f5..806a0a2a69a 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -945,6 +945,7 @@ CREATE TABLE "WEBHOOK_DELIVERIES"( ALTER TABLE "WEBHOOK_DELIVERIES" ADD CONSTRAINT "PK_WEBHOOK_DELIVERIES" PRIMARY KEY("UUID"); CREATE INDEX "COMPONENT_UUID" ON "WEBHOOK_DELIVERIES"("COMPONENT_UUID"); CREATE INDEX "CE_TASK_UUID" ON "WEBHOOK_DELIVERIES"("CE_TASK_UUID"); +CREATE INDEX "IDX_WBHK_DLVRS_WBHK_UUID" ON "WEBHOOK_DELIVERIES"("WEBHOOK_UUID"); CREATE TABLE "WEBHOOKS"( "UUID" VARCHAR(40) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTable.java new file mode 100644 index 00000000000..835685e6518 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTable.java @@ -0,0 +1,54 @@ +/* + * 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; + +public class AddIndexOnWebhookUuidInWebhookDeliveriesTable extends DdlChange { + private static final String TABLE_NAME = "webhook_deliveries"; + private static final String INDEX_NAME = "idx_wbhk_dlvrs_wbhk_uuid"; + + public AddIndexOnWebhookUuidInWebhookDeliveriesTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + if (!indexExists(INDEX_NAME)) { + context.execute(new CreateIndexBuilder() + .setUnique(false) + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn("webhook_uuid") + .build()); + } + } + + private boolean indexExists(String index) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + return DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, index, 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 54ced6ea0d9..ca3f5e06407 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 @@ -31,6 +31,7 @@ public class DbVersion89 implements DbVersion { .add(4401, "Drop local webhooks", DropLocalWebhooks.class) .add(4402, "Add Index on column 'main_branch_project_uuid' to 'components' table", AddMainBranchProjectUuidIndexToComponentTable.class) .add(4403, "Drop Github endpoint on project level setting", DropGithubEndpointOnProjectLevelSetting.class) - .add(4404, "Increase size of 'value' column in 'new_code_periods' table ", IncreaseSizeOfValueColumnInNewCodePeriodsTable.class); + .add(4404, "Increase size of 'value' column in 'new_code_periods' table ", IncreaseSizeOfValueColumnInNewCodePeriodsTable.class) + .add(4405, "Add index on column 'webhook_uuid' to 'webhook_deliveries' table", AddIndexOnWebhookUuidInWebhookDeliveriesTable.class); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTableTest.java new file mode 100644 index 00000000000..8418f26d150 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTableTest.java @@ -0,0 +1,53 @@ +/* + * 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 AddIndexOnWebhookUuidInWebhookDeliveriesTableTest { + + private static final String TABLE_NAME = "webhook_deliveries"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(AddIndexOnWebhookUuidInWebhookDeliveriesTableTest.class, "schema.sql"); + + private final MigrationStep underTest = new AddIndexOnWebhookUuidInWebhookDeliveriesTable(db.database()); + + @Test + public void execute() throws SQLException { + underTest.execute(); + + db.assertIndex(TABLE_NAME, "idx_wbhk_dlvrs_wbhk_uuid", "webhook_uuid"); + } + + @Test + public void migration_is_re_entrant() throws SQLException { + underTest.execute(); + + // re-entrant + underTest.execute(); + + db.assertIndex(TABLE_NAME, "idx_wbhk_dlvrs_wbhk_uuid", "webhook_uuid"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTableTest/schema.sql new file mode 100644 index 00000000000..b27a7bcefef --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v89/AddIndexOnWebhookUuidInWebhookDeliveriesTableTest/schema.sql @@ -0,0 +1,18 @@ +CREATE TABLE "WEBHOOK_DELIVERIES"( + "UUID" VARCHAR(40) NOT NULL, + "WEBHOOK_UUID" VARCHAR(40) NOT NULL, + "COMPONENT_UUID" VARCHAR(40) NOT NULL, + "CE_TASK_UUID" VARCHAR(40), + "ANALYSIS_UUID" VARCHAR(40), + "NAME" VARCHAR(100) NOT NULL, + "URL" VARCHAR(2000) NOT NULL, + "SUCCESS" BOOLEAN NOT NULL, + "HTTP_STATUS" INTEGER, + "DURATION_MS" BIGINT NOT NULL, + "PAYLOAD" CLOB NOT NULL, + "ERROR_STACKTRACE" CLOB, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "WEBHOOK_DELIVERIES" ADD CONSTRAINT "PK_WEBHOOK_DELIVERIES" PRIMARY KEY("UUID"); +CREATE INDEX "COMPONENT_UUID" ON "WEBHOOK_DELIVERIES"("COMPONENT_UUID"); +CREATE INDEX "CE_TASK_UUID" ON "WEBHOOK_DELIVERIES"("CE_TASK_UUID"); -- 2.39.5