OK, much progress made (see transcript below). My current question is:
"How can I get a log written of all queries made via the command-line
interface?" Then I can start figuring out what's wrong with other
queries that get automatically generated.
I tried in the transcript to start from a nice, clean, known starting
point. I had introduced problems earlier in two ways: (1) I have python
aliased to 'rlwrap -a python' and that put some escape characters meant
for the screen into the SQL output; (2) I had a couple competing
versions of Django on my development machine. (I got rid of all, and
then checked out 3530 from Subversion.
Sorry about the length of the transcript; I wanted to show everything I
did to get this far.
========================================================================
======
New svn co to Rev 3530
========================================================================
======
\python manage.py syncdb trips yields:
File
"/usr/local/lib/python2.4/site-packages/django/db/backends/oracle/base.p
y",
line 70, in execute
return Database.Cursor.execute(self, query, params)
cx_Oracle.DatabaseError: ORA-00911: invalid character
So, time to patch... 3496 is older than current HEAD of 3530
========================================================================
======
cd /usr/local/src/django/django
[EMAIL PROTECTED] django]# patch -p0 < ../../django_oracle_rev3496.patch
patching file db/models/base.py
patching file db/models/fields/__init__.py
Hunk #2 succeeded at 453 (offset 3 lines).
patching file db/models/query.py
patching file db/backends/oracle/base.py
patching file core/management.py
Hunk #4 FAILED at 222.
Hunk #5 succeeded at 262 (offset -4 lines).
Hunk #7 succeeded at 1320 with fuzz 2 (offset 2 lines).
1 out of 7 hunks FAILED -- saving rejects to file
core/management.py.rej
patching file contrib/admin/models.py
cd core
[EMAIL PROTECTED] core]# more *.rej
***************
*** 208,215 ****
r_name += '_%s' % reference_names[r_name]
else:
reference_names[r_name] = 0
final_output.append(style.SQL_KEYWORD('ALTER TABLE')
+ ' %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFEREN
CES %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
--- 222,233 ----
r_name += '_%s' % reference_names[r_name]
else:
reference_names[r_name] = 0
+ # if constraint name size is over 29 char and db is
oracle, chop it
+ constraint_name = r_name
+ if settings.DATABASE_ENGINE == 'oracle' and
len(constraint_name) > 29:
+ constraint_name = constraint_name[0:29]
final_output.append(style.SQL_KEYWORD('ALTER TABLE')
+ ' %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES
%s (%s);' % \
+ (backend.quote_name(r_table), constraint_name,
backend.quote_name(r_col),
backend.quote_name(table),
backend.quote_name(col)))
del pending_references[model]
return final_output
Some nearby stuff changed in management.py in the latest version. I'm
trying
to edit it in for this failed hunk. Seems like first half hunk
shouldn't be changed (5 %s items, 5 elements in printf tuple). Second
hunk seems to already be handled for length (none longer than 30
characters were generated), so I won't worry about that, either.
========================================================================
======
[EMAIL PROTECTED] intl]$ \python manage.py syncdb trips
Traceback (most recent call last):
...
"/usr/local/lib/python2.4/site-packages/django/db/models/query.py",
line 526
order_by_clause = " OVER (ORDER BY %s.%s)" % \
^
SyntaxError: invalid token
This is fixed by removing the space after the backslash.
========================================================================
======
[EMAIL PROTECTED] intl]$ \python manage.py syncdb trips
...
"/usr/local/lib/python2.4/site-packages/django/db/models/query.py",
line 535
else offset:
^
SyntaxError: invalid syntax
This is fixed by changing else to elif.
========================================================================
======
[EMAIL PROTECTED] intl]$ \python manage.py syncdb trips
...
"/usr/local/lib/python2.4/site-packages/django/db/models/query.py",
line 547
else:
^
IndentationError: unindent does not match any outer indentation level
Removed a space from before else: and next three lines. Added a blank
line after the return statement.
========================================================================
======
[EMAIL PROTECTED] intl]$ \python manage.py syncdb trips
[EMAIL PROTECTED] intl]$ clear
[EMAIL PROTECTED] intl]$ \python manage.py syncdb trips
Creating table auth_message
Creating table auth_group
Creating table auth_user
Creating table auth_permission
Creating many-to-many tables for Group model
Creating many-to-many tables for User model
Creating table django_content_type
ALTER TABLE auth_permission ADD CONSTRAINT
content_type_id_refs_id_728de91f FOREIGN KEY (content_type_id)
REFERENCES django_content_type (id);
ORA-00972: identifier is too long
Creating table django_session
Creating table django_site
Creating table trips_leg
Creating table trips_company
Creating table trips_contact
Creating table trips_aircraft
Creating table trips_handler
Creating table trips_traveler
Creating table trips_legservice
Creating table trips_trip
Creating table trips_actype
Creating many-to-many tables for Trip model
Creating table django_admin_log
...skipped the rest, see below for more
[EMAIL PROTECTED] intl]$
========================================================================
======
So, looks like identifiers are too long, so I hand-patched management.py
(based
on management.py.reg), and it works better now:
[EMAIL PROTECTED] core]# diff management.py management.py~
221,224d220
< # if constraint name size is over 29 char and db is
oracle, chop it
< constraint_name = r_name
< if settings.DATABASE_ENGINE == 'oracle' and
len(constraint_name) > 29:
< constraint_name = constraint_name[0:29]
226c222
< (backend.quote_name(r_table), constraint_name,
---
> (backend.quote_name(r_table), r_name,
[EMAIL PROTECTED] intl]$ \python manage.py syncdb trips
Creating table auth_message
Creating table auth_group
Creating table auth_user
Creating table auth_permission
Creating many-to-many tables for Group model
Creating many-to-many tables for User model
Creating table django_content_type
Creating table django_session
Creating table django_site
Creating table trips_leg
Creating table trips_company
Creating table trips_contact
Creating table trips_aircraft
Creating table trips_handler
Creating table trips_traveler
Creating table trips_legservice
Creating table trips_trip
Creating table trips_actype
Creating many-to-many tables for Trip model
Creating table django_admin_log
You just installed Django's auth system, which means you don't have any
superusers defined.
Would you like to create one now? (yes/no): yes
Username (Leave blank to use 'dhancock'): admin
E-mail address: [EMAIL PROTECTED]
Password:
Password (again):
Traceback (most recent call last):
... extra stuff deleted
File
"/usr/local/lib/python2.4/site-packages/django/db/backends/oracle/base.p
y", line 77, in execute
return Database.Cursor.execute(self, query, params)
cx_Oracle.DatabaseError: ORA-01830: date format picture ends before
converting entire input string
[EMAIL PROTECTED] intl]$
========================================================================
======
And that's where I got to. From here, it would be nice to see exactly
what
SQL was executed. I see in the DB API docs how to do that in an
interactive
session, but from the command-line manage.py would be great.
Cheers!
--
David Hancock | [EMAIL PROTECTED] | 410-266-4384
-----Original Message-----
From: django-users@googlegroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of Malcolm Tredinnick
Sent: Saturday, August 05, 2006 8:47 PM
To: django-users@googlegroups.com
Subject: Re: Invalid character error using Oracle backed
On Sat, 2006-08-05 at 08:15 -0700, David wrote:
> Django-0.95, with Oracle backend patch (latest: 3496?) applied.
> Python 2.4 on Linux
> Oracle XE and Oracle 10gR2
>
> When I try to run syncdb using the Oracle backend, I reliably get the
> following error (last part of traceback only, let me know if I should
> post the whole traceback):
>
> File "django/db/backends/oracle/base.py", line 70, in execute
> return Database.Cursor.execute(self, query, params)
> cx_Oracle.DatabaseError: ORA-00911: invalid character
>
> I get this error whether I configure to use an Oracle XE (10g)
instance
> or Oracle 10gR2 instance. If I do a "sqlall" and run the resulting
file
> using sqlplus, I don't get this error. (I DO get some
> identifier-too-long errors, but if I edit the output of sqlall, those
> go away.)
>
> The main question is: How can I get syncdb to work without tracing
> back?
Good question. As you probably realise, the Oracle backend is pretty
much unmaintained at this point, since the requirements for working on
it are pretty hefty (starting with "1. install Oracle"). So be aware
that you are currently the world's expert on the Oracle backend. You've
applied the patch and made it run that far. That being said, if you want
to put in the work to get this up and running, please ask as many
questions as you need to. We may not be able to help on all things
Oracle, but we can at least give you the background and motivations for
why things are done as they are at the moment and help you apply your
Oracle-specific knowledge to getting a solution.
You are going to have to put some debugging prints into
db/backends/oracle/base.py and possibly look at what (if any) character
set assumptions the cx_Oracle module and/or your Oracle instance are
making. My first step would be to look at what "query" and "params" are
just before the above traceback (wrap a try...except block around the
return statement and only print in the except portion and then re-raise
the exception).
I dimly remember having character set problems with Oracle in the past
when talking through the Python interface, but the specifics escape me
at the moment. I don't have an Oracle install around the test anything
on at the moment, either.
> A second question: What are my options with respect to long
> identifiers? So far, it's just in the constraint names. For example:
>
> ALTER TABLE trips_aircraft ADD CONSTRAINT
> type_id_referencing_trips_actype_id FOREIGN KEY (type_id) REFERENCES
> trips_actype (id);
>
> The constraint name (optional in Oracle) tries to be
> type_id_referencing_trips_actype_id but Oracle has a maximum allowed
> length of 30 characters.
Grrr. It's the 21st century. Why can't databases handle things longer
than this? Some of Oracle's restrictions and oddities are really bizarre
for a database that is used in so many data-critical situations. :-(
I have been trying really hard not to just nuke the constraint names.
When you do violate a constraint, having a useful name in the error
provides some information about where to look for the problem (since
it's always going to be an accidental violation and never where you were
expecting the problem). Anonymous (backend generated) names make
debugging harder, since you have to know how to track back from the name
to the constraint to the table(s) it applies to and that is
backend-specific.
Moving the name generation into the backend is starting to feel more and
more attractive. Or maybe just saying "most unlucky" to the debugging
case and using anonymous names everywhere we can. I really hate the last
option, though, since helping people debug problems is a reasonable
courtesy.
For now, if you want to hack on the source for your initial
testing/debugging, have a look in django/core/management.py around line
206. There's a line that says
r_name = '%s_refs_%s_%x' % (r_col, col, abs(hash((r_table,
table))))
(it's the only place we use hash() in the code in that file, so search
for that word). Change this to anything you like that will make your
backend happy. That will tide you over until we can get a more
reasonable solution in place.
Good luck,
Malcolm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users
-~----------~----~----~----~------~----~------~--~---
