001: package org.apache.torque.util;
002:
003: /*
004: * Licensed to the Apache Software Foundation (ASF) under one
005: * or more contributor license agreements. See the NOTICE file
006: * distributed with this work for additional information
007: * regarding copyright ownership. The ASF licenses this file
008: * to you under the Apache License, Version 2.0 (the
009: * "License"); you may not use this file except in compliance
010: * with the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
017: * KIND, either express or implied. See the License for the
018: * specific language governing permissions and limitations
019: * under the License.
020: */
021:
022: import java.util.HashSet;
023: import java.util.Iterator;
024: import java.util.List;
025: import java.util.Map;
026: import java.util.Set;
027:
028: import org.apache.commons.lang.StringUtils;
029: import org.apache.commons.logging.Log;
030: import org.apache.commons.logging.LogFactory;
031: import org.apache.torque.Torque;
032: import org.apache.torque.TorqueException;
033: import org.apache.torque.adapter.DB;
034: import org.apache.torque.map.ColumnMap;
035: import org.apache.torque.map.DatabaseMap;
036: import org.apache.torque.util.Criteria.Criterion;
037:
038: /**
039: * Factored out code that is used to process SQL tables. This code comes
040: * from BasePeer and is put here to reduce complexity in the BasePeer class.
041: * You should not use the methods here directly!
042: *
043: * @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
044: * @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a>
045: * @version $Id: SQLBuilder.java 535596 2007-05-06 10:47:39Z tfischer $
046: */
047: public final class SQLBuilder {
048: /** Logging */
049: protected static final Log log = LogFactory
050: .getLog(SQLBuilder.class);
051:
052: /** Function Characters */
053: public static final String[] COLUMN_CHARS = { ".", "*" };
054: public static final String[] DELIMITERS = { " ", ",", "(", ")",
055: "<", ">" };
056:
057: /**
058: * Private constructor to prevent instantiation.
059: *
060: * Class contains only static method ans should therefore not be
061: * instantiated.
062: */
063: private SQLBuilder() {
064: }
065:
066: /**
067: * Fully qualify a table name with an optional schema reference
068: *
069: * @param table The table name to use. If null is passed in, null is returned.
070: * @param dbName The name of the database to which this tables belongs.
071: * If null is passed, the default database is used.
072: *
073: * @return The table name to use inside the SQL statement. If null is passed
074: * into this method, null is returned.
075: * @exception TorqueException if an error occurs
076: */
077: public static String getFullTableName(final String table,
078: final String dbName) throws TorqueException {
079: if (table != null) {
080: int dotIndex = table.indexOf(".");
081:
082: if (dotIndex == -1) // No schema given
083: {
084: String targetDBName = (dbName == null) ? Torque
085: .getDefaultDB() : dbName;
086:
087: String targetSchema = Torque.getSchema(targetDBName);
088:
089: // If we have a default schema, fully qualify the
090: // table and return.
091: if (StringUtils.isNotEmpty(targetSchema)) {
092: return new StringBuffer().append(targetSchema)
093: .append(".").append(table).toString();
094: }
095: }
096: }
097:
098: return table;
099: }
100:
101: /**
102: * Remove a possible schema name from the table name.
103: *
104: * @param table The table name to use
105: *
106: * @return The table name with a possible schema name
107: * stripped off
108: */
109: public static String getUnqualifiedTableName(final String table) {
110: if (table != null) {
111: int dotIndex = table.lastIndexOf("."); // Do we have a dot?
112:
113: if (++dotIndex > 0) // Incrementation allows for better test _and_ substring...
114: {
115: return table.substring(dotIndex);
116: }
117: }
118:
119: return table;
120: }
121:
122: /**
123: * Removes a possible function name or clause from a column name
124: *
125: * @param name The column name, possibly containing a clause
126: *
127: * @return The column name
128: *
129: * @throws TorqueException If the column name was malformed
130: */
131: private static String removeSQLFunction(final String name)
132: throws TorqueException {
133: // Empty name => return it
134: if (StringUtils.isEmpty(name)) {
135: return name;
136: }
137:
138: // Find Table.Column
139: int dotIndex = name.indexOf('.');
140: if (dotIndex == -1) {
141: dotIndex = name.indexOf("*");
142: }
143: if (dotIndex == -1) {
144: throw new TorqueException(
145: "removeSQLFunction() : Column name " + name
146: + " does not contain a . or a *");
147: }
148: String pre = name.substring(0, dotIndex);
149: String post = name.substring(dotIndex + 1, name.length());
150: int startIndex = StringUtils.lastIndexOfAny(pre, DELIMITERS);
151: int endIndex = StringUtils.indexOfAny(post, DELIMITERS);
152: if (startIndex < 0 && endIndex < 0) {
153: return name;
154: } else {
155: if (endIndex < 0) {
156: endIndex = post.length();
157: }
158: // if startIndex == -1 the formula is correct
159: return name.substring(startIndex + 1, dotIndex + 1
160: + endIndex);
161: }
162: }
163:
164: /**
165: * Returns a table name from an identifier. Each identifier is to be qualified
166: * as [schema.]table.column. This could also contain FUNCTION([schema.]table.column).
167: *
168: * @param name The (possible fully qualified) identifier name
169: *
170: * @return the fully qualified table name
171: *
172: * @throws TorqueException If the identifier name was malformed
173: */
174: public static String getTableName(final String name,
175: final String dbName) throws TorqueException {
176: final String testName = removeSQLFunction(name);
177:
178: if (StringUtils.isEmpty(testName)) {
179: throwMalformedColumnNameException("getTableName", name);
180: }
181:
182: // Everything before the last dot is the table name
183: int rightDotIndex = testName.lastIndexOf('.');
184:
185: if (rightDotIndex < 0) {
186: if ("*".equals(testName)) {
187: return null;
188: }
189:
190: throwMalformedColumnNameException("getTableName", name);
191: }
192:
193: return getFullTableName(testName.substring(0, rightDotIndex),
194: dbName);
195: }
196:
197: /**
198: * Returns a set of all tables and possible aliases referenced
199: * from a criterion. The resulting Set can be directly used to
200: * build a WHERE clause
201: *
202: * @param crit A Criteria object
203: * @param tableCallback A Callback Object
204: * @return A Set of tables.
205: */
206: public static Set getTableSet(final Criteria crit,
207: final TableCallback tableCallback) {
208: HashSet tables = new HashSet();
209:
210: // Loop over all the Criterions
211: for (Iterator it = crit.keySet().iterator(); it.hasNext();) {
212: String key = (String) it.next();
213: Criteria.Criterion c = crit.getCriterion(key);
214: List tableNames = c.getAllTables();
215:
216: // Loop over all Tables referenced in this criterion.
217: for (Iterator it2 = tableNames.iterator(); it2.hasNext();) {
218: String name = (String) it2.next();
219: String aliasName = crit.getTableForAlias(name);
220:
221: // If the tables have an alias, add an "<xxx> AS <yyy> statement"
222: if (StringUtils.isNotEmpty(aliasName)) {
223: String newName = new StringBuffer(name.length()
224: + aliasName.length() + 4).append(aliasName)
225: .append(" AS ").append(name).toString();
226: name = newName;
227: }
228: tables.add(name);
229: }
230:
231: if (tableCallback != null) {
232: tableCallback.process(tables, key, crit);
233: }
234: }
235:
236: return tables;
237: }
238:
239: /**
240: * Builds a Query clause for Updating and deleting
241: *
242: * @param crit a <code>Criteria</code> value
243: * @param params a <code>List</code> value
244: * @param qc a <code>QueryCallback</code> value
245: * @return a <code>Query</code> value
246: * @exception TorqueException if an error occurs
247: */
248: public static Query buildQueryClause(final Criteria crit,
249: final List params, final QueryCallback qc)
250: throws TorqueException {
251: Query query = new Query();
252:
253: final String dbName = crit.getDbName();
254: final DB db = Torque.getDB(dbName);
255: final DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
256:
257: JoinBuilder.processJoins(db, dbMap, crit, query);
258: processModifiers(crit, query);
259: processSelectColumns(crit, query, dbName);
260: processAsColumns(crit, query);
261: processCriterions(db, dbMap, dbName, crit, query, params, qc);
262: processGroupBy(crit, query);
263: processHaving(crit, query);
264: processOrderBy(db, dbMap, crit, query);
265: processLimits(crit, query);
266:
267: if (log.isDebugEnabled()) {
268: log.debug(query.toString());
269: }
270: return query;
271: }
272:
273: /**
274: * adds the select columns from the criteria to the query
275: * @param criteria the criteria from which the select columns are taken
276: * @param query the query to which the select columns should be added
277: * @throws TorqueException if the select columns can not be processed
278: */
279: private static void processSelectColumns(final Criteria criteria,
280: final Query query, final String dbName)
281: throws TorqueException {
282: UniqueList selectClause = query.getSelectClause();
283: UniqueList select = criteria.getSelectColumns();
284:
285: for (int i = 0; i < select.size(); i++) {
286: String identifier = (String) select.get(i);
287: selectClause.add(identifier);
288: addTableToFromClause(getTableName(identifier, dbName),
289: criteria, query);
290: }
291: }
292:
293: /**
294: * adds the As-columns from the criteria to the query.
295: * @param criteria the criteria from which the As-columns are taken
296: * @param query the query to which the As-columns should be added
297: */
298: private static void processAsColumns(final Criteria criteria,
299: final Query query) {
300: UniqueList querySelectClause = query.getSelectClause();
301: Map criteriaAsColumns = criteria.getAsColumns();
302:
303: for (Iterator it = criteriaAsColumns.entrySet().iterator(); it
304: .hasNext();) {
305: Map.Entry entry = (Map.Entry) it.next();
306: String key = (String) entry.getKey();
307: querySelectClause.add(new StringBuffer().append(
308: entry.getValue()).append(SqlEnum.AS).append(key)
309: .toString());
310: }
311: }
312:
313: /**
314: * adds the Modifiers from the criteria to the query
315: * @param criteria the criteria from which the Modifiers are taken
316: * @param query the query to which the Modifiers should be added
317: */
318: private static void processModifiers(final Criteria criteria,
319: final Query query) {
320: UniqueList selectModifiers = query.getSelectModifiers();
321: UniqueList modifiers = criteria.getSelectModifiers();
322: for (int i = 0; i < modifiers.size(); i++) {
323: selectModifiers.add(modifiers.get(i));
324: }
325: }
326:
327: /**
328: * adds the Criterion-objects from the criteria to the query
329: * @param criteria the criteria from which the Criterion-objects are taken
330: * @param query the query to which the Criterion-objects should be added
331: * @param params the parameters if a prepared statement should be built,
332: * or null if a normal statement should be built.
333: * @throws TorqueException if the Criterion-objects can not be processed
334: */
335: private static void processCriterions(final DB db,
336: final DatabaseMap dbMap, final String dbName,
337: final Criteria crit, final Query query, final List params,
338: final QueryCallback qc) throws TorqueException {
339: UniqueList whereClause = query.getWhereClause();
340:
341: for (Iterator it = crit.keySet().iterator(); it.hasNext();) {
342: String key = (String) it.next();
343: Criteria.Criterion criterion = crit.getCriterion(key);
344: Criteria.Criterion[] someCriteria = criterion
345: .getAttachedCriterion();
346:
347: String table = null;
348: for (int i = 0; i < someCriteria.length; i++) {
349: String tableName = someCriteria[i].getTable();
350:
351: // add the table to the from clause, if it is not already
352: // contained there
353: // it is important that this piece of code is executed AFTER
354: // the joins are processed
355: addTableToFromClause(
356: getFullTableName(tableName, dbName), crit,
357: query);
358:
359: table = crit.getTableForAlias(tableName);
360: if (table == null) {
361: table = tableName;
362: }
363:
364: boolean ignoreCase = ((crit.isIgnoreCase() || someCriteria[i]
365: .isIgnoreCase()) && (dbMap.getTable(table)
366: .getColumn(someCriteria[i].getColumn())
367: .getType() instanceof String));
368:
369: someCriteria[i].setIgnoreCase(ignoreCase);
370: }
371:
372: criterion.setDB(db);
373: whereClause.add(qc.process(criterion, params));
374: }
375: }
376:
377: /**
378: * adds the OrderBy-Columns from the criteria to the query
379: * @param criteria the criteria from which the OrderBy-Columns are taken
380: * @param query the query to which the OrderBy-Columns should be added
381: * @throws TorqueException if the OrderBy-Columns can not be processed
382: */
383: private static void processOrderBy(final DB db,
384: final DatabaseMap dbMap, final Criteria crit,
385: final Query query) throws TorqueException {
386: UniqueList orderByClause = query.getOrderByClause();
387: UniqueList selectClause = query.getSelectClause();
388:
389: UniqueList orderBy = crit.getOrderByColumns();
390:
391: if (orderBy != null && orderBy.size() > 0) {
392: // Check for each String/Character column and apply
393: // toUpperCase().
394: for (int i = 0; i < orderBy.size(); i++) {
395: String orderByColumn = (String) orderBy.get(i);
396:
397: String strippedColumnName = removeSQLFunction(orderByColumn);
398: int dotPos = strippedColumnName.lastIndexOf('.');
399: if (dotPos == -1) {
400: // We are not able to look up the table in the
401: // tableMap, as no table name is given. Simply add
402: // the orderBy and hope the user knows what he is
403: // doing.
404: orderByClause.add(orderByColumn);
405: continue;
406: }
407:
408: String tableName = strippedColumnName.substring(0,
409: dotPos);
410: String table = crit.getTableForAlias(tableName);
411: if (table == null) {
412: table = tableName;
413: }
414:
415: // See if there's a space (between the column list and sort
416: // order in ORDER BY table.column DESC).
417: int spacePos = strippedColumnName.indexOf(' ');
418: String columnName;
419: if (spacePos == -1) {
420: columnName = strippedColumnName
421: .substring(dotPos + 1);
422: } else {
423: columnName = strippedColumnName.substring(
424: dotPos + 1, spacePos);
425: }
426: ColumnMap column = dbMap.getTable(table).getColumn(
427: columnName);
428:
429: // only ignore case in order by for string columns
430: // which do not have a function around them
431: if (column.getType() instanceof String
432: && orderByColumn.indexOf('(') == -1) {
433: // find space pos relative to orderByColumn
434: spacePos = orderByColumn.indexOf(' ');
435: if (spacePos == -1) {
436: orderByClause.add(db
437: .ignoreCaseInOrderBy(orderByColumn));
438: } else {
439: orderByClause.add(db
440: .ignoreCaseInOrderBy(orderByColumn
441: .substring(0, spacePos))
442: + orderByColumn.substring(spacePos));
443: }
444: selectClause.add(db.ignoreCaseInOrderBy(tableName
445: + '.' + columnName));
446: } else {
447: orderByClause.add(orderByColumn);
448: }
449: }
450: }
451: }
452:
453: /**
454: * adds the GroupBy-Columns from the criteria to the query
455: * @param criteria the criteria from which the GroupBy-Columns are taken
456: * @param query the query to which the GroupBy-Columns should be added
457: * @throws TorqueException if the GroupBy-Columns can not be processed
458: */
459: private static void processGroupBy(final Criteria crit,
460: final Query query) throws TorqueException {
461: UniqueList groupByClause = query.getGroupByClause();
462: UniqueList groupBy = crit.getGroupByColumns();
463:
464: // need to allow for multiple group bys
465: if (groupBy != null) {
466: for (int i = 0; i < groupBy.size(); i++) {
467: String columnName = (String) groupBy.get(i);
468: String column = (String) crit.getAsColumns().get(
469: columnName);
470:
471: if (column == null) {
472: column = columnName;
473: }
474:
475: if (column.indexOf('.') != -1) {
476: groupByClause.add(column);
477: } else {
478: throwMalformedColumnNameException("group by",
479: column);
480: }
481: }
482: }
483: }
484:
485: /**
486: * adds the Having-Columns from the criteria to the query
487: * @param criteria the criteria from which the Having-Columns are taken
488: * @param query the query to which the Having-Columns should be added
489: * @throws TorqueException if the Having-Columns can not be processed
490: */
491: private static void processHaving(final Criteria crit,
492: final Query query) throws TorqueException {
493: Criteria.Criterion having = crit.getHaving();
494: if (having != null) {
495: //String groupByString = null;
496: query.setHaving(having.toString());
497: }
498: }
499:
500: /**
501: * adds a Limit clause to the query if supported by the database
502: * @param criteria the criteria from which the Limit and Offset values
503: * are taken
504: * @param query the query to which the Limit clause should be added
505: * @throws TorqueException if the Database adapter cannot be obtained
506: */
507: private static void processLimits(final Criteria crit,
508: final Query query) throws TorqueException {
509: int limit = crit.getLimit();
510: int offset = crit.getOffset();
511:
512: if (offset > 0 || limit >= 0) {
513: DB db = Torque.getDB(crit.getDbName());
514: db.generateLimits(query, offset, limit);
515: }
516: }
517:
518: /**
519: * Throws a TorqueException with the malformed column name error
520: * message. The error message looks like this:<p>
521: *
522: * <code>
523: * Malformed column name in Criteria [criteriaPhrase]:
524: * '[columnName]' is not of the form 'table.column'
525: * </code>
526: *
527: * @param criteriaPhrase a String, one of "select", "join", or "order by"
528: * @param columnName a String containing the offending column name
529: * @throws TorqueException Any exceptions caught during processing will be
530: * rethrown wrapped into a TorqueException.
531: */
532: public static void throwMalformedColumnNameException(
533: final String criteriaPhrase, final String columnName)
534: throws TorqueException {
535: StringBuffer sb = new StringBuffer().append(
536: "Malformed column name in Criteria ").append(
537: criteriaPhrase).append(": '").append(
538: StringUtils.isEmpty(columnName) ? "<empty>"
539: : columnName).append(
540: "' is not of the form 'table.column'");
541:
542: throw new TorqueException(sb.toString());
543: }
544:
545: /**
546: * Returns the tablename which can be added to a From Clause.
547: * This takes care of any aliases that might be defined.
548: * For example, if an alias "a" for the table AUTHOR is defined
549: * in the Criteria criteria, getTableNameForFromClause("a", criteria)
550: * returns "AUTHOR a".
551: * @param tableName the name of a table
552: * or the alias for a table
553: * @param criteria a criteria object to resolve a possible alias
554: * @return either the tablename itself if tableOrAliasName is not an alias,
555: * or a String of the form "tableName tableOrAliasName"
556: * if tableOrAliasName is an alias for a table name
557: */
558: public static String getTableNameForFromClause(
559: final String tableName, final Criteria criteria) {
560: String shortTableName = getUnqualifiedTableName(tableName);
561:
562: // Most of the time, the alias would be for the short name...
563: String aliasName = criteria.getTableForAlias(shortTableName);
564: if (StringUtils.isEmpty(aliasName)) {
565: // But we should also check the FQN...
566: aliasName = criteria.getTableForAlias(tableName);
567: }
568:
569: if (StringUtils.isNotEmpty(aliasName)) {
570: // If the tables have an alias, add an "<xxx> <yyy> statement"
571: // <xxx> AS <yyy> causes problems on oracle
572: return new StringBuffer(tableName.length()
573: + aliasName.length() + 1).append(aliasName).append(
574: " ").append(tableName).toString();
575: }
576:
577: return tableName;
578: }
579:
580: /**
581: * Checks if the Tablename tableName is already contained in a from clause.
582: * If tableName and the tablenames in fromClause are generated by
583: * getTablenameForFromClause(String, Criteria), (which they usually are),
584: * then different aliases for the same table are treated
585: * as different tables: E.g.
586: * fromClauseContainsTableName(fromClause, "table_a a") returns false if
587: * fromClause contains only another alias for table_a ,
588: * e.g. "table_a aa" and the unaliased tablename "table_a".
589: * Special case: If tableName is null, true is returned.
590: * @param fromClause a list containing only elements of type.
591: * Query.FromElement
592: * @param tableName the tablename to check
593: * @return if the Tablename tableName is already contained in a from clause.
594: * If tableName is null, true is returned.
595: */
596: public static boolean fromClauseContainsTableName(
597: final UniqueList fromClause, final String tableName) {
598: if (tableName == null) {
599: // usually this function is called to see if tableName should be
600: // added to the fromClause. As null should not be added,
601: // true is returned.
602: return true;
603: }
604: for (Iterator it = fromClause.iterator(); it.hasNext();) {
605: Query.FromElement fromElement = (Query.FromElement) it
606: .next();
607: if (tableName.equals(fromElement.getTableName())) {
608: return true;
609: }
610: }
611: return false;
612: }
613:
614: /**
615: * adds a table to the from clause of a query, if it is not already
616: * contained there.
617: * @param tableOrAliasName the name of a table
618: * or the alias for a table
619: * @param criteria a criteria object to resolve a possible alias
620: * @param query the query where the the tablename should be added
621: * to the from clause
622: * @return the table in the from clause which represents the
623: * supplied tableOrAliasName
624: */
625: private static String addTableToFromClause(final String tableName,
626: final Criteria criteria, Query query) {
627: String tableNameForFromClause = getTableNameForFromClause(
628: tableName, criteria);
629:
630: UniqueList queryFromClause = query.getFromClause();
631:
632: // it is important that this piece of code is executed AFTER
633: // the joins are processed
634: if (!fromClauseContainsTableName(queryFromClause,
635: tableNameForFromClause)) {
636: Query.FromElement fromElement = new Query.FromElement(
637: tableNameForFromClause, null, null);
638: queryFromClause.add(fromElement);
639: }
640: return tableNameForFromClause;
641: }
642:
643: /**
644: * Inner Interface that defines the Callback method for
645: * the Table creation loop.
646: */
647: public interface TableCallback {
648: /**
649: * Callback Method for getTableSet()
650: *
651: * @param tables The current table name
652: * @param key The current criterion key.
653: * @param crit The Criteria used in getTableSet()
654: */
655: void process(Set tables, String key, Criteria crit);
656: }
657:
658: /**
659: * Inner Interface that defines the Callback method for
660: * the buildQuery Criterion evaluation
661: */
662: public interface QueryCallback {
663: /**
664: * The callback for building a query String
665: *
666: * @param criterion The current criterion
667: * @param params The parameter list passed to buildQueryString()
668: * @return WHERE SQL fragment for this criterion
669: */
670: String process(Criterion criterion, List params);
671: }
672:
673: }
|