projectwonder/wonder — pull request #213 by kierankelleher (2012-06-08 04:13:28+00:00)
Title: ERXFetchSpecification fetchRange support

Don't actually pull this into integration. The objective of this pull request is to get feedback on whether this is fundamentally something we should even consider doing in public Wonder. Is it something useful?

Setting an NSRange of { 100, 50 } on an ERXFetchSpecification and fetching will generate SQL something like this in MySQL

SELECT ..... LIMIT 100, 50;

.. that is, return the 50 records from record 101 thru 150 in what would otherwise have been a large resultset.

Implementing this in other PlugIns should be pretty easy for databases that support. The intent is not to replace ERXFetchSpecificationBatchIterator but to have another mechanism available to directly batch in SQL if desired.

Comments, opinions, criticism on its usefulness and whether we should include this in Wonder is welcome :-)

Commit comments:
----------------------------

Adds fetchRange option to ERXFetchSpecification and fetchRange support to _MySQL.Plugin.MySQLExpression.

ERXFetchSpecificationBatchIterator in its current incarnation works fine for batching where a reference to the
batch iterator is maintained. However in direct actions, as used inside current ERXRestFetchSpecification.results(...)
method, a new ERXFetchSpecificationBatchIterator is created on each request resulting in the entire population
of primary keys being fetched and then the batch of EOEnterpriseObjects for the primary key range is fetched.

Having a fetchRange option in ERXFetchSpecification, with support in SQL PlugIns, enables the same range of objects
to be returned in one fetch in a stateless request. This is useful for stateless requests that request a range of
a huge population of objects. This feature may also not have the ERXFetchSpecificationBatchIterator
limitation of not supporting multi-column primary keys. Another advantage is that it has no limit to the total count of
the population of objects form which the range if fetched whereas ERXFetchSpecificationBatchIterator does have a practical
limit associated with ther total number of primary keys that can or should be fetched.

A disadvantage of direct SQL batch fetch is that for some complex qualifiers that return duplicate rows, the DISTINCT property
must be set to true to get accurate results. Having DISTINCT and ORDER BY in the same SQL statement apparently causes
problems in some database platforms such as Oracle that is taken into account by ERXFetchSpecificationBatchIterator
(see ERXSQLHelper.newSQLHelper(entity).shouldPerformDistinctInMemory(pkFetchSpec)) reference in primaryKeys() method
of ERXFetchSpecificationBatchIterator.