db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Datastore identity strategy
Date Tue, 28 Mar 2006 21:28:25 GMT
Hi Andy,

Thanks for the quick reply to this issue.

On Mar 27, 2006, at 10:30 PM, Andy Jefferson wrote:

> Hi Craig,
>
> I don't read the spec (as it currently is) to allow identity to  
> utilise
> sequences. You have a sequence option for that.

My problem is that there are a couple of ways to use sequence. One is  
to put the sequence into a trigger/stored procedure so that the  
primary key column automatically gets the value set by the database,  
and the JDO implementation never has to care what the database did  
(except to retrieve the value that was generated). In this case,  
there's no difference in behavior compared to the identity strategy.

The second way is to explicitly set the primary key from the sequence  
as part of the insert statement, e.g. INSERT INTO EMPLOYEE (ID, NAME)  
VALUES(emp_seq, ?). Here, the implementation needs to know the  
sequence name, and the key value is not automatically generated by  
the database.

Another way might be to explicitly retrieve the next sequence value  
from the database and use it in the insert statement. Again, the  
implementation needs to know the sequence name, and the key value is  
not automatically generated by the database.

>
>> <proposed>
>> •The value “sequence” specifies that a named database sequence is
>> used to generate key values for the table. If sequence is used, then
>> the sequence attribute is required. The JDO implementation uses the
>> named sequence in insert statements for the table.
>> •The value “identity” specifies that the column identified as the key
>> column is managed by the database as an identity type, regardless of
>> how the database generates the values, e.g. using the SQL 2003
>> IDENTITY column type or using triggers and stored procedures with a
>> sequence. The JDO implementation does not provide primary key values
>> for the table.
>> </proposed>
>
> So if you have strategy="identity" to include sequences what is the  
> "sequence
> name" in a datastore that utilises sequences ?

This does not matter unless you are generating the schema.

> Are we saying that the "sequence"
> attribute name is used again with strate
> gy="identity" in that case ?

Yes, this is a reasonable interpretation. Using both identity and  
sequence makes sense for schema generation where you want the  
identity strategy (JDO implementation doesn't generate key values)  
and automatic key generation using the sequence named (JDO  
implementation generates the trigger and stored procedure to use the  
named sequence).
>
>> I'm also trying to figure out if we need the following:
>> <spec>
>> •The value “autoassign” specifies that the column identified as the
>> key column
>> is managed by the database to automatically increment key values.
>> </spec>
>
> So how do you specify MySQL "autoincrement" if you remove this ?  
> They aren't
> "IDENTITY" type.

It seems that the functionality is identical to IDENTITY as defined  
by SQL 2003. The JDO implementation at runtime doesn't need to  
generate key values; it gets the key values via getGeneratedKeys  
method. For schema generation with a MySQL database, you generate the  
id column as id INT NOT NULL AUTO_INCREMENT. Would this cause any  
problems, since MySQL doesn't support IDENTITY natively?
>
> We have (at least) 4 types of assigning ids directly in RDBMS ...  
> sequence,
> autoincrement, identity, serial. All need to be included in any  
> change.
>
I think for the TCK 2.0, we will always assume that the keys are  
generated by the database, and not explicitly by the JDO  
implementation. A future TCK maintenance revision can add more  
extensive tests for JDO implementation-generated keys.
>
>
> Clearly this also applies to "value-strategy" and not just to  
> "strategy" within
> datastore-identity.

I agree. Good thing that strategy is defined as CDATA so you can use  
any value you like.

> --
> Andy
>
> Hi Craig,
>
> Further to my previous mail, what is your proposed strategy doing  
> in the case of
> DB2 ?
> DB2 supports *both* sequences and IDENTITY columns. If "identity"  
> can mean
> sequence or IDENTITY then what does the implementation do to give  
> the user
> flexibility ?

I'd say strategy="identity" with no sequence means use the  
GENERATED ... AS IDENTITY.

strategy="identity" with sequence also specified means use a trigger  
and stored procedure to generate key values.

strategy="sequence" means use the named sequence explicitly in insert  
statements, as in INSERT INTO EMPLOYEE (ID, NAME) VALUES 
(named_sequence.nextval, ?).

Craig
>
> --
> Andy


Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message