db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tfisc...@apache.org
Subject svn commit: r470516 - in /db/torque: runtime/trunk/src/java/org/apache/torque/adapter/ runtime/trunk/src/java/org/apache/torque/util/ runtime/trunk/src/test/org/apache/torque/util/ site/trunk/xdocs/ test/trunk/test-project/src/java/org/apache/torque/
Date Thu, 02 Nov 2006 20:51:16 GMT
Author: tfischer
Date: Thu Nov  2 12:51:15 2006
New Revision: 470516

URL: http://svn.apache.org/viewvc?view=rev&rev=470516
Log:
Fixed handling of escape characters in LIKE Clauses.
Fixes TORQUE-57.
Thanks to Thandavarayan Parthasarathy for analyzing the error.

Modified:
    db/torque/runtime/trunk/src/java/org/apache/torque/adapter/AbstractDBAdapter.java
    db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DB.java
    db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBOracle.java
    db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBPostgres.java
    db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlEnum.java
    db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlExpression.java
    db/torque/runtime/trunk/src/test/org/apache/torque/util/SqlExpressionTest.java
    db/torque/site/trunk/xdocs/changes.xml
    db/torque/test/trunk/test-project/src/java/org/apache/torque/DataTest.java

Modified: db/torque/runtime/trunk/src/java/org/apache/torque/adapter/AbstractDBAdapter.java
URL: http://svn.apache.org/viewvc/db/torque/runtime/trunk/src/java/org/apache/torque/adapter/AbstractDBAdapter.java?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/runtime/trunk/src/java/org/apache/torque/adapter/AbstractDBAdapter.java (original)
+++ db/torque/runtime/trunk/src/java/org/apache/torque/adapter/AbstractDBAdapter.java Thu
Nov  2 12:51:15 2006
@@ -25,7 +25,7 @@
 import org.apache.torque.util.Query;
 
 /**
- * This class iis the abstract base for any database adapter
+ * This class is the abstract base for any database adapter
  * Support for new databases is added by subclassing this
  * class and implementing its abstract methods, and by
  * registering the new database adapter and its corresponding
@@ -245,5 +245,34 @@
     public String getBooleanString(Boolean b)
     {
         return (Boolean.TRUE.equals(b) ? "1" : "0");
+    }
+    
+    /**
+     * Whether ILIKE should be used for case insensitive like clauses.
+     * 
+     * As most databases do not use ILIKE, this implementation returns false.
+     * This behaviour may be overwritten in subclasses.
+     *  
+     * @return true if ilike should be used for case insensitive likes,
+     *         false if ignoreCase should be applied to the compared strings.
+     */
+    public boolean useIlike()
+    {
+        return false;
+    }
+    
+    /**
+     * Whether an escape clause in like should be used.
+     * Example : select * from AUTHOR where AUTHOR.NAME like '\_%' ESCAPE '\';
+     * 
+     * As most databases do not need the escape clause, this implementation
+     * always returns <code>false</code>. This behaviour can be overwritten
+     * in subclasses. 
+     * 
+     * @return whether the escape clause should be appended or not. 
+     */
+    public boolean useEscapeClauseForLike()
+    {
+        return false;
     }
 }

Modified: db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DB.java
URL: http://svn.apache.org/viewvc/db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DB.java?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DB.java (original)
+++ db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DB.java Thu Nov  2 12:51:15
2006
@@ -156,9 +156,12 @@
             throws SQLException;
 
     /**
-     * This method is used to ignore case.
+     * Modifies a SQL snippet such that its case is ignored by the database.
+     * The SQL snippet can be a column name (like AURHOR.NAME), an
+     * quoted explicit sql string (like 'abc') or any other sql value (like a
+     * number etc.).
      *
-     * @param in The string whose case to ignore.
+     * @param in The SQL snippet whose case to ignore.
      * @return The string in a case that can be ignored.
      */
     String ignoreCase(String in);
