Re: Pagination problems using SQLAlchemy & MSSQL in Pylons - no OFFSET
Philip Jenvey
On Jul 9, 2007, at 9:37 PM, BruceC wrote:
>
> Hi all, I am using Pylons, & SQLAlchemy (via PYODBC) to connect
> through to an MSSQL database. In particular, I have been trying to use
> Christoph Haas' alternative Paginator (http://workaround.org/pylons/
> paginator/paginator.html) webhelper, which looks great.
>
> I can get it to show pagination links for a list of records coming
> from a mapped Class object, but when I try to use one of the
> pagination links, I get an error, because the pagination webhelper in
> Pylons sends a SELECT statement to the database, passing a LIMIT & and
> OFFSET value. Unfortunately, unlike many other database engines (eg
> MySQL) , SQL Server does not have OFFSET in it's SQL syntax.
>
> My implementation does need pagination, & I am stuck having to use SQL
> Server... Has anyone managed to get pagination going on Pylons using
> SQL Server as the DB engine?
>
> If so, can you explain how you got around the fact that MSSQL can't
> handle SQL requests involving LIMIT & OFFSET?
>
There are a few different workarounds for the lack of offset in SQL
Server, I recall a popular one being to nest your query in a couple
SELECT TOPs, basically manually plucking the data out yourself.
Though it looks like SQL Server 2005 now supports the more standard
ROW_NUMBER() function. Are you using this version?
I notice the SQLAlchemy Oracle support seems to be using ROW_NUMBER()
for offset:
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/
sqlalchemy/databases/oracle.py#L557
If that's the case it could be doing roughly the same for SQL Server
05. CCing the sqlalchemy ML, might this be considered a SQLAlchemy bug?
--
Philip Jenvey
Michael Bayer
On Jul 10, 1:28 am, Philip Jenvey <pjen...@groovie.org> wrote:
> There are a few different workarounds for the lack of offset in SQL
> Server, I recall a popular one being to nest your query in a couple
> SELECT TOPs, basically manually plucking the data out yourself.
>
> Though it looks like SQL Server 2005 now supports the more standard
> ROW_NUMBER() function. Are you using this version?
>
> I notice the SQLAlchemy Oracle support seems to be using ROW_NUMBER()
> for offset:
>
> http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/
> sqlalchemy/databases/oracle.py#L557
>
> If that's the case it could be doing roughly the same for SQL Server
> 05. CCing the sqlalchemy ML, might this be considered a SQLAlchemy bug?
>
> --
if 2005 supports ROW_NUMBER() OVER ORDER BY like oracle does, then yes
this could be a possible feature enhancement.
Paul Johnston
if 2005 supports ROW_NUMBER() OVER ORDER BY like oracle does, then yes
this could be a possible feature enhancement.
I've raised #639 so we don't forget about this.
Paul
BruceC
Esceo
attached is an experimental patch for mssql.py merged with r2891:
this is a direct steal from the oracle implementation of limit +
offset using row_number()
what I would like to find out are the following:
the oracle implementation has visit_compound_select marked as todo,
what is the implication of that? (my guess is that limit+offset won't
work for join models?)
and are there any other shortcomings with the current patch?
Cheers
Lei
792c792,793
<
---
> self._select_state = {}
>
796,799c797,800
< if select.limit:
< s += "TOP %s " % (select.limit,)
< if select.offset:
< raise exceptions.InvalidRequestError('MSSQL does not
support LIMIT with an offset')
---
> #if select.limit:
> # s += "TOP %s " % (select.limit,)
> #if select.offset:
> # raise exceptions.InvalidRequestError('MSSQL does not support LIMIT with an offset')
846a848,883
>
> # TODO: put a real copy-container on Select and copy, or somehow make this
> # not modify the Select statement
> if self._select_state.get((select, 'visit'), False):
> # cancel out the compiled order_by on the select
> if hasattr(select, "order_by_clause"):
> self.strings[select.order_by_clause] = ""
> ansisql.ANSICompiler.visit_select(self, select)
> return
>
> if select.limit is not None or select.offset is not None:
> self._select_state[(select, 'visit')] = True
> # to use ROW_NUMBER(), an ORDER BY is required.
> orderby = self.strings[select.order_by_clause]
> if not orderby:
> orderby = select.oid_column
> self.traverse(orderby)
> orderby = self.strings[orderby]
> if not hasattr(select, '_mssql_visit'):
> select.append_column(sql.literal_column("ROW_NUMBER() OVER (ORDER BY %s)" % orderby).label("ms_rn"))
> select._mssql_visit = True
> limitselect = sql.select([c for c in select.alias('_mssql').c if c.key!='ms_rn'])
>
> if select.offset is not None:
> limitselect.append_whereclause("ms_rn > %d" % select.offset)
> if select.limit is not None:
> limitselect.append_whereclause("ms_rn<=%d" % (select.limit + select.offset))
> else:
> limitselect.append_whereclause("ms_rn<=%d" % select.limit)
>
> self.traverse(limitselect)
> self.strings[select] = self.strings[limitselect]
> #print self.strings[select], select.offset, select.limit
> self.froms[select] = self.froms[limitselect]
> else:
> ansisql.ANSICompiler.visit_select(self, select)
848c885
< super(MSSQLCompiler, self).visit_select(select)
---
> #super(MSSQLCompiler, self).visit_select(select)
> > Paul- Hide quoted text -
>
> - Show quoted text -
Michael Bayer
On Jul 13, 2007, at 4:07 AM, Esceo wrote:
>
> Hi all,
>
> attached is an experimental patch for mssql.py merged with r2891:
>
> this is a direct steal from the oracle implementation of limit +
> offset using row_number()
>
> what I would like to find out are the following:
>
> the oracle implementation has visit_compound_select marked as todo,
> what is the implication of that? (my guess is that limit+offset won't
> work for join models?)
visit_compound_select is for unions. most times when we use unions
with the ORM, an enclosing select is issued around the UNIONs so it
doesnt matter. joins are OK.
lei you
Michael Bayer
On Jul 13, 2007, at 4:20 AM, lei you wrote:
> Thank you for the extremely speedy response :)
>
> Are we aware of any other impacts of oracle's offset+limit
> implication?
it has issues with DISTINCT as well. there is ticket #536 for this
which I spent some time with but not enough to understand what was
going on there exactly...if you care to look into that and help
clarify for me that would help.
lei you
Not exactly sure about what Valentin is doing there, will need to take a look at the actual schema and data involved,
select distinct path, row_number() over (order by path desc) as ora_rn from Block
row_number() is always distinct, therefore, the results returned by the above query will include all duplicate rows, now why would group by orn_rn, path help in this scenario?
one way to solve the distinct problem could be. wrap the actual query involving distinct inside an inner query, then apply row number on that instead.
so we have
select *, row_number() over (order by path desc) from (select distinct path from Block)
lei you
# not modify the Select statement
Michael Bayer
On Jul 14, 11:15 pm, "lei you" <leiyang...@gmail.com> wrote:
> # TODO: put a real copy-container on Select and copy, or somehow make this
> # not modify the Select statement
>
> In the above comments, are we referring to a solution where the original
> select statement is wrap inside another select statement where the actual
> row_number function is applied?
>
the proper fix for this is present in the 0.4 branch of SQLAlchemy.
I'd favor deferring the MS-SQL solution to 0.4 where it will be easier
to implement.