how to specify index / unique constraint on multiple columns of a container table?

[Andy Schlaikjer <ha...@cs.cmu.edu>]

Hi all,

Is it possible to specify a unique constraint over multiple columns 
within a container table? I know a unique index on a single column is 
supported via @ElementIndex. Here's a small example:

@Entity public class Person {
   @Id private long id;

   @PersistentCollection
   @ContainerTable(
     name = "PersonAliases",
     joinColumns = @XJoinColumn(name = "person_id")
   )
   @ElementColumn(name = "value")
   private Set<String> aliases;
   // TODO uniqueConstraint = {"person_id", "value"}
}

The DDL generated with OpenJPA 1.2.0 and MySQL DBDictionary is

CREATE TABLE Person (id BIGINT NOT NULL, PRIMARY KEY (id)) TYPE = innodb
CREATE TABLE PersonAliases (person_id BIGINT, value VARCHAR(255)) TYPE = 
innodb
CREATE INDEX I_PRSNLSS_PERSON_ID ON PersonAliases (person_id)

The same question seems to apply to join tables.

-Andy

Re: how to specify index / unique constraint on multiple columns of a container table?

[Andy Schlaikjer <ha...@cs.cmu.edu>]

This doesn't quite work.. OpenJPA 1.2.0 complains about Alias (an 
Embeddable) not having an identity type:

org.apache.openjpa.persistence.ArgumentException: Cannot manipulate 
identity of type "edu.cmu.hazen.domain.Alias": it's identity type is 
unknown.


Andy Schlaikjer wrote:
> I think may I've figured it out. It seems I can use a @JoinTable along 
> with @PersistentCollection to achieve something like this:
> 
> @Embeddable
> public class Alias {
>   private String value;
>   private int frequency;
> }
> 
> @Entity
> public class Person {
>   @Id
>   private long id;
> 
>   @PersistentCollection(elementEmbedded = true)
>   @ElementDependent
>   @JoinTable(
>     name = "PersonAlias",
>     joinColumns = @JoinColumn(name = "person_id"),
>     uniqueConstraints = @UniqueConstraint(
>       columnNames = { "person_id", "value" }
>     )
>   )
>   @OrderBy(value = "frequency desc, value asc")
>   private SortedSet<Alias> aliases;
> }
> 
> CREATE TABLE Person (id BIGINT NOT NULL, PRIMARY KEY (id))
> CREATE TABLE PersonAlias (person_id BIGINT NOT NULL, value VARCHAR(255) 
> NOT NULL, frequency INTEGER, UNIQUE UNQ_person_idvalue (person_id, value))
> 
> -Andy

Re: how to specify index / unique constraint on multiple columns of a container table?

[Andy Schlaikjer <ha...@cs.cmu.edu>]

I think may I've figured it out. It seems I can use a @JoinTable along 
with @PersistentCollection to achieve something like this:

@Embeddable
public class Alias {
   private String value;
   private int frequency;
}

@Entity
public class Person {
   @Id
   private long id;

   @PersistentCollection(elementEmbedded = true)
   @ElementDependent
   @JoinTable(
     name = "PersonAlias",
     joinColumns = @JoinColumn(name = "person_id"),
     uniqueConstraints = @UniqueConstraint(
       columnNames = { "person_id", "value" }
     )
   )
   @OrderBy(value = "frequency desc, value asc")
   private SortedSet<Alias> aliases;
}

CREATE TABLE Person (id BIGINT NOT NULL, PRIMARY KEY (id))
CREATE TABLE PersonAlias (person_id BIGINT NOT NULL, value VARCHAR(255) 
NOT NULL, frequency INTEGER, UNIQUE UNQ_person_idvalue (person_id, value))

-Andy

Re: how to specify index / unique constraint on multiple columns of a container table?

[Andy Schlaikjer <ha...@cs.cmu.edu>]

Pinaki, thanks for the reply.

I've looked over the tests you'd mentioned. They do elucidate how to 
apply unique constraints on join tables via @JoinTable(uniqueConstraints 
= ...), but don't show how I might accomplish something similar with 
@ContainerTable. Here are relevant direct links:

TestUniqueConstraint.java
http://fisheye6.atlassian.com/browse/openjpa/branches/sql-cache/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/unique/TestUniqueConstraint.java?r=trunk

UniqueA.java
http://fisheye6.atlassian.com/browse/openjpa/branches/sql-cache/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/unique/UniqueA.java?r=trunk

UniqueB.java
http://fisheye6.atlassian.com/browse/openjpa/branches/sql-cache/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/unique/UniqueB.java?r=trunk

I had thought that @ContainerTable was meant to be a corollary to 
@JoinTable, but @ContainerTable lacks a uniqueConstraints element.

@JoinTable
http://java.sun.com/javaee/5/docs/api/javax/persistence/JoinTable.html

@ContainerTable
http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/javadoc/org/apache/openjpa/persistence/jdbc/ContainerTable.html

Thoughts?


Pinaki Poddar wrote:
>> Is it possible to specify a unique constraint over multiple columns within
> a container table?
> 
> Yes.
> 
> Please refer to the tests and domain classes of
> org.apache.openjpa.persistence.jdbc.unique package as example.

Re: how to specify index / unique constraint on multiple columns of a container table?

[Pinaki Poddar <pp...@apache.org>]

> Is it possible to specify a unique constraint over multiple columns within
a container table?

Yes.

Please refer to the tests and domain classes of
org.apache.openjpa.persistence.jdbc.unique package as example.
 


-- 
View this message in context: http://n2.nabble.com/how-to-specify-index---unique-constraint-on-multiple-columns-of-a-container-table--tp1317345p1331793.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.