Mailing-List: contact torque-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Developers List" <torque-dev@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Message-ID: <41445BE0.5040306@backstagetech.com.au>
Date: Mon, 13 Sep 2004 00:23:28 +1000
From: Scott Eade <seade@backstagetech.com.au>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: Apache Torque Developers List <torque-dev@db.apache.org>
Subject: Re: PostgreSQL ID generation
References: <cgfls3$4qb$1@tangens.hometree.net>
In-Reply-To: <cgfls3$4qb$1@tangens.hometree.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

[Delayed response - several reasons...]

Henning P. Schmiedehausen wrote:

>... must think some more.
>
A few things strike me:

   1. The schema reference (included in the Generator section of the
      Torque site) is by no means complete and is scant on detail given
      how important it is in the scheme of things (no pun intended). 
      This document should provide a decent specification of the effect
      the various elements and attributes will have on the generated sql
      and OM code rather than being a partial reference used to assist
      with a trial and error decoding process.
   2. PostgreSQL has a serial data type that Torque already supports to
      a limited extent.  IMHO we should fully embrace this by:
         1. Generating schema sql that used the SERIAL datatype rather
            than "default nextval('foo_bar_seq')" (the latter with a
            different sequence name structure is what PostgreSQL
            maintains automatically when the SERIAL datatype is used).
         2. Leaving sequence naming (with the available override),
            sequence creation and destruction (drop) to PostgreSQL.
         3. Enhancing the JDBC target to detect the use of sequences so
            as to produce schema files that use SERIAL.
         4. Determine whether or not the id-method-parameter element has
            any use.
   3. The column elements autoIncrement attribute in the schema is not
      clearly defined.  Correct me if I am wrong, but from your comments
      and your patch it appears that you interpret a combination of
      idMethod="native" for the table and autoIncrement="true" on a
      column to be the triggers for the use of a sequence.  I actually
      thought that primaryKey="true" had something to do with it and
      from the generated code this does in fact appear to be the case.

    Generate the sql for the following schema (comments on the resultant
    sql are included within):


  <!-- idbroker with autoIncrement=false on PK -->
  <!-- Resultant sql seems okay, not sure how IDBroker would behave. -->
  <table name="dog_idbroker" idMethod="idbroker">
    <column name="dog_id" required="true" autoIncrement="false"
        primaryKey="true" type="INTEGER"/>
    <column name="animal_id" required="true" autoIncrement="false"
        primaryKey="false" type="INTEGER"/>
    <column name="name" size="100" type="VARCHAR"/>
  </table>

  <!-- native with autoIncrement=false on PK -->
  <!-- Resultant sql includes:
            dog_id integer DEFAULT nextval('dog_native_SEQ') NOT NULL
       Should be:
            dog_id integer NOT NULL
   -->
  <table name="dog_native" idMethod="native">
    <column name="dog_id" required="true" autoIncrement="false"
        primaryKey="true" type="INTEGER"/>
    <column name="animal_id" required="true" autoIncrement="false"
        primaryKey="false" type="INTEGER"/>
    <column name="name" size="100" type="VARCHAR"/>
    <!--id-method-parameter name="seqName" value="DOG_NATIVE_DOG_ID_SEQ"/-->
  </table>

  <!-- idbroker with autoIncrement=true on PK -->
  <!-- Resultant sql includes:
            cat_id
       Should be:
            cat_id integer NOT NULL
   -->
  <table name="cat_idbroker" idMethod="idbroker">
    <column name="cat_id" required="true" autoIncrement="true"
        primaryKey="true" type="INTEGER"/>
    <column name="animal_id" required="true" autoIncrement="false"
        primaryKey="false" type="INTEGER"/>
    <column name="name" size="100" type="VARCHAR"/>
  </table>

  <!-- native with autoIncrement=true on PK -->
  <!-- Resultant sql includes (IMO correct):
            cat_id serial
   -->
  <table name="cat_native" idMethod="native">
    <column name="cat_id" required="true" autoIncrement="true"
        primaryKey="true" type="INTEGER"/>
    <column name="animal_id" required="true" autoIncrement="false"
        primaryKey="false" type="INTEGER"/>
    <column name="name" size="100" type="VARCHAR"/>
    <!--id-method-parameter name="seqName" value="CAT_NATIVE_CAT_ID_SEQ"/-->
  </table>

  <!-- idbroker with autoIncrement=true on non-PK -->
  <!-- Resultant sql includes:
            mouse_id integer NOT NULL,
            animal_id
       Should be:
             mouse_id integer NOT NULL,
            animal_id integer NOT NULL
  -->
  <table name="mouse_idbroker" idMethod="idbroker">
    <column name="mouse_id" required="true" autoIncrement="false"
        primaryKey="true" type="INTEGER"/>
    <column name="animal_id" required="true" autoIncrement="true"
        primaryKey="false" type="INTEGER"/>
    <column name="name" size="100" type="VARCHAR"/>
  </table>

  <!-- native with autoIncrement=false on non-PK -->
  <!-- Resultant sql includes:
            mouse_id integer DEFAULT nextval('mouse_native_SEQ') NOT NULL,
            animal_id serial
       Should be:
            mouse_id integer NOT NULL,
            animal_id serial
   -->
  <table name="mouse_native" idMethod="native">
    <column name="mouse_id" required="true" autoIncrement="false"
        primaryKey="true" type="INTEGER"/>
    <column name="animal_id" required="true" autoIncrement="true"
        primaryKey="false" type="INTEGER"/>
    <column name="name" size="100" type="VARCHAR"/>
    <!--id-method-parameter name="seqName" 
value="MOUSE_NATIVE_MOUSE_ID_SEQ"/-->
  </table>

We could update Torque to consistently use the SERIAL datatype, and to 
only so this when autoIncrement is true.  In addition to this it would 
be great if we could provide a means of specifying the sequence name for 
the particular column it is use on, perhaps seqName should be a column 
attribute (and perhaps an option to determine whether or not the 
sequence should be created and dropped would help out people with 
existing sequences).

Basically as it stands it is a right mess and needs to be addressed 
before the next RC.

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