Re: [Modeling-users] Limit

[Mario R. <ma...@ru...>]

> NB: Postgresql documentation says that neither LIMIT nor OFFSET are  
> part of
>     SQL92, and that there is no ANSI definition for these features.
>
> By the way, I finally searched to find out how this could be done,  
> since
> this request shows up persistently from time to time --I guess the  
> idea is
> to make it possible to present different pages in batches in a GUI.
>
> * Postgresql has  LIMIT # [OFFSET #]
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql- 
> select.html

Ah, great, i was not aware of these 2... i'm pretty sure they are not  
supported
by mssql, but have no idea about others.

>   requiring that ORDER BY is set if you want consistent batches  
> between two
>   selects
>
> * MySQL offers the very same syntax
>   http://www.mysql.com/doc/en/SELECT.html
>
>   even if it's not clear to me if an ORDER BY clause is also required  
> for
>   consistency between consecutive queries, but a few tries will show  
> (I'll
>   appreciate if someone with a mysql server at hand could try and  
> report)

I would say yes, as these would be logically separate queries (possibly  
with
other different queries ove the same connection.

> * Oracle defines the pseudo-column ROWNUM
>
> http://forums.devshed.com/archive/5/2002/11/3/47157
> http://www.orafaq.net/glossary/faqglosr.htm
> http://gethelp.devx.com/techtips/oracle_pro/10min/10min1200/10min1200- 
> 3.asp
>
>   but it has a different semantics, since the pseudo-column is filled  
> in
>   *before* any orderings --the last url gives an easy solution for  
> solving
>   this.
>
> [and there are some other comments at
>  http://www.experts-exchange.com/Databases/Q_20307165.html]
>
>
> So it should be possible to modify the FetchSpecification API:
>
>   instead of (unimplemented) setFetchLimit(self, limit) we could have
>   setFetchLimit(self, limit, offset=0). Then you'll be on your own to
>   iterate on the result sets given that you previously queried the  
> select
>   count.

Yes, this would be fine, but will not work or Oracle?
Or an Oracle layer will automatically manage limit and offset, mapping
them to some thing like "where rownum>x and rownum<y" ?

> Mario answered:
>> [...] would be really nice to have a generic declarative way for this
>> (essentially cursors, or paging, but as far the client app code is
>> concerned, there should be no worries about maintaining state
>> information).
>
>   Yes it would be nice, and not very hard to code. However I can't see  
> any
>   object in the framework where it could make sense to add such a
>   feature. It would be the responsability for an object on top of an
>   EditingContext, but there's none for the moment being.

Well, i think proposal above is very close to this. But, to clarify, I  
was just
imagining some qualifier extension to be able to set a chunkBySize=N
and then be able to say getNextChunk or getPreviousChunk or  
getChuckNum...
A setFetchLimit (or setFetchScope or setFetchSlice ...) will provide the
basics for this.

BTW, how is the ORDER BY functionality exposed vis the fetch and
qualifier interface?

>
> Back to the fetchLimit(): what do you all think about the proposal?
>
> Implementing it for Postgresql and MySQL wouldn't be that hard, except  
> that
> it will require support for SortOrderings on the SQL side --not done  
> yet.

It would be very good to have, but maybe with a different name (limit  
is too limited ;),,
and adding an page, such as setFetchSlice(self, limit, offset=0,  
page=0). This
will allow not only getting a chunk, but also jumping to subsequent (or  
previous)
chunks --> page would be the chunk index, thus, if we ask for

setFetchSlice(self, 10, offset=7, page=2)

on a resultset of total 100 items, we will get the 10 items in the 3rd  
page (if
we alas start from a page 0), i.e. from 27 to 36.

Cheers, mario