cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Kienenberger" <>
Subject Re: temporal database generation
Date Sat, 26 Aug 2006 14:58:31 GMT
The main difference is that Tomi needs to have automatic criteria
applied to every query he makes.   Ie, a "WHERE time between
effectivestart and effectiveend" for every select, insert, and update.

What I need, and what Eric sounds like he needs, is for an AUDIT_LOG
record to be created for every insert, update, and delete.   Actually
one for every field changed in an insert or update.

For example, here's what the fields of an audit record look like for
me (xml definition).

<?xml version="1.0" ?>
    <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" />

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

On 8/25/06, Eric Lazarus <> wrote:
> Tomi
> Please help me/us to understand what you need and how it differs from what I am talking
about (or what additional functionality you need.)
> Thanks
> Eric
> Tomi NA <> wrote: On 8/25/06, Mike Kienenberger  wrote:
> > Hey Eric,
> >
> > By the end of the month, I will have audit logging like you've
> > described working.   Probably Monday or Tuesday.   If it involves code
> > changes to Cayenne, it'll probably take at least another week to get
> > them committed.
> I'm looking forward to seeing how it'll work. From the JIRA issue you
> linked to, it didn't seem like the functionality I described, but if
> you say it's what Eric described he needed, then it's (for the most
> part) the functionality I need, as well.
> ---------------------------------
> Do you Yahoo!?
>  Everyone is raving about the  all-new Yahoo! Mail.

View raw message