I've a question about field versionning, in order to have an audit log on all changes (insert/update/delete) done to a database (with the user name, and other business information). The audit have to give the field value before and after the change.

So I'm looking to field version on the entity. I think to something like : - make a version after the fetch ("OriginalVersion") - make a version before the save ("CurrentVersion") - Have a script, after the change (if it suceed) that will revert to versions, read values , and then store it somewhere (I think to send it to a msqueue).

So questions are : - Is it realistic to think to such a solution in term of performance ? - If there any way to know if a given version exist ? because I've only see the methods "SaveFields" and "RollbackFields", nothing to test if a version exists.

There are some threads that were discussing this issue in a way or another: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4553 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5473 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3612

Your links doesn't really talk about the idea exposed above, and don't answer to my 2 questions.

Fabrice wrote:

I've a question about field versionning, in order to have an audit log on all changes (insert/update/delete) done to a database (with the user name, and other business information). The audit have to give the field value before and after the change.

So I'm looking to field version on the entity. I think to something like : - make a version after the fetch ("OriginalVersion") - make a version before the save ("CurrentVersion") - Have a script, after the change (if it suceed) that will revert to versions, read values , and then store it somewhere (I think to send it to a msqueue).

This is already available inside the field. A fieldobject has a CurrentValue property and a DbValue property. The DbValue property is the value in the db, the CurrentValue property is the value currently in the entity.

You can version EntityFields(2) objects, using SaveFields(name) and RollbackFields(name).

So questions are : - Is it realistic to think to such a solution in term of performance ? - If there any way to know if a given version exist ? because I've only see the methods "SaveFields" and "RollbackFields", nothing to test if a version exists.

I'm not sure what you're trying to do at runtime with the version information.

I've to log all changes and the values before and after the changes So, ie if the name of an employee have change, I've to be able to give the old value and the new one... (and when it was changed, by who, etc.)

So I see somethink like : 1) Store current version 2) Rollback to old version 3) Store old version

Of course this database will be quite big

• Is it realistic to think to such a solution in term of performance ?

It is realistic as long as you need it.

• If there any way to know if a given version exist ? because I've only see the methods "SaveFields" and "RollbackFields", nothing to test if a version exists.

Let me see if I understand you correctly, you want to audit changes done on fields, upon save actions, right?

If so, then you can handle on of the Events availabe "OnSaveEntity" (for a list of those events that you can intercept, please check the LLBLGen Pro documentation "Using the generated code -> Adapter -> DataAccessAdapter Functionality -> Intercepting activity calls").

Then on your event handling code you might loop on the Entity's changed fields (EntityField.IsChanged = true) then you might use EntityField.CurrentValue (the new value), and EntityField.DBValue (OldValue, fetched from the database).

If I'm speaking on a different subject, please clear things to me and explain your goal in details.

Thanks

You are storing your audit records in different tables (than your Entity table) right?

Then I don't see why you need to Rollback to the old values and store it, rather just create a new Entity from you AuditEntity, assign to it the Field OldValue & the New Value (as shown before, using your Original Entity's Field DBValue & Current Value) Then Save you AuditEntity in the same transaction of your Original Entity Saving call.

Yes you're almost right, the DBValue can be a solution. But I can't assume the DBValue is filled in all case because these entities can go accross the network using a serialisation via DataSet (because original xml serialisation of entities take too much place). So when going on the network, this value is lost. When the data come back, I've the old value in the dataset versionning, but I can't set the DBValue of an entity : it's of course a read-only property. I can try to use the SaveFields() only with entities that come from the network... it could be more performant. Thanks for the tip.

It is realistic as long as you need it.

No, if it take too much resource that it will break down our production server on peak time, no for me it's not realistic

You are storing your audit records in different tables (than your Entity table) right?

Yes but not in a different table, but in a different database. And on production it could also be on a different sql server because it'll have a huge number of insert. The "live" database will send data to audit db via msqueue (send and forget). The audit db have a schema completely different from the original database, because we focus on field changed

So as I see, I shouldn't have too much problem by using the SaveFields() ? And my second question, any way to know if a version exists ?

Thanks for your help

ps: It seem I've an old version of the doc because I don't see the "Intercepting activity calls" chapter. I'll try to get an new "old" version, because I'm working with version llblgen 1.0.2004.2 (and my doc is 1.0.2004.1).

Fabrice wrote:

Yes you're almost right, the DBValue can be a solution. But I can't assume the DBValue is filled in all case because these entities can go accross the network using a serialisation via DataSet (because original xml serialisation of entities take too much place).

Also in compact form?

So when going on the network, this value is lost. When the data come back, I've the old value in the dataset versionning, but I can't set the DBValue of an entity : it's of course a read-only property.

You can: entity.Fields[index].ForcedCurrentValueWrite(entity.Fields[index].CurrentValue, dbValue);

I left this method public for low level access if you need to.

You are storing your audit records in different tables (than your Entity table) right?

Yes but not in a different table, but in a different database. And on production it could also be on a different sql server because it'll have a huge number of insert. The "live" database will send data to audit db via msqueue (send and forget). The audit db have a schema completely different from the original database, because we focus on field changed

So as I see, I shouldn't have too much problem by using the SaveFields() ?

The SaveFields are there for versioning in memory if you have needs to rollback. If you simply want to track who changed what and not for rollback purposes, the SaveFields aren't an option, as it just versions data for rollback, not for who changed what. You have to bind to the fieldchanged events to track changes.

Also in compact form?

Well the transport layer (using Typed Dataset) is now quite old (1,5 years, maybe 2) and at this time the compact form didn't exist (we allready had a topic on this subject at this time). Right now it's quite hard to change it because on the client side we don't have a good design with separated layer...

You can: entity.Fields[index].ForcedCurrentValueWrite(entity.Fields[index].CurrentValue, dbValue);

I left this method public for low level access if you need to.

Yessss it'll help me a lot, thank you I missed this methods, I think it wasn't available when I done the tool that convert an collection into a dataset (and vice-versa). Ok as I can overwrite the db value, a don't need the version anymore

Thank you for the help! (and sorry for the late answer, I took some holidays )