Fixes Issue 23140 : update audit infrastructure fails with client and org tables
authorShankar Balachandran <shankar.balachandran@openbravo.com>
Wed, 20 Mar 2013 11:18:53 +0530
changeset 19939 347ce63c377b
parent 19938 b90a14776499
child 19940 6cfd12f52b4f
Fixes Issue 23140 : update audit infrastructure fails with client and org tables

With the fix, when update audit infrastructure is executed from application, the client and org tables are excluded.
An ant task has been created to run the same process including client and org tables.
build.xml
src-db/database/model/postscript-Oracle.sql
src-db/database/model/postscript-PostgreSql.sql
src-db/database/sourcedata/AD_MESSAGE.xml
--- a/build.xml	Tue Mar 19 21:15:46 2013 +0100
+++ b/build.xml	Wed Mar 20 11:18:53 2013 +0530
@@ -346,6 +346,16 @@
        </sql>
      </sequential>
   </macrodef>
+
+  <macrodef name="createAuditTriggers">
+     <sequential>
+       <sql driver="${bbdd.driver}" url="${bbdd.owner.url}" userid="${bbdd.user}" password="${bbdd.password}" onerror="abort" autocommit="true" print="yes">
+         <classpath> <fileset dir="${base.lib}"> <include name="**/*.jar"> </include> </fileset> </classpath>
+         <transaction> SELECT ad_create_audit_triggers(null);
+         </transaction>
+       </sql>
+     </sequential>
+  </macrodef>
   
   <taskdef resource="axis-tasks.properties" classpathref="project.class.path" />
   
@@ -1192,5 +1202,9 @@
                     adminMode="true" 
                     propertiesFile="${base.config}/Openbravo.properties"/>
   </target>
+
+  <target name="update.audit.trail.infrastructure">
+    <createAuditTriggers/>
+  </target>
   
 </project>
--- a/src-db/database/model/postscript-Oracle.sql	Tue Mar 19 21:15:46 2013 +0100
+++ b/src-db/database/model/postscript-Oracle.sql	Wed Mar 20 11:18:53 2013 +0530
@@ -593,7 +593,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) 2009-2012 Openbravo SLU
+* All portions are Copyright (C) 2009-2013 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -615,6 +615,7 @@
   deleted number :=0;
   created number :=0;
   v_message varchar2(500);
+  v_tableList varchar2(500);
   v_isObps number;
   
   
@@ -643,17 +644,44 @@
     RAISE_APPLICATION_ERROR(-20000, '@OBPSNeededForAudit@') ;
   end if;  
 
-  for cur_triggers in (select trigger_name
+  if p_pinstance_id is not null then
+    for cur_triggers in (select trigger_name
+                         from user_triggers
+                        where trigger_name like 'AU\_%' escape '\'
+                        and trigger_name <> 'au_ad_client_trg'
+                        and trigger_name <> 'au_ad_org_trg') loop
+      execute immediate 'drop trigger '||cur_triggers.trigger_name;
+      deleted := deleted + 1;
+    end loop;
+  else
+    for cur_triggers in (select trigger_name
                          from user_triggers
                         where trigger_name like 'AU\_%' escape '\') loop
-    execute immediate 'drop trigger '||cur_triggers.trigger_name;
-    deleted := deleted + 1;
+      execute immediate 'drop trigger '||cur_triggers.trigger_name;
+      deleted := deleted + 1;
+    end loop;
+  end if;
+
+  if p_pinstance_id is not null then
+  for cur_tables in (select * from ad_table
+                      where isfullyaudited = 'Y'
+                      and ISVIEW='N'
+                      and (UPPER(TABLENAME) = 'AD_CLIENT'
+                      or UPPER(TABLENAME) = 'AD_ORG')) loop
+      if v_tableList is null then
+        v_tableList := cur_tables.tablename;
+      else
+        v_tableList := v_tableList || ' , '|| cur_tables.tablename;
+      end if;
   end loop;
+  end if;
 
   for cur_tables in (select *
                        from ad_table
                       where isfullyaudited = 'Y'
                       AND ISVIEW='N'
+                      AND UPPER(TABLENAME) != CASE WHEN p_pinstance_id is not null THEN 'AD_ORG' ELSE ' ' END
+                      AND UPPER(TABLENAME) != CASE WHEN p_pinstance_id is not null THEN 'AD_CLIENT' ELSE ' ' END
                       order by tablename) loop
     dbms_output.put_line('Creating trigger for table '||cur_tables.tablename);
     triggerName := 'AU_'||SUBSTR(cur_tables.tablename,1,23)||'_TRG';
@@ -768,7 +796,7 @@
                         and upper(c.columnname) = u.column_name
                         AND u.data_type != 'BLOB'
                         and upper(c.columnname) not in ('CREATED','CREATEDBY','UPDATED', 'UPDATEDBY')
-			and c.isexcludeaudit='N'
+                        and c.isexcludeaudit='N'
                         order by c.position) loop
       if (cur_cols.data_type in ('VARCHAR2', 'CHAR')) then
         datatype := 'CHAR';
@@ -824,7 +852,11 @@
     created := created + 1;
   end loop;
   
-  v_Message := '@Deleted@: '||deleted||' @Created@: '||created;
+  if v_tableList is null then
+    v_Message := '@Deleted@: '||deleted||' @Created@: '||created;
+  else
+    v_Message := '@Deleted@: '||deleted||' @Created@: '||created||'. @RunAuditFromTerminalTbl@ '|| v_tableList || '. @RunAuditFromTerminalHint@' ;
+  end if;
   AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ;
   EXCEPTION
 WHEN OTHERS THEN
--- a/src-db/database/model/postscript-PostgreSql.sql	Tue Mar 19 21:15:46 2013 +0100
+++ b/src-db/database/model/postscript-PostgreSql.sql	Wed Mar 20 11:18:53 2013 +0530
@@ -358,7 +358,6 @@
  
 alter table ad_reference enable trigger ad_reference_mod_trg;
 /-- END
- 
 
 CREATE OR REPLACE FUNCTION ad_create_audit_triggers(p_pinstance_id character varying)
   RETURNS void AS
@@ -375,7 +374,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) 2009-2012 Openbravo SLU
+* All portions are Copyright (C) 2009-2013 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -390,6 +389,7 @@
   deleted NUMERIC :=0;
   created NUMERIC :=0;
   v_message VARCHAR(500);
