You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@turbine.apache.org by Jeffery Painter <je...@jivecast.com> on 2020/03/20 16:40:19 UTC
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
Re: auto-increment with postgresql
Posted by Jeffery Painter <je...@jivecast.com>.
I will try that.... for this project, I actually have a second Java
program that is injesting code from xml archives and pushing it into the
db (outside of Turbine)... Turbine was just the front end for allowing
the team to query the data.
I was relying on the database to auto-gen the primary keys for me in
this case. I can test this though based on your recommendation and see
if it works without having to define the sequence stuff in nextval().
Cheers,
Jeff
On 3/21/20 8:20 AM, Thomas Vandahl wrote:
> Hi Jeff,
>
> On 20.03.20 17:40, Jeffery Painter wrote:
>> The torque template generator is creating sequences for auto-increment
>> primary keys, however, I think there is a flaw in the logic setup.
> The management of sequences is done within the Torque Java code and not
> in SQL. When Torque was designed, the features you describe probably
> didn't exist. Please check if the code works without your manual changes
> (it should). If it doesn't, please create an issue in the Torque project.
>
> My guess is that in your setup nextval() is going to be called twice,
> once by the default clause in your table column and once by the Java
> code in Torque. That means that you ids will increment by two every
> time. Could you please verify that?
>
> Generally, my intention was to modernize the Torque code for 5.0. If the
> ownership of a sequence is now a recommended best practice, we should
> add that to the Torque templates.
>
> Bye, Thomas
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@turbine.apache.org
> For additional commands, e-mail: dev-help@turbine.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@turbine.apache.org
For additional commands, e-mail: dev-help@turbine.apache.org
Re: auto-increment with postgresql
Posted by Thomas Vandahl <tv...@apache.org>.
Hi Jeff,
On 20.03.20 17:40, Jeffery Painter wrote:
> The torque template generator is creating sequences for auto-increment
> primary keys, however, I think there is a flaw in the logic setup.
The management of sequences is done within the Torque Java code and not
in SQL. When Torque was designed, the features you describe probably
didn't exist. Please check if the code works without your manual changes
(it should). If it doesn't, please create an issue in the Torque project.
My guess is that in your setup nextval() is going to be called twice,
once by the default clause in your table column and once by the Java
code in Torque. That means that you ids will increment by two every
time. Could you please verify that?
Generally, my intention was to modernize the Torque code for 5.0. If the
ownership of a sequence is now a recommended best practice, we should
add that to the Torque templates.
Bye, Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@turbine.apache.org
For additional commands, e-mail: dev-help@turbine.apache.org
Re: auto-increment with postgresql
Posted by 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
Re: auto-increment with postgresql
Posted by 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