2
Custom Query class for Oracle.
3
Derives from: django.db.models.sql.query.Query
8
from django.db.backends import util
10
# Cache. Maps default query class to new Oracle query class.
13
def query_class(QueryClass, Database):
15
Returns a custom django.db.models.sql.query.Query subclass that is
16
appropriate for Oracle.
18
The 'Database' module (cx_Oracle) is passed in here so that all the setup
19
required to import it only needs to be done by the calling module.
23
return _classes[QueryClass]
27
class OracleQuery(QueryClass):
28
def resolve_columns(self, row, fields=()):
29
index_start = len(self.extra_select.keys())
30
values = [self.convert_values(v, None) for v in row[:index_start]]
31
for value, field in map(None, row[index_start:], fields):
32
values.append(self.convert_values(value, field))
35
def convert_values(self, value, field):
36
from django.db.models.fields import DateField, DateTimeField, \
37
TimeField, BooleanField, NullBooleanField, DecimalField, Field
38
if isinstance(value, Database.LOB):
40
# Oracle stores empty strings as null. We need to undo this in
41
# order to adhere to the Django convention of using the empty
42
# string instead of null, but only if the field accepts the
44
if value is None and isinstance(field, Field) and field.empty_strings_allowed:
46
# Convert 1 or 0 to True or False
47
elif value in (1, 0) and isinstance(field, (BooleanField, NullBooleanField)):
49
# Convert floats to decimals
50
elif value is not None and isinstance(field, DecimalField):
51
value = util.typecast_decimal(field.format_number(value))
52
# cx_Oracle always returns datetime.datetime objects for
53
# DATE and TIMESTAMP columns, but Django wants to see a
54
# python datetime.date, .time, or .datetime. We use the type
55
# of the Field to determine which to cast to, but it's not
57
# As a workaround, we cast to date if all the time-related
58
# values are 0, or to time if the date is 1/1/1900.
59
# This could be cleaned a bit by adding a method to the Field
60
# classes to normalize values from the database (the to_python
61
# method is used for validation and isn't what we want here).
62
elif isinstance(value, Database.Timestamp):
63
# In Python 2.3, the cx_Oracle driver returns its own
64
# Timestamp object that we must convert to a datetime class.
65
if not isinstance(value, datetime.datetime):
66
value = datetime.datetime(value.year, value.month,
67
value.day, value.hour, value.minute, value.second,
69
if isinstance(field, DateTimeField):
70
# DateTimeField subclasses DateField so must be checked
73
elif isinstance(field, DateField):
75
elif isinstance(field, TimeField) or (value.year == 1900 and value.month == value.day == 1):
77
elif value.hour == value.minute == value.second == value.microsecond == 0:
81
def as_sql(self, with_limits=True, with_col_aliases=False):
83
Creates the SQL for this query. Returns the SQL string and list
84
of parameters. This is overriden from the original Query class
85
to handle the additional SQL Oracle requires to emulate LIMIT
88
If 'with_limits' is False, any limit/offset information is not
89
included in the query.
92
# The `do_offset` flag indicates whether we need to construct
93
# the SQL needed to use limit/offset with Oracle.
94
do_offset = with_limits and (self.high_mark is not None
97
sql, params = super(OracleQuery, self).as_sql(with_limits=False,
98
with_col_aliases=with_col_aliases)
100
# `get_columns` needs to be called before `get_ordering` to
101
# populate `_select_alias`.
104
ordering = self.get_ordering()
106
# Oracle's ROW_NUMBER() function requires an ORDER BY clause.
108
rn_orderby = ', '.join(ordering)
110
# Create a default ORDER BY since none was specified.
111
qn = self.quote_name_unless_alias
112
opts = self.model._meta
113
rn_orderby = '%s.%s' % (qn(opts.db_table),
114
qn(opts.fields[0].db_column or opts.fields[0].column))
116
# Ensure the base query SELECTs our special "_RN" column
117
self.extra_select['_RN'] = ('ROW_NUMBER() OVER (ORDER BY %s)'
119
sql, params = super(OracleQuery, self).as_sql(with_limits=False,
120
with_col_aliases=True)
122
# Wrap the base query in an outer SELECT * with boundaries on
123
# the "_RN" column. This is the canonical way to emulate LIMIT
124
# and OFFSET on Oracle.
125
sql = 'SELECT * FROM (%s) WHERE "_RN" > %d' % (sql, self.low_mark)
126
if self.high_mark is not None:
127
sql = '%s AND "_RN" <= %d' % (sql, self.high_mark)
131
def set_limits(self, low=None, high=None):
132
super(OracleQuery, self).set_limits(low, high)
133
# We need to select the row number for the LIMIT/OFFSET sql.
134
# A placeholder is added to extra_select now, because as_sql is
135
# too late to be modifying extra_select. However, the actual sql
136
# depends on the ordering, so that is generated in as_sql.
137
self.extra_select['_RN'] = ('1', '')
139
def clear_limits(self):
140
super(OracleQuery, self).clear_limits()
141
if '_RN' in self.extra_select:
142
del self.extra_select['_RN']
144
_classes[QueryClass] = OracleQuery