+  v_tableList VARCHAR(500);
   v_isObps NUMERIC;
 BEGIN 
   select count(*) 
@@ -397,24 +397,50 @@
     from ad_system
    where Instance_key is not null
      and activation_key is not null;
-     
   if v_isObps = 0 then
     RAISE EXCEPTION '%', '@OBPSNeededForAudit@' ;
-  end if;  	
-	
+  end if;
+
+  if p_pinstance_id is not null then
+    for cur_triggers in (select *
+                         from user_triggers
+                        where trigger_name like 'au_%' and trigger_name <> 'au_ad_client_trg' and trigger_name <> 'au_ad_org_trg') loop
+    execute 'DROP TRIGGER '||cur_triggers.trigger_name||' ON '||cur_triggers.table_name;
+    execute 'DROP FUNCTION '||cur_triggers.trigger_name||'()';
+    raise notice 'deleting %', cur_triggers.trigger_name;
+    deleted := deleted + 1;
+  end loop;
+  ELSE
   for cur_triggers in (select *
                          from user_triggers
                         where trigger_name like 'au_%') loop
     execute 'DROP TRIGGER '||cur_triggers.trigger_name||' ON '||cur_triggers.table_name;
-    execute 'DROP FUNCTION '||cur_triggers.trigger_name||'()';  
+    execute 'DROP FUNCTION '||cur_triggers.trigger_name||'()';
     raise notice 'deleting %', cur_triggers.trigger_name;
     deleted := deleted + 1;
   end loop;
+  end if;
+
+  if p_pinstance_id is not null then
+  for cur_tables in (select * from ad_table
+                      where isfullyaudited = 'Y'
+                      and ISVIEW='N'
+                      and (UPPER(TABLENAME) = 'AD_CLIENT'
+                      or UPPER(TABLENAME) = 'AD_ORG')) loop
+      if v_tableList IS NULL then
+        v_tableList := cur_tables.tablename;
+      else
+        v_tableList := v_tableList || ' , '|| cur_tables.tablename;
+      end if;
+  end loop;
+  end if;
 
   for cur_tables in (select *
                        from ad_table
                       where isfullyaudited = 'Y'
                       AND ISVIEW='N'
+                      AND UPPER(TABLENAME) <> CASE WHEN p_pinstance_id is not null THEN 'AD_ORG' ELSE '' END
+                      AND UPPER(TABLENAME) <> CASE WHEN p_pinstance_id is not null THEN 'AD_CLIENT' ELSE '' END
                       order by tablename) loop
     
     triggerName := 'AU_'||SUBSTR(cur_tables.tablename,1,23)||'_TRG';
