You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Donald McLean <dm...@gmail.com> on 2009/07/09 15:30:32 UTC

Re: How would you implement a record-level versioning system with Derby and Java?

On Thu, Jul 9, 2009 at 9:03 AM, Alessandro Bottoni<al...@yahoo.it> wrote:
> Hi Guys,
> I'm completely new to Derby and I evaluating it as a possibile embedded
> RDBMS for a Java/Swing application that I'm goig to develop. I'm
> currently following the tutorials at IBM DeveloperWorks
> (http://tinyurl.com/6mlwpj) and I have ordered the IBM book at Amazon's
> already (http://tinyurl.com/ln3cas).
>
> I have a stimulating question for you all (I apologize if this topic has
> been discussed already):
>
> how would you implement a system to keep track of any old version of a
> record (row) of your database?
>
> I'm asking this because I need a system that will allow the user to see
> any old version of a record and to undo/redo any change with just a
> couple of mouse clicks.
>
> Any idea? Any suggestion?

Actually, this is a question that I have previously considered for a
project that I am starting.

My thought was to have an identity column (value assigned by the DBMS)
and a separate ID column. From here, you can do several different
things:

1. Have a "version" column. Use the max function to find the current
version, or make the current version -1 or something else easy to
find.
2. Use the max function on the identity column to find the latest version.
3. Have "previous" and "next" columns with the identity values of the
appropriate objects in the chain. The current version would have a
null "next".

There are probably other approaches that I haven't thought of.

> PS: No, I'm not planning to use any kind of ORM (Hibernate or similar).
> Should I?

I have used and recommend Hibernate. It simplifies things immensely
IMO, though it does have its quirks (some of which are not properly
explained by the documentation). It also works well with a number of
other DBMS (if changing is something that might happen later).
-------------------------------------------------------------
Family photographs are a critical legacy for
ourselves and our descendants. Protect that
legacy with a digital backup and recovery plan.

Re: How would you implement a record-level versioning system with Derby and Java?

Posted by Peter Ondruška <pe...@gmail.com>.
I would recommend also looking at some data warehousing articles as
versioning is related to that. Though I do not have any concrete to
recommend :-(. Peter

2009/7/9, Alessandro Bottoni <al...@yahoo.it>:
> Donald McLean ha scritto:
>> Actually, this is a question that I have previously considered for a
>> project that I am starting.
>>
>> My thought was to have an identity column (value assigned by the DBMS)
>> and a separate ID column. From here, you can do several different
>> things:
>>
>> 1. Have a "version" column. Use the max function to find the current
>> version, or make the current version -1 or something else easy to
>> find.
>> 2. Use the max function on the identity column to find the latest version.
>
> I thought about this mechanism but I'm not completely convinced it would
> be the right one. I mean, there are two different "user views" to take
> into account:
>
> 1) The user view onto the whole database while browsing/searching for a
> specific record.
> 2) The single-record view used to examine/edit a specific record.
>
> While it is easy to use a SELECT with MAX to get the latest version of a
> specific record, it is not clear how I could get only the most recent
> version of every single record to build up the "browse" view. It looks
> like I should use a nested SELECT:  first, select all matching records
> and, second, select the latest version of  each of them. A little bit
> messy...
>
>> 3. Have "previous" and "next" columns with the identity values of the
>> appropriate objects in the chain. The current version would have a
>> null "next".
>
> Nice approach! Actually, I did not think about this possibility. Many
> thanks for this suggestion.
>
> This would simplify a lot the SELECT operation used to generate the
> "browse" view (just select all of the records that have a NULL
> "next-version" field) while maintaining the possibility to go back and
> forth between versions (and build up a list of them, if needed).
>
> I think I will experiment with this one.
>
>> There are probably other approaches that I haven't thought of.
>
> I thought about triggers/procedures, as well, but I had the feeling they
> would have the same problem as the SELECT + MAX approach.
>
>> I have used and recommend Hibernate. It simplifies things immensely
>> IMO, though it does have its quirks (some of which are not properly
>> explained by the documentation). It also works well with a number of
>> other DBMS (if changing is something that might happen later).
>
> Thanks. I'm new to Hibernate, as well, so I'm going to ask you (all)
> which book is best for learning Hibernate. I browsed a little bit on
> Amazon's but I did not find anything to be enthusiastic about. What
> would you suggest me as a good starting point/introductory book?
>
> Any good web article/tutorial to signal, as well?
>
> Thanks again.
> --
>
> Alessandro Bottoni
> Website: http://www.alessandrobottoni.it/
>
> "In mathematics you don't understand things. You just get used to them."
>      -- John von Neumann
>

Re: How would you implement a record-level versioning system with Derby and Java?

Posted by Donald McLean <dm...@gmail.com>.
On Thu, Jul 9, 2009 at 10:44 AM, Alessandro Bottoni<al...@yahoo.it> wrote:
> Donald McLean ha scritto:
>> I have used and recommend Hibernate. It simplifies things immensely
>> IMO, though it does have its quirks (some of which are not properly
>> explained by the documentation). It also works well with a number of
>> other DBMS (if changing is something that might happen later).
>
> Thanks. I'm new to Hibernate, as well, so I'm going to ask you (all)
> which book is best for learning Hibernate. I browsed a little bit on
> Amazon's but I did not find anything to be enthusiastic about. What
> would you suggest me as a good starting point/introductory book?
>
> Any good web article/tutorial to signal, as well?

I used only the included reference and experimented a bit.
-------------------------------------------------------------
Family photographs are a critical legacy for
ourselves and our descendants. Protect that
legacy with a digital backup and recovery plan.

Re: How would you implement a record-level versioning system with Derby and Java?

Posted by Alessandro Bottoni <al...@yahoo.it>.
Donald McLean ha scritto:
> Actually, this is a question that I have previously considered for a
> project that I am starting.
> 
> My thought was to have an identity column (value assigned by the DBMS)
> and a separate ID column. From here, you can do several different
> things:
> 
> 1. Have a "version" column. Use the max function to find the current
> version, or make the current version -1 or something else easy to
> find.
> 2. Use the max function on the identity column to find the latest version.

I thought about this mechanism but I'm not completely convinced it would
be the right one. I mean, there are two different "user views" to take
into account:

1) The user view onto the whole database while browsing/searching for a
specific record.
2) The single-record view used to examine/edit a specific record.

While it is easy to use a SELECT with MAX to get the latest version of a
specific record, it is not clear how I could get only the most recent
version of every single record to build up the "browse" view. It looks
like I should use a nested SELECT:  first, select all matching records
and, second, select the latest version of  each of them. A little bit
messy...

> 3. Have "previous" and "next" columns with the identity values of the
> appropriate objects in the chain. The current version would have a
> null "next".

Nice approach! Actually, I did not think about this possibility. Many
thanks for this suggestion.

This would simplify a lot the SELECT operation used to generate the
"browse" view (just select all of the records that have a NULL
"next-version" field) while maintaining the possibility to go back and
forth between versions (and build up a list of them, if needed).

I think I will experiment with this one.

> There are probably other approaches that I haven't thought of.

I thought about triggers/procedures, as well, but I had the feeling they
would have the same problem as the SELECT + MAX approach.

> I have used and recommend Hibernate. It simplifies things immensely
> IMO, though it does have its quirks (some of which are not properly
> explained by the documentation). It also works well with a number of
> other DBMS (if changing is something that might happen later).

Thanks. I'm new to Hibernate, as well, so I'm going to ask you (all)
which book is best for learning Hibernate. I browsed a little bit on
Amazon's but I did not find anything to be enthusiastic about. What
would you suggest me as a good starting point/introductory book?

Any good web article/tutorial to signal, as well?

Thanks again.
-- 

Alessandro Bottoni
Website: http://www.alessandrobottoni.it/

"In mathematics you don't understand things. You just get used to them."
     -- John von Neumann

Re: How would you implement a record-level versioning system with Derby and Java?

Posted by Donald McLean <dm...@gmail.com>.
I like that idea. You could even create different tables for different epochs.

On Thu, Jul 9, 2009 at 10:32 AM, Peter Ondruška<pe...@gmail.com> wrote:
> Actually I would store the history values outside the main table for
> performance and storage overhead reasons (history table may have
> additional columns, e.g. When-who-etc for auditing information). Peter
-------------------------------------------------------------
Family photographs are a critical legacy for
ourselves and our descendants. Protect that
legacy with a digital backup and recovery plan.

RE: How would you implement a record-level versioning system with Derby and Java?

Posted by "Thomas J. Taylor" <Th...@Miami.edu>.
Hi Alessandro,

We've implemented an approach similar to this for storing and tracking changes to XML documents - because the values we are storing could be large (base 64-encoded binary objects), we decided to have a 'attribute-value' table that would only allow inserts, plus an 'audit-record' table that tracks when/where/who changed the value. 

This approach works reasonably well for moderate sized documents (1MB / 100,000 values/changes), but it starts to slow down as the number of records/changes exceed 1,000,000. 

In Oracle and SQL Server, we implemented a VIEW on the attribute-value/audit-record table to only show the most recent value -- makes it easier/faster to get the latest value. Value history is easily retrieved by selecting all values from the attribute-value table with the same unique attribute-id. We haven't optimized our code for Derby, so haven't tried a similar approach here.

Best Regards,

Thomas Taylor

-----Original Message-----
From: Alessandro Bottoni [mailto:alexbottoni@yahoo.it] 
Sent: Thursday, July 09, 2009 10:48 AM
To: Derby Discussion
Subject: Re: How would you implement a record-level versioning system with Derby and Java?

Peter Ondru�ka ha scritto:
> Actually I would store the history values outside the main table for
> performance and storage overhead reasons (history table may have
> additional columns, e.g. When-who-etc for auditing information). Peter

Yep... This has to be kept into account, actually, and it makes the
whole thing a little bit more complicated...

Thanks for this suggestion, Peter.

-- 

Alessandro Bottoni
Website: http://www.alessandrobottoni.it/

Who wants to remember that escape-x-alt-control-left shift-b puts you
into super-edit-debug-compile mode?
     -- (Discussion in comp.os.linux.misc on the intuitiveness of
commands, especially Emacs.)


Re: How would you implement a record-level versioning system with Derby and Java?

Posted by Alessandro Bottoni <al...@yahoo.it>.
Peter Ondruška ha scritto:
> Actually I would store the history values outside the main table for
> performance and storage overhead reasons (history table may have
> additional columns, e.g. When-who-etc for auditing information). Peter

Yep... This has to be kept into account, actually, and it makes the
whole thing a little bit more complicated...

Thanks for this suggestion, Peter.

-- 

Alessandro Bottoni
Website: http://www.alessandrobottoni.it/

Who wants to remember that escape-x-alt-control-left shift-b puts you
into super-edit-debug-compile mode?
     -- (Discussion in comp.os.linux.misc on the intuitiveness of
commands, especially Emacs.)

Re: How would you implement a record-level versioning system with Derby and Java?

Posted by Peter Ondruška <pe...@gmail.com>.
Actually I would store the history values outside the main table for
performance and storage overhead reasons (history table may have
additional columns, e.g. When-who-etc for auditing information). Peter

2009/7/9, Donald McLean <dm...@gmail.com>:
> On Thu, Jul 9, 2009 at 9:03 AM, Alessandro Bottoni<al...@yahoo.it>
> wrote:
>> Hi Guys,
>> I'm completely new to Derby and I evaluating it as a possibile embedded
>> RDBMS for a Java/Swing application that I'm goig to develop. I'm
>> currently following the tutorials at IBM DeveloperWorks
>> (http://tinyurl.com/6mlwpj) and I have ordered the IBM book at Amazon's
>> already (http://tinyurl.com/ln3cas).
>>
>> I have a stimulating question for you all (I apologize if this topic has
>> been discussed already):
>>
>> how would you implement a system to keep track of any old version of a
>> record (row) of your database?
>>
>> I'm asking this because I need a system that will allow the user to see
>> any old version of a record and to undo/redo any change with just a
>> couple of mouse clicks.
>>
>> Any idea? Any suggestion?
>
> Actually, this is a question that I have previously considered for a
> project that I am starting.
>
> My thought was to have an identity column (value assigned by the DBMS)
> and a separate ID column. From here, you can do several different
> things:
>
> 1. Have a "version" column. Use the max function to find the current
> version, or make the current version -1 or something else easy to
> find.
> 2. Use the max function on the identity column to find the latest version.
> 3. Have "previous" and "next" columns with the identity values of the
> appropriate objects in the chain. The current version would have a
> null "next".
>
> There are probably other approaches that I haven't thought of.
>
>> PS: No, I'm not planning to use any kind of ORM (Hibernate or similar).
>> Should I?
>
> I have used and recommend Hibernate. It simplifies things immensely
> IMO, though it does have its quirks (some of which are not properly
> explained by the documentation). It also works well with a number of
> other DBMS (if changing is something that might happen later).
> -------------------------------------------------------------
> Family photographs are a critical legacy for
> ourselves and our descendants. Protect that
> legacy with a digital backup and recovery plan.
>