[patch] Oracle support in latest trunk

oracle patch

oracle backend folder

(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

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

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

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

Hey, pk!

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

-- JP

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.

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

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

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',

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

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.

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.

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

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

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)

0.92 is long gone.

patch for ORA-02259

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!

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.

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! :)

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.

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

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.

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

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

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.

Milestone Version 1.0 deleted