@@ -207,11 +210,12 @@
         throws TorqueException;
 
     /**
-    * This method is for the SqlExpression.quoteAndEscape rules.  The rule is,
-    * any string in a SqlExpression with a BACKSLASH will either be changed to
-    * "\\" or left as "\".  SapDB does not need the escape character.
+    * Whether backslashes (\) should be escaped in explicit SQL strings.
+    * If true is returned, a BACKSLASH will be changed to "\\". If false 
+    * is returned, a BACKSLASH will be left as "\".
     *
-    * @return true if the database needs to escape text in SqlExpressions.
+    * @return true if the database needs to escape backslashes
+    *         in SqlExpressions.
     */
 
     boolean escapeText();
@@ -241,4 +245,20 @@
      * @return The proper date formatted String.
      */
     String getBooleanString(Boolean b);
+
+    /**
+     * Whether ILIKE should be used for case insensitive like clauses.
+     *  
+     * @return true if ilike should be used for case insensitive likes,
+     *         false if ignoreCase should be applied to the compared strings.
+     */
+    boolean useIlike();
+    
+    /**
+     * Whether an escape clause in like should be used.
+     * Example : select * from AUTHOR where AUTHOR.NAME like '\_%' ESCAPE '\';
+     * 
+     * @return whether the escape clause should be appended or not. 
+     */
+    boolean useEscapeClauseForLike();
 }

Modified: db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBOracle.java
URL: http://svn.apache.org/viewvc/db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBOracle.java?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBOracle.java (original)
+++ db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBOracle.java Thu Nov  2 12:51:15
2006
@@ -329,4 +329,18 @@
     {
         return false;
     }
+
+    /**
+     * Whether an escape clause in like should be used.
+     * Example : select * from AUTHOR where AUTHOR.NAME like '\_%' ESCAPE '\';
+     * 
+     * Oracle needs this, so this implementation always returns
+     * <code>true</code>.
+     * 
+     * @return whether the escape clause should be appended or not. 
+     */
+    public boolean useEscapeClauseForLike()
+    {
+        return true;
+    }
 }

Modified: db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBPostgres.java
URL: http://svn.apache.org/viewvc/db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBPostgres.java?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBPostgres.java (original)
+++ db/torque/runtime/trunk/src/java/org/apache/torque/adapter/DBPostgres.java Thu Nov  2
12:51:15 2006
@@ -208,4 +208,17 @@
         dateBuf.append(delim);
         return dateBuf.toString();
     }
+    
+    /**
+     * Whether ILIKE should be used for case insensitive like clauses.
+     * 
+     * As postgres uses ILIKE, this mimplementation returns true.
+     *  
+     * @return true if ilike should be used for case insensitive likes,
+     *         false if ignoreCase should be applied to the compared strings.
+     */
+    public boolean useIlike()
+    {
+        return true;
+    }
 }

Modified: db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlEnum.java
URL: http://svn.apache.org/viewvc/db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlEnum.java?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlEnum.java (original)
+++ db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlEnum.java Thu Nov  2 12:51:15
2006
@@ -101,6 +101,8 @@
         new SqlEnum(" ON ");
     public static final SqlEnum AS =
         new SqlEnum(" AS ");
