Saturday, January 21, 2017

Re: audit trail functionality in database model

Enrico,

I've done this in the past.  I didn't use Django directly for the audit
tables.  Instead I defined DB triggers on each primary table that
inserted a row of audit values into the audit table.  I can send you
sample trigger code that works in Oracle and MySQL.

The nice things about this approach are that Django can entirely
ignore the audit tables, manipulating only the primary tables, and
that audit table entries are ALWAYS created, even of you bypass
Django and do a direct INSERT, UPDATE, or DELETE to a primary
table.

You should still be able to define the audit tables as Django models,
to get the benefit of schema migrations, etc., and to make it easier
to write an audit trail viewer.

In my case, I didn't have an explicit "version" field in the primary
tables, but you could have the triggers handle that also.  Or you
could do it in the Django save() method of each model, or in a
custom manager.

If you go the custom save() or custom manager route, you could
do all of the work there, and avoid the need for triggers, but then
it would be easy to bypass the audit table by doing a direct INSERT,
UPDATE, or DELETE to a primary table.

--Fred

Fred Stluka -- mailto:fred@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

On 1/21/17 7:15 AM, enrico baranski wrote:
Hi all Django users,

I'm quite new to Django and currently experimenting with the database model. Defining fields appears to be quite intuitive and is well described in the documentation. However, I am looking into audit trail functionalities. What that means to me. I have two tables, one is my master data table (rooms) and one is my audit trail table for the rooms table.

So I aim on two major things, first I would like to increment a field "version" in my room-table to track any change on the table record. Inserting the record means version=1 and as soon as the record is changed the field version should increment to 2 and so on.

Second thing would be to automatically track the changes from one version to another in my audit trail table. So I am looking for a way to automatically make table entries when I update the main table ...

My final goal is to define the audit trail functionalities in the database models so it is forced on any record manipulation.

I hope I could describe my issues comprehensible and would be very happy to get some feedback from you guys.


Thanks a lot and best regards,
enrico
--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8f9d2c8e-3fd9-4e75-b628-4e6ff99ee83c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

No comments:

Post a Comment