Django problematic when Oracle when column/table names are mixed case

[calcium]

In my Oracle DB, some of the tables have column names which are not uppercase only.
eg
column names are "FIRSTNAME", and "CallType".

When running a query against any table with such non-uppercase only columnnames, I get an error like

django.db.utils.DatabaseError: ORA-00904: "SOMETABLENAME"."CALLTYPE": invalid identifier.

I got around my problem by editing the compiler.py file.
/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py


The diff is as follows.

*** ~/Exploded/Django-1.5.1/django/db/models/sql/compiler.py	2013-03-29 07:07:20.000000000 +1100
--- /usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py	2013-04-09 12:56:07.293736020 +1000
*************** class SQLCompiler(object):
*** 296,302 ****
--- 296,305 ----
                  col_aliases.add(c_alias)
                  aliases.add(c_alias)
              else:
+                 m = map(str.isupper, str(field.column))
                  r = '%s.%s' % (qn(alias), qn2(field.column))
+                 if True in m:  # if there is an uppercase, leave it.
+                     r = '%s."%s"' % (qn(alias), field.column)
                  result.append(r)
                  aliases.add(r)
                  if with_aliases:

ie. I check if there is an uppercase in the column name, and if so,
I quote the column name, leaving the case as it is ie. mixed.
ie. the column names seem to come in as all lower case and then it
gets converted to uppercase.
I'm not sure what to do if the column names are really all lower case.

This was just a quick and dirty fix for my use.
I think maybe a more solid fix might be required but just raising it here in case anybody just wanted a quick fix.

A similar problem arises for mixed case table names.
I didnt do a fix for that, yet.

[calcium]

For the mixed case table name problem, I get this error when doing a $python manage.py inspectdb.

DatabaseError: ORA-00942: table or view does not exist

I have traced it down to this line in
/usr/local/lib/python2.7/dist-packages/django/core/management/commands/inspectdb.py

Line 74 for i, row in enumerate(connection.introspection.get_table_description(cursor, table_name)):

And this is where I get stuck. I cannot proceed any further, for now.

Hope this helps somewhat.

[Anssi Kääriäinen]

The right place for fix seems to be in django/db/backends/oracle/base.py:quote_name(). The fix is to not upper-case already quoted names. That is, if your table_name is

    db_table = '"alReady_Quoted"'

then no upper casing should happen. However, if it is

    db_table = 'nOt_Quoted'

then automatic uppercasing should happen.

So, the fix is to remove the upper() call in the method's last line.

[Anssi Kääriäinen]

Marking as accepted. Some solution for using mixed case table names would be good.

[Shai Berger]

I don't see how it can be fixed without breaking backwards compatibility. If we do as Anssi suggested and remove the upper() call in the last line (even if we introduce the required fix for truncated names -- an extra upper() call), anybody who currently has db_table = '"alReady_Quoted"' will find her code suddenly stopped working.

Granted, these users have an easy fix (upcase the table name in their source).

FWIW, the function will then have to look like this:

    def quote_name(self, name):
        # SQL92 requires delimited (quoted) names to be case-sensitive.  When
        # not quoted, Oracle has case-insensitive behavior for identifiers, but
        # always defaults to uppercase.
        # We simplify things by making Oracle identifiers always uppercase.
        if not name.startswith('"') and not name.endswith('"'):
            name = '"%s"' % util.truncate_name(name.upper(),
                                               self.max_name_length())
            
            name = name.upper()                                                # <-- upper() added here; See explanation below
        # Oracle puts the query text into a (query % args) construct, so % signs
        # in names need to be escaped. The '%%' will be collapsed back to '%' at
        # that stage so we aren't really making the name longer here.
        name = name.replace('%','%%')
        return name                                                            # <-- upper() removed here

The reason for the weird pattern of calling upper() is still backwards compatibility; we need to have the hash characters for truncated names in upper-case, and we need to calculate the hash itself over upper-cased names or else a different hash is computed.