Re: History-based (or logged) database. - Mailing list pgsql-general

Hi Octavio;

I have had to do something like this (hotel reservation app I am developing)
and want to be able to reconstruct an accurate picture of the database from
any point in time for reporting purposes (suppose I change the configuration
of a room and want to see vacancy rate info for a certain configuration on a
certain date).

Here is what I did.  This is off the top of my head, and may not work as
written here, but it should at least show the idea and structure of my
solution ot the problem.
(simple excerpt that shows the example):

create table rooms (
room_id varchar(64) primary key,
class_id int4 references rclasses(class_id)
);

create table room_archive (
room_id varchar(64) NOT NULL,
class_id int4 NOT NULL,
valid_until TIMESTAMP NOT NULL);

CREATE FUNCTION archive_room_trig() RETURNS TRIGGER AS '
    BEGIN
        INSERT INTO room_archive(room_id, class_id, valid_until)
        VALUES (old.room_id, old.class_id, now());
    END;
' LANGUAGE PLPGSQL;

CREATE TRIGGER trig_archive_room
BEFORE INSERT OR UPDATE OR DELETE ON rooms
FOR EACH ROW archive_room_trig();

CREATE VIEW room_history AS
SELECT room_id, class_id, now() AS valid_until FROM rooms
UNION
SELECT room_id, class_id, valid_until FROM room_archive;

Best Wishes,
Chris Travers
----- Original Message -----
From: "Octavio Alvarez" <alvarezp@alvarezp.ods.org>
To: <pgsql-general@postgresql.org>
Sent: Monday, January 05, 2004 10:43 AM
Subject: [GENERAL] History-based (or logged) database.


>
> Hi! I would like to implement a database which allows me to keep track of
> changes from users, but I don't know if there is any model already used
> for this. Let me show you what I mean.
>
> Say I have a table t_table1 with 2 columns plus a PK. Normally my table
> with some data would look like:
>
> t_table1
> ------------------
>  PK | col1 | col2
> ------------------
>   1 |   3  |   4
>   2 |   4  |   7
>   3 |   6  |   9
>  ... and so on...
>
> If I make a change, I can't get the info about who made the change and
> when did he do it, I can't do a "what was the value on a certain
> date"-type query.
>
> An UPDATE col1 = 9 WHERE pk = 1; would make t_table1 look like:
>
> t_table1:
> ------------------
>  PK | col1 | col2
> ------------------
>   1 |   9  |   4
>   2 |   4  |   7
>   3 |   6  |   9
>  ... and so on...
>
> To solve my "who and when", and "what on a certain date" problem, I was
> thinking on a platform like the following:
>
> t_table1:
> -------------------
>  PK | col1 | col2 |     record_date           | who_created
> -------------------
>   1 |   3  |   4  |  2003-03-03 11:30:10 a.m. | alvarezp
>   2 |   4  |   7  |  2003-03-03 11:30:10 a.m. | alvarezp
>   3 |   6  |   9  |  2003-03-04 11:30:10 a.m. | alvarezp
>  ... and so on...
>
> Now, an UPDATE col1 = 9 WHERE pk = 1; (done on '2003-03-05 12:00:00 a.m.')
> by 'ggarcia' would make t_table1 look like:
>
> t_table1:
>     --------------------
> UID | PK | col1 | col2 |     record_date           | who_created
>     --------------------
>   1 |  1 |   3  |   4  |  2003-03-03 11:30:10 a.m. | alvarezp
>   2 |  2 |   4  |   7  |  2003-03-03 11:30:10 a.m. | alvarezp
>   3 |  3 |   6  |   9  |  2003-03-04 11:30:10 a.m. | alvarezp
>   4 |  1 |   9  |   4  |  2003-03-05 12:00:00 a.m. | ggarcia
>  ... and so on...
>
> I would extend SQL to include a "WHEN" clause in SELECT statements. If
> omitted, the query should use only the last valid records, using only UID
> = {2, 3, 4}, which will make it completely transparent to not-yet-updated
> applications.
>
> Of course, may be a "deleted" column would be needed in order to DELETE
> from t_table1;" and still have the data available for the hypothetical
> "SELECT ... WHEN '2003-03-03 3:00:00 p. m.';"
>
> Has anyone implemented something similar in PGSQL? If so, how have you
> done it?
>
> Thanks in advance.
>
> --
> Octavio Alvarez Piza.
> E-mail: alvarezp@alvarezp.ods.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>