Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.it;
  h=Received:X-Yahoo-SMTP:X-YMail-OSG:X-Yahoo-Newman-Property:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
  b=Vs08rrC8SUX6s5KJmRVyKjDRW9pH9LAQDUFVjJt4NlKztBJBSDiYW5PHQx72rN5nHBiZHlOwLKymn+u3DIy1ORkttotY++SFUX37c9/HaefUOuo8aRiHNG5KfLNumkLRd/hIu65xh+Bq+tlViI6MNxdcWV7bEnLS3PhaSvENczg=
  ;
Message-ID: <4A560262.6020004@yahoo.it>
Date: Thu, 09 Jul 2009 16:44:50 +0200
From: Alessandro Bottoni <alexbottoni@yahoo.it>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: How would you implement a record-level versioning system with
 Derby and Java?
References: <4A55EA88.1010506@yahoo.it>
 <5627810d0907090630p768223f9sf08d911a92546a70@mail.gmail.com>
In-Reply-To: <5627810d0907090630p768223f9sf08d911a92546a70@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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