--- a/app/django/db/backends/oracle/query.py Tue Oct 14 12:36:55 2008 +0000
+++ b/app/django/db/backends/oracle/query.py Tue Oct 14 16:00:59 2008 +0000
@@ -26,8 +26,16 @@
class OracleQuery(QueryClass):
def resolve_columns(self, row, fields=()):
- index_start = len(self.extra_select.keys())
- values = [self.convert_values(v, None) for v in row[:index_start]]
+ # If this query has limit/offset information, then we expect the
+ # first column to be an extra "_RN" column that we need to throw
+ # away.
+ if self.high_mark is not None or self.low_mark:
+ rn_offset = 1
+ else:
+ rn_offset = 0
+ index_start = rn_offset + len(self.extra_select.keys())
+ values = [self.convert_values(v, None)
+ for v in row[rn_offset:index_start]]
for value, field in map(None, row[index_start:], fields):
values.append(self.convert_values(value, field))
return values
@@ -82,70 +90,32 @@
"""
Creates the SQL for this query. Returns the SQL string and list
of parameters. This is overriden from the original Query class
- to accommodate Oracle's limit/offset SQL.
+ to handle the additional SQL Oracle requires to emulate LIMIT
+ and OFFSET.
If 'with_limits' is False, any limit/offset information is not
included in the query.
"""
- # The `do_offset` flag indicates whether we need to construct
- # the SQL needed to use limit/offset w/Oracle.
- do_offset = with_limits and (self.high_mark or self.low_mark)
-
- # If no offsets, just return the result of the base class
- # `as_sql`.
- if not do_offset:
- return super(OracleQuery, self).as_sql(with_limits=False,
- with_col_aliases=with_col_aliases)
-
- # `get_columns` needs to be called before `get_ordering` to
- # populate `_select_alias`.
- self.pre_sql_setup()
- out_cols = self.get_columns()
- ordering = self.get_ordering()
-
- # Getting the "ORDER BY" SQL for the ROW_NUMBER() result.
- if ordering:
- rn_orderby = ', '.join(ordering)
- else:
- # Oracle's ROW_NUMBER() function always requires an
- # order-by clause. So we need to define a default
- # order-by, since none was provided.
- qn = self.quote_name_unless_alias
- opts = self.model._meta
- rn_orderby = '%s.%s' % (qn(opts.db_table), qn(opts.fields[0].db_column or opts.fields[0].column))
- # Getting the selection SQL and the params, which has the `rn`
- # extra selection SQL.
- self.extra_select['rn'] = 'ROW_NUMBER() OVER (ORDER BY %s )' % rn_orderby
- sql, params= super(OracleQuery, self).as_sql(with_limits=False,
- with_col_aliases=True)
-
- # Constructing the result SQL, using the initial select SQL
- # obtained above.
- result = ['SELECT * FROM (%s)' % sql]
-
- # Place WHERE condition on `rn` for the desired range.
- result.append('WHERE rn > %d' % self.low_mark)
- if self.high_mark:
- result.append('AND rn <= %d' % self.high_mark)
+ # The `do_offset` flag indicates whether we need to construct
+ # the SQL needed to use limit/offset with Oracle.
+ do_offset = with_limits and (self.high_mark is not None
+ or self.low_mark)
+ if not do_offset:
+ sql, params = super(OracleQuery, self).as_sql(with_limits=False,
+ with_col_aliases=with_col_aliases)
+ else:
+ sql, params = super(OracleQuery, self).as_sql(with_limits=False,
+ with_col_aliases=True)
- # Returning the SQL w/params.
- return ' '.join(result), params
-
- def set_limits(self, low=None, high=None):
- super(OracleQuery, self).set_limits(low, high)
+ # Wrap the base query in an outer SELECT * with boundaries on
+ # the "_RN" column. This is the canonical way to emulate LIMIT
+ # and OFFSET on Oracle.
+ sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) AS "_RN", "_SUB".* FROM (%s) "_SUB") WHERE "_RN" > %d' % (sql, self.low_mark)
+ if self.high_mark is not None:
+ sql = '%s AND "_RN" <= %d' % (sql, self.high_mark)
- # We need to select the row number for the LIMIT/OFFSET sql.
- # A placeholder is added to extra_select now, because as_sql is
- # too late to be modifying extra_select. However, the actual sql
- # depends on the ordering, so that is generated in as_sql.
- self.extra_select['rn'] = '1'
-
- def clear_limits(self):
- super(OracleQuery, self).clear_limits()
- if 'rn' in self.extra_select:
- del self.extra_select['rn']
+ return sql, params
_classes[QueryClass] = OracleQuery
return OracleQuery
-