Re: AUTO_INCREMENT patch

Rod Taylor <rbt(at)rbt(dot)ca> writes:
> Yeah.. JBoss is very annoying in this regard. A temporary solution seems
> to be to use BEFORE triggers to force the sequence to be used for the
> default value. You could also do this with an INSTEAD rule (something
> like the below):
>
> CREATE OR REPLACE RULE rulename AS ON INSERT
> TO tablename
> DO INSTEAD
> INSERT INTO tablename
> ( id, col1, ...)
> VALUES ( DEFAULT, NEW.col1, ...);

That's a good workaround for 3.2.1. As regards the upcoming 3.2.2, I
just found out that it has PostgreSQL-specific code to handle this,
though it's an incomplete fix. To summarize:

- add <entity-command>postgresql-fetch-seq</entity-command> to each
affected entity (or the <defaults> section) in jbosscmp-jdbc.xml;
this resolves the "failed to insert null" problem and also makes
sure JBoss knows the id of the newly created row.

- apply my auto_increment patch

- replace the bogus auto-increment template in the PostgreSQL and
PostgreSQL 7.2 mappings in standardjbosscmp-jdbc.xml with the
following:

<auto-increment-template>?1 AUTO_INCREMENT</auto-increment-template>

the current template (?1) works if you set the corresponding
<cmp-field>'s <sql-type> to SERIAL - but if you define a relation
that involves that field, the other endpoint will also be defined
as SERIAL! Obviously that's not a recipe for success.

> AUTO_INCREMENT is non-standard (MySQL only?), however the SQL200X
> proposals do have support for the more common IDENTITY syntax which can
> accomplish the same job as well as many others. (PostgreSQL does NOT
> have the general identity implementation yet)

the IDENTITY syntax is very similar to AUTO_INCREMENT, in fact you can
apply s/auto_increment/identity/ to my patch and have a useful subset
of it :) JBoss already knows about IDENTITY, since Hypersonic SQL and
MS SQL both support it.

(interestingly, JBoss doesn't seem to know that DB/2 also supports it)

> What you're looking for is the ability to force the column to use the
> IDENTITY even when the client provides a specific value:
>
> CREATE TABLE test(col integer GENERATED ALWAYS AS IDENTITY);

as mentioned above, that's no longer a problem with JBoss 3.2.2.

> See sections 10.22, 10.23, 11.3, and 11.4 of the SQL200X working draft
> for full details.
>
> ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf

I believe this is more up to date:

ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FDIS/4FDIS1-02-Foundation-2003.pdf

DES
--
Dag-Erling Smørgrav - des(at)des(dot)no