Hi,
What I can see when I use paging in combination with SQLTemplate is this:
Cayenne first runs the main SQLTemplate query which is stored in memory
When I get the first page it determines the key values of the main query
which it then
uses in a new query which will return the main table plus the detail
table data.
This will produce the main table object through which the detail table
is accessible.
The problem here is that the key of the main table is used only. The
SQLTemplate query was manually
constructed and does a query on the main table and a left join to the
detail table so this will produce
a duplicate key value where a main table record has 2 related detail
table records.
This doesnt have to be a problem, actually the query does return the
number of records used as page
size. But internally in cayenne something weird happens. Somehow the
duplicate records are removed
and the IncrementalFaultList.checkPageResultConsistency method throws an
exception for this.
Because the main query returns the main object but also the detail
object I find it strange
that the query generated for the page only uses the main table key. I
would expect that
it also would use the key of the detail table.
An example. Say I have a main table key 1 and related detail records
with key 1, 2 and 3.
Say I run the SQLTemplate which returns key 1 but only key 1 and 2 for
the detail table.
The page query will now run for all detail records and return all
records which I did not
request.
From this I'm concluding that if an SQLTemplate is used it is not
usefull (read: faulty) to
include the detail table in this query. When paging is used all the
detail tables are automatically
queried.
If I write the main SQLTemplate query such it only returns the main
object then the
Exception does not occur.
My conclusion is then that if you want to use paging with SQLTemplate
the main
query should only return the main table. Prefetching will then return
ALL related
table records.
Hans
Andrus Adamchik wrote:
> Actually IncrementalFaultList iterator internally uses list.get(int)
> (as shown in the stack), so it seems to be the same thing as iterating
> with an index.
>
> Andrus
>
> On Nov 11, 2009, at 4:02 PM, Andrey Razumovsky wrote:
>
>
>> Could you try iterating through list using counter variable (e.g.
>> for (int i
>> = 0; i < list.size(); i++)) to see if it works?
>>
>> 2009/11/11 Hans Pikkemaat <h.pikkemaa..si-solutions.nl>
>>
>>
>>> Hi,
>>>
>>> This is the 'working' part
>>>
>>> System.out.println("Running with fetchlimit: " + pageSize);
>>> final DataContext dataContext = this.createDataContext();
>>>
>>> SQLTemplate query = new SQLTemplate(VersionedObjectImpl.class,
>>> sql);
>>> query.addPrefetch(_VersionedObjectImpl.ATTRIBUTES_PROPERTY);
>>>
>>> if (pageSize > 0) {
>>> query.setPageSize(pageSize);
>>> }
>>> List<VersionedObjectImpl> list =
>>> dataContext.performQuery(query);
>>>
>>> for (Iterator<VersionedObjectImpl> iterator = list.iterator();
>>> iterator.hasNext();) {
>>> this.show(iterator.next());
>>> }
>>>
>>> VersionedObjectImpl.class is the main table and the
>>> VersionedobjectImpl.ATTRIBUTES_PROPERTY
>>> is the relation name for the detail table.
>>>
>>> The sql provided is (my own made simple 'try out' query, the actual
>>> query
>>> is WAY more complex)
>>>
>>> select o.object_id as "OBJECT_ID", o.ancestor_path
>>> as "ANCESTOR_PATH", o.object_type as "OBJECT_TYPE",
>>> o.object_identifier as "OBJECT_IDENTIFIER",
>>> o.created_revision_id as "CREATED_REVISION_ID",
>>> o.deleted_revision_id as "DELETED_REVISION_ID",
>>> a.attribute_id
>>> as "attributes.ATTRIBUTE_ID", a.name as
>>> "attributes.NAME", a.type_name as "attributes.TYPE_NAME",
>>> a.string_value as "attributes.STRING_VALUE",
>>> a.integer_value
>>> as "attributes.INTEGER_VALUE", a.date_value as
>>> "attributes.DATE_VALUE" from rev_object o left join
>>> rev_attribute a
>>> on a.object_id=o.object_id
>>>
>>> If I run this without page size no problem.
>>> With page size set eg to 100 I get
>>>
>>>
>>> Exception in thread "main"
>>> org.apache.cayenne.CayenneRuntimeException:
>>> [v.3.0B1 Nov 09 2009 09:53:47] Some ObjectIds are missing from the
>>> database.
>>> Expected 100, fetched 85
>>> at
>>> org
>>> .apache
>>> .cayenne
>>> .access
>>> .IncrementalFaultList
>>> .checkPageResultConsistency(IncrementalFaultList.java:364)
>>> at
>>> org
>>> .apache
>>> .cayenne
>>> .access
>>> .IncrementalFaultList.resolveInterval(IncrementalFaultList.java:301)
>>> at
>>> org
>>> .apache
>>> .cayenne.access.IncrementalFaultList.get(IncrementalFaultList.java:
>>> 550)
>>> at
>>> org.apache.cayenne.access.IncrementalFaultList
>>> $1.next(IncrementalFaultList.java:467)
>>> at
>>> com
>>> .traserv
>>> .incrementalsupply
>>> .integration.demo.CayenneDemo.doit1a(CayenneDemo.java:188)
>>> at
>>> com
>>> .traserv
>>> .incrementalsupply
>>> .integration.demo.CayenneDemo.main(CayenneDemo.java:235)
>>>
>>>
>>> tx
>>>
>>> Hans
>>>
>>>
>>> Andrus Adamchik wrote:
>>>
>>>
>>>> You may have done that already in the previous messages, but could
>>>> you
>>>> give a snip of your SQLTemplate creation code here (just to make
>>>> sure I
>>>> understand all the settings used in this specific case). And also if
>>>> possible, SQL generated in the console for the initial query, and
>>>> then for
>>>> the failing page query?
>>>>
>>>> Thanks,
>>>> Andrus
>>>>
>>>>
>>>> On Nov 11, 2009, at 2:51 PM, Hans Pikkemaat wrote:
>>>>
>>>>
>>>>
>>>>
>>>>> Hi,
>>>>>
>>>>> I tried 3.0b but without effect.
>>>>>
>>>>> If I use an SQLTemplate in combination with setPageSize I also
>>>>> get the
>>>>> exception mentioned earlier:
>>>>>
>>>>> Exception in thread "main"
>>>>> org.apache.cayenne.CayenneRuntimeException:
>>>>> [v.3.0B1 Nov 09 2009 09:53:47] Some ObjectIds are missing from the
>>>>> database. Expected 100, fetched 85
>>>>> at org .apache .cayenne .access .IncrementalFaultList
>>>>> .checkPageResultConsistency(IncrementalFaultList.java:364)
>>>>> at org .apache .cayenne .access
>>>>> .IncrementalFaultList.resolveInterval(IncrementalFaultList.java:
>>>>> 301)
>>>>> at org .apache
>>>>> .cayenne
>>>>> .access.IncrementalFaultList.get(IncrementalFaultList.java: 550)
>>>>> at org.apache.cayenne.access.IncrementalFaultList
>>>>> $1.next(IncrementalFaultList.java:467)
>>>>> at com .traserv .incrementalsupply
>>>>> .integration.demo.CayenneDemo.doit1(CayenneDemo.java:136)
>>>>> at com .traserv .incrementalsupply
>>>>> .integration.demo.CayenneDemo.main(CayenneDemo.java:183)
>>>>>
>>>>> If I use a SelectQuery it works oke. But this is not an option
>>>>> for me
>>>>> because I cannot construct
>>>>> my query using SelectQuery because of its complexity.
>>>>>
>>>>> Any ideas?
>>>>>
>>>>> tx
>>>>>
>>>>> Hans
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>> --
>> Andrey
>>
>
>
This archive was generated by hypermail 2.0.0 : Fri Nov 13 2009 - 03:14:58 EST