django/db/backends/oracle/query.py

   1 """
   2 Custom Query class for this backend (a derivative of
   3 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 djang.db.models.sql.query.Query subclass that is
  16     appropraite 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