db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jmcna...@apache.org
Subject cvs commit: jakarta-turbine-torque/src/rttest/org/apache/torque DataTest.java
Date Mon, 09 Dec 2002 21:24:33 GMT
jmcnally    2002/12/09 13:24:33

  Modified:    src/java/org/apache/torque/util BasePeer.java
               src/rttest/org/apache/torque DataTest.java
  Log:
  patch by Rafal Maczewski to properly support native offset and limit in
  oracle.  Issue#: TRQ47
  
  Others have submitted similar patches in the past, but there has never been
  junit tests to go with it.  As I now need this functionality, I added a test
  case.
  
  Revision  Changes    Path
  1.56      +109 -27   jakarta-turbine-torque/src/java/org/apache/torque/util/BasePeer.java
  
  Index: BasePeer.java
  ===================================================================
  RCS file: /home/cvs/jakarta-turbine-torque/src/java/org/apache/torque/util/BasePeer.java,v
  retrieving revision 1.55
  retrieving revision 1.56
  diff -u -r1.55 -r1.56
  --- BasePeer.java	2 Dec 2002 02:58:18 -0000	1.55
  +++ BasePeer.java	9 Dec 2002 21:24:33 -0000	1.56
  @@ -930,22 +930,67 @@
           // Limit the number of rows returned.
           int limit = criteria.getLimit();
           int offset = criteria.getOffset();
  -        if (offset > 0 && db.supportsNativeOffset())
  -        {
  -            // Now set the criteria's limit and offset to return the
  -            // full resultset since the results are limited on the
  -            // server.
  +
  +        String sql;
  +        if ((limit > 0 || offset > 0) 
  +            && db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE) 
  +        {
  +            // Build Oracle-style query with limit or offset.
  +            // If the original SQL is in variable: query then the requlting 
  +            // SQL looks like this:
  +            // SELECT B.* FROM ( 
  +            //          SELECT A.*, rownum as TORQUE$ROWNUM FROM (
  +            //                  query
  +            //          ) A
  +            //     ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM <= offset
+ limit
  +            StringBuffer buf = new StringBuffer();
  +            buf.append("SELECT B.* FROM ( ");
  +            buf.append("SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( ");
  +            
  +            buf.append(query.toString());
  +            buf.append(" ) A ");
  +            buf.append(" ) B WHERE ");
  +
  +            if (offset > 0) 
  +            {
  +                buf.append(" B.TORQUE$ROWNUM > ");
  +                buf.append(offset);
  +                if (limit > 0) 
  +                {
  +                    buf.append(" AND B.TORQUE$ROWNUM <= ");
  +                    buf.append(offset + limit);
  +                }
  +            }
  +            else 
  +            {
  +                buf.append(" B.TORQUE$ROWNUM <= ");
  +                buf.append(limit);
  +            }
               criteria.setLimit(-1);
               criteria.setOffset(0);
  -        }
  -        else if (limit > 0 && db.supportsNativeLimit())
  +
  +            sql = buf.toString();
  +        } 
  +        else 
           {
  -            // Now set the criteria's limit to return the full
  -            // resultset since the results are limited on the server.
  -            criteria.setLimit(-1);
  +            if (offset > 0 && db.supportsNativeOffset())
  +            {
  +                // Now set the criteria's limit and offset to return the
  +                // full resultset since the results are limited on the
  +                // server.
  +                criteria.setLimit(-1);
  +                criteria.setOffset(0);
  +            }
  +            else if (limit > 0 && db.supportsNativeLimit())
  +            {
  +                // Now set the criteria's limit to return the full
  +                // resultset since the results are limited on the server.
  +                criteria.setLimit(-1);
  +            }
  +
  +            sql = query.toString();
           }
   
  -        String sql = query.toString();
           category.debug(sql);
           return sql;
       }
  @@ -1269,7 +1314,8 @@
               //criteria.setLimit(-1);
               //criteria.setOffset(0);
           }
  -        else if (limit > 0 && db.supportsNativeLimit())
  +        else if (limit > 0 && db.supportsNativeLimit() 
  +                 && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
           {
               limitString = String.valueOf(limit);
   
  @@ -1286,14 +1332,7 @@
   
           if (limitString != null)
           {
  -            switch (db.getLimitStyle())
  -            {
  -                case DB.LIMIT_STYLE_ORACLE :
  -                    whereClause.add("rownum <= " + limitString);
  -                    break;
  -                default :
  -                    query.setLimit(limitString);
  -            }
  +            query.setLimit(limitString);
           }
   
           return query;
  @@ -2439,7 +2478,8 @@
           int limit = criteria.getLimit();
           int offset = criteria.getOffset();
           String limitString = null;
  -        if (offset > 0 && db.supportsNativeOffset())
  +        if (offset > 0 && db.supportsNativeOffset()
  +            && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
           {
               switch (db.getLimitStyle())
               {
  @@ -2467,7 +2507,8 @@
               criteria.setLimit(-1);
               criteria.setOffset(0);
           }
  -        else if (limit > 0 && db.supportsNativeLimit())
  +        else if (limit > 0 && db.supportsNativeLimit()
  +                 && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
           {
               limitString = String.valueOf(limit);
   
  @@ -2480,9 +2521,6 @@
           {
               switch (db.getLimitStyle())
               {
  -                case DB.LIMIT_STYLE_ORACLE :
  -                    whereClause.add("rownum <= " + limitString);
  -                    break;
                       /* Don't have a Sybase install to validate this against. (dlr)
                       case DB.LIMIT_STYLE_SYBASE:
                           query.setRowcount(limitString);
  @@ -2493,7 +2531,51 @@
               }
           }
   
  -        String sql = query.toString();
  +        String sql;
  +        if ((limit > 0 || offset > 0) 
  +            && db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE) 
  +        {
  +            // Build Oracle-style query with limit or offset.
  +            // If the original SQL is in variable: query then the requlting 
  +            // SQL looks like this:
  +            // SELECT B.* FROM ( 
  +            //          SELECT A.*, rownum as TORQUE$ROWNUM FROM (
  +            //                  query
  +            //          ) A
  +            //     ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM <= offset
+ limit
  +            StringBuffer buf = new StringBuffer();
  +            buf.append("SELECT B.* FROM ( ");
  +            buf.append("SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( ");
  +            
  +            buf.append(query.toString());
  +            buf.append(" ) A ");
  +            buf.append(" ) B WHERE ");
  +
  +            if (offset > 0) 
  +            { 
  +                buf.append(" B.TORQUE$ROWNUM > ");
  +                buf.append(offset);
  +                if (limit > 0)
  +                {
  +                    buf.append(" AND B.TORQUE$ROWNUM <= ");
  +                    buf.append(offset + limit);
  +                }
  +            } 
  +            else
  +            {
  +                buf.append(" B.TORQUE$ROWNUM <= ");
  +                buf.append(limit);
  +            }
  +            criteria.setLimit(-1);
  +            criteria.setOffset(0);
  +
  +            sql = buf.toString();
  +        } 
  +        else 
  +        {
  +            sql = query.toString();
  +        }
  +
           category.debug(sql);
           queryString.append(sql);
       }
  
  
  
  1.4       +49 -1     jakarta-turbine-torque/src/rttest/org/apache/torque/DataTest.java
  
  Index: DataTest.java
  ===================================================================
  RCS file: /home/cvs/jakarta-turbine-torque/src/rttest/org/apache/torque/DataTest.java,v
  retrieving revision 1.3
  retrieving revision 1.4
  diff -u -r1.3 -r1.4
  --- DataTest.java	19 Nov 2002 17:36:40 -0000	1.3
  +++ DataTest.java	9 Dec 2002 21:24:33 -0000	1.4
  @@ -54,10 +54,16 @@
    * <http://www.apache.org/>.
    */
   
  +import java.util.Map;
  +import java.util.HashMap;
  +import java.util.List;
  +import java.util.Iterator;
   import org.apache.log4j.Category;
   import org.apache.torque.BaseTestCase;
   import org.apache.torque.test.Author;
   import org.apache.torque.test.Book;
  +import org.apache.torque.test.BookPeer;
  +import org.apache.torque.util.Criteria;
   import org.apache.torque.test.MultiPk;
   
   /**
  @@ -121,6 +127,48 @@
               MultiPk mpk = new MultiPk();
               mpk.setPrimaryKey("Svarchar:N5:Schar:");
               mpk.save();
  +        }
  +        catch (Exception ex)
  +        {
  +            ex.printStackTrace();
  +        }
  +    }
  +
  +    private static final String[] validTitles = {
  +        "Book 7 - Author 8", "Book 6 - Author 8", "Book 7 - Author 7", 
  +        "Book 6 - Author 7", "Book 7 - Author 6", "Book 6 - Author 6",
  +        "Book 7 - Author 5", "Book 6 - Author 5", "Book 7 - Author 4",
  +        "Book 6 - Author 4"};
  +
  +    /**
  +     * test limit/offset which was broken for oracle (TRQ47)
  +     */
  +    public void testLimitOffset()
  +    {
  +        Map titleMap = new HashMap();
  +        for (int j=0; j<validTitles.length; j++) 
  +        {
  +            titleMap.put(validTitles[j], null);
  +        }
  +
  +        try
  +        {
  +            Criteria crit = new Criteria();
  +            Criteria.Criterion c = crit.getNewCriterion(BookPeer.TITLE, (Object)"Book 6
- Author 1", Criteria.GREATER_EQUAL);
  +            c.and(crit.getNewCriterion(BookPeer.TITLE, (Object)"Book 8 - Author 3", Criteria.LESS_EQUAL));
  +            crit.add(c);
  +            crit.addDescendingOrderByColumn(BookPeer.BOOK_ID);
  +            crit.setLimit(10);
  +            crit.setOffset(5);
  +            List books = BookPeer.doSelect(crit);
  +            assertTrue("List should have 10 books, not " + books.size(), 
  +                       books.size() == 10);
  +            for (Iterator i=books.iterator(); i.hasNext();) 
  +            {
  +                String title = ((Book)i.next()).getTitle();
  +                assertTrue("Incorrect title: " + title, 
  +                           titleMap.containsKey(title));
  +            }
           }
           catch (Exception ex)
           {
  
  
  

Mime
View raw message