|
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 |
|
10 # Cache. Maps default query class to new Oracle query class. |
|
11 _classes = {} |
|
12 |
|
13 def query_class(QueryClass, Database): |
|
14 """ |
|
15 Returns a custom django.db.models.sql.query.Query subclass that is |
|
16 appropriate for Oracle. |
|
17 |
|
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. |
|
20 """ |
|
21 global _classes |
|
22 try: |
|
23 return _classes[QueryClass] |
|
24 except KeyError: |
|
25 pass |
|
26 |
|
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)) |
|
33 return values |
|
34 |
|
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): |
|
39 value = value.read() |
|
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 |
|
43 # empty string. |
|
44 if value is None and isinstance(field, Field) and field.empty_strings_allowed: |
|
45 value = u'' |
|
46 # Convert 1 or 0 to True or False |
|
47 elif value in (1, 0) and isinstance(field, (BooleanField, NullBooleanField)): |
|
48 value = bool(value) |
|
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 |
|
56 # always available. |
|
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, |
|
68 value.fsecond) |
|
69 if isinstance(field, DateTimeField): |
|
70 # DateTimeField subclasses DateField so must be checked |
|
71 # first. |
|
72 pass |
|
73 elif isinstance(field, DateField): |
|
74 value = value.date() |
|
75 elif isinstance(field, TimeField) or (value.year == 1900 and value.month == value.day == 1): |
|
76 value = value.time() |
|
77 elif value.hour == value.minute == value.second == value.microsecond == 0: |
|
78 value = value.date() |
|
79 return value |
|
80 |
|
81 def as_sql(self, with_limits=True, with_col_aliases=False): |
|
82 """ |
|
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 accommodate Oracle's limit/offset SQL. |
|
86 |
|
87 If 'with_limits' is False, any limit/offset information is not |
|
88 included in the query. |
|
89 """ |
|
90 # The `do_offset` flag indicates whether we need to construct |
|
91 # the SQL needed to use limit/offset w/Oracle. |
|
92 do_offset = with_limits and (self.high_mark or self.low_mark) |
|
93 |
|
94 # If no offsets, just return the result of the base class |
|
95 # `as_sql`. |
|
96 if not do_offset: |
|
97 return super(OracleQuery, self).as_sql(with_limits=False, |
|
98 with_col_aliases=with_col_aliases) |
|
99 |
|
100 # `get_columns` needs to be called before `get_ordering` to |
|
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 |
|
149 _classes[QueryClass] = OracleQuery |
|
150 return OracleQuery |
|
151 |