Re: audit trail functionality in database model

[enrico baranski]

@Fred: Thanks a lot, that really helped me! 

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+unsubscr...@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/93d2fa48-adcf-4070-bee2-4aecda830c51%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: audit trail functionality in database model

[Ryan Castner]

Django Field History is a great project to audit trail a model field

On Saturday, January 21, 2017 at 7:19:57 AM UTC-5, 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+unsubscr...@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/e374c953-17bf-47ae-b74e-34766910dc1c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: audit trail functionality in database model

[Melvyn Sopacua]

On Saturday 21 January 2017 04:15:34 enrico baranski wrote:
> 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.

Don't reinvent the wheel. There's more then enough to 
choose from:
https://djangopackages.org/grids/g/model-audit/ 
-- 
Melvyn Sopacua

-- 
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+unsubscr...@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/1757600.EvdFKAIeO7%40devstation.
For more options, visit https://groups.google.com/d/optout.

Re: audit trail functionality in database model

[Fred Stluka]

  
  
Enrico,

In the sample MySQL trigger code of my previous message, you'll
see that I always store, in the primary table, the string username 
of the most recent user to update the table.  Therefore, that value
is available to the DB trigger as NEW.update_user.

--Fred
  
  
  Fred Stluka -- mailto:f...@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/22/17 7:37 AM, enrico baranski
  wrote:


  Thinking about this topic more detailed made me
realize that I also need to track the user who performed the
insert/change (delete is globally not permitted) actions.
However, that are user names managed via Django ... so when i
use DB triggers I only can track the MySQL user who is used by
the Django application. Probably that leads to the situation
that I need to take care of this in Django or does anyone have
another idea how to deal with that? 
  
  -- 
  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+unsubscr...@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/63c129b8-6279-4410-8242-ccffef134b6f%40googlegroups.com.
  For more options, visit https://groups.google.com/d/optout.


  




-- 
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+unsubscr...@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/193ab42b-0e2b-e81e-bd71-c1ab63ee7d60%40bristle.com.
For more options, visit https://groups.google.com/d/optout.

Re: audit trail functionality in database model

[Fred Stluka]

  
  
Enrico,

the DB trigger approach sounds very exciting
  to me because I really need to be sure that there is no way to
  manipulate records without audit trail. I also would be very
  interested in the trigger code for MySQL you mentioned. 

OK.  I'll append some sample code below.

You also mentioned that you did something
  similar in the past, what was your approach to store information
  in the audit trail table? At the moment, I have two ways in front
  of me and I am not quite sure which way to go. Assuming my primary
  table (rooms) stores the fields id, room, number, size and
  version. 
  
  So I could go for one record for each change with audit trail
  table fields like id, rooms_id, room_old, room_new, number_old,
  number_new, size_old, size_new, version_old, version_new. 
  Or with a record for each field, id, rooms_id, field, old, new.
  There for every field a record is listed. 
  
  Do you know other suitable approaches to store audit trail
  information or did you go one of the listed ways? My main aim here
  again is to be able to build reasonable audit trail views where a
  user can see the history of a record with easy query's behind it.
  

My approach was one audit record per changed primary record,
not one audit record per changed primary field.  More like your 
1st example above.  

But there's no need to store old and new values in the audit table.
Just store new values in the audit table.  You can find the old
value 
in a previous row of the audit table (sorted by creation time of the

audit table records), from when it was a new value being inserted 
or updated.

Make sure you store all new values in the audit table, including the

first INSERT.  Then you can see a complete audit trail in the audit

tables, even if the value has never changed.  In a previous project,

I made the mistake of only putting old values into the audit tables,
after they changed in the primary tables.  So, I had to look at the

audit tables for all old values and at the primary tables for the
current value, which was more complicated.

Be sure to use triggers for DELETE, not just INSERT and UPDATE.
Otherwise, the audit table won't have a record of whether the 
row was deleted.

Here are a set of tables (primary and audit) and triggers (INSERT,
UPDATE, and DELETE) for a typical table in my DB.  This is valid 
MySQL code.  In this code, you'll see some other conventions I 
always follow:

- Naming conventions of:
   -- Tables (prefixed with app name ("ITF" in this case))
   -- PKs (table name, plus suffix "_id")
   -- Constraints (prefixes like "pk_", fk1_", "fk2_", etc for PK
and
    potentially multiple FKs)
   -- Audit tables (prefixed with app name plus "A")
   -- Triggers ("ai_", "au_", "ad_" prefixes for audit triggers for

    INSERT, UPDATE, and DELETE)

