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