Please pardon the cross link as I recently posted this on the EMF forum, but after pondering the matter I wonder it the issue may be more a matter of how EclipseLink is behaving with the generate orm.xml - cheers Joel

Hi,

I am having a problem persisting a rather simple EMF model using Teneo with Eclipselink. The ORM that is generated appears correct to me and the database is created without an issue. But when I try to persist entities the generated SQL is trying to discriminate on a non existent primary key.

[EL Fine]: Connection(1189166715)--UPDATE RECIPE SET RECIPE_MYCATALOGUE_ID = 1 WHERE (ID = 2)
[EL Fine]: VALUES(1)
[EL Warning]: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100614-r7608): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Column 'ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'ID' is not a column in the target table.
Error Code: 20000
Call: UPDATE RECIPE SET RECIPE_MYCATALOGUE_ID = 1 WHERE (ID = 2)
Query: DataModifyQuery(sql="UPDATE RECIPE SET RECIPE_MYCATALOGUE_ID = ? WHERE (ID = ?)")

I will add the whole of the run's output at the end of this post for context.

The ecore model:

<?xml version="1.0" encoding="UTF-8"?>
<ecore:EPackage xmi:version="2.0"
xmlns:xmi="http://www.omg.org/XMI" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ecore="http://www.eclipse.org/emf/2002/Ecore" name="recipes"
nsURI="http:///com.mirthfullife.epicerie/epicerie.ecore" nsPrefix="com.mirthfullife.epicerie">
<eClassifiers xsi:type="ecore:EClass" name="RecipeCatalogue" eSuperTypes="#//Identifiable">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="This is the receptacle of all recipes. Any system may have one or more RecipeCatalogue objects.&#xA;&#xA;RecipeCatalogue knows about and can report on all the Recipe objects it contains."/>
</eAnnotations>
<eStructuralFeatures xsi:type="ecore:EReference" name="myRecipes" upperBound="-1"
eType="#//Recipe" containment="true" eOpposite="#//Recipe/myCatalogue"/>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="name" lowerBound="1" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EString"
defaultValueLiteral="Epicerie Recipe Catalogue">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The name of the catalogue."/>
</eAnnotations>
</eStructuralFeatures>
</eClassifiers>
<eClassifiers xsi:type="ecore:EClass" name="BookKeeper" abstract="true" eSuperTypes="#//Identifiable">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The BookKeeper class knows about all book keeping type information that it is useful to record for any given Recipe, such as its creation date and its date of last modification.&#xA;&#xA;This information is automatically generated and maintained by the system and reported to the user, if needed, as read only information."/>
</eAnnotations>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="creationDate" lowerBound="1"
eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EDate">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The creation date of this object. This is automatically set at object creation time and cannot be modified."/>
</eAnnotations>
</eStructuralFeatures>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="modificationDate" lowerBound="1"
eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EDate">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The date of last modification of the object. This is automatically set each time the object is edited."/>
</eAnnotations>
</eStructuralFeatures>
</eClassifiers>
<eClassifiers xsi:type="ecore:EClass" name="Recipe" abstract="true" eSuperTypes="#//BookKeeper">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The base (abstract) class of all recipes that are held in the system. &#xA;Any concrete type of recipe (such as PaperRecipe, WebRecipe etc.) extends this base type.&#xA;&#xA;This class holds all cataloguing information that is needed to retrieve recipes from the system. Its essential attributes are its unique ID, which is created by the system at recipe creation time, and its name, which identify the recipe to the user.&#xA;&#xA;As a BookeKeeper object, Recipe also knows about all bookkeeping attributes, such as creation and modification date, which are automatically maintained by the system.&#xA;&#xA;Recipe objects are contained in one and only one specific RecipeCatalogue, of which they are aware."/>
</eAnnotations>
<eOperations name="getSource" lowerBound="1" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EString">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="This method returns the source of this Recipe, which describes whether this is an internal recipe, a recipe held on loose paper, a book recipe etc.&#xA;&#xA;This is an abstract method, implemented by each concerete Recipe subclass. "/>
</eAnnotations>
</eOperations>
<eStructuralFeatures xsi:type="ecore:EReference" name="myCatalogue" lowerBound="1"
eType="#//RecipeCatalogue" eOpposite="#//RecipeCatalogue/myRecipes"/>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="name" lowerBound="1" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EString">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The name of the recipe. It does not have to be unique, as we allow multiple recipes with the same name."/>
</eAnnotations>
</eStructuralFeatures>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="course" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EString">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The course represented by this recipe, namely aperitif, petiser, main course, dessert. &#xA;&#xA;This field will eventually be an enum provided by the persistent store and selected by the user, but it is at first modellled as a simple String for simplicity, with the user being able to enter free text in it."/>
</eAnnotations>
</eStructuralFeatures>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="meal" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EString">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The meal this recipe belongs to, namely breakfast, brunch, lunch, snack, dinner. &#xA;&#xA;This field will eventually be an enum provided by the persistent store and selected by the user, but it is at first modellled as a simple String for simplicity, with the user being able to enter free text in it."/>
</eAnnotations>
</eStructuralFeatures>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="primaryIngredient" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EString">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="The ingredient that primarily characterises this recipe, such as meat, fowl, fish, vegetable etc.&#xA;&#xA;This field will eventually be an enum provided by the persistent store and selected by the user, but it is at first modellled as a simple String for simplicity, with the user being able to enter free text in it."/>
</eAnnotations>
</eStructuralFeatures>
</eClassifiers>
<eClassifiers xsi:type="ecore:EClass" name="PaperRecipe" eSuperTypes="#//Recipe">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="This is a concrete Recipe that represents a recipe that is currently stored on loose leaf paper in VO's catalogue. &#xA;&#xA;Such a class does not need more information than is already provided by its superclass, as all other details, such as ingredients, preparation, images etc. are contained in its paper representation. As such, objects of this type essentially represent references to the external paper recipes.&#xA;&#xA;PaperRecipe is especially useful during the initial adoption of L'Epicerie, when the user may want to start cataloguing her recipes in order to get some of the benefits of L'Epicerie but without spending too much time entering recipe details. A PaperRecipe may then be converted into a full blown recipe at any later time."/>
</eAnnotations>
</eClassifiers>
<eClassifiers xsi:type="ecore:EClass" name="WebRecipe" eSuperTypes="#//Recipe">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="This is a concrete Recipe that represents a recipe that is currently stored on an external website. &#xA;&#xA;Such a class does not need more information than is already provided by its superclass, as all other details, such as ingredients, preparation, images etc. are contained in its web representation. As such, objects of this type essentially represent references to the external web recipes.&#xA;&#xA;WebRecipe is especially useful during the initial adoption of L'Epicerie, when the user may want to start cataloguing her recipes in order to get some of the benefits of L'Epicerie but without spending too much time entering recipe details. &#xA;A WebRecipe may then be converted into a full blown recipe at any later time."/>
</eAnnotations>
</eClassifiers>
<eClassifiers xsi:type="ecore:EClass" name="BookRecipe" eSuperTypes="#//Recipe">
<eAnnotations source="http://www.eclipse.org/emf/2002/GenModel">
<details key="documentation" value="This is a concrete Recipe that represents a recipe that is currently stored in a cookery book. &#xA;&#xA;Such a class does not need more information than is already provided by its superclass, as all other details, such as ingredients, preparation, images etc. are contained in its book representation. As such, objects of this type essentially represent references to the external book recipes.&#xA;&#xA;BookRecipe is especially useful during the initial adoption of L'Epicerie, when the user may want to start cataloguing her recipes in order to get some of the benefits of L'Epicerie but without spending too much time entering recipe details.&#xA;A BookRecipe may then be converted into a full blown recipe at any later time."/>
</eAnnotations>
</eClassifiers>
<eClassifiers xsi:type="ecore:EClass" name="Identifiable" abstract="true">
<eAnnotations source="teneo.jpa">
<details key="value" value="@MappedSuperclass"/>
</eAnnotations>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="id" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//ELong">
<eAnnotations source="teneo.jpa">
<details key="value" value="@Id&#xA;@GeneratedValue"/>
</eAnnotations>
</eStructuralFeatures>
<eStructuralFeatures xsi:type="ecore:EAttribute" name="version" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//ELong">
<eAnnotations source="teneo.jpa">
<details key="value" value="@Version"/>
</eAnnotations>
</eStructuralFeatures>
</eClassifiers>
</ecore:EPackage>