- All primary tables contain these extra fields which dramatically 
   reduce the number of times you have to go to the audit tables:
   -- create_user (string name of user who created row)
   -- create_dt (date/time of row creation)
   -- update_user (string name of user who last updated row)
   -- update_dt (date/time of last row update)

- All primary tables contain this extra field, which I use to track
   the status of the row (active, inactive, archived, etc.).  I
never 
   actually delete a row.  Just mark it inactive, which makes 
   undelete possible w/o having to go to the audit table.
   -- status_id

- Each audit table has exactly the same fields as its primary table,
   plus 3 additional fields:
   -- audited_change_user (string name of user who made the change)
   -- audited_change_dt (date/time time of the change.'
   -- audited_change_type ('INSERT', 'UPDATE', or 'DELETE')

- Audit tables have no need for unique, FK, or PK constraints.  
   Just values.

Here's the code:

-- 
-- ITF_PRODUCT
-- 

DROP TABLE IF EXISTS itf_product;
CREATE TABLE itf_product
 (itf_product_id INTEGER  NOT NULL
AUTO_INCREMENT
   COMMENT
'Primary key'
 ,name   VARCHAR(255) NOT NULL COMMENT
'Unique user-assigned and user-visible name'
 ,descrip    VARCHAR(255) NOT NULL COMMENT
'Description'
 ,notes  VARCHAR(255) NOT NULL COMMENT 'User
notes'
 ,create_user    VARCHAR(255) NOT NULL COMMENT 'User
who created this database row.  String, not foreign key, to preserve
history when users are deleted.'
 ,create_dt  

Re: audit trail functionality in database model

[enrico baranski]

Thinking about this topic more detailed made me realize that I also need to 
track the user who performed the insert/change (delete is globally not 
permitted) actions. However, that are user names managed via Django ... so 
when i use DB triggers I only can track the MySQL user who is used by the 
Django application. Probably that leads to the situation that I need to 
take care of this in Django or does anyone have another idea how to deal 
with that? 

-- 
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+unsubscr...@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/63c129b8-6279-4410-8242-ccffef134b6f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: audit trail functionality in database model

[enrico baranski]

Hi Fed,

the DB trigger approach sounds very exciting to me because I really need to 
be sure that there is no way to manipulate records without audit trail. I 
also would be very interested in the trigger code for MySQL you mentioned. 

You also mentioned that you did something similar in the past, what was 
your approach to store information in the audit trail table? At the moment, 
I have two ways in front of me and I am not quite sure which way to go. 
Assuming my primary table (rooms) stores the fields id, room, number, size 
and version. 

So I could go for one record for each change with audit trail table fields 
like id, rooms_id, room_old, room_new, number_old, number_new, size_old, 
size_new, version_old, version_new. 
Or with a record for each field, id, rooms_id, field, old, new. There for 
every field a record is listed. 

Do you know other suitable approaches to store audit trail information or 
did you go one of the listed ways? My main aim here again is to be able to 
build reasonable audit trail views where a user can see the history of a 
record with easy query's behind it. 

Thanks for reply,
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+unsubscr...@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/7458b627-f70d-4613-b4c2-3fe53d67452d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: audit trail functionality in database model

[enrico baranski]

Hi Mike,

thanks for that reference, I will take a look. 


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+unsubscr...@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/075d3191-cb19-44bf-ba6f-f767cc0cb03b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: audit trail functionality in database model

[Fred Stluka]

  
  
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:f...@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+unsubscr...@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.


  




-- 
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+unsubscr...@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/88db55d7-f28b-3099-50a4-1ed3b674e5e4%40bristle.com.
For more options, visit https://groups.google.com/d/optout.

Re: audit trail functionality in database model

[Mike Dewhirst]

You could have a look at Marty Alchin's Pro Django (not really for 
beginners but ...) on page 263 where he shows how to do almost exactly 
what you describe. If you got that book it would accelerate your 
progress in Django anyway. The only downside is it was published in 2008 
and Django has moved on since then - but in a good way. Largely backward 
compatible. It is probably still all relevant and very valuable.


Mike

On 21/01/2017 11:15 PM, 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+unsubscr...@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.


--
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+unsubscr...@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/f8316766-1c6a-5bdc-1f9f-23daff0750b7%40dewhirst.com.au.
For more options, visit https://groups.google.com/d/optout.

Re: Audit trail code - avoid multiple foreign key issues?

[Stodge]

No progress so far. I know I can do this:

attrs[field.name] = copy.copy(field)
if attrs[field.name].rel:
attrs[field.name].rel.related_name = "audit_%s_%s" % 
(cls.__name__,
field.name)

But this doesn't seem to make a difference. I still get:

Error: One or more models did not validate:
box.testaudit: Accessor for field 'author' clashes with related field
'User.audit_Test_author'. Add a related_name argument to the
definition for 'author'.
box.testaudit: Reverse query name for field 'author' clashes with
related field 'User.audit_Test_author'. Add a related_name argument to
the definition for 'author'.
box.test: Accessor for field 'author' clashes with related field
'User.audit_Test_author'. Add a related_name argument to the
definition for 'author'.
box.test: Reverse query name for field 'author' clashes with related
field 'User.audit_Test_author'. Add a related_name argument to the
definition for 'author'.

Is it possible to override the related_name attribute at runtime?

On Dec 4, 9:04 am, Stodge  wrote:
> I'm playing around with the Audit code here:
>
>    http://code.djangoproject.com/wiki/AuditTrail
>
> The Caveats section mentions:
>
>     Likewise, it fails when there are multiple ForeignKeys? pointing
> to the same Model, as it doesn't support / compensate for
> related_name.
>
> Does anyone know if this caveat was ever addressed or how to do it?
>
> Thanks

--

You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Audit Trail

[Larrik Jaerico]

Sérgio,

Sorry about the **kwargs thing, my fault. I'm the one who patched
AuditTrail to reflect the newest version of Django, but since I have
some custom code in there I had to go line-by-line and figure out what
to bring back to the website. I missed the **kwargs thing, though, it
seems.

-Larrik Jaerico

On Aug 21, 10:25 am, Sérgio Durand <[EMAIL PROTECTED]> wrote:
> Hi Erick,
>
> It was working on r8339.
> I've updated django this morning to r8460 to and still working !!
>
> What is "history Manager" that you said ?
>
> look how i'm using audit trail:
> 1) i have my 'ordinary' model (without audit trail feature)
> 2) i get the audit trail code and drop it into site-packages python
> directory (it works if you put on root dir of your django project too)
> 3) put import line on your model
> 4) put an 'extra' attribute in your model (that i called 'history') to
> use audit trail.
> 5) and finnaly, run the magic command 'syncdb'.. or, sqlall ,
> you should see all the sql generated by django.
>
> follows my (very) simple example:http://dpaste.com/hold/72943/
>
> Try again with a simple example like that ... if did't work, send the
> error message...
>
> Good luck!!! ;)
>
> Sergio Durand
>
> erikcw escreveu:
>
> > Hi Sergio,
>
> > I updated my code with your changes, and it still doesn't seem to be
> > working.  I don't have the history Manager in my model.
>
> > Is it working for you?  Are you using the latest trunk?
>
> > Thanks!
> > Erik
>
> > On Aug 19, 12:15 pm, Sérgio Durand <[EMAIL PROTECTED]> wrote:
>
> >> Hi people,
>
> >> Finally i've got Audit Trail [1] working again !!! :)
> >> I'm not a django/python expert (i've started studying 2 months ago), but
> >> after fighting with "python manage.py validate" and searching in google,
> >> i've got the solution... the problem basically was missing **kwargs
> >> arguments in some handler functions (because changes made in r8223).
> >> Maybe this problem is very simple (in fact i think it is), but i've
> >> spent several hours (and learned a lot of things too) to get this working.
> >> This was my first code contribution and i'm very glad for that!!
>
> >> Sergio Durand
>
> >> [1]:http://code.djangoproject.com/wiki/AuditTrail
>
> >> ps: i hope i didn't make a code mistake and sorry my english !!! ;

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Audit Trail

