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) 2008-2013 Pentaho 008// All Rights Reserved. 009*/ 010package mondrian.spi.impl; 011 012import mondrian.olap.MondrianProperties; 013import mondrian.olap.Util; 014import mondrian.rolap.SqlStatement; 015import mondrian.spi.Dialect; 016import mondrian.spi.Dialect.DatabaseProduct; 017import mondrian.spi.StatisticsProvider; 018import mondrian.util.ClassResolver; 019 020import org.apache.commons.logging.Log; 021import org.apache.commons.logging.LogFactory; 022 023import java.sql.*; 024import java.sql.Date; 025import java.util.*; 026 027/** 028 * Implementation of {@link Dialect} based on a JDBC connection and metadata. 029 * 030 * <p>If you are writing a class for a specific database dialect, we recommend 031 * that you use this as a base class, so your dialect class will be 032 * forwards-compatible. If methods are added to {@link Dialect} in future 033 * revisions, default implementations of those methods will be added to this 034 * class.</p> 035 * 036 * <p>Mondrian uses JdbcDialectImpl as a fallback if it cannot find a more 037 * specific dialect. JdbcDialectImpl reads properties from the JDBC driver's 038 * metadata, so can deduce some of the dialect's behavior.</p> 039 * 040 * @author jhyde 041 * @since Oct 10, 2008 042 */ 043public class JdbcDialectImpl implements Dialect { 044 private static final Log LOGGER = LogFactory.getLog(JdbcDialectImpl.class); 045 046 /** 047 * String used to quote identifiers. 048 */ 049 private final String quoteIdentifierString; 050 051 /** 052 * Product name per JDBC driver. 053 */ 054 private final String productName; 055 056 /** 057 * Product version per JDBC driver. 058 */ 059 protected final String productVersion; 060 061 /** 062 * Supported result set types. 063 */ 064 private final Set<List<Integer>> supportedResultSetTypes; 065 066 /** 067 * Whether database is read-only 068 */ 069 private final boolean readOnly; 070 071 /** 072 * Maximum column name length 073 */ 074 private final int maxColumnNameLength; 075 076 /** 077 * Indicates whether the database allows selection of columns 078 * not listed in the group by clause. 079 */ 080 protected boolean permitsSelectNotInGroupBy; 081 082 /** 083 * Major database product (or null if product is not a common one) 084 */ 085 protected final DatabaseProduct databaseProduct; 086 087 /** 088 * List of statistics providers. 089 */ 090 private final List<StatisticsProvider> statisticsProviders; 091 092 private static final int[] RESULT_SET_TYPE_VALUES = { 093 ResultSet.TYPE_FORWARD_ONLY, 094 ResultSet.TYPE_SCROLL_INSENSITIVE, 095 ResultSet.TYPE_SCROLL_SENSITIVE}; 096 097 private static final int[] CONCURRENCY_VALUES = { 098 ResultSet.CONCUR_READ_ONLY, 099 ResultSet.CONCUR_UPDATABLE}; 100 101 /** 102 * The size required to add quotes around a string - this ought to be 103 * large enough to prevent a reallocation. 104 */ 105 private static final int SINGLE_QUOTE_SIZE = 10; 106 /** 107 * Two strings are quoted and the character '.' is placed between them. 108 */ 109 private static final int DOUBLE_QUOTE_SIZE = 2 * SINGLE_QUOTE_SIZE + 1; 110 111 /** 112 * The default mapping of java.sql.Types to SqlStatement.Type 113 */ 114 private static final Map<Types, SqlStatement.Type> DEFAULT_TYPE_MAP; 115 static { 116 Map typeMapInitial = new HashMap<Types, SqlStatement.Type>(); 117 typeMapInitial.put(Types.SMALLINT, SqlStatement.Type.INT); 118 typeMapInitial.put(Types.INTEGER, SqlStatement.Type.INT); 119 typeMapInitial.put(Types.BOOLEAN, SqlStatement.Type.INT); 120 typeMapInitial.put(Types.DOUBLE, SqlStatement.Type.DOUBLE); 121 typeMapInitial.put(Types.FLOAT, SqlStatement.Type.DOUBLE); 122 typeMapInitial.put(Types.BIGINT, SqlStatement.Type.DOUBLE); 123 124 DEFAULT_TYPE_MAP = Collections.unmodifiableMap(typeMapInitial); 125 } 126 127 /** 128 * Creates a JdbcDialectImpl. 129 * 130 * <p>To prevent connection leaks, this constructor does not hold a 131 * reference to the connection after the call returns. It makes a copy of 132 * everything useful during the call. Derived classes must do the 133 * same.</p> 134 * 135 * @param connection Connection 136 * 137 * @throws java.sql.SQLException on error 138 */ 139 public JdbcDialectImpl( 140 Connection connection) 141 throws SQLException 142 { 143 final DatabaseMetaData metaData = connection.getMetaData(); 144 this.quoteIdentifierString = deduceIdentifierQuoteString(metaData); 145 this.productName = deduceProductName(metaData); 146 this.productVersion = deduceProductVersion(metaData); 147 this.supportedResultSetTypes = deduceSupportedResultSetStyles(metaData); 148 this.readOnly = deduceReadOnly(metaData); 149 this.maxColumnNameLength = deduceMaxColumnNameLength(metaData); 150 this.databaseProduct = 151 getProduct(this.productName, this.productVersion); 152 this.permitsSelectNotInGroupBy = 153 deduceSupportsSelectNotInGroupBy(connection); 154 this.statisticsProviders = computeStatisticsProviders(); 155 } 156 157 public JdbcDialectImpl() { 158 quoteIdentifierString = ""; 159 productName = ""; 160 productVersion = ""; 161 supportedResultSetTypes = null; 162 readOnly = true; 163 maxColumnNameLength = 0; 164 databaseProduct = null; 165 permitsSelectNotInGroupBy = true; 166 statisticsProviders = null; 167 } 168 169 public DatabaseProduct getDatabaseProduct() { 170 return databaseProduct; 171 } 172 173 public void appendHintsAfterFromClause( 174 StringBuilder buf, 175 Map<String, String> hints) 176 { 177 // Hints are always dialect-specific, so the default is a no-op 178 } 179 180 public boolean allowsDialectSharing() { 181 return true; 182 } 183 184 protected int deduceMaxColumnNameLength(DatabaseMetaData databaseMetaData) { 185 try { 186 return databaseMetaData.getMaxColumnNameLength(); 187 } catch (SQLException e) { 188 throw Util.newInternal( 189 e, 190 "while detecting maxColumnNameLength"); 191 } 192 } 193 194 protected boolean deduceReadOnly(DatabaseMetaData databaseMetaData) { 195 try { 196 return databaseMetaData.isReadOnly(); 197 } catch (SQLException e) { 198 throw Util.newInternal( 199 e, 200 "while detecting isReadOnly"); 201 } 202 } 203 204 protected String deduceProductName(DatabaseMetaData databaseMetaData) { 205 try { 206 return databaseMetaData.getDatabaseProductName(); 207 } catch (SQLException e) { 208 throw Util.newInternal(e, "while detecting database product"); 209 } 210 } 211 212 protected String deduceIdentifierQuoteString( 213 DatabaseMetaData databaseMetaData) 214 { 215 try { 216 final String quoteIdentifierString = 217 databaseMetaData.getIdentifierQuoteString(); 218 return "".equals(quoteIdentifierString) 219 // quoting not supported 220 ? null 221 : quoteIdentifierString; 222 } catch (SQLException e) { 223 throw Util.newInternal(e, "while quoting identifier"); 224 } 225 } 226 227 protected String deduceProductVersion(DatabaseMetaData databaseMetaData) { 228 String productVersion; 229 try { 230 productVersion = databaseMetaData.getDatabaseProductVersion(); 231 } catch (SQLException e11) { 232 throw Util.newInternal( 233 e11, 234 "while detecting database product version"); 235 } 236 return productVersion; 237 } 238 239 protected Set<List<Integer>> deduceSupportedResultSetStyles( 240 DatabaseMetaData databaseMetaData) 241 { 242 Set<List<Integer>> supports = new HashSet<List<Integer>>(); 243 for (int type : RESULT_SET_TYPE_VALUES) { 244 for (int concurrency : CONCURRENCY_VALUES) { 245 try { 246 if (databaseMetaData.supportsResultSetConcurrency( 247 type, concurrency)) 248 { 249 String driverName = 250 databaseMetaData.getDriverName(); 251 if (type != ResultSet.TYPE_FORWARD_ONLY 252 && driverName.equals( 253 "JDBC-ODBC Bridge (odbcjt32.dll)")) 254 { 255 // In JDK 1.6, the Jdbc-Odbc bridge announces 256 // that it can handle TYPE_SCROLL_INSENSITIVE 257 // but it does so by generating a 'COUNT(*)' 258 // query, and this query is invalid if the query 259 // contains a single-quote. So, override the 260 // driver. 261 continue; 262 } 263 supports.add( 264 new ArrayList<Integer>( 265 Arrays.asList(type, concurrency))); 266 } 267 } catch (SQLException e) { 268 // DB2 throws "com.ibm.db2.jcc.b.SqlException: Unknown type 269 // or Concurrency" for certain values of type/concurrency. 270 // No harm in interpreting all such exceptions as 'this 271 // database does not support this type/concurrency 272 // combination'. 273 Util.discard(e); 274 } 275 } 276 } 277 return supports; 278 } 279 280 /** 281 * <p>Detects whether the database is configured to permit queries 282 * that include columns in the SELECT that are not also in the GROUP BY. 283 * MySQL is an example of one that does, though this is configurable.</p> 284 * 285 * <p>The expectation is that this will not change while Mondrian is 286 * running, though some databases (MySQL) allow changing it on the fly.</p> 287 * 288 * @param conn The database connection 289 * @return Whether the feature is enabled. 290 * @throws SQLException on error 291 */ 292 protected boolean deduceSupportsSelectNotInGroupBy(Connection conn) 293 throws SQLException 294 { 295 // Most simply don't support it 296 return false; 297 } 298 299 public String toUpper(String expr) { 300 return "UPPER(" + expr + ")"; 301 } 302 303 public String caseWhenElse(String cond, String thenExpr, String elseExpr) { 304 return "CASE WHEN " + cond + " THEN " + thenExpr + " ELSE " + elseExpr 305 + " END"; 306 } 307 308 public String quoteIdentifier(final String val) { 309 int size = val.length() + SINGLE_QUOTE_SIZE; 310 StringBuilder buf = new StringBuilder(size); 311 312 quoteIdentifier(val, buf); 313 314 return buf.toString(); 315 } 316 317 public void quoteIdentifier(final String val, final StringBuilder buf) { 318 String q = getQuoteIdentifierString(); 319 if (q == null) { 320 // quoting is not supported 321 buf.append(val); 322 return; 323 } 324 // if the value is already quoted, do nothing 325 // if not, then check for a dot qualified expression 326 // like "owner.table". 327 // In that case, prefix the single parts separately. 328 if (val.startsWith(q) && val.endsWith(q)) { 329 // already quoted - nothing to do 330 buf.append(val); 331 return; 332 } 333 334 int k = val.indexOf('.'); 335 if (k > 0) { 336 // qualified 337 String val1 = Util.replace(val.substring(0, k), q, q + q); 338 String val2 = Util.replace(val.substring(k + 1), q, q + q); 339 buf.append(q); 340 buf.append(val1); 341 buf.append(q); 342 buf.append("."); 343 buf.append(q); 344 buf.append(val2); 345 buf.append(q); 346 347 } else { 348 // not Qualified 349 String val2 = Util.replace(val, q, q + q); 350 buf.append(q); 351 buf.append(val2); 352 buf.append(q); 353 } 354 } 355 356 public String quoteIdentifier(final String qual, final String name) { 357 // We know if the qalifier is null, then only the name is going 358 // to be quoted. 359 int size = name.length() 360 + ((qual == null) 361 ? SINGLE_QUOTE_SIZE 362 : (qual.length() + DOUBLE_QUOTE_SIZE)); 363 StringBuilder buf = new StringBuilder(size); 364 365 quoteIdentifier(buf, qual, name); 366 367 return buf.toString(); 368 } 369 370 public void quoteIdentifier( 371 final StringBuilder buf, 372 final String... names) 373 { 374 int nonNullNameCount = 0; 375 for (String name : names) { 376 if (name == null) { 377 continue; 378 } 379 if (nonNullNameCount > 0) { 380 buf.append('.'); 381 } 382 assert name.length() > 0 383 : "name should probably be null, not empty"; 384 quoteIdentifier(name, buf); 385 ++nonNullNameCount; 386 } 387 } 388 389 public String getQuoteIdentifierString() { 390 return quoteIdentifierString; 391 } 392 393 public void quoteStringLiteral( 394 StringBuilder buf, 395 String s) 396 { 397 Util.singleQuoteString(s, buf); 398 } 399 400 public void quoteNumericLiteral( 401 StringBuilder buf, 402 String value) 403 { 404 buf.append(value); 405 } 406 407 public void quoteBooleanLiteral(StringBuilder buf, String value) { 408 // NOTE jvs 1-Jan-2007: See quoteDateLiteral for explanation. 409 // In addition, note that we leave out UNKNOWN (even though 410 // it is a valid SQL:2003 literal) because it's really 411 // NULL in disguise, and NULL is always treated specially. 412 if (!value.equalsIgnoreCase("TRUE") 413 && !(value.equalsIgnoreCase("FALSE"))) 414 { 415 throw new NumberFormatException( 416 "Illegal BOOLEAN literal: " + value); 417 } 418 buf.append(value); 419 } 420 421 public void quoteDateLiteral(StringBuilder buf, String value) { 422 // NOTE jvs 1-Jan-2007: Check that the supplied literal is in valid 423 // SQL:2003 date format. A hack in 424 // RolapSchemaReader.lookupMemberChildByName looks for 425 // NumberFormatException to suppress it, so that is why 426 // we convert the exception here. 427 final Date date; 428 try { 429 date = Date.valueOf(value); 430 } catch (IllegalArgumentException ex) { 431 throw new NumberFormatException( 432 "Illegal DATE literal: " + value); 433 } 434 quoteDateLiteral(buf, value, date); 435 } 436 437 /** 438 * Helper method for {@link #quoteDateLiteral(StringBuilder, String)}. 439 * 440 * @param buf Buffer to append to 441 * @param value Value as string 442 * @param date Value as date 443 */ 444 protected void quoteDateLiteral( 445 StringBuilder buf, 446 String value, 447 Date date) 448 { 449 // SQL:2003 date format: DATE '2008-01-23'. 450 buf.append("DATE "); 451 Util.singleQuoteString(value, buf); 452 } 453 454 public void quoteTimeLiteral(StringBuilder buf, String value) { 455 // NOTE jvs 1-Jan-2007: See quoteDateLiteral for explanation. 456 try { 457 Time.valueOf(value); 458 } catch (IllegalArgumentException ex) { 459 throw new NumberFormatException( 460 "Illegal TIME literal: " + value); 461 } 462 buf.append("TIME "); 463 Util.singleQuoteString(value, buf); 464 } 465 466 public void quoteTimestampLiteral( 467 StringBuilder buf, 468 String value) 469 { 470 // NOTE jvs 1-Jan-2007: See quoteTimestampLiteral for explanation. 471 try { 472 Timestamp.valueOf(value); 473 } catch (IllegalArgumentException ex) { 474 throw new NumberFormatException( 475 "Illegal TIMESTAMP literal: " + value); 476 } 477 buf.append("TIMESTAMP "); 478 Util.singleQuoteString(value, buf); 479 } 480 481 public boolean requiresAliasForFromQuery() { 482 return false; 483 } 484 485 public boolean allowsAs() { 486 return true; 487 } 488 489 public boolean allowsFromQuery() { 490 return true; 491 } 492 493 public boolean allowsCompoundCountDistinct() { 494 return false; 495 } 496 497 public boolean allowsCountDistinct() { 498 return true; 499 } 500 501 public boolean allowsMultipleCountDistinct() { 502 return allowsCountDistinct(); 503 } 504 505 public boolean allowsMultipleDistinctSqlMeasures() { 506 return allowsMultipleCountDistinct(); 507 } 508 509 public String generateInline( 510 List<String> columnNames, 511 List<String> columnTypes, 512 List<String[]> valueList) 513 { 514 return generateInlineForAnsi( 515 "t", columnNames, columnTypes, valueList, false); 516 } 517 518 /** 519 * Generic algorithm to generate inline values list, 520 * using an optional FROM clause, specified by the caller of this 521 * method, appropriate to the dialect of SQL. 522 * 523 * @param columnNames Column names 524 * @param columnTypes Column types 525 * @param valueList List rows 526 * @param fromClause FROM clause, or null 527 * @param cast Whether to cast the values in the first row 528 * @return Expression that returns the given values 529 */ 530 protected String generateInlineGeneric( 531 List<String> columnNames, 532 List<String> columnTypes, 533 List<String[]> valueList, 534 String fromClause, 535 boolean cast) 536 { 537 final StringBuilder buf = new StringBuilder(); 538 int columnCount = columnNames.size(); 539 assert columnTypes.size() == columnCount; 540 541 // Some databases, e.g. Teradata, derives datatype from value of column 542 // in first row, and truncates subsequent rows. Therefore, we need to 543 // cast every value to the correct length. Figure out the maximum length 544 // now. 545 Integer[] maxLengths = new Integer[columnCount]; 546 if (cast) { 547 for (int i = 0; i < columnTypes.size(); i++) { 548 String columnType = columnTypes.get(i); 549 Datatype datatype = Datatype.valueOf(columnType); 550 if (datatype == Datatype.String) { 551 int maxLen = -1; 552 for (String[] strings : valueList) { 553 if (strings[i] != null 554 && strings[i].length() > maxLen) 555 { 556 maxLen = strings[i].length(); 557 } 558 } 559 maxLengths[i] = maxLen; 560 } 561 } 562 } 563 564 for (int i = 0; i < valueList.size(); i++) { 565 if (i > 0) { 566 buf.append(" union all "); 567 } 568 String[] values = valueList.get(i); 569 buf.append("select "); 570 for (int j = 0; j < values.length; j++) { 571 String value = values[j]; 572 if (j > 0) { 573 buf.append(", "); 574 } 575 final String columnType = columnTypes.get(j); 576 final String columnName = columnNames.get(j); 577 Datatype datatype = Datatype.valueOf(columnType); 578 final Integer maxLength = maxLengths[j]; 579 if (maxLength != null) { 580 // Generate CAST for Teradata. 581 buf.append("CAST("); 582 quote(buf, value, datatype); 583 buf.append(" AS VARCHAR(").append(maxLength).append("))"); 584 } else { 585 quote(buf, value, datatype); 586 } 587 if (allowsAs()) { 588 buf.append(" as "); 589 } else { 590 buf.append(' '); 591 } 592 quoteIdentifier(columnName, buf); 593 } 594 if (fromClause != null) { 595 buf.append(fromClause); 596 } 597 } 598 return buf.toString(); 599 } 600 601 /** 602 * Generates inline values list using ANSI 'VALUES' syntax. 603 * For example, 604 * 605 * <blockquote><code>SELECT * FROM 606 * (VALUES (1, 'a'), (2, 'b')) AS t(x, y)</code></blockquote> 607 * 608 * <p>If NULL values are present, we use a CAST to ensure that they 609 * have the same type as other columns: 610 * 611 * <blockquote><code>SELECT * FROM 612 * (VALUES (1, 'a'), (2, CASE(NULL AS VARCHAR(1)))) AS t(x, y) 613 * </code></blockquote> 614 * 615 * <p>This syntax is known to work on Derby, but not Oracle 10 or 616 * Access. 617 * 618 * @param alias Table alias 619 * @param columnNames Column names 620 * @param columnTypes Column types 621 * @param valueList List rows 622 * @param cast Whether to generate casts 623 * @return Expression that returns the given values 624 */ 625 public String generateInlineForAnsi( 626 String alias, 627 List<String> columnNames, 628 List<String> columnTypes, 629 List<String[]> valueList, 630 boolean cast) 631 { 632 final StringBuilder buf = new StringBuilder(); 633 buf.append("SELECT * FROM (VALUES "); 634 // Derby pads out strings to a common length, so we cast the 635 // string values to avoid this. Determine the cast type for each 636 // column. 637 String[] castTypes = null; 638 if (cast) { 639 castTypes = new String[columnNames.size()]; 640 for (int i = 0; i < columnNames.size(); i++) { 641 String columnType = columnTypes.get(i); 642 if (columnType.equals("String")) { 643 castTypes[i] = 644 guessSqlType(columnType, valueList, i); 645 } 646 } 647 } 648 for (int i = 0; i < valueList.size(); i++) { 649 if (i > 0) { 650 buf.append(", "); 651 } 652 String[] values = valueList.get(i); 653 buf.append("("); 654 for (int j = 0; j < values.length; j++) { 655 String value = values[j]; 656 if (j > 0) { 657 buf.append(", "); 658 } 659 final String columnType = columnTypes.get(j); 660 Datatype datatype = Datatype.valueOf(columnType); 661 if (value == null) { 662 String sqlType = 663 guessSqlType(columnType, valueList, j); 664 buf.append("CAST(NULL AS ") 665 .append(sqlType) 666 .append(")"); 667 } else if (cast && castTypes[j] != null) { 668 buf.append("CAST("); 669 quote(buf, value, datatype); 670 buf.append(" AS ") 671 .append(castTypes[j]) 672 .append(")"); 673 } else { 674 quote(buf, value, datatype); 675 } 676 } 677 buf.append(")"); 678 } 679 buf.append(") AS "); 680 quoteIdentifier(alias, buf); 681 buf.append(" ("); 682 for (int j = 0; j < columnNames.size(); j++) { 683 final String columnName = columnNames.get(j); 684 if (j > 0) { 685 buf.append(", "); 686 } 687 quoteIdentifier(columnName, buf); 688 } 689 buf.append(")"); 690 return buf.toString(); 691 } 692 693 public boolean needsExponent(Object value, String valueString) { 694 return false; 695 } 696 697 public void quote( 698 StringBuilder buf, 699 Object value, 700 Datatype datatype) 701 { 702 if (value == null) { 703 buf.append("null"); 704 } else { 705 String valueString = value.toString(); 706 if (needsExponent(value, valueString)) { 707 valueString += "E0"; 708 } 709 datatype.quoteValue(buf, this, valueString); 710 } 711 } 712 713 /** 714 * Guesses the type of a column based upon (a) its basic type, 715 * (b) a list of values. 716 * 717 * @param basicType Basic type 718 * @param valueList Value list 719 * @param column Column ordinal 720 * @return SQL type 721 */ 722 private static String guessSqlType( 723 String basicType, 724 List<String[]> valueList, 725 int column) 726 { 727 if (basicType.equals("String")) { 728 int maxLen = 1; 729 for (String[] values : valueList) { 730 final String value = values[column]; 731 if (value == null) { 732 continue; 733 } 734 maxLen = Math.max(maxLen, value.length()); 735 } 736 return "VARCHAR(" + maxLen + ")"; 737 } else { 738 return "INTEGER"; 739 } 740 } 741 742 public boolean allowsDdl() { 743 return !readOnly; 744 } 745 746 public String generateOrderItem( 747 String expr, 748 boolean nullable, 749 boolean ascending, 750 boolean collateNullsLast) 751 { 752 if (nullable) { 753 return generateOrderByNulls(expr, ascending, collateNullsLast); 754 } else { 755 if (ascending) { 756 return expr + " ASC"; 757 } else { 758 return expr + " DESC"; 759 } 760 } 761 } 762 763 /** 764 * Generates SQL to force null values to collate last. 765 * 766 * <p>This default implementation makes use of the ANSI 767 * SQL 1999 CASE-WHEN-THEN-ELSE in conjunction with IS NULL 768 * syntax. The resulting SQL will look something like this: 769 * 770 * <p><code>CASE WHEN "expr" IS NULL THEN 0 ELSE 1 END</code> 771 * 772 * <p>You can override this method for a particular database 773 * to use something more efficient, like ISNULL(). 774 * 775 * <p>ANSI SQL provides the syntax "ASC/DESC NULLS LAST" and 776 * "ASC/DESC NULLS FIRST". If your database supports the ANSI 777 * syntax, implement this method by calling 778 * {@link #generateOrderByNullsAnsi}. 779 * 780 * <p>This method is only called from 781 * {@link #generateOrderItem(String, boolean, boolean, boolean)}. 782 * Some dialects override that method and therefore never call 783 * this method. 784 * 785 * @param expr Expression. 786 * @param ascending Whether ascending. 787 * @param collateNullsLast Whether nulls should appear first or last. 788 * @return Expression to force null values to collate last or first. 789 */ 790 protected String generateOrderByNulls( 791 String expr, 792 boolean ascending, 793 boolean collateNullsLast) 794 { 795 if (collateNullsLast) { 796 if (ascending) { 797 return 798 "CASE WHEN " + expr + " IS NULL THEN 1 ELSE 0 END, " + expr 799 + " ASC"; 800 } else { 801 return 802 "CASE WHEN " + expr + " IS NULL THEN 1 ELSE 0 END, " + expr 803 + " DESC"; 804 } 805 } else { 806 if (ascending) { 807 return 808 "CASE WHEN " + expr + " IS NULL THEN 0 ELSE 1 END, " + expr 809 + " ASC"; 810 } else { 811 return 812 "CASE WHEN " + expr + " IS NULL THEN 0 ELSE 1 END, " + expr 813 + " DESC"; 814 } 815 } 816 } 817 818 /** 819 * Implementation for the {@link #generateOrderByNulls} method 820 * that uses the ANSI syntax "expr direction NULLS LAST" 821 * and "expr direction NULLS FIRST". 822 * 823 * @param expr Expression 824 * @param ascending Whether ascending 825 * @param collateNullsLast Whether nulls should appear first or last. 826 * @return Expression "expr direction NULLS LAST" 827 */ 828 protected final String generateOrderByNullsAnsi( 829 String expr, 830 boolean ascending, 831 boolean collateNullsLast) 832 { 833 if (collateNullsLast) { 834 return expr + (ascending ? " ASC" : " DESC") + " NULLS LAST"; 835 } else { 836 return expr + (ascending ? " ASC" : " DESC") + " NULLS FIRST"; 837 } 838 } 839 840 public boolean supportsGroupByExpressions() { 841 return true; 842 } 843 844 public boolean allowsSelectNotInGroupBy() { 845 return permitsSelectNotInGroupBy; 846 } 847 848 public boolean allowsJoinOn() { 849 return false; 850 } 851 852 public boolean supportsGroupingSets() { 853 return false; 854 } 855 856 public boolean supportsUnlimitedValueList() { 857 return false; 858 } 859 860 public boolean requiresGroupByAlias() { 861 return false; 862 } 863 864 public boolean requiresOrderByAlias() { 865 return false; 866 } 867 868 public boolean requiresHavingAlias() { 869 return false; 870 } 871 872 public boolean allowsOrderByAlias() { 873 return requiresOrderByAlias(); 874 } 875 876 public boolean requiresUnionOrderByOrdinal() { 877 return true; 878 } 879 880 public boolean requiresUnionOrderByExprToBeInSelectClause() { 881 return true; 882 } 883 884 public boolean supportsMultiValueInExpr() { 885 return false; 886 } 887 888 public boolean supportsResultSetConcurrency( 889 int type, 890 int concurrency) 891 { 892 return supportedResultSetTypes.contains( 893 Arrays.asList(type, concurrency)); 894 } 895 896 public String toString() { 897 return productName; 898 } 899 900 public int getMaxColumnNameLength() { 901 return maxColumnNameLength; 902 } 903 904 public boolean allowsRegularExpressionInWhereClause() { 905 return false; 906 } 907 908 public String generateCountExpression(String exp) { 909 return exp; 910 } 911 912 public String generateRegularExpression( 913 String source, 914 String javaRegExp) 915 { 916 return null; 917 } 918 919 public List<StatisticsProvider> getStatisticsProviders() { 920 return statisticsProviders; 921 } 922 923 public SqlStatement.Type getType( 924 ResultSetMetaData metaData, int columnIndex) 925 throws SQLException 926 { 927 final int columnType = metaData.getColumnType(columnIndex + 1); 928 929 SqlStatement.Type internalType = null; 930 if (columnType != Types.NUMERIC && columnType != Types.DECIMAL) { 931 internalType = DEFAULT_TYPE_MAP.get(columnType); 932 } else { 933 final int precision = metaData.getPrecision(columnIndex + 1); 934 final int scale = metaData.getScale(columnIndex + 1); 935 if (scale == 0 && precision <= 9) { 936 // An int (up to 2^31 = 2.1B) can hold any NUMBER(10, 0) value 937 // (up to 10^9 = 1B). 938 internalType = SqlStatement.Type.INT; 939 } else { 940 internalType = SqlStatement.Type.DOUBLE; 941 } 942 } 943 internalType = internalType == null ? SqlStatement.Type.OBJECT 944 : internalType; 945 logTypeInfo(metaData, columnIndex, internalType); 946 return internalType; 947 } 948 949 950 void logTypeInfo( 951 ResultSetMetaData metaData, int columnIndex, 952 SqlStatement.Type internalType) 953 throws SQLException 954 { 955 if (LOGGER.isDebugEnabled()) { 956 final int columnType = metaData.getColumnType(columnIndex + 1); 957 final int precision = metaData.getPrecision(columnIndex + 1); 958 final int scale = metaData.getScale(columnIndex + 1); 959 final String columnName = metaData.getColumnName(columnIndex + 1); 960 LOGGER.debug( 961 "JdbcDialectImpl.getType " 962 + "Dialect- " + this.getDatabaseProduct() 963 + ", Column-" 964 + columnName 965 + " is of internal type " 966 + internalType 967 + ". JDBC type was " 968 + columnType 969 + ". Column precision=" + precision 970 + ". Column scale=" + scale); 971 } 972 } 973 974 protected List<StatisticsProvider> computeStatisticsProviders() { 975 List<String> names = getStatisticsProviderNames(); 976 if (names == null) { 977 return Collections.<StatisticsProvider>singletonList( 978 new SqlStatisticsProvider()); 979 } 980 final List<StatisticsProvider> providerList = 981 new ArrayList<StatisticsProvider>(); 982 for (String name : names) { 983 try { 984 StatisticsProvider provider = 985 ClassResolver.INSTANCE.instantiateSafe(name); 986 providerList.add(provider); 987 } catch (Exception e) { 988 LOGGER.info( 989 "Error instantiating statistics provider (class=" + name 990 + ")", 991 e); 992 } 993 } 994 return providerList; 995 } 996 997 private List<String> getStatisticsProviderNames() { 998 // Dialect-specific path, e.g. "mondrian.statistics.providers.MYSQL" 999 final String path = 1000 MondrianProperties.instance().StatisticsProviders.getPath() 1001 + "." 1002 + getDatabaseProduct().name(); 1003 String nameList = MondrianProperties.instance().getProperty(path); 1004 if (nameList != null && nameList.length() > 0) { 1005 return Arrays.asList(nameList.split(",")); 1006 } 1007 1008 // Generic property, "mondrian.statistics.providers" 1009 nameList = MondrianProperties.instance().StatisticsProviders.get(); 1010 if (nameList != null && nameList.length() > 0) { 1011 return Arrays.asList(nameList.split(",")); 1012 } 1013 return null; 1014 } 1015 1016 /** 1017 * Converts a product name and version (per the JDBC driver) into a product 1018 * enumeration. 1019 * 1020 * @param productName Product name 1021 * @param productVersion Product version 1022 * @return database product 1023 */ 1024 public static DatabaseProduct getProduct( 1025 String productName, 1026 String productVersion) 1027 { 1028 final String upperProductName = productName.toUpperCase(); 1029 if (productName.equals("ACCESS")) { 1030 return DatabaseProduct.ACCESS; 1031 } else if (upperProductName.trim().equals("APACHE DERBY")) { 1032 return DatabaseProduct.DERBY; 1033 } else if (upperProductName.trim().equals("DBMS:CLOUDSCAPE")) { 1034 return DatabaseProduct.DERBY; 1035 } else if (productName.startsWith("DB2")) { 1036 if (productName.startsWith("DB2 UDB for AS/400")) { 1037 // TB "04.03.0000 V4R3m0" 1038 // this version cannot handle subqueries and is considered "old" 1039 // DEUKA "05.01.0000 V5R1m0" is ok 1040 String[] version_release = productVersion.split("\\.", 3); 1041/* 1042 if (version_release.length > 2 && 1043 "04".compareTo(version_release[0]) > 0 || 1044 ("04".compareTo(version_release[0]) == 0 1045 && "03".compareTo(version_release[1]) >= 0)) 1046 return true; 1047*/ 1048 // assume, that version <= 04 is "old" 1049 if ("04".compareTo(version_release[0]) >= 0) { 1050 return DatabaseProduct.DB2_OLD_AS400; 1051 } else { 1052 return DatabaseProduct.DB2_AS400; 1053 } 1054 } else { 1055 // DB2 on NT returns "DB2/NT" 1056 return DatabaseProduct.DB2; 1057 } 1058 } else if (upperProductName.indexOf("FIREBIRD") >= 0) { 1059 return DatabaseProduct.FIREBIRD; 1060 } else if (productName.equals("Hive")) { 1061 return DatabaseProduct.HIVE; 1062 } else if (productName.startsWith("Informix")) { 1063 return DatabaseProduct.INFORMIX; 1064 } else if (upperProductName.equals("INGRES")) { 1065 return DatabaseProduct.INGRES; 1066 } else if (productName.equals("Interbase")) { 1067 return DatabaseProduct.INTERBASE; 1068 } else if (upperProductName.equals("LUCIDDB") 1069 || upperProductName.equals("OPTIQ")) 1070 { 1071 return DatabaseProduct.LUCIDDB; 1072 } else if (upperProductName.indexOf("SQL SERVER") >= 0) { 1073 return DatabaseProduct.MSSQL; 1074 } else if (productName.equals("Oracle")) { 1075 return DatabaseProduct.ORACLE; 1076 } else if (upperProductName.indexOf("POSTGRE") >= 0) { 1077 return DatabaseProduct.POSTGRESQL; 1078 } else if (upperProductName.indexOf("NETEZZA") >= 0) { 1079 return DatabaseProduct.NETEZZA; 1080 } else if (upperProductName.equals("MYSQL (INFOBRIGHT)")) { 1081 return DatabaseProduct.INFOBRIGHT; 1082 } else if (upperProductName.equals("MYSQL")) { 1083 return DatabaseProduct.MYSQL; 1084 } else if (upperProductName.equals("MONETDB")) { 1085 return DatabaseProduct.MONETDB; 1086 } else if (upperProductName.equals("VERTICA") 1087 || upperProductName.equals("VERTICA DATABASE")) 1088 { 1089 return DatabaseProduct.VERTICA; 1090 } else if (upperProductName.equals("VECTORWISE")) { 1091 return DatabaseProduct.VECTORWISE; 1092 } else if (productName.startsWith("HP Neoview")) { 1093 return DatabaseProduct.NEOVIEW; 1094 } else if (upperProductName.indexOf("SYBASE") >= 0 1095 || upperProductName.indexOf("ADAPTIVE SERVER") >= 0) 1096 { 1097 // Sysbase Adaptive Server Enterprise 15.5 via jConnect 6.05 returns 1098 // "Adaptive Server Enterprise" as a product name. 1099 return DatabaseProduct.SYBASE; 1100 } else if (upperProductName.indexOf("TERADATA") >= 0) { 1101 return DatabaseProduct.TERADATA; 1102 } else if (upperProductName.indexOf("HSQL") >= 0) { 1103 return DatabaseProduct.HSQLDB; 1104 } else if (upperProductName.indexOf("VERTICA") >= 0) { 1105 return DatabaseProduct.VERTICA; 1106 } else if (upperProductName.indexOf("VECTORWISE") >= 0) { 1107 return DatabaseProduct.VECTORWISE; 1108 } else { 1109 return DatabaseProduct.UNKNOWN; 1110 } 1111 } 1112 1113 /** 1114 * Helper method to determine if a connection would work with 1115 * a given database product. This can be used to differenciate 1116 * between databases which use the same driver as others. 1117 * 1118 * <p>It will first try to use 1119 * {@link DatabaseMetaData#getDatabaseProductName()} and match the 1120 * name of {@link DatabaseProduct} passed as an argument. 1121 * 1122 * <p>If that fails, it will try to execute <code>select version();</code> 1123 * and obtains some information directly from the server. 1124 * 1125 * @param databaseProduct Database product instance 1126 * @param connection SQL connection 1127 * @return true if a match was found. false otherwise. 1128 */ 1129 protected static boolean isDatabase( 1130 DatabaseProduct databaseProduct, 1131 Connection connection) 1132 { 1133 Statement statement = null; 1134 ResultSet resultSet = null; 1135 1136 String dbProduct = databaseProduct.name().toLowerCase(); 1137 1138 try { 1139 // Quick and dirty check first. 1140 if (connection.getMetaData().getDatabaseProductName() 1141 .toLowerCase().contains(dbProduct)) 1142 { 1143 LOGGER.debug("Using " + databaseProduct.name() + " dialect"); 1144 return true; 1145 } 1146 1147 // Let's try using version(). 1148 statement = connection.createStatement(); 1149 resultSet = statement.executeQuery("select version()"); 1150 if (resultSet.next()) { 1151 String version = resultSet.getString(1); 1152 LOGGER.debug("Version=" + version); 1153 if (version != null) { 1154 if (version.toLowerCase().contains(dbProduct)) { 1155 LOGGER.info( 1156 "Using " + databaseProduct.name() + " dialect"); 1157 return true; 1158 } 1159 } 1160 } 1161 LOGGER.debug("NOT Using " + databaseProduct.name() + " dialect"); 1162 return false; 1163 } catch (SQLException e) { 1164 LOGGER.debug("NOT Using " + databaseProduct.name() + " dialect.", e); 1165 return false; 1166 } finally { 1167 Util.close(resultSet, statement, null); 1168 } 1169 } 1170} 1171 1172// End JdbcDialectImpl.java