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

[prev] [thread] [next] [Date index for 2005/03/11]

Thanks for all that David.  Good on you for getting that code onto
CPAN.

Looks like we were working on an old (pre-Sebastian) version of
CDBI:Loader, hence my original essay on problems with it.  I see now
that Seb. introduced a clean integration model there which you take
advantage of in your much neater version of CDBI:L:O.  (That probably
also means that your code should confirm what Version of CDBI:L it is
using.)

I agree, an 'exclude' pattern as well as a 'constraint' pattern would
be very useful.

Regards CDBI::Oracle itself, did we send you our cut of that?  You will
see there how we capture lots of the meta-data we have spoken of, we
just introduce more hash tables (keyed by column names) as class-level
accessors, and populate them.  Let me know if you would like to see it
too!

frank

btw.. off topic.. if you use oracle and have any windows workstations,
have a look at Piper (link below) and tell me what you think.  It's
free.

>>> David Naughton <naughton@xxx.xxx> 10/03/05 3:57:51 AM >>>
On Thu, Feb 24, 2005 at 11:42:33AM +1100, Frank Carnovale wrote:
> 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.

Class::DBI::Loader::Oracle 0.01 is now available on CPAN. Thanks for
your help in making that happen; I hope it will meet your needs. Now
I'll address some of your comments.
 
[..]

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

D'Oh! I left the hard-coded exclusion of PLAN_TABLE in my code.
Anyway,
hopefully that means my CPAN version will work for you. More below.

> 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.
 
Right. Notice that in CDBI::Loader::Generic there's a "constraint"
regex
option that causes only matching tables to be "discovered". To do what
you want, we need an analogous "exclude" regex option, or something
similar.  I'm working with Sebastian Riedel on some other projects, so
I
should be able to convince him to add that. After that, I'll remove
the
hard-coded exclusion from CDBI::Loader::Oracle.

> 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 implemented CDBI::Loader::Oracle a little differently than you did,
such that you should be able to accomplish this with the
already-existing "additional_base_classes" option, i.e. put

  additional_base_classes => 'ITSRR::AllTables'

in your constructor params. Let me know if that works for you.

> 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.
 
Addressed above. Again, if you have any comments on my proposed
solutions, I'd love to hear them.

> 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.
 
I propose that we also submit to Dan Sully a CDBI::Oracle patch that
allows the user to specify sequence naming conventions.

> 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.. 
 
Hmm, I'll have to think more about this. Users of other dbs probably
want these things too, so it would be nice to support it in a way that
allows all the CDBI::<db> and CDBI::Loader::<db> modules to play
nicely
together. I'd like to get others' comments.

> 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

David