Return-Path: Delivered-To: apmail-db-torque-dev-archive@www.apache.org Received: (qmail 45353 invoked from network); 2 Nov 2006 20:51:46 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 2 Nov 2006 20:51:46 -0000 Received: (qmail 24905 invoked by uid 500); 2 Nov 2006 20:51:54 -0000 Delivered-To: apmail-db-torque-dev-archive@db.apache.org Received: (qmail 24884 invoked by uid 500); 2 Nov 2006 20:51:54 -0000 Mailing-List: contact torque-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Help: List-Post: List-Id: "Apache Torque Developers List" Reply-To: "Apache Torque Developers List" Delivered-To: mailing list torque-dev@db.apache.org Received: (qmail 24868 invoked by uid 500); 2 Nov 2006 20:51:54 -0000 Received: (qmail 24861 invoked by uid 99); 2 Nov 2006 20:51:54 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 Nov 2006 12:51:53 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 Nov 2006 12:51:41 -0800 Received: by eris.apache.org (Postfix, from userid 65534) id 09F621A9846; Thu, 2 Nov 2006 12:51:17 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: torque-commits@db.apache.org From: tfischer@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20061102205117.09F621A9846@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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 false. 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 + * true. + * + * @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 @@ + + Fixed handling of escaping (by backslashes) for LIKE clauses. + 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