From 3347e33f195421f4eb04b5a3bf93ad882ed267cd Mon Sep 17 00:00:00 2001 From: Duarte Meneses Date: Thu, 13 Aug 2020 14:04:16 -0500 Subject: [PATCH] SONAR-13518 Drop unused indexes --- .../org/sonar/db/alm/AlmAppInstallDao.java | 9 -- .../org/sonar/db/alm/AlmAppInstallMapper.xml | 10 -- .../sonar/db/alm/AlmAppInstallDaoTest.java | 36 ------ .../db/migration/version/v85/DbVersion85.java | 6 +- .../version/v85/DropUnusedIndexes.java | 45 +++++++ .../version/v85/DropUnusedIndexesTest.java | 50 ++++++++ .../v85/DropUnusedIndexesTest/schema.sql | 111 ++++++++++++++++++ 7 files changed, 209 insertions(+), 58 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexes.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexesTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexesTest/schema.sql diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDao.java index a5630c417c2..c56a80a7404 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDao.java @@ -61,11 +61,6 @@ public class AlmAppInstallDao implements Dao { return Optional.ofNullable(mapper.selectByOrganizationAlmId(alm.getId(), organizationAlmId)); } - public Optional selectByInstallationId(DbSession dbSession, ALM alm, String installationId) { - AlmAppInstallMapper mapper = getMapper(dbSession); - return Optional.ofNullable(mapper.selectByInstallationId(alm.getId(), installationId)); - } - public Optional selectByOrganization(DbSession dbSession, OrganizationDto organization) { AlmAppInstallMapper mapper = getMapper(dbSession); return Optional.ofNullable(mapper.selectByOrganizationUuid(organization.getUuid())); @@ -76,10 +71,6 @@ public class AlmAppInstallDao implements Dao { return executeLargeInputs(organizationUuids, uuids -> getMapper(dbSession).selectByOrganizationUuids(uuids)); } - public List selectUnboundByUserExternalId(DbSession dbSession, String userExternalId) { - return getMapper(dbSession).selectUnboundByUserExternalId(userExternalId); - } - public void insertOrUpdate(DbSession dbSession, ALM alm, String organizationAlmId, @Nullable Boolean isOwnerUser, String installId, @Nullable String userExternalId) { checkAlm(alm); checkOrganizationAlmId(organizationAlmId); diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/alm/AlmAppInstallMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/alm/AlmAppInstallMapper.xml index 57bd49a7538..b7a8fcd42b3 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/alm/AlmAppInstallMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/alm/AlmAppInstallMapper.xml @@ -60,16 +60,6 @@ - - INSERT INTO alm_app_installs ( diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmAppInstallDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmAppInstallDaoTest.java index 0bee77f92e9..f9abbd8f5e8 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmAppInstallDaoTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmAppInstallDaoTest.java @@ -32,7 +32,6 @@ import org.sonar.core.util.Uuids; import org.sonar.db.DbSession; import org.sonar.db.DbTester; import org.sonar.db.organization.OrganizationDto; -import org.sonar.db.user.UserDto; import static java.util.Collections.emptyList; import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric; @@ -117,41 +116,6 @@ public class AlmAppInstallDaoTest { underTest.selectByOrganizationAlmId(dbSession, GITHUB, EMPTY_STRING); } - @Test - public void selectByInstallationId() { - when(uuidFactory.create()).thenReturn(A_UUID); - when(system2.now()).thenReturn(DATE); - underTest.insertOrUpdate(dbSession, GITHUB, AN_ORGANIZATION_ALM_ID, true, AN_INSTALL, Uuids.createFast()); - - assertThat(underTest.selectByInstallationId(dbSession, GITHUB, AN_INSTALL).get()) - .extracting(AlmAppInstallDto::getUuid, AlmAppInstallDto::getAlm, AlmAppInstallDto::getInstallId, AlmAppInstallDto::getOrganizationAlmId, - AlmAppInstallDto::getCreatedAt, AlmAppInstallDto::getUpdatedAt) - .contains(A_UUID, GITHUB, AN_ORGANIZATION_ALM_ID, AN_INSTALL, DATE, DATE); - - assertThat(underTest.selectByInstallationId(dbSession, GITHUB, "unknown installation")).isEmpty(); - assertThat(underTest.selectByInstallationId(dbSession, BITBUCKETCLOUD, AN_INSTALL)).isEmpty(); - } - - @Test - public void selectUnboundByUserExternalId() { - when(uuidFactory.create()).thenReturn(A_UUID); - when(system2.now()).thenReturn(DATE); - UserDto user1 = db.users().insertUser(); - UserDto user2 = db.users().insertUser(); - OrganizationDto organization1 = db.organizations().insert(); - OrganizationDto organization2 = db.organizations().insert(); - AlmAppInstallDto almAppInstall1 = db.alm().insertAlmAppInstall(app -> app.setUserExternalId(user1.getExternalId())); - AlmAppInstallDto almAppInstall2 = db.alm().insertAlmAppInstall(app -> app.setUserExternalId(user1.getExternalId())); - AlmAppInstallDto almAppInstall3 = db.alm().insertAlmAppInstall(app -> app.setUserExternalId(user2.getExternalId())); - db.alm().insertOrganizationAlmBinding(organization1, almAppInstall1, true); - db.alm().insertOrganizationAlmBinding(organization2, almAppInstall3, true); - - assertThat(underTest.selectUnboundByUserExternalId(dbSession, user1.getExternalId())) - .extracting(AlmAppInstallDto::getUuid) - .containsExactlyInAnyOrder(almAppInstall2.getUuid()); - assertThat(underTest.selectUnboundByUserExternalId(dbSession, user2.getExternalId())).isEmpty(); - } - @Test public void selectByOrganization() { OrganizationDto organization = db.organizations().insert(); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java index c3891c0face..ceea70ead6b 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java @@ -28,9 +28,9 @@ public class DbVersion85 implements DbVersion { registry .add(4000, "Delete 'project_alm_settings' orphans", DeleteProjectAlmSettingsOrphans.class) .add(4001, "Drop 'period', 'value_warning' columns from 'quality_gates_conditions' table", DropPeriodAndValueWarningColumnsFromQualityGateConditionsTable.class) - .add(4001, "Drop 'project_alm_bindings' table", DropProjectAlmBindings.class) - .add(4002, "Drop unused variation values columns in 'project_measures' table", DropUnusedVariationsInProjectMeasures.class) - .add(4003, "Drop unused periods in 'snapshots' table", DropUnusedPeriodsInSnapshots.class) + .add(4002, "Drop 'project_alm_bindings' table", DropProjectAlmBindings.class) + .add(4003, "Drop unused variation values columns in 'project_measures' table", DropUnusedVariationsInProjectMeasures.class) + .add(4004, "Drop unused periods in 'snapshots' table", DropUnusedPeriodsInSnapshots.class) ; } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexes.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexes.java new file mode 100644 index 00000000000..cf874bcaed3 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexes.java @@ -0,0 +1,45 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 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.v85; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.DropIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class DropUnusedIndexes extends DdlChange { + public DropUnusedIndexes(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + // duplicated by its PK + context.execute(new DropIndexBuilder(getDialect()).setTable("events").setName("events_uuid").build()); + // duplicated by its PK + context.execute(new DropIndexBuilder(getDialect()).setTable("ce_activity").setName("ce_activity_uuid").build()); + // no query filtering or ordering by users_updated_at + context.execute(new DropIndexBuilder(getDialect()).setTable("file_sources").setName("file_sources_updated_at").build()); + // no query filtering or ordering by users_updated_at + context.execute(new DropIndexBuilder(getDialect()).setTable("users").setName("users_updated_at").build()); + // not used + context.execute(new DropIndexBuilder(getDialect()).setTable("alm_app_installs").setName("alm_app_installs_external_id").build()); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexesTest.java new file mode 100644 index 00000000000..1439409a4a3 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexesTest.java @@ -0,0 +1,50 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 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.v85; + +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 DropUnusedIndexesTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DropUnusedIndexesTest.class, "schema.sql"); + + private MigrationStep underTest = new DropUnusedIndexes(db.database()); + + @Test + public void drops_indexes() throws SQLException { + db.assertUniqueIndex("events", "events_uuid", "uuid"); + db.assertUniqueIndex("ce_activity", "ce_activity_uuid", "uuid"); + db.assertIndex("file_sources", "file_sources_updated_at", "updated_at"); + db.assertIndex("users", "users_updated_at", "updated_at"); + db.assertIndex("alm_app_installs", "alm_app_installs_external_id", "user_external_id"); + + underTest.execute(); + + db.assertIndexDoesNotExist("events", "events_uuid"); + db.assertIndexDoesNotExist("ce_activity", "ce_activity_uuid"); + db.assertIndexDoesNotExist("file_sources", "file_sources_updated_at"); + db.assertIndexDoesNotExist("users", "users_updated_at"); + db.assertIndexDoesNotExist("alm_app_installs", "alm_app_installs_external_id"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexesTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexesTest/schema.sql new file mode 100644 index 00000000000..2f3316e177f --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropUnusedIndexesTest/schema.sql @@ -0,0 +1,111 @@ +CREATE TABLE "ALM_APP_INSTALLS"( + "UUID" VARCHAR(40) NOT NULL, + "ALM_ID" VARCHAR(40) NOT NULL, + "OWNER_ID" VARCHAR(4000) NOT NULL, + "INSTALL_ID" VARCHAR(4000) NOT NULL, + "IS_OWNER_USER" BOOLEAN NOT NULL, + "USER_EXTERNAL_ID" VARCHAR(255), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "ALM_APP_INSTALLS" ADD CONSTRAINT "PK_ALM_APP_INSTALLS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "ALM_APP_INSTALLS_OWNER" ON "ALM_APP_INSTALLS"("ALM_ID", "OWNER_ID"); +CREATE UNIQUE INDEX "ALM_APP_INSTALLS_INSTALL" ON "ALM_APP_INSTALLS"("ALM_ID", "INSTALL_ID"); +CREATE INDEX "ALM_APP_INSTALLS_EXTERNAL_ID" ON "ALM_APP_INSTALLS"("USER_EXTERNAL_ID"); + +CREATE TABLE "USERS"( + "UUID" VARCHAR(255) NOT NULL, + "LOGIN" VARCHAR(255) NOT NULL, + "ORGANIZATION_UUID" VARCHAR(40), + "NAME" VARCHAR(200), + "EMAIL" VARCHAR(100), + "CRYPTED_PASSWORD" VARCHAR(100), + "SALT" VARCHAR(40), + "HASH_METHOD" VARCHAR(10), + "ACTIVE" BOOLEAN DEFAULT TRUE, + "SCM_ACCOUNTS" VARCHAR(4000), + "EXTERNAL_LOGIN" VARCHAR(255) NOT NULL, + "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100) NOT NULL, + "EXTERNAL_ID" VARCHAR(255) NOT NULL, + "IS_ROOT" BOOLEAN NOT NULL, + "USER_LOCAL" BOOLEAN, + "ONBOARDED" BOOLEAN NOT NULL, + "HOMEPAGE_TYPE" VARCHAR(40), + "HOMEPAGE_PARAMETER" VARCHAR(40), + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT +); +ALTER TABLE "USERS" ADD CONSTRAINT "PK_USERS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS"("LOGIN"); +CREATE INDEX "USERS_UPDATED_AT" ON "USERS"("UPDATED_AT"); +CREATE UNIQUE INDEX "UNIQ_EXTERNAL_ID" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_ID"); +CREATE UNIQUE INDEX "UNIQ_EXTERNAL_LOGIN" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_LOGIN"); + +CREATE TABLE "FILE_SOURCES"( + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "FILE_UUID" VARCHAR(50) NOT NULL, + "LINE_HASHES" CLOB(2147483647), + "LINE_HASHES_VERSION" INTEGER, + "DATA_HASH" VARCHAR(50), + "SRC_HASH" VARCHAR(50), + "REVISION" VARCHAR(100), + "LINE_COUNT" INTEGER NOT NULL, + "BINARY_DATA" BLOB, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "UUID" VARCHAR(40) NOT NULL +); +ALTER TABLE "FILE_SOURCES" ADD CONSTRAINT "PK_FILE_SOURCES" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "FILE_SOURCES_FILE_UUID" ON "FILE_SOURCES"("FILE_UUID"); +CREATE INDEX "FILE_SOURCES_PROJECT_UUID" ON "FILE_SOURCES"("PROJECT_UUID"); +CREATE INDEX "FILE_SOURCES_UPDATED_AT" ON "FILE_SOURCES"("UPDATED_AT"); + +CREATE TABLE "CE_ACTIVITY"( + "UUID" VARCHAR(40) NOT NULL, + "TASK_TYPE" VARCHAR(15) NOT NULL, + "MAIN_COMPONENT_UUID" VARCHAR(40), + "COMPONENT_UUID" VARCHAR(40), + "STATUS" VARCHAR(15) NOT NULL, + "MAIN_IS_LAST" BOOLEAN NOT NULL, + "MAIN_IS_LAST_KEY" VARCHAR(55) NOT NULL, + "IS_LAST" BOOLEAN NOT NULL, + "IS_LAST_KEY" VARCHAR(55) NOT NULL, + "SUBMITTER_UUID" VARCHAR(255), + "SUBMITTED_AT" BIGINT NOT NULL, + "STARTED_AT" BIGINT, + "EXECUTED_AT" BIGINT, + "EXECUTION_COUNT" INTEGER NOT NULL, + "EXECUTION_TIME_MS" BIGINT, + "ANALYSIS_UUID" VARCHAR(50), + "ERROR_MESSAGE" VARCHAR(1000), + "ERROR_STACKTRACE" CLOB(2147483647), + "ERROR_TYPE" VARCHAR(20), + "WORKER_UUID" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "CE_ACTIVITY" ADD CONSTRAINT "PK_CE_ACTIVITY" PRIMARY KEY("UUID"); +CREATE INDEX "CE_ACTIVITY_COMPONENT" ON "CE_ACTIVITY"("COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_ISLAST" ON "CE_ACTIVITY"("IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_ISLAST_KEY" ON "CE_ACTIVITY"("IS_LAST_KEY"); +CREATE INDEX "CE_ACTIVITY_MAIN_COMPONENT" ON "CE_ACTIVITY"("MAIN_COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST" ON "CE_ACTIVITY"("MAIN_IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST_KEY" ON "CE_ACTIVITY"("MAIN_IS_LAST_KEY"); +CREATE UNIQUE INDEX "CE_ACTIVITY_UUID" ON "CE_ACTIVITY"("UUID"); + +CREATE TABLE "EVENTS"( + "UUID" VARCHAR(40) NOT NULL, + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "NAME" VARCHAR(400), + "CATEGORY" VARCHAR(50), + "DESCRIPTION" VARCHAR(4000), + "EVENT_DATA" VARCHAR(4000), + "EVENT_DATE" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "COMPONENT_UUID" VARCHAR(50) NOT NULL +); +ALTER TABLE "EVENTS" ADD CONSTRAINT "PK_EVENTS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "EVENTS_UUID" ON "EVENTS"("UUID"); +CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS"("ANALYSIS_UUID"); +CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS"("COMPONENT_UUID"); -- 2.39.5