|
1 """ |
|
2 Custom Query class for Oracle. |
|
3 Derives from: django.db.models.sql.query.Query |
|
4 """ |
|
5 |
|
6 import datetime |
|
7 |
|
8 from django.db.backends import util |
|
9 from django.utils.encoding import force_unicode |
|
10 |
|
11 # Cache. Maps default query class to new Oracle query class. |
|
12 _classes = {} |
|
13 |
|
14 def query_class(QueryClass, Database): |
|
15 """ |
|
16 Returns a custom django.db.models.sql.query.Query subclass that is |
|
17 appropriate for Oracle. |
|
18 |
|
19 The 'Database' module (cx_Oracle) is passed in here so that all the setup |
|
20 required to import it only needs to be done by the calling module. |
|
21 """ |
|
22 global _classes |
|
23 try: |
|
24 return _classes[QueryClass] |
|
25 except KeyError: |
|
26 pass |
|
27 |
|
28 class OracleQuery(QueryClass): |
|
29 def __reduce__(self): |
|
30 """ |
|
31 Enable pickling for this class (normal pickling handling doesn't |
|
32 work as Python can only pickle module-level classes by default). |
|
33 """ |
|
34 if hasattr(QueryClass, '__getstate__'): |
|
35 assert hasattr(QueryClass, '__setstate__') |
|
36 data = self.__getstate__() |
|
37 else: |
|
38 data = self.__dict__ |
|
39 return (unpickle_query_class, (QueryClass,), data) |
|
40 |
|
41 def resolve_columns(self, row, fields=()): |
|
42 # If this query has limit/offset information, then we expect the |
|
43 # first column to be an extra "_RN" column that we need to throw |
|
44 # away. |
|
45 if self.high_mark is not None or self.low_mark: |
|
46 rn_offset = 1 |
|
47 else: |
|
48 rn_offset = 0 |
|
49 index_start = rn_offset + len(self.extra_select.keys()) |
|
50 values = [self.convert_values(v, None) |
|
51 for v in row[rn_offset:index_start]] |
|
52 for value, field in map(None, row[index_start:], fields): |
|
53 values.append(self.convert_values(value, field)) |
|
54 return tuple(values) |
|
55 |
|
56 def convert_values(self, value, field): |
|
57 if isinstance(value, Database.LOB): |
|
58 value = value.read() |
|
59 if field and field.get_internal_type() == 'TextField': |
|
60 value = force_unicode(value) |
|
61 |
|
62 # Oracle stores empty strings as null. We need to undo this in |
|
63 # order to adhere to the Django convention of using the empty |
|
64 # string instead of null, but only if the field accepts the |
|
65 # empty string. |
|
66 if value is None and field and field.empty_strings_allowed: |
|
67 value = u'' |
|
68 # Convert 1 or 0 to True or False |
|
69 elif value in (1, 0) and field and field.get_internal_type() in ('BooleanField', 'NullBooleanField'): |
|
70 value = bool(value) |
|
71 # Force floats to the correct type |
|
72 elif value is not None and field and field.get_internal_type() == 'FloatField': |
|
73 value = float(value) |
|
74 # Convert floats to decimals |
|
75 elif value is not None and field and field.get_internal_type() == 'DecimalField': |
|
76 value = util.typecast_decimal(field.format_number(value)) |
|
77 # cx_Oracle always returns datetime.datetime objects for |
|
78 # DATE and TIMESTAMP columns, but Django wants to see a |
|
79 # python datetime.date, .time, or .datetime. We use the type |
|
80 # of the Field to determine which to cast to, but it's not |
|
81 # always available. |
|
82 # As a workaround, we cast to date if all the time-related |
|
83 # values are 0, or to time if the date is 1/1/1900. |
|
84 # This could be cleaned a bit by adding a method to the Field |
|
85 # classes to normalize values from the database (the to_python |
|
86 # method is used for validation and isn't what we want here). |
|
87 elif isinstance(value, Database.Timestamp): |
|
88 # In Python 2.3, the cx_Oracle driver returns its own |
|
89 # Timestamp object that we must convert to a datetime class. |
|
90 if not isinstance(value, datetime.datetime): |
|
91 value = datetime.datetime(value.year, value.month, |
|
92 value.day, value.hour, value.minute, value.second, |
|
93 value.fsecond) |
|
94 if field and field.get_internal_type() == 'DateTimeField': |
|
95 pass |
|
96 elif field and field.get_internal_type() == 'DateField': |
|
97 value = value.date() |
|
98 elif field and field.get_internal_type() == 'TimeField' or (value.year == 1900 and value.month == value.day == 1): |
|
99 value = value.time() |
|
100 elif value.hour == value.minute == value.second == value.microsecond == 0: |
|
101 value = value.date() |
|
102 return value |
|
103 |
|
104 def as_sql(self, with_limits=True, with_col_aliases=False): |
|
105 """ |
|
106 Creates the SQL for this query. Returns the SQL string and list |
|
107 of parameters. This is overriden from the original Query class |
|
108 to handle the additional SQL Oracle requires to emulate LIMIT |
|
109 and OFFSET. |
|
110 |
|
111 If 'with_limits' is False, any limit/offset information is not |
|
112 included in the query. |
|
113 """ |
|
114 |
|
115 # The `do_offset` flag indicates whether we need to construct |
|
116 # the SQL needed to use limit/offset with Oracle. |
|
117 do_offset = with_limits and (self.high_mark is not None |
|
118 or self.low_mark) |
|
119 if not do_offset: |
|
120 sql, params = super(OracleQuery, self).as_sql(with_limits=False, |
|
121 with_col_aliases=with_col_aliases) |
|
122 else: |
|
123 sql, params = super(OracleQuery, self).as_sql(with_limits=False, |
|
124 with_col_aliases=True) |
|
125 |
|
126 # Wrap the base query in an outer SELECT * with boundaries on |
|
127 # the "_RN" column. This is the canonical way to emulate LIMIT |
|
128 # and OFFSET on Oracle. |
|
129 high_where = '' |
|
130 if self.high_mark is not None: |
|
131 high_where = 'WHERE ROWNUM <= %d' % (self.high_mark,) |
|
132 sql = 'SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (%s) "_SUB" %s) WHERE "_RN" > %d' % (sql, high_where, self.low_mark) |
|
133 |
|
134 return sql, params |
|
135 |
|
136 _classes[QueryClass] = OracleQuery |
|
137 return OracleQuery |
|
138 |
|
139 def unpickle_query_class(QueryClass): |
|
140 """ |
|
141 Utility function, called by Python's unpickling machinery, that handles |
|
142 unpickling of Oracle Query subclasses. |
|
143 """ |
|
144 # XXX: Would be nice to not have any dependency on cx_Oracle here. Since |
|
145 # modules can't be pickled, we need a way to know to load the right module. |
|
146 import cx_Oracle |
|
147 |
|
148 klass = query_class(QueryClass, cx_Oracle) |
|
149 return klass.__new__(klass) |
|
150 unpickle_query_class.__safe_for_unpickling__ = True |