cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Poulsen <mailingl...@nesluop.dk>
Subject Re: Limits & Joins on Oracle
Date Tue, 14 Jun 2011 17:38:35 GMT
Hi,

I know repeated column names are common ;) - I was just spelling out how
Oracle works with respect to them. - Hoping to shed some light on why it
isn't working currently.

Someone familiar with cayenne internals should chime in on whether / how it
is possible to alias the columns when running against an oracle db.

-- 
Chris


On Tue, Jun 14, 2011 at 7:18 PM, Bruno René Santos <brunorene@holos.pt>wrote:

> Hi Chris,
>
> Your solutions are in consonance with what I said in my mail. But having
> fields with the same name is quite common. For instants all our primary keys
> are named ID and for all our tables we always have columns like
> last_modified_at, created_at, last_modified_by or created_by which we use
> for logging purposes. So it would by critical for us to have this issue
> resolved. I am willing to give it a try if someone could point me on the
> right direction in terms of cayenne inner architecture of objects
>
> Regards
> Bruno
>
> Em 14-06-2011 17:55, Chris Poulsen escreveu:
>
>  Hi,
>>
>> We have been using the same pagination pattern on Oracle as Cayenne uses
>> without problems for quite some time (years). So I can confirm that it
>> looks
>> sane.
>>
>> Anyway looking at the query in CAY-1266, one thing sticks out - The inner
>> query returns both t0.docid and t1.docid, IIRC oracle will "translate" the
>> latter into "docid_1" if the inner sql is executed without the wrapper
>> clauses.
>>
>> It just doesn't make sense to return "docid" twice from the select clause
>> as
>> you can't really distinguish them in the surrounding sql anyway (but the
>> inner query is able to execute as valid sql due to the naming trick and as
>> it is the join column it doesn't matter if we're getting the t0 or t1
>> version here).
>>
>> The solutions I can come up with at the moment would be to alias the
>> columns
>> in a way that ensures unique column names: select ...., t0.docid t0_docid,
>> ..., t1.docid t1_docid FROM ... (That would make Oracle happy)
>>
>> - And then let the mapper figure out what goes where (if possible?) i.e.
>> mapping t1_docid to the T1Object.docid and t0_docid to the T0Object.docid
>>  -
>> Exactly what Brunos colleague suggests.
>>
>> Alternatively a quick partial solution is to leave out the
>> t1.<column_name>,
>> if: 1) it is a join column and 2) it has same name as t0.<column_name>  -
>> duplicated column names that are NOT joined on, will still give errors
>> with
>> this, but the mapping code would not need to adapt to the aliased column
>> names.
>>
>> Aliasing the columns (first suggestion) will be the most robust/correct
>> solution as queries returning tables with equivalently named non-join
>> columns would return incorrect results. Although the quick hack could be
>> used to figure out if the above analysis is correct ;)
>>
>> I hope this helps you guys figure out a solution to Oracle/fetch limit
>> thing.
>>
>>
>
> --
> Bruno René Santos | brunorene@holos.pt <mailto:brunorene@holos.pt> |
> Gestor de Projectos | Analista | Programador | Investigador
>
> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>
>
> This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.
> If you are not the intended recipient or the person responsible for
> delivering the email to the intended recipient, be advised that you have
> received this email in error and that any use, dissemination, forwarding,
> printing, or copying of this email is strictly prohibited. If you have
> received this email in error please notify Bruno René Santos by telephone on
> +351 210 438 686
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message