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