limiting rowset size in SQLTransformer

[Ferenc Kiraly]

	Hi!

This is my first attempt at submitting a pathc/proposal for Cocoon.
I hope I don't press the wrong buttons.
The SQLTransformer lacks an important feature that is present in
the ESQL logicsheet. ESQL allows limiting the number of rows
returned from a database using <esql:max-rows> and skips
some rows using the <esql:skip-rows> tag.
I have implemented something similar for the SQLTransformer. The
patch is very simple and I think doesn't break any of the current
funcionality.
Basically I added two new parameters <limit> and <offset>, with the
obvious meanings and made sure they are respected, if set, in both
the cases of normal queries and stored procedures. If not present,
the behavour of SQLTransformer is not changed.
The patch against Cocoon 2.0.5-dev, with 19 lines of actual code
added and only 2 lines modified is appended below for
flaming/review/discussion. Is there any chance of getting something
similar into Cocoon proper?
	feri.



--- SQLTransformer.java.orig    2003-04-04 10:39:57.000000000 +0200
+++ SQLTransformer.java 2003-04-04 10:43:43.000000000 +0200
@@ -128,6 +128,9 @@
     public static final String MAGIC_STORED_PROCEDURE_ATTRIBUTE = 
"isstoredprocedure";
     public static final String MAGIC_UPDATE_ATTRIBUTE = "isupdate";

+    public static final String MAGIC_LIMIT = "limit";
+    public static final String MAGIC_OFFSET = "offset";
+
     /** The states we are allowed to be in **/
     protected static final int STATE_OUTSIDE = 0;
     protected static final int STATE_INSIDE_EXECUTE_QUERY_ELEMENT = 1;
@@ -275,6 +278,8 @@
             getLogger().debug( "ROW-ELEMENT: " + 
parameters.getParameter( SQLTransformer.MAGIC_ROW_ELEMENT, "row" ) );
             getLogger().debug( "NS-URI: " + parameters.getParameter( 
SQLTransformer.MAGIC_NS_URI_ELEMENT, NAMESPACE ) );
             getLogger().debug( "NS-PREFIX: " + 
parameters.getParameter( SQLTransformer.MAGIC_NS_PREFIX_ELEMENT, "" ) );
+            getLogger().debug( "OFFSET: " + parameters.getParameter( 
SQLTransformer.MAGIC_OFFSET, "0" ) );
+            getLogger().debug( "LIMIT: " + parameters.getParameter( 
SQLTransformer.MAGIC_LIMIT, "0" ) );
         }
    }

@@ -327,7 +332,8 @@
                 attr = new AttributesImpl();
                 if ( !query.isStoredProcedure() ) {
-                    while ( query.next() ) {
+                    query.doOffset();
+                    while ( query.doMore() && query.next() ) {
                         this.start( query.row_name, attr );
                         query.serializeRow(this.manager);
                         if ( index + 1 < queries.size() ) {
@@ -872,6 +878,14 @@
         /** Mapping out parameters - objectModel **/
         protected HashMap outParametersNames = null;
+        /* max number of rows to output */
+        protected int limit = 0;
+
+        /* number of rows to skip */
+        protected int offset = 0;
+
+        protected int count_limit = 0;
+
         protected Query( SQLTransformer transformer, int query_index ) {
             this.transformer = transformer;
             this.query_index = query_index;
@@ -1032,6 +1046,9 @@
         protected void execute() throws SQLException {
             this.rowset_name = properties.getParameter( 
SQLTransformer.MAGIC_DOC_ELEMENT, "rowset" );
             this.row_name = properties.getParameter( 
SQLTransformer.MAGIC_ROW_ELEMENT, "row" );
+            this.offset = Integer.parseInt( properties.getParameter( 
SQLTransformer.MAGIC_OFFSET, "0" ) );
+            this.limit = Integer.parseInt( properties.getParameter( 
SQLTransformer.MAGIC_LIMIT, "0" ) );
+            this.count_limit = 0;

             Enumeration enum = query_parts.elements();
             StringBuffer sb = new StringBuffer();
@@ -1182,6 +1199,15 @@
             query_parts.addElement( object );
         }
+        public boolean doMore() {
+            return limit <= 0 || count_limit++ < limit;
+        }
+
+        public void doOffset() throws SQLException {
+            if( offset > 0 )
+                rs.absolute( offset );
+        }
+
         protected void serializeData(ComponentManager manager,
                                      String           value)
         throws SQLException, SAXException {
@@ -1258,7 +1284,8 @@
                             try {
                                 transformer.start( (String) 
outParametersNames.get( counter ), attr );
                                 ResultSetMetaData md = rs.getMetaData();
-                                while ( rs.next() ) {
+                                doOffset();
+                                while ( doMore() && rs.next() ) {
                                     transformer.start( this.row_name, 
attr );
                                     for ( int i = 1; i <= 
md.getColumnCount(); i++ ) {
                                         transformer.start( 
md.getColumnName( i ).toLowerCase(), attr );