Thread: [Modeling-users] Limit

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hi all :) 

I'm just wondering how can i limit the result fetched by 
a qualifier. I think i forgot how this work :) 

Limit to a fixed maximum number? Don't think you can (for Sebastien to 
confirm)
unless there is hidden support for specifying TOP N on SELECTs (which i 
think
is not standard SQL?). And, how would you get the next batch then?

You probably have to do a count, and if too many you modify the fetch 
qualifier
to be more restrictive. But, i guess it would be you who would have to 
program
such an algorithm for you app -- 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).

mario

> Hi all :)
>
>
> I'm just wondering how can i limit the result fetched by
> a qualifier. I think i forgot how this work :)
>
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: SlickEdit Inc. Develop an edge.
> The most comprehensive and flexible code editor you can use.
> Code faster. C/C++, C#, Java, HTML, XML, many more. FREE 30-Day Trial.
> www.slickedit.com/sourceforge
> _______________________________________________
> Modeling-users mailing list
> Mod...@li...
> https://lists.sourceforge.net/lists/listinfo/modeling-users

> Limit to a fixed maximum number? Don't think you can (for Sebastien to
> confirm) unless there is hidden support for specifying TOP N on SELEC=
Ts
> (which i think is not standard SQL?). And, how would you get the next=
 batch
> then?

  Sorry soif, Mario's right... there's currently no way to limit or bat=
ch
  the fetch. However EditingContext.objectsCountWithFetchSpecification()
  will tell you how many objects (in fact, the upper limit) you'll get =
if
  you send the same parameters to objectsWithFetchSpecification().

  More on this: if someone knows some standard SQL for doing this, I'm
  interested, it would for sure make the release-time for such a featur=
e,
  if requested, closer than it actually is.

> 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).

Sure. Know what? It's on the TODO list :)

        Cheers,

-- S=E9bastien.

PS: I'll be off from now until tomorrow evening, but you can still expe=
ct
    v0.9 being released this week-end.

>=20
> > Hi all :)
> >
> >
> > I'm just wondering how can i limit the result fetched by
> > a qualifier. I think i forgot how this work :)
> >
> >

On Fri, Feb 21, 2003 at 04:24:53PM +0100, Sebastien Bigaret wrote:

One of my favorite game, awking dead thread :) 

Apologize for this long time answer. 

> > Limit to a fixed maximum number? Don't think you can (for Sebastien to
> > confirm) unless there is hidden support for specifying TOP N on SELECTs
> > (which i think is not standard SQL?). And, how would you get the next batch
> > then?
> 
>   Sorry soif, Mario's right... there's currently no way to limit or batch
>   the fetch. However EditingContext.objectsCountWithFetchSpecification()
>   will tell you how many objects (in fact, the upper limit) you'll get if
>   you send the same parameters to objectsWithFetchSpecification().

Yes in fact, i ever know this. 

>   More on this: if someone knows some standard SQL for doing this, I'm
>   interested, it would for sure make the release-time for such a feature,
>   if requested, closer than it actually is.

After some googling, i found nothing. In fact 'LIMIT' is a SQL92
keyword, but i found nowhere the SQL92 def of this :( , anyways i
use it on a couple of DB before and i 've never see something that
doesn't support it. 

Bye Bye .. :) 

== 

        Hi,

Soaf wrote:
> >   More on this: if someone knows some standard SQL for doing this, =
I'm
> >   interested, it would for sure make the release-time for such a
> >   feature, if requested, closer than it actually is.
>=20
> After some googling, i found nothing. In fact 'LIMIT' is a SQL92 keyw=
ord,
> but i found nowhere the SQL92 def of this :( , anyways i use it on a
> couple of DB before and i 've never see something that doesn't suppor=
t it.

NB: Postgresql documentation says that neither LIMIT nor OFFSET are par=
t 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=3D7.3&idoc=3D1&file=3Ds=
ql-select.html

  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 f=
or
  consistency between consecutive queries, but a few tries will show (I=
'll
  appreciate if someone with a mysql server at hand could try and repor=
t)

* 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 solv=
ing
  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=3D0). Then you'll be on your own to
  iterate on the result sets given that you previously queried the sele=
ct
  count.

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.

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 ye=
t.

-- S=E9bastien.

> 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

        Hi all,

  I've created a new branch today at noon, containing a first
  implementation for:

    - sorting objects w/ FetchSpecification.sortOrderings() at the SQL
      level
=20=20=20=20
    - limiting the number of objects fetched by
      EC.objectsWithFetchSpecification()

  after the different comments and requests that were submitted here.

  It is available for the postgresql and mysql adaptor layers.

Status of the proposal:
-----------------------

     The sorting mechanism and the slicing scheme are for the moment
     proof-of-concept implementation. They both suffer from different
     drawbacks (described below) that needs to be eliminated before this
     is merged in the main trunk. They will both probably needs more
     use-cases and trial-and-errors before we get to the final
     implementation.

