CMP 2.0 and many-to-many relationships

[jeffdelong]

I am having a problem with a many-to-many relationship with JBoss 3.0.0-beta (Feb 21). I have two Entity EJBs, a GroupEJB and UserEJB. They have a many-to-many relationship, defined as "Membership" in the ejb-jar.xml file. The Tables have been specified as ORG_USER, ORG_GROUP, and ORG_MEMBERSHIP in the jbosscmp_jdbc.xml file. JBoss creates the first two tables correctly, but creates (?) the relationship table as GroupEjb_users_UserEJB_group, although I cannot find this table in my schema. Furthermore, when attempting to create the EJBs and populate the tables (with users Martin, Fred, etc. where Martin is a member of group LoanProcessor and Fred is a member of Group LoanProcessor, I get the following exception

10:24:22,643 ERROR [GlobalTxEntityMap] Store failed on entity: LoanProcessor
javax.ejb.EJBException: Could insert relations into GroupEJB_users_UserEJB_group
s
Embedded Exception
Violation of unique index in statement [INSERT INTO GroupEJB_users_UserEJB_group
s (GroupEJB, UserEJB) VALUES ('LoanProcessor', 'Fred')]
at org.jboss.ejb.plugins.cmp.jdbc.JDBCInsertRelationsCommand.execute(JDB
CInsertRelationsCommand.java:78)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.insertRelations(JDBCS
toreManager.java:481)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.synchronizeRelationDa
ta(JDBCStoreManager.java:443)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.storeEntity(JDBCStore
Manager.java:424)
at org.jboss.ejb.plugins.CMPPersistenceManager.storeEntity(CMPPersistenc
eManager.java:428)
at org.jboss.ejb.EntityContainer.storeEntity(EntityContainer.java:677)
at org.jboss.ejb.GlobalTxEntityMap.syncEntities(GlobalTxEntityMap.java:8
9)
at org.jboss.ejb.GlobalTxEntityMap$GlobalTxEntityMapCleanup.beforeComple
tion(GlobalTxEntityMap.java:158)
at org.jboss.tm.TxCapsule.doBeforeCompletion(TxCapsule.java:1317)
at org.jboss.tm.TxCapsule.commit(TxCapsule.java:325)
at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:76)

It appears the groupName is a unique index on GroupEJB_users_UserEJB_group, although this is not what I want (since this table supports many-to-many relationships). Any help is appreciated. Thanks.

I don't seem to be albe to attach the jbosscmp-jdbc file, so I have copied it below:

<jbosscmp-jdbc>

java:/OracleDS
<type-mapping>Oracle8</type-mapping>
<create-table>true</create-table>
<remove-table>true</remove-table>
true
300
<select-for-update>false</select-for-update>
<pk-constraint>false</pk-constraint>

<enterprise-beans>

<ejb-name>UserEJB</ejb-name>
<table-name>ORG_USER</table-name>
java:/OracleDS
<type-mapping>Oracle8</type-mapping>
true
300
<select-for-update>false</select-for-update>
<pk-constraint>false</pk-constraint>
<cmp-field>
<field-name>userName</field-name>
<column-name>USERNAME</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<cmp-field>
<field-name>email</field-name>
<column-name>EMAIL</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<cmp-field>
<field-name>qualification</field-name>
<column-name>QUALIFICATION</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>


<ejb-name>GroupEJB</ejb-name>
<table-name>ORG_GROUP</table-name>
java:/OracleDS
<type-mapping>Oracle8</type-mapping>

true
300
<select-for-update>false</select-for-update>
<pk-constraint>false</pk-constraint>
<cmp-field>
<field-name>groupName</field-name>
<column-name>GROUPNAME</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<cmp-field>
<field-name>email</field-name>
<column-name>EMAIL</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<cmp-field>
<field-name>qualification</field-name>
<column-name>QUALIFICATION</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>

</enterprise-beans>


<ejb-relation>
<ejb-relation-name>Membership</ejb-relation-name>
<table-mapping>
<table-name>ORG_MEMBERSHIP</table-name>
<ejb-relationship-role>
<ejb-relationship-role-name>groups-have-users</ejb-relationship-role-name>
<table-key-fields>
<table-key-field>
<field-name>groupName</field-name>
<column-name>GROUPNAME</column-name>
</table-key-field>
</table-key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>users-belong-to-groups</ejb-relationship-role-name>
<table-key-fields>
<table-key-field>
<field-name>userName</field-name>
<column-name>USERNAME</column-name>
</table-key-field>
</table-key-fields>
</ejb-relationship-role>
</table-mapping>
</ejb-relation>

</jbosscmp-jdbc>

[jeffdelong]

I installed RC1 and at first it appeared the problem went away, which made it a little clearer what is happening. I think the error message: Violation of unique index in statement [INSERT INTO GROUPEJB_USERS_USEREJB_GROUP
S (GroupEJB, UserEJB) VALUES ('LoanProcessor', 'Fred')] (notice RC1 the table name is all caps) is because it is trying to do this insert a second time. The real problem I think is that the table is not getting removed and re-created (even though I have set this in the jbosscmp-jdbc) and my client test code is starting up and trying to create the EJBs their relationships. Unfortunately I can't find the relationship table to remove it manually.