Problem querying vertical inheritance

[Mark Rigby-Jones <ma...@mdnx.com>]

Hi all,

I've used Cayenne on few projects, and I'm now attempting to use inheritance for the first time. Whilst the current scope is fairly limited, I'm expecting that the inheritance tree will grow considerably in the future and thus am trying to use some amount of vertical inheritance for sub-types where I'm expecting a significant amount of additional data.

Creating new objects works fine - the discriminator column in the primary table is set correctly, and rows are added to the correct secondary tables. Trying to do a select on the root abstract object, however, returns nothing - it appears to be doing a JOIN against all of the secondary tables, which fails as no object has an entry in more than one of those table (I would expect a LEFT JOIN instead).

I include a minimal example below. If somebody could point out what where I'm going wrong or what I'm misunderstanding, that would be much appreciated.


Environment: Cayenne 3.0.2, MySQL 5.0.77. XML, classes and schema generated from the modeler.

Data map:

<?xml version="1.0" encoding="utf-8"?>
<data-map xmlns="http://cayenne.apache.org/schema/3.0/modelMap"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://cayenne.apache.org/schema/3.0/modelMap http://cayenne.apache.org/schema/3.0/modelMap.xsd"
  project-version="3.0.0.1">
	<property name="defaultPackage" value="org.example.test"/>
	<db-entity name="ITEM">
		<db-attribute name="ID" type="INTEGER" isPrimaryKey="true" isMandatory="true"/>
		<db-attribute name="TYPE" type="CHAR" isMandatory="true" length="1"/>
	</db-entity>
	<db-entity name="ITEM_BAR">
		<db-attribute name="BAR" type="VARCHAR" isMandatory="true" length="255"/>
		<db-attribute name="ID" type="INTEGER" isPrimaryKey="true" isMandatory="true"/>
	</db-entity>
	<db-entity name="ITEM_FOO">
		<db-attribute name="FOO" type="VARCHAR" isMandatory="true" length="255"/>
		<db-attribute name="ID" type="INTEGER" isPrimaryKey="true" isMandatory="true"/>
	</db-entity>
	<obj-entity name="BarItem" superEntityName="Item" className="org.example.test.BarItem">
		<qualifier><![CDATA[type = "B"]]></qualifier>
		<obj-attribute name="bar" type="java.lang.String" db-attribute-path="ITEM_BAR.BAR"/>
		<attribute-override name="type" db-attribute-path="TYPE"/>
	</obj-entity>
	<obj-entity name="FooItem" superEntityName="Item" className="org.example.test.FooItem">
		<qualifier><![CDATA[type = "F"]]></qualifier>
		<obj-attribute name="foo" type="java.lang.String" db-attribute-path="ITEM_FOO.FOO"/>
	</obj-entity>
	<obj-entity name="Item" abstract="true" className="org.example.test.Item" dbEntityName="ITEM">
		<obj-attribute name="type" type="java.lang.String" db-attribute-path="TYPE"/>
	</obj-entity>
	<db-relationship name="ITEM_BAR" source="ITEM" target="ITEM_BAR" toDependentPK="true" toMany="false">
		<db-attribute-pair source="ID" target="ID"/>
	</db-relationship>
	<db-relationship name="ITEM_FOO" source="ITEM" target="ITEM_FOO" toDependentPK="true" toMany="false">
		<db-attribute-pair source="ID" target="ID"/>
	</db-relationship>
	<db-relationship name="ITEM" source="ITEM_BAR" target="ITEM" toMany="false">
		<db-attribute-pair source="ID" target="ID"/>
	</db-relationship>
	<db-relationship name="ITEM" source="ITEM_FOO" target="ITEM" toMany="false">
		<db-attribute-pair source="ID" target="ID"/>
	</db-relationship>
</data-map>

Test Code:

	ObjectContext context = DataContext.createDataContext();

	FooItem foo = context.newObject(FooItem.class);
	foo.setFoo("Foo");
	BarItem bar = context.newObject(BarItem.class);
	bar.setBar("Bar");
	context.commitChanges();

	SelectQuery select = new SelectQuery(Item.class);
	List<Item> items = context.performQuery(select);
	select = new SelectQuery(FooItem.class);
	items = context.performQuery(select);
	select = new SelectQuery(BarItem.class);
	items = context.performQuery(select);

Output:

INFO: --- transaction started.INFO: Detected and installed adapter: org.apache.cayenne.dba.mysql.MySQLAdapterINFO: LOCK TABLES AUTO_PK_SUPPORT WRITE
INFO: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = 'ITEM'
INFO: UPDATE AUTO_PK_SUPPORT SET NEXT_ID = NEXT_ID + 20 WHERE TABLE_NAME = 'ITEM' AND NEXT_ID = 420
INFO: UNLOCK TABLES
INFO: --- will run 3 queries.
INFO: INSERT INTO ITEM (ID, TYPE) VALUES (?, ?)
INFO: [batch bind: 1->ID:420, 2->TYPE:'F']
INFO: [batch bind: 1->ID:421, 2->TYPE:'B']
INFO: === updated 2 rows.
INFO: INSERT INTO ITEM_FOO (FOO, ID) VALUES (?, ?)
INFO: [batch bind: 1->FOO:'Foo', 2->ID:420]
INFO: === updated 1 row.
INFO: INSERT INTO ITEM_BAR (BAR, ID) VALUES (?, ?)
INFO: [batch bind: 1->BAR:'Bar', 2->ID:421]
INFO: === updated 1 row.
INFO: +++ transaction committed.
INFO: --- will run 1 query.
INFO: --- transaction started.
INFO: SELECT t0.TYPE, t1.BAR, t2.FOO, t0.ID FROM ITEM t0 JOIN ITEM_BAR t1 ON (t0.ID = t1.ID) JOIN ITEM_FOO t2 ON (t0.ID = t2.ID) - prepared in 10 ms.
INFO: === returned 0 rows. - took 17 ms.
INFO: +++ transaction committed.
INFO: --- will run 1 query.
INFO: --- transaction started.
INFO: SELECT t0.TYPE, t1.FOO, t0.ID FROM ITEM t0 JOIN ITEM_FOO t1 ON (t0.ID = t1.ID) WHERE t0.TYPE = ? [bind: 1->TYPE:'F']
INFO: === returned 1 row. - took 5 ms.
INFO: +++ transaction committed.
INFO: --- will run 1 query.
INFO: --- transaction started.
INFO: SELECT t0.TYPE, t1.BAR, t0.ID FROM ITEM t0 JOIN ITEM_BAR t1 ON (t0.ID = t1.ID) WHERE t0.TYPE = ? [bind: 1->TYPE:'B']
INFO: === returned 1 row. - took 1 ms.
INFO: +++ transaction committed.


mrj
-- 
Mark Rigby-Jones
Senior IT Applications Developer

A.  MDNX, St James House, Oldbury, Bracknell, Berkshire, RG12 8TH 
T.  01344 543 976   M.  07747 86 22 01    W.  www.mdnx.com

MDNX Group Limited is registered in England and Wales number 07374236.  
Registered office: St James House, Oldbury, Bracknell, Berkshire, RG12 8TH

This communication contains information which is confidential and may also be privileged. It is for the exclusive use of the addressee. If you are not the addressee please note that any distribution, reproduction, copying, publication or use of this communication or the information is prohibited. If you have received this communication in error, please contact us immediately and also delete the communication from your computer. We accept no liability for any loss or damage suffered by any person arising from use of this e-mail. 

Please consider the environment and only print this email if you really need to.