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: pass (athena.apache.org: local policy)
Date: Fri, 29 Feb 2008 11:43:33 +0530
From: Narayanan <V.Narayanan@Sun.COM>
Subject: Re: Version column generated by default ?
In-reply-to: <6ac05c90802281516p12550221x358fc12d16b66996@mail.gmail.com>
Sender: V.Narayanan@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <47C7A28D.4060303@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <6ac05c90802281516p12550221x358fc12d16b66996@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.6 (X11/20071022)

bruehlicke wrote:
> Hi,
>
> I would like a table something like
>
> ID   BIGINT  (standart generated Identity generated always)
> NAME   VARCHAR(40)  
> VERSION  INT     
>
> Now, I would like to be able to have the system automatically increase 
> the version number, but also allow to use my own and of course if 
> version 1, 2 and 3 are used and the user selects to delete version 3 
> and re-create he will not get verision 4 bu a new version 3
>
> QUESTION:  Is there a standard "pattern" we use for this kind of 
> columns representing "versions" of a given thing ? Or is is good old 
> "my own algorithm" to maintain this baby ?
>
> B-)
>
Sorry about the garbled select outputs in the previous reply :-(

I have tried to improve the pasted select outputs in this email

I tried the following experiment with the jars of the latest trunk. If 
you notice after I insert a column
with the identity column value as 5 the generated column value still 
continues from where it left the increment
previously.

ij> create table greetings(i int generated by default as identity, ch 
char(50));
0 rows inserted/updated/deleted
ij> insert into greetings values (DEFAULT, 'salut');
1 row inserted/updated/deleted
ij> insert into greetings(ch) values ('bonjour');
1 row inserted/updated/deleted
ij>  insert into greetings values (5, 'me');
1 row inserted/updated/deleted
ij>  insert into greetings values (DEFAULT,'you');
1 row inserted/updated/deleted
ij> select * from greetings;

I          |CH
--------------------------------------------------------------
1          |salut
2          |bonjour
5          |me
3          |you

ij>  insert into greetings values (DEFAULT, 'all');
1 row inserted/updated/deleted
ij> insert into greetings values (DEFAULT, 'all');
1 row inserted/updated/deleted
ij> insert into greetings values (DEFAULT, 'all');
1 row inserted/updated/deleted
ij> select * from greetings;

I          |CH
--------------------------------------------------------------
1          |salut
2          |bonjour
5          |me
3          |you
4          |all
5          |all
6          |all

7 rows selected

Also I  experimented with deleting a row

ij> delete from greetings  where i=6;
1 row inserted/updated/deleted
ij> insert into greetings values (DEFAULT, 'all');
1 row inserted/updated/deleted
ij> select * from greetings;

I          |CH
--------------------------------------------------------------
1          |salut
2          |bonjour
5          |me
3          |you
4          |all
5          |all
7          |all

7 rows selected

So the value continues from 7 and not from 6 as you expect.

The document here 
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html that 
generated by
always does not guarantee  uniqueness.

You could use a primary key or unique constraint on a column to 
guarantee uniqueness.

I found http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html very 
informative. I am not
sure if a newer version of the document exists. I guess you could refer 
that for more information.

Narayananan