RTGov contains hibernate_sequence.*.sql scripts. The problem is that the situation with these scripts is very confused gadget-web.gadget-core.Oracle10gDialect.sql: drop sequence hibernate_sequence; gadget-web.gadget-core.Oracle10gDialect.sql: create sequence hibernate_sequence; gadget-web.gadget-core.PostgreSQLDialect.sql: drop sequence hibernate_sequence; gadget-web.gadget-core.PostgreSQLDialect.sql: create sequence hibernate_sequence; hibernate_sequence.DB2Dialect.sql:create sequence hibernate_sequence; hibernate_sequence.H2Dialect.sql:create sequence hibernate_sequence; hibernate_sequence.MySQL5Dialect.sql:create table hibernate_sequence ( next_val bigint ); hibernate_sequence.MySQL5Dialect.sql:insert into hibernate_sequence values ( 1 ); hibernate_sequence.PostgreSQLDialect.sql:create sequence hibernate_sequence; hibernate_sequence.SQLServer2008Dialect.sql:create table hibernate_sequence ( next_val bigint ); hibernate_sequence.SQLServer2008Dialect.sql:insert into hibernate_sequence values ( 1 ); Oracle10 has this sequence handle only in gadget-web.gadget-core DB2, H2, MySQL and SQLServer has sequence handle in hibernate_sequence.*.sql PostgreSQL has the sequence handled in both files And if rtgov is not installed then hibernate_sequence table is not created at all
Files content corrected and moved outside of rtgov sql module.
The issue is still present in ER6 rtgov/gadget-web.gadget-core.Oracle10gDialect.sql: drop sequence hibernate_sequence; rtgov/gadget-web.gadget-core.Oracle10gDialect.sql: create sequence hibernate_sequence; rtgov/gadget-web.gadget-core.PostgreSQLDialect.sql: drop sequence hibernate_sequence; rtgov/gadget-web.gadget-core.PostgreSQLDialect.sql: create sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-store.PostgreSQLDialect.sql: drop sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-store.PostgreSQLDialect.sql: create sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-scheduler.PostgreSQLDialect.sql: drop sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-scheduler.PostgreSQLDialect.sql: create sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-bpel.Oracle10gDialect.sql: drop sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-bpel.Oracle10gDialect.sql: create sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-store.Oracle10gDialect.sql: drop sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-store.Oracle10gDialect.sql: create sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-bpel.PostgreSQLDialect.sql: drop sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-bpel.PostgreSQLDialect.sql: create sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-scheduler.Oracle10gDialect.sql: drop sequence hibernate_sequence; riftsaw-ode/riftsaw-dao-jpa.ode-scheduler.Oracle10gDialect.sql: create sequence hibernate_sequence;
Hi Jiri, Ok I believe there were two issues related to this ticket. 1) Multiple drop/create sequence statements for Oracle & PostgreSQL 2) hibernate_sequence table not created when rtgov *not* installed (for all other rdbms) Issue 2) should have been fixed by the last update by the introduction on a <fsw-install>/sql/common directory. The hibernate_sequence table should now be created regardless of whether rtgov installed or not. Issue 1) is probably not fixable due to the way hibernate exports SQL statements to file. I suspect this is not a blocker as although there are multiple drop/create sequence statements it does not stop it from working. Each persistence-unit in a JPA persistence descriptor (persistence.xml) is the unit of generation for a SQL export i.e. for each persistence-unit you get one exported SQL file. For every persistence-unit exported hibernate will export a native sequence for Oracle and for PostgreSQL (therefore multiple drop/create sequence statements). At this time there is no way to config this without java code changes or hibernate mapping file introduction across all fsw components. For all other rdbms you must manually create a native sequence or hibernate_sequence table otherwise runtime sequence generation will fail (hence the reason for <fsw-install>/sql/common) The only way to solve this multiple identical statements in general is to provide another level of schema tooling that as input takes all generated sql files and creates one canonical schema which will remove duplicate statements (sequences, tables, fk/pk relationships). However, in doing so there are a number of other potential dangers in removing multiple same statements ie different java->table mappings for same class, different foreign/pk relationships generated representing the same logical relationship etc. So although it doesn't look great that we get multiple drop/create sequence statements it works in the end. It would require more engineering effort and tooling to get a single schema across all fsw components that are originally sql generated in isolation from each other. Maybe comment from Kevin Conner needed re: overall persistence architecture.
I opened https://bugzilla.redhat.com/show_bug.cgi?id=1013683 to deal with the issues surrounding the ID generation and have suggested that we should switch to the table generator. This has not yet been scheduled however I would hope to get it in before final otherwise we will have migration issues moving forward. I'll talk to the project leads later this week and create associated JIRAs.
The issue is not a showstopper by the way, things will still work ok. Its just a lot more standard and uniform across the database tables