#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
_______________________________________________
postgis-devel mailing list
postgis-devel@...
http://postgis.refractions.net/mailman/listinfo/postgis-devel
