Re: Applictaion data Logging

Harald Krake escribió:

> Hi!
>
> when it comes to portability the right place to implement such
> auditing is in the OR-mapper.
> Most commercial mappers already provide such a feature.
> However, if you don't like "black-boxed" third party
> software, writing your own mapper isn't a big deal.
> It's worth the effort!
>
> Some hints:
> - writing a mapper usually ends up with methods like
>   MySpecialDbObject.save() or insert() or update() or alike.
> - your mapper should provide a method to turn on/off logging
>   on a per class basis.
> - one solution to the auditing problem is an extra "logging table"
>   per "data table". The logging table holds _all_ the columns of the
>   data table _plus_ a serial number (incremented each time the
>   corresponding data tuple is modified) and things like userid/name,
>   timestamp and so on.
> - you should store the serial-number in the data-table too.
>
> At least, this is how we did it and it works fine.
> If you don't mind portability postgres probably provides some
> auditing support, but I don't know.
> Anyway, you should either use a dbms-inherent feature _or_ do it
> in your application. Mixing both levels is not a good idea, imho.
>
> Hope it helps,
> Harald.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Thanks for your answer.

I don't know what is OR-mapper, I tried to look for some information in the
internet with www.google.com, but got no luck.

Anyway because the rest of your answer, I can fegure out what a mapper is,
and how to implement such feature.

I have some thougths that want to share for your opinion:

1) I already have doInsert(), doUpdate() and doDelete() methods in my
classes, so there is where I have to do the logging (the mapper action I
think)

2) Because the logging action is in these methods, I'm not sure about the
convenience to turn on/off the data logging, but anyway it can be done.

3) In my design of data logging, I only have one "logging table" for all
the application, but this logging table has to have the table name and the
column name of what is being changed, inserted or deteled, like the
following:

id SERIAL                                        -- Primary key
tablename VARCHAR(40)        -- Table name that changed
fieldname VARCHAR(40)        -- fieldname that changed in the table
user VARCHAR(16)                    -- User that made the change
timedate TIMESTAMP WITHOUT TIME ZONE    -- Timestamp of change
keyvalue TEXT            -- The key value of the record that changed
oldcontent TEXT        -- the field's content before the change
newcontent TEXT        -- the field's new content

I'm not sure if the primary key (id SERIAL) is used in the way you
suggested the serial number???

4) What utility can have storing the serial number in the data table????

I know that my design can be more useful in my application and yours in
your application but I want to know your comments.

Another design that I'm aware, every time somebody updated the tuple, a new
tuple where written to the table with a timestamp, so if I wanted to know
the actual value I just get the record with the greatest timestamp, and if
I wanted to know the history I can see all the tuples with the same ke
value (not the primary key, because in this design, the primary key always
was a SERIAL value).

Thanks again...