1 package org.apache.torque.util;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
70 {
71 String targetDBName = (dbName == null)
72 ? Torque.getDefaultDB()
73 : dbName;
74
75 String targetSchema = Torque.getSchema(targetDBName);
76
77
78
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(".");
106
107 if (++dotIndex > 0)
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
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
138 if (leftParent < 0)
139 {
140 if (rightParent < 0)
141 {
142
143 return name;
144 }
145 }
146
147
148 if (rightParent > leftParent)
149 {
150
151 return name.substring(leftParent + 1, rightParent);
152 }
153
154
155 throwMalformedColumnNameException(
156 "removeSQLFunction",
157 name);
158
159 return null;
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
175 if (StringUtils.isEmpty(name))
176 {
177 return name;
178 }
179
180 final int spacePos = name.trim().lastIndexOf(' ');
181
182
183 if (spacePos > 0)
184 {
185
186 return name.trim().substring(spacePos + 1);
187 }
188
189
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
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
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
259 for (Iterator it2 = tableNames.iterator(); it2.hasNext(); )
260 {
261 String name = (String) it2.next();
262 String aliasName = crit.getTableForAlias(name);
263
264
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
423
424
425
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
469
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
492
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
509
510 if (column.getType() instanceof String
511 && orderByColumn.indexOf('(') == -1)
512 {
513
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
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
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
646 String aliasName = criteria.getTableForAlias(shortTableName);
647 if (StringUtils.isEmpty(aliasName))
648 {
649
650 aliasName = criteria.getTableForAlias(tableName);
651 }
652
653 if (StringUtils.isNotEmpty(aliasName))
654 {
655
656
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
691
692
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
729
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 }