Java > Open Source Codes > mondrian > rolap > sql > SqlQuery


1 /*
2 // $Id: //open/mondrian/src/main/mondrian/rolap/sql/SqlQuery.java#72 $
3 // This software is subject to the terms of the Common Public License
4 // Agreement, available at the following URL:
5 // http://www.opensource.org/licenses/cpl.html.
6 // Copyright (C) 2002-2002 Kana Software, Inc.
7 // Copyright (C) 2002-2007 Julian Hyde and others
8 // All Rights Reserved.
9 // You must accept the terms of that agreement to use this software.
10 //
11 // jhyde, Mar 21, 2002
12 */
13
14 package mondrian.rolap.sql;
15
16 import java.io.PrintWriter ;
17 import java.io.StringWriter ;
18 import java.sql.DatabaseMetaData ;
19 import java.sql.SQLException ;
20 import java.sql.Connection ;
21 import java.util.ArrayList ;
22 import java.util.Iterator ;
23 import java.util.List ;
24
25 import mondrian.olap.MondrianDef;
26 import mondrian.olap.MondrianProperties;
27 import mondrian.olap.Util;
28
29 import org.eigenbase.util.property.Property;
30 import org.eigenbase.util.property.Trigger;
31
32 import javax.sql.DataSource ;
33
34 /**
35  * <code>SqlQuery</code> allows us to build a <code>select</code>
36  * statement and generate it in database-specific SQL syntax.
37  *
38  * <p> Notable differences in database syntax are:<dl>
39  *
40  * <dt> Identifier quoting </dt>
41  * <dd> Oracle (and all JDBC-compliant drivers) uses double-quotes,
42  * for example <code>select * from "emp"</code>. Access prefers brackets,
43  * for example <code>select * from [emp]</code>. mySQL allows single- and
44  * double-quotes for string literals, and therefore does not allow
45  * identifiers to be quoted, for example <code>select 'foo', "bar" from
46  * emp</code>. </dd>
47  *
48  * <dt> AS in from clause </dt>
49  * <dd> Oracle doesn't like AS in the from * clause, for example
50  * <code>select from emp as e</code> vs. <code>select * from emp
51  * e</code>. </dd>
52  *
53  * <dt> Column aliases </dt>
54  * <dd> Some databases require that every column in the select list
55  * has a valid alias. If the expression is an expression containing
56  * non-alphanumeric characters, an explicit alias is needed. For example,
57  * Oracle will barfs at <code>select empno + 1 from emp</code>. </dd>
58  *
59  * <dt> Parentheses around table names </dt>
60  * <dd> Oracle doesn't like <code>select * from (emp)</code> </dd>
61  *
62  * <dt> Queries in FROM clause </dt>
63  * <dd> PostgreSQL and hsqldb don't allow, for example, <code>select * from
64  * (select * from emp) as e</code>.</dd>
65  *
66  * <dt> Uniqueness of index names </dt>
67  * <dd> In PostgreSQL and Oracle, index names must be unique within the
68  * database; in Access and hsqldb, they must merely be unique within their
69  * table </dd>
70  *
71  * <dt> Datatypes </dt>
72  * <dd> In Oracle, BIT is CHAR(1), TIMESTAMP is DATE.
73  * In PostgreSQL, DOUBLE is DOUBLE PRECISION, BIT is BOOL. </dd>
74  * </ul>
75  *
76  * <p>
77  * NOTE: Instances of this class are NOT thread safe so the user must make
78  * sure this is accessed by only one thread at a time.
79  *
80  * @author jhyde
81  * @version $Id: //open/mondrian/src/main/mondrian/rolap/sql/SqlQuery.java#72 $
82  */
83 public class SqlQuery {
84     /** Controls the formatting of the sql string. */
85     private static boolean generateFormattedSql =
86         MondrianProperties.instance().GenerateFormattedSql.get();
87
88     static {
89         // Trigger is used to lookup and change the value of the
90 // variable that controls formatting.
91 // Using a trigger means we don't have to look up the property eveytime.
92 MondrianProperties.instance().GenerateFormattedSql.addTrigger(
93             new Trigger() {
94                 public boolean isPersistent() {
95                     return true;
96                 }
97                 public int phase() {
98                     return Trigger.PRIMARY_PHASE;
99                 }
100                 public void execute(Property property, String value) {
101                     generateFormattedSql = property.booleanValue();
102                 }
103             }
104         );
105     }
106
107     private boolean distinct;
108
109     private final ClauseList select;
110     private final ClauseList from;
111     private final ClauseList where;
112     private final ClauseList groupBy;
113     private final ClauseList having;
114     private final ClauseList orderBy;
115
116     /**
117      * This list is used to keep track of what aliases have been used in the
118      * FROM clause. One might think that a java.util.Set would be a more
119      * appropriate Collection type, but if you only have a couple of "from
120      * aliases", then iterating over a list is faster than doing a hash lookup
121      * (as is used in java.util.HashSet).
122      */
123     private final List <String > fromAliases;
124
125     /** The SQL dialect this query is to be generated in. */
126     private final Dialect dialect;
127
128     /** Scratch buffer. Clear it before use. */
129     private final StringBuilder buf;
130
131     /**
132      * Base constructor used by all other constructors to create an empty
133      * instance.
134      */
135     public SqlQuery(Dialect dialect) {
136
137         // both select and from allow duplications
138 this.select = new ClauseList(true);
139         this.from = new ClauseList(true);
140
141         this.where = new ClauseList(false);
142         this.groupBy = new ClauseList(false);
143         this.having = new ClauseList(false);
144         this.orderBy = new ClauseList(false);
145         this.fromAliases = new ArrayList <String >();
146         this.buf = new StringBuilder (128);
147
148         this.dialect = dialect;
149     }
150
151     /**
152      * Creates a <code>SqlQuery</code>
153      *
154      * @param databaseMetaData used to determine which dialect of
155      * SQL to generate. Must not be held beyond the constructor.
156      */
157     public SqlQuery(final DatabaseMetaData databaseMetaData) {
158         this(Dialect.create(databaseMetaData));
159     }
160
161     /**
162      * Creates an empty <code>SqlQuery</code> with the same environment as this
163      * one. (As per the Gang of Four 'prototype' pattern.)
164      */
165     public SqlQuery cloneEmpty()
166     {
167         return new SqlQuery(dialect);
168     }
169
170     public void setDistinct(final boolean distinct) {
171         this.distinct = distinct;
172     }
173
174     /**
175      * The size required to add quotes around a string - this ought to be
176      * large enough to prevent a reallocation.
177      */
178     private static final int SINGLE_QUOTE_SIZE = 10;
179     /**
180      * Two strings are quoted and the character '.' is placed between them.
181      */
182     private static final int DOUBLE_QUOTE_SIZE = 2 * SINGLE_QUOTE_SIZE + 1;
183
184     /**
185      * Adds a subquery to the FROM clause of this Query with a given alias.
186      * If the query already exists it either, depending on
187      * <code>failIfExists</code>, throws an exception or does not add the query
188      * and returns false.
189      *
190      * @param query Subquery
191      * @param alias (if not null, must not be zero length).
192      * @param failIfExists if true, throws exception if alias already exists
193      * @return true if query *was* added
194      *
195      * @pre alias != null
196      */
197     public boolean addFromQuery(
198         final String query,
199         final String alias,
200         final boolean failIfExists)
201     {
202         assert alias != null;
203
204         if (fromAliases.contains(alias)) {
205             if (failIfExists) {
206                 throw Util.newInternal(
207                         "query already contains alias '" + alias + "'");
208             } else {
209                 return false;
210             }
211         }
212
213         buf.setLength(0);
214
215         buf.append('(');
216         buf.append(query);
217         buf.append(')');
218         if (alias != null) {
219             Util.assertTrue(alias.length() > 0);
220
221             if (dialect.allowsAs()) {
222                 buf.append(" as ");
223             } else {
224                 buf.append(' ');
225             }
226             dialect.quoteIdentifier(alias, buf);
227             fromAliases.add(alias);
228         }
229
230         from.add(buf.toString());
231         return true;
232     }
233
234     /**
235      * Adds <code>[schema.]table AS alias</code> to the FROM clause.
236      *
237      * @param schema schema name; may be null
238      * @param table table name
239      * @param alias table alias, may not be null
240      * (if not null, must not be zero length).
241      * @param failIfExists Whether to throw a RuntimeException if from clause
242      * already contains this alias
243      *
244      * @pre alias != null
245      * @return true if table was added
246      */
247     private boolean addFromTable(
248         final String schema,
249         final String table,
250         final String alias,
251         final String filter,
252         final boolean failIfExists)
253     {
254         if (fromAliases.contains(alias)) {
255             if (failIfExists) {
256                 throw Util.newInternal(
257                         "query already contains alias '" + alias + "'");
258             } else {
259                 return false;
260             }
261         }
262
263         buf.setLength(0);
264         dialect.quoteIdentifier(schema, table, buf);
265         if (alias != null) {
266             Util.assertTrue(alias.length() > 0);
267
268             if (dialect.allowsAs()) {
269                 buf.append(" as ");
270             } else {
271                 buf.append(' ');
272             }
273             dialect.quoteIdentifier(alias, buf);
274             fromAliases.add(alias);
275         }
276
277         from.add(buf.toString());
278
279         if (filter != null) {
280             // append filter condition to where clause
281 addWhere("(", filter, ")");
282         }
283         return true;
284     }
285
286     public void addFrom(final SqlQuery sqlQuery,
287                         final String alias,
288                         final boolean failIfExists)
289     {
290         addFromQuery(sqlQuery.toString(), alias, failIfExists);
291     }
292
293     /**
294      * Adds a relation to a query, adding appropriate join conditions, unless
295      * it is already present.
296      *
297      * <p>Returns whether the relation was added to the query.
298      *
299      * @param relation Relation to add
300      * @param alias Alias of relation. If null, uses relation's alias.
301      * @param failIfExists Whether to fail if relation is already present
302      * @return true, if relation *was* added to query
303      */
304     public boolean addFrom(final MondrianDef.Relation relation,
305                            final String alias,
306                            final boolean failIfExists)
307     {
308         if (relation instanceof MondrianDef.View) {
309             final MondrianDef.View view = (MondrianDef.View) relation;
310             final String viewAlias = (alias == null)
311                     ? view.getAlias()
312                     : alias;
313             final String sqlString = dialect.chooseQuery(view.selects);
314
315             return addFromQuery(sqlString, viewAlias, false);
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
323             return addFromTable(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(leftAlias, join.leftKey, buf);
339                 buf.append(" = ");
340                 dialect.quoteIdentifier(rightAlias, join.rightKey, buf);
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             from.toBuffer(buf, " from ", ", ");
464             where.toBuffer(buf, " where ", " and ");
465             groupBy.toBuffer(buf, " group by ", ", ");
466             having.toBuffer(buf, " having ", " and ");
467             orderBy.toBuffer(buf, " order by ", ", ");
468
469             return buf.toString();
470         }
471     }
472
473     /**
474      * Prints this SqlQuery to a PrintWriter with each clause on a separate
475      * line, and with the specified indentation prefix.
476      *
477      * @param pw Print writer
478      * @param prefix Prefix for each line
479      */
480     public void print(PrintWriter pw, String prefix) {
481         select.print(pw, prefix,
482             distinct ? "select distinct " : "select ", ", ");
483         from.print(pw, prefix, "from ", ", ");
484         where.print(pw, prefix, "where ", " and ");
485         groupBy.print(pw, prefix, "group by ", ", ");
486         having.print(pw, prefix, "having ", " and ");
487         orderBy.print(pw, prefix, "order by ", ", ");
488     }
489
490     public Dialect getDialect() {
491         return dialect;
492     }
493
494     public static SqlQuery newQuery(Connection jdbcConnection, String err) {
495         try {
496             final Dialect dialect =
497                     Dialect.create(jdbcConnection.getMetaData());
498             return new SqlQuery(dialect);
499         } catch (SQLException e) {
500             throw Util.newInternal(e, err);
501         }
502     }
503
504     public static SqlQuery newQuery(DataSource dataSource, String err) {
505         Connection jdbcConnection = null;
506         try {
507             jdbcConnection = dataSource.getConnection();
508             final Dialect dialect =
509                     Dialect.create(jdbcConnection.getMetaData());
510             return new SqlQuery(dialect);
511         } catch (SQLException e) {
512             throw Util.newInternal(e, err);
513         } finally {
514             if (jdbcConnection != null) {
515                 try {
516                     jdbcConnection.close();
517                 } catch (SQLException e) {
518                     // ignore
519 }
520             }
521         }
522     }
523
524     private class ClauseList extends ArrayList <String > {
525         private final boolean allowDups;
526
527         ClauseList(final boolean allowDups) {
528             this.allowDups = allowDups;
529         }
530
531
532         /**
533          * Adds an element to this ClauseList if either duplicates are allowed
534          * or if it has not already been added.
535          *
536          * @param element Element to add
537          * @return whether element was added, per
538          * {@link java.util.Collection#add(Object)}
539          */
540         public boolean add(final String element) {
541             if (allowDups || !contains(element)) {
542                 return super.add(element);
543             }
544             return false;
545         }
546
547         void toBuffer(final StringBuilder buf,
548                       final String first,
549                       final String sep) {
550             boolean firstTime = true;
551             for (String s : this) {
552                 if (firstTime) {
553                     buf.append(first);
554                     firstTime = false;
555                 } else {
556                     buf.append(sep);
557                 }
558                 buf.append(s);
559             }
560         }
561
562         void print(final PrintWriter pw,
563                    final String prefix,
564                    final String first,
565                    final String sep) {
566             String subprefix = prefix + " ";
567             boolean firstTime = true;
568             for (Iterator it = iterator(); it.hasNext(); ) {
569                 String s = (String ) it.next();
570
571                 if (firstTime) {
572                     pw.print(prefix);
573                     pw.print(first);
574                     firstTime = false;
575                 } else {
576                     pw.print(sep);
577                 }
578                 pw.println();
579                 pw.print(subprefix);
580                 pw.print(s);
581             }
582             if (! firstTime) {
583                 pw.println();
584             }
585         }
586     }
587
588     /**
589      * Description of a SQL dialect. It is immutable.
590      */
591     public static class Dialect {
592         private final String quoteIdentifierString;
593         private final String productName;
594         private final String productVersion;
595
596         Dialect(
597                 String quoteIdentifierString,
598                 String productName,
599                 String productVersion) {
600             this.quoteIdentifierString = quoteIdentifierString;
601             this.productName = productName;
602             this.productVersion = productVersion;
603         }
604
605         /**
606          * Creates a {@link SqlQuery.Dialect} from a {@link DatabaseMetaData}.
607          */
608         public static Dialect create(final DatabaseMetaData databaseMetaData) {
609             String productName;
610             try {
611                 productName = databaseMetaData.getDatabaseProductName();
612             } catch (SQLException e1) {
613                 throw Util.newInternal(e1, "while detecting database product");
614             }
615
616             String quoteIdentifierString;
617             try {
618                 quoteIdentifierString =
619                         databaseMetaData.getIdentifierQuoteString();
620             } catch (SQLException e) {
621                 throw Util.newInternal(e, "while quoting identifier");
622             }
623
624             if ((quoteIdentifierString == null) ||
625                     (quoteIdentifierString.trim().length() == 0)) {
626                 if (productName.toUpperCase().equals("MYSQL")) {
627                     // mm.mysql.2.0.4 driver lies. We know better.
628 quoteIdentifierString = "`";
629                 } else {
630                     // Quoting not supported
631 quoteIdentifierString = null;
632                 }
633             }
634
635             String productVersion;
636             try {
637                 productVersion = databaseMetaData.getDatabaseProductVersion();
638             } catch (SQLException e11) {
639                 throw Util.newInternal(e11,
640                         "while detecting database product version");
641             }
642
643             return new Dialect(
644                     quoteIdentifierString,
645                     productName,
646                     productVersion);
647         }
648
649         /**
650          * Creates a {@link SqlQuery.Dialect} from a
651          * {@link javax.sql.DataSource}.
652          *
653          * <p>NOTE: This method is not cheap. The implementation gets a
654          * connection from the connection pool.
655          *
656          * @return Dialect
657          */
658         public static Dialect create(DataSource dataSource) {
659             Connection conn = null;
660             try {
661                 conn = dataSource.getConnection();
662                 return create(conn.getMetaData());
663             } catch (SQLException e) {
664                 throw Util.newInternal(
665                     e, "Error while creating SQL dialect");
666             } finally {
667                 try {
668                     if (conn != null) {
669                         conn.close();
670                     }
671                 } catch (SQLException e) {
672                     // ignore
673 }
674             }
675         }
676
677         // -- detect various databases --
678
679         public boolean isAccess() {
680             return productName.equals("ACCESS");
681         }
682
683         public boolean isDerby() {
684             return productName.trim().toUpperCase().equals("APACHE DERBY");
685         }
686
687         public boolean isCloudscape() {
688             return productName.trim().toUpperCase().equals("DBMS:CLOUDSCAPE");
689         }
690
691         public boolean isDB2() {
692             // DB2 on NT returns "DB2/NT"
693 return productName.startsWith("DB2");
694         }
695
696         public boolean isAS400() {
697             // DB2/AS400 Product String = "DB2 UDB for AS/400"
698 return productName.startsWith("DB2 UDB for AS/400");
699         }
700
701         public boolean isOldAS400() {
702             if (!isAS400()) {
703                 return false;
704             }
705             // TB "04.03.0000 V4R3m0"
706 // this version cannot handle subqueries and is considered "old"
707 // DEUKA "05.01.0000 V5R1m0" is ok
708 String [] version_release = productVersion.split("\\.", 3);
709             /*
710             if ( version_release.length > 2 &&
711                 "04".compareTo(version_release[0]) > 0 ||
712                 ("04".compareTo(version_release[0]) == 0
713                 && "03".compareTo(version_release[1]) >= 0) )
714                 return true;
715             */
716             // assume, that version <= 04 is "old"
717 return ("04".compareTo(version_release[0]) >= 0);
718         }
719
720         // Note: its not clear that caching the best name would actually save
721 // very much time, so we do not do so.
722 private String getBestName() {
723             String best;
724             if (isOracle()) {
725                 best = "oracle";
726             } else if (isMSSQL()) {
727                 best = "mssql";
728             } else if (isMySQL()) {
729                 best = "mysql";
730             } else if (isAccess()) {
731                 best = "access";
732             } else if (isPostgres()) {
733                 best = "postgres";
734             } else if (isSybase()) {
735                 best = "sybase";
736             } else if (isCloudscape() || isDerby()) {
737                 best = "derby";
738             } else if (isDB2()) {
739                 best = "db2";
740             } else if (isFirebird()) {
741                 best = "firebird";
742             } else if (isInterbase()) {
743                 best = "interbase";
744             } else if (isIngres()) {
745                 best = "ingres";
746             } else if (isLucidDB()) {
747                 best = "luciddb";
748             } else if (isTeradata()) {
749                 best = "teradata";
750             } else {
751                 best = "generic";
752             }
753             return best;
754         }
755
756         /**
757          * @return SQL syntax that converts <code>expr</code>
758          * into upper case.
759          */
760         public String toUpper(String expr) {
761             if (isDB2() || isAccess())
762                 return "UCASE(" + expr + ")";
763             return "UPPER(" + expr + ")";
764         }
765
766         public String caseWhenElse(String cond, String thenExpr, String elseExpr) {
767             if (isAccess()) {
768                 return "IIF(" + cond + "," + thenExpr + "," + elseExpr + ")";
769             }
770             return "CASE WHEN " + cond + " THEN " + thenExpr + " ELSE " + elseExpr + " END";
771         }
772
773         /**
774          * Encloses an identifier in quotation marks appropriate for the
775          * current SQL dialect. For example,
776          * <code>quoteIdentifier("emp")</code> yields a string containing
777          * <code>"emp"</code> in Oracle, and a string containing
778          * <code>[emp]</code> in Access.
779          */
780         public String quoteIdentifier(final String val) {
781             int size = val.length() + SINGLE_QUOTE_SIZE;
782             StringBuilder buf = new StringBuilder (size);
783
784             quoteIdentifier(val, buf);
785
786             return buf.toString();
787         }
788
789         /**
790          * Appends to a buffer an identifier, quoted appropriately for this
791          * Dialect.
792          *
793          * @param val identifier to quote (must not be null).
794          * @param buf Buffer
795          */
796         public void quoteIdentifier(final String val, final StringBuilder buf) {
797             String q = getQuoteIdentifierString();
798             if (q == null) {
799                 // quoting is not supported
800 buf.append(val);
801                 return;
802             }
803             // if the value is already quoted, do nothing
804 // if not, then check for a dot qualified expression
805 // like "owner.table".
806 // In that case, prefix the single parts separately.
807 if (val.startsWith(q) && val.endsWith(q)) {
808                 // already quoted - nothing to do
809 buf.append(val);
810                 return;
811             }
812
813             int k = val.indexOf('.');
814             if (k > 0) {
815                 // qualified
816 String val1 = Util.replace(val.substring(0,k), q, q + q);
817                 String val2 = Util.replace(val.substring(k+1), q, q + q);
818                 buf.append(q);
819                 buf.append(val1);
820                 buf.append(q);
821                 buf.append(".");
822                 buf.append(q);
823                 buf.append(val2);
824                 buf.append(q);
825
826             } else {
827                 // not Qualified
828 String val2 = Util.replace(val, q, q + q);
829                 buf.append(q);
830                 buf.append(val2);
831                 buf.append(q);
832             }
833         }
834
835         /**
836          * Encloses an identifier in quotation marks appropriate for the
837          * current SQL dialect. For example, in Oracle, where the identifiers
838          * are quoted using double-quotes,
839          * <code>quoteIdentifier("schema","table")</code> yields a string
840          * containing <code>"schema"."table"</code>.
841          *
842          * @param qual Qualifier. If it is not null,
843          * <code>"<em>qual</em>".</code> is prepended.
844          * @param name Name to be quoted.
845          */
846         public String quoteIdentifier(final String qual, final String name) {
847             // We know if the qalifier is null, then only the name is going
848 // to be quoted.
849 int size = name.length()
850                 + ((qual == null)
851                     ? SINGLE_QUOTE_SIZE
852                     : (qual.length() + DOUBLE_QUOTE_SIZE));
853             StringBuilder buf = new StringBuilder (size);
854
855             quoteIdentifier(qual, name, buf);
856
857             return buf.toString();
858         }
859
860         /**
861          * Appends to a buffer an identifier and optional qualifier, quoted
862          * appropriately for this Dialect.
863          *
864          * @param qual optional qualifier to be quoted.
865          * @param name name to be quoted (must not be null).
866          * @param buf Buffer
867          */
868         public void quoteIdentifier(
869             final String qual,
870             final String name,
871             final StringBuilder buf)
872         {
873             if (qual == null) {
874                 quoteIdentifier(name, buf);
875
876             } else {
877                 Util.assertTrue(
878                     (qual.length() != 0),
879                     "qual should probably be null, not empty");
880
881                 quoteIdentifier(qual, buf);
882                 buf.append('.');
883                 quoteIdentifier(name, buf);
884             }
885         }
886
887         /**
888          * Returns the character which is used to quote identifiers, or null
889          * if quoting is not supported.
890          */
891         public String getQuoteIdentifierString() {
892             if (isDB2()) {
893                 return "";
894             } else {
895                 return quoteIdentifierString;
896             }
897         }
898
899         /**
900          * Appends to a buffer a single-quoted SQL string.
901          *
902          * <p>For example, in the default dialect,
903          * <code>quoteStringLiteral(buf, "Can't")</code> appends
904          * "<code>'Can''t'</code>" to <code>buf</code>.
905          */
906         public void quoteStringLiteral(StringBuilder buf, String s) {
907             Util.singleQuoteString(s, buf);
908         }
909
910         /**
911          * Appends to a buffer a numeric literal.
912          *
913          * <p>In the default dialect, numeric literals are printed as is.
914          */
915         public void quoteNumericLiteral(StringBuilder buf, String value) {
916             buf.append(value);
917         }
918
919         /**
920          * Appends to a buffer a boolean literal.
921          *
922          * <p>In the default dialect, boolean literals are printed as is.
923          */
924         public void quoteBooleanLiteral(StringBuilder buf, String value) {
925             // NOTE jvs 1-Jan-2007: See quoteDateLiteral for explanation.
926 // In addition, note that we leave out UNKNOWN (even though
927 // it is a valid SQL:2003 literal) because it's really
928 // NULL in disguise, and NULL is always treated specially.
929 if (!value.equalsIgnoreCase("TRUE")
930                 && !(value.equalsIgnoreCase("FALSE"))) {
931                 throw new NumberFormatException (
932                     "Illegal BOOLEAN literal: " + value);
933             }
934             buf.append(value);
935         }
936
937         /**
938          * Appends to a buffer a date literal.
939          *
940          * <p>For example, in the default dialect,
941          * <code>quoteStringLiteral(buf, "1969-03-17")</code>
942          * appends <code>DATE '1969-03-17'</code>.
943          */
944         public void quoteDateLiteral(StringBuilder buf, String value) {
945             // NOTE jvs 1-Jan-2007: Check that the supplied literal is in valid
946 // SQL:2003 date format. A hack in
947 // RolapSchemaReader.lookupMemberChildByName looks for
948 // NumberFormatException to suppress it, so that is why
949 // we convert the exception here.
950 try {
951                 java.sql.Date.valueOf(value);
952             } catch (IllegalArgumentException ex) {
953                 throw new NumberFormatException (
954                     "Illegal DATE literal: " + value);
955             }
956             buf.append("DATE ");
957             Util.singleQuoteString(value, buf);
958         }
959
960         /**
961          * Appends to a buffer a time literal.
962          *
963          * <p>For example, in the default dialect,
964          * <code>quoteStringLiteral(buf, "12:34:56")</code>
965          * appends <code>TIME '12:34:56'</code>.
966          */
967         public void quoteTimeLiteral(StringBuilder buf, String value) {
968             // NOTE jvs 1-Jan-2007: See quoteDateLiteral for explanation.
969 try {
970                 java.sql.Time.valueOf(value);
971             } catch (IllegalArgumentException ex) {
972                 throw new NumberFormatException (
973                     "Illegal TIME literal: " + value);
974             }
975             buf.append("TIME ");
976             Util.singleQuoteString(value, buf);
977         }
978
979         /**
980          * Appends to a buffer a timestamp literal.
981          *
982          * <p>For example, in the default dialect,
983          * <code>quoteStringLiteral(buf, "1969-03-17 12:34:56")</code>
984          * appends <code>TIMESTAMP '1969-03-17 12:34:56'</code>.
985          */
986         public void quoteTimestampLiteral(StringBuilder buf, String value) {
987             // NOTE jvs 1-Jan-2007: See quoteTimestampLiteral for explanation.
988 try {
989                 java.sql.Timestamp.valueOf(value);
990             } catch (IllegalArgumentException ex) {
991                 throw new NumberFormatException (
992                     "Illegal TIMESTAMP literal: " + value);
993             }
994             buf.append("TIMESTAMP ");
995             Util.singleQuoteString(value, buf);
996         }
997
998         /**
999          * Returns whether the underlying database is Firebird.
1000         */
1001        public boolean isFirebird() {
1002            return productName.toUpperCase().indexOf("FIREBIRD") >= 0;
1003        }
1004
1005        /**
1006         * Returns whether the underlying database is Informix.
1007         */
1008        public boolean isInformix() {
1009            return productName.startsWith("Informix");
1010        }
1011
1012        /**
1013         * Returns whether the underlying database is Ingres.
1014         */
1015        public boolean isIngres() {
1016            return productName.toUpperCase().equals("INGRES");
1017        }
1018
1019        /**
1020         * Returns whether the underlying database is Interbase.
1021         */
1022        public boolean isInterbase() {
1023            return productName.equals("Interbase");
1024        }
1025
1026        /**
1027         * Returns whether the underlying database is LucidDB.
1028         */
1029        public boolean isLucidDB() {
1030            return productName.toUpperCase().equals("LUCIDDB");
1031        }
1032
1033        /**
1034         * Returns whether the underlying database is Microsoft SQL Server.
1035         */
1036        public boolean isMSSQL() {
1037            return productName.toUpperCase().indexOf("SQL SERVER") >= 0;
1038        }
1039
1040        /**
1041         * Returns whether the underlying database is Oracle.
1042         */
1043        public boolean isOracle() {
1044            return productName.equals("Oracle");
1045        }
1046
1047        /**
1048         * Returns whether the underlying database is Postgres.
1049         */
1050        public boolean isPostgres() {
1051            return productName.toUpperCase().indexOf("POSTGRE") >= 0;
1052        }
1053
1054        /**
1055         * Returns whether the underlying database is MySQL.
1056         */
1057        public boolean isMySQL() {
1058            return productName.toUpperCase().equals("MYSQL");
1059        }
1060
1061        /**
1062         * Returns whether the underlying database is Sybase.
1063         */
1064        public boolean isSybase() {
1065            return productName.toUpperCase().indexOf("SYBASE") >= 0;
1066        }
1067
1068        /**
1069         * Returns whether the underlying database is Teradata.
1070         */
1071        public boolean isTeradata() {
1072            return productName.toUpperCase().indexOf("SYBASE") >= 0;
1073        }
1074
1075        // -- behaviors --
1076 protected boolean requiresAliasForFromItems() {
1077            return isPostgres();
1078        }
1079
1080        /**
1081         * Returns whether the SQL dialect allows "AS" in the FROM clause.
1082         * If so, "SELECT * FROM t AS alias" is a valid query.
1083         */
1084        protected boolean allowsAs() {
1085            return !isOracle() && !isSybase() && !isFirebird() &&
1086                !isInterbase();
1087        }
1088
1089        /**
1090         * Whether "select * from (select * from t)" is OK.
1091         */
1092        public boolean allowsFromQuery() {
1093            // older versions of AS400 do not allow FROM subqueries
1094 return !isMySQL() && !isOldAS400() && !isInformix() &&
1095                !isSybase() && !isInterbase();
1096        }
1097
1098        /**
1099         * Whether "select count(distinct x, y) from t" is OK.
1100         */
1101        public boolean allowsCompoundCountDistinct() {
1102            return isMySQL();
1103        }
1104
1105        /**
1106         * Returns whether this Dialect supports distinct aggregations.
1107         *
1108         * <p>For example, Access does not allow
1109         * <blockquote>
1110         * <code>select count(distinct x) from t</code>
1111         * </blockquote>
1112         */
1113        public boolean allowsCountDistinct() {
1114            return !isAccess();
1115        }
1116
1117        /**
1118         * Returns whether this Dialect supports more than one distinct
1119         * aggregation in the same query.
1120         *
1121         * <p>In Derby 10.1,
1122         * <blockquote>
1123         * <code>select couunt(distinct x) from t</code>
1124         * </blockquote>
1125         * is OK, but
1126         * <blockquote>
1127         * <code>select couunt(distinct x), count(distinct y) from t</code>
1128         * </blockquote>
1129         * gives "Multiple DISTINCT aggregates are not supported at this time."
1130         *
1131         * @return whether this Dialect supports more than one distinct
1132         * aggregation in the same query
1133         */
1134        public boolean allowsMultipleCountDistinct() {
1135            return allowsCountDistinct() &&
1136                !isDerby();
1137        }
1138
1139        /**
1140         * Chooses the variant within an array of
1141         * {@link mondrian.olap.MondrianDef.SQL} which best matches the current
1142         * SQL dialect.
1143         */
1144        public String chooseQuery(final MondrianDef.SQL[] sqls) {
1145            String best = getBestName();
1146
1147            String generic = null;
1148            for (MondrianDef.SQL sql : sqls) {
1149                if (sql.dialect.equals(best)) {
1150                    return sql.cdata;
1151                }
1152                if (sql.dialect.equals("generic")) {
1153                    generic = sql.cdata;
1154                }
1155            }
1156            if (generic == null) {
1157                throw Util.newError("View has no 'generic' variant");
1158            }
1159            return generic;
1160        }
1161
1162        /**
1163         * Generates a SQL statement to represent an inline dataset.
1164         *
1165         * <p>For example, for Oracle, generates
1166         *
1167         * <pre>
1168         * SELECT 1 AS FOO, 'a' AS BAR FROM dual
1169         * UNION ALL
1170         * SELECT 2 AS FOO, 'b' AS BAR FROM dual
1171         * </pre>
1172         *
1173         * <p>For ANSI SQL, generates:
1174         *
1175         * <pre>
1176         * VALUES (1, 'a'), (2, 'b')
1177         * </pre>
1178         *
1179         * @param columnNames List of column names
1180         * @param columnTypes List of column types ("String" or "Numeric")
1181         * @param valueList List of rows values
1182         * @return SQL string
1183         */
1184        public String generateInline(
1185                List <String > columnNames,
1186                List <String > columnTypes,
1187                List <String []> valueList) {
1188            if (isOracle()) {
1189                return generateInlineGeneric(
1190                    columnNames, columnTypes, valueList,
1191                    "from dual");
1192            } else if (isAccess()) {
1193                // Fall back to using the FoodMart 'days' table, because
1194 // Access SQL has no way to generate values not from a table.
1195 return generateInlineGeneric(
1196                    columnNames, columnTypes, valueList,
1197                    "from [days] where [day] = 1");
1198            } else if (isMySQL() || isIngres()) {
1199                return generateInlineGeneric(
1200                        columnNames, columnTypes, valueList, null);
1201            } else if (isLucidDB()) {
1202                // TODO jvs 26-Nov-2006: Eliminate this once LucidDB
1203 // can support applying column names to a VALUES clause
1204 // (needed by generateInlineForAnsi).
1205 return generateInlineGeneric(
1206                    columnNames, columnTypes, valueList,
1207                    " from (values(0))");
1208            } else {
1209                return generateInlineForAnsi(
1210                    "t", columnNames, columnTypes, valueList);
1211            }
1212        }
1213
1214        /**
1215          * Generic algorithm to generate inline values list,
1216          * using an optional FROM clause, specified by the caller of this
1217          * method, appropriate to the dialect of SQL.
1218          */
1219        private String generateInlineGeneric(
1220                List <String > columnNames,
1221                List <String > columnTypes,
1222                List <String []> valueList,
1223                String fromClause) {
1224            final StringBuilder buf = new StringBuilder ();
1225            for (int i = 0; i < valueList.size(); i++) {
1226                if (i > 0) {
1227                    buf.append(" union all ");
1228                }
1229                String [] values = valueList.get(i);
1230                buf.append("select ");
1231                for (int j = 0; j < values.length; j++) {
1232                    String value = values[j];
1233                    if (j > 0) {
1234                        buf.append(", ");
1235                    }
1236                    final String columnType = columnTypes.get(j);
1237                    final String columnName = columnNames.get(j);
1238                    Datatype datatype = Datatype.valueOf(columnType);
1239                    quote(buf, value, datatype);
1240                    if (allowsAs()) {
1241                        buf.append(" as ");
1242                    } else {
1243                        buf.append(' ');
1244                    }
1245                    quoteIdentifier(columnName, buf);
1246                }
1247                if (fromClause != null) {
1248                    buf.append(fromClause);
1249                }
1250            }
1251            return buf.toString();
1252        }
1253
1254        /**
1255         * Generates inline values list using ANSI 'VALUES' syntax.
1256         * For example,
1257         *
1258         * <blockquote><code>SELECT * FROM
1259         * (VALUES (1, 'a'), (2, 'b')) AS t(x, y)</code></blockquote>
1260         *
1261         * <p>If NULL values are present, we use a CAST to ensure that they
1262         * have the same type as other columns:
1263         *
1264         * <blockquote><code>SELECT * FROM
1265         * (VALUES (1, 'a'), (2, CASE(NULL AS VARCHAR(1)))) AS t(x, y)
1266         * </code></blockquote>
1267         *
1268         * <p>This syntax is known to work on Derby, but not Oracle 10 or
1269         * Access.
1270         */
1271        private String generateInlineForAnsi(
1272                String alias,
1273                List <String > columnNames,
1274                List <String > columnTypes,
1275                List <String []> valueList) {
1276            final StringBuilder buf = new StringBuilder ();
1277            buf.append("SELECT * FROM (VALUES ");
1278            // Derby pads out strings to a common length, so we cast the
1279 // string values to avoid this. Determine the cast type for each
1280 // column.
1281 String [] castTypes = null;
1282            if (isDerby()) {
1283                castTypes = new String [columnNames.size()];
1284                for (int i = 0; i < columnNames.size(); i++) {
1285                    String columnType = columnTypes.get(i);
1286                    if (columnType.equals("String")) {
1287                        castTypes[i] =
1288                            guessSqlType(columnType, valueList, i);
1289                    }
1290                }
1291            }
1292            for (int i = 0; i < valueList.size(); i++) {
1293                if (i > 0) {
1294                    buf.append(", ");
1295                }
1296                String [] values = valueList.get(i);
1297                buf.append("(");
1298                for (int j = 0; j < values.length; j++) {
1299                    String value = values[j];
1300                    if (j > 0) {
1301                        buf.append(", ");
1302                    }
1303                    final String columnType = columnTypes.get(j);
1304                    Datatype datatype = Datatype.valueOf(columnType);
1305                    if (value == null) {
1306                        String sqlType =
1307                            guessSqlType(columnType, valueList, j);
1308                        buf.append("CAST(NULL AS ")
1309                            .append(sqlType)
1310                            .append(")");
1311                    } else if (isDerby() && castTypes[j] != null) {
1312                        buf.append("CAST(");
1313                        quote(buf, value, datatype);
1314                        buf.append(" AS ")
1315                            .append(castTypes[j])
1316                            .append(")");
1317                    } else {
1318                        quote(buf, value, datatype);
1319                    }
1320                }
1321                buf.append(")");
1322            }
1323            buf.append(") AS ");
1324            quoteIdentifier(alias, buf);
1325            buf.append(" (");
1326            for (int j = 0; j < columnNames.size(); j++) {
1327                final String columnName = columnNames.get(j);
1328                if (j > 0) {
1329                    buf.append(", ");
1330                }
1331                quoteIdentifier(columnName, buf);
1332            }
1333            buf.append(")");
1334            return buf.toString();
1335        }
1336
1337        /**
1338         * Appends to a buffer a value quoted for its type.
1339         */
1340        public void quote(StringBuilder buf, Object value, Datatype datatype) {
1341            if (value == null) {
1342                buf.append("null");
1343            } else {
1344                datatype.quoteValue(buf, this, value.toString());
1345            }
1346        }
1347
1348        /**
1349         * Guesses the type of a column based upon (a) its basic type,
1350         * (b) a list of values.
1351         */
1352        private static String guessSqlType(
1353                String basicType, List <String []> valueList, int column) {
1354            if (basicType.equals("String")) {
1355                int maxLen = 1;
1356                for (String [] values : valueList) {
1357                    final String value = values[column];
1358                    if (value == null) {
1359                        continue;
1360                    }
1361                    maxLen = Math.max(maxLen, value.length());
1362                }
1363                return "VARCHAR(" + maxLen + ")";
1364            } else {
1365                return "INTEGER";
1366            }
1367        }
1368
1369        /**
1370         * Returns whether this dialect supports common SQL Data Definition
1371         * Language (DDL) statements such as <code>CREATE TABLE</code> and
1372         * <code>DROP INDEX</code>.
1373         */
1374        public boolean allowsDdl() {
1375            return !isAccess();
1376        }
1377
1378        /**
1379         * Returns whether NULL values appear last when sorted using ORDER BY.
1380         * According to the SQL standard, this is implementation-specific.
1381         */
1382        public boolean isNullsCollateLast() {
1383            if (isMySQL()) {
1384                return false;
1385            }
1386            return true;
1387        }
1388
1389        /**
1390         * Modifies an expression in the ORDER BY clause to ensure that NULL
1391         * values collate after all non-NULL values.
1392         * If {@link #isNullsCollateLast()} is true, there's nothing to do.
1393         */
1394        public String forceNullsCollateLast(String expr) {
1395            // If we need to support other DBMSes, note that the SQL standard
1396 // provides the syntax 'ORDER BY x ASC NULLS LAST'.
1397 if (isMySQL()) {
1398                String addIsNull = "ISNULL(" + expr + "), ";
1399                expr = addIsNull + expr;
1400            }
1401            return expr;
1402        }
1403
1404        /**
1405         * Returns whether this Dialect supports expressions in the GROUP BY
1406         * clause. Derby/Cloudscape do not.
1407         *
1408         * @return Whether this Dialect allows expressions in the GROUP BY
1409         * clause
1410         */
1411        public boolean supportsGroupByExpressions() {
1412            return !(isDerby() || isCloudscape());
1413        }
1414
1415        /**
1416         * Returns true if this Dialect can include expressions in the ORDER BY
1417         * clause only by adding an expression to the SELECT clause and using
1418         * its alias.
1419         *
1420         * <p>For example, in such a dialect,
1421         * <blockquote>
1422         * <code>SELECT x FROM t ORDER BY x + y</code>
1423         * </blockquote>
1424         * would be illegal, but
1425         * <blockquote>
1426         * <code>SELECT x, x + y AS z FROM t ORDER BY z</code>
1427         * </blockquote>
1428         *
1429         * would be legal.</p>
1430         *
1431         * <p>MySQL, DB2 and Ingres are examples of such dialects.</p>
1432         *
1433         * @return Whether this Dialect can include expressions in the ORDER BY
1434         * clause only by adding an expression to the SELECT clause and using
1435         * its alias
1436         */
1437        public boolean requiresOrderByAlias() {
1438            return isMySQL() || isDB2() || isIngres();
1439        }
1440
1441        /**
1442         * Returns true if this dialect supports multi-value IN expressions.
1443         * E.g.,
1444         *
1445         * <code>WHERE (col1, col2) IN ((val1a, val2a), (val1b, val2b))</code>
1446         *
1447         * @return true if the dialect supports multi-value IN expressions
1448         */
1449        public boolean supportsMultiValueInExpr() {
1450            return isLucidDB() || isMySQL();
1451        }
1452    }
1453
1454    /**
1455     * Datatype of a column.
1456     */
1457    public enum Datatype {
1458        String {
1459            public void quoteValue(StringBuilder buf, Dialect dialect, String value) {
1460                dialect.quoteStringLiteral(buf, value);
1461            }
1462        },
1463
1464        Numeric {
1465            public void quoteValue(StringBuilder buf, Dialect dialect, String value) {
1466                dialect.quoteNumericLiteral(buf, value);
1467            }
1468
1469            public boolean isNumeric() {
1470                return true;
1471            }
1472        },
1473
1474        Integer {
1475            public void quoteValue(StringBuilder buf, Dialect dialect, String value) {
1476                dialect.quoteNumericLiteral(buf, value);
1477            }
1478
1479            public boolean isNumeric() {
1480                return true;
1481            }
1482        },
1483
1484        Boolean {
1485            public void quoteValue(StringBuilder buf, Dialect dialect, String value) {
1486                dialect.quoteBooleanLiteral(buf, value);
1487            }
1488        },
1489
1490        Date {
1491            public void quoteValue(StringBuilder buf, Dialect dialect, String value) {
1492                dialect.quoteDateLiteral(buf, value);
1493            }
1494        },
1495
1496        Time {
1497            public void quoteValue(StringBuilder buf, Dialect dialect, String value) {
1498                dialect.quoteTimeLiteral(buf, value);
1499            }
1500        },
1501
1502        Timestamp {
1503            public void quoteValue(StringBuilder buf, Dialect dialect, String value) {
1504                dialect.quoteTimestampLiteral(buf, value);
1505            }
1506        };
1507
1508        /**
1509         * Appends to a buffer a value of this type, in the appropriate format
1510         * for this dialect.
1511         *
1512         * @param buf Buffer
1513         * @param dialect Dialect
1514         * @param value Value
1515         */
1516        public abstract void quoteValue(
1517            StringBuilder buf,
1518            Dialect dialect,
1519            String value);
1520
1521        public boolean isNumeric() {
1522            return false;
1523        }
1524    }
1525}
1526
1527// End SqlQuery.java
1528

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags