Workaround in paginate for databases without OFFSET (MSSQL)
Joel Pearson
that use either MySQL or Microsoft SQL Server, as specified by the customer. So
far, all of the code has worked well on both databases, which has been a huge
time saver for me.
I recently tried out the @paginate() decorator, and while I was pleased with
the results, I quickly discovered that it does not work with MSSQL, raising the
following exception:
InvalidRequestError: MSSQL does not support LIMIT with an offset
I've come across this sort of limitation before, and the solutions tend to be
either inefficient or complicated. For my needs, though (paging through less
than 100 records), an inefficient solution would work just fine. Fortunately, I
was able to create a workaround with just a few lines of code and a line in the
configuration file.
First, I added a new variable in the [global] section of my dev.cfg file:
paginate.simulate_offset=True
I then modified paginate.py in my turbogears installation. By default,
@paginate() works the same as before, making use of both "limit" and "offset"
in the database (or their equivalents). But if the variable
"paginate.simulate_offset" exists in the config file and evaluates to True,
then @paginate() uses "limit" in the database ("TOP" in MSSQL), but simulates
"offset" by skipping over the results until it reaches the requested records.
Obviously, paging through a thousand records would be slow, but it's plenty
fast for the small result sets I use. And in my situation, a page that loads
slowly is better than a page that never loads at all. I realize, however, that
in other situations it would be better to have one page "crash" rather than
overload the database-- that's why this workaround is off by default. (I
briefly considered naming the config file variable
"paginate.I_promise_not_to_complain_if_paginate_runs_slowly", in order to
emphasize the trade off, but that seemed like too much typing. ;-)
It would be very helpful to me if this patch were accepted. While I've only
tried it with SQLAlchemy and MSSQL, I expect it to work with SQLObject as well.
In addition, it should allow SQLALchemy users to use @paginate() with Access
and MaxDB databases, which also lack support for "offset".
I attempted to open a ticket in Trac, but it was rejected by Akismet. I'll
attach the patch in case someone else wants to try their hand at opening a ticket.
- Joel
--
Joel Pearson
Diez B. Roggisch
ORACLE doesn't even know limit - you can fake that using "ROWNUM < x", but the
offset won't work either.
So I'm in favor of making the use of limit + offset configurable. But actually
turn them off totally I'd prefer, because of above mentioned limits of other
DBs.
Diez
Jorge Godoy
> ORACLE doesn't even know limit - you can fake that using "ROWNUM < x", but
> the offset won't work either.
>
> So I'm in favor of making the use of limit + offset configurable. But
> actually turn them off totally I'd prefer, because of above mentioned
> limits of other DBs.
But please, make it simple to turn it on so that the DBs that support that
feature can benefit of it.
Maybe a special case for Oracle that would use the "rownum < x" idiom would be
better than having it off...
--
Jorge Godoy <jgo...@gmail.com>
Diez B. Roggisch
> Em Tuesday 30 October 2007 07:49:12 Diez B. Roggisch escreveu:
> > ORACLE doesn't even know limit - you can fake that using "ROWNUM < x",
> > but the offset won't work either.
> >
> > So I'm in favor of making the use of limit + offset configurable. But
> > actually turn them off totally I'd prefer, because of above mentioned
> > limits of other DBs.
>
> But please, make it simple to turn it on so that the DBs that support that
> feature can benefit of it.
The default would certainly be "on", to prevent breakage.
> Maybe a special case for Oracle that would use the "rownum < x" idiom would
> be better than having it off...
certainly not! TG uses SO and SA to abstract from that. Introducing
DB-depended code would have to go in there, if anywhere.
Apart from that, it's not much use emulating the offset whilst creating a
limit like above: cursors are lazy. So iterating to the point where the
pagination is supposed to start and then fetching one page has the exact same
cost as not applying any limit at all.
the _real_ benefit is the offset - which lets the DB discard the first n
entries. But this isn't supported by quite a few DBs.
Diez
Paul Johnston
> But please, make it simple to turn it on so that the DBs that support that
> feature can benefit of it.
The default would certainly be "on", to prevent breakage.
BTW, someone proposed a patch to let SA/MSSQL fake limit/offset, it was based on the Oracle approach. I haven't applied the patch as yet, but it will happen eventually.
Paul
Florent Aide
> I attempted to open a ticket in Trac, but it was rejected by Akismet. I'll
> attach the patch in case someone else wants to try their hand at opening a ticket.
>
> - Joel
Joel,
could you try again to create the ticket ? We changed the antispam
settings a bit and added some more training to the bayesian filters...
I hope it works now :)
Cheers,
Florent.
Joel Pearson
> could you try again to create the ticket ? We changed the antispam
> settings a bit and added some more training to the bayesian filters...
> I hope it works now :)
Florent -
When I tried again this morning, it said, "unable to connect to
database", and just now the error was "Submission rejected as
potential spam". I'll try again later.
- Joel
Joel Pearson
> > > But please, make it simple to turn it on so that the DBs that support that
> > > feature can benefit of it.
>
> > The default would certainly be "on", to prevent breakage.
>
> Sounds like a good feature. I'd suggest off by default. If it's on by
> default, I expect few people would turn it off when their DB does support
> limit/offset.
On by default, or off by default? It seems like it should ideally be
on by default for databases that need it, and off by default for the
rest, with the ability to override the default by setting a variable
in the config file.
I've attempted to implement that strategy by inspecting the
sqlobject.dburi and sqlalchemy.dburi config variables the first time
that @paginate() is used, and extracting the database name. If the
name matches a hard-coded list of databases that don't implement
offset, then the simulate_offset code is used, and a warning is
logged, "simulating OFFSET, paginate may be slow". The
paginate.simulate_offset variable, however, can be set to True or
False to override the defaults.
I briefly scanned through both SQLAlchemy (trunk) and SQLObject
database driver code, and made lists of which drivers appear not to
support offset. The results aren't always what you'd expect, because
some drivers (like Oracle in SQLA) emulate offset even though the
database doesn't support it. I made two lists, rather than one,
because for a given database, one driver might emulate offset while
another driver doesn't. The lists I came up with are:
_so_no_offset = 'mssql maxdb sybase'.split()
_sa_no_offset = 'mssql maxdb access'.split()
If anyone has any additions or corrections to these lists, please let
me know. For that matter, if anyone sees any other flaws in this
scheme, let me know that, too!
> BTW, someone proposed a patch to let SA/MSSQL fake limit/offset, it was
> based on the Oracle approach. I haven't applied the patch as yet, but it
> will happen eventually.
I believe that patch only supports SQL Server 2005, so SQL Server 2000
installations will continue to need a workaround.
- Joel
Joel Pearson
> settings a bit and added some more training to the bayesian filters...
> I hope it works now :)
Florent -
Success at last!
[PATCH] Workaround in paginate for databases without offset
http://trac.turbogears.org/ticket/1601
- Joel
Florent Aide
> > could you try again to create the ticket ? We changed the antispam
> > settings a bit and added some more training to the bayesian filters...
> > I hope it works now :)
>
> Florent -
>
> Success at last!
:-)