One important bit that I left out....
Depending on the database you're using, it might be difficult for the
delete triggers to easily know the current transaction to update the
responsible transaction ID (since on deletes, we're obviously not
sending data). In Oracle, I use a trigger on the transaction table to
update a temporary table that has a lifetime of just the current
transaction. The insert trigger on the transaction table inserts a
row into the temporary table with the OID of the new transaction, and
the delete triggers read from that table to know the responsible trans
ID.
Ken
On Dec 19, 2008, at 5:37 PM, David Holt wrote:
I agree. Thanks for sharing, Ken. I have been thinking about audit
for a long time, and your implementation seems like a very
reasonable one.
David
On 19-Dec-08, at 2:07 PM, Hugi Thordarson wrote:
Ken, that's actually... Really, really cool!
Thanks for taking the time to write this, I'm currently wrapping my
head around how to incorporate it in The Perfect DBMS Independent
Audit Trail™ :-).
On the same note; does anyone know if Wonder's audit trail is any
good?
Cheers,
- Hugi
// Hugi Thordarson
// http://hugi.karlmenn.is/
On 19.12.2008, at 18:58, Ken Anderson wrote:
I use a hybrid approach...
I use the database to copy every row modified or deleted to an
audit table. Every object has a trans_id field, which is a
foreign key relationship to a transaction table. The primary key
of that table increases like regular unique integer primary keys.
In EOF, I have sub-entities of all my EOs that have a prefix (like
Aud...). These EOs are also subclasses of their main
counterparts, then have an imported text file that represents the
code I want all audit EOs to share (here's a good case for
multiple inheritance!).
The Aud.. EOs have an additional real column called resp_trans_id
(the transaction RESPonsible for causing the row to move to the
audit table), plus an additional 'fictitious' attribute called
'asof_trans_id'. This is the trans_id that you want the entire
object structure to be 'as of'.
The primary key of the audit EOs is the oid AND asof_trans_id so
you can have multiple historical audit EOs in the EC.
The Aud EOs then have store procedures for fetching single objects
(faulting), that respects the asof_trans_id. The stored procedure
finds the right object for that asof_trans_id. For instance, if I
have a fault:
AudOrder oid = 72, asof_trans_id = 155
the stored proc first checks to see if the trans_id of the primary
Order table is less than 155. If it is, then this object hasn't
changed since trans_id 155, and the primary row is returned (but
an AudOrder object is still the object created). If not, we find
the audit row who's trans_id is less than or equal to 155. If
none exists, the fault fails.
In the AudOrder entity, you can decide whether to override
existing relationships (like items) to be from audits, or for
reference data, you could just keep the original relationship.
For to-many audit relationships, you need another stored proc that
will build the unique set, and the asof_trans_id value has to
travel along (part of the relationship keys). So, the items
relationship would be replaced with an items relationship to
AudItem. The stored procedure would build a result set that
includes all the items ASOF trans_id 155 (a union between the
primary table and the audit table).
Primary entities have a method called 'auditObjects' that goes out
and gets all the historical versions of an object.
Whew!
OK - NOW, you have the ability to say:
I have this order EO. Give me the top 10 historical versions...
You'll get an array of 10 AudOrder objects, which you can present
to the user.
You can display the date/time of the transaction record that the
audit is related to, so the user can pick the version of history
they want.
Now that the user has selected an AudOrder object, when you fire
the items to-many fault, it runs the stored proc that builds the
union of unique objects that existed asof trans_id 155.
You can keep going and going, faulting more historical objects
over time.
Cool, huh?
Ken
On Dec 19, 2008, at 12:25 PM, Hugi Thordarson wrote:
I considered that, it is a good approach since it would log all
changes to the DB regardless of the application used to access
it. But it falls short since creating an interface to access that
old data is complex (meaning that we'd have to create a separate
non-WO (or heaviliy modified EOF) application to access old data,
or relegate all requests for old data to DBAs, which is not an
option). Unless you know something I don't :-).
What we need is a simple way for (all) users to see modifications
logs for all records; Who changed What When.
Besides, I want to do this in a non-database-dependent fashion.
We're currently using Oracle (which is serious overkill for our
application) and we're bleeding money through our back ends for it.
Cheers,
- Hugi
On 19.12.2008, at 17:05, Dov Rosenberg wrote:
Most of the projects I worked on with this requirement used the
built in audit tracking functions of the database. Most DBA’s
didn’t leave the requirement to the developers to enforce. That
way everything is tracked in a consistent fashion across
applications.
Dov Rosenberg
On 12/19/08 11:57 AM, "Hugi Thordarson" <h...@karlmenn.is> wrote:
Well, for each off-topic post, I guess you must send at least
one on-topic post :-).
So, I work on government databases, and a big part of our
requirements revolves around keeping track of changes. We need
to know exactly how our databases looked at some point in time,
and we need to know who made what modifications when.
So I'm curious about how people are implementing transaction
logging. Attached is a (simplified version of a) class that
demonstrates how I do this today - you can plug it into your
application by calling Spy.register(), for example in yor
application constructor (Oh - and you should probably change
the createAndInsertTransactionForEO() method to use something
other than that EO class called "Transaction" ;-).
Does anyone have any transaction logging stories, examples or
code to share? What is the best way to do this?
Cheers,
- Hugi
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/drosenberg%40inquira.com
This email sent to drosenb...@inquira.com
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/kenlists%40anderhome.com
This email sent to kenli...@anderhome.com
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/programmingosx%40mac.com
This email sent to programming...@mac.com
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
