[postgis-devel] [PostGIS] #180: History table example implementation

[PostGIS]

#180: History table example implementation
--------------------------+-------------------------------------------------
  Reporter:  pramsey      |       Owner:  pimpaa       
      Type:  enhancement  |      Status:  assigned     
  Priority:  medium       |   Milestone:  postgis 1.5.0
 Component:  postgis      |     Version:               
Resolution:               |    Keywords:               
--------------------------+-------------------------------------------------
Comment (by pimpaa):

 Hello everyone,

 I'm a medium level user and accepted this ticket to contribute with
 postgis, which is a great tool.

 I was studying what is the best way to get this done, so i've tried RULES
 and TRIGGERS.

 I could think of two options: a set of three rules that can be built, one
 for insert, one for update and one for delete OR one trigger which threats
 the operation and resolves what do to.

 Heres the structure of both tables:

 table test(
 id serial not null,
 att1 varchar(20),
 att2 varchar(20),
 geom geometry,
 constraint test_pk primary key(id);

 table h_test(
 id serial not null,
 att1 varchar(20),
 att2 varchar(20),
 geom geometry,
 history_id serial not null,
 date_added date default current_date,
 timeofupdate time default now(),
 date_removed date default null,
 operation varchar(20),
 active_user default current_user,
 current_version integer,
 constraint h_teste_pk primary key(history_id));

 RULE INSERT would insert a new record to this table and populate it with
 its values. Its quite simple to automatically generate its code, using
 NEW.*.

 RULE DELETE a little bit more complicated, but it can be done. I used an
 update to apply the current values to date_removed, operation,
 active_user, and set a arbitrary number to current_version and id_field
 (-9999).

 RULE UPDATE: this is where i ran into trouble. Since we need to update all
 fields, i need to know them in advanced or find a way to get them in a
 plpgsql function. This rule would create a new record, with operation =
 'UPDATE', containing all new information, but i need to update the old
 record, change its current_version and set the arbitrary number to
 id_field of foo table.

 I could in this rule, create the new record and set only the
 current_version and id_field of the old record (in history table). Would
 this work?

 This would give us a solution where you only have one record for deleted
 features, multiple features to update features, and one for each insert.
 And since we would have a current_version attribute, would be easy for the
 user to identify the original atribute that each feature came from.

 The trigger approach is much easier to generate code for, but so far i can
 only insert one record for each operation. I know they can be updated, but
 i ran into the same problem as before, i need to know the fields for the
 UPDATE operation to do this.

 I don't see any other options. Since this is my first ticket, and im quite
 a intermediate user, i would like your opinion.

 Whats the best way to approach in this?

 I already have a function to create automatically the history tables and a
 history schema.

 Thanks for the attention.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/180#comment:3>
PostGIS <http://trac.osgeo.org/postgis/>
PostGIS