Friday, March 25, 2016

ADF BC Version Number and Change Indicator to Track Changed Rows

One of the common use cases in enteprise applications is to track concurrent user changes. There are two types of changes possible - when two real users are changing data in the same row, or when single user is changing data and same row is updated by PL/SQL procedure/function (all happen in the same user session). In the first case, we would like to inform a user - row data was changed (two different users changing data). In the second case, there is no need to inform user (data wasn't changed by another real user, it was changed by PL/SQL function/procedure invoked in the same session).

ADF BC by default tracks all attributes and checks if they values were changed. This would mean, if any attribute value is changed directly in DB, EO cache will be out of synch and changed row error will be reported. We can minimize number of attributes to be checked to single attribute - Version Number. There must be additional number type column created in DB table, we are going to use it for Version Number in the EO. EO attribute should be marked as Change Indicator and set to be "version number" in Track Change History property. This means, row will be considered as changed by another user, only if Version Number attribute value will be changed. All changes happening directly from PL/SQL will not increment Version Number (it will be incremented only for the changes submitted from ADF BC):

There should be new column created in the DB for Version Number attribute:

Let's see how it works. I will use two different browser sessions, to simulate two users. Change value for First Name, take a look into Version Number. Value is equal to 4, before Save:

Data is saved successfully to DB. New value 5 is assigned automatically for Version Number:

Switch to different session now. Version Number is equal to previous value 4 (this session was opened before recent commit from first session). Both sessions are working with the same row. Change value for Salary:

Try to save this change. You will get standard error message, informing user about changes in the same row. This happens, because Version Number value doesn't match with recent Version Number for the same record:

At the same time, Version Number is synchronized automatically (updated to 5) and user can try to commit his changes again. This time data is saved and Version Number is increased to 6:

We should check now, how it works data is changed in the DB directly, without increasing Version Number (the case of update happening from PL/SQL). I'm changing Employee_Id = 100 record directly in DB (changing Salary value). Version Number is not changed:

Go back to Web session and change First Name value, press Save:

Despite data was changed in DB, save was successful - Version Number was recent. First Name was successfully changed and Version Number was increased to 7:

Download sample application (make sure to create manually VERSION_NO(10, 0) column in the EMPLOYEES DB table) -

No comments: