From bb2e6f6d0c15b259fb91c333da51bb1f78e85cd7 Mon Sep 17 00:00:00 2001 From: Benjamin Campomenosi Date: Mon, 19 Sep 2022 17:23:33 +0200 Subject: SONAR-17328 add unique index to uuid in components --- server/sonar-db-dao/src/schema/schema-sq.ddl | 2 +- .../v96/CreateUniqueIndexForComponentsUuid.java | 60 ++++++++++++++++++++++ .../db/migration/version/v96/DbVersion96.java | 5 +- .../v96/DropNonUniqueIndexForComponentsUuid.java | 37 +++++++++++++ .../CreateUniqueIndexForComponentsUuidTest.java | 58 +++++++++++++++++++++ .../DropNonUniqueIndexForComponentsUuidTest.java | 58 +++++++++++++++++++++ .../schema.sql | 40 +++++++++++++++ .../schema.sql | 41 +++++++++++++++ 8 files changed, 299 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuid.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuid.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuidTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuidTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuidTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuidTest/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 223ef95ff6a..f161a202ff6 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -238,8 +238,8 @@ CREATE INDEX "PROJECTS_MODULE_UUID" ON "COMPONENTS"("MODULE_UUID" NULLS FIRST); CREATE INDEX "PROJECTS_PROJECT_UUID" ON "COMPONENTS"("PROJECT_UUID" NULLS FIRST); CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER" NULLS FIRST); CREATE INDEX "PROJECTS_ROOT_UUID" ON "COMPONENTS"("ROOT_UUID" NULLS FIRST); -CREATE INDEX "PROJECTS_UUID" ON "COMPONENTS"("UUID" NULLS FIRST); CREATE INDEX "IDX_MAIN_BRANCH_PRJ_UUID" ON "COMPONENTS"("MAIN_BRANCH_PROJECT_UUID" NULLS FIRST); +CREATE UNIQUE INDEX "COMPONENTS_UUID" ON "COMPONENTS"("UUID" NULLS FIRST); CREATE TABLE "DEFAULT_QPROFILES"( "LANGUAGE" CHARACTER VARYING(20) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuid.java new file mode 100644 index 00000000000..9623d6b8043 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuid.java @@ -0,0 +1,60 @@ +/* + * SonarQube + * Copyright (C) 2009-2022 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.v96; + +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; + +public class CreateUniqueIndexForComponentsUuid extends DdlChange { + + @VisibleForTesting + static final String COLUMN_NAME = "uuid"; + @VisibleForTesting + static final String TABLE = "components"; + @VisibleForTesting + static final String INDEX_NAME = "components_uuid"; + + public CreateUniqueIndexForComponentsUuid(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + createComponentsUuidUniqueIndex(context, connection); + } + } + + private static void createComponentsUuidUniqueIndex(Context context, Connection connection) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE, INDEX_NAME, connection)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE) + .setName(INDEX_NAME) + .addColumn(COLUMN_NAME) + .setUnique(true) + .build()); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DbVersion96.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DbVersion96.java index 0efdb9cb1e0..cd3e930d2db 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DbVersion96.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DbVersion96.java @@ -43,7 +43,10 @@ public class DbVersion96 implements DbVersion { .add(6512, "Add column 'language' to 'push_events'", AddLanguageColumnToPushEventsTable.class) .add(6513, "Delete duplicated rows in 'project_badge_token'", DeleteDuplicatedProjectBadgeTokens.class) .add(6514, "Add unique index on 'project_uuid' in 'project_badge_token'", CreateIndexForProjectBadgeTokens.class) - .add(6515,"Add column 'webhook_secret' to 'alm_settings'", AddWebhookSecretToAlmSettingsTable.class) + .add(6515, "Add column 'webhook_secret' to 'alm_settings'", AddWebhookSecretToAlmSettingsTable.class) + .add(6516, "Drop non unique index on 'uuid' in 'components'", DropNonUniqueIndexForComponentsUuid.class) + .add(6517, "Add unique index on 'uuid' in 'components'", CreateUniqueIndexForComponentsUuid.class) + ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuid.java new file mode 100644 index 00000000000..d29ec35f79f --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuid.java @@ -0,0 +1,37 @@ +/* + * SonarQube + * Copyright (C) 2009-2022 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.v96; + +import com.google.common.annotations.VisibleForTesting; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DropIndexChange; + +public class DropNonUniqueIndexForComponentsUuid extends DropIndexChange { + + @VisibleForTesting + static final String TABLE = "components"; + @VisibleForTesting + static final String INDEX_NAME = "projects_uuid"; + + public DropNonUniqueIndexForComponentsUuid(Database db) { + super(db, INDEX_NAME, TABLE); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuidTest.java new file mode 100644 index 00000000000..9a587cb3313 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuidTest.java @@ -0,0 +1,58 @@ +/* + * SonarQube + * Copyright (C) 2009-2022 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.v96; + +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.v96.CreateUniqueIndexForComponentsUuid.COLUMN_NAME; +import static org.sonar.server.platform.db.migration.version.v96.CreateUniqueIndexForComponentsUuid.INDEX_NAME; +import static org.sonar.server.platform.db.migration.version.v96.CreateUniqueIndexForComponentsUuid.TABLE; + +public class CreateUniqueIndexForComponentsUuidTest { + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateUniqueIndexForComponentsUuidTest.class, "schema.sql"); + + private final CreateUniqueIndexForComponentsUuid createUniqueIndexForComponentsUuid = new CreateUniqueIndexForComponentsUuid(db.database()); + + @Test + public void migration_should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE, INDEX_NAME); + + createUniqueIndexForComponentsUuid.execute(); + + db.assertUniqueIndex(TABLE, INDEX_NAME, COLUMN_NAME); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertIndexDoesNotExist(TABLE, INDEX_NAME); + + createUniqueIndexForComponentsUuid.execute(); + createUniqueIndexForComponentsUuid.execute(); + + db.assertUniqueIndex(TABLE, INDEX_NAME, COLUMN_NAME); + } + + +} \ No newline at end of file diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuidTest.java new file mode 100644 index 00000000000..bbc326c95c6 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuidTest.java @@ -0,0 +1,58 @@ +/* + * SonarQube + * Copyright (C) 2009-2022 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.v96; + +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.v96.DropNonUniqueIndexForComponentsUuid.INDEX_NAME; +import static org.sonar.server.platform.db.migration.version.v96.DropNonUniqueIndexForComponentsUuid.TABLE; + +public class DropNonUniqueIndexForComponentsUuidTest { + + private static final String COLUMN_NAME = "uuid"; + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(DropNonUniqueIndexForComponentsUuidTest.class, "schema.sql"); + + private final DropNonUniqueIndexForComponentsUuid dropNonUniqueIndexForComponentsUuid = new DropNonUniqueIndexForComponentsUuid(db.database()); + + @Test + public void migration_should_drop_unique_index() throws SQLException { + db.assertIndex(TABLE, INDEX_NAME, COLUMN_NAME); + + dropNonUniqueIndexForComponentsUuid.execute(); + + db.assertIndexDoesNotExist(TABLE, INDEX_NAME); + } + + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertIndex(TABLE, INDEX_NAME, COLUMN_NAME); + + dropNonUniqueIndexForComponentsUuid.execute(); + dropNonUniqueIndexForComponentsUuid.execute(); + + db.assertIndexDoesNotExist(TABLE, INDEX_NAME); + } + +} \ No newline at end of file diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuidTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuidTest/schema.sql new file mode 100644 index 00000000000..2d6834b6b0b --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/CreateUniqueIndexForComponentsUuidTest/schema.sql @@ -0,0 +1,40 @@ +CREATE TABLE "COMPONENTS"( + "UUID" CHARACTER VARYING(50) NOT NULL, + "KEE" CHARACTER VARYING(1000), + "DEPRECATED_KEE" CHARACTER VARYING(400), + "NAME" CHARACTER VARYING(2000), + "LONG_NAME" CHARACTER VARYING(2000), + "DESCRIPTION" CHARACTER VARYING(2000), + "ENABLED" BOOLEAN DEFAULT TRUE NOT NULL, + "SCOPE" CHARACTER VARYING(3), + "QUALIFIER" CHARACTER VARYING(10), + "PRIVATE" BOOLEAN NOT NULL, + "ROOT_UUID" CHARACTER VARYING(50) NOT NULL, + "LANGUAGE" CHARACTER VARYING(20), + "COPY_COMPONENT_UUID" CHARACTER VARYING(50), + "PATH" CHARACTER VARYING(2000), + "UUID_PATH" CHARACTER VARYING(1500) NOT NULL, + "PROJECT_UUID" CHARACTER VARYING(50) NOT NULL, + "MODULE_UUID" CHARACTER VARYING(50), + "MODULE_UUID_PATH" CHARACTER VARYING(1500), + "MAIN_BRANCH_PROJECT_UUID" CHARACTER VARYING(50), + "B_CHANGED" BOOLEAN, + "B_NAME" CHARACTER VARYING(500), + "B_LONG_NAME" CHARACTER VARYING(500), + "B_DESCRIPTION" CHARACTER VARYING(2000), + "B_ENABLED" BOOLEAN, + "B_QUALIFIER" CHARACTER VARYING(10), + "B_LANGUAGE" CHARACTER VARYING(20), + "B_COPY_COMPONENT_UUID" CHARACTER VARYING(50), + "B_PATH" CHARACTER VARYING(2000), + "B_UUID_PATH" CHARACTER VARYING(1500), + "B_MODULE_UUID" CHARACTER VARYING(50), + "B_MODULE_UUID_PATH" CHARACTER VARYING(1500), + "CREATED_AT" TIMESTAMP +); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "COMPONENTS"("KEE" NULLS FIRST); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "COMPONENTS"("MODULE_UUID" NULLS FIRST); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "COMPONENTS"("PROJECT_UUID" NULLS FIRST); +CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER" NULLS FIRST); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "COMPONENTS"("ROOT_UUID" NULLS FIRST); +CREATE INDEX "IDX_MAIN_BRANCH_PRJ_UUID" ON "COMPONENTS"("MAIN_BRANCH_PROJECT_UUID" NULLS FIRST); \ No newline at end of file diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuidTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuidTest/schema.sql new file mode 100644 index 00000000000..0fa3c25db24 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/DropNonUniqueIndexForComponentsUuidTest/schema.sql @@ -0,0 +1,41 @@ +CREATE TABLE "COMPONENTS"( + "UUID" CHARACTER VARYING(50) NOT NULL, + "KEE" CHARACTER VARYING(1000), + "DEPRECATED_KEE" CHARACTER VARYING(400), + "NAME" CHARACTER VARYING(2000), + "LONG_NAME" CHARACTER VARYING(2000), + "DESCRIPTION" CHARACTER VARYING(2000), + "ENABLED" BOOLEAN DEFAULT TRUE NOT NULL, + "SCOPE" CHARACTER VARYING(3), + "QUALIFIER" CHARACTER VARYING(10), + "PRIVATE" BOOLEAN NOT NULL, + "ROOT_UUID" CHARACTER VARYING(50) NOT NULL, + "LANGUAGE" CHARACTER VARYING(20), + "COPY_COMPONENT_UUID" CHARACTER VARYING(50), + "PATH" CHARACTER VARYING(2000), + "UUID_PATH" CHARACTER VARYING(1500) NOT NULL, + "PROJECT_UUID" CHARACTER VARYING(50) NOT NULL, + "MODULE_UUID" CHARACTER VARYING(50), + "MODULE_UUID_PATH" CHARACTER VARYING(1500), + "MAIN_BRANCH_PROJECT_UUID" CHARACTER VARYING(50), + "B_CHANGED" BOOLEAN, + "B_NAME" CHARACTER VARYING(500), + "B_LONG_NAME" CHARACTER VARYING(500), + "B_DESCRIPTION" CHARACTER VARYING(2000), + "B_ENABLED" BOOLEAN, + "B_QUALIFIER" CHARACTER VARYING(10), + "B_LANGUAGE" CHARACTER VARYING(20), + "B_COPY_COMPONENT_UUID" CHARACTER VARYING(50), + "B_PATH" CHARACTER VARYING(2000), + "B_UUID_PATH" CHARACTER VARYING(1500), + "B_MODULE_UUID" CHARACTER VARYING(50), + "B_MODULE_UUID_PATH" CHARACTER VARYING(1500), + "CREATED_AT" TIMESTAMP +); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "COMPONENTS"("KEE" NULLS FIRST); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "COMPONENTS"("MODULE_UUID" NULLS FIRST); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "COMPONENTS"("PROJECT_UUID" NULLS FIRST); +CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER" NULLS FIRST); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "COMPONENTS"("ROOT_UUID" NULLS FIRST); +CREATE INDEX "PROJECTS_UUID" ON "COMPONENTS"("UUID" NULLS FIRST); +CREATE INDEX "IDX_MAIN_BRANCH_PRJ_UUID" ON "COMPONENTS"("MAIN_BRANCH_PROJECT_UUID" NULLS FIRST); \ No newline at end of file -- cgit v1.2.3