details: https://code.openbravo.com/erp/devel/pi/rev/06bb942f0d9a changeset: 22287:06bb942f0d9a user: Stefan Hühner <stefan.huehner <at> openbravo.com> date: Thu Feb 27 15:27:52 2014 +0100 summary: [xsql-scrollable] Refactor Sqlc initClass generation - split out header part into own StringBuffer - prepare to allow builindg part of header only after all the xsql file has been parsed instead of building all after parsing only first method. This allows to include data known only after parsing all the file into the generated header. - Just refactoring no change in generated code. Diffing build/javasqlc before and after this commit show no changes
details: https://code.openbravo.com/erp/devel/pi/rev/bbdfd04c8448 changeset: 22288:bbdfd04c8448 user: Stefan Hühner <stefan.huehner <at> openbravo.com> date: Thu Feb 27 15:31:52 2014 +0100 summary: [xsql-scrollable] Refactor sqlc: extract reading one result row into new method - Extract the code-generation for reading one row of a query result into and instance of the generated class into a helper method. That allows to call that later from multiple place. - Note: Just refactoring zero change in generated code. details: https://code.openbravo.com/erp/devel/pi/rev/d537fb38c3c5 changeset: 22289:d537fb38c3c5 user: Stefan Hühner <stefan.huehner <at> openbravo.com> date: Thu Feb 27 15:38:49 2014 +0100 summary: [xsql-scrollabe] Small cleanup + refactor of ReportGeneralLedger In preparation of converting that report to use scrollable xsql result do the following cleanup + changes to make that easier - Remove many isDebugEnabled calls, as that code is not very hot (so not called many many times) no gain in performance for those calls and make code harder to read. - Remove some unsued variables + a duplicate call to getFamily - Move checks of having both date from & date to non empty to be early in the code and to raise error + stop process there (return). That allow to not add extra indent to following code. - move setting of elementvalueto to be =elementvaluefrom if it was not specified to be earlier. details: https://code.openbravo.com/erp/devel/pi/rev/3df74c8a30f3 changeset: 22290:3df74c8a30f3 user: Stefan Hühner <stefan.huehner <at> openbravo.com> date: Thu Feb 27 20:06:54 2014 +0100 summary: [xsql-scrolling] Implement reading of xsql results using a scrollable interface This implements a new optional feature to the Sqlc codegen for xsql files. It can be activated per SqlMethod by using return="scrollable" instead of the typtical return="multuple". When used the interface for using this SqlMethod changes. Instead of it returning an array of the generated class (i.e. ReportGeneralLedgerData[]) now it returns a single instance of this class. However this class then implements a new interface called 'ScrollableFieldProvider'. This interface allows to read the result row by row without loading all of it into memory avoiding huge memory usage and out of memory problems when processing a lot of result rows. Additionally this adds a new version of the renderJR-method which allows to pass this data directly to jasper allowing streaming of data from xsql up to jasper without loading all of the rows ever into memory. Note: that Sqlc code-generation is completely unchanged for all code not explicitely using the new feature. As now not all results are available at the same tiem code which is looking at or modifying such data before passing to to JasperReports needs to be adapted. Included here is one helper class to implement a common case of checking number of result rows and stopping processing is a limit is exceeded. This is used in several records to comply with row-number restriction on xls export. Common pattern to convert simple jasper call: Before: FieldProvider[] data = SomeDataClass.select (....); renderJR(...., data, .... ); After: ScrollableFieldProfier data = null; try { data= SomeDataclass.select( .... ); renderJR(..., data, ....); } finally { if (data != null) { data.close(); } IMPORTANT NOTE: It is very important to add this finally block which calls close() always as before calling close a connection, transaction, result set for this query are kept open. And all those must be close always (!) The finally block allows to do this easily in case of success and error. details: https://code.openbravo.com/erp/devel/pi/rev/89155988f108 changeset: 22291:89155988f108 user: Stefan Hühner <stefan.huehner <at> openbravo.com> date: Thu Feb 27 20:13:13 2014 +0100 summary: [xsql-scrollabe] Convert ReportGeneralLedger to scrollable xsql This make it possible to run huge dataset with this report without out of memory problems. Changes: - printPageXls: - use scrollable version instead of [] version - add important finally block to call close() always - use .hasData() instead of [].length != 0 - use LimitRowsScrollableFieldProvider utility class instead of manually checking [].length >limit - printPagePdf: - use scrollable version instead of [] version - add finally to call close() always - use .hasData() instead of [].length != 0 - Rewrite manual loop over [] to add data to use new private helper class Addtotals based on AbstractScrollableFieldProviderFilter. This allows to modify row by row as it is processed. The code doing the data modification per row is unchanged compare to the initial version. Just the explicit loop goes away - printPageHtml This function uses the same select but is adding limit/offset so it did not have the memory problem, so no need to rewirte all of the printPageHtml to not have a []. To not need to duplicate all the select method into a scrolling & non-scrolling one we call the scrolling function but then build a FieldProvider[] again when calling the method to avoid rewriting the rest of the printPageHtml function. diffstat: src-core/src/org/openbravo/data/ScrollableFieldProvider.java | 36 + src-core/src/org/openbravo/data/Sqlc.java | 375 +++++++-- src/org/openbravo/base/secureApp/HttpSecureAppServlet.java | 50 +- src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java | 277 ++++-- src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql | 4 +- src/org/openbravo/erpCommon/utility/AbstractScrollableFieldProviderFilter.java | 64 + src/org/openbravo/erpCommon/utility/JRFieldProviderDataSource.java | 29 +- src/org/openbravo/erpCommon/utility/JRScrollableFieldProviderDataSource.java | 60 + src/org/openbravo/erpCommon/utility/LimitRowsScrollableFieldProviderFilter.java | 28 + 9 files changed, 710 insertions(+), 213 deletions(-) diffs (truncated from 1339 to 300 lines): diff -r 0a192b614081 -r 89155988f108 src-core/src/org/openbravo/data/ScrollableFieldProvider.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-core/src/org/openbravo/data/ScrollableFieldProvider.java Thu Feb 27 20:13:13 2014 +0100 @@ -0,0 +1,36 @@ +/* + ************************************************************************************ + * Copyright (C) 2013 Openbravo S.L.U. + * Licensed under the Apache Software License version 2.0 + * You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 + * Unless required by applicable law or agreed to in writing, software distributed + * under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR + * CONDITIONS OF ANY KIND, either express or implied. See the License for the + * specific language governing permissions and limitations under the License. + ************************************************************************************ + */ +package org.openbravo.data; + +import javax.servlet.ServletException; + +/** + * Interface implemented by java code generated for xsql-files if any SqlMethod defined is using + * return="scrollable" + * + * Generated classes implementing this allow reading the result row by row without the need to load + * all the potentially big dataset all at once into memory. + * + * @author huehner + * + */ +public interface ScrollableFieldProvider { + + public boolean hasData(); + + public boolean next() throws ServletException; + + public FieldProvider get() throws ServletException; + + public void close(); + +} diff -r 0a192b614081 -r 89155988f108 src-core/src/org/openbravo/data/Sqlc.java --- a/src-core/src/org/openbravo/data/Sqlc.java Thu Feb 27 22:54:19 2014 +0530 +++ b/src-core/src/org/openbravo/data/Sqlc.java Thu Feb 27 20:13:13 2014 +0100 @@ -71,7 +71,11 @@ int numCols; boolean first = true; OutputStreamWriter out; - StringBuffer out1; // to be fixed: out1 and out2 are two auxiliar buffers... + // out1 contains header part of the class up to the import statements + StringBuffer out1; + // out3 contains header starting from import just to before the SqlMethods start + StringBuffer out3; + // out2 contains rest of the file (all SqlMethods + up to the end) StringBuffer out2; PrintWriter printWriterTxt; Parameter parameterSql; @@ -89,6 +93,8 @@ static Logger log4j = Logger.getLogger(Sqlc.class); // log4j private static boolean includeQueryTimeOut; + private List<String> scrollableFunctionNames = new ArrayList<String>(); + private Sqlc() { init(); } @@ -100,6 +106,7 @@ sqlcPackage = null; strComments = null; sqlcAccessModifier = ""; + scrollableFunctionNames = new ArrayList<String>(); } public static void main(String argv[]) throws Exception { @@ -326,6 +333,7 @@ final FileOutputStream resultsFile = new FileOutputStream(fileJava); sqlc.out = new OutputStreamWriter(resultsFile, "UTF-8"); sqlc.out1 = new StringBuffer(); + sqlc.out3 = new StringBuffer(); sqlc.out2 = new StringBuffer(); /* * FileWriter resultsFile = new FileWriter(fileJava); sqlc.out = new @@ -362,7 +370,12 @@ sqlc.out1.append("import org.openbravo.database.RDBMSIndependent;\n"); sqlc.out1.append("import org.openbravo.exception.*;\n"); } + + // after all functions have been parsed, finish building class header + sqlc.printInitClass2(); + sqlc.out.write(sqlc.out1.toString()); + sqlc.out.write(sqlc.out3.toString()); sqlc.out.write(sqlc.out2.toString()); sqlc.out.flush(); sqlc.importJavaUtil = false; @@ -419,6 +432,9 @@ || sql.sqlReturn.equalsIgnoreCase("DATE") || sql.sqlReturn.equalsIgnoreCase("SINGLE") || sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) { sql.executeType = "executeQuery"; + } else if (sql.sqlReturn.equalsIgnoreCase("SCROLLABLE")) { + sql.executeType = "executeQuery"; + scrollableFunctionNames.add(sql.sqlName); } else if (sql.sqlReturn.equalsIgnoreCase("ROWCOUNT") || sql.sqlReturn.equalsIgnoreCase("SEQUENCE")) { sql.executeType = "executeUpdate"; @@ -763,56 +779,56 @@ } if (sql.sqlImport != null) { - out2.append("import " + sql.sqlImport + ";\n"); + out3.append("import " + sql.sqlImport + ";\n"); } - out2.append("\n"); + out3.append("\n"); final String[] strCommentsVector = stringToVector(strComments, false); for (int i = 0; i < strCommentsVector.length; i++) { if (i == 0) { - out2.append("/**\n" + strCommentsVector[i] + "\n"); + out3.append("/**\n" + strCommentsVector[i] + "\n"); } else { - out2.append(" *" + strCommentsVector[i] + "\n"); + out3.append(" *" + strCommentsVector[i] + "\n"); } if (i == strCommentsVector.length - 1) { - out2.append(" */\n"); + out3.append(" */\n"); } } if (!javaFileName.equals(sqlcName)) throw new IOException("File name for xsql class " + javaFileName + " is different than the class name defined inside the file: " + sqlcName); if (sqlcAccessModifier.length() > 0) { - out2.append(sqlcAccessModifier); - out2.append(" "); + out3.append(sqlcAccessModifier); + out3.append(" "); } - out2.append("class " + sqlcName + " implements FieldProvider {\n"); - out2.append("static Logger log4j = Logger.getLogger(" + sqlcName + ".class);\n"); + out3.append("class " + sqlcName + " implements FieldProvider {\n"); + out3.append("static Logger log4j = Logger.getLogger(" + sqlcName + ".class);\n"); try { // Display column headings if (log4j.isDebugEnabled()) log4j.debug("Number of columns: " + numCols); - out2.append(" private String InitRecordNumber=\"0\";\n"); + out3.append(" private String InitRecordNumber=\"0\";\n"); for (int i = 1; i <= numCols; i++) { - out2.append(" public String "); - out2.append(TransformaNombreColumna(rsmd.getColumnLabel(i))); - out2.append(";\n"); + out3.append(" public String "); + out3.append(TransformaNombreColumna(rsmd.getColumnLabel(i))); + out3.append(";\n"); } for (final Enumeration<Object> e = sql.vecFieldAdded.elements(); e.hasMoreElements();) { final FieldAdded fieldAdded = (FieldAdded) e.nextElement(); - out2.append(" public String "); - out2.append(fieldAdded.strName); - out2.append(";\n"); + out3.append(" public String "); + out3.append(fieldAdded.strName); + out3.append(";\n"); } } catch (final SQLException e) { log4j.error("SQL Exception error:" + e); } - out2.append("\n"); - out2.append(" public String getInitRecordNumber() {\n"); - out2.append(" return InitRecordNumber;\n"); - out2.append(" }\n"); + out3.append("\n"); + out3.append(" public String getInitRecordNumber() {\n"); + out3.append(" return InitRecordNumber;\n"); + out3.append(" }\n"); // the getField function - out2.append("\n"); - out2.append(" public String getField(String fieldName) {\n"); + out3.append("\n"); + out3.append(" public String getField(String fieldName) {\n"); try { // Display column headings if (log4j.isDebugEnabled()) @@ -821,32 +837,129 @@ final String columnLabel = rsmd.getColumnLabel(i); final String transformedColumnLabel = TransformaNombreColumna(columnLabel); if (i == 1) { - out2.append(" if "); + out3.append(" if "); } else { - out2.append(" else if "); + out3.append(" else if "); } - out2.append("(fieldName.equalsIgnoreCase(\""); - out2.append(columnLabel); + out3.append("(fieldName.equalsIgnoreCase(\""); + out3.append(columnLabel); if (!columnLabel.equalsIgnoreCase(transformedColumnLabel)) - out2.append("\") || fieldName.equals(\"" + transformedColumnLabel); - out2.append("\"))\n"); - out2.append(" return " + transformedColumnLabel + ";\n"); + out3.append("\") || fieldName.equals(\"" + transformedColumnLabel); + out3.append("\"))\n"); + out3.append(" return " + transformedColumnLabel + ";\n"); } for (final Enumeration<Object> e = sql.vecFieldAdded.elements(); e.hasMoreElements();) { final FieldAdded fieldAdded = (FieldAdded) e.nextElement(); - out2.append(" else if "); - out2.append("(fieldName.equals(\""); - out2.append(fieldAdded.strName + "\"))\n"); - out2.append(" return " + fieldAdded.strName + ";\n"); + out3.append(" else if "); + out3.append("(fieldName.equals(\""); + out3.append(fieldAdded.strName + "\"))\n"); + out3.append(" return " + fieldAdded.strName + ";\n"); } } catch (final SQLException e) { log4j.error("SQL Exception error:" + e); } - out2.append(" else {\n"); - out2.append(" log4j.debug(\"Field does not exist: \" + fieldName);\n"); - out2.append(" return null;\n"); - out2.append(" }\n"); - out2.append(" }\n"); + out3.append(" else {\n"); + out3.append(" log4j.debug(\"Field does not exist: \" + fieldName);\n"); + out3.append(" return null;\n"); + out3.append(" }\n"); + out3.append(" }\n"); + } + + /* + * Write 2nd part of class init, to be called after all SqlMethod have been parsed. This allows to + * modify class header easily depending on info from all SqlMethod's + */ + private void printInitClass2() throws IOException { + + // the following emits the common code needed for scrollable results if there are any + // SqlMethod's using them + if (!scrollableFunctionNames.isEmpty()) { + out1.append("import org.openbravo.data.ScrollableFieldProvider;\n"); + + // modify implements + String searchString = "implements FieldProvider"; + + // TODO: improve this when code to built header string fields based on all SqlMethod (avoiding + // need for dummy methods) is added + int offset = out3.indexOf(searchString); + out3.delete(offset, offset + searchString.length()); + out3.insert(offset, "implements FieldProvider, ScrollableFieldProvider"); + + // add needed instance variables & functions + String toInsert = "\n"; + toInsert += " private String scrollableGetter;\n"; + toInsert += " private long countRecord;\n"; + toInsert += " private ResultSet result;\n"; + toInsert += " private boolean hasData;\n"; + toInsert += " private ConnectionProvider internalConnProvider;\n"; + toInsert += " private Connection internalConnection;\n"; + toInsert += " private boolean errorOcurred;\n"; + toInsert += " \n"; + toInsert += " @Override\n"; + toInsert += " public boolean hasData() {\n"; + toInsert += " return hasData;\n"; + toInsert += " }\n"; + toInsert += "\n"; + toInsert += " @Override\n"; + toInsert += " public boolean next() throws ServletException {\n"; + toInsert += " try {\n"; + toInsert += " if (result.next()) {\n"; + toInsert += " countRecord++;\n"; + toInsert += " return true;\n"; + toInsert += " }\n"; + toInsert += " return false;\n"; + toInsert += " } catch(SQLException e){\n"; + toInsert += " errorOcurred = true;\n"; + toInsert += " log4j.error(\"Error calling jdbc next()\", e);\n"; + toInsert += " throw new ServletException(\"@CODE=\" + Integer.toString(e.getErrorCode()) + \"@\" + e.getMessage());\n"; + toInsert += " }\n"; + toInsert += " }\n"; + toInsert += "\n"; + + toInsert += " @Override\n"; + toInsert += " public " + sqlcName + " get() throws ServletException {\n"; + toInsert += " try {\n"; + boolean isFirst = true; + for (String name : scrollableFunctionNames) { + if (isFirst) { + toInsert += " if (\"" + name + "\".equals(scrollableGetter)) {\n"; + isFirst = false; + } else { + toInsert += " } else if (\"" + name + "\".equals(scrollableGetter)) {\n"; + } + toInsert += " return get" + name + "();\n"; + } + toInsert += " } else {\n"; + toInsert += " throw new ServletException(\"getNext() called without calling any scrollable select first\");\n"; + toInsert += " }\n"; + toInsert += " } catch(SQLException e){\n"; + toInsert += " errorOcurred = true;\n"; ------------------------------------------------------------------------------ Flow-based real-time traffic analytics software. Cisco certified tool. Monitor traffic, SLAs, QoS, Medianet, WAAS etc. with NetFlow Analyzer Customize your own dashboards, set traffic alerts and generate reports. Network behavioral analysis & security monitoring. All-in-one tool. http://pubads.g.doubleclick.net/gampad/clk?id=126839071&iu=/4140/ostg.clktrk _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
