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 <j...@jivecast.com> An: d...@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-unsubscr...@turbine.apache.org For additional commands, e-mail: dev-h...@turbine.apache.org
smime.p7s
Description: S/MIME Cryptographic Signature