db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From henn...@apache.org
Subject cvs commit: db-torque/src/java/org/apache/torque/util LimitHelper.java BasePeer.java Query.java
Date Thu, 26 Aug 2004 17:01:26 GMT
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


Mime
View raw message