Source code

001/*
002 * Sonar, open source software quality management tool.
003 * Copyright (C) 2008-2012 SonarSource
004 * mailto:contact AT sonarsource DOT com
005 *
006 * Sonar is free software; you can redistribute it and/or
007 * modify it under the terms of the GNU Lesser General Public
008 * License as published by the Free Software Foundation; either
009 * version 3 of the License, or (at your option) any later version.
010 *
011 * Sonar is distributed in the hope that it will be useful,
012 * but WITHOUT ANY WARRANTY; without even the implied warranty of
013 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
014 * Lesser General Public License for more details.
015 *
016 * You should have received a copy of the GNU Lesser General Public
017 * License along with Sonar; if not, write to the Free Software
018 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02
019 */
020package org.sonar.server.filters;
021
022import com.google.common.annotations.VisibleForTesting;
023import org.apache.commons.lang.StringUtils;
024import org.slf4j.Logger;
025import org.slf4j.LoggerFactory;
026import org.sonar.api.ServerComponent;
027import org.sonar.api.database.DatabaseSession;
028import org.sonar.api.database.model.Snapshot;
029import org.sonar.api.utils.SonarException;
030import org.sonar.api.utils.TimeProfiler;
031import org.sonar.core.persistence.Database;
032import org.sonar.core.persistence.dialect.Dialect;
033import org.sonar.core.persistence.dialect.MsSql;
034
035import javax.persistence.Query;
036
037import java.util.Collections;
038
039public class FilterExecutor implements ServerComponent {
040  private static final Logger LOG = LoggerFactory.getLogger(FilterExecutor.class);
041  private static final int SQL_INITIAL_SIZE = 1000;
042  private DatabaseSession session;
043  private Dialect dialect;
044
045  public FilterExecutor(DatabaseSession session, Database database) {
046    this(session, database.getDialect());
047  }
048
049  @VisibleForTesting
050  FilterExecutor(DatabaseSession session, Dialect dialect) {
051    this.session = session;
052    this.dialect = dialect;
053  }
054
055  public FilterResult execute(Filter filter) {
056    if (filter.mustReturnEmptyResult()) {
057      return new FilterResult(filter, Collections.<Object[]> emptyList());
058    }
059
060    String sql = null;
061    try {
062      TimeProfiler profiler = new TimeProfiler(FilterExecutor.class).setLevelToDebug().start("Build/execute SQL query");
063      sql = toSql(filter);
064      LOG.debug("SQL: " + sql);
065      Query query = session.getEntityManager().createNativeQuery(sql);
066      setHqlParameters(filter, query);
067      FilterResult result = new FilterResult(filter, query.getResultList());
068      profiler.stop();
069
070      profiler.start("Process rows");
071      result.removeUnvalidRows();
072      profiler.stop();
073
074      profiler.start("Sort rows");
075      result.sort();
076      profiler.stop();
077      return result;
078
079    } catch (Exception e) {
080      throw new SonarException("Fail to execute filter: " + filter.toString() + ", sql=" + sql, e);
081    }
082  }
083
084  @VisibleForTesting
085  String toSql(Filter filter) {
086    StringBuilder sql = new StringBuilder(SQL_INITIAL_SIZE);
087    addSelectColumns(filter, sql);
088    addFromClause(filter, sql);
089    addWhereClause(filter, sql);
090    return sql.toString();
091  }
092
093  private void addSelectColumns(Filter filter, StringBuilder sql) {
094    sql.append("SELECT s.id, MAX(s.project_id) as pid, MAX(s.root_project_id) as rpid");
095    if (filter.isSortedByLanguage()) {
096      sql.append(", MAX(p.language) as lang ");
097
098    } else if (filter.isSortedByName()) {
099      sql.append(", MAX(p.long_name) as name ");
100
101    } else if (filter.isSortedByKey()) {
102      sql.append(", MAX(p.kee) as kee ");
103
104    } else if (filter.isSortedByDate()) {
105      sql.append(", MAX(s.created_at) as createdat ");
106
107    } else if (filter.isSortedByVersion()) {
108      sql.append(", MAX(s.version) as version ");
109    }
110    if (filter.getSortedMetricId() != null) {
111      sql.append(", MAX(CASE WHEN pm.metric_id=");
112      sql.append(filter.getSortedMetricId());
113      sql.append(" THEN ");
114      sql.append(filter.getColumnToSort());
115      sql.append(" ELSE NULL END) AS sortvalue");
116      sql.append(" ");
117    }
118    for (int index = 0; index < filter.getMeasureCriteria().size(); index++) {
119      MeasureCriterion criterion = filter.getMeasureCriteria().get(index);
120      String column = (criterion.isVariation() ? Filter.getVariationColumn(filter.getPeriodIndex()) : "value");
121      sql.append(", MAX(CASE WHEN pm.metric_id=");
122      sql.append(criterion.getMetricId());
123      sql.append(" AND pm.");
124      sql.append(column);
125      sql.append(criterion.getOperator());
126      sql.append(criterion.getValue());
127      sql.append(" THEN ");
128      sql.append(column);
129      sql.append(" ELSE NULL END) AS crit_");
130      sql.append(index);
131      sql.append(" ");
132    }
133  }
134
135  private void addFromClause(Filter filter, StringBuilder sql) {
136    sql.append(" FROM snapshots s ");
137    if (filter.mustJoinMeasuresTable()) {
138      sql.append(" INNER JOIN project_measures pm ");
139      if (MsSql.ID.equals(dialect.getId())) {
140        // SONAR-3422
141        sql.append(" WITH (INDEX(measures_sid_metric)) ");
142      }
143      sql.append(" ON s.id=pm.snapshot_id ");
144    }
145    sql.append(" INNER JOIN projects p ON s.project_id=p.id ");
146  }
147
148  private void addWhereClause(Filter filter, StringBuilder sql) {
149    sql.append(" WHERE ");
150    if (filter.mustJoinMeasuresTable()) {
151      if (filter.hasMeasureCriteria()) {
152        sql.append(" ( ");
153        int index = 0;
154        while (index < filter.getMeasureCriteria().size()) {
155          if (index > 0) {
156            sql.append(" OR ");
157          }
158          MeasureCriterion criterion = filter.getMeasureCriteria().get(index);
159          String column = (criterion.isVariation() ? Filter.getVariationColumn(filter.getPeriodIndex()) : "value");
160          sql.append("(pm.metric_id=").append(criterion.getMetricId()).append(" and pm.").append(column)
161              .append(criterion.getOperator()).append(criterion.getValue()).append(")");
162          index++;
163        }
164
165        if (filter.getSortedMetricId() != null && !filter.hasMeasureCriteriaOnMetric(filter.getSortedMetricId())) {
166          sql.append(" OR (pm.metric_id=").append(filter.getSortedMetricId()).append(") ");
167        }
168
169        sql.append(" ) AND ");
170      }
171      sql.append(" pm.rule_id IS NULL AND pm.rule_priority IS NULL");
172      sql.append(" AND pm.characteristic_id IS NULL");
173      sql.append(" AND pm.person_id IS NULL");
174      sql.append(" AND ");
175    }
176    sql.append(" s.status=:status AND s.islast=:islast ");
177    if (filter.getScopes() != null) {
178      sql.append(filter.getScopes().isEmpty() ? " AND s.scope IS NULL " : " AND s.scope IN (:scopes) ");
179    }
180    if (filter.hasQualifiers()) {
181      sql.append(" AND s.qualifier IN (:qualifiers) ");
182    } else if (!filter.isOnDirectChildren()) {
183      // no criteria -> we should not display all rows but no rows
184      sql.append(" AND s.qualifier IS NULL ");
185    }
186    if (filter.hasLanguages()) {
187      sql.append(" AND p.language IN (:languages) ");
188    }
189    if (filter.getFavouriteIds() != null) {
190      sql.append(filter.getFavouriteIds().isEmpty() ? " AND s.project_id IS NULL " : " AND s.project_id IN (:favourites) ");
191    }
192    if (filter.hasBaseSnapshot()) {
193      if (filter.isOnDirectChildren()) {
194        sql.append(" AND s.parent_snapshot_id=:parent_sid ");
195      } else {
196        sql.append(" AND s.root_snapshot_id=:root_sid AND s.path LIKE :path ");
197      }
198    }
199    if (filter.getDateCriterion() != null) {
200      sql.append(" AND s.created_at");
201      sql.append(filter.getDateCriterion().getOperator());
202      sql.append(" :date ");
203    }
204    if (StringUtils.isNotBlank(filter.getKeyRegexp())) {
205      sql.append(" AND UPPER(p.kee) LIKE :kee");
206    }
207    if (StringUtils.isNotBlank(filter.getNameRegexp())) {
208      sql.append(" AND UPPER(p.long_name) LIKE :name");
209    }
210    if (!filter.hasBaseSnapshot()) {
211      sql.append(" AND p.copy_resource_id IS NULL ");
212    }
213    sql.append(" GROUP BY s.id");
214  }
215
216  private void setHqlParameters(Filter filter, Query query) {
217    query.setParameter("status", Snapshot.STATUS_PROCESSED);
218    query.setParameter("islast", true);
219    if (filter.hasScopes()) {
220      query.setParameter("scopes", filter.getScopes());
221    }
222    if (filter.hasQualifiers()) {
223      query.setParameter("qualifiers", filter.getQualifiers());
224    }
225    if (filter.hasLanguages()) {
226      query.setParameter("languages", filter.getLanguages());
227    }
228    if (filter.hasFavouriteIds()) {
229      query.setParameter("favourites", filter.getFavouriteIds());
230    }
231    if (filter.getDateCriterion() != null) {
232      query.setParameter("date", filter.getDateCriterion().getDate());
233    }
234    if (filter.hasBaseSnapshot()) {
235      if (filter.isOnDirectChildren()) {
236        query.setParameter("parent_sid", filter.getBaseSnapshotId());
237      } else {
238        query.setParameter("root_sid", filter.getRootSnapshotId());
239        query.setParameter("path", new StringBuilder().append(
240            filter.getBaseSnapshotPath()).append(filter.getBaseSnapshotId()).append(".%").toString());
241      }
242    }
243    if (StringUtils.isNotBlank(filter.getKeyRegexp())) {
244      query.setParameter("kee", StringUtils.upperCase(StringUtils.replaceChars(filter.getKeyRegexp(), '*', '%')));
245    }
246    if (StringUtils.isNotBlank(filter.getNameRegexp())) {
247      query.setParameter("name", StringUtils.upperCase(StringUtils.replaceChars(filter.getNameRegexp(), '*', '%')));
248    }
249  }
250}