[boulder-oracle-sprint] Applied Oracle patch

git-svn-id: http://code.djangoproject.com/svn/django/branches/boulder-oracle-sprint@3966 bcc190cf-cafb-0310-a4f2-bffc1f526a37

7 files changed, 176 insertions, 30 deletions

diff --git a/django/contrib/admin/models.py b/django/contrib/admin/models.py
index 022d20bed9..6cf6ab97c2 100644
--- a/django/contrib/admin/models.py
+++ b/django/contrib/admin/models.py
@@ -16,7 +16,8 @@ class LogEntry(models.Model):
     action_time = models.DateTimeField(_('action time'), auto_now=True)
     user = models.ForeignKey(User)
     content_type = models.ForeignKey(ContentType, blank=True, null=True)
-    object_id = models.TextField(_('object id'), blank=True, null=True)
+    #changed for Oracle support
+    object_id = models.CharField(_('object id'), maxlength=200, blank=True, null=True)
     object_repr = models.CharField(_('object repr'), maxlength=200)
     action_flag = models.PositiveSmallIntegerField(_('action flag'))
     change_message = models.TextField(_('change message'), blank=True)
diff --git a/django/contrib/sessions/models.py b/django/contrib/sessions/models.py
index f684cd381e..86c5d71957 100644
--- a/django/contrib/sessions/models.py
+++ b/django/contrib/sessions/models.py
@@ -51,6 +51,7 @@ class Session(models.Model):
     session_key = models.CharField(_('session key'), maxlength=40, primary_key=True)
     session_data = models.TextField(_('session data'))
     expire_date = models.DateTimeField(_('expire date'))
+
     objects = SessionManager()
     class Meta:
         db_table = 'django_session'
diff --git a/django/core/management.py b/django/core/management.py
index 0af842dadd..00e863ffa1 100644
--- a/django/core/management.py
+++ b/django/core/management.py
@@ -6,6 +6,7 @@ from django.core.exceptions import ImproperlyConfigured
 import os, re, shutil, sys, textwrap
 from optparse import OptionParser
 from django.utils import termcolors
+from django.conf import settings 
 
 # For Python 2.3
 if not hasattr(__builtins__, 'set'):
@@ -84,7 +85,7 @@ def get_version():
 
 def get_sql_create(app):
     "Returns a list of the CREATE TABLE SQL statements for the given app."
-    from django.db import get_creation_module, models
+    from django.db import models,get_creation_module, backend
     data_types = get_creation_module().DATA_TYPES
 
     if not data_types:
@@ -163,6 +164,9 @@ def _get_sql_model_create(model, known_models=set()):
                 field_output.append(style.SQL_KEYWORD('UNIQUE'))
             if f.primary_key:
                 field_output.append(style.SQL_KEYWORD('PRIMARY KEY'))
+            if (settings.DATABASE_ENGINE == 'oracle') and f.unique and f.primary_key: 
+                # Suppress UNIQUE/PRIMARY KEY for Oracle (ORA-02259) 
+                field_output.remove(style.SQL_KEYWORD('UNIQUE'))
             if f.rel:
                 if f.rel.to in known_models:
                     field_output.append(style.SQL_KEYWORD('REFERENCES') + ' ' + \
@@ -187,6 +191,21 @@ def _get_sql_model_create(model, known_models=set()):
         full_statement.append('    %s%s' % (line, i < len(table_output)-1 and ',' or ''))
     full_statement.append(');')
     final_output.append('\n'.join(full_statement))
+    
+    # To simulate auto-incrementing primary keys in Oracle -- creating primary tables 
+    if (settings.DATABASE_ENGINE == 'oracle') & (opts.has_auto_field): 
+        sequence_statement = 'CREATE SEQUENCE %s_sq;' % opts.db_table 
+        final_output.append(sequence_statement) 
+        trigger_statement = '' + \
+            'CREATE OR REPLACE trigger %s_tr\n'    % opts.db_table + \
+            '  before insert on %s\n'           % backend.quote_name(opts.db_table) + \
+            '    for each row\n'  + \
+            '      when (new.id is NULL)\n' + \
+            '        begin\n' + \
+            '         select %s_sq.NEXTVAL into :new.id from DUAL;\n' % opts.db_table + \
+            '      end;\n'                                                                        
+        final_output.append(trigger_statement) 
+
 
     return final_output, pending_references
 
@@ -210,9 +229,15 @@ def _get_sql_for_pending_references(model, pending_references):
                 # For MySQL, r_name must be unique in the first 64 characters.
                 # So we are careful with character usage here.
                 r_name = '%s_refs_%s_%x' % (r_col, col, abs(hash((r_table, table))))
-                final_output.append(style.SQL_KEYWORD('ALTER TABLE') + ' %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s);' % \
-                    (backend.quote_name(r_table), r_name,
-                    backend.quote_name(r_col), backend.quote_name(table), backend.quote_name(col)))
+                # if constraint name size is over 29 char and db is oracle, chop it 
+                if settings.DATABASE_ENGINE == 'oracle' and len(r_name) > 29: 
+                    final_output.append(style.SQL_KEYWORD('ALTER TABLE') + ' %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s);' % \
+                                        (backend.quote_name(r_table), r_name[0:29], 
+                                         backend.quote_name(r_col), backend.quote_name(table), backend.quote_name(col))) 
+                else: 
+                    final_output.append(style.SQL_KEYWORD('ALTER TABLE') + ' %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s);' % \
+                                        (backend.quote_name(r_table), r_name,
+                                         backend.quote_name(r_col), backend.quote_name(table), backend.quote_name(col)))
             del pending_references[model]
     return final_output
 
@@ -250,6 +275,20 @@ def _get_many_to_many_sql_for_model(model):
                 style.SQL_FIELD(backend.quote_name(f.m2m_reverse_name()))))
             table_output.append(');')
             final_output.append('\n'.join(table_output))
