db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tfisc...@apache.org
Subject svn commit: r1237886 - in /db/torque/torque4/trunk/torque-runtime/src: main/java/org/apache/torque/criteria/ main/java/org/apache/torque/map/ main/java/org/apache/torque/sql/ main/java/org/apache/torque/util/ test/java/org/apache/torque/criteria/ test/...
Date Mon, 30 Jan 2012 18:17:29 GMT
Author: tfischer
Date: Mon Jan 30 18:17:29 2012
New Revision: 1237886

URL: http://svn.apache.org/viewvc?rev=1237886&view=rev
Log:
- TORQUE-100 add clone method for Criteria
- TORQUE-85 offset is now a long value
- Fix SQL generation if ordered by an alias value
- Handle null values in I N and NOT IN clauses correctly
- TORQUE-176 Tests for brackets in sql expression with many chained criterions
- TORQUE-89 Tests for some uniusual order by contents

Modified:
    db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
    db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java
    db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java
    db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java
    db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
    db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java
    db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java
    db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
    db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java

Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
Mon Jan 30 18:17:29 2012
@@ -54,7 +54,8 @@ import org.apache.torque.util.UniqueList
  * @author <a href="mailto:tv@apache.org">Thomas Vandahl</a>
  * @version $Id: Criteria.java 1211142 2011-12-06 21:04:03Z tfischer $
  */
