Return-Path: Mailing-List: contact turbine-torque-dev-help@jakarta.apache.org; run by ezmlm Delivered-To: mailing list turbine-torque-dev@jakarta.apache.org Received: (qmail 904 invoked by uid 97); 9 Dec 2002 21:25:49 -0000 Received: (qmail 900 invoked by uid 98); 9 Dec 2002 21:25:49 -0000 X-Antivirus: nagoya (v4218 created Aug 14 2002) Received: (qmail 817 invoked from network); 9 Dec 2002 21:25:46 -0000 Received: from daedalus.apache.org (HELO apache.org) (63.251.56.142) by nagoya.betaversion.org with SMTP; 9 Dec 2002 21:25:46 -0000 Received: (qmail 60857 invoked by uid 500); 9 Dec 2002 21:24:34 -0000 Received: (qmail 60849 invoked from network); 9 Dec 2002 21:24:34 -0000 Received: from icarus.apache.org (63.251.56.143) by daedalus.apache.org with SMTP; 9 Dec 2002 21:24:34 -0000 Received: (qmail 98260 invoked by uid 1268); 9 Dec 2002 21:24:33 -0000 Date: 9 Dec 2002 21:24:33 -0000 Message-ID: <20021209212433.98259.qmail@icarus.apache.org> From: jmcnally@apache.org To: jakarta-turbine-torque-cvs@apache.org Subject: cvs commit: jakarta-turbine-torque/src/rttest/org/apache/torque DataTest.java X-Spam-Rating: daedalus.apache.org 1.6.2 0/1000/N 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 @@ * . */ +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