Projects:Reduce Audit Trail Infrastructure Overhead/Technical Specifications
Contents |
Problem definition
Audit Trail functionality allows to track any modification in a set of configured tables. Because Openbravo can modify data in different ways (DAL, DB triggers, sqlc...) the mechanism to maintain this audit is through DB triggers they read context information from AD_Context_Info temporary table. As per initial definition, this table is populated with current session's information whenever a DB connection is borrowed from pool.
Even the time spent for each time this information is set is very small, it has been proved it causes an overall overhead.
Setting info only when required
The main issue is this information is blindly set whenever a connection is borrowed from pool, at a moment that is it unknown what that connection will be used for.
Ideal scenario
Ideally this information should only be set when it is going to be used, for example if the only audited table is C_Order, only when a modification in that table is going to be performed connection should receive the information, skipping it in the rest of cases.
Unfortunately, because modifications can come from different sources, mainly from DB triggers, it is not possible to know if a modification in a different table will finally cause a change in any other, for example modifying C_OrderLine can cause changes in C_Order, and because this information is only available at code level, it is not possible to be that restrictive.
Pragmatic approach: insert/update/delete
The vast majority of connections are borrowed from pool for read only queries. If we, at least, were able to set context information when the connection is used for insert/update/delete statements (as well as DB procedure invocations) the actual overhead would be greatly reduced.
To implement this approach it will be needed to move this set from connection borrow to a later stage, just before any DB modification.
There are some different cases which must be treated differently:
DAL
Whenever DAL is used in a standard manner to perform modifications in DB, it is possible to know whether there are modifications in the cache before they are executed in DB, only in these cases context information will be set.
SQLC
Statements in SQL are already treated so that insert/update/delete can be controlled to generate different code that handles it properly.
Uncontrolled cases
Other situations (such as direct modifications through JDBC) would be skipped from the two previous cases. Even they are no that common, they are already present in Openbravo base code. Most of them are executed within processes and background processes, because processes are in most of the cases intended to modify data, they will continue setting context information.
Still if some code isn't covered by the previous cases, it will be developers responsibility to invoke the API to set context information: org.openbravo.database.SessionInfo.saveContextInfoIntoDB(Connection)