[postgis-users] Versioning

[Paul Ramsey]

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
>