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.isSortedByKey()) {
089 sql.append(", MAX(p.kee) as kee ");
090
091 } else if (filter.isSortedByDate()) {
092 sql.append(", MAX(s.created_at) as createdat ");
093
094 } else if (filter.isSortedByVersion()) {
095 sql.append(", MAX(s.version) as version ");
096 }
097 if (filter.getSortedMetricId() != null) {
098 sql.append(", MAX(CASE WHEN pm.metric_id=");
099 sql.append(filter.getSortedMetricId());
100 sql.append(" THEN ");
101 sql.append(filter.getColumnToSort());
102 sql.append(" ELSE NULL END) AS sortvalue");
103 sql.append(" ");
104 }
105 for (int index = 0; index < filter.getMeasureCriteria().size(); index++) {
106 MeasureCriterion criterion = filter.getMeasureCriteria().get(index);
107 String column = (criterion.isVariation() ? Filter.getVariationColumn(filter.getPeriodIndex()) : "value");
108 sql.append(", MAX(CASE WHEN pm.metric_id=");
109 sql.append(criterion.getMetricId());
110 sql.append(" AND pm.");
111 sql.append(column);
112 sql.append(criterion.getOperator());
113 sql.append(criterion.getValue());
114 sql.append(" THEN ");
115 sql.append(column);
116 sql.append(" ELSE NULL END) AS crit_");
117 sql.append(index);
118 sql.append(" ");
119 }
120 }
121
122 private void addFromClause(Filter filter, StringBuilder sql) {
123 sql.append(" FROM snapshots s ");
124 if (filter.mustJoinMeasuresTable()) {
125 sql.append(" INNER JOIN project_measures pm ON s.id=pm.snapshot_id ");
126 }
127 sql.append(" INNER JOIN projects p ON s.project_id=p.id ");
128 }
129
130 private void addWhereClause(Filter filter, StringBuilder sql) {
131 sql.append(" WHERE ");
132 if (filter.mustJoinMeasuresTable()) {
133 if (filter.hasMeasureCriteria()) {
134 sql.append(" ( ");
135 int index = 0;
136 while (index < filter.getMeasureCriteria().size()) {
137 if (index > 0) {
138 sql.append(" OR ");
139 }
140 MeasureCriterion criterion = filter.getMeasureCriteria().get(index);
141 String column = (criterion.isVariation() ? Filter.getVariationColumn(filter.getPeriodIndex()) : "value");
142 sql.append("(pm.metric_id=").append(criterion.getMetricId()).append(" and pm.").append(column)
143 .append(criterion.getOperator()).append(criterion.getValue()).append(")");
144 index++;
145 }
146
147 if (filter.getSortedMetricId() != null && !filter.hasMeasureCriteriaOnMetric(filter.getSortedMetricId())) {
148 sql.append(" OR (pm.metric_id=").append(filter.getSortedMetricId()).append(") ");
149 }
150
151 sql.append(" ) AND ");
152 }
153 sql.append(" pm.rule_id IS NULL AND pm.rule_priority IS NULL");
154 sql.append(" AND pm.characteristic_id IS NULL");
155 sql.append(" AND pm.person_id IS NULL");
156 sql.append(" AND ");
157 }
158 sql.append(" s.status=:status AND s.islast=:islast ");
159 if (filter.getScopes() != null) {
160 sql.append(filter.getScopes().isEmpty() ? " AND s.scope IS NULL " : " AND s.scope IN (:scopes) ");
161 }
162 if (filter.hasQualifiers()) {
163 sql.append(" AND s.qualifier IN (:qualifiers) ");
164 } else {
165 sql.append(" AND s.qualifier IS NULL ");
166 }
167 if (filter.hasLanguages()) {
168 sql.append(" AND p.language IN (:languages) ");
169 }
170 if (filter.getFavouriteIds() != null) {
171 sql.append(filter.getFavouriteIds().isEmpty() ? " AND s.project_id IS NULL " : " AND s.project_id IN (:favourites) ");
172 }
173 if (filter.hasBaseSnapshot()) {
174 sql.append(" AND s.root_snapshot_id=:root_sid AND s.path LIKE :path ");
175 }
176 if (filter.getDateCriterion() != null) {
177 sql.append(" AND s.created_at");
178 sql.append(filter.getDateCriterion().getOperator());
179 sql.append(" :date ");
180 }
181 if (StringUtils.isNotBlank(filter.getKeyRegexp())) {
182 sql.append(" AND UPPER(p.kee) LIKE :kee");
183 }
184 if (StringUtils.isNotBlank(filter.getNameRegexp())) {
185 sql.append(" AND UPPER(p.long_name) LIKE :name");
186 }
187 if (!filter.isViewContext()) {
188 sql.append(" AND p.copy_resource_id IS NULL ");
189 }
190 sql.append(" GROUP BY s.id");
191 }
192
193 private void setHqlParameters(Filter filter, Query query) {
194 query.setParameter("status", Snapshot.STATUS_PROCESSED);
195 query.setParameter("islast", true);
196 if (filter.hasScopes()) {
197 query.setParameter("scopes", filter.getScopes());
198 }
199 if (filter.hasQualifiers()) {
200 query.setParameter("qualifiers", filter.getQualifiers());
201 }
202 if (filter.hasLanguages()) {
203 query.setParameter("languages", filter.getLanguages());
204 }
205 if (filter.hasFavouriteIds()) {
206 query.setParameter("favourites", filter.getFavouriteIds());
207 }
208 if (filter.getDateCriterion() != null) {
209 query.setParameter("date", filter.getDateCriterion().getDate());
210 }
211 if (filter.hasBaseSnapshot()) {
212 query.setParameter("root_sid", filter.getRootSnapshotId());
213 query.setParameter("path", new StringBuilder().append(
214 filter.getBaseSnapshotPath()).append(filter.getBaseSnapshotId()).append(".%").toString());
215 }
216 if (StringUtils.isNotBlank(filter.getKeyRegexp())) {
217 query.setParameter("kee", StringUtils.upperCase(StringUtils.replaceChars(filter.getKeyRegexp(), '*', '%')));
218 }
219 if (StringUtils.isNotBlank(filter.getNameRegexp())) {
220 query.setParameter("name", StringUtils.upperCase(StringUtils.replaceChars(filter.getNameRegexp(), '*', '%')));
221 }
222 }
223 }