OpenJPA
  1. OpenJPA
  2. OPENJPA-1489

Generated UNIQUE constraint names and HSQL

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Duplicate
    • Affects Version/s: 1.2.2
    • Fix Version/s: 1.3.0, 2.0.0-beta
    • Component/s: sql
    • Labels:
      None
    • Environment:
      OpenJPA 1.2.2
      HSQL 1.8.0.10 and HSQL 1.8.1.1 and 2.0.0-rc8
      Mac OS X 10.6.2
      Java 1.6.0_17 Java HotSpot(TM) 64-Bit Server VM (build 14.3-b01-101, mixed mode)

      Description

      In my unit tests, I'm using the openjpa.jdbc.SynchronizeMappings feature to have OpenJPA create the schema in an HSQL in-memory database. Two of the tables in my schema each have a unique constraint that lists the same column names (foo,bar) in the same order. The generated name for both constraints is UNQ_foobar. HSQL apparently puts the names of unique constraints into a schema-wide namespace which causes the CREATE TABLE statement for the second table to fail with "constraint already exist".

      @Entity
      @Table( uniqueConstraints = { @UniqueConstraint( columnNames =

      { "foo", "bar" } ) } )
      public class X { ... private String foo; private long bar; ... }

      @Entity
      @Table( uniqueConstraints = { @UniqueConstraint( columnNames = { "foo", "bar" }

      ) } )
      public class Y

      { ... private String foo; private long bar; ... }

      912 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856> executing stmnt 1211647530 CREATE TABLE X ( ... , foo VARCHAR(22) NOT NULL, ... , bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE (foo, bar))
      915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856> [3 ms] spent
      915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826> executing stmnt 853942561 CREATE TABLE Y ( ... , foo VARCHAR(22) NOT NULL, ... , bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE (foo, bar))
      920 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826> [5 ms] spent
      Jan 30, 2010 11:46:16 AM org.springframework.test.context.TestContextManager beforeTestMethod
      WARNING: Caught exception while allowing TestExecutionListener [org.springframework.test.context.transaction.TransactionalTestExecutionListener@2773a64a] to process 'before' execution of test method [public void com.eyealike.ps.core.test.CollectionDaoTest.setup()] for test instance [com.eyealike.ps.core.test.CollectionDaoTest@19aa5882]
      org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is <openjpa-1.2.2-r422266:898935 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: Constraint already exists: UNQ_OWNERIDKEY in statement [CREATE TABLE Y ... } [code=-60, state=S0011]

      Note that I tweaked my sample code and log trace a little to simplify the test case.

      I didn't try to reproduce this with OpenJPA 2.0.0 yet. From looking at the code in the 2.0.0 trunk, I can tell that this part has been rewritten completely and this issue might not apply to 2.0.0. I also don't know whether HSQL is violating the SQL standard by requiring unique constaint names to be schema-unique, not just table-unique.

      What works for me as a workaround is to reorder the columns in the constraint on the second table.

      Assuming this is not a bug in HSQL and assuming it also reproduces in 2.0.0 beta, I'd naively prefix constraint names with the table name but I'm no specialist in either SQL or JPA ...

        Issue Links

          Activity

          Hide
          Milosz Tylenda added a comment -

          Hannes, this should be fixed in 1.3.0 snapshot and 2.0.0 beta - OPENJPA-1165. You are right, the functionality has been rewritten in 1.3 and 2.0 branches. As for porting the fix to 1.2.3, we might ask Mike Dick who maintains that branch but I bet he will find such a change too risky/incompatible for a maintenance branch.

          Show
          Milosz Tylenda added a comment - Hannes, this should be fixed in 1.3.0 snapshot and 2.0.0 beta - OPENJPA-1165 . You are right, the functionality has been rewritten in 1.3 and 2.0 branches. As for porting the fix to 1.2.3, we might ask Mike Dick who maintains that branch but I bet he will find such a change too risky/incompatible for a maintenance branch.
          Hide
          Hannes Schmidt added a comment -

          Fixed spelling

          Show
          Hannes Schmidt added a comment - Fixed spelling

            People

            • Assignee:
              Milosz Tylenda
              Reporter:
              Hannes Schmidt
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development