Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#1990 closed enhancement (fixed)

[patch] Oracle support in latest trunk

Reported by: tzellman Owned by: adrian
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: UI/UX:

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)

django_oracle_rev2977.patch (15.3 KB) - added by tzellman 9 years ago.
oracle patch
oracle_backend.zip (8.0 KB) - added by tzellman 9 years ago.
oracle backend folder
django_oracle_rev3496.patch (15.8 KB) - added by pk11 9 years ago.
django_oracle3545.patch (18.4 KB) - added by pk11 9 years ago.
django_oracle_rev3817.patch (724 bytes) - added by Winston Lee <lee.winston@…> 9 years ago.
patch for ORA-02259
django_oracle_release-0.95.patch (19.4 KB) - added by tzellman at gmail dot com 9 years ago.
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!
0.95_plus_timestamp.patch (19.6 KB) - added by Winston Lee <lee.winston@…> 9 years ago.
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.
django_oracle_release-0.95-v2.patch (20.3 KB) - added by tzellman at google dot com 9 years ago.
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.
django_oracle_release-0.95-v2.2.patch (20.5 KB) - added by tzellman at gmail dot com 9 years ago.
Found one more instance of the full_query "problem". Attached is the updated patch.

Download all attachments as: .zip

Change History (33)

Changed 9 years ago by tzellman

oracle patch

Changed 9 years ago by tzellman

oracle backend folder

comment:1 Changed 9 years ago by anonymous

  • Summary changed from Oracle support in latest trunk to [patch] Oracle support in latest trunk

comment:2 Changed 9 years ago by adrian

(In [2986]) Added half of oracle backend. (The other half is all of the special-casing in django/db/models/query.py, which I will be refactoring.) Refs #1990. Thanks, Jason Huggins and tzellman

comment:3 Changed 9 years ago by anonymous

  • Keywords ORACLE added
  • milestone set to Version 0.92
  • Type changed from enhancement to defect
  • Version set to 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 Changed 9 years ago by anonymous

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 Changed 9 years ago by pk

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 Changed 9 years ago by jpaulofarias at gmail dot com

Hey, pk!

Send it to me, I'm working on the oracle backend and wanna see your changes.

--
JP

comment:7 Changed 9 years ago by tzellman

  • Type changed from defect to 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 Changed 9 years ago by pk

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 Changed 9 years ago by tzellman at NOSPAM gmail dot com

Yeah, if you could, send me your code changes. remove the NOSPAM from my email above. Thanks!

comment:10 Changed 9 years ago by Fawad Halim <fawad@…>

  • Cc fawad@… added

comment:11 Changed 9 years ago by Fawad Halim <fawad@…>

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 Changed 9 years ago by pk

my oracle patch can be located here: http://pk11.awardspace.com/django_oracle_rev3278.patch

Changed 9 years ago by pk11

comment:13 Changed 9 years ago by anonymous

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 Changed 9 years ago by anonymous

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:15 Changed 9 years ago by poko

i will look into that. i did not have this issue before...

comment:16 Changed 9 years ago by pk11

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

Changed 9 years ago by pk11

comment:17 Changed 9 years ago by pk11

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)

comment:18 Changed 9 years ago by Gary Wilson <gary.wilson@…>

  • milestone changed from Version 0.92 to Version 1.0

0.92 is long gone.

Changed 9 years ago by Winston Lee <lee.winston@…>

patch for ORA-02259

Changed 9 years ago by tzellman at gmail dot com

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!

Changed 9 years ago by Winston Lee <lee.winston@…>

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 Changed 9 years ago by djinn@…

  • priority changed from normal to highest
  • Type changed from enhancement to 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! :)

Changed 9 years ago by tzellman at google dot com

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.

Changed 9 years ago by tzellman at gmail dot com

Found one more instance of the full_query "problem". Attached is the updated patch.

comment:20 Changed 9 years ago by mboersma

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 Changed 9 years ago by mboersma

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 Changed 9 years ago by adrian

  • Resolution set to fixed
  • Severity changed from normal to major
  • Status changed from new to closed
  • Type changed from defect to enhancement
  • Version SVN deleted

Closing this now that we have the boulder-oracle-sprint branch.

comment:23 Changed 9 years ago by Steve (steven.lewis@…

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.

comment:24 Changed 9 years ago by anonymous

  • milestone Version 1.0 deleted

Milestone Version 1.0 deleted

Note: See TracTickets for help on using tickets.
Back to Top