Fixes issue 41977: Allows subqueries in Log Clean Up Configuration hql clause
authorCristian Berner <cristian.berner@openbravo.com>
Wed, 20 Nov 2019 17:29:27 +0100
changeset 36841 f20c0d75e5e4
parent 36840 ebfdf075a6f7
child 36842 c47b63fff577
Fixes issue 41977: Allows subqueries in Log Clean Up Configuration hql clause

A new column and field has been added to OBULOG_CONFIG and Log Clean Up Configuration respectively.
This "Entity Alias" column and field sets the alias that will be used to reference the main table in the hql where clause. By default it is empty.
modules/org.openbravo.utility.cleanup.log/src-db/database/model/tables/OBULOG_CONFIG.xml
modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_COLUMN.xml
modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_ELEMENT.xml
modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_FIELD.xml
modules/org.openbravo.utility.cleanup.log/src/org/openbravo/utility/cleanup/log/CleanEntity.java
--- a/modules/org.openbravo.utility.cleanup.log/src-db/database/model/tables/OBULOG_CONFIG.xml	Thu Nov 28 10:27:46 2019 +0100
+++ b/modules/org.openbravo.utility.cleanup.log/src-db/database/model/tables/OBULOG_CONFIG.xml	Wed Nov 20 17:29:27 2019 +0100
@@ -53,6 +53,10 @@
         <default><![CDATA[N]]></default>
         <onCreateDefault><![CDATA['N']]></onCreateDefault>
       </column>
+      <column name="ENTITY_ALIAS" primaryKey="false" required="false" type="VARCHAR" size="60" autoIncrement="false">
+        <default/>
+        <onCreateDefault/>
+      </column>
       <foreign-key foreignTable="AD_CLIENT" name="OBULOG_CONFIG_CLIENT">
         <reference local="AD_CLIENT_ID" foreign="AD_CLIENT_ID"/>
       </foreign-key>
