From a1724a62333f603eb09f129b8b7d865c99c3a616 Mon Sep 17 00:00:00 2001 From: Pierre Date: Fri, 3 Sep 2021 14:16:30 +0200 Subject: [PATCH] SONAR-15334 - Project Export Findings endpoint --- .../java/org/sonar/db/issue/IssueDao.java | 8 +- .../java/org/sonar/db/issue/IssueDto.java | 10 +++ .../java/org/sonar/db/issue/IssueMapper.java | 4 + .../org/sonar/db/issue/IssueMapper.xml | 78 +++++++++++++++++++ server/sonar-db-dao/src/schema/schema-sq.ddl | 1 + .../java/org/sonar/db/issue/IssueDaoTest.java | 11 +++ ...orIssueChangesOnIssueKeyAndChangeType.java | 51 ++++++++++++ .../db/migration/version/v91/DbVersion91.java | 1 + ...sueChangesOnIssueKeyAndChangeTypeTest.java | 53 +++++++++++++ .../version/v91/DbVersion91Test.java | 2 +- .../schema.sql | 16 ++++ 11 files changed, 233 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeType.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest/schema.sql diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueDao.java index 9d3438a5e16..ac729b3bb04 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueDao.java @@ -25,6 +25,7 @@ import java.util.Optional; import java.util.Set; import org.sonar.db.Dao; import org.sonar.db.DbSession; +import org.sonar.db.Pagination; import org.sonar.db.RowNotFoundException; import org.sonar.db.WildcardPosition; import org.sonar.db.component.ComponentDto; @@ -33,6 +34,7 @@ import static org.sonar.db.DaoUtils.buildLikeValue; import static org.sonar.db.DatabaseUtils.executeLargeInputs; public class IssueDao implements Dao { + public static final int DEFAULT_PAGE_SIZE = 1000; public Optional selectByKey(DbSession session, String key) { return Optional.ofNullable(mapper(session).selectByKey(key)); @@ -40,7 +42,7 @@ public class IssueDao implements Dao { public IssueDto selectOrFailByKey(DbSession session, String key) { Optional issue = selectByKey(session, key); - if (!issue.isPresent()) { + if (issue.isEmpty()) { throw new RowNotFoundException(String.format("Issue with key '%s' does not exist", key)); } return issue.get(); @@ -56,6 +58,10 @@ public class IssueDao implements Dao { return executeLargeInputs(keys, mapper(session)::selectByKeys); } + public List selectByComponentUuidPaginated(DbSession session, String componentUuid, int page) { + return mapper(session).selectByComponentUuidPaginated(componentUuid, Pagination.forPage(page).andSize(DEFAULT_PAGE_SIZE)); + } + public Set selectComponentUuidsOfOpenIssuesForProjectUuid(DbSession session, String projectUuid) { return mapper(session).selectComponentUuidsOfOpenIssuesForProjectUuid(projectUuid); } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueDto.java index b8c22cd31b2..a949553a8c1 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueDto.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueDto.java @@ -71,6 +71,7 @@ public final class IssueDto implements Serializable { private String assigneeUuid; private String authorLogin; private String issueAttributes; + private String securityStandards; private byte[] locations; private long createdAt; private long updatedAt; @@ -357,6 +358,15 @@ public final class IssueDto implements Serializable { return this; } + public IssueDto setSecurityStandards(@Nullable String s) { + this.securityStandards = s; + return this; + } + + public Set getSecurityStandards() { + return RuleDefinitionDto.deserializeSecurityStandardsString(securityStandards); + } + /** * Technical date */ diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueMapper.java index 48e6805c494..9fa98e1f391 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/issue/IssueMapper.java @@ -24,6 +24,7 @@ import java.util.List; import java.util.Set; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.session.ResultHandler; +import org.sonar.db.Pagination; import org.sonar.db.component.ComponentDto; public interface IssueMapper { @@ -36,6 +37,9 @@ public interface IssueMapper { List selectByKeys(List keys); + List selectByComponentUuidPaginated(@Param("componentUuid") String componentUuid, + @Param("pagination") Pagination pagination); + List selectByKeysIfNotUpdatedAt(@Param("keys") List keys, @Param("updatedAt") long updatedAt); List selectOpenByComponentUuids(List componentUuids); diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/issue/IssueMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/issue/IssueMapper.xml index ac50941c927..ff5dc7f5735 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/issue/IssueMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/issue/IssueMapper.xml @@ -30,6 +30,7 @@ r.plugin_rule_key as ruleKey, r.plugin_name as ruleRepo, r.language as language, + r.security_standards as securityStandards, p.kee as componentKey, i.component_uuid as componentUuid, p.module_uuid as moduleUuid, @@ -40,6 +41,33 @@ i.issue_type as type + + i.kee, + i.rule_uuid, + i.severity, + i.manual_severity, + i.message, + i.line, + i.locations, + i.gap, + i.effort, + i.status, + i.resolution, + i.checksum, + i.assignee, + i.author_login, + i.tags, + i.issue_attributes, + i.issue_creation_date, + i.issue_update_date, + i.issue_close_date, + i.created_at, + i.updated_at, + i.component_uuid, + i.project_uuid, + i.issue_type + + , @@ -324,5 +352,55 @@ ) i2 group by i2.issue_type, i2.severity, i2.resolution, i2.status, i2.inLeak + + + + + + diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index a2b0728d00e..869c173f7e0 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -398,6 +398,7 @@ ALTER TABLE "ISSUE_CHANGES" ADD CONSTRAINT "PK_ISSUE_CHANGES" PRIMARY KEY("UUID" CREATE INDEX "ISSUE_CHANGES_ISSUE_KEY" ON "ISSUE_CHANGES"("ISSUE_KEY"); CREATE INDEX "ISSUE_CHANGES_KEE" ON "ISSUE_CHANGES"("KEE"); CREATE INDEX "ISSUE_CHANGES_PROJECT_UUID" ON "ISSUE_CHANGES"("PROJECT_UUID"); +CREATE INDEX "ISSUE_CHANGES_ISSUE_KEY_TYPE" ON "ISSUE_CHANGES"("ISSUE_KEY", "CHANGE_TYPE"); CREATE TABLE "ISSUES"( "KEE" VARCHAR(50) NOT NULL, diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/issue/IssueDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/issue/IssueDaoTest.java index 1714a261613..691dea2e504 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/issue/IssueDaoTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/issue/IssueDaoTest.java @@ -128,6 +128,17 @@ public class IssueDaoTest { assertThat(issues).extracting("key").containsOnly("I1", "I2"); } + @Test + public void selectByComponentUuidPaginated() { + // contains I1 and I2 + prepareTables(); + + List issues = underTest.selectByComponentUuidPaginated(db.getSession(), PROJECT_UUID, 1); + + // results are not ordered, so do not use "containsExactly" + assertThat(issues).extracting("key").containsOnly("I1", "I2"); + } + @Test public void scrollNonClosedByComponentUuid() { RuleDefinitionDto rule = db.rules().insert(); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeType.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeType.java new file mode 100644 index 00000000000..f14a82e2fc1 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeType.java @@ -0,0 +1,51 @@ +/* + * 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.v91; + +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 CreateIndexForIssueChangesOnIssueKeyAndChangeType extends DdlChange { + private static final String TABLE_NAME = "issue_changes"; + private static final String INDEX_NAME = "issue_changes_issue_key_type"; + + public CreateIndexForIssueChangesOnIssueKeyAndChangeType(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection c = getDatabase().getDataSource().getConnection()) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, c)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn("issue_key") + .addColumn("change_type") + .setUnique(false) + .build()); + } + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91.java index f7b27125deb..d84329d82f4 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91.java @@ -43,6 +43,7 @@ public class DbVersion91 implements DbVersion { .add(6015, "Create 'portfolio_projects' table", CreatePortfolioProjectsTable.class) .add(6016, "Create unique index for 'portfolio_projects'", CreateIndexForPortfolioProjects.class) .add(6017, "Migrate portfolios to new tables", MigratePortfoliosToNewTables.class) + .add(6018, "Create index for 'issue_changes' on 'issue_key' and 'change_type'", CreateIndexForIssueChangesOnIssueKeyAndChangeType.class) ; } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest.java new file mode 100644 index 00000000000..b004a804fea --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest.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.v91; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest { + private final static String TABLE_NAME = "issue_changes"; + private final static String INDEX_NAME = "issue_changes_issue_key_type"; + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest.class, "schema.sql"); + + private final CreateIndexForIssueChangesOnIssueKeyAndChangeType underTest = new CreateIndexForIssueChangesOnIssueKeyAndChangeType(db.database()); + + @Test + public void should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + underTest.execute(); + db.assertIndex(TABLE_NAME, INDEX_NAME, "issue_key", "change_type"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + underTest.execute(); + //re-entrant + underTest.execute(); + + db.assertIndex(TABLE_NAME, INDEX_NAME, "issue_key", "change_type"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91Test.java index 23abf039d73..ab995043de2 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91Test.java @@ -41,7 +41,7 @@ public class DbVersion91Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 17); + verifyMigrationCount(underTest, 18); } } diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest/schema.sql new file mode 100644 index 00000000000..cdca6f7f350 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForIssueChangesOnIssueKeyAndChangeTypeTest/schema.sql @@ -0,0 +1,16 @@ +CREATE TABLE "ISSUE_CHANGES"( + "UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(50), + "ISSUE_KEY" VARCHAR(50) NOT NULL, + "USER_LOGIN" VARCHAR(255), + "CHANGE_TYPE" VARCHAR(20), + "CHANGE_DATA" CLOB, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "ISSUE_CHANGE_CREATION_DATE" BIGINT, + "PROJECT_UUID" VARCHAR(50) NOT NULL +); +ALTER TABLE "ISSUE_CHANGES" ADD CONSTRAINT "PK_ISSUE_CHANGES" PRIMARY KEY("UUID"); +CREATE INDEX "ISSUE_CHANGES_ISSUE_KEY" ON "ISSUE_CHANGES"("ISSUE_KEY"); +CREATE INDEX "ISSUE_CHANGES_KEE" ON "ISSUE_CHANGES"("KEE"); +CREATE INDEX "ISSUE_CHANGES_PROJECT_UUID" ON "ISSUE_CHANGES"("PROJECT_UUID"); -- 2.39.5