Automagically log changes in table

[George Sakkis]

This is more in the context of Turbogears/SQLAlchemy, but if anyone
has implemented something similar with other packages it might be
useful to know.

I'd like to have a way to make a table "loggable", meaning it would
get, say, two fields "last_modif ied" and "modified_b y", and every
write operation on it would automatically record the time and the id
of the user who did the addition or change (I'm not sure how to deal
with deletions let's leave this for now). Has anyone done something
like that or knows where to start from ?

George

[aspineux]

Re: Automagically log changes in table

Hi

You can get this using "triggers" and "stored procedures".
These are SQL engine dependent! This is available for long time with
postgress and only from version 5 with mysql.
This let you write SQL code (Procedure) that will be called when
"trigged" by an event like inserting new row, updating rows,
deleting ....

BR


On 17 mar, 21:43, "George Sakkis" <george.sak...@ gmail.comwrote:

This is more in the context of Turbogears/SQLAlchemy, but if anyone
has implemented something similar with other packages it might be
useful to know.
>
I'd like to have a way to make a table "loggable", meaning it would
get, say, two fields "last_modif ied" and "modified_b y", and every
write operation on it would automatically record the time and the id
of the user who did the addition or change (I'm not sure how to deal
with deletions let's leave this for now). Has anyone done something
like that or knows where to start from ?
>
George

[George Sakkis]

Re: Automagically log changes in table

On Mar 17, 7:59 pm, "aspineux" <aspin...@gmail .comwrote:

Hi
>
You can get this using "triggers" and "stored procedures".
These are SQL engine dependent! This is available for long time with
postgress and only from version 5 with mysql.
This let you write SQL code (Procedure) that will be called when
"trigged" by an event like inserting new row, updating rows,
deleting ....

I'd rather avoid triggers since I may have to deal with Mysql 4. Apart
from that, how can a trigger know the current user ? This information
is part of the context (e.g. an http request), not stored persistently
somewhere. It should be doable at the framework/orm level but I'm
rather green on Turbogears/SQLAlchemy.

George

[Diez B. Roggisch]

Re: Automagically log changes in table

George Sakkis schrieb:

This is more in the context of Turbogears/SQLAlchemy, but if anyone
has implemented something similar with other packages it might be
useful to know.

SQLObject since version 0.8 lets you define event listeners for
create/update/delete events on objects. You could hook into them.

Diez

[aspineux]

Re: Automagically log changes in table

On 18 mar, 04:20, "George Sakkis" <george.sak...@ gmail.comwrote:

On Mar 17, 7:59 pm, "aspineux" <aspin...@gmail .comwrote:
>

Hi

>

You can get this using "triggers" and "stored procedures".
These are SQL engine dependent! This is available for long time with
postgress and only from version 5 with mysql.
This let you write SQL code (Procedure) that will be called when
"trigged" by an event like inserting new row, updating rows,
deleting ....

>
I'd rather avoid triggers since I may have to deal with Mysql 4. Apart
from that, how can a trigger know the current user ?

Because each user opening an web session will login to the SQL
database using it's own
login. That way you will be able to manage security at DB too. And the
trigger
will use the userid of the SQL session.

This information
is part of the context (e.g. an http request), not stored persistently
somewhere. It should be doable at the framework/orm level but I'm
rather green on Turbogears/SQLAlchemy.
>
George

Maybe it will be easier to manage this in your web application.

it's not too dificulte to replace any

UPDATE persone
SET name = %{new_name}s
WHERE persone_id=%{pe rsoneid}

by something like

UPDATE persone
SET name = %{new_name}s, modified_by=%{u ser_id}s, modified_time=%
{now}s
WHERE persone_id=%{pe rsoneid}

BR

[Jorge Godoy]

Re: Automagically log changes in table

"aspineux" <aspineux@gmail .comwrites:

On 18 mar, 04:20, "George Sakkis" <george.sak...@ gmail.comwrote:

>>
>I'd rather avoid triggers since I may have to deal with Mysql 4. Apart
>from that, how can a trigger know the current user ?

>
Because each user opening an web session will login to the SQL database
using it's own login. That way you will be able to manage security at DB
too. And the trigger will use the userid of the SQL session.

That's not common in web applications (he mentions TurboGears later). What is
common is having a connection pool and just asking for one of the available
connections, when your app gets it, it just uses it. After each request this
connection returns to the pool to be reused.


I dunno about SQL Alchemy (also mentioned later), but SQL Object 0.8x has some
events that can be bound so they can act is triggers on your database, but
client side. Of course they don't have all the context as a real trigger
does, but those might be enough to avoid duplicating lots of code through the
app to set some variable.



--
Jorge Godoy <jgodoy@gmail.c om>