SONAR-7305 Improve performances of WS api/measures/search_history

7 files changed, 137 insertions, 36 deletions

diff --git a/server/sonar-server/src/main/java/org/sonar/server/measure/ws/SearchHistoryAction.java b/server/sonar-server/src/main/java/org/sonar/server/measure/ws/SearchHistoryAction.java
index 8400bef04ab..ab2d4394762 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/measure/ws/SearchHistoryAction.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/measure/ws/SearchHistoryAction.java
@@ -21,6 +21,7 @@
 package org.sonar.server.measure.ws;
 
 import com.google.common.collect.Sets;
+import java.util.Date;
 import java.util.List;
 import java.util.Set;
 import java.util.function.Function;
@@ -39,6 +40,7 @@ import org.sonar.db.component.SnapshotQuery;
 import org.sonar.db.component.SnapshotQuery.SORT_FIELD;
 import org.sonar.db.component.SnapshotQuery.SORT_ORDER;
 import org.sonar.db.measure.MeasureDto;
+import org.sonar.db.measure.PastMeasureQuery;
 import org.sonar.db.metric.MetricDto;
 import org.sonar.server.component.ComponentFinder;
 import org.sonar.server.user.UserSession;
@@ -122,7 +124,7 @@ public class SearchHistoryAction implements MeasuresWsAction {
           .setComponent(component)
           .setAnalyses(searchAnalyses(dbSession, request, component))
           .setMetrics(searchMetrics(dbSession, request));
-        return result.setMeasures(searchMeasures(dbSession, component, result.getAnalyses(), result.getMetrics()));
+        return result.setMeasures(searchMeasures(dbSession, request, result));
       }
     };
   }
@@ -133,12 +135,15 @@ public class SearchHistoryAction implements MeasuresWsAction {
     return component;
   }
 
