Support for (batch) query interception and audit

• Key: CAY-414
• Type: Improvement
• Status: In Progress
• Priority: Major
• Assignee: Mike Kienenberger
• Reporter: Andrus Adamchik
• Component/s: Cayenne Core Library
• Affects Version/s: 1.2 [STABLE]
• Fix Version/s: 1.2 [STABLE]

Description

There is a common need to audit what Cayenne saves to the database. Currently people implement custom solutions, usually based on inspecting ObjectStore or overriding validateFor* on DataObjects. Due to a mismatch between object model and DB schema (after all ORM is intended to hide many DB details, think flattened relationships and such), clean audit is not always possible to be done that way.

Ideally the solution should be intercepting BatchQueries, as this is what is being sent to the database in its raw form. Audit log should be commitable to the DB (two scenarios were mentioned: log committed in the same or in a separate transaction from the main change).

The discussion of the possible design started in this thread:

http://objectstyle.org/cayenne/lists/cayenne-devel/2005/12/0087.html

Not sure if we should implement a standard "cayenne audit" feature, or simply provide needed extension points and post an audit solution as an example. I am more in favor of a second option (simple extension point) as the actual audit requirements may vary from app to app.

Comments

[Ari Maniatis]

I'd like to also vote for the second more flexible option. We want to use this mechanism as a way to save related data in an accounting system. So every new 'invoice line', 'payment line', etc. creates a pair of records in an 'AccountTransaction' table with the appropriate general ledger accounting information. In addition, if we decide to denormalise our database to store redundant information (eg. keeping Contact.owing to avoid slow queries across lots of relationships) then we have a simple reliable place to put the code which maintains this redundant field.

This hook needs to run after the commit is inevitable (that is, after all validation has been done), but be able to create/modify records which are part of the same database transaction as the rest of the context changes.

Would it be possible to have two new hooks: onSave() and onDelete(), or a single onCommit() hook? These would run once for every persistent object being saved. In three tier, this code should run on the server.

[Andrus Adamchik]

Actually many of these things are doable via a TransactionDelegate now... It is just a matter of organizing things into something usable, but the hook is there and it allows to commit/rollback in the same DB transaction.

[Ari Maniatis]

OK. That is very helpful. I can see how it might work. But from http://www.objectstyle.org/confluence/display/CAYDOC/Understanding+Transactions it seems that we would normally have one TransactionDelegate per domain. So if we commit several entities in one context we would need to iterate through each entity in the context and perform the appropriate trigger code. So if we commit five invoices, each one would need to update Contact.owing.

Ideally we would have willCommit, didCommit, etc available within the entity class. In the meantime we could probably achieve something similar with reflection from the TransactionDelegate but it isn't quite as nice.

We don't want to use "User-Defined Transaction Scope" because this code should be invisible to the client (in a three tier architecture).

[Mike Kienenberger]

Here's the cayenne changes needed for Cayenne 1.2 to implement a DataContextDelegate.finishedRunQueries(DataContext, List queries) method and to augment the BatchQueries to track necessary auditing information.

[Mike Kienenberger]

ExampleOfCreatingAuditLogRecordsInSameCommit.zip contains one way you can translate BatchQueries into audit records in the same commit.

Here's what the fields of an audit record look like in this example.

<?xml version="1.0" ?>
<table>
   <column java.sql.Types="4" keyOrder="1" name="ID" nullable="false"
position="5" primaryKey="true" type="INTEGER" />
   <column java.sql.Types="12" keyOrder="0" name="SCHEMA_NAME"
nullable="true" position="11" primaryKey="false" size="32"
type="VARCHAR" />
   <column java.sql.Types="12" keyOrder="0" name="TBL_NAME"
nullable="true" position="13" primaryKey="false" size="32"
type="VARCHAR" />
   <column java.sql.Types="12" keyOrder="0" name="COL_NAME"
nullable="true" position="1" primaryKey="false" size="32"
type="VARCHAR" />
   <column java.sql.Types="12" keyOrder="0" name="MOD_TYPE"
nullable="true" position="7" primaryKey="false" size="1"
type="VARCHAR" />
   <column java.sql.Types="12" keyOrder="0" name="OLD_VALUE"
nullable="true" position="9" primaryKey="false" size="2000"
type="VARCHAR" />
   <column java.sql.Types="12" keyOrder="0" name="NEW_VALUE"
nullable="true" position="8" primaryKey="false" size="2000"
type="VARCHAR" />
   <column java.sql.Types="4" keyOrder="0" name="FOREIGN_KEY"
nullable="true" position="4" primaryKey="false" type="INTEGER" />
   <column java.sql.Types="12" keyOrder="0" name="FKEY_CONDITION"
nullable="true" position="3" primaryKey="false" size="200"
type="VARCHAR" />
   <column java.sql.Types="93" keyOrder="0" name="MOD_TIME"
nullable="true" position="6" primaryKey="false" size="6"
type="TIMESTAMP" />
   <column java.sql.Types="4" keyOrder="0" name="SYSTEM_ID"
nullable="true" position="12" primaryKey="false" type="INTEGER" />
   <column java.sql.Types="4" keyOrder="0" name="EFFECTIVE_USER_ID"
nullable="true" position="2" primaryKey="false" type="INTEGER" />
   <column java.sql.Types="4" keyOrder="0" name="REAL_USER_ID"
nullable="true" position="10" primaryKey="false" type="INTEGER" />
</table>

ID is meaningless primary key
SCHEMA_NAME.TBL_NAME.COL_NAME describes path to data changed.
MOD_TYPE is insert, delete, or update (I,D,U)
OLD_VALUE and NEW_VALUE are the state changes.
FOREIGN_KEY and FKEY_CONDITION are references to the record that was changed (FK_C is for compound keys, FK is for a single integer key).
MOD_TIME is when the record was changed.
SYSTEM_ID/EFFECTIVE_USER_ID/REAL_USER_ID describes where the data was changed. (application and user).

[Mike Kienenberger]

Note that the specifics of what to do with the generated audit records is encapsulated in processAuditRecordMapList() -- override this method to do your own processing of the audit records.

[Mike Kienenberger]

By the way, there's one bug to correct in my example code:

AuditLoggingDataContextDelegate.java:line 142 should be deleted. (It duplicates the section above it, but without the null check.)

if (null != newValue)
{
auditRecordMap.put("NEW_VALUE", newValue.toString());
}
auditRecordMap.put("NEW_VALUE", newValue);

[Mike Kienenberger]

Note that there's currently a 2.0 version of this code mixed in with the 2.0 patch for CAY-560.