SqlMemberSource


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