[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 pramsey):

 First of all, use a 'timestamp' not separate date and time columns.

 Name your derived table as 'foo_history' rather than 'h_foo'. Actually
 some comment on that would be useful. The 'h_' notation puts all the
 history tables in one separate section of a sorted listing, while the
 '_history' notation puts the history next to the table it relates to.
 Which is more valuable?

 Here's how history works:

 On insert to table foo, insert to h_foo, with date_add = now() and
 date_removed = null.

 On delete from table foo, update h_foo, set date_removed = now().

 On update to table foo, update current record h_foo setting date_removed =
 now() **and** insert new record into h_foo, with new attributes and
 date_add = now() and date_removed = null.

 Because your rules don't know the attribute names at run-time, you need to
 compose the rules during the history-enablement stage.

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