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

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

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