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) 2002-2005 Julian Hyde 008// Copyright (C) 2005-2012 Pentaho and others 009// All Rights Reserved. 010// 011// jhyde, Mar 21, 2002 012*/ 013package mondrian.rolap.sql; 014 015import mondrian.olap.*; 016import mondrian.rolap.*; 017import mondrian.spi.Dialect; 018import mondrian.spi.DialectManager; 019import mondrian.util.Pair; 020 021import java.util.*; 022import javax.sql.DataSource; 023 024/** 025 * <code>SqlQuery</code> allows us to build a <code>select</code> 026 * statement and generate it in database-specific SQL syntax. 027 * 028 * <p> Notable differences in database syntax are:<dl> 029 * 030 * <dt> Identifier quoting </dt> 031 * <dd> Oracle (and all JDBC-compliant drivers) uses double-quotes, 032 * for example <code>select * from "emp"</code>. Access prefers brackets, 033 * for example <code>select * from [emp]</code>. mySQL allows single- and 034 * double-quotes for string literals, and therefore does not allow 035 * identifiers to be quoted, for example <code>select 'foo', "bar" from 036 * emp</code>. </dd> 037 * 038 * <dt> AS in from clause </dt> 039 * <dd> Oracle doesn't like AS in the from * clause, for example 040 * <code>select from emp as e</code> vs. <code>select * from emp 041 * e</code>. </dd> 042 * 043 * <dt> Column aliases </dt> 044 * <dd> Some databases require that every column in the select list 045 * has a valid alias. If the expression is an expression containing 046 * non-alphanumeric characters, an explicit alias is needed. For example, 047 * Oracle will barfs at <code>select empno + 1 from emp</code>. </dd> 048 * 049 * <dt> Parentheses around table names </dt> 050 * <dd> Oracle doesn't like <code>select * from (emp)</code> </dd> 051 * 052 * <dt> Queries in FROM clause </dt> 053 * <dd> PostgreSQL and hsqldb don't allow, for example, <code>select * from 054 * (select * from emp) as e</code>.</dd> 055 * 056 * <dt> Uniqueness of index names </dt> 057 * <dd> In PostgreSQL and Oracle, index names must be unique within the 058 * database; in Access and hsqldb, they must merely be unique within their 059 * table </dd> 060 * 061 * <dt> Datatypes </dt> 062 * <dd> In Oracle, BIT is CHAR(1), TIMESTAMP is DATE. 063 * In PostgreSQL, DOUBLE is DOUBLE PRECISION, BIT is BOOL. </dd> 064 * </ul> 065 * 066 * <p> 067 * NOTE: Instances of this class are NOT thread safe so the user must make 068 * sure this is accessed by only one thread at a time. 069 * 070 * @author jhyde 071 */ 072public class SqlQuery { 073 /** Controls the formatting of the sql string. */ 074 private final boolean generateFormattedSql; 075 076 private boolean distinct; 077 078 private final ClauseList select; 079 private final FromClauseList from; 080 private final ClauseList where; 081 private final ClauseList groupBy; 082 private final ClauseList having; 083 private final ClauseList orderBy; 084 private final List<ClauseList> groupingSets; 085 private final ClauseList groupingFunctions; 086 087 private final List<SqlStatement.Type> types = 088 new ArrayList<SqlStatement.Type>(); 089 090 /** Controls whether table optimization hints are used */ 091 private boolean allowHints; 092 093 /** 094 * This list is used to keep track of what aliases have been used in the 095 * FROM clause. One might think that a java.util.Set would be a more 096 * appropriate Collection type, but if you only have a couple of "from 097 * aliases", then iterating over a list is faster than doing a hash lookup 098 * (as is used in java.util.HashSet). 099 */ 100 private final List<String> fromAliases; 101 102 /** The SQL dialect this query is to be generated in. */ 103 private final Dialect dialect; 104 105 /** Scratch buffer. Clear it before use. */ 106 private final StringBuilder buf; 107 108 private final Set<MondrianDef.Relation> relations = 109 new HashSet<MondrianDef.Relation>(); 110 111 private final Map<MondrianDef.Relation, MondrianDef.RelationOrJoin> 112 mapRelationToRoot = 113 new HashMap<MondrianDef.Relation, MondrianDef.RelationOrJoin>(); 114 115 private final Map<MondrianDef.RelationOrJoin, List<RelInfo>> 116 mapRootToRelations = 117 new HashMap<MondrianDef.RelationOrJoin, List<RelInfo>>(); 118 119 private final Map<String, String> columnAliases = 120 new HashMap<String, String>(); 121 122 private static final String INDENT = " "; 123 124 /** 125 * Base constructor used by all other constructors to create an empty 126 * instance. 127 * 128 * @param dialect Dialect 129 * @param formatted Whether to generate SQL formatted on multiple lines 130 */ 131 public SqlQuery(Dialect dialect, boolean formatted) { 132 assert dialect != null; 133 this.generateFormattedSql = formatted; 134 135 // both select and from allow duplications 136 this.select = new ClauseList(true); 137 this.from = new FromClauseList(true); 138 139 this.groupingFunctions = new ClauseList(false); 140 this.where = new ClauseList(false); 141 this.groupBy = new ClauseList(false); 142 this.having = new ClauseList(false); 143 this.orderBy = new ClauseList(false); 144 this.fromAliases = new ArrayList<String>(); 145 this.buf = new StringBuilder(128); 146 this.groupingSets = new ArrayList<ClauseList>(); 147 this.dialect = dialect; 148 149 // REVIEW emcdermid 10-Jul-2009: It might be okay to allow 150 // hints in all cases, but for initial implementation this 151 // allows us to them on selectively in specific situations. 152 // Usage will likely expand with experimentation. 153 this.allowHints = false; 154 } 155 156 /** 157 * Creates a SqlQuery using a given dialect and inheriting the formatting 158 * preferences from {@link MondrianProperties#GenerateFormattedSql} 159 * property. 160 * 161 * @param dialect Dialect 162 */ 163 public SqlQuery(Dialect dialect) { 164 this( 165 dialect, 166 MondrianProperties.instance().GenerateFormattedSql.get()); 167 } 168 169 /** 170 * Creates an empty <code>SqlQuery</code> with the same environment as this 171 * one. (As per the Gang of Four 'prototype' pattern.) 172 */ 173 public SqlQuery cloneEmpty() 174 { 175 return new SqlQuery(dialect); 176 } 177 178 public void setDistinct(final boolean distinct) { 179 this.distinct = distinct; 180 } 181 182 /** 183 * Chooses whether table optimization hints may be used 184 * (assuming the dialect supports it). 185 * 186 * @param t True to allow hints to be used, false otherwise 187 */ 188 public void setAllowHints(boolean t) { 189 this.allowHints = t; 190 } 191 192 /** 193 * Adds a subquery to the FROM clause of this Query with a given alias. 194 * If the query already exists it either, depending on 195 * <code>failIfExists</code>, throws an exception or does not add the query 196 * and returns false. 197 * 198 * @param query Subquery 199 * @param alias (if not null, must not be zero length). 200 * @param failIfExists if true, throws exception if alias already exists 201 * @return true if query *was* added 202 * 203 * @pre alias != null 204 */ 205 public boolean addFromQuery( 206 final String query, 207 final String alias, 208 final boolean failIfExists) 209 { 210 assert alias != null; 211 assert alias.length() > 0; 212 213 if (fromAliases.contains(alias)) { 214 if (failIfExists) { 215 throw Util.newInternal( 216 "query already contains alias '" + alias + "'"); 217 } else { 218 return false; 219 } 220 } 221 222 buf.setLength(0); 223 224 buf.append('('); 225 buf.append(query); 226 buf.append(')'); 227 if (dialect.allowsAs()) { 228 buf.append(" as "); 229 } else { 230 buf.append(' '); 231 } 232 dialect.quoteIdentifier(alias, buf); 233 fromAliases.add(alias); 234 235 from.add(buf.toString()); 236 return true; 237 } 238 239 /** 240 * Adds <code>[schema.]table AS alias</code> to the FROM clause. 241 * 242 * @param schema schema name; may be null 243 * @param name table name 244 * @param alias table alias, may not be null 245 * (if not null, must not be zero length). 246 * @param filter Extra filter condition, or null 247 * @param hints table optimization hints, if any 248 * @param failIfExists Whether to throw a RuntimeException if from clause 249 * already contains this alias 250 * 251 * @pre alias != null 252 * @return true if table was added 253 */ 254 boolean addFromTable( 255 final String schema, 256 final String name, 257 final String alias, 258 final String filter, 259 final Map hints, 260 final boolean failIfExists) 261 { 262 if (fromAliases.contains(alias)) { 263 if (failIfExists) { 264 throw Util.newInternal( 265 "query already contains alias '" + alias + "'"); 266 } else { 267 return false; 268 } 269 } 270 271 buf.setLength(0); 272 dialect.quoteIdentifier(buf, schema, name); 273 if (alias != null) { 274 Util.assertTrue(alias.length() > 0); 275 276 if (dialect.allowsAs()) { 277 buf.append(" as "); 278 } else { 279 buf.append(' '); 280 } 281 dialect.quoteIdentifier(alias, buf); 282 fromAliases.add(alias); 283 } 284 285 if (this.allowHints) { 286 dialect.appendHintsAfterFromClause(buf, hints); 287 } 288 289 from.add(buf.toString()); 290 291 if (filter != null) { 292 // append filter condition to where clause 293 addWhere("(", filter, ")"); 294 } 295 return true; 296 } 297 298 public void addFrom( 299 final SqlQuery sqlQuery, 300 final String alias, 301 final boolean failIfExists) 302 { 303 addFromQuery(sqlQuery.toString(), alias, failIfExists); 304 } 305 306 /** 307 * Adds a relation to a query, adding appropriate join conditions, unless 308 * it is already present. 309 * 310 * <p>Returns whether the relation was added to the query. 311 * 312 * @param relation Relation to add 313 * @param alias Alias of relation. If null, uses relation's alias. 314 * @param failIfExists Whether to fail if relation is already present 315 * @return true, if relation *was* added to query 316 */ 317 public boolean addFrom( 318 final MondrianDef.RelationOrJoin relation, 319 final String alias, 320 final boolean failIfExists) 321 { 322 registerRootRelation(relation); 323 324 if (relation instanceof MondrianDef.Relation) { 325 MondrianDef.Relation relation1 = (MondrianDef.Relation) relation; 326 if (relations.add(relation1) 327 && !MondrianProperties.instance() 328 .FilterChildlessSnowflakeMembers.get()) 329 { 330 // This relation is new to this query. Add a join to any other 331 // relation in the same dimension. 332 // 333 // (If FilterChildlessSnowflakeMembers were false, 334 // this would be unnecessary. Adding a relation automatically 335 // adds all relations between it and the fact table.) 336 MondrianDef.RelationOrJoin root = 337 mapRelationToRoot.get(relation1); 338 List<MondrianDef.Relation> relationsCopy = 339 new ArrayList<MondrianDef.Relation>(relations); 340 for (MondrianDef.Relation relation2 : relationsCopy) { 341 if (relation2 != relation1 342 && mapRelationToRoot.get(relation2) == root) 343 { 344 addJoinBetween(root, relation1, relation2); 345 } 346 } 347 } 348 } 349 350 if (relation instanceof MondrianDef.View) { 351 final MondrianDef.View view = (MondrianDef.View) relation; 352 final String viewAlias = 353 (alias == null) 354 ? view.getAlias() 355 : alias; 356 final String sqlString = view.getCodeSet().chooseQuery(dialect); 357 return addFromQuery(sqlString, viewAlias, false); 358 359 } else if (relation instanceof MondrianDef.InlineTable) { 360 final MondrianDef.Relation relation1 = 361 RolapUtil.convertInlineTableToRelation( 362 (MondrianDef.InlineTable) relation, dialect); 363 return addFrom(relation1, alias, failIfExists); 364 365 } else if (relation instanceof MondrianDef.Table) { 366 final MondrianDef.Table table = (MondrianDef.Table) relation; 367 final String tableAlias = 368 (alias == null) 369 ? table.getAlias() 370 : alias; 371 return addFromTable( 372 table.schema, 373 table.name, 374 tableAlias, 375 table.getFilter(), 376 table.getHintMap(), 377 failIfExists); 378 379 } else if (relation instanceof MondrianDef.Join) { 380 final MondrianDef.Join join = (MondrianDef.Join) relation; 381 return addJoin( 382 join.left, 383 join.getLeftAlias(), 384 join.leftKey, 385 join.right, 386 join.getRightAlias(), 387 join.rightKey, 388 failIfExists); 389 } else { 390 throw Util.newInternal("bad relation type " + relation); 391 } 392 } 393 394 private boolean addJoin( 395 MondrianDef.RelationOrJoin left, 396 String leftAlias, 397 String leftKey, 398 MondrianDef.RelationOrJoin right, 399 String rightAlias, 400 String rightKey, 401 boolean failIfExists) 402 { 403 boolean addLeft = addFrom(left, leftAlias, failIfExists); 404 boolean addRight = addFrom(right, rightAlias, failIfExists); 405 406 boolean added = addLeft || addRight; 407 if (added) { 408 buf.setLength(0); 409 410 dialect.quoteIdentifier(buf, leftAlias, leftKey); 411 buf.append(" = "); 412 dialect.quoteIdentifier(buf, rightAlias, rightKey); 413 final String condition = buf.toString(); 414 if (dialect.allowsJoinOn()) { 415 from.addOn( 416 leftAlias, leftKey, rightAlias, rightKey, 417 condition); 418 } else { 419 addWhere(condition); 420 } 421 } 422 return added; 423 } 424 425 private void addJoinBetween( 426 MondrianDef.RelationOrJoin root, 427 MondrianDef.Relation relation1, 428 MondrianDef.Relation relation2) 429 { 430 List<RelInfo> relations = mapRootToRelations.get(root); 431 int index1 = find(relations, relation1); 432 int index2 = find(relations, relation2); 433 assert index1 != -1; 434 assert index2 != -1; 435 int min = Math.min(index1, index2); 436 int max = Math.max(index1, index2); 437 for (int i = max - 1; i >= min; i--) { 438 RelInfo relInfo = relations.get(i); 439 addJoin( 440 relInfo.relation, 441 relInfo.leftAlias != null 442 ? relInfo.leftAlias 443 : relInfo.relation.getAlias(), 444 relInfo.leftKey, 445 relations.get(i + 1).relation, 446 relInfo.rightAlias != null 447 ? relInfo.rightAlias 448 : relations.get(i + 1).relation.getAlias(), 449 relInfo.rightKey, 450 false); 451 } 452 } 453 454 private int find(List<RelInfo> relations, MondrianDef.Relation relation) { 455 for (int i = 0, n = relations.size(); i < n; i++) { 456 RelInfo relInfo = relations.get(i); 457 if (relInfo.relation.equals(relation)) { 458 return i; 459 } 460 } 461 return -1; 462 } 463 464 /** 465 * Adds an expression to the select clause, automatically creating a 466 * column alias. 467 */ 468 public String addSelect(final String expression, SqlStatement.Type type) { 469 // Some DB2 versions (AS/400) throw an error if a column alias is 470 // *not* used in a subsequent order by (Group by). 471 // Derby fails on 'SELECT... HAVING' if column has alias. 472 switch (dialect.getDatabaseProduct()) { 473 case DB2_AS400: 474 case DERBY: 475 return addSelect(expression, type, null); 476 default: 477 return addSelect(expression, type, nextColumnAlias()); 478 } 479 } 480 481 /** 482 * Adds an expression to the SELECT and GROUP BY clauses. Uses the alias in 483 * the GROUP BY clause, if the dialect requires it. 484 * 485 * @param expression Expression 486 * @return Alias of expression 487 */ 488 public String addSelectGroupBy( 489 final String expression, 490 SqlStatement.Type type) 491 { 492 final String alias = addSelect(expression, type); 493 addGroupBy(expression, alias); 494 return alias; 495 } 496 497 public int getCurrentSelectListSize() 498 { 499 return select.size(); 500 } 501 502 public String nextColumnAlias() { 503 return "c" + select.size(); 504 } 505 506 /** 507 * Adds an expression to the select clause, with a specified type and 508 * column alias. 509 * 510 * @param expression Expression 511 * @param type Java type to be used to hold cursor column 512 * @param alias Column alias (or null for no alias) 513 * @return Column alias 514 */ 515 public String addSelect( 516 final String expression, 517 final SqlStatement.Type type, 518 String alias) 519 { 520 buf.setLength(0); 521 522 buf.append(expression); 523 if (alias != null) { 524 buf.append(" as "); 525 dialect.quoteIdentifier(alias, buf); 526 } 527 528 select.add(buf.toString()); 529 addType(type); 530 columnAliases.put(expression, alias); 531 return alias; 532 } 533 534 public String getAlias(String expression) { 535 return columnAliases.get(expression); 536 } 537 538 public void addWhere( 539 final String exprLeft, 540 final String exprMid, 541 final String exprRight) 542 { 543 int len = exprLeft.length() + exprMid.length() + exprRight.length(); 544 StringBuilder buf = new StringBuilder(len); 545 546 buf.append(exprLeft); 547 buf.append(exprMid); 548 buf.append(exprRight); 549 550 addWhere(buf.toString()); 551 } 552 553 public void addWhere(RolapStar.Condition joinCondition) { 554 String left = joinCondition.getLeft().getTableAlias(); 555 String right = joinCondition.getRight().getTableAlias(); 556 if (fromAliases.contains(left) && fromAliases.contains(right)) { 557 addWhere( 558 joinCondition.getLeft(this), 559 " = ", 560 joinCondition.getRight(this)); 561 } 562 } 563 564 public void addWhere(final String expression) 565 { 566 assert expression != null && !expression.equals(""); 567 where.add(expression); 568 } 569 570 public void addGroupBy(final String expression) 571 { 572 assert expression != null && !expression.equals(""); 573 groupBy.add(expression); 574 } 575 576 public void addGroupBy(final String expression, final String alias) { 577 if (dialect.requiresGroupByAlias()) { 578 addGroupBy(dialect.quoteIdentifier(alias)); 579 } else { 580 addGroupBy(expression); 581 } 582 } 583 584 public void addHaving(final String expression) 585 { 586 assert expression != null && !expression.equals(""); 587 having.add(expression); 588 } 589 590 /** 591 * Adds an item to the ORDER BY clause. 592 * 593 * @param expr the expr to order by 594 * @param ascending sort direction 595 * @param prepend whether to prepend to the current list of items 596 * @param nullable whether the expression might be null 597 */ 598 public void addOrderBy( 599 String expr, 600 boolean ascending, 601 boolean prepend, 602 boolean nullable) 603 { 604 this.addOrderBy(expr, ascending, prepend, nullable, true); 605 } 606 607 /** 608 * Adds an item to the ORDER BY clause. 609 * 610 * @param expr the expr to order by 611 * @param ascending sort direction 612 * @param prepend whether to prepend to the current list of items 613 * @param nullable whether the expression might be null 614 * @param collateNullsLast whether null values should appear first or last. 615 */ 616 public void addOrderBy( 617 String expr, 618 boolean ascending, 619 boolean prepend, 620 boolean nullable, 621 boolean collateNullsLast) 622 { 623 String orderExpr = 624 dialect.generateOrderItem( 625 expr, 626 nullable, 627 ascending, 628 collateNullsLast); 629 if (prepend) { 630 orderBy.add(0, orderExpr); 631 } else { 632 orderBy.add(orderExpr); 633 } 634 } 635 636 public String toString() 637 { 638 buf.setLength(0); 639 toBuffer(buf, ""); 640 return buf.toString(); 641 } 642 643 /** 644 * Writes this SqlQuery to a StringBuilder with each clause on a separate 645 * line, and with the specified indentation prefix. 646 * 647 * @param buf String builder 648 * @param prefix Prefix for each line 649 */ 650 public void toBuffer(StringBuilder buf, String prefix) { 651 final String first = distinct ? "select distinct " : "select "; 652 select.toBuffer(buf, generateFormattedSql, prefix, first, ", ", "", ""); 653 groupingFunctionsToBuffer(buf, prefix); 654 from.toBuffer( 655 buf, generateFormattedSql, prefix, " from ", ", ", "", ""); 656 where.toBuffer( 657 buf, generateFormattedSql, prefix, " where ", " and ", "", ""); 658 if (groupingSets.isEmpty()) { 659 groupBy.toBuffer( 660 buf, generateFormattedSql, prefix, " group by ", ", ", "", ""); 661 } else { 662 ClauseList.listToBuffer( 663 buf, 664 groupingSets, 665 generateFormattedSql, 666 prefix, 667 " group by grouping sets (", 668 ", ", 669 ")"); 670 } 671 having.toBuffer( 672 buf, generateFormattedSql, prefix, " having ", " and ", "", ""); 673 orderBy.toBuffer( 674 buf, generateFormattedSql, prefix, " order by ", ", ", "", ""); 675 } 676 677 private void groupingFunctionsToBuffer(StringBuilder buf, String prefix) { 678 if (groupingSets.isEmpty()) { 679 return; 680 } 681 int n = 0; 682 for (String groupingFunction : groupingFunctions) { 683 if (generateFormattedSql) { 684 buf.append(",") 685 .append(Util.nl) 686 .append(INDENT) 687 .append(prefix); 688 } else { 689 buf.append(", "); 690 } 691 buf.append("grouping(") 692 .append(groupingFunction) 693 .append(") as "); 694 dialect.quoteIdentifier("g" + n++, buf); 695 } 696 } 697 698 public Dialect getDialect() { 699 return dialect; 700 } 701 702 public static SqlQuery newQuery(DataSource dataSource, String err) { 703 final Dialect dialect = 704 DialectManager.createDialect(dataSource, null); 705 return new SqlQuery(dialect); 706 } 707 708 public void addGroupingSet(List<String> groupingColumnsExpr) { 709 ClauseList groupingList = new ClauseList(false); 710 for (String columnExp : groupingColumnsExpr) { 711 groupingList.add(columnExp); 712 } 713 groupingSets.add(groupingList); 714 } 715 716 public void addGroupingFunction(String columnExpr) { 717 groupingFunctions.add(columnExpr); 718 719 // A grouping function will end up in the select clause implicitly. It 720 // needs a corresponding type. 721 types.add(null); 722 } 723 724 private void addType(SqlStatement.Type type) { 725 types.add(type); 726 } 727 728 public Pair<String, List<SqlStatement.Type>> toSqlAndTypes() { 729 assert types.size() == select.size() + groupingFunctions.size() 730 : types.size() + " types, " 731 + (select.size() + groupingFunctions.size()) 732 + " select items in query " + this; 733 return Pair.of(toString(), types); 734 } 735 736 public void registerRootRelation(MondrianDef.RelationOrJoin root) { 737 // REVIEW: In this method, we are building data structures about the 738 // structure of a star schema. These should be built into the schema, 739 // not constructed afresh for each SqlQuery. In mondrian-4.0, 740 // these methods and the data structures 'mapRootToRelations', 741 // 'relations', 'mapRelationToRoot' will disappear. 742 if (mapRelationToRoot.containsKey(root)) { 743 return; 744 } 745 if (mapRootToRelations.containsKey(root)) { 746 return; 747 } 748 List<RelInfo> relations = new ArrayList<RelInfo>(); 749 flatten(relations, root, null, null, null, null); 750 for (RelInfo relation : relations) { 751 mapRelationToRoot.put(relation.relation, root); 752 } 753 mapRootToRelations.put(root, relations); 754 } 755 756 private void flatten( 757 List<RelInfo> relations, 758 MondrianDef.RelationOrJoin root, 759 String leftKey, 760 String leftAlias, 761 String rightKey, 762 String rightAlias) 763 { 764 if (root instanceof MondrianDef.Join) { 765 MondrianDef.Join join = (MondrianDef.Join) root; 766 flatten( 767 relations, join.left, join.leftKey, join.leftAlias, 768 join.rightKey, join.rightAlias); 769 flatten( 770 relations, join.right, leftKey, leftAlias, rightKey, 771 rightAlias); 772 } else { 773 relations.add( 774 new RelInfo( 775 (MondrianDef.Relation) root, 776 leftKey, 777 leftAlias, 778 rightKey, 779 rightAlias)); 780 } 781 } 782 783 private static class JoinOnClause { 784 private final String condition; 785 private final String left; 786 private final String right; 787 788 JoinOnClause(String condition, String left, String right) { 789 this.condition = condition; 790 this.left = left; 791 this.right = right; 792 } 793 } 794 795 static class FromClauseList extends ClauseList { 796 private final List<JoinOnClause> joinOnClauses = 797 new ArrayList<JoinOnClause>(); 798 799 FromClauseList(boolean allowsDups) { 800 super(allowsDups); 801 } 802 803 public void addOn( 804 String leftAlias, 805 String leftKey, 806 String rightAlias, 807 String rightKey, 808 String condition) 809 { 810 if (leftAlias == null && rightAlias == null) { 811 // do nothing 812 } else if (leftAlias == null) { 813 // left is the form of 'Table'.'column' 814 leftAlias = rightAlias; 815 } else if (rightAlias == null) { 816 // Only left contains table name, Table.Column = abc 817 // store the same name for right tables 818 rightAlias = leftAlias; 819 } 820 joinOnClauses.add( 821 new JoinOnClause(condition, leftAlias, rightAlias)); 822 } 823 824 public void toBuffer(StringBuilder buf, List<String> fromAliases) { 825 int n = 0; 826 for (int i = 0; i < size(); i++) { 827 final String s = get(i); 828 final String alias = fromAliases.get(i); 829 if (n++ == 0) { 830 buf.append(" from "); 831 buf.append(s); 832 } else { 833 // Add "JOIN t ON (a = b ,...)" to the FROM clause. If there 834 // is no JOIN clause matching this alias (or no JOIN clauses 835 // at all), append just ", t". 836 appendJoin(fromAliases.subList(0, i), s, alias, buf); 837 } 838 } 839 } 840 841 void appendJoin( 842 final List<String> addedTables, 843 final String from, 844 final String alias, 845 final StringBuilder buf) 846 { 847 int n = 0; 848 // first check when the current table is on the left side 849 for (JoinOnClause joinOnClause : joinOnClauses) { 850 // the first table was added before join, it has to be handled 851 // specially: Table.column = expression 852 if ((addedTables.size() == 1 853 && addedTables.get(0).equals(joinOnClause.left) 854 && joinOnClause.left.equals(joinOnClause.right)) 855 || (alias.equals(joinOnClause.left) 856 && addedTables.contains(joinOnClause.right)) 857 || (alias.equals(joinOnClause.right) 858 && addedTables.contains(joinOnClause.left))) 859 { 860 if (n++ == 0) { 861 buf.append(" join ").append(from).append(" on "); 862 } else { 863 buf.append(" and "); 864 } 865 buf.append(joinOnClause.condition); 866 } 867 } 868 if (n == 0) { 869 // No "JOIN ... ON" clause matching this alias (or maybe no 870 // JOIN ... ON clauses at all, if this is a database that 871 // doesn't support ANSI-join syntax). Append an old-style FROM 872 // item separated by a comma. 873 buf.append(joinOnClauses.isEmpty() ? ", " : " cross join ") 874 .append(from); 875 } 876 } 877 } 878 879 static class ClauseList extends ArrayList<String> { 880 protected final boolean allowDups; 881 882 ClauseList(final boolean allowDups) { 883 this.allowDups = allowDups; 884 } 885 886 /** 887 * Adds an element to this ClauseList if either duplicates are allowed 888 * or if it has not already been added. 889 * 890 * @param element Element to add 891 * @return whether element was added, per 892 * {@link java.util.Collection#add(Object)} 893 */ 894 public boolean add(final String element) { 895 if (allowDups || !contains(element)) { 896 return super.add(element); 897 } 898 return false; 899 } 900 901 final void toBuffer( 902 StringBuilder buf, 903 boolean generateFormattedSql, 904 String prefix, 905 String first, 906 String sep, 907 String last, 908 String empty) 909 { 910 if (isEmpty()) { 911 buf.append(empty); 912 return; 913 } 914 first = foo(generateFormattedSql, prefix, first); 915 sep = foo(generateFormattedSql, prefix, sep); 916 toBuffer(buf, first, sep, last); 917 } 918 919 static String foo( 920 boolean generateFormattedSql, 921 String prefix, 922 String s) 923 { 924 if (generateFormattedSql) { 925 if (s.startsWith(" ")) { 926 // E.g. " and " 927 s = Util.nl + prefix + s.substring(1); 928 } 929 if (s.endsWith(" ")) { 930 // E.g. ", " 931 s = 932 s.substring(0, s.length() - 1) + Util.nl + prefix 933 + INDENT; 934 } else if (s.endsWith("(")) { 935 // E.g. "(" 936 s = s + Util.nl + prefix + INDENT; 937 } 938 } 939 return s; 940 } 941 942 final void toBuffer( 943 final StringBuilder buf, 944 final String first, 945 final String sep, 946 final String last) 947 { 948 int n = 0; 949 buf.append(first); 950 for (String s : this) { 951 if (n++ > 0) { 952 buf.append(sep); 953 } 954 buf.append(s); 955 } 956 buf.append(last); 957 } 958 959 static void listToBuffer( 960 StringBuilder buf, 961 List<ClauseList> clauseListList, 962 boolean generateFormattedSql, 963 String prefix, 964 String first, 965 String sep, 966 String last) 967 { 968 first = foo(generateFormattedSql, prefix, first); 969 sep = foo(generateFormattedSql, prefix, sep); 970 buf.append(first); 971 int n = 0; 972 for (ClauseList clauseList : clauseListList) { 973 if (n++ > 0) { 974 buf.append(sep); 975 } 976 clauseList.toBuffer( 977 buf, false, prefix, "(", ", ", ")", "()"); 978 } 979 buf.append(last); 980 } 981 } 982 983 /** 984 * Collection of alternative code for alternative dialects. 985 */ 986 public static class CodeSet { 987 private final Map<String, String> dialectCodes = 988 new HashMap<String, String>(); 989 990 public String put(String dialect, String code) { 991 return dialectCodes.put(dialect, code); 992 } 993 994 /** 995 * Chooses the code variant which best matches the given Dialect. 996 */ 997 public String chooseQuery(Dialect dialect) { 998 String best = getBestName(dialect); 999 String bestCode = dialectCodes.get(best); 1000 if (bestCode != null) { 1001 return bestCode; 1002 } 1003 String genericCode = dialectCodes.get("generic"); 1004 if (genericCode == null) { 1005 throw Util.newError("View has no 'generic' variant"); 1006 } 1007 return genericCode; 1008 } 1009 1010 private static String getBestName(Dialect dialect) { 1011 return dialect.getDatabaseProduct().getFamily().name() 1012 .toLowerCase(); 1013 } 1014 } 1015 1016 private static class RelInfo { 1017 final MondrianDef.Relation relation; 1018 final String leftKey; 1019 final String leftAlias; 1020 final String rightKey; 1021 final String rightAlias; 1022 1023 public RelInfo( 1024 MondrianDef.Relation relation, 1025 String leftKey, 1026 String leftAlias, 1027 String rightKey, 1028 String rightAlias) 1029 { 1030 this.relation = relation; 1031 this.leftKey = leftKey; 1032 this.leftAlias = leftAlias; 1033 this.rightKey = rightKey; 1034 this.rightAlias = rightAlias; 1035 } 1036 } 1037} 1038 1039// End SqlQuery.java