Re: [Modeling-users] Limit

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

        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.