[jira] Resolved: (OPENJPA-1489) Generated UNIQUE constraint names and HSQL

["Milosz Tylenda (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OPENJPA-1489?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Milosz Tylenda resolved OPENJPA-1489.
-------------------------------------

       Resolution: Duplicate
    Fix Version/s: 2.0.0-beta
                   1.3.0
         Assignee: Milosz Tylenda

> Generated UNIQUE constraint names and HSQL 
> -------------------------------------------
>
>                 Key: OPENJPA-1489
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-1489
>             Project: OpenJPA
>          Issue Type: Improvement
>          Components: sql
>    Affects Versions: 1.2.2
>         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)
>            Reporter: Hannes Schmidt
>            Assignee: Milosz Tylenda
>            Priority: Minor
>             Fix For: 1.3.0, 2.0.0-beta
>
>
> 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 ...

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.