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