Orace adapter

[youngblood.carl]

I've put together a new oracle adapter. I didn't really agree with the
philosophy of the existing one with all the regex work to try and fix
sql coming from generic, so this is more of a ground up approach. The
contributions section says we should talk about it before attaching it
to a ticket, so any questions? =)

[youngblood.carl]

Sheesh. I can't believe I misspelled Oracle.

On Dec 8, 4:24 pm, "youngblood.carl" <youngblood.c...@gmail.com>
wrote:

[Russell Keith-Magee]

One big question -- where can we see the code? At the moment, all
you've said is that you've built something. We can't really comment on
the design decisions of something we haven't seen :-)

Yours,
Russ Magee %-)

[youngblood.carl]

Cool! I've attached it to a ticket. I look forward to any and all
feedback. Thanks!

http://south.aeracode.org/ticket/970

[Andrew Godwin]

I don't have the capabilities to test oracle backends, so I'd welcome
feedback from anyone else on the list on how this works.

Andrew

[Sam Hartsfield]

I'll try to take a look at this (do what testing I can) when I get a chance. How much have you tested this / how complete do you think it is thus far?

[Jan Vilhuber]

On Dec 8, 3:24 pm, "youngblood.carl" <youngblood.c...@gmail.com>
wrote:

Hi Carl, trying to use your code, and the first migration we tried
hits this:

"myapp:0017_whatever_whatever_whatever".
django.db.utils.DatabaseError: ORA-01408: such column list already
indexed

What details can I gather for you?
jan

[youngblood.carl]

When I first posted it, it was tested with a set of migrations I was
using as reference. Now it is being used on a project here, so it will
be tested more and more over the next six months or so. I've got 15
migrations at the moment that have been applied to both my local
developer database and our remote test environment database.

As far as completeness, I implemented all the db methods that are
automatically generated by South. I now realize there are other
commands in the documentation, so if you've got migrations you've
written by hand I'm afraid I might have missed something you need.

[youngblood.carl]

One thing that might be helpful is the actual migration module. The
error seems straightforward enough, it seems like somehow we've gotten
into a situation where you're trying to create an index on a column
that already has an index. Does it seem like your migrations from 0001
to 0017 have multiple create_index statements for the same column? If
so, is that valid? Maybe South expects the adapter to be smart enough
to NO-OP if there is already an index? If that is the problem it seems
quite feasible to implement.

[youngblood.carl]

Also, thanks for testing this and giving feedback. I stopped
monitoring this list because of the lack of interest, but I'll be
keeping a close eye on it now, so don't hesitate to keep posting more
questions or problems.

On Dec 20, 5:04 pm, Jan Vilhuber <jan.vilhu...@gmail.com> wrote:

[Jan Vilhuber]

On Dec 22, 2011, at 12:29 PM, youngblood.carl wrote:

> One thing that might be helpful is the actual migration module. The
> error seems straightforward enough, it seems like somehow we've gotten
> into a situation where you're trying to create an index on a column
> that already has an index. Does it seem like your migrations from 0001
> to 0017 have multiple create_index statements for the same column?

I would hope not :) The same migrations are used for mysql migration, and I suspect that would fail there as well.

It is, however possible, that the person who ran the oracle migrations didn't back up from a failed migration previously.

> If
> so, is that valid?

I would think it's not valid, but would defer that to others.
jan

> Maybe South expects the adapter to be smart enough
> to NO-OP if there is already an index? If that is the problem it seems
> quite feasible to implement.
>
> On Dec 20, 5:04 pm, Jan Vilhuber <jan.vilhu...@gmail.com> wrote:
>> On Dec 8, 3:24 pm, "youngblood.carl" <youngblood.c...@gmail.com>
>> wrote:
>>
>>> I've put together a new oracle adapter. I didn't really agree with the
>>> philosophy of the existing one with all the regex work to try and fix
>>> sql coming from generic, so this is more of a ground up approach. The
>>> contributions section says we should talk about it before attaching it
>>> to a ticket, so any questions? =)
>>
>> Hi Carl, trying to use your code, and the first migration we tried
>> hits this:
>>
>> "myapp:0017_whatever_whatever_whatever".
>> django.db.utils.DatabaseError: ORA-01408: such column list already
>> indexed
>>
>> What details can I gather for you?
>> jan
>