+    public static final SqlEnum ESCAPE =
+        new SqlEnum(" ESCAPE ");
 
     /**
      * returns whether o is the same SqlEnum as this object.

Modified: db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlExpression.java
URL: http://svn.apache.org/viewvc/db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlExpression.java?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlExpression.java (original)
+++ db/torque/runtime/trunk/src/java/org/apache/torque/util/SqlExpression.java Thu Nov  2
12:51:15 2006
@@ -380,39 +380,17 @@
                            DB db,
                            StringBuffer whereClause)
     {
-        // If selection is case insensitive use ILIKE for PostgreSQL or SQL
-        // UPPER() function on column name for other databases.
-        if (ignoreCase)
-        {
-            if (db instanceof DBPostgres)
-            {
-                if (comparison.equals(Criteria.LIKE))
-                {
-                    comparison = Criteria.ILIKE;
-                }
-                else if (comparison.equals(Criteria.NOT_LIKE))
-                {
-                    comparison = Criteria.NOT_ILIKE;
-                }
-            }
-            else
-            {
-                columnName = db.ignoreCase(columnName);
-            }
-        }
-        whereClause.append(columnName);
-
         // If selection criteria contains wildcards use LIKE otherwise
         // use = (equals).  Wildcards can be escaped by prepending
-        // them with \ (backslash).
-        String equalsOrLike = " = ";
-        if (comparison.equals(Criteria.NOT_LIKE))
-        {
-            equalsOrLike = " " + Criteria.NOT_EQUAL + " ";
-        }
-
+        // them with \ (backslash). However, if we switch from
+        // like to equals, we need to remove the escape characters.
+        // from the wildcards.
+        // So we need two passes: The first replaces * and ? by % and _,
+        // and checks whether we switch to equals, 
+        // the second removes escapes if we have switched to equals.
         int position = 0;
         StringBuffer sb = new StringBuffer();
+        boolean replaceWithEquals = true;
         while (position < criteria.length())
         {
             char checkWildcard = criteria.charAt(position);
@@ -420,28 +398,38 @@
             switch (checkWildcard)
             {
             case BACKSLASH:
-                // Determine whether to skip over next character.
-                switch (criteria.charAt(position + 1))
+                // if text is escaped, all backslashes are already escaped,
+                // so the next character after the backslash is the doubled
+                // backslash from escaping.
+                int charsToProceed = db.escapeText() ? 2 : 1;
+                if (position + charsToProceed >= criteria.length())
+                {
+                    charsToProceed = criteria.length() - position - 1;
+                }
+                else if (criteria.charAt(position + charsToProceed) == BACKSLASH
+                        && db.escapeText())
                 {
-                case '%':
-                case '_':
-                case '*':
-                case '?':
-                case BACKSLASH:
-                    position++;
-                    break;
+                    // the escaped backslash is also escaped,
+                    // so we need to proceed another character
+                    charsToProceed += 1;
                 }
+                sb.append(criteria.substring(
+                        position, 
+                        position + charsToProceed));
+                position += charsToProceed;
+                // code below copies escaped character into sb
+                checkWildcard = criteria.charAt(position);
                 break;
             case '%':
             case '_':
-                equalsOrLike = comparison.toString();
+                replaceWithEquals = false;
                 break;
             case '*':
-                equalsOrLike = comparison.toString();
+                replaceWithEquals = false;
                 checkWildcard = '%';
                 break;
             case '?':
-                equalsOrLike = comparison.toString();
+                replaceWithEquals = false;
                 checkWildcard = '_';
                 break;
             }
@@ -449,16 +437,98 @@
             sb.append(checkWildcard);
             position++;
         }
-        whereClause.append(equalsOrLike);
+        criteria = sb.toString();
+        
+        if (ignoreCase)
+        {
+            if (db.useIlike() && !replaceWithEquals)
+            {
+                if (SqlEnum.LIKE.equals(comparison))
+                {
+                    comparison = SqlEnum.ILIKE;
+                }
+                else if (SqlEnum.NOT_LIKE.equals(comparison))
+                {
+                    comparison = SqlEnum.NOT_ILIKE;
+                }
+            }
+            else
+            {
+                // no native case insensitive like is offered by the DB,
+                // or the LIKE was replaced with equals.
+                // need to ignore case manually.
+                columnName = db.ignoreCase(columnName);
+            }
+        }
+        whereClause.append(columnName);
+
+        if (replaceWithEquals)
+        {
+            if (comparison.equals(Criteria.NOT_LIKE) 
+                    || comparison.equals(Criteria.NOT_ILIKE))
+            {
+                whereClause.append(" ").append(Criteria.NOT_EQUAL).append(" ");
+            }
+            else
+            {
+                whereClause.append(" ").append(Criteria.EQUAL).append(" ");
+            }
+            
+            // remove escape backslashes from String 
+            position = 0;
+            sb = new StringBuffer();
+            while (position < criteria.length())
+            {
+                char checkWildcard = criteria.charAt(position);
+
+                if (checkWildcard == BACKSLASH)
+                {
+                    // if text is escaped, all backslashes are already escaped,
+                    // so the next character after the backslash is the doubled
+                    // backslash from escaping.
+                    int charsToSkip = db.escapeText() ? 2 : 1;
+                    if (position + charsToSkip >= criteria.length())
+                    {
+                        charsToSkip = criteria.length() - position - 1;
+                    }
+                    else if (criteria.charAt(position + charsToSkip) 
+                                == BACKSLASH
+                            && db.escapeText())
+                    {
+                        // the escaped backslash is also escaped,
+                        // so we need to skip another character
+                        // but add the escaped backslash to sb
+                        // so that the escaping remains.
+                        sb.append(BACKSLASH);
+                        charsToSkip += 1;
+                    }
+                    position += charsToSkip;
+                    // code below copies escaped character into sb
+                    checkWildcard = criteria.charAt(position);
+                }
+                sb.append(checkWildcard);
+                position++;
+            }
+            criteria = sb.toString();
+       }
+        else
+        {
+            whereClause.append(comparison);
+        }
 
         // If selection is case insensitive use SQL UPPER() function
         // on criteria.
-        String clauseItem = sb.toString();
-        if (ignoreCase && !(db instanceof DBPostgres))
+        if (ignoreCase && (!(db.useIlike()) || replaceWithEquals))
+        {
+            criteria = db.ignoreCase(criteria);
+        }
+        whereClause.append(criteria);
+        
+        if (!replaceWithEquals && db.useEscapeClauseForLike())
         {
-            clauseItem = db.ignoreCase(clauseItem);
+            whereClause.append(SqlEnum.ESCAPE)
+                       .append("'\\'");
         }
-        whereClause.append(clauseItem);
     }
 
     /**

Modified: db/torque/runtime/trunk/src/test/org/apache/torque/util/SqlExpressionTest.java
URL: http://svn.apache.org/viewvc/db/torque/runtime/trunk/src/test/org/apache/torque/util/SqlExpressionTest.java?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/runtime/trunk/src/test/org/apache/torque/util/SqlExpressionTest.java (original)
+++ db/torque/runtime/trunk/src/test/org/apache/torque/util/SqlExpressionTest.java Thu Nov
 2 12:51:15 2006
@@ -98,7 +98,7 @@
             // jdk 1.3
             assertEquals(result, "COL IN ('43','44','42')");
         }
-        }
+    }
 
     public void testLargeBuildInStringObjectSqlEnumbooleanDB()
     {
@@ -115,4 +115,17 @@
         System.out.println("large buildIn took " + (end - start) + " milliseconds");
     }
 
+    /**
+     * Test whether LIKE clauses are built correctly.
+     */
+    public void testBuildLike()
+    {
+        String result = SqlExpression.buildLike(
+                "COL", "fre%", SqlEnum.LIKE, false, db);
+        assertEquals("COL LIKE fre%", result);
+        
+        result = SqlExpression.buildLike(
+                "COL", "50\\\\%", SqlEnum.LIKE, false, db);
+        assertEquals("COL = 50%", result);
+    }
 }