[Sérgio Durand]

Hi Erick,

It was working on r8339.
I've updated django this morning to r8460 to and still working !!

What is "history Manager" that you said ?

look how i'm using audit trail:
1) i have my 'ordinary' model (without audit trail feature)
2) i get the audit trail code and drop it into site-packages python 
directory (it works if you put on root dir of your django project too)
3) put import line on your model
4) put an 'extra' attribute in your model (that i called 'history') to 
use audit trail.
5) and finnaly, run the magic command 'syncdb'.. or, sqlall , 
you should see all the sql generated by django.

follows my (very) simple example:
http://dpaste.com/hold/72943/

Try again with a simple example like that ... if did't work, send the 
error message...

Good luck!!! ;)

Sergio Durand

erikcw escreveu:
> Hi Sergio,
>
> I updated my code with your changes, and it still doesn't seem to be
> working.  I don't have the history Manager in my model.
>
> Is it working for you?  Are you using the latest trunk?
>
> Thanks!
> Erik
>
> On Aug 19, 12:15 pm, Sérgio Durand <[EMAIL PROTECTED]> wrote:
>   
>> Hi people,
>>
>> Finally i've got Audit Trail [1] working again !!! :)
>> I'm not a django/python expert (i've started studying 2 months ago), but
>> after fighting with "python manage.py validate" and searching in google,
>> i've got the solution... the problem basically was missing **kwargs
>> arguments in some handler functions (because changes made in r8223).
>> Maybe this problem is very simple (in fact i think it is), but i've
>> spent several hours (and learned a lot of things too) to get this working.
>> This was my first code contribution and i'm very glad for that!!
>>
>> Sergio Durand
>>
>> [1]:http://code.djangoproject.com/wiki/AuditTrail
>>
>> ps: i hope i didn't make a code mistake and sorry my english !!! ;
>> 

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Audit Trail

