24 except KeyError: |
24 except KeyError: |
25 pass |
25 pass |
26 |
26 |
27 class OracleQuery(QueryClass): |
27 class OracleQuery(QueryClass): |
28 def resolve_columns(self, row, fields=()): |
28 def resolve_columns(self, row, fields=()): |
29 index_start = len(self.extra_select.keys()) |
29 # If this query has limit/offset information, then we expect the |
30 values = [self.convert_values(v, None) for v in row[:index_start]] |
30 # first column to be an extra "_RN" column that we need to throw |
|
31 # away. |
|
32 if self.high_mark is not None or self.low_mark: |
|
33 rn_offset = 1 |
|
34 else: |
|
35 rn_offset = 0 |
|
36 index_start = rn_offset + len(self.extra_select.keys()) |
|
37 values = [self.convert_values(v, None) |
|
38 for v in row[rn_offset:index_start]] |
31 for value, field in map(None, row[index_start:], fields): |
39 for value, field in map(None, row[index_start:], fields): |
32 values.append(self.convert_values(value, field)) |
40 values.append(self.convert_values(value, field)) |
33 return values |
41 return values |
34 |
42 |
35 def convert_values(self, value, field): |
43 def convert_values(self, value, field): |
80 |
88 |
81 def as_sql(self, with_limits=True, with_col_aliases=False): |
89 def as_sql(self, with_limits=True, with_col_aliases=False): |
82 """ |
90 """ |
83 Creates the SQL for this query. Returns the SQL string and list |
91 Creates the SQL for this query. Returns the SQL string and list |
84 of parameters. This is overriden from the original Query class |
92 of parameters. This is overriden from the original Query class |
85 to accommodate Oracle's limit/offset SQL. |
93 to handle the additional SQL Oracle requires to emulate LIMIT |
|
94 and OFFSET. |
86 |
95 |
87 If 'with_limits' is False, any limit/offset information is not |
96 If 'with_limits' is False, any limit/offset information is not |
88 included in the query. |
97 included in the query. |
89 """ |
98 """ |
|
99 |
90 # The `do_offset` flag indicates whether we need to construct |
100 # The `do_offset` flag indicates whether we need to construct |
91 # the SQL needed to use limit/offset w/Oracle. |
101 # the SQL needed to use limit/offset with Oracle. |
92 do_offset = with_limits and (self.high_mark or self.low_mark) |
102 do_offset = with_limits and (self.high_mark is not None |
|
103 or self.low_mark) |
|
104 if not do_offset: |
|
105 sql, params = super(OracleQuery, self).as_sql(with_limits=False, |
|
106 with_col_aliases=with_col_aliases) |
|
107 else: |
|
108 sql, params = super(OracleQuery, self).as_sql(with_limits=False, |
|
109 with_col_aliases=True) |
93 |
110 |
94 # If no offsets, just return the result of the base class |
111 # Wrap the base query in an outer SELECT * with boundaries on |
95 # `as_sql`. |
112 # the "_RN" column. This is the canonical way to emulate LIMIT |
96 if not do_offset: |
113 # and OFFSET on Oracle. |
97 return super(OracleQuery, self).as_sql(with_limits=False, |
114 sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) AS "_RN", "_SUB".* FROM (%s) "_SUB") WHERE "_RN" > %d' % (sql, self.low_mark) |
98 with_col_aliases=with_col_aliases) |
115 if self.high_mark is not None: |
|
116 sql = '%s AND "_RN" <= %d' % (sql, self.high_mark) |
99 |
117 |
100 # `get_columns` needs to be called before `get_ordering` to |
118 return sql, params |
101 # populate `_select_alias`. |
|
102 self.pre_sql_setup() |
|
103 out_cols = self.get_columns() |
|
104 ordering = self.get_ordering() |
|
105 |
|
106 # Getting the "ORDER BY" SQL for the ROW_NUMBER() result. |
|
107 if ordering: |
|
108 rn_orderby = ', '.join(ordering) |
|
109 else: |
|
110 # Oracle's ROW_NUMBER() function always requires an |
|
111 # order-by clause. So we need to define a default |
|
112 # order-by, since none was provided. |
|
113 qn = self.quote_name_unless_alias |
|
114 opts = self.model._meta |
|
115 rn_orderby = '%s.%s' % (qn(opts.db_table), qn(opts.fields[0].db_column or opts.fields[0].column)) |
|
116 |
|
117 # Getting the selection SQL and the params, which has the `rn` |
|
118 # extra selection SQL. |
|
119 self.extra_select['rn'] = 'ROW_NUMBER() OVER (ORDER BY %s )' % rn_orderby |
|
120 sql, params= super(OracleQuery, self).as_sql(with_limits=False, |
|
121 with_col_aliases=True) |
|
122 |
|
123 # Constructing the result SQL, using the initial select SQL |
|
124 # obtained above. |
|
125 result = ['SELECT * FROM (%s)' % sql] |
|
126 |
|
127 # Place WHERE condition on `rn` for the desired range. |
|
128 result.append('WHERE rn > %d' % self.low_mark) |
|
129 if self.high_mark: |
|
130 result.append('AND rn <= %d' % self.high_mark) |
|
131 |
|
132 # Returning the SQL w/params. |
|
133 return ' '.join(result), params |
|
134 |
|
135 def set_limits(self, low=None, high=None): |
|
136 super(OracleQuery, self).set_limits(low, high) |
|
137 |
|
138 # We need to select the row number for the LIMIT/OFFSET sql. |
|
139 # A placeholder is added to extra_select now, because as_sql is |
|
140 # too late to be modifying extra_select. However, the actual sql |
|
141 # depends on the ordering, so that is generated in as_sql. |
|
142 self.extra_select['rn'] = '1' |
|
143 |
|
144 def clear_limits(self): |
|
145 super(OracleQuery, self).clear_limits() |
|
146 if 'rn' in self.extra_select: |
|
147 del self.extra_select['rn'] |
|
148 |
119 |
149 _classes[QueryClass] = OracleQuery |
120 _classes[QueryClass] = OracleQuery |
150 return OracleQuery |
121 return OracleQuery |
151 |
|