@@ -531,7 +557,7 @@
                         and upper(c.columnname) = u.column_name
                         AND u.data_type != 'BYTEA'
                         and upper(c.columnname) not in ('CREATED','CREATEDBY','UPDATED', 'UPDATEDBY')
-			and c.isexcludeaudit='N'
+                        and c.isexcludeaudit='N'
                         order by c.position) loop
       code := code || '
     V_Change := false;';
@@ -606,8 +632,13 @@
     created := created + 1;
 
   end loop;
+
+  if v_tableList is null then
+    v_Message := '@Deleted@: '||deleted||' @Created@: '||created;
+  else
+    v_Message := '@Deleted@: '||deleted||' @Created@: '||created||'. @RunAuditFromTerminalTbl@ '|| v_tableList || '. @RunAuditFromTerminalHint@' ;
+  end if;
   
-  v_Message := '@Deleted@: '||deleted||' @Created@: '||created;
   PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ;
   EXCEPTION
 WHEN OTHERS THEN
--- a/src-db/database/sourcedata/AD_MESSAGE.xml	Tue Mar 19 21:15:46 2013 +0100
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml	Wed Mar 20 11:18:53 2013 +0530
@@ -17329,6 +17329,17 @@
 <!--58F0BFB72BD84DB3BD8ACA837BC04E61-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--58F0BFB72BD84DB3BD8ACA837BC04E61--></AD_MESSAGE>
 
+<!--59A0C7204D1040A0BD56FC2810F55913--><AD_MESSAGE>
+<!--59A0C7204D1040A0BD56FC2810F55913-->  <AD_MESSAGE_ID><![CDATA[59A0C7204D1040A0BD56FC2810F55913]]></AD_MESSAGE_ID>
+<!--59A0C7204D1040A0BD56FC2810F55913-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--59A0C7204D1040A0BD56FC2810F55913-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--59A0C7204D1040A0BD56FC2810F55913-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--59A0C7204D1040A0BD56FC2810F55913-->  <VALUE><![CDATA[RunAuditFromTerminalTbl]]></VALUE>
+<!--59A0C7204D1040A0BD56FC2810F55913-->  <MSGTEXT><![CDATA[Triggers for the following table(s) have not been updated :]]></MSGTEXT>
+<!--59A0C7204D1040A0BD56FC2810F55913-->  <MSGTYPE><![CDATA[I]]></MSGTYPE>
+<!--59A0C7204D1040A0BD56FC2810F55913-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--59A0C7204D1040A0BD56FC2810F55913--></AD_MESSAGE>
+
 <!--59A653928EC7415C9B375F69451E19B2--><AD_MESSAGE>
 <!--59A653928EC7415C9B375F69451E19B2-->  <AD_MESSAGE_ID><![CDATA[59A653928EC7415C9B375F69451E19B2]]></AD_MESSAGE_ID>
 <!--59A653928EC7415C9B375F69451E19B2-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
@@ -18556,6 +18567,17 @@
 <!--81088F7236714F8A9D6FE754B10D31E8-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--81088F7236714F8A9D6FE754B10D31E8--></AD_MESSAGE>
 
+<!--813F55394F28496CA7EF592D70234102--><AD_MESSAGE>
+<!--813F55394F28496CA7EF592D70234102-->  <AD_MESSAGE_ID><![CDATA[813F55394F28496CA7EF592D70234102]]></AD_MESSAGE_ID>
+<!--813F55394F28496CA7EF592D70234102-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--813F55394F28496CA7EF592D70234102-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--813F55394F28496CA7EF592D70234102-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--813F55394F28496CA7EF592D70234102-->  <VALUE><![CDATA[RunAuditFromTerminalHint]]></VALUE>
+<!--813F55394F28496CA7EF592D70234102-->  <MSGTEXT><![CDATA[In order to update these triggers you have to execute the following command from command prompt: ant update.audit.trail.infrastructure.]]></MSGTEXT>
+<!--813F55394F28496CA7EF592D70234102-->  <MSGTYPE><![CDATA[I]]></MSGTYPE>
+<!--813F55394F28496CA7EF592D70234102-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--813F55394F28496CA7EF592D70234102--></AD_MESSAGE>
+
 <!--815107A9103240C7BA1D901BAC3A035F--><AD_MESSAGE>
 <!--815107A9103240C7BA1D901BAC3A035F-->  <AD_MESSAGE_ID><![CDATA[815107A9103240C7BA1D901BAC3A035F]]></AD_MESSAGE_ID>
 <!--815107A9103240C7BA1D901BAC3A035F-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>