package org.apache.torque.sql;
   
   /*
    * Licensed to the Apache Software Foundation (ASF) under one
    * or more contributor license agreements.  See the NOTICE file
    * distributed with this work for additional information
    * regarding copyright ownership.  The ASF licenses this file
    * to you under the Apache License, Version 2.0 (the
    * "License"); you may not use this file except in compliance
   * with the License.  You may obtain a copy of the License at
   *
   *   http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing,
   * software distributed under the License is distributed on an
   * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
   * KIND, either express or implied.  See the License for the
   * specific language governing permissions and limitations
   * under the License.
   */
  
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Map;
  
  import org.apache.commons.lang.StringUtils;
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  import org.apache.torque.Column;
  import org.apache.torque.ColumnImpl;
  import org.apache.torque.Database;
  import org.apache.torque.Torque;
  import org.apache.torque.TorqueException;
  import org.apache.torque.adapter.Adapter;
  import org.apache.torque.criteria.Criteria;
  import org.apache.torque.criteria.CriteriaInterface;
  import org.apache.torque.criteria.Criterion;
  import org.apache.torque.criteria.FromElement;
  import org.apache.torque.criteria.PreparedStatementPart;
  import org.apache.torque.criteria.SqlEnum;
  import org.apache.torque.map.ColumnMap;
  import org.apache.torque.map.DatabaseMap;
  import org.apache.torque.map.MapHelper;
  import org.apache.torque.map.TableMap;
  import org.apache.torque.om.ObjectKey;
  import org.apache.torque.sql.whereclausebuilder.CurrentDateTimePsPartBuilder;
  import org.apache.torque.sql.whereclausebuilder.CustomBuilder;
  import org.apache.torque.sql.whereclausebuilder.InBuilder;
  import org.apache.torque.sql.whereclausebuilder.LikeBuilder;
  import org.apache.torque.sql.whereclausebuilder.NullValueBuilder;
  import org.apache.torque.sql.whereclausebuilder.StandardBuilder;
  import org.apache.torque.sql.whereclausebuilder.VerbatimSqlConditionBuilder;
  import org.apache.torque.sql.whereclausebuilder.WhereClausePsPartBuilder;
  import org.apache.torque.util.UniqueColumnList;
  import org.apache.torque.util.UniqueList;
  
  /**
   * Factored out code that is used to process SQL tables. This code comes
   * from BasePeer and is put here to reduce complexity in the BasePeer class.
   * You should not use the methods here directly!
   *
   * @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
   * @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a>
   * @version $Id: SqlBuilder.java 1355228 2012-06-29 03:38:08Z tfischer $
   */
  @SuppressWarnings("deprecation")
  public final class SqlBuilder
  {
      /** Logging */
      protected static final Log log = LogFactory.getLog(SqlBuilder.class);
  
      /** Delimiters for SQL functions. */
      public static final String[] FUNCTION_DELIMITERS
              = {" ", ",", "(", ")", "<", ">"};
  
      /** The backslash character*/
      private static final char BACKSLASH = '\\';
  
      /**
       * The list of WhereClausePsPartBuilders which can build the where clause.
       */
      private static List<WhereClausePsPartBuilder> whereClausePsPartBuilders
          = new ArrayList<WhereClausePsPartBuilder>();
  
      static
      {
          whereClausePsPartBuilders.add(new VerbatimSqlConditionBuilder());
          whereClausePsPartBuilders.add(new CustomBuilder());
          whereClausePsPartBuilders.add(new CurrentDateTimePsPartBuilder());
          whereClausePsPartBuilders.add(new NullValueBuilder());
          whereClausePsPartBuilders.add(new LikeBuilder());
          whereClausePsPartBuilders.add(new InBuilder());
          whereClausePsPartBuilders.add(new StandardBuilder());
      }
  
      /**
       * Private constructor to prevent instantiation.
       *
       * Class contains only static method and should therefore not be
      * instantiated.
      */
     private SqlBuilder()
     {
         // empty
     }
 
     /**
      * Returns the Builders which are responsible to render single where clause
      * conditions. The returned list can be modified in order to change
      * the rendered SQL.
      *
      * @return the current WhereClausePsPartBuilders, not null.
      */
     public static List<WhereClausePsPartBuilder> getWhereClausePsPartBuilders()
     {
         return whereClausePsPartBuilders;
     }
 
     /**
      * Builds a Query from a criteria.
      *
      * @param crit the criteria to build the query from, not null.
      *
      * @return the corresponding query to the criteria.
      *
      * @exception TorqueException if an error occurs
      * @deprecated please use org.apache.torque.criteria.Criteria
      *             instead of org.apache.torque.util.Criteria.
      */
     @Deprecated
     public static Query buildQuery(final org.apache.torque.util.Criteria crit)
             throws TorqueException
     {
         Query sqlStatement = new Query();
 
         JoinBuilder.processJoins(crit, sqlStatement);
         processModifiers(crit, sqlStatement);
         processSelectColumns(crit, sqlStatement);
         processAsColumns(crit, sqlStatement);
         processCriterions(crit, sqlStatement);
         processGroupBy(crit, sqlStatement);
         processHaving(crit, sqlStatement);
         processOrderBy(crit, sqlStatement);
         processLimits(crit, sqlStatement);
 
         return sqlStatement;
     }
 
     /**
      * Builds a Query from a criteria.
      *
      * @param crit the criteria to build the query from, not null.
      *
      * @return the corresponding query to the criteria.
      *
      * @exception TorqueException if an error occurs
      */
     public static Query buildQuery(final Criteria crit)
             throws TorqueException
     {
         Query sqlStatement = new Query();
 
         JoinBuilder.processJoins(crit, sqlStatement);
         processModifiers(crit, sqlStatement);
         processSelectColumns(crit, sqlStatement);
         processAsColumns(crit, sqlStatement);
         processCriterions(crit, sqlStatement);
         processGroupBy(crit, sqlStatement);
         processHaving(crit, sqlStatement);
         processOrderBy(crit, sqlStatement);
         processLimits(crit, sqlStatement);
         processFromElements(crit, sqlStatement);
         sqlStatement.setFetchSize(crit.getFetchSize());
 
         return sqlStatement;
     }
 
     /**
      * Adds the select columns from the criteria to the query.
      *
      * @param criteria the criteria from which the select columns are taken.
      * @param query the query to which the select columns should be added.
      *
      * @throws TorqueException if the select columns can not be processed.
      */
     private static void processSelectColumns(
                 final CriteriaInterface<?> criteria,
                 final Query query)
             throws TorqueException
     {
         UniqueList<String> selectClause = query.getSelectClause();
         UniqueColumnList selectColumns = criteria.getSelectColumns();
 
         for (Column column : selectColumns)
         {
             String sqlExpression = column.getSqlExpression();
             Column resolvedAlias = criteria.getAsColumns().get(sqlExpression);
             if (resolvedAlias != null)
             {
                 // will be handled by processAsColumns
                 continue;
             }
             selectClause.add(sqlExpression);
             addTableToFromClause(
                     column,
                     criteria,
                     query);
         }
     }
 
     /**
      * Adds the As-columns (Aliases for columns) from the criteria
      * to the query's select clause.
      *
      * @param criteria the criteria from which the As-columns are taken,
      *        not null.
      * @param query the query to which the As-columns should be added,
      *        not null.
      *
      * @throws TorqueException if the as columns can not be processed.
      */
     private static void processAsColumns(
                 final CriteriaInterface<?> criteria,
                 final Query query)
             throws TorqueException
     {
         UniqueList<String> querySelectClause = query.getSelectClause();
         Map<String, Column> criteriaAsColumns = criteria.getAsColumns();
 
         for (Map.Entry<String, Column> entry : criteriaAsColumns.entrySet())
         {
             Column column = entry.getValue();
             querySelectClause.add(
                     column.getSqlExpression()
                         + SqlEnum.AS
                         + entry.getKey());
             addTableToFromClause(
                     column,
                     criteria,
                     query);
         }
     }
 
     /**
      * Adds the select modifiers from the criteria to the query.
      *
      * @param criteria the criteria from which the Modifiers are taken,
      *        not null.
      * @param query the query to which the Modifiers should be added,
      *        not null.
      */
     private static void processModifiers(
             final CriteriaInterface<?> criteria,
             final Query query)
     {
         UniqueList<String> selectModifiers = query.getSelectModifiers();
         UniqueList<String> modifiers = criteria.getSelectModifiers();
         for (String modifier : modifiers)
         {
             selectModifiers.add(modifier);
         }
     }
 
     /**
      * Adds the Criterions from the criteria to the query.
      *
      * @param criteria the criteria from which the Criterion-objects are taken
      * @param query the query to which the Criterion-objects should be added.
      *
      * @throws TorqueException if the Criterion-objects can not be processed
      */
     private static void processCriterions(
             final Criteria criteria,
             final Query query)
         throws TorqueException
     {
         if (criteria.getTopLevelCriterion() == null)
         {
             return;
         }
         StringBuilder where = new StringBuilder();
         appendCriterion(
                 criteria.getTopLevelCriterion(),
                 criteria,
                 where,
                 query);
         query.getWhereClause().add(where.toString());
     }
 
     static void appendCriterion(
                 Criterion criterion,
                 CriteriaInterface<?>  criteria,
                 StringBuilder where,
                 Query query)
             throws TorqueException
     {
         if (criterion.isComposite())
         {
             where.append('(');
             boolean firstPart = true;
             for (Criterion part : criterion.getParts())
             {
                 if (!firstPart)
                 {
                     where.append(criterion.getConjunction());
                 }
                 appendCriterion(
                         part,
                         criteria,
                         where,
                         query);
                 firstPart = false;
             }
             where.append(')');
             return;
         }
         // add the table to the from clause, if it is not already
         // contained there
         // it is important that this piece of code is executed AFTER
         // the joins are processed
         addTableToFromClause(
                 criterion.getLValue(),
                 criteria,
                 query);
         addTableToFromClause(
                 criterion.getRValue(),
                 criteria,
                 query);
 
         PreparedStatementPart whereClausePartOutput
             = processCriterion(criterion, criteria);
 
         where.append(whereClausePartOutput.getSql());
         query.getWhereClausePreparedStatementReplacements().addAll(
                 whereClausePartOutput.getPreparedStatementReplacements());
     }
 
     static PreparedStatementPart processCriterion(
                 Criterion criterion,
                 CriteriaInterface<?> criteria)
             throws TorqueException
     {
         final String dbName = criteria.getDbName();
         final Database database = Torque.getDatabase(dbName);
         final Adapter adapter = Torque.getAdapter(dbName);
 
         boolean ignoreCase
                 = isIgnoreCase(criterion, criteria, database);
 
         WhereClauseExpression whereClausePartInput
                 = new WhereClauseExpression(
                         criterion.getLValue(),
                         criterion.getComparison(),
                         criterion.getRValue(),
                         criterion.getSql(),
                         criterion.getPreparedStatementReplacements());
         PreparedStatementPart whereClausePartOutput = null;
         for (WhereClausePsPartBuilder builder : whereClausePsPartBuilders)
         {
             if (builder.isApplicable(whereClausePartInput, adapter))
             {
                 whereClausePartOutput = builder.buildPs(
                         whereClausePartInput,
                         ignoreCase,
                         adapter);
                 break;
             }
         }
 
         if (whereClausePartOutput == null)
         {
             // should not happen as last element in list is standardHandler
             // which takes all
             throw new RuntimeException("No handler found for whereClausePart "
                     + whereClausePartInput);
         }
         return whereClausePartOutput;
     }
 
     /**
      * Adds the Criterions from the criteria to the query.
      *
      * @param criteria the criteria from which the Criterion-objects are taken
      * @param query the query to which the Criterion-objects should be added.
      *
      * @throws TorqueException if the Criterion-objects can not be processed
      * @deprecated please use org.apache.torque.criteria.Criteria
      *             instead of org.apache.torque.util.Criteria.
      */
     @Deprecated
     private static void processCriterions(
             final org.apache.torque.util.Criteria criteria,
             final Query query)
         throws TorqueException
     {
         UniqueList<String> whereClause = query.getWhereClause();
 
         for (org.apache.torque.util.Criteria.Criterion criterion
                 : criteria.values())
         {
             StringBuilder sb = new StringBuilder();
             appendCriterionToPs(
                     criterion,
                     criteria,
                     sb,
                     query);
             whereClause.add(sb.toString());
         }
     }
 
     /**
      * @deprecated please use org.apache.torque.criteria.Criteria
      *             instead of org.apache.torque.util.Criteria.
      */
     @Deprecated
     private static void appendCriterionToPs(
                 org.apache.torque.util.Criteria.Criterion criterion,
                 org.apache.torque.util.Criteria criteria,
                 StringBuilder sb,
                 Query query)
             throws TorqueException
     {
         Column column = criterion.getColumn();
 
         // add the table to the from clause, if it is not already
         // contained there
         // it is important that this piece of code is executed AFTER
         // the joins are processed
         addTableToFromClause(
             column,
             criteria,
             query);
 
         boolean ignoreCase
                 = criteria.isIgnoreCase() || criterion.isIgnoreCase();
         final String dbName = criteria.getDbName();
         final Adapter adapter = Torque.getAdapter(dbName);
         final Database database = Torque.getDatabase(dbName);
         {
             Column databaseColumn = resolveAliasAndAsColumnAndSchema(
                     column,
                     criteria);
             ColumnMap columnMap = null;
             {
                 DatabaseMap databaseMap = database.getDatabaseMap();
                 TableMap tableMap = databaseMap.getTable(
                         databaseColumn.getTableName());
                 if (tableMap != null)
                 {
                     columnMap = tableMap.getColumn(
                             databaseColumn.getColumnName());
                 }
             }
             if (columnMap != null)
             {
                 // do not use ignoreCase on columns
                 // which do not contain String values
                 ignoreCase = ignoreCase
                     && columnMap.getType() instanceof String;
             }
         }
 
         for (int j = 0; j < criterion.getClauses().size(); j++)
         {
             sb.append('(');
         }
         String columnName = criterion.getColumn().getSqlExpression();
         WhereClauseExpression whereClausePartInput
                 = new WhereClauseExpression(
                         columnName,
                         criterion.getComparison(),
                         criterion.getValue(),
                         null,
                         null);
         PreparedStatementPart whereClausePartOutput
             = buildPs(
                 whereClausePartInput,
                 ignoreCase,
                 adapter);
         sb.append(whereClausePartOutput.getSql());
         query.getWhereClausePreparedStatementReplacements().addAll(
                 whereClausePartOutput.getPreparedStatementReplacements());
 
         for (int i = 0; i < criterion.getClauses().size(); i++)
         {
             sb.append(criterion.getConjunctions().get(i));
             org.apache.torque.util.Criteria.Criterion clause
                 = criterion.getClauses().get(i);
             appendCriterionToPs(
                     clause,
                     criteria,
                     sb,
                     query);
             sb.append(')');
         }
     }
     /**
      * adds the OrderBy-Columns from the criteria to the query
      * @param criteria the criteria from which the OrderBy-Columns are taken
      * @param query the query to which the OrderBy-Columns should be added
      * @throws TorqueException if the OrderBy-Columns can not be processed
      */
     private static void processOrderBy(
             final CriteriaInterface<?> crit,
             final Query query)
             throws TorqueException
     {
         UniqueList<String> orderByClause = query.getOrderByClause();
         UniqueList<String> selectClause = query.getSelectClause();
 
         UniqueList<OrderBy> orderByList = crit.getOrderByColumns();
 
         // Check for each String/Character column and apply
         // toUpperCase().
         for (OrderBy orderBy : orderByList)
         {
             Column column = orderBy.getColumn();
             ColumnMap columnMap = MapHelper.getColumnMap(column, crit);
             String sqlExpression = column.getSqlExpression();
 
             // Either we are not able to look up the column in the
             // databaseMap, then simply use the case in orderBy and
             // hope the user knows what he is
             // doing.
             // Or we only ignore case in order by for string columns
             // which do not have a function around them
             if (columnMap == null
                     || (columnMap.getType() instanceof String
                         && sqlExpression.indexOf('(') == -1))
             {
                 if (orderBy.isIgnoreCase() || crit.isIgnoreCase())
                 {
                     final Adapter adapter = Torque.getAdapter(crit.getDbName());
                     orderByClause.add(
                             adapter.ignoreCaseInOrderBy(sqlExpression)
                                 + ' ' + orderBy.getOrder());
                     selectClause.add(
                             adapter.ignoreCaseInOrderBy(sqlExpression));
                 }
                 else
                 {
                     orderByClause.add(sqlExpression + ' ' + orderBy.getOrder());
                     if (crit.getAsColumns().get(sqlExpression) == null)
                     {
                         selectClause.add(sqlExpression);
                     }
                 }
             }
             else
             {
                 orderByClause.add(sqlExpression + ' ' + orderBy.getOrder());
                 if (crit.getAsColumns().get(sqlExpression) == null)
                 {
                     selectClause.add(sqlExpression);
                 }
             }
             addTableToFromClause(
                    column,
                    crit,
                    query);
         }
     }
 
     /**
      * Adds the GroupBy-Columns from the criteria to the query.
      *
      * @param criteria the criteria from which the GroupBy-Columns are taken.
      * @param query the query to which the GroupBy-Columns should be added.
      *
      * @throws TorqueException if the GroupBy-Columns can not be processed
      */
     private static void processGroupBy(
             final CriteriaInterface<?> criteria,
             final Query query)
             throws TorqueException
     {
         UniqueList<String> groupByClause = query.getGroupByClause();
         UniqueList<String> selectClause = query.getSelectClause();
         UniqueColumnList groupBy = criteria.getGroupByColumns();
 
         for (Column groupByColumn : groupBy)
         {
             Column column = criteria.getAsColumns().get(
                     groupByColumn.getSqlExpression());
 
             if (column == null)
             {
                 column = groupByColumn;
             }
 
             groupByClause.add(column.getSqlExpression());
             selectClause.add(column.getSqlExpression());
             addTableToFromClause(column, criteria, query);
         }
     }
 
     /**
      * adds the Having-Columns from the criteria to the query
      * @param criteria the criteria from which the Having-Columns are taken
      * @param query the query to which the Having-Columns should be added
      * @throws TorqueException if the Having-Columns can not be processed
      */
     private static void processHaving(
             final Criteria crit,
             final Query query)
             throws TorqueException
     {
         Criterion having = crit.getHaving();
         if (having != null)
         {
             query.setHaving(having.toString());
         }
     }
 
     /**
      * adds the Having-Columns from the criteria to the query
      * @param criteria the criteria from which the Having-Columns are taken
      * @param query the query to which the Having-Columns should be added
      * @throws TorqueException if the Having-Columns can not be processed
      */
     private static void processHaving(
             final org.apache.torque.util.Criteria crit,
             final Query query)
             throws TorqueException
     {
         org.apache.torque.util.Criteria.Criterion having = crit.getHaving();
         if (having != null)
         {
             query.setHaving(having.toString());
         }
     }
 
     /**
      * Adds a Limit clause to the query if supported by the database
      * @param criteria the criteria from which the Limit and Offset values
      *        are taken
      * @param query the query to which the Limit clause should be added
      * @throws TorqueException if the Database adapter cannot be obtained
      */
     private static void processLimits(
             final CriteriaInterface<?> crit,
             final Query query)
             throws TorqueException
     {
         int limit = crit.getLimit();
         long offset = crit.getOffset();
 
         if (offset > 0 || limit >= 0)
         {
             Adapter adapter = Torque.getAdapter(crit.getDbName());
             adapter.generateLimits(query, offset, limit);
         }
     }
 
     /**
      * Checks the fromElements in the criteria and replaces the automatically
      * calculated fromElements in the query by them, if they are filled.
      *
      * @param criteria the criteria from which the query should be built.
      * @param query the query to build.
      */
     private static void processFromElements(
             final Criteria criteria,
             final Query query)
     {
         if (criteria.getFromElements().isEmpty())
         {
             log.trace("criteria's from Elements is empty,"
                     + " using automatically calculated from clause");
             return;
         }
         query.getFromClause().clear();
         query.getFromClause().addAll(criteria.getFromElements());
     }
 
     /**
      * Returns the tablename which can be added to a From Clause.
      * This takes care of any aliases that might be defined.
      * For example, if an alias "a" for the table AUTHOR is defined
      * in the Criteria criteria, getTableNameForFromClause("a", criteria)
      * returns "AUTHOR a".
      *
      * @param toAddToFromClause the column to extract the table name from,
      *        or a literal object value.
      * @param criteria a criteria object to resolve a possible alias.
      *
      * @return A prepared statement part containing either the table name
      *         itself if tableOrAliasName is not an alias,
      *         or a String of the form "tableName tableOrAliasName"
      *         if tableOrAliasName is an alias for a table name,
      *         or a ? with the replacement if toAddToFromClause is not a Column.
      */
     static PreparedStatementPart getExpressionForFromClause(
             final Object toAddToFromClause,
             final CriteriaInterface<?> criteria)
             throws TorqueException
     {
         if (!(toAddToFromClause instanceof Column))
         {
             // toAddToFromClause is a literal Value
             return new PreparedStatementPart("?", toAddToFromClause);
         }
         Column column = (Column) toAddToFromClause;
         Column resolvedColumn
                 = resolveAliasAndAsColumnAndSchema(column, criteria);
         String fullTableName
                 = resolvedColumn.getFullTableName();
 
         if (!StringUtils.equals(
                 resolvedColumn.getTableName(),
                 column.getTableName()))
         {
             // If the tables have an alias, add an "<xxx> <yyy> statement"
             // <xxx> AS <yyy> causes problems on oracle
             PreparedStatementPart result = new PreparedStatementPart();
             result.getSql()
                     .append(fullTableName)
                     .append(" ")
                     .append(column.getTableName());
             return result;
         }
         Object resolvedAlias = criteria.getAliases().get(
                 resolvedColumn.getTableName());
         if (resolvedAlias != null)
         {
             if (resolvedAlias instanceof Criteria)
             {
                 Criteria subquery = (Criteria) resolvedAlias;
                 Query renderedSubquery = SqlBuilder.buildQuery(subquery);
                 PreparedStatementPart result = new PreparedStatementPart();
                 result.getSql().append("(")
                         .append(renderedSubquery.toString())
                         .append(") ")
                         .append(resolvedColumn.getTableName());
                 result.getPreparedStatementReplacements().addAll(
                         renderedSubquery.getPreparedStatementReplacements());
                 return result;
             }
             else
             {
                 throw new TorqueException("Table name "
                     + resolvedColumn.getTableName()
                     + " resolved to an unhandleable class "
                     + resolvedAlias.getClass().getName());
             }
         }
 
         return new PreparedStatementPart(fullTableName);
     }
 
     /**
      * Fully qualify a table name with an optional schema reference.
      *
      * @param table The table name to use.
      *              If null is passed in, null is returned.
      * @param dbName The name of the database to which this tables belongs.
      *               If null is passed, the default database is used.
      *
      * @return The table name to use inside the SQL statement.
      *         If null is passed into this method, null is returned.
      * @exception TorqueException if an error occurs
      */
     public static String getFullTableName(
             final String table,
             final String dbName)
         throws TorqueException
     {
         if (table == null)
         {
             return table;
         }
 
         int dotIndex = table.indexOf(".");
         if (dotIndex == -1) // No schema given
         {
             String targetDBName = (dbName == null)
                     ? Torque.getDefaultDB()
                     : dbName;
 
             String targetSchema = Torque.getSchema(targetDBName);
 
             // If we have a default schema, fully qualify the
             // table and return.
             if (StringUtils.isNotEmpty(targetSchema))
             {
                 return new StringBuffer()
                         .append(targetSchema)
                         .append(".")
                         .append(table)
                         .toString();
             }
         }
 
         return table;
     }
 
     /**
      * Unqualify a table or column name.
      *
      * @param name the name to unqualify.
      *        If null is passed in, null is returned.
      *
      * @return The unqualified name.
      */
     public static String getUnqualifiedName(
             final String name,
             final String dbName)
         throws TorqueException
     {
         if (name == null)
         {
             return null;
         }
 
         int dotIndex = name.lastIndexOf(".");
         if (dotIndex == -1)
         {
             return name;
         }
 
         return name.substring(dotIndex + 1);
     }
 
     /**
      * Guesses a table name from a criteria by inspecting the first
      * column in the criteria.
      *
      * @param criteria the criteria to guess the table name from.
      *
      * @return the table name, not null.
      *
      * @throws TorqueException if the table name cannot be determined.
      */
     public static String guessFullTableFromCriteria(Criteria criteria)
             throws TorqueException
     {
         org.apache.torque.criteria.Criterion criterion
                 = criteria.getTopLevelCriterion();
         if (criterion == null)
         {
             throw new TorqueException("Could not determine table name "
                     + " as criteria contains no criterion");
         }
         while (criterion.isComposite())
         {
             criterion = criterion.getParts().iterator().next();
         }
         String tableName = null;
 
         Object lValue = criterion.getLValue();
         if (lValue instanceof Column)
         {
             Column column = (Column) lValue;
             tableName = column.getFullTableName();
         }
         if (tableName == null)
         {
             throw new TorqueException("Could not determine table name "
                     + " as first criterion contains no table name");
         }
         return tableName;
     }
 
     /**
      * Returns the table map for a table.
      *
      * @param tableName the name of the table.
      * @param dbName the name of the database, null for the default db.
      *
      * @return the table map for the table, not null.
      *
      * @throws TorqueException if the database or table is unknown.
      */
     public static TableMap getTableMap(String tableName, String dbName)
             throws TorqueException
     {
 
         if (dbName == null)
         {
             dbName = Torque.getDefaultDB();
         }
         DatabaseMap databaseMap = Torque.getDatabaseMap(dbName);
         if (databaseMap == null)
         {
             throw new TorqueException("Could not find database map"
                     + " for database "
                     + dbName);
         }
         String unqualifiedTableName = getUnqualifiedName(tableName, dbName);
         TableMap result = databaseMap.getTable(unqualifiedTableName);
         if (result == null)
         {
             throw new TorqueException("Could not find table "
                     + tableName
                     + " in database map of database "
                     + dbName);
         }
         return result;
     }
 
     /**
      * Returns the database name of a column.
      *
      * @param tableName the name of a table or the alias for a table.
      * @param criteria a criteria object to resolve a possible alias.
      *
      * @return either the tablename itself if tableOrAliasName is not an alias,
      *         or a String of the form "tableName tableOrAliasName"
      *         if tableOrAliasName is an alias for a table name
      */
     static Column resolveAliasAndAsColumnAndSchema(
             final Column columnToResolve,
             final CriteriaInterface<?> criteria)
             throws TorqueException
     {
         String columnNameToResolve = columnToResolve.getColumnName();
         Column resolvedColumn = criteria.getAsColumns().get(columnNameToResolve);
         boolean sqlExpressionModified = false;
         if (resolvedColumn == null)
         {
             resolvedColumn = columnToResolve;
         }
         else
         {
             sqlExpressionModified = true;
         }
         String tableNameToResolve = resolvedColumn.getTableName();
         Object resolvedAlias = criteria.getAliases().get(tableNameToResolve);
         String resolvedTableName;
         if (resolvedAlias == null || !(resolvedAlias instanceof String))
         {
             resolvedTableName = tableNameToResolve;
         }
         else
         {
             resolvedTableName = (String) resolvedAlias;
             sqlExpressionModified = true;
         }
         String resolvedSchemaName = resolvedColumn.getSchemaName();
         if (resolvedSchemaName == null)
         {
             final String dbName = criteria.getDbName();
             final Database database = Torque.getDatabase(dbName);
             resolvedSchemaName = database.getSchema();
         }
         if (sqlExpressionModified)
         {
             return new ColumnImpl(
                     resolvedSchemaName,
                     resolvedTableName,
                     resolvedColumn.getColumnName());
         }
         else
         {
             return new ColumnImpl(
                     resolvedSchemaName,
                     resolvedTableName,
                     resolvedColumn.getColumnName(),
                     resolvedColumn.getSqlExpression());
         }
     }
 
     /**
      * Checks if a fromExpression is already contained in a from clause.
      * Different aliases for the same table are treated
      * as different tables: E.g.
      * fromClauseContainsTableName(fromClause, "table_a a") returns false if
      * fromClause contains only another alias for table_a ,
      * e.g. "table_a aa" and the unaliased tablename "table_a".
      * Special case: If fromClause is null or empty, false is returned.
      *
      * @param fromClause The list to check against.
      * @param fromExpression the fromExpression to check, not null.
      *
      * @return whether the fromExpression is already contained in the from
      *         clause.
      */
     static boolean fromClauseContainsExpression(
             final UniqueList<FromElement> fromClause,
             final PreparedStatementPart fromExpression)
     {
         if (fromExpression == null || fromExpression.getSql().length() == 0)
         {
             return false;
         }
         String fromExpressionSql = fromExpression.getSql().toString();
         for (FromElement fromElement : fromClause)
         {
             if (fromExpressionSql.equals(fromElement.getFromExpression()))
             {
                 return true;
             }
         }
         return false;
     }
 
     /**
      * Adds a table to the from clause of a query, if it is not already
      * contained there.
      *
      * @param tableOrAliasName the name of a table
      *        or the alias for a table. If null, the from clause is left
      *        unchanged.
      * @param criteria a criteria object to resolve a possible alias
      * @param query the query where the the table name should be added
      *        to the from clause
      */
     static void addTableToFromClause(
                 final Object possibleColumn,
                 final CriteriaInterface<?> criteria,
                 Query query)
             throws TorqueException
     {
         if (possibleColumn == null)
         {
             return;
         }
         if (!(possibleColumn instanceof Column))
         {
             return;
         }
         Column column = (Column) possibleColumn;
         if (column.getTableName() == null)
         {
             return;
         }
         PreparedStatementPart fromClauseExpression = getExpressionForFromClause(
                 column,
                 criteria);
 
         UniqueList<FromElement> queryFromClause = query.getFromClause();
 
         // it is important that this piece of code is executed AFTER
         // the joins are processed
         if (!fromClauseContainsExpression(
             queryFromClause,
             fromClauseExpression))
         {
             FromElement fromElement = new FromElement(
                     fromClauseExpression.getSql().toString(),
                     null,
                     null,
                     fromClauseExpression.getPreparedStatementReplacements());
             queryFromClause.add(fromElement);
         }
     }
 
     /**
      * Checks whether ignoreCase is used for this criterion.
      * This is the case if ignoreCase is either set on the criterion
      * or the criteria and if ignoreCase is applicable for both values.
      *
      * @param criterion the value to check.
      * @param criteria the criteria where the criterion stems from.
      * @param database The database to check.
      *
      * @return Whether to use ignoreCase for the passed criterion.
      *
      * @throws TorqueException in the case of an error.
      */
     static boolean isIgnoreCase(
                 Criterion criterion,
                 CriteriaInterface<?> criteria,
                 Database database)
             throws TorqueException
     {
         boolean ignoreCase
             = criteria.isIgnoreCase() || criterion.isIgnoreCase();
         ignoreCase = ignoreCase
                 && ignoreCaseApplicable(
                         criterion.getLValue(),
                         criteria,
                         database)
                 && ignoreCaseApplicable(
                         criterion.getRValue(),
                         criteria,
                         database);
         return ignoreCase;
     }
 
     /**
      * Checks whether ignoreCase is applicable for this column.
      * This is not the case if the value is a column and the column type is
      * not varchar, or if the object is no column and not a String;
      * in all other cases ignoreCase is applicable.
      *
      * @param value the value to check.
      * @param criteria the criteria where the value stems from.
      * @param database The database to check.
      *
      * @return false if ignoreCase is not applicable, true otherwise.
      *
      * @throws TorqueException in the case of an error.
      */
     private static boolean ignoreCaseApplicable(
                 Object value,
                 CriteriaInterface<?> criteria,
                 Database database)
             throws TorqueException
     {
         if (value == null)
         {
             return true;
         }
         if (!(value instanceof Column))
         {
             if (value instanceof String
                     || value instanceof Iterable
                     || value.getClass().isArray())
             {
                 return true;
             }
             return false;
         }
         Column column = (Column) value;
         Column databaseColumn = resolveAliasAndAsColumnAndSchema(
                 column,
                 criteria);
         ColumnMap columnMap = null;
         {
             DatabaseMap databaseMap = database.getDatabaseMap();
             TableMap tableMap = databaseMap.getTable(
                     databaseColumn.getTableName());
             if (tableMap != null)
             {
                 columnMap = tableMap.getColumn(
                         databaseColumn.getColumnName());
             }
         }
         if (columnMap == null)
         {
             return true;
         }
         // do not use ignoreCase on columns
         // which do not contain String values
         return columnMap.getType() instanceof String;
     }
 
     /**
      * Builds an element of the where clause of a prepared statement.
      *
      * @param whereClausePart the part of the where clause to build.
      *        Can be modified during this method.
      * @param ignoreCase If true and columns represent Strings, the appropriate
      *        function defined for the database will be used to ignore
      *        differences in case.
      * @param adapter The adapter for the database for which the SQL
      *        should be created, not null.
      *
      * @deprecated remove when util.Criteria is removed
      */
     @Deprecated
     private static PreparedStatementPart buildPs(
                 WhereClauseExpression whereClausePart,
                 boolean ignoreCase,
                 Adapter adapter)
             throws TorqueException
     {
         PreparedStatementPart result = new PreparedStatementPart();
 
         // Handle SqlEnum.Custom
         if (SqlEnum.CUSTOM == whereClausePart.getOperator())
         {
             result.getSql().append(whereClausePart.getRValue());
             return result;
         }
 
         // Handle SqlEnum.CURRENT_DATE and SqlEnum.CURRENT_TIME
         if (whereClausePart.getRValue() instanceof SqlEnum)
         {
             result.getSql().append(whereClausePart.getLValue())
                 .append(whereClausePart.getOperator())
                 .append(whereClausePart.getRValue());
             return result;
         }
 
         // If rValue is an ObjectKey, take the value of that ObjectKey.
         if (whereClausePart.getRValue() instanceof ObjectKey)
         {
             whereClausePart.setRValue(
                     ((ObjectKey) whereClausePart.getRValue()).getValue());
         }
 
         /*  If rValue is null, check to see if the operator
          *  is an =, <>, or !=.  If so, replace the comparison
          *  with SqlEnum.ISNULL or SqlEnum.ISNOTNULL.
          */
         if (whereClausePart.getRValue() == null)
         {
             if (whereClausePart.getOperator().equals(SqlEnum.EQUAL))
             {
                 result.getSql().append(whereClausePart.getLValue())
                         .append(SqlEnum.ISNULL);
                 return result;
             }
             if (whereClausePart.getOperator().equals(SqlEnum.NOT_EQUAL)
                 || whereClausePart.getOperator().equals(
                         SqlEnum.ALT_NOT_EQUAL))
             {
                 result.getSql().append(whereClausePart.getLValue())
                     .append(SqlEnum.ISNOTNULL);
                 return result;
             }
         }
 
         // Handle SqlEnum.ISNULL and SqlEnum.ISNOTNULL
         if (whereClausePart.getOperator().equals(SqlEnum.ISNULL)
             || whereClausePart.getOperator().equals(SqlEnum.ISNOTNULL))
         {
             result.getSql().append(whereClausePart.getLValue())
                     .append(whereClausePart.getOperator());
             return result;
         }
 
         // handle Subqueries
         if (whereClausePart.getRValue() instanceof Criteria)
         {
             Query subquery = SqlBuilder.buildQuery(
                     (Criteria) whereClausePart.getRValue());
             result.getPreparedStatementReplacements().addAll(
                     subquery.getPreparedStatementReplacements());
             result.getSql().append(whereClausePart.getLValue())
                     .append(whereClausePart.getOperator())
                     .append("(").append(subquery.toString()).append(")");
                 return result;
         }
         if (whereClausePart.getRValue()
                 instanceof org.apache.torque.util.Criteria)
         {
             Query subquery = SqlBuilder.buildQuery(
                     (org.apache.torque.util.Criteria)
                         whereClausePart.getRValue());
             result.getPreparedStatementReplacements().addAll(
                     subquery.getPreparedStatementReplacements());
             result.getSql().append(whereClausePart.getLValue())
                     .append(whereClausePart.getOperator())
                     .append("(").append(subquery.toString()).append(")");
                 return result;
         }
 
         // handle LIKE and similar
         if (whereClausePart.getOperator().equals(Criteria.LIKE)
             || whereClausePart.getOperator().equals(Criteria.NOT_LIKE)
             || whereClausePart.getOperator().equals(Criteria.ILIKE)
             || whereClausePart.getOperator().equals(Criteria.NOT_ILIKE))
         {
             return buildPsLike(whereClausePart, ignoreCase, adapter);
         }
 
         // handle IN and similar
         if (whereClausePart.getOperator().equals(Criteria.IN)
                 || whereClausePart.getOperator().equals(Criteria.NOT_IN))
         {
             return buildPsIn(whereClausePart, ignoreCase, adapter);
         }
 
         // Standard case
         result.getPreparedStatementReplacements().add(
                 whereClausePart.getRValue());
         if (ignoreCase
             && whereClausePart.getRValue() instanceof String)
         {
             result.getSql().append(
                     adapter.ignoreCase((String) whereClausePart.getLValue()))
                 .append(whereClausePart.getOperator())
                 .append(adapter.ignoreCase("?"));
         }
         else
         {
             result.getSql().append(whereClausePart.getLValue())
                 .append(whereClausePart.getOperator())
                 .append("?");
         }
         return result;
     }
 
     /**
      * Takes a WhereClauseExpression with a LIKE operator
      * and builds an SQL phrase based on whether wildcards are present
      * and the state of theignoreCase flag.
      * Multicharacter wildcards % and * may be used
      * as well as single character wildcards, _ and ?.  These
      * characters can be escaped with \.
      *
      * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
      *                        -> UPPER(columnName) LIKE UPPER('fre%')
      *      criteria = "50\%" -> columnName = '50%'
      *
      * @param whereClausePart the part of the where clause to build.
      *        Can be modified in this method.
      * @param ignoreCase If true and columns represent Strings, the appropriate
      *        function defined for the database will be used to ignore
      *        differences in case.
      * @param adapter The adapter for the database for which the SQL
      *        should be created, not null.
      *
      * @return the rendered SQL for the WhereClauseExpression
      *
      * @deprecated remove when util.Criteria is removed
      */
     @Deprecated
     static PreparedStatementPart buildPsLike(
                 WhereClauseExpression whereClausePart,
                 boolean ignoreCase,
                 Adapter adapter)
             throws TorqueException
     {
         if (!(whereClausePart.getRValue() instanceof String))
         {
             throw new TorqueException(
                 "rValue must be a String for the operator "
                     + whereClausePart.getOperator());
         }
         String value = (String) whereClausePart.getRValue();
         // If selection criteria contains wildcards use LIKE otherwise
         // use = (equals).  Wildcards can be escaped by prepending
         // them with \ (backslash). However, if we switch from
         // like to equals, we need to remove the escape characters.
         // from the wildcards.
         // So we need two passes: The first replaces * and ? by % and _,
         // and checks whether we switch to equals,
         // the second removes escapes if we have switched to equals.
         int position = 0;
         StringBuffer sb = new StringBuffer();
         boolean replaceWithEquals = true;
         while (position < value.length())
         {
             char checkWildcard = value.charAt(position);
 
             switch (checkWildcard)
             {
             case BACKSLASH:
                 if (position + 1 >= value.length())
                 {
                     // ignore backslashes at end
                     break;
                 }
                 position++;
                 char escapedChar = value.charAt(position);
                 if (escapedChar != '*' && escapedChar != '?')
                 {
                     sb.append(checkWildcard);
                 }
                 // code below copies escaped character into sb
                 checkWildcard = escapedChar;
                 break;
             case '%':
             case '_':
                 replaceWithEquals = false;
                 break;
             case '*':
                 replaceWithEquals = false;
                 checkWildcard = '%';
                 break;
             case '?':
                 replaceWithEquals = false;
                 checkWildcard = '_';
                 break;
             default:
                 break;
             }
 
             sb.append(checkWildcard);
             position++;
         }
         value = sb.toString();
 
         if (ignoreCase)
         {
             if (adapter.useIlike() && !replaceWithEquals)
             {
                 if (SqlEnum.LIKE.equals(whereClausePart.getOperator()))
                 {
                     whereClausePart.setOperator(SqlEnum.ILIKE);
                 }
                 else if (SqlEnum.NOT_LIKE.equals(whereClausePart.getOperator()))
                 {
                     whereClausePart.setOperator(SqlEnum.NOT_ILIKE);
                 }
             }
             else
             {
                 // no native case insensitive like is offered by the DB,
                 // or the LIKE was replaced with equals.
                 // need to ignore case manually.
                 whereClausePart.setLValue(
                         adapter.ignoreCase((String) whereClausePart.getLValue()));
             }
         }
 
         PreparedStatementPart result = new PreparedStatementPart();
         result.getSql().append(whereClausePart.getLValue());
 
         if (replaceWithEquals)
         {
             if (whereClausePart.getOperator().equals(SqlEnum.NOT_LIKE)
                     || whereClausePart.getOperator().equals(SqlEnum.NOT_ILIKE))
             {
                 result.getSql().append(SqlEnum.NOT_EQUAL);
             }
             else
             {
                 result.getSql().append(SqlEnum.EQUAL);
             }
 
             // remove escape backslashes from String
             position = 0;
             sb = new StringBuffer();
             while (position < value.length())
             {
                 char checkWildcard = value.charAt(position);
 
                 if (checkWildcard == BACKSLASH
                         && position + 1 < value.length())
                 {
                     position++;
                     // code below copies escaped character into sb
                     checkWildcard = value.charAt(position);
                 }
                 sb.append(checkWildcard);
                 position++;
             }
             value = sb.toString();
         }
         else
         {
             result.getSql().append(whereClausePart.getOperator());
         }
 
         String rValueSql = "?";
         // handle ignoreCase if necessary
         if (ignoreCase && (!(adapter.useIlike()) || replaceWithEquals))
         {
             rValueSql = adapter.ignoreCase(rValueSql);
         }
         // handle escape clause if necessary
         if (!replaceWithEquals && adapter.useEscapeClauseForLike())
         {
             rValueSql = rValueSql + SqlEnum.ESCAPE + "'\\'";
         }
 
         result.getPreparedStatementReplacements().add(value);
         result.getSql().append(rValueSql);
         return result;
     }
 
     /**
      * Takes a columnName and criteria and
      * builds a SQL 'IN' expression taking into account the ignoreCase
      * flag.
      *
      * @param whereClausePart the part of the where clause to build.
      *        Can be modified in this method.
      * @param ignoreCase If true and columns represent Strings, the appropriate
      *        function defined for the database will be used to ignore
      *        differences in case.
      * @param adapter The adapter for the database for which the SQL
      *        should be created, not null.
      *
      * @return the built part.
      *
      * @deprecated remove when util.Criteria is removed
      */
     @Deprecated
     static PreparedStatementPart buildPsIn(
             WhereClauseExpression whereClausePart,
             boolean ignoreCase,
             Adapter adapter)
     {
         PreparedStatementPart result = new PreparedStatementPart();
 
         boolean ignoreCaseApplied = false;
         List<String> inClause = new ArrayList<String>();
         boolean nullContained = false;
         if (whereClausePart.getRValue() instanceof Iterable)
         {
             for (Object listValue : (Iterable<?>) whereClausePart.getRValue())
             {
                 if (listValue == null)
                 {
                     nullContained = true;
                     continue;
                 }
                 result.getPreparedStatementReplacements().add(listValue);
                 if (ignoreCase && listValue instanceof String)
                 {
                     inClause.add(adapter.ignoreCase("?"));
                     ignoreCaseApplied = true;
                 }
                 else
                 {
                     inClause.add("?");
                 }
             }
         }
         else if (whereClausePart.getRValue().getClass().isArray())
         {
             for (Object arrayValue : (Object[]) whereClausePart.getRValue())
             {
                 if (arrayValue == null)
                 {
                     nullContained = true;
                     continue;
                 }
                 result.getPreparedStatementReplacements().add(arrayValue);
                 if (ignoreCase && arrayValue instanceof String)
                 {
                     inClause.add(adapter.ignoreCase("?"));
                     ignoreCaseApplied = true;
                 }
                 else
                 {
                     inClause.add("?");
                 }
             }
         }
         else
         {
             throw new IllegalArgumentException(
                     "Unknown rValue type "
                     + whereClausePart.getRValue().getClass().getName()
                     + ". rValue must be an instance of "
                     + " Iterable or Array");
         }
 
         if (nullContained)
         {
             result.getSql().append('(');
         }
 
         if (ignoreCaseApplied)
         {
             result.getSql().append(
                     adapter.ignoreCase((String) whereClausePart.getLValue()));
         }
         else
         {
             result.getSql().append(whereClausePart.getLValue());
         }
 
         result.getSql().append(whereClausePart.getOperator())
                 .append('(')
                 .append(StringUtils.join(inClause.iterator(), ","))
                 .append(')');
         if (nullContained)
         {
             if (whereClausePart.getOperator() == SqlEnum.IN)
             {
                 result.getSql().append(Criterion.OR)
                     .append(whereClausePart.getLValue()).append(SqlEnum.ISNULL);
             }
             else if (whereClausePart.getOperator() == SqlEnum.NOT_IN)
             {
                 result.getSql().append(Criterion.AND)
                     .append(whereClausePart.getLValue()).append(
                             SqlEnum.ISNOTNULL);
             }
             result.getSql().append(')');
         }
         return result;
     }
 }