Re: History-based (or logged) database.

On Mon, Jan 05, 2004 at 05:16:23PM +0700, Chris Travers wrote:
> 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(at)alvarezp(dot)ods(dot)org>
> To: <pgsql-general(at)postgresql(dot)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(at)alvarezp(dot)ods(dot)org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

In the current issue of PostgreSQL General Bits #57
http://www.varlena.com/GeneralBits
there is also a brief example of tracking updates with triggers.

Be careful out there! I think Chris referenced OLD values
instead of NEW values in his version of the BEFORE TRIGGER.

elein
============================================================
elein(at)varlena(dot)com Varlena, LLC www.varlena.com

PostgreSQL Consulting, Support & Training

PostgreSQL General Bits http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.