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.olap.*;
015import mondrian.olap.fun.AggregateFunDef;
016import mondrian.olap.fun.ParenthesesFunDef;
017import mondrian.resource.MondrianResource;
018import mondrian.rolap.agg.*;
019import mondrian.rolap.aggmatcher.AggStar;
020import mondrian.rolap.sql.SqlQuery;
021import mondrian.spi.Dialect;
022import mondrian.util.FilteredIterableList;
023
024import mondrian.calc.*;
025import mondrian.mdx.*;
026
027import org.apache.log4j.Logger;
028
029import java.util.*;
030
031
032
033/**
034 * Utility class used by implementations of {@link mondrian.rolap.sql.SqlConstraint},
035 * used to generate constraints into {@link mondrian.rolap.sql.SqlQuery}.
036 *
037 * @author av
038 * @since Nov 21, 2005
039 */
040public class SqlConstraintUtils {
041
042    private static final Logger LOG =
043        Logger.getLogger(SqlConstraintUtils.class);
044
045    /** Utility class */
046    private SqlConstraintUtils() {
047    }
048
049    /**
050     * For every restricting member in the current context, generates
051     * a WHERE condition and a join to the fact table.
052     *
053     * @param sqlQuery the query to modify
054     * @param aggStar Aggregate table, or null if query is against fact table
055     * @param restrictMemberTypes defines the behavior if the current context
056     *   contains calculated members. If true, thows an exception.
057     * @param evaluator Evaluator
058     */
059    public static void addContextConstraint(
060        SqlQuery sqlQuery,
061        AggStar aggStar,
062        Evaluator evaluator,
063        RolapCube baseCube,
064        boolean restrictMemberTypes)
065    {
066        // Add constraint using the current evaluator context
067        Member[] members = evaluator.getNonAllMembers();
068
069        // Expand the ones that can be expanded. For this particular code line,
070        // since this will be used to build a cell request, we need to stay with
071        // only one member per ordinal in cube.
072        // This follows the same line of thought as the setContext in
073        // RolapEvaluator.
074
075        members = expandSupportedCalculatedMembers(members, evaluator);
076        members = getUniqueOrdinalMembers(members);
077
078        if (baseCube == null
079            && evaluator instanceof RolapEvaluator)
080        {
081            baseCube = ((RolapEvaluator)evaluator).getCube();
082        }
083
084        if (restrictMemberTypes) {
085            if (containsCalculatedMember(members, true)) {
086                throw Util.newInternal(
087                    "can not restrict SQL to calculated Members");
088            }
089        } else {
090            members = removeCalculatedAndDefaultMembers(members);
091        }
092
093        final CellRequest request =
094            RolapAggregationManager.makeRequest(members);
095        if (request == null) {
096            if (restrictMemberTypes) {
097                throw Util.newInternal("CellRequest is null - why?");
098            }
099            // One or more of the members was null or calculated, so the
100            // request is impossible to satisfy.
101            return;
102        }
103        RolapStar.Column[] columns = request.getConstrainedColumns();
104        Object[] values = request.getSingleValues();
105
106        Map<MondrianDef.Expression, Set<RolapMember>> mapOfSlicerMembers = null;
107
108        HashMap<MondrianDef.Expression, Boolean> done =
109            new HashMap<MondrianDef.Expression, Boolean>();
110        // following code is similar to
111        // AbstractQuerySpec#nonDistinctGenerateSQL()
112        for (int i = 0; i < columns.length; i++) {
113            RolapStar.Column column = columns[i];
114
115            String expr;
116            if (aggStar != null) {
117                int bitPos = column.getBitPosition();
118                AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
119                AggStar.Table table = aggColumn.getTable();
120                table.addToFrom(sqlQuery, false, true);
121                expr = aggColumn.generateExprString(sqlQuery);
122            } else {
123                RolapStar.Table table = column.getTable();
124                table.addToFrom(sqlQuery, false, true);
125                expr = column.generateExprString(sqlQuery);
126            }
127
128            final String value = String.valueOf(values[i]);
129            if ((RolapUtil.mdxNullLiteral().equalsIgnoreCase(value))
130                || (value.equalsIgnoreCase(RolapUtil.sqlNullValue.toString())))
131            {
132                sqlQuery.addWhere(
133                    expr,
134                    " is ",
135                    RolapUtil.sqlNullLiteral);
136            } else {
137                if (column.getDatatype().isNumeric()) {
138                    // make sure it can be parsed
139                    Double.valueOf(value);
140                }
141
142                if (mapOfSlicerMembers == null) {
143                    mapOfSlicerMembers = getSlicerMemberMap(evaluator);
144                }
145
146                MondrianDef.Expression keyForSlicerMap =
147                        column.getExpression();
148
149                if (mapOfSlicerMembers.containsKey(keyForSlicerMap)) {
150                    if (!done.containsKey(keyForSlicerMap)) {
151                        Set<RolapMember> slicerMembersSet =
152                                mapOfSlicerMembers.get(keyForSlicerMap);
153                        List<RolapMember> slicerMembers =
154                                new ArrayList<RolapMember>(slicerMembersSet);
155
156                        // search and destroy [all](s)
157                        List<RolapMember> allMembers =
158                            new ArrayList<RolapMember>();
159                        for (RolapMember slicerMember : slicerMembers) {
160                            if (slicerMember.isAll()) {
161                                allMembers.add(slicerMember);
162                            }
163                        }
164                        if (allMembers.size() > 0) {
165                            slicerMembers.removeAll(allMembers);
166                        }
167
168                        if (slicerMembers.size() > 0) {
169                            int levelIndex = slicerMembers.get(0)
170                                    .getHierarchy()
171                                    .getLevels().length;
172                            RolapLevel levelForWhere =
173                                    (RolapLevel) slicerMembers.get(0)
174                                    .getHierarchy()
175                                    .getLevels()[levelIndex - 1];
176                            final String where =
177                                    generateSingleValueInExpr(
178                                        sqlQuery, baseCube,
179                                        aggStar, slicerMembers,
180                                        levelForWhere,
181                                        restrictMemberTypes, false, false);
182                            if (!where.equals("")) {
183                                // The where clause might be null because if the
184                                // list of members is greater than the limit
185                                // permitted, we won't constraint.
186                                sqlQuery.addWhere(where);
187                            }
188                        } else {
189                            // No extra slicers.... just use the = method
190                            final StringBuilder buf = new StringBuilder();
191                            sqlQuery.getDialect().quote(
192                                buf, value,
193                                column.getDatatype());
194                            sqlQuery.addWhere(
195                                expr,
196                                " = ",
197                                buf.toString());
198                        }
199                        done.put(keyForSlicerMap, Boolean.TRUE);
200                    }
201
202                } else {
203                    // apply constraints not in the slicer
204                    final StringBuilder buf = new StringBuilder();
205                    sqlQuery.getDialect().quote(
206                        buf, value,
207                        column.getDatatype());
208                    sqlQuery.addWhere(
209                        expr,
210                        " = ",
211                        buf.toString());
212                }
213            }
214        }
215
216        // force Role based Access filtering
217        addRoleAccessConstraints(
218            sqlQuery,
219            aggStar,
220            restrictMemberTypes,
221            baseCube,
222            evaluator);
223    }
224
225    public static Map<Level, List<RolapMember>> getRoleConstraintMembers(
226        SchemaReader schemaReader,
227        Member[] members)
228    {
229        // LinkedHashMap keeps insert-order
230        Map<Level, List<RolapMember>> roleMembers =
231            new LinkedHashMap<Level, List<RolapMember>>();
232        Role role = schemaReader.getRole();
233        for (Member member : members) {
234            if (member instanceof RolapHierarchy.LimitedRollupMember
235                || member instanceof
236                   RestrictedMemberReader.MultiCardinalityDefaultMember)
237            {
238                // iterate relevant levels to get accessible members
239                List<Level> hierarchyLevels = schemaReader
240                        .getHierarchyLevels(member.getHierarchy());
241                for (Level affectedLevel : hierarchyLevels) {
242                    List<RolapMember> slicerMembers =
243                        new ArrayList<RolapMember>();
244                    boolean hasCustom = false;
245                    List<Member> availableMembers =
246                        schemaReader
247                            .getLevelMembers(affectedLevel, false);
248                    for (Member availableMember : availableMembers) {
249                        if (!availableMember.isAll()) {
250                            slicerMembers.add((RolapMember) availableMember);
251                        }
252                        hasCustom |=
253                            role.getAccess(availableMember) == Access.CUSTOM;
254                    }
255                    if (!slicerMembers.isEmpty()) {
256                        roleMembers.put(affectedLevel, slicerMembers);
257                    }
258                    if (!hasCustom) {
259                        // we don't have partial access, no need to go deeper
260                        break;
261                    }
262                }
263            }
264        }
265        return roleMembers;
266    }
267
268    private static void addRoleAccessConstraints(
269        SqlQuery sqlQuery,
270        AggStar aggStar,
271        boolean restrictMemberTypes,
272        RolapCube baseCube,
273        Evaluator evaluator)
274    {
275        Map<Level, List<RolapMember>> roleMembers =
276            getRoleConstraintMembers(
277                evaluator.getSchemaReader(),
278                evaluator.getMembers());
279        for (Map.Entry<Level, List<RolapMember>> entry
280            : roleMembers.entrySet())
281        {
282            final String where =
283                generateSingleValueInExpr(
284                    sqlQuery,
285                    baseCube,
286                    aggStar,
287                    entry.getValue(),
288                    (RolapCubeLevel) entry.getKey(),
289                    restrictMemberTypes,
290                    false,
291                    true);
292            if (where.length() > 1) {
293                // The where clause might be null because if the
294                // list of members is greater than the limit
295                // permitted, we won't constrain.
296
297                // When dealing with where clauses generated by Roles,it is
298                // safer to join the fact table because in all likelihood, this
299                // will be required.
300                // TODO: check this against MONDRIAN-1133,1201
301                joinLevelTableToFactTable(
302                    sqlQuery,
303                    baseCube,
304                    aggStar,
305                    evaluator,
306                    (RolapCubeLevel) entry.getKey());
307                // add constraints
308                sqlQuery.addWhere(where);
309            }
310        }
311    }
312
313    /**
314     * Gets a map of MondrianDef.Expression to the set of sliced members
315     * associated with each expression.
316     *
317     * This map is used by addContextConstraint() to get the set of slicer
318     * members associated with each column in the cell request's constrained
319     * columns array, {@link CellRequest#getConstrainedColumns}
320     */
321    private static Map<MondrianDef.Expression, Set<RolapMember>>
322    getSlicerMemberMap(Evaluator evaluator)
323    {
324        Map<MondrianDef.Expression, Set<RolapMember>> mapOfSlicerMembers =
325            new HashMap<MondrianDef.Expression, Set<RolapMember>>();
326
327        if (evaluator.isEvalAxes()) {
328            Member[] expandedSlicers =
329                expandSupportedCalculatedMembers(
330                    ((RolapEvaluator)evaluator).getSlicerMembers(),
331                    evaluator.push());
332
333            if (hasMultiPositionSlicer(expandedSlicers)) {
334                for (Member slicerMember : expandedSlicers) {
335                    if (slicerMember.isMeasure()) {
336                        continue;
337                    }
338                    addSlicedMemberToMap(mapOfSlicerMembers, slicerMember);
339                }
340            }
341        }
342        return mapOfSlicerMembers;
343    }
344
345    /**
346     * Adds the slicer member and all parent members to mapOfSlicerMembers
347     * capturing the sliced members associated with an Expression.
348     *
349     */
350    private static void addSlicedMemberToMap(
351        Map<MondrianDef.Expression, Set<RolapMember>> mapOfSlicerMembers,
352        Member slicerMember)
353    {
354        if (slicerMember == null || slicerMember.isAll()
355            || slicerMember.isNull())
356        {
357            return;
358        }
359        assert slicerMember instanceof RolapMember;
360        MondrianDef.Expression expression = ((RolapLevel)slicerMember
361            .getLevel()).getKeyExp();
362        if (!mapOfSlicerMembers.containsKey(expression)) {
363            mapOfSlicerMembers.put(
364                expression, new LinkedHashSet<RolapMember>());
365        }
366        mapOfSlicerMembers.get(expression).add((RolapMember)slicerMember);
367        addSlicedMemberToMap(
368            mapOfSlicerMembers, slicerMember.getParentMember());
369    }
370
371    private static boolean hasMultiPositionSlicer(Member[] slicerMembers) {
372        Map<Hierarchy, Member> mapOfSlicerMembers =
373            new HashMap<Hierarchy, Member>();
374        for (
375            Member slicerMember : slicerMembers)
376        {
377            Hierarchy hierarchy = slicerMember.getHierarchy();
378            if (mapOfSlicerMembers.containsKey(hierarchy)) {
379                // We have found a second member in this hierarchy
380                return true;
381            }
382            mapOfSlicerMembers.put(hierarchy, slicerMember);
383        }
384        return false;
385    }
386
387
388    public static Member[] expandSupportedCalculatedMembers(
389        List<Member> listOfMembers,
390        Evaluator evaluator)
391    {
392        return expandSupportedCalculatedMembers(
393            listOfMembers.toArray(
394                new Member[listOfMembers.size()]),
395                evaluator);
396    }
397
398    public static Member[] expandSupportedCalculatedMembers(
399        Member[] members,
400        Evaluator evaluator)
401    {
402        ArrayList<Member> listOfMembers = new ArrayList<Member>();
403        for (Member member : members) {
404            if (member.isCalculated()
405                && isSupportedCalculatedMember(member))
406            {
407                listOfMembers.addAll(
408                    expandExpressions(member, null, evaluator));
409            } else {
410                // just add the member
411                listOfMembers.add(member);
412            }
413        }
414        members = listOfMembers.toArray(new Member[listOfMembers.size()]);
415        return members;
416    }
417
418    public static List<Member> expandExpressions(
419        Member member,
420        Exp expression,
421        Evaluator evaluator)
422    {
423        List<Member> listOfMembers = new ArrayList<Member>();
424        if (expression == null) {
425            expression = member.getExpression();
426        }
427        if (expression instanceof ResolvedFunCall) {
428            ResolvedFunCall fun = (ResolvedFunCall)expression;
429
430          if (fun.getFunDef() instanceof ParenthesesFunDef) {
431              assert (fun.getArgCount() == 1);
432              listOfMembers.addAll(
433                  expandExpressions(member, fun.getArg(0), evaluator));
434          } else if (fun.getFunName().equals("+")) {
435              Exp[] expressions = fun.getArgs();
436              for (Exp innerExp : expressions) {
437                  listOfMembers.addAll(
438                      expandExpressions(member, innerExp, evaluator));
439              }
440          } else {
441              // Extract the list of members
442              Iterator <Member> evaluatedSet =
443                  getSetFromCalculatedMember(evaluator, member);
444              while (evaluatedSet.hasNext()) {
445                  listOfMembers.add(evaluatedSet.next());
446              }
447          }
448        } else if (expression instanceof MemberExpr) {
449            listOfMembers.add(((MemberExpr)expression).getMember());
450        } else {
451            listOfMembers.add(member);
452        }
453        return listOfMembers;
454    }
455
456    /**
457     * Check to see if this is in a list of supported calculated members.
458     * Currently, only the Aggregate and the + function is supported.
459     *
460     * @return <i>true</i> if the calculated member is supported for native
461     *         evaluation
462     */
463    public static boolean isSupportedCalculatedMember(final Member member) {
464        // Is it a supported function?
465        return isSupportedExpressionForCalculatedMember(member.getExpression());
466    }
467
468    public static boolean isSupportedExpressionForCalculatedMember(
469        final Exp expression)
470    {
471      if (expression instanceof ResolvedFunCall) {
472          ResolvedFunCall fun = (ResolvedFunCall) expression;
473
474          if (fun.getFunDef() instanceof AggregateFunDef) {
475              return true;
476          }
477
478          if (fun.getFunDef() instanceof ParenthesesFunDef) {
479              if (fun.getArgs().length == 1) {
480                  for (Exp argsExp : fun.getArgs()) {
481                      if (!isSupportedExpressionForCalculatedMember(argsExp)) {
482                          return false;
483                      }
484                  }
485              }
486              return true;
487          }
488
489          if (fun.getFunDef().getName().equals("+")) {
490              for (Exp argsExp : fun.getArgs()) {
491                  if (!isSupportedExpressionForCalculatedMember(argsExp)) {
492                      return false;
493                  }
494              }
495              return true;
496          }
497      }
498
499      if (expression instanceof MemberExpr) {
500          return true;
501      }
502
503      return false;
504    }
505
506    public static Iterator<Member> getSetFromCalculatedMember(
507        Evaluator evaluator,
508        Member member)
509    {
510        assert member.getExpression() instanceof ResolvedFunCall;
511
512        ResolvedFunCall fun = (ResolvedFunCall) member.getExpression();
513
514        // Calling the main set evaluator to extend this.
515        Exp exp = fun.getArg(0);
516        TupleIterable tupleIterable =
517            evaluator.getSetEvaluator(
518                exp, true).evaluateTupleIterable();
519        Iterable<Member> iterable = tupleIterable.slice(0);
520        return iterable.iterator();
521    }
522
523    /**
524     * Gets a list of unique ordinal cube members to make sure our
525     * cell request isn't unsatisfiable, following the same logic
526     * as RolapEvaluator
527     * @return Unique ordinal cube members
528     */
529    protected static Member[] getUniqueOrdinalMembers(Member[] members) {
530        ArrayList<Integer> currentOrdinals = new ArrayList<Integer>();
531        ArrayList<Member> uniqueMembers = new ArrayList<Member>();
532
533        for (Member member : members) {
534            final RolapMemberBase m = (RolapMemberBase) member;
535            int ordinal = m.getHierarchyOrdinal();
536            if (!currentOrdinals.contains(ordinal)) {
537                uniqueMembers.add(member);
538                currentOrdinals.add(ordinal);
539            }
540        }
541
542        return uniqueMembers.toArray(new Member[uniqueMembers.size()]);
543    }
544
545    protected static Member[] removeMultiPositionSlicerMembers(
546        Member[] members,
547        Evaluator evaluator)
548    {
549        List<Member> slicerMembers = null;
550        if (evaluator instanceof RolapEvaluator) {
551            // get the slicer members from the evaluator
552            slicerMembers =
553                ((RolapEvaluator)evaluator).getSlicerMembers();
554        }
555        if (slicerMembers != null) {
556            // Iterate the list of slicer members, grouping them by hierarchy
557            Map<Hierarchy, Set<Member>> mapOfSlicerMembers =
558                new HashMap<Hierarchy, Set<Member>>();
559            for (Member slicerMember : slicerMembers) {
560                Hierarchy hierarchy = slicerMember.getHierarchy();
561                if (!mapOfSlicerMembers.containsKey(hierarchy)) {
562                    mapOfSlicerMembers.put(hierarchy, new HashSet<Member>());
563                }
564                mapOfSlicerMembers.get(hierarchy).add(slicerMember);
565            }
566            List<Member> listOfMembers = new ArrayList<Member>();
567            // Iterate the given list of members, removing any whose hierarchy
568            // has multiple members on the slicer axis
569            for (Member member : members) {
570                Hierarchy hierarchy = member.getHierarchy();
571                if (!mapOfSlicerMembers.containsKey(hierarchy)
572                        || mapOfSlicerMembers.get(hierarchy).size() < 2)
573                {
574                    listOfMembers.add(member);
575                }
576            }
577            members = listOfMembers.toArray(new Member[listOfMembers.size()]);
578        }
579        return members;
580    }
581
582    /**
583     * Removes calculated and default members from an array.
584     *
585     * <p>This is required only if the default member is
586     * not the ALL member. The time dimension for example, has 1997 as default
587     * member. When we evaluate the query
588     * <pre>
589     *   select NON EMPTY crossjoin(
590     *     {[Time].[1998]}, [Customer].[All].children
591     *  ) on columns
592     *   from [sales]
593     * </pre>
594     * the <code>[Customer].[All].children</code> is evaluated with the default
595     * member <code>[Time].[1997]</code> in the evaluator context. This is wrong
596     * because the NON EMPTY must filter out Customers with no rows in the fact
597     * table for 1998 not 1997. So we do not restrict the time dimension and
598     * fetch all children.
599     *
600     * <p>For calculated members, effect is the same as
601     * {@link #removeCalculatedMembers(java.util.List)}.
602     *
603     * @param members Array of members
604     * @return Members with calculated members removed (except those that are
605     *    leaves in a parent-child hierarchy) and with members that are the
606     *    default member of their hierarchy
607     */
608    private static Member[] removeCalculatedAndDefaultMembers(
609        Member[] members)
610    {
611        List<Member> memberList = new ArrayList<Member>(members.length);
612        for (int i = 0; i < members.length; ++i) {
613            Member member = members[i];
614            // Skip calculated members (except if leaf of parent-child hier)
615            if (member.isCalculated() && !member.isParentChildLeaf()) {
616                continue;
617            }
618
619            // Remove members that are the default for their hierarchy,
620            // except for the measures hierarchy.
621            if (i > 0
622                && member.getHierarchy().getDefaultMember().equals(member))
623            {
624                continue;
625            }
626            memberList.add(member);
627        }
628        return memberList.toArray(new Member[memberList.size()]);
629    }
630
631    static List<Member> removeCalculatedMembers(List<Member> members) {
632        return new FilteredIterableList<Member>(
633            members,
634            new FilteredIterableList.Filter<Member>() {
635                public boolean accept(final Member m) {
636                    return !m.isCalculated() || m.isParentChildLeaf();
637                }
638            });
639    }
640
641    public static boolean containsCalculatedMember(Member[] members) {
642        return containsCalculatedMember(members, false);
643    }
644
645    public static boolean containsCalculatedMember(
646        Member[] members,
647        boolean allowExpandableMembers)
648    {
649        for (Member member : members) {
650            if (member.isCalculated()) {
651                if (allowExpandableMembers) {
652                    if (!isSupportedCalculatedMember(member)) {
653                        return true;
654                    }
655                } else {
656                    return true;
657                }
658            }
659        }
660        return false;
661    }
662
663    /**
664     * Ensures that the table of <code>level</code> is joined to the fact
665     * table
666     *
667     * @param sqlQuery sql query under construction
668     * @param aggStar The aggStar to use, if any.
669     * @param e evaluator corresponding to query
670     * @param level level to be added to query
671     */
672    public static void joinLevelTableToFactTable(
673        SqlQuery sqlQuery,
674        RolapCube baseCube,
675        AggStar aggStar,
676        Evaluator e,
677        RolapCubeLevel level)
678    {
679        RolapStar.Column starColumn = level.getBaseStarKeyColumn(baseCube);
680        if (aggStar != null) {
681            int bitPos = starColumn.getBitPosition();
682            AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
683            AggStar.Table table = aggColumn.getTable();
684            table.addToFrom(sqlQuery, false, true);
685        } else {
686            RolapStar.Table table = starColumn.getTable();
687            assert table != null;
688            table.addToFrom(sqlQuery, false, true);
689        }
690    }
691
692    /**
693     * Creates a "WHERE parent = value" constraint.
694     *
695     * @param sqlQuery the query to modify
696     * @param baseCube base cube if virtual
697     * @param aggStar Definition of the aggregate table, or null
698     * @param parent the list of parent members
699     * @param restrictMemberTypes defines the behavior if <code>parent</code>
700     * is a calculated member. If true, an exception is thrown
701     */
702    public static void addMemberConstraint(
703        SqlQuery sqlQuery,
704        RolapCube baseCube,
705        AggStar aggStar,
706        RolapMember parent,
707        boolean restrictMemberTypes)
708    {
709        List<RolapMember> list = Collections.singletonList(parent);
710        boolean exclude = false;
711        addMemberConstraint(
712            sqlQuery, baseCube, aggStar, list, restrictMemberTypes, false,
713            exclude);
714    }
715
716    /**
717     * Creates a "WHERE exp IN (...)" condition containing the values
718     * of all parents.  All parents must belong to the same level.
719     *
720     * <p>If this constraint is part of a native cross join, there are
721     * multiple constraining members, and the members comprise the cross
722     * product of all unique member keys referenced at each level, then
723     * generating IN expressions would result in incorrect results.  In that
724     * case, "WHERE ((level1 = val1a AND level2 = val2a AND ...)
725     * OR (level1 = val1b AND level2 = val2b AND ...) OR ..." is generated
726     * instead.
727     *
728     * @param sqlQuery the query to modify
729     * @param baseCube base cube if virtual
730     * @param aggStar (not used)
731     * @param members the list of members for this constraint
732     * @param restrictMemberTypes defines the behavior if <code>parents</code>
733     *   contains calculated members.
734     *   If true, and one of the members is calculated, an exception is thrown.
735     * @param crossJoin true if constraint is being generated as part of
736     *   a native crossjoin
737     * @param exclude whether to exclude the members in the SQL predicate.
738     *  e.g. not in { member list}.
739     */
740    public static void addMemberConstraint(
741        SqlQuery sqlQuery,
742        RolapCube baseCube,
743        AggStar aggStar,
744        List<RolapMember> members,
745        boolean restrictMemberTypes,
746        boolean crossJoin,
747        boolean exclude)
748    {
749        if (members.size() == 0) {
750            // Generate a predicate which is always false in order to produce
751            // the empty set.  It would be smarter to avoid executing SQL at
752            // all in this case, but doing it this way avoid special-case
753            // evaluation code.
754            String predicate = "(1 = 0)";
755            if (exclude) {
756                predicate = "(1 = 1)";
757            }
758            sqlQuery.addWhere(predicate);
759            return;
760        }
761
762        // Find out the first(lowest) unique parent level.
763        // Only need to compare members up to that level.
764        RolapMember member = members.get(0);
765        RolapLevel memberLevel = member.getLevel();
766        RolapMember firstUniqueParent = member;
767        RolapLevel firstUniqueParentLevel = null;
768        for (; firstUniqueParent != null
769                 && !firstUniqueParent.getLevel().isUnique();
770             firstUniqueParent = firstUniqueParent.getParentMember())
771        {
772        }
773
774        if (firstUniqueParent != null) {
775            // There's a unique parent along the hierarchy
776            firstUniqueParentLevel = firstUniqueParent.getLevel();
777        }
778
779        String condition = "(";
780
781        // If this constraint is part of a native cross join and there
782        // are multiple values for the parent members, then we can't
783        // use single value IN clauses
784        if (crossJoin
785            && !memberLevel.isUnique()
786            && !membersAreCrossProduct(members))
787        {
788            assert (member.getParentMember() != null);
789            condition +=
790                constrainMultiLevelMembers(
791                    sqlQuery,
792                    baseCube,
793                    aggStar,
794                    members,
795                    firstUniqueParentLevel,
796                    restrictMemberTypes,
797                    exclude);
798        } else {
799            condition +=
800                generateSingleValueInExpr(
801                    sqlQuery,
802                    baseCube,
803                    aggStar,
804                    members,
805                    firstUniqueParentLevel,
806                    restrictMemberTypes,
807                    exclude, true);
808        }
809
810        if (condition.length() > 1) {
811            // condition is not empty
812            condition += ")";
813            sqlQuery.addWhere(condition);
814        }
815    }
816
817    private static StarColumnPredicate getColumnPredicates(
818        RolapStar.Column column,
819        Collection<RolapMember> members)
820    {
821        switch (members.size()) {
822        case 0:
823            return new LiteralStarPredicate(column, false);
824        case 1:
825            return new MemberColumnPredicate(column, members.iterator().next());
826        default:
827            List<StarColumnPredicate> predicateList =
828                new ArrayList<StarColumnPredicate>();
829            for (RolapMember member : members) {
830                predicateList.add(new MemberColumnPredicate(column, member));
831            }
832            return new ListColumnPredicate(column, predicateList);
833        }
834    }
835
836    private static LinkedHashSet<RolapMember> getUniqueParentMembers(
837        Collection<RolapMember> members)
838    {
839        LinkedHashSet<RolapMember> set = new LinkedHashSet<RolapMember>();
840        for (RolapMember m : members) {
841            m = m.getParentMember();
842            if (m != null) {
843                set.add(m);
844            }
845        }
846        return set;
847    }
848
849    /**
850     * Adds to the where clause of a query expression matching a specified
851     * list of members
852     *
853     * @param sqlQuery query containing the where clause
854     * @param baseCube base cube if virtual
855     * @param aggStar aggregate star if available
856     * @param members list of constraining members
857     * @param fromLevel lowest parent level that is unique
858     * @param restrictMemberTypes defines the behavior when calculated members
859     * are present
860     * @param exclude whether to exclude the members. Default is false.
861     *
862     * @return a non-empty String if SQL is generated for the multi-level
863     * member list.
864     */
865    private static String constrainMultiLevelMembers(
866        SqlQuery sqlQuery,
867        RolapCube baseCube,
868        AggStar aggStar,
869        List<RolapMember> members,
870        RolapLevel fromLevel,
871        boolean restrictMemberTypes,
872        boolean exclude)
873    {
874        // Use LinkedHashMap so that keySet() is deterministic.
875        Map<RolapMember, List<RolapMember>> parentChildrenMap =
876            new LinkedHashMap<RolapMember, List<RolapMember>>();
877        StringBuilder condition = new StringBuilder();
878        StringBuilder condition1 = new StringBuilder();
879        if (exclude) {
880            condition.append("not (");
881        }
882
883        // First try to generate IN list for all members
884        if (sqlQuery.getDialect().supportsMultiValueInExpr()) {
885            condition1.append(
886                generateMultiValueInExpr(
887                    sqlQuery,
888                    baseCube,
889                    aggStar,
890                    members,
891                    fromLevel,
892                    restrictMemberTypes,
893                    parentChildrenMap));
894
895            // The members list might contain NULL values in the member levels.
896            //
897            // e.g.
898            //   [USA].[CA].[San Jose]
899            //   [null].[null].[San Francisco]
900            //   [null].[null].[Los Angeles]
901            //   [null].[CA].[San Diego]
902            //   [null].[CA].[Sacramento]
903            //
904            // Pick out such members to generate SQL later.
905            // These members are organized in a map that maps the parant levels
906            // containing NULL to all its children members in the list. e.g.
907            // the member list above becomes the following map, after SQL is
908            // generated for [USA].[CA].[San Jose] in the call above.
909            //
910            //   [null].[null]->([San Francisco], [Los Angeles])
911            //   [null]->([CA].[San Diego], [CA].[Sacramento])
912            //
913            if (parentChildrenMap.isEmpty()) {
914                condition.append(condition1.toString());
915                if (exclude) {
916                    // If there are no NULL values in the member levels, then
917                    // we're done except we need to also explicitly include
918                    // members containing nulls across all levels.
919                    condition.append(")");
920                    condition.append(" or ");
921                    condition.append(
922                        generateMultiValueIsNullExprs(
923                            sqlQuery,
924                            baseCube,
925                            members.get(0),
926                            fromLevel,
927                            aggStar));
928                }
929                return condition.toString();
930            }
931        } else {
932            // Multi-value IN list not supported
933            // Classify members into List that share the same parent.
934            //
935            // Using the same example as above, the resulting map will be
936            //   [USA].[CA]->[San Jose]
937            //   [null].[null]->([San Francisco], [Los Angesles])
938            //   [null].[CA]->([San Diego],[Sacramento])
939            //
940            // The idea is to be able to "compress" the original member list
941            // into groups that can use single value IN list for part of the
942            // comparison that does not involve NULLs
943            //
944            for (RolapMember m : members) {
945                if (m.isCalculated()) {
946                    if (restrictMemberTypes) {
947                        throw Util.newInternal(
948                            "addMemberConstraint: cannot "
949                            + "restrict SQL to calculated member :" + m);
950                    }
951                    continue;
952                }
953                RolapMember p = m.getParentMember();
954                List<RolapMember> childrenList = parentChildrenMap.get(p);
955                if (childrenList == null) {
956                    childrenList = new ArrayList<RolapMember>();
957                    parentChildrenMap.put(p, childrenList);
958                }
959                childrenList.add(m);
960            }
961        }
962
963        // Now we try to generate predicates for the remaining
964        // parent-children group.
965
966        // Note that NULLs are not used to enforce uniqueness
967        // so we ignore the fromLevel here.
968        boolean firstParent = true;
969        StringBuilder condition2 = new StringBuilder();
970
971        if (condition1.length() > 0) {
972            // Some members have already been translated into IN list.
973            firstParent = false;
974            condition.append(condition1.toString());
975            condition.append(" or ");
976        }
977
978        RolapLevel memberLevel = members.get(0).getLevel();
979
980        // The children for each parent are turned into IN list so they
981        // should not contain null.
982        for (RolapMember p : parentChildrenMap.keySet()) {
983            assert p != null;
984            if (condition2.toString().length() > 0) {
985                condition2.append(" or ");
986            }
987
988            condition2.append("(");
989
990            // First generate ANDs for all members in the parent lineage of
991            // this parent-children group
992            int levelCount = 0;
993            for (RolapMember gp = p; gp != null; gp = gp.getParentMember()) {
994                if (gp.isAll()) {
995                    // Ignore All member
996                    // Get the next parent
997                    continue;
998                }
999
1000                RolapLevel level = gp.getLevel();
1001
1002                // add the level to the FROM clause if this is the
1003                // first parent-children group we're generating sql for
1004                if (firstParent) {
1005                    RolapHierarchy hierarchy = level.getHierarchy();
1006
1007                    // this method can be called within the context of shared
1008                    // members, outside of the normal rolap star, therefore
1009                    // we need to check the level to see if it is a shared or
1010                    // cube level.
1011
1012                    RolapStar.Column column = null;
1013                    if (level instanceof RolapCubeLevel) {
1014                        column = ((RolapCubeLevel)level).getBaseStarKeyColumn(
1015                            baseCube);
1016                    }
1017                    if (column != null) {
1018                        if (aggStar != null) {
1019                            int bitPos = column.getBitPosition();
1020                            AggStar.Table.Column aggColumn =
1021                                aggStar.lookupColumn(bitPos);
1022                            AggStar.Table table = aggColumn.getTable();
1023                            table.addToFrom(sqlQuery, false, true);
1024                        } else {
1025                            RolapStar.Table targetTable = column.getTable();
1026                            hierarchy.addToFrom(sqlQuery, targetTable);
1027                        }
1028                    } else {
1029                        assert (aggStar == null);
1030                        hierarchy.addToFrom(sqlQuery, level.getKeyExp());
1031                    }
1032                }
1033
1034                if (levelCount > 0) {
1035                    condition2.append(" and ");
1036                }
1037                ++levelCount;
1038
1039                condition2.append(
1040                    constrainLevel(
1041                        level,
1042                        sqlQuery,
1043                        baseCube,
1044                        aggStar,
1045                        getColumnValue(
1046                            level.nameExp != null
1047                                ? gp.getName()
1048                                : gp.getKey(),
1049                            sqlQuery.getDialect(),
1050                            level.getDatatype()),
1051                        false));
1052                if (gp.getLevel() == fromLevel) {
1053                    // SQL is completely generated for this parent
1054                    break;
1055                }
1056            }
1057            firstParent = false;
1058
1059            // Next, generate children for this parent-children group
1060            List<RolapMember> children = parentChildrenMap.get(p);
1061
1062            // If no children to be generated for this parent then we are done
1063            if (!children.isEmpty()) {
1064                Map<RolapMember, List<RolapMember>> tmpParentChildrenMap =
1065                    new HashMap<RolapMember, List<RolapMember>>();
1066
1067                if (levelCount > 0) {
1068                    condition2.append(" and ");
1069                }
1070                RolapLevel childrenLevel =
1071                    (RolapLevel)(p.getLevel().getChildLevel());
1072
1073                if (sqlQuery.getDialect().supportsMultiValueInExpr()
1074                    && childrenLevel != memberLevel)
1075                {
1076                    // Multi-level children and multi-value IN list supported
1077                    condition2.append(
1078                        generateMultiValueInExpr(
1079                            sqlQuery,
1080                            baseCube,
1081                            aggStar,
1082                            children,
1083                            childrenLevel,
1084                            restrictMemberTypes,
1085                            tmpParentChildrenMap));
1086                    assert tmpParentChildrenMap.isEmpty();
1087                } else {
1088                    // Can only be single level children
1089                    // If multi-value IN list not supported, children will be on
1090                    // the same level as members list. Only single value IN list
1091                    // needs to be generated for this case.
1092                    assert childrenLevel == memberLevel;
1093                    condition2.append(
1094                        generateSingleValueInExpr(
1095                            sqlQuery,
1096                            baseCube,
1097                            aggStar,
1098                            children,
1099                            childrenLevel,
1100                            restrictMemberTypes,
1101                            false, true));
1102                }
1103            }
1104            // SQL is complete for this parent-children group.
1105            condition2.append(")");
1106        }
1107
1108        // In the case where multi-value IN expressions are not generated,
1109        // condition2 contains the entire filter condition.  In the
1110        // case of excludes, we also need to explicitly include null values,
1111        // minus the ones that are referenced in condition2.  Therefore,
1112        // we OR on a condition that corresponds to an OR'ing of IS NULL
1113        // filters on each level PLUS an exclusion of condition2.
1114        //
1115        // Note that the expression generated is non-optimal in the case where
1116        // multi-value IN's cannot be used because we end up excluding
1117        // non-null values as well as the null ones.  Ideally, we only need to
1118        // exclude the expressions corresponding to nulls, which is possible
1119        // in the multi-value IN case, since we have a map of the null values.
1120        condition.append(condition2.toString());
1121        if (exclude) {
1122            condition.append(") or (");
1123            condition.append(
1124                generateMultiValueIsNullExprs(
1125                    sqlQuery,
1126                    baseCube,
1127                    members.get(0),
1128                    fromLevel,
1129                    aggStar));
1130            condition.append(" and not(");
1131            condition.append(condition2.toString());
1132            condition.append("))");
1133        }
1134
1135        return condition.toString();
1136    }
1137
1138    /**
1139     * @param members list of members
1140     *
1141     * @return true if the members comprise the cross product of all unique
1142     * member keys referenced at each level
1143     */
1144    private static boolean membersAreCrossProduct(List<RolapMember> members)
1145    {
1146        int crossProdSize = getNumUniqueMemberKeys(members);
1147        for (Collection<RolapMember> parents = getUniqueParentMembers(members);
1148            !parents.isEmpty(); parents = getUniqueParentMembers(parents))
1149        {
1150            crossProdSize *= parents.size();
1151        }
1152        return (crossProdSize == members.size());
1153    }
1154
1155    /**
1156     * @param members list of members
1157     *
1158     * @return number of unique member keys in a list of members
1159     */
1160    private static int getNumUniqueMemberKeys(List<RolapMember> members)
1161    {
1162        final HashSet<Object> set = new HashSet<Object>();
1163        for (RolapMember m : members) {
1164            set.add(m.getKey());
1165        }
1166        return set.size();
1167    }
1168
1169    /**
1170     * @param key key corresponding to a member
1171     * @param dialect sql dialect being used
1172     * @param datatype data type of the member
1173     *
1174     * @return string value corresponding to the member
1175     */
1176    private static String getColumnValue(
1177        Object key,
1178        Dialect dialect,
1179        Dialect.Datatype datatype)
1180    {
1181        if (key != RolapUtil.sqlNullValue) {
1182            return key.toString();
1183        } else {
1184            return RolapUtil.mdxNullLiteral();
1185        }
1186    }
1187
1188    /**
1189     * Generates a sql expression constraining a level by some value
1190     *
1191     * @param level the level
1192     * @param query the query that the sql expression will be added to
1193     * @param baseCube base cube for virtual levels
1194     * @param aggStar aggregate star if available
1195     * @param columnValue value constraining the level
1196     * @param caseSensitive if true, need to handle case sensitivity of the
1197     * member value
1198     *
1199     * @return generated string corresponding to the expression
1200     */
1201    public static String constrainLevel(
1202        RolapLevel level,
1203        SqlQuery query,
1204        RolapCube baseCube,
1205        AggStar aggStar,
1206        String columnValue,
1207        boolean caseSensitive)
1208    {
1209        // this method can be called within the context of shared members,
1210        // outside of the normal rolap star, therefore we need to
1211        // check the level to see if it is a shared or cube level.
1212
1213        RolapStar.Column column = null;
1214        if (level instanceof RolapCubeLevel) {
1215            column = ((RolapCubeLevel)level).getBaseStarKeyColumn(baseCube);
1216        }
1217
1218        String columnString;
1219        Dialect.Datatype datatype;
1220        if (column != null) {
1221            if (column.getNameColumn() == null) {
1222                datatype = level.getDatatype();
1223            } else {
1224                column = column.getNameColumn();
1225                // The schema doesn't specify the datatype of the name column,
1226                // but we presume that it is a string.
1227                datatype = Dialect.Datatype.String;
1228            }
1229            if (aggStar != null) {
1230                // this makes the assumption that the name column is the same
1231                // as the key column
1232                int bitPos = column.getBitPosition();
1233                AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
1234                columnString = aggColumn.generateExprString(query);
1235            } else {
1236                columnString = column.generateExprString(query);
1237            }
1238        } else {
1239            assert (aggStar == null);
1240            MondrianDef.Expression exp = level.getNameExp();
1241            if (exp == null) {
1242                exp = level.getKeyExp();
1243                datatype = level.getDatatype();
1244            } else {
1245                // The schema doesn't specify the datatype of the name column,
1246                // but we presume that it is a string.
1247                datatype = Dialect.Datatype.String;
1248            }
1249            columnString = exp.getExpression(query);
1250        }
1251
1252        String constraint;
1253
1254        if (RolapUtil.mdxNullLiteral().equalsIgnoreCase(columnValue)) {
1255            constraint = columnString + " is " + RolapUtil.sqlNullLiteral;
1256        } else {
1257            if (datatype.isNumeric()) {
1258                // make sure it can be parsed
1259                Double.valueOf(columnValue);
1260            }
1261            final StringBuilder buf = new StringBuilder();
1262            query.getDialect().quote(buf, columnValue, datatype);
1263            String value = buf.toString();
1264            if (caseSensitive && datatype == Dialect.Datatype.String) {
1265                // Some databases (like DB2) compare case-sensitive. We convert
1266                // the value to upper-case in the DBMS (e.g. UPPER('Foo'))
1267                // rather than in Java (e.g. 'FOO') in case the DBMS is running
1268                // a different locale.
1269                if (!MondrianProperties.instance().CaseSensitive.get()) {
1270                    columnString = query.getDialect().toUpper(columnString);
1271                    value = query.getDialect().toUpper(value);
1272                }
1273            }
1274
1275            constraint = columnString + " = " + value;
1276        }
1277
1278        return constraint;
1279    }
1280
1281    /**
1282     * Generates a sql expression constraining a level by some value
1283     *
1284     * @param exp Key expression
1285     * @param datatype Key datatype
1286     * @param query the query that the sql expression will be added to
1287     * @param columnValue value constraining the level
1288     *
1289     * @return generated string corresponding to the expression
1290     */
1291    public static String constrainLevel2(
1292        SqlQuery query,
1293        MondrianDef.Expression exp,
1294        Dialect.Datatype datatype,
1295        Comparable columnValue)
1296    {
1297        String columnString = exp.getExpression(query);
1298        if (columnValue == RolapUtil.sqlNullValue) {
1299            return columnString + " is " + RolapUtil.sqlNullLiteral;
1300        } else {
1301            final StringBuilder buf = new StringBuilder();
1302            buf.append(columnString);
1303            buf.append(" = ");
1304            query.getDialect().quote(buf, columnValue, datatype);
1305            return buf.toString();
1306        }
1307    }
1308
1309    /**
1310     * Generates a multi-value IN expression corresponding to a list of
1311     * member expressions, and adds the expression to the WHERE clause
1312     * of a query, provided the member values are all non-null
1313     *
1314     * @param sqlQuery query containing the where clause
1315     * @param baseCube base cube if virtual
1316     * @param aggStar aggregate star if available
1317     * @param members list of constraining members
1318     * @param fromLevel lowest parent level that is unique
1319     * @param restrictMemberTypes defines the behavior when calculated members
1320     *        are present
1321     * @param parentWithNullToChildrenMap upon return this map contains members
1322     *        that have Null values in its (parent) levels
1323     * @return a non-empty String if multi-value IN list was generated for some
1324     *        members
1325     */
1326    private static String generateMultiValueInExpr(
1327        SqlQuery sqlQuery,
1328        RolapCube baseCube,
1329        AggStar aggStar,
1330        List<RolapMember> members,
1331        RolapLevel fromLevel,
1332        boolean restrictMemberTypes,
1333        Map<RolapMember, List<RolapMember>> parentWithNullToChildrenMap)
1334    {
1335        final StringBuilder columnBuf = new StringBuilder();
1336        final StringBuilder valueBuf = new StringBuilder();
1337        final StringBuilder memberBuf = new StringBuilder();
1338
1339        columnBuf.append("(");
1340
1341        // generate the left-hand side of the IN expression
1342        int ordinalInMultiple = 0;
1343        for (RolapMember m = members.get(0); m != null; m = m.getParentMember())
1344        {
1345            if (m.isAll()) {
1346                continue;
1347            }
1348            String columnString = getColumnString(
1349                sqlQuery, aggStar, m.getLevel(), baseCube);
1350
1351            if (ordinalInMultiple++ > 0) {
1352                columnBuf.append(", ");
1353            }
1354
1355            columnBuf.append(columnString);
1356
1357            // Only needs to compare up to the first(lowest) unique level.
1358            if (m.getLevel() == fromLevel) {
1359                break;
1360            }
1361        }
1362
1363        columnBuf.append(")");
1364
1365        // generate the RHS of the IN predicate
1366        valueBuf.append("(");
1367        int memberOrdinal = 0;
1368        for (RolapMember m : members) {
1369            if (m.isCalculated()) {
1370                if (restrictMemberTypes) {
1371                    throw Util.newInternal(
1372                        "addMemberConstraint: cannot "
1373                        + "restrict SQL to calculated member :" + m);
1374                }
1375                continue;
1376            }
1377
1378            ordinalInMultiple = 0;
1379            memberBuf.setLength(0);
1380            memberBuf.append("(");
1381
1382            boolean containsNull = false;
1383            for (RolapMember p = m; p != null; p = p.getParentMember()) {
1384                if (p.isAll()) {
1385                    // Ignore the ALL level.
1386                    // Generate SQL condition for the next level
1387                    continue;
1388                }
1389                RolapLevel level = p.getLevel();
1390
1391                String value = getColumnValue(
1392                    p.getKey(),
1393                    sqlQuery.getDialect(),
1394                    level.getDatatype());
1395
1396                // If parent at a level is NULL, record this parent and all
1397                // its children(if there's any)
1398                if (RolapUtil.mdxNullLiteral().equalsIgnoreCase(value)) {
1399                    // Add to the nullParent map
1400                    List<RolapMember> childrenList =
1401                        parentWithNullToChildrenMap.get(p);
1402                    if (childrenList == null) {
1403                        childrenList = new ArrayList<RolapMember>();
1404                        parentWithNullToChildrenMap.put(p, childrenList);
1405                    }
1406
1407                    // If p has children
1408                    if (m != p) {
1409                        childrenList.add(m);
1410                    }
1411
1412                    // Skip generating condition for this parent
1413                    containsNull = true;
1414                    break;
1415                }
1416
1417                if (ordinalInMultiple++ > 0) {
1418                    memberBuf.append(", ");
1419                }
1420
1421                sqlQuery.getDialect().quote(
1422                    memberBuf, value, level.getDatatype());
1423
1424                // Only needs to compare up to the first(lowest) unique level.
1425                if (p.getLevel() == fromLevel) {
1426                    break;
1427                }
1428            }
1429
1430            // Now check if sql string is sucessfully generated for this member.
1431            // If parent levels do not contain NULL then SQL must have been
1432            // generated successfully.
1433            if (!containsNull) {
1434                memberBuf.append(")");
1435                if (memberOrdinal++ > 0) {
1436                    valueBuf.append(", ");
1437                }
1438                valueBuf.append(memberBuf);
1439            }
1440        }
1441
1442        StringBuilder condition = new StringBuilder();
1443        if (memberOrdinal > 0) {
1444            // SQLs are generated for some members.
1445            condition.append(columnBuf);
1446            condition.append(" in ");
1447            condition.append(valueBuf);
1448            condition.append(")");
1449        }
1450
1451        return condition.toString();
1452    }
1453
1454    /**
1455     * Returns the column expression for the level, assuring
1456     * appropriate tables are added to the from clause of
1457     * sqlQuery if required.
1458     * Determines the correct table and field based on the cube
1459     * and whether an AggStar is present.
1460     */
1461    private static String getColumnString(
1462        SqlQuery sqlQuery, AggStar aggStar, RolapLevel level,
1463        RolapCube baseCube)
1464    {
1465        String columnString;
1466        RolapStar.Column column = null;
1467        if (level instanceof RolapCubeLevel) {
1468            // this method can be called within the context of shared members,
1469            // outside of the normal rolap star, therefore we need to
1470            // check the level to see if it is a shared or cube level.
1471            column = ((RolapCubeLevel)level).getBaseStarKeyColumn(baseCube);
1472        }
1473
1474        // REVIEW: The following code mostly uses the name column (or name
1475        // expression) of the level. Shouldn't it use the key column (or key
1476        // expression)?
1477        RolapHierarchy hierarchy = level.getHierarchy();
1478        if (column != null) {
1479            if (aggStar != null) {
1480                // this assumes that the name column is identical to the
1481                // id column
1482                int bitPos = column.getBitPosition();
1483                AggStar.Table.Column aggColumn =
1484                    aggStar.lookupColumn(bitPos);
1485                AggStar.Table table = aggColumn.getTable();
1486                table.addToFrom(sqlQuery, false, true);
1487                columnString = aggColumn.generateExprString(sqlQuery);
1488            } else {
1489                RolapStar.Table targetTable = column.getTable();
1490                hierarchy.addToFrom(sqlQuery, targetTable);
1491                columnString = column.generateExprString(sqlQuery);
1492            }
1493        } else {
1494            assert (aggStar == null);
1495            hierarchy.addToFrom(sqlQuery, level.getKeyExp());
1496
1497            MondrianDef.Expression nameExp = level.getNameExp();
1498            if (nameExp == null) {
1499                nameExp = level.getKeyExp();
1500            }
1501            columnString = nameExp.getExpression(sqlQuery);
1502        }
1503        return columnString;
1504    }
1505
1506
1507    /**
1508     * Generates an expression that is an OR of IS NULL expressions, one
1509     * per level in a RolapMember.
1510     *
1511     * @param sqlQuery query corresponding to the expression
1512     * @param baseCube base cube if virtual
1513     * @param member the RolapMember
1514     * @param fromLevel lowest parent level that is unique
1515     * @param aggStar aggregate star if available
1516     * @return the text of the expression
1517     */
1518    private static String generateMultiValueIsNullExprs(
1519        SqlQuery sqlQuery,
1520        RolapCube baseCube,
1521        RolapMember member,
1522        RolapLevel fromLevel,
1523        AggStar aggStar)
1524    {
1525        final StringBuilder conditionBuf = new StringBuilder();
1526
1527        conditionBuf.append("(");
1528
1529        // generate the left-hand side of the IN expression
1530        boolean isFirstLevelInMultiple = true;
1531        for (RolapMember m = member; m != null; m = m.getParentMember()) {
1532            if (m.isAll()) {
1533                continue;
1534            }
1535
1536
1537            String columnString = getColumnString(
1538                sqlQuery, aggStar, m.getLevel(), baseCube);
1539
1540            if (!isFirstLevelInMultiple) {
1541                conditionBuf.append(" or ");
1542            } else {
1543                isFirstLevelInMultiple = false;
1544            }
1545
1546            conditionBuf.append(columnString);
1547            conditionBuf.append(" is null");
1548
1549            // Only needs to compare up to the first(lowest) unique level.
1550            if (m.getLevel() == fromLevel) {
1551                break;
1552            }
1553        }
1554
1555        conditionBuf.append(")");
1556        return conditionBuf.toString();
1557    }
1558
1559    /**
1560     * Generates a multi-value IN expression corresponding to a list of
1561     * member expressions, and adds the expression to the WHERE clause
1562     * of a query, provided the member values are all non-null
1563     *
1564     *
1565     * @param sqlQuery query containing the where clause
1566     * @param baseCube base cube if virtual
1567     * @param aggStar aggregate star if available
1568     * @param members list of constraining members
1569     * @param fromLevel lowest parent level that is unique
1570     * @param restrictMemberTypes defines the behavior when calculated members
1571     *        are present
1572     * @param exclude whether to exclude the members. Default is false.
1573     * @param includeParentLevels whether to include IN list constraint
1574     *                            for parent levels.
1575     * @return a non-empty String if IN list was generated for the members.
1576     */
1577    private static String generateSingleValueInExpr(
1578        SqlQuery sqlQuery,
1579        RolapCube baseCube,
1580        AggStar aggStar,
1581        List<RolapMember> members,
1582        RolapLevel fromLevel,
1583        boolean restrictMemberTypes,
1584        boolean exclude,
1585        boolean includeParentLevels)
1586    {
1587        int maxConstraints =
1588            MondrianProperties.instance().MaxConstraints.get();
1589        Dialect dialect = sqlQuery.getDialect();
1590
1591        String condition = "";
1592        boolean firstLevel = true;
1593        for (Collection<RolapMember> c = members;
1594            !c.isEmpty();
1595            c = getUniqueParentMembers(c))
1596        {
1597            RolapMember m = c.iterator().next();
1598            if (m.isAll()) {
1599                continue;
1600            }
1601            if (m.isNull()) {
1602                return "1 = 0";
1603            }
1604            if (m.isCalculated() && !m.isParentChildLeaf()) {
1605                if (restrictMemberTypes) {
1606                    throw Util.newInternal(
1607                        "addMemberConstraint: cannot "
1608                        + "restrict SQL to calculated member :" + m);
1609                }
1610                continue;
1611            }
1612
1613            boolean containsNullKey = false;
1614            Iterator<RolapMember> it = c.iterator();
1615            while (it.hasNext()) {
1616                m = it.next();
1617                if (m.getKey() == RolapUtil.sqlNullValue) {
1618                    containsNullKey = true;
1619                }
1620            }
1621
1622            RolapLevel level = m.getLevel();
1623            RolapHierarchy hierarchy = level.getHierarchy();
1624
1625            // this method can be called within the context of shared members,
1626            // outside of the normal rolap star, therefore we need to
1627            // check the level to see if it is a shared or cube level.
1628
1629            RolapStar.Column column = null;
1630            if (level instanceof RolapCubeLevel) {
1631                column = ((RolapCubeLevel)level).getBaseStarKeyColumn(baseCube);
1632            }
1633
1634            String q;
1635            if (column != null) {
1636                if (aggStar != null) {
1637                    int bitPos = column.getBitPosition();
1638                    AggStar.Table.Column aggColumn =
1639                        aggStar.lookupColumn(bitPos);
1640                    if (aggColumn == null) {
1641                        throw Util.newInternal(
1642                            "AggStar " + aggStar + " has no column for "
1643                            + column + " (bitPos " + bitPos + ")");
1644                    }
1645                    AggStar.Table table = aggColumn.getTable();
1646                    table.addToFrom(sqlQuery, false, true);
1647                    q = aggColumn.generateExprString(sqlQuery);
1648                } else {
1649                    RolapStar.Table targetTable = column.getTable();
1650                    hierarchy.addToFrom(sqlQuery, targetTable);
1651                    q = column.generateExprString(sqlQuery);
1652                }
1653            } else {
1654                assert (aggStar == null);
1655                hierarchy.addToFrom(sqlQuery, level.getKeyExp());
1656                q = level.getKeyExp().getExpression(sqlQuery);
1657            }
1658
1659            StarColumnPredicate cc = getColumnPredicates(column, c);
1660
1661            if (!dialect.supportsUnlimitedValueList()
1662                && cc instanceof ListColumnPredicate
1663                && ((ListColumnPredicate) cc).getPredicates().size()
1664                > maxConstraints)
1665            {
1666                // Simply get them all, do not create where-clause.
1667                // Below are two alternative approaches (and code). They
1668                // both have problems.
1669                LOG.debug(
1670                    MondrianResource.instance()
1671                        .NativeSqlInClauseTooLarge.str(
1672                            level.getUniqueName(),
1673                            maxConstraints + ""));
1674            } else {
1675                String where =
1676                    RolapStar.Column.createInExpr(
1677                        q, cc, level.getDatatype(), sqlQuery);
1678                if (!where.equals("true")) {
1679                    if (!firstLevel) {
1680                        if (exclude) {
1681                            condition += " or ";
1682                        } else {
1683                            condition += " and ";
1684                        }
1685                    } else {
1686                        firstLevel = false;
1687                    }
1688                    if (exclude) {
1689                        where = "not (" + where + ")";
1690                        if (!containsNullKey) {
1691                            // Null key fails all filters so should add it here
1692                            // if not already excluded.  E.g., if the original
1693                            // exclusion filter is :
1694                            //
1695                            // not(year = '1997' and quarter in ('Q1','Q3'))
1696                            //
1697                            // then with IS NULL checks added, the filter
1698                            // becomes:
1699                            //
1700                            // (not(year = '1997') or year is null) or
1701                            // (not(quarter in ('Q1','Q3')) or quarter is null)
1702                            where = "(" + where + " or " + "(" + q
1703                                + " is null))";
1704                        }
1705                    }
1706                    condition += where;
1707                }
1708            }
1709
1710            if (m.getLevel().isUnique() || m.getLevel() == fromLevel
1711                || !includeParentLevels)
1712            {
1713                break; // no further qualification needed
1714            }
1715        }
1716
1717        return condition;
1718    }
1719}
1720
1721// End SqlConstraintUtils.java