[castor-user] Removing a Many-to-Many relation

[peter cowan]

I have a pretty standard many-to-many relation mostly working, I can create
, load and update objects fine, and the database tables populate as
expected, however when I try to remove objects I am running into trouble.
Below is a database description, the mapping and some example code.

database tables:

table - stuff:
id - int
name - varchar

table - things:
id - int
name - varchar

table - stuff_and_things:
id - int
stuff_id - int
thing_id - int

<mapping>
  <class name="my.test.app.Stuff" identity="id">
    <map-to table="stuff"/>
    <field name="id" type="int">
      <sql name="id" type="integer" />
    </field>
    <field name="name" type="string">
      <sql name="name" type="varchar" />
    </field>
    <field name="things" type="my.test.app.Thing"
           collection="arraylist">
      <sql name="stuff_id"
           many-table="stuff_and_things"
           many-key="thing_id" />
    </field>
  </class>

  <class name="my.test.app.Thing" identity="id">
    <map-to table="things" />
    <field name="id" type="int">
      <sql name="id" type="integer" />
    </field>
    <field name="name" type="string">
      <sql name="name" type="varchar" />
    </field>
  </class>
</mapping>

Both Stuff and Thing are simple Java Beans and nothing more.

In the below example, the objects are created correctly in the first
transaction, and loaded correctly in the second transaction. When I try to
remove a Thing from the ArrayList data  member of Stuff, the Thing is only
removed from the relation table stuff_and_things, and an orphan is left in
the things table.  This makes enough sense, however, when I try to then
remove the Thing from the database by uncommenting
'database.remove(thing);', I get an exception that it can't be removed due
to foreign key constraints in stuff_and_things. This also makes sense, but I
am left wondering, what am I doing wrong? How do I go about deleting those
orphaned records without doing it in a second transaction?

public static void testStuffAndThings() {
         Database database = jdoManager.getDatabase();
         database.begin();

         Thing thing = new Thing(1,"My Thing");
         Stuff stuff = new Stuff(1,"My Stuff");
         ArrayList things = new ArrayList();
         things.add(thing);
         stuff.setThings(things);

         database.create(thing);
         database.create(stuff);
         database.commit();

         database.begin();
         stuff = (Stuff) database.load(Stuff.class, 1);
         things = stuff.getThings();
         thing = things.remove(0);
         //database.remove(thing);
         database.commit();
         database.close();
}

thanks,
-peter