Re: [Modeling-users] Limit

[Sebastien B. <sbi...@us...>]

        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.