From 41ab6192d3d537d75f37994bf9c223d365366ce1 Mon Sep 17 00:00:00 2001 From: Alain Kermis Date: Thu, 24 Aug 2023 08:35:56 +0200 Subject: [PATCH] SONAR-19324 Fix api/projects/search low performances --- .../sonar/db/component/ComponentDaoIT.java | 89 ++++++++----------- .../org/sonar/db/component/ComponentDao.java | 10 +-- .../sonar/db/component/ComponentMapper.java | 4 +- .../sonar/db/component/ComponentQuery.java | 31 ------- .../sonar/db/component/ComponentMapper.xml | 61 ++++--------- .../org/sonar/db/component/SnapshotMapper.xml | 70 +++++++++------ server/sonar-db-dao/src/schema/schema-sq.ddl | 1 + .../db/component/ComponentQueryTest.java | 6 +- ...eateIndexProjectUuidInProjectBranches.java | 33 +++++++ .../migration/version/v102/DbVersion102.java | 4 +- ...IndexProjectUuidInProjectBranchesTest.java | 49 ++++++++++ .../schema.sql | 15 ++++ .../sonar/server/ce/ws/ActivityAction.java | 2 +- .../ws/template/BulkApplyTemplateAction.java | 4 +- .../server/project/ws/BulkDeleteAction.java | 3 +- .../sonar/server/project/ws/SearchAction.java | 3 +- 16 files changed, 214 insertions(+), 171 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateIndexProjectUuidInProjectBranches.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v102/CreateIndexProjectUuidInProjectBranchesTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v102/CreateIndexProjectUuidInProjectBranchesTest/schema.sql diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/component/ComponentDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/component/ComponentDaoIT.java index 2299f88c60f..c3d4d5097cd 100644 --- a/server/sonar-db-dao/src/it/java/org/sonar/db/component/ComponentDaoIT.java +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/component/ComponentDaoIT.java @@ -44,6 +44,7 @@ import org.sonar.api.resources.Scopes; import org.sonar.api.utils.System2; import org.sonar.db.DbSession; import org.sonar.db.DbTester; +import org.sonar.db.Pagination; import org.sonar.db.RowNotFoundException; import org.sonar.db.audit.AuditPersister; import org.sonar.db.audit.NoOpAuditPersister; @@ -79,6 +80,7 @@ import static org.sonar.api.resources.Qualifiers.PROJECT; import static org.sonar.api.resources.Qualifiers.SUBVIEW; import static org.sonar.api.resources.Qualifiers.VIEW; import static org.sonar.api.utils.DateUtils.parseDate; +import static org.sonar.db.Pagination.forPage; import static org.sonar.db.component.BranchDto.DEFAULT_MAIN_BRANCH_NAME; import static org.sonar.db.component.BranchType.BRANCH; import static org.sonar.db.component.BranchType.PULL_REQUEST; @@ -931,35 +933,35 @@ public class ComponentDaoIT { SnapshotDto analyzedPortfolio = db.components().insertProjectAndSnapshot(ComponentTesting.newPortfolio()); Supplier query = () -> ComponentQuery.builder().setQualifiers(PROJECT).setOnProvisionedOnly(true); - assertThat(underTest.selectByQuery(dbSession, query.get().build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().build(), forPage(1).andSize(10))) .extracting(ComponentDto::uuid) .containsOnly(provisionedProject.uuid()); // pagination - assertThat(underTest.selectByQuery(dbSession, query.get().build(), 2, 10)).isEmpty(); + assertThat(underTest.selectByQuery(dbSession, query.get().build(), forPage(3).andSize(10))).isEmpty(); // filter on qualifiers - assertThat(underTest.selectByQuery(dbSession, query.get().setQualifiers("XXX").build(), 0, 10)).isEmpty(); - assertThat(underTest.selectByQuery(dbSession, query.get().setQualifiers(PROJECT, "XXX").build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().setQualifiers("XXX").build(), forPage(1).andSize(10))).isEmpty(); + assertThat(underTest.selectByQuery(dbSession, query.get().setQualifiers(PROJECT, "XXX").build(), forPage(1).andSize(10))) .extracting(ComponentDto::uuid) .containsOnly(provisionedProject.uuid()); - assertThat(underTest.selectByQuery(dbSession, query.get().setQualifiers(PROJECT, Qualifiers.VIEW).build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().setQualifiers(PROJECT, Qualifiers.VIEW).build(), forPage(1).andSize(10))) .extracting(ComponentDto::uuid) .containsOnly(provisionedProject.uuid(), provisionedPortfolio.uuid()); // match key - assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery(provisionedProject.getKey()).build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery(provisionedProject.getKey()).build(), forPage(1).andSize(10))) .extracting(ComponentDto::uuid) .containsExactly(provisionedProject.uuid()); - assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery("pROvisiONed.proJEcT").setPartialMatchOnKey(true).build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery("pROvisiONed.proJEcT").setPartialMatchOnKey(true).build(), forPage(1).andSize(10))) .extracting(ComponentDto::uuid) .containsExactly(provisionedProject.uuid()); - assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery("missing").setPartialMatchOnKey(true).build(), 0, 10)).isEmpty(); - assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery("to be escaped '\"\\%").setPartialMatchOnKey(true).build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery("missing").setPartialMatchOnKey(true).build(), forPage(1).andSize(10))).isEmpty(); + assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery("to be escaped '\"\\%").setPartialMatchOnKey(true).build(), forPage(1).andSize(10))) .isEmpty(); // match name - assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery("ned proj").setPartialMatchOnKey(true).build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().setNameOrKeyQuery("ned proj").setPartialMatchOnKey(true).build(), forPage(1).andSize(10))) .extracting(ComponentDto::uuid) .containsExactly(provisionedProject.uuid()); } @@ -972,7 +974,7 @@ public class ComponentDaoIT { // the project does not have any analysis ComponentDto project = db.components().insertPublicProject().getMainBranchComponent(); - assertThat(underTest.selectByQuery(dbSession, query.get().build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().build(), forPage(1).andSize(10))) .extracting(ComponentDto::uuid) .containsOnly(project.uuid()); @@ -981,7 +983,7 @@ public class ComponentDaoIT { ComponentDto branchWithoutAnalysis = db.components().insertProjectBranch(project); ComponentDto branchWithAnalysis = db.components().insertProjectBranch(project); db.components().insertSnapshot(branchWithAnalysis); - assertThat(underTest.selectByQuery(dbSession, query.get().build(), 0, 10)) + assertThat(underTest.selectByQuery(dbSession, query.get().build(), forPage(1).andSize(10))) .isEmpty(); } @@ -999,7 +1001,7 @@ public class ComponentDaoIT { .setQualifiers(PROJECT) .setOnProvisionedOnly(true); - List results = underTest.selectByQuery(dbSession, query.get().build(), 0, 10); + List results = underTest.selectByQuery(dbSession, query.get().build(), forPage(1).andSize(10)); assertThat(results) .extracting(ComponentDto::uuid) .containsExactly( @@ -1172,7 +1174,8 @@ public class ComponentDaoIT { private void assertThatSelectByQueryThrowsIAE(ComponentQuery.Builder query, String expectedMessage) { ComponentQuery componentQuery = query.build(); - assertThatThrownBy(() -> underTest.selectByQuery(dbSession, componentQuery, 0, Integer.MAX_VALUE)) + Pagination pagination = forPage(1).andSize(Integer.MAX_VALUE); + assertThatThrownBy(() -> underTest.selectByQuery(dbSession, componentQuery, pagination)) .isInstanceOf(IllegalArgumentException.class) .hasMessage(expectedMessage); } @@ -1186,12 +1189,12 @@ public class ComponentDaoIT { } ComponentQuery query = ComponentQuery.builder().setNameOrKeyQuery("oJect").setQualifiers(PROJECT).build(); - List result = underTest.selectByQuery(dbSession, query, 1, 3); + List result = underTest.selectByQuery(dbSession, query, forPage(2).andSize(3)); int count = underTest.countByQuery(dbSession, query); assertThat(result).hasSize(3); assertThat(count).isEqualTo(9); - assertThat(result).extracting(ComponentDto::name).containsExactly("project-2", "project-3", "project-4"); + assertThat(result).extracting(ComponentDto::name).containsExactly("project-4", "project-5", "project-6"); } @Test @@ -1199,8 +1202,8 @@ public class ComponentDaoIT { ComponentDto main = db.components().insertPublicProject().getMainBranchComponent(); ComponentDto branch = db.components().insertProjectBranch(main); - assertThat(underTest.selectByQuery(dbSession, ALL_PROJECTS_COMPONENT_QUERY, 0, 2)).hasSize(1); - assertThat(underTest.selectByQuery(dbSession, ALL_PROJECTS_COMPONENT_QUERY, 0, 2).get(0).uuid()).isEqualTo(main.uuid()); + assertThat(underTest.selectByQuery(dbSession, ALL_PROJECTS_COMPONENT_QUERY, forPage(1).andSize(2))).hasSize(1); + assertThat(underTest.selectByQuery(dbSession, ALL_PROJECTS_COMPONENT_QUERY, forPage(1).andSize(2)).get(0).uuid()).isEqualTo(main.uuid()); } @Test @@ -1216,7 +1219,7 @@ public class ComponentDaoIT { db.components().insertProjectAndSnapshot(newPrivateProjectDto().setName("project-\\_%/-name")); ComponentQuery query = ComponentQuery.builder().setNameOrKeyQuery("-\\_%/-").setQualifiers(PROJECT).build(); - List result = underTest.selectByQuery(dbSession, query, 0, 10); + List result = underTest.selectByQuery(dbSession, query, forPage(1).andSize(10)); assertThat(result).hasSize(1); assertThat(result.get(0).name()).isEqualTo("project-\\_%/-name"); @@ -1228,7 +1231,7 @@ public class ComponentDaoIT { db.components().insertProjectAndSnapshot(newPrivateProjectDto().setKey("project-key-that-does-not-match")); ComponentQuery query = ComponentQuery.builder().setNameOrKeyQuery("project-_%-key").setQualifiers(PROJECT).build(); - List result = underTest.selectByQuery(dbSession, query, 0, 10); + List result = underTest.selectByQuery(dbSession, query, forPage(1).andSize(10)); assertThat(result).hasSize(1); assertThat(result.get(0).getKey()).isEqualTo("project-_%-key"); @@ -1242,7 +1245,7 @@ public class ComponentDaoIT { .setNameOrKeyQuery("JECT-K") .setPartialMatchOnKey(true) .setQualifiers(PROJECT).build(); - List result = underTest.selectByQuery(dbSession, query, 0, 10); + List result = underTest.selectByQuery(dbSession, query, forPage(1).andSize(10)); assertThat(result).hasSize(1); assertThat(result.get(0).getKey()).isEqualTo("project-key"); @@ -1267,19 +1270,11 @@ public class ComponentDaoIT { .containsExactlyInAnyOrder(oldProject.uuid(), recentProject.uuid()); // before date on any branch - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedBefore(recentTime))) + assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(recentTime))) .containsExactlyInAnyOrder(oldProject.uuid()); - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedBefore(aLongTimeAgo))) + assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(aLongTimeAgo))) .isEmpty(); - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedBefore(recentTime + 1_000L))) - .containsExactlyInAnyOrder(oldProject.uuid(), recentProject.uuid()); - - // after date - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedAfter(recentTime - 1_000L))) - .containsExactlyInAnyOrder(recentProject.uuid()); - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedAfter(recentTime + 1_000L))) - .isEmpty(); - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedAfter(aLongTimeAgo))) + assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(recentTime + 1_000L))) .containsExactlyInAnyOrder(oldProject.uuid(), recentProject.uuid()); } @@ -1306,19 +1301,11 @@ public class ComponentDaoIT { assertThat(selectProjectUuidsByQuery(q -> q.setAnalyzedBefore(recentTime + 1_000L))).isEmpty(); // before date on any branch - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedBefore(recentTime))) + assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(recentTime))) .containsExactlyInAnyOrder(oldProject.uuid()); - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedBefore(aLongTimeAgo))) - .isEmpty(); - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedBefore(recentTime + 1_000L))) - .containsExactlyInAnyOrder(oldProject.uuid(), recentProject.uuid()); - - // after date - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedAfter(recentTime - 1_000L))) - .containsExactlyInAnyOrder(recentProject.uuid()); - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedAfter(recentTime + 1_000L))) + assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(aLongTimeAgo))) .isEmpty(); - assertThat(selectProjectUuidsByQuery(q -> q.setAnyBranchAnalyzedAfter(aLongTimeAgo))) + assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(recentTime + 1_000L))) .containsExactlyInAnyOrder(oldProject.uuid(), recentProject.uuid()); } @@ -1387,7 +1374,7 @@ public class ComponentDaoIT { private List selectUuidsByQuery(String qualifier, Consumer query) { ComponentQuery.Builder builder = ComponentQuery.builder().setQualifiers(qualifier); query.accept(builder); - return underTest.selectByQuery(dbSession, builder.build(), 0, 5) + return underTest.selectByQuery(dbSession, builder.build(), forPage(1).andSize(5)) .stream() .map(ComponentDto::uuid) .toList(); @@ -1402,9 +1389,9 @@ public class ComponentDaoIT { ComponentQuery publicProjectsQuery = ComponentQuery.builder().setPrivate(false).setQualifiers(PROJECT).build(); ComponentQuery allProjectsQuery = ComponentQuery.builder().setPrivate(null).setQualifiers(PROJECT).build(); - assertThat(underTest.selectByQuery(dbSession, privateProjectsQuery, 0, 10)).extracting(ComponentDto::getKey).containsExactly("private-key"); - assertThat(underTest.selectByQuery(dbSession, publicProjectsQuery, 0, 10)).extracting(ComponentDto::getKey).containsExactly("public-key"); - assertThat(underTest.selectByQuery(dbSession, allProjectsQuery, 0, 10)).extracting(ComponentDto::getKey).containsOnly("public-key", "private-key"); + assertThat(underTest.selectByQuery(dbSession, privateProjectsQuery, forPage(1).andSize(10))).extracting(ComponentDto::getKey).containsExactly("private-key"); + assertThat(underTest.selectByQuery(dbSession, publicProjectsQuery, forPage(1).andSize(10))).extracting(ComponentDto::getKey).containsExactly("public-key"); + assertThat(underTest.selectByQuery(dbSession, allProjectsQuery, forPage(1).andSize(10))).extracting(ComponentDto::getKey).containsOnly("public-key", "private-key"); } @Test @@ -1412,7 +1399,7 @@ public class ComponentDaoIT { db.components().insertPrivateProject().getMainBranchComponent(); ComponentQuery dbQuery = ComponentQuery.builder().setQualifiers(PROJECT).setComponentKeys(emptySet()).build(); - List result = underTest.selectByQuery(dbSession, dbQuery, 0, 10); + List result = underTest.selectByQuery(dbSession, dbQuery, forPage(1).andSize(10)); int count = underTest.countByQuery(dbSession, dbQuery); assertThat(result).isEmpty(); @@ -1427,7 +1414,7 @@ public class ComponentDaoIT { ComponentQuery query = ComponentQuery.builder().setQualifiers(PROJECT) .setComponentKeys(newHashSet(sonarqube.getKey(), jdk8.getKey())).build(); - List result = underTest.selectByQuery(dbSession, query, 0, 10); + List result = underTest.selectByQuery(dbSession, query, forPage(1).andSize(10)); assertThat(result).hasSize(2).extracting(ComponentDto::getKey) .containsExactlyInAnyOrder(sonarqube.getKey(), jdk8.getKey()) @@ -1439,7 +1426,7 @@ public class ComponentDaoIT { db.components().insertPrivateProject().getMainBranchComponent(); ComponentQuery dbQuery = ComponentQuery.builder().setQualifiers(PROJECT).setComponentUuids(emptySet()).build(); - List result = underTest.selectByQuery(dbSession, dbQuery, 0, 10); + List result = underTest.selectByQuery(dbSession, dbQuery, forPage(1).andSize(10)); int count = underTest.countByQuery(dbSession, dbQuery); assertThat(result).isEmpty(); @@ -1454,7 +1441,7 @@ public class ComponentDaoIT { ComponentQuery query = ComponentQuery.builder().setQualifiers(PROJECT) .setComponentUuids(newHashSet(sonarqube.uuid(), jdk8.uuid())).build(); - List result = underTest.selectByQuery(dbSession, query, 0, 10); + List result = underTest.selectByQuery(dbSession, query, forPage(1).andSize(10)); assertThat(result).hasSize(2).extracting(ComponentDto::uuid) .containsOnlyOnce(sonarqube.uuid(), jdk8.uuid()) diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentDao.java index a78e4545a75..0d64637e9e4 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentDao.java @@ -31,10 +31,10 @@ import java.util.stream.Collectors; import java.util.stream.Stream; import javax.annotation.Nullable; import org.apache.ibatis.session.ResultHandler; -import org.apache.ibatis.session.RowBounds; import org.sonar.api.resources.Qualifiers; import org.sonar.db.Dao; import org.sonar.db.DbSession; +import org.sonar.db.Pagination; import org.sonar.db.RowNotFoundException; import org.sonar.db.audit.AuditPersister; import org.sonar.db.audit.model.ComponentNewValue; @@ -104,8 +104,8 @@ public class ComponentDao implements Dao { * @throws IllegalArgumentException if parameter query#getComponentKeys() has more than {@link org.sonar.db.DatabaseUtils#PARTITION_SIZE_FOR_ORACLE} values * @throws IllegalArgumentException if parameter query#getMainComponentUuids() has more than {@link org.sonar.db.DatabaseUtils#PARTITION_SIZE_FOR_ORACLE} values */ - public List selectByQuery(DbSession dbSession, ComponentQuery query, int offset, int limit) { - return selectByQueryImpl(dbSession, query, offset, limit); + public List selectByQuery(DbSession dbSession, ComponentQuery query, Pagination pagination) { + return selectByQueryImpl(dbSession, query, pagination); } /** @@ -117,12 +117,12 @@ public class ComponentDao implements Dao { return countByQueryImpl(session, query); } - private static List selectByQueryImpl(DbSession session, ComponentQuery query, int offset, int limit) { + private static List selectByQueryImpl(DbSession session, ComponentQuery query, Pagination pagination) { if (query.hasEmptySetOfComponents()) { return emptyList(); } checkThatNotTooManyComponents(query); - return mapper(session).selectByQuery(query, new RowBounds(offset, limit)); + return mapper(session).selectByQuery(query, pagination); } private static int countByQueryImpl(DbSession session, ComponentQuery query) { diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentMapper.java index 25424e75349..e09c48a58f9 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentMapper.java @@ -26,7 +26,7 @@ import javax.annotation.CheckForNull; import javax.annotation.Nullable; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.session.ResultHandler; -import org.apache.ibatis.session.RowBounds; +import org.sonar.db.Pagination; public interface ComponentMapper { @CheckForNull @@ -54,7 +54,7 @@ public interface ComponentMapper { List selectComponentsByQualifiers(@Param("qualifiers") Collection qualifiers); - List selectByQuery(@Param("query") ComponentQuery query, RowBounds rowBounds); + List selectByQuery(@Param("query") ComponentQuery query, @Param("pagination") Pagination pagination); int countByQuery(@Param("query") ComponentQuery query); diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentQuery.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentQuery.java index 7baf5d3c409..507cd691700 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentQuery.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentQuery.java @@ -38,8 +38,6 @@ public class ComponentQuery { private final Set componentUuids; private final Set componentKeys; private final Long analyzedBefore; - private final Long anyBranchAnalyzedBefore; - private final Long anyBranchAnalyzedAfter; private final Long allBranchesAnalyzedBefore; private final Date createdAfter; private final boolean onProvisionedOnly; @@ -52,8 +50,6 @@ public class ComponentQuery { this.componentKeys = builder.componentKeys; this.isPrivate = builder.isPrivate; this.analyzedBefore = builder.analyzedBefore; - this.anyBranchAnalyzedBefore = builder.anyBranchAnalyzedBefore; - this.anyBranchAnalyzedAfter = builder.anyBranchAnalyzedAfter; this.allBranchesAnalyzedBefore = builder.allBranchesAnalyzedBefore; this.createdAfter = builder.createdAfter; this.onProvisionedOnly = builder.onProvisionedOnly; @@ -103,16 +99,6 @@ public class ComponentQuery { return analyzedBefore; } - @CheckForNull - public Long getAnyBranchAnalyzedBefore() { - return anyBranchAnalyzedBefore; - } - - @CheckForNull - public Long getAnyBranchAnalyzedAfter() { - return anyBranchAnalyzedAfter; - } - @CheckForNull public Long getAllBranchesAnalyzedBefore() { return allBranchesAnalyzedBefore; @@ -144,8 +130,6 @@ public class ComponentQuery { private Set componentUuids; private Set componentKeys; private Long analyzedBefore; - private Long anyBranchAnalyzedBefore; - private Long anyBranchAnalyzedAfter; private Long allBranchesAnalyzedBefore; private Date createdAfter; private boolean onProvisionedOnly = false; @@ -193,21 +177,6 @@ public class ComponentQuery { return this; } - /** - * Filter on date of last analysis. On projects, all branches and pull requests are taken into - * account. For example the analysis of a branch is included in the filter - * even if the main branch has never been analyzed. - */ - public Builder setAnyBranchAnalyzedAfter(@Nullable Long l) { - this.anyBranchAnalyzedAfter = l; - return this; - } - - public Builder setAnyBranchAnalyzedBefore(@Nullable Long l) { - this.anyBranchAnalyzedBefore = l; - return this; - } - public Builder setCreatedAfter(@Nullable Date l) { this.createdAfter = l; return this; diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml index e55f64f3fde..6dffe0b6848 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml @@ -210,19 +210,34 @@ + + WITH main_branch_and_portfolios AS (SELECT p.uuid as projectUuid, p.kee as kee, p.uuid + from portfolios p + UNION + SELECT pb.project_uuid as projectUuid, p.kee as kee, pb.uuid + FROM project_branches pb + INNER JOIN projects p on pb.project_uuid = p.uuid + where pb.is_main = ${_true} + ) + + - from components p + from main_branch_and_portfolios mbp + INNER JOIN components p on mbp.kee = p.kee and p.branch_uuid = mbp.uuid inner join snapshots sa on sa.root_component_uuid=p.uuid and sa.status='P' and sa.islast=${_true} and sa.created_at < #{query.analyzedBefore,jdbcType=BIGINT} @@ -230,8 +245,6 @@ left join project_branches pb on pb.uuid = p.branch_uuid where p.enabled=${_true} - AND - AND p.copy_component_uuid is null and p.qualifier in @@ -280,48 +293,6 @@ where pb2.project_uuid = pb.project_uuid ) - - and ( - exists( - -- branches of projects and applications - select 1 from snapshots s - inner join project_branches pb2 on s.root_component_uuid = pb2.uuid - where pb2.project_uuid = pb.project_uuid - and s.status='P' - and s.islast = ${_true} - and s.created_at >= #{query.anyBranchAnalyzedAfter,jdbcType=BIGINT} - ) - or exists ( - -- portfolios - select 1 from snapshots s - where s.root_component_uuid = p.uuid - and s.status='P' - and s.islast = ${_true} - and s.created_at >= #{query.anyBranchAnalyzedAfter,jdbcType=BIGINT} - ) - ) - - - and ( - exists( - -- branches of projects and applications - select 1 from snapshots s - inner join project_branches pb2 on s.root_component_uuid = pb2.uuid - where pb2.project_uuid = pb.project_uuid - and s.status='P' - and s.islast = ${_true} - and s.created_at < #{query.anyBranchAnalyzedBefore,jdbcType=BIGINT} - ) - or exists ( - -- portfolios - select 1 from snapshots s - where s.root_component_uuid = p.uuid - and s.status='P' - and s.islast = ${_true} - and s.created_at < #{query.anyBranchAnalyzedBefore,jdbcType=BIGINT} - ) - ) - and ( diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/SnapshotMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/SnapshotMapper.xml index 259d9da533a..5a2356a79ab 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/SnapshotMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/SnapshotMapper.xml @@ -54,38 +54,54 @@ and coalesce(pb.project_uuid, p.branch_uuid) = #{projectUuid,jdbcType=VARCHAR} - select - result_with_duplicates.project_uuid as project_uuid , + result_with_duplicates.project_uuid as project_uuid, max(result_with_duplicates.last_analysis_date) as last_analysis_date - from + FROM ( - select - coalesce(pb.project_uuid, c.branch_uuid) as project_uuid, - s.created_at as last_analysis_date - from - snapshots s - inner join components c on s.root_component_uuid = c.branch_uuid - left join project_branches pb on pb.uuid = c.branch_uuid - where - s.islast = ${_true} - and ( - - (pb.uuid is not null and pb.project_uuid in - - #{projectUuid,jdbcType=VARCHAR} - ) - or - - (pb.uuid is null and c.branch_uuid in - - #{projectUuid,jdbcType=VARCHAR} - ) - ) + + SELECT project_uuid, last_analysis_date + FROM ( + SELECT + COALESCE(pb.project_uuid, c.branch_uuid) AS project_uuid, + s.created_at AS last_analysis_date + FROM snapshots s + INNER JOIN components c ON s.root_component_uuid = c.branch_uuid + LEFT JOIN project_branches pb ON pb.uuid = c.branch_uuid + WHERE + s.islast = ${_true} + AND pb.uuid IS NOT NULL + AND pb.project_uuid IN + + #{projectUuid,jdbcType=VARCHAR} + + ) project_case + + UNION + + + SELECT project_uuid, last_analysis_date + FROM ( + SELECT + COALESCE(pb.project_uuid, c.branch_uuid) AS project_uuid, + s.created_at AS last_analysis_date + FROM snapshots s + INNER JOIN components c ON s.root_component_uuid = c.branch_uuid + LEFT JOIN project_branches pb ON pb.uuid = c.branch_uuid + WHERE + s.islast = ${_true} + AND pb.branch_type IS NULL + AND pb.uuid IS NULL + AND c.branch_uuid IN + + #{projectUuid,jdbcType=VARCHAR} + + ) portfolio_case ) result_with_duplicates - group by - result_with_duplicates.project_uuid + GROUP BY project_uuid +