Bruno René Santos | 5 Jan 01:14 2011
Picon

Left Outer Joins without expressions

Hello all,

Is there any way to create a left outer join without using an actual 
path on the query expressions? I tried something like this:

query.addPrefetch(HoTiposFechoContas.TO_HO_TABLE_PROPERTY + 
"+").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);

but cayenne raises an exception because of the concatenated '+' sign.

Regards
Bruno
--

-- 
Bruno René Santos | brunorene <at> holos.pt <mailto:brunorene <at> holos.pt> | 
Gestor de Projectos | Analista | Programador | Tech Hunter

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 Carlos Fazenda by telephone on +351 210 438 686

(Continue reading)

Andrus Adamchik | 5 Jan 19:20 2011

Re: Left Outer Joins without expressions

IIRC in 3.0 PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS generates an outer join by default (???)

Andrus

On Jan 5, 2011, at 2:14 AM, Bruno René Santos wrote:

> Hello all,
> 
> Is there any way to create a left outer join without using an actual path on the query expressions? I tried
something like this:
> 
> query.addPrefetch(HoTiposFechoContas.TO_HO_TABLE_PROPERTY + "+").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
> 
> but cayenne raises an exception because of the concatenated '+' sign.
> 
> Regards
> Bruno
> -- 
> Bruno René Santos | brunorene <at> holos.pt <mailto:brunorene <at> holos.pt> | Gestor de Projectos | Analista
| Programador | Tech Hunter
> 
> 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
(Continue reading)

Andrus Adamchik | 5 Jan 19:29 2011

Re: transactions vs iterated query

Haven't thought about this scenario deeply... How about this:

In the simplest case you can keep your changes in the DataContext while iterating over the result
(DataContext by itself is not permanently bound to a transaction). And then commit them after iteration
is finished. This will work if only some objects have changes and you have enough memory to keep them in a DataContext.

Or if you need to flush data to DB in smaller chunks without committing a DB transaction, you can still do it by
temporary re-bidning your own transaction to the current thread for the duration of commit, and then
restoring it to the iterated query transaction when update is done.

Andrus

On Dec 29, 2010, at 3:25 PM, Hans Pikkemaat wrote:
> Hi,
> 
> I'm using an iterated query to process a huge amount of data which cannot be loaded at once.
> This query creates its own transaction and binds it to the current thread.
> 
> This means that when I process the data I received from the iterated query all queries use
> the transaction created by the iterated query.
> 
> So any updates executed while the iterated query is running will be committed when the
> iterated query (actually the Result Iterator) is closed. So the iterator is committing the
> updates for me what I don't want.
> 
> I know I can create my own transaction but this doesn't help me. I want to be able to
> create a transaction and within that transaction I want to run the iterated query and
> while in the query I want to do updates which are committed when I commit my own
> transaction.
> 
(Continue reading)

Bruno René Santos | 6 Jan 00:37 2011
Picon

Re: Left Outer Joins without expressions

Hello Andrus,

In Mysql the generated join is:

... from table1 join table2

which is an inner join... I needed a

... from table1 left join table2

Regards
Bruno

Em 05-01-2011 18:20, Andrus Adamchik escreveu:
> IIRC in 3.0 PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS generates an outer join by default (???)
>
> Andrus
>
> On Jan 5, 2011, at 2:14 AM, Bruno René Santos wrote:
>
>> Hello all,
>>
>> Is there any way to create a left outer join without using an actual path on the query expressions? I tried
something like this:
>>
>> query.addPrefetch(HoTiposFechoContas.TO_HO_TABLE_PROPERTY + "+").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
>>
>> but cayenne raises an exception because of the concatenated '+' sign.
>>
>> Regards
(Continue reading)

Hans Pikkemaat | 6 Jan 09:48 2011
Picon

Re: transactions vs iterated query

Hi,

After some investigation it comes down to two problems

1)
An example.
I have 2 libraries. One library calls the other one.
The first one is using the iterated query to get some data. It will call the second
library to process the data.

This second library however is not aware of a iterated query being used and it
simply creates a new DataContext and will commit it when it sees fit. But it doesn't
get a new transaction as there is already one bound to the thread.

This would effectively mean that every application that uses cayenne has to check
the bound transaction and if it exists create a new one and restore it later
(assuming he wants its own transaction). This is a weird situation.

2)
An example.
I have 2 libraries. One library calls the other one.
The first one is setting a task record to 'in progress'. Then it calls the second
library. The second one is using an iterated query but it is wrapped in an Iterator
such that the caller doesn't see the details. This however means that when an
exception occurs that the ResultIterator is not closed. This means that the transaction
is not unbound and as such never committed. The commit on the trasaction created
by the calling library then also doesn't work.

Now you could say that this is a design flaw. I partly agree. I should not wrap the
ResultIterator in an iterator as this prevents access to the close method.
(Continue reading)

Andrus Adamchik | 6 Jan 10:08 2011

Re: transactions vs iterated query


On Jan 6, 2011, at 10:48 AM, Hans Pikkemaat wrote:

> One library calls the other one.
> The first one is using the iterated query to get some data. It will call the second
> library to process the data.

IMO this first library (iterator control code) should be the place that does transaction switching,
transparently to the second library. E.g. in lib1:

try {
  while(it.hasNext()) {
    Map nextRow = it.next();

    // save current tx in a local ivar
    try {
       // set current tx to NULL
       // call lib2
    }
    finally {
       // restore tx
    }

  }
}
finally {
   it.close();
}