I'm not adept at reading ecore models so can you post a diagram of the class hierarchy or attach the generated source? I see that you have a mappedsuperclass IdentifiableImpl that defines an ID and I'm wondering if you have subclasses that attempt to redefined identity? I'm also wondering if you're defining the inheritance strategy only once in each hierarchy.

I would be happy to attach source and diagram, but that is not a option on the forum as far as I know. Two paths I could follow is to email you direct or open a bug report. Which do you think is most appropriate in this case?

The subclasses do not redefine the ID; the reason for the Identifiable is create the ID once at the top of each hierarchy rather than redefining it everywhere. In EMF it is non trivial to create a persistent ID, so the strategy of doing it once saves effort and mistakes. I borrowed this pattern from the EclipseLink modified EMF Library example. It seems to work only at the first level of inheritance which is rather restricting. And yes the strategy is defined only once right at the top and peculates down.

Thanks,
Joel

Shaun Smith wrote on Mon, 06 September 2010 16:25

I'm not adept at reading ecore models so can you post a diagram of the class hierarchy or attach the generated source? I see that you have a mappedsuperclass IdentifiableImpl that defines an ID and I'm wondering if you have subclasses that attempt to redefined identity? I'm also wondering if you're defining the inheritance strategy only once in each hierarchy.

What is the purpose of the <primary-key-join-column name="BOOKKEEPER_ID" /> tag within the RecipeImpl orm mapping? This seems to cause the problem, as EclipseLink seems unsure of what the ID field is - it creates the table using BOOKKEEPER_ID instead because of this tag. Removing the tag will correct this problem.