> --
> You received this message because you are subscribed to the Google Groups "South Users" group.
> To post to this group, send email to south...@googlegroups.com.
> To unsubscribe from this group, send email to south-users...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/south-users?hl=en.
>

[youngblood.carl]

> It is, however possible, that the person who ran the oracle migrations didn't back up from a failed migration previously.

I don't want to assume this is true, but it sure would make it easier
to explain. If I'm lucky I'll hit the same problem myself soon.
Another thing that might be useful is generally what the 0017
migration is about. Is it adding columns? Altering columns? Explicitly
adding indexes? Does it maybe add a constraint and then immediately
add an index?

[Jan Vilhuber]

On Dec 23, 2011, at 8:32 AM, youngblood.carl wrote:

>> It is, however possible, that the person who ran the oracle migrations didn't back up from a failed migration previously.
>
> I don't want to assume this is true, but it sure would make it easier
> to explain.

We can try running again in a more controlled setting..

> If I'm lucky I'll hit the same problem myself soon.
> Another thing that might be useful is generally what the 0017
> migration is about. Is it adding columns? Altering columns? Explicitly
> adding indexes? Does it maybe add a constraint and then immediately
> add an index?
>

Hi Carl,

without going into details (excuse my paranoia...) it does roughly this: Created a bunch of new tables, 2 m2m tables and adds columns to an existing table.

db.create_table()
db.send_create_signal()
x7

# Adding M2M table for ...
db.create_table()
db.create_unique()

db.create_table()
db.send_create_signal()
x5

# Adding M2M table for ...
db.create_table()
db.create_unique()

db.create_table()
db.send_create_signal()
x5

db.add_column(in some old table)
db.add_column(in the same old table)

When I have more time, I'll try running the migrations myself to get firsthand experience.
Thanks!
jan

[youngblood.carl]

I've uploaded a new version with 2 bug fixes.

[youngblood.carl]

Version 3 is up with a fix for string based defaults.

[Sam Hartsfield]

Hi Carl, I'm finally getting around to trying your adapter. My first problem is with adding a column with a default (it's a CharField). Your get_default method returns "u''" (literally with the u), resulting in SQL like this:

UPDATE "APP_COL" SET name = u'';

I'm not sure if it's correct in all situations, but I added this to get_default:

if isinstance(default, basestring):
    return u"'%s'" % default

Are you using South 0.7.3 or the tip (just wondering what would be best for me to test it with)?

[Sam Hartsfield]

On line 443, 'ALTER TABLE {table_name} ADD {check} );' has an unmatched right parenthesis. My migration 0004 (of 19) ran when I removed that.

After that the rest ran fine, but I'm getting an error when testing the application, so I'll have to look into that further (several triggers are invalid).

Also, it truncated a table name wrong: "DNPNETCFG_GROUPLASTMODIFICD169", but it should have been "DNPNETCFG_GROUPLASTMODIFIC3F4E". That's an issue discussed in another thread and might require modification of other code besides yours; I'll have to look. The two column names that got truncated seem to be correct.

[Sam Hartsfield]

Okay, the triggers. Here's an example trigger that your module generated:

create or replace
TRIGGER dnpnetcfg_portid_TS
BEFORE INSERT ON "DNPNETCFG_PORT"
FOR EACH ROW
BEGIN
  :NEW."ID" := dnpnetcfg_portid_TS.NEXTVAL;
END;

The error is: "ORA-04098: trigger 'DNPNET.DNPNETCFG_PORTID_TS' is invalid and failed re-validation".

The corresponding trigger SQL from manage.py sqlall is:

CREATE OR REPLACE TRIGGER "DNPNETCFG_PORT_TR"
BEFORE INSERT ON "DNPNETCFG_PORT"
FOR EACH ROW
WHEN (new."ID" IS NULL)
    BEGIN
        SELECT "DNPNETCFG_PORT_SQ".nextval
        INTO :new."ID" FROM dual;
    END;

Unfortunately I don't know enough about Oracle's SQL to know what's wrong, or why you did it that way. If it makes a difference, I believe we're using Oracle 10.

There were some other errors when I tried to run the migrations backward. I'll try to look at those, but I'm not too concerned about that right now.

[Sam Hartsfield]

Actually we were using Oracle 9, but it looks like this trigger syntax only works on Oracle 11.