Forum OpenACS Development: a Model to store Deleted Rows History

1: a Model to store Deleted Rows History

Posted by Samer Abukhait on 12/12/03 12:20 AM

Oraclers,

Did any one of you think of/implement a general dynamic DB Model to store a history for every deleted row in any table, something to work with an after delete trigger on each table.

My initial thinking is something like to store the filed name and field value.

Or this is a far impractical something to think about?

2: Re: a Model to store Deleted Rows History (response to 1)

Posted by Jeff Davis on 12/12/03 12:34 AM

ACS 3 used audit tables like that and they work (and are reasonably fast) so it's easy enough to do. Generally it's better to have a table which is a duplicate of the audited table to hold the deleted records (you can create a copy like that with "create table foo_audit as select * from foo where 0 = 1;").

With the content repository, cr_revisions just holds everything and you point at the live revision from cr_items. The disadvantage of that audit method is that you can't have unique constraints on cr_revisions columns (the advantage is rolling a revisions back or forth is trivial).

3: Re: a Model to store Deleted Rows History (response to 1)

Posted by Samer Abukhait on 12/12/03 01:04 AM

Deleted rows are meant to be history no more.. They should not be modified or deleted, so all constraints are valueless in them... ?

Plus, implementing one way in the system is much easier than creating a copy for every audited table??

I am talking about all tables in the DB, not only content or so; also an update history model is also in the same level to think in.
What you think?

If ACS 3 Model for auditing is available, can we check and discuss it?

4: Re: a Model to store Deleted Rows History (response to 1)

Posted by Tom Jackson on 12/12/03 01:14 AM

You can check the ecommerce package for one take on auditing.

5: Re: a Model to store Deleted Rows History (response to 1)

Posted by Andrew Piskorski on 01/02/04 08:33 PM

The ideal solution here is an RDBMS with really good native bi-temporal support, but AFAIK no such database exists, nor have I heard of anyone currently working on any such features.