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