hi phil,
Phil Warrick wrote:
> Hi Jakob, > > And I could try extending the current code to ?quickly? see whether > there are other issues, and perhaps later all this could be refactored. > > Often I have too much on my plate integrating components in my EJB > system to spend extra time with one component (i.e. ojb), but right now > is a good time for me because the system is relatively stable and the > users are happy. I'd like to give something back again to the ojb > project which has allowed me to concentrate on higher-level issues. And > this will in turn allow me to entend the query functionality that I need
well this sounds great !
imo refactoring SqlQueryStatement to pass the Criteria to the relevant methods
start early in the call-tree:
appendSQLClause knows the SelectionCriteria which has a link to its parent:
getCriteria(). another crucial method is getAttributeInfo which currently only
gets the attribute-name as parameter.
if you then have the Criteria available in the relevant methods (don't know all
of them right now) you'll need to extend Criteria to hold path-hints and
path-segments for the alias. extending Criteria is imo the easier part, but it's
useless without the changes in SqlQueryStatement. another issue is backward
compatibility.
> in my system. In the meantime, I am prototyping with SQL queries, as in:
what are you prototyping using SQL ?
All the queries that I can't do at the moment with the PB-api, as we have discussed, that is per-Criteria specification of
1) aliases
2) hints
I need to generate annual reports containing hundreds of queries each. They operate on large tables: worst case table collects ~1M rows per year.
The problem domain is obstetrics: a statistical database of all mothers and babies for births at a McGill University teaching hospital.
Phil
jakob
>
> Query query = QueryFactory.newQuery(A.class, sqlString);
>
> The object model in small but complex and so these are very nasty
> queries, and I literally need hundreds of them :(
>
> Phil
>
>> hi phil,
>>
>> the problem is that most of the methods in SqlQueryStatement do not
>> know from which criteria they are called, so we'd have to add Criteria
>> as additional parameter. actually it's almost the same problem as in
>> my post regarding aliases.
>> in the future (no one knows when...) i'd like to completely refactor
>> SqlQueryStatement because it's getting larger and larger... actually
>> i'd like to separate join-/alias-handling from building the
>> sql-string. during this refactoring i could integrate criteria-base
>> paths and hints.
>>
>> jakob
>>
>> Phil Warrick wrote:
>>
>>> Jakob,
>>>
>>> This looks good and I think it also applies to the other cases that I
>>> mentioned. Is it a relatively simple change to SqlQueryStatement?
>>>
>>> Phil
>>>
>>> Jakob Braeuchi wrote:
>>>
>>>> hi phil,
>>>>
>>>> imo the best solution would be to declare for what segments of the
>>>> path the alias is valid:
>>>>
>>>> crit1 = new Criteria();
>>>> crit1.setAlias("bToC1","cs");
>>>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>>>>
>>>> crit2 = new Criteria();
>>>> crit2.setAlias("bToC2","cs");
>>>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>>>>
>>>> crit1.addAndCriteria(crit2);
>>>> query = new QueryByCriteria(A.class, crit1);
>>>>
>>>> this would result in A,B,C1 and C2.
>>>>
>>>> crit1 = new Criteria();
>>>> crit1.setAlias("bToC1","bs.cs");
>>>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>>>>
>>>> crit2 = new Criteria();
>>>> crit2.setAlias("bToC2","bs.cs");
>>>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>>>>
>>>> crit1.addAndCriteria(crit2);
>>>> query = new QueryByCriteria(A.class, crit1);
>>>>
>>>> this would result in A,B1,B2,C1 and C2.
>>>>
>>>> it's not as elegant as an automatic solution, but i think i
>>>> sufficiently flexible.
>>>>
>>>> jakob
>>>>
>>>> Phil Warrick wrote:
>>>>
>>>>> Hi Jakob,
>>>>>
>>>>> You're right, there's some more general rule at work here.
>>>>>
>>>>> I think that if the path is 1-M or 1-1 followed by another 1-1, the
>>>>> alias should affect all segments of the path.
>>>>>
>>>>> If the path is 1-M followed by 1-M, there should be a choice. >>>>> Consider the following:
>>>>>
>>>>> X-1--------M-Y-1--------M-Z
>>>>>
>>>>> At least two different query are possible:
>>>>>
>>>>> 1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
>>>>> another Z with z.zAttrib = 'bar'
>>>>>
>>>>> (i.e. the same Y instance)
>>>>>
>>>>> 2) Find all X's having a Y with z.zAttrib = 'foo' AND
>>>>> having another Y with z.zAttrub = 'bar'
>>>>>
>>>>> (i.e. two Y instances)
>>>>>
>>>>> In case 1) only Z needs 2 SQL aliases.
>>>>> In case 2) Y and Z both need 2 SQL aliases.
>>>>>
>>>>> (I just saw your response...)
>>>>>
>>>>> Phil
>>>>>
>>>>>> hi phil,
>>>>>>
>>>>>> you're right, both B and C must have in your case.
>>>>>> so the alias should affect all segements of the path, not only the
>>>>>> last as it
>>>>>> does now. but i'm not sure if this is always the case ?
>>>>>>
>>>>>> jakob
>>>>>>
>>>>>> Phil Warrick wrote:
>>>>>> > Hi Jakob,
>>>>>> >
>>>>>> > I've run into a difficulty using aliases on a model with a path
>>>>>> > involving a 1-M followed by a 1-1 association, as in:
>>>>>> >
>>>>>> > A-1------M-B-1------1-C
>>>>>> >
>>>>>> > I want to find all A's having a B with c.cAttrib = 'foo1' AND
>>>>>> another B
>>>>>> > with c.cAttrib = 'foo2'
>>>>>> >
>>>>>> > I use the following code:
>>>>>> >
>>>>>> > Criteria crit1 = new Criteria();
>>>>>> > crit1.setAlias("alias1");
>>>>>> > crit1.addEqualTo("allBs.c.cAttrib", new String("foo1"));
>>>>>> >
>>>>>> > Criteria crit2 = new Criteria();
>>>>>> > crit2.setAlias("alias2");
>>>>>> > crit1.addEqualTo("allBs.c.cAttrib", new String("foo2"));
>>>>>> >
>>>>>> > crit1.addAndCriteria(crit2);
>>>>>> > > Query query = new QueryByCriteria(A.class, crit1);
>>>>>> >
>>>>>> >
>>>>>> > The following SQL is generated by ojb (assume that the class 'X'
>>>>>> > corresponds to table 'XTABLE')
>>>>>> >
>>>>>> > SELECT A0.ID FROM ATABLE A0,BTABLE A1,CTABLE A2,CTABLE A3 WHERE
>>>>>> > A1.C_ID=A2.ID AND A1.C_ID=A3.ID AND
>>>>>> > A0.ID=A1.A_ID AND
>>>>>> > (( A2.CODE = ? ) AND (A3.CODE = ? ))
>>>>>> >
>>>>>> > I believe that both the B _and_ C tables must have two SQL
>>>>>> aliases each
>>>>>> > to correctly complete the query, as in:
>>>>>> >
>>>>>> > SELECT A0.ID FROM ATABLE A0,BTABLE A1,CTABLE A2,CTABLE
>>>>>> A3,BTABLE A4 WHERE
>>>>>> > A1.C_ID=A2.ID AND A4.C_ID=A3.ID AND
>>>>>> > A0.ID=A1.A_ID AND A4.ID=A1.A_ID
>>>>>> > (( A2.CODE = ? ) AND (A3.CODE = ? ))
>>>>>> >
>>>>>> > (Perhaps there is better SQL possible than this?)
>>>>>> >
>>>>>> > I've been looking carefully at
>>>>>> > org.apache.ojr.broker.accesslayer.sql.SqlQueryStatement.java
>>>>>> >
>>>>>> > and was wondering if you could suggest the best approach to
>>>>>> correctly
>>>>>> > addressing this limitation.
>>>>>> >
>>>>>> > Thanks,
>>>>>> >
>>>>>> > Phil
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> ---------------------------------------------------------------------
>>>>>> > To unsubscribe, e-mail: [EMAIL PROTECTED]
>>>>>> > For additional commands, e-mail: [EMAIL PROTECTED]
>>>>>> >
>>>>>> >
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>>>>>> For additional commands, e-mail: [EMAIL PROTECTED]
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>>>>> For additional commands, e-mail: [EMAIL PROTECTED]
>>>>>
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>>>> For additional commands, e-mail: [EMAIL PROTECTED]
>>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>>> For additional commands, e-mail: [EMAIL PROTECTED]
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
