Ticket #87: unified_diff_for_django_oracle_support.txt

Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py
===================================================================
--- C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py (revision 477)
+++ C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py (working copy)
@@ -42,3 +42,4 @@
 OPERATOR_MAPPING = dbmod.OPERATOR_MAPPING
 DATA_TYPES = dbmod.DATA_TYPES
 DATA_TYPES_REVERSE = dbmod.DATA_TYPES_REVERSE
+EMPTY_STR_EQUIV = dbmod.EMPTY_STR_EQUIV

Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py
===================================================================
--- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py   (revision 477)
+++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py   (working copy)
@@ -146,3 +146,5 @@
     FIELD_TYPE.LONG_BLOB: 'TextField',
     FIELD_TYPE.VAR_STRING: 'CharField',
 }
+
+EMPTY_STR_EQUIV = ''
\ No newline at end of file


Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py
===================================================================
--- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py  (revision 477)
+++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py  (working copy)
@@ -177,3 +177,5 @@
     1266: 'TimeField',
     1700: 'FloatField',
 }
+
+EMPTY_STR_EQUIV = ''
\ No newline at end of file


Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py
===================================================================
--- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py (revision 477)
+++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py (working copy)
@@ -165,3 +165,5 @@
 }
 
 DATA_TYPES_REVERSE = {}
+
+EMPTY_STR_EQUIV = ''
\ No newline at end of file


Index: C:/MyStuff/projects/django-svn/trunk/django/core/management.py
===================================================================
--- C:/MyStuff/projects/django-svn/trunk/django/core/management.py  (revision 477)
+++ C:/MyStuff/projects/django-svn/trunk/django/core/management.py  (working copy)
@@ -20,7 +20,7 @@
 ADMIN_TEMPLATE_DIR = os.path.join(django.__path__[0], 'conf/admin_templates')
 
 def _get_packages_insert(app_label):
-    return "INSERT INTO packages (label, name) VALUES ('%s', '%s');" % (app_label, app_label)
+    return "INSERT INTO packages (label, name) VALUES ('%s', '%s')" % (app_label, app_label)
 
 def _get_permission_codename(action, opts):
     return '%s_%s' % (action, opts.object_name.lower())
@@ -34,11 +34,11 @@
     return perms + list(opts.permissions)
 
 def _get_permission_insert(name, codename, opts):
-    return "INSERT INTO auth_permissions (name, package, codename) VALUES ('%s', '%s', '%s');" % \
+    return "INSERT INTO auth_permissions (name, package, codename) VALUES ('%s', '%s', '%s')" % \
         (name.replace("'", "''"), opts.app_label, codename)
 
 def _get_contenttype_insert(opts):
-    return "INSERT INTO content_types (name, package, python_module_name) VALUES ('%s', '%s', '%s');" % \
+    return "INSERT INTO content_types (name, package, python_module_name) VALUES ('%s', '%s', '%s')" % \
         (opts.verbose_name, opts.app_label, opts.module_name)
 
 def _is_valid_dir_name(s):
@@ -85,9 +85,26 @@
         full_statement = ['CREATE TABLE %s (' % opts.db_table]
         for i, line in enumerate(table_output): # Combine and add commas.
             full_statement.append('    %s%s' % (line, i < len(table_output)-1 and ',' or ''))
-        full_statement.append(');')
+        full_statement.append(')')
         final_output.append('\n'.join(full_statement))
+        
+        if (db.DATABASE_ENGINE == 'oracle') & (opts.has_auto_field):
+            # To simulate auto-incrementing primary keys in Oracle
+                        
+            sequence_statement = 'CREATE SEQUENCE %s_sq\n' % 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"           % opts.db_table + \
+                "    for each row\n"  + \
+                "      begin\n" + \
+                "        select %s_sq.NEXTVAL into :new.id from DUAL;\n" % opts.db_table + \
+                "      end;\n"
+            final_output.append(trigger_statement)
+                                
 
+    
     for klass in mod._MODELS:
         opts = klass._meta
         for f in opts.many_to_many:
