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.lang.reflect.Array;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.torque.BaseTestCase;
import org.apache.torque.Column;
import org.apache.torque.Database;
import org.apache.torque.TorqueException;
import org.apache.torque.om.NumberKey;
import org.apache.torque.util.Criteria;

/**
 * Tests for SqlExpression
 *
 * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
 * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a>
 * @version $Id: SqlBuilderOldCriteriaTest.java 1448414 2013-02-20 21:06:35Z tfischer $
 */
public class SqlBuilderOldCriteriaTest extends BaseTestCase
{
    /**
     * Test that unknown columns are treated case-insensitive if ignoreCase
     * is set.
     */
    public void testignoreCaseUnknownColumnType() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("unknownTable.column1");
        criteria.add("column1", "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT unknownTable.column1 FROM unknownTable "
                   + "WHERE UPPER(column1)=UPPER(?)",
                query.toString());
    }

    public void testIgnoreCaseStringColumnType() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(stringColumnMap);
        criteria.add(stringColumnMap, "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN1 FROM TABLE "
                   + "WHERE UPPER(TABLE.COLUMN1)=UPPER(?)",
                query.toString());
    }

    public void testIgnoreCaseIntegerColumnType() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(integerColumnMap);
        criteria.add(integerColumnMap, "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN4 FROM TABLE "
                   + "WHERE TABLE.COLUMN4=?",
                query.toString());
    }

    public void testOrderByDesc() throws TorqueException
    {
        Criteria criteria = new Criteria();
        criteria.addDescendingOrderByColumn("table.column1");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT table.column1 FROM table ORDER BY table.column1 DESC",
                query.toString());
    }

    public void testOrderByAsc() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn("table.column1");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT table.column1 FROM table ORDER BY table.column1 ASC",
                query.toString());
    }

    public void testOrderByNullColumn() throws Exception
    {
        Criteria criteria = new Criteria();
        try
        {
            criteria.addAscendingOrderByColumn((Column) null);
            fail("Exception expected");
        }
        catch (NullPointerException e)
        {
            assertEquals("column is null", e.getMessage());
        }
    }

    public void testOrderByMultiple() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn("table.column1");
        criteria.addDescendingOrderByColumn("table2.column2");
        criteria.addAscendingOrderByColumn("table3.column1");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT table.column1, table2.column2, table3.column1"
                    + " FROM table, table2, table3"
                    + " ORDER BY table.column1 ASC,"
                    + " table2.column2 DESC,"
                    + " table3.column1 ASC",
                query.toString());
    }

    public void testOrderByWithDefaultSchema() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn("table.column1");
        String oldSchema = database.getSchema();
        try
        {
            database.setSchema("schema1");
            criteria.setDbName(database.getName());
            Query query = SqlBuilder.buildQuery(criteria);
            assertEquals(
                    "SELECT table.column1 FROM schema1.table "
                        + "ORDER BY table.column1 ASC",
                    query.toString());
        }
        finally
        {
            database.setSchema(oldSchema);
        }
    }

    public void testOrderByAscendingIgnoreCaseString() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn(stringColumnMap, true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
                    + "ORDER BY UPPER(TABLE.COLUMN1) ASC",
                query.toString());
    }

    public void testOrderByAscendingIgnoreCaseInteger() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn(integerColumnMap, true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN4 FROM TABLE "
                    + "ORDER BY TABLE.COLUMN4 ASC",
                query.toString());
    }

    public void testOrderByAscendingIgnoreCaseStringInCriteria()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn(stringColumnMap);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
                    + "ORDER BY UPPER(TABLE.COLUMN1) ASC",
                query.toString());
    }

    public void testOrderByAscendingIgnoreCaseIntegerInCriteria()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn(integerColumnMap);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN4 FROM TABLE "
                    + "ORDER BY TABLE.COLUMN4 ASC",
                query.toString());
    }

    public void testOrderByDescendingIgnoreCaseString() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addDescendingOrderByColumn(stringColumnMap, true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
                    + "ORDER BY UPPER(TABLE.COLUMN1) DESC",
                query.toString());
    }

    public void testOrderByDescendingIgnoreCaseInteger() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addDescendingOrderByColumn(integerColumnMap, true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN4 FROM TABLE "
                    + "ORDER BY TABLE.COLUMN4 DESC",
                query.toString());
    }

    public void testOrderByDescendingIgnoreCaseStringInCriteria()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addDescendingOrderByColumn(stringColumnMap);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
                    + "ORDER BY UPPER(TABLE.COLUMN1) DESC",
                query.toString());
    }

    public void testOrderByDescendingIgnoreCaseIntegerInCriteria()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addDescendingOrderByColumn(integerColumnMap);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN4 FROM TABLE "
                    + "ORDER BY TABLE.COLUMN4 DESC",
                query.toString());
    }

    public void testAlias() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAlias("alias", "table");
        criteria.addSelectColumn("alias.column1");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT alias.column1 FROM table alias",
                query.toString());
    }

    public void testAliasWithDefaultSchema() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAlias("alias", "table");
        criteria.addSelectColumn("alias.column1");
        String oldSchema = database.getSchema();
        try
        {
            database.setSchema("schema1");
            criteria.setDbName(database.getName());
            Query query = SqlBuilder.buildQuery(criteria);
            assertEquals(
                    "SELECT alias.column1 FROM schema1.table alias",
                    query.toString());
        }
        finally
        {
            database.setSchema(oldSchema);
        }
    }

    public void testAliasWithIgnoreCaseUnknownColumnType() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAlias("alias", "table");
        criteria.addSelectColumn("alias.column1");
        criteria.add("alias.column1", "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT alias.column1 FROM table alias "
                   + "WHERE UPPER(alias.column1)=UPPER(?)",
                query.toString());
    }

    public void testAliasWithIgnoreCaseStringColumnType() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAlias("alias", tableMap.getName());
        criteria.addSelectColumn("alias.COLUMN1");
        criteria.add("alias.COLUMN1", "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT alias.COLUMN1 FROM TABLE alias "
                   + "WHERE UPPER(alias.COLUMN1)=UPPER(?)",
                query.toString());
    }

    public void testAliasWithIgnoreCaseIntegerColumnType() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAlias("alias", tableMap.getName());
        criteria.addSelectColumn("alias.COLUMN4");
        criteria.add("alias.COLUMN4", "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT alias.COLUMN4 FROM TABLE alias "
                   + "WHERE alias.COLUMN4=?",
                query.toString());
    }

    public void testAliasWithIgnoreCaseStringColumnTypeAndDefaultSchema()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAlias("alias", tableMap.getName());
        criteria.addSelectColumn("alias.COLUMN1");
        criteria.add("alias.COLUMN1", "1");
        criteria.setIgnoreCase(true);
        String oldSchema = database.getSchema();
        try
        {
            database.setSchema("schema1");
            criteria.setDbName(database.getName());
            Query query = SqlBuilder.buildQuery(criteria);
            assertEquals(
                    "SELECT alias.COLUMN1 FROM schema1.TABLE alias "
                       + "WHERE UPPER(alias.COLUMN1)=UPPER(?)",
                    query.toString());
        }
        finally
        {
            database.setSchema(oldSchema);
        }
    }

    public void testAliasWithIgnoreCaseIntegerColumnTypeAndDefaultSchema()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAlias("alias", tableMap.getName());
        criteria.addSelectColumn("alias.COLUMN4");
        criteria.add("alias.COLUMN4", "1");
        criteria.setIgnoreCase(true);
        String oldSchema = database.getSchema();
        try
        {
            database.setSchema("schema1");
            criteria.setDbName(database.getName());
            Query query = SqlBuilder.buildQuery(criteria);
            assertEquals(
                    "SELECT alias.COLUMN4 FROM schema1.TABLE alias "
                       + "WHERE alias.COLUMN4=?",
                    query.toString());
        }
        finally
        {
            database.setSchema(oldSchema);
        }
    }

    public void testAsColumn() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAsColumn("columnAlias", stringColumnMap);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN1 AS columnAlias FROM TABLE",
                query.toString());
    }

    public void testAsColumnWithIgnoreCaseUnknownColumn() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAsColumn("columnAlias", "table.column");
        criteria.add("columnAlias", "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT table.column AS columnAlias FROM table"
                    + " WHERE UPPER(columnAlias)=UPPER(?)",
                query.toString());
    }

    public void testAsColumnWithIgnoreCaseStringColumn() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAsColumn("columnAlias", stringColumnMap);
        criteria.add("columnAlias", "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN1 AS columnAlias FROM TABLE"
                    + " WHERE UPPER(columnAlias)=UPPER(?)",
                query.toString());
    }

    public void testAsColumnWithIgnoreCaseIntegerColumn() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAsColumn("columnAlias", integerColumnMap);
        criteria.add("columnAlias", "1");
        criteria.setIgnoreCase(true);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN4 AS columnAlias FROM TABLE"
                    + " WHERE columnAlias=?",
                query.toString());
    }

    public void testAsColumnWithIgnoreCaseStringColumnAndDefaultSchema()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAsColumn("columnAlias", stringColumnMap);
        criteria.addSelectColumn("columnAlias");
        criteria.add("columnAlias", "1");
        criteria.setIgnoreCase(true);
        String oldSchema = database.getSchema();
        try
        {
            database.setSchema("schema1");
            criteria.setDbName(database.getName());
            Query query = SqlBuilder.buildQuery(criteria);
            assertEquals(
                    "SELECT TABLE.COLUMN1 AS columnAlias FROM schema1.TABLE"
                        + " WHERE UPPER(columnAlias)=UPPER(?)",
                    query.toString());
        }
        finally
        {
            database.setSchema(oldSchema);
        }
    }

    public void testAsColumnWithIgnoreCaseIntegerColumnAndDefaultSchema()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAsColumn("columnAlias", integerColumnMap);
        criteria.addSelectColumn("columnAlias");
        criteria.add("columnAlias", "1");
        criteria.setIgnoreCase(true);
        String oldSchema = database.getSchema();
        try
        {
            database.setSchema("schema1");
            criteria.setDbName(database.getName());
            Query query = SqlBuilder.buildQuery(criteria);
            assertEquals(
                    "SELECT TABLE.COLUMN4 AS columnAlias FROM schema1.TABLE"
                        + " WHERE columnAlias=?",
                    query.toString());
        }
        finally
        {
            database.setSchema(oldSchema);
        }
    }

    public void testInnerJoinImplicit()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column");
        criteria.addJoin("table1.column1", "table2.column2");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT table1.column FROM table1, table2"
                    + " WHERE table1.column1=table2.column2",
                query.toString());
    }


    public void testInnerJoinExplicit()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column1");
        criteria.addJoin(
                "table1.column1",
                "table2.column2",
                Criteria.INNER_JOIN);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT table1.column1 FROM table1 INNER JOIN table2"
                    + " ON table1.column1=table2.column2",
                query.toString());
    }

    public void testInnerJoinWithExcplicitExistingRightTable()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column1");
        criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
        criteria.addJoin("table1.column1", "table2.column2", Criteria.INNER_JOIN);
        Query query = SqlBuilder.buildQuery(criteria);
        // second join condition must be changed in order to satisfy
        // first join condition
        assertEquals(
                "SELECT table1.column1"
                    + " FROM table2 INNER JOIN table3"
                    + " ON table2.column2=table3.column3"
                    + " INNER JOIN table1"
                    + " ON table1.column1=table2.column2",
                query.toString());
    }

    public void testInnerJoinExcplicitWithExistingRightAndLeftTable()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column1");
        criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
        criteria.addJoin("table1.column1", "table4.column4", Criteria.INNER_JOIN);
        criteria.addJoin("table1.column1", "table2.column2", Criteria.INNER_JOIN);
        try
        {
            SqlBuilder.buildQuery(criteria);
            fail("Exception expected");
        }
        catch (TorqueException e)
        {
            assertEquals("Unable to create a INNER JOIN "
                    + "because both expressions table1 and table2 "
                    + "are already in use. Try to create an(other) alias.",
                e.getMessage());
        }
    }

    public void testLeftJoin()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column1");
        criteria.addJoin("table1.column", "table2.column", Criteria.LEFT_JOIN);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT table1.column1"
                    + " FROM table1 LEFT JOIN table2"
                    + " ON table1.column=table2.column",
                query.toString());
    }

    public void testLeftJoinWithExistingRightTable()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column1");
        criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
        criteria.addJoin("table1.column1", "table2.column2", Criteria.LEFT_JOIN);
        Query query = SqlBuilder.buildQuery(criteria);
        // left join must be converted to right join to satisfy
        // first join condition
        assertEquals(
                "SELECT table1.column1"
                    + " FROM table2 INNER JOIN table3"
                    + " ON table2.column2=table3.column3"
                    + " RIGHT JOIN table1"
                    + " ON table1.column1=table2.column2",
                query.toString());
    }

    public void testRightJoin()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column1");
        criteria.addJoin("table1.column", "table2.column", Criteria.RIGHT_JOIN);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT table1.column1"
                    + " FROM table1 RIGHT JOIN table2"
                    + " ON table1.column=table2.column",
                query.toString());
    }

    public void testRightJoinWithExistingRightTable()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column1");
        criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
        criteria.addJoin("table1.column1", "table2.column2", Criteria.RIGHT_JOIN);
        Query query = SqlBuilder.buildQuery(criteria);
        // right join must be converted to left join to satisfy
        // first join condition
        assertEquals(
                "SELECT table1.column1"
                    + " FROM table2 INNER JOIN table3"
                    + " ON table2.column2=table3.column3"
                    + " LEFT JOIN table1"
                    + " ON table1.column1=table2.column2",
                query.toString());
    }

    public void testInnerJoinImplicitWithAlias()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("alias.column1");
        criteria.addAlias("alias", "table1");
        criteria.addJoin("alias.column", "table2.column");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT alias.column1 FROM table1 alias, table2"
                    + " WHERE alias.column=table2.column",
                query.toString());
    }

    public void testInnerJoinImplicitWithAliasAndAsColumn()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAsColumn("x", "alias.column");
        criteria.addAlias("alias", "table1");
        criteria.addJoin("x", "table2.column");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT alias.column AS x FROM table2, table1 alias"
                    + " WHERE x=table2.column",
                query.toString());
    }


    public void testInnerJoinImplicitWithDefaultSchema()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("*");
        criteria.addJoin("table1.column", "table2.column");
        String oldSchema = database.getSchema();
        try
        {
            database.setSchema("schema1");
            criteria.setDbName(database.getName());
            Query query = SqlBuilder.buildQuery(criteria);
            assertEquals(
                    "SELECT *"
                        + " FROM schema1.table1, schema1.table2"
                        + " WHERE table1.column=table2.column",
                    query.toString());
        }
        finally
        {
            database.setSchema(oldSchema);
        }
    }

    public void testInnerJoinImplicitWithAliasAndDefaultSchema()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("alias.column1");
        criteria.addAlias("alias", "table1");
        criteria.addJoin("alias.column", "table2.column");
        String oldSchema = database.getSchema();
        try
        {
            database.setSchema("schema1");
            criteria.setDbName(database.getName());
            Query query = SqlBuilder.buildQuery(criteria);
            assertEquals(
                    "SELECT alias.column1 "
                        + "FROM schema1.table1 alias, schema1.table2"
                        + " WHERE alias.column=table2.column",
                    query.toString());
        }
        finally
        {
            database.setSchema(oldSchema);
        }
    }

    public void testInnerJoinImplicitWithAliasAndSchema()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("schema1.alias.column1");
        criteria.addAlias("alias", "table1");
        criteria.addJoin("schema1.alias.column", "schema2.table2.column");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT alias.column1 FROM schema1.table1 alias, schema2.table2"
                    + " WHERE alias.column=table2.column",
                query.toString());
    }

    public void testDistinct()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table1.column1");
        criteria.setDistinct();
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT DISTINCT table1.column1 FROM table1",
                query.toString());
    }

    public void testGroupBy()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addGroupByColumn(stringColumnMap);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals(
                "SELECT TABLE.COLUMN1 FROM TABLE GROUP BY TABLE.COLUMN1",
                query.toString());
    }

    public void testLimitPostgresql() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(stringColumnMap);
        criteria.setLimit(20);
        criteria.setDbName(databasePostgresql.getName());
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
                + "LIMIT 20",
            query.toString());
    }

    public void testOffsetPostgresql() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(stringColumnMap);
        criteria.setOffset(10);
        criteria.setDbName(databasePostgresql.getName());
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
                + "OFFSET 10",
            query.toString());
    }

    public void testLimitOffsetPostgresql() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(stringColumnMap);
        criteria.setLimit(20);
        criteria.setOffset(10);
        criteria.setDbName(databasePostgresql.getName());
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
                + "LIMIT 20 OFFSET 10",
            query.toString());
    }

    public void testLimitMysql() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(stringColumnMap);
        criteria.setLimit(20);
        criteria.setDbName(databaseMysql.getName());
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
                + "LIMIT 20",
            query.toString());
    }

    public void testOffsetMysql() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(stringColumnMap);
        criteria.setOffset(10);
        criteria.setDbName(databaseMysql.getName());
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
                + "LIMIT 18446744073709551615 OFFSET 10",
            query.toString());
    }

    public void testLimitOffsetMysql() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(stringColumnMap);
        criteria.setLimit(20);
        criteria.setOffset(10);
        criteria.setDbName(databaseMysql.getName());
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
                + "LIMIT 20 OFFSET 10",
            query.toString());
    }

    public void testHaving() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addGroupByColumn(stringColumnMap);
        criteria.addAsColumn("count", "count(*)");
        criteria.addSelectColumn(stringColumnMap);
        criteria.addHaving(
                criteria.getNewCriterion("count", 10, Criteria.GREATER_EQUAL));
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT TABLE.COLUMN1, count(*) AS count FROM TABLE "
                + "GROUP BY TABLE.COLUMN1 HAVING count>=10",
            query.toString());
    }

    public void testSelectColumnWithoutTable()
            throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("*");
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT * FROM ", query.toString());
    }


    public void testCriterionCustom() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("A", (Object) "A = functionOf(B)", Criteria.CUSTOM);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table WHERE A = functionOf(B)",
                query.toString());
        assertEquals(0, query.getPreparedStatementReplacements().size());
    }

    public void testCriterionCurrentDate() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("column", Criteria.CURRENT_DATE);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table "
                + "WHERE column=CURRENT_DATE",
            query.toString());
        assertEquals(0, query.getPreparedStatementReplacements().size());
    }

    public void testCriterionCurrentTime() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("column", Criteria.CURRENT_TIME);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table "
                + "WHERE column=CURRENT_TIME",
            query.toString());
        assertEquals(0, query.getPreparedStatementReplacements().size());
    }

    public void testCriterionObjectKey() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("column", new NumberKey(11));
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table "
                + "WHERE column=?",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                new BigDecimal(11),
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionNullValue() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("column", null);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table "
                + "WHERE column IS NULL",
            query.toString());
        assertEquals(0, query.getPreparedStatementReplacements().size());
    }

    public void testCriterionNullValueNotEqual() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("column", (Object) null, Criteria.NOT_EQUAL);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table "
                + "WHERE column IS NOT NULL",
            query.toString());
        assertEquals(0, query.getPreparedStatementReplacements().size());
    }

    public void testCriterionNullValueAltNotEqual() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("column", (Object) null, Criteria.ALT_NOT_EQUAL);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table "
                + "WHERE column IS NOT NULL",
            query.toString());
        assertEquals(0, query.getPreparedStatementReplacements().size());
    }

    public void testCriterionIsNull() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("column", (Object) "value ignored", Criteria.ISNULL);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table "
                + "WHERE column IS NULL",
            query.toString());
        assertEquals(0, query.getPreparedStatementReplacements().size());
    }

    public void testCriterionIsNotNull() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column");
        criteria.add("column", (Object) "value ignored", Criteria.ISNOTNULL);
        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column FROM table "
                + "WHERE column IS NOT NULL",
            query.toString());
        assertEquals(0, query.getPreparedStatementReplacements().size());
    }

    public void testCriterionCriteria() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");

        Criteria subquery = new Criteria();
        subquery.add("table.column2", "value2");
        subquery.addSelectColumn("table.column3");
        criteria.add("table.column3", subquery);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column3=("
                + "SELECT table.column3 FROM table "
                + "WHERE table.column2=?)",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals("value2", query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionLike() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add("table.column2", (Object) "*v%al_e2?", Criteria.LIKE);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column2 LIKE ?",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "%v%al_e2_",
                query.getPreparedStatementReplacements().get(0));
    }

    /**
     * Test whether LIKE clauses with Escapes are built correctly.
     */
    public void testCriterionLikeWithEscape() throws TorqueException
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add(
                "table.column2",
                (Object) "\\*v\\%al\\_e\\\\*2\\?\\",
                Criteria.LIKE);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column2 LIKE ?",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "*v\\%al\\_e\\\\%2?\\",
                query.getPreparedStatementReplacements().get(0));
    }

    /**
     * Test whether LIKE clauses with Escapes are built correctly in Oracle.
     * Oracle needs to have an ESCAPE clause
     */
    public void testCriterionLikeWithEscapeOracle() throws TorqueException
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add(
                "table.column2",
                (Object) "\\*v\\%al\\_e\\\\*2\\?\\",
                Criteria.LIKE);
        criteria.setDbName(databaseOracle.getName());

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column2 LIKE ? ESCAPE '\\'",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "*v\\%al\\_e\\\\%2?\\",
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionLikeIgnoreCase() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add("table.column2", (Object) "*v%al_e2?", Criteria.LIKE);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column2 ILIKE ?",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "%v%al_e2_",
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionLikeIgnoreCaseNoWildcard() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add("table.column2", (Object) "value\\\\2", Criteria.LIKE);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE UPPER(table.column2)=UPPER(?)",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "value\\2",
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionLikeInteger() throws TorqueException
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add(
                "table.column2",
                1,
                Criteria.LIKE);

        try
        {
            SqlBuilder.buildQuery(criteria);
            fail("Exception expected");
        }
        catch (TorqueException e)
        {
            assertEquals("rValue must be a String for the operator  LIKE ",
                    e.getMessage());
        }
    }

    public void testCriterionNotLike() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add("table.column2", (Object) "*val_e2?", Criteria.NOT_LIKE);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column2 NOT LIKE ?",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "%val_e2_",
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionNotLikeIgnoreCase() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add("table.column2", (Object) "*v%al_e2?", Criteria.NOT_LIKE);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column2 NOT ILIKE ?",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "%v%al_e2_",
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionNotLikeIgnoreCaseNoWildcard() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add("table.column2", (Object) "value\\\\2", Criteria.NOT_LIKE);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE UPPER(table.column2)<>UPPER(?)",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "value\\2",
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionIlike() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add("table.column2", (Object) "*val_e2?", Criteria.ILIKE);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column2 ILIKE ?",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "%val_e2_",
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionNotIlike() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add("table.column2", (Object) "*val_e2?", Criteria.NOT_ILIKE);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE table.column2 NOT ILIKE ?",
            query.toString());
        assertEquals(1, query.getPreparedStatementReplacements().size());
        assertEquals(
                "%val_e2_",
                query.getPreparedStatementReplacements().get(0));
    }

    public void testCriterionInArray() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add(
                "table.column2",
                new String[] {"a", "b", null, null},
                Criteria.IN);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
            query.toString());
        assertEquals(2, query.getPreparedStatementReplacements().size());
        assertEquals("a", query.getPreparedStatementReplacements().get(0));
        assertEquals("b", query.getPreparedStatementReplacements().get(1));
    }

    public void testCriterionInArrayIgnoreCase() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add(
                "table.column2",
                new String[] {"a", "b", null, null},
                Criteria.IN);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?)) "
                + "OR table.column2 IS NULL)",
            query.toString());
        List<Object> replacements = query.getPreparedStatementReplacements();
        assertEquals(2, replacements.size());
        assertEquals("a", replacements.get(0));
        assertEquals("b", replacements.get(1));
    }

    public void testCriterionInList() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        List<Integer> inList = new ArrayList<Integer>();
        inList.add(1);
        inList.add(2);
        inList.add(null);
        inList.add(null);
        criteria.add(
                "table.column2",
                inList,
                Criteria.IN);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE (table.column2 IN (?,?) "
                + "OR table.column2 IS NULL)",
            query.toString());
        assertEquals(2, query.getPreparedStatementReplacements().size());
        assertEquals(1, query.getPreparedStatementReplacements().get(0));
        assertEquals(2, query.getPreparedStatementReplacements().get(1));
    }

    public void testCriterionInListIgnoreCase() throws Exception
        {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        List<String> inList = new ArrayList<String>();
        inList.add("a");
        inList.add(null);
        inList.add("b");
        inList.add(null);
        criteria.add(
                "table.column2",
                inList,
                Criteria.IN);
        criteria.setIgnoreCase(true);

        Query query = SqlBuilder.buildQuery(criteria);
        assertEquals("SELECT table.column1 FROM table "
                + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?)) "
                + "OR table.column2 IS NULL)",
            query.toString());
        List<Object> replacements = query.getPreparedStatementReplacements();
        assertEquals(2, replacements.size());
        assertEquals("a", replacements.get(0));
        assertEquals("b", replacements.get(1));
    }

    public void testCriterionInLargeArray() throws TorqueException
    {
        int size = 10000;
        String[] values = new String[size];
        for (int i = 0; i < size; i++)
        {
            Array.set(values, i, String.valueOf(i));
        }
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add(
                "table.column2",
                values,
                Criteria.IN);
        long start = System.currentTimeMillis();
        Query query = SqlBuilder.buildQuery(criteria);
        long end =  System.currentTimeMillis();
        List<Object> replacements = query.getPreparedStatementReplacements();
        assertEquals(size, replacements.size());
        // time limit 100 ms
        assertTrue(end - start < 100L);
    }

    public void testCriterionInString() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addSelectColumn("table.column1");
        criteria.add(
                "table.column2",
                (Object) "illegal in value",
                Criteria.IN);

        try
        {
            SqlBuilder.buildQuery(criteria);
            fail("Exception expected");
        }
        catch (IllegalArgumentException e)
        {
            assertEquals("Unknown rValue type java.lang.String. "
                    + "rValue must be an instance of  Iterable or Array",
            e.getMessage());
        }
    }
}
