diff options
6 files changed, 364 insertions, 0 deletions
diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index c6929b10b95..bb7e4166b03 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -1165,6 +1165,7 @@ CREATE TABLE "SCA_RELEASES"( ); ALTER TABLE "SCA_RELEASES" ADD CONSTRAINT "PK_SCA_RELEASES" PRIMARY KEY("UUID"); CREATE INDEX "SCA_RELEASES_COMP_UUID_UUID" ON "SCA_RELEASES"("COMPONENT_UUID" NULLS FIRST, "UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "SCA_RELEASES_PACKAGE_URL_UNIQ" ON "SCA_RELEASES"("PACKAGE_URL" NULLS FIRST, "COMPONENT_UUID" NULLS FIRST); CREATE TABLE "SCA_VULNERABILITY_ISSUES"( "UUID" CHARACTER VARYING(40) NOT NULL, diff --git a/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateUniqueIndexOnScaReleasesIT.java b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateUniqueIndexOnScaReleasesIT.java new file mode 100644 index 00000000000..9588ff8fe88 --- /dev/null +++ b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateUniqueIndexOnScaReleasesIT.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2025 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.v202503; + +import java.sql.SQLException; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.RegisterExtension; +import org.sonar.db.MigrationDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.db.MigrationDbTester.createForMigrationStep; +import static org.sonar.server.platform.db.migration.version.v202503.CreateUniqueIndexOnScaReleases.COLUMN_NAME_COMPONENT_UUID; +import static org.sonar.server.platform.db.migration.version.v202503.CreateUniqueIndexOnScaReleases.COLUMN_NAME_PACKAGE_URL; +import static org.sonar.server.platform.db.migration.version.v202503.CreateUniqueIndexOnScaReleases.INDEX_NAME; +import static org.sonar.server.platform.db.migration.version.v202503.CreateUniqueIndexOnScaReleases.TABLE_NAME; + +class CreateUniqueIndexOnScaReleasesIT { + @RegisterExtension + public final MigrationDbTester db = createForMigrationStep(CreateUniqueIndexOnScaReleases.class); + private final DdlChange underTest = new CreateUniqueIndexOnScaReleases(db.database()); + + @Test + void execute_shouldCreateIndex() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + underTest.execute(); + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_PACKAGE_URL, COLUMN_NAME_COMPONENT_UUID); + } + + @Test + void execute_shouldBeReentrant() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + underTest.execute(); + underTest.execute(); + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_PACKAGE_URL, COLUMN_NAME_COMPONENT_UUID); + } +} diff --git a/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/MigrateRemoveDuplicateScaReleasesIT.java b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/MigrateRemoveDuplicateScaReleasesIT.java new file mode 100644 index 00000000000..0daf88d66d8 --- /dev/null +++ b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/MigrateRemoveDuplicateScaReleasesIT.java @@ -0,0 +1,138 @@ +/* + * SonarQube + * Copyright (C) 2009-2025 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.v202503; + +import java.sql.SQLException; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.RegisterExtension; +import org.sonar.db.MigrationDbTester; +import org.sonar.server.platform.db.migration.step.MigrationStep; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.sonar.db.MigrationDbTester.createForMigrationStep; + +class MigrateRemoveDuplicateScaReleasesIT { + @RegisterExtension + public final MigrationDbTester db = createForMigrationStep(MigrateRemoveDuplicateScaReleases.class); + private final MigrationStep underTest = new MigrateRemoveDuplicateScaReleases(db.database()); + + @Test + void test_removesDuplicates() throws SQLException { + // we should keep this one + insertRelease("0", "componentUuid1", "packageUrlNotDuplicated", 1L); + // we should keep these rows associated with release 0 + insertDependency("0", "scaReleaseUuid0"); + insertIssueRelease("0", "scaReleaseUuid0"); + insertIssueReleaseChange("0"); + // we should keep the first (oldest) packageUrl1 entry on componentUuid1 + insertRelease("1", "componentUuid1", "packageUrl1", 2L); + insertRelease("2", "componentUuid1", "packageUrl1", 3L); + insertRelease("3", "componentUuid1", "packageUrl1", 4L); + // we should delete these rows associated with release 3 that we delete + insertDependency("3", "scaReleaseUuid3"); + insertIssueRelease("3", "scaReleaseUuid3"); + insertIssueReleaseChange("3"); + // we should keep the first (oldest) packageUrl2 entry on componentUuid1 + insertRelease("4", "componentUuid1", "packageUrl2", 5L); + insertRelease("5", "componentUuid1", "packageUrl2", 6L); + // we should keep the first (oldest) packageUrl1 entry on componentUuid2 + insertRelease("6", "componentUuid2", "packageUrl1", 7L); + insertRelease("7", "componentUuid2", "packageUrl1", 8L); + // we should keep these rows associated with release 6 + insertDependency("6", "scaReleaseUuid6"); + insertIssueRelease("6", "scaReleaseUuid6"); + insertIssueReleaseChange("6"); + // we should delete these rows associated with release 7 that we delete + insertDependency("7", "scaReleaseUuid7"); + insertIssueRelease("7", "scaReleaseUuid7"); + insertIssueReleaseChange("7"); + + assertThat(db.countSql("select count(*) from sca_releases")).isEqualTo(8); + assertThat(db.countSql("select count(*) from sca_dependencies")).isEqualTo(4); + assertThat(db.countSql("select count(*) from sca_issues_releases")).isEqualTo(4); + assertThat(db.countSql("select count(*) from sca_issue_rels_changes")).isEqualTo(4); + underTest.execute(); + + assertThat(db.select("select uuid from sca_releases")).map(row -> row.get("uuid")) + .containsExactlyInAnyOrder("scaReleaseUuid0", "scaReleaseUuid1", "scaReleaseUuid4", "scaReleaseUuid6"); + assertThat(db.select("select uuid from sca_dependencies")).map(row -> row.get("uuid")) + .containsExactlyInAnyOrder("scaDependencyUuid0", "scaDependencyUuid6"); + assertThat(db.select("select uuid from sca_issues_releases")).map(row -> row.get("uuid")) + .containsExactlyInAnyOrder("scaIssueReleaseUuid0", "scaIssueReleaseUuid6"); + assertThat(db.select("select uuid from sca_issue_rels_changes")).map(row -> row.get("uuid")) + .containsExactlyInAnyOrder("scaIssueReleaseChangeUuid0", "scaIssueReleaseChangeUuid6"); + } + + @Test + void test_canRunMultipleTimesOnEmptyTable() throws SQLException { + assertThat(db.countSql("select count(*) from sca_releases")).isZero(); + underTest.execute(); + underTest.execute(); + assertThat(db.countSql("select count(*) from sca_releases")).isZero(); + } + + private void insertRelease(String suffix, String componentUuid, String packageUrl, long createdAt) { + db.executeInsert("sca_releases", + "uuid", "scaReleaseUuid" + suffix, + "component_uuid", componentUuid, + "package_url", packageUrl, + "package_manager", "MAVEN", + "package_name", "packageName", + "version", "1.0.0", + "license_expression", "MIT", + "declared_license_expression", "MIT", + "is_new", false, + "known", true, + "known_package", true, + "updated_at", 1L, + "created_at", createdAt); + } + + private void insertDependency(String suffix, String releaseUuid) { + db.executeInsert("sca_dependencies", + "uuid", "scaDependencyUuid" + suffix, + "sca_release_uuid", releaseUuid, + "direct", true, + "scope", "compile", + "is_new", false, + "updated_at", 1L, + "created_at", 2L); + } + + private void insertIssueRelease(String suffix, String releaseUuid) { + db.executeInsert("sca_issues_releases", + "uuid", "scaIssueReleaseUuid" + suffix, + "sca_release_uuid", releaseUuid, + "sca_issue_uuid", "scaIssueUuid" + suffix, + "severity", "LOW", + "severity_sort_key", 10, + "status", "OPEN", + "updated_at", 1L, + "created_at", 2L); + } + + private void insertIssueReleaseChange(String suffix) { + db.executeInsert("sca_issue_rels_changes", + "uuid", "scaIssueReleaseChangeUuid" + suffix, + "sca_issues_releases_uuid", "scaIssueReleaseUuid" + suffix, + "updated_at", 1L, + "created_at", 2L); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateUniqueIndexOnScaReleases.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateUniqueIndexOnScaReleases.java new file mode 100644 index 00000000000..08afc724ab8 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateUniqueIndexOnScaReleases.java @@ -0,0 +1,57 @@ +/* + * SonarQube + * Copyright (C) 2009-2025 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.v202503; + +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 CreateUniqueIndexOnScaReleases extends DdlChange { + static final String TABLE_NAME = "sca_releases"; + static final String INDEX_NAME = "sca_releases_package_url_uniq"; + static final String COLUMN_NAME_PACKAGE_URL = "package_url"; + static final String COLUMN_NAME_COMPONENT_UUID = "component_uuid"; + + public CreateUniqueIndexOnScaReleases(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + createIndex(context, connection); + } + } + + private void createIndex(Context context, Connection connection) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { + context.execute(new CreateIndexBuilder(getDialect()) + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .setUnique(true) + .addColumn(COLUMN_NAME_PACKAGE_URL, false) + .addColumn(COLUMN_NAME_COMPONENT_UUID, false) + .build()); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/DbVersion202503.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/DbVersion202503.java index a66a1884bce..1d95a39afb6 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/DbVersion202503.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/DbVersion202503.java @@ -64,6 +64,8 @@ public class DbVersion202503 implements DbVersion { .add(2025_03_025, "Create SCA encountered licenses unique index", CreateUniqueIndexOnScaEncounteredLicenses.class) .add(2025_03_026, "Add change_comment to SCA issues releases changes", AddCommentToScaIssuesReleasesChangesTable.class) .add(2025_03_027, "Drop change_type from SCA issues releases changes", DropChangeTypeFromScaIssuesReleasesChangesTable.class) + .add(2025_03_028, "Remove duplicates from SCA releases table", MigrateRemoveDuplicateScaReleases.class) + .add(2025_03_029, "Create unique index on SCA releases table", CreateUniqueIndexOnScaReleases.class) ; } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/MigrateRemoveDuplicateScaReleases.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/MigrateRemoveDuplicateScaReleases.java new file mode 100644 index 00000000000..de27c26d2c7 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/MigrateRemoveDuplicateScaReleases.java @@ -0,0 +1,113 @@ +/* + * SonarQube + * Copyright (C) 2009-2025 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.v202503; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.MigrationStep; + +public class MigrateRemoveDuplicateScaReleases implements MigrationStep { + static final String SELECT_BATCH_QUERY = """ + WITH duplicate_releases AS ( + SELECT + uuid, + ROW_NUMBER() OVER ( + PARTITION BY component_uuid, package_url + ORDER BY created_at ASC + ) AS row_num + FROM sca_releases + ) + SELECT + uuid + FROM duplicate_releases + WHERE row_num > 1 + """; + + static final String DELETE_BATCH_DEPENDENCIES_QUERY = """ + DELETE FROM sca_dependencies WHERE sca_release_uuid IN (?) + """; + + static final String DELETE_BATCH_ISSUES_RELEASES_CHANGES_QUERY = """ + DELETE FROM sca_issue_rels_changes WHERE sca_issues_releases_uuid IN (SELECT uuid FROM sca_issues_releases WHERE sca_release_uuid IN (?)) + """; + + static final String DELETE_BATCH_ISSUES_RELEASES_QUERY = """ + DELETE FROM sca_issues_releases WHERE sca_release_uuid IN (?) + """; + + static final String DELETE_BATCH_RELEASES_QUERY = """ + DELETE FROM sca_releases WHERE uuid IN (?) + """; + + private final Database db; + + public MigrateRemoveDuplicateScaReleases(Database db) { + this.db = db; + } + + private static List<String> findBatchOfDuplicates(Connection connection) throws SQLException { + List<String> results = new ArrayList<>(); + + try (PreparedStatement preparedStatement = connection.prepareStatement(SELECT_BATCH_QUERY)) { + preparedStatement.setMaxRows(999); + try (ResultSet resultSet = preparedStatement.executeQuery()) { + while (resultSet.next()) { + results.add(resultSet.getString(1)); + } + } + } + + return results; + } + + private static void deleteBatch(Connection connection, String batchSql, List<String> duplicateReleaseUuids) throws SQLException { + try (PreparedStatement preparedStatement = connection.prepareStatement(batchSql)) { + for (String uuid : duplicateReleaseUuids) { + preparedStatement.setString(1, uuid); + preparedStatement.addBatch(); + } + preparedStatement.executeBatch(); + } + } + + private static void deleteBatchOfDuplicates(Connection connection, List<String> duplicateRowUuids) throws SQLException { + deleteBatch(connection, DELETE_BATCH_DEPENDENCIES_QUERY, duplicateRowUuids); + deleteBatch(connection, DELETE_BATCH_ISSUES_RELEASES_CHANGES_QUERY, duplicateRowUuids); + deleteBatch(connection, DELETE_BATCH_ISSUES_RELEASES_QUERY, duplicateRowUuids); + deleteBatch(connection, DELETE_BATCH_RELEASES_QUERY, duplicateRowUuids); + } + + @Override + public void execute() throws SQLException { + try (var connection = db.getDataSource().getConnection()) { + List<String> duplicateRowUuids = findBatchOfDuplicates(connection); + while (!duplicateRowUuids.isEmpty()) { + deleteBatchOfDuplicates(connection, duplicateRowUuids); + connection.commit(); + duplicateRowUuids = findBatchOfDuplicates(connection); + } + } + } +} |