[erikcw]

Hi Sergio,

I updated my code with your changes, and it still doesn't seem to be
working.  I don't have the history Manager in my model.

Is it working for you?  Are you using the latest trunk?

Thanks!
Erik

On Aug 19, 12:15 pm, Sérgio Durand <[EMAIL PROTECTED]> wrote:
> Hi people,
>
> Finally i've got Audit Trail [1] working again !!! :)
> I'm not a django/python expert (i've started studying 2 months ago), but
> after fighting with "python manage.py validate" and searching in google,
> i've got the solution... the problem basically was missing **kwargs
> arguments in some handler functions (because changes made in r8223).
> Maybe this problem is very simple (in fact i think it is), but i've
> spent several hours (and learned a lot of things too) to get this working.
> This was my first code contribution and i'm very glad for that!!
>
> Sergio Durand
>
> [1]:http://code.djangoproject.com/wiki/AuditTrail
>
> ps: i hope i didn't make a code mistake and sorry my english !!! ;)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[Jason McVetta]

Here is my current thinking:

   - No changes to main model tables
   - Designate a model to be audited by including inner class "Audit"
   - One shadow table per audited model
   - Generate shadow tables for all audited models on post_syncdb
   - Only write to one DB, until multi-db support is completed for Django
   - Write audit record on post_save, w/ flag in one column indicating it
   was a post_save write
   - At the user's option (set by attribute on Audit inner class), also
   write an audit record on pre_save, w/ appropriate flag
   - Maybe include a cron script to discard old audit records, based on
   age or total number of records

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[Jason McVetta]

On 4/24/07, robin_percy <[EMAIL PROTECTED]> wrote:
>
> What about doing both?  Write a pre_save record indicating the
> operation about to be attempted.  And a post_save indicating the
> success of the operation, using a unique identifier to identify the
> pair.  Then if the post_save gets out of sync, you have a record of
> transactions that may be at fault.


I was considering something like that.  But would it require two additional
tables for every audited model?  Or I could have just one table for writing
post_save confirmation -- but how to create an identifier that is unique
across several tables?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[robin_percy]

What about doing both?  Write a pre_save record indicating the
operation about to be attempted.  And a post_save indicating the
success of the operation, using a unique identifier to identify the
pair.  Then if the post_save gets out of sync, you have a record of
transactions that may be at fault.

Just my two cents.

- Robin

On Apr 23, 2:07 pm, "Jason McVetta" <[EMAIL PROTECTED]> wrote:
> On 4/23/07, David Larlet <[EMAIL PROTECTED]> wrote:
>
>
>
> > It's a bit ugly to declare instance hidden variables (pre) but we need to
> > be
> > sure that the save/delete is effective (post) before logging it.
>
> It's not beautiful, perhaps, but I may end up doing something very similar.
> However, one requirement of my project is that the audit tables not require
> UPDATE or DELETE permission.  Which begs the question, if I write my audit
> record on the pre_save signal, how do I record the success or failure of the
> save() operation?
>
> Alternatively, I could write the audit record on post_save.  Although I
> can't think off the top of my head of an example where this would get out of
> sync with the main table, something about it feels risky.  But I will have
> to think about this one some more.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[Jason McVetta]

On 4/23/07, David Larlet <[EMAIL PROTECTED]> wrote:
>
> It's a bit ugly to declare instance hidden variables (pre) but we need to
> be
> sure that the save/delete is effective (post) before logging it.
>

It's not beautiful, perhaps, but I may end up doing something very similar.
However, one requirement of my project is that the audit tables not require
UPDATE or DELETE permission.  Which begs the question, if I write my audit
record on the pre_save signal, how do I record the success or failure of the
save() operation?

