Cayenne
  1. Cayenne
  2. CAY-414

Support for (batch) query interception and audit

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 1.2 branch
    • Fix Version/s: Undefined future
    • Component/s: Core Library
    • Labels:
      None

      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.

        Activity

        Andrus Adamchik created issue -
        Hide
        Ari Maniatis added a comment -

        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.

        Show
        Ari Maniatis added a comment - 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.
        Hide
        Andrus Adamchik added a comment -

        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.

        Show
        Andrus Adamchik added a comment - 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.
        Hide
        Ari Maniatis added a comment -

        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).

        Show
        Ari Maniatis added a comment - 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 made changes -
        Field Original Value New Value
        Assignee Andrus Adamchik [ andrus ] Mike Kienenberger [ mkienenb ]
        Hide
        Mike Kienenberger added a comment -

        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.

        Show
        Mike Kienenberger added a comment - 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 made changes -
        Attachment 1.2-audit.patch-2006-09-05-00.txt [ 10469 ]
        Mike Kienenberger made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Hide
        Mike Kienenberger added a comment -

        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).

        Show
        Mike Kienenberger added a comment - 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 made changes -
        Attachment ExampleOfCreatingAuditLogRecordsInSameCommit.zip [ 10470 ]
        Hide
        Mike Kienenberger added a comment -

        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.

        Show
        Mike Kienenberger added a comment - 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 made changes -
        Fix Version/s 1.2 [STABLE] [ 10030 ]
        Hide
        Mike Kienenberger added a comment -

        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);

        Show
        Mike Kienenberger added a comment - 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);
        Hide
        Mike Kienenberger added a comment -

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

        Show
        Mike Kienenberger added a comment - Note that there's currently a 2.0 version of this code mixed in with the 2.0 patch for CAY-560 .
        Hide
        Ari Maniatis added a comment -

        Can we close this issue now that lifecycle events are in 3.0?

        Show
        Ari Maniatis added a comment - Can we close this issue now that lifecycle events are in 3.0?
        Ari Maniatis made changes -
        Fix Version/s 1.2 branch [ 10030 ]
        Henri Yandell made changes -
        Project Import Sat Mar 21 00:51:04 PDT 2009 [ 1237621864637 ]
        Ari Maniatis made changes -
        Fix Version/s Undefined future [ 12313763 ]
        Ari Maniatis made changes -
        Workflow jira [ 12456718 ] Cayenne workflow [ 12487771 ]
        Mike Kienenberger made changes -
        Status In Progress [ 3 ] Open [ 1 ]
        Hide
        Mike Kienenberger added a comment -

        Ari is correct. 3.x and onward have lifecycle events that should provide the necessary hooks for performing auditing.

        Show
        Mike Kienenberger added a comment - Ari is correct. 3.x and onward have lifecycle events that should provide the necessary hooks for performing auditing.
        Mike Kienenberger made changes -
        Status Open [ 1 ] Closed [ 6 ]
        Resolution Won't Fix [ 2 ]

          People

          • Assignee:
            Mike Kienenberger
            Reporter:
            Andrus Adamchik
          • Votes:
            4 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development