001 /* 002 // $Id: //open/mondrian/src/main/mondrian/rolap/sql/SqlQuery.java#96 $ 003 // This software is subject to the terms of the Common Public License 004 // Agreement, available at the following URL: 005 // http://www.opensource.org/licenses/cpl.html. 006 // Copyright (C) 2002-2002 Kana Software, Inc. 007 // Copyright (C) 2002-2008 Julian Hyde and others 008 // All Rights Reserved. 009 // You must accept the terms of that agreement to use this software. 010 // 011 // jhyde, Mar 21, 2002 012 */ 013 014 package mondrian.rolap.sql; 015 016 import mondrian.olap.MondrianDef; 017 import mondrian.olap.MondrianProperties; 018 import mondrian.olap.Util; 019 import mondrian.rolap.RolapUtil; 020 021 import javax.sql.DataSource; 022 import java.io.PrintWriter; 023 import java.io.StringWriter; 024 import java.sql.*; 025 import java.util.*; 026 027 /** 028 * <code>SqlQuery</code> allows us to build a <code>select</code> 029 * statement and generate it in database-specific SQL syntax. 030 * 031 * <p> Notable differences in database syntax are:<dl> 032 * 033 * <dt> Identifier quoting </dt> 034 * <dd> Oracle (and all JDBC-compliant drivers) uses double-quotes, 035 * for example <code>select * from "emp"</code>. Access prefers brackets, 036 * for example <code>select * from [emp]</code>. mySQL allows single- and 037 * double-quotes for string literals, and therefore does not allow 038 * identifiers to be quoted, for example <code>select 'foo', "bar" from 039 * emp</code>. </dd> 040 * 041 * <dt> AS in from clause </dt> 042 * <dd> Oracle doesn't like AS in the from * clause, for example 043 * <code>select from emp as e</code> vs. <code>select * from emp 044 * e</code>. </dd> 045 * 046 * <dt> Column aliases </dt> 047 * <dd> Some databases require that every column in the select list 048 * has a valid alias. If the expression is an expression containing 049 * non-alphanumeric characters, an explicit alias is needed. For example, 050 * Oracle will barfs at <code>select empno + 1 from emp</code>. </dd> 051 * 052 * <dt> Parentheses around table names </dt> 053 * <dd> Oracle doesn't like <code>select * from (emp)</code> </dd> 054 * 055 * <dt> Queries in FROM clause </dt> 056 * <dd> PostgreSQL and hsqldb don't allow, for example, <code>select * from 057 * (select * from emp) as e</code>.</dd> 058 * 059 * <dt> Uniqueness of index names </dt> 060 * <dd> In PostgreSQL and Oracle, index names must be unique within the 061 * database; in Access and hsqldb, they must merely be unique within their 062 * table </dd> 063 * 064 * <dt> Datatypes </dt> 065 * <dd> In Oracle, BIT is CHAR(1), TIMESTAMP is DATE. 066 * In PostgreSQL, DOUBLE is DOUBLE PRECISION, BIT is BOOL. </dd> 067 * </ul> 068 * 069 * <p> 070 * NOTE: Instances of this class are NOT thread safe so the user must make 071 * sure this is accessed by only one thread at a time. 072 * 073 * @author jhyde 074 * @version $Id: //open/mondrian/src/main/mondrian/rolap/sql/SqlQuery.java#96 $ 075 */ 076 public class SqlQuery { 077 /** Controls the formatting of the sql string. */ 078 private final boolean generateFormattedSql; 079 080 private boolean distinct; 081 082 private final ClauseList select; 083 private final ClauseList from; 084 private final ClauseList where; 085 private final ClauseList groupBy; 086 private final ClauseList having; 087 private final ClauseList orderBy; 088 private final List<ClauseList> groupingSet; 089 private final ClauseList groupingFunction; 090 091 /** 092 * This list is used to keep track of what aliases have been used in the 093 * FROM clause. One might think that a java.util.Set would be a more 094 * appropriate Collection type, but if you only have a couple of "from 095 * aliases", then iterating over a list is faster than doing a hash lookup 096 * (as is used in java.util.HashSet). 097 */ 098 private final List<String> fromAliases; 099 100 /** The SQL dialect this query is to be generated in. */ 101 private final Dialect dialect; 102 103 /** Scratch buffer. Clear it before use. */ 104 private final StringBuilder buf; 105 106 /** 107 * Base constructor used by all other constructors to create an empty 108 * instance. 109 * 110 * @param dialect Dialect 111 * @param formatted Whether to generate SQL formatted on multiple lines 112 */ 113 public SqlQuery(Dialect dialect, boolean formatted) { 114 this.generateFormattedSql = formatted; 115 116 // both select and from allow duplications 117 this.select = new ClauseList(true); 118 this.from = new ClauseList(true); 119 120 this.groupingFunction = new ClauseList(false); 121 this.where = new ClauseList(false); 122 this.groupBy = new ClauseList(false); 123 this.having = new ClauseList(false); 124 this.orderBy = new ClauseList(false); 125 this.fromAliases = new ArrayList<String>(); 126 this.buf = new StringBuilder(128); 127 this.groupingSet = new ArrayList<ClauseList>(); 128 this.dialect = dialect; 129 } 130 131 /** 132 * Creates a SqlQuery using a given dialect and inheriting the formatting 133 * preferences from {@link MondrianProperties#GenerateFormattedSql} 134 * property. 135 * 136 * @param dialect Dialect 137 */ 138 public SqlQuery(Dialect dialect) { 139 this( 140 dialect, 141 MondrianProperties.instance().GenerateFormattedSql.get()); 142 } 143 144 /** 145 * Creates a <code>SqlQuery</code> 146 * 147 * @param databaseMetaData used to determine which dialect of 148 * SQL to generate. Must not be held beyond the constructor. 149 */ 150 public SqlQuery(final DatabaseMetaData databaseMetaData) { 151 this(Dialect.create(databaseMetaData)); 152 } 153 154 /** 155 * Creates an empty <code>SqlQuery</code> with the same environment as this 156 * one. (As per the Gang of Four 'prototype' pattern.) 157 */ 158 public SqlQuery cloneEmpty() 159 { 160 return new SqlQuery(dialect); 161 } 162 163 public void setDistinct(final boolean distinct) { 164 this.distinct = distinct; 165 } 166 167 /** 168 * The size required to add quotes around a string - this ought to be 169 * large enough to prevent a reallocation. 170 */ 171 private static final int SINGLE_QUOTE_SIZE = 10; 172 /** 173 * Two strings are quoted and the character '.' is placed between them. 174 */ 175 private static final int DOUBLE_QUOTE_SIZE = 2 * SINGLE_QUOTE_SIZE + 1; 176 177 /** 178 * Adds a subquery to the FROM clause of this Query with a given alias. 179 * If the query already exists it either, depending on 180 * <code>failIfExists</code>, throws an exception or does not add the query 181 * and returns false. 182 * 183 * @param query Subquery 184 * @param alias (if not null, must not be zero length). 185 * @param failIfExists if true, throws exception if alias already exists 186 * @return true if query *was* added 187 * 188 * @pre alias != null 189 */ 190 public boolean addFromQuery( 191 final String query, 192 final String alias, 193 final boolean failIfExists) 194 { 195 assert alias != null; 196 197 if (fromAliases.contains(alias)) { 198 if (failIfExists) { 199 throw Util.newInternal( 200 "query already contains alias '" + alias + "'"); 201 } else { 202 return false; 203 } 204 } 205 206 buf.setLength(0); 207 208 buf.append('('); 209 buf.append(query); 210 buf.append(')'); 211 if (alias != null) { 212 Util.assertTrue(alias.length() > 0); 213 214 if (dialect.allowsAs()) { 215 buf.append(" as "); 216 } else { 217 buf.append(' '); 218 } 219 dialect.quoteIdentifier(alias, buf); 220 fromAliases.add(alias); 221 } 222 223 from.add(buf.toString()); 224 return true; 225 } 226 227 /** 228 * Adds <code>[schema.]table AS alias</code> to the FROM clause. 229 * 230 * @param schema schema name; may be null 231 * @param table table name 232 * @param alias table alias, may not be null 233 * (if not null, must not be zero length). 234 * @param filter Extra filter condition, or null 235 * @param failIfExists Whether to throw a RuntimeException if from clause 236 * already contains this alias 237 * 238 * @pre alias != null 239 * @return true if table was added 240 */ 241 boolean addFromTable( 242 final String schema, 243 final String table, 244 final String alias, 245 final String filter, 246 final boolean failIfExists) 247 { 248 if (fromAliases.contains(alias)) { 249 if (failIfExists) { 250 throw Util.newInternal( 251 "query already contains alias '" + alias + "'"); 252 } else { 253 return false; 254 } 255 } 256 257 buf.setLength(0); 258 dialect.quoteIdentifier(buf, schema, table); 259 if (alias != null) { 260 Util.assertTrue(alias.length() > 0); 261 262 if (dialect.allowsAs()) { 263 buf.append(" as "); 264 } else { 265 buf.append(' '); 266 } 267 dialect.quoteIdentifier(alias, buf); 268 fromAliases.add(alias); 269 } 270 271 from.add(buf.toString()); 272 273 if (filter != null) { 274 // append filter condition to where clause 275 addWhere("(", filter, ")"); 276 } 277 return true; 278 } 279 280 public void addFrom(final SqlQuery sqlQuery, 281 final String alias, 282 final boolean failIfExists) 283 { 284 addFromQuery(sqlQuery.toString(), alias, failIfExists); 285 } 286 287 /** 288 * Adds a relation to a query, adding appropriate join conditions, unless 289 * it is already present. 290 * 291 * <p>Returns whether the relation was added to the query. 292 * 293 * @param relation Relation to add 294 * @param alias Alias of relation. If null, uses relation's alias. 295 * @param failIfExists Whether to fail if relation is already present 296 * @return true, if relation *was* added to query 297 */ 298 public boolean addFrom( 299 final MondrianDef.RelationOrJoin relation, 300 final String alias, 301 final boolean failIfExists) 302 { 303 if (relation instanceof MondrianDef.View) { 304 final MondrianDef.View view = (MondrianDef.View) relation; 305 final String viewAlias = (alias == null) 306 ? view.getAlias() 307 : alias; 308 final String sqlString = view.getCodeSet().chooseQuery(dialect); 309 return addFromQuery(sqlString, viewAlias, false); 310 311 } else if (relation instanceof MondrianDef.InlineTable) { 312 final MondrianDef.Relation relation1 = 313 RolapUtil.convertInlineTableToRelation( 314 (MondrianDef.InlineTable) relation, dialect); 315 return addFrom(relation1, alias, failIfExists); 316 317 } else if (relation instanceof MondrianDef.Table) { 318 final MondrianDef.Table table = (MondrianDef.Table) relation; 319 final String tableAlias = (alias == null) 320 ? table.getAlias() 321 : alias; 322 return addFromTable( 323 table.schema, table.name, tableAlias, 324 table.getFilter(), failIfExists); 325 326 } else if (relation instanceof MondrianDef.Join) { 327 final MondrianDef.Join join = (MondrianDef.Join) relation; 328 final String leftAlias = join.getLeftAlias(); 329 final String rightAlias = join.getRightAlias(); 330 331 boolean addLeft = addFrom(join.left, leftAlias, failIfExists); 332 boolean addRight = addFrom(join.right, rightAlias, failIfExists); 333 334 boolean added = addLeft || addRight; 335 if (added) { 336 buf.setLength(0); 337 338 dialect.quoteIdentifier(buf, leftAlias, join.leftKey); 339 buf.append(" = "); 340 dialect.quoteIdentifier(buf, rightAlias, join.rightKey); 341 342 addWhere(buf.toString()); 343 } 344 return added; 345 346 } else { 347 throw Util.newInternal("bad relation type " + relation); 348 } 349 } 350 351 /** 352 * Adds an expression to the select clause, automatically creating a 353 * column alias. 354 */ 355 public void addSelect(final String expression) { 356 // Some DB2 versions (AS/400) throw an error if a column alias is 357 // *not* used in a subsequent order by (Group by). 358 // Derby fails on 'SELECT... HAVING' if column has alias. 359 if (dialect.isAS400() || dialect.isDerby()) { 360 addSelect(expression, null); 361 } else { 362 addSelect(expression, nextColumnAlias()); 363 } 364 } 365 366 public int getCurrentSelectListSize() 367 { 368 return select.size(); 369 } 370 371 public String nextColumnAlias() { 372 return "c" + select.size(); 373 } 374 375 /** Adds an expression to the select clause, with a specified column 376 * alias. */ 377 public void addSelect(final String expression, final String alias) { 378 buf.setLength(0); 379 380 buf.append(expression); 381 if (alias != null) { 382 buf.append(" as "); 383 dialect.quoteIdentifier(alias, buf); 384 } 385 386 select.add(buf.toString()); 387 } 388 389 public void addWhere( 390 final String exprLeft, 391 final String exprMid, 392 final String exprRight) 393 { 394 int len = exprLeft.length() + exprMid.length() + exprRight.length(); 395 StringBuilder buf = new StringBuilder(len); 396 397 buf.append(exprLeft); 398 buf.append(exprMid); 399 buf.append(exprRight); 400 401 addWhere(buf.toString()); 402 } 403 404 public void addWhere(final String expression) 405 { 406 where.add(expression); 407 } 408 409 public void addGroupBy(final String expression) 410 { 411 groupBy.add(expression); 412 } 413 414 public void addHaving(final String expression) 415 { 416 having.add(expression); 417 } 418 419 /** 420 * Adds an item to the ORDER BY clause. 421 * 422 * @param expr the expr to order by 423 * @param ascending sort direction 424 * @param prepend whether to prepend to the current list of items 425 * @param nullable whether the expression might be null 426 */ 427 public void addOrderBy( 428 String expr, 429 boolean ascending, 430 boolean prepend, 431 boolean nullable) 432 { 433 if (nullable && !dialect.isNullsCollateLast()) { 434 expr = dialect.forceNullsCollateLast(expr); 435 } 436 437 if (ascending) { 438 expr = expr + " ASC"; 439 } else { 440 expr = expr + " DESC"; 441 } 442 if (prepend) { 443 orderBy.add(0, expr); 444 } else { 445 orderBy.add(expr); 446 } 447 } 448 449 public String toString() 450 { 451 if (generateFormattedSql) { 452 StringWriter sw = new StringWriter(256); 453 PrintWriter pw = new PrintWriter(sw); 454 print(pw, ""); 455 pw.flush(); 456 return sw.toString(); 457 458 } else { 459 buf.setLength(0); 460 461 select.toBuffer(buf, 462 distinct ? "select distinct " : "select ", ", "); 463 buf.append(getGroupingFunction("")); 464 from.toBuffer(buf, " from ", ", "); 465 where.toBuffer(buf, " where ", " and "); 466 if (hasGroupingSet()) { 467 StringWriter stringWriter = new StringWriter(); 468 printGroupingSets(new PrintWriter(stringWriter), ""); 469 buf.append(stringWriter.toString()); 470 } else { 471 groupBy.toBuffer(buf, " group by ", ", "); 472 } 473 having.toBuffer(buf, " having ", " and "); 474 orderBy.toBuffer(buf, " order by ", ", "); 475 476 return buf.toString(); 477 } 478 } 479 480 /** 481 * Prints this SqlQuery to a PrintWriter with each clause on a separate 482 * line, and with the specified indentation prefix. 483 * 484 * @param pw Print writer 485 * @param prefix Prefix for each line 486 */ 487 public void print(PrintWriter pw, String prefix) { 488 select.print( 489 pw, generateFormattedSql, prefix, 490 distinct ? "select distinct " : "select ", 491 ", "); 492 pw.print(getGroupingFunction(prefix)); 493 from.print(pw, generateFormattedSql, prefix, "from ", ", "); 494 where.print( 495 pw, generateFormattedSql, prefix, "where ", " and "); 496 if (hasGroupingSet()) { 497 printGroupingSets(pw, prefix); 498 } else { 499 groupBy.print(pw, generateFormattedSql, prefix, "group by ", ", "); 500 } 501 having.print(pw, generateFormattedSql, prefix, "having ", " and "); 502 orderBy.print(pw, generateFormattedSql, prefix, "order by ", ", "); 503 } 504 505 private String getGroupingFunction(String prefix) { 506 if (!hasGroupingSet()) { 507 return ""; 508 } 509 StringBuilder buf = new StringBuilder(); 510 for (int i = 0; i < groupingFunction.size(); i++) { 511 if (generateFormattedSql) { 512 buf.append(" ").append(prefix); 513 } 514 buf.append(", "); 515 buf.append("grouping("); 516 buf.append(groupingFunction.get(i)); 517 buf.append(") as "); 518 dialect.quoteIdentifier("g" + i, buf); 519 if (generateFormattedSql) { 520 buf.append(Util.nl); 521 } 522 } 523 return buf.toString(); 524 } 525 526 527 private void printGroupingSets(PrintWriter pw, String prefix) { 528 pw.print(" group by grouping sets ("); 529 for (int i = 0; i < groupingSet.size(); i++) { 530 if (i > 0) { 531 pw.print(","); 532 } 533 pw.print("("); 534 groupingSet.get(i).print( 535 pw, generateFormattedSql, prefix, "", ",", "", ""); 536 pw.print(")"); 537 } 538 pw.print(")"); 539 } 540 541 private boolean hasGroupingSet() { 542 return !groupingSet.isEmpty(); 543 } 544 545 public Dialect getDialect() { 546 return dialect; 547 } 548 549 public static SqlQuery newQuery(Connection jdbcConnection, String err) { 550 try { 551 final Dialect dialect = 552 Dialect.create(jdbcConnection.getMetaData()); 553 return new SqlQuery(dialect); 554 } catch (SQLException e) { 555 throw Util.newInternal(e, err); 556 } 557 } 558 559 public static SqlQuery newQuery(DataSource dataSource, String err) { 560 Connection jdbcConnection = null; 561 try { 562 jdbcConnection = dataSource.getConnection(); 563 final Dialect dialect = 564 Dialect.create(jdbcConnection.getMetaData()); 565 return new SqlQuery(dialect); 566 } catch (SQLException e) { 567 throw Util.newInternal(e, err); 568 } finally { 569 if (jdbcConnection != null) { 570 try { 571 jdbcConnection.close(); 572 } catch (SQLException e) { 573 // ignore 574 } 575 } 576 } 577 } 578 579 public void addGroupingSet(List<String> groupingColumnsExpr) { 580 ClauseList groupingList = new ClauseList(false); 581 for (String columnExp : groupingColumnsExpr) { 582 groupingList.add(columnExp); 583 } 584 groupingSet.add(groupingList); 585 } 586 587 public void addGroupingFunction(String columnExpr) { 588 groupingFunction.add(columnExpr); 589 } 590 591 static class ClauseList extends ArrayList<String> { 592 private final boolean allowDups; 593 594 ClauseList(final boolean allowDups) { 595 this.allowDups = allowDups; 596 } 597 598 599 /** 600 * Adds an element to this ClauseList if either duplicates are allowed 601 * or if it has not already been added. 602 * 603 * @param element Element to add 604 * @return whether element was added, per 605 * {@link java.util.Collection#add(Object)} 606 */ 607 public boolean add(final String element) { 608 if (allowDups || !contains(element)) { 609 return super.add(element); 610 } 611 return false; 612 } 613 614 void toBuffer(final StringBuilder buf, 615 final String first, 616 final String sep) { 617 boolean firstTime = true; 618 for (String s : this) { 619 if (firstTime) { 620 buf.append(first); 621 firstTime = false; 622 } else { 623 buf.append(sep); 624 } 625 buf.append(s); 626 } 627 } 628 629 void print( 630 final PrintWriter pw, 631 boolean generateFormattedSql, 632 final String prefix, 633 final String first, 634 final String sep) 635 { 636 print(pw, generateFormattedSql, prefix, first, sep, "", ""); 637 } 638 639 void print( 640 final PrintWriter pw, 641 boolean generateFormattedSql, 642 final String prefix, 643 final String first, 644 final String sep, 645 final String suffix, 646 final String last) 647 { 648 String subprefix = prefix + " "; 649 boolean firstTime = true; 650 for (String s : this) { 651 if (firstTime) { 652 if (generateFormattedSql) { 653 pw.print(prefix); 654 } 655 pw.print(first); 656 firstTime = false; 657 } else { 658 pw.print(sep); 659 } 660 if (generateFormattedSql) { 661 pw.println(); 662 pw.print(subprefix); 663 } 664 pw.print(s); 665 if (generateFormattedSql) { 666 pw.print(suffix); 667 } 668 } 669 pw.print(last); 670 if (!firstTime && generateFormattedSql) { 671 pw.println(); 672 } 673 } 674 } 675 676 /** 677 * Description of a SQL dialect. It is immutable. 678 */ 679 public static class Dialect { 680 private final String quoteIdentifierString; 681 private final String productName; 682 private final String productVersion; 683 private final Set<List<Integer>> supportedResultSetTypes; 684 private final boolean readOnly; 685 private final int maxColumnNameLength; 686 687 private static final int[] RESULT_SET_TYPE_VALUES = { 688 ResultSet.TYPE_FORWARD_ONLY, 689 ResultSet.TYPE_SCROLL_INSENSITIVE, 690 ResultSet.TYPE_SCROLL_SENSITIVE}; 691 private static final int[] CONCURRENCY_VALUES = { 692 ResultSet.CONCUR_READ_ONLY, 693 ResultSet.CONCUR_UPDATABLE}; 694 695 Dialect( 696 String quoteIdentifierString, 697 String productName, 698 String productVersion, 699 Set<List<Integer>> supportedResultSetTypes, 700 boolean readOnly, 701 int maxColumnNameLength) 702 { 703 this.quoteIdentifierString = quoteIdentifierString; 704 this.productName = productName; 705 this.productVersion = productVersion; 706 this.supportedResultSetTypes = supportedResultSetTypes; 707 this.readOnly = readOnly; 708 this.maxColumnNameLength = maxColumnNameLength; 709 } 710 711 /** 712 * Creates a {@link SqlQuery.Dialect} from a {@link DatabaseMetaData}. 713 */ 714 public static Dialect create(final DatabaseMetaData databaseMetaData) { 715 String productName; 716 try { 717 productName = databaseMetaData.getDatabaseProductName(); 718 } catch (SQLException e1) { 719 throw Util.newInternal(e1, "while detecting database product"); 720 } 721 722 String quoteIdentifierString; 723 try { 724 quoteIdentifierString = 725 databaseMetaData.getIdentifierQuoteString(); 726 } catch (SQLException e) { 727 throw Util.newInternal(e, "while quoting identifier"); 728 } 729 730 if ((quoteIdentifierString == null) || 731 (quoteIdentifierString.trim().length() == 0)) { 732 if (productName.toUpperCase().equals("MYSQL")) { 733 // mm.mysql.2.0.4 driver lies. We know better. 734 quoteIdentifierString = "`"; 735 } else { 736 // Quoting not supported 737 quoteIdentifierString = null; 738 } 739 } 740 741 String productVersion; 742 try { 743 productVersion = databaseMetaData.getDatabaseProductVersion(); 744 } catch (SQLException e11) { 745 throw Util.newInternal(e11, 746 "while detecting database product version"); 747 } 748 749 Set<List<Integer>> supports = new HashSet<List<Integer>>(); 750 try { 751 for (int type : RESULT_SET_TYPE_VALUES) { 752 for (int concurrency : CONCURRENCY_VALUES) { 753 if (databaseMetaData.supportsResultSetConcurrency( 754 type, concurrency)) { 755 String driverName = 756 databaseMetaData.getDriverName(); 757 if (type != ResultSet.TYPE_FORWARD_ONLY && 758 driverName.equals( 759 "JDBC-ODBC Bridge (odbcjt32.dll)")) 760 { 761 // In JDK 1.6, the Jdbc-Odbc bridge announces 762 // that it can handle TYPE_SCROLL_INSENSITIVE 763 // but it does so by generating a 'COUNT(*)' 764 // query, and this query is invalid if the query 765 // contains a single-quote. So, override the 766 // driver. 767 continue; 768 } 769 supports.add( 770 new ArrayList<Integer>( 771 Arrays.asList(type, concurrency))); 772 } 773 } 774 } 775 } catch (SQLException e11) { 776 throw Util.newInternal(e11, 777 "while detecting result set concurrency"); 778 } 779 780 final boolean readOnly; 781 try { 782 readOnly = databaseMetaData.isReadOnly(); 783 } catch (SQLException e) { 784 throw Util.newInternal(e, 785 "while detecting isReadOnly"); 786 } 787 788 final int maxColumnNameLength; 789 try { 790 maxColumnNameLength = 791 databaseMetaData.getMaxColumnNameLength(); 792 } catch (SQLException e) { 793 throw Util.newInternal(e, 794 "while detecting maxColumnNameLength"); 795 } 796 797 return new Dialect( 798 quoteIdentifierString, 799 productName, 800 productVersion, 801 supports, 802 readOnly, 803 maxColumnNameLength); 804 } 805 806 /** 807 * Creates a {@link SqlQuery.Dialect} from a 808 * {@link javax.sql.DataSource}. 809 * 810 * <p>NOTE: This method is not cheap. The implementation gets a 811 * connection from the connection pool. 812 * 813 * @return Dialect 814 */ 815 public static Dialect create(DataSource dataSource) { 816 Connection conn = null; 817 try { 818 conn = dataSource.getConnection(); 819 return create(conn.getMetaData()); 820 } catch (SQLException e) { 821 throw Util.newInternal( 822 e, "Error while creating SQL dialect"); 823 } finally { 824 try { 825 if (conn != null) { 826 conn.close(); 827 } 828 } catch (SQLException e) { 829 // ignore 830 } 831 } 832 } 833 834 // -- detect various databases -- 835 836 public boolean isAccess() { 837 return productName.equals("ACCESS"); 838 } 839 840 public boolean isDerby() { 841 return productName.trim().toUpperCase().equals("APACHE DERBY"); 842 } 843 844 public boolean isCloudscape() { 845 return productName.trim().toUpperCase().equals("DBMS:CLOUDSCAPE"); 846 } 847 848 public boolean isDB2() { 849 // DB2 on NT returns "DB2/NT" 850 return productName.startsWith("DB2"); 851 } 852 853 public boolean isAS400() { 854 // DB2/AS400 Product String = "DB2 UDB for AS/400" 855 return productName.startsWith("DB2 UDB for AS/400"); 856 } 857 858 public boolean isOldAS400() { 859 if (!isAS400()) { 860 return false; 861 } 862 // TB "04.03.0000 V4R3m0" 863 // this version cannot handle subqueries and is considered "old" 864 // DEUKA "05.01.0000 V5R1m0" is ok 865 String[] version_release = productVersion.split("\\.", 3); 866 /* 867 if (version_release.length > 2 && 868 "04".compareTo(version_release[0]) > 0 || 869 ("04".compareTo(version_release[0]) == 0 870 && "03".compareTo(version_release[1]) >= 0)) 871 return true; 872 */ 873 // assume, that version <= 04 is "old" 874 return ("04".compareTo(version_release[0]) >= 0); 875 } 876 877 // Note: its not clear that caching the best name would actually save 878 // very much time, so we do not do so. 879 private String getBestName() { 880 String best; 881 if (isOracle()) { 882 best = "oracle"; 883 } else if (isMSSQL()) { 884 best = "mssql"; 885 } else if (isMySQL()) { 886 best = "mysql"; 887 } else if (isAccess()) { 888 best = "access"; 889 } else if (isPostgres()) { 890 best = "postgres"; 891 } else if (isSybase()) { 892 best = "sybase"; 893 } else if (isCloudscape() || isDerby()) { 894 best = "derby"; 895 } else if (isDB2()) { 896 best = "db2"; 897 } else if (isFirebird()) { 898 best = "firebird"; 899 } else if (isInterbase()) { 900 best = "interbase"; 901 } else if (isIngres()) { 902 best = "ingres"; 903 } else if (isLucidDB()) { 904 best = "luciddb"; 905 } else if (isTeradata()) { 906 best = "teradata"; 907 } else { 908 best = "generic"; 909 } 910 return best; 911 } 912 913 /** 914 * @return SQL syntax that converts <code>expr</code> 915 * into upper case. 916 */ 917 public String toUpper(String expr) { 918 if (isDB2() || isAccess()) 919 return "UCASE(" + expr + ")"; 920 return "UPPER(" + expr + ")"; 921 } 922 923 public String caseWhenElse(String cond, String thenExpr, String elseExpr) { 924 if (isAccess()) { 925 return "IIF(" + cond + "," + thenExpr + "," + elseExpr + ")"; 926 } 927 return "CASE WHEN " + cond + " THEN " + thenExpr + " ELSE " + elseExpr + " END"; 928 } 929 930 /** 931 * Encloses an identifier in quotation marks appropriate for the 932 * current SQL dialect. For example, 933 * <code>quoteIdentifier("emp")</code> yields a string containing 934 * <code>"emp"</code> in Oracle, and a string containing 935 * <code>[emp]</code> in Access. 936 */ 937 public String quoteIdentifier(final String val) { 938 int size = val.length() + SINGLE_QUOTE_SIZE; 939 StringBuilder buf = new StringBuilder(size); 940 941 quoteIdentifier(val, buf); 942 943 return buf.toString(); 944 } 945 946 /** 947 * Appends to a buffer an identifier, quoted appropriately for this 948 * Dialect. 949 * 950 * @param val identifier to quote (must not be null). 951 * @param buf Buffer 952 */ 953 public void quoteIdentifier(final String val, final StringBuilder buf) { 954 String q = getQuoteIdentifierString(); 955 if (q == null) { 956 // quoting is not supported 957 buf.append(val); 958 return; 959 } 960 // if the value is already quoted, do nothing 961 // if not, then check for a dot qualified expression 962 // like "owner.table". 963 // In that case, prefix the single parts separately. 964 if (val.startsWith(q) && val.endsWith(q)) { 965 // already quoted - nothing to do 966 buf.append(val); 967 return; 968 } 969 970 int k = val.indexOf('.'); 971 if (k > 0) { 972 // qualified 973 String val1 = Util.replace(val.substring(0,k), q, q + q); 974 String val2 = Util.replace(val.substring(k + 1), q, q + q); 975 buf.append(q); 976 buf.append(val1); 977 buf.append(q); 978 buf.append("."); 979 buf.append(q); 980 buf.append(val2); 981 buf.append(q); 982 983 } else { 984 // not Qualified 985 String val2 = Util.replace(val, q, q + q); 986 buf.append(q); 987 buf.append(val2); 988 buf.append(q); 989 } 990 } 991 992 /** 993 * Encloses an identifier in quotation marks appropriate for the 994 * current SQL dialect. For example, in Oracle, where the identifiers 995 * are quoted using double-quotes, 996 * <code>quoteIdentifier("schema","table")</code> yields a string 997 * containing <code>"schema"."table"</code>. 998 * 999 * @param qual Qualifier. If it is not null, 1000 * <code>"<em>qual</em>".</code> is prepended. 1001 * @param name Name to be quoted. 1002 */ 1003 public String quoteIdentifier(final String qual, final String name) { 1004 // We know if the qalifier is null, then only the name is going 1005 // to be quoted. 1006 int size = name.length() 1007 + ((qual == null) 1008 ? SINGLE_QUOTE_SIZE 1009 : (qual.length() + DOUBLE_QUOTE_SIZE)); 1010 StringBuilder buf = new StringBuilder(size); 1011 1012 quoteIdentifier(buf, qual, name); 1013 1014 return buf.toString(); 1015 } 1016 1017 /** 1018 * Appends to a buffer a list of identifiers, quoted 1019 * appropriately for this Dialect. 1020 * 1021 * <p>Names in the list may be null, but there must be at least one 1022 * non-null name in the list.</p> 1023 * 1024 * @param buf Buffer 1025 * @param names List of names to be quoted 1026 */ 1027 public void quoteIdentifier( 1028 final StringBuilder buf, 1029 final String... names) 1030 { 1031 int nonNullNameCount = 0; 1032 for (String name : names) { 1033 if (name == null) { 1034 continue; 1035 } 1036 if (nonNullNameCount > 0) { 1037 buf.append('.'); 1038 } 1039 assert name.length() > 0 1040 : "name should probably be null, not empty"; 1041 quoteIdentifier(name, buf); 1042 ++nonNullNameCount; 1043 } 1044 } 1045 1046 /** 1047 * Returns the character which is used to quote identifiers, or null 1048 * if quoting is not supported. 1049 */ 1050 public String getQuoteIdentifierString() { 1051 return quoteIdentifierString; 1052 } 1053 1054 /** 1055 * Appends to a buffer a single-quoted SQL string. 1056 * 1057 * <p>For example, in the default dialect, 1058 * <code>quoteStringLiteral(buf, "Can't")</code> appends 1059 * "<code>'Can''t'</code>" to <code>buf</code>. 1060 */ 1061 public void quoteStringLiteral(StringBuilder buf, String s) { 1062 Util.singleQuoteString(s, buf); 1063 } 1064 1065 /** 1066 * Appends to a buffer a numeric literal. 1067 * 1068 * <p>In the default dialect, numeric literals are printed as is. 1069 */ 1070 public void quoteNumericLiteral(StringBuilder buf, String value) { 1071 buf.append(value); 1072 } 1073 1074 /** 1075 * Appends to a buffer a boolean literal. 1076 * 1077 * <p>In the default dialect, boolean literals are printed as is. 1078 */ 1079 public void quoteBooleanLiteral(StringBuilder buf, String value) { 1080 // NOTE jvs 1-Jan-2007: See quoteDateLiteral for explanation. 1081 // In addition, note that we leave out UNKNOWN (even though 1082 // it is a valid SQL:2003 literal) because it's really 1083 // NULL in disguise, and NULL is always treated specially. 1084 if (!value.equalsIgnoreCase("TRUE") 1085 && !(value.equalsIgnoreCase("FALSE"))) { 1086 throw new NumberFormatException( 1087 "Illegal BOOLEAN literal: " + value); 1088 } 1089 buf.append(value); 1090 } 1091 1092 /** 1093 * Appends to a buffer a date literal. 1094 * 1095 * <p>For example, in the default dialect, 1096 * <code>quoteStringLiteral(buf, "1969-03-17")</code> 1097 * appends <code>DATE '1969-03-17'</code>. 1098 */ 1099 public void quoteDateLiteral(StringBuilder buf, String value) { 1100 // NOTE jvs 1-Jan-2007: Check that the supplied literal is in valid 1101 // SQL:2003 date format. A hack in 1102 // RolapSchemaReader.lookupMemberChildByName looks for 1103 // NumberFormatException to suppress it, so that is why 1104 // we convert the exception here. 1105 final java.sql.Date date; 1106 try { 1107 date = java.sql.Date.valueOf(value); 1108 } catch (IllegalArgumentException ex) { 1109 throw new NumberFormatException( 1110 "Illegal DATE literal: " + value); 1111 } 1112 if (isDerby()) { 1113 // Derby accepts DATE('2008-01-23') but not SQL:2003 format. 1114 buf.append("DATE("); 1115 Util.singleQuoteString(value, buf); 1116 buf.append(")"); 1117 } else if (isAccess()) { 1118 // Access accepts #01/23/2008# but not SQL:2003 format. 1119 buf.append("#"); 1120 Calendar calendar = Calendar.getInstance(); 1121 calendar.setTime(date); 1122 buf.append(calendar.get(Calendar.MONTH) + 1); 1123 buf.append("/"); 1124 buf.append(calendar.get(Calendar.DAY_OF_MONTH)); 1125 buf.append("/"); 1126 buf.append(calendar.get(Calendar.YEAR)); 1127 buf.append("#"); 1128 } else { 1129 // SQL:2003 date format: DATE '2008-01-23'. 1130 buf.append("DATE "); 1131 Util.singleQuoteString(value, buf); 1132 } 1133 } 1134 1135 /** 1136 * Appends to a buffer a time literal. 1137 * 1138 * <p>For example, in the default dialect, 1139 * <code>quoteStringLiteral(buf, "12:34:56")</code> 1140 * appends <code>TIME '12:34:56'</code>. 1141 */ 1142 public void quoteTimeLiteral(StringBuilder buf, String value) { 1143 // NOTE jvs 1-Jan-2007: See quoteDateLiteral for explanation. 1144 try { 1145 java.sql.Time.valueOf(value); 1146 } catch (IllegalArgumentException ex) { 1147 throw new NumberFormatException( 1148 "Illegal TIME literal: " + value); 1149 } 1150 buf.append("TIME "); 1151 Util.singleQuoteString(value, buf); 1152 } 1153 1154 /** 1155 * Appends to a buffer a timestamp literal. 1156 * 1157 * <p>For example, in the default dialect, 1158 * <code>quoteStringLiteral(buf, "1969-03-17 12:34:56")</code> 1159 * appends <code>TIMESTAMP '1969-03-17 12:34:56'</code>. 1160 */ 1161 public void quoteTimestampLiteral(StringBuilder buf, String value) { 1162 // NOTE jvs 1-Jan-2007: See quoteTimestampLiteral for explanation. 1163 try { 1164 java.sql.Timestamp.valueOf(value); 1165 } catch (IllegalArgumentException ex) { 1166 throw new NumberFormatException( 1167 "Illegal TIMESTAMP literal: " + value); 1168 } 1169 buf.append("TIMESTAMP "); 1170 Util.singleQuoteString(value, buf); 1171 } 1172 1173 /** 1174 * Returns whether the underlying database is Firebird. 1175 */ 1176 public boolean isFirebird() { 1177 return productName.toUpperCase().indexOf("FIREBIRD") >= 0; 1178 } 1179 1180 /** 1181 * Returns whether the underlying database is Informix. 1182 */ 1183 public boolean isInformix() { 1184 return productName.startsWith("Informix"); 1185 } 1186 1187 /** 1188 * Returns whether the underlying database is Ingres. 1189 */ 1190 public boolean isIngres() { 1191 return productName.toUpperCase().equals("INGRES"); 1192 } 1193 1194 /** 1195 * Returns whether the underlying database is Interbase. 1196 */ 1197 public boolean isInterbase() { 1198 return productName.equals("Interbase"); 1199 } 1200 1201 /** 1202 * Returns whether the underlying database is LucidDB. 1203 */ 1204 public boolean isLucidDB() { 1205 return productName.toUpperCase().equals("LUCIDDB"); 1206 } 1207 1208 /** 1209 * Returns whether the underlying database is Microsoft SQL Server. 1210 */ 1211 public boolean isMSSQL() { 1212 return productName.toUpperCase().indexOf("SQL SERVER") >= 0; 1213 } 1214 1215 /** 1216 * Returns whether the underlying database is Oracle. 1217 */ 1218 public boolean isOracle() { 1219 return productName.equals("Oracle"); 1220 } 1221 1222 /** 1223 * Returns whether the underlying database is Postgres. 1224 */ 1225 public boolean isPostgres() { 1226 return productName.toUpperCase().indexOf("POSTGRE") >= 0; 1227 } 1228 1229 /** 1230 * Returns whether the underlying database is MySQL. 1231 */ 1232 public boolean isMySQL() { 1233 return productName.toUpperCase().equals("MYSQL"); 1234 } 1235 1236 /** 1237 * Returns whether the underlying database is Sybase. 1238 */ 1239 public boolean isSybase() { 1240 return productName.toUpperCase().indexOf("SYBASE") >= 0; 1241 } 1242 1243 /** 1244 * Returns whether the underlying database is Teradata. 1245 */ 1246 public boolean isTeradata() { 1247 return productName.toUpperCase().indexOf("TERADATA") >= 0; 1248 } 1249 1250 /** 1251 * Returns whether this Dialect requires subqueries in the FROM clause 1252 * to have an alias. 1253 * 1254 * @see #allowsFromQuery() 1255 */ 1256 public boolean requiresAliasForFromQuery() { 1257 return isMySQL() || 1258 isDerby() || 1259 isTeradata() || 1260 isPostgres(); 1261 } 1262 1263 /** 1264 * Returns whether the SQL dialect allows "AS" in the FROM clause. 1265 * If so, "SELECT * FROM t AS alias" is a valid query. 1266 */ 1267 protected boolean allowsAs() { 1268 return !isOracle() && !isSybase() && !isFirebird() && 1269 !isInterbase(); 1270 } 1271 1272 /** 1273 * Returns whether this Dialect allows a subquery in the from clause, 1274 * for example 1275 * 1276 * <blockquote><code>SELECT * FROM (SELECT * FROM t) AS x</code></blockquote> 1277 * 1278 * @see #requiresAliasForFromQuery() 1279 */ 1280 public boolean allowsFromQuery() { 1281 // Older versions of AS400 and MySQL before 4.0 do not allow FROM 1282 // subqueries in the FROM clause. 1283 return !(isMySQL() && productVersion.compareTo("4.") < 0) 1284 && !isOldAS400() && !isInformix() 1285 && !isSybase() && !isInterbase(); 1286 } 1287 1288 /** 1289 * Returns whether this Dialect allows multiple arguments to the 1290 * <code>COUNT(DISTINCT ...) aggregate function, for example 1291 * 1292 * <blockquote><code>SELECT COUNT(DISTINCT x, y) FROM t</code></blockquote> 1293 * 1294 * @see #allowsCountDistinct() 1295 * @see #allowsMultipleCountDistinct() 1296 */ 1297 public boolean allowsCompoundCountDistinct() { 1298 return isMySQL(); 1299 } 1300 1301 /** 1302 * Returns whether this Dialect supports distinct aggregations. 1303 * 1304 * <p>For example, Access does not allow 1305 * <blockquote> 1306 * <code>select count(distinct x) from t</code> 1307 * </blockquote> 1308 */ 1309 public boolean allowsCountDistinct() { 1310 return !isAccess(); 1311 } 1312 1313 /** 1314 * Returns whether this Dialect supports more than one distinct 1315 * aggregation in the same query. 1316 * 1317 * <p>In Derby 10.1, 1318 * <blockquote> 1319 * <code>select couunt(distinct x) from t</code> 1320 * </blockquote> 1321 * is OK, but 1322 * <blockquote> 1323 * <code>select couunt(distinct x), count(distinct y) from t</code> 1324 * </blockquote> 1325 * gives "Multiple DISTINCT aggregates are not supported at this time." 1326 * 1327 * @return whether this Dialect supports more than one distinct 1328 * aggregation in the same query 1329 */ 1330 public boolean allowsMultipleCountDistinct() { 1331 return allowsCountDistinct() && 1332 !isDerby(); 1333 } 1334 1335 /** 1336 * Returns whether this Dialect has performant support of distinct SQL 1337 * measures in the same query. 1338 * 1339 * @return whether this dialect supports multiple count(distinct subquery) 1340 * measures in one query. 1341 */ 1342 public boolean allowsMultipleDistinctSqlMeasures() { 1343 return allowsMultipleCountDistinct() && !isLucidDB(); 1344 } 1345 1346 /** 1347 * Generates a SQL statement to represent an inline dataset. 1348 * 1349 * <p>For example, for Oracle, generates 1350 * 1351 * <pre> 1352 * SELECT 1 AS FOO, 'a' AS BAR FROM dual 1353 * UNION ALL 1354 * SELECT 2 AS FOO, 'b' AS BAR FROM dual 1355 * </pre> 1356 * 1357 * <p>For ANSI SQL, generates: 1358 * 1359 * <pre> 1360 * VALUES (1, 'a'), (2, 'b') 1361 * </pre> 1362 * 1363 * @param columnNames List of column names 1364 * @param columnTypes List of column types ("String" or "Numeric") 1365 * @param valueList List of rows values 1366 * @return SQL string 1367 */ 1368 public String generateInline( 1369 List<String> columnNames, 1370 List<String> columnTypes, 1371 List<String[]> valueList) 1372 { 1373 if (isOracle()) { 1374 return generateInlineGeneric( 1375 columnNames, columnTypes, valueList, 1376 " from dual"); 1377 } else if (isAccess()) { 1378 // Fall back to using the FoodMart 'days' table, because 1379 // Access SQL has no way to generate values not from a table. 1380 return generateInlineGeneric( 1381 columnNames, columnTypes, valueList, 1382 " from `days` where `day` = 1"); 1383 } else if (isMySQL() || isIngres() || isMSSQL() || isTeradata()) { 1384 String fromClause = null; 1385 if (isTeradata() && valueList.size() > 1) { 1386 // In teradata, "SELECT 1,2" is valid but "SELECT 1,2 UNION 1387 // SELECT 3,4" gives "3888: SELECT for a UNION,INTERSECT or 1388 // MINUS must reference a table." 1389 fromClause = " FROM (SELECT 1 a) z "; 1390 } 1391 return generateInlineGeneric( 1392 columnNames, columnTypes, valueList, fromClause); 1393 } else { 1394 return generateInlineForAnsi( 1395 "t", columnNames, columnTypes, valueList); 1396 } 1397 } 1398 1399 /** 1400 * Generic algorithm to generate inline values list, 1401 * using an optional FROM clause, specified by the caller of this 1402 * method, appropriate to the dialect of SQL. 1403 */ 1404 private String generateInlineGeneric( 1405 List<String> columnNames, 1406 List<String> columnTypes, 1407 List<String[]> valueList, 1408 String fromClause) { 1409 final StringBuilder buf = new StringBuilder(); 1410 int columnCount = columnNames.size(); 1411 assert columnTypes.size() == columnCount; 1412 1413 // Teradata derives datatype from value of column in first row, and 1414 // truncates subsequent rows. Therefore, we need to cast every 1415 // value to the correct length. Figure out the maximum length now. 1416 Integer[] maxLengths = new Integer[columnCount]; 1417 if (isTeradata()) { 1418 for (int i = 0; i < columnTypes.size(); i++) { 1419 String columnType = columnTypes.get(i); 1420 Datatype datatype = Datatype.valueOf(columnType); 1421 if (datatype == Datatype.String) { 1422 int maxLen = -1; 1423 for (String[] strings : valueList) { 1424 if (strings[i] != null 1425 && strings[i].length() > maxLen) { 1426 maxLen = strings[i].length(); 1427 } 1428 } 1429 maxLengths[i] = maxLen; 1430 } 1431 } 1432 } 1433 1434 for (int i = 0; i < valueList.size(); i++) { 1435 if (i > 0) { 1436 buf.append(" union all "); 1437 } 1438 String[] values = valueList.get(i); 1439 buf.append("select "); 1440 for (int j = 0; j < values.length; j++) { 1441 String value = values[j]; 1442 if (j > 0) { 1443 buf.append(", "); 1444 } 1445 final String columnType = columnTypes.get(j); 1446 final String columnName = columnNames.get(j); 1447 Datatype datatype = Datatype.valueOf(columnType); 1448 final Integer maxLength = maxLengths[j]; 1449 if (maxLength != null) { 1450 // Generate CAST for Teradata. 1451 buf.append("CAST("); 1452 quote(buf, value, datatype); 1453 buf.append(" AS VARCHAR(").append(maxLength).append("))"); 1454 } else { 1455 quote(buf, value, datatype); 1456 } 1457 if (allowsAs()) { 1458 buf.append(" as "); 1459 } else { 1460 buf.append(' '); 1461 } 1462 quoteIdentifier(columnName, buf); 1463 } 1464 if (fromClause != null) { 1465 buf.append(fromClause); 1466 } 1467 } 1468 return buf.toString(); 1469 } 1470 1471 /** 1472 * Generates inline values list using ANSI 'VALUES' syntax. 1473 * For example, 1474 * 1475 * <blockquote><code>SELECT * FROM 1476 * (VALUES (1, 'a'), (2, 'b')) AS t(x, y)</code></blockquote> 1477 * 1478 * <p>If NULL values are present, we use a CAST to ensure that they 1479 * have the same type as other columns: 1480 * 1481 * <blockquote><code>SELECT * FROM 1482 * (VALUES (1, 'a'), (2, CASE(NULL AS VARCHAR(1)))) AS t(x, y) 1483 * </code></blockquote> 1484 * 1485 * <p>This syntax is known to work on Derby, but not Oracle 10 or 1486 * Access. 1487 */ 1488 private String generateInlineForAnsi( 1489 String alias, 1490 List<String> columnNames, 1491 List<String> columnTypes, 1492 List<String[]> valueList) { 1493 final StringBuilder buf = new StringBuilder(); 1494 buf.append("SELECT * FROM (VALUES "); 1495 // Derby pads out strings to a common length, so we cast the 1496 // string values to avoid this. Determine the cast type for each 1497 // column. 1498 String[] castTypes = null; 1499 if (isDerby()) { 1500 castTypes = new String[columnNames.size()]; 1501 for (int i = 0; i < columnNames.size(); i++) { 1502 String columnType = columnTypes.get(i); 1503 if (columnType.equals("String")) { 1504 castTypes[i] = 1505 guessSqlType(columnType, valueList, i); 1506 } 1507 } 1508 } 1509 for (int i = 0; i < valueList.size(); i++) { 1510 if (i > 0) { 1511 buf.append(", "); 1512 } 1513 String[] values = valueList.get(i); 1514 buf.append("("); 1515 for (int j = 0; j < values.length; j++) { 1516 String value = values[j]; 1517 if (j > 0) { 1518 buf.append(", "); 1519 } 1520 final String columnType = columnTypes.get(j); 1521 Datatype datatype = Datatype.valueOf(columnType); 1522 if (value == null) { 1523 String sqlType = 1524 guessSqlType(columnType, valueList, j); 1525 buf.append("CAST(NULL AS ") 1526 .append(sqlType) 1527 .append(")"); 1528 } else if (isDerby() && castTypes[j] != null) { 1529 buf.append("CAST("); 1530 quote(buf, value, datatype); 1531 buf.append(" AS ") 1532 .append(castTypes[j]) 1533 .append(")"); 1534 } else { 1535 quote(buf, value, datatype); 1536 } 1537 } 1538 buf.append(")"); 1539 } 1540 buf.append(") AS "); 1541 quoteIdentifier(alias, buf); 1542 buf.append(" ("); 1543 for (int j = 0; j < columnNames.size(); j++) { 1544 final String columnName = columnNames.get(j); 1545 if (j > 0) { 1546 buf.append(", "); 1547 } 1548 quoteIdentifier(columnName, buf); 1549 } 1550 buf.append(")"); 1551 return buf.toString(); 1552 } 1553 1554 /** 1555 * If Double values need to include additional exponent in its string 1556 * represenation. This is to make sure that Double literals will be 1557 * interpreted as doubles by LucidDB. 1558 * 1559 * @param value Double value to generate string for 1560 * @param valueString java string representation for this value. 1561 * @return whether an additional exponent "E0" needs to be appended 1562 * 1563 */ 1564 private boolean needsExponent(Object value, String valueString) { 1565 if (isLucidDB() && 1566 value instanceof Double && 1567 !valueString.contains("E")) { 1568 return true; 1569 } 1570 return false; 1571 } 1572 1573 /** 1574 * Appends to a buffer a value quoted for its type. 1575 */ 1576 public void quote(StringBuilder buf, Object value, Datatype datatype) { 1577 if (value == null) { 1578 buf.append("null"); 1579 } else { 1580 String valueString = value.toString(); 1581 if (needsExponent(value, valueString)) { 1582 valueString += "E0"; 1583 } 1584 datatype.quoteValue(buf, this, valueString); 1585 } 1586 } 1587 1588 /** 1589 * Guesses the type of a column based upon (a) its basic type, 1590 * (b) a list of values. 1591 */ 1592 private static String guessSqlType( 1593 String basicType, List<String[]> valueList, int column) { 1594 if (basicType.equals("String")) { 1595 int maxLen = 1; 1596 for (String[] values : valueList) { 1597 final String value = values[column]; 1598 if (value == null) { 1599 continue; 1600 } 1601 maxLen = Math.max(maxLen, value.length()); 1602 } 1603 return "VARCHAR(" + maxLen + ")"; 1604 } else { 1605 return "INTEGER"; 1606 } 1607 } 1608 1609 /** 1610 * Returns whether this dialect supports common SQL Data Definition 1611 * Language (DDL) statements such as <code>CREATE TABLE</code> and 1612 * <code>DROP INDEX</code>. 1613 * 1614 * <p>Access seems to allow DDL iff the .mdb file is writeable. 1615 * 1616 * @see java.sql.DatabaseMetaData#isReadOnly() 1617 */ 1618 public boolean allowsDdl() { 1619 return !readOnly; 1620 } 1621 1622 /** 1623 * Returns whether NULL values appear last when sorted using ORDER BY. 1624 * According to the SQL standard, this is implementation-specific. 1625 */ 1626 public boolean isNullsCollateLast() { 1627 if (isMySQL()) { 1628 return false; 1629 } 1630 return true; 1631 } 1632 1633 /** 1634 * Modifies an expression in the ORDER BY clause to ensure that NULL 1635 * values collate after all non-NULL values. 1636 * If {@link #isNullsCollateLast()} is true, there's nothing to do. 1637 */ 1638 public String forceNullsCollateLast(String expr) { 1639 // If we need to support other DBMSes, note that the SQL standard 1640 // provides the syntax 'ORDER BY x ASC NULLS LAST'. 1641 if (isMySQL()) { 1642 String addIsNull = "ISNULL(" + expr + "), "; 1643 expr = addIsNull + expr; 1644 } 1645 return expr; 1646 } 1647 1648 /** 1649 * Returns whether this Dialect supports expressions in the GROUP BY 1650 * clause. Derby/Cloudscape do not. 1651 * 1652 * @return Whether this Dialect allows expressions in the GROUP BY 1653 * clause 1654 */ 1655 public boolean supportsGroupByExpressions() { 1656 return !(isDerby() || isCloudscape()); 1657 } 1658 1659 /** 1660 * Returns whether this Dialect allows the GROUPING SETS construct in 1661 * the GROUP BY clause. Currently Oracle, DB2 and Teradata. 1662 * 1663 * @return Whether this Dialect allows GROUPING SETS clause 1664 */ 1665 public boolean supportsGroupingSets() { 1666 return isOracle() || isDB2() || isTeradata(); 1667 } 1668 1669 /** 1670 * Returns whether this Dialect places no limit on the number 1671 * of rows which can appear as elements of an IN or VALUES 1672 * expression. 1673 * 1674 * @return whether value list length is unlimited 1675 */ 1676 public boolean supportsUnlimitedValueList() { 1677 return isLucidDB(); 1678 } 1679 1680 /** 1681 * Returns true if this Dialect can include expressions in the ORDER BY 1682 * clause only by adding an expression to the SELECT clause and using 1683 * its alias. 1684 * 1685 * <p>For example, in such a dialect, 1686 * <blockquote> 1687 * <code>SELECT x FROM t ORDER BY x + y</code> 1688 * </blockquote> 1689 * would be illegal, but 1690 * <blockquote> 1691 * <code>SELECT x, x + y AS z FROM t ORDER BY z</code> 1692 * </blockquote> 1693 * 1694 * would be legal.</p> 1695 * 1696 * <p>MySQL, DB2 and Ingres are examples of such dialects.</p> 1697 * 1698 * @return Whether this Dialect can include expressions in the ORDER BY 1699 * clause only by adding an expression to the SELECT clause and using 1700 * its alias 1701 */ 1702 public boolean requiresOrderByAlias() { 1703 return isMySQL() || isDB2() || isIngres(); 1704 } 1705 1706 /** 1707 * Returns true if aliases defined in the SELECT clause can be used as 1708 * expressions in the ORDER BY clause. 1709 * 1710 * <p>For example, in such a dialect, 1711 * <blockquote> 1712 * <code>SELECT x, x + y AS z FROM t ORDER BY z</code> 1713 * </blockquote> 1714 * 1715 * would be legal.</p> 1716 * 1717 * <p>MySQL, DB2 and Ingres are examples of dialects where this is true; 1718 * Access is a dialect where this is false.</p> 1719 * 1720 * @return Whether aliases defined in the SELECT clause can be used as 1721 * expressions in the ORDER BY clause. 1722 */ 1723 public boolean allowsOrderByAlias() { 1724 return requiresOrderByAlias(); 1725 } 1726 1727 /** 1728 * Returns true if this dialect supports multi-value IN expressions. 1729 * E.g., 1730 * 1731 * <code>WHERE (col1, col2) IN ((val1a, val2a), (val1b, val2b))</code> 1732 * 1733 * @return true if the dialect supports multi-value IN expressions 1734 */ 1735 public boolean supportsMultiValueInExpr() { 1736 return isLucidDB() || isMySQL(); 1737 } 1738 1739 /** 1740 * Returns whether this Dialect supports the given concurrency type 1741 * in combination with the given result set type. 1742 * 1743 * <p>The result is similar to 1744 * {@link java.sql.DatabaseMetaData#supportsResultSetConcurrency(int, int)}, 1745 * except that the JdbcOdbc bridge in JDK 1.6 overstates its abilities. 1746 * See bug 1690406. 1747 * 1748 * @param type defined in {@link ResultSet} 1749 * @param concurrency type defined in {@link ResultSet} 1750 * @return <code>true</code> if so; <code>false</code> otherwise 1751 * @exception SQLException if a database access error occurs 1752 */ 1753 public boolean supportsResultSetConcurrency( 1754 int type, 1755 int concurrency) { 1756 return supportedResultSetTypes.contains( 1757 Arrays.asList(type, concurrency)); 1758 } 1759 1760 1761 public String toString() { 1762 return productName; 1763 } 1764 1765 /** 1766 * Returns the maximum length of the name of a database column or query 1767 * alias allowed by this dialect. 1768 * 1769 * @see java.sql.DatabaseMetaData#getMaxColumnNameLength() 1770 * 1771 * @return maximum number of characters in a column name 1772 */ 1773 public int getMaxColumnNameLength() { 1774 return maxColumnNameLength; 1775 } 1776 } 1777 1778 /** 1779 * Datatype of a column. 1780 */ 1781 public enum Datatype { 1782 String { 1783 public void quoteValue(StringBuilder buf, SqlQuery.Dialect dialect, String value) { 1784 dialect.quoteStringLiteral(buf, value); 1785 } 1786 }, 1787 1788 Numeric { 1789 public void quoteValue(StringBuilder buf, Dialect dialect, String value) { 1790 dialect.quoteNumericLiteral(buf, value); 1791 } 1792 1793 public boolean isNumeric() { 1794 return true; 1795 } 1796 }, 1797 1798 Integer { 1799 public void quoteValue(StringBuilder buf, Dialect dialect, String value) { 1800 dialect.quoteNumericLiteral(buf, value); 1801 } 1802 1803 public boolean isNumeric() { 1804 return true; 1805 } 1806 }, 1807 1808 Boolean { 1809 public void quoteValue(StringBuilder buf, Dialect dialect, String value) { 1810 dialect.quoteBooleanLiteral(buf, value); 1811 } 1812 }, 1813 1814 Date { 1815 public void quoteValue(StringBuilder buf, Dialect dialect, String value) { 1816 dialect.quoteDateLiteral(buf, value); 1817 } 1818 }, 1819 1820 Time { 1821 public void quoteValue(StringBuilder buf, Dialect dialect, String value) { 1822 dialect.quoteTimeLiteral(buf, value); 1823 } 1824 }, 1825 1826 Timestamp { 1827 public void quoteValue(StringBuilder buf, Dialect dialect, String value) { 1828 dialect.quoteTimestampLiteral(buf, value); 1829 } 1830 }; 1831 1832 /** 1833 * Appends to a buffer a value of this type, in the appropriate format 1834 * for this dialect. 1835 * 1836 * @param buf Buffer 1837 * @param dialect Dialect 1838 * @param value Value 1839 */ 1840 public abstract void quoteValue( 1841 StringBuilder buf, 1842 Dialect dialect, 1843 String value); 1844 1845 public boolean isNumeric() { 1846 return false; 1847 } 1848 } 1849 1850 /** 1851 * Collection of alternative code for alternative dialects. 1852 */ 1853 public static class CodeSet { 1854 private final Map<String, String> dialectCodes = 1855 new HashMap<String, String>(); 1856 1857 public String put(String dialect, String code) { 1858 return dialectCodes.put(dialect, code); 1859 } 1860 1861 /** 1862 * Chooses the code variant which best matches the given Dialect. 1863 */ 1864 public String chooseQuery(Dialect dialect) { 1865 String best = dialect.getBestName(); 1866 String bestCode = dialectCodes.get(best); 1867 if (bestCode != null) { 1868 return bestCode; 1869 } 1870 String genericCode = dialectCodes.get("generic"); 1871 if (genericCode == null) { 1872 throw Util.newError("View has no 'generic' variant"); 1873 } 1874 return genericCode; 1875 } 1876 } 1877 } 1878 1879 // End SqlQuery.java