#1990 closed enhancement (fixed)
[patch] Oracle support in latest trunk
Reported by: | tzellman | Owned by: | Adrian Holovaty |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | |
Severity: | major | Keywords: | ORACLE |
Cc: | fawad@… | Triage Stage: | Unreviewed |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Based on Jason Higgins' Oracle support he added in request #87 for the 0.91 release, I have provided an oracle backend for the latest Django trunk (magic removal).
Attached is the patch file (for changes made to existing django source), and a zip file containing the oracle backend directory (just unzip into the db/backends directory of the django code base).
None of the changes made in the existing code affect the other database backends.
I just created the patch (revision 2977).
Attachments (9)
Change History (33)
by , 18 years ago
Attachment: | django_oracle_rev2977.patch added |
---|
comment:1 by , 18 years ago
Summary: | Oracle support in latest trunk → [patch] Oracle support in latest trunk |
---|
comment:2 by , 18 years ago
comment:3 by , 18 years ago
Keywords: | ORACLE added |
---|---|
milestone: | → Version 0.92 |
Type: | enhancement → defect |
Version: | → SVN |
Hello:
Whgen I try to use the next command, I get an error, what is wrong?
.python manage.py syncdb
1) SELECT TABLE_NAME FROM USER_TABLES -> []
2) SELECT TABLE_NAME FROM USER_TABLES
Creating table auth_message
1) CREATE TABLE auth_message (
id number(38) NOT NULL PRIMARY KEY,
user_id integer NOT NULL,
message long NOT NULL
); -> []
2) CREATE TABLE auth_message (
id number(38) NOT NULL PRIMARY KEY,
user_id integer NOT NULL,
message long NOT NULL
)
Creating table auth_group
1) CREATE TABLE auth_group (
id number(38) NOT NULL PRIMARY KEY,
name varchar2(80) NOT NULL UNIQUE
); -> []
2) CREATE TABLE auth_group (
id number(38) NOT NULL PRIMARY KEY,
name varchar2(80) NOT NULL UNIQUE
)
Creating table auth_user
1) CREATE TABLE auth_user (
id number(38) NOT NULL PRIMARY KEY,
username varchar2(30) NOT NULL UNIQUE,
first_name varchar2(30) NOT NULL,
last_name varchar2(30) NOT NULL,
email varchar2(75) NOT NULL,
password varchar2(128) NOT NULL,
is_staff number(1) NOT NULL,
is_active number(1) NOT NULL,
is_superuser number(1) NOT NULL,
last_login date NOT NULL,
date_joined date NOT NULL
); -> []
2) CREATE TABLE auth_user (
id number(38) NOT NULL PRIMARY KEY,
username varchar2(30) NOT NULL UNIQUE,
first_name varchar2(30) NOT NULL,
last_name varchar2(30) NOT NULL,
email varchar2(75) NOT NULL,
password varchar2(128) NOT NULL,
is_staff number(1) NOT NULL,
is_active number(1) NOT NULL,
is_superuser number(1) NOT NULL,
last_login date NOT NULL,
date_joined date NOT NULL
)
1) ALTER TABLE auth_message ADD CONSTRAINT user_id_referencing_auth_user_id FOREIGN KEY (user_id) REFERENCES auth_user (id); -> []
2) ALTER TABLE auth_message ADD CONSTRAINT user_id_referencing_auth_user_id FOREIGN KEY (user_id) REFERENCES auth_user (id)
Traceback (most recent call last):
File "manage.py", line 11, in ?
execute_manager(settings)
File "C:\python24\lib\site-packages\django\core\management.py", line 1256, in
execute_manager
execute_from_command_line(action_mapping, argv)
File "C:\python24\lib\site-packages\django\core\management.py", line 1182, in
execute_from_command_line
action_mapping[action]()
File "C:\python24\lib\site-packages\django\core\management.py", line 466, in s
yncdb
cursor.execute(statement)
File "C:\python24\lib\site-packages\django\db\backends\oracle\base.py", line 7
3, in execute
return Database.Cursor.execute(self, query, params)
cx_Oracle.DatabaseError: ORA-00972: identificador demasiado largo
comment:4 by , 18 years ago
your problem is that the constraint name is too long ('add contraint .....') (from the oracle doc: The name of a schema object exceeds 30 characters. Schema objects are tables, clusters, views, indexes)
there are a couple of other things that not working. i will post a list with some fixes soon.
best,
kp
comment:5 by , 18 years ago
i discovered 4 problems so far:
- triggers were not created for m2m tables
- 'add constraint' can be too large for oracle
- some statements did not get executed because of the statement closing ';' at syncdb
- the NULL vs. empty fix was not copied over from jason's original ticket (oracle treats an empty string as null)
some stuff that i needed:
- i set "alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'" to get default ansi date format
- i catch and print db related exceptions at syncdb rather than terminating the process
if the patch owner wants to take a look, i can email my solution.
pk
comment:6 by , 18 years ago
Hey, pk!
Send it to me, I'm working on the oracle backend and wanna see your changes.
--
JP
comment:7 by , 18 years ago
Type: | defect → enhancement |
---|
Keep in mind that the changes that I sent (in the form of a patch and zip file) are not a COMPLETE solution. They do not address the syncdb command, and so will likely not work. My use case involved an existing database that I just needed to map using Django.
I did also notice that not all of my changes were added to the codebase, so there are still some gaps. I tried moving from my altered django codebase to the latest, and things went from working to not-working. I would consider this a work-in-progress for sure, and don't expect things to just work right off the bat all the time. If something isn't working, or if there is curious behaviour, fix it and send the svn patch. If I have time, I'll see what I can do as well.
comment:8 by , 18 years ago
hey tzellman, i really appreciated your work, without your first version i could not have gotten a working (so far) oracle backend either.
i emailed my modifications to JP btw. but if you are interested, i can email it to you as well.
thanks,
pk
comment:9 by , 18 years ago
Yeah, if you could, send me your code changes. remove the NOSPAM from my email above. Thanks!
comment:10 by , 18 years ago
Cc: | added |
---|
comment:11 by , 18 years ago
There's a small problem in creation.py. Apparently, Oracle doesn't like 2 long fields in a table, so it croaks while creating the django_admin_log table with the error
SQL Error: ORA-01754: a table may contain only one column of type LONG
The workaround is to use CLOB instead of LONG
27c27
< 'TextField': 'long',
---
'TextField': 'clob',
31c31
< 'XMLField': 'long',
---
'XMLField': 'clob',
comment:12 by , 18 years ago
my oracle patch can be located here: http://pk11.awardspace.com/django_oracle_rev3278.patch
by , 18 years ago
Attachment: | django_oracle_rev3496.patch added |
---|
comment:13 by , 18 years ago
Looking at the patch code, I see that the offset/limit implementation is done using Oracle's row_number analytic function rather than using the rownum pseudo-column. It seems to me that rownum would be simpler and better. Tom Kyte says:
If you use row_number() when you can use rownum (SHOULD use rownum) the only thing you'll achieve: lack of performance increased resource usage Your choice.
Also, I think the row_number function works from 9i onward. That shouldn't be a huge roadblock since most have moved on from 8i by now.
comment:14 by , 18 years ago
Thanks for your work, it's good to be able to use the Django DB API on Oracle. However, despite all efforts we still can't get the app working. Using the current revision and also revision 3496 (both patched with django_oracle_rev3496.patch), ORA-01830 still shows its ugly face.
File "/usr/local/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/backends/oracle/base.py", 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
This is triggered whenever django tries to add a user to the admin section, and also when a browser connects (django_session table). How is this possible if cursor.execute("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'") is called for every connection? Something odd is going on.
comment:16 by , 18 years ago
hello,
could you post the actual query?
also, you can try to remove this
# 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()
from oracle's base.py and see what happens (i needed because our standard date format is not the ansi one).
pk11
by , 18 years ago
Attachment: | django_oracle3545.patch added |
---|
comment:17 by , 18 years ago
i think i fixed your problem, let me know how it works (please note, my new patch is against rev3545 if you using against the official .96
the "add constraint" part won't work)
by , 18 years ago
Attachment: | django_oracle_release-0.95.patch added |
---|
I created a patch for the stable 0.95 release (in SVN at /tags/releases/0.95/). This patch incorporates the changes made by pk11 and Winston Lee (basically, the previous 2 patches). You can apply this to a freshly checked out 0.95 release and you'll get all the latest updates that everyone has been contributing. As always, thanks to everyone for continuing the effort to supply Oracle bindings for Django!
by , 18 years ago
Attachment: | 0.95_plus_timestamp.patch added |
---|
Binding of datetime.time is to the timestamp field. cx_Oracle expects datetime.datetime to persist to timestamp field. Hard-coded 1900-01-01 as the date, not pretty but gets it saving the time.
comment:19 by , 18 years ago
priority: | normal → highest |
---|---|
Type: | enhancement → defect |
Hi,
just found a little Bug in django\core\management.py:
Original Line @ 477 for model in model_list: if model in created_models: sql = _get_many_to_many_sql_for_model(model) if sql: print "Creating many-to-many tables for %s model" % model.__name__ for statement in sql: cursor.execute(statement) PATCH: Line @ 520 for model in model_list: if model in created_models: sql = _get_many_to_many_sql_for_model(model) if sql: print "Creating many-to-many tables for %s model" % model.__name__ for statement in sql: #So Orcale dont bother. Dirk try: cursor.execute(statement) except Exception, e: print statement print e
Just wrote a try except block around execute. Now everything works fine. Just my little contribution to Django! :)
by , 18 years ago
Attachment: | django_oracle_release-0.95-v2.patch added |
---|
I updated with the last two changes, and ALSO found a bug that was causing the previous patch to fail for MySQL. Essentially, the change was in query.py. One of the changes was to unpack the full_query, so it was failing on MySQL due to not enough items to unpack. I added a check and only unpacked the full_query if it is oracle.
by , 18 years ago
Attachment: | django_oracle_release-0.95-v2.2.patch added |
---|
Found one more instance of the full_query "problem". Attached is the updated patch.
comment:20 by , 18 years ago
0.95 plus django_oracle_release-0.95-v2.2.patch didn't work for me. I could do a "syncdb" successfully, but any subsequent SELECT statement fails with "ORA-00905: missing keyword."
The solution is to remove the optional AS keyword from the alias clause in a join. This would appear to be an Oracle bug. I made this change at line 473 of django/db/models/query.py:
# Compose the join dictionary into SQL describing the joins.
if joins:
if settings.DATABASE_ENGINE == 'oracle':
sql.append(" ".join(["%s %s %s ON %s" % (join_type, table, alias, condition)
for (alias, (table, join_type, condition)) in joins.items()]))
else:
sql.append(" ".join(["%s %s AS %s ON %s" % (join_type, table, alias, condition)
for (alias, (table, join_type, condition)) in joins.items()]))
I simply omit the "AS" keyword with another ugly oracle conditional test. But it works here.
My company is really hoping official Oracle support comes out with the 1.0 version of Django. We like MySQL, but unless we can use Oracle we won't be able to use Django in this environment.
comment:21 by , 18 years ago
The Colorado Front Range Pythoneers group is hosting a code sprint on November 4th to try to push this over the edge, fix the obvious bugs, and create a cleaner patch that could be accepted into the main trunk.
Please join us or provide your advice at our sprint wiki page:
http://wiki.python.org/moin/BoulderSprint
comment:22 by , 18 years ago
Resolution: | → fixed |
---|---|
Severity: | normal → major |
Status: | new → closed |
Type: | defect → enhancement |
Version: | SVN |
Closing this now that we have the boulder-oracle-sprint
branch.
comment:23 by , 18 years ago
I'm getting a segmentation fault running inspectdb against one of my oracle databases. The smaller schema works, but it seems to have issues with the larger one.
Running Python 2.4.3 against Oracle 10g. It's probably running an older kernel, so I'll try it on a newer machine with a newer kernel. Actually, I'm also running on an older OCI driver version for 10g.
oracle patch