SqlConstraintUtils


1   /*
2   // $Id: //open/mondrian/src/main/mondrian/rolap/SqlConstraintUtils.java#21 $
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) 2004-2005 TONBELLER AG
7   // All Rights Reserved.
8   // You must accept the terms of that agreement to use this software.
9    */
10  package mondrian.rolap;
11  
12  import java.util.*;
13  
14  import mondrian.olap.Evaluator;
15  import mondrian.olap.Member;
16  import mondrian.olap.MondrianDef;
17  import mondrian.olap.MondrianProperties;
18  import mondrian.olap.Util;
19  import mondrian.rolap.agg.*;
20  import mondrian.rolap.sql.SqlQuery;
21  import mondrian.rolap.aggmatcher.AggStar;
22  
23  /**
24   * Utility class used by implementations of {@link mondrian.rolap.sql.SqlConstraint},
25   * used to generate constraints into {@link mondrian.rolap.sql.SqlQuery}.
26   *
27   * @author av
28   * @since Nov 21, 2005
29   * @version $Id: //open/mondrian/src/main/mondrian/rolap/SqlConstraintUtils.java#21 $
30   */
31  public class SqlConstraintUtils {
32  
33      /** Utility class */
34      private SqlConstraintUtils() {
35      }
36  
37      /**
38       * For every restricting member in the current context, generates
39       * a WHERE condition and a join to the fact table.
40       *
41       * @param sqlQuery the query to modify
42       * @param aggStar Aggregate table, or null if query is against fact table
43       * @param strict defines the behavior if the current context contains
44       *   calculated members.
45       *   If true, an exception is thrown.
46       * @param evaluator Evaluator
47       */
48      public static void addContextConstraint(
49          SqlQuery sqlQuery,
50          AggStar aggStar,
51          Evaluator evaluator,
52          boolean strict) {
53  
54          Member[] members = evaluator.getMembers();
55          if (strict) {
56              if (containsCalculatedMember(members)) {
57                  throw Util.newInternal("can not restrict SQL to calculated Members");
58              }
59          } else {
60              members = removeCalculatedMembers(members);
61              members = removeDefaultMembers(members);
62          }
63  
64          CellRequest request =
65                  RolapAggregationManager.makeRequest(members, false, false);
66          if (request == null) {
67              if (strict) {
68                  throw Util.newInternal("CellRequest is null - why?");
69              }
70              // One or more of the members was null or calculated, so the
71              // request is impossible to satisfy.
72              return;
73          }
74          RolapStar.Column[] columns = request.getConstrainedColumns();
75          Object  [] values = request.getSingleValues();
76          int arity = columns.length;
77          // following code is similar to AbstractQuerySpec#nonDistinctGenerateSQL()
78          for (int i = 0; i < arity; i++) {
79              RolapStar.Column column = columns[i];
80  
81              String   expr;
82              if (aggStar != null) {
83                  int bitPos = column.getBitPosition();
84                  AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
85                  AggStar.Table table = aggColumn.getTable();
86                  table.addToFrom(sqlQuery, false, true);
87  
88                  expr = aggColumn.generateExprString(sqlQuery);
89              } else {
90                  RolapStar.Table table = column.getTable();
91                  table.addToFrom(sqlQuery, false, true);
92  
93                  expr = column.generateExprString(sqlQuery);
94              }
95  
96              final String   value = String.valueOf(values[i]);
97              if (RolapUtil.mdxNullLiteral.equalsIgnoreCase(value)) {
98                  sqlQuery.addWhere(
99                      expr,
100                     " is ",
101                     RolapUtil.sqlNullLiteral);
102             } else {
103                 if (column.getDatatype().isNumeric()) {
104                     // make sure it can be parsed
105                     Double.valueOf(value);
106                 }
107                 final StringBuilder   buf = new StringBuilder  ();
108                 sqlQuery.getDialect().quote(buf, value, column.getDatatype());
109                 sqlQuery.addWhere(
110                     expr,
111                     " = ",
112                     buf.toString());
113             }
114         }
115     }
116 
117     /**
118      * Removes the default members from an array.
119      *
120      * <p>This is required only if the default member is
121      * not the ALL member. The time dimension for example, has 1997 as default
122      * member. When we evaluate the query
123      * <pre>
124      *   select NON EMPTY crossjoin(
125      *     {[Time].[1998]}, [Customer].[All].children
126      *   ) on columns
127      *   from [sales]
128      * </pre>
129      * the <code>[Customer].[All].children</code> is evaluated with the default
130      * member <code>[Time].[1997]</code> in the evaluator context. This is wrong
131      * because the NON EMPTY must filter out Customres with no rows in the fact
132      * table for 1998 not 1997. So we do not restrict the time dimension and
133      * fetch all children.
134      *
135      * @param members Array of members
136      * @return Array of members with default members removed
137      */
138     private static Member[] removeDefaultMembers(Member[] members) {
139         List<Member> result = new ArrayList<Member>();
140         result.add(members[0]); // add the measure
141         for (int i = 1; i < members.length; i++) {
142             Member m = members[i];
143             if (m.getHierarchy().getDefaultMember().equals(m)) {
144                 continue;
145             }
146             result.add(m);
147         }
148         return result.toArray(new Member[result.size()]);
149     }
150 
151     private static Member[] removeCalculatedMembers(Member[] members) {
152         List<Member> result = new ArrayList<Member>();
153         for (Member member : members) {
154             if (!member.isCalculated()) {
155                 result.add(member);
156             }
157         }
158         return result.toArray(new Member[result.size()]);
159     }
160 
161     private static boolean containsCalculatedMember(Member[] members) {
162         for (Member member : members) {
163             if (member.isCalculated()) {
164                 return true;
165             }
166         }
167         return false;
168     }
169 
170     /**
171      * Ensures that the table of <code>level</code> is joined to the fact
172      * table
173      *
174      * @param sqlQuery sql query under construction
175      * @param aggStar
176      * @param e evaluator corresponding to query
177      * @param level level to be added to query
178      * @param levelToColumnMap maps level to star columns; set only in the
179      */
180     public static void joinLevelTableToFactTable(
181         SqlQuery sqlQuery,
182         AggStar aggStar,
183         Evaluator e,
184         RolapLevel level,
185         Map<RolapLevel, RolapStar.Column> levelToColumnMap)
186     {
187         RolapCube cube = (RolapCube) e.getCube();
188         if (!cube.isVirtual()) {
189             RolapStar star = cube.getStar();
190             levelToColumnMap = star.getLevelToColumnMap(cube);
191         }
192         RolapStar.Column starColumn = levelToColumnMap.get(level);
193         assert starColumn != null;
194         if (aggStar != null) {
195             int bitPos = starColumn.getBitPosition();
196             AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
197             AggStar.Table table = aggColumn.getTable();
198             table.addToFrom(sqlQuery, false, true);
199         } else {
200             RolapStar.Table table = starColumn.getTable();
201             assert table != null;
202             table.addToFrom(sqlQuery, false, true);
203         }
204     }
205 
206     /**
207      * Creates a "WHERE parent = value" constraint.
208      *
209      * @param sqlQuery the query to modify
210      * @param levelToColumnMap where to find each level's key
211      * @param aggStar Definition of the aggregate table, or null
212      * @param parent the list of parent members
213      * @param strict defines the behavior if <code>parent</code>
214      * is a calculated member. If true, an exception is thrown
215      */
216     public static void addMemberConstraint(
217         SqlQuery sqlQuery,
218         Map<RolapLevel, RolapStar.Column> levelToColumnMap,
219         AggStar aggStar,
220         RolapMember parent,
221         boolean strict)
222     {
223         List<RolapMember> list = Collections.singletonList(parent);
224         addMemberConstraint(
225             sqlQuery, levelToColumnMap, aggStar, list, strict, false);
226     }
227 
228     /**
229      * Creates a "WHERE exp IN (...)" condition containing the values
230      * of all parents.  All parents must belong to the same level.
231      *
232      * <p>If this constraint is part of a native cross join, there are
233      * multiple constraining members, and the members comprise the cross
234      * product of all unique member keys referenced at each level, then
235      * generating IN expressions would result in incorrect results.  In that
236      * case, "WHERE ((level1 = val1a AND level2 = val2a AND ...)
237      * OR (level1 = val1b AND level2 = val2b AND ...) OR ..." is generated
238      * instead.
239      *
240      * @param sqlQuery the query to modify
241      * @param levelToColumnMap where to find each level's key
242      * @param aggStar (not used)
243      * @param parents the list of parent members
244      * @param strict defines the behavior if <code>parents</code>
245      *   contains calculated members.
246      *   If true, and one of the members is calculated, an exception is thrown.
247      * @param crossJoin true if constraint is being generated as part of
248      *   a native crossjoin
249      */
250     public static void addMemberConstraint(
251         SqlQuery sqlQuery,
252         Map<RolapLevel, RolapStar.Column> levelToColumnMap,
253         AggStar aggStar,
254         List<RolapMember> parents,
255         boolean strict,
256         boolean crossJoin)
257     {
258         if (parents.size() == 0) {
259             return;
260         }
261 
262         // If this constraint is part of a native cross join and there
263         // are multiple values for the parent members, then we can't
264         // use IN clauses
265         if (crossJoin) {
266             RolapLevel level = parents.get(0).getLevel();
267             if (!level.isUnique() && !membersAreCrossProduct(parents)) {
268                 constrainMultiLevelMembers(sqlQuery, parents, strict);
269                 return;
270             }
271         }
272 
273         for (Collection<RolapMember> c = parents;
274             !c.isEmpty();
275             c = getUniqueParentMembers(c))
276         {
277             RolapMember m = c.iterator().next();
278             if (m.isAll()) {
279                 continue;
280             }
281             if (m.isCalculated()) {
282                 if (strict) {
283                     throw Util.newInternal("addMemberConstraint: cannot " +
284                         "restrict SQL to calculated member :" + m);
285                 }
286                 continue;
287             }
288             RolapLevel level = m.getLevel();
289             RolapHierarchy hierarchy = level.getHierarchy();
290             hierarchy.addToFrom(sqlQuery, level.getKeyExp());
291             String   q = level.getKeyExp().getExpression(sqlQuery);
292             RolapStar.Column column = levelToColumnMap.get(level);
293             StarColumnPredicate cc = getColumnPredicates(column, c);
294 
295             if (!strict &&
296                 cc instanceof ListColumnPredicate &&
297                 ((ListColumnPredicate) cc).getPredicates().size() >=
298                     MondrianProperties.instance().MaxConstraints.get())
299             {
300                 // Simply get them all, do not create where-clause.
301                 // Below are two alternative approaches (and code). They
302                 // both have problems.
303             } else {
304                 final String   where = RolapStar.Column.createInExpr(
305                     q, cc, level.getDatatype(), sqlQuery.getDialect());
306                 if (!where.equals("true")) {
307                     sqlQuery.addWhere(where);
308                 }
309             }
310             if (level.isUnique()) {
311                 break; // no further qualification needed
312             }
313         }
314     }
315 
316     private static StarColumnPredicate getColumnPredicates(
317         RolapStar.Column column,
318         Collection<RolapMember> members)
319     {
320         switch (members.size()) {
321         case 0:
322             return new LiteralStarPredicate(column, false);
323         case 1:
324             return new MemberColumnPredicate(column, members.iterator().next());
325         default:
326             List<StarColumnPredicate> predicateList =
327                 new ArrayList<StarColumnPredicate>();
328             for (RolapMember member : members) {
329                 predicateList.add(new MemberColumnPredicate(column, member));
330             }
331             return new ListColumnPredicate(column, predicateList);
332         }
333     }
334 
335     private static LinkedHashSet<RolapMember> getUniqueParentMembers(
336         Collection<RolapMember> members)
337     {
338         LinkedHashSet<RolapMember> set = new LinkedHashSet<RolapMember>();
339         for (RolapMember m : members) {
340             m = m.getParentMember();
341             if (m != null) {
342                 set.add(m);
343             }
344         }
345         return set;
346     }
347 
348     /**
349      * Adds to the where clause of a query expression matching a specified
350      * list of members
351      *
352      * @param sqlQuery query containing the where clause
353      * @param members list of constraining members
354      * @param strict defines the behavior when calculated members are present
355      */
356     private static void constrainMultiLevelMembers(
357         SqlQuery sqlQuery,
358         List<RolapMember> members,
359         boolean strict)
360     {
361         if (sqlQuery.getDialect().supportsMultiValueInExpr()) {
362             if (generateMultiValueInExpr(sqlQuery, members, strict)) {
363                 return;
364             }
365         }
366 
367         // iterate through each level in each member generating
368         // AND's across the levels and OR's across the members
369         String   condition = "(";
370         boolean firstMember = true;
371         for (RolapMember m : members) {
372             if (m.isCalculated()) {
373                 if (strict) {
374                     throw Util.newInternal("addMemberConstraint: cannot " +
375                         "restrict SQL to calculated member :" + m);
376                 }
377                 continue;
378             }
379             if (!firstMember) {
380                 condition += " or ";
381             }
382             condition += "(";
383             boolean firstLevel = true;
384             do {
385                 if (!m.isAll()) {
386                     RolapLevel level = m.getLevel();
387                     // add the level to the FROM clause if this is the
388                     // first member we're generating sql for
389                     if (firstMember) {
390                         RolapHierarchy hierarchy =
391                             (RolapHierarchy) level.getHierarchy();
392                         hierarchy.addToFrom(sqlQuery, level.getKeyExp());
393                     }
394                     if (!firstLevel) {
395                         condition += " and ";
396                     } else {
397                         firstLevel = false;
398                     }
399                     condition += constrainLevel(
400                         level,
401                         sqlQuery,
402                         getColumnValue(
403                             m.getSqlKey(),
404                             sqlQuery.getDialect(),
405                             level.getDatatype()),
406                             false);
407                 }
408                 m = m.getParentMember();
409             } while (m != null);
410             condition += ")";
411             firstMember = false;
412         }
413         condition += ")";
414         sqlQuery.addWhere(condition);
415     }
416 
417     /**
418      * @param members list of members
419      *
420      * @return true if the members comprise the cross product of all unique
421      * member keys referenced at each level
422      */
423     private static boolean membersAreCrossProduct(List<RolapMember> members)
424     {
425         int crossProdSize = getNumUniqueMemberKeys(members);
426         for (Collection<RolapMember> parents = getUniqueParentMembers(members);
427             !parents.isEmpty(); parents = getUniqueParentMembers(parents))
428         {
429             crossProdSize *= parents.size();
430         }
431         return (crossProdSize == members.size());
432     }
433 
434     /**
435      * @param members list of members
436      *
437      * @return number of unique member keys in a list of members
438      */
439     private static int getNumUniqueMemberKeys(List<RolapMember> members)
440     {
441         Set<Object  > set = new HashSet<Object  >();
442         for (RolapMember m : members) {
443             set.add(m.getKey());
444         }
445         return set.size();
446     }
447 
448     /**
449      * @param key key corresponding to a member
450      * @param dialect sql dialect being used
451      * @param datatype data type of the member
452      *
453      * @return string value corresponding to the member
454      */
455     private static String   getColumnValue(
456         Object   key,
457         SqlQuery.Dialect dialect,
458         SqlQuery.Datatype datatype)
459     {
460         if (key != RolapUtil.sqlNullValue) {
461             return key.toString();
462         } else {
463             return RolapUtil.mdxNullLiteral;
464         }
465     }
466 
467     /**
468      * Generates a sql expression constraining a level by some value
469      *
470      * @param level the level
471      * @param query the query that the sql expression will be added to
472      * @param columnValue value constraining the level
473      * @param caseSensitive if true, need to handle case sensitivity of the
474      * member value
475      *
476      * @return generated string corresponding to the expression
477      */
478     public static String   constrainLevel(
479         RolapLevel level,
480         SqlQuery query,
481         String   columnValue,
482         boolean caseSensitive)
483     {
484         MondrianDef.Expression exp = level.getNameExp();
485         SqlQuery.Datatype datatype;
486         if (exp == null) {
487             exp = level.getKeyExp();
488             datatype = level.getDatatype();
489         } else {
490             // The schema doesn't specify the datatype of the name column, but
491             // we presume that it is a string.
492             datatype = SqlQuery.Datatype.String;
493         }
494         String   column = exp.getExpression(query);
495         String   constraint;
496         if (RolapUtil.mdxNullLiteral.equalsIgnoreCase(columnValue)) {
497             constraint = column + " is " + RolapUtil.sqlNullLiteral;
498         } else {
499             if (datatype.isNumeric()) {
500                 // make sure it can be parsed
501                 Double.valueOf(columnValue);
502             }
503             final StringBuilder   buf = new StringBuilder  ();
504             query.getDialect().quote(buf, columnValue, datatype);
505             String   value = buf.toString();
506             if (caseSensitive && datatype == SqlQuery.Datatype.String) {
507                 // Some databases (like DB2) compare case-sensitive. We convert
508                 // the value to upper-case in the DBMS (e.g. UPPER('Foo'))
509                 // rather than in Java (e.g. 'FOO') in case the DBMS is running
510                 // a different locale.
511                 if (!MondrianProperties.instance().CaseSensitive.get()) {
512                     column = query.getDialect().toUpper(column);
513                     value = query.getDialect().toUpper(value);
514                 }
515             }
516 
517             constraint = column + " = " + value;
518         }
519 
520         return constraint;
521     }
522 
523     /**
524      * Generates a multi-value IN expression corresponding to a list of
525      * member expressions, and adds the expression to the WHERE clause
526      * of a query, provided the member values are all non-null
527      *
528      * @param sqlQuery query containing the where clause
529      * @param members list of constraining members
530      * @param strict defines the behavior when calculated members are present
531      *
532      * @return Whether it was possible to generate a multi-value IN expression
533      */
534     private static boolean generateMultiValueInExpr(
535         SqlQuery sqlQuery,
536         List<RolapMember> members,
537         boolean strict)
538     {
539         final StringBuilder   columnBuf = new StringBuilder  ();
540         columnBuf.append("(");
541         final StringBuilder   valueBuf = new StringBuilder  ();
542         valueBuf.append("(");
543 
544         boolean firstMember = true;
545         for (RolapMember m : members) {
546             if (m.isCalculated()) {
547                 if (strict) {
548                     throw Util.newInternal("addMemberConstraint: cannot " +
549                         "restrict SQL to calculated member :" + m);
550                 }
551                 continue;
552             }
553             boolean firstLevel = true;
554             do {
555                 if (m.isAll()) {
556                     m = (RolapMember) m.getParentMember();
557                     continue;
558                 }
559                 RolapLevel level = m.getLevel();
560                 // generate the left-hand side of the IN expression, if we're
561                 // iterating over the first member
562                 if (firstMember) {
563                     RolapHierarchy hierarchy =
564                         (RolapHierarchy) level.getHierarchy();
565                     hierarchy.addToFrom(sqlQuery, level.getKeyExp());
566                     if (!firstLevel) {
567                         columnBuf.append(",");
568                     }
569                     MondrianDef.Expression exp = level.getNameExp();
570                     if (exp == null) {
571                         exp = level.getKeyExp();
572                     }
573                     columnBuf.append(exp.getExpression(sqlQuery));
574                 }
575 
576                 if (firstLevel) {
577                     if (!firstMember) {
578                         valueBuf.append("), ");
579                     }
580                     valueBuf.append("(");
581                     firstLevel = false;
582                 } else {
583                     valueBuf.append(",");
584                 }
585 
586                 String   value = getColumnValue(
587                     m.getSqlKey(),
588                     sqlQuery.getDialect(),
589                     level.getDatatype());
590                 if (RolapUtil.mdxNullLiteral.equalsIgnoreCase(value)) {
591                     return false;
592                 }
593                 final StringBuilder   buf = new StringBuilder  ();
594                 sqlQuery.getDialect().quote(buf, value, level.getDatatype());
595                 valueBuf.append(buf.toString());
596 
597                 m = m.getParentMember();
598             } while (m != null);
599             firstMember = false;
600         }
601 
602         columnBuf.append(")");
603         valueBuf.append("))");
604 
605         sqlQuery.addWhere(
606             columnBuf.toString() + " in " + valueBuf.toString());
607 
608         return true;
609     }
610 }
611 
612 // End SqlConstraintUtils.java
613
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags