From 1a64babff4c22cb4e412fd86b6fdb802e9c9983d Mon Sep 17 00:00:00 2001 From: Julien Lancelot Date: Fri, 21 Oct 2016 13:00:00 +0200 Subject: SONAR-8221 Fix project measures indexing on MySQL Replace streaming of projects by first loading all projects once, then load measures project by project --- .../db/measure/ProjectMeasuresIndexerIterator.java | 333 +++++++++++++++++++++ .../ProjectMeasuresIndexerIteratorTest.java | 291 ++++++++++++++++++ 2 files changed, 624 insertions(+) create mode 100644 sonar-db/src/main/java/org/sonar/db/measure/ProjectMeasuresIndexerIterator.java create mode 100644 sonar-db/src/test/java/org/sonar/db/measure/ProjectMeasuresIndexerIteratorTest.java (limited to 'sonar-db') diff --git a/sonar-db/src/main/java/org/sonar/db/measure/ProjectMeasuresIndexerIterator.java b/sonar-db/src/main/java/org/sonar/db/measure/ProjectMeasuresIndexerIterator.java new file mode 100644 index 00000000000..b61b3850826 --- /dev/null +++ b/sonar-db/src/main/java/org/sonar/db/measure/ProjectMeasuresIndexerIterator.java @@ -0,0 +1,333 @@ +/* + * 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 com.google.common.base.Joiner; +import com.google.common.collect.ImmutableSet; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.Iterator; +import java.util.List; +import java.util.Map; +import java.util.Optional; +import java.util.Set; +import javax.annotation.CheckForNull; +import javax.annotation.Nullable; +import org.apache.commons.lang.StringUtils; +import org.sonar.api.resources.Qualifiers; +import org.sonar.api.resources.Scopes; +import org.sonar.core.util.CloseableIterator; +import org.sonar.db.DatabaseUtils; +import org.sonar.db.DbSession; + +import static org.sonar.api.measures.CoreMetrics.ALERT_STATUS_KEY; +import static org.sonar.api.measures.Metric.ValueType.BOOL; +import static org.sonar.api.measures.Metric.ValueType.FLOAT; +import static org.sonar.api.measures.Metric.ValueType.INT; +import static org.sonar.api.measures.Metric.ValueType.LEVEL; +import static org.sonar.api.measures.Metric.ValueType.MILLISEC; +import static org.sonar.api.measures.Metric.ValueType.PERCENT; +import static org.sonar.api.measures.Metric.ValueType.RATING; +import static org.sonar.api.measures.Metric.ValueType.WORK_DUR; +import static org.sonar.db.DatabaseUtils.repeatCondition; + +public class ProjectMeasuresIndexerIterator extends CloseableIterator { + + private static final Set METRIC_TYPES = ImmutableSet.of(INT.name(), FLOAT.name(), PERCENT.name(), BOOL.name(), MILLISEC.name(), LEVEL.name(), RATING.name(), + WORK_DUR.name()); + + private static final Joiner METRICS_JOINER = Joiner.on("','"); + + private static final String SQL_PROJECTS = "SELECT p.uuid, p.kee, p.name, s.uuid, s.created_at FROM projects p " + + "LEFT OUTER JOIN snapshots s ON s.component_uuid=p.uuid AND s.islast=? " + + "WHERE p.enabled=? AND p.scope=? AND p.qualifier=?"; + + private static final String DATE_FILTER = " AND s.created_at>?"; + + private static final String PROJECT_FILTER = " AND p.uuid=?"; + + private static final String SQL_METRICS = "SELECT m.id, m.name FROM metrics m " + + "WHERE m.val_type IN ('" + METRICS_JOINER.join(METRIC_TYPES) + "') " + + "AND m.enabled=?"; + + private static final String SQL_MEASURES = "SELECT pm.metric_id, pm.value, pm.variation_value_1, pm.text_value FROM project_measures pm " + + "WHERE pm.component_uuid = ? AND pm.analysis_uuid = ? " + + "AND pm.metric_id IN ({metricIds}) " + + "AND (pm.value IS NOT NULL OR pm.variation_value_1 IS NOT NULL OR pm.text_value IS NOT NULL) " + + "AND pm.person_id IS NULL "; + + private final PreparedStatement measuresStatement; + private final Map metricKeysByIds; + private final Iterator projects; + + private ProjectMeasuresIndexerIterator(PreparedStatement measuresStatement, Map metricKeysByIds, List projects) throws SQLException { + this.measuresStatement = measuresStatement; + this.metricKeysByIds = metricKeysByIds; + this.projects = projects.iterator(); + } + + public static ProjectMeasuresIndexerIterator create(DbSession session, long afterDate, @Nullable String projectUuid) { + try { + Map metrics = selectMetricKeysByIds(session); + List projects = selectProjects(session, afterDate, projectUuid); + PreparedStatement projectsStatement = createMeasuresStatement(session, metrics.keySet()); + return new ProjectMeasuresIndexerIterator(projectsStatement, metrics, projects); + } catch (SQLException e) { + throw new IllegalStateException("Fail to execute request to select all project measures", e); + } + } + + private static Map selectMetricKeysByIds(DbSession session) { + Map metrics = new HashMap<>(); + try (PreparedStatement stmt = createMetricsStatement(session); + ResultSet rs = stmt.executeQuery()) { + while (rs.next()) { + metrics.put(rs.getLong(1), rs.getString(2)); + } + return metrics; + } catch (SQLException e) { + throw new IllegalStateException("Fail to execute request to select all metrics", e); + } + } + + private static PreparedStatement createMetricsStatement(DbSession session) throws SQLException { + PreparedStatement stmt = session.getConnection().prepareStatement(SQL_METRICS); + stmt.setBoolean(1, true); + return stmt; + } + + private static List selectProjects(DbSession session, long afterDate, @Nullable String projectUuid) { + List projects = new ArrayList<>(); + try (PreparedStatement stmt = createProjectsStatement(session, afterDate, projectUuid); + ResultSet rs = stmt.executeQuery()) { + while (rs.next()) { + long analysisDate = rs.getLong(5); + Project project = new Project(rs.getString(1), rs.getString(2), rs.getString(3), getString(rs, 4).orElseGet(() -> null), rs.wasNull() ? null : analysisDate); + projects.add(project); + } + return projects; + } catch (SQLException e) { + throw new IllegalStateException("Fail to execute request to select all projects", e); + } + } + + private static PreparedStatement createProjectsStatement(DbSession session, long afterDate, @Nullable String projectUuid) { + try { + String sql = SQL_PROJECTS; + sql += afterDate <= 0L ? "" : DATE_FILTER; + sql += projectUuid == null ? "" : PROJECT_FILTER; + PreparedStatement stmt = session.getConnection().prepareStatement(sql); + stmt.setBoolean(1, true); + stmt.setBoolean(2, true); + stmt.setString(3, Scopes.PROJECT); + stmt.setString(4, Qualifiers.PROJECT); + int index = 5; + if (afterDate > 0L) { + stmt.setLong(index, afterDate); + index++; + } + if (projectUuid != null) { + stmt.setString(index, projectUuid); + } + return stmt; + } catch (SQLException e) { + throw new IllegalStateException("Fail to prepare SQL request to select all project measures", e); + } + } + + private static PreparedStatement createMeasuresStatement(DbSession session, Set metricIds) throws SQLException { + try { + String sql = StringUtils.replace(SQL_MEASURES, "{metricIds}", repeatCondition("?", metricIds.size(), ",")); + PreparedStatement stmt = session.getConnection().prepareStatement(sql); + int index = 3; + for (Long metricId : metricIds) { + stmt.setLong(index, metricId); + index++; + } + return stmt; + } catch (SQLException e) { + throw new IllegalStateException("Fail to prepare SQL request to select measures", e); + } + } + + @Override + @CheckForNull + protected ProjectMeasures doNext() { + if (!projects.hasNext()) { + return null; + } + Project project = projects.next(); + Measures measures = selectMeasures(project.getUuid(), project.getAnalysisUuid()); + return new ProjectMeasures(project, measures); + } + + private Measures selectMeasures(String projectUuid, @Nullable String analysisUuid) { + Measures measures = new Measures(); + if (analysisUuid == null || metricKeysByIds.isEmpty()) { + return measures; + } + ResultSet rs = null; + try { + measuresStatement.setString(1, projectUuid); + measuresStatement.setString(2, analysisUuid); + rs = measuresStatement.executeQuery(); + while (rs.next()) { + readMeasure(rs, measures); + } + return measures; + } catch (Exception e) { + throw new IllegalStateException(String.format("Fail to execute request to select measures of project %s, analysis %s", projectUuid, analysisUuid), e); + } finally { + DatabaseUtils.closeQuietly(rs); + } + } + + private void readMeasure(ResultSet rs, Measures measures) throws SQLException { + String metricKey = metricKeysByIds.get(rs.getLong(1)); + Optional value = metricKey.startsWith("new_") ? getDouble(rs, 3) : getDouble(rs, 2); + if (value.isPresent()) { + measures.addNumericMeasure(metricKey, value.get()); + return; + } else if (ALERT_STATUS_KEY.equals(metricKey)) { + String textValue = rs.getString(4); + if (!rs.wasNull()) { + measures.setQualityGateStatus(textValue); + return; + } + } + throw new IllegalArgumentException("Measure has no value"); + } + + @Override + protected void doClose() throws Exception { + measuresStatement.close(); + } + + private static Optional getDouble(ResultSet rs, int index) { + try { + Double value = rs.getDouble(index); + if (!rs.wasNull()) { + return Optional.of(value); + } + return Optional.empty(); + } catch (SQLException e) { + throw new IllegalStateException("Fail to get double value", e); + } + } + + private static Optional getString(ResultSet rs, int index) { + try { + String value = rs.getString(index); + if (!rs.wasNull()) { + return Optional.of(value); + } + return Optional.empty(); + } catch (SQLException e) { + throw new IllegalStateException("Fail to get string value", e); + } + } + + public static class Project { + private final String uuid; + private final String key; + private final String name; + private final String analysisUuid; + private final Long analysisDate; + + public Project(String uuid, String key, String name, @Nullable String analysisUuid, @Nullable Long analysisDate) { + this.uuid = uuid; + this.key = key; + this.name = name; + this.analysisUuid = analysisUuid; + this.analysisDate = analysisDate; + } + + public String getUuid() { + return uuid; + } + + public String getKey() { + return key; + } + + public String getName() { + return name; + } + + @CheckForNull + public String getAnalysisUuid() { + return analysisUuid; + } + + @CheckForNull + public Long getAnalysisDate() { + return analysisDate; + } + } + + public static class Measures { + + private Map numericMeasures = new HashMap<>(); + private String qualityGateStatus; + + Measures addNumericMeasure(String metricKey, double value) { + numericMeasures.put(metricKey, value); + return this; + } + + public Map getNumericMeasures() { + return numericMeasures; + } + + Measures setQualityGateStatus(@Nullable String qualityGateStatus) { + this.qualityGateStatus = qualityGateStatus; + return this; + } + + @CheckForNull + public String getQualityGateStatus() { + return qualityGateStatus; + } + } + + public static class ProjectMeasures { + private Project project; + private Measures measures; + + public ProjectMeasures(Project project, Measures measures) { + this.project = project; + this.measures = measures; + } + + public Project getProject() { + return project; + } + + public Measures getMeasures() { + return measures; + } + + } + +} diff --git a/sonar-db/src/test/java/org/sonar/db/measure/ProjectMeasuresIndexerIteratorTest.java b/sonar-db/src/test/java/org/sonar/db/measure/ProjectMeasuresIndexerIteratorTest.java new file mode 100644 index 00000000000..69763cf453e --- /dev/null +++ b/sonar-db/src/test/java/org/sonar/db/measure/ProjectMeasuresIndexerIteratorTest.java @@ -0,0 +1,291 @@ +/* + * 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 com.google.common.collect.Maps; +import java.util.Map; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.junit.rules.ExpectedException; +import org.sonar.api.measures.Metric; +import org.sonar.api.utils.System2; +import org.sonar.db.DbClient; +import org.sonar.db.DbSession; +import org.sonar.db.DbTester; +import org.sonar.db.component.ComponentDbTester; +import org.sonar.db.component.ComponentDto; +import org.sonar.db.component.SnapshotDto; +import org.sonar.db.measure.ProjectMeasuresIndexerIterator.ProjectMeasures; +import org.sonar.db.metric.MetricDto; +import org.sonar.db.metric.MetricTesting; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.entry; +import static org.sonar.api.measures.Metric.Level.WARN; +import static org.sonar.api.measures.Metric.ValueType.DATA; +import static org.sonar.api.measures.Metric.ValueType.DISTRIB; +import static org.sonar.api.measures.Metric.ValueType.INT; +import static org.sonar.api.measures.Metric.ValueType.LEVEL; +import static org.sonar.api.measures.Metric.ValueType.STRING; +import static org.sonar.db.component.ComponentTesting.newDeveloper; +import static org.sonar.db.component.ComponentTesting.newProjectDto; +import static org.sonar.db.component.ComponentTesting.newView; +import static org.sonar.db.component.SnapshotTesting.newAnalysis; + +public class ProjectMeasuresIndexerIteratorTest { + + @Rule + public ExpectedException expectedException = ExpectedException.none(); + + @Rule + public DbTester dbTester = DbTester.create(System2.INSTANCE); + + DbClient dbClient = dbTester.getDbClient(); + DbSession dbSession = dbTester.getSession(); + + ComponentDbTester componentDbTester = new ComponentDbTester(dbTester); + + @Test + public void return_project_measure() { + MetricDto metric1 = insertIntMetric("ncloc"); + MetricDto metric2 = insertIntMetric("coverage"); + ComponentDto project = newProjectDto().setKey("Project-Key").setName("Project Name"); + SnapshotDto analysis = componentDbTester.insertProjectAndSnapshot(project); + insertMeasure(project, analysis, metric1, 10d); + insertMeasure(project, analysis, metric2, 20d); + + Map docsById = createResultSetAndReturnDocsById(); + + assertThat(docsById).hasSize(1); + ProjectMeasures doc = docsById.get(project.uuid()); + assertThat(doc).isNotNull(); + assertThat(doc.getProject().getUuid()).isEqualTo(project.uuid()); + assertThat(doc.getProject().getKey()).isEqualTo("Project-Key"); + assertThat(doc.getProject().getName()).isEqualTo("Project Name"); + assertThat(doc.getProject().getAnalysisDate()).isNotNull().isEqualTo(analysis.getCreatedAt()); + assertThat(doc.getMeasures().getNumericMeasures()).containsOnly(entry("ncloc", 10d), entry("coverage", 20d)); + } + + @Test + public void return_project_measure_having_leak() throws Exception { + MetricDto metric = insertIntMetric("new_lines"); + ComponentDto project = newProjectDto(); + SnapshotDto analysis = componentDbTester.insertProjectAndSnapshot(project); + insertMeasureOnLeak(project, analysis, metric, 10d); + + Map docsById = createResultSetAndReturnDocsById(); + + assertThat(docsById.get(project.uuid()).getMeasures().getNumericMeasures()).containsOnly(entry("new_lines", 10d)); + } + + @Test + public void return_quality_gate_status_measure() throws Exception { + MetricDto metric = insertMetric("alert_status", LEVEL); + ComponentDto project = newProjectDto(); + SnapshotDto analysis = componentDbTester.insertProjectAndSnapshot(project); + insertMeasure(project, analysis, metric, WARN.name()); + + Map docsById = createResultSetAndReturnDocsById(); + + assertThat(docsById.get(project.uuid()).getMeasures().getQualityGateStatus()).isEqualTo("WARN"); + } + + @Test + public void does_not_return_none_numeric_metrics() throws Exception { + MetricDto dataMetric = insertMetric("data", DATA); + MetricDto distribMetric = insertMetric("distrib", DISTRIB); + MetricDto stringMetric = insertMetric("string", STRING); + ComponentDto project = newProjectDto(); + SnapshotDto analysis = componentDbTester.insertProjectAndSnapshot(project); + insertMeasure(project, analysis, dataMetric, "dat"); + insertMeasure(project, analysis, distribMetric, "dis"); + insertMeasure(project, analysis, stringMetric, "str"); + + Map docsById = createResultSetAndReturnDocsById(); + + assertThat(docsById.get(project.uuid()).getMeasures().getNumericMeasures()).isEmpty(); + } + + @Test + public void does_not_return_disabled_metrics() throws Exception { + MetricDto disabledMetric = insertMetric("disabled", false, false, INT); + ComponentDto project = newProjectDto(); + SnapshotDto analysis = componentDbTester.insertProjectAndSnapshot(project); + insertMeasure(project, analysis, disabledMetric, 10d); + + Map docsById = createResultSetAndReturnDocsById(); + + assertThat(docsById.get(project.uuid()).getMeasures().getNumericMeasures()).isEmpty(); + } + + @Test + public void fail_when_measure_return_no_value() throws Exception { + MetricDto metric = insertIntMetric("new_lines"); + ComponentDto project = newProjectDto(); + SnapshotDto analysis = componentDbTester.insertProjectAndSnapshot(project); + insertMeasure(project, analysis, metric, 10d); + + expectedException.expect(IllegalStateException.class); + createResultSetAndReturnDocsById(); + } + + @Test + public void return_many_project_measures() { + componentDbTester.insertProjectAndSnapshot(newProjectDto()); + componentDbTester.insertProjectAndSnapshot(newProjectDto()); + componentDbTester.insertProjectAndSnapshot(newProjectDto()); + + assertThat(createResultSetAndReturnDocsById()).hasSize(3); + } + + @Test + public void return_project_without_analysis() throws Exception { + ComponentDto project = componentDbTester.insertComponent(newProjectDto()); + dbClient.snapshotDao().insert(dbSession, newAnalysis(project).setLast(false)); + dbSession.commit(); + + Map docsById = createResultSetAndReturnDocsById(); + + assertThat(docsById).hasSize(1); + ProjectMeasures doc = docsById.get(project.uuid()); + assertThat(doc.getProject().getAnalysisDate()).isNull(); + } + + @Test + public void does_not_return_non_active_projects() throws Exception { + // Disabled project + componentDbTester.insertProjectAndSnapshot(newProjectDto().setEnabled(false)); + // Disabled project with analysis + ComponentDto project = componentDbTester.insertComponent(newProjectDto().setEnabled(false)); + dbClient.snapshotDao().insert(dbSession, newAnalysis(project)); + + // A view + componentDbTester.insertProjectAndSnapshot(newView()); + + // A developer + componentDbTester.insertProjectAndSnapshot(newDeveloper("dev")); + + dbSession.commit(); + + assertResultSetIsEmpty(); + } + + @Test + public void return_only_docs_from_given_project() throws Exception { + ComponentDto project = newProjectDto(); + SnapshotDto analysis = componentDbTester.insertProjectAndSnapshot(project); + componentDbTester.insertProjectAndSnapshot(newProjectDto()); + componentDbTester.insertProjectAndSnapshot(newProjectDto()); + + Map docsById = createResultSetAndReturnDocsById(0L, project.uuid()); + + assertThat(docsById).hasSize(1); + ProjectMeasures doc = docsById.get(project.uuid()); + assertThat(doc).isNotNull(); + assertThat(doc.getProject().getUuid()).isEqualTo(project.uuid()); + assertThat(doc.getProject().getKey()).isNotNull().isEqualTo(project.getKey()); + assertThat(doc.getProject().getName()).isNotNull().isEqualTo(project.name()); + assertThat(doc.getProject().getAnalysisDate()).isNotNull().isEqualTo(analysis.getCreatedAt()); + } + + @Test + public void return_only_docs_after_date() throws Exception { + ComponentDto project1 = newProjectDto(); + dbClient.componentDao().insert(dbSession, project1); + dbClient.snapshotDao().insert(dbSession, newAnalysis(project1).setCreatedAt(1_000_000L)); + ComponentDto project2 = newProjectDto(); + dbClient.componentDao().insert(dbSession, project2); + dbClient.snapshotDao().insert(dbSession, newAnalysis(project2).setCreatedAt(2_000_000L)); + dbSession.commit(); + + Map docsById = createResultSetAndReturnDocsById(1_500_000L, null); + + assertThat(docsById).hasSize(1); + assertThat(docsById.get(project2.uuid())).isNotNull(); + } + + @Test + public void return_nothing_on_unknown_project() throws Exception { + componentDbTester.insertProjectAndSnapshot(newProjectDto()); + + Map docsById = createResultSetAndReturnDocsById(0L, "UNKNOWN"); + + assertThat(docsById).isEmpty(); + } + + private Map createResultSetAndReturnDocsById() { + return createResultSetAndReturnDocsById(0L, null); + } + + private Map createResultSetAndReturnDocsById(long date, @Nullable String projectUuid) { + ProjectMeasuresIndexerIterator it = ProjectMeasuresIndexerIterator.create(dbTester.getSession(), date, projectUuid); + Map docsById = Maps.uniqueIndex(it, pm -> pm.getProject().getUuid()); + it.close(); + return docsById; + } + + private void assertResultSetIsEmpty() { + assertThat(createResultSetAndReturnDocsById()).isEmpty(); + } + + private MetricDto insertIntMetric(String metricKey) { + return insertMetric(metricKey, true, false, INT); + } + + private MetricDto insertMetric(String metricKey, Metric.ValueType type) { + return insertMetric(metricKey, true, false, type); + } + + private MetricDto insertMetric(String metricKey, boolean enabled, boolean hidden, Metric.ValueType type) { + MetricDto metric = dbClient.metricDao().insert(dbSession, + MetricTesting.newMetricDto() + .setKey(metricKey) + .setEnabled(enabled) + .setHidden(hidden) + .setValueType(type.name())); + dbSession.commit(); + return metric; + } + + private MeasureDto insertMeasure(ComponentDto project, SnapshotDto analysis, MetricDto metric, double value) { + return insertMeasure(project, analysis, metric, value, null); + } + + private MeasureDto insertMeasureOnLeak(ComponentDto project, SnapshotDto analysis, MetricDto metric, double value) { + return insertMeasure(project, analysis, metric, null, value); + } + + private MeasureDto insertMeasure(ComponentDto project, SnapshotDto analysis, MetricDto metric, String value) { + return insertMeasure(MeasureTesting.newMeasureDto(metric, project, analysis).setData(value)); + } + + private MeasureDto insertMeasure(ComponentDto project, SnapshotDto analysis, MetricDto metric, @Nullable Double value, @Nullable Double leakValue) { + return insertMeasure(MeasureTesting.newMeasureDto(metric, project, analysis).setValue(value).setVariation(1, leakValue)); + } + + private MeasureDto insertMeasure(MeasureDto measure) { + dbClient.measureDao().insert(dbSession, measure); + dbSession.commit(); + return measure; + } + +} -- cgit v1.2.3