001/*
002// This software is subject to the terms of the Eclipse Public License v1.0
003// Agreement, available at the following URL:
004// http://www.eclipse.org/legal/epl-v10.html.
005// You must accept the terms of that agreement to use this software.
006//
007// Copyright (C) 2004-2005 TONBELLER AG
008// Copyright (C) 2006-2009 Pentaho
009// All Rights Reserved.
010*/
011package mondrian.rolap;
012
013import mondrian.mdx.*;
014import mondrian.olap.*;
015import mondrian.olap.type.MemberType;
016import mondrian.olap.type.StringType;
017import mondrian.rolap.aggmatcher.AggStar;
018import mondrian.rolap.sql.SqlQuery;
019import mondrian.spi.Dialect;
020
021import java.util.ArrayList;
022import java.util.List;
023
024/**
025 * Creates SQL from parse tree nodes. Currently it creates the SQL that
026 * accesses a measure for the ORDER BY that is generated for a TopCount.<p/>
027 *
028 * @author av
029 * @since Nov 17, 2005
030  */
031public class RolapNativeSql {
032
033    private SqlQuery sqlQuery;
034    private Dialect dialect;
035
036    CompositeSqlCompiler numericCompiler;
037    CompositeSqlCompiler booleanCompiler;
038
039    RolapStoredMeasure storedMeasure;
040    final AggStar aggStar;
041    final Evaluator evaluator;
042    final RolapLevel rolapLevel;
043
044    /**
045     * We remember one of the measures so we can generate
046     * the constraints from RolapAggregationManager. Also
047     * make sure all measures live in the same star.
048     *
049     * @see RolapAggregationManager#makeRequest(RolapEvaluator)
050     */
051    private boolean saveStoredMeasure(RolapStoredMeasure m) {
052        if (storedMeasure != null) {
053            RolapStar star1 = getStar(storedMeasure);
054            RolapStar star2 = getStar(m);
055            if (star1 != star2) {
056                return false;
057            }
058        }
059        this.storedMeasure = m;
060        return true;
061    }
062
063    private RolapStar getStar(RolapStoredMeasure m) {
064        return ((RolapStar.Measure) m.getStarMeasure()).getStar();
065    }
066
067    /**
068     * Translates an expression into SQL
069     */
070    interface SqlCompiler {
071        /**
072         * Returns SQL. If <code>exp</code> can not be compiled into SQL,
073         * returns null.
074         *
075         * @param exp Expression
076         * @return SQL, or null if cannot be converted into SQL
077         */
078        String compile(Exp exp);
079    }
080
081    /**
082     * Implementation of {@link SqlCompiler} that uses chain of responsibility
083     * to find a matching sql compiler.
084     */
085    static class CompositeSqlCompiler implements SqlCompiler {
086        List<SqlCompiler> compilers = new ArrayList<SqlCompiler>();
087
088        public void add(SqlCompiler compiler) {
089            compilers.add(compiler);
090        }
091
092        public String compile(Exp exp) {
093            for (SqlCompiler compiler : compilers) {
094                String s = compiler.compile(exp);
095                if (s != null) {
096                    return s;
097                }
098            }
099            return null;
100        }
101
102        public String toString() {
103            return compilers.toString();
104        }
105    }
106
107    /**
108     * Compiles a numeric literal to SQL.
109     */
110    class NumberSqlCompiler implements SqlCompiler {
111        public String compile(Exp exp) {
112            if (!(exp instanceof Literal)) {
113                return null;
114            }
115            if ((exp.getCategory() & Category.Numeric) == 0) {
116                return null;
117            }
118            Literal literal = (Literal) exp;
119            String expr = String.valueOf(literal.getValue());
120            if (dialect.getDatabaseProduct().getFamily()
121                == Dialect.DatabaseProduct.DB2)
122            {
123                expr = "FLOAT(" + expr + ")";
124            }
125            return expr;
126        }
127
128        public String toString() {
129            return "NumberSqlCompiler";
130        }
131    }
132
133    /**
134     * Base class to remove MemberScalarExp.
135     */
136    abstract class MemberSqlCompiler implements SqlCompiler {
137        protected Exp unwind(Exp exp) {
138            return exp;
139        }
140    }
141
142    /**
143     * Compiles a measure into SQL, the measure will be aggregated
144     * like <code>sum(measure)</code>.
145     */
146    class StoredMeasureSqlCompiler extends MemberSqlCompiler {
147
148        public String compile(Exp exp) {
149            exp = unwind(exp);
150            if (!(exp instanceof MemberExpr)) {
151                return null;
152            }
153            final Member member = ((MemberExpr) exp).getMember();
154            if (!(member instanceof RolapStoredMeasure)) {
155                return null;
156            }
157            RolapStoredMeasure measure = (RolapStoredMeasure) member;
158            if (measure.isCalculated()) {
159                return null; // ??
160            }
161            if (!saveStoredMeasure(measure)) {
162                return null;
163            }
164
165            String exprInner;
166            // Use aggregate table to create condition if available
167            if (aggStar != null
168                && measure.getStarMeasure() instanceof RolapStar.Column)
169            {
170                RolapStar.Column column =
171                    (RolapStar.Column) measure.getStarMeasure();
172                int bitPos = column.getBitPosition();
173                AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
174                exprInner = aggColumn.generateExprString(sqlQuery);
175            } else {
176                exprInner =
177                    measure.getMondrianDefExpression().getExpression(sqlQuery);
178            }
179
180            String expr = measure.getAggregator().getExpression(exprInner);
181            if (dialect.getDatabaseProduct().getFamily()
182                == Dialect.DatabaseProduct.DB2)
183            {
184                expr = "FLOAT(" + expr + ")";
185            }
186            return expr;
187        }
188
189        public String toString() {
190            return "StoredMeasureSqlCompiler";
191        }
192    }
193
194    /**
195     * Compiles a MATCHES MDX operator into SQL regular
196     * expression match.
197     */
198    class MatchingSqlCompiler extends FunCallSqlCompilerBase {
199
200        protected MatchingSqlCompiler()
201        {
202            super(Category.Logical, "MATCHES", 2);
203        }
204
205        public String compile(Exp exp) {
206            if (!match(exp)) {
207                return null;
208            }
209            if (!dialect.allowsRegularExpressionInWhereClause()
210                || !(exp instanceof ResolvedFunCall)
211                || evaluator == null)
212            {
213                return null;
214            }
215
216            final Exp arg0 = ((ResolvedFunCall)exp).getArg(0);
217            final Exp arg1 = ((ResolvedFunCall)exp).getArg(1);
218
219            // Must finish by ".Caption" or ".Name"
220            if (!(arg0 instanceof ResolvedFunCall)
221                || ((ResolvedFunCall)arg0).getArgCount() != 1
222                || !(arg0.getType() instanceof StringType)
223                || (!((ResolvedFunCall)arg0).getFunName().equals("Name")
224                    && !((ResolvedFunCall)arg0)
225                            .getFunName().equals("Caption")))
226            {
227                return null;
228            }
229
230            final boolean useCaption;
231            if (((ResolvedFunCall)arg0).getFunName().equals("Name")) {
232                useCaption = false;
233            } else {
234                useCaption = true;
235            }
236
237            // Must be ".CurrentMember"
238            final Exp currMemberExpr = ((ResolvedFunCall)arg0).getArg(0);
239            if (!(currMemberExpr instanceof ResolvedFunCall)
240                || ((ResolvedFunCall)currMemberExpr).getArgCount() != 1
241                || !(currMemberExpr.getType() instanceof MemberType)
242                || !((ResolvedFunCall)currMemberExpr)
243                        .getFunName().equals("CurrentMember"))
244            {
245                return null;
246            }
247
248            // Must be a dimension, a hierarchy or a level.
249            final RolapCubeDimension dimension;
250            final Exp dimExpr = ((ResolvedFunCall)currMemberExpr).getArg(0);
251            if (dimExpr instanceof DimensionExpr) {
252                dimension =
253                    (RolapCubeDimension) evaluator.getCachedResult(
254                        new ExpCacheDescriptor(dimExpr, evaluator));
255            } else if (dimExpr instanceof HierarchyExpr) {
256                final RolapCubeHierarchy hierarchy =
257                    (RolapCubeHierarchy) evaluator.getCachedResult(
258                        new ExpCacheDescriptor(dimExpr, evaluator));
259                dimension = (RolapCubeDimension) hierarchy.getDimension();
260            } else if (dimExpr instanceof LevelExpr) {
261                final RolapCubeLevel level =
262                    (RolapCubeLevel) evaluator.getCachedResult(
263                        new ExpCacheDescriptor(dimExpr, evaluator));
264                dimension = (RolapCubeDimension) level.getDimension();
265            } else {
266                return null;
267            }
268
269            if (rolapLevel != null
270                && dimension.equals(rolapLevel.getDimension()))
271            {
272                // We can't use the evaluator because the filter is filtering
273                // a set which is uses same dimension as the predicate.
274                // We must use, in order of priority,
275                //  - caption requested: caption->name->key
276                //  - name requested: name->key
277                MondrianDef.Expression expression = useCaption
278                ? rolapLevel.captionExp == null
279                        ? rolapLevel.nameExp == null
280                            ? rolapLevel.keyExp
281                            : rolapLevel.nameExp
282                        : rolapLevel.captionExp
283                    : rolapLevel.nameExp == null
284                        ? rolapLevel.keyExp
285                        : rolapLevel.nameExp;
286                /*
287                 * If an aggregation table is used, it might be more efficient
288                 * to use only the aggregate table and not the hierarchy table.
289                 * Try to lookup the column bit key. If that fails, we will
290                 * link the aggregate table to the hierarchy table. If no
291                 * aggregate table is used, we can use the column expression
292                 * directly.
293                 */
294                String sourceExp;
295                if (aggStar != null
296                    && rolapLevel instanceof RolapCubeLevel
297                    && expression == rolapLevel.keyExp)
298                {
299                    int bitPos =
300                        ((RolapCubeLevel)rolapLevel).getStarKeyColumn()
301                            .getBitPosition();
302                    mondrian.rolap.aggmatcher.AggStar.Table.Column col =
303                        aggStar.lookupColumn(bitPos);
304                    if (col != null) {
305                        sourceExp = col.generateExprString(sqlQuery);
306                    } else {
307                        // Make sure the level table is part of the query.
308                        rolapLevel.getHierarchy().addToFrom(
309                            sqlQuery,
310                            expression);
311                        sourceExp = expression.getExpression(sqlQuery);
312                    }
313                } else if (aggStar != null) {
314                    // Make sure the level table is part of the query.
315                    rolapLevel.getHierarchy().addToFrom(sqlQuery, expression);
316                    sourceExp = expression.getExpression(sqlQuery);
317                } else {
318                    sourceExp = expression.getExpression(sqlQuery);
319                }
320
321                // The dialect might require the use of the alias rather
322                // then the column exp.
323                if (dialect.requiresHavingAlias()) {
324                    sourceExp = sqlQuery.getAlias(sourceExp);
325                }
326                return
327                    dialect.generateRegularExpression(
328                        sourceExp,
329                        String.valueOf(
330                            evaluator.getCachedResult(
331                                new ExpCacheDescriptor(arg1, evaluator))));
332            } else {
333                return null;
334            }
335        }
336        public String toString() {
337            return "MatchingSqlCompiler";
338        }
339    }
340
341    /**
342     * Compiles the underlying expression of a calculated member.
343     */
344    class CalculatedMemberSqlCompiler extends MemberSqlCompiler {
345        SqlCompiler compiler;
346
347        CalculatedMemberSqlCompiler(SqlCompiler argumentCompiler) {
348            this.compiler = argumentCompiler;
349        }
350
351        public String compile(Exp exp) {
352            exp = unwind(exp);
353            if (!(exp instanceof MemberExpr)) {
354                return null;
355            }
356            final Member member = ((MemberExpr) exp).getMember();
357            if (!(member instanceof RolapCalculatedMember)) {
358                return null;
359            }
360            exp = member.getExpression();
361            if (exp == null) {
362                return null;
363            }
364            return compiler.compile(exp);
365        }
366
367        public String toString() {
368            return "CalculatedMemberSqlCompiler";
369        }
370    }
371
372    /**
373     * Contains utility methods to compile FunCall expressions into SQL.
374     */
375    abstract class FunCallSqlCompilerBase implements SqlCompiler {
376        int category;
377        String mdx;
378        int argCount;
379
380        FunCallSqlCompilerBase(int category, String mdx, int argCount) {
381            this.category = category;
382            this.mdx = mdx;
383            this.argCount = argCount;
384        }
385
386        /**
387         * @return true if exp is a matching FunCall
388         */
389        protected boolean match(Exp exp) {
390            if ((exp.getCategory() & category) == 0) {
391                return false;
392            }
393            if (!(exp instanceof FunCall)) {
394                return false;
395            }
396            FunCall fc = (FunCall) exp;
397            if (!mdx.equalsIgnoreCase(fc.getFunName())) {
398                return false;
399            }
400            Exp[] args = fc.getArgs();
401            if (args.length != argCount) {
402                return false;
403            }
404            return true;
405        }
406
407        /**
408         * compiles the arguments of a FunCall
409         *
410         * @return array of expressions or null if either exp does not match or
411         * any argument could not be compiled.
412         */
413        protected String[] compileArgs(Exp exp, SqlCompiler compiler) {
414            if (!match(exp)) {
415                return null;
416            }
417            Exp[] args = ((FunCall) exp).getArgs();
418            String[] sqls = new String[args.length];
419            for (int i = 0; i < args.length; i++) {
420                sqls[i] = compiler.compile(args[i]);
421                if (sqls[i] == null) {
422                    return null;
423                }
424            }
425            return sqls;
426        }
427    }
428
429    /**
430     * Compiles a funcall, e.g. foo(a, b, c).
431     */
432    class FunCallSqlCompiler extends FunCallSqlCompilerBase {
433        SqlCompiler compiler;
434        String sql;
435
436        protected FunCallSqlCompiler(
437            int category, String mdx, String sql,
438            int argCount, SqlCompiler argumentCompiler)
439        {
440            super(category, mdx, argCount);
441            this.sql = sql;
442            this.compiler = argumentCompiler;
443        }
444
445        public String compile(Exp exp) {
446            String[] args = compileArgs(exp, compiler);
447            if (args == null) {
448                return null;
449            }
450            StringBuilder buf = new StringBuilder();
451            buf.append(sql);
452            buf.append("(");
453            for (int i = 0; i < args.length; i++) {
454                if (i > 0) {
455                    buf.append(", ");
456                }
457                buf.append(args[i]);
458            }
459            buf.append(") ");
460            return buf.toString();
461        }
462
463        public String toString() {
464            return "FunCallSqlCompiler[" + mdx + "]";
465        }
466    }
467
468    /**
469     * Shortcut for an unary operator like NOT(a).
470     */
471    class UnaryOpSqlCompiler extends FunCallSqlCompiler {
472        protected UnaryOpSqlCompiler(
473            int category,
474            String mdx,
475            String sql,
476            SqlCompiler argumentCompiler)
477        {
478            super(category, mdx, sql, 1, argumentCompiler);
479        }
480    }
481
482    /**
483     * Shortcut for ().
484     */
485    class ParenthesisSqlCompiler extends FunCallSqlCompiler {
486        protected ParenthesisSqlCompiler(
487            int category,
488            SqlCompiler argumentCompiler)
489        {
490            super(category, "()", "", 1, argumentCompiler);
491        }
492
493        public String toString() {
494            return "ParenthesisSqlCompiler";
495        }
496    }
497
498    /**
499     * Compiles an infix operator like addition into SQL like <code>(a
500     * + b)</code>.
501     */
502    class InfixOpSqlCompiler extends FunCallSqlCompilerBase {
503        private final String sql;
504        private final SqlCompiler compiler;
505
506        protected InfixOpSqlCompiler(
507            int category,
508            String mdx,
509            String sql,
510            SqlCompiler argumentCompiler)
511        {
512            super(category, mdx, 2);
513            this.sql = sql;
514            this.compiler = argumentCompiler;
515        }
516
517        public String compile(Exp exp) {
518            String[] args = compileArgs(exp, compiler);
519            if (args == null) {
520                return null;
521            }
522            return "(" + args[0] + " " + sql + " " + args[1] + ")";
523        }
524
525        public String toString() {
526            return "InfixSqlCompiler[" + mdx + "]";
527        }
528    }
529
530    /**
531     * Compiles an <code>IsEmpty(measure)</code>
532     * expression into SQL <code>measure is null</code>.
533     */
534    class IsEmptySqlCompiler extends FunCallSqlCompilerBase {
535        private final SqlCompiler compiler;
536
537        protected IsEmptySqlCompiler(
538            int category, String mdx,
539            SqlCompiler argumentCompiler)
540        {
541            super(category, mdx, 1);
542            this.compiler = argumentCompiler;
543        }
544
545        public String compile(Exp exp) {
546            String[] args = compileArgs(exp, compiler);
547            if (args == null) {
548                return null;
549            }
550            return "(" + args[0] + " is null" + ")";
551        }
552
553        public String toString() {
554            return "IsEmptySqlCompiler[" + mdx + "]";
555        }
556    }
557
558    /**
559     * Compiles an <code>IIF(cond, val1, val2)</code> expression into SQL
560     * <code>CASE WHEN cond THEN val1 ELSE val2 END</code>.
561     */
562    class IifSqlCompiler extends FunCallSqlCompilerBase {
563
564        SqlCompiler valueCompiler;
565
566        IifSqlCompiler(int category, SqlCompiler valueCompiler) {
567            super(category, "iif", 3);
568            this.valueCompiler = valueCompiler;
569        }
570
571        public String compile(Exp exp) {
572            if (!match(exp)) {
573                return null;
574            }
575            Exp[] args = ((FunCall) exp).getArgs();
576            String cond = booleanCompiler.compile(args[0]);
577            String val1 = valueCompiler.compile(args[1]);
578            String val2 = valueCompiler.compile(args[2]);
579            if (cond == null || val1 == null || val2 == null) {
580                return null;
581            }
582            return sqlQuery.getDialect().caseWhenElse(cond, val1, val2);
583        }
584    }
585
586    /**
587     * Creates a RolapNativeSql.
588     *
589     * @param sqlQuery the query which is needed for different SQL dialects -
590     * it is not modified
591     */
592    public RolapNativeSql(
593        SqlQuery sqlQuery,
594        AggStar aggStar,
595        Evaluator evaluator,
596        RolapLevel rolapLevel)
597    {
598        this.sqlQuery = sqlQuery;
599        this.rolapLevel = rolapLevel;
600        this.evaluator = evaluator;
601        this.dialect = sqlQuery.getDialect();
602        this.aggStar = aggStar;
603
604        numericCompiler = new CompositeSqlCompiler();
605        booleanCompiler = new CompositeSqlCompiler();
606
607        numericCompiler.add(new NumberSqlCompiler());
608        numericCompiler.add(new StoredMeasureSqlCompiler());
609        numericCompiler.add(new CalculatedMemberSqlCompiler(numericCompiler));
610        numericCompiler.add(
611            new ParenthesisSqlCompiler(Category.Numeric, numericCompiler));
612        numericCompiler.add(
613            new InfixOpSqlCompiler(
614                Category.Numeric, "+", "+", numericCompiler));
615        numericCompiler.add(
616            new InfixOpSqlCompiler(
617                Category.Numeric, "-", "-", numericCompiler));
618        numericCompiler.add(
619            new InfixOpSqlCompiler(
620                Category.Numeric, "/", "/", numericCompiler));
621        numericCompiler.add(
622            new InfixOpSqlCompiler(
623                Category.Numeric, "*", "*", numericCompiler));
624        numericCompiler.add(
625            new IifSqlCompiler(Category.Numeric, numericCompiler));
626
627        booleanCompiler.add(
628            new InfixOpSqlCompiler(
629                Category.Logical, "<", "<", numericCompiler));
630        booleanCompiler.add(
631            new InfixOpSqlCompiler(
632                Category.Logical, "<=", "<=", numericCompiler));
633        booleanCompiler.add(
634            new InfixOpSqlCompiler(
635                Category.Logical, ">", ">", numericCompiler));
636        booleanCompiler.add(
637            new InfixOpSqlCompiler(
638                Category.Logical, ">=", ">=", numericCompiler));
639        booleanCompiler.add(
640            new InfixOpSqlCompiler(
641                Category.Logical, "=", "=", numericCompiler));
642        booleanCompiler.add(
643            new InfixOpSqlCompiler(
644                Category.Logical, "<>", "<>", numericCompiler));
645        booleanCompiler.add(
646            new IsEmptySqlCompiler(
647                Category.Logical, "IsEmpty", numericCompiler));
648
649        booleanCompiler.add(
650            new InfixOpSqlCompiler(
651                Category.Logical, "and", "AND", booleanCompiler));
652        booleanCompiler.add(
653            new InfixOpSqlCompiler(
654                Category.Logical, "or", "OR", booleanCompiler));
655        booleanCompiler.add(
656            new UnaryOpSqlCompiler(
657                Category.Logical, "not", "NOT", booleanCompiler));
658        booleanCompiler.add(
659            new MatchingSqlCompiler());
660        booleanCompiler.add(
661            new ParenthesisSqlCompiler(Category.Logical, booleanCompiler));
662        booleanCompiler.add(
663            new IifSqlCompiler(Category.Logical, booleanCompiler));
664    }
665
666    /**
667     * Generates an aggregate of a measure, e.g. "sum(Store_Sales)" for
668     * TopCount. The returned expr will be added to the select list and to the
669     * order by clause.
670     */
671    public String generateTopCountOrderBy(Exp exp) {
672        return numericCompiler.compile(exp);
673    }
674
675    public String generateFilterCondition(Exp exp) {
676        return booleanCompiler.compile(exp);
677    }
678
679    public RolapStoredMeasure getStoredMeasure() {
680        return storedMeasure;
681    }
682
683}
684
685// End RolapNativeSql.java