-public class Criteria implements Serializable, CriteriaInterface<Criteria>
+public class Criteria
+        implements Serializable, Cloneable, CriteriaInterface<Criteria>
 {
     /** Serial version. */
     private static final long serialVersionUID = -9001666575933085601L;
@@ -175,7 +176,7 @@ public class Criteria implements Seriali
     private int limit = -1;
 
     /** To start the results at a row other than the first one. */
-    private int offset = 0;
+    private long offset = 0;
 
     /**
      * Aliases for table names. The key of the map is the alias,
@@ -608,7 +609,7 @@ public class Criteria implements Seriali
      *        result.
      * @return A modified Criteria object.
      */
-    public Criteria setOffset(int offset)
+    public Criteria setOffset(long offset)
     {
         this.offset = offset;
         return this;
@@ -619,7 +620,7 @@ public class Criteria implements Seriali
      *
      * @return The value for offset.
      */
-    public int getOffset()
+    public long getOffset()
     {
         return offset;
     }
@@ -921,6 +922,21 @@ public class Criteria implements Seriali
         return hashCodeBuilder.toHashCode();
     }
 
+    /**
+     * Returns a cloned object.
+     */
+    public Object clone()
+    {
+        try
+        {
+            return super.clone();
+        }
+        catch (CloneNotSupportedException e) {
+            // should not happen as we implement Cloneable
+            throw new RuntimeException(e);
+        }
+    }
+
    /*
     * ------------------------------------------------------------------------
     *

Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java
Mon Jan 30 18:17:29 2012
@@ -140,7 +140,7 @@ public interface CriteriaInterface<T>
      *
      * @return An int with the value for offset.
      */
-    int getOffset();
+    long getOffset();
 
     /**
      * Get select columns.
@@ -186,5 +186,5 @@ public interface CriteriaInterface<T>
      *        result.
      * @return A modified Criteria object.
      */
-    public T setOffset(int offset);
+    public T setOffset(long offset);
 }

Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java
Mon Jan 30 18:17:29 2012
@@ -40,10 +40,12 @@ public final class MapHelper
 
     /**
      * Returns the table map for a table name.
+     * As aliases and asColumns are resolved, the returned table map need not
+     * contain the same table name as the column.
      *
      * @param column the column to get the table map for.
      * @param criteria A criteria containing the database name and perhaps
-     *        aliases for the table name, not null.
+     *        aliases for the column and table name, not null.
      * @param defaultTableMap a default table map which is used if the table
      *        name cannot be resolved, may be null.
      * @return the table map, or null if the table name cannot be resolved.
@@ -55,6 +57,16 @@ public final class MapHelper
     {
         TableMap result = null;
         String tableName = column.getTableName();
+        if (tableName == null)
+        {
+            // try asColumns
+            Column asColumn = (Column) criteria.getAsColumns().get(
+                    column.getSqlExpression());
+            if (asColumn != null)
+            {
+                tableName = asColumn.getTableName();
+            }
+        }
         if (tableName != null)
         {
             String databaseName = criteria.getDbName();
@@ -82,4 +94,58 @@ public final class MapHelper
         return result;
     }
 
+    /**
+     * Returns the column map for a column.
+     * As aliases and asColumns are resolved, the returned column map need not
+     * contain the same column name as the column.
+     *
+     * @param column the column to get the column map for.
+     * @param criteria A criteria containing the database name and perhaps
+     *        aliases for the column and table name, not null.
+     * @return the column map, or null if the column name cannot be resolved.
+     */
+    public static ColumnMap getColumnMap(
+            Column column,
+            CriteriaInterface criteria)
+    {
+        String tableName = column.getTableName();
+        {
+            // try asColumns
+            Column asColumn = (Column) criteria.getAsColumns().get(
+                    column.getSqlExpression());
+            if (asColumn != null)
+            {
+                column = asColumn;
+                tableName = asColumn.getTableName();
+            }
+        }
+
+        if (tableName == null)
+        {
+            return null;
+        }
+        String databaseName = criteria.getDbName();
+        DatabaseMap databaseMap = Torque.getDatabaseMap(databaseName);
+        TableMap tableMap = null;
+        if (databaseMap != null)
+        {
+            tableMap = databaseMap.getTable(tableName);
+        }
+        if (tableMap == null)
+        {
+            // try aliases
+            Map<String, String> aliases = criteria.getAliases();
+            if (aliases.get(tableName) != null)
+            {
+                tableName = aliases.get(tableName);
+                tableMap = databaseMap.getTable(tableName);
+            }
+        }
+        // try aliases
+        if (tableMap == null)
+        {
+            return null;
+        }
+        return tableMap.getColumn(column.getColumnName());
+    }
 }

Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java
Mon Jan 30 18:17:29 2012
@@ -495,12 +495,7 @@ public final class SqlBuilder
         for (OrderBy orderBy : orderByList)
         {
             Column column = orderBy.getColumn();
-            TableMap tableMap = MapHelper.getTableMap(column, crit, null);
-            ColumnMap columnMap = null;
-            if (tableMap != null)
-            {
-                columnMap = tableMap.getColumn(column.getColumnName());
-            }
+            ColumnMap columnMap = MapHelper.getColumnMap(column, crit);
             String sqlExpression = column.getSqlExpression();
 
             // Either we are not able to look up the column in the
@@ -524,13 +519,19 @@ public final class SqlBuilder
                 else
                 {
                     orderByClause.add(sqlExpression + ' ' + orderBy.getOrder());
-                    selectClause.add(sqlExpression);
+                    if (crit.getAsColumns().get(sqlExpression) == null)
+                    {
+                        selectClause.add(sqlExpression);
+                    }
                 }
             }
             else
             {
                 orderByClause.add(sqlExpression + ' ' + orderBy.getOrder());
-                selectClause.add(sqlExpression);
+                if (crit.getAsColumns().get(sqlExpression) == null)
+                {
+                    selectClause.add(sqlExpression);
+                }
             }
             addTableToFromClause(
                    column,
@@ -624,7 +625,7 @@ public final class SqlBuilder
             throws TorqueException
     {
         int limit = crit.getLimit();
-        int offset = crit.getOffset();
+        long offset = crit.getOffset();
 
         if (offset > 0 || limit >= 0)
         {
@@ -1281,10 +1282,16 @@ public final class SqlBuilder
 
         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)
                 {
@@ -1301,6 +1308,11 @@ public final class SqlBuilder
         {
             for (Object arrayValue : (Object[]) whereClausePart.getRValue())
             {
+                if (arrayValue == null)
+                {
+                    nullContained = true;
+                    continue;
+                }
                 result.getPreparedStatementReplacements().add(arrayValue);
                 if (ignoreCase && arrayValue instanceof String)
                 {
@@ -1322,6 +1334,11 @@ public final class SqlBuilder
                     + " Iterable or Array");
         }
 
+        if (nullContained)
+        {
+            result.getSql().append('(');
+        }
+
         if (ignoreCaseApplied)
         {
             result.getSql().append(db.ignoreCase(whereClausePart.getLValue()));
@@ -1335,6 +1352,21 @@ public final class SqlBuilder
                 .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;
     }
 }

Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
Mon Jan 30 18:17:29 2012
@@ -1184,7 +1184,7 @@ public class BasePeerImpl implements Ser
             log.trace("query took " + (queryEndTime - startTime)
                     + " milliseconds");
 
-            int offset;
+            long offset;
             Database database = Torque.getDatabase(criteria.getDbName());
             if (database.getAdapter().supportsNativeOffset())
             {
@@ -1195,7 +1195,7 @@ public class BasePeerImpl implements Ser
                 offset = criteria.getOffset();
             }
 
-            int limit;
+            long limit;
             if (database.getAdapter().supportsNativeLimit())
             {
                 limit = -1; //database takes care of offset
@@ -1336,7 +1336,7 @@ public class BasePeerImpl implements Ser
             log.trace("query took " + (queryEndTime - startTime)
                     + " milliseconds");
 
-            int offset;
+            long offset;
             Database database = Torque.getDatabase(criteria.getDbName());
             if (database.getAdapter().supportsNativeOffset())
             {
@@ -1347,7 +1347,7 @@ public class BasePeerImpl implements Ser
                 offset = criteria.getOffset();
             }
 
-            int limit;
+            long limit;
             if (database.getAdapter().supportsNativeLimit())
             {
                 limit = -1; //database takes care of offset

Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java
Mon Jan 30 18:17:29 2012
@@ -191,7 +191,7 @@ public class Criteria implements Seriali
     private int limit = -1;
 
     /** To start the results at a row other than the first one. */
-    private int offset = 0;
+    private long offset = 0;
 
     /**
      * Aliases for table names. The key of the map is the alias,
@@ -1659,7 +1659,7 @@ public class Criteria implements Seriali
      * @param offset An int with the value for offset.
      * @return A modified Criteria object.
      */
-    public Criteria setOffset(int offset)
+    public Criteria setOffset(long offset)
     {
         this.offset = offset;
         return this;
@@ -1670,7 +1670,7 @@ public class Criteria implements Seriali
      *
      * @return An int with the value for offset.
      */
-    public int getOffset()
+    public long getOffset()
     {
         return offset;
     }
@@ -1988,7 +1988,7 @@ public class Criteria implements Seriali
     public int hashCode()
     {
         int result = 16;
-        result = 37 * result + offset;
+        result = 37 * result + new Long(offset).hashCode();
         result = 37 * result + limit;
         result = 37 * result + (ignoreCase ? 0 : 1);
         result = 37 * result + (singleRecord ? 0 : 1);

Modified: db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java
Mon Jan 30 18:17:29 2012
@@ -91,9 +91,9 @@ public class CriteriaTest extends BaseTe
     }
 
     /**
-     * Test basic adding of strings.
+     * Test basic where condition on a string.
      */
-    public void testAddString()
+    public void testWhereString()
     {
         final Column column = new ColumnImpl("myTable", "myColumn");
         final String value = "myValue";
@@ -111,6 +111,66 @@ public class CriteriaTest extends BaseTe
     }
 
     /**
+     * Test where condition with several ANDs compairing against Strings.
+     */
+    public void testAndString()
+    {
+        final Column column1 = new ColumnImpl("myTable", "myColumn1");
+        final Column column2 = new ColumnImpl("myTable", "myColumn2");
+        final String value1a = "1a";
+        final String value1b = "1b";
+        final String value2a = "2a";
+        final String value2b = "2b";
+
+        // Add the string
+        c.where(column1, value1a)
+            .and(column1, value1b)
+            .and(column2, value2a)
+            .and(column2, value2b);
+
+        // Verify that the Criterion is a composite
+        assertTrue(c.getTopLevelCriterion().isComposite());
+
+        // Verify that what we get out is what we put in
+        assertEquals(
+                column1,
+                c.getTopLevelCriterion().getParts().get(0).getColumn());
+        assertEquals(
+                value1a,
+                c.getTopLevelCriterion().getParts().get(0).getValue());
+        assertEquals(
+                Criteria.EQUAL,
+                c.getTopLevelCriterion().getParts().get(0).getComparison());
+        assertEquals(
+                column1,
+                c.getTopLevelCriterion().getParts().get(1).getColumn());
+        assertEquals(
+                value1b,
+                c.getTopLevelCriterion().getParts().get(1).getValue());
+        assertEquals(
+                Criteria.EQUAL,
+                c.getTopLevelCriterion().getParts().get(1).getComparison());
+        assertEquals(
+                column2,
+                c.getTopLevelCriterion().getParts().get(2).getColumn());
+        assertEquals(
+                value2a,
+                c.getTopLevelCriterion().getParts().get(2).getValue());
+        assertEquals(
+                Criteria.EQUAL,
+                c.getTopLevelCriterion().getParts().get(2).getComparison());
+        assertEquals(
+                column2,
+                c.getTopLevelCriterion().getParts().get(3).getColumn());
+        assertEquals(
+                value2b,
+                c.getTopLevelCriterion().getParts().get(3).getValue());
+        assertEquals(
+                Criteria.EQUAL,
+                c.getTopLevelCriterion().getParts().get(3).getComparison());
+    }
+
+    /**
      * Test that nesting Criterions works for equals comparison.
      */
     public void testNestedCriterionComparisonEqual() throws TorqueException
@@ -635,6 +695,99 @@ public class CriteriaTest extends BaseTe
     }
 
     /**
+     * Test that cloning works.
+     */
+    public void testClone()
+    {
+        c.setOffset(10);
+        c.setLimit(11);
+        c.setIgnoreCase(true);
+        c.setSingleRecord(true);
+        c.setDbName("myDB");
+        c.setAll();
+        c.setDistinct();
+        c.addSelectColumn(new ColumnImpl("Author", "NAME"));
+        c.addSelectColumn(new ColumnImpl("Author", "AUTHOR_ID"));
+        c.addDescendingOrderByColumn(new ColumnImpl("Author", "NAME"));
+        c.addAscendingOrderByColumn(new ColumnImpl("Author", "AUTHOR_ID"));
+        c.addAlias("Writer", "Author");
+        c.addAsColumn("AUTHOR_NAME", new ColumnImpl("Author", "NAME"));
+        c.addJoin(new ColumnImpl("Author", "AUTHOR_ID"),
+                new ColumnImpl("Book", "AUTHOR_ID"),
+                Criteria.INNER_JOIN);
+        c.where(new ColumnImpl("Author", "NAME"), "author%", Criteria.LIKE);
+
+        // Clone the object
+        Criteria cClone = (Criteria) c.clone();
+
+        // Check the clone
+        assertEquals(c.getTopLevelCriterion(), cClone.getTopLevelCriterion());
+        assertEquals(10, cClone.getOffset());
+        assertEquals(c.getOffset(), cClone.getOffset());
+        assertEquals(11, cClone.getLimit());
+        assertEquals(c.getLimit(), cClone.getLimit());
+        assertEquals(true, cClone.isIgnoreCase());
+        assertEquals(c.isIgnoreCase(), cClone.isIgnoreCase());
+        assertEquals(true, cClone.isSingleRecord());
+        assertEquals(c.isSingleRecord(), cClone.isSingleRecord());
+        assertEquals("myDB", cClone.getDbName());
+        assertEquals(c.getDbName(), cClone.getDbName());
+        List<String> selectModifiersClone = cClone.getSelectModifiers();
+        assertTrue(selectModifiersClone.contains(
+                Criteria.ALL.toString()));
+        assertTrue(selectModifiersClone.contains(
+                Criteria.DISTINCT.toString()));
+        assertEquals(c.getSelectModifiers(), cClone.getSelectModifiers());
+        List<Column> selectColumnsClone = cClone.getSelectColumns();
+        assertTrue(selectColumnsClone.contains(
+                new ColumnImpl("Author", "NAME")));
+        assertTrue(selectColumnsClone.contains(
+                new ColumnImpl("Author", "AUTHOR_ID")));
+        assertEquals(c.getSelectColumns(), cClone.getSelectColumns());
+        List<OrderBy> orderByColumnsClone = cClone.getOrderByColumns();
+        assertTrue(orderByColumnsClone.contains(new OrderBy(
+                new ColumnImpl("Author.NAME"),
+                SqlEnum.DESC,
+                false)));
+        assertTrue(orderByColumnsClone.contains(new OrderBy(
+                new ColumnImpl("Author.AUTHOR_ID"),
+                SqlEnum.ASC,
+                false)));
+        assertEquals(c.getOrderByColumns(), cClone.getOrderByColumns());
+        Map<String, String> aliasesClone = cClone.getAliases();
+        assertTrue(aliasesClone.containsKey("Writer"));
+        assertEquals("Author", aliasesClone.get("Writer"));
+        assertEquals(c.getAliases(), cClone.getAliases());
+        Map<String, Column> asColumnsClone = cClone.getAsColumns();
+        assertTrue(asColumnsClone.containsKey("AUTHOR_NAME"));
+        assertEquals(new ColumnImpl("Author", "NAME"),
+                asColumnsClone.get("AUTHOR_NAME"));
+        assertEquals(c.getAsColumns(), cClone.getAsColumns());
+
+        // Check Joins
+        List<Join> joinsClone = cClone.getJoins();
+        Join joinClone = joinsClone.get(0);
+        assertEquals(new ColumnImpl("Author", "AUTHOR_ID"),
+                joinClone.getLeftColumn());
+        assertEquals(new ColumnImpl("Book", "AUTHOR_ID"),
+                joinClone.getRightColumn());
+        assertEquals(Criteria.INNER_JOIN, joinClone.getJoinType());
+        assertEquals(c.getJoins(), cClone.getJoins());
+
+        // Some Criterion checks
+        Criterion cnClone = cClone.getTopLevelCriterion();
+        assertEquals("author%", cnClone.getValue());
+        assertEquals(Criteria.LIKE, cnClone.getComparison());
+        assertEquals(c.getTopLevelCriterion().isIgnoreCase(), cnClone.isIgnoreCase());
+
+        // Confirm that equals() checks all of the above.
+        assertEquals(c, cClone);
+
+        // Check hashCode() too.
+        assertEquals(c.hashCode(), cClone.hashCode());
+    }
+
+    /**
      * Test that {@link Criteria#equals(Object)} works correctly for a simple
      * Criteria object.
      * @throws TorqueException

Modified: db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
Mon Jan 30 18:17:29 2012
@@ -43,6 +43,74 @@ import org.apache.torque.om.NumberKey;
 public class SqlBuilderTest extends BaseTestCase
 {
     /**
+     * Test where condition with several ANDs compairing against Strings.
+     */
+    public void testAndString() throws Exception
+    {
+        final Column column1 = new ColumnImpl("myTable", "myColumn1");
+        final Column column2 = new ColumnImpl("myTable", "myColumn2");
+        final String value1a = "1a";
+        final String value1b = "1b";
+        final String value2a = "2a";
+        final String value2b = "2b";
+        Criteria c = new Criteria().where(column1, value1a)
+            .and(column1, value1b)
+            .and(column2, value2a)
+            .and(column2, value2b);
+
+        Query query = SqlBuilder.buildQuery(c);
+
+        assertEquals(
+                "SELECT  FROM myTable "
+                   + "WHERE (myTable.myColumn1=? "
+                   + "AND myTable.myColumn1=? "
+                   + "AND myTable.myColumn2=? "
+                   + "AND myTable.myColumn2=?)",
+                query.toString());
+        List<String> expectedReplacements = new ArrayList<String>();
+        expectedReplacements.add(value1a);
+        expectedReplacements.add(value1b);
+        expectedReplacements.add(value2a);
+        expectedReplacements.add(value2b);
+        assertEquals(
+                expectedReplacements,
+                query.getPreparedStatementReplacements());
+    }
+
+    /**
+     * Test where condition with several ANDs compairing against Strings.
+     */
+    public void testAndCriterions() throws Exception
+    {
+        final Column column1 = new ColumnImpl("myTable", "myColumn1");
+        final Column column2 = new ColumnImpl("myTable", "myColumn2");
+        final String value1a = "1a";
+        final String value1b = "1b";
+        final String value2a = "2a";
+        Criterion criterion1 = new Criterion(column1, value1a);
+        Criterion criterion2 = new Criterion(column1, value1b);
+        Criterion criterion3 = new Criterion(column2, value2a);
+        criterion1.and(criterion2).and(criterion3);
+        Criteria c = new Criteria().where(criterion1);
+
+        Query query = SqlBuilder.buildQuery(c);
+
+        assertEquals(
+                "SELECT  FROM myTable "
+                   + "WHERE (myTable.myColumn1=? "
+                   + "AND myTable.myColumn1=? "
+                   + "AND myTable.myColumn2=?)",
+                query.toString());
+        List<String> expectedReplacements = new ArrayList<String>();
+        expectedReplacements.add(value1a);
+        expectedReplacements.add(value1b);
+        expectedReplacements.add(value2a);
+        assertEquals(
+                expectedReplacements,
+                query.getPreparedStatementReplacements());
+    }
+
+    /**
      * Test that unknown columns are treated case-insensitive if ignoreCase
      * is set.
      */
@@ -149,6 +217,60 @@ public class SqlBuilderTest extends Base
                 query.toString());
     }
 
+    public void testOrderByWithFunction() throws Exception
+    {
+        Criteria criteria = new Criteria();
+        criteria.addAscendingOrderByColumn(
+                new ColumnImpl("count(table.column1)"));
+        Query query = SqlBuilder.buildQuery(criteria);
+        assertEquals(
+                "SELECT count(table.column1) FROM table "
+                    + "ORDER BY count(table.column1) ASC",
+                query.toString());
+    }
+
+    public void testOrderByWithAsColumn() throws Exception
+    {
+        Criteria criteria = new Criteria();
+        criteria.addAsColumn("asColumn", stringColumnMap);
+        criteria.addAscendingOrderByColumn("asColumn");
+        Query query = SqlBuilder.buildQuery(criteria);
+        assertEquals(
+                "SELECT TABLE.COLUMN1 AS asColumn "
+                    + "FROM TABLE "
+                    + "ORDER BY asColumn ASC",
+                query.toString());
+    }
+
+    public void testOrderByWithAsColumnIgnoreCase() throws Exception
+    {
+        Criteria criteria = new Criteria();
+        criteria.addAsColumn("asColumn", stringColumnMap);
+        criteria.addAscendingOrderByColumn("asColumn", true);
+        Query query = SqlBuilder.buildQuery(criteria);
+        assertEquals(
+                "SELECT TABLE.COLUMN1 AS asColumn, UPPER(asColumn) "
+                    + "FROM TABLE "
+                    + "ORDER BY UPPER(asColumn) ASC",
+                query.toString());
+    }
+
+    public void testOrderByWithAsColumnAndAliasIgnoreCase() throws Exception
+    {
+        Criteria criteria = new Criteria();
+        criteria.addAlias("alias", stringColumnMap.getTableName());
+        criteria.addAsColumn(
+                "asColumn",
+                new ColumnImpl("alias", stringColumnMap.getColumnName()));
+        criteria.addAscendingOrderByColumn("asColumn", true);
+        Query query = SqlBuilder.buildQuery(criteria);
+        assertEquals(
+                "SELECT alias.COLUMN1 AS asColumn, UPPER(asColumn) "
+                    + "FROM TABLE alias "
+                    + "ORDER BY UPPER(asColumn) ASC",
+                query.toString());
+    }
+
     public void testOrderByAscendingIgnoreCaseString() throws Exception
     {
         Criteria criteria = new Criteria();
@@ -1152,39 +1274,38 @@ public class SqlBuilderTest extends Base
     {
         Criteria criteria = new Criteria();
         criteria.addSelectColumn("table.column1");
-        String[] inValue = new String[] {"a", "b", null};
+        String[] inValue = new String[] {"a", "b", null, null};
         criteria.whereIn("table.column2", inValue.clone());
 
         Query query = SqlBuilder.buildQuery(criteria);
 
         assertEquals("SELECT table.column1 FROM table "
-                + "WHERE table.column2 IN (?,?,?)",
+                + "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
             query.toString());
         List<Object> replacements = query.getPreparedStatementReplacements();
-        assertEquals(inValue.length, replacements.size());
+        assertEquals(2, replacements.size());
         assertEquals(inValue[0], replacements.get(0));
         assertEquals(inValue[1], replacements.get(1));
-        assertEquals(inValue[2], replacements.get(2));
     }
 
     public void testCriterionInArrayIgnoreCase() throws Exception
     {
         Criteria criteria = new Criteria();
         criteria.addSelectColumn("table.column1");
-        String[] inValue = new String[] {"a", "b", null};
+        String[] inValue = new String[] {"a", "b", null, null};
         criteria.whereIn("table.column2", inValue.clone());
         criteria.setIgnoreCase(true);
 
         Query query = SqlBuilder.buildQuery(criteria);
 
         assertEquals("SELECT table.column1 FROM table "
-                + "WHERE UPPER(table.column2) IN (UPPER(?),UPPER(?),?)",
+                + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?))"
+                + " OR table.column2 IS NULL)",
             query.toString());
         List<Object> replacements = query.getPreparedStatementReplacements();
-        assertEquals(inValue.length, replacements.size());
+        assertEquals(2, replacements.size());
         assertEquals(inValue[0], replacements.get(0));
         assertEquals(inValue[1], replacements.get(1));
-        assertEquals(inValue[2], replacements.get(2));
     }
 
     public void testCriterionInList() throws Exception
@@ -1193,20 +1314,20 @@ public class SqlBuilderTest extends Base
         criteria.addSelectColumn("table.column1");
         List<Integer> inList = new ArrayList<Integer>();
         inList.add(1);
+        inList.add(null);
         inList.add(2);
         inList.add(null);
         criteria.whereIn("table.column2", inList);
 
         Query query = SqlBuilder.buildQuery(criteria);
         assertEquals("SELECT table.column1 FROM table "
-                + "WHERE table.column2 IN (?,?,?)",
+                + "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
             query.toString());
         assertEquals(
-                inList.size(),
+                2,
                 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
@@ -1217,18 +1338,43 @@ public class SqlBuilderTest extends Base
         inList.add("a");
         inList.add("b");
         inList.add(null);
+        inList.add(null);
         criteria.whereIn("table.column2", inList);
         criteria.setIgnoreCase(true);
 
         Query query = SqlBuilder.buildQuery(criteria);
         assertEquals("SELECT table.column1 FROM table "
-                + "WHERE UPPER(table.column2) IN (UPPER(?),UPPER(?),?)",
+                + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?))"
+                + " OR table.column2 IS NULL)",
             query.toString());
         List<Object> replacements = query.getPreparedStatementReplacements();
-        assertEquals(inList.size(), replacements.size());
+        assertEquals(2, replacements.size());
         assertEquals("a", replacements.get(0));
         assertEquals("b", replacements.get(1));
-        assertEquals(null, replacements.get(2));
+    }
+
+    public void testCriterionNotInList() throws Exception
+    {
+        Criteria criteria = new Criteria();
+        criteria.addSelectColumn("table.column1");
+        List<Integer> inList = new ArrayList<Integer>();
+        inList.add(1);
+        inList.add(null);
+        inList.add(2);
+        inList.add(null);
+        criteria.where(
+                "table.column2",
+                inList,
+                Criteria.NOT_IN);
+
+        Query query = SqlBuilder.buildQuery(criteria);
+        assertEquals("SELECT table.column1 FROM table "
+                + "WHERE (table.column2 NOT IN (?,?) "
+                + "AND table.column2 IS NOT NULL)",
+            query.toString());
+        assertEquals(2, query.getPreparedStatementReplacements().size());
+        assertEquals(1, query.getPreparedStatementReplacements().get(0));
+        assertEquals(2, query.getPreparedStatementReplacements().get(1));
     }
 
     public void testCriterionInLargeArray() throws TorqueException

