001 /*
002 // $Id: //open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#93 $
003 // This software is subject to the terms of the Common Public License
004 // Agreement, available at the following URL:
005 // http://www.opensource.org/licenses/cpl.html.
006 // Copyright (C) 2001-2002 Kana Software, Inc.
007 // Copyright (C) 2001-2008 Julian Hyde and others
008 // All Rights Reserved.
009 // You must accept the terms of that agreement to use this software.
010 //
011 // jhyde, 21 December, 2001
012 */
013
014 package mondrian.rolap;
015
016 import mondrian.olap.*;
017 import mondrian.resource.MondrianResource;
018 import mondrian.rolap.sql.*;
019 import mondrian.rolap.aggmatcher.AggStar;
020 import mondrian.rolap.agg.AggregationManager;
021 import mondrian.rolap.agg.CellRequest;
022
023 import javax.sql.DataSource;
024 import java.sql.*;
025 import java.util.*;
026
027 /**
028 * A <code>SqlMemberSource</code> reads members from a SQL database.
029 *
030 * <p>It's a good idea to put a {@link CacheMemberReader} on top of this.
031 *
032 * @author jhyde
033 * @since 21 December, 2001
034 * @version $Id: //open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#93 $
035 */
036 class SqlMemberSource
037 implements MemberReader, SqlTupleReader.MemberBuilder
038 {
039 private final SqlConstraintFactory sqlConstraintFactory =
040 SqlConstraintFactory.instance();
041 private final RolapHierarchy hierarchy;
042 private final DataSource dataSource;
043 private MemberCache cache;
044 private int lastOrdinal = 0;
045 private boolean assignOrderKeys;
046
047 SqlMemberSource(RolapHierarchy hierarchy) {
048 this.hierarchy = hierarchy;
049 this.dataSource =
050 hierarchy.getRolapSchema().getInternalConnection().getDataSource();
051 assignOrderKeys =
052 MondrianProperties.instance().CompareSiblingsByOrderKey.get();
053 }
054
055 // implement MemberSource
056 public RolapHierarchy getHierarchy() {
057 return hierarchy;
058 }
059
060 // implement MemberSource
061 public boolean setCache(MemberCache cache) {
062 this.cache = cache;
063 return true; // yes, we support cache writeback
064 }
065
066 // implement MemberSource
067 public int getMemberCount() {
068 RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
069 int count = 0;
070 for (RolapLevel level : levels) {
071 count += getLevelMemberCount(level);
072 }
073 return count;
074 }
075
076 public RolapMember substitute(RolapMember member) {
077 return member;
078 }
079
080 public RolapMember desubstitute(RolapMember member) {
081 return member;
082 }
083
084 public RolapMember lookupMember(
085 List<Id.Segment> uniqueNameParts,
086 boolean failIfNotFound)
087 {
088 throw new UnsupportedOperationException();
089 }
090
091 public int getLevelMemberCount(RolapLevel level) {
092 if (level.isAll()) {
093 return 1;
094 }
095 return getMemberCount(level, dataSource);
096 }
097
098 private int getMemberCount(RolapLevel level, DataSource dataSource) {
099 boolean[] mustCount = new boolean[1];
100 String sql = makeLevelMemberCountSql(level, dataSource, mustCount);
101 final SqlStatement stmt =
102 RolapUtil.executeQuery(
103 dataSource, sql, "SqlMemberSource.getLevelMemberCount",
104 "while counting members of level '" + level);
105 try {
106 ResultSet resultSet = stmt.getResultSet();
107 int count;
108 if (! mustCount[0]) {
109 Util.assertTrue(resultSet.next());
110 ++stmt.rowCount;
111 count = resultSet.getInt(1);
112 } else {
113 // count distinct "manually"
114 ResultSetMetaData rmd = resultSet.getMetaData();
115 int nColumns = rmd.getColumnCount();
116 String[] colStrings = new String[nColumns];
117 count = 0;
118 while (resultSet.next()) {
119 ++stmt.rowCount;
120 boolean isEqual = true;
121 for (int i = 0; i < nColumns; i++) {
122 String colStr = resultSet.getString(i + 1);
123 if (!colStr.equals(colStrings[i])) {
124 isEqual = false;
125 }
126 colStrings[i] = colStr;
127 }
128 if (!isEqual) {
129 count++;
130 }
131 }
132 }
133 return count;
134 } catch (SQLException e) {
135 throw stmt.handle(e);
136 } finally {
137 stmt.close();
138 }
139 }
140
141 /**
142 * Generates the SQL statement to count the members in
143 * <code>level</code>. For example, <blockquote>
144 *
145 * <pre>SELECT count(*) FROM (
146 * SELECT DISTINCT "country", "state_province"
147 * FROM "customer") AS "init"</pre>
148 *
149 * </blockquote> counts the non-leaf "state_province" level. MySQL
150 * doesn't allow SELECT-in-FROM, so we use the syntax<blockquote>
151 *
152 * <pre>SELECT count(DISTINCT "country", "state_province")
153 * FROM "customer"</pre>
154 *
155 * </blockquote>. The leaf level requires a different query:<blockquote>
156 *
157 * <pre>SELECT count(*) FROM "customer"</pre>
158 *
159 * </blockquote> counts the leaf "name" level of the "customer" hierarchy.
160 */
161 private String makeLevelMemberCountSql(
162 RolapLevel level,
163 DataSource dataSource,
164 boolean[] mustCount)
165 {
166 mustCount[0] = false;
167 SqlQuery sqlQuery =
168 SqlQuery.newQuery(
169 dataSource,
170 "while generating query to count members in level " + level);
171 int levelDepth = level.getDepth();
172 RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
173 if (levelDepth == levels.length) {
174 // "select count(*) from schema.customer"
175 sqlQuery.addSelect("count(*)");
176 hierarchy.addToFrom(sqlQuery, level.getKeyExp());
177 return sqlQuery.toString();
178 }
179 if (!sqlQuery.getDialect().allowsFromQuery()) {
180 String columnList = "";
181 int columnCount = 0;
182 for (int i = levelDepth; i >= 0; i--) {
183 RolapLevel level2 = levels[i];
184 if (level2.isAll()) {
185 continue;
186 }
187 if (columnCount > 0) {
188 if (sqlQuery.getDialect().allowsCompoundCountDistinct()) {
189 columnList += ", ";
190 } else if (true) {
191 // for databases where both SELECT-in-FROM and
192 // COUNT DISTINCT do not work, we do not
193 // generate any count and do the count
194 // distinct "manually".
195 mustCount[0] = true;
196 } else if (sqlQuery.getDialect().isSybase()) {
197 // "select count(distinct convert(varchar, c1) +
198 // convert(varchar, c2)) from table"
199 if (columnCount == 1) {
200 // Conversion to varchar is expensive, so we only
201 // do it when we know we are dealing with a
202 // compound key.
203 columnList = "convert(varchar, " + columnList + ")";
204 }
205 columnList += " + ";
206 } else {
207 // Apparently this database allows neither
208 // SELECT-in-FROM nor compound COUNT DISTINCT. I don't
209 // know any database where this happens. If you receive
210 // this error, try a workaround similar to the Sybase
211 // workaround above.
212 throw Util.newInternal(
213 "Cannot generate query to count members of level '" +
214 level.getUniqueName() +
215 "': database supports neither SELECT-in-FROM nor compound COUNT DISTINCT");
216 }
217 }
218 hierarchy.addToFrom(sqlQuery, level2.getKeyExp());
219
220 String keyExp = level2.getKeyExp().getExpression(sqlQuery);
221 if (columnCount > 0 &&
222 !sqlQuery.getDialect().allowsCompoundCountDistinct() &&
223 sqlQuery.getDialect().isSybase()) {
224
225 keyExp = "convert(varchar, " + columnList + ")";
226 }
227 columnList += keyExp;
228
229 if (level2.isUnique()) {
230 break; // no further qualification needed
231 }
232 ++columnCount;
233 }
234 if (mustCount[0]) {
235 sqlQuery.addSelect(columnList);
236 sqlQuery.addOrderBy(columnList, true, false, true);
237 } else {
238 sqlQuery.addSelect("count(DISTINCT " + columnList + ")");
239 }
240 return sqlQuery.toString();
241
242 } else {
243 sqlQuery.setDistinct(true);
244 for (int i = levelDepth; i >= 0; i--) {
245 RolapLevel level2 = levels[i];
246 if (level2.isAll()) {
247 continue;
248 }
249 hierarchy.addToFrom(sqlQuery, level2.getKeyExp());
250 sqlQuery.addSelect(level2.getKeyExp().getExpression(sqlQuery));
251 if (level2.isUnique()) {
252 break; // no further qualification needed
253 }
254 }
255 SqlQuery outerQuery =
256 SqlQuery.newQuery(
257 dataSource,
258 "while generating query to count members in level " + level);
259 outerQuery.addSelect("count(*)");
260 // Note: the "init" is for Postgres, which requires
261 // FROM-queries to have an alias
262 boolean failIfExists = true;
263 outerQuery.addFrom(sqlQuery, "init", failIfExists);
264 return outerQuery.toString();
265 }
266 }
267
268
269 public List<RolapMember> getMembers() {
270 return getMembers(dataSource);
271 }
272
273 private List<RolapMember> getMembers(DataSource dataSource) {
274 String sql = makeKeysSql(dataSource);
275 RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
276 SqlStatement stmt =
277 RolapUtil.executeQuery(
278 dataSource, sql, "SqlMemberSource.getMembers",
279 "while building member cache");
280 try {
281 List<RolapMember> list = new ArrayList<RolapMember>();
282 Map<MemberKey, RolapMember> map =
283 new HashMap<MemberKey, RolapMember>();
284 RolapMember root = null;
285 if (hierarchy.hasAll()) {
286 root = hierarchy.getAllMember();
287 list.add(root);
288 }
289
290 int limit = MondrianProperties.instance().ResultLimit.get();
291 ResultSet resultSet = stmt.getResultSet();
292 while (resultSet.next()) {
293 ++stmt.rowCount;
294 if (limit > 0 && limit < stmt.rowCount) {
295 // result limit exceeded, throw an exception
296 throw stmt.handle(
297 MondrianResource.instance().MemberFetchLimitExceeded.
298 ex(limit));
299 }
300
301 int column = 0;
302 RolapMember member = root;
303 for (RolapLevel level : levels) {
304 if (level.isAll()) {
305 continue;
306 }
307 Object value = resultSet.getObject(column + 1);
308 if (value == null) {
309 value = RolapUtil.sqlNullValue;
310 }
311 RolapMember parent = member;
312 MemberKey key = new MemberKey(parent, value);
313 member = map.get(key);
314 if (member == null) {
315 member = new RolapMember(parent, level, value);
316 member.setOrdinal(lastOrdinal++);
317 /*
318 RME is this right
319 if (level.getOrdinalExp() != level.getKeyExp()) {
320 member.setOrdinal(lastOrdinal++);
321 }
322 */
323 if (value == RolapUtil.sqlNullValue) {
324 addAsOldestSibling(list, member);
325 } else {
326 list.add(member);
327 }
328 map.put(key, member);
329 }
330 column++;
331
332 // REVIEW jvs 20-Feb-2007: What about caption?
333
334 if (!level.getOrdinalExp().equals(level.getKeyExp())) {
335 if (assignOrderKeys) {
336 Object orderKey = resultSet.getObject(column + 1);
337 setOrderKey(member, orderKey);
338 }
339 column++;
340 }
341
342 Property[] properties = level.getProperties();
343 for (Property property : properties) {
344 member.setProperty(property.getName(),
345 resultSet.getObject(column + 1));
346 column++;
347 }
348 }
349 }
350
351 return list;
352 } catch (SQLException e) {
353 throw stmt.handle(e);
354 } finally {
355 stmt.close();
356 }
357 }
358
359 private void setOrderKey(RolapMember member, Object orderKey) {
360 if ((orderKey != null) && !(orderKey instanceof Comparable)) {
361 orderKey = orderKey.toString();
362 }
363 member.setOrderKey((Comparable) orderKey);
364 }
365
366 /**
367 * Adds <code>member</code> just before the first element in
368 * <code>list</code> which has the same parent.
369 */
370 private void addAsOldestSibling(
371 List<RolapMember> list,
372 RolapMember member)
373 {
374 int i = list.size();
375 while (--i >= 0) {
376 RolapMember sibling = list.get(i);
377 if (sibling.getParentMember() != member.getParentMember()) {
378 break;
379 }
380 }
381 list.add(i + 1, member);
382 }
383
384 private String makeKeysSql(DataSource dataSource) {
385 SqlQuery sqlQuery =
386 SqlQuery.newQuery(
387 dataSource,
388 "while generating query to retrieve members of " + hierarchy);
389 RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
390 for (RolapLevel level : levels) {
391 if (level.isAll()) {
392 continue;
393 }
394 MondrianDef.Expression exp = level.getKeyExp();
395 hierarchy.addToFrom(sqlQuery, exp);
396 String expString = exp.getExpression(sqlQuery);
397 sqlQuery.addSelect(expString);
398 sqlQuery.addGroupBy(expString);
399 exp = level.getOrdinalExp();
400 hierarchy.addToFrom(sqlQuery, exp);
401 expString = exp.getExpression(sqlQuery);
402 sqlQuery.addOrderBy(expString, true, false, true);
403 sqlQuery.addGroupBy(expString);
404 if (!exp.equals(level.getKeyExp())) {
405 sqlQuery.addSelect(expString);
406 }
407
408 RolapProperty[] properties = level.getProperties();
409 for (RolapProperty property : properties) {
410 exp = property.getExp();
411 hierarchy.addToFrom(sqlQuery, exp);
412 expString = exp.getExpression(sqlQuery);
413 sqlQuery.addSelect(expString);
414 sqlQuery.addGroupBy(expString);
415 }
416 }
417 return sqlQuery.toString();
418 }
419
420 // implement MemberReader
421 public List<RolapMember> getMembersInLevel(
422 RolapLevel level,
423 int startOrdinal,
424 int endOrdinal) {
425 TupleConstraint constraint =
426 sqlConstraintFactory.getLevelMembersConstraint(null);
427 return getMembersInLevel(level, startOrdinal, endOrdinal, constraint);
428 }
429
430 public List<RolapMember> getMembersInLevel(
431 RolapLevel level,
432 int startOrdinal,
433 int endOrdinal,
434 TupleConstraint constraint) {
435 if (level.isAll()) {
436 final List<RolapMember> list = new ArrayList<RolapMember>();
437 list.add(hierarchy.getAllMember());
438 //return Collections.singletonList(hierarchy.getAllMember());
439 return list;
440 }
441 return getMembersInLevel(level, constraint);
442 }
443
444 private List<RolapMember> getMembersInLevel(
445 RolapLevel level,
446 TupleConstraint constraint)
447 {
448 final TupleReader tupleReader =
449 level.getDimension().isHighCardinality()
450 ? new HighCardSqlTupleReader(constraint)
451 : new SqlTupleReader(constraint);
452 tupleReader.addLevelMembers(level, this, null);
453 final List<RolapMember[]> tupleList =
454 tupleReader.readTuples(dataSource, null, null);
455
456 return new AbstractList<RolapMember>() {
457 public RolapMember get(final int index) {
458 return tupleList.get(index)[0];
459 }
460
461 public int size() {
462 return tupleList.size();
463 }
464
465 public mondrian.rolap.RolapMember[] toArray() {
466 final List<Member> l = new ArrayList<Member>();
467 for (final RolapMember[] tuple : tupleList) {
468 l.add(tuple[0]);
469 }
470 return l.toArray(new RolapMember[l.size()]);
471 }
472
473 public <T> T[] toArray(T[] pattern) {
474 return (T[]) toArray();
475 }
476
477 public Iterator<RolapMember> iterator() {
478 final Iterator<RolapMember[]> it = tupleList.iterator();
479 return new Iterator<RolapMember>() {
480 public boolean hasNext() {
481 return it.hasNext();
482 }
483 public RolapMember next() {
484 return it.next()[0];
485 }
486 public void remove() {
487 it.remove();
488 }
489 };
490 }
491 };
492 }
493
494 public MemberCache getMemberCache() {
495 return cache;
496 }
497
498 public Object getMemberCacheLock() {
499 return cache;
500 }
501
502 // implement MemberSource
503 public List<RolapMember> getRootMembers() {
504 return getMembersInLevel(
505 (RolapLevel) hierarchy.getLevels()[0],
506 0,
507 Integer.MAX_VALUE);
508 }
509
510 /**
511 * Generates the SQL statement to access the children of
512 * <code>member</code>. For example, <blockquote>
513 *
514 * <pre>SELECT "city"
515 * FROM "customer"
516 * WHERE "country" = 'USA'
517 * AND "state_province" = 'BC'
518 * GROUP BY "city"</pre>
519 * </blockquote> retrieves the children of the member
520 * <code>[Canada].[BC]</code>.
521 * <p>Note that this method is never called in the context of
522 * virtual cubes, it is only called on regular cubes.
523 *
524 * <p>See also {@link SqlTupleReader#makeLevelMembersSql}.
525 */
526 String makeChildMemberSql(
527 RolapMember member,
528 DataSource dataSource,
529 MemberChildrenConstraint constraint)
530 {
531 SqlQuery sqlQuery =
532 SqlQuery.newQuery(
533 dataSource,
534 "while generating query to retrieve children of member "
535 + member);
536
537 // If this is a non-empty constraint, it is more efficient to join to
538 // an aggregate table than to the fact table. See whether a suitable
539 // aggregate table exists.
540 AggStar aggStar = chooseAggStar(constraint, member);
541
542 // Create the condition, which is either the parent member or
543 // the full context (non empty).
544 constraint.addMemberConstraint(sqlQuery, null, aggStar, member);
545
546 RolapLevel level = (RolapLevel) member.getLevel().getChildLevel();
547 hierarchy.addToFrom(sqlQuery, level.getKeyExp());
548 String q = level.getKeyExp().getExpression(sqlQuery);
549 sqlQuery.addSelect(q);
550 sqlQuery.addGroupBy(q);
551
552 // in non empty mode the level table must be joined to the fact table
553 constraint.addLevelConstraint(sqlQuery, null, aggStar, level);
554
555 if (level.hasCaptionColumn()) {
556 MondrianDef.Expression captionExp = level.getCaptionExp();
557 hierarchy.addToFrom(sqlQuery, captionExp);
558 String captionSql = captionExp.getExpression(sqlQuery);
559 sqlQuery.addSelect(captionSql);
560 sqlQuery.addGroupBy(captionSql);
561 }
562
563 hierarchy.addToFrom(sqlQuery, level.getOrdinalExp());
564 String orderBy = level.getOrdinalExp().getExpression(sqlQuery);
565 sqlQuery.addOrderBy(orderBy, true, false, true);
566 if (!orderBy.equals(q)) {
567 sqlQuery.addGroupBy(orderBy);
568 sqlQuery.addSelect(orderBy);
569 }
570
571 RolapProperty[] properties = level.getProperties();
572 for (RolapProperty property : properties) {
573 final MondrianDef.Expression exp = property.getExp();
574 hierarchy.addToFrom(sqlQuery, exp);
575 final String s = exp.getExpression(sqlQuery);
576 sqlQuery.addSelect(s);
577 sqlQuery.addGroupBy(s);
578 }
579 return sqlQuery.toString();
580 }
581
582 private static AggStar chooseAggStar(
583 MemberChildrenConstraint constraint,
584 RolapMember member)
585 {
586 if (!(constraint instanceof SqlContextConstraint)) {
587 return null;
588 }
589
590 SqlContextConstraint contextConstraint =
591 (SqlContextConstraint) constraint;
592 Evaluator evaluator = contextConstraint.getEvaluator();
593 RolapCube cube = (RolapCube) evaluator.getCube();
594 RolapStar star = cube.getStar();
595 final int starColumnCount = star.getColumnCount();
596 BitKey measureBitKey = BitKey.Factory.makeBitKey(starColumnCount);
597 BitKey levelBitKey = BitKey.Factory.makeBitKey(starColumnCount);
598
599 // Convert global ordinal to cube based ordinal (the 0th dimension
600 // is always [Measures])
601 final Member[] members = evaluator.getMembers();
602 Member measure = members[0];
603 int ordinal = measure.getOrdinal();
604
605 // childLevel will always end up being a RolapCubeLevel, but the API
606 // calls into this method can be both shared RolapMembers and
607 // RolapCubeMembers so this cast is necessary for now. Also note that
608 // this method will never be called in the context of a virtual cube
609 // so baseCube isn't necessary for retrieving the correct column
610
611 // get the level using the current depth
612 RolapCubeLevel childLevel =
613 (RolapCubeLevel) member.getLevel().getChildLevel();
614
615 RolapStar.Column column = childLevel.getStarKeyColumn();
616
617 // set a bit for each level which is constrained in the context
618 final CellRequest request =
619 RolapAggregationManager.makeRequest(members);
620 if (request == null) {
621 // One or more calculated members. Cannot use agg table.
622 return null;
623 }
624 // TODO: RME why is this using the array of constrained columns
625 // from the CellRequest rather than just the constrained columns
626 // BitKey (method getConstrainedColumnsBitKey)?
627 RolapStar.Column[] columns = request.getConstrainedColumns();
628 for (RolapStar.Column column1 : columns) {
629 levelBitKey.set(column1.getBitPosition());
630 }
631
632 // set the masks
633 levelBitKey.set(column.getBitPosition());
634 measureBitKey.set(ordinal);
635
636 // find the aggstar using the masks
637 return AggregationManager.instance().findAgg(
638 star, levelBitKey, measureBitKey, new boolean[]{ false });
639 }
640
641 public void getMemberChildren(List<RolapMember> parentMembers, List<RolapMember> children) {
642 MemberChildrenConstraint constraint = sqlConstraintFactory.getMemberChildrenConstraint(null);
643 getMemberChildren(parentMembers, children, constraint);
644 }
645
646 public void getMemberChildren(
647 List<RolapMember> parentMembers,
648 List<RolapMember> children,
649 MemberChildrenConstraint mcc)
650 {
651 // try to fetch all children at once
652 RolapLevel childLevel =
653 getCommonChildLevelForDescendants(parentMembers);
654 if (childLevel != null) {
655 TupleConstraint lmc =
656 sqlConstraintFactory.getDescendantsConstraint(
657 parentMembers, mcc);
658 List<RolapMember> list =
659 getMembersInLevel(childLevel, 0, Integer.MAX_VALUE, lmc);
660 children.addAll(list);
661 return;
662 }
663
664 // fetch them one by one
665 for (RolapMember parentMember : parentMembers) {
666 getMemberChildren(parentMember, children, mcc);
667 }
668 }
669
670 public void getMemberChildren(
671 RolapMember parentMember,
672 List<RolapMember> children)
673 {
674 MemberChildrenConstraint constraint =
675 sqlConstraintFactory.getMemberChildrenConstraint(null);
676 getMemberChildren(parentMember, children, constraint);
677 }
678
679 public void getMemberChildren(
680 RolapMember parentMember,
681 List<RolapMember> children,
682 MemberChildrenConstraint constraint)
683 {
684 // allow parent child calculated members through
685 // this fixes the non closure parent child hierarchy bug
686 if (!parentMember.isAll() &&
687 parentMember.isCalculated() &&
688 !parentMember.getLevel().isParentChild()) {
689 return;
690 }
691 getMemberChildren2(parentMember, children, constraint);
692 }
693
694 /**
695 * If all parents belong to the same level and no parent/child is involved,
696 * returns that level; this indicates that all member children can be
697 * fetched at once. Otherwise returns null.
698 */
699 private RolapLevel getCommonChildLevelForDescendants(
700 List<RolapMember> parents)
701 {
702 // at least two members required
703 if (parents.size() < 2) {
704 return null;
705 }
706 RolapLevel parentLevel = null;
707 RolapLevel childLevel = null;
708 for (RolapMember member : parents) {
709 // we can not fetch children of calc members
710 if (member.isCalculated()) {
711 return null;
712 }
713 // first round?
714 if (parentLevel == null) {
715 parentLevel = member.getLevel();
716 // check for parent/child
717 if (parentLevel.isParentChild()) {
718 return null;
719 }
720 childLevel = (RolapLevel) parentLevel.getChildLevel();
721 if (childLevel == null) {
722 return null;
723 }
724 if (childLevel.isParentChild()) {
725 return null;
726 }
727 } else if (parentLevel != member.getLevel()) {
728 return null;
729 }
730 }
731 return childLevel;
732 }
733
734 private void getMemberChildren2(
735 RolapMember parentMember,
736 List<RolapMember> children,
737 MemberChildrenConstraint constraint)
738 {
739 String sql;
740 boolean parentChild;
741 final RolapLevel parentLevel = parentMember.getLevel();
742 RolapLevel childLevel;
743 if (parentLevel.isParentChild()) {
744 sql = makeChildMemberSqlPC(parentMember);
745 parentChild = true;
746 childLevel = parentLevel;
747 } else {
748 childLevel = (RolapLevel) parentLevel.getChildLevel();
749 if (childLevel == null) {
750 // member is at last level, so can have no children
751 return;
752 }
753 if (childLevel.isParentChild()) {
754 sql = makeChildMemberSql_PCRoot(parentMember);
755 parentChild = true;
756 } else {
757 sql = makeChildMemberSql(parentMember, dataSource, constraint);
758 parentChild = false;
759 }
760 }
761 SqlStatement stmt =
762 RolapUtil.executeQuery(
763 dataSource, sql, "SqlMemberSource.getMemberChildren",
764 "while building member cache");
765 try {
766
767 int limit = MondrianProperties.instance().ResultLimit.get();
768 boolean checkCacheStatus = true;
769
770 ResultSet resultSet = stmt.getResultSet();
771 while (resultSet.next()) {
772 ++stmt.rowCount;
773 if (limit > 0 && limit < stmt.rowCount) {
774 // result limit exceeded, throw an exception
775 throw MondrianResource.instance().MemberFetchLimitExceeded.
776 ex(limit);
777 }
778
779 Object value = resultSet.getObject(1);
780 if (value == null) {
781 value = RolapUtil.sqlNullValue;
782 }
783 Object captionValue;
784 int columnOffset;
785 if (childLevel.hasCaptionColumn()) {
786 // The columnOffset needs to take into account
787 // the caption column if one exists
788 columnOffset = 2;
789 captionValue = resultSet.getObject(columnOffset);
790 } else {
791 columnOffset = 1;
792 captionValue = null;
793 }
794 Object key = cache.makeKey(parentMember, value);
795 RolapMember member = cache.getMember(key, checkCacheStatus);
796 checkCacheStatus = false; /* Only check the first time */
797 if (member == null) {
798 member = makeMember(
799 parentMember, childLevel, value, captionValue,
800 parentChild, resultSet, key, columnOffset);
801 }
802 if (value == RolapUtil.sqlNullValue) {
803 children.toArray();
804 addAsOldestSibling(children, member);
805 } else {
806 children.add(member);
807 }
808 }
809 } catch (SQLException e) {
810 throw stmt.handle(e);
811 } finally {
812 stmt.close();
813 }
814 }
815
816 public RolapMember makeMember(
817 RolapMember parentMember,
818 RolapLevel childLevel,
819 Object value,
820 Object captionValue,
821 boolean parentChild,
822 ResultSet resultSet,
823 Object key,
824 int columnOffset)
825 throws SQLException {
826
827 RolapMember member = new RolapMember(parentMember, childLevel, value);
828 if (!childLevel.getOrdinalExp().equals(childLevel.getKeyExp())) {
829 member.setOrdinal(lastOrdinal++);
830 }
831 if (captionValue != null) {
832 member.setCaption(captionValue.toString());
833 }
834 if (parentChild) {
835 // Create a 'public' and a 'data' member. The public member is
836 // calculated, and its value is the aggregation of the data member
837 // and all of the children. The children and the data member belong
838 // to the parent member; the data member does not have any
839 // children.
840 final RolapParentChildMember parentChildMember =
841 childLevel.hasClosedPeer() ?
842 new RolapParentChildMember(
843 parentMember, childLevel, value, member)
844 : new RolapParentChildMemberNoClosure(
845 parentMember, childLevel, value, member);
846
847 member = parentChildMember;
848 }
849 Property[] properties = childLevel.getProperties();
850 if (!childLevel.getOrdinalExp().equals(childLevel.getKeyExp())) {
851 if (assignOrderKeys) {
852 Object orderKey = resultSet.getObject(columnOffset + 1);
853 setOrderKey(member, orderKey);
854 }
855 ++columnOffset;
856 }
857 for (int j = 0; j < properties.length; j++) {
858 Property property = properties[j];
859 member.setProperty(
860 property.getName(),
861 resultSet.getObject(columnOffset + j + 1));
862 }
863 cache.putMember(key, member);
864 return member;
865 }
866
867 /**
868 * Generates the SQL to find all root members of a parent-child hierarchy.
869 * For example, <blockquote>
870 *
871 * <pre>SELECT "employee_id"
872 * FROM "employee"
873 * WHERE "supervisor_id" IS NULL
874 * GROUP BY "employee_id"</pre>
875 * </blockquote> retrieves the root members of the <code>[Employee]</code>
876 * hierarchy.
877 *
878 * <p>Currently, parent-child hierarchies may have only one level (plus the
879 * 'All' level).
880 */
881 private String makeChildMemberSql_PCRoot(RolapMember member) {
882 SqlQuery sqlQuery =
883 SqlQuery.newQuery(
884 dataSource,
885 "while generating query to retrieve children of parent/child " +
886 "hierarchy member " + member);
887 Util.assertTrue(
888 member.isAll(),
889 "In the current implementation, parent/child hierarchies must " +
890 "have only one level (plus the 'All' level).");
891
892 RolapLevel level = (RolapLevel) member.getLevel().getChildLevel();
893
894 Util.assertTrue(!level.isAll(), "all level cannot be parent-child");
895 Util.assertTrue(level.isUnique(), "parent-child level '"
896 + level + "' must be unique");
897
898 hierarchy.addToFrom(sqlQuery, level.getParentExp());
899 String parentId = level.getParentExp().getExpression(sqlQuery);
900 StringBuilder condition = new StringBuilder(64);
901 condition.append(parentId);
902 if (level.getNullParentValue() == null ||
903 level.getNullParentValue().equalsIgnoreCase("NULL")) {
904 condition.append(" IS NULL");
905 } else {
906 // Quote the value if it doesn't seem to be a number.
907 try {
908 Util.discard(Double.parseDouble(level.getNullParentValue()));
909 condition.append(" = ");
910 condition.append(level.getNullParentValue());
911 } catch (NumberFormatException e) {
912 condition.append(" = ");
913 Util.singleQuoteString(level.getNullParentValue(), condition);
914 }
915 }
916 sqlQuery.addWhere(condition.toString());
917 hierarchy.addToFrom(sqlQuery, level.getKeyExp());
918 String childId = level.getKeyExp().getExpression(sqlQuery);
919 sqlQuery.addSelect(childId);
920 sqlQuery.addGroupBy(childId);
921 hierarchy.addToFrom(sqlQuery, level.getOrdinalExp());
922 String orderBy = level.getOrdinalExp().getExpression(sqlQuery);
923 sqlQuery.addOrderBy(orderBy, true, false, true);
924 if (!orderBy.equals(childId)) {
925 sqlQuery.addGroupBy(orderBy);
926 sqlQuery.addSelect(orderBy);
927 }
928
929 RolapProperty[] properties = level.getProperties();
930 for (RolapProperty property : properties) {
931 final MondrianDef.Expression exp = property.getExp();
932 hierarchy.addToFrom(sqlQuery, exp);
933 final String s = exp.getExpression(sqlQuery);
934 sqlQuery.addSelect(s);
935 sqlQuery.addGroupBy(s);
936 }
937 return sqlQuery.toString();
938 }
939
940 /**
941 * Generates the SQL statement to access the children of
942 * <code>member</code> in a parent-child hierarchy. For example,
943 * <blockquote>
944 *
945 * <pre>SELECT "employee_id"
946 * FROM "employee"
947 * WHERE "supervisor_id" = 5</pre>
948 * </blockquote> retrieves the children of the member
949 * <code>[Employee].[5]</code>.
950 *
951 * <p>See also {@link SqlTupleReader#makeLevelMembersSql}.
952 */
953 private String makeChildMemberSqlPC(RolapMember member) {
954 SqlQuery sqlQuery =
955 SqlQuery.newQuery(
956 dataSource,
957 "while generating query to retrieve children of " +
958 "parent/child hierarchy member " + member);
959 RolapLevel level = member.getLevel();
960
961 Util.assertTrue(!level.isAll(), "all level cannot be parent-child");
962 Util.assertTrue(level.isUnique(), "parent-child level '"
963 + level + "' must be unique");
964
965 hierarchy.addToFrom(sqlQuery, level.getParentExp());
966 String parentId = level.getParentExp().getExpression(sqlQuery);
967
968 StringBuilder buf = new StringBuilder();
969 sqlQuery.getDialect().quote(buf, member.getKey(), level.getDatatype());
970 sqlQuery.addWhere(parentId, " = ", buf.toString());
971
972 hierarchy.addToFrom(sqlQuery, level.getKeyExp());
973 String childId = level.getKeyExp().getExpression(sqlQuery);
974 sqlQuery.addSelect(childId);
975 sqlQuery.addGroupBy(childId);
976 hierarchy.addToFrom(sqlQuery, level.getOrdinalExp());
977 String orderBy = level.getOrdinalExp().getExpression(sqlQuery);
978 sqlQuery.addOrderBy(orderBy, true, false, true);
979 if (!orderBy.equals(childId)) {
980 sqlQuery.addGroupBy(orderBy);
981 sqlQuery.addSelect(orderBy);
982 }
983
984 RolapProperty[] properties = level.getProperties();
985 for (RolapProperty property : properties) {
986 final MondrianDef.Expression exp = property.getExp();
987 hierarchy.addToFrom(sqlQuery, exp);
988 final String s = exp.getExpression(sqlQuery);
989 sqlQuery.addSelect(s);
990 sqlQuery.addGroupBy(s);
991 }
992 return sqlQuery.toString();
993 }
994
995 // implement MemberReader
996 public RolapMember getLeadMember(RolapMember member, int n) {
997 throw new UnsupportedOperationException();
998 }
999
1000 public void getMemberRange(
1001 RolapLevel level,
1002 RolapMember startMember,
1003 RolapMember endMember,
1004 List<RolapMember> memberList)
1005 {
1006 throw new UnsupportedOperationException();
1007 }
1008
1009 public int compare(
1010 RolapMember m1,
1011 RolapMember m2,
1012 boolean siblingsAreEqual)
1013 {
1014 throw new UnsupportedOperationException();
1015 }
1016
1017
1018 public TupleReader.MemberBuilder getMemberBuilder() {
1019 return this;
1020 }
1021
1022 public RolapMember getDefaultMember() {
1023 // we expected the CacheMemberReader to implement this
1024 throw new UnsupportedOperationException();
1025 }
1026
1027 public RolapMember getMemberParent(RolapMember member) {
1028 throw new UnsupportedOperationException();
1029 }
1030
1031 // ~ -- Inner classes ------------------------------------------------------
1032
1033 /**
1034 * Member of a parent-child dimension which has a closure table.
1035 *
1036 * <p>When looking up cells, this member will automatically be converted
1037 * to a corresponding member of the auxiliary dimension which maps onto
1038 * the closure table.
1039 */
1040 private static class RolapParentChildMember extends RolapMember {
1041 private final RolapMember dataMember;
1042 private int depth = 0;
1043
1044 public RolapParentChildMember(
1045 RolapMember parentMember,
1046 RolapLevel childLevel,
1047 Object value,
1048 RolapMember dataMember)
1049 {
1050 super(parentMember, childLevel, value);
1051 this.dataMember = dataMember;
1052 this.depth = (parentMember != null)
1053 ? parentMember.getDepth() + 1
1054 : 0;
1055 }
1056
1057 public Member getDataMember() {
1058 return dataMember;
1059 }
1060
1061 public Object getPropertyValue(String propertyName, boolean matchCase) {
1062 if (Util.equal(
1063 propertyName, Property.CONTRIBUTING_CHILDREN.name, matchCase))
1064 {
1065 List<RolapMember> list = new ArrayList<RolapMember>();
1066 list.add(dataMember);
1067 RolapHierarchy hierarchy = getHierarchy();
1068 hierarchy.getMemberReader().getMemberChildren(dataMember, list);
1069 return list;
1070 } else {
1071 return super.getPropertyValue(propertyName, matchCase);
1072 }
1073 }
1074
1075 /**
1076 * @return the members's depth
1077 * @see mondrian.olap.Member#getDepth()
1078 */
1079 public int getDepth() {
1080 return depth;
1081 }
1082
1083 public int getOrdinal() {
1084 return dataMember.getOrdinal();
1085 }
1086 }
1087
1088 /**
1089 * Member of a parent-child dimension which has no closure table.
1090 *
1091 * <p>This member is calculated. When you ask for its value, it returns
1092 * an expression which aggregates the values of its child members.
1093 * This calculation is very inefficient, and we can only support
1094 * aggregatable measures ("count distinct" is non-aggregatable).
1095 * Unfortunately it's the best we can do without a closure table.
1096 */
1097 private static class RolapParentChildMemberNoClosure
1098 extends RolapParentChildMember {
1099
1100 public RolapParentChildMemberNoClosure(
1101 RolapMember parentMember,
1102 RolapLevel childLevel, Object value, RolapMember dataMember)
1103 {
1104 super(parentMember, childLevel, value, dataMember);
1105 }
1106
1107 protected boolean computeCalculated(final MemberType memberType) {
1108 return true;
1109 }
1110
1111 public Exp getExpression() {
1112 return getHierarchy().getAggregateChildrenExpression();
1113 }
1114 }
1115 }
1116
1117 // End SqlMemberSource.java