Modified: db/torque/site/trunk/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/db/torque/site/trunk/xdocs/changes.xml?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/site/trunk/xdocs/changes.xml (original)
+++ db/torque/site/trunk/xdocs/changes.xml Thu Nov  2 12:51:15 2006
@@ -28,6 +28,9 @@
   <body>
 
   <release version="3.2.1-dev" date="in SVN">
+    <action type="fix" dev="tfischer" issue="TORQUE-57">
+      Fixed handling of escaping (by backslashes) for LIKE clauses. 
+    </action>
     <action type="fix" dev="tfischer" issue="TORQUE-59">
       Booleanchar and Booleanint now work for chained (and'ed, or'ed)
       Criterions. 

Modified: db/torque/test/trunk/test-project/src/java/org/apache/torque/DataTest.java
URL: http://svn.apache.org/viewvc/db/torque/test/trunk/test-project/src/java/org/apache/torque/DataTest.java?view=diff&rev=470516&r1=470515&r2=470516
==============================================================================
--- db/torque/test/trunk/test-project/src/java/org/apache/torque/DataTest.java (original)
+++ db/torque/test/trunk/test-project/src/java/org/apache/torque/DataTest.java Thu Nov  2
12:51:15 2006
@@ -28,11 +28,20 @@
 
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
+import org.apache.torque.adapter.DB;
+import org.apache.torque.adapter.DBAxion;
+import org.apache.torque.adapter.DBCloudscape;
 import org.apache.torque.adapter.DBDerby;
 import org.apache.torque.adapter.DBFirebird;
 import org.apache.torque.adapter.DBHypersonicSQL;