@@ -98,8 +115,22 @@
             table_output.append('    %s_id %s NOT NULL REFERENCES %s (%s),' % \
                 (f.rel.to.object_name.lower(), db.DATA_TYPES['IntegerField'], f.rel.to.db_table, f.rel.to.pk.name))
             table_output.append('    UNIQUE (%s_id, %s_id)' % (opts.object_name.lower(), f.rel.to.object_name.lower()))
-            table_output.append(');')
+            table_output.append(')')
             final_output.append('\n'.join(table_output))
+            
+            if (db.DATABASE_ENGINE == 'oracle') & (opts.has_auto_field):
+                sequence_statement = 'CREATE SEQUENCE %s_sq\n' % f.get_m2m_db_table(opts)
+                final_output.append(sequence_statement)    
+                                
+                trigger_statement = "" + \
+                    "CREATE OR REPLACE trigger %s_tr\n"    % f.get_m2m_db_table(opts) + \
+                    "  before insert on %s\n"           % f.get_m2m_db_table(opts) + \
+                    "    for each row\n"  + \
+                    "      begin\n" + \
+                    "        select %s_sq.NEXTVAL into :new.id from DUAL;\n" % f.get_m2m_db_table(opts) + \
+                    "      end;\n"
+                final_output.append(trigger_statement)                
+            
     return final_output
 get_sql_create.help_doc = "Prints the CREATE TABLE SQL statements for the given app(s)."
 get_sql_create.args = APP_ARGS
@@ -116,23 +147,27 @@
         try:
             if cursor is not None:
                 # Check whether the table exists.
-                cursor.execute("SELECT 1 FROM %s LIMIT 1" % klass._meta.db_table)
+                cursor.execute("SELECT COUNT(1) FROM %s" % klass._meta.db_table)
         except:
             # The table doesn't exist, so it doesn't need to be dropped.
             db.db.rollback()
         else:
             output.append("DROP TABLE %s;" % klass._meta.db_table)
+            if db.DATABASE_ENGINE == 'oracle':
+                output.append("DROP SEQUENCE %s_sq" % klass._meta.db_table)
+            
     for klass in mod._MODELS:
         opts = klass._meta
         for f in opts.many_to_many:
             try:
                 if cursor is not None:
-                    cursor.execute("SELECT 1 FROM %s LIMIT 1" % f.get_m2m_db_table(opts))
+                    cursor.execute("SELECT COUNT(1) FROM %s" % f.get_m2m_db_table(opts))
             except:
                 db.db.rollback()
             else:
                 output.append("DROP TABLE %s;" % f.get_m2m_db_table(opts))
-
+                if db.DATABASE_ENGINE == 'oracle':
+                    output.append("DROP SEQUENCE %s_sq" % f.get_m2m_db_table(opts))
     app_label = mod._MODELS[0]._meta.app_label
 
     # Delete from packages, auth_permissions, content_types.
@@ -181,12 +216,15 @@
 
 def get_sql_sequence_reset(mod):
     "Returns a list of the SQL statements to reset PostgreSQL sequences for the given module."
-    from django.core import meta
+    from django.core import db, meta
     output = []
     for klass in mod._MODELS:
         for f in klass._meta.fields:
             if isinstance(f, meta.AutoField):
-                output.append("SELECT setval('%s_%s_seq', (SELECT max(%s) FROM %s));" % (klass._meta.db_table, f.name, f.name, klass._meta.db_table))
+                if db.DATABASE_ENGINE == 'postgresql':
+                    output.append("SELECT setval('%s_%s_seq', (SELECT max(%s) FROM %s));" % (klass._meta.db_table, f.name, f.name, klass._meta.db_table))
+                else:
+                    raise "Not implemented yet for %s!" % (db.DATABASE_ENGINE)
     return output
 get_sql_sequence_reset.help_doc = "Prints the SQL statements for resetting PostgreSQL sequences for the given app(s)."
 get_sql_sequence_reset.args = APP_ARGS


