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