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 1450486 2013-02-26 22:18:00Z 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);
        processForUpdate(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());
    }

    /**
     * Adds a possible FOR UPDATE Clause to the query.
     *
     * @param criteria the criteria from which the query should be built.
     * @param query the query to build.
     *
     * @throws TorqueException if the Database adapter cannot be obtained
     */
    private static void processForUpdate(
            final Criteria criteria,
            final Query query)
            throws TorqueException
    {
        if (criteria.isForUpdate())
        {
            Adapter adapter = Torque.getAdapter(criteria.getDbName());
            query.setForUpdate(adapter.getUpdateLockClause());
        }
    }

    /**
     * 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;
    }
}