Index: C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py
===================================================================
--- C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py   (revision 477)
+++ C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py   (working copy)
@@ -1045,8 +1045,8 @@
     kwargs['select'] = kwargs.get('select', {}).items()
 
     cursor = db.db.cursor()
-    select, sql, params = function_get_sql_clause(opts, **kwargs)
-    cursor.execute("SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql, params)
+    select, sql, params, full_query = function_get_sql_clause(opts, **kwargs)
+    cursor.execute(full_query, params)
     fill_cache = kwargs.get('select_related')
     index_end = len(opts.fields)
     while 1:
@@ -1070,7 +1070,7 @@
     kwargs['offset'] = None
     kwargs['limit'] = None
     kwargs['select_related'] = False
-    _, sql, params = function_get_sql_clause(opts, **kwargs)
+    _, sql, params, full_query = function_get_sql_clause(opts, **kwargs)
     cursor = db.db.cursor()
     cursor.execute("SELECT COUNT(*)" + sql, params)
     return cursor.fetchone()[0]
@@ -1087,7 +1087,7 @@
         fields = [f.name for f in opts.fields]
 
     cursor = db.db.cursor()
-    _, sql, params = function_get_sql_clause(opts, **kwargs)
+    _, sql, params, full_query = function_get_sql_clause(opts, **kwargs)
     select = ['%s.%s' % (opts.db_table, f) for f in fields]
     cursor.execute("SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql, params)
     while 1:
@@ -1116,7 +1116,7 @@
                 db_table = new_prefix
             cache_tables_seen.append(db_table)
             where.append('%s.%s = %s.%s' % (old_prefix, f.name, db_table, f.rel.field_name))
-            select.extend(['%s.%s' % (db_table, f2.name) for f2 in f.rel.to.fields])
+            select.extend(['%s.%s as "%s.%s"' % (db_table, f2.name, db_table, f2.name) for f2 in f.rel.to.fields])
             _fill_table_cache(f.rel.to, select, tables, where, db_table, cache_tables_seen)
 
 def _throw_bad_kwarg_error(kwarg):
@@ -1266,16 +1266,58 @@
                 order_by.append('%s%s ASC' % (table_prefix, f))
     order_by = ", ".join(order_by)
 
-    # LIMIT and OFFSET clauses
-    if kwargs.get('limit') is not None:
-        limit_sql = " LIMIT %s " % kwargs['limit']
-        if kwargs.get('offset') is not None and kwargs['offset'] != 0:
-            limit_sql += "OFFSET %s " % kwargs['offset']
+
+    sql = " FROM " + ",".join(tables) + (where and " WHERE " + " AND ".join(where) or "") + (order_by and " ORDER BY " + order_by or "")
+
+    if (db.DATABASE_ENGINE != 'oracle'):
+        # LIMIT and OFFSET clauses
+        if kwargs.get('limit') is not None:            
+            limit_sql = " LIMIT %s " % kwargs['limit']
+            if kwargs.get('offset') is not None and kwargs['offset'] != 0:
+                limit_sql += "OFFSET %s " % kwargs['offset']
+        else:
+            limit_sql = ""        
+
+        full_query = "SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql + limit_sql
+        return select, sql + limit_sql, params, full_query
     else:
-        limit_sql = ""
+        # To support limits and offsets, Oracle requires some funky rewriting of an otherwise normal looking query.
+        
+        select_clause = ",".join(select)
+        distinct = (kwargs.get('distinct') and "DISTINCT " or "")
+        from_clause = ",".join(tables)
+        where_clause = (where and " WHERE " + " AND ".join(where) or "")
+        
+        if order_by:            
+            order_by_clause = " OVER (ORDER BY %s )" % (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)" % (opts.db_table, opts.fields[0].name)            
+        
+        # limit_and_offset_clause
+        limit = kwargs.get('limit',0)
+        offset = kwargs.get('offset',0)
+        
+        limit_and_offset_clause = ''
+        if limit:
+            limit = int(limit)
+            offset = int(offset)
+            limit_and_offset_clause = "WHERE rn > %s AND rn <= %s" % (offset, limit+offset)
+        else:
+            limit_and_offset_clause = "WHERE rn > %s" % (offset)
+                                 
+        full_query = """SELECT * FROM 
+                         (SELECT %s                  
+                          %s,
+                          ROW_NUMBER() %s AS rn
+                          FROM %s
+                          %s
+                         )
+                         %s
+                     """ % (distinct, select_clause, order_by_clause, from_clause, where_clause, limit_and_offset_clause)
+        return select, sql, params, full_query                               
 
-    return select, " FROM " + ",".join(tables) + (where and " WHERE " + " AND ".join(where) or "") + (order_by and " ORDER BY " + order_by or "") + limit_sql, params
-
 def function_get_in_bulk(opts, klass, *args, **kwargs):
     id_list = args and args[0] or kwargs['id_list']
     assert id_list != [], "get_in_bulk() cannot be passed an empty list."
@@ -1300,7 +1342,7 @@
     kwargs['order_by'] = [] # Clear this because it'll mess things up otherwise.
     if field.null:
         kwargs.setdefault('where', []).append('%s.%s IS NOT NULL' % (opts.db_table, field.name))
-    select, sql, params = function_get_sql_clause(opts, **kwargs)
+    select, sql, params, full_query = function_get_sql_clause(opts, **kwargs)
     sql = 'SELECT %s %s GROUP BY 1 ORDER BY 1' % (db.get_date_trunc_sql(kind, '%s.%s' % (opts.db_table, field.name)), sql)
     cursor = db.db.cursor()
     cursor.execute(sql, params)


Index: C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py
===================================================================
--- C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py (revision 477)
+++ C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py (working copy)
@@ -1,5 +1,6 @@
 from django.conf import settings
 from django.core import formfields, validators
+from django.core import db
 from django.core.exceptions import ObjectDoesNotExist
 from django.utils.functional import curry
 from django.utils.text import capfirst
@@ -88,6 +89,12 @@
 
     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 value == '':
+            string_replacement = getattr(db,'EMPTY_STR_EQUIV','')
+            value = string_replacement
         return value
 
     def get_db_prep_lookup(self, lookup_type, value):
@@ -255,6 +262,14 @@
         kwargs['blank'] = True
         Field.__init__(self, *args, **kwargs)
 
+    def get_db_prep_lookup(self, lookup_type, value):
+        if db.DATABASE_ENGINE == 'oracle':
+            if value == 'True':
+                value = 1
+            elif value == 'False':
+                value = 0
+        return Field.get_db_prep_lookup(self, lookup_type, value)
+
     def get_manipulator_field_objs(self):
         return [formfields.CheckboxField]
 
@@ -289,7 +304,9 @@
     def get_db_prep_save(self, value):
         # Casts dates into string format for entry into database.
         if value is not None:
-            value = value.strftime('%Y-%m-%d')
+            if db.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):
@@ -299,7 +316,9 @@
     def get_db_prep_save(self, value):
         # Casts dates into string format for entry into database.
         if value is not None:
-            value = value.strftime('%Y-%m-%d %H:%M:%S')
+            if db.DATABASE_ENGINE != 'oracle':
+            #Oracle does not need a string conversion        
+                value = value.strftime('%Y-%m-%d %H:%M:%S')
         return Field.get_db_prep_save(self, value)
 
     def get_manipulator_field_objs(self):
@@ -493,7 +512,9 @@
     def get_db_prep_save(self, value):
         # Casts dates into string format for entry into database.
         if value is not None:
-            value = value.strftime('%H:%M:%S')
+            if db.DATABASE_ENGINE != 'oracle':
+            #Oracle does not need a string conversion            
+                value = value.strftime('%H:%M:%S')
         return Field.get_db_prep_save(self, value)
 
     def get_manipulator_field_objs(self):