fixed bug 29127: PR14Q3 -> PR14Q4 update can fail activating FKs
authorAsier Lostalé <asier.lostale@openbravo.com>
Thu, 05 Mar 2015 14:18:04 +0100
changeset 26129 9a12a6271cdd
parent 26128 a410732b5a7f
child 26131 ec6a5bf6a16e
fixed bug 29127: PR14Q3 -> PR14Q4 update can fail activating FKs

Added build validation to prevent this error.

This build validation prevents issue #29127 by deleting rows in ad_process_run for executions of
CostingRuleProcess which is removed but due to issue #29142 update database process is not able
to properly handle.

It is a temporary workaround and it should be removed one #29142 is resolved.

It should have been implemented as ModuleScript because it is repairing data, but because
ad_process_run and ad_process_request tables are recreated when updating from PR14Q3 to PR14Q4,
module script would be executed without indexes nor DB statistics which might result, depending
on the data volumes in these table, in slow executions due to poor query execution plans.
Exceptionally, implementing it as build validation to be executed before actual database update
allowing better plans.
src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/RemoveCostingRuleProcessExecutions.class
src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/RemoveCostingRuleProcessExecutionsData.class
src-util/buildvalidation/src/org/openbravo/buildvalidation/RemoveCostingRuleProcessExecutions.java
src-util/buildvalidation/src/org/openbravo/buildvalidation/RemoveCostingRuleProcessExecutions_data.xsql
Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/RemoveCostingRuleProcessExecutions.class has changed
Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/RemoveCostingRuleProcessExecutionsData.class has changed
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/RemoveCostingRuleProcessExecutions.java	Thu Mar 05 14:18:04 2015 +0100
@@ -0,0 +1,67 @@
+/*
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License.
+ * The Original Code is Openbravo ERP.
+ * The Initial Developer of the Original Code is Openbravo SLU
+ * All portions are Copyright (C) 2015 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+ */
+
+package org.openbravo.buildvalidation;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.log4j.Logger;
+import org.openbravo.database.ConnectionProvider;
+
+/**
+ * This build validation prevents issue #29127 by deleting rows in ad_process_run for executions of
+ * CostingRuleProcess which is removed but due to issue #29142 update database process is not able
+ * to properly handle.
+ * 
+ * It is a temporary workaround and it should be removed one #29142 is resolved.
+ * 
+ * It should have been implemented as ModuleScript because it is repairing data, but because
+ * ad_process_run and ad_process_request tables are recreated when updating from PR14Q3 to PR14Q4,
+ * module script would be executed without indexes nor DB statistics which might result, depending
+ * on the data volumes in these table, in slow executions due to poor query execution plans.
+ * Exceptionally, implementing it as build validation to be executed before actual database update
+ * allowing better plans.
+ * 
+ * @author alostale
+ *
+ */
+public class RemoveCostingRuleProcessExecutions extends BuildValidation {
+  private static Logger log = Logger.getLogger(RemoveCostingRuleProcessExecutions.class);
+
+  @Override
+  public List<String> execute() {
+    ConnectionProvider cp = getConnectionProvider();
+
+    try {
+      // CostingRuleProcess is removed in PR14Q4, check if it is present to remove data if so
+      if (RemoveCostingRuleProcessExecutionsData.isCostingRuleProcessPresent(cp)) {
+        int deletedRows = RemoveCostingRuleProcessExecutionsData.removeCostingRuleExecutions(cp);
+        if (deletedRows > 0) {
+          log.info("Deleted " + deletedRows
+              + " rows in ad_process_run for execution of CostingRuleProcess");
+        }
+      }
+    } catch (Exception e) {
+      handleError(e);
+    }
+
+    return new ArrayList<String>();
+  }
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/RemoveCostingRuleProcessExecutions_data.xsql	Thu Mar 05 14:18:04 2015 +0100
@@ -0,0 +1,41 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!--
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License.
+ * The Original Code is Openbravo ERP.
+ * The Initial Developer of the Original Code is Openbravo SLU
+ * All portions are Copyright (C) 2015 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+-->
+
+
+<SqlClass name="RemoveCostingRuleProcessExecutionsData" package="org.openbravo.buildvalidation">
+   <SqlMethod name="isCostingRuleProcessPresent" type="preparedStatement" return="boolean">
+      <Sql><![CDATA[
+             select count(*) as present
+             from ad_process
+             where ad_process_id = 'A269DCA4DE114E438695B66E166999B4'
+      ]]></Sql>
+   </SqlMethod>
+   
+   <SqlMethod name="removeCostingRuleExecutions" type="preparedStatement" return="rowcount">
+    <Sql><![CDATA[
+      delete from ad_process_run pr
+       where exists (select 1 
+                      from ad_process_request r 
+                     where r.ad_process_request_id = pr.ad_process_request_id
+                       and r.ad_process_id = 'A269DCA4DE114E438695B66E166999B4')
+      ]]>
+    </Sql>
+  </SqlMethod>
+</SqlClass>