Projects:Paged Datagrid/How to convert an existing Selector
Contents |
Introduction
This page describes the needed changes in order to convert an existing selector to leverage the new paging mechanism introduced in the Paged Datagrid project.
The conversion of an selector in optional. The changes done to the common code are designed in a way that the decision to use them can be done on a per selector basis.
Changes needed
To convert an existing selector changes in three different files are needed:
- xsql file containing all the SQL queries
- html to add the new UI artifacts
- java file to add the new logic
The example changes shown below are based on the conversion of the Product selector which is shipped in the core module and are shown in the common format as produced by the diff-tool. However thee diff-files have been edited to shorten them to be presentable more easily.
Changes: xsql-file
All xsql files of the selectors do contain at minimum two methods. One method (i.e. called countRows) to count all the rows which match the current filter criteria and a another one (i.e. called select) which selects the rows which match the current filter criteria and are inside some visible window using limit/offset in postgres and rownum on oracle.
The countRows method needs to be changed so it supports these queries including limit/offset or rownum. This is needed to be able to count the rows which are in i.e. the second page of the datagrid.
The technical change do add this is different on oracle/postgres and consists of a number of optional parameters which are then provided in the calling java code depending on the database system (oracle or postgres) used at runtime.
<SqlMethod name="countRows" type="preparedStatement" return="String">
<Sql> <![CDATA[
- SELECT count(*) as value
- FROM M_Product p, M_ProductPrice pr
+ SELECT count(*) AS value FROM ( SELECT '0' AS rn1, B.* FROM (
+ SELECT 1 FROM M_Product p, M_ProductPrice pr
WHERE p.M_Product_ID=pr.M_Product_ID
AND p.IsSummary='N'
AND p.IsActive='Y'
AND pr.IsActive='Y'
AND pr.M_PriceList_Version_ID = ?
AND p.AD_Client_ID IN ('1')
- AND p.AD_Org_ID IN ('1')
+ AND p.AD_Org_ID IN ('1')
+ AND 1=1
+ ) B
+ ) A
]]></Sql>
+ <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
<Parameter name="key" ignoreValue="%" optional="true" after="WHERE "><![CDATA[ UPPER(p.Value) LIKE UPPER(?) AND ]]></Parameter>
<Parameter name="name" ignoreValue="%" optional="true" after="WHERE "><![CDATA[ UPPER(p.Name) LIKE UPPER(?) AND ]]></Parameter>
<Parameter name="priceListVersion"/>
<Parameter name="adUserClient" type="replace" optional="true" after="p.AD_Client_ID IN (" text="'1'"/>
<Parameter name="adUserOrg" type="replace" optional="true" after="p.AD_Org_ID IN (" text="'1'"/>
+ <Parameter name="pgLimit" type="argument" optional="true" after="AND 1=1"><![CDATA[LIMIT ]]></Parameter>
+ <Parameter name="oraLimit1" type="argument" optional="true" after=") B"><![CDATA[ WHERE ROWNUM <= ]]></Parameter>
+ <Parameter name="oraLimit2" type="argument" optional="true" after=") A "><![CDATA[WHERE RN1 BETWEEN ]]></Parameter>
</SqlMethod>
Changes: java-file
The changes needed in the java servlet of a selector can be grouped into these areas:
- maintain current backendPage in the session per user (Naming-Scheme: <selectorName>.currentPage, Example: Product.currentPage)
- sent backendPageSize to the client as part of structure response
- adjust backendPage if a user changes the visible page
- calculate absolute server-side offset based on relative client offset and backendPage number
- send current backend page with the data response
The server-side backendPage number (stored in the users' session) needs to be removed, when the filters are cleared as it is only relevant to the current filters.
private void removePageSessionVariables(VariablesSecureApp vars) { + vars.removeSessionValue("Product.currentPage"); }
The number of rows per backendPage is a server-side constant, but need to be available to the client side (javascript) code. To make it available it is transferred with the response to the STRUCTURE request.
private void printGridStructure( ... + xmlDocument.setParameter("backendPageSize", String.valueOf(TableSQLData.maxRowsPerGridPage)); }
When the client-side code is asking for some rows with the standard DATA request the backendPage value needs to be read and the relative client-side offsets (relative to the start of a backendPage) need to be transformed into absolute server-side offsets. Additionally if the user clicked on a change-page link the backendPage needs to be incremented/decremented and a reexecution of the countQuery needs to be triggered to count the rows in the new changed backendPage. Finally the current backendPage needs to be sent to the client with the result returning the rows.
private void printGridData( ... + int page = 0; ... // build sql orderBy clause String strOrderBy = SelectorUtility.buildOrderByClause(strOrderCols, strOrderDirs); + page = TableSQLData.calcAndGetBackendPage(vars, "Product.currentPage"); + if (vars.getStringParameter("movePage", "").length() > 0) { + // on movePage action force executing countRows again + strNewFilter = ""; + } + int oldOffset = offset; + offset = (page * TableSQLData.maxRowsPerGridPage) + offset; + log4j.debug("relativeOffset: " + oldOffset + " absoluteOffset: " + offset); + + // New filter or first load if (strNewFilter.equals("1") || strNewFilter.equals("")) { - // New filter or first load - strNumRows = ProductData.countRows(this, strKey, strName, strPriceListVersion, Utility - .getContext(this, vars, "#User_Client", "Product"), Utility.getSelectorOrgs(this, - vars, strOrg)); + + // calculate params for sql limit/offset or rownum clause + String rownum = "0", oraLimit1 = null, oraLimit2 = null, pgLimit = null; + if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) { + oraLimit1 = String.valueOf(offset + TableSQLData.maxRowsPerGridPage); + oraLimit2 = (offset + 1) + " AND " + oraLimit1; + rownum = "ROWNUM"; + } else { + pgLimit = TableSQLData.maxRowsPerGridPage + " OFFSET " + offset; + } + + strNumRows = ProductData.countRows(this, rownum, strKey, strName, strPriceListVersion, + Utility.getContext(this, vars, "#User_Client", "Product"), Utility.getSelectorOrgs( + this, vars, strOrg), pgLimit, oraLimit1, oraLimit2); vars.setSessionValue("Product.numrows", strNumRows); ... strRowsData.append(" </status>\n"); - strRowsData.append(" <rows numRows=\"").append(strNumRows).append("\">\n"); + strRowsData.append(" <rows numRows=\"").append(strNumRows).append( + "\" backendPage=\"" + page + "\">\n");
Changes: html-file
The changes needed in the html-file can be grouped into three areas:
- new javascript import to allow access to the messaging system
- a javascript method to update data/visibility for the new UI artifacts
- add UI artifact for the new record position / count widget
To be able to get the translated labels for the new change page links an include to messages.js needs to be added (only if not yet present):
<script language="JavaScript" src="../../../../../web/js/utils.js" type="text/javascript"></script> +<script language="JavaScript" src="../../../../../web/js/messages.js" type="text/javascript"></script>
Then two new javascript functions are needed which manage the status/visibility update of the new UI artifact. The gridMovePage is just a small wrapper function as the id attribute of the grid object could have a different name per selector. The 'updateHeader' function will be called on each scroll and updates the new row position widget and the visibility of the previous page and next page links/buttons.
+function gridMovePage(direction) { + dijit.byId('grid').gridMovePage(direction); + return true; +} + +function updateHeader(liveGrid, offset) { + var backendPageSize = liveGrid.getBackendPageSize(); + var currPageStart = (liveGrid.metaData.getBackendPage()*backendPageSize); + var pageFull = (liveGrid.metaData.getTotalRows() >= backendPageSize); + var firstPage = (liveGrid.metaData.getBackendPage() == 0); + var res = "<nobr class='DataGrid_Popup_text_bookmark'>"; + if (!firstPage) { + res = res + "<a href='#' onclick='gridMovePage(\"PREVIOUSPAGE\"); setWindowElementFocus(\"grid_table_dummy_input\",\"id\");' class='DataGrid_Popup_text_pagerange' id='prevPage_link'>" + getMessage("GridPreviousPage") + " " + backendPageSize +"</a> | "; + } + res = res + ((liveGrid.visibleRows>0)?(currPageStart+offset+1):0) + " - "+ (currPageStart+offset+liveGrid.visibleRows) + " / " + (currPageStart+liveGrid.metaData.getTotalRows()); + if (pageFull) { + res = res + " | <a href='#' onclick='gridMovePage(\"NEXTPAGE\"); setWindowElementFocus(\"grid_table_dummy_input\",\"id\");' class='DataGrid_Popup_text_pagerange' id='nextPage_link'>" + getMessage("GridNextPage") + " " + backendPageSize +"</a>"; + } + res = res + "</nobr>"; + liveGrid.setGridPaging(!firstPage,pageFull); + dojo.byId('bookmark').innerHTML = res; +} </script>
The last change is the addition of the new widget which shows the relative position of the currently visible rows and the total number of rows (in the current page). Note: This is the same widget which is already present in the Toolbar in of the grid view in all generated window. The html for this widget needs to be added just before table tag with the id="grid_sample" which is present for all selectors.
+ + <table width="100%" cellspacing="0" cellpadding="0" border="0" id="grid_bookmark"> + <tr> + <td class="DataGrid_Popup_text_container"> + <div id="bookmark"> + <nobr class='DataGrid_Popup_text_bookmark'> </nobr> + </div> + </td> + </tr> + </table> + <table cellspacing="0" cellpadding="0" width="10px" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;" id="grid_sample">
Additional changes for Multiple selectors
If a selector which can be used to select multiple records at once is to be converted one additional change is needed to its java code. The multiple selectors do have one additional methods in its servlet which is called to get the list of id's inside a specific record offset-range. This record offset-range needs to be converted from client-side relative offsets to server-side absolute offsets (by simply adding backendPageSize+backendPage) After this adjusting of the offset the rest of the processing can be used unchanged.
private void printGridDataSelectedRows( ... ... // build sql orderBy clause String strOrderBy = SelectorUtility.buildOrderByClause(strOrderCols, strOrderDirs); + // get current page + String strPage = vars.getSessionValue("ProductMultiple|currentPage", "0"); + int page = Integer.parseInt(strPage); + int oldMinOffset = minOffset; + int oldMaxOffset = maxOffset; + minOffset = (page * TableSQLData.maxRowsPerGridPage) + minOffset; + maxOffset = (page * TableSQLData.maxRowsPerGridPage) + maxOffset; + log4j.debug("relativeMinOffset: " + oldMinOffset + " absoluteMinOffset: " + minOffset); + log4j.debug("relativeMaxOffset: " + oldMaxOffset + " absoluteMaxOffset: " + maxOffset); }
How to check for correct the behavior after the conversion
If a converted selector is used with a number of rows which is below the backendPageSize (10000) then only two user visible changes can be noticed.
- The addition of the row-position widget just on top of the grid
- On a resort (click on a column header) the selected row is moved to the first row
The new functionality can be tested in two ways:
- Create enough data in the underlying table (i.e. 25000 rows to have a full first page, one full intermediate page, and a partically filled last page)
- Or change the org.openbravo.erpCommon.utility.TableSQLData.maxRowsPerGridPage constant to a lower value to reduce the pagesize and be able to test with less rows present.
Partial list of use cases to test
Assumption: Enough rows are present to use the new paging behavior
- On the first page a next page link and a next page button are shown
- On an intermediate page both previous and next page links/buttons are shown
- On the last page only the previous page link/button are shown
- The row offset calculation is performed correctly (no holes or duplicate rows on a row change)
- On a change of sort order (click on a column header) the selected rows and visible page are both moved to row 1 in page 1
- The row number on the left side show the correct absolute row number
- On a change of the filter and click on search the selected rows is positioned to row 1 of page 1
- Performance: When scrolling inside a backendPage only the getRows sql-query is executed in the backend and not the count query (i.e. check in debugger or with debug output)
- Performance: When switching page or changing the filter both the count and the getRows queries are executed (i.e. check in debugger or with debug output)
- Portability: The changed xsql queries work fine on both postgresql and oracle