Alternatively, I could write the audit record on post_save.  Although I
can't think off the top of my head of an example where this would get out of
sync with the main table, something about it feels risky.  But I will have
to think about this one some more.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[Jason McVetta]

On 4/21/07, Tim Chase <[EMAIL PROTECTED]> wrote:
>
> Todd Schraml authored a good article in Dr. Dobb's Journal
> (http://www.ddj.com/dept/database/184406340) titled _Table
> Patterns & Changing Data_ where he discusses five patterns for
> keeping historical data, and the requirements that would lead to
> choosing one pattern over another.



Thanks for the article, Tim -- it looks informative.  :)

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[Jason McVetta]

On 4/20/07, Malcolm Tredinnick <[EMAIL PROTECTED]> wrote:
>
> In your descriptions below, you remove the ability for a developer to
> use manual primary keys, by the sound of it, since one of your special
> fields wants to be a single-column primary key. That's not invisible.


Does Django currently allow use of composite primary keys?  If so, I wasn't
aware. Either way, the only restriction I would want to put on the keying of
the tables is that they not use one of the reserved column names (e.g.
"record_is_deleted").

I wouldn't go as far as to say "significantly heavier" without a lot of
> profiling under operational loads. Using sensible table indexing,
> including functional indexes on databases that have them, should make
> that query perform reasonably well for many cases (gut feel based on a
> bit of experience with large databases).


Fair enough.  It definitely makes the queries more complex, since they must
always include statements to select the current, non-deleted record.  But
that doesn't mean the performance would be unacceptable.

A real drawback of this method that you don't mention is that adding
> audit support requires changing the database table. So you can't add it
> to standard (shipped with Django) or third-party modules easily.


Yes, I would prefer my solution be drop-in compatible with both standard
Django and typical third-party modules.  Which does argue against modifying
the model tables.

>  2. The audit table, as described above, is written seperately
> > from the working table used for reads.  It would be most
>
> You still have to change the main table for the model, though, right?
> Otherwise you won't be able to have more than one tuple of data for each
> model (due to constraint violations). This was a problem that existed in
> the design of the FullHistory work as well: how to store arbitrary bits
> of changed information without having to alter existing tables.


No, I will not need to change the main table for the model.  But as you
point out, the audit table's schema cannot simply be main table +
record-keeping columns.  Instead, its schema must be: main table with
modified contraints + record-keeping columns.

