001 /*
002 // $Id: //open/mondrian/src/main/mondrian/rolap/agg/AbstractQuerySpec.java#14 $
003 // This software is subject to the terms of the Common Public License
004 // Agreement, available at the following URL:
005 // http://www.opensource.org/licenses/cpl.html.
006 // Copyright (C) 2005-2008 Julian Hyde and others
007 // All Rights Reserved.
008 // You must accept the terms of that agreement to use this software.
009 */
010
011 package mondrian.rolap.agg;
012
013 import mondrian.rolap.RolapStar;
014 import mondrian.rolap.StarColumnPredicate;
015 import mondrian.rolap.StarPredicate;
016 import mondrian.rolap.sql.SqlQuery;
017 import mondrian.olap.Util;
018
019 import java.util.*;
020
021 /**
022 * Base class for {@link QuerySpec} implementations.
023 *
024 * @author jhyde
025 * @author Richard M. Emberson
026 * @version $Id: //open/mondrian/src/main/mondrian/rolap/agg/AbstractQuerySpec.java#14 $
027 */
028 public abstract class AbstractQuerySpec implements QuerySpec {
029 private final RolapStar star;
030 protected final boolean countOnly;
031
032 /**
033 * Creates an AbstractQuerySpec.
034 *
035 * @param star Star which defines columns of interest and their
036 * relationships
037 *
038 * @param countOnly If true, generate no GROUP BY clause, so the query
039 * returns a single row containing a grand total
040 */
041 protected AbstractQuerySpec(final RolapStar star, boolean countOnly) {
042 this.star = star;
043 this.countOnly = countOnly;
044 }
045
046 /**
047 * Creates a query object.
048 *
049 * @return a new query object
050 */
051 protected SqlQuery newSqlQuery() {
052 return getStar().getSqlQuery();
053 }
054
055 public RolapStar getStar() {
056 return star;
057 }
058
059 /**
060 * Adds a measure to a query.
061 *
062 * @param i Ordinal of measure
063 * @param sqlQuery Query object
064 */
065 protected void addMeasure(final int i, final SqlQuery sqlQuery) {
066 RolapStar.Measure measure = getMeasure(i);
067 Util.assertTrue(measure.getTable() == getStar().getFactTable());
068 measure.getTable().addToFrom(sqlQuery, false, true);
069
070 String exprInner = measure.generateExprString(sqlQuery);
071 String exprOuter = measure.getAggregator().getExpression(exprInner);
072 sqlQuery.addSelect(exprOuter, getMeasureAlias(i));
073 }
074
075 protected abstract boolean isAggregate();
076
077 protected void nonDistinctGenerateSql(SqlQuery sqlQuery)
078 {
079 // add constraining dimensions
080 RolapStar.Column[] columns = getColumns();
081 int arity = columns.length;
082 if (countOnly) {
083 sqlQuery.addSelect("count(*)");
084 }
085 for (int i = 0; i < arity; i++) {
086 RolapStar.Column column = columns[i];
087 RolapStar.Table table = column.getTable();
088 if (table.isFunky()) {
089 // this is a funky dimension -- ignore for now
090 continue;
091 }
092 table.addToFrom(sqlQuery, false, true);
093
094 String expr = column.generateExprString(sqlQuery);
095
096 StarColumnPredicate predicate = getColumnPredicate(i);
097 final String where = RolapStar.Column.createInExpr(
098 expr,
099 predicate,
100 column.getDatatype(),
101 sqlQuery);
102 if (!where.equals("true")) {
103 sqlQuery.addWhere(where);
104 }
105
106 if (countOnly) {
107 continue;
108 }
109
110 // some DB2 (AS400) versions throw an error, if a column alias is
111 // there and *not* used in a subsequent order by/group by
112 final SqlQuery.Dialect dialect = sqlQuery.getDialect();
113 if (dialect.isAS400()) {
114 sqlQuery.addSelect(expr, null);
115 } else {
116 sqlQuery.addSelect(expr, getColumnAlias(i));
117 }
118
119 if (isAggregate()) {
120 sqlQuery.addGroupBy(expr);
121 }
122
123 // Add ORDER BY clause to make the results deterministic.
124 // Derby has a bug with ORDER BY, so ignore it.
125 if (isOrdered()) {
126 sqlQuery.addOrderBy(expr, true, false, false);
127 }
128 }
129
130 // Add compound member predicates
131 extraPredicates(sqlQuery);
132
133 // add measures
134 if (!countOnly) {
135 for (int i = 0, count = getMeasureCount(); i < count; i++) {
136 addMeasure(i, sqlQuery);
137 }
138 }
139 }
140
141 /**
142 * Whether to add an ORDER BY clause to make results deterministic.
143 * Necessary if query returns more than one row and results are for
144 * human consumption.
145 *
146 * @return whether to sort query
147 */
148 protected boolean isOrdered() {
149 return false;
150 }
151
152 public String generateSqlQuery() {
153 SqlQuery sqlQuery = newSqlQuery();
154
155 int k = getDistinctMeasureCount();
156 final SqlQuery.Dialect dialect = sqlQuery.getDialect();
157 if (!dialect.allowsCountDistinct() && k > 0 ||
158 !dialect.allowsMultipleCountDistinct() && k > 1) {
159 distinctGenerateSql(sqlQuery, countOnly);
160 } else {
161 nonDistinctGenerateSql(sqlQuery);
162 }
163 if (!countOnly) {
164 addGroupingFunction(sqlQuery);
165 addGroupingSets(sqlQuery);
166 }
167 return sqlQuery.toString();
168 }
169
170 protected void addGroupingFunction(SqlQuery sqlQuery) {
171 throw new UnsupportedOperationException();
172 }
173
174 protected void addGroupingSets(SqlQuery sqlQuery) {
175 throw new UnsupportedOperationException();
176 }
177
178 /**
179 * Returns the number of measures whose aggregation function is
180 * distinct-count.
181 *
182 * @return Number of distinct-count measures
183 */
184 protected int getDistinctMeasureCount() {
185 int k = 0;
186 for (int i = 0, count = getMeasureCount(); i < count; i++) {
187 RolapStar.Measure measure = getMeasure(i);
188 if (measure.getAggregator().isDistinct()) {
189 ++k;
190 }
191 }
192 return k;
193 }
194
195 /**
196 * Generates a SQL query to retrieve the values in this segment using
197 * an algorithm which converts distinct-aggregates to non-distinct
198 * aggregates over subqueries.
199 *
200 * @param outerSqlQuery Query to modify
201 * @param countOnly If true, only generate a single row: no need to
202 * generate a GROUP BY clause or put any constraining columns in the
203 * SELECT clause
204 */
205 protected void distinctGenerateSql(
206 final SqlQuery outerSqlQuery,
207 boolean countOnly)
208 {
209 final SqlQuery.Dialect dialect = outerSqlQuery.getDialect();
210 // Generate something like
211 //
212 // select d0, d1, count(m0)
213 // from (
214 // select distinct dim1.x as d0, dim2.y as d1, f.z as m0
215 // from f, dim1, dim2
216 // where dim1.k = f.k1
217 // and dim2.k = f.k2) as dummyname
218 // group by d0, d1
219 //
220 // or, if countOnly=true
221 //
222 // select count(m0)
223 // from (
224 // select distinct f.z as m0
225 // from f, dim1, dim2
226 // where dim1.k = f.k1
227 // and dim2.k = f.k2) as dummyname
228
229 final SqlQuery innerSqlQuery = newSqlQuery();
230 innerSqlQuery.setDistinct(true);
231
232 // add constraining dimensions
233 RolapStar.Column[] columns = getColumns();
234 int arity = columns.length;
235 for (int i = 0; i < arity; i++) {
236 RolapStar.Column column = columns[i];
237 RolapStar.Table table = column.getTable();
238 if (table.isFunky()) {
239 // this is a funky dimension -- ignore for now
240 continue;
241 }
242 table.addToFrom(innerSqlQuery, false, true);
243 String expr = column.generateExprString(innerSqlQuery);
244 StarColumnPredicate predicate = getColumnPredicate(i);
245 final String where = RolapStar.Column.createInExpr(
246 expr,
247 predicate,
248 column.getDatatype(),
249 innerSqlQuery);
250 if (!where.equals("true")) {
251 innerSqlQuery.addWhere(where);
252 }
253 if (countOnly) {
254 continue;
255 }
256 final String alias = "d" + i;
257 innerSqlQuery.addSelect(expr, alias);
258 final String quotedAlias = dialect.quoteIdentifier(alias);
259 outerSqlQuery.addSelect(quotedAlias);
260 outerSqlQuery.addGroupBy(quotedAlias);
261 }
262
263 // add predicates not associated with columns
264 extraPredicates(innerSqlQuery);
265
266 // add measures
267 for (int i = 0, count = getMeasureCount(); i < count; i++) {
268 RolapStar.Measure measure = getMeasure(i);
269
270 Util.assertTrue(measure.getTable() == getStar().getFactTable());
271 measure.getTable().addToFrom(innerSqlQuery, false, true);
272
273 String alias = getMeasureAlias(i);
274 String expr = measure.generateExprString(outerSqlQuery);
275 innerSqlQuery.addSelect(expr, alias);
276
277 outerSqlQuery.addSelect(
278 measure.getAggregator().getNonDistinctAggregator().getExpression(
279 dialect.quoteIdentifier(alias)));
280 }
281 outerSqlQuery.addFrom(innerSqlQuery, "dummyname", true);
282 }
283
284 /**
285 * Adds predicates not associated with columns.
286 *
287 * @param sqlQuery Query
288 */
289 protected void extraPredicates(SqlQuery sqlQuery) {
290 List<StarPredicate> predicateList = getPredicateList();
291 for (StarPredicate predicate : predicateList) {
292 for (RolapStar.Column column :
293 predicate.getConstrainedColumnList()) {
294 final RolapStar.Table table = column.getTable();
295 table.addToFrom(sqlQuery, false, true);
296 }
297 StringBuilder buf = new StringBuilder();
298 predicate.toSql(sqlQuery, buf);
299 final String where = buf.toString();
300 if (!where.equals("true")) {
301 sqlQuery.addWhere(where);
302 }
303 }
304 }
305
306 /**
307 * Returns a list of predicates not associated with a particular column.
308 *
309 * @return list of non-column predicates
310 */
311 protected List<StarPredicate> getPredicateList() {
312 return Collections.emptyList();
313 }
314 }
315
316
317 // End AbstractQuerySpec.java