001 /*
002 // This software is subject to the terms of the Common Public License
003 // Agreement, available at the following URL:
004 // http://www.opensource.org/licenses/cpl.html.
005 // Copyright (C) 2004-2005 TONBELLER AG
006 // All Rights Reserved.
007 // You must accept the terms of that agreement to use this software.
008 */
009 package mondrian.rolap;
010
011 import java.util.ArrayList;
012 import java.util.List;
013
014 import mondrian.olap.*;
015 import mondrian.rolap.sql.SqlQuery;
016 import mondrian.mdx.MemberExpr;
017
018 /**
019 * Creates SQL from parse tree nodes. Currently it creates the SQL that
020 * accesses a measure for the ORDER BY that is generated for a TopCount.<p/>
021 *
022 * @author av
023 * @since Nov 17, 2005
024 * @version $Id: //open/mondrian/src/main/mondrian/rolap/RolapNativeSql.java#18 $
025 */
026 public class RolapNativeSql {
027
028 private SqlQuery sqlQuery;
029 private SqlQuery.Dialect dialect;
030
031 CompositeSqlCompiler numericCompiler;
032 CompositeSqlCompiler booleanCompiler;
033
034 RolapStoredMeasure storedMeasure;
035
036 /**
037 * We remember one of the measures so we can generate
038 * the constraints from RolapAggregationManager. Also
039 * make sure all measures live in the same star.
040 *
041 * @see RolapAggregationManager#makeRequest(RolapEvaluator)
042 */
043 private boolean saveStoredMeasure(RolapStoredMeasure m) {
044 if (storedMeasure != null) {
045 RolapStar star1 = getStar(storedMeasure);
046 RolapStar star2 = getStar(m);
047 if (star1 != star2)
048 return false;
049 }
050 this.storedMeasure = m;
051 return true;
052 }
053
054 private RolapStar getStar(RolapStoredMeasure m) {
055 return ((RolapStar.Measure)m.getStarMeasure()).getStar();
056 }
057
058 /**
059 * Translates an expression into SQL
060 *
061 * @author av
062 * @since Nov 23, 2005
063 */
064 interface SqlCompiler {
065 /**
066 * Returns SQL. If <code>exp</code> can not be compiled into SQL,
067 * returns null.
068 *
069 * @param exp Expression
070 * @return SQL, or null if cannot be converted into SQL
071 */
072 String compile(Exp exp);
073 }
074
075 /**
076 * Implementation of {@link SqlCompiler} that uses chain of responsibility
077 * to find a matching sql compiler.
078 *
079 * @author av
080 * @since Nov 23, 2005
081 */
082 static class CompositeSqlCompiler implements SqlCompiler {
083 List<SqlCompiler> compilers = new ArrayList<SqlCompiler>();
084
085 public void add(SqlCompiler compiler) {
086 compilers.add(compiler);
087 }
088
089 public String compile(Exp exp) {
090 for (SqlCompiler compiler : compilers) {
091 String s = compiler.compile(exp);
092 if (s != null) {
093 return s;
094 }
095 }
096 return null;
097 }
098
099 public String toString() {
100 return compilers.toString();
101 }
102
103 }
104
105 /**
106 * Compiles a numeric literal to SQL.
107 *
108 * @author av
109 * @since Nov 23, 2005
110 */
111 class NumberSqlCompiler implements SqlCompiler {
112 public String compile(Exp exp) {
113 if (!(exp instanceof Literal)) {
114 return null;
115 }
116 if ((exp.getCategory() & Category.Numeric) == 0) {
117 return null;
118 }
119 Literal literal = (Literal) exp;
120 String expr = String.valueOf(literal.getValue());
121 if (dialect.isDB2()) {
122 expr = "FLOAT(" + expr + ")";
123 }
124 return expr;
125 }
126
127 public String toString() {
128 return "NumberSqlCompiler";
129 }
130 }
131
132 /**
133 * base class to remove MemberScalarExp
134 * @author av
135 * @since Nov 23, 2005
136 */
137 abstract class MemberSqlCompiler implements SqlCompiler {
138 protected Exp unwind(Exp exp) {
139 return exp;
140 }
141 }
142
143 /**
144 * compiles a measure into SQL, the measure will be aggregated like <code>sum(measure)</code>
145 *
146 * @author av
147 * @since Nov 23, 2005
148 */
149 class StoredMeasureSqlCompiler extends MemberSqlCompiler {
150
151 public String compile(Exp exp) {
152 exp = unwind(exp);
153 if (!(exp instanceof MemberExpr)) {
154 return null;
155 }
156 final Member member = ((MemberExpr) exp).getMember();
157 if (!(member instanceof RolapStoredMeasure)) {
158 return null;
159 }
160 RolapStoredMeasure measure = (RolapStoredMeasure) member;
161 if (measure.isCalculated()) {
162 return null; // ??
163 }
164 if (!saveStoredMeasure(measure)) {
165 return null;
166 }
167 String exprInner = measure.getMondrianDefExpression().getExpression(sqlQuery);
168 String expr = measure.getAggregator().getExpression(exprInner);
169 if (dialect.isDB2()) {
170 expr = "FLOAT(" + expr + ")";
171 }
172 return expr;
173 }
174
175 public String toString() {
176 return "StoredMeasureSqlCompiler";
177 }
178 }
179
180 /**
181 * compiles the underlying expression of a calculated member
182 *
183 * @author av
184 * @since Nov 23, 2005
185 */
186 class CalculatedMemberSqlCompiler extends MemberSqlCompiler {
187 SqlCompiler compiler;
188
189 CalculatedMemberSqlCompiler(SqlCompiler argumentCompiler) {
190 this.compiler = argumentCompiler;
191 }
192
193 public String compile(Exp exp) {
194 exp = unwind(exp);
195 if (!(exp instanceof MemberExpr)) {
196 return null;
197 }
198 final Member member = ((MemberExpr) exp).getMember();
199 if (!(member instanceof RolapCalculatedMember)) {
200 return null;
201 }
202 exp = member.getExpression();
203 if (exp == null) {
204 return null;
205 }
206 return compiler.compile(exp);
207 }
208
209 public String toString() {
210 return "CalculatedMemberSqlCompiler";
211 }
212 }
213
214 /**
215 * contains utility methods to compile FunCall expressions into SQL.
216 *
217 * @author av
218 * @since Nov 23, 2005
219 */
220 abstract class FunCallSqlCompilerBase implements SqlCompiler {
221 int category;
222 String mdx;
223 int argCount;
224
225 FunCallSqlCompilerBase(int category, String mdx, int argCount) {
226 this.category = category;
227 this.mdx = mdx;
228 this.argCount = argCount;
229 }
230
231 /**
232 * @return true if exp is a matching FunCall
233 */
234 protected boolean match(Exp exp) {
235 if ((exp.getCategory() & category) == 0) {
236 return false;
237 }
238 if (!(exp instanceof FunCall)) {
239 return false;
240 }
241 FunCall fc = (FunCall) exp;
242 if (!mdx.equalsIgnoreCase(fc.getFunName())) {
243 return false;
244 }
245 Exp[] args = fc.getArgs();
246 if (args.length != argCount) {
247 return false;
248 }
249 return true;
250 }
251
252 /**
253 * compiles the arguments of a FunCall
254 *
255 * @return array of expressions or null if either exp does not match or
256 * any argument could not be compiled.
257 */
258 protected String[] compileArgs(Exp exp, SqlCompiler compiler) {
259 if (!match(exp)) {
260 return null;
261 }
262 Exp[] args = ((FunCall) exp).getArgs();
263 String[] sqls = new String[args.length];
264 for (int i = 0; i < args.length; i++) {
265 sqls[i] = compiler.compile(args[i]);
266 if (sqls[i] == null) {
267 return null;
268 }
269 }
270 return sqls;
271 }
272 }
273
274 /**
275 * compiles a funcall, e.g. foo(a, b, c)
276 * @author av
277 * @since Nov 23, 2005
278 */
279 class FunCallSqlCompiler extends FunCallSqlCompilerBase {
280 SqlCompiler compiler;
281 String sql;
282
283 protected FunCallSqlCompiler(int category, String mdx, String sql,
284 int argCount, SqlCompiler argumentCompiler) {
285 super(category, mdx, argCount);
286 this.sql = sql;
287 this.compiler = argumentCompiler;
288 }
289
290 public String compile(Exp exp) {
291 String[] args = compileArgs(exp, compiler);
292 if (args == null) {
293 return null;
294 }
295 StringBuilder buf = new StringBuilder();
296 buf.append(sql);
297 buf.append("(");
298 for (int i = 0; i < args.length; i++) {
299 if (i > 0) {
300 buf.append(", ");
301 }
302 buf.append(args[i]);
303 }
304 buf.append(") ");
305 return buf.toString();
306 }
307
308 public String toString() {
309 return "FunCallSqlCompiler[" + mdx + "]";
310 }
311 }
312
313 /**
314 * shortcut for an unary operator like NOT(a)
315 * @author av
316 * @since Nov 23, 2005
317 */
318 class UnaryOpSqlCompiler extends FunCallSqlCompiler {
319 protected UnaryOpSqlCompiler(int category, String mdx, String sql, SqlCompiler argumentCompiler) {
320 super(category, mdx, sql, 1, argumentCompiler);
321 }
322 }
323
324 /**
325 * shortcut for ()
326 * @author av
327 * @since Nov 23, 2005
328 */
329 class ParenthesisSqlCompiler extends FunCallSqlCompiler {
330 protected ParenthesisSqlCompiler(int category, SqlCompiler argumentCompiler) {
331 super(category, "()", "", 1, argumentCompiler);
332 }
333
334 public String toString() {
335 return "ParenthesisSqlCompiler";
336 }
337 }
338
339 /**
340 * compiles an infix operator like addition into SQL like <code>(a + b)</code>
341 *
342 * @author av
343 * @since Nov 23, 2005
344 */
345 class InfixOpSqlCompiler extends FunCallSqlCompilerBase {
346 private final String sql;
347 private final SqlCompiler compiler;
348
349 protected InfixOpSqlCompiler(int category, String mdx, String sql,
350 SqlCompiler argumentCompiler) {
351 super(category, mdx, 2);
352 this.sql = sql;
353 this.compiler = argumentCompiler;
354 }
355
356 public String compile(Exp exp) {
357 String[] args = compileArgs(exp, compiler);
358 if (args == null) {
359 return null;
360 }
361 return "(" + args[0] + " " + sql + " " + args[1] + ")";
362 }
363
364 public String toString() {
365 return "InfixSqlCompiler[" + mdx + "]";
366 }
367 }
368
369 /**
370 * compiles an <code>IsEmpty(measure)</code>
371 * expression into SQL <code>measure is null</code>
372 *
373 */
374 class IsEmptySqlCompiler extends FunCallSqlCompilerBase {
375 private final SqlCompiler compiler;
376
377 protected IsEmptySqlCompiler(int category, String mdx,
378 SqlCompiler argumentCompiler) {
379 super(category, mdx, 1);
380 this.compiler = argumentCompiler;
381 }
382
383 public String compile(Exp exp) {
384 String[] args = compileArgs(exp, compiler);
385 if (args == null) {
386 return null;
387 }
388 return "(" + args[0] + " is null" + ")";
389 }
390
391 public String toString() {
392 return "IsEmptySqlCompiler[" + mdx + "]";
393 }
394 }
395
396 /**
397 * compiles an <code>IIF(cond, val1, val2)</code>
398 * expression into SQL <code>CASE WHEN cond THEN val1 ELSE val2 END</code>
399 *
400 * @author av
401 * @since Nov 23, 2005
402 */
403 class IifSqlCompiler extends FunCallSqlCompilerBase {
404
405 SqlCompiler valueCompiler;
406
407 IifSqlCompiler(int category, SqlCompiler valueCompiler) {
408 super(category, "iif", 3);
409 this.valueCompiler = valueCompiler;
410 }
411
412 public String compile(Exp exp) {
413 if (!match(exp)) {
414 return null;
415 }
416 Exp[] args = ((FunCall) exp).getArgs();
417 String cond = booleanCompiler.compile(args[0]);
418 String val1 = valueCompiler.compile(args[1]);
419 String val2 = valueCompiler.compile(args[2]);
420 if (cond == null || val1 == null || val2 == null) {
421 return null;
422 }
423 return sqlQuery.getDialect().caseWhenElse(cond, val1, val2);
424 }
425
426 }
427
428 /**
429 * creates a new instance
430 * @param sqlQuery the query which is needed for differen SQL dialects - its not modified.
431 */
432 RolapNativeSql(SqlQuery sqlQuery) {
433 this.sqlQuery = sqlQuery;
434 this.dialect = sqlQuery.getDialect();
435
436 numericCompiler = new CompositeSqlCompiler();
437 booleanCompiler = new CompositeSqlCompiler();
438
439 numericCompiler.add(new NumberSqlCompiler());
440 numericCompiler.add(new StoredMeasureSqlCompiler());
441 numericCompiler.add(new CalculatedMemberSqlCompiler(numericCompiler));
442 numericCompiler.add(new ParenthesisSqlCompiler(Category.Numeric, numericCompiler));
443 numericCompiler.add(new InfixOpSqlCompiler(Category.Numeric, "+", "+", numericCompiler));
444 numericCompiler.add(new InfixOpSqlCompiler(Category.Numeric, "-", "-", numericCompiler));
445 numericCompiler.add(new InfixOpSqlCompiler(Category.Numeric, "/", "/", numericCompiler));
446 numericCompiler.add(new InfixOpSqlCompiler(Category.Numeric, "*", "*", numericCompiler));
447 numericCompiler.add(new IifSqlCompiler(Category.Numeric, numericCompiler));
448
449 booleanCompiler.add(new InfixOpSqlCompiler(Category.Logical, "<", "<", numericCompiler));
450 booleanCompiler.add(new InfixOpSqlCompiler(Category.Logical, "<=", "<=", numericCompiler));
451 booleanCompiler.add(new InfixOpSqlCompiler(Category.Logical, ">", ">", numericCompiler));
452 booleanCompiler.add(new InfixOpSqlCompiler(Category.Logical, ">=", ">=", numericCompiler));
453 booleanCompiler.add(new InfixOpSqlCompiler(Category.Logical, "=", "=", numericCompiler));
454 booleanCompiler.add(new InfixOpSqlCompiler(Category.Logical, "<>", "<>", numericCompiler));
455 booleanCompiler.add(new IsEmptySqlCompiler(Category.Logical, "IsEmpty", numericCompiler));
456
457 booleanCompiler.add(new InfixOpSqlCompiler(Category.Logical, "and", "AND", booleanCompiler));
458 booleanCompiler.add(new InfixOpSqlCompiler(Category.Logical, "or", "OR", booleanCompiler));
459 booleanCompiler.add(new UnaryOpSqlCompiler(Category.Logical, "not", "NOT", booleanCompiler));
460 booleanCompiler.add(new ParenthesisSqlCompiler(Category.Logical, booleanCompiler));
461 booleanCompiler.add(new IifSqlCompiler(Category.Logical, booleanCompiler));
462 }
463
464 /**
465 * generates an aggregate of a measure, e.g. "sum(Store_Sales)" for TopCount. The
466 * returned expr will be added to the select list and to the order by clause.
467 */
468 public String generateTopCountOrderBy(Exp exp) {
469 return numericCompiler.compile(exp);
470 }
471
472 public String generateFilterCondition(Exp exp) {
473 return booleanCompiler.compile(exp);
474 }
475
476 public RolapStoredMeasure getStoredMeasure() {
477 return storedMeasure;
478 }
479
480 }
481
482 // End RolapNativeSql.java