Eclipse Community Forums: EMF "Technology" (Ecore Tools, EMFatic, etc) » [Teneo] Child objects in DB have multiple references to parent.

Home » Modeling » EMF "Technology" (Ecore Tools, EMFatic, etc) » [Teneo] Child objects in DB have multiple references to parent.

Show: Today's Messages :: Show Polls :: Message Navigator

[Teneo] Child objects in DB have multiple references to parent. [message #86667]

Mon, 18 June 2007 11:40

Duncan Alexander

Messages: 90
Registered: July 2009

Member

Hi,
I noticed that Teneo generates multiple columns for references between a child and parent in a
one-to-many relationship. I can understand what each of them represents but wonder why they both
must exist independently and I think it is causing my orphan objects to not be deleted.

For example, I have an object called ItemScheme which contains Items in a list.

The mapping looks like:

<subclass entity-name="base.ItemScheme" abstract="true" lazy="false" extends="base.Maintainable"
discriminator-value="base.ItemScheme">
<meta attribute="eclassName">ItemScheme</meta>
<meta attribute="epackage">http://www.sdmx.org/resources/infomodel/1.0/base.xmi</meta>
<list name="Item" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="`itemscheme_item_urn`" not-null="false" unique="false"/>
</key>
<list-index column="`itemscheme_item_idx`"/>
<one-to-many entity-name="base.Item"/>
</list>
</subclass>

And when I look at the generated columns in the DB for Items I see the itemscheme_item_urn column
which points to the parent but also an eContainer column which references the same parent.

The problem I have is that if I update my ItemScheme from a dettached object with a different set of
Items then I can correctly see my new items in the database with refernce to the ItemScheme parent.

But I can see that the old Items which are no longer referenced still exist in the database with
their itemscheme_item_urn field set to null, but the eContainer field still referencing the
ItemScheme. I would expect the Item to be removed completely under these circumstances.

duncan

Report message to a moderator

Re: [Teneo] Child objects in DB have multiple references to parent. [message #86743 is a reply to message #86667]

Mon, 18 June 2007 21:03

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Hi Duncan,
For containment relations it is required that an extra column is used to store the econtainer
relation. This extra column is required to determine the container when the item is read from the db
indepedently from the parent. Although in your example the parent could also be read from the
itemscheme_item_urn this information is not necessarily present at hibernate (object) level of the
item (only the parent knows who its children are). Also it is possible to model the containment
association in a separate table of which the item does not 'officially' know that it is there.
If you don't read the items independently from the parent or are not interested in the containment
columns then there is an option to disable them.

The other thing you note, that the item is not removed is not how it should be, the relation has
delete-orphan which signals hibernate to remove the de-referenced items. Hibernate sees that the
item is not part anymore of the association as it nullifies the foreign-key to the parent. Can you
show me the update code?

gr. Martin

Duncan ALexander wrote:
> Hi,
> I noticed that Teneo generates multiple columns for references
> between a child and parent in a one-to-many relationship. I can
> understand what each of them represents but wonder why they both must
> exist independently and I think it is causing my orphan objects to not
> be deleted.
>
> For example, I have an object called ItemScheme which contains Items in
> a list.
>
> The mapping looks like:
>
> <subclass entity-name="base.ItemScheme" abstract="true" lazy="false"
> extends="base.Maintainable" discriminator-value="base.ItemScheme">
> <meta attribute="eclassName">ItemScheme</meta>
> <meta
> attribute="epackage">http://www.sdmx.org/resources/infomodel/1.0/base.xmi</meta>
>
> <list name="Item" lazy="true" cascade="all,delete-orphan">
> <key update="true">
> <column name="`itemscheme_item_urn`" not-null="false"
> unique="false"/>
> </key>
> <list-index column="`itemscheme_item_idx`"/>
> <one-to-many entity-name="base.Item"/>
> </list>
> </subclass>
>
> And when I look at the generated columns in the DB for Items I see the
> itemscheme_item_urn column which points to the parent but also an
> eContainer column which references the same parent.
>
> The problem I have is that if I update my ItemScheme from a dettached
> object with a different set of Items then I can correctly see my new
> items in the database with refernce to the ItemScheme parent.
>
> But I can see that the old Items which are no longer referenced still
> exist in the database with their itemscheme_item_urn field set to null,
> but the eContainer field still referencing the ItemScheme. I would
> expect the Item to be removed completely under these circumstances.
>
> duncan

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org

Report message to a moderator

Re: [Teneo] Child objects in DB have multiple references to parent. [message #86765 is a reply to message #86743]

Tue, 19 June 2007 09:47

Duncan Alexander

Messages: 90
Registered: July 2009

Member

Hi Martin,
here is an example.

- I have seeded the database with a sublass of ItemScheme and Item called ConceptScheme and Concept
as follows, unique ids are shown in brackets :

conceptSchemeOne (urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SDM X:conceptScheme1)
- concept1 (urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conc eptScheme1.concept1)
- concept2 (urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conc eptScheme1.concept3)

My database is dropped and recreated before each test to ensure a known start state. I then manually
create some 'detached' objects as follows (I am co-opting the unused URI field on the objects to add
some updated info which I can test against):

-----------------------------------------------------------
// Load the initial database values including those shown above then clear and close the session.
loadBaseRegistry();

// Create a detached version of the concept scheme
ConceptScheme conceptSchemeOne = ConceptSchemeFactory.eINSTANCE.createConceptScheme();

conceptSchemeOne.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SDMX :conceptScheme1 ");
conceptSchemeOne.setUri("UPDATED CONCEPT SCHEME");

// Create a detached version of concept one
Concept conceptOne = ConceptSchemeFactory.eINSTANCE.createConcept();

conceptOne.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conce ptScheme1.concept1 ");
conceptOne.setUri("UPDATED CONCEPT ONE");

// Create a new concept
Concept conceptThree = ConceptSchemeFactory.eINSTANCE.createConcept();

conceptThree.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conce ptScheme1.concept3 ");
conceptThree.setUri("NEW CONCEPT THREE");

// Add the concepts to the concept scheme
conceptSchemeOne.getItem().add(conceptOne);
conceptSchemeOne.getItem().add(conceptThree);

// open a session
Session session = teneoSession.getSession();
Transaction transaction = session.getTransaction();
transaction.begin();

session.update(conceptSchemeOne);

session.flush();
transaction.commit();
session.close();
----------------------------------------------------------

If I then load conceptSchemeOne through Hibernate and check the Items it contains, this is correctly
shown as conceptOne and coneptThree but if I look in the DB I can still see conceptTwo exists.

The conceptTwo row no longer has a reference to its parent through the 'itemscheme_item_urn' column,
this is now set to null, but its eContainer column does still reference conceptSchemeOne.

I have traced the SQL which hibernate is producing for the above update, and it looks like this :

------------------------------------------------------------ -

20:11:47,531 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'select concept_.`urn`,
concept_.`id` as id3_0_, concept_.`uri` as uri4_0_, concept_.`internationalstring_name_e_id` as
internat5_0_, concept_.`internationalstring_description_e_id` as internat6_0_,
concept_.econtainer_class as econtainer64_0_, concept_.e_container as e65_0_,
concept_.e_container_featureid as e66_0_, concept_.`version` as version7_0_, concept_.`startdate` as
startdate8_0_, concept_.`enddate` as enddate9_0_, concept_.`type_type_e_id` as type19_0_,
concept_.`representation_representation_e_id` as represe20_0_ from `base_identifiable` concept_
where concept_.`urn`=?' with bind parameters:
{1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}

20:11:47,640 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'select concept_.`urn`,
concept_.`id` as id3_0_, concept_.`uri` as uri4_0_, concept_.`internationalstring_name_e_id` as
internat5_0_, concept_.`internationalstring_description_e_id` as internat6_0_,
concept_.econtainer_class as econtainer64_0_, concept_.e_container as e65_0_,
concept_.e_container_featureid as e66_0_, concept_.`version` as version7_0_, concept_.`startdate` as
startdate8_0_, concept_.`enddate` as enddate9_0_, concept_.`type_type_e_id` as type19_0_,
concept_.`representation_representation_e_id` as represe20_0_ from `base_identifiable` concept_
where concept_.`urn`=?' with bind parameters:
{1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept3}

20:11:53,625 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'insert into
`base_identifiable` (`id`, `uri`, `internationalstring_name_e_id`,
`internationalstring_description_e_id`, econtainer_class, e_container, e_container_featureid,
`version`, `startdate`, `enddate`, `type_type_e_id`, `representation_representation_e_id`, `dtype`,
`urn`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'cs.Concept', ?)' with bind parameters:
{1=concept3, 2=NEW_CONCEPT, 3=663, 4=664, 5=cs.ConceptScheme,
6=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SD MX:conceptScheme1, 7=-12, 8=null,
9=null, 10=null, 11=null, 12=null,
13=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept3}

20:11:53,640 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'update
`base_identifiable` set `id`=?, `uri`=?, `internationalstring_name_e_id`=?,
`internationalstring_description_e_id`=?, econtainer_class=?, e_container=?,
e_container_featureid=?, `version`=?, `startdate`=?, `enddate`=? where `urn`=?' with bind
parameters: {1=SDMX, 2=UPDATED_AGENCY, 3=657, 4=658, 5=base.SDMXContainer, 6=SDMXContainer, 7=-2,
8=null, 9=null, 10=null, 11=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX}

20:11:53,656 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'update
`base_identifiable` set `id`=?, `uri`=?, `internationalstring_name_e_id`=?,
`internationalstring_description_e_id`=?, econtainer_class=?, e_container=?,
e_container_featureid=?, `version`=?, `startdate`=?, `enddate`=?, `agency_agency_urn`=? where
`urn`=?' with bind parameters: {1=conceptScheme1, 2=SUBMITTED CONCEPT SCHEME, 3=659, 4=660,
5=base.SDMXContainer, 6=SDMXContainer, 7=-2, 8=null, 9=null, 10=null,
11=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX,
12=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1}

20:11:53,671 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'update
`base_identifiable` set `id`=?, `uri`=?, `internationalstring_name_e_id`=?,
`internationalstring_description_e_id`=?, econtainer_class=?, e_container=?,
e_container_featureid=?, `version`=?, `startdate`=?, `enddate`=?, `type_type_e_id`=?,
`representation_representation_e_id`=? where `urn`=?' with bind parameters: {1=concept1,
2=UPDATED_CONCEPT, 3=661, 4=662, 5=cs.ConceptScheme,
6=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SD MX:conceptScheme1, 7=-12, 8=null,
9=null, 10=null, 11=null, 12=null,
13=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}

20:11:53,734 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'update
`base_identifiable` set `item_item_urn`=null, `item_item_idx`=null where `item_item_urn`=?' with
bind parameters: {1=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX}

***###*** 20:11:53,765 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'update
`base_identifiable` set `itemscheme_item_urn`=null, `itemscheme_item_idx`=null where
`itemscheme_item_urn`=?' with bind parameters:
{1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1} ***###***

20:11:53,812 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'update
`base_identifiable` set `item_item_urn`=null, `item_item_idx`=null where `item_item_urn`=?' with
bind parameters: {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}

20:11:53,906 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'update
`base_identifiable` set `itemscheme_item_urn`=?, `itemscheme_item_idx`=? where `urn`=?' with bind
parameters: {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1, 2=0,
3=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:con ceptScheme1.concept1}

20:11:53,906 DEBUG LogPreparedStatement:83 - executing PreparedStatement: 'update
`base_identifiable` set `itemscheme_item_urn`=?, `itemscheme_item_idx`=? where `urn`=?' with bind
parameters: {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1, 2=1,
3=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:con ceptScheme1.concept3}

------------------------------------------------------------ ------------------

It seems that Hibernate is setting the parent of all Items to be null one by one then setting the
parent of the items from the update to point to the correct concept scheme. See ***###***

I have tried to perform this equivalent update by loading the concept scheme from hibernate, closing
the session, removing the concept2, adding concept3 then reattaching and updating the conceptScheme
and this seems to work. i.e. It seems to remember that concept2 existed and therefore needs to
deleted. But this is not the way it will work in the real application, detached objects will always
be loaded from remote clients in a way similiar to above.

Duncan

Martin Taal wrote:
> Hi Duncan,
> For containment relations it is required that an extra column is used to
> store the econtainer relation. This extra column is required to
> determine the container when the item is read from the db indepedently
> from the parent. Although in your example the parent could also be read
> from the itemscheme_item_urn this information is not necessarily present
> at hibernate (object) level of the item (only the parent knows who its
> children are). Also it is possible to model the containment association
> in a separate table of which the item does not 'officially' know that it
> is there.
> If you don't read the items independently from the parent or are not
> interested in the containment columns then there is an option to disable
> them.
>
> The other thing you note, that the item is not removed is not how it
> should be, the relation has delete-orphan which signals hibernate to
> remove the de-referenced items. Hibernate sees that the item is not part
> anymore of the association as it nullifies the foreign-key to the
> parent. Can you show me the update code?
>
> gr. Martin
>
> Duncan ALexander wrote:
>> Hi,
>> I noticed that Teneo generates multiple columns for references
>> between a child and parent in a one-to-many relationship. I can
>> understand what each of them represents but wonder why they both must
>> exist independently and I think it is causing my orphan objects to not
>> be deleted.
>>
>> For example, I have an object called ItemScheme which contains Items
>> in a list.
>>
>> The mapping looks like:
>>
>> <subclass entity-name="base.ItemScheme" abstract="true" lazy="false"
>> extends="base.Maintainable" discriminator-value="base.ItemScheme">
>> <meta attribute="eclassName">ItemScheme</meta>
>> <meta
>> attribute="epackage">http://www.sdmx.org/resources/infomodel/1.0/base.xmi</meta>
>>
>> <list name="Item" lazy="true" cascade="all,delete-orphan">
>> <key update="true">
>> <column name="`itemscheme_item_urn`" not-null="false"
>> unique="false"/>
>> </key>
>> <list-index column="`itemscheme_item_idx`"/>
>> <one-to-many entity-name="base.Item"/>
>> </list> </subclass>
>>
>> And when I look at the generated columns in the DB for Items I see the
>> itemscheme_item_urn column which points to the parent but also an
>> eContainer column which references the same parent.
>>
>> The problem I have is that if I update my ItemScheme from a dettached
>> object with a different set of Items then I can correctly see my new
>> items in the database with refernce to the ItemScheme parent.
>>
>> But I can see that the old Items which are no longer referenced still
>> exist in the database with their itemscheme_item_urn field set to
>> null, but the eContainer field still referencing the ItemScheme. I
>> would expect the Item to be removed completely under these circumstances.
>>
>> duncan
>
>

Report message to a moderator

Re: [Teneo] Child objects in DB have multiple references to parent. [message #86774 is a reply to message #86765]

Tue, 19 June 2007 21:53

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Hi Duncan,
I tested this same case and with session.update I got the same results as you (an orphanned child),
I then did session.merge and this worked (the orphaned child was removed).

See this part of the hibernate manual for some more info on the difference between save, update and
merge: 10.7. Automatic state detection

gr. Martin

Duncan ALexander wrote:
> Hi Martin,
> here is an example.
>
> - I have seeded the database with a sublass of ItemScheme and Item
> called ConceptScheme and Concept as follows, unique ids are shown in
> brackets :
>
> conceptSchemeOne
> (urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SDM X:conceptScheme1)
>
> - concept1
> (urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conc eptScheme1.concept1)
>
> - concept2
> (urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conc eptScheme1.concept3)
>
>
> My database is dropped and recreated before each test to ensure a known
> start state. I then manually create some 'detached' objects as follows
> (I am co-opting the unused URI field on the objects to add some updated
> info which I can test against):
>
> -----------------------------------------------------------
> // Load the initial database values including those shown above then
> clear and close the session.
> loadBaseRegistry();
>
> // Create a detached version of the concept scheme
> ConceptScheme conceptSchemeOne =
> ConceptSchemeFactory.eINSTANCE.createConceptScheme();
>
> conceptSchemeOne.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SDMX :conceptScheme1 ");
>
> conceptSchemeOne.setUri("UPDATED CONCEPT SCHEME");
>
> // Create a detached version of concept one
> Concept conceptOne =
> ConceptSchemeFactory.eINSTANCE.createConcept();
>
> conceptOne.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conce ptScheme1.concept1 ");
>
> conceptOne.setUri("UPDATED CONCEPT ONE");
>
> // Create a new concept
> Concept conceptThree =
> ConceptSchemeFactory.eINSTANCE.createConcept();
>
> conceptThree.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conce ptScheme1.concept3 ");
>
> conceptThree.setUri("NEW CONCEPT THREE");
>
> // Add the concepts to the concept scheme
> conceptSchemeOne.getItem().add(conceptOne);
> conceptSchemeOne.getItem().add(conceptThree);
>
> // open a session
> Session session = teneoSession.getSession();
> Transaction transaction = session.getTransaction();
> transaction.begin();
>
> session.update(conceptSchemeOne);
>
> session.flush();
> transaction.commit();
> session.close();
> ----------------------------------------------------------
>
> If I then load conceptSchemeOne through Hibernate and check the Items it
> contains, this is correctly shown as conceptOne and coneptThree but if I
> look in the DB I can still see conceptTwo exists.
>
> The conceptTwo row no longer has a reference to its parent through the
> 'itemscheme_item_urn' column, this is now set to null, but its
> eContainer column does still reference conceptSchemeOne.
>
> I have traced the SQL which hibernate is producing for the above update,
> and it looks like this :
>
> ------------------------------------------------------------ -
>
> 20:11:47,531 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'select concept_.`urn`, concept_.`id` as id3_0_,
> concept_.`uri` as uri4_0_, concept_.`internationalstring_name_e_id` as
> internat5_0_, concept_.`internationalstring_description_e_id` as
> internat6_0_, concept_.econtainer_class as econtainer64_0_,
> concept_.e_container as e65_0_, concept_.e_container_featureid as
> e66_0_, concept_.`version` as version7_0_, concept_.`startdate` as
> startdate8_0_, concept_.`enddate` as enddate9_0_,
> concept_.`type_type_e_id` as type19_0_,
> concept_.`representation_representation_e_id` as represe20_0_ from
> `base_identifiable` concept_ where concept_.`urn`=?' with bind
> parameters:
> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}
>
>
> 20:11:47,640 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'select concept_.`urn`, concept_.`id` as id3_0_,
> concept_.`uri` as uri4_0_, concept_.`internationalstring_name_e_id` as
> internat5_0_, concept_.`internationalstring_description_e_id` as
> internat6_0_, concept_.econtainer_class as econtainer64_0_,
> concept_.e_container as e65_0_, concept_.e_container_featureid as
> e66_0_, concept_.`version` as version7_0_, concept_.`startdate` as
> startdate8_0_, concept_.`enddate` as enddate9_0_,
> concept_.`type_type_e_id` as type19_0_,
> concept_.`representation_representation_e_id` as represe20_0_ from
> `base_identifiable` concept_ where concept_.`urn`=?' with bind
> parameters:
> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept3}
>
>
> 20:11:53,625 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'insert into `base_identifiable` (`id`, `uri`,
> `internationalstring_name_e_id`, `internationalstring_description_e_id`,
> econtainer_class, e_container, e_container_featureid, `version`,
> `startdate`, `enddate`, `type_type_e_id`,
> `representation_representation_e_id`, `dtype`, `urn`) values (?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, 'cs.Concept', ?)' with bind parameters:
> {1=concept3, 2=NEW_CONCEPT, 3=663, 4=664, 5=cs.ConceptScheme,
> 6=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SD MX:conceptScheme1,
> 7=-12, 8=null, 9=null, 10=null, 11=null, 12=null,
> 13=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept3}
>
>
> 20:11:53,640 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'update `base_identifiable` set `id`=?, `uri`=?,
> `internationalstring_name_e_id`=?,
> `internationalstring_description_e_id`=?, econtainer_class=?,
> e_container=?, e_container_featureid=?, `version`=?, `startdate`=?,
> `enddate`=? where `urn`=?' with bind parameters: {1=SDMX,
> 2=UPDATED_AGENCY, 3=657, 4=658, 5=base.SDMXContainer, 6=SDMXContainer,
> 7=-2, 8=null, 9=null, 10=null,
> 11=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX}
>
> 20:11:53,656 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'update `base_identifiable` set `id`=?, `uri`=?,
> `internationalstring_name_e_id`=?,
> `internationalstring_description_e_id`=?, econtainer_class=?,
> e_container=?, e_container_featureid=?, `version`=?, `startdate`=?,
> `enddate`=?, `agency_agency_urn`=? where `urn`=?' with bind parameters:
> {1=conceptScheme1, 2=SUBMITTED CONCEPT SCHEME, 3=659, 4=660,
> 5=base.SDMXContainer, 6=SDMXContainer, 7=-2, 8=null, 9=null, 10=null,
> 11=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX,
> 12=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1}
>
>
> 20:11:53,671 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'update `base_identifiable` set `id`=?, `uri`=?,
> `internationalstring_name_e_id`=?,
> `internationalstring_description_e_id`=?, econtainer_class=?,
> e_container=?, e_container_featureid=?, `version`=?, `startdate`=?,
> `enddate`=?, `type_type_e_id`=?, `representation_representation_e_id`=?
> where `urn`=?' with bind parameters: {1=concept1, 2=UPDATED_CONCEPT,
> 3=661, 4=662, 5=cs.ConceptScheme,
> 6=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SD MX:conceptScheme1,
> 7=-12, 8=null, 9=null, 10=null, 11=null, 12=null,
> 13=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}
>
>
> 20:11:53,734 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'update `base_identifiable` set `item_item_urn`=null,
> `item_item_idx`=null where `item_item_urn`=?' with bind parameters:
> {1=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX}
>
> ***###*** 20:11:53,765 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'update `base_identifiable` set
> `itemscheme_item_urn`=null, `itemscheme_item_idx`=null where
> `itemscheme_item_urn`=?' with bind parameters:
> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1}
> ***###***
>
> 20:11:53,812 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'update `base_identifiable` set `item_item_urn`=null,
> `item_item_idx`=null where `item_item_urn`=?' with bind parameters:
> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}
>
>
> 20:11:53,906 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'update `base_identifiable` set
> `itemscheme_item_urn`=?, `itemscheme_item_idx`=? where `urn`=?' with
> bind parameters:
> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1,
> 2=0,
> 3=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:con ceptScheme1.concept1}
>
>
> 20:11:53,906 DEBUG LogPreparedStatement:83 - executing
> PreparedStatement: 'update `base_identifiable` set
> `itemscheme_item_urn`=?, `itemscheme_item_idx`=? where `urn`=?' with
> bind parameters:
> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1,
> 2=1,
> 3=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:con ceptScheme1.concept3}
>
>
> ------------------------------------------------------------ ------------------
>
>
> It seems that Hibernate is setting the parent of all Items to be null
> one by one then setting the parent of the items from the update to point
> to the correct concept scheme. See ***###***
>
> I have tried to perform this equivalent update by loading the concept
> scheme from hibernate, closing the session, removing the concept2,
> adding concept3 then reattaching and updating the conceptScheme and this
> seems to work. i.e. It seems to remember that concept2 existed and
> therefore needs to deleted. But this is not the way it will work in the
> real application, detached objects will always be loaded from remote
> clients in a way similiar to above.
>
>
> Duncan
>
>
>
> Martin Taal wrote:
>> Hi Duncan,
>> For containment relations it is required that an extra column is used
>> to store the econtainer relation. This extra column is required to
>> determine the container when the item is read from the db indepedently
>> from the parent. Although in your example the parent could also be
>> read from the itemscheme_item_urn this information is not necessarily
>> present at hibernate (object) level of the item (only the parent knows
>> who its children are). Also it is possible to model the containment
>> association in a separate table of which the item does not
>> 'officially' know that it is there.
>> If you don't read the items independently from the parent or are not
>> interested in the containment columns then there is an option to
>> disable them.
>>
>> The other thing you note, that the item is not removed is not how it
>> should be, the relation has delete-orphan which signals hibernate to
>> remove the de-referenced items. Hibernate sees that the item is not
>> part anymore of the association as it nullifies the foreign-key to the
>> parent. Can you show me the update code?
>>
>> gr. Martin
>>
>> Duncan ALexander wrote:
>>> Hi,
>>> I noticed that Teneo generates multiple columns for references
>>> between a child and parent in a one-to-many relationship. I can
>>> understand what each of them represents but wonder why they both must
>>> exist independently and I think it is causing my orphan objects to
>>> not be deleted.
>>>
>>> For example, I have an object called ItemScheme which contains Items
>>> in a list.
>>>
>>> The mapping looks like:
>>>
>>> <subclass entity-name="base.ItemScheme" abstract="true" lazy="false"
>>> extends="base.Maintainable" discriminator-value="base.ItemScheme">
>>> <meta attribute="eclassName">ItemScheme</meta>
>>> <meta
>>> attribute="epackage">http://www.sdmx.org/resources/infomodel/1.0/base.xmi</meta>
>>>
>>> <list name="Item" lazy="true" cascade="all,delete-orphan">
>>> <key update="true">
>>> <column name="`itemscheme_item_urn`" not-null="false"
>>> unique="false"/>
>>> </key>
>>> <list-index column="`itemscheme_item_idx`"/>
>>> <one-to-many entity-name="base.Item"/>
>>> </list> </subclass>
>>>
>>> And when I look at the generated columns in the DB for Items I see
>>> the itemscheme_item_urn column which points to the parent but also an
>>> eContainer column which references the same parent.
>>>
>>> The problem I have is that if I update my ItemScheme from a dettached
>>> object with a different set of Items then I can correctly see my new
>>> items in the database with refernce to the ItemScheme parent.
>>>
>>> But I can see that the old Items which are no longer referenced still
>>> exist in the database with their itemscheme_item_urn field set to
>>> null, but the eContainer field still referencing the ItemScheme. I
>>> would expect the Item to be removed completely under these
>>> circumstances.
>>>
>>> duncan
>>
>>

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org