+import org.apache.torque.adapter.DBInformix;
+import org.apache.torque.adapter.DBInstantDB;
 import org.apache.torque.adapter.DBInterbase;
+import org.apache.torque.adapter.DBMM;
+import org.apache.torque.adapter.DBMSSQL;
 import org.apache.torque.adapter.DBOracle;
+import org.apache.torque.adapter.DBSybase;
+import org.apache.torque.adapter.DBWeblogic;
 import org.apache.torque.om.StringKey;
 import org.apache.torque.test.A;
 import org.apache.torque.test.APeer;
@@ -1030,17 +1039,60 @@
         List result = AuthorPeer.doSelect(criteria);
         assertTrue("Size of result is not 1, but " + result.size(), 
         		result.size() == 1);
+
+        // LIKE treatment might be different (e.g. postgres), so check extra
+        criteria = new Criteria();
+        criteria.add(
+                AuthorPeer.NAME, 
+                (Object) author.getName().toLowerCase().replace('r', '%'),
+                Criteria.LIKE);
+        criteria.setIgnoreCase(true);
+        result = AuthorPeer.doSelect(criteria);
+        assertTrue("Size of result is not 1, but " + result.size(), 
+                result.size() == 1);
         
-        // check ignore case in order by
-        if (Torque.getDB(Torque.getDefaultDB()) instanceof DBInterbase
-                || Torque.getDB(Torque.getDefaultDB()) instanceof DBFirebird)
+        // Check that case is not ignored if ignoreCase is not set
+        // This is known not to work for mysql
+        author = new Author();
+        author.setName("author");
+        author.save();
+        
+        DB db = Torque.getDB(Torque.getDefaultDB()); 
+        if (db instanceof DBMM 
+                || db instanceof DBAxion 
+                || db instanceof DBCloudscape
+                || db instanceof DBInformix
+                || db instanceof DBInstantDB
+                || db instanceof DBWeblogic
+                || db instanceof DBMSSQL
+                || db instanceof DBSybase)
         {
             log.error("testIgnoreCase(): "
-                    + "Case insensitive ordering is known not to work with "
-                    + "Firebird and Interbase");
-            // failing is "expected", so exit without error
-            return;
+                    + "Case sensitive comparisons are known not to work"
+                    + " with Axion, Cloudscape, Informix, InstantDB, "
+                    + "Mysql, MSSQL, Sybase and Weblogic");
+            // failing is "expected", so bypass without error
+        }
+        else
+        {
+            criteria = new Criteria();
+            criteria.add(AuthorPeer.NAME, author.getName());
+            result = AuthorPeer.doSelect(criteria);
+            assertTrue("Size of result is not 1, but " + result.size(), 
+                    result.size() == 1);
+    
+            // again check LIKE treatment
+            criteria = new Criteria();
+            criteria.add(
+                    AuthorPeer.NAME, 
+                    (Object) author.getName().replace('r', '%'),
+                    Criteria.LIKE);
+            result = AuthorPeer.doSelect(criteria);
+            assertTrue("Size of result is not 1, but " + result.size(), 
+                    result.size() == 1);
         }
