db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Scott Eade <se...@backstagetech.com.au>
Subject Re: PostgreSQL ID generation
Date Sun, 29 Aug 2004 12:50:15 GMT
Henning P. Schmiedehausen wrote:

>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.
>  
>
I don't have a problem with your patch, it works fine and it removes the 
appearance of a problem highlighted in the faq:
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: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message