[postgis-users] Versioning

[Johannes Sommer]

Hi Juan,

the following link should be interesting:

http://pgfoundry.org/projects/tablelog/

tablelog adresses your problem and tracks the historical state of your table (including geometry).

"PostgreSQL Table Log uses a trigger to log any INSERTs, UPDATEs and DELETEs on a specific table into another table. The second part of tablelog is able to restore the state of the original table or of a specific row for any time in the past."

greetings,
Johannes

-------- Original-Nachricht --------
> Datum: Wed, 13 May 2009 14:43:06 -0700
> Von: Paul Ramsey <pramsey at opengeo.org>
> An: jp.alcantara at geo-st.com, PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Betreff: Re: [postgis-users] Versioning

> Then you should be able to get by with a pretty simple system.
> For each table you want to retain history for, add a second table,
> table_history that adds two columns (date_added, date_deleted).
> The put some insert/update/delete triggers on your main tables.
> - on insert of row, simultaneously insert a copy into the _history
> table with an appropriate date_added
> - on delete of row update the date_deleted field in the equivalent
> _history table row
> - on update of row, flag the current copy of the row as deleted, and
> add a new copy of the row with the current date as the added date
> 
> Now a snapshot of "historical state" as of date_query is as simple as
> querying the history table for "date_added < date_query and
> (date_deleted > date_query or date_deleted is null)"
> 
> The thrifty among us will say "hey, I can just have one table with
> date_added and date_deleted columns" and "current state is just all
> records with "date_deleted is null". However, your table will grow
> over time, and eventually things will get slower than you would like.
> Having a "working table" that is just current also removes the need
> for client applications to understand the "history concept", they can
> just work on the table as normal and history is maintained for them
> transparently.
> 
> P.
> 
> 
> 2009/5/13 Juan Pedro Pérez Alcántara <jp.alcantara at geo-st.com>:
> > Sure, you right. I'm refering versioning in it's first meaning, that is,
> > history tracking.
> >
> > Thanks,
> >
> > Juan Pedro Pérez Alcántara
> >
> > On Wed, 2009-05-13 at 12:33 -0700, Paul Ramsey wrote:
> >> Juan Pedro,
> >>
> >> What do you mean by "versioning"?
> >>
> >> History tracking? (Every state of the data is one version, but there
> >> is only one branch of the data.)
> >> Branching and merging? (People can take a copy of the data (a version)
> >> and work on it, then merge it back into the main database.)
> >>
> >> Versioning is an overloaded word. Describe your actual use case.
> >>
> >> P.
> >>
> >> 2009/5/13 Juan Pedro Pérez Alcántara <jp.alcantara at geo-st.com>:
> >> > Hello all,
> >> >
> >> > soon we will face a project which will need a really strong
> > versioning
> >> > mechanism, including geometry. I'm sure anybody here have already
> >> > addressed this sort of problem. I wonder if you can drop a line or
> > two
> >> > about this issue, for I consider it hard to implement. Sure the
> >> > solutions will vary wildly depending on the scenario, the data, the
> >> > versioning schedule, etc., but I'll be glad to hear about your
> >> > experiences in that field.
> >> >
> >> > Greetings,
> >> >
> >> > Juan Pedro Pérez Alcántara
> >> >
> >> >
> >> > _______________________________________________
> >> > postgis-users mailing list
> >> > postgis-users at postgis.refractions.net
> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >
> >>
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
Neu: GMX FreeDSL Komplettanschluss mit DSL 6.000 Flatrate + Telefonanschluss für nur 17,95 Euro/mtl.!* http://dslspecial.gmx.de/freedsl-surfflat/?ac=OM.AD.PD003K11308T4569a