-  private List<MeasureDto> searchMeasures(DbSession dbSession, ComponentDto component, List<SnapshotDto> analyses, List<MetricDto> metrics) {
-    return dbClient.measureDao().selectPastMeasures(
-      dbSession,
-      component.uuid(),
-      analyses.stream().map(SnapshotDto::getUuid).collect(Collectors.toList()),
-      metrics.stream().map(MetricDto::getId).collect(Collectors.toList()));
+  private List<MeasureDto> searchMeasures(DbSession dbSession, SearchHistoryRequest request, SearchHistoryResult result) {
+    Date from = parseStartingDateOrDateTime(request.getFrom());
+    Date to = parseEndingDateOrDateTime(request.getTo());
+    PastMeasureQuery dbQuery = new PastMeasureQuery(
+      result.getComponent().uuid(),
+      result.getMetrics().stream().map(MetricDto::getId).collect(Collectors.toList()),
+      from == null ? null : from.getTime(),
+      to == null ? null : (to.getTime() + 1_000L));
+    return dbClient.measureDao().selectPastMeasures(dbSession, dbQuery);
   }
 
   private List<SnapshotDto> searchAnalyses(DbSession dbSession, SearchHistoryRequest request, ComponentDto component) {
diff --git a/server/sonar-server/src/main/java/org/sonar/server/measure/ws/SearchHistoryResult.java b/server/sonar-server/src/main/java/org/sonar/server/measure/ws/SearchHistoryResult.java
index 3d23e3c4a3b..f2d3e52474f 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/measure/ws/SearchHistoryResult.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/measure/ws/SearchHistoryResult.java
@@ -25,6 +25,8 @@ import com.google.common.collect.ImmutableList;
 import com.google.common.collect.Table;
 import java.util.ArrayList;
 import java.util.List;
+import java.util.Set;
+import org.sonar.core.util.stream.Collectors;
 import org.sonar.db.component.ComponentDto;
 import org.sonar.db.component.SnapshotDto;
 import org.sonar.db.measure.MeasureDto;
@@ -35,7 +37,6 @@ import org.sonarqube.ws.client.measure.SearchHistoryRequest;
 import static java.util.Collections.emptyList;
 import static java.util.Objects.requireNonNull;
 import static org.sonar.api.utils.Paging.offset;
-import static org.sonar.core.util.stream.Collectors.toList;
 import static org.sonar.db.metric.MetricDtoFunctions.isOptimizedForBestValue;
 import static org.sonar.server.measure.ws.MetricDtoWithBestValue.isEligibleForBestValue;
 
@@ -51,8 +52,8 @@ class SearchHistoryResult {
     this.request = request;
   }
 
-  boolean hasResults() {
-    return !analyses.isEmpty();
+  public ComponentDto getComponent() {
+    return requireNonNull(component);
   }
 
   SearchHistoryResult setComponent(ComponentDto component) {
@@ -67,7 +68,8 @@ class SearchHistoryResult {
 
   SearchHistoryResult setAnalyses(List<SnapshotDto> analyses) {
     this.paging = Common.Paging.newBuilder().setPageIndex(request.getPage()).setPageSize(request.getPageSize()).setTotal(analyses.size()).build();
-    this.analyses = analyses.stream().skip(offset(request.getPage(), request.getPageSize())).limit(request.getPageSize()).collect(toList());
+    this.analyses = analyses.stream().skip(offset(request.getPage(), request.getPageSize())).limit(request.getPageSize()).collect(Collectors.toList());
+
     return this;
   }
 
@@ -85,9 +87,16 @@ class SearchHistoryResult {
   }
 
   SearchHistoryResult setMeasures(List<MeasureDto> measures) {
-    this.measures = ImmutableList.<MeasureDto>builder()
-      .addAll(measures)
-      .addAll(addBestValuesToMeasures(component, measures)).build();
+    Set<String> analysisUuids = analyses.stream().map(SnapshotDto::getUuid).collect(Collectors.toHashSet());
+    ImmutableList.Builder<MeasureDto> measuresBuilder = ImmutableList.builder();
+    List<MeasureDto> filteredMeasures = measures.stream()
+      .filter(measure -> analysisUuids.contains(measure.getAnalysisUuid()))
+      .collect(Collectors.toArrayList());
+    measuresBuilder.addAll(filteredMeasures);
+    measuresBuilder.addAll(computeBestValues(filteredMeasures));
+
+    this.measures = measuresBuilder.build();
+
     return this;
   }
 
@@ -98,7 +107,7 @@ class SearchHistoryResult {
    * <li>metric is optimized for best value</li>
    * </ul>
    */
-  private List<MeasureDto> addBestValuesToMeasures(ComponentDto component, List<MeasureDto> measures) {
+  private List<MeasureDto> computeBestValues(List<MeasureDto> measures) {
     if (!isEligibleForBestValue().test(component)) {
       return emptyList();
     }
diff --git a/sonar-db/src/main/java/org/sonar/db/measure/MeasureDao.java b/sonar-db/src/main/java/org/sonar/db/measure/MeasureDao.java
index 5539c79a73d..0727df94d04 100644
--- a/sonar-db/src/main/java/org/sonar/db/measure/MeasureDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/measure/MeasureDao.java
@@ -120,14 +120,17 @@ public class MeasureDao implements Dao {
       ids -> mapper(dbSession).selectPastMeasuresOnSingleAnalysis(componentUuid, analysisUuid, ids));
   }
 
-  public List<MeasureDto> selectPastMeasures(DbSession dbSession, String componentUuid, List<String> analysisUuids, List<Integer> metricIds) {
-    if (analysisUuids.isEmpty() || metricIds.isEmpty()) {
-      return emptyList();
-    }
-
-    return executeLargeInputs(
-      analysisUuids,
-      analyses -> mapper(dbSession).selectPastMeasuresOnSeveralAnalyses(componentUuid, analyses, metricIds));
+  /**
+   * Select measures of:
+   * - one component
+   * - for a list of metrics
+   * - with analysis from a date (inclusive) - optional
+   * - with analysis to a date (exclusive) - optional
+   *
+   * If no constraints on dates, all the history is returned
+   */
+  public List<MeasureDto> selectPastMeasures(DbSession dbSession, PastMeasureQuery query) {
+    return mapper(dbSession).selectPastMeasuresOnSeveralAnalyses(query);
   }
 
   /**
diff --git a/sonar-db/src/main/java/org/sonar/db/measure/MeasureMapper.java b/sonar-db/src/main/java/org/sonar/db/measure/MeasureMapper.java
index eb282770edc..05c548436ea 100644
--- a/sonar-db/src/main/java/org/sonar/db/measure/MeasureMapper.java
+++ b/sonar-db/src/main/java/org/sonar/db/measure/MeasureMapper.java
@@ -43,8 +43,7 @@ public interface MeasureMapper {
   List<PastMeasureDto> selectPastMeasuresOnSingleAnalysis(@Param("componentUuid") String componentUuid, @Param("analysisUuid") String analysisUuid,
     @Param("metricIds") List<Integer> metricIds);
 
-  List<MeasureDto> selectPastMeasuresOnSeveralAnalyses(@Param("componentUuid") String componentUuid, @Param("analysisUuids") Collection<String> analysisUuid,
-    @Param("metricIds") Collection<Integer> metricIds);
+  List<MeasureDto> selectPastMeasuresOnSeveralAnalyses(@Param("query") PastMeasureQuery query);
 
   List<MeasureDto> selectProjectMeasuresOfDeveloper(@Param("developerId") long developerId, @Param("metricIds") Collection<Integer> metricIds);
 
diff --git a/sonar-db/src/main/java/org/sonar/db/measure/PastMeasureQuery.java b/sonar-db/src/main/java/org/sonar/db/measure/PastMeasureQuery.java
new file mode 100644
index 00000000000..4db1a3f7168
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/measure/PastMeasureQuery.java
@@ -0,0 +1,66 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.db.measure;
+
+import java.util.List;
+import javax.annotation.CheckForNull;
+import javax.annotation.Nullable;
+import org.sonar.db.component.SnapshotDto;
+
+import static java.util.Objects.requireNonNull;
+
+public class PastMeasureQuery {
+  private final String componentUuid;
+  private final List<Integer> metricIds;
+  private final Long from;
+  private final Long to;
+  private final String status;
+
+  public PastMeasureQuery(String componentUuid, List<Integer> metricIds, @Nullable Long from, @Nullable Long to) {
+    this.componentUuid = requireNonNull(componentUuid);
+    this.metricIds = requireNonNull(metricIds);
+    this.from = from;
+    this.to = to;
+    this.status = SnapshotDto.STATUS_PROCESSED;
+  }
+
+  public String getComponentUuid() {
+    return componentUuid;
+  }
+
+  public List<Integer> getMetricIds() {
+    return metricIds;
+  }
+
+  @CheckForNull
+  public Long getFrom() {
+    return from;
+  }
+
+  @CheckForNull
+  public Long getTo() {
+    return to;
+  }
+
+  public String getStatus() {
+    return status;
+  }
+}
diff --git a/sonar-db/src/main/resources/org/sonar/db/measure/MeasureMapper.xml b/sonar-db/src/main/resources/org/sonar/db/measure/MeasureMapper.xml
index c56d91b0cb5..2a75e4a422c 100644
--- a/sonar-db/src/main/resources/org/sonar/db/measure/MeasureMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/measure/MeasureMapper.xml
@@ -142,12 +142,18 @@
   <select id="selectPastMeasuresOnSeveralAnalyses" parameterType="map" resultType="Measure">
     select <include refid="measureColumns"/>
     from project_measures pm
-    inner join snapshots analysis on analysis.uuid = pm.analysis_uuid
+      inner join snapshots analysis on analysis.uuid = pm.analysis_uuid
     where
-    pm.component_uuid = #{componentUuid}
-    and analysis.uuid in <foreach item="analysisUuid" collection="analysisUuids" open="(" separator="," close=")">#{analysisUuid}</foreach>
-    and pm.metric_id in <foreach item="metricId" collection="metricIds" open="(" separator="," close=")">#{metricId}</foreach>
+    pm.component_uuid = #{query.componentUuid, jdbcType=VARCHAR}
+    <if test="query.from!= null">
+      and analysis.created_at>=#{query.from, jdbcType=BIGINT}
+    </if>
+    <if test="query.to!=null">
+      and analysis.created_at&lt;#{query.to, jdbcType=BIGINT}
+    </if>
+    and pm.metric_id in <foreach item="metricId" collection="query.metricIds" open="(" separator="," close=")">#{metricId, jdbcType=VARCHAR}</foreach>
     and pm.person_id is null
+    and analysis.status=#{query.status, jdbcType=VARCHAR}
   </select>
 
   <select id="selectProjectMeasuresOfDeveloper" parameterType="map" resultType="Measure">
diff --git a/sonar-db/src/test/java/org/sonar/db/measure/MeasureDaoTest.java b/sonar-db/src/test/java/org/sonar/db/measure/MeasureDaoTest.java
index 55c3bd734fc..2500442b07a 100644
--- a/sonar-db/src/test/java/org/sonar/db/measure/MeasureDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/measure/MeasureDaoTest.java
@@ -30,12 +30,14 @@ import org.junit.rules.ExpectedException;
 import org.sonar.api.resources.Scopes;
 import org.sonar.api.utils.System2;
 import org.sonar.core.util.UuidFactoryImpl;
+import org.sonar.db.DbClient;
+import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
 import org.sonar.db.component.ComponentDto;
+import org.sonar.db.component.SnapshotDto;
 import org.sonar.db.component.SnapshotTesting;
 import org.sonar.db.organization.OrganizationDto;
 
-import static com.google.common.collect.Lists.newArrayList;
 import static java.util.Arrays.asList;
 import static java.util.Collections.emptyList;
 import static java.util.Collections.singletonList;
@@ -44,9 +46,11 @@ import static org.sonar.api.resources.Qualifiers.FILE;
 import static org.sonar.api.resources.Qualifiers.PROJECT;
 import static org.sonar.api.resources.Qualifiers.UNIT_TEST_FILE;
 import static org.sonar.api.resources.Qualifiers.VIEW;
+import static org.sonar.api.utils.DateUtils.parseDate;
 import static org.sonar.db.component.ComponentTesting.newDeveloper;
 import static org.sonar.db.component.ComponentTesting.newFileDto;
 import static org.sonar.db.component.ComponentTesting.newModuleDto;
+import static org.sonar.db.component.SnapshotTesting.newAnalysis;
 import static org.sonar.db.measure.MeasureTreeQuery.Strategy.CHILDREN;
 import static org.sonar.db.measure.MeasureTreeQuery.Strategy.LEAVES;
 
@@ -65,6 +69,8 @@ public class MeasureDaoTest {
 
   @Rule
   public DbTester db = DbTester.create(System2.INSTANCE);
+  private DbClient dbClient = db.getDbClient();
+  private DbSession dbSession = db.getSession();
 
   private MeasureDao underTest = db.getDbClient().measureDao();
 
@@ -451,16 +457,23 @@ public class MeasureDaoTest {
   @Test
   public void select_past_measures_with_several_analyses() {
     ComponentDto project = db.components().insertProject();
-    insertAnalysis(LAST_ANALYSIS_UUID, project.uuid(), true);
-    insertAnalysis(OTHER_ANALYSIS_UUID, project.uuid(), false);
+    long lastAnalysisDate = parseDate("2017-01-25").getTime();
+    long previousAnalysisDate = lastAnalysisDate - 10_000_000_000L;
+    long oldAnalysisDate = lastAnalysisDate - 100_000_000_000L;
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(project).setUuid(LAST_ANALYSIS_UUID).setCreatedAt(lastAnalysisDate));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(project).setUuid(OTHER_ANALYSIS_UUID).setCreatedAt(previousAnalysisDate).setLast(false));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(project).setUuid("OLD_ANALYSIS_UUID").setCreatedAt(oldAnalysisDate).setLast(false));
+    db.commit();
 
     // project
     insertMeasure("PROJECT_M1", LAST_ANALYSIS_UUID, project.uuid(), NCLOC_METRIC_ID);
     insertMeasure("PROJECT_M2", OTHER_ANALYSIS_UUID, project.uuid(), NCLOC_METRIC_ID);
+    insertMeasure("PROJECT_M3", "OLD_ANALYSIS_UUID", project.uuid(), NCLOC_METRIC_ID);
     db.commit();
 
-    // Children measures of project
-    List<MeasureDto> result = underTest.selectPastMeasures(db.getSession(), project.uuid(), newArrayList(LAST_ANALYSIS_UUID, OTHER_ANALYSIS_UUID), singletonList(NCLOC_METRIC_ID));
+    // Measures of project for last and previous analyses
+    List<MeasureDto> result = underTest.selectPastMeasures(db.getSession(),
+      new PastMeasureQuery(project.uuid(), singletonList(NCLOC_METRIC_ID), previousAnalysisDate, lastAnalysisDate + 1_000L));
 
     assertThat(result).hasSize(2).extracting(MeasureDto::getData).containsOnly("PROJECT_M1", "PROJECT_M2");
   }
@@ -548,8 +561,8 @@ public class MeasureDaoTest {
     db.getDbClient().measureDao().insert(db.getSession(), measure);
   }
 
-  private void insertAnalysis(String uuid, String projectUuid, boolean isLast) {
-    db.getDbClient().snapshotDao().insert(db.getSession(), SnapshotTesting.newSnapshot()
+  private SnapshotDto insertAnalysis(String uuid, String projectUuid, boolean isLast) {
+    return db.getDbClient().snapshotDao().insert(db.getSession(), SnapshotTesting.newSnapshot()
       .setUuid(uuid)
       .setComponentUuid(projectUuid)
       .setLast(isLast));