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