[Django] #20226: Django problematic when Oracle when column/table names are mixed case
Django
----------------------------------------------+-------------------------
Reporter: calcium | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.5
Severity: Normal | Keywords: Oracle, ORM
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 1 | UI/UX: 0
----------------------------------------------+-------------------------
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, 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/20226>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: calcium | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: Oracle, ORM | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
Changes (by calcium):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
Oops... the line above should read
+ m = map(str.isupper, str(field.column))
and not
+ m = map(str.isupper, field.column)
--
Ticket URL: <https://code.djangoproject.com/ticket/20226#comment:1>
Django
Reporter: calcium | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: Oracle, ORM | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 1 | UI/UX: 0
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/20226#comment:2>
Django
Reporter: calcium | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: Oracle, ORM | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 1 | UI/UX: 0
Comment (by akaariai):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/20226#comment:3>
Django
Reporter: calcium | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Keywords: Oracle, ORM | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* stage: Unreviewed => Accepted
Comment:
Marking as accepted. Some solution for using mixed case table names would
be good.
--
Ticket URL: <https://code.djangoproject.com/ticket/20226#comment:4>
Django
Reporter: calcium | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: Oracle, ORM | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Comment (by shai):
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:
{{{#!python
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/20226#comment:5>
Django
Reporter: calcium | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: Oracle, ORM | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by shai):
* cc: shai@… (added)
* easy: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/20226#comment:6>
Django
Reporter: calcium | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle, ORM | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by timgraham:
Old description:
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/20226#comment:7>