View Javadoc

1   package org.apache.torque.util;
2   
3   /*
4    * Copyright 2001-2005 The Apache Software Foundation.
5    *
6    * Licensed under the Apache License, Version 2.0 (the "License")
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    *
10   *     http://www.apache.org/licenses/LICENSE-2.0
11   *
12   * Unless required by applicable law or agreed to in writing, software
13   * distributed under the License is distributed on an "AS IS" BASIS,
14   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   * See the License for the specific language governing permissions and
16   * limitations under the License.
17   */
18  
19  import java.io.Serializable;
20  import java.util.HashSet;
21  import java.util.Iterator;
22  import java.util.List;
23  import java.util.Map;
24  import java.util.Set;
25  
26  import org.apache.commons.lang.StringUtils;
27  import org.apache.commons.logging.Log;
28  import org.apache.commons.logging.LogFactory;
29  import org.apache.torque.Torque;
30  import org.apache.torque.TorqueException;
31  import org.apache.torque.adapter.DB;
32  import org.apache.torque.map.ColumnMap;
33  import org.apache.torque.map.DatabaseMap;
34  import org.apache.torque.util.Criteria.Criterion;
35  
36  /***
37   * Factored out code that is used to process SQL tables. This code comes
38   * from BasePeer and is put here to reduce complexity in the BasePeer class.
39   * You should not use the methods here directly!
40   *
41   * @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
42   * @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a>
43   * @version $Id: SQLBuilder.java 239636 2005-08-24 12:38:09Z henning $
44   */
45  public abstract class SQLBuilder
46          implements Serializable
47  {
48      /*** Logging */
49      protected static Log log = LogFactory.getLog(SQLBuilder.class);
50  
51      /***
52       * Fully qualify a table name with an optional schema reference
53       *
54       * @param table The table name to use. If null is passed in, null is returned.
55       * @param dbName The name of the database to which this tables belongs.
56       *               If null is passed, the default database is used.
57       *
58       * @return The table name to use inside the SQL statement. If null is passed
59       *         into this method, null is returned.
60       * @exception TorqueException if an error occurs
61       */
62      public static final String getFullTableName(final String table, final String dbName)
63              throws TorqueException
64      {
65          if (table != null)
66          {
67              int dotIndex = table.indexOf(".");
68  
69              if (dotIndex == -1) // No schema given
70              {
71                  String targetDBName = (dbName == null)
72                          ? Torque.getDefaultDB()
73                          : dbName;
74  
75                  String targetSchema = Torque.getSchema(targetDBName);
76  
77                  // If we have a default schema, fully qualify the
78                  // table and return.
79                  if (StringUtils.isNotEmpty(targetSchema))
80                  {
81                      return new StringBuffer()
82                              .append(targetSchema)
83                              .append(".")
84                              .append(table)
85                              .toString();
86                  }
87              }
88          }
89  
90          return table;
91      }
92  
93      /***
94       * Remove a possible schema name from the table name.
95       *
96       * @param table The table name to use
97       *
98       * @return The table name with a possible schema name
99       *         stripped off
100      */
101     public static final String getUnqualifiedTableName(final String table)
102     {
103         if (table != null)
104         {
105             int dotIndex = table.lastIndexOf("."); // Do we have a dot?
106 
107             if (++dotIndex > 0) // Incrementation allows for better test _and_ substring...
108             {
109                 return table.substring(dotIndex);
110             }
111         }
112 
113         return table;
114     }
115 
116     /***
117      * Removes a possible function name or clause from a column name
118      *
119      * @param name The column name, possibly containing a clause
120      *
121      * @return The column name
122      *
123      * @throws TorqueException If the column name was malformed
124      */
125     private static String removeSQLFunction(final String name)
126             throws TorqueException
127     {
128         // Empty name => return it
129         if (StringUtils.isEmpty(name))
130         {
131             return name;
132         }
133 
134         final int leftParent = name.lastIndexOf('(');
135         final int rightParent = name.indexOf(')');
136 
137         // Do we have Parentheses?
138         if (leftParent < 0)
139         {
140             if (rightParent < 0)
141             {
142                 // No left, no right => No function ==> return it
143                 return name;
144             }
145         }
146 
147         // We have a left parenthesis. Is the right one behind it?
148         if (rightParent > leftParent)
149         {
150             // Yes. Strip off the function, return the column name
151             return name.substring(leftParent + 1, rightParent);
152         }
153 
154         // Bracket mismatch or wrong order ==> Exception
155         throwMalformedColumnNameException(
156                 "removeSQLFunction",
157                 name);
158 
159         return null; // Ugh
160     }
161 
162     /***
163      * Removes possible qualifiers (like DISTINCT) from a column name
164      *
165      * @param name The column name, possibly containing qualifiers
166      *
167      * @return The column name
168      *
169      * @throws TorqueException If the column name was malformed
170      */
171     private static String removeQualifiers(final String name)
172             throws TorqueException
173     {
174         // Empty name => return it
175         if (StringUtils.isEmpty(name))
176         {
177             return name;
178         }
179 
180         final int spacePos = name.trim().lastIndexOf(' ');
181 
182         // Do we have spaces, indicating that qualifiers are used ?
183         if (spacePos > 0)
184         {
185             // Qualifiers are first, tablename is piece after last space
186             return name.trim().substring(spacePos + 1);
187         }
188 
189         // no spaces, nothing changed
190         return name;
191     }
192 
193 
194     /***
195      * Returns a table name from an identifier. Each identifier is to be qualified
196      * as [schema.]table.column. This could also contain FUNCTION([schema.]table.column).
197      *
198      * @param name The (possible fully qualified) identifier name
199      *
200      * @return the fully qualified table name
201      *
202      * @throws TorqueException If the identifier name was malformed
203      */
204     public static String getTableName(final String name, final String dbName)
205             throws TorqueException
206     {
207         final String testName = removeQualifiers(removeSQLFunction(name));
208 
209         if (StringUtils.isEmpty(testName))
210         {
211             throwMalformedColumnNameException(
212                     "getTableName",
213                     name);
214         }
215 
216         // Everything before the last dot is the table name
217         int rightDotIndex = testName.lastIndexOf('.');
218 
219         if (rightDotIndex < 0)
220         {
221             if ("*".equals(testName))
222             {
223                 return null;
224             }
225 
226             throwMalformedColumnNameException(
227                     "getTableName",
228                     name);
229         }
230 
231         return getFullTableName(testName.substring(0, rightDotIndex), dbName);
232     }
233 
234 
235 
236     /***
237      * Returns a set of all tables and possible aliases referenced
238      * from a criterion. The resulting Set can be directly used to
239      * build a WHERE clause
240      *
241      * @param crit A Criteria object
242      * @param tableCallback A Callback Object
243      * @return A Set of tables.
244      */
245     public static final Set getTableSet(
246             final Criteria crit,
247             final TableCallback tableCallback)
248     {
249         HashSet tables = new HashSet();
250 
251         // Loop over all the Criterions
252         for (Iterator it = crit.keySet().iterator(); it.hasNext(); )
253         {
254             String key = (String) it.next();
255             Criteria.Criterion c = crit.getCriterion(key);
256             List tableNames = c.getAllTables();
257 
258             // Loop over all Tables referenced in this criterion.
259             for (Iterator it2 = tableNames.iterator(); it2.hasNext(); )
260             {
261                 String name = (String) it2.next();
262                 String aliasName = crit.getTableForAlias(name);
263 
264                 // If the tables have an alias, add an "<xxx> AS <yyy> statement"
265                 if (StringUtils.isNotEmpty(aliasName))
266                 {
267                     String newName =
268                             new StringBuffer(name.length() + aliasName.length() + 4)
269                             .append(aliasName)
270                             .append(" AS ")
271                             .append(name)
272                             .toString();
273                     name = newName;
274                 }
275                 tables.add(name);
276             }
277 
278             if (tableCallback != null)
279             {
280                 tableCallback.process(tables, key, crit);
281             }
282         }
283 
284         return tables;
285     }
286 
287     /***
288      * Builds a Query clause for Updating and deleting
289      *
290      * @param crit a <code>Criteria</code> value
291      * @param params a <code>List</code> value
292      * @param qc a <code>QueryCallback</code> value
293      * @return a <code>Query</code> value
294      * @exception TorqueException if an error occurs
295      */
296     public static final Query buildQueryClause(final Criteria crit,
297             final List params,
298             final QueryCallback qc)
299             throws TorqueException
300     {
301         Query query = new Query();
302 
303         final String dbName = crit.getDbName();
304         final DB db = Torque.getDB(dbName);
305         final DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
306 
307         JoinBuilder.processJoins(db, dbMap, crit, query);
308         processModifiers(crit, query);
309         processSelectColumns(crit, query, dbName);
310         processAsColumns(crit, query);
311         processCriterions(db, dbMap, dbName, crit, query,  params, qc);
312         processGroupBy(crit, query);
313         processHaving(crit, query);
314         processOrderBy(db, dbMap, crit, query);
315         LimitHelper.buildLimit(crit, query);
316 
317         if (log.isDebugEnabled())
318         {
319             log.debug(query.toString());
320         }
321         return query;
322     }
323 
324 
325     /***
326      * adds the select columns from the criteria to the query
327      * @param criteria the criteria from which the select columns are taken
328      * @param query the query to which the select columns should be added
329      * @throws TorqueException if the select columns can not be processed
330      */
331     private static final void processSelectColumns(
332             final Criteria criteria,
333             final Query query,
334             final String dbName)
335         throws TorqueException
336     {
337         UniqueList selectClause = query.getSelectClause();
338         UniqueList select = criteria.getSelectColumns();
339 
340         for (int i = 0; i < select.size(); i++)
341         {
342             String identifier = (String) select.get(i);
343             selectClause.add(identifier);
344             addTableToFromClause(getTableName(identifier, dbName), criteria, query);
345         }
346     }
347 
348     /***
349      * adds the As-columns from the criteria to the query.
350      * @param criteria the criteria from which the As-columns are taken
351      * @param query the query to which the As-columns should be added
352      */
353     private static final void processAsColumns(
354             final Criteria criteria,
355             final Query query)
356     {
357         UniqueList querySelectClause = query.getSelectClause();
358         Map criteriaAsColumns = criteria.getAsColumns();
359 
360         for (Iterator it = criteriaAsColumns.keySet().iterator(); it.hasNext(); )
361         {
362             String key = (String) it.next();
363             querySelectClause.add(
364                     new StringBuffer()
365                     .append(criteriaAsColumns.get(key))
366                     .append(SqlEnum.AS)
367                     .append(key)
368                     .toString());
369         }
370     }
371 
372     /***
373      * adds the Modifiers from the criteria to the query
374      * @param criteria the criteria from which the Modifiers are taken
375      * @param query the query to which the Modifiers should be added
376      */
377     private static final void processModifiers(
378             final Criteria criteria,
379             final Query query)
380     {
381         UniqueList selectModifiers = query.getSelectModifiers();
382         UniqueList modifiers = criteria.getSelectModifiers();
383         for (int i = 0; i < modifiers.size(); i++)
384         {
385             selectModifiers.add(modifiers.get(i));
386         }
387     }
388 
389     /***
390      * adds the Criterion-objects from the criteria to the query
391      * @param criteria the criteria from which the Criterion-objects are taken
392      * @param query the query to which the Criterion-objects should be added
393      * @param params the parameters if a prepared statement should be built,
394      *        or null if a normal statement should be built.
395      * @throws TorqueException if the Criterion-objects can not be processed
396      */
397     private static final void processCriterions(
398             final DB db,
399             final DatabaseMap dbMap,
400             final String dbName,
401             final Criteria crit,
402             final Query query,
403             final List params,
404             final QueryCallback qc)
405         throws TorqueException
406     {
407         UniqueList fromClause = query.getFromClause();
408         UniqueList whereClause = query.getWhereClause();
409 
410         for (Iterator it = crit.keySet().iterator(); it.hasNext(); )
411         {
412             String key = (String) it.next();
413             Criteria.Criterion criterion = crit.getCriterion(key);
414             Criteria.Criterion[] someCriteria =
415                     criterion.getAttachedCriterion();
416 
417             String table = null;
418             for (int i = 0; i < someCriteria.length; i++)
419             {
420                 String tableName = someCriteria[i].getTable();
421 
422                 // add the table to the from clause, if it is not already
423                 // contained there
424                 // it is important that this piece of code is executed AFTER
425                 // the joins are processed
426                 addTableToFromClause(getFullTableName(tableName, dbName), crit, query);
427 
428                 table = crit.getTableForAlias(tableName);
429                 if (table == null)
430                 {
431                     table = tableName;
432                 }
433 
434                 boolean ignoreCase =  ((crit.isIgnoreCase() || someCriteria[i].isIgnoreCase())
435                         && (dbMap.getTable(table)
436                                 .getColumn(someCriteria[i].getColumn())
437                                 .getType()
438                                 instanceof String));
439 
440                 someCriteria[i].setIgnoreCase(ignoreCase);
441             }
442 
443             criterion.setDB(db);
444             whereClause.add(qc.process(criterion, params));
445         }
446     }
447 
448     /***
449      * adds the OrderBy-Columns from the criteria to the query
450      * @param criteria the criteria from which the OrderBy-Columns are taken
451      * @param query the query to which the OrderBy-Columns should be added
452      * @throws TorqueException if the OrderBy-Columns can not be processed
453      */
454     private static final void processOrderBy(
455             final DB db,
456             final DatabaseMap dbMap,
457             final Criteria crit,
458             final Query query)
459             throws TorqueException
460     {
461         UniqueList orderByClause = query.getOrderByClause();
462         UniqueList selectClause = query.getSelectClause();
463 
464         UniqueList orderBy = crit.getOrderByColumns();
465 
466         if (orderBy != null && orderBy.size() > 0)
467         {
468             // Check for each String/Character column and apply
469             // toUpperCase().
470             for (int i = 0; i < orderBy.size(); i++)
471             {
472                 String orderByColumn = (String) orderBy.get(i);
473 
474                 String strippedColumnName
475                         = removeSQLFunction(orderByColumn);
476                 int dotPos = strippedColumnName.lastIndexOf('.');
477                 if (dotPos == -1)
478                 {
479                     throwMalformedColumnNameException(
480                             "order by",
481                             orderByColumn);
482                 }
483 
484                 String tableName = strippedColumnName.substring(0, dotPos);
485                 String table = crit.getTableForAlias(tableName);
486                 if (table == null)
487                 {
488                     table = tableName;
489                 }
490 
491                 // See if there's a space (between the column list and sort
492                 // order in ORDER BY table.column DESC).
493                 int spacePos = strippedColumnName.indexOf(' ');
494                 String columnName;
495                 if (spacePos == -1)
496                 {
497                     columnName =
498                             strippedColumnName.substring(dotPos + 1);
499                 }
500                 else
501                 {
502                     columnName = strippedColumnName.substring(
503                             dotPos + 1,
504                             spacePos);
505                 }
506                 ColumnMap column = dbMap.getTable(table).getColumn(columnName);
507 
508                 // only ignore case in order by for string columns
509                 // which do not have a function around them
510                 if (column.getType() instanceof String
511                         && orderByColumn.indexOf('(') == -1)
512                 {
513                     // find space pos relative to orderByColumn
514                     spacePos = orderByColumn.indexOf(' ');
515                     if (spacePos == -1)
516                     {
517                         orderByClause.add(
518                                 db.ignoreCaseInOrderBy(orderByColumn));
519                     }
520                     else
521                     {
522                         orderByClause.add(
523                                 db.ignoreCaseInOrderBy(
524                                         orderByColumn.substring(0, spacePos))
525                                 + orderByColumn.substring(spacePos));
526                     }
527                     selectClause.add(
528                             db.ignoreCaseInOrderBy(tableName + '.' + columnName));
529                 }
530                 else
531                 {
532                     orderByClause.add(orderByColumn);
533                 }
534             }
535         }
536     }
537 
538     /***
539      * adds the GroupBy-Columns from the criteria to the query
540      * @param criteria the criteria from which the GroupBy-Columns are taken
541      * @param query the query to which the GroupBy-Columns should be added
542      * @throws TorqueException if the GroupBy-Columns can not be processed
543      */
544     private static final void processGroupBy(
545             final Criteria crit,
546             final Query query)
547             throws TorqueException
548     {
549         UniqueList groupByClause = query.getGroupByClause();
550         UniqueList groupBy = crit.getGroupByColumns();
551 
552         // need to allow for multiple group bys
553         if (groupBy != null)
554         {
555             for (int i = 0; i < groupBy.size(); i++)
556             {
557                 String columnName = (String) groupBy.get(i);
558                 String column = (String) crit.getAsColumns().get(columnName);
559 
560                 if (column == null)
561                 {
562                     column = columnName;
563                 }
564 
565                 if (column.indexOf('.') != -1)
566                 {
567                     groupByClause.add(column);
568                 }
569                 else
570                 {
571                     throwMalformedColumnNameException("group by",
572                             column);
573                 }
574             }
575         }
576     }
577 
578     /***
579      * adds the Having-Columns from the criteria to the query
580      * @param criteria the criteria from which the Having-Columns are taken
581      * @param query the query to which the Having-Columns should be added
582      * @throws TorqueException if the Having-Columns can not be processed
583      */
584     private static final void processHaving(
585             final Criteria crit,
586             final Query query)
587             throws TorqueException
588     {
589         Criteria.Criterion having = crit.getHaving();
590         if (having != null)
591         {
592             //String groupByString = null;
593             query.setHaving(having.toString());
594         }
595     }
596 
597     /***
598      * Throws a TorqueException with the malformed column name error
599      * message.  The error message looks like this:<p>
600      *
601      * <code>
602      *     Malformed column name in Criteria [criteriaPhrase]:
603      *     '[columnName]' is not of the form 'table.column'
604      * </code>
605      *
606      * @param criteriaPhrase a String, one of "select", "join", or "order by"
607      * @param columnName a String containing the offending column name
608      * @throws TorqueException Any exceptions caught during processing will be
609      *         rethrown wrapped into a TorqueException.
610      */
611     public static final void throwMalformedColumnNameException(
612         final String criteriaPhrase,
613         final String columnName)
614         throws TorqueException
615     {
616         StringBuffer sb = new StringBuffer()
617                 .append("Malformed column name in Criteria ")
618                 .append(criteriaPhrase)
619                 .append(": '")
620                 .append(StringUtils.isEmpty(columnName) ? "<empty>" : columnName)
621                 .append("' is not of the form 'table.column'");
622 
623         throw new TorqueException(sb.toString());
624     }
625 
626     /***
627      * Returns the tablename which can be added to a From Clause.
628      * This takes care of any aliases that might be defined.
629      * For example, if an alias "a" for the table AUTHOR is defined
630      * in the Criteria criteria, getTableNameForFromClause("a", criteria)
631      * returns "AUTHOR a".
632      * @param tableName the name of a table
633      *        or the alias for a table
634      * @param criteria a criteria object to resolve a possible alias
635      * @return either the tablename itself if tableOrAliasName is not an alias,
636      *         or a String of the form "tableName tableOrAliasName"
637      *         if tableOrAliasName is an alias for a table name
638      */
639     public static final String getTableNameForFromClause(
640             final String tableName,
641             final Criteria criteria)
642     {
643         String shortTableName = getUnqualifiedTableName(tableName);
644 
645         // Most of the time, the alias would be for the short name...
646         String aliasName = criteria.getTableForAlias(shortTableName);
647         if (StringUtils.isEmpty(aliasName))
648         {
649             // But we should also check the FQN...
650             aliasName = criteria.getTableForAlias(tableName);
651         }
652 
653         if (StringUtils.isNotEmpty(aliasName))
654         {
655             // If the tables have an alias, add an "<xxx> <yyy> statement"
656         	// <xxx> AS <yyy> causes problems on oracle
657             return new StringBuffer(
658                     tableName.length() + aliasName.length() + 1)
659                     .append(aliasName)
660                     .append(" ")
661                     .append(tableName)
662                     .toString();
663         }
664 
665         return tableName;
666     }
667 
668     /***
669      * Checks if the Tablename tableName is already contained in a from clause.
670      * If tableName and the tablenames in fromClause are generated by
671      * getTablenameForFromClause(String, Criteria), (which they usually are),
672      * then different aliases for the same table are treated
673      * as different tables: E.g.
674      * fromClauseContainsTableName(fromClause, "table_a a") returns false if
675      * fromClause contains only another alias for table_a ,
676      * e.g. "table_a aa" and the unaliased tablename "table_a".
677      * Special case: If tableName is null, true is returned.
678      * @param fromClause a list containing only elements of type.
679      *        Query.FromElement
680      * @param tableName the tablename to check
681      * @return if the Tablename tableName is already contained in a from clause.
682      *         If tableName is null, true is returned.
683      */
684     public static final boolean fromClauseContainsTableName(
685             final UniqueList fromClause,
686             final String tableName)
687     {
688         if (tableName == null)
689         {
690             // usually this function is called to see if tableName should be
691             // added to the fromClause. As null should not be added,
692             // true is returned.
693             return true;
694         }
695         for ( Iterator it = fromClause.iterator(); it.hasNext();)
696         {
697             Query.FromElement fromElement
698                     = (Query.FromElement) it.next();
699             if (tableName.equals(fromElement.getTableName()))
700             {
701                 return true;
702             }
703         }
704         return false;
705     }
706 
707     /***
708      * adds a table to the from clause of a query, if it is not already
709      * contained there.
710      * @param tableOrAliasName the name of a table
711      *        or the alias for a table
712      * @param criteria a criteria object to resolve a possible alias
713      * @param query the query where the the tablename should be added
714      *        to the from clause
715      * @return the table in the from clause which represents the
716      *         supplied tableOrAliasName
717      */
718     private static final String addTableToFromClause(
719             final String tableName,
720             final Criteria criteria,
721             Query query)
722     {
723         String tableNameForFromClause
724                 = getTableNameForFromClause(tableName, criteria);
725 
726         UniqueList queryFromClause = query.getFromClause();
727 
728         // it is important that this piece of code is executed AFTER
729         // the joins are processed
730         if (!fromClauseContainsTableName(
731             queryFromClause,
732             tableNameForFromClause))
733         {
734             Query.FromElement fromElement
735                     = new Query.FromElement(
736                             tableNameForFromClause, null, null);
737             queryFromClause.add(fromElement);
738         }
739         return tableNameForFromClause;
740     }
741 
742     /***
743      * Inner Interface that defines the Callback method for
744      * the Table creation loop.
745      */
746     public interface TableCallback
747     {
748         /***
749          * Callback Method for getTableSet()
750          *
751          * @param tables The current table name
752          * @param key The current criterion key.
753          * @param crit The Criteria used in getTableSet()
754          */
755         void process(Set tables, String key, Criteria crit);
756     }
757 
758     /***
759      * Inner Interface that defines the Callback method for
760      * the buildQuery Criterion evaluation
761      */
762     public interface QueryCallback
763     {
764         /***
765          * The callback for building a query String
766          *
767          * @param criterion The current criterion
768          * @param params The parameter list passed to buildQueryString()
769          * @return WHERE SQL fragment for this criterion
770          */
771         String process(Criterion criterion, List params);
772     }
773 
774 }