[Cayenne-user] Problem with inheritance

On Fri, Dec 9, 2011 at 3:00 AM, Mathias Clerc wrote:
Hello,

It seems like I have missed something in inheritance in Cayenne.

I have one main table called "Element" with fields id (PK), name and description
Another table "File" inherits from "Element" with fields ID(PK, FK on
Element.id) and path.

When I do a context.performIteratedQuery(new
SelectQuery(Element.class)) The query I see is :
SELECT t0.ID, t0.DESCRIPTION, t0.NAME, t0.TYPE, t0.ID, t0.ID, t0.PATH
FROM ELEMENT t0

I tried checking and unchecking the "To Dep PK" field for the relation in ID.

What did I miss ?

Hi Mathias,

I believe you are trying to use a currently unimplemented inheritance
mechanism.  Cayenne does not currently support horizontal
(multiple-table) inheritance:

http://cayenne.apache.org/doc/inheritance-overview.html

There are tricks you can do to make working with multiple related
tables easier (such as adding getters/setters in your subclass to
reference the parent), but you cannot currently model this behavior
automatically.

mrg

2011/12/13 Mathias Clerc <tlarhices@gmail.com>:
Hi Michael,

Hello

Actually I am trying to use Cayenne vertical inheritance. Some more
details on what I did :

I have followed the guide here :
http://cayenne.apache.org/doc30/modeling-vertical-inheritance.html
and with a lot of trial and error, I finally got it to insert data.

Unfortunately I cannot make the select queries to work correctly.
After some searches I found that old thread with the same problem :
http://cayenne.195.n3.nabble.com/Vertical-inheritance-td827636i20.html
I tried the workaround from that thread but it didn't help.


In my DB, I have one root table called ELEMENT from which the tables
"FILE", "PRINCIPAL" and "PERMISSION" are inheriting.
If I create a new ELEMENT like this :
Element elem = context.newObject(Element.class);
elem.setName("test1");
elem.setDescription("test1 description");
elem.setType("ELEMENT");
Or a PRINCIPAL like this :
Principal p = context.newObject(Principal.class);
p.setPassword("password");
p.setName("principal1");
p.setDescription("principal1 description");
p.setType("PRINCIPAL");
It inserts perfectly.

If I then run through JDBC "SELECT t0.ID, t0.DESCRIPTION, t0.NAME FROM
ELEMENT t0", I get the values back.

But if I make a simple select with Cayenne like :
context.performQuery(new SelectQuery(Element.class));

It does not return anything as it is trying to use the query :
SELECT t0.ID, t0.DESCRIPTION, t0.NAME, t0.TYPE, t1.PATH, t2.PASSWORD,
t3.VALUE FROM ELEMENT t0 JOIN FILE t1 ON (t0.ID = t1.ID) JOIN
PRINCIPAL t2 ON (t0.ID = t2.ID) JOIN PERMISSION t3 ON (t0.ID = t3.ID)

The query is wrong as it is trying to join the parent table on all the
child tables at the same time.
"test1" is in no subtable and "principal1" is not in FILE or PERMISSION.

I have put the map file content there : http://pastebin.com/KtVhDdYE


Do you have any idea on what to try next to get it to work ?

2011/12/13 Michael Gentry <mgentry@masslight.net>:

On Tue, Dec 13, 2011 at 2:47 AM, Mathias Clerc wrote:
2011/12/13 Mathias Clerc <tlarhices@gmail.com>:
If I replace my query by :
String sql = "select * FROM ELEMENT";
SQLTemplate select = new SQLTemplate(Element.class, sql);

List<?> result = context.performQuery(select);

Then it works perfectly well. When an element with type="FILE" is
found it returns a File instance, when an element with
type="PRINCIPAL" is found it returns a Principal instance.

I guess I am missing some simple checkbox somewhere in the modeler.

Hi Mathias,

I think we might need to see more of your model to understand what is
going on.  In the query you gave:

SELECT t0.ID, t0.DESCRIPTION, t0.NAME, t0.TYPE, t1.PATH, t2.PASSWORD,
t3.VALUE FROM ELEMENT t0 JOIN FILE t1 ON (t0.ID = t1.ID) JOIN
PRINCIPAL t2 ON (t0.ID = t2.ID) JOIN PERMISSION t3 ON (t0.ID = t3.ID)

I'm curious as to while FILE is listed there when you are retrieving
PRINCIPAL.  I suspect you have something in the joins not quite right.

Another option would be to do a smaller test case.  Create a small
project with just the inheritance and use an H2 in-memory database to
test it.  This is how I often explore a lot of things.  H2 is great
for this kind of activity because it is small and vast and you don't
need a complicated setup (it is memory-only).  If you try this, be
sure you use org.apache.cayenne.access.dbsync.CreateIfNoSchemaStrategy
on the DataNode to automatically create your schema for your test.

mrg