Removing a Many-to-Many relation

[pacowan]

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

[Werner Guttmann-6]

Peter,

can you please highlight the (e.g. foreign key) constraints in your SQL
schema, just for completeness ?

Regards
Werner

peter cowan wrote:

> 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
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[pa cowan]

werner,

there are two foreign keys in the stuff_and_things table:

"stuff_and_things_stuff_id_fkey" FOREIGN KEY (stuff_id) REFERENCES stuff(id)

"stuff_and_things_thing_id_fkey" FOREIGN KEY (thing_id) REFERENCES things(id)

also, for total completeness, we are using castor 1.3,  our database is postgresql 8.1 and

we are using postgresql-8.1-407.jdbc3.jar

what is the expected behavior in the scenarios i laid out?

-peter

On Wed, Sep 2, 2009 at 1:06 AM, Werner Guttmann <wguttmn@...> wrote:

Peter,

can you please highlight the (e.g. foreign key) constraints in your SQL
schema, just for completeness ?

Regards
Werner

peter cowan wrote:
> 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
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email

[Ralf Joachim-2]

Hi Peter,

it looks like you are using a unidirectional relation between Stuff and Thing. Castor docs say this is not supported. While I use unidirectional mappings in some cases I never did so for many to many relations. Therefore I am not sure if this really is the reason for your problem but chances are quite high.

I suggest you to use a bidirectional mapping for many to many relations (Stuff references Thing and Thing references Stuff). In addition you have to care that both sides of the relation are in sync before you call one of Castor's methods to persist your objects.

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

         Thing thing = new Thing(1,"My Thing");
         database.create(thing);

         Stuff stuff = new Stuff(1,"My Stuff");
         database.create(stuff);

         stuff.setThings(new ArrayList());
         stuff.getThings().add(thing);

         thing.setStuffs(new ArrayList());
         thing.getStuffs().add(stuff);

         database.commit();

         database.begin();

         stuff = (Stuff) database.load(Stuff.class, 1);
         thing = stuff.getThings().remove(0);
         thing.getStuffs().remove(0);

         database.remove(thing);

         database.commit();
         database.close();
}

P.S. At the moment I working at a project that also uses PostgreSQL together with Castor. During this project I recognized some issues in Castor related to the handling of identifier names (table and column names) of PostgreSQL. If you want to use upper case identifier names you will need to use SVN head where I already fixed all these issues. Lower case identifier names will work with 1.3.

Regards
Ralf


peter cowan schrieb:

werner,

there are two foreign keys in the stuff_and_things table:

"stuff_and_things_stuff_id_fkey" FOREIGN KEY (stuff_id) REFERENCES stuff(id)

"stuff_and_things_thing_id_fkey" FOREIGN KEY (thing_id) REFERENCES things(id)

also, for total completeness, we are using castor 1.3,  our database is postgresql 8.1 and

we are using postgresql-8.1-407.jdbc3.jar

what is the expected behavior in the scenarios i laid out?

-peter

On Wed, Sep 2, 2009 at 1:06 AM, Werner Guttmann <wguttmn@...> wrote:

Peter,

can you please highlight the (e.g. foreign key) constraints in your SQL
schema, just for completeness ?

Regards
Werner

peter cowan wrote:
> 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
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email

--------------------------------------------------------------------- To unsubscribe from this list, please visit: http://xircles.codehaus.org/manage_email

[pa cowan]

Ralf,

thanks, the bi-directional mapping did the trick. also, it is working, and i am not calling this line:

         thing.setStuffs(new ArrayList());
         thing.getStuffs().add(stuff);

i'm gonna add that line to be safe, but it looks like it is not necessary in some situations.

thanks for your tip, i had noticed that issue with uppercase identifier names, but didn't realize it was castor specific. we are fine with all lower case.

2009/9/2 Ralf Joachim <ralf.joachim@...>

Hi Peter,

it looks like you are using a unidirectional relation between Stuff and Thing. Castor docs say this is not supported. While I use unidirectional mappings in some cases I never did so for many to many relations. Therefore I am not sure if this really is the reason for your problem but chances are quite high.

I suggest you to use a bidirectional mapping for many to many relations (Stuff references Thing and Thing references Stuff). In addition you have to care that both sides of the relation are in sync before you call one of Castor's methods to persist your objects.

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

         Thing thing = new Thing(1,"My Thing");

         database.create(thing);

         Stuff stuff = new Stuff(1,"My Stuff");

         database.create(stuff);

         stuff.setThings(new ArrayList());
         stuff.getThings().add(thing);

         thing.setStuffs(new ArrayList());
         thing.getStuffs().add(stuff);

         database.commit();

         database.begin();

         stuff = (Stuff) database.load(Stuff.class, 1);

         thing = stuff.getThings().remove(0);
         thing.getStuffs().remove(0);

         database.remove(thing);

         database.commit();
         database.close();
}

P.S. At the moment I working at a project that also uses PostgreSQL together with Castor. During this project I recognized some issues in Castor related to the handling of identifier names (table and column names) of PostgreSQL. If you want to use upper case identifier names you will need to use SVN head where I already fixed all these issues. Lower case identifier names will work with 1.3.

Regards
Ralf


peter cowan schrieb:

werner,

there are two foreign keys in the stuff_and_things table:

"stuff_and_things_stuff_id_fkey" FOREIGN KEY (stuff_id) REFERENCES stuff(id)

"stuff_and_things_thing_id_fkey" FOREIGN KEY (thing_id) REFERENCES things(id)

also, for total completeness, we are using castor 1.3,  our database is postgresql 8.1 and

we are using postgresql-8.1-407.jdbc3.jar

what is the expected behavior in the scenarios i laid out?

-peter

On Wed, Sep 2, 2009 at 1:06 AM, Werner Guttmann <wguttmn@...> wrote:

Peter,

can you please highlight the (e.g. foreign key) constraints in your SQL
schema, just for completeness ?

Regards
Werner

peter cowan wrote:
> 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
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email

-- 

Syscon Ingenieurbüro für Meß- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstraße 11
72127 Kusterdingen
Germany

Tel.   [phone]
Mobil: [phone]
Fax    [phone]

Internet: www.syscon.eu
E-Mail: ralf.joachim@...

Sitz der Gesellschaft: D-72127 Kusterdingen
Registereintrag: Amtsgericht Stuttgart, HRB 382295
Geschäftsleitung: Jens Joachim, Ralf Joachim

--------------------------------------------------------------------- To unsubscribe from this list, please visit: http://xircles.codehaus.org/manage_email