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: Django and timezones ... how to initialize a field to now() in the in clients time zone?

[Andreas Kuhne]

Hi,

The problem here is as you say, your server doesn't know the clients
timezone. This isn't trivial to find out either. I can help you with some
pointers though:
1. ALWAYS save all times in the database in UTC. ALWAYS - never diviate
from this - one exception - if you are running a server that will only be
used in a specific timezone. Set the default timezone in django to UTC.
2. You can set the timezone for the current request -
https://docs.djangoproject.com/en/1.10/topics/i18n/timezones/#selecting-the-current-time-zone.
The problem that you have is to identifiy the users timezone - this isn't
that easy - it can be done via a javascript library like the one you are
using, or via a project like this:
https://github.com/Miserlou/django-easy-timezones.

Regards,

Andréas


2017-01-21 10:40 GMT+01:00 Bernd Wechner :

> I have a model with this field:
>
> date_time = models.DateTimeField(default=timezone.now)
>
> if I look at the generic create form, it inializes the date_time field
> with now in UTC, not in local time.
>
> Now I appreciate there are two things at play here:
>
>1. the desired UX is to see now in my time zone.
>2. Django, running on the server has no idea what my time zone is.
>
> Now I can think my way through this and find some solutions, not short of
> imagination here. But as I work on nutting one out, I am struck, as ever,
> with the notion that 1. is surely a ubiquitous use case ... namely I cannot
> seriously be the first person to have this desire or tackle this problem
> and is there not a stock standard approach in Django to doing this?
>
> As I read it I can find the time zone in Javascript at the client side:
>
> This works fine for example:
>
> var tz_name = jstz.determine().name();
> var tz_offset = new Date().getTimezoneOffset()/60;
>
> giving me pleasing results (though I appreciate, before anyone points it
> out, that pleasing results are by no means guaranteed as it as it is
> premised on the correct TZ configuration of the client computer among other
> things, but the application in mind does not REQUIRE time accuracy (am just
> logging events that happened at a given time and timezone errors are of no
> practical data integrity or security concerns for a few good reasons) .
>
> And I can submit these in hidden fields too.
>
> But the obvious solution, which I find a tad kludgy alas is at the client
> side in JS to find the date_time input field, and adjust it's value from
> UTC to local time. Right now it's presenting as UTC.
>
> Even if I could find a way to ask Django to use a different timezone
> (tmiezone.activate) it won't kow what Timezone until it's heard from the
> client. And it's not in HTTP headers alas, though someone thinks it should
> be:
>
> http://blogs.windwardreports.com/davidt/2010/04/can-we-
> please-get-a-time-zone-in-the-http-request-header.html
>
> On submission of course as I can submit the TZ info in hidden fields it
> should be easy to convert the submission back to UTC for saving into the
> database.
>
> Still, I wonder openly, is this not long solved, more elegantly?
>
> Regards,
>
> Bernd.
>
> --
> 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/4738d161-186d-4205-a148-1f17e5d231ef%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/CALXYUbn11Fi8P-rFQKni7_3xH2ajKv6iS1aZBZzNCnrtQH3VcA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Django-channels and JSON-RPC

[Alexander Prokhorov]

If you are going to implement JSON-RPC based on Channels, I would be happy 
to participate, I suppose we will start doing this in a few week anyway.

пятница, 20 января 2017 г., 13:36:49 UTC+3 пользователь Fabien Millerand 
написал:
>
> Thanks a lot for your answer Andrew.
>
> On a side note, would you be related to Mike Godwin? I just draw some 
> moustaches on Trump's face 5 minutes before seeing your message... It is 
> disturbing...
>
>
>
>
> Le jeudi 19 janvier 2017 19:27:10 UTC+1, Andrew Godwin a écrit :
>>
>> I haven't seen anything like that personally, but I also don't see all of 
>> the Channels stuff going on, so maybe there is one.
>>
>> It would be relatively easy to implement as a single class-based consumer 
>> that dispatches to RPC handlers based on method name, though, as it matches 
>> the consumer pattern very well.
>>
>> Andrew
>>
>> On Thu, Jan 19, 2017 at 7:34 AM, Fabien Millerand  
>> wrote:
>>
>>> Hi everyone,
>>>
>>> I am looking to implement a websocket server based on Django using 
>>> JSON-RPC protocol.
>>>
>>> I have been looking around for a pre-made solution without success. I am 
>>> also a newbie in Django so I am a little bit lost...
>>>
>>> Did anyone try to develop something like 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...@googlegroups.com.
>>> To post to this group, send email to django...@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/f8fdc9b4-ef54-4e39-ad66-9f5b4d9eb042%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/bb455b33-2725-4b0b-874a-8091a096fafd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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: SESSION_EXPIRE_AT_BROWSER_CLOSE

[ADEWALE ADISA]

thanks again Mr James.
am able to solve the problem now by running the management command "python
manage.py clearsessions".
This pratically delete all the session stored on the django session
database.

so SESSION_EXPIRE_AT_BROWSER_CLOSE behave as it should now.
On Jan 22, 2017 9:41 AM, "James Bennett"  wrote:

> Make sure you're not looking at users who already had a session cookie set
> before you changed the setting. Existing cookies might not get immediately
> rewritten to have the shorter expiration.
>
> --
> 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/CAL13Cg-ycUN1cjiy_sbtdvRYa0sF4KMVHUK%
> 2B%2BTipC1GCy0XWww%40mail.gmail.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/CAMGzuy9ncC3hq0D0j%3DYkXF%3DK%2B5is5COnThs2Y5_cq6sXZAz7_Q%40mail.gmail.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: SESSION_EXPIRE_AT_BROWSER_CLOSE

[ADEWALE ADISA]

Thanks Mr James for the urgent reply.
Even when the server and the user pc are restarted after the setting, the
situation is the same.
from the django docs; there is a section that goes :

Note

Some browsers (Chrome, for example) provide settings that allow users to
continue browsing sessions after closing and re-opening the browser. In
some cases, this can interfere with the SESSION_EXPIRE_AT_BROWSER_CLOSE
setting and prevent sessions from expiring on browser close. Please be
aware of this while testing Django applications which have the
SESSION_EXPIRE_AT_BROWSER_CLOSE setting enabled.

Maybe this is what is causing the issue. but i just think that people must
have a way of going around this problem.
On Jan 22, 2017 9:41 AM, "James Bennett"  wrote:

> Make sure you're not looking at users who already had a session cookie set
> before you changed the setting. Existing cookies might not get immediately
> rewritten to have the shorter expiration.
>
> --
> 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/CAL13Cg-ycUN1cjiy_sbtdvRYa0sF4KMVHUK%
> 2B%2BTipC1GCy0XWww%40mail.gmail.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/CAMGzuy-oT2j8JxGDMrfda7-wSnXpm2jeQ-v2h%3DXrPNUxUUqerA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: SESSION_EXPIRE_AT_BROWSER_CLOSE

[James Bennett]

Make sure you're not looking at users who already had a session cookie set
before you changed the setting. Existing cookies might not get immediately
rewritten to have the shorter expiration.

-- 
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/CAL13Cg-ycUN1cjiy_sbtdvRYa0sF4KMVHUK%2B%2BTipC1GCy0XWww%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

SESSION_EXPIRE_AT_BROWSER_CLOSE

[ADEWALE ADISA]

Good day;
Please i need help on the issues am facing on
SESSION_EXPIRE_AT_BROWSER_CLOSE django settings.py. In my setting file i
have:

SESSION_EXPIRE_AT_BROWSER_CLOSE = True

but unfortunately, whenever my users close there browsers and open it
again, they are login automatically, which shows that the session did not
expire.
Am facing this issue on all browers.
On chrome, when i went to the settings and manually choose to expire
cookies, the  SESSION_EXPIRE_AT_BROWSER_CLOSE worked.
In deployed application, i can not be asking my users to be changing
cookies setting in their browsers.
Please how can i achieve session expire after closing browser irrespective
of user browser settings. Or if there is javasctipt snippet i can use to
control this.

Thanks in advance.
soliu - fxSoftlogix

-- 
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/CAMGzuy9pJJBsrz%3De983Np0txHukj7mxPFUftO-7oiU1WDNo6Mg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.