--- a/modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_COLUMN.xml	Thu Nov 28 10:27:46 2019 +0100
+++ b/modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_COLUMN.xml	Wed Nov 20 17:29:27 2019 +0100
@@ -202,6 +202,45 @@
 <!--34F286EB36B44D30B48C32C65BF0F647-->  <IS_CHILD_PROPERTY_IN_PARENT><![CDATA[Y]]></IS_CHILD_PROPERTY_IN_PARENT>
 <!--34F286EB36B44D30B48C32C65BF0F647--></AD_COLUMN>
 
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA--><AD_COLUMN>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <AD_COLUMN_ID><![CDATA[5AA3B1FAF2C047C7AC7BCB081AADF1DA]]></AD_COLUMN_ID>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <NAME><![CDATA[Entity Alias]]></NAME>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <DESCRIPTION><![CDATA[Defines the main entity alias in the HQL Where Clause.]]></DESCRIPTION>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <HELP><![CDATA[Defines the main entity alias in the HQL Where Clause so it can be used in subqueries. If left empty there'll be no alias on the main entity.]]></HELP>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <COLUMNNAME><![CDATA[Entity_Alias]]></COLUMNNAME>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <AD_TABLE_ID><![CDATA[D370F1BCD26D42D8A33AFF5D3941BEF7]]></AD_TABLE_ID>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <AD_REFERENCE_ID><![CDATA[10]]></AD_REFERENCE_ID>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <FIELDLENGTH><![CDATA[60]]></FIELDLENGTH>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISKEY><![CDATA[N]]></ISKEY>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISPARENT><![CDATA[N]]></ISPARENT>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISMANDATORY><![CDATA[N]]></ISMANDATORY>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISUPDATEABLE><![CDATA[Y]]></ISUPDATEABLE>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISIDENTIFIER><![CDATA[N]]></ISIDENTIFIER>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <SEQNO><![CDATA[140]]></SEQNO>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISTRANSLATED><![CDATA[N]]></ISTRANSLATED>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISENCRYPTED><![CDATA[N]]></ISENCRYPTED>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISSELECTIONCOLUMN><![CDATA[N]]></ISSELECTIONCOLUMN>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <AD_ELEMENT_ID><![CDATA[6BD369C35B54496EA399272F0E8EDF7F]]></AD_ELEMENT_ID>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISSESSIONATTR><![CDATA[N]]></ISSESSIONATTR>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISSECONDARYKEY><![CDATA[N]]></ISSECONDARYKEY>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISDESENCRYPTABLE><![CDATA[N]]></ISDESENCRYPTABLE>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <DEVELOPMENTSTATUS><![CDATA[RE]]></DEVELOPMENTSTATUS>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <AD_MODULE_ID><![CDATA[8A34B301DC524EA3A07513DF9F42CC90]]></AD_MODULE_ID>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <POSITION><![CDATA[14]]></POSITION>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISTRANSIENT><![CDATA[N]]></ISTRANSIENT>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISAUTOSAVE><![CDATA[Y]]></ISAUTOSAVE>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ALLOWSORTING><![CDATA[Y]]></ALLOWSORTING>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ALLOWFILTERING><![CDATA[Y]]></ALLOWFILTERING>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <ALLOWED_CROSS_ORG_LINK><![CDATA[N]]></ALLOWED_CROSS_ORG_LINK>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA-->  <IS_CHILD_PROPERTY_IN_PARENT><![CDATA[N]]></IS_CHILD_PROPERTY_IN_PARENT>
+<!--5AA3B1FAF2C047C7AC7BCB081AADF1DA--></AD_COLUMN>
+
 <!--8F90A1176A6747A0985A80F0A4ED651C--><AD_COLUMN>
 <!--8F90A1176A6747A0985A80F0A4ED651C-->  <AD_COLUMN_ID><![CDATA[8F90A1176A6747A0985A80F0A4ED651C]]></AD_COLUMN_ID>
 <!--8F90A1176A6747A0985A80F0A4ED651C-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_ELEMENT.xml	Thu Nov 28 10:27:46 2019 +0100
+++ b/modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_ELEMENT.xml	Wed Nov 20 17:29:27 2019 +0100
@@ -12,6 +12,20 @@
 <!--3A651C35FB7E405292950352CD09DD46-->  <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
 <!--3A651C35FB7E405292950352CD09DD46--></AD_ELEMENT>
 
+<!--6BD369C35B54496EA399272F0E8EDF7F--><AD_ELEMENT>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <AD_ELEMENT_ID><![CDATA[6BD369C35B54496EA399272F0E8EDF7F]]></AD_ELEMENT_ID>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <COLUMNNAME><![CDATA[Entity_Alias]]></COLUMNNAME>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <NAME><![CDATA[Entity Alias]]></NAME>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <PRINTNAME><![CDATA[Entity Alias]]></PRINTNAME>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <DESCRIPTION><![CDATA[Defines the main entity alias in the HQL Where Clause.]]></DESCRIPTION>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <HELP><![CDATA[Defines the main entity alias in the HQL Where Clause so it can be used in subqueries. If left empty there'll be no alias on the main entity.]]></HELP>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <AD_MODULE_ID><![CDATA[8A34B301DC524EA3A07513DF9F42CC90]]></AD_MODULE_ID>
+<!--6BD369C35B54496EA399272F0E8EDF7F-->  <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
+<!--6BD369C35B54496EA399272F0E8EDF7F--></AD_ELEMENT>
+
 <!--87622BE79A504BE48BE22DF76CE1D8A2--><AD_ELEMENT>
 <!--87622BE79A504BE48BE22DF76CE1D8A2-->  <AD_ELEMENT_ID><![CDATA[87622BE79A504BE48BE22DF76CE1D8A2]]></AD_ELEMENT_ID>
 <!--87622BE79A504BE48BE22DF76CE1D8A2-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_FIELD.xml	Thu Nov 28 10:27:46 2019 +0100
+++ b/modules/org.openbravo.utility.cleanup.log/src-db/database/sourcedata/AD_FIELD.xml	Wed Nov 20 17:29:27 2019 +0100
@@ -27,6 +27,34 @@
 <!--2243B45A4249483C95A892DF0D8F941F-->  <EM_OBUIAPP_SHOWSUMMARY><![CDATA[N]]></EM_OBUIAPP_SHOWSUMMARY>
 <!--2243B45A4249483C95A892DF0D8F941F--></AD_FIELD>
 
+<!--27789A09D28544E08D85D23BE25B695F--><AD_FIELD>
+<!--27789A09D28544E08D85D23BE25B695F-->  <AD_FIELD_ID><![CDATA[27789A09D28544E08D85D23BE25B695F]]></AD_FIELD_ID>
+<!--27789A09D28544E08D85D23BE25B695F-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--27789A09D28544E08D85D23BE25B695F-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--27789A09D28544E08D85D23BE25B695F-->  <NAME><![CDATA[Entity Alias]]></NAME>
+<!--27789A09D28544E08D85D23BE25B695F-->  <DESCRIPTION><![CDATA[Defines the main entity alias in the HQL Where Clause.]]></DESCRIPTION>
+<!--27789A09D28544E08D85D23BE25B695F-->  <HELP><![CDATA[Defines the main entity alias in the HQL Where Clause so it can be used in subqueries. If left empty there'll be no alias on the main entity.]]></HELP>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISCENTRALLYMAINTAINED><![CDATA[Y]]></ISCENTRALLYMAINTAINED>
+<!--27789A09D28544E08D85D23BE25B695F-->  <AD_TAB_ID><![CDATA[60097D591A22496696B79D80FE53F2A9]]></AD_TAB_ID>
+<!--27789A09D28544E08D85D23BE25B695F-->  <AD_COLUMN_ID><![CDATA[5AA3B1FAF2C047C7AC7BCB081AADF1DA]]></AD_COLUMN_ID>
+<!--27789A09D28544E08D85D23BE25B695F-->  <IGNOREINWAD><![CDATA[N]]></IGNOREINWAD>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISDISPLAYED><![CDATA[Y]]></ISDISPLAYED>
+<!--27789A09D28544E08D85D23BE25B695F-->  <DISPLAYLENGTH><![CDATA[60]]></DISPLAYLENGTH>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISREADONLY><![CDATA[N]]></ISREADONLY>
+<!--27789A09D28544E08D85D23BE25B695F-->  <SEQNO><![CDATA[60]]></SEQNO>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISSAMELINE><![CDATA[N]]></ISSAMELINE>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISFIELDONLY><![CDATA[N]]></ISFIELDONLY>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISENCRYPTED><![CDATA[N]]></ISENCRYPTED>
+<!--27789A09D28544E08D85D23BE25B695F-->  <SHOWINRELATION><![CDATA[N]]></SHOWINRELATION>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISFIRSTFOCUSEDFIELD><![CDATA[N]]></ISFIRSTFOCUSEDFIELD>
+<!--27789A09D28544E08D85D23BE25B695F-->  <AD_MODULE_ID><![CDATA[8A34B301DC524EA3A07513DF9F42CC90]]></AD_MODULE_ID>
+<!--27789A09D28544E08D85D23BE25B695F-->  <STARTINODDCOLUMN><![CDATA[N]]></STARTINODDCOLUMN>
+<!--27789A09D28544E08D85D23BE25B695F-->  <STARTNEWLINE><![CDATA[N]]></STARTNEWLINE>
+<!--27789A09D28544E08D85D23BE25B695F-->  <ISSHOWNINSTATUSBAR><![CDATA[N]]></ISSHOWNINSTATUSBAR>
+<!--27789A09D28544E08D85D23BE25B695F-->  <EM_OBUIAPP_SHOWSUMMARY><![CDATA[N]]></EM_OBUIAPP_SHOWSUMMARY>
+<!--27789A09D28544E08D85D23BE25B695F--></AD_FIELD>
+
 <!--6C561A964545449AB2DBCCF9FA8053E1--><AD_FIELD>
 <!--6C561A964545449AB2DBCCF9FA8053E1-->  <AD_FIELD_ID><![CDATA[6C561A964545449AB2DBCCF9FA8053E1]]></AD_FIELD_ID>
 <!--6C561A964545449AB2DBCCF9FA8053E1-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
@@ -96,7 +124,8 @@
 <!--7FFF3E59819D4B229D8513E0B3E987D0-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
 <!--7FFF3E59819D4B229D8513E0B3E987D0-->  <NAME><![CDATA[HQL Where Clause]]></NAME>
 <!--7FFF3E59819D4B229D8513E0B3E987D0-->  <DESCRIPTION><![CDATA[Remove only the records matching this criteria]]></DESCRIPTION>
-<!--7FFF3E59819D4B229D8513E0B3E987D0-->  <HELP><![CDATA[HQL where clause that will be used to delete only the records that match this criteria. In case this field is not set, all records will be deleted.]]></HELP>
+<!--7FFF3E59819D4B229D8513E0B3E987D0-->  <HELP><![CDATA[HQL where clause that will be used to delete only the records that match this criteria. In case this field is not set, all records will be deleted.
+It is possible to add subqueries referencing the main table, use the alias defined in Entity Alias field to reference the main table.]]></HELP>
 <!--7FFF3E59819D4B229D8513E0B3E987D0-->  <ISCENTRALLYMAINTAINED><![CDATA[N]]></ISCENTRALLYMAINTAINED>
 <!--7FFF3E59819D4B229D8513E0B3E987D0-->  <AD_TAB_ID><![CDATA[60097D591A22496696B79D80FE53F2A9]]></AD_TAB_ID>
 <!--7FFF3E59819D4B229D8513E0B3E987D0-->  <AD_COLUMN_ID><![CDATA[CDD88BCF980748AD989429F1BDD116C7]]></AD_COLUMN_ID>
--- a/modules/org.openbravo.utility.cleanup.log/src/org/openbravo/utility/cleanup/log/CleanEntity.java	Thu Nov 28 10:27:46 2019 +0100
+++ b/modules/org.openbravo.utility.cleanup.log/src/org/openbravo/utility/cleanup/log/CleanEntity.java	Wed Nov 20 17:29:27 2019 +0100
@@ -11,7 +11,7 @@
  * 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-2015 Openbravo SLU 
+ * All portions are Copyright (C) 2013-2019 Openbravo SLU
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -71,8 +71,15 @@
       ProcessLogger bgLogger) {
     Entity entity = ModelProvider.getInstance().getEntityByTableId(config.getTable().getId());
 
+
     String hql = "delete from " + entity.getName();
 
+    // An alias can be added to the main table, so it can be referenced from HQL subqueries,
+    // See issue https://issues.openbravo.com/view.php?id=41977
+    if (config.getEntityAlias() != null && StringUtils.isAlphanumeric(config.getEntityAlias())) {
+      hql += " " + config.getEntityAlias();
+    }
+
     String where = "";
     if (config.getOlderThan() != 0L) {
       String prop;