How it works
------------

  Examples of use are given in tests/test_EditingContext_Global.py,
  methods test_17_fetchSpecWithSortOrderings() and
  test_18_fetchSpecWithFetchLimit(), from which the following examples
  are extracted.

  IMPORTANT NOTE: the test-db for Author_Books needs to be cleaned and
  --------------- repopulated with new values for the new tests to
                  pass.
                  Simply execute './test_EditingContext_Global.py -r'
                  and you'll be fine.

  * Ordering

    Basically, ordering a result consists in building one or more
    SortOrdering and giving it to the FetchSpecification:

      q=3DqualifierWithQualifierFormat('author.lastName !=3D NULL"')
      fs=3DFetchSpecification('Book', q)
      from Modeling.SortOrdering import \
           SortOrdering, compareDescending, compareCaseInsensitiveAscending
      so0=3DSortOrdering('author.lastName', compareDescending)
      so1=3DSortOrdering('price', compareDescending)
      so2=3DSortOrdering('title', compareCaseInsensitiveAscending)
      fs.setSortOrderings([so0, so1, so2])
      ec=3DEditingContext()
      objs=3Dec.objectsWithFetchSpecification(fs)

    Orderings are applied in the given order.

  * Fetch limit / slicing

    FetchSpecification now comes with an enhanced API, with the
    following methods:

      - setFetchSlice(limit, offset=3D0, page=3D1)

         Parameters offset and page are mutually _exclusive_ and they
         should be self explicative.

         Please note: offset starts at zero (0), page at one (1)

      - fetchSlice(): returns a mapping with keys 'limit' and 'offset'

      - fetchLimit(limit): alias for setFetchSlice(limit, offset=3D0)

      - getNext/PreviousSlice(): only available if setFetchSlice() was
          previously called w/ parameter 'page'
          Raises ValueError otherwise, or if you request the
          previous page when you already are at page 1.
          It simply recalcultes the offset, given the current limit.=20

    Example of use --for full details cf.test_18_fetchSpecWithFetchLimit

        q=3DqualifierWithQualifierFormat('author.lastName !=3D NULL"')
        fs=3DFetchSpecification('Book', q)
        so1=3DSortOrdering('id', compareAscending)
        fs.setSortOrderings([so1])         ## !!! Important: see below !!!

        fs.setFetchSlice(2, page=3D1)
        objs=3Dec.objectsWithFetchSpecification(fs) # first page
        # [...]
        fs.getNextSlice()
        objs=3Dec.objectsWithFetchSpecification(fs) # second page
        # etc.

    You know that you reached the last page when you get zero objects.
    EC.objectsCountWithFetchSpecification() will give you an idea of how
    many pages you can possibly get.
=20=20=20=20

Known problems / possible evolutions / open questions
-----------------------------------------------------

  - defining a SortOrdering this way seems a bit painful. I wonder
    whether we should design a little parser in FS.setSortOrderings()
    for supplying them as strings, maybe something like:
    "author.lastName asc, price desc, title iasc"

  - there is no default order applied to the result set when using
    fetchSlice(); it is your responsability to specify a specific order
    [such as: SortOrdering('id', compareAscending)] to your
    FetchSpecification before requesting a first batch and the
    subsequents ones, or you'll probably get inconsistent subsets.

    This might change in the future. E.g. a default ordering on the
    primary key will probably be added if no other orderings is
    specified, or maybe it could be added to any existing orderings as
    well.

  - Sorting does NOT work when inheritance is taken into account,
    i.e. if you set FetchSpecification's 'isDeep' flag to true --you'll
    get n*limit results in each batch, n being the number of
    subclasses(+1) of the current Entity. Moreover the batches will not
    be sorted as a whole, but they will be made of sorted subsets coming
    from the different sub-entities.

  - if you fetch slices against an EC that has some inserted and/or
    deleted objects (for the entity you're fetching):

      - deleted objects will not appear, so you may get less objects
        than expected,

      - inserted objects will appear in each batch. This is a problem in
        itself, and a second one because in that case, you'll *never*
        get zero object after e.g. fs.getNextSlice()

  The last two problems (inheritance & inserted objects) are real
  problems, not easy to solve. I can vaguely see some different way to
  solve them but this would require a big refactor of the fetching
  process, or a second fetching scheme for slicing fetches only. For the
  moment I'm not positively sure that this is worth the effort ; however
  I'm also reluctant in leaving that as-is since it would mean two
  different semantics for ec.objectsWithFetchSpecification() depending
  on the FetchSpecification itself.

How to get it:
-------------

I won't make any public release for this, please use cvs. To check-out a
new copy of this branch, you'll have to:

$ cvs -d:pserver:ano...@cv...:/cvsroot/modeling login
  (empty password)

$ cvs -d:pserver:ano...@cv...:/cvsroot/modeling checkout -=
r brch-0_9pre5-SQL_SortOrdering_and_LIMIT ProjectModeling

or use 'cvs update -r brch-0_9pre5-SQL_SortOrdering_and_LIMIT' in your
working copy if you've already checked out the main trunk.

Future
------

  I'll probably not work on this until I get some feedbacks. BTW none of
  these features will be integrated before 0.9 is released
  (feature-freeze until then!).

    Please share any questions/critics/comments/proposals/etc. this
  proposal inspires you --even if you do not have the time to actually
  test it, you may have interesting comments for the discussion :)

        Enjoy sorting & happy slicing!

-- S=E9bastien.

On Sat, Mar 01, 2003 at 02:15:07PM +0000, Sebastien Bigaret wrote:

[snip]

> * 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)

This is right in fact in mysql if you don't have a 'ORDER BY' clause the server 
will use the insert ordering.

[snip]

> 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.

Yeah this sound great. 

Bye Bye :)