Oracle patch is ready
Matt Boersma
We made a wiki page describing the goals and status of the code (quick
summary: Oracle works and we think it's done, unless you tell us
otherwise). Attached to that page is a patch against rev 5036 of the
trunk. See here:
http://code.djangoproject.com/wiki/OracleBranch
The diff itself is here:
http://code.djangoproject.com/attachment/wiki/OracleBranch/django-oracle-rev5036.diff
This is a "non-trivial patch," we believe, so rather than attaching it
to the closed Trac bug #1990, we're advertising it here as recommended
on the "Contributing to Django" page. There are some minor
architectural changes in there, although we've made every effort to
minimize the overall deltas.
Please review the patch if you're able to and let us know what you
think.
Malcolm Tredinnick
> The "boulder-oracle-sprint" branch is ready to come home.
Nice work, guys. :-)
For my part, I'll grab the branch and read over the diff. Might be a few
days until I can give some comments, since I think this weekend is going
to be a bit busy, but this shouldn't drop out of sight.
Cheers,
Malcolm
Jacob Kaplan-Moss
> The "boulder-oracle-sprint" branch is ready to come home.
Wahoo!!
Just to clarify, Matt: you're willing to commit to maintaining Oracle
support once we merge this into trunk, yes? If so, I'm +1 on merging
this in (there's a few things we had to do I'm not 100% thrilled with
-- in particular the QS method overriding hack) but I'm OK fixing that
stuff a bit later. I'd love to get this to be an "official" feature!
Jacob
Ben Ford
Great work on the oracle stuff!! I've been using it with the multiple-db branch for the last month or so and it's been great!
A couple of things I had to mess with (mainly to use introspect db):
- in django.core.management (line 842) I had to get my tables like this:
for table_name in [list_of_desired_table_names>]: #introspection_module.get_table_list(cursor):
because my get_table_list in oracle.introspection looks like this:
def get_table_list(cursor):
"Returns a list of table names in the current database."
cursor.execute("SELECT TABLE_NAME FROM USER_TABLES")
return [row[0].upper() for row in cursor.fetchall()]
I know nothing about oracle so I don't really know what the function of USER_TABLES is, but it wasn't getting me what I needed.
I can't think of any other major things I changed at right now, but suffice to say my svn check out is looking pretty tatty at the moment (I'm also using the gis branch) and if I can do anything to help getting this into trunk I will :)
Regards,
Ben
Regards,
Ben Ford
ben.f...@gmail.com
+628111880346
Jim Baker
As we discussed at PyCon, we can make an even stronger commitment,
since this continues to be an official project for the Front Range
Pythoneers in Boulder. But people ultimately get this work done, so
kudos go to ring leader Matt Boersma, the intrepid Ian Kelly, Eric
Dobbs, Matt Drew, Michelle Cyr, and Mitch Smith for making this
happen.
And a special thanks to you for coming out on an early flight from
Lawrence and working with us in Boulder on Saturday, Nov 4, that did
make all the difference. (For those interested in user group
sprinting, I'll plug it here, http://wiki.python.org/moin/BoulderSprint
:)
- Jim
On Apr 19, 10:08 pm, "Jacob Kaplan-Moss" <jacob.kaplanm...@gmail.com>
wrote:
Matt Boersma
Array BioPharma, we will stay on top of it. If you need an individual
point of contact, I can be that, and Ian Kelly and I will be
monitoring the mailing lists and Trac more actively and responding to
Oracle-related issues.
Matt
On Apr 19, 10:08 pm, Jim Baker <james.edward.ba...@gmail.com> wrote:
> Jacob,
>
> As we discussed at PyCon, we can make an even stronger commitment,
> since this continues to be an official project for the Front Range
> Pythoneers in Boulder. But people ultimately get this work done, so
> kudos go to ring leader Matt Boersma, the intrepid Ian Kelly, Eric
> Dobbs, Matt Drew, Michelle Cyr, and Mitch Smith for making this
> happen.
>
> And a special thanks to you for coming out on an early flight from
> Lawrence and working with us in Boulder on Saturday, Nov 4, that did
> make all the difference. (For those interested in user group
> sprinting, I'll plug it here,http://wiki.python.org/moin/BoulderSprint
Adrian Holovaty
Congrats, guys! Thanks to all of you for your hard work. I'm +1 for
merging it, too, once we've taken a look at it.
Let's organize the merge process. Malcolm, since you've been dealing
with query.py and other databasey parts of Django, do you want to take
the lead on the merge? Or Jacob? I could get to it next week if you
guys are busy.
Adrian
--
Adrian Holovaty
holovaty.com | djangoproject.com
Matt Boersma
process we found a problem we'd created for Postgres that wasn't
caught by any of the tests. So we fixed that and added a "datatypes"
test to modeltests.
So there is a slightly newer patch available now:
http://code.djangoproject.com/attachment/wiki/OracleBranch/django-oracle-rev5046.diff
We'll try not to do this again so people can focus on the content of
the patch for a bit, but these two things needed to be fixed ASAP. If
you're testing the boulder-oracle-sprint branch directly, please
update.
Matt
On Apr 20, 10:03 am, "Adrian Holovaty" <holov...@gmail.com> wrote:
benk
I have been using the Oracle branch now for a few months and it is
looking really good.
I have a question regarding the use of the 'db_tablespace' option in
the Meta class. I am able to set this option within my application and
successfully perform a syncdb that will place all my tables in the
correct tablespace. The question/issue that I am having is that I
cannot see a way of instructing any other application (such as
authentication) to also create its tables in the tablespace of my
choosing.
To my understanding the application will either create its tables in
the tablespace specified in its own models.py or it create its tables
in whatever the default tablespace happens to be. This results in
tables being created in several different tablespaces.
For my 2c worth, if this cannot already be done by the current Oracle
database framework, I think that would be a beneficial addition if
only to provide completeness to the option to assign tablespaces.
Regards
Ben Khoo
On Apr 21, 1:54 am, Matt Boersma <oggh...@gmail.com> wrote:
> Ben Khoo already found a good bug (#4093) that we fixed, and in the
> process we found a problem we'd created for Postgres that wasn't
> caught by any of the tests. So we fixed that and added a "datatypes"
> test to modeltests.
>
> So there is a slightly newer patch available now:http://code.djangoproject.com/attachment/wiki/OracleBranch/django-ora...
Matt Boersma
db_tablespace parameter back to models not directly under your
control, like auth, sessions, etc., so the db_tablespace parameter we
added is only a halfway solution.
Our thinking was that for any "real" Oracle deployment, chances are
high that you'll want to take the output of "./manage.py sqlall" and
at least modify the index and row tablespaces, as well as create more
specific grant permissions (and change LOBs to be stored out-of-table,
and...). We are unlikely to please a full-time Oracle DBA with the
DDL we create no matter what we do, given the richness of Oracle's
storage options.
Having the db_tablespace parameter makes at least the models under
your control more self-documenting. But it's not perfect. Here are
the three options I can think of:
- Leave as-is, realizing it helps somewhat
- Drop the "db_tablespace" and index tablespace keywords altogether to
avoid this confusion
- Add a mechanism for a default global tablespace and global index
tablespace that apply unless overridden in a specific model or column
Thoughts?
Matt
Carlo C8E Miron
2007/4/23, Matt Boersma <ogg...@gmail.com>:
[...]
> Having the db_tablespace parameter makes at least the models under
> your control more self-documenting. But it's not perfect. Here are
> the three options I can think of:
> - Leave as-is, realizing it helps somewhat
-0, it may lead to false expectations, as Ben Khoo's one.
> - Drop the "db_tablespace" and index tablespace keywords altogether to
> avoid this confusion
+0
> - Add a mechanism for a default global tablespace and global index
> tablespace that apply unless overridden in a specific model or column
+1, as it seems to accomplish the most of the basic needs with minimal hassle
(c)
--
Carlo C8E Miron
benk
I can see where you are going regarding the manual "massaging" of
"sqlall" output to create the database tables. My primary reason for
the post was to ensure that I did not miss a piece of documentation
showing how the tablespace could be set for other applications.
To clear up potential misconceptions, I am most certainly not an
Oracle DBA. Far from it. I am merely a lowly programmer who is
currently working with Django.
For the moment, I have worked around the issue of tables appearing in
different tablespaces by creating an Oracle user configured to use a
specific default tablespace which is the same as the db_tablespace.
This means that for applications like auth where the db_tablespace is
not specified, Oracle will create the table in my configured default
tablespace. All of this of course happens at the user creation level
in Oracle and makes the explicit setting of the db_tablespace option
redundant.
In my personal opinion (that I encourage those with higher powers to
overrule as required) I think that it is leaving the db_tablespace
option "as is" would be completely acceptable with the addition of a
note to prevent misconceptions like mine. The only disadvantage that I
can see with this path is that this option is a "halfway solution" as
you put it and only applies to Oracle. This may lead to a longer term
cluttering of the Django API for limited gain.
Ben
Matt Boersma
Jacob might weigh in and decide the "db_tablespace" minor controversy.
But I hope this unresolved issue isn't perceived as holding up
integration of the Oracle patch. I don't think it should. At worst,
we could strip out the "db_tablespace" options altogether to simplify
the patch, then resurrect it as a separate patch on trunk once we know
which of the below options is preferred. I also think it's ok to
leave it as-is for now.
So this is my "ping" to see if there's any general feedback on the
patch, and if there's any way we can facilitate it getting
incorporated. Not a complaint! [ducks head]
thanks,
Matt
Malcolm Tredinnick
On Thu, 2007-04-26 at 20:04 +0000, Matt Boersma wrote:
> I had purposely let this thread dangle, hoping Malcolm or Adrian or
> Jacob might weigh in and decide the "db_tablespace" minor controversy.
>
> But I hope this unresolved issue isn't perceived as holding up
> integration of the Oracle patch. I don't think it should. At worst,
> we could strip out the "db_tablespace" options altogether to simplify
> the patch, then resurrect it as a separate patch on trunk once we know
> which of the below options is preferred. I also think it's ok to
> leave it as-is for now.
Sorry, I guess I wasn't paying enough attention. I hadn't actually
realised there was a real controversy here. I'll go back and reread the
thread with my eyes open.
> So this is my "ping" to see if there's any general feedback on the
> patch, and if there's any way we can facilitate it getting
> incorporated. Not a complaint! [ducks head]
I've been reading through the patch a couple of times. No real problems
that I can see. Not unexpected, since it's clear you guys have clues and
the Guiding Hand Of Jacob(tm) has been present from the outset.
There are a couple of things we might need to flag as
quasi-backwards-incompat issues. My list is on a computer that is
shutdown at the moment, but one area I remember is that I think the
names of indexes or tables will change slightly for MySQL in the process
of introducing truncate_name(). This isn't a showstopper, but it means
that sqlreset and friends break slightly across the merge point (because
the newly generated names don't match the originals) so we should flag
it.
My memory says there was one other issue like that that we'd need to
note. I'll finish reading through tonight or tomorrow and post my list.
Nothing that would prevent a merge, though. It's all post-merge
public-awareness stuff.
Regards,
Malcolm
Michael van der Westhuizen
On 4/27/07, Malcolm Tredinnick <mal...@pointy-stick.com> wrote:
>
> Hey Matt,
>
> On Thu, 2007-04-26 at 20:04 +0000, Matt Boersma wrote:
> > I had purposely let this thread dangle, hoping Malcolm or Adrian or
> > Jacob might weigh in and decide the "db_tablespace" minor controversy.
> >
> > But I hope this unresolved issue isn't perceived as holding up
> > integration of the Oracle patch. I don't think it should. At worst,
> > we could strip out the "db_tablespace" options altogether to simplify
> > the patch, then resurrect it as a separate patch on trunk once we know
> > which of the below options is preferred. I also think it's ok to
> > leave it as-is for now.
>
> Sorry, I guess I wasn't paying enough attention. I hadn't actually
> realised there was a real controversy here. I'll go back and reread the
> thread with my eyes open.
>
[snip]
For what it's worth, I don't believe that this is enough of an issue
to hold up a merge.
If it's not appropriate to set the users default tablespace (alter
user bob default tablespace new_tablespace), there are other ways to
solve the problem.
Using the database-specific "Providing initial SQL data" method
described in the model API documentation, one could quite simply add
the following to <appname>/sql/<modelname>.oracle.sql:
alter table in_wrong_place move tablespace new_tablespace;
alter index in_wrong_place_ix rebuild tablespace new_index_tablespace;
Do something like the above for each of the models in the
(third-party) apps you use and the problem is solved.
There are other reasons you'd want to move objects out of the default
tablespace. Oracle tuning often involves moving objects to a
tablespace that will provide optimal access to the objects based on
what those objects look like and how the objects are accessed. This
sort of thing is fairly common in the Oracle world and generally
happens either in load-testing or scheduled maintenance on production
databases.
In summary: while a tweakable setting for the default tablespace
initially sounds like a good idea it duplicates functionality already
present in Oracle (the users default tablespace) without adding much
in the way of value. IMHO this should not hold up a merge (and
therefore more widespread testing).
Michael
vizcayno
> alter table in_wrong_place move tablespace new_tablespace;
> alter index in_wrong_place_ix rebuild tablespace new_index_tablespace;
>
creating Oracle tables, I must coordinate with DBA about the
definition of tablespaces and sizes. There is a clear separation
between data and indexes at the tablespace level. During the creation
of the tables and indexes, I must clearly define where to put tables
and where to put indexes. No "alters" is allowed in this phase, it is
seen as a lack of planning.
> In summary: while a tweakable setting for the default tablespace
> initially sounds like a good idea it duplicates functionality already
> present in Oracle (the users default tablespace) without adding much
> in the way of value. IMHO this should not hold up a merge (and
> therefore more widespread testing).
>
Personally, I am doing some testings. The nice Django project for
Oracle is in this phase also. All is working well for me by the
moment. The only trouble I have (scope trouble) is with the interfase
cx_Oracle which, by now, does not support Unicode and Decimals but,
with the help of this helpful group I adapted some tricks.
Regards.
benk
I think I may have found a bug with the way Django/Oracle is handling
NCLOB datatypes (used by TextFields) and the SELECT DISTINCT
statements. I have submitted a ticket (#4186).
Regards
Ian Kelly
Unfortunately (or fortunately, depending on your point of view),
Oracle doesn't allow the DISTINCT keyword to be used when the SELECT
list includes a LOB column. Matt and I talked about this a couple of
weeks ago, but we couldn't see there being any good solution to the
problem, so we just let the matter drop. Oracle also doesn't allow
LOB columns to be used in primary keys, indexes, or ORDER BY clauses,
as well as some other restrictions that aren't currently relevant to
Django.
One way to work around this is to keep your TextFields separate from
any tables that you foresee using the distinct method with. In the
example from the ticket, this would amount to using something like:
[user.myuser for user in User.objects.filter(username='Ben').distinct()]
in place of:
MyUser.objects.filter(basenode__username='Ben').distinct()
This isn't to say that I'm not interested in solving the problem; I
just don't know what a proper solution would entail. Any suggestions?
Thanks,
Ian Kelly
benk
Thanks for the response.
This isn't actually a show stopper for me, I was just logging the
issue as I stumbled across it.
Unfortunately I am not a Oracle expert so I don't have any useful
database level suggestions. My only thoughts with regards to Django is
that this issue/limitation should be documented somewhere so that my
post is not repeated by other users.
On a separate topic, I submitted a ticket a few months back (#3464)
about use of a CharField for the IPAddressField datatype. I noticed
that it has since been fixed in the branch so my ticket should
probably be closed by someone in authority.
Thanks
Ben
world_domination_kites
re: SELECT DISTINCT LOBdata in Oracle
I have seen "select distinct myLOB" done in Oracle as "select distinct
first4kchar(myLOB)", where first4kchar() takes a LOB argument and
return's the first 4000 characters in a varchar2 type. Could dig up
the PL/SQL, ask me for it if you are interested.
Chris
On Apr 30, 3:42 pm, "Ian Kelly" <ian.g.ke...@gmail.com> wrote:
> Hi Ben,
>
> Unfortunately (or fortunately, depending on your point of view),
> Oracle doesn't allow the DISTINCT keyword to be used when the SELECT
> list includes a LOB column. Matt and I talked about this a couple of
> weeks ago, but we couldn't see there being any good solution to the
> problem, so we just let the matter drop. Oracle also doesn't allow
> LOB columns to be used in primary keys, indexes, or ORDER BY clauses,
> as well as some other restrictions that aren't currently relevant to
> Django.
>
> One way to work around this is to keep your TextFields separate from
> any tables that you foresee using the distinct method with. In the
> example from the ticket, this would amount to using something like:
>
> [user.myuser for user in User.objects.filter(username='Ben').distinct()]
>
> in place of:
>
> MyUser.objects.filter(basenode__username='Ben').distinct()
>
> This isn't to say that I'm not interested in solving the problem; I
> just don't know what a proper solution would entail. Any suggestions?
>
> Thanks,
> Ian Kelly
>
Ian Kelly
From your description, it sounds to me like this would cause the query
to return a 4000-character varchar2 in place of the lob, and so the
domain objects returned by the Django distinct query would have their
TextFields truncated to 4000 characters. Silently returning truncated
data is worse than just producing an error, so this approach gets -0
from me if that's the case.
But If I'm misunderstanding you, and the query does still return the
LOB field, then I would like to see it.
Thanks,
Ian
Jason F. McBrayer
> So this is my "ping" to see if there's any general feedback on the
> patch, and if there's any way we can facilitate it getting
> incorporated. Not a complaint! [ducks head]
I'm doing some testing here, and not really having any serious
problems. One thing that I've noticed is that sqlreset does not drop
sequences before trying to create them. Is this the way it's supposed
to work? In my usage, it causes manage.py reset [app] to frequently
fail.
I've also seen some problems in interaction between sqlreset and the
Oracle recycle bin, but I'm not sure those would happen with a clean
schema (the schema I am testing with is hand-coded and in flux).
--
+-----------------------------------------------------------+
| Jason F. McBrayer jmc...@carcosa.net |
| If someone conquers a thousand times a thousand others in |
| battle, and someone else conquers himself, the latter one |
| is the greatest of all conquerors. --- The Dhammapada |
Ian Kelly
>
> Matt Boersma <ogg...@gmail.com> writes:
>
> > So this is my "ping" to see if there's any general feedback on the
> > patch, and if there's any way we can facilitate it getting
> > incorporated. Not a complaint! [ducks head]
>
> I'm doing some testing here, and not really having any serious
> problems. One thing that I've noticed is that sqlreset does not drop
> sequences before trying to create them. Is this the way it's supposed
> to work? In my usage, it causes manage.py reset [app] to frequently
> fail.
>
> I've also seen some problems in interaction between sqlreset and the
> Oracle recycle bin, but I'm not sure those would happen with a clean
> schema (the schema I am testing with is hand-coded and in flux).
Hi Jason,
It should drop the sequence at the time it drops the table. Have you
been using the latest revision of the branch? There was an
indentation bug I fixed a couple of weeks ago that was preventing
sequences from being dropped under most circumstances.
When I was looking at this just now, I did run into a couple of other
bugs affecting the management commands, which are now fixed in
revision [5136], so you might want to update any way and see if this
has fixed the problem for you.
I don't use the Oracle recycle bin. What kinds of problems are you seeing?
Thanks,
Ian
Jason F. McBrayer
> It should drop the sequence at the time it drops the table. Have you
> been using the latest revision of the branch? There was an
> indentation bug I fixed a couple of weeks ago that was preventing
> sequences from being dropped under most circumstances.
My checkout was from April 12 (don't have the tree I installed from
handy to check the revision, unfortunately), so that's likely the
problem. I'll test with revision 5136 and make sure that it's fixed.
If you don't hear from me about it, assume that it's fine.
> I don't use the Oracle recycle bin. What kinds of problems are you seeing?
When running manage.py reset [various apps] I was getting various
errors from Oracle apparently referring to things that weren't in the
active schema, but were in the recycle bin. However, I don't think
these are very reproducable, and I didn't save the output; if I see
anything like them again I'll make a point of recording as much
information about it as I can.
benk
I have run into a curious problem while using TextFields (NCLOB
datatype) in the oracle branch.
The problem that I am having is that if I attempt to store a piece of
text greater than 4000 characters in length. The save function will
complete successfully however when I attempt extract the data from the
database, all I receive is a series of '?' characters.
For example
>>> for i in range(3998,4003,1):
... s = 'a'*i
... t = Test(long_text=s)
... t.save()
... print 'text length = %d, saved length =
%d'%(len(s),len(Test.objects.get(pk=t.id).long_text))
text length = 3998, saved length = 3998
text length = 3999, saved length = 3999
text length = 4000, saved length = 4000
text length = 4001, saved length = 2001
text length = 4002, saved length = 2002
I have seen this happen on an Oracle 10g (10.1.0) database running on
a
SUSE 9.3 (Kernal: 2.6.11.4-21.9-smp, Machine: x86) machine.
Curiously I also ran the same test on a Windows XP Home machine using
the free Oracle Express Edition and it ran just fine.
The Oracle Express Edition that I am using is:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
It seems that the problem is isolated to the database running on the
linux machine for I have manually tested the following combinations.
Django on Linux + Oracle on Linux : Fail
Django on WinXP + Oracle on Linux : Fail
Django on Linux + Oracle on WinXP : Pass
Django on WinXP + Oracle on WinXP : Pass
Given the result of this investigation, my gut tells me that the
problem lies
in some configurable part of the Oracle installation that is running
on Linux
however I have no idea what this could be because from what I can see
both
instances of the database are "identical".
Unfortunately I don't have another Linux box on which to test this to
find out
if it is just this one installation that is playing up.
Has anyone else encountered a similar problem or have any thoughts on
how I could
get to the bottom of this mystery?
Kind Regards
Ben
Jeremy Dunck
...
>
> text length = 3998, saved length = 3998
> text length = 3999, saved length = 3999
> text length = 4000, saved length = 4000
> text length = 4001, saved length = 2001
> text length = 4002, saved length = 2002
>
> Django on Linux + Oracle on Linux : Fail
> Django on WinXP + Oracle on Linux : Fail
> Django on Linux + Oracle on WinXP : Pass
> Django on WinXP + Oracle on WinXP : Pass
> however I have no idea what this could be because from what I can see
> both
> instances of the database are "identical".
Speaking in total ignorance of NCLOB and the DB-API implementation,
could this be a unicode byte order problem? I'm not sure why the 4k
boundary would matter, but that's what it smells like to me.
Ian Kelly
Actually, the 4k boundary is the part that makes sense to me. We're
binding strings naively, without calling setinputsizes, so I believe
they get bound as a VARCHAR2 if they're 4k or less, and they get bound
as an CLOB if they're longer than 4k.
As for what the actual problem is, my guess is as good as yours. It's
probably an encoding problem as you said. All I can recommend is to
fiddle with it and see if anything clears it up. Some things to try
would be changing the database's character encoding, changing the
Django process's NLS_LANG setting, or changing the table to use CLOB
instead of NCLOB.