I expected this was the case, so it appears that this is a Teneo issue as it is Teneo that generates the ORM. I will ask Martin if he can have another think about this. - Thanks

Chris Delahunt wrote on Tue, 07 September 2010 14:47

Hello Joel,

What is the purpose of the <primary-key-join-column name="BOOKKEEPER_ID" /> tag within the RecipeImpl orm mapping? This seems to cause the problem, as EclipseLink seems unsure of what the ID field is - it creates the table using BOOKKEEPER_ID instead because of this tag. Removing the tag will correct this problem.

None of my posts seem to work this afternoon. Here's one more attempt.

The BOOKKEEPER_ID tag in the orm.xml seems to be sets up a "BOOKKEEPER_ID" field in RECIPE to point to the BOOKKEEPER table, and acts as the Id field as well. This isn't incorrect neccessarily - inserts seem to work quite well. The problem is that the update statement seems to be using SQL directly, and is expecting a field "ID" to exist on the RECIPE Table. So there is a mismatch in this update statement - either the Orm is wrong to set up this field, or the update query is wrong to use an "ID" field.

You might want to check the stack trace on the exception to see how/where the query gets created. If it is controlled by the application, it chances are that the app is using a native sql query instead of a JPQL query (createNativeQuery vs createQuery). If it had have used JPQL, EclipseLink would translate the recipe.id into the correct "BOOKKEEPER_ID" field automatically. So if you do have control of it, you might be able to change it to use the correct field for the SQL or to use a JPQL query instead.

Here is the stacktrace. As far as I can tell there is not an indication of who actually creates the update statement. Possibly this is because it is being ran under Junit and part to the trace has been lost. Tomorrow I will set up a small example to simply run this code from the Activator to see if I can catch more.

I looked a bit further with the stack trace, see that the update statement is issued to set the foreign key, even though it is set correctly in the insert statement. Looking again at the mappings, this is occuring because the RecipeImpl has a Many-to-one to RecipeCatalogueImpl which is setting the value in the insert statement, but the RecipeCatalogueImpl has an independent One-to-many back to RecipeImpl that is using a join-column, which is causing the update statement.

This is a problem because it means there are two potentially independent mappings trying to set the RECIPE's RECIPE_MYCATALOGUE_ID field. A bug should be filed in EclipseLink to throw an error that there are multiple writable mappings for this field, and the the orm needs to be changed so that the One-to-Many is 'mapped-by' the eContainer attribute.

There could also be another bug in EclipseLink since the update statement should still not be using the "ID" field. I'll try to file a bug if I can reproduce it without the extra writable mappings.

My model and project is simple enough at the moment, but but they utilise Spring to initialise all bundles. I will have to strip this back to a basic test case to reduce complications. I will not be able to do this till Saturday.

Many Thanks,
Joel

Chris Delahunt wrote on Wed, 08 September 2010 14:59

Hello Joel,

I looked a bit further with the stack trace, see that the update statement is issued to set the foreign key, even though it is set correctly in the insert statement. Looking again at the mappings, this is occuring because the RecipeImpl has a Many-to-one to RecipeCatalogueImpl which is setting the value in the insert statement, but the RecipeCatalogueImpl has an independent One-to-many back to RecipeImpl that is using a join-column, which is causing the update statement.

This is a problem because it means there are two potentially independent mappings trying to set the RECIPE's RECIPE_MYCATALOGUE_ID field. A bug should be filed in EclipseLink to throw an error that there are multiple writable mappings for this field, and the the orm needs to be changed so that the One-to-Many is 'mapped-by' the eContainer attribute.

There could also be another bug in EclipseLink since the update statement should still not be using the "ID" field. I'll try to file a bug if I can reproduce it without the extra writable mappings.