Bug 1014591 - hibernate_sequence creation is confusing in SQL scripts
hibernate_sequence creation is confusing in SQL scripts
Product: JBoss Fuse Service Works 6
Classification: JBoss
Component: Configuration (Show other bugs)
Unspecified Unspecified
unspecified Severity high
: ER6
: ---
Assigned To: Steve Johnson
Matej Melko
: Reopened
Depends On:
  Show dependency treegraph
Reported: 2013-10-02 07:24 EDT by Jiri Pechanec
Modified: 2015-11-02 03:08 EST (History)
4 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
The SQL scripts used to create Hibernate for RTGov are not currently standardised. This issue does not impact on users. The scripts still work.
Story Points: ---
Clone Of:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

  None (edit)
Description Jiri Pechanec 2013-10-02 07:24:25 EDT
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
Comment 5 Steve Johnson 2013-10-18 10:43:50 EDT
Files content corrected and moved outside of rtgov sql module.
Comment 6 Jiri Pechanec 2013-10-22 02:27:53 EDT
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;
Comment 7 Steve Johnson 2013-10-22 06:50:52 EDT
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.
Comment 8 kconner 2013-10-22 08:52:39 EDT
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.
Comment 9 Anne-Louise Tangring 2013-12-12 12:26:46 EST
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

Note You need to log in before you can comment on or make changes to this bug.