Problems with m:n mappings

[Laurie Harper]

This is driving me slightly nuts :-) I ran into a strange error tring to add a collection property modelled by an indirection table in the database. If I do this (much abreviated):

tx = odmg.newTransaction();
tx.begin();
tx.lock(project, WRITE);
project.getList().add(foo);
tx.commit();

where the getList() method initialises the collection property to 'new LinkedList()' if it's not already set, I get an error about there being no transaction in progress. That's the problem I want to solve.


In trying to understand what's going on, I tried to boil it down to a really simple test case. The test case consists of two classes and three tables modelling an n:m relationship. The problem is, it breaks in a different way than the above: when I add items to the collection property and commit, the indirection table isn't updated (so the collection property doesn't get persisted).

The test case involves two simple classes, A and B. A has a collection of B. The database contains tables A and B and an indirection table A_B. I can load an instance of A from the database and OJB uses rows from A_B to populate A's collection property. But if I try and modify the collection property on A, nothing is written to the A_B table. Here's the various files that constitute my test case. Any suggestions what I'm doing wrong may help shed light on real problem, described above.

One oddity is that the query to load the AB instances only works if I use the SQL table/column names in the OQL query! (see lines marked XXX in the test case below). I'm unclear about why that should be...

Schema:

CREATE SEQUENCE A_SEQ;
CREATE SEQUENCE B_SEQ;
CREATE SEQUENCE A_B_SEQ;

CREATE TABLE A
(
ID INTEGER DEFAULT nextval('A_SEQ') NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE B
(
ID INTEGER DEFAULT nextval('B_SEQ') NOT NULL,
PRIMARY KEY (ID)
);


CREATE TABLE A_B
(
A_ID INTEGER DEFAULT nextval('A_B_SEQ') NOT NULL,
B_ID INTEGER DEFAULT nextval('A_B_SEQ') NOT NULL,
PRIMARY KEY (A_ID,B_ID)
);

ALTER TABLE A_B
ADD CONSTRAINT A_FK_1 FOREIGN KEY (A_ID) REFERENCES A (ID)
;
ALTER TABLE A_B
ADD CONSTRAINT A_FK_2 FOREIGN KEY (B_ID) REFERENCES B (ID)
;

Repository_user.xml entries:

<class-descriptor table="A" class="A">
<field-descriptor id="1"
name="id"
column="ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>

<collection-descriptor
name="listOfB"
element-class-ref="B"
orderby="id"
sort="ASC"
auto-retrieve="true"
auto-update="true"
indirection-table="A_B"
>
<fk-pointing-to-this-class column="A_ID"/>
<fk-pointing-to-element-class column="B_ID"/>
</collection-descriptor>
</class-descriptor>

<class-descriptor table="B" class="B">
<field-descriptor id="1"
name="id"
column="ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
</class-descriptor>

<!-- Included so we can query it in the test cases -->
<class-descriptor table="A_B" class="AB">
<field-descriptor id="1"
name="aID"
column="A_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="bID"
column="B_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
</class-descriptor>

Object model:

import java.util.List;
import java.util.LinkedList;

/**
* A has n:m relationship with B
*/
public class A {
private int id;
public int getID() {
return id;
}

private List listOfB;
public List getListOfB() {
return (null == listOfB) ? new LinkedList() : listOfB;
}
}

/* --- */

import java.util.List;

/**
* B has m:n relationship with A
*/
public class B {
private int id;
public int getID() {
return id;
}
}

/* --- */

import java.util.List;
import java.util.LinkedList;

/**
* Expose the A_B table so we can query it in the test cases.
*/
public class AB {
private int aID, bID;
public int getAID() {
return aID;
}
public int getBID() {
return bID;
}
}

Test case:

import java.util.List;

import org.odmg.*;
import org.apache.ojb.odmg.OJB;
import junit.framework.TestCase;
import junit.framework.TestSuite;
import junit.framework.Test;
import junit.textui.TestRunner;

/**
* Creates instances of A and B that reference each other and persists them.
*/
public class RelationshipTest extends TestCase {
private Implementation odmg;
private Database db;

public static void main(String[] args) {
TestRunner.run(RelationshipTest.class);
}
public static Test suite() {
return new TestSuite(RelationshipTest.class);
}
public RelationshipTest(String name) {
super(name);
}

public void setUp() throws ODMGException {
System.out.println("setup");
odmg = OJB.getInstance();
db = odmg.newDatabase();
db.open("repository.xml", Database.OPEN_READ_WRITE);
}

public void testCreate() throws Exception {
A a1;
B b1, b2;

// Create instances of B to be associated with an A
{
Transaction tx1 = odmg.newTransaction();
tx1.begin();
b1 = new B();
b2 = new B();
tx1.lock(b1, Transaction.WRITE);
tx1.lock(b2, Transaction.WRITE);
tx1.commit();
}

// Create an instance of A
{
Transaction tx2 = odmg.newTransaction();
tx2.begin();
a1 = new A();
tx2.lock(a1, Transaction.WRITE);
tx2.commit();
}

// Modify A's collection of Bs
{
Transaction tx3 = odmg.newTransaction();
tx3.begin();
List bs = a1.getListOfB(); // creates new list in a1
bs.add(b1);
bs.add(b2);
tx3.commit();
}

// Check that the A_B table was populated
{
OQLQuery q = odmg.newOQLQuery();
//q.create("select AB from AB where AB.aID = "+a1.getID());//XXX
q.create("select AB from AB where A_B.A_ID = "+a1.getID());//XXX
List abs = (DList) q.execute();
assertNotNull("abs", abs);
assertEquals("abs.size", 2, abs.size());
}
}

public void testLoad() throws Exception {
OQLQuery q = odmg.newOQLQuery();
q.create("select A from A where A.ID = 1");
A a = (A) ((DList) q.execute()).get(0);
List listOfB = a.getListOfB();

assertEquals("a.id", 1, a.getID());
assertNotNull("a.listOfB", listOfB);
assertEquals("a.bs.size", 2, listOfB.size());
assertEquals("a.bs[0].id", 1, ((B) listOfB.get(0)).getID());
assertEquals("a.bs[1].id", 2, ((B) listOfB.get(1)).getID());
}
}


--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>