Modified: 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=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java
(original)
+++ db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java
Mon Jan 30 18:17:29 2012
@@ -1109,17 +1109,16 @@ public class SqlBuilderTestOldCriteria e
         criteria.addSelectColumn("table.column1");
         criteria.add(
                 "table.column2",
-                new String[] {"a", "b", null},
+                new String[] {"a", "b", null, null},
                 Criteria.IN);
 
         Query query = SqlBuilder.buildQuery(criteria);
         assertEquals("SELECT table.column1 FROM table "
-                + "WHERE table.column2 IN (?,?,?)",
+                + "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
             query.toString());
-        assertEquals(3, query.getPreparedStatementReplacements().size());
+        assertEquals(2, 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
@@ -1128,19 +1127,19 @@ public class SqlBuilderTestOldCriteria e
         criteria.addSelectColumn("table.column1");
         criteria.add(
                 "table.column2",
-                new String[] {"a", "b", null},
+                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(?),?)",
+                + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?)) "
+                + "OR table.column2 IS NULL)",
             query.toString());
         List<Object> replacements = query.getPreparedStatementReplacements();
-        assertEquals(3, replacements.size());
+        assertEquals(2, replacements.size());
         assertEquals("a", replacements.get(0));
         assertEquals("b", replacements.get(1));
