001 /*
002 // $Id: //open/mondrian/src/main/mondrian/rolap/SqlConstraintUtils.java#44 $
003 // This software is subject to the terms of the Common Public License
004 // Agreement, available at the following URL:
005 // http://www.opensource.org/licenses/cpl.html.
006 // Copyright (C) 2004-2005 TONBELLER AG
007 // All Rights Reserved.
008 // You must accept the terms of that agreement to use this software.
009 */
010 package mondrian.rolap;
011
012 import java.util.*;
013
014 import mondrian.olap.Evaluator;
015 import mondrian.olap.Member;
016 import mondrian.olap.MondrianDef;
017 import mondrian.olap.MondrianProperties;
018 import mondrian.olap.Util;
019 import mondrian.rolap.agg.*;
020 import mondrian.rolap.sql.SqlQuery;
021 import mondrian.rolap.aggmatcher.AggStar;
022 import mondrian.util.FilteredIterableList;
023
024 /**
025 * Utility class used by implementations of {@link mondrian.rolap.sql.SqlConstraint},
026 * used to generate constraints into {@link mondrian.rolap.sql.SqlQuery}.
027 *
028 * @author av
029 * @since Nov 21, 2005
030 * @version $Id: //open/mondrian/src/main/mondrian/rolap/SqlConstraintUtils.java#44 $
031 */
032 public class SqlConstraintUtils {
033
034 /** Utility class */
035 private SqlConstraintUtils() {
036 }
037
038 /**
039 * For every restricting member in the current context, generates
040 * a WHERE condition and a join to the fact table.
041 *
042 * @param sqlQuery the query to modify
043 * @param aggStar Aggregate table, or null if query is against fact table
044 * @param restrictMemberTypes defines the behavior if the current context contains
045 * calculated members.
046 * If true, an exception is thrown.
047 * @param evaluator Evaluator
048 */
049 public static void addContextConstraint(
050 SqlQuery sqlQuery,
051 AggStar aggStar,
052 Evaluator evaluator,
053 boolean restrictMemberTypes) {
054 // Add constraint using the current evaluator context
055 Member[] members = evaluator.getMembers();
056
057 if (restrictMemberTypes) {
058 if (containsCalculatedMember(members)) {
059 throw Util.newInternal(
060 "can not restrict SQL to calculated Members");
061 }
062 } else {
063 List<Member> memberList =
064 removeCalculatedMembers(Arrays.asList(members));
065 memberList = removeDefaultMembers(memberList);
066 members = memberList.toArray(new Member[memberList.size()]);
067 }
068
069 final CellRequest request =
070 RolapAggregationManager.makeRequest(members);
071 if (request == null) {
072 if (restrictMemberTypes) {
073 throw Util.newInternal("CellRequest is null - why?");
074 }
075 // One or more of the members was null or calculated, so the
076 // request is impossible to satisfy.
077 return;
078 }
079 RolapStar.Column[] columns = request.getConstrainedColumns();
080 Object[] values = request.getSingleValues();
081 int arity = columns.length;
082 // following code is similar to AbstractQuerySpec#nonDistinctGenerateSQL()
083 for (int i = 0; i < arity; i++) {
084 RolapStar.Column column = columns[i];
085
086 String expr;
087 if (aggStar != null) {
088 int bitPos = column.getBitPosition();
089 AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
090 AggStar.Table table = aggColumn.getTable();
091 table.addToFrom(sqlQuery, false, true);
092
093 expr = aggColumn.generateExprString(sqlQuery);
094 } else {
095 RolapStar.Table table = column.getTable();
096 table.addToFrom(sqlQuery, false, true);
097
098 expr = column.generateExprString(sqlQuery);
099 }
100
101 final String value = String.valueOf(values[i]);
102 if (RolapUtil.mdxNullLiteral.equalsIgnoreCase(value)) {
103 sqlQuery.addWhere(
104 expr,
105 " is ",
106 RolapUtil.sqlNullLiteral);
107 } else {
108 if (column.getDatatype().isNumeric()) {
109 // make sure it can be parsed
110 Double.valueOf(value);
111 }
112 final StringBuilder buf = new StringBuilder();
113 sqlQuery.getDialect().quote(buf, value, column.getDatatype());
114 sqlQuery.addWhere(
115 expr,
116 " = ",
117 buf.toString());
118 }
119 }
120 }
121
122 /**
123 * Removes the default members from an array.
124 *
125 * <p>This is required only if the default member is
126 * not the ALL member. The time dimension for example, has 1997 as default
127 * member. When we evaluate the query
128 * <pre>
129 * select NON EMPTY crossjoin(
130 * {[Time].[1998]}, [Customer].[All].children
131 * ) on columns
132 * from [sales]
133 * </pre>
134 * the <code>[Customer].[All].children</code> is evaluated with the default
135 * member <code>[Time].[1997]</code> in the evaluator context. This is wrong
136 * because the NON EMPTY must filter out Customers with no rows in the fact
137 * table for 1998 not 1997. So we do not restrict the time dimension and
138 * fetch all children.
139 *
140 * @param members Array of members
141 * @return Array of members with default members removed
142 */
143 private static List<Member> removeDefaultMembers(List<Member> members) {
144 List<Member> result = new ArrayList<Member>();
145 result.add(members.get(0)); // add the measure
146 for (int i = 1; i < members.size(); i++) {
147 Member m = members.get(i);
148 if (m.getHierarchy().getDefaultMember().equals(m)) {
149 continue;
150 }
151 result.add(m);
152 }
153 return result;
154 }
155
156 static List<Member> removeCalculatedMembers(List<Member> members) {
157 List<Member> result = new FilteredIterableList<Member>(members,
158 new FilteredIterableList.Filter<Member>() {
159 public boolean accept(final Member m) {
160 return !m.isCalculated();
161 }
162 });
163 return result;
164 }
165
166 public static boolean containsCalculatedMember(Member[] members) {
167 for (Member member : members) {
168 if (member.isCalculated()) {
169 return true;
170 }
171 }
172 return false;
173 }
174
175 /**
176 * Ensures that the table of <code>level</code> is joined to the fact
177 * table
178 *
179 * @param sqlQuery sql query under construction
180 * @param aggStar
181 * @param e evaluator corresponding to query
182 * @param level level to be added to query
183 */
184 public static void joinLevelTableToFactTable(
185 SqlQuery sqlQuery,
186 RolapCube baseCube,
187 AggStar aggStar,
188 Evaluator e,
189 RolapCubeLevel level)
190 {
191 RolapStar.Column starColumn = level.getBaseStarKeyColumn(baseCube);
192 if (aggStar != null) {
193 int bitPos = starColumn.getBitPosition();
194 AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
195 AggStar.Table table = aggColumn.getTable();
196 table.addToFrom(sqlQuery, false, true);
197 } else {
198 RolapStar.Table table = starColumn.getTable();
199 assert table != null;
200 table.addToFrom(sqlQuery, false, true);
201 }
202 }
203
204 /**
205 * Creates a "WHERE parent = value" constraint.
206 *
207 * @param sqlQuery the query to modify
208 * @param baseCube base cube if virtual
209 * @param aggStar Definition of the aggregate table, or null
210 * @param parent the list of parent members
211 * @param restrictMemberTypes defines the behavior if <code>parent</code>
212 * is a calculated member. If true, an exception is thrown
213 */
214 public static void addMemberConstraint(
215 SqlQuery sqlQuery,
216 RolapCube baseCube,
217 AggStar aggStar,
218 RolapMember parent,
219 boolean restrictMemberTypes)
220 {
221 List<RolapMember> list = Collections.singletonList(parent);
222 addMemberConstraint(
223 sqlQuery, baseCube, aggStar, list, restrictMemberTypes, false);
224 }
225
226 /**
227 * Creates a "WHERE exp IN (...)" condition containing the values
228 * of all parents. All parents must belong to the same level.
229 *
230 * <p>If this constraint is part of a native cross join, there are
231 * multiple constraining members, and the members comprise the cross
232 * product of all unique member keys referenced at each level, then
233 * generating IN expressions would result in incorrect results. In that
234 * case, "WHERE ((level1 = val1a AND level2 = val2a AND ...)
235 * OR (level1 = val1b AND level2 = val2b AND ...) OR ..." is generated
236 * instead.
237 *
238 * @param sqlQuery the query to modify
239 * @param baseCube base cube if virtual
240 * @param aggStar (not used)
241 * @param members the list of members for this constraint
242 * @param restrictMemberTypes defines the behavior if <code>parents</code>
243 * contains calculated members.
244 * If true, and one of the members is calculated, an exception is thrown.
245 * @param crossJoin true if constraint is being generated as part of
246 * a native crossjoin
247 */
248 public static void addMemberConstraint(
249 SqlQuery sqlQuery,
250 RolapCube baseCube,
251 AggStar aggStar,
252 List<RolapMember> members,
253 boolean restrictMemberTypes,
254 boolean crossJoin)
255 {
256 if (members.size() == 0) {
257 // Generate a predicate which is always false in order to produce
258 // the empty set. It would be smarter to avoid executing SQL at
259 // all in this case, but doing it this way avoid special-case
260 // evaluation code.
261 sqlQuery.addWhere("(1 = 0)");
262 return;
263 }
264
265 // Find out the first(lowest) unqiue parent level.
266 // Only need to compare members up to that level.
267 RolapMember member = members.get(0);
268 RolapLevel memberLevel = member.getLevel();
269 RolapMember firstUniqueParent = member;
270 RolapLevel firstUniqueParentLevel = null;
271 for (; firstUniqueParent != null &&
272 !firstUniqueParent.getLevel().isUnique();
273 firstUniqueParent = firstUniqueParent.getParentMember()) {
274 }
275
276 if (firstUniqueParent != null) {
277 // There's a unique parent along the hiearchy
278 firstUniqueParentLevel = firstUniqueParent.getLevel();
279 }
280
281 String condition = "(";
282
283 // If this constraint is part of a native cross join and there
284 // are multiple values for the parent members, then we can't
285 // use single value IN clauses
286 if (crossJoin &&
287 !memberLevel.isUnique() && !membersAreCrossProduct(members)) {
288 assert (member.getParentMember() != null);
289 condition +=
290 constrainMultiLevelMembers(
291 sqlQuery,
292 baseCube,
293 members,
294 firstUniqueParentLevel,
295 restrictMemberTypes);
296 } else {
297 condition +=
298 generateSingleValueInExpr(
299 sqlQuery,
300 baseCube,
301 members,
302 firstUniqueParentLevel,
303 restrictMemberTypes);
304 }
305
306 if (condition.length() > 1) {
307 // condition is not empty
308 condition += ")";
309 sqlQuery.addWhere(condition);
310 }
311 return;
312 }
313
314 private static StarColumnPredicate getColumnPredicates(
315 RolapStar.Column column,
316 Collection<RolapMember> members)
317 {
318 switch (members.size()) {
319 case 0:
320 return new LiteralStarPredicate(column, false);
321 case 1:
322 return new MemberColumnPredicate(column, members.iterator().next());
323 default:
324 List<StarColumnPredicate> predicateList =
325 new ArrayList<StarColumnPredicate>();
326 for (RolapMember member : members) {
327 predicateList.add(new MemberColumnPredicate(column, member));
328 }
329 return new ListColumnPredicate(column, predicateList);
330 }
331 }
332
333 private static LinkedHashSet<RolapMember> getUniqueParentMembers(
334 Collection<RolapMember> members)
335 {
336 LinkedHashSet<RolapMember> set = new LinkedHashSet<RolapMember>();
337 for (RolapMember m : members) {
338 m = m.getParentMember();
339 if (m != null) {
340 set.add(m);
341 }
342 }
343 return set;
344 }
345
346 /**
347 * Adds to the where clause of a query expression matching a specified
348 * list of members
349 *
350 * @param sqlQuery query containing the where clause
351 * @param baseCube base cube if virtual
352 * @param members list of constraining members
353 * @param fromLevel lowest parent level that is unique
354 * @param restrictMemberTypes defines the behavior when calculated members are present
355 *
356 * @return a non-empty String if SQL is generated for the multi-level member list.
357 */
358 private static String constrainMultiLevelMembers(
359 SqlQuery sqlQuery,
360 RolapCube baseCube,
361 List<RolapMember> members,
362 RolapLevel fromLevel,
363 boolean restrictMemberTypes)
364 {
365 // Use LinkedHashMap so that keySet() is deterministic.
366 Map<RolapMember, List<RolapMember>> parentChildrenMap =
367 new LinkedHashMap<RolapMember, List<RolapMember>>();
368 String condition = "";
369
370 // First try to generate IN list for all members
371 if (sqlQuery.getDialect().supportsMultiValueInExpr()) {
372 condition +=
373 generateMultiValueInExpr(
374 sqlQuery,
375 baseCube,
376 members,
377 fromLevel,
378 restrictMemberTypes,
379 parentChildrenMap);
380
381 // The members list might contain NULL values in the member levels.
382 //
383 // e.g.
384 // [USA].[CA].[San Jose]
385 // [null].[null].[San Francisco]
386 // [null].[null].[Los Angeles]
387 // [null].[CA].[San Diego]
388 // [null].[CA].[Sacramento]
389 //
390 // Pick out such members to generate SQL later.
391 // These members are organized in a map that maps the parant levels
392 // containing NULL to all its children members in the list. e.g. the
393 // member list above becomes the following map, after SQL is generated
394 // for [USA].[CA].[San Jose] in the call above.
395 //
396 // [null].[null]->([San Francisco], [Los Angeles])
397 // [null]->([CA].[San Diego], [CA].[Sacramento])
398 //
399 if (parentChildrenMap.isEmpty()) {
400 return condition;
401 }
402 } else {
403 // Multi-value IN list not supported
404 // Classify members into List that share the same parent.
405 //
406 // Using the same example as above, the resulting map will be
407 // [USA].[CA]->[San Jose]
408 // [null].[null]->([San Francisco], [Los Angesles])
409 // [null].[CA]->([San Diego],[Sacramento])
410 //
411 // The idea is to be able to "compress" the original member list into
412 // groups that can use single value IN list for part of the comparison
413 // that does not involve NULLs.
414 //
415 for (RolapMember m : members) {
416 if (m.isCalculated()) {
417 if (restrictMemberTypes) {
418 throw Util.newInternal("addMemberConstraint: cannot " +
419 "restrict SQL to calculated member :" + m);
420 }
421 continue;
422 }
423 RolapMember p = m.getParentMember();
424 List<RolapMember> childrenList = parentChildrenMap.get(p);
425 if (childrenList == null) {
426 childrenList = new ArrayList<RolapMember>();
427 parentChildrenMap.put(p, childrenList);
428 }
429 childrenList.add(m);
430 }
431 }
432
433 // Now we try to generate predicates for the remaining
434 // parent-children group.
435
436 // Note that NULLs are not used to enforce uniqueness
437 // so we ignore the fromLevel here.
438 boolean firstParent = true;
439
440 if (condition.length() > 0) {
441 // Some members have already been translated into IN list.
442 firstParent = false;
443 }
444
445 RolapLevel memberLevel = members.get(0).getLevel();
446
447 // The children for each parent are turned into IN list so they
448 // should not contain null.
449 for (RolapMember p : parentChildrenMap.keySet()) {
450 if (!firstParent) {
451 condition += " or ";
452 }
453
454 condition += "(";
455
456 // First generate ANDs for all members in the parent lineage of this parent-children group
457 boolean firstLevel = true;
458 for (RolapMember gp = p; gp != null; gp = gp.getParentMember()) {
459 if (gp.isAll()) {
460 // Ignore All member
461 // Get the next parent
462 continue;
463 }
464
465 RolapLevel level = gp.getLevel();
466
467 // add the level to the FROM clause if this is the
468 // first parent-children group we're generating sql for
469 if (firstParent) {
470 RolapHierarchy hierarchy =
471 (RolapHierarchy) level.getHierarchy();
472
473 // this method can be called within the context of shared
474 // members, outside of the normal rolap star, therefore
475 // we need to check the level to see if it is a shared or
476 // cube level.
477
478 RolapStar.Column column = null;
479 if (level instanceof RolapCubeLevel) {
480 column =
481 ((RolapCubeLevel)level).
482 getBaseStarKeyColumn(baseCube);
483 }
484
485 if (column != null) {
486 RolapStar.Table targetTable = column.getTable();
487 hierarchy.addToFrom(sqlQuery, targetTable);
488 } else {
489 hierarchy.addToFrom(sqlQuery, level.getKeyExp());
490 }
491 }
492
493 if (!firstLevel) {
494 condition += " and ";
495 } else {
496 firstLevel = false;
497 }
498
499 condition += constrainLevel(
500 level,
501 sqlQuery,
502 baseCube,
503 getColumnValue(
504 gp.getKey(),
505 sqlQuery.getDialect(),
506 level.getDatatype()),
507 false);
508 if (gp.getLevel() == fromLevel) {
509 // SQL is completely generated for this parent
510 break;
511 }
512 }
513 firstParent = false;
514
515 // Next, generate children for this parent-children group
516 List<RolapMember> children = parentChildrenMap.get(p);
517
518 // If no children to be generated for this parent then we are done
519 if (!children.isEmpty()) {
520 Map<RolapMember, List<RolapMember>> tmpParentChildrenMap =
521 new HashMap<RolapMember, List<RolapMember>>();
522
523 condition += " and ";
524
525 RolapLevel childrenLevel =
526 (RolapLevel)(p.getLevel().getChildLevel());
527
528 if (sqlQuery.getDialect().supportsMultiValueInExpr() &&
529 childrenLevel != memberLevel) {
530 // Multi-level children and multi-value IN list supported
531 condition +=
532 generateMultiValueInExpr(
533 sqlQuery,
534 baseCube,
535 children,
536 childrenLevel,
537 restrictMemberTypes,
538 tmpParentChildrenMap);
539 assert (tmpParentChildrenMap.isEmpty());
540 } else {
541 // Can only be single level children
542 // If multi-value IN list not supported, children will be on
543 // the same level as members list. Only single value IN list
544 // needs to be generated for this case.
545 assert (childrenLevel == memberLevel);
546 condition +=
547 generateSingleValueInExpr(
548 sqlQuery,
549 baseCube,
550 children,
551 childrenLevel,
552 restrictMemberTypes);
553 }
554 }
555 // SQL is complete for this parent-children group.
556 condition += ")";
557 }
558
559 return condition;
560 }
561
562 /**
563 * @param members list of members
564 *
565 * @return true if the members comprise the cross product of all unique
566 * member keys referenced at each level
567 */
568 private static boolean membersAreCrossProduct(List<RolapMember> members)
569 {
570 int crossProdSize = getNumUniqueMemberKeys(members);
571 for (Collection<RolapMember> parents = getUniqueParentMembers(members);
572 !parents.isEmpty(); parents = getUniqueParentMembers(parents))
573 {
574 crossProdSize *= parents.size();
575 }
576 return (crossProdSize == members.size());
577 }
578
579 /**
580 * @param members list of members
581 *
582 * @return number of unique member keys in a list of members
583 */
584 private static int getNumUniqueMemberKeys(List<RolapMember> members)
585 {
586 final HashSet<Object> set = new HashSet<Object>();
587 for (RolapMember m : members) {
588 set.add(m.getKey());
589 }
590 return set.size();
591 }
592
593 /**
594 * @param key key corresponding to a member
595 * @param dialect sql dialect being used
596 * @param datatype data type of the member
597 *
598 * @return string value corresponding to the member
599 */
600 private static String getColumnValue(
601 Object key,
602 SqlQuery.Dialect dialect,
603 SqlQuery.Datatype datatype)
604 {
605 if (key != RolapUtil.sqlNullValue) {
606 return key.toString();
607 } else {
608 return RolapUtil.mdxNullLiteral;
609 }
610 }
611
612 /**
613 * Generates a sql expression constraining a level by some value
614 *
615 * @param level the level
616 * @param query the query that the sql expression will be added to
617 * @param baseCube base cube for virtual levels
618 * @param columnValue value constraining the level
619 * @param caseSensitive if true, need to handle case sensitivity of the
620 * member value
621 *
622 * @return generated string corresponding to the expression
623 */
624 public static String constrainLevel(
625 RolapLevel level,
626 SqlQuery query,
627 RolapCube baseCube,
628 String columnValue,
629 boolean caseSensitive)
630 {
631
632 // this method can be called within the context of shared members,
633 // outside of the normal rolap star, therefore we need to
634 // check the level to see if it is a shared or cube level.
635
636 RolapStar.Column column = null;
637 if (level instanceof RolapCubeLevel) {
638 column = ((RolapCubeLevel)level).getBaseStarKeyColumn(baseCube);
639 }
640
641 String columnString;
642 SqlQuery.Datatype datatype;
643 if (column != null) {
644 if (column.getNameColumn() == null) {
645 datatype = level.getDatatype();
646 } else {
647 column = column.getNameColumn();
648 // The schema doesn't specify the datatype of the name column, but
649 // we presume that it is a string.
650 datatype = SqlQuery.Datatype.String;
651 }
652 columnString = column.generateExprString(query);
653 } else {
654 MondrianDef.Expression exp = level.getNameExp();
655 if (exp == null) {
656 exp = level.getKeyExp();
657 datatype = level.getDatatype();
658 } else {
659 // The schema doesn't specify the datatype of the name column, but
660 // we presume that it is a string.
661 datatype = SqlQuery.Datatype.String;
662 }
663 columnString = exp.getExpression(query);
664 }
665
666 String constraint;
667
668 if (RolapUtil.mdxNullLiteral.equalsIgnoreCase(columnValue)) {
669 constraint = columnString + " is " + RolapUtil.sqlNullLiteral;
670 } else {
671 if (datatype.isNumeric()) {
672 // make sure it can be parsed
673 Double.valueOf(columnValue);
674 }
675 final StringBuilder buf = new StringBuilder();
676 query.getDialect().quote(buf, columnValue, datatype);
677 String value = buf.toString();
678 if (caseSensitive && datatype == SqlQuery.Datatype.String) {
679 // Some databases (like DB2) compare case-sensitive. We convert
680 // the value to upper-case in the DBMS (e.g. UPPER('Foo'))
681 // rather than in Java (e.g. 'FOO') in case the DBMS is running
682 // a different locale.
683 if (!MondrianProperties.instance().CaseSensitive.get()) {
684 columnString = query.getDialect().toUpper(columnString);
685 value = query.getDialect().toUpper(value);
686 }
687 }
688
689 constraint = columnString + " = " + value;
690 }
691
692 return constraint;
693 }
694
695 /**
696 * Generates a multi-value IN expression corresponding to a list of
697 * member expressions, and adds the expression to the WHERE clause
698 * of a query, provided the member values are all non-null
699 *
700 * @param sqlQuery query containing the where clause
701 * @param baseCube base cube if virtual
702 * @param members list of constraining members
703 * @param fromLevel lowest parent level that is unique
704 * @param restrictMemberTypes defines the behavior when calculated members are present
705 * @param parentWithNullToChildrenMap upon return this map contains members
706 * that have Null values in its (parent) levels
707 * @return a non-empty String if multi-value IN list was generated for some members.
708 */
709 private static String generateMultiValueInExpr(
710 SqlQuery sqlQuery,
711 RolapCube baseCube,
712 List<RolapMember> members,
713 RolapLevel fromLevel,
714 boolean restrictMemberTypes,
715 Map<RolapMember, List<RolapMember>> parentWithNullToChildrenMap)
716 {
717 final StringBuilder columnBuf = new StringBuilder();
718 final StringBuilder valueBuf = new StringBuilder();
719
720 columnBuf.append("(");
721
722 // generate the left-hand side of the IN expression
723 boolean isFirstLevelInMultiple = true;
724 for (RolapMember m = members.get(0); m != null; m = m.getParentMember()) {
725 if (m.isAll()) {
726 continue;
727 }
728 RolapLevel level = m.getLevel();
729 RolapHierarchy hierarchy = (RolapHierarchy) level.getHierarchy();
730
731 // this method can be called within the context of shared members,
732 // outside of the normal rolap star, therefore we need to
733 // check the level to see if it is a shared or cube level.
734
735 RolapStar.Column column = null;
736 if (level instanceof RolapCubeLevel) {
737 column = ((RolapCubeLevel)level).getBaseStarKeyColumn(baseCube);
738 }
739
740 String columnString = null;
741 if (column != null) {
742 RolapStar.Table targetTable = column.getTable();
743 hierarchy.addToFrom(sqlQuery, targetTable);
744
745 RolapStar.Column nameColumn = column.getNameColumn();
746 if (nameColumn == null) {
747 nameColumn = column;
748 }
749 columnString = nameColumn.generateExprString(sqlQuery);
750 } else {
751 hierarchy.addToFrom(sqlQuery, level.getKeyExp());
752
753 MondrianDef.Expression nameExp = level.getNameExp();
754 if (nameExp == null) {
755 nameExp = level.getKeyExp();
756 }
757 columnString = nameExp.getExpression(sqlQuery);
758 }
759
760 if (!isFirstLevelInMultiple) {
761 columnBuf.append(",");
762 } else {
763 isFirstLevelInMultiple = false;
764 }
765
766 columnBuf.append(columnString);
767
768 // Only needs to compare up to the first(lowest) unique level.
769 if (m.getLevel() == fromLevel) {
770 break;
771 }
772 }
773
774 columnBuf.append(")");
775
776 // generate the RHS of the IN predicate
777 valueBuf.append("(");
778 boolean isFirstMember = true;
779 String memberString;
780 for (RolapMember m : members) {
781 if (m.isCalculated()) {
782 if (restrictMemberTypes) {
783 throw Util.newInternal("addMemberConstraint: cannot " +
784 "restrict SQL to calculated member :" + m);
785 }
786 continue;
787 }
788
789 isFirstLevelInMultiple = true;
790 memberString = "(";
791
792 boolean containsNull = false;
793 for (RolapMember p = m; p != null; p = p.getParentMember()) {
794
795 if (p.isAll()) {
796 // Ignore the ALL level.
797 // Generate SQL condition for the next level
798 continue;
799 }
800 RolapLevel level = p.getLevel();
801
802 String value = getColumnValue(
803 p.getKey(),
804 sqlQuery.getDialect(),
805 level.getDatatype());
806
807 // If parent at a level is NULL, record this parent and all
808 // its children(if there's any)
809 if (RolapUtil.mdxNullLiteral.equalsIgnoreCase(value)) {
810 // Add to the nullParent map
811 List<RolapMember> childrenList =
812 parentWithNullToChildrenMap.get(p);
813 if (childrenList == null) {
814 childrenList = new ArrayList<RolapMember>();
815 parentWithNullToChildrenMap.put(p, childrenList);
816 }
817
818 // If p has children
819 if (m != p) {
820 childrenList.add(m);
821 }
822
823 // Skip generating condition for this parent
824 containsNull = true;
825 break;
826 }
827
828 if (isFirstLevelInMultiple) {
829 isFirstLevelInMultiple = false;
830 } else {
831 memberString += ",";
832 }
833
834 final StringBuilder buf = new StringBuilder();
835 sqlQuery.getDialect().quote(buf, value, level.getDatatype());
836 memberString += buf.toString();
837
838 // Only needs to compare up to the first(lowest) unique level.
839 if (p.getLevel() == fromLevel) {
840 break;
841 }
842 }
843
844 // now check if sql string is sucessfully generated for this member
845 // If parent levels do not contain NULL then SQL must have been generated
846 //successfully.
847 if (!containsNull) {
848 memberString += ")";
849 if (!isFirstMember) {
850 valueBuf.append(",");
851 }
852 valueBuf.append(memberString);
853 isFirstMember = false;
854 }
855 }
856
857 String condition = "";
858 if (!isFirstMember) {
859 // SQLs are generated for some members.
860 valueBuf.append(")");
861 condition += columnBuf.toString() + " in " + valueBuf.toString();
862 }
863
864 return condition;
865 }
866
867 /**
868 * Generates a multi-value IN expression corresponding to a list of
869 * member expressions, and adds the expression to the WHERE clause
870 * of a query, provided the member values are all non-null
871 *
872 * @param sqlQuery query containing the where clause
873 * @param baseCube base cube if virtual
874 * @param members list of constraining members
875 * @param fromLevel lowest parent level that is unique
876 * @param restrictMemberTypes defines the behavior when calculated members are present
877 * @return a non-empty String if IN list was generated for the members.
878 */
879 private static String generateSingleValueInExpr(
880 SqlQuery sqlQuery,
881 RolapCube baseCube,
882 List<RolapMember> members,
883 RolapLevel fromLevel,
884 boolean restrictMemberTypes)
885 {
886 int maxConstraints =
887 MondrianProperties.instance().MaxConstraints.get();
888 SqlQuery.Dialect dialect = sqlQuery.getDialect();
889
890 String condition = "";
891 boolean firstLevel = true;
892 for (Collection<RolapMember> c = members;
893 !c.isEmpty();
894 c = getUniqueParentMembers(c))
895 {
896 RolapMember m = c.iterator().next();
897 if (m.isAll()) {
898 continue;
899 }
900 if (m.isCalculated()) {
901 if (restrictMemberTypes) {
902 throw Util.newInternal("addMemberConstraint: cannot " +
903 "restrict SQL to calculated member :" + m);
904 }
905 continue;
906 }
907 RolapLevel level = m.getLevel();
908 RolapHierarchy hierarchy = level.getHierarchy();
909
910 // this method can be called within the context of shared members,
911 // outside of the normal rolap star, therefore we need to
912 // check the level to see if it is a shared or cube level.
913
914 RolapStar.Column column = null;
915 if (level instanceof RolapCubeLevel) {
916 column = ((RolapCubeLevel)level).getBaseStarKeyColumn(baseCube);
917 }
918
919 StarColumnPredicate cc = getColumnPredicates(column, c);
920 String q = null;
921 if (column != null) {
922 RolapStar.Table targetTable = column.getTable();
923 hierarchy.addToFrom(sqlQuery, targetTable);
924 q = column.generateExprString(sqlQuery);
925 } else {
926 hierarchy.addToFrom(sqlQuery, level.getKeyExp());
927 q = level.getKeyExp().getExpression(sqlQuery);
928 }
929
930 if (!dialect.supportsUnlimitedValueList() &&
931 cc instanceof ListColumnPredicate &&
932 ((ListColumnPredicate) cc).getPredicates().size() >
933 maxConstraints)
934 {
935 // Simply get them all, do not create where-clause.
936 // Below are two alternative approaches (and code). They
937 // both have problems.
938 } else {
939 final String where =
940 RolapStar.Column.createInExpr(
941 q, cc, level.getDatatype(), sqlQuery);
942 if (!where.equals("true")) {
943 if (!firstLevel) {
944 condition += " and ";
945 } else {
946 firstLevel = false;
947 }
948 condition += where;
949 }
950 }
951
952 if (m.getLevel().isUnique() || m.getLevel() == fromLevel) {
953 break; // no further qualification needed
954 }
955 }
956 return condition;
957 }
958 }
959
960 // End SqlConstraintUtils.java