Report message to a moderator

Re: [Teneo] Child objects in DB have multiple references to parent. [message #86838 is a reply to message #86774]

Wed, 20 June 2007 10:10

Duncan Alexander

Messages: 90
Registered: July 2009

Member

Hi Martin,
aha...yes, that works. I hadnt tried this again since solving issues with the version flag when I
had discounted its use. That seems to delete the orphan. Excellent, thanks for that.

It has thrown up a slight issue with our object design whereby trying to merge detached objects
which have related objects in tables with synthetic IDs are making the related objects appear as
transient and the merge baulks at this. BUT, this is solvable by moving to composite IDs for these
tables, I think. So, another step (leap) forward!

Many thanks

Duncan

Martin Taal wrote:
> Hi Duncan,
> I tested this same case and with session.update I got the same results
> as you (an orphanned child), I then did session.merge and this worked
> (the orphaned child was removed).
>
> See this part of the hibernate manual for some more info on the
> difference between save, update and merge: 10.7. Automatic state detection
>
> gr. Martin
>
> Duncan ALexander wrote:
>> Hi Martin,
>> here is an example.
>>
>> - I have seeded the database with a sublass of ItemScheme and Item
>> called ConceptScheme and Concept as follows, unique ids are shown in
>> brackets :
>>
>> conceptSchemeOne
>> (urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SDM X:conceptScheme1)
>>
>> - concept1
>> (urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conc eptScheme1.concept1)
>>
>> - concept2
>> (urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conc eptScheme1.concept3)
>>
>>
>> My database is dropped and recreated before each test to ensure a
>> known start state. I then manually create some 'detached' objects as
>> follows (I am co-opting the unused URI field on the objects to add
>> some updated info which I can test against):
>>
>> -----------------------------------------------------------
>> // Load the initial database values including those shown above
>> then clear and close the session.
>> loadBaseRegistry();
>>
>> // Create a detached version of the concept scheme
>> ConceptScheme conceptSchemeOne =
>> ConceptSchemeFactory.eINSTANCE.createConceptScheme();
>>
>> conceptSchemeOne.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SDMX :conceptScheme1 ");
>>
>> conceptSchemeOne.setUri("UPDATED CONCEPT SCHEME");
>>
>> // Create a detached version of concept one
>> Concept conceptOne =
>> ConceptSchemeFactory.eINSTANCE.createConcept();
>>
>> conceptOne.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conce ptScheme1.concept1 ");
>>
>> conceptOne.setUri("UPDATED CONCEPT ONE");
>>
>> // Create a new concept
>> Concept conceptThree =
>> ConceptSchemeFactory.eINSTANCE.createConcept();
>>
>> conceptThree.setUrn(" urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:conce ptScheme1.concept3 ");
>>
>> conceptThree.setUri("NEW CONCEPT THREE");
>>
>> // Add the concepts to the concept scheme
>> conceptSchemeOne.getItem().add(conceptOne);
>> conceptSchemeOne.getItem().add(conceptThree);
>>
>> // open a session
>> Session session = teneoSession.getSession();
>> Transaction transaction = session.getTransaction();
>> transaction.begin();
>>
>> session.update(conceptSchemeOne);
>>
>> session.flush();
>> transaction.commit();
>> session.close();
>> ----------------------------------------------------------
>>
>> If I then load conceptSchemeOne through Hibernate and check the Items
>> it contains, this is correctly shown as conceptOne and coneptThree but
>> if I look in the DB I can still see conceptTwo exists.
>>
>> The conceptTwo row no longer has a reference to its parent through the
>> 'itemscheme_item_urn' column, this is now set to null, but its
>> eContainer column does still reference conceptSchemeOne.
>>
>> I have traced the SQL which hibernate is producing for the above
>> update, and it looks like this :
>>
>> ------------------------------------------------------------ -
>>
>> 20:11:47,531 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'select concept_.`urn`, concept_.`id` as id3_0_,
>> concept_.`uri` as uri4_0_, concept_.`internationalstring_name_e_id` as
>> internat5_0_, concept_.`internationalstring_description_e_id` as
>> internat6_0_, concept_.econtainer_class as econtainer64_0_,
>> concept_.e_container as e65_0_, concept_.e_container_featureid as
>> e66_0_, concept_.`version` as version7_0_, concept_.`startdate` as
>> startdate8_0_, concept_.`enddate` as enddate9_0_,
>> concept_.`type_type_e_id` as type19_0_,
>> concept_.`representation_representation_e_id` as represe20_0_ from
>> `base_identifiable` concept_ where concept_.`urn`=?' with bind
>> parameters:
>> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}
>>
>>
>> 20:11:47,640 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'select concept_.`urn`, concept_.`id` as id3_0_,
>> concept_.`uri` as uri4_0_, concept_.`internationalstring_name_e_id` as
>> internat5_0_, concept_.`internationalstring_description_e_id` as
>> internat6_0_, concept_.econtainer_class as econtainer64_0_,
>> concept_.e_container as e65_0_, concept_.e_container_featureid as
>> e66_0_, concept_.`version` as version7_0_, concept_.`startdate` as
>> startdate8_0_, concept_.`enddate` as enddate9_0_,
>> concept_.`type_type_e_id` as type19_0_,
>> concept_.`representation_representation_e_id` as represe20_0_ from
>> `base_identifiable` concept_ where concept_.`urn`=?' with bind
>> parameters:
>> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept3}
>>
>>
>> 20:11:53,625 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'insert into `base_identifiable` (`id`, `uri`,
>> `internationalstring_name_e_id`,
>> `internationalstring_description_e_id`, econtainer_class, e_container,
>> e_container_featureid, `version`, `startdate`, `enddate`,
>> `type_type_e_id`, `representation_representation_e_id`, `dtype`,
>> `urn`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'cs.Concept', ?)'
>> with bind parameters: {1=concept3, 2=NEW_CONCEPT, 3=663, 4=664,
>> 5=cs.ConceptScheme,
>> 6=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SD MX:conceptScheme1,
>> 7=-12, 8=null, 9=null, 10=null, 11=null, 12=null,
>> 13=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept3}
>>
>>
>> 20:11:53,640 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'update `base_identifiable` set `id`=?, `uri`=?,
>> `internationalstring_name_e_id`=?,
>> `internationalstring_description_e_id`=?, econtainer_class=?,
>> e_container=?, e_container_featureid=?, `version`=?, `startdate`=?,
>> `enddate`=? where `urn`=?' with bind parameters: {1=SDMX,
>> 2=UPDATED_AGENCY, 3=657, 4=658, 5=base.SDMXContainer, 6=SDMXContainer,
>> 7=-2, 8=null, 9=null, 10=null,
>> 11=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX}
>>
>> 20:11:53,656 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'update `base_identifiable` set `id`=?, `uri`=?,
>> `internationalstring_name_e_id`=?,
>> `internationalstring_description_e_id`=?, econtainer_class=?,
>> e_container=?, e_container_featureid=?, `version`=?, `startdate`=?,
>> `enddate`=?, `agency_agency_urn`=? where `urn`=?' with bind
>> parameters: {1=conceptScheme1, 2=SUBMITTED CONCEPT SCHEME, 3=659,
>> 4=660, 5=base.SDMXContainer, 6=SDMXContainer, 7=-2, 8=null, 9=null,
>> 10=null, 11=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX,
>> 12=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1}
>>
>>
>> 20:11:53,671 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'update `base_identifiable` set `id`=?, `uri`=?,
>> `internationalstring_name_e_id`=?,
>> `internationalstring_description_e_id`=?, econtainer_class=?,
>> e_container=?, e_container_featureid=?, `version`=?, `startdate`=?,
>> `enddate`=?, `type_type_e_id`=?,
>> `representation_representation_e_id`=? where `urn`=?' with bind
>> parameters: {1=concept1, 2=UPDATED_CONCEPT, 3=661, 4=662,
>> 5=cs.ConceptScheme,
>> 6=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=SD MX:conceptScheme1,
>> 7=-12, 8=null, 9=null, 10=null, 11=null, 12=null,
>> 13=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}
>>
>>
>> 20:11:53,734 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'update `base_identifiable` set
>> `item_item_urn`=null, `item_item_idx`=null where `item_item_urn`=?'
>> with bind parameters: {1=urn:sdmx:org.sdmx.infomodel.base.Agency=SDMX}
>>
>> ***###*** 20:11:53,765 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'update `base_identifiable` set
>> `itemscheme_item_urn`=null, `itemscheme_item_idx`=null where
>> `itemscheme_item_urn`=?' with bind parameters:
>> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1}
>> ***###***
>>
>> 20:11:53,812 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'update `base_identifiable` set
>> `item_item_urn`=null, `item_item_idx`=null where `item_item_urn`=?'
>> with bind parameters:
>> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:co nceptScheme1.concept1}
>>
>>
>> 20:11:53,906 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'update `base_identifiable` set
>> `itemscheme_item_urn`=?, `itemscheme_item_idx`=? where `urn`=?' with
>> bind parameters:
>> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1,
>> 2=0,
>> 3=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:con ceptScheme1.concept1}
>>
>>
>> 20:11:53,906 DEBUG LogPreparedStatement:83 - executing
>> PreparedStatement: 'update `base_identifiable` set
>> `itemscheme_item_urn`=?, `itemscheme_item_idx`=? where `urn`=?' with
>> bind parameters:
>> {1=urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=S DMX:conceptScheme1,
>> 2=1,
>> 3=urn:sdmx:org.sdmx.infomodel.conceptscheme.Concept=SDMX:con ceptScheme1.concept3}
>>
>>
>> ------------------------------------------------------------ ------------------
>>
>>
>> It seems that Hibernate is setting the parent of all Items to be null
>> one by one then setting the parent of the items from the update to
>> point to the correct concept scheme. See ***###***
>>
>> I have tried to perform this equivalent update by loading the concept
>> scheme from hibernate, closing the session, removing the concept2,
>> adding concept3 then reattaching and updating the conceptScheme and
>> this seems to work. i.e. It seems to remember that concept2 existed
>> and therefore needs to deleted. But this is not the way it will work
>> in the real application, detached objects will always be loaded from
>> remote clients in a way similiar to above.
>>
>>
>> Duncan
>>
>>
>>
>> Martin Taal wrote:
>>> Hi Duncan,
>>> For containment relations it is required that an extra column is used
>>> to store the econtainer relation. This extra column is required to
>>> determine the container when the item is read from the db
>>> indepedently from the parent. Although in your example the parent
>>> could also be read from the itemscheme_item_urn this information is
>>> not necessarily present at hibernate (object) level of the item (only
>>> the parent knows who its children are). Also it is possible to model
>>> the containment association in a separate table of which the item
>>> does not 'officially' know that it is there.
>>> If you don't read the items independently from the parent or are not
>>> interested in the containment columns then there is an option to
>>> disable them.
>>>
>>> The other thing you note, that the item is not removed is not how it
>>> should be, the relation has delete-orphan which signals hibernate to
>>> remove the de-referenced items. Hibernate sees that the item is not
>>> part anymore of the association as it nullifies the foreign-key to
>>> the parent. Can you show me the update code?
>>>
>>> gr. Martin
>>>
>>> Duncan ALexander wrote:
>>>> Hi,
>>>> I noticed that Teneo generates multiple columns for references
>>>> between a child and parent in a one-to-many relationship. I can
>>>> understand what each of them represents but wonder why they both
>>>> must exist independently and I think it is causing my orphan objects
>>>> to not be deleted.
>>>>
>>>> For example, I have an object called ItemScheme which contains Items
>>>> in a list.
>>>>
>>>> The mapping looks like:
>>>>
>>>> <subclass entity-name="base.ItemScheme" abstract="true" lazy="false"
>>>> extends="base.Maintainable" discriminator-value="base.ItemScheme">
>>>> <meta attribute="eclassName">ItemScheme</meta>
>>>> <meta
>>>> attribute="epackage">http://www.sdmx.org/resources/infomodel/1.0/base.xmi</meta>
>>>>
>>>> <list name="Item" lazy="true" cascade="all,delete-orphan">
>>>> <key update="true">
>>>> <column name="`itemscheme_item_urn`"
>>>> not-null="false" unique="false"/>
>>>> </key>
>>>> <list-index column="`itemscheme_item_idx`"/>
>>>> <one-to-many entity-name="base.Item"/>
>>>> </list> </subclass>
>>>>
>>>> And when I look at the generated columns in the DB for Items I see
>>>> the itemscheme_item_urn column which points to the parent but also
>>>> an eContainer column which references the same parent.
>>>>
>>>> The problem I have is that if I update my ItemScheme from a
>>>> dettached object with a different set of Items then I can correctly
>>>> see my new items in the database with refernce to the ItemScheme
>>>> parent.
>>>>
>>>> But I can see that the old Items which are no longer referenced
>>>> still exist in the database with their itemscheme_item_urn field set
>>>> to null, but the eContainer field still referencing the ItemScheme.
>>>> I would expect the Item to be removed completely under these
>>>> circumstances.
>>>>
>>>> duncan
>>>
>>>
>
>

Report message to a moderator