-        assertEquals(null, replacements.get(2));
     }
 
     public void testCriterionInList() throws Exception
@@ -1151,6 +1150,7 @@ public class SqlBuilderTestOldCriteria e
         inList.add(1);
         inList.add(2);
         inList.add(null);
+        inList.add(null);
         criteria.add(
                 "table.column2",
                 inList,
@@ -1158,12 +1158,12 @@ public class SqlBuilderTestOldCriteria e
 
         Query query = SqlBuilder.buildQuery(criteria);
         assertEquals("SELECT table.column1 FROM table "
-                + "WHERE table.column2 IN (?,?,?)",
+                + "WHERE (table.column2 IN (?,?) "
+                + "OR table.column2 IS NULL)",
             query.toString());
-        assertEquals(3, query.getPreparedStatementReplacements().size());
+        assertEquals(2, 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
@@ -1172,6 +1172,7 @@ public class SqlBuilderTestOldCriteria e
         criteria.addSelectColumn("table.column1");
         List<String> inList = new ArrayList<String>();
         inList.add("a");
+        inList.add(null);
         inList.add("b");
         inList.add(null);
         criteria.add(
@@ -1182,13 +1183,13 @@ public class SqlBuilderTestOldCriteria e
 
         Query query = SqlBuilder.buildQuery(criteria);
         assertEquals("SELECT table.column1 FROM table "
-                + "WHERE UPPER(table.column2) IN (UPPER(?),UPPER(?),?)",
+                + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?)) "
+                + "OR table.column2 IS NULL)",
             query.toString());
         List<Object> replacements = query.getPreparedStatementReplacements();
-        assertEquals(3, replacements.size());
+        assertEquals(2, replacements.size());
         assertEquals("a", replacements.get(0));
         assertEquals("b", replacements.get(1));
-        assertEquals(null, replacements.get(2));
     }
 
     public void testCriterionInLargeArray() throws TorqueException



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message