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 */
020 package org.sonar.server.filters;
021
022 import org.apache.commons.lang.StringUtils;
023 import org.slf4j.Logger;
024 import org.slf4j.LoggerFactory;
025 import org.sonar.api.ServerComponent;
026 import org.sonar.api.database.DatabaseSession;
027 import org.sonar.api.database.model.Snapshot;
028 import org.sonar.api.utils.SonarException;
029 import org.sonar.api.utils.TimeProfiler;
030
031 import javax.persistence.Query;
032 import java.util.Collections;
033
034 public class FilterExecutor implements ServerComponent {
035 private static final Logger LOG = LoggerFactory.getLogger(FilterExecutor.class);
036 private static final int SQL_INITIAL_SIZE = 1000;
037 private DatabaseSession session;
038
039 public FilterExecutor(DatabaseSession session) {
040 this.session = session;
041 }
042
043 public FilterResult execute(Filter filter) {
044 if (filter.mustReturnEmptyResult()) {
045 return new FilterResult(filter, Collections.emptyList());
046 }
047
048 String sql = null;
049 try {
050 TimeProfiler profiler = new TimeProfiler(FilterExecutor.class).setLevelToDebug().start("Build/execute SQL query");
051 sql = toSql(filter);
052 LOG.debug("SQL: " + sql);
053 Query query = session.getEntityManager().createNativeQuery(sql);
054 setHqlParameters(filter, query);
055 FilterResult result = new FilterResult(filter, query.getResultList());
056 profiler.stop();
057
058 profiler.start("Process rows");
059 result.removeUnvalidRows();
060 profiler.stop();
061
062 profiler.start("Sort rows");
063 result.sort();
064 profiler.stop();
065 return result;
066
067 } catch (Exception e) {
068 throw new SonarException("Fail to execute filter: " + filter.toString() + ", sql=" + sql, e);
069 }
070 }
071
072 private String toSql(Filter filter) {
073 StringBuilder sql = new StringBuilder(SQL_INITIAL_SIZE);
074 addSelectColumns(filter, sql);
075 addFromClause(filter, sql);
076 addWhereClause(filter, sql);
077 return sql.toString();
078 }
079
080 private void addSelectColumns(Filter filter, StringBuilder sql) {
081 sql.append("SELECT s.id, MAX(s.project_id) as pid, MAX(s.root_project_id) as rpid");
082 if (filter.isSortedByLanguage()) {
083 sql.append(", MAX(p.language) as lang ");
084
085 } else if (filter.isSortedByName()) {
086 sql.append(", MAX(p.long_name) as name ");
087
088 } else if (filter.isSortedByDate()) {
089 sql.append(", MAX(s.created_at) as createdat ");
090
091 } else if (filter.isSortedByVersion()) {
092 sql.append(", MAX(s.version) as version ");
093 }
094 if (filter.getSortedMetricId() != null) {
095 sql.append(", MAX(CASE WHEN pm.metric_id=");
096 sql.append(filter.getSortedMetricId());
097 sql.append(" THEN ");
098 sql.append(filter.getColumnToSort());
099 sql.append(" ELSE NULL END) AS sortvalue");
100 sql.append(" ");
101 }
102 for (int index = 0; index < filter.getMeasureCriteria().size(); index++) {
103 MeasureCriterion criterion = filter.getMeasureCriteria().get(index);
104 String column = (criterion.isVariation() ? Filter.getVariationColumn(filter.getPeriodIndex()) : "value");
105 sql.append(", MAX(CASE WHEN pm.metric_id=");
106 sql.append(criterion.getMetricId());
107 sql.append(" AND pm.");
108 sql.append(column);
109 sql.append(criterion.getOperator());
110 sql.append(criterion.getValue());
111 sql.append(" THEN ");
112 sql.append(column);
113 sql.append(" ELSE NULL END) AS crit_");
114 sql.append(index);
115 sql.append(" ");
116 }
117 }
118
119 private void addFromClause(Filter filter, StringBuilder sql) {
120 sql.append(" FROM snapshots s ");
121 if (filter.mustJoinMeasuresTable()) {
122 sql.append(" INNER JOIN project_measures pm ON s.id=pm.snapshot_id ");
123 }
124 sql.append(" INNER JOIN projects p ON s.project_id=p.id ");
125 }
126
127 private void addWhereClause(Filter filter, StringBuilder sql) {
128 sql.append(" WHERE ");
129 if (filter.mustJoinMeasuresTable()) {
130 if (filter.hasMeasureCriteria()) {
131 sql.append(" ( ");
132 int index = 0;
133 while (index < filter.getMeasureCriteria().size()) {
134 if (index > 0) {
135 sql.append(" OR ");
136 }
137 MeasureCriterion criterion = filter.getMeasureCriteria().get(index);
138 String column = (criterion.isVariation() ? Filter.getVariationColumn(filter.getPeriodIndex()) : "value");
139 sql.append("(pm.metric_id=").append(criterion.getMetricId()).append(" and pm.").append(column)
140 .append(criterion.getOperator()).append(criterion.getValue()).append(")");
141 index++;
142 }
143
144 if (filter.getSortedMetricId() != null && !filter.hasMeasureCriteriaOnMetric(filter.getSortedMetricId())) {
145 sql.append(" OR (pm.metric_id=").append(filter.getSortedMetricId()).append(") ");
146 }
147
148 sql.append(" ) AND ");
149 }
150 sql.append(" pm.rule_id IS NULL AND pm.rule_priority IS NULL");
151 sql.append(" AND pm.characteristic_id IS NULL");
152 sql.append(" AND pm.person_id IS NULL");
153 sql.append(" AND ");
154 }
155 sql.append(" s.status=:status AND s.islast=:islast ");
156 if (filter.getScopes() != null) {
157 sql.append(filter.getScopes().isEmpty() ? " AND s.scope IS NULL " : " AND s.scope IN (:scopes) ");
158 }
159 if (filter.hasQualifiers()) {
160 sql.append(" AND s.qualifier IN (:qualifiers) ");
161 } else {
162 sql.append(" AND s.qualifier IS NULL ");
163 }
164 if (filter.hasLanguages()) {
165 sql.append(" AND p.language IN (:languages) ");
166 }
167 if (filter.getFavouriteIds() != null) {
168 sql.append(filter.getFavouriteIds().isEmpty() ? " AND s.project_id IS NULL " : " AND s.project_id IN (:favourites) ");
169 }
170 if (filter.hasBaseSnapshot()) {
171 sql.append(" AND s.root_snapshot_id=:root_sid AND s.path LIKE :path ");
172 }
173 if (filter.getDateCriterion() != null) {
174 sql.append(" AND s.created_at");
175 sql.append(filter.getDateCriterion().getOperator());
176 sql.append(" :date ");
177 }
178 if (StringUtils.isNotBlank(filter.getKeyRegexp())) {
179 sql.append(" AND UPPER(p.kee) LIKE :kee");
180 }
181 if (StringUtils.isNotBlank(filter.getNameRegexp())) {
182 sql.append(" AND UPPER(p.long_name) LIKE :name");
183 }
184 if (!filter.isViewContext()) {
185 sql.append(" AND p.copy_resource_id IS NULL ");
186 }
187 sql.append(" GROUP BY s.id");
188 }
189
190 private void setHqlParameters(Filter filter, Query query) {
191 query.setParameter("status", Snapshot.STATUS_PROCESSED);
192 query.setParameter("islast", true);
193 if (filter.hasScopes()) {
194 query.setParameter("scopes", filter.getScopes());
195 }
196 if (filter.hasQualifiers()) {
197 query.setParameter("qualifiers", filter.getQualifiers());
198 }
199 if (filter.hasLanguages()) {
200 query.setParameter("languages", filter.getLanguages());
201 }
202 if (filter.hasFavouriteIds()) {
203 query.setParameter("favourites", filter.getFavouriteIds());
204 }
205 if (filter.getDateCriterion() != null) {
206 query.setParameter("date", filter.getDateCriterion().getDate());
207 }
208 if (filter.hasBaseSnapshot()) {
209 query.setParameter("root_sid", filter.getRootSnapshotId());
210 query.setParameter("path", new StringBuilder().append(
211 filter.getBaseSnapshotPath()).append(filter.getBaseSnapshotId()).append(".%").toString());
212 }
213 if (StringUtils.isNotBlank(filter.getKeyRegexp())) {
214 query.setParameter("kee", StringUtils.upperCase(StringUtils.replaceChars(filter.getKeyRegexp(), '*', '%')));
215 }
216 if (StringUtils.isNotBlank(filter.getNameRegexp())) {
217 query.setParameter("name", StringUtils.upperCase(StringUtils.replaceChars(filter.getNameRegexp(), '*', '%')));
218 }
219 }
220 }