jos kelleners
|
| Posted: 07/19/2004, 7:24 AM |
|
We are using codecharge studio in combination with JSP.
Now we have to create a change log. We should log all record changes in a seperate file. For this reason we look at the record before and after the change.
Is there an object in codecharge which reflects those two situations. So that we can compare both objects. Or is there an other way to solve this problem (we do not want to specify each field in the coding).
Please help.
|
|
|
 |
Anton
|
| Posted: 07/20/2004, 7:59 AM |
|
Add your own ValidationListener into any components which are 'instanceof VerifiableControl' via addValidationListener(...). Your validation listener can then be notified of changed values in the model via normal CCS onValidate(e) firing of events.
Before you assign each ValidationListener object to the listeners you stash a copy of the value of the target control as an Object oldValue.
So, if the page is valid (your new listeners never deny the change) then you have a Vector of listeners with cloned old values being preserved so that the toString() can simply detail any new and old values.
The real problem is linking back to the PK value in the changed row so that you can write out traceable values in 'audit' style.
Did something similar but for 'row has changed' type of situations.
FK values being shown as fkOldVal = 16 and fkNewVal = 23 is a tad difficult to read and will have no relevance to a database which further changes and FK 23 or 16 row entry is removed!
Making the FK changed values Audit readable then stresses your i/o traffic back to the database.
Not got an answer to that one.
|
|
|
|
jos kelleners
|
| Posted: 07/20/2004, 12:45 PM |
|
We have a method to compare two instances of an object. So we were looking if there are objects whicht reflect the field value before and after the update.
|
|
|
|
Anton Hinxman
|
| Posted: 07/22/2004, 12:29 AM |
|
I can see where you are coming from but no such facility has been built into CCS – oldValue and getOldValue() would be nice to avoid all of this! It is as if you want to clone the VerifiableControl objects on a page and then test the post changed page for differences.
A bit excessive, IHO, because you can get the old values held against VerifiableControls on the page’s AfterInitialize event thus enabling you to traverse the pages model and stash the current values into a Vector of simple old value container objects. Given the fact that you may have many records and editable grids on a single page then you will have to mirror such structures in your Vector of stashed old value containers.
The real problem comes when you have to look at this new vector because each row of an editable grid fires separate SQL events. You can only report the true audit of change so such an audit must come AFTER the SQL has been submitted and is truly posted without error.
The page's processing can only be for one submit button so perhaps you can just copy out values from this portion of the data model and reduce your complexity?
The previous ValidationListener idea would reduce the processing and complexity further because they can be initially attached with old values and know when data has actually been changed. If effect such custom listeners could do the donkey work for the production of the audit trail. You would only need to attach such listeners to the portion of the page model being submitted. The control itself has getFieldSource() which is good enough for your audit and any custom ValidationListener will have the onValidate(Event e) so as to e.getControl() and hence the changed control value.
I can see no other simpler way.
It is a worthy idea (I have been asked for this also) to try to get an audit of changes so perhaps someone else has cracked this one or has some futher ideas.
|
|
|
|
Anton Hinxman
|
| Posted: 07/22/2004, 3:46 AM |
|
Dam it, just realised, - you/we would have to round trip to the database values in case the SQL event updates with stale data from the user.
I prevent stale data being posted with a user readable conflict/collision message so OK for me but 'if I did not do this then unchanged and out of date data would change/revert columns in a row and this would be an auditable change'. Such changes would not fire Listener events so this blows that one out as well – the problem also applies to your model object value compare idea.
It seems to get nasty and ugly no matter how you look at it.
If one has to round trip to the database then you do have the (DataObjectEvent e) e.getWhere() to fetch a pre-change row set of values. Still not 100% though (and expensive) unless you use a conflict resolution method as well. Would not work well with multi-table fetches.
Still thinking...
|
|
|
|
jos kelleners
|
| Posted: 07/22/2004, 12:10 PM |
|
I was thinking, is it possible to catch the database querys Codecharge does when it inserts,update or delete a record.
|
|
|
|
Anton Hinxman
|
| Posted: 07/26/2004, 4:57 AM |
|
Yes, sure that's the easy bit, try:
CCLogger.getInstance().debug("#1. getSql()= "+ e.getSql());
CCLogger.getInstance().debug("#1. getWhere()= "+ e.getWhere());
in BeforeExecuteUpdate(DataObjectEvent e), BeforeExecuteUpdate(DataObjectEvent e), and BeforeExecuteDelete(DataObjectEvent e).
I also use e.setSQL("#") to cancel a row of updates if a data collision is detected.
Instead of just dumping the getSQL() and getWhere() strings off into an Audit table how about cycling the sql parameters used to build the SQL statement being submitted?
Have a look at:
com.codecharge.db.SqlParameters sqlParams = (SqlParameters) e.getDataSource();
This way you can stash the where condition but actually detect and cross check the 'real' changes a user WILL make to a row.
The above SQLParameters offers 'Collection getSqlParameters()' which you can use to Iterate and the objects in a collection and these are of type com.codecharge.db.SqlParameter. SqlParameter objects offer you the ability to String getSourceName() so that should resolve to the column source.
If you do anything fancy with the data then you may need to resolve the actual table source used for the parameter. All that you will need is in these two classes - build as an unpacked JSP project to extract the CCS code for them. They are well worth examination.
For the sake of simplicity you could just record such parameters in a two tables. One parent table for the action and form identifier and a child table for the columns changed with one row per parameter submitted with the structures being:
ParentAudit table:
pauditid int(10) unsigned not null autoincrement
waschange DateTime default NOW()
formname VarChar(20) // your longest form name should fit here
actionof Char(1) // "U", "D", "I"
ChildAudit table:
cauditid int(10) unsigned not null autoincrement
pauditid int(10) unsigned not null // match to audit parent
columnof VarChar(30) // your longest column name fits here
valueof VarChar(200) // your longest <anything>.toString() fits here or where statement of a delete
Rather database i/o expensive and could mount up to be quite big and you still have the problem of stale values reverting changed columns in table.
I suppose that you could have two forms, of the same name on different pages, submitting different values so a page ref may be required in the parent Audit table.
Certainly, splitting of the getSQL() down into identified portions, by looking at the actual SQL build parameters, will help with the clarity of the Audit trail.
|
|
|
|
|
