details: https://code.openbravo.com/erp/devel/pi/rev/9dbe45c34ea5 changeset: 16850:9dbe45c34ea5 user: Asier Lostalé <asier.lostale <at> openbravo.com> date: Fri Jun 15 12:28:17 2012 +0200 summary: fixed bug 20363: Implemented audit trail support for text/clob column type
details: https://code.openbravo.com/erp/devel/pi/rev/2c50e12c22aa changeset: 16851:2c50e12c22aa user: Miguel de Juana <miguel.dejuana <at> openbravo.com> date: Thu May 24 13:41:52 2012 +0200 summary: fixed bug 20365: Don't allow legth as column/field name as it causes js errors diffstat: src-db/database/model/functions/AD_ISJAVAWORD.xml | 4 +- src-db/database/model/postscript-Oracle.sql | 13 +- src-db/database/model/postscript-PostgreSql.sql | 16 +- src-db/database/model/tables/AD_AUDIT_TRAIL.xml | 8 + src-db/database/model/triggers/AD_COLUMN_TRG2.xml | 5 +- src-db/database/model/triggers/AD_ELEMENT_TRG.xml | 9 +- src-db/database/model/triggers/AD_FIELD_TRG.xml | 6 +- src-db/database/model/views/AD_AUDIT_TRAIL_V.xml | 20 +- src-db/database/sourcedata/AD_COLUMN.xml | 150 +++++++++- src-db/database/sourcedata/AD_ELEMENT.xml | 26 + src-db/database/sourcedata/AD_FIELD.xml | 2 + src-db/database/sourcedata/AD_MESSAGE.xml | 33 ++ src/org/openbravo/erpCommon/businessUtility/AuditTrailDeletedRecords.java | 4 +- src/org/openbravo/erpCommon/businessUtility/AuditTrailPopup.java | 12 +- 14 files changed, 281 insertions(+), 27 deletions(-) diffs (truncated from 665 to 300 lines): diff -r 146581313d40 -r 2c50e12c22aa src-db/database/model/functions/AD_ISJAVAWORD.xml --- a/src-db/database/model/functions/AD_ISJAVAWORD.xml Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/model/functions/AD_ISJAVAWORD.xml Thu May 24 13:41:52 2012 +0200 @@ -16,13 +16,13 @@ * 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-2010 Openbravo SLU +* All portions are Copyright (C) 2009-2012 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ BEGIN --Prevents column names to generate conflicts with DAL's getters and setters - RETURN case when lower(WORD) in ('class') + RETURN case when lower(WORD) in ('class', 'length') THEN 'Y' ELSE 'N' END; END AD_ISJAVAWORD ]]></body> diff -r 146581313d40 -r 2c50e12c22aa src-db/database/model/postscript-Oracle.sql --- a/src-db/database/model/postscript-Oracle.sql Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/model/postscript-Oracle.sql Thu May 24 13:41:52 2012 +0200 @@ -621,7 +621,7 @@ WHERE a.value <= 1024 /-- END -create or replace FUNCTION AD_GET_RDBMS RETURN VARCHAR2 +create or replace FUNCTION AD_GET_RDBMS RETURN VARCHAR2 DETERMINISTIC AS /************************************************************************* * The contents of this file are subject to the Openbravo Public License @@ -635,7 +635,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 Openbravo SLU +* All portions are Copyright (C) 2009-2012 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -700,7 +700,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-2010 Openbravo SLU +* All portions are Copyright (C) 2009-2012 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -799,6 +799,8 @@ V_NEW_NUMBER NUMBER := NULL; V_OLD_DATE DATE := NULL; V_NEW_DATE DATE := NULL; + V_OLD_TEXT CLOB := NULL; + V_NEW_TEXT CLOB := NULL; V_TIME DATE; V_ORG VARCHAR2(32); V_CLIENT VARCHAR2(32); @@ -875,7 +877,7 @@ and upper(c.columnname) not in ('CREATED','CREATEDBY','UPDATED', 'UPDATEDBY') and c.isexcludeaudit='N' order by c.position) loop - if (cur_cols.data_type in ('VARCHAR2', 'CHAR', 'CLOB')) then + if (cur_cols.data_type in ('VARCHAR2', 'CHAR')) then datatype := 'CHAR'; code := code || 'IF (UPDATING AND ((COALESCE(:NEW.'||cur_cols.COLUMN_NAME||',''.'') != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||',''.'')) OR ((:NEW.'||cur_cols.COLUMN_NAME||' IS NULL) AND :OLD.'||cur_cols.COLUMN_NAME||'=''.'') OR ((:OLD.'||cur_cols.COLUMN_NAME||' IS NULL) AND :NEW.'||cur_cols.COLUMN_NAME||'=''.'')))'; elsif (cur_cols.data_type in ('NVARCHAR2', 'NCHAR')) then @@ -884,6 +886,9 @@ elsif (cur_cols.data_type in ('DATE')) then datatype := 'DATE'; code := code || 'IF (UPDATING AND COALESCE(:NEW.'||cur_cols.COLUMN_NAME||', now()) != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||', now()))'; + elsif (cur_cols.data_type in ('CLOB')) then + datatype := 'TEXT'; + code := code || 'IF (UPDATING AND ((COALESCE(:NEW.'||cur_cols.COLUMN_NAME||',''.'') != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||',''.'')) OR ((:NEW.'||cur_cols.COLUMN_NAME||' IS NULL) AND :OLD.'||cur_cols.COLUMN_NAME||'=''.'') OR ((:OLD.'||cur_cols.COLUMN_NAME||' IS NULL) AND :NEW.'||cur_cols.COLUMN_NAME||'=''.'')))'; else datatype := 'NUMBER'; code := code || 'IF (UPDATING AND COALESCE(:NEW.'||cur_cols.COLUMN_NAME||', -1) != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||', -1))'; diff -r 146581313d40 -r 2c50e12c22aa src-db/database/model/postscript-PostgreSql.sql --- a/src-db/database/model/postscript-PostgreSql.sql Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/model/postscript-PostgreSql.sql Thu May 24 13:41:52 2012 +0200 @@ -372,14 +372,14 @@ * 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 Openbravo SLU +* All portions are Copyright (C) 2009-2012 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ BEGIN return 'POSTGRE'; END; $BODY$ - LANGUAGE 'plpgsql' VOLATILE + LANGUAGE 'plpgsql' IMMUTABLE /-- END CREATE OR REPLACE VIEW AD_INTEGER AS @@ -490,7 +490,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-2010 Openbravo SLU +* All portions are Copyright (C) 2009-2012 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -569,6 +569,8 @@ V_NEW_NUMBER NUMERIC := NULL; V_OLD_DATE TIMESTAMP := NULL; V_NEW_DATE TIMESTAMP := NULL; + V_OLD_TEXT TEXT := NULL; + V_NEW_TEXT TEXT := NULL; V_TIME TIMESTAMP; V_ORG VARCHAR(32); V_CLIENT VARCHAR(32); @@ -648,7 +650,7 @@ order by c.position) loop code := code || ' V_Change := false;'; - if (cur_cols.data_type in ('VARCHAR', 'BPCHAR', 'TEXT')) then + if (cur_cols.data_type in ('VARCHAR', 'BPCHAR')) then datatype := 'CHAR'; code := code || ' IF TG_OP = ''UPDATE'' THEN @@ -660,6 +662,12 @@ IF TG_OP = ''UPDATE'' THEN V_CHANGE = COALESCE(new.'||cur_cols.COLUMN_NAME||', now()) != COALESCE(old.'||cur_cols.COLUMN_NAME||', now()); END IF;'; + elsif (cur_cols.data_type in ('TEXT')) then + datatype := 'TEXT'; + code := code || ' + IF TG_OP = ''UPDATE'' THEN + V_CHANGE = (COALESCE(new.'||cur_cols.COLUMN_NAME||',''.'') != COALESCE(old.'||cur_cols.COLUMN_NAME||',''.'') OR (new.'||cur_cols.COLUMN_NAME||' IS NULL AND old.'||cur_cols.COLUMN_NAME||'=''.'') OR (old.'||cur_cols.COLUMN_NAME||' IS NULL AND new.'||cur_cols.COLUMN_NAME||'=''.'') ); + END IF;'; else datatype := 'NUMBER'; code := code || ' diff -r 146581313d40 -r 2c50e12c22aa src-db/database/model/tables/AD_AUDIT_TRAIL.xml --- a/src-db/database/model/tables/AD_AUDIT_TRAIL.xml Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/model/tables/AD_AUDIT_TRAIL.xml Thu May 24 13:41:52 2012 +0200 @@ -101,6 +101,14 @@ <default/> <onCreateDefault/> </column> + <column name="OLD_TEXT" primaryKey="false" required="false" type="CLOB" size="4000" autoIncrement="false"> + <default/> + <onCreateDefault/> + </column> + <column name="NEW_TEXT" primaryKey="false" required="false" type="CLOB" size="4000" autoIncrement="false"> + <default/> + <onCreateDefault/> + </column> <index name="AD_AUDIT_TRAIL_INSERT_IDX" unique="false"> <index-column name="AD_TABLE_ID"/> <index-column name="RECORD_ID"/> diff -r 146581313d40 -r 2c50e12c22aa src-db/database/model/triggers/AD_COLUMN_TRG2.xml --- a/src-db/database/model/triggers/AD_COLUMN_TRG2.xml Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/model/triggers/AD_COLUMN_TRG2.xml Thu May 24 13:41:52 2012 +0200 @@ -14,7 +14,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) 2008-2009 Openbravo SLU +* All portions are Copyright (C) 2008-2012 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -57,6 +57,9 @@ IF AD_IsJavaWord(:new.Name)='Y' THEN RAISE_APPLICATION_ERROR(-20000, '@NotAllowedColumnName@ "'||:new.name||'" @ReservedJavaWord@') ; END IF; + IF AD_IsJavaWord(:new.ColumnName)='Y' THEN + RAISE_APPLICATION_ERROR(-20000, '@NotAllowedDBColumnName@ "'||:new.ColumnName||'" @ReservedJavaWord@') ; + END IF; /** * Create Sequence for DocumentNo and Value columns diff -r 146581313d40 -r 2c50e12c22aa src-db/database/model/triggers/AD_ELEMENT_TRG.xml --- a/src-db/database/model/triggers/AD_ELEMENT_TRG.xml Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/model/triggers/AD_ELEMENT_TRG.xml Thu May 24 13:41:52 2012 +0200 @@ -17,7 +17,7 @@ * parts created by ComPiere are Copyright (C) ComPiere, Inc.; * All Rights Reserved. * Contributor(s): Openbravo SLU - * Contributions are Copyright (C) 2001-2009 Openbravo, S.L.U. + * Contributions are Copyright (C) 2001-2012 Openbravo, S.L.U. * * Specifically, this derivative work is based upon the following Compiere * file and version. @@ -35,6 +35,13 @@ IF AD_isTriggerEnabled()='N' THEN RETURN; END IF; + IF AD_IsJavaWord(:new.Name)='Y' THEN + RAISE_APPLICATION_ERROR(-20000, '@NotAllowedElementName@ "'||:new.Name||'" @ReservedJavaWord@') ; + END IF; + IF AD_IsJavaWord(:new.ColumnName)='Y' THEN + RAISE_APPLICATION_ERROR(-20000, '@NotAllowedDBColumnName@ "'||:new.ColumnName||'" @ReservedJavaWord@') ; + END IF; + -- Insert AD_Element Trigger IF INSERTING diff -r 146581313d40 -r 2c50e12c22aa src-db/database/model/triggers/AD_FIELD_TRG.xml --- a/src-db/database/model/triggers/AD_FIELD_TRG.xml Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/model/triggers/AD_FIELD_TRG.xml Thu May 24 13:41:52 2012 +0200 @@ -17,7 +17,7 @@ * parts created by ComPiere are Copyright (C) ComPiere, Inc.; * All Rights Reserved. * Contributor(s): Openbravo SLU - * Contributions are Copyright (C) 2001-2008 Openbravo, S.L.U. + * Contributions are Copyright (C) 2001-2012 Openbravo, S.L.U. * * Specifically, this derivative work is based upon the following Compiere * file and version. @@ -31,6 +31,10 @@ IF AD_isTriggerEnabled()='N' THEN RETURN; END IF; + IF AD_IsJavaWord(:new.Name)='Y' THEN + RAISE_APPLICATION_ERROR(-20000, '@NotAllowedFieldName@ "'||:new.Name||'" @ReservedJavaWord@') ; + END IF; + IF INSERTING THEN INSERT diff -r 146581313d40 -r 2c50e12c22aa src-db/database/model/views/AD_AUDIT_TRAIL_V.xml --- a/src-db/database/model/views/AD_AUDIT_TRAIL_V.xml Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/model/views/AD_AUDIT_TRAIL_V.xml Thu May 24 13:41:52 2012 +0200 @@ -1,6 +1,6 @@ <?xml version="1.0"?> <database name="VIEW AD_AUDIT_TRAIL_V"> - <view name="AD_AUDIT_TRAIL_V"><![CDATA[SELECT at.ad_audit_trail_id, at.ad_client_id, at.ad_org_id, at.isactive, at.created, at.createdby, at.updated, at.updatedby, at.ad_table_id, at.record_id, at.record_revision, at.ad_user_id, at.event_time, at.processtype, at.process_id, at.action, at.ad_column_id, at.old_char, at.new_char, at.old_nchar, at.new_nchar, at.old_number, at.new_number, at.old_date, at.new_date, + <view name="AD_AUDIT_TRAIL_V"><![CDATA[SELECT at.ad_audit_trail_id, at.ad_client_id, at.ad_org_id, at.isactive, at.created, at.createdby, at.updated, at.updatedby, at.ad_table_id, at.record_id, at.record_revision, at.ad_user_id, at.event_time, at.processtype, at.process_id, at.action, at.ad_column_id, at.old_char, at.new_char, at.old_nchar, at.new_nchar, at.old_number, at.new_number, at.old_date, at.new_date, at.old_text, at.new_text, CASE WHEN at.processtype = 'P' THEN (SELECT to_char(p.name) AS to_char FROM ad_process p @@ -12,6 +12,22 @@ FROM ad_form f WHERE f.ad_form_id = at.process_id) ELSE '' -END AS process_desc, COALESCE(at.old_char, to_char(at.old_nchar), to_char(at.old_date), to_char(at.old_number)) AS old_value, COALESCE(at.new_char, to_char(at.new_nchar), to_char(at.new_date), to_char(at.new_number)) AS new_value +END AS process_desc, COALESCE(at.old_char, to_char(at.old_nchar), to_char(at.old_date), to_char(at.old_number), +CASE +WHEN ad_get_rdbms() = 'ORACLE' THEN +CASE +WHEN length(at.old_text) > 3996 THEN (to_char(substr(at.old_text, 1, 3996)) || '...') +ELSE to_char(at.old_text) +END +ELSE to_char(at.old_text) +END) AS old_value, COALESCE(at.new_char, to_char(at.new_nchar), to_char(at.new_date), to_char(at.new_number), +CASE +WHEN ad_get_rdbms() = 'ORACLE' THEN +CASE +WHEN length(at.new_text) > 3996 THEN (to_char(substr(at.new_text, 1, 3996)) || '...') +ELSE to_char(at.new_text) +END +ELSE to_char(at.new_text) +END) AS new_value FROM ad_audit_trail at]]></view> </database> diff -r 146581313d40 -r 2c50e12c22aa src-db/database/sourcedata/AD_COLUMN.xml --- a/src-db/database/sourcedata/AD_COLUMN.xml Fri Jun 15 16:33:38 2012 +0200 +++ b/src-db/database/sourcedata/AD_COLUMN.xml Thu May 24 13:41:52 2012 +0200 @@ -223395,6 +223395,40 @@ <!--209C6CD23241412F80A346497E8544E0--> <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE> <!--209C6CD23241412F80A346497E8544E0--></AD_COLUMN> +<!--20A0358265C94676A1762AA284590490--><AD_COLUMN> +<!--20A0358265C94676A1762AA284590490--> <AD_COLUMN_ID><![CDATA[20A0358265C94676A1762AA284590490]]></AD_COLUMN_ID> +<!--20A0358265C94676A1762AA284590490--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> +<!--20A0358265C94676A1762AA284590490--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> +<!--20A0358265C94676A1762AA284590490--> <ISACTIVE><![CDATA[Y]]></ISACTIVE> +<!--20A0358265C94676A1762AA284590490--> <NAME><![CDATA[Old Text]]></NAME> +<!--20A0358265C94676A1762AA284590490--> <DESCRIPTION><![CDATA[Old Text value]]></DESCRIPTION> +<!--20A0358265C94676A1762AA284590490--> <COLUMNNAME><![CDATA[OLD_Text]]></COLUMNNAME> +<!--20A0358265C94676A1762AA284590490--> <AD_TABLE_ID><![CDATA[D728885674EB49DA9595AD66A26B2D28]]></AD_TABLE_ID> +<!--20A0358265C94676A1762AA284590490--> <AD_REFERENCE_ID><![CDATA[14]]></AD_REFERENCE_ID> +<!--20A0358265C94676A1762AA284590490--> <FIELDLENGTH><![CDATA[3000000]]></FIELDLENGTH> +<!--20A0358265C94676A1762AA284590490--> <ISKEY><![CDATA[N]]></ISKEY> +<!--20A0358265C94676A1762AA284590490--> <ISPARENT><![CDATA[N]]></ISPARENT> +<!--20A0358265C94676A1762AA284590490--> <ISMANDATORY><![CDATA[N]]></ISMANDATORY> +<!--20A0358265C94676A1762AA284590490--> <ISUPDATEABLE><![CDATA[Y]]></ISUPDATEABLE> +<!--20A0358265C94676A1762AA284590490--> <ISIDENTIFIER><![CDATA[N]]></ISIDENTIFIER> +<!--20A0358265C94676A1762AA284590490--> <SEQNO><![CDATA[290]]></SEQNO> +<!--20A0358265C94676A1762AA284590490--> <ISTRANSLATED><![CDATA[N]]></ISTRANSLATED> +<!--20A0358265C94676A1762AA284590490--> <ISENCRYPTED><![CDATA[N]]></ISENCRYPTED> +<!--20A0358265C94676A1762AA284590490--> <ISSELECTIONCOLUMN><![CDATA[N]]></ISSELECTIONCOLUMN> +<!--20A0358265C94676A1762AA284590490--> <AD_ELEMENT_ID><![CDATA[6AFA9B0233204ABEAECEF50C444C5089]]></AD_ELEMENT_ID> +<!--20A0358265C94676A1762AA284590490--> <ISSESSIONATTR><![CDATA[N]]></ISSESSIONATTR> +<!--20A0358265C94676A1762AA284590490--> <ISSECONDARYKEY><![CDATA[N]]></ISSECONDARYKEY> +<!--20A0358265C94676A1762AA284590490--> <ISDESENCRYPTABLE><![CDATA[N]]></ISDESENCRYPTABLE> +<!--20A0358265C94676A1762AA284590490--> <DEVELOPMENTSTATUS><![CDATA[RE]]></DEVELOPMENTSTATUS> +<!--20A0358265C94676A1762AA284590490--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +<!--20A0358265C94676A1762AA284590490--> <POSITION><![CDATA[26]]></POSITION> +<!--20A0358265C94676A1762AA284590490--> <ISTRANSIENT><![CDATA[N]]></ISTRANSIENT> +<!--20A0358265C94676A1762AA284590490--> <ISAUTOSAVE><![CDATA[Y]]></ISAUTOSAVE> +<!--20A0358265C94676A1762AA284590490--> <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW> +<!--20A0358265C94676A1762AA284590490--> <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION> +<!--20A0358265C94676A1762AA284590490--> <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE> ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
