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?)
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)
On Jul 11, 9:43 am, BruceC <[EMAIL PROTECTED]> wrote:
> Thank you all for looking into this - most appreciated!
>
> On Jul 10, 6:13 pm, "Paul Johnston" <[EMAIL PROTECTED]> wrote:
>
>
>
> > Hi,
>
> > if 2005 supports ROW_NUMBER() OVER ORDER BY like oracle does, then yes
>
> > > this could be a possible feature enhancement.
>
> > Another approach that occured to me is doing TOP (limit+offset) then seeking
> > the cursor. For server-side cursors this is probably nearly as efficient as
> > LIMIT/OFFSET.
>
> > I've raised #639 so we don't forget about this.
>
> > Paul- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
