You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Mike Kienenberger <mk...@gmail.com> on 2006/05/19 00:01:37 UTC
Error when join table spans datamaps
Here's an odd error that I've hit for the first time:
Caused by: java.sql.SQLException: invalid schema name: ENG_WORK_MGMT
in statement [SELECT DISTINCT t0.ACTIVITY_YEAR, t0.BUDGET_CODE, t0.GL,
t0.IS_ACTIVE, t0.NAME, t0.OBJ_TYPE, t0.SUB_ACTIVITY, t0.WORK_ORDER,
t0.ACTIVITY, t0.ID, t0.WORK_TYPE FROM CORE_WORK_MGMT.WORK t0,
ENG_WORK_MGMT.AUTHORIZATION_DOC__WORK t1 WHERE t0.ID = t1.WORK_ID AND
(t1.AUTHORIZATION_DOCUMENT_ID = ?)]
It's a join table where one foreign key points to a different DataMap
which is in a different DataNode/schema.
Any thoughts?
Re: Error when join table spans datamaps
Posted by Mike Kienenberger <mk...@gmail.com>.
Yeah, it's the relationships that are killing me -- in all other
cases, I can just write my own code.
Thanks!
On 5/19/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> On May 19, 2006, at 5:57 PM, Mike Kienenberger wrote:
>
> > On 5/18/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> >> Short of
> >> horrible performance, maybe doing joins within Cayenne SQLAction code
> >> is not such bad an idea???
> >
> > Taking a brief look at SQLAction -- Is there a way to register an
> > observer/delegate with the engine from the application that can return
> > an alternate query to execute?
>
> One way to customize SQLAction is to subclass a query itself
> overriding 'createSQLAction'. But this won't work for relationships
> of course.
>
> Alternatively you can use DataConextDelegate to intercept the queries
> and if needed wrap them in a custom decorator that builds the right
> SQLAction (check various indirect queries on how the decorator might
> look like, e.g. ObjectIdQuery).
>
> Andrus
>
>
Re: Error when join table spans datamaps
Posted by Andrus Adamchik <an...@objectstyle.org>.
On May 19, 2006, at 5:57 PM, Mike Kienenberger wrote:
> On 5/18/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>> Short of
>> horrible performance, maybe doing joins within Cayenne SQLAction code
>> is not such bad an idea???
>
> Taking a brief look at SQLAction -- Is there a way to register an
> observer/delegate with the engine from the application that can return
> an alternate query to execute?
One way to customize SQLAction is to subclass a query itself
overriding 'createSQLAction'. But this won't work for relationships
of course.
Alternatively you can use DataConextDelegate to intercept the queries
and if needed wrap them in a custom decorator that builds the right
SQLAction (check various indirect queries on how the decorator might
look like, e.g. ObjectIdQuery).
Andrus
Re: Error when join table spans datamaps
Posted by Mike Kienenberger <mk...@gmail.com>.
On 5/18/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> Short of
> horrible performance, maybe doing joins within Cayenne SQLAction code
> is not such bad an idea???
Taking a brief look at SQLAction -- Is there a way to register an
observer/delegate with the engine from the application that can return
an alternate query to execute?
Re: Error when join table spans datamaps
Posted by Mike Kienenberger <mk...@gmail.com>.
On 5/18/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> Yeah, things can be simplified in case of relationships (i.e.
> qualifiers matching a single object). A generic solution that works
> with an arbitrary qualifier will be substantially more involved.
Yeah, I just hit that problem in a selectQuery.
Re: Error when join table spans datamaps
Posted by Andrus Adamchik <an...@objectstyle.org>.
Yeah, things can be simplified in case of relationships (i.e.
qualifiers matching a single object). A generic solution that works
with an arbitrary qualifier will be substantially more involved.
Andrus
On May 18, 2006, at 7:05 PM, Mike Kienenberger wrote:
> On 5/18/06, Mike Kienenberger <mk...@gmail.com> wrote:
>> This is my first-pass at solving the problem at the application level
>
> Cleaned up version that can go in the Base DataObject class.
>
> protected List getToManyRelationshipTheHardWay(String
> targetPrimaryKey, String joinTable, String sourcePrimaryKey, Class
> targetClass)
> {
> String sql = "select distinct(" + targetPrimaryKey + ") as ID
> from " + joinTable + " where " + sourcePrimaryKey + " = $PRIMARY_KEY";
>
> // set parameters and run it...
> Map parameters = new HashMap();
> parameters.put("PRIMARY_KEY", getPrimaryKey());
> SQLTemplate rawSelect = new SQLTemplate(getClass(), sql, true);
> rawSelect.setParameters(parameters);
> rawSelect.setFetchingDataRows(true);
> List distinctFKList = getDataContext().performQuery(rawSelect);
>
> List toManyList = new ArrayList(distinctFKList.size());
> Iterator foreignKeyIterator = distinctFKList.iterator();
> while (foreignKeyIterator.hasNext())
> {
> DataRow dataRow = (DataRow) foreignKeyIterator.next();
> Object primaryKey = (Object) dataRow.get("ID");
> if (null == primaryKey) continue;
>
> toManyList.add(DataObjectUtils.objectForPK(getDataContext
> (),
> targetClass, primaryKey));
> }
>
> return toManyList;
> }
>
Re: Error when join table spans datamaps
Posted by Mike Kienenberger <mk...@gmail.com>.
On 5/18/06, Mike Kienenberger <mk...@gmail.com> wrote:
> This is my first-pass at solving the problem at the application level
Cleaned up version that can go in the Base DataObject class.
protected List getToManyRelationshipTheHardWay(String
targetPrimaryKey, String joinTable, String sourcePrimaryKey, Class
targetClass)
{
String sql = "select distinct(" + targetPrimaryKey + ") as ID
from " + joinTable + " where " + sourcePrimaryKey + " = $PRIMARY_KEY";
// set parameters and run it...
Map parameters = new HashMap();
parameters.put("PRIMARY_KEY", getPrimaryKey());
SQLTemplate rawSelect = new SQLTemplate(getClass(), sql, true);
rawSelect.setParameters(parameters);
rawSelect.setFetchingDataRows(true);
List distinctFKList = getDataContext().performQuery(rawSelect);
List toManyList = new ArrayList(distinctFKList.size());
Iterator foreignKeyIterator = distinctFKList.iterator();
while (foreignKeyIterator.hasNext())
{
DataRow dataRow = (DataRow) foreignKeyIterator.next();
Object primaryKey = (Object) dataRow.get("ID");
if (null == primaryKey) continue;
toManyList.add(DataObjectUtils.objectForPK(getDataContext(),
targetClass, primaryKey));
}
return toManyList;
}
Re: Error when join table spans datamaps
Posted by Mike Kienenberger <mk...@gmail.com>.
On 5/18/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> But this means that Cayenne will be doing a cartesian
> product in memory, turning Cayenne in a database engine. I am really
> not comfortable with "this is a natural limitation" excuse I was
> about to make. Still not sure how else we can solve this. Short of
> horrible performance, maybe doing joins within Cayenne SQLAction code
> is not such bad an idea???
I completely understand that this is going to give horrible performance.
But horrible performance is better than no performance.
This is my first-pass at solving the problem at the application level
by replacing getWorkList() { return (List)readProperty("workList"); }
with the following. Seems like there might be a better way to do
this without an SQLTemplate. If I hit a second relationship like
this, I would probably pull the string together from the
ObjEntity/DbEntity fields -- actually I might see if I can fix this in
the template since all of that info is available there.
However, I'm not certain that this is sufficient. This might not be
something I can fix from the application level, short of removing the
relationships in the datamap.
I'm hoping I can leave addToWorkList and removeFromWorkList alone.
I haven't looked at SQLAction so I'm not sure how that fits in yet.
I'll take a look when I get a chance.
private List getWorkListTheHardWay()
{
String sql = "select distinct(WORK_ID) as ID from
ENG_WORK_MGMT.AUTHORIZATION_DOC__WORK where AUTHORIZATION_DOCUMENT_ID
= $PRIMARY_KEY";
// set parameters and run it...
Map parameters = new HashMap();
parameters.put("PRIMARY_KEY", getPrimaryKey());
SQLTemplate rawSelect = new SQLTemplate(getClass(), sql, true);
rawSelect.setParameters(parameters);
rawSelect.setFetchingDataRows(true);
List distinctFKList = getDataContext().performQuery(rawSelect);
List toManyList = new ArrayList(distinctFKList.size());
Iterator foreignKeyIterator = distinctFKList.iterator();
while (foreignKeyIterator.hasNext())
{
DataRow dataRow = (DataRow) foreignKeyIterator.next();
Object primaryKey = (Object) dataRow.get("ID");
if (null == primaryKey) continue;
toManyList.add(DataObjectUtils.objectForPK(getDataContext(),
com.gvea.core_work_mgmt.entity.cayenne.Work.class, primaryKey));
}
return toManyList;
}
Re: Error when join table spans datamaps
Posted by Andrus Adamchik <an...@objectstyle.org>.
On May 18, 2006, at 6:28 PM, Mike Kienenberger wrote:
> But the root problem seems to be that Cayenne should probably not be
> doing a join this way. There's no guarantee that two DataNodes are
> running on the same database type, much less in the same database.
>
> Maybe it should be changing the join sql into two queries.
I understand. But this means that Cayenne will be doing a cartesian
product in memory, turning Cayenne in a database engine. I am really
not comfortable with "this is a natural limitation" excuse I was
about to make. Still not sure how else we can solve this. Short of
horrible performance, maybe doing joins within Cayenne SQLAction code
is not such bad an idea???
Andrus
Re: Error when join table spans datamaps
Posted by Mike Kienenberger <mk...@gmail.com>.
On 5/18/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> Tough one. What DB is that, Oracle? Can you run this query from sqlplus?
I'm testing it with HSQLDB. It'll be production on Oracle. On
Oracle, I could "cheat" in this particular case and set up a login
that accesses both schemas.
I could probably do something similar in HSQLDB by moving both schemas
into the same database.
But the root problem seems to be that Cayenne should probably not be
doing a join this way. There's no guarantee that two DataNodes are
running on the same database type, much less in the same database.
Maybe it should be changing the join sql into two queries.
> On May 18, 2006, at 6:01 PM, Mike Kienenberger wrote:
>
> > Here's an odd error that I've hit for the first time:
> >
> > Caused by: java.sql.SQLException: invalid schema name: ENG_WORK_MGMT
> > in statement [SELECT DISTINCT t0.ACTIVITY_YEAR, t0.BUDGET_CODE, t0.GL,
> > t0.IS_ACTIVE, t0.NAME, t0.OBJ_TYPE, t0.SUB_ACTIVITY, t0.WORK_ORDER,
> > t0.ACTIVITY, t0.ID, t0.WORK_TYPE FROM CORE_WORK_MGMT.WORK t0,
> > ENG_WORK_MGMT.AUTHORIZATION_DOC__WORK t1 WHERE t0.ID = t1.WORK_ID AND
> > (t1.AUTHORIZATION_DOCUMENT_ID = ?)]
> >
> > It's a join table where one foreign key points to a different DataMap
> > which is in a different DataNode/schema.
> >
> > Any thoughts?
> >
>
>
Re: Error when join table spans datamaps
Posted by Andrus Adamchik <an...@objectstyle.org>.
Tough one. What DB is that, Oracle? Can you run this query from sqlplus?
Andrus
On May 18, 2006, at 6:01 PM, Mike Kienenberger wrote:
> Here's an odd error that I've hit for the first time:
>
> Caused by: java.sql.SQLException: invalid schema name: ENG_WORK_MGMT
> in statement [SELECT DISTINCT t0.ACTIVITY_YEAR, t0.BUDGET_CODE, t0.GL,
> t0.IS_ACTIVE, t0.NAME, t0.OBJ_TYPE, t0.SUB_ACTIVITY, t0.WORK_ORDER,
> t0.ACTIVITY, t0.ID, t0.WORK_TYPE FROM CORE_WORK_MGMT.WORK t0,
> ENG_WORK_MGMT.AUTHORIZATION_DOC__WORK t1 WHERE t0.ID = t1.WORK_ID AND
> (t1.AUTHORIZATION_DOCUMENT_ID = ?)]
>
> It's a join table where one foreign key points to a different DataMap
> which is in a different DataNode/schema.
>
> Any thoughts?
>