[postgis-users] Versioning

[Paragon Corporation]

Here is a slightly combined version of what Paul says that uses table
inheritance to try to achieve the best of both worlds.  I think Ben is doing
something like that where he keeps the whole history and current version in
one table.

1) Create a table called mystuff_all(should have the date fields in it)

2) Create a table called mystuff which inherits from mystuff_all

3) Create a table called mystuff_history which inherits from mystuff_all

Use Paul's trigger adding triggers against mystuff to add records into
mystuff_history when things change in mystuff -- the full record basically.

Those apps that have version awareness can query from mystuff_all and others
just use mystuff.

Leo



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Wednesday, May 13, 2009 5:43 PM
To: jp.alcantara at geo-st.com; PostGIS Users Discussion
Subject: 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