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) 2004-2005 TONBELLER AG
008// Copyright (C) 2005-2005 Julian Hyde
009// Copyright (C) 2005-2013 Pentaho and others
010// All Rights Reserved.
011*/
012package mondrian.rolap;
013
014import mondrian.calc.TupleList;
015import mondrian.calc.impl.ListTupleList;
016import mondrian.calc.impl.UnaryTupleList;
017import mondrian.olap.*;
018import mondrian.olap.fun.FunUtil;
019import mondrian.resource.MondrianResource;
020import mondrian.rolap.agg.AggregationManager;
021import mondrian.rolap.agg.CellRequest;
022import mondrian.rolap.aggmatcher.AggStar;
023import mondrian.rolap.sql.*;
024import mondrian.server.Locus;
025import mondrian.server.monitor.SqlStatementEvent;
026import mondrian.util.Pair;
027
028import org.apache.log4j.Logger;
029
030import java.sql.ResultSet;
031import java.sql.SQLException;
032import java.util.*;
033
034import javax.sql.DataSource;
035
036/**
037 * Reads the members of a single level (level.members) or of multiple levels
038 * (crossjoin).
039 *
040 * <p>Allows the result to be restricted by a {@link TupleConstraint}. So
041 * the SqlTupleReader can also read Member.Descendants (which is level.members
042 * restricted to a common parent) and member.children (which is a special case
043 * of member.descendants). Other constraints, especially for the current slicer
044 * or evaluation context, are possible.
045 *
046 * <h3>Caching</h3>
047 *
048 * <p>When a SqlTupleReader reads level.members, it groups the result into
049 * parent/children pairs and puts them into the cache. In order that these can
050 * be found later when the children of a parent are requested, a matching
051 * constraint must be provided for every parent.
052 *
053 * <ul>
054 *
055 * <li>When reading members from a single level, then the constraint is not
056 * required to join the fact table in
057 * {@link TupleConstraint#addLevelConstraint(mondrian.rolap.sql.SqlQuery, RolapCube, mondrian.rolap.aggmatcher.AggStar, RolapLevel)}
058 * although it may do so to restrict
059 * the result. Also it is permitted to cache the parent/children from all
060 * members in MemberCache, so
061 * {@link TupleConstraint#getMemberChildrenConstraint(RolapMember)}
062 * should not return null.</li>
063 *
064 * <li>When reading multiple levels (i.e. we are performing a crossjoin),
065 * then we can not store the parent/child pairs in the MemberCache and
066 * {@link TupleConstraint#getMemberChildrenConstraint(RolapMember)}
067 * must return null. Also
068 * {@link TupleConstraint#addConstraint(mondrian.rolap.sql.SqlQuery, mondrian.rolap.RolapCube, mondrian.rolap.aggmatcher.AggStar)}
069 * is required to join the fact table for the levels table.</li>
070 * </ul>
071 *
072 * @author av
073 * @since Nov 11, 2005
074 */
075public class SqlTupleReader implements TupleReader {
076    private static final Logger LOGGER =
077        Logger.getLogger(SqlTupleReader.class);
078    protected final TupleConstraint constraint;
079    List<TargetBase> targets = new ArrayList<TargetBase>();
080    int maxRows = 0;
081
082    /**
083     * How many members could not be instantiated in this iteration. This
084     * phenomenon occurs in a parent-child hierarchy, where a member cannot be
085     * created before its parent. Populating the hierarchy will take multiple
086     * passes and will terminate in success when missedMemberCount == 0 at the
087     * end of a pass, or failure if a pass generates failures but does not
088     * manage to load any more members.
089     */
090    private int missedMemberCount;
091    private static final String UNION = "union";
092
093    /**
094     * Helper class for SqlTupleReader;
095     * keeps track of target levels and constraints for adding to sql query.
096     */
097    private class Target extends TargetBase {
098        final MemberCache cache;
099
100        RolapLevel[] levels;
101        int levelDepth;
102        boolean parentChild;
103        List<RolapMember> members;
104        final HashMap<Object, RolapMember> keyToMember =
105            new HashMap<Object, RolapMember>();
106        List<List<RolapMember>> siblings;
107        // if set, the rows for this target come from the array rather
108        // than native sql
109        // current member within the current result set row
110        // for this target
111
112        public Target(
113            RolapLevel level,
114            MemberBuilder memberBuilder,
115            List<RolapMember> srcMembers)
116        {
117            super(srcMembers, level, memberBuilder);
118            this.cache = memberBuilder.getMemberCache();
119        }
120
121        public void open() {
122            levels = (RolapLevel[]) level.getHierarchy().getLevels();
123            setList(new ArrayList<RolapMember>());
124            levelDepth = level.getDepth();
125            parentChild = level.isParentChild();
126            // members[i] is the current member of level#i, and siblings[i]
127            // is the current member of level#i plus its siblings
128            members =
129                new ArrayList<RolapMember>(
130                    Collections.<RolapMember>nCopies(levels.length, null));
131            siblings = new ArrayList<List<RolapMember>>();
132            for (int i = 0; i < levels.length + 1; i++) {
133                siblings.add(new ArrayList<RolapMember>());
134            }
135        }
136
137        int internalAddRow(SqlStatement stmt, int column)
138            throws SQLException
139        {
140            RolapMember member = null;
141            if (getCurrMember() != null) {
142                setCurrMember(member);
143            } else {
144                boolean checkCacheStatus = true;
145                for (int i = 0; i <= levelDepth; i++) {
146                    RolapLevel childLevel = levels[i];
147                    if (childLevel.isAll()) {
148                        member = memberBuilder.allMember();
149                        continue;
150                    }
151                    RolapMember parentMember = member;
152                    final List<SqlStatement.Accessor> accessors =
153                        stmt.getAccessors();
154                    if (parentChild) {
155                        Object parentValue =
156                            accessors.get(column++).get();
157                        if (parentValue == null
158                            || parentValue.toString().equals(
159                                childLevel.getNullParentValue()))
160                        {
161                            // member is at top of hierarchy; its parent is the
162                            // 'all' member. Convert null to placeholder value
163                            // for uniformity in hashmaps.
164                            parentValue = RolapUtil.sqlNullValue;
165                        } else {
166                            Object parentKey =
167                                cache.makeKey(
168                                    member,
169                                    parentValue);
170                            parentMember = cache.getMember(parentKey);
171                            if (parentMember == null) {
172                                // Maybe it wasn't caching.
173                                // We have an intermediate volatile map.
174                                parentMember = keyToMember.get(parentValue);
175                            }
176                            if (parentMember == null) {
177                                LOGGER.warn(
178                                    MondrianResource.instance()
179                                        .LevelTableParentNotFound.str(
180                                            childLevel.getUniqueName(),
181                                            String.valueOf(parentValue)));
182                            }
183                        }
184                    }
185                    Object value = accessors.get(column++).get();
186                    if (value == null) {
187                        value = RolapUtil.sqlNullValue;
188                    }
189                    Object captionValue;
190                    if (childLevel.hasCaptionColumn()) {
191                        captionValue = accessors.get(column++).get();
192                    } else {
193                        captionValue = null;
194                    }
195                    Object key;
196                    if (parentChild) {
197                        key = cache.makeKey(member, value);
198                    } else {
199                        key = cache.makeKey(parentMember, value);
200                    }
201                    member = cache.getMember(key, checkCacheStatus);
202                    checkCacheStatus = false; // only check the first time
203                    if (member == null) {
204                        if (constraint instanceof
205                            RolapNativeCrossJoin.NonEmptyCrossJoinConstraint
206                            && childLevel.isParentChild())
207                        {
208                            member =
209                                castToNonEmptyCJConstraint(constraint)
210                                    .findMember(value);
211                        }
212                        if (member == null) {
213                            member = memberBuilder.makeMember(
214                                parentMember, childLevel, value, captionValue,
215                                parentChild, stmt, key, column);
216                        }
217                    }
218
219                    // Skip over the columns consumed by makeMember
220                    if (!childLevel.getOrdinalExp().equals(
221                            childLevel.getKeyExp()))
222                    {
223                        ++column;
224                    }
225                    column += childLevel.getProperties().length;
226
227                    // Cache in our intermediate map the key/member pair
228                    // for later lookups of children.
229                    keyToMember.put(member.getKey(), member);
230
231                    if (member != members.get(i)) {
232                        // Flush list we've been building.
233                        List<RolapMember> children = siblings.get(i + 1);
234                        if (children != null) {
235                            MemberChildrenConstraint mcc =
236                                constraint.getMemberChildrenConstraint(
237                                    members.get(i));
238                            if (mcc != null) {
239                                cache.putChildren(
240                                    members.get(i), mcc, children);
241                            }
242                        }
243                        // Start a new list, if the cache needs one. (We don't
244                        // synchronize, so it's possible that the cache will
245                        // have one by the time we complete it.)
246                        MemberChildrenConstraint mcc =
247                            constraint.getMemberChildrenConstraint(member);
248                        // we keep a reference to cachedChildren so they don't
249                        // get garbage-collected
250                        List<RolapMember> cachedChildren =
251                            cache.getChildrenFromCache(member, mcc);
252                        if (i < levelDepth && cachedChildren == null) {
253                            siblings.set(i + 1, new ArrayList<RolapMember>());
254                        } else {
255                            // don't bother building up a list
256                            siblings.set(i + 1,  null);
257                        }
258                        // Record new current member of this level.
259                        members.set(i, member);
260                        // If we're building a list of siblings at this level,
261                        // we haven't seen this one before, so add it.
262                        if (siblings.get(i) != null) {
263                            if (value == RolapUtil.sqlNullValue) {
264                                addAsOldestSibling(siblings.get(i), member);
265                            } else {
266                                siblings.get(i).add(member);
267                            }
268                        }
269                    }
270                }
271                setCurrMember(member);
272            }
273            getList().add(member);
274            return column;
275        }
276
277        public List<Member> close() {
278            synchronized (cacheLock) {
279                return internalClose();
280            }
281        }
282
283        /**
284         * Cleans up after all rows have been processed, and returns the list of
285         * members.
286         *
287         * @return list of members
288         */
289        public List<Member> internalClose() {
290            for (int i = 0; i < members.size(); i++) {
291                RolapMember member = members.get(i);
292                final List<RolapMember> children = siblings.get(i + 1);
293                if (member != null && children != null) {
294                    // If we are finding the members of a particular level, and
295                    // we happen to find some of the children of an ancestor of
296                    // that level, we can't be sure that we have found all of
297                    // the children, so don't put them in the cache.
298                    if (member.getDepth() < level.getDepth()) {
299                        continue;
300                    }
301                    MemberChildrenConstraint mcc =
302                        constraint.getMemberChildrenConstraint(member);
303                    if (mcc != null) {
304                        cache.putChildren(member, mcc, children);
305                    }
306                }
307            }
308            return Util.cast(getList());
309        }
310
311        /**
312         * Adds <code>member</code> just before the first element in
313         * <code>list</code> which has the same parent.
314         */
315        private void addAsOldestSibling(
316            List<RolapMember> list,
317            RolapMember member)
318        {
319            int i = list.size();
320            while (--i >= 0) {
321                RolapMember sibling = list.get(i);
322                if (sibling.getParentMember() != member.getParentMember()) {
323                    break;
324                }
325            }
326            list.add(i + 1, member);
327        }
328    }
329
330    public SqlTupleReader(TupleConstraint constraint) {
331        this.constraint = constraint;
332    }
333
334    public void addLevelMembers(
335        RolapLevel level,
336        MemberBuilder memberBuilder,
337        List<RolapMember> srcMembers)
338    {
339        targets.add(new Target(level, memberBuilder, srcMembers));
340    }
341
342    public Object getCacheKey() {
343        List<Object> key = new ArrayList<Object>();
344        key.add(constraint.getCacheKey());
345        key.add(SqlTupleReader.class);
346        for (TargetBase target : targets) {
347            // don't include the level in the key if the target isn't
348            // processed through native sql
349            if (target.srcMembers != null) {
350                key.add(target.getLevel());
351            }
352        }
353        return key;
354    }
355
356    /**
357     * @return number of targets that contain enumerated sets with calculated
358     * members
359     */
360    public int getEnumTargetCount()
361    {
362        int enumTargetCount = 0;
363        for (TargetBase target : targets) {
364            if (target.getSrcMembers() != null) {
365                enumTargetCount++;
366            }
367        }
368        return enumTargetCount;
369    }
370
371    protected void prepareTuples(
372        DataSource dataSource,
373        TupleList partialResult,
374        List<List<RolapMember>> newPartialResult)
375    {
376        String message = "Populating member cache with members for " + targets;
377        SqlStatement stmt = null;
378        final ResultSet resultSet;
379        boolean execQuery = (partialResult == null);
380        try {
381            if (execQuery) {
382                // we're only reading tuples from the targets that are
383                // non-enum targets
384                List<TargetBase> partialTargets = new ArrayList<TargetBase>();
385                for (TargetBase target : targets) {
386                    if (target.srcMembers == null) {
387                        partialTargets.add(target);
388                    }
389                }
390                final Pair<String, List<SqlStatement.Type>> pair =
391                    makeLevelMembersSql(dataSource);
392                String sql = pair.left;
393                List<SqlStatement.Type> types = pair.right;
394                assert sql != null && !sql.equals("");
395                stmt = RolapUtil.executeQuery(
396                    dataSource, sql, types, maxRows, 0,
397                    new SqlStatement.StatementLocus(
398                        Locus.peek().execution,
399                        "SqlTupleReader.readTuples " + partialTargets,
400                        message,
401                        SqlStatementEvent.Purpose.TUPLES, 0),
402                    -1, -1, null);
403                resultSet = stmt.getResultSet();
404            } else {
405                resultSet = null;
406            }
407
408            for (TargetBase target : targets) {
409                target.open();
410            }
411
412            int limit = MondrianProperties.instance().ResultLimit.get();
413            int fetchCount = 0;
414
415            // determine how many enum targets we have
416            int enumTargetCount = getEnumTargetCount();
417            int[] srcMemberIdxes = null;
418            if (enumTargetCount > 0) {
419                srcMemberIdxes = new int[enumTargetCount];
420            }
421
422            boolean moreRows;
423            int currPartialResultIdx = 0;
424            if (execQuery) {
425                moreRows = resultSet.next();
426                if (moreRows) {
427                    ++stmt.rowCount;
428                }
429            } else {
430                moreRows = currPartialResultIdx < partialResult.size();
431            }
432            while (moreRows) {
433                if (limit > 0 && limit < ++fetchCount) {
434                    // result limit exceeded, throw an exception
435                    throw MondrianResource.instance().MemberFetchLimitExceeded
436                        .ex((long) limit);
437                }
438
439                if (enumTargetCount == 0) {
440                    int column = 0;
441                    for (TargetBase target : targets) {
442                        target.setCurrMember(null);
443                        column = target.addRow(stmt, column);
444                    }
445                } else {
446                    // find the first enum target, then call addTargets()
447                    // to form the cross product of the row from resultSet
448                    // with each of the list of members corresponding to
449                    // the enumerated targets
450                    int firstEnumTarget = 0;
451                    for (; firstEnumTarget < targets.size();
452                        firstEnumTarget++)
453                    {
454                        if (targets.get(firstEnumTarget).srcMembers != null) {
455                            break;
456                        }
457                    }
458                    List<RolapMember> partialRow;
459                    if (execQuery) {
460                        partialRow = null;
461                    } else {
462                        partialRow =
463                            Util.cast(partialResult.get(currPartialResultIdx));
464                    }
465                    resetCurrMembers(partialRow);
466                    addTargets(
467                        0, firstEnumTarget, enumTargetCount, srcMemberIdxes,
468                        stmt, message);
469                    if (newPartialResult != null) {
470                        savePartialResult(newPartialResult);
471                    }
472                }
473
474                if (execQuery) {
475                    moreRows = resultSet.next();
476                    if (moreRows) {
477                        ++stmt.rowCount;
478                    }
479                } else {
480                    currPartialResultIdx++;
481                    moreRows = currPartialResultIdx < partialResult.size();
482                }
483            }
484        } catch (SQLException e) {
485            if (stmt == null) {
486                throw Util.newError(e, message);
487            } else {
488                throw stmt.handle(e);
489            }
490        } finally {
491            if (stmt != null) {
492                stmt.close();
493            }
494        }
495    }
496
497    public TupleList readMembers(
498        DataSource dataSource,
499        TupleList partialResult,
500        List<List<RolapMember>> newPartialResult)
501    {
502        int memberCount = countMembers();
503        while (true) {
504            missedMemberCount = 0;
505            int memberCountBefore = memberCount;
506            prepareTuples(dataSource, partialResult, newPartialResult);
507            memberCount = countMembers();
508            if (missedMemberCount == 0) {
509                // We have successfully read all members. This is always the
510                // case in a regular hierarchy. In a parent-child hierarchy
511                // it may take several passes, because we cannot create a member
512                // before we create its parent.
513                break;
514            }
515            if (memberCount == memberCountBefore) {
516                // This pass made no progress. This must be because of a cycle.
517                throw Util.newError(
518                    "Parent-child hierarchy contains cyclic data");
519            }
520        }
521
522        assert targets.size() == 1;
523
524        return new UnaryTupleList(
525            bumpNullMember(
526                targets.get(0).close()));
527    }
528
529    protected List<Member> bumpNullMember(List<Member> members) {
530        if (members.size() > 0
531            && ((RolapMemberBase)members.get(members.size() - 1)).getKey()
532                == RolapUtil.sqlNullValue)
533        {
534            Member removed = members.remove(members.size() - 1);
535            members.add(0, removed);
536        }
537        return members;
538    }
539
540    /**
541     * Returns the number of members that have been read from all targets.
542     *
543     * @return Number of members that have been read from all targets
544     */
545    private int countMembers() {
546        int n = 0;
547        for (TargetBase target : targets) {
548            if (target.getList() != null) {
549                n += target.getList().size();
550            }
551        }
552        return n;
553    }
554
555    public TupleList readTuples(
556        DataSource jdbcConnection,
557        TupleList partialResult,
558        List<List<RolapMember>> newPartialResult)
559    {
560        prepareTuples(jdbcConnection, partialResult, newPartialResult);
561
562        // List of tuples
563        final int n = targets.size();
564        @SuppressWarnings({"unchecked"})
565        final Iterator<Member>[] iter = new Iterator[n];
566        for (int i = 0; i < n; i++) {
567            TargetBase t = targets.get(i);
568            iter[i] = t.close().iterator();
569        }
570        List<Member> members = new ArrayList<Member>();
571        while (iter[0].hasNext()) {
572            for (int i = 0; i < n; i++) {
573                members.add(iter[i].next());
574            }
575        }
576
577        TupleList tupleList =
578            n == 1
579                ? new UnaryTupleList(members)
580                : new ListTupleList(n, members);
581
582        // need to hierarchize the columns from the enumerated targets
583        // since we didn't necessarily add them in the order in which
584        // they originally appeared in the cross product
585        int enumTargetCount = getEnumTargetCount();
586        if (enumTargetCount > 0) {
587            tupleList = FunUtil.hierarchizeTupleList(tupleList, false);
588        }
589        return tupleList;
590    }
591
592    /**
593     * Sets the current member for those targets that retrieve their column
594     * values from native sql
595     *
596     * @param partialRow if set, previously cached result set
597     */
598    private void resetCurrMembers(List<RolapMember> partialRow) {
599        int nativeTarget = 0;
600        for (TargetBase target : targets) {
601            if (target.srcMembers == null) {
602                // if we have a previously cached row, use that by picking
603                // out the column corresponding to this target; otherwise,
604                // we need to retrieve a new column value from the current
605                // result set
606                if (partialRow != null) {
607                    target.setCurrMember(partialRow.get(nativeTarget++));
608                } else {
609                    target.setCurrMember(null);
610                }
611            }
612        }
613    }
614
615    /**
616     * Recursively forms the cross product of a row retrieved through sql
617     * with each of the targets that contains an enumerated set of members.
618     *
619     * @param currEnumTargetIdx current enum target that recursion
620     *     is being applied on
621     * @param currTargetIdx index within the list of a targets that
622     *     currEnumTargetIdx corresponds to
623     * @param nEnumTargets number of targets that have enumerated members
624     * @param srcMemberIdxes for each enumerated target, the current member
625     *     to be retrieved to form the current cross product row
626     * @param stmt Statement containing the result set corresponding to rows
627     *     retrieved through native SQL
628     * @param message Message to issue on failure
629     */
630    private void addTargets(
631        int currEnumTargetIdx,
632        int currTargetIdx,
633        int nEnumTargets,
634        int[] srcMemberIdxes,
635        SqlStatement stmt,
636        String message)
637    {
638        // loop through the list of members for the current enum target
639        TargetBase currTarget = targets.get(currTargetIdx);
640        for (int i = 0; i < currTarget.srcMembers.size(); i++) {
641            srcMemberIdxes[currEnumTargetIdx] = i;
642            // if we're not on the last enum target, recursively move
643            // to the next one
644            if (currEnumTargetIdx < nEnumTargets - 1) {
645                int nextTargetIdx = currTargetIdx + 1;
646                for (; nextTargetIdx < targets.size(); nextTargetIdx++) {
647                    if (targets.get(nextTargetIdx).srcMembers != null) {
648                        break;
649                    }
650                }
651                addTargets(
652                    currEnumTargetIdx + 1, nextTargetIdx, nEnumTargets,
653                    srcMemberIdxes, stmt, message);
654            } else {
655                // form a cross product using the columns from the current
656                // result set row and the current members that recursion
657                // has reached for the enum targets
658                int column = 0;
659                int enumTargetIdx = 0;
660                for (TargetBase target : targets) {
661                    if (target.srcMembers == null) {
662                        try {
663                            column = target.addRow(stmt, column);
664                        } catch (Throwable e) {
665                            throw Util.newError(e, message);
666                        }
667                    } else {
668                        RolapMember member =
669                            target.srcMembers.get(
670                                srcMemberIdxes[enumTargetIdx++]);
671                        target.getList().add(member);
672                    }
673                }
674            }
675        }
676    }
677
678    /**
679     * Retrieves the current members fetched from the targets executed
680     * through sql and form tuples, adding them to partialResult
681     *
682     * @param partialResult list containing the columns and rows corresponding
683     * to data fetched through sql
684     */
685    private void savePartialResult(List<List<RolapMember>> partialResult) {
686        List<RolapMember> row = new ArrayList<RolapMember>();
687        for (TargetBase target : targets) {
688            if (target.srcMembers == null) {
689                row.add(target.getCurrMember());
690            }
691        }
692        partialResult.add(row);
693    }
694
695    Pair<String, List<SqlStatement.Type>> makeLevelMembersSql(
696        DataSource dataSource)
697    {
698        // In the case of a virtual cube, if we need to join to the fact
699        // table, we do not necessarily have a single underlying fact table,
700        // as the underlying base cubes in the virtual cube may all reference
701        // different fact tables.
702        //
703        // Therefore, we need to gather the underlying fact tables by going
704        // through the list of measures referenced in the query.  And then
705        // we generate one sub-select per fact table, joining against each
706        // underlying fact table, unioning the sub-selects.
707        RolapCube cube = null;
708        boolean virtualCube = false;
709        if (constraint instanceof SqlContextConstraint) {
710            SqlContextConstraint sqlConstraint =
711                (SqlContextConstraint) constraint;
712            Query query = constraint.getEvaluator().getQuery();
713            cube = (RolapCube) query.getCube();
714            if (sqlConstraint.isJoinRequired()) {
715                virtualCube = cube.isVirtual();
716            }
717        }
718
719        if (virtualCube) {
720            Query query = constraint.getEvaluator().getQuery();
721
722            // Make fact table appear in fixed sequence
723
724            final Collection<RolapCube> baseCubes =
725                getBaseCubeCollection(query);
726            Collection<RolapCube> fullyJoiningBaseCubes =
727                getFullyJoiningBaseCubes(baseCubes);
728            if (fullyJoiningBaseCubes.size() == 0) {
729                return sqlForEmptyTuple(dataSource, baseCubes);
730            }
731            // generate sub-selects, each one joining with one of
732            // the fact table referenced
733            String prependString = "";
734            final StringBuilder selectString = new StringBuilder();
735            List<SqlStatement.Type> types = null;
736
737            final int savepoint =
738                getEvaluator(constraint).savepoint();
739
740            SqlQuery unionQuery = SqlQuery.newQuery(dataSource, "");
741
742            try {
743                for (RolapCube baseCube : fullyJoiningBaseCubes) {
744                    // Use the measure from the corresponding base cube in the
745                    // context to find the correct join path to the base fact
746                    // table.
747                    //
748                    // The first non-calculated measure is fine since the
749                    // constraint logic only uses it
750                    // to find the correct fact table to join to.
751                    Member measureInCurrentbaseCube = null;
752                    for (Member currMember : baseCube.getMeasures()) {
753                        if (!currMember.isCalculated()) {
754                            measureInCurrentbaseCube = currMember;
755                            break;
756                        }
757                    }
758
759                    if (measureInCurrentbaseCube == null) {
760                        // Couldn't find a non-calculated member in this cube.
761                        // Pick any measure and the code will fallback to
762                        // the fact table.
763                        if (LOGGER.isDebugEnabled()) {
764                            LOGGER.debug(
765                                "No non-calculated member found in cube "
766                                + baseCube.getName());
767                        }
768                        measureInCurrentbaseCube =
769                            baseCube.getMeasures().get(0);
770                    }
771
772                    // Force the constraint evaluator's measure
773                    // to the one in the base cube.
774                    getEvaluator(constraint)
775                        .setContext(measureInCurrentbaseCube);
776
777                    selectString.append(prependString);
778
779                    // Generate the select statement for the current base cube.
780                    // Make sure to pass WhichSelect.NOT_LAST if there are more
781                    // than one base cube and it isn't the last one so that
782                    // the order by clause is not added to unionized queries
783                    // (that would be illegal SQL)
784                    final Pair<String, List<SqlStatement.Type>> pair =
785                        generateSelectForLevels(
786                            dataSource, baseCube,
787                            fullyJoiningBaseCubes.size() == 1
788                                ? WhichSelect.ONLY
789                                : WhichSelect.NOT_LAST);
790                    selectString.append(pair.left);
791                    types = pair.right;
792                    prependString =
793                        MondrianProperties.instance().GenerateFormattedSql.get()
794                            ? Util.nl + UNION + Util.nl
795                            : " " + UNION + " ";
796                }
797            } finally {
798                // Restore the original measure member
799                getEvaluator(constraint).restore(savepoint);
800            }
801
802            if (fullyJoiningBaseCubes.size() == 1) {
803                // Because there is only one virtual cube to
804                // join on, we can swap the union query by
805                // the original one.
806                return Pair.of(selectString.toString(), types);
807            } else {
808                // Add the subquery to the wrapper query.
809                unionQuery.addFromQuery(
810                    selectString.toString(), "unionQuery", true);
811
812                // Dont forget to select all columns.
813                unionQuery.addSelect("*", null, null);
814
815                // Sort the union of the cubes.
816                // The order by columns need to be numbers,
817                // not column name strings or expressions.
818                if (fullyJoiningBaseCubes.size() > 1) {
819                    for (int i = 0; i < types.size(); i++) {
820                        unionQuery.addOrderBy(
821                            i + 1 + "",
822                            true,
823                            false,
824                            // We can't order the nulls
825                            // because column ordinals used as alias
826                            // are not supported by functions.
827                            // FIXME This dialect call is old and
828                            // has lost its meaning in the process.
829                            unionQuery.getDialect()
830                                .requiresUnionOrderByOrdinal());
831                    }
832                }
833                return Pair.of(unionQuery.toSqlAndTypes().left, types);
834            }
835
836        } else {
837            // This is the standard code path with regular single-fact table
838            // cubes.
839            return generateSelectForLevels(
840                dataSource, cube, WhichSelect.ONLY);
841        }
842    }
843
844    private Collection<RolapCube> getFullyJoiningBaseCubes(
845        Collection<RolapCube> baseCubes)
846    {
847        final Collection<RolapCube> fullyJoiningCubes =
848            new ArrayList<RolapCube>();
849        for (RolapCube baseCube : baseCubes) {
850            boolean allTargetsJoin = true;
851            for (TargetBase target : targets) {
852                if (!targetIsOnBaseCube(target, baseCube)) {
853                    allTargetsJoin = false;
854                }
855            }
856            if (allTargetsJoin) {
857                fullyJoiningCubes.add(baseCube);
858            }
859        }
860        return fullyJoiningCubes;
861    }
862
863
864    Collection<RolapCube> getBaseCubeCollection(final Query query) {
865        RolapCube.CubeComparator cubeComparator =
866            new RolapCube.CubeComparator();
867        Collection<RolapCube> baseCubes =
868            new TreeSet<RolapCube>(cubeComparator);
869        baseCubes.addAll(query.getBaseCubes());
870        return baseCubes;
871    }
872
873    Pair<String, List<SqlStatement.Type>> sqlForEmptyTuple(
874        DataSource dataSource,
875        final Collection<RolapCube> baseCubes)
876    {
877        final SqlQuery sqlQuery = SqlQuery.newQuery(dataSource, null);
878        sqlQuery.addSelect("0", null);
879        sqlQuery.addFrom(baseCubes.iterator().next().getFact(), null, true);
880        sqlQuery.addWhere("1 = 0");
881        return sqlQuery.toSqlAndTypes();
882    }
883
884    /**
885     * Generates the SQL string corresponding to the levels referenced.
886     *
887     * @param dataSource jdbc connection that they query will execute against
888     * @param baseCube this is the cube object for regular cubes, and the
889     *   underlying base cube for virtual cubes
890     * @param whichSelect Position of this select statement in a union
891     * @return SQL statement string and types
892     */
893    Pair<String, List<SqlStatement.Type>> generateSelectForLevels(
894        DataSource dataSource,
895        RolapCube baseCube,
896        WhichSelect whichSelect)
897    {
898        String s =
899            "while generating query to retrieve members of level(s) " + targets;
900
901        // Allow query to use optimization hints from the table definition
902        SqlQuery sqlQuery = SqlQuery.newQuery(dataSource, s);
903        sqlQuery.setAllowHints(true);
904
905
906        Evaluator evaluator = getEvaluator(constraint);
907        AggStar aggStar = chooseAggStar(constraint, evaluator, baseCube);
908
909        // add the selects for all levels to fetch
910        for (TargetBase target : targets) {
911            // if we're going to be enumerating the values for this target,
912            // then we don't need to generate sql for it
913            if (target.getSrcMembers() == null) {
914                addLevelMemberSql(
915                    sqlQuery,
916                    target.getLevel(),
917                    baseCube,
918                    whichSelect,
919                    aggStar);
920            }
921        }
922
923        constraint.addConstraint(sqlQuery, baseCube, aggStar);
924
925        return sqlQuery.toSqlAndTypes();
926    }
927
928    boolean targetIsOnBaseCube(TargetBase target, RolapCube baseCube) {
929        return baseCube == null || baseCube.findBaseCubeHierarchy(
930            target.getLevel().getHierarchy()) != null;
931    }
932
933    /**
934     * <p>Determines whether the GROUP BY clause is required, based on the
935     * schema definitions of the hierarchy and level properties.</p>
936     *
937     * <p>The GROUP BY clause may only be eliminated if the level identified by
938     * the uniqueKeyLevelName exists, the query is at a depth to include it,
939     * and all properties in the included levels are functionally dependent on
940     * their level values.</p>
941     *
942     *
943     * @param sqlQuery     The query object being constructed
944     * @param hierarchy    Hierarchy of the cube
945     * @param levels       Levels in this hierarchy
946     * @param levelDepth   Level depth at which the query is occuring
947     * @return whether the GROUP BY is needed
948     *
949     */
950    private boolean isGroupByNeeded(
951        SqlQuery sqlQuery,
952        RolapHierarchy hierarchy,
953        RolapLevel[] levels,
954        int levelDepth)
955    {
956        // Figure out if we need to generate GROUP BY at all.  It may only be
957        // eliminated if we are at a depth that includes the unique key level,
958        // and all properties of included levels depend on the level value.
959        boolean needsGroupBy = false;  // figure out if we need GROUP BY at all
960
961        if (hierarchy.getUniqueKeyLevelName() == null) {
962            needsGroupBy = true;
963        } else {
964            boolean foundUniqueKeyLevelName = false;
965            for (int i = 0; i <= levelDepth; i++) {
966                RolapLevel lvl = levels[i];
967
968                // can ignore the "all" level
969                if (!(lvl.isAll())) {
970                    if (hierarchy.getUniqueKeyLevelName().equals(
971                            lvl.getName()))
972                    {
973                       foundUniqueKeyLevelName = true;
974                    }
975                    for (RolapProperty p : lvl.getProperties()) {
976                        if (!p.dependsOnLevelValue()) {
977                            needsGroupBy = true;
978                            // GROUP BY is required, so break out of
979                            // properties loop
980                            break;
981                        }
982                    }
983                    if (needsGroupBy) {
984                        // GROUP BY is required, so break out of levels loop
985                        break;
986                    }
987                }
988            }
989            if (!foundUniqueKeyLevelName) {
990                // if we're not deep enough to be unique,
991                // then the GROUP BY is required
992                needsGroupBy = true;
993            }
994        }
995
996        return needsGroupBy;
997    }
998
999    /**
1000     * Generates the SQL statement to access members of <code>level</code>. For
1001     * example, <blockquote>
1002     * <pre>SELECT "country", "state_province", "city"
1003     * FROM "customer"
1004     * GROUP BY "country", "state_province", "city", "init", "bar"
1005     * ORDER BY "country", "state_province", "city"</pre>
1006     * </blockquote> accesses the "City" level of the "Customers"
1007     * hierarchy. Note that:<ul>
1008     *
1009     * <li><code>"country", "state_province"</code> are the parent keys;</li>
1010     *
1011     * <li><code>"city"</code> is the level key;</li>
1012     *
1013     * <li><code>"init", "bar"</code> are member properties.</li>
1014     * </ul>
1015     *
1016     * @param sqlQuery the query object being constructed
1017     * @param level level to be added to the sql query
1018     * @param baseCube this is the cube object for regular cubes, and the
1019     *   underlying base cube for virtual cubes
1020     * @param whichSelect describes whether this select belongs to a larger
1021     * @param aggStar aggregate star if available
1022     */
1023    protected void addLevelMemberSql(
1024        SqlQuery sqlQuery,
1025        RolapLevel level,
1026        RolapCube baseCube,
1027        WhichSelect whichSelect,
1028        AggStar aggStar)
1029    {
1030        RolapHierarchy hierarchy = level.getHierarchy();
1031
1032        // lookup RolapHierarchy of base cube that matches this hierarchy
1033
1034        if (hierarchy instanceof RolapCubeHierarchy) {
1035            RolapCubeHierarchy cubeHierarchy = (RolapCubeHierarchy)hierarchy;
1036            if (baseCube != null
1037                && !cubeHierarchy.getCube().equals(baseCube))
1038            {
1039                // replace the hierarchy with the underlying base cube hierarchy
1040                // in the case of virtual cubes
1041                hierarchy = baseCube.findBaseCubeHierarchy(hierarchy);
1042            }
1043        }
1044
1045        RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
1046        int levelDepth = level.getDepth();
1047
1048        boolean needsGroupBy =
1049            isGroupByNeeded(sqlQuery, hierarchy, levels, levelDepth);
1050
1051        for (int i = 0; i <= levelDepth; i++) {
1052            RolapLevel currLevel = levels[i];
1053            if (currLevel.isAll()) {
1054                continue;
1055            }
1056
1057            // Determine if the aggregate table contains the collapsed level
1058            boolean levelCollapsed =
1059                (aggStar != null)
1060                && SqlMemberSource.isLevelCollapsed(
1061                    aggStar,
1062                    (RolapCubeLevel)currLevel);
1063
1064            boolean multipleCols =
1065                SqlMemberSource.levelContainsMultipleColumns(currLevel);
1066
1067            if (levelCollapsed && !multipleCols) {
1068                // if this is a single column collapsed level, there is
1069                // no need to join it with dimension tables
1070                RolapStar.Column starColumn =
1071                    ((RolapCubeLevel) currLevel).getStarKeyColumn();
1072                int bitPos = starColumn.getBitPosition();
1073                AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
1074                String q = aggColumn.generateExprString(sqlQuery);
1075                sqlQuery.addSelectGroupBy(q, starColumn.getInternalType());
1076                if (whichSelect == WhichSelect.ONLY) {
1077                    sqlQuery.addOrderBy(q, true, false, true);
1078                }
1079                aggColumn.getTable().addToFrom(sqlQuery, false, true);
1080                continue;
1081            }
1082
1083            MondrianDef.Expression keyExp = currLevel.getKeyExp();
1084            MondrianDef.Expression ordinalExp = currLevel.getOrdinalExp();
1085            MondrianDef.Expression captionExp = currLevel.getCaptionExp();
1086            MondrianDef.Expression parentExp = currLevel.getParentExp();
1087
1088            if (parentExp != null) {
1089                if (!levelCollapsed) {
1090                    hierarchy.addToFrom(sqlQuery, parentExp);
1091                }
1092                String parentSql = parentExp.getExpression(sqlQuery);
1093                sqlQuery.addSelectGroupBy(
1094                    parentSql, currLevel.getInternalType());
1095                if (whichSelect == WhichSelect.LAST
1096                    || whichSelect == WhichSelect.ONLY)
1097                {
1098                    sqlQuery.addOrderBy(parentSql, true, false, true, false);
1099                }
1100            }
1101
1102            String keySql = keyExp.getExpression(sqlQuery);
1103            String ordinalSql = ordinalExp.getExpression(sqlQuery);
1104
1105            if (!levelCollapsed) {
1106                hierarchy.addToFrom(sqlQuery, keyExp);
1107                hierarchy.addToFrom(sqlQuery, ordinalExp);
1108            }
1109            String captionSql = null;
1110            if (captionExp != null) {
1111                captionSql = captionExp.getExpression(sqlQuery);
1112                if (!levelCollapsed) {
1113                    hierarchy.addToFrom(sqlQuery, captionExp);
1114                }
1115            }
1116
1117            String alias =
1118                sqlQuery.addSelect(keySql, currLevel.getInternalType());
1119            if (needsGroupBy) {
1120                sqlQuery.addGroupBy(keySql, alias);
1121            }
1122
1123            if (captionSql != null) {
1124                alias = sqlQuery.addSelect(captionSql, null);
1125                if (needsGroupBy) {
1126                    sqlQuery.addGroupBy(captionSql, alias);
1127                }
1128            }
1129
1130            if (!ordinalSql.equals(keySql)) {
1131                alias = sqlQuery.addSelect(ordinalSql, null);
1132                if (needsGroupBy) {
1133                    sqlQuery.addGroupBy(ordinalSql, alias);
1134                }
1135            }
1136
1137            constraint.addLevelConstraint(
1138                sqlQuery, baseCube, aggStar, currLevel);
1139
1140            if (levelCollapsed) {
1141                // add join between key and aggstar
1142                // join to dimension tables starting
1143                // at the lowest granularity and working
1144                // towards the fact table
1145                hierarchy.addToFromInverse(sqlQuery, keyExp);
1146
1147                RolapStar.Column starColumn =
1148                    ((RolapCubeLevel) currLevel).getStarKeyColumn();
1149                int bitPos = starColumn.getBitPosition();
1150                AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
1151                RolapStar.Condition condition =
1152                    new RolapStar.Condition(keyExp, aggColumn.getExpression());
1153                sqlQuery.addWhere(condition.toString(sqlQuery));
1154            }
1155
1156            if (whichSelect == WhichSelect.ONLY) {
1157                sqlQuery.addOrderBy(ordinalSql, true, false, true);
1158            }
1159
1160            RolapProperty[] properties = currLevel.getProperties();
1161            for (RolapProperty property : properties) {
1162                final MondrianDef.Expression propExp = property.getExp();
1163                final String propSql;
1164                if (propExp instanceof MondrianDef.Column) {
1165                    // When dealing with a column, we must use the same table
1166                    // alias as the one used by the level. We also assume that
1167                    // the property lives in the same table as the level.
1168                    propSql =
1169                        sqlQuery.getDialect().quoteIdentifier(
1170                            currLevel.getTableAlias(),
1171                            ((MondrianDef.Column)propExp).name);
1172                } else {
1173                    propSql = property.getExp().getExpression(sqlQuery);
1174                }
1175                alias = sqlQuery.addSelect(propSql, null);
1176                if (needsGroupBy) {
1177                    // Certain dialects allow us to eliminate properties
1178                    // from the group by that are functionally dependent
1179                    // on the level value
1180                    if (!sqlQuery.getDialect().allowsSelectNotInGroupBy()
1181                        || !property.dependsOnLevelValue())
1182                    {
1183                        sqlQuery.addGroupBy(propSql, alias);
1184                    }
1185                }
1186            }
1187        }
1188    }
1189
1190    /**
1191     * Obtains the evaluator used to find an aggregate table to support
1192     * the Tuple constraint.
1193     *
1194     * @param constraint Constraint
1195     * @return evaluator for constraint
1196     */
1197    protected Evaluator getEvaluator(TupleConstraint constraint) {
1198        if (constraint instanceof SqlContextConstraint) {
1199            return constraint.getEvaluator();
1200        }
1201        if (constraint instanceof DescendantsConstraint) {
1202            DescendantsConstraint descConstraint =
1203                (DescendantsConstraint) constraint;
1204            MemberChildrenConstraint mcc =
1205                descConstraint.getMemberChildrenConstraint(null);
1206            if (mcc instanceof SqlContextConstraint) {
1207                SqlContextConstraint scc = (SqlContextConstraint) mcc;
1208                return scc.getEvaluator();
1209            }
1210        }
1211        return null;
1212    }
1213
1214    /**
1215     * Obtains the AggStar instance which corresponds to an aggregate table
1216     * which can be used to support the member constraint.
1217     *
1218     * @param constraint The tuple constraint to apply.
1219     * @param evaluator the current evaluator to obtain the cube and members to
1220     *        be queried  @return AggStar for aggregate table
1221     * @param baseCube The base cube from which to choose an aggregation star.
1222     *        Can be null, in which case we use the evaluator's cube.
1223     */
1224    AggStar chooseAggStar(
1225        TupleConstraint constraint,
1226        Evaluator evaluator,
1227        RolapCube baseCube)
1228    {
1229        if (!MondrianProperties.instance().UseAggregates.get()) {
1230            return null;
1231        }
1232
1233        if (evaluator == null) {
1234            return null;
1235        }
1236
1237        if (baseCube == null) {
1238            baseCube = (RolapCube) evaluator.getCube();
1239        }
1240
1241        // Current cannot support aggregate tables for virtual cubes
1242        if (baseCube.isVirtual()) {
1243            return null;
1244        }
1245
1246        RolapStar star = baseCube.getStar();
1247        final int starColumnCount = star.getColumnCount();
1248        BitKey measureBitKey = BitKey.Factory.makeBitKey(starColumnCount);
1249        BitKey levelBitKey = BitKey.Factory.makeBitKey(starColumnCount);
1250
1251        // Convert global ordinal to cube based ordinal (the 0th dimension
1252        // is always [Measures]). In the case of filter constraint this will
1253        // be the measure on which the filter will be done.
1254
1255        // Since we support aggregated members as arguments, we'll expand
1256        // this too.
1257        // Failing to do so could result in chosing the wrong aggstar, as the
1258        // level would not be passed to the bitkeys
1259        final Member[] members =
1260          SqlConstraintUtils.expandSupportedCalculatedMembers(
1261              evaluator.getNonAllMembers(), evaluator);
1262
1263        // if measure is calculated, we can't continue
1264        if (!(members[0] instanceof RolapBaseCubeMeasure)) {
1265            return null;
1266        }
1267
1268        RolapBaseCubeMeasure measure = (RolapBaseCubeMeasure)members[0];
1269
1270        int bitPosition =
1271            ((RolapStar.Measure) measure.getStarMeasure()).getBitPosition();
1272
1273        // set a bit for each level which is constrained in the context
1274        final CellRequest request =
1275            RolapAggregationManager.makeRequest(members);
1276        if (request == null) {
1277            // One or more calculated members. Cannot use agg table.
1278            return null;
1279        }
1280        // TODO: RME why is this using the array of constrained columns
1281        // from the CellRequest rather than just the constrained columns
1282        // BitKey (method getConstrainedColumnsBitKey)?
1283        RolapStar.Column[] columns = request.getConstrainedColumns();
1284        for (RolapStar.Column column1 : columns) {
1285            levelBitKey.set(column1.getBitPosition());
1286        }
1287
1288        // set the masks
1289        for (TargetBase target : targets) {
1290            RolapLevel level = target.level;
1291            if (!level.isAll()) {
1292                RolapStar.Column column =
1293                    ((RolapCubeLevel)level).getBaseStarKeyColumn(baseCube);
1294                if (column != null) {
1295                    levelBitKey.set(column.getBitPosition());
1296                }
1297            }
1298        }
1299
1300        // Set the bits for limited rollup members
1301        RolapUtil.constraintBitkeyForLimitedMembers(
1302            evaluator, evaluator.getMembers(), baseCube, levelBitKey);
1303
1304        measureBitKey.set(bitPosition);
1305
1306        if (constraint
1307            instanceof RolapNativeCrossJoin.NonEmptyCrossJoinConstraint)
1308        {
1309            // Cannot evaluate NonEmptyCrossJoinConstraint using an agg
1310            // table if one of its args is a DescendantsConstraint.
1311            RolapNativeCrossJoin.NonEmptyCrossJoinConstraint necj =
1312                (RolapNativeCrossJoin.NonEmptyCrossJoinConstraint)
1313                    constraint;
1314            for (CrossJoinArg arg : necj.args) {
1315                if (arg instanceof DescendantsCrossJoinArg
1316                    || arg instanceof MemberListCrossJoinArg)
1317                {
1318                    final RolapLevel level = arg.getLevel();
1319                    if (level != null && !level.isAll()) {
1320                        RolapStar.Column column =
1321                            ((RolapCubeLevel)level)
1322                                .getBaseStarKeyColumn(baseCube);
1323                        levelBitKey.set(column.getBitPosition());
1324                    }
1325                }
1326            }
1327        }
1328
1329        // find the aggstar using the masks
1330        return AggregationManager.findAgg(
1331            star, levelBitKey, measureBitKey, new boolean[] {false});
1332    }
1333
1334    int getMaxRows() {
1335        return maxRows;
1336    }
1337
1338    void setMaxRows(int maxRows) {
1339        this.maxRows = maxRows;
1340    }
1341
1342    /**
1343     * Description of the position of a SELECT statement in a UNION. Queries
1344     * on virtual cubes tend to generate unions.
1345     */
1346    enum WhichSelect {
1347        /**
1348         * Select statement does not belong to a union.
1349         */
1350        ONLY,
1351        /**
1352         * Select statement belongs to a UNION, but is not the last. Typically
1353         * this occurs when querying a virtual cube.
1354         */
1355        NOT_LAST,
1356        /**
1357         * Select statement is the last in a UNION. Typically
1358         * this occurs when querying a virtual cube.
1359         */
1360        LAST
1361    }
1362}
1363
1364// End SqlTupleReader.java