Fixes issue 40547: Check for duplicates in Event Observer
authorNono Carballo <nonofce@gmail.com>
Tue, 14 May 2019 13:09:53 -0400
changeset 36312 9d8fe95b6904
parent 36309 1596542c21af
Fixes issue 40547: Check for duplicates in Event Observer

Prior this fix, duplicate values in m_ch_value were checked in a statement
level trigger, blocking insertions in some flows.

This fix removes trigger and adds logic for check duplicates in an Event
Observer, to avoid the table mutating problem in Oracle.
src-db/database/model/functions/M_CH_VALUE_TRG2.xml
src-db/database/model/triggers/M_CH_VALUE_TRG2.xml
src/org/openbravo/event/CharacteristicValueEventHandler.java
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/functions/M_CH_VALUE_TRG2.xml	Tue May 14 13:09:53 2019 -0400
@@ -0,0 +1,43 @@
+<?xml version="1.0"?>
+  <database name="FUNCTION M_CH_VALUE_TRG2">
+    <function name="M_CH_VALUE_TRG2" type="VARCHAR">
+      <body><![CDATA[/*************************************************************************
+* 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) 2013-2016 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+  v_Count     NUMBER;
+
+BEGIN
+  IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 
+  END IF;
+
+  --check for duplicated names in variant characteristics
+  SELECT count(*)  INTO v_count
+  FROM dual
+  WHERE EXISTS (SELECT 1
+                FROM m_ch_value chv
+                    JOIN m_characteristic ch ON chv.m_characteristic_id = ch.m_characteristic_id AND ch.isvariant = 'Y'
+                GROUP BY chv.name, chv.m_characteristic_id
+                HAVING count(*) > 1);
+
+  IF (v_Count > 0) THEN
+    RAISE_APPLICATION_ERROR(-20000, '@DuplicatedChValueName@');
+  END IF;
+
+IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
+END M_CH_VALUE_TRG2
+]]></body>
+    </function>
+  </database>
--- a/src-db/database/model/triggers/M_CH_VALUE_TRG2.xml	Fri May 17 11:14:19 2019 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,44 +0,0 @@
-<?xml version="1.0"?>
-  <database name="TRIGGER M_CH_VALUE_TRG2">
-    <trigger name="M_CH_VALUE_TRG2" table="M_CH_VALUE" fires="after" insert="true" update="true" delete="false" foreach="statement">
-      <body><![CDATA[
-
-/*************************************************************************
-* 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) 2013-2016 Openbravo SLU
-* All Rights Reserved.
-* Contributor(s):  ______________________________________.
-************************************************************************/
-  v_Count     NUMBER;
-
-BEGIN
-  IF AD_isTriggerEnabled()='N' THEN RETURN;
-  END IF;
-
-  --check for duplicated names in variant characteristics
-  SELECT count(*)  INTO v_count
-  FROM dual
-  WHERE EXISTS (SELECT 1
-                FROM m_ch_value chv
-                    JOIN m_characteristic ch ON chv.m_characteristic_id = ch.m_characteristic_id AND ch.isvariant = 'Y'
-                GROUP BY chv.name, chv.m_characteristic_id
-                HAVING count(*) > 1);
-
-  IF (v_Count > 0) THEN
-    RAISE_APPLICATION_ERROR(-20000, '@DuplicatedChValueName@');
-  END IF;
-
-END M_CH_VALUE_TRG2
-]]></body>
-    </trigger>
-  </database>
--- a/src/org/openbravo/event/CharacteristicValueEventHandler.java	Fri May 17 11:14:19 2019 +0200
+++ b/src/org/openbravo/event/CharacteristicValueEventHandler.java	Tue May 14 13:09:53 2019 -0400
@@ -28,18 +28,22 @@
 import org.hibernate.Session;
 import org.hibernate.query.Query;
 import org.hibernate.resource.transaction.spi.TransactionStatus;
+import org.openbravo.base.exception.OBException;
 import org.openbravo.base.model.Entity;
 import org.openbravo.base.model.ModelProvider;
 import org.openbravo.base.model.Property;
 import org.openbravo.base.secureApp.VariablesSecureApp;
 import org.openbravo.client.kernel.RequestContext;
+import org.openbravo.client.kernel.event.EntityNewEvent;
 import org.openbravo.client.kernel.event.EntityPersistenceEventObserver;
 import org.openbravo.client.kernel.event.EntityUpdateEvent;
 import org.openbravo.client.kernel.event.TransactionBeginEvent;
 import org.openbravo.client.kernel.event.TransactionCompletedEvent;
 import org.openbravo.dal.core.OBContext;
 import org.openbravo.dal.service.OBDal;
+import org.openbravo.erpCommon.utility.OBMessageUtils;
 import org.openbravo.materialmgmt.VariantChDescUpdateProcess;
+import org.openbravo.model.common.plm.Characteristic;
 import org.openbravo.model.common.plm.CharacteristicValue;
 import org.openbravo.scheduling.OBScheduler;
 import org.openbravo.scheduling.ProcessBundle;
@@ -60,11 +64,44 @@
     chvalueUpdated.set(null);
   }
 
+  public void onNew(@Observes EntityNewEvent event) {
+    if (!isValidEvent(event)) {
+      return;
+    }
+    CharacteristicValue characteristicValue = (CharacteristicValue) event.getTargetInstance();
+    checkForDuplicates(characteristicValue);
+  }
+
+  private void checkForDuplicates(CharacteristicValue characteristicValue) {
+    Characteristic characteristic = characteristicValue.getCharacteristic();
+    if (characteristic.isVariant()) {
+      if (existsDuplicateNameInSameCharacteristic(characteristicValue)) {
+        throw new OBException(OBMessageUtils.messageBD("DuplicatedChValueName"));
+      }
+    }
+  }
+
+  private boolean existsDuplicateNameInSameCharacteristic(CharacteristicValue characteristicValue) {
+    //@formatter:off
+    String hql = "select 1 from CharacteristicValue chv join chv.characteristic ch where chv.name = :name and ch.id = :characteristicId";
+    //@formatter:on
+
+    Query<Integer> query = OBDal.getInstance()
+        .getSession()
+        .createQuery(hql, Integer.class)
+        .setParameter("name", characteristicValue.getName())
+        .setParameter("characteristicId", characteristicValue.getCharacteristic().getId());
+
+    query.setMaxResults(1);
+    return !query.list().isEmpty();
+  }
+
   public void onUpdate(@Observes EntityUpdateEvent event) {
     if (!isValidEvent(event)) {
       return;
     }
     final CharacteristicValue chv = (CharacteristicValue) event.getTargetInstance();
+    checkForDuplicates(chv);
     chvalueUpdated.set(chv.getId());
     // Update all product characteristics configurations with updated code of the characteristic.
     // Only when product characteristics is not linked with subset.