Henning P. Schmiedehausen wrote:
I don't have a problem with your patch, it works fine and it removes the appearance of a problem highlighted in the faq:Hi,
currently, PostgreSQL uses a kind of "autoincrement in the database" for "native" id generation.
It uses a sequence, created e.g. with
create sequence foo_bar_seq;
and then teaches the tables to do autoincrementing a la MySQL:
create table foo ( bar integer default nextval('foo_bar_seq') primary key (bar) );
While this works well, it is not compatible to e.g. Hibernate which wants to do its own primary key management with a sequence.
It also makes it impossible to build "better" (e.g. a hi/lo) sequence generator because the getIDMethodSQL method from the adapter returns "select currval('<sequence name>')".
I'd propose to change the PostgreSQL adapter to use the native sequence model. It does the following things:
- Changes the native type of ID generation for PostgreSQL from AUTO_INCREMENT to SEQUENCE.
- Makes the DBPostgres adapter return "select nextval('<sequence name>') from getIDMethodSQL
- Removes the DEFAULT nextval(..) statement from the ID columns in table generation
- Allows AUTO_INCREMENT columns in all tables, regardless whether the ID mode is native or not. A "serial" column is really an auto incrementing
column, no matter how the id elements are generated.
Please note that the current sql generation makes no sense. If we create
or SQL with the DEFAULT nextval('sequence'), why restrict serials to tables
that use native Ids? They don't use this anyway.
This is the natural model of ID generation with PostgreSQL and is compatible to e.g. Hibernate (you can use a Hibernate Application and a Torque Application without any changes in the database schema.
I'd really like to see this patch go in.
http://wiki.apache.org/db-torque/PostgreSQLFAQ#head-e2be9dec72b7c31f16be1b4e70364e8190684691
On a related note: I don't understand why the sequence creation has been dropped from the schema generation? They could be generated in a second file, but as it currently is, the regular sequence of "write a schema, torque:om, torque:sql, torque:create-db, torque:insert-sql, run your application" no longer works because the sequences are missing.
I'd very much love to see this revision:
http://cvs.apache.org/viewcvs.cgi/db-torque/src/generator/src/templates/sql/base/postgresql/table.vm?r1=1.1&r2=1.1.2.1&only_with_tag=TORQUE_3_1_BRANCH
reverted.
What am I missing Henning? If my schema is:
<table name="category" idMethod="native">
<column name="category_id" required="true" autoIncrement="true"
primaryKey="true" type="INTEGER"/>
<column name="name" size="100" type="VARCHAR"/>
<id-method-parameter name="seqName" value="CATEGORY_CATEGORY_ID_SEQ"/>
</table>The generated sql is:
----------------------------------------------------------------------------- -- category ----------------------------------------------------------------------------- DROP TABLE category CASCADE;
CREATE TABLE category
(
category_id serial,
name varchar (100),
PRIMARY KEY (category_id)
);When this is executed PostgreSQL automatically creates a sequence (from pgAdmin III):
CREATE SEQUENCE public.category_category_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
The above sequence is automatically dropped when the table is dropped. Woo hoo, everything works!
The trick is the following line in the schema:
<id-method-parameter name="seqName" value="CATEGORY_CATEGORY_ID_SEQ"/>
this is glossing over the fact that Table.getSequenceName() by default (if no seqName is provided in the schema) returns just ${TABLE_NAME}_SEQ (in my example CATEGORY_SEQ) rather than ${TABLE_NAME}_${COLUMN_NAME}_SEQ.
In my schemas I have always been specifying seqName for my PostgreSQL sequences to get Torque to use the sequences that are automatically created. The patch you refer to above was stopping Torque from generating the code to manage the sequences because PostgreSQL is doing this automatically - is it not doing this for you? Up until now I have always had to remove the Torque generated sequence handling code because it only caused problems.
My preference is to have the MapBuilder classes generated with sequence names that match those used by the database, but to still allow this to be overridden by seqName. As things currently stand this is not set up to handle different values for different database systems and I am not sure if there are any unwritten rules to make this easy to apply (e.g. can only one primary key column be configured to be autoIncrement="true"?)
Or am I just totally missing something obvious?
Scott
-- Scott Eade Backstage Technologies Pty. Ltd. http://www.backstagetech.com.au
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