+        # To simulate auto-incrementing primary keys in Oracle -- creating m2m tables 
+        if (settings.DATABASE_ENGINE == 'oracle'): 
+            m_table = f.m2m_db_table() 
+            sequence_statement = 'CREATE SEQUENCE %s_sq;' % m_table 
+            final_output.append(sequence_statement) 
+            trigger_statement = '' + \
+            'CREATE OR REPLACE trigger %s_tr\n'    % m_table + \
+            '  before insert on %s\n'           % backend.quote_name(m_table) + \
+            '    for each row\n'  + \
+            '      when (new.id is NULL)\n' + \
+            '        begin\n' + \
+            '         select %s_sq.NEXTVAL into :new.id from DUAL;\n' % m_table + \
+            '      end;\n' 
+            final_output.append(trigger_statement) 
     return final_output
 
 def get_sql_delete(app):
@@ -481,7 +520,12 @@ def syncdb(verbosity=1, interactive=True):
             if verbosity >= 1:
                 print "Creating table %s" % model._meta.db_table
             for statement in sql:
-                cursor.execute(statement)
+                # go on if one table could not be created 
+                try: 
+                    cursor.execute(statement) 
+                except Exception, e: 
+                    print statement 
+                    print e 
             table_list.append(model._meta.db_table)
 
         for model in model_list:
@@ -491,7 +535,11 @@ def syncdb(verbosity=1, interactive=True):
                     if verbosity >= 2:
                         print "Creating many-to-many tables for %s.%s model" % (app_name, model._meta.object_name)
                     for statement in sql:
-                        cursor.execute(statement)
+                        try:
+                            cursor.execute(statement)
+                        except Exception, e:
+                            print statement
+                            print e
 
         transaction.commit_unless_managed()
 
@@ -1397,7 +1445,8 @@ def execute_from_command_line(action_mapping=DEFAULT_ACTION_MAPPING, argv=None):
         if not mod_list:
             parser.print_usage_and_exit()
         if action not in NO_SQL_TRANSACTION:
-            print style.SQL_KEYWORD("BEGIN;")
+            if settings.DATABASE_ENGINE != 'oracle':
+                print style.SQL_KEYWORD("BEGIN;")
         for mod in mod_list:
             if action == 'reset':
                 output = action_mapping[action](mod, options.interactive)
@@ -1406,7 +1455,8 @@ def execute_from_command_line(action_mapping=DEFAULT_ACTION_MAPPING, argv=None):
             if output:
                 print '\n'.join(output)
         if action not in NO_SQL_TRANSACTION:
-            print style.SQL_KEYWORD("COMMIT;")
+            if settings.DATABASE_ENGINE != 'oracle':
+                print style.SQL_KEYWORD("COMMIT;")
 
 def setup_environ(settings_mod):
     """
diff --git a/django/db/backends/oracle/base.py b/django/db/backends/oracle/base.py
index dfe2df11dc..5924e02e6c 100644
--- a/django/db/backends/oracle/base.py
+++ b/django/db/backends/oracle/base.py
@@ -39,6 +39,10 @@ class DatabaseWrapper(local):
             else:
                 conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)
                 self.connection = Database.connect(conn_string)
+        # set oracle date to ansi date format
+        cursor =  self.connection.cursor()
+        cursor.execute("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'")
+        cursor.close()					     
         return FormatStylePlaceholderCursor(self.connection)
 
     def _commit(self):
@@ -67,11 +71,19 @@ class FormatStylePlaceholderCursor(Database.Cursor):
     def execute(self, query, params=None):
         if params is None: params = []
         query = self.convert_arguments(query, len(params))
-        return Database.Cursor.execute(self, query, params)
+        # cx can not execute the query with the closing ';' 
+        if query.endswith(';') :
+            query = query[0:len(query)-1]			
+	print query
+	print params	
+	return Database.Cursor.execute(self, query, params)
 
     def executemany(self, query, params=None):
         if params is None: params = []
         query = self.convert_arguments(query, len(params[0]))
+        # cx can not execute the query with the closing ';' 
+        if query.endswith(';') :
+            query = query[0:len(query)-1]						    
         return Database.Cursor.executemany(self, query, params)
 
     def convert_arguments(self, query, num_params):
diff --git a/django/db/models/base.py b/django/db/models/base.py
index 13209a97ac..71f800f4bf 100644
--- a/django/db/models/base.py
+++ b/django/db/models/base.py
@@ -171,8 +171,9 @@ class Model(object):
         record_exists = True
         if pk_set:
             # Determine whether a record with the primary key already exists.
-            cursor.execute("SELECT 1 FROM %s WHERE %s=%%s LIMIT 1" % \
-                (backend.quote_name(self._meta.db_table), backend.quote_name(self._meta.pk.column)), [pk_val])
+            lim = settings.DATABASE_ENGINE != 'oracle' and ' LIMIT 1' or ''
+            cursor.execute("SELECT 1 FROM %s WHERE %s=%%s %s" % \
+                (backend.quote_name(self._meta.db_table), backend.quote_name(self._meta.pk.column), lim), [pk_val])
             # If it does already exist, do an UPDATE.
             if cursor.fetchone():
                 db_values = [f.get_db_prep_save(f.pre_save(self, False)) for f in non_pks]
diff --git a/django/db/models/fields/__init__.py b/django/db/models/fields/__init__.py
index 2ec5e8c7c7..f75dca25cf 100644
--- a/django/db/models/fields/__init__.py
+++ b/django/db/models/fields/__init__.py
@@ -159,6 +159,10 @@ class Field(object):
 
     def get_db_prep_save(self, value):
         "Returns field's value prepared for saving into a database."
+        # Oracle treats empty strings ('') the same as NULLs.  
+        # To get around this wart, we need to change it to something else...  
+        if settings.DATABASE_ENGINE == 'oracle' and  value == '': 
+            value = ' '
         return value
 
     def get_db_prep_lookup(self, lookup_type, value):
@@ -458,9 +462,13 @@ class DateField(Field):
     def get_db_prep_save(self, value):
         # Casts dates into string format for entry into database.
         if isinstance(value, datetime.datetime):
-            value = value.date().strftime('%Y-%m-%d')
+            if settings.DATABASE_ENGINE != 'oracle':
+                #Oracle does not need a string conversion
+                value = value.date().strftime('%Y-%m-%d')
         elif isinstance(value, datetime.date):
-            value = value.strftime('%Y-%m-%d')
+            if settings.DATABASE_ENGINE != 'oracle':
+                #Oracle does not need a string conversion
+                value = value.strftime('%Y-%m-%d')
         return Field.get_db_prep_save(self, value)
 
     def get_manipulator_field_objs(self):
@@ -490,21 +498,25 @@ class DateTimeField(DateField):
     def get_db_prep_save(self, value):
         # Casts dates into string format for entry into database.
         if isinstance(value, datetime.datetime):
-            # MySQL will throw a warning if microseconds are given, because it
+            # MySQL/Oracle will throw a warning if microseconds are given, because it
             # doesn't support microseconds.
-            if settings.DATABASE_ENGINE == 'mysql' and hasattr(value, 'microsecond'):
+            if (settings.DATABASE_ENGINE == 'mysql' or settings.DATABASE_ENGINE=='oracle') and hasattr(value, 'microsecond'):
                 value = value.replace(microsecond=0)
             value = str(value)
         elif isinstance(value, datetime.date):
-            # MySQL will throw a warning if microseconds are given, because it
+            # MySQL/Oracle will throw a warning if microseconds are given, because it
             # doesn't support microseconds.
-            if settings.DATABASE_ENGINE == 'mysql' and hasattr(value, 'microsecond'):
+            if (settings.DATABASE_ENGINE == 'mysql' or settings.DATABASE_ENGINE=='oracle') and hasattr(value, 'microsecond'):
                 value = datetime.datetime(value.year, value.month, value.day, microsecond=0)
             value = str(value)
             
         return Field.get_db_prep_save(self, value)
 
     def get_db_prep_lookup(self, lookup_type, value):
+        # Oracle will throw an error if microseconds are given, because it
+        # doesn't support microseconds.
+        if (settings.DATABASE_ENGINE=='oracle') and hasattr(value, 'microsecond'):
+            value = value.replace(microsecond=0)
         if lookup_type == 'range':
             value = [str(v) for v in value]
         else:
@@ -532,8 +544,13 @@ class DateTimeField(DateField):
     def flatten_data(self,follow, obj = None):
         val = self._get_val_from_obj(obj)
         date_field, time_field = self.get_manipulator_field_names('')
-        return {date_field: (val is not None and val.strftime("%Y-%m-%d") or ''),
-                time_field: (val is not None and val.strftime("%H:%M:%S") or '')}
+        #cx_Oracle does not support strftime 
+        if (settings.DATABASE_ENGINE=='oracle'): 
+            return {date_field: (val is not None or ''), 
+                    time_field: (val is not None or '')} 
+        else: 
+            return {date_field: (val is not None and val.strftime("%Y-%m-%d") or ''), 
+                    time_field: (val is not None and val.strftime("%H:%M:%S") or '')}
 
 class EmailField(CharField):
     def __init__(self, *args, **kwargs):
@@ -765,7 +782,13 @@ class TimeField(Field):
             # doesn't support microseconds.
             if settings.DATABASE_ENGINE == 'mysql':
                 value = value.replace(microsecond=0)
-            value = str(value)
+                value = str(value) 
+            elif settings.DATABASE_ENGINE == 'oracle': 
+                value = value.replace(microsecond=0) 
+                # cx_Oracle expects a datetime.datetime to persist into TIMESTAMP field.
+                value = datetime.datetime(1900, 1, 1, value.hour, value.minute, value.second)
+            else: 
+                value = str(value)
         return Field.get_db_prep_save(self, value)
 
     def get_manipulator_field_objs(self):
diff --git a/django/db/models/query.py b/django/db/models/query.py
index 53ed63ae5b..fd9465ee5c 100644
--- a/django/db/models/query.py
+++ b/django/db/models/query.py
@@ -3,6 +3,7 @@ from django.db.models.fields import DateField, FieldDoesNotExist
 from django.db.models import signals
 from django.dispatch import dispatcher
 from django.utils.datastructures import SortedDict
+from django.conf import settings
 import operator
 import re
 
@@ -168,8 +169,17 @@ class QuerySet(object):
         extra_select = self._select.items()
 
         cursor = connection.cursor()
-        select, sql, params = self._get_sql_clause()
-        cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params)
+        
+        full_query = None
+        if (settings.DATABASE_ENGINE == 'oracle'):
+            select, sql, params, full_query = self._get_sql_clause() 
+        else:
+            select, sql, params = self._get_sql_clause() 
+
+        if not full_query: 
+            cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params) 
+        else: 
+            cursor.execute(full_query, params) 
         fill_cache = self._select_related
         index_end = len(self.model._meta.fields)
         while 1:
@@ -192,7 +202,10 @@ class QuerySet(object):
         counter._offset = None
         counter._limit = None
         counter._select_related = False
-        select, sql, params = counter._get_sql_clause()
+        if (settings.DATABASE_ENGINE == 'oracle'):
+            select, sql, params, full_query = counter._get_sql_clause() 
+        else:
+            select, sql, params = counter._get_sql_clause() 
         cursor = connection.cursor()
         if self._distinct:
             id_col = "%s.%s" % (backend.quote_name(self.model._meta.db_table),
@@ -501,13 +514,48 @@ class QuerySet(object):
             sql.append("ORDER BY " + ", ".join(order_by))
 
         # LIMIT and OFFSET clauses
-        if self._limit is not None:
-            sql.append("%s " % backend.get_limit_offset_sql(self._limit, self._offset))
+        if (settings.DATABASE_ENGINE != 'oracle'):             
+            if self._limit is not None:
+                sql.append("%s " % backend.get_limit_offset_sql(self._limit, self._offset))
+            else:
+                assert self._offset is None, "'offset' is not allowed without 'limit'"
+
+            return select, " ".join(sql), params
         else:
-            assert self._offset is None, "'offset' is not allowed without 'limit'"
+            # To support limits and offsets, Oracle requires some funky rewriting of an otherwise normal looking query. 
+            select_clause = ",".join(select) 
+            distinct = (self._distinct and "DISTINCT " or "") 
 
-        return select, " ".join(sql), params
+            if order_by:  
+                order_by_clause = " OVER (ORDER BY %s )" % (", ".join(order_by)) 
+            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. 
+                order_by_clause = " OVER (ORDER BY %s.%s)" % \
+                    (backend.quote_name(opts.db_table),  
+                    backend.quote_name(opts.fields[0].db_column or opts.fields[0].column)) 
+            # limit_and_offset_clause 
+            offset = self._offset and int(self._offset) or 0 
+            limit = self._limit and int(self._limit) or None 
+            limit_and_offset_clause = '' 
+            if limit: 
+                limit_and_offset_clause = "WHERE rn > %s AND rn <= %s" % (offset, limit+offset) 
+            elif offset:
+                limit_and_offset_clause = "WHERE rn > %s" % (offset) 
 
+            if len(limit_and_offset_clause) > 0: 
+                full_query = """SELECT * FROM  
+                    (SELECT %s    
+                    %s, 
+                    ROW_NUMBER() %s AS rn 
+                    %s 
+                    ) 
+                    %s 
+                    """ % (distinct, select_clause, order_by_clause, " ".join(sql), limit_and_offset_clause)
+            else:
+                full_query = None 
+             
+            return select, " ".join(sql), params, full_query
 class ValuesQuerySet(QuerySet):
     def iterator(self):
         # select_related and select aren't supported in values().
@@ -523,7 +571,10 @@ class ValuesQuerySet(QuerySet):
             field_names = [f.attname for f in self.model._meta.fields]
 
         cursor = connection.cursor()
-        select, sql, params = self._get_sql_clause()
+        if (settings.DATABASE_ENGINE == 'oracle'):
+            select, sql, params, full_query = self._get_sql_clause() 
+        else:
+            select, sql, params = self._get_sql_clause() 
         select = ['%s.%s' % (backend.quote_name(self.model._meta.db_table), backend.quote_name(c)) for c in columns]
         cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params)
         while 1:
@@ -636,6 +687,9 @@ def get_where_clause(lookup_type, table_prefix, field_name, value):
     if table_prefix.endswith('.'):
         table_prefix = backend.quote_name(table_prefix[:-1])+'.'
     field_name = backend.quote_name(field_name)
+    #put some oracle exceptions here 
+    if lookup_type == "icontains" and settings.DATABASE_ENGINE == 'oracle': 
+        return 'lower(%s%s) %s' % (table_prefix, field_name, (backend.OPERATOR_MAPPING[lookup_type] % '%s')) 
     try:
         return '%s%s %s' % (table_prefix, field_name, (backend.OPERATOR_MAPPING[lookup_type] % '%s'))
     except KeyError:
@@ -667,6 +721,7 @@ def fill_table_cache(opts, select, tables, where, old_prefix, cache_tables_seen)
     Helper function that recursively populates the select, tables and where (in
     place) for select_related queries.
     """
+    from django.db.models.fields import AutoField 
     qn = backend.quote_name
     for f in opts.fields:
         if f.rel and not f.null:
@@ -680,7 +735,10 @@ def fill_table_cache(opts, select, tables, where, old_prefix, cache_tables_seen)
             cache_tables_seen.append(db_table)
             where.append('%s.%s = %s.%s' % \
                 (qn(old_prefix), qn(f.column), qn(db_table), qn(f.rel.get_related_field().column)))
-            select.extend(['%s.%s' % (qn(db_table), qn(f2.column)) for f2 in f.rel.to._meta.fields])
+            if settings.DATABASE_ENGINE == 'oracle':
+                select.extend(['%s.%s' % (backend.quote_name(db_table), backend.quote_name(f2.column)) for f2 in f.rel.to._meta.fields if not isinstance(f2, AutoField)]) 
+            else:
+                select.extend(['%s.%s' % (backend.quote_name(db_table), backend.quote_name(f2.column)) for f2 in f.rel.to._meta.fields]) 
             fill_table_cache(f.rel.to._meta, select, tables, where, db_table, cache_tables_seen)
 
 def parse_lookup(kwarg_items, opts):