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

5 files changed, 108 insertions, 21 deletions

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));