I'm developing a TurboGears / SQLAlchemy application with multiple deployments
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
Index: paginate.py
===================================================================
--- paginate.py (revision 3580)
+++ paginate.py (working copy)
@@ -200,7 +200,15 @@
# we replace the var with the sliced one
endpoint = offset + limit_
log.debug("slicing data between %d and %d", offset, endpoint)
- output[var_name] = var_data[offset:endpoint]
+ if turbogears.config.get('paginate.simulate_offset', False):
+ var_data_iter = iter(var_data[:endpoint])
+ # skip over the number of records specified by offset
+ for i in range(offset):
+ var_data_iter.next()
+ # return the records that remain
+ output[var_name] = list(var_data_iter)
+ else:
+ output[var_name] = var_data[offset:endpoint]
return output
return decorated
