1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
"""
Custom Query class for Oracle.
Derives from: django.db.models.sql.query.Query
"""
import datetime
from django.db.backends import util
# Cache. Maps default query class to new Oracle query class.
_classes = {}
def query_class(QueryClass, Database):
"""
Returns a custom django.db.models.sql.query.Query subclass that is
appropriate for Oracle.
The 'Database' module (cx_Oracle) is passed in here so that all the setup
required to import it only needs to be done by the calling module.
"""
global _classes
try:
return _classes[QueryClass]
except KeyError:
pass
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]]
for value, field in map(None, row[index_start:], fields):
values.append(self.convert_values(value, field))
return values
def convert_values(self, value, field):
from django.db.models.fields import DateField, DateTimeField, \
TimeField, BooleanField, NullBooleanField, DecimalField, Field
if isinstance(value, Database.LOB):
value = value.read()
# Oracle stores empty strings as null. We need to undo this in
# order to adhere to the Django convention of using the empty
# string instead of null, but only if the field accepts the
# empty string.
if value is None and isinstance(field, Field) and field.empty_strings_allowed:
value = u''
# Convert 1 or 0 to True or False
elif value in (1, 0) and isinstance(field, (BooleanField, NullBooleanField)):
value = bool(value)
# Convert floats to decimals
elif value is not None and isinstance(field, DecimalField):
value = util.typecast_decimal(field.format_number(value))
# cx_Oracle always returns datetime.datetime objects for
# DATE and TIMESTAMP columns, but Django wants to see a
# python datetime.date, .time, or .datetime. We use the type
# of the Field to determine which to cast to, but it's not
# always available.
# As a workaround, we cast to date if all the time-related
# values are 0, or to time if the date is 1/1/1900.
# This could be cleaned a bit by adding a method to the Field
# classes to normalize values from the database (the to_python
# method is used for validation and isn't what we want here).
elif isinstance(value, Database.Timestamp):
# In Python 2.3, the cx_Oracle driver returns its own
# Timestamp object that we must convert to a datetime class.
if not isinstance(value, datetime.datetime):
value = datetime.datetime(value.year, value.month,
value.day, value.hour, value.minute, value.second,
value.fsecond)
if isinstance(field, DateTimeField):
# DateTimeField subclasses DateField so must be checked
# first.
pass
elif isinstance(field, DateField):
value = value.date()
elif isinstance(field, TimeField) or (value.year == 1900 and value.month == value.day == 1):
value = value.time()
elif value.hour == value.minute == value.second == value.microsecond == 0:
value = value.date()
return value
def as_sql(self, with_limits=True, with_col_aliases=False):
"""
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.
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 is not None
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 is not None:
result.append('AND rn <= %d' % self.high_mark)
# Returning the SQL w/params.
return ' '.join(result), params
def set_limits(self, low=None, high=None):
super(OracleQuery, self).set_limits(low, high)
# 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']
_classes[QueryClass] = OracleQuery
return OracleQuery
|
