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