> 
(Continue reading)

Andrus Adamchik | 6 Jan 10:13 2011

Re: Left Outer Joins without expressions

Which version of Cayenne are you using?

I am checking our unit tests (Cayenne 3.1, but this part hasn't changed from 3.0) including simple to-one
and to-many joint prefetching and both generate outer joins:

 SELECT DISTINCT t0.DATE_OF_BIRTH, t0.ARTIST_ID, t0.ARTIST_NAME, t1.ESTIMATED_PRICE,
t1.PAINTING_DESCRIPTION, t1.PAINTING_TITLE, t1.ARTIST_ID, t1.GALLERY_ID, t1.PAINTING_ID FROM
ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) 

SELECT t0.ARTIST_ID, t0.PAINTING_ID, t0.GALLERY_ID, t0.PAINTING_DESCRIPTION,
t0.ESTIMATED_PRICE, t0.PAINTING_TITLE, t1.ARTIST_NAME, t1.DATE_OF_BIRTH, t1.ARTIST_ID FROM
PAINTING t0 LEFT JOIN ARTIST t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) ORDER BY t0.PAINTING_ID 

Andrus

On Jan 6, 2011, at 1:37 AM, Bruno René Santos wrote:

> Hello Andrus,
> 
> In Mysql the generated join is:
> 
> ... from table1 join table2
> 
> which is an inner join... I needed a
> 
> ... from table1 left join table2
> 
> Regards
> Bruno
> 
(Continue reading)

Bruno René Santos | 6 Jan 10:41 2011
Picon

Re: Left Outer Joins without expressions

Hello Andrus,

I am so sorry you are right Cayenne is generating outer joins. The 
problem was that i was checking one of the prefetches for a Many-to-one 
relationship where the second query uses an inner join to join the 
related table with the root table.

Thank you
Bruno

Em 06-01-2011 09:13, Andrus Adamchik escreveu:
> Which version of Cayenne are you using?
>
> I am checking our unit tests (Cayenne 3.1, but this part hasn't changed from 3.0) including simple to-one
and to-many joint prefetching and both generate outer joins:
>
>   SELECT DISTINCT t0.DATE_OF_BIRTH, t0.ARTIST_ID, t0.ARTIST_NAME, t1.ESTIMATED_PRICE,
t1.PAINTING_DESCRIPTION, t1.PAINTING_TITLE, t1.ARTIST_ID, t1.GALLERY_ID, t1.PAINTING_ID FROM
ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
>
> SELECT t0.ARTIST_ID, t0.PAINTING_ID, t0.GALLERY_ID, t0.PAINTING_DESCRIPTION,
t0.ESTIMATED_PRICE, t0.PAINTING_TITLE, t1.ARTIST_NAME, t1.DATE_OF_BIRTH, t1.ARTIST_ID FROM
PAINTING t0 LEFT JOIN ARTIST t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) ORDER BY t0.PAINTING_ID
>
> Andrus
>
> On Jan 6, 2011, at 1:37 AM, Bruno René Santos wrote:
>
>> Hello Andrus,
>>
(Continue reading)

Hans Pikkemaat | 6 Jan 14:21 2011
Picon

Re: transactions vs iterated query

Hi,

That sounds logical but the issue could be that the calling party is simply not aware that the
called library is using cayenne.

This would mean that an application which is calling some library always should clear
(and keep) the transaction. This is not very logical.

And what about the other example with the exception? Is there a way to call the close
method on the result iterator without exposing the ResultIterator to the calling party?

tx

Hans

On 1/6/11 10:08 AM, Andrus Adamchik wrote:
> On Jan 6, 2011, at 10:48 AM, Hans Pikkemaat wrote:
>
>> One library calls the other one.
>> The first one is using the iterated query to get some data. It will call the second
>> library to process the data.
> IMO this first library (iterator control code) should be the place that does transaction switching,
transparently to the second library. E.g. in lib1:
>
> try {
>    while(it.hasNext()) {
>      Map nextRow = it.next();
>
>      // save current tx in a local ivar
>      try {
(Continue reading)

Andrus Adamchik | 11 Jan 12:41 2011

Re: deprecate XML package in 3.1

Hi Adrian,

Sorry for a belated reply and thanks for describing your XML serialization scenario. I think it sorta
proves my point that current serialization framework only works in very special cases (e.g. in your case
we have a single entity per serialization run, no handling of relationships, and no real-time
performance requirements). And this particular special case is fairly straightforward to build from scratch.

Cheers,
Andrus

On Dec 4, 2010, at 5:08 PM, Adrian Wiesmann wrote:
> Hi there
> 
>> the issue in the subject is being currently discussed by Cayenne developers (see the message from dev
below). Anybody's using XML serialization now? If so I'd like to hear how you deal with all the
deficiencies of this package?
> 
> Yes, actually both, serialisation and deserialisation. We thought it is a nice way to have snapshot
files. A snapshot file is just a zipped container containing a manifest file and one or multiple files
containing data. One table represents a single file, XML formatted.
> 
> Since all our tables have UUID as primary keys snapshot files make it possible to allow road warriors to
easily take data on the road and later check it back into the main database...
> 
> It is quite convenient to have some support from Cayenne for this.
> 
> Cheers,
> Adrian
> 

(Continue reading)


Gmane