Re: Class::DBI::Oracle vs Class::DBI::Loader

[prev] [thread] [next] [Date index for 2005/02/24]

David N. and others,

Now that Ian has quite rightly shamed me into contributing our
Class::DBI::Loader::Oracle code, I should add some comments.

This might be of interest to other Oracle users of Maypole so I'll copy
that list too (.. see recent Class::DBI discussion on Class::DBI::Oracle
to get above code).

There are a couple of things that should be parameterised in
CDBI::L:O.

You'll see that we exclude one non-application table from the
auto-discovery loop (PLAN_TABLE) because that's one that our (alright,
my) 3rd-party Oracle tool (http://www.dbnet.com.au/piper) creates to
help with explain-plans.  In the general case, an array-ref of
exceptions should be made available by the caller to avoid doing all
that auto-discovery work on tables you're not interested in or which
break assumptions such as pkey existence.

You'll also see that we found it necessary to name an app-specific Base
class; this was necessary because we needed to ensure that our local
Class::DBI triggers were in the inheritance path.  (The Maypole
framework we are using is so damn complex that the BaseClass declaration
in Class::DBI::Loader::Oracle turned out to be a short-circuit
inheritance path back to the core Class::DBI code which had the effect
of "hiding" the Class::DBI-style triggers we named in our Maypole::Model
base class; this was our fix to that problem). 

I always had the intention of implementing some way of parameterising
these settings and thus making this code suitable for contributing to
the general Class::DBI community but it's tricky because it gets called
dynamically by Class::DBI::Loader and not by code that we write. 
Haven't tried to work out a solution to that.. I'm feel I'm still in
Class::DBI Kindergarten when I hit problems like that. 

For what it's worth, the general problem is a consistent theme I have
seen on the Maypole mailing list regards auto-discovery for postgress
and mysql users as well.. the too-clever auto-discovery logic needs to
be given fine-tuning options somehow which would solve a lot of people's
problems.  I guess this means the real enhancement to be done is in
Class::DBI::Loader itself.

Regards the other Oracle issue that arose, namely sequence detection..
there is by design, no formal tie in oracle between a sequence generator
and the table(s) it might be used for.  The catalogs will reveal nothing
here.  So you have to assume a naming convention.  Luckily we had
control over our physical db design so we simply ensured every table
BLAH had a sequence called BLAH_SEQ.  This allowed us to hack
Class::DBI::Oracle to assert exactly what sequence to declare if it
exists.

Class::DBI::Oracle would also be the right place to pick up ref
integrity settings but we have philosphical problems about this lofty
goal.. we much prefer declaring these things manually to Class::DBI
rather than to Oracle.. so we haven't addressed that.  Instead we use
our hack of that module to pick up lots of rich info about the columns..
nullability, datatype, length etc.  The Maypole framework doesn't need
this in its Perl code but we rely on it heavily in our overrides and in
our web page templates.  Again, haven't contributed this back because we
are probably supposed to be using Class::DBI::Column to capture this
stuff, and last time I looked I got the impression it's for internal use
only or something like that.. 

Interested in any thoughts on the above stuff re how Oracle support
should proceed.  I believe there's great untapped potential out there in
the Oracle community.

regds
Frank