[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 - [EMAIL PROTECTED]
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/analyze.c,v
retrieving revision 1.283
diff -u -u -r1.283 analyze.c
--- src/backend/parser/analyze.c 1 Aug 2003 00:15:22 -0000 1.283
+++ src/backend/parser/analyze.c 2 Aug 2003 16:01:59 -0000
@@ -949,6 +949,28 @@
}
}
+ /* Check for AUTO_INCREMENT constraint */
+ foreach(clist, column->constraints)
+ {
+ constraint = lfirst(clist);
+ if (IsA(constraint, Constraint) &&
+ constraint->contype == CONSTR_AUTO_INCREMENT) {
+ column->typename->typeid = LookupTypeName(column->typename);
+ if (column->typename->typeid != InvalidOid)
+ column->typename->names = NIL;
+ if (is_serial)
+ elog(WARNING, "both SERIAL and AUTO_INCREMENT specified for column '%s.%s'",
+ cxt->relation->relname, column->colname);
+ else if (column->typename->typeid != INT4OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("AUTO_INCREMENT columns must be of integer type")));
+ else
+ is_serial = true;
+ break;
+ }
+ }
+
/* Do necessary work on the column type declaration */
transformColumnType(pstate, column);
@@ -1112,6 +1134,10 @@
case CONSTR_ATTR_DEFERRED:
case CONSTR_ATTR_IMMEDIATE:
/* transformConstraintAttrs took care of these */
+ break;
+
+ case CONSTR_AUTO_INCREMENT:
+ /* already processed */
break;
default:
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.427
diff -u -u -r2.427 gram.y
--- src/backend/parser/gram.y 19 Jul 2003 20:20:52 -0000 2.427
+++ src/backend/parser/gram.y 2 Aug 2003 12:01:23 -0000
@@ -327,7 +327,7 @@
/* ordinary key words in alphabetical order */
%token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD AFTER
AGGREGATE ALL ALTER ANALYSE ANALYZE AND ANY ARRAY AS ASC
- ASSERTION ASSIGNMENT AT AUTHORIZATION
+ ASSERTION ASSIGNMENT AT AUTHORIZATION AUTO_INCREMENT
BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
BOOLEAN_P BOTH BY
@@ -1604,6 +1604,12 @@
n->fk_del_action = (char) ($5 & 0xFF);
n->deferrable = FALSE;
n->initdeferred = FALSE;
+ $$ = (Node *)n;
+ }
+ | AUTO_INCREMENT
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_AUTO_INCREMENT;
$$ = (Node *)n;
}
;
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/keywords.c,v
retrieving revision 1.140
diff -u -u -r1.140 keywords.c
--- src/backend/parser/keywords.c 25 Jun 2003 03:40:18 -0000 1.140
+++ src/backend/parser/keywords.c 2 Aug 2003 12:00:54 -0000
@@ -50,6 +50,7 @@
{"assignment", ASSIGNMENT},
{"at", AT},
{"authorization", AUTHORIZATION},
+ {"auto_increment", AUTO_INCREMENT},
{"backward", BACKWARD},
{"before", BEFORE},
{"begin", BEGIN_P},
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
