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