cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Kienenberger <mkien...@gmail.com>
Subject Re: Obtain primary key for DataObject before commitChanges
Date Wed, 12 Aug 2015 15:05:05 GMT
On Tue, Aug 11, 2015 at 8:49 PM, Aristedes Maniatis <ari@maniatis.org> wrote:
> I'm interested in a different part of the problem you are solving. Other than the relationship
data discuss here, how are you storing the actual changes in your audit table? Does your problem
just require "Bob changed record 23" or are you keeping a complete "diff" of the changes?
If the latter, how are you serialising those changes into the audit log?

I've been involved with auditing in at least three projects over the
last 15 years, and have handled them similarly each time, although we
also have used record-level logging (adding date/time of last change
to each record) in addition in one project.

This is a combined summary of what I've used in the past and what I'd
probably use in the next project, along with any particular
application-specific fields, such as REAL_USER_ID and
EFFECTIVE_USER_ID.

    <db-entity name="LOG">

        <db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
isMandatory="true" length="22"/>
        <db-attribute name="MODIFICATION_DATE" type="TIMESTAMP"
isMandatory="true"/>
        <db-attribute name="MODIFICATION_TYPE" type="VARCHAR" length="1"/>
        <db-attribute name="TABLE_NAME" type="VARCHAR"
isMandatory="true" length="32"/>
        <db-attribute name="COLUMN_NAME" type="VARCHAR"
isMandatory="true" length="64"/>
        <db-attribute name="FOREIGN_RECORD_KEY" type="INTEGER" length="22"/>
        <db-attribute name="FKEY_CONDITION" type="VARCHAR" length="200"/>
        <db-attribute name="OLD_VALUE" type="VARCHAR" length="4000"/>
        <db-attribute name="NEW_VALUE" type="VARCHAR" length="4000"/>

        <db-attribute name="EFFECTIVE_USER_ID" type="INTEGER" length="22"/>
        <db-attribute name="REAL_USER_ID" type="INTEGER" length="22"/>
   </db-entity>

It's a complete diff of any database change, when it was made, and who
made it.   If necessary, it can be used to reverse a change or replay
a change back.    Not only does it fulfil general auditing purposes,
but it's been very helpful in debugging what an end-user really did as
opposed to what they claim they did.   It could potentially be hard to
determine what data was committed together since it's at such a
fine-grained level and the application is multi-threaded.   Perhaps
adding a commit id would be a slight improvement, but the
MODIFICATION_DATE timestamp has been sufficient so far.

For a schema where every record has a single primary key of a specific
type (such as integer), you only need FOREIGN_RECORD_KEY.   If you
have compound primary keys, then you need to also use FKEY_CONDITION
which is a string-representation of your primary key.

Mime
View raw message