+
+        // check ignore case in order by
         cleanBookstore();
         author = new Author();
         author.setName("a");
@@ -1048,30 +1100,40 @@
         author = new Author();
         author.setName("B");
         author.save();
-        criteria.clear();
-        criteria.setIgnoreCase(true);
-        criteria.addAscendingOrderByColumn(AuthorPeer.NAME);
-        result = AuthorPeer.doSelect(criteria);
-        assertTrue("Size of result is not 2, but " + result.size(), 
-        		result.size() == 2);
-        author = (Author) result.get(0);
-        assertEquals("First", author.getName(), "a");
-        
-        cleanBookstore();
-        author = new Author();
-        author.setName("A");
-        author.save();
-        author = new Author();
-        author.setName("b");
-        author.save();
-        criteria.clear();
-        criteria.setIgnoreCase(true);
-        criteria.addAscendingOrderByColumn(AuthorPeer.NAME);
-        result = AuthorPeer.doSelect(criteria);
-        assertTrue("Size of result is not 2, but " + result.size(), 
-        		result.size() == 2);
-        author = (Author) result.get(0);
-        assertEquals("First", author.getName(), "A");
+        if (db instanceof DBInterbase || db instanceof DBFirebird)
+        {
+            log.error("testIgnoreCase(): "
+                    + "Case insensitive ordering is known not to work with "
+                    + "Firebird and Interbase");
+            // failing is "expected", so bypass without error
+        }
+        else
+        {
+            criteria.clear();
+            criteria.setIgnoreCase(true);
+            criteria.addAscendingOrderByColumn(AuthorPeer.NAME);
+            result = AuthorPeer.doSelect(criteria);
+            assertTrue("Size of result is not 2, but " + result.size(), 
+            		result.size() == 2);
+            author = (Author) result.get(0);
+            assertEquals("First", author.getName(), "a");
+            
+            cleanBookstore();
+            author = new Author();
+            author.setName("A");
+            author.save();
+            author = new Author();
+            author.setName("b");
+            author.save();
+            criteria.clear();
+            criteria.setIgnoreCase(true);
+            criteria.addAscendingOrderByColumn(AuthorPeer.NAME);
+            result = AuthorPeer.doSelect(criteria);
+            assertTrue("Size of result is not 2, but " + result.size(), 
+            		result.size() == 2);
+            author = (Author) result.get(0);
+            assertEquals("First", author.getName(), "A");
+        }
     }
 
     /**
@@ -1123,7 +1185,8 @@
         author = new Author();
         AuthorPeer.populateObject(record, BookPeer.numColumns + 1, author);
 
-        if (book.getAuthorId() == author.getAuthorId()) {
+        if (book.getAuthorId() == author.getAuthorId())
+        {
             fail("wrong Ids read");
         }
     }
@@ -1569,6 +1632,127 @@
             assertTrue("IfcTablePeer.doSelect should return instances of TestInterface",

                     i.next() instanceof TestInterface);
         }
+    }
+
+    public void testEscaping() throws Exception
+    {
+        String[] authorNames 
+                = {"abc", "bbc", "a_c", "a%c", "a\\c", "a\"c", "a'c"};
+
+        Map likeResults = new HashMap();
+
+        likeResults.put("a\\_c", "a_c");
+        likeResults.put("a\\_%", "a_c");
+        likeResults.put("%\\_c", "a_c");
+        
+        likeResults.put("a\\%c", "a%c");
+        likeResults.put("a\\%%", "a%c");
+        likeResults.put("%\\%c", "a%c");
+
+        likeResults.put("a\\\\c", "a\\c");
+        likeResults.put("a\\\\%", "a\\c");
+        likeResults.put("%\\\\c", "a\\c");
+
+        likeResults.put("a\"c", "a\"c");
+        likeResults.put("a\"%", "a\"c");
+        likeResults.put("%\"c", "a\"c");
+        
+        likeResults.put("a'c", "a'c");
+        likeResults.put("a'%", "a'c");
+        likeResults.put("%'c", "a'c");
+        cleanBookstore();
+
+        for (int i = 0; i < authorNames.length; ++i)
+        {
+            Author author = new Author();
+            author.setName(authorNames[i]);
+            author.save();
+        }
+        
+        for (int i = 0; i < authorNames.length; ++i)
+        {
+            Criteria criteria = new Criteria();
+            criteria.add(AuthorPeer.NAME, authorNames[i]);
+            List authorList = AuthorPeer.doSelect(criteria);
+            assertEquals(
+                    "AuthorList should contain one author"
+                        + " when querying for " + authorNames[i], 
+                    1, 
+                    authorList.size());
+            Author author = (Author) authorList.get(0);
+            assertEquals("Name of author should be " + authorNames[i],
+                    authorNames[i],
+                    author.getName());
+        }
+
+        for (Iterator likeResultIt = likeResults.entrySet().iterator(); 
+                likeResultIt.hasNext(); )
+        {
+            Map.Entry likeResult = (Map.Entry) likeResultIt.next();
+            Criteria criteria = new Criteria();
+            criteria.add(
+                    AuthorPeer.NAME, 
+                    likeResult.getKey(), 
+                    Criteria.LIKE);
+            List authorList = AuthorPeer.doSelect(criteria);
+            assertEquals(
+                    "AuthorList should contain one author"
+                        + " when querying for " + likeResult.getKey(), 
+                    1, 
+                    authorList.size());
+            Author author = (Author) authorList.get(0);
+            assertEquals("Name of author should be " 
+                        + likeResult.getValue()
+                        + " when querying for "
+                        + likeResult.getKey(),
+                    likeResult.getValue(),
+                    author.getName());
+        }
+        
+        // check that case insensitivity is maintained if
+        // a like is replaced with an equals (no wildcard present)
+        // This might be a problem for databases which use ILIKE
+        Criteria criteria = new Criteria();
+        criteria.add(AuthorPeer.NAME, (Object) "AbC", Criteria.LIKE);
+        criteria.setIgnoreCase(true);
+        List authorList = AuthorPeer.doSelect(criteria);
+        assertEquals(
+                "AuthorList should contain one author", 
+                1, 
+                authorList.size());
+        Author author = (Author) authorList.get(0);
+        assertEquals("Name of author should be abc",
+                "abc",
+                author.getName());
+        
+        // check that the escape clause (where needed) also works with all
+        // the other query stuff
+        criteria = new Criteria();
+        Criteria.Criterion criterion1 = criteria.getNewCriterion(
+                AuthorPeer.NAME, 
+                (Object) "b%",
+                Criteria.LIKE);
+        Criteria.Criterion criterion2 = criteria.getNewCriterion(
+                AuthorPeer.NAME, 
+                (Object) "a\\%%", 
+                Criteria.LIKE);
+        Criteria.Criterion criterion3 = criteria.getNewCriterion(
+                AuthorPeer.NAME, 
+                (Object) "cbc", 
+                Criteria.LIKE);
+        criteria.add(criterion1.or(criterion2).or(criterion3));
+        criteria.addAscendingOrderByColumn(AuthorPeer.NAME);
+        criteria.setOffset(1);
+        criteria.setLimit(1);
+        authorList = AuthorPeer.doSelect(criteria);
+        assertEquals(
+                "AuthorList should contain one author", 
+                1, 
+                authorList.size());
+        author = (Author) authorList.get(0);
+        assertEquals("Name of author should be bbc",
+                "bbc",
+                author.getName());
     }
 
     /**



---------------------------------------------------------------------
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