app/django/db/backends/oracle/query.py
changeset 54 03e267d67478
child 323 ff1a9aa48cfd
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/app/django/db/backends/oracle/query.py	Fri Jul 18 18:22:23 2008 +0000
@@ -0,0 +1,151 @@
+"""
+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 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:
+                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
+