Added: db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java?rev=1224848&view=auto ============================================================================== --- db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java (added) +++ db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java Tue Dec 27 01:17:11 2011 @@ -0,0 +1,1238 @@ +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:[email protected]";>Martin Poeschl</a> + * @author <a href="mailto:[email protected]";>Scott Eade</a> + * @version $Id: SqlBuilderTest.java 1211142 2011-12-06 21:04:03Z tfischer $ + */ +public class SqlBuilderTestOldCriteria 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"); + Database database = databaseMap.getDatabase(); + 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()); + } + + 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"); + Database database = databaseMap.getDatabase(); + database.setSchema("schema1"); + criteria.setDbName(database.getName()); + Query query = SqlBuilder.buildQuery(criteria); + assertEquals( + "SELECT alias.column1 FROM schema1.table alias", + query.toString()); + } + + 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); + Database database = databaseMap.getDatabase(); + 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()); + } + + 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); + Database database = databaseMap.getDatabase(); + 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()); + } + + 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); + Database database = databaseMap.getDatabase(); + 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()); + } + + public void testAsColumnWithIgnoreCaseIntegerColumnAndDefaultSchema() + throws Exception + { + Criteria criteria = new Criteria(); + criteria.addAsColumn("columnAlias", integerColumnMap); + criteria.addSelectColumn("columnAlias"); + criteria.add("columnAlias", "1"); + criteria.setIgnoreCase(true); + Database database = databaseMap.getDatabase(); + 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()); + } + + 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 table2.column2=table1.column1", + 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 table names 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 table2.column2=table1.column1", + 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 table2.column2=table1.column1", + 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"); + Database database = databaseMap.getDatabase(); + 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()); + } + + public void testInnerJoinImplicitWithAliasAndDefaultSchema() + throws Exception + { + Criteria criteria = new Criteria(); + criteria.addSelectColumn("alias.column1"); + criteria.addAlias("alias", "table1"); + criteria.addJoin("alias.column", "table2.column"); + Database database = databaseMap.getDatabase(); + 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()); + } + + 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}, + Criteria.IN); + + Query query = SqlBuilder.buildQuery(criteria); + assertEquals("SELECT table.column1 FROM table " + + "WHERE table.column2 IN (?,?,?)", + query.toString()); + assertEquals(3, query.getPreparedStatementReplacements().size()); + assertEquals("a", query.getPreparedStatementReplacements().get(0)); + assertEquals("b", query.getPreparedStatementReplacements().get(1)); + assertEquals(null, query.getPreparedStatementReplacements().get(2)); + } + + public void testCriterionInArrayIgnoreCase() throws Exception + { + Criteria criteria = new Criteria(); + criteria.addSelectColumn("table.column1"); + criteria.add( + "table.column2", + new String[] {"a", "b", null}, + Criteria.IN); + criteria.setIgnoreCase(true); + + Query query = SqlBuilder.buildQuery(criteria); + assertEquals("SELECT table.column1 FROM table " + + "WHERE UPPER(table.column2) IN (UPPER(?),UPPER(?),?)", + query.toString()); + List<Object> replacements = query.getPreparedStatementReplacements(); + assertEquals(3, replacements.size()); + assertEquals("a", replacements.get(0)); + assertEquals("b", replacements.get(1)); + assertEquals(null, replacements.get(2)); + } + + 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); + criteria.add( + "table.column2", + inList, + Criteria.IN); + + Query query = SqlBuilder.buildQuery(criteria); + assertEquals("SELECT table.column1 FROM table " + + "WHERE table.column2 IN (?,?,?)", + query.toString()); + assertEquals(3, query.getPreparedStatementReplacements().size()); + assertEquals(1, query.getPreparedStatementReplacements().get(0)); + assertEquals(2, query.getPreparedStatementReplacements().get(1)); + assertEquals(null, query.getPreparedStatementReplacements().get(2)); + } + + public void testCriterionInListIgnoreCase() throws Exception + { + Criteria criteria = new Criteria(); + criteria.addSelectColumn("table.column1"); + List<String> inList = new ArrayList<String>(); + inList.add("a"); + 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(?),?)", + query.toString()); + List<Object> replacements = query.getPreparedStatementReplacements(); + assertEquals(3, replacements.size()); + assertEquals("a", replacements.get(0)); + assertEquals("b", replacements.get(1)); + assertEquals(null, replacements.get(2)); + } + + 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 50 ms + assertTrue(end - start < 50L); + } + + 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()); + } + } +}
Modified: db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/CriteriaTest.java URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/CriteriaTest.java?rev=1224848&r1=1224847&r2=1224848&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/CriteriaTest.java (original) +++ db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/CriteriaTest.java Tue Dec 27 01:17:11 2011 @@ -30,6 +30,8 @@ import org.apache.torque.BaseTestCase; import org.apache.torque.ColumnImpl; import org.apache.torque.Torque; import org.apache.torque.TorqueException; +import org.apache.torque.criteria.Join; +import org.apache.torque.criteria.SqlEnum; import org.apache.torque.map.ColumnMap; import org.apache.torque.map.DatabaseMap; import org.apache.torque.map.TableMap; @@ -37,7 +39,6 @@ import org.apache.torque.sql.OrderBy; import org.apache.torque.sql.Query; import org.apache.torque.sql.SqlBuilder; import org.apache.torque.util.Criteria.Criterion; -import org.apache.torque.util.Criteria.Join; /** * Test class for Criteria. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
