This one time, at band camp, Matt Mower said:
MM>I hope someone can help me. I'm in over my head...
MM>
MM>I am experimenting with Castor JDO at the moment (0.9.5.2) and am having
MM>a problem where Castor appears to be generating invalid SQL against my
MM>HSQLDB 1.7.1 database.
MM>
MM>I have a class 'Topic' which uses a 'parent' field to allow topics to
MM>refer to each other in a hierarchy. By trial and error I came up with
MM>the following mapping for the class:
MM>
MM> <class name="Topic" identity="id" key-generator="IDENTITY">
MM> <map-to table="topics"/>
MM>
MM> <field name="id" type="java.lang.Integer">
MM> <sql name="id" type="integer"/>
MM> </field>
MM> <field name="name" type="java.lang.String">
MM> <sql name="name" type="varchar"/>
MM> </field>
MM> <field name="parent" type="Topic">
MM> <sql name="parent"/>
MM> </field>
MM> <field name="subTopics" type="Topic" required="true"
MM>collection="arraylist">
MM> <sql many-table="topics" many-key="parent"/>
MM> </field>
MM> </class>
MM>
MM>for the table built with:
MM>
MM> create table topics ( id int identity, name varchar(64) not null,
MM>parent int );
MM>
MM>To get a list of top-level topics (ID=-1) I formulated an OQL query:
MM>
MM> SELECT t FROM Topic t WHERE parent = -1
MM>
MM>and the SQL being generated is:
MM>
MM> SELECT topics.id,topics.name,topics.parent,topics_f2.id FROM topics
MM>LEFT OUTER JOIN topics_f2 ON (topics.id=topics_f2.parent) WHERE
MM>topics.parent = - 1
MM>
MM>which is failing because no table 'topics_f2' exists in the database.
MM>
MM>Using command line SQL I amended the query to:
MM>
MM> SELECT topics.id,topics.name,topics.parent,f2.id FROM topics LEFT OUTER
MM>JOIN topics f2 ON (topics.id=f2.parent) WHERE topics.parent = - 1;
MM>
MM>[by changing 'topics_f2' to 'topics f2' and using 'f2' as the table
MM>reference from there on.]
MM>
MM>Using this modified query I get what look like valid results:
MM>
MM>ID NAME PARENT ID
MM>0 A -1 2
MM>0 A -1 3
MM>0 A -1 4
MM>1 B -1 5
MM>1 B -1 6
MM>
MM>where topics with ID 2,3,4 have parent 0 and 5,6 have parent 1. I am
MM>assuming that Castor would then lazy-instantiate the corresponding child
MM>Topics and populate the sub-topics array on request.
MM>
MM>I searched the archives and BugZilla before posting. I did find someone
MM>who seems to have logged the exact same problem
MM>(http://www.mail-archive.com/[EMAIL PROTECTED]/msg15923.html)
MM>couldn't see a solution linked from that.
MM>
MM>I'd be really grateful if someone could advise me how to go forward with
MM>this problem.
Matt,
I believe that this is bug in the alias creation so I've filed a Bugzilla
report on it:
http://bugzilla.exolab.org/show_bug.cgi?id=1483
I think it's a pretty simple thing to fix. The reason that it hasn't
been fixed yet is that this type of alias is only created for a use case
where classes of the same type are being joined (aka self-join). Castor
does not directly support self-joins, but there's a slight chance that
it might work for you.
Anyway I am generating a test case for this situation and I'd like to
get your client code from you to include in the test. Please post it to
the mailing list so that I can proceed.
Bruce
--
perl -e 'print unpack("u30","<0G)[EMAIL PROTECTED]&5R\"F9E<G)E=\$\!F<FEI+F-O;0\`\`");'
The Castor Project
http://www.castor.org/
Apache Geronimo
http://incubator.apache.org/projects/geronimo.html
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
