[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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
