Re: Datastore identity strategy

[Craig L Russell]

Hi Andy,

Thanks for the quick reply to this issue.

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

Hi Craig,


sequences. You have a sequence option for that.

I don't read the spec (as it currently is) to allow identity to utilise

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>

name" in a datastore that utilises sequences ?

So if you have strategy="identity" to include sequences what is the "sequence

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>

"IDENTITY" type.

So how do you specify MySQL "autoincrement" if you remove this ? They aren't

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.

datastore-identity.

Clearly this also applies to "value-strategy" and not just to "strategy" within

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

--
Andy

Hi Craig,


DB2 ?

flexibility ?

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

Craig

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, ?).

--
Andy

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

smime.p7s
Description: S/MIME cryptographic signature