Fixes issue 28939
authorEduardo Argal Guibert <eduardo.argal@openbravo.com>
Fri, 06 Mar 2015 17:03:04 +0100
changeset 26141 b9fc7e8e60ae
parent 26140 e20da585bb7e
child 26142 7a7adf930de6
Fixes issue 28939
Perfromance problem running module script in environments with large amount of transactions (invoices and orders).
New index is added in postgres environments when this is not present. Problem comes from table recreation, so impacts those who update from a version that requires invoice table rebuilding.
src-util/modulescript/build/classes/org/openbravo/modulescript/UpdateBpPaymentLine.class
src-util/modulescript/build/classes/org/openbravo/modulescript/UpdateBpPaymentLineData.class
src-util/modulescript/src/org/openbravo/modulescript/UpdateBpPaymentLine.java
src-util/modulescript/src/org/openbravo/modulescript/UpdateBpPaymentLine_data.xsql
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdateBpPaymentLine.class has changed
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdateBpPaymentLineData.class has changed
--- a/src-util/modulescript/src/org/openbravo/modulescript/UpdateBpPaymentLine.java	Fri Mar 06 16:52:17 2015 +0100
+++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdateBpPaymentLine.java	Fri Mar 06 17:03:04 2015 +0100
@@ -19,23 +19,53 @@
 package org.openbravo.modulescript;
 
 import org.apache.log4j.Logger;
+import org.openbravo.data.UtilSql;
 import org.openbravo.database.ConnectionProvider;
 import org.openbravo.modulescript.ModuleScript;
+import java.io.FileInputStream;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+
+import javax.servlet.ServletException;
+/**
+ * This module script has ben created due to issue 28939. 
+ * Due to some performance issues this module script sometimes 
+ * has to create a primary key when it is missing. 
+ * This should never be done but in really strange situations.
+ *
+ */
 
 public class UpdateBpPaymentLine extends ModuleScript {
-
   private static final Logger log4j = Logger.getLogger(UpdateBpPaymentLine.class);
 
   @Override
   public void execute() {
     try {
       ConnectionProvider cp = getConnectionProvider();
-      
       boolean executed = UpdateBpPaymentLineData.isModuleScriptExecuted(cp);
       if (!executed) {
-    	int count = 0;
-        count = UpdateBpPaymentLineData.updateBpPaymentLineInvoice(cp);
-        count += UpdateBpPaymentLineData.updateBpPaymentLineOrder(cp);
+        String strRDBMS = cp.getRDBMS();
+        boolean missingIndex =  false;
+        int count = 0;
+        try{
+          if (strRDBMS.equalsIgnoreCase("POSTGRE")) {
+            missingIndex = !invoiceIndexExists(cp);
+            if(missingIndex){
+              UpdateBpPaymentLineData.createInvoiceIndex(cp);
+              UpdateBpPaymentLineData.analyzeInvoice(cp);
+            }
+          }
+          count = UpdateBpPaymentLineData.updateBpPaymentLineInvoice(cp);
+          count += UpdateBpPaymentLineData.updateBpPaymentLineOrder(cp);
+        }finally{
+          if (strRDBMS.equalsIgnoreCase("POSTGRE")) {
+            if(missingIndex){
+              UpdateBpPaymentLineData.dropInvoiceIndex(cp);
+            }
+          }
+        }
         if (count > 0)
           log4j.info("Updated " + count + " Payment Scheduled Details.");
         UpdateBpPaymentLineData.createPreference(cp);
@@ -44,5 +74,42 @@
       handleError(e);
     }
   }
+  private static boolean invoiceIndexExists(ConnectionProvider connectionProvider)    throws ServletException {
+    String strSql = "";
+    strSql = strSql + 
+      "        select" +
+      "            i.relname as index_name" +
+      "        from" +
+      "            pg_class i," +
+      "            pg_index ix" +
+      "        where i.oid = ix.indexrelid" +
+      "            and i.relname = 'c_invoice_key'    ";
 
+    ResultSet result;
+    boolean boolReturn = false;
+    PreparedStatement st = null;
+
+    try {
+    st = connectionProvider.getPreparedStatement(strSql);
+
+      result = st.executeQuery();
+      if(result.next()) {
+        boolReturn = !UtilSql.getValue(result, "index_name").equals("0");
+      }
+      result.close();
+    } catch(SQLException e){
+      log4j.error("SQL error in query: " + strSql + "Exception:"+ e);
+      throw new ServletException("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
+    } catch(Exception ex){
+      log4j.error("Exception in query: " + strSql + "Exception:"+ ex);
+      throw new ServletException("@CODE=@" + ex.getMessage());
+    } finally {
+      try {
+        connectionProvider.releasePreparedStatement(st);
+      } catch(Exception ignore){
+        ignore.printStackTrace();
+      }
+    }
+    return(boolReturn);
+  }
 }
\ No newline at end of file
--- a/src-util/modulescript/src/org/openbravo/modulescript/UpdateBpPaymentLine_data.xsql	Fri Mar 06 16:52:17 2015 +0100
+++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdateBpPaymentLine_data.xsql	Fri Mar 06 17:03:04 2015 +0100
@@ -74,4 +74,28 @@
       ]]>
     </Sql>
   </SqlMethod>
+  <SqlMethod name="createInvoiceIndex" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        alter table c_invoice add constraint c_invoice_key primary key (c_invoice_id)    
+      ]]>
+    </Sql>
+  </SqlMethod>
+  <SqlMethod name="dropInvoiceIndex" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        alter table c_invoice drop constraint c_invoice_key cascade    
+      ]]>
+    </Sql>
+  </SqlMethod>
+  <SqlMethod name="analyzeInvoice" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        ANALYZE c_invoice  
+      ]]>
+    </Sql>
+  </SqlMethod>
 </SqlClass>
\ No newline at end of file