You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by he...@apache.org on 2004/08/26 19:01:26 UTC
cvs commit: db-torque/src/java/org/apache/torque/util LimitHelper.java BasePeer.java Query.java
henning 2004/08/26 10:01:26
Modified: src/java/org/apache/torque/util Tag: TORQUE_3_1_BRANCH
BasePeer.java Query.java
Added: src/java/org/apache/torque/util Tag: TORQUE_3_1_BRANCH
LimitHelper.java
Log:
Folks,
while I was using a blind eye to look at the mess that the LIMIT /
OFFSET generation has turned into, the last patch from Augustin was
the straw that broke the camels' back. Or, to be literal, broke the
LIMIT / OFFSET generation for everything else but DB2. E.g. PostgreSQL.
Which upsets me, because I use PostgreSQL. However, I didn't use LIMIT
until about an hour ago.
At some point, one has to take a step back, look at what has been done
and ask oneself "is this really what I intended to do". Three
different places in the already much too large BasePeer class where
limits are checked in different ways; Criteria manipulation just to
satisfy a single caller of createQueryString, patch over patch over
patch just to get this somehow to compile.
My stomach couldn't take this any longer (and I need a working OFFSET
LIMIT for PostgreSQL and I would not touch this mess with a 3 metre
pole).
So I ripped everything out, rewrote it into a helper class and put it
back in. Cleaned up the logic and everything. It still passes the unit
tests (which is a good sign). And there are a lot of the invariants
removed. Why do we need "supportsNativeOffset" and
"supportsNativeLimit" when e.g. Oracle and DB2 allows this (by using a
subquery) but return false/false for the supportsNativeLimit/Offset
(DB2) or true/false (Oracle)?
All of this stuff _needs_ testing. Sorry Scott. We will need an RC2.
Revision Changes Path
No revision
No revision
1.76.2.4 +28 -283 db-torque/src/java/org/apache/torque/util/BasePeer.java
Index: BasePeer.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/util/BasePeer.java,v
retrieving revision 1.76.2.3
retrieving revision 1.76.2.4
diff -u -r1.76.2.3 -r1.76.2.4
--- BasePeer.java 6 Aug 2004 12:13:12 -0000 1.76.2.3
+++ BasePeer.java 26 Aug 2004 17:01:25 -0000 1.76.2.4
@@ -73,6 +73,7 @@
* @author <a href="mailto:stephenh@chase3000.com">Stephen Haberman</a>
* @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
* @author <a href="mailto:vido@ldh.org">Augustin Vidovic</a>
+ * @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
* @version $Id$
*/
public abstract class BasePeer implements java.io.Serializable
@@ -887,100 +888,6 @@
}
/**
- * Build Oracle-style query with limit or offset.
- * If the original SQL is in variable: query then the requlting
- * SQL looks like this:
- * <pre>
- * SELECT B.* FROM (
- * SELECT A.*, rownum as TORQUE$ROWNUM FROM (
- * query
- * ) A
- * ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM
- * <= offset + limit
- * </pre>
- *
- * @param query the query
- * @param limit
- * @param offset
- * @return oracle-style query
- */
- private static String createOracleLimitOffsetQuery(Query query,
- int limit, int offset)
- {
- 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);
- }
- return buf.toString();
- }
-
- /**
- * Build DB2 (OLAP) -style query with limit or offset.
- * If the original SQL is in variable: query then the requlting
- * SQL looks like this:
- * <pre>
- * SELECT B.* FROM (
- * SELECT A.*, row_number() over() as TORQUE$ROWNUM FROM (
- * query
- * ) A
- * ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM
- * <= offset + limit
- * </pre>
- *
- * @param query the query
- * @param limit
- * @param offset
- * @return oracle-style query
- */
- private static String createDB2LimitOffsetQuery(Query query,
- int limit, int offset)
- {
- StringBuffer buf = new StringBuffer();
- buf.append("SELECT B.* FROM ( ");
- buf.append("SELECT A.*, row_number() over() 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);
- }
- return buf.toString();
- }
-
- /**
* Method to create an SQL query for actual execution based on values in a
* Criteria.
*
@@ -992,51 +899,8 @@
throws TorqueException
{
Query query = createQuery(criteria);
- DB db = Torque.getDB(criteria.getDbName());
-
- // Limit the number of rows returned.
- int limit = criteria.getLimit();
- int offset = criteria.getOffset();
-
- String sql = null;
- if (limit > 0 || offset > 0)
- {
- if (db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
- {
- sql = createOracleLimitOffsetQuery(query, limit, offset);
- criteria.setLimit(-1);
- criteria.setOffset(0);
- }
- else if (db.getLimitStyle() == DB.LIMIT_STYLE_DB2)
- {
- sql = createDB2LimitOffsetQuery(query, limit, offset);
- criteria.setLimit(-1);
- criteria.setOffset(0);
- }
- }
- else
- {
- 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();
- }
- if (log.isDebugEnabled())
- {
- log.debug(sql);
- }
- return sql;
+ StringBuffer stmt = new StringBuffer();
+ return query.toStringBuffer(stmt).toString();
}
/**
@@ -1306,62 +1170,7 @@
}
}
- // Limit the number of rows returned.
- int limit = criteria.getLimit();
- int offset = criteria.getOffset();
- String limitString = null;
- if (offset > 0 && db.supportsNativeOffset())
- {
- switch (db.getLimitStyle())
- {
- case DB.LIMIT_STYLE_MYSQL :
- limitString = new StringBuffer()
- .append(offset)
- .append(", ")
- .append(limit)
- .toString();
- break;
- case DB.LIMIT_STYLE_POSTGRES :
- limitString = new StringBuffer()
- .append(limit)
- .append(" offset ")
- .append(offset)
- .toString();
- break;
- }
-
- // The following is now done in createQueryString() to enable this
- // method to be used as part of Criteria.toString() without altering
- // the criteria itself. The commented code is retained here to
- // make it easier to understand how the criteria is built into a
- // query.
-
- // 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()
- && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
- {
- limitString = String.valueOf(limit);
-
- // The following is now done in createQueryString() to enable this
- // method to be used as part of Criteria.toString() without altering
- // the criteria itself. The commented code is retained here to
- // make it easier to understand how the criteria is built into a
- // query.
-
- // Now set the criteria's limit to return the full
- // resultset since the results are limited on the server.
- //criteria.setLimit(-1);
- }
-
- if (limitString != null)
- {
- query.setLimit(limitString);
- }
+ LimitHelper.buildLimit(criteria, query);
return query;
}
@@ -1406,12 +1215,27 @@
public static List doSelect(Criteria criteria, Connection con)
throws TorqueException
{
- return executeQuery(
- createQueryString(criteria),
- criteria.getOffset(),
- criteria.getLimit(),
- criteria.isSingleRecord(),
- con);
+ Query query = createQuery(criteria);
+
+ if (query.hasLimit())
+ {
+ // We don't need Village to limit the Query
+ return executeQuery(query.toString(),
+ 0,
+ -1,
+ criteria.isSingleRecord(),
+ con);
+ }
+ else
+ {
+ // There is not limit string registered
+ // with the query. Let Village decide.
+ return executeQuery(query.toString(),
+ criteria.getOffset(),
+ criteria.getLimit(),
+ criteria.isSingleRecord(),
+ con);
+ }
}
/**
@@ -2512,87 +2336,8 @@
}
}
- // Limit the number of rows returned.
- int limit = criteria.getLimit();
- int offset = criteria.getOffset();
- String limitString = null;
- if (offset > 0 && db.supportsNativeOffset()
- && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
- {
- switch (db.getLimitStyle())
- {
- case DB.LIMIT_STYLE_MYSQL :
- limitString = new StringBuffer()
- .append(offset)
- .append(", ")
- .append(limit)
- .toString();
- break;
- case DB.LIMIT_STYLE_POSTGRES :
- limitString = new StringBuffer()
- .append(limit)
- .append(" offset ")
- .append(offset)
- .toString();
- break;
- }
-
- // 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()
- && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
- {
- limitString = String.valueOf(limit);
-
- // Now set the criteria's limit to return the full
- // resultset since the results are limited on the server.
- criteria.setLimit(-1);
- }
-
- if (limitString != null)
- {
- switch (db.getLimitStyle())
- {
- /* Don't have a Sybase install to validate this against(dlr)
- case DB.LIMIT_STYLE_SYBASE:
- query.setRowcount(limitString);
- break;
- */
- default :
- query.setLimit(limitString);
- }
- }
-
- String sql = null;
- if (limit > 0 || offset > 0)
- {
- if ( db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
- {
- sql = createOracleLimitOffsetQuery(query, limit, offset);
- criteria.setLimit(-1);
- criteria.setOffset(0);
- }
- else if ( db.getLimitStyle() == DB.LIMIT_STYLE_DB2)
- {
- sql = createDB2LimitOffsetQuery(query, limit, offset);
- criteria.setLimit(-1);
- criteria.setOffset(0);
- }
- }
- else
- {
- sql = query.toString();
- }
-
- if (log.isDebugEnabled())
- {
- log.debug(sql);
- }
- queryString.append(sql);
+ LimitHelper.buildLimit(criteria, query);
+ queryString.append(query.toString());
}
/**
1.12.2.3 +75 -3 db-torque/src/java/org/apache/torque/util/Query.java
Index: Query.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/util/Query.java,v
retrieving revision 1.12.2.2
retrieving revision 1.12.2.3
diff -u -r1.12.2.2 -r1.12.2.3
--- Query.java 20 May 2004 04:36:06 -0000 1.12.2.2
+++ Query.java 26 Aug 2004 17:01:25 -0000 1.12.2.3
@@ -50,6 +50,8 @@
private UniqueList groupByColumns = new UniqueList();
private String having;
private String limit;
+ private String preLimit;
+ private String postLimit;
private String rowcount;
/**
@@ -183,6 +185,28 @@
}
/**
+ * Get the Pre limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public void setPreLimit(String preLimit)
+ {
+ this.preLimit = preLimit;
+ }
+
+ /**
+ * Set the Post limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public void setPostLimit(String postLimit)
+ {
+ this.postLimit = postLimit;
+ }
+
+ /**
* Set the rowcount number. This is used to limit the number of
* rows returned by Sybase and MS SQL/Server.
*
@@ -216,6 +240,40 @@
}
/**
+ * Get the Post limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public String getPostLimit()
+ {
+ return postLimit;
+ }
+
+ /**
+ * Get the Pre limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public String getPreLimit()
+ {
+ return preLimit;
+ }
+
+ /**
+ * True if this query has a limit clause registered.
+ *
+ * @return true if a limit clause exists.
+ */
+ public boolean hasLimit()
+ {
+ return ((preLimit != null)
+ || (postLimit != null)
+ || (limit != null));
+ }
+
+ /**
* Get the rowcount number. This is used to limit the number of
* returned by a query in Sybase and MS SQL/Server.
*
@@ -233,7 +291,16 @@
*/
public String toString()
{
- StringBuffer stmt = new StringBuffer();
+ return toStringBuffer(new StringBuffer()).toString();
+ }
+
+ public StringBuffer toStringBuffer(StringBuffer stmt)
+ {
+ if (preLimit != null)
+ {
+ stmt.append(preLimit);
+ }
+
if (rowcount != null)
{
stmt.append(ROWCOUNT)
@@ -275,6 +342,11 @@
stmt.append(ROWCOUNT)
.append("0");
}
- return stmt.toString();
+ if (postLimit != null)
+ {
+ stmt.append(postLimit);
+ }
+
+ return stmt;
}
}
No revision
Index: Query.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/util/Query.java,v
retrieving revision 1.12.2.2
retrieving revision 1.12.2.3
diff -u -r1.12.2.2 -r1.12.2.3
--- Query.java 20 May 2004 04:36:06 -0000 1.12.2.2
+++ Query.java 26 Aug 2004 17:01:25 -0000 1.12.2.3
@@ -50,6 +50,8 @@
private UniqueList groupByColumns = new UniqueList();
private String having;
private String limit;
+ private String preLimit;
+ private String postLimit;
private String rowcount;
/**
@@ -183,6 +185,28 @@
}
/**
+ * Get the Pre limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public void setPreLimit(String preLimit)
+ {
+ this.preLimit = preLimit;
+ }
+
+ /**
+ * Set the Post limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public void setPostLimit(String postLimit)
+ {
+ this.postLimit = postLimit;
+ }
+
+ /**
* Set the rowcount number. This is used to limit the number of
* rows returned by Sybase and MS SQL/Server.
*
@@ -216,6 +240,40 @@
}
/**
+ * Get the Post limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public String getPostLimit()
+ {
+ return postLimit;
+ }
+
+ /**
+ * Get the Pre limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public String getPreLimit()
+ {
+ return preLimit;
+ }
+
+ /**
+ * True if this query has a limit clause registered.
+ *
+ * @return true if a limit clause exists.
+ */
+ public boolean hasLimit()
+ {
+ return ((preLimit != null)
+ || (postLimit != null)
+ || (limit != null));
+ }
+
+ /**
* Get the rowcount number. This is used to limit the number of
* returned by a query in Sybase and MS SQL/Server.
*
@@ -233,7 +291,16 @@
*/
public String toString()
{
- StringBuffer stmt = new StringBuffer();
+ return toStringBuffer(new StringBuffer()).toString();
+ }
+
+ public StringBuffer toStringBuffer(StringBuffer stmt)
+ {
+ if (preLimit != null)
+ {
+ stmt.append(preLimit);
+ }
+
if (rowcount != null)
{
stmt.append(ROWCOUNT)
@@ -275,6 +342,11 @@
stmt.append(ROWCOUNT)
.append("0");
}
- return stmt.toString();
+ if (postLimit != null)
+ {
+ stmt.append(postLimit);
+ }
+
+ return stmt;
}
}
No revision
Index: Query.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/util/Query.java,v
retrieving revision 1.12.2.2
retrieving revision 1.12.2.3
diff -u -r1.12.2.2 -r1.12.2.3
--- Query.java 20 May 2004 04:36:06 -0000 1.12.2.2
+++ Query.java 26 Aug 2004 17:01:25 -0000 1.12.2.3
@@ -50,6 +50,8 @@
private UniqueList groupByColumns = new UniqueList();
private String having;
private String limit;
+ private String preLimit;
+ private String postLimit;
private String rowcount;
/**
@@ -183,6 +185,28 @@
}
/**
+ * Get the Pre limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public void setPreLimit(String preLimit)
+ {
+ this.preLimit = preLimit;
+ }
+
+ /**
+ * Set the Post limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public void setPostLimit(String postLimit)
+ {
+ this.postLimit = postLimit;
+ }
+
+ /**
* Set the rowcount number. This is used to limit the number of
* rows returned by Sybase and MS SQL/Server.
*
@@ -216,6 +240,40 @@
}
/**
+ * Get the Post limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public String getPostLimit()
+ {
+ return postLimit;
+ }
+
+ /**
+ * Get the Pre limit String. Oracle and DB2 want to encapsulate
+ * a query into a subquery for limiting.
+ *
+ * @return A String with the preLimit.
+ */
+ public String getPreLimit()
+ {
+ return preLimit;
+ }
+
+ /**
+ * True if this query has a limit clause registered.
+ *
+ * @return true if a limit clause exists.
+ */
+ public boolean hasLimit()
+ {
+ return ((preLimit != null)
+ || (postLimit != null)
+ || (limit != null));
+ }
+
+ /**
* Get the rowcount number. This is used to limit the number of
* returned by a query in Sybase and MS SQL/Server.
*
@@ -233,7 +291,16 @@
*/
public String toString()
{
- StringBuffer stmt = new StringBuffer();
+ return toStringBuffer(new StringBuffer()).toString();
+ }
+
+ public StringBuffer toStringBuffer(StringBuffer stmt)
+ {
+ if (preLimit != null)
+ {
+ stmt.append(preLimit);
+ }
+
if (rowcount != null)
{
stmt.append(ROWCOUNT)
@@ -275,6 +342,11 @@
stmt.append(ROWCOUNT)
.append("0");
}
- return stmt.toString();
+ if (postLimit != null)
+ {
+ stmt.append(postLimit);
+ }
+
+ return stmt;
}
}
1.1.2.1 +246 -0 db-torque/src/java/org/apache/torque/util/Attic/LimitHelper.java
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: cvs commit: db-torque/src/java/org/apache/torque/util LimitHelper.java
BasePeer.java Query.java
Posted by Scott Eade <se...@backstagetech.com.au>.
henning@apache.org wrote:
>henning 2004/08/26 10:01:26
>
> All of this stuff _needs_ testing. Sorry Scott. We will need an RC2.
>
>
As you say, this is what release candidates are for.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
RE: cvs commit: db-torque/src/java/org/apache/torque/util LimitHelper.java
BasePeer.java Query.java
Posted by Thomas Fischer <Fi...@seitenbau.net>.
Henning,
thanks for sorting this out. I was also very confused about the
offset/limit-handling some time ago, and I thought "ok, just don't touch
this stuff".
Sorry I am too busy at the moment to help testing but I hope this will
change soon.
Thomas
> Folks,
>
> while I was using a blind eye to look at the mess that the LIMIT /
> OFFSET generation has turned into, the last patch from Augustin was
> the straw that broke the camels' back. Or, to be literal, broke the
> LIMIT / OFFSET generation for everything else but DB2. E.g. PostgreSQL.
>
> Which upsets me, because I use PostgreSQL. However, I didn't use LIMIT
> until about an hour ago.
>
> At some point, one has to take a step back, look at what has been done
> and ask oneself "is this really what I intended to do". Three
> different places in the already much too large BasePeer class where
> limits are checked in different ways; Criteria manipulation just to
> satisfy a single caller of createQueryString, patch over patch over
> patch just to get this somehow to compile.
>
> My stomach couldn't take this any longer (and I need a working OFFSET
> LIMIT for PostgreSQL and I would not touch this mess with a 3 metre
> pole).
>
> So I ripped everything out, rewrote it into a helper class and put it
> back in. Cleaned up the logic and everything. It still passes the unit
> tests (which is a good sign). And there are a lot of the invariants
> removed. Why do we need "supportsNativeOffset" and
> "supportsNativeLimit" when e.g. Oracle and DB2 allows this (by using a
> subquery) but return false/false for the supportsNativeLimit/Offset
> (DB2) or true/false (Oracle)?
>
> All of this stuff _needs_ testing. Sorry Scott. We will need an RC2.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org