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