If you could find a way that all the audit-related information was in a
> separate table, I would be tempted to implement it similar to
> ContentTypes -- as a table that is related to the model(s) it governs
> (maybe there is a shadow audit table for each real model table, but
> that's a bit uncool from a database design perspective). The extra table
> join won't really hurt you with today's databases and proper indexing.


Keeping a shadow table for each audit table is looking like the best
solution to me right now.  Granted it does add a little clutter to the table
namespace -- but shadow tables will be created only for models that
explictly ask for them.   Is it worthwhile to make it possible to store the
audit shadow tables in a separate db?

I suspect you will be able to do most of it as a third-party app without
> needing to hack on core. Some monkey patching -- a term I hate because
> it suggests it is wrong somehow, but I'll stick with the common phrase
> -- of the QuerySet class might be necessary (replacing QuerySet with a
> version that understands how to get the "real" row for a model). You
> might be able to get away with subclassing QuerySet in a lot of cases,
> but I'm not sure that will give you everything.


If I am only interested in writing shadow tables to keep history, don't
think I would need to touch QuerySet.  Instead, just define some pre_save
and pre_delete callbacks for the dispatcher.  (And maybe  post_save and
post_delete, to verify that the save/delete had been successful?)

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[David Larlet]

2007/4/20, Jason McVetta <[EMAIL PROTECTED]>:
> I need to add real audit trail and change-notification support to an
> existing Django app and to one that is under development.  The best way to
> do this, it seems to me, is to add audit support to the Django framework
> itself.  My requirements are similar to those named by Paul Childs in his
> django-users post last June:
>

Here is what I have quickly done with signals (I don't know if it can
be helpful):

## signals.py

from django.contrib.admin.models import LogEntry, ADDITION, CHANGE, DELETION
from django.contrib.contenttypes.models import ContentType

""" Those functions allow application logging.

It's a bit ugly to declare instance hidden variables (pre) but we need to be
sure that the save/delete is effective (post) before logging it.

PROTO: For the moment, it used the admin LogEntry model which is not really
safe given the fact that django admin will be changed soon to include newforms.

TODO: Add diff in change_message argument of LogEntry when this is a CHANGE.
"""

def log_pre_save(sender, instance, signal):
try:
initial_object = sender.objects.get(id=instance.id)
instance._action_flag = CHANGE
except sender.DoesNotExist:
instance._action_flag = ADDITION

def log_post_save(sender, instance, signal):
LogEntry.objects.log_action(
user_id=instance.user.id,
content_type_id=ContentType.objects.get_for_model(sender).id,
object_id=instance.id,
object_repr=str(sender.objects.get(id=instance.id)),
action_flag=instance._action_flag)

def log_pre_delete(sender, instance, signal):
instance._object_repr = str(sender.objects.get(id=instance.id))

def log_post_delete(sender, instance, signal):
LogEntry.objects.log_action(
user_id=instance.user.id,
content_type_id=ContentType.objects.get_for_model(sender).id,
object_id=instance.id,
object_repr=instance._object_repr,
action_flag=DELETION)

## todo/models.py
from sibiocle.misc.signals import log_pre_save, log_post_save,
log_pre_delete, log_post_delete

[...]

dispatcher.connect(log_pre_save,signal=signals.pre_save,sender=Todo)
dispatcher.connect(log_post_save,   signal=signals.post_save,   sender=Todo)
dispatcher.connect(log_pre_delete,  signal=signals.pre_delete,  sender=Todo)
dispatcher.connect(log_post_delete, signal=signals.post_delete, sender=Todo)

Anyway, I'm really intersted in your work, let us know your progression ;-).

Regards,
David

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[Tim Chase]

> I will be working on this project as part of my Real Job(tm),
> so devoting time to it should not be a problem. However,
> before I begin coding, I want the community's input on a few
> issues.
> 
> What is the right way, at DB level, to implement the audit 
> trail?  I had two ideas:

Todd Schraml authored a good article in Dr. Dobb's Journal
(http://www.ddj.com/dept/database/184406340) titled _Table
Patterns & Changing Data_ where he discusses five patterns for
keeping historical data, and the requirements that would lead to
choosing one pattern over another.  While it's written in the
context of SarbOx, auditing can be needed even where SarbOx isn't
a concern.

While I know I had thought of each of these patterns
independently, I hadn't gathered such patterns into one place at
the same time to weigh benefits vs. costs.  I don't know the
answer to your "What is the right way, at the DB level, to
implement the audit trail" question, but my thought would be it
depends on the scenario.

I don't know if seeing the various common ways of keeping an
audit trail would help guide design decisions, but ya'll may find
the link helpful.

-tim




--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: audit trail support

[Malcolm Tredinnick]

Hi Jason,

On Fri, 2007-04-20 at 13:30 -0400, Jason McVetta wrote:
> I need to add real audit trail and change-notification support to an
> existing Django app and to one that is under development.  The best
> way to do this, it seems to me, is to add audit support to the Django
> framework itself.  My requirements are similar to those named by Paul
> Childs in his django-users post last June:
>   * rows, once written, are immutable -- system should be able to
> operate without UPDATE and DELETE privileges on the audit db
>   * editing a record creates a new row (with higher version and/or
> date) but does not touch old record
>   * deleting a record creates a new row to be created with the
> "record_is_deleted" flag set to true
>   * (optional) whenever a new row is written, user must supply a
> comment explaining the change
>   * (optional) whenever a new row is written, an email is sent to
> an address specified in the model
> To clarify, when I say "row" I mean a literal row in the database; and
> when I say "record" I mean the representation of a Django model.  A
> record will have many rows associated with it in the audit db, if it
> has been modified many times.  
> 
> Audit trail support would be enabled on a per-model basis, probably by
> including an AuditTrail inner class.  That inner class would then be
> used to specify audit options.  Beyond including this inner class, and
> an to-be-determined method for attaching comments to changes, audit
> trail support should be invisible to developers. 

In your descriptions below, you remove the ability for a developer to
use manual primary keys, by the sound of it, since one of your special
fields wants to be a single-column primary key. That's not invisible.

>  Both the existing admin log and the FullHistory branch are inadequate
> for my requirements.  

Understood. There's a bunch of trade-offs that have to be made with any
of these solutions. You have harder (as in stricter, not more difficult)
recording requirements, so your solution will be more invasive than
FullHistory. Uros Trebec, the Full History summer of code student last
year, and I went around and around on the trade-offs a bit when working
out an approach he could take last year. I think it will be hard to
reconcile both goals, so you've probably made a practical decision in
making requirements like yours a separate application.

> I will be working on this project as part of my Real Job(tm), so
> devoting time to it should not be a problem.  However, before I begin
> coding, I want the community's input on a few issues. 
> 
> What is the right way, at DB level, to implement the audit trail?  I
> had two ideas:
>  1. The table representing a model with AuditTrail enabled will
> include fields such as "record_id", "row_id",
> "record_version", "record_is_deleted", and "row_timestamp".
> Row_id will be the primary key for the table, but will not be
> meaningful for identifying a given record.  Record_id and
> record_version will be unique together, and together
> sufficient for locating the current version of a given record.
> Reading the current record can be accomplished by a query like
> "SELECT *, max(record_version) FROM table_name WHERE
> record_is_deleted IS FALSE GROUP BY record_id", or a database
> view encapsulating the same query. 
> Advantage:  The audit table is guaranteed to be in sync with
> the production data, since they are one and the same
> Disadvantage:  Significantly heavier database load.  

I wouldn't go as far as to say "significantly heavier" without a lot of
profiling under operational loads. Using sensible table indexing,
including functional indexes on databases that have them, should make
that query perform reasonably well for many cases (gut feel based on a
bit of experience with large databases).

A real drawback of this method that you don't mention is that adding
audit support requires changing the database table. So you can't add it
to standard (shipped with Django) or third-party modules easily.

>  1. The audit table, as described above, is written seperately
> from the working table used for reads.  It would be most
> useful if the audit table could be written to a wholly
> separate database.  The working table is unchanged from
> today's Django. 
> Advantage:  Fairly trivial increase in database load
> Disadvantage:  In the event of an application error, it would
> be possible for the working table and audit table to get out
> of sync

You still have to change the main table for the model, though, right?
Otherwise you won't be able to have more than one tuple of data for each
model (due to constraint violations). This was a problem that existed in
the design of the FullHistory work as well: how to store arbitrary 

Re: audit trail support

[[EMAIL PROTECTED]]

Thank you for the advice, David -- you make a good point.

How would you track which Django user made which change?  Perhaps I
could subclass models.Model so save() always fills an (uneditable)
field 'updated_by' with the current user.  Likewise, we could have an
(editable) field "reason_for_change" that would be written like
normal, then cleared in the working record by the triggered
procedure.



On Apr 20, 3:00 pm, drourke <[EMAIL PROTECTED]> wrote:
> I would (and do) implement audit trails right in the database itself
> using the trigger and procedure language that your database provides.
>
> In this manner, any front-end application writing to the database will
> have the audit-trail by default, without need for front-end
> application support.
>
> In other words, I don't believe that the application-level is the
> place to put the audit trail.
>
> - david
>
> On Apr 20, 10:30 am, "Jason McVetta" <[EMAIL PROTECTED]> wrote:
>
> > I need to add real audit trail and change-notification support to an
> > existing Django app and to one that is under development.  The best way to
> > do this, it seems to me, is to add audit support to the Django framework
> > itself.  My requirements are similar to those named by Paul Childs in his
> > django-users 
> > postlast
> > June:
>
> >- rows, once written, are immutable -- system should be able to
> >operate without UPDATE and DELETE privileges on the audit db
> >- editing a record creates a new row (with higher version and/or date)
> >but does not touch old record
> >- deleting a record creates a new row to be created with the
> >"record_is_deleted" flag set to true
> >- (optional) whenever a new row is written, user must supply a comment
> >explaining the change
> >- (optional) whenever a new row is written, an email is sent to an
> >address specified in the model
>
> > To clarify, when I say "row" I mean a literal row in the database; and when
> > I say "record" I mean the representation of a Django model.  A record will
> > have many rows associated with it in the audit db, if it has been modified
> > many times.
>
> > Audit trail support would be enabled on a per-model basis, probably by
> > including an AuditTrail inner class.  That inner class would then be used to
> > specify audit options.  Beyond including this inner class, and an
> > to-be-determined method for attaching comments to changes, audit trail
> > support should be invisible to developers.  Both the existing admin log and
> > the FullHistory branch are
> > inadequate for my requirements.
>
> > I will be working on this project as part of my Real Job(tm), so devoting
> > time to it should not be a problem.  However, before I begin coding, I want
> > the community's input on a few issues.
>
> > What is the right way, at DB level, to implement the audit trail?  I had two
> > ideas:
>
> >1. The table representing a model with AuditTrail enabled will include
> >fields such as "record_id", "row_id", "record_version", 
> > "record_is_deleted",
> >and "row_timestamp".  Row_id will be the primary key for the table, but 
> > will
> >not be meaningful for identifying a given record.  Record_id and
> >record_version will be unique together, and together sufficient for 
> > locating
> >the current version of a given record.  Reading the current record can be
> >accomplished by a query like "SELECT *, max(record_version) FROM 
> > table_name
> >WHERE record_is_deleted IS FALSE GROUP BY record_id", or a database view
> >encapsulating the same query.
> >Advantage:  The audit table is guaranteed to be in sync with the
> >production data, since they are one and the same
> >Disadvantage:  Significantly heavier database load.
> >2. The audit table, as described above, is written seperately from the
> >working table used for reads.  It would be most useful if the audit table
> >could be written to a wholly separate database.  The working table is
> >unchanged from today's Django.
> >Advantage:  Fairly trivial increase in database load
> >Disadvantage:  In the event of an application error, it would be
> >possible for the working table and audit table to get out of sync
> >3. I am open to better suggestions.
>
> > Perhaps the core developers can tell me, will it be possible to do this as a
> > contrib plugin?  I would, of course, rather do it as a plugin than hack on
> > the Django core.  But either way, my ultimate goal is to create a high
> > quality solution that can be included in the main trunk.
>
> > Lastly, would it have been more appropriate to post this to
> > django-developers?
>
> > Jason


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send 

Re: audit trail support

[drourke]

I would (and do) implement audit trails right in the database itself
using the trigger and procedure language that your database provides.

In this manner, any front-end application writing to the database will
have the audit-trail by default, without need for front-end
application support.

In other words, I don't believe that the application-level is the
place to put the audit trail.

- david

On Apr 20, 10:30 am, "Jason McVetta" <[EMAIL PROTECTED]> wrote:
> I need to add real audit trail and change-notification support to an
> existing Django app and to one that is under development.  The best way to
> do this, it seems to me, is to add audit support to the Django framework
> itself.  My requirements are similar to those named by Paul Childs in his
> django-users 
> postlast
> June:
>
>- rows, once written, are immutable -- system should be able to
>operate without UPDATE and DELETE privileges on the audit db
>- editing a record creates a new row (with higher version and/or date)
>but does not touch old record
>- deleting a record creates a new row to be created with the
>"record_is_deleted" flag set to true
>- (optional) whenever a new row is written, user must supply a comment
>explaining the change
>- (optional) whenever a new row is written, an email is sent to an
>address specified in the model
>
> To clarify, when I say "row" I mean a literal row in the database; and when
> I say "record" I mean the representation of a Django model.  A record will
> have many rows associated with it in the audit db, if it has been modified
> many times.
>
> Audit trail support would be enabled on a per-model basis, probably by
> including an AuditTrail inner class.  That inner class would then be used to
> specify audit options.  Beyond including this inner class, and an
> to-be-determined method for attaching comments to changes, audit trail
> support should be invisible to developers.  Both the existing admin log and
> the FullHistory branch are
> inadequate for my requirements.
>
> I will be working on this project as part of my Real Job(tm), so devoting
> time to it should not be a problem.  However, before I begin coding, I want
> the community's input on a few issues.
>
> What is the right way, at DB level, to implement the audit trail?  I had two
> ideas:
>
>1. The table representing a model with AuditTrail enabled will include
>fields such as "record_id", "row_id", "record_version", 
> "record_is_deleted",
>and "row_timestamp".  Row_id will be the primary key for the table, but 
> will
>not be meaningful for identifying a given record.  Record_id and
>record_version will be unique together, and together sufficient for 
> locating
>the current version of a given record.  Reading the current record can be
>accomplished by a query like "SELECT *, max(record_version) FROM table_name
>WHERE record_is_deleted IS FALSE GROUP BY record_id", or a database view
>encapsulating the same query.
>Advantage:  The audit table is guaranteed to be in sync with the
>production data, since they are one and the same
>Disadvantage:  Significantly heavier database load.
>2. The audit table, as described above, is written seperately from the
>working table used for reads.  It would be most useful if the audit table
>could be written to a wholly separate database.  The working table is
>unchanged from today's Django.
>Advantage:  Fairly trivial increase in database load
>Disadvantage:  In the event of an application error, it would be
>possible for the working table and audit table to get out of sync
>3. I am open to better suggestions.
>
> Perhaps the core developers can tell me, will it be possible to do this as a
> contrib plugin?  I would, of course, rather do it as a plugin than hack on
> the Django core.  But either way, my ultimate goal is to create a high
> quality solution that can be included in the main trunk.
>
> Lastly, would it have been more appropriate to post this to
> django-developers?
>
> Jason


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---