From b3e6b1168fc4b06768529125fa14a12a5e4e9257 Mon Sep 17 00:00:00 2001 From: Simon Brandhof Date: Mon, 8 Oct 2012 19:11:52 +0200 Subject: [PATCH] SONAR-3621 improve the SQL request of measure filters --- .../org/sonar/core/measure/MeasureFilter.java | 161 ++++++++ .../core/measure/MeasureFilterExecutor.java | 88 +++++ .../sonar/core/measure/MeasureFilterRow.java | 54 +++ .../sonar/core/measure/MeasureFilterSort.java | 104 +++++ .../sonar/core/measure/MeasureFilterSql.java | 245 ++++++++++++ .../measure/MeasureFilterValueCondition.java | 84 ++++ .../core/measure/MesasureFilterContext.java | 43 +++ .../org/sonar/core/resource/ResourceDao.java | 5 + .../sonar/core/resource/ResourceMapper.java | 2 + .../sonar/core/resource/ResourceMapper.xml | 4 + .../measure/MeasureFilterExecutorTest.java | 359 ++++++++++++++++++ .../core/persistence/AbstractDaoTestCase.java | 9 +- .../MeasureFilterExecutorTest/shared.xml | 162 ++++++++ 13 files changed, 1317 insertions(+), 3 deletions(-) create mode 100644 sonar-core/src/main/java/org/sonar/core/measure/MeasureFilter.java create mode 100644 sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterExecutor.java create mode 100644 sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterRow.java create mode 100644 sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterSort.java create mode 100644 sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterSql.java create mode 100644 sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterValueCondition.java create mode 100644 sonar-core/src/main/java/org/sonar/core/measure/MesasureFilterContext.java create mode 100644 sonar-core/src/test/java/org/sonar/core/measure/MeasureFilterExecutorTest.java create mode 100644 sonar-core/src/test/resources/org/sonar/core/measure/MeasureFilterExecutorTest/shared.xml diff --git a/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilter.java b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilter.java new file mode 100644 index 00000000000..d3a9ffd0b36 --- /dev/null +++ b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilter.java @@ -0,0 +1,161 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2012 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.core.measure; + +import com.google.common.collect.Lists; +import com.google.common.collect.Sets; +import org.sonar.api.measures.Metric; + +import java.util.Date; +import java.util.List; +import java.util.Set; + +public class MeasureFilter { + // conditions on resources + private String baseResourceKey; + private boolean onBaseResourceChildren = false; // only if baseResourceKey is set + private Set resourceScopes = Sets.newHashSet(); + private Set resourceQualifiers = Sets.newHashSet(); + private Set resourceLanguages = Sets.newHashSet(); + private String resourceName; + private Date fromDate = null, toDate = null; + private boolean userFavourites = false; + + // conditions on measures + private List measureConditions = Lists.newArrayList(); + + // sort + private MeasureFilterSort sort = new MeasureFilterSort(); + + public String baseResourceKey() { + return baseResourceKey; + } + + public MeasureFilter setBaseResourceKey(String s) { + this.baseResourceKey = s; + return this; + } + + public MeasureFilter setOnBaseResourceChildren(boolean b) { + this.onBaseResourceChildren = b; + return this; + } + + public boolean isOnBaseResourceChildren() { + return onBaseResourceChildren; + } + + public MeasureFilter setResourceScopes(Set resourceScopes) { + this.resourceScopes = resourceScopes; + return this; + } + + public MeasureFilter setResourceQualifiers(String... qualifiers) { + this.resourceQualifiers = Sets.newHashSet(qualifiers); + return this; + } + + public MeasureFilter setResourceLanguages(String... languages) { + this.resourceLanguages = Sets.newHashSet(languages); + return this; + } + + public MeasureFilter setUserFavourites(boolean b) { + this.userFavourites = b; + return this; + } + + public boolean userFavourites() { + return userFavourites; + } + + public String resourceName() { + return resourceName; + } + + public MeasureFilter setResourceName(String s) { + this.resourceName = s; + return this; + } + + public MeasureFilter addCondition(MeasureFilterValueCondition condition) { + this.measureConditions.add(condition); + return this; + } + + public MeasureFilter setSortOn(MeasureFilterSort.Field sortField) { + this.sort.setField(sortField); + return this; + } + + public MeasureFilter setSortAsc(boolean b) { + this.sort.setAsc(b); + return this; + } + + public MeasureFilter setSortOnMetric(Metric m) { + this.sort.setMetric(m); + return this; + } + + public MeasureFilter setSortOnPeriod(int period) { + this.sort.setPeriod(period); + return this; + } + + public MeasureFilter setFromDate(Date d) { + this.fromDate = d; + return this; + } + + public MeasureFilter setToDate(Date d) { + this.toDate = d; + return this; + } + + public Date fromDate() { + return fromDate; + } + + public Date toDate() { + return toDate; + } + + public Set resourceScopes() { + return resourceScopes; + } + + public Set resourceQualifiers() { + return resourceQualifiers; + } + + public Set resourceLanguages() { + return resourceLanguages; + } + + public List measureConditions() { + return measureConditions; + } + + MeasureFilterSort sort() { + return sort; + } + +} diff --git a/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterExecutor.java b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterExecutor.java new file mode 100644 index 00000000000..eb66a9aa0b4 --- /dev/null +++ b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterExecutor.java @@ -0,0 +1,88 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2012 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.core.measure; + +import org.apache.ibatis.session.SqlSession; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; +import org.sonar.api.ServerComponent; +import org.sonar.core.persistence.Database; +import org.sonar.core.persistence.MyBatis; +import org.sonar.core.resource.ResourceDao; + +import javax.annotation.Nullable; +import java.sql.Connection; +import java.util.Collections; +import java.util.List; + +public class MeasureFilterExecutor implements ServerComponent { + private static final Logger FILTER_LOG = LoggerFactory.getLogger("org.sonar.MEASURE_FILTER"); + + private MyBatis mybatis; + private Database database; + private ResourceDao resourceDao; + + public MeasureFilterExecutor(MyBatis mybatis, Database database, ResourceDao resourceDao) { + this.mybatis = mybatis; + this.database = database; + this.resourceDao = resourceDao; + } + + public List execute(MeasureFilter filter, @Nullable Long userId) { + List rows; + SqlSession session = null; + try { + session = mybatis.openSession(); + MesasureFilterContext context = prepareContext(filter, userId, session); + + if (isValid(filter, context)) { + MeasureFilterSql sql = new MeasureFilterSql(database, filter, context); + Connection connection = session.getConnection(); + rows = sql.execute(connection); + } else { + rows = Collections.emptyList(); + } + + } catch (Exception e) { + throw new IllegalStateException(e); + + } finally { + MyBatis.closeQuietly(session); + } + + return rows; + } + + private MesasureFilterContext prepareContext(MeasureFilter filter, Long userId, SqlSession session) { + MesasureFilterContext context = new MesasureFilterContext(); + context.setUserId(userId); + if (filter.baseResourceKey() != null) { + context.setBaseSnapshot(resourceDao.getLastSnapshot(filter.baseResourceKey(), session)); + } + return context; + } + + static boolean isValid(MeasureFilter filter, MesasureFilterContext context) { + return + !(filter.resourceQualifiers().isEmpty() && !filter.userFavourites()) && + !(filter.isOnBaseResourceChildren() && context.getBaseSnapshot() == null) && + !(filter.userFavourites() && context.getUserId() == null); + } +} diff --git a/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterRow.java b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterRow.java new file mode 100644 index 00000000000..cf041422c42 --- /dev/null +++ b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterRow.java @@ -0,0 +1,54 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2012 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.core.measure; + +public class MeasureFilterRow { + private final long snapshotId; + private final long resourceId; + private final long resourceRootId; + private String sortText; + + MeasureFilterRow(long snapshotId, long resourceId, long resourceRootId) { + this.snapshotId = snapshotId; + this.resourceId = resourceId; + this.resourceRootId = resourceRootId; + } + + public long getSnapshotId() { + return snapshotId; + } + + public long getResourceId() { + return resourceId; + } + + public long getResourceRootId() { + return resourceRootId; + } + + public String getSortText() { + return sortText; + } + + MeasureFilterRow setSortText(String s) { + this.sortText = s; + return this; + } +} diff --git a/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterSort.java b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterSort.java new file mode 100644 index 00000000000..3abb3672664 --- /dev/null +++ b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterSort.java @@ -0,0 +1,104 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2012 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.core.measure; + +import org.sonar.api.measures.Metric; + +class MeasureFilterSort { + public static enum Field { + KEY, NAME, VERSION, LANGUAGE, DATE, METRIC + } + + private Field field = Field.NAME; + private Metric metric = null; + private int period = -1; + private boolean asc = true; + + MeasureFilterSort() { + } + + void setField(Field field) { + this.field = field; + } + + void setMetric(Metric metric) { + this.field = Field.METRIC; + this.metric = metric; + } + + void setPeriod(int period) { + this.period = (period > 0 ? period : -1); + } + + void setAsc(boolean asc) { + this.asc = asc; + } + + public Field field() { + return field; + } + + boolean onMeasures() { + return field == Field.METRIC; + } + + Metric metric() { + return metric; + } + + boolean isSortedByDatabase() { + return metric != null && metric.isNumericType(); + } + + boolean isAsc() { + return asc; + } + + String column() { + // only numeric metrics can be sorted by database, else results are sorted programmatically. + String column = null; + switch (field) { + case KEY: + column = "p.kee"; + break; + case NAME: + column = "p.long_name"; + break; + case VERSION: + column = "s.version"; + break; + case LANGUAGE: + column = "p.language"; + break; + case DATE: + column = "s.created_at"; + break; + case METRIC: + if (metric.isNumericType()) { + column = (period > 0 ? "pm.variation_value_" + period : "pm.value"); + } else { + column = "pm.text_value"; + } + break; + } + return column; + } + +} diff --git a/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterSql.java b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterSql.java new file mode 100644 index 00000000000..e59708c8467 --- /dev/null +++ b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterSql.java @@ -0,0 +1,245 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2012 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.core.measure; + +import com.google.common.base.Function; +import com.google.common.collect.Lists; +import com.google.common.collect.Ordering; +import org.apache.commons.lang.StringEscapeUtils; +import org.apache.commons.lang.StringUtils; +import org.slf4j.LoggerFactory; +import org.sonar.api.measures.Metric; +import org.sonar.core.persistence.Database; +import org.sonar.core.persistence.dialect.PostgreSql; +import org.sonar.core.resource.SnapshotDto; + +import javax.annotation.Nullable; +import java.sql.*; +import java.util.Collection; +import java.util.List; + +class MeasureFilterSql { + + private final Database database; + private final MeasureFilter filter; + private final MesasureFilterContext context; + private final StringBuilder sql = new StringBuilder(1000); + private final List dateParameters = Lists.newArrayList(); + + MeasureFilterSql(Database database, MeasureFilter filter, MesasureFilterContext context) { + this.database = database; + this.filter = filter; + this.context = context; + init(); + } + + List execute(Connection connection) throws SQLException { + PreparedStatement statement = connection.prepareStatement(sql.toString()); + ResultSet rs = null; + try { + for (int index = 0; index < dateParameters.size(); index++) { + statement.setDate(index + 1, dateParameters.get(index)); + } + rs = statement.executeQuery(); + return process(rs); + + } finally { + closeQuietly(statement, rs); + } + } + + String sql() { + return sql.toString(); + } + + private void init() { + sql.append("SELECT block.id, max(block.rid) rid, max(block.rootid) rootid, max(sortval) sortval"); + for (int index = 0; index < filter.measureConditions().size(); index++) { + sql.append(", max(crit_").append(index).append(")"); + } + sql.append(" FROM ("); + + appendSortBlock(); + for (int index = 0; index < filter.measureConditions().size(); index++) { + MeasureFilterValueCondition condition = filter.measureConditions().get(index); + sql.append(" UNION "); + appendConditionBlock(index, condition); + } + + sql.append(") block GROUP BY block.id"); + if (!filter.measureConditions().isEmpty()) { + sql.append(" HAVING "); + for (int index = 0; index < filter.measureConditions().size(); index++) { + if (index > 0) { + sql.append(" AND "); + } + sql.append(" max(crit_").append(index).append(") IS NOT NULL "); + } + } + if (filter.sort().isSortedByDatabase()) { + sql.append(" ORDER BY sortval"); + sql.append(filter.sort().isAsc() ? " ASC " : " DESC "); + } + } + + private void appendSortBlock() { + sql.append(" SELECT s.id, s.project_id rid, s.root_project_id rootid, ").append(filter.sort().column()).append(" sortval"); + for (int index = 0; index < filter.measureConditions().size(); index++) { + MeasureFilterValueCondition condition = filter.measureConditions().get(index); + sql.append(", ").append(nullSelect(condition.metric())).append(" crit_").append(index); + } + sql.append(" FROM snapshots s INNER JOIN projects p ON s.project_id=p.id "); + if (filter.sort().onMeasures()) { + sql.append(" LEFT OUTER JOIN project_measures pm ON s.id=pm.snapshot_id AND pm.metric_id="); + sql.append(filter.sort().metric().getId()); + sql.append(" AND pm.rule_id IS NULL AND pm.rule_priority IS NULL AND pm.characteristic_id IS NULL AND pm.person_id IS NULL "); + } + sql.append(" WHERE "); + appendResourceConditions(); + } + + private void appendConditionBlock(int conditionIndex, MeasureFilterValueCondition condition) { + sql.append(" SELECT s.id, s.project_id rid, s.root_project_id rootid, null sortval"); + for (int j = 0; j < filter.measureConditions().size(); j++) { + sql.append(", "); + if (j == conditionIndex) { + sql.append(condition.valueColumn()); + } else { + sql.append(nullSelect(filter.measureConditions().get(j).metric())); + } + sql.append(" crit_").append(j); + } + sql.append(" FROM snapshots s INNER JOIN projects p ON s.project_id=p.id INNER JOIN project_measures pm ON s.id=pm.snapshot_id "); + sql.append(" WHERE "); + appendResourceConditions(); + sql.append(" AND pm.rule_id IS NULL AND pm.rule_priority IS NULL AND pm.characteristic_id IS NULL AND pm.person_id IS NULL AND "); + condition.appendSql(sql); + } + + private void appendResourceConditions() { + sql.append(" s.status='P' AND s.islast=").append(database.getDialect().getTrueSqlValue()); + sql.append(" AND p.copy_resource_id IS NULL "); + if (!filter.resourceQualifiers().isEmpty()) { + sql.append(" AND s.qualifier IN "); + appendInStatement(filter.resourceQualifiers(), sql); + } + if (!filter.resourceScopes().isEmpty()) { + sql.append(" AND s.scope IN "); + appendInStatement(filter.resourceScopes(), sql); + } + if (!filter.resourceLanguages().isEmpty()) { + sql.append(" AND p.language IN "); + appendInStatement(filter.resourceLanguages(), sql); + } + if (filter.fromDate() != null) { + sql.append(" AND s.created_at >= ? "); + dateParameters.add(new java.sql.Date(filter.fromDate().getTime())); + } + if (filter.toDate() != null) { + sql.append(" AND s.created_at <= ? "); + dateParameters.add(new java.sql.Date(filter.toDate().getTime())); + } + if (filter.userFavourites() && context.getUserId() != null) { + sql.append(" AND s.project_id IN (SELECT props.resource_id FROM properties props WHERE props.prop_key='favourite' AND props.user_id="); + sql.append(context.getUserId()); + sql.append(" AND props.resource_id IS NOT NULL) "); + } + if (StringUtils.isNotBlank(filter.resourceName())) { + sql.append(" AND s.project_id IN (SELECT rindex.resource_id FROM resource_index rindex WHERE rindex.kee like '"); + sql.append(StringEscapeUtils.escapeSql(StringUtils.lowerCase(filter.resourceName()))); + sql.append("%'"); + if (!filter.resourceQualifiers().isEmpty()) { + sql.append(" AND rindex.qualifier IN "); + appendInStatement(filter.resourceQualifiers(), sql); + } + sql.append(") "); + } + SnapshotDto baseSnapshot = context.getBaseSnapshot(); + if (baseSnapshot != null) { + if (filter.isOnBaseResourceChildren()) { + sql.append(" AND s.parent_snapshot_id=").append(baseSnapshot.getId()); + } else { + Long rootSnapshotId = (baseSnapshot.getRootId() != null ? baseSnapshot.getRootId() : baseSnapshot.getId()); + sql.append(" AND s.root_snapshot_id=").append(rootSnapshotId); + sql.append(" AND s.path LIKE '").append(baseSnapshot.getPath()).append(baseSnapshot.getId()).append(".%'"); + } + } + } + + List process(ResultSet rs) throws SQLException { + List rows = Lists.newArrayList(); + boolean sortTextValues = !filter.sort().isSortedByDatabase(); + while (rs.next()) { + MeasureFilterRow row = new MeasureFilterRow(rs.getLong(1), rs.getLong(2), rs.getLong(3)); + if (sortTextValues) { + row.setSortText(rs.getString(4)); + } + rows.add(row); + } + if (sortTextValues) { + // database does not manage case-insensitive text sorting. It must be done programmatically + Function function = new Function() { + public String apply(@Nullable MeasureFilterRow row) { + return (row != null ? StringUtils.defaultString(row.getSortText()) : ""); + } + }; + Ordering ordering = Ordering.from(String.CASE_INSENSITIVE_ORDER).onResultOf(function).nullsFirst(); + if (!filter.sort().isAsc()) { + ordering = ordering.reverse(); + } + rows = ordering.sortedCopy(rows); + } + return rows; + } + + private String nullSelect(Metric metric) { + if (metric.isNumericType() && PostgreSql.ID.equals(database.getDialect().getId())) { + return "null::integer"; + } + return "null"; + } + + + private static void appendInStatement(Collection values, StringBuilder to) { + to.append(" ('"); + to.append(StringUtils.join(values, "','")); + to.append("') "); + } + + private static void closeQuietly(@Nullable Statement stmt, @Nullable ResultSet rs) { + if (rs != null) { + try { + rs.close(); + } catch (SQLException e) { + LoggerFactory.getLogger(MeasureFilterSql.class).warn("Fail to close result set", e); + // ignore + } + } + if (stmt != null) { + try { + stmt.close(); + } catch (SQLException e) { + LoggerFactory.getLogger(MeasureFilterSql.class).warn("Fail to close statement", e); + // ignore + } + } + + } +} diff --git a/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterValueCondition.java b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterValueCondition.java new file mode 100644 index 00000000000..145e7d7bd86 --- /dev/null +++ b/sonar-core/src/main/java/org/sonar/core/measure/MeasureFilterValueCondition.java @@ -0,0 +1,84 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2012 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.core.measure; + +import org.sonar.api.measures.Metric; + +public class MeasureFilterValueCondition { + + public static enum Operator { + GREATER(">"), GREATER_OR_EQUALS(">="), EQUALS("="), LESS("<"), LESS_OR_EQUALS("<="); + + private String sql; + + private Operator(String sql) { + this.sql = sql; + } + + public String getSql() { + return sql; + } + } + + private final Metric metric; + private final Operator operator; + private final float value; + private int period = -1; + + public MeasureFilterValueCondition(Metric metric, Operator operator, float value) { + this.metric = metric; + this.operator = operator; + this.value = value; + } + + public MeasureFilterValueCondition setPeriod(int period) { + this.period = (period > 0 ? period : -1); + return this; + } + + public Metric metric() { + return metric; + } + + public Operator operator() { + return operator; + } + + public double value() { + return value; + } + + public int period() { + return period; + } + + String valueColumn() { + if (period > 0) { + return "pm.variation_value_" + period; + } + return "pm.value"; + } + + void appendSql(StringBuilder sql) { + sql.append(" pm.metric_id="); + sql.append(metric.getId()); + sql.append(" AND ").append(valueColumn()).append(operator.getSql()).append(value); + } +} diff --git a/sonar-core/src/main/java/org/sonar/core/measure/MesasureFilterContext.java b/sonar-core/src/main/java/org/sonar/core/measure/MesasureFilterContext.java new file mode 100644 index 00000000000..c5362a3cdba --- /dev/null +++ b/sonar-core/src/main/java/org/sonar/core/measure/MesasureFilterContext.java @@ -0,0 +1,43 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2012 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.core.measure; + +import org.sonar.core.resource.SnapshotDto; + +public class MesasureFilterContext { + private Long userId; + private SnapshotDto baseSnapshot; + + public Long getUserId() { + return userId; + } + + public void setUserId(Long userId) { + this.userId = userId; + } + + public SnapshotDto getBaseSnapshot() { + return baseSnapshot; + } + + public void setBaseSnapshot(SnapshotDto baseSnapshot) { + this.baseSnapshot = baseSnapshot; + } +} diff --git a/sonar-core/src/main/java/org/sonar/core/resource/ResourceDao.java b/sonar-core/src/main/java/org/sonar/core/resource/ResourceDao.java index 7d50a1094b8..1b6a4e1398f 100644 --- a/sonar-core/src/main/java/org/sonar/core/resource/ResourceDao.java +++ b/sonar-core/src/main/java/org/sonar/core/resource/ResourceDao.java @@ -76,6 +76,10 @@ public class ResourceDao { return session.getMapper(ResourceMapper.class).selectResource(projectId); } + public SnapshotDto getLastSnapshot(String resourceKey, SqlSession session) { + return session.getMapper(ResourceMapper.class).selectLastSnapshotByKey(resourceKey); + } + public List getDescendantProjects(long projectId) { SqlSession session = mybatis.openSession(); try { @@ -92,6 +96,7 @@ public class ResourceDao { return resources; } + private void appendChildProjects(long projectId, ResourceMapper mapper, List resources) { List subProjects = mapper.selectDescendantProjects(projectId); for (ResourceDto subProject : subProjects) { diff --git a/sonar-core/src/main/java/org/sonar/core/resource/ResourceMapper.java b/sonar-core/src/main/java/org/sonar/core/resource/ResourceMapper.java index cba769cf2c0..05b8ba5d53d 100644 --- a/sonar-core/src/main/java/org/sonar/core/resource/ResourceMapper.java +++ b/sonar-core/src/main/java/org/sonar/core/resource/ResourceMapper.java @@ -26,6 +26,8 @@ import java.util.List; public interface ResourceMapper { SnapshotDto selectSnapshot(Long snapshotId); + SnapshotDto selectLastSnapshotByKey(String resourceKey); + ResourceDto selectResource(long id); List selectDescendantProjects(long rootProjectId); diff --git a/sonar-core/src/main/resources/org/sonar/core/resource/ResourceMapper.xml b/sonar-core/src/main/resources/org/sonar/core/resource/ResourceMapper.xml index e35451c4f6e..0cc52cbbdf8 100644 --- a/sonar-core/src/main/resources/org/sonar/core/resource/ResourceMapper.xml +++ b/sonar-core/src/main/resources/org/sonar/core/resource/ResourceMapper.xml @@ -79,6 +79,10 @@ select * from snapshots where id=#{id} + + diff --git a/sonar-core/src/test/java/org/sonar/core/measure/MeasureFilterExecutorTest.java b/sonar-core/src/test/java/org/sonar/core/measure/MeasureFilterExecutorTest.java new file mode 100644 index 00000000000..75922b5eea2 --- /dev/null +++ b/sonar-core/src/test/java/org/sonar/core/measure/MeasureFilterExecutorTest.java @@ -0,0 +1,359 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2012 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar 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. + * + * Sonar 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 Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.core.measure; + +import org.junit.Before; +import org.junit.Test; +import org.sonar.api.measures.Metric; +import org.sonar.api.utils.DateUtils; +import org.sonar.core.persistence.AbstractDaoTestCase; +import org.sonar.core.resource.ResourceDao; +import org.sonar.core.resource.SnapshotDto; + +import java.util.List; + +import static org.fest.assertions.Assertions.assertThat; + +public class MeasureFilterExecutorTest extends AbstractDaoTestCase { + private static final long JAVA_PROJECT_ID = 1L; + private static final long JAVA_FILE_BIG_ID = 3L; + private static final long JAVA_FILE_TINY_ID = 4L; + private static final long JAVA_PROJECT_SNAPSHOT_ID = 101L; + private static final long JAVA_FILE_BIG_SNAPSHOT_ID = 103L; + private static final long JAVA_FILE_TINY_SNAPSHOT_ID = 104L; + private static final long JAVA_PACKAGE_SNAPSHOT_ID = 102L; + private static final long PHP_PROJECT_ID = 10L; + private static final long PHP_SNAPSHOT_ID = 110L; + private static final Metric METRIC_LINES = new Metric.Builder("lines", "Lines", Metric.ValueType.INT).create().setId(1); + private static final Metric METRIC_PROFILE = new Metric.Builder("profile", "Profile", Metric.ValueType.STRING).create().setId(2); + private static final Metric METRIC_COVERAGE = new Metric.Builder("coverage", "Coverage", Metric.ValueType.FLOAT).create().setId(3); + + private MeasureFilterExecutor executor; + + @Before + public void before() { + executor = new MeasureFilterExecutor(getMyBatis(), getDatabase(), new ResourceDao(getMyBatis())); + setupData("shared"); + } + + @Test + public void invalid_filter_should_not_return_results() { + MeasureFilter filter = new MeasureFilter(); + // no qualifiers + assertThat(executor.execute(filter, null)).isEmpty(); + } + + @Test + public void filter_is_not_valid_if_missing_base_snapshot() { + MesasureFilterContext context = new MesasureFilterContext(); + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setOnBaseResourceChildren(true); + assertThat(MeasureFilterExecutor.isValid(filter, context)).isFalse(); + + context.setBaseSnapshot(new SnapshotDto().setId(123L)); + assertThat(MeasureFilterExecutor.isValid(filter, context)).isTrue(); + } + + @Test + public void filter_is_not_valid_if_anonymous_favourites() { + MesasureFilterContext context = new MesasureFilterContext(); + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setUserFavourites(true); + assertThat(MeasureFilterExecutor.isValid(filter, context)).isFalse(); + + context.setUserId(123L); + assertThat(MeasureFilterExecutor.isValid(filter, context)).isTrue(); + } + + @Test + public void projects_without_measure_conditions() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setSortOn(MeasureFilterSort.Field.LANGUAGE); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(2); + verifyJavaProject(rows.get(0)); + verifyPhpProject(rows.get(1)); + } + + @Test + public void test_default_sort() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA"); + + assertThat(filter.sort().isAsc()).isTrue(); + assertThat(filter.sort().field()).isEqualTo(MeasureFilterSort.Field.NAME); + assertThat(filter.sort().metric()).isNull(); + } + + @Test + public void sort_by_ascending_resource_name() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA").setSortAsc(true); + List rows = executor.execute(filter, null); + + // Big -> Tiny + assertThat(rows).hasSize(2); + verifyJavaBigFile(rows.get(0)); + verifyJavaTinyFile(rows.get(1)); + } + + @Test + public void sort_by_descending_resource_name() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA").setSortAsc(false); + List rows = executor.execute(filter, null); + + // Tiny -> Big + assertThat(rows).hasSize(2); + verifyJavaTinyFile(rows.get(0)); + verifyJavaBigFile(rows.get(1)); + } + + @Test + public void sort_by_ascending_text_measure() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setSortOnMetric(METRIC_PROFILE); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(2); + verifyPhpProject(rows.get(0));//php way + verifyJavaProject(rows.get(1));// Sonar way + } + + @Test + public void sort_by_descending_text_measure() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setSortOnMetric(METRIC_PROFILE).setSortAsc(false); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(2); + verifyJavaProject(rows.get(0));// Sonar way + verifyPhpProject(rows.get(1));//php way + } + + @Test + public void sort_by_missing_text_measure() { + // the metric 'profile' is not set on files + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA").setSortOnMetric(METRIC_PROFILE); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(2);//2 files randomly sorted + } + + @Test + public void sort_by_ascending_numeric_measure() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA").setSortOnMetric(METRIC_LINES); + List rows = executor.execute(filter, null); + + // Tiny -> Big + assertThat(rows).hasSize(2); + verifyJavaTinyFile(rows.get(0)); + verifyJavaBigFile(rows.get(1)); + } + + @Test + public void sort_by_descending_numeric_measure() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA").setSortOnMetric(METRIC_LINES).setSortAsc(false); + List rows = executor.execute(filter, null); + + // Big -> Tiny + assertThat(rows).hasSize(2); + verifyJavaBigFile(rows.get(0)); + verifyJavaTinyFile(rows.get(1)); + } + + @Test + public void sort_by_missing_numeric_measure() { + // coverage measures are not computed + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA").setSortOnMetric(METRIC_COVERAGE); + List rows = executor.execute(filter, null); + + // 2 files, random order + assertThat(rows).hasSize(2); + } + + @Test + public void sort_by_ascending_variation() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setSortOnMetric(METRIC_LINES).setSortOnPeriod(5); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(2); + verifyJavaProject(rows.get(0));// +400 + verifyPhpProject(rows.get(1));// +4900 + } + + @Test + public void sort_by_descending_variation() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK") + .setSortOnMetric(METRIC_LINES).setSortOnPeriod(5).setSortAsc(false); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(2); + verifyPhpProject(rows.get(0));// +4900 + verifyJavaProject(rows.get(1));// +400 + } + + @Test + public void sort_by_ascending_date() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setSortOn(MeasureFilterSort.Field.DATE); + List rows = executor.execute(filter, null); + + verifyJavaProject(rows.get(0));// 2008 + verifyPhpProject(rows.get(1));// 2012 + } + + @Test + public void sort_by_descending_date() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setSortOn(MeasureFilterSort.Field.DATE).setSortAsc(false); + List rows = executor.execute(filter, null); + + verifyPhpProject(rows.get(0));// 2012 + verifyJavaProject(rows.get(1));// 2008 + } + + @Test + public void condition_on_numeric_measure() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA") + .setSortOnMetric(METRIC_LINES) + .addCondition(new MeasureFilterValueCondition(METRIC_LINES, MeasureFilterValueCondition.Operator.GREATER, 200)); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(1); + verifyJavaBigFile(rows.get(0)); + } + + @Test + public void condition_on_measure_variation() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK") + .setSortOnMetric(METRIC_LINES) + .addCondition(new MeasureFilterValueCondition(METRIC_LINES, MeasureFilterValueCondition.Operator.GREATER, 1000).setPeriod(5)); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(1); + verifyPhpProject(rows.get(0)); + } + + @Test + public void multiple_conditions_on_numeric_measures() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA") + .setSortOnMetric(METRIC_LINES) + .addCondition(new MeasureFilterValueCondition(METRIC_LINES, MeasureFilterValueCondition.Operator.GREATER, 2)) + .addCondition(new MeasureFilterValueCondition(METRIC_LINES, MeasureFilterValueCondition.Operator.LESS_OR_EQUALS, 50)); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(1); + verifyJavaTinyFile(rows.get(0)); + } + + @Test + public void filter_by_language() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setResourceLanguages("java", "cobol"); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(1); + verifyJavaProject(rows.get(0)); + } + + @Test + public void filter_by_min_date() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setFromDate(DateUtils.parseDate("2012-12-13")); + List rows = executor.execute(filter, null); + + // php has been analyzed in 2012-12-13, whereas java project has been analyzed in 2008 + assertThat(rows).hasSize(1); + verifyPhpProject(rows.get(0)); + } + + @Test + public void filter_by_range_of_dates() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK") + .setFromDate(DateUtils.parseDate("2007-01-01")) + .setToDate(DateUtils.parseDate("2010-01-01")); + List rows = executor.execute(filter, null); + + // php has been analyzed in 2012-12-13, whereas java project has been analyzed in 2008 + assertThat(rows).hasSize(1); + verifyJavaProject(rows.get(0)); + } + + @Test + public void filter_by_resource_name() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK").setResourceName("PHP Proj"); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(1); + verifyPhpProject(rows.get(0)); + } + + @Test + public void filter_by_base_resource() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("CLA").setBaseResourceKey("java_project"); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(2); + // default sort is on resource name + verifyJavaBigFile(rows.get(0)); + verifyJavaTinyFile(rows.get(1)); + } + + @Test + public void filter_by_parent_resource() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK", "PAC", "CLA").setBaseResourceKey("java_project").setOnBaseResourceChildren(true); + List rows = executor.execute(filter, null); + + assertThat(rows).hasSize(1);// the package org.sonar.foo + assertThat(rows.get(0).getSnapshotId()).isEqualTo(JAVA_PACKAGE_SNAPSHOT_ID); + } + + @Test + public void filter_by_parent_without_children() { + MeasureFilter filter = new MeasureFilter().setResourceQualifiers("TRK", "PAC", "CLA").setBaseResourceKey("java_project:org.sonar.foo.Big").setOnBaseResourceChildren(true); + List rows = executor.execute(filter, null); + + assertThat(rows).isEmpty(); + } + + @Test + public void filter_by_user_favourites() { + MeasureFilter filter = new MeasureFilter().setUserFavourites(true); + List rows = executor.execute(filter, 50L); + + assertThat(rows).hasSize(2); + verifyJavaBigFile(rows.get(0)); + verifyPhpProject(rows.get(1)); + } + + private void verifyJavaProject(MeasureFilterRow row) { + assertThat(row.getSnapshotId()).isEqualTo(JAVA_PROJECT_SNAPSHOT_ID); + assertThat(row.getResourceId()).isEqualTo(JAVA_PROJECT_ID); + assertThat(row.getResourceRootId()).isEqualTo(JAVA_PROJECT_ID); + } + + private void verifyJavaBigFile(MeasureFilterRow row) { + assertThat(row.getSnapshotId()).isEqualTo(JAVA_FILE_BIG_SNAPSHOT_ID); + assertThat(row.getResourceId()).isEqualTo(JAVA_FILE_BIG_ID); + assertThat(row.getResourceRootId()).isEqualTo(JAVA_PROJECT_ID); + } + + private void verifyJavaTinyFile(MeasureFilterRow row) { + assertThat(row.getSnapshotId()).isEqualTo(JAVA_FILE_TINY_SNAPSHOT_ID); + assertThat(row.getResourceId()).isEqualTo(JAVA_FILE_TINY_ID); + assertThat(row.getResourceRootId()).isEqualTo(JAVA_PROJECT_ID); + } + + private void verifyPhpProject(MeasureFilterRow row) { + assertThat(row.getSnapshotId()).isEqualTo(PHP_SNAPSHOT_ID); + assertThat(row.getResourceId()).isEqualTo(PHP_PROJECT_ID); + assertThat(row.getResourceRootId()).isEqualTo(PHP_PROJECT_ID); + } +} diff --git a/sonar-core/src/test/java/org/sonar/core/persistence/AbstractDaoTestCase.java b/sonar-core/src/test/java/org/sonar/core/persistence/AbstractDaoTestCase.java index 98bf90b68bc..560cc8d8ea6 100644 --- a/sonar-core/src/test/java/org/sonar/core/persistence/AbstractDaoTestCase.java +++ b/sonar-core/src/test/java/org/sonar/core/persistence/AbstractDaoTestCase.java @@ -19,9 +19,6 @@ */ package org.sonar.core.persistence; -import org.dbunit.ext.mssql.InsertIdentityOperation; -import org.dbunit.operation.DatabaseOperation; - import com.google.common.collect.Maps; import com.google.common.io.Closeables; import org.apache.commons.io.IOUtils; @@ -34,6 +31,8 @@ import org.dbunit.database.IDatabaseConnection; import org.dbunit.dataset.*; import org.dbunit.dataset.filter.DefaultColumnFilter; import org.dbunit.dataset.xml.FlatXmlDataSet; +import org.dbunit.ext.mssql.InsertIdentityOperation; +import org.dbunit.operation.DatabaseOperation; import org.junit.Assert; import org.junit.Before; import org.sonar.api.config.Settings; @@ -78,6 +77,10 @@ public abstract class AbstractDaoTestCase { return myBatis; } + protected Database getDatabase() { + return database; + } + protected void setupData(String... testNames) { InputStream[] streams = new InputStream[testNames.length]; try { diff --git a/sonar-core/src/test/resources/org/sonar/core/measure/MeasureFilterExecutorTest/shared.xml b/sonar-core/src/test/resources/org/sonar/core/measure/MeasureFilterExecutorTest/shared.xml new file mode 100644 index 00000000000..7d94c344edc --- /dev/null +++ b/sonar-core/src/test/resources/org/sonar/core/measure/MeasureFilterExecutorTest/shared.xml @@ -0,0 +1,162 @@ + + \ No newline at end of file -- 2.39.5