[apologies if this comes through twice]
I'm working on getting auto-incrementing CMP fields in JBoss 3.2.1 to
work with PostgreSQL data sources. There are currently two obstacles
to this.
The first problem is that the syntax JBoss uses for mapping Java types
onto SQL types isn't powerful enough to understand PostgreSQL's SERIAL
pseudo-types. The attached patch therefore adds support for MySQL's
AUTO_INCREMENT syntax to PostgreSQL. It works by defining a new
column constraint (CONSTR_AUTO_INCREMENT) which is handled specially
by transformColumnDefinition() - after it has transformed SERIAL
pseudo-types to the corresponding INT types, but before it actually
creates the sequence and adds the synthetic DEFAULT constraint. It is
thus possible to specify AUTO_INCREMENT on a SERIAL column; this will
generate a warning but does not have any other negative side effects.
It will however generate an error if AUTO_INCREMENT is specified on a
non-integer column.
(given that this is my first experience with PostgreSQL sources, I'd
like some feedback about the use of elog() / ereport())
Usage test:
des=# create table test1 ( foo int auto_increment, bar text );
NOTICE: CREATE TABLE will create implicit sequence "test1_foo_seq" for SERIAL column "test1.foo"
CREATE TABLE
des=# \d test1
Table "public.test1"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
foo | integer | not null default nextval('public.test1_foo_seq'::text)
bar | text |
des=# create table test2 ( foo int auto_increment, bar text );
NOTICE: CREATE TABLE will create implicit sequence "test2_foo_seq" for SERIAL column "test2.foo"
CREATE TABLE
des=# \d test2
Table "public.test2"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
foo | integer | not null default nextval('public.test2_foo_seq'::text)
bar | text |
des=# create table test3 ( foo serial auto_increment, bar text );
WARNING: both SERIAL and AUTO_INCREMENT specified for column 'test3.foo'
NOTICE: CREATE TABLE will create implicit sequence "test3_foo_seq" for SERIAL column "test3.foo"
CREATE TABLE
des=# \d test3
Table "public.test3"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
foo | integer | not null default nextval('public.test3_foo_seq'::text)
bar | text |
des=# create table test4 ( foo text auto_increment, bar text );
ERROR: AUTO_INCREMENT columns must be of integer type
The second problem is an issue which is probably best solved in JBoss
and not in PostgreSQL (JBoss insists on inserting null into the auto-
incrementing column, which obviously doesn't work)
DES
--
Dag-Erling Smørgrav - des@des.no
