Re: auto-increment with postgresql

[Georg Kallidis <ge...@cedis.fu-berlin.de>]

Hi Jeff,

(cross posting this to torque-dev)

>comfortable enough with torque source 
> code to make the necessary changes, but happy to help if you point me in 

> the right direction :-)
As I did something similar, I could give you some hints below, and answer 
this also, though a little bit late ;)

Thomas is right - if you implemented the sequences as you described, as 
postgresql adapter has by default id method type "sequence", not native, 
the sequence is called twice. Torque adapter type "sequence" will call the 
sequence "manually", if inserting new data. But changing this to native 
would be not sufficient - to get a consistent behaviour /schema we need to 
change more - like Thomas said torque templates (find some hints below) 
have to be changed. 
On the other hand using "serial" - serial is a pseudo type in postgresql 
only (?) - we may add this in database.xsd. Reading the value - this would 
be just an integer type (subtypes small and big optionally). But this is 
not generic - we may end up with a postgres specific schema. This seems 
though quite easy to implement in the end.
Though, to change the sequences like you described, you would _not_ need 
to change the database schema, but the composition of database generation. 
This is possible, but you need to understand, how torque templates 
generates sql, then you could move the create sequence, add autoincrement, 
and add sequence assignment statement. Start with ddl.xml to find where 
the database generation is done. You find it in torque-templates -> java 
resources org/apache/torque/templates/sql/outlets/ddl.xml. Search for 
"sequence" here and you find it as a "mergepoint in an "outlet"-element. 
This element has an attribute path which points to database dependent 
source file: ddl/${option:database}/table.groovy (in tt -> java resources 
sql/templates/ddl/postgresql/table.groovy). There is always a groovy and 
vm (velocity) version of this partials, change both if needed. You may 
edit this and just move the line with "sequence" before the create table 
statement (do the same for table.vm to be sure). You may move the 
elements, inside and outside here, and you may use mergepoints, which are 
not used, e.g.tableCreateOptions.groovy to add an "alter sequence" (you 
may just copy the code from sequence and change it to what you need, using 
primaryKeyColumnNames, name = table name (?). You may also check or edit 
sequence.groovy in (templates/ddl/postgresql).  To use the increment 
(nextval) update PlatformPostgresImpl method getAutoIncrement method, you 
probably have to insert the table name later in velocity templates and 
change postgresql adapter to type native - I am not sure, if this done 
anywhere and if it is possible. 
As a result, I would not yet start at once with either of those two. The 
first one breaks the rules, the second one, is difficult in the details.
May be the best is just to create an new Torque issue, as "improvement 
feature" and hopefully wait for the fearless resolver ? ;-)

Best regards, Georg




Von:    Jeffery Painter <je...@jivecast.com>
An:     dev@turbine.apache.org
Datum:  20.03.2020 17:40
Betreff:        auto-increment with postgresql



Hello turbine/torque devs,


I don't want to forget this...

I have modified my postgresql database manually for now since I just 
have couple tables I am dealing with on this project and need to get it 
done today.

The torque template generator is creating sequences for auto-increment 
primary keys, however, I think there is a flaw in the logic setup.

I have a simple table here (removed a bunch of the misc columns to show 
what is going on...)

from schema.xml

...

   <table name="SIGNAL_SUMMARY" idMethod="native">
         <column name="REF_ID"            required="true" 
primaryKey="true" type="INTEGER" autoIncrement="true"/>
         <column name="DRUG_ID"          type="INTEGER"/>
         <column name="EVENT_ID"         type="INTEGER"/>
         <column name="SERIOUS"          type="BOOLEANINT" default="0"/>

     <foreign-key foreignTable="DRUG">
         <reference local="DRUG_ID" foreign="REF_ID"></reference>
     </foreign-key>
     <foreign-key foreignTable="DRUG_EVENT">
         <reference local="EVENT_ID" foreign="REF_ID"></reference>
     </foreign-key>

   </table>

The above works fine in MySQL. When switching to PostgreSQL, I get the 
following code...

-- -----------------------------------------------------------------------
-- SIGNAL_SUMMARY
-- -----------------------------------------------------------------------
CREATE TABLE SIGNAL_SUMMARY
(
     REF_ID INTEGER NOT NULL,
     DRUG_ID INTEGER,
     EVENT_ID INTEGER,
     SERIOUS INT2 default 0,
     PRIMARY KEY(REF_ID)
);


CREATE SEQUENCE SIGNAL_SUMMARY_SEQ INCREMENT BY 1 START WITH 1 NO 
MAXVALUE NO CYCLE;

...

To make this work for real, the sequence should be created before the 
table, and (2) update the auto-increment to use the sequence, finally 
(3) assign the sequence to be owned by 'table_name.id'

1. CREATE SEQUENCE SIGNAL_SUMMARY_SEQ INCREMENT BY 1 START WITH 1 NO 
MAXVALUE NO CYCLE;

2. create table... update the REF_ID line of sql to read...

   REF_ID INTEGER NOT NULL DEFAULT nextval('SIGNAL_SUMMARY_SEQ'),

3. ALTER SEQUENCE SIGNAL_SUMMARY_SEQ OWNED BY SIGNAL_SUMMARY.REF_ID;


... alternatively, it looks like you can do CREATE TABLE table_name( id 
SERIAL ); and PostgreSQL supposedly handles this all automagically for 
you. I did not try this.

I found help from this link:

https://www.postgresqltutorial.com/postgresql-serial/

I am not sure I am confident / comfortable enough with torque source 
code to make the necessary changes, but happy to help if you point me in 
the right direction :-)


Thanks,

Jeffery







---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@turbine.apache.org
For additional commands, e-mail: dev-help@turbine.apache.org